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 EXCLUDE ( supersedes WITH &&, deprecates WITH && )
68 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0526', :eg_version); -- miker
70 CREATE TABLE config.bib_source (
71 id SERIAL PRIMARY KEY,
72 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
73 source TEXT NOT NULL UNIQUE,
74 transcendant BOOL NOT NULL DEFAULT FALSE
76 COMMENT ON TABLE config.bib_source IS $$
77 This is table is used to set up the relative "quality" of each
78 MARC source, such as OCLC. Also identifies "transcendant" sources,
79 i.e., sources of bib records that should display in the OPAC
80 even if no copies or located URIs are attached.
83 CREATE TABLE config.standing (
84 id SERIAL PRIMARY KEY,
85 value TEXT NOT NULL UNIQUE
87 COMMENT ON TABLE config.standing IS $$
90 This table contains the values that can be applied to a patron
91 by a staff member. These values should not be changed, other
92 than for translation, as the ID column is currently a "magic
93 number" in the source. :(
96 CREATE TABLE config.standing_penalty (
97 id SERIAL PRIMARY KEY,
98 name TEXT NOT NULL UNIQUE,
103 INSERT INTO config.standing_penalty (id,name,label,block_list)
104 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
105 INSERT INTO config.standing_penalty (id,name,label,block_list)
106 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
107 INSERT INTO config.standing_penalty (id,name,label,block_list)
108 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
109 INSERT INTO config.standing_penalty (id,name,label,block_list)
110 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
112 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
113 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
114 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
115 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
116 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
117 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
118 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
119 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
120 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
121 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
122 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
124 SELECT SETVAL('config.standing_penalty_id_seq', 100);
126 CREATE TABLE config.xml_transform (
127 name TEXT PRIMARY KEY,
128 namespace_uri TEXT NOT NULL,
129 prefix TEXT NOT NULL,
133 CREATE TABLE config.biblio_fingerprint (
134 id SERIAL PRIMARY KEY,
137 first_word BOOL NOT NULL DEFAULT FALSE,
138 format TEXT NOT NULL DEFAULT 'marcxml'
141 INSERT INTO config.biblio_fingerprint (name, xpath, format)
144 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
145 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
146 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
147 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
148 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
152 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
155 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
156 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
157 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
158 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
159 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
164 CREATE TABLE config.metabib_class (
165 name TEXT PRIMARY KEY,
166 label TEXT NOT NULL UNIQUE
169 CREATE TABLE config.metabib_field (
170 id SERIAL PRIMARY KEY,
171 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
175 weight INT NOT NULL DEFAULT 1,
176 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
177 search_field BOOL NOT NULL DEFAULT TRUE,
178 facet_field BOOL NOT NULL DEFAULT FALSE,
181 COMMENT ON TABLE config.metabib_field IS $$
182 XPath used for record indexing ingest
184 This table contains the XPath used to chop up MODS into its
185 indexable parts. Each XPath entry is named and assigned to
186 a "class" of either title, subject, author, keyword, series
190 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
192 CREATE TABLE config.metabib_search_alias (
193 alias TEXT PRIMARY KEY,
194 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
195 field INT REFERENCES config.metabib_field (id)
198 CREATE TABLE config.non_cataloged_type (
199 id SERIAL PRIMARY KEY,
200 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
202 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
203 in_house BOOL NOT NULL DEFAULT FALSE,
204 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
206 COMMENT ON TABLE config.non_cataloged_type IS $$
207 Types of valid non-cataloged items.
210 CREATE TABLE config.identification_type (
211 id SERIAL PRIMARY KEY,
212 name TEXT NOT NULL UNIQUE
214 COMMENT ON TABLE config.identification_type IS $$
215 Types of valid patron identification.
217 Each patron must display at least one valid form of identification
218 in order to get a library card. This table lists those forms.
221 CREATE TABLE config.rule_circ_duration (
222 id SERIAL PRIMARY KEY,
223 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
224 extended INTERVAL NOT NULL,
225 normal INTERVAL NOT NULL,
226 shrt INTERVAL NOT NULL,
227 max_renewals INT NOT NULL
229 COMMENT ON TABLE config.rule_circ_duration IS $$
230 Circulation Duration rules
232 Each circulation is given a duration based on one of these rules.
235 CREATE TABLE config.hard_due_date (
236 id SERIAL PRIMARY KEY,
237 name TEXT NOT NULL UNIQUE,
238 ceiling_date TIMESTAMPTZ NOT NULL,
239 forceto BOOL NOT NULL,
240 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
243 CREATE TABLE config.hard_due_date_values (
244 id SERIAL PRIMARY KEY,
245 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
246 DEFERRABLE INITIALLY DEFERRED,
247 ceiling_date TIMESTAMPTZ NOT NULL,
248 active_date TIMESTAMPTZ NOT NULL
251 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
253 temp_value config.hard_due_date_values%ROWTYPE;
257 SELECT DISTINCT ON (hard_due_date) *
258 FROM config.hard_due_date_values
259 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
260 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
262 UPDATE config.hard_due_date
263 SET ceiling_date = temp_value.ceiling_date
264 WHERE id = temp_value.hard_due_date
265 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
268 updated := updated + 1;
274 $func$ LANGUAGE plpgsql;
276 CREATE TABLE config.rule_max_fine (
277 id SERIAL PRIMARY KEY,
278 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
279 amount NUMERIC(6,2) NOT NULL,
280 is_percent BOOL NOT NULL DEFAULT FALSE
282 COMMENT ON TABLE config.rule_max_fine IS $$
283 Circulation Max Fine rules
285 Each circulation is given a maximum fine based on one of
289 CREATE TABLE config.rule_recurring_fine (
290 id SERIAL PRIMARY KEY,
291 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
292 high NUMERIC(6,2) NOT NULL,
293 normal NUMERIC(6,2) NOT NULL,
294 low NUMERIC(6,2) NOT NULL,
295 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
296 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
298 COMMENT ON TABLE config.rule_recurring_fine IS $$
299 Circulation Recurring Fine rules
301 Each circulation is given a recurring fine amount based on one of
302 these rules. Note that it is recommended to run the fine generator
303 (from cron) at least as frequently as the lowest recurrence interval
304 used by your circulation rules so that accrued fines will be up
309 CREATE TABLE config.rule_age_hold_protect (
310 id SERIAL PRIMARY KEY,
311 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
312 age INTERVAL NOT NULL,
315 COMMENT ON TABLE config.rule_age_hold_protect IS $$
316 Hold Item Age Protection rules
318 A hold request can only capture new(ish) items when they are
319 within a particular proximity of the pickup_lib of the request.
320 The proximity ('prox' column) is calculated by counting
321 the number of tree edges between the pickup_lib and either the
322 owning_lib or circ_lib of the copy that could fulfill the hold,
323 as determined by the distance_is_from_owner value of the hold matrix
324 rule controlling the hold request.
327 CREATE TABLE config.copy_status (
328 id SERIAL PRIMARY KEY,
329 name TEXT NOT NULL UNIQUE,
330 holdable BOOL NOT NULL DEFAULT FALSE,
331 opac_visible BOOL NOT NULL DEFAULT FALSE
333 COMMENT ON TABLE config.copy_status IS $$
336 The available copy statuses, and whether a copy in that
337 status is available for hold request capture. 0 (zero) is
338 the only special number in this set, meaning that the item
339 is available for immediate checkout, and is counted as available
342 Statuses with an ID below 100 are not removable, and have special
343 meaning in the code. Do not change them except to translate the
346 You may add and remove statuses above 100, and these can be used
347 to remove items from normal circulation without affecting the rest
348 of the copy's values or its location.
351 CREATE TABLE config.net_access_level (
352 id SERIAL PRIMARY KEY,
353 name TEXT NOT NULL UNIQUE
355 COMMENT ON TABLE config.net_access_level IS $$
356 Patron Network Access level
358 This will be used to inform the in-library firewall of how much
359 internet access the using patron should be allowed.
363 CREATE TABLE config.remote_account (
364 id SERIAL PRIMARY KEY,
366 host TEXT NOT NULL, -- name or IP, :port optional
367 username TEXT, -- optional, since we could default to $USER
368 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
369 account TEXT, -- aka profile or FTP "account" command
370 path TEXT, -- aka directory
371 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
372 last_activity TIMESTAMP WITH TIME ZONE
375 CREATE TABLE config.marc21_rec_type_map (
376 code TEXT PRIMARY KEY,
377 type_val TEXT NOT NULL,
378 blvl_val TEXT NOT NULL
381 CREATE TABLE config.marc21_ff_pos_map (
382 id SERIAL PRIMARY KEY,
383 fixed_field TEXT NOT NULL,
385 rec_type TEXT NOT NULL,
386 start_pos INT NOT NULL,
388 default_val TEXT NOT NULL DEFAULT ' '
391 CREATE TABLE config.marc21_physical_characteristic_type_map (
392 ptype_key TEXT PRIMARY KEY,
393 label TEXT NOT NULL -- I18N
396 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
397 id SERIAL PRIMARY KEY,
398 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
399 subfield TEXT NOT NULL,
400 start_pos INT NOT NULL,
402 label TEXT NOT NULL -- I18N
405 CREATE TABLE config.marc21_physical_characteristic_value_map (
406 id SERIAL PRIMARY KEY,
408 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
409 label TEXT NOT NULL -- I18N
413 CREATE TABLE config.z3950_source (
414 name TEXT PRIMARY KEY,
415 label TEXT NOT NULL UNIQUE,
419 record_format TEXT NOT NULL DEFAULT 'FI',
420 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
421 auth BOOL NOT NULL DEFAULT TRUE
424 COMMENT ON TABLE config.z3950_source IS $$
427 Each row in this table represents a database searchable via Z39.50.
430 COMMENT ON COLUMN config.z3950_source.record_format IS $$
434 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
435 Z39.50 preferred record syntax..
439 CREATE TABLE config.z3950_attr (
440 id SERIAL PRIMARY KEY,
441 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
446 truncation INT NOT NULL DEFAULT 0,
447 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
450 CREATE TABLE config.i18n_locale (
451 code TEXT PRIMARY KEY,
452 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
453 name TEXT UNIQUE NOT NULL,
457 CREATE TABLE config.i18n_core (
458 id BIGSERIAL PRIMARY KEY,
459 fq_field TEXT NOT NULL,
460 identity_value TEXT NOT NULL,
461 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
465 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
467 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
471 UPDATE config.i18n_core
472 SET identity_value = $$ || quote_literal(new_ident) || $$
473 WHERE fq_field LIKE '$$ || hint || $$.%'
474 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
479 $_$ LANGUAGE PLPGSQL;
481 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
483 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
486 $_$ LANGUAGE PLPGSQL;
488 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
490 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
493 $_$ LANGUAGE PLPGSQL;
495 CREATE TABLE config.billing_type (
496 id SERIAL PRIMARY KEY,
498 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
499 default_price NUMERIC(6,2),
500 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
503 CREATE TABLE config.settings_group (
504 name TEXT PRIMARY KEY,
505 label TEXT UNIQUE NOT NULL -- I18N
508 CREATE TABLE config.org_unit_setting_type (
509 name TEXT PRIMARY KEY,
510 label TEXT UNIQUE NOT NULL,
511 grp TEXT REFERENCES config.settings_group (name),
513 datatype TEXT NOT NULL DEFAULT 'string',
518 -- define valid datatypes
520 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
521 ( 'bool', 'integer', 'float', 'currency', 'interval',
522 'date', 'string', 'object', 'array', 'link' ) ),
524 -- fm_class is meaningful only for 'link' datatype
526 CONSTRAINT coust_no_empty_link CHECK
527 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
528 ( datatype <> 'link' AND fm_class IS NULL ) )
531 CREATE TABLE config.usr_setting_type (
533 name TEXT PRIMARY KEY,
534 opac_visible BOOL NOT NULL DEFAULT FALSE,
535 label TEXT UNIQUE NOT NULL,
537 grp TEXT REFERENCES config.settings_group (name),
538 datatype TEXT NOT NULL DEFAULT 'string',
542 -- define valid datatypes
544 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
545 ( 'bool', 'integer', 'float', 'currency', 'interval',
546 'date', 'string', 'object', 'array', 'link' ) ),
549 -- fm_class is meaningful only for 'link' datatype
551 CONSTRAINT coust_no_empty_link CHECK
552 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
553 ( datatype <> 'link' AND fm_class IS NULL ) )
557 -- Some handy functions, based on existing ones, to provide optional ingest normalization
559 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
560 SELECT SUBSTRING($1,$2);
561 $func$ LANGUAGE SQL STRICT IMMUTABLE;
563 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
564 SELECT SUBSTRING($1,1,$2);
565 $func$ LANGUAGE SQL STRICT IMMUTABLE;
567 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
568 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
569 $func$ LANGUAGE SQL STRICT IMMUTABLE;
571 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
572 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
573 $func$ LANGUAGE SQL STRICT IMMUTABLE;
575 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
576 SELECT approximate_date( $1, '0');
577 $func$ LANGUAGE SQL STRICT IMMUTABLE;
579 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
580 SELECT approximate_date( $1, '9');
581 $func$ LANGUAGE SQL STRICT IMMUTABLE;
583 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
584 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
585 $func$ LANGUAGE SQL STRICT IMMUTABLE;
587 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
588 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
589 $func$ LANGUAGE SQL STRICT IMMUTABLE;
591 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
596 # Find the first ISBN, force it to ISBN13 and return it
600 foreach my $word (split(/\s/, $input)) {
601 my $isbn = Business::ISBN->new($word);
603 # First check the checksum; if it is not valid, fix it and add the original
604 # bad-checksum ISBN to the output
605 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
606 $isbn->fix_checksum();
609 # If we now have a valid ISBN, force it to ISBN13 and return it
610 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
613 $func$ LANGUAGE PLPERLU;
615 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
616 Inspired by translate_isbn1013
618 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
619 version without hypens and with a repaired checksum if the checksum was bad
623 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
628 # For each ISBN found in a single string containing a set of ISBNs:
629 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
630 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
635 foreach my $word (split(/\s/, $input)) {
636 my $isbn = Business::ISBN->new($word);
638 # First check the checksum; if it is not valid, fix it and add the original
639 # bad-checksum ISBN to the output
640 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
641 $output .= $isbn->isbn() . " ";
642 $isbn->fix_checksum();
645 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
646 # and add the normalized original ISBN to the output
647 if ($isbn && $isbn->is_valid()) {
648 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
649 $output .= $isbn->isbn . " ";
651 # If we successfully converted the ISBN to its counterpart, add the
652 # converted ISBN to the output as well
653 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
656 return $output if $output;
658 # If there were no valid ISBNs, just return the raw input
660 $func$ LANGUAGE PLPERLU;
662 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
663 The translate_isbn1013 function takes an input ISBN and returns the
664 following in a single space-delimited string if the input ISBN is valid:
665 - The normalized input ISBN (hyphens stripped)
666 - The normalized input ISBN with a fixed checksum if the checksum was bad
667 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
670 -- And ... a table in which to register them
672 CREATE TABLE config.index_normalizer (
673 id SERIAL PRIMARY KEY,
674 name TEXT UNIQUE NOT NULL,
677 param_count INT NOT NULL DEFAULT 0
680 CREATE TABLE config.metabib_field_index_norm_map (
681 id SERIAL PRIMARY KEY,
682 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
683 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
685 pos INT NOT NULL DEFAULT 0
688 CREATE TABLE config.record_attr_definition (
689 name TEXT PRIMARY KEY,
690 label TEXT NOT NULL, -- I18N
692 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
693 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
695 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
696 tag TEXT, -- LIKE format
697 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
699 -- This is used for both tag/sf and xpath entries
702 -- For xpath-extracted attrs
704 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
709 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
711 -- For phys-char fields
712 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
715 CREATE TABLE config.record_attr_index_norm_map (
716 id SERIAL PRIMARY KEY,
717 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
718 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
720 pos INT NOT NULL DEFAULT 0
723 CREATE TABLE config.coded_value_map (
724 id SERIAL PRIMARY KEY,
725 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
731 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
732 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
733 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
734 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
735 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
736 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
737 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
739 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
747 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
749 SELECT n.func AS func,
750 n.param_count AS param_count,
752 FROM config.index_normalizer n
753 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
754 WHERE field = NEW.field AND m.pos < 0
756 EXECUTE 'SELECT ' || normalizer.func || '(' ||
757 quote_literal( value ) ||
759 WHEN normalizer.param_count > 0
760 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
770 IF NEW.index_vector = ''::tsvector THEN
774 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
776 SELECT n.func AS func,
777 n.param_count AS param_count,
779 FROM config.index_normalizer n
780 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
781 WHERE field = NEW.field AND m.pos >= 0
783 EXECUTE 'SELECT ' || normalizer.func || '(' ||
784 quote_literal( value ) ||
786 WHEN normalizer.param_count > 0
787 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
795 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
796 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
798 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
805 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
806 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
807 SELECT DISTINCT l.version
808 FROM config.upgrade_log l
809 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
810 WHERE d.db_patch = my_db_patch
813 -- List applied db patches that are superseded by (and block the application of) my_db_patch
814 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
815 SELECT DISTINCT l.version
816 FROM config.upgrade_log l
817 JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
818 WHERE d.db_patch = my_db_patch
821 -- List applied db patches that deprecates (and block the application of) my_db_patch
822 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
824 FROM config.db_patch_dependencies
825 WHERE my_db_patch::TEXT[] && deprecates
828 -- List applied db patches that supersedes (and block the application of) my_db_patch
829 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS TEXT AS $$
831 FROM config.db_patch_dependencies
832 WHERE my_db_patch::TEXT[] && supersedes
835 -- Make sure that no deprecated or superseded db patches are currently applied
836 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
838 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( my_db_patch )
840 SELECT * FROM evergreen.upgrade_list_applied_supersedes( my_db_patch )
842 SELECT * FROM evergreen.upgrade_list_applied_deprecated( my_db_patch )
844 SELECT * FROM evergreen.upgrade_list_applied_superseded( my_db_patch ))x
847 -- Raise an exception if there are, in fact, dep/sup confilct
848 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
850 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
852 Upgrade script % can not be applied:
853 applied deprecated scripts %
854 applied superseded scripts %
858 ARRAY_ACUM(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
859 ARRAY_ACUM(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
860 evergreen.upgrade_list_applied_deprecated(my_db_patch),
861 evergreen.upgrade_list_applied_superseded(my_db_patch);
864 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);