00001 import coral
00002 from RecoLuminosity.LumiDB import nameDealer
00003 class dbUtil(object):
00004 """Class wrap up all the database operations.\n
00005 """
00006 def __init__( self , schema):
00007 """
00008 Input: coral schema handle.
00009 """
00010 self.__schema = schema
00011
00012 def listIndex(self,tablename):
00013 mytable=self.__schema.tableHandle(tablename)
00014 print 'numberofindices ', mytable.description().numberOfIndices()
00015 for i in range(0,mytable.description().numberOfIndices()):
00016 index=mytable.description().index(i)
00017 print ' ', index.name(),' -> '
00018 for iColumn in index.columnNames():
00019 print iColumn
00020 print ' (tablespace : ',index.tableSpaceName(),')'
00021 def describeSchema(self):
00022 """
00023 Print out the overview of the schema
00024 """
00025 try:
00026 tablelist=self.__schema.listTables()
00027 for t in tablelist:
00028 table = self.__schema.tableHandle(t)
00029 print 'table ===',t,'==='
00030 n=table.description().numberOfColumns()
00031 for i in range(0,n):
00032 columndesp=table.description().columnDescription(i)
00033 print '\t',columndesp.name(),columndesp.type()
00034 if table.description().hasPrimaryKey():
00035 print 'Primary Key : '
00036 print '\t',table.description().primaryKey().columnNames()
00037 print 'Indices : '
00038 self.listIndex(t)
00039 viewlist=self.__schema.listViews()
00040 for v in viewlist:
00041 myview = self.__schema.viewHandle('V0')
00042 print 'definition : ',myview.definition()
00043 n=myview.numberOfColumns()
00044 for i in range(0,n):
00045 columndesp=view.column(i)
00046 print '\t',columndesp.name(),columndesp.type()
00047 except Exception, e:
00048 raise Exception, str(e)
00049 def existRow( self, tableName, condition, conditionDefDict,conditionDict):
00050 """
00051 Return true if one row fulfills the selection criteria
00052 """
00053 try:
00054 tableHandle = self.__schema.tableHandle(tableName)
00055 query = tableHandle.newQuery()
00056 queryBind=coral.AttributeList()
00057 for colname,coltype in conditionDefDict.items():
00058 queryBind.extend(colname,coltype)
00059 queryBind[colname].setData(conditionDict[colname])
00060 query.setCondition(condition,queryBind)
00061 cursor = query.execute()
00062 result=False
00063 while ( cursor.next() ):
00064 result=True
00065 cursor.close()
00066 del query
00067 return result
00068 except Exception, e:
00069 raise Exception, str(e)
00070 def insertOneRow( self, tableName, tabrowDefDict, tabrowValueDict ):
00071 """
00072 Insert row
00073 """
00074 try:
00075 tableHandle = self.__schema.tableHandle(tableName)
00076 editor = tableHandle.dataEditor()
00077 inputData = coral.AttributeList()
00078 for name,type in tabrowDefDict.items():
00079
00080 inputData.extend( name, type )
00081 inputData[name].setData(tabrowValueDict[name])
00082 editor.insertRow( inputData )
00083 except Exception, e:
00084 raise Exception, 'dbUtil.insertOneRow:'+str(e)
00085
00086 def singleUpdate( self,tableName,setClause,updateCondition,inputData):
00087 try:
00088 dataEditor=self.__schema.tableHandle(tableName).dataEditor()
00089 n=dataEditor.updateRows(setClause,updateCondition,inputData)
00090 return n
00091 except Exception, e:
00092 raise RuntimeError('dbUtil.updateOneRow:'+str(e))
00093
00094 def updateRows( self,tableName,updateAction,updateCondition,bindvarDef,bulkinput):
00095 '''
00096 update rows, note update must be ordered
00097 input :
00098 tableName, string
00099 updateAction,string e.g. flag=:newflag
00100 conditionstring, string ,e.g. runnum=:runnum and cmslsnum=:cmslsnum
00101 bindvarDef,[('newflag','string'),('runnum','unsigned int'),('cmslsnum','unsigned int')]
00102 bulkinput,[[('newflag','GOOD'),('runnum',1234),('cmslsnum',1)],[]]
00103 '''
00104 try:
00105 dataEditor=self.__schema.tableHandle(tableName).dataEditor()
00106 updateData=coral.AttributeList()
00107 for (columnname,columntype) in bindvarDef:
00108 updateData.extend(columnname,columntype)
00109 bulkOperation=dataEditor.bulkUpdateRows(updateAction,updateCondition,updateData,len(bulkinput))
00110 for valuelist in bulkinput:
00111 for (columnname,columnvalue) in valuelist:
00112 updateData[columnname].setData(columnvalue)
00113 bulkOperation.processNextIteration()
00114 bulkOperation.flush()
00115 del bulkOperation
00116 except Exception, e:
00117 raise Exception, 'dbUtil.updateRows:'+str(e)
00118 def bulkInsert( self, tableName, tabrowDef, bulkinput):
00119 """
00120 input:
00121 tableName, string
00122 tabrowDef,[('RUNNUM':'unsigned int'),('CMSLSNUM','unsigned int'),('FLAG','string'),('COMMENT','string')]
00123 bulkinput,[[('RUNNUM',1234),('CMSLSNUM',1234),('FLAG','GOOD'),('COMMENT','coment')],[]]
00124 """
00125 try:
00126 dataEditor=self.__schema.tableHandle(tableName).dataEditor()
00127 insertdata=coral.AttributeList()
00128 for (columnname,columntype) in tabrowDef:
00129 insertdata.extend(columnname,columntype)
00130 bulkOperation=dataEditor.bulkInsert(insertdata,len(bulkinput))
00131 for valuelist in bulkinput:
00132 for (columnname,columnvalue) in valuelist:
00133
00134 insertdata[columnname].setData(columnvalue)
00135 bulkOperation.processNextIteration()
00136 bulkOperation.flush()
00137 del bulkOperation
00138 except:
00139 raise
00140
00141 def deleteRows( self, tableName, condition, conditionbindDict ):
00142 """
00143 Delete row(s)
00144 """
00145 try:
00146 tableHandle = self.__schema.tableHandle(tableName)
00147 editor = tableHandle.dataEditor()
00148 editor.deleteRows( condition, conditionbindDict )
00149 except Exception, e:
00150 raise Exception, str(e)
00151
00152 def dropTable( self, tableName ):
00153 """
00154 Drop specified table.If associated Id table exists, drop also Id table
00155 """
00156 try:
00157 self.__schema.dropIfExistsTable( tableName )
00158 self.__schema.dropIfExistsTable( nameDealer.idTableName(tableName) )
00159 except Exception, e:
00160 raise Exception, str(e)
00161
00162 def dropAllTables( self ):
00163 """
00164 Drop all tables can be listed by schema.listTables
00165 """
00166 try:
00167 for t in self.__schema.listTables():
00168 self.__schema.dropTable(t)
00169 except Exception, e:
00170 raise Exception, str(e)
00171
00172 def createTable( self,description,withIdTable=False,withEntryTables=False,withRevMapTable=False):
00173 """
00174 Create table if non-existing, create Id table if required
00175 """
00176 try:
00177 tableHandle=self.__schema.createTable(description)
00178 tableName=tableHandle.description().name()
00179 tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
00180 if withIdTable is True:
00181 self.createIDTable(tableName,True)
00182 if withEntryTables is True:
00183 entrytableName=nameDealer.entryTableName(tableName)
00184 self.createEntryTable(tableName,True)
00185 self.createIDTable(entrytableName,True)
00186 if withRevMapTable is True:
00187 self.createRevMapTable(tableName,True)
00188 except Exception, e:
00189 raise RuntimeError('dbUtil.createTable'+str(e))
00190
00191 def tableExists( self,tableName ):
00192 """
00193 Tell whether table exists
00194 """
00195 try:
00196 self.__schema.tableHandle(tableName)
00197 return True
00198 except coral.Exception, e:
00199 return False
00200
00201 def createIDTable( self, tableName, deleteOld=True ):
00202 """
00203 Create ID table for the given table.\n
00204 Input: name of the table which needs new associated id table
00205 Output: name of the id table created
00206 """
00207 try:
00208 idtableName=nameDealer.idTableName(tableName)
00209 if deleteOld is True:
00210 self.__schema.dropIfExistsTable(idtableName)
00211 else:
00212 if self.__schema.existsTable(idtableName):
00213 print 'table '+idtableName+' exists, do nothing'
00214 return
00215 description = coral.TableDescription()
00216 description.setName( idtableName )
00217 description.setPrimaryKey( nameDealer.idTableColumnDefinition()[0] )
00218 description.insertColumn( nameDealer.idTableColumnDefinition()[0], nameDealer.idTableColumnDefinition()[1])
00219 idtableHandle=self.__schema.createTable( description )
00220 idtableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
00221 inputData = coral.AttributeList()
00222 editor = idtableHandle.dataEditor()
00223 editor.rowBuffer( inputData )
00224 inputData[ nameDealer.idTableColumnDefinition()[0] ].setData(0)
00225 editor.insertRow( inputData )
00226 except Exception, e:
00227 raise RuntimeError('dbUtil.createIDTable'+str(e))
00228
00229 def createEntryTable( self, tableName, deleteOld=True ):
00230 """
00231 Create Entry table for the given table.\n
00232 Input: name of the table which needs new associated id table
00233 Output: name of the id table created
00234 """
00235 try:
00236 entrytableName=nameDealer.entryTableName(tableName)
00237 if deleteOld is True:
00238 self.__schema.dropIfExistsTable(entrytableName)
00239 else:
00240 if self.__schema.existsTable(entrytableName):
00241 print 'table '+entrytableName+' exists, do nothing'
00242 return
00243 description = coral.TableDescription()
00244 description.setName( entrytableName )
00245 description.insertColumn( 'ENTRY_ID' ,'unsigned long long')
00246 description.insertColumn( 'REVISION_ID' ,'unsigned long long')
00247 description.insertColumn( 'NAME' ,'string')
00248 tableHandle=self.__schema.createTable( description )
00249 tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
00250 except Exception, e:
00251 raise RuntimeError(' dbUtil.createEntryTable '+str(e))
00252
00253 def createRevMapTable( self, tableName, deleteOld=True ):
00254 """
00255 Create Rev table for the given table.\n
00256 Input: name of the table
00257 Output: name of the id table
00258 """
00259 try:
00260 revmaptableName=nameDealer.revmapTableName(tableName)
00261 if deleteOld is True:
00262 self.__schema.dropIfExistsTable(revmaptableName)
00263 else:
00264 if self.__schema.existsTable(revmaptableName):
00265 print 'table '+revmaptableName+' exists, do nothing'
00266 return
00267 description = coral.TableDescription()
00268 description.setName( revmaptableName )
00269 description.insertColumn( 'DATA_ID','unsigned long long')
00270 description.insertColumn( 'REVISION_ID' ,'unsigned long long')
00271 tableHandle=self.__schema.createTable( description )
00272 tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
00273 except Exception, e:
00274 raise RuntimeError(' dbUtil.createRevMapTable '+str(e))
00275
00276 if __name__ == "__main__":
00277 pass
1.7.3