[ckan-dev] new migrate methodology.

[David Read]

On 22 February 2011 22:42, David Raznick <kindly at gmail.com> wrote:
> 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.

actually:
--ckan-migration

> 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.

These process seems thorough, but almost as difficult as the last
migrate test system to carry out, which was its demise. Can we not do
this all as an automatic test on buildbot, or are the steps a bit
difficult?

One other thing the old test system did was testing data migrations
(not just schema migrations). Any ideas for this, or do we just cross
this bridge the next time we do it?

Dave

> Thanks
> David
>
> _______________________________________________
> ckan-dev mailing list
> ckan-dev at lists.okfn.org
> http://lists.okfn.org/mailman/listinfo/ckan-dev
>
>