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');
54 CREATE TABLE config.global_flag (
56 ) INHERITS (config.internal_flag);
57 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
59 CREATE TABLE config.upgrade_log (
60 version TEXT PRIMARY KEY,
61 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
65 CREATE TABLE config.db_patch_dependencies (
66 db_patch TEXT PRIMARY KEY,
71 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
77 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
79 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
85 CREATE TRIGGER no_overlapping_sups
86 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
87 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
89 CREATE TRIGGER no_overlapping_deps
90 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
91 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
93 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0766', :eg_version); -- gmc/miker
95 CREATE TABLE config.bib_source (
96 id SERIAL PRIMARY KEY,
97 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
98 source TEXT NOT NULL UNIQUE,
99 transcendant BOOL NOT NULL DEFAULT FALSE,
100 can_have_copies BOOL NOT NULL DEFAULT TRUE
102 COMMENT ON TABLE config.bib_source IS $$
103 This is table is used to set up the relative "quality" of each
104 MARC source, such as OCLC. Also identifies "transcendant" sources,
105 i.e., sources of bib records that should display in the OPAC
106 even if no copies or located URIs are attached. Also indicates if
107 the source is allowed to have actual copies on its bibs. Volumes
108 for targeted URIs are unaffected by this setting.
111 CREATE TABLE config.standing (
112 id SERIAL PRIMARY KEY,
113 value TEXT NOT NULL UNIQUE
115 COMMENT ON TABLE config.standing IS $$
118 This table contains the values that can be applied to a patron
119 by a staff member. These values should not be changed, other
120 than for translation, as the ID column is currently a "magic
121 number" in the source. :(
124 CREATE TABLE config.standing_penalty (
125 id SERIAL PRIMARY KEY,
126 name TEXT NOT NULL UNIQUE,
129 staff_alert BOOL NOT NULL DEFAULT FALSE,
133 CREATE TABLE config.xml_transform (
134 name TEXT PRIMARY KEY,
135 namespace_uri TEXT NOT NULL,
136 prefix TEXT NOT NULL,
140 CREATE TABLE config.biblio_fingerprint (
141 id SERIAL PRIMARY KEY,
144 first_word BOOL NOT NULL DEFAULT FALSE,
145 format TEXT NOT NULL DEFAULT 'marcxml'
148 INSERT INTO config.biblio_fingerprint (name, xpath, format)
151 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
152 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
153 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
154 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
155 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
159 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
162 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
163 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
164 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
165 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
171 CREATE TABLE config.metabib_class (
172 name TEXT PRIMARY KEY,
173 label TEXT NOT NULL UNIQUE,
174 buoyant BOOL DEFAULT FALSE NOT NULL,
175 restrict BOOL DEFAULT FALSE NOT NULL,
176 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
177 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
178 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
179 d_weight NUMERIC DEFAULT 0.1 NOT NULL
182 CREATE TABLE config.metabib_field (
183 id SERIAL PRIMARY KEY,
184 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
188 weight INT NOT NULL DEFAULT 1,
189 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
190 search_field BOOL NOT NULL DEFAULT TRUE,
191 facet_field BOOL NOT NULL DEFAULT FALSE,
192 browse_field BOOL NOT NULL DEFAULT TRUE,
195 restrict BOOL DEFAULT FALSE NOT NULL
197 COMMENT ON TABLE config.metabib_field IS $$
198 XPath used for record indexing ingest
200 This table contains the XPath used to chop up MODS into its
201 indexable parts. Each XPath entry is named and assigned to
202 a "class" of either title, subject, author, keyword, series
206 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
208 CREATE TABLE config.ts_config_list (
212 COMMENT ON TABLE config.ts_config_list IS $$
215 A list of full text configs with names and descriptions.
218 CREATE TABLE config.metabib_class_ts_map (
219 id SERIAL PRIMARY KEY,
220 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
221 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
222 active BOOL NOT NULL DEFAULT TRUE,
223 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
224 index_lang TEXT NULL,
225 search_lang TEXT NULL,
226 always BOOL NOT NULL DEFAULT true
228 COMMENT ON TABLE config.metabib_class_ts_map IS $$
229 Text Search Configs for metabib class indexing
231 This table contains text search config definitions for
232 storing index_vector values.
235 CREATE TABLE config.metabib_field_ts_map (
236 id SERIAL PRIMARY KEY,
237 metabib_field INT NOT NULL REFERENCES config.metabib_field (id),
238 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
239 active BOOL NOT NULL DEFAULT TRUE,
240 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
241 index_lang TEXT NULL,
242 search_lang TEXT NULL
244 COMMENT ON TABLE config.metabib_field_ts_map IS $$
245 Text Search Configs for metabib field indexing
247 This table contains text search config definitions for
248 storing index_vector values.
251 CREATE TABLE config.metabib_search_alias (
252 alias TEXT PRIMARY KEY,
253 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
254 field INT REFERENCES config.metabib_field (id)
257 CREATE TABLE config.non_cataloged_type (
258 id SERIAL PRIMARY KEY,
259 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
261 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
262 in_house BOOL NOT NULL DEFAULT FALSE,
263 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
265 COMMENT ON TABLE config.non_cataloged_type IS $$
266 Types of valid non-cataloged items.
269 CREATE TABLE config.identification_type (
270 id SERIAL PRIMARY KEY,
271 name TEXT NOT NULL UNIQUE
273 COMMENT ON TABLE config.identification_type IS $$
274 Types of valid patron identification.
276 Each patron must display at least one valid form of identification
277 in order to get a library card. This table lists those forms.
280 CREATE TABLE config.rule_circ_duration (
281 id SERIAL PRIMARY KEY,
282 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
283 extended INTERVAL NOT NULL,
284 normal INTERVAL NOT NULL,
285 shrt INTERVAL NOT NULL,
286 max_renewals INT NOT NULL
288 COMMENT ON TABLE config.rule_circ_duration IS $$
289 Circulation Duration rules
291 Each circulation is given a duration based on one of these rules.
294 CREATE TABLE config.hard_due_date (
295 id SERIAL PRIMARY KEY,
296 name TEXT NOT NULL UNIQUE,
297 ceiling_date TIMESTAMPTZ NOT NULL,
298 forceto BOOL NOT NULL,
299 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
302 CREATE TABLE config.hard_due_date_values (
303 id SERIAL PRIMARY KEY,
304 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
305 DEFERRABLE INITIALLY DEFERRED,
306 ceiling_date TIMESTAMPTZ NOT NULL,
307 active_date TIMESTAMPTZ NOT NULL
310 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
312 temp_value config.hard_due_date_values%ROWTYPE;
316 SELECT DISTINCT ON (hard_due_date) *
317 FROM config.hard_due_date_values
318 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
319 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
321 UPDATE config.hard_due_date
322 SET ceiling_date = temp_value.ceiling_date
323 WHERE id = temp_value.hard_due_date
324 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
327 updated := updated + 1;
333 $func$ LANGUAGE plpgsql;
335 CREATE TABLE config.rule_max_fine (
336 id SERIAL PRIMARY KEY,
337 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
338 amount NUMERIC(6,2) NOT NULL,
339 is_percent BOOL NOT NULL DEFAULT FALSE
341 COMMENT ON TABLE config.rule_max_fine IS $$
342 Circulation Max Fine rules
344 Each circulation is given a maximum fine based on one of
348 CREATE TABLE config.rule_recurring_fine (
349 id SERIAL PRIMARY KEY,
350 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
351 high NUMERIC(6,2) NOT NULL,
352 normal NUMERIC(6,2) NOT NULL,
353 low NUMERIC(6,2) NOT NULL,
354 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
355 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
357 COMMENT ON TABLE config.rule_recurring_fine IS $$
358 Circulation Recurring Fine rules
360 Each circulation is given a recurring fine amount based on one of
361 these rules. Note that it is recommended to run the fine generator
362 (from cron) at least as frequently as the lowest recurrence interval
363 used by your circulation rules so that accrued fines will be up
368 CREATE TABLE config.rule_age_hold_protect (
369 id SERIAL PRIMARY KEY,
370 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
371 age INTERVAL NOT NULL,
374 COMMENT ON TABLE config.rule_age_hold_protect IS $$
375 Hold Item Age Protection rules
377 A hold request can only capture new(ish) items when they are
378 within a particular proximity of the pickup_lib of the request.
379 The proximity ('prox' column) is calculated by counting
380 the number of tree edges between the pickup_lib and either the
381 owning_lib or circ_lib of the copy that could fulfill the hold,
382 as determined by the distance_is_from_owner value of the hold matrix
383 rule controlling the hold request.
386 CREATE TABLE config.copy_status (
387 id SERIAL PRIMARY KEY,
388 name TEXT NOT NULL UNIQUE,
389 holdable BOOL NOT NULL DEFAULT FALSE,
390 opac_visible BOOL NOT NULL DEFAULT FALSE,
391 copy_active BOOL NOT NULL DEFAULT FALSE,
392 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
394 COMMENT ON TABLE config.copy_status IS $$
397 The available copy statuses, and whether a copy in that
398 status is available for hold request capture. 0 (zero) is
399 the only special number in this set, meaning that the item
400 is available for immediate checkout, and is counted as available
403 Statuses with an ID below 100 are not removable, and have special
404 meaning in the code. Do not change them except to translate the
407 You may add and remove statuses above 100, and these can be used
408 to remove items from normal circulation without affecting the rest
409 of the copy's values or its location.
412 CREATE TABLE config.net_access_level (
413 id SERIAL PRIMARY KEY,
414 name TEXT NOT NULL UNIQUE
416 COMMENT ON TABLE config.net_access_level IS $$
417 Patron Network Access level
419 This will be used to inform the in-library firewall of how much
420 internet access the using patron should be allowed.
424 CREATE TABLE config.remote_account (
425 id SERIAL PRIMARY KEY,
427 host TEXT NOT NULL, -- name or IP, :port optional
428 username TEXT, -- optional, since we could default to $USER
429 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
430 account TEXT, -- aka profile or FTP "account" command
431 path TEXT, -- aka directory
432 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
433 last_activity TIMESTAMP WITH TIME ZONE
436 CREATE TABLE config.marc21_rec_type_map (
437 code TEXT PRIMARY KEY,
438 type_val TEXT NOT NULL,
439 blvl_val TEXT NOT NULL
442 CREATE TABLE config.marc21_ff_pos_map (
443 id SERIAL PRIMARY KEY,
444 fixed_field TEXT NOT NULL,
446 rec_type TEXT NOT NULL,
447 start_pos INT NOT NULL,
449 default_val TEXT NOT NULL DEFAULT ' '
452 CREATE TABLE config.marc21_physical_characteristic_type_map (
453 ptype_key TEXT PRIMARY KEY,
454 label TEXT NOT NULL -- I18N
457 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
458 id SERIAL PRIMARY KEY,
459 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
460 subfield TEXT NOT NULL,
461 start_pos INT NOT NULL,
463 label TEXT NOT NULL -- I18N
466 CREATE TABLE config.marc21_physical_characteristic_value_map (
467 id SERIAL PRIMARY KEY,
469 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
470 label TEXT NOT NULL -- I18N
474 CREATE TABLE config.z3950_source (
475 name TEXT PRIMARY KEY,
476 label TEXT NOT NULL UNIQUE,
480 record_format TEXT NOT NULL DEFAULT 'FI',
481 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
482 auth BOOL NOT NULL DEFAULT TRUE,
483 use_perm INT -- REFERENCES permission.perm_list (id)
486 COMMENT ON TABLE config.z3950_source IS $$
489 Each row in this table represents a database searchable via Z39.50.
492 COMMENT ON COLUMN config.z3950_source.record_format IS $$
496 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
497 Z39.50 preferred record syntax..
500 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
501 If set, this permission is required for the source to be listed in the staff
502 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
505 CREATE TABLE config.z3950_attr (
506 id SERIAL PRIMARY KEY,
507 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
512 truncation INT NOT NULL DEFAULT 0,
513 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
516 CREATE TABLE config.i18n_locale (
517 code TEXT PRIMARY KEY,
518 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
519 name TEXT UNIQUE NOT NULL,
523 CREATE TABLE config.i18n_core (
524 id BIGSERIAL PRIMARY KEY,
525 fq_field TEXT NOT NULL,
526 identity_value TEXT NOT NULL,
527 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
531 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
533 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
537 UPDATE config.i18n_core
538 SET identity_value = $$ || quote_literal(new_ident) || $$
539 WHERE fq_field LIKE '$$ || hint || $$.%'
540 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
545 $_$ LANGUAGE PLPGSQL;
547 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
549 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
552 $_$ LANGUAGE PLPGSQL;
554 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
556 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
559 $_$ LANGUAGE PLPGSQL;
561 CREATE TABLE config.billing_type (
562 id SERIAL PRIMARY KEY,
564 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
565 default_price NUMERIC(6,2),
566 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
569 CREATE TABLE config.settings_group (
570 name TEXT PRIMARY KEY,
571 label TEXT UNIQUE NOT NULL -- I18N
574 CREATE TABLE config.org_unit_setting_type (
575 name TEXT PRIMARY KEY,
576 label TEXT UNIQUE NOT NULL,
577 grp TEXT REFERENCES config.settings_group (name),
579 datatype TEXT NOT NULL DEFAULT 'string',
584 -- define valid datatypes
586 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
587 ( 'bool', 'integer', 'float', 'currency', 'interval',
588 'date', 'string', 'object', 'array', 'link' ) ),
590 -- fm_class is meaningful only for 'link' datatype
592 CONSTRAINT coust_no_empty_link CHECK
593 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
594 ( datatype <> 'link' AND fm_class IS NULL ) )
597 CREATE TABLE config.usr_setting_type (
599 name TEXT PRIMARY KEY,
600 opac_visible BOOL NOT NULL DEFAULT FALSE,
601 label TEXT UNIQUE NOT NULL,
603 grp TEXT REFERENCES config.settings_group (name),
604 datatype TEXT NOT NULL DEFAULT 'string',
608 -- define valid datatypes
610 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
611 ( 'bool', 'integer', 'float', 'currency', 'interval',
612 'date', 'string', 'object', 'array', 'link' ) ),
615 -- fm_class is meaningful only for 'link' datatype
617 CONSTRAINT coust_no_empty_link CHECK
618 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
619 ( datatype <> 'link' AND fm_class IS NULL ) )
623 -- Some handy functions, based on existing ones, to provide optional ingest normalization
625 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
626 SELECT SUBSTRING($1,$2);
627 $func$ LANGUAGE SQL STRICT IMMUTABLE;
629 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
630 SELECT SUBSTRING($1,1,$2);
631 $func$ LANGUAGE SQL STRICT IMMUTABLE;
633 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
634 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
635 $func$ LANGUAGE SQL STRICT IMMUTABLE;
637 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
638 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
639 $func$ LANGUAGE SQL STRICT IMMUTABLE;
641 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
642 SELECT approximate_date( $1, '0');
643 $func$ LANGUAGE SQL STRICT IMMUTABLE;
645 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
646 SELECT approximate_date( $1, '9');
647 $func$ LANGUAGE SQL STRICT IMMUTABLE;
649 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
650 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
651 $func$ LANGUAGE SQL STRICT IMMUTABLE;
653 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
654 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
655 $func$ LANGUAGE SQL STRICT IMMUTABLE;
657 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
662 # Find the first ISBN, force it to ISBN13 and return it
666 foreach my $word (split(/\s/, $input)) {
667 my $isbn = Business::ISBN->new($word);
669 # First check the checksum; if it is not valid, fix it and add the original
670 # bad-checksum ISBN to the output
671 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
672 $isbn->fix_checksum();
675 # If we now have a valid ISBN, force it to ISBN13 and return it
676 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
679 $func$ LANGUAGE PLPERLU;
681 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
682 Inspired by translate_isbn1013
684 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
685 version without hypens and with a repaired checksum if the checksum was bad
689 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
694 # For each ISBN found in a single string containing a set of ISBNs:
695 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
696 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
701 foreach my $word (split(/\s/, $input)) {
702 my $isbn = Business::ISBN->new($word);
704 # First check the checksum; if it is not valid, fix it and add the original
705 # bad-checksum ISBN to the output
706 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
707 $output .= $isbn->isbn() . " ";
708 $isbn->fix_checksum();
711 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
712 # and add the normalized original ISBN to the output
713 if ($isbn && $isbn->is_valid()) {
714 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
715 $output .= $isbn->isbn . " ";
717 # If we successfully converted the ISBN to its counterpart, add the
718 # converted ISBN to the output as well
719 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
722 return $output if $output;
724 # If there were no valid ISBNs, just return the raw input
726 $func$ LANGUAGE PLPERLU;
728 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
729 The translate_isbn1013 function takes an input ISBN and returns the
730 following in a single space-delimited string if the input ISBN is valid:
731 - The normalized input ISBN (hyphens stripped)
732 - The normalized input ISBN with a fixed checksum if the checksum was bad
733 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
736 -- And ... a table in which to register them
738 CREATE TABLE config.index_normalizer (
739 id SERIAL PRIMARY KEY,
740 name TEXT UNIQUE NOT NULL,
743 param_count INT NOT NULL DEFAULT 0
746 CREATE TABLE config.metabib_field_index_norm_map (
747 id SERIAL PRIMARY KEY,
748 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
749 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
751 pos INT NOT NULL DEFAULT 0
754 CREATE TABLE config.record_attr_definition (
755 name TEXT PRIMARY KEY,
756 label TEXT NOT NULL, -- I18N
758 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
759 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
761 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
762 tag TEXT, -- LIKE format
763 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
765 -- This is used for both tag/sf and xpath entries
768 -- For xpath-extracted attrs
770 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
775 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
777 -- For phys-char fields
778 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
781 CREATE TABLE config.record_attr_index_norm_map (
782 id SERIAL PRIMARY KEY,
783 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
784 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
786 pos INT NOT NULL DEFAULT 0
789 CREATE TABLE config.coded_value_map (
790 id SERIAL PRIMARY KEY,
791 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
795 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
797 is_simple BOOL NOT NULL DEFAULT FALSE
800 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
801 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
802 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
803 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
804 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
805 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
806 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
808 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$
810 current_row config.coded_value_map%ROWTYPE;
812 -- Look for a current value
813 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
815 IF FOUND AND NOT add_only THEN
816 -- Update anything we were handed
817 current_row.value := COALESCE(current_row.value, in_value);
818 current_row.description := COALESCE(current_row.description, in_description);
819 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
820 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
821 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
822 UPDATE config.coded_value_map
824 value = current_row.value,
825 description = current_row.description,
826 opac_visible = current_row.opac_visible,
827 search_label = current_row.search_label,
828 is_simple = current_row.is_simple
829 WHERE id = current_row.id;
831 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
832 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
835 $f$ LANGUAGE PLPGSQL;
837 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
838 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
839 SELECT DISTINCT l.version
840 FROM config.upgrade_log l
841 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
842 WHERE d.db_patch = $1
845 -- List applied db patches that are superseded by (and block the application of) my_db_patch
846 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
847 SELECT DISTINCT l.version
848 FROM config.upgrade_log l
849 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
850 WHERE d.db_patch = $1
853 -- List applied db patches that deprecates (and block the application of) my_db_patch
854 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
856 FROM config.db_patch_dependencies
857 WHERE ARRAY[$1]::TEXT[] && deprecates
860 -- List applied db patches that supersedes (and block the application of) my_db_patch
861 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
863 FROM config.db_patch_dependencies
864 WHERE ARRAY[$1]::TEXT[] && supersedes
867 -- Make sure that no deprecated or superseded db patches are currently applied
868 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
870 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
872 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
874 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
876 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
879 -- Raise an exception if there are, in fact, dep/sup conflict
880 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
885 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
886 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
887 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
889 Upgrade script % can not be applied:
890 applied deprecated scripts %
891 applied superseded scripts %
895 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
896 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
897 evergreen.upgrade_list_applied_deprecated(my_db_patch),
898 evergreen.upgrade_list_applied_superseded(my_db_patch);
901 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
906 CREATE TABLE config.barcode_completion (
907 id SERIAL PRIMARY KEY,
908 active BOOL NOT NULL DEFAULT true,
909 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
912 length INT NOT NULL DEFAULT 0,
914 padding_end BOOL NOT NULL DEFAULT false,
915 asset BOOL NOT NULL DEFAULT true,
916 actor BOOL NOT NULL DEFAULT true
919 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
921 -- Add support for logging, only keep the most recent five rows for each category.
924 CREATE TABLE config.org_unit_setting_type_log (
925 id BIGSERIAL PRIMARY KEY,
926 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
927 org INT, --REFERENCES actor.org_unit (id),
930 field_name TEXT REFERENCES config.org_unit_setting_type (name)
933 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
934 Org Unit setting Logs
936 This table contains the most recent changes to each setting
937 in actor.org_unit_setting, allowing for mistakes to be undone.
938 This is NOT meant to be an auditor, but rather an undo/redo.
941 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
943 -- Only keeps the most recent five settings changes.
944 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
945 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
947 IF (TG_OP = 'UPDATE') THEN
949 ELSIF (TG_OP = 'INSERT') THEN
954 $oustl_limit$ LANGUAGE plpgsql;
956 CREATE TRIGGER limit_logs_oust
957 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
958 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
960 CREATE TABLE config.sms_carrier (
961 id SERIAL PRIMARY KEY,
965 active BOOLEAN DEFAULT TRUE
968 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
970 CREATE TABLE config.usr_activity_type (
971 id SERIAL PRIMARY KEY,
975 label TEXT NOT NULL, -- i18n
976 egroup config.usr_activity_group NOT NULL,
977 enabled BOOL NOT NULL DEFAULT TRUE,
978 transient BOOL NOT NULL DEFAULT FALSE,
979 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
982 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
983 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
985 CREATE TABLE config.filter_dialog_interface (
986 key TEXT PRIMARY KEY,
990 CREATE TABLE config.filter_dialog_filter_set (
991 id SERIAL PRIMARY KEY,
993 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
994 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
995 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
996 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
997 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
998 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1001 CREATE TABLE config.best_hold_order(
1002 id SERIAL PRIMARY KEY,
1003 name TEXT UNIQUE, -- i18n
1004 pprox INT, -- copy capture <-> pickup lib prox
1005 hprox INT, -- copy circ lib <-> request lib prox
1006 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1007 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1008 priority INT, -- group hold priority
1009 cut INT, -- cut-in-line
1010 depth INT, -- selection depth
1011 htime INT, -- time since last home-lib circ exceeds org-unit setting
1012 rtime INT, -- request time
1013 shtime INT -- time since copy last trip home exceeds org-unit setting
1016 -- At least one of these columns must contain a non-null value
1017 ALTER TABLE config.best_hold_order ADD CHECK ((
1018 pprox IS NOT NULL OR
1019 hprox IS NOT NULL OR
1020 aprox IS NOT NULL OR
1021 priority IS NOT NULL OR
1023 depth IS NOT NULL OR
1024 htime IS NOT NULL OR