CMS 3D CMS Logo

/data/refman/pasoursint/CMSSW_5_3_3/src/CondCore/TagCollection/python/entryComment.py

Go to the documentation of this file.
00001 import coral
00002 import DBImpl
00003 import CommonUtils
00004 
00005 class entryComment(object):
00006     """Class add optional comment on given entry in a given table\n
00007     """
00008     def __init__( self, session ):
00009         """Input: coral schema handle.
00010         """
00011         self.__session = session
00012         self.__entryCommentTableColumns={'entryid':'unsigned long','tablename':'string','comment':'string'}
00013         self.__entryCommentTableNotNullColumns=['entryid','tablename']
00014         self.__entryCommentTablePK=('entryid','tablename')
00015 
00016     def existCommentTable(self):
00017         """Check if entry comment table exists
00018         """
00019         try:
00020             transaction=self.__session.transaction()
00021             transaction.start(True)
00022             schema = self.__session.nominalSchema()
00023             result=schema.existsTable(CommonUtils.commentTableName())
00024             transaction.commit()
00025             #print result
00026         except Exception, er:
00027             transaction.rollback()
00028             raise Exception, str(er)
00029         return result
00030     
00031     def createEntryCommentTable(self):
00032         """Create entry comment able.Existing table will be deleted.
00033         """
00034         try:
00035            transaction=self.__session.transaction()
00036            transaction.start()
00037            schema = self.__session.nominalSchema()
00038            schema.dropIfExistsTable(CommonUtils.commentTableName())
00039            description = coral.TableDescription()
00040            description.setName(CommonUtils.commentTableName())
00041            for columnName, columnType in self.__entryCommentTableColumns.items():
00042                description.insertColumn(columnName,columnType)
00043            for columnName in self.__entryCommentTableNotNullColumns:
00044                description.setNotNullConstraint(columnName,True)
00045            description.setPrimaryKey(self.__entryCommentTablePK)
00046            tablehandle=schema.createTable(description)
00047            tablehandle.privilegeManager().grantToPublic(coral.privilege_Select)
00048            transaction.commit()
00049         except Exception, e:
00050            transaction.rollback() 
00051            raise Exception, str(e)
00052     def insertComment( self, tablename, entryid,comment ):
00053         """insert comment on the given entry of given table
00054         """
00055         transaction=self.__session.transaction()
00056         try:
00057             transaction.start(False)
00058             tabrowValueDict={'entryid':entryid,'tablename':tablename,'comment':comment}
00059             schema = self.__session.nominalSchema()
00060             dbop=DBImpl.DBImpl(schema)
00061             dbop.insertOneRow(CommonUtils.commentTableName(),self.__entryCommentTableColumns,tabrowValueDict)
00062             transaction.commit()
00063         except Exception, e:
00064             transaction.rollback()
00065             raise Exception, str(e)
00066     def bulkinsertComments( self, tableName,bulkinput):
00067         """bulk insert comments for a given table
00068         bulkinput [{'entryid':unsigned long, 'tablename':string,'comment':string}]
00069         """
00070         transaction=self.__session.transaction()
00071         try:
00072             transaction.start(False)
00073             schema = self.__session.nominalSchema()
00074             dbop=DBImpl.DBImpl(schema)
00075             dbop.bulkInsert(CommonUtils.commentTableName(),self.__entryCommentTableColumns,bulkinput)
00076             transaction.commit()  
00077         except Exception, e:
00078             transaction.rollback()
00079             raise Exception, str(e)    
00080         
00081     def getCommentForId( self, tableName, entryid ):
00082         """get comment for given id in given table
00083         """
00084         transaction=self.__session.transaction()
00085         comment=''
00086         try:
00087             transaction.start(True)
00088             schema = self.__session.nominalSchema()
00089             query = schema.tableHandle(CommonUtils.commentTableName()).newQuery()
00090             condition='entryid = :entryid AND tablename = :tablename'
00091             conditionbindDict=coral.AttributeList()
00092             conditionbindDict.extend('entryid','unsigned long')
00093             conditionbindDict.extend('tablename','string')
00094             conditionbindDict['entryid'].setData(entryid)
00095             conditionbindDict['tablename'].setData(tableName)
00096             query.addToOutputList('comment')
00097             query.setCondition(condition,conditionbindDict)
00098             cursor=query.execute()
00099             if cursor.next():
00100                 comment=cursor.currentRow()['comment'].data()
00101                 cursor.close()
00102             transaction.commit()
00103             del query
00104             return comment
00105         except Exception, e:
00106             transaction.rollback()
00107             raise Exception, str(e)
00108     def getCommentsForTable( self, tableName ):
00109         """get all comments for given table
00110         result=[(entryid,comment)]
00111         """
00112         transaction=self.__session.transaction()
00113         result=[]
00114         
00115         try:
00116             transaction.start(True)
00117             schema = self.__session.nominalSchema()
00118             query = schema.tableHandle(CommonUtils.commentTableName()).newQuery()
00119             condition='tablename = :tablename'
00120             conditionbindDict=coral.AttributeList()
00121             conditionbindDict.extend('tablename','string')
00122             conditionbindDict['tablename'].setData(tableName)
00123             query.addToOutputList('entryid')
00124             query.addToOutputList('comment')
00125             query.setCondition(condition,conditionbindDict)
00126             cursor=query.execute()
00127             while cursor.next():
00128                 comment=cursor.currentRow()['comment'].data()
00129                 entryid=cursor.currentRow()['entryid'].data()
00130                 result.append((entryid,comment))  
00131             cursor.close()
00132             transaction.commit()
00133             del query
00134             return result
00135         except Exception, e:
00136             transaction.rollback()
00137             raise Exception, str(e)
00138         
00139     def modifyCommentForId( self, tableName, entryid, newcomment ):
00140         """replace comment for given entry for given table
00141         """
00142         transaction=self.__session.transaction()
00143         try:
00144             transaction.start(False)
00145             editor = self.__session.nominalSchema().tableHandle(CommonUtils.commentTableName()).dataEditor()
00146             inputData = coral.AttributeList()
00147             inputData.extend('newcomment','string')
00148             inputData.extend('entryid','unsigned long')
00149             inputData.extend('tablename','string')
00150             inputData['newcomment'].setData(newcomment)
00151             inputData['entryid'].setData(entryid)
00152             inputData['tablename'].setData(tableName)
00153             editor.updateRows( "comment = :newcomment", "entryid = :entryid AND tablename = :tablename", inputData )
00154             transaction.commit()
00155         except Exception, e:
00156             transaction.rollback()
00157             raise Exception, str(e)
00158 
00159     def replaceId( self, tableName, oldentryid, newentryid ):
00160         """replace entryid in given table
00161         """
00162         transaction=self.__session.transaction()
00163         try:
00164             transaction.start(False)
00165             editor = self.__session.nominalSchema().tableHandle(CommonUtils.commentTableName()).dataEditor()
00166             inputData = coral.AttributeList()
00167             inputData.extend('newentryid','unsigned long')
00168             inputData.extend('oldentryid','unsigned long')
00169             inputData.extend('tablename','string')
00170             inputData['newentryid'].setData(newentryid)
00171             inputData['oldentryid'].setData(oldentryid)
00172             inputData['tablename'].setData(tableName)
00173             editor.updateRows( "entryid = :newentryid", "entryid = :oldentryid AND tablename = :tablename", inputData )
00174             transaction.commit()
00175         except Exception, e:
00176             transaction.rollback()
00177             raise Exception, str(e)
00178         
00179     def deleteCommentForId( self, tablename, entryid):
00180         """delete selected comment entry 
00181         """
00182         transaction=self.__session.transaction()
00183         try:
00184             transaction.start(False)
00185             dbop=DBImpl.DBImpl(self.__session.nominalSchema())
00186             condition='tablename = :tablename AND entryid = :entryid'
00187             conditionbindDict=coral.AttributeList()
00188             conditionbindDict.extend('tablename','string')
00189             conditionbindDict.extend('entryid','unsigned long')
00190             conditionbindDict['tablename'].setData(tablename)
00191             conditionbindDict['entryid'].setData(entryid)
00192             dbop.deleteRows(CommonUtils.commentTableName(),condition,conditionbindDict)
00193             transaction.commit()
00194         except Exception, e:
00195             transaction.rollback()
00196             raise Exception, str(e)
00197         
00198     def clearAllEntriesForTable( self, tablename ):
00199         """delete all entries related with given table
00200         """
00201         transaction=self.__session.transaction()
00202         try:
00203             transaction.start(False)
00204             dbop=DBImpl.DBImpl(self.__session.nominalSchema())
00205             condition='tablename = :tablename'
00206             conditionbindDict=coral.AttributeList()
00207             conditionbindDict.extend('tablename','string')
00208             conditionbindDict['tablename'].setData(tablename)
00209             dbop.deleteRows(CommonUtils.commentTableName(),condition,conditionbindDict)
00210             transaction.commit()
00211         except Exception, e:
00212             transaction.rollback()
00213             raise Exception, str(e)
00214         
00215     
00216 if __name__ == "__main__":
00217     svc = coral.ConnectionService()
00218     session = svc.connect( 'sqlite_file:testentryComment.db',
00219                            accessMode = coral.access_Update )
00220     try:
00221         entrycomment=entryComment(session)
00222         print "test create entrycomment table"
00223         entrycomment.createEntryCommentTable()
00224         print "test insert one comment"
00225         entrycomment.insertComment(CommonUtils.inventoryTableName(),12,'comment1')
00226         entrycomment.insertComment(CommonUtils.treeTableName('ABCTREE'),12,'comment1')
00227         print "test bulk insert"
00228         bulkinput=[]
00229         bulkinput.append({'entryid':21,'tablename':CommonUtils.inventoryTableName(),'comment':'mycomment'})
00230         bulkinput.append({'entryid':22,'tablename':CommonUtils.inventoryTableName(),'comment':'mycomment2'})
00231         bulkinput.append({'entryid':23,'tablename':CommonUtils.inventoryTableName(),'comment':'mycomment3'})
00232         bulkinput.append({'entryid':24,'tablename':CommonUtils.inventoryTableName(),'comment':'mycomment4'})
00233         entrycomment.bulkinsertComments(CommonUtils.inventoryTableName(),bulkinput)
00234         print "test getCommentsForTable ",CommonUtils.inventoryTableName()
00235         results=entrycomment.getCommentsForTable(CommonUtils.inventoryTableName())
00236         print results
00237         result=entrycomment.getCommentForId(CommonUtils.inventoryTableName(),23)
00238         print result
00239         entrycomment.modifyCommentForId(CommonUtils.inventoryTableName(),23, 'mynewcomment' )
00240         print entrycomment.getCommentForId(CommonUtils.inventoryTableName(),23)
00241         print 'test replaceid'
00242         entrycomment.replaceId(CommonUtils.inventoryTableName(),23,33 )
00243         print entrycomment.getCommentForId(CommonUtils.inventoryTableName(),33)
00244         print 'test deletecomment for id'
00245         entrycomment.deleteCommentForId(CommonUtils.inventoryTableName(), 24)
00246         print entrycomment.getCommentsForTable(CommonUtils.inventoryTableName())
00247         print 'clearAllEntriesForTable'
00248         entrycomment.clearAllEntriesForTable(CommonUtils.inventoryTableName())
00249         print entrycomment.getCommentsForTable(CommonUtils.inventoryTableName())
00250         del session
00251     except Exception, e:
00252         print "Failed in unit test"
00253         print str(e)
00254         del session
00255