From 81d2b2a8bb99cbce38eed58d7d88978b5cef98cd Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Mon, 2 Mar 2015 17:14:30 +0000 Subject: [PATCH] LP#1427287: database schema for storing MARC tag+subfield metadata This patch defines several tables, views, and functions for storing information about MARC tags and subfields recognized by Evergreen: Tables: * config.marc_format: list of MARC formats; at present, however, only MARC21 is supported * config.marc_field: list of fields recognized by the Evergreen database. * config.marc_subfield: list of subfields recognized by Evergreen Views: * config.marc_field_for_ou * config.marc_subfield_for_ou These two views assist in the process of determining the set of MARC fields and subfields are meant to be in force at a particular OU. These are generally not meant to be queried directly, in favor of the config.ou_marc_{sub}fields() functions. Functions: * config.ou_marc_fields(marc_format, marc_record_type, ou) * config.ou_marc_subfields(marc_format, marc_record_type, ou) These functions return the authoritative set of fields or subfields in force at a given OU. Signed-off-by: Galen Charlton --- Open-ILS/src/sql/Pg/002.schema.config.sql | 215 +++++++++++++++++++ .../sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql | 229 +++++++++++++++++++++ 2 files changed, 444 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index fe2ceebecc..64c525a139 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -1082,4 +1082,219 @@ CREATE TABLE config.z3950_index_field_map ( ) ); +CREATE TABLE config.marc_format ( + id SERIAL PRIMARY KEY, + code TEXT NOT NULL, + name TEXT NOT NULL +); +COMMENT ON TABLE config.marc_format IS $$ +List of MARC formats supported by this Evergreen +database. This exists primarily as a hook for future +support of UNIMARC, though whether that will ever +happen remains to be seen. +$$; + +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') + ), + tag CHAR(3) NOT NULL, + name TEXT, + description TEXT, + fixed_field BOOLEAN, + repeatable BOOLEAN, + mandatory BOOLEAN, + hidden BOOLEAN, + owner INTEGER REFERENCES actor.org_unit (id) + -- if the owner is null, the data about the field is + -- assumed to come from the controlling MARC standard +); + +COMMENT ON TABLE config.marc_field IS $$ +This table stores a list of MARC fields recognized by the Evergreen +instance. Note that we're not aiming for completely generic ISO2709 +support: we're assuming things like three characters for a tag, +one-character subfield labels, two indicators per variable data field, +and the like, all of which are technically specializations of ISO2709. + +Of particular significance is the owner column; if it's set to a null +value, the field definition is assumed to come from a national +standards body; if it's set to a non-null value, the field definition +is an OU-level addition to or override of the standard. +$$; + +CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag); +CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner); + +CREATE UNIQUE INDEX config_standard_marc_tags_are_unique + ON config.marc_field(marc_format, marc_record_type, tag) + WHERE owner IS NULL; +ALTER TABLE config.marc_field + ADD CONSTRAINT config_standard_marc_tags_are_fully_specified + CHECK ((owner IS NOT NULL) OR + ( + owner IS NULL AND + repeatable IS NOT NULL AND + mandatory IS NOT NULL AND + hidden IS NOT NULL + ) + ); + +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') + ), + tag CHAR(3) NOT NULL, + code CHAR(1) NOT NULL, + description TEXT, + repeatable BOOLEAN, + mandatory BOOLEAN, + hidden BOOLEAN, + owner INTEGER REFERENCES actor.org_unit (id) + -- if the owner is null, the data about the subfield is + -- assumed to come from the controlling MARC standard +); + +COMMENT ON TABLE config.marc_subfield IS $$ +This table stores the list of subfields recognized by this Evergreen +instance. As with config.marc_field, of particular significance is the +owner column; if it's set to a null value, the subfield definition is +assumed to come from a national standards body; if it's set to a non-null +value, the subfield definition is an OU-level addition to or override +of the standard. +$$; + +CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code); +CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique + ON config.marc_subfield(marc_format, marc_record_type, tag, code) + WHERE owner IS NULL; +ALTER TABLE config.marc_subfield + ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified + CHECK ((owner IS NOT NULL) OR + ( + owner IS NULL AND + repeatable IS NOT NULL AND + mandatory IS NOT NULL AND + hidden IS NOT NULL + ) + ); + +CREATE OR REPLACE VIEW config.marc_field_for_ou AS +WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag, + name, description, fixed_field, repeatable, + mandatory, hidden, owner, depth) AS ( + -- start with all MARC fields defined by the controlling national standard + SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0 + FROM config.marc_field + WHERE owner IS NULL + UNION + -- 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] + FROM config.marc_field + WHERE owner IS NULL + ) + UNION + -- and start walking down the org unit hierarchy, + -- letting entries for child OUs override field names, + -- descriptions, repeatability, and the like. Letting + -- fixed-fieldness be overridable is something that falls + -- from the implementation, but is unlikely to be useful + SELECT c.id, marc_format, marc_record_type, tag, + COALESCE(c.name, p.name), + COALESCE(c.description, p.description), + COALESCE(c.fixed_field, p.fixed_field), + COALESCE(c.repeatable, p.repeatable), + COALESCE(c.mandatory, p.mandatory), + COALESCE(c.hidden, p.hidden), + c.owner, + depth + 1 + FROM config.marc_field c + JOIN ou_marc_fields p USING (marc_format, marc_record_type, tag) + JOIN actor.org_unit aou ON (c.owner = aou.id) + WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL)) +) +SELECT id, marc_format, marc_record_type, tag, + name, description, fixed_field, repeatable, + mandatory, hidden, owner, depth +FROM ou_marc_fields; + +CREATE OR REPLACE VIEW config.marc_subfield_for_ou AS +WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code, + description, repeatable, + mandatory, hidden, owner, depth) AS ( + -- start with all MARC subfields defined by the controlling national standard + SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory, hidden, owner, 0 + FROM config.marc_subfield + WHERE owner IS NULL + UNION + -- 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] + FROM config.marc_subfield + WHERE owner IS NULL + ) + UNION + -- and start walking down the org unit hierarchy, + -- letting entries for child OUs override subfield + -- descriptions, repeatability, and the like. + SELECT c.id, marc_format, marc_record_type, tag, code, + COALESCE(c.description, p.description), + COALESCE(c.repeatable, p.repeatable), + COALESCE(c.mandatory, p.mandatory), + COALESCE(c.hidden, p.hidden), + c.owner, + depth + 1 + FROM config.marc_subfield c + JOIN ou_marc_subfields p USING (marc_format, marc_record_type, tag, code) + JOIN actor.org_unit aou ON (c.owner = aou.id) + WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL)) +) +SELECT id, marc_format, marc_record_type, tag, code, + description, repeatable, + 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$ + 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, + fixed_field, repeatable, mandatory, hidden, owner, depth, + MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag) AS winner + FROM config.marc_field_for_ou + WHERE (owner IS NULL + OR owner IN (SELECT id FROM actor.org_unit_ancestors($3))) + AND marc_format = $1 + AND marc_record_type = $2 + ) AS s + WHERE depth = winner + 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$ + 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, + repeatable, mandatory, hidden, owner, depth, + MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag, code) AS winner + FROM config.marc_subfield_for_ou + WHERE (owner IS NULL + OR owner IN (SELECT id FROM actor.org_unit_ancestors($3))) + AND marc_format = $1 + AND marc_record_type = $2 + ) AS s + WHERE depth = winner + AND not hidden; +$func$ LANGUAGE SQL; + COMMIT; 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 new file mode 100644 index 0000000000..d283070c77 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.marc-tag-tables.sql @@ -0,0 +1,229 @@ +BEGIN; + +-- FIXME: the following drop commands are present purely as +-- a convenience for development; they should be removed +-- before committing to master. +DROP FUNCTION IF EXISTS config.ou_marc_fields(INTEGER, TEXT, INTEGER); +DROP FUNCTION IF EXISTS config.ou_marc_subfields(INTEGER, TEXT, 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; +DROP TABLE IF EXISTS config.marc_field; +DROP TABLE IF EXISTS config.marc_format; + +CREATE TABLE config.marc_format ( + id SERIAL PRIMARY KEY, + code TEXT NOT NULL, + name TEXT NOT NULL +); +COMMENT ON TABLE config.marc_format IS $$ +List of MARC formats supported by this Evergreen +database. This exists primarily as a hook for future +support of UNIMARC, though whether that will ever +happen remains to be seen. +$$; + +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') + ), + tag CHAR(3) NOT NULL, + name TEXT, + description TEXT, + fixed_field BOOLEAN, + repeatable BOOLEAN, + mandatory BOOLEAN, + hidden BOOLEAN, + owner INTEGER REFERENCES actor.org_unit (id) + -- if the owner is null, the data about the field is + -- assumed to come from the controlling MARC standard +); + +COMMENT ON TABLE config.marc_field IS $$ +This table stores a list of MARC fields recognized by the Evergreen +instance. Note that we're not aiming for completely generic ISO2709 +support: we're assuming things like three characters for a tag, +one-character subfield labels, two indicators per variable data field, +and the like, all of which are technically specializations of ISO2709. + +Of particular significance is the owner column; if it's set to a null +value, the field definition is assumed to come from a national +standards body; if it's set to a non-null value, the field definition +is an OU-level addition to or override of the standard. +$$; + +CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag); +CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner); + +CREATE UNIQUE INDEX config_standard_marc_tags_are_unique + ON config.marc_field(marc_format, marc_record_type, tag) + WHERE owner IS NULL; +ALTER TABLE config.marc_field + ADD CONSTRAINT config_standard_marc_tags_are_fully_specified + CHECK ((owner IS NOT NULL) OR + ( + owner IS NULL AND + repeatable IS NOT NULL AND + mandatory IS NOT NULL AND + hidden IS NOT NULL + ) + ); + +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') + ), + tag CHAR(3) NOT NULL, + code CHAR(1) NOT NULL, + description TEXT, + repeatable BOOLEAN, + mandatory BOOLEAN, + hidden BOOLEAN, + owner INTEGER REFERENCES actor.org_unit (id) + -- if the owner is null, the data about the subfield is + -- assumed to come from the controlling MARC standard +); + +COMMENT ON TABLE config.marc_subfield IS $$ +This table stores the list of subfields recognized by this Evergreen +instance. As with config.marc_field, of particular significance is the +owner column; if it's set to a null value, the subfield definition is +assumed to come from a national standards body; if it's set to a non-null +value, the subfield definition is an OU-level addition to or override +of the standard. +$$; + +CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code); +CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique + ON config.marc_subfield(marc_format, marc_record_type, tag, code) + WHERE owner IS NULL; +ALTER TABLE config.marc_subfield + ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified + CHECK ((owner IS NOT NULL) OR + ( + owner IS NULL AND + repeatable IS NOT NULL AND + mandatory IS NOT NULL AND + hidden IS NOT NULL + ) + ); + +CREATE OR REPLACE VIEW config.marc_field_for_ou AS +WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag, + name, description, fixed_field, repeatable, + mandatory, hidden, owner, depth) AS ( + -- start with all MARC fields defined by the controlling national standard + SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0 + FROM config.marc_field + WHERE owner IS NULL + UNION + -- 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] + FROM config.marc_field + WHERE owner IS NULL + ) + UNION + -- and start walking down the org unit hierarchy, + -- letting entries for child OUs override field names, + -- descriptions, repeatability, and the like. Letting + -- fixed-fieldness be overridable is something that falls + -- from the implementation, but is unlikely to be useful + SELECT c.id, marc_format, marc_record_type, tag, + COALESCE(c.name, p.name), + COALESCE(c.description, p.description), + COALESCE(c.fixed_field, p.fixed_field), + COALESCE(c.repeatable, p.repeatable), + COALESCE(c.mandatory, p.mandatory), + COALESCE(c.hidden, p.hidden), + c.owner, + depth + 1 + FROM config.marc_field c + JOIN ou_marc_fields p USING (marc_format, marc_record_type, tag) + JOIN actor.org_unit aou ON (c.owner = aou.id) + WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL)) +) +SELECT id, marc_format, marc_record_type, tag, + name, description, fixed_field, repeatable, + mandatory, hidden, owner, depth +FROM ou_marc_fields; + +CREATE OR REPLACE VIEW config.marc_subfield_for_ou AS +WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code, + description, repeatable, + mandatory, hidden, owner, depth) AS ( + -- start with all MARC subfields defined by the controlling national standard + SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory, hidden, owner, 0 + FROM config.marc_subfield + WHERE owner IS NULL + UNION + -- 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] + FROM config.marc_subfield + WHERE owner IS NULL + ) + UNION + -- and start walking down the org unit hierarchy, + -- letting entries for child OUs override subfield + -- descriptions, repeatability, and the like. + SELECT c.id, marc_format, marc_record_type, tag, code, + COALESCE(c.description, p.description), + COALESCE(c.repeatable, p.repeatable), + COALESCE(c.mandatory, p.mandatory), + COALESCE(c.hidden, p.hidden), + c.owner, + depth + 1 + FROM config.marc_subfield c + JOIN ou_marc_subfields p USING (marc_format, marc_record_type, tag, code) + JOIN actor.org_unit aou ON (c.owner = aou.id) + WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL)) +) +SELECT id, marc_format, marc_record_type, tag, code, + description, repeatable, + 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$ + 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, + fixed_field, repeatable, mandatory, hidden, owner, depth, + MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag) AS winner + FROM config.marc_field_for_ou + WHERE (owner IS NULL + OR owner IN (SELECT id FROM actor.org_unit_ancestors($3))) + AND marc_format = $1 + AND marc_record_type = $2 + ) AS s + WHERE depth = winner + 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$ + 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, + repeatable, mandatory, hidden, owner, depth, + MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag, code) AS winner + FROM config.marc_subfield_for_ou + WHERE (owner IS NULL + OR owner IN (SELECT id FROM actor.org_unit_ancestors($3))) + AND marc_format = $1 + AND marc_record_type = $2 + ) AS s + WHERE depth = winner + AND not hidden; +$func$ LANGUAGE SQL; + +COMMIT; -- 2.11.0