On 12/10/06, Victor Ng <[EMAIL PROTECTED]> wrote:
>
> Hi Russ,
>
> I've got a rough version of schema evolution working now.
...
> I honestly think it's a *bad* solution to the problem. I've been
> looking at sqlalchemy and the 'migrate' project :
The attached file is in no way a complete solution, but it's working
for us with PegasusNews.com. It doesn't handle rollback; we assume a
db restore of an old version is the likely recovery approach. :)
We're using postgresql and an old version of django, but with
adjustments, the script should be workable for lots of people. One
thing to note is that postgresql can do almost any schema changes
under a single transaction. This is an unusual feature, and the
script would be much more complicated for, say, mssql or oracle. It
gives nice error messages and generally tries hard to only fail in
clean ways.
db_upgrade.py is a general driver script. It expects a setting,
UPGRADE_STEPS_PATH, to define the path at which a separate
upgrade_steps module can be imported. See the attached
upgrade_steps.py for further usage notes. db_upgrade.py also expects
the target schema to have a table named db_version which defines the
current version.
texasgigs=> \d db_version
Table "public.db_version"
Column | Type | Modifiers
---------+-----------------------+-----------
branch | character varying(50) |
version | integer |
The general approach is to get the version from the db, the highest
version magically-named migration function in the upgrade_steps
module, backup the existing db, and then successively run the
migration functions until either all of them have been run or an error
occurs.
If DEBUG=True, commits are done between each migration function.
Also, note that the passed conn and cursor are Django's wrapped
conn/cursor, so you are free to use the DJ DB API in the migrations;
you aren't limited to SQL via conn/cursor.
I hope it's useful to someone, and let me know of any questions.
-Jeremy
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
"""
This script is called by ./util/db_upgrade.py.
Functions in here are passed an open connection and related cursor.
Functions should be named <branch-name><destination-version>,
e.g. trunk2 takes trunk from version 1 to version 2 of the database.
Make a new function for each commit (unless you're fixing a data-eating upgrade FUBAR).
If you're making schema changes, mention the new DB version number in svn commit messages.
Throw errors from here if bad things happen in the upgrade; they'll be handled in the driver script.
Don't update db_version or commit in here; the driver script will take care of it.
"""
def trunk2(conn, cursor):
cursor.execute("some sql statements here")
def trunk3(conn, cursor):
cursor.execute("""
UPDATE categories set representation = replace(representation, '/', '>');
""")
from django.conf.settings import UPGRADE_STEPS_PATH
from ellington.categories.parts import import_categories
import os
fn = os.path.join(UPGRADE_STEPS_PATH,"dy", "cats")
import_categories.import_categories(fn)
#!/usr/bin/python
import sys, os
import psycopg
from django.core.db import db
from django.conf.settings import DATABASE_USER, DATABASE_NAME, DATABASE_HOST, DATABASE_PORT, DATABASE_PASSWORD, DEBUG, TIME_ZONE, UPGRADE_STEPS_PATH, DEBUG
def usage():
sys.stderr.write("%s [--nobackup] \n Expects DJANGO_SETTINGS_MODULE to be set.\n")
if len(sys.argv) == 1:
do_backup = True
else:
if sys.argv[1] == "--nobackup":
do_backup = False
else:
usage()
sys.exit()
try:
os.environ['DJANGO_SETTINGS_MODULE']
except KeyError:
usage()
sys.exit()
def get_conn_string():
if DATABASE_NAME == '':
from django.core.exceptions import ImproperlyConfigured
raise ImproperlyConfigured, "You need to specify DATABASE_NAME in your Django settings file."
conn_string = "dbname=%s" % DATABASE_NAME
if DATABASE_USER:
conn_string = "user=%s %s" % (DATABASE_USER, conn_string)
if DATABASE_PASSWORD:
conn_string += " password='%s'" % DATABASE_PASSWORD
if DATABASE_HOST:
conn_string += " host=%s" % DATABASE_HOST
if DATABASE_PORT:
conn_string += " port=%s" % DATABASE_PORT
return conn_string
def get_db():
log("Opening DB for upgrade")
cur = db.cursor()
conn = db.connection
return conn, cur
def get_current_version(cur):
"""
Assumes:
create table db_version (branch varchar(50), version integer);
insert into db_version values ('trunk', 1);
"""
try:
cur.execute('select branch, version from db_version')
except psycopg.ProgrammingError, e:
logerr("Couldn't find db_version \n====%s\n====:\n perhaps you forgot to restore a DB dump or should create the table with ('trunk', 1)?" % e)
return cur.fetchone()
def log(s):
sys.stderr.write('info: %s\n' % s)
def logerr(s):
sys.stdout.write('error: %s\n' % s)
sys.stdout.write('error: You probably want to restore the DB!\nQuitting.\n')
sys.exit()
def backup_db(init, final) :
import subprocess
from time import gmtime, strftime
dumpfile = "db_preupgrade_%s_from_%d_to_%d_at_%s_dump" % \
(DATABASE_NAME, init, final, strftime("%Y%m%dT%H%M%S", gmtime()))
log("If you're asked for the DB password, it's: %s" % DATABASE_PASSWORD)
#FIXME: use subprocess.Popen and tie pg_dump output to gzip input.
ret = subprocess.call(["pg_dump", "-f", dumpfile, "-U", DATABASE_USER, "-h", DATABASE_HOST, DATABASE_NAME])
if ret != 0:
raise RuntimeError, "Failed to create pg_dump %d" % ret
return dumpfile
if __name__ == '__main__':
sys.path.insert(0, UPGRADE_STEPS_PATH)
import upgrade_steps
conn, cur = get_db()
label, initial_version = get_current_version(cur)
log("Starting at version %d of db branch %s." % (initial_version, label))
steps = []
version = initial_version
while True:
try:
version += 1
func_name = '%s%d' % (label, version)
steps.append(getattr(upgrade_steps, func_name))
if not callable(steps[-1]):
logerr("%s is not callable, quitting." % func_name)
sys.exit()
except SystemExit:
raise
except:
break
final_version = version-1
if initial_version == final_version:
log("No DB upgrade to do. Already at %s version %d." % (label, initial_version))
log("Quitting.")
sys.exit()
log("Upgrading from %d to %d on %s" % (initial_version, final_version, label))
if do_backup:
try:
log("Backing up existing db.")
dumpfile = backup_db(initial_version, final_version)
except RuntimeError, e:
logerr(e)
log("Backed up to %s" % dumpfile)
else:
log("skipping backup")
version = initial_version
for step in steps:
try:
version += 1
step(conn, cur)
cur.execute("update db_version set version = %d", [version])
if DEBUG: #commit changes from each step to aid in debugging.
conn.commit()
log("Done with version %s" % version)
except psycopg.ProgrammingError, e:
logerr("SQL failed in db upgrade to version %d:%s" % (version, e))
except Exception, e:
logerr("Unknown error updating on to version %d: %s" % (version, e))
conn.commit() #final commit with all steps completed.
log("Successfully completed upgrade from %d to %d" % (initial_version, final_version))
