Thread: [SQLObject] Inheritance failing to clean up?
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Dan W. <da...@wi...> - 2006-03-29 20:43:29
|
Howdy all, We're using InheritableSQLObject in our project and we're seeing an issue when an integrity error comes up. Basically if someone trys to insert a duplicate that already exists in the DB then then things correctly error out and we can catch the error. What we're seeing is if only one column is duplicated in a child class (assuming uniqueness) then some remnants of the insert actions are left behind in the superclass tables. Let me show you. We have the following: #============================================================== from sqlobject import * from sqlobject.inheritance import InheritableSQLObject connection = connectionForURI('mysql://root@magellan/sqlobject') sqlhub.processConnection = connection class Master(InheritableSQLObject): handle = StringCol(length=45, alternateID=True) class FirstChild(Master): name = StringCol(length=45, alternateID=True) class SecondChild(FirstChild): _inheritable = False title = StringCol(length=45, alternateID=True) if __name__ == '__main__': projectA = SecondChild(title="Number One", name="No. One", handle="number_one") projectB = SecondChild(title="Number Two", name="No. Two", handle="number_two") try: projectC = SecondChild(title="Number Three", name="No. Three", handle="number_two") except Exception, e: print e pass try: projectD = SecondChild(title="Number Four", name="No. One", handle="number_four") except Exception, e: print e pass try: projectE = SecondChild(title="Number One", name="No. Five", handle="number_five") except Exception, e: print e pass try: projectF = SecondChild(title="Number Five", name="No. Five", handle="number_five") except Exception, e: print e pass #============================================================== After we create the tables and run this we get the following output: underdog: python2.4 ./trash.py (1062, "Duplicate entry 'number_two' for key 2") (1062, "Duplicate entry 'No. One' for key 2") (1062, "Duplicate entry 'Number One' for key 2") (1062, "Duplicate entry 'number_five' for key 2") I would expect the first three error messages but not the fourth one. In our database we have: mysql> select * from master; +----+------------+-------------+ | id | child_name | handle | +----+------------+-------------+ | 1 | FirstChild | number_one | | 2 | FirstChild | number_two | | 3 | FirstChild | number_four | | 4 | FirstChild | number_five | +----+------------+-------------+ mysql> select * from first_child; +----+-------------+----------+ | id | child_name | name | +----+-------------+----------+ | 1 | SecondChild | No. One | | 2 | SecondChild | No. Two | | 4 | SecondChild | No. Five | +----+-------------+----------+ mysql> select * from second_child; +----+------------+ | id | title | +----+------------+ | 1 | Number One | | 2 | Number Two | +----+------------+ So what is happening is that things are trying to insert, failing, and not cleaning up in tables that are inherited from by our bottom child class, SecondChild. Now, I would expect transactions to do something about that, but that didn't seem to be working when I added it in. Is there something I'm missing or does inheritance just not work this way? Dan -- Software Developer Wild Brain, Inc. http://www.wildbrain.com/ |
From: Oleg B. <ph...@ma...> - 2006-03-29 21:06:16
|
On Wed, Mar 29, 2006 at 12:42:57PM -0800, Dan Weeks wrote: > Now, I would expect transactions to do something about that, but that didn't > seem to be working when I added it in. Can you show a test program with transaction? Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Dan W. <da...@wi...> - 2006-03-29 21:28:37
|
On 2006-03-30 01:05:58 +0400, Oleg Broytmann wrote: > On Wed, Mar 29, 2006 at 12:42:57PM -0800, Dan Weeks wrote: > > Now, I would expect transactions to do something about that, but that didn't > > seem to be working when I added it in. > > Can you show a test program with transaction? Sorry, I should have put that in the first message. If I'm not doing transactions correctly then I'll gladly take pointers on that as well. This produces the same results as the previous message. #==================================================================== from sqlobject import * from sqlobject.inheritance import InheritableSQLObject connection = connectionForURI('mysql://root@magellan/sqlobject') sqlhub.processConnection = connection class Master(InheritableSQLObject): handle = StringCol(length=45, alternateID=True) class FirstChild(Master): name = StringCol(length=45, alternateID=True) class SecondChild(FirstChild): _inheritable = False title = StringCol(length=45, alternateID=True) if __name__ == '__main__': trans = connection.transaction() projectA = SecondChild(title="Number One", name="No. One", handle="number_one") projectB = SecondChild(title="Number Two", name="No. Two", handle="number_two") trans.commit() try: projectC = SecondChild(title="Number Three", name="No. Three", handle="number_two") trans.commit() except Exception, e: trans.rollback() trans.begin() print e pass try: projectD = SecondChild(title="Number Four", name="No. One", handle="number_four") trans.commit() except Exception, e: trans.rollback() trans.begin() print e pass try: projectE = SecondChild(title="Number One", name="No. Five", handle="number_five") trans.commit() except Exception, e: trans.rollback() trans.begin() print e pass try: projectF = SecondChild(title="Number Five", name="No. Five", handle="number_five") trans.commit() except Exception, e: trans.rollback() trans.begin() print e pass #==================================================================== Dan -- Software Developer Wild Brain, Inc. http://www.wildbrain.com/ |
From: Oleg B. <ph...@ph...> - 2006-03-29 21:32:10
|
On Wed, Mar 29, 2006 at 01:25:27PM -0800, Dan Weeks wrote: > trans = connection.transaction() > projectA = SecondChild(title="Number One", name="No. One", > handle="number_one") > projectB = SecondChild(title="Number Two", name="No. Two", > handle="number_two") > trans.commit() It is not enough simply to create transaction. Transactions in SQLObject are really a kind of connection; you either globally replace all connections with the transaction or pass the "trans" as the connection to all operations. Either 1) trans = connection.transaction() sqlhub.processConnection = trans or 2) trans = connection.transaction() projectA = SecondChild(title="Number One", name="No. One", handle="number_one", connection=trans) projectB = SecondChild(title="Number Two", name="No. Two", handle="number_two", connection=trans) trans.commit() Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Oleg B. <ph...@ph...> - 2006-03-29 21:34:11
|
On Thu, Mar 30, 2006 at 01:32:00AM +0400, Oleg Broytmann wrote: > Either > > 1) > trans = connection.transaction() > sqlhub.processConnection = trans > > or > > 2) > trans = connection.transaction() > projectA = SecondChild(title="Number One", name="No. One", > handle="number_one", connection=trans) > projectB = SecondChild(title="Number Two", name="No. Two", > handle="number_two", connection=trans) > trans.commit() or 3) trans = connection.transaction() class SecondChild(InheritableSQLObject): _connection = trans Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Dan W. <da...@wi...> - 2006-03-29 21:38:33
|
On 2006-03-30 01:32:00 +0400, Oleg Broytmann wrote: > It is not enough simply to create transaction. Transactions in SQLObject > are really a kind of connection; you either globally replace all > connections with the transaction or pass the "trans" as the connection to > all operations. > > Either > > 1) > trans = connection.transaction() > sqlhub.processConnection = trans > Ah, that makes much more sense now. So, in doing that I get a NotSuportedError from mysql: underdog: python2.4 ./trash.py (1062, "Duplicate entry 'number_two' for key 2") Traceback (most recent call last): File "./trash.py", line 40, in ? trans.rollback() File "/usr/local/lib/python2.4/site-packages/SQLObject-0.7.1dev_r1588-py2.4.egg/sqlobject/dbconnection.py", line 801, in rollback self._connection.rollback() _mysql_exceptions.NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back") So, I'm guessing what I want to do, on failure make sure there's no garbage left in the tables, isn't going to work. Thanks for your help Oleg. Dan -- Software Developer Wild Brain, Inc. http://www.wildbrain.com/ |
From: Oleg B. <ph...@ma...> - 2006-03-29 21:51:30
|
On Wed, Mar 29, 2006 at 01:38:26PM -0800, Dan Weeks wrote: > _mysql_exceptions.NotSupportedError: (1196, "Warning: Some non-transactional changed tables couldn't be rolled back") I love MySQL! :( > So, I'm guessing what I want to do, on failure make sure there's no garbage > left in the tables, isn't going to work. Change DB engines to transactional (BDB or InnoDB). Oleg. -- Oleg Broytmann http://phd.pp.ru/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |