2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008-2011 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
5 * Copyright (C) 2010 Merrimack Valley Library Consortium
6 * Jason Stephenson <jstephenson@mvlc.org>
7 * Copyright (C) 2010 Laurentian University
8 * Dan Scott <dscott@laurentian.ca>
10 * This program is free software; you can redistribute it and/or
11 * modify it under the terms of the GNU General Public License
12 * as published by the Free Software Foundation; either version 2
13 * of the License, or (at your option) any later version.
15 * This program is distributed in the hope that it will be useful,
16 * but WITHOUT ANY WARRANTY; without even the implied warranty of
17 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 * GNU General Public License for more details.
24 DROP SCHEMA IF EXISTS stats CASCADE;
25 DROP SCHEMA IF EXISTS config CASCADE;
31 COMMENT ON SCHEMA config IS $$
32 The config schema holds static configuration data for the
33 Evergreen installation.
36 CREATE TABLE config.internal_flag (
37 name TEXT PRIMARY KEY,
39 enabled BOOL NOT NULL DEFAULT FALSE
41 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_insert');
42 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_update');
43 INSERT INTO config.internal_flag (name) VALUES ('ingest.reingest.force_on_same_marc');
44 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri');
45 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec');
46 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor');
47 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
52 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
53 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');
55 CREATE TABLE config.global_flag (
57 ) INHERITS (config.internal_flag);
58 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
60 CREATE TABLE config.upgrade_log (
61 version TEXT PRIMARY KEY,
62 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
66 CREATE TABLE config.db_patch_dependencies (
67 db_patch TEXT PRIMARY KEY,
72 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
78 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
80 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
86 CREATE TRIGGER no_overlapping_sups
87 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
88 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
90 CREATE TRIGGER no_overlapping_deps
91 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
92 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
94 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1020', :eg_version); -- berick/csharp/kmlussier
96 CREATE TABLE config.bib_source (
97 id SERIAL PRIMARY KEY,
98 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
99 source TEXT NOT NULL UNIQUE,
100 transcendant BOOL NOT NULL DEFAULT FALSE,
101 can_have_copies BOOL NOT NULL DEFAULT TRUE
103 COMMENT ON TABLE config.bib_source IS $$
104 This is table is used to set up the relative "quality" of each
105 MARC source, such as OCLC. Also identifies "transcendant" sources,
106 i.e., sources of bib records that should display in the OPAC
107 even if no copies or located URIs are attached. Also indicates if
108 the source is allowed to have actual copies on its bibs. Volumes
109 for targeted URIs are unaffected by this setting.
112 CREATE TABLE config.standing (
113 id SERIAL PRIMARY KEY,
114 value TEXT NOT NULL UNIQUE
116 COMMENT ON TABLE config.standing IS $$
119 This table contains the values that can be applied to a patron
120 by a staff member. These values should not be changed, other
121 than for translation, as the ID column is currently a "magic
122 number" in the source. :(
125 CREATE TABLE config.standing_penalty (
126 id SERIAL PRIMARY KEY,
127 name TEXT NOT NULL UNIQUE,
130 staff_alert BOOL NOT NULL DEFAULT FALSE,
132 ignore_proximity INTEGER
135 CREATE TABLE config.xml_transform (
136 name TEXT PRIMARY KEY,
137 namespace_uri TEXT NOT NULL,
138 prefix TEXT NOT NULL,
142 CREATE TABLE config.biblio_fingerprint (
143 id SERIAL PRIMARY KEY,
146 first_word BOOL NOT NULL DEFAULT FALSE,
147 format TEXT NOT NULL DEFAULT 'marcxml'
150 INSERT INTO config.biblio_fingerprint (name, xpath, format)
153 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
154 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
155 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
157 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
161 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
164 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
165 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
168 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
173 INSERT INTO config.biblio_fingerprint (name, xpath, format)
176 '//mods32:mods/mods32:titleInfo/mods32:partName',
180 INSERT INTO config.biblio_fingerprint (name, xpath, format)
183 '//mods32:mods/mods32:titleInfo/mods32:partNumber',
187 CREATE TABLE config.metabib_class (
188 name TEXT PRIMARY KEY,
189 label TEXT NOT NULL UNIQUE,
190 buoyant BOOL DEFAULT FALSE NOT NULL,
191 restrict BOOL DEFAULT FALSE NOT NULL,
192 combined BOOL DEFAULT FALSE NOT NULL,
193 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
194 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
195 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
196 d_weight NUMERIC DEFAULT 0.1 NOT NULL
199 CREATE TABLE config.metabib_field (
200 id SERIAL PRIMARY KEY,
201 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
205 weight INT NOT NULL DEFAULT 1,
206 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
207 search_field BOOL NOT NULL DEFAULT TRUE,
208 facet_field BOOL NOT NULL DEFAULT FALSE,
209 browse_field BOOL NOT NULL DEFAULT TRUE,
211 browse_sort_xpath TEXT,
213 authority_xpath TEXT,
215 restrict BOOL DEFAULT FALSE NOT NULL
217 COMMENT ON TABLE config.metabib_field IS $$
218 XPath used for record indexing ingest
220 This table contains the XPath used to chop up MODS into its
221 indexable parts. Each XPath entry is named and assigned to
222 a "class" of either title, subject, author, keyword, series
226 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
228 CREATE TABLE config.ts_config_list (
232 COMMENT ON TABLE config.ts_config_list IS $$
235 A list of full text configs with names and descriptions.
238 CREATE TABLE config.metabib_class_ts_map (
239 id SERIAL PRIMARY KEY,
240 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
241 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
242 active BOOL NOT NULL DEFAULT TRUE,
243 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
244 index_lang TEXT NULL,
245 search_lang TEXT NULL,
246 always BOOL NOT NULL DEFAULT true
248 COMMENT ON TABLE config.metabib_class_ts_map IS $$
249 Text Search Configs for metabib class indexing
251 This table contains text search config definitions for
252 storing index_vector values.
255 CREATE TABLE config.metabib_field_ts_map (
256 id SERIAL PRIMARY KEY,
257 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
258 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
259 active BOOL NOT NULL DEFAULT TRUE,
260 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
261 index_lang TEXT NULL,
262 search_lang TEXT NULL
264 COMMENT ON TABLE config.metabib_field_ts_map IS $$
265 Text Search Configs for metabib field indexing
267 This table contains text search config definitions for
268 storing index_vector values.
271 CREATE TABLE config.metabib_search_alias (
272 alias TEXT PRIMARY KEY,
273 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
274 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
277 CREATE TABLE config.non_cataloged_type (
278 id SERIAL PRIMARY KEY,
279 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
281 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
282 in_house BOOL NOT NULL DEFAULT FALSE,
283 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
285 COMMENT ON TABLE config.non_cataloged_type IS $$
286 Types of valid non-cataloged items.
289 CREATE TABLE config.identification_type (
290 id SERIAL PRIMARY KEY,
291 name TEXT NOT NULL UNIQUE
293 COMMENT ON TABLE config.identification_type IS $$
294 Types of valid patron identification.
296 Each patron must display at least one valid form of identification
297 in order to get a library card. This table lists those forms.
300 CREATE TABLE config.rule_circ_duration (
301 id SERIAL PRIMARY KEY,
302 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
303 extended INTERVAL NOT NULL,
304 normal INTERVAL NOT NULL,
305 shrt INTERVAL NOT NULL,
306 max_renewals INT NOT NULL
308 COMMENT ON TABLE config.rule_circ_duration IS $$
309 Circulation Duration rules
311 Each circulation is given a duration based on one of these rules.
314 CREATE TABLE config.hard_due_date (
315 id SERIAL PRIMARY KEY,
316 name TEXT NOT NULL UNIQUE,
317 ceiling_date TIMESTAMPTZ NOT NULL,
318 forceto BOOL NOT NULL,
319 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
322 CREATE TABLE config.hard_due_date_values (
323 id SERIAL PRIMARY KEY,
324 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
325 DEFERRABLE INITIALLY DEFERRED,
326 ceiling_date TIMESTAMPTZ NOT NULL,
327 active_date TIMESTAMPTZ NOT NULL
330 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
332 temp_value config.hard_due_date_values%ROWTYPE;
336 SELECT DISTINCT ON (hard_due_date) *
337 FROM config.hard_due_date_values
338 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
339 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
341 UPDATE config.hard_due_date
342 SET ceiling_date = temp_value.ceiling_date
343 WHERE id = temp_value.hard_due_date
344 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
347 updated := updated + 1;
353 $func$ LANGUAGE plpgsql;
355 CREATE TABLE config.rule_max_fine (
356 id SERIAL PRIMARY KEY,
357 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
358 amount NUMERIC(6,2) NOT NULL,
359 is_percent BOOL NOT NULL DEFAULT FALSE
361 COMMENT ON TABLE config.rule_max_fine IS $$
362 Circulation Max Fine rules
364 Each circulation is given a maximum fine based on one of
368 CREATE TABLE config.rule_recurring_fine (
369 id SERIAL PRIMARY KEY,
370 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
371 high NUMERIC(6,2) NOT NULL,
372 normal NUMERIC(6,2) NOT NULL,
373 low NUMERIC(6,2) NOT NULL,
374 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
375 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
377 COMMENT ON TABLE config.rule_recurring_fine IS $$
378 Circulation Recurring Fine rules
380 Each circulation is given a recurring fine amount based on one of
381 these rules. Note that it is recommended to run the fine generator
382 (from cron) at least as frequently as the lowest recurrence interval
383 used by your circulation rules so that accrued fines will be up
388 CREATE TABLE config.rule_age_hold_protect (
389 id SERIAL PRIMARY KEY,
390 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
391 age INTERVAL NOT NULL,
394 COMMENT ON TABLE config.rule_age_hold_protect IS $$
395 Hold Item Age Protection rules
397 A hold request can only capture new(ish) items when they are
398 within a particular proximity of the pickup_lib of the request.
399 The proximity ('prox' column) is calculated by counting
400 the number of tree edges between the pickup_lib and either the
401 owning_lib or circ_lib of the copy that could fulfill the hold,
402 as determined by the distance_is_from_owner value of the hold matrix
403 rule controlling the hold request.
406 CREATE TABLE config.copy_status (
407 id SERIAL PRIMARY KEY,
408 name TEXT NOT NULL UNIQUE,
409 holdable BOOL NOT NULL DEFAULT FALSE,
410 opac_visible BOOL NOT NULL DEFAULT FALSE,
411 copy_active BOOL NOT NULL DEFAULT FALSE,
412 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
413 is_available BOOL NOT NULL DEFAULT FALSE
415 COMMENT ON TABLE config.copy_status IS $$
418 The available copy statuses, and whether a copy in that
419 status is available for hold request capture. 0 (zero) is
420 the only special number in this set, meaning that the item
421 is available for immediate checkout, and is counted as available
424 Statuses with an ID below 100 are not removable, and have special
425 meaning in the code. Do not change them except to translate the
428 You may add and remove statuses above 100, and these can be used
429 to remove items from normal circulation without affecting the rest
430 of the copy's values or its location.
433 CREATE TABLE config.net_access_level (
434 id SERIAL PRIMARY KEY,
435 name TEXT NOT NULL UNIQUE
437 COMMENT ON TABLE config.net_access_level IS $$
438 Patron Network Access level
440 This will be used to inform the in-library firewall of how much
441 internet access the using patron should be allowed.
445 CREATE TABLE config.remote_account (
446 id SERIAL PRIMARY KEY,
448 host TEXT NOT NULL, -- name or IP, :port optional
449 username TEXT, -- optional, since we could default to $USER
450 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
451 account TEXT, -- aka profile or FTP "account" command
452 path TEXT, -- aka directory
453 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
454 last_activity TIMESTAMP WITH TIME ZONE
457 CREATE TABLE config.marc21_rec_type_map (
458 code TEXT PRIMARY KEY,
459 type_val TEXT NOT NULL,
460 blvl_val TEXT NOT NULL
463 CREATE TABLE config.marc21_ff_pos_map (
464 id SERIAL PRIMARY KEY,
465 fixed_field TEXT NOT NULL,
467 rec_type TEXT NOT NULL,
468 start_pos INT NOT NULL,
470 default_val TEXT NOT NULL DEFAULT ' '
473 CREATE TABLE config.marc21_physical_characteristic_type_map (
474 ptype_key TEXT PRIMARY KEY,
475 label TEXT NOT NULL -- I18N
478 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
479 id SERIAL PRIMARY KEY,
480 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
481 subfield TEXT NOT NULL,
482 start_pos INT NOT NULL,
484 label TEXT NOT NULL -- I18N
487 CREATE TABLE config.marc21_physical_characteristic_value_map (
488 id SERIAL PRIMARY KEY,
490 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
491 label TEXT NOT NULL -- I18N
495 CREATE TABLE config.z3950_source (
496 name TEXT PRIMARY KEY,
497 label TEXT NOT NULL UNIQUE,
501 record_format TEXT NOT NULL DEFAULT 'FI',
502 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
503 auth BOOL NOT NULL DEFAULT TRUE,
504 use_perm INT -- REFERENCES permission.perm_list (id)
507 COMMENT ON TABLE config.z3950_source IS $$
510 Each row in this table represents a database searchable via Z39.50.
513 COMMENT ON COLUMN config.z3950_source.record_format IS $$
517 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
518 Z39.50 preferred record syntax..
521 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
522 If set, this permission is required for the source to be listed in the staff
523 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
526 CREATE TABLE config.z3950_attr (
527 id SERIAL PRIMARY KEY,
528 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
533 truncation INT NOT NULL DEFAULT 0,
534 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
537 CREATE TABLE config.z3950_source_credentials (
538 id SERIAL PRIMARY KEY,
539 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
540 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
543 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
546 CREATE TABLE config.i18n_locale (
547 code TEXT PRIMARY KEY,
548 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
549 name TEXT UNIQUE NOT NULL,
553 CREATE TABLE config.i18n_core (
554 id BIGSERIAL PRIMARY KEY,
555 fq_field TEXT NOT NULL,
556 identity_value TEXT NOT NULL,
557 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
561 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
563 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
567 UPDATE config.i18n_core
568 SET identity_value = $$ || quote_literal(new_ident) || $$
569 WHERE fq_field LIKE '$$ || hint || $$.%'
570 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
575 $_$ LANGUAGE PLPGSQL;
577 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
579 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
582 $_$ LANGUAGE PLPGSQL;
584 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
586 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
589 $_$ LANGUAGE PLPGSQL;
591 CREATE TABLE config.billing_type (
592 id SERIAL PRIMARY KEY,
594 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
595 default_price NUMERIC(6,2),
596 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
599 CREATE TABLE config.settings_group (
600 name TEXT PRIMARY KEY,
601 label TEXT UNIQUE NOT NULL -- I18N
604 CREATE TABLE config.org_unit_setting_type (
605 name TEXT PRIMARY KEY,
606 label TEXT UNIQUE NOT NULL,
607 grp TEXT REFERENCES config.settings_group (name),
609 datatype TEXT NOT NULL DEFAULT 'string',
614 -- define valid datatypes
616 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
617 ( 'bool', 'integer', 'float', 'currency', 'interval',
618 'date', 'string', 'object', 'array', 'link' ) ),
620 -- fm_class is meaningful only for 'link' datatype
622 CONSTRAINT coust_no_empty_link CHECK
623 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
624 ( datatype <> 'link' AND fm_class IS NULL ) )
627 CREATE TABLE config.usr_setting_type (
629 name TEXT PRIMARY KEY,
630 opac_visible BOOL NOT NULL DEFAULT FALSE,
631 label TEXT UNIQUE NOT NULL,
633 grp TEXT REFERENCES config.settings_group (name),
634 datatype TEXT NOT NULL DEFAULT 'string',
639 -- define valid datatypes
641 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
642 ( 'bool', 'integer', 'float', 'currency', 'interval',
643 'date', 'string', 'object', 'array', 'link' ) ),
646 -- fm_class is meaningful only for 'link' datatype
648 CONSTRAINT coust_no_empty_link CHECK
649 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
650 ( datatype <> 'link' AND fm_class IS NULL ) )
654 -- Some handy functions, based on existing ones, to provide optional ingest normalization
656 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
657 SELECT SUBSTRING($1,$2);
658 $func$ LANGUAGE SQL STRICT IMMUTABLE;
660 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
661 SELECT SUBSTRING($1,1,$2);
662 $func$ LANGUAGE SQL STRICT IMMUTABLE;
664 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
665 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
666 $func$ LANGUAGE SQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
669 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
670 $func$ LANGUAGE SQL STRICT IMMUTABLE;
672 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
673 SELECT approximate_date( $1, '0');
674 $func$ LANGUAGE SQL STRICT IMMUTABLE;
676 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
677 SELECT approximate_date( $1, '9');
678 $func$ LANGUAGE SQL STRICT IMMUTABLE;
680 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
681 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
682 $func$ LANGUAGE SQL STRICT IMMUTABLE;
684 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
685 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
686 $func$ LANGUAGE SQL STRICT IMMUTABLE;
688 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
693 # Find the first ISBN, force it to ISBN13 and return it
697 foreach my $word (split(/\s/, $input)) {
698 my $isbn = Business::ISBN->new($word);
700 # First check the checksum; if it is not valid, fix it and add the original
701 # bad-checksum ISBN to the output
702 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
703 $isbn->fix_checksum();
706 # If we now have a valid ISBN, force it to ISBN13 and return it
707 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
710 $func$ LANGUAGE PLPERLU;
712 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
713 Inspired by translate_isbn1013
715 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
716 version without hypens and with a repaired checksum if the checksum was bad
720 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
725 # For each ISBN found in a single string containing a set of ISBNs:
726 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
727 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
732 foreach my $word (split(/\s/, $input)) {
733 my $isbn = Business::ISBN->new($word);
735 # First check the checksum; if it is not valid, fix it and add the original
736 # bad-checksum ISBN to the output
737 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
738 $output .= $isbn->isbn() . " ";
739 $isbn->fix_checksum();
742 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
743 # and add the normalized original ISBN to the output
744 if ($isbn && $isbn->is_valid()) {
745 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
746 $output .= $isbn->isbn . " ";
748 # If we successfully converted the ISBN to its counterpart, add the
749 # converted ISBN to the output as well
750 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
753 return $output if $output;
755 # If there were no valid ISBNs, just return the raw input
757 $func$ LANGUAGE PLPERLU;
759 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
760 The translate_isbn1013 function takes an input ISBN and returns the
761 following in a single space-delimited string if the input ISBN is valid:
762 - The normalized input ISBN (hyphens stripped)
763 - The normalized input ISBN with a fixed checksum if the checksum was bad
764 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
767 -- And ... a table in which to register them
769 CREATE TABLE config.index_normalizer (
770 id SERIAL PRIMARY KEY,
771 name TEXT UNIQUE NOT NULL,
774 param_count INT NOT NULL DEFAULT 0
777 CREATE TABLE config.metabib_field_index_norm_map (
778 id SERIAL PRIMARY KEY,
779 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
780 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
782 pos INT NOT NULL DEFAULT 0
785 CREATE TABLE config.record_attr_definition (
786 name TEXT PRIMARY KEY,
787 label TEXT NOT NULL, -- I18N
789 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
790 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
791 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
792 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
794 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
795 tag TEXT, -- LIKE format
796 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
798 -- This is used for both tag/sf and xpath entries
801 -- For xpath-extracted attrs
803 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
808 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
810 -- For phys-char fields
811 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
813 -- Source of vocabulary terms for this record attribute;
814 -- typically will be a URI referring to a SKOS vocabulary
818 CREATE TABLE config.record_attr_index_norm_map (
819 id SERIAL PRIMARY KEY,
820 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
821 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
823 pos INT NOT NULL DEFAULT 0
826 CREATE TABLE config.coded_value_map (
827 id SERIAL PRIMARY KEY,
828 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
832 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
834 is_simple BOOL NOT NULL DEFAULT FALSE,
835 concept_uri TEXT -- URI expressing the SKOS concept that the
836 -- coded value represents
839 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
841 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
842 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
843 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
844 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
845 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
846 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
847 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
849 CREATE OR REPLACE FUNCTION config.update_coded_value_map(in_ctype TEXT, in_code TEXT, in_value TEXT, in_description TEXT DEFAULT NULL, in_opac_visible BOOL DEFAULT NULL, in_search_label TEXT DEFAULT NULL, in_is_simple BOOL DEFAULT NULL, add_only BOOL DEFAULT FALSE) RETURNS VOID AS $f$
851 current_row config.coded_value_map%ROWTYPE;
853 -- Look for a current value
854 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
856 IF FOUND AND NOT add_only THEN
857 -- Update anything we were handed
858 current_row.value := COALESCE(current_row.value, in_value);
859 current_row.description := COALESCE(current_row.description, in_description);
860 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
861 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
862 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
863 UPDATE config.coded_value_map
865 value = current_row.value,
866 description = current_row.description,
867 opac_visible = current_row.opac_visible,
868 search_label = current_row.search_label,
869 is_simple = current_row.is_simple
870 WHERE id = current_row.id;
872 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
873 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
876 $f$ LANGUAGE PLPGSQL;
878 CREATE TABLE config.composite_attr_entry_definition(
879 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
880 definition TEXT NOT NULL -- JSON
883 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
884 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
885 SELECT DISTINCT l.version
886 FROM config.upgrade_log l
887 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
888 WHERE d.db_patch = $1
891 -- List applied db patches that are superseded by (and block the application of) my_db_patch
892 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
893 SELECT DISTINCT l.version
894 FROM config.upgrade_log l
895 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
896 WHERE d.db_patch = $1
899 -- List applied db patches that deprecates (and block the application of) my_db_patch
900 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
902 FROM config.db_patch_dependencies
903 WHERE ARRAY[$1]::TEXT[] && deprecates
906 -- List applied db patches that supersedes (and block the application of) my_db_patch
907 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
909 FROM config.db_patch_dependencies
910 WHERE ARRAY[$1]::TEXT[] && supersedes
913 -- Make sure that no deprecated or superseded db patches are currently applied
914 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
916 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
918 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
920 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
922 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
925 -- Raise an exception if there are, in fact, dep/sup conflict
926 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
931 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
932 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
933 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
935 Upgrade script % can not be applied:
936 applied deprecated scripts %
937 applied superseded scripts %
941 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
942 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
943 evergreen.upgrade_list_applied_deprecated(my_db_patch),
944 evergreen.upgrade_list_applied_superseded(my_db_patch);
947 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
952 CREATE TABLE config.barcode_completion (
953 id SERIAL PRIMARY KEY,
954 active BOOL NOT NULL DEFAULT true,
955 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
958 length INT NOT NULL DEFAULT 0,
960 padding_end BOOL NOT NULL DEFAULT false,
961 asset BOOL NOT NULL DEFAULT true,
962 actor BOOL NOT NULL DEFAULT true
965 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
967 -- Add support for logging, only keep the most recent five rows for each category.
970 CREATE TABLE config.org_unit_setting_type_log (
971 id BIGSERIAL PRIMARY KEY,
972 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
973 org INT, --REFERENCES actor.org_unit (id),
976 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
979 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
980 Org Unit setting Logs
982 This table contains the most recent changes to each setting
983 in actor.org_unit_setting, allowing for mistakes to be undone.
984 This is NOT meant to be an auditor, but rather an undo/redo.
987 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
989 -- Only keeps the most recent five settings changes.
990 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
991 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org ORDER BY date_applied DESC LIMIT 4);
993 IF (TG_OP = 'UPDATE') THEN
995 ELSIF (TG_OP = 'INSERT') THEN
1000 $oustl_limit$ LANGUAGE plpgsql;
1002 CREATE TRIGGER limit_logs_oust
1003 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1004 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1006 CREATE TABLE config.sms_carrier (
1007 id SERIAL PRIMARY KEY,
1011 active BOOLEAN DEFAULT TRUE
1014 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1016 CREATE TABLE config.usr_activity_type (
1017 id SERIAL PRIMARY KEY,
1021 label TEXT NOT NULL, -- i18n
1022 egroup config.usr_activity_group NOT NULL,
1023 enabled BOOL NOT NULL DEFAULT TRUE,
1024 transient BOOL NOT NULL DEFAULT TRUE,
1025 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1028 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1029 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1031 CREATE TABLE config.filter_dialog_interface (
1032 key TEXT PRIMARY KEY,
1036 CREATE TABLE config.filter_dialog_filter_set (
1037 id SERIAL PRIMARY KEY,
1039 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1040 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1041 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1042 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1043 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1044 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1047 CREATE TABLE config.best_hold_order(
1048 id SERIAL PRIMARY KEY,
1049 name TEXT UNIQUE, -- i18n
1050 pprox INT, -- copy capture <-> pickup lib prox
1051 hprox INT, -- copy circ lib <-> request lib prox
1052 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1053 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1054 priority INT, -- group hold priority
1055 cut INT, -- cut-in-line
1056 depth INT, -- selection depth
1057 htime INT, -- time since last home-lib circ exceeds org-unit setting
1058 rtime INT, -- request time
1059 shtime INT -- time since copy last trip home exceeds org-unit setting
1062 -- At least one of these columns must contain a non-null value
1063 ALTER TABLE config.best_hold_order ADD CHECK ((
1064 pprox IS NOT NULL OR
1065 hprox IS NOT NULL OR
1066 aprox IS NOT NULL OR
1067 priority IS NOT NULL OR
1069 depth IS NOT NULL OR
1070 htime IS NOT NULL OR
1074 CREATE OR REPLACE FUNCTION
1075 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1078 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1084 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1087 $func$ LANGUAGE PLPGSQL STABLE;
1089 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1090 Used by a config.z3950_index_field_map constraint trigger
1091 to verify z3950_attr_type maps.
1094 -- drop these in down here since they reference config.metabib_field
1095 -- and config.record_attr_definition
1096 CREATE TABLE config.z3950_index_field_map (
1097 id SERIAL PRIMARY KEY,
1098 label TEXT NOT NULL, -- i18n
1099 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1100 record_attr TEXT REFERENCES config.record_attr_definition(name),
1101 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1102 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1103 CONSTRAINT metabib_field_or_record_attr CHECK (
1104 metabib_field IS NOT NULL OR
1105 record_attr IS NOT NULL
1107 CONSTRAINT attr_or_attr_type CHECK (
1108 z3950_attr IS NOT NULL OR
1109 z3950_attr_type IS NOT NULL
1113 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1114 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1115 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1117 CREATE TABLE config.marc_format (
1118 id SERIAL PRIMARY KEY,
1122 COMMENT ON TABLE config.marc_format IS $$
1123 List of MARC formats supported by this Evergreen
1124 database. This exists primarily as a hook for future
1125 support of UNIMARC, though whether that will ever
1126 happen remains to be seen.
1129 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1131 CREATE TABLE config.marc_field (
1132 id SERIAL PRIMARY KEY,
1133 marc_format INTEGER NOT NULL
1134 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1135 marc_record_type config.marc_record_type NOT NULL,
1136 tag CHAR(3) NOT NULL,
1139 fixed_field BOOLEAN,
1143 owner INTEGER -- REFERENCES actor.org_unit (id)
1144 -- if the owner is null, the data about the field is
1145 -- assumed to come from the controlling MARC standard
1148 COMMENT ON TABLE config.marc_field IS $$
1149 This table stores a list of MARC fields recognized by the Evergreen
1150 instance. Note that we're not aiming for completely generic ISO2709
1151 support: we're assuming things like three characters for a tag,
1152 one-character subfield labels, two indicators per variable data field,
1153 and the like, all of which are technically specializations of ISO2709.
1155 Of particular significance is the owner column; if it's set to a null
1156 value, the field definition is assumed to come from a national
1157 standards body; if it's set to a non-null value, the field definition
1158 is an OU-level addition to or override of the standard.
1161 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1162 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1164 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1165 ON config.marc_field(marc_format, marc_record_type, tag)
1166 WHERE owner IS NULL;
1167 ALTER TABLE config.marc_field
1168 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1169 CHECK ((owner IS NOT NULL) OR
1172 repeatable IS NOT NULL AND
1173 mandatory IS NOT NULL AND
1178 CREATE TABLE config.marc_subfield (
1179 id SERIAL PRIMARY KEY,
1180 marc_format INTEGER NOT NULL
1181 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1182 marc_record_type config.marc_record_type NOT NULL,
1183 tag CHAR(3) NOT NULL,
1184 code CHAR(1) NOT NULL,
1190 REFERENCES config.record_attr_definition (name)
1191 DEFERRABLE INITIALLY DEFERRED,
1192 owner INTEGER -- REFERENCES actor.org_unit (id)
1193 -- if the owner is null, the data about the subfield is
1194 -- assumed to come from the controlling MARC standard
1197 COMMENT ON TABLE config.marc_subfield IS $$
1198 This table stores the list of subfields recognized by this Evergreen
1199 instance. As with config.marc_field, of particular significance is the
1200 owner column; if it's set to a null value, the subfield definition is
1201 assumed to come from a national standards body; if it's set to a non-null
1202 value, the subfield definition is an OU-level addition to or override
1206 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1207 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1208 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1209 WHERE owner IS NULL;
1210 ALTER TABLE config.marc_subfield
1211 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1212 CHECK ((owner IS NOT NULL) OR
1215 repeatable IS NOT NULL AND
1216 mandatory IS NOT NULL AND