[Twisted-Python] adbapi and multiple queries in single transaction.
Hi, I am working on an application that uses twisted and twisted.e.adbapi with sqlite in the backend. All db queries are put in separate functions in class say tableGateway.py which uses runOperation, runQuery etc as appropriate. A layer that uses it, lets say objectGateway.py, defines operations. Thus, lets say an add operation may actually call 2-3 or more functions from tableGateway.py. If my application quits or crashes in middle of an operation then it is likely that db will be inconsistent. What I want is each objectGateway level operation should be done in a single transaction. I initially thought of putting all the queries per operation in a runInteraction. However this results in code-duplication as many queries are shared between different operations. What I could think of for preventing this is: 1. do a runInteraction at objectGateway and then txn that I get will be passed to appropriate functions of tableGateway. 2. An objectGateway level log based recovery. Option 1 requires all the tableGateway functions to take txn as param. This can be avoided by instantiating tableGateway with a txn for each operation, however tableGateway instantiation is not that cheap. Even so, if any objectGateway function calls other objectGateway function then the txn will have to be passed to that as well. Any comments/suggestions? Regards, vishal DISCLAIMER ========== This e-mail may contain privileged and confidential information which is the property of Persistent Systems Ltd. It is intended only for the use of the individual or entity to which it is addressed. If you are not the intended recipient, you are not authorized to read, retain, copy, print, distribute or use this message. If you have received this communication in error, please notify the sender and delete all copies of this message. Persistent Systems Ltd. does not accept any liability for virus infected mails.
On Jun 23, 2009, at 9:45 AM, Vishal Shetye wrote:
It seems like the easiest way to deal with this would be to make 'private' methods for all the standard queries; these methods/ functions would accept a transaction object like you said, but the methods themselves would only be called from an interaction, which can supply the transaction object. Then in each public ObjectGateway method you can just define an inner function to serve as the interaction, calling each private query method in turn, using the transaction object provided to that interaction. Here's a stupidly trivial example: class ObjectGateway(object): def __init__(self, pool): self.pool = pool def _getUserName(self, trans, user_id): result = trans.execute('SELECT username FROM user WHERE id = %s', user_id); return result[0][0] def _sessionUpdateQuery(self, trans, sid, username): trans.execute('UPDATE session SET username = %s WHERE sid = %s', [username, sid]); def handleLogin(self, sid, user_id): def _loginInteraction(trans): u = self._getUserName(trans, user_id) self._sessionUpdateQuery(trans, sid, username) return u return self.pool.runInteraction(_loginInteraction) Other than being careful not to mess around with the instance state during those interactions (they are running in a thread, after all), this should be pretty straightforward. Hope this helps, -phil
On Jun 23, 2009, at 9:45 AM, Vishal Shetye wrote:
It seems like the easiest way to deal with this would be to make 'private' methods for all the standard queries; these methods/ functions would accept a transaction object like you said, but the methods themselves would only be called from an interaction, which can supply the transaction object. Then in each public ObjectGateway method you can just define an inner function to serve as the interaction, calling each private query method in turn, using the transaction object provided to that interaction. Here's a stupidly trivial example: class ObjectGateway(object): def __init__(self, pool): self.pool = pool def _getUserName(self, trans, user_id): result = trans.execute('SELECT username FROM user WHERE id = %s', user_id); return result[0][0] def _sessionUpdateQuery(self, trans, sid, username): trans.execute('UPDATE session SET username = %s WHERE sid = %s', [username, sid]); def handleLogin(self, sid, user_id): def _loginInteraction(trans): u = self._getUserName(trans, user_id) self._sessionUpdateQuery(trans, sid, username) return u return self.pool.runInteraction(_loginInteraction) Other than being careful not to mess around with the instance state during those interactions (they are running in a thread, after all), this should be pretty straightforward. Hope this helps, -phil
participants (2)
-
Phil Christensen -
Vishal Shetye