#!/usr/bin/python # ***** BEGIN LICENSE BLOCK ***** # Version: MPL 1.1/GPL 2.0/LGPL 2.1 # # The contents of this file are subject to the Mozilla Public License Version # 1.1 (the "License"); you may not use this file except in compliance with # the License. You may obtain a copy of the License at # http://www.mozilla.org/MPL/ # # Software distributed under the License is distributed on an "AS IS" basis, # WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License # for the specific language governing rights and limitations under the # License. # # The Original Code is addons.mozilla.org site. # # The Initial Developer of the Original Code is # The Mozilla Foundation. # Portions created by the Initial Developer are Copyright (C) 2006 # the Initial Developer. All Rights Reserved. # # Contributor(s): # Lars Lohn (Original Author) # # Alternatively, the contents of this file may be used under the terms of # either the GNU General Public License Version 2 or later (the "GPL"), or # the GNU Lesser General Public License Version 2.1 or later (the "LGPL"), # in which case the provisions of the GPL or the LGPL are applicable instead # of those above. If you wish to allow use of your version of this file only # under the terms of either the GPL or the LGPL, and not to allow others to # use your version of this file under the terms of the MPL, indicate your # decision by deleting the provisions above and replace them with the notice # and other provisions required by the GPL or the LGPL. If you do not delete # the provisions above, a recipient may use your version of this file under # the terms of any one of the MPL, the GPL or the LGPL. # # ***** END LICENSE BLOCK ***** import cse.Database import cse.MySQLDatabase import cse.TabularData from Crypto.Hash import * import mx.DateTime import os.path import urllib2 import urllib import Image import sys import os import traceback version = "0.7" standardError = sys.stderr #standardError = sys.stdout #sys.stderr = sys.stdout class MigrationException(Exception): pass #----------------------------------------------------------------------------------------------------------- # imageAndThumbFromURL #----------------------------------------------------------------------------------------------------------- def imageAndThumbFromURL (aURL, xThumbSize=200, yThumbSize=150, scratchDirectory=".", transparentPngPathName="./blank.png"): urlReader = urllib2.urlopen(aURL) contentType = urlReader.info()['Content-Type'] content = urlReader.read() temporaryFileName = ("%s/i.%s" % (scratchDirectory, aURL[-3:])).replace("//", "/") temporaryThumbFileName = ("%s/t.png" % (scratchDirectory, )).replace("//", "/") f = open(temporaryFileName, "w") try: f.write(content) f.close() im = Image.open(temporaryFileName) try: im.thumbnail((xThumbSize, yThumbSize), Image.ANTIALIAS) targetImage = Image.open(transparentPngPathName) targetImage.paste(im, ((xThumbSize - im.size[0]) / 2, (yThumbSize - im.size[1]) / 2)) targetImage.save(temporaryThumbFileName) f = open(temporaryThumbFileName) thumbContent = f.read() f.close() finally: os.unlink(temporaryThumbFileName) finally: os.unlink(temporaryFileName) return (content, contentType, thumbContent, "image/png") #----------------------------------------------------------------------------------------------------------- # sha1Hash #----------------------------------------------------------------------------------------------------------- def sha1Hash (aURL, addonID, cachePath): basename = os.path.basename(aURL) try: f = open ("%s/%d/%s" % (cachePath, addonID, basename)) #print "getting from cache" block = f.read() f.close() fromCache = True except: #print "getting from web" fromCache = False aURL = urllib.quote(aURL, "/:") urlReader = urllib2.urlopen(aURL) block = urlReader.read() shaCalculator = SHA.new() shaCalculator.update(block) if not fromCache and cachePath: try: f = open("%s/%d/%s" % (cachePath, addonID, basename), "w") except: #print "missing directory %s/%d" % (cachePath, addonID) os.mkdir("%s/%d" % (cachePath, addonID)) f = open("%s/%d/%s" % (cachePath, addonID, basename), "w") #print "putting to cache" f.write(block) f.close() return "sha1:%s" % shaCalculator.hexdigest() #----------------------------------------------------------------------------------------------------------- # firstWords #----------------------------------------------------------------------------------------------------------- def firstWords (originalString, maxCharacters = 250, maxSearchBack = 20): """This function returns the first maxCharacters of originalString making sure that the end of the string was cut on a word break and elipsis (...) was appended. input: originalString - the string maxCharacters - the maximum number of characters to return maxSearchBack - if no word break was found after searching back this many characters, give up and return the maximum number of characters """ try: originalString = originalString.lstrip() if len(originalString) <= maxCharacters: maxCharacters = len(originalString) breakIndex = maxCharacters - 3 maximumSearchBackIndex = breakIndex - maxSearchBack if maximumSearchBackIndex < 0: maximumSearchBackIndex = 0 firstLineBreak = min(originalString.find('\n'), originalString.find('\r')) if -1 < firstLineBreak < breakIndex: breakIndex = firstLineBreak while breakIndex > maximumSearchBackIndex and not (originalString[breakIndex] == " " and originalString[breakIndex-1] != " "): breakIndex -= 1 if breakIndex == maximumSearchBackIndex: breakIndex = maxCharacters - 3 try: if originalString[breakIndex - 1] in ".,:\"'!": breakIndex -= 1 returnPhrase = "%s..." % originalString[:breakIndex] except IndexError: returnPhrase = "" assert(len(returnPhrase) <= maxCharacters) return returnPhrase except AssertionError: print "%d [[[%s]]]" % (len(originalString), originalString) print "%d <<<%s>>>" % (len(returnPhrase), returnPhrase) except TypeError, x: print >>standardError, x print >>standardError, " %s passed in as a string" % originalString except Exception, x: print >>standardError, x traceback.print_exc(file=standardError) #----------------------------------------------------------------------------------------------------------- # yesNoEnumToTinyIntMappings #----------------------------------------------------------------------------------------------------------- yesNoEnumToTinyIntMappingForApproval = { "YES": 4, "NO": 1, "?":2, "DISABLED":5 } yesNoEnumToTinyIntMappingForHighlight = { "YES": 1, "NO": 0 } #----------------------------------------------------------------------------------------------------------- # nullToEmptyString #----------------------------------------------------------------------------------------------------------- def nullToEmptyString (aString): if aString is None: return "" return aString #----------------------------------------------------------------------------------------------------------- # nullToEmptyDate #----------------------------------------------------------------------------------------------------------- def nullToEmptyDate (aDate): if aDate is None: return "0000-00-00 00:00:00" return aDate #----------------------------------------------------------------------------------------------------------- # addTranslation #----------------------------------------------------------------------------------------------------------- def addTranslation (newDB, aTransalationString, aLocaleString): """adds a new translation to the translations table and returns the index""" newDB.executeSql("UPDATE translations_seq SET id=LAST_INSERT_ID(id+1)") newID = newDB.singleValueSql("SELECT LAST_INSERT_ID()") newDB.commit() newDB.executeManySql("insert into translations (id, locale, localized_string, created) values (%s, %s, %s, %s)", [(newID, aLocaleString, aTransalationString, mx.DateTime.now())] ) newDB.commit() return newID #----------------------------------------------------------------------------------------------------------- # cleanMetaDataTables #----------------------------------------------------------------------------------------------------------- def cleanMetaDataTables (newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tclearing metadata tables..." % mx.DateTime.now() listOfTranslationsToDelete = newDB.executeSql(""" select name from applications union select shortname from applications union select name from platforms union select shortname from platforms union select name from tags union select description from tags""").contents[1] newDB.executeSql("delete from tags") newDB.executeSql("delete from appversions") newDB.executeSql("delete from applications") newDB.executeSql("delete from platforms") newDB.executeSql("delete from addons_users") newDB.executeSql("delete from users") newDB.executeManySql("delete from translations where id = %s", listOfTranslationsToDelete) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # applicationsToApplications #----------------------------------------------------------------------------------------------------------- appversionCorrection = { "Firefox": { "0.9.x":"0.9.3", "1.0PR":"0.10", "1.5.0.4":"1.5.0.*", "1.6a1":"1.5.0.*", "Deer Park":"1.0+" }, "Thunderbird": { "1.5.0.5": "1.5.0.*", "1.5beta": "1.5b", "1.6a1": "1.5.0.*" } } appversionCorrectionForMin = { "Firefox": { ".9":"0.9", "0.1":"0.10", "0.80":"0.8", "0.9.6":"0.10", "0.9.x":"0.10", "00.8":"0.8", "1.0PR":"0.10", "1.4.0":"1.4", "1.5*":"1.5", "1.5.0.*":"1.5", "1.5.0.4":"1.5", "1.6":"2.0a1", "2.0b1":"2.0b2", "Deer Park":"1.5" }, "Flock": { "0.1":"0.1", "0.4.0":"0.4" }, "Mozilla": { "-": "1.0", "1":"1.0", "1.8+":"1.8" }, "Netscape": { "8":"8.0" }, "Nvu": { "1.0":"1.1" }, "SeaMonkey": { "1.0":"1.1" }, "Sunbird": { "0.3.1":"0.3a1" }, "Thunderbird": { "1.4":"1.5", "1.4.1":"1.5", "1.5.0.*":"1.5", "1.5.0.2":"1.5", "1.5.0.4":"1.5", "1.5.0.5":"1.5", "1.6":"2.0" } } appversionCorrectionForMax = { "Firefox": { "0.10.1":"0.10", "0.10.1+":"0.10.1", "0.11":"0.10", "01.6":"1.5.0.*", "1.1":"1.0.8", "1.5":"1.5.0.*", "1.5.0.*":"1.5.0.*", "1.5.0.4":"1.5.0.*", "1.6":"1.5.0.*", "1.6a1":"1.5.0.*", "2.0":"2.0.0.*", "2.0.0.*":"2.0.0.*", "2.0.0.a3":"2.0a3", "2.0.0.b1":"2.0b1", "3.0":"3.0a1", "3.0.0.a1":"3.0a1", "Deer Park":"1.0+" }, "Flock": { "0.*":"0.5.*", "0.8.x":"0.8.*", "10.0":"1.0", "6.02":"6.0", "9999.0+":"1.0+" }, "Mozilla": { "-":"1.0", "1":"1.0", "1.5.0.*":"1.5", }, "SeaMonkey": { "1.5.0.a":"1.5a" }, "Sunbird": { "0.3.1":"0.3a1" }, "Thunderbird": { "0.10":"0.9", "0.9.0+":"0.9+", "1.2":"1.1a1", "1.4":"1.5b1", "1.4.1":"1.5b2", "1.5":"1.5.0.*", "1.5.0.2":"1.5.0.*", "1.5.0.4":"1.5.0.*", "1.6":"1.5.0.*", "1.6a1":"1.5.0.*", "2.0":"2.0.0.*", "3.0":"3.0a1", "3.0a1":"3.0a1" } } #----------------------------------------------------------------------------------------------------------- # correctAppversion #----------------------------------------------------------------------------------------------------------- def correctAppversion (appName, versionName, correctionDictionary): try: return correctionDictionary[appName][versionName] except KeyError: return versionName #----------------------------------------------------------------------------------------------------------- # applicationsToApplications #----------------------------------------------------------------------------------------------------------- applicationsInsertSql = """ insert into applications (id, guid, name, shortname, supported, created) values (%s, %s, %s, %s, %s, %s)""" appversionsInsertSql = """ insert into appversions (id, application_id, version, created) values (%s, %s, %s, %s)""" def applicationsToApplications (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning applicationsToApplications..." % mx.DateTime.now() applicationsAlreadyInserted = {} appversionsAlreadyInserted = {} for a in oldDB.executeSqlNoCache("select * from applications"): if a.AppName not in applicationsAlreadyInserted: newDB.executeManySql(applicationsInsertSql, [ (a.AppID, #id a.GUID, #guid addTranslation(newDB, a.AppName, "en-US"), #name addTranslation(newDB, a.AppName, "en-US"), #shortname a.supported, #supported mx.DateTime.now()) #created ] ) applicationsAlreadyInserted[a.AppName] = a.AppID appversionsAlreadyInserted[a.AppName] = {} correctedAppversion = correctAppversion(a.AppName, a.Version, appversionCorrection) if correctedAppversion not in appversionsAlreadyInserted[a.AppName]: newDB.executeManySql(appversionsInsertSql, [ (a.AppID, #id applicationsAlreadyInserted[a.AppName], #application_id correctedAppversion, #version mx.DateTime.now()) #created ] ) appversionsAlreadyInserted[a.AppName][correctedAppversion] = True newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # categoriesToTags #----------------------------------------------------------------------------------------------------------- tagsInsertSql = """ insert into tags (id, name, description, addontype_id, application_id, created) values (%s, %s, %s, %s, %s, %s)""" typeEnumToTypeNameMapping = {'E': 'Extension', 'T': 'Theme', 'P': 'Plugin' } def categoriesToTags (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning categoriesToTags..." % mx.DateTime.now() for c in oldDB.executeSqlNoCache("select * from categories"): newDB.executeManySql(tagsInsertSql, [ (c.CategoryID, #id addTranslation(newDB, c.CatName, workingEnvironment["locale"]), #name addTranslation(newDB, c.CatDesc, workingEnvironment["locale"]), #description newDB.singleValueSql("select a.id from addontypes a join translations t on a.name = t.id and t.locale = 'en-US' and localized_string = '%s'" % typeEnumToTypeNameMapping[c.CatType]), #addontype_id newDB.singleValueSql("select a.id from applications a join translations t on a.name = t.id and t.locale = 'en-US' and localized_string = '%s'" % c.CatApp), #application_id mx.DateTime.now()) #created ] ) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # osToPlatforms #----------------------------------------------------------------------------------------------------------- plaformsInsertSql = """ insert into platforms (id, name, shortname, created) values (%s, %s, %s, %s)""" def osToPlatforms (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning osToPlatforms..." % mx.DateTime.now() for o in oldDB.executeSqlNoCache("select * from os"): newDB.executeManySql(plaformsInsertSql, [ (o.OSID, #id addTranslation(newDB, o.OSName, "en-US"), #name addTranslation(newDB, o.OSName, "en-US"), #shortname mx.DateTime.now()) #created ] ) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # userprofilesToUsers #----------------------------------------------------------------------------------------------------------- usersInsertSql = """ insert into users (id, firstname, lastname, nickname, email, homepage, password, emailhidden, confirmationcode, created, notes) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" def userprofilesToUsers (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning userprofilesToUsers..." % mx.DateTime.now() for u in oldDB.executeSqlNoCache("select * from userprofiles"): splitName = u.UserName.split() try: lastName = splitName[-1] except: lastName = "?" try: firstName = ' '.join(splitName[:-1]) except: firstname = "?" newDB.executeManySql(usersInsertSql, [ (u.UserID, #id firstName, #firstname lastName, #lastname '', #nickname u.UserEmail, #email u.UserWebsite, #homepage u.UserPass, #password u.UserEmailHide, #emailhidden u.ConfirmationCode, #confirmationcode mx.DateTime.now(), #created None) #notes ] ) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # cleanAddonsTables #----------------------------------------------------------------------------------------------------------- def cleanAddonsTables (newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tclearing addons tables..." % mx.DateTime.now() if "all" in workingEnvironment["addons"]: listOfTranslationsToDelete = newDB.executeSql(""" select caption from previews union select releasenotes from versions union select name from addons union select homepage from addons union select description from addons union select summary from addons union select developercomments from addons union select eula from addons union select privacypolicy from addons """).contents[1] newDB.executeSql("delete from previews") newDB.executeSql("delete from addons_tags") newDB.executeSql("delete from applications_versions") newDB.executeSql("delete from files") newDB.executeSql("delete from versions") newDB.executeSql("delete from addons_users") newDB.executeSql("delete from addons") else: listOfTranslationsToDelete = newDB.executeSql(""" select caption from previews where addon_id in (select id from addonSelections) union select releasenotes from versions where addon_id in (select id from addonSelections) union select name from addons where id in (select id from addonSelections) union select homepage from addons where id in (select id from addonSelections) union select description from addons where id in (select id from addonSelections) union select summary from addons where id in (select id from addonSelections) union select developercomments from addons where id in (select id from addonSelections) union select eula from addons where id in (select id from addonSelections) union select privacypolicy from addons where id in (select id from addonSelections) """).contents[1] newDB.executeSql("delete from previews where addon_id in (select id from addonSelections)") newDB.executeSql("delete from addons_tags where addon_id in (select id from addonSelections)") newDB.executeSql("delete from applications_versions where version_id in (select v.id from versions v where addon_id in (select id from addonSelections))") newDB.executeSql("delete from files where version_id in (select v.id from versions v where addon_id in (select id from addonSelections))") newDB.executeSql("delete from versions where addon_id in (select id from addonSelections)") newDB.executeSql("delete from addons_users where addon_id in (select id from addonSelections)") newDB.executeSql("delete from addons where id in (select id from addonSelections)") newDB.executeManySql("delete from translations where id = %s", listOfTranslationsToDelete) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # mainToAddOns #----------------------------------------------------------------------------------------------------------- addonsInsertSql = """ insert into addons (id, guid, name, addontype_id, created, homepage, description, averagerating, weeklydownloads, totaldownloads, developercomments, summary, eula, privacypolicy) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""" def mainToAddOns (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning mainToAddOns..." % mx.DateTime.now() if "all" in workingEnvironment["addons"]: sql = "select m.* from main m" else: sql = "select m.* from main m where m.ID in (select id from addonSelections)" currentLocale = workingEnvironment["locale"] for a in oldDB.executeSqlNoCache(sql): #print a.ID, sql x = (a.ID, #id a.GUID, #guid addTranslation(newDB, a.Name, currentLocale), #name newDB.singleValueSql("select a.id from addontypes a join translations t on a.name = t.id and t.locale = 'en-US' and localized_string = '%s'" % typeEnumToTypeNameMapping[a.Type]), #addontype_id mx.DateTime.now(), #created addTranslation(newDB, a.Homepage, currentLocale), #homepage addTranslation(newDB, a.Description, currentLocale), #description a.Rating, #averagerating a.downloadcount, #weeklydownloads a.TotalDownloads, #totaldownloads addTranslation(newDB, a.devcomments, currentLocale), #developercomments addTranslation(newDB, firstWords(a.Description, 250), currentLocale), #summary None, #eula None) #privacypolicy newDB.executeManySql(addonsInsertSql, [ x ] ) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # authorxrefToAddons_users #----------------------------------------------------------------------------------------------------------- addons_usersInsertSql = """ insert into addons_users (addon_id, user_id) values (%s, %s)""" def authorxrefToAddons_users (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning authorxrefToAddons_users..." % mx.DateTime.now() if "all" in workingEnvironment["addons"]: sql = "select * from authorxref" else: sql = "select * from authorxref where id in (select id from addonSelections)" for u in oldDB.executeSqlNoCache(sql): newDB.executeManySql(addons_usersInsertSql, [ (u.ID, u.UserID) ] ) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # versionToVerions #----------------------------------------------------------------------------------------------------------- versionsInsertSql = """ insert into versions (id, addon_id, version, releasenotes, approvalnotes, created, modified) values (%s, %s, %s, %s, %s, %s, %s)""" applications_versionsInsertSql = """ insert into applications_versions (application_id, version_id, min, max) values (%s, %s, %s, %s)""" applications_versionsUpdateSql = """ update applications_versions set min = %s, max = %s where application_id =%s and version_id = %s""" filesInsertSql = """ insert into files (version_id, platform_id, filename, size, hash, status, datestatuschanged, created) values (%s, %s, %s, %s, %s, %s, %s, %s)""" filesUpdateSql = """ update files set size = %s, hash = %s, status = %s, datestatuschanged = %s, created = %s where version_id = %s and platform_id = %s and filename = %s""" findAppVersionSql = """select id from appversions where application_id = %d and version = '%s'""" findAppNameSql = """select appname from applications where AppID = %d""" findNewAppIDSql = "select a.id from applications a join translations t on a.name = t.id and t.locale = 'en-US' and localized_string = '%s'" appversionsInsertWithNewIDSql = """ insert into appversions (application_id, version, created) values (%s, %s, %s)""" addonUpdateForStatus = """update addons set status = %s where id = %s""" def versionToVerions (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning versionToVerions..." % mx.DateTime.now() if "all" in workingEnvironment["addons"]: if "verbose" in workingEnvironment: print >>standardError, "\t\t\t(this may take over ten minutes)" # for duplicates of the same Addon, Version, OS and Application, take only the latest #sql = "select * from version where vID in (select max(vID) from version group by ID, Version, OSID, AppID) order by ID, vID" sql = "select * from version order by ID, vID" else: #sql = "select * from version where id in (select id from addonSelections) and vID in (select max(vID) from version group by ID, Version, OSID, AppID) order by ID, vID" sql = "select * from version where id in (select id from addonSelections) order by ID, vID" versionsAlreadyInserted = {} applications_versionsAlreadyInserted = {} filesAlreadyInserted = {} fileStatusKeyedByAddon = {} for v in filterVersionsAndFilesGenerator(sql, oldDB, newDB): #print v try: if v.ID in fileStatusKeyedByAddon: fileStatusKeyedByAddon[v.ID].append(yesNoEnumToTinyIntMappingForApproval[v.approved]) else: fileStatusKeyedByAddon[v.ID] = [yesNoEnumToTinyIntMappingForApproval[v.approved]] if v.AppID == 79: v.AppID = 1 #hack hack hack if (v.ID, v.Version) not in versionsAlreadyInserted: newDB.executeManySql(versionsInsertSql, [ (v.vID, #id v.ID, #addon_id v.Version, #version addTranslation(newDB, v.Notes, workingEnvironment["locale"]), #releasenotes "", #approvalnotes v.DateAdded, #created nullToEmptyDate(v.DateUpdated)) #modified ] ) versionId = versionsAlreadyInserted[(v.ID, v.Version)] = v.vID else: versionId = versionsAlreadyInserted[(v.ID, v.Version)] applicationName = oldDB.singleValueSql(findAppNameSql % v.AppID) #newAppId = newDB.singleValueSql(findNewAppIDSql % applicationName) newAppId = v.newAppId appversionIdForMin = newDB.singleValueSql(findAppVersionSql % (newAppId, v.MinAppVer)) if not appversionIdForMin: translatedVersion = correctAppversion(applicationName, v.MinAppVer, appversionCorrectionForMin) appversionIdForMin = newDB.singleValueSql(findAppVersionSql % (newAppId, translatedVersion)) if not appversionIdForMin: newDB.executeManySql(appversionsInsertWithNewIDSql, [ (newAppId, translatedVersion , mx.DateTime.now()) ] ) appversionIdForMin = newDB.singleValueSql(findAppVersionSql % (newAppId, translatedVersion)) appversionIdForMax = newDB.singleValueSql(findAppVersionSql % (newAppId, v.MaxAppVer)) if not appversionIdForMax: translatedVersion = correctAppversion(applicationName, v.MaxAppVer, appversionCorrectionForMax) appversionIdForMax = newDB.singleValueSql(findAppVersionSql % (newAppId, translatedVersion)) if not appversionIdForMax: newDB.executeManySql(appversionsInsertWithNewIDSql, [ (newAppId, translatedVersion, mx.DateTime.now()) ] ) appversionIdForMax = newDB.singleValueSql(findAppVersionSql % (newAppId, translatedVersion)) if (v.AppID, versionId) not in applications_versionsAlreadyInserted: newDB.executeManySql(applications_versionsInsertSql, [ (v.AppID, versionId, appversionIdForMin, appversionIdForMax) ] ) applications_versionsAlreadyInserted[(v.AppID, versionId)] = (appversionIdForMin, appversionIdForMax) elif applications_versionsAlreadyInserted[(v.AppID, versionId)] != (appversionIdForMin, appversionIdForMax): print >>standardError, "%s\tWARNING -- version ID %d of addon %s for application %d has a min/max appversion conflict" % (mx.DateTime.now(), v.vID, v.ID, v.AppID) print >>standardError, " old values: %s are superceded by new values: %s," % (applications_versionsAlreadyInserted[(v.AppID, versionId)], (appversionIdForMin, appversionIdForMax)) newDB.executeManySql(applications_versionsUpdateSql, [ (appversionIdForMin, appversionIdForMax, v.AppID, versionId) ] ) applications_versionsAlreadyInserted[(v.AppID, versionId)] = (appversionIdForMin, appversionIdForMax) baseFileName = os.path.basename(v.URI) #print "considering:", (versionId, v.OSID, baseFileName, v.AppID), v.URI #if (versionId, v.OSID, baseFileName, v.AppID) not in filesAlreadyInserted: if 'ignoreHash' not in workingEnvironment and ("recalculateHash" in workingEnvironment or v.hash == "" or v.hash is None): try: newHash = sha1Hash(v.URI, v.ID, workingEnvironment["fileCachePath"]) except Exception, x: print >>standardError, "%s\tWARNING -- version ID %d of addon %s for application %d has a URL problem - %s: %s -- No hash value has been computed" % (mx.DateTime.now(), v.vID, v.ID, v.AppID, x, v.URI) newHash = "" else: newHash = v.hash if (versionId, v.OSID, baseFileName) not in filesAlreadyInserted: newDB.executeManySql(filesInsertSql, [ (versionId, #version_id v.OSID, #platform_id baseFileName, #filename v.Size, #size newHash, #hash yesNoEnumToTinyIntMappingForApproval[v.approved], #status nullToEmptyDate(v.DateApproved), #datestatuschanged mx.DateTime.now()) #created ] ) #filesAlreadyInserted[(versionId, v.OSID, baseFileName, v.AppID)] = None filesAlreadyInserted[(versionId, v.OSID, baseFileName)] = None else: #print >>standardError, "%s\tWARNING -- version ID %d of addon %s for application %d has a duplicate file - the later one supercedes the older one" % (mx.DateTime.now(), versionId, v.ID, v.AppID) newDB.executeManySql(filesUpdateSql, [ (v.Size, #size newHash, #hash yesNoEnumToTinyIntMappingForApproval[v.approved], #status nullToEmptyDate(v.DateApproved), #datestatuschanged mx.DateTime.now(), #created versionId, #version_id v.OSID, #platform_id baseFileName ) #filename ] ) newDB.commit() except KeyboardInterrupt, x: raise x except Exception, x: print >>standardError, "%s\tWARNING -- version ID %d of addon %s for application %d fails to migrate.\n\t\t\t%s" % (mx.DateTime.now(), v.vID, v.ID, v.AppID, x) traceback.print_exc(file=standardError) newDB.rollback() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tupdating addon status..." try: for addonId, listOfFileApprovals in fileStatusKeyedByAddon.iteritems(): #print addonId, listOfFileApprovals, if 4 in listOfFileApprovals: #if any files approved newAddonStatus = workingEnvironment["addonStatusWhenSomeFilesApproved"] #print "any", newAddonStatus else: newAddonStatus = workingEnvironment["addonStatusWhenNoFilesApproved"] #print "all", newAddonStatus newDB.executeManySql (addonUpdateForStatus, [(newAddonStatus, addonId)]) except Exception, x: newDB.rollback() raise x newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." def onlyFirefoxUnlessThereIsNoFirefox (versionFileListForOneVersion): differentFilesFound = False try: initialURI = versionFileListForOneVersion[0].URI except IndexError: return for x in versionFileListForOneVersion: differentFilesFound = initialURI != x.URI if differentFilesFound: break if differentFilesFound: firefoxFound = False for aVersionFile in versionFileListForOneVersion: if aVersionFile.AppID == 1: #Firefox #print "FF only", aVersionFile #if len(versionFileListForOneVersion) > 1: # print aVersionFile.ID, aVersionFile.Version yield aVersionFile firefoxFound = True if not firefoxFound: for aVersionFile in versionFileListForOneVersion: #print "NO FF", aVersionFile yield aVersionFile else: for aVersionFile in versionFileListForOneVersion: #print "NO FF", aVersionFile yield aVersionFile def filterVersionsAndFilesGenerator (sqlToFetchVersionFiles, oldDB, newDB): previousVersion = None currentVersion = None versionFileList = [] for aVersionFileRow in oldDB.executeSqlNoCache(sqlToFetchVersionFiles): applicationName = oldDB.singleValueSql(findAppNameSql % aVersionFileRow.AppID) newAppId = newDB.singleValueSql(findNewAppIDSql % applicationName) aVersionFileRow.__dict__["newAppId"] = newAppId currentVersion = (aVersionFileRow.ID, aVersionFileRow.Version, aVersionFileRow.OSID) if previousVersion != currentVersion: for x in onlyFirefoxUnlessThereIsNoFirefox(versionFileList): yield x previousVersion = currentVersion versionFileList = [] versionFileList.append(aVersionFileRow) for x in onlyFirefoxUnlessThereIsNoFirefox(versionFileList): yield x #----------------------------------------------------------------------------------------------------------- # categoryxrefToAddons_tags #----------------------------------------------------------------------------------------------------------- addons_tagsInsertSql = """ insert into addons_tags (addon_id, tag_id) values (%s, %s)""" def categoryxrefToAddons_tags (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning categoryxrefToAddons_tags..." % mx.DateTime.now() if "all" in workingEnvironment["addons"]: sql = "select * from categoryxref" else: sql = "select * from categoryxref where id in (select id from addonSelections)" for c in oldDB.executeSqlNoCache(sql): #print c.ID, c.CategoryID try: newDB.executeManySql(addons_tagsInsertSql, [ (c.ID, c.CategoryID) ] ) except: pass newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # previewsToPreviews #----------------------------------------------------------------------------------------------------------- previewsInsertSql = """ insert into previews (id, addon_id, filedata, filetype, thumbdata, thumbtype, caption, highlight, created) values (%s, %s, %s, %s, %s, %s, %s, %s, %s)""" def previewsToPreviews (oldDB, newDB, workingEnvironment): if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning previewsToPreviews..." % mx.DateTime.now() if "all" in workingEnvironment["addons"]: sql = "select * from previews" else: sql = "select * from previews where id in (select id from addonSelections)" for p in oldDB.executeSqlNoCache(sql): contentType = thumbType = "" content = thumbContent = None if 'noPreviewImageProcessing' not in workingEnvironment: try: content, contentType, thumbContent, thumbType = imageAndThumbFromURL("%s%s" % (workingEnvironment["previewURIPrefix"], p.PreviewURI)) except Exception, x: print >>standardError, "%s\tWARNING -- Error downloading %s%s for addon_id %d, preview.id %s - %s" % (mx.DateTime.now(), workingEnvironment["previewURIPrefix"], p.PreviewURI, p.ID, p.PreviewID, x) newDB.executeManySql(previewsInsertSql, [ (p.PreviewID, #id p.ID, #addon_id content, #filedata contentType, #filetype thumbContent, #thumbdata thumbType, #thumbtype addTranslation(newDB, p.caption, workingEnvironment["locale"]), #caption yesNoEnumToTinyIntMappingForHighlight[p.preview], #caption mx.DateTime.now()) #created ] ) newDB.commit() if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # setupAddonSelectionTable #----------------------------------------------------------------------------------------------------------- def setupAddonSelectionTable(oldDB, newDB, workingEnvironment): if "all" in workingEnvironment["addons"]: return if "verbose" in workingEnvironment: print >>standardError, "%s\tbeginning setupAddonSelectionTable..." % mx.DateTime.now() cleanupAddonSelectionTable(oldDB, newDB, workingEnvironment) oldDB.executeSql("create table addonSelections (id int, primary key (id))") newDB.executeSql("create table addonSelections (id int, primary key (id))") try: listOfAddons = [x.strip() for x in workingEnvironment["addons"].split(",")] if "not" in workingEnvironment: for anAddon in oldDB.executeSqlNoCache("select ID from main"): if str(anAddon.ID) not in listOfAddons: oldDB.executeSql("insert into addonSelections (id) values (%d)" % anAddon.ID) newDB.executeSql("insert into addonSelections (id) values (%d)" % anAddon.ID) else: oldDB.executeManySql("insert into addonSelections (id) values (%s)", listOfAddons) newDB.executeManySql("insert into addonSelections (id) values (%s)", listOfAddons) except Exception, x: cleanupAddonSelectionTable(oldDB, newDB, workingEnvironment) raise x if "verbose" in workingEnvironment: print >>standardError, "\t\t\tdone." #----------------------------------------------------------------------------------------------------------- # cleanupAddonSelectionTable #----------------------------------------------------------------------------------------------------------- def cleanupAddonSelectionTable (oldDB, newDB, workingEnvironment): if "all" in workingEnvironment["addons"]: return oldDB.executeSql("drop table if exists addonSelections") newDB.executeSql("drop table if exists addonSelections") #----------------------------------------------------------------------------------------------------------- # cleanMetaDataTables_test #----------------------------------------------------------------------------------------------------------- def cleanMetaDataTables_test(newDB, workingEnvironment): print >>standardError, "cleanMetaDataTables_test" errorCount = 0 try: cleanAddonsTables(newDB, workingEnvironment) cleanMetaDataTables(newDB, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** cleanMetaDataTables: %s" % x errorCount += 1 try: if newDB.singleValueSql("select count(*) from tags") != 0: print >>standardError, " ***Failure*** cleanMetaDataTables: tags table not cleared" errorCount += 1 if newDB.singleValueSql("select count(*) from appversions") != 0: print >>standardError, " ***Failure*** cleanMetaDataTables: appversions table not cleared" errorCount += 1 if newDB.singleValueSql("select count(*) from applications") != 0: print >>standardError, " ***Failure*** cleanMetaDataTables: applications table not cleared" errorCount += 1 if newDB.singleValueSql("select count(*) from platforms") != 0: print >>standardError, " ***Failure*** cleanMetaDataTables: platforms table not cleared" errorCount += 1 if newDB.singleValueSql("select count(*) from addons_users") != 0: print >>standardError, " ***Failure*** cleanMetaDataTables: addons_users table not cleared" errorCount += 1 if newDB.singleValueSql("select count(*) from users") != 0: print >>standardError, " ***Failure*** cleanMetaDataTables: users table not cleared" errorCount += 1 #if newDB.singleValueSql("select count(*) from addontypes") != 2: # print >>standardError, " ***Failure*** cleanMetaDataTables: addontypes not initialized properly" # errorCount += 1 except Exception, x: print >>standardError, " ***Failure*** cleanMetaDataTables: Testing system failure: %s" % x print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # applicationsToApplications_test #----------------------------------------------------------------------------------------------------------- def applicationsToApplications_test(oldDB, newDB, workingEnvironment): print >>standardError, "applicationsToApplications_test" errorCount = 0 try: cleanMetaDataTables(newDB, workingEnvironment) applicationsToApplications(oldDB, newDB, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** applicationsToApplications: %s" % x errorCount += 1 try: for anApplication in oldDB.executeSqlNoCache("select distinct AppName from applications"): if newDB.singleValueSql("select count(*) from applications a join translations t on a.name = t.id and t.locale = 'en-US' where t.localized_string = '%s'" % anApplication.AppName) != 1: print >>standardError, " ***Failure*** applicationsToApplications: %s not in new database" % anApplication.AppName errorCount += 1 for anApplication in oldDB.executeSqlNoCache("select * from applications"): sql = """ select count(*) from applications a join appversions av on a.id = av.application_id where av.id = %d""" % anApplication.AppID result = newDB.executeSql(sql) if len(result.content) == 0: print >>standardError, " ***Failure*** applicationsToApplications: %s - %s %s not in new database" % (anApplication.AppID, anApplication.AppName, anApplication.Version) errorCount += 1 if len(result.content) > 1: print >>standardError, " ***Failure*** applicationsToApplications: %s - %s %s duplicated in new database" % (anApplication.AppID, anApplication.AppName, anApplication.Version) errorCount += 1 except Exception, x: print >>standardError, " ***Failure*** applicationsToApplications: Testing system failure: %s" % x print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # categoriesToTags_test #----------------------------------------------------------------------------------------------------------- def categoriesToTags_test(oldDB, newDB, workingEnvironment): print >>standardError, "categoriesToTags_test" errorCount = 0 try: cleanMetaDataTables(newDB, workingEnvironment) applicationsToApplications(oldDB, newDB, workingEnvironment) categoriesToTags(oldDB, newDB, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** categoriesToTags: %s" % x errorCount += 1 try: for x in zip(oldDB.executeSql("select * from categories order by CategoryID"), newDB.executeSql("select * from tags order by id")): if (x[0].CategoryID != x[1].id): print >>standardError, " ***Failure*** categoriesToTags: %s - %s missing in new database" % (x[0].CategoryID, x[0].CatName) errorCount += 1 break except Exception, x: print >>standardError, " ***Failure*** categoriesToTags: Testing system failure: %s" % x print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # osToPlatforms_test #----------------------------------------------------------------------------------------------------------- def osToPlatforms_test(oldDB, newDB, workingEnvironment): print >>standardError, "osToPlatforms_test" errorCount = 0 try: cleanMetaDataTables(newDB, workingEnvironment) osToPlatforms(oldDB, newDB, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** osToPlatforms: %s" % x errorCount += 1 try: for x in zip(oldDB.executeSql("select * from os order by OSID"), newDB.executeSql("select * from platforms order by id")): if (x[0].OSID != x[1].id): print >>standardError, " ***Failure*** osToPlatforms: %s - %s missing in new database" % (x[0].OSID, x[0].OSName) errorCount += 1 break except Exception, x: print >>standardError, " ***Failure*** osToPlatforms: Testing system failure: %s" % x print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # userprofilesToUsers_test #----------------------------------------------------------------------------------------------------------- def userprofilesToUsers_test(oldDB, newDB, workingEnvironment): print >>standardError, "userprofilesToUsers_test" errorCount = 0 try: cleanMetaDataTables(newDB, workingEnvironment) userprofilesToUsers(oldDB, newDB, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** userprofilesToUsers: %s" % x errorCount += 1 try: for x in zip(oldDB.executeSql("select * from userprofiles order by UserID"), newDB.executeSql("select * from users order by id")): if (x[0].UserID != x[1].id): print >>standardError, " ***Failure*** userprofilesToUsers: %s - %s missing in new database" % (x[0].UserID, x[0].UserName) errorCount += 1 break except Exception, x: print >>standardError, " ***Failure*** userprofilesToUsers: Testing system failure: %s" % x print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # cleanAddonsTables_test #----------------------------------------------------------------------------------------------------------- def cleanAddonsTables_test(newDB, workingEnvironment): print >>standardError, 'cleanAddonsTables_test ("all" phase)' errorCount = 0 theTables = [ "previews", "addons_tags", "applications_versions", "files", "versions", "addons_users", "addons" ] try: workingEnvironment["addons"] = "all" cleanAddonsTables(newDB, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** cleanAddonsTables: %s" % x errorCount += 1 try: for aTableName in theTables: if newDB.singleValueSql("select count(*) from %s" % aTableName) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: %s table not cleared" % aTableName errorCount += 1 except Exception, x: print >>standardError, ' ***Failure*** cleanAddonsTables: Testing system failure during "all" phase: %s' % x print >>standardError, " %d errors" % errorCount print >>standardError, 'cleanAddonsTables_test ("subset" phase)' errorCount = 0 try: workingEnvironment["addons"] = "all" cleanAddonsTables(newDB, workingEnvironment) cleanMetaDataTables(newDB, workingEnvironment) applicationsToApplications (oldDatabase, newDatabase, workingEnvironment) categoriesToTags (oldDatabase, newDatabase, workingEnvironment) osToPlatforms (oldDatabase, newDatabase, workingEnvironment) userprofilesToUsers (oldDatabase, newDatabase, workingEnvironment) mainToAddOns (oldDatabase, newDatabase, workingEnvironment) authorxrefToAddons_users (oldDatabase, newDatabase, workingEnvironment) versionToVerions (oldDatabase, newDatabase, workingEnvironment) categoryxrefToAddons_tags (oldDatabase, newDatabase, workingEnvironment) previewsToPreviews (oldDatabase, newDatabase, workingEnvironment) delimitedListOfAddons = workingEnvironment["addons"] = "3110,3112,3115" delimitedListOfVersionIdsThatShouldBeDeleted = ",".join((str(x.id) for x in newDB.executeSql("select v.id from versions v where addon_id in (%s)" % delimitedListOfAddons))) setupAddonSelectionTable(oldDatabase, newDatabase, workingEnvironment) try: cleanAddonsTables(newDB, workingEnvironment) finally: cleanupAddonSelectionTable (oldDatabase, newDatabase, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** cleanAddonsTables: %s" % x errorCount += 1 try: if newDB.singleValueSql("select count(*) from addons where id in (%s)" % delimitedListOfAddons) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: one or more of deleted addon_id values %s found in addons table" % delimitedListOfAddons errorCount += 1 if newDB.singleValueSql("select count(*) from versions where addon_id in (%s)" % delimitedListOfAddons) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: one or more of deleted addon_id values %s found in versions table" % delimitedListOfAddons errorCount += 1 if newDB.singleValueSql("select count(*) from applications_versions where version_id in (%s)" % delimitedListOfVersionIdsThatShouldBeDeleted) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: one or more of deleted version values %s found in applications_versions table" % delimitedListOfVersionIdsThatShouldBeDeleted errorCount += 1 if newDB.singleValueSql("select count(*) from files where version_id in (%s)" % delimitedListOfVersionIdsThatShouldBeDeleted) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: one or more of deleted version values %s found in files table" % delimitedListOfVersionIdsThatShouldBeDeleted errorCount += 1 if newDB.singleValueSql("select count(*) from addons_tags where addon_id in (%s)" % delimitedListOfAddons) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: one or more of deleted addon_id values %s found in addons_tags table" % delimitedListOfAddons errorCount += 1 if newDB.singleValueSql("select count(*) from previews where addon_id in (%s)" % delimitedListOfAddons) != 0: print >>standardError, " ***Failure*** cleanAddonsTables: one or more of deleted addon_id values %s found in previews table" % delimitedListOfAddons errorCount += 1 except Exception, x: print >>standardError, ' ***Failure*** cleanAddonsTables: Testing system failure during "subset" phase: %s' % x traceback.print_exc(file=standardError) print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # mainToAddOnsTables_test #----------------------------------------------------------------------------------------------------------- def mainToAddOnsTables_test (oldDB, newDB, workingEnvironment): print >>standardError, 'mainToAddOns_test ("all" phase)' errorCount = 0 theTables = [ "previews", "addons_tags", "applications_versions", "files", "versions", "addons_users", "addons" ] try: workingEnvironment["addons"] = "all" cleanAddonsTables(newDB, workingEnvironment) cleanMetaDataTables(newDB, workingEnvironment) applicationsToApplications (oldDatabase, newDatabase, workingEnvironment) categoriesToTags (oldDatabase, newDatabase, workingEnvironment) osToPlatforms (oldDatabase, newDatabase, workingEnvironment) userprofilesToUsers (oldDatabase, newDatabase, workingEnvironment) mainToAddOns (oldDatabase, newDatabase, workingEnvironment) authorxrefToAddons_users (oldDatabase, newDatabase, workingEnvironment) versionToVerions (oldDatabase, newDatabase, workingEnvironment) categoryxrefToAddons_tags (oldDatabase, newDatabase, workingEnvironment) previewsToPreviews (oldDatabase, newDatabase, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** mainToAddOns: %s" % x errorCount += 1 try: # check addons Table for anAddonFromOldDB in oldDB.executeSql("select * from main"): try: newDB.executeSql("select * from addons where id = %d" % anAddonFromOldDB.ID).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: addon %d missing from new database "all" phase' % anAddonFromOldDB.ID errorCount += 1 # check addons_users Table for anAuthorxrefFromOldDB in oldDB.executeSql("select * from authorxref"): try: newDB.executeSql("select * from addons_users where addon_id = %d and user_id = %d" % (anAuthorxrefFromOldDB.ID, anAuthorxrefFromOldDB.UserID)).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: addons_users %d, %d missing from new database "all" phase' % (anAuthorxrefFromOldDB.ID, anAuthorxrefFromOldDB.UserID) errorCount += 1 # check versions, files, applications_versions tables ##TESTED ONLY IN SUBSET PHASE## #for aVersionFromOldDB in oldDB.executeSql("select * from version"): # try: # aVersionFromNewDB = newDB.executeSql("select * from versions where id = %d" % aVersionFromOldDB.vID).__iter__().next() # except StopIteration: # print >>standardError, ' ***Failure*** mainToAddOns: versions %d missing from new database "all" phase' % aVersionFromOldDB.vID # errorCount += 1 # try: # newDB.executeSql("select * from applications_versions where application_id = %d and version_id = %d" % (aVersionFromOldDB.AppID, aVersionFromOldDB.vID)).__iter__().next() # except StopIteration: # print >>standardError, ' ***Failure*** mainToAddOns: applications_versions %d, %d missing from new database "all" phase' % (aVersionFromOldDB.AppID, aVersionFromOldDB.vID) # errorCount += 1 # try: # newDB.executeSql("select * from files where version_id = %d" % aVersionFromOldDB.vID).__iter__().next() # except StopIteration: # print >>standardError, ' ***Failure*** mainToAddOns: files entry for version %d missing from new database "all" phase' % aVersionFromOldDB.vID # errorCount += 1 # check categoryxref Table for aCategoryxrefFromOldDB in oldDB.executeSql("select * from categoryxref"): try: newDB.executeSql("select * from addons_tags where addon_id = %d and tag_id = %d" % (aCategoryxrefFromOldDB.ID, aCategoryxrefFromOldDB.CategoryID)).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: addons_tags %d, %d missing from new database "all" phase' % (aCategoryxrefFromOldDB.ID, aCategoryxrefFromOldDB.CategoryID) errorCount += 1 # check previews Table for aPreviewsFromOldDB in oldDB.executeSql("select * from previews"): try: newDB.executeSql("select * from previews where id = %d " % aPreviewsFromOldDB.PreviewID).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: previews %d missing from new database "all" phase' % aPreviewsFromOldDB.PreviewID errorCount += 1 except Exception, x: print >>standardError, ' ***Failure*** mainToAddOns: Testing system failure during "all" phase: %s' % x print >>standardError, " %d errors" % errorCount print >>standardError, 'mainToAddOns_test ("subset" phase)' errorCount = 0 try: workingEnvironment["addons"] = "all" cleanAddonsTables(newDB, workingEnvironment) cleanMetaDataTables(newDB, workingEnvironment) applicationsToApplications (oldDatabase, newDatabase, workingEnvironment) categoriesToTags (oldDatabase, newDatabase, workingEnvironment) osToPlatforms (oldDatabase, newDatabase, workingEnvironment) userprofilesToUsers (oldDatabase, newDatabase, workingEnvironment) delimitedListOfAddons = workingEnvironment["addons"] = "3110,3112,3115" delimitedListOfVersionIdsThatShouldBeDeleted = ",".join((str(x.id) for x in newDB.executeSql("select v.id from versions v where addon_id in (%s)" % delimitedListOfAddons))) setupAddonSelectionTable(oldDatabase, newDatabase, workingEnvironment) try: mainToAddOns (oldDatabase, newDatabase, workingEnvironment) authorxrefToAddons_users (oldDatabase, newDatabase, workingEnvironment) versionToVerions (oldDatabase, newDatabase, workingEnvironment) categoryxrefToAddons_tags (oldDatabase, newDatabase, workingEnvironment) previewsToPreviews (oldDatabase, newDatabase, workingEnvironment) finally: cleanupAddonSelectionTable (oldDatabase, newDatabase, workingEnvironment) except Exception, x: print >>standardError, " ***Failure*** mainToAddOns: %s" % x errorCount += 1 try: # check addons Table for anAddonFromOldDB in oldDB.executeSql("select * from main where id in (%s)" % delimitedListOfAddons): try: newDB.executeSql("select * from addons where id = %d" % anAddonFromOldDB.ID).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: addon %d missing from new database "subset" phase' % anAddonFromOldDB.ID errorCount += 1 # check addons_users Table for anAuthorxrefFromOldDB in oldDB.executeSql("select * from authorxref where id in (%s)" % delimitedListOfAddons): try: newDB.executeSql("select * from addons_users where addon_id = %d and user_id = %d" % (anAuthorxrefFromOldDB.ID, anAuthorxrefFromOldDB.UserID)).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: addons_users %d, %d missing from new database "subset" phase' % (anAuthorxrefFromOldDB.ID, anAuthorxrefFromOldDB.UserID) errorCount += 1 # check versions, files, applications_versions tables for aVersionFromOldDB in oldDB.executeSql("select * from version where id in (%s)" % delimitedListOfAddons): try: aVersionFromNewDB = newDB.executeSql("select * from versions where id = %d" % aVersionFromOldDB.vID).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: versions %d missing from new database "subset" phase' % aVersionFromOldDB.vID errorCount += 1 try: newDB.executeSql("select * from applications_versions where application_id = %d and version_id = %d" % (aVersionFromOldDB.AppID, aVersionFromOldDB.vID)).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: applications_versions %d, %d missing from new database "subset" phase' % (aVersionFromOldDB.AppID, aVersionFromOldDB.vID) errorCount += 1 try: newDB.executeSql("select * from files where version_id = %d" % aVersionFromOldDB.vID).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: files entry for version %d missing from new database "subset" phase' % aVersionFromOldDB.vID errorCount += 1 # check categoryxref Table for aCategoryxrefFromOldDB in oldDB.executeSql("select * from categoryxref where id in (%s)" % delimitedListOfAddons): try: newDB.executeSql("select * from addons_tags where addon_id = %d and tag_id = %d" % (aCategoryxrefFromOldDB.ID, aCategoryxrefFromOldDB.CategoryID)).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: addons_tags %d, %d missing from new database "all" phase' % (aCategoryxrefFromOldDB.ID, aCategoryxrefFromOldDB.CategoryID) errorCount += 1 # check previews Table for aPreviewsFromOldDB in oldDB.executeSql("select * from previews where id in (%s)" % delimitedListOfAddons): try: newDB.executeSql("select * from previews where id = %d " % aPreviewsFromOldDB.PreviewID).__iter__().next() except StopIteration: print >>standardError, ' ***Failure*** mainToAddOns: previews %d missing from new database "all" phase' % aPreviewsFromOldDB.PreviewID errorCount += 1 except Exception, x: print >>standardError, ' ***Failure*** mainToAddOns: Testing system failure during "subset" phase: %s' % x traceback.print_exc(file=standardError) print >>standardError, " %d errors" % errorCount #----------------------------------------------------------------------------------------------------------- # runTests #----------------------------------------------------------------------------------------------------------- def runTests (oldDB, newDB, workingEnvironment): cleanMetaDataTables_test(newDB, workingEnvironment) applicationsToApplications_test(oldDB, newDB, workingEnvironment) categoriesToTags_test(oldDB, newDB, workingEnvironment) osToPlatforms_test(oldDB, newDB, workingEnvironment) userprofilesToUsers_test(oldDB, newDB, workingEnvironment) cleanAddonsTables_test(newDB, workingEnvironment) mainToAddOnsTables_test(oldDB, newDB, workingEnvironment) #----------------------------------------------------------------------------------------------------------- # addBogusTranslations #----------------------------------------------------------------------------------------------------------- def addBogusTranslations (newDB): counter = 1 for aTranslationIDRow in newDB.executeSqlNoCache("select distinct id from translations"): for aLocale in ['de', 'ru', 'nl']: newDB.executeManySql("insert into translations (id, locale, localized_string) values (%s, %s, %s)", [(aTranslationIDRow.id, aLocale, "bogus translation #%06d" % counter)]) newDB.commit() counter += 1 #----------------------------------------------------------------------------------------------------------- # removeBogusTranslations #----------------------------------------------------------------------------------------------------------- def removeBogusTranslations (newDB): newDB.executeSql ("delete from translations where localized_string like 'bogus translation #%'") newDB.commit() #=========================================================================================================== # main #=========================================================================================================== if __name__ == "__main__": import cse.ConfigurationManager try: options = [ ('?', 'help', False, None, 'print this message'), ('c', 'config', True, './migration.conf', 'specify the location and name of the config file'), (None, 'oldDatabaseName', True, "", 'the name of the old database within the server'), (None, 'oldServerName', True, "", 'the name of the old database server'), (None, 'oldUserName', True, "", 'the name of the user in the old database'), (None, 'oldPassword', True, "", 'the password for the user in the old database'), (None, 'newDatabaseName', True, "", 'the name of the new database within the server'), (None, 'newServerName', True, "", 'the name of the new database server'), (None, 'newUserName', True, "", 'the name of the user in the new database'), (None, 'newPassword', True, "", 'the password for the user in the new database'), ('a', 'addons', True, "all", 'a quoted comma delimited list of the ids of addons OR the word "all"'), ('n', 'not', False, None, """reverses the meaning of the "addon" option. If the "addon" option has a list, then specify everything except what's on the list"""), ('M', 'migrateMetaData', False, None, 'if present, this switch causes the metadata tables to be migrated'), ('A', 'migrateAddons', False, None, 'if present, this switch causes the addons in the "addons" option list to be migrated'), ('v', 'verbose', False, None, 'print status information as it runs to stderr'), ('l', 'locale', True, "en-US", 'set the locale for addons being migrated'), (None, 'clear', False, None, 'clear all exisiting information from the new database'), (None, 'clearAddons', False, None, 'clear all exisiting addons in the "addons" option list information from the new database'), (None, 'test', False, None, 'run the test cases'), (None, 'show', False, None, 'echo the configuration to stdout and then quit'), (None, 'bogusTranslationsInsert', False, None, 'fill out the translations table with a full set of locales for all entries for load testing'), (None, 'bogusTranslationsRemove', False, None, 'remove any bogus translations from the translations table'), (None, 'logPathName', True, "./migration.log", 'a progressive log of all runs of the migration script'), (None, 'addonStatusWhenNoFilesApproved', True, "sandbox", 'the status to set an addon if no files approved (null, sandbox, pending, nominated, public, disabled)'), (None, 'addonStatusWhenSomeFilesApproved', True, "public", 'the status to set an addon if some files approved (null, sandbox, pending, nominated, public, disabled)'), (None, 'noPreviewImageProcessing', False, "", 'do not process preview images'), (None, 'previewURIPrefix', True, "https://addons.mozilla.org", 'a prefix to add to the URI in the preview table to enable downloading'), (None, 'fileCachePath', True, "", 'a path for the caching of files (blank for no cache)'), (None, 'recalculateHash', False, "", 'force the recaclulation of the hash for the xpi files'), (None, 'ignoreHash', False, "", 'no not allow the migration to touch the hash'), ] workingEnvironment = cse.ConfigurationManager.ConfigurationManager(options) #workingEnvironment["version"] = version if 'help' in workingEnvironment: print >>standardError, "migration %s\nThis routine migrates data from the old AMO database schema to the new one." % version workingEnvironment.outputCommandSummary(standardError, 1) sys.exit() if 'show' in workingEnvironment: workingEnvironment.output(sys.stdout) sys.exit() except cse.ConfigurationManager.ConfigurationManagerNotAnOption, x: print >>standardError, "m1 %s\n%s\nFor usage, try --help" % (version, x) sys.exit() try: useLogFile = open(workingEnvironment["logPathName"], "a") print >>useLogFile, mx.DateTime.now() workingEnvironment.output(useLogFile) useLogFile.close() if "verbose" in workingEnvironment: print >>standardError, "%s beginning migration version %s with options:" % (mx.DateTime.now(), version) workingEnvironment.output(standardError) validStatusStates = {"null": 0, "sandbox": 1, "pending": 2, "nominated": 3, "public": 4, "disabled": 5} try: workingEnvironment["addonStatusWhenNoFilesApproved"] = validStatusStates[workingEnvironment["addonStatusWhenNoFilesApproved"]] except KeyError: print >>standardError, "%s is not a valid value for addonStatusWhenNoFilesApproved. See --help" % workingEnvironment["addonStatusWhenNoFilesApproved"] sys.exit() try: workingEnvironment["addonStatusWhenSomeFilesApproved"] = validStatusStates[workingEnvironment["addonStatusWhenSomeFilesApproved"]] except KeyError: print >>standardError, "%s is not a valid value for addonStatusWhenSomeFilesApproved. See --help" % workingEnvironment["addonStatusWhenSomeFilesApproved"] sys.exit() oldDatabase = cse.MySQLDatabase.MySQLDatabase(workingEnvironment["oldDatabaseName"], workingEnvironment["oldServerName"], workingEnvironment["oldUserName"], workingEnvironment["oldPassword"]) newDatabase = cse.MySQLDatabase.MySQLDatabase(workingEnvironment["newDatabaseName"], workingEnvironment["newServerName"], workingEnvironment["newUserName"], workingEnvironment["newPassword"]) if "test" in workingEnvironment: runTests(oldDatabase, newDatabase, workingEnvironment) else: setupAddonSelectionTable(oldDatabase, newDatabase, workingEnvironment) try: if "clear" in workingEnvironment: originalAddonsOption = workingEnvironment["addons"] workingEnvironment["addons"] = "all" cleanAddonsTables(newDatabase, workingEnvironment) cleanMetaDataTables(newDatabase, workingEnvironment) workingEnvironment["addons"] = originalAddonsOption elif "clearAddons" in workingEnvironment: cleanAddonsTables(newDatabase, workingEnvironment) if "migrateMetaData" in workingEnvironment: if "verbose" in workingEnvironment: print >>standardError, "%s beginning metadata migration" % mx.DateTime.now() try: cleanMetaDataTables (newDatabase, workingEnvironment) except: raise MigrationException("Migrating MetaData implies clearing MetaData tables, but there appears to be Addon data in the database. Clear the Addon data before trying to migrate MetaData.") applicationsToApplications (oldDatabase, newDatabase, workingEnvironment) categoriesToTags (oldDatabase, newDatabase, workingEnvironment) osToPlatforms (oldDatabase, newDatabase, workingEnvironment) userprofilesToUsers (oldDatabase, newDatabase, workingEnvironment) if "migrateAddons" in workingEnvironment: if "verbose" in workingEnvironment: print >>standardError, "%s beginning addons migration" % mx.DateTime.now() cleanAddonsTables (newDatabase, workingEnvironment) mainToAddOns (oldDatabase, newDatabase, workingEnvironment) categoryxrefToAddons_tags (oldDatabase, newDatabase, workingEnvironment) authorxrefToAddons_users (oldDatabase, newDatabase, workingEnvironment) versionToVerions (oldDatabase, newDatabase, workingEnvironment) #categoryxrefToAddons_tags (oldDatabase, newDatabase, workingEnvironment) previewsToPreviews (oldDatabase, newDatabase, workingEnvironment) if "bogusTranslationsInsert" in workingEnvironment: if "verbose" in workingEnvironment: print >>standardError, "%s creating bogus translations" % mx.DateTime.now() addBogusTranslations(newDatabase) if "bogusTranslationsRemove" in workingEnvironment: if "verbose" in workingEnvironment: print >>standardError, "%s removing bogus translations" % mx.DateTime.now() removeBogusTranslations(newDatabase) finally: cleanupAddonSelectionTable (oldDatabase, newDatabase, workingEnvironment) except KeyboardInterrupt: print >>standardError, "Interrupted..." pass except MigrationException, x: print >>standardError, x except Exception, x: print >>standardError, x traceback.print_exc(file=standardError) if "verbose" in workingEnvironment: print >>standardError, "done."