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,
65 CONSTRAINT supersede_once EXCLUDE USING GIST ( supersedes WITH && ),
66 CONSTRAINT deprecate_once EXCLUDE USING GIST ( deprecates WITH && )
69 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0526', :eg_version); -- miker
71 CREATE TABLE config.bib_source (
72 id SERIAL PRIMARY KEY,
73 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
74 source TEXT NOT NULL UNIQUE,
75 transcendant BOOL NOT NULL DEFAULT FALSE
77 COMMENT ON TABLE config.bib_source IS $$
78 This is table is used to set up the relative "quality" of each
79 MARC source, such as OCLC. Also identifies "transcendant" sources,
80 i.e., sources of bib records that should display in the OPAC
81 even if no copies or located URIs are attached.
84 CREATE TABLE config.standing (
85 id SERIAL PRIMARY KEY,
86 value TEXT NOT NULL UNIQUE
88 COMMENT ON TABLE config.standing IS $$
91 This table contains the values that can be applied to a patron
92 by a staff member. These values should not be changed, other
93 than for translation, as the ID column is currently a "magic
94 number" in the source. :(
97 CREATE TABLE config.standing_penalty (
98 id SERIAL PRIMARY KEY,
99 name TEXT NOT NULL UNIQUE,
104 INSERT INTO config.standing_penalty (id,name,label,block_list)
105 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
106 INSERT INTO config.standing_penalty (id,name,label,block_list)
107 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
108 INSERT INTO config.standing_penalty (id,name,label,block_list)
109 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
110 INSERT INTO config.standing_penalty (id,name,label,block_list)
111 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
113 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
114 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
115 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
116 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
117 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
118 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
119 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
120 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
121 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
122 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
123 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
125 SELECT SETVAL('config.standing_penalty_id_seq', 100);
127 CREATE TABLE config.xml_transform (
128 name TEXT PRIMARY KEY,
129 namespace_uri TEXT NOT NULL,
130 prefix TEXT NOT NULL,
134 CREATE TABLE config.biblio_fingerprint (
135 id SERIAL PRIMARY KEY,
138 first_word BOOL NOT NULL DEFAULT FALSE,
139 format TEXT NOT NULL DEFAULT 'marcxml'
142 INSERT INTO config.biblio_fingerprint (name, xpath, format)
145 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
146 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
147 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
148 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
149 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
153 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
156 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
157 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
158 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
159 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
160 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
165 CREATE TABLE config.metabib_class (
166 name TEXT PRIMARY KEY,
167 label TEXT NOT NULL UNIQUE
170 CREATE TABLE config.metabib_field (
171 id SERIAL PRIMARY KEY,
172 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
176 weight INT NOT NULL DEFAULT 1,
177 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
178 search_field BOOL NOT NULL DEFAULT TRUE,
179 facet_field BOOL NOT NULL DEFAULT FALSE,
182 COMMENT ON TABLE config.metabib_field IS $$
183 XPath used for record indexing ingest
185 This table contains the XPath used to chop up MODS into its
186 indexable parts. Each XPath entry is named and assigned to
187 a "class" of either title, subject, author, keyword, series
191 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
193 CREATE TABLE config.metabib_search_alias (
194 alias TEXT PRIMARY KEY,
195 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
196 field INT REFERENCES config.metabib_field (id)
199 CREATE TABLE config.non_cataloged_type (
200 id SERIAL PRIMARY KEY,
201 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
203 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
204 in_house BOOL NOT NULL DEFAULT FALSE,
205 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
207 COMMENT ON TABLE config.non_cataloged_type IS $$
208 Types of valid non-cataloged items.
211 CREATE TABLE config.identification_type (
212 id SERIAL PRIMARY KEY,
213 name TEXT NOT NULL UNIQUE
215 COMMENT ON TABLE config.identification_type IS $$
216 Types of valid patron identification.
218 Each patron must display at least one valid form of identification
219 in order to get a library card. This table lists those forms.
222 CREATE TABLE config.rule_circ_duration (
223 id SERIAL PRIMARY KEY,
224 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
225 extended INTERVAL NOT NULL,
226 normal INTERVAL NOT NULL,
227 shrt INTERVAL NOT NULL,
228 max_renewals INT NOT NULL
230 COMMENT ON TABLE config.rule_circ_duration IS $$
231 Circulation Duration rules
233 Each circulation is given a duration based on one of these rules.
236 CREATE TABLE config.hard_due_date (
237 id SERIAL PRIMARY KEY,
238 name TEXT NOT NULL UNIQUE,
239 ceiling_date TIMESTAMPTZ NOT NULL,
240 forceto BOOL NOT NULL,
241 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
244 CREATE TABLE config.hard_due_date_values (
245 id SERIAL PRIMARY KEY,
246 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
247 DEFERRABLE INITIALLY DEFERRED,
248 ceiling_date TIMESTAMPTZ NOT NULL,
249 active_date TIMESTAMPTZ NOT NULL
252 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
254 temp_value config.hard_due_date_values%ROWTYPE;
258 SELECT DISTINCT ON (hard_due_date) *
259 FROM config.hard_due_date_values
260 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
261 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
263 UPDATE config.hard_due_date
264 SET ceiling_date = temp_value.ceiling_date
265 WHERE id = temp_value.hard_due_date
266 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
269 updated := updated + 1;
275 $func$ LANGUAGE plpgsql;
277 CREATE TABLE config.rule_max_fine (
278 id SERIAL PRIMARY KEY,
279 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
280 amount NUMERIC(6,2) NOT NULL,
281 is_percent BOOL NOT NULL DEFAULT FALSE
283 COMMENT ON TABLE config.rule_max_fine IS $$
284 Circulation Max Fine rules
286 Each circulation is given a maximum fine based on one of
290 CREATE TABLE config.rule_recurring_fine (
291 id SERIAL PRIMARY KEY,
292 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
293 high NUMERIC(6,2) NOT NULL,
294 normal NUMERIC(6,2) NOT NULL,
295 low NUMERIC(6,2) NOT NULL,
296 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
297 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
299 COMMENT ON TABLE config.rule_recurring_fine IS $$
300 Circulation Recurring Fine rules
302 Each circulation is given a recurring fine amount based on one of
303 these rules. Note that it is recommended to run the fine generator
304 (from cron) at least as frequently as the lowest recurrence interval
305 used by your circulation rules so that accrued fines will be up
310 CREATE TABLE config.rule_age_hold_protect (
311 id SERIAL PRIMARY KEY,
312 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
313 age INTERVAL NOT NULL,
316 COMMENT ON TABLE config.rule_age_hold_protect IS $$
317 Hold Item Age Protection rules
319 A hold request can only capture new(ish) items when they are
320 within a particular proximity of the pickup_lib of the request.
321 The proximity ('prox' column) is calculated by counting
322 the number of tree edges between the pickup_lib and either the
323 owning_lib or circ_lib of the copy that could fulfill the hold,
324 as determined by the distance_is_from_owner value of the hold matrix
325 rule controlling the hold request.
328 CREATE TABLE config.copy_status (
329 id SERIAL PRIMARY KEY,
330 name TEXT NOT NULL UNIQUE,
331 holdable BOOL NOT NULL DEFAULT FALSE,
332 opac_visible BOOL NOT NULL DEFAULT FALSE
334 COMMENT ON TABLE config.copy_status IS $$
337 The available copy statuses, and whether a copy in that
338 status is available for hold request capture. 0 (zero) is
339 the only special number in this set, meaning that the item
340 is available for immediate checkout, and is counted as available
343 Statuses with an ID below 100 are not removable, and have special
344 meaning in the code. Do not change them except to translate the
347 You may add and remove statuses above 100, and these can be used
348 to remove items from normal circulation without affecting the rest
349 of the copy's values or its location.
352 CREATE TABLE config.net_access_level (
353 id SERIAL PRIMARY KEY,
354 name TEXT NOT NULL UNIQUE
356 COMMENT ON TABLE config.net_access_level IS $$
357 Patron Network Access level
359 This will be used to inform the in-library firewall of how much
360 internet access the using patron should be allowed.
364 CREATE TABLE config.remote_account (
365 id SERIAL PRIMARY KEY,
367 host TEXT NOT NULL, -- name or IP, :port optional
368 username TEXT, -- optional, since we could default to $USER
369 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
370 account TEXT, -- aka profile or FTP "account" command
371 path TEXT, -- aka directory
372 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
373 last_activity TIMESTAMP WITH TIME ZONE
376 CREATE TABLE config.marc21_rec_type_map (
377 code TEXT PRIMARY KEY,
378 type_val TEXT NOT NULL,
379 blvl_val TEXT NOT NULL
382 CREATE TABLE config.marc21_ff_pos_map (
383 id SERIAL PRIMARY KEY,
384 fixed_field TEXT NOT NULL,
386 rec_type TEXT NOT NULL,
387 start_pos INT NOT NULL,
389 default_val TEXT NOT NULL DEFAULT ' '
392 CREATE TABLE config.marc21_physical_characteristic_type_map (
393 ptype_key TEXT PRIMARY KEY,
394 label TEXT NOT NULL -- I18N
397 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
398 id SERIAL PRIMARY KEY,
399 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
400 subfield TEXT NOT NULL,
401 start_pos INT NOT NULL,
403 label TEXT NOT NULL -- I18N
406 CREATE TABLE config.marc21_physical_characteristic_value_map (
407 id SERIAL PRIMARY KEY,
409 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
410 label TEXT NOT NULL -- I18N
414 CREATE TABLE config.z3950_source (
415 name TEXT PRIMARY KEY,
416 label TEXT NOT NULL UNIQUE,
420 record_format TEXT NOT NULL DEFAULT 'FI',
421 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
422 auth BOOL NOT NULL DEFAULT TRUE
425 COMMENT ON TABLE config.z3950_source IS $$
428 Each row in this table represents a database searchable via Z39.50.
431 COMMENT ON COLUMN config.z3950_source.record_format IS $$
435 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
436 Z39.50 preferred record syntax..
440 CREATE TABLE config.z3950_attr (
441 id SERIAL PRIMARY KEY,
442 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
447 truncation INT NOT NULL DEFAULT 0,
448 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
451 CREATE TABLE config.i18n_locale (
452 code TEXT PRIMARY KEY,
453 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
454 name TEXT UNIQUE NOT NULL,
458 CREATE TABLE config.i18n_core (
459 id BIGSERIAL PRIMARY KEY,
460 fq_field TEXT NOT NULL,
461 identity_value TEXT NOT NULL,
462 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
466 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
468 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
472 UPDATE config.i18n_core
473 SET identity_value = $$ || quote_literal(new_ident) || $$
474 WHERE fq_field LIKE '$$ || hint || $$.%'
475 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
480 $_$ LANGUAGE PLPGSQL;
482 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
484 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
487 $_$ LANGUAGE PLPGSQL;
489 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
491 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
494 $_$ LANGUAGE PLPGSQL;
496 CREATE TABLE config.billing_type (
497 id SERIAL PRIMARY KEY,
499 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
500 default_price NUMERIC(6,2),
501 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
504 CREATE TABLE config.settings_group (
505 name TEXT PRIMARY KEY,
506 label TEXT UNIQUE NOT NULL -- I18N
509 CREATE TABLE config.org_unit_setting_type (
510 name TEXT PRIMARY KEY,
511 label TEXT UNIQUE NOT NULL,
512 grp TEXT REFERENCES config.settings_group (name),
514 datatype TEXT NOT NULL DEFAULT 'string',
519 -- define valid datatypes
521 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
522 ( 'bool', 'integer', 'float', 'currency', 'interval',
523 'date', 'string', 'object', 'array', 'link' ) ),
525 -- fm_class is meaningful only for 'link' datatype
527 CONSTRAINT coust_no_empty_link CHECK
528 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
529 ( datatype <> 'link' AND fm_class IS NULL ) )
532 CREATE TABLE config.usr_setting_type (
534 name TEXT PRIMARY KEY,
535 opac_visible BOOL NOT NULL DEFAULT FALSE,
536 label TEXT UNIQUE NOT NULL,
538 grp TEXT REFERENCES config.settings_group (name),
539 datatype TEXT NOT NULL DEFAULT 'string',
543 -- define valid datatypes
545 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
546 ( 'bool', 'integer', 'float', 'currency', 'interval',
547 'date', 'string', 'object', 'array', 'link' ) ),
550 -- fm_class is meaningful only for 'link' datatype
552 CONSTRAINT coust_no_empty_link CHECK
553 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
554 ( datatype <> 'link' AND fm_class IS NULL ) )
558 -- Some handy functions, based on existing ones, to provide optional ingest normalization
560 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
561 SELECT SUBSTRING($1,$2);
562 $func$ LANGUAGE SQL STRICT IMMUTABLE;
564 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
565 SELECT SUBSTRING($1,1,$2);
566 $func$ LANGUAGE SQL STRICT IMMUTABLE;
568 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
569 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
570 $func$ LANGUAGE SQL STRICT IMMUTABLE;
572 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
573 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
574 $func$ LANGUAGE SQL STRICT IMMUTABLE;
576 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
577 SELECT approximate_date( $1, '0');
578 $func$ LANGUAGE SQL STRICT IMMUTABLE;
580 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
581 SELECT approximate_date( $1, '9');
582 $func$ LANGUAGE SQL STRICT IMMUTABLE;
584 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
585 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
586 $func$ LANGUAGE SQL STRICT IMMUTABLE;
588 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
589 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
590 $func$ LANGUAGE SQL STRICT IMMUTABLE;
592 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
597 # Find the first ISBN, force it to ISBN13 and return it
601 foreach my $word (split(/\s/, $input)) {
602 my $isbn = Business::ISBN->new($word);
604 # First check the checksum; if it is not valid, fix it and add the original
605 # bad-checksum ISBN to the output
606 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
607 $isbn->fix_checksum();
610 # If we now have a valid ISBN, force it to ISBN13 and return it
611 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
614 $func$ LANGUAGE PLPERLU;
616 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
617 Inspired by translate_isbn1013
619 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
620 version without hypens and with a repaired checksum if the checksum was bad
624 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
629 # For each ISBN found in a single string containing a set of ISBNs:
630 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
631 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
636 foreach my $word (split(/\s/, $input)) {
637 my $isbn = Business::ISBN->new($word);
639 # First check the checksum; if it is not valid, fix it and add the original
640 # bad-checksum ISBN to the output
641 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
642 $output .= $isbn->isbn() . " ";
643 $isbn->fix_checksum();
646 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
647 # and add the normalized original ISBN to the output
648 if ($isbn && $isbn->is_valid()) {
649 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
650 $output .= $isbn->isbn . " ";
652 # If we successfully converted the ISBN to its counterpart, add the
653 # converted ISBN to the output as well
654 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
657 return $output if $output;
659 # If there were no valid ISBNs, just return the raw input
661 $func$ LANGUAGE PLPERLU;
663 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
664 The translate_isbn1013 function takes an input ISBN and returns the
665 following in a single space-delimited string if the input ISBN is valid:
666 - The normalized input ISBN (hyphens stripped)
667 - The normalized input ISBN with a fixed checksum if the checksum was bad
668 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
671 -- And ... a table in which to register them
673 CREATE TABLE config.index_normalizer (
674 id SERIAL PRIMARY KEY,
675 name TEXT UNIQUE NOT NULL,
678 param_count INT NOT NULL DEFAULT 0
681 CREATE TABLE config.metabib_field_index_norm_map (
682 id SERIAL PRIMARY KEY,
683 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
684 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
686 pos INT NOT NULL DEFAULT 0
689 CREATE TABLE config.record_attr_definition (
690 name TEXT PRIMARY KEY,
691 label TEXT NOT NULL, -- I18N
693 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
694 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
696 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
697 tag TEXT, -- LIKE format
698 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
700 -- This is used for both tag/sf and xpath entries
703 -- For xpath-extracted attrs
705 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
710 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
712 -- For phys-char fields
713 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
716 CREATE TABLE config.record_attr_index_norm_map (
717 id SERIAL PRIMARY KEY,
718 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
719 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
721 pos INT NOT NULL DEFAULT 0
724 CREATE TABLE config.coded_value_map (
725 id SERIAL PRIMARY KEY,
726 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
732 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
733 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
734 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
735 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
736 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
737 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
738 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
740 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
748 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
750 SELECT n.func AS func,
751 n.param_count AS param_count,
753 FROM config.index_normalizer n
754 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
755 WHERE field = NEW.field AND m.pos < 0
757 EXECUTE 'SELECT ' || normalizer.func || '(' ||
758 quote_literal( value ) ||
760 WHEN normalizer.param_count > 0
761 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
771 IF NEW.index_vector = ''::tsvector THEN
775 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
777 SELECT n.func AS func,
778 n.param_count AS param_count,
780 FROM config.index_normalizer n
781 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
782 WHERE field = NEW.field AND m.pos >= 0
784 EXECUTE 'SELECT ' || normalizer.func || '(' ||
785 quote_literal( value ) ||
787 WHEN normalizer.param_count > 0
788 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
796 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
797 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
799 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
806 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
807 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
808 SELECT DISTINCT l.version
809 FROM config.upgrade_log l
810 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
811 WHERE d.db_patch = my_db_patch
814 -- List applied db patches that are superseded by (and block the application of) my_db_patch
815 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
816 SELECT DISTINCT l.version
817 FROM config.upgrade_log l
818 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
819 WHERE d.db_patch = my_db_patch
822 -- List applied db patches that deprecates (and block the application of) my_db_patch
823 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
825 FROM config.db_patch_dependencies
826 WHERE my_db_patch::TEXT[] && deprecates
829 -- List applied db patches that supersedes (and block the application of) my_db_patch
830 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
832 FROM config.db_patch_dependencies
833 WHERE my_db_patch::TEXT[] && supersedes
836 -- Make sure that no deprecated or superseded db patches are currently applied
837 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
839 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( my_db_patch )
841 SELECT * FROM evergreen.upgrade_list_applied_supersedes( my_db_patch )
843 SELECT * FROM evergreen.upgrade_list_applied_deprecated( my_db_patch )
845 SELECT * FROM evergreen.upgrade_list_applied_superseded( my_db_patch ))x
848 -- Raise an exception if there are, in fact, dep/sup confilct
849 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
851 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
853 Upgrade script % can not be applied:
854 applied deprecated scripts %
855 applied superseded scripts %
859 ARRAY_ACUM(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
860 ARRAY_ACUM(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
861 evergreen.upgrade_list_applied_deprecated(my_db_patch),
862 evergreen.upgrade_list_applied_superseded(my_db_patch);
865 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);