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