def dropIndex(dbsession): dbsession.transaction().start(False) schema=dbsession.nominalSchema() schema.tableHandle( nameDealer.lumisummaryTableName() ).schemaEditor().dropIndex('lumisummary_runnum') schema.tableHandle( nameDealer.trgTableName() ).schemaEditor().dropIndex('trg_runnum') schema.tableHandle( nameDealer.hltTableName() ).schemaEditor().dropIndex('hlt_runnum') dbsession.transaction().commit()
def runListInDB(dbsession,lumiversion=''): """ list available runs in the DB output: [runnumber] """ runlist=[] try: dbsession.transaction().start(True) schema=dbsession.nominalSchema() query=schema.tableHandle(nameDealer.lumisummaryTableName()).newQuery() query.addToOutputList("RUNNUM","run") query.addToOutputList("LUMIVERSION","lumiversion") queryBind=coral.AttributeList() queryBind.extend("lumiversion","string") if len(lumiversion)!=0: queryBind["lumiversion"].setData(lumiversion) query.setCondition("LUMIVERSION=:lumiversion",queryBind) query.addToOrderList('RUNNUM') result=coral.AttributeList() result.extend("run","unsigned int") result.extend("lumiversion","string") query.defineOutput(result) cursor=query.execute() while cursor.next(): r=cursor.currentRow()['run'].data() v=cursor.currentRow()['lumiversion'].data() runlist.append((r,v)) del query dbsession.transaction().commit() except Exception,e: print str(e) dbsession.transaction().rollback() del dbsession
def createIndex(dbsession): dbsession.transaction().start(False) schema = dbsession.nominalSchema() schema.tableHandle(nameDealer.lumisummaryTableName()).schemaEditor().createIndex("lumisummary_runnum", ("RUNNUM")) schema.tableHandle(nameDealer.trgTableName()).schemaEditor().createIndex("trg_runnum", ("RUNNUM")) schema.tableHandle(nameDealer.hltTableName()).schemaEditor().createIndex("hlt_runnum", ("RUNNUM")) dbsession.transaction().commit()
def deliveredLumiForRun(self,runnum): # #select sum(INSTLUMI),count(INSTLUMI) from lumisummary where runnum=124025 and lumiversion='0001'; #apply norm factor and ls length in sec on the query result #unit E27cm^-2 # #if c.VERBOSE: # print 'deliveredLumiForRun : norm : ',c.NORM,' : run : ',runnum #output ['run','totalls','delivered','beammode'] c = self._c dbsession = self._session delivered=0.0 totalls=0 try: dbsession.transaction().start(True) schema=dbsession.nominalSchema() query=schema.tableHandle(nameDealer.lumisummaryTableName()).newQuery() query.addToOutputList("sum(INSTLUMI)","totallumi") query.addToOutputList("count(INSTLUMI)","totalls") query.addToOutputList("NUMORBIT","norbits") queryBind=coral.AttributeList() queryBind.extend("runnum","unsigned int") queryBind.extend("lumiversion","string") queryBind["runnum"].setData(int(runnum)) queryBind["lumiversion"].setData(c.LUMIVERSION) result=coral.AttributeList() result.extend("totallumi","float") result.extend("totalls","unsigned int") result.extend("norbits","unsigned int") query.defineOutput(result) query.setCondition("RUNNUM =:runnum AND LUMIVERSION =:lumiversion",queryBind) query.limitReturnedRows(1) query.groupBy('NUMORBIT') cursor=query.execute() while cursor.next(): delivereddata=cursor.currentRow()['totallumi'].data() totallsdata=cursor.currentRow()['totalls'].data() norbitsdata=cursor.currentRow()['norbits'].data() if delivereddata: totalls=totallsdata norbits=norbitsdata lstime=self.lslengthsec(norbits,c.NBX) delivered=delivereddata*c.NORM*lstime del query dbsession.transaction().commit() lumidata=[] if delivered==0.0: lumidata=[str(runnum),'N/A','N/A','N/A'] else: lumidata=[str(runnum),str(totalls),'%.3f'%delivered,c.BEAMMODE] return lumidata except Exception,e: print str(e) dbsession.transaction().rollback() del dbsession
def dropIndex(dbsession): dbsession.transaction().start(False) schema = dbsession.nominalSchema() schema.tableHandle( nameDealer.lumisummaryTableName()).schemaEditor().dropIndex( 'lumisummary_runnum') schema.tableHandle( nameDealer.trgTableName()).schemaEditor().dropIndex('trg_runnum') schema.tableHandle( nameDealer.hltTableName()).schemaEditor().dropIndex('hlt_runnum') dbsession.transaction().commit()
def dropTables(schema,tablelist): try: db=dbUtil.dbUtil(schema) for tablename in tablelist: if tablename in [nameDealer.luminormTableName(),nameDealer.lumidataTableName(),nameDealer.trgdataTableName(),nameDealer.hltdataTableName()]: db.dropTable( nameDealer.idTableName(tablename) ) db.dropTable( nameDealer.entryTableName(tablename) ) db.dropTable( nameDealer.revmapTableName(tablename) ) if tablename in [nameDealer.trgTableName(),nameDealer.lumisummaryTableName(),nameDealer.lumisummaryv2TableName(),nameDealer.lumidetailTableName(),nameDealer.hltTableName()]: db.dropTable( nameDealer.idTableName(tablename) ) db.dropTable( tablename ) except : raise
def deliveredLumiForRunSection(dbsession,c,runnum,section): delivered=0.0 totalls=0 try: dbsession.transaction().start(True) schema=dbsession.nominalSchema() query=schema.newQuery() query.addToTableList(nameDealer.lumidetailTableName(),'lumidetail') query.addToTableList(nameDealer.lumisummaryTableName(),'lumisummary') queryCondition=coral.AttributeList() queryCondition.extend("runnumber","unsigned int") queryCondition.extend("section","unsigned int") queryCondition.extend("lumiversion","string") queryCondition["runnumber"].setData(int(runnum)) queryCondition["section"].setData(int(section)) queryCondition["lumiversion"].setData(c.LUMIVERSION) query.setCondition("lumisummary.RUNNUM=:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.LUMILSNUM=:section and lumisummary.LUMISUMMARY_ID=lumidetail.LUMISUMMARY_ID",queryCondition) query.addToOutputList("lumisummary.LUMILSNUM","lumils") query.addToOutputList("lumisummary.INSTLUMI","instlumi") query.addToOutputList("lumisummary.NUMORBIT","norbits") query.addToOutputList("lumidetail.BXLUMIVALUE","lumivalue") result=coral.AttributeList() result.extend("lumils","unsigned int") result.extend("instlumi","float") result.extend("norbits","unsigned int") result.extend("lumivalue","blob") query.defineOutput(result) cursor=query.execute() while cursor.next(): cmsls=cursor.currentRow()["lumils"].data() instlumi=cursor.currentRow()["instlumi"].data()*c.NORM norbits=cursor.currentRow()["norbits"].data() lumivalue=cursor.currentRow()["lumivalue"].data() lumibytes=lumivalue.readline() print "read bytes:", len(lumibytes) buffer=array.array('f') buffer.fromstring(lumibytes) print "got floats :", len(buffer) print "deliveredLumiForRunSection", runnum, section, cmsls, instlumi, norbits, lumivalue for ix in range(len(buffer)): if buffer[ix]>1.e-4: print ix, buffer[ix] cursor.close() del query dbsession.transaction().commit() except Exception,e: print str(e) dbsession.transaction().rollback() del dbsession
def dropLumi(dbsession): print('droping lumi db schema...') dbsession.transaction().start(False) schema = dbsession.nominalSchema() db = dbUtil.dbUtil(schema) db.dropTable(nameDealer.lumidetailTableName()) db.dropTable(nameDealer.cmsrunsummaryTableName()) db.dropTable(nameDealer.lumisummaryTableName()) db.dropTable(nameDealer.trgTableName()) db.dropTable(nameDealer.hltTableName()) db.dropTable(nameDealer.trghltMapTableName()) db.dropTable(nameDealer.lumiresultTableName()) db.dropTable(nameDealer.lumihltresultTableName()) db.dropTable(nameDealer.lumivalidationTableName()) dbsession.transaction().commit()
def dropLumi(dbsession): print('droping lumi db schema...') dbsession.transaction().start(False) schema=dbsession.nominalSchema() db=dbUtil.dbUtil(schema) db.dropTable( nameDealer.lumidetailTableName() ) db.dropTable( nameDealer.cmsrunsummaryTableName() ) db.dropTable( nameDealer.lumisummaryTableName() ) db.dropTable( nameDealer.trgTableName() ) db.dropTable( nameDealer.hltTableName() ) db.dropTable( nameDealer.trghltMapTableName() ) db.dropTable( nameDealer.lumiresultTableName() ) db.dropTable( nameDealer.lumihltresultTableName() ) db.dropTable( nameDealer.lumivalidationTableName() ) dbsession.transaction().commit()
def dropTables(schema, tablelist): try: db = dbUtil.dbUtil(schema) for tablename in tablelist: if tablename in [ nameDealer.luminormTableName(), nameDealer.lumidataTableName(), nameDealer.trgdataTableName(), nameDealer.hltdataTableName() ]: db.dropTable(nameDealer.idTableName(tablename)) db.dropTable(nameDealer.entryTableName(tablename)) db.dropTable(nameDealer.revmapTableName(tablename)) if tablename in [ nameDealer.trgTableName(), nameDealer.lumisummaryTableName(), nameDealer.lumisummaryv2TableName(), nameDealer.lumidetailTableName(), nameDealer.hltTableName() ]: db.dropTable(nameDealer.idTableName(tablename)) db.dropTable(tablename) except: raise
def recordedLumiForRun(self,runnum,lslist=[-1]): ''' lslist=[-1] means to take all in the db output: ['runnumber','trgtable{}','deadtable{}'] ''' dbsession = self._session c = self._c recorded=0.0 lumidata=[] #[runnumber,trgtable,deadtable] trgtable={} #{hltpath:[l1seed,hltprescale,l1prescale]} deadtable={} #{lsnum:[deadtime,instlumi,bit_0,norbits]} lumidata.append(runnum) lumidata.append(trgtable) lumidata.append(deadtable) collectedseeds=[] #[(hltpath,l1seed)] try: dbsession.transaction().start(True) schema=dbsession.nominalSchema() query=schema.newQuery() query.addToTableList(nameDealer.cmsrunsummaryTableName(),'cmsrunsummary') query.addToTableList(nameDealer.trghltMapTableName(),'trghltmap')#small table first queryCondition=coral.AttributeList() queryCondition.extend("runnumber","unsigned int") queryCondition["runnumber"].setData(int(runnum)) query.setCondition("trghltmap.HLTKEY=cmsrunsummary.HLTKEY AND cmsrunsummary.RUNNUM=:runnumber",queryCondition) query.addToOutputList("trghltmap.HLTPATHNAME","hltpathname") query.addToOutputList("trghltmap.L1SEED","l1seed") result=coral.AttributeList() result.extend("hltpathname","string") result.extend("l1seed","string") query.defineOutput(result) cursor=query.execute() while cursor.next(): hltpathname=cursor.currentRow()["hltpathname"].data() l1seed=cursor.currentRow()["l1seed"].data() collectedseeds.append((hltpathname,l1seed)) #print 'collectedseeds ',collectedseeds del query dbsession.transaction().commit() #loop over hltpath for (hname,sname) in collectedseeds: l1bitname=hltTrgSeedMapper.findUniqueSeed(hname,sname) #print 'found unque seed ',hname,l1bitname if l1bitname: lumidata[1][hname]=[] lumidata[1][hname].append(l1bitname.replace('\"','')) dbsession.transaction().start(True) schema=dbsession.nominalSchema() hltprescQuery=schema.tableHandle(nameDealer.hltTableName()).newQuery() hltprescQuery.addToOutputList("PATHNAME","hltpath") hltprescQuery.addToOutputList("PRESCALE","hltprescale") hltprescCondition=coral.AttributeList() hltprescCondition.extend('runnumber','unsigned int') hltprescCondition.extend('cmslsnum','unsigned int') hltprescCondition.extend('inf','unsigned int') hltprescResult=coral.AttributeList() hltprescResult.extend('hltpath','string') hltprescResult.extend('hltprescale','unsigned int') hltprescQuery.defineOutput(hltprescResult) hltprescCondition['runnumber'].setData(int(runnum)) hltprescCondition['cmslsnum'].setData(1) hltprescCondition['inf'].setData(0) hltprescQuery.setCondition("RUNNUM =:runnumber and CMSLSNUM =:cmslsnum and PRESCALE !=:inf",hltprescCondition) cursor=hltprescQuery.execute() while cursor.next(): hltpath=cursor.currentRow()['hltpath'].data() hltprescale=cursor.currentRow()['hltprescale'].data() if lumidata[1].has_key(hltpath): lumidata[1][hltpath].append(hltprescale) cursor.close() del hltprescQuery dbsession.transaction().commit() dbsession.transaction().start(True) schema=dbsession.nominalSchema() query=schema.newQuery() query.addToTableList(nameDealer.trgTableName(),'trg') query.addToTableList(nameDealer.lumisummaryTableName(),'lumisummary')#small table first--right-most queryCondition=coral.AttributeList() queryCondition.extend("runnumber","unsigned int") queryCondition.extend("lumiversion","string") #queryCondition.extend("alive","bool") queryCondition["runnumber"].setData(int(runnum)) queryCondition["lumiversion"].setData(c.LUMIVERSION) #queryCondition["alive"].setData(True) query.setCondition("lumisummary.RUNNUM=:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.CMSLSNUM=trg.CMSLSNUM and lumisummary.RUNNUM=trg.RUNNUM",queryCondition) #query.setCondition("trg.RUNNUM =:runnumber AND lumisummary.RUNNUM=:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.CMSLSNUM=trg.CMSLSNUM AND lumisummary.cmsalive=:alive AND trg.BITNUM=:bitnum",queryCondition) #query.addToOutputList("sum(lumisummary.INSTLUMI*(1-trg.DEADTIME/(lumisummary.numorbit*3564)))","recorded") query.addToOutputList("lumisummary.CMSLSNUM","cmsls") query.addToOutputList("lumisummary.INSTLUMI","instlumi") query.addToOutputList("lumisummary.NUMORBIT","norbits") query.addToOutputList("trg.TRGCOUNT","trgcount") query.addToOutputList("trg.BITNAME","bitname") query.addToOutputList("trg.DEADTIME","trgdeadtime") query.addToOutputList("trg.PRESCALE","trgprescale") query.addToOutputList("trg.BITNUM","trgbitnum") #query.addToOrderList("trg.BITNAME") #query.addToOrderList("trg.CMSLSNUM") result=coral.AttributeList() result.extend("cmsls","unsigned int") result.extend("instlumi","float") result.extend("norbits","unsigned int") result.extend("trgcount","unsigned int") result.extend("bitname","string") result.extend("trgdeadtime","unsigned long long") result.extend("trgprescale","unsigned int") result.extend("trgbitnum","unsigned int") trgprescalemap={} query.defineOutput(result) cursor=query.execute() while cursor.next(): cmsls=cursor.currentRow()["cmsls"].data() instlumi=cursor.currentRow()["instlumi"].data()*c.NORM norbits=cursor.currentRow()["norbits"].data() trgcount=cursor.currentRow()["trgcount"].data() trgbitname=cursor.currentRow()["bitname"].data() trgdeadtime=cursor.currentRow()["trgdeadtime"].data() trgprescale=cursor.currentRow()["trgprescale"].data() trgbitnum=cursor.currentRow()["trgbitnum"].data() if cmsls==1: if not trgprescalemap.has_key(trgbitname): trgprescalemap[trgbitname]=trgprescale if trgbitnum==0: if not deadtable.has_key(cmsls): deadtable[cmsls]=[] deadtable[cmsls].append(trgdeadtime) deadtable[cmsls].append(instlumi) deadtable[cmsls].append(trgcount) deadtable[cmsls].append(norbits) cursor.close() del query dbsession.transaction().commit() # #consolidate results # #trgtable #print 'trgprescalemap',trgprescalemap #print lumidata[1] for hpath,trgdataseq in lumidata[1].items(): bitn=trgdataseq[0] if trgprescalemap.has_key(bitn) and len(trgdataseq)==2: lumidata[1][hpath].append(trgprescalemap[bitn]) #filter selected cmsls lumidata[2]=self.filterDeadtable(deadtable,lslist) if not c.NOWARNING: for l,deaddata in lumidata[2].items(): if deaddata[1]==0.0: print '[Warning] : run:ls has 0 instlumi ',str(runnum)+':'+str(l) if deaddata[2]==0 or deaddata[0]==0: print '[Warning] : run:ls has 0 deadtime and/or 0 zerobias bit counts ',str(runnum)+':'+str(l) #print 'lumidata[2] ',lumidata[2] except Exception,e: print str(e) dbsession.transaction().rollback() del dbsession
def createLumi(dbsession): print('creating lumi db schema...') dbsession.transaction().start(False) schema = dbsession.nominalSchema() db = dbUtil.dbUtil(schema) #cms run summary table cmsrunsummary = coral.TableDescription() cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName()) cmsrunsummary.insertColumn('RUNNUM', 'unsigned int') cmsrunsummary.insertColumn('HLTKEY', 'string') cmsrunsummary.insertColumn('FILLNUM', 'unsigned int') cmsrunsummary.insertColumn('SEQUENCE', 'string') cmsrunsummary.insertColumn('STARTTIME', 'time stamp', 6) cmsrunsummary.insertColumn('STOPTIME', 'time stamp', 6) cmsrunsummary.setPrimaryKey('RUNNUM') cmsrunsummary.setNotNullConstraint('HLTKEY', True) cmsrunsummary.setNotNullConstraint('FILLNUM', True) cmsrunsummary.setNotNullConstraint('SEQUENCE', True) cmsrunsummary.createIndex('cmsrunsummary_fillnum', ('FILLNUM')) cmsrunsummary.createIndex('cmsrunsummary_startime', ('STARTTIME')) db.createTable(cmsrunsummary, False) #lumi summary table summary = coral.TableDescription() summary.setName(nameDealer.lumisummaryTableName()) summary.insertColumn('LUMISUMMARY_ID', 'unsigned long long') summary.insertColumn('RUNNUM', 'unsigned int') summary.insertColumn('CMSLSNUM', 'unsigned int') summary.insertColumn('LUMILSNUM', 'unsigned int') summary.insertColumn('LUMIVERSION', 'string') summary.insertColumn('DTNORM', 'float') summary.insertColumn('LHCNORM', 'float') summary.insertColumn('INSTLUMI', 'float') summary.insertColumn('INSTLUMIERROR', 'float') summary.insertColumn('INSTLUMIQUALITY', 'short') summary.insertColumn('CMSALIVE', 'short') summary.insertColumn('STARTORBIT', 'unsigned int') summary.insertColumn('NUMORBIT', 'unsigned int') summary.insertColumn('LUMISECTIONQUALITY', 'short') summary.insertColumn('BEAMENERGY', 'float') summary.insertColumn('BEAMSTATUS', 'string') summary.insertColumn('CMSBXINDEXBLOB', 'blob') summary.insertColumn('BEAMINTENSITYBLOB_1', 'blob') summary.insertColumn('BEAMINTENSITYBLOB_2', 'blob') summary.setPrimaryKey('LUMISUMMARY_ID') summary.setNotNullConstraint('RUNNUM', True) summary.setNotNullConstraint('CMSLSNUM', True) summary.setNotNullConstraint('LUMILSNUM', True) summary.setNotNullConstraint('LUMIVERSION', True) summary.setNotNullConstraint('DTNORM', True) summary.setNotNullConstraint('LHCNORM', True) summary.setNotNullConstraint('INSTLUMI', True) summary.setNotNullConstraint('INSTLUMIERROR', True) summary.setNotNullConstraint('INSTLUMIQUALITY', True) summary.setNotNullConstraint('CMSALIVE', True) summary.setNotNullConstraint('STARTORBIT', True) summary.setNotNullConstraint('NUMORBIT', True) summary.setNotNullConstraint('LUMISECTIONQUALITY', True) summary.setNotNullConstraint('BEAMENERGY', True) summary.setNotNullConstraint('BEAMSTATUS', True) summary.setUniqueConstraint(('RUNNUM', 'LUMIVERSION', 'LUMILSNUM')) summary.createIndex('lumisummary_runnum', ('RUNNUM')) db.createTable(summary, True) #lumi detail table detail = coral.TableDescription() detail.setName(nameDealer.lumidetailTableName()) detail.insertColumn('LUMIDETAIL_ID', 'unsigned long long') detail.insertColumn('LUMISUMMARY_ID', 'unsigned long long') detail.insertColumn('BXLUMIVALUE', 'blob') detail.insertColumn('BXLUMIERROR', 'blob') detail.insertColumn('BXLUMIQUALITY', 'blob') detail.insertColumn('ALGONAME', 'string') detail.setPrimaryKey('LUMIDETAIL_ID') detail.createForeignKey('DETAILSOURCE', 'LUMISUMMARY_ID', nameDealer.lumisummaryTableName(), 'LUMISUMMARY_ID') detail.setNotNullConstraint('BXLUMIVALUE', True) detail.setNotNullConstraint('BXLUMIERROR', True) detail.setNotNullConstraint('BXLUMIQUALITY', True) detail.setNotNullConstraint('ALGONAME', True) detail.setUniqueConstraint(('LUMISUMMARY_ID', 'ALGONAME')) db.createTable(detail, True) #trg table trg = coral.TableDescription() trg.setName(nameDealer.trgTableName()) trg.insertColumn('TRG_ID', 'unsigned long long') trg.insertColumn('RUNNUM', 'unsigned int') trg.insertColumn('CMSLSNUM', 'unsigned int') trg.insertColumn('BITNUM', 'unsigned int') trg.insertColumn('BITNAME', 'string') trg.insertColumn('TRGCOUNT', 'unsigned int') trg.insertColumn('DEADTIME', 'unsigned long long') trg.insertColumn('PRESCALE', 'unsigned int') trg.setNotNullConstraint('RUNNUM', True) trg.setNotNullConstraint('CMSLSNUM', True) trg.setNotNullConstraint('BITNUM', True) trg.setNotNullConstraint('BITNAME', True) trg.setNotNullConstraint('TRGCOUNT', True) trg.setNotNullConstraint('DEADTIME', True) trg.setNotNullConstraint('PRESCALE', True) trg.setPrimaryKey('TRG_ID') trg.createIndex('trg_runnum', ('RUNNUM')) db.createTable(trg, True) #hlt table hlt = coral.TableDescription() hlt.setName(nameDealer.hltTableName()) hlt.insertColumn('HLT_ID', 'unsigned long long') hlt.insertColumn('RUNNUM', 'unsigned int') hlt.insertColumn('CMSLSNUM', 'unsigned int') hlt.insertColumn('PATHNAME', 'string') hlt.insertColumn('INPUTCOUNT', 'unsigned int') hlt.insertColumn('ACCEPTCOUNT', 'unsigned int') hlt.insertColumn('PRESCALE', 'unsigned int') hlt.setPrimaryKey('HLT_ID') hlt.setNotNullConstraint('RUNNUM', True) hlt.setNotNullConstraint('CMSLSNUM', True) hlt.setNotNullConstraint('PATHNAME', True) hlt.setNotNullConstraint('INPUTCOUNT', True) hlt.setNotNullConstraint('ACCEPTCOUNT', True) hlt.setNotNullConstraint('PRESCALE', True) hlt.createIndex('hlt_runnum', ('RUNNUM')) db.createTable(hlt, True) #trghlt map table trghlt = coral.TableDescription() trghlt.setName(nameDealer.trghltMapTableName()) #trghlt.insertColumn( 'RUNNUM','unsigned int' ) trghlt.insertColumn('HLTKEY', 'string') trghlt.insertColumn('HLTPATHNAME', 'string') trghlt.insertColumn('L1SEED', 'string') trghlt.setNotNullConstraint('HLTKEY', True) trghlt.setNotNullConstraint('HLTPATHNAME', True) trghlt.setNotNullConstraint('L1SEED', True) db.createTable(trghlt, False) #lumiresult table lumiresult = coral.TableDescription() lumiresult.setName(nameDealer.lumiresultTableName()) lumiresult.insertColumn('RUNNUM', 'unsigned int') lumiresult.insertColumn('LUMIVERSION', 'string') lumiresult.insertColumn('DELIVEREDLUMI', 'float') lumiresult.insertColumn('RECORDEDLUMI', 'float') db.createTable(lumiresult, False) #lumihltresult table lumihltresult = coral.TableDescription() lumihltresult.setName(nameDealer.lumihltresultTableName()) lumihltresult.insertColumn('RUNNUM', 'unsigned int') lumihltresult.insertColumn('LUMIVERSION', 'string') lumihltresult.insertColumn('HLTPATH', 'float') lumihltresult.insertColumn('RECORDEDLUMI', 'float') db.createTable(lumihltresult, False) #lumivalidation table lumivalidation = coral.TableDescription() lumivalidation.setName(nameDealer.lumivalidationTableName()) lumivalidation.insertColumn('RUNNUM', 'unsigned int') lumivalidation.insertColumn('CMSLSNUM', 'unsigned int') lumivalidation.insertColumn('FLAG', 'string') lumivalidation.insertColumn('COMMENT', 'string') lumivalidation.setPrimaryKey(('RUNNUM', 'CMSLSNUM')) lumivalidation.setNotNullConstraint('FLAG', True) db.createTable(lumivalidation, False) dbsession.transaction().commit()
def createOldSchema(schema): ''' create tables of lumidb1 if not exist ''' try: created=[] db=dbUtil.dbUtil(schema) if not schema.existsTable(nameDealer.lumivalidationTableName()): lumivalidation=coral.TableDescription() lumivalidation.setName( nameDealer.lumivalidationTableName() ) lumivalidation.insertColumn( 'RUNNUM','unsigned int' ) lumivalidation.insertColumn( 'CMSLSNUM','unsigned int' ) lumivalidation.insertColumn( 'FLAG','string',28,False ) lumivalidation.insertColumn( 'COMMENT','string',1024,False ) lumivalidation.setPrimaryKey(('RUNNUM','CMSLSNUM')) lumivalidation.setNotNullConstraint('FLAG',True) db.createTable(lumivalidation,withIdTable=False) created.append(nameDealer.lumivalidationTableName()) if not schema.existsTable(nameDealer.cmsrunsummaryTableName()): cmsrunsummary=coral.TableDescription() cmsrunsummary.setName( nameDealer.cmsrunsummaryTableName() ) cmsrunsummary.insertColumn('RUNNUM','unsigned int') cmsrunsummary.insertColumn('HLTKEY','string',128,False) cmsrunsummary.insertColumn('FILLNUM','unsigned int') cmsrunsummary.insertColumn('SEQUENCE','string',56,False) cmsrunsummary.insertColumn('STARTTIME','time stamp',6) cmsrunsummary.insertColumn('STOPTIME','time stamp',6) cmsrunsummary.setPrimaryKey('RUNNUM') cmsrunsummary.setNotNullConstraint('HLTKEY',True) cmsrunsummary.setNotNullConstraint('FILLNUM',True) cmsrunsummary.setNotNullConstraint('SEQUENCE',True) cmsrunsummary.createIndex('cmsrunsummary_fillnum',('FILLNUM')) cmsrunsummary.createIndex('cmsrunsummary_startime',('STARTTIME')) db.createTable(cmsrunsummary,withIdTable=False) created.append(nameDealer.cmsrunsummaryTableName()) if not schema.existsTable(nameDealer.lumisummaryTableName()): summary=coral.TableDescription() summary.setName( nameDealer.lumisummaryTableName() ) summary.insertColumn('LUMISUMMARY_ID','unsigned long long') summary.insertColumn('RUNNUM','unsigned int') summary.insertColumn('CMSLSNUM','unsigned int') summary.insertColumn('LUMILSNUM','unsigned int') summary.insertColumn('LUMIVERSION','string',28,False) summary.insertColumn('DTNORM','float') summary.insertColumn('LHCNORM','float') summary.insertColumn('CMSALIVE','short') summary.insertColumn('INSTLUMI','float') summary.insertColumn('INSTLUMIERROR','float') summary.insertColumn('INSTLUMIQUALITY','short') summary.insertColumn('BEAMSTATUS','string',28,False) summary.insertColumn('BEAMENERGY','float') summary.insertColumn('NUMORBIT','unsigned int') summary.insertColumn('STARTORBIT','unsigned int') summary.insertColumn('LUMISECTIONQUALITY','short') summary.insertColumn('CMSBXINDEXBLOB','blob') summary.insertColumn('BEAMINTENSITYBLOB_1','blob') summary.insertColumn('BEAMINTENSITYBLOB_2','blob') summary.setPrimaryKey('LUMISUMMARY_ID') summary.setNotNullConstraint('RUNNUM',True) summary.setNotNullConstraint('CMSLSNUM',True) summary.setNotNullConstraint('LUMILSNUM',True) summary.setNotNullConstraint('CMSALIVE',True) summary.setNotNullConstraint('LUMIVERSION',True) summary.setNotNullConstraint('DTNORM',True) summary.setNotNullConstraint('LHCNORM',True) summary.setNotNullConstraint('INSTLUMI',True) summary.setNotNullConstraint('INSTLUMIERROR',True) summary.setNotNullConstraint('INSTLUMIQUALITY',True) summary.setNotNullConstraint('STARTORBIT',True) summary.setNotNullConstraint('NUMORBIT',True) summary.setNotNullConstraint('LUMISECTIONQUALITY',True) summary.setNotNullConstraint('BEAMENERGY',True) summary.setNotNullConstraint('BEAMSTATUS',True) summary.setUniqueConstraint(('RUNNUM','LUMIVERSION','LUMILSNUM')) summary.createIndex('lumisummary_runnum',('RUNNUM')) db.createTable(summary,withIdTable=True) created.append(nameDealer.lumisummaryTableName()) if not schema.existsTable(nameDealer.lumidetailTableName()): detail=coral.TableDescription() detail.setName( nameDealer.lumidetailTableName() ) detail.insertColumn('LUMIDETAIL_ID','unsigned long long') detail.insertColumn('LUMISUMMARY_ID','unsigned long long') detail.insertColumn('BXLUMIVALUE','blob') detail.insertColumn('BXLUMIERROR','blob') detail.insertColumn('BXLUMIQUALITY','blob') detail.insertColumn('ALGONAME','string',28,False) detail.setPrimaryKey('LUMIDETAIL_ID') detail.createForeignKey('DETAILSOURCE','LUMISUMMARY_ID',nameDealer.lumisummaryTableName(),'LUMISUMMARY_ID') detail.setNotNullConstraint('BXLUMIVALUE',True) detail.setNotNullConstraint('BXLUMIERROR',True) detail.setNotNullConstraint('BXLUMIQUALITY',True) detail.setNotNullConstraint('ALGONAME',True) detail.setUniqueConstraint(('LUMISUMMARY_ID','ALGONAME')) db.createTable(detail,withIdTable=True) created.append(nameDealer.lumidetailTableName()) if not schema.existsTable(nameDealer.trgTableName()): trg=coral.TableDescription() trg.setName( nameDealer.trgTableName() ) trg.insertColumn('TRG_ID','unsigned long long') trg.insertColumn('RUNNUM','unsigned int') trg.insertColumn('CMSLSNUM','unsigned int') trg.insertColumn('BITNUM','unsigned int') trg.insertColumn('BITNAME','string',56,False) trg.insertColumn('TRGCOUNT','unsigned int') trg.insertColumn('DEADTIME','unsigned long long') trg.insertColumn('PRESCALE','unsigned int') trg.setNotNullConstraint('RUNNUM',True) trg.setNotNullConstraint('CMSLSNUM',True) trg.setNotNullConstraint('BITNUM',True) trg.setNotNullConstraint('BITNAME',True) trg.setNotNullConstraint('TRGCOUNT',True) trg.setNotNullConstraint('DEADTIME',True) trg.setNotNullConstraint('PRESCALE',True) trg.setPrimaryKey('TRG_ID') trg.createIndex('trg_runnum',('RUNNUM')) db.createTable(trg,withIdTable=True) created.append( nameDealer.trgTableName() ) if not schema.existsTable( nameDealer.hltTableName() ): hlt=coral.TableDescription() hlt.setName( nameDealer.hltTableName() ) hlt.insertColumn( 'HLT_ID','unsigned long long') hlt.insertColumn( 'RUNNUM','unsigned int') hlt.insertColumn( 'CMSLSNUM','unsigned int') hlt.insertColumn( 'PATHNAME','string',256,False) hlt.insertColumn( 'INPUTCOUNT','unsigned int') hlt.insertColumn( 'ACCEPTCOUNT','unsigned int') hlt.insertColumn( 'PRESCALE','unsigned int') hlt.setPrimaryKey( 'HLT_ID' ) hlt.setNotNullConstraint('RUNNUM',True) hlt.setNotNullConstraint('CMSLSNUM',True) hlt.setNotNullConstraint('PATHNAME',True) hlt.setNotNullConstraint('INPUTCOUNT',True) hlt.setNotNullConstraint('ACCEPTCOUNT',True) hlt.setNotNullConstraint('PRESCALE',True) hlt.createIndex('hlt_runnum',('RUNNUM')) db.createTable(hlt,withIdTable=True) created.append( nameDealer.hltTableName() ) if not schema.existsTable( nameDealer.trghltMapTableName() ): trghlt=coral.TableDescription() trghlt.setName( nameDealer.trghltMapTableName() ) trghlt.insertColumn( 'HLTKEY','string',128,False ) trghlt.insertColumn( 'HLTPATHNAME','string',256,False ) trghlt.insertColumn( 'L1SEED','string',1024,false ) trghlt.setNotNullConstraint('HLTKEY',True) trghlt.setNotNullConstraint('HLTPATHNAME',True) trghlt.setNotNullConstraint('L1SEED',True) db.createTable(trghlt,withIdTable=False) created.append( nameDealer.trghltMapTableName() ) return created except: raise
def createOldSchema(schema): ''' create tables of lumidb1 if not exist ''' try: created = [] db = dbUtil.dbUtil(schema) if not schema.existsTable(nameDealer.lumivalidationTableName()): lumivalidation = coral.TableDescription() lumivalidation.setName(nameDealer.lumivalidationTableName()) lumivalidation.insertColumn('RUNNUM', 'unsigned int') lumivalidation.insertColumn('CMSLSNUM', 'unsigned int') lumivalidation.insertColumn('FLAG', 'string', 28, False) lumivalidation.insertColumn('COMMENT', 'string', 1024, False) lumivalidation.setPrimaryKey(('RUNNUM', 'CMSLSNUM')) lumivalidation.setNotNullConstraint('FLAG', True) db.createTable(lumivalidation, withIdTable=False) created.append(nameDealer.lumivalidationTableName()) if not schema.existsTable(nameDealer.cmsrunsummaryTableName()): cmsrunsummary = coral.TableDescription() cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName()) cmsrunsummary.insertColumn('RUNNUM', 'unsigned int') cmsrunsummary.insertColumn('HLTKEY', 'string', 128, False) cmsrunsummary.insertColumn('FILLNUM', 'unsigned int') cmsrunsummary.insertColumn('SEQUENCE', 'string', 56, False) cmsrunsummary.insertColumn('STARTTIME', 'time stamp', 6) cmsrunsummary.insertColumn('STOPTIME', 'time stamp', 6) cmsrunsummary.setPrimaryKey('RUNNUM') cmsrunsummary.setNotNullConstraint('HLTKEY', True) cmsrunsummary.setNotNullConstraint('FILLNUM', True) cmsrunsummary.setNotNullConstraint('SEQUENCE', True) cmsrunsummary.createIndex('cmsrunsummary_fillnum', ('FILLNUM')) cmsrunsummary.createIndex('cmsrunsummary_startime', ('STARTTIME')) db.createTable(cmsrunsummary, withIdTable=False) created.append(nameDealer.cmsrunsummaryTableName()) if not schema.existsTable(nameDealer.lumisummaryTableName()): summary = coral.TableDescription() summary.setName(nameDealer.lumisummaryTableName()) summary.insertColumn('LUMISUMMARY_ID', 'unsigned long long') summary.insertColumn('RUNNUM', 'unsigned int') summary.insertColumn('CMSLSNUM', 'unsigned int') summary.insertColumn('LUMILSNUM', 'unsigned int') summary.insertColumn('LUMIVERSION', 'string', 28, False) summary.insertColumn('DTNORM', 'float') summary.insertColumn('LHCNORM', 'float') summary.insertColumn('CMSALIVE', 'short') summary.insertColumn('INSTLUMI', 'float') summary.insertColumn('INSTLUMIERROR', 'float') summary.insertColumn('INSTLUMIQUALITY', 'short') summary.insertColumn('BEAMSTATUS', 'string', 28, False) summary.insertColumn('BEAMENERGY', 'float') summary.insertColumn('NUMORBIT', 'unsigned int') summary.insertColumn('STARTORBIT', 'unsigned int') summary.insertColumn('LUMISECTIONQUALITY', 'short') summary.insertColumn('CMSBXINDEXBLOB', 'blob') summary.insertColumn('BEAMINTENSITYBLOB_1', 'blob') summary.insertColumn('BEAMINTENSITYBLOB_2', 'blob') summary.setPrimaryKey('LUMISUMMARY_ID') summary.setNotNullConstraint('RUNNUM', True) summary.setNotNullConstraint('CMSLSNUM', True) summary.setNotNullConstraint('LUMILSNUM', True) summary.setNotNullConstraint('CMSALIVE', True) summary.setNotNullConstraint('LUMIVERSION', True) summary.setNotNullConstraint('DTNORM', True) summary.setNotNullConstraint('LHCNORM', True) summary.setNotNullConstraint('INSTLUMI', True) summary.setNotNullConstraint('INSTLUMIERROR', True) summary.setNotNullConstraint('INSTLUMIQUALITY', True) summary.setNotNullConstraint('STARTORBIT', True) summary.setNotNullConstraint('NUMORBIT', True) summary.setNotNullConstraint('LUMISECTIONQUALITY', True) summary.setNotNullConstraint('BEAMENERGY', True) summary.setNotNullConstraint('BEAMSTATUS', True) summary.setUniqueConstraint(('RUNNUM', 'LUMIVERSION', 'LUMILSNUM')) summary.createIndex('lumisummary_runnum', ('RUNNUM')) db.createTable(summary, withIdTable=True) created.append(nameDealer.lumisummaryTableName()) if not schema.existsTable(nameDealer.lumidetailTableName()): detail = coral.TableDescription() detail.setName(nameDealer.lumidetailTableName()) detail.insertColumn('LUMIDETAIL_ID', 'unsigned long long') detail.insertColumn('LUMISUMMARY_ID', 'unsigned long long') detail.insertColumn('BXLUMIVALUE', 'blob') detail.insertColumn('BXLUMIERROR', 'blob') detail.insertColumn('BXLUMIQUALITY', 'blob') detail.insertColumn('ALGONAME', 'string', 28, False) detail.setPrimaryKey('LUMIDETAIL_ID') detail.createForeignKey('DETAILSOURCE', 'LUMISUMMARY_ID', nameDealer.lumisummaryTableName(), 'LUMISUMMARY_ID') detail.setNotNullConstraint('BXLUMIVALUE', True) detail.setNotNullConstraint('BXLUMIERROR', True) detail.setNotNullConstraint('BXLUMIQUALITY', True) detail.setNotNullConstraint('ALGONAME', True) detail.setUniqueConstraint(('LUMISUMMARY_ID', 'ALGONAME')) db.createTable(detail, withIdTable=True) created.append(nameDealer.lumidetailTableName()) if not schema.existsTable(nameDealer.trgTableName()): trg = coral.TableDescription() trg.setName(nameDealer.trgTableName()) trg.insertColumn('TRG_ID', 'unsigned long long') trg.insertColumn('RUNNUM', 'unsigned int') trg.insertColumn('CMSLSNUM', 'unsigned int') trg.insertColumn('BITNUM', 'unsigned int') trg.insertColumn('BITNAME', 'string', 56, False) trg.insertColumn('TRGCOUNT', 'unsigned int') trg.insertColumn('DEADTIME', 'unsigned long long') trg.insertColumn('PRESCALE', 'unsigned int') trg.setNotNullConstraint('RUNNUM', True) trg.setNotNullConstraint('CMSLSNUM', True) trg.setNotNullConstraint('BITNUM', True) trg.setNotNullConstraint('BITNAME', True) trg.setNotNullConstraint('TRGCOUNT', True) trg.setNotNullConstraint('DEADTIME', True) trg.setNotNullConstraint('PRESCALE', True) trg.setPrimaryKey('TRG_ID') trg.createIndex('trg_runnum', ('RUNNUM')) db.createTable(trg, withIdTable=True) created.append(nameDealer.trgTableName()) if not schema.existsTable(nameDealer.hltTableName()): hlt = coral.TableDescription() hlt.setName(nameDealer.hltTableName()) hlt.insertColumn('HLT_ID', 'unsigned long long') hlt.insertColumn('RUNNUM', 'unsigned int') hlt.insertColumn('CMSLSNUM', 'unsigned int') hlt.insertColumn('PATHNAME', 'string', 256, False) hlt.insertColumn('INPUTCOUNT', 'unsigned int') hlt.insertColumn('ACCEPTCOUNT', 'unsigned int') hlt.insertColumn('PRESCALE', 'unsigned int') hlt.setPrimaryKey('HLT_ID') hlt.setNotNullConstraint('RUNNUM', True) hlt.setNotNullConstraint('CMSLSNUM', True) hlt.setNotNullConstraint('PATHNAME', True) hlt.setNotNullConstraint('INPUTCOUNT', True) hlt.setNotNullConstraint('ACCEPTCOUNT', True) hlt.setNotNullConstraint('PRESCALE', True) hlt.createIndex('hlt_runnum', ('RUNNUM')) db.createTable(hlt, withIdTable=True) created.append(nameDealer.hltTableName()) if not schema.existsTable(nameDealer.trghltMapTableName()): trghlt = coral.TableDescription() trghlt.setName(nameDealer.trghltMapTableName()) trghlt.insertColumn('HLTKEY', 'string', 128, False) trghlt.insertColumn('HLTPATHNAME', 'string', 256, False) trghlt.insertColumn('L1SEED', 'string', 1024, false) trghlt.setNotNullConstraint('HLTKEY', True) trghlt.setNotNullConstraint('HLTPATHNAME', True) trghlt.setNotNullConstraint('L1SEED', True) db.createTable(trghlt, withIdTable=False) created.append(nameDealer.trghltMapTableName()) return created except: raise
def createLumi(dbsession): print('creating lumi db schema...') dbsession.transaction().start(False) schema=dbsession.nominalSchema() db=dbUtil.dbUtil(schema) #cms run summary table cmsrunsummary=coral.TableDescription() cmsrunsummary.setName( nameDealer.cmsrunsummaryTableName() ) cmsrunsummary.insertColumn('RUNNUM','unsigned int') cmsrunsummary.insertColumn('HLTKEY','string') cmsrunsummary.insertColumn('FILLNUM','unsigned int') cmsrunsummary.insertColumn('SEQUENCE','string') cmsrunsummary.insertColumn('STARTTIME','time stamp',6) cmsrunsummary.insertColumn('STOPTIME','time stamp',6) cmsrunsummary.setPrimaryKey('RUNNUM') cmsrunsummary.setNotNullConstraint('HLTKEY',True) cmsrunsummary.setNotNullConstraint('FILLNUM',True) cmsrunsummary.setNotNullConstraint('SEQUENCE',True) cmsrunsummary.createIndex('cmsrunsummary_fillnum',('FILLNUM')) cmsrunsummary.createIndex('cmsrunsummary_startime',('STARTTIME')) db.createTable(cmsrunsummary,False) #lumi summary table summary=coral.TableDescription() summary.setName( nameDealer.lumisummaryTableName() ) summary.insertColumn('LUMISUMMARY_ID','unsigned long long') summary.insertColumn('RUNNUM','unsigned int') summary.insertColumn('CMSLSNUM','unsigned int') summary.insertColumn('LUMILSNUM','unsigned int') summary.insertColumn('LUMIVERSION','string') summary.insertColumn('DTNORM','float') summary.insertColumn('LHCNORM','float') summary.insertColumn('INSTLUMI','float') summary.insertColumn('INSTLUMIERROR','float') summary.insertColumn('INSTLUMIQUALITY','short') summary.insertColumn('CMSALIVE','short') summary.insertColumn('STARTORBIT','unsigned int') summary.insertColumn('NUMORBIT','unsigned int') summary.insertColumn('LUMISECTIONQUALITY','short') summary.insertColumn('BEAMENERGY','float') summary.insertColumn('BEAMSTATUS','string') summary.insertColumn('CMSBXINDEXBLOB','blob') summary.insertColumn('BEAMINTENSITYBLOB_1','blob') summary.insertColumn('BEAMINTENSITYBLOB_2','blob') summary.setPrimaryKey('LUMISUMMARY_ID') summary.setNotNullConstraint('RUNNUM',True) summary.setNotNullConstraint('CMSLSNUM',True) summary.setNotNullConstraint('LUMILSNUM',True) summary.setNotNullConstraint('LUMIVERSION',True) summary.setNotNullConstraint('DTNORM',True) summary.setNotNullConstraint('LHCNORM',True) summary.setNotNullConstraint('INSTLUMI',True) summary.setNotNullConstraint('INSTLUMIERROR',True) summary.setNotNullConstraint('INSTLUMIQUALITY',True) summary.setNotNullConstraint('CMSALIVE',True) summary.setNotNullConstraint('STARTORBIT',True) summary.setNotNullConstraint('NUMORBIT',True) summary.setNotNullConstraint('LUMISECTIONQUALITY',True) summary.setNotNullConstraint('BEAMENERGY',True) summary.setNotNullConstraint('BEAMSTATUS',True) summary.setUniqueConstraint(('RUNNUM','LUMIVERSION','LUMILSNUM')) summary.createIndex('lumisummary_runnum',('RUNNUM')) db.createTable(summary,True) #lumi detail table detail=coral.TableDescription() detail.setName( nameDealer.lumidetailTableName() ) detail.insertColumn('LUMIDETAIL_ID','unsigned long long') detail.insertColumn('LUMISUMMARY_ID','unsigned long long') detail.insertColumn('BXLUMIVALUE','blob') detail.insertColumn('BXLUMIERROR','blob') detail.insertColumn('BXLUMIQUALITY','blob') detail.insertColumn('ALGONAME','string') detail.setPrimaryKey('LUMIDETAIL_ID') detail.createForeignKey('DETAILSOURCE','LUMISUMMARY_ID',nameDealer.lumisummaryTableName(),'LUMISUMMARY_ID') detail.setNotNullConstraint('BXLUMIVALUE',True) detail.setNotNullConstraint('BXLUMIERROR',True) detail.setNotNullConstraint('BXLUMIQUALITY',True) detail.setNotNullConstraint('ALGONAME',True) detail.setUniqueConstraint(('LUMISUMMARY_ID','ALGONAME')) db.createTable(detail,True) #trg table trg=coral.TableDescription() trg.setName( nameDealer.trgTableName() ) trg.insertColumn('TRG_ID','unsigned long long') trg.insertColumn('RUNNUM','unsigned int') trg.insertColumn('CMSLSNUM','unsigned int') trg.insertColumn('BITNUM','unsigned int') trg.insertColumn('BITNAME','string') trg.insertColumn('TRGCOUNT','unsigned int') trg.insertColumn('DEADTIME','unsigned long long') trg.insertColumn('PRESCALE','unsigned int') trg.setNotNullConstraint('RUNNUM',True) trg.setNotNullConstraint('CMSLSNUM',True) trg.setNotNullConstraint('BITNUM',True) trg.setNotNullConstraint('BITNAME',True) trg.setNotNullConstraint('TRGCOUNT',True) trg.setNotNullConstraint('DEADTIME',True) trg.setNotNullConstraint('PRESCALE',True) trg.setPrimaryKey('TRG_ID') trg.createIndex('trg_runnum',('RUNNUM')) db.createTable(trg,True) #hlt table hlt=coral.TableDescription() hlt.setName( nameDealer.hltTableName() ) hlt.insertColumn( 'HLT_ID','unsigned long long') hlt.insertColumn( 'RUNNUM','unsigned int') hlt.insertColumn( 'CMSLSNUM','unsigned int') hlt.insertColumn( 'PATHNAME','string') hlt.insertColumn( 'INPUTCOUNT','unsigned int') hlt.insertColumn( 'ACCEPTCOUNT','unsigned int') hlt.insertColumn( 'PRESCALE','unsigned int') hlt.setPrimaryKey( 'HLT_ID' ) hlt.setNotNullConstraint('RUNNUM',True) hlt.setNotNullConstraint('CMSLSNUM',True) hlt.setNotNullConstraint('PATHNAME',True) hlt.setNotNullConstraint('INPUTCOUNT',True) hlt.setNotNullConstraint('ACCEPTCOUNT',True) hlt.setNotNullConstraint('PRESCALE',True) hlt.createIndex('hlt_runnum',('RUNNUM')) db.createTable(hlt,True) #trghlt map table trghlt=coral.TableDescription() trghlt.setName( nameDealer.trghltMapTableName() ) #trghlt.insertColumn( 'RUNNUM','unsigned int' ) trghlt.insertColumn( 'HLTKEY','string' ) trghlt.insertColumn( 'HLTPATHNAME','string' ) trghlt.insertColumn( 'L1SEED','string' ) trghlt.setNotNullConstraint('HLTKEY',True) trghlt.setNotNullConstraint('HLTPATHNAME',True) trghlt.setNotNullConstraint('L1SEED',True) db.createTable(trghlt,False) #lumiresult table lumiresult=coral.TableDescription() lumiresult.setName( nameDealer.lumiresultTableName() ) lumiresult.insertColumn( 'RUNNUM','unsigned int' ) lumiresult.insertColumn( 'LUMIVERSION','string' ) lumiresult.insertColumn( 'DELIVEREDLUMI','float' ) lumiresult.insertColumn( 'RECORDEDLUMI','float' ) db.createTable(lumiresult,False) #lumihltresult table lumihltresult=coral.TableDescription() lumihltresult.setName( nameDealer.lumihltresultTableName() ) lumihltresult.insertColumn( 'RUNNUM','unsigned int' ) lumihltresult.insertColumn( 'LUMIVERSION','string' ) lumihltresult.insertColumn( 'HLTPATH','float' ) lumihltresult.insertColumn( 'RECORDEDLUMI','float' ) db.createTable(lumihltresult,False) #lumivalidation table lumivalidation=coral.TableDescription() lumivalidation.setName( nameDealer.lumivalidationTableName() ) lumivalidation.insertColumn( 'RUNNUM','unsigned int' ) lumivalidation.insertColumn( 'CMSLSNUM','unsigned int' ) lumivalidation.insertColumn( 'FLAG','string' ) lumivalidation.insertColumn( 'COMMENT','string' ) lumivalidation.setPrimaryKey(('RUNNUM','CMSLSNUM')) lumivalidation.setNotNullConstraint('FLAG',True) db.createTable(lumivalidation,False) dbsession.transaction().commit()
def createLumi(dbsession): print "creating lumi db schema..." dbsession.transaction().start(False) schema = dbsession.nominalSchema() db = dbUtil.dbUtil(schema) # cms run summary table cmsrunsummary = coral.TableDescription() cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName()) cmsrunsummary.insertColumn("RUNNUM", "unsigned int") cmsrunsummary.insertColumn("HLTKEY", "string") cmsrunsummary.insertColumn("FILLNUM", "unsigned int") cmsrunsummary.insertColumn("SEQUENCE", "string") cmsrunsummary.insertColumn("STARTTIME", "time stamp", 6) cmsrunsummary.insertColumn("STOPTIME", "time stamp", 6) cmsrunsummary.setPrimaryKey("RUNNUM") cmsrunsummary.setNotNullConstraint("HLTKEY", True) cmsrunsummary.setNotNullConstraint("FILLNUM", True) cmsrunsummary.setNotNullConstraint("SEQUENCE", True) cmsrunsummary.createIndex("cmsrunsummary_fillnum", ("FILLNUM")) cmsrunsummary.createIndex("cmsrunsummary_startime", ("STARTTIME")) db.createTable(cmsrunsummary, False) # lumi summary table summary = coral.TableDescription() summary.setName(nameDealer.lumisummaryTableName()) summary.insertColumn("LUMISUMMARY_ID", "unsigned long long") summary.insertColumn("RUNNUM", "unsigned int") summary.insertColumn("CMSLSNUM", "unsigned int") summary.insertColumn("LUMILSNUM", "unsigned int") summary.insertColumn("LUMIVERSION", "string") summary.insertColumn("DTNORM", "float") summary.insertColumn("LHCNORM", "float") summary.insertColumn("INSTLUMI", "float") summary.insertColumn("INSTLUMIERROR", "float") summary.insertColumn("INSTLUMIQUALITY", "short") summary.insertColumn("CMSALIVE", "short") summary.insertColumn("STARTORBIT", "unsigned int") summary.insertColumn("NUMORBIT", "unsigned int") summary.insertColumn("LUMISECTIONQUALITY", "short") summary.insertColumn("BEAMENERGY", "float") summary.insertColumn("BEAMSTATUS", "string") summary.insertColumn("CMSBXINDEXBLOB", "blob") summary.insertColumn("BEAMINTENSITYBLOB_1", "blob") summary.insertColumn("BEAMINTENSITYBLOB_2", "blob") summary.setPrimaryKey("LUMISUMMARY_ID") summary.setNotNullConstraint("RUNNUM", True) summary.setNotNullConstraint("CMSLSNUM", True) summary.setNotNullConstraint("LUMILSNUM", True) summary.setNotNullConstraint("LUMIVERSION", True) summary.setNotNullConstraint("DTNORM", True) summary.setNotNullConstraint("LHCNORM", True) summary.setNotNullConstraint("INSTLUMI", True) summary.setNotNullConstraint("INSTLUMIERROR", True) summary.setNotNullConstraint("INSTLUMIQUALITY", True) summary.setNotNullConstraint("CMSALIVE", True) summary.setNotNullConstraint("STARTORBIT", True) summary.setNotNullConstraint("NUMORBIT", True) summary.setNotNullConstraint("LUMISECTIONQUALITY", True) summary.setNotNullConstraint("BEAMENERGY", True) summary.setNotNullConstraint("BEAMSTATUS", True) summary.setUniqueConstraint(("RUNNUM", "LUMIVERSION", "LUMILSNUM")) summary.createIndex("lumisummary_runnum", ("RUNNUM")) db.createTable(summary, True) # lumi detail table detail = coral.TableDescription() detail.setName(nameDealer.lumidetailTableName()) detail.insertColumn("LUMIDETAIL_ID", "unsigned long long") detail.insertColumn("LUMISUMMARY_ID", "unsigned long long") detail.insertColumn("BXLUMIVALUE", "blob") detail.insertColumn("BXLUMIERROR", "blob") detail.insertColumn("BXLUMIQUALITY", "blob") detail.insertColumn("ALGONAME", "string") detail.setPrimaryKey("LUMIDETAIL_ID") detail.createForeignKey("DETAILSOURCE", "LUMISUMMARY_ID", nameDealer.lumisummaryTableName(), "LUMISUMMARY_ID") detail.setNotNullConstraint("BXLUMIVALUE", True) detail.setNotNullConstraint("BXLUMIERROR", True) detail.setNotNullConstraint("BXLUMIQUALITY", True) detail.setNotNullConstraint("ALGONAME", True) detail.setUniqueConstraint(("LUMISUMMARY_ID", "ALGONAME")) db.createTable(detail, True) # trg table trg = coral.TableDescription() trg.setName(nameDealer.trgTableName()) trg.insertColumn("TRG_ID", "unsigned long long") trg.insertColumn("RUNNUM", "unsigned int") trg.insertColumn("CMSLSNUM", "unsigned int") trg.insertColumn("BITNUM", "unsigned int") trg.insertColumn("BITNAME", "string") trg.insertColumn("TRGCOUNT", "unsigned int") trg.insertColumn("DEADTIME", "unsigned long long") trg.insertColumn("PRESCALE", "unsigned int") trg.setNotNullConstraint("RUNNUM", True) trg.setNotNullConstraint("CMSLSNUM", True) trg.setNotNullConstraint("BITNUM", True) trg.setNotNullConstraint("BITNAME", True) trg.setNotNullConstraint("TRGCOUNT", True) trg.setNotNullConstraint("DEADTIME", True) trg.setNotNullConstraint("PRESCALE", True) trg.setPrimaryKey("TRG_ID") trg.createIndex("trg_runnum", ("RUNNUM")) db.createTable(trg, True) # hlt table hlt = coral.TableDescription() hlt.setName(nameDealer.hltTableName()) hlt.insertColumn("HLT_ID", "unsigned long long") hlt.insertColumn("RUNNUM", "unsigned int") hlt.insertColumn("CMSLSNUM", "unsigned int") hlt.insertColumn("PATHNAME", "string") hlt.insertColumn("INPUTCOUNT", "unsigned int") hlt.insertColumn("ACCEPTCOUNT", "unsigned int") hlt.insertColumn("PRESCALE", "unsigned int") hlt.setPrimaryKey("HLT_ID") hlt.setNotNullConstraint("RUNNUM", True) hlt.setNotNullConstraint("CMSLSNUM", True) hlt.setNotNullConstraint("PATHNAME", True) hlt.setNotNullConstraint("INPUTCOUNT", True) hlt.setNotNullConstraint("ACCEPTCOUNT", True) hlt.setNotNullConstraint("PRESCALE", True) hlt.createIndex("hlt_runnum", ("RUNNUM")) db.createTable(hlt, True) # trghlt map table trghlt = coral.TableDescription() trghlt.setName(nameDealer.trghltMapTableName()) # trghlt.insertColumn( 'RUNNUM','unsigned int' ) trghlt.insertColumn("HLTKEY", "string") trghlt.insertColumn("HLTPATHNAME", "string") trghlt.insertColumn("L1SEED", "string") trghlt.setNotNullConstraint("HLTKEY", True) trghlt.setNotNullConstraint("HLTPATHNAME", True) trghlt.setNotNullConstraint("L1SEED", True) db.createTable(trghlt, False) # lumiresult table lumiresult = coral.TableDescription() lumiresult.setName(nameDealer.lumiresultTableName()) lumiresult.insertColumn("RUNNUM", "unsigned int") lumiresult.insertColumn("LUMIVERSION", "string") lumiresult.insertColumn("DELIVEREDLUMI", "float") lumiresult.insertColumn("RECORDEDLUMI", "float") db.createTable(lumiresult, False) # lumihltresult table lumihltresult = coral.TableDescription() lumihltresult.setName(nameDealer.lumihltresultTableName()) lumihltresult.insertColumn("RUNNUM", "unsigned int") lumihltresult.insertColumn("LUMIVERSION", "string") lumihltresult.insertColumn("HLTPATH", "float") lumihltresult.insertColumn("RECORDEDLUMI", "float") db.createTable(lumihltresult, False) # lumivalidation table lumivalidation = coral.TableDescription() lumivalidation.setName(nameDealer.lumivalidationTableName()) lumivalidation.insertColumn("RUNNUM", "unsigned int") lumivalidation.insertColumn("CMSLSNUM", "unsigned int") lumivalidation.insertColumn("FLAG", "string") lumivalidation.insertColumn("COMMENT", "string") lumivalidation.setPrimaryKey(("RUNNUM", "CMSLSNUM")) lumivalidation.setNotNullConstraint("FLAG", True) db.createTable(lumivalidation, False) dbsession.transaction().commit()