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 ('0594', :eg_version); -- phasefx/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
97 COMMENT ON TABLE config.bib_source IS $$
98 This is table is used to set up the relative "quality" of each
99 MARC source, such as OCLC. Also identifies "transcendant" sources,
100 i.e., sources of bib records that should display in the OPAC
101 even if no copies or located URIs are attached.
104 CREATE TABLE config.standing (
105 id SERIAL PRIMARY KEY,
106 value TEXT NOT NULL UNIQUE
108 COMMENT ON TABLE config.standing IS $$
111 This table contains the values that can be applied to a patron
112 by a staff member. These values should not be changed, other
113 than for translation, as the ID column is currently a "magic
114 number" in the source. :(
117 CREATE TABLE config.standing_penalty (
118 id SERIAL PRIMARY KEY,
119 name TEXT NOT NULL UNIQUE,
124 INSERT INTO config.standing_penalty (id,name,label,block_list)
125 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
126 INSERT INTO config.standing_penalty (id,name,label,block_list)
127 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
128 INSERT INTO config.standing_penalty (id,name,label,block_list)
129 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
130 INSERT INTO config.standing_penalty (id,name,label,block_list)
131 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
133 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
134 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
135 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
136 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
137 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
138 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
139 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
140 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
141 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
142 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
143 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
145 SELECT SETVAL('config.standing_penalty_id_seq', 100);
147 CREATE TABLE config.xml_transform (
148 name TEXT PRIMARY KEY,
149 namespace_uri TEXT NOT NULL,
150 prefix TEXT NOT NULL,
154 CREATE TABLE config.biblio_fingerprint (
155 id SERIAL PRIMARY KEY,
158 first_word BOOL NOT NULL DEFAULT FALSE,
159 format TEXT NOT NULL DEFAULT 'marcxml'
162 INSERT INTO config.biblio_fingerprint (name, xpath, format)
165 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
166 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
167 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
168 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
169 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
173 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
176 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
177 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
178 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
179 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
180 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
185 CREATE TABLE config.metabib_class (
186 name TEXT PRIMARY KEY,
187 label TEXT NOT NULL UNIQUE
190 CREATE TABLE config.metabib_field (
191 id SERIAL PRIMARY KEY,
192 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
196 weight INT NOT NULL DEFAULT 1,
197 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
198 search_field BOOL NOT NULL DEFAULT TRUE,
199 facet_field BOOL NOT NULL DEFAULT FALSE,
202 COMMENT ON TABLE config.metabib_field IS $$
203 XPath used for record indexing ingest
205 This table contains the XPath used to chop up MODS into its
206 indexable parts. Each XPath entry is named and assigned to
207 a "class" of either title, subject, author, keyword, series
211 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
213 CREATE TABLE config.metabib_search_alias (
214 alias TEXT PRIMARY KEY,
215 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
216 field INT REFERENCES config.metabib_field (id)
219 CREATE TABLE config.non_cataloged_type (
220 id SERIAL PRIMARY KEY,
221 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
223 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
224 in_house BOOL NOT NULL DEFAULT FALSE,
225 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
227 COMMENT ON TABLE config.non_cataloged_type IS $$
228 Types of valid non-cataloged items.
231 CREATE TABLE config.identification_type (
232 id SERIAL PRIMARY KEY,
233 name TEXT NOT NULL UNIQUE
235 COMMENT ON TABLE config.identification_type IS $$
236 Types of valid patron identification.
238 Each patron must display at least one valid form of identification
239 in order to get a library card. This table lists those forms.
242 CREATE TABLE config.rule_circ_duration (
243 id SERIAL PRIMARY KEY,
244 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
245 extended INTERVAL NOT NULL,
246 normal INTERVAL NOT NULL,
247 shrt INTERVAL NOT NULL,
248 max_renewals INT NOT NULL
250 COMMENT ON TABLE config.rule_circ_duration IS $$
251 Circulation Duration rules
253 Each circulation is given a duration based on one of these rules.
256 CREATE TABLE config.hard_due_date (
257 id SERIAL PRIMARY KEY,
258 name TEXT NOT NULL UNIQUE,
259 ceiling_date TIMESTAMPTZ NOT NULL,
260 forceto BOOL NOT NULL,
261 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
264 CREATE TABLE config.hard_due_date_values (
265 id SERIAL PRIMARY KEY,
266 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
267 DEFERRABLE INITIALLY DEFERRED,
268 ceiling_date TIMESTAMPTZ NOT NULL,
269 active_date TIMESTAMPTZ NOT NULL
272 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
274 temp_value config.hard_due_date_values%ROWTYPE;
278 SELECT DISTINCT ON (hard_due_date) *
279 FROM config.hard_due_date_values
280 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
281 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
283 UPDATE config.hard_due_date
284 SET ceiling_date = temp_value.ceiling_date
285 WHERE id = temp_value.hard_due_date
286 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
289 updated := updated + 1;
295 $func$ LANGUAGE plpgsql;
297 CREATE TABLE config.rule_max_fine (
298 id SERIAL PRIMARY KEY,
299 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
300 amount NUMERIC(6,2) NOT NULL,
301 is_percent BOOL NOT NULL DEFAULT FALSE
303 COMMENT ON TABLE config.rule_max_fine IS $$
304 Circulation Max Fine rules
306 Each circulation is given a maximum fine based on one of
310 CREATE TABLE config.rule_recurring_fine (
311 id SERIAL PRIMARY KEY,
312 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
313 high NUMERIC(6,2) NOT NULL,
314 normal NUMERIC(6,2) NOT NULL,
315 low NUMERIC(6,2) NOT NULL,
316 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
317 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
319 COMMENT ON TABLE config.rule_recurring_fine IS $$
320 Circulation Recurring Fine rules
322 Each circulation is given a recurring fine amount based on one of
323 these rules. Note that it is recommended to run the fine generator
324 (from cron) at least as frequently as the lowest recurrence interval
325 used by your circulation rules so that accrued fines will be up
330 CREATE TABLE config.rule_age_hold_protect (
331 id SERIAL PRIMARY KEY,
332 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
333 age INTERVAL NOT NULL,
336 COMMENT ON TABLE config.rule_age_hold_protect IS $$
337 Hold Item Age Protection rules
339 A hold request can only capture new(ish) items when they are
340 within a particular proximity of the pickup_lib of the request.
341 The proximity ('prox' column) is calculated by counting
342 the number of tree edges between the pickup_lib and either the
343 owning_lib or circ_lib of the copy that could fulfill the hold,
344 as determined by the distance_is_from_owner value of the hold matrix
345 rule controlling the hold request.
348 CREATE TABLE config.copy_status (
349 id SERIAL PRIMARY KEY,
350 name TEXT NOT NULL UNIQUE,
351 holdable BOOL NOT NULL DEFAULT FALSE,
352 opac_visible BOOL NOT NULL DEFAULT FALSE,
353 copy_active BOOL NOT NULL DEFAULT FALSE
355 COMMENT ON TABLE config.copy_status IS $$
358 The available copy statuses, and whether a copy in that
359 status is available for hold request capture. 0 (zero) is
360 the only special number in this set, meaning that the item
361 is available for immediate checkout, and is counted as available
364 Statuses with an ID below 100 are not removable, and have special
365 meaning in the code. Do not change them except to translate the
368 You may add and remove statuses above 100, and these can be used
369 to remove items from normal circulation without affecting the rest
370 of the copy's values or its location.
373 CREATE TABLE config.net_access_level (
374 id SERIAL PRIMARY KEY,
375 name TEXT NOT NULL UNIQUE
377 COMMENT ON TABLE config.net_access_level IS $$
378 Patron Network Access level
380 This will be used to inform the in-library firewall of how much
381 internet access the using patron should be allowed.
385 CREATE TABLE config.remote_account (
386 id SERIAL PRIMARY KEY,
388 host TEXT NOT NULL, -- name or IP, :port optional
389 username TEXT, -- optional, since we could default to $USER
390 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
391 account TEXT, -- aka profile or FTP "account" command
392 path TEXT, -- aka directory
393 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
394 last_activity TIMESTAMP WITH TIME ZONE
397 CREATE TABLE config.marc21_rec_type_map (
398 code TEXT PRIMARY KEY,
399 type_val TEXT NOT NULL,
400 blvl_val TEXT NOT NULL
403 CREATE TABLE config.marc21_ff_pos_map (
404 id SERIAL PRIMARY KEY,
405 fixed_field TEXT NOT NULL,
407 rec_type TEXT NOT NULL,
408 start_pos INT NOT NULL,
410 default_val TEXT NOT NULL DEFAULT ' '
413 CREATE TABLE config.marc21_physical_characteristic_type_map (
414 ptype_key TEXT PRIMARY KEY,
415 label TEXT NOT NULL -- I18N
418 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
419 id SERIAL PRIMARY KEY,
420 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
421 subfield TEXT NOT NULL,
422 start_pos INT NOT NULL,
424 label TEXT NOT NULL -- I18N
427 CREATE TABLE config.marc21_physical_characteristic_value_map (
428 id SERIAL PRIMARY KEY,
430 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
431 label TEXT NOT NULL -- I18N
435 CREATE TABLE config.z3950_source (
436 name TEXT PRIMARY KEY,
437 label TEXT NOT NULL UNIQUE,
441 record_format TEXT NOT NULL DEFAULT 'FI',
442 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
443 auth BOOL NOT NULL DEFAULT TRUE
446 COMMENT ON TABLE config.z3950_source IS $$
449 Each row in this table represents a database searchable via Z39.50.
452 COMMENT ON COLUMN config.z3950_source.record_format IS $$
456 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
457 Z39.50 preferred record syntax..
461 CREATE TABLE config.z3950_attr (
462 id SERIAL PRIMARY KEY,
463 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
468 truncation INT NOT NULL DEFAULT 0,
469 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
472 CREATE TABLE config.i18n_locale (
473 code TEXT PRIMARY KEY,
474 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
475 name TEXT UNIQUE NOT NULL,
479 CREATE TABLE config.i18n_core (
480 id BIGSERIAL PRIMARY KEY,
481 fq_field TEXT NOT NULL,
482 identity_value TEXT NOT NULL,
483 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
487 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
489 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
493 UPDATE config.i18n_core
494 SET identity_value = $$ || quote_literal(new_ident) || $$
495 WHERE fq_field LIKE '$$ || hint || $$.%'
496 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
501 $_$ LANGUAGE PLPGSQL;
503 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
505 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
508 $_$ LANGUAGE PLPGSQL;
510 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
512 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
515 $_$ LANGUAGE PLPGSQL;
517 CREATE TABLE config.billing_type (
518 id SERIAL PRIMARY KEY,
520 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
521 default_price NUMERIC(6,2),
522 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
525 CREATE TABLE config.settings_group (
526 name TEXT PRIMARY KEY,
527 label TEXT UNIQUE NOT NULL -- I18N
530 CREATE TABLE config.org_unit_setting_type (
531 name TEXT PRIMARY KEY,
532 label TEXT UNIQUE NOT NULL,
533 grp TEXT REFERENCES config.settings_group (name),
535 datatype TEXT NOT NULL DEFAULT 'string',
540 -- define valid datatypes
542 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
543 ( 'bool', 'integer', 'float', 'currency', 'interval',
544 'date', 'string', 'object', 'array', 'link' ) ),
546 -- fm_class is meaningful only for 'link' datatype
548 CONSTRAINT coust_no_empty_link CHECK
549 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
550 ( datatype <> 'link' AND fm_class IS NULL ) )
553 CREATE TABLE config.usr_setting_type (
555 name TEXT PRIMARY KEY,
556 opac_visible BOOL NOT NULL DEFAULT FALSE,
557 label TEXT UNIQUE NOT NULL,
559 grp TEXT REFERENCES config.settings_group (name),
560 datatype TEXT NOT NULL DEFAULT 'string',
564 -- define valid datatypes
566 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
567 ( 'bool', 'integer', 'float', 'currency', 'interval',
568 'date', 'string', 'object', 'array', 'link' ) ),
571 -- fm_class is meaningful only for 'link' datatype
573 CONSTRAINT coust_no_empty_link CHECK
574 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
575 ( datatype <> 'link' AND fm_class IS NULL ) )
579 -- Some handy functions, based on existing ones, to provide optional ingest normalization
581 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
582 SELECT SUBSTRING($1,$2);
583 $func$ LANGUAGE SQL STRICT IMMUTABLE;
585 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
586 SELECT SUBSTRING($1,1,$2);
587 $func$ LANGUAGE SQL STRICT IMMUTABLE;
589 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
590 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
591 $func$ LANGUAGE SQL STRICT IMMUTABLE;
593 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
594 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
595 $func$ LANGUAGE SQL STRICT IMMUTABLE;
597 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
598 SELECT approximate_date( $1, '0');
599 $func$ LANGUAGE SQL STRICT IMMUTABLE;
601 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
602 SELECT approximate_date( $1, '9');
603 $func$ LANGUAGE SQL STRICT IMMUTABLE;
605 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
606 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
607 $func$ LANGUAGE SQL STRICT IMMUTABLE;
609 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
610 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
611 $func$ LANGUAGE SQL STRICT IMMUTABLE;
613 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
618 # Find the first ISBN, force it to ISBN13 and return it
622 foreach my $word (split(/\s/, $input)) {
623 my $isbn = Business::ISBN->new($word);
625 # First check the checksum; if it is not valid, fix it and add the original
626 # bad-checksum ISBN to the output
627 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
628 $isbn->fix_checksum();
631 # If we now have a valid ISBN, force it to ISBN13 and return it
632 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
635 $func$ LANGUAGE PLPERLU;
637 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
638 Inspired by translate_isbn1013
640 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
641 version without hypens and with a repaired checksum if the checksum was bad
645 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
650 # For each ISBN found in a single string containing a set of ISBNs:
651 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
652 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
657 foreach my $word (split(/\s/, $input)) {
658 my $isbn = Business::ISBN->new($word);
660 # First check the checksum; if it is not valid, fix it and add the original
661 # bad-checksum ISBN to the output
662 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
663 $output .= $isbn->isbn() . " ";
664 $isbn->fix_checksum();
667 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
668 # and add the normalized original ISBN to the output
669 if ($isbn && $isbn->is_valid()) {
670 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
671 $output .= $isbn->isbn . " ";
673 # If we successfully converted the ISBN to its counterpart, add the
674 # converted ISBN to the output as well
675 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
678 return $output if $output;
680 # If there were no valid ISBNs, just return the raw input
682 $func$ LANGUAGE PLPERLU;
684 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
685 The translate_isbn1013 function takes an input ISBN and returns the
686 following in a single space-delimited string if the input ISBN is valid:
687 - The normalized input ISBN (hyphens stripped)
688 - The normalized input ISBN with a fixed checksum if the checksum was bad
689 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
692 -- And ... a table in which to register them
694 CREATE TABLE config.index_normalizer (
695 id SERIAL PRIMARY KEY,
696 name TEXT UNIQUE NOT NULL,
699 param_count INT NOT NULL DEFAULT 0
702 CREATE TABLE config.metabib_field_index_norm_map (
703 id SERIAL PRIMARY KEY,
704 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
705 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
707 pos INT NOT NULL DEFAULT 0
710 CREATE TABLE config.record_attr_definition (
711 name TEXT PRIMARY KEY,
712 label TEXT NOT NULL, -- I18N
714 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
715 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
717 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
718 tag TEXT, -- LIKE format
719 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
721 -- This is used for both tag/sf and xpath entries
724 -- For xpath-extracted attrs
726 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
731 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
733 -- For phys-char fields
734 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
737 CREATE TABLE config.record_attr_index_norm_map (
738 id SERIAL PRIMARY KEY,
739 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
740 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
742 pos INT NOT NULL DEFAULT 0
745 CREATE TABLE config.coded_value_map (
746 id SERIAL PRIMARY KEY,
747 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
753 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
754 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
755 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
756 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
757 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
758 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
759 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
761 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
769 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
771 SELECT n.func AS func,
772 n.param_count AS param_count,
774 FROM config.index_normalizer n
775 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
776 WHERE field = NEW.field AND m.pos < 0
778 EXECUTE 'SELECT ' || normalizer.func || '(' ||
779 quote_literal( value ) ||
781 WHEN normalizer.param_count > 0
782 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
792 IF NEW.index_vector = ''::tsvector THEN
796 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
798 SELECT n.func AS func,
799 n.param_count AS param_count,
801 FROM config.index_normalizer n
802 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
803 WHERE field = NEW.field AND m.pos >= 0
805 EXECUTE 'SELECT ' || normalizer.func || '(' ||
806 quote_literal( value ) ||
808 WHEN normalizer.param_count > 0
809 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
817 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
818 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
820 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
827 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
828 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
829 SELECT DISTINCT l.version
830 FROM config.upgrade_log l
831 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
832 WHERE d.db_patch = $1
835 -- List applied db patches that are superseded by (and block the application of) my_db_patch
836 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
837 SELECT DISTINCT l.version
838 FROM config.upgrade_log l
839 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
840 WHERE d.db_patch = $1
843 -- List applied db patches that deprecates (and block the application of) my_db_patch
844 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
846 FROM config.db_patch_dependencies
847 WHERE ARRAY[$1]::TEXT[] && deprecates
850 -- List applied db patches that supersedes (and block the application of) my_db_patch
851 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
853 FROM config.db_patch_dependencies
854 WHERE ARRAY[$1]::TEXT[] && supersedes
857 -- Make sure that no deprecated or superseded db patches are currently applied
858 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
860 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
862 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
864 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
866 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
869 -- Raise an exception if there are, in fact, dep/sup conflict
870 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
875 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
876 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
877 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
879 Upgrade script % can not be applied:
880 applied deprecated scripts %
881 applied superseded scripts %
885 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
886 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
887 evergreen.upgrade_list_applied_deprecated(my_db_patch),
888 evergreen.upgrade_list_applied_superseded(my_db_patch);
891 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
896 CREATE TABLE config.barcode_completion (
897 id SERIAL PRIMARY KEY,
898 active BOOL NOT NULL DEFAULT true,
899 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
902 length INT NOT NULL DEFAULT 0,
904 padding_end BOOL NOT NULL DEFAULT false,
905 asset BOOL NOT NULL DEFAULT true,
906 actor BOOL NOT NULL DEFAULT true
909 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);