def promoteNormToTypeDefault(schema,normname,lumitype): ''' set the named norm as default for a given type,reset the old default if any thisnormid=normIdByName(schema,normname) olddefaultid=normIdByType(schema,lumitype=lumitype,defaultonly=True) if thisnormid: update LUMINORMSV2 set ISTYPEDEFAULT=1 where DATA_ID=:thisnormid else: raise ValueError('normname does not exist, nothing to update') if olddefaultid and olddefaultid!=thisnormid: update LUMINORMSV2 set ISTYPEDEFAULT=0 where DATA_ID=:olddefaultid ''' try: thisnormid=normIdByName(schema,normname) olddefaultid=normIdByType(schema,lumitype=lumitype,defaultonly=True) if not thisnormid: raise ValueError(normname+' does not exist, nothing to update') setClause='ISTYPEDEFAULT=1' updateCondition='DATA_ID=:thisnormid' inputData=coral.AttributeList() inputData.extend('thisnormid','unsigned long long') inputData['thisnormid'].setData(thisnormid) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.luminormTable(),setClause,updateCondition,inputData) if olddefaultid: setClause='ISTYPEDEFAULT=0' updateCondition='DATA_ID=:olddefaultid' inputData=coral.AttributeList() inputData.extend('olddefaultid','unsigned long long') inputData['olddefaultid'].setData(olddefaultid) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.luminormTable(),setClause,updateCondition,inputData) except : raise
def promoteNormToTypeDefault(schema,normname,lumitype): ''' set the named norm as default for a given type,reset the old default if any thisnormid=normIdByName(schema,normname) olddefaultid=normIdByType(schema,lumitype=lumitype,defaultonly=True) if thisnormid: update LUMINORMSV2 set ISTYPEDEFAULT=1 where DATA_ID=:thisnormid else: raise ValueError('normname does not exist, nothing to update') if olddefaultid and olddefaultid!=thisnormid: update LUMINORMSV2 set ISTYPEDEFAULT=0 where DATA_ID=:olddefaultid ''' try: thisnormid=normIdByName(schema,normname) olddefaultid=normIdByType(schema,lumitype=lumitype,defaultonly=True) if not thisnormid: raise ValueError(normname+' does not exist, nothing to update') setClause='ISTYPEDEFAULT=1' updateCondition='DATA_ID=:thisnormid' inputData=coral.AttributeList() inputData.extend('thisnormid','unsigned long long') inputData['thisnormid'].setData(thisnormid) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.luminormTable(),setClause,updateCondition,inputData) if olddefaultid: setClause='ISTYPEDEFAULT=0' updateCondition='DATA_ID=:olddefaultid' inputData=coral.AttributeList() inputData.extend('olddefaultid','unsigned long long') inputData['olddefaultid'].setData(olddefaultid) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.luminormTable(),setClause,updateCondition,inputData) except : raise
def addindb(session, datatag, normtag, lumidata, bulksize): ''' input : [fill,run,lumils,cmsls,lstime,beamstatus,beamenergy,delivered,recorded,avgpu] ''' hfresultDefDict = [('RUNNUM', 'unsigned int'), ('LS', 'unsigned int'), ('CMSLS', 'unsigned int'), ('FILLNUM', 'unsigned int'), ('TIME', 'time stamp'), ('BEAM_STATUS', 'string'), ('ENERGY', 'unsigned int'), ('DELIVERED', 'float'), ('RECORDED', 'float'), ('AVG_PU', 'float'), ('DATA_VERSION', 'string'), ('NORM_VERSION', 'string'), ('INSERT_TIME', 'time stamp')] committedrows = 0 nrows = 0 bulkvalues = [] lute = lumiTime.lumiTime() try: for datum in lumidata: [ fillnum, runnum, lumils, cmsls, lstime_char, beamstatus, beamenergy, delivered, recorded, avgpu ] = datum inserttime = coral.TimeStamp() lstime = lute.StrToDatetime(lstime_char, customfm='%m/%d/%y %H:%M:%S') corallstime = coral.TimeStamp(lstime.year, lstime.month, lstime.day, lstime.hour, lstime.minute, lstime.second, 0) bulkvalues.append([('RUNNUM', runnum), ('LS', lumils), ('CMSLS', cmsls), ('FILLNUM', fillnum), ('TIME', corallstime), ('BEAM_STATUS', beamstatus), ('ENERGY', beamenergy), ('DELIVERED', delivered), ('RECORDED', recorded), ('AVG_PU', avgpu), ('DATA_VERSION', datatag), ('NORM_VERSION', normtag), ('INSERT_TIME', inserttime)]) nrows += 1 committedrows += 1 if nrows == bulksize: print 'committing trg in LS chunck ', nrows db = dbUtil.dbUtil(session.nominalSchema()) session.transaction().start(False) db.bulkInsert('HFLUMIRESULT', hfresultDefDict, bulkvalues) session.transaction().commit() nrows = 0 bulkvalues = [] elif committedrows == len(lumidata): print 'committing at the end ' db = dbUtil.dbUtil(session.nominalSchema()) session.transaction().start(False) db.bulkInsert('HFLUMIRESULT', hfresultDefDict, bulkvalues) session.transaction().commit() except: print 'error in addindb' raise
def createDataTag(schema,tagname,lumitype='HF'): ''' insert into tags(tagname,tagid,creationtime) values() output: tagname,tagid,creationtime ''' if lumitype not in ['HF','PIXEL']: raise ValueError('unknown lumitype '+lumitype) if lumitype=='HF': tagstablename=nameDealer.tagsTableName() else: tagstablename=nameDealer.pixeltagsTableName() try: iddealer=idDealer.idDealer(schema) tagid=iddealer.generateNextIDForTable( tagstablename ) db=dbUtil.dbUtil(schema) tabrowDefDict={} tabrowDefDict['TAGNAME']='string' tabrowDefDict['TAGID']='unsigned long long' tabrowDefDict['CREATIONTIME']='time stamp' tabrowValueDict={} tabrowValueDict['TAGNAME']=tagname tabrowValueDict['TAGID']=tagid creationtime=coral.TimeStamp() tabrowValueDict['CREATIONTIME']=creationtime db.insertOneRow(tagstablename,tabrowDefDict, tabrowValueDict ) return (tagname,tagid,creationtime) except: raise
def addRunToCurrentDataTag(schema,runnum,lumiid,trgid,hltid,lumitype='HF',comment=''): ''' select tagid from tags insert into tagruns(tagid,runnum,lumidataid,trgdataid,hltdataid,creationtime,comment) values(tagid,runnum,lumiid,trgid,hltid,creationtime,comment) ''' if lumitype not in ['HF','PIXEL']: raise ValueError('unknown lumitype '+lumitype) if lumitype=='HF': tagrunstablename=nameDealer.tagRunsTableName() else: tagrunstablename=nameDealer.pixeltagRunsTableName() currenttagid=currentDataTag(schema,lumitype=lumitype)[0] try: db=dbUtil.dbUtil(schema) tabrowDefDict={} tabrowDefDict['TAGID']='unsigned long long' tabrowDefDict['RUNNUM']='unsigned int' tabrowDefDict['LUMIDATAID']='unsigned long long' tabrowDefDict['TRGDATAID']='unsigned long long' tabrowDefDict['HLTDATAID']='unsigned long long' tabrowDefDict['CREATIONTIME']='time stamp' tabrowDefDict['COMMENT']='string' tabrowValueDict={} tabrowValueDict['TAGID']=currenttagid tabrowValueDict['RUNNUM']=runnum tabrowValueDict['LUMIDATAID']=lumiid tabrowValueDict['TRGDATAID']=trgid tabrowValueDict['HLTDATAID']=hltid tabrowValueDict['CREATIONTIME']=coral.TimeStamp() tabrowValueDict['COMMENT']=comment db.insertOneRow( tagrunstablename,tabrowDefDict, tabrowValueDict ) except: raise
def insertLumiDetaildata(dbsession,perlsrawdata,perbunchrawdata,summaryidlsmap): dataDef=[] dataDef.append(('LUMISUMMARY_ID','unsigned long long')) dataDef.append(('LUMIDETAIL_ID','unsigned long long')) dataDef.append(('BXLUMIVALUE','blob')) dataDef.append(('BXLUMIERROR','blob')) dataDef.append(('BXLUMIQUALITY','blob')) dataDef.append(('ALGONAME','string')) perbunchiData=[] dbsession.transaction().start(False) iddealer=idDealer.idDealer(dbsession.nominalSchema()) db=dbUtil.dbUtil(dbsession.nominalSchema()) print('to insert lumidetail ') for algoname in ['OCC1','OCC2','ET']: for cmslsnum,instlumi in perlsrawdata.items(): lumisummary_id=summaryidlsmap[cmslsnum] lumidetail_id=iddealer.generateNextIDForTable('LUMIDETAIL') bxdata=array.array('f') bxerror=array.array('f') bxquality=array.array('h') for bxidx in range(1,3565): lumifraction=0.0 if bxidx in perbunchrawdata: lumifraction=perbunchrawdata[bxidx] bxlumivalue=float(instlumi*lumifraction)/float(bunchnorm) bxdata.append(bxlumivalue) bxerror.append(0.0) bxquality.append(1) bxdataBlob=CommonUtil.packArraytoBlob(bxdata) bxerrorBlob=CommonUtil.packArraytoBlob(bxerror) bxqualityBlob=CommonUtil.packArraytoBlob(bxquality) perbunchiData.append([('LUMISUMMARY_ID',lumisummary_id),('LUMIDETAIL_ID',lumidetail_id),('BXLUMIVALUE',bxdataBlob),('BXLUMIERROR',bxerrorBlob),('BXLUMIQUALITY',bxqualityBlob),('ALGONAME',algoname)]) db.bulkInsert('LUMIDETAIL',dataDef,perbunchiData) dbsession.transaction().commit() return
def updateLSTrg(dbsession, runnum, perlsrawdata): ''' input: perlsrawdata [(cmslsnum,normbitcount,normbitprescale),(cmslsnum,normbitcount,normbitprescale)...] update lstrg set bitzerocount=:normbitcount,bitzeroprescale=:normbitprescale where runnum=:runnum and cmslsnum=:cmslsnum ''' dataDef = [] dataDef.append(('CMSLSNUM', 'unsigned int')) dataDef.append(('BITZEROCOUNT', 'unsigned int')) dataDef.append(('BITZEROPRESCALE', 'unsigned int')) dataDef.append(('RUNNUM', 'unsigned int')) dataDef.append(('CMSLSNUM', 'unsigned int')) bulkinput = [] dbsession.transaction().start(False) db = dbUtil.dbUtil(dbsession.nominalSchema()) updateAction = 'BITZEROCOUNT=:bitzerocount,BITZEROPRESCALE=:bitzeroprescale' updateCondition = 'RUNNUM=:runnum AND CMSLSNUM=:cmslsnum' bindvarDef = [('bitzerocount', 'unsigned int'), ('bitzeroprescale', 'unsigned int'), ('runnum', 'unsigned int'), ('cmslsnum', 'unsigned int')] for (cmslsnum, normbitcount, normbitprescale) in perlsrawdata: bulkinput.append([('bitzerocount', normbitcount), ('bitzeroprescale', normbitprescale), ('runnum', runnum), ('cmslsnum', cmslsnum)]) db.updateRows('LSTRG', updateAction, updateCondition, bindvarDef, bulkinput) #dbsession.transaction().rollback() dbsession.transaction().commit()
def describeLumi(dbsession): print 'lumi db schema dump...' dbsession.transaction().start(True) schema=dbsession.nominalSchema() db=dbUtil.dbUtil(schema) db.describeSchema() dbsession.transaction().commit()
def insertLumiDetaildata(dbsession,perlsrawdata,perbunchrawdata,summaryidlsmap): dataDef=[] dataDef.append(('LUMISUMMARY_ID','unsigned long long')) dataDef.append(('LUMIDETAIL_ID','unsigned long long')) dataDef.append(('BXLUMIVALUE','blob')) dataDef.append(('BXLUMIERROR','blob')) dataDef.append(('BXLUMIQUALITY','blob')) dataDef.append(('ALGONAME','string')) perbunchiData=[] dbsession.transaction().start(False) iddealer=idDealer.idDealer(dbsession.nominalSchema()) db=dbUtil.dbUtil(dbsession.nominalSchema()) print('to insert lumidetail ') for algoname in ['OCC1','OCC2','ET']: for cmslsnum,instlumi in perlsrawdata.items(): lumisummary_id=summaryidlsmap[cmslsnum] lumidetail_id=iddealer.generateNextIDForTable('LUMIDETAIL') bxdata=array.array('f') bxerror=array.array('f') bxquality=array.array('h') for bxidx in range(1,3565): lumifraction=0.0 if bxidx in perbunchrawdata: lumifraction=perbunchrawdata[bxidx] bxlumivalue=float(instlumi*lumifraction)/float(bunchnorm) bxdata.append(bxlumivalue) bxerror.append(0.0) bxquality.append(1) bxdataBlob=CommonUtil.packArraytoBlob(bxdata) bxerrorBlob=CommonUtil.packArraytoBlob(bxerror) bxqualityBlob=CommonUtil.packArraytoBlob(bxquality) perbunchiData.append([('LUMISUMMARY_ID',lumisummary_id),('LUMIDETAIL_ID',lumidetail_id),('BXLUMIVALUE',bxdataBlob),('BXLUMIERROR',bxerrorBlob),('BXLUMIQUALITY',bxqualityBlob),('ALGONAME',algoname)]) db.bulkInsert('LUMIDETAIL',dataDef,perbunchiData) dbsession.transaction().commit() return
def describeLumi(dbsession): print('lumi db schema dump...') dbsession.transaction().start(True) schema = dbsession.nominalSchema() db = dbUtil.dbUtil(schema) db.describeSchema() dbsession.transaction().commit()
def updatedb(schema,runmap,lumitype): ''' update lumidata set starttime=:rstart,stoptime=:rstop,nls=:nls where runnum=:runnum and data_id=:lumidataid ''' lumidatatableName='' if lumitype=='HF': lumitableName=nameDealer.lumidataTableName() elif lumitype == 'PIXEL': lumitableName = nameDealer.pixellumidataTableName() else: assert False, "ERROR Unknown lumitype '%s'" % lumitype t=lumiTime.lumiTime() setClause='STARTTIME=:runstarttime,STOPTIME=:runstoptime,NLS=:nls' updateCondition='RUNNUM=:runnum AND DATA_ID=:lumidataid' inputData=coral.AttributeList() inputData.extend('runstarttime','time stamp') inputData.extend('runstoptime','time stamp') inputData.extend('nls','unsigned int') inputData.extend('runnum','unsigned int') inputData.extend('lumidataid','unsigned long long') db=dbUtil.dbUtil(schema) for (run,lumidataid) in runmap.keys(): [runstarttime,runstoptime,nls]=runmap[(run,lumidataid)] runstartT=coral.TimeStamp(runstarttime.year,runstarttime.month,runstarttime.day,runstarttime.hour,runstarttime.minute,runstarttime.second,runstarttime.microsecond*1000) runstopT=coral.TimeStamp(runstoptime.year,runstoptime.month,runstoptime.day,runstoptime.hour,runstoptime.minute,runstoptime.second,runstoptime.microsecond*1000) inputData['runstarttime'].setData(runstartT) inputData['runstoptime'].setData(runstopT) inputData['nls'].setData(nls) inputData['runnum'].setData(int(run)) inputData['lumidataid'].setData(int(lumidataid)) db.singleUpdate(lumitableName,setClause,updateCondition,inputData)
def updatedb(schema, runkeymap, keymaskmap): ''' update trgdata set algomask_h=:algomask_h,algomask_l=:algomask_l,techmask=:techmask where runnum=:runnum input: runkeymap keymaskmap ''' setClause = 'ALGOMASK_H=:algomask_h,ALGOMASK_L=:algomask_l,TECHMASK=:techmask' updateCondition = 'RUNNUM=:runnum' inputData = coral.AttributeList() inputData.extend('algomask_h', 'unsigned long long') inputData.extend('algomask_l', 'unsigned long long') inputData.extend('techmask', 'unsigned long long') inputData.extend('runnum', 'unsigned int') db = dbUtil.dbUtil(schema) for runnum in runkeymap.keys(): gt_rs_key = runkeymap[runnum] print runnum, gt_rs_key [algo_h, algo_l, tech] = keymaskmap[gt_rs_key] inputData['algomask_h'].setData(algo_h) inputData['algomask_l'].setData(algo_l) inputData['techmask'].setData(tech) inputData['runnum'].setData(runnum) r = db.singleUpdate(nameDealer.trgdataTableName(), setClause, updateCondition, inputData) if r > 0: print 'updated'
def updatedb(schema,runkeymap,keymaskmap): ''' update trgdata set algomask_h=:algomask_h,algomask_l=:algomask_l,techmask=:techmask where runnum=:runnum input: runkeymap keymaskmap ''' setClause='ALGOMASK_H=:algomask_h,ALGOMASK_L=:algomask_l,TECHMASK=:techmask' updateCondition='RUNNUM=:runnum' inputData=coral.AttributeList() inputData.extend('algomask_h','unsigned long long') inputData.extend('algomask_l','unsigned long long') inputData.extend('techmask','unsigned long long') inputData.extend('runnum','unsigned int') db=dbUtil.dbUtil(schema) for runnum in runkeymap.keys(): gt_rs_key=runkeymap[runnum] print(runnum,gt_rs_key) [algo_h,algo_l,tech]=keymaskmap[gt_rs_key] inputData['algomask_h'].setData(algo_h) inputData['algomask_l'].setData(algo_l) inputData['techmask'].setData(tech) inputData['runnum'].setData(runnum) r=db.singleUpdate(nameDealer.trgdataTableName(),setClause,updateCondition,inputData) if r>0: print('updated')
def insertLumiSummarydata(dbsession, perlsrawdata): """ input: perlsrawdata {cmslsnum:instlumi} insert into lumisummary(lumisummary_id,runnum,cmslsnum,lumilsnum,lumiversion,dtnorm,lhcnorm,instlumi,instlumierror,instlumiquality,cmsalive,startorbit,numorbit,lumisectionquality,beamenergy,beamstatus) values() """ summaryidlsmap = {} dataDef = [] dataDef.append(("LUMISUMMARY_ID", "unsigned long long")) dataDef.append(("RUNNUM", "unsigned int")) dataDef.append(("CMSLSNUM", "unsigned int")) dataDef.append(("LUMILSNUM", "unsigned int")) dataDef.append(("LUMIVERSION", "string")) dataDef.append(("DTNORM", "float")) dataDef.append(("LHCNORM", "float")) dataDef.append(("INSTLUMI", "float")) dataDef.append(("INSTLUMIERROR", "float")) dataDef.append(("INSTLUMIQUALITY", "short")) dataDef.append(("CMSALIVE", "short")) dataDef.append(("STARTORBIT", "unsigned int")) dataDef.append(("NUMORBIT", "unsigned int")) dataDef.append(("LUMISECTIONQUALITY", "short")) dataDef.append(("BEAMENERGY", "float")) dataDef.append(("BEAMSTATUS", "string")) perlsiData = [] dbsession.transaction().start(False) iddealer = idDealer.idDealer(dbsession.nominalSchema()) db = dbUtil.dbUtil(dbsession.nominalSchema()) lumisummary_id = 0 for cmslsnum, instlumi in perlsrawdata.items(): mystartorbit = startorbit + numorbit * (cmslsnum - 1) lumisummary_id = iddealer.generateNextIDForTable("LUMISUMMARY") summaryidlsmap[cmslsnum] = lumisummary_id perlsiData.append( [ ("LUMISUMMARY_ID", lumisummary_id), ("RUNNUM", runnum), ("CMSLSNUM", cmslsnum), ("LUMILSNUM", cmslsnum), ("LUMIVERSION", lumiversion), ("DTNORM", dtnorm), ("LHCNORM", lhcnorm), ("INSTLUMI", instlumi), ("INSTLUMIERROR", 0.0), ("CMSALIVE", cmsalive), ("STARTORBIT", mystartorbit), ("NUMORBIT", numorbit), ("LUMISECTIONQUALITY", 1), ("BEAMENERGY", beamenergy), ("BEAMSTATUS", beamstatus), ] ) db.bulkInsert("LUMISUMMARY", dataDef, perlsiData) dbsession.transaction().commit() print "lumisummary to insert : ", perlsiData print "summaryidlsmap ", summaryidlsmap return summaryidlsmap
def insertIntglumiData(dbsession, intlumitorun, bulksize=1000): ''' input intlumitorun [(runnumber,intglumiub)] insert into INTGLUMI(intglumi_id,runnum,startrun,intglumi) values() ''' if not intlumitorun or len(intlumitorun) == 0: return dataDef = [] dataDef.append(('RUNNUM', 'unsigned int')) dataDef.append(('STARTRUN', 'unsigned int')) dataDef.append(('INTGLUMI', 'float')) nrows = 0 committedrows = 0 perrunData = [] startrun = min([x[0] for x in intlumitorun]) try: for (runnum, intglumi) in intlumitorun: nrows += 1 committedrows += 1 perrunData.append([('RUNNUM', runnum), ('STARTRUN', startrun), ('INTGLUMI', intglumi)]) if nrows == bulksize: print 'committing ', nrows, ' rows' db = dbUtil.dbUtil(dbsession.nominalSchema()) dbsession.transaction().start(False) db.bulkInsert('INTGLUMI', dataDef, perrunData) dbsession.transaction().commit() nrows = 0 perrunData = [] elif committedrows == len(intlumitorun): print 'committing trg at the end ' dbsession.transaction().start(False) db = dbUtil.dbUtil(dbsession.nominalSchema()) db.bulkInsert('INTGLUMI', dataDef, perrunData) dbsession.transaction().commit() except Exception as e: dbsession.transaction().rollback() del dbsession raise Exception('insertIntglumiData: ' + str(e))
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 addEntry(schema,datatableName,entryinfo,branchinfo): ''' input: entryinfo (revision_id(0),entry_id(1),entry_name(2),data_id(3)) branchinfo (branch_id,branch_name) 1.allocate and insert a new revision into the revisions table 2.allocate and insert a new entry into the entry table with the new revision 3.inset into data_rev table with new data_id ,revision)id mapping insert into revisions(revision_id,branch_id,branch_name,comment,ctime) values() insert into datatablename_entries (entry_id,revision_id) values() insert into datatablename_rev(data_id,revision_id) values() ''' try: revisiontableName=nameDealer.revisionTableName() entrytableName=nameDealer.entryTableName(datatableName) revtableName=nameDealer.revmapTableName(datatableName) db=dbUtil.dbUtil(schema) tabrowDefDict={} tabrowDefDict['REVISION_ID']='unsigned long long' tabrowDefDict['BRANCH_ID']='unsigned long long' tabrowDefDict['BRANCH_NAME']='string' tabrowDefDict['CTIME']='time stamp' tabrowValueDict={} tabrowValueDict['REVISION_ID']=entryinfo[0] tabrowValueDict['BRANCH_ID']=branchinfo[0] tabrowValueDict['BRANCH_NAME']=branchinfo[1] tabrowValueDict['CTIME']=coral.TimeStamp() db.insertOneRow(revisiontableName,tabrowDefDict,tabrowValueDict) tabrowDefDict={} tabrowDefDict['REVISION_ID']='unsigned long long' tabrowDefDict['ENTRY_ID']='unsigned long long' tabrowDefDict['NAME']='string' tabrowValueDict={} tabrowValueDict['REVISION_ID']=entryinfo[0] tabrowValueDict['ENTRY_ID']=entryinfo[1] tabrowValueDict['NAME']=entryinfo[2] db.insertOneRow(entrytableName,tabrowDefDict,tabrowValueDict) tabrowDefDict={} tabrowDefDict['REVISION_ID']='unsigned long long' tabrowDefDict['DATA_ID']='unsigned long long' tabrowValueDict={} tabrowValueDict['REVISION_ID']=entryinfo[0] tabrowValueDict['DATA_ID']=entryinfo[3] db.insertOneRow(revtableName,tabrowDefDict,tabrowValueDict) except: raise
def insertIntglumiData(dbsession,intlumitorun,bulksize=1000): ''' input intlumitorun [(runnumber,intglumiub)] insert into INTGLUMI(intglumi_id,runnum,startrun,intglumi) values() ''' if not intlumitorun or len(intlumitorun)==0: return dataDef=[] dataDef.append(('RUNNUM','unsigned int')) dataDef.append(('STARTRUN','unsigned int')) dataDef.append(('INTGLUMI','float')) nrows=0 committedrows=0 perrunData=[] startrun=min([x[0] for x in intlumitorun]) try: for (runnum,intglumi) in intlumitorun: nrows+=1 committedrows+=1 perrunData.append([('RUNNUM',runnum),('STARTRUN',startrun),('INTGLUMI',intglumi)]) if nrows==bulksize: print('committing ',nrows,' rows') db=dbUtil.dbUtil(dbsession.nominalSchema()) dbsession.transaction().start(False) db.bulkInsert('INTGLUMI',dataDef,perrunData) dbsession.transaction().commit() nrows=0 perrunData=[] elif committedrows==len(intlumitorun): print('committing trg at the end ') dbsession.transaction().start(False) db=dbUtil.dbUtil(dbsession.nominalSchema()) db.bulkInsert('INTGLUMI',dataDef,perrunData) dbsession.transaction().commit() except Exception as e: dbsession.transaction().rollback() del dbsession raise Exception('insertIntglumiData: '+str(e))
def addindb(session,datatag,normtag,lumidata,bulksize): ''' input : [fill,run,lumils,cmsls,lstime,beamstatus,beamenergy,delivered,recorded,avgpu] ''' hfresultDefDict=[('RUNNUM','unsigned int'),('LS','unsigned int'),('CMSLS','unsigned int'),('FILLNUM','unsigned int'),('TIME','time stamp'),('BEAM_STATUS','string'),('ENERGY','unsigned int'),('DELIVERED','float'),('RECORDED','float'),('AVG_PU','float'),('DATA_VERSION','string'),('NORM_VERSION','string'),('INSERT_TIME','time stamp')] committedrows=0 nrows=0 bulkvalues=[] lute=lumiTime.lumiTime() try: for datum in lumidata: [fillnum,runnum,lumils,cmsls,lstime_char,beamstatus,beamenergy,delivered,recorded,avgpu]=datum inserttime=coral.TimeStamp() lstime=lute.StrToDatetime(lstime_char,customfm='%m/%d/%y %H:%M:%S') corallstime=coral.TimeStamp(lstime.year,lstime.month,lstime.day,lstime.hour,lstime.minute,lstime.second,0) bulkvalues.append([('RUNNUM',runnum),('LS',lumils),('CMSLS',cmsls),('FILLNUM',fillnum),('TIME',corallstime),('BEAM_STATUS',beamstatus),('ENERGY',beamenergy),('DELIVERED',delivered),('RECORDED',recorded),('AVG_PU',avgpu),('DATA_VERSION',datatag),('NORM_VERSION',normtag),('INSERT_TIME',inserttime)]) nrows+=1 committedrows+=1 if nrows==bulksize: print 'committing trg in LS chunck ',nrows db=dbUtil.dbUtil(session.nominalSchema()) session.transaction().start(False) db.bulkInsert('HFLUMIRESULT',hfresultDefDict,bulkvalues) session.transaction().commit() nrows=0 bulkvalues=[] elif committedrows==len(lumidata): print 'committing at the end ' db=dbUtil.dbUtil(session.nominalSchema()) session.transaction().start(False) db.bulkInsert('HFLUMIRESULT',hfresultDefDict,bulkvalues) session.transaction().commit() except : print 'error in addindb' raise
def createBranch(schema, name, parentname, comment=''): ''' create a new branch/tag under given parentnode insert into revisions(revision_id,branch_id,branch_name,name,comment,ctime) values() return (revisionid,parentid,parentname) ''' try: parentid = None revisionid = 0 if not parentname is None: qHandle = schema.newQuery() qHandle.addToTableList(nameDealer.revisionTableName()) qHandle.addToOutputList('REVISION_ID', 'revision_id') qCondition = coral.AttributeList() qCondition.extend('parentname', 'string') qCondition['parentname'].setData(parentname) qResult = coral.AttributeList() qResult.extend('revision_id', 'unsigned long long') qHandle.defineOutput(qResult) qHandle.setCondition('NAME=:parentname', qCondition) cursor = qHandle.execute() while cursor.next(): parentid = cursor.currentRow()['revision_id'].data() del qHandle else: parentname = 'ROOT' iddealer = idDealer.idDealer(schema) revisionid = iddealer.generateNextIDForTable( nameDealer.revisionTableName()) db = dbUtil.dbUtil(schema) tabrowDefDict = {} tabrowDefDict['REVISION_ID'] = 'unsigned long long' tabrowDefDict['BRANCH_ID'] = 'unsigned long long' tabrowDefDict['BRANCH_NAME'] = 'string' tabrowDefDict['NAME'] = 'string' tabrowDefDict['COMMENT'] = 'string' tabrowDefDict['CTIME'] = 'time stamp' tabrowValueDict = {} tabrowValueDict['REVISION_ID'] = revisionid tabrowValueDict['BRANCH_ID'] = parentid tabrowValueDict['BRANCH_NAME'] = parentname tabrowValueDict['NAME'] = name tabrowValueDict['COMMENT'] = comment tabrowValueDict['CTIME'] = coral.TimeStamp() db.insertOneRow(nameDealer.revisionTableName(), tabrowDefDict, tabrowValueDict) return (revisionid, parentid, parentname) except: raise
def insertLumiDetaildata(dbsession, perlsrawdata, perbunchrawdata, summaryidlsmap): dataDef = [] dataDef.append(("LUMISUMMARY_ID", "unsigned long long")) dataDef.append(("LUMIDETAIL_ID", "unsigned long long")) dataDef.append(("BXLUMIVALUE", "blob")) dataDef.append(("BXLUMIERROR", "blob")) dataDef.append(("BXLUMIQUALITY", "blob")) dataDef.append(("ALGONAME", "string")) perbunchiData = [] dbsession.transaction().start(False) iddealer = idDealer.idDealer(dbsession.nominalSchema()) db = dbUtil.dbUtil(dbsession.nominalSchema()) print "to insert lumidetail " for algoname in ["OCC1", "OCC2", "ET"]: for cmslsnum, instlumi in perlsrawdata.items(): lumisummary_id = summaryidlsmap[cmslsnum] lumidetail_id = iddealer.generateNextIDForTable("LUMIDETAIL") print "cmslsnum ", lumidetail_id, lumisummary_id bxdataocc1 = array.array("f") bxdataocc2 = array.array("f") bxdataet = array.array("f") bxerror = array.array("f") bxquality = array.array("h") for bxidx in range(1, 3565): lumifraction = 0.0 if perbunchrawdata.has_key(bxidx): lumifraction = perbunchrawdata[bxidx] bxlumivalue = float(instlumi * lumifraction) bxdataocc1.append(bxlumivalue) bxdataocc2.append(bxlumivalue) bxdataet.append(bxlumivalue) bxdataocc1Blob = CommonUtil.packArraytoBlob(bxdataocc1) bxdataocc2Blob = CommonUtil.packArraytoBlob(bxdataocc2) bxdataetBlob = CommonUtil.packArraytoBlob(bxdataet) bxqualityBlob = CommonUtil.packArraytoBlob(bxquality) perbunchiData.append( [ ("LUMISUMMARY_ID", lumisummary_id), ("LUMIDETAIL_ID", lumidetail_id), ("BXLUMIVALUE", bxdataocc1Blob), ("BXLUMIERROR", bxdataocc2Blob), ("BXLUMIQUALITY", bxqualityBlob), ("ALGONAME", algoname), ] ) db.bulkInsert("LUMIDETAIL", dataDef, perbunchiData) print perbunchiData dbsession.transaction().commit() return
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 createNorm(schema, normname, lumitype, istypedefault, branchinfo, comment=''): ''' branchinfo(normrevisionid,branchname) ''' try: entry_id = revisionDML.entryInBranch(schema, nameDealer.luminormv2TableName(), normname, branchinfo[1]) if entry_id is None: (revision_id, entry_id, data_id) = revisionDML.bookNewEntry( schema, nameDealer.luminormv2TableName()) entryinfo = (revision_id, entry_id, normname, data_id) revisionDML.addEntry(schema, nameDealer.luminormv2TableName(), entryinfo, branchinfo) else: (revision_id, data_id) = revisionDML.bookNewRevision( schema, nameDealer.luminormv2TableName()) revisionDML.addRevision(schema, nameDealer.luminormv2TableName(), (revision_id, data_id), branchinfo) tabrowDefDict = { 'DATA_ID': 'unsigned long long', 'ENTRY_ID': 'unsigned long long', 'ENTRY_NAME': 'string', 'LUMITYPE': 'string', 'ISTYPEDEFAULT': 'unsigned int', 'COMMENT': 'string', 'CTIME': 'time stamp' } tabrowValueDict = { 'DATA_ID': data_id, 'ENTRY_ID': entry_id, 'ENTRY_NAME': normname, 'LUMITYPE': lumitype, 'ISTYPEDEFAULT': istypedefault, 'COMMENT': comment, 'CTIME': coral.TimeStamp() } db = dbUtil.dbUtil(schema) db.insertOneRow(nameDealer.luminormv2TableName(), tabrowDefDict, tabrowValueDict) return (revision_id, entry_id, data_id) except: raise
def insertLumiSummarydata(dbsession, perlsrawdata): ''' input: perlsrawdata {cmslsnum:instlumi} insert into lumisummary(lumisummary_id,runnum,cmslsnum,lumilsnum,lumiversion,dtnorm,lhcnorm,instlumi,instlumierror,instlumiquality,cmsalive,startorbit,numorbit,lumisectionquality,beamenergy,beamstatus) values() ''' summaryidlsmap = {} dataDef = [] dataDef.append(('LUMISUMMARY_ID', 'unsigned long long')) dataDef.append(('RUNNUM', 'unsigned int')) dataDef.append(('CMSLSNUM', 'unsigned int')) dataDef.append(('LUMILSNUM', 'unsigned int')) dataDef.append(('LUMIVERSION', 'string')) dataDef.append(('DTNORM', 'float')) dataDef.append(('LHCNORM', 'float')) dataDef.append(('INSTLUMI', 'float')) dataDef.append(('INSTLUMIERROR', 'float')) dataDef.append(('INSTLUMIQUALITY', 'short')) dataDef.append(('CMSALIVE', 'short')) dataDef.append(('STARTORBIT', 'unsigned int')) dataDef.append(('NUMORBIT', 'unsigned int')) dataDef.append(('LUMISECTIONQUALITY', 'short')) dataDef.append(('BEAMENERGY', 'float')) dataDef.append(('BEAMSTATUS', 'string')) perlsiData = [] dbsession.transaction().start(False) iddealer = idDealer.idDealer(dbsession.nominalSchema()) db = dbUtil.dbUtil(dbsession.nominalSchema()) lumisummary_id = 0 for cmslsnum, instlumi in perlsrawdata.items(): mystartorbit = startorbit + numorbit * (cmslsnum - 1) lumisummary_id = iddealer.generateNextIDForTable('LUMISUMMARY') summaryidlsmap[cmslsnum] = lumisummary_id perlsiData.append([('LUMISUMMARY_ID', lumisummary_id), ('RUNNUM', runnum), ('CMSLSNUM', cmslsnum), ('LUMILSNUM', cmslsnum), ('LUMIVERSION', lumiversion), ('DTNORM', dtnorm), ('LHCNORM', lhcnorm), ('INSTLUMI', instlumi), ('INSTLUMIERROR', 0.0), ('CMSALIVE', cmsalive), ('STARTORBIT', mystartorbit), ('NUMORBIT', numorbit), ('LUMISECTIONQUALITY', 1), ('BEAMENERGY', beamenergy), ('BEAMSTATUS', beamstatus)]) db.bulkInsert('LUMISUMMARY', dataDef, perlsiData) dbsession.transaction().commit() print('lumisummary to insert : ', perlsiData) print('summaryidlsmap ', summaryidlsmap) return summaryidlsmap
def updateLumiSummarydata(dbsession,fillinfo): ''' input: fillinfo [[fillnum,fillscheme,ncollidingbunches],[fillnum,fillscheme,ncollidingbunches]] update lumisummary set FILLSCHEME=:fillscheme, NCOLLIDINGBUNCHES=:ncollidingbunches where FILLNUM=:fillnum ''' dbsession.transaction().start(False) db=dbUtil.dbUtil(dbsession.nominalSchema()) updateAction='FILLSCHEME=:fillscheme,NCOLLIDINGBUNCHES=:ncollidingbunches' updateCondition='FILLNUM=:fillnum' bindvarDef=[('fillscheme','string'),('ncollidingbunches','unsigned int'),('fillnum','unsigned int')] bulkinput=[] for fillline in fillinfo: bulkinput.append([('fillscheme',fillline[1]),('ncollidingbunches',fillline[2]),('fillnum',fillline[0])]) db.updateRows('CMSRUNSUMMARY',updateAction,updateCondition,bindvarDef,bulkinput) dbsession.transaction().commit()
def createBranch(schema,name,parentname,comment=''): ''' create a new branch/tag under given parentnode insert into revisions(revision_id,branch_id,branch_name,name,comment,ctime) values() return (revisionid,parentid,parentname) ''' try: parentid=None revisionid=0 if not parentname is None: qHandle=schema.newQuery() qHandle.addToTableList( nameDealer.revisionTableName() ) qHandle.addToOutputList( 'REVISION_ID','revision_id' ) qCondition=coral.AttributeList() qCondition.extend('parentname','string') qCondition['parentname'].setData(parentname) qResult=coral.AttributeList() qResult.extend('revision_id','unsigned long long') qHandle.defineOutput(qResult) qHandle.setCondition('NAME=:parentname',qCondition) cursor=qHandle.execute() while next(cursor): parentid=cursor.currentRow()['revision_id'].data() del qHandle else: parentname='ROOT' iddealer=idDealer.idDealer(schema) revisionid=iddealer.generateNextIDForTable( nameDealer.revisionTableName() ) db=dbUtil.dbUtil(schema) tabrowDefDict={} tabrowDefDict['REVISION_ID']='unsigned long long' tabrowDefDict['BRANCH_ID']='unsigned long long' tabrowDefDict['BRANCH_NAME']='string' tabrowDefDict['NAME']='string' tabrowDefDict['COMMENT']='string' tabrowDefDict['CTIME']='time stamp' tabrowValueDict={} tabrowValueDict['REVISION_ID']=revisionid tabrowValueDict['BRANCH_ID']=parentid tabrowValueDict['BRANCH_NAME']=parentname tabrowValueDict['NAME']=name tabrowValueDict['COMMENT']=comment tabrowValueDict['CTIME']=coral.TimeStamp() db.insertOneRow(nameDealer.revisionTableName(),tabrowDefDict, tabrowValueDict ) return (revisionid,parentid,parentname) except: raise
def createValidation(dbsession): ''' lumivalidation table ''' dbsession.transaction().start(False) schema = dbsession.nominalSchema() db = dbUtil.dbUtil(schema) 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 createValidation(dbsession): ''' lumivalidation table ''' dbsession.transaction().start(False) schema=dbsession.nominalSchema() db=dbUtil.dbUtil(schema) 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 createValidation(dbsession): """ lumivalidation table """ dbsession.transaction().start(False) schema = dbsession.nominalSchema() db = dbUtil.dbUtil(schema) 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 insertValueToNormId(schema, normdataid, sincerun, corrector, amodetag, egev, parameters, comment=''): ''' insert into LUMINORMSV2DATA(DATA_ID,SINCERUN,CORRECTOR,...) values(normdataid,)sincerun,corrector,...); require len(parameters)>=1. input: parameterDict {'NORM_OCC1':normocc1,'NORM_OCC2':normocc2,'NORM_ET':normet,'NORM_PU':normpu,'DRIFT':drift,'A1':a1,...} output: ''' if len(parameters) == 0: raise ValueError('appendValueToNormId: at least one value is required') try: db = dbUtil.dbUtil(schema) tabrowDefDict = {} tabrowDefDict['DATA_ID'] = 'unsigned long long' tabrowDefDict['CORRECTOR'] = 'string' tabrowDefDict['SINCE'] = 'unsigned int' tabrowDefDict['AMODETAG'] = 'string' tabrowDefDict['NOMINALEGEV'] = 'unsigned int' tabrowDefDict['COMMENT'] = 'string' tabrowValueDict = {} tabrowValueDict['DATA_ID'] = normdataid tabrowValueDict['CORRECTOR'] = corrector tabrowValueDict['SINCE'] = sincerun tabrowValueDict['AMODETAG'] = amodetag tabrowValueDict['NOMINALEGEV'] = egev tabrowValueDict['COMMENT'] = comment for paramname, paramvalue in parameters.items(): try: floatparam = float(paramvalue) tabrowDefDict[paramname.upper()] = 'float' tabrowValueDict[paramname.upper()] = float(paramvalue) except ValueError: tabrowDefDict[paramname.upper()] = 'string' tabrowValueDict[paramname.upper()] = paramvalue db.insertOneRow(nameDealer.luminormv2dataTableName(), tabrowDefDict, tabrowValueDict) except: raise
def demoteNormFromTypeDefault(schema,normname,lumitype): ''' demote norm from typedefault to non default ''' try: thisnormid=normIdByName(schema,normname) if not thisnormid: raise ValueError(normname+' does not exist, nothing to update') setClause='ISTYPEDEFAULT=0' updateCondition='DATA_ID=:thisnormid AND LUMITYPE=:lumitype' inputData=coral.AttributeList() inputData.extend('thisnormid','unsigned long long') inputData.extend('LUMITYPE','string') inputData['thisnormid'].setData(thisnormid) inputData['LUMITYPE'].setData(lumitype) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.luminormv2TableName(),setClause,updateCondition,inputData) except : raise
def demoteNormFromTypeDefault(schema,normname,lumitype): ''' demote norm from typedefault to non default ''' try: thisnormid=normIdByName(schema,normname) if not thisnormid: raise ValueError(normname+' does not exist, nothing to update') setClause='ISTYPEDEFAULT=0' updateCondition='DATA_ID=:thisnormid AND LUMITYPE=:lumitype' inputData=coral.AttributeList() inputData.extend('thisnormid','unsigned long long') inputData.extend('LUMITYPE','string') inputData['thisnormid'].setData(thisnormid) inputData['LUMITYPE'].setData(lumitype) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.luminormv2TableName(),setClause,updateCondition,inputData) except : raise
def insertLumiSummarydata(dbsession,runnum,perlsrawdata,deliveredonly=False): ''' input: perlsrawdata {cmslsnum:instlumi} insert into lumisummary(lumisummary_id,runnum,cmslsnum,lumilsnum,lumiversion,dtnorm,lhcnorm,instlumi,instlumierror,instlumiquality,cmsalive,startorbit,numorbit,lumisectionquality,beamenergy,beamstatus) values() ''' summaryidlsmap={} dataDef=[] dataDef.append(('LUMISUMMARY_ID','unsigned long long')) dataDef.append(('RUNNUM','unsigned int')) dataDef.append(('CMSLSNUM','unsigned int')) dataDef.append(('LUMILSNUM','unsigned int')) dataDef.append(('LUMIVERSION','string')) dataDef.append(('DTNORM','float')) dataDef.append(('LHCNORM','float')) dataDef.append(('INSTLUMI','float')) dataDef.append(('INSTLUMIERROR','float')) dataDef.append(('INSTLUMIQUALITY','short')) dataDef.append(('CMSALIVE','short')) dataDef.append(('STARTORBIT','unsigned int')) dataDef.append(('NUMORBIT','unsigned int')) dataDef.append(('LUMISECTIONQUALITY','short')) dataDef.append(('BEAMENERGY','float')) dataDef.append(('BEAMSTATUS','string')) perlsiData=[] dbsession.transaction().start(False) iddealer=idDealer.idDealer(dbsession.nominalSchema()) db=dbUtil.dbUtil(dbsession.nominalSchema()) lumisummary_id=0 for cmslsnum,instlumi in perlsrawdata.items(): mystartorbit=startorbit+numorbit*(cmslsnum-1) lumisummary_id=iddealer.generateNextIDForTable('LUMISUMMARY') summaryidlsmap[cmslsnum]=lumisummary_id if deliveredonly: perlsiData.append([('LUMISUMMARY_ID',lumisummary_id),('RUNNUM',runnum),('CMSLSNUM',0),('LUMILSNUM',cmslsnum),('LUMIVERSION',lumiversion),('DTNORM',dtnorm),('LHCNORM',lhcnorm),('INSTLUMI',instlumi),('INSTLUMIERROR',0.0),('CMSALIVE',0),('STARTORBIT',mystartorbit),('NUMORBIT',numorbit),('LUMISECTIONQUALITY',1),('BEAMENERGY',beamenergy),('BEAMSTATUS',beamstatus)]) else: perlsiData.append([('LUMISUMMARY_ID',lumisummary_id),('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('LUMILSNUM',cmslsnum),('LUMIVERSION',lumiversion),('DTNORM',dtnorm),('LHCNORM',lhcnorm),('INSTLUMI',instlumi),('INSTLUMIERROR',0.0),('CMSALIVE',1),('STARTORBIT',mystartorbit),('NUMORBIT',numorbit),('LUMISECTIONQUALITY',1),('BEAMENERGY',beamenergy),('BEAMSTATUS',beamstatus)]) db.bulkInsert('LUMISUMMARY',dataDef,perlsiData) dbsession.transaction().commit() print 'lumisummary to insert : ',perlsiData print 'summaryidlsmap ',summaryidlsmap return summaryidlsmap
def createNorm(schema,normname,lumitype,istypedefault,branchinfo,comment=''): ''' branchinfo(normrevisionid,branchname) ''' try: entry_id=revisionDML.entryInBranch(schema,nameDealer.luminormv2TableName(),normname,branchinfo[1]) if entry_id is None: (revision_id,entry_id,data_id)=revisionDML.bookNewEntry(schema,nameDealer.luminormv2TableName()) entryinfo=(revision_id,entry_id,normname,data_id) revisionDML.addEntry(schema,nameDealer.luminormv2TableName(),entryinfo,branchinfo) else: (revision_id,data_id)=revisionDML.bookNewRevision( schema,nameDealer.luminormv2TableName() ) revisionDML.addRevision(schema,nameDealer.luminormv2TableName(),(revision_id,data_id),branchinfo) tabrowDefDict={'DATA_ID':'unsigned long long','ENTRY_ID':'unsigned long long','ENTRY_NAME':'string','LUMITYPE':'string','ISTYPEDEFAULT':'unsigned int','COMMENT':'string','CTIME':'time stamp'} tabrowValueDict={'DATA_ID':data_id,'ENTRY_ID':entry_id,'ENTRY_NAME':normname,'LUMITYPE':lumitype,'ISTYPEDEFAULT':istypedefault,'COMMENT':comment,'CTIME':coral.TimeStamp()} db=dbUtil.dbUtil(schema) db.insertOneRow(nameDealer.luminormv2TableName(),tabrowDefDict,tabrowValueDict) return (revision_id,entry_id,data_id) except : raise
def updateLumiSummarydata(dbsession, fillinfo): ''' input: fillinfo [[fillnum,fillscheme,ncollidingbunches],[fillnum,fillscheme,ncollidingbunches]] update lumisummary set FILLSCHEME=:fillscheme, NCOLLIDINGBUNCHES=:ncollidingbunches where FILLNUM=:fillnum ''' dbsession.transaction().start(False) db = dbUtil.dbUtil(dbsession.nominalSchema()) updateAction = 'FILLSCHEME=:fillscheme,NCOLLIDINGBUNCHES=:ncollidingbunches' updateCondition = 'FILLNUM=:fillnum' bindvarDef = [('fillscheme', 'string'), ('ncollidingbunches', 'unsigned int'), ('fillnum', 'unsigned int')] bulkinput = [] for fillline in fillinfo: bulkinput.append([('fillscheme', fillline[1]), ('ncollidingbunches', fillline[2]), ('fillnum', fillline[0])]) db.updateRows('CMSRUNSUMMARY', updateAction, updateCondition, bindvarDef, bulkinput) dbsession.transaction().commit()
def updateLSTrg(dbsession,runnum,perlsrawdata): ''' input: perlsrawdata [(cmslsnum,normbitcount,normbitprescale),(cmslsnum,normbitcount,normbitprescale)...] update lstrg set bitzerocount=:normbitcount,bitzeroprescale=:normbitprescale where runnum=:runnum and cmslsnum=:cmslsnum ''' dataDef=[] dataDef.append(('CMSLSNUM','unsigned int')) dataDef.append(('BITZEROCOUNT','unsigned int')) dataDef.append(('BITZEROPRESCALE','unsigned int')) dataDef.append(('RUNNUM','unsigned int')) dataDef.append(('CMSLSNUM','unsigned int')) bulkinput=[] dbsession.transaction().start(False) db=dbUtil.dbUtil(dbsession.nominalSchema()) updateAction='BITZEROCOUNT=:bitzerocount,BITZEROPRESCALE=:bitzeroprescale' updateCondition='RUNNUM=:runnum AND CMSLSNUM=:cmslsnum' bindvarDef=[('bitzerocount','unsigned int'),('bitzeroprescale','unsigned int'),('runnum','unsigned int'),('cmslsnum','unsigned int')] for (cmslsnum,normbitcount,normbitprescale) in perlsrawdata: bulkinput.append([('bitzerocount',normbitcount),('bitzeroprescale',normbitprescale),('runnum',runnum),('cmslsnum',cmslsnum)]) db.updateRows('LSTRG',updateAction,updateCondition,bindvarDef,bulkinput) #dbsession.transaction().rollback() dbsession.transaction().commit()
def addRevision(schema,datatableName,revisioninfo,branchinfo): ''' 1.insert a new revision into the revisions table 2.insert into data_id, revision_id pair to datatable_revmap insert into revisions(revision_id,branch_id,branch_name,ctime) values() insert into datatable_rev(data_id,revision_id) values()) input: revisioninfo (revision_id(0),data_id(1)) branchinfo (branch_id(0),branch_name(1)) ''' try: revisiontableName=nameDealer.revisionTableName() revtableName=nameDealer.revmapTableName(datatableName) db=dbUtil.dbUtil(schema) tabrowDefDict={} tabrowDefDict['REVISION_ID']='unsigned long long' tabrowDefDict['BRANCH_ID']='unsigned long long' tabrowDefDict['BRANCH_NAME']='string' tabrowDefDict['CTIME']='time stamp' tabrowValueDict={} tabrowValueDict['REVISION_ID']=revisioninfo[0] tabrowValueDict['BRANCH_ID']=branchinfo[0] tabrowValueDict['BRANCH_NAME']=branchinfo[1] tabrowValueDict['CTIME']=coral.TimeStamp() db.insertOneRow(revisiontableName,tabrowDefDict,tabrowValueDict) tabrowDefDict={} tabrowDefDict['REVISION_ID']='unsigned long long' tabrowDefDict['DATA_ID']='unsigned long long' tabrowValueDict={} tabrowValueDict['REVISION_ID']=revisioninfo[0] tabrowValueDict['DATA_ID']=revisioninfo[1] db.insertOneRow(revtableName,tabrowDefDict,tabrowValueDict) except: raise
def updatedb(schema, runmap, lumitype): ''' update lumidata set starttime=:rstart,stoptime=:rstop,nls=:nls where runnum=:runnum and data_id=:lumidataid ''' lumidatatableName = '' if lumitype == 'HF': lumitableName = nameDealer.lumidataTableName() elif lumitype == 'PIXEL': lumitableName = nameDealer.pixellumidataTableName() else: assert False, "ERROR Unknown lumitype '%s'" % lumitype t = lumiTime.lumiTime() setClause = 'STARTTIME=:runstarttime,STOPTIME=:runstoptime,NLS=:nls' updateCondition = 'RUNNUM=:runnum AND DATA_ID=:lumidataid' inputData = coral.AttributeList() inputData.extend('runstarttime', 'time stamp') inputData.extend('runstoptime', 'time stamp') inputData.extend('nls', 'unsigned int') inputData.extend('runnum', 'unsigned int') inputData.extend('lumidataid', 'unsigned long long') db = dbUtil.dbUtil(schema) for (run, lumidataid) in runmap.keys(): [runstarttime, runstoptime, nls] = runmap[(run, lumidataid)] runstartT = coral.TimeStamp(runstarttime.year, runstarttime.month, runstarttime.day, runstarttime.hour, runstarttime.minute, runstarttime.second, runstarttime.microsecond * 1000) runstopT = coral.TimeStamp(runstoptime.year, runstoptime.month, runstoptime.day, runstoptime.hour, runstoptime.minute, runstoptime.second, runstoptime.microsecond * 1000) inputData['runstarttime'].setData(runstartT) inputData['runstoptime'].setData(runstopT) inputData['nls'].setData(nls) inputData['runnum'].setData(int(run)) inputData['lumidataid'].setData(int(lumidataid)) db.singleUpdate(lumitableName, setClause, updateCondition, inputData)
def insertValueToNormId(schema,normdataid,sincerun,corrector,amodetag,egev,parameters,comment=''): ''' insert into LUMINORMSV2DATA(DATA_ID,SINCERUN,CORRECTOR,...) values(normdataid,)sincerun,corrector,...); require len(parameters)>=1. input: parameterDict {'NORM_OCC1':normocc1,'NORM_OCC2':normocc2,'NORM_ET':normet,'NORM_PU':normpu,'DRIFT':drift,'A1':a1,...} output: ''' if len(parameters)==0: raise ValueError('appendValueToNormId: at least one value is required') try: db=dbUtil.dbUtil(schema) tabrowDefDict={} tabrowDefDict['DATA_ID']='unsigned long long' tabrowDefDict['CORRECTOR']='string' tabrowDefDict['SINCE']='unsigned int' tabrowDefDict['AMODETAG']='string' tabrowDefDict['NOMINALEGEV']='unsigned int' tabrowDefDict['COMMENT']='string' tabrowValueDict={} tabrowValueDict['DATA_ID']=normdataid tabrowValueDict['CORRECTOR']=corrector tabrowValueDict['SINCE']=sincerun tabrowValueDict['AMODETAG']=amodetag tabrowValueDict['NOMINALEGEV']=egev tabrowValueDict['COMMENT']=comment for paramname,paramvalue in parameters.items(): try: floatparam=float(paramvalue) tabrowDefDict[paramname.upper()]='float' tabrowValueDict[paramname.upper()]=float(paramvalue) except ValueError: tabrowDefDict[paramname.upper()]='string' tabrowValueDict[paramname.upper()]=paramvalue db.insertOneRow(nameDealer.luminormv2dataTableName(),tabrowDefDict,tabrowValueDict) except: raise
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 createTables(schema): ''' create new tables if not exist revisions,revisions_id,luminorms,luminorms_entries,luminorms_entries_id,fillscheme ''' try: created=[] db=dbUtil.dbUtil(schema) if not schema.existsTable(nameDealer.fillschemeTableName()): print('creating fillscheme table') fillschemeTab=coral.TableDescription() fillschemeTab.setName( nameDealer.fillschemeTableName() ) fillschemeTab.insertColumn( 'FILLSCHEME_ID','unsigned long long' ) fillschemeTab.insertColumn( 'FILLSCHEMEPATTERN','string',128,False ) fillschemeTab.insertColumn( 'CORRECTIONFACTOR','float' ) fillschemeTab.setPrimaryKey( 'FILLSCHEME_ID' ) db.createTable(fillschemeTab,withIdTable=True) created.append( nameDealer.fillschemeTableName() ) if not schema.existsTable(nameDealer.revisionTableName()): print('creating revisions table') revisionsTab=coral.TableDescription() revisionsTab.setName( nameDealer.revisionTableName() ) revisionsTab.insertColumn( 'REVISION_ID','unsigned long long') revisionsTab.insertColumn( 'BRANCH_ID','unsigned long long') revisionsTab.insertColumn( 'NAME', 'string',56,False) revisionsTab.insertColumn( 'BRANCH_NAME', 'string',56,False) revisionsTab.insertColumn( 'COMMENT', 'string',1024,False) revisionsTab.insertColumn( 'CTIME', 'time stamp',6) revisionsTab.setPrimaryKey( 'REVISION_ID' ) revisionsTab.setUniqueConstraint(('NAME')) db.createTable(revisionsTab,withIdTable=True) created.append(nameDealer.revisionTableName()) if not schema.existsTable(nameDealer.luminormTableName()): print('creating luminorms table') luminormsTab=coral.TableDescription() luminormsTab.setName( nameDealer.luminormTableName() ) luminormsTab.insertColumn( 'DATA_ID','unsigned long long') luminormsTab.insertColumn( 'ENTRY_ID','unsigned long long') luminormsTab.insertColumn( 'ENTRY_NAME','string',56,False) luminormsTab.insertColumn( 'AMODETAG', 'string',28,False) luminormsTab.insertColumn( 'NORM_1', 'float') luminormsTab.insertColumn( 'EGEV_1', 'unsigned int') luminormsTab.insertColumn( 'NORM_2', 'float') luminormsTab.insertColumn( 'EGEV_2', 'unsigned int') luminormsTab.setPrimaryKey( 'DATA_ID' ) db.createTable(luminormsTab,withIdTable=True,withEntryTables=True,withRevMapTable=True) created.append(nameDealer.luminormTableName()) if not schema.existsTable(nameDealer.lumidataTableName()): print('creating lumidata table') lumidataTab=coral.TableDescription() lumidataTab.setName( nameDealer.lumidataTableName() ) lumidataTab.insertColumn( 'DATA_ID','unsigned long long') lumidataTab.insertColumn( 'ENTRY_ID','unsigned long long') lumidataTab.insertColumn( 'ENTRY_NAME','string',56,False) lumidataTab.insertColumn( 'SOURCE', 'string',128,False) lumidataTab.insertColumn( 'RUNNUM', 'unsigned int') lumidataTab.insertColumn( 'NOMINALEGEV', 'float') lumidataTab.setPrimaryKey( 'DATA_ID' ) db.createTable(lumidataTab,withIdTable=True,withEntryTables=True,withRevMapTable=True) created.append(nameDealer.lumidataTableName()) if not schema.existsTable(nameDealer.lumisummaryv2TableName() ): print('creating lumisummaryv2 table') summary=coral.TableDescription() summary.setName( nameDealer.lumisummaryv2TableName() ) summary.insertColumn('DATA_ID','unsigned long long') summary.insertColumn('RUNNUM','unsigned int') summary.insertColumn('LUMILSNUM','unsigned int') summary.insertColumn('CMSLSNUM','unsigned int') 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('CMSBXINDEXBLOB','blob') summary.insertColumn('BEAMINTENSITYBLOB_1','blob') summary.insertColumn('BEAMINTENSITYBLOB_2','blob') summary.insertColumn('BXLUMIVALUE_OCC1','blob') summary.insertColumn('BXLUMIVALUE_OCC2','blob') summary.insertColumn('BXLUMIVALUE_ET','blob') summary.insertColumn('BXLUMIERROR_OCC1','blob') summary.insertColumn('BXLUMIERROR_OCC2','blob') summary.insertColumn('BXLUMIERROR_ET','blob') summary.insertColumn('BXLUMIQUALITY_OCC1','blob') summary.insertColumn('BXLUMIQUALITY_OCC2','blob') summary.insertColumn('BXLUMIQUALITY_ET','blob') summary.setPrimaryKey(('DATA_ID','LUMILSNUM')) db.createTable(summary,withIdTable=False) created.append(nameDealer.lumisummaryv2TableName()) # # This table exists in the old schema # if not schema.existsTable(nameDealer.cmsrunsummaryTableName()): print('creating cmsrunsummary table') cmsrunsummary=coral.TableDescription() cmsrunsummary.setName( nameDealer.cmsrunsummaryTableName() ) cmsrunsummary.insertColumn('RUNNUM','unsigned int') cmsrunsummary.insertColumn('HLTKEY','string',128,False) cmsrunsummary.insertColumn('L1KEY','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.insertColumn('EGEV','unsigned int') cmsrunsummary.insertColumn('AMODETAG','string',28,False) cmsrunsummary.insertColumn('FILLSCHEME','string',128,False) cmsrunsummary.insertColumn('NCOLLIDINGBUNCHES','usigned int') cmsrunsummary.setPrimaryKey('RUNNUM') db.createTable(cmsrunsummary,withIdTable=False) created.append(nameDealer.cmsrunsummaryTableName()) # # This table exists in the old schema # if not schema.existsTable(nameDealer.trghltMapTableName()): print('creating trghltmap table') 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()) if not schema.existsTable(nameDealer.trgdataTableName()): print('creating trgdata table') trgdataTab=coral.TableDescription() trgdataTab.setName( nameDealer.trgdataTableName() ) trgdataTab.insertColumn( 'DATA_ID','unsigned long long') trgdataTab.insertColumn( 'ENTRY_ID','unsigned long long') trgdataTab.insertColumn( 'ENTRY_NAME','string',56,False) trgdataTab.insertColumn( 'SOURCE', 'string',128,False) trgdataTab.insertColumn( 'RUNNUM', 'unsigned int') trgdataTab.insertColumn( 'BITZERONAME', 'string',56,False) trgdataTab.insertColumn( 'BITNAMECLOB', 'string',6000) trgdataTab.setPrimaryKey( 'DATA_ID' ) db.createTable(trgdataTab,withIdTable=True,withEntryTables=True,withRevMapTable=True) created.append(nameDealer.trgdataTableName()) if not schema.existsTable(nameDealer.lstrgTableName()): print('creating lstrg table') lstrgTab=coral.TableDescription() lstrgTab.setName( nameDealer.lstrgTableName() ) lstrgTab.insertColumn( 'DATA_ID','unsigned long long') lstrgTab.insertColumn( 'RUNNUM', 'unsigned int') lstrgTab.insertColumn( 'CMSLSNUM', 'unsigned int') lstrgTab.insertColumn( 'DEADTIMECOUNT', 'unsigned long long') lstrgTab.insertColumn( 'BITZEROCOUNT', 'unsigned int') lstrgTab.insertColumn( 'BITZEROPRESCALE', 'unsigned int') lstrgTab.insertColumn( 'DEADFRAC', 'float') lstrgTab.insertColumn( 'PRESCALEBLOB', 'blob') lstrgTab.insertColumn( 'TRGCOUNTBLOB', 'blob') lstrgTab.setPrimaryKey( ('DATA_ID','CMSLSNUM') ) db.createTable(lstrgTab,withIdTable=False) created.append( nameDealer.lstrgTableName() ) if not schema.existsTable(nameDealer.hltdataTableName()): print('creating hltdata table') hltdataTab=coral.TableDescription() hltdataTab.setName( nameDealer.hltdataTableName() ) hltdataTab.insertColumn( 'DATA_ID','unsigned long long') hltdataTab.insertColumn( 'ENTRY_ID','unsigned long long') hltdataTab.insertColumn( 'ENTRY_NAME','string',56,False) hltdataTab.insertColumn( 'RUNNUM', 'unsigned int') hltdataTab.insertColumn( 'SOURCE', 'string',128,False) hltdataTab.insertColumn( 'NPATH', 'unsigned int') hltdataTab.insertColumn( 'PATHNAMECLOB', 'string',6000) hltdataTab.setPrimaryKey( 'DATA_ID' ) db.createTable(hltdataTab,withIdTable=True,withEntryTables=True,withRevMapTable=True) created.append(nameDealer.hltTableName()) if not schema.existsTable(nameDealer.lshltTableName()): print('creating lshlt table') lshltTab=coral.TableDescription() lshltTab.setName( nameDealer.lshltTableName() ) lshltTab.insertColumn( 'DATA_ID','unsigned long long') lshltTab.insertColumn( 'RUNNUM', 'unsigned int') lshltTab.insertColumn( 'CMSLSNUM', 'unsigned int') lshltTab.insertColumn( 'PRESCALEBLOB', 'blob') lshltTab.insertColumn( 'HLTCOUNTBLOB', 'blob') lshltTab.insertColumn( 'HLTACCEPTBLOB', 'blob') db.createTable(lshltTab,withIdTable=False) lshltTab.setPrimaryKey( ('DATA_ID','CMSLSNUM') ) created.append(nameDealer.lshltTableName()) if not schema.existsTable(nameDealer.lumivalidationTableName()): print('creating lumivalidation table') 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()) return created except : raise
def insertLumischemaV2(dbsession,runnum,datasource,perlsrawdata,perbunchrawdata,deliveredonly=False): ''' input: lumirundata[datasource] perlsrawdata: {cmslsnum:instlumi} perbunchrawdata: {bxidx:lumifraction} lumilsdata {lumilsnum:[cmslsnum,instlumi,instlumierror,instlumiquality,beamstatus,beamenergy,numorbit,startorbit,cmsbxindexblob,beam1intensityblob,beam2intensityblob,bxlumivalue_occ1,bxlumierror_occ1,bxlumiquality_occ1,bxlumivalue_occ2,bxlumierror_occ2,bxlumiquality_occ2,bxlumivalue_et,bxlumierror_et,bxlumiquality_et]} ''' branchrevision_id=3#databranch_id branchinfo=(branchrevision_id,'DATA') lumirundata=[datasource] lumilsdata={} dbsession.transaction().start(True) oldlumidataid=dataDML.guessLumiDataIdByRun(dbsession.nominalSchema(),runnum) dbsession.transaction().commit() for cmslsnum,instlumi in perlsrawdata.items(): mystartorbit=startorbit+numorbit*(cmslsnum-1) bxdataocc1blob=None bxdataocc2blob=None bxdataetblob=None bxerrorocc1blob=None bxerrorocc2blob=None bxerroretblob=None bxqualityocc1blob=None bxqualityocc2blob=None bxqualityetblob=None cmsbxindexblob=None beam1intensityblob=None beam2intensityblob=None if perbunchrawdata: bxdataArray=array.array('f') bxerrorArray=array.array('f') bxqualityArray=array.array('h') cmsbxindexArray=array.array('h') beam1intensityArray=array.array('f') beam2intensityArray=array.array('f') for bxidx in range(1,3565): lumifraction=0.0 if perbunchrawdata.has_key(bxidx): lumifraction=perbunchrawdata[bxidx] bxlumivalue=float(instlumi*lumifraction)/float(bunchnorm) bxdataArray.append(bxlumivalue) beam1intensityArray.append(9124580336.0) beam1intensityArray.append(8932813306.0) cmsbxindexArray.append(bxidx) bxqualityArray.append(1) bxerrorArray.append(0.0) bxdataocc1blob=CommonUtil.packArraytoBlob(bxdataArray) bxdataocc2blob=CommonUtil.packArraytoBlob(bxdataArray) bxdataetblob=CommonUtil.packArraytoBlob(bxdataArray) bxerrorocc1blob=CommonUtil.packArraytoBlob(bxerrorArray) bxerrorocc2blob=CommonUtil.packArraytoBlob(bxerrorArray) bxerroretblob=CommonUtil.packArraytoBlob(bxerrorArray) bxqualityocc1blob=CommonUtil.packArraytoBlob(bxqualityArray) bxqualityocc2blob=CommonUtil.packArraytoBlob(bxqualityArray) bxqualityetblob=CommonUtil.packArraytoBlob(bxqualityArray) cmsbxindexblob=CommonUtil.packArraytoBlob(cmsbxindexArray) beam1intensityblob=CommonUtil.packArraytoBlob(beam1intensityArray) beam2intensityblob=CommonUtil.packArraytoBlob(beam2intensityArray) if deliveredonly: perlsdata=[0,float(instlumi)/float(6370),0.0,1,'STABLE BEAMS',beamenergy,numorbit,mystartorbit,cmsbxindexblob,beam1intensityblob,beam2intensityblob,bxdataocc1blob,bxerrorocc1blob,bxqualityocc1blob,bxdataocc2blob,bxerrorocc2blob,bxqualityocc2blob,bxdataetblob,bxerroretblob,bxqualityetblob] else: perlsdata=[cmslsnum,float(instlumi)/float(6370),0.0,1,'STABLE BEAMS',beamenergy,numorbit,mystartorbit,cmsbxindexblob,beam1intensityblob,beam2intensityblob,bxdataocc1blob,bxerrorocc1blob,bxqualityocc1blob,bxdataocc2blob,bxerrorocc2blob,bxqualityocc2blob,bxdataetblob,bxerroretblob,bxqualityetblob] lumilsdata[cmslsnum]=perlsdata print 'toinsert from scratch',lumilsdata dbsession.transaction().start(False) (revision_id,entry_id,data_id)=dataDML.addLumiRunDataToBranch(dbsession.nominalSchema(),runnum,lumirundata,branchinfo) newlumilsnumMin=min(lumilsdata.keys()) newlumilsnumMax=max(lumilsdata.keys()) #update id of existing to new #update lumisummaryv2 set DATA_ID=:data_id where DATA_ID=:oldid and lumilsnum<newlumilsnumMin or lumilsnum>newlumilsnumMax #lumidataid is not None update lumilsdata set lumidataid=:newlumidataid where runnum=:run a inputData=coral.AttributeList() inputData.extend('dataid','unsigned long long') inputData.extend('oldid','unsigned long long') inputData.extend('lumilsnumMin','unsigned int') inputData.extend('lumilsnumMax','unsigned int') inputData['dataid'].setData( data_id ) inputData['oldid'].setData( oldlumidataid ) inputData['lumilsnumMin'].setData( newlumilsnumMin ) inputData['lumilsnumMax'].setData( newlumilsnumMax ) db=dbUtil.dbUtil(dbsession.nominalSchema()) db.singleUpdate('LUMISUMMARYV2','DATA_ID=:dataid','DATA_ID=:oldid AND LUMILSNUM<:lumilsnumMin OR lumilsnum>:lumilsnumMax',inputData) print 'to update existing id ',oldlumidataid,' outside region ',newlumilsnumMin,' , ',newlumilsnumMax dataDML.bulkInsertLumiLSSummary(dbsession,runnum,data_id,lumilsdata) 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 insertupdateValidationData(dbsession,data): ''' input: data {run:[[ls,status,comment]]} ''' toreplacenocomment=[]#[[('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('FLAG',flag)],[]] toreplacewcomment=[]#[[('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('FLAG',flag),('COMMENT',comment)],[]] toinsert=[] #[[('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('FLAG',flag),('COMMENT',comment)],[]] try: dbsession.transaction().start(True) dbutil=dbUtil.dbUtil(dbsession.nominalSchema()) for run,alllsdata in data.items(): lsselection=[] if len(alllsdata)==0:#cross query lumisummary to get all the cmslsnum for this run,then insert all to default queryHandle=dbsession.nominalSchema().newQuery() lumisummaryData=lumiQueryAPI.lumisummaryByrun(queryHandle,run,'0001') del queryHandle for lumisummary in lumisummaryData: if lumisummary[-1]==1:#cmsalive lsselection.append([lumisummary[0],'UNKNOWN','NA']) else: lsselection=alllsdata if len(lsselection)==0: print 'no LS found for run '+str(run)+' do nothing' continue for lsdata in lsselection: condition='RUNNUM=:runnum AND CMSLSNUM=:cmslsnum' conditionDefDict={} conditionDefDict['runnum']='unsigned int' conditionDefDict['cmslsnum']='unsigned int' conditionDict={} conditionDict['runnum']=run conditionDict['cmslsnum']=lsdata[0] if dbutil.existRow(nameDealer.lumivalidationTableName(),condition,conditionDefDict,conditionDict): if len(lsdata)>2 and lsdata[2]: toreplacewcomment.append([('runnum',run),('cmslsnum',lsdata[0]),('flag',lsdata[1]),('comment',lsdata[2])]) else: toreplacenocomment.append([('runnum',run),('cmslsnum',lsdata[0]),('flag',lsdata[1]),('comment','')]) else: if len(lsdata)>2 and lsdata[2]: toinsert.append([('RUNNUM',run),('CMSLSNUM',lsdata[0]),('FLAG',lsdata[1]),('COMMENT',lsdata[2])]) else: toinsert.append([('RUNNUM',run),('CMSLSNUM',lsdata[0]),('FLAG',lsdata[1]),('COMMENT','N/A')]) dbsession.transaction().commit() #print 'toreplace with comment ',toreplacewcomment #print 'toreplace without comment ',toreplacenocomment #print 'toinsert ',toinsert #perform insert if len(toinsert)!=0: dbsession.transaction().start(False) dbutil=dbUtil.dbUtil(dbsession.nominalSchema()) tabrowDef=[] tabrowDef.append(('RUNNUM','unsigned int')) tabrowDef.append(('CMSLSNUM','unsigned int')) tabrowDef.append(('FLAG','string')) tabrowDef.append(('COMMENT','string')) dbutil.bulkInsert(nameDealer.lumivalidationTableName(),tabrowDef,toinsert) dbsession.transaction().commit() #perform update with comment if len(toreplacewcomment)!=0: dbsession.transaction().start(False) dbutil=dbUtil.dbUtil(dbsession.nominalSchema()) updateAction='FLAG=:flag,COMMENT=:comment' updateCondition='RUNNUM=:runnum and CMSLSNUM=:cmslsnum' bindvarDef=[] bindvarDef.append(('flag','string')) bindvarDef.append(('comment','string')) bindvarDef.append(('runnum','unsigned int')) bindvarDef.append(('cmslsnum','unsigned int')) dbutil.updateRows(nameDealer.lumivalidationTableName(),updateAction,updateCondition,bindvarDef,toreplacewcomment) dbsession.transaction().commit() #perform update with NO comment if len(toreplacenocomment)!=0: dbsession.transaction().start(False) dbutil=dbUtil.dbUtil(dbsession.nominalSchema()) updateAction='FLAG=:flag' updateCondition='RUNNUM=:runnum and CMSLSNUM=:cmslsnum' bindvarDef=[] bindvarDef.append(('flag','string')) bindvarDef.append(('runnum','unsigned int')) bindvarDef.append(('cmslsnum','unsigned int')) dbutil.updateRows(nameDealer.lumivalidationTableName(),updateAction,updateCondition,bindvarDef,toreplacenocomment) dbsession.transaction().commit() except Exception, e: dbsession.transaction().rollback() del dbsession raise Exception, 'lumiValidate.insertupdateValidationData:'+str(e)
def patchV2(dbsession,runnum,inputpathnames,inputdata): ''' inputpathnames: [pathname,] inputdata: {cmslsnum:[presc,presc...]} update the most recent version of lshlt data if some ls exist if none old ls exists insert new hlt records ''' try: dbsession.transaction().start(True) oldhltdataid=dataDML.guessHltDataIdByRun(dbsession.nominalSchema(),runnum) existingrundata=dataDML.hltRunById(dbsession.nominalSchema(),oldhltdataid) dbsession.transaction().commit() if not oldhltdataid:#no data at all dbsession.transaction().start(False) insertV2(dbsession,runnum,inputpathnames,inputdata) dbsession.transaction().commit() return hltnamedict=existingrundata[3]#[(pathidx,hltname),(pathidx,hltname)...] dbsession.transaction().start(True) existinglsdata=dataDML.hltLSById(dbsession.nominalSchema(),oldhltdataid) dbsession.transaction().commit() oldlsdata=existinglsdata[1] existinglslist=oldlsdata.keys() toupdate={}#{cmslsnum:[presc,presc...]} toinsert={}#{cmslsnum:[presc,presc...]} if existinglslist and len(existinglslist)!=0:#there are some existing data for cmslsnum,oldlscontent in oldlsdata.items(): if cmslsnum in inputdata.keys(): # if overlap with new data, update old data with new toupdate[cmslsnum]=inputdata[cmslsnum] for cmslsnum,lshltcontent in inputdata.items(): if cmslsnum in toupdate: continue #it's to update not to insert toinsert[cmslsnum]=inputdata[cmslsnum] # # insert into lshlt(data_id,runnum,cmslsnum,prescaleblob,hltcountblob,hltacceptblob) values() # dbsession.transaction().start(False) tabrowDefDict={'DATA_ID':'unsigned long long','RUNNUM':'unsigned int','CMSLSNUM':'unsigned int','PRESCALEBLOB':'blob','HLTCOUNTBLOB':'blob','HLTACCEPTBLOB':'blob'} for cmslsnum,perlsdata in toinsert.items(): prescaleArray=array.array('I') hltcountArray=array.array('I') hltacceptArray=array.array('I') for (pathidx,hltname) in hltnamedict: thispathIdx=inputpathnames.index(hltname) thispresc=perlsdata[thispathIdx] thiscount=0 thisaccept=0 prescaleArray.append(thispresc) hltcountArray.append(thiscount) hltacceptArray.append(thisaccept) prescaleblob=CommonUtil.packArraytoBlob(prescaleArray) hltcountblob=CommonUtil.packArraytoBlob(hltcountArray) hltacceptblob=CommonUtil.packArraytoBlob(hltacceptArray) tabrowValueDict={'DATA_ID':oldhltdataid,'RUNNUM':int(runnum),'CMSLSNUM':int(cmslsnum),'PRESCALEBLOB':prescaleblob,'HLTCOUNTBLOB':hltcountblob,'HLTACCEPTBLOB':hltacceptblob} db=dbUtil.dbUtil(dbsession.nominalSchema()) db.insertOneRow(nameDealer.lshltTableName(),tabrowDefDict,tabrowValueDict) # # update lshlt set prescaleblob=:prescaleblob,hltcoutblob=:hltcountblob,hltacceptblob=:hltacceptblob where data_id=:olddata_id and cmslsnum=:cmslsnum; # setClause='PRESCALEBLOB=:prescaleblob,HLTCOUNTBLOB=:hltcountblob,HLTACCEPTBLOB=:hltacceptblob' updateCondition='DATA_ID=:oldhltdataid and CMSLSNUM=:cmslsnum' for cmslsnum,perlsdata in toupdate.items(): prescaleArray=array.array('I') hltcountArray=array.array('I') hltacceptArray=array.array('I') for (pathidx,hltname) in hltnamedict: thispathIdx=inputpathnames.index(hltname) thispresc=perlsdata[thispathIdx] thiscount=0 thisaccept=0 prescaleArray.append(thispresc) hltcountArray.append(thiscount) hltacceptArray.append(thisaccept) prescaleblob=CommonUtil.packArraytoBlob(prescaleArray) hltcountblob=CommonUtil.packArraytoBlob(hltcountArray) hltacceptblob=CommonUtil.packArraytoBlob(hltacceptArray) iData=coral.AttributeList() iData.extend('prescaleblob','blob') iData.extend('hltcountblob','blob') iData.extend('hltacceptblob','blob') iData.extend('olddata_id','unsigned int') iData.extend('cmslsnum','unsigned int') iData['prescaleblob'].setData(prescaleblob) iData['hltcountblob'].setData(hltcountblob) iData['hltacceptblob'].setData(hltacceptblob) iData['olddata_id'].setData(int(olddata_id)) iData['cmslsnum'].setData(int(cmslsnum)) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.lshltTableName(),setClause,updateCondition,iData) dbsession.transaction().commit() #dbsession.transaction().rollback() 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 createTables(schema): ''' create new tables if not exist revisions,revisions_id,luminorms,luminorms_entries,luminorms_entries_id,fillscheme ''' try: created = [] db = dbUtil.dbUtil(schema) if not schema.existsTable(nameDealer.fillschemeTableName()): print('creating fillscheme table') fillschemeTab = coral.TableDescription() fillschemeTab.setName(nameDealer.fillschemeTableName()) fillschemeTab.insertColumn('FILLSCHEME_ID', 'unsigned long long') fillschemeTab.insertColumn('FILLSCHEMEPATTERN', 'string', 128, False) fillschemeTab.insertColumn('CORRECTIONFACTOR', 'float') fillschemeTab.setPrimaryKey('FILLSCHEME_ID') db.createTable(fillschemeTab, withIdTable=True) created.append(nameDealer.fillschemeTableName()) if not schema.existsTable(nameDealer.revisionTableName()): print('creating revisions table') revisionsTab = coral.TableDescription() revisionsTab.setName(nameDealer.revisionTableName()) revisionsTab.insertColumn('REVISION_ID', 'unsigned long long') revisionsTab.insertColumn('BRANCH_ID', 'unsigned long long') revisionsTab.insertColumn('NAME', 'string', 56, False) revisionsTab.insertColumn('BRANCH_NAME', 'string', 56, False) revisionsTab.insertColumn('COMMENT', 'string', 1024, False) revisionsTab.insertColumn('CTIME', 'time stamp', 6) revisionsTab.setPrimaryKey('REVISION_ID') revisionsTab.setUniqueConstraint(('NAME')) db.createTable(revisionsTab, withIdTable=True) created.append(nameDealer.revisionTableName()) if not schema.existsTable(nameDealer.luminormTableName()): print('creating luminorms table') luminormsTab = coral.TableDescription() luminormsTab.setName(nameDealer.luminormTableName()) luminormsTab.insertColumn('DATA_ID', 'unsigned long long') luminormsTab.insertColumn('ENTRY_ID', 'unsigned long long') luminormsTab.insertColumn('ENTRY_NAME', 'string', 56, False) luminormsTab.insertColumn('AMODETAG', 'string', 28, False) luminormsTab.insertColumn('NORM_1', 'float') luminormsTab.insertColumn('EGEV_1', 'unsigned int') luminormsTab.insertColumn('NORM_2', 'float') luminormsTab.insertColumn('EGEV_2', 'unsigned int') luminormsTab.setPrimaryKey('DATA_ID') db.createTable(luminormsTab, withIdTable=True, withEntryTables=True, withRevMapTable=True) created.append(nameDealer.luminormTableName()) if not schema.existsTable(nameDealer.lumidataTableName()): print('creating lumidata table') lumidataTab = coral.TableDescription() lumidataTab.setName(nameDealer.lumidataTableName()) lumidataTab.insertColumn('DATA_ID', 'unsigned long long') lumidataTab.insertColumn('ENTRY_ID', 'unsigned long long') lumidataTab.insertColumn('ENTRY_NAME', 'string', 56, False) lumidataTab.insertColumn('SOURCE', 'string', 128, False) lumidataTab.insertColumn('RUNNUM', 'unsigned int') lumidataTab.insertColumn('NOMINALEGEV', 'float') lumidataTab.setPrimaryKey('DATA_ID') db.createTable(lumidataTab, withIdTable=True, withEntryTables=True, withRevMapTable=True) created.append(nameDealer.lumidataTableName()) if not schema.existsTable(nameDealer.lumisummaryv2TableName()): print('creating lumisummaryv2 table') summary = coral.TableDescription() summary.setName(nameDealer.lumisummaryv2TableName()) summary.insertColumn('DATA_ID', 'unsigned long long') summary.insertColumn('RUNNUM', 'unsigned int') summary.insertColumn('LUMILSNUM', 'unsigned int') summary.insertColumn('CMSLSNUM', 'unsigned int') 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('CMSBXINDEXBLOB', 'blob') summary.insertColumn('BEAMINTENSITYBLOB_1', 'blob') summary.insertColumn('BEAMINTENSITYBLOB_2', 'blob') summary.insertColumn('BXLUMIVALUE_OCC1', 'blob') summary.insertColumn('BXLUMIVALUE_OCC2', 'blob') summary.insertColumn('BXLUMIVALUE_ET', 'blob') summary.insertColumn('BXLUMIERROR_OCC1', 'blob') summary.insertColumn('BXLUMIERROR_OCC2', 'blob') summary.insertColumn('BXLUMIERROR_ET', 'blob') summary.insertColumn('BXLUMIQUALITY_OCC1', 'blob') summary.insertColumn('BXLUMIQUALITY_OCC2', 'blob') summary.insertColumn('BXLUMIQUALITY_ET', 'blob') summary.setPrimaryKey(('DATA_ID', 'LUMILSNUM')) db.createTable(summary, withIdTable=False) created.append(nameDealer.lumisummaryv2TableName()) # # This table exists in the old schema # if not schema.existsTable(nameDealer.cmsrunsummaryTableName()): print('creating cmsrunsummary table') cmsrunsummary = coral.TableDescription() cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName()) cmsrunsummary.insertColumn('RUNNUM', 'unsigned int') cmsrunsummary.insertColumn('HLTKEY', 'string', 128, False) cmsrunsummary.insertColumn('L1KEY', '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.insertColumn('EGEV', 'unsigned int') cmsrunsummary.insertColumn('AMODETAG', 'string', 28, False) cmsrunsummary.insertColumn('FILLSCHEME', 'string', 128, False) cmsrunsummary.insertColumn('NCOLLIDINGBUNCHES', 'usigned int') cmsrunsummary.setPrimaryKey('RUNNUM') db.createTable(cmsrunsummary, withIdTable=False) created.append(nameDealer.cmsrunsummaryTableName()) # # This table exists in the old schema # if not schema.existsTable(nameDealer.trghltMapTableName()): print('creating trghltmap table') 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()) if not schema.existsTable(nameDealer.trgdataTableName()): print('creating trgdata table') trgdataTab = coral.TableDescription() trgdataTab.setName(nameDealer.trgdataTableName()) trgdataTab.insertColumn('DATA_ID', 'unsigned long long') trgdataTab.insertColumn('ENTRY_ID', 'unsigned long long') trgdataTab.insertColumn('ENTRY_NAME', 'string', 56, False) trgdataTab.insertColumn('SOURCE', 'string', 128, False) trgdataTab.insertColumn('RUNNUM', 'unsigned int') trgdataTab.insertColumn('BITZERONAME', 'string', 56, False) trgdataTab.insertColumn('BITNAMECLOB', 'string', 6000) trgdataTab.setPrimaryKey('DATA_ID') db.createTable(trgdataTab, withIdTable=True, withEntryTables=True, withRevMapTable=True) created.append(nameDealer.trgdataTableName()) if not schema.existsTable(nameDealer.lstrgTableName()): print('creating lstrg table') lstrgTab = coral.TableDescription() lstrgTab.setName(nameDealer.lstrgTableName()) lstrgTab.insertColumn('DATA_ID', 'unsigned long long') lstrgTab.insertColumn('RUNNUM', 'unsigned int') lstrgTab.insertColumn('CMSLSNUM', 'unsigned int') lstrgTab.insertColumn('DEADTIMECOUNT', 'unsigned long long') lstrgTab.insertColumn('BITZEROCOUNT', 'unsigned int') lstrgTab.insertColumn('BITZEROPRESCALE', 'unsigned int') lstrgTab.insertColumn('DEADFRAC', 'float') lstrgTab.insertColumn('PRESCALEBLOB', 'blob') lstrgTab.insertColumn('TRGCOUNTBLOB', 'blob') lstrgTab.setPrimaryKey(('DATA_ID', 'CMSLSNUM')) db.createTable(lstrgTab, withIdTable=False) created.append(nameDealer.lstrgTableName()) if not schema.existsTable(nameDealer.hltdataTableName()): print('creating hltdata table') hltdataTab = coral.TableDescription() hltdataTab.setName(nameDealer.hltdataTableName()) hltdataTab.insertColumn('DATA_ID', 'unsigned long long') hltdataTab.insertColumn('ENTRY_ID', 'unsigned long long') hltdataTab.insertColumn('ENTRY_NAME', 'string', 56, False) hltdataTab.insertColumn('RUNNUM', 'unsigned int') hltdataTab.insertColumn('SOURCE', 'string', 128, False) hltdataTab.insertColumn('NPATH', 'unsigned int') hltdataTab.insertColumn('PATHNAMECLOB', 'string', 6000) hltdataTab.setPrimaryKey('DATA_ID') db.createTable(hltdataTab, withIdTable=True, withEntryTables=True, withRevMapTable=True) created.append(nameDealer.hltTableName()) if not schema.existsTable(nameDealer.lshltTableName()): print('creating lshlt table') lshltTab = coral.TableDescription() lshltTab.setName(nameDealer.lshltTableName()) lshltTab.insertColumn('DATA_ID', 'unsigned long long') lshltTab.insertColumn('RUNNUM', 'unsigned int') lshltTab.insertColumn('CMSLSNUM', 'unsigned int') lshltTab.insertColumn('PRESCALEBLOB', 'blob') lshltTab.insertColumn('HLTCOUNTBLOB', 'blob') lshltTab.insertColumn('HLTACCEPTBLOB', 'blob') db.createTable(lshltTab, withIdTable=False) lshltTab.setPrimaryKey(('DATA_ID', 'CMSLSNUM')) created.append(nameDealer.lshltTableName()) if not schema.existsTable(nameDealer.lumivalidationTableName()): print('creating lumivalidation table') 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()) 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 patchV2(dbsession,runnum,inputpathnames,inputdata): ''' inputpathnames: [pathname,] inputdata: {cmslsnum:[presc,presc...]} update the most recent version of lshlt data if some ls exist if none old ls exists insert new hlt records ''' try: dbsession.transaction().start(True) oldhltdataid=dataDML.guessHltDataIdByRun(dbsession.nominalSchema(),runnum) existingrundata=dataDML.hltRunById(dbsession.nominalSchema(),oldhltdataid) dbsession.transaction().commit() if not oldhltdataid:#no data at all dbsession.transaction().start(False) insertV2(dbsession,runnum,inputpathnames,inputdata) dbsession.transaction().commit() return hltnamedict=existingrundata[3]#[(pathidx,hltname),(pathidx,hltname)...] dbsession.transaction().start(True) existinglsdata=dataDML.hltLSById(dbsession.nominalSchema(),oldhltdataid) dbsession.transaction().commit() oldlsdata=existinglsdata[1] existinglslist=oldlsdata.keys() toupdate={}#{cmslsnum:[presc,presc...]} toinsert={}#{cmslsnum:[presc,presc...]} if existinglslist and len(existinglslist)!=0:#there are some existing data for cmslsnum,oldlscontent in oldlsdata.items(): if cmslsnum in inputdata.keys(): # if overlap with new data, update old data with new toupdate[cmslsnum]=inputdata[cmslsnum] for cmslsnum,lshltcontent in inputdata.items(): if cmslsnum in toupdate: continue #it's to update not to insert toinsert[cmslsnum]=inputdata[cmslsnum] # # insert into lshlt(data_id,runnum,cmslsnum,prescaleblob,hltcountblob,hltacceptblob) values() # dbsession.transaction().start(False) tabrowDefDict={'DATA_ID':'unsigned long long','RUNNUM':'unsigned int','CMSLSNUM':'unsigned int','PRESCALEBLOB':'blob','HLTCOUNTBLOB':'blob','HLTACCEPTBLOB':'blob'} for cmslsnum,perlsdata in toinsert.items(): prescaleArray=array.array('I') hltcountArray=array.array('I') hltacceptArray=array.array('I') for (pathidx,hltname) in hltnamedict: thispathIdx=inputpathnames.index(hltname) thispresc=perlsdata[thispathIdx] thiscount=0 thisaccept=0 prescaleArray.append(thispresc) hltcountArray.append(thiscount) hltacceptArray.append(thisaccept) prescaleblob=CommonUtil.packArraytoBlob(prescaleArray) hltcountblob=CommonUtil.packArraytoBlob(hltcountArray) hltacceptblob=CommonUtil.packArraytoBlob(hltacceptArray) tabrowValueDict={'DATA_ID':oldhltdataid,'RUNNUM':int(runnum),'CMSLSNUM':int(cmslsnum),'PRESCALEBLOB':prescaleblob,'HLTCOUNTBLOB':hltcountblob,'HLTACCEPTBLOB':hltacceptblob} db=dbUtil.dbUtil(dbsession.nominalSchema()) db.insertOneRow(nameDealer.lshltTableName(),tabrowDefDict,tabrowValueDict) # # update lshlt set prescaleblob=:prescaleblob,hltcoutblob=:hltcountblob,hltacceptblob=:hltacceptblob where data_id=:olddata_id and cmslsnum=:cmslsnum; # setClause='PRESCALEBLOB=:prescaleblob,HLTCOUNTBLOB=:hltcountblob,HLTACCEPTBLOB=:hltacceptblob' updateCondition='DATA_ID=:oldhltdataid and CMSLSNUM=:cmslsnum' for cmslsnum,perlsdata in toupdate.items(): prescaleArray=array.array('I') hltcountArray=array.array('I') hltacceptArray=array.array('I') for (pathidx,hltname) in hltnamedict: thispathIdx=inputpathnames.index(hltname) thispresc=perlsdata[thispathIdx] thiscount=0 thisaccept=0 prescaleArray.append(thispresc) hltcountArray.append(thiscount) hltacceptArray.append(thisaccept) prescaleblob=CommonUtil.packArraytoBlob(prescaleArray) hltcountblob=CommonUtil.packArraytoBlob(hltcountArray) hltacceptblob=CommonUtil.packArraytoBlob(hltacceptArray) iData=coral.AttributeList() iData.extend('prescaleblob','blob') iData.extend('hltcountblob','blob') iData.extend('hltacceptblob','blob') iData.extend('olddata_id','unsigned int') iData.extend('cmslsnum','unsigned int') iData['prescaleblob'].setData(prescaleblob) iData['hltcountblob'].setData(hltcountblob) iData['hltacceptblob'].setData(hltacceptblob) iData['olddata_id'].setData(int(olddata_id)) iData['cmslsnum'].setData(int(cmslsnum)) db=dbUtil.dbUtil(schema) db.singleUpdate(nameDealer.lshltTableName(),setClause,updateCondition,iData) dbsession.transaction().commit() #dbsession.transaction().rollback() 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()