CMS 3D CMS Logo
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Properties Friends Macros Pages
List of all members | Public Member Functions | Private Attributes
dbUtil.dbUtil Class Reference
Inheritance diagram for dbUtil.dbUtil:

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.

6 
7  def __init__( self , schema):
8  """
9  Input: coral schema handle.
10  """
11  self.__schema = schema
dbUtil.dbUtil.__init__
def __init__
Definition: dbUtil.py:6

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.

118 
119  def bulkInsert( self, tableName, tabrowDef, bulkinput):
120  """
121  input:
122  tableName, string
123  tabrowDef,[('RUNNUM':'unsigned int'),('CMSLSNUM','unsigned int'),('FLAG','string'),('COMMENT','string')]
124  bulkinput,[[('RUNNUM',1234),('CMSLSNUM',1234),('FLAG','GOOD'),('COMMENT','coment')],[]]
125  """
126  try:
127  dataEditor=self.__schema.tableHandle(tableName).dataEditor()
128  insertdata=coral.AttributeList()
129  for (columnname,columntype) in tabrowDef:
130  insertdata.extend(columnname,columntype)
131  bulkOperation=dataEditor.bulkInsert(insertdata,len(bulkinput))
132  for valuelist in bulkinput:
133  for (columnname,columnvalue) in valuelist:
134 # print 'columnname ',columnname,' columnvalue ',columnvalue
135  insertdata[columnname].setData(columnvalue)
136  bulkOperation.processNextIteration()
137  bulkOperation.flush()
138  del bulkOperation
139  except:
140  raise
dbUtil.dbUtil.bulkInsert
def bulkInsert
Definition: dbUtil.py:118
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.

References nameDealer.entryTableName().

Referenced by dbUtil.dbUtil.createTable().

229 
230  def createEntryTable( self, tableName, deleteOld=True ):
231  """
232  Create Entry table for the given table.\n
233  Input: name of the table which needs new associated id table
234  Output: name of the id table created
235  """
236  try:
237  entrytableName=nameDealer.entryTableName(tableName)
238  if deleteOld is True:
239  self.__schema.dropIfExistsTable(entrytableName)
240  else:
241  if self.__schema.existsTable(entrytableName):
242  print 'table '+entrytableName+' exists, do nothing'
243  return
244  description = coral.TableDescription()
245  description.setName( entrytableName )
246  description.insertColumn( 'ENTRY_ID' ,'unsigned long long')
247  description.insertColumn( 'REVISION_ID' ,'unsigned long long')
248  description.insertColumn( 'NAME' ,'string',56,False)
249  tableHandle=self.__schema.createTable( description )
250  tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
251  except Exception, e:
252  raise RuntimeError(' dbUtil.createEntryTable '+str(e))
nameDealer.entryTableName
def entryTableName
Definition: nameDealer.py:103
dbUtil.dbUtil.createEntryTable
def createEntryTable
Definition: dbUtil.py:229
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.

References nameDealer.idTableColumnDefinition(), and nameDealer.idTableName().

Referenced by dbUtil.dbUtil.createTable().

201 
202  def createIDTable( self, tableName, deleteOld=True ):
203  """
204  Create ID table for the given table.\n
205  Input: name of the table which needs new associated id table
206  Output: name of the id table created
207  """
208  try:
209  idtableName=nameDealer.idTableName(tableName)
210  if deleteOld is True:
211  self.__schema.dropIfExistsTable(idtableName)
212  else:
213  if self.__schema.existsTable(idtableName):
214  print 'table '+idtableName+' exists, do nothing'
215  return
216  description = coral.TableDescription()
217  description.setName( idtableName )
218  description.setPrimaryKey( nameDealer.idTableColumnDefinition()[0] )
219  description.insertColumn( nameDealer.idTableColumnDefinition()[0], nameDealer.idTableColumnDefinition()[1])
220  idtableHandle=self.__schema.createTable( description )
221  idtableHandle.privilegeManager().grantToPublic( coral.privilege_Select )
222  inputData = coral.AttributeList()
223  editor = idtableHandle.dataEditor()
224  editor.rowBuffer( inputData )
225  inputData[ nameDealer.idTableColumnDefinition()[0] ].setData(0)
226  editor.insertRow( inputData )
227  except Exception, e:
228  raise RuntimeError('dbUtil.createIDTable'+str(e))
dbUtil.dbUtil.createIDTable
def createIDTable
Definition: dbUtil.py:201
nameDealer.idTableName
def idTableName
Definition: nameDealer.py:94
nameDealer.idTableColumnDefinition
def idTableColumnDefinition
Definition: nameDealer.py:97
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.

