def listtables( schema,tablename ):
try:
listOfTableNames = schema.listTables()
#Dictionaries are created for resolving table dependencies
dTable=mseqdict( [], {})
dCopyTable=mseqdict( [], {})
for tableName in listOfTableNames:
if tablename==tableName:
#Add tablename to dictionary
dTable.append(tableName,'')
description = coral.TableDescription()
description.setName( tableName )
table = schema.tableHandle(tableName )
numberOfForeignKeys = table.description().numberOfForeignKeys()
for i in range(0, numberOfForeignKeys):
foreignKey = table.description().foreignKey( i )
columnNames = foreignKey.columnNames()
columnNamesR = foreignKey.referencedColumnNames()
for key,value in dTable.items():
dCopyTable.append(key,'')
return dCopyTable
except Exception, e:
raise Exception (" " + str(e))
return False
def createInventoryTable(self):
"""Create tag inventory table. Existing table will be deleted.
"""
try:
transaction = self.__session.transaction()
transaction.start()
schema = self.__session.nominalSchema()
schema.dropIfExistsTable(self.__tagInventoryTableName)
description = coral.TableDescription()
description.setName(self.__tagInventoryTableName)
for columnName, columnType in self.__tagInventoryTableColumns.items(
):
description.insertColumn(columnName, columnType)
for columnName in self.__tagInventoryTableNotNullColumns:
description.setNotNullConstraint(columnName, True)
#for columnName in self.__tagInventoryTableUniqueColumns :
#description.setUniqueConstraint(columnName)
#combinedunique1=('pfn','recordname','objectname','labelname')
#description.setUniqueConstraint(combinedunique1)
#combinedunique2=('tagname','pfn')
#description.setUniqueConstraint(combinedunique2)
description.setPrimaryKey(self.__tagInventoryTablePK)
self.__tagInventoryTableHandle = schema.createTable(description)
self.__tagInventoryTableHandle.privilegeManager().grantToPublic(
coral.privilege_Select)
#create also the associated id table
generator = IdGenerator.IdGenerator(schema)
generator.createIDTable(self.__tagInventoryIDName, True)
transaction.commit()
except Exception, er:
transaction.rollback()
raise Exception, str(er)
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 createTagTreeTable( self ):
"""Create tag tree table. Existing table will be deleted.
"""
transaction=self.__session.transaction()
try:
transaction.start(False)
schema = self.__session.nominalSchema()
schema.dropIfExistsTable( self.__tagTreeTableName )
description = coral.TableDescription();
description.setName( self.__tagTreeTableName )
for columnName, columnType in self.__tagTreeTableColumns.items():
description.insertColumn(columnName, columnType)
for columnName in self.__tagTreeTableNotNullColumns :
description.setNotNullConstraint(columnName,True)
for columnName in self.__tagTreeTableUniqueColumns :
description.setUniqueConstraint(columnName)
description.setPrimaryKey( self.__tagTreeTablePK )
#description.createForeignKey('tagid_FK','tagid',self.__tagInventoryTableName,'tagid')
self.__tagTreeTableHandle = schema.createTable( description )
self.__tagTreeTableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
self.__tagTreeTableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
self.__tagTreeTableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
self.__tagTreeTableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
#create also the associated id table
generator=IdGenerator.IdGenerator(self.__session.nominalSchema())
generator.createIDTable(self.__tagTreeIDs,True)
transaction.commit()
except Exception as er:
transaction.rollback()
raise Exception(str(er))
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 createValidation(dbsession):
'''
lumivalidation table
'''
dbsession.transaction().start(False)
schema = dbsession.nominalSchema()
db = dbUtil.dbUtil(schema)
lumivalidation = coral.TableDescription()
lumivalidation.setName(nameDealer.lumivalidationTableName())
lumivalidation.insertColumn('RUNNUM', 'unsigned int')
lumivalidation.insertColumn('CMSLSNUM', 'unsigned int')
lumivalidation.insertColumn('FLAG', 'string')
lumivalidation.insertColumn('COMMENT', 'string')
lumivalidation.setPrimaryKey(('RUNNUM', 'CMSLSNUM'))
lumivalidation.setNotNullConstraint('FLAG', True)
db.createTable(lumivalidation, False)
dbsession.transaction().commit()
def fillData(self):
"fillData method of GroupApp"
try:
print "In fillData"
#m_proxy = self.m_svc.connect(self.m_connectionString, self.m_userName, self.m_password)
m_proxy = self.m_svc.connect(self.m_connectionString,
coral.access_Update)
m_proxy.transaction().start()
schema = m_proxy.nominalSchema()
#Get rid of the previous tables and views
schema.dropIfExistsTable("ORA_UT_GB_T")
print "Describing new table"
description = coral.TableDescription()
description.setName("ORA_UT_GB_T")
description.insertColumn("ID", "int")
description.setPrimaryKey("ID")
description.insertColumn("x", "float")
description.setNotNullConstraint("x")
description.insertColumn("GrB", "int")
print "About to create the table"
table = schema.createTable(description)
rowBuffer = coral.AttributeList()
rowBuffer.extend("ID", "int")
rowBuffer.extend("x", "float")
rowBuffer.extend("GrB", "int")
for i in range(0, 100):
rowBuffer["ID"].setData(i)
rowBuffer["GrB"].setData(i % 10)
rowBuffer["x"].setData((i % 14) + 0.1 * (i % 17))
table.dataEditor().insertRow(rowBuffer)
m_proxy.transaction().commit()
del m_proxy
print "fillData SUCCESS"
return True
except Exception, e:
raise Exception("Error in fillData method: " + str(e))
return False
def createEntryCommentTable(self):
"""Create entry comment able.Existing table will be deleted.
"""
try:
transaction=self.__session.transaction()
transaction.start()
schema = self.__session.nominalSchema()
schema.dropIfExistsTable(CommonUtils.commentTableName())
description = coral.TableDescription()
description.setName(CommonUtils.commentTableName())
for columnName, columnType in self.__entryCommentTableColumns.items():
description.insertColumn(columnName,columnType)
for columnName in self.__entryCommentTableNotNullColumns:
description.setNotNullConstraint(columnName,True)
description.setPrimaryKey(self.__entryCommentTablePK)
tablehandle=schema.createTable(description)
tablehandle.privilegeManager().grantToPublic(coral.privilege_Select)
transaction.commit()
except Exception, e:
transaction.rollback()
raise Exception, str(e)
def createRevMapTable( self, tableName, deleteOld=True ):
"""
Create Rev table for the given table.\n
Input: name of the table
Output: name of the id table
"""
try:
revmaptableName=nameDealer.revmapTableName(tableName)
if deleteOld is True:
self.__schema.dropIfExistsTable(revmaptableName)
else:
if self.__schema.existsTable(revmaptableName):
print 'table '+revmaptableName+' exists, do nothing'
return
description = coral.TableDescription()
description.setName( revmaptableName )
description.insertColumn( 'DATA_ID','unsigned long long')
description.insertColumn( 'REVISION_ID' ,'unsigned long long')
tableHandle=self.__schema.createTable( description )
tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
except Exception, e:
raise RuntimeError(' dbUtil.createRevMapTable '+str(e))
def createEntryTable( self, tableName, deleteOld=True ):
"""
Create Entry 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:
entrytableName=nameDealer.entryTableName(tableName)
if deleteOld is True:
self.__schema.dropIfExistsTable(entrytableName)
else:
if self.__schema.existsTable(entrytableName):
print 'table '+entrytableName+' exists, do nothing'
return
description = coral.TableDescription()
description.setName( entrytableName )
description.insertColumn( 'ENTRY_ID' ,'unsigned long long')
description.insertColumn( 'REVISION_ID' ,'unsigned long long')
description.insertColumn( 'NAME' ,'string',56,False)
tableHandle=self.__schema.createTable( description )
tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
except Exception, e:
raise RuntimeError(' dbUtil.createEntryTable '+str(e))
def createTable(self, tableName):
print self.dbg("Create Table")
self.connect()
try:
self.schema.dropIfExistsTable(tableName)
description = coral.TableDescription()
description.setName(tableName)
description.insertColumn('ID', 'int')
description.insertColumn('Data', 'float')
description.setPrimaryKey('ID')
tableHandle = self.schema.createTable(description)
tableHandle.dataEditor().rowBuffer(self.rowBuffer)
for i in range(20):
self.rowBuffer["ID"].setData(i)
self.rowBuffer["Data"].setData(i)
tableHandle.dataEditor().insertRow(self.rowBuffer)
except Exception, e:
print str(e)
def listtableset( schema,tableset ):
try:
listOfTableNames = schema.listTables()
#Dictionaries are created for resolving table dependencies
dTable=mseqdict( [], {})
dCircTable=mseqdict( [], {})
dCopyTable=mseqdict( [], {})
dTempTable=mseqdict( [], {})
for table in listOfTableNames:
for tableName in tableset:
if tableName==table:
#Add tablename to dictionary
dTable.append(tableName,'')
description = coral.TableDescription()
description.setName( tableName )
table = schema.tableHandle(tableName )
numberOfForeignKeys = table.description().numberOfForeignKeys()
for i in range(0, numberOfForeignKeys):
foreignKey = table.description().foreignKey( i )
columnNames = foreignKey.columnNames()
#Add referenced tablename to dictionary
dTable.append (tableName, foreignKey.referencedTableName())
dCircTable.append (tableName, foreignKey.referencedTableName())
columnNamesR = foreignKey.referencedColumnNames()
#For checking circular dependency between the tables
d1=mseqdict( [], {})
d2=mseqdict( [], {})
for rTable, refTable in dCircTable.items():
for table in refTable:
d1.append(rTable,table)
dCircTable.swap()
for rTable, refTable in dCircTable.items():
for table in refTable:
d2.append(rTable,table)
for key,value in d1.items():
firsttable=key
secondtable=value
for key,value in d2.items():
if key==firsttable and value==secondtable:
raise Exception ("Circular Dependency exists between tables : "+firsttable,secondtable)
#For retrieving the tables in order of dependency
r1=mseqdict( [], {})
r2=mseqdict( [], {})
for rTable, refTable in dTable.items():
for table in refTable:
r1.append(table,'')
r1.append(rTable,'')
for rTable, refTable in r1.items():
test=rTable
for rTable1, refTable1 in dTable.items():
if rTable1==test:
for table in refTable1:
if rTable1!=table:
r2.append(table,'')
for key,value in r2.items():
r1.remove(key,'')
for key,value in r2.items():
dTempTable.append(key,'')
for key,value in r1.items():
dTempTable.append(key,'')
for key,value in dTempTable.items():
iTable= key
for table in tableset:
if table==iTable:
dCopyTable.append(key,'')
return dCopyTable
except Exception, e:
raise Exception (" " + str(e))
return False
return result + 1
except Exception, e:
raise Exception, str(e)
if __name__ == "__main__":
fakeIDtableName = 'Fake_ID'
svc = coral.ConnectionService()
session = svc.connect('sqlite_file:fake.db')
transaction = session.transaction()
try:
transaction.start(False)
schema = session.nominalSchema()
idor = idDealer(schema)
if schema.existsTable(fakeIDtableName) is False:
description = coral.TableDescription()
description.setName(fakeIDtableName)
description.setPrimaryKey(idor.getIDColumnDefinition()[0])
description.insertColumn(idor.getIDColumnDefinition()[0],
idor.getIDColumnDefinition()[1])
idtableHandle = schema.createTable(description)
idtableHandle.privilegeManager().grantToPublic(
coral.privilege_Select)
inputData = coral.AttributeList()
editor = idtableHandle.dataEditor()
editor.rowBuffer(inputData)
inputData[idor.getIDColumnDefinition()[0]].setData(0)
editor.insertRow(inputData)
idor.generateNextIDForTable('Fake')
print idor.getIDforTable('Fake')
transaction.commit()
def createOldSchema(schema):
'''
create tables of lumidb1 if not exist
'''
try:
created = []
db = dbUtil.dbUtil(schema)
if not schema.existsTable(nameDealer.lumivalidationTableName()):
lumivalidation = coral.TableDescription()
lumivalidation.setName(nameDealer.lumivalidationTableName())
lumivalidation.insertColumn('RUNNUM', 'unsigned int')
lumivalidation.insertColumn('CMSLSNUM', 'unsigned int')
lumivalidation.insertColumn('FLAG', 'string', 28, False)
lumivalidation.insertColumn('COMMENT', 'string', 1024, False)
lumivalidation.setPrimaryKey(('RUNNUM', 'CMSLSNUM'))
lumivalidation.setNotNullConstraint('FLAG', True)
db.createTable(lumivalidation, withIdTable=False)
created.append(nameDealer.lumivalidationTableName())
if not schema.existsTable(nameDealer.cmsrunsummaryTableName()):
cmsrunsummary = coral.TableDescription()
cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName())
cmsrunsummary.insertColumn('RUNNUM', 'unsigned int')
cmsrunsummary.insertColumn('HLTKEY', 'string', 128, False)
cmsrunsummary.insertColumn('FILLNUM', 'unsigned int')
cmsrunsummary.insertColumn('SEQUENCE', 'string', 56, False)
cmsrunsummary.insertColumn('STARTTIME', 'time stamp', 6)
cmsrunsummary.insertColumn('STOPTIME', 'time stamp', 6)
cmsrunsummary.setPrimaryKey('RUNNUM')
cmsrunsummary.setNotNullConstraint('HLTKEY', True)
cmsrunsummary.setNotNullConstraint('FILLNUM', True)
cmsrunsummary.setNotNullConstraint('SEQUENCE', True)
cmsrunsummary.createIndex('cmsrunsummary_fillnum', ('FILLNUM'))
cmsrunsummary.createIndex('cmsrunsummary_startime', ('STARTTIME'))
db.createTable(cmsrunsummary, withIdTable=False)
created.append(nameDealer.cmsrunsummaryTableName())
if not schema.existsTable(nameDealer.lumisummaryTableName()):
summary = coral.TableDescription()
summary.setName(nameDealer.lumisummaryTableName())
summary.insertColumn('LUMISUMMARY_ID', 'unsigned long long')
summary.insertColumn('RUNNUM', 'unsigned int')
summary.insertColumn('CMSLSNUM', 'unsigned int')
summary.insertColumn('LUMILSNUM', 'unsigned int')
summary.insertColumn('LUMIVERSION', 'string', 28, False)
summary.insertColumn('DTNORM', 'float')
summary.insertColumn('LHCNORM', 'float')
summary.insertColumn('CMSALIVE', 'short')
summary.insertColumn('INSTLUMI', 'float')
summary.insertColumn('INSTLUMIERROR', 'float')
summary.insertColumn('INSTLUMIQUALITY', 'short')
summary.insertColumn('BEAMSTATUS', 'string', 28, False)
summary.insertColumn('BEAMENERGY', 'float')
summary.insertColumn('NUMORBIT', 'unsigned int')
summary.insertColumn('STARTORBIT', 'unsigned int')
summary.insertColumn('LUMISECTIONQUALITY', 'short')
summary.insertColumn('CMSBXINDEXBLOB', 'blob')
summary.insertColumn('BEAMINTENSITYBLOB_1', 'blob')
summary.insertColumn('BEAMINTENSITYBLOB_2', 'blob')
summary.setPrimaryKey('LUMISUMMARY_ID')
summary.setNotNullConstraint('RUNNUM', True)
summary.setNotNullConstraint('CMSLSNUM', True)
summary.setNotNullConstraint('LUMILSNUM', True)
summary.setNotNullConstraint('CMSALIVE', True)
summary.setNotNullConstraint('LUMIVERSION', True)
summary.setNotNullConstraint('DTNORM', True)
summary.setNotNullConstraint('LHCNORM', True)
summary.setNotNullConstraint('INSTLUMI', True)
summary.setNotNullConstraint('INSTLUMIERROR', True)
summary.setNotNullConstraint('INSTLUMIQUALITY', True)
summary.setNotNullConstraint('STARTORBIT', True)
summary.setNotNullConstraint('NUMORBIT', True)
summary.setNotNullConstraint('LUMISECTIONQUALITY', True)
summary.setNotNullConstraint('BEAMENERGY', True)
summary.setNotNullConstraint('BEAMSTATUS', True)
summary.setUniqueConstraint(('RUNNUM', 'LUMIVERSION', 'LUMILSNUM'))
summary.createIndex('lumisummary_runnum', ('RUNNUM'))
db.createTable(summary, withIdTable=True)
created.append(nameDealer.lumisummaryTableName())
if not schema.existsTable(nameDealer.lumidetailTableName()):
detail = coral.TableDescription()
detail.setName(nameDealer.lumidetailTableName())
detail.insertColumn('LUMIDETAIL_ID', 'unsigned long long')
detail.insertColumn('LUMISUMMARY_ID', 'unsigned long long')
detail.insertColumn('BXLUMIVALUE', 'blob')
detail.insertColumn('BXLUMIERROR', 'blob')
detail.insertColumn('BXLUMIQUALITY', 'blob')
detail.insertColumn('ALGONAME', 'string', 28, False)
detail.setPrimaryKey('LUMIDETAIL_ID')
detail.createForeignKey('DETAILSOURCE', 'LUMISUMMARY_ID',
nameDealer.lumisummaryTableName(),
'LUMISUMMARY_ID')
detail.setNotNullConstraint('BXLUMIVALUE', True)
detail.setNotNullConstraint('BXLUMIERROR', True)
detail.setNotNullConstraint('BXLUMIQUALITY', True)
detail.setNotNullConstraint('ALGONAME', True)
detail.setUniqueConstraint(('LUMISUMMARY_ID', 'ALGONAME'))
db.createTable(detail, withIdTable=True)
created.append(nameDealer.lumidetailTableName())
if not schema.existsTable(nameDealer.trgTableName()):
trg = coral.TableDescription()
trg.setName(nameDealer.trgTableName())
trg.insertColumn('TRG_ID', 'unsigned long long')
trg.insertColumn('RUNNUM', 'unsigned int')
trg.insertColumn('CMSLSNUM', 'unsigned int')
trg.insertColumn('BITNUM', 'unsigned int')
trg.insertColumn('BITNAME', 'string', 56, False)
trg.insertColumn('TRGCOUNT', 'unsigned int')
trg.insertColumn('DEADTIME', 'unsigned long long')
trg.insertColumn('PRESCALE', 'unsigned int')
trg.setNotNullConstraint('RUNNUM', True)
trg.setNotNullConstraint('CMSLSNUM', True)
trg.setNotNullConstraint('BITNUM', True)
trg.setNotNullConstraint('BITNAME', True)
trg.setNotNullConstraint('TRGCOUNT', True)
trg.setNotNullConstraint('DEADTIME', True)
trg.setNotNullConstraint('PRESCALE', True)
trg.setPrimaryKey('TRG_ID')
trg.createIndex('trg_runnum', ('RUNNUM'))
db.createTable(trg, withIdTable=True)
created.append(nameDealer.trgTableName())
if not schema.existsTable(nameDealer.hltTableName()):
hlt = coral.TableDescription()
hlt.setName(nameDealer.hltTableName())
hlt.insertColumn('HLT_ID', 'unsigned long long')
hlt.insertColumn('RUNNUM', 'unsigned int')
hlt.insertColumn('CMSLSNUM', 'unsigned int')
hlt.insertColumn('PATHNAME', 'string', 256, False)
hlt.insertColumn('INPUTCOUNT', 'unsigned int')
hlt.insertColumn('ACCEPTCOUNT', 'unsigned int')
hlt.insertColumn('PRESCALE', 'unsigned int')
hlt.setPrimaryKey('HLT_ID')
hlt.setNotNullConstraint('RUNNUM', True)
hlt.setNotNullConstraint('CMSLSNUM', True)
hlt.setNotNullConstraint('PATHNAME', True)
hlt.setNotNullConstraint('INPUTCOUNT', True)
hlt.setNotNullConstraint('ACCEPTCOUNT', True)
hlt.setNotNullConstraint('PRESCALE', True)
hlt.createIndex('hlt_runnum', ('RUNNUM'))
db.createTable(hlt, withIdTable=True)
created.append(nameDealer.hltTableName())
if not schema.existsTable(nameDealer.trghltMapTableName()):
trghlt = coral.TableDescription()
trghlt.setName(nameDealer.trghltMapTableName())
trghlt.insertColumn('HLTKEY', 'string', 128, False)
trghlt.insertColumn('HLTPATHNAME', 'string', 256, False)
trghlt.insertColumn('L1SEED', 'string', 1024, false)
trghlt.setNotNullConstraint('HLTKEY', True)
trghlt.setNotNullConstraint('HLTPATHNAME', True)
trghlt.setNotNullConstraint('L1SEED', True)
db.createTable(trghlt, withIdTable=False)
created.append(nameDealer.trghltMapTableName())
return created
except:
raise
def run(self):
"Run method of InsertSelect"
try:
print("InsertSelect: In run")
session = self.m_svc.connect(self.m_connectionString,
coral.access_Update)
session.transaction().start()
workingSchema = session.nominalSchema()
workingSchema.dropIfExistsTable("T4")
description = coral.TableDescription(
workingSchema.tableHandle("T2").description())
description.setName("T4")
table = workingSchema.createTable(description)
table.privilegeManager().grantToPublic(coral.privilege_Select)
operation = table.dataEditor().insertWithQuery()
query = operation.query()
query.addToTableList("T2")
query.setCondition("id<10", coral.AttributeList())
numberOfRowsInserted = operation.execute()
del operation
if (numberOfRowsInserted != 6):
raise Exception("Unexpected number of rows inserted.")
bulkOperation = table.dataEditor().bulkInsertWithQuery(3)
queryb = bulkOperation.query()
queryb.addToTableList("T2")
condition = coral.AttributeList()
condition.extend("idmin", "long")
condition.extend("idmax", "long")
queryb.setCondition("id>:idmin AND id<:idmax", condition)
idmin = 10
idmax = 13
for i in range(0, 5):
condition[0].setData(idmin)
condition[1].setData(idmax)
bulkOperation.processNextIteration()
idmin += 4
idmax += 4
bulkOperation.flush()
del bulkOperation
# add three more rows (for the next tests to make better sense)
rowBuffer = coral.AttributeList()
table.dataEditor().rowBuffer(rowBuffer)
rowBuffer[0].setData(100)
rowBuffer[1].setData(200)
rowBuffer[2].setData(300)
for i in range(0, 3):
table.dataEditor().insertRow(rowBuffer)
rowBuffer[0].setData(rowBuffer[0].data() + 1)
rowBuffer[1].setData(rowBuffer[1].data() + 2)
rowBuffer[2].setData(rowBuffer[2].data() + 3)
session.transaction().commit()
del session
time.sleep(1)
return True
except Exception as e:
raise Exception("Error in Run method: " + str(e))
return False
print MSGHDR, "Connect R/W to:", coralUrl
session = svc.connect(coralUrl, coral.access_Update)
print MSGHDR, "Got a R/W session" ###,session
schema = session.nominalSchema()
print MSGHDR, "Got a schema" ###,schema
# Start a r/w transaction
print MSGHDR, "Create table", tableName
readOnly = False
session.transaction().start(readOnly)
# Drop the table if it already exists
schema.dropIfExistsTable(tableName)
# Create the table
tableDesc = coral.TableDescription()
tableDesc.setName(tableName)
tableDesc.insertColumn("ID", "int")
tableDesc.setPrimaryKey("ID")
tableDesc.insertColumn("x", "float")
table = schema.createTable(tableDesc)
# Commit the transaction
session.transaction().commit()
# Sleep to avoid ORA-01466
time.sleep(1)
#------------------------------------------------------------------------
# *** FILL TABLE ***
#------------------------------------------------------------------------
def _printTableInfo( schema,tableName ):
try:
description = coral.TableDescription()
description.setName( tableName )
table = schema.tableHandle(tableName )
numberOfColumns = table.description().numberOfColumns()
print "Table " , tableName
print "Columns : " , numberOfColumns
for i in range(0, numberOfColumns):
column = table.description().columnDescription( i )
print "" , column.name() , " (" , column.type() , ")"
if ( column.isUnique() ):
print " UNIQUE";
if ( column.isNotNull() ):
print " NOT NULL"
if ( table.description().hasPrimaryKey() ):
columnNames = table.description().primaryKey().columnNames()
print ""
print "Primary key defined for column :"
for iColumn in columnNames:
print " ",iColumn , " "
numberOfUniqueConstraints = table.description().numberOfUniqueConstraints()
print ""
print "Unique Constraints : " , numberOfUniqueConstraints
for i in range( 0, numberOfUniqueConstraints ):
uniqueConstraint = table.description().uniqueConstraint( i )
print "" , uniqueConstraint.name() , " defined for column"
columnNames = uniqueConstraint.columnNames()
for iColumn in columnNames:
print " ",iColumn
numberOfIndices = table.description().numberOfIndices()
print ""
print "Index : " , numberOfIndices
for i in range(0, numberOfIndices ):
index = table.description().index( i )
print "" , index.name()
if ( index.isUnique() ):
print " (UNIQUE)"
print " defined for column"
columnNames = index.columnNames()
for iColumn in columnNames:
print " ",iColumn
numberOfForeignKeys = table.description().numberOfForeignKeys()
print ""
print "Foreign Keys : " , numberOfForeignKeys
for i in range(0, numberOfForeignKeys):
foreignKey = table.description().foreignKey( i )
print "" , foreignKey.name() , " defined for column"
columnNames = foreignKey.columnNames()
for iColumn in columnNames:
print " ",iColumn
print " references -> " , foreignKey.referencedTableName() , "on Column ";
columnNamesR = foreignKey.referencedColumnNames()
for iColumn in columnNamesR:
print " ",iColumn
print "--------------------------------------"
except Exception, e:
raise Exception (" " + str(e))
return False
def prepareSchema(serviceName):
try:
svc = coral.ConnectionService()
connectionName = '/dbdev/service_' + serviceName
print connectionName
session = svc.connect(connectionName=connectionName,
accessMode=coral.access_Update)
transaction = session.transaction()
transaction.start()
schema = session.nominalSchema()
# Clear the old table
schema.dropIfExistsTable(tableName)
global m_rowBuffer
print type(m_rowBuffer)
if (m_rowBuffer):
del m_rowBuffer
m_rowBuffer = coral.AttributeList()
rowBuffer = m_rowBuffer
# Create the new table
description = coral.TableDescription()
description.setName(tableName)
description.insertColumn("ID", "int")
description.setNotNullConstraint("ID")
description.setPrimaryKey("ID")
rowBuffer.extend("ID", "int")
description.insertColumn("V_B", "bool")
rowBuffer.extend("V_B", "bool")
description.insertColumn("V_UC", "unsigned char")
rowBuffer.extend("V_UC", "unsigned char")
description.insertColumn("V_SC", "char")
rowBuffer.extend("V_SC", "char")
description.insertColumn("V_US", "unsigned short")
rowBuffer.extend("V_US", "unsigned short")
description.insertColumn("V_SS", "short")
rowBuffer.extend("V_SS", "short")
description.insertColumn("V_UI", "unsigned int")
rowBuffer.extend("V_UI", "unsigned int")
description.insertColumn("V_SI", "int")
rowBuffer.extend("V_SI", "int")
description.insertColumn("V_UL", "unsigned long")
rowBuffer.extend("V_UL", "unsigned long")
description.insertColumn("V_SL", "long")
rowBuffer.extend("V_SL", "long")
description.insertColumn("V_ULL", "unsigned long long")
rowBuffer.extend("V_ULL", "unsigned long long")
description.insertColumn("V_SLL", "long long")
rowBuffer.extend("V_SLL", "long long")
description.insertColumn("V_F", "float")
rowBuffer.extend("V_F", "float")
description.insertColumn("V_D", "double")
rowBuffer.extend("V_D", "double")
description.insertColumn("V_LD", "double")
rowBuffer.extend("V_LD", "double")
description.insertColumn("V_BLOB", "blob")
rowBuffer.extend("V_BLOB", "blob")
description.insertColumn("V_DATE", "date")
rowBuffer.extend("V_DATE", "date")
description.insertColumn("V_TIMESTAMP", "time stamp")
rowBuffer.extend("V_TIMESTAMP", "time stamp")
description.insertColumn("V_SG", "string")
rowBuffer.extend("V_SG", "string")
description.insertColumn("V_SF", "string", 5)
rowBuffer.extend("V_SF", "string")
description.insertColumn("V_SV", "string", 50, False)
rowBuffer.extend("V_SV", "string")
schema.createTable(description)
transaction.commit()
del session
print "PrepareSchema SUCCESS for /dbdev/service_", serviceName
except Exception, e:
raise Exception("PrepareSchema FAILURE for /dbdev/service_" + " " +
serviceName + " " + str(e))
def listobjects( schema ):
try:
listOfTableNames = schema.listTables()
#Dictionaries are created for resolving table dependencies
dTable=mseqdict( [], {})
dRefTable=mseqdict( [], {})
dCopyTable=mseqdict( [], {})
for tableName in listOfTableNames:
#Add tablename to dictionary
dTable.append(tableName,'')
description = coral.TableDescription()
description.setName( tableName )
table = schema.tableHandle(tableName )
numberOfForeignKeys = table.description().numberOfForeignKeys()
for i in range(0, numberOfForeignKeys):
foreignKey = table.description().foreignKey( i )
columnNames = foreignKey.columnNames()
#Add referenced tablename to dictionary
dRefTable.append (tableName, foreignKey.referencedTableName())
columnNamesR = foreignKey.referencedColumnNames()
#For retrieving the tables in order of dependency
r1=mseqdict( [], {})
r2=mseqdict( [], {})
for rTable, refTable in dRefTable.items():
for table in refTable:
r1.append(table,'')
r1.append(rTable,'')
for rTable, refTable in r1.items():
test=rTable
for rTable1, refTable1 in dRefTable.items():
if rTable1==test:
for table in refTable1:
if rTable1!=table:
r2.append(table,'')
for key,value in r2.items():
r1.remove(key,'')
dTable.remove(key,'')
for key,value in r1.items():
dTable.remove(key,'')
for key,value in dTable.items():
dCopyTable.append(key,'')
for key,value in r2.items():
dCopyTable.append(key,'')
for key,value in r1.items():
dCopyTable.append(key,'')
return dCopyTable
except Exception, e:
raise Exception (" " + str(e))
return False
def createTables(schema):
'''
create new tables if not exist
revisions,revisions_id,luminorms,luminorms_entries,luminorms_entries_id,fillscheme
'''
try:
created = []
db = dbUtil.dbUtil(schema)
if not schema.existsTable(nameDealer.fillschemeTableName()):
print('creating fillscheme table')
fillschemeTab = coral.TableDescription()
fillschemeTab.setName(nameDealer.fillschemeTableName())
fillschemeTab.insertColumn('FILLSCHEME_ID', 'unsigned long long')
fillschemeTab.insertColumn('FILLSCHEMEPATTERN', 'string', 128,
False)
fillschemeTab.insertColumn('CORRECTIONFACTOR', 'float')
fillschemeTab.setPrimaryKey('FILLSCHEME_ID')
db.createTable(fillschemeTab, withIdTable=True)
created.append(nameDealer.fillschemeTableName())
if not schema.existsTable(nameDealer.revisionTableName()):
print('creating revisions table')
revisionsTab = coral.TableDescription()
revisionsTab.setName(nameDealer.revisionTableName())
revisionsTab.insertColumn('REVISION_ID', 'unsigned long long')
revisionsTab.insertColumn('BRANCH_ID', 'unsigned long long')
revisionsTab.insertColumn('NAME', 'string', 56, False)
revisionsTab.insertColumn('BRANCH_NAME', 'string', 56, False)
revisionsTab.insertColumn('COMMENT', 'string', 1024, False)
revisionsTab.insertColumn('CTIME', 'time stamp', 6)
revisionsTab.setPrimaryKey('REVISION_ID')
revisionsTab.setUniqueConstraint(('NAME'))
db.createTable(revisionsTab, withIdTable=True)
created.append(nameDealer.revisionTableName())
if not schema.existsTable(nameDealer.luminormTableName()):
print('creating luminorms table')
luminormsTab = coral.TableDescription()
luminormsTab.setName(nameDealer.luminormTableName())
luminormsTab.insertColumn('DATA_ID', 'unsigned long long')
luminormsTab.insertColumn('ENTRY_ID', 'unsigned long long')
luminormsTab.insertColumn('ENTRY_NAME', 'string', 56, False)
luminormsTab.insertColumn('AMODETAG', 'string', 28, False)
luminormsTab.insertColumn('NORM_1', 'float')
luminormsTab.insertColumn('EGEV_1', 'unsigned int')
luminormsTab.insertColumn('NORM_2', 'float')
luminormsTab.insertColumn('EGEV_2', 'unsigned int')
luminormsTab.setPrimaryKey('DATA_ID')
db.createTable(luminormsTab,
withIdTable=True,
withEntryTables=True,
withRevMapTable=True)
created.append(nameDealer.luminormTableName())
if not schema.existsTable(nameDealer.lumidataTableName()):
print('creating lumidata table')
lumidataTab = coral.TableDescription()
lumidataTab.setName(nameDealer.lumidataTableName())
lumidataTab.insertColumn('DATA_ID', 'unsigned long long')
lumidataTab.insertColumn('ENTRY_ID', 'unsigned long long')
lumidataTab.insertColumn('ENTRY_NAME', 'string', 56, False)
lumidataTab.insertColumn('SOURCE', 'string', 128, False)
lumidataTab.insertColumn('RUNNUM', 'unsigned int')
lumidataTab.insertColumn('NOMINALEGEV', 'float')
lumidataTab.setPrimaryKey('DATA_ID')
db.createTable(lumidataTab,
withIdTable=True,
withEntryTables=True,
withRevMapTable=True)
created.append(nameDealer.lumidataTableName())
if not schema.existsTable(nameDealer.lumisummaryv2TableName()):
print('creating lumisummaryv2 table')
summary = coral.TableDescription()
summary.setName(nameDealer.lumisummaryv2TableName())
summary.insertColumn('DATA_ID', 'unsigned long long')
summary.insertColumn('RUNNUM', 'unsigned int')
summary.insertColumn('LUMILSNUM', 'unsigned int')
summary.insertColumn('CMSLSNUM', 'unsigned int')
summary.insertColumn('INSTLUMI', 'float')
summary.insertColumn('INSTLUMIERROR', 'float')
summary.insertColumn('INSTLUMIQUALITY', 'short')
summary.insertColumn('BEAMSTATUS', 'string', 28, False)
summary.insertColumn('BEAMENERGY', 'float')
summary.insertColumn('NUMORBIT', 'unsigned int')
summary.insertColumn('STARTORBIT', 'unsigned int')
summary.insertColumn('CMSBXINDEXBLOB', 'blob')
summary.insertColumn('BEAMINTENSITYBLOB_1', 'blob')
summary.insertColumn('BEAMINTENSITYBLOB_2', 'blob')
summary.insertColumn('BXLUMIVALUE_OCC1', 'blob')
summary.insertColumn('BXLUMIVALUE_OCC2', 'blob')
summary.insertColumn('BXLUMIVALUE_ET', 'blob')
summary.insertColumn('BXLUMIERROR_OCC1', 'blob')
summary.insertColumn('BXLUMIERROR_OCC2', 'blob')
summary.insertColumn('BXLUMIERROR_ET', 'blob')
summary.insertColumn('BXLUMIQUALITY_OCC1', 'blob')
summary.insertColumn('BXLUMIQUALITY_OCC2', 'blob')
summary.insertColumn('BXLUMIQUALITY_ET', 'blob')
summary.setPrimaryKey(('DATA_ID', 'LUMILSNUM'))
db.createTable(summary, withIdTable=False)
created.append(nameDealer.lumisummaryv2TableName())
#
# This table exists in the old schema
#
if not schema.existsTable(nameDealer.cmsrunsummaryTableName()):
print('creating cmsrunsummary table')
cmsrunsummary = coral.TableDescription()
cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName())
cmsrunsummary.insertColumn('RUNNUM', 'unsigned int')
cmsrunsummary.insertColumn('HLTKEY', 'string', 128, False)
cmsrunsummary.insertColumn('L1KEY', 'string', 128, False)
cmsrunsummary.insertColumn('FILLNUM', 'unsigned int')
cmsrunsummary.insertColumn('SEQUENCE', 'string', 56, False)
cmsrunsummary.insertColumn('STARTTIME', 'time stamp', 6)
cmsrunsummary.insertColumn('STOPTIME', 'time stamp', 6)
cmsrunsummary.insertColumn('EGEV', 'unsigned int')
cmsrunsummary.insertColumn('AMODETAG', 'string', 28, False)
cmsrunsummary.insertColumn('FILLSCHEME', 'string', 128, False)
cmsrunsummary.insertColumn('NCOLLIDINGBUNCHES', 'usigned int')
cmsrunsummary.setPrimaryKey('RUNNUM')
db.createTable(cmsrunsummary, withIdTable=False)
created.append(nameDealer.cmsrunsummaryTableName())
#
# This table exists in the old schema
#
if not schema.existsTable(nameDealer.trghltMapTableName()):
print('creating trghltmap table')
trghlt = coral.TableDescription()
trghlt.setName(nameDealer.trghltMapTableName())
trghlt.insertColumn('HLTKEY', 'string', 128, False)
trghlt.insertColumn('HLTPATHNAME', 'string', 256, False)
trghlt.insertColumn('L1SEED', 'string', 1024, False)
trghlt.setNotNullConstraint('HLTKEY', True)
trghlt.setNotNullConstraint('HLTPATHNAME', True)
trghlt.setNotNullConstraint('L1SEED', True)
db.createTable(trghlt, withIdTable=False)
created.append(nameDealer.trghltMapTableName())
if not schema.existsTable(nameDealer.trgdataTableName()):
print('creating trgdata table')
trgdataTab = coral.TableDescription()
trgdataTab.setName(nameDealer.trgdataTableName())
trgdataTab.insertColumn('DATA_ID', 'unsigned long long')
trgdataTab.insertColumn('ENTRY_ID', 'unsigned long long')
trgdataTab.insertColumn('ENTRY_NAME', 'string', 56, False)
trgdataTab.insertColumn('SOURCE', 'string', 128, False)
trgdataTab.insertColumn('RUNNUM', 'unsigned int')
trgdataTab.insertColumn('BITZERONAME', 'string', 56, False)
trgdataTab.insertColumn('BITNAMECLOB', 'string', 6000)
trgdataTab.setPrimaryKey('DATA_ID')
db.createTable(trgdataTab,
withIdTable=True,
withEntryTables=True,
withRevMapTable=True)
created.append(nameDealer.trgdataTableName())
if not schema.existsTable(nameDealer.lstrgTableName()):
print('creating lstrg table')
lstrgTab = coral.TableDescription()
lstrgTab.setName(nameDealer.lstrgTableName())
lstrgTab.insertColumn('DATA_ID', 'unsigned long long')
lstrgTab.insertColumn('RUNNUM', 'unsigned int')
lstrgTab.insertColumn('CMSLSNUM', 'unsigned int')
lstrgTab.insertColumn('DEADTIMECOUNT', 'unsigned long long')
lstrgTab.insertColumn('BITZEROCOUNT', 'unsigned int')
lstrgTab.insertColumn('BITZEROPRESCALE', 'unsigned int')
lstrgTab.insertColumn('DEADFRAC', 'float')
lstrgTab.insertColumn('PRESCALEBLOB', 'blob')
lstrgTab.insertColumn('TRGCOUNTBLOB', 'blob')
lstrgTab.setPrimaryKey(('DATA_ID', 'CMSLSNUM'))
db.createTable(lstrgTab, withIdTable=False)
created.append(nameDealer.lstrgTableName())
if not schema.existsTable(nameDealer.hltdataTableName()):
print('creating hltdata table')
hltdataTab = coral.TableDescription()
hltdataTab.setName(nameDealer.hltdataTableName())
hltdataTab.insertColumn('DATA_ID', 'unsigned long long')
hltdataTab.insertColumn('ENTRY_ID', 'unsigned long long')
hltdataTab.insertColumn('ENTRY_NAME', 'string', 56, False)
hltdataTab.insertColumn('RUNNUM', 'unsigned int')
hltdataTab.insertColumn('SOURCE', 'string', 128, False)
hltdataTab.insertColumn('NPATH', 'unsigned int')
hltdataTab.insertColumn('PATHNAMECLOB', 'string', 6000)
hltdataTab.setPrimaryKey('DATA_ID')
db.createTable(hltdataTab,
withIdTable=True,
withEntryTables=True,
withRevMapTable=True)
created.append(nameDealer.hltTableName())
if not schema.existsTable(nameDealer.lshltTableName()):
print('creating lshlt table')
lshltTab = coral.TableDescription()
lshltTab.setName(nameDealer.lshltTableName())
lshltTab.insertColumn('DATA_ID', 'unsigned long long')
lshltTab.insertColumn('RUNNUM', 'unsigned int')
lshltTab.insertColumn('CMSLSNUM', 'unsigned int')
lshltTab.insertColumn('PRESCALEBLOB', 'blob')
lshltTab.insertColumn('HLTCOUNTBLOB', 'blob')
lshltTab.insertColumn('HLTACCEPTBLOB', 'blob')
db.createTable(lshltTab, withIdTable=False)
lshltTab.setPrimaryKey(('DATA_ID', 'CMSLSNUM'))
created.append(nameDealer.lshltTableName())
if not schema.existsTable(nameDealer.lumivalidationTableName()):
print('creating lumivalidation table')
lumivalidation = coral.TableDescription()
lumivalidation.setName(nameDealer.lumivalidationTableName())
lumivalidation.insertColumn('RUNNUM', 'unsigned int')
lumivalidation.insertColumn('CMSLSNUM', 'unsigned int')
lumivalidation.insertColumn('FLAG', 'string', 28, False)
lumivalidation.insertColumn('COMMENT', 'string', 1024, False)
lumivalidation.setPrimaryKey(('RUNNUM', 'CMSLSNUM'))
lumivalidation.setNotNullConstraint('FLAG', True)
db.createTable(lumivalidation, withIdTable=False)
created.append(nameDealer.lumivalidationTableName())
return created
except:
raise
def run(self):
"Run method of ThreadBody"
try:
print "In ThreadBody: run"
print "Thread no ", self.m_threadID, " running..."
session = self.m_connection
session.transaction().start()
schema = self.m_schemaName
print "Thread no ", self.m_threadID, " about to create and fill a table"
#The table name
tableName = "ORA_UT_MS_T_"
print tableName, self.m_threadID
schema.dropIfExistsTable(tableName)
description = coral.TableDescription()
description.setName(tableName)
description.insertColumn("I", "int")
description.insertColumn("F", "float")
description.insertColumn("D", "double")
description.setPrimaryKey("I")
description.setNotNullConstraint("F")
table = schema.createTable(description)
rowBuffer = coral.AttributeList()
rowBuffer.extend("I", "int")
rowBuffer.extend("F", "float")
rowBuffer.extend("D", "double")
for row in range(0, 100):
i = row
rowBuffer[0].setData(i)
f = row + 0.001 * self.m_threadID
rowBuffer[1].setData(f)
d = row + 0.000001 * self.m_threadID
rowBuffer[2].setData(d)
table.dataEditor().insertRow(rowBuffer)
session.transaction().commit()
time.sleep(1)
session.transaction().start(True)
print "Thread no ", self.m_threadID, " about to perform a query"
query = schema.tableHandle(tableName).newQuery()
outputBuffer = coral.AttributeList()
outputBuffer.extend("RES", "double")
query.addToOutputList("F+D", "RES")
query.addToOrderList("I")
query.defineOutput(outputBuffer)
cursor = query.execute()
row = 0
#res = outputBuffer[0].data()
while (cursor.next()):
res = outputBuffer[0].data()
if (math.fabs(res - (2 * row + 0.001001 * self.m_threadID)) >
0.00001):
raise RuntimeError("Unexpected data")
row = row + 1
if (row != 100):
raise RuntimeError("Unexpected number of rows")
session.transaction().commit()
except Exception, e:
raise Exception("Error in Run method: " + str(e))
def createSchema(self):
"createSchema method of SchemaTest"
try:
print("In createSchema")
self.m_proxy.transaction().start()
#Get rid of the previous tables and views
self.m_proxy.nominalSchema().dropIfExistsView("V0")
self.m_proxy.nominalSchema().dropIfExistsTable("T3")
self.m_proxy.nominalSchema().dropIfExistsTable("T2")
self.m_proxy.nominalSchema().dropIfExistsTable("T1")
# Create the first table
description1 = coral.TableDescription("SchemaTest_Test")
description1.setName("T1")
description1.insertColumn("id", "int")
# Define primary key
description1.setPrimaryKey("id")
description1.insertColumn("t", "short")
description1.insertColumn("Xx", "float")
# Set column not null
description1.setNotNullConstraint("Xx")
description1.insertColumn("Y", "double")
# Add a unique constraint specifying a name
description1.setUniqueConstraint("Y", "U_T1_Y")
description1.insertColumn("Z", "double")
# Add a variable size string
description1.insertColumn("Comment", "string", 100, False)
# Add a blob
description1.insertColumn("Data", "blob")
# Create the actual table
table = self.m_proxy.nominalSchema().createTable(description1)
# Change the column name
table.schemaEditor().renameColumn("Xx", "X")
# Change the column type
table.schemaEditor().changeColumnType("t", "long long")
# Add a not null constaint
table.schemaEditor().setNotNullConstraint("t")
# Add a unique constraint made out of two columns
constraintColumns = ("X", "Z")
table.schemaEditor().setUniqueConstraint(constraintColumns)
# Add a unique index
table.schemaEditor().createIndex("T1_IDX_t", "t", True)
# Give privileges
table.privilegeManager().grantToPublic(coral.privilege_Select)
# Create the second table
description2 = coral.TableDescription("SchemaDefinition_Test")
description2.setName("T2")
description2.insertColumn("id", "long")
description2.setUniqueConstraint("id")
description2.setNotNullConstraint("id")
description2.insertColumn("tx", "float")
description2.setNotNullConstraint("tx")
description2.insertColumn("ty", "double")
description2.setNotNullConstraint("ty")
self.m_proxy.nominalSchema().createTable(
description2).privilegeManager().grantToPublic(
coral.privilege_Select)
# Create the third table
description3 = coral.TableDescription("SchemaTest_Test")
description3.setName("T3")
description3.insertColumn("id", "long")
description3.setPrimaryKey("id")
description3.insertColumn("fk1", "long")
description3.createForeignKey("T3_FK1", "fk1", "T1", "id")
description3.insertColumn("fk2", "long")
description3.createForeignKey("T3_FK2", "fk2", "T2", "id")
description3.insertColumn("Qn", "float")
self.m_proxy.nominalSchema().createTable(
description3).privilegeManager().grantToPublic(
coral.privilege_Select)
# Create a view
factory = self.m_proxy.nominalSchema().viewFactory()
factory.addToOutputList("T3.id", "id")
factory.addToOutputList("T1.X", "x")
factory.addToOutputList("T1.Y", "y")
factory.addToOutputList("T1.Z", "z")
factory.addToOutputList("T2.tx", "TX")
factory.addToOutputList("T2.ty", "TY")
factory.addToTableList("T3")
factory.addToTableList("T1")
factory.addToTableList("T2")
factory.setCondition(
"T1.id = T3.fk1 AND T2.id = T3.fk2 AND T3.Qn > 0.5",
coral.AttributeList())
factory.create("V0").privilegeManager().grantToPublic(
coral.privilege_Select)
del factory
self.m_proxy.transaction().commit()
print("createSchema SUCESS")
time.sleep(1)
except Exception as e:
raise Exception("Error in createSchema method: " + str(e))
def createLumi(dbsession):
print('creating lumi db schema...')
dbsession.transaction().start(False)
schema = dbsession.nominalSchema()
db = dbUtil.dbUtil(schema)
#cms run summary table
cmsrunsummary = coral.TableDescription()
cmsrunsummary.setName(nameDealer.cmsrunsummaryTableName())
cmsrunsummary.insertColumn('RUNNUM', 'unsigned int')
cmsrunsummary.insertColumn('HLTKEY', 'string')
cmsrunsummary.insertColumn('FILLNUM', 'unsigned int')
cmsrunsummary.insertColumn('SEQUENCE', 'string')
cmsrunsummary.insertColumn('STARTTIME', 'time stamp', 6)
cmsrunsummary.insertColumn('STOPTIME', 'time stamp', 6)
cmsrunsummary.setPrimaryKey('RUNNUM')
cmsrunsummary.setNotNullConstraint('HLTKEY', True)
cmsrunsummary.setNotNullConstraint('FILLNUM', True)
cmsrunsummary.setNotNullConstraint('SEQUENCE', True)
cmsrunsummary.createIndex('cmsrunsummary_fillnum', ('FILLNUM'))
cmsrunsummary.createIndex('cmsrunsummary_startime', ('STARTTIME'))
db.createTable(cmsrunsummary, False)
#lumi summary table
summary = coral.TableDescription()
summary.setName(nameDealer.lumisummaryTableName())
summary.insertColumn('LUMISUMMARY_ID', 'unsigned long long')
summary.insertColumn('RUNNUM', 'unsigned int')
summary.insertColumn('CMSLSNUM', 'unsigned int')
summary.insertColumn('LUMILSNUM', 'unsigned int')
summary.insertColumn('LUMIVERSION', 'string')
summary.insertColumn('DTNORM', 'float')
summary.insertColumn('LHCNORM', 'float')
summary.insertColumn('INSTLUMI', 'float')
summary.insertColumn('INSTLUMIERROR', 'float')
summary.insertColumn('INSTLUMIQUALITY', 'short')
summary.insertColumn('CMSALIVE', 'short')
summary.insertColumn('STARTORBIT', 'unsigned int')
summary.insertColumn('NUMORBIT', 'unsigned int')
summary.insertColumn('LUMISECTIONQUALITY', 'short')
summary.insertColumn('BEAMENERGY', 'float')
summary.insertColumn('BEAMSTATUS', 'string')
summary.insertColumn('CMSBXINDEXBLOB', 'blob')
summary.insertColumn('BEAMINTENSITYBLOB_1', 'blob')
summary.insertColumn('BEAMINTENSITYBLOB_2', 'blob')
summary.setPrimaryKey('LUMISUMMARY_ID')
summary.setNotNullConstraint('RUNNUM', True)
summary.setNotNullConstraint('CMSLSNUM', True)
summary.setNotNullConstraint('LUMILSNUM', True)
summary.setNotNullConstraint('LUMIVERSION', True)
summary.setNotNullConstraint('DTNORM', True)
summary.setNotNullConstraint('LHCNORM', True)
summary.setNotNullConstraint('INSTLUMI', True)
summary.setNotNullConstraint('INSTLUMIERROR', True)
summary.setNotNullConstraint('INSTLUMIQUALITY', True)
summary.setNotNullConstraint('CMSALIVE', True)
summary.setNotNullConstraint('STARTORBIT', True)
summary.setNotNullConstraint('NUMORBIT', True)
summary.setNotNullConstraint('LUMISECTIONQUALITY', True)
summary.setNotNullConstraint('BEAMENERGY', True)
summary.setNotNullConstraint('BEAMSTATUS', True)
summary.setUniqueConstraint(('RUNNUM', 'LUMIVERSION', 'LUMILSNUM'))
summary.createIndex('lumisummary_runnum', ('RUNNUM'))
db.createTable(summary, True)
#lumi detail table
detail = coral.TableDescription()
detail.setName(nameDealer.lumidetailTableName())
detail.insertColumn('LUMIDETAIL_ID', 'unsigned long long')
detail.insertColumn('LUMISUMMARY_ID', 'unsigned long long')
detail.insertColumn('BXLUMIVALUE', 'blob')
detail.insertColumn('BXLUMIERROR', 'blob')
detail.insertColumn('BXLUMIQUALITY', 'blob')
detail.insertColumn('ALGONAME', 'string')
detail.setPrimaryKey('LUMIDETAIL_ID')
detail.createForeignKey('DETAILSOURCE', 'LUMISUMMARY_ID',
nameDealer.lumisummaryTableName(),
'LUMISUMMARY_ID')
detail.setNotNullConstraint('BXLUMIVALUE', True)
detail.setNotNullConstraint('BXLUMIERROR', True)
detail.setNotNullConstraint('BXLUMIQUALITY', True)
detail.setNotNullConstraint('ALGONAME', True)
detail.setUniqueConstraint(('LUMISUMMARY_ID', 'ALGONAME'))
db.createTable(detail, True)
#trg table
trg = coral.TableDescription()
trg.setName(nameDealer.trgTableName())
trg.insertColumn('TRG_ID', 'unsigned long long')
trg.insertColumn('RUNNUM', 'unsigned int')
trg.insertColumn('CMSLSNUM', 'unsigned int')
trg.insertColumn('BITNUM', 'unsigned int')
trg.insertColumn('BITNAME', 'string')
trg.insertColumn('TRGCOUNT', 'unsigned int')
trg.insertColumn('DEADTIME', 'unsigned long long')
trg.insertColumn('PRESCALE', 'unsigned int')
trg.setNotNullConstraint('RUNNUM', True)
trg.setNotNullConstraint('CMSLSNUM', True)
trg.setNotNullConstraint('BITNUM', True)
trg.setNotNullConstraint('BITNAME', True)
trg.setNotNullConstraint('TRGCOUNT', True)
trg.setNotNullConstraint('DEADTIME', True)
trg.setNotNullConstraint('PRESCALE', True)
trg.setPrimaryKey('TRG_ID')
trg.createIndex('trg_runnum', ('RUNNUM'))
db.createTable(trg, True)
#hlt table
hlt = coral.TableDescription()
hlt.setName(nameDealer.hltTableName())
hlt.insertColumn('HLT_ID', 'unsigned long long')
hlt.insertColumn('RUNNUM', 'unsigned int')
hlt.insertColumn('CMSLSNUM', 'unsigned int')
hlt.insertColumn('PATHNAME', 'string')
hlt.insertColumn('INPUTCOUNT', 'unsigned int')
hlt.insertColumn('ACCEPTCOUNT', 'unsigned int')
hlt.insertColumn('PRESCALE', 'unsigned int')
hlt.setPrimaryKey('HLT_ID')
hlt.setNotNullConstraint('RUNNUM', True)
hlt.setNotNullConstraint('CMSLSNUM', True)
hlt.setNotNullConstraint('PATHNAME', True)
hlt.setNotNullConstraint('INPUTCOUNT', True)
hlt.setNotNullConstraint('ACCEPTCOUNT', True)
hlt.setNotNullConstraint('PRESCALE', True)
hlt.createIndex('hlt_runnum', ('RUNNUM'))
db.createTable(hlt, True)
#trghlt map table
trghlt = coral.TableDescription()
trghlt.setName(nameDealer.trghltMapTableName())
#trghlt.insertColumn( 'RUNNUM','unsigned int' )
trghlt.insertColumn('HLTKEY', 'string')
trghlt.insertColumn('HLTPATHNAME', 'string')
trghlt.insertColumn('L1SEED', 'string')
trghlt.setNotNullConstraint('HLTKEY', True)
trghlt.setNotNullConstraint('HLTPATHNAME', True)
trghlt.setNotNullConstraint('L1SEED', True)
db.createTable(trghlt, False)
#lumiresult table
lumiresult = coral.TableDescription()
lumiresult.setName(nameDealer.lumiresultTableName())
lumiresult.insertColumn('RUNNUM', 'unsigned int')
lumiresult.insertColumn('LUMIVERSION', 'string')
lumiresult.insertColumn('DELIVEREDLUMI', 'float')
lumiresult.insertColumn('RECORDEDLUMI', 'float')
db.createTable(lumiresult, False)
#lumihltresult table
lumihltresult = coral.TableDescription()
lumihltresult.setName(nameDealer.lumihltresultTableName())
lumihltresult.insertColumn('RUNNUM', 'unsigned int')
lumihltresult.insertColumn('LUMIVERSION', 'string')
lumihltresult.insertColumn('HLTPATH', 'float')
lumihltresult.insertColumn('RECORDEDLUMI', 'float')
db.createTable(lumihltresult, False)
#lumivalidation table
lumivalidation = coral.TableDescription()
lumivalidation.setName(nameDealer.lumivalidationTableName())
lumivalidation.insertColumn('RUNNUM', 'unsigned int')
lumivalidation.insertColumn('CMSLSNUM', 'unsigned int')
lumivalidation.insertColumn('FLAG', 'string')
lumivalidation.insertColumn('COMMENT', 'string')
lumivalidation.setPrimaryKey(('RUNNUM', 'CMSLSNUM'))
lumivalidation.setNotNullConstraint('FLAG', True)
db.createTable(lumivalidation, False)
dbsession.transaction().commit()