sql patcher

output

$ python patcher.py 
-- UPDATE customer to version 1
create table customer (
        id integer primary key,
    );
update _schema_version set version='1' where what='customer';
-- UPDATE customer to version 2
alter table customer add column (email text);
create unique index foo on customer(email);
update _schema_version set version='2' where what='customer';

-- REVERT customer to version 1
alter table customer drop column email;
drop index foo;
update _schema_version set version='1' where what='customer';

patcher.py

class Patcher:
    """
    I help maintain schema versions
    """
    
    def __init__(self):
        self.patches = {}
        self.reverts = {}


    def patch(self, what, to_version, sql):
        """
        Record the patch to get to a certain version
        """
        if what not in self.patches:
            self.patches[what] = {}
        self.patches[what][to_version] = sql


    def unpatch(self, what, to_version, sql):
        """
        Record the patch to revert to a certain version
        """
        if what not in self.reverts:
            self.reverts[what] = {}
        self.reverts[what][to_version] = sql


    def update(self, what, from_version, to_version):
        """
        Get the sql needed to update `what` from from_version to to_version
        """
        patches = self.patches[what]
        for i in xrange(from_version+1, to_version+1):
            yield '-- UPDATE %s to version %s' % (what, i)
            for s in patches[i]:
                yield s + ';'
            yield "update _schema_version set version='%s' where what='%s';" % (i, what)


    def revert(self, what, from_version, to_version):
        """
        Get the sql needed to revert `what` from from_version to to_version
        """
        reverts = self.reverts[what]
        for i in xrange(from_version-1, to_version-1, -1):
            yield '-- REVERT %s to version %s' % (what, i)
            for s in reverts[i]:
                yield s + ';'
            yield "update _schema_version set version='%s' where what='%s';" % (i, what)
        
    


sqlite = Patcher()

sqlite.patch('customer', 1, [
    '''create table customer (
        id integer primary key,
    )''',
])
sqlite.unpatch('customer', 0, [
    '''drop table customer''',
])

sqlite.patch('customer', 2, [
    '''alter table customer add column (email text)''',
    '''create unique index foo on customer(email)''',
])
sqlite.unpatch('customer', 1, [
    '''alter table customer drop column email''',
    '''drop index foo''',
])

# I want to update customer from nothing to 2
for sql in sqlite.update('customer', 0, 2):
    print sql

print ''
# I want to revert customer from 2 to 1
for sql in sqlite.revert('customer', 2, 1):
    print sql