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 ('0528', :eg_version); -- dbs
91 CREATE TABLE config.bib_source (
92 id SERIAL PRIMARY KEY,
93 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
94 source TEXT NOT NULL UNIQUE,
95 transcendant BOOL NOT NULL DEFAULT FALSE
97 COMMENT ON TABLE config.bib_source IS $$
98 This is table is used to set up the relative "quality" of each
99 MARC source, such as OCLC. Also identifies "transcendant" sources,
100 i.e., sources of bib records that should display in the OPAC
101 even if no copies or located URIs are attached.
104 CREATE TABLE config.standing (
105 id SERIAL PRIMARY KEY,
106 value TEXT NOT NULL UNIQUE
108 COMMENT ON TABLE config.standing IS $$
111 This table contains the values that can be applied to a patron
112 by a staff member. These values should not be changed, other
113 than for translation, as the ID column is currently a "magic
114 number" in the source. :(
117 CREATE TABLE config.standing_penalty (
118 id SERIAL PRIMARY KEY,
119 name TEXT NOT NULL UNIQUE,
124 INSERT INTO config.standing_penalty (id,name,label,block_list)
125 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
126 INSERT INTO config.standing_penalty (id,name,label,block_list)
127 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
128 INSERT INTO config.standing_penalty (id,name,label,block_list)
129 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
130 INSERT INTO config.standing_penalty (id,name,label,block_list)
131 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
133 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
134 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
135 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
136 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
137 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
138 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
139 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
140 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
141 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
142 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
143 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
145 SELECT SETVAL('config.standing_penalty_id_seq', 100);
147 CREATE TABLE config.xml_transform (
148 name TEXT PRIMARY KEY,
149 namespace_uri TEXT NOT NULL,
150 prefix TEXT NOT NULL,
154 CREATE TABLE config.biblio_fingerprint (
155 id SERIAL PRIMARY KEY,
158 first_word BOOL NOT NULL DEFAULT FALSE,
159 format TEXT NOT NULL DEFAULT 'marcxml'
162 INSERT INTO config.biblio_fingerprint (name, xpath, format)
165 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
166 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
167 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
168 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
169 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
173 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
176 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
177 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
178 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
179 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
180 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
185 CREATE TABLE config.metabib_class (
186 name TEXT PRIMARY KEY,
187 label TEXT NOT NULL UNIQUE
190 CREATE TABLE config.metabib_field (
191 id SERIAL PRIMARY KEY,
192 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
196 weight INT NOT NULL DEFAULT 1,
197 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
198 search_field BOOL NOT NULL DEFAULT TRUE,
199 facet_field BOOL NOT NULL DEFAULT FALSE,
202 COMMENT ON TABLE config.metabib_field IS $$
203 XPath used for record indexing ingest
205 This table contains the XPath used to chop up MODS into its
206 indexable parts. Each XPath entry is named and assigned to
207 a "class" of either title, subject, author, keyword, series
211 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
213 CREATE TABLE config.metabib_search_alias (
214 alias TEXT PRIMARY KEY,
215 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
216 field INT REFERENCES config.metabib_field (id)
219 CREATE TABLE config.non_cataloged_type (
220 id SERIAL PRIMARY KEY,
221 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
223 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
224 in_house BOOL NOT NULL DEFAULT FALSE,
225 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
227 COMMENT ON TABLE config.non_cataloged_type IS $$
228 Types of valid non-cataloged items.
231 CREATE TABLE config.identification_type (
232 id SERIAL PRIMARY KEY,
233 name TEXT NOT NULL UNIQUE
235 COMMENT ON TABLE config.identification_type IS $$
236 Types of valid patron identification.
238 Each patron must display at least one valid form of identification
239 in order to get a library card. This table lists those forms.
242 CREATE TABLE config.rule_circ_duration (
243 id SERIAL PRIMARY KEY,
244 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
245 extended INTERVAL NOT NULL,
246 normal INTERVAL NOT NULL,
247 shrt INTERVAL NOT NULL,
248 max_renewals INT NOT NULL
250 COMMENT ON TABLE config.rule_circ_duration IS $$
251 Circulation Duration rules
253 Each circulation is given a duration based on one of these rules.
256 CREATE TABLE config.hard_due_date (
257 id SERIAL PRIMARY KEY,
258 name TEXT NOT NULL UNIQUE,
259 ceiling_date TIMESTAMPTZ NOT NULL,
260 forceto BOOL NOT NULL,
261 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
264 CREATE TABLE config.hard_due_date_values (
265 id SERIAL PRIMARY KEY,
266 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
267 DEFERRABLE INITIALLY DEFERRED,
268 ceiling_date TIMESTAMPTZ NOT NULL,
269 active_date TIMESTAMPTZ NOT NULL
272 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
274 temp_value config.hard_due_date_values%ROWTYPE;
278 SELECT DISTINCT ON (hard_due_date) *
279 FROM config.hard_due_date_values
280 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
281 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
283 UPDATE config.hard_due_date
284 SET ceiling_date = temp_value.ceiling_date
285 WHERE id = temp_value.hard_due_date
286 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
289 updated := updated + 1;
295 $func$ LANGUAGE plpgsql;
297 CREATE TABLE config.rule_max_fine (
298 id SERIAL PRIMARY KEY,
299 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
300 amount NUMERIC(6,2) NOT NULL,
301 is_percent BOOL NOT NULL DEFAULT FALSE
303 COMMENT ON TABLE config.rule_max_fine IS $$
304 Circulation Max Fine rules
306 Each circulation is given a maximum fine based on one of
310 CREATE TABLE config.rule_recurring_fine (
311 id SERIAL PRIMARY KEY,
312 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
313 high NUMERIC(6,2) NOT NULL,
314 normal NUMERIC(6,2) NOT NULL,
315 low NUMERIC(6,2) NOT NULL,
316 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
317 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
319 COMMENT ON TABLE config.rule_recurring_fine IS $$
320 Circulation Recurring Fine rules
322 Each circulation is given a recurring fine amount based on one of
323 these rules. Note that it is recommended to run the fine generator
324 (from cron) at least as frequently as the lowest recurrence interval
325 used by your circulation rules so that accrued fines will be up
330 CREATE TABLE config.rule_age_hold_protect (
331 id SERIAL PRIMARY KEY,
332 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
333 age INTERVAL NOT NULL,
336 COMMENT ON TABLE config.rule_age_hold_protect IS $$
337 Hold Item Age Protection rules
339 A hold request can only capture new(ish) items when they are
340 within a particular proximity of the pickup_lib of the request.
341 The proximity ('prox' column) is calculated by counting
342 the number of tree edges between the pickup_lib and either the
343 owning_lib or circ_lib of the copy that could fulfill the hold,
344 as determined by the distance_is_from_owner value of the hold matrix
345 rule controlling the hold request.
348 CREATE TABLE config.copy_status (
349 id SERIAL PRIMARY KEY,
350 name TEXT NOT NULL UNIQUE,
351 holdable BOOL NOT NULL DEFAULT FALSE,
352 opac_visible BOOL NOT NULL DEFAULT FALSE
354 COMMENT ON TABLE config.copy_status IS $$
357 The available copy statuses, and whether a copy in that
358 status is available for hold request capture. 0 (zero) is
359 the only special number in this set, meaning that the item
360 is available for immediate checkout, and is counted as available
363 Statuses with an ID below 100 are not removable, and have special
364 meaning in the code. Do not change them except to translate the
367 You may add and remove statuses above 100, and these can be used
368 to remove items from normal circulation without affecting the rest
369 of the copy's values or its location.
372 CREATE TABLE config.net_access_level (
373 id SERIAL PRIMARY KEY,
374 name TEXT NOT NULL UNIQUE
376 COMMENT ON TABLE config.net_access_level IS $$
377 Patron Network Access level
379 This will be used to inform the in-library firewall of how much
380 internet access the using patron should be allowed.
384 CREATE TABLE config.remote_account (
385 id SERIAL PRIMARY KEY,
387 host TEXT NOT NULL, -- name or IP, :port optional
388 username TEXT, -- optional, since we could default to $USER
389 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
390 account TEXT, -- aka profile or FTP "account" command
391 path TEXT, -- aka directory
392 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
393 last_activity TIMESTAMP WITH TIME ZONE
396 CREATE TABLE config.marc21_rec_type_map (
397 code TEXT PRIMARY KEY,
398 type_val TEXT NOT NULL,
399 blvl_val TEXT NOT NULL
402 CREATE TABLE config.marc21_ff_pos_map (
403 id SERIAL PRIMARY KEY,
404 fixed_field TEXT NOT NULL,
406 rec_type TEXT NOT NULL,
407 start_pos INT NOT NULL,
409 default_val TEXT NOT NULL DEFAULT ' '
412 CREATE TABLE config.marc21_physical_characteristic_type_map (
413 ptype_key TEXT PRIMARY KEY,
414 label TEXT NOT NULL -- I18N
417 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
418 id SERIAL PRIMARY KEY,
419 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
420 subfield TEXT NOT NULL,
421 start_pos INT NOT NULL,
423 label TEXT NOT NULL -- I18N
426 CREATE TABLE config.marc21_physical_characteristic_value_map (
427 id SERIAL PRIMARY KEY,
429 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
430 label TEXT NOT NULL -- I18N
434 CREATE TABLE config.z3950_source (
435 name TEXT PRIMARY KEY,
436 label TEXT NOT NULL UNIQUE,
440 record_format TEXT NOT NULL DEFAULT 'FI',
441 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
442 auth BOOL NOT NULL DEFAULT TRUE
445 COMMENT ON TABLE config.z3950_source IS $$
448 Each row in this table represents a database searchable via Z39.50.
451 COMMENT ON COLUMN config.z3950_source.record_format IS $$
455 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
456 Z39.50 preferred record syntax..
460 CREATE TABLE config.z3950_attr (
461 id SERIAL PRIMARY KEY,
462 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
467 truncation INT NOT NULL DEFAULT 0,
468 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
471 CREATE TABLE config.i18n_locale (
472 code TEXT PRIMARY KEY,
473 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
474 name TEXT UNIQUE NOT NULL,
478 CREATE TABLE config.i18n_core (
479 id BIGSERIAL PRIMARY KEY,
480 fq_field TEXT NOT NULL,
481 identity_value TEXT NOT NULL,
482 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
486 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
488 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
492 UPDATE config.i18n_core
493 SET identity_value = $$ || quote_literal(new_ident) || $$
494 WHERE fq_field LIKE '$$ || hint || $$.%'
495 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
500 $_$ LANGUAGE PLPGSQL;
502 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
504 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
507 $_$ LANGUAGE PLPGSQL;
509 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
511 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
514 $_$ LANGUAGE PLPGSQL;
516 CREATE TABLE config.billing_type (
517 id SERIAL PRIMARY KEY,
519 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
520 default_price NUMERIC(6,2),
521 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
524 CREATE TABLE config.settings_group (
525 name TEXT PRIMARY KEY,
526 label TEXT UNIQUE NOT NULL -- I18N
529 CREATE TABLE config.org_unit_setting_type (
530 name TEXT PRIMARY KEY,
531 label TEXT UNIQUE NOT NULL,
532 grp TEXT REFERENCES config.settings_group (name),
534 datatype TEXT NOT NULL DEFAULT 'string',
539 -- define valid datatypes
541 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
542 ( 'bool', 'integer', 'float', 'currency', 'interval',
543 'date', 'string', 'object', 'array', 'link' ) ),
545 -- fm_class is meaningful only for 'link' datatype
547 CONSTRAINT coust_no_empty_link CHECK
548 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
549 ( datatype <> 'link' AND fm_class IS NULL ) )
552 CREATE TABLE config.usr_setting_type (
554 name TEXT PRIMARY KEY,
555 opac_visible BOOL NOT NULL DEFAULT FALSE,
556 label TEXT UNIQUE NOT NULL,
558 grp TEXT REFERENCES config.settings_group (name),
559 datatype TEXT NOT NULL DEFAULT 'string',
563 -- define valid datatypes
565 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
566 ( 'bool', 'integer', 'float', 'currency', 'interval',
567 'date', 'string', 'object', 'array', 'link' ) ),
570 -- fm_class is meaningful only for 'link' datatype
572 CONSTRAINT coust_no_empty_link CHECK
573 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
574 ( datatype <> 'link' AND fm_class IS NULL ) )
578 -- Some handy functions, based on existing ones, to provide optional ingest normalization
580 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
581 SELECT SUBSTRING($1,$2);
582 $func$ LANGUAGE SQL STRICT IMMUTABLE;
584 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
585 SELECT SUBSTRING($1,1,$2);
586 $func$ LANGUAGE SQL STRICT IMMUTABLE;
588 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
589 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
590 $func$ LANGUAGE SQL STRICT IMMUTABLE;
592 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
593 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
594 $func$ LANGUAGE SQL STRICT IMMUTABLE;
596 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
597 SELECT approximate_date( $1, '0');
598 $func$ LANGUAGE SQL STRICT IMMUTABLE;
600 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
601 SELECT approximate_date( $1, '9');
602 $func$ LANGUAGE SQL STRICT IMMUTABLE;
604 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
605 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
606 $func$ LANGUAGE SQL STRICT IMMUTABLE;
608 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
609 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
610 $func$ LANGUAGE SQL STRICT IMMUTABLE;
612 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
617 # Find the first ISBN, force it to ISBN13 and return it
621 foreach my $word (split(/\s/, $input)) {
622 my $isbn = Business::ISBN->new($word);
624 # First check the checksum; if it is not valid, fix it and add the original
625 # bad-checksum ISBN to the output
626 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
627 $isbn->fix_checksum();
630 # If we now have a valid ISBN, force it to ISBN13 and return it
631 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
634 $func$ LANGUAGE PLPERLU;
636 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
637 Inspired by translate_isbn1013
639 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
640 version without hypens and with a repaired checksum if the checksum was bad
644 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
649 # For each ISBN found in a single string containing a set of ISBNs:
650 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
651 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
656 foreach my $word (split(/\s/, $input)) {
657 my $isbn = Business::ISBN->new($word);
659 # First check the checksum; if it is not valid, fix it and add the original
660 # bad-checksum ISBN to the output
661 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
662 $output .= $isbn->isbn() . " ";
663 $isbn->fix_checksum();
666 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
667 # and add the normalized original ISBN to the output
668 if ($isbn && $isbn->is_valid()) {
669 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
670 $output .= $isbn->isbn . " ";
672 # If we successfully converted the ISBN to its counterpart, add the
673 # converted ISBN to the output as well
674 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
677 return $output if $output;
679 # If there were no valid ISBNs, just return the raw input
681 $func$ LANGUAGE PLPERLU;
683 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
684 The translate_isbn1013 function takes an input ISBN and returns the
685 following in a single space-delimited string if the input ISBN is valid:
686 - The normalized input ISBN (hyphens stripped)
687 - The normalized input ISBN with a fixed checksum if the checksum was bad
688 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
691 -- And ... a table in which to register them
693 CREATE TABLE config.index_normalizer (
694 id SERIAL PRIMARY KEY,
695 name TEXT UNIQUE NOT NULL,
698 param_count INT NOT NULL DEFAULT 0
701 CREATE TABLE config.metabib_field_index_norm_map (
702 id SERIAL PRIMARY KEY,
703 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
704 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
706 pos INT NOT NULL DEFAULT 0
709 CREATE TABLE config.record_attr_definition (
710 name TEXT PRIMARY KEY,
711 label TEXT NOT NULL, -- I18N
713 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
714 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
716 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
717 tag TEXT, -- LIKE format
718 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
720 -- This is used for both tag/sf and xpath entries
723 -- For xpath-extracted attrs
725 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
730 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
732 -- For phys-char fields
733 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
736 CREATE TABLE config.record_attr_index_norm_map (
737 id SERIAL PRIMARY KEY,
738 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
739 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
741 pos INT NOT NULL DEFAULT 0
744 CREATE TABLE config.coded_value_map (
745 id SERIAL PRIMARY KEY,
746 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
752 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
753 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
754 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
755 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
756 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
757 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
758 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
760 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
768 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
770 SELECT n.func AS func,
771 n.param_count AS param_count,
773 FROM config.index_normalizer n
774 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
775 WHERE field = NEW.field AND m.pos < 0
777 EXECUTE 'SELECT ' || normalizer.func || '(' ||
778 quote_literal( value ) ||
780 WHEN normalizer.param_count > 0
781 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
791 IF NEW.index_vector = ''::tsvector THEN
795 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
797 SELECT n.func AS func,
798 n.param_count AS param_count,
800 FROM config.index_normalizer n
801 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
802 WHERE field = NEW.field AND m.pos >= 0
804 EXECUTE 'SELECT ' || normalizer.func || '(' ||
805 quote_literal( value ) ||
807 WHEN normalizer.param_count > 0
808 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
816 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
817 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
819 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
826 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
827 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
828 SELECT DISTINCT l.version
829 FROM config.upgrade_log l
830 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
831 WHERE d.db_patch = $1
834 -- List applied db patches that are superseded by (and block the application of) my_db_patch
835 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
836 SELECT DISTINCT l.version
837 FROM config.upgrade_log l
838 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
839 WHERE d.db_patch = $1
842 -- List applied db patches that deprecates (and block the application of) my_db_patch
843 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS TEXT AS $$
845 FROM config.db_patch_dependencies
846 WHERE ARRAY[$1]::TEXT[] && deprecates
849 -- List applied db patches that supersedes (and block the application of) my_db_patch
850 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
852 FROM config.db_patch_dependencies
853 WHERE ARRAY[$1]::TEXT[] && supersedes
856 -- Make sure that no deprecated or superseded db patches are currently applied
857 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
859 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
861 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
863 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
865 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
868 -- Raise an exception if there are, in fact, dep/sup confilct
869 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
871 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
873 Upgrade script % can not be applied:
874 applied deprecated scripts %
875 applied superseded scripts %
879 ARRAY_ACUM(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
880 ARRAY_ACUM(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
881 evergreen.upgrade_list_applied_deprecated(my_db_patch),
882 evergreen.upgrade_list_applied_superseded(my_db_patch);
885 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);