Ian Bicking: the old part of his blog

SQLObject Tools

The whole web/WSGIKit/Rails thing has me thinking about the steps to get an application up and running, and I want to make that work really easily. I feel like WSGIKit does that fairly decently right now (no documentation, but that will come soon!) -- but I think SQLObject is also a key component in the equation (for any framework that uses SQLObject). So I've been thinking about how to make it easier to manage.

The first thing I've done is ConnectionHub, which will become the "default" connection. It's just a way to register a connection after class creation, so you can (probably based on configuration) connect all your tables to the same database, after the modules have been loaded. It will look something like:

import sqlobject

sqlobject.hub.processConnection = connectionForURI(
    'mysql://localhost/testdb')

But frameworks can probably put in that glue on their own, so you can just define your classes with no boilerplate configuration or glue. This is something people are already doing in some projects (like sqlo for Zope 3 -- a project which seems entirely invisible to Google), but this turns it into a supported pattern. And it's just plain easier. You can register connections globally (processConnection) or per-thread (threadConnection).

The other thing I've started work on is a sqlobject-admin command-line client. The program can find your SQLObject classes and create and drop tables, as well as show you the SQL CREATE statements and check your data definitions against the actual tables in the database (an imperfect comparison, but at least it can show obvious differences).

Still to be written for sqlobject-admin is something that records the SQL CREATE statements and gives them a "version" (which doesn't necessarily match with your software's version), and also stores that version number in the database (in some special table). Later when you do development elsewhere and have to bring a database in sync with the latest version of your code, the tool will be able to query the database for its version and run upgrade scripts (that you hand-code). The operations are actually pretty crude, but it's mostly just to codify a process that allows for more agile and automated database evolution.

Created 02 Apr '05

Comments:

Hey Ian,

actually today I was doing my considerations about switching to Ruby on Rails and there is really one thing keeping me from switching to the dark side of the Rails framework: lack of production ready PostgreSQL drivers. Ok, it's just a suspect. The http://download.rubyonrails.com/ site shows on their left column some links to recommended libraries and, guess what, their development is hanged since 2003 (http://www.postgresql.jp/interfaces/ruby/) and supports PostgreSQL up to 7.2. So I searched a bit more and found http://wiki.rubyonrails.org/rails/show/PostgreSQL which doesn't mention how Pg 8 is supported. So I google a bit and find a host claiming:

postgres-pr (0.3.6) A pure Ruby interface to the PostgreSQL (>= 7.4) database

So, wassup in the Ruby world with the Pg 8 support? You, for sure, know this stuff better than me. Maybe the PostgreSQL engine hasn't changed that much from version 7 to 8(having added only a few things beside Win support) so this maybe is a non-issue. But still I wouldn't like to get burnt loosing data in some of my tables for some unsupported driver I've used because there's no alternative to use MySQL.

But, probably, I'll decide to take my chances and switch anyway.

Mike

# Mike

Beats me, really. But I do know that the Postgres driver situation has always been a little odd on Python, and I guess that there may be some real technical reason for that, between psycopg, Popy, PygreSQL, and maybe one other driver. Now things are pretty stable, but it took a while for that to get figured out.

# Ian Bicking

You may find "sqlos" project has better results than "sqlo". <-;

# Tim Middleton

:-)

# Ksińôgowy