References nameDealer.revmapTableName().

Referenced by dbUtil.dbUtil.createTable().

253 
254  def createRevMapTable( self, tableName, deleteOld=True ):
255  """
256  Create Rev table for the given table.\n
257  Input: name of the table
258  Output: name of the id table
259  """
260  try:
261  revmaptableName=nameDealer.revmapTableName(tableName)
262  if deleteOld is True:
263  self.__schema.dropIfExistsTable(revmaptableName)
264  else:
265  if self.__schema.existsTable(revmaptableName):
266  print 'table '+revmaptableName+' exists, do nothing'
267  return
268  description = coral.TableDescription()
269  description.setName( revmaptableName )
270  description.insertColumn( 'DATA_ID','unsigned long long')
271  description.insertColumn( 'REVISION_ID' ,'unsigned long long')
272  tableHandle=self.__schema.createTable( description )
273  tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
274  except Exception, e:
275  raise RuntimeError(' dbUtil.createRevMapTable '+str(e))
nameDealer.revmapTableName
def revmapTableName
Definition: nameDealer.py:100
dbUtil.dbUtil.createRevMapTable
def createRevMapTable
Definition: dbUtil.py:253
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.

References dbUtil.dbUtil.createEntryTable(), python.IdGenerator.IdGenerator.createIDTable(), dbUtil.dbUtil.createIDTable(), dbUtil.dbUtil.createRevMapTable(), nameDealer.entryTableName(), and mergeVDriftHistosByStation.name.

172 
173  def createTable( self,description,withIdTable=False,withEntryTables=False,withRevMapTable=False):
174  """
175  Create table if non-existing, create Id table if required
176  """
177  try:
178  tableHandle=self.__schema.createTable(description)
179  tableName=tableHandle.description().name()
180  tableHandle.privilegeManager().grantToPublic(coral.privilege_Select)
181  if withIdTable is True:
182  self.createIDTable(tableName,True)
183  if withEntryTables is True:
184  entrytableName=nameDealer.entryTableName(tableName)
185  self.createEntryTable(tableName,True)
186  self.createIDTable(entrytableName,True)
187  if withRevMapTable is True:
188  self.createRevMapTable(tableName,True)
189  except Exception, e:
190  raise RuntimeError('dbUtil.createTable'+str(e))
dbUtil.dbUtil.createIDTable
def createIDTable
Definition: dbUtil.py:201
dbUtil.dbUtil.createRevMapTable
def createRevMapTable
Definition: dbUtil.py:253
dbUtil.dbUtil.createTable
def createTable
Definition: dbUtil.py:172
nameDealer.entryTableName
def entryTableName
Definition: nameDealer.py:103
dbUtil.dbUtil.createEntryTable
def createEntryTable
Definition: dbUtil.py:229
def dbUtil.dbUtil.deleteRows (   self,
  tableName,
  condition,
  conditionbindDict 
) 
Delete row(s)

Definition at line 141 of file dbUtil.py.

