LuckyDonkey

SQLAlchemy Migrate is a really good tool for managing database upgrades for SQLAlchemy centric projects. I've been using it for 6 months on New Metal Army and I haven't had a screwed website upgrade yet!

For those that don't know SQLAlchemy-migrate allows you to version control database changes and easily upgrade and downgrade a database. Basically you write a python script with two functions: upgrade and downgrade. You test the script against the database, commit it to the SQLAlchemy-migrate version repository (not to be confused with your source control mechanism). Finally you upgrade your development database.

When it the time comes to deploy the new application you simply ask sqlalchemy-migrate to upgrade your database to the current version. sqlalchemy-migrate reads the current version of your schema from the database (via a custom table it inserts) and proceeds to upgrade your schema by running each upgrade script in turn.

Often you want your upgrades and downgrades to run within a transaction. Not because you expect them to fail but because while writing them you don't wont to leave the database partially upgraded or downgraded if your script fails. To do this I wrote a transaction decorator. Here is a template for an upgrade script:

#!/usr/bin/env python
# encoding: utf-8
 
from datetime import datetime
from sqlalchemy import *
from migrate import *
from migrate.changeset import *
 
metadata = MetaData(migrate_engine)
 
def transaction(f, *args, **kwargs):
    def wrapper(*args, **kwargs):
        connection = migrate_engine.connect()
        transaction = connection.begin()
        try:
            result = f(*args, **kwargs)
            transaction.commit()
            return result
        except:
            transaction.rollback()
            raise
        finally:
            connection.close()
 
    wrapper.__name__ = f.__name__
    wrapper.__dict__ = f.__dict__
    wrapper.__doc__ = f.__doc__
    return wrapper
 
@transaction
def upgrade():
    pass
 
@transaction
def downgrade():
    pass
 

I fill in the upgrade and downgrade functions and I'm done
I include the decorator in every script as it's good practice to make your scripts as independent as possible. If you imported it from a module you may improve it in the future and inadvertently break your old downgrade scripts.

I hope this helps you version control your database schema and data.

While setting up a new FreeBSD 7.0 server I found that psycopg 2.0.7 doesn't easy_install on FreeBSD. It's because of a configuration problem in config.h at the bottom.

#if defined(__FreeBSD__) || (defined(_WIN32) && !defined(__GNUC__)) || defined(__sun__)
/* what's this, we have no round function either? */
static double round(double num)
{
  return (num >= 0) ? floor(num + 0.5) : ceil(num - 0.5);
}
#endif
 

However 'round' is defined in FreeBSD and has been since FreeBSD 5.3 (according to the manual page). The fix is simple, just remove the 'defined(__FreeBSD__) ||' part of the '#if' and you should be fine. Now you can 'easy_install .' psycopg2.

PS: I'd tried to raise a ticket but http://www.initd.org/ trac seems to have been down for ages.

Every bloody operating system has it's own unique way of starting things at boot time. Today I bothered to learn how OS X does it. Here's how I got PostgreSQL to start when my machine is turned on:

What gets started is handled by a program called SystemStarter. On boot it looks through /Library/StartupItems for folders. Inside those folders it looks for a script with the same name as the folder and a plist called StartupParameters.plist.

So as super user:

cd /Library/StartupItems/
mkdir PostgreSQL
touch PostgreSQL/PostgreSQL
touch PostgreSQL/StartupParameters.plist
 

Now we need to fill in the details. In PostgreSQL/PostgreSQL enter:

#!/bin/sh

sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/data/logfile start

This is a long line, but it's basically running postgres (via pg_ctl) as the user postgres. You'll need to change this to the whatever user you run postgres as and where ever your install of postgres is. I've used the standard names and directories so it's likely this will work for you too.

Now lets look at the plist. This file contains information for SystemStarter about when to start postgres, what postgres needs and provides and any messages to print to log files:

 
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist SYSTEM "file://localhost/System/Library/DTDs/PropertyList.dtd">
<plist version="0.9">
<dict>
        <key>Description</key>
        <string>PostgreSQL</string>
        <key>Messages</key>
        <dict>
                <key>start</key>
                <string>Starting PostgreSQL</string>
                <key>stop</key>
                <string>Stopping PostgreSQL</string>
        </dict>
        <key>OrderPreference</key>
        <string>None</string>
        <key>Provides</key>
        <array>
                <string>PostgreSQL</string>
        </array>
</dict>
</plist>
 

You can test your new settings by typing sudo SystemStarter -n -D, fix any error messages, reboot and you should have postgres all the time