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 ('0702', :eg_version); -- berick/miker
92 CREATE TABLE config.bib_source (
93 id SERIAL PRIMARY KEY,
94 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
95 source TEXT NOT NULL UNIQUE,
96 transcendant BOOL NOT NULL DEFAULT FALSE,
97 can_have_copies BOOL NOT NULL DEFAULT TRUE
99 COMMENT ON TABLE config.bib_source IS $$
100 This is table is used to set up the relative "quality" of each
101 MARC source, such as OCLC. Also identifies "transcendant" sources,
102 i.e., sources of bib records that should display in the OPAC
103 even if no copies or located URIs are attached. Also indicates if
104 the source is allowed to have actual copies on its bibs. Volumes
105 for targeted URIs are unaffected by this setting.
108 CREATE TABLE config.standing (
109 id SERIAL PRIMARY KEY,
110 value TEXT NOT NULL UNIQUE
112 COMMENT ON TABLE config.standing IS $$
115 This table contains the values that can be applied to a patron
116 by a staff member. These values should not be changed, other
117 than for translation, as the ID column is currently a "magic
118 number" in the source. :(
121 CREATE TABLE config.standing_penalty (
122 id SERIAL PRIMARY KEY,
123 name TEXT NOT NULL UNIQUE,
126 staff_alert BOOL NOT NULL DEFAULT FALSE,
130 CREATE TABLE config.xml_transform (
131 name TEXT PRIMARY KEY,
132 namespace_uri TEXT NOT NULL,
133 prefix TEXT NOT NULL,
137 CREATE TABLE config.biblio_fingerprint (
138 id SERIAL PRIMARY KEY,
141 first_word BOOL NOT NULL DEFAULT FALSE,
142 format TEXT NOT NULL DEFAULT 'marcxml'
145 INSERT INTO config.biblio_fingerprint (name, xpath, format)
148 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
149 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
150 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
151 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
152 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
156 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
159 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
160 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
161 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
162 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
163 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
168 CREATE TABLE config.metabib_class (
169 name TEXT PRIMARY KEY,
170 label TEXT NOT NULL UNIQUE,
171 buoyant BOOL DEFAULT FALSE NOT NULL,
172 restrict BOOL DEFAULT FALSE NOT NULL
175 CREATE TABLE config.metabib_field (
176 id SERIAL PRIMARY KEY,
177 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
181 weight INT NOT NULL DEFAULT 1,
182 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
183 search_field BOOL NOT NULL DEFAULT TRUE,
184 facet_field BOOL NOT NULL DEFAULT FALSE,
185 browse_field BOOL NOT NULL DEFAULT TRUE,
188 restrict BOOL DEFAULT FALSE NOT NULL
190 COMMENT ON TABLE config.metabib_field IS $$
191 XPath used for record indexing ingest
193 This table contains the XPath used to chop up MODS into its
194 indexable parts. Each XPath entry is named and assigned to
195 a "class" of either title, subject, author, keyword, series
199 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
201 CREATE TABLE config.metabib_search_alias (
202 alias TEXT PRIMARY KEY,
203 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
204 field INT REFERENCES config.metabib_field (id)
207 CREATE TABLE config.non_cataloged_type (
208 id SERIAL PRIMARY KEY,
209 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
211 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
212 in_house BOOL NOT NULL DEFAULT FALSE,
213 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
215 COMMENT ON TABLE config.non_cataloged_type IS $$
216 Types of valid non-cataloged items.
219 CREATE TABLE config.identification_type (
220 id SERIAL PRIMARY KEY,
221 name TEXT NOT NULL UNIQUE
223 COMMENT ON TABLE config.identification_type IS $$
224 Types of valid patron identification.
226 Each patron must display at least one valid form of identification
227 in order to get a library card. This table lists those forms.
230 CREATE TABLE config.rule_circ_duration (
231 id SERIAL PRIMARY KEY,
232 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
233 extended INTERVAL NOT NULL,
234 normal INTERVAL NOT NULL,
235 shrt INTERVAL NOT NULL,
236 max_renewals INT NOT NULL
238 COMMENT ON TABLE config.rule_circ_duration IS $$
239 Circulation Duration rules
241 Each circulation is given a duration based on one of these rules.
244 CREATE TABLE config.hard_due_date (
245 id SERIAL PRIMARY KEY,
246 name TEXT NOT NULL UNIQUE,
247 ceiling_date TIMESTAMPTZ NOT NULL,
248 forceto BOOL NOT NULL,
249 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
252 CREATE TABLE config.hard_due_date_values (
253 id SERIAL PRIMARY KEY,
254 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
255 DEFERRABLE INITIALLY DEFERRED,
256 ceiling_date TIMESTAMPTZ NOT NULL,
257 active_date TIMESTAMPTZ NOT NULL
260 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
262 temp_value config.hard_due_date_values%ROWTYPE;
266 SELECT DISTINCT ON (hard_due_date) *
267 FROM config.hard_due_date_values
268 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
269 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
271 UPDATE config.hard_due_date
272 SET ceiling_date = temp_value.ceiling_date
273 WHERE id = temp_value.hard_due_date
274 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
277 updated := updated + 1;
283 $func$ LANGUAGE plpgsql;
285 CREATE TABLE config.rule_max_fine (
286 id SERIAL PRIMARY KEY,
287 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
288 amount NUMERIC(6,2) NOT NULL,
289 is_percent BOOL NOT NULL DEFAULT FALSE
291 COMMENT ON TABLE config.rule_max_fine IS $$
292 Circulation Max Fine rules
294 Each circulation is given a maximum fine based on one of
298 CREATE TABLE config.rule_recurring_fine (
299 id SERIAL PRIMARY KEY,
300 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
301 high NUMERIC(6,2) NOT NULL,
302 normal NUMERIC(6,2) NOT NULL,
303 low NUMERIC(6,2) NOT NULL,
304 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
305 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
307 COMMENT ON TABLE config.rule_recurring_fine IS $$
308 Circulation Recurring Fine rules
310 Each circulation is given a recurring fine amount based on one of
311 these rules. Note that it is recommended to run the fine generator
312 (from cron) at least as frequently as the lowest recurrence interval
313 used by your circulation rules so that accrued fines will be up
318 CREATE TABLE config.rule_age_hold_protect (
319 id SERIAL PRIMARY KEY,
320 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
321 age INTERVAL NOT NULL,
324 COMMENT ON TABLE config.rule_age_hold_protect IS $$
325 Hold Item Age Protection rules
327 A hold request can only capture new(ish) items when they are
328 within a particular proximity of the pickup_lib of the request.
329 The proximity ('prox' column) is calculated by counting
330 the number of tree edges between the pickup_lib and either the
331 owning_lib or circ_lib of the copy that could fulfill the hold,
332 as determined by the distance_is_from_owner value of the hold matrix
333 rule controlling the hold request.
336 CREATE TABLE config.copy_status (
337 id SERIAL PRIMARY KEY,
338 name TEXT NOT NULL UNIQUE,
339 holdable BOOL NOT NULL DEFAULT FALSE,
340 opac_visible BOOL NOT NULL DEFAULT FALSE,
341 copy_active BOOL NOT NULL DEFAULT FALSE,
342 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
344 COMMENT ON TABLE config.copy_status IS $$
347 The available copy statuses, and whether a copy in that
348 status is available for hold request capture. 0 (zero) is
349 the only special number in this set, meaning that the item
350 is available for immediate checkout, and is counted as available
353 Statuses with an ID below 100 are not removable, and have special
354 meaning in the code. Do not change them except to translate the
357 You may add and remove statuses above 100, and these can be used
358 to remove items from normal circulation without affecting the rest
359 of the copy's values or its location.
362 CREATE TABLE config.net_access_level (
363 id SERIAL PRIMARY KEY,
364 name TEXT NOT NULL UNIQUE
366 COMMENT ON TABLE config.net_access_level IS $$
367 Patron Network Access level
369 This will be used to inform the in-library firewall of how much
370 internet access the using patron should be allowed.
374 CREATE TABLE config.remote_account (
375 id SERIAL PRIMARY KEY,
377 host TEXT NOT NULL, -- name or IP, :port optional
378 username TEXT, -- optional, since we could default to $USER
379 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
380 account TEXT, -- aka profile or FTP "account" command
381 path TEXT, -- aka directory
382 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
383 last_activity TIMESTAMP WITH TIME ZONE
386 CREATE TABLE config.marc21_rec_type_map (
387 code TEXT PRIMARY KEY,
388 type_val TEXT NOT NULL,
389 blvl_val TEXT NOT NULL
392 CREATE TABLE config.marc21_ff_pos_map (
393 id SERIAL PRIMARY KEY,
394 fixed_field TEXT NOT NULL,
396 rec_type TEXT NOT NULL,
397 start_pos INT NOT NULL,
399 default_val TEXT NOT NULL DEFAULT ' '
402 CREATE TABLE config.marc21_physical_characteristic_type_map (
403 ptype_key TEXT PRIMARY KEY,
404 label TEXT NOT NULL -- I18N
407 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
408 id SERIAL PRIMARY KEY,
409 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
410 subfield TEXT NOT NULL,
411 start_pos INT NOT NULL,
413 label TEXT NOT NULL -- I18N
416 CREATE TABLE config.marc21_physical_characteristic_value_map (
417 id SERIAL PRIMARY KEY,
419 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
420 label TEXT NOT NULL -- I18N
424 CREATE TABLE config.z3950_source (
425 name TEXT PRIMARY KEY,
426 label TEXT NOT NULL UNIQUE,
430 record_format TEXT NOT NULL DEFAULT 'FI',
431 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
432 auth BOOL NOT NULL DEFAULT TRUE,
433 use_perm INT -- REFERENCES permission.perm_list (id)
436 COMMENT ON TABLE config.z3950_source IS $$
439 Each row in this table represents a database searchable via Z39.50.
442 COMMENT ON COLUMN config.z3950_source.record_format IS $$
446 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
447 Z39.50 preferred record syntax..
450 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
451 If set, this permission is required for the source to be listed in the staff
452 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
455 CREATE TABLE config.z3950_attr (
456 id SERIAL PRIMARY KEY,
457 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
462 truncation INT NOT NULL DEFAULT 0,
463 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
466 CREATE TABLE config.i18n_locale (
467 code TEXT PRIMARY KEY,
468 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
469 name TEXT UNIQUE NOT NULL,
473 CREATE TABLE config.i18n_core (
474 id BIGSERIAL PRIMARY KEY,
475 fq_field TEXT NOT NULL,
476 identity_value TEXT NOT NULL,
477 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
481 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
483 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
487 UPDATE config.i18n_core
488 SET identity_value = $$ || quote_literal(new_ident) || $$
489 WHERE fq_field LIKE '$$ || hint || $$.%'
490 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
495 $_$ LANGUAGE PLPGSQL;
497 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
499 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
502 $_$ LANGUAGE PLPGSQL;
504 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
506 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
509 $_$ LANGUAGE PLPGSQL;
511 CREATE TABLE config.billing_type (
512 id SERIAL PRIMARY KEY,
514 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
515 default_price NUMERIC(6,2),
516 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
519 CREATE TABLE config.settings_group (
520 name TEXT PRIMARY KEY,
521 label TEXT UNIQUE NOT NULL -- I18N
524 CREATE TABLE config.org_unit_setting_type (
525 name TEXT PRIMARY KEY,
526 label TEXT UNIQUE NOT NULL,
527 grp TEXT REFERENCES config.settings_group (name),
529 datatype TEXT NOT NULL DEFAULT 'string',
534 -- define valid datatypes
536 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
537 ( 'bool', 'integer', 'float', 'currency', 'interval',
538 'date', 'string', 'object', 'array', 'link' ) ),
540 -- fm_class is meaningful only for 'link' datatype
542 CONSTRAINT coust_no_empty_link CHECK
543 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
544 ( datatype <> 'link' AND fm_class IS NULL ) )
547 CREATE TABLE config.usr_setting_type (
549 name TEXT PRIMARY KEY,
550 opac_visible BOOL NOT NULL DEFAULT FALSE,
551 label TEXT UNIQUE NOT NULL,
553 grp TEXT REFERENCES config.settings_group (name),
554 datatype TEXT NOT NULL DEFAULT 'string',
558 -- define valid datatypes
560 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
561 ( 'bool', 'integer', 'float', 'currency', 'interval',
562 'date', 'string', 'object', 'array', 'link' ) ),
565 -- fm_class is meaningful only for 'link' datatype
567 CONSTRAINT coust_no_empty_link CHECK
568 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
569 ( datatype <> 'link' AND fm_class IS NULL ) )
573 -- Some handy functions, based on existing ones, to provide optional ingest normalization
575 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
576 SELECT SUBSTRING($1,$2);
577 $func$ LANGUAGE SQL STRICT IMMUTABLE;
579 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
580 SELECT SUBSTRING($1,1,$2);
581 $func$ LANGUAGE SQL STRICT IMMUTABLE;
583 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
584 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
585 $func$ LANGUAGE SQL STRICT IMMUTABLE;
587 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
588 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
589 $func$ LANGUAGE SQL STRICT IMMUTABLE;
591 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
592 SELECT approximate_date( $1, '0');
593 $func$ LANGUAGE SQL STRICT IMMUTABLE;
595 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
596 SELECT approximate_date( $1, '9');
597 $func$ LANGUAGE SQL STRICT IMMUTABLE;
599 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
600 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
601 $func$ LANGUAGE SQL STRICT IMMUTABLE;
603 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
604 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
605 $func$ LANGUAGE SQL STRICT IMMUTABLE;
607 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
612 # Find the first ISBN, force it to ISBN13 and return it
616 foreach my $word (split(/\s/, $input)) {
617 my $isbn = Business::ISBN->new($word);
619 # First check the checksum; if it is not valid, fix it and add the original
620 # bad-checksum ISBN to the output
621 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
622 $isbn->fix_checksum();
625 # If we now have a valid ISBN, force it to ISBN13 and return it
626 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
629 $func$ LANGUAGE PLPERLU;
631 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
632 Inspired by translate_isbn1013
634 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
635 version without hypens and with a repaired checksum if the checksum was bad
639 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
644 # For each ISBN found in a single string containing a set of ISBNs:
645 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
646 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
651 foreach my $word (split(/\s/, $input)) {
652 my $isbn = Business::ISBN->new($word);
654 # First check the checksum; if it is not valid, fix it and add the original
655 # bad-checksum ISBN to the output
656 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
657 $output .= $isbn->isbn() . " ";
658 $isbn->fix_checksum();
661 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
662 # and add the normalized original ISBN to the output
663 if ($isbn && $isbn->is_valid()) {
664 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
665 $output .= $isbn->isbn . " ";
667 # If we successfully converted the ISBN to its counterpart, add the
668 # converted ISBN to the output as well
669 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
672 return $output if $output;
674 # If there were no valid ISBNs, just return the raw input
676 $func$ LANGUAGE PLPERLU;
678 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
679 The translate_isbn1013 function takes an input ISBN and returns the
680 following in a single space-delimited string if the input ISBN is valid:
681 - The normalized input ISBN (hyphens stripped)
682 - The normalized input ISBN with a fixed checksum if the checksum was bad
683 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
686 -- And ... a table in which to register them
688 CREATE TABLE config.index_normalizer (
689 id SERIAL PRIMARY KEY,
690 name TEXT UNIQUE NOT NULL,
693 param_count INT NOT NULL DEFAULT 0
696 CREATE TABLE config.metabib_field_index_norm_map (
697 id SERIAL PRIMARY KEY,
698 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
699 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
701 pos INT NOT NULL DEFAULT 0
704 CREATE TABLE config.record_attr_definition (
705 name TEXT PRIMARY KEY,
706 label TEXT NOT NULL, -- I18N
708 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
709 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
711 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
712 tag TEXT, -- LIKE format
713 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
715 -- This is used for both tag/sf and xpath entries
718 -- For xpath-extracted attrs
720 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
725 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
727 -- For phys-char fields
728 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
731 CREATE TABLE config.record_attr_index_norm_map (
732 id SERIAL PRIMARY KEY,
733 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
734 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
736 pos INT NOT NULL DEFAULT 0
739 CREATE TABLE config.coded_value_map (
740 id SERIAL PRIMARY KEY,
741 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
747 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
748 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
749 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
750 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
751 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
752 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
753 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
755 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
763 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
765 SELECT n.func AS func,
766 n.param_count AS param_count,
768 FROM config.index_normalizer n
769 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
770 WHERE field = NEW.field AND m.pos < 0
772 EXECUTE 'SELECT ' || normalizer.func || '(' ||
773 quote_literal( value ) ||
775 WHEN normalizer.param_count > 0
776 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
786 IF NEW.index_vector = ''::tsvector THEN
790 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
792 SELECT n.func AS func,
793 n.param_count AS param_count,
795 FROM config.index_normalizer n
796 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
797 WHERE field = NEW.field AND m.pos >= 0
799 EXECUTE 'SELECT ' || normalizer.func || '(' ||
800 quote_literal( value ) ||
802 WHEN normalizer.param_count > 0
803 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
811 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
812 value := ARRAY_TO_STRING(
813 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
815 value := public.search_normalize(value);
818 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
824 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
825 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
826 SELECT DISTINCT l.version
827 FROM config.upgrade_log l
828 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
829 WHERE d.db_patch = $1
832 -- List applied db patches that are superseded by (and block the application of) my_db_patch
833 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( 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.supersedes)
837 WHERE d.db_patch = $1
840 -- List applied db patches that deprecates (and block the application of) my_db_patch
841 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
843 FROM config.db_patch_dependencies
844 WHERE ARRAY[$1]::TEXT[] && deprecates
847 -- List applied db patches that supersedes (and block the application of) my_db_patch
848 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
850 FROM config.db_patch_dependencies
851 WHERE ARRAY[$1]::TEXT[] && supersedes
854 -- Make sure that no deprecated or superseded db patches are currently applied
855 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
857 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
859 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
861 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
863 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
866 -- Raise an exception if there are, in fact, dep/sup conflict
867 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
872 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
873 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
874 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
876 Upgrade script % can not be applied:
877 applied deprecated scripts %
878 applied superseded scripts %
882 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
883 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
884 evergreen.upgrade_list_applied_deprecated(my_db_patch),
885 evergreen.upgrade_list_applied_superseded(my_db_patch);
888 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
893 CREATE TABLE config.barcode_completion (
894 id SERIAL PRIMARY KEY,
895 active BOOL NOT NULL DEFAULT true,
896 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
899 length INT NOT NULL DEFAULT 0,
901 padding_end BOOL NOT NULL DEFAULT false,
902 asset BOOL NOT NULL DEFAULT true,
903 actor BOOL NOT NULL DEFAULT true
906 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
908 -- Add support for logging, only keep the most recent five rows for each category.
911 CREATE TABLE config.org_unit_setting_type_log (
912 id BIGSERIAL PRIMARY KEY,
913 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
914 org INT, --REFERENCES actor.org_unit (id),
917 field_name TEXT REFERENCES config.org_unit_setting_type (name)
920 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
921 Org Unit setting Logs
923 This table contains the most recent changes to each setting
924 in actor.org_unit_setting, allowing for mistakes to be undone.
925 This is NOT meant to be an auditor, but rather an undo/redo.
928 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
930 -- Only keeps the most recent five settings changes.
931 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
932 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
934 IF (TG_OP = 'UPDATE') THEN
936 ELSIF (TG_OP = 'INSERT') THEN
941 $oustl_limit$ LANGUAGE plpgsql;
943 CREATE TRIGGER limit_logs_oust
944 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
945 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
947 CREATE TABLE config.sms_carrier (
948 id SERIAL PRIMARY KEY,
952 active BOOLEAN DEFAULT TRUE
955 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
957 CREATE TABLE config.usr_activity_type (
958 id SERIAL PRIMARY KEY,
962 label TEXT NOT NULL, -- i18n
963 egroup config.usr_activity_group NOT NULL,
964 enabled BOOL NOT NULL DEFAULT TRUE,
965 transient BOOL NOT NULL DEFAULT FALSE,
966 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
969 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
970 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));