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');
62 CREATE TABLE config.global_flag (
64 ) INHERITS (config.internal_flag);
65 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
67 CREATE TABLE config.upgrade_log (
68 version TEXT PRIMARY KEY,
69 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
72 INSERT INTO config.upgrade_log (version) VALUES ('0410'); -- gmc
74 CREATE TABLE config.bib_source (
75 id SERIAL PRIMARY KEY,
76 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
77 source TEXT NOT NULL UNIQUE,
78 transcendant BOOL NOT NULL DEFAULT FALSE
80 COMMENT ON TABLE config.bib_source IS $$
82 * Copyright (C) 2005 Georgia Public Library Service
83 * Mike Rylander <mrylander@gmail.com>
85 * Valid sources of MARC records
87 * This is table is used to set up the relative "quality" of each
88 * MARC source, such as OCLC.
92 * This program is free software; you can redistribute it and/or
93 * modify it under the terms of the GNU General Public License
94 * as published by the Free Software Foundation; either version 2
95 * of the License, or (at your option) any later version.
97 * This program is distributed in the hope that it will be useful,
98 * but WITHOUT ANY WARRANTY; without even the implied warranty of
99 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
100 * GNU General Public License for more details.
104 CREATE TABLE config.standing (
105 id SERIAL PRIMARY KEY,
106 value TEXT NOT NULL UNIQUE
108 COMMENT ON TABLE config.standing IS $$
110 * Copyright (C) 2005 Georgia Public Library Service
111 * Mike Rylander <mrylander@gmail.com>
115 * This table contains the values that can be applied to a patron
116 * by a staff member. These values should not be changed, other
117 * than for translation, as the ID column is currently a "magic
118 * number" in the source. :(
122 * This program is free software; you can redistribute it and/or
123 * modify it under the terms of the GNU General Public License
124 * as published by the Free Software Foundation; either version 2
125 * of the License, or (at your option) any later version.
127 * This program is distributed in the hope that it will be useful,
128 * but WITHOUT ANY WARRANTY; without even the implied warranty of
129 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
130 * GNU General Public License for more details.
134 CREATE TABLE config.standing_penalty (
135 id SERIAL PRIMARY KEY,
136 name TEXT NOT NULL UNIQUE,
141 INSERT INTO config.standing_penalty (id,name,label,block_list)
142 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
143 INSERT INTO config.standing_penalty (id,name,label,block_list)
144 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
145 INSERT INTO config.standing_penalty (id,name,label,block_list)
146 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
147 INSERT INTO config.standing_penalty (id,name,label,block_list)
148 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
150 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
151 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
152 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
153 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
154 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
155 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
156 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
157 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
158 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
159 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
160 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
162 SELECT SETVAL('config.standing_penalty_id_seq', 100);
164 CREATE TABLE config.xml_transform (
165 name TEXT PRIMARY KEY,
166 namespace_uri TEXT NOT NULL,
167 prefix TEXT NOT NULL,
171 CREATE TABLE config.biblio_fingerprint (
172 id SERIAL PRIMARY KEY,
175 first_word BOOL NOT NULL DEFAULT FALSE,
176 format TEXT NOT NULL DEFAULT 'marcxml'
179 INSERT INTO config.biblio_fingerprint (name, xpath, format)
182 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
183 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
184 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
185 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
186 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
190 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
193 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
194 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
195 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
196 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
197 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
202 CREATE TABLE config.metabib_class (
203 name TEXT PRIMARY KEY,
204 label TEXT NOT NULL UNIQUE
207 CREATE TABLE config.metabib_field (
208 id SERIAL PRIMARY KEY,
209 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
213 weight INT NOT NULL DEFAULT 1,
214 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
215 search_field BOOL NOT NULL DEFAULT TRUE,
216 facet_field BOOL NOT NULL DEFAULT FALSE,
219 COMMENT ON TABLE config.metabib_field IS $$
221 * Copyright (C) 2005 Georgia Public Library Service
222 * Mike Rylander <mrylander@gmail.com>
224 * XPath used for record indexing ingest
226 * This table contains the XPath used to chop up MODS into its
227 * indexable parts. Each XPath entry is named and assigned to
228 * a "class" of either title, subject, author, keyword or series.
233 * This program is free software; you can redistribute it and/or
234 * modify it under the terms of the GNU General Public License
235 * as published by the Free Software Foundation; either version 2
236 * of the License, or (at your option) any later version.
238 * This program is distributed in the hope that it will be useful,
239 * but WITHOUT ANY WARRANTY; without even the implied warranty of
240 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
241 * GNU General Public License for more details.
245 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
247 CREATE TABLE config.metabib_search_alias (
248 alias TEXT PRIMARY KEY,
249 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
250 field INT REFERENCES config.metabib_field (id)
253 CREATE TABLE config.non_cataloged_type (
254 id SERIAL PRIMARY KEY,
255 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
257 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
258 in_house BOOL NOT NULL DEFAULT FALSE,
259 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
261 COMMENT ON TABLE config.non_cataloged_type IS $$
263 * Copyright (C) 2005 Georgia Public Library Service
264 * Mike Rylander <mrylander@gmail.com>
266 * Types of valid non-cataloged items.
271 * This program is free software; you can redistribute it and/or
272 * modify it under the terms of the GNU General Public License
273 * as published by the Free Software Foundation; either version 2
274 * of the License, or (at your option) any later version.
276 * This program is distributed in the hope that it will be useful,
277 * but WITHOUT ANY WARRANTY; without even the implied warranty of
278 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
279 * GNU General Public License for more details.
283 CREATE TABLE config.identification_type (
284 id SERIAL PRIMARY KEY,
285 name TEXT NOT NULL UNIQUE
287 COMMENT ON TABLE config.identification_type IS $$
289 * Copyright (C) 2005 Georgia Public Library Service
290 * Mike Rylander <mrylander@gmail.com>
292 * Types of valid patron identification.
294 * Each patron must display at least one valid form of identification
295 * in order to get a library card. This table lists those forms.
300 * This program is free software; you can redistribute it and/or
301 * modify it under the terms of the GNU General Public License
302 * as published by the Free Software Foundation; either version 2
303 * of the License, or (at your option) any later version.
305 * This program is distributed in the hope that it will be useful,
306 * but WITHOUT ANY WARRANTY; without even the implied warranty of
307 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
308 * GNU General Public License for more details.
312 CREATE TABLE config.rule_circ_duration (
313 id SERIAL PRIMARY KEY,
314 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
315 extended INTERVAL NOT NULL,
316 normal INTERVAL NOT NULL,
317 shrt INTERVAL NOT NULL,
318 max_renewals INT NOT NULL
320 COMMENT ON TABLE config.rule_circ_duration IS $$
322 * Copyright (C) 2005 Georgia Public Library Service
323 * Mike Rylander <mrylander@gmail.com>
325 * Circulation Duration rules
327 * Each circulation is given a duration based on one of these rules.
332 * This program is free software; you can redistribute it and/or
333 * modify it under the terms of the GNU General Public License
334 * as published by the Free Software Foundation; either version 2
335 * of the License, or (at your option) any later version.
337 * This program is distributed in the hope that it will be useful,
338 * but WITHOUT ANY WARRANTY; without even the implied warranty of
339 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
340 * GNU General Public License for more details.
344 CREATE TABLE config.rule_max_fine (
345 id SERIAL PRIMARY KEY,
346 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
347 amount NUMERIC(6,2) NOT NULL,
348 is_percent BOOL NOT NULL DEFAULT FALSE
350 COMMENT ON TABLE config.rule_max_fine IS $$
352 * Copyright (C) 2005 Georgia Public Library Service
353 * Mike Rylander <mrylander@gmail.com>
355 * Circulation Max Fine rules
357 * Each circulation is given a maximum fine based on one of
363 * This program is free software; you can redistribute it and/or
364 * modify it under the terms of the GNU General Public License
365 * as published by the Free Software Foundation; either version 2
366 * of the License, or (at your option) any later version.
368 * This program is distributed in the hope that it will be useful,
369 * but WITHOUT ANY WARRANTY; without even the implied warranty of
370 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
371 * GNU General Public License for more details.
375 CREATE TABLE config.rule_recurring_fine (
376 id SERIAL PRIMARY KEY,
377 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
378 high NUMERIC(6,2) NOT NULL,
379 normal NUMERIC(6,2) NOT NULL,
380 low NUMERIC(6,2) NOT NULL,
381 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
383 COMMENT ON TABLE config.rule_recurring_fine IS $$
385 * Copyright (C) 2005 Georgia Public Library Service
386 * Mike Rylander <mrylander@gmail.com>
388 * Circulation Recurring Fine rules
390 * Each circulation is given a recurring fine amount based on one of
391 * these rules. The recurrence_interval should not be any shorter
392 * than the interval between runs of the fine_processor.pl script
393 * (which is run from CRON), or you could miss fines.
398 * This program is free software; you can redistribute it and/or
399 * modify it under the terms of the GNU General Public License
400 * as published by the Free Software Foundation; either version 2
401 * of the License, or (at your option) any later version.
403 * This program is distributed in the hope that it will be useful,
404 * but WITHOUT ANY WARRANTY; without even the implied warranty of
405 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
406 * GNU General Public License for more details.
411 CREATE TABLE config.rule_age_hold_protect (
412 id SERIAL PRIMARY KEY,
413 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
414 age INTERVAL NOT NULL,
417 COMMENT ON TABLE config.rule_age_hold_protect IS $$
419 * Copyright (C) 2005 Georgia Public Library Service
420 * Mike Rylander <mrylander@gmail.com>
422 * Hold Item Age Protection rules
424 * A hold request can only capture new(ish) items when they are
425 * within a particular proximity of the home_ou of the requesting
426 * user. The proximity ('prox' column) is calculated by counting
427 * the number of tree edges between the user's home_ou and the owning_lib
428 * of the copy that could fulfill the hold.
433 * This program is free software; you can redistribute it and/or
434 * modify it under the terms of the GNU General Public License
435 * as published by the Free Software Foundation; either version 2
436 * of the License, or (at your option) any later version.
438 * This program is distributed in the hope that it will be useful,
439 * but WITHOUT ANY WARRANTY; without even the implied warranty of
440 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
441 * GNU General Public License for more details.
445 CREATE TABLE config.copy_status (
446 id SERIAL PRIMARY KEY,
447 name TEXT NOT NULL UNIQUE,
448 holdable BOOL NOT NULL DEFAULT FALSE,
449 opac_visible BOOL NOT NULL DEFAULT FALSE
451 COMMENT ON TABLE config.copy_status IS $$
453 * Copyright (C) 2005 Georgia Public Library Service
454 * Mike Rylander <mrylander@gmail.com>
458 * The available copy statuses, and whether a copy in that
459 * status is available for hold request capture. 0 (zero) is
460 * the only special number in this set, meaning that the item
461 * is available for immediate checkout, and is counted as available
464 * Statuses with an ID below 100 are not removable, and have special
465 * meaning in the code. Do not change them except to translate the
468 * You may add and remove statuses above 100, and these can be used
469 * to remove items from normal circulation without affecting the rest
470 * of the copy's values or its location.
474 * This program is free software; you can redistribute it and/or
475 * modify it under the terms of the GNU General Public License
476 * as published by the Free Software Foundation; either version 2
477 * of the License, or (at your option) any later version.
479 * This program is distributed in the hope that it will be useful,
480 * but WITHOUT ANY WARRANTY; without even the implied warranty of
481 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
482 * GNU General Public License for more details.
486 CREATE TABLE config.net_access_level (
487 id SERIAL PRIMARY KEY,
488 name TEXT NOT NULL UNIQUE
490 COMMENT ON TABLE config.net_access_level IS $$
492 * Copyright (C) 2005 Georgia Public Library Service
493 * Mike Rylander <mrylander@gmail.com>
495 * Patron Network Access level
497 * This will be used to inform the in-library firewall of how much
498 * internet access the using patron should be allowed.
502 * This program is free software; you can redistribute it and/or
503 * modify it under the terms of the GNU General Public License
504 * as published by the Free Software Foundation; either version 2
505 * of the License, or (at your option) any later version.
507 * This program is distributed in the hope that it will be useful,
508 * but WITHOUT ANY WARRANTY; without even the implied warranty of
509 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
510 * GNU General Public License for more details.
515 CREATE TABLE config.remote_account (
516 id SERIAL PRIMARY KEY,
518 host TEXT NOT NULL, -- name or IP, :port optional
519 username TEXT, -- optional, since we could default to $USER
520 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
521 account TEXT, -- aka profile or FTP "account" command
522 path TEXT, -- aka directory
523 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
524 last_activity TIMESTAMP WITH TIME ZONE
527 CREATE TABLE config.audience_map (
528 code TEXT PRIMARY KEY,
533 CREATE TABLE config.lit_form_map (
534 code TEXT PRIMARY KEY,
539 CREATE TABLE config.language_map (
540 code TEXT PRIMARY KEY,
544 CREATE TABLE config.item_form_map (
545 code TEXT PRIMARY KEY,
549 CREATE TABLE config.item_type_map (
550 code TEXT PRIMARY KEY,
554 CREATE TABLE config.bib_level_map (
555 code TEXT PRIMARY KEY,
559 CREATE TABLE config.marc21_rec_type_map (
560 code TEXT PRIMARY KEY,
561 type_val TEXT NOT NULL,
562 blvl_val TEXT NOT NULL
565 CREATE TABLE config.marc21_ff_pos_map (
566 id SERIAL PRIMARY KEY,
567 fixed_field TEXT NOT NULL,
569 rec_type TEXT NOT NULL,
570 start_pos INT NOT NULL,
572 default_val TEXT NOT NULL DEFAULT ' '
575 CREATE TABLE config.marc21_physical_characteristic_type_map (
576 ptype_key TEXT PRIMARY KEY,
577 label TEXT NOT NULL -- I18N
580 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
581 id SERIAL PRIMARY KEY,
582 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
583 subfield TEXT NOT NULL,
584 start_pos INT NOT NULL,
586 label TEXT NOT NULL -- I18N
589 CREATE TABLE config.marc21_physical_characteristic_value_map (
590 id SERIAL PRIMARY KEY,
592 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
593 label TEXT NOT NULL -- I18N
597 CREATE TABLE config.z3950_source (
598 name TEXT PRIMARY KEY,
599 label TEXT NOT NULL UNIQUE,
603 record_format TEXT NOT NULL DEFAULT 'FI',
604 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
605 auth BOOL NOT NULL DEFAULT TRUE
608 COMMENT ON TABLE config.z3950_source IS $$
611 Each row in this table represents a database searchable via Z39.50.
614 COMMENT ON COLUMN config.z3950_source.record_format IS $$
618 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
619 Z39.50 preferred record syntax..
623 CREATE TABLE config.z3950_attr (
624 id SERIAL PRIMARY KEY,
625 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
630 truncation INT NOT NULL DEFAULT 0,
631 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
634 CREATE TABLE config.i18n_locale (
635 code TEXT PRIMARY KEY,
636 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
637 name TEXT UNIQUE NOT NULL,
641 CREATE TABLE config.i18n_core (
642 id BIGSERIAL PRIMARY KEY,
643 fq_field TEXT NOT NULL,
644 identity_value TEXT NOT NULL,
645 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
649 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
651 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
655 UPDATE config.i18n_core
656 SET identity_value = $$ || quote_literal(new_ident) || $$
657 WHERE fq_field LIKE '$$ || hint || $$.%'
658 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
663 $_$ LANGUAGE PLPGSQL;
665 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
667 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
670 $_$ LANGUAGE PLPGSQL;
672 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
674 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
677 $_$ LANGUAGE PLPGSQL;
679 CREATE TABLE config.billing_type (
680 id SERIAL PRIMARY KEY,
682 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
683 default_price NUMERIC(6,2),
684 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
687 CREATE TABLE config.settings_group (
688 name TEXT PRIMARY KEY,
689 label TEXT UNIQUE NOT NULL -- I18N
692 CREATE TABLE config.org_unit_setting_type (
693 name TEXT PRIMARY KEY,
694 label TEXT UNIQUE NOT NULL,
695 grp TEXT REFERENCES config.settings_group (name),
697 datatype TEXT NOT NULL DEFAULT 'string',
702 -- define valid datatypes
704 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
705 ( 'bool', 'integer', 'float', 'currency', 'interval',
706 'date', 'string', 'object', 'array', 'link' ) ),
708 -- fm_class is meaningful only for 'link' datatype
710 CONSTRAINT coust_no_empty_link CHECK
711 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
712 ( datatype <> 'link' AND fm_class IS NULL ) )
715 CREATE TABLE config.usr_setting_type (
717 name TEXT PRIMARY KEY,
718 opac_visible BOOL NOT NULL DEFAULT FALSE,
719 label TEXT UNIQUE NOT NULL,
721 grp TEXT REFERENCES config.settings_group (name),
722 datatype TEXT NOT NULL DEFAULT 'string',
726 -- define valid datatypes
728 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
729 ( 'bool', 'integer', 'float', 'currency', 'interval',
730 'date', 'string', 'object', 'array', 'link' ) ),
733 -- fm_class is meaningful only for 'link' datatype
735 CONSTRAINT coust_no_empty_link CHECK
736 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
737 ( datatype <> 'link' AND fm_class IS NULL ) )
741 -- Some handy functions, based on existing ones, to provide optional ingest normalization
743 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
744 SELECT SUBSTRING($1,$2);
745 $func$ LANGUAGE SQL STRICT IMMUTABLE;
747 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
748 SELECT SUBSTRING($1,1,$2);
749 $func$ LANGUAGE SQL STRICT IMMUTABLE;
751 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
752 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
753 $func$ LANGUAGE SQL STRICT IMMUTABLE;
755 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
760 # For each ISBN found in a single string containing a set of ISBNs:
761 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
762 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
767 foreach my $word (split(/\s/, $input)) {
768 my $isbn = Business::ISBN->new($word);
770 # First check the checksum; if it is not valid, fix it and add the original
771 # bad-checksum ISBN to the output
772 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
773 $output .= $isbn->isbn() . " ";
774 $isbn->fix_checksum();
777 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
778 # and add the normalized original ISBN to the output
779 if ($isbn && $isbn->is_valid()) {
780 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
781 $output .= $isbn->isbn . " ";
783 # If we successfully converted the ISBN to its counterpart, add the
784 # converted ISBN to the output as well
785 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
788 return $output if $output;
790 # If there were no valid ISBNs, just return the raw input
792 $func$ LANGUAGE PLPERLU;
794 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
796 * Copyright (C) 2010 Merrimack Valley Library Consortium
797 * Jason Stephenson <jstephenson@mvlc.org>
798 * Copyright (C) 2010 Laurentian University
799 * Dan Scott <dscott@laurentian.ca>
801 * The translate_isbn1013 function takes an input ISBN and returns the
802 * following in a single space-delimited string if the input ISBN is valid:
803 * - The normalized input ISBN (hyphens stripped)
804 * - The normalized input ISBN with a fixed checksum if the checksum was bad
805 * - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
809 -- And ... a table in which to register them
811 CREATE TABLE config.index_normalizer (
812 id SERIAL PRIMARY KEY,
813 name TEXT UNIQUE NOT NULL,
816 param_count INT NOT NULL DEFAULT 0
819 CREATE TABLE config.metabib_field_index_norm_map (
820 id SERIAL PRIMARY KEY,
821 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
822 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
824 pos INT NOT NULL DEFAULT 0
827 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
835 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
837 SELECT n.func AS func,
838 n.param_count AS param_count,
840 FROM config.index_normalizer n
841 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
842 WHERE field = NEW.field AND m.pos < 0
844 EXECUTE 'SELECT ' || normalizer.func || '(' ||
845 quote_literal( value ) ||
847 WHEN normalizer.param_count > 0
848 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
858 IF NEW.index_vector = ''::tsvector THEN
862 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
864 SELECT n.func AS func,
865 n.param_count AS param_count,
867 FROM config.index_normalizer n
868 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
869 WHERE field = NEW.field AND m.pos >= 0
871 EXECUTE 'SELECT ' || normalizer.func || '(' ||
872 quote_literal( value ) ||
874 WHEN normalizer.param_count > 0
875 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
883 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
884 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
886 NEW.index_vector = to_tsvector(TG_ARGV[0], value);