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 ('ingest.skip_display_indexing');
52 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
53 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');
56 CREATE TABLE config.global_flag (
58 ) INHERITS (config.internal_flag);
59 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
61 CREATE TABLE config.upgrade_log (
62 version TEXT PRIMARY KEY,
63 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
67 CREATE TABLE config.db_patch_dependencies (
68 db_patch TEXT PRIMARY KEY,
73 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
79 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
81 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
87 CREATE TRIGGER no_overlapping_sups
88 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
89 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
91 CREATE TRIGGER no_overlapping_deps
92 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
93 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
95 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1110', :eg_version); --gmcharlt/cesardv/kmlussier
97 CREATE TABLE config.bib_source (
98 id SERIAL PRIMARY KEY,
99 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
100 source TEXT NOT NULL UNIQUE,
101 transcendant BOOL NOT NULL DEFAULT FALSE,
102 can_have_copies BOOL NOT NULL DEFAULT TRUE
104 COMMENT ON TABLE config.bib_source IS $$
105 This is table is used to set up the relative "quality" of each
106 MARC source, such as OCLC. Also identifies "transcendant" sources,
107 i.e., sources of bib records that should display in the OPAC
108 even if no copies or located URIs are attached. Also indicates if
109 the source is allowed to have actual copies on its bibs. Volumes
110 for targeted URIs are unaffected by this setting.
113 CREATE TABLE config.standing (
114 id SERIAL PRIMARY KEY,
115 value TEXT NOT NULL UNIQUE
117 COMMENT ON TABLE config.standing IS $$
120 This table contains the values that can be applied to a patron
121 by a staff member. These values should not be changed, other
122 than for translation, as the ID column is currently a "magic
123 number" in the source. :(
126 CREATE TABLE config.standing_penalty (
127 id SERIAL PRIMARY KEY,
128 name TEXT NOT NULL UNIQUE,
131 staff_alert BOOL NOT NULL DEFAULT FALSE,
133 ignore_proximity INTEGER
136 CREATE TABLE config.xml_transform (
137 name TEXT PRIMARY KEY,
138 namespace_uri TEXT NOT NULL,
139 prefix TEXT NOT NULL,
143 CREATE TABLE config.biblio_fingerprint (
144 id SERIAL PRIMARY KEY,
147 first_word BOOL NOT NULL DEFAULT FALSE,
148 format TEXT NOT NULL DEFAULT 'marcxml'
151 INSERT INTO config.biblio_fingerprint (name, xpath, format)
154 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
155 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
157 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
158 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
162 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
165 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
168 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
169 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
174 INSERT INTO config.biblio_fingerprint (name, xpath, format)
177 '//mods32:mods/mods32:titleInfo/mods32:partName',
181 INSERT INTO config.biblio_fingerprint (name, xpath, format)
184 '//mods32:mods/mods32:titleInfo/mods32:partNumber',
188 CREATE TABLE config.metabib_class (
189 name TEXT PRIMARY KEY,
190 label TEXT NOT NULL UNIQUE,
191 buoyant BOOL DEFAULT FALSE NOT NULL,
192 restrict BOOL DEFAULT FALSE NOT NULL,
193 combined BOOL DEFAULT FALSE NOT NULL,
194 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
195 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
196 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
197 d_weight NUMERIC DEFAULT 0.1 NOT NULL
200 CREATE TABLE config.metabib_field (
201 id SERIAL PRIMARY KEY,
202 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
206 weight INT NOT NULL DEFAULT 1,
207 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
208 search_field BOOL NOT NULL DEFAULT TRUE,
209 facet_field BOOL NOT NULL DEFAULT FALSE,
210 browse_field BOOL NOT NULL DEFAULT TRUE,
212 browse_sort_xpath TEXT,
215 authority_xpath TEXT,
217 restrict BOOL DEFAULT FALSE NOT NULL,
218 display_field BOOL NOT NULL DEFAULT TRUE
220 COMMENT ON TABLE config.metabib_field IS $$
221 XPath used for record indexing ingest
223 This table contains the XPath used to chop up MODS into its
224 indexable parts. Each XPath entry is named and assigned to
225 a "class" of either title, subject, author, keyword, series
229 CREATE TABLE config.metabib_field_virtual_map (
230 id SERIAL PRIMARY KEY,
231 real INT NOT NULL REFERENCES config.metabib_field (id),
232 virtual INT NOT NULL REFERENCES config.metabib_field (id),
233 weight INT NOT NULL DEFAULT 1
235 COMMENT ON TABLE config.metabib_field_virtual_map IS $$
236 Maps between real (physically extracted) index definitions
237 and virtual (target sync, no required extraction of its own)
240 The virtual side may not extract any data of its own, but
241 will collect data from all of the real fields. This reduces
242 extraction (ingest) overhead by eliminating duplcated extraction,
243 and allows for searching across novel combinations of fields, such
244 as names used as either subjects or authors. By preserving this
245 mapping rather than defining duplicate extractions, information
246 about the originating, "real" index definitions can be used
247 in interesting ways, such as highlighting in search results.
250 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
252 CREATE TABLE config.display_field_map (
253 name TEXT PRIMARY KEY,
254 field INTEGER REFERENCES config.metabib_field (id),
255 multi BOOLEAN DEFAULT FALSE
258 CREATE TABLE config.ts_config_list (
262 COMMENT ON TABLE config.ts_config_list IS $$
265 A list of full text configs with names and descriptions.
268 CREATE TABLE config.metabib_class_ts_map (
269 id SERIAL PRIMARY KEY,
270 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
271 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
272 active BOOL NOT NULL DEFAULT TRUE,
273 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
274 index_lang TEXT NULL,
275 search_lang TEXT NULL,
276 always BOOL NOT NULL DEFAULT true
278 COMMENT ON TABLE config.metabib_class_ts_map IS $$
279 Text Search Configs for metabib class indexing
281 This table contains text search config definitions for
282 storing index_vector values.
285 CREATE TABLE config.metabib_field_ts_map (
286 id SERIAL PRIMARY KEY,
287 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
288 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
289 active BOOL NOT NULL DEFAULT TRUE,
290 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
291 index_lang TEXT NULL,
292 search_lang TEXT NULL
294 COMMENT ON TABLE config.metabib_field_ts_map IS $$
295 Text Search Configs for metabib field indexing
297 This table contains text search config definitions for
298 storing index_vector values.
301 CREATE TABLE config.metabib_search_alias (
302 alias TEXT PRIMARY KEY,
303 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
304 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
307 CREATE TABLE config.non_cataloged_type (
308 id SERIAL PRIMARY KEY,
309 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
311 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
312 in_house BOOL NOT NULL DEFAULT FALSE,
313 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
315 COMMENT ON TABLE config.non_cataloged_type IS $$
316 Types of valid non-cataloged items.
319 CREATE TABLE config.identification_type (
320 id SERIAL PRIMARY KEY,
321 name TEXT NOT NULL UNIQUE
323 COMMENT ON TABLE config.identification_type IS $$
324 Types of valid patron identification.
326 Each patron must display at least one valid form of identification
327 in order to get a library card. This table lists those forms.
330 CREATE TABLE config.rule_circ_duration (
331 id SERIAL PRIMARY KEY,
332 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
333 extended INTERVAL NOT NULL,
334 normal INTERVAL NOT NULL,
335 shrt INTERVAL NOT NULL,
336 max_renewals INT NOT NULL
338 COMMENT ON TABLE config.rule_circ_duration IS $$
339 Circulation Duration rules
341 Each circulation is given a duration based on one of these rules.
344 CREATE TABLE config.hard_due_date (
345 id SERIAL PRIMARY KEY,
346 name TEXT NOT NULL UNIQUE,
347 ceiling_date TIMESTAMPTZ NOT NULL,
348 forceto BOOL NOT NULL,
349 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
352 CREATE TABLE config.hard_due_date_values (
353 id SERIAL PRIMARY KEY,
354 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
355 DEFERRABLE INITIALLY DEFERRED,
356 ceiling_date TIMESTAMPTZ NOT NULL,
357 active_date TIMESTAMPTZ NOT NULL
360 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
362 temp_value config.hard_due_date_values%ROWTYPE;
366 SELECT DISTINCT ON (hard_due_date) *
367 FROM config.hard_due_date_values
368 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
369 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
371 UPDATE config.hard_due_date
372 SET ceiling_date = temp_value.ceiling_date
373 WHERE id = temp_value.hard_due_date
374 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
375 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
378 updated := updated + 1;
384 $func$ LANGUAGE plpgsql;
386 CREATE TABLE config.rule_max_fine (
387 id SERIAL PRIMARY KEY,
388 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
389 amount NUMERIC(6,2) NOT NULL,
390 is_percent BOOL NOT NULL DEFAULT FALSE
392 COMMENT ON TABLE config.rule_max_fine IS $$
393 Circulation Max Fine rules
395 Each circulation is given a maximum fine based on one of
399 CREATE TABLE config.rule_recurring_fine (
400 id SERIAL PRIMARY KEY,
401 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
402 high NUMERIC(6,2) NOT NULL,
403 normal NUMERIC(6,2) NOT NULL,
404 low NUMERIC(6,2) NOT NULL,
405 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
406 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
408 COMMENT ON TABLE config.rule_recurring_fine IS $$
409 Circulation Recurring Fine rules
411 Each circulation is given a recurring fine amount based on one of
412 these rules. Note that it is recommended to run the fine generator
413 (from cron) at least as frequently as the lowest recurrence interval
414 used by your circulation rules so that accrued fines will be up
419 CREATE TABLE config.rule_age_hold_protect (
420 id SERIAL PRIMARY KEY,
421 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
422 age INTERVAL NOT NULL,
425 COMMENT ON TABLE config.rule_age_hold_protect IS $$
426 Hold Item Age Protection rules
428 A hold request can only capture new(ish) items when they are
429 within a particular proximity of the pickup_lib of the request.
430 The proximity ('prox' column) is calculated by counting
431 the number of tree edges between the pickup_lib and either the
432 owning_lib or circ_lib of the copy that could fulfill the hold,
433 as determined by the distance_is_from_owner value of the hold matrix
434 rule controlling the hold request.
437 CREATE TABLE config.copy_status (
438 id SERIAL PRIMARY KEY,
439 name TEXT NOT NULL UNIQUE,
440 holdable BOOL NOT NULL DEFAULT FALSE,
441 opac_visible BOOL NOT NULL DEFAULT FALSE,
442 copy_active BOOL NOT NULL DEFAULT FALSE,
443 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
444 is_available BOOL NOT NULL DEFAULT FALSE
446 COMMENT ON TABLE config.copy_status IS $$
449 The available copy statuses, and whether a copy in that
450 status is available for hold request capture. 0 (zero) is
451 the only special number in this set, meaning that the item
452 is available for immediate checkout, and is counted as available
455 Statuses with an ID below 100 are not removable, and have special
456 meaning in the code. Do not change them except to translate the
459 You may add and remove statuses above 100, and these can be used
460 to remove items from normal circulation without affecting the rest
461 of the copy's values or its location.
464 CREATE TABLE config.net_access_level (
465 id SERIAL PRIMARY KEY,
466 name TEXT NOT NULL UNIQUE
468 COMMENT ON TABLE config.net_access_level IS $$
469 Patron Network Access level
471 This will be used to inform the in-library firewall of how much
472 internet access the using patron should be allowed.
476 CREATE TABLE config.remote_account (
477 id SERIAL PRIMARY KEY,
479 host TEXT NOT NULL, -- name or IP, :port optional
480 username TEXT, -- optional, since we could default to $USER
481 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
482 account TEXT, -- aka profile or FTP "account" command
483 path TEXT, -- aka directory
484 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
485 last_activity TIMESTAMP WITH TIME ZONE
488 CREATE TABLE config.marc21_rec_type_map (
489 code TEXT PRIMARY KEY,
490 type_val TEXT NOT NULL,
491 blvl_val TEXT NOT NULL
494 CREATE TABLE config.marc21_ff_pos_map (
495 id SERIAL PRIMARY KEY,
496 fixed_field TEXT NOT NULL,
498 rec_type TEXT NOT NULL,
499 start_pos INT NOT NULL,
501 default_val TEXT NOT NULL DEFAULT ' '
504 CREATE TABLE config.marc21_physical_characteristic_type_map (
505 ptype_key TEXT PRIMARY KEY,
506 label TEXT NOT NULL -- I18N
509 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
510 id SERIAL PRIMARY KEY,
511 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
512 subfield TEXT NOT NULL,
513 start_pos INT NOT NULL,
515 label TEXT NOT NULL -- I18N
518 CREATE TABLE config.marc21_physical_characteristic_value_map (
519 id SERIAL PRIMARY KEY,
521 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
522 label TEXT NOT NULL -- I18N
526 CREATE TABLE config.z3950_source (
527 name TEXT PRIMARY KEY,
528 label TEXT NOT NULL UNIQUE,
532 record_format TEXT NOT NULL DEFAULT 'FI',
533 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
534 auth BOOL NOT NULL DEFAULT TRUE,
535 use_perm INT -- REFERENCES permission.perm_list (id)
538 COMMENT ON TABLE config.z3950_source IS $$
541 Each row in this table represents a database searchable via Z39.50.
544 COMMENT ON COLUMN config.z3950_source.record_format IS $$
548 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
549 Z39.50 preferred record syntax..
552 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
553 If set, this permission is required for the source to be listed in the staff
554 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
557 CREATE TABLE config.z3950_attr (
558 id SERIAL PRIMARY KEY,
559 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
564 truncation INT NOT NULL DEFAULT 0,
565 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
568 CREATE TABLE config.z3950_source_credentials (
569 id SERIAL PRIMARY KEY,
570 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
571 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
574 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
577 CREATE TABLE config.i18n_locale (
578 code TEXT PRIMARY KEY,
579 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
580 name TEXT UNIQUE NOT NULL,
582 rtl BOOL NOT NULL DEFAULT FALSE
585 CREATE TABLE config.i18n_core (
586 id BIGSERIAL PRIMARY KEY,
587 fq_field TEXT NOT NULL,
588 identity_value TEXT NOT NULL,
589 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
593 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
595 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
599 UPDATE config.i18n_core
600 SET identity_value = $$ || quote_literal(new_ident) || $$
601 WHERE fq_field LIKE '$$ || hint || $$.%'
602 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
607 $_$ LANGUAGE PLPGSQL;
609 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
611 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
614 $_$ LANGUAGE PLPGSQL;
616 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
618 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
621 $_$ LANGUAGE PLPGSQL;
623 CREATE TABLE config.billing_type (
624 id SERIAL PRIMARY KEY,
626 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
627 default_price NUMERIC(6,2),
628 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
631 CREATE TABLE config.settings_group (
632 name TEXT PRIMARY KEY,
633 label TEXT UNIQUE NOT NULL -- I18N
636 CREATE TABLE config.org_unit_setting_type (
637 name TEXT PRIMARY KEY,
638 label TEXT UNIQUE NOT NULL,
639 grp TEXT REFERENCES config.settings_group (name),
641 datatype TEXT NOT NULL DEFAULT 'string',
646 -- define valid datatypes
648 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
649 ( 'bool', 'integer', 'float', 'currency', 'interval',
650 'date', 'string', 'object', 'array', 'link' ) ),
652 -- fm_class is meaningful only for 'link' datatype
654 CONSTRAINT coust_no_empty_link CHECK
655 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
656 ( datatype <> 'link' AND fm_class IS NULL ) )
659 CREATE TABLE config.usr_setting_type (
661 name TEXT PRIMARY KEY,
662 opac_visible BOOL NOT NULL DEFAULT FALSE,
663 label TEXT UNIQUE NOT NULL,
665 grp TEXT REFERENCES config.settings_group (name),
666 datatype TEXT NOT NULL DEFAULT 'string',
671 -- define valid datatypes
673 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
674 ( 'bool', 'integer', 'float', 'currency', 'interval',
675 'date', 'string', 'object', 'array', 'link' ) ),
678 -- fm_class is meaningful only for 'link' datatype
680 CONSTRAINT coust_no_empty_link CHECK
681 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
682 ( datatype <> 'link' AND fm_class IS NULL ) )
686 -- Some handy functions, based on existing ones, to provide optional ingest normalization
688 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
689 SELECT SUBSTRING($1,$2);
690 $func$ LANGUAGE SQL STRICT IMMUTABLE;
692 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
693 SELECT SUBSTRING($1,1,$2);
694 $func$ LANGUAGE SQL STRICT IMMUTABLE;
696 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
697 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
698 $func$ LANGUAGE SQL STRICT IMMUTABLE;
700 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
701 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
702 $func$ LANGUAGE SQL STRICT IMMUTABLE;
704 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
705 SELECT approximate_date( $1, '0');
706 $func$ LANGUAGE SQL STRICT IMMUTABLE;
708 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
709 SELECT approximate_date( $1, '9');
710 $func$ LANGUAGE SQL STRICT IMMUTABLE;
712 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
713 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
714 $func$ LANGUAGE SQL STRICT IMMUTABLE;
716 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
717 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
718 $func$ LANGUAGE SQL STRICT IMMUTABLE;
720 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
725 # Find the first ISBN, force it to ISBN13 and return it
729 foreach my $word (split(/\s/, $input)) {
730 my $isbn = Business::ISBN->new($word);
732 # First check the checksum; if it is not valid, fix it and add the original
733 # bad-checksum ISBN to the output
734 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
735 $isbn->fix_checksum();
738 # If we now have a valid ISBN, force it to ISBN13 and return it
739 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
742 $func$ LANGUAGE PLPERLU;
744 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
745 Inspired by translate_isbn1013
747 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
748 version without hypens and with a repaired checksum if the checksum was bad
752 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
757 # For each ISBN found in a single string containing a set of ISBNs:
758 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
759 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
764 foreach my $word (split(/\s/, $input)) {
765 my $isbn = Business::ISBN->new($word);
767 # First check the checksum; if it is not valid, fix it and add the original
768 # bad-checksum ISBN to the output
769 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
770 $output .= $isbn->isbn() . " ";
771 $isbn->fix_checksum();
774 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
775 # and add the normalized original ISBN to the output
776 if ($isbn && $isbn->is_valid()) {
777 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
778 $output .= $isbn->isbn . " ";
780 # If we successfully converted the ISBN to its counterpart, add the
781 # converted ISBN to the output as well
782 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
785 return $output if $output;
787 # If there were no valid ISBNs, just return the raw input
789 $func$ LANGUAGE PLPERLU;
791 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
792 The translate_isbn1013 function takes an input ISBN and returns the
793 following in a single space-delimited string if the input ISBN is valid:
794 - The normalized input ISBN (hyphens stripped)
795 - The normalized input ISBN with a fixed checksum if the checksum was bad
796 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
799 -- And ... a table in which to register them
801 CREATE TABLE config.index_normalizer (
802 id SERIAL PRIMARY KEY,
803 name TEXT UNIQUE NOT NULL,
806 param_count INT NOT NULL DEFAULT 0
809 CREATE TABLE config.metabib_field_index_norm_map (
810 id SERIAL PRIMARY KEY,
811 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
812 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
814 pos INT NOT NULL DEFAULT 0
817 CREATE TABLE config.record_attr_definition (
818 name TEXT PRIMARY KEY,
819 label TEXT NOT NULL, -- I18N
821 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
822 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
823 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
824 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
826 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
827 tag TEXT, -- LIKE format
828 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
830 -- This is used for both tag/sf and xpath entries
833 -- For xpath-extracted attrs
835 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
840 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
842 -- For phys-char fields
843 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
845 -- Source of vocabulary terms for this record attribute;
846 -- typically will be a URI referring to a SKOS vocabulary
850 CREATE TABLE config.record_attr_index_norm_map (
851 id SERIAL PRIMARY KEY,
852 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
853 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
855 pos INT NOT NULL DEFAULT 0
858 CREATE TABLE config.coded_value_map (
859 id SERIAL PRIMARY KEY,
860 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
864 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
866 is_simple BOOL NOT NULL DEFAULT FALSE,
867 concept_uri TEXT -- URI expressing the SKOS concept that the
868 -- coded value represents
871 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
873 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
874 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
875 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
876 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
877 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
878 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
879 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
881 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$
883 current_row config.coded_value_map%ROWTYPE;
885 -- Look for a current value
886 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
888 IF FOUND AND NOT add_only THEN
889 -- Update anything we were handed
890 current_row.value := COALESCE(current_row.value, in_value);
891 current_row.description := COALESCE(current_row.description, in_description);
892 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
893 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
894 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
895 UPDATE config.coded_value_map
897 value = current_row.value,
898 description = current_row.description,
899 opac_visible = current_row.opac_visible,
900 search_label = current_row.search_label,
901 is_simple = current_row.is_simple
902 WHERE id = current_row.id;
904 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
905 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
908 $f$ LANGUAGE PLPGSQL;
910 CREATE TABLE config.composite_attr_entry_definition(
911 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
912 definition TEXT NOT NULL -- JSON
915 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
916 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
917 SELECT DISTINCT l.version
918 FROM config.upgrade_log l
919 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
920 WHERE d.db_patch = $1
923 -- List applied db patches that are superseded by (and block the application of) my_db_patch
924 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
925 SELECT DISTINCT l.version
926 FROM config.upgrade_log l
927 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
928 WHERE d.db_patch = $1
931 -- List applied db patches that deprecates (and block the application of) my_db_patch
932 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
934 FROM config.db_patch_dependencies
935 WHERE ARRAY[$1]::TEXT[] && deprecates
938 -- List applied db patches that supersedes (and block the application of) my_db_patch
939 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
941 FROM config.db_patch_dependencies
942 WHERE ARRAY[$1]::TEXT[] && supersedes
945 -- Make sure that no deprecated or superseded db patches are currently applied
946 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
948 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
950 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
952 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
954 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
957 -- Raise an exception if there are, in fact, dep/sup conflict
958 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
963 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
964 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
965 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
967 Upgrade script % can not be applied:
968 applied deprecated scripts %
969 applied superseded scripts %
973 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
974 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
975 evergreen.upgrade_list_applied_deprecated(my_db_patch),
976 evergreen.upgrade_list_applied_superseded(my_db_patch);
979 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
984 CREATE TABLE config.barcode_completion (
985 id SERIAL PRIMARY KEY,
986 active BOOL NOT NULL DEFAULT true,
987 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
990 length INT NOT NULL DEFAULT 0,
992 padding_end BOOL NOT NULL DEFAULT false,
993 asset BOOL NOT NULL DEFAULT true,
994 actor BOOL NOT NULL DEFAULT true
997 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
999 -- Add support for logging, only keep the most recent five rows for each category.
1002 CREATE TABLE config.org_unit_setting_type_log (
1003 id BIGSERIAL PRIMARY KEY,
1004 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1005 org INT, --REFERENCES actor.org_unit (id),
1006 original_value TEXT,
1008 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1011 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1012 Org Unit setting Logs
1014 This table contains the most recent changes to each setting
1015 in actor.org_unit_setting, allowing for mistakes to be undone.
1016 This is NOT meant to be an auditor, but rather an undo/redo.
1019 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1021 -- Only keeps the most recent five settings changes.
1022 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1023 (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);
1025 IF (TG_OP = 'UPDATE') THEN
1027 ELSIF (TG_OP = 'INSERT') THEN
1032 $oustl_limit$ LANGUAGE plpgsql;
1034 CREATE TRIGGER limit_logs_oust
1035 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1036 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1038 CREATE TABLE config.sms_carrier (
1039 id SERIAL PRIMARY KEY,
1043 active BOOLEAN DEFAULT TRUE
1046 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1048 CREATE TABLE config.usr_activity_type (
1049 id SERIAL PRIMARY KEY,
1053 label TEXT NOT NULL, -- i18n
1054 egroup config.usr_activity_group NOT NULL,
1055 enabled BOOL NOT NULL DEFAULT TRUE,
1056 transient BOOL NOT NULL DEFAULT TRUE,
1057 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1060 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1061 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1063 CREATE TABLE config.filter_dialog_interface (
1064 key TEXT PRIMARY KEY,
1068 CREATE TABLE config.filter_dialog_filter_set (
1069 id SERIAL PRIMARY KEY,
1071 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1072 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1073 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1074 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1075 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1076 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1079 CREATE TABLE config.best_hold_order(
1080 id SERIAL PRIMARY KEY,
1081 name TEXT UNIQUE, -- i18n
1082 pprox INT, -- copy capture <-> pickup lib prox
1083 hprox INT, -- copy circ lib <-> request lib prox
1084 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1085 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1086 priority INT, -- group hold priority
1087 cut INT, -- cut-in-line
1088 depth INT, -- selection depth
1089 htime INT, -- time since last home-lib circ exceeds org-unit setting
1090 rtime INT, -- request time
1091 shtime INT -- time since copy last trip home exceeds org-unit setting
1094 -- At least one of these columns must contain a non-null value
1095 ALTER TABLE config.best_hold_order ADD CHECK ((
1096 pprox IS NOT NULL OR
1097 hprox IS NOT NULL OR
1098 aprox IS NOT NULL OR
1099 priority IS NOT NULL OR
1101 depth IS NOT NULL OR
1102 htime IS NOT NULL OR
1106 CREATE OR REPLACE FUNCTION
1107 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1110 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1116 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1119 $func$ LANGUAGE PLPGSQL STABLE;
1121 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1122 Used by a config.z3950_index_field_map constraint trigger
1123 to verify z3950_attr_type maps.
1126 -- drop these in down here since they reference config.metabib_field
1127 -- and config.record_attr_definition
1128 CREATE TABLE config.z3950_index_field_map (
1129 id SERIAL PRIMARY KEY,
1130 label TEXT NOT NULL, -- i18n
1131 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1132 record_attr TEXT REFERENCES config.record_attr_definition(name),
1133 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1134 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1135 CONSTRAINT metabib_field_or_record_attr CHECK (
1136 metabib_field IS NOT NULL OR
1137 record_attr IS NOT NULL
1139 CONSTRAINT attr_or_attr_type CHECK (
1140 z3950_attr IS NOT NULL OR
1141 z3950_attr_type IS NOT NULL
1145 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1146 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1147 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1149 CREATE TABLE config.marc_format (
1150 id SERIAL PRIMARY KEY,
1154 COMMENT ON TABLE config.marc_format IS $$
1155 List of MARC formats supported by this Evergreen
1156 database. This exists primarily as a hook for future
1157 support of UNIMARC, though whether that will ever
1158 happen remains to be seen.
1161 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1163 CREATE TABLE config.marc_field (
1164 id SERIAL PRIMARY KEY,
1165 marc_format INTEGER NOT NULL
1166 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1167 marc_record_type config.marc_record_type NOT NULL,
1168 tag CHAR(3) NOT NULL,
1171 fixed_field BOOLEAN,
1175 owner INTEGER -- REFERENCES actor.org_unit (id)
1176 -- if the owner is null, the data about the field is
1177 -- assumed to come from the controlling MARC standard
1180 COMMENT ON TABLE config.marc_field IS $$
1181 This table stores a list of MARC fields recognized by the Evergreen
1182 instance. Note that we're not aiming for completely generic ISO2709
1183 support: we're assuming things like three characters for a tag,
1184 one-character subfield labels, two indicators per variable data field,
1185 and the like, all of which are technically specializations of ISO2709.
1187 Of particular significance is the owner column; if it's set to a null
1188 value, the field definition is assumed to come from a national
1189 standards body; if it's set to a non-null value, the field definition
1190 is an OU-level addition to or override of the standard.
1193 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1194 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1196 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1197 ON config.marc_field(marc_format, marc_record_type, tag)
1198 WHERE owner IS NULL;
1199 ALTER TABLE config.marc_field
1200 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1201 CHECK ((owner IS NOT NULL) OR
1204 repeatable IS NOT NULL AND
1205 mandatory IS NOT NULL AND
1210 CREATE TABLE config.marc_subfield (
1211 id SERIAL PRIMARY KEY,
1212 marc_format INTEGER NOT NULL
1213 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1214 marc_record_type config.marc_record_type NOT NULL,
1215 tag CHAR(3) NOT NULL,
1216 code CHAR(1) NOT NULL,
1222 REFERENCES config.record_attr_definition (name)
1223 DEFERRABLE INITIALLY DEFERRED,
1224 owner INTEGER -- REFERENCES actor.org_unit (id)
1225 -- if the owner is null, the data about the subfield is
1226 -- assumed to come from the controlling MARC standard
1229 COMMENT ON TABLE config.marc_subfield IS $$
1230 This table stores the list of subfields recognized by this Evergreen
1231 instance. As with config.marc_field, of particular significance is the
1232 owner column; if it's set to a null value, the subfield definition is
1233 assumed to come from a national standards body; if it's set to a non-null
1234 value, the subfield definition is an OU-level addition to or override
1238 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1239 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1240 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1241 WHERE owner IS NULL;
1242 ALTER TABLE config.marc_subfield
1243 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1244 CHECK ((owner IS NOT NULL) OR
1247 repeatable IS NOT NULL AND
1248 mandatory IS NOT NULL AND
1253 CREATE TABLE config.copy_tag_type (
1254 code TEXT NOT NULL PRIMARY KEY,
1255 label TEXT NOT NULL,
1256 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1259 CREATE INDEX config_copy_tag_type_owner_idx
1260 ON config.copy_tag_type (owner);