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