[SQLObject] InheritableSQLObject: Data corruption when child record create fails

[Tom C. <su...@ic...>]

Hi all,

I am doing some tests with InheritableSQLobject and have run into a problem 
which I hope to get some feedback on.

The problem is that when creating a new object based on an 
InheritableSQLObject class, and the creation of the child object fails after 
a new parent was created, the parent record will remain in the database 
without the relevant child.

Here is a short example:

~~~~~~~~~~ file: inheritTest3.py ~~~~~~~~~~~~
from sqlobject import *
from sqlobject.inheritance import InheritableSQLObject

__connection__ = "sqlite:/:memory:"

class Vehicle(InheritableSQLObject):
    vid = StringCol(notNone=True, default=None, unique=True)
    wheels = IntCol()

class Bicycle(Vehicle):
    seats = IntCol(notNone=True, default=None)

class Car(Vehicle):
    doors = IntCol(notNone=True, default=None)

def createTables():
    Vehicle.createTable()
    Bicycle.createTable()
    Car.createTable()
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Here is a sample session showing the problem when trying to create a bicycle 
but only supplying the args required for the Vehicle part of the object:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In [1]: from inheritTest3 import *

In [2]: createTables(True)

In [3]: b = Bicycle(vid="id1", wheels=4)
ERROR: An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line statement', (22, 0))

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)

/home/tomc/mobilereporter.repo/main/app/<ipython console> in <module>()

/usr/lib/python2.5/site-packages/sqlobject/main.pyc in __init__(self, **kw)
   1221                 id = None
   1222
-> 1223             self._create(id, **kw)
   1224

.... snipping lots of traceback parts ............

/usr/lib/python2.5/site-packages/sqlobject/sqlite/sqliteconnection.pyc in 
_executeRetry(self, conn, cursor, query)
    187                 raise DuplicateEntryError(msg)
    188             else:
--> 189                 raise IntegrityError(msg)
    190         except self.module.InternalError, e:
    191             raise InternalError(ErrorMessage(e))

IntegrityError: bicycle.seats may not be NULL
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you now supply all required args for the bicycle, using the same field 
values for the vid and wheels as before, a duplicate entry error is raised:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

In [4]: b = Bicycle(vid="id1", wheels=4, seats=2)
ERROR: An unexpected error occurred while tokenizing input
The following traceback may be corrupted or invalid
The error message is: ('EOF in multi-line statement', (22, 0))

---------------------------------------------------------------------------
DuplicateEntryError                       Traceback (most recent call last)

/home/tomc/mobilereporter.repo/main/app/<ipython console> in <module>()

/usr/lib/python2.5/site-packages/sqlobject/main.pyc in __init__(self, **kw)
   1221                 id = None
   1222
-> 1223             self._create(id, **kw)
   1224
   1225             for func in post_funcs:

.... snipping lots of traceback parts ............

/usr/lib/python2.5/site-packages/sqlobject/sqlite/sqliteconnection.pyc in 
_executeRetry(self, conn, cursor, query)
    185             msg = ErrorMessage(e)
    186             if msg.startswith('column') and msg.endswith('not 
unique'):
--> 187                 raise DuplicateEntryError(msg)
    188             else:
    189                 raise IntegrityError(msg)

DuplicateEntryError: column vid is not unique
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The duplicate entry is because of the parent vehicle record was created when 
the first bicycle creation failed: 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
In [5]: q = "SELECT * from vehicle"

In [6]: Vehicle._connection.queryAll(q)
Out[6]: [(1, 'id1', 4, 'Bicycle')]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

So, the problem is that if a new child object is created, but fails after the 
parent has been created, the parent is left in tact in the database.

I'm attaching a patch as a possible fix for this, but I am not sure under 
which other circumstances this patch will cause other problems. Maybe a flag 
is needed inside the block that creates the child (parentCreatedHere=True), 
and only if this flag is set, should the parent be deleted in exception if 
the child creation failed?

I would appreciate if someone with more insight into this can look at the 
patch an modify where neccessary.

BTW: This patch is against SQLObject 0.11.0.



Cheers,
 Tom