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 ('0678', :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,
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,
170 bouyant BOOL DEFAULT FALSE NOT NULL,
171 restrict BOOL DEFAULT FALSE NOT NULL
174 CREATE TABLE config.metabib_field (
175 id SERIAL PRIMARY KEY,
176 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
180 weight INT NOT NULL DEFAULT 1,
181 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
182 search_field BOOL NOT NULL DEFAULT TRUE,
183 facet_field BOOL NOT NULL DEFAULT FALSE,
184 browse_field BOOL NOT NULL DEFAULT TRUE,
187 restrict BOOL DEFAULT FALSE NOT NULL
189 COMMENT ON TABLE config.metabib_field IS $$
190 XPath used for record indexing ingest
192 This table contains the XPath used to chop up MODS into its
193 indexable parts. Each XPath entry is named and assigned to
194 a "class" of either title, subject, author, keyword, series
198 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
200 CREATE TABLE config.metabib_search_alias (
201 alias TEXT PRIMARY KEY,
202 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
203 field INT REFERENCES config.metabib_field (id)
206 CREATE TABLE config.non_cataloged_type (
207 id SERIAL PRIMARY KEY,
208 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
210 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
211 in_house BOOL NOT NULL DEFAULT FALSE,
212 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
214 COMMENT ON TABLE config.non_cataloged_type IS $$
215 Types of valid non-cataloged items.
218 CREATE TABLE config.identification_type (
219 id SERIAL PRIMARY KEY,
220 name TEXT NOT NULL UNIQUE
222 COMMENT ON TABLE config.identification_type IS $$
223 Types of valid patron identification.
225 Each patron must display at least one valid form of identification
226 in order to get a library card. This table lists those forms.
229 CREATE TABLE config.rule_circ_duration (
230 id SERIAL PRIMARY KEY,
231 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
232 extended INTERVAL NOT NULL,
233 normal INTERVAL NOT NULL,
234 shrt INTERVAL NOT NULL,
235 max_renewals INT NOT NULL
237 COMMENT ON TABLE config.rule_circ_duration IS $$
238 Circulation Duration rules
240 Each circulation is given a duration based on one of these rules.
243 CREATE TABLE config.hard_due_date (
244 id SERIAL PRIMARY KEY,
245 name TEXT NOT NULL UNIQUE,
246 ceiling_date TIMESTAMPTZ NOT NULL,
247 forceto BOOL NOT NULL,
248 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
251 CREATE TABLE config.hard_due_date_values (
252 id SERIAL PRIMARY KEY,
253 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
254 DEFERRABLE INITIALLY DEFERRED,
255 ceiling_date TIMESTAMPTZ NOT NULL,
256 active_date TIMESTAMPTZ NOT NULL
259 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
261 temp_value config.hard_due_date_values%ROWTYPE;
265 SELECT DISTINCT ON (hard_due_date) *
266 FROM config.hard_due_date_values
267 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
268 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
270 UPDATE config.hard_due_date
271 SET ceiling_date = temp_value.ceiling_date
272 WHERE id = temp_value.hard_due_date
273 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
276 updated := updated + 1;
282 $func$ LANGUAGE plpgsql;
284 CREATE TABLE config.rule_max_fine (
285 id SERIAL PRIMARY KEY,
286 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
287 amount NUMERIC(6,2) NOT NULL,
288 is_percent BOOL NOT NULL DEFAULT FALSE
290 COMMENT ON TABLE config.rule_max_fine IS $$
291 Circulation Max Fine rules
293 Each circulation is given a maximum fine based on one of
297 CREATE TABLE config.rule_recurring_fine (
298 id SERIAL PRIMARY KEY,
299 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
300 high NUMERIC(6,2) NOT NULL,
301 normal NUMERIC(6,2) NOT NULL,
302 low NUMERIC(6,2) NOT NULL,
303 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
304 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
306 COMMENT ON TABLE config.rule_recurring_fine IS $$
307 Circulation Recurring Fine rules
309 Each circulation is given a recurring fine amount based on one of
310 these rules. Note that it is recommended to run the fine generator
311 (from cron) at least as frequently as the lowest recurrence interval
312 used by your circulation rules so that accrued fines will be up
317 CREATE TABLE config.rule_age_hold_protect (
318 id SERIAL PRIMARY KEY,
319 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
320 age INTERVAL NOT NULL,
323 COMMENT ON TABLE config.rule_age_hold_protect IS $$
324 Hold Item Age Protection rules
326 A hold request can only capture new(ish) items when they are
327 within a particular proximity of the pickup_lib of the request.
328 The proximity ('prox' column) is calculated by counting
329 the number of tree edges between the pickup_lib and either the
330 owning_lib or circ_lib of the copy that could fulfill the hold,
331 as determined by the distance_is_from_owner value of the hold matrix
332 rule controlling the hold request.
335 CREATE TABLE config.copy_status (
336 id SERIAL PRIMARY KEY,
337 name TEXT NOT NULL UNIQUE,
338 holdable BOOL NOT NULL DEFAULT FALSE,
339 opac_visible BOOL NOT NULL DEFAULT FALSE,
340 copy_active BOOL NOT NULL DEFAULT FALSE,
341 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE
343 COMMENT ON TABLE config.copy_status IS $$
346 The available copy statuses, and whether a copy in that
347 status is available for hold request capture. 0 (zero) is
348 the only special number in this set, meaning that the item
349 is available for immediate checkout, and is counted as available
352 Statuses with an ID below 100 are not removable, and have special
353 meaning in the code. Do not change them except to translate the
356 You may add and remove statuses above 100, and these can be used
357 to remove items from normal circulation without affecting the rest
358 of the copy's values or its location.
361 CREATE TABLE config.net_access_level (
362 id SERIAL PRIMARY KEY,
363 name TEXT NOT NULL UNIQUE
365 COMMENT ON TABLE config.net_access_level IS $$
366 Patron Network Access level
368 This will be used to inform the in-library firewall of how much
369 internet access the using patron should be allowed.
373 CREATE TABLE config.remote_account (
374 id SERIAL PRIMARY KEY,
376 host TEXT NOT NULL, -- name or IP, :port optional
377 username TEXT, -- optional, since we could default to $USER
378 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
379 account TEXT, -- aka profile or FTP "account" command
380 path TEXT, -- aka directory
381 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
382 last_activity TIMESTAMP WITH TIME ZONE
385 CREATE TABLE config.marc21_rec_type_map (
386 code TEXT PRIMARY KEY,
387 type_val TEXT NOT NULL,
388 blvl_val TEXT NOT NULL
391 CREATE TABLE config.marc21_ff_pos_map (
392 id SERIAL PRIMARY KEY,
393 fixed_field TEXT NOT NULL,
395 rec_type TEXT NOT NULL,
396 start_pos INT NOT NULL,
398 default_val TEXT NOT NULL DEFAULT ' '
401 CREATE TABLE config.marc21_physical_characteristic_type_map (
402 ptype_key TEXT PRIMARY KEY,
403 label TEXT NOT NULL -- I18N
406 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
407 id SERIAL PRIMARY KEY,
408 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
409 subfield TEXT NOT NULL,
410 start_pos INT NOT NULL,
412 label TEXT NOT NULL -- I18N
415 CREATE TABLE config.marc21_physical_characteristic_value_map (
416 id SERIAL PRIMARY KEY,
418 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
419 label TEXT NOT NULL -- I18N
423 CREATE TABLE config.z3950_source (
424 name TEXT PRIMARY KEY,
425 label TEXT NOT NULL UNIQUE,
429 record_format TEXT NOT NULL DEFAULT 'FI',
430 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
431 auth BOOL NOT NULL DEFAULT TRUE,
432 use_perm INT -- REFERENCES permission.perm_list (id)
435 COMMENT ON TABLE config.z3950_source IS $$
438 Each row in this table represents a database searchable via Z39.50.
441 COMMENT ON COLUMN config.z3950_source.record_format IS $$
445 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
446 Z39.50 preferred record syntax..
449 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
450 If set, this permission is required for the source to be listed in the staff
451 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
454 CREATE TABLE config.z3950_attr (
455 id SERIAL PRIMARY KEY,
456 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
461 truncation INT NOT NULL DEFAULT 0,
462 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
465 CREATE TABLE config.i18n_locale (
466 code TEXT PRIMARY KEY,
467 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
468 name TEXT UNIQUE NOT NULL,
472 CREATE TABLE config.i18n_core (
473 id BIGSERIAL PRIMARY KEY,
474 fq_field TEXT NOT NULL,
475 identity_value TEXT NOT NULL,
476 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
480 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
482 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
486 UPDATE config.i18n_core
487 SET identity_value = $$ || quote_literal(new_ident) || $$
488 WHERE fq_field LIKE '$$ || hint || $$.%'
489 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
494 $_$ LANGUAGE PLPGSQL;
496 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
498 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
501 $_$ LANGUAGE PLPGSQL;
503 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
505 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
508 $_$ LANGUAGE PLPGSQL;
510 CREATE TABLE config.billing_type (
511 id SERIAL PRIMARY KEY,
513 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
514 default_price NUMERIC(6,2),
515 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
518 CREATE TABLE config.settings_group (
519 name TEXT PRIMARY KEY,
520 label TEXT UNIQUE NOT NULL -- I18N
523 CREATE TABLE config.org_unit_setting_type (
524 name TEXT PRIMARY KEY,
525 label TEXT UNIQUE NOT NULL,
526 grp TEXT REFERENCES config.settings_group (name),
528 datatype TEXT NOT NULL DEFAULT 'string',
533 -- define valid datatypes
535 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
536 ( 'bool', 'integer', 'float', 'currency', 'interval',
537 'date', 'string', 'object', 'array', 'link' ) ),
539 -- fm_class is meaningful only for 'link' datatype
541 CONSTRAINT coust_no_empty_link CHECK
542 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
543 ( datatype <> 'link' AND fm_class IS NULL ) )
546 CREATE TABLE config.usr_setting_type (
548 name TEXT PRIMARY KEY,
549 opac_visible BOOL NOT NULL DEFAULT FALSE,
550 label TEXT UNIQUE NOT NULL,
552 grp TEXT REFERENCES config.settings_group (name),
553 datatype TEXT NOT NULL DEFAULT 'string',
557 -- define valid datatypes
559 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
560 ( 'bool', 'integer', 'float', 'currency', 'interval',
561 'date', 'string', 'object', 'array', 'link' ) ),
564 -- fm_class is meaningful only for 'link' datatype
566 CONSTRAINT coust_no_empty_link CHECK
567 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
568 ( datatype <> 'link' AND fm_class IS NULL ) )
572 -- Some handy functions, based on existing ones, to provide optional ingest normalization
574 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
575 SELECT SUBSTRING($1,$2);
576 $func$ LANGUAGE SQL STRICT IMMUTABLE;
578 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
579 SELECT SUBSTRING($1,1,$2);
580 $func$ LANGUAGE SQL STRICT IMMUTABLE;
582 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
583 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
584 $func$ LANGUAGE SQL STRICT IMMUTABLE;
586 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
587 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
588 $func$ LANGUAGE SQL STRICT IMMUTABLE;
590 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
591 SELECT approximate_date( $1, '0');
592 $func$ LANGUAGE SQL STRICT IMMUTABLE;
594 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
595 SELECT approximate_date( $1, '9');
596 $func$ LANGUAGE SQL STRICT IMMUTABLE;
598 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
599 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
600 $func$ LANGUAGE SQL STRICT IMMUTABLE;
602 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
603 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
604 $func$ LANGUAGE SQL STRICT IMMUTABLE;
606 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
611 # Find the first ISBN, force it to ISBN13 and return it
615 foreach my $word (split(/\s/, $input)) {
616 my $isbn = Business::ISBN->new($word);
618 # First check the checksum; if it is not valid, fix it and add the original
619 # bad-checksum ISBN to the output
620 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
621 $isbn->fix_checksum();
624 # If we now have a valid ISBN, force it to ISBN13 and return it
625 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
628 $func$ LANGUAGE PLPERLU;
630 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
631 Inspired by translate_isbn1013
633 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
634 version without hypens and with a repaired checksum if the checksum was bad
638 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
643 # For each ISBN found in a single string containing a set of ISBNs:
644 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
645 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
650 foreach my $word (split(/\s/, $input)) {
651 my $isbn = Business::ISBN->new($word);
653 # First check the checksum; if it is not valid, fix it and add the original
654 # bad-checksum ISBN to the output
655 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
656 $output .= $isbn->isbn() . " ";
657 $isbn->fix_checksum();
660 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
661 # and add the normalized original ISBN to the output
662 if ($isbn && $isbn->is_valid()) {
663 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
664 $output .= $isbn->isbn . " ";
666 # If we successfully converted the ISBN to its counterpart, add the
667 # converted ISBN to the output as well
668 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
671 return $output if $output;
673 # If there were no valid ISBNs, just return the raw input
675 $func$ LANGUAGE PLPERLU;
677 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
678 The translate_isbn1013 function takes an input ISBN and returns the
679 following in a single space-delimited string if the input ISBN is valid:
680 - The normalized input ISBN (hyphens stripped)
681 - The normalized input ISBN with a fixed checksum if the checksum was bad
682 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
685 -- And ... a table in which to register them
687 CREATE TABLE config.index_normalizer (
688 id SERIAL PRIMARY KEY,
689 name TEXT UNIQUE NOT NULL,
692 param_count INT NOT NULL DEFAULT 0
695 CREATE TABLE config.metabib_field_index_norm_map (
696 id SERIAL PRIMARY KEY,
697 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
698 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
700 pos INT NOT NULL DEFAULT 0
703 CREATE TABLE config.record_attr_definition (
704 name TEXT PRIMARY KEY,
705 label TEXT NOT NULL, -- I18N
707 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
708 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
710 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
711 tag TEXT, -- LIKE format
712 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
714 -- This is used for both tag/sf and xpath entries
717 -- For xpath-extracted attrs
719 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
724 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
726 -- For phys-char fields
727 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
730 CREATE TABLE config.record_attr_index_norm_map (
731 id SERIAL PRIMARY KEY,
732 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
733 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
735 pos INT NOT NULL DEFAULT 0
738 CREATE TABLE config.coded_value_map (
739 id SERIAL PRIMARY KEY,
740 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
746 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
747 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
748 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
749 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
750 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
751 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
752 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
754 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
762 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
764 SELECT n.func AS func,
765 n.param_count AS param_count,
767 FROM config.index_normalizer n
768 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
769 WHERE field = NEW.field AND m.pos < 0
771 EXECUTE 'SELECT ' || normalizer.func || '(' ||
772 quote_literal( value ) ||
774 WHEN normalizer.param_count > 0
775 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
785 IF NEW.index_vector = ''::tsvector THEN
789 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
791 SELECT n.func AS func,
792 n.param_count AS param_count,
794 FROM config.index_normalizer n
795 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
796 WHERE field = NEW.field AND m.pos >= 0
798 EXECUTE 'SELECT ' || normalizer.func || '(' ||
799 quote_literal( value ) ||
801 WHEN normalizer.param_count > 0
802 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
810 IF TG_TABLE_NAME::TEXT ~ 'browse_entry$' THEN
811 value := ARRAY_TO_STRING(
812 evergreen.regexp_split_to_array(value, E'\\W+'), ' '
816 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
822 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
823 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
824 SELECT DISTINCT l.version
825 FROM config.upgrade_log l
826 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
827 WHERE d.db_patch = $1
830 -- List applied db patches that are superseded by (and block the application of) my_db_patch
831 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
832 SELECT DISTINCT l.version
833 FROM config.upgrade_log l
834 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
835 WHERE d.db_patch = $1
838 -- List applied db patches that deprecates (and block the application of) my_db_patch
839 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
841 FROM config.db_patch_dependencies
842 WHERE ARRAY[$1]::TEXT[] && deprecates
845 -- List applied db patches that supersedes (and block the application of) my_db_patch
846 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
848 FROM config.db_patch_dependencies
849 WHERE ARRAY[$1]::TEXT[] && supersedes
852 -- Make sure that no deprecated or superseded db patches are currently applied
853 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
855 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
857 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
859 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
861 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
864 -- Raise an exception if there are, in fact, dep/sup conflict
865 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
870 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
871 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
872 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
874 Upgrade script % can not be applied:
875 applied deprecated scripts %
876 applied superseded scripts %
880 ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
881 ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
882 evergreen.upgrade_list_applied_deprecated(my_db_patch),
883 evergreen.upgrade_list_applied_superseded(my_db_patch);
886 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
891 CREATE TABLE config.barcode_completion (
892 id SERIAL PRIMARY KEY,
893 active BOOL NOT NULL DEFAULT true,
894 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
897 length INT NOT NULL DEFAULT 0,
899 padding_end BOOL NOT NULL DEFAULT false,
900 asset BOOL NOT NULL DEFAULT true,
901 actor BOOL NOT NULL DEFAULT true
904 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
906 -- Add support for logging, only keep the most recent five rows for each category.
909 CREATE TABLE config.org_unit_setting_type_log (
910 id BIGSERIAL PRIMARY KEY,
911 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
912 org INT, --REFERENCES actor.org_unit (id),
915 field_name TEXT REFERENCES config.org_unit_setting_type (name)
918 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
919 Org Unit setting Logs
921 This table contains the most recent changes to each setting
922 in actor.org_unit_setting, allowing for mistakes to be undone.
923 This is NOT meant to be an auditor, but rather an undo/redo.
926 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
928 -- Only keeps the most recent five settings changes.
929 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN
930 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
932 IF (TG_OP = 'UPDATE') THEN
934 ELSIF (TG_OP = 'INSERT') THEN
939 $oustl_limit$ LANGUAGE plpgsql;
941 CREATE TRIGGER limit_logs_oust
942 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
943 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
945 CREATE TABLE config.sms_carrier (
946 id SERIAL PRIMARY KEY,
950 active BOOLEAN DEFAULT TRUE