import MySQLdb
from aus import Release, Update, Patch

debug = 0

class DatabaseHandler:
    def __init__(self, dbname):
        self.dbname = dbname
        self.connection = MySQLdb.connect(user="root", db=dbname)
            
    def close(self):
        self.connection.close()
            
    def execute(self, query, paras):
        if debug:
          print query
          print paras
        cursor = self.connection.cursor()
        cursor.execute(query, paras)
        response = cursor.fetchall()
        cursor.close()
        self.connection.commit()
        return response

    def getPatch(self, id):
        return self.execute('SELECT * FROM patches WHERE id=%s', (id))

    def setPatch(self, patch):
        type = patch.getType()
        url = patch.getUrl()
        hashFunction = patch.getHashFunction()
        hashValue = patch.getHashValue()
        size = patch.getSize()
        buildId = patch.getBuildId()

        typeint = None
        if type == "complete":
            typeint = 0
        elif type == "partial":
            typeint = 1
        self.execute('INSERT INTO patches VALUES(null, %s, %s, %s, %s, %s, %s)', (typeint, url, hashFunction, hashValue, size, buildId))
        return int(self.execute('SELECT last_insert_id()',())[0][0])

    def getUpdate(self, id):
        return self.execute('SELECT * FROM updates WHERE id=%s', (id))

    def setUpdate(self, update, complete_patch_id, partial_patch_id):
        type = update.getType()
        version = update.getVersion()
        appv = update.getAppVersion()
        extv = update.getExtensionVersion()
        detailsUrl = update.getDetailsUrl()
        licenseUrl = update.getLicenseUrl()

        typeint = None
        if type == "minor":
            typeint = 0
        elif type == "major":
            typeint = 1
        else:
            print "WARNING: unknown updateType: "+type
        self.execute('INSERT INTO updates VALUES (null, %s, %s, %s, %s, %s, %s, %s, %s)', (typeint, version, appv, extv, detailsUrl, licenseUrl, complete_patch_id, partial_patch_id))
        return int(self.execute('SELECT last_insert_id()',())[0][0])

    def getAllReleases(self):
        release = None
        for (release_id, product, version, buildId, buildTarget, locale, channel, osVersion, distribution, distributionVersion, update_id) in self.execute('SELECT releases.id, products.name as product, versions.name as version, buildIds.name as buildId, buildTargets.name as buildTarget, locales.name as locale, channels.name as channel, osVersions.name as osVersion, distributions.name as distribution, distributionVersions.name as distributionVersion, releases.update_id FROM releases, products, versions, buildIds, buildTargets, locales, channels, osVersions, distributions, distributionVersions WHERE releases.product_id = products.id and releases.version_id = versions.id and releases.buildId_id = buildIds.id and releases.buildTarget_id = buildTargets.id and releases.locale_id = locales.id and releases.channel_id = channels.id and releases.osVersion_id = osVersions.id and releases.distribution_id = distributions.id and releases.distributionVersion_id = distributionVersions.id', ()):
            release = Release(product, version, buildTarget, buildId, locale, channel, None)
            if update_id == None:
                continue
            updateResult = self.getUpdate(update_id) 
            if len(updateResult) == 1:
                (update_id, updateTypeInt, version, appv, extv, detailsUrl, licenseUrl, complete_patch_id, partial_patch_id) = updateResult[0]
                updateType = None
                if updateTypeInt == 0:
                    updateType = "minor"
                elif updateTypeInt == 1:
                    updateType = "major"
                else:
                    print "WARNING: unknown updateType: "+updateTypeInt
                update = Update(updateType, version, appv, extv, detailsUrl, licenseUrl)
                release.setUpdate(update)
                completePatchResult = self.getPatch(complete_patch_id)
                if len(completePatchResult) == 1:
                    (complete_patch_id, typeint, url, hashFunction, hashValue, size, build) = completePatchResult[0]
                    type = None
                    if typeint == 0:
                        type = "complete"
                    elif typeint == 1:
                        type = "partial"
                    update.setCompletePatch(Patch(type, url, hashFunction, hashValue, size, build))
                partialPatchResult = self.getPatch(partial_patch_id)
                if len(partialPatchResult) == 1:
                    (partial_patch_id, type, url, hashFunction, hashValue, size, build) = partialPatchResult[0]
                    type = None
                    if typeint == 0:
                        type = "complete"
                    elif typeint == 1:
                        type = "partial"
                    update.setPartialPatch(Patch(type, url, hashFunction, hashValue, size, build))
            yield release
    
    def setRelease(self, release, update_id):
        product = release.getProduct()
        version = release.getVersion()
        buildTarget = release.getBuildTarget()
        buildid = release.getBuildId()
        locale  = release.getLocale()
        channel = release.getChannel()
        osVersion = release.getOsVersion()
        distribution = release.getDistribution()
        distributionVersion = release.getDistributionVersion()

        # TODO lots of duplicate code, make this a more generic loop
        self.execute('INSERT INTO products VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (product, product))
        self.execute('INSERT INTO versions VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (version, version))
        self.execute('INSERT INTO buildids VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (buildid, buildid))
        self.execute('INSERT INTO buildTargets VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (buildTarget, buildTarget))
        self.execute('INSERT INTO locales VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (locale, locale))
        self.execute('INSERT INTO channels VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (channel, channel))
        self.execute('INSERT INTO osVersions VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (osVersion, osVersion))
        self.execute('INSERT INTO distributions VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (distribution, distribution))
        self.execute('INSERT INTO distributionVersions VALUES (null, %s) ON DUPLICATE KEY UPDATE name=%s', (distributionVersion, distributionVersion))
        # FIXME more efficient to collect ids above and insert instead of subselect (or join)
        self.execute('INSERT IGNORE INTO releases VALUES (null, (SELECT id FROM products WHERE name=%s),(SELECT id FROM versions WHERE name=%s),(SELECT id FROM buildids WHERE name=%s),(SELECT id FROM buildTargets WHERE name=%s),(SELECT id FROM locales WHERE name=%s),(SELECT id FROM channels WHERE name=%s),(SELECT id FROM osVersions WHERE name=%s),(SELECT id FROM distributions WHERE name=%s),(SELECT id FROM distributionVersions WHERE name=%s), %s)', (product, version, buildid, buildTarget, locale, channel, osVersion, distribution, distributionVersion, update_id))