CMS 3D CMS Logo
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Pages
lumiQueryAPI.py
Go to the documentation of this file.
1 import os
2 import coral,datetime
3 from RecoLuminosity.LumiDB import nameDealer,lumiTime,CommonUtil
4 import array
5 from RecoLuminosity.LumiDB import argparse, nameDealer, selectionParser, hltTrgSeedMapper, \
6  connectstrParser, cacheconfigParser, tablePrinter, csvReporter, csvSelectionParser
7 from RecoLuminosity.LumiDB.wordWrappers import wrap_always, wrap_onspace, wrap_onspace_strict
8 from pprint import pprint, pformat
9 
10 '''
11 This module defines lowlevel SQL query API for lumiDB
12 We do not like range queries so far because of performance of range scan.Use only necessary.
13 The principle is to query by runnumber and per each coral queryhandle
14 Try reuse db session/transaction and just renew query handle each time to reduce metadata queries.
15 Avoid unnecessary explicit order by
16 Do not handle transaction in here.
17 Do not do explicit del queryhandle in here.
18 Note: all the returned dict format are not sorted by itself.Sort it outside if needed.
19 '''
20 ###==============temporarilly here======###
21 
22 class ParametersObject (object):
23 
24  def __init__ (self):
25  self.norm = 1.0
26  self.lumiversion = '0001'
27  self.NBX = 3564 # number beam crossings
28  self.rotationRate = 11245.613 # for 3.5 TeV Beam energy
29  self.normFactor = 6.37
30  self.beammode = '' #possible choices stable, quiet, either
31  self.verbose = False
32  self.noWarnings = False
33  self.lumischema = 'CMS_LUMI_PROD'
34  #self.lumidb = 'oracle://cms_orcoff_prod/cms_lumi_prod'
35  self.lumisummaryname = 'LUMISUMMARY'
36  self.lumidetailname = 'LUMIDETAIL'
37  self.lumiXing = False
38  self.xingMinLum = 1.0e-4
39  self.xingIndex = 5
40  self.minBiasXsec = 71300 # unit: microbarn
41  self.pileupHistName = 'pileup'
42  self.maxPileupBin = 10
43  self.calculateTimeParameters()
44 
45  def calculateTimeParameters (self):
46  '''Given the rotation rate, calculate lumi section length and
47  rotation time. This should be called if rotationRate is
48  updated.'''
49  self.rotationTime = 1 / self.rotationRate
50  self.lumiSectionLen = 2**18 * self.rotationTime
51 
52  def defaultfrontierConfigString (self):
53  return '''<frontier-connect><proxy url = "http://cmst0frontier.cern.ch:3128"/><proxy url = "http://cmst0frontier.cern.ch:3128"/><proxy url = "http://cmst0frontier1.cern.ch:3128"/><proxy url = "http://cmst0frontier2.cern.ch:3128"/><server url = "http://cmsfrontier.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier1.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier2.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier3.cern.ch:8000/FrontierInt"/><server url = "http://cmsfrontier4.cern.ch:8000/FrontierInt"/></frontier-connect>'''
54 
55 def lslengthsec (numorbit, numbx):
56  #print numorbit, numbx
57  l = numorbit * numbx * 25.0e-09
58  return l
59 
60 def lsBylsLumi (deadtable):
61  """
62  input: {lsnum:[deadtime, instlumi, bit_0, norbits,prescale...]}
63  output: {lsnum:[instlumi, recordedlumi...]}
64  """
65  result = {}
66  for myls, deadArray in deadtable.items():
67  lstime = lslengthsec (deadArray[3], 3564)
68  instlumi = deadArray[1] * lstime
69  if float( deadArray[2] ) == 0.0:
70  deadfrac = 1.0
71  else:
72  deadfrac = float (deadArray[0]) / (float (deadArray[2])*float(deadArray[4]))
73  recordedLumi = instlumi * (1.0 - deadfrac)
74  myLsList = [instlumi, recordedLumi]
75  #print myls,instlumi,recordedLumi,lstime,deadfrac
76  if len (deadArray) > 5:
77  myLsList.extend (deadArray[5:])
78  result[myls] = myLsList
79  return result
80 
81 
82 def deliveredLumiForRange (dbsession, parameters, inputRange):
83  '''Takes either single run as a string or dictionary of run ranges'''
84  lumidata = []
85  # is this a single string?
86  if isinstance (inputRange, str):
87  lumidata.append( deliveredLumiForRun (dbsession, parameters, inputRange) )
88  else:
89  # if not, it's one of these dictionary things
90  for run in sorted( inputRange.runs() ):
91  if parameters.verbose:
92  print "run", run
93  lumidata.append( deliveredLumiForRun (dbsession, parameters, run) )
94  #print lumidata
95  return lumidata
96 
97 
98 def recordedLumiForRange (dbsession, parameters, inputRange):
99  '''Takes either single run as a string or dictionary of run ranges'''
100  lumidata = []
101  # is this a single string?
102  if isinstance (inputRange, str):
103  lumiDataPiece = recordedLumiForRun (dbsession, parameters, inputRange)
104  if parameters.lumiXing:
105  # get the xing information for the run
106  xingLumiDict = xingLuminosityForRun (dbsession, inputRange,
107  parameters)
108  mergeXingLumi (lumiDataPiece, xingLumiDict)
109  lumidata.append (lumiDataPiece)
110 
111  else:
112  # we want to collapse the lists so that every run is considered once.
113  runLsDict = {}
114  maxLumiSectionDict = {}
115  for (run, lslist) in sorted (inputRange.runsandls().items() ):
116  if(len(lslist)!=0):
117  maxLumiSectionDict[run] = max ( max (lslist),
118  maxLumiSectionDict.get(run,0) )
119  runLsDict.setdefault (run, []).append (lslist)
120  for run, metaLsList in sorted (runLsDict.iteritems()):
121  if parameters.verbose:
122  print "run", run
123  runLumiData = []
124  for lslist in metaLsList:
125  runLumiData.append( recordedLumiForRun (dbsession, parameters,
126  run, lslist) )
127  if parameters.lumiXing:
128  # get the xing information once for the whole run
129  xingLumiDict = xingLuminosityForRun (dbsession, run,
130  parameters,
131  maxLumiSection = \
132  maxLumiSectionDict[run])
133  # merge it with every piece of lumi data for this run
134  for lumiDataPiece in runLumiData:
135  mergeXingLumi (lumiDataPiece, xingLumiDict)
136  lumidata.append (lumiDataPiece)
137  else:
138  lumidata.extend( runLumiData )
139  return lumidata
140 
141 
142 
143 def deliveredLumiForRun (dbsession, parameters, runnum):
144  """
145  select sum (INSTLUMI), count (INSTLUMI) from lumisummary where runnum = 124025 and lumiversion = '0001';
146  select INSTLUMI,NUMORBIT from lumisummary where runnum = 124025 and lumiversion = '0001'
147  query result unit E27cm^-2 (= 1 / mb)"""
148  #if parameters.verbose:
149  # print 'deliveredLumiForRun : norm : ', parameters.norm, ' : run : ', runnum
150  #output ['run', 'totalls', 'delivered', 'beammode']
151  delivered = 0.0
152  totalls = 0
153  try:
154  conditionstring="RUNNUM = :runnum AND LUMIVERSION = :lumiversion"
155  dbsession.transaction().start (True)
156  schema = dbsession.nominalSchema()
157  query = schema.tableHandle (nameDealer.lumisummaryTableName()).newQuery()
158  #query.addToOutputList ("sum (INSTLUMI)", "totallumi")
159  #query.addToOutputList ("count (INSTLUMI)", "totalls")
160  query.addToOutputList("INSTLUMI",'instlumi')
161  query.addToOutputList ("NUMORBIT", "norbits")
162  queryBind = coral.AttributeList()
163  queryBind.extend ("runnum", "unsigned int")
164  queryBind.extend ("lumiversion", "string")
165  queryBind["runnum"].setData (int (runnum))
166  queryBind["lumiversion"].setData (parameters.lumiversion)
167  #print parameters.beammode
168  if len(parameters.beammode)!=0:
169  conditionstring=conditionstring+' and BEAMSTATUS=:beamstatus'
170  queryBind.extend('beamstatus','string')
171  queryBind['beamstatus'].setData(parameters.beammode)
172  result = coral.AttributeList()
173  result.extend ("instlumi", "float")
174  result.extend ("norbits", "unsigned int")
175  query.defineOutput (result)
176  query.setCondition (conditionstring,queryBind)
177  #query.limitReturnedRows (1)
178  #query.groupBy ('NUMORBIT')
179  cursor = query.execute()
180  while cursor.next():
181  instlumi = cursor.currentRow()['instlumi'].data()
182  norbits = cursor.currentRow()['norbits'].data()
183 
184  if instlumi is not None and norbits is not None:
185  lstime = lslengthsec(norbits, parameters.NBX)
186  delivered=delivered+instlumi*parameters.norm*lstime
187  totalls+=1
188  del query
189  dbsession.transaction().commit()
190  lumidata = []
191  if delivered == 0.0:
192  lumidata = [str (runnum), 'N/A', 'N/A', 'N/A']
193  else:
194  lumidata = [str (runnum), str (totalls), '%.3f'%delivered, parameters.beammode]
195  return lumidata
196  except Exception, e:
197  print str (e)
198  dbsession.transaction().rollback()
199  del dbsession
200 
201 def recordedLumiForRun (dbsession, parameters, runnum, lslist = None):
202  """
203  lslist = [] means take none in the db
204  lslist = None means to take all in the db
205  output: ['runnumber', 'trgtable{}', 'deadtable{}']
206  """
207  recorded = 0.0
208  lumidata = [] #[runnumber, trgtable, deadtable]
209  trgtable = {} #{hltpath:[l1seed, hltprescale, l1prescale]}
210  deadtable = {} #{lsnum:[deadtime, instlumi, bit_0, norbits,bitzero_prescale]}
211  lumidata.append (runnum)
212  lumidata.append (trgtable)
213  lumidata.append (deadtable)
214  collectedseeds = [] #[ (hltpath, l1seed)]
215  conditionstring='trghltmap.HLTKEY = cmsrunsummary.HLTKEY AND cmsrunsummary.RUNNUM = :runnumber'
216  try:
217  dbsession.transaction().start (True)
218  schema = dbsession.nominalSchema()
219  query = schema.newQuery()
220  query.addToTableList (nameDealer.cmsrunsummaryTableName(), 'cmsrunsummary')
221  query.addToTableList (nameDealer.trghltMapTableName(), 'trghltmap')#small table first
222  queryCondition = coral.AttributeList()
223  queryCondition.extend ("runnumber", "unsigned int")
224  queryCondition["runnumber"].setData (int (runnum))
225  query.setCondition (conditionstring,queryCondition)
226  query.addToOutputList ("trghltmap.HLTPATHNAME", "hltpathname")
227  query.addToOutputList ("trghltmap.L1SEED", "l1seed")
228  result = coral.AttributeList()
229  result.extend ("hltpathname", "string")
230  result.extend ("l1seed", "string")
231  query.defineOutput (result)
232  cursor = query.execute()
233  while cursor.next():
234  hltpathname = cursor.currentRow()["hltpathname"].data()
235  l1seed = cursor.currentRow()["l1seed"].data()
236  collectedseeds.append ( (hltpathname, l1seed))
237  #print 'collectedseeds ', collectedseeds
238  del query
239  dbsession.transaction().commit()
240  #loop over hltpath
241  for (hname, sname) in collectedseeds:
242  l1bitname = hltTrgSeedMapper.findUniqueSeed (hname, sname)
243  #print 'found unque seed ', hname, l1bitname
244  if l1bitname:
245  lumidata[1][hname] = []
246  lumidata[1][hname].append (l1bitname.replace ('\"', ''))
247  dbsession.transaction().start (True)
248  schema = dbsession.nominalSchema()
249  hltprescQuery = schema.tableHandle (nameDealer.hltTableName()).newQuery()
250  hltprescQuery.addToOutputList ("PATHNAME", "hltpath")
251  hltprescQuery.addToOutputList ("PRESCALE", "hltprescale")
252  hltprescCondition = coral.AttributeList()
253  hltprescCondition.extend ('runnumber', 'unsigned int')
254  hltprescCondition.extend ('cmslsnum', 'unsigned int')
255  hltprescCondition.extend ('inf', 'unsigned int')
256  hltprescResult = coral.AttributeList()
257  hltprescResult.extend ('hltpath', 'string')
258  hltprescResult.extend ('hltprescale', 'unsigned int')
259  hltprescQuery.defineOutput (hltprescResult)
260  hltprescCondition['runnumber'].setData (int (runnum))
261  hltprescCondition['cmslsnum'].setData (1)
262  hltprescCondition['inf'].setData (0)
263  hltprescQuery.setCondition ("RUNNUM = :runnumber and CMSLSNUM = :cmslsnum and PRESCALE != :inf",
264  hltprescCondition)
265  cursor = hltprescQuery.execute()
266  while cursor.next():
267  hltpath = cursor.currentRow()['hltpath'].data()
268  hltprescale = cursor.currentRow()['hltprescale'].data()
269  if lumidata[1].has_key (hltpath):
270  lumidata[1][hltpath].append (hltprescale)
271 
272  cursor.close()
273  del hltprescQuery
274  dbsession.transaction().commit()
275  dbsession.transaction().start (True)
276  schema = dbsession.nominalSchema()
277  query = schema.newQuery()
278  query.addToTableList (nameDealer.trgTableName(), 'trg')
279  query.addToTableList (nameDealer.lumisummaryTableName(), 'lumisummary')#small table first--right-most
280  queryCondition = coral.AttributeList()
281  queryCondition.extend ("runnumber", "unsigned int")
282  queryCondition.extend ("lumiversion", "string")
283  queryCondition["runnumber"].setData (int (runnum))
284  queryCondition["lumiversion"].setData (parameters.lumiversion)
285  conditionstring='lumisummary.RUNNUM =:runnumber and lumisummary.LUMIVERSION =:lumiversion AND lumisummary.CMSLSNUM=trg.CMSLSNUM and lumisummary.RUNNUM=trg.RUNNUM'
286  if len(parameters.beammode)!=0:
287  conditionstring=conditionstring+' and lumisummary.BEAMSTATUS=:beamstatus'
288  queryCondition.extend('beamstatus','string')
289  queryCondition['beamstatus'].setData(parameters.beammode)
290  query.setCondition(conditionstring,queryCondition)
291  query.addToOutputList ("lumisummary.CMSLSNUM", "cmsls")
292  query.addToOutputList ("lumisummary.INSTLUMI", "instlumi")
293  query.addToOutputList ("lumisummary.NUMORBIT", "norbits")
294  query.addToOutputList ("trg.TRGCOUNT", "trgcount")
295  query.addToOutputList ("trg.BITNAME", "bitname")
296  query.addToOutputList ("trg.DEADTIME", "trgdeadtime")
297  query.addToOutputList ("trg.PRESCALE", "trgprescale")
298  query.addToOutputList ("trg.BITNUM", "trgbitnum")
299 
300  result = coral.AttributeList()
301  result.extend ("cmsls", "unsigned int")
302  result.extend ("instlumi", "float")
303  result.extend ("norbits", "unsigned int")
304  result.extend ("trgcount", "unsigned int")
305  result.extend ("bitname", "string")
306  result.extend ("trgdeadtime", "unsigned long long")
307  result.extend ("trgprescale", "unsigned int")
308  result.extend ("trgbitnum", "unsigned int")
309  trgprescalemap = {}
310  query.defineOutput (result)
311  cursor = query.execute()
312  while cursor.next():
313  cmsls = cursor.currentRow()["cmsls"].data()
314  instlumi = cursor.currentRow()["instlumi"].data()*parameters.norm
315  norbits = cursor.currentRow()["norbits"].data()
316  trgcount = cursor.currentRow()["trgcount"].data()
317  trgbitname = cursor.currentRow()["bitname"].data()
318  trgdeadtime = cursor.currentRow()["trgdeadtime"].data()
319  trgprescale = cursor.currentRow()["trgprescale"].data()
320  trgbitnum = cursor.currentRow()["trgbitnum"].data()
321  if cmsls == 1:
322  if not trgprescalemap.has_key (trgbitname):
323  trgprescalemap[trgbitname] = trgprescale
324  if trgbitnum == 0:
325  if not deadtable.has_key (cmsls):
326  deadtable[cmsls] = []
327  deadtable[cmsls].append (trgdeadtime)
328  deadtable[cmsls].append (instlumi)
329  deadtable[cmsls].append (trgcount)
330  deadtable[cmsls].append (norbits)
331  deadtable[cmsls].append (trgprescale)
332  cursor.close()
333  del query
334  dbsession.transaction().commit()
335 
336  #
337  #consolidate results
338  #
339  #trgtable
340  #print 'trgprescalemap', trgprescalemap
341  #print lumidata[1]
342  for hpath, trgdataseq in lumidata[1].items():
343  bitn = trgdataseq[0]
344  if trgprescalemap.has_key (bitn) and len (trgdataseq) == 2:
345  lumidata[1][hpath].append (trgprescalemap[bitn])
346  #filter selected cmsls
347  lumidata[2] = filterDeadtable (deadtable, lslist)
348  #print 'lslist ',lslist
349  if not parameters.noWarnings:
350  if len(lumidata[2])!=0:
351  for lumi, deaddata in lumidata[2].items():
352  if deaddata[1] == 0.0 and deaddata[2]!=0 and deaddata[0]!=0:
353  print '[Warning] : run %s :ls %d has 0 instlumi but trigger has data' % (runnum, lumi)
354  if (deaddata[2] == 0 or deaddata[0] == 0) and deaddata[1]!=0.0:
355  print '[Warning] : run %s :ls %d has 0 dead counts or 0 zerobias bit counts, but inst!=0' % (runnum, lumi)
356  #print 'lumidata[2] ', lumidata[2]
357  except Exception, e:
358  print str (e)
359  dbsession.transaction().rollback()
360  del dbsession
361  #print 'before return lumidata ', lumidata
362  ## if parameters.lumiXing:
363  ## xingLumiDict = xingLuminosityForRun (dbsession, runnum, parameters)
364  ## mergeXingLumi (lumidata, xingLumiDict)
365  return lumidata
366 
367 
368 def filterDeadtable (inTable, lslist):
369  result = {}
370  if lslist is None:
371  return inTable
372  if len (lslist) == 0: #if request no ls, then return nothing
373  return result
374  for existingLS in inTable.keys():
375  if existingLS in lslist:
376  result[existingLS] = inTable[existingLS]
377  return result
378 
379 
380 def printDeliveredLumi (lumidata, mode):
381  labels = [ ('Run', 'Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Beam Mode')]
382  print tablePrinter.indent (labels+lumidata, hasHeader = True, separateRows = False,
383  prefix = '| ', postfix = ' |', justify = 'right',
384  delim = ' | ', wrapfunc = lambda x: wrap_onspace (x, 20) )
385 
386 def dumpData (lumidata, filename):
387  """
388  input params: lumidata [{'fieldname':value}]
389  filename csvname
390  """
391 
392  r = csvReporter.csvReporter(filename)
393  r.writeRows(lumidata)
394 
395 def calculateTotalRecorded (deadtable):
396  """
397  input: {lsnum:[deadtime, instlumi, bit_0, norbits,prescale]}
398  output: recordedLumi
399  """
400  recordedLumi = 0.0
401  for myls, d in deadtable.items():
402  instLumi = d[1]
403  #deadfrac = float (d[0])/float (d[2]*3564)
404  #print myls, float (d[2])
405  if float (d[2]) == 0.0:
406  deadfrac = 1.0
407  else:
408  deadfrac = float (d[0])/(float (d[2])*float (d[-1]))
409  lstime = lslengthsec (d[3], 3564)
410  recordedLumi += instLumi* (1.0-deadfrac)*lstime
411  return recordedLumi
412 
413 
414 def splitlistToRangeString (inPut):
415  result = []
416  first = inPut[0]
417  last = inPut[0]
418  result.append ([inPut[0]])
419  counter = 0
420  for i in inPut[1:]:
421  if i == last+1:
422  result[counter].append (i)
423  else:
424  counter += 1
425  result.append ([i])
426  last = i
427  return ', '.join (['['+str (min (x))+'-'+str (max (x))+']' for x in result])
428 
429 
430 def calculateEffective (trgtable, totalrecorded):
431  """
432  input: trgtable{hltpath:[l1seed, hltprescale, l1prescale]}, totalrecorded (float)
433  output:{hltpath, recorded}
434  """
435  #print 'inputtrgtable', trgtable
436  result = {}
437  for hltpath, data in trgtable.items():
438  if len (data) == 3:
439  result[hltpath] = totalrecorded/ (data[1]*data[2])
440  else:
441  result[hltpath] = 0.0
442  return result
443 
444 
445 def getDeadfractions (deadtable):
446  """
447  inputtable: {lsnum:[deadtime, instlumi, bit_0, norbits,bit_0_prescale]}
448  output: {lsnum:deadfraction}
449  """
450  result = {}
451  for myls, d in deadtable.items():
452  #deadfrac = float (d[0])/ (float (d[2])*float (3564))
453  if float (d[2]) == 0.0: ##no beam
454  deadfrac = -1.0
455  else:
456  deadfrac = float (d[0])/ (float (d[2])*float(d[-1]))
457  result[myls] = deadfrac
458  return result
459 
460 def printPerLSLumi (lumidata, isVerbose = False):
461  '''
462  input lumidata [['runnumber', 'trgtable{}', 'deadtable{}']]
463  deadtable {lsnum:[deadtime, instlumi, bit_0, norbits,prescale]}
464  '''
465  datatoprint = []
466  totalrow = []
467  labels = [ ('Run', 'LS', 'Delivered', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
468  lastrowlabels = [ ('Selected LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
469  totalDeliveredLS = 0
470  totalSelectedLS = 0
471  totalDelivered = 0.0
472  totalRecorded = 0.0
473 
474  for perrundata in lumidata:
475  runnumber = perrundata[0]
476  deadtable = perrundata[2]
477  lumiresult = lsBylsLumi (deadtable)
478  totalSelectedLS = totalSelectedLS+len (deadtable)
479  for lsnum, dataperls in lumiresult.items():
480  rowdata = []
481  if len (dataperls) == 0:
482  rowdata += [str (runnumber), str (lsnum), 'N/A', 'N/A']
483  else:
484  rowdata += [str (runnumber), str (lsnum), '%.3f' % (dataperls[0]), '%.3f' % (dataperls[1])]
485  totalDelivered = totalDelivered+dataperls[0]
486  totalRecorded = totalRecorded+dataperls[1]
487  datatoprint.append (rowdata)
488  totalrow.append ([str (totalSelectedLS), '%.3f'% (totalDelivered), '%.3f'% (totalRecorded)])
489  print ' == = '
490  print tablePrinter.indent (labels+datatoprint, hasHeader = True, separateRows = False, prefix = '| ',
491  postfix = ' |', justify = 'right', delim = ' | ',
492  wrapfunc = lambda x: wrap_onspace_strict (x, 22))
493  print ' == = Total : '
494  print tablePrinter.indent (lastrowlabels+totalrow, hasHeader = True, separateRows = False, prefix = '| ',
495  postfix = ' |', justify = 'right', delim = ' | ',
496  wrapfunc = lambda x: wrap_onspace (x, 20))
497 
498 
499 def dumpPerLSLumi (lumidata):
500  datatodump = []
501  for perrundata in lumidata:
502  runnumber = perrundata[0]
503  deadtable = perrundata[2]
504  lumiresult = lsBylsLumi (deadtable)
505  for lsnum, dataperls in lumiresult.items():
506  rowdata = []
507  if len (dataperls) == 0:
508  rowdata += [str (runnumber), str (lsnum), 'N/A', 'N/A']
509  else:
510  rowdata += [str (runnumber), str (lsnum), dataperls[0], dataperls[1]]
511  if len (dataperls) > 2:
512  rowdata.extend ( flatten (dataperls[2:]) )
513  datatodump.append (rowdata)
514  return datatodump
515 
516 
517 def printRecordedLumi (lumidata, isVerbose = False, hltpath = ''):
518  datatoprint = []
519  totalrow = []
520  labels = [ ('Run', 'HLT path', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
521  lastrowlabels = [ ('Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
522  if len (hltpath) != 0 and hltpath != 'all':
523  lastrowlabels = [ ('Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'),
524  'Effective '+u' (/\u03bcb) '.encode ('utf-8')+hltpath)]
525  if isVerbose:
526  labels = [ ('Run', 'HLT-path', 'L1-bit', 'L1-presc', 'HLT-presc', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'))]
527  totalSelectedLS = 0
528  totalRecorded = 0.0
529  totalRecordedInPath = 0.0
530 
531  for dataperRun in lumidata:
532  runnum = dataperRun[0]
533  if len (dataperRun[1]) == 0:
534  rowdata = []
535  rowdata += [str (runnum)]+2*['N/A']
536  datatoprint.append (rowdata)
537  continue
538  perlsdata = dataperRun[2]
539  totalSelectedLS = totalSelectedLS+len (perlsdata)
540  recordedLumi = 0.0
541  #norbits = perlsdata.values()[0][3]
542  recordedLumi = calculateTotalRecorded (perlsdata)
543  totalRecorded = totalRecorded+recordedLumi
544  trgdict = dataperRun[1]
545  effective = calculateEffective (trgdict, recordedLumi)
546  if trgdict.has_key (hltpath) and effective.has_key (hltpath):
547  rowdata = []
548  l1bit = trgdict[hltpath][0]
549  if len (trgdict[hltpath]) != 3:
550  if not isVerbose:
551  rowdata += [str (runnum), hltpath, 'N/A']
552  else:
553  rowdata += [str (runnum), hltpath, l1bit, 'N/A', 'N/A', 'N/A']
554  else:
555  if not isVerbose:
556  rowdata += [str (runnum), hltpath, '%.3f'% (effective[hltpath])]
557  else:
558  hltprescale = trgdict[hltpath][1]
559  l1prescale = trgdict[hltpath][2]
560  rowdata += [str (runnum), hltpath, l1bit, str (l1prescale), str (hltprescale),
561  '%.3f'% (effective[hltpath])]
562  totalRecordedInPath = totalRecordedInPath+effective[hltpath]
563  datatoprint.append (rowdata)
564  continue
565 
566  for trg, trgdata in trgdict.items():
567  #print trg, trgdata
568  rowdata = []
569  if trg == trgdict.keys()[0]:
570  rowdata += [str (runnum)]
571  else:
572  rowdata += ['']
573  l1bit = trgdata[0]
574  if len (trgdata) == 3:
575  if not isVerbose:
576  rowdata += [trg, '%.3f'% (effective[trg])]
577  else:
578  hltprescale = trgdata[1]
579  l1prescale = trgdata[2]
580  rowdata += [trg, l1bit, str (l1prescale), str (hltprescale), '%.3f'% (effective[trg])]
581  else:
582  if not isVerbose:
583  rowdata += [trg, 'N/A']
584  else:
585  rowdata += [trg, l1bit, 'N/A', 'N/A', '%.3f'% (effective[trg])]
586  datatoprint.append (rowdata)
587  #print datatoprint
588  print ' == = '
589  print tablePrinter.indent (labels+datatoprint, hasHeader = True, separateRows = False, prefix = '| ',
590  postfix = ' |', justify = 'right', delim = ' | ',
591  wrapfunc = lambda x: wrap_onspace_strict (x, 22))
592 
593  if len (hltpath) != 0 and hltpath != 'all':
594  totalrow.append ([str (totalSelectedLS), '%.3f'% (totalRecorded), '%.3f'% (totalRecordedInPath)])
595  else:
596  totalrow.append ([str (totalSelectedLS), '%.3f'% (totalRecorded)])
597  print ' == = Total : '
598  print tablePrinter.indent (lastrowlabels+totalrow, hasHeader = True, separateRows = False, prefix = '| ',
599  postfix = ' |', justify = 'right', delim = ' | ',
600  wrapfunc = lambda x: wrap_onspace (x, 20))
601  if isVerbose:
602  deadtoprint = []
603  deadtimelabels = [ ('Run', 'Lumi section : Dead fraction')]
604 
605  for dataperRun in lumidata:
606  runnum = dataperRun[0]
607  if len (dataperRun[1]) == 0:
608  deadtoprint.append ([str (runnum), 'N/A'])
609  continue
610  perlsdata = dataperRun[2]
611  #print 'perlsdata 2 : ', perlsdata
612  deadT = getDeadfractions (perlsdata)
613  t = ''
614  for myls, de in deadT.items():
615  if de<0:
616  t += str (myls)+':nobeam '
617  else:
618  t += str (myls)+':'+'%.5f'% (de)+' '
619  deadtoprint.append ([str (runnum), t])
620  print ' == = '
621  print tablePrinter.indent (deadtimelabels+deadtoprint, hasHeader = True, separateRows = True, prefix = '| ',
622  postfix = ' |', justify = 'right', delim = ' | ',
623  wrapfunc = lambda x: wrap_onspace (x, 80))
624 
625 
626 def dumpRecordedLumi (lumidata, hltpath = ''):
627  #labels = ['Run', 'HLT path', 'Recorded']
628  datatodump = []
629  for dataperRun in lumidata:
630  runnum = dataperRun[0]
631  if len (dataperRun[1]) == 0:
632  rowdata = []
633  rowdata += [str (runnum)]+2*['N/A']
634  datatodump.append (rowdata)
635  continue
636  perlsdata = dataperRun[2]
637  recordedLumi = 0.0
638  #norbits = perlsdata.values()[0][3]
639  recordedLumi = calculateTotalRecorded (perlsdata)
640  trgdict = dataperRun[1]
641  effective = calculateEffective (trgdict, recordedLumi)
642  if trgdict.has_key (hltpath) and effective.has_key (hltpath):
643  rowdata = []
644  l1bit = trgdict[hltpath][0]
645  if len (trgdict[hltpath]) != 3:
646  rowdata += [str (runnum), hltpath, 'N/A']
647  else:
648  hltprescale = trgdict[hltpath][1]
649  l1prescale = trgdict[hltpath][2]
650  rowdata += [str (runnum), hltpath, effective[hltpath]]
651  datatodump.append (rowdata)
652  continue
653 
654  for trg, trgdata in trgdict.items():
655  #print trg, trgdata
656  rowdata = []
657  rowdata += [str (runnum)]
658  l1bit = trgdata[0]
659  if len (trgdata) == 3:
660  rowdata += [trg, effective[trg]]
661  else:
662  rowdata += [trg, 'N/A']
663  datatodump.append (rowdata)
664  return datatodump
665 
666 
667 def printOverviewData (delivered, recorded, hltpath = ''):
668  if len (hltpath) == 0 or hltpath == 'all':
669  toprowlabels = [ ('Run', 'Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8') )]
670  lastrowlabels = [ ('Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8') ) ]
671  else:
672  toprowlabels = [ ('Run', 'Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'), 'Effective'+u' (/\u03bcb) '.encode ('utf-8')+hltpath )]
673  lastrowlabels = [ ('Delivered LS', 'Delivered'+u' (/\u03bcb)'.encode ('utf-8'), 'Selected LS', 'Recorded'+u' (/\u03bcb)'.encode ('utf-8'), 'Effective '+u' (/\u03bcb) '.encode ('utf-8')+hltpath)]
674  datatable = []
675  totaldata = []
676  totalDeliveredLS = 0
677  totalSelectedLS = 0
678  totalDelivered = 0.0
679  totalRecorded = 0.0
680  totalRecordedInPath = 0.0
681  totaltable = []
682  for runidx, deliveredrowdata in enumerate (delivered):
683  rowdata = []
684  rowdata += [deliveredrowdata[0], deliveredrowdata[1], deliveredrowdata[2]]
685  if deliveredrowdata[1] == 'N/A': #run does not exist
686  if hltpath != '' and hltpath != 'all':
687  rowdata += ['N/A', 'N/A', 'N/A']
688  else:
689  rowdata += ['N/A', 'N/A']
690  datatable.append (rowdata)
691  continue
692  totalDeliveredLS += int (deliveredrowdata[1])
693  totalDelivered += float (deliveredrowdata[2])
694  selectedls = recorded[runidx][2].keys()
695  #print 'runidx ', runidx, deliveredrowdata
696  #print 'selectedls ', selectedls
697  if len (selectedls) == 0:
698  selectedlsStr = '[]'
699  recordedLumi = 0
700  if hltpath != '' and hltpath != 'all':
701  rowdata += [selectedlsStr, 'N/A', 'N/A']
702  else:
703  rowdata += [selectedlsStr, 'N/A']
704  else:
705  selectedlsStr = splitlistToRangeString (selectedls)
706  recordedLumi = calculateTotalRecorded (recorded[runidx][2])
707  lumiinPaths = calculateEffective (recorded[runidx][1], recordedLumi)
708  if hltpath != '' and hltpath != 'all':
709  if lumiinPaths.has_key (hltpath):
710  rowdata += [selectedlsStr, '%.3f'% (recordedLumi), '%.3f'% (lumiinPaths[hltpath])]
711  totalRecordedInPath += lumiinPaths[hltpath]
712  else:
713  rowdata += [selectedlsStr, '%.3f'% (recordedLumi), 'N/A']
714  else:
715  #rowdata += [selectedlsStr, '%.3f'% (recordedLumi), '%.3f'% (recordedLumi)]
716  rowdata += [selectedlsStr, '%.3f'% (recordedLumi)]
717  totalSelectedLS += len (selectedls)
718  totalRecorded += recordedLumi
719  datatable.append (rowdata)
720 
721  if hltpath != '' and hltpath != 'all':
722  totaltable = [[str (totalDeliveredLS), '%.3f'% (totalDelivered), str (totalSelectedLS),
723  '%.3f'% (totalRecorded), '%.3f'% (totalRecordedInPath)]]
724  else:
725  totaltable = [[str (totalDeliveredLS), '%.3f'% (totalDelivered), str (totalSelectedLS),
726  '%.3f'% (totalRecorded)]]
727  print tablePrinter.indent (toprowlabels+datatable, hasHeader = True, separateRows = False, prefix = '| ',
728  postfix = ' |', justify = 'right', delim = ' | ',
729  wrapfunc = lambda x: wrap_onspace (x, 20))
730  print ' == = Total : '
731  print tablePrinter.indent (lastrowlabels+totaltable, hasHeader = True, separateRows = False, prefix = '| ',
732  postfix = ' |', justify = 'right', delim = ' | ',
733  wrapfunc = lambda x: wrap_onspace (x, 20))
734 
735 
736 def dumpOverview (delivered, recorded, hltpath = ''):
737  #toprowlabels = ['run', 'delivered', 'recorded', 'hltpath']
738  datatable = []
739  for runidx, deliveredrowdata in enumerate (delivered):
740  rowdata = []
741  rowdata += [deliveredrowdata[0], deliveredrowdata[2]]
742  if deliveredrowdata[1] == 'N/A': #run does not exist
743  rowdata += ['N/A', 'N/A']
744  datatable.append (rowdata)
745  continue
746  recordedLumi = calculateTotalRecorded (recorded[runidx][2])
747  lumiinPaths = calculateEffective (recorded[runidx][1], recordedLumi)
748  if hltpath != '' and hltpath != 'all':
749  if lumiinPaths.has_key (hltpath):
750  rowdata += [recordedLumi, lumiinPaths[hltpath]]
751  else:
752  rowdata += [recordedLumi, 'N/A']
753  else:
754  rowdata += [recordedLumi, recordedLumi]
755  datatable.append (rowdata)
756  return datatable
757 
758 
759 def xingLuminosityForRun (dbsession, runnum, parameters, lumiXingDict = {},
760  maxLumiSection = None):
761  '''Given a run number and a minimum xing luminosity value,
762  returns a dictionary (keyed by (run, lumi section)) where the
763  value is a list of tuples of (xingID, xingLum).
764 
765  - For all xing luminosities, simply set minLumValue to 0.
766 
767  - If you want one dictionary for several runs, pass it in to
768  "lumiXingDict"
769 
770 
771  select
772  s.cmslsnum, d.bxlumivalue, d.bxlumierror, d.bxlumiquality, d.algoname from LUMIDETAIL d, LUMISUMMARY s where s.runnum = 133885 and d.algoname = 'OCC1' and s.lumisummary_id = d.lumisummary_id order by s.startorbit, s.cmslsnum
773  '''
774  try:
775  runnum = int (runnum)
776  dbsession.transaction().start (True)
777  schema = dbsession.schema (parameters.lumischema)
778  if not schema:
779  raise 'cannot connect to schema ', parameters.lumischema
780  detailOutput = coral.AttributeList()
781  detailOutput.extend ('startorbit', 'unsigned int')
782  detailOutput.extend ('cmslsnum', 'unsigned int')
783  detailOutput.extend ('bxlumivalue', 'blob')
784  detailOutput.extend ('bxlumierror', 'blob')
785  detailOutput.extend ('bxlumiquality', 'blob')
786  detailOutput.extend ('algoname', 'string')
787  detailCondition = coral.AttributeList()
788  detailCondition.extend ('runnum', 'unsigned int')
789  detailCondition.extend ('algoname', 'string')
790  detailCondition['runnum'].setData (runnum)
791  detailCondition['algoname'].setData ('OCC1')
792  query = schema.newQuery()
793  query.addToTableList(nameDealer.lumisummaryTableName(), 's')
794  query.addToTableList(nameDealer.lumidetailTableName(), 'd')
795  query.addToOutputList ('s.STARTORBIT', 'startorbit')
796  query.addToOutputList ('s.CMSLSNUM', 'cmslsnum')
797  query.addToOutputList ('d.BXLUMIVALUE', 'bxlumivalue')
798  query.addToOutputList ('d.BXLUMIERROR', 'bxlumierror')
799  query.addToOutputList ('d.BXLUMIQUALITY', 'bxlumiquality')
800  query.addToOutputList ('d.ALGONAME', 'algoname')
801  query.setCondition ('s.RUNNUM =:runnum and d.ALGONAME =:algoname and s.LUMISUMMARY_ID=d.LUMISUMMARY_ID',detailCondition)
802  query.addToOrderList ('s.CMSLSNUM')
803  query.defineOutput (detailOutput)
804  cursor = query.execute()
805  count = 0
806  while cursor.next():
807  ## ## Note: If you are going to break out of this loop early,
808  ## ## make sure you call cursor.close():
809  ##
810  ## if count > 20 :
811  ## cursor.close()
812  ## break
813  ## count += 1
814  cmslsnum = cursor.currentRow()['cmslsnum'].data()
815  algoname = cursor.currentRow()['algoname'].data()
816  bxlumivalue = cursor.currentRow()['bxlumivalue'].data()
817  startorbit = cursor.currentRow()['startorbit'].data()
818 
819  if maxLumiSection and maxLumiSection < cmslsnum:
820  cursor.close()
821  break
822 
823  xingArray = array.array ('f')
824  xingArray.fromstring( bxlumivalue.readline() )
825  numPrinted = 0
826  xingLum = []
827  for index, lum in enumerate (xingArray):
828  lum *= parameters.normFactor
829  if lum < parameters.xingMinLum:
830  continue
831  xingLum.append( (index, lum) )
832  lumiXingDict[ (runnum, cmslsnum) ] = xingLum
833  del query
834  dbsession.transaction().commit()
835  return lumiXingDict
836  except Exception, e:
837  print str (e)
838  print "whoops"
839  dbsession.transaction().rollback()
840  del dbsession
841 
842 
843 def flatten (obj):
844  '''Given nested lists or tuples, returns a single flattened list'''
845  result = []
846  for piece in obj:
847  if hasattr (piece, '__iter__') and not isinstance (piece, basestring):
848  result.extend( flatten (piece) )
849  else:
850  result.append (piece)
851  return result
852 
853 
854 def mergeXingLumi (triplet, xingLumiDict):
855  '''Given general xing information and a xingLumiDict, the xing
856  luminosity information is merged with the general information'''
857  runNumber = triplet[0]
858  deadTable = triplet[2]
859  for lumi, lumiList in deadTable.iteritems():
860  key = ( int(runNumber), int(lumi) )
861  xingLumiValues = xingLumiDict.get (key)
862  if xingLumiValues:
863  lumiList.append( flatten (xingLumiValues) )
864 
865 
866 def setupSession (connectString, siteconfpath, parameters, debug = False):
867  '''returns database session'''
868  connectparser = connectstrParser.connectstrParser (connectString)
869  connectparser.parse()
870  usedefaultfrontierconfig = False
871  cacheconfigpath = ''
872  if connectparser.needsitelocalinfo():
873  if not siteconfpath:
874  cacheconfigpath = os.environ['CMS_PATH']
875  if cacheconfigpath:
876  cacheconfigpath = os.path.join (cacheconfigpath, 'SITECONF', 'local', 'JobConfig', 'site-local-config.xml')
877  else:
878  usedefaultfrontierconfig = True
879  else:
880  cacheconfigpath = siteconfpath
881  cacheconfigpath = os.path.join (cacheconfigpath, 'site-local-config.xml')
882  ccp = cacheconfigParser.cacheconfigParser()
883  if usedefaultfrontierconfig:
884  ccp.parseString (parameters.defaultfrontierConfigString)
885  else:
886  ccp.parse (cacheconfigpath)
887  connectString = connectparser.fullfrontierStr (connectparser.schemaname(), ccp.parameterdict())
888  svc = coral.ConnectionService()
889  if debug :
890  msg = coral.MessageStream ('')
891  msg.setMsgVerbosity (coral.message_Level_Debug)
892  parameters.verbose = True
893  session = svc.connect (connectString, accessMode = coral.access_ReadOnly)
894  session.typeConverter().setCppTypeForSqlType ("unsigned int", "NUMBER (10)")
895  session.typeConverter().setCppTypeForSqlType ("unsigned long long", "NUMBER (20)")
896  return session, svc
897 
898 
899 
900 ###==============real api=====###
901 
902 def allruns(schemaHandle,requireRunsummary=True,requireLumisummary=False,requireTrg=False,requireHlt=False):
903  '''
904  find all runs in the DB. By default requires cmsrunsummary table contain the run. The condition can be loosed in situation where db loading failed on certain data portions.
905  '''
906  if not requireRunsummary and not requireLumiummary and not requireTrg and not requireHlt:
907  print 'must require at least one table'
908  raise
909  runresult=[]
910  runlist=[]
911  numdups=0
912  if requireRunsummary:
913  numdups=numdups+1
914  queryHandle=schemaHandle.newQuery()
915  queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
916  queryHandle.addToOutputList("RUNNUM","run")
917  #queryBind=coral.AttributeList()
918  result=coral.AttributeList()
919  result.extend("run","unsigned int")
920  queryHandle.defineOutput(result)
921  cursor=queryHandle.execute()
922  while cursor.next():
923  r=cursor.currentRow()['run'].data()
924  runlist.append(r)
925  del queryHandle
926  if requireLumisummary:
927  numdups=numdups+1
928  queryHandle=schemaHandle.newQuery()
929  queryHandle.addToTableList(nameDealer.lumisummaryTableName())
930  queryHandle.addToOutputList("distinct RUNNUM","run")
931  #queryBind=coral.AttributeList()
932  result=coral.AttributeList()
933  result.extend("run","unsigned int")
934  queryHandle.defineOutput(result)
935  cursor=queryHandle.execute()
936  while cursor.next():
937  r=cursor.currentRow()['run'].data()
938  runlist.append(r)
939  del queryHandle
940  if requireTrg:
941  numdups=numdups+1
942  queryHandle=schemaHandle.newQuery()
943  queryHandle.addToTableList(nameDealer.trgTableName())
944  queryHandle.addToOutputList("distinct RUNNUM","run")
945  #queryBind=coral.AttributeList()
946  result=coral.AttributeList()
947  result.extend("run","unsigned int")
948  queryHandle.defineOutput(result)
949  cursor=queryHandle.execute()
950  while cursor.next():
951  r=cursor.currentRow()['run'].data()
952  runlist.append(r)
953  del queryHandle
954  if requireHlt:
955  numdups=numdups+1
956  queryHandle=schemaHandle.newQuery()
957  queryHandle.addToTableList(nameDealer.hltTableName())
958  queryHandle.addToOutputList("distinct RUNNUM","run")
959  #queryBind=coral.AttributeList()
960  result=coral.AttributeList()
961  result.extend("run","unsigned int")
962  queryHandle.defineOutput(result)
963  cursor=queryHandle.execute()
964  while cursor.next():
965  r=cursor.currentRow()['run'].data()
966  runlist.append(r)
967  del queryHandle
968  dupresult=CommonUtil.count_dups(runlist)
969  for dup in dupresult:
970  if dup[1]==numdups:
971  runresult.append(dup[0])
972  runresult.sort()
973  return runresult
974 
975 def validation(queryHandle,run=None,cmsls=None):
976  '''retrieve validation data per run or all
977  input: run. if not run, retrive all; if cmslsnum selection list pesent, filter out unselected result
978  output: {run:[[cmslsnum,status,comment]]}
979  '''
980  result={}
981  queryHandle.addToTableList(nameDealer.lumivalidationTableName())
982  queryHandle.addToOutputList('RUNNUM','runnum')
983  queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
984  queryHandle.addToOutputList('FLAG','flag')
985  queryHandle.addToOutputList('COMMENT','comment')
986  if run:
987  queryCondition='RUNNUM=:runnum'
988  queryBind=coral.AttributeList()
989  queryBind.extend('runnum','unsigned int')
990  queryBind['runnum'].setData(run)
991  queryHandle.setCondition(queryCondition,queryBind)
992  queryResult=coral.AttributeList()
993  queryResult.extend('runnum','unsigned int')
994  queryResult.extend('cmslsnum','unsigned int')
995  queryResult.extend('flag','string')
996  queryResult.extend('comment','string')
997  queryHandle.defineOutput(queryResult)
998  cursor=queryHandle.execute()
999  while cursor.next():
1000  runnum=cursor.currentRow()['runnum'].data()
1001  if not result.has_key(runnum):
1002  result[runnum]=[]
1003  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1004  flag=cursor.currentRow()['flag'].data()
1005  comment=cursor.currentRow()['comment'].data()
1006  result[runnum].append([cmslsnum,flag,comment])
1007  if run and cmsls and len(cmsls)!=0:
1008  selectedresult={}
1009  for runnum,perrundata in result.items():
1010  for lsdata in perrundata:
1011  if lsdata[0] not in cmsls:
1012  continue
1013  if not selectedresult.has_key(runnum):
1014  selectedresult[runnum]=[]
1015  selectedresult[runnum].append(lsdata)
1016  return selectedresult
1017  else:
1018  return result
1019 
1020 def allfills(queryHandle,filtercrazy=True):
1021  '''select distinct fillnum from cmsrunsummary
1022  there are crazy fill numbers. we assume they are not valid runs
1023  '''
1024  result=[]
1025  queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
1026  queryHandle.addToOutputList('distinct FILLNUM','fillnum')
1027 
1028  if filtercrazy:
1029  queryCondition='FILLNUM>:zero and FILLNUM<:crazybig'
1030  queryBind=coral.AttributeList()
1031  queryBind.extend('zero','unsigned int')
1032  queryBind.extend('crazybig','unsigned int')
1033  queryBind['zero'].setData(int(0))
1034  queryBind['crazybig'].setData(int(29701))
1035  queryHandle.setCondition(queryCondition,queryBind)
1036  queryResult=coral.AttributeList()
1037  queryResult.extend('fillnum','unsigned int')
1038  queryHandle.defineOutput(queryResult)
1039  cursor=queryHandle.execute()
1040  while cursor.next():
1041  result.append(cursor.currentRow()['fillnum'].data())
1042  result.sort()
1043  return result
1044 def runsummaryByrun(queryHandle,runnum):
1045  '''
1046  select fillnum,sequence,hltkey,to_char(starttime),to_char(stoptime) from cmsrunsummary where runnum=:runnum
1047  output: [fillnum,sequence,hltkey,starttime,stoptime]
1048  '''
1049  t=lumiTime.lumiTime()
1050  result=[]
1051  queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
1052  queryCondition=coral.AttributeList()
1053  queryCondition.extend('runnum','unsigned int')
1054  queryCondition['runnum'].setData(int(runnum))
1055  queryHandle.addToOutputList('FILLNUM','fillnum')
1056  queryHandle.addToOutputList('SEQUENCE','sequence')
1057  queryHandle.addToOutputList('HLTKEY','hltkey')
1058  queryHandle.addToOutputList('to_char(STARTTIME,\''+t.coraltimefm+'\')','starttime')
1059  queryHandle.addToOutputList('to_char(STOPTIME,\''+t.coraltimefm+'\')','stoptime')
1060  queryHandle.setCondition('RUNNUM=:runnum',queryCondition)
1061  queryResult=coral.AttributeList()
1062  queryResult.extend('fillnum','unsigned int')
1063  queryResult.extend('sequence','string')
1064  queryResult.extend('hltkey','string')
1065  queryResult.extend('starttime','string')
1066  queryResult.extend('stoptime','string')
1067  queryHandle.defineOutput(queryResult)
1068  cursor=queryHandle.execute()
1069  while cursor.next():
1070  result.append(cursor.currentRow()['fillnum'].data())
1071  result.append(cursor.currentRow()['sequence'].data())
1072  result.append(cursor.currentRow()['hltkey'].data())
1073  result.append(cursor.currentRow()['starttime'].data())
1074  result.append(cursor.currentRow()['stoptime'].data())
1075  #if len(result)!=5:
1076  # print 'wrong runsummary result'
1077  # raise
1078  return result
1079 
1080 def lumisummaryByrun(queryHandle,runnum,lumiversion,beamstatus=None,beamenergy=None,beamenergyfluctuation=0.09):
1081  '''
1082  one can impose beamstatus, beamenergy selections at the SQL query level or process them later from the general result
1083  select cmslsnum,instlumi,numorbit,startorbit,beamstatus,beamenery from lumisummary where runnum=:runnum and lumiversion=:lumiversion order by startorbit;
1084  output: [[cmslsnum,instlumi,numorbit,startorbit,beamstatus,beamenergy,cmsalive]]
1085  Note: the non-cmsalive LS are included in the result
1086  '''
1087  result=[]
1088  queryHandle.addToTableList(nameDealer.lumisummaryTableName())
1089  queryCondition=coral.AttributeList()
1090  queryCondition.extend('runnum','unsigned int')
1091  queryCondition.extend('lumiversion','string')
1092  conditionstring='RUNNUM=:runnum and LUMIVERSION=:lumiversion'
1093  queryCondition['runnum'].setData(int(runnum))
1094  queryCondition['lumiversion'].setData(lumiversion)
1095  queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
1096  queryHandle.addToOutputList('INSTLUMI','instlumi')
1097  queryHandle.addToOutputList('NUMORBIT','numorbit')
1098  queryHandle.addToOutputList('STARTORBIT','startorbit')
1099  queryHandle.addToOutputList('BEAMSTATUS','beamstatus')
1100  queryHandle.addToOutputList('BEAMENERGY','beamenergy')
1101  queryHandle.addToOutputList('CMSALIVE','cmsalive')
1102  if beamstatus and len(beamstatus)!=0:
1103  conditionstring=conditionstring+' and BEAMSTATUS=:beamstatus'
1104  queryCondition.extend('beamstatus','string')
1105  queryCondition['beamstatus'].setData(beamstatus)
1106  if beamenergy:
1107  minBeamenergy=float(beamenergy*(1.0-beamenergyfluctuation))
1108  maxBeamenergy=float(beamenergy*(1.0+beamenergyfluctuation))
1109  conditionstring=conditionstring+' and BEAMENERGY>:minBeamenergy and BEAMENERGY<:maxBeamenergy'
1110  queryCondition.extend('minBeamenergy','float')
1111  queryCondition.extend('maxBeamenergy','float')
1112  queryCondition['minBeamenergy'].setData(float(minBeamenergy))
1113  queryCondition['maxBeamenergy'].setData(float(maxBeamenergy))
1114  queryResult=coral.AttributeList()
1115  queryResult.extend('cmslsnum','unsigned int')
1116  queryResult.extend('instlumi','float')
1117  queryResult.extend('numorbit','unsigned int')
1118  queryResult.extend('startorbit','unsigned int')
1119  queryResult.extend('beamstatus','string')
1120  queryResult.extend('beamenergy','float')
1121  queryResult.extend('cmsalive','unsigned int')
1122  queryHandle.defineOutput(queryResult)
1123  queryHandle.setCondition(conditionstring,queryCondition)
1124  queryHandle.addToOrderList('startorbit')
1125  cursor=queryHandle.execute()
1126  while cursor.next():
1127  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1128  instlumi=cursor.currentRow()['instlumi'].data()
1129  numorbit=cursor.currentRow()['numorbit'].data()
1130  startorbit=cursor.currentRow()['startorbit'].data()
1131  beamstatus=cursor.currentRow()['beamstatus'].data()
1132  beamenergy=cursor.currentRow()['beamenergy'].data()
1133  cmsalive=cursor.currentRow()['cmsalive'].data()
1134  result.append([cmslsnum,instlumi,numorbit,startorbit,beamstatus,beamenergy,cmsalive])
1135  return result
1136 
1137 def lumisumByrun(queryHandle,runnum,lumiversion,beamstatus=None,beamenergy=None,beamenergyfluctuation=0.09):
1138  '''
1139  beamenergy unit : GeV
1140  beamenergyfluctuation : fraction allowed to fluctuate around beamenergy value
1141  select sum(instlumi) from lumisummary where runnum=:runnum and lumiversion=:lumiversion
1142  output: float totallumi
1143  Note: the output is the raw result, need to apply LS length in time(sec)
1144  '''
1145  result=0.0
1146  queryHandle.addToTableList(nameDealer.lumisummaryTableName())
1147  queryCondition=coral.AttributeList()
1148  queryCondition.extend('runnum','unsigned int')
1149  queryCondition.extend('lumiversion','string')
1150 
1151  queryCondition['runnum'].setData(int(runnum))
1152  queryCondition['lumiversion'].setData(lumiversion)
1153  queryHandle.addToOutputList('sum(INSTLUMI)','lumitotal')
1154  conditionstring='RUNNUM=:runnum and LUMIVERSION=:lumiversion'
1155  if beamstatus and len(beamstatus)!=0:
1156  conditionstring=conditionstring+' and BEAMSTATUS=:beamstatus'
1157  queryCondition.extend('beamstatus','string')
1158  queryCondition['beamstatus'].setData(beamstatus)
1159  if beamenergy and beamenergy!=0.0:
1160  minBeamenergy=float(beamenergy*(1.0-beamenergyfluctuation))
1161  maxBeamenergy=float(beamenergy*(1.0+beamenergyfluctuation))
1162  conditionstring=conditionstring+' and BEAMENERGY>:minBeamenergy and BEAMENERGY<:maxBeamenergy'
1163  queryCondition.extend('minBeamenergy','float')
1164  queryCondition.extend('maxBeamenergy','float')
1165  queryCondition['minBeamenergy'].setData(float(minBeamenergy))
1166  queryCondition['maxBeamenergy'].setData(float(maxBeamenergy))
1167  queryHandle.setCondition(conditionstring,queryCondition)
1168  queryResult=coral.AttributeList()
1169  queryResult.extend('lumitotal','float')
1170  queryHandle.defineOutput(queryResult)
1171  cursor=queryHandle.execute()
1172  while cursor.next():
1173  result=cursor.currentRow()['lumitotal'].data()
1174  return result
1175 
1176 def trgbitzeroByrun(queryHandle,runnum):
1177  '''
1178  select cmslsnum,trgcount,deadtime,bitname,prescale from trg where runnum=:runnum and bitnum=0;
1179  output: {cmslsnum:[trgcount,deadtime,bitname,prescale]}
1180  '''
1181  result={}
1182  queryHandle.addToTableList(nameDealer.trgTableName())
1183  queryCondition=coral.AttributeList()
1184  queryCondition.extend('runnum','unsigned int')
1185  queryCondition.extend('bitnum','unsigned int')
1186  queryCondition['runnum'].setData(int(runnum))
1187  queryCondition['bitnum'].setData(int(0))
1188  queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
1189  queryHandle.addToOutputList('TRGCOUNT','trgcount')
1190  queryHandle.addToOutputList('DEADTIME','deadtime')
1191  queryHandle.addToOutputList('BITNAME','bitname')
1192  queryHandle.addToOutputList('PRESCALE','prescale')
1193  queryHandle.setCondition('RUNNUM=:runnum and BITNUM=:bitnum',queryCondition)
1194  queryResult=coral.AttributeList()
1195  queryResult.extend('cmslsnum','unsigned int')
1196  queryResult.extend('trgcount','unsigned int')
1197  queryResult.extend('deadtime','unsigned int')
1198  queryResult.extend('bitname','string')
1199  queryResult.extend('prescale','unsigned int')
1200  queryHandle.defineOutput(queryResult)
1201  cursor=queryHandle.execute()
1202  while cursor.next():
1203  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1204  trgcount=cursor.currentRow()['trgcount'].data()
1205  deadtime=cursor.currentRow()['deadtime'].data()
1206  bitname=cursor.currentRow()['bitname'].data()
1207  prescale=cursor.currentRow()['prescale'].data()
1208  if not result.has_key(cmslsnum):
1209  result[cmslsnum]=[trgcount,deadtime,bitname,prescale]
1210  return result
1211 
1212 def lumisummarytrgbitzeroByrun(queryHandle,runnum,lumiversion,beamstatus=None,beamenergy=None,beamenergyfluctuation=0.09):
1213  '''
1214  select l.cmslsnum,l.instlumi,l.numorbit,l.startorbit,l.beamstatus,l.beamenery,t.trgcount,t.deadtime,t.bitname,t.prescale from trg t,lumisummary l where t.bitnum=:bitnum and l.runnum=:runnum and l.lumiversion=:lumiversion and l.runnum=t.runnum and t.cmslsnum=l.cmslsnum;
1215  Everything you ever need to know about bitzero and avg luminosity. Since we do not know if joint query is better of sperate, support both.
1216  output: {cmslsnum:[instlumi,numorbit,startorbit,beamstatus,beamenergy,bitzerocount,deadtime,bitname,prescale]}
1217  Note: only cmsalive LS are included in the result. Therefore, this function cannot be used for calculating delivered!
1218  '''
1219  result={}
1220  queryHandle.addToTableList(nameDealer.trgTableName(),'t')
1221  queryHandle.addToTableList(nameDealer.lumisummaryTableName(),'l')
1222  queryCondition=coral.AttributeList()
1223  queryCondition.extend('bitnum','unsigned int')
1224  queryCondition.extend('runnum','unsigned int')
1225  queryCondition.extend('lumiversion','string')
1226  queryCondition['bitnum'].setData(int(0))
1227  queryCondition['runnum'].setData(int(runnum))
1228  queryCondition['lumiversion'].setData(lumiversion)
1229 
1230  queryHandle.addToOutputList('l.CMSLSNUM','cmslsnum')
1231  queryHandle.addToOutputList('l.INSTLUMI','instlumi')
1232  queryHandle.addToOutputList('l.NUMORBIT','numorbit')
1233  queryHandle.addToOutputList('l.STARTORBIT','startorbit')
1234  queryHandle.addToOutputList('l.BEAMSTATUS','beamstatus')
1235  queryHandle.addToOutputList('l.BEAMENERGY','beamenergy')
1236  queryHandle.addToOutputList('t.TRGCOUNT','trgcount')
1237  queryHandle.addToOutputList('t.DEADTIME','deadtime')
1238  queryHandle.addToOutputList('t.BITNAME','bitname')
1239  queryHandle.addToOutputList('t.PRESCALE','prescale')
1240  conditionstring='t.BITNUM=:bitnum and l.RUNNUM=:runnum and l.LUMIVERSION=:lumiversion and l.RUNNUM=t.RUNNUM and t.CMSLSNUM=l.CMSLSNUM'
1241  if beamstatus and len(beamstatus)!=0:
1242  conditionstring=conditionstring+' and l.BEAMSTATUS=:beamstatus'
1243  queryCondition.extend('beamstatus','string')
1244  queryCondition['beamstatus'].setData(beamstatus)
1245  if beamenergy and beamenergy!=0.0:
1246  minBeamenergy=float(beamenergy*(1-beamenergyfluctuation))
1247  maxBeamenergy=float(beamenergy*(1+beamenergyfluctuation))
1248  conditionstring=conditionstring+' and l.BEAMENERGY>:minBeamenergy and l.BEAMENERGY<:maxBeamenergy'
1249  queryCondition.extend('minBeamenergy','float')
1250  queryCondition.extend('maxBeamenergy','float')
1251  queryCondition['minBeamenergy'].setData(float(minBeamenergy))
1252  queryCondition['maxBeamenergy'].setData(float(maxBeamenergy))
1253  queryHandle.setCondition(conditionstring,queryCondition)
1254  queryResult=coral.AttributeList()
1255  queryResult.extend('cmslsnum','unsigned int')
1256  queryResult.extend('instlumi','float')
1257  queryResult.extend('numorbit','unsigned int')
1258  queryResult.extend('startorbit','unsigned int')
1259  queryResult.extend('beamstatus','string')
1260  queryResult.extend('beamenergy','float')
1261  queryResult.extend('trgcount','unsigned int')
1262  queryResult.extend('deadtime','unsigned int')
1263  queryResult.extend('bitname','string')
1264  queryResult.extend('prescale','unsigned int')
1265  queryHandle.defineOutput(queryResult)
1266  cursor=queryHandle.execute()
1267  while cursor.next():
1268  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1269  instlumi=cursor.currentRow()['instlumi'].data()
1270  numorbit=cursor.currentRow()['numorbit'].data()
1271  startorbit=cursor.currentRow()['startorbit'].data()
1272  beamstatus=cursor.currentRow()['beamstatus'].data()
1273  beamenergy=cursor.currentRow()['beamenergy'].data()
1274  trgcount=cursor.currentRow()['trgcount'].data()
1275  deadtime=cursor.currentRow()['deadtime'].data()
1276  bitname=cursor.currentRow()['bitname'].data()
1277  prescale=cursor.currentRow()['prescale'].data()
1278  if not result.has_key(cmslsnum):
1279  result[cmslsnum]=[instlumi,numorbit,startorbit,beamstatus,beamenergy,trgcount,deadtime,bitname,prescale]
1280  return result
1281 
1282 def trgBybitnameByrun(queryHandle,runnum,bitname):
1283  '''
1284  select cmslsnum,trgcount,deadtime,bitnum,prescale from trg where runnum=:runnum and bitname=:bitname;
1285  output: {cmslsnum:[trgcount,deadtime,bitnum,prescale]}
1286  '''
1287  result={}
1288  queryHandle.addToTableList(nameDealer.trgTableName())
1289  queryCondition=coral.AttributeList()
1290  queryCondition.extend('runnum','unsigned int')
1291  queryCondition.extend('bitname','string')
1292  queryCondition['runnum'].setData(int(runnum))
1293  queryCondition['bitname'].setData(bitname)
1294  queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
1295  queryHandle.addToOutputList('TRGCOUNT','trgcount')
1296  queryHandle.addToOutputList('DEADTIME','deadtime')
1297  queryHandle.addToOutputList('BITNUM','bitnum')
1298  queryHandle.addToOutputList('PRESCALE','prescale')
1299  queryHandle.setCondition('RUNNUM=:runnum and BITNAME=:bitname',queryCondition)
1300  queryResult=coral.AttributeList()
1301  queryResult.extend('cmslsnum','unsigned int')
1302  queryResult.extend('trgcount','unsigned int')
1303  queryResult.extend('deadtime','unsigned long long')
1304  queryResult.extend('bitnum','unsigned int')
1305  queryResult.extend('prescale','unsigned int')
1306  queryHandle.defineOutput(queryResult)
1307  cursor=queryHandle.execute()
1308  while cursor.next():
1309  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1310  trgcount=cursor.currentRow()['trgcount'].data()
1311  deadtime=cursor.currentRow()['deadtime'].data()
1312  bitnum=cursor.currentRow()['bitnum'].data()
1313  prescale=cursor.currentRow()['prescale'].data()
1314  if not result.has_key(cmslsnum):
1315  result[cmslsnum]=[trgcount,deadtime,bitnum,prescale]
1316  return result
1317 
1318 def trgAllbitsByrun(queryHandle,runnum):
1319  '''
1320  all you ever want to know about trigger
1321  select cmslsnum,trgcount,deadtime,bitnum,bitname,prescale from trg where runnum=:runnum order by bitnum,cmslsnum
1322  this can be changed to blob query later
1323  output: {cmslsnum:{bitname:[bitnum,trgcount,deadtime,prescale]}}
1324  '''
1325  result={}
1326  queryHandle.addToTableList(nameDealer.trgTableName())
1327  queryCondition=coral.AttributeList()
1328  queryCondition.extend('runnum','unsigned int')
1329  queryCondition['runnum'].setData(int(runnum))
1330  queryHandle.addToOutputList('cmslsnum')
1331  queryHandle.addToOutputList('trgcount')
1332  queryHandle.addToOutputList('deadtime')
1333  queryHandle.addToOutputList('bitnum')
1334  queryHandle.addToOutputList('bitname')
1335  queryHandle.addToOutputList('prescale')
1336  queryHandle.setCondition('runnum=:runnum',queryCondition)
1337  queryResult=coral.AttributeList()
1338  queryResult.extend('cmslsnum','unsigned int')
1339  queryResult.extend('trgcount','unsigned int')
1340  queryResult.extend('deadtime','unsigned long long')
1341  queryResult.extend('bitnum','unsigned int')
1342  queryResult.extend('bitname','string')
1343  queryResult.extend('prescale','unsigned int')
1344  queryHandle.defineOutput(queryResult)
1345  queryHandle.addToOrderList('bitnum')
1346  queryHandle.addToOrderList('cmslsnum')
1347  cursor=queryHandle.execute()
1348  while cursor.next():
1349  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1350  trgcount=cursor.currentRow()['trgcount'].data()
1351  deadtime=cursor.currentRow()['deadtime'].data()
1352  bitnum=cursor.currentRow()['bitnum'].data()
1353  bitname=cursor.currentRow()['bitname'].data()
1354  prescale=cursor.currentRow()['prescale'].data()
1355  if not result.has_key(cmslsnum):
1356  dataperLS={}
1357  dataperLS[bitname]=[bitnum,trgcount,deadtime,prescale]
1358  result[cmslsnum]=dataperLS
1359  else:
1360  result[cmslsnum][bitname]=[bitnum,trgcount,deadtime,prescale]
1361  return result
1362 
1363 
1364 def hltBypathByrun(queryHandle,runnum,hltpath):
1365  '''
1366  select cmslsnum,inputcount,acceptcount,prescale from hlt where runnum=:runnum and pathname=:pathname
1367  output: {cmslsnum:[inputcount,acceptcount,prescale]}
1368  '''
1369  result={}
1370  queryHandle.addToTableList(nameDealer.hltTableName())
1371  queryCondition=coral.AttributeList()
1372  queryCondition.extend('runnum','unsigned int')
1373  queryCondition.extend('pathname','string')
1374  queryCondition['runnum'].setData(int(runnum))
1375  queryCondition['pathname'].setData(hltpath)
1376  queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
1377  queryHandle.addToOutputList('INPUTCOUNT','inputcount')
1378  queryHandle.addToOutputList('ACCEPTCOUNT','acceptcount')
1379  queryHandle.addToOutputList('PRESCALE','prescale')
1380  queryHandle.setCondition('RUNNUM=:runnum and PATHNAME=:pathname',queryCondition)
1381  queryResult=coral.AttributeList()
1382  queryResult.extend('cmslsnum','unsigned int')
1383  queryResult.extend('inputcount','unsigned int')
1384  queryResult.extend('acceptcount','unsigned int')
1385  queryResult.extend('prescale','unsigned int')
1386  queryHandle.defineOutput(queryResult)
1387  cursor=queryHandle.execute()
1388  while cursor.next():
1389  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1390  inputcount=cursor.currentRow()['inputcount'].data()
1391  acceptcount=cursor.currentRow()['acceptcount'].data()
1392  prescale=cursor.currentRow()['prescale'].data()
1393  if not result.has_key(cmslsnum):
1394  result[cmslsnum]=[inputcount,acceptcount,prescale]
1395  return result
1396 
1397 def hltAllpathByrun(queryHandle,runnum):
1398  '''
1399  select cmslsnum,inputcount,acceptcount,prescale,pathname from hlt where runnum=:runnum
1400  this can be changed to blob query later
1401  output: {cmslsnum:{pathname:[inputcount,acceptcount,prescale]}}
1402  '''
1403  result={}
1404  queryHandle.addToTableList(nameDealer.hltTableName())
1405  queryCondition=coral.AttributeList()
1406  queryCondition.extend('runnum','unsigned int')
1407  queryCondition['runnum'].setData(int(runnum))
1408  queryHandle.addToOutputList('CMSLSNUM','cmslsnum')
1409  queryHandle.addToOutputList('INPUTCOUNT','inputcount')
1410  queryHandle.addToOutputList('ACCEPTCOUNT','acceptcount')
1411  queryHandle.addToOutputList('PRESCALE','prescale')
1412  queryHandle.addToOutputList('PATHNAME','pathname')
1413  queryHandle.setCondition('RUNNUM=:runnum',queryCondition)
1414  queryResult=coral.AttributeList()
1415  queryResult.extend('cmslsnum','unsigned int')
1416  queryResult.extend('inputcount','unsigned int')
1417  queryResult.extend('acceptcount','unsigned int')
1418  queryResult.extend('prescale','unsigned int')
1419  queryResult.extend('pathname','string')
1420  queryHandle.defineOutput(queryResult)
1421  cursor=queryHandle.execute()
1422  while cursor.next():
1423  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1424  inputcount=cursor.currentRow()['inputcount'].data()
1425  acceptcount=cursor.currentRow()['acceptcount'].data()
1426  prescale=cursor.currentRow()['prescale'].data()
1427  pathname=cursor.currentRow()['pathname'].data()
1428  if not result.has_key(cmslsnum):
1429  dataperLS={}
1430  dataperLS[pathname]=[inputcount,acceptcount,prescale]
1431  result[cmslsnum]=dataperLS
1432  else:
1433  result[cmslsnum][pathname]=[inputcount,acceptcount,prescale]
1434  return result
1435 
1436 
1437 def beamIntensityForRun(query,parameters,runnum):
1438  '''
1439  select CMSBXINDEXBLOB,BEAMINTENSITYBLOB_1,BEAMINTENSITYBLOB_2 from LUMISUMMARY where runnum=146315 and LUMIVERSION='0001'
1440 
1441  output : result {startorbit: [(bxidx,beam1intensity,beam2intensity)]}
1442  '''
1443  result={} #{startorbit:[(bxidx,occlumi,occlumierr,beam1intensity,beam2intensity)]}
1444 
1445  lumisummaryOutput=coral.AttributeList()
1446  lumisummaryOutput.extend('cmslsnum','unsigned int')
1447  lumisummaryOutput.extend('startorbit','unsigned int')
1448  lumisummaryOutput.extend('bxindexblob','blob');
1449  lumisummaryOutput.extend('beamintensityblob1','blob');
1450  lumisummaryOutput.extend('beamintensityblob2','blob');
1451  condition=coral.AttributeList()
1452  condition.extend('runnum','unsigned int')
1453  condition.extend('lumiversion','string')
1454  condition['runnum'].setData(int(runnum))
1455  condition['lumiversion'].setData(parameters.lumiversion)
1456 
1457  query.addToTableList(parameters.lumisummaryname)
1458  query.addToOutputList('CMSLSNUM','cmslsnum')
1459  query.addToOutputList('STARTORBIT','startorbit')
1460  query.addToOutputList('CMSBXINDEXBLOB','bxindexblob')
1461  query.addToOutputList('BEAMINTENSITYBLOB_1','beamintensityblob1')
1462  query.addToOutputList('BEAMINTENSITYBLOB_2','beamintensityblob2')
1463  query.setCondition('RUNNUM=:runnum AND LUMIVERSION=:lumiversion',condition)
1464  query.defineOutput(lumisummaryOutput)
1465  cursor=query.execute()
1466  while cursor.next():
1467  #cmslsnum=cursor.currentRow()['cmslsnum'].data()
1468  startorbit=cursor.currentRow()['startorbit'].data()
1469  if not cursor.currentRow()["bxindexblob"].isNull():
1470  bxindexblob=cursor.currentRow()['bxindexblob'].data()
1471  beamintensityblob1=cursor.currentRow()['beamintensityblob1'].data()
1472  beamintensityblob2=cursor.currentRow()['beamintensityblob2'].data()
1473  if bxindexblob.readline() is not None and beamintensityblob1.readline() is not None and beamintensityblob2.readline() is not None:
1474  bxidx=array.array('h')
1475  bxidx.fromstring(bxindexblob.readline())
1476  bb1=array.array('f')
1477  bb1.fromstring(beamintensityblob1.readline())
1478  bb2=array.array('f')
1479  bb2.fromstring(beamintensityblob2.readline())
1480  for index,bxidxvalue in enumerate(bxidx):
1481  if not result.has_key(startorbit):
1482  result[startorbit]=[]
1483  b1intensity=bb1[index]
1484  b2intensity=bb2[index]
1485  result[startorbit].append((bxidxvalue,b1intensity,b2intensity))
1486  return result
1487 
1488 def calibratedDetailForRunLimitresult(query,parameters,runnum,algoname='OCC1'):
1489  '''select
1490  s.cmslsnum,d.bxlumivalue,d.bxlumierror,d.bxlumiquality,d.algoname from LUMIDETAIL d,LUMISUMMARY s where s.runnum=133885 and d.algoname='OCC1' and s.lumisummary_id=d.lumisummary_id order by s.startorbit,s.cmslsnum
1491  result={(startorbit,cmslsnum):[(lumivalue,lumierr),]}
1492  '''
1493  result={}
1494  detailOutput=coral.AttributeList()
1495  detailOutput.extend('cmslsnum','unsigned int')
1496  detailOutput.extend('startorbit','unsigned int')
1497  detailOutput.extend('bxlumivalue','blob')
1498  detailOutput.extend('bxlumierror','blob')
1499  detailCondition=coral.AttributeList()
1500  detailCondition.extend('runnum','unsigned int')
1501  detailCondition.extend('algoname','string')
1502  detailCondition['runnum'].setData(runnum)
1503  detailCondition['algoname'].setData(algoname)
1504 
1505  query.addToTableList(parameters.lumisummaryname,'s')
1506  query.addToTableList(parameters.lumidetailname,'d')
1507  query.addToOutputList('s.CMSLSNUM','cmslsnum')
1508  query.addToOutputList('s.STARTORBIT','startorbit')
1509  query.addToOutputList('d.BXLUMIVALUE','bxlumivalue')
1510  query.addToOutputList('d.BXLUMIERROR','bxlumierror')
1511  query.addToOutputList('d.BXLUMIQUALITY','bxlumiquality')
1512  query.setCondition('s.RUNNUM=:runnum and d.ALGONAME=:algoname and s.LUMISUMMARY_ID=d.LUMISUMMARY_ID',detailCondition)
1513  query.defineOutput(detailOutput)
1514  cursor=query.execute()
1515  while cursor.next():
1516  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1517  bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
1518  bxlumierror=cursor.currentRow()['bxlumierror'].data()
1519  startorbit=cursor.currentRow()['startorbit'].data()
1520 
1521  bxlumivalueArray=array.array('f')
1522  bxlumivalueArray.fromstring(bxlumivalue.readline())
1523  bxlumierrorArray=array.array('f')
1524  bxlumierrorArray.fromstring(bxlumierror.readline())
1525  xingLum=[]
1526  #apply selection criteria
1527  maxlumi=max(bxlumivalueArray)*parameters.normFactor
1528  for index,lum in enumerate(bxlumivalueArray):
1529  lum *= parameters.normFactor
1530  lumierror = bxlumierrorArray[index]*parameters.normFactor
1531  if lum<max(parameters.xingMinLum,maxlumi*0.2):
1532  continue
1533  xingLum.append( (index,lum,lumierror) )
1534  if len(xingLum)!=0:
1535  result[(startorbit,cmslsnum)]=xingLum
1536  return result
1537 
1538 def lumidetailByrunByAlgo(queryHandle,runnum,algoname='OCC1'):
1539  '''
1540  select s.cmslsnum,d.bxlumivalue,d.bxlumierror,d.bxlumiquality,s.startorbit from LUMIDETAIL d,LUMISUMMARY s where s.runnum=:runnum and d.algoname=:algoname and s.lumisummary_id=d.lumisummary_id order by s.startorbit
1541  output: [[cmslsnum,bxlumivalue,bxlumierror,bxlumiquality,startorbit]]
1542  since the output is ordered by time, it has to be in seq list format
1543  '''
1544  result=[]
1545  queryHandle.addToTableList(nameDealer.lumidetailTableName(),'d')
1546  queryHandle.addToTableList(nameDealer.lumisummaryTableName(),'s')
1547  queryCondition=coral.AttributeList()
1548  queryCondition.extend('runnum','unsigned int')
1549  queryCondition.extend('algoname','string')
1550  queryCondition['runnum'].setData(int(runnum))
1551  queryCondition['algoname'].setData(algoname)
1552  queryHandle.addToOutputList('s.CMSLSNUM','cmslsnum')
1553  queryHandle.addToOutputList('d.BXLUMIVALUE','bxlumivalue')
1554  queryHandle.addToOutputList('d.BXLUMIERROR','bxlumierror')
1555  queryHandle.addToOutputList('d.BXLUMIQUALITY','bxlumiquality')
1556  queryHandle.addToOutputList('s.STARTORBIT','startorbit')
1557  queryHandle.setCondition('s.runnum=:runnum and d.algoname=:algoname and s.lumisummary_id=d.lumisummary_id',queryCondition)
1558  queryResult=coral.AttributeList()
1559  queryResult.extend('cmslsnum','unsigned int')
1560  queryResult.extend('bxlumivalue','blob')
1561  queryResult.extend('bxlumierror','blob')
1562  queryResult.extend('bxlumiquality','blob')
1563  queryResult.extend('startorbit','unsigned int')
1564  queryHandle.addToOrderList('s.STARTORBIT')
1565  queryHandle.defineOutput(queryResult)
1566  cursor=queryHandle.execute()
1567  while cursor.next():
1568  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1569  bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
1570  bxlumierror=cursor.currentRow()['bxlumierror'].data()
1571  bxlumiquality=cursor.currentRow()['bxlumiquality'].data()
1572  startorbit=cursor.currentRow()['startorbit'].data()
1573  result.append([cmslsnum,bxlumivalue,bxlumierror,bxlumiquality,startorbit])
1574  return result
1575 
1576 def lumidetailAllalgosByrun(queryHandle,runnum):
1577  '''
1578  select s.cmslsnum,d.bxlumivalue,d.bxlumierror,d.bxlumiquality,d.algoname,s.startorbit from LUMIDETAIL d,LUMISUMMARY s where s.runnum=:runnumber and s.lumisummary_id=d.lumisummary_id order by s.startorbit,d.algoname
1579  output: {algoname:{cmslsnum:[bxlumivalue,bxlumierror,bxlumiquality,startorbit]}}
1580  '''
1581  result={}
1582  queryHandle.addToTableList(nameDealer.lumidetailTableName(),'d')
1583  queryHandle.addToTableList(nameDealer.lumisummaryTableName(),'s')
1584  queryCondition=coral.AttributeList()
1585  queryCondition.extend('runnum','unsigned int')
1586  queryCondition['runnum'].setData(int(runnum))
1587  queryHandle.addToOutputList('s.CMSLSNUM','cmslsnum')
1588  queryHandle.addToOutputList('d.BXLUMIVALUE','bxlumivalue')
1589  queryHandle.addToOutputList('d.BXLUMIERROR','bxlumierror')
1590  queryHandle.addToOutputList('d.BXLUMIQUALITY','bxlumiquality')
1591  queryHandle.addToOutputList('d.ALGONAME','algoname')
1592  queryHandle.addToOutputList('s.STARTORBIT','startorbit')
1593  queryHandle.setCondition('s.RUNNUM=:runnum and s.LUMISUMMARY_ID=d.LUMISUMMARY_ID',queryCondition)
1594  queryResult=coral.AttributeList()
1595  queryResult.extend('cmslsnum','unsigned int')
1596  queryResult.extend('bxlumivalue','blob')
1597  queryResult.extend('bxlumierror','blob')
1598  queryResult.extend('bxlumiquality','blob')
1599  queryResult.extend('algoname','string')
1600  queryResult.extend('startorbit','unsigned int')
1601  queryHandle.addToOrderList('startorbit')
1602  queryHandle.addToOrderList('algoname')
1603  queryHandle.defineOutput(queryResult)
1604  cursor=queryHandle.execute()
1605  while cursor.next():
1606  cmslsnum=cursor.currentRow()['cmslsnum'].data()
1607  bxlumivalue=cursor.currentRow()['bxlumivalue'].data()
1608  bxlumierror=cursor.currentRow()['bxlumierror'].data()
1609  bxlumiquality=cursor.currentRow()['bxlumiquality'].data()
1610  algoname=cursor.currentRow()['algoname'].data()
1611  startorbit=cursor.currentRow()['startorbit'].data()
1612  if not result.has_key(algoname):
1613  dataPerAlgo={}
1614  dataPerAlgo[cmslsnum]=[bxlumivalue,bxlumierror,bxlumiquality,startorbit]
1615  result[algoname]=dataPerAlgo
1616  else:
1617  result[algoname][cmslsnum]=[bxlumivalue,bxlumierror,bxlumiquality,startorbit]
1618  return result
1619 
1620 def hlttrgMappingByrun(queryHandle,runnum):
1621  '''
1622  select m.hltpathname,m.l1seed from cmsrunsummary r,trghltmap m where r.runnum=:runnum and m.hltkey=r.hltkey
1623  output: {hltpath:l1seed}
1624  '''
1625  result={}
1626  queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName(),'r')
1627  queryHandle.addToTableList(nameDealer.trghltMapTableName(),'m')
1628  queryCondition=coral.AttributeList()
1629  queryCondition.extend('runnum','unsigned int')
1630  queryCondition['runnum'].setData(int(runnum))
1631  queryHandle.addToOutputList('m.HLTPATHNAME','hltpathname')
1632  queryHandle.addToOutputList('m.L1SEED','l1seed')
1633  queryHandle.setCondition('r.RUNNUM=:runnum and m.HLTKEY=r.HLTKEY',queryCondition)
1634  queryResult=coral.AttributeList()
1635  queryResult.extend('hltpathname','string')
1636  queryResult.extend('l1seed','string')
1637  queryHandle.defineOutput(queryResult)
1638  cursor=queryHandle.execute()
1639  while cursor.next():
1640  hltpathname=cursor.currentRow()['hltpathname'].data()
1641  l1seed=cursor.currentRow()['l1seed'].data()
1642  if not result.has_key(hltpathname):
1643  result[hltpathname]=l1seed
1644  return result
1645 
1646 def runsByfillrange(queryHandle,minFill,maxFill):
1647  '''
1648  find all runs in the fill range inclusive
1649  select runnum,fillnum from cmsrunsummary where fillnum>=:minFill and fillnum<=:maxFill
1650  output: fillDict={fillnum:[runlist]}
1651  '''
1652  result={}
1653  queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
1654  queryCondition=coral.AttributeList()
1655  queryCondition.extend('minFill','unsigned int')
1656  queryCondition.extend('maxFill','unsigned int')
1657  queryCondition['minFill'].setData(int(minFill))
1658  queryCondition['maxFill'].setData(int(maxFill))
1659  queryHandle.addToOutputList('RUNNUM','runnum')
1660  queryHandle.addToOutputList('FILLNUM','fillnum')
1661  queryHandle.setCondition('FILLNUM>=:minFill and FILLNUM<=:maxFill',queryCondition)
1662  queryResult=coral.AttributeList()
1663  queryResult.extend('runnum','unsigned int')
1664  queryResult.extend('fillnum','unsigned int')
1665  queryHandle.defineOutput(queryResult)
1666  cursor=queryHandle.execute()
1667  while cursor.next():
1668  runnum=cursor.currentRow()['runnum'].data()
1669  fillnum=cursor.currentRow()['fillnum'].data()
1670  if not result.has_key(fillnum):
1671  result[fillnum]=[runnum]
1672  else:
1673  result[fillnum].append(runnum)
1674  return result
1675 
1676 def runsByTimerange(queryHandle,minTime,maxTime):
1677  '''
1678  find all runs in the time range inclusive
1679  the selected run must have started after minTime and finished by maxTime
1680  select runnum,to_char(startTime),to_char(stopTime) from cmsrunsummary where startTime>=timestamp(minTime) and stopTime<=timestamp(maxTime);
1681  input: minTime,maxTime in python obj datetime.datetime
1682  output: {runnum:[starttime,stoptime]} return in python obj datetime.datetime
1683  '''
1684  t=lumiTime.lumiTime()
1685  result={}
1686  coralminTime=coral.TimeStamp(minTime.year,minTime.month,minTime.day,minTime.hour,minTime.minute,minTime.second,0)
1687  coralmaxTime=coral.TimeStamp(maxTime.year,maxTime.month,maxTime.day,maxTime.hour,maxTime.minute,maxTime.second,0)
1688  queryHandle.addToTableList(nameDealer.cmsrunsummaryTableName())
1689  queryCondition=coral.AttributeList()
1690  queryCondition.extend('minTime','time stamp')
1691  queryCondition.extend('maxTime','time stamp')
1692  queryCondition['minTime'].setData(coralminTime)
1693  queryCondition['maxTime'].setData(coralmaxTime)
1694  queryHandle.addToOutputList('RUNNUM','runnum')
1695  queryHandle.addToOutputList('TO_CHAR(STARTTIME,\''+t.coraltimefm+'\')','starttime')
1696  queryHandle.addToOutputList('TO_CHAR(STOPTIME,\''+t.coraltimefm+'\')','stoptime')
1697  queryHandle.setCondition('STARTTIME>=:minTime and STOPTIME<=:maxTime',queryCondition)
1698  queryResult=coral.AttributeList()
1699  queryResult.extend('runnum','unsigned int')
1700  queryResult.extend('starttime','string')
1701  queryResult.extend('stoptime','string')
1702  queryHandle.defineOutput(queryResult)
1703  cursor=queryHandle.execute()
1704  while cursor.next():
1705  runnum=cursor.currentRow()['runnum'].data()
1706  starttimeStr=cursor.currentRow()['starttime'].data()
1707  stoptimeStr=cursor.currentRow()['stoptime'].data()
1708  if not result.has_key(runnum):
1709  result[runnum]=[t.StrToDatetime(starttimeStr),t.StrToDatetime(stoptimeStr)]
1710  return result
1711 
1712 if __name__=='__main__':
1713  msg=coral.MessageStream('')
1714  #msg.setMsgVerbosity(coral.message_Level_Debug)
1715  msg.setMsgVerbosity(coral.message_Level_Error)
1716  os.environ['CORAL_AUTH_PATH']='/afs/cern.ch/cms/DB/lumi'
1717  svc = coral.ConnectionService()
1718  connectstr='oracle://cms_orcoff_prod/cms_lumi_prod'
1719  session=svc.connect(connectstr,accessMode=coral.access_ReadOnly)
1720  session.typeConverter().setCppTypeForSqlType("unsigned int","NUMBER(10)")
1721  session.typeConverter().setCppTypeForSqlType("unsigned long long","NUMBER(20)")
1722  session.transaction().start(True)
1723  schema=session.nominalSchema()
1724  allruns=allruns(schema,requireLumisummary=True,requireTrg=True,requireHlt=True)
1725  print 'allruns in runsummary and lumisummary and trg and hlt ',len(allruns)
1726  #q=schema.newQuery()
1727  #runsummaryOut=runsummaryByrun(q,139400)
1728  #del q
1729  #q=schema.newQuery()
1730  #lumisummaryOut=lumisummaryByrun(q,139400,'0001')
1731  #del q
1732  #q=schema.newQuery()
1733  #lumisummaryOutStablebeam7TeV=lumisummaryByrun(q,139400,'0001',beamstatus='STABLE BEAMS',beamenergy=3.5E003,beamenergyfluctuation=0.09)
1734  #del q
1735  #q=schema.newQuery()
1736  #lumitotal=lumisumByrun(q,139400,'0001')
1737  #del q
1738  #q=schema.newQuery()
1739  #lumitotalStablebeam7TeV=lumisumByrun(q,139400,'0001',beamstatus='STABLE BEAMS',beamenergy=3.5E003,beamenergyfluctuation=0.09)
1740  #del q
1741  #q=schema.newQuery()
1742  #trgbitzero=trgbitzeroByrun(q,139400)
1743  #del q
1744  #q=schema.newQuery()
1745  #lumijointrg=lumisummarytrgbitzeroByrun(q,135525,'0001')
1746  #del q
1747  #q=schema.newQuery()
1748  #lumijointrgStablebeam7TeV=lumisummarytrgbitzeroByrun(q,135525,'0001',beamstatus='STABLE BEAMS',beamenergy=3.5E003,beamenergyfluctuation=0.09)
1749  #del q
1750  #q=schema.newQuery()
1751  #trgforbit=trgBybitnameByrun(q,139400,'L1_ZeroBias')
1752  #del q
1753  #q=schema.newQuery()
1754  #trgallbits=trgAllbitsByrun(q,139400)
1755  #del q
1756  #q=schema.newQuery()
1757  #hltbypath=hltBypathByrun(q,139400,'HLT_Mu5')
1758  #del q
1759  #q=schema.newQuery()
1760  #hltallpath=hltAllpathByrun(q,139400)
1761  #del q
1762  #q=schema.newQuery()
1763  #hlttrgmap=hlttrgMappingByrun(q,139400)
1764  #del q
1765  #q=schema.newQuery()
1766  #occ1detail=lumidetailByrunByAlgo(q,139400,'OCC1')
1767  #del q
1768  #q=schema.newQuery()
1769  #alldetail=lumidetailAllalgosByrun(q,139400)
1770  #del q
1771  #q=schema.newQuery()
1772  #runsbyfill=runsByfillrange(q,1150,1170)
1773  #del q
1774  #now=datetime.datetime.now()
1775  #aweek=datetime.timedelta(weeks=1)
1776  #lastweek=now-aweek
1777  #print lastweek
1778  #q=schema.newQuery()
1779  #runsinaweek=runsByTimerange(q,lastweek,now)
1780  #del q
1781  q=schema.newQuery()
1782  allfills=allfills(q)
1783  del q
1784  session.transaction().commit()
1785  del session
1786  del svc
1787  #print 'runsummaryByrun : ',runsummaryOut
1788  #print
1789  #print 'lumisummaryByrun : ',lumisummaryOut
1790  #print '######'
1791  #print 'lumisummaryByrun stable beams 7TeV : ',lumisummaryOutStablebeam7TeV
1792  #print '######'
1793  #print 'totallumi : ',lumitotal
1794  #print
1795  #print
1796  #print 'totallumi stable beam and 7TeV: ',lumitotalStablebeam7TeV
1797  #print
1798  #print 'trgbitzero : ',trgbitzero
1799  #print
1800  #print 'lumijointrg : ', lumijointrg
1801  #print 'total LS : ',len(lumijointrg)
1802  #print 'lumijointrg stable beams 7TeV :', lumijointrgStablebeam7TeV
1803  #print 'total LS : ',len(lumijointrgStablebeam7TeV)
1804  #print 'trgforbit L1_ZeroBias ',trgforbit
1805  #print
1806  #print 'trgallbits ',trgallbits[1] #big query. be aware of speed
1807  #print
1808  #print 'hltforpath HLT_Mu5',hltbypath
1809  #print
1810  #print 'hltallpath ',hltallpath
1811  #print
1812  #print 'hlttrgmap ',hlttrgmap
1813  #print
1814  #print 'lumidetail occ1 ',len(occ1detail)
1815  #print
1816  #print 'runsbyfill ',runsbyfill
1817  #print
1818  #print 'runsinaweek ',runsinaweek.keys()
1819  print 'all fills ',allfills
start
Definition: start.py:1
lumiQueryAPI.recordedLumiForRange
def recordedLumiForRange
Definition: lumiQueryAPI.py:98
lumiQueryAPI.recordedLumiForRun
def recordedLumiForRun
Definition: lumiQueryAPI.py:201
lumiQueryAPI.calculateEffective
def calculateEffective
Definition: lumiQueryAPI.py:430
lumiQueryAPI.printDeliveredLumi
def printDeliveredLumi
Definition: lumiQueryAPI.py:380
nameDealer.lumivalidationTableName
def lumivalidationTableName
Definition: nameDealer.py:56
nameDealer.lumisummaryTableName
def lumisummaryTableName
Definition: nameDealer.py:20
nameDealer.trghltMapTableName
def trghltMapTableName
Definition: nameDealer.py:47
nameDealer.hltTableName
def hltTableName
Definition: nameDealer.py:38
lumiQueryAPI.ParametersObject
==============temporarilly here======###
Definition: lumiQueryAPI.py:22
lumiQueryAPI.lumidetailByrunByAlgo
def lumidetailByrunByAlgo
Definition: lumiQueryAPI.py:1538
nameDealer.lumidetailTableName
def lumidetailTableName
Definition: nameDealer.py:25
max
const T & max(const T &a, const T &b)
Definition: MaterialBudgetTrackerHistos.cc:4
lumiQueryAPI.deliveredLumiForRun
def deliveredLumiForRun
Definition: lumiQueryAPI.py:143
lumiQueryAPI.lumidetailAllalgosByrun
def lumidetailAllalgosByrun
Definition: lumiQueryAPI.py:1576
lumiQueryAPI.xingLuminosityForRun
def xingLuminosityForRun
Definition: lumiQueryAPI.py:760
lumiQueryAPI.lumisummarytrgbitzeroByrun
def lumisummarytrgbitzeroByrun
Definition: lumiQueryAPI.py:1212
CommonUtil.count_dups
def count_dups
Definition: CommonUtil.py:43
lumiQueryAPI.calibratedDetailForRunLimitresult
def calibratedDetailForRunLimitresult
Definition: lumiQueryAPI.py:1488
lumiQueryAPI.getDeadfractions
def getDeadfractions
Definition: lumiQueryAPI.py:445
lumiQueryAPI.hlttrgMappingByrun
def hlttrgMappingByrun
Definition: lumiQueryAPI.py:1620
lumiQueryAPI.deliveredLumiForRange
def deliveredLumiForRange
Definition: lumiQueryAPI.py:82
dbtoconf.object
list object
Definition: dbtoconf.py:77
lumiQueryAPI.calculateTotalRecorded
def calculateTotalRecorded
Definition: lumiQueryAPI.py:395
lumiQueryAPI.beamIntensityForRun
def beamIntensityForRun
Definition: lumiQueryAPI.py:1437
nameDealer.trgTableName
def trgTableName
Definition: nameDealer.py:35
if
perl if(1 lt scalar(@::datatypes))
Definition: edlooper.cc:31
lumiQueryAPI.printOverviewData
def printOverviewData
Definition: lumiQueryAPI.py:667
lumiQueryAPI.printRecordedLumi
def printRecordedLumi
Definition: lumiQueryAPI.py:517
lumiQueryAPI.splitlistToRangeString
def splitlistToRangeString
Definition: lumiQueryAPI.py:414
lumiQueryAPI.dumpRecordedLumi
def dumpRecordedLumi
Definition: lumiQueryAPI.py:626
nameDealer.cmsrunsummaryTableName
def cmsrunsummaryTableName
Definition: nameDealer.py:14
Generated for CMSSW Reference Manual by doxygen 1.8.5