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

[Mike Rylander]

On Mon, Jun 6, 2011 at 12:23 AM, Dan Scott <dan at coffeecode.net> wrote:
> 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).
>

Duh, no, sorry.  You had it correct.  eg_version should be the highest
number in config.upgrade_log.version that matches ^\d{4}$ before the
script in hand is applied.

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

They looked the same by eyeballing, but in case I missed something,
what happens if you apply
upgrade/0537.schema.upgrade-dep-tracking-fix.sql ?

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



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