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

[Dan Scott]

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

Checking what's actually happening, it looks like the following
functions always return a row containing a NULL (at least in a clean
database schema):

evergreen=# \pset null (NULL)
Null display is "(NULL)".
evergreen=# SELECT evergreen.upgrade_list_applied_deprecated ('0548');
 upgrade_list_applied_deprecated
---------------------------------
 (NULL)
(1 row)

evergreen=# SELECT evergreen.upgrade_list_applied_superseded ('0548');
 upgrade_list_applied_superseded
---------------------------------
 (NULL)
(1 row)

... and that means that COUNT(*) != 0 in the UNION within
evergreen.upgrade_deps_block_check(). If I understand this correctly,
and this isn't just something related to my environment, that means
that for over a month we've effectively not been able to actually test
or apply the database schema upgrade scripts that have been created
using the new database schema regime. That seems non-good.

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.
  * 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.
  * 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.
  * 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?