[OPEN-ILS-DEV] A Proposal for Improving Functional DB Upgrades

[Dan Wells]

[Apologies if this hits the list multiple times; Chris Sharp and I were trying to work around a "greylisting" issue".]

Hello all,

One source of mild but continual frustration in our development/release process is database upgrade scripts.  In my experience, functional upgrades seem to cause the most headaches.

I propose that we stop doing functional DB upgrade scripts.  Instead, we separate *all* of our database functions into one or more dedicated files (probably more than one, for a variety of reasons, but that can be worked out later).  Once this is done, instead of worrying about upgrades for functions, we simply (via a new script) reload all the function files as part of the normal upgrade process.  We would continue doing DB upgrade scripts for data and schema changes.

Benefits:

-          No need to create and manage upgrade scripts for functions

o   This also means no need for workarounds (e.g. no-op scripts, different same-number upgrade scripts for different releases)

-          Reduced chance of incongruent DBs (i.e. DBs with mixed function versions).  When in doubt, just reset them all.

-          Easier management of local functional DB customizations (merge like any other code, then apply; this is our biggest upgrade frustration right now)

-          Easier development process (edit and apply from same root code as DB creation)

Drawbacks:

-          Time to load (minor, see below)

-          Loss of simple DB "version marking" for functional changes (could reference commits instead (like other code), though versioning could be shoehorned in if desired)

-          Less direct connection between schema and functions (no longer in same file)

I am sure there is more I am not thinking of at the moment, but I've mulled this over for quite a while, and haven't found a good reason to dismiss the idea.

One thing I wondered about was time needed to recreate every function.  Attached is a very quick-and-dirty scrape of every function in my local 2.10-ish branch (NOT for production use, but hey, it rolls back).  On a slow dev machine and a cold DB, this file still loads for me in under 7 seconds.  On a faster machine and a warm DB, it is less than 1 second.

I know it would take a little massaging to get our dependency ducks in a row, but it is certainly doable and I think it would be worth it.

What say you all?

Sincerely,
Dan


Daniel Wells
Library Programmer/Analyst
Hekman Library, Calvin College
616.526.7133

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20161028/af7e6c0b/attachment-0001.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: all_funcs.sql
Type: application/octet-stream
Size: 604441 bytes
Desc: all_funcs.sql
URL: <http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20161028/af7e6c0b/attachment-0001.obj>