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

[Dan Scott]

On Sun, Jun 5, 2011 at 11:17 AM, Mike Rylander <mrylander at gmail.com> wrote:
> 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...

Well, if as you suggest below that git will automatically figure out
that a git rm file1 / git add file2 will notice that file1 and file2
are substantially similar and treat it as git mv file1 file2, that
removes one step at least. So, just my git ignorance on display.

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

I get the same results with eg_version="'0549'", fwiw. So eg_version
should always equal the same number as the upgrade script that's
currently being run? We're applying upgrade script '0549' to version
0549? (I'm trying to work out what the columns in config.upgrade_log
are supposed to mean).

The functions look exactly like the functions in 002.schema.config.sql
in master, running on PostgreSQL 9.0.4 on Fedora 15. I get the same
result (FALSE) when I invoke
evergreen.upgrade_verify_no_dep_conflicts('0549') if I roll back to
ec28db76588c79d7d9176fe7eeb4e6537a277301 (when 0533 was added) and
recreate the functions from 002.schema.config.sql at that time. And of
course, at that point in time evergreen.upgrade_deps_block_check()
fails because of the ARRAY_ACUM() typo.

> [snip]
<snip>

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

Okay, that's cool.