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 ('0669', :eg_version); -- tsbere/miker
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,
125 staff_alert BOOL NOT NULL DEFAULT FALSE,
128 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert)
129 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW', TRUE);
130 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert)
131 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW', TRUE);
132 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert)
133 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC', TRUE);
134 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert)
135 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW', TRUE);
137 INSERT INTO config.standing_penalty (id,name,label,staff_alert) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks',TRUE);
138 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
139 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC', TRUE);
140 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD', TRUE);
141 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW', TRUE);
142 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW', TRUE);
143 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW', TRUE);
144 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD', TRUE);
145 INSERT INTO config.standing_penalty (id,name,label,block_list,staff_alert) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW', TRUE);
146 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
147 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
148 INSERT INTO config.standing_penalty (id, name, label, staff_alert) VALUES
151 'INVALID_PATRON_EMAIL_ADDRESS',
154 'Patron had an invalid email address',
162 'INVALID_PATRON_DAY_PHONE',
165 'Patron had an invalid daytime phone number',
173 'INVALID_PATRON_EVENING_PHONE',
176 'Patron had an invalid evening phone number',
184 'INVALID_PATRON_OTHER_PHONE',
187 'Patron had an invalid other phone number',
195 SELECT SETVAL('config.standing_penalty_id_seq', 100);
197 CREATE TABLE config.xml_transform (
198 name TEXT PRIMARY KEY,
199 namespace_uri TEXT NOT NULL,
200 prefix TEXT NOT NULL,
204 CREATE TABLE config.biblio_fingerprint (
205 id SERIAL PRIMARY KEY,
208 first_word BOOL NOT NULL DEFAULT FALSE,
209 format TEXT NOT NULL DEFAULT 'marcxml'
212 INSERT INTO config.biblio_fingerprint (name, xpath, format)
215 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
216 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
217 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
218 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
219 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
223 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
226 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
227 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
228 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
229 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
230 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
235 CREATE TABLE config.metabib_class (
236 name TEXT PRIMARY KEY,
237 label TEXT NOT NULL UNIQUE
240 CREATE TABLE config.metabib_field (
241 id SERIAL PRIMARY KEY,
242 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
246 weight INT NOT NULL DEFAULT 1,
247 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
248 search_field BOOL NOT NULL DEFAULT TRUE,
249 facet_field BOOL NOT NULL DEFAULT FALSE,
252 COMMENT ON TABLE config.metabib_field IS $$
253 XPath used for record indexing ingest
255 This table contains the XPath used to chop up MODS into its
256 indexable parts. Each XPath entry is named and assigned to
257 a "class" of either title, subject, author, keyword, series
261 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
263 CREATE TABLE config.metabib_search_alias (
264 alias TEXT PRIMARY KEY,
265 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
266 field INT REFERENCES config.metabib_field (id)
269 CREATE TABLE config.non_cataloged_type (
270 id SERIAL PRIMARY KEY,
271 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
273 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
274 in_house BOOL NOT NULL DEFAULT FALSE,
275 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
277 COMMENT ON TABLE config.non_cataloged_type IS $$
278 Types of valid non-cataloged items.
281 CREATE TABLE config.identification_type (
282 id SERIAL PRIMARY KEY,
283 name TEXT NOT NULL UNIQUE
285 COMMENT ON TABLE config.identification_type IS $$
286 Types of valid patron identification.
288 Each patron must display at least one valid form of identification
289 in order to get a library card. This table lists those forms.
292 CREATE TABLE config.rule_circ_duration (
293 id SERIAL PRIMARY KEY,
294 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
295 extended INTERVAL NOT NULL,
296 normal INTERVAL NOT NULL,
297 shrt INTERVAL NOT NULL,
298 max_renewals INT NOT NULL
300 COMMENT ON TABLE config.rule_circ_duration IS $$
301 Circulation Duration rules
303 Each circulation is given a duration based on one of these rules.
306 CREATE TABLE config.hard_due_date (
307 id SERIAL PRIMARY KEY,
308 name TEXT NOT NULL UNIQUE,
309 ceiling_date TIMESTAMPTZ NOT NULL,
310 forceto BOOL NOT NULL,
311 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
314 CREATE TABLE config.hard_due_date_values (
315 id SERIAL PRIMARY KEY,
316 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
317 DEFERRABLE INITIALLY DEFERRED,
318 ceiling_date TIMESTAMPTZ NOT NULL,
319 active_date TIMESTAMPTZ NOT NULL
322 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
324 temp_value config.hard_due_date_values%ROWTYPE;
328 SELECT DISTINCT ON (hard_due_date) *
329 FROM config.hard_due_date_values
330 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
331 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
333 UPDATE config.hard_due_date
334 SET ceiling_date = temp_value.ceiling_date
335 WHERE id = temp_value.hard_due_date
336 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
339 updated := updated + 1;
345 $func$ LANGUAGE plpgsql;
347 CREATE TABLE config.rule_max_fine (
348 id SERIAL PRIMARY KEY,
349 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
350 amount NUMERIC(6,2) NOT NULL,
351 is_percent BOOL NOT NULL DEFAULT FALSE
353 COMMENT ON TABLE config.rule_max_fine IS $$
354 Circulation Max Fine rules
356 Each circulation is given a maximum fine based on one of
360 CREATE TABLE config.rule_recurring_fine (
361 id SERIAL PRIMARY KEY,
362 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
363 high NUMERIC(6,2) NOT NULL,
364 normal NUMERIC(6,2) NOT NULL,
365 low NUMERIC(6,2) NOT NULL,
366 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
367 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
369 COMMENT ON TABLE config.rule_recurring_fine IS $$
370 Circulation Recurring Fine rules
372 Each circulation is given a recurring fine amount based on one of
373 these rules. Note that it is recommended to run the fine generator
374 (from cron) at least as frequently as the lowest recurrence interval
375 used by your circulation rules so that accrued fines will be up
380 CREATE TABLE config.rule_age_hold_protect (
381 id SERIAL PRIMARY KEY,
382 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
383 age INTERVAL NOT NULL,
386 COMMENT ON TABLE config.rule_age_hold_protect IS $$
387 Hold Item Age Protection rules
389 A hold request can only capture new(ish) items when they are
390 within a particular proximity of the pickup_lib of the request.
391 The proximity ('prox' column) is calculated by counting
392 the number of tree edges between the pickup_lib and either the
393 owning_lib or circ_lib of the copy that could fulfill the hold,
394 as determined by the distance_is_from_owner value of the hold matrix
395 rule controlling the hold request.
398 CREATE TABLE config.copy_status (
399 id SERIAL PRIMARY KEY,
400 name TEXT NOT NULL UNIQUE,
401 holdable BOOL NOT NULL DEFAULT FALSE,
402 opac_visible BOOL NOT NULL DEFAULT FALSE,
403 copy_active BOOL NOT NULL DEFAULT FALSE
405 COMMENT ON TABLE config.copy_status IS $$
408 The available copy statuses, and whether a copy in that
409 status is available for hold request capture. 0 (zero) is
410 the only special number in this set, meaning that the item
411 is available for immediate checkout, and is counted as available
414 Statuses with an ID below 100 are not removable, and have special
415 meaning in the code. Do not change them except to translate the
418 You may add and remove statuses above 100, and these can be used
419 to remove items from normal circulation without affecting the rest
420 of the copy's values or its location.
423 CREATE TABLE config.net_access_level (
424 id SERIAL PRIMARY KEY,
425 name TEXT NOT NULL UNIQUE
427 COMMENT ON TABLE config.net_access_level IS $$
428 Patron Network Access level
430 This will be used to inform the in-library firewall of how much
431 internet access the using patron should be allowed.
435 CREATE TABLE config.remote_account (
436 id SERIAL PRIMARY KEY,
438 host TEXT NOT NULL, -- name or IP, :port optional
439 username TEXT, -- optional, since we could default to $USER
440 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
441 account TEXT, -- aka profile or FTP "account" command
442 path TEXT, -- aka directory
443 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
444 last_activity TIMESTAMP WITH TIME ZONE
447 CREATE TABLE config.marc21_rec_type_map (
448 code TEXT PRIMARY KEY,
449 type_val TEXT NOT NULL,
450 blvl_val TEXT NOT NULL
453 CREATE TABLE config.marc21_ff_pos_map (
454 id SERIAL PRIMARY KEY,
455 fixed_field TEXT NOT NULL,
457 rec_type TEXT NOT NULL,
458 start_pos INT NOT NULL,
460 default_val TEXT NOT NULL DEFAULT ' '
463 CREATE TABLE config.marc21_physical_characteristic_type_map (
464 ptype_key TEXT PRIMARY KEY,
465 label TEXT NOT NULL -- I18N
468 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
469 id SERIAL PRIMARY KEY,
470 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
471 subfield TEXT NOT NULL,
472 start_pos INT NOT NULL,
474 label TEXT NOT NULL -- I18N
477 CREATE TABLE config.marc21_physical_characteristic_value_map (
478 id SERIAL PRIMARY KEY,
480 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
481 label TEXT NOT NULL -- I18N
485 CREATE TABLE config.z3950_source (
486 name TEXT PRIMARY KEY,
487 label TEXT NOT NULL UNIQUE,
491 record_format TEXT NOT NULL DEFAULT 'FI',
492 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
493 auth BOOL NOT NULL DEFAULT TRUE,
494 use_perm INT -- REFERENCES permission.perm_list (id)
497 COMMENT ON TABLE config.z3950_source IS $$
500 Each row in this table represents a database searchable via Z39.50.
503 COMMENT ON COLUMN config.z3950_source.record_format IS $$
507 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
508 Z39.50 preferred record syntax..
511 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
512 If set, this permission is required for the source to be listed in the staff
513 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
516 CREATE TABLE config.z3950_attr (
517 id SERIAL PRIMARY KEY,
518 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
523 truncation INT NOT NULL DEFAULT 0,
524 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
527 CREATE TABLE config.i18n_locale (
528 code TEXT PRIMARY KEY,
529 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
530 name TEXT UNIQUE NOT NULL,
534 CREATE TABLE config.i18n_core (
535 id BIGSERIAL PRIMARY KEY,
536 fq_field TEXT NOT NULL,
537 identity_value TEXT NOT NULL,
538 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
542 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
544 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
548 UPDATE config.i18n_core
549 SET identity_value = $$ || quote_literal(new_ident) || $$
550 WHERE fq_field LIKE '$$ || hint || $$.%'
551 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
556 $_$ LANGUAGE PLPGSQL;
558 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
560 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
563 $_$ LANGUAGE PLPGSQL;
565 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
567 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
570 $_$ LANGUAGE PLPGSQL;
572 CREATE TABLE config.billing_type (
573 id SERIAL PRIMARY KEY,
575 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
576 default_price NUMERIC(6,2),
577 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
580 CREATE TABLE config.settings_group (
581 name TEXT PRIMARY KEY,
582 label TEXT UNIQUE NOT NULL -- I18N
585 CREATE TABLE config.org_unit_setting_type (
586 name TEXT PRIMARY KEY,
587 label TEXT UNIQUE NOT NULL,
588 grp TEXT REFERENCES config.settings_group (name),
590 datatype TEXT NOT NULL DEFAULT 'string',
595 -- define valid datatypes
597 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
598 ( 'bool', 'integer', 'float', 'currency', 'interval',
599 'date', 'string', 'object', 'array', 'link' ) ),
601 -- fm_class is meaningful only for 'link' datatype
603 CONSTRAINT coust_no_empty_link CHECK
604 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
605 ( datatype <> 'link' AND fm_class IS NULL ) )
608 CREATE TABLE config.usr_setting_type (
610 name TEXT PRIMARY KEY,
611 opac_visible BOOL NOT NULL DEFAULT FALSE,
612 label TEXT UNIQUE NOT NULL,
614 grp TEXT REFERENCES config.settings_group (name),
615 datatype TEXT NOT NULL DEFAULT 'string',
619 -- define valid datatypes
621 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
622 ( 'bool', 'integer', 'float', 'currency', 'interval',
623 'date', 'string', 'object', 'array', 'link' ) ),
626 -- fm_class is meaningful only for 'link' datatype
628 CONSTRAINT coust_no_empty_link CHECK
629 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
630 ( datatype <> 'link' AND fm_class IS NULL ) )
634 -- Some handy functions, based on existing ones, to provide optional ingest normalization
636 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
637 SELECT SUBSTRING($1,$2);
638 $func$ LANGUAGE SQL STRICT IMMUTABLE;
640 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
641 SELECT SUBSTRING($1,1,$2);
642 $func$ LANGUAGE SQL STRICT IMMUTABLE;
644 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
645 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
646 $func$ LANGUAGE SQL STRICT IMMUTABLE;
648 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
649 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
650 $func$ LANGUAGE SQL STRICT IMMUTABLE;
652 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
653 SELECT approximate_date( $1, '0');
654 $func$ LANGUAGE SQL STRICT IMMUTABLE;
656 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
657 SELECT approximate_date( $1, '9');
658 $func$ LANGUAGE SQL STRICT IMMUTABLE;
660 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
661 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
662 $func$ LANGUAGE SQL STRICT IMMUTABLE;
664 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
665 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
666 $func$ LANGUAGE SQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
673 # Find the first ISBN, force it to ISBN13 and return it
677 foreach my $word (split(/\s/, $input)) {
678 my $isbn = Business::ISBN->new($word);
680 # First check the checksum; if it is not valid, fix it and add the original
681 # bad-checksum ISBN to the output
682 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
683 $isbn->fix_checksum();
686 # If we now have a valid ISBN, force it to ISBN13 and return it
687 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
690 $func$ LANGUAGE PLPERLU;
692 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
693 Inspired by translate_isbn1013
695 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
696 version without hypens and with a repaired checksum if the checksum was bad
700 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
705 # For each ISBN found in a single string containing a set of ISBNs:
706 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
707 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
712 foreach my $word (split(/\s/, $input)) {
713 my $isbn = Business::ISBN->new($word);
715 # First check the checksum; if it is not valid, fix it and add the original
716 # bad-checksum ISBN to the output
717 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
718 $output .= $isbn->isbn() . " ";
719 $isbn->fix_checksum();
722 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
723 # and add the normalized original ISBN to the output
724 if ($isbn && $isbn->is_valid()) {
725 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
726 $output .= $isbn->isbn . " ";
728 # If we successfully converted the ISBN to its counterpart, add the
729 # converted ISBN to the output as well
730 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
733 return $output if $output;
735 # If there were no valid ISBNs, just return the raw input
737 $func$ LANGUAGE PLPERLU;
739 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
740 The translate_isbn1013 function takes an input ISBN and returns the
741 following in a single space-delimited string if the input ISBN is valid:
742 - The normalized input ISBN (hyphens stripped)
743 - The normalized input ISBN with a fixed checksum if the checksum was bad
744 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
747 -- And ... a table in which to register them
749 CREATE TABLE config.index_normalizer (
750 id SERIAL PRIMARY KEY,
751 name TEXT UNIQUE NOT NULL,
754 param_count INT NOT NULL DEFAULT 0
757 CREATE TABLE config.metabib_field_index_norm_map (
758 id SERIAL PRIMARY KEY,
759 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
760 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
762 pos INT NOT NULL DEFAULT 0
765 CREATE TABLE config.record_attr_definition (
766 name TEXT PRIMARY KEY,
767 label TEXT NOT NULL, -- I18N
769 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
770 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
772 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
773 tag TEXT, -- LIKE format
774 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
776 -- This is used for both tag/sf and xpath entries
779 -- For xpath-extracted attrs
781 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
786 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
788 -- For phys-char fields
789 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
792 CREATE TABLE config.record_attr_index_norm_map (
793 id SERIAL PRIMARY KEY,
794 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
795 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
797 pos INT NOT NULL DEFAULT 0
800 CREATE TABLE config.coded_value_map (
801 id SERIAL PRIMARY KEY,
802 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
808 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
809 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
810 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
811 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
812 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
813 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
814 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
816 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
824 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
826 SELECT n.func AS func,
827 n.param_count AS param_count,
829 FROM config.index_normalizer n
830 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
831 WHERE field = NEW.field AND m.pos < 0
833 EXECUTE 'SELECT ' || normalizer.func || '(' ||
834 quote_literal( value ) ||
836 WHEN normalizer.param_count > 0
837 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
847 IF NEW.index_vector = ''::tsvector THEN
851 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
853 SELECT n.func AS func,
854 n.param_count AS param_count,
856 FROM config.index_normalizer n
857 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
858 WHERE field = NEW.field AND m.pos >= 0
860 EXECUTE 'SELECT ' || normalizer.func || '(' ||
861 quote_literal( value ) ||
863 WHEN normalizer.param_count > 0
864 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
872 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
873 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
875 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
882 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
883 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
884 SELECT DISTINCT l.version
885 FROM config.upgrade_log l
886 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
887 WHERE d.db_patch = $1
890 -- List applied db patches that are superseded by (and block the application of) my_db_patch
891 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
892 SELECT DISTINCT l.version
893 FROM config.upgrade_log l
894 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
895 WHERE d.db_patch = $1
898 -- List applied db patches that deprecates (and block the application of) my_db_patch
899 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
901 FROM config.db_patch_dependencies
902 WHERE ARRAY[$1]::TEXT[] && deprecates
905 -- List applied db patches that supersedes (and block the application of) my_db_patch
906 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
908 FROM config.db_patch_dependencies
909 WHERE ARRAY[$1]::TEXT[] && supersedes
912 -- Make sure that no deprecated or superseded db patches are currently applied
913 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
915 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
917 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
919 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
921 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
924 -- Raise an exception if there are, in fact, dep/sup conflict
925 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
930 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
931 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
932 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
934 Upgrade script % can not be applied:
935 applied deprecated scripts %
936 applied superseded scripts %
940 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
941 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
942 evergreen.upgrade_list_applied_deprecated(my_db_patch),
943 evergreen.upgrade_list_applied_superseded(my_db_patch);
946 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
951 CREATE TABLE config.barcode_completion (
952 id SERIAL PRIMARY KEY,
953 active BOOL NOT NULL DEFAULT true,
954 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
957 length INT NOT NULL DEFAULT 0,
959 padding_end BOOL NOT NULL DEFAULT false,
960 asset BOOL NOT NULL DEFAULT true,
961 actor BOOL NOT NULL DEFAULT true
964 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
966 -- Add support for logging, only keep the most recent five rows for each category.
969 CREATE TABLE config.org_unit_setting_type_log (
970 id BIGSERIAL PRIMARY KEY,
971 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
972 org INT, --REFERENCES actor.org_unit (id),
975 field_name TEXT REFERENCES config.org_unit_setting_type (name)
978 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
979 Org Unit setting Logs
981 This table contains the most recent changes to each setting
982 in actor.org_unit_setting, allowing for mistakes to be undone.
983 This is NOT meant to be an auditor, but rather an undo/redo.
986 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
988 -- Only keeps the most recent five settings changes.
989 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
990 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
992 IF (TG_OP = 'UPDATE') THEN
994 ELSIF (TG_OP = 'INSERT') THEN
999 $oustl_limit$ LANGUAGE plpgsql;
1001 CREATE TRIGGER limit_logs_oust
1002 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1003 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1005 CREATE TABLE config.sms_carrier (
1006 id SERIAL PRIMARY KEY,
1010 active BOOLEAN DEFAULT TRUE