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.

I thought I'd run through a list of tools that I used to make New Metal Army. New Metal Army is my first major website and I learned a lot along the way. Here are the tools in no particular order:

Subversion

First and foremost, subversion is a source control tool but it's also a safety net. I can't see how you can call yourself a developer if you don't have source control. From day one I got myself an account on Webfaction set up subversion and started versioning everything. They allow https connection so everything is safe and because I don't live in their data center it's off site as well

Trac

Again on Webfaction, I installed Trac. Trac is a project management webtool. It hooks in to svn so you can see repository changes as a timeline and see checkins as colored diffs. It also has a ticket system and wiki. I found the wiki to be invaluable. It allowed me to track my thoughts in a 'low barrier to entry' fashion. It's so easy to add a wiki page, there really isn't any excuse for not doing it! The ticket/task tracking is basic but fine for small teams. There is a simple notion of a milestone and a release but the metrics for measuring progress just aren't there. For me though, this was fine. Web projects are relatively simple and single man projects don't require metrics really.

TextMate

TextMate is a text editor primarily aimed at programmers but useful for anyone who needs to edit structured documents where the content is more important then the layout. I first ran in to TextMate while I was evaluating Ruby. The Ruby community seems to be dominated by macbook pro users sitting in Starbucks, bashing code in to TextMate and buzzing on coffee. Although I decided not to use Ruby I did stick with TextMate. It really is the premier text editor for OS X. It's the first editor that has replaced emacs for me. I didn't use any extensions for TextMate but I did use PyChecker and JSLint with it to check my python and Javascript code on save.

CSSEdit

CSSEdit is an editor devoted to CSS. The cool thing is that it has live editting. So as you edit the CSS file for a site, it changes the preview of the site. This is quite frankly brilliant and very cheap compared to the time it saved me.
Combined with YUI's CSSReset I can proudly say I 'ported' New Metal Army from Firefox to Safari, IE and Opera in a few days with minimal difficulty. CSSReset should be the basis for ALL website development and Yahoo should get a fucking medal for creating it. Cheers guys

Pixelmator

Pixelmator is a nice image editor the supports PSD's and layers. I got it as part of a MacHeist bundle and just started using it rather then paying hundreds of quid for Photoshop. Because it supports PSD's I can still work with artists who quite rightly want to use Photoshop.

VMWare Fusion

Working on a mac makes it hard to get Internet Explorer working. I don't want or need a PC so VMWare comes to the rescue. VMWare is an OS virtualisation package that basically allows me to run various versions on Windows, Linux and BSD in a window. So I can run IE 5.5, 6.0 and 7 in separate virtual instances and check compatibility across IE5.5+, Safari, FireFox 2+ and Opera 9+. Since I'm a one man operation time consuming and boring tasks like this need to be made as painless as possible... or they won't get done.

Firebug

Firebug is an extension for Firefox that allows you to examine pages and page elements to see how their layout has been calculated but it also allows you to debug Javascript inside the browser. It far exceeds IE's rather forced script debugger compatibility or .NET integration. This tool saved me months of debugging and more importantly sped up the learning curve for web development.

Wingware IDE

There were times when I needed to debug fiddly python code and the python command line debugger just wasn't cutting it. I turned to Wingware's IDE for debugging and it served me very well. I only used it a few times though.

NeoOffice

Neooffice is a Mac port of Open Office. It's a good replacement for Claris or MS Office. I only needed to write a few letters to lawyers and such but it's good to not have to pay £500 for the privilege. I did use it a lot for opening up 50Mb csv files and it coped quite well with it.

Python

Well this is pretty obvious, TurboGears uses Python so I am forced to work with it. Well luckily python is one of the best languages to do anything in. With a vast array of modules it's really quick to create scripts to process data for your website. As an affiliate for many music companies (iTunes, HMV, Play, Amazon, ...) I get access to huge XML and CSV files with their latest prices, stock and shipping information. Because of python's flexibility it is easy to merge this data in to New Metal Army.

TurboGears

TurboGears is a no brainer here. It's the web framework New Metal Army sits upon. For me though TG was more then a framework. TG comes with a great community bundled for free. I learned a lot just reading the mailing list. I also learned a lot by reading the code for TurboGears itself. It's well written and cleanly constructed. There is some advanced python on there but I don't think anything is overly complicated. Python is a great language to read and I think more programmers need to learn that they CAN dive in to most code and quickly work out what is going on.

PostgreSQL

Postgres has always been my database of choice. Unfortunately I have no real reason for this other then it's always covered my needs and it is standards compliant so I expect it to cover my needs for the foreseeable future. TurboGears uses an ORM mapper (SQLAlchemy) between it and the database so I could swap Postgres for MSSQL, MySQL, Firebird, Oracle or a number of others. Postgres serves New Metal Army VERY well.

I've been working in deployment scripts for my current project and sometimes I need to drop all the tables and sequences from the database so I can create everything from scratch. I had been doing a

 
DROP SCHEMA public CASCADE;
CREATE SCHEMA public AUTHORIZATION bob;
GRANT ALL ON SCHEMA public TO bob;
 

This was great but a little destructive and over the top. It removes stored procedures and triggers as well which isn't what I want. So I looked at the SQLAlchemy docs and there is a metadata command drop_all()

 
def drop_tables():
    metadata.drop_tables()
 

Unfortunately this doesn't CASCADE so this isn't going to work in cases where tables have foreign keys (which is most of the time). It also leaves sequences in place.

So I looked at dropping each table in turn with a cascade. So how do you get a list of tables and sequences in the database? I could hard code the names of the tables and sequences but that seemed a little poor.

It turns out that PostgreSQL has a set of views that expose the inner workings of your database and a few queries can give you all the information you need.

 
def get_table_list_from_db():
    """
    return a list of table names from the current
    databases public schema
    """
    sql="select table_name from information_schema.tables"\
        "where table_schema='public'"
    execute = metadata.execute
    return [name for (name, ) in execute(text(sql))]
 
def get_seq_list_from_db():
    """return a list of the sequence names from the current
       databases public schema
    """
    sql="select sequence_name from information_schema.sequences"\
        "where sequence_schema='public'"
    execute = metadata.execute
    return [name for (name, ) in execute(text(sql))]
 
def drop_all_tables_and_sequences():
    execute = metadata.execute
    for table in get_table_list_from_db():
        try:
            execute(text("DROP TABLE %s CASCADE" % table))
        except SQLError, e:
            print e
 
    for seq in get_seq_list_from_db():
        try:
            execute(text("DROP SEQUENCE %s CASCADE" % table))
        except SQLError, e:
            print e
 

The information_schema is full of interesting information. It's actually a 'view' of lower level database tables. You can find all sorts of performance and configuration information in there. Quite handy Here are some docs.

Good luck.