Database upgrades: SQL versus code

When last I wrote about database upgrades, one of the design designs was whether to have a database upgrade be a hunk of code (as in rails and other systems I've worked with), or an SQL script (as in the non-automated upgrade scripts that were checked in to MIFOS prior to December's work).

For the first cut (last December), I went with SQL, on the theory that (1) it might be easier for people to understand, especially sysadmins and others who wouldn't necessarily read much of the Java code, (2) many of the cases which came to mind, such as adding a column or adding a table, could be done this way, and (3) if the automated upgrade runs into trouble, it may be easier to run some scripts one at a time, perhaps with changes, rather than trying to mess around with Java (again, for some people). But I did have in mind, even then, that I might need to add code upgrades at some point.

Well, we found a case where the SQL scripts don't work. The MIFOS database contains tables which store things like strings which are displayed to the user. MIFOS ships with a whole bunch of these ("loan", "client", etc), but such strings can also be added by the microfinance institution. In adding them (at least the way our database is currently set up), one needs to assign at least one ID which is not referenced directly from the Java code, but which also is referenced elsewhere in the database. Although there are variants of SQL which have variables and the like (PL/SQL, PL/pgSQL, etc), I don't think MySQL has those kinds of extensions (and trying to turn SQL into a procedural language is somewhat awkward anyway). So the solution will be to implement Java upgrades. I have in mind keeping the ability to do SQL upgrades (that is, each upgrade is either a java upgrade or a SQL one). That is largely to ease the transition (we have about 19 upgrades already, and won't need to convert them over all at once). We'll also see whether writing upgrades in SQL, in those cases where it is possible, ends up being appealing or just a source of confusion.