On Jan 27, Oleg Broytmann <[email protected]> wrote:
> > What we need is a way to execute a SQL statement like (for MySQL):
> > ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES info_type;
>
> The question was 'how to add', not 'how to change'. SQLObject
> cannot change column types, but you can just execute the query:
>
> connection.query('ALTER TABLE my_info ADD FOREIGN KEY (kind_id) REFERENCES
> info_type')
I see; anyway, I've used a horrible workaround to build the needed
query using SQLObject.
I'll describe it here, in case someone else has a similar need (disclaimer:
it's well beyond utterly insanety ;-)
This is the metacode, to handle a list of tables where some columns must
be "converted" to foreign keys:
for origTable in tablesThatNeedFK:
# Name of the "fake" table we temporary need.
fakeTableName = 'myfaketable%s' % origTable.sqlmeta.table
attrs = {}
for col in columnsOforigTable:
if thisIsNOTaColToMakeFK:
# XXX: maybe it can work even _copying_ the column - not tested.
attrs[colName] = createASQLObjectColWithTheSameParameters()
continue
fk = ForeignKey(foreignTableName, name=colName, default=None)
attrs[colName] = fk
# Create a new sqlobject.SQLObject subclass, identical to the original
# one, but with a different name and with some columns replaced with FK.
newcls = type(fakeTableName, (SQLObject,), attrs)
# Connect it.
newcls.setConnection(origTable._connection)
for fkCol in columnsThatMustBeMadeFK:
# Get the SQL that _WOULD BE_ run, if we had to create
# this "fake" table, using the createReferenceConstraint method.
fkQuery = newcls._connection.createReferenceConstraint(newcls,
newcls.sqlmeta.columns[fkCol])
# Remove "myfaketable" to get references to _real_ tables.
fkQuery = fkQuery.replace('myfaketable', '')
# Execute the query.
origTable._connection.query(fkQuery)
# Disconnect. Maybe it's possible/wise to remove the "fake" class
# from the registry. Another solution is to store the built "fake"
# classes in a global proxy somewhere, so that they can be retrieved
# from there, if the precedure is run again.
newcls._connection.close()
As said, pure craziness... but it works. :-)
--
Davide Alberani <[email protected]> [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/
------------------------------------------------------------------------------
This SF.net email is sponsored by:
SourcForge Community
SourceForge wants to tell your story.
http://p.sf.net/sfu/sf-spreadtheword
_______________________________________________
sqlobject-discuss mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
