From e0114f18272c347795c21ac59e9c1e4520e2de65 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 14 Mar 2011 12:08:58 -0400 Subject: [PATCH] SQL schema cleanup --- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 26 +++++++++++++------------- Open-ILS/src/sql/Pg/950.data.seed-values.sql | 14 +++++++------- 2 files changed, 20 insertions(+), 20 deletions(-) diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index bdfff7a73e..84ca9bd8f8 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -24,9 +24,9 @@ CREATE TABLE vandelay.match_set_point ( required BOOL NOT NULL DEFAULT TRUE, quality INT NOT NULL DEFAULT 1, -- higher is better CONSTRAINT vmsp_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL), - CONSTRAINT vmsp_need_a_tag_or_a_ff CHECK (tag IS NOT NULL AND svf IS NULL) OR (tag IS NULL AND svf IS NOT NULL)), - CONSTRAINT vmsp_def_once_per_set UNIQUE (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,'')) + CONSTRAINT vmsp_need_a_tag_or_a_ff CHECK ((tag IS NOT NULL AND svf IS NULL) OR (tag IS NULL AND svf IS NOT NULL)) ); +CREATE UNIQUE INDEX vmsp_def_once_per_set ON vandelay.match_set_point (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,'')); CREATE TABLE vandelay.match_set_quality ( id SERIAL PRIMARY KEY, @@ -37,9 +37,9 @@ CREATE TABLE vandelay.match_set_quality ( value TEXT NOT NULL, quality INT NOT NULL DEFAULT 1, -- higher is better CONSTRAINT vmsq_need_a_subfield_with_a_tag CHECK ((tag IS NOT NULL AND subfield IS NOT NULL) OR tag IS NULL), - CONSTRAINT vmsq_need_a_tag_or_a_ff CHECK (tag IS NOT NULL AND svf IS NULL) OR (tag IS NULL AND svf IS NOT NULL)), - CONSTRAINT vmsq_def_once_per_set UNIQUE (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,'')) + CONSTRAINT vmsq_need_a_tag_or_a_ff CHECK ((tag IS NOT NULL AND svf IS NULL) OR (tag IS NULL AND svf IS NOT NULL)) ); +CREATE UNIQUE INDEX vmsq_def_once_per_set ON vandelay.match_set_quality (match_set, COALESCE(tag,''), COALESCE(subfield,''), COALESCE(svf,'')); CREATE TABLE vandelay.queue ( @@ -48,7 +48,7 @@ CREATE TABLE vandelay.queue ( name TEXT NOT NULL, complete BOOL NOT NULL DEFAULT FALSE, queue_type TEXT NOT NULL DEFAULT 'bib' CHECK (queue_type IN ('bib','authority')), - match_set INT REFERENCES vandelay.match_set (id) DEFERRABLE INITIALLY DEFERRED ON UPDATE CASCADE ON DELETE SET NULL, + match_set INT REFERENCES vandelay.match_set (id) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED, CONSTRAINT vand_queue_name_once_per_owner_const UNIQUE (owner,name,queue_type) ); @@ -122,7 +122,7 @@ CREATE TABLE vandelay.queued_bib_record ( queue INT NOT NULL REFERENCES vandelay.bib_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, bib_source INT REFERENCES config.bib_source (id) DEFERRABLE INITIALLY DEFERRED, imported_as BIGINT REFERENCES biblio.record_entry (id) DEFERRABLE INITIALLY DEFERRED, - import_error INT REFERENCES vandelay.import_error (id) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, + import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, error_detail TEXT ) INHERITS (vandelay.queued_record); ALTER TABLE vandelay.queued_bib_record ADD PRIMARY KEY (id); @@ -148,7 +148,7 @@ CREATE TABLE vandelay.import_item ( id BIGSERIAL PRIMARY KEY, record BIGINT NOT NULL REFERENCES vandelay.queued_bib_record (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, definition BIGINT NOT NULL REFERENCES vandelay.import_item_attr_definition (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, - import_error INT REFERENCES vandelay.import_error (id) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, + import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, error_detail TEXT, owning_lib INT, circ_lib INT, @@ -311,7 +311,7 @@ BEGIN END; $func$ LANGUAGE PLPGSQL; -CREAT TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT ); +CREATE TYPE vandelay.flat_marc AS ( tag CHAR(3), ind1 TEXT, ind2 TEXT, subfield TEXT, value TEXT ); CREATE OR REPLACE FUNCTION vandelay.flay_marc ( TEXT ) RETURNS SETOF vandelay.flat_marc AS $func$ use MARC::Record; @@ -515,9 +515,9 @@ BEGIN END IF; -- add the quality for this match - FOR tmp_rec IN SELECT * FROM UNNEST(potential_matches); + FOR tmp_rec IN SELECT * FROM UNNEST(potential_matches) LOOP tmp_quality := COALESCE((quality_set -> tmp_rec::TEXT)::INT, 0); - quality := quality || hstore(tmp_rec::TEXT, (tmp_quality + test.quality)::TEXT); + quality_set := quality_set || hstore(tmp_rec::TEXT, (tmp_quality + test.quality)::TEXT); END LOOP; END LOOP; @@ -537,9 +537,9 @@ BEGIN END IF; -- add the quality for this match - FOR tmp_rec IN SELECT * FROM UNNEST(potential_matches); + FOR tmp_rec IN SELECT * FROM UNNEST(potential_matches) LOOP tmp_quality := COALESCE((quality_set -> tmp_rec::TEXT)::INT, 0); - quality := quality || hstore(tmp_rec::TEXT, (tmp_quality + test.quality)::TEXT); + quality_set := quality_set || hstore(tmp_rec::TEXT, (tmp_quality + test.quality)::TEXT); END LOOP; END IF; @@ -1657,7 +1657,7 @@ ALTER TABLE vandelay.authority_queue ADD PRIMARY KEY (id); CREATE TABLE vandelay.queued_authority_record ( queue INT NOT NULL REFERENCES vandelay.authority_queue (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, imported_as INT REFERENCES authority.record_entry (id) DEFERRABLE INITIALLY DEFERRED, - import_error INT REFERENCES vandelay.import_error (id) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, + import_error TEXT REFERENCES vandelay.import_error (code) ON DELETE SET NULL ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED, error_detail TEXT ) INHERITS (vandelay.queued_record); ALTER TABLE vandelay.queued_authority_record ADD PRIMARY KEY (id); diff --git a/Open-ILS/src/sql/Pg/950.data.seed-values.sql b/Open-ILS/src/sql/Pg/950.data.seed-values.sql index fbe9877ea4..a010d07c26 100644 --- a/Open-ILS/src/sql/Pg/950.data.seed-values.sql +++ b/Open-ILS/src/sql/Pg/950.data.seed-values.sql @@ -3186,13 +3186,13 @@ INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (2, 'author', oils_i18n_gettext(2, 'Author of work', 'vqbrad', 'description'),'//*[@tag="100" or @tag="110" or @tag="113"]/*[contains("ad",@code)]'); INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (3, 'language', oils_i18n_gettext(3, 'Language of work', 'vqbrad', 'description'),'//*[@tag="240"]/*[@code="l"][1]'); INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (4, 'pagination', oils_i18n_gettext(4, 'Pagination', 'vqbrad', 'description'),'//*[@tag="300"]/*[@code="a"][1]'); -INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident, remove ) VALUES (5, 'isbn',oils_i18n_gettext(5, 'ISBN', 'vqbrad', 'description'),'//*[@tag="020"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$); -INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident, remove ) VALUES (6, 'issn',oils_i18n_gettext(6, 'ISSN', 'vqbrad', 'description'),'//*[@tag="022"]/*[@code="a"]', TRUE, $r$(?:-|\s.+$)$r$); +INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, remove ) VALUES (5, 'isbn',oils_i18n_gettext(5, 'ISBN', 'vqbrad', 'description'),'//*[@tag="020"]/*[@code="a"]', $r$(?:-|\s.+$)$r$); +INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, remove ) VALUES (6, 'issn',oils_i18n_gettext(6, 'ISSN', 'vqbrad', 'description'),'//*[@tag="022"]/*[@code="a"]', $r$(?:-|\s.+$)$r$); INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (7, 'price',oils_i18n_gettext(7, 'Price', 'vqbrad', 'description'),'//*[@tag="020" or @tag="022"]/*[@code="c"][1]'); -INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (8, 'rec_identifier',oils_i18n_gettext(8, 'Accession Number', 'vqbrad', 'description'),'//*[@tag="001"]', TRUE); -INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (9, 'eg_tcn',oils_i18n_gettext(9, 'TCN Value', 'vqbrad', 'description'),'//*[@tag="901"]/*[@code="a"]', TRUE); -INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (10, 'eg_tcn_source',oils_i18n_gettext(10, 'TCN Source', 'vqbrad', 'description'),'//*[@tag="901"]/*[@code="b"]', TRUE); -INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, ident ) VALUES (11, 'eg_identifier',oils_i18n_gettext(11, 'Internal ID', 'vqbrad', 'description'),'//*[@tag="901"]/*[@code="c"]', TRUE); +INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath) VALUES (8, 'rec_identifier',oils_i18n_gettext(8, 'Accession Number', 'vqbrad', 'description'),'//*[@tag="001"]'); +INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath) VALUES (9, 'eg_tcn',oils_i18n_gettext(9, 'TCN Value', 'vqbrad', 'description'),'//*[@tag="901"]/*[@code="a"]'); +INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath) VALUES (10, 'eg_tcn_source',oils_i18n_gettext(10, 'TCN Source', 'vqbrad', 'description'),'//*[@tag="901"]/*[@code="b"]'); +INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath) VALUES (11, 'eg_identifier',oils_i18n_gettext(11, 'Internal ID', 'vqbrad', 'description'),'//*[@tag="901"]/*[@code="c"]'); INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (12, 'publisher',oils_i18n_gettext(12, 'Publisher', 'vqbrad', 'description'),'//*[@tag="260"]/*[@code="b"][1]'); INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath, remove ) VALUES (13, 'pubdate',oils_i18n_gettext(13, 'Publication Date', 'vqbrad', 'description'),'//*[@tag="260"]/*[@code="c"][1]',$r$\D$r$); INSERT INTO vandelay.bib_attr_definition ( id, code, description, xpath ) VALUES (14, 'edition',oils_i18n_gettext(14, 'Edition', 'vqbrad', 'description'),'//*[@tag="250"]/*[@code="a"][1]'); @@ -3246,7 +3246,7 @@ INSERT INTO vandelay.import_item_attr_definition ( 'k' ); -INSERT INTO vandelay.authority_attr_definition (id, code, description, xpath, ident ) VALUES (1, 'rec_identifier',oils_i18n_gettext(1, 'Identifier', 'vqarad', 'description'),'//*[@tag="001"]', TRUE); +INSERT INTO vandelay.authority_attr_definition (id, code, description, xpath) VALUES (1, 'rec_identifier',oils_i18n_gettext(1, 'Identifier', 'vqarad', 'description'),'//*[@tag="001"]'); SELECT SETVAL('vandelay.authority_attr_definition_id_seq'::TEXT, 100); -- 2.11.0