Public Member Functions
def	__init__
def	bulkInsert
def	createEntryTable
def	createIDTable
def	createRevMapTable
def	createTable
def	deleteRows
def	describeSchema
def	dropAllTables
def	dropTable
def	existRow
def	insertOneRow
def	listIndex
def	singleUpdate
def	tableExists
def	updateRows
Private Attributes
	__schema

Detailed Description

Class wrap up all the database operations.\n

Definition at line 3 of file dbUtil.py.

Constructor & Destructor Documentation

def dbUtil::dbUtil::__init__	(	self,
		schema
	)

Input: coral schema handle.

Definition at line 6 of file dbUtil.py.

00007                                 :
00008         """
00009         Input: coral schema handle.
00010         """
00011         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 118 of file dbUtil.py.

00119                                                           :
00120         """
00121         input:
00122            tableName, string
00123            tabrowDef,[('RUNNUM':'unsigned int'),('CMSLSNUM','unsigned int'),('FLAG','string'),('COMMENT','string')]
00124            bulkinput,[[('RUNNUM',1234),('CMSLSNUM',1234),('FLAG','GOOD'),('COMMENT','coment')],[]]
00125         """
00126         try:
00127             dataEditor=self.__schema.tableHandle(tableName).dataEditor()
00128             insertdata=coral.AttributeList()
00129             for (columnname,columntype) in tabrowDef:
00130                 insertdata.extend(columnname,columntype)
00131             bulkOperation=dataEditor.bulkInsert(insertdata,len(bulkinput))
00132             for valuelist in bulkinput:
00133                 for (columnname,columnvalue) in valuelist:
00134 #                    print 'columnname ',columnname,' columnvalue ',columnvalue
00135                     insertdata[columnname].setData(columnvalue)
00136                 bulkOperation.processNextIteration()
00137             bulkOperation.flush()
00138             del bulkOperation
00139         except:
00140             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 229 of file dbUtil.py.

00230                                                            :
00231         """
00232         Create Entry table  for the given table.\n
00233         Input: name of the table which needs new associated id table
00234         Output: name of the id table created
00235         """
00236         try:
00237           entrytableName=nameDealer.entryTableName(tableName)
00238           if deleteOld is True:
00239             self.__schema.dropIfExistsTable(entrytableName)
00240           else:
00241             if self.__schema.existsTable(entrytableName):
00242                print 'table '+entrytableName+' exists, do nothing'
00243                return
00244           description = coral.TableDescription()
00245           description.setName( entrytableName )
00246           description.insertColumn( 'ENTRY_ID' ,'unsigned long long')
00247           description.insertColumn( 'REVISION_ID' ,'unsigned long long')
00248           description.insertColumn( 'NAME' ,'string')
00249           tableHandle=self.__schema.createTable( description )
00250           tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
00251         except Exception, e:
00252           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 201 of file dbUtil.py.

00202                                                         :
00203         """
00204         Create ID table  for the given table.\n
00205         Input: name of the table which needs new associated id table
00206         Output: name of the id table created
00207         """
00208         try:
00209           idtableName=nameDealer.idTableName(tableName)
00210           if deleteOld is True:
00211             self.__schema.dropIfExistsTable(idtableName)
00212           else:
00213             if self.__schema.existsTable(idtableName):
00214                print 'table '+idtableName+' exists, do nothing'
00215                return
00216           description = coral.TableDescription()
00217           description.setName( idtableName )
00218           description.setPrimaryKey( nameDealer.idTableColumnDefinition()[0] )
00219           description.insertColumn( nameDealer.idTableColumnDefinition()[0], nameDealer.idTableColumnDefinition()[1])
00220           idtableHandle=self.__schema.createTable( description )
00221           idtableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
00222           inputData = coral.AttributeList()
00223           editor = idtableHandle.dataEditor()
00224           editor.rowBuffer( inputData )
00225           inputData[ nameDealer.idTableColumnDefinition()[0] ].setData(0)
00226           editor.insertRow( inputData )
00227         except Exception, e:
00228           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 253 of file dbUtil.py.

