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 ('0787', :eg_version); -- callender/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) ON UPDATE CASCADE ON DELETE CASCADE 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.z3950_source_credentials (
518 id SERIAL PRIMARY KEY,
519 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
520 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
523 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
526 CREATE TABLE config.i18n_locale (
527 code TEXT PRIMARY KEY,
528 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
529 name TEXT UNIQUE NOT NULL,
533 CREATE TABLE config.i18n_core (
534 id BIGSERIAL PRIMARY KEY,
535 fq_field TEXT NOT NULL,
536 identity_value TEXT NOT NULL,
537 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
541 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
543 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
547 UPDATE config.i18n_core
548 SET identity_value = $$ || quote_literal(new_ident) || $$
549 WHERE fq_field LIKE '$$ || hint || $$.%'
550 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
555 $_$ LANGUAGE PLPGSQL;
557 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
559 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
562 $_$ LANGUAGE PLPGSQL;
564 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
566 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
569 $_$ LANGUAGE PLPGSQL;
571 CREATE TABLE config.billing_type (
572 id SERIAL PRIMARY KEY,
574 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
575 default_price NUMERIC(6,2),
576 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
579 CREATE TABLE config.settings_group (
580 name TEXT PRIMARY KEY,
581 label TEXT UNIQUE NOT NULL -- I18N
584 CREATE TABLE config.org_unit_setting_type (
585 name TEXT PRIMARY KEY,
586 label TEXT UNIQUE NOT NULL,
587 grp TEXT REFERENCES config.settings_group (name),
589 datatype TEXT NOT NULL DEFAULT 'string',
594 -- define valid datatypes
596 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
597 ( 'bool', 'integer', 'float', 'currency', 'interval',
598 'date', 'string', 'object', 'array', 'link' ) ),
600 -- fm_class is meaningful only for 'link' datatype
602 CONSTRAINT coust_no_empty_link CHECK
603 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
604 ( datatype <> 'link' AND fm_class IS NULL ) )
607 CREATE TABLE config.usr_setting_type (
609 name TEXT PRIMARY KEY,
610 opac_visible BOOL NOT NULL DEFAULT FALSE,
611 label TEXT UNIQUE NOT NULL,
613 grp TEXT REFERENCES config.settings_group (name),
614 datatype TEXT NOT NULL DEFAULT 'string',
618 -- define valid datatypes
620 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
621 ( 'bool', 'integer', 'float', 'currency', 'interval',
622 'date', 'string', 'object', 'array', 'link' ) ),
625 -- fm_class is meaningful only for 'link' datatype
627 CONSTRAINT coust_no_empty_link CHECK
628 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
629 ( datatype <> 'link' AND fm_class IS NULL ) )
633 -- Some handy functions, based on existing ones, to provide optional ingest normalization
635 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
636 SELECT SUBSTRING($1,$2);
637 $func$ LANGUAGE SQL STRICT IMMUTABLE;
639 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
640 SELECT SUBSTRING($1,1,$2);
641 $func$ LANGUAGE SQL STRICT IMMUTABLE;
643 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
644 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
645 $func$ LANGUAGE SQL STRICT IMMUTABLE;
647 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
648 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
649 $func$ LANGUAGE SQL STRICT IMMUTABLE;
651 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
652 SELECT approximate_date( $1, '0');
653 $func$ LANGUAGE SQL STRICT IMMUTABLE;
655 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
656 SELECT approximate_date( $1, '9');
657 $func$ LANGUAGE SQL STRICT IMMUTABLE;
659 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
660 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
661 $func$ LANGUAGE SQL STRICT IMMUTABLE;
663 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
664 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
665 $func$ LANGUAGE SQL STRICT IMMUTABLE;
667 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
672 # Find the first ISBN, force it to ISBN13 and return it
676 foreach my $word (split(/\s/, $input)) {
677 my $isbn = Business::ISBN->new($word);
679 # First check the checksum; if it is not valid, fix it and add the original
680 # bad-checksum ISBN to the output
681 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
682 $isbn->fix_checksum();
685 # If we now have a valid ISBN, force it to ISBN13 and return it
686 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
689 $func$ LANGUAGE PLPERLU;
691 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
692 Inspired by translate_isbn1013
694 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
695 version without hypens and with a repaired checksum if the checksum was bad
699 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
704 # For each ISBN found in a single string containing a set of ISBNs:
705 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
706 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
711 foreach my $word (split(/\s/, $input)) {
712 my $isbn = Business::ISBN->new($word);
714 # First check the checksum; if it is not valid, fix it and add the original
715 # bad-checksum ISBN to the output
716 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
717 $output .= $isbn->isbn() . " ";
718 $isbn->fix_checksum();
721 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
722 # and add the normalized original ISBN to the output
723 if ($isbn && $isbn->is_valid()) {
724 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
725 $output .= $isbn->isbn . " ";
727 # If we successfully converted the ISBN to its counterpart, add the
728 # converted ISBN to the output as well
729 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
732 return $output if $output;
734 # If there were no valid ISBNs, just return the raw input
736 $func$ LANGUAGE PLPERLU;
738 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
739 The translate_isbn1013 function takes an input ISBN and returns the
740 following in a single space-delimited string if the input ISBN is valid:
741 - The normalized input ISBN (hyphens stripped)
742 - The normalized input ISBN with a fixed checksum if the checksum was bad
743 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
746 -- And ... a table in which to register them
748 CREATE TABLE config.index_normalizer (
749 id SERIAL PRIMARY KEY,
750 name TEXT UNIQUE NOT NULL,
753 param_count INT NOT NULL DEFAULT 0
756 CREATE TABLE config.metabib_field_index_norm_map (
757 id SERIAL PRIMARY KEY,
758 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
759 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
761 pos INT NOT NULL DEFAULT 0
764 CREATE TABLE config.record_attr_definition (
765 name TEXT PRIMARY KEY,
766 label TEXT NOT NULL, -- I18N
768 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
769 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
771 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
772 tag TEXT, -- LIKE format
773 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
775 -- This is used for both tag/sf and xpath entries
778 -- For xpath-extracted attrs
780 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
785 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
787 -- For phys-char fields
788 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
791 CREATE TABLE config.record_attr_index_norm_map (
792 id SERIAL PRIMARY KEY,
793 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
794 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
796 pos INT NOT NULL DEFAULT 0
799 CREATE TABLE config.coded_value_map (
800 id SERIAL PRIMARY KEY,
801 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
805 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
807 is_simple BOOL NOT NULL DEFAULT FALSE
810 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
811 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
812 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
813 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
814 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
815 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
816 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
818 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$
820 current_row config.coded_value_map%ROWTYPE;
822 -- Look for a current value
823 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
825 IF FOUND AND NOT add_only THEN
826 -- Update anything we were handed
827 current_row.value := COALESCE(current_row.value, in_value);
828 current_row.description := COALESCE(current_row.description, in_description);
829 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
830 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
831 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
832 UPDATE config.coded_value_map
834 value = current_row.value,
835 description = current_row.description,
836 opac_visible = current_row.opac_visible,
837 search_label = current_row.search_label,
838 is_simple = current_row.is_simple
839 WHERE id = current_row.id;
841 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
842 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
845 $f$ LANGUAGE PLPGSQL;
847 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
848 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
849 SELECT DISTINCT l.version
850 FROM config.upgrade_log l
851 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
852 WHERE d.db_patch = $1
855 -- List applied db patches that are superseded by (and block the application of) my_db_patch
856 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
857 SELECT DISTINCT l.version
858 FROM config.upgrade_log l
859 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
860 WHERE d.db_patch = $1
863 -- List applied db patches that deprecates (and block the application of) my_db_patch
864 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
866 FROM config.db_patch_dependencies
867 WHERE ARRAY[$1]::TEXT[] && deprecates
870 -- List applied db patches that supersedes (and block the application of) my_db_patch
871 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
873 FROM config.db_patch_dependencies
874 WHERE ARRAY[$1]::TEXT[] && supersedes
877 -- Make sure that no deprecated or superseded db patches are currently applied
878 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
880 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
882 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
884 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
886 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
889 -- Raise an exception if there are, in fact, dep/sup conflict
890 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
895 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
896 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
897 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
899 Upgrade script % can not be applied:
900 applied deprecated scripts %
901 applied superseded scripts %
905 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
906 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
907 evergreen.upgrade_list_applied_deprecated(my_db_patch),
908 evergreen.upgrade_list_applied_superseded(my_db_patch);
911 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
916 CREATE TABLE config.barcode_completion (
917 id SERIAL PRIMARY KEY,
918 active BOOL NOT NULL DEFAULT true,
919 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
922 length INT NOT NULL DEFAULT 0,
924 padding_end BOOL NOT NULL DEFAULT false,
925 asset BOOL NOT NULL DEFAULT true,
926 actor BOOL NOT NULL DEFAULT true
929 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
931 -- Add support for logging, only keep the most recent five rows for each category.
934 CREATE TABLE config.org_unit_setting_type_log (
935 id BIGSERIAL PRIMARY KEY,
936 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
937 org INT, --REFERENCES actor.org_unit (id),
940 field_name TEXT REFERENCES config.org_unit_setting_type (name)
943 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
944 Org Unit setting Logs
946 This table contains the most recent changes to each setting
947 in actor.org_unit_setting, allowing for mistakes to be undone.
948 This is NOT meant to be an auditor, but rather an undo/redo.
951 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
953 -- Only keeps the most recent five settings changes.
954 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
955 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
957 IF (TG_OP = 'UPDATE') THEN
959 ELSIF (TG_OP = 'INSERT') THEN
964 $oustl_limit$ LANGUAGE plpgsql;
966 CREATE TRIGGER limit_logs_oust
967 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
968 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
970 CREATE TABLE config.sms_carrier (
971 id SERIAL PRIMARY KEY,
975 active BOOLEAN DEFAULT TRUE
978 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
980 CREATE TABLE config.usr_activity_type (
981 id SERIAL PRIMARY KEY,
985 label TEXT NOT NULL, -- i18n
986 egroup config.usr_activity_group NOT NULL,
987 enabled BOOL NOT NULL DEFAULT TRUE,
988 transient BOOL NOT NULL DEFAULT FALSE,
989 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
992 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
993 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
995 CREATE TABLE config.filter_dialog_interface (
996 key TEXT PRIMARY KEY,
1000 CREATE TABLE config.filter_dialog_filter_set (
1001 id SERIAL PRIMARY KEY,
1003 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1004 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1005 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1006 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1007 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1008 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1011 CREATE TABLE config.best_hold_order(
1012 id SERIAL PRIMARY KEY,
1013 name TEXT UNIQUE, -- i18n
1014 pprox INT, -- copy capture <-> pickup lib prox
1015 hprox INT, -- copy circ lib <-> request lib prox
1016 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1017 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1018 priority INT, -- group hold priority
1019 cut INT, -- cut-in-line
1020 depth INT, -- selection depth
1021 htime INT, -- time since last home-lib circ exceeds org-unit setting
1022 rtime INT, -- request time
1023 shtime INT -- time since copy last trip home exceeds org-unit setting
1026 -- At least one of these columns must contain a non-null value
1027 ALTER TABLE config.best_hold_order ADD CHECK ((
1028 pprox IS NOT NULL OR
1029 hprox IS NOT NULL OR
1030 aprox IS NOT NULL OR
1031 priority IS NOT NULL OR
1033 depth IS NOT NULL OR
1034 htime IS NOT NULL OR