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 ('1309', :eg_version); -- malexander/willisb/sandbergja
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,
211 browse_nocase BOOL NOT NULL DEFAULT FALSE,
213 browse_sort_xpath TEXT,
216 authority_xpath TEXT,
218 restrict BOOL DEFAULT FALSE NOT NULL,
219 display_field BOOL NOT NULL DEFAULT TRUE
221 COMMENT ON TABLE config.metabib_field IS $$
222 XPath used for record indexing ingest
224 This table contains the XPath used to chop up MODS into its
225 indexable parts. Each XPath entry is named and assigned to
226 a "class" of either title, subject, author, keyword, series
230 CREATE TABLE config.metabib_field_virtual_map (
231 id SERIAL PRIMARY KEY,
232 real INT NOT NULL REFERENCES config.metabib_field (id),
233 virtual INT NOT NULL REFERENCES config.metabib_field (id),
234 weight INT NOT NULL DEFAULT 1
236 COMMENT ON TABLE config.metabib_field_virtual_map IS $$
237 Maps between real (physically extracted) index definitions
238 and virtual (target sync, no required extraction of its own)
241 The virtual side may not extract any data of its own, but
242 will collect data from all of the real fields. This reduces
243 extraction (ingest) overhead by eliminating duplcated extraction,
244 and allows for searching across novel combinations of fields, such
245 as names used as either subjects or authors. By preserving this
246 mapping rather than defining duplicate extractions, information
247 about the originating, "real" index definitions can be used
248 in interesting ways, such as highlighting in search results.
251 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
253 CREATE TABLE config.display_field_map (
254 name TEXT PRIMARY KEY,
255 field INTEGER REFERENCES config.metabib_field (id),
256 multi BOOLEAN DEFAULT FALSE
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,
338 max_auto_renewals INTEGER
340 COMMENT ON TABLE config.rule_circ_duration IS $$
341 Circulation Duration rules
343 Each circulation is given a duration based on one of these rules.
346 CREATE TABLE config.hard_due_date (
347 id SERIAL PRIMARY KEY,
348 name TEXT NOT NULL UNIQUE,
349 ceiling_date TIMESTAMPTZ NOT NULL,
350 forceto BOOL NOT NULL,
351 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
354 CREATE TABLE config.hard_due_date_values (
355 id SERIAL PRIMARY KEY,
356 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
357 DEFERRABLE INITIALLY DEFERRED,
358 ceiling_date TIMESTAMPTZ NOT NULL,
359 active_date TIMESTAMPTZ NOT NULL
362 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
364 temp_value config.hard_due_date_values%ROWTYPE;
368 SELECT DISTINCT ON (hard_due_date) *
369 FROM config.hard_due_date_values
370 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
371 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
373 UPDATE config.hard_due_date
374 SET ceiling_date = temp_value.ceiling_date
375 WHERE id = temp_value.hard_due_date
376 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
377 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
380 updated := updated + 1;
386 $func$ LANGUAGE plpgsql;
388 CREATE TABLE config.rule_max_fine (
389 id SERIAL PRIMARY KEY,
390 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
391 amount NUMERIC(6,2) NOT NULL,
392 is_percent BOOL NOT NULL DEFAULT FALSE
394 COMMENT ON TABLE config.rule_max_fine IS $$
395 Circulation Max Fine rules
397 Each circulation is given a maximum fine based on one of
401 CREATE TABLE config.rule_recurring_fine (
402 id SERIAL PRIMARY KEY,
403 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
404 high NUMERIC(6,2) NOT NULL,
405 normal NUMERIC(6,2) NOT NULL,
406 low NUMERIC(6,2) NOT NULL,
407 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
408 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
410 COMMENT ON TABLE config.rule_recurring_fine IS $$
411 Circulation Recurring Fine rules
413 Each circulation is given a recurring fine amount based on one of
414 these rules. Note that it is recommended to run the fine generator
415 (from cron) at least as frequently as the lowest recurrence interval
416 used by your circulation rules so that accrued fines will be up
421 CREATE TABLE config.rule_age_hold_protect (
422 id SERIAL PRIMARY KEY,
423 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
424 age INTERVAL NOT NULL,
427 COMMENT ON TABLE config.rule_age_hold_protect IS $$
428 Hold Item Age Protection rules
430 A hold request can only capture new(ish) items when they are
431 within a particular proximity of the pickup_lib of the request.
432 The proximity ('prox' column) is calculated by counting
433 the number of tree edges between the pickup_lib and either the
434 owning_lib or circ_lib of the copy that could fulfill the hold,
435 as determined by the distance_is_from_owner value of the hold matrix
436 rule controlling the hold request.
439 CREATE TABLE config.copy_status (
440 id SERIAL PRIMARY KEY,
441 name TEXT NOT NULL UNIQUE,
442 holdable BOOL NOT NULL DEFAULT FALSE,
443 opac_visible BOOL NOT NULL DEFAULT FALSE,
444 copy_active BOOL NOT NULL DEFAULT FALSE,
445 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
446 is_available BOOL NOT NULL DEFAULT FALSE,
447 hopeless_prone BOOL NOT NULL DEFAULT FALSE
449 COMMENT ON TABLE config.copy_status IS $$
452 The available copy statuses, and whether a copy in that
453 status is available for hold request capture. 0 (zero) is
454 the only special number in this set, meaning that the item
455 is available for immediate checkout, and is counted as available
458 Statuses with an ID below 100 are not removable, and have special
459 meaning in the code. Do not change them except to translate the
462 You may add and remove statuses above 100, and these can be used
463 to remove items from normal circulation without affecting the rest
464 of the copy's values or its location.
467 CREATE TABLE config.net_access_level (
468 id SERIAL PRIMARY KEY,
469 name TEXT NOT NULL UNIQUE
471 COMMENT ON TABLE config.net_access_level IS $$
472 Patron Network Access level
474 This will be used to inform the in-library firewall of how much
475 internet access the using patron should be allowed.
479 CREATE TABLE config.remote_account (
480 id SERIAL PRIMARY KEY,
482 host TEXT NOT NULL, -- name or IP, :port optional
483 username TEXT, -- optional, since we could default to $USER
484 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
485 account TEXT, -- aka profile or FTP "account" command
486 path TEXT, -- aka directory
487 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
488 last_activity TIMESTAMP WITH TIME ZONE
491 CREATE TABLE config.marc21_rec_type_map (
492 code TEXT PRIMARY KEY,
493 type_val TEXT NOT NULL,
494 blvl_val TEXT NOT NULL
497 CREATE TABLE config.marc21_ff_pos_map (
498 id SERIAL PRIMARY KEY,
499 fixed_field TEXT NOT NULL,
501 rec_type TEXT NOT NULL,
502 start_pos INT NOT NULL,
504 default_val TEXT NOT NULL DEFAULT ' '
507 CREATE TABLE config.marc21_physical_characteristic_type_map (
508 ptype_key TEXT PRIMARY KEY,
509 label TEXT NOT NULL -- I18N
512 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
513 id SERIAL PRIMARY KEY,
514 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
515 subfield TEXT NOT NULL,
516 start_pos INT NOT NULL,
518 label TEXT NOT NULL -- I18N
521 CREATE TABLE config.marc21_physical_characteristic_value_map (
522 id SERIAL PRIMARY KEY,
524 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
525 label TEXT NOT NULL -- I18N
529 CREATE TABLE config.z3950_source (
530 name TEXT PRIMARY KEY,
531 label TEXT NOT NULL UNIQUE,
535 record_format TEXT NOT NULL DEFAULT 'FI',
536 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
537 auth BOOL NOT NULL DEFAULT TRUE,
538 use_perm INT -- REFERENCES permission.perm_list (id)
541 COMMENT ON TABLE config.z3950_source IS $$
544 Each row in this table represents a database searchable via Z39.50.
547 COMMENT ON COLUMN config.z3950_source.record_format IS $$
551 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
552 Z39.50 preferred record syntax..
555 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
556 If set, this permission is required for the source to be listed in the staff
557 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
560 CREATE TABLE config.z3950_attr (
561 id SERIAL PRIMARY KEY,
562 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
567 truncation INT NOT NULL DEFAULT 0,
568 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
571 CREATE TABLE config.z3950_source_credentials (
572 id SERIAL PRIMARY KEY,
573 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
574 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
577 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
580 CREATE TABLE config.i18n_locale (
581 code TEXT PRIMARY KEY,
582 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
583 name TEXT UNIQUE NOT NULL,
585 rtl BOOL NOT NULL DEFAULT FALSE
588 CREATE TABLE config.i18n_core (
589 id BIGSERIAL PRIMARY KEY,
590 fq_field TEXT NOT NULL,
591 identity_value TEXT NOT NULL,
592 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
596 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
598 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
602 UPDATE config.i18n_core
603 SET identity_value = $$ || quote_literal(new_ident) || $$
604 WHERE fq_field LIKE '$$ || hint || $$.%'
605 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
610 $_$ LANGUAGE PLPGSQL;
612 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
614 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
617 $_$ LANGUAGE PLPGSQL;
619 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
621 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
624 $_$ LANGUAGE PLPGSQL;
626 CREATE TABLE config.billing_type (
627 id SERIAL PRIMARY KEY,
629 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
630 default_price NUMERIC(6,2),
631 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
634 CREATE TABLE config.settings_group (
635 name TEXT PRIMARY KEY,
636 label TEXT UNIQUE NOT NULL -- I18N
639 CREATE TABLE config.org_unit_setting_type (
640 name TEXT PRIMARY KEY,
641 label TEXT UNIQUE NOT NULL,
642 grp TEXT REFERENCES config.settings_group (name),
644 datatype TEXT NOT NULL DEFAULT 'string',
649 -- define valid datatypes
651 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
652 ( 'bool', 'integer', 'float', 'currency', 'interval',
653 'date', 'string', 'object', 'array', 'link' ) ),
655 -- fm_class is meaningful only for 'link' datatype
657 CONSTRAINT coust_no_empty_link CHECK
658 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
659 ( datatype <> 'link' AND fm_class IS NULL ) )
662 CREATE TABLE config.usr_setting_type (
664 name TEXT PRIMARY KEY,
665 opac_visible BOOL NOT NULL DEFAULT FALSE,
666 label TEXT UNIQUE NOT NULL,
668 grp TEXT REFERENCES config.settings_group (name),
669 datatype TEXT NOT NULL DEFAULT 'string',
674 -- define valid datatypes
676 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
677 ( 'bool', 'integer', 'float', 'currency', 'interval',
678 'date', 'string', 'object', 'array', 'link' ) ),
681 -- fm_class is meaningful only for 'link' datatype
683 CONSTRAINT coust_no_empty_link CHECK
684 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
685 ( datatype <> 'link' AND fm_class IS NULL ) )
689 CREATE TABLE config.workstation_setting_type (
690 name TEXT PRIMARY KEY,
691 label TEXT UNIQUE NOT NULL,
692 grp TEXT REFERENCES config.settings_group (name),
694 datatype TEXT NOT NULL DEFAULT 'string',
697 -- define valid datatypes
699 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
700 ( 'bool', 'integer', 'float', 'currency', 'interval',
701 'date', 'string', 'object', 'array', 'link' ) ),
703 -- fm_class is meaningful only for 'link' datatype
705 CONSTRAINT cwst_no_empty_link CHECK
706 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
707 ( datatype <> 'link' AND fm_class IS NULL ) )
710 -- Prevent setting types from being both user and workstation settings.
711 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
712 RETURNS TRIGGER AS $FUNC$
715 IF TG_TABLE_NAME = 'usr_setting_type' THEN
716 PERFORM TRUE FROM config.workstation_setting_type cwst
717 WHERE cwst.name = NEW.name;
723 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
724 PERFORM TRUE FROM config.usr_setting_type cust
725 WHERE cust.name = NEW.name;
732 '% Cannot be used as both a user setting and a workstation setting.',
735 $FUNC$ LANGUAGE PLPGSQL STABLE;
737 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
738 AFTER INSERT OR UPDATE ON config.usr_setting_type
739 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
741 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
742 AFTER INSERT OR UPDATE ON config.workstation_setting_type
743 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
747 -- Some handy functions, based on existing ones, to provide optional ingest normalization
749 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
750 SELECT SUBSTRING($1,$2);
751 $func$ LANGUAGE SQL STRICT IMMUTABLE;
753 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
754 SELECT SUBSTRING($1,1,$2);
755 $func$ LANGUAGE SQL STRICT IMMUTABLE;
757 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
758 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
759 $func$ LANGUAGE SQL STRICT IMMUTABLE;
761 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
762 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
763 $func$ LANGUAGE SQL STRICT IMMUTABLE;
765 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
766 SELECT approximate_date( $1, '0');
767 $func$ LANGUAGE SQL STRICT IMMUTABLE;
769 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
770 SELECT approximate_date( $1, '9');
771 $func$ LANGUAGE SQL STRICT IMMUTABLE;
773 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
774 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
775 $func$ LANGUAGE SQL STRICT IMMUTABLE;
777 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
778 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
779 $func$ LANGUAGE SQL STRICT IMMUTABLE;
781 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
786 # Find the first ISBN, force it to ISBN13 and return it
790 foreach my $word (split(/\s/, $input)) {
791 my $isbn = Business::ISBN->new($word);
793 # First check the checksum; if it is not valid, fix it and add the original
794 # bad-checksum ISBN to the output
795 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
796 $isbn->fix_checksum();
799 # If we now have a valid ISBN, force it to ISBN13 and return it
800 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
803 $func$ LANGUAGE PLPERLU;
805 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
806 Inspired by translate_isbn1013
808 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
809 version without hypens and with a repaired checksum if the checksum was bad
813 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
818 # For each ISBN found in a single string containing a set of ISBNs:
819 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
820 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
825 foreach my $word (split(/\s/, $input)) {
826 my $isbn = Business::ISBN->new($word);
828 # First check the checksum; if it is not valid, fix it and add the original
829 # bad-checksum ISBN to the output
830 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
831 $output .= $isbn->isbn() . " ";
832 $isbn->fix_checksum();
835 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
836 # and add the normalized original ISBN to the output
837 if ($isbn && $isbn->is_valid()) {
838 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
839 $output .= $isbn->isbn . " ";
841 # If we successfully converted the ISBN to its counterpart, add the
842 # converted ISBN to the output as well
843 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
846 return $output if $output;
848 # If there were no valid ISBNs, just return the raw input
850 $func$ LANGUAGE PLPERLU;
852 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
853 The translate_isbn1013 function takes an input ISBN and returns the
854 following in a single space-delimited string if the input ISBN is valid:
855 - The normalized input ISBN (hyphens stripped)
856 - The normalized input ISBN with a fixed checksum if the checksum was bad
857 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
860 -- And ... a table in which to register them
862 CREATE TABLE config.index_normalizer (
863 id SERIAL PRIMARY KEY,
864 name TEXT UNIQUE NOT NULL,
867 param_count INT NOT NULL DEFAULT 0
870 CREATE TABLE config.metabib_field_index_norm_map (
871 id SERIAL PRIMARY KEY,
872 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
873 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
875 pos INT NOT NULL DEFAULT 0
878 CREATE TABLE config.record_attr_definition (
879 name TEXT PRIMARY KEY,
880 label TEXT NOT NULL, -- I18N
882 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
883 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
884 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
885 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
887 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
888 tag TEXT, -- LIKE format
889 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
891 -- This is used for both tag/sf and xpath entries
894 -- For xpath-extracted attrs
896 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
901 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
903 -- For phys-char fields
904 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
906 -- Source of vocabulary terms for this record attribute;
907 -- typically will be a URI referring to a SKOS vocabulary
911 CREATE TABLE config.record_attr_index_norm_map (
912 id SERIAL PRIMARY KEY,
913 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
914 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
916 pos INT NOT NULL DEFAULT 0
919 CREATE TABLE config.coded_value_map (
920 id SERIAL PRIMARY KEY,
921 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
925 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
927 is_simple BOOL NOT NULL DEFAULT FALSE,
928 concept_uri TEXT -- URI expressing the SKOS concept that the
929 -- coded value represents
932 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
934 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
935 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
936 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
937 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
938 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
939 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
940 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
942 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$
944 current_row config.coded_value_map%ROWTYPE;
946 -- Look for a current value
947 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
949 IF FOUND AND NOT add_only THEN
950 -- Update anything we were handed
951 current_row.value := COALESCE(current_row.value, in_value);
952 current_row.description := COALESCE(current_row.description, in_description);
953 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
954 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
955 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
956 UPDATE config.coded_value_map
958 value = current_row.value,
959 description = current_row.description,
960 opac_visible = current_row.opac_visible,
961 search_label = current_row.search_label,
962 is_simple = current_row.is_simple
963 WHERE id = current_row.id;
965 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
966 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
969 $f$ LANGUAGE PLPGSQL;
971 CREATE TABLE config.composite_attr_entry_definition(
972 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
973 definition TEXT NOT NULL -- JSON
976 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
977 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
978 SELECT DISTINCT l.version
979 FROM config.upgrade_log l
980 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
981 WHERE d.db_patch = $1
984 -- List applied db patches that are superseded by (and block the application of) my_db_patch
985 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
986 SELECT DISTINCT l.version
987 FROM config.upgrade_log l
988 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
989 WHERE d.db_patch = $1
992 -- List applied db patches that deprecates (and block the application of) my_db_patch
993 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
995 FROM config.db_patch_dependencies
996 WHERE ARRAY[$1]::TEXT[] && deprecates
999 -- List applied db patches that supersedes (and block the application of) my_db_patch
1000 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
1002 FROM config.db_patch_dependencies
1003 WHERE ARRAY[$1]::TEXT[] && supersedes
1006 -- Make sure that no deprecated or superseded db patches are currently applied
1007 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
1009 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
1011 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
1013 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
1015 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
1018 -- Raise an exception if there are, in fact, dep/sup conflict
1019 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
1024 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
1025 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
1026 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
1028 Upgrade script % can not be applied:
1029 applied deprecated scripts %
1030 applied superseded scripts %
1034 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
1035 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
1036 evergreen.upgrade_list_applied_deprecated(my_db_patch),
1037 evergreen.upgrade_list_applied_superseded(my_db_patch);
1040 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
1043 $$ LANGUAGE PLPGSQL;
1045 CREATE TABLE config.barcode_completion (
1046 id SERIAL PRIMARY KEY,
1047 active BOOL NOT NULL DEFAULT true,
1048 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
1051 length INT NOT NULL DEFAULT 0,
1053 padding_end BOOL NOT NULL DEFAULT false,
1054 asset BOOL NOT NULL DEFAULT true,
1055 actor BOOL NOT NULL DEFAULT true
1058 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
1060 -- Add support for logging, only keep the most recent five rows for each category.
1063 CREATE TABLE config.org_unit_setting_type_log (
1064 id BIGSERIAL PRIMARY KEY,
1065 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1066 org INT, --REFERENCES actor.org_unit (id),
1067 original_value TEXT,
1069 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1072 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1073 Org Unit setting Logs
1075 This table contains the most recent changes to each setting
1076 in actor.org_unit_setting, allowing for mistakes to be undone.
1077 This is NOT meant to be an auditor, but rather an undo/redo.
1080 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1082 -- Only keeps the most recent five settings changes.
1083 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1084 (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);
1086 IF (TG_OP = 'UPDATE') THEN
1088 ELSIF (TG_OP = 'INSERT') THEN
1093 $oustl_limit$ LANGUAGE plpgsql;
1095 CREATE TRIGGER limit_logs_oust
1096 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1097 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1099 CREATE TABLE config.sms_carrier (
1100 id SERIAL PRIMARY KEY,
1104 active BOOLEAN DEFAULT TRUE
1107 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1109 CREATE TABLE config.usr_activity_type (
1110 id SERIAL PRIMARY KEY,
1114 label TEXT NOT NULL, -- i18n
1115 egroup config.usr_activity_group NOT NULL,
1116 enabled BOOL NOT NULL DEFAULT TRUE,
1117 transient BOOL NOT NULL DEFAULT TRUE,
1118 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1121 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1122 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1124 CREATE TABLE config.filter_dialog_interface (
1125 key TEXT PRIMARY KEY,
1129 CREATE TABLE config.filter_dialog_filter_set (
1130 id SERIAL PRIMARY KEY,
1132 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1133 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1134 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1135 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1136 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1137 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1140 CREATE TABLE config.best_hold_order(
1141 id SERIAL PRIMARY KEY,
1142 name TEXT UNIQUE, -- i18n
1143 pprox INT, -- copy capture <-> pickup lib prox
1144 hprox INT, -- copy circ lib <-> request lib prox
1145 owning_lib_to_home_lib_prox INT, -- copy owning lib <-> user home lib prox
1146 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1147 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1148 priority INT, -- group hold priority
1149 cut INT, -- cut-in-line
1150 depth INT, -- selection depth
1151 htime INT, -- time since last home-lib circ exceeds org-unit setting
1152 rtime INT, -- request time
1153 shtime INT -- time since copy last trip home exceeds org-unit setting
1156 -- At least one of these columns must contain a non-null value
1157 ALTER TABLE config.best_hold_order ADD CHECK ((
1158 pprox IS NOT NULL OR
1159 hprox IS NOT NULL OR
1160 owning_lib_to_home_lib_prox IS NOT NULL OR
1161 aprox IS NOT NULL OR
1162 priority IS NOT NULL OR
1164 depth IS NOT NULL OR
1165 htime IS NOT NULL OR
1169 CREATE OR REPLACE FUNCTION
1170 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1173 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1179 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1182 $func$ LANGUAGE PLPGSQL STABLE;
1184 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1185 Used by a config.z3950_index_field_map constraint trigger
1186 to verify z3950_attr_type maps.
1189 -- drop these in down here since they reference config.metabib_field
1190 -- and config.record_attr_definition
1191 CREATE TABLE config.z3950_index_field_map (
1192 id SERIAL PRIMARY KEY,
1193 label TEXT NOT NULL, -- i18n
1194 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1195 record_attr TEXT REFERENCES config.record_attr_definition(name),
1196 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1197 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1198 CONSTRAINT metabib_field_or_record_attr CHECK (
1199 metabib_field IS NOT NULL OR
1200 record_attr IS NOT NULL
1202 CONSTRAINT attr_or_attr_type CHECK (
1203 z3950_attr IS NOT NULL OR
1204 z3950_attr_type IS NOT NULL
1208 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1209 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1210 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1212 CREATE TABLE config.marc_format (
1213 id SERIAL PRIMARY KEY,
1217 COMMENT ON TABLE config.marc_format IS $$
1218 List of MARC formats supported by this Evergreen
1219 database. This exists primarily as a hook for future
1220 support of UNIMARC, though whether that will ever
1221 happen remains to be seen.
1224 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1226 CREATE TABLE config.marc_field (
1227 id SERIAL PRIMARY KEY,
1228 marc_format INTEGER NOT NULL
1229 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1230 marc_record_type config.marc_record_type NOT NULL,
1231 tag CHAR(3) NOT NULL,
1234 fixed_field BOOLEAN,
1238 owner INTEGER -- REFERENCES actor.org_unit (id)
1239 -- if the owner is null, the data about the field is
1240 -- assumed to come from the controlling MARC standard
1243 COMMENT ON TABLE config.marc_field IS $$
1244 This table stores a list of MARC fields recognized by the Evergreen
1245 instance. Note that we're not aiming for completely generic ISO2709
1246 support: we're assuming things like three characters for a tag,
1247 one-character subfield labels, two indicators per variable data field,
1248 and the like, all of which are technically specializations of ISO2709.
1250 Of particular significance is the owner column; if it's set to a null
1251 value, the field definition is assumed to come from a national
1252 standards body; if it's set to a non-null value, the field definition
1253 is an OU-level addition to or override of the standard.
1256 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1257 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1259 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1260 ON config.marc_field(marc_format, marc_record_type, tag)
1261 WHERE owner IS NULL;
1262 ALTER TABLE config.marc_field
1263 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1264 CHECK ((owner IS NOT NULL) OR
1267 repeatable IS NOT NULL AND
1268 mandatory IS NOT NULL AND
1273 CREATE TABLE config.marc_subfield (
1274 id SERIAL PRIMARY KEY,
1275 marc_format INTEGER NOT NULL
1276 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1277 marc_record_type config.marc_record_type NOT NULL,
1278 tag CHAR(3) NOT NULL,
1279 code CHAR(1) NOT NULL,
1285 REFERENCES config.record_attr_definition (name)
1286 DEFERRABLE INITIALLY DEFERRED,
1287 owner INTEGER -- REFERENCES actor.org_unit (id)
1288 -- if the owner is null, the data about the subfield is
1289 -- assumed to come from the controlling MARC standard
1292 COMMENT ON TABLE config.marc_subfield IS $$
1293 This table stores the list of subfields recognized by this Evergreen
1294 instance. As with config.marc_field, of particular significance is the
1295 owner column; if it's set to a null value, the subfield definition is
1296 assumed to come from a national standards body; if it's set to a non-null
1297 value, the subfield definition is an OU-level addition to or override
1301 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1302 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1303 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1304 WHERE owner IS NULL;
1305 ALTER TABLE config.marc_subfield
1306 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1307 CHECK ((owner IS NOT NULL) OR
1310 repeatable IS NOT NULL AND
1311 mandatory IS NOT NULL AND
1316 CREATE TABLE config.copy_tag_type (
1317 code TEXT NOT NULL PRIMARY KEY,
1318 label TEXT NOT NULL,
1319 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1322 CREATE INDEX config_copy_tag_type_owner_idx
1323 ON config.copy_tag_type (owner);
1325 CREATE TABLE config.hold_type (
1327 hold_type TEXT UNIQUE,
1331 INSERT INTO config.hold_type (hold_type,description) VALUES
1333 ('V','Volume Hold'),
1335 ('M','Metarecord Hold'),
1336 ('R','Recall Hold'),
1338 ('I','Issuance Hold'),
1342 CREATE TABLE config.print_template (
1343 id SERIAL PRIMARY KEY,
1345 label TEXT NOT NULL, -- i18n
1346 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
1347 active BOOLEAN NOT NULL DEFAULT FALSE,
1348 locale TEXT REFERENCES config.i18n_locale(code)
1349 ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1350 content_type TEXT NOT NULL DEFAULT 'text/html',
1351 template TEXT NOT NULL,
1352 CONSTRAINT name_once_per_lib UNIQUE (owner, name),
1353 CONSTRAINT label_once_per_lib UNIQUE (owner, label)
1356 CREATE TABLE config.carousel_type (
1357 id SERIAL PRIMARY KEY,
1359 automatic BOOLEAN NOT NULL DEFAULT TRUE,
1360 filter_by_age BOOLEAN NOT NULL DEFAULT FALSE,
1361 filter_by_copy_owning_lib BOOLEAN NOT NULL DEFAULT FALSE,
1362 filter_by_copy_location BOOLEAN NOT NULL DEFAULT FALSE
1365 INSERT INTO config.carousel_type
1366 (id, name, automatic, filter_by_age, filter_by_copy_owning_lib, filter_by_copy_location)
1368 (1, 'Manual', FALSE, FALSE, FALSE, FALSE),
1369 (2, 'Newly Catalogued Items', TRUE, TRUE, TRUE, TRUE),
1370 (3, 'Recently Returned Items', TRUE, TRUE, TRUE, TRUE),
1371 (4, 'Top Circulated Items', TRUE, TRUE, TRUE, FALSE),
1372 (5, 'Newest Items By Shelving Location', TRUE, TRUE, TRUE, FALSE)
1375 SELECT SETVAL('config.carousel_type_id_seq'::TEXT, 100);
1377 CREATE TABLE config.geolocation_service (
1378 id SERIAL PRIMARY KEY,
1380 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)