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 ('0891', :eg_version); -- csharp/paxed/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 OR REPLACE FUNCTION
173 config.metabib_representative_field_is_valid(INTEGER, TEXT) RETURNS BOOLEAN AS $$
174 SELECT EXISTS (SELECT 1 FROM config.metabib_field WHERE id = $1 AND field_class = $2);
175 $$ LANGUAGE SQL STRICT IMMUTABLE;
177 COMMENT ON FUNCTION config.metabib_representative_field_is_valid(INTEGER, TEXT) IS $$
178 Ensure the field_class value on the selected representative field matches
182 CREATE TABLE config.metabib_class (
183 name TEXT PRIMARY KEY,
184 label TEXT NOT NULL UNIQUE,
185 buoyant BOOL DEFAULT FALSE NOT NULL,
186 restrict BOOL DEFAULT FALSE NOT NULL,
187 combined BOOL DEFAULT FALSE NOT NULL,
188 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
189 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
190 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
191 d_weight NUMERIC DEFAULT 0.1 NOT NULL
192 representative_field INTEGER REFERENCES config.metabib_field(id),
193 CONSTRAINT rep_field_unique UNIQUE(representative_field),
194 CONSTRAINT rep_field_is_valid CHECK (
195 representative_field IS NULL OR
196 config.metabib_representative_field_is_valid(representative_field, name)
200 CREATE TABLE config.metabib_field (
201 id SERIAL PRIMARY KEY,
202 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
206 weight INT NOT NULL DEFAULT 1,
207 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
208 search_field BOOL NOT NULL DEFAULT TRUE,
209 facet_field BOOL NOT NULL DEFAULT FALSE,
210 browse_field BOOL NOT NULL DEFAULT TRUE,
212 browse_sort_xpath TEXT,
215 authority_xpath TEXT,
217 restrict BOOL DEFAULT FALSE NOT NULL,
218 display_field BOOL NOT NULL DEFAULT TRUE
220 COMMENT ON TABLE config.metabib_field IS $$
221 XPath used for record indexing ingest
223 This table contains the XPath used to chop up MODS into its
224 indexable parts. Each XPath entry is named and assigned to
225 a "class" of either title, subject, author, keyword, series
229 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
231 CREATE TABLE config.ts_config_list (
235 COMMENT ON TABLE config.ts_config_list IS $$
238 A list of full text configs with names and descriptions.
241 CREATE TABLE config.metabib_class_ts_map (
242 id SERIAL PRIMARY KEY,
243 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
244 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
245 active BOOL NOT NULL DEFAULT TRUE,
246 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
247 index_lang TEXT NULL,
248 search_lang TEXT NULL,
249 always BOOL NOT NULL DEFAULT true
251 COMMENT ON TABLE config.metabib_class_ts_map IS $$
252 Text Search Configs for metabib class indexing
254 This table contains text search config definitions for
255 storing index_vector values.
258 CREATE TABLE config.metabib_field_ts_map (
259 id SERIAL PRIMARY KEY,
260 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
261 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
262 active BOOL NOT NULL DEFAULT TRUE,
263 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
264 index_lang TEXT NULL,
265 search_lang TEXT NULL
267 COMMENT ON TABLE config.metabib_field_ts_map IS $$
268 Text Search Configs for metabib field indexing
270 This table contains text search config definitions for
271 storing index_vector values.
274 CREATE TABLE config.metabib_search_alias (
275 alias TEXT PRIMARY KEY,
276 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
277 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
280 CREATE TABLE config.non_cataloged_type (
281 id SERIAL PRIMARY KEY,
282 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
284 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
285 in_house BOOL NOT NULL DEFAULT FALSE,
286 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
288 COMMENT ON TABLE config.non_cataloged_type IS $$
289 Types of valid non-cataloged items.
292 CREATE TABLE config.identification_type (
293 id SERIAL PRIMARY KEY,
294 name TEXT NOT NULL UNIQUE
296 COMMENT ON TABLE config.identification_type IS $$
297 Types of valid patron identification.
299 Each patron must display at least one valid form of identification
300 in order to get a library card. This table lists those forms.
303 CREATE TABLE config.rule_circ_duration (
304 id SERIAL PRIMARY KEY,
305 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
306 extended INTERVAL NOT NULL,
307 normal INTERVAL NOT NULL,
308 shrt INTERVAL NOT NULL,
309 max_renewals INT NOT NULL
311 COMMENT ON TABLE config.rule_circ_duration IS $$
312 Circulation Duration rules
314 Each circulation is given a duration based on one of these rules.
317 CREATE TABLE config.hard_due_date (
318 id SERIAL PRIMARY KEY,
319 name TEXT NOT NULL UNIQUE,
320 ceiling_date TIMESTAMPTZ NOT NULL,
321 forceto BOOL NOT NULL,
322 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
325 CREATE TABLE config.hard_due_date_values (
326 id SERIAL PRIMARY KEY,
327 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
328 DEFERRABLE INITIALLY DEFERRED,
329 ceiling_date TIMESTAMPTZ NOT NULL,
330 active_date TIMESTAMPTZ NOT NULL
333 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
335 temp_value config.hard_due_date_values%ROWTYPE;
339 SELECT DISTINCT ON (hard_due_date) *
340 FROM config.hard_due_date_values
341 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
342 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
344 UPDATE config.hard_due_date
345 SET ceiling_date = temp_value.ceiling_date
346 WHERE id = temp_value.hard_due_date
347 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
350 updated := updated + 1;
356 $func$ LANGUAGE plpgsql;
358 CREATE TABLE config.rule_max_fine (
359 id SERIAL PRIMARY KEY,
360 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
361 amount NUMERIC(6,2) NOT NULL,
362 is_percent BOOL NOT NULL DEFAULT FALSE
364 COMMENT ON TABLE config.rule_max_fine IS $$
365 Circulation Max Fine rules
367 Each circulation is given a maximum fine based on one of
371 CREATE TABLE config.rule_recurring_fine (
372 id SERIAL PRIMARY KEY,
373 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
374 high NUMERIC(6,2) NOT NULL,
375 normal NUMERIC(6,2) NOT NULL,
376 low NUMERIC(6,2) NOT NULL,
377 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
378 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
380 COMMENT ON TABLE config.rule_recurring_fine IS $$
381 Circulation Recurring Fine rules
383 Each circulation is given a recurring fine amount based on one of
384 these rules. Note that it is recommended to run the fine generator
385 (from cron) at least as frequently as the lowest recurrence interval
386 used by your circulation rules so that accrued fines will be up
391 CREATE TABLE config.rule_age_hold_protect (
392 id SERIAL PRIMARY KEY,
393 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
394 age INTERVAL NOT NULL,
397 COMMENT ON TABLE config.rule_age_hold_protect IS $$
398 Hold Item Age Protection rules
400 A hold request can only capture new(ish) items when they are
401 within a particular proximity of the pickup_lib of the request.
402 The proximity ('prox' column) is calculated by counting
403 the number of tree edges between the pickup_lib and either the
404 owning_lib or circ_lib of the copy that could fulfill the hold,
405 as determined by the distance_is_from_owner value of the hold matrix
406 rule controlling the hold request.
409 CREATE TABLE config.copy_status (
410 id SERIAL PRIMARY KEY,
411 name TEXT NOT NULL UNIQUE,
412 holdable BOOL NOT NULL DEFAULT FALSE,
413 opac_visible BOOL NOT NULL DEFAULT FALSE,
414 copy_active BOOL NOT NULL DEFAULT FALSE,
415 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
417 COMMENT ON TABLE config.copy_status IS $$
420 The available copy statuses, and whether a copy in that
421 status is available for hold request capture. 0 (zero) is
422 the only special number in this set, meaning that the item
423 is available for immediate checkout, and is counted as available
426 Statuses with an ID below 100 are not removable, and have special
427 meaning in the code. Do not change them except to translate the
430 You may add and remove statuses above 100, and these can be used
431 to remove items from normal circulation without affecting the rest
432 of the copy's values or its location.
435 CREATE TABLE config.net_access_level (
436 id SERIAL PRIMARY KEY,
437 name TEXT NOT NULL UNIQUE
439 COMMENT ON TABLE config.net_access_level IS $$
440 Patron Network Access level
442 This will be used to inform the in-library firewall of how much
443 internet access the using patron should be allowed.
447 CREATE TABLE config.remote_account (
448 id SERIAL PRIMARY KEY,
450 host TEXT NOT NULL, -- name or IP, :port optional
451 username TEXT, -- optional, since we could default to $USER
452 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
453 account TEXT, -- aka profile or FTP "account" command
454 path TEXT, -- aka directory
455 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
456 last_activity TIMESTAMP WITH TIME ZONE
459 CREATE TABLE config.marc21_rec_type_map (
460 code TEXT PRIMARY KEY,
461 type_val TEXT NOT NULL,
462 blvl_val TEXT NOT NULL
465 CREATE TABLE config.marc21_ff_pos_map (
466 id SERIAL PRIMARY KEY,
467 fixed_field TEXT NOT NULL,
469 rec_type TEXT NOT NULL,
470 start_pos INT NOT NULL,
472 default_val TEXT NOT NULL DEFAULT ' '
475 CREATE TABLE config.marc21_physical_characteristic_type_map (
476 ptype_key TEXT PRIMARY KEY,
477 label TEXT NOT NULL -- I18N
480 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
481 id SERIAL PRIMARY KEY,
482 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
483 subfield TEXT NOT NULL,
484 start_pos INT NOT NULL,
486 label TEXT NOT NULL -- I18N
489 CREATE TABLE config.marc21_physical_characteristic_value_map (
490 id SERIAL PRIMARY KEY,
492 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
493 label TEXT NOT NULL -- I18N
497 CREATE TABLE config.z3950_source (
498 name TEXT PRIMARY KEY,
499 label TEXT NOT NULL UNIQUE,
503 record_format TEXT NOT NULL DEFAULT 'FI',
504 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
505 auth BOOL NOT NULL DEFAULT TRUE,
506 use_perm INT -- REFERENCES permission.perm_list (id)
509 COMMENT ON TABLE config.z3950_source IS $$
512 Each row in this table represents a database searchable via Z39.50.
515 COMMENT ON COLUMN config.z3950_source.record_format IS $$
519 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
520 Z39.50 preferred record syntax..
523 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
524 If set, this permission is required for the source to be listed in the staff
525 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
528 CREATE TABLE config.z3950_attr (
529 id SERIAL PRIMARY KEY,
530 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
535 truncation INT NOT NULL DEFAULT 0,
536 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
539 CREATE TABLE config.z3950_source_credentials (
540 id SERIAL PRIMARY KEY,
541 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
542 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
545 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
548 CREATE TABLE config.i18n_locale (
549 code TEXT PRIMARY KEY,
550 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
551 name TEXT UNIQUE NOT NULL,
555 CREATE TABLE config.i18n_core (
556 id BIGSERIAL PRIMARY KEY,
557 fq_field TEXT NOT NULL,
558 identity_value TEXT NOT NULL,
559 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
563 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
565 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
569 UPDATE config.i18n_core
570 SET identity_value = $$ || quote_literal(new_ident) || $$
571 WHERE fq_field LIKE '$$ || hint || $$.%'
572 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
577 $_$ LANGUAGE PLPGSQL;
579 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
581 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
584 $_$ LANGUAGE PLPGSQL;
586 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
588 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
591 $_$ LANGUAGE PLPGSQL;
593 CREATE TABLE config.billing_type (
594 id SERIAL PRIMARY KEY,
596 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
597 default_price NUMERIC(6,2),
598 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
601 CREATE TABLE config.settings_group (
602 name TEXT PRIMARY KEY,
603 label TEXT UNIQUE NOT NULL -- I18N
606 CREATE TABLE config.org_unit_setting_type (
607 name TEXT PRIMARY KEY,
608 label TEXT UNIQUE NOT NULL,
609 grp TEXT REFERENCES config.settings_group (name),
611 datatype TEXT NOT NULL DEFAULT 'string',
616 -- define valid datatypes
618 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
619 ( 'bool', 'integer', 'float', 'currency', 'interval',
620 'date', 'string', 'object', 'array', 'link' ) ),
622 -- fm_class is meaningful only for 'link' datatype
624 CONSTRAINT coust_no_empty_link CHECK
625 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
626 ( datatype <> 'link' AND fm_class IS NULL ) )
629 CREATE TABLE config.usr_setting_type (
631 name TEXT PRIMARY KEY,
632 opac_visible BOOL NOT NULL DEFAULT FALSE,
633 label TEXT UNIQUE NOT NULL,
635 grp TEXT REFERENCES config.settings_group (name),
636 datatype TEXT NOT NULL DEFAULT 'string',
641 -- define valid datatypes
643 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
644 ( 'bool', 'integer', 'float', 'currency', 'interval',
645 'date', 'string', 'object', 'array', 'link' ) ),
648 -- fm_class is meaningful only for 'link' datatype
650 CONSTRAINT coust_no_empty_link CHECK
651 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
652 ( datatype <> 'link' AND fm_class IS NULL ) )
656 -- Some handy functions, based on existing ones, to provide optional ingest normalization
658 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
659 SELECT SUBSTRING($1,$2);
660 $func$ LANGUAGE SQL STRICT IMMUTABLE;
662 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
663 SELECT SUBSTRING($1,1,$2);
664 $func$ LANGUAGE SQL STRICT IMMUTABLE;
666 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
667 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
668 $func$ LANGUAGE SQL STRICT IMMUTABLE;
670 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
671 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
672 $func$ LANGUAGE SQL STRICT IMMUTABLE;
674 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
675 SELECT approximate_date( $1, '0');
676 $func$ LANGUAGE SQL STRICT IMMUTABLE;
678 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
679 SELECT approximate_date( $1, '9');
680 $func$ LANGUAGE SQL STRICT IMMUTABLE;
682 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
683 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
684 $func$ LANGUAGE SQL STRICT IMMUTABLE;
686 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
687 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
688 $func$ LANGUAGE SQL STRICT IMMUTABLE;
690 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
695 # Find the first ISBN, force it to ISBN13 and return it
699 foreach my $word (split(/\s/, $input)) {
700 my $isbn = Business::ISBN->new($word);
702 # First check the checksum; if it is not valid, fix it and add the original
703 # bad-checksum ISBN to the output
704 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
705 $isbn->fix_checksum();
708 # If we now have a valid ISBN, force it to ISBN13 and return it
709 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
712 $func$ LANGUAGE PLPERLU;
714 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
715 Inspired by translate_isbn1013
717 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
718 version without hypens and with a repaired checksum if the checksum was bad
722 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
727 # For each ISBN found in a single string containing a set of ISBNs:
728 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
729 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
734 foreach my $word (split(/\s/, $input)) {
735 my $isbn = Business::ISBN->new($word);
737 # First check the checksum; if it is not valid, fix it and add the original
738 # bad-checksum ISBN to the output
739 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
740 $output .= $isbn->isbn() . " ";
741 $isbn->fix_checksum();
744 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
745 # and add the normalized original ISBN to the output
746 if ($isbn && $isbn->is_valid()) {
747 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
748 $output .= $isbn->isbn . " ";
750 # If we successfully converted the ISBN to its counterpart, add the
751 # converted ISBN to the output as well
752 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
755 return $output if $output;
757 # If there were no valid ISBNs, just return the raw input
759 $func$ LANGUAGE PLPERLU;
761 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
762 The translate_isbn1013 function takes an input ISBN and returns the
763 following in a single space-delimited string if the input ISBN is valid:
764 - The normalized input ISBN (hyphens stripped)
765 - The normalized input ISBN with a fixed checksum if the checksum was bad
766 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
769 -- And ... a table in which to register them
771 CREATE TABLE config.index_normalizer (
772 id SERIAL PRIMARY KEY,
773 name TEXT UNIQUE NOT NULL,
776 param_count INT NOT NULL DEFAULT 0
779 CREATE TABLE config.metabib_field_index_norm_map (
780 id SERIAL PRIMARY KEY,
781 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
782 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
784 pos INT NOT NULL DEFAULT 0
787 CREATE TABLE config.record_attr_definition (
788 name TEXT PRIMARY KEY,
789 label TEXT NOT NULL, -- I18N
791 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
792 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
793 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
794 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
796 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
797 tag TEXT, -- LIKE format
798 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
800 -- This is used for both tag/sf and xpath entries
803 -- For xpath-extracted attrs
805 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
810 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
812 -- For phys-char fields
813 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
816 CREATE TABLE config.record_attr_index_norm_map (
817 id SERIAL PRIMARY KEY,
818 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
819 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
821 pos INT NOT NULL DEFAULT 0
824 CREATE TABLE config.coded_value_map (
825 id SERIAL PRIMARY KEY,
826 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
830 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
832 is_simple BOOL NOT NULL DEFAULT FALSE
835 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
836 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
837 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
838 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
839 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
840 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
841 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
843 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$
845 current_row config.coded_value_map%ROWTYPE;
847 -- Look for a current value
848 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
850 IF FOUND AND NOT add_only THEN
851 -- Update anything we were handed
852 current_row.value := COALESCE(current_row.value, in_value);
853 current_row.description := COALESCE(current_row.description, in_description);
854 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
855 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
856 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
857 UPDATE config.coded_value_map
859 value = current_row.value,
860 description = current_row.description,
861 opac_visible = current_row.opac_visible,
862 search_label = current_row.search_label,
863 is_simple = current_row.is_simple
864 WHERE id = current_row.id;
866 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
867 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
870 $f$ LANGUAGE PLPGSQL;
872 CREATE TABLE config.composite_attr_entry_definition(
873 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
874 definition TEXT NOT NULL -- JSON
877 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
878 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
879 SELECT DISTINCT l.version
880 FROM config.upgrade_log l
881 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
882 WHERE d.db_patch = $1
885 -- List applied db patches that are superseded by (and block the application of) my_db_patch
886 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
887 SELECT DISTINCT l.version
888 FROM config.upgrade_log l
889 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
890 WHERE d.db_patch = $1
893 -- List applied db patches that deprecates (and block the application of) my_db_patch
894 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
896 FROM config.db_patch_dependencies
897 WHERE ARRAY[$1]::TEXT[] && deprecates
900 -- List applied db patches that supersedes (and block the application of) my_db_patch
901 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
903 FROM config.db_patch_dependencies
904 WHERE ARRAY[$1]::TEXT[] && supersedes
907 -- Make sure that no deprecated or superseded db patches are currently applied
908 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
910 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
912 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
914 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
916 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
919 -- Raise an exception if there are, in fact, dep/sup conflict
920 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
925 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
926 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
927 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
929 Upgrade script % can not be applied:
930 applied deprecated scripts %
931 applied superseded scripts %
935 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
936 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
937 evergreen.upgrade_list_applied_deprecated(my_db_patch),
938 evergreen.upgrade_list_applied_superseded(my_db_patch);
941 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
946 CREATE TABLE config.barcode_completion (
947 id SERIAL PRIMARY KEY,
948 active BOOL NOT NULL DEFAULT true,
949 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
952 length INT NOT NULL DEFAULT 0,
954 padding_end BOOL NOT NULL DEFAULT false,
955 asset BOOL NOT NULL DEFAULT true,
956 actor BOOL NOT NULL DEFAULT true
959 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
961 -- Add support for logging, only keep the most recent five rows for each category.
964 CREATE TABLE config.org_unit_setting_type_log (
965 id BIGSERIAL PRIMARY KEY,
966 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
967 org INT, --REFERENCES actor.org_unit (id),
970 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
973 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
974 Org Unit setting Logs
976 This table contains the most recent changes to each setting
977 in actor.org_unit_setting, allowing for mistakes to be undone.
978 This is NOT meant to be an auditor, but rather an undo/redo.
981 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
983 -- Only keeps the most recent five settings changes.
984 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
985 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
987 IF (TG_OP = 'UPDATE') THEN
989 ELSIF (TG_OP = 'INSERT') THEN
994 $oustl_limit$ LANGUAGE plpgsql;
996 CREATE TRIGGER limit_logs_oust
997 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
998 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1000 CREATE TABLE config.sms_carrier (
1001 id SERIAL PRIMARY KEY,
1005 active BOOLEAN DEFAULT TRUE
1008 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1010 CREATE TABLE config.usr_activity_type (
1011 id SERIAL PRIMARY KEY,
1015 label TEXT NOT NULL, -- i18n
1016 egroup config.usr_activity_group NOT NULL,
1017 enabled BOOL NOT NULL DEFAULT TRUE,
1018 transient BOOL NOT NULL DEFAULT FALSE,
1019 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1022 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1023 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1025 CREATE TABLE config.filter_dialog_interface (
1026 key TEXT PRIMARY KEY,
1030 CREATE TABLE config.filter_dialog_filter_set (
1031 id SERIAL PRIMARY KEY,
1033 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1034 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1035 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1036 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1037 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1038 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1041 CREATE TABLE config.best_hold_order(
1042 id SERIAL PRIMARY KEY,
1043 name TEXT UNIQUE, -- i18n
1044 pprox INT, -- copy capture <-> pickup lib prox
1045 hprox INT, -- copy circ lib <-> request lib prox
1046 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1047 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1048 priority INT, -- group hold priority
1049 cut INT, -- cut-in-line
1050 depth INT, -- selection depth
1051 htime INT, -- time since last home-lib circ exceeds org-unit setting
1052 rtime INT, -- request time
1053 shtime INT -- time since copy last trip home exceeds org-unit setting
1056 -- At least one of these columns must contain a non-null value
1057 ALTER TABLE config.best_hold_order ADD CHECK ((
1058 pprox IS NOT NULL OR
1059 hprox IS NOT NULL OR
1060 aprox IS NOT NULL OR
1061 priority IS NOT NULL OR
1063 depth IS NOT NULL OR
1064 htime IS NOT NULL OR
1068 CREATE OR REPLACE FUNCTION
1069 evergreen.z3950_attr_name_is_valid(TEXT) RETURNS BOOLEAN AS $func$
1070 SELECT EXISTS (SELECT 1 FROM config.z3950_attr WHERE name = $1);
1071 $func$ LANGUAGE SQL STRICT IMMUTABLE;
1073 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid(TEXT) IS $$
1074 Results in TRUE if there exists at least one config.z3950_attr
1075 with the provided name. Used by config.z3950_index_field_map
1076 to verify z3950_attr_type maps.
1079 -- drop these in down here since they reference config.metabib_field
1080 -- and config.record_attr_definition
1081 CREATE TABLE config.z3950_index_field_map (
1082 id SERIAL PRIMARY KEY,
1083 label TEXT NOT NULL, -- i18n
1084 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1085 record_attr TEXT REFERENCES config.record_attr_definition(name),
1086 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1087 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1088 CONSTRAINT metabib_field_or_record_attr CHECK (
1089 metabib_field IS NOT NULL OR
1090 record_attr IS NOT NULL
1092 CONSTRAINT attr_or_attr_type CHECK (
1093 z3950_attr IS NOT NULL OR
1094 z3950_attr_type IS NOT NULL
1096 -- ensure the selected z3950_attr_type refers to a valid attr name
1097 CONSTRAINT valid_z3950_attr_type CHECK (
1098 z3950_attr_type IS NULL OR
1099 evergreen.z3950_attr_name_is_valid(z3950_attr_type)