[OPEN-ILS-DEV] DB versioning

[Mike Rylander]

Dan Scott and I have been discussing on and off over IRC how we might
go about versioning the database schema for EG.  This came up because
of a schema update patch he sent in that fixes some of my sloppy
typos.

The idea we both hit upon (more or less) was to create a new table to
record the schema revision number, the update time of the schema, and
a description of the changes.  Then use upgrade files with
monotonically increasing numbers in their names to identify what has
and has not been applied to any given database instance when presented
with a set of upgrade SQL scripts.

So, I went about creating the skeleton of a first cut this.  Trying to
be internally consistent, the first update script would install this
new table and register the change in this shiny new versioning
repository in a file called
ILS/Open-ILS/src/sql/Pg/upgrade/1.schema-versioning.sql, thusly:

----------------------------------------------
BEGIN;

-- create a table to record versioning information
CREATE TABLE config.schema_version (
        revision INT PRIMARY KEY,
        apply_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
        description TEXT NOT NULL
);

-- register the update
INSERT INTO config.schema_version ( revision, description ) VALUES ( 1,
'
Creates a table to be used in schema versioning.  This is a simple setup with
no care taken to initiate replication.  In fact, do not run this or any other
upgrade SQL script directly if you currently use Slony-I for replication of
your Evergreen database.  It should, however, be safe on PGPool (I and II)
replicated databases -- but that is entirely untested.

See Open-ILS/src/sql/Pg/upgrades/1.schema-versioning.sql
' );

COMMIT;
----------------------------------------------

So, you'll notice in the description there's a big warning about not
using this script directly when using Slony-I for replication.  That's
because Slony requires the use of a special mechanism for DDL stuff.
See http://slony.info/ for more info, but suffice it to say that it's
one of the trade-offs in we've made in selecting a trigger-based
replication mechanism for the PINES EG installation.  As Slony is
(TTBOMK) the most widely used replication system for Postrgres, I
think we'll need to address this (and I have some ideas as to how) but
I'm going to ignore it for this prototype explanation.

Ahem ... moving on.

As mentioned, Dan sent a schema upgrade script.  Slightly modified,
here it is in a file called
ILS/Open-ILS/src/sql/Pg/upgrade/2.typo-fix.sql:

----------------------------------------------
-- DB Updates to accompany typo-fix patch provided by Dan Scott
<denials at gmail.com>
-- http://list.georgialibraries.org/pipermail/open-ils-dev/2007-May/001032.html

BEGIN;

-- config schema
ALTER TABLE config.rule_recuring_fine RENAME COLUMN recurance_interval
TO recurrance_interval;
ALTER TABLE config.rule_recuring_fine RENAME TO config.rule_recurring_fine;

-- asset schema
ALTER TABLE asset.copy_tranparency_map RENAME TO asset.copy_transparency_map;
DROP INDEX cp_tr_cp_idx;
ALTER TABLE asset.copy_transparency_map RENAME COLUMN tansparency TO
transparency;
CREATE INDEX cp_tr_cp_idx ON asset.copy_transparency_map (transparency);

-- action schema
ALTER TABLE action.circulation RENAME COLUMN recuring_fine TO recurring_fine;
ALTER TABLE action.circulation RENAME COLUMN recuring_fine_rule TO
recurring_fine_rule;
ALTER TABLE action.transit_copy RENAME COLUMN persistant_transfer TO
persistent_transfer;

-- reporter schema
ALTER TABLE reporter.report RENAME COLUMN recurance TO recurrance;

-- register the update
INSERT INTO config.schema_version ( revision, description ) VALUES ( 2,
'
Fixes several instances of known typos including "tansparency", "tranparency",
"recuring" and "recurance".

Justification for typo fixes - especially with "tansparency" or "tranparency"
vs. "transparency", and to a lesser extent for recuring / recurance, I _know_
that I would end up tripping over that some time in the future as I struggle
to figure out why my perfectly sensible SQL was erroring out. Plus it gives a
little more polish to the code.

See Open-ILS/src/sql/Pg/upgrades/2.typo-fix.sql
' );

COMMIT;
----------------------------------------------

As you can see, no biggie there.  But now we know, definitively, if
those changes exist in the database.  They are registered in
config.schema_version table and accessible to an upgrade script.

"But Mike," I hear you saying, "it looks like all DB changes from now
on are going to have to be in the form of these little patches, and
that just sounds like pain!"  Well, as the fixes are incorporated into
the main schema files (via actual patches, and not scripts) we will
include the INSERT statements that they would add to
config.schema_version along with the direct changes.  We just
pre-register the changes so the upgrade SQL won't be used.

I'd like to open the floor to comments on this.  There's plenty more
we could store in the schema_version table, for instance, but I'm
avoiding going to far with that unless there is a demonstrable need
for some other data (which can be reliably and completely captured,
and will provide some benefit by it's record).  Remember that we can
always change the schema of the schema_version table if we need to in
the future... ;)

(Next installment will be thoughts on addressing the Slony problem ...
until then, thanks for listening, all.)

-- 
Mike Rylander