Thread: [SQL-CVS] [ sqlobject-Bugs-1432190 ] Table creation order for foreign key constraints
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: SourceForge.net <no...@so...> - 2006-09-09 18:02:34
|
Bugs item #1432190, was opened at 2006-02-15 09:22 Message generated for change (Comment added) made by abadger1999 You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None Status: Open Resolution: None Priority: 5 Submitted By: BjLindqvist (sonderblade) Assigned to: Oleg Broytmann (phd) Summary: Table creation order for foreign key constraints Initial Comment: Note: I had to apply my patch in https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 otherwise the foreign key constrains would be completely ignored. I have a simple model.py file containing my two tables like this: class Network(SQLObject): name = StringCol(alternateID = True, length = 80) class Channel(SQLObject): network = ForeignKey("Network", notNull = True) name = StringCol() Using "tg-admin sql sql" produces a listing like this: CREATE TABLE channel ( id INT PRIMARY KEY AUTO_INCREMENT, network_id INT NOT NULL, name TEXT ); CREATE TABLE network ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL UNIQUE ); ALTER TABLE channel ADD CONSTRAINT channel_network_id_exists FOREIGN KEY (network_id) REFERENCES network (id); Creating these two tables manually work: Network.createTable() Channel.createTable() But when I try to create them using "tg-admin sql create" (which just wraps "sqlobject-admin create") I get the following exception: Traceback (most recent call last): File "/usr/bin/tg-admin", line 7, in ? sys.exit( File "/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py", line 269, in main command.run() File "/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py", line 129, in run command.the_runner.run(sys.argv) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 100, in run runner.run() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 231, in run self.command() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 583, in command tableConstraints = soClass.createTable() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/main.py", line 1372, in createTable conn.query(constraint) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 314, in query return self._runWithConnection(self._query, s) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 228, in _runWithConnection val = meth(conn, *args) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 311, in _query self._executeRetry(conn, conn.cursor(), s) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/mysql/mysqlconnection.py", line 66, in _executeRetry return cursor.execute(query) File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1005, "Can't create table './test/#sql-1cf0_b9.frm' (errno: 150)") The problem is because sqlobject creates the Channel table before the Network table and therefore the query (despite the order that "tg-admin sql sql" seem to suggest): ALTER TABLE channel ADD CONSTRAINT channel_network_id_exists FOREIGN KEY (network_id) REFERENCES network (id); is executed before the Network table has been created. I think the problem can be solved by making it so all constraint statements are executed after the create table statements are executed. ---------------------------------------------------------------------- Comment By: Toshio Kuratomi (abadger1999) Date: 2006-09-09 14:02 Message: Logged In: YES user_id=944205 This problem affects postgresql as well so I doubt that https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 will help it. Also, that patch looks like it is aimed at getting foreign keys to work in mysql (otherwise they're ignored.) There is a patch in the TurboGears trac that addresses this issue: Bug: http://trac.turbogears.org/turbogears/ticket/279 Patch: http://trac.turbogears.org/turbogears/attachment/ticket/279/command.patch The patch orders the table creations so that tables with foreign keys are created after the tables they reference. I've tested this patch successfully with a simple model from TurboGears which I'll attach. ---------------------------------------------------------------------- Comment By: Oleg Broytmann (phd) Date: 2006-05-23 09:22 Message: Logged In: YES user_id=4799 The patch https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 was applied. Does it help? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&group_id=74338 |
From: SourceForge.net <no...@so...> - 2006-09-09 18:06:23
|
Bugs item #1432190, was opened at 2006-02-15 09:22 Message generated for change (Comment added) made by abadger1999 You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None Status: Open Resolution: None Priority: 5 Submitted By: BjLindqvist (sonderblade) Assigned to: Oleg Broytmann (phd) Summary: Table creation order for foreign key constraints Initial Comment: Note: I had to apply my patch in https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 otherwise the foreign key constrains would be completely ignored. I have a simple model.py file containing my two tables like this: class Network(SQLObject): name = StringCol(alternateID = True, length = 80) class Channel(SQLObject): network = ForeignKey("Network", notNull = True) name = StringCol() Using "tg-admin sql sql" produces a listing like this: CREATE TABLE channel ( id INT PRIMARY KEY AUTO_INCREMENT, network_id INT NOT NULL, name TEXT ); CREATE TABLE network ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL UNIQUE ); ALTER TABLE channel ADD CONSTRAINT channel_network_id_exists FOREIGN KEY (network_id) REFERENCES network (id); Creating these two tables manually work: Network.createTable() Channel.createTable() But when I try to create them using "tg-admin sql create" (which just wraps "sqlobject-admin create") I get the following exception: Traceback (most recent call last): File "/usr/bin/tg-admin", line 7, in ? sys.exit( File "/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py", line 269, in main command.run() File "/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py", line 129, in run command.the_runner.run(sys.argv) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 100, in run runner.run() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 231, in run self.command() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 583, in command tableConstraints = soClass.createTable() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/main.py", line 1372, in createTable conn.query(constraint) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 314, in query return self._runWithConnection(self._query, s) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 228, in _runWithConnection val = meth(conn, *args) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 311, in _query self._executeRetry(conn, conn.cursor(), s) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/mysql/mysqlconnection.py", line 66, in _executeRetry return cursor.execute(query) File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1005, "Can't create table './test/#sql-1cf0_b9.frm' (errno: 150)") The problem is because sqlobject creates the Channel table before the Network table and therefore the query (despite the order that "tg-admin sql sql" seem to suggest): ALTER TABLE channel ADD CONSTRAINT channel_network_id_exists FOREIGN KEY (network_id) REFERENCES network (id); is executed before the Network table has been created. I think the problem can be solved by making it so all constraint statements are executed after the create table statements are executed. ---------------------------------------------------------------------- Comment By: Toshio Kuratomi (abadger1999) Date: 2006-09-09 14:06 Message: Logged In: YES user_id=944205 Or perhaps I'll paste it inline... There doesn't seem to be a way for a mere commenter to attach a file. Also note: The turbogears bug suggests that the patch will also fix the problem with dropping tables reported in this SQLObject Bug: http://sourceforge.net/tracker/index.php?func=detail&aid=1495030&group_id=74338&atid=540672 ---BEGIN TEST CASE--- from sqlobject import * from turbogears.database import PackageHub hub = PackageHub('packagedb') __connection__ = hub class Collection(SQLObject): name = StringCol(length=128, notNone=True) class Branch(SQLObject): collection = ForeignKey('Collection') ---END TEST CASE--- ---------------------------------------------------------------------- Comment By: Toshio Kuratomi (abadger1999) Date: 2006-09-09 14:02 Message: Logged In: YES user_id=944205 This problem affects postgresql as well so I doubt that https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 will help it. Also, that patch looks like it is aimed at getting foreign keys to work in mysql (otherwise they're ignored.) There is a patch in the TurboGears trac that addresses this issue: Bug: http://trac.turbogears.org/turbogears/ticket/279 Patch: http://trac.turbogears.org/turbogears/attachment/ticket/279/command.patch The patch orders the table creations so that tables with foreign keys are created after the tables they reference. I've tested this patch successfully with a simple model from TurboGears which I'll attach. ---------------------------------------------------------------------- Comment By: Oleg Broytmann (phd) Date: 2006-05-23 09:22 Message: Logged In: YES user_id=4799 The patch https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 was applied. Does it help? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&group_id=74338 |
From: SourceForge.net <no...@so...> - 2006-11-13 16:42:08
|
Bugs item #1432190, was opened at 2006-02-15 17:22 Message generated for change (Settings changed) made by phd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&group_id=74338 Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: None Group: None >Status: Closed >Resolution: Fixed Priority: 5 Private: No Submitted By: BjLindqvist (sonderblade) Assigned to: Oleg Broytmann (phd) Summary: Table creation order for foreign key constraints Initial Comment: Note: I had to apply my patch in https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 otherwise the foreign key constrains would be completely ignored. I have a simple model.py file containing my two tables like this: class Network(SQLObject): name = StringCol(alternateID = True, length = 80) class Channel(SQLObject): network = ForeignKey("Network", notNull = True) name = StringCol() Using "tg-admin sql sql" produces a listing like this: CREATE TABLE channel ( id INT PRIMARY KEY AUTO_INCREMENT, network_id INT NOT NULL, name TEXT ); CREATE TABLE network ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(80) NOT NULL UNIQUE ); ALTER TABLE channel ADD CONSTRAINT channel_network_id_exists FOREIGN KEY (network_id) REFERENCES network (id); Creating these two tables manually work: Network.createTable() Channel.createTable() But when I try to create them using "tg-admin sql create" (which just wraps "sqlobject-admin create") I get the following exception: Traceback (most recent call last): File "/usr/bin/tg-admin", line 7, in ? sys.exit( File "/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py", line 269, in main command.run() File "/usr/lib/python2.4/site-packages/TurboGears-0.9a0dev_r778-py2.4.egg/turbogears/command/base.py", line 129, in run command.the_runner.run(sys.argv) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 100, in run runner.run() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 231, in run self.command() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/manager/command.py", line 583, in command tableConstraints = soClass.createTable() File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/main.py", line 1372, in createTable conn.query(constraint) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 314, in query return self._runWithConnection(self._query, s) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 228, in _runWithConnection val = meth(conn, *args) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/dbconnection.py", line 311, in _query self._executeRetry(conn, conn.cursor(), s) File "/usr/lib/python2.4/site-packages/SQLObject-0.8dev_r1596-py2.4.egg/sqlobject/mysql/mysqlconnection.py", line 66, in _executeRetry return cursor.execute(query) File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line 137, in execute self.errorhandler(self, exc, value) File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line 33, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1005, "Can't create table './test/#sql-1cf0_b9.frm' (errno: 150)") The problem is because sqlobject creates the Channel table before the Network table and therefore the query (despite the order that "tg-admin sql sql" seem to suggest): ALTER TABLE channel ADD CONSTRAINT channel_network_id_exists FOREIGN KEY (network_id) REFERENCES network (id); is executed before the Network table has been created. I think the problem can be solved by making it so all constraint statements are executed after the create table statements are executed. ---------------------------------------------------------------------- Comment By: Toshio Kuratomi (abadger1999) Date: 2006-09-09 22:06 Message: Logged In: YES user_id=944205 Or perhaps I'll paste it inline... There doesn't seem to be a way for a mere commenter to attach a file. Also note: The turbogears bug suggests that the patch will also fix the problem with dropping tables reported in this SQLObject Bug: http://sourceforge.net/tracker/index.php?func=detail&aid=1495030&group_id=74338&atid=540672 ---BEGIN TEST CASE--- from sqlobject import * from turbogears.database import PackageHub hub = PackageHub('packagedb') __connection__ = hub class Collection(SQLObject): name = StringCol(length=128, notNone=True) class Branch(SQLObject): collection = ForeignKey('Collection') ---END TEST CASE--- ---------------------------------------------------------------------- Comment By: Toshio Kuratomi (abadger1999) Date: 2006-09-09 22:02 Message: Logged In: YES user_id=944205 This problem affects postgresql as well so I doubt that https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 will help it. Also, that patch looks like it is aimed at getting foreign keys to work in mysql (otherwise they're ignored.) There is a patch in the TurboGears trac that addresses this issue: Bug: http://trac.turbogears.org/turbogears/ticket/279 Patch: http://trac.turbogears.org/turbogears/attachment/ticket/279/command.patch The patch orders the table creations so that tables with foreign keys are created after the tables they reference. I've tested this patch successfully with a simple model from TurboGears which I'll attach. ---------------------------------------------------------------------- Comment By: Oleg Broytmann (phd) Date: 2006-05-23 17:22 Message: Logged In: YES user_id=4799 The patch https://sourceforge.net/tracker/index.php?func=detail&aid=1432176&group_id=74338&atid=540674 was applied. Does it help? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=540672&aid=1432190&group_id=74338 |