[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
Here is my initial stab at this: http://code.google.com/p/sqlitefktg4sa/ Code plus tests. I welcome your feedback. On Aug 22, 1:48 pm, jason kirtland [EMAIL PROTECTED] wrote: 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 wrote: 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 [EMAIL PROTECTED] 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_... 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
On Wed, Aug 27, 2008 at 3:21 AM, Randy Syring [EMAIL PROTECTED] 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
Gaëtan, Thank you. On Aug 27, 5:23 am, Gaetan de Menten [EMAIL PROTECTED] wrote: On Wed, Aug 27, 2008 at 3:21 AM, Randy Syring [EMAIL PROTECTED] 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 Mentenhttp://openhex.org --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
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. On Aug 22, 1:48 pm, jason kirtland [EMAIL PROTECTED] wrote: 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 wrote: 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 [EMAIL PROTECTED] 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_... 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
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) 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_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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
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) 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_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) 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_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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
Thank you, I will look into this. On Aug 22, 12:44 pm, Michael Bayer [EMAIL PROTECTED] 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_... 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
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 [EMAIL PROTECTED] 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_... 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLite Foreign Key Triggers: how to extend sqlalchemy to generate them
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 wrote: 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 [EMAIL PROTECTED] 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_... 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. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---