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.
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?
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.
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---