Skip to content

Instantly share code, notes, and snippets.

@iffy
Created October 14, 2011 16:19
Show Gist options
  • Save iffy/1287559 to your computer and use it in GitHub Desktop.
Save iffy/1287559 to your computer and use it in GitHub Desktop.
sql patcher
$ 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';
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment