def countEvents(keyFileName, quiet=1, whereToWrite="null"):
"""Event counter method, it counts a number of events presented in
a key file"""
if whereToWrite == "null":
# Redirect the standard file descriptors to /dev/null.
sys.stdin.close()
sys.stdout = NullDevice()
sys.stderr = NullDevice()
try:
position, needToSwap, fileUid, nSyncValues = keyFileHeaderReader(
keyFileName)
except:
print "Fail to parse header of", keyFileName
gen_util.printExcept()
return 0 # error
keyFile = open(keyFileName, 'rb')
keyFile.seek(position)
if needToSwap:
print "File was produced on another endian machine, byte swapping is enabled"
firstSV = ""
lastSV = ""
nSV = 0
svRecordList = []
maxIdx = -1
magicNumber = 4294967295 # 2*32-1
while 1:
try:
sv = array.array('I')
sv.fromfile(keyFile, 6)
run = sv[0]
evt = sv[1]
uid = sv[2]
nSV += 1
print "%d/%d/%d" % (run, evt, uid)
if not len(firstSV):
firstSV = "%d/%d/%d" % (run, evt, uid)
lastSV = "%d/%d/%d" % (run, evt, uid)
except EOFError:
break
keyFile.close()
if nSV == 0:
raise "no events found"
# Redirect the standard file descriptors to default I/O
sys.stdin = sys.__stdin__
sys.stdout = sys.__stdout__
sys.stderr = sys.__stderr__
return nSV
def countEvents(keyFileName,quiet=1,whereToWrite="null"):
"""Event counter method, it counts a number of events presented in
a key file"""
if whereToWrite=="null":
# Redirect the standard file descriptors to /dev/null.
sys.stdin.close()
sys.stdout = NullDevice()
sys.stderr = NullDevice()
try:
position,needToSwap,fileUid,nSyncValues=keyFileHeaderReader(keyFileName)
except:
print "Fail to parse header of",keyFileName
gen_util.printExcept()
return 0 # error
keyFile = open(keyFileName,'rb')
keyFile.seek(position)
if needToSwap:
print "File was produced on another endian machine, byte swapping is enabled"
firstSV = ""
lastSV = ""
nSV = 0
svRecordList = []
maxIdx =-1
magicNumber = 4294967295 # 2*32-1
while 1:
try:
sv = array.array('I')
sv.fromfile(keyFile,6)
run = sv[0]
evt = sv[1]
uid = sv[2]
nSV+=1
print "%d/%d/%d"%(run,evt,uid)
if not len(firstSV):
firstSV="%d/%d/%d"%(run,evt,uid)
lastSV="%d/%d/%d"%(run,evt,uid)
except EOFError:
break
keyFile.close()
if nSV == 0:
raise "no events found"
# Redirect the standard file descriptors to default I/O
sys.stdin = sys.__stdin__
sys.stdout = sys.__stdout__
sys.stderr = sys.__stderr__
return nSV
def connect(dbHost,dbPort,dbName,userName="",userPass=""):
"""Establish connection to MySQL db"""
try:
userName, userPass = esdb_auth.authToESMySQL(dbHost,userName,userPass,"adminLogin")
db = MySQLdb.connect(host=dbHost,port=dbPort,user=userName,passwd=userPass,db=dbName)
except:
print "WARNING: First attempt to connect to %s fail, try again"%dbHost
time.sleep(60)
try:
db = MySQLdb.connect(host=dbHost,port=dbPort,user=userName,passwd=userPass,db=dbName)
except:
print "ERROR: Second attemp to connect to %s fail, please investigate"%dbHost
gen_util.printExcept()
sys.exit(1)
return db
def endTxn(self,msg=""):
"""Update EventStore db log and invoke COMMIT transaction. In the case
of SQLite we use DB-API db.commit() to commit our transactions. Please note
that SQLite only support DB locking."""
if self.dbType=="mysql":
if self.commitFlag:
self.updateDBAndLog("COMMIT")
elif self.dbType=="sqlite":
if self.commitFlag:
if self.db.isolation_level:
return
query="COMMIT"
counter=0
cu = self.db.cursor()
while 1:
if counter>10: break
try:
self.db.commit() ## sdobbs, 4/19/14
# cu.execute(query)
# self.cursor.execute(query)
self.updateLog(query)
break
except:
print "Wait for COMMIT"
gen_util.printExcept()
pass
counter+=1
cu.close()
# self.db.commit()
# cu = self.db.cursor()
# cu.execute(query)
# cu.close()
# self.db.commit()
# self.updateLog(query)
if self.verbose and msg:
message = " End transaction: "+msg
message+=", commit flag=%s"%self.commitFlag
if self.dbType=='sqlite':
message+=", %s"%self.db.isolation_level
print message
print
sys.__stdout__.flush()
def endTxn(self, msg=""):
"""Update EventStore db log and invoke COMMIT transaction. In the case
of SQLite we use DB-API db.commit() to commit our transactions. Please note
that SQLite only support DB locking."""
if self.dbType == "mysql":
if self.commitFlag:
self.updateDBAndLog("COMMIT")
elif self.dbType == "sqlite":
if self.commitFlag:
if self.db.isolation_level:
return
query = "COMMIT"
counter = 0
cu = self.db.cursor()
while 1:
if counter > 10: break
try:
self.db.commit() ## sdobbs, 4/19/14
# cu.execute(query)
# self.cursor.execute(query)
self.updateLog(query)
break
except:
print "Wait for COMMIT"
gen_util.printExcept()
pass
counter += 1
cu.close()
# self.db.commit()
# cu = self.db.cursor()
# cu.execute(query)
# cu.close()
# self.db.commit()
# self.updateLog(query)
if self.verbose and msg:
message = " End transaction: " + msg
message += ", commit flag=%s" % self.commitFlag
if self.dbType == 'sqlite':
message += ", %s" % self.db.isolation_level
print message
print
sys.__stdout__.flush()
def moveFileInES(self,fileIn,fileOut):
"""Move a single fileIn to fileOut destination. First update FileID table and
then physically copy file to new location.
@type fileIn: string
@param fileIn: file name
@type fileOut: string
@param fileOut: file name
@rtype: tuple
@return: (status code, query, compensatingQuery), where query is SQL query how
to update FileID table. It's printed out by L{moveFilesInES} in the case of
failure for debugging purpose.
"""
query = ""
cQuery= ""
# I need to add log what I'm doing since updating DB and
# moving/copying file are independent operations
if self.verbose:
print "Using moveFileInES"
print "Attempt to move %s to %s"%(fileIn,fileOut)
if os.path.isfile(fileIn):
query = "SELECT fileId FROM FileID WHERE fileName='%s'"%fileIn
tup = self.fetchOne(query)
if tup and len(tup):
id = tup[0]
moveTo = fileOut
if os.path.isdir(fileOut):
moveTo = os.path.join(fileOut,os.path.split(fileIn)[1])
# first copy file to new location
try:
shutil.copy2(fileIn,fileOut)
except:
gen_util.printExcept()
print "Cannot copy %s to %s"%(fileIn,fileOut)
return (self.error, "N/A", "N/A")
query = "UPDATE FileID SET fileName='%s' WHERE fileID='%s'"%(moveTo,id)
cQuery= "UPDATE FileID SET fileName='%s' WHERE fileID='%s'"%(fileIn,id)
try:
self.startTxn()
self.updateDBAndLog(query)
self.endTxn()
if self.verbose:
print query
except:
gen_util.printExcept()
print "Fail to update DB with the following query"
print query
print "------------------------------------------"
print "You MUST invoke the following command to compensate"
print cQuery
return (self.error, query, cQuery)
try:
os.remove(fileIn)
if self.verbose:
print "File %s has been successfully moved"%fileIn
except:
gen_util.printExcept()
print "WARNING: cannot remove %s"%fileIn
return (self.ok, query, cQuery)
return (self.ok, query, cQuery)
return (self.error, query, cQuery) # error
def startTxn(self,msg=""):
"""Update EventStore db log and invoke BEGIN transaction. In the case
of SQLite we rely on IMMEDIATE transaction mechanism which locks DB for
that transaction."""
if self.verbose and msg:
message ="Start transaction: "+msg
message+=", commit flag=%s"%self.commitFlag
if self.dbType=='sqlite':
message+=", %s"%self.db.isolation_level
print message
sys.__stdout__.flush()
if self.dbType=="mysql":
if self.commitFlag:
self.updateDBAndLog("BEGIN")
elif self.dbType=="sqlite":
if not self.commitFlag:
return
if self.db.isolation_level:
return
counter=0
query = "BEGIN IMMEDIATE"
cu = self.db.cursor()
while 1:
if counter>10: break
try:
cu.execute(query)
# self.cursor.execute(query)
self.updateLog(query)
break
except:
print "Wait for BEGIN"
gen_util.printExcept()
pass
counter+=1
cu.close()
return
def startTxn(self, msg=""):
"""Update EventStore db log and invoke BEGIN transaction. In the case
of SQLite we rely on IMMEDIATE transaction mechanism which locks DB for
that transaction."""
if self.verbose and msg:
message = "Start transaction: " + msg
message += ", commit flag=%s" % self.commitFlag
if self.dbType == 'sqlite':
message += ", %s" % self.db.isolation_level
print message
sys.__stdout__.flush()
if self.dbType == "mysql":
if self.commitFlag:
self.updateDBAndLog("BEGIN")
elif self.dbType == "sqlite":
if not self.commitFlag:
return
if self.db.isolation_level:
return
counter = 0
query = "BEGIN IMMEDIATE"
cu = self.db.cursor()
while 1:
if counter > 10: break
try:
cu.execute(query)
# self.cursor.execute(query)
self.updateLog(query)
break
except:
print "Wait for BEGIN"
gen_util.printExcept()
pass
counter += 1
cu.close()
return
def connect(dbHost, dbPort, dbName, userName="", userPass=""):
"""Establish connection to MySQL db"""
try:
userName, userPass = esdb_auth.authToESMySQL(dbHost, userName,
userPass, "adminLogin")
db = MySQLdb.connect(host=dbHost,
port=dbPort,
user=userName,
passwd=userPass,
db=dbName)
except:
print "WARNING: First attempt to connect to %s fail, try again" % dbHost
time.sleep(60)
try:
db = MySQLdb.connect(host=dbHost,
port=dbPort,
user=userName,
passwd=userPass,
db=dbName)
except:
print "ERROR: Second attemp to connect to %s fail, please investigate" % dbHost
gen_util.printExcept()
sys.exit(1)
return db
def moveFileInES(self, fileIn, fileOut):
"""Move a single fileIn to fileOut destination. First update FileID table and
then physically copy file to new location.
@type fileIn: string
@param fileIn: file name
@type fileOut: string
@param fileOut: file name
@rtype: tuple
@return: (status code, query, compensatingQuery), where query is SQL query how
to update FileID table. It's printed out by L{moveFilesInES} in the case of
failure for debugging purpose.
"""
query = ""
cQuery = ""
# I need to add log what I'm doing since updating DB and
# moving/copying file are independent operations
if self.verbose:
print "Using moveFileInES"
print "Attempt to move %s to %s" % (fileIn, fileOut)
if os.path.isfile(fileIn):
query = "SELECT fileId FROM FileID WHERE fileName='%s'" % fileIn
tup = self.fetchOne(query)
if tup and len(tup):
id = tup[0]
moveTo = fileOut
if os.path.isdir(fileOut):
moveTo = os.path.join(fileOut, os.path.split(fileIn)[1])
# first copy file to new location
try:
shutil.copy2(fileIn, fileOut)
except:
gen_util.printExcept()
print "Cannot copy %s to %s" % (fileIn, fileOut)
return (self.error, "N/A", "N/A")
query = "UPDATE FileID SET fileName='%s' WHERE fileID='%s'" % (
moveTo, id)
cQuery = "UPDATE FileID SET fileName='%s' WHERE fileID='%s'" % (
fileIn, id)
try:
self.startTxn()
self.updateDBAndLog(query)
self.endTxn()
if self.verbose:
print query
except:
gen_util.printExcept()
print "Fail to update DB with the following query"
print query
print "------------------------------------------"
print "You MUST invoke the following command to compensate"
print cQuery
return (self.error, query, cQuery)
try:
os.remove(fileIn)
if self.verbose:
print "File %s has been successfully moved" % fileIn
except:
gen_util.printExcept()
print "WARNING: cannot remove %s" % fileIn
return (self.ok, query, cQuery)
return (self.ok, query, cQuery)
return (self.error, query, cQuery) # error
def dump(keyFileName, verbose=1, whereToWrite="std"):
"""Dump content of a key file. The output can be redirected to /dev/null
or stdout."""
if whereToWrite == "null":
# Redirect the standard file descriptors to /dev/null.
sys.stdin.close()
sys.stdout = NullDevice()
sys.stderr = NullDevice()
try:
position, needToSwap, fileUid, nSyncValues = keyFileHeaderReader(
keyFileName)
except:
print "Fail to parse header of", keyFileName
gen_util.printExcept()
return 0 # error
keyFile = open(keyFileName, 'rb')
keyFile.seek(position)
if needToSwap:
print "File was produced on another endian machine, byte swapping is enabled"
firstSV = ""
lastSV = ""
nSV = 0
svRecordList = []
maxIdx = -1
magicNumber = 4294967295 # 2*32-1
while 1:
try:
sv = array.array('I')
sv.fromfile(keyFile, 6)
run = sv[0]
evt = sv[1]
uid = sv[2]
svRecordList.append((run, evt, uid))
nSV += 1
if not len(firstSV):
firstSV = "%d/%d/%d" % (run, evt, uid)
lastSV = "%d/%d/%d" % (run, evt, uid)
if verbose:
print "********"
print "SyncValue: " + str(run) + " " + str(evt) + " " + str(
uid)
except EOFError:
break
keyFile.close()
if nSV == 0:
raise "no events found"
print "First sync value :", firstSV
print " Last sync value :", lastSV
print "Number of syncValues :", nSV
print "Number of sv in header:", nSyncValues
print
if nSyncValues != nSV:
raise Exception("nSyncValues in header doesn't match number found")
# Redirect the standard file descriptors to default I/O
sys.stdin = sys.__stdin__
sys.stdout = sys.__stdout__
sys.stderr = sys.__stderr__
return svRecordList
checkArg([timeS])
x+=2
continue
if sys.argv[x]=="-dataVersionName":
svName = sys.argv[x+1]
checkArg([svName])
x+=2
continue
# if we reach here, that means we found unkown option
if dictOpt.has_key(sys.argv[x]):
x+=dictOpt[sys.argv[x]]
else:
msg="Option '%s' is not allowed"%sys.argv[x]
raise msg
except:
gen_util.printExcept()
sys.exit(1)
if not grade:
print "You need to provide a grade"
print usage
sys.exit(1)
if not timeS:
print "You need to provide a time stamp"
print usage
sys.exit(1)
pid = "%s"%os.getpid()
localtime = "%s"%time.strftime("%H:%M:%S",time.localtime())
# connect to dbIn EventStore
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
checkArg([timeS])
x += 2
continue
if sys.argv[x] == "-dataVersionName":
svName = sys.argv[x + 1]
checkArg([svName])
x += 2
continue
# if we reach here, that means we found unkown option
if dictOpt.has_key(sys.argv[x]):
x += dictOpt[sys.argv[x]]
else:
msg = "Option '%s' is not allowed" % sys.argv[x]
raise msg
except:
gen_util.printExcept()
sys.exit(1)
if not grade:
print "You need to provide a grade"
print usage
sys.exit(1)
if not timeS:
print "You need to provide a time stamp"
print usage
sys.exit(1)
pid = "%s" % os.getpid()
localtime = "%s" % time.strftime("%H:%M:%S", time.localtime())
# connect to dbIn EventStore
def ESFixPath(args):
"""Fix paths in EventStoreDB. The CLEOc data path specifications:
/cleo/{detector,simulated}/{event,calibration}/{daq,pass2_version}/123400/123456/{specific_version_path}
"""
localOpt = ["[ -prefix <Site CLEOc data prefix, e.g. /cdat> ]"]
usage = es_init.helpMsg("ESFixPath",localOpt)
usageDescription=""
examples="""
"""
userCommand="ESFixPath.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]
prefix = "/cdat/" ### CHECK
x = 1
while x < len(args):
try:
if string.lower(args[x]) == "-prefix":
prefix = args[x+1]
x+=2
continue
if dictOpt.has_key(args[x]):
x+=dictOpt[args[x]]
else:
print "Option '%s' is not allowed"%args[x]
raise
except:
sys.exit(1)
if prefix[0]!="/":
print "Prefix should start with /"
print usage
sys.exit(1)
# initialize log
outputLog, globalLog = es_init.ESOutputLog(logFile)
# connect to dbIn EventStore
db, dbType = es_init.ESDBConnector(dbHost,dbName,userName,userPass)
sql = sql_util.SQLUtil(db,dbType,outputLog)
tableList = sql.getTables()
# Initialize ESManager for dbOut
es_init.ESInput(userCommand,outputLog,dbType)
# lock all tables for write operation in dbOut
sql.lockTables()
# postpone all commits to DB, once job will finish we'll invoke commit.
sql.setCommitFlag(0)
sql.startTxn()
try:
# read all releases (from Solaris) which would be used to find out pass2_version
relList = os.listdir(RELPATH)
# start processing
print "Processing:"
sys.__stdout__.flush()
# we can replace retrieval of all files from DB, by using min/max fileId and then
# loop over file Ids.
# query="SELECT fileId,fileName FROM FileID"
# tup = sql.fetchAll(query)
# for item in tup:
# fileId = item[0]
# file = item[1]
query="SELECT MIN(fileId),MAX(fileId) FROM FileID"
tup = sql.fetchOne(query)
minId= long(tup[0])
maxId= long(tup[1])+1
for fileId in xrange(minId,maxId):
query = "SELECT fileName FROM FileID WHERE fileId='%s'"%fileId
tup = sql.fetchOne(query)
if not tup: continue
file = tup[0]
if not os_path_util.isFile(file):
print "Found non existing file",file
continue
# Open KeyFile table, locate fileId, graphid=>svName (with all parents)
# create a new link
keyFile= file
query ="SELECT run,graphid FROM KeyFile WHERE keyFileId='%s'"%fileId
tup = sql.fetchAll(query)
for item in tup:
run = item[0]
gid = item[1]
query="""SELECT svName FROM SpecificVersion,GraphPath
WHERE GraphPath.svid=SpecificVersion.svid
AND GraphPath.graphid='%s'"""%gid
tup = sql.fetchOne(query) # FIXME, there're many svid's assigned to gid
svName = tup[0]
dir,fileName = os.path.split(keyFile)
dList,idList,dict,dictId,graph=sql.getAllParents(svName)
parentList = newParentList(sql,run,dList)
if string.lower(svName)=='daq':
release = 'daq'
else:
release = getRelease(relList,svName)
newPath = formNewPath(prefix,run,release,svName,parentList)
newDir = os.path.join(newPath,'index')
if not os.path.isdir(newDir):
os.makedirs(newDir)
newFile = os.path.join(newDir,fileName)
print "Link (key)",newFile,"=>",keyFile
sys.__stdout__.flush()
if not os_path_util.isFile(newFile):
os.symlink(file,newFile)
# change db entry
query="UPDATE FileID SET fileName='%s' WHERE fileId='%s'"%(newFile,fileId)
sql.updateDBAndLog(query)
# Open Location table, locate fileId, graphid=>svName and open
# loc. file header to locate data files
locFile = file
query = "SELECT run,graphid FROM Location WHERE locationFileId='%s'"%fileId
tup = sql.fetchAll(query)
for item in tup:
run = item[0]
gid = item[1]
query="""SELECT svName FROM SpecificVersion,GraphPath
WHERE GraphPath.svid=SpecificVersion.svid
AND GraphPath.graphid='%s'"""%gid
tup = sql.fetchOne(query) # FIXME, there're many svid's assigned to gid
svName = tup[0]
dir,fileName = os.path.split(locFile)
dList,idList,dict,dictId,graph=sql.getAllParents(svName)
parentList = newParentList(sql,run,dList)
if string.lower(svName)=='daq':
release = 'daq'
else:
release = getRelease(relList,svName)
newPath = formNewPath(prefix,run,release,svName,parentList)
newDir = os.path.join(newPath,'index')
if not os.path.isdir(newDir):
os.makedirs(newDir)
newFile = os.path.join(newDir,fileName)
print "Link (loc)",newFile,"=>",locFile
sys.__stdout__.flush()
if not os_path_util.isFile(newFile):
os.symlink(locFile,newFile)
# change db entry
query="UPDATE FileID SET fileName='%s' WHERE fileId='%s'"%(newFile,fileId)
sql.updateDBAndLog(query)
# open loc. file header and get data file id's
query="SELECT fileName,fileId FROM FileID WHERE"
count=0
for id in file_util.getFileIds(locFile):
if not count:
query+= " fileId='%s'"%id
count =1
else:
query+= " OR fileId='%s'"%id
tup = sql.fetchAll(query)
for item in tup:
datFile = '%s'%item[0]
if not os_path_util.isFile(datFile):
print "Found non existing file",datFile
continue
fileId = item[1]
dir,fileName = os.path.split(datFile)
newDir = os.path.join(newPath,'data')
if not os.path.isdir(newDir):
os.makedirs(newDir)
newFile = os.path.join(newDir,fileName)
print "Link (dat)",newFile,"=>",datFile
sys.__stdout__.flush()
if not os_path_util.isFile(newFile):
os.symlink(datFile,newFile)
# change db entry
query="UPDATE FileID SET fileName='%s' WHERE fileId='%s'"%(newFile,fileId)
sql.updateDBAndLog(query)
except:
print "Caught an error during merging step."
gen_util.printExcept()
db.rollback()
return
# everything is ready for commit
sql.setCommitFlag(1)
sql.endTxn()
sql.commit()
sql.unlockTables()
sql.close()
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
def ESDBConnector(dbHost,dbName,userName="",userPass="",isolationLevel="",dbPort="",dbSocket=""):
"""ESDBConnector connect to given host(MySQL) and/or file(SQLite) using dbName.
In the cae of SQLite user may pass isolationLevel:
- default mode, if no isolationLevel is passed will start db with BEGIN
- autocommit mode, isolationLeve='None'
- you may also pass: DEFERRED, IMMEDIATE or EXCLUSIVE levels
- DEFERRED deffer locks until first access
- IMMEDIATE implies lock for first BEGIN IMMEDIATE and doesn't allow
anything to write to DB, but allow to read
- EXCLUSIVE will lock entire DB.
We use IMMEDIATE for SQLite and READ COMMITTED isolation level for MySQL.
We also turn off autocommit.
@type dbHost: string
@type dbName: string
@type userName: string
@type userPass: string
@type isolationLevel: string
@type dbPort: integer
@type dbSocket: string
@param dbHost: name of the host, lnx151.lns.cornell.edu or db file name /sqlite.db
@param dbName: DB name, obsolete in the case of SQLite
@param userName: user name
@param userPass: password
@param isolationLevel: valid only for SQLite, setup isolation level
@param dbPort: port number, e.g. default for MysQL is 3306
@param dbSocket: socket name, e.g. /var/log/mysql=
@rtype: tuple (db, dbType)
@return: return object to underlying DB and its type, e.g. "sqlite" or "mysql"
"""
sqLite = 0
try:
ipAddress = socket.gethostbyname(dbHost)
except:
# if name resolution failed we assume that our dbHost is flat file name
curDir = os.path.split(dbHost)[0]
if not curDir: curDir=os.getcwd()
dirList = os.listdir(curDir)
if os.path.isfile(dbHost) or not dirList.count(dbHost):
sqLite=1
else:
print "Cannot resolve dbHost='%s'"%dbHost
sys.exit(1)
# read userName and password from user-specific file
if not sqLite:
# check login name and password to access EventStore MySQL DB.
userName, userPass = esdb_auth.authToESMySQL(dbHost,userName,userPass)
# connect to MySQL EventStoreDB
if sqLite:
if isolationLevel:
db = sqlite.connect(dbHost,timeout=15.0,isolation_level=isolationLevel)
else:
db = sqlite.connect(dbHost,timeout=15.0,isolation_level=None)
dbType = "sqlite"
else:
if not dbName and dbHost:
print "You are attempting to connect to host '%s'"%dbHost
print "But did not specified DB name '%s'"%dbName
sys.exit(1)
try:
if dbPort:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName,port=int(dbPort))
else:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName)
except:
print "WARNING: First attempt to connect to %s@%s:%s:%s fail, try again"%(dbName,dbHost,dbPort,dbSocket)
time.sleep(60)
try:
if dbPort:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName,port=int(dbPort))
else:
db = MySQLdb.connect(host=dbHost,user=userName,passwd=userPass,db=dbName)
except:
print "ERROR: Second attempt to connect to %s@%s:%s:%s fail, please investigate"%(dbName,dbHost,dbPort,dbSocket)
gen_util.printExcept()
sys.exit(1)
dbType = "mysql"
if userName!="eventstore": ## master DB user name
cursor = db.cursor()
# cursor.execute("SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE")
cursor.execute("SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED")
cursor.execute("SET AUTOCOMMIT=0")
cursor.close()
return (db,dbType)
def ESFixPath(args):
"""Fix paths in EventStoreDB. The CLEOc data path specifications:
/cleo/{detector,simulated}/{event,calibration}/{daq,pass2_version}/123400/123456/{specific_version_path}
"""
localOpt = ["[ -prefix <Site CLEOc data prefix, e.g. /cdat> ]"]
usage = es_init.helpMsg("ESFixPath", localOpt)
usageDescription = ""
examples = """
"""
userCommand = "ESFixPath.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]
prefix = "/cdat/" ### CHECK
x = 1
while x < len(args):
try:
if string.lower(args[x]) == "-prefix":
prefix = args[x + 1]
x += 2
continue
if dictOpt.has_key(args[x]):
x += dictOpt[args[x]]
else:
print "Option '%s' is not allowed" % args[x]
raise
except:
sys.exit(1)
if prefix[0] != "/":
print "Prefix should start with /"
print usage
sys.exit(1)
# initialize log
outputLog, globalLog = es_init.ESOutputLog(logFile)
# connect to dbIn EventStore
db, dbType = es_init.ESDBConnector(dbHost, dbName, userName, userPass)
sql = sql_util.SQLUtil(db, dbType, outputLog)
tableList = sql.getTables()
# Initialize ESManager for dbOut
es_init.ESInput(userCommand, outputLog, dbType)
# lock all tables for write operation in dbOut
sql.lockTables()
# postpone all commits to DB, once job will finish we'll invoke commit.
sql.setCommitFlag(0)
sql.startTxn()
try:
# read all releases (from Solaris) which would be used to find out pass2_version
relList = os.listdir(RELPATH)
# start processing
print "Processing:"
sys.__stdout__.flush()
# we can replace retrieval of all files from DB, by using min/max fileId and then
# loop over file Ids.
# query="SELECT fileId,fileName FROM FileID"
# tup = sql.fetchAll(query)
# for item in tup:
# fileId = item[0]
# file = item[1]
query = "SELECT MIN(fileId),MAX(fileId) FROM FileID"
tup = sql.fetchOne(query)
minId = long(tup[0])
maxId = long(tup[1]) + 1
for fileId in xrange(minId, maxId):
query = "SELECT fileName FROM FileID WHERE fileId='%s'" % fileId
tup = sql.fetchOne(query)
if not tup: continue
file = tup[0]
if not os_path_util.isFile(file):
print "Found non existing file", file
continue
# Open KeyFile table, locate fileId, graphid=>svName (with all parents)
# create a new link
keyFile = file
query = "SELECT run,graphid FROM KeyFile WHERE keyFileId='%s'" % fileId
tup = sql.fetchAll(query)
for item in tup:
run = item[0]
gid = item[1]
query = """SELECT svName FROM SpecificVersion,GraphPath
WHERE GraphPath.svid=SpecificVersion.svid
AND GraphPath.graphid='%s'""" % gid
tup = sql.fetchOne(
query) # FIXME, there're many svid's assigned to gid
svName = tup[0]
dir, fileName = os.path.split(keyFile)
dList, idList, dict, dictId, graph = sql.getAllParents(svName)
parentList = newParentList(sql, run, dList)
if string.lower(svName) == 'daq':
release = 'daq'
else:
release = getRelease(relList, svName)
newPath = formNewPath(prefix, run, release, svName, parentList)
newDir = os.path.join(newPath, 'index')
if not os.path.isdir(newDir):
os.makedirs(newDir)
newFile = os.path.join(newDir, fileName)
print "Link (key)", newFile, "=>", keyFile
sys.__stdout__.flush()
if not os_path_util.isFile(newFile):
os.symlink(file, newFile)
# change db entry
query = "UPDATE FileID SET fileName='%s' WHERE fileId='%s'" % (
newFile, fileId)
sql.updateDBAndLog(query)
# Open Location table, locate fileId, graphid=>svName and open
# loc. file header to locate data files
locFile = file
query = "SELECT run,graphid FROM Location WHERE locationFileId='%s'" % fileId
tup = sql.fetchAll(query)
for item in tup:
run = item[0]
gid = item[1]
query = """SELECT svName FROM SpecificVersion,GraphPath
WHERE GraphPath.svid=SpecificVersion.svid
AND GraphPath.graphid='%s'""" % gid
tup = sql.fetchOne(
query) # FIXME, there're many svid's assigned to gid
svName = tup[0]
dir, fileName = os.path.split(locFile)
dList, idList, dict, dictId, graph = sql.getAllParents(svName)
parentList = newParentList(sql, run, dList)
if string.lower(svName) == 'daq':
release = 'daq'
else:
release = getRelease(relList, svName)
newPath = formNewPath(prefix, run, release, svName, parentList)
newDir = os.path.join(newPath, 'index')
if not os.path.isdir(newDir):
os.makedirs(newDir)
newFile = os.path.join(newDir, fileName)
print "Link (loc)", newFile, "=>", locFile
sys.__stdout__.flush()
if not os_path_util.isFile(newFile):
os.symlink(locFile, newFile)
# change db entry
query = "UPDATE FileID SET fileName='%s' WHERE fileId='%s'" % (
newFile, fileId)
sql.updateDBAndLog(query)
# open loc. file header and get data file id's
query = "SELECT fileName,fileId FROM FileID WHERE"
count = 0
for id in file_util.getFileIds(locFile):
if not count:
query += " fileId='%s'" % id
count = 1
else:
query += " OR fileId='%s'" % id
tup = sql.fetchAll(query)
for item in tup:
datFile = '%s' % item[0]
if not os_path_util.isFile(datFile):
print "Found non existing file", datFile
continue
fileId = item[1]
dir, fileName = os.path.split(datFile)
newDir = os.path.join(newPath, 'data')
if not os.path.isdir(newDir):
os.makedirs(newDir)
newFile = os.path.join(newDir, fileName)
print "Link (dat)", newFile, "=>", datFile
sys.__stdout__.flush()
if not os_path_util.isFile(newFile):
os.symlink(datFile, newFile)
# change db entry
query = "UPDATE FileID SET fileName='%s' WHERE fileId='%s'" % (
newFile, fileId)
sql.updateDBAndLog(query)
except:
print "Caught an error during merging step."
gen_util.printExcept()
db.rollback()
return
# everything is ready for commit
sql.setCommitFlag(1)
sql.endTxn()
sql.commit()
sql.unlockTables()
sql.close()
returnStatus = es_init.ESOutput(1, userCommand, historyFile, outputLog,
globalLog)
return returnStatus
def dump(keyFileName,verbose=1,whereToWrite="std"):
"""Dump content of a key file. The output can be redirected to /dev/null
or stdout."""
if whereToWrite=="null":
# Redirect the standard file descriptors to /dev/null.
sys.stdin.close()
sys.stdout = NullDevice()
sys.stderr = NullDevice()
try:
position,needToSwap,fileUid,nSyncValues=keyFileHeaderReader(keyFileName)
except:
print "Fail to parse header of",keyFileName
gen_util.printExcept()
return 0 # error
keyFile = open(keyFileName,'rb')
keyFile.seek(position)
if needToSwap:
print "File was produced on another endian machine, byte swapping is enabled"
firstSV = ""
lastSV = ""
nSV = 0
svRecordList = []
maxIdx =-1
magicNumber = 4294967295 # 2*32-1
while 1:
try:
sv = array.array('I')
sv.fromfile(keyFile,6)
run = sv[0]
evt = sv[1]
uid = sv[2]
svRecordList.append( ( run,evt,uid ) )
nSV+=1
if not len(firstSV):
firstSV="%d/%d/%d"%(run,evt,uid)
lastSV="%d/%d/%d"%(run,evt,uid)
if verbose:
print "********"
print "SyncValue: "+str(run)+" "+str(evt)+" "+str(uid)
except EOFError:
break
keyFile.close()
if nSV == 0:
raise "no events found"
print "First sync value :",firstSV
print " Last sync value :",lastSV
print "Number of syncValues :",nSV
print "Number of sv in header:",nSyncValues
print
if nSyncValues != nSV:
raise Exception("nSyncValues in header doesn't match number found")
# Redirect the standard file descriptors to default I/O
sys.stdin = sys.__stdin__
sys.stdout = sys.__stdout__
sys.stderr = sys.__stderr__
return svRecordList
