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 stats CASCADE;
21 DROP SCHEMA 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.reingest.skip_located_uri');
58 CREATE TABLE config.global_flag (
60 ) INHERITS (config.internal_flag);
61 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
63 CREATE TABLE config.upgrade_log (
64 version TEXT PRIMARY KEY,
65 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
68 INSERT INTO config.upgrade_log (version) VALUES ('0256'); -- Scott McKellar
70 CREATE TABLE config.bib_source (
71 id SERIAL PRIMARY KEY,
72 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
73 source TEXT NOT NULL UNIQUE,
74 transcendant BOOL NOT NULL DEFAULT FALSE
76 COMMENT ON TABLE config.bib_source IS $$
78 * Copyright (C) 2005 Georgia Public Library Service
79 * Mike Rylander <mrylander@gmail.com>
81 * Valid sources of MARC records
83 * This is table is used to set up the relative "quality" of each
84 * MARC source, such as OCLC.
88 * This program is free software; you can redistribute it and/or
89 * modify it under the terms of the GNU General Public License
90 * as published by the Free Software Foundation; either version 2
91 * of the License, or (at your option) any later version.
93 * This program is distributed in the hope that it will be useful,
94 * but WITHOUT ANY WARRANTY; without even the implied warranty of
95 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
96 * GNU General Public License for more details.
100 CREATE TABLE config.standing (
101 id SERIAL PRIMARY KEY,
102 value TEXT NOT NULL UNIQUE
104 COMMENT ON TABLE config.standing IS $$
106 * Copyright (C) 2005 Georgia Public Library Service
107 * Mike Rylander <mrylander@gmail.com>
111 * This table contains the values that can be applied to a patron
112 * by a staff member. These values should not be changed, other
113 * than for translation, as the ID column is currently a "magic
114 * number" in the source. :(
118 * This program is free software; you can redistribute it and/or
119 * modify it under the terms of the GNU General Public License
120 * as published by the Free Software Foundation; either version 2
121 * of the License, or (at your option) any later version.
123 * This program is distributed in the hope that it will be useful,
124 * but WITHOUT ANY WARRANTY; without even the implied warranty of
125 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
126 * GNU General Public License for more details.
130 CREATE TABLE config.standing_penalty (
131 id SERIAL PRIMARY KEY,
132 name TEXT NOT NULL UNIQUE,
137 INSERT INTO config.standing_penalty (id,name,label,block_list)
138 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
139 INSERT INTO config.standing_penalty (id,name,label,block_list)
140 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
141 INSERT INTO config.standing_penalty (id,name,label,block_list)
142 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
143 INSERT INTO config.standing_penalty (id,name,label,block_list)
144 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
146 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
147 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
148 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
149 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
150 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
151 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
152 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
153 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
154 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
155 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
156 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
158 SELECT SETVAL('config.standing_penalty_id_seq', 100);
160 CREATE TABLE config.xml_transform (
161 name TEXT PRIMARY KEY,
162 namespace_uri TEXT NOT NULL,
163 prefix TEXT NOT NULL,
167 CREATE TABLE config.biblio_fingerprint (
168 id SERIAL PRIMARY KEY,
171 first_word BOOL NOT NULL DEFAULT FALSE,
172 format TEXT NOT NULL DEFAULT 'marcxml'
175 INSERT INTO config.biblio_fingerprint (name, xpath, format)
178 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
179 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
180 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
181 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
182 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
186 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
189 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
190 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
191 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
192 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
193 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
198 CREATE TABLE config.metabib_class (
199 name TEXT PRIMARY KEY,
200 label TEXT NOT NULL UNIQUE
203 CREATE TABLE config.metabib_field (
204 id SERIAL PRIMARY KEY,
205 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
209 weight INT NOT NULL DEFAULT 1,
210 format TEXT NOT NULL DEFAULT 'mods33',
211 search_field BOOL NOT NULL DEFAULT TRUE,
212 facet_field BOOL NOT NULL DEFAULT FALSE,
215 COMMENT ON TABLE config.metabib_field IS $$
217 * Copyright (C) 2005 Georgia Public Library Service
218 * Mike Rylander <mrylander@gmail.com>
220 * XPath used for record indexing ingest
222 * This table contains the XPath used to chop up MODS into its
223 * indexable parts. Each XPath entry is named and assigned to
224 * a "class" of either title, subject, author, keyword or series.
229 * This program is free software; you can redistribute it and/or
230 * modify it under the terms of the GNU General Public License
231 * as published by the Free Software Foundation; either version 2
232 * of the License, or (at your option) any later version.
234 * This program is distributed in the hope that it will be useful,
235 * but WITHOUT ANY WARRANTY; without even the implied warranty of
236 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
237 * GNU General Public License for more details.
241 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
243 CREATE TABLE config.metabib_search_alias (
244 alias TEXT PRIMARY KEY,
245 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
246 field INT REFERENCES config.metabib_field (id)
249 CREATE TABLE config.non_cataloged_type (
250 id SERIAL PRIMARY KEY,
251 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
253 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
254 in_house BOOL NOT NULL DEFAULT FALSE,
255 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
257 COMMENT ON TABLE config.non_cataloged_type IS $$
259 * Copyright (C) 2005 Georgia Public Library Service
260 * Mike Rylander <mrylander@gmail.com>
262 * Types of valid non-cataloged items.
267 * This program is free software; you can redistribute it and/or
268 * modify it under the terms of the GNU General Public License
269 * as published by the Free Software Foundation; either version 2
270 * of the License, or (at your option) any later version.
272 * This program is distributed in the hope that it will be useful,
273 * but WITHOUT ANY WARRANTY; without even the implied warranty of
274 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
275 * GNU General Public License for more details.
279 CREATE TABLE config.identification_type (
280 id SERIAL PRIMARY KEY,
281 name TEXT NOT NULL UNIQUE
283 COMMENT ON TABLE config.identification_type IS $$
285 * Copyright (C) 2005 Georgia Public Library Service
286 * Mike Rylander <mrylander@gmail.com>
288 * Types of valid patron identification.
290 * Each patron must display at least one valid form of identification
291 * in order to get a library card. This table lists those forms.
296 * This program is free software; you can redistribute it and/or
297 * modify it under the terms of the GNU General Public License
298 * as published by the Free Software Foundation; either version 2
299 * of the License, or (at your option) any later version.
301 * This program is distributed in the hope that it will be useful,
302 * but WITHOUT ANY WARRANTY; without even the implied warranty of
303 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
304 * GNU General Public License for more details.
308 CREATE TABLE config.rule_circ_duration (
309 id SERIAL PRIMARY KEY,
310 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
311 extended INTERVAL NOT NULL,
312 normal INTERVAL NOT NULL,
313 shrt INTERVAL NOT NULL,
314 max_renewals INT NOT NULL
316 COMMENT ON TABLE config.rule_circ_duration IS $$
318 * Copyright (C) 2005 Georgia Public Library Service
319 * Mike Rylander <mrylander@gmail.com>
321 * Circulation Duration rules
323 * Each circulation is given a duration based on one of these rules.
328 * This program is free software; you can redistribute it and/or
329 * modify it under the terms of the GNU General Public License
330 * as published by the Free Software Foundation; either version 2
331 * of the License, or (at your option) any later version.
333 * This program is distributed in the hope that it will be useful,
334 * but WITHOUT ANY WARRANTY; without even the implied warranty of
335 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
336 * GNU General Public License for more details.
340 CREATE TABLE config.rule_max_fine (
341 id SERIAL PRIMARY KEY,
342 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
343 amount NUMERIC(6,2) NOT NULL,
344 is_percent BOOL NOT NULL DEFAULT FALSE
346 COMMENT ON TABLE config.rule_max_fine IS $$
348 * Copyright (C) 2005 Georgia Public Library Service
349 * Mike Rylander <mrylander@gmail.com>
351 * Circulation Max Fine rules
353 * Each circulation is given a maximum fine based on one of
359 * This program is free software; you can redistribute it and/or
360 * modify it under the terms of the GNU General Public License
361 * as published by the Free Software Foundation; either version 2
362 * of the License, or (at your option) any later version.
364 * This program is distributed in the hope that it will be useful,
365 * but WITHOUT ANY WARRANTY; without even the implied warranty of
366 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
367 * GNU General Public License for more details.
371 CREATE TABLE config.rule_recurring_fine (
372 id SERIAL PRIMARY KEY,
373 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
374 high NUMERIC(6,2) NOT NULL,
375 normal NUMERIC(6,2) NOT NULL,
376 low NUMERIC(6,2) NOT NULL,
377 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
379 COMMENT ON TABLE config.rule_recurring_fine IS $$
381 * Copyright (C) 2005 Georgia Public Library Service
382 * Mike Rylander <mrylander@gmail.com>
384 * Circulation Recurring Fine rules
386 * Each circulation is given a recurring fine amount based on one of
387 * these rules. The recurrence_interval should not be any shorter
388 * than the interval between runs of the fine_processor.pl script
389 * (which is run from CRON), or you could miss fines.
394 * This program is free software; you can redistribute it and/or
395 * modify it under the terms of the GNU General Public License
396 * as published by the Free Software Foundation; either version 2
397 * of the License, or (at your option) any later version.
399 * This program is distributed in the hope that it will be useful,
400 * but WITHOUT ANY WARRANTY; without even the implied warranty of
401 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
402 * GNU General Public License for more details.
407 CREATE TABLE config.rule_age_hold_protect (
408 id SERIAL PRIMARY KEY,
409 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
410 age INTERVAL NOT NULL,
413 COMMENT ON TABLE config.rule_age_hold_protect IS $$
415 * Copyright (C) 2005 Georgia Public Library Service
416 * Mike Rylander <mrylander@gmail.com>
418 * Hold Item Age Protection rules
420 * A hold request can only capture new(ish) items when they are
421 * within a particular proximity of the home_ou of the requesting
422 * user. The proximity ('prox' column) is calculated by counting
423 * the number of tree edges between the user's home_ou and the owning_lib
424 * of the copy that could fulfill the hold.
429 * This program is free software; you can redistribute it and/or
430 * modify it under the terms of the GNU General Public License
431 * as published by the Free Software Foundation; either version 2
432 * of the License, or (at your option) any later version.
434 * This program is distributed in the hope that it will be useful,
435 * but WITHOUT ANY WARRANTY; without even the implied warranty of
436 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
437 * GNU General Public License for more details.
441 CREATE TABLE config.copy_status (
442 id SERIAL PRIMARY KEY,
443 name TEXT NOT NULL UNIQUE,
444 holdable BOOL NOT NULL DEFAULT FALSE,
445 opac_visible BOOL NOT NULL DEFAULT FALSE
447 COMMENT ON TABLE config.copy_status IS $$
449 * Copyright (C) 2005 Georgia Public Library Service
450 * Mike Rylander <mrylander@gmail.com>
454 * The available copy statuses, and whether a copy in that
455 * status is available for hold request capture. 0 (zero) is
456 * the only special number in this set, meaning that the item
457 * is available for immediate checkout, and is counted as available
460 * Statuses with an ID below 100 are not removable, and have special
461 * meaning in the code. Do not change them except to translate the
464 * You may add and remove statuses above 100, and these can be used
465 * to remove items from normal circulation without affecting the rest
466 * of the copy's values or its location.
470 * This program is free software; you can redistribute it and/or
471 * modify it under the terms of the GNU General Public License
472 * as published by the Free Software Foundation; either version 2
473 * of the License, or (at your option) any later version.
475 * This program is distributed in the hope that it will be useful,
476 * but WITHOUT ANY WARRANTY; without even the implied warranty of
477 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
478 * GNU General Public License for more details.
482 CREATE TABLE config.net_access_level (
483 id SERIAL PRIMARY KEY,
484 name TEXT NOT NULL UNIQUE
486 COMMENT ON TABLE config.net_access_level IS $$
488 * Copyright (C) 2005 Georgia Public Library Service
489 * Mike Rylander <mrylander@gmail.com>
491 * Patron Network Access level
493 * This will be used to inform the in-library firewall of how much
494 * internet access the using patron should be allowed.
498 * This program is free software; you can redistribute it and/or
499 * modify it under the terms of the GNU General Public License
500 * as published by the Free Software Foundation; either version 2
501 * of the License, or (at your option) any later version.
503 * This program is distributed in the hope that it will be useful,
504 * but WITHOUT ANY WARRANTY; without even the implied warranty of
505 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
506 * GNU General Public License for more details.
511 CREATE TABLE config.remote_account (
512 id SERIAL PRIMARY KEY,
514 host TEXT NOT NULL, -- name or IP, :port optional
515 username TEXT, -- optional, since we could default to $USER
516 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
517 account TEXT, -- aka profile or FTP "account" command
518 path TEXT, -- aka directory
519 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
520 last_activity TIMESTAMP WITH TIME ZONE
523 CREATE TABLE config.audience_map (
524 code TEXT PRIMARY KEY,
529 CREATE TABLE config.lit_form_map (
530 code TEXT PRIMARY KEY,
535 CREATE TABLE config.language_map (
536 code TEXT PRIMARY KEY,
540 CREATE TABLE config.item_form_map (
541 code TEXT PRIMARY KEY,
545 CREATE TABLE config.item_type_map (
546 code TEXT PRIMARY KEY,
550 CREATE TABLE config.bib_level_map (
551 code TEXT PRIMARY KEY,
555 CREATE TABLE config.marc21_rec_type_map (
556 code TEXT PRIMARY KEY,
557 type_val TEXT NOT NULL,
558 blvl_val TEXT NOT NULL
561 CREATE TABLE config.marc21_ff_pos_map (
562 id SERIAL PRIMARY KEY,
563 fixed_field TEXT NOT NULL,
565 rec_type TEXT NOT NULL,
566 start_pos INT NOT NULL,
568 default_val TEXT NOT NULL DEFAULT ' '
571 CREATE TABLE config.marc21_physical_characteristic_type_map (
572 ptype_key TEXT PRIMARY KEY,
573 label TEXT NOT NULL -- I18N
576 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
577 id SERIAL PRIMARY KEY,
578 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
579 subfield TEXT NOT NULL,
580 start_pos INT NOT NULL,
582 label TEXT NOT NULL -- I18N
585 CREATE TABLE config.marc21_physical_characteristic_value_map (
586 id SERIAL PRIMARY KEY,
588 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
589 label TEXT NOT NULL -- I18N
593 CREATE TABLE config.z3950_source (
594 name TEXT PRIMARY KEY,
595 label TEXT NOT NULL UNIQUE,
599 record_format TEXT NOT NULL DEFAULT 'FI',
600 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
601 auth BOOL NOT NULL DEFAULT TRUE
604 COMMENT ON TABLE config.z3950_source IS $$
607 Each row in this table represents a database searchable via Z39.50.
610 COMMENT ON COLUMN config.z3950_source.record_format IS $$
614 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
615 Z39.50 preferred record syntax..
619 CREATE TABLE config.z3950_attr (
620 id SERIAL PRIMARY KEY,
621 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
626 truncation INT NOT NULL DEFAULT 0,
627 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
630 CREATE TABLE config.i18n_locale (
631 code TEXT PRIMARY KEY,
632 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
633 name TEXT UNIQUE NOT NULL,
637 CREATE TABLE config.i18n_core (
638 id BIGSERIAL PRIMARY KEY,
639 fq_field TEXT NOT NULL,
640 identity_value TEXT NOT NULL,
641 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
645 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
647 CREATE TABLE config.billing_type (
648 id SERIAL PRIMARY KEY,
650 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
651 default_price NUMERIC(6,2),
652 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
655 CREATE TABLE config.org_unit_setting_type (
656 name TEXT PRIMARY KEY,
657 label TEXT UNIQUE NOT NULL,
659 datatype TEXT NOT NULL DEFAULT 'string',
664 -- define valid datatypes
666 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
667 ( 'bool', 'integer', 'float', 'currency', 'interval',
668 'date', 'string', 'object', 'array', 'link' ) ),
670 -- fm_class is meaningful only for 'link' datatype
672 CONSTRAINT coust_no_empty_link CHECK
673 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
674 ( datatype <> 'link' AND fm_class IS NULL ) )
677 CREATE TABLE config.usr_setting_type (
679 name TEXT PRIMARY KEY,
680 opac_visible BOOL NOT NULL DEFAULT FALSE,
681 label TEXT UNIQUE NOT NULL,
683 datatype TEXT NOT NULL DEFAULT 'string',
687 -- define valid datatypes
689 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
690 ( 'bool', 'integer', 'float', 'currency', 'interval',
691 'date', 'string', 'object', 'array', 'link' ) ),
694 -- fm_class is meaningful only for 'link' datatype
696 CONSTRAINT coust_no_empty_link CHECK
697 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
698 ( datatype <> 'link' AND fm_class IS NULL ) )
702 -- Some handy functions, based on existing ones, to provide optional ingest normalization
704 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
705 SELECT SUBSTRING($1,$2);
706 $func$ LANGUAGE SQL STRICT IMMUTABLE;
708 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
709 SELECT SUBSTRING($1,1,$2);
710 $func$ LANGUAGE SQL STRICT IMMUTABLE;
712 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
713 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
714 $func$ LANGUAGE SQL STRICT IMMUTABLE;
716 -- And ... a table in which to register them
718 CREATE TABLE config.index_normalizer (
719 id SERIAL PRIMARY KEY,
720 name TEXT UNIQUE NOT NULL,
723 param_count INT NOT NULL DEFAULT 0
726 CREATE TABLE config.metabib_field_index_norm_map (
727 id SERIAL PRIMARY KEY,
728 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
729 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
731 pos INT NOT NULL DEFAULT 0
734 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
739 IF NEW.index_vector = ''::tsvector THEN
745 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
747 SELECT n.func AS func,
748 n.param_count AS param_count,
750 FROM config.index_normalizer n
751 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
752 WHERE field = NEW.field
755 EXECUTE 'SELECT ' || normalizer.func || '(' ||
756 quote_literal( value ) ||
758 WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
766 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
767 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
769 NEW.index_vector = to_tsvector(TG_ARGV[0], value);