On Sun, Apr 10, 2005 at 12:29:29PM -0600, David Everly wrote: > Does anyone know of a package that I can look at that has a mechanism to > run a series of certain SQL scripts based on upgrading from a certain > version to a certain version? > > For instance, when upgrading from 1.1-1 to 1.1-2, I want to delete a row > from a table, when upgrading from 1.1-2 to 1.2-1, I want to alter a > table, and when upgrading from 1.1-1 to 1.2-1, I want to do both of > these actions (first the delete, then the alter). > > I'm already using wwwconfig-common. Any suggestions on nice ways to > organize these to know which scripts to run and in which order are > appreciated. It's a stone drag of a problem. I used to do the exact method suggested by Craig Small (switch and go), but that becomes too wieldy if you've got a lot of regular updates. What I switched to doing was having an external script do the upgrade. I've got two programs that do this, each fairly differently. For an internal project, I've got a simple field in the DB which specifies which DB revision it's currently up to (incremented by one every time the DB changes), and then I just for i in $(seq $dbver $lastver); do mysql < upgradeto$i.sql; done (each upgradeto$i.sql has, as it's last statement, "update config set dbver=6" or whatever the dbver is it's upgrading to). For IRM, when I took over maintenance it was a mess, with random code determining what to upgrade and how. I've since switched it to a very large hash/dictionary (depending on your preferred term) keyed on the database version it's coming from, and with the data being an array of the instructions to execute against the database[1]. You then (again) retrieve the current DB version, and run through the upgrades hash, running any statements associated with keys that are >= the retrieved DB version. Note that both of these methods require the DB to have some knowledge of it's own version. This is very useful, and a damn sight easier than managing it all through package version numbers (especially if the program is going to be used by more than just Debianites). There is also rumoured to be a tool floating around that can take a look at a database schema, compare it against a representation of that schema (in XML, of course), and return a bunch of SQL statements to be executed which will bring the schema up-to-date. Unfortunately, I've only heard of this with regard to the MDB database abstraction library for PHP. - Matt [1] I've recently enhanced this to also allow the specification of a function to execute instead; this is needed because some of the upgrade instructions are conditional upon the state of the database at upgrade time, and the previous method just wasn't working. The code for this is only in Arch at the moment, not released).
Attachment:
signature.asc
Description: Digital signature