From 1649d0550c1ebdc21fce7975687fec4717ab8ab5 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Mon, 16 Mar 2015 20:47:43 +0000 Subject: [PATCH] LP#1427287: (erratum) create Pg enum type to represent MARC record type Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/002.schema.config.sql | 22 +++++++++---------- .../sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql | 25 +++++++++++----------- 2 files changed, 23 insertions(+), 24 deletions(-) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 64c525a139..a8c86c7e55 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -1094,13 +1094,13 @@ support of UNIMARC, though whether that will ever happen remains to be seen. $$; +CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial'); + CREATE TABLE config.marc_field ( id SERIAL PRIMARY KEY, marc_format INTEGER NOT NULL REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED, - marc_record_type TEXT NOT NULL CHECK ( - marc_record_type IN ('biblio', 'authority', 'serial') - ), + marc_record_type config.marc_record_type NOT NULL, tag CHAR(3) NOT NULL, name TEXT, description TEXT, @@ -1147,9 +1147,7 @@ CREATE TABLE config.marc_subfield ( id SERIAL PRIMARY KEY, marc_format INTEGER NOT NULL REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED, - marc_record_type TEXT NOT NULL CHECK ( - marc_record_type IN ('biblio', 'authority', 'serial') - ), + marc_record_type config.marc_record_type NOT NULL, tag CHAR(3) NOT NULL, code CHAR(1) NOT NULL, description TEXT, @@ -1197,8 +1195,8 @@ WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag, -- as well as any purely local ones that have been added SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0 FROM config.marc_field - WHERE ARRAY[marc_format::TEXT, marc_record_type, tag] NOT IN ( - SELECT ARRAY[marc_format::TEXT, marc_record_type, tag] + WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] NOT IN ( + SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] FROM config.marc_field WHERE owner IS NULL ) @@ -1239,8 +1237,8 @@ WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code, -- as well as any purely local ones that have been added SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory, hidden, owner, 0 FROM config.marc_subfield - WHERE ARRAY[marc_format::TEXT, marc_record_type, tag, code] NOT IN ( - SELECT ARRAY[marc_format::TEXT, marc_record_type, tag, code] + WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] NOT IN ( + SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] FROM config.marc_subfield WHERE owner IS NULL ) @@ -1265,7 +1263,7 @@ SELECT id, marc_format, marc_record_type, tag, code, mandatory, hidden, owner, depth FROM ou_marc_subfields; -CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_record_type TEXT, ou INTEGER) RETURNS SETOF config.marc_field AS $func$ +CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_record_type config.marc_record_type, ou INTEGER) RETURNS SETOF config.marc_field AS $func$ SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner FROM ( SELECT id, marc_format, marc_record_type, tag, name, description, @@ -1281,7 +1279,7 @@ CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_recor AND not hidden; $func$ LANGUAGE SQL; -CREATE OR REPLACE FUNCTION config.ou_marc_subfields(marc_format INTEGER, marc_record_type TEXT, ou INTEGER) RETURNS SETOF config.marc_subfield AS $func$ +CREATE OR REPLACE FUNCTION config.ou_marc_subfields(marc_format INTEGER, marc_record_type config.marc_record_type, ou INTEGER) RETURNS SETOF config.marc_subfield AS $func$ SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory, hidden, owner FROM ( SELECT id, marc_format, marc_record_type, tag, code, description, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql index d283070c77..30bbbd85d5 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql @@ -3,8 +3,11 @@ BEGIN; -- FIXME: the following drop commands are present purely as -- a convenience for development; they should be removed -- before committing to master. +DROP TYPE IF EXISTS config.marc_record_type CASCADE; DROP FUNCTION IF EXISTS config.ou_marc_fields(INTEGER, TEXT, INTEGER); DROP FUNCTION IF EXISTS config.ou_marc_subfields(INTEGER, TEXT, INTEGER); +DROP FUNCTION IF EXISTS config.ou_marc_fields(INTEGER, config.marc_record_type, INTEGER); +DROP FUNCTION IF EXISTS config.ou_marc_subfields(INTEGER, config.marc_record_type, INTEGER); DROP VIEW IF EXISTS config.marc_field_for_ou; DROP VIEW IF EXISTS config.marc_subfield_for_ou; DROP TABLE IF EXISTS config.marc_subfield; @@ -23,13 +26,13 @@ support of UNIMARC, though whether that will ever happen remains to be seen. $$; +CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial'); + CREATE TABLE config.marc_field ( id SERIAL PRIMARY KEY, marc_format INTEGER NOT NULL REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED, - marc_record_type TEXT NOT NULL CHECK ( - marc_record_type IN ('biblio', 'authority', 'serial') - ), + marc_record_type config.marc_record_type NOT NULL, tag CHAR(3) NOT NULL, name TEXT, description TEXT, @@ -76,9 +79,7 @@ CREATE TABLE config.marc_subfield ( id SERIAL PRIMARY KEY, marc_format INTEGER NOT NULL REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED, - marc_record_type TEXT NOT NULL CHECK ( - marc_record_type IN ('biblio', 'authority', 'serial') - ), + marc_record_type config.marc_record_type NOT NULL, tag CHAR(3) NOT NULL, code CHAR(1) NOT NULL, description TEXT, @@ -126,8 +127,8 @@ WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag, -- as well as any purely local ones that have been added SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0 FROM config.marc_field - WHERE ARRAY[marc_format::TEXT, marc_record_type, tag] NOT IN ( - SELECT ARRAY[marc_format::TEXT, marc_record_type, tag] + WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] NOT IN ( + SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] FROM config.marc_field WHERE owner IS NULL ) @@ -168,8 +169,8 @@ WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code, -- as well as any purely local ones that have been added SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory, hidden, owner, 0 FROM config.marc_subfield - WHERE ARRAY[marc_format::TEXT, marc_record_type, tag, code] NOT IN ( - SELECT ARRAY[marc_format::TEXT, marc_record_type, tag, code] + WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] NOT IN ( + SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] FROM config.marc_subfield WHERE owner IS NULL ) @@ -194,7 +195,7 @@ SELECT id, marc_format, marc_record_type, tag, code, mandatory, hidden, owner, depth FROM ou_marc_subfields; -CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_record_type TEXT, ou INTEGER) RETURNS SETOF config.marc_field AS $func$ +CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_record_type config.marc_record_type, ou INTEGER) RETURNS SETOF config.marc_field AS $func$ SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner FROM ( SELECT id, marc_format, marc_record_type, tag, name, description, @@ -210,7 +211,7 @@ CREATE OR REPLACE FUNCTION config.ou_marc_fields(marc_format INTEGER, marc_recor AND not hidden; $func$ LANGUAGE SQL; -CREATE OR REPLACE FUNCTION config.ou_marc_subfields(marc_format INTEGER, marc_record_type TEXT, ou INTEGER) RETURNS SETOF config.marc_subfield AS $func$ +CREATE OR REPLACE FUNCTION config.ou_marc_subfields(marc_format INTEGER, marc_record_type config.marc_record_type, ou INTEGER) RETURNS SETOF config.marc_subfield AS $func$ SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory, hidden, owner FROM ( SELECT id, marc_format, marc_record_type, tag, code, description, -- 2.11.0