A while ago I proposed a system for DB upgrades that records each
upgrade so it knows not to apply it again. This allows you to
continuously run the script (while developing for example) to get the
newest updates without having errors everywhere from upgrades that are
Victor liked the idea so I started working on it a bit. I wanted to
post what I've got to get some feedback.
I was originally going to use a sequential numeric ID but realized that
if two people were working simultaneously and then committed and had to
resolve by changing their IDs, their db would be messed up (fixable but
still messed up) so I though a string ID would be good. That way each
developer can commit changes with their initials followed by a
sequential number and be sure it is unique.
I think the layout of all this needs to be cleaned up... I'm not
convinced that we shouldn't just have one file for all the upgrades from
now on (instead of one per version) because it will always just apply
the necessary ones to make sure you're up to date. Also, the definition
of the upgrades should be in a file that gets included - that way we can
have more than one page that uses the data (for example I wanted to be
able to click on an upgrade description and open up the SQL for just
that upgrade in another window).
There are plenty of improvements that could be made. I've already
included the ability to pass in an array of upgrade ids to apply so we
can easily add checkboxes to the print view that let you execute only
certain ones, etc...
I think we should also allow upgrades that consist of running functions
so we can do more complex things like updating all the DB data to remove
extra slashes, etc (things that can't be done with just SQL).
The workflow as I see it is as follows:
1) develop your db changes locally.
2) Once you're sure they're right, commit additions to the upgrade
definition file. All the other developers can run the file regularly to
get updates required by code updates in CVS.
3) When we release, someone installs the DB schema from the last
release, runs the db upgrade file, and then exports a new schema with
the most recent changes and includes the new schema in the release. The
contents of mantis_upgrade_table will be exported as well so no matter
what release someone installs, the right upgrades will be marked as
The one thing that we have to be careful of is never to change an
upgrade command once it is released (and ideally once it is in CVS)
since people may have already run that upgrade and it won't run again if
you change it. You'd have to add a new upgrade that makes the
correction based on the state after the incorrect one.
Anyway, everybody have a look. Just apply the patch and then load up
Beta4 Productions (http://www.beta4.com)