SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them

[Randy Syring]

I would like sqlalchemy to generate triggers on an SQLite database to
enforce foreign key relationships. The method is documented here:

http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers

and I have written a foreign key trigger generator here:

http://rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_trigger_generator

Although it does not recognize the kind of references sqlalchemy
generates in the CREATE TABLE statements.

Anyway, the point of this post is that I would like to know how I
should go about extending sqlalchemy so that when I use ForeignKey
constructs in the metadata, create statements on the tables would also
create the triggers and drop statements on the tables would drop the
said triggers to enforce the Foreign Key relationship.

Thanks.

[Michael Bayer]

you can build this functionality using the DDL() construct provided by
SQLAlchemy:

http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_DDL

the ForeignKey objects on the table can be pulled out using:

for c in table.c:
for fk in c.foreign_keys:
do_something_with_fk(fk)

[jason kirtland]

DDL() has some simple templating capabilities that can help out a bit
here, but I'd suggest taking the ForeignKey code Mike provided as a
start and putting together an after-create listener using
Table.append_ddl_listener directly:

http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_sqlalchemy.schema_Table

It would look something like:

def fks_for_sqlite(event, table, bind):

for c in table.c:
for fk in c.foreign_keys:

sql = your_code_to_make_trigger_for_fk(fk)
bind.execute(sql)

tbl.append_ddl_listener('after-create', fks_for_sqlite)

[Randy Syring]

Thank you, I will look into this.

On Aug 22, 12:44 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> you can build this functionality using the DDL() construct provided by  
> SQLAlchemy:
>

> http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_s...

>
> the ForeignKey objects on the table can be pulled out using:
>
> for c in table.c:
>         for fk in c.foreign_keys:
>                 do_something_with_fk(fk)
>
> On Aug 22, 2008, at 11:19 AM, Randy Syring wrote:
>
>
>
> > I would like sqlalchemy to generate triggers on an SQLite database to
> > enforce foreign key relationships.  The method is documented here:
>
> >http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
>
> > and I have written a foreign key trigger generator here:
>

> >http://rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_...

[Randy Syring]

Jason,

Thank you for the response. Using the method you suggest, am I
understanding correctly that fks_for_sqlite() would only be run when a
create() was processed for that table? Also, I am assuming I would
need to create a complimentary function for handling the 'after-drop'
event.

On Aug 22, 1:25 pm, jason kirtland <j...@discorporate.us> wrote:
> DDL() has some simple templating capabilities that can help out a bit
> here, but I'd suggest taking the ForeignKey code Mike provided as a
> start and putting together an after-create listener using
> Table.append_ddl_listener directly:
>

> http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_s...

>
> It would look something like:
>
>    def fks_for_sqlite(event, table, bind):
>        for c in table.c:
>            for fk in c.foreign_keys:
>               sql = your_code_to_make_trigger_for_fk(fk)
>               bind.execute(sql)
>
>    tbl.append_ddl_listener('after-create', fks_for_sqlite)
>
> Michael Bayer wrote:
> > you can build this functionality using the DDL() construct provided by  
> > SQLAlchemy:
>

> >http://www.sqlalchemy.org/docs/05/sqlalchemy_schema.html#docstrings_s...

>
> > the ForeignKey objects on the table can be pulled out using:
>
> > for c in table.c:
> >    for fk in c.foreign_keys:
> >            do_something_with_fk(fk)
>
> > On Aug 22, 2008, at 11:19 AM, Randy Syring wrote:
>
> >> I would like sqlalchemy to generate triggers on an SQLite database to
> >> enforce foreign key relationships.  The method is documented here:
>
> >>http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
>
> >> and I have written a foreign key trigger generator here:
>

> >>http://rcs-comp.com/site/index.php/view/Utilities-SQLite_foreign_key_...

[jason kirtland]

Yep, though possibly you'd want it on before-drop. You can actually
handle both tasks in the same function if you like- the event name will
be passed in as the first argument.

[Randy Syring]

Ok, so I was going to try and implement a solution using the method
discussed here, but ran into a problem b/c I am using Elixir objects
and not declaring the tables directly. Can I still use this method?
How do I get the table references from the Elixir objects?

Thanks.

[Randy Syring]

Here is my initial stab at this:

http://code.google.com/p/sqlitefktg4sa/

Code plus tests.

I welcome your feedback.

[Gaetan de Menten]

On Wed, Aug 27, 2008 at 3:21 AM, Randy Syring <rsy...@gmail.com> wrote:
>
> Ok, so I was going to try and implement a solution using the method
> discussed here, but ran into a problem b/c I am using Elixir objects
> and not declaring the tables directly. Can I still use this method?
> How do I get the table references from the Elixir objects?

YourEntity.table

After running setup_all(), and obviously before running create_all()
or similar (metadata.create_all(), etc...).

--
Gaëtan de Menten
http://openhex.org

[Randy Syring]

Gaëtan,

Thank you.

On Aug 27, 5:23 am, "Gaetan de Menten" <gdemen...@gmail.com> wrote: