[ckan-dev] new migrate methodology.

[David Raznick]

Hello

I have been battling with the migration process for a while and have come up
with a new methodology to making migration scripts.

Currently, even with the best intentions, it is easy to get them wrong.  I
got mine wrong, and decided that the current way that is documented is not
foolproof enough.

Now that we have changed paster db create/init to use db upgrade it is
paramount that we do not make any mistakes.

The new process will follow along the lines of..

 1. Get a dump of the database schema before you add your new migrate
scripts.

   paster db clean
   paster db upgrade
   pg_dump -h host -s -f old.sql dbname

2.  Get a dump of the database as you have specified it in the model.

   paster db clean
   paster db create-test  #this makes the database as defined in the model
   pg_dump -h host -s -f new.sql dbname

3. Get agpdiff (apt-get it).  It produces sql it thinks that you need to run
on the database in order to get it to the updated schema.

   apgdiff old.sql new.sql > upgrade.diff
   (or if you don't want to install java use
http://apgdiff.startnet.biz/diff_online.php)

4. The upgrade.diff file created will have all the changes needed in sql.
 Delete the drop index lines as they are not created in the model.

5. Put the resulting sql in your migrate script.

   eg migrate_engine.execute('''update table .........; update table
....''')

6.  Do a dump again, then a diff again to see if the the only thing left are
drop index statements.

7.  run nosetests with --ckan-migrate flag.

Its that simple.  Well almost..

*  If you are doing any table/field renaming adding that to your new migrate
script first and use this as a base for your diff (i.e add a migrate script
with these renaming before 1).  This way the resulting sql wont try and drop
and recreate the field/table!!
*  It sometimes drops the foreign key constraints in the wrong order causing
an error so you may need to rearrange the order in the resulting
upgrade.diff.
*  If you need to do any data transfer in the migrations then do it between
the dropping of the constraints and adding of new ones

An example of a script doing it this way is here.
https://bitbucket.org/okfn/ckan/src/0d911add0300/ckan/migration/versions/034_resource_group_table.py
This hopefully will be about as difficult as they come.

I am going to update the docs to this effect if no-one has any objections to
doing it this way.

Thanks

David
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.okfn.org/pipermail/ckan-dev/attachments/20110222/e8270a8c/attachment.html>