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');
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');
149 SELECT SETVAL('config.standing_penalty_id_seq', 100);
151 CREATE TABLE config.xml_transform (
152 name TEXT PRIMARY KEY,
153 namespace_uri TEXT NOT NULL,
154 prefix TEXT NOT NULL,
158 CREATE TABLE config.biblio_fingerprint (
159 id SERIAL PRIMARY KEY,
162 first_word BOOL NOT NULL DEFAULT FALSE,
163 format TEXT NOT NULL DEFAULT 'marcxml'
166 INSERT INTO config.biblio_fingerprint (name, xpath, format)
169 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
170 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
171 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
172 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
173 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
177 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
180 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
181 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
182 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
183 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
184 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
189 CREATE TABLE config.metabib_class (
190 name TEXT PRIMARY KEY,
191 label TEXT NOT NULL UNIQUE
194 CREATE TABLE config.metabib_field (
195 id SERIAL PRIMARY KEY,
196 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
200 weight INT NOT NULL DEFAULT 1,
201 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
202 search_field BOOL NOT NULL DEFAULT TRUE,
203 facet_field BOOL NOT NULL DEFAULT FALSE,
206 COMMENT ON TABLE config.metabib_field IS $$
207 XPath used for record indexing ingest
209 This table contains the XPath used to chop up MODS into its
210 indexable parts. Each XPath entry is named and assigned to
211 a "class" of either title, subject, author, keyword, series
215 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
217 CREATE TABLE config.metabib_search_alias (
218 alias TEXT PRIMARY KEY,
219 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
220 field INT REFERENCES config.metabib_field (id)
223 CREATE TABLE config.non_cataloged_type (
224 id SERIAL PRIMARY KEY,
225 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
227 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
228 in_house BOOL NOT NULL DEFAULT FALSE,
229 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
231 COMMENT ON TABLE config.non_cataloged_type IS $$
232 Types of valid non-cataloged items.
235 CREATE TABLE config.identification_type (
236 id SERIAL PRIMARY KEY,
237 name TEXT NOT NULL UNIQUE
239 COMMENT ON TABLE config.identification_type IS $$
240 Types of valid patron identification.
242 Each patron must display at least one valid form of identification
243 in order to get a library card. This table lists those forms.
246 CREATE TABLE config.rule_circ_duration (
247 id SERIAL PRIMARY KEY,
248 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
249 extended INTERVAL NOT NULL,
250 normal INTERVAL NOT NULL,
251 shrt INTERVAL NOT NULL,
252 max_renewals INT NOT NULL
254 COMMENT ON TABLE config.rule_circ_duration IS $$
255 Circulation Duration rules
257 Each circulation is given a duration based on one of these rules.
260 CREATE TABLE config.hard_due_date (
261 id SERIAL PRIMARY KEY,
262 name TEXT NOT NULL UNIQUE,
263 ceiling_date TIMESTAMPTZ NOT NULL,
264 forceto BOOL NOT NULL,
265 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
268 CREATE TABLE config.hard_due_date_values (
269 id SERIAL PRIMARY KEY,
270 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
271 DEFERRABLE INITIALLY DEFERRED,
272 ceiling_date TIMESTAMPTZ NOT NULL,
273 active_date TIMESTAMPTZ NOT NULL
276 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
278 temp_value config.hard_due_date_values%ROWTYPE;
282 SELECT DISTINCT ON (hard_due_date) *
283 FROM config.hard_due_date_values
284 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
285 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
287 UPDATE config.hard_due_date
288 SET ceiling_date = temp_value.ceiling_date
289 WHERE id = temp_value.hard_due_date
290 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
293 updated := updated + 1;
299 $func$ LANGUAGE plpgsql;
301 CREATE TABLE config.rule_max_fine (
302 id SERIAL PRIMARY KEY,
303 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
304 amount NUMERIC(6,2) NOT NULL,
305 is_percent BOOL NOT NULL DEFAULT FALSE
307 COMMENT ON TABLE config.rule_max_fine IS $$
308 Circulation Max Fine rules
310 Each circulation is given a maximum fine based on one of
314 CREATE TABLE config.rule_recurring_fine (
315 id SERIAL PRIMARY KEY,
316 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
317 high NUMERIC(6,2) NOT NULL,
318 normal NUMERIC(6,2) NOT NULL,
319 low NUMERIC(6,2) NOT NULL,
320 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
321 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
323 COMMENT ON TABLE config.rule_recurring_fine IS $$
324 Circulation Recurring Fine rules
326 Each circulation is given a recurring fine amount based on one of
327 these rules. Note that it is recommended to run the fine generator
328 (from cron) at least as frequently as the lowest recurrence interval
329 used by your circulation rules so that accrued fines will be up
334 CREATE TABLE config.rule_age_hold_protect (
335 id SERIAL PRIMARY KEY,
336 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
337 age INTERVAL NOT NULL,
340 COMMENT ON TABLE config.rule_age_hold_protect IS $$
341 Hold Item Age Protection rules
343 A hold request can only capture new(ish) items when they are
344 within a particular proximity of the pickup_lib of the request.
345 The proximity ('prox' column) is calculated by counting
346 the number of tree edges between the pickup_lib and either the
347 owning_lib or circ_lib of the copy that could fulfill the hold,
348 as determined by the distance_is_from_owner value of the hold matrix
349 rule controlling the hold request.
352 CREATE TABLE config.copy_status (
353 id SERIAL PRIMARY KEY,
354 name TEXT NOT NULL UNIQUE,
355 holdable BOOL NOT NULL DEFAULT FALSE,
356 opac_visible BOOL NOT NULL DEFAULT FALSE,
357 copy_active BOOL NOT NULL DEFAULT FALSE
359 COMMENT ON TABLE config.copy_status IS $$
362 The available copy statuses, and whether a copy in that
363 status is available for hold request capture. 0 (zero) is
364 the only special number in this set, meaning that the item
365 is available for immediate checkout, and is counted as available
368 Statuses with an ID below 100 are not removable, and have special
369 meaning in the code. Do not change them except to translate the
372 You may add and remove statuses above 100, and these can be used
373 to remove items from normal circulation without affecting the rest
374 of the copy's values or its location.
377 CREATE TABLE config.net_access_level (
378 id SERIAL PRIMARY KEY,
379 name TEXT NOT NULL UNIQUE
381 COMMENT ON TABLE config.net_access_level IS $$
382 Patron Network Access level
384 This will be used to inform the in-library firewall of how much
385 internet access the using patron should be allowed.
389 CREATE TABLE config.remote_account (
390 id SERIAL PRIMARY KEY,
392 host TEXT NOT NULL, -- name or IP, :port optional
393 username TEXT, -- optional, since we could default to $USER
394 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
395 account TEXT, -- aka profile or FTP "account" command
396 path TEXT, -- aka directory
397 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
398 last_activity TIMESTAMP WITH TIME ZONE
401 CREATE TABLE config.marc21_rec_type_map (
402 code TEXT PRIMARY KEY,
403 type_val TEXT NOT NULL,
404 blvl_val TEXT NOT NULL
407 CREATE TABLE config.marc21_ff_pos_map (
408 id SERIAL PRIMARY KEY,
409 fixed_field TEXT NOT NULL,
411 rec_type TEXT NOT NULL,
412 start_pos INT NOT NULL,
414 default_val TEXT NOT NULL DEFAULT ' '
417 CREATE TABLE config.marc21_physical_characteristic_type_map (
418 ptype_key TEXT PRIMARY KEY,
419 label TEXT NOT NULL -- I18N
422 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
423 id SERIAL PRIMARY KEY,
424 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
425 subfield TEXT NOT NULL,
426 start_pos INT NOT NULL,
428 label TEXT NOT NULL -- I18N
431 CREATE TABLE config.marc21_physical_characteristic_value_map (
432 id SERIAL PRIMARY KEY,
434 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
435 label TEXT NOT NULL -- I18N
439 CREATE TABLE config.z3950_source (
440 name TEXT PRIMARY KEY,
441 label TEXT NOT NULL UNIQUE,
445 record_format TEXT NOT NULL DEFAULT 'FI',
446 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
447 auth BOOL NOT NULL DEFAULT TRUE,
448 use_perm INT -- REFERENCES permission.perm_list (id)
451 COMMENT ON TABLE config.z3950_source IS $$
454 Each row in this table represents a database searchable via Z39.50.
457 COMMENT ON COLUMN config.z3950_source.record_format IS $$
461 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
462 Z39.50 preferred record syntax..
465 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
466 If set, this permission is required for the source to be listed in the staff
467 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
470 CREATE TABLE config.z3950_attr (
471 id SERIAL PRIMARY KEY,
472 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
477 truncation INT NOT NULL DEFAULT 0,
478 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
481 CREATE TABLE config.i18n_locale (
482 code TEXT PRIMARY KEY,
483 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
484 name TEXT UNIQUE NOT NULL,
488 CREATE TABLE config.i18n_core (
489 id BIGSERIAL PRIMARY KEY,
490 fq_field TEXT NOT NULL,
491 identity_value TEXT NOT NULL,
492 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
496 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
498 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
502 UPDATE config.i18n_core
503 SET identity_value = $$ || quote_literal(new_ident) || $$
504 WHERE fq_field LIKE '$$ || hint || $$.%'
505 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
510 $_$ LANGUAGE PLPGSQL;
512 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
514 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
517 $_$ LANGUAGE PLPGSQL;
519 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
521 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
524 $_$ LANGUAGE PLPGSQL;
526 CREATE TABLE config.billing_type (
527 id SERIAL PRIMARY KEY,
529 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
530 default_price NUMERIC(6,2),
531 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
534 CREATE TABLE config.settings_group (
535 name TEXT PRIMARY KEY,
536 label TEXT UNIQUE NOT NULL -- I18N
539 CREATE TABLE config.org_unit_setting_type (
540 name TEXT PRIMARY KEY,
541 label TEXT UNIQUE NOT NULL,
542 grp TEXT REFERENCES config.settings_group (name),
544 datatype TEXT NOT NULL DEFAULT 'string',
549 -- define valid datatypes
551 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
552 ( 'bool', 'integer', 'float', 'currency', 'interval',
553 'date', 'string', 'object', 'array', 'link' ) ),
555 -- fm_class is meaningful only for 'link' datatype
557 CONSTRAINT coust_no_empty_link CHECK
558 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
559 ( datatype <> 'link' AND fm_class IS NULL ) )
562 CREATE TABLE config.usr_setting_type (
564 name TEXT PRIMARY KEY,
565 opac_visible BOOL NOT NULL DEFAULT FALSE,
566 label TEXT UNIQUE NOT NULL,
568 grp TEXT REFERENCES config.settings_group (name),
569 datatype TEXT NOT NULL DEFAULT 'string',
573 -- define valid datatypes
575 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
576 ( 'bool', 'integer', 'float', 'currency', 'interval',
577 'date', 'string', 'object', 'array', 'link' ) ),
580 -- fm_class is meaningful only for 'link' datatype
582 CONSTRAINT coust_no_empty_link CHECK
583 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
584 ( datatype <> 'link' AND fm_class IS NULL ) )
588 -- Some handy functions, based on existing ones, to provide optional ingest normalization
590 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
591 SELECT SUBSTRING($1,$2);
592 $func$ LANGUAGE SQL STRICT IMMUTABLE;
594 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
595 SELECT SUBSTRING($1,1,$2);
596 $func$ LANGUAGE SQL STRICT IMMUTABLE;
598 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
599 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
600 $func$ LANGUAGE SQL STRICT IMMUTABLE;
602 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
603 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
604 $func$ LANGUAGE SQL STRICT IMMUTABLE;
606 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
607 SELECT approximate_date( $1, '0');
608 $func$ LANGUAGE SQL STRICT IMMUTABLE;
610 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
611 SELECT approximate_date( $1, '9');
612 $func$ LANGUAGE SQL STRICT IMMUTABLE;
614 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
615 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
616 $func$ LANGUAGE SQL STRICT IMMUTABLE;
618 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
619 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
620 $func$ LANGUAGE SQL STRICT IMMUTABLE;
622 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
627 # Find the first ISBN, force it to ISBN13 and return it
631 foreach my $word (split(/\s/, $input)) {
632 my $isbn = Business::ISBN->new($word);
634 # First check the checksum; if it is not valid, fix it and add the original
635 # bad-checksum ISBN to the output
636 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
637 $isbn->fix_checksum();
640 # If we now have a valid ISBN, force it to ISBN13 and return it
641 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
644 $func$ LANGUAGE PLPERLU;
646 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
647 Inspired by translate_isbn1013
649 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
650 version without hypens and with a repaired checksum if the checksum was bad
654 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
659 # For each ISBN found in a single string containing a set of ISBNs:
660 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
661 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
666 foreach my $word (split(/\s/, $input)) {
667 my $isbn = Business::ISBN->new($word);
669 # First check the checksum; if it is not valid, fix it and add the original
670 # bad-checksum ISBN to the output
671 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
672 $output .= $isbn->isbn() . " ";
673 $isbn->fix_checksum();
676 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
677 # and add the normalized original ISBN to the output
678 if ($isbn && $isbn->is_valid()) {
679 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
680 $output .= $isbn->isbn . " ";
682 # If we successfully converted the ISBN to its counterpart, add the
683 # converted ISBN to the output as well
684 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
687 return $output if $output;
689 # If there were no valid ISBNs, just return the raw input
691 $func$ LANGUAGE PLPERLU;
693 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
694 The translate_isbn1013 function takes an input ISBN and returns the
695 following in a single space-delimited string if the input ISBN is valid:
696 - The normalized input ISBN (hyphens stripped)
697 - The normalized input ISBN with a fixed checksum if the checksum was bad
698 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
701 -- And ... a table in which to register them
703 CREATE TABLE config.index_normalizer (
704 id SERIAL PRIMARY KEY,
705 name TEXT UNIQUE NOT NULL,
708 param_count INT NOT NULL DEFAULT 0
711 CREATE TABLE config.metabib_field_index_norm_map (
712 id SERIAL PRIMARY KEY,
713 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
714 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
716 pos INT NOT NULL DEFAULT 0
719 CREATE TABLE config.record_attr_definition (
720 name TEXT PRIMARY KEY,
721 label TEXT NOT NULL, -- I18N
723 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
724 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
726 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
727 tag TEXT, -- LIKE format
728 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
730 -- This is used for both tag/sf and xpath entries
733 -- For xpath-extracted attrs
735 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
740 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
742 -- For phys-char fields
743 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
746 CREATE TABLE config.record_attr_index_norm_map (
747 id SERIAL PRIMARY KEY,
748 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
749 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
751 pos INT NOT NULL DEFAULT 0
754 CREATE TABLE config.coded_value_map (
755 id SERIAL PRIMARY KEY,
756 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
762 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
763 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
764 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
765 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
766 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
767 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
768 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
770 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
778 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
780 SELECT n.func AS func,
781 n.param_count AS param_count,
783 FROM config.index_normalizer n
784 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
785 WHERE field = NEW.field AND m.pos < 0
787 EXECUTE 'SELECT ' || normalizer.func || '(' ||
788 quote_literal( value ) ||
790 WHEN normalizer.param_count > 0
791 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
801 IF NEW.index_vector = ''::tsvector THEN
805 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
807 SELECT n.func AS func,
808 n.param_count AS param_count,
810 FROM config.index_normalizer n
811 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
812 WHERE field = NEW.field AND m.pos >= 0
814 EXECUTE 'SELECT ' || normalizer.func || '(' ||
815 quote_literal( value ) ||
817 WHEN normalizer.param_count > 0
818 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
826 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
827 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
829 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
836 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
837 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
838 SELECT DISTINCT l.version
839 FROM config.upgrade_log l
840 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
841 WHERE d.db_patch = $1
844 -- List applied db patches that are superseded by (and block the application of) my_db_patch
845 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
846 SELECT DISTINCT l.version
847 FROM config.upgrade_log l
848 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
849 WHERE d.db_patch = $1
852 -- List applied db patches that deprecates (and block the application of) my_db_patch
853 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
855 FROM config.db_patch_dependencies
856 WHERE ARRAY[$1]::TEXT[] && deprecates
859 -- List applied db patches that supersedes (and block the application of) my_db_patch
860 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
862 FROM config.db_patch_dependencies
863 WHERE ARRAY[$1]::TEXT[] && supersedes
866 -- Make sure that no deprecated or superseded db patches are currently applied
867 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
869 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
871 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
873 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
875 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
878 -- Raise an exception if there are, in fact, dep/sup conflict
879 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
884 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
885 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
886 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
888 Upgrade script % can not be applied:
889 applied deprecated scripts %
890 applied superseded scripts %
894 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
895 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
896 evergreen.upgrade_list_applied_deprecated(my_db_patch),
897 evergreen.upgrade_list_applied_superseded(my_db_patch);
900 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
905 CREATE TABLE config.barcode_completion (
906 id SERIAL PRIMARY KEY,
907 active BOOL NOT NULL DEFAULT true,
908 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
911 length INT NOT NULL DEFAULT 0,
913 padding_end BOOL NOT NULL DEFAULT false,
914 asset BOOL NOT NULL DEFAULT true,
915 actor BOOL NOT NULL DEFAULT true
918 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
920 -- Add support for logging, only keep the most recent five rows for each category.
923 CREATE TABLE config.org_unit_setting_type_log (
924 id BIGSERIAL PRIMARY KEY,
925 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
926 org INT, --REFERENCES actor.org_unit (id),
929 field_name TEXT REFERENCES config.org_unit_setting_type (name)
932 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
933 Org Unit setting Logs
935 This table contains the most recent changes to each setting
936 in actor.org_unit_setting, allowing for mistakes to be undone.
937 This is NOT meant to be an auditor, but rather an undo/redo.
940 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
942 -- Only keeps the most recent five settings changes.
943 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
944 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
946 IF (TG_OP = 'UPDATE') THEN
948 ELSIF (TG_OP = 'INSERT') THEN
953 $oustl_limit$ LANGUAGE plpgsql;
955 CREATE TRIGGER limit_logs_oust
956 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
957 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
959 CREATE TABLE config.sms_carrier (
960 id SERIAL PRIMARY KEY,
964 active BOOLEAN DEFAULT TRUE