I've patched SQLObject to do late reference on creation of tables.
It is no 100%, but it is a starting point.
I've attached the patch.
--
JP
On 10/1/05, Ian Bicking <[EMAIL PROTECTED]> wrote:
João Paulo Fernandes Farias wrote:
> Hi!
>
> How do I create tables with cyclic references?
>
> As an example, suppose we have two classes:
>
> class A(SQLObject):
> b = ForeignKey('B')
>
> class B(SQLObject):
> a = ForeignKey('A')
>
>
> /me using turbogears...
>
> I issue a: turbogears-admin.py sql create
>
> It gives me some erros because it tries to create table A but B does not
> exists, so it cannot be referenced.
>
> My suggestion is: Create all tables and then issue all the "alter table
> add constraint" as necessary.
This is kind of hard. Technically you can do it like:
class A(SQLObject):
b = ForeignKey('B')
class B(SQLObject):
pass
def create():
B.createTable()
A.createTable()
B.sqlmeta.addColumn(ForeignKey(name='a', foreignKey='A'),
changeSchema=True)
However, this won't work with sqlobject-admin (and thus
turbogears-admin.py).
Index: sqlobject/dbconnection.py
===================================================================
--- sqlobject/dbconnection.py (revisão 1061)
+++ sqlobject/dbconnection.py (cópia de trabalho)
@@ -513,11 +513,25 @@
assert 0, 'Implement in subclasses'
def createTable(self, soClass):
- self.query(self.createTableSQL(soClass))
+ createSql, constraints = self.createTableSQL(soClass)
+ self.query(createSql)
+ return constraints
+
+ def createReferenceConstraints(self, soClass):
+ refConstraints = [self.createReferenceConstraint(soClass, column) \
+ for column in soClass.sqlmeta.columnList \
+ if isinstance(column, col.SOForeignKey)]
+ refConstraintDefs = [constraint \
+ for constraint in refConstraints \
+ if constraint]
+ return refConstraintDefs
+
def createTableSQL(self, soClass):
- return ('CREATE TABLE %s (\n%s\n)' %
+ constraints = self.createReferenceConstraints(soClass)
+ createSql = ('CREATE TABLE %s (\n%s\n)' %
(soClass.sqlmeta.table, self.createColumns(soClass)))
+ return createSql, constraints
def createColumns(self, soClass):
columnDefs = [self.createIDColumn(soClass)] \
@@ -525,6 +539,9 @@
for col in soClass.sqlmeta.columnList]
return ",\n".join([" %s" % c for c in columnDefs])
+ def createReferenceConstraint(self, soClass, col):
+ assert 0, "Implement in subclasses"
+
def createColumn(self, soClass, col):
assert 0, "Implement in subclasses"
Index: sqlobject/col.py
===================================================================
--- sqlobject/col.py (revisão 1061)
+++ sqlobject/col.py (cópia de trabalho)
@@ -707,6 +707,10 @@
def postgresCreateSQL(self):
sql = SOKeyCol.postgresCreateSQL(self)
+ return sql
+
+ def postgresCreateReferenceConstraint(self):
+ sTName = self.soClass.sqlmeta.table
other = findClass(self.foreignKey, self.soClass.sqlmeta.registry)
tName = other.sqlmeta.table
idName = other.sqlmeta.idName
@@ -719,16 +723,16 @@
action = 'ON DELETE RESTRICT'
else:
action = ''
- constraint = ('CONSTRAINT %(colName)s_exists '
+ constraint = ('ALTER TABLE %(sTName)s ADD CONSTRAINT %(colName)s_exists '
'FOREIGN KEY (%(colName)s) '
'REFERENCES %(tName)s (%(idName)s) '
'%(action)s' %
{'tName': tName,
'colName': self.dbName,
'idName': idName,
- 'action': action})
- sql = ', '.join([sql, constraint])
- return sql
+ 'action': action,
+ 'sTName': sTName})
+ return constraint
def sybaseCreateSQL(self):
sql = SOKeyCol.sybaseCreateSQL(self)
Index: sqlobject/postgres/pgconnection.py
===================================================================
--- sqlobject/postgres/pgconnection.py (revisão 1061)
+++ sqlobject/postgres/pgconnection.py (cópia de trabalho)
@@ -138,6 +138,9 @@
def createColumn(self, soClass, col):
return col.postgresCreateSQL()
+ def createReferenceConstraint(self, soClass, col):
+ return col.postgresCreateReferenceConstraint()
+
def createIndexSQL(self, soClass, index):
return index.postgresCreateIndexSQL(soClass)
Index: sqlobject/manager/command.py
===================================================================
--- sqlobject/manager/command.py (revisão 1061)
+++ sqlobject/manager/command.py (cópia de trabalho)
@@ -502,11 +502,20 @@
def command(self):
classes = self.classes()
+ allConstraints = []
for cls in classes:
if self.options.verbose >= 1:
print '-- %s from %s' % (
cls.__name__, cls.__module__)
- print cls.createTableSQL().strip() + ';\n'
+ createSql, constraints = cls.createTableSQL()
+ print createSql.strip() + ';\n'
+ allConstraints.append(constraints)
+ for constraints in allConstraints:
+ if constraints:
+ for constraint in constraints:
+ if constraint:
+ print constraint.strip() + ';\n'
+
class CommandList(Command):
@@ -540,6 +549,7 @@
created = 0
existing = 0
dbs_created = []
+ constraints = {}
for soClass in self.classes(require_some=True):
if (self.options.create_db
and soClass._connection not in dbs_created):
@@ -548,6 +558,8 @@
else:
print '(simulating; cannot create database)'
dbs_created.append(soClass._connection)
+ if soClass._connection not in constraints.keys():
+ constraints[soClass._connection] = []
exists = soClass._connection.tableExists(soClass.sqlmeta.table)
if v >= 1:
if exists:
@@ -562,12 +574,22 @@
if self.options.interactive:
if self.ask('Create %s' % soClass.__name__):
created += 1
- soClass.createTable()
+ tableConstraints = soClass.createTable()
+ if tableConstraints:
+ constraints[soClass._connection].append(tableConstraints)
+
else:
print 'Cancelled'
else:
created += 1
- soClass.createTable()
+ tableConstraints = soClass.createTable()
+ if tableConstraints:
+ constraints[soClass._connection].append(tableConstraints)
+ for connection in constraints.keys():
+ for constraintList in constraints[connection]:
+ for constraint in constraintList:
+ if constraint:
+ connection.query(constraint)
if v >= 1:
print '%i tables created (%i already exist)' % (
created, existing)
Index: sqlobject/main.py
===================================================================
--- sqlobject/main.py (revisão 1061)
+++ sqlobject/main.py (cópia de trabalho)
@@ -1305,24 +1305,25 @@
conn = connection or cls._connection
if ifNotExists and conn.tableExists(cls.sqlmeta.table):
return
- conn.createTable(cls)
+ constraints = conn.createTable(cls)
if createJoinTables:
cls.createJoinTables(ifNotExists=ifNotExists,
connection=conn)
if createIndexes:
cls.createIndexes(ifNotExists=ifNotExists,
connection=conn)
+ return constraints
createTable = classmethod(createTable)
def createTableSQL(cls, createJoinTables=True, connection=None,
createIndexes=True):
conn = connection or cls._connection
- sql = conn.createTableSQL(cls)
+ sql, constraints = conn.createTableSQL(cls)
if createJoinTables:
sql += '\n' + cls.createJoinTablesSQL(connection=conn)
if createIndexes:
sql += '\n' + cls.createIndexesSQL(connection=conn)
- return sql
+ return sql, constraints
createTableSQL = classmethod(createTableSQL)
def createJoinTables(cls, ifNotExists=False, connection=None):
