From 252ac3453011b57a797f5a162ba98cbcf0280724 Mon Sep 17 00:00:00 2001 From: scottmk Date: Tue, 27 Jul 2010 17:27:01 +0000 Subject: [PATCH] Merge Dan Wells' changes to the serial schema from the seials-integration branch. M Open-ILS/src/sql/Pg/002.schema.config.sql A Open-ILS/src/sql/Pg/upgrade/0352.schema.serials-integration.sql M Open-ILS/src/sql/Pg/210.schema.serials.sql M Open-ILS/examples/fm_IDL.xml git-svn-id: svn://svn.open-ils.org/ILS/trunk@17039 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 273 ++++++++++++------ Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/210.schema.serials.sql | 55 +++- .../0352.schema.serials-integration.sql | 80 +++++ 4 files changed, 314 insertions(+), 96 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0352.schema.serials-integration.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 00d2ca5c6b..6b64b3f47a 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -3007,19 +3007,20 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + - + - + - - - + + + @@ -3038,12 +3039,12 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + - + @@ -3059,7 +3060,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3073,17 +3074,37 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + + - - - + + + + + + + + + + + + + + + + + + + + + + @@ -3097,12 +3118,13 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + - - - + + + + @@ -3112,12 +3134,43 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + @@ -3126,11 +3179,9 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - - @@ -3153,7 +3204,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + @@ -3161,83 +3212,95 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + - - + + + + + + + + + - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + - - - - - - - - - - - - - - - + + + + + + + + + + + + + + + + + + + + @@ -3254,7 +3317,9 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + @@ -3263,9 +3328,15 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA - + + + + + + + @@ -3284,11 +3355,17 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + - - + + @@ -3297,9 +3374,17 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + - + @@ -3309,9 +3394,17 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + - + @@ -3321,6 +3414,14 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + + + + + diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index d656461344..d0e0bd183f 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -68,7 +68,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0351'); -- Scott McKellar +INSERT INTO config.upgrade_log (version) VALUES ('0352'); -- Scott McKellar CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/210.schema.serials.sql b/Open-ILS/src/sql/Pg/210.schema.serials.sql index 2235c36369..f1638e803c 100644 --- a/Open-ILS/src/sql/Pg/210.schema.serials.sql +++ b/Open-ILS/src/sql/Pg/210.schema.serials.sql @@ -30,7 +30,7 @@ CREATE RULE protect_mfhd_delete AS ON DELETE TO serial.record_entry DO INSTEAD U CREATE TABLE serial.subscription ( id SERIAL PRIMARY KEY, - owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + owning_lib INT NOT NULL DEFAULT 1 REFERENCES actor.org_unit (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, 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) @@ -40,6 +40,20 @@ CREATE TABLE serial.subscription ( -- acquisitions/business-side tables link to here ); +CREATE TABLE serial.subscription_note ( + id SERIAL PRIMARY KEY, + subscription INT NOT NULL + REFERENCES serial.subscription (id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + creator INT NOT NULL + REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + pub BOOL NOT NULL DEFAULT FALSE, + title TEXT NOT NULL, + value TEXT NOT NULL +); CREATE TABLE serial.caption_and_pattern ( id SERIAL PRIMARY KEY, @@ -50,7 +64,7 @@ CREATE TABLE serial.caption_and_pattern ( type TEXT NOT NULL CONSTRAINT cap_type CHECK ( type in ( 'basic', 'supplement', 'index' )), - create_time TIMESTAMPTZ NOT NULL DEFAULT now(), + create_date TIMESTAMPTZ NOT NULL DEFAULT now(), active BOOL NOT NULL DEFAULT FALSE, pattern_code TEXT NOT NULL, -- must contain JSON enum_1 TEXT, @@ -88,11 +102,26 @@ CREATE TABLE serial.distribution ( DEFERRABLE INITIALLY DEFERRED, bind_unit_template INT REFERENCES asset.copy_template (id) DEFERRABLE INITIALLY DEFERRED, - unit_label_base TEXT, + unit_label_prefix TEXT, unit_label_suffix TEXT ); CREATE UNIQUE INDEX one_dist_per_sre_idx ON serial.distribution (record_entry); +CREATE TABLE serial.distribution_note ( + id SERIAL PRIMARY KEY, + distribution INT NOT NULL + REFERENCES serial.distribution (id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + creator INT NOT NULL + REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + pub BOOL NOT NULL DEFAULT FALSE, + title TEXT NOT NULL, + value TEXT NOT NULL +); + CREATE TABLE serial.stream ( id SERIAL PRIMARY KEY, distribution INT NOT NULL @@ -143,20 +172,22 @@ CREATE TABLE serial.issuance ( DEFERRABLE INITIALLY DEFERRED, label TEXT, date_published TIMESTAMP WITH TIME ZONE, + caption_and_pattern INT REFERENCES serial.caption_and_pattern (id) + DEFERRABLE INITIALLY DEFERRED, holding_code TEXT, holding_type TEXT CONSTRAINT valid_holding_type CHECK ( holding_type IS NULL OR holding_type IN ('basic','supplement','index') ), - holding_link_id INT + holding_link_id INT -- probably defunct -- TODO: add columns for separate enumeration/chronology values ); CREATE TABLE serial.unit ( - label TEXT, - label_sort_key TEXT, - contents TEXT NOT NULL + sort_key TEXT, + detailed_contents TEXT NOT NULL, + summary_contents TEXT NOT NULL ) INHERITS (asset.copy); ALTER TABLE serial.unit ADD PRIMARY KEY (id); @@ -190,6 +221,11 @@ CREATE TABLE serial.item ( DEFERRABLE INITIALLY DEFERRED, date_expected TIMESTAMP WITH TIME ZONE, date_received TIMESTAMP WITH TIME ZONE + status TEXT CONSTRAINT valid_status CHECK + ( + status IN ('Bindery', 'Bound', 'Claimed', 'Discarded', 'Expected', 'Not Held', 'Not Published', 'Received') + ) DEFAULT 'Expected', + shadowed BOOL NOT NULL DEFAULT FALSE -- ignore when generating summaries/labels ); CREATE TABLE serial.item_note ( @@ -207,7 +243,7 @@ CREATE TABLE serial.item_note ( value TEXT NOT NULL ); -CREATE TABLE serial.bib_summary ( +CREATE TABLE serial.basic_summary ( id SERIAL PRIMARY KEY, distribution INT NOT NULL REFERENCES serial.distribution (id) @@ -217,7 +253,7 @@ CREATE TABLE serial.bib_summary ( textual_holdings TEXT ); -CREATE TABLE serial.sup_summary ( +CREATE TABLE serial.supplement_summary ( id SERIAL PRIMARY KEY, distribution INT NOT NULL REFERENCES serial.distribution (id) @@ -238,3 +274,4 @@ CREATE TABLE serial.index_summary ( ); COMMIT; + diff --git a/Open-ILS/src/sql/Pg/upgrade/0352.schema.serials-integration.sql b/Open-ILS/src/sql/Pg/upgrade/0352.schema.serials-integration.sql new file mode 100644 index 0000000000..5f6c0428b4 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0352.schema.serials-integration.sql @@ -0,0 +1,80 @@ +-- Apply Dan Wells' changes to the serial schema, from the +-- seials-integration branch + +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0352'); -- Scott McKellar + +CREATE TABLE serial.subscription_note ( + id SERIAL PRIMARY KEY, + subscription INT NOT NULL + REFERENCES serial.subscription (id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + creator INT NOT NULL + REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + pub BOOL NOT NULL DEFAULT FALSE, + title TEXT NOT NULL, + value TEXT NOT NULL +); + +ALTER TABLE serial.caption_and_pattern +RENAME COLUMN create_time TO create_date; + +ALTER TABLE serial.distribution +RENAME COLUMN unit_label_base TO unit_label_prefix; + +CREATE TABLE serial.distribution_note ( + id SERIAL PRIMARY KEY, + distribution INT NOT NULL + REFERENCES serial.distribution (id) + ON DELETE CASCADE + DEFERRABLE INITIALLY DEFERRED, + creator INT NOT NULL + REFERENCES actor.usr (id) + DEFERRABLE INITIALLY DEFERRED, + create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(), + pub BOOL NOT NULL DEFAULT FALSE, + title TEXT NOT NULL, + value TEXT NOT NULL +); + +ALTER TABLE serial.issuance +ADD COLUMN caption_and_pattern INT REFERENCES serial.caption_and_pattern (id) + DEFERRABLE INITIALLY DEFERRED; +------- Begin surgery on serial.unit + +ALTER TABLE serial.unit + DROP COLUMN label; + +ALTER TABLE serial.unit + RENAME COLUMN label_sort_key TO sort_key; + +ALTER TABLE serial.unit + RENAME COLUMN contents TO detailed_contents; + +ALTER TABLE serial.unit + ADD COLUMN summary_contents TEXT; + +UPDATE serial.unit +SET summary_contents = detailed_contents; + +ALTER TABLE serial.unit + ALTER column summary_contents SET NOT NULL; + +------- End surgery on serial.unit + +ALTER TABLE serial.item +ADD COLUMN status TEXT CONSTRAINT value_status_check CHECK ( + status IN ( 'Bindery', 'Bound', 'Claimed', 'Discarded', + 'Expected', 'Not Held', 'Not Published', 'Received')) + DEFAULT 'Expected', +ADD COLUMN shadowed BOOL NOT NULL DEFAULT FALSE; + +ALTER TABLE serial.bib_summary RENAME TO basic_summary; + +ALTER TABLE serial.sup_summary RENAME TO supplement_summary; + +COMMIT; -- 2.43.2