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');
50 CREATE TABLE config.global_flag (
52 ) INHERITS (config.internal_flag);
53 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
55 CREATE TABLE config.upgrade_log (
56 version TEXT PRIMARY KEY,
57 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
61 CREATE TABLE config.db_patch_dependencies (
62 db_patch TEXT PRIMARY KEY,
67 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
73 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
75 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
81 CREATE TRIGGER no_overlapping_sups
82 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
83 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
85 CREATE TRIGGER no_overlapping_deps
86 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
87 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
89 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0658', :eg_version); -- jamesrf/dbs
91 CREATE TABLE config.bib_source (
92 id SERIAL PRIMARY KEY,
93 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
94 source TEXT NOT NULL UNIQUE,
95 transcendant BOOL NOT NULL DEFAULT FALSE,
96 can_have_copies BOOL NOT NULL DEFAULT TRUE
98 COMMENT ON TABLE config.bib_source IS $$
99 This is table is used to set up the relative "quality" of each
100 MARC source, such as OCLC. Also identifies "transcendant" sources,
101 i.e., sources of bib records that should display in the OPAC
102 even if no copies or located URIs are attached. Also indicates if
103 the source is allowed to have actual copies on its bibs. Volumes
104 for targeted URIs are unaffected by this setting.
107 CREATE TABLE config.standing (
108 id SERIAL PRIMARY KEY,
109 value TEXT NOT NULL UNIQUE
111 COMMENT ON TABLE config.standing IS $$
114 This table contains the values that can be applied to a patron
115 by a staff member. These values should not be changed, other
116 than for translation, as the ID column is currently a "magic
117 number" in the source. :(
120 CREATE TABLE config.standing_penalty (
121 id SERIAL PRIMARY KEY,
122 name TEXT NOT NULL UNIQUE,
127 INSERT INTO config.standing_penalty (id,name,label,block_list)
128 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
129 INSERT INTO config.standing_penalty (id,name,label,block_list)
130 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
131 INSERT INTO config.standing_penalty (id,name,label,block_list)
132 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
133 INSERT INTO config.standing_penalty (id,name,label,block_list)
134 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
136 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
137 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
138 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
139 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
140 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
141 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
142 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
143 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
144 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
145 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
146 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
148 SELECT SETVAL('config.standing_penalty_id_seq', 100);
150 CREATE TABLE config.xml_transform (
151 name TEXT PRIMARY KEY,
152 namespace_uri TEXT NOT NULL,
153 prefix TEXT NOT NULL,
157 CREATE TABLE config.biblio_fingerprint (
158 id SERIAL PRIMARY KEY,
161 first_word BOOL NOT NULL DEFAULT FALSE,
162 format TEXT NOT NULL DEFAULT 'marcxml'
165 INSERT INTO config.biblio_fingerprint (name, xpath, format)
168 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
169 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
170 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
171 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
172 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
176 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
179 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
180 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
181 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
182 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
183 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
188 CREATE TABLE config.metabib_class (
189 name TEXT PRIMARY KEY,
190 label TEXT NOT NULL UNIQUE
193 CREATE TABLE config.metabib_field (
194 id SERIAL PRIMARY KEY,
195 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
199 weight INT NOT NULL DEFAULT 1,
200 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
201 search_field BOOL NOT NULL DEFAULT TRUE,
202 facet_field BOOL NOT NULL DEFAULT FALSE,
205 COMMENT ON TABLE config.metabib_field IS $$
206 XPath used for record indexing ingest
208 This table contains the XPath used to chop up MODS into its
209 indexable parts. Each XPath entry is named and assigned to
210 a "class" of either title, subject, author, keyword, series
214 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
216 CREATE TABLE config.metabib_search_alias (
217 alias TEXT PRIMARY KEY,
218 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
219 field INT REFERENCES config.metabib_field (id)
222 CREATE TABLE config.non_cataloged_type (
223 id SERIAL PRIMARY KEY,
224 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
226 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
227 in_house BOOL NOT NULL DEFAULT FALSE,
228 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
230 COMMENT ON TABLE config.non_cataloged_type IS $$
231 Types of valid non-cataloged items.
234 CREATE TABLE config.identification_type (
235 id SERIAL PRIMARY KEY,
236 name TEXT NOT NULL UNIQUE
238 COMMENT ON TABLE config.identification_type IS $$
239 Types of valid patron identification.
241 Each patron must display at least one valid form of identification
242 in order to get a library card. This table lists those forms.
245 CREATE TABLE config.rule_circ_duration (
246 id SERIAL PRIMARY KEY,
247 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
248 extended INTERVAL NOT NULL,
249 normal INTERVAL NOT NULL,
250 shrt INTERVAL NOT NULL,
251 max_renewals INT NOT NULL
253 COMMENT ON TABLE config.rule_circ_duration IS $$
254 Circulation Duration rules
256 Each circulation is given a duration based on one of these rules.
259 CREATE TABLE config.hard_due_date (
260 id SERIAL PRIMARY KEY,
261 name TEXT NOT NULL UNIQUE,
262 ceiling_date TIMESTAMPTZ NOT NULL,
263 forceto BOOL NOT NULL,
264 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
267 CREATE TABLE config.hard_due_date_values (
268 id SERIAL PRIMARY KEY,
269 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
270 DEFERRABLE INITIALLY DEFERRED,
271 ceiling_date TIMESTAMPTZ NOT NULL,
272 active_date TIMESTAMPTZ NOT NULL
275 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
277 temp_value config.hard_due_date_values%ROWTYPE;
281 SELECT DISTINCT ON (hard_due_date) *
282 FROM config.hard_due_date_values
283 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
284 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
286 UPDATE config.hard_due_date
287 SET ceiling_date = temp_value.ceiling_date
288 WHERE id = temp_value.hard_due_date
289 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
292 updated := updated + 1;
298 $func$ LANGUAGE plpgsql;
300 CREATE TABLE config.rule_max_fine (
301 id SERIAL PRIMARY KEY,
302 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
303 amount NUMERIC(6,2) NOT NULL,
304 is_percent BOOL NOT NULL DEFAULT FALSE
306 COMMENT ON TABLE config.rule_max_fine IS $$
307 Circulation Max Fine rules
309 Each circulation is given a maximum fine based on one of
313 CREATE TABLE config.rule_recurring_fine (
314 id SERIAL PRIMARY KEY,
315 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
316 high NUMERIC(6,2) NOT NULL,
317 normal NUMERIC(6,2) NOT NULL,
318 low NUMERIC(6,2) NOT NULL,
319 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
320 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
322 COMMENT ON TABLE config.rule_recurring_fine IS $$
323 Circulation Recurring Fine rules
325 Each circulation is given a recurring fine amount based on one of
326 these rules. Note that it is recommended to run the fine generator
327 (from cron) at least as frequently as the lowest recurrence interval
328 used by your circulation rules so that accrued fines will be up
333 CREATE TABLE config.rule_age_hold_protect (
334 id SERIAL PRIMARY KEY,
335 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
336 age INTERVAL NOT NULL,
339 COMMENT ON TABLE config.rule_age_hold_protect IS $$
340 Hold Item Age Protection rules
342 A hold request can only capture new(ish) items when they are
343 within a particular proximity of the pickup_lib of the request.
344 The proximity ('prox' column) is calculated by counting
345 the number of tree edges between the pickup_lib and either the
346 owning_lib or circ_lib of the copy that could fulfill the hold,
347 as determined by the distance_is_from_owner value of the hold matrix
348 rule controlling the hold request.
351 CREATE TABLE config.copy_status (
352 id SERIAL PRIMARY KEY,
353 name TEXT NOT NULL UNIQUE,
354 holdable BOOL NOT NULL DEFAULT FALSE,
355 opac_visible BOOL NOT NULL DEFAULT FALSE,
356 copy_active BOOL NOT NULL DEFAULT FALSE
358 COMMENT ON TABLE config.copy_status IS $$
361 The available copy statuses, and whether a copy in that
362 status is available for hold request capture. 0 (zero) is
363 the only special number in this set, meaning that the item
364 is available for immediate checkout, and is counted as available
367 Statuses with an ID below 100 are not removable, and have special
368 meaning in the code. Do not change them except to translate the
371 You may add and remove statuses above 100, and these can be used
372 to remove items from normal circulation without affecting the rest
373 of the copy's values or its location.
376 CREATE TABLE config.net_access_level (
377 id SERIAL PRIMARY KEY,
378 name TEXT NOT NULL UNIQUE
380 COMMENT ON TABLE config.net_access_level IS $$
381 Patron Network Access level
383 This will be used to inform the in-library firewall of how much
384 internet access the using patron should be allowed.
388 CREATE TABLE config.remote_account (
389 id SERIAL PRIMARY KEY,
391 host TEXT NOT NULL, -- name or IP, :port optional
392 username TEXT, -- optional, since we could default to $USER
393 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
394 account TEXT, -- aka profile or FTP "account" command
395 path TEXT, -- aka directory
396 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
397 last_activity TIMESTAMP WITH TIME ZONE
400 CREATE TABLE config.marc21_rec_type_map (
401 code TEXT PRIMARY KEY,
402 type_val TEXT NOT NULL,
403 blvl_val TEXT NOT NULL
406 CREATE TABLE config.marc21_ff_pos_map (
407 id SERIAL PRIMARY KEY,
408 fixed_field TEXT NOT NULL,
410 rec_type TEXT NOT NULL,
411 start_pos INT NOT NULL,
413 default_val TEXT NOT NULL DEFAULT ' '
416 CREATE TABLE config.marc21_physical_characteristic_type_map (
417 ptype_key TEXT PRIMARY KEY,
418 label TEXT NOT NULL -- I18N
421 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
422 id SERIAL PRIMARY KEY,
423 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
424 subfield TEXT NOT NULL,
425 start_pos INT NOT NULL,
427 label TEXT NOT NULL -- I18N
430 CREATE TABLE config.marc21_physical_characteristic_value_map (
431 id SERIAL PRIMARY KEY,
433 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
434 label TEXT NOT NULL -- I18N
438 CREATE TABLE config.z3950_source (
439 name TEXT PRIMARY KEY,
440 label TEXT NOT NULL UNIQUE,
444 record_format TEXT NOT NULL DEFAULT 'FI',
445 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
446 auth BOOL NOT NULL DEFAULT TRUE,
447 use_perm INT -- REFERENCES permission.perm_list (id)
450 COMMENT ON TABLE config.z3950_source IS $$
453 Each row in this table represents a database searchable via Z39.50.
456 COMMENT ON COLUMN config.z3950_source.record_format IS $$
460 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
461 Z39.50 preferred record syntax..
464 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
465 If set, this permission is required for the source to be listed in the staff
466 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
469 CREATE TABLE config.z3950_attr (
470 id SERIAL PRIMARY KEY,
471 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
476 truncation INT NOT NULL DEFAULT 0,
477 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
480 CREATE TABLE config.i18n_locale (
481 code TEXT PRIMARY KEY,
482 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
483 name TEXT UNIQUE NOT NULL,
487 CREATE TABLE config.i18n_core (
488 id BIGSERIAL PRIMARY KEY,
489 fq_field TEXT NOT NULL,
490 identity_value TEXT NOT NULL,
491 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
495 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
497 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
501 UPDATE config.i18n_core
502 SET identity_value = $$ || quote_literal(new_ident) || $$
503 WHERE fq_field LIKE '$$ || hint || $$.%'
504 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
509 $_$ LANGUAGE PLPGSQL;
511 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
513 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
516 $_$ LANGUAGE PLPGSQL;
518 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
520 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
523 $_$ LANGUAGE PLPGSQL;
525 CREATE TABLE config.billing_type (
526 id SERIAL PRIMARY KEY,
528 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
529 default_price NUMERIC(6,2),
530 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
533 CREATE TABLE config.settings_group (
534 name TEXT PRIMARY KEY,
535 label TEXT UNIQUE NOT NULL -- I18N
538 CREATE TABLE config.org_unit_setting_type (
539 name TEXT PRIMARY KEY,
540 label TEXT UNIQUE NOT NULL,
541 grp TEXT REFERENCES config.settings_group (name),
543 datatype TEXT NOT NULL DEFAULT 'string',
548 -- define valid datatypes
550 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
551 ( 'bool', 'integer', 'float', 'currency', 'interval',
552 'date', 'string', 'object', 'array', 'link' ) ),
554 -- fm_class is meaningful only for 'link' datatype
556 CONSTRAINT coust_no_empty_link CHECK
557 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
558 ( datatype <> 'link' AND fm_class IS NULL ) )
561 CREATE TABLE config.usr_setting_type (
563 name TEXT PRIMARY KEY,
564 opac_visible BOOL NOT NULL DEFAULT FALSE,
565 label TEXT UNIQUE NOT NULL,
567 grp TEXT REFERENCES config.settings_group (name),
568 datatype TEXT NOT NULL DEFAULT 'string',
572 -- define valid datatypes
574 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
575 ( 'bool', 'integer', 'float', 'currency', 'interval',
576 'date', 'string', 'object', 'array', 'link' ) ),
579 -- fm_class is meaningful only for 'link' datatype
581 CONSTRAINT coust_no_empty_link CHECK
582 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
583 ( datatype <> 'link' AND fm_class IS NULL ) )
587 -- Some handy functions, based on existing ones, to provide optional ingest normalization
589 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
590 SELECT SUBSTRING($1,$2);
591 $func$ LANGUAGE SQL STRICT IMMUTABLE;
593 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
594 SELECT SUBSTRING($1,1,$2);
595 $func$ LANGUAGE SQL STRICT IMMUTABLE;
597 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
598 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
599 $func$ LANGUAGE SQL STRICT IMMUTABLE;
601 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
602 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
603 $func$ LANGUAGE SQL STRICT IMMUTABLE;
605 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
606 SELECT approximate_date( $1, '0');
607 $func$ LANGUAGE SQL STRICT IMMUTABLE;
609 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
610 SELECT approximate_date( $1, '9');
611 $func$ LANGUAGE SQL STRICT IMMUTABLE;
613 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
614 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
615 $func$ LANGUAGE SQL STRICT IMMUTABLE;
617 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
618 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
619 $func$ LANGUAGE SQL STRICT IMMUTABLE;
621 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
626 # Find the first ISBN, force it to ISBN13 and return it
630 foreach my $word (split(/\s/, $input)) {
631 my $isbn = Business::ISBN->new($word);
633 # First check the checksum; if it is not valid, fix it and add the original
634 # bad-checksum ISBN to the output
635 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
636 $isbn->fix_checksum();
639 # If we now have a valid ISBN, force it to ISBN13 and return it
640 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
643 $func$ LANGUAGE PLPERLU;
645 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
646 Inspired by translate_isbn1013
648 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
649 version without hypens and with a repaired checksum if the checksum was bad
653 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
658 # For each ISBN found in a single string containing a set of ISBNs:
659 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
660 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
665 foreach my $word (split(/\s/, $input)) {
666 my $isbn = Business::ISBN->new($word);
668 # First check the checksum; if it is not valid, fix it and add the original
669 # bad-checksum ISBN to the output
670 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
671 $output .= $isbn->isbn() . " ";
672 $isbn->fix_checksum();
675 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
676 # and add the normalized original ISBN to the output
677 if ($isbn && $isbn->is_valid()) {
678 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
679 $output .= $isbn->isbn . " ";
681 # If we successfully converted the ISBN to its counterpart, add the
682 # converted ISBN to the output as well
683 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
686 return $output if $output;
688 # If there were no valid ISBNs, just return the raw input
690 $func$ LANGUAGE PLPERLU;
692 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
693 The translate_isbn1013 function takes an input ISBN and returns the
694 following in a single space-delimited string if the input ISBN is valid:
695 - The normalized input ISBN (hyphens stripped)
696 - The normalized input ISBN with a fixed checksum if the checksum was bad
697 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
700 -- And ... a table in which to register them
702 CREATE TABLE config.index_normalizer (
703 id SERIAL PRIMARY KEY,
704 name TEXT UNIQUE NOT NULL,
707 param_count INT NOT NULL DEFAULT 0
710 CREATE TABLE config.metabib_field_index_norm_map (
711 id SERIAL PRIMARY KEY,
712 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
713 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
715 pos INT NOT NULL DEFAULT 0
718 CREATE TABLE config.record_attr_definition (
719 name TEXT PRIMARY KEY,
720 label TEXT NOT NULL, -- I18N
722 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
723 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
725 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
726 tag TEXT, -- LIKE format
727 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
729 -- This is used for both tag/sf and xpath entries
732 -- For xpath-extracted attrs
734 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
739 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
741 -- For phys-char fields
742 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
745 CREATE TABLE config.record_attr_index_norm_map (
746 id SERIAL PRIMARY KEY,
747 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
748 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
750 pos INT NOT NULL DEFAULT 0
753 CREATE TABLE config.coded_value_map (
754 id SERIAL PRIMARY KEY,
755 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
761 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
762 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
763 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
764 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
765 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
766 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
767 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
769 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
777 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
779 SELECT n.func AS func,
780 n.param_count AS param_count,
782 FROM config.index_normalizer n
783 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
784 WHERE field = NEW.field AND m.pos < 0
786 EXECUTE 'SELECT ' || normalizer.func || '(' ||
787 quote_literal( value ) ||
789 WHEN normalizer.param_count > 0
790 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
800 IF NEW.index_vector = ''::tsvector THEN
804 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
806 SELECT n.func AS func,
807 n.param_count AS param_count,
809 FROM config.index_normalizer n
810 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
811 WHERE field = NEW.field AND m.pos >= 0
813 EXECUTE 'SELECT ' || normalizer.func || '(' ||
814 quote_literal( value ) ||
816 WHEN normalizer.param_count > 0
817 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
825 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
826 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
828 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
835 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
836 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
837 SELECT DISTINCT l.version
838 FROM config.upgrade_log l
839 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
840 WHERE d.db_patch = $1
843 -- List applied db patches that are superseded by (and block the application of) my_db_patch
844 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
845 SELECT DISTINCT l.version
846 FROM config.upgrade_log l
847 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
848 WHERE d.db_patch = $1
851 -- List applied db patches that deprecates (and block the application of) my_db_patch
852 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
854 FROM config.db_patch_dependencies
855 WHERE ARRAY[$1]::TEXT[] && deprecates
858 -- List applied db patches that supersedes (and block the application of) my_db_patch
859 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
861 FROM config.db_patch_dependencies
862 WHERE ARRAY[$1]::TEXT[] && supersedes
865 -- Make sure that no deprecated or superseded db patches are currently applied
866 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
868 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
870 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
872 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
874 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
877 -- Raise an exception if there are, in fact, dep/sup conflict
878 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
883 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
884 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
885 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
887 Upgrade script % can not be applied:
888 applied deprecated scripts %
889 applied superseded scripts %
893 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
894 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
895 evergreen.upgrade_list_applied_deprecated(my_db_patch),
896 evergreen.upgrade_list_applied_superseded(my_db_patch);
899 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
904 CREATE TABLE config.barcode_completion (
905 id SERIAL PRIMARY KEY,
906 active BOOL NOT NULL DEFAULT true,
907 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
910 length INT NOT NULL DEFAULT 0,
912 padding_end BOOL NOT NULL DEFAULT false,
913 asset BOOL NOT NULL DEFAULT true,
914 actor BOOL NOT NULL DEFAULT true
917 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
919 -- Add support for logging, only keep the most recent five rows for each category.
922 CREATE TABLE config.org_unit_setting_type_log (
923 id BIGSERIAL PRIMARY KEY,
924 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
925 org INT, --REFERENCES actor.org_unit (id),
928 field_name TEXT REFERENCES config.org_unit_setting_type (name)
931 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
932 Org Unit setting Logs
934 This table contains the most recent changes to each setting
935 in actor.org_unit_setting, allowing for mistakes to be undone.
936 This is NOT meant to be an auditor, but rather an undo/redo.
939 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
941 -- Only keeps the most recent five settings changes.
942 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
943 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
945 IF (TG_OP = 'UPDATE') THEN
947 ELSIF (TG_OP = 'INSERT') THEN
952 $oustl_limit$ LANGUAGE plpgsql;
954 CREATE TRIGGER limit_logs_oust
955 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
956 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();