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 ('serial.rematerialize_on_same_holding_code');
52 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');
54 CREATE TABLE config.global_flag (
56 ) INHERITS (config.internal_flag);
57 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
59 CREATE TABLE config.upgrade_log (
60 version TEXT PRIMARY KEY,
61 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
65 CREATE TABLE config.db_patch_dependencies (
66 db_patch TEXT PRIMARY KEY,
71 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
77 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
79 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
85 CREATE TRIGGER no_overlapping_sups
86 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
87 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
89 CREATE TRIGGER no_overlapping_deps
90 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
91 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
93 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1072', :eg_version); -- gmcharlt/kmlussier
95 CREATE TABLE config.bib_source (
96 id SERIAL PRIMARY KEY,
97 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
98 source TEXT NOT NULL UNIQUE,
99 transcendant BOOL NOT NULL DEFAULT FALSE,
100 can_have_copies BOOL NOT NULL DEFAULT TRUE
102 COMMENT ON TABLE config.bib_source IS $$
103 This is table is used to set up the relative "quality" of each
104 MARC source, such as OCLC. Also identifies "transcendant" sources,
105 i.e., sources of bib records that should display in the OPAC
106 even if no copies or located URIs are attached. Also indicates if
107 the source is allowed to have actual copies on its bibs. Volumes
108 for targeted URIs are unaffected by this setting.
111 CREATE TABLE config.standing (
112 id SERIAL PRIMARY KEY,
113 value TEXT NOT NULL UNIQUE
115 COMMENT ON TABLE config.standing IS $$
118 This table contains the values that can be applied to a patron
119 by a staff member. These values should not be changed, other
120 than for translation, as the ID column is currently a "magic
121 number" in the source. :(
124 CREATE TABLE config.standing_penalty (
125 id SERIAL PRIMARY KEY,
126 name TEXT NOT NULL UNIQUE,
129 staff_alert BOOL NOT NULL DEFAULT FALSE,
131 ignore_proximity INTEGER
134 CREATE TABLE config.xml_transform (
135 name TEXT PRIMARY KEY,
136 namespace_uri TEXT NOT NULL,
137 prefix TEXT NOT NULL,
141 CREATE TABLE config.biblio_fingerprint (
142 id SERIAL PRIMARY KEY,
145 first_word BOOL NOT NULL DEFAULT FALSE,
146 format TEXT NOT NULL DEFAULT 'marcxml'
149 INSERT INTO config.biblio_fingerprint (name, xpath, format)
152 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
153 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
154 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
155 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
160 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
163 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
164 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
165 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
172 INSERT INTO config.biblio_fingerprint (name, xpath, format)
175 '//mods32:mods/mods32:titleInfo/mods32:partName',
179 INSERT INTO config.biblio_fingerprint (name, xpath, format)
182 '//mods32:mods/mods32:titleInfo/mods32:partNumber',
186 CREATE TABLE config.metabib_class (
187 name TEXT PRIMARY KEY,
188 label TEXT NOT NULL UNIQUE,
189 buoyant BOOL DEFAULT FALSE NOT NULL,
190 restrict BOOL DEFAULT FALSE NOT NULL,
191 combined BOOL DEFAULT FALSE NOT NULL,
192 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
193 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
194 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
195 d_weight NUMERIC DEFAULT 0.1 NOT NULL
198 CREATE TABLE config.metabib_field (
199 id SERIAL PRIMARY KEY,
200 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
204 weight INT NOT NULL DEFAULT 1,
205 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
206 search_field BOOL NOT NULL DEFAULT TRUE,
207 facet_field BOOL NOT NULL DEFAULT FALSE,
208 browse_field BOOL NOT NULL DEFAULT TRUE,
210 browse_sort_xpath TEXT,
213 authority_xpath TEXT,
215 restrict BOOL DEFAULT FALSE NOT NULL,
216 display_field BOOL NOT NULL DEFAULT TRUE
218 COMMENT ON TABLE config.metabib_field IS $$
219 XPath used for record indexing ingest
221 This table contains the XPath used to chop up MODS into its
222 indexable parts. Each XPath entry is named and assigned to
223 a "class" of either title, subject, author, keyword, series
227 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
229 CREATE TABLE config.ts_config_list (
233 COMMENT ON TABLE config.ts_config_list IS $$
236 A list of full text configs with names and descriptions.
239 CREATE TABLE config.metabib_class_ts_map (
240 id SERIAL PRIMARY KEY,
241 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
242 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
243 active BOOL NOT NULL DEFAULT TRUE,
244 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
245 index_lang TEXT NULL,
246 search_lang TEXT NULL,
247 always BOOL NOT NULL DEFAULT true
249 COMMENT ON TABLE config.metabib_class_ts_map IS $$
250 Text Search Configs for metabib class indexing
252 This table contains text search config definitions for
253 storing index_vector values.
256 CREATE TABLE config.metabib_field_ts_map (
257 id SERIAL PRIMARY KEY,
258 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
259 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
260 active BOOL NOT NULL DEFAULT TRUE,
261 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
262 index_lang TEXT NULL,
263 search_lang TEXT NULL
265 COMMENT ON TABLE config.metabib_field_ts_map IS $$
266 Text Search Configs for metabib field indexing
268 This table contains text search config definitions for
269 storing index_vector values.
272 CREATE TABLE config.metabib_search_alias (
273 alias TEXT PRIMARY KEY,
274 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
275 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
278 CREATE TABLE config.non_cataloged_type (
279 id SERIAL PRIMARY KEY,
280 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
282 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
283 in_house BOOL NOT NULL DEFAULT FALSE,
284 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
286 COMMENT ON TABLE config.non_cataloged_type IS $$
287 Types of valid non-cataloged items.
290 CREATE TABLE config.identification_type (
291 id SERIAL PRIMARY KEY,
292 name TEXT NOT NULL UNIQUE
294 COMMENT ON TABLE config.identification_type IS $$
295 Types of valid patron identification.
297 Each patron must display at least one valid form of identification
298 in order to get a library card. This table lists those forms.
301 CREATE TABLE config.rule_circ_duration (
302 id SERIAL PRIMARY KEY,
303 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
304 extended INTERVAL NOT NULL,
305 normal INTERVAL NOT NULL,
306 shrt INTERVAL NOT NULL,
307 max_renewals INT NOT NULL
309 COMMENT ON TABLE config.rule_circ_duration IS $$
310 Circulation Duration rules
312 Each circulation is given a duration based on one of these rules.
315 CREATE TABLE config.hard_due_date (
316 id SERIAL PRIMARY KEY,
317 name TEXT NOT NULL UNIQUE,
318 ceiling_date TIMESTAMPTZ NOT NULL,
319 forceto BOOL NOT NULL,
320 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
323 CREATE TABLE config.hard_due_date_values (
324 id SERIAL PRIMARY KEY,
325 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
326 DEFERRABLE INITIALLY DEFERRED,
327 ceiling_date TIMESTAMPTZ NOT NULL,
328 active_date TIMESTAMPTZ NOT NULL
331 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
333 temp_value config.hard_due_date_values%ROWTYPE;
337 SELECT DISTINCT ON (hard_due_date) *
338 FROM config.hard_due_date_values
339 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
340 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
342 UPDATE config.hard_due_date
343 SET ceiling_date = temp_value.ceiling_date
344 WHERE id = temp_value.hard_due_date
345 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
346 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
349 updated := updated + 1;
355 $func$ LANGUAGE plpgsql;
357 CREATE TABLE config.rule_max_fine (
358 id SERIAL PRIMARY KEY,
359 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
360 amount NUMERIC(6,2) NOT NULL,
361 is_percent BOOL NOT NULL DEFAULT FALSE
363 COMMENT ON TABLE config.rule_max_fine IS $$
364 Circulation Max Fine rules
366 Each circulation is given a maximum fine based on one of
370 CREATE TABLE config.rule_recurring_fine (
371 id SERIAL PRIMARY KEY,
372 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
373 high NUMERIC(6,2) NOT NULL,
374 normal NUMERIC(6,2) NOT NULL,
375 low NUMERIC(6,2) NOT NULL,
376 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
377 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
379 COMMENT ON TABLE config.rule_recurring_fine IS $$
380 Circulation Recurring Fine rules
382 Each circulation is given a recurring fine amount based on one of
383 these rules. Note that it is recommended to run the fine generator
384 (from cron) at least as frequently as the lowest recurrence interval
385 used by your circulation rules so that accrued fines will be up
390 CREATE TABLE config.rule_age_hold_protect (
391 id SERIAL PRIMARY KEY,
392 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
393 age INTERVAL NOT NULL,
396 COMMENT ON TABLE config.rule_age_hold_protect IS $$
397 Hold Item Age Protection rules
399 A hold request can only capture new(ish) items when they are
400 within a particular proximity of the pickup_lib of the request.
401 The proximity ('prox' column) is calculated by counting
402 the number of tree edges between the pickup_lib and either the
403 owning_lib or circ_lib of the copy that could fulfill the hold,
404 as determined by the distance_is_from_owner value of the hold matrix
405 rule controlling the hold request.
408 CREATE TABLE config.copy_status (
409 id SERIAL PRIMARY KEY,
410 name TEXT NOT NULL UNIQUE,
411 holdable BOOL NOT NULL DEFAULT FALSE,
412 opac_visible BOOL NOT NULL DEFAULT FALSE,
413 copy_active BOOL NOT NULL DEFAULT FALSE,
414 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
415 is_available BOOL NOT NULL DEFAULT FALSE
417 COMMENT ON TABLE config.copy_status IS $$
420 The available copy statuses, and whether a copy in that
421 status is available for hold request capture. 0 (zero) is
422 the only special number in this set, meaning that the item
423 is available for immediate checkout, and is counted as available
426 Statuses with an ID below 100 are not removable, and have special
427 meaning in the code. Do not change them except to translate the
430 You may add and remove statuses above 100, and these can be used
431 to remove items from normal circulation without affecting the rest
432 of the copy's values or its location.
435 CREATE TABLE config.net_access_level (
436 id SERIAL PRIMARY KEY,
437 name TEXT NOT NULL UNIQUE
439 COMMENT ON TABLE config.net_access_level IS $$
440 Patron Network Access level
442 This will be used to inform the in-library firewall of how much
443 internet access the using patron should be allowed.
447 CREATE TABLE config.remote_account (
448 id SERIAL PRIMARY KEY,
450 host TEXT NOT NULL, -- name or IP, :port optional
451 username TEXT, -- optional, since we could default to $USER
452 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
453 account TEXT, -- aka profile or FTP "account" command
454 path TEXT, -- aka directory
455 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
456 last_activity TIMESTAMP WITH TIME ZONE
459 CREATE TABLE config.marc21_rec_type_map (
460 code TEXT PRIMARY KEY,
461 type_val TEXT NOT NULL,
462 blvl_val TEXT NOT NULL
465 CREATE TABLE config.marc21_ff_pos_map (
466 id SERIAL PRIMARY KEY,
467 fixed_field TEXT NOT NULL,
469 rec_type TEXT NOT NULL,
470 start_pos INT NOT NULL,
472 default_val TEXT NOT NULL DEFAULT ' '
475 CREATE TABLE config.marc21_physical_characteristic_type_map (
476 ptype_key TEXT PRIMARY KEY,
477 label TEXT NOT NULL -- I18N
480 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
481 id SERIAL PRIMARY KEY,
482 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
483 subfield TEXT NOT NULL,
484 start_pos INT NOT NULL,
486 label TEXT NOT NULL -- I18N
489 CREATE TABLE config.marc21_physical_characteristic_value_map (
490 id SERIAL PRIMARY KEY,
492 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
493 label TEXT NOT NULL -- I18N
497 CREATE TABLE config.z3950_source (
498 name TEXT PRIMARY KEY,
499 label TEXT NOT NULL UNIQUE,
503 record_format TEXT NOT NULL DEFAULT 'FI',
504 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
505 auth BOOL NOT NULL DEFAULT TRUE,
506 use_perm INT -- REFERENCES permission.perm_list (id)
509 COMMENT ON TABLE config.z3950_source IS $$
512 Each row in this table represents a database searchable via Z39.50.
515 COMMENT ON COLUMN config.z3950_source.record_format IS $$
519 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
520 Z39.50 preferred record syntax..
523 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
524 If set, this permission is required for the source to be listed in the staff
525 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
528 CREATE TABLE config.z3950_attr (
529 id SERIAL PRIMARY KEY,
530 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
535 truncation INT NOT NULL DEFAULT 0,
536 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
539 CREATE TABLE config.z3950_source_credentials (
540 id SERIAL PRIMARY KEY,
541 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
542 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
545 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
548 CREATE TABLE config.i18n_locale (
549 code TEXT PRIMARY KEY,
550 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
551 name TEXT UNIQUE NOT NULL,
553 rtl BOOL NOT NULL DEFAULT FALSE
556 CREATE TABLE config.i18n_core (
557 id BIGSERIAL PRIMARY KEY,
558 fq_field TEXT NOT NULL,
559 identity_value TEXT NOT NULL,
560 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
564 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
566 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
570 UPDATE config.i18n_core
571 SET identity_value = $$ || quote_literal(new_ident) || $$
572 WHERE fq_field LIKE '$$ || hint || $$.%'
573 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
578 $_$ LANGUAGE PLPGSQL;
580 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
582 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
585 $_$ LANGUAGE PLPGSQL;
587 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
589 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
592 $_$ LANGUAGE PLPGSQL;
594 CREATE TABLE config.billing_type (
595 id SERIAL PRIMARY KEY,
597 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
598 default_price NUMERIC(6,2),
599 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
602 CREATE TABLE config.settings_group (
603 name TEXT PRIMARY KEY,
604 label TEXT UNIQUE NOT NULL -- I18N
607 CREATE TABLE config.org_unit_setting_type (
608 name TEXT PRIMARY KEY,
609 label TEXT UNIQUE NOT NULL,
610 grp TEXT REFERENCES config.settings_group (name),
612 datatype TEXT NOT NULL DEFAULT 'string',
617 -- define valid datatypes
619 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
620 ( 'bool', 'integer', 'float', 'currency', 'interval',
621 'date', 'string', 'object', 'array', 'link' ) ),
623 -- fm_class is meaningful only for 'link' datatype
625 CONSTRAINT coust_no_empty_link CHECK
626 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
627 ( datatype <> 'link' AND fm_class IS NULL ) )
630 CREATE TABLE config.usr_setting_type (
632 name TEXT PRIMARY KEY,
633 opac_visible BOOL NOT NULL DEFAULT FALSE,
634 label TEXT UNIQUE NOT NULL,
636 grp TEXT REFERENCES config.settings_group (name),
637 datatype TEXT NOT NULL DEFAULT 'string',
642 -- define valid datatypes
644 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
645 ( 'bool', 'integer', 'float', 'currency', 'interval',
646 'date', 'string', 'object', 'array', 'link' ) ),
649 -- fm_class is meaningful only for 'link' datatype
651 CONSTRAINT coust_no_empty_link CHECK
652 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
653 ( datatype <> 'link' AND fm_class IS NULL ) )
657 -- Some handy functions, based on existing ones, to provide optional ingest normalization
659 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
660 SELECT SUBSTRING($1,$2);
661 $func$ LANGUAGE SQL STRICT IMMUTABLE;
663 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
664 SELECT SUBSTRING($1,1,$2);
665 $func$ LANGUAGE SQL STRICT IMMUTABLE;
667 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
668 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
669 $func$ LANGUAGE SQL STRICT IMMUTABLE;
671 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
672 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
673 $func$ LANGUAGE SQL STRICT IMMUTABLE;
675 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
676 SELECT approximate_date( $1, '0');
677 $func$ LANGUAGE SQL STRICT IMMUTABLE;
679 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
680 SELECT approximate_date( $1, '9');
681 $func$ LANGUAGE SQL STRICT IMMUTABLE;
683 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
684 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
685 $func$ LANGUAGE SQL STRICT IMMUTABLE;
687 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
688 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
689 $func$ LANGUAGE SQL STRICT IMMUTABLE;
691 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
696 # Find the first ISBN, force it to ISBN13 and return it
700 foreach my $word (split(/\s/, $input)) {
701 my $isbn = Business::ISBN->new($word);
703 # First check the checksum; if it is not valid, fix it and add the original
704 # bad-checksum ISBN to the output
705 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
706 $isbn->fix_checksum();
709 # If we now have a valid ISBN, force it to ISBN13 and return it
710 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
713 $func$ LANGUAGE PLPERLU;
715 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
716 Inspired by translate_isbn1013
718 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
719 version without hypens and with a repaired checksum if the checksum was bad
723 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
728 # For each ISBN found in a single string containing a set of ISBNs:
729 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
730 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
735 foreach my $word (split(/\s/, $input)) {
736 my $isbn = Business::ISBN->new($word);
738 # First check the checksum; if it is not valid, fix it and add the original
739 # bad-checksum ISBN to the output
740 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
741 $output .= $isbn->isbn() . " ";
742 $isbn->fix_checksum();
745 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
746 # and add the normalized original ISBN to the output
747 if ($isbn && $isbn->is_valid()) {
748 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
749 $output .= $isbn->isbn . " ";
751 # If we successfully converted the ISBN to its counterpart, add the
752 # converted ISBN to the output as well
753 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
756 return $output if $output;
758 # If there were no valid ISBNs, just return the raw input
760 $func$ LANGUAGE PLPERLU;
762 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
763 The translate_isbn1013 function takes an input ISBN and returns the
764 following in a single space-delimited string if the input ISBN is valid:
765 - The normalized input ISBN (hyphens stripped)
766 - The normalized input ISBN with a fixed checksum if the checksum was bad
767 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
770 -- And ... a table in which to register them
772 CREATE TABLE config.index_normalizer (
773 id SERIAL PRIMARY KEY,
774 name TEXT UNIQUE NOT NULL,
777 param_count INT NOT NULL DEFAULT 0
780 CREATE TABLE config.metabib_field_index_norm_map (
781 id SERIAL PRIMARY KEY,
782 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
783 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
785 pos INT NOT NULL DEFAULT 0
788 CREATE TABLE config.record_attr_definition (
789 name TEXT PRIMARY KEY,
790 label TEXT NOT NULL, -- I18N
792 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
793 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
794 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
795 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
797 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
798 tag TEXT, -- LIKE format
799 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
801 -- This is used for both tag/sf and xpath entries
804 -- For xpath-extracted attrs
806 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
811 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
813 -- For phys-char fields
814 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
816 -- Source of vocabulary terms for this record attribute;
817 -- typically will be a URI referring to a SKOS vocabulary
821 CREATE TABLE config.record_attr_index_norm_map (
822 id SERIAL PRIMARY KEY,
823 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
824 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
826 pos INT NOT NULL DEFAULT 0
829 CREATE TABLE config.coded_value_map (
830 id SERIAL PRIMARY KEY,
831 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
835 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
837 is_simple BOOL NOT NULL DEFAULT FALSE,
838 concept_uri TEXT -- URI expressing the SKOS concept that the
839 -- coded value represents
842 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
844 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
845 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
846 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
847 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
848 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
849 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
850 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
852 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$
854 current_row config.coded_value_map%ROWTYPE;
856 -- Look for a current value
857 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
859 IF FOUND AND NOT add_only THEN
860 -- Update anything we were handed
861 current_row.value := COALESCE(current_row.value, in_value);
862 current_row.description := COALESCE(current_row.description, in_description);
863 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
864 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
865 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
866 UPDATE config.coded_value_map
868 value = current_row.value,
869 description = current_row.description,
870 opac_visible = current_row.opac_visible,
871 search_label = current_row.search_label,
872 is_simple = current_row.is_simple
873 WHERE id = current_row.id;
875 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
876 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
879 $f$ LANGUAGE PLPGSQL;
881 CREATE TABLE config.composite_attr_entry_definition(
882 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
883 definition TEXT NOT NULL -- JSON
886 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
887 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
888 SELECT DISTINCT l.version
889 FROM config.upgrade_log l
890 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
891 WHERE d.db_patch = $1
894 -- List applied db patches that are superseded by (and block the application of) my_db_patch
895 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( 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.supersedes))
899 WHERE d.db_patch = $1
902 -- List applied db patches that deprecates (and block the application of) my_db_patch
903 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
905 FROM config.db_patch_dependencies
906 WHERE ARRAY[$1]::TEXT[] && deprecates
909 -- List applied db patches that supersedes (and block the application of) my_db_patch
910 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
912 FROM config.db_patch_dependencies
913 WHERE ARRAY[$1]::TEXT[] && supersedes
916 -- Make sure that no deprecated or superseded db patches are currently applied
917 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
919 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
921 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
923 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
925 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
928 -- Raise an exception if there are, in fact, dep/sup conflict
929 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
934 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
935 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
936 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
938 Upgrade script % can not be applied:
939 applied deprecated scripts %
940 applied superseded scripts %
944 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
945 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
946 evergreen.upgrade_list_applied_deprecated(my_db_patch),
947 evergreen.upgrade_list_applied_superseded(my_db_patch);
950 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
955 CREATE TABLE config.barcode_completion (
956 id SERIAL PRIMARY KEY,
957 active BOOL NOT NULL DEFAULT true,
958 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
961 length INT NOT NULL DEFAULT 0,
963 padding_end BOOL NOT NULL DEFAULT false,
964 asset BOOL NOT NULL DEFAULT true,
965 actor BOOL NOT NULL DEFAULT true
968 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
970 -- Add support for logging, only keep the most recent five rows for each category.
973 CREATE TABLE config.org_unit_setting_type_log (
974 id BIGSERIAL PRIMARY KEY,
975 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
976 org INT, --REFERENCES actor.org_unit (id),
979 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
982 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
983 Org Unit setting Logs
985 This table contains the most recent changes to each setting
986 in actor.org_unit_setting, allowing for mistakes to be undone.
987 This is NOT meant to be an auditor, but rather an undo/redo.
990 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
992 -- Only keeps the most recent five settings changes.
993 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
994 (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);
996 IF (TG_OP = 'UPDATE') THEN
998 ELSIF (TG_OP = 'INSERT') THEN
1003 $oustl_limit$ LANGUAGE plpgsql;
1005 CREATE TRIGGER limit_logs_oust
1006 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1007 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1009 CREATE TABLE config.sms_carrier (
1010 id SERIAL PRIMARY KEY,
1014 active BOOLEAN DEFAULT TRUE
1017 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1019 CREATE TABLE config.usr_activity_type (
1020 id SERIAL PRIMARY KEY,
1024 label TEXT NOT NULL, -- i18n
1025 egroup config.usr_activity_group NOT NULL,
1026 enabled BOOL NOT NULL DEFAULT TRUE,
1027 transient BOOL NOT NULL DEFAULT TRUE,
1028 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1031 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1032 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1034 CREATE TABLE config.filter_dialog_interface (
1035 key TEXT PRIMARY KEY,
1039 CREATE TABLE config.filter_dialog_filter_set (
1040 id SERIAL PRIMARY KEY,
1042 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1043 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1044 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1045 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1046 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1047 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1050 CREATE TABLE config.best_hold_order(
1051 id SERIAL PRIMARY KEY,
1052 name TEXT UNIQUE, -- i18n
1053 pprox INT, -- copy capture <-> pickup lib prox
1054 hprox INT, -- copy circ lib <-> request lib prox
1055 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1056 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1057 priority INT, -- group hold priority
1058 cut INT, -- cut-in-line
1059 depth INT, -- selection depth
1060 htime INT, -- time since last home-lib circ exceeds org-unit setting
1061 rtime INT, -- request time
1062 shtime INT -- time since copy last trip home exceeds org-unit setting
1065 -- At least one of these columns must contain a non-null value
1066 ALTER TABLE config.best_hold_order ADD CHECK ((
1067 pprox IS NOT NULL OR
1068 hprox IS NOT NULL OR
1069 aprox IS NOT NULL OR
1070 priority IS NOT NULL OR
1072 depth IS NOT NULL OR
1073 htime IS NOT NULL OR
1077 CREATE OR REPLACE FUNCTION
1078 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1081 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1087 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1090 $func$ LANGUAGE PLPGSQL STABLE;
1092 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1093 Used by a config.z3950_index_field_map constraint trigger
1094 to verify z3950_attr_type maps.
1097 -- drop these in down here since they reference config.metabib_field
1098 -- and config.record_attr_definition
1099 CREATE TABLE config.z3950_index_field_map (
1100 id SERIAL PRIMARY KEY,
1101 label TEXT NOT NULL, -- i18n
1102 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1103 record_attr TEXT REFERENCES config.record_attr_definition(name),
1104 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1105 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1106 CONSTRAINT metabib_field_or_record_attr CHECK (
1107 metabib_field IS NOT NULL OR
1108 record_attr IS NOT NULL
1110 CONSTRAINT attr_or_attr_type CHECK (
1111 z3950_attr IS NOT NULL OR
1112 z3950_attr_type IS NOT NULL
1116 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1117 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1118 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1120 CREATE TABLE config.marc_format (
1121 id SERIAL PRIMARY KEY,
1125 COMMENT ON TABLE config.marc_format IS $$
1126 List of MARC formats supported by this Evergreen
1127 database. This exists primarily as a hook for future
1128 support of UNIMARC, though whether that will ever
1129 happen remains to be seen.
1132 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1134 CREATE TABLE config.marc_field (
1135 id SERIAL PRIMARY KEY,
1136 marc_format INTEGER NOT NULL
1137 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1138 marc_record_type config.marc_record_type NOT NULL,
1139 tag CHAR(3) NOT NULL,
1142 fixed_field BOOLEAN,
1146 owner INTEGER -- REFERENCES actor.org_unit (id)
1147 -- if the owner is null, the data about the field is
1148 -- assumed to come from the controlling MARC standard
1151 COMMENT ON TABLE config.marc_field IS $$
1152 This table stores a list of MARC fields recognized by the Evergreen
1153 instance. Note that we're not aiming for completely generic ISO2709
1154 support: we're assuming things like three characters for a tag,
1155 one-character subfield labels, two indicators per variable data field,
1156 and the like, all of which are technically specializations of ISO2709.
1158 Of particular significance is the owner column; if it's set to a null
1159 value, the field definition is assumed to come from a national
1160 standards body; if it's set to a non-null value, the field definition
1161 is an OU-level addition to or override of the standard.
1164 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1165 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1167 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1168 ON config.marc_field(marc_format, marc_record_type, tag)
1169 WHERE owner IS NULL;
1170 ALTER TABLE config.marc_field
1171 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1172 CHECK ((owner IS NOT NULL) OR
1175 repeatable IS NOT NULL AND
1176 mandatory IS NOT NULL AND
1181 CREATE TABLE config.marc_subfield (
1182 id SERIAL PRIMARY KEY,
1183 marc_format INTEGER NOT NULL
1184 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1185 marc_record_type config.marc_record_type NOT NULL,
1186 tag CHAR(3) NOT NULL,
1187 code CHAR(1) NOT NULL,
1193 REFERENCES config.record_attr_definition (name)
1194 DEFERRABLE INITIALLY DEFERRED,
1195 owner INTEGER -- REFERENCES actor.org_unit (id)
1196 -- if the owner is null, the data about the subfield is
1197 -- assumed to come from the controlling MARC standard
1200 COMMENT ON TABLE config.marc_subfield IS $$
1201 This table stores the list of subfields recognized by this Evergreen
1202 instance. As with config.marc_field, of particular significance is the
1203 owner column; if it's set to a null value, the subfield definition is
1204 assumed to come from a national standards body; if it's set to a non-null
1205 value, the subfield definition is an OU-level addition to or override
1209 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1210 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1211 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1212 WHERE owner IS NULL;
1213 ALTER TABLE config.marc_subfield
1214 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1215 CHECK ((owner IS NOT NULL) OR
1218 repeatable IS NOT NULL AND
1219 mandatory IS NOT NULL AND
1224 CREATE TABLE config.copy_tag_type (
1225 code TEXT NOT NULL PRIMARY KEY,
1226 label TEXT NOT NULL,
1227 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1230 CREATE INDEX config_copy_tag_type_owner_idx
1231 ON config.copy_tag_type (owner);