00254                                                             :
00255         """
00256         Create Rev table  for the given table.\n
00257         Input: name of the table
00258         Output: name of the id table 
00259         """
00260         try:
00261           revmaptableName=nameDealer.revmapTableName(tableName)
00262           if deleteOld is True:
00263             self.__schema.dropIfExistsTable(revmaptableName)
00264           else:
00265             if self.__schema.existsTable(revmaptableName):
00266                print 'table '+revmaptableName+' exists, do nothing'
00267                return
00268           description = coral.TableDescription()
00269           description.setName( revmaptableName )
00270           description.insertColumn( 'DATA_ID','unsigned long long')
00271           description.insertColumn( 'REVISION_ID' ,'unsigned long long')
00272           tableHandle=self.__schema.createTable( description )
00273           tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
00274         except Exception, e:
00275           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 172 of file dbUtil.py.

00173                                                                                                     :
00174         """
00175         Create table if non-existing, create Id table if required
00176         """
00177         try:
00178             tableHandle=self.__schema.createTable(description)
00179             tableName=tableHandle.description().name()
00180             tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
00181             if withIdTable is True:
00182                 self.createIDTable(tableName,True)
00183             if withEntryTables is True:
00184                 entrytableName=nameDealer.entryTableName(tableName)
00185                 self.createEntryTable(tableName,True)
00186                 self.createIDTable(entrytableName,True)
00187             if withRevMapTable is True:
00188                 self.createRevMapTable(tableName,True)
00189         except Exception, e:
00190             raise RuntimeError('dbUtil.createTable'+str(e))

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

Delete row(s)

Definition at line 141 of file dbUtil.py.

00142                                                                    :
00143         """
00144         Delete row(s)
00145         """
00146         try:
00147             tableHandle = self.__schema.tableHandle(tableName)
00148             editor = tableHandle.dataEditor()
00149             editor.deleteRows( condition, conditionbindDict )
00150         except Exception, e:
00151             raise Exception, str(e)

def dbUtil::dbUtil::describeSchema ( self )

Print out the overview of the schema

Definition at line 21 of file dbUtil.py.

00022                             :
00023         """
00024         Print out the overview of the schema
00025         """
00026         try:
00027             tablelist=self.__schema.listTables()
00028             for t in tablelist:
00029                 table = self.__schema.tableHandle(t)
00030                 print 'table ===',t,'==='
00031                 n=table.description().numberOfColumns()
00032                 for i in range(0,n):
00033                   columndesp=table.description().columnDescription(i)
00034                   print '\t',columndesp.name(),columndesp.type()
00035                 if table.description().hasPrimaryKey():
00036                   print 'Primary Key : '
00037                   print '\t',table.description().primaryKey().columnNames()
00038                 print 'Indices : '
00039                 self.listIndex(t)
00040             viewlist=self.__schema.listViews()
00041             for v in viewlist:
00042                 myview = self.__schema.viewHandle('V0')
00043                 print 'definition : ',myview.definition()
00044                 n=myview.numberOfColumns()
00045                 for i in range(0,n):
00046                   columndesp=view.column(i)
00047                   print '\t',columndesp.name(),columndesp.type()
00048         except Exception, e:
            raise Exception, str(e)

def dbUtil::dbUtil::dropAllTables ( self )

Drop all tables can be listed by schema.listTables

Definition at line 162 of file dbUtil.py.

00163                              :
00164         """
00165         Drop all tables can be listed by schema.listTables
00166         """
00167         try:
00168             for t in self.__schema.listTables():
00169                 self.__schema.dropTable(t)
00170         except Exception, e:
00171             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 152 of file dbUtil.py.

00153                                     :
00154         """
00155         Drop specified table.If associated Id table exists, drop also Id table
00156         """
00157         try:
00158             self.__schema.dropIfExistsTable( tableName )
00159             self.__schema.dropIfExistsTable( nameDealer.idTableName(tableName) )
00160         except Exception, e:
00161             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 49 of file dbUtil.py.

