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 ('0674', :eg_version); -- phasefx/Dyrcona/senator
91 CREATE TABLE config.bib_source (
92 id SERIAL PRIMARY KEY,
93 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
94 source TEXT NOT NULL UNIQUE,
95 transcendant BOOL NOT NULL DEFAULT FALSE,
96 can_have_copies BOOL NOT NULL DEFAULT TRUE
98 COMMENT ON TABLE config.bib_source IS $$
99 This is table is used to set up the relative "quality" of each
100 MARC source, such as OCLC. Also identifies "transcendant" sources,
101 i.e., sources of bib records that should display in the OPAC
102 even if no copies or located URIs are attached. Also indicates if
103 the source is allowed to have actual copies on its bibs. Volumes
104 for targeted URIs are unaffected by this setting.
107 CREATE TABLE config.standing (
108 id SERIAL PRIMARY KEY,
109 value TEXT NOT NULL UNIQUE
111 COMMENT ON TABLE config.standing IS $$
114 This table contains the values that can be applied to a patron
115 by a staff member. These values should not be changed, other
116 than for translation, as the ID column is currently a "magic
117 number" in the source. :(
120 CREATE TABLE config.standing_penalty (
121 id SERIAL PRIMARY KEY,
122 name TEXT NOT NULL UNIQUE,
125 staff_alert BOOL NOT NULL DEFAULT FALSE,
129 CREATE TABLE config.xml_transform (
130 name TEXT PRIMARY KEY,
131 namespace_uri TEXT NOT NULL,
132 prefix TEXT NOT NULL,
136 CREATE TABLE config.biblio_fingerprint (
137 id SERIAL PRIMARY KEY,
140 first_word BOOL NOT NULL DEFAULT FALSE,
141 format TEXT NOT NULL DEFAULT 'marcxml'
144 INSERT INTO config.biblio_fingerprint (name, xpath, format)
147 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
148 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
149 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
150 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
151 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
155 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
158 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
159 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
160 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
161 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
162 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
167 CREATE TABLE config.metabib_class (
168 name TEXT PRIMARY KEY,
169 label TEXT NOT NULL UNIQUE
172 CREATE TABLE config.metabib_field (
173 id SERIAL PRIMARY KEY,
174 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
178 weight INT NOT NULL DEFAULT 1,
179 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
180 search_field BOOL NOT NULL DEFAULT TRUE,
181 facet_field BOOL NOT NULL DEFAULT FALSE,
184 COMMENT ON TABLE config.metabib_field IS $$
185 XPath used for record indexing ingest
187 This table contains the XPath used to chop up MODS into its
188 indexable parts. Each XPath entry is named and assigned to
189 a "class" of either title, subject, author, keyword, series
193 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
195 CREATE TABLE config.metabib_search_alias (
196 alias TEXT PRIMARY KEY,
197 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
198 field INT REFERENCES config.metabib_field (id)
201 CREATE TABLE config.non_cataloged_type (
202 id SERIAL PRIMARY KEY,
203 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
205 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
206 in_house BOOL NOT NULL DEFAULT FALSE,
207 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
209 COMMENT ON TABLE config.non_cataloged_type IS $$
210 Types of valid non-cataloged items.
213 CREATE TABLE config.identification_type (
214 id SERIAL PRIMARY KEY,
215 name TEXT NOT NULL UNIQUE
217 COMMENT ON TABLE config.identification_type IS $$
218 Types of valid patron identification.
220 Each patron must display at least one valid form of identification
221 in order to get a library card. This table lists those forms.
224 CREATE TABLE config.rule_circ_duration (
225 id SERIAL PRIMARY KEY,
226 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
227 extended INTERVAL NOT NULL,
228 normal INTERVAL NOT NULL,
229 shrt INTERVAL NOT NULL,
230 max_renewals INT NOT NULL
232 COMMENT ON TABLE config.rule_circ_duration IS $$
233 Circulation Duration rules
235 Each circulation is given a duration based on one of these rules.
238 CREATE TABLE config.hard_due_date (
239 id SERIAL PRIMARY KEY,
240 name TEXT NOT NULL UNIQUE,
241 ceiling_date TIMESTAMPTZ NOT NULL,
242 forceto BOOL NOT NULL,
243 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
246 CREATE TABLE config.hard_due_date_values (
247 id SERIAL PRIMARY KEY,
248 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
249 DEFERRABLE INITIALLY DEFERRED,
250 ceiling_date TIMESTAMPTZ NOT NULL,
251 active_date TIMESTAMPTZ NOT NULL
254 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
256 temp_value config.hard_due_date_values%ROWTYPE;
260 SELECT DISTINCT ON (hard_due_date) *
261 FROM config.hard_due_date_values
262 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
263 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
265 UPDATE config.hard_due_date
266 SET ceiling_date = temp_value.ceiling_date
267 WHERE id = temp_value.hard_due_date
268 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
271 updated := updated + 1;
277 $func$ LANGUAGE plpgsql;
279 CREATE TABLE config.rule_max_fine (
280 id SERIAL PRIMARY KEY,
281 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
282 amount NUMERIC(6,2) NOT NULL,
283 is_percent BOOL NOT NULL DEFAULT FALSE
285 COMMENT ON TABLE config.rule_max_fine IS $$
286 Circulation Max Fine rules
288 Each circulation is given a maximum fine based on one of
292 CREATE TABLE config.rule_recurring_fine (
293 id SERIAL PRIMARY KEY,
294 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
295 high NUMERIC(6,2) NOT NULL,
296 normal NUMERIC(6,2) NOT NULL,
297 low NUMERIC(6,2) NOT NULL,
298 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
299 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
301 COMMENT ON TABLE config.rule_recurring_fine IS $$
302 Circulation Recurring Fine rules
304 Each circulation is given a recurring fine amount based on one of
305 these rules. Note that it is recommended to run the fine generator
306 (from cron) at least as frequently as the lowest recurrence interval
307 used by your circulation rules so that accrued fines will be up
312 CREATE TABLE config.rule_age_hold_protect (
313 id SERIAL PRIMARY KEY,
314 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
315 age INTERVAL NOT NULL,
318 COMMENT ON TABLE config.rule_age_hold_protect IS $$
319 Hold Item Age Protection rules
321 A hold request can only capture new(ish) items when they are
322 within a particular proximity of the pickup_lib of the request.
323 The proximity ('prox' column) is calculated by counting
324 the number of tree edges between the pickup_lib and either the
325 owning_lib or circ_lib of the copy that could fulfill the hold,
326 as determined by the distance_is_from_owner value of the hold matrix
327 rule controlling the hold request.
330 CREATE TABLE config.copy_status (
331 id SERIAL PRIMARY KEY,
332 name TEXT NOT NULL UNIQUE,
333 holdable BOOL NOT NULL DEFAULT FALSE,
334 opac_visible BOOL NOT NULL DEFAULT FALSE,
335 copy_active BOOL NOT NULL DEFAULT FALSE,
336 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
338 COMMENT ON TABLE config.copy_status IS $$
341 The available copy statuses, and whether a copy in that
342 status is available for hold request capture. 0 (zero) is
343 the only special number in this set, meaning that the item
344 is available for immediate checkout, and is counted as available
347 Statuses with an ID below 100 are not removable, and have special
348 meaning in the code. Do not change them except to translate the
351 You may add and remove statuses above 100, and these can be used
352 to remove items from normal circulation without affecting the rest
353 of the copy's values or its location.
356 CREATE TABLE config.net_access_level (
357 id SERIAL PRIMARY KEY,
358 name TEXT NOT NULL UNIQUE
360 COMMENT ON TABLE config.net_access_level IS $$
361 Patron Network Access level
363 This will be used to inform the in-library firewall of how much
364 internet access the using patron should be allowed.
368 CREATE TABLE config.remote_account (
369 id SERIAL PRIMARY KEY,
371 host TEXT NOT NULL, -- name or IP, :port optional
372 username TEXT, -- optional, since we could default to $USER
373 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
374 account TEXT, -- aka profile or FTP "account" command
375 path TEXT, -- aka directory
376 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
377 last_activity TIMESTAMP WITH TIME ZONE
380 CREATE TABLE config.marc21_rec_type_map (
381 code TEXT PRIMARY KEY,
382 type_val TEXT NOT NULL,
383 blvl_val TEXT NOT NULL
386 CREATE TABLE config.marc21_ff_pos_map (
387 id SERIAL PRIMARY KEY,
388 fixed_field TEXT NOT NULL,
390 rec_type TEXT NOT NULL,
391 start_pos INT NOT NULL,
393 default_val TEXT NOT NULL DEFAULT ' '
396 CREATE TABLE config.marc21_physical_characteristic_type_map (
397 ptype_key TEXT PRIMARY KEY,
398 label TEXT NOT NULL -- I18N
401 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
402 id SERIAL PRIMARY KEY,
403 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
404 subfield TEXT NOT NULL,
405 start_pos INT NOT NULL,
407 label TEXT NOT NULL -- I18N
410 CREATE TABLE config.marc21_physical_characteristic_value_map (
411 id SERIAL PRIMARY KEY,
413 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
414 label TEXT NOT NULL -- I18N
418 CREATE TABLE config.z3950_source (
419 name TEXT PRIMARY KEY,
420 label TEXT NOT NULL UNIQUE,
424 record_format TEXT NOT NULL DEFAULT 'FI',
425 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
426 auth BOOL NOT NULL DEFAULT TRUE,
427 use_perm INT -- REFERENCES permission.perm_list (id)
430 COMMENT ON TABLE config.z3950_source IS $$
433 Each row in this table represents a database searchable via Z39.50.
436 COMMENT ON COLUMN config.z3950_source.record_format IS $$
440 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
441 Z39.50 preferred record syntax..
444 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
445 If set, this permission is required for the source to be listed in the staff
446 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
449 CREATE TABLE config.z3950_attr (
450 id SERIAL PRIMARY KEY,
451 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
456 truncation INT NOT NULL DEFAULT 0,
457 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
460 CREATE TABLE config.i18n_locale (
461 code TEXT PRIMARY KEY,
462 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
463 name TEXT UNIQUE NOT NULL,
467 CREATE TABLE config.i18n_core (
468 id BIGSERIAL PRIMARY KEY,
469 fq_field TEXT NOT NULL,
470 identity_value TEXT NOT NULL,
471 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
475 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
477 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
481 UPDATE config.i18n_core
482 SET identity_value = $$ || quote_literal(new_ident) || $$
483 WHERE fq_field LIKE '$$ || hint || $$.%'
484 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
489 $_$ LANGUAGE PLPGSQL;
491 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
493 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
496 $_$ LANGUAGE PLPGSQL;
498 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
500 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
503 $_$ LANGUAGE PLPGSQL;
505 CREATE TABLE config.billing_type (
506 id SERIAL PRIMARY KEY,
508 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
509 default_price NUMERIC(6,2),
510 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
513 CREATE TABLE config.settings_group (
514 name TEXT PRIMARY KEY,
515 label TEXT UNIQUE NOT NULL -- I18N
518 CREATE TABLE config.org_unit_setting_type (
519 name TEXT PRIMARY KEY,
520 label TEXT UNIQUE NOT NULL,
521 grp TEXT REFERENCES config.settings_group (name),
523 datatype TEXT NOT NULL DEFAULT 'string',
528 -- define valid datatypes
530 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
531 ( 'bool', 'integer', 'float', 'currency', 'interval',
532 'date', 'string', 'object', 'array', 'link' ) ),
534 -- fm_class is meaningful only for 'link' datatype
536 CONSTRAINT coust_no_empty_link CHECK
537 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
538 ( datatype <> 'link' AND fm_class IS NULL ) )
541 CREATE TABLE config.usr_setting_type (
543 name TEXT PRIMARY KEY,
544 opac_visible BOOL NOT NULL DEFAULT FALSE,
545 label TEXT UNIQUE NOT NULL,
547 grp TEXT REFERENCES config.settings_group (name),
548 datatype TEXT NOT NULL DEFAULT 'string',
552 -- define valid datatypes
554 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
555 ( 'bool', 'integer', 'float', 'currency', 'interval',
556 'date', 'string', 'object', 'array', 'link' ) ),
559 -- fm_class is meaningful only for 'link' datatype
561 CONSTRAINT coust_no_empty_link CHECK
562 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
563 ( datatype <> 'link' AND fm_class IS NULL ) )
567 -- Some handy functions, based on existing ones, to provide optional ingest normalization
569 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
570 SELECT SUBSTRING($1,$2);
571 $func$ LANGUAGE SQL STRICT IMMUTABLE;
573 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
574 SELECT SUBSTRING($1,1,$2);
575 $func$ LANGUAGE SQL STRICT IMMUTABLE;
577 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
578 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
579 $func$ LANGUAGE SQL STRICT IMMUTABLE;
581 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
582 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
583 $func$ LANGUAGE SQL STRICT IMMUTABLE;
585 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
586 SELECT approximate_date( $1, '0');
587 $func$ LANGUAGE SQL STRICT IMMUTABLE;
589 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
590 SELECT approximate_date( $1, '9');
591 $func$ LANGUAGE SQL STRICT IMMUTABLE;
593 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
594 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
595 $func$ LANGUAGE SQL STRICT IMMUTABLE;
597 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
598 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
599 $func$ LANGUAGE SQL STRICT IMMUTABLE;
601 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
606 # Find the first ISBN, force it to ISBN13 and return it
610 foreach my $word (split(/\s/, $input)) {
611 my $isbn = Business::ISBN->new($word);
613 # First check the checksum; if it is not valid, fix it and add the original
614 # bad-checksum ISBN to the output
615 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
616 $isbn->fix_checksum();
619 # If we now have a valid ISBN, force it to ISBN13 and return it
620 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
623 $func$ LANGUAGE PLPERLU;
625 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
626 Inspired by translate_isbn1013
628 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
629 version without hypens and with a repaired checksum if the checksum was bad
633 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
638 # For each ISBN found in a single string containing a set of ISBNs:
639 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
640 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
645 foreach my $word (split(/\s/, $input)) {
646 my $isbn = Business::ISBN->new($word);
648 # First check the checksum; if it is not valid, fix it and add the original
649 # bad-checksum ISBN to the output
650 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
651 $output .= $isbn->isbn() . " ";
652 $isbn->fix_checksum();
655 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
656 # and add the normalized original ISBN to the output
657 if ($isbn && $isbn->is_valid()) {
658 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
659 $output .= $isbn->isbn . " ";
661 # If we successfully converted the ISBN to its counterpart, add the
662 # converted ISBN to the output as well
663 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
666 return $output if $output;
668 # If there were no valid ISBNs, just return the raw input
670 $func$ LANGUAGE PLPERLU;
672 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
673 The translate_isbn1013 function takes an input ISBN and returns the
674 following in a single space-delimited string if the input ISBN is valid:
675 - The normalized input ISBN (hyphens stripped)
676 - The normalized input ISBN with a fixed checksum if the checksum was bad
677 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
680 -- And ... a table in which to register them
682 CREATE TABLE config.index_normalizer (
683 id SERIAL PRIMARY KEY,
684 name TEXT UNIQUE NOT NULL,
687 param_count INT NOT NULL DEFAULT 0
690 CREATE TABLE config.metabib_field_index_norm_map (
691 id SERIAL PRIMARY KEY,
692 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
693 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
695 pos INT NOT NULL DEFAULT 0
698 CREATE TABLE config.record_attr_definition (
699 name TEXT PRIMARY KEY,
700 label TEXT NOT NULL, -- I18N
702 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
703 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
705 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
706 tag TEXT, -- LIKE format
707 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
709 -- This is used for both tag/sf and xpath entries
712 -- For xpath-extracted attrs
714 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
719 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
721 -- For phys-char fields
722 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
725 CREATE TABLE config.record_attr_index_norm_map (
726 id SERIAL PRIMARY KEY,
727 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
728 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
730 pos INT NOT NULL DEFAULT 0
733 CREATE TABLE config.coded_value_map (
734 id SERIAL PRIMARY KEY,
735 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
741 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
742 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
743 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
744 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
745 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
746 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
747 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
749 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
757 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
759 SELECT n.func AS func,
760 n.param_count AS param_count,
762 FROM config.index_normalizer n
763 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
764 WHERE field = NEW.field AND m.pos < 0
766 EXECUTE 'SELECT ' || normalizer.func || '(' ||
767 quote_literal( value ) ||
769 WHEN normalizer.param_count > 0
770 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
780 IF NEW.index_vector = ''::tsvector THEN
784 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
786 SELECT n.func AS func,
787 n.param_count AS param_count,
789 FROM config.index_normalizer n
790 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
791 WHERE field = NEW.field AND m.pos >= 0
793 EXECUTE 'SELECT ' || normalizer.func || '(' ||
794 quote_literal( value ) ||
796 WHEN normalizer.param_count > 0
797 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
805 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
806 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
808 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
815 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
816 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
817 SELECT DISTINCT l.version
818 FROM config.upgrade_log l
819 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
820 WHERE d.db_patch = $1
823 -- List applied db patches that are superseded by (and block the application of) my_db_patch
824 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
825 SELECT DISTINCT l.version
826 FROM config.upgrade_log l
827 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
828 WHERE d.db_patch = $1
831 -- List applied db patches that deprecates (and block the application of) my_db_patch
832 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
834 FROM config.db_patch_dependencies
835 WHERE ARRAY[$1]::TEXT[] && deprecates
838 -- List applied db patches that supersedes (and block the application of) my_db_patch
839 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
841 FROM config.db_patch_dependencies
842 WHERE ARRAY[$1]::TEXT[] && supersedes
845 -- Make sure that no deprecated or superseded db patches are currently applied
846 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
848 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
850 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
852 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
854 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
857 -- Raise an exception if there are, in fact, dep/sup conflict
858 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
863 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
864 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
865 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
867 Upgrade script % can not be applied:
868 applied deprecated scripts %
869 applied superseded scripts %
873 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
874 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
875 evergreen.upgrade_list_applied_deprecated(my_db_patch),
876 evergreen.upgrade_list_applied_superseded(my_db_patch);
879 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
884 CREATE TABLE config.barcode_completion (
885 id SERIAL PRIMARY KEY,
886 active BOOL NOT NULL DEFAULT true,
887 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
890 length INT NOT NULL DEFAULT 0,
892 padding_end BOOL NOT NULL DEFAULT false,
893 asset BOOL NOT NULL DEFAULT true,
894 actor BOOL NOT NULL DEFAULT true
897 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
899 -- Add support for logging, only keep the most recent five rows for each category.
902 CREATE TABLE config.org_unit_setting_type_log (
903 id BIGSERIAL PRIMARY KEY,
904 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
905 org INT, --REFERENCES actor.org_unit (id),
908 field_name TEXT REFERENCES config.org_unit_setting_type (name)
911 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
912 Org Unit setting Logs
914 This table contains the most recent changes to each setting
915 in actor.org_unit_setting, allowing for mistakes to be undone.
916 This is NOT meant to be an auditor, but rather an undo/redo.
919 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
921 -- Only keeps the most recent five settings changes.
922 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
923 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
925 IF (TG_OP = 'UPDATE') THEN
927 ELSIF (TG_OP = 'INSERT') THEN
932 $oustl_limit$ LANGUAGE plpgsql;
934 CREATE TRIGGER limit_logs_oust
935 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
936 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
938 CREATE TABLE config.sms_carrier (
939 id SERIAL PRIMARY KEY,
943 active BOOLEAN DEFAULT TRUE