141 
142  def deleteRows( self, tableName, condition, conditionbindDict ):
143  """
144  Delete row(s)
145  """
146  try:
147  tableHandle = self.__schema.tableHandle(tableName)
148  editor = tableHandle.dataEditor()
149  editor.deleteRows( condition, conditionbindDict )
150  except Exception, e:
151  raise Exception, str(e)
dbUtil.dbUtil.deleteRows
def deleteRows
Definition: dbUtil.py:141
def dbUtil.dbUtil.describeSchema (   self) 
Print out the overview of the schema

Definition at line 21 of file dbUtil.py.

References dbUtil.dbUtil.listIndex().

21 
22  def describeSchema(self):
23  """
24  Print out the overview of the schema
25  """
26  try:
27  tablelist=self.__schema.listTables()
28  for t in tablelist:
29  table = self.__schema.tableHandle(t)
30  print 'table ===',t,'==='
31  n=table.description().numberOfColumns()
32  for i in range(0,n):
33  columndesp=table.description().columnDescription(i)
34  print '\t',columndesp.name(),columndesp.type()
35  if table.description().hasPrimaryKey():
36  print 'Primary Key : '
37  print '\t',table.description().primaryKey().columnNames()
38  print 'Indices : '
39  self.listIndex(t)
40  viewlist=self.__schema.listViews()
41  for v in viewlist:
42  myview = self.__schema.viewHandle('V0')
43  print 'definition : ',myview.definition()
44  n=myview.numberOfColumns()
45  for i in range(0,n):
46  columndesp=view.column(i)
47  print '\t',columndesp.name(),columndesp.type()
48  except Exception, e:
raise Exception, str(e)
dbUtil.dbUtil.describeSchema
def describeSchema
Definition: dbUtil.py:21
dbUtil.dbUtil.listIndex
def listIndex
Definition: dbUtil.py:12
def dbUtil.dbUtil.dropAllTables (   self) 
Drop all tables can be listed by schema.listTables

Definition at line 162 of file dbUtil.py.

162 
163  def dropAllTables( self ):
164  """
165  Drop all tables can be listed by schema.listTables
166  """
167  try:
168  for t in self.__schema.listTables():
169  self.__schema.dropTable(t)
170  except Exception, e:
171  raise Exception, str(e)
dbUtil.dbUtil.dropAllTables
def dropAllTables
Definition: dbUtil.py:162
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.

References nameDealer.idTableName().

152 
153  def dropTable( self, tableName ):
154  """
155  Drop specified table.If associated Id table exists, drop also Id table
156  """
157  try:
158  self.__schema.dropIfExistsTable( tableName )
159  self.__schema.dropIfExistsTable( nameDealer.idTableName(tableName) )
160  except Exception, e:
161  raise Exception, str(e)
nameDealer.idTableName
def idTableName
Definition: nameDealer.py:94
dbUtil.dbUtil.dropTable
def dropTable
Definition: dbUtil.py:152
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.

49 
50  def existRow( self, tableName, condition, conditionDefDict,conditionDict):
51  """
52  Return true if one row fulfills the selection criteria
53  """
54  try:
55  tableHandle = self.__schema.tableHandle(tableName)
56  query = tableHandle.newQuery()
57  queryBind=coral.AttributeList()
58  for colname,coltype in conditionDefDict.items():
59  queryBind.extend(colname,coltype)
60  queryBind[colname].setData(conditionDict[colname])
61  query.setCondition(condition,queryBind)
62  cursor = query.execute()
63  result=False
64  while ( cursor.next() ):
65  result=True
66  cursor.close()
67  del query
68  return result
69  except Exception, e:
raise Exception, str(e)
dbUtil.dbUtil.existRow
def existRow
Definition: dbUtil.py:49
def dbUtil.dbUtil.insertOneRow (   self,
  tableName,
  tabrowDefDict,
  tabrowValueDict 
) 
Insert row

Definition at line 70 of file dbUtil.py.

