[OPEN-ILS-DEV] ***SPAM*** Proposed serial schema (attn: dbwells)

[Scott McKellar]

As you are aware if you have been following the discussion on IRC, we're
about to overhaul the serial schema.

The attachment is a draft of an upgrade script for the new version of the
schema.  Corresponding changes will be applied to the installation scripts
and to the IDL.

WARNING: the upgrade script DROPs all the tables in the schema, except for
serial.record_entry, before replacing them with new CREATE statements.  Any
data in the old tables will be lost if you make no provision to save it
first.  I had hoped to come up with a conversion script to preserve
existing data, but it turned out to be infeasible because the new tables
require information not 
available from the old ones.

---------------------

I started with the proposal prepared by Dan Wells, as it existed some time
on Sunday, at:

http://open-ils.org/dokuwiki/doku.php?id=acq:serials:basic_predicting_and_receiving

...and changed it in various mostly minor ways as described below.  These
changes have been discussed internally within Equinox, but not all of them
within the wider community till now.  I won't address the rationale for
these changes in detail here, but of course everything is open for
discussion.


1. I rearranged the white space to suit my own impeccable good taste. 
Mainly I broke up long lines into shorter ones so that I wouldn't have to
stretch my editor window or wrap around.

2. I removed all instances of DEFAULT NULL as unnecessary, since NULL is
the default default.

3. I moved the creation of serial.unit above the creation of serial.item so
that the latter can link to the former without a separate ALTER TABLE.

4. In asset.copy: I fixed the CHECK constraint on the fine_level column (it
was inadvertently checking loan_duration due to an apparent editing
glitch).  Also: I declared names for both CHECK constraints. 

5. In asset.copy_template: I declared foreign key constraints for the
status and location columns, pointing to config.copy_status and
asset.copy_location, respectively.  In this respect I followed the
precedent of asset.copy.  However unlike the situation in asset.copy, these
columns are nullable, and have no default values.

6. In serial.distribution: holding_lib is now NOT NULL, and the ON DELETE
clause is gone.

7. In serial.issuance: I changed the CHECK constraint to add a separate
test for nullity (since testing for NULL in an IN list doesn't work).  I
also declared a name for the constraint.

8. In serial.stream: I added a nullable text column, routing_label, to
identify a routing list.  This column is unique for a given distribution.

9. I added a table serial.routing_list_user, linking to serial.stream, to
define the recipients on a routing list (either people or departments).

10. In serial.unit: I added a PRIMARY KEY declaration so that the id column
(inherited from asset.copy) could serve as the object of a foreign key.

11. In serial.item: for the link to serial.unit, I changed the ON DELETE
clause from CASCADE to SET NULL.


---------------------

What I *haven't* done yet:

1. I haven't defined any indexes for performances, because I don't have
sound instincts for where the bottlenecks will be.  I defined one index
explicitly in order to implement a uniqueness constraint, and various other
indexes implicitly.

2. I haven't touched the stored procedures for purging and merging users,
to reflect the presence of various links to actor.usr.  Those changes can
wait for the dust to settle.

3. Most importantly, I haven't committed any of this yet, pending further
discussion.  However I hope to commit it within a day or two so that people
can start working with this schema.

---------------------

Unresolved issues:

1. Should serial.subscription.end_date be nullable?  There has been some
debate about what a null end_date would mean, or whether it should be
allowed, and I don't know if that issue has been resolved.

2. In serial.issuance: a comment, inherited from the wiki, says: "TODO: add
columns for separate enumeration/chronology values."  Do we still think we
need those?  Do we know what they should look like?  We can always add them
later, but I'd rather include them now if we can.

3. In the script, the DROP TABLE statements come before the beginning of
any transaction.  That way if a DROP fails because a table doesn't exist,
then the rest of the script can still run.  Alternatively I can include
them within the transaction so that a rollback will preserve the old
tables.  My feeling is that, if you're not ready to drop those tables, then
you shouldn't be running the script anyway.


Scott McKellar
-------------- next part --------------
A non-text attachment was scrubbed...
Name: serial.sql
Type: application/octet-stream
Size: 9175 bytes
Desc: not available
Url : http://libmail.georgialibraries.org/pipermail/open-ils-dev/attachments/20100517/2dd70e2b/attachment.obj