def __init__(self, backup=False): self.project_dbupgrade = dbupgrade.DBUpgradeTools('projectdata', drop=True) self.leginon_dbupgrade = dbupgrade.DBUpgradeTools('leginondata', drop=True) self.updatelib = updatelib.UpdateLib(self.project_dbupgrade) self.selected_revision = self.getSchemaRevision() self.backup = backup self.valid_upgrade = ['leginon', 'project', 'appion'] self.required_upgrade = self.valid_upgrade self.excluded_appiondbs = [] self.setForceUpdate(False)
def run(self): if not self.required_upgrade: print "\033[31mNothing to do\033[0m" return divider = "-------------------------------------------" checkout_revision = self.updatelib.getCheckOutRevision() revision_in_database = self.updatelib.getDatabaseRevision() if self.updatelib.needUpdate(checkout_revision, self.selected_revision, self.force) == 'now': try: if 'leginon' in self.required_upgrade: # leginon part print divider if self.backup: self.leginon_dbupgrade.backupDatabase("leginondb.sql", data=True) print "\033[35mUpgrading %s\033[0m" % ( self.leginon_dbupgrade.getDatabaseName()) self.upgradeLeginonDB() if 'project' in self.required_upgrade: # project part print divider if self.backup: self.leginon_dbupgrade.backupDatabase("leginondb.sql", data=True) print "\033[35mUpgrading %s\033[0m" % ( self.project_dbupgrade.getDatabaseName()) self.upgradeProjectDB() if 'appion' in self.required_upgrade: # appion part print divider appiondblist = self.getAppionDatabases( self.project_dbupgrade) if self.backup: self.appionbackup(appiondblist) for appiondbname in appiondblist: if self.inExcluded_AppionDBList(appiondbname): print "\033[31mSkipping database %s\033[0m" % ( appiondbname) time.sleep(1) continue if not self.project_dbupgrade.databaseExists( appiondbname): print "\033[31merror database %s does not exist\033[0m" % ( appiondbname) time.sleep(1) continue self.appion_dbupgrade = dbupgrade.DBUpgradeTools( 'appiondata', appiondbname, drop=True) print "\033[35mUpgrading %s\033[0m" % ( self.appion_dbupgrade.getDatabaseName()) self.upgradeAppionDB() except: print "\033[31mUpdate failed\033[0m" raise print divider print "\033[35mSuccessful Update\033[0m" self.commitUpdate()
def appionbackup(self, appiondblist): appiondb_unique_list = list(set(appiondblist)) for appiondbname in appiondb_unique_list: if not self.project_dbupgrade.databaseExists(appiondbname): print "\033[31merror database %s does not exist\033[0m" % ( appiondbname) time.sleep(1) continue appion_dbupgrade = dbupgrade.DBUpgradeTools('appiondata', appiondbname, drop=True) appion_dbupgrade.backupDatabase("%s.sql" % (appiondbname), data=True)
def run(self): divider = "-------------------------------------------" try: print divider self.scanLeginonDB() print divider self.scanProjectDB() # appion part print divider appiondblist = self.getAppionDatabases(self.project_dbtools) for appiondbname in appiondblist: if not self.project_dbtools.databaseExists(appiondbname): print "\033[31merror database %s does not exist\033[0m" % ( appiondbname) time.sleep(1) continue self.appion_dbtools = dbupgrade.DBUpgradeTools('appiondata', appiondbname, drop=True) self.scanAppionDB() except: print "\033[31mUpdate failed\033[0m" raise
def __init__(self): self.project_dbtools = dbupgrade.DBUpgradeTools('projectdata') self.leginon_dbtools = dbupgrade.DBUpgradeTools('leginondata')
#!/usr/bin/env python from sinedon import dbupgrade, dbconfig import updatelib project_dbupgrade = dbupgrade.DBUpgradeTools('projectdata', drop=True) if __name__ == "__main__": updatelib_inst = updatelib.UpdateLib(project_dbupgrade) checkout_version = raw_input('Revert to checkout version, for example, 2.1 -->') if checkout_version != 'trunk': try: map((lambda x:int(x)),checkout_version.split('.')[:2]) except: print "valid versions are 'trunk', '2.1', or '2.1.2' etc" raise checkout_revision = int(raw_input('Revert to checkout revision, for example, 16500 -->')) updatelib_inst.updateDatabaseVersion(checkout_version) print "\033[35mVersion Updated in the database %s\033[0m" % checkout_version updatelib_inst.updateDatabaseRevision(checkout_revision) print "\033[35mRevision Updated in the database as %d\033[0m" % checkout_revision
import sys from sinedon import dbupgrade, dbconfig from leginon import projectdata, leginondata # This will update databases that were archived with auto_increment off. excluding_tablenames = ['ImportMappingData','ImportDBConfigData'] def activateAutoIncrement(database): q = 'Show Tables;' r = database.returnCustomSQL(q) if not r: print "No archive to be deactivate" return if r[0] == 'AcquisitionFFTData': print "Acting on original data. STOP!!!!!" raw_input('kill this!!!!') return for tablenametuple in r: tablename = tablenametuple[0] if tablename in excluding_tablenames: continue if database.columnExists(tablename, 'DEF_id'): q = 'ALTER TABLE `%s` CHANGE `DEF_id` `DEF_id` INT(16) NOT NULL;' % (tablename,) database.executeCustomSQL(q) if __name__ == "__main__": projectdb = dbupgrade.DBUpgradeTools('projectdata') activateAutoIncrement(projectdb) leginondb = dbupgrade.DBUpgradeTools('leginondata') activateAutoIncrement(leginondb)
#!/usr/bin/env python import sys from sinedon import dbupgrade, dbconfig from leginon import projectdata, leginondata # This will update databases that were installed prior to r12857. # The tables that will be affected are in the dbemdata database and the project database. # Migrate the user data from project to dbemdata because dbemdata is already in Sinedon format. # The tables affected are dbemdata[GroupData, UserData] # and project[users,login,pis,userdetails,projectowner] if __name__ == "__main__": projectdb = dbupgrade.DBUpgradeTools('projectdata', drop=True) leginondb = dbupgrade.DBUpgradeTools('leginondata', drop=False) print "\nWould you like to back up the database to local file before upgrading?" answer = raw_input('Yes/No (default=Yes): ') if not answer.lower().startswith('n'): leginondb.backupDatabase("leginondb.sql", data=True) projectdb.backupDatabase("projectdb.sql", data=True) #=================== # leginon table # update from Anchi which occured in revison 12330 #=================== if leginondb.columnExists('AcquisitionImageTargetData', 'delta row'): leginondb.changeColumnDefinition('AcquisitionImageTargetData', 'delta row', leginondb.float) leginondb.changeColumnDefinition('AcquisitionImageTargetData',
def upgradeAppionDB(appiondbname, projectdb, backup=True): print "" print "========================" print "Upgrading appion database: " + appiondbname time.sleep(0.1) appiondb = dbupgrade.DBUpgradeTools('appiondata', appiondbname, drop=True) if backup: appiondb.backupDatabase(appiondbname + ".sql", data=True) #=================== # rename tables: when renaming table you must rename all columns linking to that table #=================== ### refinement tables appiondb.renameTable('ApRefinementData', 'ApRefineIterData', updaterefs=False) appiondb.renameTable('ApRefinementRunData', 'ApRefineRunData', updaterefs=False) appiondb.renameTable('ApParticleClassificationData', 'ApRefineParticleData', updaterefs=False) appiondb.renameTable('ApRefinementParamsData', 'ApEmanRefineIterData', updaterefs=False) ### plural particles tables appiondb.renameTable('ApStackParticlesData', 'ApStackParticleData', updaterefs=False) appiondb.renameTable('ApAlignParticlesData', 'ApAlignParticleData', updaterefs=False) appiondb.renameTable('ApClusteringParticlesData', 'ApClusteringParticleData', updaterefs=False) ### no long required to be on a cluster appiondb.renameTable('ApClusterJobData', 'ApAppionJobData', updaterefs=False) #=================== # rename columns: #=================== ### refinement tables appiondb.renameColumn('ApRefineIterData', 'REF|ApRefinementParamsData|refinementParams', 'REF|ApEmanRefineIterData|emanParams') appiondb.renameColumn('ApRefineIterData', 'REF|ApRefinementRunData|refinementRun', 'REF|ApRefineRunData|refineRun') appiondb.renameColumn('ApRefineRunData', 'name', 'runname') appiondb.renameColumn('ApEulerJumpData', 'REF|ApRefinementRunData|refRun', 'REF|ApRefineRunData|refineRun') appiondb.renameColumn('Ap3dDensityData', 'REF|ApRefinementData|iterid', 'REF|ApRefineIterData|refineIter') appiondb.renameColumn('ApFSCData', 'REF|ApRefinementData|refinementData', 'REF|ApRefineIterData|refineIter') appiondb.renameColumn('ApTiltsInAlignRunData', 'primary', 'primary_tiltseries') ### special case of conflicting Xmipp columns if (appiondb.columnExists( 'ApRefineIterData', 'REF|ApXmippRefineIterationParamsData|xmippRefineParams') and appiondb.columnExists( 'ApRefineIterData', 'REF|ApXmippRefineIterData|xmippParams')): appiondb.dropColumn( 'ApRefineIterData', 'REF|ApXmippRefineIterationParamsData|xmippRefineParams') else: appiondb.renameColumn( 'ApRefineIterData', 'REF|ApXmippRefineIterationParamsData|xmippRefineParams', 'REF|ApXmippRefineIterData|xmippParams') #=================== # move columns to new table #=================== """ 3 steps: (1) Create new column in destination table (2) Run update query to insert source data into destination column (3) Remove old column from source table """ ### Symmetry, Mask, Imask if (appiondb.columnExists('ApEmanRefineIterData', 'REF|ApSymmetryData|symmetry') and appiondb.columnExists('ApEmanRefineIterData', 'mask') and appiondb.columnExists('ApEmanRefineIterData', 'imask')): appiondb.addColumn('ApRefineIterData', 'REF|ApSymmetryData|symmetry', appiondb.link, index=True) appiondb.addColumn('ApRefineIterData', 'mask', appiondb.int) appiondb.addColumn('ApRefineIterData', 'imask', appiondb.int) updateq = ( "UPDATE ApRefineIterData AS refiter " + " LEFT JOIN ApEmanRefineIterData AS emaniter " + " ON refiter.`REF|ApEmanRefineIterData|emanParams` = emaniter.`DEF_id` " + " SET " + " refiter.`REF|ApSymmetryData|symmetry` = emaniter.`REF|ApSymmetryData|symmetry`, " + " refiter.`mask` = emaniter.`mask`, " + " refiter.`imask` = emaniter.`imask`, " + " refiter.`DEF_timestamp` = refiter.`DEF_timestamp` ") appiondb.executeCustomSQL(updateq) appiondb.dropColumn('ApEmanRefineIterData', 'REF|ApSymmetryData|symmetry') appiondb.dropColumn('ApEmanRefineIterData', 'mask') appiondb.dropColumn('ApEmanRefineIterData', 'imask') #=================== # merge EMAN/Coran fields #=================== if appiondb.tableExists('ApRefineIterData') and appiondb.columnExists( 'ApRefineIterData', 'SpiCoranGoodClassAvg'): appiondb.addColumn('ApRefineIterData', 'postRefineClassAverages', appiondb.str) appiondb.addColumn('ApRefineIterData', 'refineClassAverages', appiondb.str) updateq = ( "UPDATE ApRefineIterData AS refiter " + " SET " + " refiter.`postRefineClassAverages` = " + " IF(refiter.`SpiCoranGoodClassAvg` IS NOT NULL, refiter.`SpiCoranGoodClassAvg`, NULL), " + " refiter.`refineClassAverages` = " + " IF(refiter.`emanClassAvg` IS NOT NULL, refiter.`emanClassAvg`, refiter.`classAverage`), " + " refiter.`DEF_timestamp` = refiter.`DEF_timestamp` ") appiondb.executeCustomSQL(updateq) appiondb.dropColumn('ApRefineIterData', 'classAverage') appiondb.dropColumn('ApRefineIterData', 'SpiCoranGoodClassAvg') appiondb.dropColumn('ApRefineIterData', 'emanClassAverage') appiondb.dropColumn('ApRefineIterData', 'emanClassAvg') appiondb.dropColumn('ApRefineIterData', 'MsgPGoodClassAvg') if appiondb.tableExists('ApRefineParticleData'): appiondb.renameColumn('ApRefineParticleData', 'coran_keep', 'postRefine_keep') ### special: invert values if appiondb.addColumn('ApRefineParticleData', 'refine_keep', appiondb.bool): appiondb.updateColumn('ApRefineParticleData', 'refine_keep', "MOD(IFNULL(`thrown_out`,0)+1,2)", "") appiondb.dropColumn('ApRefineParticleData', 'thrown_out') if (appiondb.columnExists('ApRefineParticleData', 'postRefine_keep') and appiondb.columnExists('ApRefineParticleData', 'msgp_keep')): appiondb.updateColumn( 'ApRefineParticleData', 'postRefine_keep', "`msgp_keep`", "`postRefine_keep` IS NULL AND `msgp_keep` IS NOT NULL") appiondb.dropColumn('ApRefineParticleData', 'msgp_keep') appiondb.changeColumnDefinition('ApRefineParticleData', 'refine_keep', appiondb.bool) appiondb.changeColumnDefinition('ApRefineParticleData', 'postRefine_keep', appiondb.bool) ### index columns appiondb.indexColumn('ApRefineParticleData', 'refine_keep') appiondb.indexColumn('ApRefineParticleData', 'postRefine_keep') ### Need to change: if appiondb.tableExists('ApRefineGoodBadParticleData'): appiondb.renameColumn('ApRefineGoodBadParticleData', 'REF|ApRefinementData|refine', 'REF|ApRefineIterData|refine', appiondb.link) appiondb.renameColumn('ApRefineGoodBadParticleData', 'good_normal', 'good_refine', appiondb.int) appiondb.renameColumn('ApRefineGoodBadParticleData', 'bad_normal', 'bad_refine', appiondb.int) appiondb.renameColumn('ApRefineGoodBadParticleData', 'good_coran', 'good_postRefine', appiondb.int) appiondb.renameColumn('ApRefineGoodBadParticleData', 'bad_coran', 'bad_postRefine', appiondb.int) appiondb.dropColumn('ApRefineGoodBadParticleData', 'good_msgp') appiondb.dropColumn('ApRefineGoodBadParticleData', 'bad_msgp') #=================== # add new columns calculated from old data #=================== ### count number of iterations if appiondb.addColumn('ApRefineRunData', 'num_iter', appiondb.int, index=True): """ selectq = ("SELECT refrun.`DEF_id`, COUNT(refiter.`DEF_id`) FROM ApRefineRunData AS refrun " +" LEFT JOIN ApRefineIterData AS refiter " +" ON refiter.`REF|ApRefineRunData|refineRun` = refrun.`DEF_id` " +" GROUP BY refrun.`DEF_id` " ) print appiondb.returnCustomSQL(selectq) """ updateq = ( "UPDATE ApRefineRunData AS refrun " + " SET " + " refrun.`num_iter` = " + "(SELECT COUNT(refiter.`DEF_id`) AS numiter " + " FROM ApRefineIterData AS refiter " + " WHERE refiter.`REF|ApRefineRunData|refineRun` = refrun.`DEF_id`), " + " refrun.`DEF_timestamp` = refrun.`DEF_timestamp` ") appiondb.executeCustomSQL(updateq) ### get boxsize if appiondb.addColumn('ApStackData', 'boxsize', appiondb.int, index=True): """ selectq = ("SELECT stackparams.`boxSize`, stackparams.`bin` FROM ApStackData AS stack " +" LEFT JOIN ApRunsInStackData AS runsinstack " +" ON runsinstack.`REF|ApStackData|stack` = stack.`DEF_id` " +" LEFT JOIN ApStackRunData AS stackrun " +" ON runsinstack.`REF|ApStackRunData|stackrun` = stackrun.`DEF_id` " +" LEFT JOIN ApStackParamsData AS stackparams " +" ON stackrun.`REF|ApStackParamsData|stackparams` = stackparams.`DEF_id` " ) print appiondb.returnCustomSQL(selectq) """ updateq = ( "UPDATE ApStackData AS stack " + " LEFT JOIN ApRunsInStackData AS runsinstack " + " ON runsinstack.`REF|ApStackData|stack` = stack.`DEF_id` " + " LEFT JOIN ApStackRunData AS stackrun " + " ON runsinstack.`REF|ApStackRunData|stackrun` = stackrun.`DEF_id` " + " LEFT JOIN ApStackParamsData AS stackparams " + " ON stackrun.`REF|ApStackParamsData|stackparams` = stackparams.`DEF_id` " + " SET " + " stack.`boxsize` = stackparams.`boxSize`/stackparams.`bin`, " + " stack.`DEF_timestamp` = stack.`DEF_timestamp` ") appiondb.executeCustomSQL(updateq) ### link to stackParams via StackRun via RunsInStack to get bin, boxSize #=================== # appion fields with spaces #=================== appiondb.renameColumn('ApMaskMakerParamsData', 'mask type', 'mask_type') appiondb.renameColumn('ApMaskMakerParamsData', 'region diameter', 'region_diameter') appiondb.renameColumn('ApMaskMakerParamsData', 'edge blur', 'edge_blur') appiondb.renameColumn('ApMaskMakerParamsData', 'edge low', 'edge_low') appiondb.renameColumn('ApMaskMakerParamsData', 'edge high', 'edge_high') appiondb.renameColumn('ApMaskMakerParamsData', 'region std', 'region_std') appiondb.renameColumn('ApMaskMakerParamsData', 'convex hull', 'convex_hull') appiondb.renameColumn('ApProtomoParamsData', 'series name', 'series_name') appiondb.renameColumn('ApProtomoAlignerParamsData', 'REF|ApProtomoRefinementParamsData|refine cycle', 'REF|ApProtomoRefinementParamsData|refine_cycle') appiondb.renameColumn('ApProtomoAlignerParamsData', 'REF|ApProtomoRefinementParamsData|good cycle', 'REF|ApProtomoRefinementParamsData|good_cycle') appiondb.renameColumn('ApProtomoAlignerParamsData', 'good start', 'good_start') appiondb.renameColumn('ApProtomoAlignerParamsData', 'good end', 'good_end') appiondb.renameColumn('ApTomoAlignerParamsData', 'REF|ApProtomoRefinementParamsData|refine cycle', 'REF|ApProtomoRefinementParamsData|refine_cycle') appiondb.renameColumn('ApTomoAlignerParamsData', 'REF|ApProtomoRefinementParamsData|good cycle', 'REF|ApProtomoRefinementParamsData|good_cycle') appiondb.renameColumn('ApTomoAlignerParamsData', 'good start', 'good_start') appiondb.renameColumn('ApTomoAlignerParamsData', 'good end', 'good_end') appiondb.renameColumn('ApTomoAvgParticleData', 'REF|ApAlignParticlesData|aligned particle', 'REF|ApAlignParticlesData|aligned_particle') appiondb.renameColumn('ApTomoAvgParticleData', 'z shift', 'z_shift') appiondb.renameColumn('ApInitialModelData', 'REF|Ap3dDensityData|original density', 'REF|Ap3dDensityData|original_density') appiondb.renameColumn('ApInitialModelData', 'REF|ApInitialModelData|original model', 'REF|ApInitialModelData|original_model') #=================== # special fix for symmetry tables that are mis-described #=================== appiondb.updateColumn( 'ApSymmetryData', 'description', "'7-fold symmetry along the z axis'", "`symmetry` = 'C7 (z)' AND `description` LIKE '3-fold symmetry%'") #=================== # clarify icosahedral symmetry #=================== appiondb.updateColumn('ApSymmetryData', 'symmetry', "'Icos (2 3 5) Viper/3DEM'", "`symmetry` = 'Icos (2 3 5)'") appiondb.updateColumn('ApSymmetryData', 'symmetry', "'Icos (5 3 2) EMAN'", "`symmetry` = 'Icos (5 3 2)'") appiondb.updateColumn('ApSymmetryData', 'symmetry', "'Icos (2 5 3) Crowther'", "`symmetry` = 'Icos (2 5 3)'") #=================== # repair misnamed ApAppionJobData job names #=================== #selectq = "SELECT DISTINCT `jobtype` FROM `ApAppionJobData` ORDER BY `jobtype`;" jobmap = { 'ace': 'pyace', 'ace2': 'pyace2', 'templatepicker': 'templatecorrelator', 'makestack': 'makestack2', 'recon': 'emanrecon', } if appiondb.tableExists('ApAppionJobData'): for key in jobmap.keys(): appiondb.updateColumn('ApAppionJobData', 'jobtype', "'" + jobmap[key] + "'", "`jobtype` = '" + key + "' ") #=================== # add index to ApStackParticleData #=================== appiondb.indexColumn('ApParticleData', 'label', length=8) appiondb.indexColumn('ApStackParticleData', 'particleNumber') appiondb.indexColumn('ApAlignParticleData', 'partnum') appiondb.indexColumn('ApClusteringParticleData', 'partnum') appiondb.indexColumn('ApRefineIterData', 'iteration') appiondb.indexColumn('ApCtfData', 'confidence') appiondb.indexColumn('ApCtfData', 'confidence_d') appiondb.indexColumn('ApPathData', 'path', length=32) #=================== # index all columns named hidden #=================== for tablename in appiondb.getAllTables(): appiondb.changeColumnDefinition(tablename, 'hidden', appiondb.bool) appiondb.indexColumn(tablename, 'hidden') ### old version misnamed column appiondb.renameColumn( tablename, 'REF|project|projects|project', "REF|" + projectdb.getSinedonName() + "|projects|project") appiondb.renameColumn( tablename, 'project|projects|project', "REF|" + projectdb.getSinedonName() + "|projects|project") appiondb.indexColumn( tablename, "REF|" + projectdb.getSinedonName() + "|projects|project") ### refine tables appiondb.renameColumn(tablename, 'REF|ApRefinementData|refinementData', 'REF|ApRefineIterData|refineIter', appiondb.link) appiondb.renameColumn(tablename, 'REF|ApRefinementData|refinement', 'REF|ApRefineIterData|refineIter', appiondb.link) appiondb.renameColumn(tablename, 'REF|ApRefinementData|refine', 'REF|ApRefineIterData|refineIter', appiondb.link) appiondb.renameColumn(tablename, 'REF|ApRefinementRunData|refinementRun', 'REF|ApRefineRunData|refineRun', appiondb.link) appiondb.renameColumn(tablename, 'REF|ApRefinementParamsData|refinementParams', 'REF|ApEmanRefineIterData|emanParams', appiondb.link) ### plural particles tables appiondb.renameColumn(tablename, 'REF|ApStackParticlesData|particle', 'REF|ApStackParticleData|particle', appiondb.link) appiondb.renameColumn(tablename, 'REF|ApStackParticlesData|stackpart', 'REF|ApStackParticleData|stackpart', appiondb.link) appiondb.renameColumn(tablename, 'REF|ApAlignParticlesData|alignparticle', 'REF|ApAlignParticleData|alignparticle', appiondb.link) ### no long required to be on a cluster appiondb.renameColumn(tablename, 'REF|ApClusterJobData|job', 'REF|ApAppionJobData|job') appiondb.renameColumn(tablename, 'REF|ApClusterJobData|jobfile', 'REF|ApAppionJobData|job') #=================== # set all boolean columns to TINYINT(1), there was an old bug #=================== appiondb.changeColumnDefinition('ApAssessmentData', 'selectionkeep', appiondb.bool) appiondb.indexColumn('ApAssessmentData', 'selectionkeep') appiondb.changeColumnDefinition('ApAlignParticleData', 'mirror', appiondb.bool) print "DONE"