Reference Manual

Go to the documentation of this file.
00001 ############################################################
00002 # LumiDB Revision and Versioning API
00003 #
00004 # Author:      Zhen Xie
00005 ############################################################
00006 
00007 import coral
00008 from RecoLuminosity.LumiDB import nameDealer,idDealer,dbUtil
00009 #==============================
00010 # SELECT
00011 #==============================
00012 def revisionsInTag(schema,tagrevisionid,branchid):
00013     '''
00014     returns all revisions before tag in selected branch
00015     select revision_id from revisions where revision_id!=0 and revision_id<tagrevisionid and branch_id=:branchid
00016     result=[revision_id]
00017     '''
00018     result=[]
00019     qHandle=schema.newQuery()
00020     try:
00021         nextbranches=[]
00022         qHandle.addToTableList( nameDealer.revisionTableName() )
00023         qHandle.addToOutputList('distinct BRANCH_ID','branch_id')
00024         qCondition=coral.AttributeList()
00025         qCondition.extend('branchid','unsigned long long')
00026         qCondition['branchid'].setData(branchid)
00027         qResult=coral.AttributeList()
00028         qResult.extend('branch_id','unsigned long long')
00029         qHandle.defineOutput(qResult)
00030         qHandle.setCondition('BRANCH_ID>:branchid',qCondition)
00031         cursor=qHandle.execute()
00032         while cursor.next():
00033             nextbranches.append(cursor.currentRow()['branch_id'].data())
00034         del qHandle
00035         candidates=[]
00036         conditionStr='REVISION_ID!=0 and BRANCH_ID=:branchid and REVISION_ID<:tagrevisionid'
00037         qHandle=schema.newQuery()
00038         qHandle.addToTableList( nameDealer.revisionTableName() )
00039         qHandle.addToOutputList('REVISION_ID','revision_id')
00040         qCondition=coral.AttributeList()
00041         qCondition.extend('branchid','unsigned long long')
00042         qCondition.extend('tagrevisionid','unsigned long long')
00043         qCondition['branchid'].setData(branchid)
00044         qCondition['tagrevisionid'].setData(tagrevisionid)
00045         qResult=coral.AttributeList()
00046         qResult.extend('revision_id','unsigned long long')
00047         qHandle.defineOutput(qResult)
00048         qHandle.setCondition(conditionStr,qCondition)
00049         cursor=qHandle.execute()
00050         while cursor.next():
00051             candidates.append(cursor.currentRow()['revision_id'].data())
00052         del qHandle
00053         for c in candidates:
00054             if c in nextbranches:
00055                 continue
00056             result.append(c)
00057         return result
00058     except:
00059         if qHandle:del qHandle
00060         raise
00061 def revisionsInBranch(schema,branchid):
00062     '''
00063     returns all revision values in a branch
00064     result=[revision_id]
00065     select distinct branch_id from revisions where branch_id>:branchid;
00066     select revision_id from revisions where branch_id=:branchid ;
00067     if the branchid matches and the revisionid is not in the branchid collection,not 0, then this revision is in the branch
00068     require also revisionid>branchid
00069     '''
00070     result=[]
00071     qHandle=schema.newQuery()
00072     try:
00073         nextbranches=[]
00074         qHandle.addToTableList( nameDealer.revisionTableName() )
00075         qHandle.addToOutputList('distinct BRANCH_ID','branch_id')
00076         qCondition=coral.AttributeList()
00077         qCondition.extend('branchid','unsigned long long')
00078         qCondition['branchid'].setData(branchid)
00079         qResult=coral.AttributeList()
00080         qResult.extend('branch_id','unsigned long long')
00081         qHandle.defineOutput(qResult)
00082         qHandle.setCondition('BRANCH_ID>:branchid',qCondition)
00083         cursor=qHandle.execute()
00084         while cursor.next():
00085             nextbranches.append(cursor.currentRow()['branch_id'].data())
00086         del qHandle
00087         candidates=[]
00088         conditionStr='BRANCH_ID=:branchid and REVISION_ID!=0'
00089         qHandle=schema.newQuery()
00090         qHandle.addToTableList( nameDealer.revisionTableName() )
00091         qHandle.addToOutputList('REVISION_ID','revision_id')
00092         qCondition=coral.AttributeList()
00093         qCondition.extend('branchid','unsigned long long')
00094         qCondition['branchid'].setData(branchid)
00095         qResult=coral.AttributeList()
00096         qResult.extend('revision_id','unsigned long long')
00097         qHandle.defineOutput(qResult)
00098         qHandle.setCondition(conditionStr,qCondition)
00099         cursor=qHandle.execute()
00100         while cursor.next():
00101             candidates.append(cursor.currentRow()['revision_id'].data())
00102         del qHandle
00103         for c in candidates:
00104             if c in nextbranches:
00105                 continue
00106             result.append(c)
00107         return result
00108     except:
00109         if qHandle: del qHandle
00110         raise
00111 
00112 def branchType(schema,name):
00113     '''
00114     output: tag,branch
00115     the difference between tag and branch: tag is an empty branch
00116     select count(revision_id) from revisions where branch_name=:name
00117     if >0: is real branch
00118     else: is tag
00119     '''
00120     result='tag'
00121     try:
00122         qHandle=schema.newQuery()
00123         qHandle.addToTableList( nameDealer.revisionTableName() )
00124         qHandle.addToOutputList('count(REVISION_ID)','nchildren')
00125         qCondition=coral.AttributeList()
00126         qCondition.extend('branch_name','string')
00127         qCondition['branch_name'].setData(name)
00128         qResult=coral.AttributeList()
00129         qResult.extend('nchildren','unsigned int')
00130         qHandle.defineOutput(qResult)
00131         conditionStr='BRANCH_NAME=:branch_name'
00132         qHandle.setCondition(conditionStr,qCondition)
00133         cursor=qHandle.execute()
00134         while cursor.next():
00135             if cursor.currentRow()['nchildren'].data()>0:
00136                 result='branch'                
00137         del qHandle
00138         return result
00139     except :
00140         raise 
00141 #def revisionsInBranch(schema,branchid):
00142 #    '''
00143 #    returns all revision values in a branch/tag
00144 #    result=[revision_id]
00145 #    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)
00146 #    '''
00147 #    result=[]
00148 #    try:
00149 #        qHandle=schema.newQuery()
00150 #        subquery=qHandle.defineSubQuery('B')
00151 #        subquery.addToTableList( nameDealer.revisionTableName(),'a' )
00152 #        subquery.addToOutputList('distinct a.BRANCH_ID')
00153 #        subqueryCondition=coral.AttributeList()
00154 #        subqueryCondition.extend('branchid','unsigned long long')
00155 #        subqueryCondition['branchid'].setData(branchid)
00156 #        subquery.setCondition('a.BRANCH_ID>:branchid',subqueryCondition)
00157 #        
00158 #        qHandle.addToTableList( nameDealer.revisionTableName(),'r' )
00159 #        qHandle.addToTableList( 'B')
00160 #        qHandle.addToOutputList('r.REVISION_ID','revision_id')
00161 #        qCondition=coral.AttributeList()
00162 #        qCondition.extend('branchid','unsigned long long')
00163 #        qCondition['branchid'].setData(branchid)
00164 #        qResult=coral.AttributeList()
00165 #        qResult.extend('revision_id','unsigned long long')
00166 #        qHandle.defineOutput(qResult)
00167 #        conditionStr='r.BRANCH_ID=:branchid AND r.REVISION_ID NOT IN B'
00168 #        qHandle.setCondition(conditionStr,qCondition)
00169 #        cursor=qHandle.execute()
00170 #        while cursor.next():
00171 #            result.append(cursor.currentRow()['revision_id'].data())
00172 #        del qHandle
00173 #        return result
00174 #    except :
00175 #        raise 
00176     
00177 def revisionsInBranchName(schema,branchname):
00178     '''
00179     returns all revisions in a branch/tag by name
00180     '''
00181     result=[]
00182     try:
00183         (revision_id,branch_id)=branchInfoByName(schema,branchname)
00184         result=revisionsInBranch(schema,revision_id)
00185         return result
00186     except :
00187         raise 
00188 def entryInBranch(schema,datatableName,entryname,branch):
00189     '''
00190     whether an entry(by name) already exists in the given branch
00191     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
00192     input:
00193         if isinstance(branch,str):byname
00194         else: byid
00195     output:entry_id/None
00196     '''
00197     try:
00198         result=None
00199         byname=False
00200         if isinstance(branch,str):
00201             byname=True
00202         qHandle=schema.newQuery()
00203         qHandle.addToTableList( nameDealer.entryTableName(datatableName),'e' )
00204         qHandle.addToTableList( nameDealer.revisionTableName(),'r' )
00205         qHandle.addToOutputList('e.ENTRY_ID','entry_id')
00206         qCondition=coral.AttributeList()
00207         qCondition.extend('entryname','string')
00208         qCondition['entryname'].setData(entryname)
00209         qConditionStr='r.REVISION_ID=e.REVISION_ID and e.NAME=:entryname and '
00210         if byname:
00211             qCondition.extend('branch_name','string')
00212             qCondition['branch_name'].setData(branch)
00213             qConditionStr+='r.BRANCH_NAME=:branch_name'
00214         else:
00215             qCondition.extend('branch_id','unsigned long long')
00216             qCondition['branch_id'].setData(branch)
00217             qConditionStr+='r.BRANCH_ID=:branch_id'
00218         qResult=coral.AttributeList()
00219         qResult.extend('entry_id','unsigned long long')
00220         qHandle.defineOutput(qResult)
00221         qHandle.setCondition(qConditionStr,qCondition)
00222         cursor=qHandle.execute()
00223         while cursor.next():
00224             entry_id=cursor.currentRow()['entry_id'].data()
00225             result=entry_id
00226         del qHandle
00227         return result
00228     except :
00229         raise 
00230 
00231 def dataRevisionsOfEntry(schema,datatableName,entry,revrange):
00232     '''
00233     all data version of the given entry whose revision falls in branch revision range
00234     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
00235     input: if isinstance(entry,str): d.entry_name=:entry ; else d.entry_id=:entry
00236     output: [data_id]
00237     '''
00238     qHandle=schema.newQuery()
00239     try:
00240         result=[]
00241         byname=False
00242         if isinstance(entry,str):
00243             byname=True
00244         qHandle.addToTableList( datatableName,'d' )
00245         qHandle.addToTableList( nameDealer.revmapTableName(datatableName), 'r')
00246         qHandle.addToOutputList('d.DATA_ID','data_id')
00247         qHandle.addToOutputList('r.REVISION_ID','revision_id')
00248         qCondition=coral.AttributeList()
00249         qConditionStr='d.DATA_ID=r.DATA_ID and '
00250         if byname:
00251             qCondition.extend('entry_name','string')
00252             qCondition['entry_name'].setData(entry)
00253             qConditionStr+='d.ENTRY_NAME=:entry_name'
00254         else:
00255             qCondition.extend('entry_id','unsigned long long')
00256             qCondition['entry_id'].setData(entry)
00257             qConditionStr+='d.ENTRY_ID=:entry_id'
00258         qResult=coral.AttributeList()
00259         qResult.extend('data_id','unsigned long long')
00260         qResult.extend('revision_id','unsigned long long')
00261         qHandle.defineOutput(qResult)
00262         qHandle.setCondition(qConditionStr,qCondition)
00263         cursor=qHandle.execute()
00264         while cursor.next():
00265             data_id=cursor.currentRow()['data_id'].data()
00266             revision_id=cursor.currentRow()['revision_id'].data()
00267             if revision_id in revrange:
00268                 result.append(data_id)
00269         return result
00270     except :
00271         del qHandle
00272         raise
00273 
00274 def latestDataRevisionOfEntry(schema,datatableName,entry,revrange):
00275     '''
00276     return max(data_id) of all datarevisionofEntry
00277     '''
00278     result=dataRevisionsOfEntry(schema,datatableName,entry,revrange)
00279     if result and len(result)!=0: return max(result)
00280     return None
00281     
00282 def branchInfoByName(schema,branchName):
00283     '''
00284     select (revision_id,branch_id) from revisions where name=:branchName
00285     '''
00286     try:
00287          qHandle=schema.newQuery()
00288          qHandle.addToTableList( nameDealer.revisionTableName() )
00289          qHandle.addToOutputList('REVISION_ID','revision_id')
00290          qHandle.addToOutputList('BRANCH_ID','branch_id')
00291          qCondition=coral.AttributeList()
00292          qCondition.extend('name','string')
00293          qCondition['name'].setData(branchName)
00294          qResult=coral.AttributeList()
00295          qResult.extend('revision_id','unsigned long long')
00296          qResult.extend('branch_id','unsigned long long')
00297          qHandle.defineOutput(qResult)
00298          qHandle.setCondition('NAME=:name',qCondition)
00299          cursor=qHandle.execute()
00300          revision_id=None
00301          branch_id=None
00302          while cursor.next():
00303              revision_id=cursor.currentRow()['revision_id'].data()
00304              branch_id=cursor.currentRow()['branch_id'].data()
00305          del qHandle
00306          return (revision_id,branch_id)
00307     except Exception,e :
00308         raise RuntimeError(' revisionDML.branchInfoByName: '+str(e))
00309     
00310 
00311 #=======================================================
00312 #
00313 #   INSERT requires in update transaction
00314 #
00315 #=======================================================
00316 def bookNewEntry(schema,datatableName):
00317     '''
00318     allocate new revision_id,entry_id,data_id
00319     '''
00320     try:
00321         entrytableName=nameDealer.entryTableName(datatableName)
00322         iddealer=idDealer.idDealer(schema)
00323         revision_id=iddealer.generateNextIDForTable( nameDealer.revisionTableName() )
00324         data_id=iddealer.generateNextIDForTable( datatableName)
00325         entry_id=iddealer.generateNextIDForTable( nameDealer.entryTableName(datatableName) )
00326         return (revision_id,entry_id,data_id)
00327     except:
00328         raise
00329     
00330 def bookNewRevision(schema,datatableName):
00331     '''
00332     allocate new revision_id,data_id
00333     '''
00334     try:
00335         iddealer=idDealer.idDealer(schema)
00336         revision_id=iddealer.generateNextIDForTable( nameDealer.revisionTableName() )
00337         data_id=iddealer.generateNextIDForTable(datatableName)
00338         return (revision_id,data_id)
00339     except:
00340         raise
00341      
00342 def addEntry(schema,datatableName,entryinfo,branchinfo):
00343     '''
00344     input:
00345         entryinfo (revision_id(0),entry_id(1),entry_name(2),data_id(3))
00346         branchinfo (branch_id,branch_name)
00347     1.allocate and insert a new revision into the revisions table
00348     2.allocate and insert a new entry into the entry table with the new revision
00349     3.inset into data_rev table with new data_id ,revision)id mapping
00350     
00351     insert into revisions(revision_id,branch_id,branch_name,comment,ctime) values()
00352     insert into datatablename_entries (entry_id,revision_id) values()
00353     insert into datatablename_rev(data_id,revision_id) values()
00354     '''
00355     try:
00356         revisiontableName=nameDealer.revisionTableName()
00357         entrytableName=nameDealer.entryTableName(datatableName)
00358         revtableName=nameDealer.revmapTableName(datatableName)
00359         
00360         db=dbUtil.dbUtil(schema)
00361         tabrowDefDict={}
00362         tabrowDefDict['REVISION_ID']='unsigned long long'
00363         tabrowDefDict['BRANCH_ID']='unsigned long long'
00364         tabrowDefDict['BRANCH_NAME']='string'
00365         tabrowDefDict['CTIME']='time stamp'
00366         tabrowValueDict={}
00367         tabrowValueDict['REVISION_ID']=entryinfo[0]
00368         tabrowValueDict['BRANCH_ID']=branchinfo[0]
00369         tabrowValueDict['BRANCH_NAME']=branchinfo[1]
00370         tabrowValueDict['CTIME']=coral.TimeStamp()
00371         db.insertOneRow(revisiontableName,tabrowDefDict,tabrowValueDict)
00372         
00373         tabrowDefDict={}
00374         tabrowDefDict['REVISION_ID']='unsigned long long'
00375         tabrowDefDict['ENTRY_ID']='unsigned long long'    
00376         tabrowDefDict['NAME']='string'
00377         
00378         tabrowValueDict={}
00379         tabrowValueDict['REVISION_ID']=entryinfo[0]
00380         tabrowValueDict['ENTRY_ID']=entryinfo[1]
00381         tabrowValueDict['NAME']=entryinfo[2]
00382         db.insertOneRow(entrytableName,tabrowDefDict,tabrowValueDict)
00383     
00384         tabrowDefDict={}
00385         tabrowDefDict['REVISION_ID']='unsigned long long'
00386         tabrowDefDict['DATA_ID']='unsigned long long'
00387         tabrowValueDict={}
00388         tabrowValueDict['REVISION_ID']=entryinfo[0]
00389         tabrowValueDict['DATA_ID']=entryinfo[3]
00390         db.insertOneRow(revtableName,tabrowDefDict,tabrowValueDict)
00391     except:
00392         raise
00393     
00394 def addRevision(schema,datatableName,revisioninfo,branchinfo):
00395     '''
00396     1.insert a new revision into the revisions table
00397     2.insert into data_id, revision_id pair to  datatable_revmap
00398     insert into revisions(revision_id,branch_id,branch_name,ctime) values()
00399     insert into datatable_rev(data_id,revision_id) values())
00400     input:
00401          revisioninfo (revision_id(0),data_id(1))
00402          branchinfo  (branch_id(0),branch_name(1))
00403     '''
00404     try:
00405         revisiontableName=nameDealer.revisionTableName()
00406         revtableName=nameDealer.revmapTableName(datatableName)
00407         
00408         db=dbUtil.dbUtil(schema)
00409         tabrowDefDict={}
00410         tabrowDefDict['REVISION_ID']='unsigned long long'
00411         tabrowDefDict['BRANCH_ID']='unsigned long long'
00412         tabrowDefDict['BRANCH_NAME']='string'
00413         tabrowDefDict['CTIME']='time stamp'
00414 
00415         tabrowValueDict={}
00416         tabrowValueDict['REVISION_ID']=revisioninfo[0]
00417         tabrowValueDict['BRANCH_ID']=branchinfo[0]
00418         tabrowValueDict['BRANCH_NAME']=branchinfo[1]
00419         tabrowValueDict['CTIME']=coral.TimeStamp()
00420         
00421         db.insertOneRow(revisiontableName,tabrowDefDict,tabrowValueDict)
00422         
00423         tabrowDefDict={}
00424         tabrowDefDict['REVISION_ID']='unsigned long long'
00425         tabrowDefDict['DATA_ID']='unsigned long long'
00426         tabrowValueDict={}
00427         tabrowValueDict['REVISION_ID']=revisioninfo[0]
00428         tabrowValueDict['DATA_ID']=revisioninfo[1]
00429         db.insertOneRow(revtableName,tabrowDefDict,tabrowValueDict)
00430     except:
00431         raise    
00432 def createBranch(schema,name,parentname,comment=''):
00433     '''
00434     create a new branch/tag under given parentnode
00435     insert into revisions(revision_id,branch_id,branch_name,name,comment,ctime) values()
00436     return (revisionid,parentid,parentname)
00437     '''
00438     try:
00439         parentid=None
00440         revisionid=0       
00441         if not parentname is None:
00442             qHandle=schema.newQuery()
00443             qHandle.addToTableList( nameDealer.revisionTableName() )
00444             qHandle.addToOutputList( 'REVISION_ID','revision_id' )
00445             qCondition=coral.AttributeList()
00446             qCondition.extend('parentname','string')
00447             qCondition['parentname'].setData(parentname)
00448             qResult=coral.AttributeList()
00449             qResult.extend('revision_id','unsigned long long')
00450             qHandle.defineOutput(qResult)
00451             qHandle.setCondition('NAME=:parentname',qCondition)
00452             cursor=qHandle.execute()
00453             while cursor.next():
00454                 parentid=cursor.currentRow()['revision_id'].data()
00455             del qHandle
00456         else:
00457             parentname='ROOT'
00458         iddealer=idDealer.idDealer(schema)
00459         revisionid=iddealer.generateNextIDForTable( nameDealer.revisionTableName() )
00460         db=dbUtil.dbUtil(schema)
00461         tabrowDefDict={}
00462         tabrowDefDict['REVISION_ID']='unsigned long long'
00463         tabrowDefDict['BRANCH_ID']='unsigned long long'
00464         tabrowDefDict['BRANCH_NAME']='string'
00465         tabrowDefDict['NAME']='string'
00466         tabrowDefDict['COMMENT']='string'
00467         tabrowDefDict['CTIME']='time stamp'
00468         tabrowValueDict={}
00469         tabrowValueDict['REVISION_ID']=revisionid
00470         tabrowValueDict['BRANCH_ID']=parentid
00471         tabrowValueDict['BRANCH_NAME']=parentname
00472         tabrowValueDict['NAME']=name
00473         tabrowValueDict['COMMENT']=comment
00474         tabrowValueDict['CTIME']=coral.TimeStamp()
00475         db.insertOneRow(nameDealer.revisionTableName(),tabrowDefDict, tabrowValueDict )
00476         return (revisionid,parentid,parentname)
00477     except:
00478         raise
00479     
00480 ################################################################
00481 # Data Tagging  API
00482 ################################################################
00483 def createDataTag(schema,tagname,lumitype='HF'):
00484     '''
00485     insert into tags(tagname,tagid,creationtime) values()
00486     output:
00487         tagname,tagid,creationtime
00488     '''
00489     if lumitype not in ['HF','PIXEL']:
00490         raise ValueError('unknown lumitype '+lumitype)
00491     if lumitype=='HF':
00492         tagstablename=nameDealer.tagsTableName()
00493     else:
00494         tagstablename=nameDealer.pixeltagsTableName()
00495     try:
00496         iddealer=idDealer.idDealer(schema)
00497         tagid=iddealer.generateNextIDForTable( tagstablename )
00498         db=dbUtil.dbUtil(schema)
00499         tabrowDefDict={}
00500         tabrowDefDict['TAGNAME']='string'
00501         tabrowDefDict['TAGID']='unsigned long long'
00502         tabrowDefDict['CREATIONTIME']='time stamp'
00503         tabrowValueDict={}
00504         tabrowValueDict['TAGNAME']=tagname
00505         tabrowValueDict['TAGID']=tagid
00506         creationtime=coral.TimeStamp()
00507         tabrowValueDict['CREATIONTIME']=creationtime
00508         db.insertOneRow(tagstablename,tabrowDefDict, tabrowValueDict )
00509         return (tagname,tagid,creationtime)
00510     except:
00511         raise
00512 
00513 def currentDataTag(schema,lumitype='HF'):
00514     '''
00515     select tagid,tagname from tags
00516     output:(tagid,tagname)
00517     '''
00518     if lumitype not in ['HF','PIXEL']:
00519         raise ValueError('unknown lumitype '+lumitype)
00520     if lumitype=='HF':
00521         tagstablename=nameDealer.tagsTableName()
00522     else:
00523         tagstablename=nameDealer.pixeltagsTableName()
00524     tagmap={}
00525     try:
00526         qHandle=schema.newQuery()
00527         qHandle.addToTableList( tagstablename )
00528         qHandle.addToOutputList('TAGID')
00529         qHandle.addToOutputList('TAGNAME')
00530         qResult=coral.AttributeList()
00531         qResult.extend('TAGID','unsigned long long')
00532         qResult.extend('TAGNAME','string')
00533         qHandle.defineOutput(qResult)
00534         cursor=qHandle.execute()
00535         currenttagid=0
00536         while cursor.next():
00537             tagid=cursor.currentRow()['TAGID'].data()
00538             tagname=cursor.currentRow()['TAGNAME'].data()
00539             tagmap[tagid]=tagname
00540         del qHandle
00541         if len(tagmap)!=0:
00542             currenttagid=max(tagmap.keys())
00543         if currenttagid==0:
00544             raise 'currentDataTag: no tag available'
00545         return (currenttagid,tagmap[currenttagid])
00546     except:
00547         raise
00548         
00549 def addRunToCurrentDataTag(schema,runnum,lumiid,trgid,hltid,lumitype='HF',comment=''):
00550     '''
00551     select tagid from tags
00552     insert into tagruns(tagid,runnum,lumidataid,trgdataid,hltdataid,creationtime,comment) values(tagid,runnum,lumiid,trgid,hltid,creationtime,comment)
00553     '''
00554     if lumitype not in ['HF','PIXEL']:
00555         raise ValueError('unknown lumitype '+lumitype)
00556     if lumitype=='HF':
00557         tagrunstablename=nameDealer.tagrunsTableName()
00558     else:
00559         tagrunstablename=nameDealer.pixeltagRunsTableName()
00560     currenttagid=currentDataTag(schema,lumitype=lumitype)[0]
00561     try:
00562         db=dbUtil.dbUtil(schema)
00563         tabrowDefDict={}
00564         tabrowDefDict['TAGID']='unsigned long long'
00565         tabrowDefDict['RUNNUM']='unsigned int'
00566         tabrowDefDict['LUMIDATAID']='unsigned long long'
00567         tabrowDefDict['TRGDATAID']='unsigned long long'
00568         tabrowDefDict['HLTDATAID']='unsigned long long'
00569         tabrowDefDict['CREATIONTIME']='time stamp'
00570         tabrowDefDict['COMMENT']='string'
00571         tabrowValueDict={}
00572         tabrowValueDict['TAGID']=currenttagid
00573         tabrowValueDict['RUNNUM']=runnum
00574         tabrowValueDict['LUMIDATAID']=lumiid
00575         tabrowValueDict['TRGDATAID']=trgid
00576         tabrowValueDict['HLTDATAID']=hltid
00577         tabrowValueDict['CREATIONTIME']=coral.TimeStamp()
00578         tabrowValueDict['COMMENT']=comment
00579         db.insertOneRow( tagrunstablename,tabrowDefDict, tabrowValueDict )
00580     except:
00581         raise
00582 
00583 def alldataTags(schema,lumitype='HF'):
00584     '''
00585     select tagname,tagid from tags,tagruns  
00586         if number of tags==1->open tag
00587         if tagid is max ->open tag
00588         for closed tag:
00589            max run=max(runnum) where tagid=:tagid
00590            min run
00591               select min(runnum) from tagruns where tagid<=:tagid
00592         for open tag:
00593            max run=None
00594            min run
00595               select min(runnum) from tagruns where tagid<=:tagid
00596     output:
00597        {tagid:(name,minrun,maxrun,creationtime)}
00598     '''
00599     if lumitype not in ['HF','PIXEL']:
00600         raise ValueError('unknown lumitype '+lumitype)
00601     if lumitype=='HF':
00602         tagstablename=nameDealer.tagsTableName()
00603         tagrunstablename=nameDealer.tagRunsTableName()
00604     else:
00605         tagstablename=nameDealer.pixeltagsTableName()
00606         tagrunstablename=nameDealer.pixeltagRunsTableName()
00607     tagmap={}#{tagid:[tagname,minrun,maxrun,creationtime]}
00608     try:
00609         qHandle=schema.newQuery()
00610         qHandle.addToTableList( tagstablename )
00611         qCondition=coral.AttributeList()
00612         qHandle.addToOutputList('TAGNAME')
00613         qHandle.addToOutputList('TAGID')
00614         qHandle.addToOutputList("TO_CHAR(CREATIONTIME,\'MM/DD/YY HH24:MI:SS\')",'creationtime')
00615         qResult=coral.AttributeList()        
00616         qResult.extend('TAGNAME','string')
00617         qResult.extend('TAGID','unsigned long long')
00618         qResult.extend('creationtime','string')
00619         qHandle.defineOutput(qResult)
00620         cursor=qHandle.execute()
00621         while cursor.next():
00622             tagname=cursor.currentRow()['TAGNAME'].data()
00623             tagid=cursor.currentRow()['TAGID'].data()
00624             creationtime=cursor.currentRow()['creationtime'].data()
00625             tagmap[tagid]=[tagname,0,0,creationtime]
00626         del qHandle
00627         
00628         tagids=tagmap.keys()
00629         allruns=set()
00630         for tagid in tagids:
00631             qConditionStr='TAGID<=:tagid'
00632             qCondition=coral.AttributeList()
00633             qCondition.extend('tagid','unsigned long long')
00634             qCondition['tagid'].setData(tagid)
00635             qHandle=schema.newQuery()
00636             qHandle.addToTableList( tagrunstablename )
00637             qResult=coral.AttributeList()
00638             qResult.extend('RUNNUM','unsigned int')
00639             qHandle.defineOutput(qResult)
00640             qHandle.setCondition(qConditionStr,qCondition)
00641             qHandle.addToOutputList('RUNNUM')
00642             cursor=qHandle.execute()
00643             while cursor.next():
00644                 rnum=cursor.currentRow()['RUNNUM'].data()
00645                 allruns.add(rnum)
00646             minrun=0
00647             maxrun=0
00648             if len(allruns)!=0:
00649                 minrun=min(allruns)
00650                 maxrun=max(allruns)
00651             tagmap[tagid][1]=minrun
00652             if len(tagmap)>1 and tagid!=max(tagids):
00653                 tagmap[tagid][2]=maxrun   
00654     except:
00655         raise
00656     return tagmap
00657 
00658 def dataIdsByTagName(schema,tagname,runlist=None,withcomment=False,lumitype='HF'):
00659     '''
00660     select tagid from tags where tagname=:tagname
00661     input:
00662         runlist: select run list, if None, all
00663     output:
00664         {run:(lumidataid,trgdataid,hltdataid,(creationtime,comment)}
00665     '''
00666     if lumitype not in ['HF','PIXEL']:
00667         raise ValueError('unknown lumitype '+lumitype)
00668     if lumitype=='HF':
00669         tagstablename=nameDealer.tagsTableName()
00670     else:
00671         tagstablename=nameDealer.pixeltagsTableName()        
00672     tagid=None
00673     try:
00674         qHandle=schema.newQuery()
00675         qHandle.addToTableList( tagstablename )
00676         qConditionStr='TAGNAME=:tagname'
00677         qCondition=coral.AttributeList()
00678         qCondition.extend('tagname','string')
00679         qCondition['tagname'].setData(tagname)
00680         qHandle.addToOutputList('TAGID')
00681         qResult=coral.AttributeList()        
00682         qResult.extend('TAGID','unsigned long long')
00683         qHandle.defineOutput(qResult)
00684         qHandle.setCondition(qConditionStr,qCondition)
00685         cursor=qHandle.execute()
00686         while cursor.next():
00687             if not cursor.currentRow()['TAGID'].isNull():
00688                 tagid=cursor.currentRow()['TAGID'].data()
00689         del qHandle
00690     except:
00691         raise
00692     if tagid is None:
00693         return {}
00694     return dataIdsByTagId(schema,tagid,runlist=runlist,withcomment=withcomment,lumitype=lumitype)
00695 
00696 
00697 def dataTagInfo(schema,tagname,runlist=None,lumitype='HF'):
00698     '''
00699     select tagid from tags where tagname=:tagname
00700     select runnum,comment from tagruns where tagid<=:tagid
00701     input:
00702         runlist: select run list, if None, all
00703     output:
00704        {tagid:(name,minrun,maxrun,creationtime)}
00705     '''
00706     if lumitype not in ['HF','PIXEL']:
00707         raise ValueError('unknown lumitype '+lumitype)
00708     if lumitype=='HF':
00709         tagstablename=nameDealer.tagsTableName()
00710         tagrunstablename=nameDealer.tagRunsTableName()
00711     else:
00712         tagstablename=nameDealer.pixeltagsTableName()
00713         tagrunstablename=nameDealer.pixeltagRunsTableName()
00714     tagmap={}#{tagid:[tagname,minrun,maxrun,creationtime]}
00715     try:
00716         qHandle=schema.newQuery()
00717         qHandle.addToTableList( tagstablename )
00718         qCondition=coral.AttributeList()
00719         qHandle.addToOutputList('TAGNAME')
00720         qHandle.addToOutputList('TAGID')
00721         qHandle.addToOutputList("TO_CHAR(CREATIONTIME,\'MM/DD/YY HH24:MI:SS\')",'creationtime')
00722         qResult=coral.AttributeList()        
00723         qResult.extend('TAGNAME','string')
00724         qResult.extend('TAGID','unsigned long long')
00725         qResult.extend('creationtime','string')
00726         qHandle.defineOutput(qResult)
00727         cursor=qHandle.execute()
00728         while cursor.next():
00729             tagname=cursor.currentRow()['TAGNAME'].data()
00730             tagid=cursor.currentRow()['TAGID'].data()
00731             creationtime=cursor.currentRow()['creationtime'].data()
00732             tagmap[tagid]=[tagname,0,0,creationtime]
00733         del qHandle
00734         
00735         tagids=tagmap.keys()
00736         allruns=set()
00737         for tagid in tagids:
00738             qConditionStr='TAGID<=:tagid'
00739             qCondition=coral.AttributeList()
00740             qCondition.extend('tagid','unsigned long long')
00741             qCondition['tagid'].setData(tagid)
00742             qHandle=schema.newQuery()
00743             qHandle.addToTableList(tagrunstablename)
00744             qResult=coral.AttributeList()
00745             qResult.extend('RUNNUM','unsigned int')
00746             qHandle.defineOutput(qResult)
00747             qHandle.setCondition(qConditionStr,qCondition)
00748             qHandle.addToOutputList('RUNNUM')
00749             cursor=qHandle.execute()
00750             while cursor.next():
00751                 rnum=cursor.currentRow()['RUNNUM'].data()
00752                 if runlist is not None and rnum not in runlist:
00753                     continue
00754                 allruns.add(rnum)
00755             minrun=0
00756             maxrun=0
00757             if len(allruns)!=0:
00758                 minrun=min(allruns)
00759                 maxrun=max(allruns)
00760             tagmap[tagid][1]=minrun
00761             if len(tagmap)>1 and tagid!=max(tagids):
00762                 tagmap[tagid][2]=maxrun   
00763     except:
00764         raise
00765     return tagmap
00766 
00767 def dataIdsByTagId(schema,tagid,runlist=None,withcomment=False,lumitype='HF'):
00768     '''
00769     select runnum,lumidataid,trgdataid,hltdataid,comment from tagruns where TAGID<=:tagid;
00770     input:
00771         runlist: select run list, if None, all
00772     output:
00773         {run:(lumidataid,trgdataid,hltdataid,(creationtime,comment))}
00774     '''
00775     if lumitype not in ['HF','PIXEL']:
00776         raise ValueError('unknown lumitype '+lumitype)
00777     if lumitype=='HF':
00778         tagrunstablename=nameDealer.tagRunsTableName()
00779     else:
00780         tagrunstablename=nameDealer.pixeltagRunsTableName()
00781     result={}#{run:[lumiid,trgid,hltid,comment(optional)]} 
00782     commentdict={}#{(lumiid,trgid,hltid):[ctimestr,comment]}
00783     try:
00784         qHandle=schema.newQuery()
00785         qHandle.addToTableList(tagrunstablename)
00786         qConditionStr='TAGID<=:tagid'
00787         qCondition=coral.AttributeList()
00788         qCondition.extend('tagid','unsigned long long')
00789         qCondition['tagid'].setData(tagid)
00790         qResult=coral.AttributeList()        
00791         qResult.extend('RUNNUM','unsigned int')
00792         qResult.extend('LUMIDATAID','unsigned long long')
00793         qResult.extend('TRGDATAID','unsigned long long')
00794         qResult.extend('HLTDATAID','unsigned long long')
00795         if withcomment:
00796             qResult.extend('COMMENT','string')
00797             qResult.extend('creationtime','string')
00798         qHandle.defineOutput(qResult)
00799         qHandle.setCondition(qConditionStr,qCondition)
00800         qHandle.addToOutputList('RUNNUM')
00801         qHandle.addToOutputList('LUMIDATAID')
00802         qHandle.addToOutputList('TRGDATAID')
00803         qHandle.addToOutputList('HLTDATAID')
00804         if withcomment:
00805             qHandle.addToOutputList('COMMENT')
00806             qHandle.addToOutputList("TO_CHAR(CREATIONTIME,\'MM/DD/YY HH24:MI:SS\')",'creationtime')
00807         cursor=qHandle.execute()
00808         while cursor.next():
00809             runnum=cursor.currentRow()['RUNNUM'].data()
00810             if runlist is not None and runnum not in runlist:
00811                 continue
00812             lumidataid=0
00813             if not cursor.currentRow()['LUMIDATAID'].isNull():
00814                 lumidataid=cursor.currentRow()['LUMIDATAID'].data()
00815             trgdataid=0
00816             if not cursor.currentRow()['TRGDATAID'].isNull():
00817                 trgdataid=cursor.currentRow()['TRGDATAID'].data()
00818             hltdataid=0
00819             if not cursor.currentRow()['HLTDATAID'].isNull():
00820                 hltdataid=cursor.currentRow()['HLTDATAID'].data()
00821             if not result.has_key(runnum):
00822                 result[runnum]=[0,0,0]
00823             if lumidataid>result[runnum][0]:
00824                 result[runnum][0]=lumidataid
00825             if trgdataid>result[runnum][1]:
00826                 result[runnum][1]=trgdataid
00827             if hltdataid>result[runnum][2]:
00828                 result[runnum][2]=hltdataid    
00829             if withcomment:
00830                 comment=''
00831                 creationtime=''
00832                 if not cursor.currentRow()['creationtime'].isNull():
00833                     creationtime=cursor.currentRow()['creationtime'].data()
00834                 if not cursor.currentRow()['COMMENT'].isNull():
00835                     comment=cursor.currentRow()['COMMENT'].data()
00836                 commentdict[(lumidataid,trgdataid,hltdataid)]=(creationtime,comment)
00837         del qHandle
00838         if withcomment:
00839             for run,resultentry in result.items():
00840                 lumiid=resultentry[0]
00841                 trgid=resultentry[1]
00842                 hltid=resultentry[2]
00843                 if commentdict.has_key((lumiid,trgid,hltid)):
00844                     resultentry.append(commentdict[(lumiid,trgid,hltid)])
00845                 elif commentdict.has_key((lumiid,0,0)):
00846                     resultentry.append(commentdict[(lumiid,0,0)])
00847                 elif commentdict.has_ley((0,trgid,0)):
00848                     resultentry.append(commentdict[(0,trgid,0)])
00849                 elif commentdict.has_ley((0,0,hltid)):
00850                     resultentry.append(commentdict[(0,0,hltid)])
00851                 else:
00852                     resultentry.append(())
00853                     
00854     except:
00855         raise
00856     return result
00857     
00858 def dataIdsByCurrentTag(schema,runlist=None,lumitype='HF'):
00859     '''
00860     dataIdsByTagId(schema,currenttagid,runlist)
00861     output:
00862        (currenttagname,{run:(lumidataid,trgdataid,hltdataid)})
00863     '''
00864     (currenttagid,currenttagname)=currentDataTag(schema)
00865     result=dataIdsByTagId(schema,currenttagid,runlist=runlist,withcomment=False,lumitype=lumitype)
00866     return (currenttagname,result)
00867 
00868 if __name__ == "__main__":
00869     import sessionManager
00870     import lumidbDDL
00871     #myconstr='oracle://cms_orcoff_prep/cms_lumi_dev_offline'
00872     #authpath='/afs/cern.ch/user/x/xiezhen'
00873     myconstr='sqlite_file:test.db'
00874     svc=sessionManager.sessionManager(myconstr,debugON=False)
00875     session=svc.openSession(isReadOnly=False,cpp2sqltype=[('unsigned int','NUMBER(10)'),('unsigned long long','NUMBER(20)')])
00876     schema=session.nominalSchema()
00877     session.transaction().start(False)
00878     tables=lumidbDDL.createTables(schema)
00879     trunkinfo=createBranch(schema,'TRUNK',None,comment='main')
00880     #print trunkinfo
00881     datainfo=createBranch(schema,'DATA','TRUNK',comment='hold data')
00882     #print datainfo
00883     norminfo=createBranch(schema,'NORM','TRUNK',comment='hold normalization factor')
00884     #print norminfo
00885     (branchid,branchparent)=branchInfoByName(schema,'DATA')
00886     databranchinfo=(branchid,'DATA')
00887     print databranchinfo
00888     for runnum in [1200,1211,1222,1233,1345,1222,1200]:
00889         lumientryid=entryInBranch(schema,nameDealer.lumidataTableName(),str(runnum),'DATA')
00890         trgentryid=entryInBranch(schema,nameDealer.trgdataTableName(),str(runnum),'DATA')
00891         hltentryid=entryInBranch(schema,nameDealer.hltdataTableName(),str(runnum),'DATA')
00892         if lumientryid is None:
00893             (revision_id,entry_id,data_id)=bookNewEntry( schema,nameDealer.lumidataTableName() )
00894             entryinfo=(revision_id,entry_id,str(runnum),data_id)
00895             addEntry(schema,nameDealer.lumidataTableName(),entryinfo,databranchinfo)
00896             #add data here
00897         else:
00898             revisioninfo=bookNewRevision( schema,nameDealer.lumidataTableName() )
00899             addRevision(schema,nameDealer.lumidataTableName(),revisioninfo,databranchinfo)
00900             #add data here
00901         if trgentryid is None:
00902             (revision_id,entry_id,data_id)=bookNewEntry( schema,nameDealer.trgdataTableName() )
00903             entryinfo=(revision_id,entry_id,str(runnum),data_id)
00904             addEntry(schema,nameDealer.trgdataTableName(),entryinfo,databranchinfo)
00905             #add data here
00906         else:
00907             revisioninfo=bookNewRevision( schema,nameDealer.trgdataTableName() )
00908             addRevision(schema,nameDealer.trgdataTableName(),revisioninfo,databranchinfo)      
00909              #add data here
00910         if hltentryid is None:
00911             (revision_id,entry_id,data_id)=bookNewEntry( schema,nameDealer.hltdataTableName() )
00912             entryinfo=(revision_id,entry_id,str(runnum),data_id)
00913             addEntry(schema,nameDealer.hltdataTableName(),entryinfo,databranchinfo)
00914             #add data here
00915         else:
00916             revisioninfo=bookNewRevision( schema,nameDealer.hltdataTableName() )
00917             addRevision(schema,nameDealer.hltdataTableName(),revisioninfo,databranchinfo)
00918             #add data here
00919         
00920     session.transaction().commit()
00921     print 'test reading'
00922     session.transaction().start(True)
00923     print branchType(schema,'DATA')
00924     revlist=revisionsInBranchName(schema,'DATA')
00925     print 'DATA revlist ',revlist
00926     lumientry_id=entryInBranch(schema,nameDealer.lumidataTableName(),'1211','DATA')
00927     print lumientry_id
00928     latestrevision=latestDataRevisionOfEntry(schema,nameDealer.lumidataTableName(),lumientry_id,revlist)
00929     print 'latest data_id for run 1211 ',latestrevision
00930     session.transaction().commit()
00931     del session
/data/refman/pasoursint/CMSSW_5_3_8_patch3/src/RecoLuminosity/LumiDB/python/revisionDML.py
