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');
49 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
51 CREATE TABLE config.global_flag (
53 ) INHERITS (config.internal_flag);
54 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
56 CREATE TABLE config.upgrade_log (
57 version TEXT PRIMARY KEY,
58 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
62 CREATE TABLE config.db_patch_dependencies (
63 db_patch TEXT PRIMARY KEY,
68 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
74 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
76 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
82 CREATE TRIGGER no_overlapping_sups
83 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
84 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
86 CREATE TRIGGER no_overlapping_deps
87 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
88 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
90 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0738', :eg_version); -- senator/dbwells
91 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('2.3.0', :eg_version);
93 CREATE TABLE config.bib_source (
94 id SERIAL PRIMARY KEY,
95 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
96 source TEXT NOT NULL UNIQUE,
97 transcendant BOOL NOT NULL DEFAULT FALSE,
98 can_have_copies BOOL NOT NULL DEFAULT TRUE
100 COMMENT ON TABLE config.bib_source IS $$
101 This is table is used to set up the relative "quality" of each
102 MARC source, such as OCLC. Also identifies "transcendant" sources,
103 i.e., sources of bib records that should display in the OPAC
104 even if no copies or located URIs are attached. Also indicates if
105 the source is allowed to have actual copies on its bibs. Volumes
106 for targeted URIs are unaffected by this setting.
109 CREATE TABLE config.standing (
110 id SERIAL PRIMARY KEY,
111 value TEXT NOT NULL UNIQUE
113 COMMENT ON TABLE config.standing IS $$
116 This table contains the values that can be applied to a patron
117 by a staff member. These values should not be changed, other
118 than for translation, as the ID column is currently a "magic
119 number" in the source. :(
122 CREATE TABLE config.standing_penalty (
123 id SERIAL PRIMARY KEY,
124 name TEXT NOT NULL UNIQUE,
127 staff_alert BOOL NOT NULL DEFAULT FALSE,
131 CREATE TABLE config.xml_transform (
132 name TEXT PRIMARY KEY,
133 namespace_uri TEXT NOT NULL,
134 prefix TEXT NOT NULL,
138 CREATE TABLE config.biblio_fingerprint (
139 id SERIAL PRIMARY KEY,
142 first_word BOOL NOT NULL DEFAULT FALSE,
143 format TEXT NOT NULL DEFAULT 'marcxml'
146 INSERT INTO config.biblio_fingerprint (name, xpath, format)
149 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
150 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
151 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
152 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
153 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
157 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
160 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
161 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
162 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
163 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
164 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
169 CREATE TABLE config.metabib_class (
170 name TEXT PRIMARY KEY,
171 label TEXT NOT NULL UNIQUE,
172 buoyant BOOL DEFAULT FALSE NOT NULL,
173 restrict BOOL DEFAULT FALSE NOT NULL
176 CREATE TABLE config.metabib_field (
177 id SERIAL PRIMARY KEY,
178 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
182 weight INT NOT NULL DEFAULT 1,
183 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
184 search_field BOOL NOT NULL DEFAULT TRUE,
185 facet_field BOOL NOT NULL DEFAULT FALSE,
186 browse_field BOOL NOT NULL DEFAULT TRUE,
189 restrict BOOL DEFAULT FALSE NOT NULL
191 COMMENT ON TABLE config.metabib_field IS $$
192 XPath used for record indexing ingest
194 This table contains the XPath used to chop up MODS into its
195 indexable parts. Each XPath entry is named and assigned to
196 a "class" of either title, subject, author, keyword, series
200 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
202 CREATE TABLE config.metabib_search_alias (
203 alias TEXT PRIMARY KEY,
204 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
205 field INT REFERENCES config.metabib_field (id)
208 CREATE TABLE config.non_cataloged_type (
209 id SERIAL PRIMARY KEY,
210 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
212 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
213 in_house BOOL NOT NULL DEFAULT FALSE,
214 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
216 COMMENT ON TABLE config.non_cataloged_type IS $$
217 Types of valid non-cataloged items.
220 CREATE TABLE config.identification_type (
221 id SERIAL PRIMARY KEY,
222 name TEXT NOT NULL UNIQUE
224 COMMENT ON TABLE config.identification_type IS $$
225 Types of valid patron identification.
227 Each patron must display at least one valid form of identification
228 in order to get a library card. This table lists those forms.
231 CREATE TABLE config.rule_circ_duration (
232 id SERIAL PRIMARY KEY,
233 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
234 extended INTERVAL NOT NULL,
235 normal INTERVAL NOT NULL,
236 shrt INTERVAL NOT NULL,
237 max_renewals INT NOT NULL
239 COMMENT ON TABLE config.rule_circ_duration IS $$
240 Circulation Duration rules
242 Each circulation is given a duration based on one of these rules.
245 CREATE TABLE config.hard_due_date (
246 id SERIAL PRIMARY KEY,
247 name TEXT NOT NULL UNIQUE,
248 ceiling_date TIMESTAMPTZ NOT NULL,
249 forceto BOOL NOT NULL,
250 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
253 CREATE TABLE config.hard_due_date_values (
254 id SERIAL PRIMARY KEY,
255 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
256 DEFERRABLE INITIALLY DEFERRED,
257 ceiling_date TIMESTAMPTZ NOT NULL,
258 active_date TIMESTAMPTZ NOT NULL
261 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
263 temp_value config.hard_due_date_values%ROWTYPE;
267 SELECT DISTINCT ON (hard_due_date) *
268 FROM config.hard_due_date_values
269 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
270 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
272 UPDATE config.hard_due_date
273 SET ceiling_date = temp_value.ceiling_date
274 WHERE id = temp_value.hard_due_date
275 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
278 updated := updated + 1;
284 $func$ LANGUAGE plpgsql;
286 CREATE TABLE config.rule_max_fine (
287 id SERIAL PRIMARY KEY,
288 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
289 amount NUMERIC(6,2) NOT NULL,
290 is_percent BOOL NOT NULL DEFAULT FALSE
292 COMMENT ON TABLE config.rule_max_fine IS $$
293 Circulation Max Fine rules
295 Each circulation is given a maximum fine based on one of
299 CREATE TABLE config.rule_recurring_fine (
300 id SERIAL PRIMARY KEY,
301 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
302 high NUMERIC(6,2) NOT NULL,
303 normal NUMERIC(6,2) NOT NULL,
304 low NUMERIC(6,2) NOT NULL,
305 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
306 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
308 COMMENT ON TABLE config.rule_recurring_fine IS $$
309 Circulation Recurring Fine rules
311 Each circulation is given a recurring fine amount based on one of
312 these rules. Note that it is recommended to run the fine generator
313 (from cron) at least as frequently as the lowest recurrence interval
314 used by your circulation rules so that accrued fines will be up
319 CREATE TABLE config.rule_age_hold_protect (
320 id SERIAL PRIMARY KEY,
321 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
322 age INTERVAL NOT NULL,
325 COMMENT ON TABLE config.rule_age_hold_protect IS $$
326 Hold Item Age Protection rules
328 A hold request can only capture new(ish) items when they are
329 within a particular proximity of the pickup_lib of the request.
330 The proximity ('prox' column) is calculated by counting
331 the number of tree edges between the pickup_lib and either the
332 owning_lib or circ_lib of the copy that could fulfill the hold,
333 as determined by the distance_is_from_owner value of the hold matrix
334 rule controlling the hold request.
337 CREATE TABLE config.copy_status (
338 id SERIAL PRIMARY KEY,
339 name TEXT NOT NULL UNIQUE,
340 holdable BOOL NOT NULL DEFAULT FALSE,
341 opac_visible BOOL NOT NULL DEFAULT FALSE,
342 copy_active BOOL NOT NULL DEFAULT FALSE,
343 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
345 COMMENT ON TABLE config.copy_status IS $$
348 The available copy statuses, and whether a copy in that
349 status is available for hold request capture. 0 (zero) is
350 the only special number in this set, meaning that the item
351 is available for immediate checkout, and is counted as available
354 Statuses with an ID below 100 are not removable, and have special
355 meaning in the code. Do not change them except to translate the
358 You may add and remove statuses above 100, and these can be used
359 to remove items from normal circulation without affecting the rest
360 of the copy's values or its location.
363 CREATE TABLE config.net_access_level (
364 id SERIAL PRIMARY KEY,
365 name TEXT NOT NULL UNIQUE
367 COMMENT ON TABLE config.net_access_level IS $$
368 Patron Network Access level
370 This will be used to inform the in-library firewall of how much
371 internet access the using patron should be allowed.
375 CREATE TABLE config.remote_account (
376 id SERIAL PRIMARY KEY,
378 host TEXT NOT NULL, -- name or IP, :port optional
379 username TEXT, -- optional, since we could default to $USER
380 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
381 account TEXT, -- aka profile or FTP "account" command
382 path TEXT, -- aka directory
383 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
384 last_activity TIMESTAMP WITH TIME ZONE
387 CREATE TABLE config.marc21_rec_type_map (
388 code TEXT PRIMARY KEY,
389 type_val TEXT NOT NULL,
390 blvl_val TEXT NOT NULL
393 CREATE TABLE config.marc21_ff_pos_map (
394 id SERIAL PRIMARY KEY,
395 fixed_field TEXT NOT NULL,
397 rec_type TEXT NOT NULL,
398 start_pos INT NOT NULL,
400 default_val TEXT NOT NULL DEFAULT ' '
403 CREATE TABLE config.marc21_physical_characteristic_type_map (
404 ptype_key TEXT PRIMARY KEY,
405 label TEXT NOT NULL -- I18N
408 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
409 id SERIAL PRIMARY KEY,
410 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
411 subfield TEXT NOT NULL,
412 start_pos INT NOT NULL,
414 label TEXT NOT NULL -- I18N
417 CREATE TABLE config.marc21_physical_characteristic_value_map (
418 id SERIAL PRIMARY KEY,
420 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
421 label TEXT NOT NULL -- I18N
425 CREATE TABLE config.z3950_source (
426 name TEXT PRIMARY KEY,
427 label TEXT NOT NULL UNIQUE,
431 record_format TEXT NOT NULL DEFAULT 'FI',
432 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
433 auth BOOL NOT NULL DEFAULT TRUE,
434 use_perm INT -- REFERENCES permission.perm_list (id)
437 COMMENT ON TABLE config.z3950_source IS $$
440 Each row in this table represents a database searchable via Z39.50.
443 COMMENT ON COLUMN config.z3950_source.record_format IS $$
447 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
448 Z39.50 preferred record syntax..
451 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
452 If set, this permission is required for the source to be listed in the staff
453 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
456 CREATE TABLE config.z3950_attr (
457 id SERIAL PRIMARY KEY,
458 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
463 truncation INT NOT NULL DEFAULT 0,
464 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
467 CREATE TABLE config.i18n_locale (
468 code TEXT PRIMARY KEY,
469 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
470 name TEXT UNIQUE NOT NULL,
474 CREATE TABLE config.i18n_core (
475 id BIGSERIAL PRIMARY KEY,
476 fq_field TEXT NOT NULL,
477 identity_value TEXT NOT NULL,
478 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
482 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
484 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
488 UPDATE config.i18n_core
489 SET identity_value = $$ || quote_literal(new_ident) || $$
490 WHERE fq_field LIKE '$$ || hint || $$.%'
491 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
496 $_$ LANGUAGE PLPGSQL;
498 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
500 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
503 $_$ LANGUAGE PLPGSQL;
505 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
507 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
510 $_$ LANGUAGE PLPGSQL;
512 CREATE TABLE config.billing_type (
513 id SERIAL PRIMARY KEY,
515 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
516 default_price NUMERIC(6,2),
517 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
520 CREATE TABLE config.settings_group (
521 name TEXT PRIMARY KEY,
522 label TEXT UNIQUE NOT NULL -- I18N
525 CREATE TABLE config.org_unit_setting_type (
526 name TEXT PRIMARY KEY,
527 label TEXT UNIQUE NOT NULL,
528 grp TEXT REFERENCES config.settings_group (name),
530 datatype TEXT NOT NULL DEFAULT 'string',
535 -- define valid datatypes
537 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
538 ( 'bool', 'integer', 'float', 'currency', 'interval',
539 'date', 'string', 'object', 'array', 'link' ) ),
541 -- fm_class is meaningful only for 'link' datatype
543 CONSTRAINT coust_no_empty_link CHECK
544 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
545 ( datatype <> 'link' AND fm_class IS NULL ) )
548 CREATE TABLE config.usr_setting_type (
550 name TEXT PRIMARY KEY,
551 opac_visible BOOL NOT NULL DEFAULT FALSE,
552 label TEXT UNIQUE NOT NULL,
554 grp TEXT REFERENCES config.settings_group (name),
555 datatype TEXT NOT NULL DEFAULT 'string',
559 -- define valid datatypes
561 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
562 ( 'bool', 'integer', 'float', 'currency', 'interval',
563 'date', 'string', 'object', 'array', 'link' ) ),
566 -- fm_class is meaningful only for 'link' datatype
568 CONSTRAINT coust_no_empty_link CHECK
569 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
570 ( datatype <> 'link' AND fm_class IS NULL ) )
574 -- Some handy functions, based on existing ones, to provide optional ingest normalization
576 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
577 SELECT SUBSTRING($1,$2);
578 $func$ LANGUAGE SQL STRICT IMMUTABLE;
580 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
581 SELECT SUBSTRING($1,1,$2);
582 $func$ LANGUAGE SQL STRICT IMMUTABLE;
584 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
585 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
586 $func$ LANGUAGE SQL STRICT IMMUTABLE;
588 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
589 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
590 $func$ LANGUAGE SQL STRICT IMMUTABLE;
592 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
593 SELECT approximate_date( $1, '0');
594 $func$ LANGUAGE SQL STRICT IMMUTABLE;
596 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
597 SELECT approximate_date( $1, '9');
598 $func$ LANGUAGE SQL STRICT IMMUTABLE;
600 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
601 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
602 $func$ LANGUAGE SQL STRICT IMMUTABLE;
604 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
605 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
606 $func$ LANGUAGE SQL STRICT IMMUTABLE;
608 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
613 # Find the first ISBN, force it to ISBN13 and return it
617 foreach my $word (split(/\s/, $input)) {
618 my $isbn = Business::ISBN->new($word);
620 # First check the checksum; if it is not valid, fix it and add the original
621 # bad-checksum ISBN to the output
622 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
623 $isbn->fix_checksum();
626 # If we now have a valid ISBN, force it to ISBN13 and return it
627 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
630 $func$ LANGUAGE PLPERLU;
632 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
633 Inspired by translate_isbn1013
635 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
636 version without hypens and with a repaired checksum if the checksum was bad
640 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
645 # For each ISBN found in a single string containing a set of ISBNs:
646 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
647 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
652 foreach my $word (split(/\s/, $input)) {
653 my $isbn = Business::ISBN->new($word);
655 # First check the checksum; if it is not valid, fix it and add the original
656 # bad-checksum ISBN to the output
657 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
658 $output .= $isbn->isbn() . " ";
659 $isbn->fix_checksum();
662 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
663 # and add the normalized original ISBN to the output
664 if ($isbn && $isbn->is_valid()) {
665 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
666 $output .= $isbn->isbn . " ";
668 # If we successfully converted the ISBN to its counterpart, add the
669 # converted ISBN to the output as well
670 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
673 return $output if $output;
675 # If there were no valid ISBNs, just return the raw input
677 $func$ LANGUAGE PLPERLU;
679 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
680 The translate_isbn1013 function takes an input ISBN and returns the
681 following in a single space-delimited string if the input ISBN is valid:
682 - The normalized input ISBN (hyphens stripped)
683 - The normalized input ISBN with a fixed checksum if the checksum was bad
684 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
687 -- And ... a table in which to register them
689 CREATE TABLE config.index_normalizer (
690 id SERIAL PRIMARY KEY,
691 name TEXT UNIQUE NOT NULL,
694 param_count INT NOT NULL DEFAULT 0
697 CREATE TABLE config.metabib_field_index_norm_map (
698 id SERIAL PRIMARY KEY,
699 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
700 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
702 pos INT NOT NULL DEFAULT 0
705 CREATE TABLE config.record_attr_definition (
706 name TEXT PRIMARY KEY,
707 label TEXT NOT NULL, -- I18N
709 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
710 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
712 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
713 tag TEXT, -- LIKE format
714 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
716 -- This is used for both tag/sf and xpath entries
719 -- For xpath-extracted attrs
721 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
726 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
728 -- For phys-char fields
729 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
732 CREATE TABLE config.record_attr_index_norm_map (
733 id SERIAL PRIMARY KEY,
734 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
735 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
737 pos INT NOT NULL DEFAULT 0
740 CREATE TABLE config.coded_value_map (
741 id SERIAL PRIMARY KEY,
742 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
746 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
748 is_simple BOOL NOT NULL DEFAULT FALSE
751 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
752 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
753 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
754 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
755 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
756 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
757 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
759 CREATE OR REPLACE FUNCTION config.update_coded_value_map(in_ctype TEXT, in_code TEXT, in_value TEXT, in_description TEXT DEFAULT NULL, in_opac_visible BOOL DEFAULT NULL, in_search_label TEXT DEFAULT NULL, in_is_simple BOOL DEFAULT NULL, add_only BOOL DEFAULT FALSE) RETURNS VOID AS $f$
761 current_row config.coded_value_map%ROWTYPE;
763 -- Look for a current value
764 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
767 -- Update anything we were handed
768 current_row.value := COALESCE(current_row.value, in_value);
769 current_row.description := COALESCE(current_row.description, in_description);
770 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
771 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
772 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
773 UPDATE config.coded_value_map
775 value = current_row.value,
776 description = current_row.description,
777 opac_visible = current_row.opac_visible,
778 search_label = current_row.search_label,
779 is_simple = current_row.is_simple
780 WHERE id = current_row.id;
781 ELSIF NOT add_only THEN
782 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
783 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
786 $f$ LANGUAGE PLPGSQL;
788 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
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'\'')
819 IF NEW.index_vector = ''::tsvector THEN
823 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
825 SELECT n.func AS func,
826 n.param_count AS param_count,
828 FROM config.index_normalizer n
829 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
830 WHERE field = NEW.field AND m.pos >= 0
832 EXECUTE 'SELECT ' || normalizer.func || '(' ||
833 quote_literal( value ) ||
835 WHEN normalizer.param_count > 0
836 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
844 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
845 value := ARRAY_TO_STRING(
846 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
848 value := public.search_normalize(value);
851 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
857 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
858 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
859 SELECT DISTINCT l.version
860 FROM config.upgrade_log l
861 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
862 WHERE d.db_patch = $1
865 -- List applied db patches that are superseded by (and block the application of) my_db_patch
866 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
867 SELECT DISTINCT l.version
868 FROM config.upgrade_log l
869 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
870 WHERE d.db_patch = $1
873 -- List applied db patches that deprecates (and block the application of) my_db_patch
874 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
876 FROM config.db_patch_dependencies
877 WHERE ARRAY[$1]::TEXT[] && deprecates
880 -- List applied db patches that supersedes (and block the application of) my_db_patch
881 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
883 FROM config.db_patch_dependencies
884 WHERE ARRAY[$1]::TEXT[] && supersedes
887 -- Make sure that no deprecated or superseded db patches are currently applied
888 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
890 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
892 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
894 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
896 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
899 -- Raise an exception if there are, in fact, dep/sup conflict
900 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
905 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
906 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
907 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
909 Upgrade script % can not be applied:
910 applied deprecated scripts %
911 applied superseded scripts %
915 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
916 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
917 evergreen.upgrade_list_applied_deprecated(my_db_patch),
918 evergreen.upgrade_list_applied_superseded(my_db_patch);
921 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
926 CREATE TABLE config.barcode_completion (
927 id SERIAL PRIMARY KEY,
928 active BOOL NOT NULL DEFAULT true,
929 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
932 length INT NOT NULL DEFAULT 0,
934 padding_end BOOL NOT NULL DEFAULT false,
935 asset BOOL NOT NULL DEFAULT true,
936 actor BOOL NOT NULL DEFAULT true
939 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
941 -- Add support for logging, only keep the most recent five rows for each category.
944 CREATE TABLE config.org_unit_setting_type_log (
945 id BIGSERIAL PRIMARY KEY,
946 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
947 org INT, --REFERENCES actor.org_unit (id),
950 field_name TEXT REFERENCES config.org_unit_setting_type (name)
953 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
954 Org Unit setting Logs
956 This table contains the most recent changes to each setting
957 in actor.org_unit_setting, allowing for mistakes to be undone.
958 This is NOT meant to be an auditor, but rather an undo/redo.
961 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
963 -- Only keeps the most recent five settings changes.
964 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
965 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
967 IF (TG_OP = 'UPDATE') THEN
969 ELSIF (TG_OP = 'INSERT') THEN
974 $oustl_limit$ LANGUAGE plpgsql;
976 CREATE TRIGGER limit_logs_oust
977 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
978 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
980 CREATE TABLE config.sms_carrier (
981 id SERIAL PRIMARY KEY,
985 active BOOLEAN DEFAULT TRUE
988 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
990 CREATE TABLE config.usr_activity_type (
991 id SERIAL PRIMARY KEY,
995 label TEXT NOT NULL, -- i18n
996 egroup config.usr_activity_group NOT NULL,
997 enabled BOOL NOT NULL DEFAULT TRUE,
998 transient BOOL NOT NULL DEFAULT FALSE,
999 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1002 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1003 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));