Esempio n. 1
0
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()
Esempio n. 2
0
def createValidation(dbsession):
    '''
    lumivalidation table
    '''
    dbsession.transaction().start(False)
    schema=dbsession.nominalSchema()
    db=dbUtil.dbUtil(schema)
    lumivalidation=coral.TableDescription()
    lumivalidation.setName( nameDealer.lumivalidationTableName() )
    lumivalidation.insertColumn( 'RUNNUM','unsigned int' )
    lumivalidation.insertColumn( 'CMSLSNUM','unsigned int' )
    lumivalidation.insertColumn( 'FLAG','string' )
    lumivalidation.insertColumn( 'COMMENT','string' )
    lumivalidation.setPrimaryKey(('RUNNUM','CMSLSNUM'))
    lumivalidation.setNotNullConstraint('FLAG',True)
    db.createTable(lumivalidation,False)
    dbsession.transaction().commit()
Esempio n. 3
0
def createValidation(dbsession):
    """
    lumivalidation table
    """
    dbsession.transaction().start(False)
    schema = dbsession.nominalSchema()
    db = dbUtil.dbUtil(schema)
    lumivalidation = coral.TableDescription()
    lumivalidation.setName(nameDealer.lumivalidationTableName())
    lumivalidation.insertColumn("RUNNUM", "unsigned int")
    lumivalidation.insertColumn("CMSLSNUM", "unsigned int")
    lumivalidation.insertColumn("FLAG", "string")
    lumivalidation.insertColumn("COMMENT", "string")
    lumivalidation.setPrimaryKey(("RUNNUM", "CMSLSNUM"))
    lumivalidation.setNotNullConstraint("FLAG", True)
    db.createTable(lumivalidation, False)
    dbsession.transaction().commit()
Esempio n. 4
0
def validation(schema,run=None,cmsls=None):
    '''retrieve validation data per run or all
    input: run. if not run, retrive all; if cmslsnum selection list pesent, filter out unselected result
    output: {run:[[cmslsnum,status,comment]]}
    '''
    result={}
    qHandle=schema.newQuery()
    queryHandle.addToTableList(nameDealer.lumivalidationTableName())
    queryHandle.addToOutputList('RUNNUM','runnum')
    queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
    queryHandle.addToOutputList('FLAG','flag')
    queryHandle.addToOutputList('COMMENT','comment')
    if run:
        queryCondition='RUNNUM=:runnum'
        queryBind=coral.AttributeList()
        queryBind.extend('runnum','unsigned int')
        queryBind['runnum'].setData(run)
        queryHandle.setCondition(queryCondition,queryBind)
    queryResult=coral.AttributeList()
    queryResult.extend('runnum','unsigned int')
    queryResult.extend('cmslsnum','unsigned int')
    queryResult.extend('flag','string')
    queryResult.extend('comment','string')
    queryHandle.defineOutput(queryResult)
    cursor=queryHandle.execute()
    while cursor.next():
        runnum=cursor.currentRow()['runnum'].data()
        if not result.has_key(runnum):
            result[runnum]=[]
        cmslsnum=cursor.currentRow()['cmslsnum'].data()
        flag=cursor.currentRow()['flag'].data()
        comment=cursor.currentRow()['comment'].data()
        result[runnum].append([cmslsnum,flag,comment])
    if run and cmsls and len(cmsls)!=0:
        selectedresult={}
        for runnum,perrundata in result.items():
            for lsdata in perrundata:
                if lsdata[0] not in cmsls:
                    continue
                if not selectedresult.has_key(runnum):
                    selectedresult[runnum]=[]
                selectedresult[runnum].append(lsdata)
        return selectedresult
    else:
        return result
