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 ('0772', :eg_version); -- senator/miker
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 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
178 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
179 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
180 d_weight NUMERIC DEFAULT 0.1 NOT NULL
183 CREATE TABLE config.metabib_field (
184 id SERIAL PRIMARY KEY,
185 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
189 weight INT NOT NULL DEFAULT 1,
190 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
191 search_field BOOL NOT NULL DEFAULT TRUE,
192 facet_field BOOL NOT NULL DEFAULT FALSE,
193 browse_field BOOL NOT NULL DEFAULT TRUE,
196 restrict BOOL DEFAULT FALSE NOT NULL
198 COMMENT ON TABLE config.metabib_field IS $$
199 XPath used for record indexing ingest
201 This table contains the XPath used to chop up MODS into its
202 indexable parts. Each XPath entry is named and assigned to
203 a "class" of either title, subject, author, keyword, series
207 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
209 CREATE TABLE config.ts_config_list (
213 COMMENT ON TABLE config.ts_config_list IS $$
216 A list of full text configs with names and descriptions.
219 CREATE TABLE config.metabib_class_ts_map (
220 id SERIAL PRIMARY KEY,
221 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
222 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
223 active BOOL NOT NULL DEFAULT TRUE,
224 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
225 index_lang TEXT NULL,
226 search_lang TEXT NULL,
227 always BOOL NOT NULL DEFAULT true
229 COMMENT ON TABLE config.metabib_class_ts_map IS $$
230 Text Search Configs for metabib class indexing
232 This table contains text search config definitions for
233 storing index_vector values.
236 CREATE TABLE config.metabib_field_ts_map (
237 id SERIAL PRIMARY KEY,
238 metabib_field INT NOT NULL REFERENCES config.metabib_field (id),
239 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
240 active BOOL NOT NULL DEFAULT TRUE,
241 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
242 index_lang TEXT NULL,
243 search_lang TEXT NULL
245 COMMENT ON TABLE config.metabib_field_ts_map IS $$
246 Text Search Configs for metabib field indexing
248 This table contains text search config definitions for
249 storing index_vector values.
252 CREATE TABLE config.metabib_search_alias (
253 alias TEXT PRIMARY KEY,
254 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
255 field INT REFERENCES config.metabib_field (id)
258 CREATE TABLE config.non_cataloged_type (
259 id SERIAL PRIMARY KEY,
260 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
262 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
263 in_house BOOL NOT NULL DEFAULT FALSE,
264 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
266 COMMENT ON TABLE config.non_cataloged_type IS $$
267 Types of valid non-cataloged items.
270 CREATE TABLE config.identification_type (
271 id SERIAL PRIMARY KEY,
272 name TEXT NOT NULL UNIQUE
274 COMMENT ON TABLE config.identification_type IS $$
275 Types of valid patron identification.
277 Each patron must display at least one valid form of identification
278 in order to get a library card. This table lists those forms.
281 CREATE TABLE config.rule_circ_duration (
282 id SERIAL PRIMARY KEY,
283 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
284 extended INTERVAL NOT NULL,
285 normal INTERVAL NOT NULL,
286 shrt INTERVAL NOT NULL,
287 max_renewals INT NOT NULL
289 COMMENT ON TABLE config.rule_circ_duration IS $$
290 Circulation Duration rules
292 Each circulation is given a duration based on one of these rules.
295 CREATE TABLE config.hard_due_date (
296 id SERIAL PRIMARY KEY,
297 name TEXT NOT NULL UNIQUE,
298 ceiling_date TIMESTAMPTZ NOT NULL,
299 forceto BOOL NOT NULL,
300 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
303 CREATE TABLE config.hard_due_date_values (
304 id SERIAL PRIMARY KEY,
305 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
306 DEFERRABLE INITIALLY DEFERRED,
307 ceiling_date TIMESTAMPTZ NOT NULL,
308 active_date TIMESTAMPTZ NOT NULL
311 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
313 temp_value config.hard_due_date_values%ROWTYPE;
317 SELECT DISTINCT ON (hard_due_date) *
318 FROM config.hard_due_date_values
319 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
320 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
322 UPDATE config.hard_due_date
323 SET ceiling_date = temp_value.ceiling_date
324 WHERE id = temp_value.hard_due_date
325 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
328 updated := updated + 1;
334 $func$ LANGUAGE plpgsql;
336 CREATE TABLE config.rule_max_fine (
337 id SERIAL PRIMARY KEY,
338 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
339 amount NUMERIC(6,2) NOT NULL,
340 is_percent BOOL NOT NULL DEFAULT FALSE
342 COMMENT ON TABLE config.rule_max_fine IS $$
343 Circulation Max Fine rules
345 Each circulation is given a maximum fine based on one of
349 CREATE TABLE config.rule_recurring_fine (
350 id SERIAL PRIMARY KEY,
351 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
352 high NUMERIC(6,2) NOT NULL,
353 normal NUMERIC(6,2) NOT NULL,
354 low NUMERIC(6,2) NOT NULL,
355 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
356 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
358 COMMENT ON TABLE config.rule_recurring_fine IS $$
359 Circulation Recurring Fine rules
361 Each circulation is given a recurring fine amount based on one of
362 these rules. Note that it is recommended to run the fine generator
363 (from cron) at least as frequently as the lowest recurrence interval
364 used by your circulation rules so that accrued fines will be up
369 CREATE TABLE config.rule_age_hold_protect (
370 id SERIAL PRIMARY KEY,
371 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
372 age INTERVAL NOT NULL,
375 COMMENT ON TABLE config.rule_age_hold_protect IS $$
376 Hold Item Age Protection rules
378 A hold request can only capture new(ish) items when they are
379 within a particular proximity of the pickup_lib of the request.
380 The proximity ('prox' column) is calculated by counting
381 the number of tree edges between the pickup_lib and either the
382 owning_lib or circ_lib of the copy that could fulfill the hold,
383 as determined by the distance_is_from_owner value of the hold matrix
384 rule controlling the hold request.
387 CREATE TABLE config.copy_status (
388 id SERIAL PRIMARY KEY,
389 name TEXT NOT NULL UNIQUE,
390 holdable BOOL NOT NULL DEFAULT FALSE,
391 opac_visible BOOL NOT NULL DEFAULT FALSE,
392 copy_active BOOL NOT NULL DEFAULT FALSE,
393 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
395 COMMENT ON TABLE config.copy_status IS $$
398 The available copy statuses, and whether a copy in that
399 status is available for hold request capture. 0 (zero) is
400 the only special number in this set, meaning that the item
401 is available for immediate checkout, and is counted as available
404 Statuses with an ID below 100 are not removable, and have special
405 meaning in the code. Do not change them except to translate the
408 You may add and remove statuses above 100, and these can be used
409 to remove items from normal circulation without affecting the rest
410 of the copy's values or its location.
413 CREATE TABLE config.net_access_level (
414 id SERIAL PRIMARY KEY,
415 name TEXT NOT NULL UNIQUE
417 COMMENT ON TABLE config.net_access_level IS $$
418 Patron Network Access level
420 This will be used to inform the in-library firewall of how much
421 internet access the using patron should be allowed.
425 CREATE TABLE config.remote_account (
426 id SERIAL PRIMARY KEY,
428 host TEXT NOT NULL, -- name or IP, :port optional
429 username TEXT, -- optional, since we could default to $USER
430 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
431 account TEXT, -- aka profile or FTP "account" command
432 path TEXT, -- aka directory
433 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
434 last_activity TIMESTAMP WITH TIME ZONE
437 CREATE TABLE config.marc21_rec_type_map (
438 code TEXT PRIMARY KEY,
439 type_val TEXT NOT NULL,
440 blvl_val TEXT NOT NULL
443 CREATE TABLE config.marc21_ff_pos_map (
444 id SERIAL PRIMARY KEY,
445 fixed_field TEXT NOT NULL,
447 rec_type TEXT NOT NULL,
448 start_pos INT NOT NULL,
450 default_val TEXT NOT NULL DEFAULT ' '
453 CREATE TABLE config.marc21_physical_characteristic_type_map (
454 ptype_key TEXT PRIMARY KEY,
455 label TEXT NOT NULL -- I18N
458 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
459 id SERIAL PRIMARY KEY,
460 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
461 subfield TEXT NOT NULL,
462 start_pos INT NOT NULL,
464 label TEXT NOT NULL -- I18N
467 CREATE TABLE config.marc21_physical_characteristic_value_map (
468 id SERIAL PRIMARY KEY,
470 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
471 label TEXT NOT NULL -- I18N
475 CREATE TABLE config.z3950_source (
476 name TEXT PRIMARY KEY,
477 label TEXT NOT NULL UNIQUE,
481 record_format TEXT NOT NULL DEFAULT 'FI',
482 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
483 auth BOOL NOT NULL DEFAULT TRUE,
484 use_perm INT -- REFERENCES permission.perm_list (id)
487 COMMENT ON TABLE config.z3950_source IS $$
490 Each row in this table represents a database searchable via Z39.50.
493 COMMENT ON COLUMN config.z3950_source.record_format IS $$
497 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
498 Z39.50 preferred record syntax..
501 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
502 If set, this permission is required for the source to be listed in the staff
503 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
506 CREATE TABLE config.z3950_attr (
507 id SERIAL PRIMARY KEY,
508 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
513 truncation INT NOT NULL DEFAULT 0,
514 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
517 CREATE TABLE config.i18n_locale (
518 code TEXT PRIMARY KEY,
519 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
520 name TEXT UNIQUE NOT NULL,
524 CREATE TABLE config.i18n_core (
525 id BIGSERIAL PRIMARY KEY,
526 fq_field TEXT NOT NULL,
527 identity_value TEXT NOT NULL,
528 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
532 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
534 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
538 UPDATE config.i18n_core
539 SET identity_value = $$ || quote_literal(new_ident) || $$
540 WHERE fq_field LIKE '$$ || hint || $$.%'
541 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
546 $_$ LANGUAGE PLPGSQL;
548 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
550 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
553 $_$ LANGUAGE PLPGSQL;
555 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
557 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
560 $_$ LANGUAGE PLPGSQL;
562 CREATE TABLE config.billing_type (
563 id SERIAL PRIMARY KEY,
565 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
566 default_price NUMERIC(6,2),
567 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
570 CREATE TABLE config.settings_group (
571 name TEXT PRIMARY KEY,
572 label TEXT UNIQUE NOT NULL -- I18N
575 CREATE TABLE config.org_unit_setting_type (
576 name TEXT PRIMARY KEY,
577 label TEXT UNIQUE NOT NULL,
578 grp TEXT REFERENCES config.settings_group (name),
580 datatype TEXT NOT NULL DEFAULT 'string',
585 -- define valid datatypes
587 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
588 ( 'bool', 'integer', 'float', 'currency', 'interval',
589 'date', 'string', 'object', 'array', 'link' ) ),
591 -- fm_class is meaningful only for 'link' datatype
593 CONSTRAINT coust_no_empty_link CHECK
594 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
595 ( datatype <> 'link' AND fm_class IS NULL ) )
598 CREATE TABLE config.usr_setting_type (
600 name TEXT PRIMARY KEY,
601 opac_visible BOOL NOT NULL DEFAULT FALSE,
602 label TEXT UNIQUE NOT NULL,
604 grp TEXT REFERENCES config.settings_group (name),
605 datatype TEXT NOT NULL DEFAULT 'string',
609 -- define valid datatypes
611 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
612 ( 'bool', 'integer', 'float', 'currency', 'interval',
613 'date', 'string', 'object', 'array', 'link' ) ),
616 -- fm_class is meaningful only for 'link' datatype
618 CONSTRAINT coust_no_empty_link CHECK
619 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
620 ( datatype <> 'link' AND fm_class IS NULL ) )
624 -- Some handy functions, based on existing ones, to provide optional ingest normalization
626 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
627 SELECT SUBSTRING($1,$2);
628 $func$ LANGUAGE SQL STRICT IMMUTABLE;
630 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
631 SELECT SUBSTRING($1,1,$2);
632 $func$ LANGUAGE SQL STRICT IMMUTABLE;
634 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
635 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
636 $func$ LANGUAGE SQL STRICT IMMUTABLE;
638 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
639 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
640 $func$ LANGUAGE SQL STRICT IMMUTABLE;
642 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
643 SELECT approximate_date( $1, '0');
644 $func$ LANGUAGE SQL STRICT IMMUTABLE;
646 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
647 SELECT approximate_date( $1, '9');
648 $func$ LANGUAGE SQL STRICT IMMUTABLE;
650 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
651 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
652 $func$ LANGUAGE SQL STRICT IMMUTABLE;
654 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
655 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
656 $func$ LANGUAGE SQL STRICT IMMUTABLE;
658 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
663 # Find the first ISBN, force it to ISBN13 and return it
667 foreach my $word (split(/\s/, $input)) {
668 my $isbn = Business::ISBN->new($word);
670 # First check the checksum; if it is not valid, fix it and add the original
671 # bad-checksum ISBN to the output
672 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
673 $isbn->fix_checksum();
676 # If we now have a valid ISBN, force it to ISBN13 and return it
677 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
680 $func$ LANGUAGE PLPERLU;
682 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
683 Inspired by translate_isbn1013
685 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
686 version without hypens and with a repaired checksum if the checksum was bad
690 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
695 # For each ISBN found in a single string containing a set of ISBNs:
696 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
697 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
702 foreach my $word (split(/\s/, $input)) {
703 my $isbn = Business::ISBN->new($word);
705 # First check the checksum; if it is not valid, fix it and add the original
706 # bad-checksum ISBN to the output
707 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
708 $output .= $isbn->isbn() . " ";
709 $isbn->fix_checksum();
712 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
713 # and add the normalized original ISBN to the output
714 if ($isbn && $isbn->is_valid()) {
715 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
716 $output .= $isbn->isbn . " ";
718 # If we successfully converted the ISBN to its counterpart, add the
719 # converted ISBN to the output as well
720 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
723 return $output if $output;
725 # If there were no valid ISBNs, just return the raw input
727 $func$ LANGUAGE PLPERLU;
729 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
730 The translate_isbn1013 function takes an input ISBN and returns the
731 following in a single space-delimited string if the input ISBN is valid:
732 - The normalized input ISBN (hyphens stripped)
733 - The normalized input ISBN with a fixed checksum if the checksum was bad
734 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
737 -- And ... a table in which to register them
739 CREATE TABLE config.index_normalizer (
740 id SERIAL PRIMARY KEY,
741 name TEXT UNIQUE NOT NULL,
744 param_count INT NOT NULL DEFAULT 0
747 CREATE TABLE config.metabib_field_index_norm_map (
748 id SERIAL PRIMARY KEY,
749 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
750 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
752 pos INT NOT NULL DEFAULT 0
755 CREATE TABLE config.record_attr_definition (
756 name TEXT PRIMARY KEY,
757 label TEXT NOT NULL, -- I18N
759 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
760 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
762 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
763 tag TEXT, -- LIKE format
764 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
766 -- This is used for both tag/sf and xpath entries
769 -- For xpath-extracted attrs
771 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
776 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
778 -- For phys-char fields
779 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
782 CREATE TABLE config.record_attr_index_norm_map (
783 id SERIAL PRIMARY KEY,
784 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
785 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
787 pos INT NOT NULL DEFAULT 0
790 CREATE TABLE config.coded_value_map (
791 id SERIAL PRIMARY KEY,
792 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
796 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
798 is_simple BOOL NOT NULL DEFAULT FALSE
801 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
802 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
803 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
804 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
805 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
806 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
807 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
809 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$
811 current_row config.coded_value_map%ROWTYPE;
813 -- Look for a current value
814 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
816 IF FOUND AND NOT add_only THEN
817 -- Update anything we were handed
818 current_row.value := COALESCE(current_row.value, in_value);
819 current_row.description := COALESCE(current_row.description, in_description);
820 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
821 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
822 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
823 UPDATE config.coded_value_map
825 value = current_row.value,
826 description = current_row.description,
827 opac_visible = current_row.opac_visible,
828 search_label = current_row.search_label,
829 is_simple = current_row.is_simple
830 WHERE id = current_row.id;
832 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
833 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
836 $f$ LANGUAGE PLPGSQL;
838 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
839 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
840 SELECT DISTINCT l.version
841 FROM config.upgrade_log l
842 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
843 WHERE d.db_patch = $1
846 -- List applied db patches that are superseded by (and block the application of) my_db_patch
847 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
848 SELECT DISTINCT l.version
849 FROM config.upgrade_log l
850 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
851 WHERE d.db_patch = $1
854 -- List applied db patches that deprecates (and block the application of) my_db_patch
855 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
857 FROM config.db_patch_dependencies
858 WHERE ARRAY[$1]::TEXT[] && deprecates
861 -- List applied db patches that supersedes (and block the application of) my_db_patch
862 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
864 FROM config.db_patch_dependencies
865 WHERE ARRAY[$1]::TEXT[] && supersedes
868 -- Make sure that no deprecated or superseded db patches are currently applied
869 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
871 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
873 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
875 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
877 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
880 -- Raise an exception if there are, in fact, dep/sup conflict
881 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
886 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
887 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
888 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
890 Upgrade script % can not be applied:
891 applied deprecated scripts %
892 applied superseded scripts %
896 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
897 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
898 evergreen.upgrade_list_applied_deprecated(my_db_patch),
899 evergreen.upgrade_list_applied_superseded(my_db_patch);
902 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
907 CREATE TABLE config.barcode_completion (
908 id SERIAL PRIMARY KEY,
909 active BOOL NOT NULL DEFAULT true,
910 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
913 length INT NOT NULL DEFAULT 0,
915 padding_end BOOL NOT NULL DEFAULT false,
916 asset BOOL NOT NULL DEFAULT true,
917 actor BOOL NOT NULL DEFAULT true
920 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
922 -- Add support for logging, only keep the most recent five rows for each category.
925 CREATE TABLE config.org_unit_setting_type_log (
926 id BIGSERIAL PRIMARY KEY,
927 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
928 org INT, --REFERENCES actor.org_unit (id),
931 field_name TEXT REFERENCES config.org_unit_setting_type (name)
934 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
935 Org Unit setting Logs
937 This table contains the most recent changes to each setting
938 in actor.org_unit_setting, allowing for mistakes to be undone.
939 This is NOT meant to be an auditor, but rather an undo/redo.
942 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
944 -- Only keeps the most recent five settings changes.
945 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
946 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
948 IF (TG_OP = 'UPDATE') THEN
950 ELSIF (TG_OP = 'INSERT') THEN
955 $oustl_limit$ LANGUAGE plpgsql;
957 CREATE TRIGGER limit_logs_oust
958 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
959 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
961 CREATE TABLE config.sms_carrier (
962 id SERIAL PRIMARY KEY,
966 active BOOLEAN DEFAULT TRUE
969 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
971 CREATE TABLE config.usr_activity_type (
972 id SERIAL PRIMARY KEY,
976 label TEXT NOT NULL, -- i18n
977 egroup config.usr_activity_group NOT NULL,
978 enabled BOOL NOT NULL DEFAULT TRUE,
979 transient BOOL NOT NULL DEFAULT FALSE,
980 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
983 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
984 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
986 CREATE TABLE config.filter_dialog_interface (
987 key TEXT PRIMARY KEY,
991 CREATE TABLE config.filter_dialog_filter_set (
992 id SERIAL PRIMARY KEY,
994 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
995 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
996 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
997 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
998 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
999 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1002 CREATE TABLE config.best_hold_order(
1003 id SERIAL PRIMARY KEY,
1004 name TEXT UNIQUE, -- i18n
1005 pprox INT, -- copy capture <-> pickup lib prox
1006 hprox INT, -- copy circ lib <-> request lib prox
1007 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1008 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1009 priority INT, -- group hold priority
1010 cut INT, -- cut-in-line
1011 depth INT, -- selection depth
1012 htime INT, -- time since last home-lib circ exceeds org-unit setting
1013 rtime INT, -- request time
1014 shtime INT -- time since copy last trip home exceeds org-unit setting
1017 -- At least one of these columns must contain a non-null value
1018 ALTER TABLE config.best_hold_order ADD CHECK ((
1019 pprox IS NOT NULL OR
1020 hprox IS NOT NULL OR
1021 aprox IS NOT NULL OR
1022 priority IS NOT NULL OR
1024 depth IS NOT NULL OR
1025 htime IS NOT NULL OR