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 ('0811', :eg_version); -- fparks/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 combined BOOL DEFAULT FALSE NOT NULL,
178 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
179 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
180 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
181 d_weight NUMERIC DEFAULT 0.1 NOT NULL
184 CREATE TABLE config.metabib_field (
185 id SERIAL PRIMARY KEY,
186 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
190 weight INT NOT NULL DEFAULT 1,
191 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
192 search_field BOOL NOT NULL DEFAULT TRUE,
193 facet_field BOOL NOT NULL DEFAULT FALSE,
194 browse_field BOOL NOT NULL DEFAULT TRUE,
197 restrict BOOL DEFAULT FALSE NOT NULL
199 COMMENT ON TABLE config.metabib_field IS $$
200 XPath used for record indexing ingest
202 This table contains the XPath used to chop up MODS into its
203 indexable parts. Each XPath entry is named and assigned to
204 a "class" of either title, subject, author, keyword, series
208 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
210 CREATE TABLE config.ts_config_list (
214 COMMENT ON TABLE config.ts_config_list IS $$
217 A list of full text configs with names and descriptions.
220 CREATE TABLE config.metabib_class_ts_map (
221 id SERIAL PRIMARY KEY,
222 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
223 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
224 active BOOL NOT NULL DEFAULT TRUE,
225 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
226 index_lang TEXT NULL,
227 search_lang TEXT NULL,
228 always BOOL NOT NULL DEFAULT true
230 COMMENT ON TABLE config.metabib_class_ts_map IS $$
231 Text Search Configs for metabib class indexing
233 This table contains text search config definitions for
234 storing index_vector values.
237 CREATE TABLE config.metabib_field_ts_map (
238 id SERIAL PRIMARY KEY,
239 metabib_field INT NOT NULL REFERENCES config.metabib_field (id),
240 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
241 active BOOL NOT NULL DEFAULT TRUE,
242 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
243 index_lang TEXT NULL,
244 search_lang TEXT NULL
246 COMMENT ON TABLE config.metabib_field_ts_map IS $$
247 Text Search Configs for metabib field indexing
249 This table contains text search config definitions for
250 storing index_vector values.
253 CREATE TABLE config.metabib_search_alias (
254 alias TEXT PRIMARY KEY,
255 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
256 field INT REFERENCES config.metabib_field (id)
259 CREATE TABLE config.non_cataloged_type (
260 id SERIAL PRIMARY KEY,
261 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
263 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
264 in_house BOOL NOT NULL DEFAULT FALSE,
265 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
267 COMMENT ON TABLE config.non_cataloged_type IS $$
268 Types of valid non-cataloged items.
271 CREATE TABLE config.identification_type (
272 id SERIAL PRIMARY KEY,
273 name TEXT NOT NULL UNIQUE
275 COMMENT ON TABLE config.identification_type IS $$
276 Types of valid patron identification.
278 Each patron must display at least one valid form of identification
279 in order to get a library card. This table lists those forms.
282 CREATE TABLE config.rule_circ_duration (
283 id SERIAL PRIMARY KEY,
284 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
285 extended INTERVAL NOT NULL,
286 normal INTERVAL NOT NULL,
287 shrt INTERVAL NOT NULL,
288 max_renewals INT NOT NULL
290 COMMENT ON TABLE config.rule_circ_duration IS $$
291 Circulation Duration rules
293 Each circulation is given a duration based on one of these rules.
296 CREATE TABLE config.hard_due_date (
297 id SERIAL PRIMARY KEY,
298 name TEXT NOT NULL UNIQUE,
299 ceiling_date TIMESTAMPTZ NOT NULL,
300 forceto BOOL NOT NULL,
301 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
304 CREATE TABLE config.hard_due_date_values (
305 id SERIAL PRIMARY KEY,
306 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
307 DEFERRABLE INITIALLY DEFERRED,
308 ceiling_date TIMESTAMPTZ NOT NULL,
309 active_date TIMESTAMPTZ NOT NULL
312 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
314 temp_value config.hard_due_date_values%ROWTYPE;
318 SELECT DISTINCT ON (hard_due_date) *
319 FROM config.hard_due_date_values
320 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
321 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
323 UPDATE config.hard_due_date
324 SET ceiling_date = temp_value.ceiling_date
325 WHERE id = temp_value.hard_due_date
326 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
329 updated := updated + 1;
335 $func$ LANGUAGE plpgsql;
337 CREATE TABLE config.rule_max_fine (
338 id SERIAL PRIMARY KEY,
339 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
340 amount NUMERIC(6,2) NOT NULL,
341 is_percent BOOL NOT NULL DEFAULT FALSE
343 COMMENT ON TABLE config.rule_max_fine IS $$
344 Circulation Max Fine rules
346 Each circulation is given a maximum fine based on one of
350 CREATE TABLE config.rule_recurring_fine (
351 id SERIAL PRIMARY KEY,
352 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
353 high NUMERIC(6,2) NOT NULL,
354 normal NUMERIC(6,2) NOT NULL,
355 low NUMERIC(6,2) NOT NULL,
356 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
357 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
359 COMMENT ON TABLE config.rule_recurring_fine IS $$
360 Circulation Recurring Fine rules
362 Each circulation is given a recurring fine amount based on one of
363 these rules. Note that it is recommended to run the fine generator
364 (from cron) at least as frequently as the lowest recurrence interval
365 used by your circulation rules so that accrued fines will be up
370 CREATE TABLE config.rule_age_hold_protect (
371 id SERIAL PRIMARY KEY,
372 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
373 age INTERVAL NOT NULL,
376 COMMENT ON TABLE config.rule_age_hold_protect IS $$
377 Hold Item Age Protection rules
379 A hold request can only capture new(ish) items when they are
380 within a particular proximity of the pickup_lib of the request.
381 The proximity ('prox' column) is calculated by counting
382 the number of tree edges between the pickup_lib and either the
383 owning_lib or circ_lib of the copy that could fulfill the hold,
384 as determined by the distance_is_from_owner value of the hold matrix
385 rule controlling the hold request.
388 CREATE TABLE config.copy_status (
389 id SERIAL PRIMARY KEY,
390 name TEXT NOT NULL UNIQUE,
391 holdable BOOL NOT NULL DEFAULT FALSE,
392 opac_visible BOOL NOT NULL DEFAULT FALSE,
393 copy_active BOOL NOT NULL DEFAULT FALSE,
394 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
396 COMMENT ON TABLE config.copy_status IS $$
399 The available copy statuses, and whether a copy in that
400 status is available for hold request capture. 0 (zero) is
401 the only special number in this set, meaning that the item
402 is available for immediate checkout, and is counted as available
405 Statuses with an ID below 100 are not removable, and have special
406 meaning in the code. Do not change them except to translate the
409 You may add and remove statuses above 100, and these can be used
410 to remove items from normal circulation without affecting the rest
411 of the copy's values or its location.
414 CREATE TABLE config.net_access_level (
415 id SERIAL PRIMARY KEY,
416 name TEXT NOT NULL UNIQUE
418 COMMENT ON TABLE config.net_access_level IS $$
419 Patron Network Access level
421 This will be used to inform the in-library firewall of how much
422 internet access the using patron should be allowed.
426 CREATE TABLE config.remote_account (
427 id SERIAL PRIMARY KEY,
429 host TEXT NOT NULL, -- name or IP, :port optional
430 username TEXT, -- optional, since we could default to $USER
431 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
432 account TEXT, -- aka profile or FTP "account" command
433 path TEXT, -- aka directory
434 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
435 last_activity TIMESTAMP WITH TIME ZONE
438 CREATE TABLE config.marc21_rec_type_map (
439 code TEXT PRIMARY KEY,
440 type_val TEXT NOT NULL,
441 blvl_val TEXT NOT NULL
444 CREATE TABLE config.marc21_ff_pos_map (
445 id SERIAL PRIMARY KEY,
446 fixed_field TEXT NOT NULL,
448 rec_type TEXT NOT NULL,
449 start_pos INT NOT NULL,
451 default_val TEXT NOT NULL DEFAULT ' '
454 CREATE TABLE config.marc21_physical_characteristic_type_map (
455 ptype_key TEXT PRIMARY KEY,
456 label TEXT NOT NULL -- I18N
459 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
460 id SERIAL PRIMARY KEY,
461 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
462 subfield TEXT NOT NULL,
463 start_pos INT NOT NULL,
465 label TEXT NOT NULL -- I18N
468 CREATE TABLE config.marc21_physical_characteristic_value_map (
469 id SERIAL PRIMARY KEY,
471 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
472 label TEXT NOT NULL -- I18N
476 CREATE TABLE config.z3950_source (
477 name TEXT PRIMARY KEY,
478 label TEXT NOT NULL UNIQUE,
482 record_format TEXT NOT NULL DEFAULT 'FI',
483 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
484 auth BOOL NOT NULL DEFAULT TRUE,
485 use_perm INT -- REFERENCES permission.perm_list (id)
488 COMMENT ON TABLE config.z3950_source IS $$
491 Each row in this table represents a database searchable via Z39.50.
494 COMMENT ON COLUMN config.z3950_source.record_format IS $$
498 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
499 Z39.50 preferred record syntax..
502 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
503 If set, this permission is required for the source to be listed in the staff
504 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
507 CREATE TABLE config.z3950_attr (
508 id SERIAL PRIMARY KEY,
509 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
514 truncation INT NOT NULL DEFAULT 0,
515 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
518 CREATE TABLE config.z3950_source_credentials (
519 id SERIAL PRIMARY KEY,
520 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
521 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
524 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
527 CREATE TABLE config.i18n_locale (
528 code TEXT PRIMARY KEY,
529 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
530 name TEXT UNIQUE NOT NULL,
534 CREATE TABLE config.i18n_core (
535 id BIGSERIAL PRIMARY KEY,
536 fq_field TEXT NOT NULL,
537 identity_value TEXT NOT NULL,
538 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
542 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
544 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
548 UPDATE config.i18n_core
549 SET identity_value = $$ || quote_literal(new_ident) || $$
550 WHERE fq_field LIKE '$$ || hint || $$.%'
551 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
556 $_$ LANGUAGE PLPGSQL;
558 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
560 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
563 $_$ LANGUAGE PLPGSQL;
565 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
567 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
570 $_$ LANGUAGE PLPGSQL;
572 CREATE TABLE config.billing_type (
573 id SERIAL PRIMARY KEY,
575 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
576 default_price NUMERIC(6,2),
577 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
580 CREATE TABLE config.settings_group (
581 name TEXT PRIMARY KEY,
582 label TEXT UNIQUE NOT NULL -- I18N
585 CREATE TABLE config.org_unit_setting_type (
586 name TEXT PRIMARY KEY,
587 label TEXT UNIQUE NOT NULL,
588 grp TEXT REFERENCES config.settings_group (name),
590 datatype TEXT NOT NULL DEFAULT 'string',
595 -- define valid datatypes
597 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
598 ( 'bool', 'integer', 'float', 'currency', 'interval',
599 'date', 'string', 'object', 'array', 'link' ) ),
601 -- fm_class is meaningful only for 'link' datatype
603 CONSTRAINT coust_no_empty_link CHECK
604 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
605 ( datatype <> 'link' AND fm_class IS NULL ) )
608 CREATE TABLE config.usr_setting_type (
610 name TEXT PRIMARY KEY,
611 opac_visible BOOL NOT NULL DEFAULT FALSE,
612 label TEXT UNIQUE NOT NULL,
614 grp TEXT REFERENCES config.settings_group (name),
615 datatype TEXT NOT NULL DEFAULT 'string',
620 -- define valid datatypes
622 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
623 ( 'bool', 'integer', 'float', 'currency', 'interval',
624 'date', 'string', 'object', 'array', 'link' ) ),
627 -- fm_class is meaningful only for 'link' datatype
629 CONSTRAINT coust_no_empty_link CHECK
630 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
631 ( datatype <> 'link' AND fm_class IS NULL ) )
635 -- Some handy functions, based on existing ones, to provide optional ingest normalization
637 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
638 SELECT SUBSTRING($1,$2);
639 $func$ LANGUAGE SQL STRICT IMMUTABLE;
641 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
642 SELECT SUBSTRING($1,1,$2);
643 $func$ LANGUAGE SQL STRICT IMMUTABLE;
645 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
646 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
647 $func$ LANGUAGE SQL STRICT IMMUTABLE;
649 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
650 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
651 $func$ LANGUAGE SQL STRICT IMMUTABLE;
653 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
654 SELECT approximate_date( $1, '0');
655 $func$ LANGUAGE SQL STRICT IMMUTABLE;
657 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
658 SELECT approximate_date( $1, '9');
659 $func$ LANGUAGE SQL STRICT IMMUTABLE;
661 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
662 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
663 $func$ LANGUAGE SQL STRICT IMMUTABLE;
665 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
666 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
667 $func$ LANGUAGE SQL STRICT IMMUTABLE;
669 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
674 # Find the first ISBN, force it to ISBN13 and return it
678 foreach my $word (split(/\s/, $input)) {
679 my $isbn = Business::ISBN->new($word);
681 # First check the checksum; if it is not valid, fix it and add the original
682 # bad-checksum ISBN to the output
683 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
684 $isbn->fix_checksum();
687 # If we now have a valid ISBN, force it to ISBN13 and return it
688 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
691 $func$ LANGUAGE PLPERLU;
693 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
694 Inspired by translate_isbn1013
696 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
697 version without hypens and with a repaired checksum if the checksum was bad
701 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
706 # For each ISBN found in a single string containing a set of ISBNs:
707 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
708 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
713 foreach my $word (split(/\s/, $input)) {
714 my $isbn = Business::ISBN->new($word);
716 # First check the checksum; if it is not valid, fix it and add the original
717 # bad-checksum ISBN to the output
718 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
719 $output .= $isbn->isbn() . " ";
720 $isbn->fix_checksum();
723 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
724 # and add the normalized original ISBN to the output
725 if ($isbn && $isbn->is_valid()) {
726 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
727 $output .= $isbn->isbn . " ";
729 # If we successfully converted the ISBN to its counterpart, add the
730 # converted ISBN to the output as well
731 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
734 return $output if $output;
736 # If there were no valid ISBNs, just return the raw input
738 $func$ LANGUAGE PLPERLU;
740 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
741 The translate_isbn1013 function takes an input ISBN and returns the
742 following in a single space-delimited string if the input ISBN is valid:
743 - The normalized input ISBN (hyphens stripped)
744 - The normalized input ISBN with a fixed checksum if the checksum was bad
745 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
748 -- And ... a table in which to register them
750 CREATE TABLE config.index_normalizer (
751 id SERIAL PRIMARY KEY,
752 name TEXT UNIQUE NOT NULL,
755 param_count INT NOT NULL DEFAULT 0
758 CREATE TABLE config.metabib_field_index_norm_map (
759 id SERIAL PRIMARY KEY,
760 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
761 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
763 pos INT NOT NULL DEFAULT 0
766 CREATE TABLE config.record_attr_definition (
767 name TEXT PRIMARY KEY,
768 label TEXT NOT NULL, -- I18N
770 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
771 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
773 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
774 tag TEXT, -- LIKE format
775 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
777 -- This is used for both tag/sf and xpath entries
780 -- For xpath-extracted attrs
782 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
787 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
789 -- For phys-char fields
790 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
793 CREATE TABLE config.record_attr_index_norm_map (
794 id SERIAL PRIMARY KEY,
795 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
796 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
798 pos INT NOT NULL DEFAULT 0
801 CREATE TABLE config.coded_value_map (
802 id SERIAL PRIMARY KEY,
803 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
807 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
809 is_simple BOOL NOT NULL DEFAULT FALSE
812 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
813 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
814 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
815 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
816 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
817 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
818 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
820 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$
822 current_row config.coded_value_map%ROWTYPE;
824 -- Look for a current value
825 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
827 IF FOUND AND NOT add_only THEN
828 -- Update anything we were handed
829 current_row.value := COALESCE(current_row.value, in_value);
830 current_row.description := COALESCE(current_row.description, in_description);
831 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
832 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
833 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
834 UPDATE config.coded_value_map
836 value = current_row.value,
837 description = current_row.description,
838 opac_visible = current_row.opac_visible,
839 search_label = current_row.search_label,
840 is_simple = current_row.is_simple
841 WHERE id = current_row.id;
843 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
844 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
847 $f$ LANGUAGE PLPGSQL;
849 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
850 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
851 SELECT DISTINCT l.version
852 FROM config.upgrade_log l
853 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
854 WHERE d.db_patch = $1
857 -- List applied db patches that are superseded by (and block the application of) my_db_patch
858 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
859 SELECT DISTINCT l.version
860 FROM config.upgrade_log l
861 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
862 WHERE d.db_patch = $1
865 -- List applied db patches that deprecates (and block the application of) my_db_patch
866 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
868 FROM config.db_patch_dependencies
869 WHERE ARRAY[$1]::TEXT[] && deprecates
872 -- List applied db patches that supersedes (and block the application of) my_db_patch
873 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
875 FROM config.db_patch_dependencies
876 WHERE ARRAY[$1]::TEXT[] && supersedes
879 -- Make sure that no deprecated or superseded db patches are currently applied
880 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
882 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
884 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
886 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
888 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
891 -- Raise an exception if there are, in fact, dep/sup conflict
892 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
897 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
898 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
899 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
901 Upgrade script % can not be applied:
902 applied deprecated scripts %
903 applied superseded scripts %
907 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
908 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
909 evergreen.upgrade_list_applied_deprecated(my_db_patch),
910 evergreen.upgrade_list_applied_superseded(my_db_patch);
913 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
918 CREATE TABLE config.barcode_completion (
919 id SERIAL PRIMARY KEY,
920 active BOOL NOT NULL DEFAULT true,
921 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
924 length INT NOT NULL DEFAULT 0,
926 padding_end BOOL NOT NULL DEFAULT false,
927 asset BOOL NOT NULL DEFAULT true,
928 actor BOOL NOT NULL DEFAULT true
931 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
933 -- Add support for logging, only keep the most recent five rows for each category.
936 CREATE TABLE config.org_unit_setting_type_log (
937 id BIGSERIAL PRIMARY KEY,
938 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
939 org INT, --REFERENCES actor.org_unit (id),
942 field_name TEXT REFERENCES config.org_unit_setting_type (name)
945 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
946 Org Unit setting Logs
948 This table contains the most recent changes to each setting
949 in actor.org_unit_setting, allowing for mistakes to be undone.
950 This is NOT meant to be an auditor, but rather an undo/redo.
953 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
955 -- Only keeps the most recent five settings changes.
956 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
957 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
959 IF (TG_OP = 'UPDATE') THEN
961 ELSIF (TG_OP = 'INSERT') THEN
966 $oustl_limit$ LANGUAGE plpgsql;
968 CREATE TRIGGER limit_logs_oust
969 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
970 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
972 CREATE TABLE config.sms_carrier (
973 id SERIAL PRIMARY KEY,
977 active BOOLEAN DEFAULT TRUE
980 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
982 CREATE TABLE config.usr_activity_type (
983 id SERIAL PRIMARY KEY,
987 label TEXT NOT NULL, -- i18n
988 egroup config.usr_activity_group NOT NULL,
989 enabled BOOL NOT NULL DEFAULT TRUE,
990 transient BOOL NOT NULL DEFAULT FALSE,
991 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
994 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
995 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
997 CREATE TABLE config.filter_dialog_interface (
998 key TEXT PRIMARY KEY,
1002 CREATE TABLE config.filter_dialog_filter_set (
1003 id SERIAL PRIMARY KEY,
1005 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1006 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1007 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1008 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1009 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1010 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1013 CREATE TABLE config.best_hold_order(
1014 id SERIAL PRIMARY KEY,
1015 name TEXT UNIQUE, -- i18n
1016 pprox INT, -- copy capture <-> pickup lib prox
1017 hprox INT, -- copy circ lib <-> request lib prox
1018 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1019 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1020 priority INT, -- group hold priority
1021 cut INT, -- cut-in-line
1022 depth INT, -- selection depth
1023 htime INT, -- time since last home-lib circ exceeds org-unit setting
1024 rtime INT, -- request time
1025 shtime INT -- time since copy last trip home exceeds org-unit setting
1028 -- At least one of these columns must contain a non-null value
1029 ALTER TABLE config.best_hold_order ADD CHECK ((
1030 pprox IS NOT NULL OR
1031 hprox IS NOT NULL OR
1032 aprox IS NOT NULL OR
1033 priority IS NOT NULL OR
1035 depth IS NOT NULL OR
1036 htime IS NOT NULL OR
1040 CREATE OR REPLACE FUNCTION
1041 evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$
1042 SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1);
1043 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1045 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$
1046 Results in TRUE if there exists at least one config.z3950_attr
1047 with the provided name. Used by config.z3950_index_field_map
1048 to verify z3950_attr_type maps.
1051 -- drop these in down here since they reference config.metabib_field
1052 -- and config.record_attr_definition
1053 CREATE TABLE config.z3950_index_field_map (
1054 id SERIAL PRIMARY KEY,
1055 label TEXT NOT NULL, -- i18n
1056 metabib_field INTEGER REFERENCES config.metabib_field(id),
1057 record_attr TEXT REFERENCES config.record_attr_definition(name),
1058 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1059 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1060 CONSTRAINT metabib_field_or_record_attr CHECK (
1061 metabib_field IS NOT NULL OR
1062 record_attr IS NOT NULL
1064 CONSTRAINT attr_or_attr_type CHECK (
1065 z3950_attr IS NOT NULL OR
1066 z3950_attr_type IS NOT NULL
1068 -- ensure the selected z3950_attr_type refers to a valid attr name
1069 CONSTRAINT valid_z3950_attr_type CHECK (
1070 z3950_attr_type IS NULL OR
1071 evergreen.z3950_attr_name_is_valid(z3950_attr_type)