00001 import os,coral
00002 from RecoLuminosity.LumiDB import nameDealer,dbUtil,revisionDML,lumiTime
00003 import array
00004
00005
00006
00007
00008
00009
00010
00011
00012 def runList(schema,fillnum=None,runmin=None,runmax=None,startT=None,stopT=None,l1keyPattern=None,hltkeyPattern=None,amodetag=None,nominalEnergy=None,energyFlut=0.2,requiretrg=True,requirehlt=True):
00013 '''
00014 select runnum from cmsrunsummary r,lumidata l,trgdata t,hltdata h where r.runnum=l.runnum and l.runnum=t.runnum and t.runnum=h.runnum and r.fillnum=:fillnum and r.runnum>:runmin and r.runnum<:runmax and r.starttime>=:startT and r.stopTime<=:stopT and r.amodetag=:amodetag and regexp_like(r.l1key,:l1keypattern) and regexp_like(hltkey,:hltkeypattern) and l.nominalEnergy>=:nominalEnergy*(1-energyFlut) and l.nominalEnergy<=:nominalEnergy*(1+energyFlut)
00015 '''
00016 result=[]
00017 qHandle=schema.newQuery()
00018 r=nameDealer.cmsrunsummaryTableName()
00019 l=nameDealer.lumidataTableName()
00020 t=nameDealer.trgdataTableName()
00021 h=nameDealer.hltdataTableName()
00022 try:
00023 qHandle.addToTableList(r)
00024 qHandle.addToTableList(l)
00025 if requiretrg:
00026 qHandle.addToTableList(t)
00027 if requirehlt:
00028 qHandle.addToTableList(h)
00029 qConditionStr=r+'.runnum='+l+'.runnum and '+l+'.runnum='+t+'.runnum and '+t+'.runnum='+h+'.runnum'
00030 qCondition=coral.AttributeList()
00031 if fillnum:
00032 qConditionStr+=' and '+r+'.fillnum=:fillnum'
00033 qCondition.extend('fillnum','unsigned int')
00034 qCondition['fillnum'].setData(fillnum)
00035 if runmin:
00036 qConditionStr+=' and '+r+'.runnum>=:runmin'
00037 qCondition.extend('runmin','unsigned int')
00038 qCondition['runmin'].setData(runmin)
00039 if runmax:
00040 qConditionStr+=' and '+r+'.runnum<=:runmax'
00041 qCondition.extend('runmax','unsigned int')
00042 qCondition['runmax'].setData(runmax)
00043 if startT:
00044 qConditionStr+=' and '+r+'.starttime>=:startT'
00045 qCondition.extend('start','time stamp')
00046 qCondition['startT'].setData(startT)
00047 if stopT:
00048 qConditionStr+=' and '+r+'.stoptime<=:stopT'
00049 qCondition.extend('stop','time stamp')
00050 qCondition['stop'].setData(stopT)
00051 if amodetag:
00052 qConditionStr+=' and '+r+'.amodetag=:amodetag'
00053 qCondition.extend('amodetag','string')
00054 qCondition['amodetag'].setData(amodetag)
00055 if l1keyPattern:
00056 qConditionStr+=' and regexp_like('+r+'.l1key,:l1keypattern)'
00057 qCondition.extend('l1keypattern','string')
00058 qCondition['l1keypattern'].setData(l1keyPattern)
00059 if hltkeyPattern:
00060 qConditionStr+=' and regexp_like('+r+'.hltkey,:hltkeypattern)'
00061 qCondition.extend('hltkeypattern','string')
00062 qCondition['hltkeypattern'].setData(hltkeyPattern)
00063 if nominalEnergy:
00064 emin=nominalEnergy*(1.0-energyFlut)
00065 emax=nominalEnergy*(1.0+energyFlut)
00066 qConditionStr+=' and '+l+'.nominalegev>=:emin and '+l+'.nominalegev<=:emax'
00067 qCondition.extend('emin','float')
00068 qCondition.extend('emax','float')
00069 qCondition['emin'].setData(emin)
00070 qCondition['emax'].setData(emax)
00071 qResult=coral.AttributeList()
00072 qResult.extend('runnum','unsigned int')
00073 qHandle.defineOutput(qResult)
00074 qHandle.setCondition(qConditionStr,qCondition)
00075 qHandle.addToOutputList(r+'.RUNNUM','runnum')
00076 cursor=qHandle.execute()
00077 while cursor.next():
00078 result.append(cursor.currentRow()['runnum'].data())
00079 except :
00080 del qHandle
00081 raise
00082 del qHandle
00083 return result
00084
00085 def runsummary(schema,runnum,sessionflavor=''):
00086 '''
00087 select l1key,amodetag,egev,hltkey,fillnum,sequence,to_char(starttime),to_char(stoptime) from cmsrunsummary where runnum=:runnum
00088 output: [l1key,amodetag,egev,hltkey,fillnum,sequence,starttime,stoptime]
00089 '''
00090 result=[]
00091 qHandle=schema.newQuery()
00092 t=lumiTime.lumiTime()
00093 try:
00094 qHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
00095 qCondition=coral.AttributeList()
00096 qCondition.extend('runnum','unsigned int')
00097 qCondition['runnum'].setData(int(runnum))
00098 qHandle.addToOutputList('L1KEY','l1key')
00099 qHandle.addToOutputList('AMODETAG','amodetag')
00100 qHandle.addToOutputList('EGEV','egev')
00101 qHandle.addToOutputList('HLTKEY','hltkey')
00102 qHandle.addToOutputList('FILLNUM','fillnum')
00103 qHandle.addToOutputList('SEQUENCE','sequence')
00104 if sessionflavor=='SQLite':
00105 qHandle.addToOutputList('STARTTIME','starttime')
00106 qHandle.addToOutputList('STOPTIME','stoptime')
00107 else:
00108 qHandle.addToOutputList('to_char(STARTTIME,\''+t.coraltimefm+'\')','starttime')
00109 qHandle.addToOutputList('to_char(STOPTIME,\''+t.coraltimefm+'\')','stoptime')
00110 qHandle.setCondition('RUNNUM=:runnum',qCondition)
00111 qResult=coral.AttributeList()
00112 qResult.extend('l1key','string')
00113 qResult.extend('amodetag','string')
00114 qResult.extend('egev','unsigned int')
00115 qResult.extend('hltkey','string')
00116 qResult.extend('fillnum','unsigned int')
00117 qResult.extend('sequence','string')
00118 qResult.extend('starttime','string')
00119 qResult.extend('stoptime','string')
00120 qHandle.defineOutput(qResult)
00121 cursor=qHandle.execute()
00122 while cursor.next():
00123 result.append(cursor.currentRow()['l1key'].data())
00124 result.append(cursor.currentRow()['amodetag'].data())
00125 result.append(cursor.currentRow()['egev'].data())
00126 result.append(cursor.currentRow()['hltkey'].data())
00127 result.append(cursor.currentRow()['fillnum'].data())
00128 result.append(cursor.currentRow()['sequence'].data())
00129 result.append(cursor.currentRow()['starttime'].data())
00130 result.append(cursor.currentRow()['stoptime'].data())
00131 except :
00132 del qHandle
00133 raise
00134 del qHandle
00135 return result
00136
00137 def mostRecentLuminorms(schema,branchfilter):
00138 '''
00139 this overview query should be only for norm
00140 select e.name,max(n.data_id),r.revision_id , n.amodetag,n.norm_1,n.egev_1,n.norm_2,n.egev_2 from luminorms_entries e,luminorms_rev r,luminorms n where n.entry_id=e.entry_id and n.data_id=r.data_id and r.revision_id>=min(branchfilter) and r.revision_id<=max(branchfilter) group by e.entry_name,r.revision_id,n.amodetag,n.norm_1,n.egev_1,n.norm_2,n.egev_2;
00141 output {norm_name:[data_id,amodetag,norm_1,egev_1,norm_2,egev_2]}
00142 '''
00143 print branchfilter
00144 result={}
00145 entry2datamap={}
00146 branchmin=0
00147 branchmax=0
00148 if branchfilter and len(branchfilter)!=0:
00149 branchmin=min(branchfilter)
00150 branchmax=max(branchfilter)
00151 else:
00152 return result
00153 qHandle=schema.newQuery()
00154 try:
00155 qHandle.addToTableList(nameDealer.entryTableName(nameDealer.luminormTableName()),'e')
00156 qHandle.addToTableList(nameDealer.luminormTableName(),'n')
00157 qHandle.addToTableList(nameDealer.revmapTableName(nameDealer.luminormTableName()),'r')
00158 qHandle.addToOutputList('e.NAME','normname')
00159 qHandle.addToOutputList('max(r.DATA_ID)','data_id')
00160 qHandle.addToOutputList('r.REVISION_ID','revision_id')
00161 qHandle.addToOutputList('n.AMODETAG','amodetag')
00162 qHandle.addToOutputList('n.NORM_1','norm_1')
00163 qHandle.addToOutputList('n.EGEV_1','energy_1')
00164 qHandle.addToOutputList('n.NORM_2','norm_2')
00165 qHandle.addToOutputList('n.EGEV_2','energy_2')
00166 qCondition=coral.AttributeList()
00167 qCondition.extend('branchmin','unsigned long long')
00168 qCondition.extend('branchmax','unsigned long long')
00169 qCondition['branchmin'].setData(branchmin)
00170 qCondition['branchmax'].setData(branchmax)
00171 qResult=coral.AttributeList()
00172 qResult.extend('normname','string')
00173 qResult.extend('data_id','unsigned long long')
00174 qResult.extend('revision_id','unsigned long long')
00175 qResult.extend('amodetag','string')
00176 qResult.extend('norm_1','float')
00177 qResult.extend('energy_1','unsigned int')
00178 qResult.extend('norm_2','float')
00179 qResult.extend('energy_2','unsigned int')
00180 qHandle.defineOutput(qResult)
00181 qHandle.setCondition('n.ENTRY_ID=e.ENTRY_ID and n.DATA_ID=r.DATA_ID AND n.DATA_ID=r.DATA_ID AND r.REVISION_ID>=:branchmin AND r.REVISION_ID<=:branchmax',qCondition)
00182 qHandle.groupBy('e.name,r.revision_id,n.amodetag,n.norm_1,n.egev_1,n.norm_2,n.egev_2')
00183 cursor=qHandle.execute()
00184 while cursor.next():
00185 normname=cursor.currentRow()['normname'].data()
00186 amodetag=cursor.currentRow()['amodetag'].data()
00187 norm_1=cursor.currentRow()['norm_1'].data()
00188 energy_1=cursor.currentRow()['energy_1'].data()
00189 norm_2=None
00190 if cursor.currentRow()['norm_2'].data():
00191 norm_2=cursor.currentRow()['norm_2'].data()
00192 energy_2=None
00193 if cursor.currentRow()['energy_2'].data():
00194 energy_2=cursor.currentRow()['energy_2'].data()
00195 result[normname]=[amodetag,norm_1,energy_1,norm_2,energy_2]
00196 except:
00197 raise
00198 return result
00199 def luminormById(schema,dataid):
00200 '''
00201 select entry_name,amodetag,norm_1,egev_1,norm_2,egev_2 from luminorms where DATA_ID=:dataid
00202 result [name(0),amodetag(1),norm_1(2),egev_1(3),norm_2(4),energy_2(5) ]
00203 '''
00204 result=[]
00205 qHandle=schema.newQuery()
00206 try:
00207 qHandle.addToTableList(nameDealer.luminormTableName())
00208 qHandle.addToOutputList('ENTRY_NAME','normname')
00209 qHandle.addToOutputList('AMODETAG','amodetag')
00210 qHandle.addToOutputList('NORM_1','norm_1')
00211 qHandle.addToOutputList('EGEV_1','energy_1')
00212 qHandle.addToOutputList('NORM_2','norm_2')
00213 qHandle.addToOutputList('EGEV_2','energy_2')
00214 qCondition=coral.AttributeList()
00215 qCondition.extend('dataid','unsigned long long')
00216 qCondition['dataid'].setData(dataid)
00217 qResult=coral.AttributeList()
00218 qResult.extend('normname','string')
00219 qResult.extend('amodetag','string')
00220 qResult.extend('norm_1','float')
00221 qResult.extend('energy_1','unsigned int')
00222 qResult.extend('norm_2','float')
00223 qResult.extend('energy_2','unsigned int')
00224 qHandle.defineOutput(qResult)
00225 qHandle.setCondition('DATA_ID=:dataid',qCondition)
00226 cursor=qHandle.execute()
00227 while cursor.next():
00228 normname=cursor.currentRow()['normname'].data()
00229 amodetag=cursor.currentRow()['amodetag'].data()
00230 norm_1=cursor.currentRow()['norm_1'].data()
00231 energy_1=cursor.currentRow()['energy_1'].data()
00232 norm_2=None
00233 if cursor.currentRow()['norm_2'].data():
00234 norm_2=cursor.currentRow()['norm_2'].data()
00235 energy_2=None
00236 if cursor.currentRow()['energy_2'].data():
00237 energy_2=cursor.currentRow()['energy_2'].data()
00238 result.extend([normname,amodetag,norm_1,energy_1,norm_2,energy_2])
00239 except :
00240 del qHandle
00241 raise
00242 del qHandle
00243 return result
00244
00245 def trgRunById(schema,dataid):
00246 '''
00247 select RUNNUM,SOURCE,BITZERONAME,BITNAMECLOB from trgdata where DATA_ID=:dataid
00248 result [runnum(0),datasource(1),bitzeroname(2),bitnameclob(3)]
00249 '''
00250 result=[]
00251 qHandle=schema.newQuery()
00252 try:
00253 qHandle.addToTableList(nameDealer.trgdataTableName())
00254 qHandle.addToOutputList('RUNNUM','runnum')
00255 qHandle.addToOutputList('SOURCE','source')
00256 qHandle.addToOutputList('BITZERONAME','bitzeroname')
00257 qHandle.addToOutputList('BITNAMECLOB','bitnameclob')
00258 qCondition=coral.AttributeList()
00259 qCondition.extend('dataid','unsigned long long')
00260 qCondition['dataid'].setData(dataid)
00261 qResult=coral.AttributeList()
00262 qResult.extend('runnum','unsigned int')
00263 qResult.extend('source','string')
00264 qResult.extend('bitzeroname','string')
00265 qResult.extend('bitnameclob','string')
00266 qHandle.defineOutput(qResult)
00267 qHandle.setCondition('DATA_ID=:dataid',qCondition)
00268 cursor=qHandle.execute()
00269 while cursor.next():
00270 runnum=cursor.currentRow()['runnum'].data()
00271 print 'runnum ',runnum
00272 source=cursor.currentRow()['source'].data()
00273 print 'source ',source
00274 bitzeroname=cursor.currentRow()['bitzeroname'].data()
00275 print 'bitzeroname ',bitzeroname
00276 bitnameclob=cursor.currentRow()['bitnameclob'].data()
00277 print 'bitnameclob ',bitnameclob
00278
00279 result.extend([runnum,source,bitzeroname,bitnameclob])
00280 except :
00281 del qHandle
00282 raise
00283 del qHandle
00284 return result
00285
00286 def trgLSById(schema,dataid,withblobdata=False):
00287 '''
00288 result (runnum,{cmslsnum:[deadtimecount(0),bitzerocount(1),bitzeroprescale(2),deadfrac(3),prescalesblob(4),trgcountblob(5)]})
00289 '''
00290 runnum=0
00291 result={}
00292 qHandle=schema.newQuery()
00293 try:
00294 qHandle.addToTableList(nameDealer.lstrgTableName())
00295 qHandle.addToOutputList('RUNNUM','runnum')
00296 qHandle.addToOutputList('CMSLSNUM','cmslsnum')
00297 qHandle.addToOutputList('DEADTIMECOUNT','deadtimecount')
00298 qHandle.addToOutputList('BITZEROCOUNT','bitzerocount')
00299 qHandle.addToOutputList('BITZEROPRESCALE','bitzeroprescale')
00300 qHandle.addToOutputList('DEADFRAC','deadfrac')
00301 if withblobdata:
00302 qHandle.addToOutputList('PRESCALESBLOB','prescalesblob')
00303 qHandle.addToOutputList('TRGCOUNTBLOB','trgcountblob')
00304 qConditionStr='DATA_ID=:dataid'
00305 qCondition=coral.AttributeList()
00306 qCondition.extend('dataid','unsigned long long')
00307 qCondition['dataid'].setData(dataid)
00308 qResult=coral.AttributeList()
00309 qResult.extend('runnum','unsigned int')
00310 qResult.extend('cmslsnum','unsigned int')
00311 qResult.extend('deadtimecount','unsigned long long')
00312 qResult.extend('bitzerocount','unsigned int')
00313 qResult.extend('bitzeroprescale','unsigned int')
00314 qResult.extend('deadfrac','float')
00315 if withblobdata:
00316 qResult.extend('prescalesblob','blob')
00317 qResult.extend('trgcountblob','blob')
00318 qHandle.defineOutput(qResult)
00319 qHandle.setCondition(qConditionStr,qCondition)
00320 cursor=qHandle.execute()
00321 while cursor.next():
00322 runnum=cursor.currentRow()['runnum'].data()
00323 cmslsnum=cursor.currentRow()['cmslsnum'].data()
00324 deadtimecount=cursor.currentRow()['deadtimecount'].data()
00325 bitzerocount=cursor.currentRow()['bitzerocount'].data()
00326 bitzeroprescale=cursor.currentRow()['bitzeroprescale'].data()
00327 deadfrac=cursor.currentRow()['deadfrac'].data()
00328 if not result.has_key(cmslsnum):
00329 result[cmslsnum]=[]
00330 result[cmslsnum].append(deadtimecount)
00331 result[cmslsnum].append(bitzerocount)
00332 result[cmslsnum].append(bitzeroprescale)
00333 result[cmslsnum].append(deadfrac)
00334 prescalesblob=None
00335 trgcountblob=None
00336 if withblobdata:
00337 prescalesblob=cursor.currentRow()['prescalesblob']
00338 trgcountblob=cursor.currentRow()['trgcountblob']
00339 result[cmslsnum].extend([prescalesblob,trgcountblob])
00340 except:
00341 del qHandle
00342 raise
00343 del qHandle
00344 return (runnum,result)
00345 def lumiRunById(schema,dataid):
00346 '''
00347 result [runnum(0),datasource(1)]
00348 '''
00349 result=[]
00350 qHandle=schema.newQuery()
00351 try:
00352 qHandle.addToTableList(nameDealer.lumidataTableName())
00353 qHandle.addToOutputList('RUNNUM','runnum')
00354 qHandle.addToOutputList('SOURCE','datasource')
00355 qConditionStr='DATA_ID=:dataid'
00356 qCondition=coral.AttributeList()
00357 qCondition.extend('dataid','unsigned long long')
00358 qCondition['dataid'].setData(dataid)
00359 qResult=coral.AttributeList()
00360 qResult.extend('runnum','unsigned int')
00361 qResult.extend('datasource','string')
00362 qHandle.defineOutput(qResult)
00363 qHandle.setCondition(qConditionStr,qCondition)
00364 cursor=qHandle.execute()
00365 while cursor.next():
00366 runnum=cursor.currentRow()['runnum'].data()
00367 datasource=cursor.currentRow()['datasource'].data()
00368 result.extend([runnum,datasource])
00369 except :
00370 del qHandle
00371 raise
00372 del qHandle
00373 return result
00374 def lumiLSById(schema,dataid,beamstatus=None,beamenergy=None,beamenergyFluc=0.2,withBXInfo=False,bxAlgo='OCC1',withBeamIntensity=False):
00375 '''
00376 result (runnum,{lumilsnum,[cmslsnum(0),instlumi(1),instlumierr(2),instlumiqlty(3),beamstatus(4),beamenergy(5),numorbit(6),startorbit(7),bxvalueblob(8),bxerrblob(9),bxindexblob(10),beam1intensity(11),beam2intensity(12)]})
00377 '''
00378 runnum=0
00379 result={}
00380 qHandle=schema.newQuery()
00381 if withBXInfo and bxAlgo not in ['OCC1','OCC2','ET']:
00382 raise ValueError('unknown lumi algo '+bxAlgo)
00383 if beamstatus and beamstatus not in ['STABLE BEAMS',]:
00384 raise ValueError('unknown beam status '+beamstatus)
00385 try:
00386 qHandle.addToTableList(nameDealer.lumisummaryv2TableName())
00387 qHandle.addToOutputList('RUNNUM','runnum')
00388 qHandle.addToOutputList('CMSLSNUM','cmslsnum')
00389 qHandle.addToOutputList('LUMILSNUM','lumilsnum')
00390 qHandle.addToOutputList('INSTLUMI','instlumi')
00391 qHandle.addToOutputList('INSTLUMIERROR','instlumierr')
00392 qHandle.addToOutputList('INSTLUMIQUALITY','instlumiqlty')
00393 qHandle.addToOutputList('BEAMSTATUS','beamstatus')
00394 qHandle.addToOutputList('BEAMENERGY','beamenergy')
00395 qHandle.addToOutputList('NUMORBIT','numorbit')
00396 qHandle.addToOutputList('STARTORBIT','startorbit')
00397 if withBXInfo:
00398 qHandle.addToOutputList('BXLUMIVALUE_'+bxAlgo,'bxvalue')
00399 qHandle.addToOutputList('BXLUMIERROR_'+bxAlgo,'bxerror')
00400 if withBeamIntensity:
00401 qHandle.addToOutputList('CMSBXINDEXBLOB','bxindexblob')
00402 qHandle.addToOutputList('BEAMINTENSITYBLOB_1','beam1intensity')
00403 qHandle.addToOutputList('BEAMINTENSITYBLOB_2','beam2intensity')
00404
00405 qConditionStr='DATA_ID=:dataid'
00406 qCondition=coral.AttributeList()
00407 qCondition.extend('dataid','unsigned long long')
00408 qCondition['dataid'].setData(dataid)
00409 if beamstatus:
00410 qConditionStr+=' and BEAMSTATUS=:beamstatus'
00411 qCondition.extend('beamstatus','string')
00412 qCondition['beamstatus'].setData(beamstatus)
00413 if beamenergy:
00414 emin=float(beamenergy)*(1.0-beamenergyFluc)
00415 emax=float(beamenergy)*(1.0+beamenergyFluc)
00416 qConditionStr+=' and BEAMENERGY>=:emin and BEAMENERGY<=:emax'
00417 qCondition.extend('beamenergy','float')
00418 qCondition['emin'].setData(emin)
00419 qCondition['emax'].setData(emax)
00420 qResult=coral.AttributeList()
00421 qResult.extend('runnum','unsigned int')
00422 qResult.extend('cmslsnum','unsigned int')
00423 qResult.extend('lumilsnum','unsigned int')
00424 qResult.extend('instlumi','float')
00425 qResult.extend('instlumierr','float')
00426 qResult.extend('instlumiqlty','short')
00427 qResult.extend('beamstatus','string')
00428 qResult.extend('beamenergy','float')
00429 qResult.extend('numorbit','unsigned int')
00430 qResult.extend('startorbit','unsigned int')
00431 if withBXInfo:
00432 qResult.extend('bxvalue','blob')
00433 qResult.extend('bxerror','blob')
00434 if withBeamIntensity:
00435 qResult.extend('bxindexblob','blob')
00436 qResult.extend('beam1intensity','blob')
00437 qResult.extend('beam2intensity','blob')
00438 qHandle.defineOutput(qResult)
00439 qHandle.setCondition(qConditionStr,qCondition)
00440 cursor=qHandle.execute()
00441 while cursor.next():
00442 runnum=cursor.currentRow()['runnum'].data()
00443 cmslsnum=cursor.currentRow()['cmslsnum'].data()
00444 lumilsnum=cursor.currentRow()['lumilsnum'].data()
00445 instlumi=cursor.currentRow()['instlumi'].data()
00446 instlumierr=cursor.currentRow()['instlumierr'].data()
00447 instlumiqlty=cursor.currentRow()['instlumiqlty'].data()
00448 beamstatus=cursor.currentRow()['beamstatus'].data()
00449 beamenergy=cursor.currentRow()['beamenergy'].data()
00450 numorbit=cursor.currentRow()['numorbit'].data()
00451 startorbit=cursor.currentRow()['startorbit'].data()
00452 bxvalueblob=None
00453 bxerrblob=None
00454 if withBXInfo:
00455 bxvalueblob=cursor.currentRow()['bxvalue'].data()
00456 bxerrblob==cursor.currentRow()['bxerror'].data()
00457 bxindexblob=None
00458 beam1intensity=None
00459 beam2intensity=None
00460 if withBeamIntensity:
00461 bxindexblob=cursor.currentRow()['bxindexblob'].data()
00462 beam1intensity=cursor.currentRow()['beam1intensity'].data()
00463 beam2intensity=cursor.currentRow()['beam2intensity'].data()
00464 if not result.has_key(lumilsnum):
00465 result[lumilsnum]=[]
00466 result[lumilsnum].extend([cmslsnum,instlumi,instlumierr,instlumiqlty,beamstatus,beamenergy,numorbit,startorbit,bxvalueblob,bxerrblob,bxindexblob,beam1intensity,beam2intensity])
00467 except :
00468 del qHandle
00469 raise
00470 del qHandle
00471 return (runnum,result)
00472 def beamInfoById(schema,dataid):
00473 '''
00474 result (runnum,{lumilsnum,[cmslsnum(0),beamstatus(1),beamenergy(2),beam1intensity(3),beam2intensity(4)]})
00475 '''
00476 runnum=0
00477 result={}
00478 qHandle=schema.newQuery()
00479 try:
00480 qHandle.addToTableList(nameDealer.lumisummaryv2TableName())
00481 qHandle.addToOutputList('RUNNUM','runnum')
00482 qHandle.addToOutputList('CMSLSNUM','cmslsnum')
00483 qHandle.addToOutputList('LUMILSNUM','lumilsnum')
00484 qHandle.addToOutputList('BEAMSTATUS','beamstatus')
00485 qHandle.addToOutputList('BEAMENERGY','beamenergy')
00486 qHandle.addToOutputList('CMSBXINDEXBLOB','bxindexblob')
00487 qHandle.addToOutputList('BEAMINTENSITYBLOB_1','beam1intensity')
00488 qHandle.addToOutputList('BEAMINTENSITYBLOB_2','beam2intensity')
00489 qConditionStr='DATA_ID=:dataid'
00490 qCondition=coral.AttributeList()
00491 qCondition.extend('dataid','unsigned long long')
00492 qCondition['dataid'].setData(dataid)
00493 qResult=coral.AttributeList()
00494 qResult.extend('runnum','unsigned int')
00495 qResult.extend('cmslsnum','unsigned int')
00496 qResult.extend('lumilsnum','unsigned int')
00497 qResult.extend('beamstatus','string')
00498 qResult.extend('beamenergy','float')
00499 qResult.extend('bxindexblob','blob')
00500 qResult.extend('beam1intensity','blob')
00501 qResult.extend('beam2intensity','blob')
00502 qHandle.defineOutput(qResult)
00503 qHandle.setCondition(qConditionStr,qCondition)
00504 cursor=qHandle.execute()
00505 while cursor.next():
00506 runnum=cursor.currentRow()['runnum'].data()
00507 cmslsnum=cursor.currentRow()['cmslsnum'].data()
00508 lumilsnum=cursor.currentRow()['lumilsnum'].data()
00509 beamstatus=cursor.currentRow()['beamstatus'].data()
00510 beamenergy=cursor.currentRow()['beamenergy'].data()
00511 bxindexblob=cursor.currentRow()['bxindexblob'].data()
00512 beam1intensity=cursor.currentRow()['beam1intensity'].data()
00513 beam2intensity=cursor.currentRow()['beam2intensity'].data()
00514 if not result.has_key(lumilsnum):
00515 result[lumilsnum]=[]
00516 result[lumilsnum].extend([lumilsnum,beamstatus,beamenergy,bxindexblob,beam1intensity,beam2intensity])
00517 except :
00518 del qHandle
00519 raise
00520 del qHandle
00521 return (runnum,result)
00522 def lumiBXByAlgo(schema,dataid,algoname):
00523 '''
00524 result {lumilsnum:[cmslsnum,numorbit,startorbit,bxlumivalue,bxlumierr,bxlumiqlty]}
00525 '''
00526 result={}
00527 qHandle=schema.newQuery()
00528 try:
00529 qHandle.addToTableList(nameDealer.lumisummaryv2TableName())
00530 qHandle.addToOutputList('CMSLSNUM','cmslsnum')
00531 qHandle.addToOutputList('LUMILSNUM','lumilsnum')
00532
00533 qHandle.addToOutputList('NUMORBIT','numorbit')
00534 qHandle.addToOutputList('STARTORBIT','startorbit')
00535 qHandle.addToOutputList('BXLUMIVALUE_'+algoname,'bxlumivalue')
00536 qHandle.addToOutputList('BXLUMIERROR_'+algoname,'bxlumierr')
00537 qHandle.addToOutputList('BXLUMIQUALITY_'+algoname,'bxlumiqlty')
00538 qConditionStr='DATA_ID=:dataid'
00539 qCondition=coral.AttributeList()
00540 qCondition.extend('dataid','unsigned long long')
00541 qCondition['dataid'].setData(dataid)
00542 qResult=coral.AttributeList()
00543 qResult.extend('cmslsnum','unsigned int')
00544 qResult.extend('lumilsnum','unsigned int')
00545 qResult.extend('numorbit','unsigned int')
00546 qResult.extend('startorbit','unsigned int')
00547 qResult.extend('bxlumivalue','blob')
00548 qResult.extend('bxlumierr','blob')
00549 qResult.extend('bxlumiqlty','blob')
00550 qHandle.defineOutput(qResult)
00551 qHandle.setCondition(qConditionStr,qCondition)
00552 cursor=qHandle.execute()
00553 while cursor.next():
00554 cmslsnum=cursor.currentRow()['cmslsnum'].data()
00555 lumilsnum=cursor.currentRow()['lumilsnum'].data()
00556 numorbit=cursor.currentRow()['numorbit'].data()
00557 startorbit=cursor.currentRow()['startorbit'].data()
00558 bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
00559 bxlumierr=cursor.currentRow()['bxlumierr'].data()
00560 bxlumiqlty=cursor.currentRow()['bxlumiqlty'].data()
00561 if not result.has_key(algoname):
00562 result[algoname]={}
00563 if not result[algoname].has_key(lumilsnum):
00564 result[algoname][lumilsnum]=[]
00565 result[algoname][lumilsnum].extend([cmslsnum,numorbit,startorbit,bxlumivalue,bxlumierr,bxlumiqlty])
00566 except :
00567 del qHandle
00568 raise RuntimeError(' dataDML.lumiBXById: '+str(e))
00569 del qHandle
00570 return result
00571
00572 def hltRunById(schema,dataid):
00573 '''
00574 result [runnum(0),datasource(1),npath(2),pathnameclob(3)]
00575 '''
00576 result=[]
00577 qHandle=schema.newQuery()
00578 try:
00579 qHandle.addToTableList(nameDealer.hltdataTableName())
00580 qHandle.addToOutputList('RUNNUM','runnum')
00581 qHandle.addToOutputList('SOURCE','datasource')
00582 qHandle.addToOutputList('NPATH','npath')
00583 qHandle.addToOutputList('PATHNAMECLOB','pathnameclob')
00584 qConditionStr='DATA_ID=:dataid'
00585 qCondition=coral.AttributeList()
00586 qCondition.extend('dataid','unsigned long long')
00587 qCondition['dataid'].setData(dataid)
00588 qResult=coral.AttributeList()
00589 qResult.extend('runnum','unsigned int')
00590 qResult.extend('datasource','string')
00591 qResult.extend('npath','unsigned int')
00592 qResult.extend('pathnameclob','string')
00593 qHandle.defineOutput(qResult)
00594 qHandle.setCondition(qConditionStr,qCondition)
00595 cursor=qHandle.execute()
00596 while cursor.next():
00597 runnum=cursor.currentRow()['runnum'].data()
00598 datasource=cursor.currentRow()['datasource'].data()
00599 npath=cursor.currentRow()['npath'].data()
00600 pathnameclob=cursor.currentRow()['pathnameclob'].data()
00601 result.extend([runnum,datasource,npath,pathnameclob])
00602 except :
00603 del qHandle
00604 raise
00605 del qHandle
00606 return result
00607
00608 def hlttrgMappingByrun(schema,runnum):
00609 '''
00610 select m.hltkey,m.hltpathname,m.l1seed from cmsrunsummary r,trghltmap m where r.runnum=:runnum and m.hltkey=r.hltkey
00611 output: {hltpath:l1seed}
00612 '''
00613 result={}
00614 queryHandle=schema.newQuery()
00615 r=nameDealer.cmsrunsummaryTableName()
00616 m=nameDealer.trghltMapTableName()
00617 try:
00618 queryHandle.addToTableList(r)
00619 queryHandle.addToTableList(m)
00620 queryCondition=coral.AttributeList()
00621 queryCondition.extend('runnum','unsigned int')
00622 queryCondition['runnum'].setData(int(runnum))
00623
00624 queryHandle.addToOutputList(m+'.HLTPATHNAME','hltpathname')
00625 queryHandle.addToOutputList(m+'.L1SEED','l1seed')
00626 queryHandle.setCondition(r+'.RUNNUM=:runnum and '+m+'.HLTKEY='+r+'.HLTKEY',queryCondition)
00627 queryResult=coral.AttributeList()
00628
00629 queryResult.extend('hltpathname','string')
00630 queryResult.extend('l1seed','string')
00631 queryHandle.defineOutput(queryResult)
00632 cursor=queryHandle.execute()
00633 while cursor.next():
00634
00635 hltpathname=cursor.currentRow()['hltpathname'].data()
00636 l1seed=cursor.currentRow()['l1seed'].data()
00637 if not result.has_key(hltpathname):
00638 result[hltpathname]=l1seed
00639 except :
00640 del queryHandle
00641 raise
00642 del queryHandle
00643 return result
00644
00645 def hltLSById(schema,dataid):
00646 '''
00647 result (runnum, {cmslsnum:[prescaleblob,hltcountblob,hltacceptblob]}
00648 '''
00649 result={}
00650 qHandle=schema.newQuery()
00651 try:
00652 qHandle.addToTableList(nameDealer.lshltTableName())
00653 qHandle.addToOutputList('RUNNUM','runnum')
00654 qHandle.addToOutputList('CMSLSNUM','cmslsnum')
00655 qHandle.addToOutputList('PRESCALEBLOB','prescaleblob')
00656 qHandle.addToOutputList('HLTCOUNTBLOB','hltcountblob')
00657 qHandle.addToOutputList('HLTACCEPTBLOB','hltacceptblob')
00658 qConditionStr='DATA_ID=:dataid'
00659 qCondition=coral.AttributeList()
00660 qCondition.extend('dataid','unsigned long long')
00661 qCondition['dataid'].setData(dataid)
00662 qResult=coral.AttributeList()
00663 qResult.extend('runnum','unsigned int')
00664 qResult.extend('cmslsnum','unsigned int')
00665 qResult.extend('prescaleblob','blob')
00666 qResult.extend('hltcountblob','blob')
00667 qResult.extend('hltacceptblob','blob')
00668 qHandle.defineOutput(qResult)
00669 qHandle.setCondition(qConditionStr,qCondition)
00670 cursor=qHandle.execute()
00671 while cursor.next():
00672 runnum=cursor.currentRow()['runnum'].data()
00673 cmslsnum=cursor.currentRow()['cmslsnum'].data()
00674 prescaleblob=cursor.currentRow()['prescaleblob'].data()
00675 hltcountblob=cursor.currentRow()['hltcountblob'].data()
00676 hltacceptblob=cursor.currentRow()['hltacceptblob'].data()
00677 if not result.has_key(cmslsnum):
00678 result[cmslsnum]=[]
00679 result[cmslsnum].extend([prescaleblob,hltcountblob,hltacceptblob])
00680 except :
00681 del qHandle
00682 raise
00683 del qHandle
00684 return (runnum,result)
00685 def guessDataIdByRun(schema,runnum):
00686 '''
00687 get dataids by runnumber, if there are duplicates, pick max(dataid).Bypass full version lookups
00688 result (lumidataid(0),trgdataid(1),hltdataid(2))
00689 '''
00690 lumiids=[]
00691 trgids=[]
00692 hltids=[]
00693 qHandle=schema.newQuery()
00694 try:
00695 qHandle.addToTableList(nameDealer.lumidataTableName(),'l')
00696 qHandle.addToTableList(nameDealer.trgdataTableName(),'t')
00697 qHandle.addToTableList(nameDealer.hltdataTableName(),'h')
00698 qHandle.addToOutputList('l.DATA_ID','lumidataid')
00699 qHandle.addToOutputList('t.DATA_ID','trgdataid')
00700 qHandle.addToOutputList('h.DATA_ID','hltdataid')
00701 qConditionStr='l.RUNNUM=t.RUNNUM and t.RUNNUM=h.RUNNUM and l.RUNNUM=:runnum '
00702 qCondition=coral.AttributeList()
00703 qCondition.extend('runnum','unsigned int')
00704 qCondition['runnum'].setData(runnum)
00705 qResult=coral.AttributeList()
00706 qResult.extend('lumidataid','unsigned long long')
00707 qResult.extend('trgdataid','unsigned long long')
00708 qResult.extend('hltdataid','unsigned long long')
00709 qHandle.defineOutput(qResult)
00710 qHandle.setCondition(qConditionStr,qCondition)
00711 cursor=qHandle.execute()
00712 while cursor.next():
00713 lumidataid=cursor.currentRow()['lumidataid'].data()
00714 trgdataid=cursor.currentRow()['trgdataid'].data()
00715 hltdataid=cursor.currentRow()['hltdataid'].data()
00716 lumiids.append(lumidataid)
00717 trgids.append(trgdataid)
00718 hltids.append(hltdataid)
00719 except :
00720 del qHandle
00721 raise
00722 del qHandle
00723 return (max(lumiids),max(trgids),max(hltids))
00724
00725 def guessnormIdByContext(schema,amodetag,egev1):
00726 '''
00727 get norm dataids by amodetag, egev if there are duplicates, pick max(dataid).Bypass full version lookups
00728 select data_id from luminorm where amodetag=:amodetag and egev_1=:egev1
00729 '''
00730 luminormids=[]
00731 qHandle=schema.newQuery()
00732 try:
00733 qHandle.addToTableList( nameDealer.luminormTableName() )
00734 qHandle.addToOutputList('DATA_ID','normdataid')
00735 qConditionStr='AMODETAG=:amodetag AND EGEV_1=:egev1'
00736 qCondition=coral.AttributeList()
00737 qCondition.extend('amodetag','string')
00738 qCondition.extend('egev1','unsigned int')
00739 qCondition['amodetag'].setData(amodetag)
00740 qCondition['egev1'].setData(egev1)
00741 qResult=coral.AttributeList()
00742 qResult.extend('normdataid','unsigned long long')
00743 qHandle.defineOutput(qResult)
00744 qHandle.setCondition(qConditionStr,qCondition)
00745 cursor=qHandle.execute()
00746 while cursor.next():
00747 normdataid=cursor.currentRow()['normdataid'].data()
00748 luminormids.append(normdataid)
00749 except :
00750 del qHandle
00751 raise
00752 del qHandle
00753 if len(luminormids) !=0:return max(luminormids)
00754 return None
00755
00756 def guessnormIdByName(schema,normname):
00757 '''
00758 get norm dataids by name, if there are duplicates, pick max(dataid).Bypass full version lookups
00759 select luminorm.data_id from luminorm where name=:normname
00760 result luminormdataid
00761 '''
00762 luminormids=[]
00763 qHandle=schema.newQuery()
00764 try:
00765 qHandle.addToTableList( nameDealer.entryTableName(nameDealer.luminormTableName()) )
00766 qHandle.addToTableList( nameDealer.luminormTableName() )
00767 qHandle.addToOutputList('DATA_ID','normdataid')
00768 qConditionStr='ENTRY_NAME=:normname '
00769 qCondition=coral.AttributeList()
00770 qCondition.extend('normname','string')
00771 qCondition['normname'].setData(normname)
00772 qResult=coral.AttributeList()
00773 qResult.extend('normdataid','unsigned long long')
00774 qHandle.defineOutput(qResult)
00775 qHandle.setCondition(qConditionStr,qCondition)
00776 cursor=qHandle.execute()
00777 while cursor.next():
00778 normdataid=cursor.currentRow()['normdataid'].data()
00779 luminormids.append(normdataid)
00780 except :
00781 del qHandle
00782 raise
00783 del qHandle
00784 if len(luminormids) !=0:return max(luminormids)
00785 return None
00786
00787
00788
00789 def dataentryIdByRun(schema,runnum,branchfilter):
00790 '''
00791 select el.entry_id,et.entry_id,eh.entry_id,el.revision_id,et.revision_id,eh.revision_id from lumidataentiries el,trgdataentries et,hltdataentries eh where el.name=et.name and et.name=eh.name and el.name=:entryname;
00792 check on entryrev
00793
00794 return [lumientryid,trgentryid,hltentryid]
00795 '''
00796 result=[]
00797 qHandle=schema.newQuery()
00798 try:
00799 qHandle.addToTableList(nameDealer.entryTableName( lumidataTableName() ))
00800 qHandle.addToTableList(nameDealer.entryTableName( trgdataTableName() ))
00801 qHandle.addToTableList(nameDealer.entryTableName( hltdataTableName() ))
00802 qHandle.addToOutputList(lumidataTableName()+'.ENTRY_ID','lumientryid')
00803 qHandle.addToOutputList(trgdataTableName()+'.ENTRY_ID','trgentryid')
00804 qHandle.addToOutputList(hltdataTableName()+'.ENTRY_ID','hltentryid')
00805 qConditionStr=lumidataTableName()+'.NAME='+trgdataTableName()+'.NAME AND '+trgdataTableName()+'.NAME='+hltdataTableName()+'.NAME AND '+lumidataTableName()+'.NAME=:runnumstr'
00806 qCondition=coral.AttributeList()
00807 qCondition.extend('runnumstr','string')
00808 qCondition['runnumstr'].setData(str(runnum))
00809 qResult=coral.AttributeList()
00810 qResult.extend('lumientryid','unsigned long long')
00811 qResult.extend('trgentryid','unsigned long long')
00812 qResult.extend('hltentryid','unsigned long long')
00813 qHandle.defineOutput(qResult)
00814 qHandle.setCondition(qConditionStr,qCondition)
00815 cursor=qHandle.execute()
00816 while cursor.next():
00817 lumientryid=cursor.currentRow()['lumientryid'].data()
00818 trgentryid=cursor.currentRow()['trgentryid'].data()
00819 hltentryid=cursor.currentRow()['hltentryid'].data()
00820 if lumientryid in branchfilter and trgentryid in branchfilter and hltentryid in branchfilter:
00821 result.extend([lumientryid,trgentryid,hltentryid])
00822 except:
00823 del qHandle
00824 raise
00825 del qHandle
00826 return result
00827
00828 def latestdataIdByEntry(schema,entryid,datatype,branchfilter):
00829 '''
00830 select l.data_id,rl.revision_id from lumidatatable l,lumirevisions rl where l.data_id=rl.data_id and l.entry_id=:entryid
00831 check revision_id is in branch
00832 '''
00833 dataids=[]
00834 datatablename=''
00835 revmaptablename=''
00836 if datatype=='lumi':
00837 datatablename=nameDealer.lumidataTableName()
00838 elif datatype=='trg':
00839 datatablename=nameDealer.trgdataTableName()
00840 elif dataytpe=='hlt':
00841 tablename=nameDealer.hltdataTableName()
00842 else:
00843 raise RunTimeError('datatype '+datatype+' is not supported')
00844 revmaptablename=nameDealer.revmapTableName(datatablename)
00845 qHandle=schema.newQuery()
00846 try:
00847 qHandle.addToTableList(revmaptablename)
00848 qHandle.addToTableList(datatablename)
00849 qHandle.addToOutputList('l.DATA_ID','dataid')
00850 qHandle.addToOutputList(revmaptablename+'.REVISION_ID','revisionid')
00851 qConditionStr=datatablename+'.DATA_ID='+revmaptablename+'.DATA_ID AND '+datatablename+'.ENTRY_ID=:entryid'
00852 qCondition=coral.AttributeList()
00853 qCondition.extend('entryid','unsigned long long')
00854 qResult=coral.AttributeList()
00855 qResult.extend('dataid','unsigned long long')
00856 qResult.extend('revisionid','unsigned long long')
00857 qHandle.defineOutput(qResult)
00858 qHandle.setCondition(qConditionStr,qCondition)
00859 cursor=qHandle.execute()
00860 while cursor.next():
00861 dataid=cursor.currentRow()['dataid'].data()
00862 revisionid=cursor.currentRow()['revisionid'].data()
00863 if revisionid in branchfilter:
00864 dataids.append(dataid)
00865 except:
00866 del qHandle
00867 raise
00868 del qHandle
00869 if len(dataids)!=0:return max(dataids)
00870 return None
00871
00872
00873
00874
00875 def addNormToBranch(schema,normname,amodetag,norm1,egev1,optionalnormdata,branchinfo):
00876 '''
00877 input:
00878 branchinfo(normrevisionid,branchname)
00879 optionalnormdata {'norm2':norm2,'egev2':egev2}
00880 output:
00881 (revision_id,entry_id,data_id)
00882 '''
00883 print 'branchinfo ',branchinfo
00884 norm2=None
00885 if optionalnormdata.has_key('norm2'):
00886 norm2=optionalnormdata['norm2']
00887 egev2=None
00888 if optionalnormdata.has_key('egev2'):
00889 egev2=optionalnormdata['egev2']
00890 try:
00891 entry_id=revisionDML.entryInBranch(schema,nameDealer.luminormTableName(),normname,branchinfo[1])
00892 if entry_id is None:
00893 (revision_id,entry_id,data_id)=revisionDML.bookNewEntry(schema,nameDealer.luminormTableName())
00894 entryinfo=(revision_id,entry_id,normname,data_id)
00895 revisionDML.addEntry(schema,nameDealer.luminormTableName(),entryinfo,branchinfo)
00896 else:
00897 (revision_id,data_id)=revisionDML.bookNewRevision( schema,nameDealer.luminormTableName() )
00898 revisionDML.addRevision(schema,nameDealer.luminormTableName(),(revision_id,data_id),branchinfo)
00899 tabrowDefDict={'DATA_ID':'unsigned long long','ENTRY_ID':'unsigned long long','ENTRY_NAME':'string','AMODETAG':'string','NORM_1':'float','EGEV_1':'unsigned int','NORM_2':'float','EGEV_2':'unsigned int'}
00900 tabrowValueDict={'DATA_ID':data_id,'ENTRY_ID':entry_id,'ENTRY_NAME':normname,'AMODETAG':amodetag,'NORM_1':norm1,'EGEV_1':egev1,'NORM_2':norm2,'EGEV_2':egev2}
00901 db=dbUtil.dbUtil(schema)
00902 db.insertOneRow(nameDealer.luminormTableName(),tabrowDefDict,tabrowValueDict)
00903 return (revision_id,entry_id,data_id)
00904 except :
00905 raise
00906 def addLumiRunDataToBranch(schema,runnumber,lumirundata,branchinfo):
00907 '''
00908 input:
00909 lumirundata [datasource]
00910 branchinfo (branch_id,branch_name)
00911 output:
00912 (revision_id,entry_id,data_id)
00913 '''
00914 try:
00915 datasource=lumirundata[0]
00916 entry_id=revisionDML.entryInBranch(schema,nameDealer.lumidataTableName(),str(runnumber),branchinfo[1])
00917 if entry_id is None:
00918 (revision_id,entry_id,data_id)=revisionDML.bookNewEntry(schema,nameDealer.lumidataTableName())
00919 entryinfo=(revision_id,entry_id,str(runnumber),data_id)
00920 revisionDML.addEntry(schema,nameDealer.lumidataTableName(),entryinfo,branchinfo)
00921 else:
00922 (revision_id,data_id)=revisionDML.bookNewRevision( schema,nameDealer.lumidataTableName() )
00923 revisionDML.addRevision(schema,nameDealer.lumidataTableName(),(revision_id,data_id),branchinfo)
00924 tabrowDefDict={'DATA_ID':'unsigned long long','ENTRY_ID':'unsigned long long','ENTRY_NAME':'string','RUNNUM':'unsigned int','SOURCE':'string'}
00925 tabrowValueDict={'DATA_ID':data_id,'ENTRY_ID':entry_id,'ENTRY_NAME':str(runnumber),'RUNNUM':int(runnumber),'SOURCE':datasource}
00926 db=dbUtil.dbUtil(schema)
00927 db.insertOneRow(nameDealer.lumidataTableName(),tabrowDefDict,tabrowValueDict)
00928 return (revision_id,entry_id,data_id)
00929 except :
00930 raise
00931 def addTrgRunDataToBranch(schema,runnumber,trgrundata,branchinfo):
00932 '''
00933 input:
00934 trgrundata [datasource(0),bitzeroname(1),bitnameclob(2)]
00935 bitnames clob, bitnames separated by ','
00936 output:
00937 (revision_id,entry_id,data_id)
00938 '''
00939 try:
00940 datasource=trgrundata[0]
00941 bitzeroname=trgrundata[1]
00942 bitnames=trgrundata[2]
00943 entry_id=revisionDML.entryInBranch(schema,nameDealer.trgdataTableName(),str(runnumber),branchinfo[1])
00944 if entry_id is None:
00945 (revision_id,entry_id,data_id)=revisionDML.bookNewEntry(schema,nameDealer.trgdataTableName())
00946 entryinfo=(revision_id,entry_id,str(runnumber),data_id)
00947 revisionDML.addEntry(schema,nameDealer.trgdataTableName(),entryinfo,branchinfo)
00948 else:
00949 (revision_id,data_id)=revisionDML.bookNewRevision( schema,nameDealer.trgdataTableName() )
00950 revisionDML.addRevision(schema,nameDealer.trgdataTableName(),(revision_id,data_id),branchinfo)
00951 tabrowDefDict={'DATA_ID':'unsigned long long','ENTRY_ID':'unsigned long long','ENTRY_NAME':'string','SOURCE':'string','RUNNUM':'unsigned int','BITZERONAME':'string','BITNAMECLOB':'string'}
00952 tabrowValueDict={'DATA_ID':data_id,'ENTRY_ID':entry_id,'ENTRY_NAME':str(runnumber),'SOURCE':datasource,'RUNNUM':int(runnumber),'BITZERONAME':bitzeroname,'BITNAMECLOB':bitnames}
00953 db=dbUtil.dbUtil(schema)
00954 db.insertOneRow(nameDealer.trgdataTableName(),tabrowDefDict,tabrowValueDict)
00955 return (revision_id,entry_id,data_id)
00956 except :
00957 raise
00958 def addHLTRunDataToBranch(schema,runnumber,hltrundata,branchinfo):
00959 '''
00960 input:
00961 hltrundata [pathnameclob(0),datasource(1)]
00962 output:
00963 (revision_id,entry_id,data_id)
00964 '''
00965 try:
00966 pathnames=hltrundata[0]
00967 datasource=hltrundata[1]
00968 npath=len(pathnames.split(','))
00969 entry_id=revisionDML.entryInBranch(schema,nameDealer.lumidataTableName(),str(runnumber),branchinfo[1])
00970 if entry_id is None:
00971 (revision_id,entry_id,data_id)=revisionDML.bookNewEntry(schema,nameDealer.hltdataTableName())
00972 entryinfo=(revision_id,entry_id,str(runnumber),data_id)
00973 revisionDML.addEntry(schema,nameDealer.hltdataTableName(),entryinfo,branchinfo)
00974 else:
00975 (revision_id,data_id)=revisionDML.bookNewRevision( schema,nameDealer.hltdataTableName() )
00976 revisionDML.addRevision(schema,nameDealer.hltdataTableName(),(revision_id,data_id),branchinfo)
00977 tabrowDefDict={'DATA_ID':'unsigned long long','ENTRY_ID':'unsigned long long','ENTRY_NAME':'string','RUNNUM':'unsigned int','SOURCE':'string','NPATH':'unsigned int','PATHNAMECLOB':'string'}
00978 tabrowValueDict={'DATA_ID':data_id,'ENTRY_ID':entry_id,'ENTRY_NAME':str(runnumber),'RUNNUM':int(runnumber),'SOURCE':datasource,'NPATH':npath,'PATHNAMECLOB':pathnames}
00979 db=dbUtil.dbUtil(schema)
00980 db.insertOneRow(nameDealer.hltdataTableName(),tabrowDefDict,tabrowValueDict)
00981 return (revision_id,entry_id,data_id)
00982 except :
00983 raise
00984
00985 def insertRunSummaryData(schema,runnumber,runsummarydata,complementalOnly=False):
00986 '''
00987 input:
00988 runsummarydata [l1key,amodetag,egev,sequence,hltkey,fillnum,starttime,stoptime]
00989 output:
00990 '''
00991 l1key=runsummarydata[0]
00992 amodetag=runsummarydata[1]
00993 egev=runsummarydata[2]
00994 hltkey=''
00995 fillnum=0
00996 sequence=''
00997 starttime=''
00998 stoptime=''
00999 if not complementalOnly:
01000 sequence=runsummarydata[3]
01001 hltkey=runsummarydata[4]
01002 fillnum=runsummarydata[5]
01003 starttime=runsummarydata[6]
01004 stoptime=runsummarydata[7]
01005 try:
01006 if not complementalOnly:
01007 tabrowDefDict={'RUNNUM':'unsigned int','L1KEY':'string','AMODETAG':'string','EGEV':'unsigned int','SEQUENCE':'string','HLTKEY':'string','FILLNUM':'unsigned int','STARTTIME':'time stamp','STOPTIME':'time stamp'}
01008 tabrowValueDict={'RUNNUM':int(runnumber),'L1KEY':l1key,'AMODETAG':amodetag,'EGEV':int(egev),'SEQUENCE':sequence,'HLTKEY':hltkey,'FILLNUM':int(fillnum),'STARTTIME':starttime,'STOPTIME':stoptime}
01009 db=dbUtil.dbUtil(schema)
01010 db.insertOneRow(nameDealer.cmsrunsummaryTableName(),tabrowDefDict,tabrowValueDict)
01011 else:
01012 setClause='L1KEY=:l1key,AMODETAG=:amodetag,EGEV=:egev'
01013 updateCondition='RUNNUM=:runnum'
01014 inputData=coral.AttributeList()
01015 inputData.extend('l1key','string')
01016 inputData.extend('amodetag','string')
01017 inputData.extend('egev','unsigned int')
01018 inputData.extend('runnum','unsigned int')
01019 inputData['l1key'].setData(l1key)
01020 inputData['amodetag'].setData(amodetag)
01021 inputData['egev'].setData(int(egev))
01022 inputData['runnum'].setData(int(runnumber))
01023 db=dbUtil.dbUtil(schema)
01024 db.singleUpdate(nameDealer.cmsrunsummaryTableName(),setClause,updateCondition,inputData)
01025 except :
01026 raise
01027 def insertTrgHltMap(schema,hltkey,trghltmap):
01028 '''
01029 input:
01030 trghltmap {hltpath:l1seed}
01031 output:
01032 '''
01033 hltkeyExists=False
01034 nrows=0
01035 try:
01036 kQueryBindList=coral.AttributeList()
01037 kQueryBindList.extend('hltkey','string')
01038 kQuery=schema.newQuery()
01039 kQuery.addToTableList(nameDealer.trghltMapTableName())
01040 kQuery.setCondition('HLTKEY=:hltkey',kQueryBindList)
01041 kQueryBindList['hltkey'].setData(hltkey)
01042 kResult=kQuery.execute()
01043 while kResult.next():
01044 hltkeyExists=True
01045 if not hltkeyExists:
01046 bulkvalues=[]
01047 trghltDefDict=[('HLTKEY','string'),('HLTPATHNAME','string'),('L1SEED','string')]
01048 for hltpath,l1seed in trghltmap.items():
01049 bulkvalues.append([('HLTKEY',hltkey),('HLTPATHNAME',hltpath),('L1SEED',l1seed)])
01050 db=dbUtil.dbUtil(schema)
01051 db.bulkInsert(nameDealer.trghltMapTableName(),trghltDefDict,bulkvalues)
01052 nrows=len(bulkvalues)
01053 return nrows
01054 except :
01055 print 'error in insertTrgHltMap '
01056 raise
01057 def bulkInsertTrgLSData(session,runnumber,data_id,trglsdata,bulksize=500):
01058 '''
01059 insert trg per-LS data for given run and data_id, this operation can be split in transaction chuncks
01060 input:
01061 trglsdata {cmslsnum:[deadtime,bitzerocount,bitzeroprescale,trgcountBlob,trgprescaleBlob]}
01062 result nrows inserted
01063 if nrows==0, then this insertion failed
01064 '''
01065 print 'total number of trg rows ',len(trglsdata)
01066 lstrgDefDict=[('DATA_ID','unsigned long long'),('RUNNUM','unsigned int'),('CMSLSNUM','unsigned int'),('DEADTIMECOUNT','unsigned long long'),('BITZEROCOUNT','unsigned int'),('BITZEROPRESCALE','unsigned int'),('PRESCALEBLOB','blob'),('TRGCOUNTBLOB','blob')]
01067 committedrows=0
01068 nrows=0
01069 bulkvalues=[]
01070 try:
01071 for cmslsnum,perlstrg in trglsdata.items():
01072 deadtimecount=perlstrg[0]
01073 bitzerocount=perlstrg[1]
01074 bitzeroprescale=perlstrg[2]
01075 trgcountblob=perlstrg[3]
01076 trgprescaleblob=perlstrg[4]
01077 bulkvalues.append([('DATA_ID',data_id),('RUNNUM',runnumber),('CMSLSNUM',cmslsnum),('DEADTIMECOUNT',deadtimecount),('BITZEROCOUNT',bitzerocount),('BITZEROPRESCALE',bitzeroprescale),('PRESCALEBLOB',trgprescaleblob),('TRGCOUNTBLOB',trgcountblob)])
01078 nrows+=1
01079 committedrows+=1
01080 if nrows==bulksize:
01081 print 'committing trg in LS chunck ',nrows
01082 db=dbUtil.dbUtil(session.nominalSchema())
01083 session.transaction().start(False)
01084 db.bulkInsert(nameDealer.lstrgTableName(),lstrgDefDict,bulkvalues)
01085 session.transaction().commit()
01086 nrows=0
01087 bulkvalues=[]
01088 elif committedrows==len(trglsdata):
01089 print 'committing trg at the end '
01090 db=dbUtil.dbUtil(session.nominalSchema())
01091 session.transaction().start(False)
01092 db.bulkInsert(nameDealer.lstrgTableName(),lstrgDefDict,bulkvalues)
01093 session.transaction().commit()
01094 except :
01095 print 'error in bulkInsertTrgLSData'
01096 raise
01097 def bulkInsertHltLSData(session,runnumber,data_id,hltlsdata,bulksize=500):
01098 '''
01099 input:
01100 hltlsdata {cmslsnum:[inputcountBlob,acceptcountBlob,prescaleBlob]}
01101 '''
01102 print 'total number of hlt rows ',len(hltlsdata)
01103 lshltDefDict=[('DATA_ID','unsigned long long'),('RUNNUM','unsigned int'),('CMSLSNUM','unsigned int'),('PRESCALEBLOB','blob'),('HLTCOUNTBLOB','blob'),('HLTACCEPTBLOB','blob')]
01104 committedrows=0
01105 nrows=0
01106 bulkvalues=[]
01107 try:
01108 for cmslsnum,perlshlt in hltlsdata.items():
01109 inputcountblob=perlshlt[0]
01110 acceptcountblob=perlshlt[1]
01111 prescaleblob=perlshlt[2]
01112 bulkvalues.append([('DATA_ID',data_id),('RUNNUM',runnumber),('CMSLSNUM',cmslsnum),('PRESCALEBLOB',prescaleblob),('HLTCOUNTBLOB',inputcountblob),('HLTACCEPTBLOB',acceptcountblob)])
01113
01114 nrows+=1
01115 committedrows+=1
01116 if nrows==bulksize:
01117 print 'committing hlt in LS chunck ',nrows
01118 db=dbUtil.dbUtil(session.nominalSchema())
01119 session.transaction().start(False)
01120 db.bulkInsert(nameDealer.lshltTableName(),lshltDefDict,bulkvalues)
01121 session.transaction().commit()
01122 nrows=0
01123 bulkvalues=[]
01124 elif committedrows==len(hltlsdata):
01125 print 'committing hlt at the end '
01126 db=dbUtil.dbUtil(session.nominalSchema())
01127 session.transaction().start(False)
01128 db.bulkInsert(nameDealer.lshltTableName(),lshltDefDict,bulkvalues)
01129 session.transaction().commit()
01130 except :
01131 print 'error in bulkInsertHltLSData'
01132 raise
01133
01134 def bulkInsertLumiLSSummary(session,runnumber,data_id,lumilsdata,bulksize=500):
01135 '''
01136 input:
01137 lumilsdata {lumilsnum:[cmslsnum,instlumi,instlumierror,instlumiquality,beamstatus,beamenergy,numorbit,startorbit,cmsbxindexblob,beam1intensity,beam2intensity,bxlumivalue_occ1,bxlumierror_occ1,bxlumiquality_occ1,bxlumivalue_occ2,bxlumierror_occ2,bxlumiquality_occ2,bxlumivalue_et,bxlumierror_et,bxlumiquality_et]}
01138 '''
01139 lslumiDefDict=[('DATA_ID','unsigned long long'),('RUNNUM','unsigned int'),('LUMILSNUM','unsigned int'),('CMSLSNUM','unsigned int'),('INSTLUMI','float'),('INSTLUMIERROR','float'),('INSTLUMIQUALITY','short'),('BEAMSTATUS','string'),('BEAMENERGY','float'),('NUMORBIT','unsigned int'),('STARTORBIT','unsigned int'),('CMSBXINDEXBLOB','blob'),('BEAMINTENSITYBLOB_1','blob'),('BEAMINTENSITYBLOB_2','blob'),('BXLUMIVALUE_OCC1','blob'),('BXLUMIERROR_OCC1','blob'),('BXLUMIQUALITY_OCC1','blob'),('BXLUMIVALUE_OCC2','blob'),('BXLUMIERROR_OCC2','blob'),('BXLUMIQUALITY_OCC2','blob'),('BXLUMIVALUE_ET','blob'),('BXLUMIERROR_ET','blob'),('BXLUMIQUALITY_ET','blob')]
01140 print 'total number of lumi rows ',len(lumilsdata)
01141 try:
01142 committedrows=0
01143 nrows=0
01144 bulkvalues=[]
01145 for lumilsnum,perlslumi in lumilsdata.items():
01146 cmslsnum=perlslumi[0]
01147 instlumi=perlslumi[1]
01148 instlumierror=perlslumi[2]
01149 instlumiquality=perlslumi[3]
01150 beamstatus=perlslumi[4]
01151 beamenergy=perlslumi[5]
01152 numorbit=perlslumi[6]
01153 startorbit=perlslumi[7]
01154 cmsbxindexindexblob=perlslumi[8]
01155 beam1intensity=perlslumi[9]
01156 beam2intensity=perlslumi[10]
01157 bxlumivalue_occ1=perlslumi[11]
01158 bxlumierror_occ1=perlslumi[12]
01159 bxlumiquality_occ1=perlslumi[13]
01160 bxlumivalue_occ2=perlslumi[14]
01161 bxlumierror_occ2=perlslumi[15]
01162 bxlumiquality_occ2=perlslumi[16]
01163 bxlumivalue_et=perlslumi[17]
01164 bxlumierror_et=perlslumi[18]
01165 bxlumiquality_et=perlslumi[19]
01166 bulkvalues.append([('DATA_ID',data_id),('RUNNUM',runnumber),('LUMILSNUM',lumilsnum),('CMSLSNUM',cmslsnum),('INSTLUMI',instlumi),('INSTLUMIERROR',instlumierror),('INSTLUMIQUALITY',instlumiquality),('BEAMSTATUS',beamstatus),('BEAMENERGY',beamenergy),('NUMORBIT',numorbit),('STARTORBIT',startorbit),('CMSBXINDEXBLOB',cmsbxindexindexblob),('BEAMINTENSITYBLOB_1',beam1intensity),('BEAMINTENSITYBLOB_2',beam2intensity),('BXLUMIVALUE_OCC1',bxlumivalue_occ1),('BXLUMIERROR_OCC1',bxlumierror_occ1),('BXLUMIQUALITY_OCC1',bxlumiquality_occ1),('BXLUMIVALUE_OCC2',bxlumivalue_occ2),('BXLUMIERROR_OCC2',bxlumierror_occ2),('BXLUMIQUALITY_OCC2',bxlumiquality_occ2),('BXLUMIVALUE_ET',bxlumivalue_et),('BXLUMIERROR_ET',bxlumierror_et),('BXLUMIQUALITY_ET',bxlumiquality_et)])
01167 nrows+=1
01168 committedrows+=1
01169 if nrows==bulksize:
01170 print 'committing lumi in LS chunck ',nrows
01171 db=dbUtil.dbUtil(session.nominalSchema())
01172 session.transaction().start(False)
01173 db.bulkInsert(nameDealer.lumisummaryv2TableName(),lslumiDefDict,bulkvalues)
01174 session.transaction().commit()
01175 nrows=0
01176 bulkvalues=[]
01177 elif committedrows==len(lumilsdata):
01178 print 'committing lumi at the end '
01179 db=dbUtil.dbUtil(session.nominalSchema())
01180 session.transaction().start(False)
01181 db.bulkInsert(nameDealer.lumisummaryv2TableName(),lslumiDefDict,bulkvalues)
01182 session.transaction().commit()
01183 except :
01184 raise
01185
01186
01187
01188
01189
01190
01191
01192
01193
01194
01195
01196
01197
01198
01199
01200
01201
01202
01203
01204
01205
01206
01207
01208
01209
01210
01211
01212
01213
01214
01215
01216
01217
01218
01219
01220
01221
01222
01223
01224
01225
01226
01227
01228
01229
01230
01231
01232
01233
01234
01235
01236
01237
01238
01239
01240
01241
01242
01243
01244
01245
01246
01247
01248
01249
01250 if __name__ == "__main__":
01251 import sessionManager
01252 import lumidbDDL,revisionDML,generateDummyData
01253
01254 myconstr='oracle://devdb10/cms_xiezhen_dev'
01255 svc=sessionManager.sessionManager(myconstr,authpath='/afs/cern.ch/user/x/xiezhen',debugON=False)
01256 session=svc.openSession(isReadOnly=False,cpp2sqltype=[('unsigned int','NUMBER(10)'),('unsigned long long','NUMBER(20)')])
01257 schema=session.nominalSchema()
01258 session.transaction().start(False)
01259 lumidbDDL.dropTables(schema,nameDealer.schemaV2Tables())
01260 lumidbDDL.dropTables(schema,nameDealer.commonTables())
01261 tables=lumidbDDL.createTables(schema)
01262 try:
01263
01264 trunkinfo=revisionDML.createBranch(schema,'TRUNK',None,comment='main')
01265 print trunkinfo
01266 datainfo=revisionDML.createBranch(schema,'DATA','TRUNK',comment='hold data')
01267 print datainfo
01268 norminfo=revisionDML.createBranch(schema,'NORM','TRUNK',comment='hold normalization factor')
01269 print norminfo
01270 except:
01271 raise
01272
01273 (normbranchid,normbranchparent)=revisionDML.branchInfoByName(schema,'NORM')
01274 normbranchinfo=(normbranchid,'NORM')
01275 addNormToBranch(schema,'pp7TeV','PROTPHYS',6370.0,3500,{},normbranchinfo)
01276 addNormToBranch(schema,'hi7TeV','HIPHYS',2.38,3500,{},normbranchinfo)
01277 (branchid,branchparent)=revisionDML.branchInfoByName(schema,'DATA')
01278 branchinfo=(branchid,'DATA')
01279 for runnum in [1200,1211,1222,1233,1345]:
01280 runsummarydata=generateDummyData.runsummary(schema,'PROTPHYS',3500)
01281 insertRunSummaryData(schema,runnum,runsummarydata)
01282 hlttrgmap=generateDummyData.hlttrgmap(schema)
01283 insertTrgHltMap(schema,hlttrgmap[0],hlttrgmap[1])
01284 lumidummydata=generateDummyData.lumiSummary(schema,20)
01285 lumirundata=[lumidummydata[0]]
01286 lumilsdata=lumidummydata[1]
01287 (lumirevid,lumientryid,lumidataid)=addLumiRunDataToBranch(schema,runnum,lumirundata,branchinfo)
01288 insertLumiLSSummary(schema,runnum,lumidataid,lumilsdata)
01289 trgdata=generateDummyData.trg(schema,20)
01290 trgrundata=[trgdata[0],trgdata[1],trgdata[2]]
01291 trglsdata=trgdata[3]
01292 (trgrevid,trgentryid,trgdataid)=addTrgRunDataToBranch(schema,runnum,trgrundata,branchinfo)
01293 insertTrgLSData(schema,runnum,trgdataid,trglsdata)
01294 hltdata=generateDummyData.hlt(schema,20)
01295 hltrundata=[hltdata[0],hltdata[1]]
01296 hltlsdata=hltdata[2]
01297 (hltrevid,hltentryid,hltdataid)=addHLTRunDataToBranch(schema,runnum,hltrundata,branchinfo)
01298 insertHltLSData(schema,runnum,hltdataid,hltlsdata)
01299 session.transaction().commit()
01300 print 'test reading'
01301 session.transaction().start(True)
01302 print '===inspecting NORM by name==='
01303 normrevlist=revisionDML.revisionsInBranchName(schema,'NORM')
01304 luminormentry_id=revisionDML.entryInBranch(schema,nameDealer.luminormTableName(),'pp7TeV','NORM')
01305 latestNorms=revisionDML.latestDataRevisionOfEntry(schema,nameDealer.luminormTableName(),luminormentry_id,normrevlist)
01306 print 'latest norm data_id for pp7TeV ',latestNorms
01307
01308 print '===inspecting DATA branch==='
01309 print revisionDML.branchType(schema,'DATA')
01310 revlist=revisionDML.revisionsInBranchName(schema,'DATA')
01311 print revlist
01312 lumientry_id=revisionDML.entryInBranch(schema,nameDealer.lumidataTableName(),'1211','DATA')
01313 latestrevision=revisionDML.latestDataRevisionOfEntry(schema,nameDealer.lumidataTableName(),lumientry_id,revlist)
01314 print 'latest lumi data_id for run 1211 ',latestrevision
01315 lumientry_id=revisionDML.entryInBranch(schema,nameDealer.lumidataTableName(),'1222','DATA')
01316 latestrevision=revisionDML.latestDataRevisionOfEntry(schema,nameDealer.lumidataTableName(),lumientry_id,revlist)
01317 print 'latest lumi data_id for run 1222 ',latestrevision
01318 trgentry_id=revisionDML.entryInBranch(schema,nameDealer.trgdataTableName(),'1222','DATA')
01319 latestrevision=revisionDML.latestDataRevisionOfEntry(schema,nameDealer.trgdataTableName(),trgentry_id,revlist)
01320 print 'latest trg data_id for run 1222 ',latestrevision
01321 session.transaction().commit()
01322 print 'tagging data so far as data_orig'
01323 session.transaction().start(False)
01324 (revisionid,parentid,parentname)=revisionDML.createBranch(schema,'data_orig','DATA',comment='tag of 2010data')
01325 session.transaction().commit()
01326 session.transaction().start(True)
01327 print revisionDML.branchType(schema,'data_orig')
01328 revlist=revisionDML.revisionsInTag(schema,revisionid,branchinfo[0])
01329 print revlist
01330 session.transaction().commit()
01331 session.transaction().start(False)
01332 for runnum in [1200,1222]:
01333 print 'revising lumidata for run ',runnum
01334 lumidummydata=generateDummyData.lumiSummary(schema,20)
01335 lumirundata=[lumidummydata[0]]
01336 lumilsdata=lumidummydata[1]
01337 (lumirevid,lumientryid,lumidataid)=addLumiRunDataToBranch(schema,runnum,lumirundata,branchinfo)
01338 insertLumiLSSummary(schema,runnum,lumidataid,lumilsdata)
01339 revlist=revisionDML.revisionsInTag(schema,revisionid,branchinfo[0])
01340 print 'revisions in branch DATA',revisionDML.revisionsInBranch(schema,branchinfo[0])
01341 session.transaction().commit()
01342
01343
01344 print '===test reading==='
01345 session.transaction().start(True)
01346
01347
01348
01349
01350
01351
01352
01353
01354
01355
01356
01357
01358
01359
01360
01361
01362
01363
01364
01365
01366
01367 print 'trg run, trgdataid ',trgdataid
01368 print trgRunById(schema,trgdataid)
01369
01370
01371
01372
01373
01374
01375 session.transaction().commit()
01376 del session
1.7.3