From 4c674a6811833670e76d48c7587561736126f401 Mon Sep 17 00:00:00 2001 From: miker Date: Fri, 11 Jun 2010 21:20:39 +0000 Subject: [PATCH] Serials schema adjustments designed by Dan Wells. Essential features are moving subscription to attach directly to BRE, as the controlled peer to SRE, and allowing distributions to optionally link to an SRE in order to extend them. git-svn-id: svn://svn.open-ils.org/ILS/trunk@16690 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 34 ++++++++++--------- Open-ILS/src/sql/Pg/210.schema.serials.sql | 31 ++++++++++------- .../0307.schema.serials-rearrangement.sql | 12 +++++++ 3 files changed, 48 insertions(+), 29 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0307.schema.serials-rearrangement.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index ff06baf468..6989048bb6 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3016,7 +3016,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3034,7 +3034,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3045,17 +3045,18 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + - + - + + @@ -3067,6 +3068,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + @@ -3078,14 +3080,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + - + @@ -3101,7 +3103,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3129,7 +3131,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3192,7 +3194,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3210,8 +3212,8 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - + + @@ -3229,7 +3231,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3244,7 +3246,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3256,7 +3258,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3268,7 +3270,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + diff --git a/Open-ILS/src/sql/Pg/210.schema.serials.sql b/Open-ILS/src/sql/Pg/210.schema.serials.sql index 29663bd913..c320491244 100644 --- a/Open-ILS/src/sql/Pg/210.schema.serials.sql +++ b/Open-ILS/src/sql/Pg/210.schema.serials.sql @@ -26,10 +26,22 @@ CREATE INDEX serial_record_entry_owning_lib_idx ON serial.record_entry ( owning_ CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD UPDATE serial.record_entry SET deleted = true WHERE old.id = serial.record_entry.id; +CREATE TABLE serial.subscription ( + id SERIAL PRIMARY KEY, + start_date TIMESTAMP WITH TIME ZONE NOT NULL, + end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription + record_entry BIGINT REFERENCES biblio.record_entry (id) + ON DELETE SET NULL + DEFERRABLE INITIALLY DEFERRED, + expected_date_offset INTERVAL + -- acquisitions/business-side tables link to here +); + + CREATE TABLE serial.caption_and_pattern ( id SERIAL PRIMARY KEY, - record BIGINT NOT NULL - REFERENCES serial.record_entry (id) + subscription INT NOT NULL + REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, type TEXT NOT NULL @@ -51,20 +63,12 @@ CREATE TABLE serial.caption_and_pattern ( chron_5 TEXT ); -CREATE TABLE serial.subscription ( - id SERIAL PRIMARY KEY, - start_date TIMESTAMP WITH TIME ZONE NOT NULL, - end_date TIMESTAMP WITH TIME ZONE, -- interpret NULL as current subscription - record_entry BIGINT REFERENCES serial.record_entry (id) - ON DELETE SET NULL - DEFERRABLE INITIALLY DEFERRED, - expected_date_offset INTERVAL - -- acquisitions/business-side tables link to here -); - --at least one distribution per org_unit holding issues CREATE TABLE serial.distribution ( id SERIAL PRIMARY KEY, + record_entry BIGINT REFERENCES serial.record_entry (id) + ON DELETE SET NULL + DEFERRABLE INITIALLY DEFERRED, subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE @@ -84,6 +88,7 @@ CREATE TABLE serial.distribution ( unit_label_base TEXT, unit_label_suffix TEXT ); +CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry); CREATE TABLE serial.stream ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/0307.schema.serials-rearrangement.sql b/Open-ILS/src/sql/Pg/upgrade/0307.schema.serials-rearrangement.sql new file mode 100644 index 0000000000..394f81687c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0307.schema.serials-rearrangement.sql @@ -0,0 +1,12 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0307'); --miker + +ALTER TABLE serial.caption_and_pattern DROP COLUMN record; +ALTER TABLE serial.caption_and_pattern ADD COLUMN subscription INT NOT NULL REFERENCES serial.subscription (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE serial.distribution ADD COLUMN record_entry INT REFERENCES serial.record_entry (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; +CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry); + + +COMMIT; + -- 2.43.2