dbUtil.dbUtil Class Reference

Inheritance diagram for dbUtil.dbUtil:

Public Member Functions
def	__init__ (self, schema)
def	bulkInsert (self, tableName, tabrowDef, bulkinput)
def	createEntryTable (self, tableName, deleteOld=True)
def	createIDTable (self, tableName, deleteOld=True)
def	createRevMapTable (self, tableName, deleteOld=True)
def	createTable (self, description, withIdTable=False, withEntryTables=False, withRevMapTable=False)
def	deleteRows (self, tableName, condition, conditionbindDict)
def	describeSchema (self)
def	dropAllTables (self)
def	dropTable (self, tableName)
def	existRow (self, tableName, condition, conditionDefDict, conditionDict)
def	insertOneRow (self, tableName, tabrowDefDict, tabrowValueDict)
def	listIndex (self, tablename)
def	singleUpdate (self, tableName, setClause, updateCondition, inputData)
def	tableExists (self, tableName)
def	updateRows (self, tableName, updateAction, updateCondition, bindvarDef, bulkinput)

Private Attributes
	__schema

Detailed Description

Class wrap up all the database operations.\n

Definition at line 4 of file dbUtil.py.

Constructor & Destructor Documentation

def dbUtil.dbUtil.__init__	(		self,
			schema
	)

Input: coral schema handle.

Definition at line 7 of file dbUtil.py.

    def __init__( self , schema):
        """
        Input: coral schema handle.
        """
        self.__schema = schema
        

Member Function Documentation

def dbUtil.dbUtil.bulkInsert	(		self,
			tableName,
			tabrowDef,
			bulkinput
	)

input:
   tableName, string
   tabrowDef,[('RUNNUM':'unsigned int'),('CMSLSNUM','unsigned int'),('FLAG','string'),('COMMENT','string')]
   bulkinput,[[('RUNNUM',1234),('CMSLSNUM',1234),('FLAG','GOOD'),('COMMENT','coment')],[]]

Definition at line 119 of file dbUtil.py.

    def bulkInsert( self, tableName, tabrowDef, bulkinput):
        """
        input:
           tableName, string
           tabrowDef,[('RUNNUM':'unsigned int'),('CMSLSNUM','unsigned int'),('FLAG','string'),('COMMENT','string')]
           bulkinput,[[('RUNNUM',1234),('CMSLSNUM',1234),('FLAG','GOOD'),('COMMENT','coment')],[]]
        """
        try:
            dataEditor=self.__schema.tableHandle(tableName).dataEditor()
            insertdata=coral.AttributeList()
            for (columnname,columntype) in tabrowDef:
                insertdata.extend(columnname,columntype)
            bulkOperation=dataEditor.bulkInsert(insertdata,len(bulkinput))
            for valuelist in bulkinput:
                for (columnname,columnvalue) in valuelist:
#                    print 'columnname ',columnname,' columnvalue ',columnvalue
                    insertdata[columnname].setData(columnvalue)
                bulkOperation.processNextIteration()
            bulkOperation.flush()
            del bulkOperation
        except:
            raise
        

def dbUtil.dbUtil.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

Definition at line 230 of file dbUtil.py.

References nameDealer.entryTableName(), edm.print(), and str.

Referenced by dbUtil.dbUtil.createTable().

    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 as e:
          raise RuntimeError(' dbUtil.createEntryTable '+str(e))
      

def dbUtil.dbUtil.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

Definition at line 202 of file dbUtil.py.

References nameDealer.idTableColumnDefinition(), nameDealer.idTableName(), edm.print(), and str.

Referenced by dbUtil.dbUtil.createTable().

    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 dbUtil.dbUtil.createRevMapTable	(		self,
			tableName,
			deleteOld = True
	)

Create Rev table  for the given table.\n
Input: name of the table
Output: name of the id table 

Definition at line 254 of file dbUtil.py.

References edm.print(), nameDealer.revmapTableName(), and str.

Referenced by dbUtil.dbUtil.createTable().

    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 as e:
          raise RuntimeError(' dbUtil.createRevMapTable '+str(e))     
      

def dbUtil.dbUtil.createTable	(		self,
			description,
			withIdTable = False,
			withEntryTables = False,
			withRevMapTable = False
	)

Create table if non-existing, create Id table if required

Definition at line 173 of file dbUtil.py.

References dbUtil.dbUtil.createEntryTable(), dbUtil.dbUtil.createIDTable(), dbUtil.dbUtil.createRevMapTable(), nameDealer.entryTableName(), dataset.name, and str.

    def createTable( self,description,withIdTable=False,withEntryTables=False,withRevMapTable=False):
        """
        Create table if non-existing, create Id table if required
        """
        try:
            tableHandle=self.__schema.createTable(description)
            tableName=tableHandle.description().name()
            tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
            if withIdTable is True:
                self.createIDTable(tableName,True)
            if withEntryTables is True:
                entrytableName=nameDealer.entryTableName(tableName)
                self.createEntryTable(tableName,True)
                self.createIDTable(entrytableName,True)
            if withRevMapTable is True:
                self.createRevMapTable(tableName,True)
        except Exception as e:
            raise RuntimeError('dbUtil.createTable'+str(e))

def dbUtil.dbUtil.deleteRows	(		self,
			tableName,
			condition,
			conditionbindDict
	)

Delete row(s)

Definition at line 142 of file dbUtil.py.

References str.

    def deleteRows( self, tableName, condition, conditionbindDict ):
        """
        Delete row(s)
        """
        try:
            tableHandle = self.__schema.tableHandle(tableName)
            editor = tableHandle.dataEditor()
            editor.deleteRows( condition, conditionbindDict )
        except Exception as e:
            raise Exception(str(e))
        

