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 ('0647', :eg_version); -- berick/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,
444 use_perm INT -- REFERENCES permission.perm_list (id)
447 COMMENT ON TABLE config.z3950_source IS $$
450 Each row in this table represents a database searchable via Z39.50.
453 COMMENT ON COLUMN config.z3950_source.record_format IS $$
457 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
458 Z39.50 preferred record syntax..
461 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
462 If set, this permission is required for the source to be listed in the staff
463 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
466 CREATE TABLE config.z3950_attr (
467 id SERIAL PRIMARY KEY,
468 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
473 truncation INT NOT NULL DEFAULT 0,
474 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
477 CREATE TABLE config.i18n_locale (
478 code TEXT PRIMARY KEY,
479 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
480 name TEXT UNIQUE NOT NULL,
484 CREATE TABLE config.i18n_core (
485 id BIGSERIAL PRIMARY KEY,
486 fq_field TEXT NOT NULL,
487 identity_value TEXT NOT NULL,
488 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
492 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
494 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
498 UPDATE config.i18n_core
499 SET identity_value = $$ || quote_literal(new_ident) || $$
500 WHERE fq_field LIKE '$$ || hint || $$.%'
501 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
506 $_$ LANGUAGE PLPGSQL;
508 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
510 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
513 $_$ LANGUAGE PLPGSQL;
515 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
517 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
520 $_$ LANGUAGE PLPGSQL;
522 CREATE TABLE config.billing_type (
523 id SERIAL PRIMARY KEY,
525 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
526 default_price NUMERIC(6,2),
527 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
530 CREATE TABLE config.settings_group (
531 name TEXT PRIMARY KEY,
532 label TEXT UNIQUE NOT NULL -- I18N
535 CREATE TABLE config.org_unit_setting_type (
536 name TEXT PRIMARY KEY,
537 label TEXT UNIQUE NOT NULL,
538 grp TEXT REFERENCES config.settings_group (name),
540 datatype TEXT NOT NULL DEFAULT 'string',
545 -- define valid datatypes
547 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
548 ( 'bool', 'integer', 'float', 'currency', 'interval',
549 'date', 'string', 'object', 'array', 'link' ) ),
551 -- fm_class is meaningful only for 'link' datatype
553 CONSTRAINT coust_no_empty_link CHECK
554 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
555 ( datatype <> 'link' AND fm_class IS NULL ) )
558 CREATE TABLE config.usr_setting_type (
560 name TEXT PRIMARY KEY,
561 opac_visible BOOL NOT NULL DEFAULT FALSE,
562 label TEXT UNIQUE NOT NULL,
564 grp TEXT REFERENCES config.settings_group (name),
565 datatype TEXT NOT NULL DEFAULT 'string',
569 -- define valid datatypes
571 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
572 ( 'bool', 'integer', 'float', 'currency', 'interval',
573 'date', 'string', 'object', 'array', 'link' ) ),
576 -- fm_class is meaningful only for 'link' datatype
578 CONSTRAINT coust_no_empty_link CHECK
579 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
580 ( datatype <> 'link' AND fm_class IS NULL ) )
584 -- Some handy functions, based on existing ones, to provide optional ingest normalization
586 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
587 SELECT SUBSTRING($1,$2);
588 $func$ LANGUAGE SQL STRICT IMMUTABLE;
590 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
591 SELECT SUBSTRING($1,1,$2);
592 $func$ LANGUAGE SQL STRICT IMMUTABLE;
594 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
595 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
596 $func$ LANGUAGE SQL STRICT IMMUTABLE;
598 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
599 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
600 $func$ LANGUAGE SQL STRICT IMMUTABLE;
602 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
603 SELECT approximate_date( $1, '0');
604 $func$ LANGUAGE SQL STRICT IMMUTABLE;
606 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
607 SELECT approximate_date( $1, '9');
608 $func$ LANGUAGE SQL STRICT IMMUTABLE;
610 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
611 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
612 $func$ LANGUAGE SQL STRICT IMMUTABLE;
614 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
615 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
616 $func$ LANGUAGE SQL STRICT IMMUTABLE;
618 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
623 # Find the first ISBN, force it to ISBN13 and return it
627 foreach my $word (split(/\s/, $input)) {
628 my $isbn = Business::ISBN->new($word);
630 # First check the checksum; if it is not valid, fix it and add the original
631 # bad-checksum ISBN to the output
632 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
633 $isbn->fix_checksum();
636 # If we now have a valid ISBN, force it to ISBN13 and return it
637 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
640 $func$ LANGUAGE PLPERLU;
642 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
643 Inspired by translate_isbn1013
645 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
646 version without hypens and with a repaired checksum if the checksum was bad
650 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
655 # For each ISBN found in a single string containing a set of ISBNs:
656 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
657 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
662 foreach my $word (split(/\s/, $input)) {
663 my $isbn = Business::ISBN->new($word);
665 # First check the checksum; if it is not valid, fix it and add the original
666 # bad-checksum ISBN to the output
667 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
668 $output .= $isbn->isbn() . " ";
669 $isbn->fix_checksum();
672 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
673 # and add the normalized original ISBN to the output
674 if ($isbn && $isbn->is_valid()) {
675 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
676 $output .= $isbn->isbn . " ";
678 # If we successfully converted the ISBN to its counterpart, add the
679 # converted ISBN to the output as well
680 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
683 return $output if $output;
685 # If there were no valid ISBNs, just return the raw input
687 $func$ LANGUAGE PLPERLU;
689 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
690 The translate_isbn1013 function takes an input ISBN and returns the
691 following in a single space-delimited string if the input ISBN is valid:
692 - The normalized input ISBN (hyphens stripped)
693 - The normalized input ISBN with a fixed checksum if the checksum was bad
694 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
697 -- And ... a table in which to register them
699 CREATE TABLE config.index_normalizer (
700 id SERIAL PRIMARY KEY,
701 name TEXT UNIQUE NOT NULL,
704 param_count INT NOT NULL DEFAULT 0
707 CREATE TABLE config.metabib_field_index_norm_map (
708 id SERIAL PRIMARY KEY,
709 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
710 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
712 pos INT NOT NULL DEFAULT 0
715 CREATE TABLE config.record_attr_definition (
716 name TEXT PRIMARY KEY,
717 label TEXT NOT NULL, -- I18N
719 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
720 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
722 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
723 tag TEXT, -- LIKE format
724 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
726 -- This is used for both tag/sf and xpath entries
729 -- For xpath-extracted attrs
731 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
736 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
738 -- For phys-char fields
739 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
742 CREATE TABLE config.record_attr_index_norm_map (
743 id SERIAL PRIMARY KEY,
744 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
745 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
747 pos INT NOT NULL DEFAULT 0
750 CREATE TABLE config.coded_value_map (
751 id SERIAL PRIMARY KEY,
752 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
758 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
759 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
760 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
761 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
762 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
763 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
764 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
766 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
774 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
776 SELECT n.func AS func,
777 n.param_count AS param_count,
779 FROM config.index_normalizer n
780 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
781 WHERE field = NEW.field AND m.pos < 0
783 EXECUTE 'SELECT ' || normalizer.func || '(' ||
784 quote_literal( value ) ||
786 WHEN normalizer.param_count > 0
787 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
797 IF NEW.index_vector = ''::tsvector THEN
801 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
803 SELECT n.func AS func,
804 n.param_count AS param_count,
806 FROM config.index_normalizer n
807 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
808 WHERE field = NEW.field AND m.pos >= 0
810 EXECUTE 'SELECT ' || normalizer.func || '(' ||
811 quote_literal( value ) ||
813 WHEN normalizer.param_count > 0
814 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
822 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
823 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
825 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
832 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
833 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
834 SELECT DISTINCT l.version
835 FROM config.upgrade_log l
836 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
837 WHERE d.db_patch = $1
840 -- List applied db patches that are superseded by (and block the application of) my_db_patch
841 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
842 SELECT DISTINCT l.version
843 FROM config.upgrade_log l
844 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
845 WHERE d.db_patch = $1
848 -- List applied db patches that deprecates (and block the application of) my_db_patch
849 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
851 FROM config.db_patch_dependencies
852 WHERE ARRAY[$1]::TEXT[] && deprecates
855 -- List applied db patches that supersedes (and block the application of) my_db_patch
856 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
858 FROM config.db_patch_dependencies
859 WHERE ARRAY[$1]::TEXT[] && supersedes
862 -- Make sure that no deprecated or superseded db patches are currently applied
863 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
865 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
867 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
869 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
871 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
874 -- Raise an exception if there are, in fact, dep/sup conflict
875 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
880 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
881 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
882 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
884 Upgrade script % can not be applied:
885 applied deprecated scripts %
886 applied superseded scripts %
890 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
891 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
892 evergreen.upgrade_list_applied_deprecated(my_db_patch),
893 evergreen.upgrade_list_applied_superseded(my_db_patch);
896 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
901 CREATE TABLE config.barcode_completion (
902 id SERIAL PRIMARY KEY,
903 active BOOL NOT NULL DEFAULT true,
904 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
907 length INT NOT NULL DEFAULT 0,
909 padding_end BOOL NOT NULL DEFAULT false,
910 asset BOOL NOT NULL DEFAULT true,
911 actor BOOL NOT NULL DEFAULT true
914 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
916 -- Add support for logging, only keep the most recent five rows for each category.
919 CREATE TABLE config.org_unit_setting_type_log (
920 id BIGSERIAL PRIMARY KEY,
921 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
922 org INT, --REFERENCES actor.org_unit (id),
925 field_name TEXT REFERENCES config.org_unit_setting_type (name)
928 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
929 Org Unit setting Logs
931 This table contains the most recent changes to each setting
932 in actor.org_unit_setting, allowing for mistakes to be undone.
933 This is NOT meant to be an auditor, but rather an undo/redo.
936 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
938 -- Only keeps the most recent five settings changes.
939 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
940 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
942 IF (TG_OP = 'UPDATE') THEN
944 ELSIF (TG_OP = 'INSERT') THEN
949 $oustl_limit$ LANGUAGE plpgsql;
951 CREATE TRIGGER limit_logs_oust
952 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
953 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();