I've been thinking about ways to migrate database content as my site is
developed, and I though I'd see what the community has to say about an
idea.
I'm using subversion to track changes to the codebase, so I need the
migration solution to work well in parallel with subversion. I'd like
to do something similar to ROR and SQLAlchemy's migration solutions -
namely that you would define up and down step files in which you
explicitly define how to migrate between versions of the database
schema. Here's what seems to make sense to me:
1) create a migration file (more on this later)
2) prior to upgrading to a new version of the site, run a pre-migrate
script (eg 'tg-admin pre-migrate'). this script would create a copy of
the current site's files and create a 'transfer' database into which
the script would copy the existing database. In a sense, the
pre-migrate script would be creating a backup copy of the existing
site, including the database.
3) upgrade the site code using subversion (or by editing the code in
place).
4) run a migrate script (eg 'tg-admin migrate'). This script would
drop the site's database and reinitialize it (sql drop && sql create).
The new database would conform to the updated site's model definitions.
The migrate script would then populate the new (empty) database using
the backup copy and the migration file created in step 1
5) "tg-admin migrate" would delete the old site copy and drop the
transfer database if the migration completed without errors
The migrate script would move data between the old and new sites by
essentially starting both simultaneously. It would scan the new site's
model for all the defined classes and populate them based on the
migration file.
Migration files would specify differences between the two models.
Unless you specify otherwise in the migrate file, "tg-admin migrate"
would assume that any class or property with the same name in both the
new and old model has not changed, and assign the value from the old
version to the new version.
Anything that has been changed could be specified in the migrate file,
so for example if Class.property1 had been renamed to Class.property2,
you could specify something like this in the migrate file:
new.Class.property2 = old.Class.property1
Ideally, "tg-admin migrate" would provide a set of conversion functions
allowing you to do thing such as change MultipleJoins to RelatedJoins,
or FloatCol's to IntCols - something like this:
new.Class.property1 = MultipleJoinToRelatedJoin( old.Class.property1 )
new.Class.property2 = FloatColToIntCol( old.Class.property2,
round='down' )
You could also specify ways in which the old database would need to be
changed to conform to the new model:
new.Class.version = old.Class.version + 1
To clarify a bit: if you had the following classes:
OLD---------
def Journal_Entry(SQLObject):
date = DateTimeCol()
content = StringCol()
def Link(SQLObject):
uri = StringCol()
NEW---------
def Journal_Entry(SQLObject):
date_created = DateTimeCol()
content = UnicodeCol()
def FavoriteLink(SQLObject):
uri = StringCol()
comment = StringCol()
MIGRATE-----
new.Journal_Entry.date_created = old.Journal_Entry.date
new.Journal_Entry.content = StringColToUnicodeCol(
old.Journal_Entry.content )
new.FavoriteLink = old.Link
--------------
the migrate script would retrieve all the new model's classes, discover
the Journal_Entry class, and retrieve all instances of that class from
the old site's backup copy. For each existing instance, it would
create an instance in the new site. Each new instance's property
'date_created' would be set to the old instance's 'date' property.
Content properties would be converted to unicode. etc.
The benefits of doing migrations like this is that the migration would
be completely independent of the DB Provider (in fact it would be a
good way to change between SQLAlchemy and SQLObject), involves no
tinkering with SQL, and plays well with version management software.
Comments?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
