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 ('0670', :eg_version); -- berick/senator
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, org_depth) VALUES
151 'INVALID_PATRON_EMAIL_ADDRESS',
154 'Patron had an invalid email address',
163 'INVALID_PATRON_DAY_PHONE',
166 'Patron had an invalid daytime phone number',
175 'INVALID_PATRON_EVENING_PHONE',
178 'Patron had an invalid evening phone number',
187 'INVALID_PATRON_OTHER_PHONE',
190 'Patron had an invalid other phone number',
199 SELECT SETVAL('config.standing_penalty_id_seq', 100);
201 CREATE TABLE config.xml_transform (
202 name TEXT PRIMARY KEY,
203 namespace_uri TEXT NOT NULL,
204 prefix TEXT NOT NULL,
208 CREATE TABLE config.biblio_fingerprint (
209 id SERIAL PRIMARY KEY,
212 first_word BOOL NOT NULL DEFAULT FALSE,
213 format TEXT NOT NULL DEFAULT 'marcxml'
216 INSERT INTO config.biblio_fingerprint (name, xpath, format)
219 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
220 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
221 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
222 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
223 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
227 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
230 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
231 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
232 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
233 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
234 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
239 CREATE TABLE config.metabib_class (
240 name TEXT PRIMARY KEY,
241 label TEXT NOT NULL UNIQUE
244 CREATE TABLE config.metabib_field (
245 id SERIAL PRIMARY KEY,
246 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
250 weight INT NOT NULL DEFAULT 1,
251 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
252 search_field BOOL NOT NULL DEFAULT TRUE,
253 facet_field BOOL NOT NULL DEFAULT FALSE,
256 COMMENT ON TABLE config.metabib_field IS $$
257 XPath used for record indexing ingest
259 This table contains the XPath used to chop up MODS into its
260 indexable parts. Each XPath entry is named and assigned to
261 a "class" of either title, subject, author, keyword, series
265 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
267 CREATE TABLE config.metabib_search_alias (
268 alias TEXT PRIMARY KEY,
269 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
270 field INT REFERENCES config.metabib_field (id)
273 CREATE TABLE config.non_cataloged_type (
274 id SERIAL PRIMARY KEY,
275 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
277 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
278 in_house BOOL NOT NULL DEFAULT FALSE,
279 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
281 COMMENT ON TABLE config.non_cataloged_type IS $$
282 Types of valid non-cataloged items.
285 CREATE TABLE config.identification_type (
286 id SERIAL PRIMARY KEY,
287 name TEXT NOT NULL UNIQUE
289 COMMENT ON TABLE config.identification_type IS $$
290 Types of valid patron identification.
292 Each patron must display at least one valid form of identification
293 in order to get a library card. This table lists those forms.
296 CREATE TABLE config.rule_circ_duration (
297 id SERIAL PRIMARY KEY,
298 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
299 extended INTERVAL NOT NULL,
300 normal INTERVAL NOT NULL,
301 shrt INTERVAL NOT NULL,
302 max_renewals INT NOT NULL
304 COMMENT ON TABLE config.rule_circ_duration IS $$
305 Circulation Duration rules
307 Each circulation is given a duration based on one of these rules.
310 CREATE TABLE config.hard_due_date (
311 id SERIAL PRIMARY KEY,
312 name TEXT NOT NULL UNIQUE,
313 ceiling_date TIMESTAMPTZ NOT NULL,
314 forceto BOOL NOT NULL,
315 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
318 CREATE TABLE config.hard_due_date_values (
319 id SERIAL PRIMARY KEY,
320 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
321 DEFERRABLE INITIALLY DEFERRED,
322 ceiling_date TIMESTAMPTZ NOT NULL,
323 active_date TIMESTAMPTZ NOT NULL
326 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
328 temp_value config.hard_due_date_values%ROWTYPE;
332 SELECT DISTINCT ON (hard_due_date) *
333 FROM config.hard_due_date_values
334 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
335 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
337 UPDATE config.hard_due_date
338 SET ceiling_date = temp_value.ceiling_date
339 WHERE id = temp_value.hard_due_date
340 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
343 updated := updated + 1;
349 $func$ LANGUAGE plpgsql;
351 CREATE TABLE config.rule_max_fine (
352 id SERIAL PRIMARY KEY,
353 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
354 amount NUMERIC(6,2) NOT NULL,
355 is_percent BOOL NOT NULL DEFAULT FALSE
357 COMMENT ON TABLE config.rule_max_fine IS $$
358 Circulation Max Fine rules
360 Each circulation is given a maximum fine based on one of
364 CREATE TABLE config.rule_recurring_fine (
365 id SERIAL PRIMARY KEY,
366 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
367 high NUMERIC(6,2) NOT NULL,
368 normal NUMERIC(6,2) NOT NULL,
369 low NUMERIC(6,2) NOT NULL,
370 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
371 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
373 COMMENT ON TABLE config.rule_recurring_fine IS $$
374 Circulation Recurring Fine rules
376 Each circulation is given a recurring fine amount based on one of
377 these rules. Note that it is recommended to run the fine generator
378 (from cron) at least as frequently as the lowest recurrence interval
379 used by your circulation rules so that accrued fines will be up
384 CREATE TABLE config.rule_age_hold_protect (
385 id SERIAL PRIMARY KEY,
386 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
387 age INTERVAL NOT NULL,
390 COMMENT ON TABLE config.rule_age_hold_protect IS $$
391 Hold Item Age Protection rules
393 A hold request can only capture new(ish) items when they are
394 within a particular proximity of the pickup_lib of the request.
395 The proximity ('prox' column) is calculated by counting
396 the number of tree edges between the pickup_lib and either the
397 owning_lib or circ_lib of the copy that could fulfill the hold,
398 as determined by the distance_is_from_owner value of the hold matrix
399 rule controlling the hold request.
402 CREATE TABLE config.copy_status (
403 id SERIAL PRIMARY KEY,
404 name TEXT NOT NULL UNIQUE,
405 holdable BOOL NOT NULL DEFAULT FALSE,
406 opac_visible BOOL NOT NULL DEFAULT FALSE,
407 copy_active BOOL NOT NULL DEFAULT FALSE
409 COMMENT ON TABLE config.copy_status IS $$
412 The available copy statuses, and whether a copy in that
413 status is available for hold request capture. 0 (zero) is
414 the only special number in this set, meaning that the item
415 is available for immediate checkout, and is counted as available
418 Statuses with an ID below 100 are not removable, and have special
419 meaning in the code. Do not change them except to translate the
422 You may add and remove statuses above 100, and these can be used
423 to remove items from normal circulation without affecting the rest
424 of the copy's values or its location.
427 CREATE TABLE config.net_access_level (
428 id SERIAL PRIMARY KEY,
429 name TEXT NOT NULL UNIQUE
431 COMMENT ON TABLE config.net_access_level IS $$
432 Patron Network Access level
434 This will be used to inform the in-library firewall of how much
435 internet access the using patron should be allowed.
439 CREATE TABLE config.remote_account (
440 id SERIAL PRIMARY KEY,
442 host TEXT NOT NULL, -- name or IP, :port optional
443 username TEXT, -- optional, since we could default to $USER
444 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
445 account TEXT, -- aka profile or FTP "account" command
446 path TEXT, -- aka directory
447 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
448 last_activity TIMESTAMP WITH TIME ZONE
451 CREATE TABLE config.marc21_rec_type_map (
452 code TEXT PRIMARY KEY,
453 type_val TEXT NOT NULL,
454 blvl_val TEXT NOT NULL
457 CREATE TABLE config.marc21_ff_pos_map (
458 id SERIAL PRIMARY KEY,
459 fixed_field TEXT NOT NULL,
461 rec_type TEXT NOT NULL,
462 start_pos INT NOT NULL,
464 default_val TEXT NOT NULL DEFAULT ' '
467 CREATE TABLE config.marc21_physical_characteristic_type_map (
468 ptype_key TEXT PRIMARY KEY,
469 label TEXT NOT NULL -- I18N
472 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
473 id SERIAL PRIMARY KEY,
474 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
475 subfield TEXT NOT NULL,
476 start_pos INT NOT NULL,
478 label TEXT NOT NULL -- I18N
481 CREATE TABLE config.marc21_physical_characteristic_value_map (
482 id SERIAL PRIMARY KEY,
484 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
485 label TEXT NOT NULL -- I18N
489 CREATE TABLE config.z3950_source (
490 name TEXT PRIMARY KEY,
491 label TEXT NOT NULL UNIQUE,
495 record_format TEXT NOT NULL DEFAULT 'FI',
496 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
497 auth BOOL NOT NULL DEFAULT TRUE,
498 use_perm INT -- REFERENCES permission.perm_list (id)
501 COMMENT ON TABLE config.z3950_source IS $$
504 Each row in this table represents a database searchable via Z39.50.
507 COMMENT ON COLUMN config.z3950_source.record_format IS $$
511 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
512 Z39.50 preferred record syntax..
515 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
516 If set, this permission is required for the source to be listed in the staff
517 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
520 CREATE TABLE config.z3950_attr (
521 id SERIAL PRIMARY KEY,
522 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
527 truncation INT NOT NULL DEFAULT 0,
528 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
531 CREATE TABLE config.i18n_locale (
532 code TEXT PRIMARY KEY,
533 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
534 name TEXT UNIQUE NOT NULL,
538 CREATE TABLE config.i18n_core (
539 id BIGSERIAL PRIMARY KEY,
540 fq_field TEXT NOT NULL,
541 identity_value TEXT NOT NULL,
542 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
546 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
548 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
552 UPDATE config.i18n_core
553 SET identity_value = $$ || quote_literal(new_ident) || $$
554 WHERE fq_field LIKE '$$ || hint || $$.%'
555 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
560 $_$ LANGUAGE PLPGSQL;
562 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
564 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
567 $_$ LANGUAGE PLPGSQL;
569 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
571 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
574 $_$ LANGUAGE PLPGSQL;
576 CREATE TABLE config.billing_type (
577 id SERIAL PRIMARY KEY,
579 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
580 default_price NUMERIC(6,2),
581 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
584 CREATE TABLE config.settings_group (
585 name TEXT PRIMARY KEY,
586 label TEXT UNIQUE NOT NULL -- I18N
589 CREATE TABLE config.org_unit_setting_type (
590 name TEXT PRIMARY KEY,
591 label TEXT UNIQUE NOT NULL,
592 grp TEXT REFERENCES config.settings_group (name),
594 datatype TEXT NOT NULL DEFAULT 'string',
599 -- define valid datatypes
601 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
602 ( 'bool', 'integer', 'float', 'currency', 'interval',
603 'date', 'string', 'object', 'array', 'link' ) ),
605 -- fm_class is meaningful only for 'link' datatype
607 CONSTRAINT coust_no_empty_link CHECK
608 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
609 ( datatype <> 'link' AND fm_class IS NULL ) )
612 CREATE TABLE config.usr_setting_type (
614 name TEXT PRIMARY KEY,
615 opac_visible BOOL NOT NULL DEFAULT FALSE,
616 label TEXT UNIQUE NOT NULL,
618 grp TEXT REFERENCES config.settings_group (name),
619 datatype TEXT NOT NULL DEFAULT 'string',
623 -- define valid datatypes
625 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
626 ( 'bool', 'integer', 'float', 'currency', 'interval',
627 'date', 'string', 'object', 'array', 'link' ) ),
630 -- fm_class is meaningful only for 'link' datatype
632 CONSTRAINT coust_no_empty_link CHECK
633 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
634 ( datatype <> 'link' AND fm_class IS NULL ) )
638 -- Some handy functions, based on existing ones, to provide optional ingest normalization
640 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
641 SELECT SUBSTRING($1,$2);
642 $func$ LANGUAGE SQL STRICT IMMUTABLE;
644 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
645 SELECT SUBSTRING($1,1,$2);
646 $func$ LANGUAGE SQL STRICT IMMUTABLE;
648 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
649 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
650 $func$ LANGUAGE SQL STRICT IMMUTABLE;
652 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
653 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
654 $func$ LANGUAGE SQL STRICT IMMUTABLE;
656 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
657 SELECT approximate_date( $1, '0');
658 $func$ LANGUAGE SQL STRICT IMMUTABLE;
660 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
661 SELECT approximate_date( $1, '9');
662 $func$ LANGUAGE SQL STRICT IMMUTABLE;
664 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
665 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
666 $func$ LANGUAGE SQL STRICT IMMUTABLE;
668 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
669 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
670 $func$ LANGUAGE SQL STRICT IMMUTABLE;
672 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
677 # Find the first ISBN, force it to ISBN13 and return it
681 foreach my $word (split(/\s/, $input)) {
682 my $isbn = Business::ISBN->new($word);
684 # First check the checksum; if it is not valid, fix it and add the original
685 # bad-checksum ISBN to the output
686 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
687 $isbn->fix_checksum();
690 # If we now have a valid ISBN, force it to ISBN13 and return it
691 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
694 $func$ LANGUAGE PLPERLU;
696 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
697 Inspired by translate_isbn1013
699 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
700 version without hypens and with a repaired checksum if the checksum was bad
704 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
709 # For each ISBN found in a single string containing a set of ISBNs:
710 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
711 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
716 foreach my $word (split(/\s/, $input)) {
717 my $isbn = Business::ISBN->new($word);
719 # First check the checksum; if it is not valid, fix it and add the original
720 # bad-checksum ISBN to the output
721 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
722 $output .= $isbn->isbn() . " ";
723 $isbn->fix_checksum();
726 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
727 # and add the normalized original ISBN to the output
728 if ($isbn && $isbn->is_valid()) {
729 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
730 $output .= $isbn->isbn . " ";
732 # If we successfully converted the ISBN to its counterpart, add the
733 # converted ISBN to the output as well
734 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
737 return $output if $output;
739 # If there were no valid ISBNs, just return the raw input
741 $func$ LANGUAGE PLPERLU;
743 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
744 The translate_isbn1013 function takes an input ISBN and returns the
745 following in a single space-delimited string if the input ISBN is valid:
746 - The normalized input ISBN (hyphens stripped)
747 - The normalized input ISBN with a fixed checksum if the checksum was bad
748 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
751 -- And ... a table in which to register them
753 CREATE TABLE config.index_normalizer (
754 id SERIAL PRIMARY KEY,
755 name TEXT UNIQUE NOT NULL,
758 param_count INT NOT NULL DEFAULT 0
761 CREATE TABLE config.metabib_field_index_norm_map (
762 id SERIAL PRIMARY KEY,
763 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
764 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
766 pos INT NOT NULL DEFAULT 0
769 CREATE TABLE config.record_attr_definition (
770 name TEXT PRIMARY KEY,
771 label TEXT NOT NULL, -- I18N
773 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
774 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
776 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
777 tag TEXT, -- LIKE format
778 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
780 -- This is used for both tag/sf and xpath entries
783 -- For xpath-extracted attrs
785 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
790 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
792 -- For phys-char fields
793 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
796 CREATE TABLE config.record_attr_index_norm_map (
797 id SERIAL PRIMARY KEY,
798 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
799 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
801 pos INT NOT NULL DEFAULT 0
804 CREATE TABLE config.coded_value_map (
805 id SERIAL PRIMARY KEY,
806 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
812 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
813 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
814 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
815 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
816 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
817 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
818 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
820 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
828 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
830 SELECT n.func AS func,
831 n.param_count AS param_count,
833 FROM config.index_normalizer n
834 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
835 WHERE field = NEW.field AND m.pos < 0
837 EXECUTE 'SELECT ' || normalizer.func || '(' ||
838 quote_literal( value ) ||
840 WHEN normalizer.param_count > 0
841 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
851 IF NEW.index_vector = ''::tsvector THEN
855 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
857 SELECT n.func AS func,
858 n.param_count AS param_count,
860 FROM config.index_normalizer n
861 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
862 WHERE field = NEW.field AND m.pos >= 0
864 EXECUTE 'SELECT ' || normalizer.func || '(' ||
865 quote_literal( value ) ||
867 WHEN normalizer.param_count > 0
868 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
876 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
877 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
879 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
886 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
887 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
888 SELECT DISTINCT l.version
889 FROM config.upgrade_log l
890 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
891 WHERE d.db_patch = $1
894 -- List applied db patches that are superseded by (and block the application of) my_db_patch
895 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
896 SELECT DISTINCT l.version
897 FROM config.upgrade_log l
898 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
899 WHERE d.db_patch = $1
902 -- List applied db patches that deprecates (and block the application of) my_db_patch
903 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
905 FROM config.db_patch_dependencies
906 WHERE ARRAY[$1]::TEXT[] && deprecates
909 -- List applied db patches that supersedes (and block the application of) my_db_patch
910 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
912 FROM config.db_patch_dependencies
913 WHERE ARRAY[$1]::TEXT[] && supersedes
916 -- Make sure that no deprecated or superseded db patches are currently applied
917 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
919 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
921 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
923 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
925 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
928 -- Raise an exception if there are, in fact, dep/sup conflict
929 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
934 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
935 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
936 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
938 Upgrade script % can not be applied:
939 applied deprecated scripts %
940 applied superseded scripts %
944 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
945 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
946 evergreen.upgrade_list_applied_deprecated(my_db_patch),
947 evergreen.upgrade_list_applied_superseded(my_db_patch);
950 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
955 CREATE TABLE config.barcode_completion (
956 id SERIAL PRIMARY KEY,
957 active BOOL NOT NULL DEFAULT true,
958 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
961 length INT NOT NULL DEFAULT 0,
963 padding_end BOOL NOT NULL DEFAULT false,
964 asset BOOL NOT NULL DEFAULT true,
965 actor BOOL NOT NULL DEFAULT true
968 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
970 -- Add support for logging, only keep the most recent five rows for each category.
973 CREATE TABLE config.org_unit_setting_type_log (
974 id BIGSERIAL PRIMARY KEY,
975 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
976 org INT, --REFERENCES actor.org_unit (id),
979 field_name TEXT REFERENCES config.org_unit_setting_type (name)
982 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
983 Org Unit setting Logs
985 This table contains the most recent changes to each setting
986 in actor.org_unit_setting, allowing for mistakes to be undone.
987 This is NOT meant to be an auditor, but rather an undo/redo.
990 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
992 -- Only keeps the most recent five settings changes.
993 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
994 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
996 IF (TG_OP = 'UPDATE') THEN
998 ELSIF (TG_OP = 'INSERT') THEN
1003 $oustl_limit$ LANGUAGE plpgsql;
1005 CREATE TRIGGER limit_logs_oust
1006 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1007 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1009 CREATE TABLE config.sms_carrier (
1010 id SERIAL PRIMARY KEY,
1014 active BOOLEAN DEFAULT TRUE