From e047b415d433b41b42348f7093e2f9afab89f050 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 5 Mar 2013 09:20:43 -0500 Subject: [PATCH] Z39.50 Batch Search/Overlay : SQL / IDL Copied some missing pieces from the upgrade scripts into the seed schema / data files. Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/002.schema.config.sql | 35 ++++++++++++++++++++++ Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 1 + Open-ILS/src/sql/Pg/800.fkeys.sql | 1 + .../XXXX.schema.z39-batch-fetch-overlay.sql | 9 ++++-- .../upgrade/YYYY.schema.vandelay_bucket_match.sql | 3 +- 5 files changed, 46 insertions(+), 3 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 190ecfc3dd..d49e249694 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -1036,4 +1036,39 @@ ALTER TABLE config.best_hold_order ADD CHECK (( rtime IS NOT NULL )); +CREATE OR REPLACE FUNCTION + evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$ + SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1); +$func$ LANGUAGE SQL STRICT IMMUTABLE; + +COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$ +Results in TRUE if there exists at least one config.z3950_attr +with the provided name. Used by config.z3950_index_field_map +to verify z3950_attr_type maps. +$$; + +-- drop these in down here since they reference config.metabib_field +-- and config.record_attr_definition +CREATE TABLE config.z3950_index_field_map ( + id SERIAL PRIMARY KEY, + label TEXT NOT NULL, -- i18n + metabib_field INTEGER REFERENCES config.metabib_field(id), + record_attr TEXT REFERENCES config.record_attr_definition(name), + z3950_attr INTEGER REFERENCES config.z3950_attr(id), + z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name) + CONSTRAINT metabib_field_or_record_attr CHECK ( + metabib_field IS NOT NULL OR + record_attr IS NOT NULL + ), + CONSTRAINT attr_or_attr_type CHECK ( + z3950_attr IS NOT NULL OR + z3950_attr_type IS NOT NULL + ), + -- ensure the selected z3950_attr_type refers to a valid attr name + CONSTRAINT valid_z3950_attr_type CHECK ( + z3950_attr_type IS NULL OR + evergreen.z3950_attr_name_is_valid(z3950_attr_type) + ) +); + COMMIT; diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 34249d8b51..72fd2750a0 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -118,6 +118,7 @@ CREATE TYPE vandelay.bib_queue_queue_type AS ENUM ('bib', 'acq'); CREATE TABLE vandelay.bib_queue ( queue_type vandelay.bib_queue_queue_type NOT NULL DEFAULT 'bib', item_attr_def BIGINT REFERENCES vandelay.import_item_attr_definition (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, + match_bucket INTEGER, -- REFERENCES container.biblio_record_entry_bucket(id); CONSTRAINT vand_bib_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type) ) INHERITS (vandelay.queue); ALTER TABLE vandelay.bib_queue ADD PRIMARY KEY (id); diff --git a/Open-ILS/src/sql/Pg/800.fkeys.sql b/Open-ILS/src/sql/Pg/800.fkeys.sql index cf55a30e7c..efb37c5c43 100644 --- a/Open-ILS/src/sql/Pg/800.fkeys.sql +++ b/Open-ILS/src/sql/Pg/800.fkeys.sql @@ -91,6 +91,7 @@ ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_creator_fkey FOREIGN KEY (cre ALTER TABLE serial.unit ADD CONSTRAINT serial_unit_editor_fkey FOREIGN KEY (editor) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE vandelay.import_item ADD CONSTRAINT imported_as_fkey FOREIGN KEY (imported_as) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED; +ALTER TABLE vandelay.bib_queue ADD CONSTRAINT match_bucket_fkey FOREIGN KEY (match_bucket) REFERENCES container.biblio_record_entry_bucket(id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_copy_fkey FOREIGN KEY (owning_copy) REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED; ALTER TABLE asset.copy_note ADD CONSTRAINT asset_copy_note_creator_fkey FOREIGN KEY (creator) REFERENCES actor.usr (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql index 7bfaac3cde..44fff4af4b 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.z39-batch-fetch-overlay.sql @@ -3,10 +3,15 @@ BEGIN; -- TODO version check CREATE OR REPLACE FUNCTION - evergreen.z3950_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$ + evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$ SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1); $func$ LANGUAGE SQL STRICT IMMUTABLE; +COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$ +Results in TRUE if there exists at least one config.z3950_attr +with the provided name. Used by config.z3950_index_field_map +to verify z3950_attr_type maps. +$$; CREATE TABLE config.z3950_index_field_map ( id SERIAL PRIMARY KEY, @@ -26,7 +31,7 @@ CREATE TABLE config.z3950_index_field_map ( -- ensure the selected z3950_attr_type refers to a valid attr name CONSTRAINT valid_z3950_attr_type CHECK ( z3950_attr_type IS NULL OR - evergreen.z3950_name_is_valid(z3950_attr_type) + evergreen.z3950_attr_name_is_valid(z3950_attr_type) ) ); diff --git a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql index b4e3caaa30..ba2f79fb7a 100644 --- a/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql +++ b/Open-ILS/src/sql/Pg/upgrade/YYYY.schema.vandelay_bucket_match.sql @@ -3,7 +3,8 @@ BEGIN; -- TODO version check ALTER TABLE vandelay.bib_queue ADD COLUMN match_bucket - INTEGER REFERENCES container.biblio_record_entry_bucket(id); + INTEGER REFERENCES container.biblio_record_entry_bucket(id) + ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED; CREATE OR REPLACE FUNCTION vandelay.match_bib_record() RETURNS TRIGGER AS $func$ DECLARE -- 2.11.0