A reasonable tool to generate SQL diffs for our databases is Pyrseas. Basically this tool works with existing databases, to generate a YAML representation that is more easily diffed, and can be used to auto-generate an SQL update file.
Pyrseas is a python module, and can be installed into your dev virtualenv w/ pip:
pip install pyrseas
This gives you two commands: dbtoyaml and yamltodb
To generate an sql update, you'll need a YAML representation of the new state, and the database to be updated. Let's assume you've completed make a dev change to a module that requires a db schema change. Make the change the usual way for that module, so that the *-initialize_db command creates the correct schema (you already did that, or the tests won't pass)
We'll use the testdb since you've probably already have an ini file pointing at it.
python -m python -m cnxauthoring.scripts.initializedb testing.ini
dbtoyaml authoring-test >authoring-new-feature.yml
Now we need a db with the old schema. If you did not keep a copy of the db with the schema to be upgraded, checkout head of master, and create one. There are two ways to do so. First is to use the built-in initializedb functionality:
git checkout master
python -m python -m cnxauthoring.scripts.initializedb testing.ini
Alternatively, if you have the YAML file that represents the db to be upgraded, you can use it to "update" an empty db:
dropdb authoring-test (if needed)
createdb authoring-test
yamltodb authoring-test older-authoring.yml | psql authoring-test -f -
Either way, you're now ready to generate the update sql:
yamltodb authoring-test authoring-new-feature.yml >authoring-new-feature-update.sql
Sanity check the update script
update your database:
psql -U cnxauthoring authoring -f authoring-new-feature-update.sql
FIXME: How to distribute the update script and/or the YAML of the new schema? Should we checkin a YAML file next to the SQL schema? This could be used to test correct generation of the DB, BTW. Also, for version-to-version updates, perhaps we should store the update SQL in the repo.