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.upgrade_log (
50 version TEXT PRIMARY KEY,
51 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
54 INSERT INTO config.upgrade_log (version) VALUES ('0179'); -- dbs
56 CREATE TABLE config.bib_source (
57 id SERIAL PRIMARY KEY,
58 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
59 source TEXT NOT NULL UNIQUE,
60 transcendant BOOL NOT NULL DEFAULT FALSE
62 COMMENT ON TABLE config.bib_source IS $$
64 * Copyright (C) 2005 Georgia Public Library Service
65 * Mike Rylander <mrylander@gmail.com>
67 * Valid sources of MARC records
69 * This is table is used to set up the relative "quality" of each
70 * MARC source, such as OCLC.
74 * This program is free software; you can redistribute it and/or
75 * modify it under the terms of the GNU General Public License
76 * as published by the Free Software Foundation; either version 2
77 * of the License, or (at your option) any later version.
79 * This program is distributed in the hope that it will be useful,
80 * but WITHOUT ANY WARRANTY; without even the implied warranty of
81 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
82 * GNU General Public License for more details.
86 CREATE TABLE config.standing (
87 id SERIAL PRIMARY KEY,
88 value TEXT NOT NULL UNIQUE
90 COMMENT ON TABLE config.standing IS $$
92 * Copyright (C) 2005 Georgia Public Library Service
93 * Mike Rylander <mrylander@gmail.com>
97 * This table contains the values that can be applied to a patron
98 * by a staff member. These values should not be changed, other
99 * than for translation, as the ID column is currently a "magic
100 * number" in the source. :(
104 * This program is free software; you can redistribute it and/or
105 * modify it under the terms of the GNU General Public License
106 * as published by the Free Software Foundation; either version 2
107 * of the License, or (at your option) any later version.
109 * This program is distributed in the hope that it will be useful,
110 * but WITHOUT ANY WARRANTY; without even the implied warranty of
111 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
112 * GNU General Public License for more details.
116 CREATE TABLE config.standing_penalty (
117 id SERIAL PRIMARY KEY,
118 name TEXT NOT NULL UNIQUE,
123 INSERT INTO config.standing_penalty (id,name,label,block_list)
124 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
125 INSERT INTO config.standing_penalty (id,name,label,block_list)
126 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
127 INSERT INTO config.standing_penalty (id,name,label,block_list)
128 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
129 INSERT INTO config.standing_penalty (id,name,label,block_list)
130 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
132 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
133 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
134 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
135 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
136 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
137 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
138 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
139 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
140 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
141 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
142 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
144 SELECT SETVAL('config.standing_penalty_id_seq', 100);
146 CREATE TABLE config.xml_transform (
147 name TEXT PRIMARY KEY,
148 namespace_uri TEXT NOT NULL,
149 prefix TEXT NOT NULL,
153 CREATE TABLE config.biblio_fingerprint (
154 id SERIAL PRIMARY KEY,
157 first_word BOOL NOT NULL DEFAULT FALSE,
158 format TEXT NOT NULL DEFAULT 'marcxml'
161 INSERT INTO config.biblio_fingerprint (name, xpath, format)
164 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
165 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
166 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
167 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
168 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
172 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
175 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
176 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
177 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
178 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
179 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
184 CREATE TABLE config.metabib_field (
185 id SERIAL PRIMARY KEY,
186 field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')),
189 weight INT NOT NULL DEFAULT 1,
190 format TEXT NOT NULL DEFAULT 'mods33',
191 search_field BOOL NOT NULL DEFAULT TRUE,
192 facet_field BOOL NOT NULL DEFAULT FALSE
194 COMMENT ON TABLE config.metabib_field IS $$
196 * Copyright (C) 2005 Georgia Public Library Service
197 * Mike Rylander <mrylander@gmail.com>
199 * XPath used for record indexing ingest
201 * This table contains the XPath used to chop up MODS into its
202 * indexable parts. Each XPath entry is named and assigned to
203 * a "class" of either title, subject, author, keyword or series.
208 * This program is free software; you can redistribute it and/or
209 * modify it under the terms of the GNU General Public License
210 * as published by the Free Software Foundation; either version 2
211 * of the License, or (at your option) any later version.
213 * This program is distributed in the hope that it will be useful,
214 * but WITHOUT ANY WARRANTY; without even the implied warranty of
215 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
216 * GNU General Public License for more details.
220 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
222 CREATE TABLE config.non_cataloged_type (
223 id SERIAL PRIMARY KEY,
224 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
226 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
227 in_house BOOL NOT NULL DEFAULT FALSE,
228 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
230 COMMENT ON TABLE config.non_cataloged_type IS $$
232 * Copyright (C) 2005 Georgia Public Library Service
233 * Mike Rylander <mrylander@gmail.com>
235 * Types of valid non-cataloged items.
240 * This program is free software; you can redistribute it and/or
241 * modify it under the terms of the GNU General Public License
242 * as published by the Free Software Foundation; either version 2
243 * of the License, or (at your option) any later version.
245 * This program is distributed in the hope that it will be useful,
246 * but WITHOUT ANY WARRANTY; without even the implied warranty of
247 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
248 * GNU General Public License for more details.
252 CREATE TABLE config.identification_type (
253 id SERIAL PRIMARY KEY,
254 name TEXT NOT NULL UNIQUE
256 COMMENT ON TABLE config.identification_type IS $$
258 * Copyright (C) 2005 Georgia Public Library Service
259 * Mike Rylander <mrylander@gmail.com>
261 * Types of valid patron identification.
263 * Each patron must display at least one valid form of identification
264 * in order to get a library card. This table lists those forms.
269 * This program is free software; you can redistribute it and/or
270 * modify it under the terms of the GNU General Public License
271 * as published by the Free Software Foundation; either version 2
272 * of the License, or (at your option) any later version.
274 * This program is distributed in the hope that it will be useful,
275 * but WITHOUT ANY WARRANTY; without even the implied warranty of
276 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
277 * GNU General Public License for more details.
281 CREATE TABLE config.rule_circ_duration (
282 id SERIAL PRIMARY KEY,
283 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
284 extended INTERVAL NOT NULL,
285 normal INTERVAL NOT NULL,
286 shrt INTERVAL NOT NULL,
287 max_renewals INT NOT NULL
289 COMMENT ON TABLE config.rule_circ_duration IS $$
291 * Copyright (C) 2005 Georgia Public Library Service
292 * Mike Rylander <mrylander@gmail.com>
294 * Circulation Duration rules
296 * Each circulation is given a duration based on one of these rules.
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_max_fine (
314 id SERIAL PRIMARY KEY,
315 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
316 amount NUMERIC(6,2) NOT NULL,
317 is_percent BOOL NOT NULL DEFAULT FALSE
319 COMMENT ON TABLE config.rule_max_fine IS $$
321 * Copyright (C) 2005 Georgia Public Library Service
322 * Mike Rylander <mrylander@gmail.com>
324 * Circulation Max Fine rules
326 * Each circulation is given a maximum fine based on one of
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_recurring_fine (
345 id SERIAL PRIMARY KEY,
346 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
347 high NUMERIC(6,2) NOT NULL,
348 normal NUMERIC(6,2) NOT NULL,
349 low NUMERIC(6,2) NOT NULL,
350 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
352 COMMENT ON TABLE config.rule_recurring_fine IS $$
354 * Copyright (C) 2005 Georgia Public Library Service
355 * Mike Rylander <mrylander@gmail.com>
357 * Circulation Recurring Fine rules
359 * Each circulation is given a recurring fine amount based on one of
360 * these rules. The recurrence_interval should not be any shorter
361 * than the interval between runs of the fine_processor.pl script
362 * (which is run from CRON), or you could miss fines.
367 * This program is free software; you can redistribute it and/or
368 * modify it under the terms of the GNU General Public License
369 * as published by the Free Software Foundation; either version 2
370 * of the License, or (at your option) any later version.
372 * This program is distributed in the hope that it will be useful,
373 * but WITHOUT ANY WARRANTY; without even the implied warranty of
374 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
375 * GNU General Public License for more details.
380 CREATE TABLE config.rule_age_hold_protect (
381 id SERIAL PRIMARY KEY,
382 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
383 age INTERVAL NOT NULL,
386 COMMENT ON TABLE config.rule_age_hold_protect IS $$
388 * Copyright (C) 2005 Georgia Public Library Service
389 * Mike Rylander <mrylander@gmail.com>
391 * Hold Item Age Protection rules
393 * A hold request can only capture new(ish) items when they are
394 * within a particular proximity of the home_ou of the requesting
395 * user. The proximity ('prox' column) is calculated by counting
396 * the number of tree edges between the user's home_ou and the owning_lib
397 * of the copy that could fulfill the hold.
402 * This program is free software; you can redistribute it and/or
403 * modify it under the terms of the GNU General Public License
404 * as published by the Free Software Foundation; either version 2
405 * of the License, or (at your option) any later version.
407 * This program is distributed in the hope that it will be useful,
408 * but WITHOUT ANY WARRANTY; without even the implied warranty of
409 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
410 * GNU General Public License for more details.
414 CREATE TABLE config.copy_status (
415 id SERIAL PRIMARY KEY,
416 name TEXT NOT NULL UNIQUE,
417 holdable BOOL NOT NULL DEFAULT FALSE,
418 opac_visible BOOL NOT NULL DEFAULT FALSE
420 COMMENT ON TABLE config.copy_status IS $$
422 * Copyright (C) 2005 Georgia Public Library Service
423 * Mike Rylander <mrylander@gmail.com>
427 * The available copy statuses, and whether a copy in that
428 * status is available for hold request capture. 0 (zero) is
429 * the only special number in this set, meaning that the item
430 * is available for immediate checkout, and is counted as available
433 * Statuses with an ID below 100 are not removable, and have special
434 * meaning in the code. Do not change them except to translate the
437 * You may add and remove statuses above 100, and these can be used
438 * to remove items from normal circulation without affecting the rest
439 * of the copy's values or its location.
443 * This program is free software; you can redistribute it and/or
444 * modify it under the terms of the GNU General Public License
445 * as published by the Free Software Foundation; either version 2
446 * of the License, or (at your option) any later version.
448 * This program is distributed in the hope that it will be useful,
449 * but WITHOUT ANY WARRANTY; without even the implied warranty of
450 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
451 * GNU General Public License for more details.
455 CREATE TABLE config.net_access_level (
456 id SERIAL PRIMARY KEY,
457 name TEXT NOT NULL UNIQUE
459 COMMENT ON TABLE config.net_access_level IS $$
461 * Copyright (C) 2005 Georgia Public Library Service
462 * Mike Rylander <mrylander@gmail.com>
464 * Patron Network Access level
466 * This will be used to inform the in-library firewall of how much
467 * internet access the using patron should be allowed.
471 * This program is free software; you can redistribute it and/or
472 * modify it under the terms of the GNU General Public License
473 * as published by the Free Software Foundation; either version 2
474 * of the License, or (at your option) any later version.
476 * This program is distributed in the hope that it will be useful,
477 * but WITHOUT ANY WARRANTY; without even the implied warranty of
478 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
479 * GNU General Public License for more details.
484 CREATE TABLE config.remote_account (
485 id SERIAL PRIMARY KEY,
487 host TEXT NOT NULL, -- name or IP, :port optional
488 username TEXT, -- optional, since we could default to $USER
489 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
490 account TEXT, -- aka profile or FTP "account" command
491 path TEXT, -- aka directory
492 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
493 last_activity TIMESTAMP WITH TIME ZONE
496 CREATE TABLE config.audience_map (
497 code TEXT PRIMARY KEY,
502 CREATE TABLE config.lit_form_map (
503 code TEXT PRIMARY KEY,
508 CREATE TABLE config.language_map (
509 code TEXT PRIMARY KEY,
513 CREATE TABLE config.item_form_map (
514 code TEXT PRIMARY KEY,
518 CREATE TABLE config.item_type_map (
519 code TEXT PRIMARY KEY,
523 CREATE TABLE config.bib_level_map (
524 code TEXT PRIMARY KEY,
528 CREATE TABLE config.marc21_rec_type_map (
529 code TEXT PRIMARY KEY,
530 type_val TEXT NOT NULL,
531 blvl_val TEXT NOT NULL
534 CREATE TABLE config.marc21_ff_pos_map (
535 id SERIAL PRIMARY KEY,
536 fixed_field TEXT NOT NULL,
538 rec_type TEXT NOT NULL,
539 start_pos INT NOT NULL,
541 default_val TEXT NOT NULL DEFAULT ' '
544 CREATE TABLE config.marc21_physical_characteristic_type_map (
545 ptype_key TEXT PRIMARY KEY,
546 label TEXT NOT NULL -- I18N
549 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
550 id SERIAL PRIMARY KEY,
551 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
552 subfield TEXT NOT NULL,
553 start_pos INT NOT NULL,
555 label TEXT NOT NULL -- I18N
558 CREATE TABLE config.marc21_physical_characteristic_value_map (
559 id SERIAL PRIMARY KEY,
561 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
562 label TEXT NOT NULL -- I18N
566 CREATE TABLE config.z3950_source (
567 name TEXT PRIMARY KEY,
568 label TEXT NOT NULL UNIQUE,
572 record_format TEXT NOT NULL DEFAULT 'FI',
573 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
574 auth BOOL NOT NULL DEFAULT TRUE
577 COMMENT ON TABLE config.z3950_source IS $$
580 Each row in this table represents a database searchable via Z39.50.
583 COMMENT ON COLUMN config.z3950_source.record_format IS $$
587 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
588 Z39.50 preferred record syntax..
592 CREATE TABLE config.z3950_attr (
593 id SERIAL PRIMARY KEY,
594 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
599 truncation INT NOT NULL DEFAULT 0,
600 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
603 CREATE TABLE config.i18n_locale (
604 code TEXT PRIMARY KEY,
605 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
606 name TEXT UNIQUE NOT NULL,
610 CREATE TABLE config.i18n_core (
611 id BIGSERIAL PRIMARY KEY,
612 fq_field TEXT NOT NULL,
613 identity_value TEXT NOT NULL,
614 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
618 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
620 CREATE TABLE config.billing_type (
621 id SERIAL PRIMARY KEY,
623 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
624 default_price NUMERIC(6,2),
625 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
628 CREATE TABLE config.org_unit_setting_type (
629 name TEXT PRIMARY KEY,
630 label TEXT UNIQUE NOT NULL,
632 datatype TEXT NOT NULL DEFAULT 'string',
637 -- define valid datatypes
639 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
640 ( 'bool', 'integer', 'float', 'currency', 'interval',
641 'date', 'string', 'object', 'array', 'link' ) ),
643 -- fm_class is meaningful only for 'link' datatype
645 CONSTRAINT coust_no_empty_link CHECK
646 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
647 ( datatype <> 'link' AND fm_class IS NULL ) )
650 CREATE TABLE config.usr_setting_type (
652 name TEXT PRIMARY KEY,
653 opac_visible BOOL NOT NULL DEFAULT FALSE,
654 label TEXT UNIQUE NOT NULL,
656 datatype TEXT NOT NULL DEFAULT 'string',
660 -- define valid datatypes
662 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
663 ( 'bool', 'integer', 'float', 'currency', 'interval',
664 'date', 'string', 'object', 'array', 'link' ) ),
667 -- fm_class is meaningful only for 'link' datatype
669 CONSTRAINT coust_no_empty_link CHECK
670 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
671 ( datatype <> 'link' AND fm_class IS NULL ) )
675 -- Some handy functions, based on existing ones, to provide optional ingest normalization
677 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
678 SELECT SUBSTRING($1,$2);
679 $func$ LANGUAGE SQL STRICT IMMUTABLE;
681 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
682 SELECT SUBSTRING($1,1,$2);
683 $func$ LANGUAGE SQL STRICT IMMUTABLE;
685 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
686 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
687 $func$ LANGUAGE SQL STRICT IMMUTABLE;
689 -- And ... a table in which to register them
691 CREATE TABLE config.index_normalizer (
692 id SERIAL PRIMARY KEY,
693 name TEXT UNIQUE NOT NULL,
696 param_count INT NOT NULL DEFAULT 0
699 CREATE TABLE config.metabib_field_index_norm_map (
700 id SERIAL PRIMARY KEY,
701 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
702 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
704 pos INT NOT NULL DEFAULT 0
707 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
714 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
716 SELECT n.func AS func,
717 n.param_count AS param_count,
719 FROM config.index_normalizer n
720 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
721 WHERE field = NEW.field
724 EXECUTE 'SELECT ' || normalizer.func || '(' ||
725 quote_literal( value ) ||
727 WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
735 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
736 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
738 NEW.index_vector = to_tsvector(TG_ARGV[0], value);