70 
71  def insertOneRow( self, tableName, tabrowDefDict, tabrowValueDict ):
72  """
73  Insert row
74  """
75  try:
76  tableHandle = self.__schema.tableHandle(tableName)
77  editor = tableHandle.dataEditor()
78  inputData = coral.AttributeList()
79  for name,type in tabrowDefDict.items():
80  # print name, type
81  inputData.extend( name, type )
82  inputData[name].setData(tabrowValueDict[name])
83  editor.insertRow( inputData )
84  except Exception, e:
85  raise Exception, 'dbUtil.insertOneRow:'+str(e)
dbUtil.dbUtil.insertOneRow
def insertOneRow
Definition: dbUtil.py:70
def dbUtil.dbUtil.listIndex (   self,
  tablename 
)

Definition at line 12 of file dbUtil.py.

References getHLTprescales.index.

Referenced by dbUtil.dbUtil.describeSchema().

12 
13  def listIndex(self,tablename):
14  mytable=self.__schema.tableHandle(tablename)
15  print 'numberofindices ', mytable.description().numberOfIndices()
16  for i in range(0,mytable.description().numberOfIndices()):
17  index=mytable.description().index(i)
18  print ' ', index.name(),' -> '
19  for iColumn in index.columnNames():
20  print iColumn
print ' (tablespace : ',index.tableSpaceName(),')'
dbUtil.dbUtil.listIndex
def listIndex
Definition: dbUtil.py:12
def dbUtil.dbUtil.singleUpdate (   self,
  tableName,
  setClause,
  updateCondition,
  inputData 
)

Definition at line 86 of file dbUtil.py.

86 
87  def singleUpdate( self,tableName,setClause,updateCondition,inputData):
88  try:
89  dataEditor=self.__schema.tableHandle(tableName).dataEditor()
90  n=dataEditor.updateRows(setClause,updateCondition,inputData)
91  return n
92  except Exception, e:
93  raise RuntimeError('dbUtil.updateOneRow:'+str(e))
dbUtil.dbUtil.singleUpdate
def singleUpdate
Definition: dbUtil.py:86
def dbUtil.dbUtil.tableExists (   self,
  tableName 
) 
Tell whether table exists

Definition at line 191 of file dbUtil.py.

191 
192  def tableExists( self,tableName ):
193  """
194  Tell whether table exists
195  """
196  try:
197  self.__schema.tableHandle(tableName)
198  return True
199  except coral.Exception, e:
200  return False
dbUtil.dbUtil.tableExists
def tableExists
Definition: dbUtil.py:191
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.

94 
95  def updateRows( self,tableName,updateAction,updateCondition,bindvarDef,bulkinput):
96  '''
97  update rows, note update must be ordered
98  input :
99  tableName, string
100  updateAction,string e.g. flag=:newflag
101  conditionstring, string ,e.g. runnum=:runnum and cmslsnum=:cmslsnum
102  bindvarDef,[('newflag','string'),('runnum','unsigned int'),('cmslsnum','unsigned int')]
103  bulkinput,[[('newflag','GOOD'),('runnum',1234),('cmslsnum',1)],[]]
104  '''
105  try:
106  dataEditor=self.__schema.tableHandle(tableName).dataEditor()
107  updateData=coral.AttributeList()
108  for (columnname,columntype) in bindvarDef:
109  updateData.extend(columnname,columntype)
110  bulkOperation=dataEditor.bulkUpdateRows(updateAction,updateCondition,updateData,len(bulkinput))
111  for valuelist in bulkinput:
112  for (columnname,columnvalue) in valuelist:
113  updateData[columnname].setData(columnvalue)
114  bulkOperation.processNextIteration()
115  bulkOperation.flush()
116  del bulkOperation
117  except Exception, e:
raise Exception, 'dbUtil.updateRows:'+str(e)
dbUtil.dbUtil.updateRows
def updateRows
Definition: dbUtil.py:94

Member Data Documentation

dbUtil.dbUtil.__schema
private

Definition at line 10 of file dbUtil.py.

Generated for CMSSW Reference Manual by doxygen 1.8.5