00050                                                                              :
00051         """
00052         Return true if one row fulfills the selection criteria
00053         """
00054         try:
00055             tableHandle = self.__schema.tableHandle(tableName)
00056             query = tableHandle.newQuery()
00057             queryBind=coral.AttributeList()
00058             for colname,coltype in conditionDefDict.items():
00059                 queryBind.extend(colname,coltype)
00060                 queryBind[colname].setData(conditionDict[colname])
00061             query.setCondition(condition,queryBind)
00062             cursor = query.execute()
00063             result=False
00064             while ( cursor.next() ):
00065                 result=True
00066                 cursor.close()
00067             del query
00068             return result
00069         except Exception, e:
            raise Exception, str(e)

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

Insert row

Definition at line 70 of file dbUtil.py.

00071                                                                        :
00072         """
00073         Insert row 
00074         """
00075         try:
00076             tableHandle = self.__schema.tableHandle(tableName)
00077             editor = tableHandle.dataEditor()
00078             inputData = coral.AttributeList()
00079             for name,type in tabrowDefDict.items():
00080                # print name, type
00081                 inputData.extend( name, type )
00082                 inputData[name].setData(tabrowValueDict[name])
00083             editor.insertRow( inputData )
00084         except Exception, e:
00085             raise Exception, 'dbUtil.insertOneRow:'+str(e)

def dbUtil::dbUtil::listIndex	(	self,
		tablename
	)

Definition at line 12 of file dbUtil.py.

00013                                  :
00014         mytable=self.__schema.tableHandle(tablename)
00015         print 'numberofindices ', mytable.description().numberOfIndices()
00016         for i in range(0,mytable.description().numberOfIndices()):
00017             index=mytable.description().index(i)
00018             print ' ', index.name(),' -> '
00019             for iColumn in index.columnNames():
00020                 print iColumn
            print ' (tablespace : ',index.tableSpaceName(),')'

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

Definition at line 86 of file dbUtil.py.

00087                                                                          :
00088         try:
00089             dataEditor=self.__schema.tableHandle(tableName).dataEditor()
00090             n=dataEditor.updateRows(setClause,updateCondition,inputData)
00091             return n
00092         except Exception, e:
00093             raise RuntimeError('dbUtil.updateOneRow:'+str(e))

def dbUtil::dbUtil::tableExists	(	self,
		tableName
	)

Tell whether table exists

Definition at line 191 of file dbUtil.py.

00192                                      :
00193         """
00194         Tell whether table exists
00195         """
00196         try:
00197           self.__schema.tableHandle(tableName)
00198           return True
00199         except coral.Exception, e:
00200           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 94 of file dbUtil.py.

00095                                                                                      :
00096         '''
00097         update rows, note update must be ordered
00098         input :
00099            tableName, string
00100            updateAction,string  e.g. flag=:newflag
00101            conditionstring, string ,e.g. runnum=:runnum and cmslsnum=:cmslsnum
00102            bindvarDef,[('newflag','string'),('runnum','unsigned int'),('cmslsnum','unsigned int')]
00103            bulkinput,[[('newflag','GOOD'),('runnum',1234),('cmslsnum',1)],[]]
00104         '''
00105         try:
00106             dataEditor=self.__schema.tableHandle(tableName).dataEditor()
00107             updateData=coral.AttributeList()
00108             for (columnname,columntype) in bindvarDef:
00109                 updateData.extend(columnname,columntype)
00110             bulkOperation=dataEditor.bulkUpdateRows(updateAction,updateCondition,updateData,len(bulkinput))
00111             for valuelist in bulkinput:
00112                 for (columnname,columnvalue) in valuelist:
00113                     updateData[columnname].setData(columnvalue)
00114                 bulkOperation.processNextIteration()
00115             bulkOperation.flush()
00116             del bulkOperation
00117         except Exception, e:
            raise Exception, 'dbUtil.updateRows:'+str(e)

Member Data Documentation

dbUtil::dbUtil::__schema [private]

Definition at line 8 of file dbUtil.py.

dbUtil::dbUtil Class Reference

Public Member Functions

Private Attributes

Detailed Description

Constructor & Destructor Documentation

Member Function Documentation

Member Data Documentation