Wednesday, April 28, 2010

Updating field sqlite databases

The feature I'm currently working on requires a new column to be added to an existing table in one of our sqlite databases. There are quite a few of these databases in the field so I wanted to write a routine that would update them automatically. After reading around. I settled on the following simple scheme:

def do_upgrade(self):
Do upgrade to a field database, anything we need to add to existing databases should be done here

conn = self.engine.connect()
conn.execute("alter table drive_stats add drive_model text") #Update for Sprint 43
except OperationalError:

This uses the Easier to ask forgiveness scheme, We try to alter the database, if it raises an error then ignore it.
ALTER will always complete in constant time so there isn't much of a penalty. We could run a SELECT to check for the existence of the column first, but this will take more time. I spent an hour or so looking for a better way of doing it, but this seemed to be quite widely accepted.