def normIdByName(schema,normname):
'''
select max(DATA_ID) FROM LUMINORMSV2 WHERE ENTRY_NAME=:normname
'''
luminormids=[]
result=None
qHandle=schema.newQuery()
try:
qHandle.addToTableList( nameDealer.luminormv2TableName() )
qHandle.addToOutputList('DATA_ID')
if normname:
qConditionStr='ENTRY_NAME=:normname '
qCondition=coral.AttributeList()
qCondition.extend('normname','string')
qCondition['normname'].setData(normname)
qResult=coral.AttributeList()
qResult.extend('DATA_ID','unsigned long long')
qHandle.defineOutput(qResult)
if normname:
qHandle.setCondition(qConditionStr,qCondition)
cursor=qHandle.execute()
while cursor.next():
dataid=cursor.currentRow()['DATA_ID'].data()
luminormids.append(dataid)
except :
del qHandle
raise
del qHandle
if len(luminormids) !=0:
return max(luminormids)
return result
def promoteNormToTypeDefault(schema,normname,lumitype):
'''
set the named norm as default for a given type,reset the old default if any
thisnormid=normIdByName(schema,normname)
olddefaultid=normIdByType(schema,lumitype=lumitype,defaultonly=True)
if thisnormid:
update LUMINORMSV2 set ISTYPEDEFAULT=1 where DATA_ID=:thisnormid
else:
raise ValueError('normname does not exist, nothing to update')
if olddefaultid and olddefaultid!=thisnormid:
update LUMINORMSV2 set ISTYPEDEFAULT=0 where DATA_ID=:olddefaultid
'''
try:
thisnormid=normIdByName(schema,normname)
olddefaultid=normIdByType(schema,lumitype=lumitype,defaultonly=True)
if not thisnormid:
raise ValueError(normname+' does not exist, nothing to update')
setClause='ISTYPEDEFAULT=1'
updateCondition='DATA_ID=:thisnormid'
inputData=coral.AttributeList()
inputData.extend('thisnormid','unsigned long long')
inputData['thisnormid'].setData(thisnormid)
db=dbUtil.dbUtil(schema)
db.singleUpdate(nameDealer.luminormTable(),setClause,updateCondition,inputData)
if olddefaultid:
setClause='ISTYPEDEFAULT=0'
updateCondition='DATA_ID=:olddefaultid'
inputData=coral.AttributeList()
inputData.extend('olddefaultid','unsigned long long')
inputData['olddefaultid'].setData(olddefaultid)
db=dbUtil.dbUtil(schema)
db.singleUpdate(nameDealer.luminormTable(),setClause,updateCondition,inputData)
except :
raise
def branchType(schema,name):
'''
output: tag,branch
the difference between tag and branch: tag is an empty branch
select count(revision_id) from revisions where branch_name=:name
if >0: is real branch
else: is tag
'''
result='tag'
try:
qHandle=schema.newQuery()
qHandle.addToTableList( nameDealer.revisionTableName() )
qHandle.addToOutputList('count(REVISION_ID)','nchildren')
qCondition=coral.AttributeList()
qCondition.extend('branch_name','string')
qCondition['branch_name'].setData(name)
qResult=coral.AttributeList()
qResult.extend('nchildren','unsigned int')
qHandle.defineOutput(qResult)
conditionStr='BRANCH_NAME=:branch_name'
qHandle.setCondition(conditionStr,qCondition)
cursor=qHandle.execute()
while next(cursor):
if cursor.currentRow()['nchildren'].data()>0:
result='branch'
del qHandle
return result
except :
raise
def branchInfoByName(schema,branchName):
'''
select (revision_id,branch_id) from revisions where name=:branchName
'''
try:
qHandle=schema.newQuery()
qHandle.addToTableList( nameDealer.revisionTableName() )
qHandle.addToOutputList('REVISION_ID','revision_id')
qHandle.addToOutputList('BRANCH_ID','branch_id')
qCondition=coral.AttributeList()
qCondition.extend('name','string')
qCondition['name'].setData(branchName)
qResult=coral.AttributeList()
qResult.extend('revision_id','unsigned long long')
qResult.extend('branch_id','unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition('NAME=:name',qCondition)
cursor=qHandle.execute()
revision_id=None
branch_id=None
while next(cursor):
revision_id=cursor.currentRow()['revision_id'].data()
branch_id=cursor.currentRow()['branch_id'].data()
del qHandle
return (revision_id,branch_id)
except Exception as e :
raise RuntimeError(' revisionDML.branchInfoByName: '+str(e))
def getDataTagId(schema,tagname,lumitype='HF'):
'''
select tagid from tags where tagname=:tagname
'''
if lumitype not in ['HF','PIXEL']:
raise ValueError('unknown lumitype '+lumitype)
if lumitype=='HF':
tagstablename=nameDealer.tagsTableName()
else:
tagstablename=nameDealer.pixeltagsTableName()
tagid=None
try:
qHandle=schema.newQuery()
qHandle.addToTableList( tagstablename )
qConditionStr='TAGNAME=:tagname'
qCondition=coral.AttributeList()
qCondition.extend('tagname','string')
qCondition['tagname'].setData(tagname)
qHandle.addToOutputList('TAGID')
qResult=coral.AttributeList()
qResult.extend('TAGID','unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition(qConditionStr,qCondition)
cursor=qHandle.execute()
while next(cursor):
if not cursor.currentRow()['TAGID'].isNull():
tagid=cursor.currentRow()['TAGID'].data()
del qHandle
except:
raise
return tagid
def missingTimeRuns(dbsession,c):
'''return all the runs with starttime or stoptime column NULL in lumi db
select runnum from CMSRUNSUMMARY where starttime is NULL or stoptime is NULL
'''
result=[]
try:
emptyBindVarList=coral.AttributeList()
dbsession.transaction().start(True)
schema=dbsession.nominalSchema()
if not schema:
raise 'cannot connect to schema '
if not schema.existsTable(c.runsummarytable):
raise 'non-existing table '+c.runsummarytable
query=schema.newQuery()
query.addToTableList(c.runsummarytable)
query.addToOutputList('RUNNUM','runnum')
query.setCondition('STARTTIME IS NULL AND STOPTIME IS NULL',emptyBindVarList)
query.addToOrderList('runnum')
queryOutput=coral.AttributeList()
queryOutput.extend('runnum','unsigned int')
query.defineOutput(queryOutput)
cursor=query.execute()
while next(cursor):
result.append(cursor.currentRow()['runnum'].data())
del query
dbsession.transaction().commit()
except Exception as e:
print str(e)
dbsession.transaction().rollback()
del dbsession
return result
def getrunsInResult(schema, minrun=132440, maxrun=500000):
'''
get runs in result tables in specified range
output:
[runnum]
select distinct runnum from hflumiresult where runnum>=:minrun and runnum<=:maxrun;
'''
result = []
qHandle = schema.newQuery()
try:
qHandle.addToTableList('HFLUMIRESULT')
qHandle.addToOutputList('distinct RUNNUM')
qCondition = coral.AttributeList()
qCondition.extend('minrun', 'unsigned int')
qCondition.extend('maxrun', 'unsigned int')
qCondition['minrun'].setData(minrun)
qCondition['maxrun'].setData(maxrun)
qResult = coral.AttributeList()
qResult.extend('RUNNUM', 'unsigned int')
qHandle.defineOutput(qResult)
qHandle.setCondition('RUNNUM>=:minrun AND RUNNUM<=:maxrun', qCondition)
cursor = qHandle.execute()
while cursor.next():
runnum = cursor.currentRow()['RUNNUM'].data()
result.append(runnum)
del qHandle
except:
if qHandle: del qHandle
raise
return result
def deliveredLumiForRun(self,runnum):
#
#select sum(INSTLUMI),count(INSTLUMI) from lumisummary where runnum=124025 and lumiversion='0001';
#apply norm factor and ls length in sec on the query result
#unit E27cm^-2
#
#if c.VERBOSE:
# print 'deliveredLumiForRun : norm : ',c.NORM,' : run : ',runnum
#output ['run','totalls','delivered','beammode']
c = self._c
dbsession = self._session
delivered=0.0
totalls=0
try:
dbsession.transaction().start(True)
schema=dbsession.nominalSchema()
query=schema.tableHandle(nameDealer.lumisummaryTableName()).newQuery()
query.addToOutputList("sum(INSTLUMI)","totallumi")
query.addToOutputList("count(INSTLUMI)","totalls")
query.addToOutputList("NUMORBIT","norbits")
queryBind=coral.AttributeList()
queryBind.extend("runnum","unsigned int")
queryBind.extend("lumiversion","string")
queryBind["runnum"].setData(int(runnum))
queryBind["lumiversion"].setData(c.LUMIVERSION)
result=coral.AttributeList()
result.extend("totallumi","float")
result.extend("totalls","unsigned int")
result.extend("norbits","unsigned int")
query.defineOutput(result)
query.setCondition("RUNNUM =:runnum AND LUMIVERSION =:lumiversion",queryBind)
query.limitReturnedRows(1)
query.groupBy('NUMORBIT')
cursor=query.execute()
while cursor.next():
delivereddata=cursor.currentRow()['totallumi'].data()
totallsdata=cursor.currentRow()['totalls'].data()
norbitsdata=cursor.currentRow()['norbits'].data()
if delivereddata:
totalls=totallsdata
norbits=norbitsdata
lstime=self.lslengthsec(norbits,c.NBX)
delivered=delivereddata*c.NORM*lstime
del query
dbsession.transaction().commit()
lumidata=[]
if delivered==0.0:
lumidata=[str(runnum),'N/A','N/A','N/A']
else:
lumidata=[str(runnum),str(totalls),'%.3f'%delivered,c.BEAMMODE]
return lumidata
except Exception,e:
print str(e)
dbsession.transaction().rollback()
del dbsession
def driftcorrectionsForRange(schema,
inputRange,
correctionTerm,
startrun=160403):
'''
select intglumi from intglumi where runnum=:runnum and startrun=:startrun
input : inputRange. str if a single run, [runs] if a list of runs
output: {run:driftcorrection}
'''
result = {}
runs = []
if isinstance(inputRange, str):
runs.append(int(inputRange))
else:
runs = inputRange
for r in runs:
defaultresult = 1.0
intglumi = 0.0
lint = 0.0
if r < 150008: # no drift corrections for 2010 data
result[r] = defaultresult
continue
if r > 189738: # no drift correction for 2012 data
result[r] = defaultresult
continue
qHandle = schema.newQuery()
try:
qHandle.addToTableList(nameDealer.intglumiTableName())
qResult = coral.AttributeList()
qResult.extend('INTGLUMI', 'float')
qHandle.addToOutputList('INTGLUMI')
qConditionStr = 'RUNNUM=:runnum AND STARTRUN=:startrun'
qCondition = coral.AttributeList()
qCondition.extend('runnum', 'unsigned int')
qCondition.extend('startrun', 'unsigned int')
qCondition['runnum'].setData(int(r))
qCondition['startrun'].setData(int(startrun))
qHandle.setCondition(qConditionStr, qCondition)
qHandle.defineOutput(qResult)
cursor = qHandle.execute()
while next(cursor):
intglumi = cursor.currentRow()['INTGLUMI'].data()
lint = intglumi * 6.37 * 1.0e-9 #(convert to /fb)
#print lint
except:
del qHandle
raise
del qHandle
if not lint:
print('[WARNING] null intglumi for run ', r, ' ')
result[r] = defaultresult + correctionTerm.drift * lint
#print 'lint ',lint,' result ',result
return result
def test01AttributeListModule(self): # Ported to unittest CORALCOOL-2940
print coral.AttributeList.__doc__
list1 = coral.AttributeList()
date1 = coral.Date()
x = 'a'
xType1 = 'in'
xType2 = 't'
list1.extend(x, xType1 + xType2)
list1.extend("bx", "date")
list1.extend("c", "int") # AL1 has size 3
list2 = coral.AttributeList()
list2.extend("c", "int")
list2.extend("d", "long")
list2.extend("e", "long double") # AL2 has size 3
list1.merge(list2) # AL1 has now size 5 (c,d,e shared with AL2)
list3 = coral.AttributeList()
list3.extend("a", "int")
list3.extend("b", "date") # NB: AL1 has bx, AL3 has b
list3.extend("c", "int")
list3.extend("d", "long")
list3.extend("e", "long double") # AL3 has size 5
list4 = coral.AttributeList()
list4.extend("c", "int")
list4.extend("d", "long")
list4.extend("e", "long double") # AL4 has size 3
list1[0].setData(100)
list1['bx'].setData(date1)
list1[2].setData(1000) # also sets it for AL2
list1[3].setData(10000) # also sets it for AL2
list1[4].setData(100000L) # also sets it for AL2
list3.copyData(list1) # NB: weird, copies AL1[bx] into AL3[b]
list4.fastCopyData(
list2) # NB: no type checking, but c,d,e match types
print "---LIST1----"
for attr in iter(list1):
print attr.data()
print "---LIST1----\n", list1
print "---LIST2----\n", list2
print "---LIST3----\n", list3
print "---LIST4----\n", list4
self.assertEqual(list1.size(), len(list1))
self.assertEqual(list1.size(), 5)
self.assertEqual(list2.size(), 3)
self.assertEqual(list3.size(), 5)
self.assertEqual(list4.size(), 3)
self.assertTrue(list1 != list2)
self.assertTrue(
list1 == list3) # WEIRD! Element names [2] differ, bx!=b
self.assertEqual(list1, list3) # WEIRD as above...
self.assertTrue(list3 != list4)
self.assertTrue(list2 == list4)
def normInfoByName(schema, normname):
'''
select DATA_ID,LUMITYPE,ISTYPEDEFAULT,COMMENT,TO_CHAR(CTIME,\'MM/DD/YY HH24:MI\') FROM LUMINORMS WHERE ENTRY_NAME=:normname
output:
[data_id[0],lumitype[1],istypedefault[2],comment[3],creationtime[4]]
'''
result = {}
qHandle = schema.newQuery()
try:
qHandle.addToTableList(nameDealer.luminormv2TableName())
qHandle.addToOutputList('DATA_ID')
qHandle.addToOutputList('LUMITYPE')
qHandle.addToOutputList('ISTYPEDEFAULT')
qHandle.addToOutputList('COMMENT')
qHandle.addToOutputList('TO_CHAR(CTIME,\'MM/DD/YY HH24:MI\')', 'ctime')
qConditionStr = 'ENTRY_NAME=:normname'
qCondition = coral.AttributeList()
qCondition.extend('normname', 'string')
qCondition['normname'].setData(normname)
qResult = coral.AttributeList()
qResult.extend('DATA_ID', 'unsigned long long')
qResult.extend('LUMITYPE', 'string')
qResult.extend('ISTYPEDEFAULT', 'unsigned int')
qResult.extend('COMMENT', 'string')
qResult.extend('ctime', 'string')
qHandle.defineOutput(qResult)
qHandle.setCondition(qConditionStr, qCondition)
cursor = qHandle.execute()
while cursor.next():
if not cursor.currentRow()['DATA_ID'].isNull():
dataid = cursor.currentRow()['DATA_ID'].data()
else:
continue
lumitype = cursor.currentRow()['LUMITYPE'].data()
istypedefault = cursor.currentRow()['ISTYPEDEFAULT'].data()
comment = ''
if not cursor.currentRow()['COMMENT'].isNull():
comment = cursor.currentRow()['COMMENT'].data()
creationtime = cursor.currentRow()['ctime'].data()
if not result.has_key(dataid):
result[dataid] = [
dataid, lumitype, istypedefault, comment, creationtime
]
except:
del qHandle
raise
if len(result) > 0:
maxdataid = max(result.keys())
return result[maxdataid]
return result
def revisionsInBranch(schema,branchid):
'''
returns all revision values in a branch
result=[revision_id]
select distinct branch_id from revisions where branch_id>:branchid;
select revision_id from revisions where branch_id=:branchid ;
if the branchid matches and the revisionid is not in the branchid collection,not 0, then this revision is in the branch
require also revisionid>branchid
'''
result=[]
qHandle=schema.newQuery()
try:
nextbranches=[]
qHandle.addToTableList( nameDealer.revisionTableName() )
qHandle.addToOutputList('distinct BRANCH_ID','branch_id')
qCondition=coral.AttributeList()
qCondition.extend('branchid','unsigned long long')
qCondition['branchid'].setData(branchid)
qResult=coral.AttributeList()
qResult.extend('branch_id','unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition('BRANCH_ID>:branchid',qCondition)
cursor=qHandle.execute()
while next(cursor):
nextbranches.append(cursor.currentRow()['branch_id'].data())
del qHandle
candidates=[]
conditionStr='BRANCH_ID=:branchid and REVISION_ID!=0'
qHandle=schema.newQuery()
qHandle.addToTableList( nameDealer.revisionTableName() )
qHandle.addToOutputList('REVISION_ID','revision_id')
qCondition=coral.AttributeList()
qCondition.extend('branchid','unsigned long long')
qCondition['branchid'].setData(branchid)
qResult=coral.AttributeList()
qResult.extend('revision_id','unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition(conditionStr,qCondition)
cursor=qHandle.execute()
while next(cursor):
candidates.append(cursor.currentRow()['revision_id'].data())
del qHandle
for c in candidates:
if c in nextbranches:
continue
result.append(c)
return result
except:
if qHandle: del qHandle
raise
def revisionsInTag(schema,tagrevisionid,branchid):
'''
returns all revisions before tag in selected branch
select revision_id from revisions where revision_id!=0 and revision_id<tagrevisionid and branch_id=:branchid
result=[revision_id]
'''
result=[]
qHandle=schema.newQuery()
try:
nextbranches=[]
qHandle.addToTableList( nameDealer.revisionTableName() )
qHandle.addToOutputList('distinct BRANCH_ID','branch_id')
qCondition=coral.AttributeList()
qCondition.extend('branchid','unsigned long long')
qCondition['branchid'].setData(branchid)
qResult=coral.AttributeList()
qResult.extend('branch_id','unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition('BRANCH_ID>:branchid',qCondition)
cursor=qHandle.execute()
while next(cursor):
nextbranches.append(cursor.currentRow()['branch_id'].data())
del qHandle
candidates=[]
conditionStr='REVISION_ID!=0 and BRANCH_ID=:branchid and REVISION_ID<:tagrevisionid'
qHandle=schema.newQuery()
qHandle.addToTableList( nameDealer.revisionTableName() )
qHandle.addToOutputList('REVISION_ID','revision_id')
qCondition=coral.AttributeList()
qCondition.extend('branchid','unsigned long long')
qCondition.extend('tagrevisionid','unsigned long long')
qCondition['branchid'].setData(branchid)
qCondition['tagrevisionid'].setData(tagrevisionid)
qResult=coral.AttributeList()
qResult.extend('revision_id','unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition(conditionStr,qCondition)
cursor=qHandle.execute()
while next(cursor):
candidates.append(cursor.currentRow()['revision_id'].data())
del qHandle
for c in candidates:
if c in nextbranches:
continue
result.append(c)
return result
except:
if qHandle:del qHandle
raise
def createBranch(schema, name, parentname, comment=''):
'''
create a new branch/tag under given parentnode
insert into revisions(revision_id,branch_id,branch_name,name,comment,ctime) values()
return (revisionid,parentid,parentname)
'''
try:
parentid = None
revisionid = 0
if not parentname is None:
qHandle = schema.newQuery()
qHandle.addToTableList(nameDealer.revisionTableName())
qHandle.addToOutputList('REVISION_ID', 'revision_id')
qCondition = coral.AttributeList()
qCondition.extend('parentname', 'string')
qCondition['parentname'].setData(parentname)
qResult = coral.AttributeList()
qResult.extend('revision_id', 'unsigned long long')
qHandle.defineOutput(qResult)
qHandle.setCondition('NAME=:parentname', qCondition)
cursor = qHandle.execute()
while cursor.next():
parentid = cursor.currentRow()['revision_id'].data()
del qHandle
else:
parentname = 'ROOT'
iddealer = idDealer.idDealer(schema)
revisionid = iddealer.generateNextIDForTable(
nameDealer.revisionTableName())
db = dbUtil.dbUtil(schema)
tabrowDefDict = {}
tabrowDefDict['REVISION_ID'] = 'unsigned long long'
tabrowDefDict['BRANCH_ID'] = 'unsigned long long'
tabrowDefDict['BRANCH_NAME'] = 'string'
tabrowDefDict['NAME'] = 'string'
tabrowDefDict['COMMENT'] = 'string'
tabrowDefDict['CTIME'] = 'time stamp'
tabrowValueDict = {}
tabrowValueDict['REVISION_ID'] = revisionid
tabrowValueDict['BRANCH_ID'] = parentid
tabrowValueDict['BRANCH_NAME'] = parentname
tabrowValueDict['NAME'] = name
tabrowValueDict['COMMENT'] = comment
tabrowValueDict['CTIME'] = coral.TimeStamp()
db.insertOneRow(nameDealer.revisionTableName(), tabrowDefDict,
tabrowValueDict)
return (revisionid, parentid, parentname)
except:
raise
def recalibrateLumiForRun(dbsession,c,delta,runnums):
'''
update LUMISUMMARY set INSTLUMI=:delta*INSTLUMI where RUNNUM in (1,3,57,90)
'''
updaterows=0
try:
dbsession.transaction().start(False)
schema=dbsession.nominalSchema()
if not schema:
raise 'cannot connect to schema'
if not schema.existsTable(c.lumisummarytable):
raise 'non-existing table '+c.lumisummarytable
runliststring=','.join([str(x) for x in runnums])
print 'applying delta '+delta+' on run list '+runliststring
nchanged=0
inputData=coral.AttributeList()
inputData.extend('delta','float')
inputData['delta'].setData(float(delta))
nchanged=schema.tableHandle(c.lumisummarytable).dataEditor().updateRows('INSTLUMI=INSTLUMI*:delta','RUNNUM in ('+runliststring+')',inputData)
print 'total number of row changed ',nchanged
if c.isdryrun:
dbsession.transaction().rollback()
else:
dbsession.transaction().commit()
return nchanged
except Exception as e:
print str(e)
dbsession.transaction().rollback()
del dbsession
def createIDTable(self, idtableName, deleteOld=True):
"""Create ID table 'tableName_ID' for the given table.\n
Input: name of the table which needs new associated id table
Output: name of the id table created
"""
dbop = DBImpl.DBImpl(self.__schema)
try:
if dbop.tableExists(idtableName) is True:
if deleteOld is True:
dbop.dropTable(idtableName)
else:
return
description = coral.TableDescription()
description.setName(idtableName)
description.insertColumn(self.__idTableColumnName,
self.__idTableColumnType)
idtableHandle = self.__schema.createTable(description)
idtableHandle.privilegeManager().grantToPublic(
coral.privilege_Select)
inputData = coral.AttributeList()
editor = idtableHandle.dataEditor()
editor.rowBuffer(inputData)
inputData[self.__idTableColumnName].setData(1)
editor.insertRow(inputData)
except Exception, e:
raise Exception, str(e)
def getNode( self, label='ROOT' ):
"""return result of query "select * from treetable where nodelabel=label" in Node structure \n
Input: name of the node to get. Default to 'ROOT' \n
Output: selected node
"""
result=Node.Node()
if label=='ROOT':
return result
transaction=self.__session.transaction()
try:
transaction.start(True)
query=self.__session.nominalSchema().tableHandle(self.__tagTreeTableName).newQuery()
condition = 'nodelabel =:nodelabel'
conditionData = coral.AttributeList()
conditionData.extend( 'nodelabel','string' )
query.setCondition( condition, conditionData)
conditionData['nodelabel'].setData(label)
cursor = query.execute()
while ( cursor.next() ):
result.tagid=cursor.currentRow()['tagid'].data()
result.nodeid=cursor.currentRow()['nodeid'].data()
result.nodelabel=cursor.currentRow()['nodelabel'].data()
result.lft=cursor.currentRow()['lft'].data()
result.rgt=cursor.currentRow()['rgt'].data()
result.parentid=cursor.currentRow()['parentid'].data()
result.globalsince=cursor.currentRow()['globalsince'].data()
result.globaltill=cursor.currentRow()['globaltill'].data()
transaction.commit()
del query
return result
except coral.Exception, er:
transaction.rollback()
raise Exception, str(er)
def currentDataTag(schema,lumitype='HF'):
'''
select tagid,tagname from tags
output:(tagid,tagname)
'''
if lumitype not in ['HF','PIXEL']:
raise ValueError('unknown lumitype '+lumitype)
if lumitype=='HF':
tagstablename=nameDealer.tagsTableName()
else:
tagstablename=nameDealer.pixeltagsTableName()
tagmap={}
try:
qHandle=schema.newQuery()
qHandle.addToTableList( tagstablename )
qHandle.addToOutputList('TAGID')
qHandle.addToOutputList('TAGNAME')
qResult=coral.AttributeList()
qResult.extend('TAGID','unsigned long long')
qResult.extend('TAGNAME','string')
qHandle.defineOutput(qResult)
cursor=qHandle.execute()
currenttagid=0
while next(cursor):
tagid=cursor.currentRow()['TAGID'].data()
tagname=cursor.currentRow()['TAGNAME'].data()
tagmap[tagid]=tagname
del qHandle
if len(tagmap)!=0:
currenttagid=max(tagmap.keys())
if currenttagid==0:
raise 'currentDataTag: no tag available'
return (currenttagid,tagmap[currenttagid])
except:
raise
def calibrateRange(dbsession, normfactor, startrun, endrun):
'''
update lumisummary set instlumi=instlumi*:norm where runnum>=:startrun and runnum<=:endrun
'''
try:
dbsession.transaction().start(False)
schema = dbsession.nominalSchema()
if not schema:
raise 'cannot connect to schema'
if not schema.existsTable('LUMISUMMARY'):
raise 'non-existing table LUMISUMMARY'
inputData = coral.AttributeList()
inputData.extend('normfactor', 'float')
inputData['normfactor'].setData(float(normfactor))
inputData.extend('startrun', 'unsigned int')
inputData['startrun'].setData(int(startrun))
inputData.extend('endrun', 'unsigned int')
inputData['endrun'].setData(int(endrun))
nchanged = schema.tableHandle('LUMISUMMARY').dataEditor().updateRows(
'INSTLUMI=INSTLUMI*:normfactor',
'RUNNUM>=:startrun AND RUNNUM<=:endrun', inputData)
dbsession.transaction().commit()
return nchanged
except Exception as e:
print str(e)
dbsession.transaction().rollback()
del dbsession
def createIDTable(self, tableName, deleteOld=True):
"""
Create ID table for the given table.\n
Input: name of the table which needs new associated id table
Output: name of the id table created
"""
try:
idtableName = nameDealer.idTableName(tableName)
if deleteOld is True:
self.__schema.dropIfExistsTable(idtableName)
else:
if self.__schema.existsTable(idtableName):
print('table ' + idtableName + ' exists, do nothing')
return
description = coral.TableDescription()
description.setName(idtableName)
description.setPrimaryKey(nameDealer.idTableColumnDefinition()[0])
description.insertColumn(nameDealer.idTableColumnDefinition()[0],
nameDealer.idTableColumnDefinition()[1])
idtableHandle = self.__schema.createTable(description)
idtableHandle.privilegeManager().grantToPublic(
coral.privilege_Select)
inputData = coral.AttributeList()
editor = idtableHandle.dataEditor()
editor.rowBuffer(inputData)
inputData[nameDealer.idTableColumnDefinition()[0]].setData(0)
editor.insertRow(inputData)
except Exception as e:
raise RuntimeError('dbUtil.createIDTable' + str(e))
def getNodeById( self, nodeid ):
"""return result of query "select * from treetable where nodeid=:nodeid" in Node structure \n
Input: id of the node to get.\n
Output: selected node
"""
result=Node.Node()
transaction=self.__session.transaction()
try:
transaction.start(True)
schema = self.__session.nominalSchema()
query = schema.tableHandle(self.__tagTreeTableName).newQuery()
condition = 'nodeid =:nodeid'
conditionData = coral.AttributeList()
conditionData.extend( 'nodeid','unsigned int' )
conditionData['nodeid'].setData(nodeid)
query.setCondition( condition, conditionData)
cursor = query.execute()
while ( next(cursor) ):
result.tagid=cursor.currentRow()['tagid'].data()
result.nodeid=cursor.currentRow()['nodeid'].data()
result.nodelabel=cursor.currentRow()['nodelabel'].data()
result.lft=cursor.currentRow()['lft'].data()
result.rgt=cursor.currentRow()['rgt'].data()
result.parentid=cursor.currentRow()['parentid'].data()
result.globalsince=cursor.currentRow()['globalsince'].data()
result.globaltill=cursor.currentRow()['globaltill'].data()
transaction.commit()
del query
return result
except coral.Exception as er:
transaction.rollback()
raise Exception(str(er))
except Exception as er:
transaction.rollback()
raise Exception(str(er))
def updatedb(schema, runkeymap, keymaskmap):
'''
update trgdata set algomask_h=:algomask_h,algomask_l=:algomask_l,techmask=:techmask where runnum=:runnum
input:
runkeymap
keymaskmap
'''
setClause = 'ALGOMASK_H=:algomask_h,ALGOMASK_L=:algomask_l,TECHMASK=:techmask'
updateCondition = 'RUNNUM=:runnum'
inputData = coral.AttributeList()
inputData.extend('algomask_h', 'unsigned long long')
inputData.extend('algomask_l', 'unsigned long long')
inputData.extend('techmask', 'unsigned long long')
inputData.extend('runnum', 'unsigned int')
db = dbUtil.dbUtil(schema)
for runnum in runkeymap.keys():
gt_rs_key = runkeymap[runnum]
print runnum, gt_rs_key
[algo_h, algo_l, tech] = keymaskmap[gt_rs_key]
inputData['algomask_h'].setData(algo_h)
inputData['algomask_l'].setData(algo_l)
inputData['techmask'].setData(tech)
inputData['runnum'].setData(runnum)
r = db.singleUpdate(nameDealer.trgdataTableName(), setClause,
updateCondition, inputData)
if r > 0:
print 'updated'
def nChildren( self, label='ROOT' ):
"""Number of children nodes of the given node
Input: label of the parent node
Output: number of children
"""
if label is 'ROOT' :
#"select count(*) from tagTreeTable"
try:
transaction=self.__session.transaction()
transaction.start(True)
schema = self.__session.nominalSchema()
query = schema.tableHandle(self.__tagTreeTableName).newQuery()
query.addToOutputList('count(*)', 'ct')
mycounts=coral.AttributeList()
mycounts.extend('ct', 'unsigned long');
query.defineOutput( mycounts );
cursor = query.execute();
while ( next(cursor) ):
n= cursor.currentRow()['ct'].data()
transaction.commit()
del query
return n
except coral.Exception as er:
transaction.rollback()
raise Exception(str(er))
except Exception as er:
transaction.rollback()
raise Exception(str(er))
else:
me=self.getNode(label)
return int((me.rgt-me.lft)/2)
def copydata(self, rowCount=-1):
try:
self.m_sourceSession.transaction().start()
self.m_destSession.transaction().start()
listsourceTable = listschema(self.m_sourceSession.nominalSchema())
listdestTable = listobjects(self.m_destSession.nominalSchema())
self._checktable(listsourceTable, listdestTable)
selectionclause = ""
selectionparameters = coral.AttributeList()
for key, value in listsourceTable.items():
iTable = key
print iTable
currentCount = 0
self._copytablelayout(iTable)
self._copydatalayout(iTable, selectionclause,
selectionparameters, currentCount,
rowCount)
self.m_destSession.transaction().commit()
self.m_sourceSession.transaction().commit()
print "copydata SUCCESS"
return True
except Exception as e:
self.m_destSession.transaction().rollback()
self.m_sourceSession.transaction().commit()
raise Exception("Error in copydata method: " + str(e))
return False
def ReadGeometryTagId(self):
""" Read the geometry Id corresponding to the geometry tag name in order to collect the child node Id's """
query = self.dbSchema.tableHandle("HVS_TAG2NODE").newQuery()
query.addToOutputList('TAG_NAME')
query.addToOutputList('TAG_ID')
bindstag2node = coral.AttributeList()
bindstag2node.extend('tagN', 'string')
bindstag2node[0].setData(self.dbGeoTag)
condString = 'TAG_NAME=:tagN'
query.setCondition(condString, bindstag2node)
for currentRow in iter(query.execute()):
for i in range(0, currentRow.size()):
if currentRow[i].specification().name() == "TAG_ID":
Logging.log.verbose("*** GeoTagId ******** " +
str(currentRow))
self.dbGeoTagId = currentRow[i].data()
continue
del query
return
def replaceLeafLinks(self, leafnodelinks ):
"""modify the tagid link in leafnodes
Input: {oldtagid:newtagid , oldtagid:newtagid}
This function does not check if the nodes are all really leafs. User has to check before passing the input argument
"""
if len(leafnodelinks.keys())==0:
raise 'TagTree::replaceLeafLinks: empty input '
transaction=self.__session.transaction()
transaction.start(False)
schema = self.__session.nominalSchema()
try:
updateAction="tagid = :newtagid"
updateCondition="tagid = :oldtagid"
updateData=coral.AttributeList()
updateData.extend('newtagid','unsigned long')
updateData.extend('oldtagid','unsigned long')
mybulkOperation=schema.tableHandle(self.__tagTreeTableName).dataEditor().bulkUpdateRows("tagid = :newtagid","tagid = :oldtagid",updateData,1000)
for oldtagid in leafnodelinks.keys():
updateData['newtagid'].setData(leafnodelinks[oldtagid])
updateData['oldtagid'].setData(oldtagid)
mybulkOperation.processNextIteration()
mybulkOperation.flush()
transaction.commit()
del mybulkOperation
except Exception as er:
transaction.rollback()
raise Exception(str(er))
def patchDeadtimeForRun(dbsession,c,runnum,deadtimeDict):
'''
input: deadtimeDict{ls:deadtimebeamactive}
loop over input
update TRG set DEADTIME=:deadtimebeamactive where RUNNUM=:runnum and CMSLSNUM=:lsnum
output: number of rows changed
'''
totalchanged=0
try:
dbsession.transaction().start(False)
schema=dbsession.nominalSchema()
if not schema:
raise Exception('cannot connect to schema ')
if not schema.existsTable(c.lumitrgtable):
raise Exception('non-existing table '+c.lumitrgtable)
for lsnum,deadtimebeamactive in deadtimeDict.items():
nchanged=0
inputData=coral.AttributeList()
inputData.extend('deadtimebeamactive','unsigned int')
inputData.extend('runnum','unsigned int')
inputData.extend('lsnum','unsigned int')
inputData['deadtimebeamactive'].setData(deadtimebeamactive)
inputData['runnum'].setData(runnum)
inputData['lsnum'].setData(lsnum)
nchanged=schema.tableHandle(c.lumitrgtable).dataEditor().updateRows('DEADTIME=:deadtimebeamactive','RUNNUM=:runnum AND CMSLSNUM=:lsnum',inputData)
print 'rows changed for ls ',str(lsnum),str(nchanged)
totalchanged+=nchanged
dbsession.transaction().commit()
return totalchanged
except Exception as e:
print str(e)
dbsession.transaction().rollback()
del dbsession
def getAllLeaves( self ):
"""Get all leaf nodes.\n
Output: list of leaf nodes
Query "SELECT * FROM treetable WHERE lft=rgt-1"
"""
result=[]
try:
transaction=self.__session.transaction()
transaction.start(True)
schema = self.__session.nominalSchema()
query = schema.tableHandle(self.__tagTreeTableName).newQuery()
condition = 'lft=rgt-1'
conditionData = coral.AttributeList()
query.setCondition( condition, conditionData)
cursor = query.execute()
while ( cursor.next() ):
resultNode=Node.Node()
resultNode.tagid=cursor.currentRow()['tagid'].data()
resultNode.nodeid=cursor.currentRow()['nodeid'].data()
resultNode.nodelabel=cursor.currentRow()['nodelabel'].data()
resultNode.lft=cursor.currentRow()['lft'].data()
resultNode.rgt=cursor.currentRow()['rgt'].data()
resultNode.parentid=cursor.currentRow()['parentid'].data()
resultNode.globalsince=cursor.currentRow()['globalsince'].data()
resultNode.globaltill=cursor.currentRow()['globaltill'].data()
result.append( resultNode )
transaction.commit()
del query
return result
except coral.Exception, er:
transaction.rollback()
del query
raise Exception, str(er)
def updateRows(self, tableName, updateAction, updateCondition, bindvarDef,
bulkinput):
'''
update rows, note update must be ordered
input :
tableName, string
updateAction,string e.g. flag=:newflag
conditionstring, string ,e.g. runnum=:runnum and cmslsnum=:cmslsnum
bindvarDef,[('newflag','string'),('runnum','unsigned int'),('cmslsnum','unsigned int')]
bulkinput,[[('newflag','GOOD'),('runnum',1234),('cmslsnum',1)],[]]
'''
try:
dataEditor = self.__schema.tableHandle(tableName).dataEditor()
updateData = coral.AttributeList()
for (columnname, columntype) in bindvarDef:
updateData.extend(columnname, columntype)
bulkOperation = dataEditor.bulkUpdateRows(updateAction,
updateCondition,
updateData,
len(bulkinput))
for valuelist in bulkinput:
for (columnname, columnvalue) in valuelist:
updateData[columnname].setData(columnvalue)
bulkOperation.processNextIteration()
bulkOperation.flush()
del bulkOperation
except Exception as e:
raise Exception('dbUtil.updateRows:' + str(e))
def getEntryById(self, tagId):
"""Get basic tag from inventory by id.\n
Input: tagid
Output: leafNode
"""
leafnode = Node.LeafNode()
transaction = self.__session.transaction()
try:
transaction.start(True)
query = self.__session.nominalSchema().tableHandle(
self.__tagInventoryTableName).newQuery()
for columnName in self.__tagInventoryTableColumns:
query.addToOutputList(columnName)
condition = "tagid=:tagid"
conditionData = coral.AttributeList()
conditionData.extend('tagid', 'unsigned long')
conditionData['tagid'].setData(tagId)
query.setCondition(condition, conditionData)
cursor = query.execute()
while (next(cursor)):
#print 'got it'
leafnode.tagid = cursor.currentRow()['tagid'].data()
leafnode.tagname = cursor.currentRow()['tagname'].data()
leafnode.objectname = cursor.currentRow()['objectname'].data()
leafnode.pfn = cursor.currentRow()['pfn'].data()
leafnode.labelname = cursor.currentRow()['labelname'].data()
leafnode.recordname = cursor.currentRow()['recordname'].data()
transaction.commit()
del query
return leafnode
except Exception, e:
transaction.rollback()
raise Exception, str(e)