def dbUtil.dbUtil.describeSchema

(

self

)

Print out the overview of the schema

Definition at line 22 of file dbUtil.py.

References dbUtil.dbUtil.listIndex(), edm.print(), and str.

    def describeSchema(self):
        """
        Print out the overview of the schema
        """
        try:
            tablelist=self.__schema.listTables()
            for t in tablelist:
                table = self.__schema.tableHandle(t)
                print('table ===',t,'===')
                n=table.description().numberOfColumns()
                for i in range(0,n):
                  columndesp=table.description().columnDescription(i)
                  print('\t',columndesp.name(),columndesp.type())
                if table.description().hasPrimaryKey():
                  print('Primary Key : ')
                  print('\t',table.description().primaryKey().columnNames())
                print('Indices : ')
                self.listIndex(t)
            viewlist=self.__schema.listViews()
            for v in viewlist:
                myview = self.__schema.viewHandle('V0')
                print('definition : ',myview.definition())
                n=myview.numberOfColumns()
                for i in range(0,n):
                  columndesp=view.column(i)
                  print('\t',columndesp.name(),columndesp.type())
        except Exception as e:
            raise Exception(str(e))

def dbUtil.dbUtil.dropAllTables

(

self

)

Drop all tables can be listed by schema.listTables

Definition at line 163 of file dbUtil.py.

References str.

    def dropAllTables( self ):
        """
        Drop all tables can be listed by schema.listTables
        """
        try:
            for t in self.__schema.listTables():
                self.__schema.dropTable(t)
        except Exception as e:
            raise Exception(str(e))

def dbUtil.dbUtil.dropTable	(		self,
			tableName
	)

Drop specified table.If associated Id table exists, drop also Id table

Definition at line 153 of file dbUtil.py.

References nameDealer.idTableName(), and str.

    def dropTable( self, tableName ):
        """
        Drop specified table.If associated Id table exists, drop also Id table
        """
        try:
            self.__schema.dropIfExistsTable( tableName )
            self.__schema.dropIfExistsTable( nameDealer.idTableName(tableName) )
        except Exception as e:
            raise Exception(str(e))

def dbUtil.dbUtil.existRow	(		self,
			tableName,
			condition,
			conditionDefDict,
			conditionDict
	)

Return true if one row fulfills the selection criteria

Definition at line 50 of file dbUtil.py.

References GetRecoTauVFromDQM_MC_cff.next, and str.

    def existRow( self, tableName, condition, conditionDefDict,conditionDict):
        """
        Return true if one row fulfills the selection criteria
        """
        try:
            tableHandle = self.__schema.tableHandle(tableName)
            query = tableHandle.newQuery()
            queryBind=coral.AttributeList()
            for colname,coltype in conditionDefDict.items():
                queryBind.extend(colname,coltype)
                queryBind[colname].setData(conditionDict[colname])
            query.setCondition(condition,queryBind)
            cursor = query.execute()
            result=False
            while ( next(cursor) ):
                result=True
                cursor.close()
            del query
            return result
        except Exception as e:
            raise Exception(str(e))

def dbUtil.dbUtil.insertOneRow	(		self,
			tableName,
			tabrowDefDict,
			tabrowValueDict
	)

Insert row 

Definition at line 71 of file dbUtil.py.

References str.

    def insertOneRow( self, tableName, tabrowDefDict, tabrowValueDict ):
        """
        Insert row 
        """
        try:
            tableHandle = self.__schema.tableHandle(tableName)
            editor = tableHandle.dataEditor()
            inputData = coral.AttributeList()
            for name,type in tabrowDefDict.items():
               # print name, type
                inputData.extend( name, type )
                inputData[name].setData(tabrowValueDict[name])
            editor.insertRow( inputData )
        except Exception as e:
            raise Exception('dbUtil.insertOneRow:'+str(e))

def dbUtil.dbUtil.listIndex	(		self,
			tablename
	)

Definition at line 13 of file dbUtil.py.

References edm.print().

Referenced by dbUtil.dbUtil.describeSchema().

    def listIndex(self,tablename):
        mytable=self.__schema.tableHandle(tablename)
        print('numberofindices ', mytable.description().numberOfIndices())
        for i in range(0,mytable.description().numberOfIndices()):
            index=mytable.description().index(i)
            print(' ', index.name(),' -> ')
            for iColumn in index.columnNames():
                print(iColumn)
            print(' (tablespace : ',index.tableSpaceName(),')')

def dbUtil.dbUtil.singleUpdate	(		self,
			tableName,
			setClause,
			updateCondition,
			inputData
	)

Definition at line 87 of file dbUtil.py.

References str.

    def singleUpdate( self,tableName,setClause,updateCondition,inputData):
        try:
            dataEditor=self.__schema.tableHandle(tableName).dataEditor()
            n=dataEditor.updateRows(setClause,updateCondition,inputData)
            return n
        except Exception as e:
            raise RuntimeError('dbUtil.updateOneRow:'+str(e))
    

def dbUtil.dbUtil.tableExists	(		self,
			tableName
	)

Tell whether table exists

Definition at line 192 of file dbUtil.py.

    def tableExists( self,tableName ):
        """
        Tell whether table exists
        """
        try:
          self.__schema.tableHandle(tableName)
          return True
        except coral.Exception as e:
          return False

def dbUtil.dbUtil.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)],[]]

Definition at line 95 of file dbUtil.py.

References str.

    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))

Member Data Documentation

dbUtil.dbUtil.__schema

private

Definition at line 11 of file dbUtil.py.