Been having a lot of fun last week with my Zope 3 project, using
SQLObject. The project is near the end, so now its time for polishing
some sharp edges.

One of the first things that came to mind was the lack of DELETE
CASCADE and foreign key constraints. That one was a required feature
for the project, so I’ve poked around a bit, and it didn’t seemed to
be hard to do. Hands at work, and one hour later I had a very basic
working version. I sent a patch to the mailing list and asked for some
ideas, because I wasn’t 100% sure if my approach made sense. Ian
Bicking pitched in asking some questions, which made me realize
that I had misunderstood the way that DROP TABLE xxx CASCADE
worked.

Hands at work again, and I’ve got some working tests which
seemed fine to me. Then I’ve tried running the tests with SQLite,
which I found out to be a very nice database. I’ve had heard of it a
couple times since I started with SQLObject, but never got the time to
figure out what it was about. It ends up that installing and setting
up SQLite was a snap. Ready to go, run the tests. Two failing. Ops, I
had forgot to implement the DELETE RESTRICT constraint for the other
databases. It worked for Postgres because it was handled by the
database. Fixed that one and checked in the work so far to
cascadegeddon-branch (suggestive name eh?).

This morning I felt that it would be interesting to put my app to use
the constraints and see how it would behave. The app itself is a
complete rewrite of an application to keep track of samples in a
laboratory (I’m not sure if I’m allowed to mention the lab name), and
has a fairly large set of classes. The old app was a
Powerbuilder-based app, using Sybase. During the development, we used
PostgreSQL, and this week we are working on getting Sybase support
working on SQLObject to move the app over. I received a couple of CSV
files, which I had to massage to get into a reasonable shape for later
import into the system. For massaging and importing, I wrote a small
class behaved like a mix of list and dict. I used python’s csv module
to parse the files and populate an instance of this class, modify the
table structure and then dump back to another file. Then, after all of
this, I was able to use the same class, together with SQLObject to
import record by record (instead of using the database features) so
that it works cross-database. After adding the constraints, my import
script failed to import 145 records out of around 60k, because they
failed to pass the constraints. That was a very good thing, at least worth
the time I spent to implement the constraints ;)

So, if that wasn’t enough fun, I used postgresql_autodoc, which is a
perl script to dump a database to some interesting formats like
Dia, HTML, XML and dot (part of the graphviz package) for documentation purposes, and then used
dot to generate this image, which shows the tables and their
relationships (in this case, the foreign key constraints).