[OPEN-ILS-DEV] Database upgrades (recent approach)

[Bill Erickson]

On Mon, Sep 21, 2009 at 3:23 PM, Dan Scott <dan at coffeecode.net> wrote:

> It's great to see the atomic database migration scripts (better name
> suggestions appreciated) getting committed recently! Is there a design
> document that lays out how these are expected to be used & how other
> developers should create these?
>


Thanks for the poke Dan, I've been meaning to discuss and request feedback
on this.  The problem of managing schema changes has gotten out of hand when
working on a development server.  The goal was to start moving in the
direction of atomic changes then work out the rough edges.   Here's a rough
brain dump of where things stand currently:

Each branch has it's own upgrade directory in Open-ILS/src/sql/Pg/, if
needed.

Every schema change or addition of seed data to a branch results in a new
file in that directory.

Clumping multiple changes into a single file is OK if they are tightly
linked (e.g. seed data for 2 related org unit settings).

Each new file uses the next unique numeric prefix.  0001, 0002, etc.

Following the numbers is ".schema" or ".data", indicating a schema change or
new seed data.

Anything after that is just descriptive.

If it's necessary to back-port a DB change, just merge the .sql file back
into the branch's upgrade directory.

Each new file tells the DB that it has been inserted with "INSERT INTO
config.upgrade_log (version) VALUES ('$ENTRY');"  The value for $ENTRY needs
discussion.  I belive most of the existing scripts put the actual file name
in there, while 002.schema.config.sql (which defines the baseline for a
newly created DB) is currently just storing the numeric prefix.

The value in 002.schema.config.sql will also need to be bumped up with each
additional script.

For schema changes, the db upgrade scripts capture the ALTER TABLE, etc.
statements.  Changes to the table definitions still need to be added to the
base schema file for new installations.

Similarly, seed data should still go into 950.data.seed-values.sql as well.
At least, that's the current thought.  I could see an argument for changing
that.

At minimun, this makes it possible for a developer to easily see what DB
changes need to be applied since a given revision.  The longer term goal is
automated DB upgrades.

I'm sure I'm missing a thing or two.  Does this sound reasonable?

I'll be glad to wiki-fy this after we come to concensus, work out the edges,
etc.



>
> I also think we have a number of atomic scripts to create retroactively (?)
>


It may be simpler to just account for missing changes in the first batch of
upgrade scripts.  IOW, going back and adding all the changes could be a big
job and not worth the effort.  I'll defer judgement on that, though.



>
> As an aside, I ran across
> http://people.debian.org/~seanius/policy/dbapp-policy.html/ch-dbapps.html<http://people.debian.org/%7Eseanius/policy/dbapp-policy.html/ch-dbapps.html>
> and http://people.debian.org/~seanius/policy/dbconfig-common.html<http://people.debian.org/%7Eseanius/policy/dbconfig-common.html>
> recently, which contributes some suggestions towards resolving the
> thorny problems of how we would go about packaging up the database
> schema / migrations should we ever get to the shangri-la of Debian
> packages for Evergreen.
>


Thanks, Dan!

-b


-- 
Bill Erickson
| VP, Software Development & Integration
| Equinox Software, Inc. / The Evergreen Experts
| phone: 877-OPEN-ILS (673-6457)
| email: erickson at esilibrary.com
| web: http://esilibrary.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20090921/cac53998/attachment.htm