def oldToNew(schema):
'''
modify old tables:cmsrunsummary
alter table cmsrunsummary add column(l1key string,egev unsigned int,amodetag string)
'''
try:
tableHandle=schema.tableHandle(nameDealer.cmsrunsummaryTableName())
tableHandle.schemaEditor().insertColumn('L1KEY','string',128,False)
tableHandle.schemaEditor().insertColumn('EGEV','unsigned int')
tableHandle.schemaEditor().insertColumn('AMODETAG','string',28,False)
createTables(schema)
except:
raise
def newToOld(schema):
try:
dropTables(schema,['REVISIONS','LUMINORMS','LUMIDATA','LUMISUMMARYV2','TRGDATA','LSTRG','HLTDATA','LSHLT'])
tableHandle=schema.tableHandle(nameDealer.cmsrunsummaryTableName())
ncol=tableHandle.description().numberOfColumns()
todrop=[]
for i in range(ncol):
colname=tableHandle.description().columnDescription(i).name()
if colname in ['L1KEY','EGEV','AMODETAG']:
todrop.append(colname)
for colname in todrop:
tableHandle.schemaEditor().dropColumn(colname)
except :
raise
def oldToNew(schema):
'''
modify old tables:cmsrunsummary
alter table cmsrunsummary add column(l1key string,egev unsigned int,amodetag string)
'''
try:
tableHandle = schema.tableHandle(nameDealer.cmsrunsummaryTableName())
tableHandle.schemaEditor().insertColumn('L1KEY', 'string', 128, False)
tableHandle.schemaEditor().insertColumn('EGEV', 'unsigned int')
tableHandle.schemaEditor().insertColumn('AMODETAG', 'string', 28,
False)
createTables(schema)
except:
raise
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 newToOld(schema):
try:
dropTables(schema, [
'REVISIONS', 'LUMINORMS', 'LUMIDATA', 'LUMISUMMARYV2', 'TRGDATA',
'LSTRG', 'HLTDATA', 'LSHLT'
])
tableHandle = schema.tableHandle(nameDealer.cmsrunsummaryTableName())
ncol = tableHandle.description().numberOfColumns()
todrop = []
for i in range(ncol):
colname = tableHandle.description().columnDescription(i).name()
if colname in ['L1KEY', 'EGEV', 'AMODETAG']:
todrop.append(colname)
for colname in todrop:
tableHandle.schemaEditor().dropColumn(colname)
except:
raise
def correctionsForRangeV2(schema,inputRange,correctionTerm):
'''
decide on the corrections to apply in the input range depending on amodetag,egev and runrange
select fillschemepattern,correctionfactor from fillscheme;
[(fillschemepattern,afterglow),...]
select fillnum,runnum,fillscheme,ncollidingbunches,egev from cmsrunsummary where amodetag='PROTPYHS' and egev>3000
{runnum: (fillnum,fillscheme,ncollidingbunches),...}
input: correctionTerm correction terms used in the formula
output:
{runnum:(constantfactor,afterglowfactor,ncollidingbx,nonlinearfactor1,nonlinearfactor2)}
'''
runs=[]
result={}
constfactor=1.0 #default
afterglow=1.0 #default
ncollidingbunches=0 #default
nonlinear_1=1.0 #default
nonlinear_2=1.0 #default
if isinstance(inputRange,str):
runs.append(int(inputRange))
else:
runs=inputRange
for r in runs:
if r<150008 :
result[r]=(constfactor,afterglow,ncollidingbunches,nonlinear_1, nonlinear_2)
afterglows=[]
s=nameDealer.fillschemeTableName()
r=nameDealer.cmsrunsummaryTableName()
qHandle=schema.newQuery()
try:
qHandle.addToTableList(s)
qResult=coral.AttributeList()
qResult.extend('FILLSCHEMEPATTERN','string')
qResult.extend('CORRECTIONFACTOR','float')
qHandle.defineOutput(qResult)
qHandle.addToOutputList('FILLSCHEMEPATTERN')
qHandle.addToOutputList('CORRECTIONFACTOR')
cursor=qHandle.execute()
while next(cursor):
fillschemePattern=cursor.currentRow()['FILLSCHEMEPATTERN'].data()
afterglowfac=cursor.currentRow()['CORRECTIONFACTOR'].data()
afterglows.append((fillschemePattern,afterglowfac))
except :
del qHandle
raise
del qHandle
qHandle=schema.newQuery()
try:
qHandle.addToTableList(r)
qHandle.addToOutputList('FILLNUM', 'fillnum')
qHandle.addToOutputList('RUNNUM', 'runnum')
qHandle.addToOutputList('FILLSCHEME','fillscheme')
qHandle.addToOutputList('NCOLLIDINGBUNCHES','ncollidingbunches')
qResult=coral.AttributeList()
qResult.extend('fillnum','unsigned int')
qResult.extend('runnum','unsigned int')
qResult.extend('fillscheme','string')
qResult.extend('ncollidingbunches','unsigned int')
qConditionStr='AMODETAG=:amodetag AND EGEV>=:egev'#filter out lowenergy and non-proton runs
qCondition=coral.AttributeList()
qCondition.extend('amodetag','string')
qCondition.extend('egev','unsigned int')
qCondition['amodetag'].setData('PROTPHYS')
qCondition['egev'].setData(3000)
qHandle.defineOutput(qResult)
qHandle.setCondition(qConditionStr,qCondition)
cursor=qHandle.execute()
while next(cursor):
runnum=cursor.currentRow()['runnum'].data()
#print 'runnum ',runnum
if runnum not in runs or runnum in result:
continue
fillnum=cursor.currentRow()['fillnum'].data()
afterglow=1.0
constfactor=correctionTerm.constfactor
nonlinear_1=correctionTerm.t1
nonlinear_2=correctionTerm.t2
ncollidingbunches=0
if cursor.currentRow()['ncollidingbunches']:
ncollidingbunches=cursor.currentRow()['ncollidingbunches'].data()
fillscheme=''
if cursor.currentRow()['fillscheme']:
fillscheme=cursor.currentRow()['fillscheme'].data()
if fillscheme and len(fillscheme)!=0:
if fillnum>=2124: #afterglow'salready applied by lumidaq in hf root for fill<2124
afterglow=afterglowByFillscheme(fillscheme,afterglows)
result[runnum]=(constfactor,afterglow,ncollidingbunches,nonlinear_1,nonlinear_2)
except :
del qHandle
raise
del qHandle
for run in runs:
if run not in result.keys():
result[run]=(constfactor,afterglow,ncollidingbunches,nonlinear_1,nonlinear_2)
return result
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 correctionsForRangeV2(schema, inputRange, correctionTerm):
'''
decide on the corrections to apply in the input range depending on amodetag,egev and runrange
select fillschemepattern,correctionfactor from fillscheme;
[(fillschemepattern,afterglow),...]
select fillnum,runnum,fillscheme,ncollidingbunches,egev from cmsrunsummary where amodetag='PROTPYHS' and egev>3000
{runnum: (fillnum,fillscheme,ncollidingbunches),...}
input: correctionTerm correction terms used in the formula
output:
{runnum:(constantfactor,afterglowfactor,ncollidingbx,nonlinearfactor1,nonlinearfactor2)}
'''
runs = []
result = {}
constfactor = 1.0 #default
afterglow = 1.0 #default
ncollidingbunches = 0 #default
nonlinear_1 = 1.0 #default
nonlinear_2 = 1.0 #default
if isinstance(inputRange, str):
runs.append(int(inputRange))
else:
runs = inputRange
for r in runs:
if r < 150008:
result[r] = (constfactor, afterglow, ncollidingbunches,
nonlinear_1, nonlinear_2)
afterglows = []
s = nameDealer.fillschemeTableName()
r = nameDealer.cmsrunsummaryTableName()
qHandle = schema.newQuery()
try:
qHandle.addToTableList(s)
qResult = coral.AttributeList()
qResult.extend('FILLSCHEMEPATTERN', 'string')
qResult.extend('CORRECTIONFACTOR', 'float')
qHandle.defineOutput(qResult)
qHandle.addToOutputList('FILLSCHEMEPATTERN')
qHandle.addToOutputList('CORRECTIONFACTOR')
cursor = qHandle.execute()
while next(cursor):
fillschemePattern = cursor.currentRow()['FILLSCHEMEPATTERN'].data()
afterglowfac = cursor.currentRow()['CORRECTIONFACTOR'].data()
afterglows.append((fillschemePattern, afterglowfac))
except:
del qHandle
raise
del qHandle
qHandle = schema.newQuery()
try:
qHandle.addToTableList(r)
qHandle.addToOutputList('FILLNUM', 'fillnum')
qHandle.addToOutputList('RUNNUM', 'runnum')
qHandle.addToOutputList('FILLSCHEME', 'fillscheme')
qHandle.addToOutputList('NCOLLIDINGBUNCHES', 'ncollidingbunches')
qResult = coral.AttributeList()
qResult.extend('fillnum', 'unsigned int')
qResult.extend('runnum', 'unsigned int')
qResult.extend('fillscheme', 'string')
qResult.extend('ncollidingbunches', 'unsigned int')
qConditionStr = 'AMODETAG=:amodetag AND EGEV>=:egev' #filter out lowenergy and non-proton runs
qCondition = coral.AttributeList()
qCondition.extend('amodetag', 'string')
qCondition.extend('egev', 'unsigned int')
qCondition['amodetag'].setData('PROTPHYS')
qCondition['egev'].setData(3000)
qHandle.defineOutput(qResult)
qHandle.setCondition(qConditionStr, qCondition)
cursor = qHandle.execute()
while next(cursor):
runnum = cursor.currentRow()['runnum'].data()
#print 'runnum ',runnum
if runnum not in runs or runnum in result:
continue
fillnum = cursor.currentRow()['fillnum'].data()
afterglow = 1.0
constfactor = correctionTerm.constfactor
nonlinear_1 = correctionTerm.t1
nonlinear_2 = correctionTerm.t2
ncollidingbunches = 0
if cursor.currentRow()['ncollidingbunches']:
ncollidingbunches = cursor.currentRow(
)['ncollidingbunches'].data()
fillscheme = ''
if cursor.currentRow()['fillscheme']:
fillscheme = cursor.currentRow()['fillscheme'].data()
if fillscheme and len(fillscheme) != 0:
if fillnum >= 2124: #afterglow'salready applied by lumidaq in hf root for fill<2124
afterglow = afterglowByFillscheme(fillscheme, afterglows)
result[runnum] = (constfactor, afterglow, ncollidingbunches,
nonlinear_1, nonlinear_2)
except:
del qHandle
raise
del qHandle
for run in runs:
if run not in result.keys():
result[run] = (constfactor, afterglow, ncollidingbunches,
nonlinear_1, nonlinear_2)
return result
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 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 correctionsForRange(schema,inputRange):
'''
select fillschemepattern,correctionfactor from fillscheme;
[(fillschemepattern,afterglow),...]
select fillnum,runnum,fillscheme,ncollidingbunches,egev from cmsrunsummary where amodetag='PROTPYHS' and egev>3000
{runnum: (fillnum,fillscheme,ncollidingbunches),...}
output:
{runnum:(constantfactor,afterglowfactor,nonlinearfactor)}
'''
runs=[]
result={}
if isinstance(inputRange,str):
runs.append(int(inputRange))
else:
runs=inputRange
for r in runs:
if r<160442 :
result[r]=(1.0,1.0,0.0)
afterglows=[]
s=nameDealer.fillschemeTableName()
r=nameDealer.cmsrunsummaryTableName()
qHandle=schema.newQuery()
try:
qHandle.addToTableList(s)
qResult=coral.AttributeList()
qResult.extend('FILLSCHEMEPATTERN','string')
qResult.extend('CORRECTIONFACTOR','float')
qHandle.defineOutput(qResult)
qHandle.addToOutputList('FILLSCHEMEPATTERN')
qHandle.addToOutputList('CORRECTIONFACTOR')
cursor=qHandle.execute()
while cursor.next():
fillschemePattern=cursor.currentRow()['FILLSCHEMEPATTERN'].data()
afterglowfac=cursor.currentRow()['CORRECTIONFACTOR'].data()
afterglows.append((fillschemePattern,afterglowfac))
except :
del qHandle
raise
del qHandle
qHandle=schema.newQuery()
try:
qHandle.addToTableList(r)
qHandle.addToOutputList('FILLNUM', 'fillnum')
qHandle.addToOutputList('RUNNUM', 'runnum')
qHandle.addToOutputList('FILLSCHEME','fillscheme')
qHandle.addToOutputList('NCOLLIDINGBUNCHES','ncollidingbunches')
qResult=coral.AttributeList()
qResult.extend('fillnum','unsigned int')
qResult.extend('runnum','unsigned int')
qResult.extend('fillscheme','string')
qResult.extend('ncollidingbunches','unsigned int')
qConditionStr='AMODETAG=:amodetag AND EGEV>=:egev'
qCondition=coral.AttributeList()
qCondition.extend('amodetag','string')
qCondition.extend('egev','unsigned int')
qCondition['amodetag'].setData('PROTPHYS')
qCondition['egev'].setData(3000)
qHandle.defineOutput(qResult)
qHandle.setCondition(qConditionStr,qCondition)
cursor=qHandle.execute()
while cursor.next():
runnum=cursor.currentRow()['runnum'].data()
#print 'runnum ',runnum
if runnum not in runs or result.has_key(runnum):
continue
fillnum=cursor.currentRow()['fillnum'].data()
constfactor=1.141
afterglow=1.0
nonlinear=0.076
nonlinearPerBX=0.0
ncollidingbunches=0
if cursor.currentRow()['ncollidingbunches']:
ncollidingbunches=cursor.currentRow()['ncollidingbunches'].data()
fillscheme=''
if cursor.currentRow()['fillscheme']:
fillscheme=cursor.currentRow()['fillscheme'].data()
if fillscheme and len(fillscheme)!=0:
afterglow=afterglowByFillscheme(fillscheme,afterglows)
if ncollidingbunches and ncollidingbunches!=0:
nonlinearPerBX=float(1)/float(ncollidingbunches)
nonlinear=nonlinearPerBX*nonlinear
result[runnum]=(constfactor,afterglow,nonlinear)
except :
del qHandle
raise
del qHandle
for run in runs:
if run not in result.keys():
result[run]=(1.0,1.0,0.0)
return result
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 pixelcorrectionsForRange(schema,inputRange):
'''
select fillschemepattern,correctionfactor from fillscheme;
[(fillschemepattern,afterglow),...]
select fillnum,runnum,fillscheme from cmsrunsummary where amodetag='PROTPHYS'
{runnum: (fillnum,fillscheme),...}
output:
{runnum:(afterglowfactor)}
'''
runs=[]
result={}
if isinstance(inputRange,str):
runs.append(int(inputRange))
else:
runs=inputRange
afterglows=[]
s=nameDealer.fillschemeTableName()
r=nameDealer.cmsrunsummaryTableName()
qHandle=schema.newQuery()
try:
qHandle.addToTableList(s)
qResult=coral.AttributeList()
qResult.extend('FILLSCHEMEPATTERN','string')
qResult.extend('PIXELCORRECTIONFACTOR','float')
qHandle.defineOutput(qResult)
qHandle.addToOutputList('FILLSCHEMEPATTERN')
qHandle.addToOutputList('PIXELCORRECTIONFACTOR')
cursor=qHandle.execute()
while next(cursor):
fillschemePattern=cursor.currentRow()['FILLSCHEMEPATTERN'].data()
afterglowfac=cursor.currentRow()['PIXELCORRECTIONFACTOR'].data()
afterglows.append((fillschemePattern,afterglowfac))
except :
del qHandle
raise
del qHandle
qHandle=schema.newQuery()
try:
qConditionStr='FILLNUM>:minfillnum'
qCondition=coral.AttributeList()
qCondition.extend('minfillnum','unsigned int')
qCondition['minfillnum'].setData(1600)
qHandle.addToTableList(r)
qHandle.addToOutputList('FILLNUM', 'fillnum')
qHandle.addToOutputList('RUNNUM', 'runnum')
qHandle.addToOutputList('FILLSCHEME','fillscheme')
qResult=coral.AttributeList()
qResult.extend('fillnum','unsigned int')
qResult.extend('runnum','unsigned int')
qResult.extend('fillscheme','string')
qHandle.setCondition(qConditionStr,qCondition)
qHandle.defineOutput(qResult)
cursor=qHandle.execute()
while next(cursor):
runnum=cursor.currentRow()['runnum'].data()
if runnum not in runs or runnum in result:
continue
fillnum=cursor.currentRow()['fillnum'].data()
afterglow=1.0
fillscheme=''
if cursor.currentRow()['fillscheme']:
fillscheme=cursor.currentRow()['fillscheme'].data()
if fillscheme and len(fillscheme)!=0:
afterglow=afterglowByFillscheme(fillscheme,afterglows)
result[runnum]=afterglow
except :
del qHandle
raise
del qHandle
for run in runs:
if run not in result.keys():
result[run]=1.0 #those have no fillscheme
return result
def recordedLumiForRun(self,runnum,lslist=[-1]):
'''
lslist=[-1] means to take all in the db
output: ['runnumber','trgtable{}','deadtable{}']
'''
dbsession = self._session
c = self._c
recorded=0.0
lumidata=[] #[runnumber,trgtable,deadtable]
trgtable={} #{hltpath:[l1seed,hltprescale,l1prescale]}
deadtable={} #{lsnum:[deadtime,instlumi,bit_0,norbits]}
lumidata.append(runnum)
lumidata.append(trgtable)
lumidata.append(deadtable)
collectedseeds=[] #[(hltpath,l1seed)]
try:
dbsession.transaction().start(True)
schema=dbsession.nominalSchema()
query=schema.newQuery()
query.addToTableList(nameDealer.cmsrunsummaryTableName(),'cmsrunsummary')
query.addToTableList(nameDealer.trghltMapTableName(),'trghltmap')#small table first
queryCondition=coral.AttributeList()
queryCondition.extend("runnumber","unsigned int")
queryCondition["runnumber"].setData(int(runnum))
query.setCondition("trghltmap.HLTKEY=cmsrunsummary.HLTKEY AND cmsrunsummary.RUNNUM=:runnumber",queryCondition)
query.addToOutputList("trghltmap.HLTPATHNAME","hltpathname")
query.addToOutputList("trghltmap.L1SEED","l1seed")
result=coral.AttributeList()
result.extend("hltpathname","string")
result.extend("l1seed","string")
query.defineOutput(result)
cursor=query.execute()
while cursor.next():
hltpathname=cursor.currentRow()["hltpathname"].data()
l1seed=cursor.currentRow()["l1seed"].data()
collectedseeds.append((hltpathname,l1seed))
#print 'collectedseeds ',collectedseeds
del query
dbsession.transaction().commit()
#loop over hltpath
for (hname,sname) in collectedseeds:
l1bitname=hltTrgSeedMapper.findUniqueSeed(hname,sname)
#print 'found unque seed ',hname,l1bitname
if l1bitname:
lumidata[1][hname]=[]
lumidata[1][hname].append(l1bitname.replace('\"',''))
dbsession.transaction().start(True)
schema=dbsession.nominalSchema()
hltprescQuery=schema.tableHandle(nameDealer.hltTableName()).newQuery()
hltprescQuery.addToOutputList("PATHNAME","hltpath")
hltprescQuery.addToOutputList("PRESCALE","hltprescale")
hltprescCondition=coral.AttributeList()
hltprescCondition.extend('runnumber','unsigned int')
hltprescCondition.extend('cmslsnum','unsigned int')
hltprescCondition.extend('inf','unsigned int')
hltprescResult=coral.AttributeList()
hltprescResult.extend('hltpath','string')
hltprescResult.extend('hltprescale','unsigned int')
hltprescQuery.defineOutput(hltprescResult)
hltprescCondition['runnumber'].setData(int(runnum))
hltprescCondition['cmslsnum'].setData(1)
hltprescCondition['inf'].setData(0)
hltprescQuery.setCondition("RUNNUM =:runnumber and CMSLSNUM =:cmslsnum and PRESCALE !=:inf",hltprescCondition)
cursor=hltprescQuery.execute()
while cursor.next():
hltpath=cursor.currentRow()['hltpath'].data()
hltprescale=cursor.currentRow()['hltprescale'].data()
if lumidata[1].has_key(hltpath):
lumidata[1][hltpath].append(hltprescale)
cursor.close()
del hltprescQuery
dbsession.transaction().commit()
dbsession.transaction().start(True)
schema=dbsession.nominalSchema()
query=schema.newQuery()
query.addToTableList(nameDealer.trgTableName(),'trg')
query.addToTableList(nameDealer.lumisummaryTableName(),'lumisummary')#small table first--right-most
queryCondition=coral.AttributeList()
queryCondition.extend("runnumber","unsigned int")
queryCondition.extend("lumiversion","string")
#queryCondition.extend("alive","bool")
queryCondition["runnumber"].setData(int(runnum))
queryCondition["lumiversion"].setData(c.LUMIVERSION)
#queryCondition["alive"].setData(True)
query.setCondition("lumisummary.RUNNUM=:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.CMSLSNUM=trg.CMSLSNUM and lumisummary.RUNNUM=trg.RUNNUM",queryCondition)
#query.setCondition("trg.RUNNUM =:runnumber AND lumisummary.RUNNUM=:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.CMSLSNUM=trg.CMSLSNUM AND lumisummary.cmsalive=:alive AND trg.BITNUM=:bitnum",queryCondition)
#query.addToOutputList("sum(lumisummary.INSTLUMI*(1-trg.DEADTIME/(lumisummary.numorbit*3564)))","recorded")
query.addToOutputList("lumisummary.CMSLSNUM","cmsls")
query.addToOutputList("lumisummary.INSTLUMI","instlumi")
query.addToOutputList("lumisummary.NUMORBIT","norbits")
query.addToOutputList("trg.TRGCOUNT","trgcount")
query.addToOutputList("trg.BITNAME","bitname")
query.addToOutputList("trg.DEADTIME","trgdeadtime")
query.addToOutputList("trg.PRESCALE","trgprescale")
query.addToOutputList("trg.BITNUM","trgbitnum")
#query.addToOrderList("trg.BITNAME")
#query.addToOrderList("trg.CMSLSNUM")
result=coral.AttributeList()
result.extend("cmsls","unsigned int")
result.extend("instlumi","float")
result.extend("norbits","unsigned int")
result.extend("trgcount","unsigned int")
result.extend("bitname","string")
result.extend("trgdeadtime","unsigned long long")
result.extend("trgprescale","unsigned int")
result.extend("trgbitnum","unsigned int")
trgprescalemap={}
query.defineOutput(result)
cursor=query.execute()
while cursor.next():
cmsls=cursor.currentRow()["cmsls"].data()
instlumi=cursor.currentRow()["instlumi"].data()*c.NORM
norbits=cursor.currentRow()["norbits"].data()
trgcount=cursor.currentRow()["trgcount"].data()
trgbitname=cursor.currentRow()["bitname"].data()
trgdeadtime=cursor.currentRow()["trgdeadtime"].data()
trgprescale=cursor.currentRow()["trgprescale"].data()
trgbitnum=cursor.currentRow()["trgbitnum"].data()
if cmsls==1:
if not trgprescalemap.has_key(trgbitname):
trgprescalemap[trgbitname]=trgprescale
if trgbitnum==0:
if not deadtable.has_key(cmsls):
deadtable[cmsls]=[]
deadtable[cmsls].append(trgdeadtime)
deadtable[cmsls].append(instlumi)
deadtable[cmsls].append(trgcount)
deadtable[cmsls].append(norbits)
cursor.close()
del query
dbsession.transaction().commit()
#
#consolidate results
#
#trgtable
#print 'trgprescalemap',trgprescalemap
#print lumidata[1]
for hpath,trgdataseq in lumidata[1].items():
bitn=trgdataseq[0]
if trgprescalemap.has_key(bitn) and len(trgdataseq)==2:
lumidata[1][hpath].append(trgprescalemap[bitn])
#filter selected cmsls
lumidata[2]=self.filterDeadtable(deadtable,lslist)
if not c.NOWARNING:
for l,deaddata in lumidata[2].items():
if deaddata[1]==0.0:
print '[Warning] : run:ls has 0 instlumi ',str(runnum)+':'+str(l)
if deaddata[2]==0 or deaddata[0]==0:
print '[Warning] : run:ls has 0 deadtime and/or 0 zerobias bit counts ',str(runnum)+':'+str(l)
#print 'lumidata[2] ',lumidata[2]
except Exception,e:
print str(e)
dbsession.transaction().rollback()
del dbsession
def 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 pixelcorrectionsForRange(schema, inputRange):
'''
select fillschemepattern,correctionfactor from fillscheme;
[(fillschemepattern,afterglow),...]
select fillnum,runnum,fillscheme from cmsrunsummary where amodetag='PROTPHYS'
{runnum: (fillnum,fillscheme),...}
output:
{runnum:(afterglowfactor)}
'''
runs = []
result = {}
if isinstance(inputRange, str):
runs.append(int(inputRange))
else:
runs = inputRange
afterglows = []
s = nameDealer.fillschemeTableName()
r = nameDealer.cmsrunsummaryTableName()
qHandle = schema.newQuery()
try:
qHandle.addToTableList(s)
qResult = coral.AttributeList()
qResult.extend('FILLSCHEMEPATTERN', 'string')
qResult.extend('PIXELCORRECTIONFACTOR', 'float')
qHandle.defineOutput(qResult)
qHandle.addToOutputList('FILLSCHEMEPATTERN')
qHandle.addToOutputList('PIXELCORRECTIONFACTOR')
cursor = qHandle.execute()
while next(cursor):
fillschemePattern = cursor.currentRow()['FILLSCHEMEPATTERN'].data()
afterglowfac = cursor.currentRow()['PIXELCORRECTIONFACTOR'].data()
afterglows.append((fillschemePattern, afterglowfac))
except:
del qHandle
raise
del qHandle
qHandle = schema.newQuery()
try:
qConditionStr = 'FILLNUM>:minfillnum'
qCondition = coral.AttributeList()
qCondition.extend('minfillnum', 'unsigned int')
qCondition['minfillnum'].setData(1600)
qHandle.addToTableList(r)
qHandle.addToOutputList('FILLNUM', 'fillnum')
qHandle.addToOutputList('RUNNUM', 'runnum')
qHandle.addToOutputList('FILLSCHEME', 'fillscheme')
qResult = coral.AttributeList()
qResult.extend('fillnum', 'unsigned int')
qResult.extend('runnum', 'unsigned int')
qResult.extend('fillscheme', 'string')
qHandle.setCondition(qConditionStr, qCondition)
qHandle.defineOutput(qResult)
cursor = qHandle.execute()
while next(cursor):
runnum = cursor.currentRow()['runnum'].data()
if runnum not in runs or runnum in result:
continue
fillnum = cursor.currentRow()['fillnum'].data()
afterglow = 1.0
fillscheme = ''
if cursor.currentRow()['fillscheme']:
fillscheme = cursor.currentRow()['fillscheme'].data()
if fillscheme and len(fillscheme) != 0:
afterglow = afterglowByFillscheme(fillscheme, afterglows)
result[runnum] = afterglow
except:
del qHandle
raise
del qHandle
for run in runs:
if run not in result.keys():
result[run] = 1.0 #those have no fillscheme
return result
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()
