SQLite Foreign Key Trigger Generator for SQLAlchemy

Background

This project exists because SQLite parses fk column constraints but does not enforce them. You can read more about the problem and its solution if you are interested. The gist of it all is that triggers can be used in SQLite to enforce fk column constraints. I had previously created a project to do this in PHP but have recently been trying to move to Python. Since I am using SQLAlchemy, I really desired to generate the FKs automatically, and this project was born.

External Links

• SQLAlchemy Google Groups Thread
• SQLite Wiki Page on FK Triggers
• SQLite FK Trigger Generator written in PHP

Installation

• download the zip package
• unzip contents to somewhere on your python path
• /usr/lib/python2.5/site-packages or
• c:\Python25\Lib\site-packages
• you should end up with something like c:\Python25\Lib\site-packages\sqlitefktg4sa\...

Source Code

Now lives at bitbucket: https://bitbucket.org/rsyring/sqlitefktg4sa/

Stability

• currently only tested on windows. If you have problems on non-windows system, feel free to put in a bug report and I will fix quickly.
• Even though we are only at 0.1, the code is very simple and there is a small test suite covering the obvious use cases. Therefore, it should be pretty stable and any errors should be obvious up front.

Example

Download the package and look in the tests directory for a full example. Its really simple to use:

import * from sqlalchamy
from sqlitefktg4sa import SqliteFkTriggerGenerator, auto_assign

# define tables using Sqlalchemy
...

# set some tables manually to have FK generation happen
mytable1.append_ddl_listener('after-create', SqliteFkTriggerGenerator)
mytable2.append_ddl_listener('after-create', SqliteFkTriggerGenerator)

# OR, assign it automatically for all tables in your metadata
auto_assign(metadata)


#create tables and triggers
metadata.create_all()

Features

Currently supports:

• insert
• update
• delete
• delete cascade