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.

For the last year I've been working on a project in TurboGears. Well no I can basically say version 1.0 if New Metal Army is done. It's been a long time (nearly a year) and it's far from complete but what is there is basically feature complete and it makes a nice site.

Here is a quick summary of New Metal Army:

• Pulls together the news from the top metal and rock webites from around the world. All news is tagged and associated with appropriate bands
• Has full gig listings for the uk rock/metal scene with full venue details and links to buy tickets
• Brings together band details from wikipedia, flickr, youtube, musicbrainz and amazon

As it currently stands there are 2500+ bands, 400+ gigs, 100+ venues, 500,000+ band pictures and 1000000+ band videos and it's growing all the time.

I've learned a lot about the internet doing this project. Most of my time was spent researching different ways to scrape and understand websites. Here is a summary of what I learned:

• The internet is a mess. There are a lot of sites with HTML that isn't just slightly wrong but VERY wrong.
• BeautifulSoup goes a long way to parsing the bad markup in the internet.
• Structural markup (bold, italic, div etc) were the least of my worries. The semantic meaning of the data is very hard to discern. Now this is obvious but when I started I didn't really think about it. I assumed that MicroFormats would come to my rescue... but no one uses them (well very few people use them). I must confess that I have a task in my Trac to add them but it's not a priority at the moment. So even I don't use them!
• Even with MicroFormats the data I needed is tainted by human input (like most of the internet). I deal a lot with band names and bands names are often misspelt and adjectives like 'a' and 'the' added and removed.
• Following on from spellings: people in the world still insist on using foreign languages that have funny accents and despite what you read unicode, while simple to code in python, is not simple to think in. When will everyone learn to speak local
• A human algorithm for tagging things is... to tag things with a scatter gun approach. The code base looks in flickr for pictures of bands. It does this by looking at the tags associated with a picture. The trouble is when someone takes some photos at a concert they mass upload them and tag them all with the names of all the bands at the concert and sometimes with bands like the bands at the concert... not to useful.
• Python does scale pretty well to large projects... but it's easy to get 'leaks' which make your heap grow and grow. They aren't really leaks they are normally lists of things that you are forgetting to tidy up and python is dutifully holding them for you. This is something I really didn't think about until I deployed the site and associated tools and noticed that my 'Job Runner' (threaded application that ran various jobs) just grew and grew.
• Before you start writing anything in Python look for a module on the internet that does it. If you can't find a module to do it then write your own. However, write you own module knowing this: As soon as you have finished, somehow you will find a module that is more complete then yours on the internet and you will kick yourself for not finding it before!
• Zombies rock... everyone likes zombie and Simon (my Zombie) is no exception to this

I intend to add some articles on how I did various bits and bobs over the next few weeks.