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 ('0897', :eg_version); -- csharp/miker/bshum
96 CREATE TABLE config.bib_source (
97 id SERIAL PRIMARY KEY,
98 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
99 source TEXT NOT NULL UNIQUE,
100 transcendant BOOL NOT NULL DEFAULT FALSE,
101 can_have_copies BOOL NOT NULL DEFAULT TRUE
103 COMMENT ON TABLE config.bib_source IS $$
104 This is table is used to set up the relative "quality" of each
105 MARC source, such as OCLC. Also identifies "transcendant" sources,
106 i.e., sources of bib records that should display in the OPAC
107 even if no copies or located URIs are attached. Also indicates if
108 the source is allowed to have actual copies on its bibs. Volumes
109 for targeted URIs are unaffected by this setting.
112 CREATE TABLE config.standing (
113 id SERIAL PRIMARY KEY,
114 value TEXT NOT NULL UNIQUE
116 COMMENT ON TABLE config.standing IS $$
119 This table contains the values that can be applied to a patron
120 by a staff member. These values should not be changed, other
121 than for translation, as the ID column is currently a "magic
122 number" in the source. :(
125 CREATE TABLE config.standing_penalty (
126 id SERIAL PRIMARY KEY,
127 name TEXT NOT NULL UNIQUE,
130 staff_alert BOOL NOT NULL DEFAULT FALSE,
134 CREATE TABLE config.xml_transform (
135 name TEXT PRIMARY KEY,
136 namespace_uri TEXT NOT NULL,
137 prefix TEXT NOT NULL,
141 CREATE TABLE config.biblio_fingerprint (
142 id SERIAL PRIMARY KEY,
145 first_word BOOL NOT NULL DEFAULT FALSE,
146 format TEXT NOT NULL DEFAULT 'marcxml'
149 INSERT INTO config.biblio_fingerprint (name, xpath, format)
152 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
153 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
154 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
155 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
160 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
163 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
164 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
165 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
172 CREATE TABLE config.metabib_class (
173 name TEXT PRIMARY KEY,
174 label TEXT NOT NULL UNIQUE,
175 buoyant BOOL DEFAULT FALSE NOT NULL,
176 restrict BOOL DEFAULT FALSE NOT NULL,
177 combined BOOL DEFAULT FALSE NOT NULL,
178 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
179 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
180 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
181 d_weight NUMERIC DEFAULT 0.1 NOT NULL
184 CREATE TABLE config.metabib_field (
185 id SERIAL PRIMARY KEY,
186 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
190 weight INT NOT NULL DEFAULT 1,
191 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
192 search_field BOOL NOT NULL DEFAULT TRUE,
193 facet_field BOOL NOT NULL DEFAULT FALSE,
194 browse_field BOOL NOT NULL DEFAULT TRUE,
196 browse_sort_xpath TEXT,
198 authority_xpath TEXT,
200 restrict BOOL DEFAULT FALSE NOT NULL
202 COMMENT ON TABLE config.metabib_field IS $$
203 XPath used for record indexing ingest
205 This table contains the XPath used to chop up MODS into its
206 indexable parts. Each XPath entry is named and assigned to
207 a "class" of either title, subject, author, keyword, series
211 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
213 CREATE TABLE config.ts_config_list (
217 COMMENT ON TABLE config.ts_config_list IS $$
220 A list of full text configs with names and descriptions.
223 CREATE TABLE config.metabib_class_ts_map (
224 id SERIAL PRIMARY KEY,
225 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
226 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
227 active BOOL NOT NULL DEFAULT TRUE,
228 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
229 index_lang TEXT NULL,
230 search_lang TEXT NULL,
231 always BOOL NOT NULL DEFAULT true
233 COMMENT ON TABLE config.metabib_class_ts_map IS $$
234 Text Search Configs for metabib class indexing
236 This table contains text search config definitions for
237 storing index_vector values.
240 CREATE TABLE config.metabib_field_ts_map (
241 id SERIAL PRIMARY KEY,
242 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
243 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
244 active BOOL NOT NULL DEFAULT TRUE,
245 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
246 index_lang TEXT NULL,
247 search_lang TEXT NULL
249 COMMENT ON TABLE config.metabib_field_ts_map IS $$
250 Text Search Configs for metabib field indexing
252 This table contains text search config definitions for
253 storing index_vector values.
256 CREATE TABLE config.metabib_search_alias (
257 alias TEXT PRIMARY KEY,
258 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
259 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
262 CREATE TABLE config.non_cataloged_type (
263 id SERIAL PRIMARY KEY,
264 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
266 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
267 in_house BOOL NOT NULL DEFAULT FALSE,
268 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
270 COMMENT ON TABLE config.non_cataloged_type IS $$
271 Types of valid non-cataloged items.
274 CREATE TABLE config.identification_type (
275 id SERIAL PRIMARY KEY,
276 name TEXT NOT NULL UNIQUE
278 COMMENT ON TABLE config.identification_type IS $$
279 Types of valid patron identification.
281 Each patron must display at least one valid form of identification
282 in order to get a library card. This table lists those forms.
285 CREATE TABLE config.rule_circ_duration (
286 id SERIAL PRIMARY KEY,
287 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
288 extended INTERVAL NOT NULL,
289 normal INTERVAL NOT NULL,
290 shrt INTERVAL NOT NULL,
291 max_renewals INT NOT NULL
293 COMMENT ON TABLE config.rule_circ_duration IS $$
294 Circulation Duration rules
296 Each circulation is given a duration based on one of these rules.
299 CREATE TABLE config.hard_due_date (
300 id SERIAL PRIMARY KEY,
301 name TEXT NOT NULL UNIQUE,
302 ceiling_date TIMESTAMPTZ NOT NULL,
303 forceto BOOL NOT NULL,
304 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
307 CREATE TABLE config.hard_due_date_values (
308 id SERIAL PRIMARY KEY,
309 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
310 DEFERRABLE INITIALLY DEFERRED,
311 ceiling_date TIMESTAMPTZ NOT NULL,
312 active_date TIMESTAMPTZ NOT NULL
315 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
317 temp_value config.hard_due_date_values%ROWTYPE;
321 SELECT DISTINCT ON (hard_due_date) *
322 FROM config.hard_due_date_values
323 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
324 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
326 UPDATE config.hard_due_date
327 SET ceiling_date = temp_value.ceiling_date
328 WHERE id = temp_value.hard_due_date
329 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
332 updated := updated + 1;
338 $func$ LANGUAGE plpgsql;
340 CREATE TABLE config.rule_max_fine (
341 id SERIAL PRIMARY KEY,
342 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
343 amount NUMERIC(6,2) NOT NULL,
344 is_percent BOOL NOT NULL DEFAULT FALSE
346 COMMENT ON TABLE config.rule_max_fine IS $$
347 Circulation Max Fine rules
349 Each circulation is given a maximum fine based on one of
353 CREATE TABLE config.rule_recurring_fine (
354 id SERIAL PRIMARY KEY,
355 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
356 high NUMERIC(6,2) NOT NULL,
357 normal NUMERIC(6,2) NOT NULL,
358 low NUMERIC(6,2) NOT NULL,
359 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
360 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
362 COMMENT ON TABLE config.rule_recurring_fine IS $$
363 Circulation Recurring Fine rules
365 Each circulation is given a recurring fine amount based on one of
366 these rules. Note that it is recommended to run the fine generator
367 (from cron) at least as frequently as the lowest recurrence interval
368 used by your circulation rules so that accrued fines will be up
373 CREATE TABLE config.rule_age_hold_protect (
374 id SERIAL PRIMARY KEY,
375 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
376 age INTERVAL NOT NULL,
379 COMMENT ON TABLE config.rule_age_hold_protect IS $$
380 Hold Item Age Protection rules
382 A hold request can only capture new(ish) items when they are
383 within a particular proximity of the pickup_lib of the request.
384 The proximity ('prox' column) is calculated by counting
385 the number of tree edges between the pickup_lib and either the
386 owning_lib or circ_lib of the copy that could fulfill the hold,
387 as determined by the distance_is_from_owner value of the hold matrix
388 rule controlling the hold request.
391 CREATE TABLE config.copy_status (
392 id SERIAL PRIMARY KEY,
393 name TEXT NOT NULL UNIQUE,
394 holdable BOOL NOT NULL DEFAULT FALSE,
395 opac_visible BOOL NOT NULL DEFAULT FALSE,
396 copy_active BOOL NOT NULL DEFAULT FALSE,
397 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
399 COMMENT ON TABLE config.copy_status IS $$
402 The available copy statuses, and whether a copy in that
403 status is available for hold request capture. 0 (zero) is
404 the only special number in this set, meaning that the item
405 is available for immediate checkout, and is counted as available
408 Statuses with an ID below 100 are not removable, and have special
409 meaning in the code. Do not change them except to translate the
412 You may add and remove statuses above 100, and these can be used
413 to remove items from normal circulation without affecting the rest
414 of the copy's values or its location.
417 CREATE TABLE config.net_access_level (
418 id SERIAL PRIMARY KEY,
419 name TEXT NOT NULL UNIQUE
421 COMMENT ON TABLE config.net_access_level IS $$
422 Patron Network Access level
424 This will be used to inform the in-library firewall of how much
425 internet access the using patron should be allowed.
429 CREATE TABLE config.remote_account (
430 id SERIAL PRIMARY KEY,
432 host TEXT NOT NULL, -- name or IP, :port optional
433 username TEXT, -- optional, since we could default to $USER
434 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
435 account TEXT, -- aka profile or FTP "account" command
436 path TEXT, -- aka directory
437 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
438 last_activity TIMESTAMP WITH TIME ZONE
441 CREATE TABLE config.marc21_rec_type_map (
442 code TEXT PRIMARY KEY,
443 type_val TEXT NOT NULL,
444 blvl_val TEXT NOT NULL
447 CREATE TABLE config.marc21_ff_pos_map (
448 id SERIAL PRIMARY KEY,
449 fixed_field TEXT NOT NULL,
451 rec_type TEXT NOT NULL,
452 start_pos INT NOT NULL,
454 default_val TEXT NOT NULL DEFAULT ' '
457 CREATE TABLE config.marc21_physical_characteristic_type_map (
458 ptype_key TEXT PRIMARY KEY,
459 label TEXT NOT NULL -- I18N
462 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
463 id SERIAL PRIMARY KEY,
464 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
465 subfield TEXT NOT NULL,
466 start_pos INT NOT NULL,
468 label TEXT NOT NULL -- I18N
471 CREATE TABLE config.marc21_physical_characteristic_value_map (
472 id SERIAL PRIMARY KEY,
474 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
475 label TEXT NOT NULL -- I18N
479 CREATE TABLE config.z3950_source (
480 name TEXT PRIMARY KEY,
481 label TEXT NOT NULL UNIQUE,
485 record_format TEXT NOT NULL DEFAULT 'FI',
486 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
487 auth BOOL NOT NULL DEFAULT TRUE,
488 use_perm INT -- REFERENCES permission.perm_list (id)
491 COMMENT ON TABLE config.z3950_source IS $$
494 Each row in this table represents a database searchable via Z39.50.
497 COMMENT ON COLUMN config.z3950_source.record_format IS $$
501 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
502 Z39.50 preferred record syntax..
505 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
506 If set, this permission is required for the source to be listed in the staff
507 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
510 CREATE TABLE config.z3950_attr (
511 id SERIAL PRIMARY KEY,
512 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
517 truncation INT NOT NULL DEFAULT 0,
518 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
521 CREATE TABLE config.z3950_source_credentials (
522 id SERIAL PRIMARY KEY,
523 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
524 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
527 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
530 CREATE TABLE config.i18n_locale (
531 code TEXT PRIMARY KEY,
532 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
533 name TEXT UNIQUE NOT NULL,
537 CREATE TABLE config.i18n_core (
538 id BIGSERIAL PRIMARY KEY,
539 fq_field TEXT NOT NULL,
540 identity_value TEXT NOT NULL,
541 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
545 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
547 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
551 UPDATE config.i18n_core
552 SET identity_value = $$ || quote_literal(new_ident) || $$
553 WHERE fq_field LIKE '$$ || hint || $$.%'
554 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
559 $_$ LANGUAGE PLPGSQL;
561 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
563 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
566 $_$ LANGUAGE PLPGSQL;
568 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
570 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
573 $_$ LANGUAGE PLPGSQL;
575 CREATE TABLE config.billing_type (
576 id SERIAL PRIMARY KEY,
578 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
579 default_price NUMERIC(6,2),
580 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
583 CREATE TABLE config.settings_group (
584 name TEXT PRIMARY KEY,
585 label TEXT UNIQUE NOT NULL -- I18N
588 CREATE TABLE config.org_unit_setting_type (
589 name TEXT PRIMARY KEY,
590 label TEXT UNIQUE NOT NULL,
591 grp TEXT REFERENCES config.settings_group (name),
593 datatype TEXT NOT NULL DEFAULT 'string',
598 -- define valid datatypes
600 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
601 ( 'bool', 'integer', 'float', 'currency', 'interval',
602 'date', 'string', 'object', 'array', 'link' ) ),
604 -- fm_class is meaningful only for 'link' datatype
606 CONSTRAINT coust_no_empty_link CHECK
607 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
608 ( datatype <> 'link' AND fm_class IS NULL ) )
611 CREATE TABLE config.usr_setting_type (
613 name TEXT PRIMARY KEY,
614 opac_visible BOOL NOT NULL DEFAULT FALSE,
615 label TEXT UNIQUE NOT NULL,
617 grp TEXT REFERENCES config.settings_group (name),
618 datatype TEXT NOT NULL DEFAULT 'string',
623 -- define valid datatypes
625 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
626 ( 'bool', 'integer', 'float', 'currency', 'interval',
627 'date', 'string', 'object', 'array', 'link' ) ),
630 -- fm_class is meaningful only for 'link' datatype
632 CONSTRAINT coust_no_empty_link CHECK
633 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
634 ( datatype <> 'link' AND fm_class IS NULL ) )
638 -- Some handy functions, based on existing ones, to provide optional ingest normalization
640 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
641 SELECT SUBSTRING($1,$2);
642 $func$ LANGUAGE SQL STRICT IMMUTABLE;
644 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
645 SELECT SUBSTRING($1,1,$2);
646 $func$ LANGUAGE SQL STRICT IMMUTABLE;
648 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
649 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
650 $func$ LANGUAGE SQL STRICT IMMUTABLE;
652 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
653 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
654 $func$ LANGUAGE SQL STRICT IMMUTABLE;
656 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
657 SELECT approximate_date( $1, '0');
658 $func$ LANGUAGE SQL STRICT IMMUTABLE;
660 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
661 SELECT approximate_date( $1, '9');
662 $func$ LANGUAGE SQL STRICT IMMUTABLE;
664 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
665 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
666 $func$ LANGUAGE SQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
669 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
670 $func$ LANGUAGE SQL STRICT IMMUTABLE;
672 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
677 # Find the first ISBN, force it to ISBN13 and return it
681 foreach my $word (split(/\s/, $input)) {
682 my $isbn = Business::ISBN->new($word);
684 # First check the checksum; if it is not valid, fix it and add the original
685 # bad-checksum ISBN to the output
686 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
687 $isbn->fix_checksum();
690 # If we now have a valid ISBN, force it to ISBN13 and return it
691 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
694 $func$ LANGUAGE PLPERLU;
696 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
697 Inspired by translate_isbn1013
699 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
700 version without hypens and with a repaired checksum if the checksum was bad
704 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
709 # For each ISBN found in a single string containing a set of ISBNs:
710 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
711 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
716 foreach my $word (split(/\s/, $input)) {
717 my $isbn = Business::ISBN->new($word);
719 # First check the checksum; if it is not valid, fix it and add the original
720 # bad-checksum ISBN to the output
721 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
722 $output .= $isbn->isbn() . " ";
723 $isbn->fix_checksum();
726 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
727 # and add the normalized original ISBN to the output
728 if ($isbn && $isbn->is_valid()) {
729 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
730 $output .= $isbn->isbn . " ";
732 # If we successfully converted the ISBN to its counterpart, add the
733 # converted ISBN to the output as well
734 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
737 return $output if $output;
739 # If there were no valid ISBNs, just return the raw input
741 $func$ LANGUAGE PLPERLU;
743 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
744 The translate_isbn1013 function takes an input ISBN and returns the
745 following in a single space-delimited string if the input ISBN is valid:
746 - The normalized input ISBN (hyphens stripped)
747 - The normalized input ISBN with a fixed checksum if the checksum was bad
748 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
751 -- And ... a table in which to register them
753 CREATE TABLE config.index_normalizer (
754 id SERIAL PRIMARY KEY,
755 name TEXT UNIQUE NOT NULL,
758 param_count INT NOT NULL DEFAULT 0
761 CREATE TABLE config.metabib_field_index_norm_map (
762 id SERIAL PRIMARY KEY,
763 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
764 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
766 pos INT NOT NULL DEFAULT 0
769 CREATE TABLE config.record_attr_definition (
770 name TEXT PRIMARY KEY,
771 label TEXT NOT NULL, -- I18N
773 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
774 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
775 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
776 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
778 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
779 tag TEXT, -- LIKE format
780 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
782 -- This is used for both tag/sf and xpath entries
785 -- For xpath-extracted attrs
787 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
792 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
794 -- For phys-char fields
795 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
798 CREATE TABLE config.record_attr_index_norm_map (
799 id SERIAL PRIMARY KEY,
800 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
801 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
803 pos INT NOT NULL DEFAULT 0
806 CREATE TABLE config.coded_value_map (
807 id SERIAL PRIMARY KEY,
808 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
812 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
814 is_simple BOOL NOT NULL DEFAULT FALSE
817 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
818 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
819 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
820 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
821 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
822 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
823 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
825 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$
827 current_row config.coded_value_map%ROWTYPE;
829 -- Look for a current value
830 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
832 IF FOUND AND NOT add_only THEN
833 -- Update anything we were handed
834 current_row.value := COALESCE(current_row.value, in_value);
835 current_row.description := COALESCE(current_row.description, in_description);
836 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
837 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
838 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
839 UPDATE config.coded_value_map
841 value = current_row.value,
842 description = current_row.description,
843 opac_visible = current_row.opac_visible,
844 search_label = current_row.search_label,
845 is_simple = current_row.is_simple
846 WHERE id = current_row.id;
848 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
849 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
852 $f$ LANGUAGE PLPGSQL;
854 CREATE TABLE config.composite_attr_entry_definition(
855 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
856 definition TEXT NOT NULL -- JSON
859 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
860 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
861 SELECT DISTINCT l.version
862 FROM config.upgrade_log l
863 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
864 WHERE d.db_patch = $1
867 -- List applied db patches that are superseded by (and block the application of) my_db_patch
868 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
869 SELECT DISTINCT l.version
870 FROM config.upgrade_log l
871 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
872 WHERE d.db_patch = $1
875 -- List applied db patches that deprecates (and block the application of) my_db_patch
876 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
878 FROM config.db_patch_dependencies
879 WHERE ARRAY[$1]::TEXT[] && deprecates
882 -- List applied db patches that supersedes (and block the application of) my_db_patch
883 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
885 FROM config.db_patch_dependencies
886 WHERE ARRAY[$1]::TEXT[] && supersedes
889 -- Make sure that no deprecated or superseded db patches are currently applied
890 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
892 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
894 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
896 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
898 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
901 -- Raise an exception if there are, in fact, dep/sup conflict
902 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
907 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
908 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
909 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
911 Upgrade script % can not be applied:
912 applied deprecated scripts %
913 applied superseded scripts %
917 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
918 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
919 evergreen.upgrade_list_applied_deprecated(my_db_patch),
920 evergreen.upgrade_list_applied_superseded(my_db_patch);
923 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
928 CREATE TABLE config.barcode_completion (
929 id SERIAL PRIMARY KEY,
930 active BOOL NOT NULL DEFAULT true,
931 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
934 length INT NOT NULL DEFAULT 0,
936 padding_end BOOL NOT NULL DEFAULT false,
937 asset BOOL NOT NULL DEFAULT true,
938 actor BOOL NOT NULL DEFAULT true
941 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
943 -- Add support for logging, only keep the most recent five rows for each category.
946 CREATE TABLE config.org_unit_setting_type_log (
947 id BIGSERIAL PRIMARY KEY,
948 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
949 org INT, --REFERENCES actor.org_unit (id),
952 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
955 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
956 Org Unit setting Logs
958 This table contains the most recent changes to each setting
959 in actor.org_unit_setting, allowing for mistakes to be undone.
960 This is NOT meant to be an auditor, but rather an undo/redo.
963 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
965 -- Only keeps the most recent five settings changes.
966 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
967 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
969 IF (TG_OP = 'UPDATE') THEN
971 ELSIF (TG_OP = 'INSERT') THEN
976 $oustl_limit$ LANGUAGE plpgsql;
978 CREATE TRIGGER limit_logs_oust
979 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
980 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
982 CREATE TABLE config.sms_carrier (
983 id SERIAL PRIMARY KEY,
987 active BOOLEAN DEFAULT TRUE
990 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
992 CREATE TABLE config.usr_activity_type (
993 id SERIAL PRIMARY KEY,
997 label TEXT NOT NULL, -- i18n
998 egroup config.usr_activity_group NOT NULL,
999 enabled BOOL NOT NULL DEFAULT TRUE,
1000 transient BOOL NOT NULL DEFAULT FALSE,
1001 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1004 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1005 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1007 CREATE TABLE config.filter_dialog_interface (
1008 key TEXT PRIMARY KEY,
1012 CREATE TABLE config.filter_dialog_filter_set (
1013 id SERIAL PRIMARY KEY,
1015 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1016 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1017 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1018 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1019 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1020 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1023 CREATE TABLE config.best_hold_order(
1024 id SERIAL PRIMARY KEY,
1025 name TEXT UNIQUE, -- i18n
1026 pprox INT, -- copy capture <-> pickup lib prox
1027 hprox INT, -- copy circ lib <-> request lib prox
1028 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1029 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1030 priority INT, -- group hold priority
1031 cut INT, -- cut-in-line
1032 depth INT, -- selection depth
1033 htime INT, -- time since last home-lib circ exceeds org-unit setting
1034 rtime INT, -- request time
1035 shtime INT -- time since copy last trip home exceeds org-unit setting
1038 -- At least one of these columns must contain a non-null value
1039 ALTER TABLE config.best_hold_order ADD CHECK ((
1040 pprox IS NOT NULL OR
1041 hprox IS NOT NULL OR
1042 aprox IS NOT NULL OR
1043 priority IS NOT NULL OR
1045 depth IS NOT NULL OR
1046 htime IS NOT NULL OR
1050 CREATE OR REPLACE FUNCTION
1051 evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$
1052 SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1);
1053 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1055 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$
1056 Results in TRUE if there exists at least one config.z3950_attr
1057 with the provided name. Used by config.z3950_index_field_map
1058 to verify z3950_attr_type maps.
1061 -- drop these in down here since they reference config.metabib_field
1062 -- and config.record_attr_definition
1063 CREATE TABLE config.z3950_index_field_map (
1064 id SERIAL PRIMARY KEY,
1065 label TEXT NOT NULL, -- i18n
1066 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1067 record_attr TEXT REFERENCES config.record_attr_definition(name),
1068 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1069 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1070 CONSTRAINT metabib_field_or_record_attr CHECK (
1071 metabib_field IS NOT NULL OR
1072 record_attr IS NOT NULL
1074 CONSTRAINT attr_or_attr_type CHECK (
1075 z3950_attr IS NOT NULL OR
1076 z3950_attr_type IS NOT NULL
1078 -- ensure the selected z3950_attr_type refers to a valid attr name
1079 CONSTRAINT valid_z3950_attr_type CHECK (
1080 z3950_attr_type IS NULL OR
1081 evergreen.z3950_attr_name_is_valid(z3950_attr_type)