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()
60 INSERT INTO config.upgrade_log (version) VALUES ('0521'); -- dbs
62 CREATE TABLE config.bib_source (
63 id SERIAL PRIMARY KEY,
64 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
65 source TEXT NOT NULL UNIQUE,
66 transcendant BOOL NOT NULL DEFAULT FALSE
68 COMMENT ON TABLE config.bib_source IS $$
69 This is table is used to set up the relative "quality" of each
70 MARC source, such as OCLC. Also identifies "transcendant" sources,
71 i.e., sources of bib records that should display in the OPAC
72 even if no copies or located URIs are attached.
75 CREATE TABLE config.standing (
76 id SERIAL PRIMARY KEY,
77 value TEXT NOT NULL UNIQUE
79 COMMENT ON TABLE config.standing IS $$
82 This table contains the values that can be applied to a patron
83 by a staff member. These values should not be changed, other
84 than for translation, as the ID column is currently a "magic
85 number" in the source. :(
88 CREATE TABLE config.standing_penalty (
89 id SERIAL PRIMARY KEY,
90 name TEXT NOT NULL UNIQUE,
95 INSERT INTO config.standing_penalty (id,name,label,block_list)
96 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
97 INSERT INTO config.standing_penalty (id,name,label,block_list)
98 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
99 INSERT INTO config.standing_penalty (id,name,label,block_list)
100 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
101 INSERT INTO config.standing_penalty (id,name,label,block_list)
102 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
104 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
105 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
106 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
107 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
108 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
109 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
110 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
111 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
112 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
113 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
114 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
116 SELECT SETVAL('config.standing_penalty_id_seq', 100);
118 CREATE TABLE config.xml_transform (
119 name TEXT PRIMARY KEY,
120 namespace_uri TEXT NOT NULL,
121 prefix TEXT NOT NULL,
125 CREATE TABLE config.biblio_fingerprint (
126 id SERIAL PRIMARY KEY,
129 first_word BOOL NOT NULL DEFAULT FALSE,
130 format TEXT NOT NULL DEFAULT 'marcxml'
133 INSERT INTO config.biblio_fingerprint (name, xpath, format)
136 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
137 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
138 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
139 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
140 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
144 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
147 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
148 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
149 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
150 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
151 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
156 CREATE TABLE config.metabib_class (
157 name TEXT PRIMARY KEY,
158 label TEXT NOT NULL UNIQUE
161 CREATE TABLE config.metabib_field (
162 id SERIAL PRIMARY KEY,
163 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
167 weight INT NOT NULL DEFAULT 1,
168 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
169 search_field BOOL NOT NULL DEFAULT TRUE,
170 facet_field BOOL NOT NULL DEFAULT FALSE,
173 COMMENT ON TABLE config.metabib_field IS $$
174 XPath used for record indexing ingest
176 This table contains the XPath used to chop up MODS into its
177 indexable parts. Each XPath entry is named and assigned to
178 a "class" of either title, subject, author, keyword, series
182 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
184 CREATE TABLE config.metabib_search_alias (
185 alias TEXT PRIMARY KEY,
186 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
187 field INT REFERENCES config.metabib_field (id)
190 CREATE TABLE config.non_cataloged_type (
191 id SERIAL PRIMARY KEY,
192 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
194 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
195 in_house BOOL NOT NULL DEFAULT FALSE,
196 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
198 COMMENT ON TABLE config.non_cataloged_type IS $$
199 Types of valid non-cataloged items.
202 CREATE TABLE config.identification_type (
203 id SERIAL PRIMARY KEY,
204 name TEXT NOT NULL UNIQUE
206 COMMENT ON TABLE config.identification_type IS $$
207 Types of valid patron identification.
209 Each patron must display at least one valid form of identification
210 in order to get a library card. This table lists those forms.
213 CREATE TABLE config.rule_circ_duration (
214 id SERIAL PRIMARY KEY,
215 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
216 extended INTERVAL NOT NULL,
217 normal INTERVAL NOT NULL,
218 shrt INTERVAL NOT NULL,
219 max_renewals INT NOT NULL
221 COMMENT ON TABLE config.rule_circ_duration IS $$
222 Circulation Duration rules
224 Each circulation is given a duration based on one of these rules.
227 CREATE TABLE config.hard_due_date (
228 id SERIAL PRIMARY KEY,
229 name TEXT NOT NULL UNIQUE,
230 ceiling_date TIMESTAMPTZ NOT NULL,
231 forceto BOOL NOT NULL,
232 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
235 CREATE TABLE config.hard_due_date_values (
236 id SERIAL PRIMARY KEY,
237 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
238 DEFERRABLE INITIALLY DEFERRED,
239 ceiling_date TIMESTAMPTZ NOT NULL,
240 active_date TIMESTAMPTZ NOT NULL
243 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
245 temp_value config.hard_due_date_values%ROWTYPE;
249 SELECT DISTINCT ON (hard_due_date) *
250 FROM config.hard_due_date_values
251 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
252 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
254 UPDATE config.hard_due_date
255 SET ceiling_date = temp_value.ceiling_date
256 WHERE id = temp_value.hard_due_date
257 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
260 updated := updated + 1;
266 $func$ LANGUAGE plpgsql;
268 CREATE TABLE config.rule_max_fine (
269 id SERIAL PRIMARY KEY,
270 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
271 amount NUMERIC(6,2) NOT NULL,
272 is_percent BOOL NOT NULL DEFAULT FALSE
274 COMMENT ON TABLE config.rule_max_fine IS $$
275 Circulation Max Fine rules
277 Each circulation is given a maximum fine based on one of
281 CREATE TABLE config.rule_recurring_fine (
282 id SERIAL PRIMARY KEY,
283 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
284 high NUMERIC(6,2) NOT NULL,
285 normal NUMERIC(6,2) NOT NULL,
286 low NUMERIC(6,2) NOT NULL,
287 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
288 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
290 COMMENT ON TABLE config.rule_recurring_fine IS $$
291 Circulation Recurring Fine rules
293 Each circulation is given a recurring fine amount based on one of
294 these rules. Note that it is recommended to run the fine generator
295 (from cron) at least as frequently as the lowest recurrence interval
296 used by your circulation rules so that accrued fines will be up
301 CREATE TABLE config.rule_age_hold_protect (
302 id SERIAL PRIMARY KEY,
303 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
304 age INTERVAL NOT NULL,
307 COMMENT ON TABLE config.rule_age_hold_protect IS $$
308 Hold Item Age Protection rules
310 A hold request can only capture new(ish) items when they are
311 within a particular proximity of the pickup_lib of the request.
312 The proximity ('prox' column) is calculated by counting
313 the number of tree edges between the pickup_lib and either the
314 owning_lib or circ_lib of the copy that could fulfill the hold,
315 as determined by the distance_is_from_owner value of the hold matrix
316 rule controlling the hold request.
319 CREATE TABLE config.copy_status (
320 id SERIAL PRIMARY KEY,
321 name TEXT NOT NULL UNIQUE,
322 holdable BOOL NOT NULL DEFAULT FALSE,
323 opac_visible BOOL NOT NULL DEFAULT FALSE
325 COMMENT ON TABLE config.copy_status IS $$
328 The available copy statuses, and whether a copy in that
329 status is available for hold request capture. 0 (zero) is
330 the only special number in this set, meaning that the item
331 is available for immediate checkout, and is counted as available
334 Statuses with an ID below 100 are not removable, and have special
335 meaning in the code. Do not change them except to translate the
338 You may add and remove statuses above 100, and these can be used
339 to remove items from normal circulation without affecting the rest
340 of the copy's values or its location.
343 CREATE TABLE config.net_access_level (
344 id SERIAL PRIMARY KEY,
345 name TEXT NOT NULL UNIQUE
347 COMMENT ON TABLE config.net_access_level IS $$
348 Patron Network Access level
350 This will be used to inform the in-library firewall of how much
351 internet access the using patron should be allowed.
355 CREATE TABLE config.remote_account (
356 id SERIAL PRIMARY KEY,
358 host TEXT NOT NULL, -- name or IP, :port optional
359 username TEXT, -- optional, since we could default to $USER
360 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
361 account TEXT, -- aka profile or FTP "account" command
362 path TEXT, -- aka directory
363 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
364 last_activity TIMESTAMP WITH TIME ZONE
367 CREATE TABLE config.marc21_rec_type_map (
368 code TEXT PRIMARY KEY,
369 type_val TEXT NOT NULL,
370 blvl_val TEXT NOT NULL
373 CREATE TABLE config.marc21_ff_pos_map (
374 id SERIAL PRIMARY KEY,
375 fixed_field TEXT NOT NULL,
377 rec_type TEXT NOT NULL,
378 start_pos INT NOT NULL,
380 default_val TEXT NOT NULL DEFAULT ' '
383 CREATE TABLE config.marc21_physical_characteristic_type_map (
384 ptype_key TEXT PRIMARY KEY,
385 label TEXT NOT NULL -- I18N
388 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
389 id SERIAL PRIMARY KEY,
390 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
391 subfield TEXT NOT NULL,
392 start_pos INT NOT NULL,
394 label TEXT NOT NULL -- I18N
397 CREATE TABLE config.marc21_physical_characteristic_value_map (
398 id SERIAL PRIMARY KEY,
400 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
401 label TEXT NOT NULL -- I18N
405 CREATE TABLE config.z3950_source (
406 name TEXT PRIMARY KEY,
407 label TEXT NOT NULL UNIQUE,
411 record_format TEXT NOT NULL DEFAULT 'FI',
412 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
413 auth BOOL NOT NULL DEFAULT TRUE
416 COMMENT ON TABLE config.z3950_source IS $$
419 Each row in this table represents a database searchable via Z39.50.
422 COMMENT ON COLUMN config.z3950_source.record_format IS $$
426 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
427 Z39.50 preferred record syntax..
431 CREATE TABLE config.z3950_attr (
432 id SERIAL PRIMARY KEY,
433 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
438 truncation INT NOT NULL DEFAULT 0,
439 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
442 CREATE TABLE config.i18n_locale (
443 code TEXT PRIMARY KEY,
444 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
445 name TEXT UNIQUE NOT NULL,
449 CREATE TABLE config.i18n_core (
450 id BIGSERIAL PRIMARY KEY,
451 fq_field TEXT NOT NULL,
452 identity_value TEXT NOT NULL,
453 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
457 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
459 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
463 UPDATE config.i18n_core
464 SET identity_value = $$ || quote_literal(new_ident) || $$
465 WHERE fq_field LIKE '$$ || hint || $$.%'
466 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
471 $_$ LANGUAGE PLPGSQL;
473 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
475 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
478 $_$ LANGUAGE PLPGSQL;
480 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
482 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
485 $_$ LANGUAGE PLPGSQL;
487 CREATE TABLE config.billing_type (
488 id SERIAL PRIMARY KEY,
490 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
491 default_price NUMERIC(6,2),
492 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
495 CREATE TABLE config.settings_group (
496 name TEXT PRIMARY KEY,
497 label TEXT UNIQUE NOT NULL -- I18N
500 CREATE TABLE config.org_unit_setting_type (
501 name TEXT PRIMARY KEY,
502 label TEXT UNIQUE NOT NULL,
503 grp TEXT REFERENCES config.settings_group (name),
505 datatype TEXT NOT NULL DEFAULT 'string',
510 -- define valid datatypes
512 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
513 ( 'bool', 'integer', 'float', 'currency', 'interval',
514 'date', 'string', 'object', 'array', 'link' ) ),
516 -- fm_class is meaningful only for 'link' datatype
518 CONSTRAINT coust_no_empty_link CHECK
519 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
520 ( datatype <> 'link' AND fm_class IS NULL ) )
523 CREATE TABLE config.usr_setting_type (
525 name TEXT PRIMARY KEY,
526 opac_visible BOOL NOT NULL DEFAULT FALSE,
527 label TEXT UNIQUE NOT NULL,
529 grp TEXT REFERENCES config.settings_group (name),
530 datatype TEXT NOT NULL DEFAULT 'string',
534 -- define valid datatypes
536 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
537 ( 'bool', 'integer', 'float', 'currency', 'interval',
538 'date', 'string', 'object', 'array', 'link' ) ),
541 -- fm_class is meaningful only for 'link' datatype
543 CONSTRAINT coust_no_empty_link CHECK
544 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
545 ( datatype <> 'link' AND fm_class IS NULL ) )
549 -- Some handy functions, based on existing ones, to provide optional ingest normalization
551 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
552 SELECT SUBSTRING($1,$2);
553 $func$ LANGUAGE SQL STRICT IMMUTABLE;
555 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
556 SELECT SUBSTRING($1,1,$2);
557 $func$ LANGUAGE SQL STRICT IMMUTABLE;
559 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
560 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
561 $func$ LANGUAGE SQL STRICT IMMUTABLE;
563 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
564 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
565 $func$ LANGUAGE SQL STRICT IMMUTABLE;
567 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
568 SELECT approximate_date( $1, '0');
569 $func$ LANGUAGE SQL STRICT IMMUTABLE;
571 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
572 SELECT approximate_date( $1, '9');
573 $func$ LANGUAGE SQL STRICT IMMUTABLE;
575 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
576 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
577 $func$ LANGUAGE SQL STRICT IMMUTABLE;
579 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
580 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
581 $func$ LANGUAGE SQL STRICT IMMUTABLE;
583 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
588 # Find the first ISBN, force it to ISBN13 and return it
592 foreach my $word (split(/\s/, $input)) {
593 my $isbn = Business::ISBN->new($word);
595 # First check the checksum; if it is not valid, fix it and add the original
596 # bad-checksum ISBN to the output
597 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
598 $isbn->fix_checksum();
601 # If we now have a valid ISBN, force it to ISBN13 and return it
602 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
605 $func$ LANGUAGE PLPERLU;
607 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
608 Inspired by translate_isbn1013
610 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
611 version without hypens and with a repaired checksum if the checksum was bad
615 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
620 # For each ISBN found in a single string containing a set of ISBNs:
621 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
622 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
627 foreach my $word (split(/\s/, $input)) {
628 my $isbn = Business::ISBN->new($word);
630 # First check the checksum; if it is not valid, fix it and add the original
631 # bad-checksum ISBN to the output
632 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
633 $output .= $isbn->isbn() . " ";
634 $isbn->fix_checksum();
637 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
638 # and add the normalized original ISBN to the output
639 if ($isbn && $isbn->is_valid()) {
640 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
641 $output .= $isbn->isbn . " ";
643 # If we successfully converted the ISBN to its counterpart, add the
644 # converted ISBN to the output as well
645 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
648 return $output if $output;
650 # If there were no valid ISBNs, just return the raw input
652 $func$ LANGUAGE PLPERLU;
654 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
655 The translate_isbn1013 function takes an input ISBN and returns the
656 following in a single space-delimited string if the input ISBN is valid:
657 - The normalized input ISBN (hyphens stripped)
658 - The normalized input ISBN with a fixed checksum if the checksum was bad
659 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
662 -- And ... a table in which to register them
664 CREATE TABLE config.index_normalizer (
665 id SERIAL PRIMARY KEY,
666 name TEXT UNIQUE NOT NULL,
669 param_count INT NOT NULL DEFAULT 0
672 CREATE TABLE config.metabib_field_index_norm_map (
673 id SERIAL PRIMARY KEY,
674 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
675 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
677 pos INT NOT NULL DEFAULT 0
680 CREATE TABLE config.record_attr_definition (
681 name TEXT PRIMARY KEY,
682 label TEXT NOT NULL, -- I18N
684 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
685 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
687 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
688 tag TEXT, -- LIKE format
689 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
691 -- This is used for both tag/sf and xpath entries
694 -- For xpath-extracted attrs
696 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
701 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
703 -- For phys-char fields
704 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
707 CREATE TABLE config.record_attr_index_norm_map (
708 id SERIAL PRIMARY KEY,
709 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
710 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
712 pos INT NOT NULL DEFAULT 0
715 CREATE TABLE config.coded_value_map (
716 id SERIAL PRIMARY KEY,
717 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
723 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
724 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
725 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
726 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
727 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
728 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
729 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
731 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
739 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
741 SELECT n.func AS func,
742 n.param_count AS param_count,
744 FROM config.index_normalizer n
745 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
746 WHERE field = NEW.field AND m.pos < 0
748 EXECUTE 'SELECT ' || normalizer.func || '(' ||
749 quote_literal( value ) ||
751 WHEN normalizer.param_count > 0
752 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
762 IF NEW.index_vector = ''::tsvector THEN
766 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
768 SELECT n.func AS func,
769 n.param_count AS param_count,
771 FROM config.index_normalizer n
772 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
773 WHERE field = NEW.field AND m.pos >= 0
775 EXECUTE 'SELECT ' || normalizer.func || '(' ||
776 quote_literal( value ) ||
778 WHEN normalizer.param_count > 0
779 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
787 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
788 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
790 NEW.index_vector = to_tsvector(TG_ARGV[0], value);