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()
