3 SELECT evergreen.upgrade_deps_block_check('0933', :eg_version);
6 CREATE TABLE config.marc_format (
11 COMMENT ON TABLE config.marc_format IS $$
12 List of MARC formats supported by this Evergreen
13 database. This exists primarily as a hook for future
14 support of UNIMARC, though whether that will ever
15 happen remains to be seen.
18 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
20 CREATE TABLE config.marc_field (
21 id SERIAL PRIMARY KEY,
22 marc_format INTEGER NOT NULL
23 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
24 marc_record_type config.marc_record_type NOT NULL,
32 owner INTEGER REFERENCES actor.org_unit (id)
33 -- if the owner is null, the data about the field is
34 -- assumed to come from the controlling MARC standard
37 COMMENT ON TABLE config.marc_field IS $$
38 This table stores a list of MARC fields recognized by the Evergreen
39 instance. Note that we're not aiming for completely generic ISO2709
40 support: we're assuming things like three characters for a tag,
41 one-character subfield labels, two indicators per variable data field,
42 and the like, all of which are technically specializations of ISO2709.
44 Of particular significance is the owner column; if it's set to a null
45 value, the field definition is assumed to come from a national
46 standards body; if it's set to a non-null value, the field definition
47 is an OU-level addition to or override of the standard.
50 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
51 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
53 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
54 ON config.marc_field(marc_format, marc_record_type, tag)
56 ALTER TABLE config.marc_field
57 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
58 CHECK ((owner IS NOT NULL) OR
61 repeatable IS NOT NULL AND
62 mandatory IS NOT NULL AND
67 CREATE TABLE config.marc_subfield (
68 id SERIAL PRIMARY KEY,
69 marc_format INTEGER NOT NULL
70 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
71 marc_record_type config.marc_record_type NOT NULL,
73 code CHAR(1) NOT NULL,
79 REFERENCES config.record_attr_definition (name)
80 DEFERRABLE INITIALLY DEFERRED,
81 owner INTEGER REFERENCES actor.org_unit (id)
82 -- if the owner is null, the data about the subfield is
83 -- assumed to come from the controlling MARC standard
86 COMMENT ON TABLE config.marc_subfield IS $$
87 This table stores the list of subfields recognized by this Evergreen
88 instance. As with config.marc_field, of particular significance is the
89 owner column; if it's set to a null value, the subfield definition is
90 assumed to come from a national standards body; if it's set to a non-null
91 value, the subfield definition is an OU-level addition to or override
95 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
96 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
97 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
99 ALTER TABLE config.marc_subfield
100 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
101 CHECK ((owner IS NOT NULL) OR
104 repeatable IS NOT NULL AND
105 mandatory IS NOT NULL AND
110 CREATE OR REPLACE VIEW config.marc_field_for_ou AS
111 WITH RECURSIVE ou_marc_fields(id, marc_format, marc_record_type, tag,
112 name, description, fixed_field, repeatable,
113 mandatory, hidden, owner, depth) AS (
114 -- start with all MARC fields defined by the controlling national standard
115 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0
116 FROM config.marc_field
119 -- as well as any purely local ones that have been added
120 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner, 0
121 FROM config.marc_field
122 WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag] NOT IN (
123 SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag]
124 FROM config.marc_field
128 -- and start walking down the org unit hierarchy,
129 -- letting entries for child OUs override field names,
130 -- descriptions, repeatability, and the like. Letting
131 -- fixed-fieldness be overridable is something that falls
132 -- from the implementation, but is unlikely to be useful
133 SELECT c.id, marc_format, marc_record_type, tag,
134 COALESCE(c.name, p.name),
135 COALESCE(c.description, p.description),
136 COALESCE(c.fixed_field, p.fixed_field),
137 COALESCE(c.repeatable, p.repeatable),
138 COALESCE(c.mandatory, p.mandatory),
139 COALESCE(c.hidden, p.hidden),
142 FROM config.marc_field c
143 JOIN ou_marc_fields p USING (marc_format, marc_record_type, tag)
144 JOIN actor.org_unit aou ON (c.owner = aou.id)
145 WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL))
147 SELECT id, marc_format, marc_record_type, tag,
148 name, description, fixed_field, repeatable,
149 mandatory, hidden, owner, depth
152 CREATE OR REPLACE VIEW config.marc_subfield_for_ou AS
153 WITH RECURSIVE ou_marc_subfields(id, marc_format, marc_record_type, tag, code,
154 description, repeatable,
155 mandatory, hidden, value_ctype, owner, depth) AS (
156 -- start with all MARC subfields defined by the controlling national standard
157 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
158 hidden, value_ctype, owner, 0
159 FROM config.marc_subfield
162 -- as well as any purely local ones that have been added
163 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
164 hidden, value_ctype, owner, 0
165 FROM config.marc_subfield
166 WHERE ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code] NOT IN (
167 SELECT ARRAY[marc_format::TEXT, marc_record_type::TEXT, tag, code]
168 FROM config.marc_subfield
172 -- and start walking down the org unit hierarchy,
173 -- letting entries for child OUs override subfield
174 -- descriptions, repeatability, and the like.
175 SELECT c.id, marc_format, marc_record_type, tag, code,
176 COALESCE(c.description, p.description),
177 COALESCE(c.repeatable, p.repeatable),
178 COALESCE(c.mandatory, p.mandatory),
179 COALESCE(c.hidden, p.hidden),
180 COALESCE(c.value_ctype, p.value_ctype),
183 FROM config.marc_subfield c
184 JOIN ou_marc_subfields p USING (marc_format, marc_record_type, tag, code)
185 JOIN actor.org_unit aou ON (c.owner = aou.id)
186 WHERE (aou.parent_ou = p.owner OR (aou.parent_ou IS NULL AND p.owner IS NULL))
188 SELECT id, marc_format, marc_record_type, tag, code,
189 description, repeatable,
190 mandatory, hidden, value_ctype, owner, depth
191 FROM ou_marc_subfields;
193 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$
194 SELECT id, marc_format, marc_record_type, tag, name, description, fixed_field, repeatable, mandatory, hidden, owner
196 SELECT id, marc_format, marc_record_type, tag, name, description,
197 fixed_field, repeatable, mandatory, hidden, owner, depth,
198 MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag) AS winner
199 FROM config.marc_field_for_ou
201 OR owner IN (SELECT id FROM actor.org_unit_ancestors($3)))
203 AND marc_record_type = $2
209 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$
210 SELECT id, marc_format, marc_record_type, tag, code, description, repeatable, mandatory,
211 hidden, value_ctype, owner
213 SELECT id, marc_format, marc_record_type, tag, code, description,
214 repeatable, mandatory, hidden, value_ctype, owner, depth,
215 MAX(depth) OVER (PARTITION BY marc_format, marc_record_type, tag, code) AS winner
216 FROM config.marc_subfield_for_ou
218 OR owner IN (SELECT id FROM actor.org_unit_ancestors($3)))
220 AND marc_record_type = $2