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 OR REPLACE FUNCTION
187 config.metabib_representative_field_is_valid(INTEGER, TEXT) RETURNS BOOLEAN AS $$
188 SELECT EXISTS (SELECT 1 FROM config.metabib_field WHERE id = $1 AND field_class = $2);
189 $$ LANGUAGE SQL STRICT IMMUTABLE;
191 COMMENT ON FUNCTION config.metabib_representative_field_is_valid(INTEGER, TEXT) IS $$
192 Ensure the field_class value on the selected representative field matches
196 CREATE TABLE config.metabib_class (
197 name TEXT PRIMARY KEY,
198 label TEXT NOT NULL UNIQUE,
199 buoyant BOOL DEFAULT FALSE NOT NULL,
200 restrict BOOL DEFAULT FALSE NOT NULL,
201 combined BOOL DEFAULT FALSE NOT NULL,
202 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
203 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
204 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
205 d_weight NUMERIC DEFAULT 0.1 NOT NULL
208 CREATE TABLE config.metabib_field (
209 id SERIAL PRIMARY KEY,
210 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
214 weight INT NOT NULL DEFAULT 1,
215 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
216 search_field BOOL NOT NULL DEFAULT TRUE,
217 facet_field BOOL NOT NULL DEFAULT FALSE,
218 browse_field BOOL NOT NULL DEFAULT TRUE,
220 browse_sort_xpath TEXT,
223 authority_xpath TEXT,
225 restrict BOOL DEFAULT FALSE NOT NULL,
226 display_field BOOL NOT NULL DEFAULT TRUE
228 COMMENT ON TABLE config.metabib_field IS $$
229 XPath used for record indexing ingest
231 This table contains the XPath used to chop up MODS into its
232 indexable parts. Each XPath entry is named and assigned to
233 a "class" of either title, subject, author, keyword, series
237 CREATE OR REPLACE FUNCTION
238 config.metabib_representative_field_is_valid(INTEGER, TEXT) RETURNS BOOLEAN AS $$
239 SELECT EXISTS (SELECT 1 FROM config.metabib_field WHERE id = $1 AND field_class = $2);
240 $$ LANGUAGE SQL STRICT IMMUTABLE;
242 COMMENT ON FUNCTION config.metabib_representative_field_is_valid(INTEGER, TEXT) IS $$
243 Ensure the field_class value on the selected representative field matches
247 ALTER TABLE config.metabib_class
248 ADD COLUMN representative_field
249 INTEGER REFERENCES config.metabib_field(id),
250 ADD CONSTRAINT rep_field_unique UNIQUE(representative_field),
251 ADD CONSTRAINT rep_field_is_valid CHECK (
252 representative_field IS NULL OR
253 config.metabib_representative_field_is_valid(representative_field, name)
257 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
259 CREATE TABLE config.ts_config_list (
263 COMMENT ON TABLE config.ts_config_list IS $$
266 A list of full text configs with names and descriptions.
269 CREATE TABLE config.metabib_class_ts_map (
270 id SERIAL PRIMARY KEY,
271 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
272 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
273 active BOOL NOT NULL DEFAULT TRUE,
274 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
275 index_lang TEXT NULL,
276 search_lang TEXT NULL,
277 always BOOL NOT NULL DEFAULT true
279 COMMENT ON TABLE config.metabib_class_ts_map IS $$
280 Text Search Configs for metabib class indexing
282 This table contains text search config definitions for
283 storing index_vector values.
286 CREATE TABLE config.metabib_field_ts_map (
287 id SERIAL PRIMARY KEY,
288 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
289 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
290 active BOOL NOT NULL DEFAULT TRUE,
291 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
292 index_lang TEXT NULL,
293 search_lang TEXT NULL
295 COMMENT ON TABLE config.metabib_field_ts_map IS $$
296 Text Search Configs for metabib field indexing
298 This table contains text search config definitions for
299 storing index_vector values.
302 CREATE TABLE config.metabib_search_alias (
303 alias TEXT PRIMARY KEY,
304 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
305 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
308 CREATE TABLE config.non_cataloged_type (
309 id SERIAL PRIMARY KEY,
310 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
312 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
313 in_house BOOL NOT NULL DEFAULT FALSE,
314 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
316 COMMENT ON TABLE config.non_cataloged_type IS $$
317 Types of valid non-cataloged items.
320 CREATE TABLE config.identification_type (
321 id SERIAL PRIMARY KEY,
322 name TEXT NOT NULL UNIQUE
324 COMMENT ON TABLE config.identification_type IS $$
325 Types of valid patron identification.
327 Each patron must display at least one valid form of identification
328 in order to get a library card. This table lists those forms.
331 CREATE TABLE config.rule_circ_duration (
332 id SERIAL PRIMARY KEY,
333 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
334 extended INTERVAL NOT NULL,
335 normal INTERVAL NOT NULL,
336 shrt INTERVAL NOT NULL,
337 max_renewals INT NOT NULL
339 COMMENT ON TABLE config.rule_circ_duration IS $$
340 Circulation Duration rules
342 Each circulation is given a duration based on one of these rules.
345 CREATE TABLE config.hard_due_date (
346 id SERIAL PRIMARY KEY,
347 name TEXT NOT NULL UNIQUE,
348 ceiling_date TIMESTAMPTZ NOT NULL,
349 forceto BOOL NOT NULL,
350 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
353 CREATE TABLE config.hard_due_date_values (
354 id SERIAL PRIMARY KEY,
355 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
356 DEFERRABLE INITIALLY DEFERRED,
357 ceiling_date TIMESTAMPTZ NOT NULL,
358 active_date TIMESTAMPTZ NOT NULL
361 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
363 temp_value config.hard_due_date_values%ROWTYPE;
367 SELECT DISTINCT ON (hard_due_date) *
368 FROM config.hard_due_date_values
369 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
370 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
372 UPDATE config.hard_due_date
373 SET ceiling_date = temp_value.ceiling_date
374 WHERE id = temp_value.hard_due_date
375 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
376 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
379 updated := updated + 1;
385 $func$ LANGUAGE plpgsql;
387 CREATE TABLE config.rule_max_fine (
388 id SERIAL PRIMARY KEY,
389 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
390 amount NUMERIC(6,2) NOT NULL,
391 is_percent BOOL NOT NULL DEFAULT FALSE
393 COMMENT ON TABLE config.rule_max_fine IS $$
394 Circulation Max Fine rules
396 Each circulation is given a maximum fine based on one of
400 CREATE TABLE config.rule_recurring_fine (
401 id SERIAL PRIMARY KEY,
402 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
403 high NUMERIC(6,2) NOT NULL,
404 normal NUMERIC(6,2) NOT NULL,
405 low NUMERIC(6,2) NOT NULL,
406 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
407 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
409 COMMENT ON TABLE config.rule_recurring_fine IS $$
410 Circulation Recurring Fine rules
412 Each circulation is given a recurring fine amount based on one of
413 these rules. Note that it is recommended to run the fine generator
414 (from cron) at least as frequently as the lowest recurrence interval
415 used by your circulation rules so that accrued fines will be up
420 CREATE TABLE config.rule_age_hold_protect (
421 id SERIAL PRIMARY KEY,
422 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
423 age INTERVAL NOT NULL,
426 COMMENT ON TABLE config.rule_age_hold_protect IS $$
427 Hold Item Age Protection rules
429 A hold request can only capture new(ish) items when they are
430 within a particular proximity of the pickup_lib of the request.
431 The proximity ('prox' column) is calculated by counting
432 the number of tree edges between the pickup_lib and either the
433 owning_lib or circ_lib of the copy that could fulfill the hold,
434 as determined by the distance_is_from_owner value of the hold matrix
435 rule controlling the hold request.
438 CREATE TABLE config.copy_status (
439 id SERIAL PRIMARY KEY,
440 name TEXT NOT NULL UNIQUE,
441 holdable BOOL NOT NULL DEFAULT FALSE,
442 opac_visible BOOL NOT NULL DEFAULT FALSE,
443 copy_active BOOL NOT NULL DEFAULT FALSE,
444 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
445 is_available BOOL NOT NULL DEFAULT FALSE
447 COMMENT ON TABLE config.copy_status IS $$
450 The available copy statuses, and whether a copy in that
451 status is available for hold request capture. 0 (zero) is
452 the only special number in this set, meaning that the item
453 is available for immediate checkout, and is counted as available
456 Statuses with an ID below 100 are not removable, and have special
457 meaning in the code. Do not change them except to translate the
460 You may add and remove statuses above 100, and these can be used
461 to remove items from normal circulation without affecting the rest
462 of the copy's values or its location.
465 CREATE TABLE config.net_access_level (
466 id SERIAL PRIMARY KEY,
467 name TEXT NOT NULL UNIQUE
469 COMMENT ON TABLE config.net_access_level IS $$
470 Patron Network Access level
472 This will be used to inform the in-library firewall of how much
473 internet access the using patron should be allowed.
477 CREATE TABLE config.remote_account (
478 id SERIAL PRIMARY KEY,
480 host TEXT NOT NULL, -- name or IP, :port optional
481 username TEXT, -- optional, since we could default to $USER
482 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
483 account TEXT, -- aka profile or FTP "account" command
484 path TEXT, -- aka directory
485 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
486 last_activity TIMESTAMP WITH TIME ZONE
489 CREATE TABLE config.marc21_rec_type_map (
490 code TEXT PRIMARY KEY,
491 type_val TEXT NOT NULL,
492 blvl_val TEXT NOT NULL
495 CREATE TABLE config.marc21_ff_pos_map (
496 id SERIAL PRIMARY KEY,
497 fixed_field TEXT NOT NULL,
499 rec_type TEXT NOT NULL,
500 start_pos INT NOT NULL,
502 default_val TEXT NOT NULL DEFAULT ' '
505 CREATE TABLE config.marc21_physical_characteristic_type_map (
506 ptype_key TEXT PRIMARY KEY,
507 label TEXT NOT NULL -- I18N
510 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
511 id SERIAL PRIMARY KEY,
512 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
513 subfield TEXT NOT NULL,
514 start_pos INT NOT NULL,
516 label TEXT NOT NULL -- I18N
519 CREATE TABLE config.marc21_physical_characteristic_value_map (
520 id SERIAL PRIMARY KEY,
522 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
523 label TEXT NOT NULL -- I18N
527 CREATE TABLE config.z3950_source (
528 name TEXT PRIMARY KEY,
529 label TEXT NOT NULL UNIQUE,
533 record_format TEXT NOT NULL DEFAULT 'FI',
534 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
535 auth BOOL NOT NULL DEFAULT TRUE,
536 use_perm INT -- REFERENCES permission.perm_list (id)
539 COMMENT ON TABLE config.z3950_source IS $$
542 Each row in this table represents a database searchable via Z39.50.
545 COMMENT ON COLUMN config.z3950_source.record_format IS $$
549 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
550 Z39.50 preferred record syntax..
553 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
554 If set, this permission is required for the source to be listed in the staff
555 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
558 CREATE TABLE config.z3950_attr (
559 id SERIAL PRIMARY KEY,
560 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
565 truncation INT NOT NULL DEFAULT 0,
566 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
569 CREATE TABLE config.z3950_source_credentials (
570 id SERIAL PRIMARY KEY,
571 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
572 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
575 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
578 CREATE TABLE config.i18n_locale (
579 code TEXT PRIMARY KEY,
580 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
581 name TEXT UNIQUE NOT NULL,
583 rtl BOOL NOT NULL DEFAULT FALSE
586 CREATE TABLE config.i18n_core (
587 id BIGSERIAL PRIMARY KEY,
588 fq_field TEXT NOT NULL,
589 identity_value TEXT NOT NULL,
590 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
594 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
596 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
600 UPDATE config.i18n_core
601 SET identity_value = $$ || quote_literal(new_ident) || $$
602 WHERE fq_field LIKE '$$ || hint || $$.%'
603 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
608 $_$ LANGUAGE PLPGSQL;
610 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
612 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
615 $_$ LANGUAGE PLPGSQL;
617 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
619 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
622 $_$ LANGUAGE PLPGSQL;
624 CREATE TABLE config.billing_type (
625 id SERIAL PRIMARY KEY,
627 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
628 default_price NUMERIC(6,2),
629 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
632 CREATE TABLE config.settings_group (
633 name TEXT PRIMARY KEY,
634 label TEXT UNIQUE NOT NULL -- I18N
637 CREATE TABLE config.org_unit_setting_type (
638 name TEXT PRIMARY KEY,
639 label TEXT UNIQUE NOT NULL,
640 grp TEXT REFERENCES config.settings_group (name),
642 datatype TEXT NOT NULL DEFAULT 'string',
647 -- define valid datatypes
649 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
650 ( 'bool', 'integer', 'float', 'currency', 'interval',
651 'date', 'string', 'object', 'array', 'link' ) ),
653 -- fm_class is meaningful only for 'link' datatype
655 CONSTRAINT coust_no_empty_link CHECK
656 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
657 ( datatype <> 'link' AND fm_class IS NULL ) )
660 CREATE TABLE config.usr_setting_type (
662 name TEXT PRIMARY KEY,
663 opac_visible BOOL NOT NULL DEFAULT FALSE,
664 label TEXT UNIQUE NOT NULL,
666 grp TEXT REFERENCES config.settings_group (name),
667 datatype TEXT NOT NULL DEFAULT 'string',
672 -- define valid datatypes
674 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
675 ( 'bool', 'integer', 'float', 'currency', 'interval',
676 'date', 'string', 'object', 'array', 'link' ) ),
679 -- fm_class is meaningful only for 'link' datatype
681 CONSTRAINT coust_no_empty_link CHECK
682 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
683 ( datatype <> 'link' AND fm_class IS NULL ) )
687 -- Some handy functions, based on existing ones, to provide optional ingest normalization
689 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
690 SELECT SUBSTRING($1,$2);
691 $func$ LANGUAGE SQL STRICT IMMUTABLE;
693 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
694 SELECT SUBSTRING($1,1,$2);
695 $func$ LANGUAGE SQL STRICT IMMUTABLE;
697 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
698 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
699 $func$ LANGUAGE SQL STRICT IMMUTABLE;
701 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
702 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
703 $func$ LANGUAGE SQL STRICT IMMUTABLE;
705 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
706 SELECT approximate_date( $1, '0');
707 $func$ LANGUAGE SQL STRICT IMMUTABLE;
709 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
710 SELECT approximate_date( $1, '9');
711 $func$ LANGUAGE SQL STRICT IMMUTABLE;
713 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
714 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
715 $func$ LANGUAGE SQL STRICT IMMUTABLE;
717 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
718 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
719 $func$ LANGUAGE SQL STRICT IMMUTABLE;
721 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
726 # Find the first ISBN, force it to ISBN13 and return it
730 foreach my $word (split(/\s/, $input)) {
731 my $isbn = Business::ISBN->new($word);
733 # First check the checksum; if it is not valid, fix it and add the original
734 # bad-checksum ISBN to the output
735 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
736 $isbn->fix_checksum();
739 # If we now have a valid ISBN, force it to ISBN13 and return it
740 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
743 $func$ LANGUAGE PLPERLU;
745 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
746 Inspired by translate_isbn1013
748 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
749 version without hypens and with a repaired checksum if the checksum was bad
753 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
758 # For each ISBN found in a single string containing a set of ISBNs:
759 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
760 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
765 foreach my $word (split(/\s/, $input)) {
766 my $isbn = Business::ISBN->new($word);
768 # First check the checksum; if it is not valid, fix it and add the original
769 # bad-checksum ISBN to the output
770 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
771 $output .= $isbn->isbn() . " ";
772 $isbn->fix_checksum();
775 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
776 # and add the normalized original ISBN to the output
777 if ($isbn && $isbn->is_valid()) {
778 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
779 $output .= $isbn->isbn . " ";
781 # If we successfully converted the ISBN to its counterpart, add the
782 # converted ISBN to the output as well
783 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
786 return $output if $output;
788 # If there were no valid ISBNs, just return the raw input
790 $func$ LANGUAGE PLPERLU;
792 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
793 The translate_isbn1013 function takes an input ISBN and returns the
794 following in a single space-delimited string if the input ISBN is valid:
795 - The normalized input ISBN (hyphens stripped)
796 - The normalized input ISBN with a fixed checksum if the checksum was bad
797 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
800 -- And ... a table in which to register them
802 CREATE TABLE config.index_normalizer (
803 id SERIAL PRIMARY KEY,
804 name TEXT UNIQUE NOT NULL,
807 param_count INT NOT NULL DEFAULT 0
810 CREATE TABLE config.metabib_field_index_norm_map (
811 id SERIAL PRIMARY KEY,
812 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
813 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
815 pos INT NOT NULL DEFAULT 0
818 CREATE TABLE config.record_attr_definition (
819 name TEXT PRIMARY KEY,
820 label TEXT NOT NULL, -- I18N
822 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
823 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
824 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
825 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
827 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
828 tag TEXT, -- LIKE format
829 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
831 -- This is used for both tag/sf and xpath entries
834 -- For xpath-extracted attrs
836 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
841 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
843 -- For phys-char fields
844 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
846 -- Source of vocabulary terms for this record attribute;
847 -- typically will be a URI referring to a SKOS vocabulary
851 CREATE TABLE config.record_attr_index_norm_map (
852 id SERIAL PRIMARY KEY,
853 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
854 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
856 pos INT NOT NULL DEFAULT 0
859 CREATE TABLE config.coded_value_map (
860 id SERIAL PRIMARY KEY,
861 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
865 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
867 is_simple BOOL NOT NULL DEFAULT FALSE,
868 concept_uri TEXT -- URI expressing the SKOS concept that the
869 -- coded value represents
872 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
874 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
875 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
876 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
877 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
878 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
879 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
880 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
882 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$
884 current_row config.coded_value_map%ROWTYPE;
886 -- Look for a current value
887 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
889 IF FOUND AND NOT add_only THEN
890 -- Update anything we were handed
891 current_row.value := COALESCE(current_row.value, in_value);
892 current_row.description := COALESCE(current_row.description, in_description);
893 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
894 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
895 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
896 UPDATE config.coded_value_map
898 value = current_row.value,
899 description = current_row.description,
900 opac_visible = current_row.opac_visible,
901 search_label = current_row.search_label,
902 is_simple = current_row.is_simple
903 WHERE id = current_row.id;
905 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
906 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
909 $f$ LANGUAGE PLPGSQL;
911 CREATE TABLE config.composite_attr_entry_definition(
912 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
913 definition TEXT NOT NULL -- JSON
916 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
917 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
918 SELECT DISTINCT l.version
919 FROM config.upgrade_log l
920 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
921 WHERE d.db_patch = $1
924 -- List applied db patches that are superseded by (and block the application of) my_db_patch
925 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
926 SELECT DISTINCT l.version
927 FROM config.upgrade_log l
928 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
929 WHERE d.db_patch = $1
932 -- List applied db patches that deprecates (and block the application of) my_db_patch
933 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
935 FROM config.db_patch_dependencies
936 WHERE ARRAY[$1]::TEXT[] && deprecates
939 -- List applied db patches that supersedes (and block the application of) my_db_patch
940 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
942 FROM config.db_patch_dependencies
943 WHERE ARRAY[$1]::TEXT[] && supersedes
946 -- Make sure that no deprecated or superseded db patches are currently applied
947 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
949 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
951 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
953 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
955 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
958 -- Raise an exception if there are, in fact, dep/sup conflict
959 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
964 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
965 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
966 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
968 Upgrade script % can not be applied:
969 applied deprecated scripts %
970 applied superseded scripts %
974 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
975 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
976 evergreen.upgrade_list_applied_deprecated(my_db_patch),
977 evergreen.upgrade_list_applied_superseded(my_db_patch);
980 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
985 CREATE TABLE config.barcode_completion (
986 id SERIAL PRIMARY KEY,
987 active BOOL NOT NULL DEFAULT true,
988 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
991 length INT NOT NULL DEFAULT 0,
993 padding_end BOOL NOT NULL DEFAULT false,
994 asset BOOL NOT NULL DEFAULT true,
995 actor BOOL NOT NULL DEFAULT true
998 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
1000 -- Add support for logging, only keep the most recent five rows for each category.
1003 CREATE TABLE config.org_unit_setting_type_log (
1004 id BIGSERIAL PRIMARY KEY,
1005 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1006 org INT, --REFERENCES actor.org_unit (id),
1007 original_value TEXT,
1009 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1012 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1013 Org Unit setting Logs
1015 This table contains the most recent changes to each setting
1016 in actor.org_unit_setting, allowing for mistakes to be undone.
1017 This is NOT meant to be an auditor, but rather an undo/redo.
1020 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1022 -- Only keeps the most recent five settings changes.
1023 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1024 (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);
1026 IF (TG_OP = 'UPDATE') THEN
1028 ELSIF (TG_OP = 'INSERT') THEN
1033 $oustl_limit$ LANGUAGE plpgsql;
1035 CREATE TRIGGER limit_logs_oust
1036 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1037 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1039 CREATE TABLE config.sms_carrier (
1040 id SERIAL PRIMARY KEY,
1044 active BOOLEAN DEFAULT TRUE
1047 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1049 CREATE TABLE config.usr_activity_type (
1050 id SERIAL PRIMARY KEY,
1054 label TEXT NOT NULL, -- i18n
1055 egroup config.usr_activity_group NOT NULL,
1056 enabled BOOL NOT NULL DEFAULT TRUE,
1057 transient BOOL NOT NULL DEFAULT TRUE,
1058 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1061 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1062 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1064 CREATE TABLE config.filter_dialog_interface (
1065 key TEXT PRIMARY KEY,
1069 CREATE TABLE config.filter_dialog_filter_set (
1070 id SERIAL PRIMARY KEY,
1072 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1073 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1074 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1075 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1076 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1077 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1080 CREATE TABLE config.best_hold_order(
1081 id SERIAL PRIMARY KEY,
1082 name TEXT UNIQUE, -- i18n
1083 pprox INT, -- copy capture <-> pickup lib prox
1084 hprox INT, -- copy circ lib <-> request lib prox
1085 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1086 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1087 priority INT, -- group hold priority
1088 cut INT, -- cut-in-line
1089 depth INT, -- selection depth
1090 htime INT, -- time since last home-lib circ exceeds org-unit setting
1091 rtime INT, -- request time
1092 shtime INT -- time since copy last trip home exceeds org-unit setting
1095 -- At least one of these columns must contain a non-null value
1096 ALTER TABLE config.best_hold_order ADD CHECK ((
1097 pprox IS NOT NULL OR
1098 hprox IS NOT NULL OR
1099 aprox IS NOT NULL OR
1100 priority IS NOT NULL OR
1102 depth IS NOT NULL OR
1103 htime IS NOT NULL OR
1107 CREATE OR REPLACE FUNCTION
1108 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1111 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1117 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1120 $func$ LANGUAGE PLPGSQL STABLE;
1122 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1123 Used by a config.z3950_index_field_map constraint trigger
1124 to verify z3950_attr_type maps.
1127 -- drop these in down here since they reference config.metabib_field
1128 -- and config.record_attr_definition
1129 CREATE TABLE config.z3950_index_field_map (
1130 id SERIAL PRIMARY KEY,
1131 label TEXT NOT NULL, -- i18n
1132 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1133 record_attr TEXT REFERENCES config.record_attr_definition(name),
1134 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1135 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1136 CONSTRAINT metabib_field_or_record_attr CHECK (
1137 metabib_field IS NOT NULL OR
1138 record_attr IS NOT NULL
1140 CONSTRAINT attr_or_attr_type CHECK (
1141 z3950_attr IS NOT NULL OR
1142 z3950_attr_type IS NOT NULL
1146 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1147 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1148 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1150 CREATE TABLE config.marc_format (
1151 id SERIAL PRIMARY KEY,
1155 COMMENT ON TABLE config.marc_format IS $$
1156 List of MARC formats supported by this Evergreen
1157 database. This exists primarily as a hook for future
1158 support of UNIMARC, though whether that will ever
1159 happen remains to be seen.
1162 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1164 CREATE TABLE config.marc_field (
1165 id SERIAL PRIMARY KEY,
1166 marc_format INTEGER NOT NULL
1167 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1168 marc_record_type config.marc_record_type NOT NULL,
1169 tag CHAR(3) NOT NULL,
1172 fixed_field BOOLEAN,
1176 owner INTEGER -- REFERENCES actor.org_unit (id)
1177 -- if the owner is null, the data about the field is
1178 -- assumed to come from the controlling MARC standard
1181 COMMENT ON TABLE config.marc_field IS $$
1182 This table stores a list of MARC fields recognized by the Evergreen
1183 instance. Note that we're not aiming for completely generic ISO2709
1184 support: we're assuming things like three characters for a tag,
1185 one-character subfield labels, two indicators per variable data field,
1186 and the like, all of which are technically specializations of ISO2709.
1188 Of particular significance is the owner column; if it's set to a null
1189 value, the field definition is assumed to come from a national
1190 standards body; if it's set to a non-null value, the field definition
1191 is an OU-level addition to or override of the standard.
1194 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1195 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1197 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1198 ON config.marc_field(marc_format, marc_record_type, tag)
1199 WHERE owner IS NULL;
1200 ALTER TABLE config.marc_field
1201 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1202 CHECK ((owner IS NOT NULL) OR
1205 repeatable IS NOT NULL AND
1206 mandatory IS NOT NULL AND
1211 CREATE TABLE config.marc_subfield (
1212 id SERIAL PRIMARY KEY,
1213 marc_format INTEGER NOT NULL
1214 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1215 marc_record_type config.marc_record_type NOT NULL,
1216 tag CHAR(3) NOT NULL,
1217 code CHAR(1) NOT NULL,
1223 REFERENCES config.record_attr_definition (name)
1224 DEFERRABLE INITIALLY DEFERRED,
1225 owner INTEGER -- REFERENCES actor.org_unit (id)
1226 -- if the owner is null, the data about the subfield is
1227 -- assumed to come from the controlling MARC standard
1230 COMMENT ON TABLE config.marc_subfield IS $$
1231 This table stores the list of subfields recognized by this Evergreen
1232 instance. As with config.marc_field, of particular significance is the
1233 owner column; if it's set to a null value, the subfield definition is
1234 assumed to come from a national standards body; if it's set to a non-null
1235 value, the subfield definition is an OU-level addition to or override
1239 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1240 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1241 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1242 WHERE owner IS NULL;
1243 ALTER TABLE config.marc_subfield
1244 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1245 CHECK ((owner IS NOT NULL) OR
1248 repeatable IS NOT NULL AND
1249 mandatory IS NOT NULL AND
1254 CREATE TABLE config.copy_tag_type (
1255 code TEXT NOT NULL PRIMARY KEY,
1256 label TEXT NOT NULL,
1257 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1260 CREATE INDEX config_copy_tag_type_owner_idx
1261 ON config.copy_tag_type (owner);