Esempio n. 5
0
def insertupdateValidationData(dbsession,data):
    '''
    input: data {run:[[ls,status,comment]]}
    '''
    toreplacenocomment=[]#[[('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('FLAG',flag)],[]]
    toreplacewcomment=[]#[[('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('FLAG',flag),('COMMENT',comment)],[]]
    toinsert=[] #[[('RUNNUM',runnum),('CMSLSNUM',cmslsnum),('FLAG',flag),('COMMENT',comment)],[]]
    try:
        dbsession.transaction().start(True)
        dbutil=dbUtil.dbUtil(dbsession.nominalSchema())
        for run,alllsdata in data.items():
            lsselection=[]
            if len(alllsdata)==0:#cross query lumisummary to get all the cmslsnum for this run,then insert all to default
                queryHandle=dbsession.nominalSchema().newQuery()
                lumisummaryData=lumiQueryAPI.lumisummaryByrun(queryHandle,run,'0001')
                del queryHandle
                for lumisummary in lumisummaryData:
                    if lumisummary[-1]==1:#cmsalive
                        lsselection.append([lumisummary[0],'UNKNOWN','NA'])
            else:
                lsselection=alllsdata
            if len(lsselection)==0:
                print 'no LS found for run '+str(run)+' do nothing'
                continue
            for lsdata in lsselection:
                condition='RUNNUM=:runnum AND CMSLSNUM=:cmslsnum'
                conditionDefDict={}
                conditionDefDict['runnum']='unsigned int'
                conditionDefDict['cmslsnum']='unsigned int'
                conditionDict={}
                conditionDict['runnum']=run
                conditionDict['cmslsnum']=lsdata[0]
                if dbutil.existRow(nameDealer.lumivalidationTableName(),condition,conditionDefDict,conditionDict):
                    if len(lsdata)>2 and lsdata[2]:
                        toreplacewcomment.append([('runnum',run),('cmslsnum',lsdata[0]),('flag',lsdata[1]),('comment',lsdata[2])])
                    else: 
                        toreplacenocomment.append([('runnum',run),('cmslsnum',lsdata[0]),('flag',lsdata[1]),('comment','')])
                else:
                    if len(lsdata)>2 and lsdata[2]:
                        toinsert.append([('RUNNUM',run),('CMSLSNUM',lsdata[0]),('FLAG',lsdata[1]),('COMMENT',lsdata[2])])
                    else:
                        toinsert.append([('RUNNUM',run),('CMSLSNUM',lsdata[0]),('FLAG',lsdata[1]),('COMMENT','N/A')])
        dbsession.transaction().commit()
        #print 'toreplace with comment ',toreplacewcomment
        #print 'toreplace without comment ',toreplacenocomment
        #print 'toinsert ',toinsert
        #perform insert
        if len(toinsert)!=0:
            dbsession.transaction().start(False)
            dbutil=dbUtil.dbUtil(dbsession.nominalSchema())
            tabrowDef=[]
            tabrowDef.append(('RUNNUM','unsigned int'))
            tabrowDef.append(('CMSLSNUM','unsigned int'))
            tabrowDef.append(('FLAG','string'))
            tabrowDef.append(('COMMENT','string'))
            dbutil.bulkInsert(nameDealer.lumivalidationTableName(),tabrowDef,toinsert)
            dbsession.transaction().commit()
        #perform update with comment
        if len(toreplacewcomment)!=0:
            dbsession.transaction().start(False)
            dbutil=dbUtil.dbUtil(dbsession.nominalSchema())
            updateAction='FLAG=:flag,COMMENT=:comment'
            updateCondition='RUNNUM=:runnum and CMSLSNUM=:cmslsnum'
            bindvarDef=[]        
            bindvarDef.append(('flag','string'))
            bindvarDef.append(('comment','string'))
            bindvarDef.append(('runnum','unsigned int'))
            bindvarDef.append(('cmslsnum','unsigned int'))
            dbutil.updateRows(nameDealer.lumivalidationTableName(),updateAction,updateCondition,bindvarDef,toreplacewcomment)
            dbsession.transaction().commit()
        #perform update with NO comment
        if len(toreplacenocomment)!=0:
            dbsession.transaction().start(False)
            dbutil=dbUtil.dbUtil(dbsession.nominalSchema())
            updateAction='FLAG=:flag'
            updateCondition='RUNNUM=:runnum and CMSLSNUM=:cmslsnum'
            bindvarDef=[]        
            bindvarDef.append(('flag','string'))
            bindvarDef.append(('runnum','unsigned int'))
            bindvarDef.append(('cmslsnum','unsigned int'))
            dbutil.updateRows(nameDealer.lumivalidationTableName(),updateAction,updateCondition,bindvarDef,toreplacenocomment)
            dbsession.transaction().commit()
    except Exception, e:
        dbsession.transaction().rollback()
        del dbsession
        raise Exception, 'lumiValidate.insertupdateValidationData:'+str(e)
Esempio n. 6
0
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 
Esempio n. 7
0
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
Esempio n. 8
0
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()
Esempio n. 9
0
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()