timeS = ""
minR = 0
maxR = 0
svName = ""
view = "all"
inStream = "all"
dataset = ""
energy = ""
badRunList = 0
goodRunList= 0
x = 1
fileRunList= ""
while x < len(sys.argv):
try:
if string.lower(sys.argv[x]) == "-db" :
dbInName,dbInHost,dbInPort,dbInSocket=es_init.decodeHostNameString(sys.argv[x+1])
x+=2
continue
if sys.argv[x]=="-runRange":
minR = int(sys.argv[x+1])
msg = "you need to use -runRange <minR> <maxR>"
if x+2<len(sys.argv) and sys.argv[x+2]:
if sys.argv[x+2][0]=="-":
raise msg
else:
raise msg
maxR = int(sys.argv[x+2])
checkArg([minR,maxR])
x+=3
continue
if sys.argv[x]=="-grade":
# input file
iFile = sys.argv[1]
if not os.path.isfile(iFile):
print "File '%s' not found"%iFile
sys.exit(1)
dbName = 'EventStoreTest' ### UPDATE
dbHost = 'lnx151.lns.cornell.edu' ### UPDATE
dbPort = 3307
if len(sys.argv)>=3 and sys.argv[2]:
if os.path.isfile(sys.argv[2]):
dbHost = sys.argv[2] # will use it as sqlite
dbName = ""
dbPort = ""
else:
dbName,dbHost,dbPort,dbSock=es_init.decodeHostNameString(sys.argv[2])
grade = "daq" ### UPDATE
if len(sys.argv)==4 and sys.argv[3]:
grade = sys.argv[3]
if dbName=="EventStore" and os.environ['USER']!='pass2': ### UPDATE
print "To do run truncation in EventStore you need to be 'pass2'" ### UPDATE
sys.exit(1)
fLines = open(iFile,'r').readlines()
run = string.split((fLines[-1]))[1]
runBase= "%s00"%run[:4]
if dbHost and not dbName:
try:
timeS = ""
minR = 0
maxR = 0
svName = ""
view = "all"
inStream = "all"
dataset = ""
energy = ""
badRunList = 0
goodRunList = 0
x = 1
fileRunList = ""
while x < len(sys.argv):
try:
if string.lower(sys.argv[x]) == "-db":
dbInName, dbInHost, dbInPort, dbInSocket = es_init.decodeHostNameString(
sys.argv[x + 1])
x += 2
continue
if sys.argv[x] == "-runRange":
minR = int(sys.argv[x + 1])
msg = "you need to use -runRange <minR> <maxR>"
if x + 2 < len(sys.argv) and sys.argv[x + 2]:
if sys.argv[x + 2][0] == "-":
raise msg
else:
raise msg
maxR = int(sys.argv[x + 2])
checkArg([minR, maxR])
x += 3
continue
if sys.argv[x] == "-grade":
def db2db(args):
"""A tool to merge one EventStore DB into another. We use the following algorithm:
- lock tables of output DB for writing
- loop over available grade,timeStamps in input DB => get graphid
- update Version table in output DB
- loop over files in FileID
- using new graphid and new (fileName,fileType) update KeyFile
and Location tables. In the case of location file, change fileIds in its
header to new values. In both cases change fileName of index/location files.
- update FileID with new fileName (if any, in the case of data file we don't
change its name).
- rename the file.
Please note, we only intend to change a prefix of files and rely on the fact
that all files in input DB were properly configured (according with EventStore
specs. This routine accepts a list of command line arguments to do the merging.
Here an example:
ESDB2DB -dbin EventStore1@/home/vk/sqlite.db -dbout EventStore2@lnx151 -changeprefix /nfs /cdat
"""
msg = """
Usage: ESDB2DB [ -help ] [ --help ] [ -examples ] [ -profile ]
[ -verbose ] [ -historyfile <filename> ]
[ -logFile </path/filename or 'stdout' or 'stderr'> ]
[ -dbin [<dbName>@<dbHost> or <fileName>] ]
[ -dbout [<dbName>@<dbHost> or <fileName>] ]
[ -changeprefix <fromDir> <toDir> ]
"""
msg += "\n"
msg += "\nOptions can be specified in any order."
msg += "\nFor option description please run 'ESDB2DB --help'"
msg += "\nFor use cases please run 'ESDB2DB -examples'"
msg += "\nContact: Sean Dobbs, [email protected]\n"
usage = msg
usageDescription = ""
examples = """
Examples:
to merge content of SQLite EventStore1 DB into MySQL EventStore2 DB
ESDB2DB -dbin EventStore1@/home/vk/sqlite.db -dbout EventStore2@lnx151
to merge content of SQLite EventStore1 DB into MySQL EventStore2 DB, plus
change prefix of all files in SQLite from /nfs to /cdat
ESDB2DB -dbin EventStore1@/home/vk/sqlite.db -dbout EventStore2@lnx151
-changeprefix /nfs /cdat
"""
userCommand = "ESDB2DB.py"
optList, dictOpt = es_init.ESOptions(userCommand, args, usage, usageDescription, examples)
dbName, dbHost, userName, userPass, dbPort, dbSocket = optList[0]
historyFile, logFile, verbose, profile = optList[1]
userCommand = optList[2]
dbInHost = bInName = dbOutHost = dbOutHost = oldDir = newDir = ""
x = 1
inOut = 0
while x < len(args):
try:
if string.lower(args[x]) == "-dbin":
dbInName, dbInHost, dbInPort, dbInSocket = es_init.decodeHostNameString(args[x + 1])
inOut += 1
x += 2
continue
if string.lower(args[x]) == "-dbout":
dbOutName, dbOutHost, dbOutPort, dbOutSocket = es_init.decodeHostNameString(args[x + 1])
inOut += 1
x += 2
continue
if string.lower(args[x]) == "-changeprefix":
oldDir = args[x + 1]
newDir = args[x + 2]
x += 3
continue
if dictOpt.has_key(args[x]):
x += dictOpt[args[x]]
else:
print "Option '%s' is not allowed" % args[x]
raise
except:
gen_util.printExcept()
sys.exit(1)
if inOut != 2:
print usage
sys.exit(1)
if newDir:
if not os_path_util.checkPermission(newDir):
print "You don't have permission to write to", newDir
sys.exit(1)
# initialize log
outputLog, globalLog = es_init.ESOutputLog(logFile)
# connect to dbIn EventStore
dbIn, dbInType = es_init.ESDBConnector(dbInHost, dbInName, userName, userPass, "EXCLUSIVE", dbInPort, dbInSocket)
sqlIn = sql_util.SQLUtil(dbIn, dbInType, outputLog)
inTableList = sqlIn.getTables()
# in the case of SQLite we need to open DB with isolation level EXCLUSIVE
# in order to lock all tables.
# dbOut,dbOutType= es_init.ESDBConnector(dbOutHost,dbOutName,userName,userPass,'EXCLUSIVE',dbPort,dbSocket)
dbOut, dbOutType = es_init.ESDBConnector(dbOutHost, dbOutName, userName, userPass, "", dbOutPort, dbOutSocket)
sqlOut = sql_util.SQLUtil(dbOut, dbOutType, outputLog)
outTableList = sqlOut.getTables()
if verbose:
print "input table list:", inTableList
print "output table list:", outTableList
if inTableList != outTableList:
for table in inTableList:
if not outTableList.count(table) and table != "MaxMasterID":
if verbose:
print "No '%s' EventStore table found, we will create it" % table
sqlOut.createTables(table)
# we're going to use a dictionary of old/new ids for data files
dict = {}
# Initialize ESManager for dbOut
es_init.ESInput(userCommand, outputLog, dbOutType)
esManager = ESManager.ESManager(dbOut, dbOutType, outputLog)
esManager.setVerboseLevel(verbose)
# lock all tables for write operation in dbOut
esManager.lockTables()
# postpone all commits to DB, once job will finish we'll invoke commit.
esManager.setCommitFlag(0)
# get list of parents from dbIn
parentList = []
query = "SELECT parentId FROM PathDepend"
tup = sqlIn.fetchAll(query)
for item in tup:
parentId = item[0]
query = (
"""SELECT svName FROM GraphPath,SpecificVersion WHERE
GraphPath.svid=SpecificVersion.svid AND
graphid='%s'"""
% parentId
)
data = sqlIn.fetchAll(query)
for name in data:
if not parentList.count(name[0]):
parentList.append(name[0])
# Clean-up dbIn and remove empty fileNames (they may be created in the case of dskim when
# parent files where not store into db)
query = "DELETE FROM FileID where fileName IS NULL"
tup = sqlIn.updateDBAndLog(query)
# First step is to modify path prefix in dbIn to new one
if newDir:
print "Modifying original DB to use new path prefix"
try:
query = "SELECT fileId,fileName FROM FileID"
tup = sqlIn.fetchAll(query)
for item in tup:
fileId = item[0]
fileName = item[1]
# replace fileName with new fileId
newFileName = os.path.normpath(gen_util.changeFileName(fileName, oldDir, newDir))
query = "UPDATE FileID SET fileName='%s' WHERE fileId='%s'" % (newFileName, fileId)
sqlIn.updateDBAndLog(query)
if verbose:
print fileName, "=>", newFileName
except:
dbIn.rollback()
print "Caught an error during merging step."
gen_util.printExcept()
status = es_init.ESOutput(0, userCommand, historyFile, outputLog, globalLog)
return status
# commit changes
dbIn.commit()
# put everything in try/except block, if something bad happens we'll rollback
maxFileId = 0
if dbOutType == "sqlite":
query = "BEGIN IMMEDIATE"
sqlOut.updateDBAndLog(query)
try:
# get max fileId from dbOut, in the case if we'll need to rollback
query = "SELECT MAX(fileId) FROM FileID"
tup = sqlOut.fetchOne(query)
maxFileId = tup[0]
# get info from Version dbIn.
query = """SELECT grade,timeStamp,minRunNumber,maxRunNumber,
Version.graphid,svName
FROM Version,GraphPath,SpecificVersion
WHERE Version.graphid=GraphPath.graphid
AND GraphPath.svid=SpecificVersion.svid"""
tup = sqlIn.fetchAll(query)
dictGraphId = {}
lastGraphId = 0
locList = []
keyList = []
# loop over entries in Version dbIn and fill out Version dbOut.
for item in tup:
_grade = item[0]
_timeS = item[1]
_minR = item[2]
_maxR = item[3]
_gid = item[4]
_svName = item[5]
# set-up all parameters
esManager.setSVName(_svName)
esManager.setGrade(_grade)
esManager.setTimeStamp(_timeS)
esManager.setMinRun(_minR)
esManager.setMaxRun(_maxR)
esManager.setParents(parentList)
# update GraphPath, SpecificVersion tables
newGraphId = esManager.updateVersion()
if not newGraphId:
print "Fail to update Version table"
sys.exit(1)
# fill out dictionary of old->new graphid's
if not dictGraphId.has_key(_gid):
dictGraphId[_gid] = newGraphId
parentgidList = ""
for parent in parentList:
query = (
"""SELECT graphid FROM GraphPath,SpecificVersion WHERE
GraphPath.svid=SpecificVersion.svid AND
SpecificVersion.svName='%s'"""
% parent
)
parentgidList = sqlOut.fetchAll(query)
# Loop over FileID table in dbIn and update FileID, KeyFile, Location, FileType,
# RunUID tables in dbOut
#
# while inserting into dbOut we need to change file names
query = "SELECT fileId FROM FileID"
tup = sqlIn.fetchAll(query)
print "Processing:"
for item in tup:
fileId = item[0]
query = (
"""SELECT fileName,type FROM FileID,FileType
WHERE FileID.typeId=FileType.id AND fileId=%s"""
% fileId
)
tup = sqlIn.fetchOne(query)
# print query
# print tup
if not tup:
continue
fileName = tup[0]
fileType = tup[1]
print fileName
# check dbOut if file already there (e.g. we used parents)
query = "SELECT fileId FROM FileID where fileName='%s'" % fileName
tup = sqlOut.fetchOne(query)
if tup and tup[0]:
if verbose:
print "Found '%s' in dbOut with fileId=%s" % (fileName, tup[0])
dict[fileId] = tup[0]
# look-up Location table and save it to the list to preserve order of loc.files
query = "SELECT id,run,uid,graphid FROM Location WHERE locationFileId=%s" % fileId
tup = sqlIn.fetchAll(query)
for item in tup:
id = item[0]
run = item[1]
uid = item[2]
gid = item[3]
try:
newGraphId = dictGraphId[gid]
except:
continue
locList.append((id, newGraphId, run, uid, fileId))
# look-up KeyFile table and save it
query = "SELECT graphid,view,run,uid FROM KeyFile WHERE keyFileId=%s" % fileId
tup = sqlIn.fetchAll(query)
for item in tup:
gid = item[0]
view = item[1]
run = item[2]
uid = item[3]
try:
newGraphId = dictGraphId[gid]
except:
continue
keyList.append((newGraphId, view, run, uid, fileId))
continue
# change file permission to be user-writable
if fileType == "levio":
os.chmod(fileName, 0644)
query = "SELECT id FROM FileType WHERE type='%s'" % fileType
try:
tup = sqlOut.fetchOne(query)
except:
print "No typeId found for %s" % fileType
raise
if not tup:
typeId = esManager.updateFileType(fileName)
else:
typeId = tup[0]
# get new file id
newFileId = esManager.getIds(1)
dict[fileId] = newFileId
# make new fileName and copy loc. files and make hard link for key files
# if fileType=='ikey' or fileType=='levio' or fileType=='lhddm':
# fromField = 'esdb-%s'%fileId
# toField = 'esdb-%s'%newFileId
# newFileName=gen_util.changeFileName(fileName,fromField,toField)
# dir,file = os.path.split(newFileName)
# if not os.path.isdir(dir):
# if verbose:
# print "Create",dir
# os.makedirs(dir)
# if fileType=='ikey':
# if not os.path.isfile(newFileName):
# os.link(fileName,newFileName)
# else:
# if not os.path.islink(newFileName):
# print "File '%s' not found"%newFileName
# raise
# if verbose:
# print "Link",fileName,newFileName
# else:
# if fileName!=newFileName:
# shutil.copy(fileName,newFileName)
# if verbose:
# print "Copy",fileName,newFileName
# else:
newFileName = fileName
if fileType == "ikey":
# update KeyFile table
query = "SELECT view,run,uid,graphid FROM KeyFile WHERE keyFileId=%s" % fileId
tup = sqlIn.fetchAll(query)
for item in tup:
view = item[0]
esManager.setView(view)
run = item[1]
uid = item[2]
gid = item[3]
newGraphId = dictGraphId[gid]
esManager.updateKeyFile(newGraphId, view, run, uid, newFileId)
# we also need to make a copy of key file ids for all parents
# for parentgid in parentgidList:
# query="""SELECT keyFileId,view FROM KeyFile WHERE
# graphid='%s' AND run='%s' AND uid='%s'"""%(parentgid[0],run,uid)
# data = sqlOut.fetchAll(query)
# for id in data:
# kid = id[0]
# view = id[1]
# check if we already injected key file into newGraphId
# query="""SELECT keyFileId FROM KeyFIle WHERE graphid='%s'
# AND view='%s' AND run='%s'
# AND uid='%s'"""%(newGraphId,view,run,uid)
# tup = sqlOut.fetchOne(query)
# if not tup:
# esManager.updateKeyFile(newGraphId,view,run,uid,kid)
# update RunUID table
esManager.updateRunUID(run, uid)
# update FileID table
if verbose:
print "Update:", newFileId, newFileName, typeId
esManager.updateFileID(newFileId, newFileName, typeId)
# update KeyFile table
for item in keyList:
gid = item[0]
view = item[1]
run = item[2]
uid = item[3]
keyFileId = item[4]
# check if this entry exists in DB
query = """SELECT graphid FROM KeyFile WHERE graphid='%s' AND view='%s' AND run='%s'
AND uid='%s' AND keyFileId='%s'""" % (
gid,
view,
run,
uid,
keyFileId,
)
tup = sqlOut.fetchOne(query)
if tup:
if verbose:
msg = "Found (%s,%s,%s,%s,%s)" % (gid, view, run, uid, keyFileId)
msg += "in KeyFile table, no update necessary"
else:
esManager.updateKeyFile(gid, view, run, uid, keyFileId)
except:
print "Caught an error during merging step."
gen_util.printExcept()
# something wrong, loop over new entries in dbOut and remove newly created files
query = """SELECT fileName FROM FileID WHERE fileId='%s'""" % maxFileId
tup = sqlIn.fetchAll(query)
origList = []
for item in tup:
origList.append(item[0])
query = (
"""SELECT fileName,type FROM FileID,FileType WHERE
FileID.typeId=FileType.id AND fileId>'%s'"""
% maxFileId
)
tup = sqlOut.fetchAll(query)
for item in tup:
fileName = item[0]
fileType = item[1]
if fileType == "ikey" or fileType == "lpds" or fileType == "lbin" or fileType == "lhddm":
os.chmod(fileName, 0644)
if verbose:
print "Remove", fileName
try:
if not origList.count(fileName):
os.remove(fileName)
except:
pass
# remove as well last file we process
# if oldDir!=newDir:
# if verbose: print "Remove",newFileName
# try:
# os.chmod(newFileName,0644)
# os.remove(newFileName)
# except:
# pass
# let's rollback
if verbose:
print "Invoke rollback"
dbOut.rollback()
esManager.rollback()
status = es_init.ESOutput(0, userCommand, historyFile, outputLog, globalLog)
return status
# if dbOutType=="sqlite":
# query="COMMIT"
# sqlOut.updateDBAndLog(query)
query = """SELECT fileName,type FROM FileID,FileType WHERE
FileID.typeId=FileType.id"""
tupOut = sqlOut.fetchAll(query)
dbOut.commit()
esManager.unlockTables()
esManager.close()
# everything is fine and we may clean-up original files
query = """SELECT fileName,type FROM FileID,FileType WHERE
FileID.typeId=FileType.id"""
tupIn = sqlIn.fetchAll(query)
listOut = list(tupOut)
for item in tupIn:
if listOut.count(item):
continue
fileName = item[0]
fileType = item[1]
if fileType == "ikey" or fileType == "levio" or fileType == "lhddm":
if verbose:
print "Remove", fileName
try:
os.remove(fileName)
except:
pass
returnStatus = es_init.ESOutput(1, userCommand, historyFile, outputLog, globalLog)
return returnStatus
def db2db(args):
"""A tool to merge one EventStore DB into another. We use the following algorithm:
- lock tables of output DB for writing
- loop over available grade,timeStamps in input DB => get graphid
- update Version table in output DB
- loop over files in FileID
- using new graphid and new (fileName,fileType) update KeyFile
and Location tables. In the case of location file, change fileIds in its
header to new values. In both cases change fileName of index/location files.
- update FileID with new fileName (if any, in the case of data file we don't
change its name).
- rename the file.
Please note, we only intend to change a prefix of files and rely on the fact
that all files in input DB were properly configured (according with EventStore
specs. This routine accepts a list of command line arguments to do the merging.
Here an example:
ESDB2DB -dbin EventStore1@/home/vk/sqlite.db -dbout EventStore2@lnx151 -changeprefix /nfs /cdat
"""
msg="""
Usage: ESDB2DB [ -help ] [ --help ] [ -examples ] [ -profile ]
[ -verbose ] [ -historyfile <filename> ]
[ -logFile </path/filename or 'stdout' or 'stderr'> ]
[ -dbin [<dbName>@<dbHost> or <fileName>] ]
[ -dbout [<dbName>@<dbHost> or <fileName>] ]
[ -changeprefix <fromDir> <toDir> ]
"""
msg+= "\n"
msg+= "\nOptions can be specified in any order."
msg+= "\nFor option description please run 'ESDB2DB --help'"
msg+= "\nFor use cases please run 'ESDB2DB -examples'"
msg+= "\nContact: Sean Dobbs, [email protected]\n"
usage = msg
usageDescription=""
examples="""
Examples:
to merge content of SQLite EventStore1 DB into MySQL EventStore2 DB
ESDB2DB -dbin EventStore1@/home/vk/sqlite.db -dbout EventStore2@lnx151
to merge content of SQLite EventStore1 DB into MySQL EventStore2 DB, plus
change prefix of all files in SQLite from /nfs to /cdat
ESDB2DB -dbin EventStore1@/home/vk/sqlite.db -dbout EventStore2@lnx151
-changeprefix /nfs /cdat
"""
userCommand="ESDB2DB.py"
optList, dictOpt = es_init.ESOptions(userCommand,args,usage,usageDescription,examples)
dbName,dbHost,userName,userPass,dbPort,dbSocket = optList[0]
historyFile,logFile,verbose,profile = optList[1]
userCommand = optList[2]
dbInHost=bInName=dbOutHost=dbOutHost=oldDir=newDir=""
x = 1
inOut = 0
while x < len(args):
try:
if string.lower(args[x]) == "-dbin" :
dbInName,dbInHost,dbInPort,dbInSocket=es_init.decodeHostNameString(args[x+1])
inOut+=1
x+=2
continue
if string.lower(args[x]) == "-dbout" :
dbOutName,dbOutHost,dbOutPort,dbOutSocket=es_init.decodeHostNameString(args[x+1])
inOut+=1
x+=2
continue
if string.lower(args[x]) == "-changeprefix":
oldDir = args[x+1]
newDir = args[x+2]
x+=3
continue
if dictOpt.has_key(args[x]):
x+=dictOpt[args[x]]
else:
print "Option '%s' is not allowed"%args[x]
raise
except:
gen_util.printExcept()
sys.exit(1)
if inOut!=2:
print usage
sys.exit(1)
if newDir:
if not os_path_util.checkPermission(newDir):
print "You don't have permission to write to",newDir
sys.exit(1)
# initialize log
outputLog, globalLog = es_init.ESOutputLog(logFile)
# connect to dbIn EventStore
dbIn, dbInType = es_init.ESDBConnector(dbInHost,dbInName,userName,userPass,'EXCLUSIVE',dbInPort,dbInSocket)
sqlIn = sql_util.SQLUtil(dbIn,dbInType,outputLog)
inTableList = sqlIn.getTables()
# in the case of SQLite we need to open DB with isolation level EXCLUSIVE
# in order to lock all tables.
# dbOut,dbOutType= es_init.ESDBConnector(dbOutHost,dbOutName,userName,userPass,'EXCLUSIVE',dbPort,dbSocket)
dbOut,dbOutType= es_init.ESDBConnector(dbOutHost,dbOutName,userName,userPass,'',dbOutPort,dbOutSocket)
sqlOut = sql_util.SQLUtil(dbOut,dbOutType,outputLog)
outTableList = sqlOut.getTables()
if verbose:
print "input table list:",inTableList
print "output table list:",outTableList
if inTableList!=outTableList:
for table in inTableList:
if not outTableList.count(table) and table!='MaxMasterID':
if verbose:
print "No '%s' EventStore table found, we will create it"%table
sqlOut.createTables(table)
# we're going to use a dictionary of old/new ids for data files
dict = {}
# Initialize ESManager for dbOut
es_init.ESInput(userCommand,outputLog,dbOutType)
esManager = ESManager.ESManager(dbOut,dbOutType,outputLog)
esManager.setVerboseLevel(verbose)
# lock all tables for write operation in dbOut
esManager.lockTables()
# postpone all commits to DB, once job will finish we'll invoke commit.
esManager.setCommitFlag(0)
# get list of parents from dbIn
parentList=[]
query="SELECT parentId FROM PathDepend"
tup = sqlIn.fetchAll(query)
for item in tup:
parentId = item[0]
query="""SELECT svName FROM GraphPath,SpecificVersion WHERE
GraphPath.svid=SpecificVersion.svid AND
graphid='%s'"""%parentId
data = sqlIn.fetchAll(query)
for name in data:
if not parentList.count(name[0]):
parentList.append(name[0])
# Clean-up dbIn and remove empty fileNames (they may be created in the case of dskim when
# parent files where not store into db)
query = "DELETE FROM FileID where fileName IS NULL"
tup = sqlIn.updateDBAndLog(query)
# First step is to modify path prefix in dbIn to new one
if newDir:
print "Modifying original DB to use new path prefix"
try:
query = "SELECT fileId,fileName FROM FileID"
tup = sqlIn.fetchAll(query)
for item in tup:
fileId = item[0]
fileName= item[1]
# replace fileName with new fileId
newFileName = os.path.normpath(gen_util.changeFileName(fileName,oldDir,newDir))
query = "UPDATE FileID SET fileName='%s' WHERE fileId='%s'"%(newFileName,fileId)
sqlIn.updateDBAndLog(query)
if verbose: print fileName,"=>",newFileName
except:
dbIn.rollback()
print "Caught an error during merging step."
gen_util.printExcept()
status = es_init.ESOutput(0,userCommand,historyFile,outputLog,globalLog)
return status
# commit changes
dbIn.commit()
# put everything in try/except block, if something bad happens we'll rollback
maxFileId = 0
if dbOutType=="sqlite":
query="BEGIN IMMEDIATE"
sqlOut.updateDBAndLog(query)
try:
# get max fileId from dbOut, in the case if we'll need to rollback
query = "SELECT MAX(fileId) FROM FileID"
tup = sqlOut.fetchOne(query)
maxFileId = tup[0]
# get info from Version dbIn.
query = """SELECT grade,timeStamp,minRunNumber,maxRunNumber,
Version.graphid,svName
FROM Version,GraphPath,SpecificVersion
WHERE Version.graphid=GraphPath.graphid
AND GraphPath.svid=SpecificVersion.svid"""
tup = sqlIn.fetchAll(query)
dictGraphId= {}
lastGraphId= 0
locList = []
keyList = []
# loop over entries in Version dbIn and fill out Version dbOut.
for item in tup:
_grade = item[0]
_timeS = item[1]
_minR = item[2]
_maxR = item[3]
_gid = item[4]
_svName= item[5]
# set-up all parameters
esManager.setSVName(_svName)
esManager.setGrade(_grade)
esManager.setTimeStamp(_timeS)
esManager.setMinRun(_minR)
esManager.setMaxRun(_maxR)
esManager.setParents(parentList)
# update GraphPath, SpecificVersion tables
newGraphId=esManager.updateVersion()
if not newGraphId:
print "Fail to update Version table"
sys.exit(1)
# fill out dictionary of old->new graphid's
if not dictGraphId.has_key(_gid):
dictGraphId[_gid]=newGraphId
parentgidList=""
for parent in parentList:
query="""SELECT graphid FROM GraphPath,SpecificVersion WHERE
GraphPath.svid=SpecificVersion.svid AND
SpecificVersion.svName='%s'"""%parent
parentgidList= sqlOut.fetchAll(query)
# Loop over FileID table in dbIn and update FileID, KeyFile, Location, FileType,
# RunUID tables in dbOut
#
# while inserting into dbOut we need to change file names
query="SELECT fileId FROM FileID"
tup = sqlIn.fetchAll(query)
print "Processing:"
for item in tup:
fileId = item[0]
query = """SELECT fileName,type FROM FileID,FileType
WHERE FileID.typeId=FileType.id AND fileId=%s"""%fileId
tup = sqlIn.fetchOne(query)
# print query
# print tup
if not tup:
continue
fileName= tup[0]
fileType= tup[1]
print fileName
# check dbOut if file already there (e.g. we used parents)
query = "SELECT fileId FROM FileID where fileName='%s'"%fileName
tup = sqlOut.fetchOne(query)
if tup and tup[0]:
if verbose:
print "Found '%s' in dbOut with fileId=%s"%(fileName,tup[0])
dict[fileId]=tup[0]
# look-up Location table and save it to the list to preserve order of loc.files
query="SELECT id,run,uid,graphid FROM Location WHERE locationFileId=%s"%fileId
tup = sqlIn.fetchAll(query)
for item in tup:
id = item[0]
run = item[1]
uid = item[2]
gid = item[3]
try:
newGraphId = dictGraphId[gid]
except:
continue
locList.append((id,newGraphId,run,uid,fileId))
# look-up KeyFile table and save it
query="SELECT graphid,view,run,uid FROM KeyFile WHERE keyFileId=%s"%fileId
tup = sqlIn.fetchAll(query)
for item in tup:
gid = item[0]
view= item[1]
run = item[2]
uid = item[3]
try:
newGraphId = dictGraphId[gid]
except:
continue
keyList.append((newGraphId,view,run,uid,fileId))
continue
# change file permission to be user-writable
if fileType=='levio':
os.chmod(fileName,0644)
query = "SELECT id FROM FileType WHERE type='%s'"%fileType
try:
tup = sqlOut.fetchOne(query)
except:
print "No typeId found for %s"%fileType
raise
if not tup:
typeId = esManager.updateFileType(fileName)
else:
typeId = tup[0]
# get new file id
newFileId = esManager.getIds(1)
dict[fileId]=newFileId
# make new fileName and copy loc. files and make hard link for key files
#if fileType=='ikey' or fileType=='levio' or fileType=='lhddm':
# fromField = 'esdb-%s'%fileId
# toField = 'esdb-%s'%newFileId
# newFileName=gen_util.changeFileName(fileName,fromField,toField)
# dir,file = os.path.split(newFileName)
# if not os.path.isdir(dir):
# if verbose:
# print "Create",dir
# os.makedirs(dir)
# if fileType=='ikey':
# if not os.path.isfile(newFileName):
# os.link(fileName,newFileName)
# else:
# if not os.path.islink(newFileName):
# print "File '%s' not found"%newFileName
# raise
# if verbose:
# print "Link",fileName,newFileName
# else:
# if fileName!=newFileName:
# shutil.copy(fileName,newFileName)
# if verbose:
# print "Copy",fileName,newFileName
#else:
newFileName=fileName
if fileType=='ikey':
# update KeyFile table
query="SELECT view,run,uid,graphid FROM KeyFile WHERE keyFileId=%s"%fileId
tup = sqlIn.fetchAll(query)
for item in tup:
view= item[0]
esManager.setView(view)
run = item[1]
uid = item[2]
gid = item[3]
newGraphId = dictGraphId[gid]
esManager.updateKeyFile(newGraphId,view,run,uid,newFileId)
# we also need to make a copy of key file ids for all parents
# for parentgid in parentgidList:
# query="""SELECT keyFileId,view FROM KeyFile WHERE
# graphid='%s' AND run='%s' AND uid='%s'"""%(parentgid[0],run,uid)
# data = sqlOut.fetchAll(query)
# for id in data:
# kid = id[0]
# view = id[1]
# check if we already injected key file into newGraphId
# query="""SELECT keyFileId FROM KeyFIle WHERE graphid='%s'
# AND view='%s' AND run='%s'
# AND uid='%s'"""%(newGraphId,view,run,uid)
# tup = sqlOut.fetchOne(query)
# if not tup:
# esManager.updateKeyFile(newGraphId,view,run,uid,kid)
# update RunUID table
esManager.updateRunUID(run,uid)
# update FileID table
if verbose:
print "Update:",newFileId,newFileName,typeId
esManager.updateFileID(newFileId,newFileName,typeId)
# update KeyFile table
for item in keyList:
gid = item[0]
view = item[1]
run = item[2]
uid = item[3]
keyFileId = item[4]
# check if this entry exists in DB
query = """SELECT graphid FROM KeyFile WHERE graphid='%s' AND view='%s' AND run='%s'
AND uid='%s' AND keyFileId='%s'"""%(gid,view,run,uid,keyFileId)
tup = sqlOut.fetchOne(query)
if tup:
if verbose:
msg = "Found (%s,%s,%s,%s,%s)"%(gid,view,run,uid,keyFileId)
msg+= "in KeyFile table, no update necessary"
else:
esManager.updateKeyFile(gid,view,run,uid,keyFileId)
except:
print "Caught an error during merging step."
gen_util.printExcept()
# something wrong, loop over new entries in dbOut and remove newly created files
query = """SELECT fileName FROM FileID WHERE fileId='%s'"""%maxFileId
tup = sqlIn.fetchAll(query)
origList = []
for item in tup:
origList.append(item[0])
query = """SELECT fileName,type FROM FileID,FileType WHERE
FileID.typeId=FileType.id AND fileId>'%s'"""%maxFileId
tup = sqlOut.fetchAll(query)
for item in tup:
fileName = item[0]
fileType = item[1]
if fileType=='ikey' or fileType=='lpds' or fileType=='lbin' or fileType=='lhddm':
os.chmod(fileName,0644)
if verbose: print "Remove",fileName
try:
if not origList.count(fileName):
os.remove(fileName)
except:
pass
# remove as well last file we process
# if oldDir!=newDir:
# if verbose: print "Remove",newFileName
# try:
# os.chmod(newFileName,0644)
# os.remove(newFileName)
# except:
# pass
# let's rollback
if verbose: print "Invoke rollback"
dbOut.rollback()
esManager.rollback()
status = es_init.ESOutput(0,userCommand,historyFile,outputLog,globalLog)
return status
#if dbOutType=="sqlite":
# query="COMMIT"
# sqlOut.updateDBAndLog(query)
query = """SELECT fileName,type FROM FileID,FileType WHERE
FileID.typeId=FileType.id"""
tupOut= sqlOut.fetchAll(query)
dbOut.commit()
esManager.unlockTables()
esManager.close()
# everything is fine and we may clean-up original files
query = """SELECT fileName,type FROM FileID,FileType WHERE
FileID.typeId=FileType.id"""
tupIn = sqlIn.fetchAll(query)
listOut = list(tupOut)
for item in tupIn:
if listOut.count(item): continue
fileName = item[0]
fileType = item[1]
if fileType=='ikey' or fileType=='levio' or fileType=='lhddm':
if verbose: print "Remove",fileName
try:
os.remove(fileName)
except:
pass
returnStatus = es_init.ESOutput(1,userCommand,historyFile,outputLog,globalLog)
return returnStatus