[OPEN-ILS-GENERAL] Database schema deprecation/supersedes stuff

[Mike Rylander]

On Sun, Jun 5, 2011 at 1:04 AM, Dan Scott <dan at coffeecode.net> wrote:
> So, using the example of the native-array-functions branch for a
> database schema change, our process is something like:
>
> 1. Modify the base schema files in Open-ILS/src/sql/Pg
> 2. Create an upgrade script by hand named
> Open-ILS/src/sql/Pg/upgrade/XXXX.native-array-functions.sql in the
> branch; this script should just contain the core DDL/DML
> 3. When the time comes that we want to merge the change to a core branch:
>  1. cd Open-ILS/src/sql/Pg/
>  2. ./make-db-patch.pl --wrap upgrade/XXXX.native-array-functions.sql
> --num 0549 --name native-array-functions
>      (this generates upgrade/0549.native-array-functions.sql)
>  3. mv upgrade/0549.native-array-functions.sql
> upgrade/0549.native-array-functions.sql.temp
>  4. git mv upgrade/XXXX.native-array-functions.sql
> upgrade/0549.native-array-functions.sql
>  5. mv upgrade/0549.native-array-functions.sql.temp
> upgrade/0549.native-array-functions.sql
>      (the previous 3 steps preserve the git history of the file)

Not sure I follow this, re history.  But, in any case...

>  6. Edit the generated file to include useful comments
> 4. Then we test the upgrade script:
>  1. git checkout origin/master
>  2. perl Open-ILS/src/support-scripts/eg_db_config.pl --create-schema
>  3. psql -v eg_version="'0548'" -f
> Open-ILS/src/sql/Pg/upgrade/0549.native-array-functions.sql
>
> The problem with this last step is that, no matter what I supply as
> the eg_version value, I get:
>
> Upgrade script 0549 can not be applied:
>  applied deprecated scripts <NULL>
>  applied superseded scripts <NULL>
>  deprecated by <NULL>
>  superseded by <NULL>
>

hrm... I think you want `psql -v eg_version="'0549'" -f
Open-ILS/src/sql/Pg/upgrade/0549.native-array-functions.sql`, but I'm
not getting this same result.  I tested with trunk as of 0533, and
then with 0537 applied.  What do your evergreen.upgrade_list_applied_*
functions look like?

[snip]

>
> Other thoughts going through the process:
>  * I've tried to document what I think is a rational path for the new
> database schema regime. If it's reasonably accurate, we should
> probably throw it into the wiki.

The ultimate goal, at least as discussed at the conf while building
this stuff, was actually to move towards having a baseline schema for,
say, each major version (first number in the version string, 2 in
2.1.0, say) and then simply have an upgrade path from there forward,
including for fresh installs.

So it might look something like:

Developer:
---------------
1. Create an XXXX script that performs the needed modifications,
wrapped in BEGIN/ROLLBACK, and make sure it doesn't bomb out.
2. Commit that locally and publish a topic branch, or hand off to
committer, or be the committer and...

Committer:
----------------
1a. Cherry-pick/merge into master (or temporary merging branch)
         OR
1b. Pull master into working branch

2. Remove BEGIN/ROLLBACK from XXXX script and use make_db_patch.pl to
wrap in the next available.
         (NOTE: git should notice the similarity of the two and
consider it a move instead of add/delete)
3. Commit and push to origin/master

>  * When we actually can start making use of the deprecates /
> supsersedes tables, I suspect it will make more sense to have upgrade
> scripts broken into small pieces rather than large monoliths. For
> example, the native-array-functions upgrade script currently replaces
> approximately a dozen functions. If a subsequent fix or enhancement
> updates one of those functions, we wouldn't be able to deprecate the
> native-array-functions upgrade script because the updates to the other
> 11 functions in that script are still required.

Agreed completely.  Side benefit of this combined with the above
"base+upgrade everywhere": one place to edit schema objects, meaning
dramatically reduced chances of missing one or the other.

>  * Are we ready to start making use of pgTAP? Changing the database
> schema seems like a perfect use case for unit tests, to ensure that
> expected behaviour is maintained through the upgrade, and to
> demonstrate that buggy behaviour is fixed or non-existent behaviour
> comes into existence via the upgrade.

Ready? Sure. Tuit-ful? Not I...

>  * It seems weird to me to have scripts like make-db-patch.pl and the
> comments scripts living in the Open-ILS/src/sql/Pg/ directory - would
> there be an objection to moving them into build/tools?
>

No objections.

-- 
Mike Rylander
 | VP, Research and Design
 | Equinox Software, Inc. / Your Library's Guide to Open Source
 | phone:  1-877-OPEN-ILS (673-6457)
 | email:  miker at esilibrary.com
 | web:  http://www.esilibrary.com