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,
212 authority_xpath TEXT,
214 restrict BOOL DEFAULT FALSE NOT NULL,
215 display_field BOOL NOT NULL DEFAULT TRUE
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
345 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
348 updated := updated + 1;
354 $func$ LANGUAGE plpgsql;
356 CREATE TABLE config.rule_max_fine (
357 id SERIAL PRIMARY KEY,
358 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
359 amount NUMERIC(6,2) NOT NULL,
360 is_percent BOOL NOT NULL DEFAULT FALSE
362 COMMENT ON TABLE config.rule_max_fine IS $$
363 Circulation Max Fine rules
365 Each circulation is given a maximum fine based on one of
369 CREATE TABLE config.rule_recurring_fine (
370 id SERIAL PRIMARY KEY,
371 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
372 high NUMERIC(6,2) NOT NULL,
373 normal NUMERIC(6,2) NOT NULL,
374 low NUMERIC(6,2) NOT NULL,
375 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
376 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
378 COMMENT ON TABLE config.rule_recurring_fine IS $$
379 Circulation Recurring Fine rules
381 Each circulation is given a recurring fine amount based on one of
382 these rules. Note that it is recommended to run the fine generator
383 (from cron) at least as frequently as the lowest recurrence interval
384 used by your circulation rules so that accrued fines will be up
389 CREATE TABLE config.rule_age_hold_protect (
390 id SERIAL PRIMARY KEY,
391 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
392 age INTERVAL NOT NULL,
395 COMMENT ON TABLE config.rule_age_hold_protect IS $$
396 Hold Item Age Protection rules
398 A hold request can only capture new(ish) items when they are
399 within a particular proximity of the pickup_lib of the request.
400 The proximity ('prox' column) is calculated by counting
401 the number of tree edges between the pickup_lib and either the
402 owning_lib or circ_lib of the copy that could fulfill the hold,
403 as determined by the distance_is_from_owner value of the hold matrix
404 rule controlling the hold request.
407 CREATE TABLE config.copy_status (
408 id SERIAL PRIMARY KEY,
409 name TEXT NOT NULL UNIQUE,
410 holdable BOOL NOT NULL DEFAULT FALSE,
411 opac_visible BOOL NOT NULL DEFAULT FALSE,
412 copy_active BOOL NOT NULL DEFAULT FALSE,
413 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
414 is_available BOOL NOT NULL DEFAULT FALSE
416 COMMENT ON TABLE config.copy_status IS $$
419 The available copy statuses, and whether a copy in that
420 status is available for hold request capture. 0 (zero) is
421 the only special number in this set, meaning that the item
422 is available for immediate checkout, and is counted as available
425 Statuses with an ID below 100 are not removable, and have special
426 meaning in the code. Do not change them except to translate the
429 You may add and remove statuses above 100, and these can be used
430 to remove items from normal circulation without affecting the rest
431 of the copy's values or its location.
434 CREATE TABLE config.net_access_level (
435 id SERIAL PRIMARY KEY,
436 name TEXT NOT NULL UNIQUE
438 COMMENT ON TABLE config.net_access_level IS $$
439 Patron Network Access level
441 This will be used to inform the in-library firewall of how much
442 internet access the using patron should be allowed.
446 CREATE TABLE config.remote_account (
447 id SERIAL PRIMARY KEY,
449 host TEXT NOT NULL, -- name or IP, :port optional
450 username TEXT, -- optional, since we could default to $USER
451 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
452 account TEXT, -- aka profile or FTP "account" command
453 path TEXT, -- aka directory
454 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
455 last_activity TIMESTAMP WITH TIME ZONE
458 CREATE TABLE config.marc21_rec_type_map (
459 code TEXT PRIMARY KEY,
460 type_val TEXT NOT NULL,
461 blvl_val TEXT NOT NULL
464 CREATE TABLE config.marc21_ff_pos_map (
465 id SERIAL PRIMARY KEY,
466 fixed_field TEXT NOT NULL,
468 rec_type TEXT NOT NULL,
469 start_pos INT NOT NULL,
471 default_val TEXT NOT NULL DEFAULT ' '
474 CREATE TABLE config.marc21_physical_characteristic_type_map (
475 ptype_key TEXT PRIMARY KEY,
476 label TEXT NOT NULL -- I18N
479 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
480 id SERIAL PRIMARY KEY,
481 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
482 subfield TEXT NOT NULL,
483 start_pos INT NOT NULL,
485 label TEXT NOT NULL -- I18N
488 CREATE TABLE config.marc21_physical_characteristic_value_map (
489 id SERIAL PRIMARY KEY,
491 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
492 label TEXT NOT NULL -- I18N
496 CREATE TABLE config.z3950_source (
497 name TEXT PRIMARY KEY,
498 label TEXT NOT NULL UNIQUE,
502 record_format TEXT NOT NULL DEFAULT 'FI',
503 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
504 auth BOOL NOT NULL DEFAULT TRUE,
505 use_perm INT -- REFERENCES permission.perm_list (id)
508 COMMENT ON TABLE config.z3950_source IS $$
511 Each row in this table represents a database searchable via Z39.50.
514 COMMENT ON COLUMN config.z3950_source.record_format IS $$
518 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
519 Z39.50 preferred record syntax..
522 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
523 If set, this permission is required for the source to be listed in the staff
524 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
527 CREATE TABLE config.z3950_attr (
528 id SERIAL PRIMARY KEY,
529 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
534 truncation INT NOT NULL DEFAULT 0,
535 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
538 CREATE TABLE config.z3950_source_credentials (
539 id SERIAL PRIMARY KEY,
540 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
541 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
544 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
547 CREATE TABLE config.i18n_locale (
548 code TEXT PRIMARY KEY,
549 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
550 name TEXT UNIQUE NOT NULL,
552 rtl BOOL NOT NULL DEFAULT FALSE
555 CREATE TABLE config.i18n_core (
556 id BIGSERIAL PRIMARY KEY,
557 fq_field TEXT NOT NULL,
558 identity_value TEXT NOT NULL,
559 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
563 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
565 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
569 UPDATE config.i18n_core
570 SET identity_value = $$ || quote_literal(new_ident) || $$
571 WHERE fq_field LIKE '$$ || hint || $$.%'
572 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
577 $_$ LANGUAGE PLPGSQL;
579 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
581 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
584 $_$ LANGUAGE PLPGSQL;
586 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
588 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
591 $_$ LANGUAGE PLPGSQL;
593 CREATE TABLE config.billing_type (
594 id SERIAL PRIMARY KEY,
596 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
597 default_price NUMERIC(6,2),
598 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
601 CREATE TABLE config.settings_group (
602 name TEXT PRIMARY KEY,
603 label TEXT UNIQUE NOT NULL -- I18N
606 CREATE TABLE config.org_unit_setting_type (
607 name TEXT PRIMARY KEY,
608 label TEXT UNIQUE NOT NULL,
609 grp TEXT REFERENCES config.settings_group (name),
611 datatype TEXT NOT NULL DEFAULT 'string',
616 -- define valid datatypes
618 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
619 ( 'bool', 'integer', 'float', 'currency', 'interval',
620 'date', 'string', 'object', 'array', 'link' ) ),
622 -- fm_class is meaningful only for 'link' datatype
624 CONSTRAINT coust_no_empty_link CHECK
625 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
626 ( datatype <> 'link' AND fm_class IS NULL ) )
629 CREATE TABLE config.usr_setting_type (
631 name TEXT PRIMARY KEY,
632 opac_visible BOOL NOT NULL DEFAULT FALSE,
633 label TEXT UNIQUE NOT NULL,
635 grp TEXT REFERENCES config.settings_group (name),
636 datatype TEXT NOT NULL DEFAULT 'string',
641 -- define valid datatypes
643 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
644 ( 'bool', 'integer', 'float', 'currency', 'interval',
645 'date', 'string', 'object', 'array', 'link' ) ),
648 -- fm_class is meaningful only for 'link' datatype
650 CONSTRAINT coust_no_empty_link CHECK
651 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
652 ( datatype <> 'link' AND fm_class IS NULL ) )
656 -- Some handy functions, based on existing ones, to provide optional ingest normalization
658 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
659 SELECT SUBSTRING($1,$2);
660 $func$ LANGUAGE SQL STRICT IMMUTABLE;
662 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
663 SELECT SUBSTRING($1,1,$2);
664 $func$ LANGUAGE SQL STRICT IMMUTABLE;
666 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
667 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
668 $func$ LANGUAGE SQL STRICT IMMUTABLE;
670 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
671 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
672 $func$ LANGUAGE SQL STRICT IMMUTABLE;
674 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
675 SELECT approximate_date( $1, '0');
676 $func$ LANGUAGE SQL STRICT IMMUTABLE;
678 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
679 SELECT approximate_date( $1, '9');
680 $func$ LANGUAGE SQL STRICT IMMUTABLE;
682 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
683 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
684 $func$ LANGUAGE SQL STRICT IMMUTABLE;
686 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
687 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
688 $func$ LANGUAGE SQL STRICT IMMUTABLE;
690 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
695 # Find the first ISBN, force it to ISBN13 and return it
699 foreach my $word (split(/\s/, $input)) {
700 my $isbn = Business::ISBN->new($word);
702 # First check the checksum; if it is not valid, fix it and add the original
703 # bad-checksum ISBN to the output
704 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
705 $isbn->fix_checksum();
708 # If we now have a valid ISBN, force it to ISBN13 and return it
709 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
712 $func$ LANGUAGE PLPERLU;
714 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
715 Inspired by translate_isbn1013
717 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
718 version without hypens and with a repaired checksum if the checksum was bad
722 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
727 # For each ISBN found in a single string containing a set of ISBNs:
728 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
729 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
734 foreach my $word (split(/\s/, $input)) {
735 my $isbn = Business::ISBN->new($word);
737 # First check the checksum; if it is not valid, fix it and add the original
738 # bad-checksum ISBN to the output
739 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
740 $output .= $isbn->isbn() . " ";
741 $isbn->fix_checksum();
744 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
745 # and add the normalized original ISBN to the output
746 if ($isbn && $isbn->is_valid()) {
747 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
748 $output .= $isbn->isbn . " ";
750 # If we successfully converted the ISBN to its counterpart, add the
751 # converted ISBN to the output as well
752 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
755 return $output if $output;
757 # If there were no valid ISBNs, just return the raw input
759 $func$ LANGUAGE PLPERLU;
761 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
762 The translate_isbn1013 function takes an input ISBN and returns the
763 following in a single space-delimited string if the input ISBN is valid:
764 - The normalized input ISBN (hyphens stripped)
765 - The normalized input ISBN with a fixed checksum if the checksum was bad
766 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
769 -- And ... a table in which to register them
771 CREATE TABLE config.index_normalizer (
772 id SERIAL PRIMARY KEY,
773 name TEXT UNIQUE NOT NULL,
776 param_count INT NOT NULL DEFAULT 0
779 CREATE TABLE config.metabib_field_index_norm_map (
780 id SERIAL PRIMARY KEY,
781 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
782 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
784 pos INT NOT NULL DEFAULT 0
787 CREATE TABLE config.record_attr_definition (
788 name TEXT PRIMARY KEY,
789 label TEXT NOT NULL, -- I18N
791 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
792 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
793 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
794 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
796 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
797 tag TEXT, -- LIKE format
798 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
800 -- This is used for both tag/sf and xpath entries
803 -- For xpath-extracted attrs
805 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
810 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
812 -- For phys-char fields
813 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
815 -- Source of vocabulary terms for this record attribute;
816 -- typically will be a URI referring to a SKOS vocabulary
820 CREATE TABLE config.record_attr_index_norm_map (
821 id SERIAL PRIMARY KEY,
822 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
823 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
825 pos INT NOT NULL DEFAULT 0
828 CREATE TABLE config.coded_value_map (
829 id SERIAL PRIMARY KEY,
830 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
834 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
836 is_simple BOOL NOT NULL DEFAULT FALSE,
837 concept_uri TEXT -- URI expressing the SKOS concept that the
838 -- coded value represents
841 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
843 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
844 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
845 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
846 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
847 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
848 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
849 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
851 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$
853 current_row config.coded_value_map%ROWTYPE;
855 -- Look for a current value
856 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
858 IF FOUND AND NOT add_only THEN
859 -- Update anything we were handed
860 current_row.value := COALESCE(current_row.value, in_value);
861 current_row.description := COALESCE(current_row.description, in_description);
862 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
863 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
864 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
865 UPDATE config.coded_value_map
867 value = current_row.value,
868 description = current_row.description,
869 opac_visible = current_row.opac_visible,
870 search_label = current_row.search_label,
871 is_simple = current_row.is_simple
872 WHERE id = current_row.id;
874 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
875 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
878 $f$ LANGUAGE PLPGSQL;
880 CREATE TABLE config.composite_attr_entry_definition(
881 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
882 definition TEXT NOT NULL -- JSON
885 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
886 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
887 SELECT DISTINCT l.version
888 FROM config.upgrade_log l
889 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
890 WHERE d.db_patch = $1
893 -- List applied db patches that are superseded by (and block the application of) my_db_patch
894 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
895 SELECT DISTINCT l.version
896 FROM config.upgrade_log l
897 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
898 WHERE d.db_patch = $1
901 -- List applied db patches that deprecates (and block the application of) my_db_patch
902 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
904 FROM config.db_patch_dependencies
905 WHERE ARRAY[$1]::TEXT[] && deprecates
908 -- List applied db patches that supersedes (and block the application of) my_db_patch
909 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
911 FROM config.db_patch_dependencies
912 WHERE ARRAY[$1]::TEXT[] && supersedes
915 -- Make sure that no deprecated or superseded db patches are currently applied
916 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
918 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
920 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
922 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
924 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
927 -- Raise an exception if there are, in fact, dep/sup conflict
928 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
933 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
934 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
935 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
937 Upgrade script % can not be applied:
938 applied deprecated scripts %
939 applied superseded scripts %
943 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
944 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
945 evergreen.upgrade_list_applied_deprecated(my_db_patch),
946 evergreen.upgrade_list_applied_superseded(my_db_patch);
949 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
954 CREATE TABLE config.barcode_completion (
955 id SERIAL PRIMARY KEY,
956 active BOOL NOT NULL DEFAULT true,
957 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
960 length INT NOT NULL DEFAULT 0,
962 padding_end BOOL NOT NULL DEFAULT false,
963 asset BOOL NOT NULL DEFAULT true,
964 actor BOOL NOT NULL DEFAULT true
967 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
969 -- Add support for logging, only keep the most recent five rows for each category.
972 CREATE TABLE config.org_unit_setting_type_log (
973 id BIGSERIAL PRIMARY KEY,
974 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
975 org INT, --REFERENCES actor.org_unit (id),
978 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
981 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
982 Org Unit setting Logs
984 This table contains the most recent changes to each setting
985 in actor.org_unit_setting, allowing for mistakes to be undone.
986 This is NOT meant to be an auditor, but rather an undo/redo.
989 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
991 -- Only keeps the most recent five settings changes.
992 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
993 (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);
995 IF (TG_OP = 'UPDATE') THEN
997 ELSIF (TG_OP = 'INSERT') THEN
1002 $oustl_limit$ LANGUAGE plpgsql;
1004 CREATE TRIGGER limit_logs_oust
1005 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1006 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1008 CREATE TABLE config.sms_carrier (
1009 id SERIAL PRIMARY KEY,
1013 active BOOLEAN DEFAULT TRUE
1016 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1018 CREATE TABLE config.usr_activity_type (
1019 id SERIAL PRIMARY KEY,
1023 label TEXT NOT NULL, -- i18n
1024 egroup config.usr_activity_group NOT NULL,
1025 enabled BOOL NOT NULL DEFAULT TRUE,
1026 transient BOOL NOT NULL DEFAULT TRUE,
1027 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1030 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1031 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1033 CREATE TABLE config.filter_dialog_interface (
1034 key TEXT PRIMARY KEY,
1038 CREATE TABLE config.filter_dialog_filter_set (
1039 id SERIAL PRIMARY KEY,
1041 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1042 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1043 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1044 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1045 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1046 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1049 CREATE TABLE config.best_hold_order(
1050 id SERIAL PRIMARY KEY,
1051 name TEXT UNIQUE, -- i18n
1052 pprox INT, -- copy capture <-> pickup lib prox
1053 hprox INT, -- copy circ lib <-> request lib prox
1054 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1055 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1056 priority INT, -- group hold priority
1057 cut INT, -- cut-in-line
1058 depth INT, -- selection depth
1059 htime INT, -- time since last home-lib circ exceeds org-unit setting
1060 rtime INT, -- request time
1061 shtime INT -- time since copy last trip home exceeds org-unit setting
1064 -- At least one of these columns must contain a non-null value
1065 ALTER TABLE config.best_hold_order ADD CHECK ((
1066 pprox IS NOT NULL OR
1067 hprox IS NOT NULL OR
1068 aprox IS NOT NULL OR
1069 priority IS NOT NULL OR
1071 depth IS NOT NULL OR
1072 htime IS NOT NULL OR
1076 CREATE OR REPLACE FUNCTION
1077 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1080 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1086 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1089 $func$ LANGUAGE PLPGSQL STABLE;
1091 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1092 Used by a config.z3950_index_field_map constraint trigger
1093 to verify z3950_attr_type maps.
1096 -- drop these in down here since they reference config.metabib_field
1097 -- and config.record_attr_definition
1098 CREATE TABLE config.z3950_index_field_map (
1099 id SERIAL PRIMARY KEY,
1100 label TEXT NOT NULL, -- i18n
1101 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1102 record_attr TEXT REFERENCES config.record_attr_definition(name),
1103 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1104 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1105 CONSTRAINT metabib_field_or_record_attr CHECK (
1106 metabib_field IS NOT NULL OR
1107 record_attr IS NOT NULL
1109 CONSTRAINT attr_or_attr_type CHECK (
1110 z3950_attr IS NOT NULL OR
1111 z3950_attr_type IS NOT NULL
1115 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1116 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1117 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1119 CREATE TABLE config.marc_format (
1120 id SERIAL PRIMARY KEY,
1124 COMMENT ON TABLE config.marc_format IS $$
1125 List of MARC formats supported by this Evergreen
1126 database. This exists primarily as a hook for future
1127 support of UNIMARC, though whether that will ever
1128 happen remains to be seen.
1131 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1133 CREATE TABLE config.marc_field (
1134 id SERIAL PRIMARY KEY,
1135 marc_format INTEGER NOT NULL
1136 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1137 marc_record_type config.marc_record_type NOT NULL,
1138 tag CHAR(3) NOT NULL,
1141 fixed_field BOOLEAN,
1145 owner INTEGER -- REFERENCES actor.org_unit (id)
1146 -- if the owner is null, the data about the field is
1147 -- assumed to come from the controlling MARC standard
1150 COMMENT ON TABLE config.marc_field IS $$
1151 This table stores a list of MARC fields recognized by the Evergreen
1152 instance. Note that we're not aiming for completely generic ISO2709
1153 support: we're assuming things like three characters for a tag,
1154 one-character subfield labels, two indicators per variable data field,
1155 and the like, all of which are technically specializations of ISO2709.
1157 Of particular significance is the owner column; if it's set to a null
1158 value, the field definition is assumed to come from a national
1159 standards body; if it's set to a non-null value, the field definition
1160 is an OU-level addition to or override of the standard.
1163 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1164 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1166 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1167 ON config.marc_field(marc_format, marc_record_type, tag)
1168 WHERE owner IS NULL;
1169 ALTER TABLE config.marc_field
1170 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1171 CHECK ((owner IS NOT NULL) OR
1174 repeatable IS NOT NULL AND
1175 mandatory IS NOT NULL AND
1180 CREATE TABLE config.marc_subfield (
1181 id SERIAL PRIMARY KEY,
1182 marc_format INTEGER NOT NULL
1183 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1184 marc_record_type config.marc_record_type NOT NULL,
1185 tag CHAR(3) NOT NULL,
1186 code CHAR(1) NOT NULL,
1192 REFERENCES config.record_attr_definition (name)
1193 DEFERRABLE INITIALLY DEFERRED,
1194 owner INTEGER -- REFERENCES actor.org_unit (id)
1195 -- if the owner is null, the data about the subfield is
1196 -- assumed to come from the controlling MARC standard
1199 COMMENT ON TABLE config.marc_subfield IS $$
1200 This table stores the list of subfields recognized by this Evergreen
1201 instance. As with config.marc_field, of particular significance is the
1202 owner column; if it's set to a null value, the subfield definition is
1203 assumed to come from a national standards body; if it's set to a non-null
1204 value, the subfield definition is an OU-level addition to or override
1208 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1209 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1210 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1211 WHERE owner IS NULL;
1212 ALTER TABLE config.marc_subfield
1213 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1214 CHECK ((owner IS NOT NULL) OR
1217 repeatable IS NOT NULL AND
1218 mandatory IS NOT NULL AND
1223 CREATE TABLE config.copy_tag_type (
1224 code TEXT NOT NULL PRIMARY KEY,
1225 label TEXT NOT NULL,
1226 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1229 CREATE INDEX config_copy_tag_type_owner_idx
1230 ON config.copy_tag_type (owner);