If you AssUMe...

Sorry if this is a relatively short post, but it's a busy weekend and I'm kindof stuck here. I'll describe the situation -- maybe someone out there can advise?

Last week, we had written a unit test that failed:

  def testGetOrInsert(self):
    """tests if get_or_insert works correctly"""
    helpers.create_tables([TestModel()], self.connection)
    model1 = TestModel(number=1)
    model1.put()
    model2 = TestModel.get_or_insert('foo', number=13, text='t')
    self.assertEquals(13, model2.number)
    fetched = TestModel.get_by_key_name('foo')
    self.assertEquals(13, fetched)

The logs indicated that our lack of transacton support was to blame. This week, I wanted to go ahead and fix that!

My assumption was (spoiler alert: that was probably wrong!) that a sqlite cursor is the equivalent of a database transaction. So, I decided to store cursors of active transactions in a dictionary within my datastore implementation:

  def __init__(self, database_name, connection=None):
    """Constructor.

    Initializes and loads the datastore from the backing files, if they exist.

    Args:
      database_name: the name of the sqlite instance
      connection: a pre-initialized connection for unit tests, optional
    """
    #TODO: initialize sqlite instance
    if connection:
      self.connection = connection
    self.__next_tx_handle = 1
    self.__open_transactions = {}
    self.__tx_handle_lock = threading.Lock()
    
    
  def _get_cursor(self, transaction=None, may_update=False, delete=False):
    """Opens a new or returns an opened transaction (cursor).
    
    Args:
      transaction: an optional transaction protocol buffer. If not set,
        a new, temporary object is returned. If set to a transaction without
        a handle, a new object will be created. If set to a transaction, the
        appropriate element. If the handle has no handle, that means a new
        cursor will be created and the handle be set.
      may_update: if set to True (default is False), the newly created
        cursor object will be remembered as a new transaction and its handle
        updated in the transaction object
      delete: if set to true (default is False), delete the cursor from
        the internal data structure if it exists
        
    Returns:
      a cursor
    """
    if not transaction:
      return self.connection.cursor()
    self.__tx_handle_lock.acquire()
    try:
      if transaction.has_handle():
        handle = transaction.handle()
        if handle in self.__open_transactions:
          if delete:
            return self.__open_transactions.pop(handle)
          else:
            return self.__open_transactions.get(handle)
        else:
          raise apiproxy_errors.ApplicationError(
            datastore_pb.Error.BAD_REQUEST,
            'Transaction handle %d not found' % handle)
      else:
        if not may_update:
          return self.connection.cursor()
        handle = self.__next_tx_handle
        self.__next_tx_handle += 1
        transaction.set_handle(handle)
        cursor = self.connection.cursor()
        self.__open_transactions[handle] = cursor
    finally:
      self.__tx_handle_lock.release()

With this helper-method, creating a transaction or doing a commit or rollback seemed easy:

  def _Dynamic_BeginTransaction(self, request, transaction):
    self._get_cursor(transaction, True, False)


  def _Dynamic_Commit(self, transaction, transaction_response):
    cursor = self._get_cursor(transaction, False, True)
    cursor.commit()
    cursor.close()
    

  def _Dynamic_Rollback(self, transaction, transaction_response):
    cursor = self._get_cursor(transaction, False, True)
    cursor.rollback()
    cursor.close()

I also updated my get and put implementations to fetch the proper cursor and operate on it.

As so often, simply assuming does not cut it, though: I am getting an error

AttributeError: 'pysqlite2.dbapi2.Cursor' object 
  has no attribute 'commit'

Turns out that commit and roolback seem to be on connection level. Before I go ahead and refactor anything though, I was hoping that one of you could point me to good documentation that answers the following questions (or just answer them directly):

• How are parallel transactions expected to be handled? Open one connection object for each?


• If that is the case, how to open multiple connections on the same in-memory instance for unit tests


• What cleanup is required at the end of a transaction? Should I close the connection object? Do I need to close any cursors first, or are those automatically cleaned up?

Feedback is appreciated.