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"
