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 ('serial.rematerialize_on_same_holding_code');
51 CREATE TABLE config.global_flag (
53 ) INHERITS (config.internal_flag);
54 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
56 CREATE TABLE config.upgrade_log (
57 version TEXT PRIMARY KEY,
58 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
62 CREATE TABLE config.db_patch_dependencies (
63 db_patch TEXT PRIMARY KEY,
68 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
74 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
76 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
82 CREATE TRIGGER no_overlapping_sups
83 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
84 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
86 CREATE TRIGGER no_overlapping_deps
87 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
88 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
90 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0760', :eg_version); -- senator/miker
92 CREATE TABLE config.bib_source (
93 id SERIAL PRIMARY KEY,
94 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
95 source TEXT NOT NULL UNIQUE,
96 transcendant BOOL NOT NULL DEFAULT FALSE,
97 can_have_copies BOOL NOT NULL DEFAULT TRUE
99 COMMENT ON TABLE config.bib_source IS $$
100 This is table is used to set up the relative "quality" of each
101 MARC source, such as OCLC. Also identifies "transcendant" sources,
102 i.e., sources of bib records that should display in the OPAC
103 even if no copies or located URIs are attached. Also indicates if
104 the source is allowed to have actual copies on its bibs. Volumes
105 for targeted URIs are unaffected by this setting.
108 CREATE TABLE config.standing (
109 id SERIAL PRIMARY KEY,
110 value TEXT NOT NULL UNIQUE
112 COMMENT ON TABLE config.standing IS $$
115 This table contains the values that can be applied to a patron
116 by a staff member. These values should not be changed, other
117 than for translation, as the ID column is currently a "magic
118 number" in the source. :(
121 CREATE TABLE config.standing_penalty (
122 id SERIAL PRIMARY KEY,
123 name TEXT NOT NULL UNIQUE,
126 staff_alert BOOL NOT NULL DEFAULT FALSE,
130 CREATE TABLE config.xml_transform (
131 name TEXT PRIMARY KEY,
132 namespace_uri TEXT NOT NULL,
133 prefix TEXT NOT NULL,
137 CREATE TABLE config.biblio_fingerprint (
138 id SERIAL PRIMARY KEY,
141 first_word BOOL NOT NULL DEFAULT FALSE,
142 format TEXT NOT NULL DEFAULT 'marcxml'
145 INSERT INTO config.biblio_fingerprint (name, xpath, format)
148 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
149 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
150 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
151 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
152 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
156 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
159 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
160 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
161 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
162 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
163 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
168 CREATE TABLE config.metabib_class (
169 name TEXT PRIMARY KEY,
170 label TEXT NOT NULL UNIQUE,
171 buoyant BOOL DEFAULT FALSE NOT NULL,
172 restrict BOOL DEFAULT FALSE NOT NULL,
173 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
174 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
175 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
176 d_weight NUMERIC DEFAULT 0.1 NOT NULL
179 CREATE TABLE config.metabib_field (
180 id SERIAL PRIMARY KEY,
181 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
185 weight INT NOT NULL DEFAULT 1,
186 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
187 search_field BOOL NOT NULL DEFAULT TRUE,
188 facet_field BOOL NOT NULL DEFAULT FALSE,
189 browse_field BOOL NOT NULL DEFAULT TRUE,
192 restrict BOOL DEFAULT FALSE NOT NULL
194 COMMENT ON TABLE config.metabib_field IS $$
195 XPath used for record indexing ingest
197 This table contains the XPath used to chop up MODS into its
198 indexable parts. Each XPath entry is named and assigned to
199 a "class" of either title, subject, author, keyword, series
203 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
205 CREATE TABLE config.ts_config_list (
209 COMMENT ON TABLE config.ts_config_list IS $$
212 A list of full text configs with names and descriptions.
215 CREATE TABLE config.metabib_class_ts_map (
216 id SERIAL PRIMARY KEY,
217 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
218 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
219 active BOOL NOT NULL DEFAULT TRUE,
220 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
221 index_lang TEXT NULL,
222 search_lang TEXT NULL,
223 always BOOL NOT NULL DEFAULT true
225 COMMENT ON TABLE config.metabib_class_ts_map IS $$
226 Text Search Configs for metabib class indexing
228 This table contains text search config definitions for
229 storing index_vector values.
232 CREATE TABLE config.metabib_field_ts_map (
233 id SERIAL PRIMARY KEY,
234 metabib_field INT NOT NULL REFERENCES config.metabib_field (id),
235 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
236 active BOOL NOT NULL DEFAULT TRUE,
237 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
238 index_lang TEXT NULL,
239 search_lang TEXT NULL
241 COMMENT ON TABLE config.metabib_field_ts_map IS $$
242 Text Search Configs for metabib field indexing
244 This table contains text search config definitions for
245 storing index_vector values.
248 CREATE TABLE config.metabib_search_alias (
249 alias TEXT PRIMARY KEY,
250 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
251 field INT REFERENCES config.metabib_field (id)
254 CREATE TABLE config.non_cataloged_type (
255 id SERIAL PRIMARY KEY,
256 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
258 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
259 in_house BOOL NOT NULL DEFAULT FALSE,
260 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
262 COMMENT ON TABLE config.non_cataloged_type IS $$
263 Types of valid non-cataloged items.
266 CREATE TABLE config.identification_type (
267 id SERIAL PRIMARY KEY,
268 name TEXT NOT NULL UNIQUE
270 COMMENT ON TABLE config.identification_type IS $$
271 Types of valid patron identification.
273 Each patron must display at least one valid form of identification
274 in order to get a library card. This table lists those forms.
277 CREATE TABLE config.rule_circ_duration (
278 id SERIAL PRIMARY KEY,
279 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
280 extended INTERVAL NOT NULL,
281 normal INTERVAL NOT NULL,
282 shrt INTERVAL NOT NULL,
283 max_renewals INT NOT NULL
285 COMMENT ON TABLE config.rule_circ_duration IS $$
286 Circulation Duration rules
288 Each circulation is given a duration based on one of these rules.
291 CREATE TABLE config.hard_due_date (
292 id SERIAL PRIMARY KEY,
293 name TEXT NOT NULL UNIQUE,
294 ceiling_date TIMESTAMPTZ NOT NULL,
295 forceto BOOL NOT NULL,
296 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
299 CREATE TABLE config.hard_due_date_values (
300 id SERIAL PRIMARY KEY,
301 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
302 DEFERRABLE INITIALLY DEFERRED,
303 ceiling_date TIMESTAMPTZ NOT NULL,
304 active_date TIMESTAMPTZ NOT NULL
307 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
309 temp_value config.hard_due_date_values%ROWTYPE;
313 SELECT DISTINCT ON (hard_due_date) *
314 FROM config.hard_due_date_values
315 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
316 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
318 UPDATE config.hard_due_date
319 SET ceiling_date = temp_value.ceiling_date
320 WHERE id = temp_value.hard_due_date
321 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
324 updated := updated + 1;
330 $func$ LANGUAGE plpgsql;
332 CREATE TABLE config.rule_max_fine (
333 id SERIAL PRIMARY KEY,
334 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
335 amount NUMERIC(6,2) NOT NULL,
336 is_percent BOOL NOT NULL DEFAULT FALSE
338 COMMENT ON TABLE config.rule_max_fine IS $$
339 Circulation Max Fine rules
341 Each circulation is given a maximum fine based on one of
345 CREATE TABLE config.rule_recurring_fine (
346 id SERIAL PRIMARY KEY,
347 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
348 high NUMERIC(6,2) NOT NULL,
349 normal NUMERIC(6,2) NOT NULL,
350 low NUMERIC(6,2) NOT NULL,
351 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
352 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
354 COMMENT ON TABLE config.rule_recurring_fine IS $$
355 Circulation Recurring Fine rules
357 Each circulation is given a recurring fine amount based on one of
358 these rules. Note that it is recommended to run the fine generator
359 (from cron) at least as frequently as the lowest recurrence interval
360 used by your circulation rules so that accrued fines will be up
365 CREATE TABLE config.rule_age_hold_protect (
366 id SERIAL PRIMARY KEY,
367 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
368 age INTERVAL NOT NULL,
371 COMMENT ON TABLE config.rule_age_hold_protect IS $$
372 Hold Item Age Protection rules
374 A hold request can only capture new(ish) items when they are
375 within a particular proximity of the pickup_lib of the request.
376 The proximity ('prox' column) is calculated by counting
377 the number of tree edges between the pickup_lib and either the
378 owning_lib or circ_lib of the copy that could fulfill the hold,
379 as determined by the distance_is_from_owner value of the hold matrix
380 rule controlling the hold request.
383 CREATE TABLE config.copy_status (
384 id SERIAL PRIMARY KEY,
385 name TEXT NOT NULL UNIQUE,
386 holdable BOOL NOT NULL DEFAULT FALSE,
387 opac_visible BOOL NOT NULL DEFAULT FALSE,
388 copy_active BOOL NOT NULL DEFAULT FALSE,
389 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
391 COMMENT ON TABLE config.copy_status IS $$
394 The available copy statuses, and whether a copy in that
395 status is available for hold request capture. 0 (zero) is
396 the only special number in this set, meaning that the item
397 is available for immediate checkout, and is counted as available
400 Statuses with an ID below 100 are not removable, and have special
401 meaning in the code. Do not change them except to translate the
404 You may add and remove statuses above 100, and these can be used
405 to remove items from normal circulation without affecting the rest
406 of the copy's values or its location.
409 CREATE TABLE config.net_access_level (
410 id SERIAL PRIMARY KEY,
411 name TEXT NOT NULL UNIQUE
413 COMMENT ON TABLE config.net_access_level IS $$
414 Patron Network Access level
416 This will be used to inform the in-library firewall of how much
417 internet access the using patron should be allowed.
421 CREATE TABLE config.remote_account (
422 id SERIAL PRIMARY KEY,
424 host TEXT NOT NULL, -- name or IP, :port optional
425 username TEXT, -- optional, since we could default to $USER
426 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
427 account TEXT, -- aka profile or FTP "account" command
428 path TEXT, -- aka directory
429 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
430 last_activity TIMESTAMP WITH TIME ZONE
433 CREATE TABLE config.marc21_rec_type_map (
434 code TEXT PRIMARY KEY,
435 type_val TEXT NOT NULL,
436 blvl_val TEXT NOT NULL
439 CREATE TABLE config.marc21_ff_pos_map (
440 id SERIAL PRIMARY KEY,
441 fixed_field TEXT NOT NULL,
443 rec_type TEXT NOT NULL,
444 start_pos INT NOT NULL,
446 default_val TEXT NOT NULL DEFAULT ' '
449 CREATE TABLE config.marc21_physical_characteristic_type_map (
450 ptype_key TEXT PRIMARY KEY,
451 label TEXT NOT NULL -- I18N
454 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
455 id SERIAL PRIMARY KEY,
456 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
457 subfield TEXT NOT NULL,
458 start_pos INT NOT NULL,
460 label TEXT NOT NULL -- I18N
463 CREATE TABLE config.marc21_physical_characteristic_value_map (
464 id SERIAL PRIMARY KEY,
466 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
467 label TEXT NOT NULL -- I18N
471 CREATE TABLE config.z3950_source (
472 name TEXT PRIMARY KEY,
473 label TEXT NOT NULL UNIQUE,
477 record_format TEXT NOT NULL DEFAULT 'FI',
478 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
479 auth BOOL NOT NULL DEFAULT TRUE,
480 use_perm INT -- REFERENCES permission.perm_list (id)
483 COMMENT ON TABLE config.z3950_source IS $$
486 Each row in this table represents a database searchable via Z39.50.
489 COMMENT ON COLUMN config.z3950_source.record_format IS $$
493 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
494 Z39.50 preferred record syntax..
497 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
498 If set, this permission is required for the source to be listed in the staff
499 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
502 CREATE TABLE config.z3950_attr (
503 id SERIAL PRIMARY KEY,
504 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
509 truncation INT NOT NULL DEFAULT 0,
510 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
513 CREATE TABLE config.i18n_locale (
514 code TEXT PRIMARY KEY,
515 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
516 name TEXT UNIQUE NOT NULL,
520 CREATE TABLE config.i18n_core (
521 id BIGSERIAL PRIMARY KEY,
522 fq_field TEXT NOT NULL,
523 identity_value TEXT NOT NULL,
524 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
528 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
530 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
534 UPDATE config.i18n_core
535 SET identity_value = $$ || quote_literal(new_ident) || $$
536 WHERE fq_field LIKE '$$ || hint || $$.%'
537 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
542 $_$ LANGUAGE PLPGSQL;
544 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
546 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
549 $_$ LANGUAGE PLPGSQL;
551 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
553 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
556 $_$ LANGUAGE PLPGSQL;
558 CREATE TABLE config.billing_type (
559 id SERIAL PRIMARY KEY,
561 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
562 default_price NUMERIC(6,2),
563 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
566 CREATE TABLE config.settings_group (
567 name TEXT PRIMARY KEY,
568 label TEXT UNIQUE NOT NULL -- I18N
571 CREATE TABLE config.org_unit_setting_type (
572 name TEXT PRIMARY KEY,
573 label TEXT UNIQUE NOT NULL,
574 grp TEXT REFERENCES config.settings_group (name),
576 datatype TEXT NOT NULL DEFAULT 'string',
581 -- define valid datatypes
583 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
584 ( 'bool', 'integer', 'float', 'currency', 'interval',
585 'date', 'string', 'object', 'array', 'link' ) ),
587 -- fm_class is meaningful only for 'link' datatype
589 CONSTRAINT coust_no_empty_link CHECK
590 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
591 ( datatype <> 'link' AND fm_class IS NULL ) )
594 CREATE TABLE config.usr_setting_type (
596 name TEXT PRIMARY KEY,
597 opac_visible BOOL NOT NULL DEFAULT FALSE,
598 label TEXT UNIQUE NOT NULL,
600 grp TEXT REFERENCES config.settings_group (name),
601 datatype TEXT NOT NULL DEFAULT 'string',
605 -- define valid datatypes
607 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
608 ( 'bool', 'integer', 'float', 'currency', 'interval',
609 'date', 'string', 'object', 'array', 'link' ) ),
612 -- fm_class is meaningful only for 'link' datatype
614 CONSTRAINT coust_no_empty_link CHECK
615 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
616 ( datatype <> 'link' AND fm_class IS NULL ) )
620 -- Some handy functions, based on existing ones, to provide optional ingest normalization
622 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
623 SELECT SUBSTRING($1,$2);
624 $func$ LANGUAGE SQL STRICT IMMUTABLE;
626 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
627 SELECT SUBSTRING($1,1,$2);
628 $func$ LANGUAGE SQL STRICT IMMUTABLE;
630 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
631 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
632 $func$ LANGUAGE SQL STRICT IMMUTABLE;
634 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
635 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
636 $func$ LANGUAGE SQL STRICT IMMUTABLE;
638 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
639 SELECT approximate_date( $1, '0');
640 $func$ LANGUAGE SQL STRICT IMMUTABLE;
642 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
643 SELECT approximate_date( $1, '9');
644 $func$ LANGUAGE SQL STRICT IMMUTABLE;
646 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
647 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
648 $func$ LANGUAGE SQL STRICT IMMUTABLE;
650 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
651 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
652 $func$ LANGUAGE SQL STRICT IMMUTABLE;
654 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
659 # Find the first ISBN, force it to ISBN13 and return it
663 foreach my $word (split(/\s/, $input)) {
664 my $isbn = Business::ISBN->new($word);
666 # First check the checksum; if it is not valid, fix it and add the original
667 # bad-checksum ISBN to the output
668 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
669 $isbn->fix_checksum();
672 # If we now have a valid ISBN, force it to ISBN13 and return it
673 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
676 $func$ LANGUAGE PLPERLU;
678 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
679 Inspired by translate_isbn1013
681 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
682 version without hypens and with a repaired checksum if the checksum was bad
686 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
691 # For each ISBN found in a single string containing a set of ISBNs:
692 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
693 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
698 foreach my $word (split(/\s/, $input)) {
699 my $isbn = Business::ISBN->new($word);
701 # First check the checksum; if it is not valid, fix it and add the original
702 # bad-checksum ISBN to the output
703 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
704 $output .= $isbn->isbn() . " ";
705 $isbn->fix_checksum();
708 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
709 # and add the normalized original ISBN to the output
710 if ($isbn && $isbn->is_valid()) {
711 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
712 $output .= $isbn->isbn . " ";
714 # If we successfully converted the ISBN to its counterpart, add the
715 # converted ISBN to the output as well
716 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
719 return $output if $output;
721 # If there were no valid ISBNs, just return the raw input
723 $func$ LANGUAGE PLPERLU;
725 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
726 The translate_isbn1013 function takes an input ISBN and returns the
727 following in a single space-delimited string if the input ISBN is valid:
728 - The normalized input ISBN (hyphens stripped)
729 - The normalized input ISBN with a fixed checksum if the checksum was bad
730 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
733 -- And ... a table in which to register them
735 CREATE TABLE config.index_normalizer (
736 id SERIAL PRIMARY KEY,
737 name TEXT UNIQUE NOT NULL,
740 param_count INT NOT NULL DEFAULT 0
743 CREATE TABLE config.metabib_field_index_norm_map (
744 id SERIAL PRIMARY KEY,
745 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
746 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
748 pos INT NOT NULL DEFAULT 0
751 CREATE TABLE config.record_attr_definition (
752 name TEXT PRIMARY KEY,
753 label TEXT NOT NULL, -- I18N
755 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
756 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
758 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
759 tag TEXT, -- LIKE format
760 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
762 -- This is used for both tag/sf and xpath entries
765 -- For xpath-extracted attrs
767 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
772 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
774 -- For phys-char fields
775 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
778 CREATE TABLE config.record_attr_index_norm_map (
779 id SERIAL PRIMARY KEY,
780 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
781 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
783 pos INT NOT NULL DEFAULT 0
786 CREATE TABLE config.coded_value_map (
787 id SERIAL PRIMARY KEY,
788 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
792 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
794 is_simple BOOL NOT NULL DEFAULT FALSE
797 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
798 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
799 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
800 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
801 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
802 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
803 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
805 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$
807 current_row config.coded_value_map%ROWTYPE;
809 -- Look for a current value
810 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
812 IF FOUND AND NOT add_only THEN
813 -- Update anything we were handed
814 current_row.value := COALESCE(current_row.value, in_value);
815 current_row.description := COALESCE(current_row.description, in_description);
816 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
817 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
818 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
819 UPDATE config.coded_value_map
821 value = current_row.value,
822 description = current_row.description,
823 opac_visible = current_row.opac_visible,
824 search_label = current_row.search_label,
825 is_simple = current_row.is_simple
826 WHERE id = current_row.id;
828 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
829 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
832 $f$ LANGUAGE PLPGSQL;
834 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
835 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
836 SELECT DISTINCT l.version
837 FROM config.upgrade_log l
838 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
839 WHERE d.db_patch = $1
842 -- List applied db patches that are superseded by (and block the application of) my_db_patch
843 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
844 SELECT DISTINCT l.version
845 FROM config.upgrade_log l
846 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
847 WHERE d.db_patch = $1
850 -- List applied db patches that deprecates (and block the application of) my_db_patch
851 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
853 FROM config.db_patch_dependencies
854 WHERE ARRAY[$1]::TEXT[] && deprecates
857 -- List applied db patches that supersedes (and block the application of) my_db_patch
858 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
860 FROM config.db_patch_dependencies
861 WHERE ARRAY[$1]::TEXT[] && supersedes
864 -- Make sure that no deprecated or superseded db patches are currently applied
865 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
867 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
869 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
871 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
873 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
876 -- Raise an exception if there are, in fact, dep/sup conflict
877 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
882 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
883 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
884 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
886 Upgrade script % can not be applied:
887 applied deprecated scripts %
888 applied superseded scripts %
892 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
893 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
894 evergreen.upgrade_list_applied_deprecated(my_db_patch),
895 evergreen.upgrade_list_applied_superseded(my_db_patch);
898 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
903 CREATE TABLE config.barcode_completion (
904 id SERIAL PRIMARY KEY,
905 active BOOL NOT NULL DEFAULT true,
906 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
909 length INT NOT NULL DEFAULT 0,
911 padding_end BOOL NOT NULL DEFAULT false,
912 asset BOOL NOT NULL DEFAULT true,
913 actor BOOL NOT NULL DEFAULT true
916 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
918 -- Add support for logging, only keep the most recent five rows for each category.
921 CREATE TABLE config.org_unit_setting_type_log (
922 id BIGSERIAL PRIMARY KEY,
923 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
924 org INT, --REFERENCES actor.org_unit (id),
927 field_name TEXT REFERENCES config.org_unit_setting_type (name)
930 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
931 Org Unit setting Logs
933 This table contains the most recent changes to each setting
934 in actor.org_unit_setting, allowing for mistakes to be undone.
935 This is NOT meant to be an auditor, but rather an undo/redo.
938 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
940 -- Only keeps the most recent five settings changes.
941 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
942 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
944 IF (TG_OP = 'UPDATE') THEN
946 ELSIF (TG_OP = 'INSERT') THEN
951 $oustl_limit$ LANGUAGE plpgsql;
953 CREATE TRIGGER limit_logs_oust
954 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
955 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
957 CREATE TABLE config.sms_carrier (
958 id SERIAL PRIMARY KEY,
962 active BOOLEAN DEFAULT TRUE
965 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
967 CREATE TABLE config.usr_activity_type (
968 id SERIAL PRIMARY KEY,
972 label TEXT NOT NULL, -- i18n
973 egroup config.usr_activity_group NOT NULL,
974 enabled BOOL NOT NULL DEFAULT TRUE,
975 transient BOOL NOT NULL DEFAULT FALSE,
976 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
979 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
980 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
982 CREATE TABLE config.filter_dialog_interface (
983 key TEXT PRIMARY KEY,
987 CREATE TABLE config.filter_dialog_filter_set (
988 id SERIAL PRIMARY KEY,
990 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
991 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
992 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
993 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
994 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
995 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
998 CREATE TABLE config.best_hold_order(
999 id SERIAL PRIMARY KEY,
1000 name TEXT UNIQUE, -- i18n
1001 pprox INT, -- copy capture <-> pickup lib prox
1002 hprox INT, -- copy circ lib <-> request lib prox
1003 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1004 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1005 priority INT, -- group hold priority
1006 cut INT, -- cut-in-line
1007 depth INT, -- selection depth
1008 htime INT, -- time since last home-lib circ exceeds org-unit setting
1009 rtime INT, -- request time
1010 shtime INT -- time since copy last trip home exceeds org-unit setting
1013 -- At least one of these columns must contain a non-null value
1014 ALTER TABLE config.best_hold_order ADD CHECK ((
1015 pprox IS NOT NULL OR
1016 hprox IS NOT NULL OR
1017 aprox IS NOT NULL OR
1018 priority IS NOT NULL OR
1020 depth IS NOT NULL OR
1021 htime IS NOT NULL OR