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

[Mike Rylander]

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.

-- 
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