2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
20 DROP SCHEMA IF EXISTS stats CASCADE;
21 DROP SCHEMA IF EXISTS config CASCADE;
27 COMMENT ON SCHEMA config IS $$
29 * Copyright (C) 2005 Georgia Public Library Service
30 * Mike Rylander <mrylander@gmail.com>
32 * The config schema holds static configuration data for the
33 * Open-ILS installation.
37 * This program is free software; you can redistribute it and/or
38 * modify it under the terms of the GNU General Public License
39 * as published by the Free Software Foundation; either version 2
40 * of the License, or (at your option) any later version.
42 * This program is distributed in the hope that it will be useful,
43 * but WITHOUT ANY WARRANTY; without even the implied warranty of
44 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
45 * GNU General Public License for more details.
49 CREATE TABLE config.internal_flag (
50 name TEXT PRIMARY KEY,
52 enabled BOOL NOT NULL DEFAULT FALSE
54 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_insert');
55 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_update');
56 INSERT INTO config.internal_flag (name) VALUES ('ingest.reingest.force_on_same_marc');
57 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri');
58 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec');
59 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor');
60 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry');
61 INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only');
63 CREATE TABLE config.global_flag (
65 ) INHERITS (config.internal_flag);
66 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
68 CREATE TABLE config.upgrade_log (
69 version TEXT PRIMARY KEY,
70 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
73 INSERT INTO config.upgrade_log (version) VALUES ('0507'); -- miker
75 CREATE TABLE config.bib_source (
76 id SERIAL PRIMARY KEY,
77 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
78 source TEXT NOT NULL UNIQUE,
79 transcendant BOOL NOT NULL DEFAULT FALSE
81 COMMENT ON TABLE config.bib_source IS $$
83 * Copyright (C) 2005 Georgia Public Library Service
84 * Mike Rylander <mrylander@gmail.com>
86 * Valid sources of MARC records
88 * This is table is used to set up the relative "quality" of each
89 * MARC source, such as OCLC.
93 * This program is free software; you can redistribute it and/or
94 * modify it under the terms of the GNU General Public License
95 * as published by the Free Software Foundation; either version 2
96 * of the License, or (at your option) any later version.
98 * This program is distributed in the hope that it will be useful,
99 * but WITHOUT ANY WARRANTY; without even the implied warranty of
100 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
101 * GNU General Public License for more details.
105 CREATE TABLE config.standing (
106 id SERIAL PRIMARY KEY,
107 value TEXT NOT NULL UNIQUE
109 COMMENT ON TABLE config.standing IS $$
111 * Copyright (C) 2005 Georgia Public Library Service
112 * Mike Rylander <mrylander@gmail.com>
116 * This table contains the values that can be applied to a patron
117 * by a staff member. These values should not be changed, other
118 * than for translation, as the ID column is currently a "magic
119 * number" in the source. :(
123 * This program is free software; you can redistribute it and/or
124 * modify it under the terms of the GNU General Public License
125 * as published by the Free Software Foundation; either version 2
126 * of the License, or (at your option) any later version.
128 * This program is distributed in the hope that it will be useful,
129 * but WITHOUT ANY WARRANTY; without even the implied warranty of
130 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
131 * GNU General Public License for more details.
135 CREATE TABLE config.standing_penalty (
136 id SERIAL PRIMARY KEY,
137 name TEXT NOT NULL UNIQUE,
142 INSERT INTO config.standing_penalty (id,name,label,block_list)
143 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
144 INSERT INTO config.standing_penalty (id,name,label,block_list)
145 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
146 INSERT INTO config.standing_penalty (id,name,label,block_list)
147 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
148 INSERT INTO config.standing_penalty (id,name,label,block_list)
149 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
151 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
152 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
153 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
154 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
155 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
156 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
157 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
158 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
159 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
160 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
161 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
163 SELECT SETVAL('config.standing_penalty_id_seq', 100);
165 CREATE TABLE config.xml_transform (
166 name TEXT PRIMARY KEY,
167 namespace_uri TEXT NOT NULL,
168 prefix TEXT NOT NULL,
172 CREATE TABLE config.biblio_fingerprint (
173 id SERIAL PRIMARY KEY,
176 first_word BOOL NOT NULL DEFAULT FALSE,
177 format TEXT NOT NULL DEFAULT 'marcxml'
180 INSERT INTO config.biblio_fingerprint (name, xpath, format)
183 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
184 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
185 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
186 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
187 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
191 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
194 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
195 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
196 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
197 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
198 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
203 CREATE TABLE config.metabib_class (
204 name TEXT PRIMARY KEY,
205 label TEXT NOT NULL UNIQUE
208 CREATE TABLE config.metabib_field (
209 id SERIAL PRIMARY KEY,
210 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
214 weight INT NOT NULL DEFAULT 1,
215 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
216 search_field BOOL NOT NULL DEFAULT TRUE,
217 facet_field BOOL NOT NULL DEFAULT FALSE,
220 COMMENT ON TABLE config.metabib_field IS $$
222 * Copyright (C) 2005 Georgia Public Library Service
223 * Mike Rylander <mrylander@gmail.com>
225 * XPath used for record indexing ingest
227 * This table contains the XPath used to chop up MODS into its
228 * indexable parts. Each XPath entry is named and assigned to
229 * a "class" of either title, subject, author, keyword or series.
234 * This program is free software; you can redistribute it and/or
235 * modify it under the terms of the GNU General Public License
236 * as published by the Free Software Foundation; either version 2
237 * of the License, or (at your option) any later version.
239 * This program is distributed in the hope that it will be useful,
240 * but WITHOUT ANY WARRANTY; without even the implied warranty of
241 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
242 * GNU General Public License for more details.
246 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
248 CREATE TABLE config.metabib_search_alias (
249 alias TEXT PRIMARY KEY,
250 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
251 field INT REFERENCES config.metabib_field (id)
254 CREATE TABLE config.non_cataloged_type (
255 id SERIAL PRIMARY KEY,
256 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
258 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
259 in_house BOOL NOT NULL DEFAULT FALSE,
260 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
262 COMMENT ON TABLE config.non_cataloged_type IS $$
264 * Copyright (C) 2005 Georgia Public Library Service
265 * Mike Rylander <mrylander@gmail.com>
267 * Types of valid non-cataloged items.
272 * This program is free software; you can redistribute it and/or
273 * modify it under the terms of the GNU General Public License
274 * as published by the Free Software Foundation; either version 2
275 * of the License, or (at your option) any later version.
277 * This program is distributed in the hope that it will be useful,
278 * but WITHOUT ANY WARRANTY; without even the implied warranty of
279 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
280 * GNU General Public License for more details.
284 CREATE TABLE config.identification_type (
285 id SERIAL PRIMARY KEY,
286 name TEXT NOT NULL UNIQUE
288 COMMENT ON TABLE config.identification_type IS $$
290 * Copyright (C) 2005 Georgia Public Library Service
291 * Mike Rylander <mrylander@gmail.com>
293 * Types of valid patron identification.
295 * Each patron must display at least one valid form of identification
296 * in order to get a library card. This table lists those forms.
301 * This program is free software; you can redistribute it and/or
302 * modify it under the terms of the GNU General Public License
303 * as published by the Free Software Foundation; either version 2
304 * of the License, or (at your option) any later version.
306 * This program is distributed in the hope that it will be useful,
307 * but WITHOUT ANY WARRANTY; without even the implied warranty of
308 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
309 * GNU General Public License for more details.
313 CREATE TABLE config.rule_circ_duration (
314 id SERIAL PRIMARY KEY,
315 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
316 extended INTERVAL NOT NULL,
317 normal INTERVAL NOT NULL,
318 shrt INTERVAL NOT NULL,
319 max_renewals INT NOT NULL
321 COMMENT ON TABLE config.rule_circ_duration IS $$
323 * Copyright (C) 2005 Georgia Public Library Service
324 * Mike Rylander <mrylander@gmail.com>
326 * Circulation Duration rules
328 * Each circulation is given a duration based on one of these rules.
333 * This program is free software; you can redistribute it and/or
334 * modify it under the terms of the GNU General Public License
335 * as published by the Free Software Foundation; either version 2
336 * of the License, or (at your option) any later version.
338 * This program is distributed in the hope that it will be useful,
339 * but WITHOUT ANY WARRANTY; without even the implied warranty of
340 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
341 * GNU General Public License for more details.
345 CREATE TABLE config.hard_due_date (
346 id SERIAL PRIMARY KEY,
347 name TEXT NOT NULL UNIQUE,
348 ceiling_date TIMESTAMPTZ NOT NULL,
349 forceto BOOL NOT NULL,
350 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
353 CREATE TABLE config.hard_due_date_values (
354 id SERIAL PRIMARY KEY,
355 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
356 DEFERRABLE INITIALLY DEFERRED,
357 ceiling_date TIMESTAMPTZ NOT NULL,
358 active_date TIMESTAMPTZ NOT NULL
361 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
363 temp_value config.hard_due_date_values%ROWTYPE;
367 SELECT DISTINCT ON (hard_due_date) *
368 FROM config.hard_due_date_values
369 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
370 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
372 UPDATE config.hard_due_date
373 SET ceiling_date = temp_value.ceiling_date
374 WHERE id = temp_value.hard_due_date
375 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
378 updated := updated + 1;
384 $func$ LANGUAGE plpgsql;
386 CREATE TABLE config.rule_max_fine (
387 id SERIAL PRIMARY KEY,
388 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
389 amount NUMERIC(6,2) NOT NULL,
390 is_percent BOOL NOT NULL DEFAULT FALSE
392 COMMENT ON TABLE config.rule_max_fine IS $$
394 * Copyright (C) 2005 Georgia Public Library Service
395 * Mike Rylander <mrylander@gmail.com>
397 * Circulation Max Fine rules
399 * Each circulation is given a maximum fine based on one of
405 * This program is free software; you can redistribute it and/or
406 * modify it under the terms of the GNU General Public License
407 * as published by the Free Software Foundation; either version 2
408 * of the License, or (at your option) any later version.
410 * This program is distributed in the hope that it will be useful,
411 * but WITHOUT ANY WARRANTY; without even the implied warranty of
412 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
413 * GNU General Public License for more details.
417 CREATE TABLE config.rule_recurring_fine (
418 id SERIAL PRIMARY KEY,
419 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
420 high NUMERIC(6,2) NOT NULL,
421 normal NUMERIC(6,2) NOT NULL,
422 low NUMERIC(6,2) NOT NULL,
423 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
424 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
426 COMMENT ON TABLE config.rule_recurring_fine IS $$
428 * Copyright (C) 2005 Georgia Public Library Service
429 * Mike Rylander <mrylander@gmail.com>
431 * Circulation Recurring Fine rules
433 * Each circulation is given a recurring fine amount based on one of
434 * these rules. The recurrence_interval should not be any shorter
435 * than the interval between runs of the fine_processor.pl script
436 * (which is run from CRON), or you could miss fines.
441 * This program is free software; you can redistribute it and/or
442 * modify it under the terms of the GNU General Public License
443 * as published by the Free Software Foundation; either version 2
444 * of the License, or (at your option) any later version.
446 * This program is distributed in the hope that it will be useful,
447 * but WITHOUT ANY WARRANTY; without even the implied warranty of
448 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
449 * GNU General Public License for more details.
454 CREATE TABLE config.rule_age_hold_protect (
455 id SERIAL PRIMARY KEY,
456 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
457 age INTERVAL NOT NULL,
460 COMMENT ON TABLE config.rule_age_hold_protect IS $$
462 * Copyright (C) 2005 Georgia Public Library Service
463 * Mike Rylander <mrylander@gmail.com>
465 * Hold Item Age Protection rules
467 * A hold request can only capture new(ish) items when they are
468 * within a particular proximity of the home_ou of the requesting
469 * user. The proximity ('prox' column) is calculated by counting
470 * the number of tree edges between the user's home_ou and the owning_lib
471 * of the copy that could fulfill the hold.
476 * This program is free software; you can redistribute it and/or
477 * modify it under the terms of the GNU General Public License
478 * as published by the Free Software Foundation; either version 2
479 * of the License, or (at your option) any later version.
481 * This program is distributed in the hope that it will be useful,
482 * but WITHOUT ANY WARRANTY; without even the implied warranty of
483 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
484 * GNU General Public License for more details.
488 CREATE TABLE config.copy_status (
489 id SERIAL PRIMARY KEY,
490 name TEXT NOT NULL UNIQUE,
491 holdable BOOL NOT NULL DEFAULT FALSE,
492 opac_visible BOOL NOT NULL DEFAULT FALSE
494 COMMENT ON TABLE config.copy_status IS $$
496 * Copyright (C) 2005 Georgia Public Library Service
497 * Mike Rylander <mrylander@gmail.com>
501 * The available copy statuses, and whether a copy in that
502 * status is available for hold request capture. 0 (zero) is
503 * the only special number in this set, meaning that the item
504 * is available for immediate checkout, and is counted as available
507 * Statuses with an ID below 100 are not removable, and have special
508 * meaning in the code. Do not change them except to translate the
511 * You may add and remove statuses above 100, and these can be used
512 * to remove items from normal circulation without affecting the rest
513 * of the copy's values or its location.
517 * This program is free software; you can redistribute it and/or
518 * modify it under the terms of the GNU General Public License
519 * as published by the Free Software Foundation; either version 2
520 * of the License, or (at your option) any later version.
522 * This program is distributed in the hope that it will be useful,
523 * but WITHOUT ANY WARRANTY; without even the implied warranty of
524 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
525 * GNU General Public License for more details.
529 CREATE TABLE config.net_access_level (
530 id SERIAL PRIMARY KEY,
531 name TEXT NOT NULL UNIQUE
533 COMMENT ON TABLE config.net_access_level IS $$
535 * Copyright (C) 2005 Georgia Public Library Service
536 * Mike Rylander <mrylander@gmail.com>
538 * Patron Network Access level
540 * This will be used to inform the in-library firewall of how much
541 * internet access the using patron should be allowed.
545 * This program is free software; you can redistribute it and/or
546 * modify it under the terms of the GNU General Public License
547 * as published by the Free Software Foundation; either version 2
548 * of the License, or (at your option) any later version.
550 * This program is distributed in the hope that it will be useful,
551 * but WITHOUT ANY WARRANTY; without even the implied warranty of
552 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
553 * GNU General Public License for more details.
558 CREATE TABLE config.remote_account (
559 id SERIAL PRIMARY KEY,
561 host TEXT NOT NULL, -- name or IP, :port optional
562 username TEXT, -- optional, since we could default to $USER
563 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
564 account TEXT, -- aka profile or FTP "account" command
565 path TEXT, -- aka directory
566 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
567 last_activity TIMESTAMP WITH TIME ZONE
570 CREATE TABLE config.marc21_rec_type_map (
571 code TEXT PRIMARY KEY,
572 type_val TEXT NOT NULL,
573 blvl_val TEXT NOT NULL
576 CREATE TABLE config.marc21_ff_pos_map (
577 id SERIAL PRIMARY KEY,
578 fixed_field TEXT NOT NULL,
580 rec_type TEXT NOT NULL,
581 start_pos INT NOT NULL,
583 default_val TEXT NOT NULL DEFAULT ' '
586 CREATE TABLE config.marc21_physical_characteristic_type_map (
587 ptype_key TEXT PRIMARY KEY,
588 label TEXT NOT NULL -- I18N
591 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
592 id SERIAL PRIMARY KEY,
593 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
594 subfield TEXT NOT NULL,
595 start_pos INT NOT NULL,
597 label TEXT NOT NULL -- I18N
600 CREATE TABLE config.marc21_physical_characteristic_value_map (
601 id SERIAL PRIMARY KEY,
603 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
604 label TEXT NOT NULL -- I18N
608 CREATE TABLE config.z3950_source (
609 name TEXT PRIMARY KEY,
610 label TEXT NOT NULL UNIQUE,
614 record_format TEXT NOT NULL DEFAULT 'FI',
615 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
616 auth BOOL NOT NULL DEFAULT TRUE
619 COMMENT ON TABLE config.z3950_source IS $$
622 Each row in this table represents a database searchable via Z39.50.
625 COMMENT ON COLUMN config.z3950_source.record_format IS $$
629 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
630 Z39.50 preferred record syntax..
634 CREATE TABLE config.z3950_attr (
635 id SERIAL PRIMARY KEY,
636 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
641 truncation INT NOT NULL DEFAULT 0,
642 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
645 CREATE TABLE config.i18n_locale (
646 code TEXT PRIMARY KEY,
647 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
648 name TEXT UNIQUE NOT NULL,
652 CREATE TABLE config.i18n_core (
653 id BIGSERIAL PRIMARY KEY,
654 fq_field TEXT NOT NULL,
655 identity_value TEXT NOT NULL,
656 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
660 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
662 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
666 UPDATE config.i18n_core
667 SET identity_value = $$ || quote_literal(new_ident) || $$
668 WHERE fq_field LIKE '$$ || hint || $$.%'
669 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
674 $_$ LANGUAGE PLPGSQL;
676 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
678 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
681 $_$ LANGUAGE PLPGSQL;
683 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
685 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
688 $_$ LANGUAGE PLPGSQL;
690 CREATE TABLE config.billing_type (
691 id SERIAL PRIMARY KEY,
693 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
694 default_price NUMERIC(6,2),
695 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
698 CREATE TABLE config.settings_group (
699 name TEXT PRIMARY KEY,
700 label TEXT UNIQUE NOT NULL -- I18N
703 CREATE TABLE config.org_unit_setting_type (
704 name TEXT PRIMARY KEY,
705 label TEXT UNIQUE NOT NULL,
706 grp TEXT REFERENCES config.settings_group (name),
708 datatype TEXT NOT NULL DEFAULT 'string',
713 -- define valid datatypes
715 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
716 ( 'bool', 'integer', 'float', 'currency', 'interval',
717 'date', 'string', 'object', 'array', 'link' ) ),
719 -- fm_class is meaningful only for 'link' datatype
721 CONSTRAINT coust_no_empty_link CHECK
722 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
723 ( datatype <> 'link' AND fm_class IS NULL ) )
726 CREATE TABLE config.usr_setting_type (
728 name TEXT PRIMARY KEY,
729 opac_visible BOOL NOT NULL DEFAULT FALSE,
730 label TEXT UNIQUE NOT NULL,
732 grp TEXT REFERENCES config.settings_group (name),
733 datatype TEXT NOT NULL DEFAULT 'string',
737 -- define valid datatypes
739 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
740 ( 'bool', 'integer', 'float', 'currency', 'interval',
741 'date', 'string', 'object', 'array', 'link' ) ),
744 -- fm_class is meaningful only for 'link' datatype
746 CONSTRAINT coust_no_empty_link CHECK
747 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
748 ( datatype <> 'link' AND fm_class IS NULL ) )
752 -- Some handy functions, based on existing ones, to provide optional ingest normalization
754 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
755 SELECT SUBSTRING($1,$2);
756 $func$ LANGUAGE SQL STRICT IMMUTABLE;
758 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
759 SELECT SUBSTRING($1,1,$2);
760 $func$ LANGUAGE SQL STRICT IMMUTABLE;
762 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
763 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
764 $func$ LANGUAGE SQL STRICT IMMUTABLE;
766 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
767 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
768 $func$ LANGUAGE SQL STRICT IMMUTABLE;
770 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
771 SELECT approximate_date( $1, '0');
772 $func$ LANGUAGE SQL STRICT IMMUTABLE;
774 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
775 SELECT approximate_date( $1, '9');
776 $func$ LANGUAGE SQL STRICT IMMUTABLE;
778 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
779 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
780 $func$ LANGUAGE SQL STRICT IMMUTABLE;
782 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
783 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
784 $func$ LANGUAGE SQL STRICT IMMUTABLE;
786 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
791 # Find the first ISBN, force it to ISBN13 and return it
795 foreach my $word (split(/\s/, $input)) {
796 my $isbn = Business::ISBN->new($word);
798 # First check the checksum; if it is not valid, fix it and add the original
799 # bad-checksum ISBN to the output
800 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
801 $isbn->fix_checksum();
804 # If we now have a valid ISBN, force it to ISBN13 and return it
805 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
808 $func$ LANGUAGE PLPERLU;
810 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
812 * Copyright (C) 2011 Equinox Software
813 * Mike Rylander <mrylander@gmail.com>
815 * Inspired by translate_isbn1013
817 * The force_to_isbn13 function takes an input ISBN and returns the ISBN13
818 * version without hypens and with a repaired checksum if the checksum was bad
823 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
828 # For each ISBN found in a single string containing a set of ISBNs:
829 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
830 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
835 foreach my $word (split(/\s/, $input)) {
836 my $isbn = Business::ISBN->new($word);
838 # First check the checksum; if it is not valid, fix it and add the original
839 # bad-checksum ISBN to the output
840 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
841 $output .= $isbn->isbn() . " ";
842 $isbn->fix_checksum();
845 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
846 # and add the normalized original ISBN to the output
847 if ($isbn && $isbn->is_valid()) {
848 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
849 $output .= $isbn->isbn . " ";
851 # If we successfully converted the ISBN to its counterpart, add the
852 # converted ISBN to the output as well
853 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
856 return $output if $output;
858 # If there were no valid ISBNs, just return the raw input
860 $func$ LANGUAGE PLPERLU;
862 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
864 * Copyright (C) 2010 Merrimack Valley Library Consortium
865 * Jason Stephenson <jstephenson@mvlc.org>
866 * Copyright (C) 2010 Laurentian University
867 * Dan Scott <dscott@laurentian.ca>
869 * The translate_isbn1013 function takes an input ISBN and returns the
870 * following in a single space-delimited string if the input ISBN is valid:
871 * - The normalized input ISBN (hyphens stripped)
872 * - The normalized input ISBN with a fixed checksum if the checksum was bad
873 * - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
877 -- And ... a table in which to register them
879 CREATE TABLE config.index_normalizer (
880 id SERIAL PRIMARY KEY,
881 name TEXT UNIQUE NOT NULL,
884 param_count INT NOT NULL DEFAULT 0
887 CREATE TABLE config.metabib_field_index_norm_map (
888 id SERIAL PRIMARY KEY,
889 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
890 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
892 pos INT NOT NULL DEFAULT 0
895 CREATE TABLE config.record_attr_definition (
896 name TEXT PRIMARY KEY,
897 label TEXT NOT NULL, -- I18N
899 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
900 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
902 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
903 tag TEXT, -- LIKE format
904 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
906 -- This is used for both tag/sf and xpath entries
909 -- For xpath-extracted attrs
911 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
916 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
918 -- For phys-char fields
919 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
922 CREATE TABLE config.record_attr_index_norm_map (
923 id SERIAL PRIMARY KEY,
924 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
925 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
927 pos INT NOT NULL DEFAULT 0
930 CREATE TABLE config.coded_value_map (
931 id SERIAL PRIMARY KEY,
932 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
938 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
939 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
940 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
941 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
942 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
943 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
944 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
946 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
954 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
956 SELECT n.func AS func,
957 n.param_count AS param_count,
959 FROM config.index_normalizer n
960 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
961 WHERE field = NEW.field AND m.pos < 0
963 EXECUTE 'SELECT ' || normalizer.func || '(' ||
964 quote_literal( value ) ||
966 WHEN normalizer.param_count > 0
967 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
977 IF NEW.index_vector = ''::tsvector THEN
981 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
983 SELECT n.func AS func,
984 n.param_count AS param_count,
986 FROM config.index_normalizer n
987 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
988 WHERE field = NEW.field AND m.pos >= 0
990 EXECUTE 'SELECT ' || normalizer.func || '(' ||
991 quote_literal( value ) ||
993 WHEN normalizer.param_count > 0
994 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
1002 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
1003 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
1005 NEW.index_vector = to_tsvector(TG_ARGV[0], value);
1010 $$ LANGUAGE PLPGSQL;