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.disable_metabib_field_entry');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_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');
55 CREATE TABLE config.global_flag (
57 ) INHERITS (config.internal_flag);
58 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
60 CREATE TABLE config.upgrade_log (
61 version TEXT PRIMARY KEY,
62 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
66 CREATE TABLE config.db_patch_dependencies (
67 db_patch TEXT PRIMARY KEY,
72 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
78 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
80 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
86 CREATE TRIGGER no_overlapping_sups
87 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
88 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
90 CREATE TRIGGER no_overlapping_deps
91 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
92 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
94 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0941', :eg_version); -- yboston/dyrcona
96 CREATE TABLE config.bib_source (
97 id SERIAL PRIMARY KEY,
98 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
99 source TEXT NOT NULL UNIQUE,
100 transcendant BOOL NOT NULL DEFAULT FALSE,
101 can_have_copies BOOL NOT NULL DEFAULT TRUE
103 COMMENT ON TABLE config.bib_source IS $$
104 This is table is used to set up the relative "quality" of each
105 MARC source, such as OCLC. Also identifies "transcendant" sources,
106 i.e., sources of bib records that should display in the OPAC
107 even if no copies or located URIs are attached. Also indicates if
108 the source is allowed to have actual copies on its bibs. Volumes
109 for targeted URIs are unaffected by this setting.
112 CREATE TABLE config.standing (
113 id SERIAL PRIMARY KEY,
114 value TEXT NOT NULL UNIQUE
116 COMMENT ON TABLE config.standing IS $$
119 This table contains the values that can be applied to a patron
120 by a staff member. These values should not be changed, other
121 than for translation, as the ID column is currently a "magic
122 number" in the source. :(
125 CREATE TABLE config.standing_penalty (
126 id SERIAL PRIMARY KEY,
127 name TEXT NOT NULL UNIQUE,
130 staff_alert BOOL NOT NULL DEFAULT FALSE,
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 CREATE TABLE config.metabib_class (
173 name TEXT PRIMARY KEY,
174 label TEXT NOT NULL UNIQUE,
175 buoyant BOOL DEFAULT FALSE NOT NULL,
176 restrict BOOL DEFAULT FALSE NOT NULL,
177 combined BOOL DEFAULT FALSE NOT NULL,
178 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
179 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
180 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
181 d_weight NUMERIC DEFAULT 0.1 NOT NULL
184 CREATE TABLE config.metabib_field (
185 id SERIAL PRIMARY KEY,
186 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
190 weight INT NOT NULL DEFAULT 1,
191 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
192 search_field BOOL NOT NULL DEFAULT TRUE,
193 facet_field BOOL NOT NULL DEFAULT FALSE,
194 browse_field BOOL NOT NULL DEFAULT TRUE,
196 browse_sort_xpath TEXT,
198 authority_xpath TEXT,
200 restrict BOOL DEFAULT FALSE NOT NULL
202 COMMENT ON TABLE config.metabib_field IS $$
203 XPath used for record indexing ingest
205 This table contains the XPath used to chop up MODS into its
206 indexable parts. Each XPath entry is named and assigned to
207 a "class" of either title, subject, author, keyword, series
211 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
213 CREATE TABLE config.ts_config_list (
217 COMMENT ON TABLE config.ts_config_list IS $$
220 A list of full text configs with names and descriptions.
223 CREATE TABLE config.metabib_class_ts_map (
224 id SERIAL PRIMARY KEY,
225 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
226 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
227 active BOOL NOT NULL DEFAULT TRUE,
228 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
229 index_lang TEXT NULL,
230 search_lang TEXT NULL,
231 always BOOL NOT NULL DEFAULT true
233 COMMENT ON TABLE config.metabib_class_ts_map IS $$
234 Text Search Configs for metabib class indexing
236 This table contains text search config definitions for
237 storing index_vector values.
240 CREATE TABLE config.metabib_field_ts_map (
241 id SERIAL PRIMARY KEY,
242 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
243 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
244 active BOOL NOT NULL DEFAULT TRUE,
245 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
246 index_lang TEXT NULL,
247 search_lang TEXT NULL
249 COMMENT ON TABLE config.metabib_field_ts_map IS $$
250 Text Search Configs for metabib field indexing
252 This table contains text search config definitions for
253 storing index_vector values.
256 CREATE TABLE config.metabib_search_alias (
257 alias TEXT PRIMARY KEY,
258 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
259 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
262 CREATE TABLE config.non_cataloged_type (
263 id SERIAL PRIMARY KEY,
264 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
266 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
267 in_house BOOL NOT NULL DEFAULT FALSE,
268 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
270 COMMENT ON TABLE config.non_cataloged_type IS $$
271 Types of valid non-cataloged items.
274 CREATE TABLE config.identification_type (
275 id SERIAL PRIMARY KEY,
276 name TEXT NOT NULL UNIQUE
278 COMMENT ON TABLE config.identification_type IS $$
279 Types of valid patron identification.
281 Each patron must display at least one valid form of identification
282 in order to get a library card. This table lists those forms.
285 CREATE TABLE config.rule_circ_duration (
286 id SERIAL PRIMARY KEY,
287 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
288 extended INTERVAL NOT NULL,
289 normal INTERVAL NOT NULL,
290 shrt INTERVAL NOT NULL,
291 max_renewals INT NOT NULL
293 COMMENT ON TABLE config.rule_circ_duration IS $$
294 Circulation Duration rules
296 Each circulation is given a duration based on one of these rules.
299 CREATE TABLE config.hard_due_date (
300 id SERIAL PRIMARY KEY,
301 name TEXT NOT NULL UNIQUE,
302 ceiling_date TIMESTAMPTZ NOT NULL,
303 forceto BOOL NOT NULL,
304 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
307 CREATE TABLE config.hard_due_date_values (
308 id SERIAL PRIMARY KEY,
309 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
310 DEFERRABLE INITIALLY DEFERRED,
311 ceiling_date TIMESTAMPTZ NOT NULL,
312 active_date TIMESTAMPTZ NOT NULL
315 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
317 temp_value config.hard_due_date_values%ROWTYPE;
321 SELECT DISTINCT ON (hard_due_date) *
322 FROM config.hard_due_date_values
323 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
324 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
326 UPDATE config.hard_due_date
327 SET ceiling_date = temp_value.ceiling_date
328 WHERE id = temp_value.hard_due_date
329 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
332 updated := updated + 1;
338 $func$ LANGUAGE plpgsql;
340 CREATE TABLE config.rule_max_fine (
341 id SERIAL PRIMARY KEY,
342 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
343 amount NUMERIC(6,2) NOT NULL,
344 is_percent BOOL NOT NULL DEFAULT FALSE
346 COMMENT ON TABLE config.rule_max_fine IS $$
347 Circulation Max Fine rules
349 Each circulation is given a maximum fine based on one of
353 CREATE TABLE config.rule_recurring_fine (
354 id SERIAL PRIMARY KEY,
355 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
356 high NUMERIC(6,2) NOT NULL,
357 normal NUMERIC(6,2) NOT NULL,
358 low NUMERIC(6,2) NOT NULL,
359 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
360 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
362 COMMENT ON TABLE config.rule_recurring_fine IS $$
363 Circulation Recurring Fine rules
365 Each circulation is given a recurring fine amount based on one of
366 these rules. Note that it is recommended to run the fine generator
367 (from cron) at least as frequently as the lowest recurrence interval
368 used by your circulation rules so that accrued fines will be up
373 CREATE TABLE config.rule_age_hold_protect (
374 id SERIAL PRIMARY KEY,
375 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
376 age INTERVAL NOT NULL,
379 COMMENT ON TABLE config.rule_age_hold_protect IS $$
380 Hold Item Age Protection rules
382 A hold request can only capture new(ish) items when they are
383 within a particular proximity of the pickup_lib of the request.
384 The proximity ('prox' column) is calculated by counting
385 the number of tree edges between the pickup_lib and either the
386 owning_lib or circ_lib of the copy that could fulfill the hold,
387 as determined by the distance_is_from_owner value of the hold matrix
388 rule controlling the hold request.
391 CREATE TABLE config.copy_status (
392 id SERIAL PRIMARY KEY,
393 name TEXT NOT NULL UNIQUE,
394 holdable BOOL NOT NULL DEFAULT FALSE,
395 opac_visible BOOL NOT NULL DEFAULT FALSE,
396 copy_active BOOL NOT NULL DEFAULT FALSE,
397 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
399 COMMENT ON TABLE config.copy_status IS $$
402 The available copy statuses, and whether a copy in that
403 status is available for hold request capture. 0 (zero) is
404 the only special number in this set, meaning that the item
405 is available for immediate checkout, and is counted as available
408 Statuses with an ID below 100 are not removable, and have special
409 meaning in the code. Do not change them except to translate the
412 You may add and remove statuses above 100, and these can be used
413 to remove items from normal circulation without affecting the rest
414 of the copy's values or its location.
417 CREATE TABLE config.net_access_level (
418 id SERIAL PRIMARY KEY,
419 name TEXT NOT NULL UNIQUE
421 COMMENT ON TABLE config.net_access_level IS $$
422 Patron Network Access level
424 This will be used to inform the in-library firewall of how much
425 internet access the using patron should be allowed.
429 CREATE TABLE config.remote_account (
430 id SERIAL PRIMARY KEY,
432 host TEXT NOT NULL, -- name or IP, :port optional
433 username TEXT, -- optional, since we could default to $USER
434 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
435 account TEXT, -- aka profile or FTP "account" command
436 path TEXT, -- aka directory
437 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
438 last_activity TIMESTAMP WITH TIME ZONE
441 CREATE TABLE config.marc21_rec_type_map (
442 code TEXT PRIMARY KEY,
443 type_val TEXT NOT NULL,
444 blvl_val TEXT NOT NULL
447 CREATE TABLE config.marc21_ff_pos_map (
448 id SERIAL PRIMARY KEY,
449 fixed_field TEXT NOT NULL,
451 rec_type TEXT NOT NULL,
452 start_pos INT NOT NULL,
454 default_val TEXT NOT NULL DEFAULT ' '
457 CREATE TABLE config.marc21_physical_characteristic_type_map (
458 ptype_key TEXT PRIMARY KEY,
459 label TEXT NOT NULL -- I18N
462 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
463 id SERIAL PRIMARY KEY,
464 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
465 subfield TEXT NOT NULL,
466 start_pos INT NOT NULL,
468 label TEXT NOT NULL -- I18N
471 CREATE TABLE config.marc21_physical_characteristic_value_map (
472 id SERIAL PRIMARY KEY,
474 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
475 label TEXT NOT NULL -- I18N
479 CREATE TABLE config.z3950_source (
480 name TEXT PRIMARY KEY,
481 label TEXT NOT NULL UNIQUE,
485 record_format TEXT NOT NULL DEFAULT 'FI',
486 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
487 auth BOOL NOT NULL DEFAULT TRUE,
488 use_perm INT -- REFERENCES permission.perm_list (id)
491 COMMENT ON TABLE config.z3950_source IS $$
494 Each row in this table represents a database searchable via Z39.50.
497 COMMENT ON COLUMN config.z3950_source.record_format IS $$
501 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
502 Z39.50 preferred record syntax..
505 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
506 If set, this permission is required for the source to be listed in the staff
507 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
510 CREATE TABLE config.z3950_attr (
511 id SERIAL PRIMARY KEY,
512 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
517 truncation INT NOT NULL DEFAULT 0,
518 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
521 CREATE TABLE config.z3950_source_credentials (
522 id SERIAL PRIMARY KEY,
523 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
524 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
527 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
530 CREATE TABLE config.i18n_locale (
531 code TEXT PRIMARY KEY,
532 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
533 name TEXT UNIQUE NOT NULL,
537 CREATE TABLE config.i18n_core (
538 id BIGSERIAL PRIMARY KEY,
539 fq_field TEXT NOT NULL,
540 identity_value TEXT NOT NULL,
541 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
545 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
547 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
551 UPDATE config.i18n_core
552 SET identity_value = $$ || quote_literal(new_ident) || $$
553 WHERE fq_field LIKE '$$ || hint || $$.%'
554 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
559 $_$ LANGUAGE PLPGSQL;
561 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
563 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
566 $_$ LANGUAGE PLPGSQL;
568 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
570 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
573 $_$ LANGUAGE PLPGSQL;
575 CREATE TABLE config.billing_type (
576 id SERIAL PRIMARY KEY,
578 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
579 default_price NUMERIC(6,2),
580 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
583 CREATE TABLE config.settings_group (
584 name TEXT PRIMARY KEY,
585 label TEXT UNIQUE NOT NULL -- I18N
588 CREATE TABLE config.org_unit_setting_type (
589 name TEXT PRIMARY KEY,
590 label TEXT UNIQUE NOT NULL,
591 grp TEXT REFERENCES config.settings_group (name),
593 datatype TEXT NOT NULL DEFAULT 'string',
598 -- define valid datatypes
600 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
601 ( 'bool', 'integer', 'float', 'currency', 'interval',
602 'date', 'string', 'object', 'array', 'link' ) ),
604 -- fm_class is meaningful only for 'link' datatype
606 CONSTRAINT coust_no_empty_link CHECK
607 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
608 ( datatype <> 'link' AND fm_class IS NULL ) )
611 CREATE TABLE config.usr_setting_type (
613 name TEXT PRIMARY KEY,
614 opac_visible BOOL NOT NULL DEFAULT FALSE,
615 label TEXT UNIQUE NOT NULL,
617 grp TEXT REFERENCES config.settings_group (name),
618 datatype TEXT NOT NULL DEFAULT 'string',
623 -- define valid datatypes
625 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
626 ( 'bool', 'integer', 'float', 'currency', 'interval',
627 'date', 'string', 'object', 'array', 'link' ) ),
630 -- fm_class is meaningful only for 'link' datatype
632 CONSTRAINT coust_no_empty_link CHECK
633 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
634 ( datatype <> 'link' AND fm_class IS NULL ) )
638 -- Some handy functions, based on existing ones, to provide optional ingest normalization
640 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
641 SELECT SUBSTRING($1,$2);
642 $func$ LANGUAGE SQL STRICT IMMUTABLE;
644 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
645 SELECT SUBSTRING($1,1,$2);
646 $func$ LANGUAGE SQL STRICT IMMUTABLE;
648 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
649 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
650 $func$ LANGUAGE SQL STRICT IMMUTABLE;
652 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
653 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
654 $func$ LANGUAGE SQL STRICT IMMUTABLE;
656 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
657 SELECT approximate_date( $1, '0');
658 $func$ LANGUAGE SQL STRICT IMMUTABLE;
660 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
661 SELECT approximate_date( $1, '9');
662 $func$ LANGUAGE SQL STRICT IMMUTABLE;
664 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
665 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
666 $func$ LANGUAGE SQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
669 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
670 $func$ LANGUAGE SQL STRICT IMMUTABLE;
672 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
677 # Find the first ISBN, force it to ISBN13 and return it
681 foreach my $word (split(/\s/, $input)) {
682 my $isbn = Business::ISBN->new($word);
684 # First check the checksum; if it is not valid, fix it and add the original
685 # bad-checksum ISBN to the output
686 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
687 $isbn->fix_checksum();
690 # If we now have a valid ISBN, force it to ISBN13 and return it
691 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
694 $func$ LANGUAGE PLPERLU;
696 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
697 Inspired by translate_isbn1013
699 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
700 version without hypens and with a repaired checksum if the checksum was bad
704 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
709 # For each ISBN found in a single string containing a set of ISBNs:
710 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
711 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
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 $output .= $isbn->isbn() . " ";
723 $isbn->fix_checksum();
726 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
727 # and add the normalized original ISBN to the output
728 if ($isbn && $isbn->is_valid()) {
729 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
730 $output .= $isbn->isbn . " ";
732 # If we successfully converted the ISBN to its counterpart, add the
733 # converted ISBN to the output as well
734 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
737 return $output if $output;
739 # If there were no valid ISBNs, just return the raw input
741 $func$ LANGUAGE PLPERLU;
743 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
744 The translate_isbn1013 function takes an input ISBN and returns the
745 following in a single space-delimited string if the input ISBN is valid:
746 - The normalized input ISBN (hyphens stripped)
747 - The normalized input ISBN with a fixed checksum if the checksum was bad
748 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
751 -- And ... a table in which to register them
753 CREATE TABLE config.index_normalizer (
754 id SERIAL PRIMARY KEY,
755 name TEXT UNIQUE NOT NULL,
758 param_count INT NOT NULL DEFAULT 0
761 CREATE TABLE config.metabib_field_index_norm_map (
762 id SERIAL PRIMARY KEY,
763 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
764 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
766 pos INT NOT NULL DEFAULT 0
769 CREATE TABLE config.record_attr_definition (
770 name TEXT PRIMARY KEY,
771 label TEXT NOT NULL, -- I18N
773 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
774 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
775 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
776 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
778 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
779 tag TEXT, -- LIKE format
780 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
782 -- This is used for both tag/sf and xpath entries
785 -- For xpath-extracted attrs
787 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
792 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
794 -- For phys-char fields
795 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
797 -- Source of vocabulary terms for this record attribute;
798 -- typically will be a URI referring to a SKOS vocabulary
802 CREATE TABLE config.record_attr_index_norm_map (
803 id SERIAL PRIMARY KEY,
804 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
805 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
807 pos INT NOT NULL DEFAULT 0
810 CREATE TABLE config.coded_value_map (
811 id SERIAL PRIMARY KEY,
812 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
816 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
818 is_simple BOOL NOT NULL DEFAULT FALSE,
819 concept_uri TEXT -- URI expressing the SKOS concept that the
820 -- coded value represents
823 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
824 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
825 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
826 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
827 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
828 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
829 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
831 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$
833 current_row config.coded_value_map%ROWTYPE;
835 -- Look for a current value
836 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
838 IF FOUND AND NOT add_only THEN
839 -- Update anything we were handed
840 current_row.value := COALESCE(current_row.value, in_value);
841 current_row.description := COALESCE(current_row.description, in_description);
842 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
843 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
844 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
845 UPDATE config.coded_value_map
847 value = current_row.value,
848 description = current_row.description,
849 opac_visible = current_row.opac_visible,
850 search_label = current_row.search_label,
851 is_simple = current_row.is_simple
852 WHERE id = current_row.id;
854 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
855 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
858 $f$ LANGUAGE PLPGSQL;
860 CREATE TABLE config.composite_attr_entry_definition(
861 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
862 definition TEXT NOT NULL -- JSON
865 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
866 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
867 SELECT DISTINCT l.version
868 FROM config.upgrade_log l
869 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
870 WHERE d.db_patch = $1
873 -- List applied db patches that are superseded by (and block the application of) my_db_patch
874 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
875 SELECT DISTINCT l.version
876 FROM config.upgrade_log l
877 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
878 WHERE d.db_patch = $1
881 -- List applied db patches that deprecates (and block the application of) my_db_patch
882 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
884 FROM config.db_patch_dependencies
885 WHERE ARRAY[$1]::TEXT[] && deprecates
888 -- List applied db patches that supersedes (and block the application of) my_db_patch
889 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
891 FROM config.db_patch_dependencies
892 WHERE ARRAY[$1]::TEXT[] && supersedes
895 -- Make sure that no deprecated or superseded db patches are currently applied
896 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
898 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
900 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
902 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
904 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
907 -- Raise an exception if there are, in fact, dep/sup conflict
908 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
913 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
914 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
915 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
917 Upgrade script % can not be applied:
918 applied deprecated scripts %
919 applied superseded scripts %
923 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
924 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
925 evergreen.upgrade_list_applied_deprecated(my_db_patch),
926 evergreen.upgrade_list_applied_superseded(my_db_patch);
929 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
934 CREATE TABLE config.barcode_completion (
935 id SERIAL PRIMARY KEY,
936 active BOOL NOT NULL DEFAULT true,
937 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
940 length INT NOT NULL DEFAULT 0,
942 padding_end BOOL NOT NULL DEFAULT false,
943 asset BOOL NOT NULL DEFAULT true,
944 actor BOOL NOT NULL DEFAULT true
947 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
949 -- Add support for logging, only keep the most recent five rows for each category.
952 CREATE TABLE config.org_unit_setting_type_log (
953 id BIGSERIAL PRIMARY KEY,
954 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
955 org INT, --REFERENCES actor.org_unit (id),
958 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
961 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
962 Org Unit setting Logs
964 This table contains the most recent changes to each setting
965 in actor.org_unit_setting, allowing for mistakes to be undone.
966 This is NOT meant to be an auditor, but rather an undo/redo.
969 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
971 -- Only keeps the most recent five settings changes.
972 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
973 (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);
975 IF (TG_OP = 'UPDATE') THEN
977 ELSIF (TG_OP = 'INSERT') THEN
982 $oustl_limit$ LANGUAGE plpgsql;
984 CREATE TRIGGER limit_logs_oust
985 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
986 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
988 CREATE TABLE config.sms_carrier (
989 id SERIAL PRIMARY KEY,
993 active BOOLEAN DEFAULT TRUE
996 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
998 CREATE TABLE config.usr_activity_type (
999 id SERIAL PRIMARY KEY,
1003 label TEXT NOT NULL, -- i18n
1004 egroup config.usr_activity_group NOT NULL,
1005 enabled BOOL NOT NULL DEFAULT TRUE,
1006 transient BOOL NOT NULL DEFAULT FALSE,
1007 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1010 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1011 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1013 CREATE TABLE config.filter_dialog_interface (
1014 key TEXT PRIMARY KEY,
1018 CREATE TABLE config.filter_dialog_filter_set (
1019 id SERIAL PRIMARY KEY,
1021 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1022 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1023 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1024 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1025 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1026 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1029 CREATE TABLE config.best_hold_order(
1030 id SERIAL PRIMARY KEY,
1031 name TEXT UNIQUE, -- i18n
1032 pprox INT, -- copy capture <-> pickup lib prox
1033 hprox INT, -- copy circ lib <-> request lib prox
1034 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1035 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1036 priority INT, -- group hold priority
1037 cut INT, -- cut-in-line
1038 depth INT, -- selection depth
1039 htime INT, -- time since last home-lib circ exceeds org-unit setting
1040 rtime INT, -- request time
1041 shtime INT -- time since copy last trip home exceeds org-unit setting
1044 -- At least one of these columns must contain a non-null value
1045 ALTER TABLE config.best_hold_order ADD CHECK ((
1046 pprox IS NOT NULL OR
1047 hprox IS NOT NULL OR
1048 aprox IS NOT NULL OR
1049 priority IS NOT NULL OR
1051 depth IS NOT NULL OR
1052 htime IS NOT NULL OR
1056 CREATE OR REPLACE FUNCTION
1057 evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$
1058 SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1);
1059 $func$ LANGUAGE SQL STRICT STABLE;
1061 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$
1062 Results in TRUE if there exists at least one config.z3950_attr
1063 with the provided name. Used by config.z3950_index_field_map
1064 to verify z3950_attr_type maps.
1067 -- drop these in down here since they reference config.metabib_field
1068 -- and config.record_attr_definition
1069 CREATE TABLE config.z3950_index_field_map (
1070 id SERIAL PRIMARY KEY,
1071 label TEXT NOT NULL, -- i18n
1072 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1073 record_attr TEXT REFERENCES config.record_attr_definition(name),
1074 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1075 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1076 CONSTRAINT metabib_field_or_record_attr CHECK (
1077 metabib_field IS NOT NULL OR
1078 record_attr IS NOT NULL
1080 CONSTRAINT attr_or_attr_type CHECK (
1081 z3950_attr IS NOT NULL OR
1082 z3950_attr_type IS NOT NULL
1084 -- ensure the selected z3950_attr_type refers to a valid attr name
1085 CONSTRAINT valid_z3950_attr_type CHECK (
1086 z3950_attr_type IS NULL OR
1087 evergreen.z3950_attr_name_is_valid(z3950_attr_type)
1091 CREATE TABLE config.marc_format (
1092 id SERIAL PRIMARY KEY,
1096 COMMENT ON TABLE config.marc_format IS $$
1097 List of MARC formats supported by this Evergreen
1098 database. This exists primarily as a hook for future
1099 support of UNIMARC, though whether that will ever
1100 happen remains to be seen.
1103 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1105 CREATE TABLE config.marc_field (
1106 id SERIAL PRIMARY KEY,
1107 marc_format INTEGER NOT NULL
1108 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1109 marc_record_type config.marc_record_type NOT NULL,
1110 tag CHAR(3) NOT NULL,
1113 fixed_field BOOLEAN,
1117 owner INTEGER -- REFERENCES actor.org_unit (id)
1118 -- if the owner is null, the data about the field is
1119 -- assumed to come from the controlling MARC standard
1122 COMMENT ON TABLE config.marc_field IS $$
1123 This table stores a list of MARC fields recognized by the Evergreen
1124 instance. Note that we're not aiming for completely generic ISO2709
1125 support: we're assuming things like three characters for a tag,
1126 one-character subfield labels, two indicators per variable data field,
1127 and the like, all of which are technically specializations of ISO2709.
1129 Of particular significance is the owner column; if it's set to a null
1130 value, the field definition is assumed to come from a national
1131 standards body; if it's set to a non-null value, the field definition
1132 is an OU-level addition to or override of the standard.
1135 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1136 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1138 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1139 ON config.marc_field(marc_format, marc_record_type, tag)
1140 WHERE owner IS NULL;
1141 ALTER TABLE config.marc_field
1142 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1143 CHECK ((owner IS NOT NULL) OR
1146 repeatable IS NOT NULL AND
1147 mandatory IS NOT NULL AND
1152 CREATE TABLE config.marc_subfield (
1153 id SERIAL PRIMARY KEY,
1154 marc_format INTEGER NOT NULL
1155 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1156 marc_record_type config.marc_record_type NOT NULL,
1157 tag CHAR(3) NOT NULL,
1158 code CHAR(1) NOT NULL,
1164 REFERENCES config.record_attr_definition (name)
1165 DEFERRABLE INITIALLY DEFERRED,
1166 owner INTEGER -- REFERENCES actor.org_unit (id)
1167 -- if the owner is null, the data about the subfield is
1168 -- assumed to come from the controlling MARC standard
1171 COMMENT ON TABLE config.marc_subfield IS $$
1172 This table stores the list of subfields recognized by this Evergreen
1173 instance. As with config.marc_field, of particular significance is the
1174 owner column; if it's set to a null value, the subfield definition is
1175 assumed to come from a national standards body; if it's set to a non-null
1176 value, the subfield definition is an OU-level addition to or override
1180 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1181 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1182 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1183 WHERE owner IS NULL;
1184 ALTER TABLE config.marc_subfield
1185 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1186 CHECK ((owner IS NOT NULL) OR
1189 repeatable IS NOT NULL AND
1190 mandatory IS NOT NULL AND