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