CMS 3D CMS Logo
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Pages
revisionDML.py
Go to the documentation of this file.
1 ############################################################
2 # LumiDB Revision and Versioning API
3 #
4 # Author: Zhen Xie
5 ############################################################
6 
7 import coral
8 from RecoLuminosity.LumiDB import nameDealer,idDealer,dbUtil
9 #==============================
10 # SELECT
11 #==============================
12 def revisionsInTag(schema,tagrevisionid,branchid):
13  '''
14  returns all revisions before tag in selected branch
15  select revision_id from revisions where revision_id!=0 and revision_id<tagrevisionid and branch_id=:branchid
16  result=[revision_id]
17  '''
18  result=[]
19  qHandle=schema.newQuery()
20  try:
21  nextbranches=[]
22  qHandle.addToTableList( nameDealer.revisionTableName() )
23  qHandle.addToOutputList('distinct BRANCH_ID','branch_id')
24  qCondition=coral.AttributeList()
25  qCondition.extend('branchid','unsigned long long')
26  qCondition['branchid'].setData(branchid)
27  qResult=coral.AttributeList()
28  qResult.extend('branch_id','unsigned long long')
29  qHandle.defineOutput(qResult)
30  qHandle.setCondition('BRANCH_ID>:branchid',qCondition)
31  cursor=qHandle.execute()
32  while next(cursor):
33  nextbranches.append(cursor.currentRow()['branch_id'].data())
34  del qHandle
35  candidates=[]
36  conditionStr='REVISION_ID!=0 and BRANCH_ID=:branchid and REVISION_ID<:tagrevisionid'
37  qHandle=schema.newQuery()
38  qHandle.addToTableList( nameDealer.revisionTableName() )
39  qHandle.addToOutputList('REVISION_ID','revision_id')
40  qCondition=coral.AttributeList()
41  qCondition.extend('branchid','unsigned long long')
42  qCondition.extend('tagrevisionid','unsigned long long')
43  qCondition['branchid'].setData(branchid)
44  qCondition['tagrevisionid'].setData(tagrevisionid)
45  qResult=coral.AttributeList()
46  qResult.extend('revision_id','unsigned long long')
47  qHandle.defineOutput(qResult)
48  qHandle.setCondition(conditionStr,qCondition)
49  cursor=qHandle.execute()
50  while next(cursor):
51  candidates.append(cursor.currentRow()['revision_id'].data())
52  del qHandle
53  for c in candidates:
54  if c in nextbranches:
55  continue
56  result.append(c)
57  return result
58  except:
59  if qHandle:del qHandle
60  raise
61 def revisionsInBranch(schema,branchid):
62  '''
63  returns all revision values in a branch
64  result=[revision_id]
65  select distinct branch_id from revisions where branch_id>:branchid;
66  select revision_id from revisions where branch_id=:branchid ;
67  if the branchid matches and the revisionid is not in the branchid collection,not 0, then this revision is in the branch
68  require also revisionid>branchid
69  '''
70  result=[]
71  qHandle=schema.newQuery()
72  try:
73  nextbranches=[]
74  qHandle.addToTableList( nameDealer.revisionTableName() )
75  qHandle.addToOutputList('distinct BRANCH_ID','branch_id')
76  qCondition=coral.AttributeList()
77  qCondition.extend('branchid','unsigned long long')
78  qCondition['branchid'].setData(branchid)
79  qResult=coral.AttributeList()
80  qResult.extend('branch_id','unsigned long long')
81  qHandle.defineOutput(qResult)
82  qHandle.setCondition('BRANCH_ID>:branchid',qCondition)
83  cursor=qHandle.execute()
84  while next(cursor):
85  nextbranches.append(cursor.currentRow()['branch_id'].data())
86  del qHandle
87  candidates=[]
88  conditionStr='BRANCH_ID=:branchid and REVISION_ID!=0'
89  qHandle=schema.newQuery()
90  qHandle.addToTableList( nameDealer.revisionTableName() )
91  qHandle.addToOutputList('REVISION_ID','revision_id')
92  qCondition=coral.AttributeList()
93  qCondition.extend('branchid','unsigned long long')
94  qCondition['branchid'].setData(branchid)
95  qResult=coral.AttributeList()
96  qResult.extend('revision_id','unsigned long long')
97  qHandle.defineOutput(qResult)
98  qHandle.setCondition(conditionStr,qCondition)
99  cursor=qHandle.execute()
100  while next(cursor):
101  candidates.append(cursor.currentRow()['revision_id'].data())
102  del qHandle
103  for c in candidates:
104  if c in nextbranches:
105  continue
106  result.append(c)
107  return result
108  except:
109  if qHandle: del qHandle
110  raise
111 
112 def branchType(schema,name):
113  '''
114  output: tag,branch
115  the difference between tag and branch: tag is an empty branch
116  select count(revision_id) from revisions where branch_name=:name
117  if >0: is real branch
118  else: is tag
119  '''
120  result='tag'
121  try:
122  qHandle=schema.newQuery()
123  qHandle.addToTableList( nameDealer.revisionTableName() )
124  qHandle.addToOutputList('count(REVISION_ID)','nchildren')
125  qCondition=coral.AttributeList()
126  qCondition.extend('branch_name','string')
127  qCondition['branch_name'].setData(name)
128  qResult=coral.AttributeList()
129  qResult.extend('nchildren','unsigned int')
130  qHandle.defineOutput(qResult)
131  conditionStr='BRANCH_NAME=:branch_name'
132  qHandle.setCondition(conditionStr,qCondition)
133  cursor=qHandle.execute()
134  while next(cursor):
135  if cursor.currentRow()['nchildren'].data()>0:
136  result='branch'
137  del qHandle
138  return result
139  except :
140  raise
141 #def revisionsInBranch(schema,branchid):
142 # '''
143 # returns all revision values in a branch/tag
144 # result=[revision_id]
145 # select r.revision_id from revisions r where r.branch_id=:branchid and r.revision_id not in (select distinct a.branch_id from revisions a where a.branch_id>:branchid)
146 # '''
147 # result=[]
148 # try:
149 # qHandle=schema.newQuery()
150 # subquery=qHandle.defineSubQuery('B')
151 # subquery.addToTableList( nameDealer.revisionTableName(),'a' )
152 # subquery.addToOutputList('distinct a.BRANCH_ID')
153 # subqueryCondition=coral.AttributeList()
154 # subqueryCondition.extend('branchid','unsigned long long')
155 # subqueryCondition['branchid'].setData(branchid)
156 # subquery.setCondition('a.BRANCH_ID>:branchid',subqueryCondition)
157 #
158 # qHandle.addToTableList( nameDealer.revisionTableName(),'r' )
159 # qHandle.addToTableList( 'B')
160 # qHandle.addToOutputList('r.REVISION_ID','revision_id')
161 # qCondition=coral.AttributeList()
162 # qCondition.extend('branchid','unsigned long long')
163 # qCondition['branchid'].setData(branchid)
164 # qResult=coral.AttributeList()
165 # qResult.extend('revision_id','unsigned long long')
166 # qHandle.defineOutput(qResult)
167 # conditionStr='r.BRANCH_ID=:branchid AND r.REVISION_ID NOT IN B'
168 # qHandle.setCondition(conditionStr,qCondition)
169 # cursor=qHandle.execute()
170 # while cursor.next():
171 # result.append(cursor.currentRow()['revision_id'].data())
172 # del qHandle
173 # return result
174 # except :
175 # raise
176 
177 def revisionsInBranchName(schema,branchname):
178  '''
179  returns all revisions in a branch/tag by name
180  '''
181  result=[]
182  try:
183  (revision_id,branch_id)=branchInfoByName(schema,branchname)
184  result=revisionsInBranch(schema,revision_id)
185  return result
186  except :
187  raise
188 def entryInBranch(schema,datatableName,entryname,branch):
189  '''
190  whether an entry(by name) already exists in the given branch
191  select e.entry_id from entrytable e,revisiontable r where r.revision_id=e.revision_id and e.name=:entryname and r.branch_name=branchname/branch_id
192  input:
193  if isinstance(branch,str):byname
194  else: byid
195  output:entry_id/None
196  '''
197  try:
198  result=None
199  byname=False
200  if isinstance(branch,str):
201  byname=True
202  qHandle=schema.newQuery()
203  qHandle.addToTableList( nameDealer.entryTableName(datatableName),'e' )
204  qHandle.addToTableList( nameDealer.revisionTableName(),'r' )
205  qHandle.addToOutputList('e.ENTRY_ID','entry_id')
206  qCondition=coral.AttributeList()
207  qCondition.extend('entryname','string')
208  qCondition['entryname'].setData(entryname)
209  qConditionStr='r.REVISION_ID=e.REVISION_ID and e.NAME=:entryname and '
210  if byname:
211  qCondition.extend('branch_name','string')
212  qCondition['branch_name'].setData(branch)
213  qConditionStr+='r.BRANCH_NAME=:branch_name'
214  else:
215  qCondition.extend('branch_id','unsigned long long')
216  qCondition['branch_id'].setData(branch)
217  qConditionStr+='r.BRANCH_ID=:branch_id'
218  qResult=coral.AttributeList()
219  qResult.extend('entry_id','unsigned long long')
220  qHandle.defineOutput(qResult)
221  qHandle.setCondition(qConditionStr,qCondition)
222  cursor=qHandle.execute()
223  while next(cursor):
224  entry_id=cursor.currentRow()['entry_id'].data()
225  result=entry_id
226  del qHandle
227  return result
228  except :
229  raise
230 
231 def dataRevisionsOfEntry(schema,datatableName,entry,revrange):
232  '''
233  all data version of the given entry whose revision falls in branch revision range
234  select d.data_id,r.revision_id from datatable d, datarevmaptable r where d.entry_id(or name )=:entry and d.data_id=r.data_id
235  input: if isinstance(entry,str): d.entry_name=:entry ; else d.entry_id=:entry
236  output: [data_id]
237  '''
238  qHandle=schema.newQuery()
239  try:
240  result=[]
241  byname=False
242  if isinstance(entry,str):
243  byname=True
244  qHandle.addToTableList( datatableName,'d' )
245  qHandle.addToTableList( nameDealer.revmapTableName(datatableName), 'r')
246  qHandle.addToOutputList('d.DATA_ID','data_id')
247  qHandle.addToOutputList('r.REVISION_ID','revision_id')
248  qCondition=coral.AttributeList()
249  qConditionStr='d.DATA_ID=r.DATA_ID and '
250  if byname:
251  qCondition.extend('entry_name','string')
252  qCondition['entry_name'].setData(entry)
253  qConditionStr+='d.ENTRY_NAME=:entry_name'
254  else:
255  qCondition.extend('entry_id','unsigned long long')
256  qCondition['entry_id'].setData(entry)
257  qConditionStr+='d.ENTRY_ID=:entry_id'
258  qResult=coral.AttributeList()
259  qResult.extend('data_id','unsigned long long')
260  qResult.extend('revision_id','unsigned long long')
261  qHandle.defineOutput(qResult)
262  qHandle.setCondition(qConditionStr,qCondition)
263  cursor=qHandle.execute()
264  while next(cursor):
265  data_id=cursor.currentRow()['data_id'].data()
266  revision_id=cursor.currentRow()['revision_id'].data()
267  if revision_id in revrange:
268  result.append(data_id)
269  return result
270  except :
271  del qHandle
272  raise
273 
274 def latestDataRevisionOfEntry(schema,datatableName,entry,revrange):
275  '''
276  return max(data_id) of all datarevisionofEntry
277  '''
278  result=dataRevisionsOfEntry(schema,datatableName,entry,revrange)
279  if result and len(result)!=0: return max(result)
280  return None
281 
282 def branchInfoByName(schema,branchName):
283  '''
284  select (revision_id,branch_id) from revisions where name=:branchName
285  '''
286  try:
287  qHandle=schema.newQuery()
288  qHandle.addToTableList( nameDealer.revisionTableName() )
289  qHandle.addToOutputList('REVISION_ID','revision_id')
290  qHandle.addToOutputList('BRANCH_ID','branch_id')
291  qCondition=coral.AttributeList()
292  qCondition.extend('name','string')
293  qCondition['name'].setData(branchName)
294  qResult=coral.AttributeList()
295  qResult.extend('revision_id','unsigned long long')
296  qResult.extend('branch_id','unsigned long long')
297  qHandle.defineOutput(qResult)
298  qHandle.setCondition('NAME=:name',qCondition)
299  cursor=qHandle.execute()
300  revision_id=None
301  branch_id=None
302  while next(cursor):
303  revision_id=cursor.currentRow()['revision_id'].data()
304  branch_id=cursor.currentRow()['branch_id'].data()
305  del qHandle
306  return (revision_id,branch_id)
307  except Exception as e :
308  raise RuntimeError(' revisionDML.branchInfoByName: '+str(e))
309 
310 
311 #=======================================================
312 #
313 # INSERT requires in update transaction
314 #
315 #=======================================================
316 def bookNewEntry(schema,datatableName):
317  '''
318  allocate new revision_id,entry_id,data_id
319  '''
320  try:
321  entrytableName=nameDealer.entryTableName(datatableName)
322  iddealer=idDealer.idDealer(schema)
323  revision_id=iddealer.generateNextIDForTable( nameDealer.revisionTableName() )
324  data_id=iddealer.generateNextIDForTable( datatableName)
325  entry_id=iddealer.generateNextIDForTable( nameDealer.entryTableName(datatableName) )
326  return (revision_id,entry_id,data_id)
327  except:
328  raise
329 
330 def bookNewRevision(schema,datatableName):
331  '''
332  allocate new revision_id,data_id
333  '''
334  try:
335  iddealer=idDealer.idDealer(schema)
336  revision_id=iddealer.generateNextIDForTable( nameDealer.revisionTableName() )
337  data_id=iddealer.generateNextIDForTable(datatableName)
338  return (revision_id,data_id)
339  except:
340  raise
341 
342 def addEntry(schema,datatableName,entryinfo,branchinfo):
343  '''
344  input:
345  entryinfo (revision_id(0),entry_id(1),entry_name(2),data_id(3))
346  branchinfo (branch_id,branch_name)
347  1.allocate and insert a new revision into the revisions table
348  2.allocate and insert a new entry into the entry table with the new revision
349  3.inset into data_rev table with new data_id ,revision)id mapping
350 
351  insert into revisions(revision_id,branch_id,branch_name,comment,ctime) values()
352  insert into datatablename_entries (entry_id,revision_id) values()
353  insert into datatablename_rev(data_id,revision_id) values()
354  '''
355  try:
356  revisiontableName=nameDealer.revisionTableName()
357  entrytableName=nameDealer.entryTableName(datatableName)
358  revtableName=nameDealer.revmapTableName(datatableName)
359 
360  db=dbUtil.dbUtil(schema)
361  tabrowDefDict={}
362  tabrowDefDict['REVISION_ID']='unsigned long long'
363  tabrowDefDict['BRANCH_ID']='unsigned long long'
364  tabrowDefDict['BRANCH_NAME']='string'
365  tabrowDefDict['CTIME']='time stamp'
366  tabrowValueDict={}
367  tabrowValueDict['REVISION_ID']=entryinfo[0]
368  tabrowValueDict['BRANCH_ID']=branchinfo[0]
369  tabrowValueDict['BRANCH_NAME']=branchinfo[1]
370  tabrowValueDict['CTIME']=coral.TimeStamp()
371  db.insertOneRow(revisiontableName,tabrowDefDict,tabrowValueDict)
372 
373  tabrowDefDict={}
374  tabrowDefDict['REVISION_ID']='unsigned long long'
375  tabrowDefDict['ENTRY_ID']='unsigned long long'
376  tabrowDefDict['NAME']='string'
377 
378  tabrowValueDict={}
379  tabrowValueDict['REVISION_ID']=entryinfo[0]
380  tabrowValueDict['ENTRY_ID']=entryinfo[1]
381  tabrowValueDict['NAME']=entryinfo[2]
382  db.insertOneRow(entrytableName,tabrowDefDict,tabrowValueDict)
383 
384  tabrowDefDict={}
385  tabrowDefDict['REVISION_ID']='unsigned long long'
386  tabrowDefDict['DATA_ID']='unsigned long long'
387  tabrowValueDict={}
388  tabrowValueDict['REVISION_ID']=entryinfo[0]
389  tabrowValueDict['DATA_ID']=entryinfo[3]
390  db.insertOneRow(revtableName,tabrowDefDict,tabrowValueDict)
391  except:
392  raise
393 
394 def addRevision(schema,datatableName,revisioninfo,branchinfo):
395  '''
396  1.insert a new revision into the revisions table
397  2.insert into data_id, revision_id pair to datatable_revmap
398  insert into revisions(revision_id,branch_id,branch_name,ctime) values()
399  insert into datatable_rev(data_id,revision_id) values())
400  input:
401  revisioninfo (revision_id(0),data_id(1))
402  branchinfo (branch_id(0),branch_name(1))
403  '''
404  try:
405  revisiontableName=nameDealer.revisionTableName()
406  revtableName=nameDealer.revmapTableName(datatableName)
407 
408  db=dbUtil.dbUtil(schema)
409  tabrowDefDict={}
410  tabrowDefDict['REVISION_ID']='unsigned long long'
411  tabrowDefDict['BRANCH_ID']='unsigned long long'
412  tabrowDefDict['BRANCH_NAME']='string'
413  tabrowDefDict['CTIME']='time stamp'
414 
415  tabrowValueDict={}
416  tabrowValueDict['REVISION_ID']=revisioninfo[0]
417  tabrowValueDict['BRANCH_ID']=branchinfo[0]
418  tabrowValueDict['BRANCH_NAME']=branchinfo[1]
419  tabrowValueDict['CTIME']=coral.TimeStamp()
420 
421  db.insertOneRow(revisiontableName,tabrowDefDict,tabrowValueDict)
422 
423  tabrowDefDict={}
424  tabrowDefDict['REVISION_ID']='unsigned long long'
425  tabrowDefDict['DATA_ID']='unsigned long long'
426  tabrowValueDict={}
427  tabrowValueDict['REVISION_ID']=revisioninfo[0]
428  tabrowValueDict['DATA_ID']=revisioninfo[1]
429  db.insertOneRow(revtableName,tabrowDefDict,tabrowValueDict)
430  except:
431  raise
432 def createBranch(schema,name,parentname,comment=''):
433  '''
434  create a new branch/tag under given parentnode
435  insert into revisions(revision_id,branch_id,branch_name,name,comment,ctime) values()
436  return (revisionid,parentid,parentname)
437  '''
438  try:
439  parentid=None
440  revisionid=0
441  if not parentname is None:
442  qHandle=schema.newQuery()
443  qHandle.addToTableList( nameDealer.revisionTableName() )
444  qHandle.addToOutputList( 'REVISION_ID','revision_id' )
445  qCondition=coral.AttributeList()
446  qCondition.extend('parentname','string')
447  qCondition['parentname'].setData(parentname)
448  qResult=coral.AttributeList()
449  qResult.extend('revision_id','unsigned long long')
450  qHandle.defineOutput(qResult)
451  qHandle.setCondition('NAME=:parentname',qCondition)
452  cursor=qHandle.execute()
453  while next(cursor):
454  parentid=cursor.currentRow()['revision_id'].data()
455  del qHandle
456  else:
457  parentname='ROOT'
458  iddealer=idDealer.idDealer(schema)
459  revisionid=iddealer.generateNextIDForTable( nameDealer.revisionTableName() )
460  db=dbUtil.dbUtil(schema)
461  tabrowDefDict={}
462  tabrowDefDict['REVISION_ID']='unsigned long long'
463  tabrowDefDict['BRANCH_ID']='unsigned long long'
464  tabrowDefDict['BRANCH_NAME']='string'
465  tabrowDefDict['NAME']='string'
466  tabrowDefDict['COMMENT']='string'
467  tabrowDefDict['CTIME']='time stamp'
468  tabrowValueDict={}
469  tabrowValueDict['REVISION_ID']=revisionid
470  tabrowValueDict['BRANCH_ID']=parentid
471  tabrowValueDict['BRANCH_NAME']=parentname
472  tabrowValueDict['NAME']=name
473  tabrowValueDict['COMMENT']=comment
474  tabrowValueDict['CTIME']=coral.TimeStamp()
475  db.insertOneRow(nameDealer.revisionTableName(),tabrowDefDict, tabrowValueDict )
476  return (revisionid,parentid,parentname)
477  except:
478  raise
479 
480 ################################################################
481 # Data Tagging API
482 ################################################################
483 def createDataTag(schema,tagname,lumitype='HF'):
484  '''
485  insert into tags(tagname,tagid,creationtime) values()
486  output:
487  tagname,tagid,creationtime
488  '''
489  if lumitype not in ['HF','PIXEL']:
490  raise ValueError('unknown lumitype '+lumitype)
491  if lumitype=='HF':
492  tagstablename=nameDealer.tagsTableName()
493  else:
494  tagstablename=nameDealer.pixeltagsTableName()
495  try:
496  iddealer=idDealer.idDealer(schema)
497  tagid=iddealer.generateNextIDForTable( tagstablename )
498  db=dbUtil.dbUtil(schema)
499  tabrowDefDict={}
500  tabrowDefDict['TAGNAME']='string'
501  tabrowDefDict['TAGID']='unsigned long long'
502  tabrowDefDict['CREATIONTIME']='time stamp'
503  tabrowValueDict={}
504  tabrowValueDict['TAGNAME']=tagname
505  tabrowValueDict['TAGID']=tagid
506  creationtime=coral.TimeStamp()
507  tabrowValueDict['CREATIONTIME']=creationtime
508  db.insertOneRow(tagstablename,tabrowDefDict, tabrowValueDict )
509  return (tagname,tagid,creationtime)
510  except:
511  raise
512 
513 def currentDataTag(schema,lumitype='HF'):
514  '''
515  select tagid,tagname from tags
516  output:(tagid,tagname)
517  '''
518  if lumitype not in ['HF','PIXEL']:
519  raise ValueError('unknown lumitype '+lumitype)
520  if lumitype=='HF':
521  tagstablename=nameDealer.tagsTableName()
522  else:
523  tagstablename=nameDealer.pixeltagsTableName()
524  tagmap={}
525  try:
526  qHandle=schema.newQuery()
527  qHandle.addToTableList( tagstablename )
528  qHandle.addToOutputList('TAGID')
529  qHandle.addToOutputList('TAGNAME')
530  qResult=coral.AttributeList()
531  qResult.extend('TAGID','unsigned long long')
532  qResult.extend('TAGNAME','string')
533  qHandle.defineOutput(qResult)
534  cursor=qHandle.execute()
535  currenttagid=0
536  while next(cursor):
537  tagid=cursor.currentRow()['TAGID'].data()
538  tagname=cursor.currentRow()['TAGNAME'].data()
539  tagmap[tagid]=tagname
540  del qHandle
541  if len(tagmap)!=0:
542  currenttagid=max(tagmap.keys())
543  if currenttagid==0:
544  raise 'currentDataTag: no tag available'
545  return (currenttagid,tagmap[currenttagid])
546  except:
547  raise
548 
549 def addRunToCurrentDataTag(schema,runnum,lumiid,trgid,hltid,lumitype='HF',comment=''):
550  '''
551  select tagid from tags
552  insert into tagruns(tagid,runnum,lumidataid,trgdataid,hltdataid,creationtime,comment) values(tagid,runnum,lumiid,trgid,hltid,creationtime,comment)
553  '''
554  if lumitype not in ['HF','PIXEL']:
555  raise ValueError('unknown lumitype '+lumitype)
556  if lumitype=='HF':
557  tagrunstablename=nameDealer.tagRunsTableName()
558  else:
559  tagrunstablename=nameDealer.pixeltagRunsTableName()
560  currenttagid=currentDataTag(schema,lumitype=lumitype)[0]
561  try:
562  db=dbUtil.dbUtil(schema)
563  tabrowDefDict={}
564  tabrowDefDict['TAGID']='unsigned long long'
565  tabrowDefDict['RUNNUM']='unsigned int'
566  tabrowDefDict['LUMIDATAID']='unsigned long long'
567  tabrowDefDict['TRGDATAID']='unsigned long long'
568  tabrowDefDict['HLTDATAID']='unsigned long long'
569  tabrowDefDict['CREATIONTIME']='time stamp'
570  tabrowDefDict['COMMENT']='string'
571  tabrowValueDict={}
572  tabrowValueDict['TAGID']=currenttagid
573  tabrowValueDict['RUNNUM']=runnum
574  tabrowValueDict['LUMIDATAID']=lumiid
575  tabrowValueDict['TRGDATAID']=trgid
576  tabrowValueDict['HLTDATAID']=hltid
577  tabrowValueDict['CREATIONTIME']=coral.TimeStamp()
578  tabrowValueDict['COMMENT']=comment
579  db.insertOneRow( tagrunstablename,tabrowDefDict, tabrowValueDict )
580  except:
581  raise
582 
583 def alldataTags(schema,lumitype='HF'):
584  '''
585  select tagname,tagid from tags,tagruns
586  if number of tags==1->open tag
587  if tagid is max ->open tag
588  for closed tag:
589  max run=max(runnum) where tagid=:tagid
590  min run
591  select min(runnum) from tagruns where tagid<=:tagid
592  for open tag:
593  max run=None
594  min run
595  select min(runnum) from tagruns where tagid<=:tagid
596  output:
597  {tagid:(name,minrun,maxrun,creationtime)}
598  '''
599  if lumitype not in ['HF','PIXEL']:
600  raise ValueError('unknown lumitype '+lumitype)
601  if lumitype=='HF':
602  tagstablename=nameDealer.tagsTableName()
603  tagrunstablename=nameDealer.tagRunsTableName()
604  else:
605  tagstablename=nameDealer.pixeltagsTableName()
606  tagrunstablename=nameDealer.pixeltagRunsTableName()
607  tagmap={}#{tagid:[tagname,minrun,maxrun,creationtime]}
608  try:
609  qHandle=schema.newQuery()
610  qHandle.addToTableList( tagstablename )
611  qCondition=coral.AttributeList()
612  qHandle.addToOutputList('TAGNAME')
613  qHandle.addToOutputList('TAGID')
614  qHandle.addToOutputList("TO_CHAR(CREATIONTIME,\'MM/DD/YY HH24:MI:SS\')",'creationtime')
615  qResult=coral.AttributeList()
616  qResult.extend('TAGNAME','string')
617  qResult.extend('TAGID','unsigned long long')
618  qResult.extend('creationtime','string')
619  qHandle.defineOutput(qResult)
620  cursor=qHandle.execute()
621  while next(cursor):
622  tagname=cursor.currentRow()['TAGNAME'].data()
623  tagid=cursor.currentRow()['TAGID'].data()
624  creationtime=cursor.currentRow()['creationtime'].data()
625  tagmap[tagid]=[tagname,0,0,creationtime]
626  del qHandle
627 
628  tagids=tagmap.keys()
629  allruns=set()
630  for tagid in tagids:
631  qConditionStr='TAGID<=:tagid'
632  qCondition=coral.AttributeList()
633  qCondition.extend('tagid','unsigned long long')
634  qCondition['tagid'].setData(tagid)
635  qHandle=schema.newQuery()
636  qHandle.addToTableList( tagrunstablename )
637  qResult=coral.AttributeList()
638  qResult.extend('RUNNUM','unsigned int')
639  qHandle.defineOutput(qResult)
640  qHandle.setCondition(qConditionStr,qCondition)
641  qHandle.addToOutputList('RUNNUM')
642  cursor=qHandle.execute()
643  while next(cursor):
644  rnum=cursor.currentRow()['RUNNUM'].data()
645  allruns.add(rnum)
646  minrun=0
647  maxrun=0
648  if len(allruns)!=0:
649  minrun=min(allruns)
650  maxrun=max(allruns)
651  tagmap[tagid][1]=minrun
652  if len(tagmap)>1 and tagid!=max(tagids):
653  tagmap[tagid][2]=maxrun
654  except:
655  raise
656  return tagmap
657 
658 def getDataTagId(schema,tagname,lumitype='HF'):
659  '''
660  select tagid from tags where tagname=:tagname
661  '''
662  if lumitype not in ['HF','PIXEL']:
663  raise ValueError('unknown lumitype '+lumitype)
664  if lumitype=='HF':
665  tagstablename=nameDealer.tagsTableName()
666  else:
667  tagstablename=nameDealer.pixeltagsTableName()
668  tagid=None
669  try:
670  qHandle=schema.newQuery()
671  qHandle.addToTableList( tagstablename )
672  qConditionStr='TAGNAME=:tagname'
673  qCondition=coral.AttributeList()
674  qCondition.extend('tagname','string')
675  qCondition['tagname'].setData(tagname)
676  qHandle.addToOutputList('TAGID')
677  qResult=coral.AttributeList()
678  qResult.extend('TAGID','unsigned long long')
679  qHandle.defineOutput(qResult)
680  qHandle.setCondition(qConditionStr,qCondition)
681  cursor=qHandle.execute()
682  while next(cursor):
683  if not cursor.currentRow()['TAGID'].isNull():
684  tagid=cursor.currentRow()['TAGID'].data()
685  del qHandle
686  except:
687  raise
688  return tagid
689 def dataIdsByTagName(schema,tagname,runlist=None,withcomment=False,lumitype='HF'):
690  '''
691  select tagid from tags where tagname=:tagname
692  input:
693  runlist: select run list, if None, all
694  output:
695  {run:(lumidataid,trgdataid,hltdataid,(creationtime,comment)}
696  '''
697  if lumitype not in ['HF','PIXEL']:
698  raise ValueError('unknown lumitype '+lumitype)
699  if lumitype=='HF':
700  tagstablename=nameDealer.tagsTableName()
701  else:
702  tagstablename=nameDealer.pixeltagsTableName()
703  tagid=None
704  try:
705  qHandle=schema.newQuery()
706  qHandle.addToTableList( tagstablename )
707  qConditionStr='TAGNAME=:tagname'
708  qCondition=coral.AttributeList()
709  qCondition.extend('tagname','string')
710  qCondition['tagname'].setData(tagname)
711  qHandle.addToOutputList('TAGID')
712  qResult=coral.AttributeList()
713  qResult.extend('TAGID','unsigned long long')
714  qHandle.defineOutput(qResult)
715  qHandle.setCondition(qConditionStr,qCondition)
716  cursor=qHandle.execute()
717  while next(cursor):
718  if not cursor.currentRow()['TAGID'].isNull():
719  tagid=cursor.currentRow()['TAGID'].data()
720  del qHandle
721  except:
722  raise
723  if tagid is None:
724  return {}
725  return dataIdsByTagId(schema,tagid,runlist=runlist,withcomment=withcomment,lumitype=lumitype)
726 
727 
728 def dataTagInfo(schema,tagname,runlist=None,lumitype='HF'):
729  '''
730  select tagid from tags where tagname=:tagname
731  select runnum,comment from tagruns where tagid<=:tagid
732  input:
733  runlist: select run list, if None, all
734  output:
735  {tagid:(name,minrun,maxrun,creationtime)}
736  '''
737  if lumitype not in ['HF','PIXEL']:
738  raise ValueError('unknown lumitype '+lumitype)
739  if lumitype=='HF':
740  tagstablename=nameDealer.tagsTableName()
741  tagrunstablename=nameDealer.tagRunsTableName()
742  else:
743  tagstablename=nameDealer.pixeltagsTableName()
744  tagrunstablename=nameDealer.pixeltagRunsTableName()
745  tagmap={}#{tagid:[tagname,minrun,maxrun,creationtime]}
746  try:
747  qHandle=schema.newQuery()
748  qHandle.addToTableList( tagstablename )
749  qCondition=coral.AttributeList()
750  qHandle.addToOutputList('TAGNAME')
751  qHandle.addToOutputList('TAGID')
752  qHandle.addToOutputList("TO_CHAR(CREATIONTIME,\'MM/DD/YY HH24:MI:SS\')",'creationtime')
753  qResult=coral.AttributeList()
754  qResult.extend('TAGNAME','string')
755  qResult.extend('TAGID','unsigned long long')
756  qResult.extend('creationtime','string')
757  qHandle.defineOutput(qResult)
758  cursor=qHandle.execute()
759  while next(cursor):
760  tagname=cursor.currentRow()['TAGNAME'].data()
761  tagid=cursor.currentRow()['TAGID'].data()
762  creationtime=cursor.currentRow()['creationtime'].data()
763  tagmap[tagid]=[tagname,0,0,creationtime]
764  del qHandle
765 
766  tagids=tagmap.keys()
767  allruns=set()
768  for tagid in tagids:
769  qConditionStr='TAGID<=:tagid'
770  qCondition=coral.AttributeList()
771  qCondition.extend('tagid','unsigned long long')
772  qCondition['tagid'].setData(tagid)
773  qHandle=schema.newQuery()
774  qHandle.addToTableList(tagrunstablename)
775  qResult=coral.AttributeList()
776  qResult.extend('RUNNUM','unsigned int')
777  qHandle.defineOutput(qResult)
778  qHandle.setCondition(qConditionStr,qCondition)
779  qHandle.addToOutputList('RUNNUM')
780  cursor=qHandle.execute()
781  while next(cursor):
782  rnum=cursor.currentRow()['RUNNUM'].data()
783  if runlist is not None and rnum not in runlist:
784  continue
785  allruns.add(rnum)
786  minrun=0
787  maxrun=0
788  if len(allruns)!=0:
789  minrun=min(allruns)
790  maxrun=max(allruns)
791  tagmap[tagid][1]=minrun
792  if len(tagmap)>1 and tagid!=max(tagids):
793  tagmap[tagid][2]=maxrun
794  except:
795  raise
796  return tagmap
797 
798 def dataIdsByTagId(schema,tagid,runlist=None,withcomment=False,lumitype='HF'):
799  '''
800  select runnum,lumidataid,trgdataid,hltdataid,comment from tagruns where TAGID<=:tagid;
801  input:
802  runlist: select run list, if None, all
803  output:
804  {run:(lumidataid,trgdataid,hltdataid,(creationtime,comment))}
805  '''
806  if lumitype not in ['HF','PIXEL']:
807  raise ValueError('unknown lumitype '+lumitype)
808  if lumitype=='HF':
809  tagrunstablename=nameDealer.tagRunsTableName()
810  else:
811  tagrunstablename=nameDealer.pixeltagRunsTableName()
812  result={}#{run:[lumiid,trgid,hltid,comment(optional)]}
813  commentdict={}#{(lumiid,trgid,hltid):[ctimestr,comment]}
814  try:
815  qHandle=schema.newQuery()
816  qHandle.addToTableList(tagrunstablename)
817  qConditionStr='TAGID<=:tagid'
818  qCondition=coral.AttributeList()
819  qCondition.extend('tagid','unsigned long long')
820  qCondition['tagid'].setData(tagid)
821  qResult=coral.AttributeList()
822  qResult.extend('RUNNUM','unsigned int')
823  qResult.extend('LUMIDATAID','unsigned long long')
824  qResult.extend('TRGDATAID','unsigned long long')
825  qResult.extend('HLTDATAID','unsigned long long')
826  if withcomment:
827  qResult.extend('COMMENT','string')
828  qResult.extend('creationtime','string')
829  qHandle.defineOutput(qResult)
830  qHandle.setCondition(qConditionStr,qCondition)
831  qHandle.addToOutputList('RUNNUM')
832  qHandle.addToOutputList('LUMIDATAID')
833  qHandle.addToOutputList('TRGDATAID')
834  qHandle.addToOutputList('HLTDATAID')
835  if withcomment:
836  qHandle.addToOutputList('COMMENT')
837  qHandle.addToOutputList("TO_CHAR(CREATIONTIME,\'MM/DD/YY HH24:MI:SS\')",'creationtime')
838  cursor=qHandle.execute()
839  while next(cursor):
840  runnum=cursor.currentRow()['RUNNUM'].data()
841  if runlist is not None and runnum not in runlist:
842  continue
843  lumidataid=0
844  if not cursor.currentRow()['LUMIDATAID'].isNull():
845  lumidataid=cursor.currentRow()['LUMIDATAID'].data()
846  trgdataid=0
847  if not cursor.currentRow()['TRGDATAID'].isNull():
848  trgdataid=cursor.currentRow()['TRGDATAID'].data()
849  hltdataid=0
850  if not cursor.currentRow()['HLTDATAID'].isNull():
851  hltdataid=cursor.currentRow()['HLTDATAID'].data()
852  if runnum not in result:
853  result[runnum]=[0,0,0]
854  if lumidataid>result[runnum][0]:
855  result[runnum][0]=lumidataid
856  if trgdataid>result[runnum][1]:
857  result[runnum][1]=trgdataid
858  if hltdataid>result[runnum][2]:
859  result[runnum][2]=hltdataid
860  if withcomment:
861  comment=''
862  creationtime=''
863  if not cursor.currentRow()['creationtime'].isNull():
864  creationtime=cursor.currentRow()['creationtime'].data()
865  if not cursor.currentRow()['COMMENT'].isNull():
866  comment=cursor.currentRow()['COMMENT'].data()
867  commentdict[(lumidataid,trgdataid,hltdataid)]=(creationtime,comment)
868  del qHandle
869  if withcomment:
870  for run,resultentry in result.items():
871  lumiid=resultentry[0]
872  trgid=resultentry[1]
873  hltid=resultentry[2]
874  if (lumiid,trgid,hltid) in commentdict:
875  resultentry.append(commentdict[(lumiid,trgid,hltid)])
876  elif (lumiid,0,0) in commentdict:
877  resultentry.append(commentdict[(lumiid,0,0)])
878  elif commentdict.has_ley((0,trgid,0)):
879  resultentry.append(commentdict[(0,trgid,0)])
880  elif commentdict.has_ley((0,0,hltid)):
881  resultentry.append(commentdict[(0,0,hltid)])
882  else:
883  resultentry.append(())
884 
885  except:
886  raise
887  return result
888 
889 def dataIdsByCurrentTag(schema,runlist=None,lumitype='HF'):
890  '''
891  dataIdsByTagId(schema,currenttagid,runlist)
892  output:
893  (currenttagname,{run:(lumidataid,trgdataid,hltdataid)})
894  '''
895  (currenttagid,currenttagname)=currentDataTag(schema)
896  result=dataIdsByTagId(schema,currenttagid,runlist=runlist,withcomment=False,lumitype=lumitype)
897  return (currenttagname,result)
898 
899 if __name__ == "__main__":
900  import sessionManager
901  import lumidbDDL
902  #myconstr='oracle://cms_orcoff_prep/cms_lumi_dev_offline'
903  #authpath='/afs/cern.ch/user/x/xiezhen'
904  myconstr='sqlite_file:test.db'
905  svc=sessionManager.sessionManager(myconstr,debugON=False)
906  session=svc.openSession(isReadOnly=False,cpp2sqltype=[('unsigned int','NUMBER(10)'),('unsigned long long','NUMBER(20)')])
907  schema=session.nominalSchema()
908  session.transaction().start(False)
909  tables=lumidbDDL.createTables(schema)
910  trunkinfo=createBranch(schema,'TRUNK',None,comment='main')
911  #print trunkinfo
912  datainfo=createBranch(schema,'DATA','TRUNK',comment='hold data')
913  #print datainfo
914  norminfo=createBranch(schema,'NORM','TRUNK',comment='hold normalization factor')
915  #print norminfo
916  (branchid,branchparent)=branchInfoByName(schema,'DATA')
917  databranchinfo=(branchid,'DATA')
918  print databranchinfo
919  for runnum in [1200,1211,1222,1233,1345,1222,1200]:
920  lumientryid=entryInBranch(schema,nameDealer.lumidataTableName(),str(runnum),'DATA')
921  trgentryid=entryInBranch(schema,nameDealer.trgdataTableName(),str(runnum),'DATA')
922  hltentryid=entryInBranch(schema,nameDealer.hltdataTableName(),str(runnum),'DATA')
923  if lumientryid is None:
924  (revision_id,entry_id,data_id)=bookNewEntry( schema,nameDealer.lumidataTableName() )
925  entryinfo=(revision_id,entry_id,str(runnum),data_id)
926  addEntry(schema,nameDealer.lumidataTableName(),entryinfo,databranchinfo)
927  #add data here
928  else:
929  revisioninfo=bookNewRevision( schema,nameDealer.lumidataTableName() )
930  addRevision(schema,nameDealer.lumidataTableName(),revisioninfo,databranchinfo)
931  #add data here
932  if trgentryid is None:
933  (revision_id,entry_id,data_id)=bookNewEntry( schema,nameDealer.trgdataTableName() )
934  entryinfo=(revision_id,entry_id,str(runnum),data_id)
935  addEntry(schema,nameDealer.trgdataTableName(),entryinfo,databranchinfo)
936  #add data here
937  else:
938  revisioninfo=bookNewRevision( schema,nameDealer.trgdataTableName() )
939  addRevision(schema,nameDealer.trgdataTableName(),revisioninfo,databranchinfo)
940  #add data here
941  if hltentryid is None:
942  (revision_id,entry_id,data_id)=bookNewEntry( schema,nameDealer.hltdataTableName() )
943  entryinfo=(revision_id,entry_id,str(runnum),data_id)
944  addEntry(schema,nameDealer.hltdataTableName(),entryinfo,databranchinfo)
945  #add data here
946  else:
947  revisioninfo=bookNewRevision( schema,nameDealer.hltdataTableName() )
948  addRevision(schema,nameDealer.hltdataTableName(),revisioninfo,databranchinfo)
949  #add data here
950 
951  session.transaction().commit()
952  print 'test reading'
953  session.transaction().start(True)
954  print branchType(schema,'DATA')
955  revlist=revisionsInBranchName(schema,'DATA')
956  print 'DATA revlist ',revlist
957  lumientry_id=entryInBranch(schema,nameDealer.lumidataTableName(),'1211','DATA')
958  print lumientry_id
959  latestrevision=latestDataRevisionOfEntry(schema,nameDealer.lumidataTableName(),lumientry_id,revlist)
960  print 'latest data_id for run 1211 ',latestrevision
961  session.transaction().commit()
962  del session
nameDealer.tagsTableName
def tagsTableName
Definition: nameDealer.py:67
start
Definition: start.py:1
revisionDML.branchInfoByName
def branchInfoByName
Definition: revisionDML.py:282
nameDealer.revmapTableName
def revmapTableName
Definition: nameDealer.py:100
nameDealer.tagRunsTableName
def tagRunsTableName
Definition: nameDealer.py:64
revisionDML.revisionsInBranchName
def revisionsInBranchName
Definition: revisionDML.py:177
nameDealer.revisionTableName
def revisionTableName
Definition: nameDealer.py:11
nameDealer.pixeltagRunsTableName
def pixeltagRunsTableName
Definition: nameDealer.py:70
revisionDML.createBranch
def createBranch
Definition: revisionDML.py:432
revisionDML.dataIdsByTagId
def dataIdsByTagId
Definition: revisionDML.py:798
revisionDML.addRunToCurrentDataTag
def addRunToCurrentDataTag
Definition: revisionDML.py:549
nameDealer.trgdataTableName
def trgdataTableName
Definition: nameDealer.py:46
nameDealer.hltdataTableName
def hltdataTableName
Definition: nameDealer.py:58
lumidbDDL.createTables
def createTables
Definition: lumidbDDL.py:8
revisionDML.dataIdsByTagName
def dataIdsByTagName
Definition: revisionDML.py:689
nameDealer.entryTableName
def entryTableName
Definition: nameDealer.py:103
min
T min(T a, T b)
Definition: MathUtil.h:58
nameDealer.lumidataTableName
def lumidataTableName
Definition: nameDealer.py:19
revisionDML.revisionsInBranch
def revisionsInBranch
Definition: revisionDML.py:61
revisionDML.getDataTagId
def getDataTagId
Definition: revisionDML.py:658
revisionDML.currentDataTag
def currentDataTag
Definition: revisionDML.py:513
revisionDML.dataRevisionsOfEntry
def dataRevisionsOfEntry
Definition: revisionDML.py:231
revisionDML.bookNewRevision
def bookNewRevision
Definition: revisionDML.py:330
revisionDML.dataIdsByCurrentTag
def dataIdsByCurrentTag
Definition: revisionDML.py:889
revisionDML.revisionsInTag
def revisionsInTag
Definition: revisionDML.py:12
revisionDML.bookNewEntry
def bookNewEntry
Definition: revisionDML.py:316
data
char data[epos_bytes_allocation]
Definition: EPOS_Wrapper.h:82
revisionDML.entryInBranch
def entryInBranch
Definition: revisionDML.py:188
GetRecoTauVFromDQM_MC_cff.next
tuple next
Definition: GetRecoTauVFromDQM_MC_cff.py:30
revisionDML.createDataTag
def createDataTag
Data Tagging API.
Definition: revisionDML.py:483
nameDealer.pixeltagsTableName
def pixeltagsTableName
Definition: nameDealer.py:73
revisionDML.latestDataRevisionOfEntry
def latestDataRevisionOfEntry
Definition: revisionDML.py:274
Generated for CMSSW Reference Manual by doxygen 1.8.5