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.assume_inserts_only');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_display_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');
56 CREATE TABLE config.global_flag (
58 ) INHERITS (config.internal_flag);
59 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
61 CREATE TABLE config.upgrade_log (
62 version TEXT PRIMARY KEY,
63 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
67 CREATE TABLE config.db_patch_dependencies (
68 db_patch TEXT PRIMARY KEY,
73 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
79 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
81 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
87 CREATE TRIGGER no_overlapping_sups
88 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
89 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
91 CREATE TRIGGER no_overlapping_deps
92 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
93 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
95 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1077', :eg_version); -- csharp/gmcharlt
97 CREATE TABLE config.bib_source (
98 id SERIAL PRIMARY KEY,
99 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
100 source TEXT NOT NULL UNIQUE,
101 transcendant BOOL NOT NULL DEFAULT FALSE,
102 can_have_copies BOOL NOT NULL DEFAULT TRUE
104 COMMENT ON TABLE config.bib_source IS $$
105 This is table is used to set up the relative "quality" of each
106 MARC source, such as OCLC. Also identifies "transcendant" sources,
107 i.e., sources of bib records that should display in the OPAC
108 even if no copies or located URIs are attached. Also indicates if
109 the source is allowed to have actual copies on its bibs. Volumes
110 for targeted URIs are unaffected by this setting.
113 CREATE TABLE config.standing (
114 id SERIAL PRIMARY KEY,
115 value TEXT NOT NULL UNIQUE
117 COMMENT ON TABLE config.standing IS $$
120 This table contains the values that can be applied to a patron
121 by a staff member. These values should not be changed, other
122 than for translation, as the ID column is currently a "magic
123 number" in the source. :(
126 CREATE TABLE config.standing_penalty (
127 id SERIAL PRIMARY KEY,
128 name TEXT NOT NULL UNIQUE,
131 staff_alert BOOL NOT NULL DEFAULT FALSE,
133 ignore_proximity INTEGER
136 CREATE TABLE config.xml_transform (
137 name TEXT PRIMARY KEY,
138 namespace_uri TEXT NOT NULL,
139 prefix TEXT NOT NULL,
143 CREATE TABLE config.biblio_fingerprint (
144 id SERIAL PRIMARY KEY,
147 first_word BOOL NOT NULL DEFAULT FALSE,
148 format TEXT NOT NULL DEFAULT 'marcxml'
151 INSERT INTO config.biblio_fingerprint (name, xpath, format)
154 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
155 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
157 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
158 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
162 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
165 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
168 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
169 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
174 INSERT INTO config.biblio_fingerprint (name, xpath, format)
177 '//mods32:mods/mods32:titleInfo/mods32:partName',
181 INSERT INTO config.biblio_fingerprint (name, xpath, format)
184 '//mods32:mods/mods32:titleInfo/mods32:partNumber',
188 CREATE TABLE config.metabib_class (
189 name TEXT PRIMARY KEY,
190 label TEXT NOT NULL UNIQUE,
191 buoyant BOOL DEFAULT FALSE NOT NULL,
192 restrict BOOL DEFAULT FALSE NOT NULL,
193 combined BOOL DEFAULT FALSE NOT NULL,
194 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
195 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
196 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
197 d_weight NUMERIC DEFAULT 0.1 NOT NULL
200 CREATE TABLE config.metabib_field (
201 id SERIAL PRIMARY KEY,
202 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
206 weight INT NOT NULL DEFAULT 1,
207 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
208 search_field BOOL NOT NULL DEFAULT TRUE,
209 facet_field BOOL NOT NULL DEFAULT FALSE,
210 browse_field BOOL NOT NULL DEFAULT TRUE,
212 browse_sort_xpath TEXT,
215 authority_xpath TEXT,
217 restrict BOOL DEFAULT FALSE NOT NULL,
218 display_field BOOL NOT NULL DEFAULT TRUE
220 COMMENT ON TABLE config.metabib_field IS $$
221 XPath used for record indexing ingest
223 This table contains the XPath used to chop up MODS into its
224 indexable parts. Each XPath entry is named and assigned to
225 a "class" of either title, subject, author, keyword, series
229 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
231 CREATE TABLE config.display_field_map (
232 name TEXT PRIMARY KEY,
233 field INTEGER REFERENCES config.metabib_field (id),
234 multi BOOLEAN DEFAULT FALSE
237 CREATE TABLE config.ts_config_list (
241 COMMENT ON TABLE config.ts_config_list IS $$
244 A list of full text configs with names and descriptions.
247 CREATE TABLE config.metabib_class_ts_map (
248 id SERIAL PRIMARY KEY,
249 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
250 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
251 active BOOL NOT NULL DEFAULT TRUE,
252 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
253 index_lang TEXT NULL,
254 search_lang TEXT NULL,
255 always BOOL NOT NULL DEFAULT true
257 COMMENT ON TABLE config.metabib_class_ts_map IS $$
258 Text Search Configs for metabib class indexing
260 This table contains text search config definitions for
261 storing index_vector values.
264 CREATE TABLE config.metabib_field_ts_map (
265 id SERIAL PRIMARY KEY,
266 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
267 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
268 active BOOL NOT NULL DEFAULT TRUE,
269 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
270 index_lang TEXT NULL,
271 search_lang TEXT NULL
273 COMMENT ON TABLE config.metabib_field_ts_map IS $$
274 Text Search Configs for metabib field indexing
276 This table contains text search config definitions for
277 storing index_vector values.
280 CREATE TABLE config.metabib_search_alias (
281 alias TEXT PRIMARY KEY,
282 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
283 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
286 CREATE TABLE config.non_cataloged_type (
287 id SERIAL PRIMARY KEY,
288 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
290 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
291 in_house BOOL NOT NULL DEFAULT FALSE,
292 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
294 COMMENT ON TABLE config.non_cataloged_type IS $$
295 Types of valid non-cataloged items.
298 CREATE TABLE config.identification_type (
299 id SERIAL PRIMARY KEY,
300 name TEXT NOT NULL UNIQUE
302 COMMENT ON TABLE config.identification_type IS $$
303 Types of valid patron identification.
305 Each patron must display at least one valid form of identification
306 in order to get a library card. This table lists those forms.
309 CREATE TABLE config.rule_circ_duration (
310 id SERIAL PRIMARY KEY,
311 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
312 extended INTERVAL NOT NULL,
313 normal INTERVAL NOT NULL,
314 shrt INTERVAL NOT NULL,
315 max_renewals INT NOT NULL
317 COMMENT ON TABLE config.rule_circ_duration IS $$
318 Circulation Duration rules
320 Each circulation is given a duration based on one of these rules.
323 CREATE TABLE config.hard_due_date (
324 id SERIAL PRIMARY KEY,
325 name TEXT NOT NULL UNIQUE,
326 ceiling_date TIMESTAMPTZ NOT NULL,
327 forceto BOOL NOT NULL,
328 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
331 CREATE TABLE config.hard_due_date_values (
332 id SERIAL PRIMARY KEY,
333 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
334 DEFERRABLE INITIALLY DEFERRED,
335 ceiling_date TIMESTAMPTZ NOT NULL,
336 active_date TIMESTAMPTZ NOT NULL
339 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
341 temp_value config.hard_due_date_values%ROWTYPE;
345 SELECT DISTINCT ON (hard_due_date) *
346 FROM config.hard_due_date_values
347 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
348 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
350 UPDATE config.hard_due_date
351 SET ceiling_date = temp_value.ceiling_date
352 WHERE id = temp_value.hard_due_date
353 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
354 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
357 updated := updated + 1;
363 $func$ LANGUAGE plpgsql;
365 CREATE TABLE config.rule_max_fine (
366 id SERIAL PRIMARY KEY,
367 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
368 amount NUMERIC(6,2) NOT NULL,
369 is_percent BOOL NOT NULL DEFAULT FALSE
371 COMMENT ON TABLE config.rule_max_fine IS $$
372 Circulation Max Fine rules
374 Each circulation is given a maximum fine based on one of
378 CREATE TABLE config.rule_recurring_fine (
379 id SERIAL PRIMARY KEY,
380 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
381 high NUMERIC(6,2) NOT NULL,
382 normal NUMERIC(6,2) NOT NULL,
383 low NUMERIC(6,2) NOT NULL,
384 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
385 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
387 COMMENT ON TABLE config.rule_recurring_fine IS $$
388 Circulation Recurring Fine rules
390 Each circulation is given a recurring fine amount based on one of
391 these rules. Note that it is recommended to run the fine generator
392 (from cron) at least as frequently as the lowest recurrence interval
393 used by your circulation rules so that accrued fines will be up
398 CREATE TABLE config.rule_age_hold_protect (
399 id SERIAL PRIMARY KEY,
400 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
401 age INTERVAL NOT NULL,
404 COMMENT ON TABLE config.rule_age_hold_protect IS $$
405 Hold Item Age Protection rules
407 A hold request can only capture new(ish) items when they are
408 within a particular proximity of the pickup_lib of the request.
409 The proximity ('prox' column) is calculated by counting
410 the number of tree edges between the pickup_lib and either the
411 owning_lib or circ_lib of the copy that could fulfill the hold,
412 as determined by the distance_is_from_owner value of the hold matrix
413 rule controlling the hold request.
416 CREATE TABLE config.copy_status (
417 id SERIAL PRIMARY KEY,
418 name TEXT NOT NULL UNIQUE,
419 holdable BOOL NOT NULL DEFAULT FALSE,
420 opac_visible BOOL NOT NULL DEFAULT FALSE,
421 copy_active BOOL NOT NULL DEFAULT FALSE,
422 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
423 is_available BOOL NOT NULL DEFAULT FALSE
425 COMMENT ON TABLE config.copy_status IS $$
428 The available copy statuses, and whether a copy in that
429 status is available for hold request capture. 0 (zero) is
430 the only special number in this set, meaning that the item
431 is available for immediate checkout, and is counted as available
434 Statuses with an ID below 100 are not removable, and have special
435 meaning in the code. Do not change them except to translate the
438 You may add and remove statuses above 100, and these can be used
439 to remove items from normal circulation without affecting the rest
440 of the copy's values or its location.
443 CREATE TABLE config.net_access_level (
444 id SERIAL PRIMARY KEY,
445 name TEXT NOT NULL UNIQUE
447 COMMENT ON TABLE config.net_access_level IS $$
448 Patron Network Access level
450 This will be used to inform the in-library firewall of how much
451 internet access the using patron should be allowed.
455 CREATE TABLE config.remote_account (
456 id SERIAL PRIMARY KEY,
458 host TEXT NOT NULL, -- name or IP, :port optional
459 username TEXT, -- optional, since we could default to $USER
460 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
461 account TEXT, -- aka profile or FTP "account" command
462 path TEXT, -- aka directory
463 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
464 last_activity TIMESTAMP WITH TIME ZONE
467 CREATE TABLE config.marc21_rec_type_map (
468 code TEXT PRIMARY KEY,
469 type_val TEXT NOT NULL,
470 blvl_val TEXT NOT NULL
473 CREATE TABLE config.marc21_ff_pos_map (
474 id SERIAL PRIMARY KEY,
475 fixed_field TEXT NOT NULL,
477 rec_type TEXT NOT NULL,
478 start_pos INT NOT NULL,
480 default_val TEXT NOT NULL DEFAULT ' '
483 CREATE TABLE config.marc21_physical_characteristic_type_map (
484 ptype_key TEXT PRIMARY KEY,
485 label TEXT NOT NULL -- I18N
488 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
489 id SERIAL PRIMARY KEY,
490 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
491 subfield TEXT NOT NULL,
492 start_pos INT NOT NULL,
494 label TEXT NOT NULL -- I18N
497 CREATE TABLE config.marc21_physical_characteristic_value_map (
498 id SERIAL PRIMARY KEY,
500 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
501 label TEXT NOT NULL -- I18N
505 CREATE TABLE config.z3950_source (
506 name TEXT PRIMARY KEY,
507 label TEXT NOT NULL UNIQUE,
511 record_format TEXT NOT NULL DEFAULT 'FI',
512 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
513 auth BOOL NOT NULL DEFAULT TRUE,
514 use_perm INT -- REFERENCES permission.perm_list (id)
517 COMMENT ON TABLE config.z3950_source IS $$
520 Each row in this table represents a database searchable via Z39.50.
523 COMMENT ON COLUMN config.z3950_source.record_format IS $$
527 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
528 Z39.50 preferred record syntax..
531 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
532 If set, this permission is required for the source to be listed in the staff
533 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
536 CREATE TABLE config.z3950_attr (
537 id SERIAL PRIMARY KEY,
538 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
543 truncation INT NOT NULL DEFAULT 0,
544 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
547 CREATE TABLE config.z3950_source_credentials (
548 id SERIAL PRIMARY KEY,
549 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
550 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
553 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
556 CREATE TABLE config.i18n_locale (
557 code TEXT PRIMARY KEY,
558 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
559 name TEXT UNIQUE NOT NULL,
561 rtl BOOL NOT NULL DEFAULT FALSE
564 CREATE TABLE config.i18n_core (
565 id BIGSERIAL PRIMARY KEY,
566 fq_field TEXT NOT NULL,
567 identity_value TEXT NOT NULL,
568 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
572 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
574 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
578 UPDATE config.i18n_core
579 SET identity_value = $$ || quote_literal(new_ident) || $$
580 WHERE fq_field LIKE '$$ || hint || $$.%'
581 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
586 $_$ LANGUAGE PLPGSQL;
588 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
590 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
593 $_$ LANGUAGE PLPGSQL;
595 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
597 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
600 $_$ LANGUAGE PLPGSQL;
602 CREATE TABLE config.billing_type (
603 id SERIAL PRIMARY KEY,
605 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
606 default_price NUMERIC(6,2),
607 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
610 CREATE TABLE config.settings_group (
611 name TEXT PRIMARY KEY,
612 label TEXT UNIQUE NOT NULL -- I18N
615 CREATE TABLE config.org_unit_setting_type (
616 name TEXT PRIMARY KEY,
617 label TEXT UNIQUE NOT NULL,
618 grp TEXT REFERENCES config.settings_group (name),
620 datatype TEXT NOT NULL DEFAULT 'string',
625 -- define valid datatypes
627 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
628 ( 'bool', 'integer', 'float', 'currency', 'interval',
629 'date', 'string', 'object', 'array', 'link' ) ),
631 -- fm_class is meaningful only for 'link' datatype
633 CONSTRAINT coust_no_empty_link CHECK
634 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
635 ( datatype <> 'link' AND fm_class IS NULL ) )
638 CREATE TABLE config.usr_setting_type (
640 name TEXT PRIMARY KEY,
641 opac_visible BOOL NOT NULL DEFAULT FALSE,
642 label TEXT UNIQUE NOT NULL,
644 grp TEXT REFERENCES config.settings_group (name),
645 datatype TEXT NOT NULL DEFAULT 'string',
650 -- define valid datatypes
652 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
653 ( 'bool', 'integer', 'float', 'currency', 'interval',
654 'date', 'string', 'object', 'array', 'link' ) ),
657 -- fm_class is meaningful only for 'link' datatype
659 CONSTRAINT coust_no_empty_link CHECK
660 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
661 ( datatype <> 'link' AND fm_class IS NULL ) )
665 -- Some handy functions, based on existing ones, to provide optional ingest normalization
667 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
668 SELECT SUBSTRING($1,$2);
669 $func$ LANGUAGE SQL STRICT IMMUTABLE;
671 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
672 SELECT SUBSTRING($1,1,$2);
673 $func$ LANGUAGE SQL STRICT IMMUTABLE;
675 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
676 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
677 $func$ LANGUAGE SQL STRICT IMMUTABLE;
679 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
680 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
681 $func$ LANGUAGE SQL STRICT IMMUTABLE;
683 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
684 SELECT approximate_date( $1, '0');
685 $func$ LANGUAGE SQL STRICT IMMUTABLE;
687 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
688 SELECT approximate_date( $1, '9');
689 $func$ LANGUAGE SQL STRICT IMMUTABLE;
691 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
692 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
693 $func$ LANGUAGE SQL STRICT IMMUTABLE;
695 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
696 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
697 $func$ LANGUAGE SQL STRICT IMMUTABLE;
699 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
704 # Find the first ISBN, force it to ISBN13 and return it
708 foreach my $word (split(/\s/, $input)) {
709 my $isbn = Business::ISBN->new($word);
711 # First check the checksum; if it is not valid, fix it and add the original
712 # bad-checksum ISBN to the output
713 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
714 $isbn->fix_checksum();
717 # If we now have a valid ISBN, force it to ISBN13 and return it
718 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
721 $func$ LANGUAGE PLPERLU;
723 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
724 Inspired by translate_isbn1013
726 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
727 version without hypens and with a repaired checksum if the checksum was bad
731 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
736 # For each ISBN found in a single string containing a set of ISBNs:
737 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
738 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
743 foreach my $word (split(/\s/, $input)) {
744 my $isbn = Business::ISBN->new($word);
746 # First check the checksum; if it is not valid, fix it and add the original
747 # bad-checksum ISBN to the output
748 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
749 $output .= $isbn->isbn() . " ";
750 $isbn->fix_checksum();
753 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
754 # and add the normalized original ISBN to the output
755 if ($isbn && $isbn->is_valid()) {
756 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
757 $output .= $isbn->isbn . " ";
759 # If we successfully converted the ISBN to its counterpart, add the
760 # converted ISBN to the output as well
761 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
764 return $output if $output;
766 # If there were no valid ISBNs, just return the raw input
768 $func$ LANGUAGE PLPERLU;
770 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
771 The translate_isbn1013 function takes an input ISBN and returns the
772 following in a single space-delimited string if the input ISBN is valid:
773 - The normalized input ISBN (hyphens stripped)
774 - The normalized input ISBN with a fixed checksum if the checksum was bad
775 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
778 -- And ... a table in which to register them
780 CREATE TABLE config.index_normalizer (
781 id SERIAL PRIMARY KEY,
782 name TEXT UNIQUE NOT NULL,
785 param_count INT NOT NULL DEFAULT 0
788 CREATE TABLE config.metabib_field_index_norm_map (
789 id SERIAL PRIMARY KEY,
790 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
791 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
793 pos INT NOT NULL DEFAULT 0
796 CREATE TABLE config.record_attr_definition (
797 name TEXT PRIMARY KEY,
798 label TEXT NOT NULL, -- I18N
800 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
801 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
802 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
803 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
805 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
806 tag TEXT, -- LIKE format
807 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
809 -- This is used for both tag/sf and xpath entries
812 -- For xpath-extracted attrs
814 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
819 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
821 -- For phys-char fields
822 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
824 -- Source of vocabulary terms for this record attribute;
825 -- typically will be a URI referring to a SKOS vocabulary
829 CREATE TABLE config.record_attr_index_norm_map (
830 id SERIAL PRIMARY KEY,
831 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
832 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
834 pos INT NOT NULL DEFAULT 0
837 CREATE TABLE config.coded_value_map (
838 id SERIAL PRIMARY KEY,
839 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
843 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
845 is_simple BOOL NOT NULL DEFAULT FALSE,
846 concept_uri TEXT -- URI expressing the SKOS concept that the
847 -- coded value represents
850 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
852 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
853 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
854 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
855 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
856 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
857 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
858 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
860 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$
862 current_row config.coded_value_map%ROWTYPE;
864 -- Look for a current value
865 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
867 IF FOUND AND NOT add_only THEN
868 -- Update anything we were handed
869 current_row.value := COALESCE(current_row.value, in_value);
870 current_row.description := COALESCE(current_row.description, in_description);
871 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
872 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
873 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
874 UPDATE config.coded_value_map
876 value = current_row.value,
877 description = current_row.description,
878 opac_visible = current_row.opac_visible,
879 search_label = current_row.search_label,
880 is_simple = current_row.is_simple
881 WHERE id = current_row.id;
883 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
884 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
887 $f$ LANGUAGE PLPGSQL;
889 CREATE TABLE config.composite_attr_entry_definition(
890 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
891 definition TEXT NOT NULL -- JSON
894 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
895 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
896 SELECT DISTINCT l.version
897 FROM config.upgrade_log l
898 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
899 WHERE d.db_patch = $1
902 -- List applied db patches that are superseded by (and block the application of) my_db_patch
903 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
904 SELECT DISTINCT l.version
905 FROM config.upgrade_log l
906 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
907 WHERE d.db_patch = $1
910 -- List applied db patches that deprecates (and block the application of) my_db_patch
911 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
913 FROM config.db_patch_dependencies
914 WHERE ARRAY[$1]::TEXT[] && deprecates
917 -- List applied db patches that supersedes (and block the application of) my_db_patch
918 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
920 FROM config.db_patch_dependencies
921 WHERE ARRAY[$1]::TEXT[] && supersedes
924 -- Make sure that no deprecated or superseded db patches are currently applied
925 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
927 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
929 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
931 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
933 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
936 -- Raise an exception if there are, in fact, dep/sup conflict
937 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
942 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
943 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
944 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
946 Upgrade script % can not be applied:
947 applied deprecated scripts %
948 applied superseded scripts %
952 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
953 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
954 evergreen.upgrade_list_applied_deprecated(my_db_patch),
955 evergreen.upgrade_list_applied_superseded(my_db_patch);
958 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
963 CREATE TABLE config.barcode_completion (
964 id SERIAL PRIMARY KEY,
965 active BOOL NOT NULL DEFAULT true,
966 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
969 length INT NOT NULL DEFAULT 0,
971 padding_end BOOL NOT NULL DEFAULT false,
972 asset BOOL NOT NULL DEFAULT true,
973 actor BOOL NOT NULL DEFAULT true
976 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
978 -- Add support for logging, only keep the most recent five rows for each category.
981 CREATE TABLE config.org_unit_setting_type_log (
982 id BIGSERIAL PRIMARY KEY,
983 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
984 org INT, --REFERENCES actor.org_unit (id),
987 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
990 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
991 Org Unit setting Logs
993 This table contains the most recent changes to each setting
994 in actor.org_unit_setting, allowing for mistakes to be undone.
995 This is NOT meant to be an auditor, but rather an undo/redo.
998 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1000 -- Only keeps the most recent five settings changes.
1001 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1002 (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);
1004 IF (TG_OP = 'UPDATE') THEN
1006 ELSIF (TG_OP = 'INSERT') THEN
1011 $oustl_limit$ LANGUAGE plpgsql;
1013 CREATE TRIGGER limit_logs_oust
1014 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1015 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1017 CREATE TABLE config.sms_carrier (
1018 id SERIAL PRIMARY KEY,
1022 active BOOLEAN DEFAULT TRUE
1025 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1027 CREATE TABLE config.usr_activity_type (
1028 id SERIAL PRIMARY KEY,
1032 label TEXT NOT NULL, -- i18n
1033 egroup config.usr_activity_group NOT NULL,
1034 enabled BOOL NOT NULL DEFAULT TRUE,
1035 transient BOOL NOT NULL DEFAULT TRUE,
1036 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1039 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1040 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1042 CREATE TABLE config.filter_dialog_interface (
1043 key TEXT PRIMARY KEY,
1047 CREATE TABLE config.filter_dialog_filter_set (
1048 id SERIAL PRIMARY KEY,
1050 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1051 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1052 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1053 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1054 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1055 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1058 CREATE TABLE config.best_hold_order(
1059 id SERIAL PRIMARY KEY,
1060 name TEXT UNIQUE, -- i18n
1061 pprox INT, -- copy capture <-> pickup lib prox
1062 hprox INT, -- copy circ lib <-> request lib prox
1063 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1064 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1065 priority INT, -- group hold priority
1066 cut INT, -- cut-in-line
1067 depth INT, -- selection depth
1068 htime INT, -- time since last home-lib circ exceeds org-unit setting
1069 rtime INT, -- request time
1070 shtime INT -- time since copy last trip home exceeds org-unit setting
1073 -- At least one of these columns must contain a non-null value
1074 ALTER TABLE config.best_hold_order ADD CHECK ((
1075 pprox IS NOT NULL OR
1076 hprox IS NOT NULL OR
1077 aprox IS NOT NULL OR
1078 priority IS NOT NULL OR
1080 depth IS NOT NULL OR
1081 htime IS NOT NULL OR
1085 CREATE OR REPLACE FUNCTION
1086 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1089 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1095 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1098 $func$ LANGUAGE PLPGSQL STABLE;
1100 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1101 Used by a config.z3950_index_field_map constraint trigger
1102 to verify z3950_attr_type maps.
1105 -- drop these in down here since they reference config.metabib_field
1106 -- and config.record_attr_definition
1107 CREATE TABLE config.z3950_index_field_map (
1108 id SERIAL PRIMARY KEY,
1109 label TEXT NOT NULL, -- i18n
1110 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1111 record_attr TEXT REFERENCES config.record_attr_definition(name),
1112 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1113 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1114 CONSTRAINT metabib_field_or_record_attr CHECK (
1115 metabib_field IS NOT NULL OR
1116 record_attr IS NOT NULL
1118 CONSTRAINT attr_or_attr_type CHECK (
1119 z3950_attr IS NOT NULL OR
1120 z3950_attr_type IS NOT NULL
1124 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1125 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1126 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1128 CREATE TABLE config.marc_format (
1129 id SERIAL PRIMARY KEY,
1133 COMMENT ON TABLE config.marc_format IS $$
1134 List of MARC formats supported by this Evergreen
1135 database. This exists primarily as a hook for future
1136 support of UNIMARC, though whether that will ever
1137 happen remains to be seen.
1140 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1142 CREATE TABLE config.marc_field (
1143 id SERIAL PRIMARY KEY,
1144 marc_format INTEGER NOT NULL
1145 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1146 marc_record_type config.marc_record_type NOT NULL,
1147 tag CHAR(3) NOT NULL,
1150 fixed_field BOOLEAN,
1154 owner INTEGER -- REFERENCES actor.org_unit (id)
1155 -- if the owner is null, the data about the field is
1156 -- assumed to come from the controlling MARC standard
1159 COMMENT ON TABLE config.marc_field IS $$
1160 This table stores a list of MARC fields recognized by the Evergreen
1161 instance. Note that we're not aiming for completely generic ISO2709
1162 support: we're assuming things like three characters for a tag,
1163 one-character subfield labels, two indicators per variable data field,
1164 and the like, all of which are technically specializations of ISO2709.
1166 Of particular significance is the owner column; if it's set to a null
1167 value, the field definition is assumed to come from a national
1168 standards body; if it's set to a non-null value, the field definition
1169 is an OU-level addition to or override of the standard.
1172 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1173 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1175 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1176 ON config.marc_field(marc_format, marc_record_type, tag)
1177 WHERE owner IS NULL;
1178 ALTER TABLE config.marc_field
1179 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1180 CHECK ((owner IS NOT NULL) OR
1183 repeatable IS NOT NULL AND
1184 mandatory IS NOT NULL AND
1189 CREATE TABLE config.marc_subfield (
1190 id SERIAL PRIMARY KEY,
1191 marc_format INTEGER NOT NULL
1192 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1193 marc_record_type config.marc_record_type NOT NULL,
1194 tag CHAR(3) NOT NULL,
1195 code CHAR(1) NOT NULL,
1201 REFERENCES config.record_attr_definition (name)
1202 DEFERRABLE INITIALLY DEFERRED,
1203 owner INTEGER -- REFERENCES actor.org_unit (id)
1204 -- if the owner is null, the data about the subfield is
1205 -- assumed to come from the controlling MARC standard
1208 COMMENT ON TABLE config.marc_subfield IS $$
1209 This table stores the list of subfields recognized by this Evergreen
1210 instance. As with config.marc_field, of particular significance is the
1211 owner column; if it's set to a null value, the subfield definition is
1212 assumed to come from a national standards body; if it's set to a non-null
1213 value, the subfield definition is an OU-level addition to or override
1217 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1218 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1219 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1220 WHERE owner IS NULL;
1221 ALTER TABLE config.marc_subfield
1222 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1223 CHECK ((owner IS NOT NULL) OR
1226 repeatable IS NOT NULL AND
1227 mandatory IS NOT NULL AND
1232 CREATE TABLE config.copy_tag_type (
1233 code TEXT NOT NULL PRIMARY KEY,
1234 label TEXT NOT NULL,
1235 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1238 CREATE INDEX config_copy_tag_type_owner_idx
1239 ON config.copy_tag_type (owner);