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 OR REPLACE FUNCTION
228 config.metabib_representative_field_is_valid(INTEGER, TEXT) RETURNS BOOLEAN AS $$
229 SELECT EXISTS (SELECT 1 FROM config.metabib_field WHERE id = $1 AND field_class = $2);
230 $$ LANGUAGE SQL STRICT IMMUTABLE;
232 COMMENT ON FUNCTION config.metabib_representative_field_is_valid(INTEGER, TEXT) IS $$
233 Ensure the field_class value on the selected representative field matches
237 ALTER TABLE config.metabib_class
238 ADD COLUMN representative_field
239 INTEGER REFERENCES config.metabib_field(id),
240 ADD CONSTRAINT rep_field_unique UNIQUE(representative_field),
241 ADD CONSTRAINT rep_field_is_valid CHECK (
242 representative_field IS NULL OR
243 config.metabib_representative_field_is_valid(representative_field, name)
247 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
249 CREATE TABLE config.ts_config_list (
253 COMMENT ON TABLE config.ts_config_list IS $$
256 A list of full text configs with names and descriptions.
259 CREATE TABLE config.metabib_class_ts_map (
260 id SERIAL PRIMARY KEY,
261 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
262 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
263 active BOOL NOT NULL DEFAULT TRUE,
264 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
265 index_lang TEXT NULL,
266 search_lang TEXT NULL,
267 always BOOL NOT NULL DEFAULT true
269 COMMENT ON TABLE config.metabib_class_ts_map IS $$
270 Text Search Configs for metabib class indexing
272 This table contains text search config definitions for
273 storing index_vector values.
276 CREATE TABLE config.metabib_field_ts_map (
277 id SERIAL PRIMARY KEY,
278 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
279 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
280 active BOOL NOT NULL DEFAULT TRUE,
281 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
282 index_lang TEXT NULL,
283 search_lang TEXT NULL
285 COMMENT ON TABLE config.metabib_field_ts_map IS $$
286 Text Search Configs for metabib field indexing
288 This table contains text search config definitions for
289 storing index_vector values.
292 CREATE TABLE config.metabib_search_alias (
293 alias TEXT PRIMARY KEY,
294 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
295 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
298 CREATE TABLE config.non_cataloged_type (
299 id SERIAL PRIMARY KEY,
300 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
302 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
303 in_house BOOL NOT NULL DEFAULT FALSE,
304 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
306 COMMENT ON TABLE config.non_cataloged_type IS $$
307 Types of valid non-cataloged items.
310 CREATE TABLE config.identification_type (
311 id SERIAL PRIMARY KEY,
312 name TEXT NOT NULL UNIQUE
314 COMMENT ON TABLE config.identification_type IS $$
315 Types of valid patron identification.
317 Each patron must display at least one valid form of identification
318 in order to get a library card. This table lists those forms.
321 CREATE TABLE config.rule_circ_duration (
322 id SERIAL PRIMARY KEY,
323 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
324 extended INTERVAL NOT NULL,
325 normal INTERVAL NOT NULL,
326 shrt INTERVAL NOT NULL,
327 max_renewals INT NOT NULL
329 COMMENT ON TABLE config.rule_circ_duration IS $$
330 Circulation Duration rules
332 Each circulation is given a duration based on one of these rules.
335 CREATE TABLE config.hard_due_date (
336 id SERIAL PRIMARY KEY,
337 name TEXT NOT NULL UNIQUE,
338 ceiling_date TIMESTAMPTZ NOT NULL,
339 forceto BOOL NOT NULL,
340 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
343 CREATE TABLE config.hard_due_date_values (
344 id SERIAL PRIMARY KEY,
345 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
346 DEFERRABLE INITIALLY DEFERRED,
347 ceiling_date TIMESTAMPTZ NOT NULL,
348 active_date TIMESTAMPTZ NOT NULL
351 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
353 temp_value config.hard_due_date_values%ROWTYPE;
357 SELECT DISTINCT ON (hard_due_date) *
358 FROM config.hard_due_date_values
359 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
360 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
362 UPDATE config.hard_due_date
363 SET ceiling_date = temp_value.ceiling_date
364 WHERE id = temp_value.hard_due_date
365 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
366 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
369 updated := updated + 1;
375 $func$ LANGUAGE plpgsql;
377 CREATE TABLE config.rule_max_fine (
378 id SERIAL PRIMARY KEY,
379 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
380 amount NUMERIC(6,2) NOT NULL,
381 is_percent BOOL NOT NULL DEFAULT FALSE
383 COMMENT ON TABLE config.rule_max_fine IS $$
384 Circulation Max Fine rules
386 Each circulation is given a maximum fine based on one of
390 CREATE TABLE config.rule_recurring_fine (
391 id SERIAL PRIMARY KEY,
392 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
393 high NUMERIC(6,2) NOT NULL,
394 normal NUMERIC(6,2) NOT NULL,
395 low NUMERIC(6,2) NOT NULL,
396 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
397 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
399 COMMENT ON TABLE config.rule_recurring_fine IS $$
400 Circulation Recurring Fine rules
402 Each circulation is given a recurring fine amount based on one of
403 these rules. Note that it is recommended to run the fine generator
404 (from cron) at least as frequently as the lowest recurrence interval
405 used by your circulation rules so that accrued fines will be up
410 CREATE TABLE config.rule_age_hold_protect (
411 id SERIAL PRIMARY KEY,
412 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
413 age INTERVAL NOT NULL,
416 COMMENT ON TABLE config.rule_age_hold_protect IS $$
417 Hold Item Age Protection rules
419 A hold request can only capture new(ish) items when they are
420 within a particular proximity of the pickup_lib of the request.
421 The proximity ('prox' column) is calculated by counting
422 the number of tree edges between the pickup_lib and either the
423 owning_lib or circ_lib of the copy that could fulfill the hold,
424 as determined by the distance_is_from_owner value of the hold matrix
425 rule controlling the hold request.
428 CREATE TABLE config.copy_status (
429 id SERIAL PRIMARY KEY,
430 name TEXT NOT NULL UNIQUE,
431 holdable BOOL NOT NULL DEFAULT FALSE,
432 opac_visible BOOL NOT NULL DEFAULT FALSE,
433 copy_active BOOL NOT NULL DEFAULT FALSE,
434 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
435 is_available BOOL NOT NULL DEFAULT FALSE
437 COMMENT ON TABLE config.copy_status IS $$
440 The available copy statuses, and whether a copy in that
441 status is available for hold request capture. 0 (zero) is
442 the only special number in this set, meaning that the item
443 is available for immediate checkout, and is counted as available
446 Statuses with an ID below 100 are not removable, and have special
447 meaning in the code. Do not change them except to translate the
450 You may add and remove statuses above 100, and these can be used
451 to remove items from normal circulation without affecting the rest
452 of the copy's values or its location.
455 CREATE TABLE config.net_access_level (
456 id SERIAL PRIMARY KEY,
457 name TEXT NOT NULL UNIQUE
459 COMMENT ON TABLE config.net_access_level IS $$
460 Patron Network Access level
462 This will be used to inform the in-library firewall of how much
463 internet access the using patron should be allowed.
467 CREATE TABLE config.remote_account (
468 id SERIAL PRIMARY KEY,
470 host TEXT NOT NULL, -- name or IP, :port optional
471 username TEXT, -- optional, since we could default to $USER
472 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
473 account TEXT, -- aka profile or FTP "account" command
474 path TEXT, -- aka directory
475 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
476 last_activity TIMESTAMP WITH TIME ZONE
479 CREATE TABLE config.marc21_rec_type_map (
480 code TEXT PRIMARY KEY,
481 type_val TEXT NOT NULL,
482 blvl_val TEXT NOT NULL
485 CREATE TABLE config.marc21_ff_pos_map (
486 id SERIAL PRIMARY KEY,
487 fixed_field TEXT NOT NULL,
489 rec_type TEXT NOT NULL,
490 start_pos INT NOT NULL,
492 default_val TEXT NOT NULL DEFAULT ' '
495 CREATE TABLE config.marc21_physical_characteristic_type_map (
496 ptype_key TEXT PRIMARY KEY,
497 label TEXT NOT NULL -- I18N
500 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
501 id SERIAL PRIMARY KEY,
502 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
503 subfield TEXT NOT NULL,
504 start_pos INT NOT NULL,
506 label TEXT NOT NULL -- I18N
509 CREATE TABLE config.marc21_physical_characteristic_value_map (
510 id SERIAL PRIMARY KEY,
512 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
513 label TEXT NOT NULL -- I18N
517 CREATE TABLE config.z3950_source (
518 name TEXT PRIMARY KEY,
519 label TEXT NOT NULL UNIQUE,
523 record_format TEXT NOT NULL DEFAULT 'FI',
524 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
525 auth BOOL NOT NULL DEFAULT TRUE,
526 use_perm INT -- REFERENCES permission.perm_list (id)
529 COMMENT ON TABLE config.z3950_source IS $$
532 Each row in this table represents a database searchable via Z39.50.
535 COMMENT ON COLUMN config.z3950_source.record_format IS $$
539 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
540 Z39.50 preferred record syntax..
543 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
544 If set, this permission is required for the source to be listed in the staff
545 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
548 CREATE TABLE config.z3950_attr (
549 id SERIAL PRIMARY KEY,
550 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
555 truncation INT NOT NULL DEFAULT 0,
556 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
559 CREATE TABLE config.z3950_source_credentials (
560 id SERIAL PRIMARY KEY,
561 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
562 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
565 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
568 CREATE TABLE config.i18n_locale (
569 code TEXT PRIMARY KEY,
570 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
571 name TEXT UNIQUE NOT NULL,
573 rtl BOOL NOT NULL DEFAULT FALSE
576 CREATE TABLE config.i18n_core (
577 id BIGSERIAL PRIMARY KEY,
578 fq_field TEXT NOT NULL,
579 identity_value TEXT NOT NULL,
580 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
584 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
586 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
590 UPDATE config.i18n_core
591 SET identity_value = $$ || quote_literal(new_ident) || $$
592 WHERE fq_field LIKE '$$ || hint || $$.%'
593 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
598 $_$ LANGUAGE PLPGSQL;
600 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
602 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
605 $_$ LANGUAGE PLPGSQL;
607 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
609 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
612 $_$ LANGUAGE PLPGSQL;
614 CREATE TABLE config.billing_type (
615 id SERIAL PRIMARY KEY,
617 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
618 default_price NUMERIC(6,2),
619 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
622 CREATE TABLE config.settings_group (
623 name TEXT PRIMARY KEY,
624 label TEXT UNIQUE NOT NULL -- I18N
627 CREATE TABLE config.org_unit_setting_type (
628 name TEXT PRIMARY KEY,
629 label TEXT UNIQUE NOT NULL,
630 grp TEXT REFERENCES config.settings_group (name),
632 datatype TEXT NOT NULL DEFAULT 'string',
637 -- define valid datatypes
639 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
640 ( 'bool', 'integer', 'float', 'currency', 'interval',
641 'date', 'string', 'object', 'array', 'link' ) ),
643 -- fm_class is meaningful only for 'link' datatype
645 CONSTRAINT coust_no_empty_link CHECK
646 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
647 ( datatype <> 'link' AND fm_class IS NULL ) )
650 CREATE TABLE config.usr_setting_type (
652 name TEXT PRIMARY KEY,
653 opac_visible BOOL NOT NULL DEFAULT FALSE,
654 label TEXT UNIQUE NOT NULL,
656 grp TEXT REFERENCES config.settings_group (name),
657 datatype TEXT NOT NULL DEFAULT 'string',
662 -- define valid datatypes
664 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
665 ( 'bool', 'integer', 'float', 'currency', 'interval',
666 'date', 'string', 'object', 'array', 'link' ) ),
669 -- fm_class is meaningful only for 'link' datatype
671 CONSTRAINT coust_no_empty_link CHECK
672 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
673 ( datatype <> 'link' AND fm_class IS NULL ) )
677 -- Some handy functions, based on existing ones, to provide optional ingest normalization
679 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
680 SELECT SUBSTRING($1,$2);
681 $func$ LANGUAGE SQL STRICT IMMUTABLE;
683 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
684 SELECT SUBSTRING($1,1,$2);
685 $func$ LANGUAGE SQL STRICT IMMUTABLE;
687 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
688 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
689 $func$ LANGUAGE SQL STRICT IMMUTABLE;
691 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
692 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
693 $func$ LANGUAGE SQL STRICT IMMUTABLE;
695 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
696 SELECT approximate_date( $1, '0');
697 $func$ LANGUAGE SQL STRICT IMMUTABLE;
699 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
700 SELECT approximate_date( $1, '9');
701 $func$ LANGUAGE SQL STRICT IMMUTABLE;
703 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
704 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
705 $func$ LANGUAGE SQL STRICT IMMUTABLE;
707 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
708 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
709 $func$ LANGUAGE SQL STRICT IMMUTABLE;
711 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
716 # Find the first ISBN, force it to ISBN13 and return it
720 foreach my $word (split(/\s/, $input)) {
721 my $isbn = Business::ISBN->new($word);
723 # First check the checksum; if it is not valid, fix it and add the original
724 # bad-checksum ISBN to the output
725 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
726 $isbn->fix_checksum();
729 # If we now have a valid ISBN, force it to ISBN13 and return it
730 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
733 $func$ LANGUAGE PLPERLU;
735 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
736 Inspired by translate_isbn1013
738 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
739 version without hypens and with a repaired checksum if the checksum was bad
743 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
748 # For each ISBN found in a single string containing a set of ISBNs:
749 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
750 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
755 foreach my $word (split(/\s/, $input)) {
756 my $isbn = Business::ISBN->new($word);
758 # First check the checksum; if it is not valid, fix it and add the original
759 # bad-checksum ISBN to the output
760 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
761 $output .= $isbn->isbn() . " ";
762 $isbn->fix_checksum();
765 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
766 # and add the normalized original ISBN to the output
767 if ($isbn && $isbn->is_valid()) {
768 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
769 $output .= $isbn->isbn . " ";
771 # If we successfully converted the ISBN to its counterpart, add the
772 # converted ISBN to the output as well
773 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
776 return $output if $output;
778 # If there were no valid ISBNs, just return the raw input
780 $func$ LANGUAGE PLPERLU;
782 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
783 The translate_isbn1013 function takes an input ISBN and returns the
784 following in a single space-delimited string if the input ISBN is valid:
785 - The normalized input ISBN (hyphens stripped)
786 - The normalized input ISBN with a fixed checksum if the checksum was bad
787 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
790 -- And ... a table in which to register them
792 CREATE TABLE config.index_normalizer (
793 id SERIAL PRIMARY KEY,
794 name TEXT UNIQUE NOT NULL,
797 param_count INT NOT NULL DEFAULT 0
800 CREATE TABLE config.metabib_field_index_norm_map (
801 id SERIAL PRIMARY KEY,
802 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
803 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
805 pos INT NOT NULL DEFAULT 0
808 CREATE TABLE config.record_attr_definition (
809 name TEXT PRIMARY KEY,
810 label TEXT NOT NULL, -- I18N
812 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
813 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
814 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
815 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
817 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
818 tag TEXT, -- LIKE format
819 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
821 -- This is used for both tag/sf and xpath entries
824 -- For xpath-extracted attrs
826 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
831 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
833 -- For phys-char fields
834 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
836 -- Source of vocabulary terms for this record attribute;
837 -- typically will be a URI referring to a SKOS vocabulary
841 CREATE TABLE config.record_attr_index_norm_map (
842 id SERIAL PRIMARY KEY,
843 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
844 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
846 pos INT NOT NULL DEFAULT 0
849 CREATE TABLE config.coded_value_map (
850 id SERIAL PRIMARY KEY,
851 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
855 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
857 is_simple BOOL NOT NULL DEFAULT FALSE,
858 concept_uri TEXT -- URI expressing the SKOS concept that the
859 -- coded value represents
862 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
864 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
865 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
866 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
867 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
868 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
869 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
870 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
872 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$
874 current_row config.coded_value_map%ROWTYPE;
876 -- Look for a current value
877 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
879 IF FOUND AND NOT add_only THEN
880 -- Update anything we were handed
881 current_row.value := COALESCE(current_row.value, in_value);
882 current_row.description := COALESCE(current_row.description, in_description);
883 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
884 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
885 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
886 UPDATE config.coded_value_map
888 value = current_row.value,
889 description = current_row.description,
890 opac_visible = current_row.opac_visible,
891 search_label = current_row.search_label,
892 is_simple = current_row.is_simple
893 WHERE id = current_row.id;
895 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
896 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
899 $f$ LANGUAGE PLPGSQL;
901 CREATE TABLE config.composite_attr_entry_definition(
902 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
903 definition TEXT NOT NULL -- JSON
906 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
907 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
908 SELECT DISTINCT l.version
909 FROM config.upgrade_log l
910 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
911 WHERE d.db_patch = $1
914 -- List applied db patches that are superseded by (and block the application of) my_db_patch
915 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
916 SELECT DISTINCT l.version
917 FROM config.upgrade_log l
918 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
919 WHERE d.db_patch = $1
922 -- List applied db patches that deprecates (and block the application of) my_db_patch
923 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
925 FROM config.db_patch_dependencies
926 WHERE ARRAY[$1]::TEXT[] && deprecates
929 -- List applied db patches that supersedes (and block the application of) my_db_patch
930 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
932 FROM config.db_patch_dependencies
933 WHERE ARRAY[$1]::TEXT[] && supersedes
936 -- Make sure that no deprecated or superseded db patches are currently applied
937 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
939 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
941 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
943 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
945 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
948 -- Raise an exception if there are, in fact, dep/sup conflict
949 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
954 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
955 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
956 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
958 Upgrade script % can not be applied:
959 applied deprecated scripts %
960 applied superseded scripts %
964 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
965 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
966 evergreen.upgrade_list_applied_deprecated(my_db_patch),
967 evergreen.upgrade_list_applied_superseded(my_db_patch);
970 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
975 CREATE TABLE config.barcode_completion (
976 id SERIAL PRIMARY KEY,
977 active BOOL NOT NULL DEFAULT true,
978 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
981 length INT NOT NULL DEFAULT 0,
983 padding_end BOOL NOT NULL DEFAULT false,
984 asset BOOL NOT NULL DEFAULT true,
985 actor BOOL NOT NULL DEFAULT true
988 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
990 -- Add support for logging, only keep the most recent five rows for each category.
993 CREATE TABLE config.org_unit_setting_type_log (
994 id BIGSERIAL PRIMARY KEY,
995 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
996 org INT, --REFERENCES actor.org_unit (id),
999 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1002 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1003 Org Unit setting Logs
1005 This table contains the most recent changes to each setting
1006 in actor.org_unit_setting, allowing for mistakes to be undone.
1007 This is NOT meant to be an auditor, but rather an undo/redo.
1010 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1012 -- Only keeps the most recent five settings changes.
1013 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1014 (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);
1016 IF (TG_OP = 'UPDATE') THEN
1018 ELSIF (TG_OP = 'INSERT') THEN
1023 $oustl_limit$ LANGUAGE plpgsql;
1025 CREATE TRIGGER limit_logs_oust
1026 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1027 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1029 CREATE TABLE config.sms_carrier (
1030 id SERIAL PRIMARY KEY,
1034 active BOOLEAN DEFAULT TRUE
1037 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1039 CREATE TABLE config.usr_activity_type (
1040 id SERIAL PRIMARY KEY,
1044 label TEXT NOT NULL, -- i18n
1045 egroup config.usr_activity_group NOT NULL,
1046 enabled BOOL NOT NULL DEFAULT TRUE,
1047 transient BOOL NOT NULL DEFAULT TRUE,
1048 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1051 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1052 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1054 CREATE TABLE config.filter_dialog_interface (
1055 key TEXT PRIMARY KEY,
1059 CREATE TABLE config.filter_dialog_filter_set (
1060 id SERIAL PRIMARY KEY,
1062 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1063 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1064 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1065 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1066 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1067 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1070 CREATE TABLE config.best_hold_order(
1071 id SERIAL PRIMARY KEY,
1072 name TEXT UNIQUE, -- i18n
1073 pprox INT, -- copy capture <-> pickup lib prox
1074 hprox INT, -- copy circ lib <-> request lib prox
1075 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1076 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1077 priority INT, -- group hold priority
1078 cut INT, -- cut-in-line
1079 depth INT, -- selection depth
1080 htime INT, -- time since last home-lib circ exceeds org-unit setting
1081 rtime INT, -- request time
1082 shtime INT -- time since copy last trip home exceeds org-unit setting
1085 -- At least one of these columns must contain a non-null value
1086 ALTER TABLE config.best_hold_order ADD CHECK ((
1087 pprox IS NOT NULL OR
1088 hprox IS NOT NULL OR
1089 aprox IS NOT NULL OR
1090 priority IS NOT NULL OR
1092 depth IS NOT NULL OR
1093 htime IS NOT NULL OR
1097 CREATE OR REPLACE FUNCTION
1098 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1101 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1107 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1110 $func$ LANGUAGE PLPGSQL STABLE;
1112 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1113 Used by a config.z3950_index_field_map constraint trigger
1114 to verify z3950_attr_type maps.
1117 -- drop these in down here since they reference config.metabib_field
1118 -- and config.record_attr_definition
1119 CREATE TABLE config.z3950_index_field_map (
1120 id SERIAL PRIMARY KEY,
1121 label TEXT NOT NULL, -- i18n
1122 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1123 record_attr TEXT REFERENCES config.record_attr_definition(name),
1124 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1125 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1126 CONSTRAINT metabib_field_or_record_attr CHECK (
1127 metabib_field IS NOT NULL OR
1128 record_attr IS NOT NULL
1130 CONSTRAINT attr_or_attr_type CHECK (
1131 z3950_attr IS NOT NULL OR
1132 z3950_attr_type IS NOT NULL
1136 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1137 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1138 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1140 CREATE TABLE config.marc_format (
1141 id SERIAL PRIMARY KEY,
1145 COMMENT ON TABLE config.marc_format IS $$
1146 List of MARC formats supported by this Evergreen
1147 database. This exists primarily as a hook for future
1148 support of UNIMARC, though whether that will ever
1149 happen remains to be seen.
1152 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1154 CREATE TABLE config.marc_field (
1155 id SERIAL PRIMARY KEY,
1156 marc_format INTEGER NOT NULL
1157 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1158 marc_record_type config.marc_record_type NOT NULL,
1159 tag CHAR(3) NOT NULL,
1162 fixed_field BOOLEAN,
1166 owner INTEGER -- REFERENCES actor.org_unit (id)
1167 -- if the owner is null, the data about the field is
1168 -- assumed to come from the controlling MARC standard
1171 COMMENT ON TABLE config.marc_field IS $$
1172 This table stores a list of MARC fields recognized by the Evergreen
1173 instance. Note that we're not aiming for completely generic ISO2709
1174 support: we're assuming things like three characters for a tag,
1175 one-character subfield labels, two indicators per variable data field,
1176 and the like, all of which are technically specializations of ISO2709.
1178 Of particular significance is the owner column; if it's set to a null
1179 value, the field definition is assumed to come from a national
1180 standards body; if it's set to a non-null value, the field definition
1181 is an OU-level addition to or override of the standard.
1184 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1185 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1187 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1188 ON config.marc_field(marc_format, marc_record_type, tag)
1189 WHERE owner IS NULL;
1190 ALTER TABLE config.marc_field
1191 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1192 CHECK ((owner IS NOT NULL) OR
1195 repeatable IS NOT NULL AND
1196 mandatory IS NOT NULL AND
1201 CREATE TABLE config.marc_subfield (
1202 id SERIAL PRIMARY KEY,
1203 marc_format INTEGER NOT NULL
1204 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1205 marc_record_type config.marc_record_type NOT NULL,
1206 tag CHAR(3) NOT NULL,
1207 code CHAR(1) NOT NULL,
1213 REFERENCES config.record_attr_definition (name)
1214 DEFERRABLE INITIALLY DEFERRED,
1215 owner INTEGER -- REFERENCES actor.org_unit (id)
1216 -- if the owner is null, the data about the subfield is
1217 -- assumed to come from the controlling MARC standard
1220 COMMENT ON TABLE config.marc_subfield IS $$
1221 This table stores the list of subfields recognized by this Evergreen
1222 instance. As with config.marc_field, of particular significance is the
1223 owner column; if it's set to a null value, the subfield definition is
1224 assumed to come from a national standards body; if it's set to a non-null
1225 value, the subfield definition is an OU-level addition to or override
1229 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1230 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1231 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1232 WHERE owner IS NULL;
1233 ALTER TABLE config.marc_subfield
1234 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1235 CHECK ((owner IS NOT NULL) OR
1238 repeatable IS NOT NULL AND
1239 mandatory IS NOT NULL AND
1244 CREATE TABLE config.copy_tag_type (
1245 code TEXT NOT NULL PRIMARY KEY,
1246 label TEXT NOT NULL,
1247 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1250 CREATE INDEX config_copy_tag_type_owner_idx
1251 ON config.copy_tag_type (owner);