In reference to my database related mess during the first beta release of 3.6.0, I want to propose a few changes to how we manage schema changes in NAV.
We do version control our schema, which is good, but the process has some weaknesses. The fact that we maintain a baseline and change scripts separately at the same time has repeatedly caused inconsistencies.
Usually, I catch these problems with a simple schema diffing tool, but since we still have no automated tests going on, this is a tedious manual task and prone to being forgotten.
I found a series of articles about database version control via the Coding Horror blog. These describe many of the ideas I was having as I was fixing the b1 release: http://www.codinghorror.com/blog/2008/02/get-your-database-under-version-con...
Basic summary of the changes I'm suggesting:
* We keep the sql scripts, doc/sql/*.sql, as they are - these are our baseline.
* Instead of everyone maintaining both doc/sql/upgrades/trunk.sql and the baseline scripts: Whenever a developer needs to change the schema, he/she should check in a new sql scrip which contains only the ALTER/UPDATE statements needed to migrate to the new schema.
* Schema initialization and upgrade are merged into one automatic process that applies the baseline, and then consecutively the change scripts committed since the baseline.
* To keep track of which schema changes have been installed, a new schema_change_log table in the database is updated by the automagic schema install/upgrade process.
* Every once in a while, typically at a new feature release, the schema change files can be summarized into a new baseline.
This process would also make it easier to install an arbitrary revision from a Mercurial repo and ensure that one has the correct schema for that revision.
I guess we will be busy prepping the final 3.6.0 for some time yet, but we should be looking into this post 3.6 (not to mention all the automatic testing I'm dreaming of putting togheter post 3.6).
Feedback/comments are appreciated, though I know many of you are busy with non-NAV-related stuff this time of year.