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 ('0817', :eg_version); -- berick/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,
199 restrict BOOL DEFAULT FALSE NOT NULL
201 COMMENT ON TABLE config.metabib_field IS $$
202 XPath used for record indexing ingest
204 This table contains the XPath used to chop up MODS into its
205 indexable parts. Each XPath entry is named and assigned to
206 a "class" of either title, subject, author, keyword, series
210 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
212 CREATE TABLE config.ts_config_list (
216 COMMENT ON TABLE config.ts_config_list IS $$
219 A list of full text configs with names and descriptions.
222 CREATE TABLE config.metabib_class_ts_map (
223 id SERIAL PRIMARY KEY,
224 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
225 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
226 active BOOL NOT NULL DEFAULT TRUE,
227 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
228 index_lang TEXT NULL,
229 search_lang TEXT NULL,
230 always BOOL NOT NULL DEFAULT true
232 COMMENT ON TABLE config.metabib_class_ts_map IS $$
233 Text Search Configs for metabib class indexing
235 This table contains text search config definitions for
236 storing index_vector values.
239 CREATE TABLE config.metabib_field_ts_map (
240 id SERIAL PRIMARY KEY,
241 metabib_field INT NOT NULL REFERENCES config.metabib_field (id),
242 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
243 active BOOL NOT NULL DEFAULT TRUE,
244 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
245 index_lang TEXT NULL,
246 search_lang TEXT NULL
248 COMMENT ON TABLE config.metabib_field_ts_map IS $$
249 Text Search Configs for metabib field indexing
251 This table contains text search config definitions for
252 storing index_vector values.
255 CREATE TABLE config.metabib_search_alias (
256 alias TEXT PRIMARY KEY,
257 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
258 field INT REFERENCES config.metabib_field (id)
261 CREATE TABLE config.non_cataloged_type (
262 id SERIAL PRIMARY KEY,
263 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
265 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
266 in_house BOOL NOT NULL DEFAULT FALSE,
267 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
269 COMMENT ON TABLE config.non_cataloged_type IS $$
270 Types of valid non-cataloged items.
273 CREATE TABLE config.identification_type (
274 id SERIAL PRIMARY KEY,
275 name TEXT NOT NULL UNIQUE
277 COMMENT ON TABLE config.identification_type IS $$
278 Types of valid patron identification.
280 Each patron must display at least one valid form of identification
281 in order to get a library card. This table lists those forms.
284 CREATE TABLE config.rule_circ_duration (
285 id SERIAL PRIMARY KEY,
286 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
287 extended INTERVAL NOT NULL,
288 normal INTERVAL NOT NULL,
289 shrt INTERVAL NOT NULL,
290 max_renewals INT NOT NULL
292 COMMENT ON TABLE config.rule_circ_duration IS $$
293 Circulation Duration rules
295 Each circulation is given a duration based on one of these rules.
298 CREATE TABLE config.hard_due_date (
299 id SERIAL PRIMARY KEY,
300 name TEXT NOT NULL UNIQUE,
301 ceiling_date TIMESTAMPTZ NOT NULL,
302 forceto BOOL NOT NULL,
303 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
306 CREATE TABLE config.hard_due_date_values (
307 id SERIAL PRIMARY KEY,
308 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
309 DEFERRABLE INITIALLY DEFERRED,
310 ceiling_date TIMESTAMPTZ NOT NULL,
311 active_date TIMESTAMPTZ NOT NULL
314 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
316 temp_value config.hard_due_date_values%ROWTYPE;
320 SELECT DISTINCT ON (hard_due_date) *
321 FROM config.hard_due_date_values
322 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
323 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
325 UPDATE config.hard_due_date
326 SET ceiling_date = temp_value.ceiling_date
327 WHERE id = temp_value.hard_due_date
328 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
331 updated := updated + 1;
337 $func$ LANGUAGE plpgsql;
339 CREATE TABLE config.rule_max_fine (
340 id SERIAL PRIMARY KEY,
341 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
342 amount NUMERIC(6,2) NOT NULL,
343 is_percent BOOL NOT NULL DEFAULT FALSE
345 COMMENT ON TABLE config.rule_max_fine IS $$
346 Circulation Max Fine rules
348 Each circulation is given a maximum fine based on one of
352 CREATE TABLE config.rule_recurring_fine (
353 id SERIAL PRIMARY KEY,
354 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
355 high NUMERIC(6,2) NOT NULL,
356 normal NUMERIC(6,2) NOT NULL,
357 low NUMERIC(6,2) NOT NULL,
358 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
359 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
361 COMMENT ON TABLE config.rule_recurring_fine IS $$
362 Circulation Recurring Fine rules
364 Each circulation is given a recurring fine amount based on one of
365 these rules. Note that it is recommended to run the fine generator
366 (from cron) at least as frequently as the lowest recurrence interval
367 used by your circulation rules so that accrued fines will be up
372 CREATE TABLE config.rule_age_hold_protect (
373 id SERIAL PRIMARY KEY,
374 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
375 age INTERVAL NOT NULL,
378 COMMENT ON TABLE config.rule_age_hold_protect IS $$
379 Hold Item Age Protection rules
381 A hold request can only capture new(ish) items when they are
382 within a particular proximity of the pickup_lib of the request.
383 The proximity ('prox' column) is calculated by counting
384 the number of tree edges between the pickup_lib and either the
385 owning_lib or circ_lib of the copy that could fulfill the hold,
386 as determined by the distance_is_from_owner value of the hold matrix
387 rule controlling the hold request.
390 CREATE TABLE config.copy_status (
391 id SERIAL PRIMARY KEY,
392 name TEXT NOT NULL UNIQUE,
393 holdable BOOL NOT NULL DEFAULT FALSE,
394 opac_visible BOOL NOT NULL DEFAULT FALSE,
395 copy_active BOOL NOT NULL DEFAULT FALSE,
396 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
398 COMMENT ON TABLE config.copy_status IS $$
401 The available copy statuses, and whether a copy in that
402 status is available for hold request capture. 0 (zero) is
403 the only special number in this set, meaning that the item
404 is available for immediate checkout, and is counted as available
407 Statuses with an ID below 100 are not removable, and have special
408 meaning in the code. Do not change them except to translate the
411 You may add and remove statuses above 100, and these can be used
412 to remove items from normal circulation without affecting the rest
413 of the copy's values or its location.
416 CREATE TABLE config.net_access_level (
417 id SERIAL PRIMARY KEY,
418 name TEXT NOT NULL UNIQUE
420 COMMENT ON TABLE config.net_access_level IS $$
421 Patron Network Access level
423 This will be used to inform the in-library firewall of how much
424 internet access the using patron should be allowed.
428 CREATE TABLE config.remote_account (
429 id SERIAL PRIMARY KEY,
431 host TEXT NOT NULL, -- name or IP, :port optional
432 username TEXT, -- optional, since we could default to $USER
433 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
434 account TEXT, -- aka profile or FTP "account" command
435 path TEXT, -- aka directory
436 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
437 last_activity TIMESTAMP WITH TIME ZONE
440 CREATE TABLE config.marc21_rec_type_map (
441 code TEXT PRIMARY KEY,
442 type_val TEXT NOT NULL,
443 blvl_val TEXT NOT NULL
446 CREATE TABLE config.marc21_ff_pos_map (
447 id SERIAL PRIMARY KEY,
448 fixed_field TEXT NOT NULL,
450 rec_type TEXT NOT NULL,
451 start_pos INT NOT NULL,
453 default_val TEXT NOT NULL DEFAULT ' '
456 CREATE TABLE config.marc21_physical_characteristic_type_map (
457 ptype_key TEXT PRIMARY KEY,
458 label TEXT NOT NULL -- I18N
461 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
462 id SERIAL PRIMARY KEY,
463 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
464 subfield TEXT NOT NULL,
465 start_pos INT NOT NULL,
467 label TEXT NOT NULL -- I18N
470 CREATE TABLE config.marc21_physical_characteristic_value_map (
471 id SERIAL PRIMARY KEY,
473 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
474 label TEXT NOT NULL -- I18N
478 CREATE TABLE config.z3950_source (
479 name TEXT PRIMARY KEY,
480 label TEXT NOT NULL UNIQUE,
484 record_format TEXT NOT NULL DEFAULT 'FI',
485 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
486 auth BOOL NOT NULL DEFAULT TRUE,
487 use_perm INT -- REFERENCES permission.perm_list (id)
490 COMMENT ON TABLE config.z3950_source IS $$
493 Each row in this table represents a database searchable via Z39.50.
496 COMMENT ON COLUMN config.z3950_source.record_format IS $$
500 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
501 Z39.50 preferred record syntax..
504 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
505 If set, this permission is required for the source to be listed in the staff
506 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
509 CREATE TABLE config.z3950_attr (
510 id SERIAL PRIMARY KEY,
511 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
516 truncation INT NOT NULL DEFAULT 0,
517 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
520 CREATE TABLE config.z3950_source_credentials (
521 id SERIAL PRIMARY KEY,
522 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
523 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
526 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
529 CREATE TABLE config.i18n_locale (
530 code TEXT PRIMARY KEY,
531 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
532 name TEXT UNIQUE NOT NULL,
536 CREATE TABLE config.i18n_core (
537 id BIGSERIAL PRIMARY KEY,
538 fq_field TEXT NOT NULL,
539 identity_value TEXT NOT NULL,
540 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
544 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
546 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
550 UPDATE config.i18n_core
551 SET identity_value = $$ || quote_literal(new_ident) || $$
552 WHERE fq_field LIKE '$$ || hint || $$.%'
553 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
558 $_$ LANGUAGE PLPGSQL;
560 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
562 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
565 $_$ LANGUAGE PLPGSQL;
567 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
569 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
572 $_$ LANGUAGE PLPGSQL;
574 CREATE TABLE config.billing_type (
575 id SERIAL PRIMARY KEY,
577 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
578 default_price NUMERIC(6,2),
579 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
582 CREATE TABLE config.settings_group (
583 name TEXT PRIMARY KEY,
584 label TEXT UNIQUE NOT NULL -- I18N
587 CREATE TABLE config.org_unit_setting_type (
588 name TEXT PRIMARY KEY,
589 label TEXT UNIQUE NOT NULL,
590 grp TEXT REFERENCES config.settings_group (name),
592 datatype TEXT NOT NULL DEFAULT 'string',
597 -- define valid datatypes
599 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
600 ( 'bool', 'integer', 'float', 'currency', 'interval',
601 'date', 'string', 'object', 'array', 'link' ) ),
603 -- fm_class is meaningful only for 'link' datatype
605 CONSTRAINT coust_no_empty_link CHECK
606 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
607 ( datatype <> 'link' AND fm_class IS NULL ) )
610 CREATE TABLE config.usr_setting_type (
612 name TEXT PRIMARY KEY,
613 opac_visible BOOL NOT NULL DEFAULT FALSE,
614 label TEXT UNIQUE NOT NULL,
616 grp TEXT REFERENCES config.settings_group (name),
617 datatype TEXT NOT NULL DEFAULT 'string',
622 -- define valid datatypes
624 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
625 ( 'bool', 'integer', 'float', 'currency', 'interval',
626 'date', 'string', 'object', 'array', 'link' ) ),
629 -- fm_class is meaningful only for 'link' datatype
631 CONSTRAINT coust_no_empty_link CHECK
632 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
633 ( datatype <> 'link' AND fm_class IS NULL ) )
637 -- Some handy functions, based on existing ones, to provide optional ingest normalization
639 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
640 SELECT SUBSTRING($1,$2);
641 $func$ LANGUAGE SQL STRICT IMMUTABLE;
643 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
644 SELECT SUBSTRING($1,1,$2);
645 $func$ LANGUAGE SQL STRICT IMMUTABLE;
647 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
648 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
649 $func$ LANGUAGE SQL STRICT IMMUTABLE;
651 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
652 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
653 $func$ LANGUAGE SQL STRICT IMMUTABLE;
655 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
656 SELECT approximate_date( $1, '0');
657 $func$ LANGUAGE SQL STRICT IMMUTABLE;
659 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
660 SELECT approximate_date( $1, '9');
661 $func$ LANGUAGE SQL STRICT IMMUTABLE;
663 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
664 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
665 $func$ LANGUAGE SQL STRICT IMMUTABLE;
667 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
668 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
669 $func$ LANGUAGE SQL STRICT IMMUTABLE;
671 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
676 # Find the first ISBN, force it to ISBN13 and return it
680 foreach my $word (split(/\s/, $input)) {
681 my $isbn = Business::ISBN->new($word);
683 # First check the checksum; if it is not valid, fix it and add the original
684 # bad-checksum ISBN to the output
685 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
686 $isbn->fix_checksum();
689 # If we now have a valid ISBN, force it to ISBN13 and return it
690 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
693 $func$ LANGUAGE PLPERLU;
695 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
696 Inspired by translate_isbn1013
698 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
699 version without hypens and with a repaired checksum if the checksum was bad
703 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
708 # For each ISBN found in a single string containing a set of ISBNs:
709 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
710 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
715 foreach my $word (split(/\s/, $input)) {
716 my $isbn = Business::ISBN->new($word);
718 # First check the checksum; if it is not valid, fix it and add the original
719 # bad-checksum ISBN to the output
720 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
721 $output .= $isbn->isbn() . " ";
722 $isbn->fix_checksum();
725 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
726 # and add the normalized original ISBN to the output
727 if ($isbn && $isbn->is_valid()) {
728 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
729 $output .= $isbn->isbn . " ";
731 # If we successfully converted the ISBN to its counterpart, add the
732 # converted ISBN to the output as well
733 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
736 return $output if $output;
738 # If there were no valid ISBNs, just return the raw input
740 $func$ LANGUAGE PLPERLU;
742 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
743 The translate_isbn1013 function takes an input ISBN and returns the
744 following in a single space-delimited string if the input ISBN is valid:
745 - The normalized input ISBN (hyphens stripped)
746 - The normalized input ISBN with a fixed checksum if the checksum was bad
747 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
750 -- And ... a table in which to register them
752 CREATE TABLE config.index_normalizer (
753 id SERIAL PRIMARY KEY,
754 name TEXT UNIQUE NOT NULL,
757 param_count INT NOT NULL DEFAULT 0
760 CREATE TABLE config.metabib_field_index_norm_map (
761 id SERIAL PRIMARY KEY,
762 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
763 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
765 pos INT NOT NULL DEFAULT 0
768 CREATE TABLE config.record_attr_definition (
769 name TEXT PRIMARY KEY,
770 label TEXT NOT NULL, -- I18N
772 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
773 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
775 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
776 tag TEXT, -- LIKE format
777 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
779 -- This is used for both tag/sf and xpath entries
782 -- For xpath-extracted attrs
784 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
789 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
791 -- For phys-char fields
792 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
795 CREATE TABLE config.record_attr_index_norm_map (
796 id SERIAL PRIMARY KEY,
797 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
798 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
800 pos INT NOT NULL DEFAULT 0
803 CREATE TABLE config.coded_value_map (
804 id SERIAL PRIMARY KEY,
805 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
809 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
811 is_simple BOOL NOT NULL DEFAULT FALSE
814 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
815 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
816 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
817 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
818 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
819 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
820 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
822 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$
824 current_row config.coded_value_map%ROWTYPE;
826 -- Look for a current value
827 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
829 IF FOUND AND NOT add_only THEN
830 -- Update anything we were handed
831 current_row.value := COALESCE(current_row.value, in_value);
832 current_row.description := COALESCE(current_row.description, in_description);
833 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
834 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
835 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
836 UPDATE config.coded_value_map
838 value = current_row.value,
839 description = current_row.description,
840 opac_visible = current_row.opac_visible,
841 search_label = current_row.search_label,
842 is_simple = current_row.is_simple
843 WHERE id = current_row.id;
845 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
846 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
849 $f$ LANGUAGE PLPGSQL;
851 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
852 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
853 SELECT DISTINCT l.version
854 FROM config.upgrade_log l
855 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
856 WHERE d.db_patch = $1
859 -- List applied db patches that are superseded by (and block the application of) my_db_patch
860 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
861 SELECT DISTINCT l.version
862 FROM config.upgrade_log l
863 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
864 WHERE d.db_patch = $1
867 -- List applied db patches that deprecates (and block the application of) my_db_patch
868 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
870 FROM config.db_patch_dependencies
871 WHERE ARRAY[$1]::TEXT[] && deprecates
874 -- List applied db patches that supersedes (and block the application of) my_db_patch
875 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
877 FROM config.db_patch_dependencies
878 WHERE ARRAY[$1]::TEXT[] && supersedes
881 -- Make sure that no deprecated or superseded db patches are currently applied
882 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
884 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
886 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
888 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
890 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
893 -- Raise an exception if there are, in fact, dep/sup conflict
894 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
899 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
900 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
901 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
903 Upgrade script % can not be applied:
904 applied deprecated scripts %
905 applied superseded scripts %
909 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
910 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
911 evergreen.upgrade_list_applied_deprecated(my_db_patch),
912 evergreen.upgrade_list_applied_superseded(my_db_patch);
915 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
920 CREATE TABLE config.barcode_completion (
921 id SERIAL PRIMARY KEY,
922 active BOOL NOT NULL DEFAULT true,
923 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
926 length INT NOT NULL DEFAULT 0,
928 padding_end BOOL NOT NULL DEFAULT false,
929 asset BOOL NOT NULL DEFAULT true,
930 actor BOOL NOT NULL DEFAULT true
933 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
935 -- Add support for logging, only keep the most recent five rows for each category.
938 CREATE TABLE config.org_unit_setting_type_log (
939 id BIGSERIAL PRIMARY KEY,
940 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
941 org INT, --REFERENCES actor.org_unit (id),
944 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
947 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
948 Org Unit setting Logs
950 This table contains the most recent changes to each setting
951 in actor.org_unit_setting, allowing for mistakes to be undone.
952 This is NOT meant to be an auditor, but rather an undo/redo.
955 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
957 -- Only keeps the most recent five settings changes.
958 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
959 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
961 IF (TG_OP = 'UPDATE') THEN
963 ELSIF (TG_OP = 'INSERT') THEN
968 $oustl_limit$ LANGUAGE plpgsql;
970 CREATE TRIGGER limit_logs_oust
971 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
972 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
974 CREATE TABLE config.sms_carrier (
975 id SERIAL PRIMARY KEY,
979 active BOOLEAN DEFAULT TRUE
982 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
984 CREATE TABLE config.usr_activity_type (
985 id SERIAL PRIMARY KEY,
989 label TEXT NOT NULL, -- i18n
990 egroup config.usr_activity_group NOT NULL,
991 enabled BOOL NOT NULL DEFAULT TRUE,
992 transient BOOL NOT NULL DEFAULT FALSE,
993 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
996 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
997 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
999 CREATE TABLE config.filter_dialog_interface (
1000 key TEXT PRIMARY KEY,
1004 CREATE TABLE config.filter_dialog_filter_set (
1005 id SERIAL PRIMARY KEY,
1007 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1008 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1009 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1010 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1011 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1012 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1015 CREATE TABLE config.best_hold_order(
1016 id SERIAL PRIMARY KEY,
1017 name TEXT UNIQUE, -- i18n
1018 pprox INT, -- copy capture <-> pickup lib prox
1019 hprox INT, -- copy circ lib <-> request lib prox
1020 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1021 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1022 priority INT, -- group hold priority
1023 cut INT, -- cut-in-line
1024 depth INT, -- selection depth
1025 htime INT, -- time since last home-lib circ exceeds org-unit setting
1026 rtime INT, -- request time
1027 shtime INT -- time since copy last trip home exceeds org-unit setting
1030 -- At least one of these columns must contain a non-null value
1031 ALTER TABLE config.best_hold_order ADD CHECK ((
1032 pprox IS NOT NULL OR
1033 hprox IS NOT NULL OR
1034 aprox IS NOT NULL OR
1035 priority IS NOT NULL OR
1037 depth IS NOT NULL OR
1038 htime IS NOT NULL OR
1042 CREATE OR REPLACE FUNCTION
1043 evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$
1044 SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1);
1045 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1047 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$
1048 Results in TRUE if there exists at least one config.z3950_attr
1049 with the provided name. Used by config.z3950_index_field_map
1050 to verify z3950_attr_type maps.
1053 -- drop these in down here since they reference config.metabib_field
1054 -- and config.record_attr_definition
1055 CREATE TABLE config.z3950_index_field_map (
1056 id SERIAL PRIMARY KEY,
1057 label TEXT NOT NULL, -- i18n
1058 metabib_field INTEGER REFERENCES config.metabib_field(id),
1059 record_attr TEXT REFERENCES config.record_attr_definition(name),
1060 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1061 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1062 CONSTRAINT metabib_field_or_record_attr CHECK (
1063 metabib_field IS NOT NULL OR
1064 record_attr IS NOT NULL
1066 CONSTRAINT attr_or_attr_type CHECK (
1067 z3950_attr IS NOT NULL OR
1068 z3950_attr_type IS NOT NULL
1070 -- ensure the selected z3950_attr_type refers to a valid attr name
1071 CONSTRAINT valid_z3950_attr_type CHECK (
1072 z3950_attr_type IS NULL OR
1073 evergreen.z3950_attr_name_is_valid(z3950_attr_type)