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.reingest.force_on_same_marc');
56 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri');
57 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec');
58 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor');
59 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry');
61 CREATE TABLE config.global_flag (
63 ) INHERITS (config.internal_flag);
64 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
66 CREATE TABLE config.upgrade_log (
67 version TEXT PRIMARY KEY,
68 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
71 INSERT INTO config.upgrade_log (version) VALUES ('0332'); -- gmc
73 CREATE TABLE config.bib_source (
74 id SERIAL PRIMARY KEY,
75 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
76 source TEXT NOT NULL UNIQUE,
77 transcendant BOOL NOT NULL DEFAULT FALSE
79 COMMENT ON TABLE config.bib_source IS $$
81 * Copyright (C) 2005 Georgia Public Library Service
82 * Mike Rylander <mrylander@gmail.com>
84 * Valid sources of MARC records
86 * This is table is used to set up the relative "quality" of each
87 * MARC source, such as OCLC.
91 * This program is free software; you can redistribute it and/or
92 * modify it under the terms of the GNU General Public License
93 * as published by the Free Software Foundation; either version 2
94 * of the License, or (at your option) any later version.
96 * This program is distributed in the hope that it will be useful,
97 * but WITHOUT ANY WARRANTY; without even the implied warranty of
98 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
99 * GNU General Public License for more details.
103 CREATE TABLE config.standing (
104 id SERIAL PRIMARY KEY,
105 value TEXT NOT NULL UNIQUE
107 COMMENT ON TABLE config.standing IS $$
109 * Copyright (C) 2005 Georgia Public Library Service
110 * Mike Rylander <mrylander@gmail.com>
114 * This table contains the values that can be applied to a patron
115 * by a staff member. These values should not be changed, other
116 * than for translation, as the ID column is currently a "magic
117 * number" in the source. :(
121 * This program is free software; you can redistribute it and/or
122 * modify it under the terms of the GNU General Public License
123 * as published by the Free Software Foundation; either version 2
124 * of the License, or (at your option) any later version.
126 * This program is distributed in the hope that it will be useful,
127 * but WITHOUT ANY WARRANTY; without even the implied warranty of
128 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
129 * GNU General Public License for more details.
133 CREATE TABLE config.standing_penalty (
134 id SERIAL PRIMARY KEY,
135 name TEXT NOT NULL UNIQUE,
140 INSERT INTO config.standing_penalty (id,name,label,block_list)
141 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
142 INSERT INTO config.standing_penalty (id,name,label,block_list)
143 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
144 INSERT INTO config.standing_penalty (id,name,label,block_list)
145 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
146 INSERT INTO config.standing_penalty (id,name,label,block_list)
147 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
149 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
150 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
151 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
152 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
153 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
154 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
155 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
156 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
157 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
158 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
159 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
161 SELECT SETVAL('config.standing_penalty_id_seq', 100);
163 CREATE TABLE config.xml_transform (
164 name TEXT PRIMARY KEY,
165 namespace_uri TEXT NOT NULL,
166 prefix TEXT NOT NULL,
170 CREATE TABLE config.biblio_fingerprint (
171 id SERIAL PRIMARY KEY,
174 first_word BOOL NOT NULL DEFAULT FALSE,
175 format TEXT NOT NULL DEFAULT 'marcxml'
178 INSERT INTO config.biblio_fingerprint (name, xpath, format)
181 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
182 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
183 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
184 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
185 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
189 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
192 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
193 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
194 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
195 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
196 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
201 CREATE TABLE config.metabib_class (
202 name TEXT PRIMARY KEY,
203 label TEXT NOT NULL UNIQUE
206 CREATE TABLE config.metabib_field (
207 id SERIAL PRIMARY KEY,
208 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
212 weight INT NOT NULL DEFAULT 1,
213 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
214 search_field BOOL NOT NULL DEFAULT TRUE,
215 facet_field BOOL NOT NULL DEFAULT FALSE,
218 COMMENT ON TABLE config.metabib_field IS $$
220 * Copyright (C) 2005 Georgia Public Library Service
221 * Mike Rylander <mrylander@gmail.com>
223 * XPath used for record indexing ingest
225 * This table contains the XPath used to chop up MODS into its
226 * indexable parts. Each XPath entry is named and assigned to
227 * a "class" of either title, subject, author, keyword or series.
232 * This program is free software; you can redistribute it and/or
233 * modify it under the terms of the GNU General Public License
234 * as published by the Free Software Foundation; either version 2
235 * of the License, or (at your option) any later version.
237 * This program is distributed in the hope that it will be useful,
238 * but WITHOUT ANY WARRANTY; without even the implied warranty of
239 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
240 * GNU General Public License for more details.
244 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
246 CREATE TABLE config.metabib_search_alias (
247 alias TEXT PRIMARY KEY,
248 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
249 field INT REFERENCES config.metabib_field (id)
252 CREATE TABLE config.non_cataloged_type (
253 id SERIAL PRIMARY KEY,
254 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
256 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
257 in_house BOOL NOT NULL DEFAULT FALSE,
258 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
260 COMMENT ON TABLE config.non_cataloged_type IS $$
262 * Copyright (C) 2005 Georgia Public Library Service
263 * Mike Rylander <mrylander@gmail.com>
265 * Types of valid non-cataloged items.
270 * This program is free software; you can redistribute it and/or
271 * modify it under the terms of the GNU General Public License
272 * as published by the Free Software Foundation; either version 2
273 * of the License, or (at your option) any later version.
275 * This program is distributed in the hope that it will be useful,
276 * but WITHOUT ANY WARRANTY; without even the implied warranty of
277 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
278 * GNU General Public License for more details.
282 CREATE TABLE config.identification_type (
283 id SERIAL PRIMARY KEY,
284 name TEXT NOT NULL UNIQUE
286 COMMENT ON TABLE config.identification_type IS $$
288 * Copyright (C) 2005 Georgia Public Library Service
289 * Mike Rylander <mrylander@gmail.com>
291 * Types of valid patron identification.
293 * Each patron must display at least one valid form of identification
294 * in order to get a library card. This table lists those forms.
299 * This program is free software; you can redistribute it and/or
300 * modify it under the terms of the GNU General Public License
301 * as published by the Free Software Foundation; either version 2
302 * of the License, or (at your option) any later version.
304 * This program is distributed in the hope that it will be useful,
305 * but WITHOUT ANY WARRANTY; without even the implied warranty of
306 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
307 * GNU General Public License for more details.
311 CREATE TABLE config.rule_circ_duration (
312 id SERIAL PRIMARY KEY,
313 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
314 extended INTERVAL NOT NULL,
315 normal INTERVAL NOT NULL,
316 shrt INTERVAL NOT NULL,
317 max_renewals INT NOT NULL
319 COMMENT ON TABLE config.rule_circ_duration IS $$
321 * Copyright (C) 2005 Georgia Public Library Service
322 * Mike Rylander <mrylander@gmail.com>
324 * Circulation Duration rules
326 * Each circulation is given a duration based on one of these rules.
331 * This program is free software; you can redistribute it and/or
332 * modify it under the terms of the GNU General Public License
333 * as published by the Free Software Foundation; either version 2
334 * of the License, or (at your option) any later version.
336 * This program is distributed in the hope that it will be useful,
337 * but WITHOUT ANY WARRANTY; without even the implied warranty of
338 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
339 * GNU General Public License for more details.
343 CREATE TABLE config.rule_max_fine (
344 id SERIAL PRIMARY KEY,
345 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
346 amount NUMERIC(6,2) NOT NULL,
347 is_percent BOOL NOT NULL DEFAULT FALSE
349 COMMENT ON TABLE config.rule_max_fine IS $$
351 * Copyright (C) 2005 Georgia Public Library Service
352 * Mike Rylander <mrylander@gmail.com>
354 * Circulation Max Fine rules
356 * Each circulation is given a maximum fine based on one of
362 * This program is free software; you can redistribute it and/or
363 * modify it under the terms of the GNU General Public License
364 * as published by the Free Software Foundation; either version 2
365 * of the License, or (at your option) any later version.
367 * This program is distributed in the hope that it will be useful,
368 * but WITHOUT ANY WARRANTY; without even the implied warranty of
369 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
370 * GNU General Public License for more details.
374 CREATE TABLE config.rule_recurring_fine (
375 id SERIAL PRIMARY KEY,
376 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
377 high NUMERIC(6,2) NOT NULL,
378 normal NUMERIC(6,2) NOT NULL,
379 low NUMERIC(6,2) NOT NULL,
380 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
382 COMMENT ON TABLE config.rule_recurring_fine IS $$
384 * Copyright (C) 2005 Georgia Public Library Service
385 * Mike Rylander <mrylander@gmail.com>
387 * Circulation Recurring Fine rules
389 * Each circulation is given a recurring fine amount based on one of
390 * these rules. The recurrence_interval should not be any shorter
391 * than the interval between runs of the fine_processor.pl script
392 * (which is run from CRON), or you could miss fines.
397 * This program is free software; you can redistribute it and/or
398 * modify it under the terms of the GNU General Public License
399 * as published by the Free Software Foundation; either version 2
400 * of the License, or (at your option) any later version.
402 * This program is distributed in the hope that it will be useful,
403 * but WITHOUT ANY WARRANTY; without even the implied warranty of
404 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
405 * GNU General Public License for more details.
410 CREATE TABLE config.rule_age_hold_protect (
411 id SERIAL PRIMARY KEY,
412 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
413 age INTERVAL NOT NULL,
416 COMMENT ON TABLE config.rule_age_hold_protect IS $$
418 * Copyright (C) 2005 Georgia Public Library Service
419 * Mike Rylander <mrylander@gmail.com>
421 * Hold Item Age Protection rules
423 * A hold request can only capture new(ish) items when they are
424 * within a particular proximity of the home_ou of the requesting
425 * user. The proximity ('prox' column) is calculated by counting
426 * the number of tree edges between the user's home_ou and the owning_lib
427 * of the copy that could fulfill the hold.
432 * This program is free software; you can redistribute it and/or
433 * modify it under the terms of the GNU General Public License
434 * as published by the Free Software Foundation; either version 2
435 * of the License, or (at your option) any later version.
437 * This program is distributed in the hope that it will be useful,
438 * but WITHOUT ANY WARRANTY; without even the implied warranty of
439 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
440 * GNU General Public License for more details.
444 CREATE TABLE config.copy_status (
445 id SERIAL PRIMARY KEY,
446 name TEXT NOT NULL UNIQUE,
447 holdable BOOL NOT NULL DEFAULT FALSE,
448 opac_visible BOOL NOT NULL DEFAULT FALSE
450 COMMENT ON TABLE config.copy_status IS $$
452 * Copyright (C) 2005 Georgia Public Library Service
453 * Mike Rylander <mrylander@gmail.com>
457 * The available copy statuses, and whether a copy in that
458 * status is available for hold request capture. 0 (zero) is
459 * the only special number in this set, meaning that the item
460 * is available for immediate checkout, and is counted as available
463 * Statuses with an ID below 100 are not removable, and have special
464 * meaning in the code. Do not change them except to translate the
467 * You may add and remove statuses above 100, and these can be used
468 * to remove items from normal circulation without affecting the rest
469 * of the copy's values or its location.
473 * This program is free software; you can redistribute it and/or
474 * modify it under the terms of the GNU General Public License
475 * as published by the Free Software Foundation; either version 2
476 * of the License, or (at your option) any later version.
478 * This program is distributed in the hope that it will be useful,
479 * but WITHOUT ANY WARRANTY; without even the implied warranty of
480 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
481 * GNU General Public License for more details.
485 CREATE TABLE config.net_access_level (
486 id SERIAL PRIMARY KEY,
487 name TEXT NOT NULL UNIQUE
489 COMMENT ON TABLE config.net_access_level IS $$
491 * Copyright (C) 2005 Georgia Public Library Service
492 * Mike Rylander <mrylander@gmail.com>
494 * Patron Network Access level
496 * This will be used to inform the in-library firewall of how much
497 * internet access the using patron should be allowed.
501 * This program is free software; you can redistribute it and/or
502 * modify it under the terms of the GNU General Public License
503 * as published by the Free Software Foundation; either version 2
504 * of the License, or (at your option) any later version.
506 * This program is distributed in the hope that it will be useful,
507 * but WITHOUT ANY WARRANTY; without even the implied warranty of
508 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
509 * GNU General Public License for more details.
514 CREATE TABLE config.remote_account (
515 id SERIAL PRIMARY KEY,
517 host TEXT NOT NULL, -- name or IP, :port optional
518 username TEXT, -- optional, since we could default to $USER
519 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
520 account TEXT, -- aka profile or FTP "account" command
521 path TEXT, -- aka directory
522 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
523 last_activity TIMESTAMP WITH TIME ZONE
526 CREATE TABLE config.audience_map (
527 code TEXT PRIMARY KEY,
532 CREATE TABLE config.lit_form_map (
533 code TEXT PRIMARY KEY,
538 CREATE TABLE config.language_map (
539 code TEXT PRIMARY KEY,
543 CREATE TABLE config.item_form_map (
544 code TEXT PRIMARY KEY,
548 CREATE TABLE config.item_type_map (
549 code TEXT PRIMARY KEY,
553 CREATE TABLE config.bib_level_map (
554 code TEXT PRIMARY KEY,
558 CREATE TABLE config.marc21_rec_type_map (
559 code TEXT PRIMARY KEY,
560 type_val TEXT NOT NULL,
561 blvl_val TEXT NOT NULL
564 CREATE TABLE config.marc21_ff_pos_map (
565 id SERIAL PRIMARY KEY,
566 fixed_field TEXT NOT NULL,
568 rec_type TEXT NOT NULL,
569 start_pos INT NOT NULL,
571 default_val TEXT NOT NULL DEFAULT ' '
574 CREATE TABLE config.marc21_physical_characteristic_type_map (
575 ptype_key TEXT PRIMARY KEY,
576 label TEXT NOT NULL -- I18N
579 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
580 id SERIAL PRIMARY KEY,
581 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
582 subfield TEXT NOT NULL,
583 start_pos INT NOT NULL,
585 label TEXT NOT NULL -- I18N
588 CREATE TABLE config.marc21_physical_characteristic_value_map (
589 id SERIAL PRIMARY KEY,
591 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
592 label TEXT NOT NULL -- I18N
596 CREATE TABLE config.z3950_source (
597 name TEXT PRIMARY KEY,
598 label TEXT NOT NULL UNIQUE,
602 record_format TEXT NOT NULL DEFAULT 'FI',
603 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
604 auth BOOL NOT NULL DEFAULT TRUE
607 COMMENT ON TABLE config.z3950_source IS $$
610 Each row in this table represents a database searchable via Z39.50.
613 COMMENT ON COLUMN config.z3950_source.record_format IS $$
617 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
618 Z39.50 preferred record syntax..
622 CREATE TABLE config.z3950_attr (
623 id SERIAL PRIMARY KEY,
624 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
629 truncation INT NOT NULL DEFAULT 0,
630 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
633 CREATE TABLE config.i18n_locale (
634 code TEXT PRIMARY KEY,
635 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
636 name TEXT UNIQUE NOT NULL,
640 CREATE TABLE config.i18n_core (
641 id BIGSERIAL PRIMARY KEY,
642 fq_field TEXT NOT NULL,
643 identity_value TEXT NOT NULL,
644 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
648 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
650 CREATE TABLE config.billing_type (
651 id SERIAL PRIMARY KEY,
653 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
654 default_price NUMERIC(6,2),
655 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
658 CREATE TABLE config.settings_group (
659 name TEXT PRIMARY KEY,
660 label TEXT UNIQUE NOT NULL -- I18N
663 CREATE TABLE config.org_unit_setting_type (
664 name TEXT PRIMARY KEY,
665 label TEXT UNIQUE NOT NULL,
666 grp TEXT REFERENCES config.settings_group (name),
668 datatype TEXT NOT NULL DEFAULT 'string',
673 -- define valid datatypes
675 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
676 ( 'bool', 'integer', 'float', 'currency', 'interval',
677 'date', 'string', 'object', 'array', 'link' ) ),
679 -- fm_class is meaningful only for 'link' datatype
681 CONSTRAINT coust_no_empty_link CHECK
682 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
683 ( datatype <> 'link' AND fm_class IS NULL ) )
686 CREATE TABLE config.usr_setting_type (
688 name TEXT PRIMARY KEY,
689 opac_visible BOOL NOT NULL DEFAULT FALSE,
690 label TEXT UNIQUE NOT NULL,
692 grp TEXT REFERENCES config.settings_group (name),
693 datatype TEXT NOT NULL DEFAULT 'string',
697 -- define valid datatypes
699 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
700 ( 'bool', 'integer', 'float', 'currency', 'interval',
701 'date', 'string', 'object', 'array', 'link' ) ),
704 -- fm_class is meaningful only for 'link' datatype
706 CONSTRAINT coust_no_empty_link CHECK
707 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
708 ( datatype <> 'link' AND fm_class IS NULL ) )
712 -- Some handy functions, based on existing ones, to provide optional ingest normalization
714 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
715 SELECT SUBSTRING($1,$2);
716 $func$ LANGUAGE SQL STRICT IMMUTABLE;
718 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
719 SELECT SUBSTRING($1,1,$2);
720 $func$ LANGUAGE SQL STRICT IMMUTABLE;
722 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
723 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
724 $func$ LANGUAGE SQL STRICT IMMUTABLE;
726 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
727 SELECT isbn FROM (SELECT isn_weak(true), $1 || ' ' || REPLACE( CASE WHEN length($1) = 10 THEN isbn13($1)::TEXT WHEN length($1) = 13 THEN isbn($1)::TEXT ELSE '' END, '-', '') AS isbn)x;
728 $func$ LANGUAGE SQL STRICT IMMUTABLE;
730 -- And ... a table in which to register them
732 CREATE TABLE config.index_normalizer (
733 id SERIAL PRIMARY KEY,
734 name TEXT UNIQUE NOT NULL,
737 param_count INT NOT NULL DEFAULT 0
740 CREATE TABLE config.metabib_field_index_norm_map (
741 id SERIAL PRIMARY KEY,
742 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
743 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
745 pos INT NOT NULL DEFAULT 0
748 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
756 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
758 SELECT n.func AS func,
759 n.param_count AS param_count,
761 FROM config.index_normalizer n
762 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
763 WHERE field = NEW.field AND m.pos < 0
765 EXECUTE 'SELECT ' || normalizer.func || '(' ||
766 quote_literal( value ) ||
768 WHEN normalizer.param_count > 0
769 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
779 IF NEW.index_vector = ''::tsvector THEN
783 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
785 SELECT n.func AS func,
786 n.param_count AS param_count,
788 FROM config.index_normalizer n
789 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
790 WHERE field = NEW.field AND m.pos >= 0
792 EXECUTE 'SELECT ' || normalizer.func || '(' ||
793 quote_literal( value ) ||
795 WHEN normalizer.param_count > 0
796 THEN ',' || REPLACE(REPLACE(BTRIM(normalizer.params,'[]'),E'\'',E'\\\''),E'"',E'\'')
804 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
805 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
807 NEW.index_vector = to_tsvector(TG_ARGV[0], value);