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
206 representative_field INTEGER REFERENCES config.metabib_field(id),
207 CONSTRAINT rep_field_unique UNIQUE(representative_field),
208 CONSTRAINT rep_field_is_valid CHECK (
209 representative_field IS NULL OR
210 config.metabib_representative_field_is_valid(representative_field, name)
214 CREATE TABLE config.metabib_field (
215 id SERIAL PRIMARY KEY,
216 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
220 weight INT NOT NULL DEFAULT 1,
221 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
222 search_field BOOL NOT NULL DEFAULT TRUE,
223 facet_field BOOL NOT NULL DEFAULT FALSE,
224 browse_field BOOL NOT NULL DEFAULT TRUE,
226 browse_sort_xpath TEXT,
229 authority_xpath TEXT,
231 restrict BOOL DEFAULT FALSE NOT NULL,
232 display_field BOOL NOT NULL DEFAULT TRUE
234 COMMENT ON TABLE config.metabib_field IS $$
235 XPath used for record indexing ingest
237 This table contains the XPath used to chop up MODS into its
238 indexable parts. Each XPath entry is named and assigned to
239 a "class" of either title, subject, author, keyword, series
243 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
245 CREATE TABLE config.ts_config_list (
249 COMMENT ON TABLE config.ts_config_list IS $$
252 A list of full text configs with names and descriptions.
255 CREATE TABLE config.metabib_class_ts_map (
256 id SERIAL PRIMARY KEY,
257 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
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,
263 always BOOL NOT NULL DEFAULT true
265 COMMENT ON TABLE config.metabib_class_ts_map IS $$
266 Text Search Configs for metabib class indexing
268 This table contains text search config definitions for
269 storing index_vector values.
272 CREATE TABLE config.metabib_field_ts_map (
273 id SERIAL PRIMARY KEY,
274 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
275 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
276 active BOOL NOT NULL DEFAULT TRUE,
277 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
278 index_lang TEXT NULL,
279 search_lang TEXT NULL
281 COMMENT ON TABLE config.metabib_field_ts_map IS $$
282 Text Search Configs for metabib field indexing
284 This table contains text search config definitions for
285 storing index_vector values.
288 CREATE TABLE config.metabib_search_alias (
289 alias TEXT PRIMARY KEY,
290 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
291 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
294 CREATE TABLE config.non_cataloged_type (
295 id SERIAL PRIMARY KEY,
296 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
298 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
299 in_house BOOL NOT NULL DEFAULT FALSE,
300 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
302 COMMENT ON TABLE config.non_cataloged_type IS $$
303 Types of valid non-cataloged items.
306 CREATE TABLE config.identification_type (
307 id SERIAL PRIMARY KEY,
308 name TEXT NOT NULL UNIQUE
310 COMMENT ON TABLE config.identification_type IS $$
311 Types of valid patron identification.
313 Each patron must display at least one valid form of identification
314 in order to get a library card. This table lists those forms.
317 CREATE TABLE config.rule_circ_duration (
318 id SERIAL PRIMARY KEY,
319 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
320 extended INTERVAL NOT NULL,
321 normal INTERVAL NOT NULL,
322 shrt INTERVAL NOT NULL,
323 max_renewals INT NOT NULL
325 COMMENT ON TABLE config.rule_circ_duration IS $$
326 Circulation Duration rules
328 Each circulation is given a duration based on one of these rules.
331 CREATE TABLE config.hard_due_date (
332 id SERIAL PRIMARY KEY,
333 name TEXT NOT NULL UNIQUE,
334 ceiling_date TIMESTAMPTZ NOT NULL,
335 forceto BOOL NOT NULL,
336 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
339 CREATE TABLE config.hard_due_date_values (
340 id SERIAL PRIMARY KEY,
341 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
342 DEFERRABLE INITIALLY DEFERRED,
343 ceiling_date TIMESTAMPTZ NOT NULL,
344 active_date TIMESTAMPTZ NOT NULL
347 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
349 temp_value config.hard_due_date_values%ROWTYPE;
353 SELECT DISTINCT ON (hard_due_date) *
354 FROM config.hard_due_date_values
355 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
356 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
358 UPDATE config.hard_due_date
359 SET ceiling_date = temp_value.ceiling_date
360 WHERE id = temp_value.hard_due_date
361 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
362 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
365 updated := updated + 1;
371 $func$ LANGUAGE plpgsql;
373 CREATE TABLE config.rule_max_fine (
374 id SERIAL PRIMARY KEY,
375 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
376 amount NUMERIC(6,2) NOT NULL,
377 is_percent BOOL NOT NULL DEFAULT FALSE
379 COMMENT ON TABLE config.rule_max_fine IS $$
380 Circulation Max Fine rules
382 Each circulation is given a maximum fine based on one of
386 CREATE TABLE config.rule_recurring_fine (
387 id SERIAL PRIMARY KEY,
388 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
389 high NUMERIC(6,2) NOT NULL,
390 normal NUMERIC(6,2) NOT NULL,
391 low NUMERIC(6,2) NOT NULL,
392 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
393 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
395 COMMENT ON TABLE config.rule_recurring_fine IS $$
396 Circulation Recurring Fine rules
398 Each circulation is given a recurring fine amount based on one of
399 these rules. Note that it is recommended to run the fine generator
400 (from cron) at least as frequently as the lowest recurrence interval
401 used by your circulation rules so that accrued fines will be up
406 CREATE TABLE config.rule_age_hold_protect (
407 id SERIAL PRIMARY KEY,
408 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
409 age INTERVAL NOT NULL,
412 COMMENT ON TABLE config.rule_age_hold_protect IS $$
413 Hold Item Age Protection rules
415 A hold request can only capture new(ish) items when they are
416 within a particular proximity of the pickup_lib of the request.
417 The proximity ('prox' column) is calculated by counting
418 the number of tree edges between the pickup_lib and either the
419 owning_lib or circ_lib of the copy that could fulfill the hold,
420 as determined by the distance_is_from_owner value of the hold matrix
421 rule controlling the hold request.
424 CREATE TABLE config.copy_status (
425 id SERIAL PRIMARY KEY,
426 name TEXT NOT NULL UNIQUE,
427 holdable BOOL NOT NULL DEFAULT FALSE,
428 opac_visible BOOL NOT NULL DEFAULT FALSE,
429 copy_active BOOL NOT NULL DEFAULT FALSE,
430 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
431 is_available BOOL NOT NULL DEFAULT FALSE
433 COMMENT ON TABLE config.copy_status IS $$
436 The available copy statuses, and whether a copy in that
437 status is available for hold request capture. 0 (zero) is
438 the only special number in this set, meaning that the item
439 is available for immediate checkout, and is counted as available
442 Statuses with an ID below 100 are not removable, and have special
443 meaning in the code. Do not change them except to translate the
446 You may add and remove statuses above 100, and these can be used
447 to remove items from normal circulation without affecting the rest
448 of the copy's values or its location.
451 CREATE TABLE config.net_access_level (
452 id SERIAL PRIMARY KEY,
453 name TEXT NOT NULL UNIQUE
455 COMMENT ON TABLE config.net_access_level IS $$
456 Patron Network Access level
458 This will be used to inform the in-library firewall of how much
459 internet access the using patron should be allowed.
463 CREATE TABLE config.remote_account (
464 id SERIAL PRIMARY KEY,
466 host TEXT NOT NULL, -- name or IP, :port optional
467 username TEXT, -- optional, since we could default to $USER
468 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
469 account TEXT, -- aka profile or FTP "account" command
470 path TEXT, -- aka directory
471 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
472 last_activity TIMESTAMP WITH TIME ZONE
475 CREATE TABLE config.marc21_rec_type_map (
476 code TEXT PRIMARY KEY,
477 type_val TEXT NOT NULL,
478 blvl_val TEXT NOT NULL
481 CREATE TABLE config.marc21_ff_pos_map (
482 id SERIAL PRIMARY KEY,
483 fixed_field TEXT NOT NULL,
485 rec_type TEXT NOT NULL,
486 start_pos INT NOT NULL,
488 default_val TEXT NOT NULL DEFAULT ' '
491 CREATE TABLE config.marc21_physical_characteristic_type_map (
492 ptype_key TEXT PRIMARY KEY,
493 label TEXT NOT NULL -- I18N
496 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
497 id SERIAL PRIMARY KEY,
498 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
499 subfield TEXT NOT NULL,
500 start_pos INT NOT NULL,
502 label TEXT NOT NULL -- I18N
505 CREATE TABLE config.marc21_physical_characteristic_value_map (
506 id SERIAL PRIMARY KEY,
508 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
509 label TEXT NOT NULL -- I18N
513 CREATE TABLE config.z3950_source (
514 name TEXT PRIMARY KEY,
515 label TEXT NOT NULL UNIQUE,
519 record_format TEXT NOT NULL DEFAULT 'FI',
520 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
521 auth BOOL NOT NULL DEFAULT TRUE,
522 use_perm INT -- REFERENCES permission.perm_list (id)
525 COMMENT ON TABLE config.z3950_source IS $$
528 Each row in this table represents a database searchable via Z39.50.
531 COMMENT ON COLUMN config.z3950_source.record_format IS $$
535 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
536 Z39.50 preferred record syntax..
539 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
540 If set, this permission is required for the source to be listed in the staff
541 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
544 CREATE TABLE config.z3950_attr (
545 id SERIAL PRIMARY KEY,
546 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
551 truncation INT NOT NULL DEFAULT 0,
552 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
555 CREATE TABLE config.z3950_source_credentials (
556 id SERIAL PRIMARY KEY,
557 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
558 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
561 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
564 CREATE TABLE config.i18n_locale (
565 code TEXT PRIMARY KEY,
566 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
567 name TEXT UNIQUE NOT NULL,
569 rtl BOOL NOT NULL DEFAULT FALSE
572 CREATE TABLE config.i18n_core (
573 id BIGSERIAL PRIMARY KEY,
574 fq_field TEXT NOT NULL,
575 identity_value TEXT NOT NULL,
576 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
580 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
582 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
586 UPDATE config.i18n_core
587 SET identity_value = $$ || quote_literal(new_ident) || $$
588 WHERE fq_field LIKE '$$ || hint || $$.%'
589 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
594 $_$ LANGUAGE PLPGSQL;
596 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
598 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
601 $_$ LANGUAGE PLPGSQL;
603 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
605 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
608 $_$ LANGUAGE PLPGSQL;
610 CREATE TABLE config.billing_type (
611 id SERIAL PRIMARY KEY,
613 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
614 default_price NUMERIC(6,2),
615 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
618 CREATE TABLE config.settings_group (
619 name TEXT PRIMARY KEY,
620 label TEXT UNIQUE NOT NULL -- I18N
623 CREATE TABLE config.org_unit_setting_type (
624 name TEXT PRIMARY KEY,
625 label TEXT UNIQUE NOT NULL,
626 grp TEXT REFERENCES config.settings_group (name),
628 datatype TEXT NOT NULL DEFAULT 'string',
633 -- define valid datatypes
635 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
636 ( 'bool', 'integer', 'float', 'currency', 'interval',
637 'date', 'string', 'object', 'array', 'link' ) ),
639 -- fm_class is meaningful only for 'link' datatype
641 CONSTRAINT coust_no_empty_link CHECK
642 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
643 ( datatype <> 'link' AND fm_class IS NULL ) )
646 CREATE TABLE config.usr_setting_type (
648 name TEXT PRIMARY KEY,
649 opac_visible BOOL NOT NULL DEFAULT FALSE,
650 label TEXT UNIQUE NOT NULL,
652 grp TEXT REFERENCES config.settings_group (name),
653 datatype TEXT NOT NULL DEFAULT 'string',
658 -- define valid datatypes
660 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
661 ( 'bool', 'integer', 'float', 'currency', 'interval',
662 'date', 'string', 'object', 'array', 'link' ) ),
665 -- fm_class is meaningful only for 'link' datatype
667 CONSTRAINT coust_no_empty_link CHECK
668 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
669 ( datatype <> 'link' AND fm_class IS NULL ) )
673 -- Some handy functions, based on existing ones, to provide optional ingest normalization
675 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
676 SELECT SUBSTRING($1,$2);
677 $func$ LANGUAGE SQL STRICT IMMUTABLE;
679 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
680 SELECT SUBSTRING($1,1,$2);
681 $func$ LANGUAGE SQL STRICT IMMUTABLE;
683 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
684 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
685 $func$ LANGUAGE SQL STRICT IMMUTABLE;
687 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
688 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
689 $func$ LANGUAGE SQL STRICT IMMUTABLE;
691 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
692 SELECT approximate_date( $1, '0');
693 $func$ LANGUAGE SQL STRICT IMMUTABLE;
695 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
696 SELECT approximate_date( $1, '9');
697 $func$ LANGUAGE SQL STRICT IMMUTABLE;
699 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
700 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
701 $func$ LANGUAGE SQL STRICT IMMUTABLE;
703 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
704 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
705 $func$ LANGUAGE SQL STRICT IMMUTABLE;
707 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
712 # Find the first ISBN, force it to ISBN13 and return it
716 foreach my $word (split(/\s/, $input)) {
717 my $isbn = Business::ISBN->new($word);
719 # First check the checksum; if it is not valid, fix it and add the original
720 # bad-checksum ISBN to the output
721 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
722 $isbn->fix_checksum();
725 # If we now have a valid ISBN, force it to ISBN13 and return it
726 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
729 $func$ LANGUAGE PLPERLU;
731 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
732 Inspired by translate_isbn1013
734 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
735 version without hypens and with a repaired checksum if the checksum was bad
739 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
744 # For each ISBN found in a single string containing a set of ISBNs:
745 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
746 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
751 foreach my $word (split(/\s/, $input)) {
752 my $isbn = Business::ISBN->new($word);
754 # First check the checksum; if it is not valid, fix it and add the original
755 # bad-checksum ISBN to the output
756 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
757 $output .= $isbn->isbn() . " ";
758 $isbn->fix_checksum();
761 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
762 # and add the normalized original ISBN to the output
763 if ($isbn && $isbn->is_valid()) {
764 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
765 $output .= $isbn->isbn . " ";
767 # If we successfully converted the ISBN to its counterpart, add the
768 # converted ISBN to the output as well
769 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
772 return $output if $output;
774 # If there were no valid ISBNs, just return the raw input
776 $func$ LANGUAGE PLPERLU;
778 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
779 The translate_isbn1013 function takes an input ISBN and returns the
780 following in a single space-delimited string if the input ISBN is valid:
781 - The normalized input ISBN (hyphens stripped)
782 - The normalized input ISBN with a fixed checksum if the checksum was bad
783 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
786 -- And ... a table in which to register them
788 CREATE TABLE config.index_normalizer (
789 id SERIAL PRIMARY KEY,
790 name TEXT UNIQUE NOT NULL,
793 param_count INT NOT NULL DEFAULT 0
796 CREATE TABLE config.metabib_field_index_norm_map (
797 id SERIAL PRIMARY KEY,
798 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
799 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
801 pos INT NOT NULL DEFAULT 0
804 CREATE TABLE config.record_attr_definition (
805 name TEXT PRIMARY KEY,
806 label TEXT NOT NULL, -- I18N
808 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
809 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
810 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
811 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
813 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
814 tag TEXT, -- LIKE format
815 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
817 -- This is used for both tag/sf and xpath entries
820 -- For xpath-extracted attrs
822 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
827 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
829 -- For phys-char fields
830 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
832 -- Source of vocabulary terms for this record attribute;
833 -- typically will be a URI referring to a SKOS vocabulary
837 CREATE TABLE config.record_attr_index_norm_map (
838 id SERIAL PRIMARY KEY,
839 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
840 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
842 pos INT NOT NULL DEFAULT 0
845 CREATE TABLE config.coded_value_map (
846 id SERIAL PRIMARY KEY,
847 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
851 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
853 is_simple BOOL NOT NULL DEFAULT FALSE,
854 concept_uri TEXT -- URI expressing the SKOS concept that the
855 -- coded value represents
858 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
860 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
861 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
862 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
863 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
864 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
865 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
866 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
868 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$
870 current_row config.coded_value_map%ROWTYPE;
872 -- Look for a current value
873 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
875 IF FOUND AND NOT add_only THEN
876 -- Update anything we were handed
877 current_row.value := COALESCE(current_row.value, in_value);
878 current_row.description := COALESCE(current_row.description, in_description);
879 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
880 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
881 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
882 UPDATE config.coded_value_map
884 value = current_row.value,
885 description = current_row.description,
886 opac_visible = current_row.opac_visible,
887 search_label = current_row.search_label,
888 is_simple = current_row.is_simple
889 WHERE id = current_row.id;
891 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
892 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
895 $f$ LANGUAGE PLPGSQL;
897 CREATE TABLE config.composite_attr_entry_definition(
898 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
899 definition TEXT NOT NULL -- JSON
902 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
903 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
904 SELECT DISTINCT l.version
905 FROM config.upgrade_log l
906 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
907 WHERE d.db_patch = $1
910 -- List applied db patches that are superseded by (and block the application of) my_db_patch
911 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
912 SELECT DISTINCT l.version
913 FROM config.upgrade_log l
914 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
915 WHERE d.db_patch = $1
918 -- List applied db patches that deprecates (and block the application of) my_db_patch
919 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
921 FROM config.db_patch_dependencies
922 WHERE ARRAY[$1]::TEXT[] && deprecates
925 -- List applied db patches that supersedes (and block the application of) my_db_patch
926 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
928 FROM config.db_patch_dependencies
929 WHERE ARRAY[$1]::TEXT[] && supersedes
932 -- Make sure that no deprecated or superseded db patches are currently applied
933 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
935 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
937 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
939 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
941 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
944 -- Raise an exception if there are, in fact, dep/sup conflict
945 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
950 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
951 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
952 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
954 Upgrade script % can not be applied:
955 applied deprecated scripts %
956 applied superseded scripts %
960 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
961 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
962 evergreen.upgrade_list_applied_deprecated(my_db_patch),
963 evergreen.upgrade_list_applied_superseded(my_db_patch);
966 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
971 CREATE TABLE config.barcode_completion (
972 id SERIAL PRIMARY KEY,
973 active BOOL NOT NULL DEFAULT true,
974 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
977 length INT NOT NULL DEFAULT 0,
979 padding_end BOOL NOT NULL DEFAULT false,
980 asset BOOL NOT NULL DEFAULT true,
981 actor BOOL NOT NULL DEFAULT true
984 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
986 -- Add support for logging, only keep the most recent five rows for each category.
989 CREATE TABLE config.org_unit_setting_type_log (
990 id BIGSERIAL PRIMARY KEY,
991 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
992 org INT, --REFERENCES actor.org_unit (id),
995 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
998 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
999 Org Unit setting Logs
1001 This table contains the most recent changes to each setting
1002 in actor.org_unit_setting, allowing for mistakes to be undone.
1003 This is NOT meant to be an auditor, but rather an undo/redo.
1006 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1008 -- Only keeps the most recent five settings changes.
1009 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1010 (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);
1012 IF (TG_OP = 'UPDATE') THEN
1014 ELSIF (TG_OP = 'INSERT') THEN
1019 $oustl_limit$ LANGUAGE plpgsql;
1021 CREATE TRIGGER limit_logs_oust
1022 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1023 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1025 CREATE TABLE config.sms_carrier (
1026 id SERIAL PRIMARY KEY,
1030 active BOOLEAN DEFAULT TRUE
1033 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1035 CREATE TABLE config.usr_activity_type (
1036 id SERIAL PRIMARY KEY,
1040 label TEXT NOT NULL, -- i18n
1041 egroup config.usr_activity_group NOT NULL,
1042 enabled BOOL NOT NULL DEFAULT TRUE,
1043 transient BOOL NOT NULL DEFAULT TRUE,
1044 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1047 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1048 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1050 CREATE TABLE config.filter_dialog_interface (
1051 key TEXT PRIMARY KEY,
1055 CREATE TABLE config.filter_dialog_filter_set (
1056 id SERIAL PRIMARY KEY,
1058 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1059 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1060 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1061 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1062 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1063 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1066 CREATE TABLE config.best_hold_order(
1067 id SERIAL PRIMARY KEY,
1068 name TEXT UNIQUE, -- i18n
1069 pprox INT, -- copy capture <-> pickup lib prox
1070 hprox INT, -- copy circ lib <-> request lib prox
1071 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1072 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1073 priority INT, -- group hold priority
1074 cut INT, -- cut-in-line
1075 depth INT, -- selection depth
1076 htime INT, -- time since last home-lib circ exceeds org-unit setting
1077 rtime INT, -- request time
1078 shtime INT -- time since copy last trip home exceeds org-unit setting
1081 -- At least one of these columns must contain a non-null value
1082 ALTER TABLE config.best_hold_order ADD CHECK ((
1083 pprox IS NOT NULL OR
1084 hprox IS NOT NULL OR
1085 aprox IS NOT NULL OR
1086 priority IS NOT NULL OR
1088 depth IS NOT NULL OR
1089 htime IS NOT NULL OR
1093 CREATE OR REPLACE FUNCTION
1094 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1097 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1103 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1106 $func$ LANGUAGE PLPGSQL STABLE;
1108 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1109 Used by a config.z3950_index_field_map constraint trigger
1110 to verify z3950_attr_type maps.
1113 -- drop these in down here since they reference config.metabib_field
1114 -- and config.record_attr_definition
1115 CREATE TABLE config.z3950_index_field_map (
1116 id SERIAL PRIMARY KEY,
1117 label TEXT NOT NULL, -- i18n
1118 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1119 record_attr TEXT REFERENCES config.record_attr_definition(name),
1120 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1121 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1122 CONSTRAINT metabib_field_or_record_attr CHECK (
1123 metabib_field IS NOT NULL OR
1124 record_attr IS NOT NULL
1126 CONSTRAINT attr_or_attr_type CHECK (
1127 z3950_attr IS NOT NULL OR
1128 z3950_attr_type IS NOT NULL
1132 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1133 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1134 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1136 CREATE TABLE config.marc_format (
1137 id SERIAL PRIMARY KEY,
1141 COMMENT ON TABLE config.marc_format IS $$
1142 List of MARC formats supported by this Evergreen
1143 database. This exists primarily as a hook for future
1144 support of UNIMARC, though whether that will ever
1145 happen remains to be seen.
1148 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1150 CREATE TABLE config.marc_field (
1151 id SERIAL PRIMARY KEY,
1152 marc_format INTEGER NOT NULL
1153 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1154 marc_record_type config.marc_record_type NOT NULL,
1155 tag CHAR(3) NOT NULL,
1158 fixed_field BOOLEAN,
1162 owner INTEGER -- REFERENCES actor.org_unit (id)
1163 -- if the owner is null, the data about the field is
1164 -- assumed to come from the controlling MARC standard
1167 COMMENT ON TABLE config.marc_field IS $$
1168 This table stores a list of MARC fields recognized by the Evergreen
1169 instance. Note that we're not aiming for completely generic ISO2709
1170 support: we're assuming things like three characters for a tag,
1171 one-character subfield labels, two indicators per variable data field,
1172 and the like, all of which are technically specializations of ISO2709.
1174 Of particular significance is the owner column; if it's set to a null
1175 value, the field definition is assumed to come from a national
1176 standards body; if it's set to a non-null value, the field definition
1177 is an OU-level addition to or override of the standard.
1180 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1181 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1183 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1184 ON config.marc_field(marc_format, marc_record_type, tag)
1185 WHERE owner IS NULL;
1186 ALTER TABLE config.marc_field
1187 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1188 CHECK ((owner IS NOT NULL) OR
1191 repeatable IS NOT NULL AND
1192 mandatory IS NOT NULL AND
1197 CREATE TABLE config.marc_subfield (
1198 id SERIAL PRIMARY KEY,
1199 marc_format INTEGER NOT NULL
1200 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1201 marc_record_type config.marc_record_type NOT NULL,
1202 tag CHAR(3) NOT NULL,
1203 code CHAR(1) NOT NULL,
1209 REFERENCES config.record_attr_definition (name)
1210 DEFERRABLE INITIALLY DEFERRED,
1211 owner INTEGER -- REFERENCES actor.org_unit (id)
1212 -- if the owner is null, the data about the subfield is
1213 -- assumed to come from the controlling MARC standard
1216 COMMENT ON TABLE config.marc_subfield IS $$
1217 This table stores the list of subfields recognized by this Evergreen
1218 instance. As with config.marc_field, of particular significance is the
1219 owner column; if it's set to a null value, the subfield definition is
1220 assumed to come from a national standards body; if it's set to a non-null
1221 value, the subfield definition is an OU-level addition to or override
1225 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1226 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1227 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1228 WHERE owner IS NULL;
1229 ALTER TABLE config.marc_subfield
1230 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1231 CHECK ((owner IS NOT NULL) OR
1234 repeatable IS NOT NULL AND
1235 mandatory IS NOT NULL AND
1240 CREATE TABLE config.copy_tag_type (
1241 code TEXT NOT NULL PRIMARY KEY,
1242 label TEXT NOT NULL,
1243 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1246 CREATE INDEX config_copy_tag_type_owner_idx
1247 ON config.copy_tag_type (owner);