sqlobject-discuss

[SQLObject] foreign keys

[Timo S. <tw...@gm...>]

Hi,

I studied the available documentation, but I did not find a hint
if it is possible to add a foreign key, *after* a table (in the
database) was created.

In short, I need something like that:
"ALTER TABLE foo ADD FOREIGN KEY (dest_id) REFERENCES dest_type"

Is this possible with SQLObject?

I would be glad for any comments (and I hope that I did not
miss something obvious in the documentation files).


Thanks,
Timo

Re: [SQLObject] How to add a foreign key at runtime

[Oleg B. <ph...@ph...>]

On Mon, Jan 12, 2009 at 05:19:20PM +0100, Timo Schulz wrote:
> I studied the available documentation, but I did not find a hint
> if it is possible to add a foreign key, *after* a table (in the
> database) was created.
> 
> In short, I need something like that:
> "ALTER TABLE foo ADD FOREIGN KEY (dest_id) REFERENCES dest_type"
> 
> Is this possible with SQLObject?

   Foo.sqlmeta.addColumn(
      ForeignKey("DestType", name='dest', default=None),
   changeSchema=True)

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] How to add a foreign key at runtime

[Davide A. <dav...@gm...>]

On Mon, Jan 12, 2009 at 6:15 PM, Oleg Broytmann <ph...@ph...> wrote:

> > In short, I need something like that:
> > "ALTER TABLE foo ADD FOREIGN KEY (dest_id) REFERENCES dest_type"
> >
> > Is this possible with SQLObject?
>
>   Foo.sqlmeta.addColumn(
>      ForeignKey("DestType", name='dest', default=None),
>   changeSchema=True)
>

Hi,
this is not exactly what we were trying to do, since we already
have a column that must be "made" foreign key.

A simple example:
=============================================================
from sqlobject import *

URI = 'mysql://USER:PWD@localhost/DB'

class InfoType(SQLObject):
    kind = UnicodeCol(notNone=True)

class MyInfo(SQLObject):
    # For a series of reasons we don't want to specify 'kind' as ForeignKey,
    # at creation time.
    kindID = IntCol(notNone=True)
    info = UnicodeCol(notNone=True)

# Connect.
conn = connectionForURI(URI, debug=True)
InfoType.setConnection(conn)
MyInfo.setConnection(conn)

# Drop/create tables.
InfoType.dropTable(ifExists=True)
MyInfo.dropTable(ifExists=True)
InfoType.createTable(ifNotExists=True)
MyInfo.createTable(ifNotExists=True)

# The foreign key, pointing to InfoType.id.
fk = ForeignKey('InfoType', name='kind', default=None)
MyInfo.sqlmeta.addColumn(fk, changeSchema=True)
=============================================================

The above code raises an AssertionError:
  AssertionError: The class __main__.MyInfo already has a column 'kindID'
(<IntCol 895364c kindID>), you cannot add the column <ForeignKey 89b588c
kind>

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;

Notice that the my_info table already exists and is full of data, so
we can't drop/recreate it.

Is there any way to do this, using SQLObject?


Thanks!
-- 
Davide Alberani <dav...@gm...>  [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/

Re: [SQLObject] How to add a foreign key at runtime

[Oleg B. <ph...@ph...>]

On Tue, Jan 27, 2009 at 02:12:15PM +0100, Davide Alberani 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')

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.

Re: [SQLObject] How to add a foreign key at runtime

[Davide A. <dav...@gm...>]

On Jan 27, Oleg Broytmann <ph...@ph...> 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 <dav...@gm...> [PGP KeyID: 0x465BFD47]
http://erlug.linux.it/~da/