[OPEN-ILS-DEV] SUGGESTION: Tagging DB schema versions

[Mike Rylander]

On Thu, Aug 21, 2008 at 8:37 PM, Mike Rylander <mrylander at gmail.com> wrote:
> On Thu, Aug 21, 2008 at 8:05 PM, Brandon W. Uhlman
> <brandon.uhlman at bclibrary.ca> wrote:
>> Hi!
>>
>> As part of trying to track down a problem, I'm wondering if my installed
>> software version and the database schema that I'm working on have diverged.
>>
>> To that end, I'm going through collections of schema upgrade scripts to see
>> if new schemata from each of the scripts have been added to my instance. It
>> would be a lot easier if we took a page from (say) Ruby on Rails, which
>> includes in their 'database migrations' a version tag, which lets me know
>> which revision of the database my schema is currently at.
>>
>> This could take the form of a single table, for example,
>> config.db_schema_version, with a single column, version, and a single
>> record, with a string representing the current version. In the short term we
>> could keep the string for human use, but over the longer term, we could
>> automate schema upgrades by watching this value.
>>
>> Lately, I feel like I've been throwing out lots of ideas that require
>> coding, and not submitting lots of code. I hope that will change soon. :-p
>>
>> Any comments on this idea?
>
> We've tossed this idea around before.  In fact, we've gone as far as
> spec'ing out a whole versioning and upgrade plan, but obviously it's
> not come to anything.
>
> I think a well-known location in the database to store the schema
> version would be great.  The one change I'd make to your plan would be
> to store /all/ the versions (that are, in fact, versioned) in that
> table, and add a timestamptz column defaulting to now() so you'd know
> when each upgrade was applied.
>
> Thanks for bringing it back  up, B.

With the looming 1.4.0.0 release (working on rc2 ATM) I wanted to
bring this back up.  As of 1.4.0.0rc2, there is a table called
config.upgrade_log with version and install_date columns.  From now on
we'll supply one (or more) inserts into that table at each upgrade in
order to track how and when the schema changes for a particular
installation.

This can be extended in the future, but it's a start and a seed.
Thanks again, Brandon, for poking about this.

-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / The Evergreen Experts
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com