[OPEN-ILS-DEV] Proposed upgrade to serial schema

[Dan Wells]

Hello Scott,

You have divined my intentions exactly.  The only small change I would make is to note that the 'shadowed' column will soon have a NOT NULL constraint, so I think it makes sense to add it now.

Thanks,
Dan

>>> On 7/27/2010 at 10:22 AM, Scott McKellar <mck9 at swbell.net> wrote:
> The attached is a draft of an upgrade script for the serial schema.  Please 
> review before I commit it (along with the corresponding changes to the schema 
> itself and its install script).
> 
> This script is designed to bring the trunk version of the serial schema into 
> sync with Dan Wells' most recent version in the seials-integration branch, 
> except as noted below:
> 
> 1. I ignored a handful of minor changes in comments and white space.
> 
> 2. Dan's version doesn't include a couple of triggers present in trunk: 
> b_maintain_901 and c_maintain_control_numbers.  I'm not dropping them from 
> trunk.
> 
> 3. Where Dan's version adds the column caption_and_pattern to the 
> serial.issuance table, it ostensibly adds it in the middle, between 
> date_published and holding_code.  I don't have a good way to add it anywhere 
> but the end.
> 
> 4. Dan's version of serial.unit (which inherits from asset.copy) is 
> completely different.  Here's the trunk version:
> 
> CREATE TABLE serial.unit (
>     label           TEXT,
>     label_sort_key  TEXT,
>     contents        TEXT    NOT NULL
> ) INHERITS (asset.copy);
> 
> ALTER TABLE serial.unit ADD PRIMARY KEY (id);
> 
> And here's Dan's version:
> 
> CREATE TABLE serial.unit (
>     sort_key          TEXT,
>     detailed_contents TEXT    NOT NULL,
>     summary_contents  TEXT    NOT NULL
> ) INHERITS (asset.copy);
> 
> ALTER TABLE serial.unit ADD PRIMARY KEY (id);
> 
> Note that each has three text columns but the names are all different.  I 
> *think* what I need to do is:
> 
> a. Drop the "label" column;
> 
> b. Rename the "label_sort_key" column to "sort_key";
> 
> c. Rename the "contents" column to "detailed_contents";
> 
> d, Add a "summary_contents" column, initially nullable;
> 
> e. Copy the "detailed_contents" column to the "summary_contents" column;
> 
> f. Make the "summary_contents" column NOT NULL.
> 
> Later, someone will presumably massage the two contents columns to make them 
> appropriately different (assuming there's any data in the table in the first 
> place).
> 
> If I'm guessing wrong about serial.unit, please let me know.  I can do 
> anything reasonable but I need to know the rules.
> 
> I have not yet started looking at the necessary IDL changes.
> 
> Scott McKellar