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');
56 CREATE TABLE config.upgrade_log (
57 version TEXT PRIMARY KEY,
58 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
61 INSERT INTO config.upgrade_log (version) VALUES ('0201'); -- miker
63 CREATE TABLE config.bib_source (
64 id SERIAL PRIMARY KEY,
65 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
66 source TEXT NOT NULL UNIQUE,
67 transcendant BOOL NOT NULL DEFAULT FALSE
69 COMMENT ON TABLE config.bib_source IS $$
71 * Copyright (C) 2005 Georgia Public Library Service
72 * Mike Rylander <mrylander@gmail.com>
74 * Valid sources of MARC records
76 * This is table is used to set up the relative "quality" of each
77 * MARC source, such as OCLC.
81 * This program is free software; you can redistribute it and/or
82 * modify it under the terms of the GNU General Public License
83 * as published by the Free Software Foundation; either version 2
84 * of the License, or (at your option) any later version.
86 * This program is distributed in the hope that it will be useful,
87 * but WITHOUT ANY WARRANTY; without even the implied warranty of
88 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
89 * GNU General Public License for more details.
93 CREATE TABLE config.standing (
94 id SERIAL PRIMARY KEY,
95 value TEXT NOT NULL UNIQUE
97 COMMENT ON TABLE config.standing IS $$
99 * Copyright (C) 2005 Georgia Public Library Service
100 * Mike Rylander <mrylander@gmail.com>
104 * This table contains the values that can be applied to a patron
105 * by a staff member. These values should not be changed, other
106 * than for translation, as the ID column is currently a "magic
107 * number" in the source. :(
111 * This program is free software; you can redistribute it and/or
112 * modify it under the terms of the GNU General Public License
113 * as published by the Free Software Foundation; either version 2
114 * of the License, or (at your option) any later version.
116 * This program is distributed in the hope that it will be useful,
117 * but WITHOUT ANY WARRANTY; without even the implied warranty of
118 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
119 * GNU General Public License for more details.
123 CREATE TABLE config.standing_penalty (
124 id SERIAL PRIMARY KEY,
125 name TEXT NOT NULL UNIQUE,
130 INSERT INTO config.standing_penalty (id,name,label,block_list)
131 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
132 INSERT INTO config.standing_penalty (id,name,label,block_list)
133 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
134 INSERT INTO config.standing_penalty (id,name,label,block_list)
135 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
136 INSERT INTO config.standing_penalty (id,name,label,block_list)
137 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
139 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
140 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
141 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
142 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
143 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
144 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
145 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
146 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
147 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
148 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
149 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
151 SELECT SETVAL('config.standing_penalty_id_seq', 100);
153 CREATE TABLE config.xml_transform (
154 name TEXT PRIMARY KEY,
155 namespace_uri TEXT NOT NULL,
156 prefix TEXT NOT NULL,
160 CREATE TABLE config.biblio_fingerprint (
161 id SERIAL PRIMARY KEY,
164 first_word BOOL NOT NULL DEFAULT FALSE,
165 format TEXT NOT NULL DEFAULT 'marcxml'
168 INSERT INTO config.biblio_fingerprint (name, xpath, format)
171 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
172 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
173 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
174 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
175 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
179 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
182 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
183 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
184 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
185 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
186 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
191 CREATE TABLE config.metabib_field (
192 id SERIAL PRIMARY KEY,
193 field_class TEXT NOT NULL CHECK (lower(field_class) IN ('title','author','subject','keyword','series')),
196 weight INT NOT NULL DEFAULT 1,
197 format TEXT NOT NULL DEFAULT 'mods33',
198 search_field BOOL NOT NULL DEFAULT TRUE,
199 facet_field BOOL NOT NULL DEFAULT FALSE
201 COMMENT ON TABLE config.metabib_field IS $$
203 * Copyright (C) 2005 Georgia Public Library Service
204 * Mike Rylander <mrylander@gmail.com>
206 * XPath used for record indexing ingest
208 * This table contains the XPath used to chop up MODS into its
209 * indexable parts. Each XPath entry is named and assigned to
210 * a "class" of either title, subject, author, keyword or series.
215 * This program is free software; you can redistribute it and/or
216 * modify it under the terms of the GNU General Public License
217 * as published by the Free Software Foundation; either version 2
218 * of the License, or (at your option) any later version.
220 * This program is distributed in the hope that it will be useful,
221 * but WITHOUT ANY WARRANTY; without even the implied warranty of
222 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
223 * GNU General Public License for more details.
227 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
229 CREATE TABLE config.non_cataloged_type (
230 id SERIAL PRIMARY KEY,
231 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
233 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
234 in_house BOOL NOT NULL DEFAULT FALSE,
235 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
237 COMMENT ON TABLE config.non_cataloged_type IS $$
239 * Copyright (C) 2005 Georgia Public Library Service
240 * Mike Rylander <mrylander@gmail.com>
242 * Types of valid non-cataloged items.
247 * This program is free software; you can redistribute it and/or
248 * modify it under the terms of the GNU General Public License
249 * as published by the Free Software Foundation; either version 2
250 * of the License, or (at your option) any later version.
252 * This program is distributed in the hope that it will be useful,
253 * but WITHOUT ANY WARRANTY; without even the implied warranty of
254 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
255 * GNU General Public License for more details.
259 CREATE TABLE config.identification_type (
260 id SERIAL PRIMARY KEY,
261 name TEXT NOT NULL UNIQUE
263 COMMENT ON TABLE config.identification_type IS $$
265 * Copyright (C) 2005 Georgia Public Library Service
266 * Mike Rylander <mrylander@gmail.com>
268 * Types of valid patron identification.
270 * Each patron must display at least one valid form of identification
271 * in order to get a library card. This table lists those forms.
276 * This program is free software; you can redistribute it and/or
277 * modify it under the terms of the GNU General Public License
278 * as published by the Free Software Foundation; either version 2
279 * of the License, or (at your option) any later version.
281 * This program is distributed in the hope that it will be useful,
282 * but WITHOUT ANY WARRANTY; without even the implied warranty of
283 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
284 * GNU General Public License for more details.
288 CREATE TABLE config.rule_circ_duration (
289 id SERIAL PRIMARY KEY,
290 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
291 extended INTERVAL NOT NULL,
292 normal INTERVAL NOT NULL,
293 shrt INTERVAL NOT NULL,
294 max_renewals INT NOT NULL
296 COMMENT ON TABLE config.rule_circ_duration IS $$
298 * Copyright (C) 2005 Georgia Public Library Service
299 * Mike Rylander <mrylander@gmail.com>
301 * Circulation Duration rules
303 * Each circulation is given a duration based on one of these rules.
308 * This program is free software; you can redistribute it and/or
309 * modify it under the terms of the GNU General Public License
310 * as published by the Free Software Foundation; either version 2
311 * of the License, or (at your option) any later version.
313 * This program is distributed in the hope that it will be useful,
314 * but WITHOUT ANY WARRANTY; without even the implied warranty of
315 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
316 * GNU General Public License for more details.
320 CREATE TABLE config.rule_max_fine (
321 id SERIAL PRIMARY KEY,
322 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
323 amount NUMERIC(6,2) NOT NULL,
324 is_percent BOOL NOT NULL DEFAULT FALSE
326 COMMENT ON TABLE config.rule_max_fine IS $$
328 * Copyright (C) 2005 Georgia Public Library Service
329 * Mike Rylander <mrylander@gmail.com>
331 * Circulation Max Fine rules
333 * Each circulation is given a maximum fine based on one of
339 * This program is free software; you can redistribute it and/or
340 * modify it under the terms of the GNU General Public License
341 * as published by the Free Software Foundation; either version 2
342 * of the License, or (at your option) any later version.
344 * This program is distributed in the hope that it will be useful,
345 * but WITHOUT ANY WARRANTY; without even the implied warranty of
346 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
347 * GNU General Public License for more details.
351 CREATE TABLE config.rule_recurring_fine (
352 id SERIAL PRIMARY KEY,
353 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
354 high NUMERIC(6,2) NOT NULL,
355 normal NUMERIC(6,2) NOT NULL,
356 low NUMERIC(6,2) NOT NULL,
357 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
359 COMMENT ON TABLE config.rule_recurring_fine IS $$
361 * Copyright (C) 2005 Georgia Public Library Service
362 * Mike Rylander <mrylander@gmail.com>
364 * Circulation Recurring Fine rules
366 * Each circulation is given a recurring fine amount based on one of
367 * these rules. The recurrence_interval should not be any shorter
368 * than the interval between runs of the fine_processor.pl script
369 * (which is run from CRON), or you could miss fines.
374 * This program is free software; you can redistribute it and/or
375 * modify it under the terms of the GNU General Public License
376 * as published by the Free Software Foundation; either version 2
377 * of the License, or (at your option) any later version.
379 * This program is distributed in the hope that it will be useful,
380 * but WITHOUT ANY WARRANTY; without even the implied warranty of
381 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
382 * GNU General Public License for more details.
387 CREATE TABLE config.rule_age_hold_protect (
388 id SERIAL PRIMARY KEY,
389 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
390 age INTERVAL NOT NULL,
393 COMMENT ON TABLE config.rule_age_hold_protect IS $$
395 * Copyright (C) 2005 Georgia Public Library Service
396 * Mike Rylander <mrylander@gmail.com>
398 * Hold Item Age Protection rules
400 * A hold request can only capture new(ish) items when they are
401 * within a particular proximity of the home_ou of the requesting
402 * user. The proximity ('prox' column) is calculated by counting
403 * the number of tree edges between the user's home_ou and the owning_lib
404 * of the copy that could fulfill the hold.
409 * This program is free software; you can redistribute it and/or
410 * modify it under the terms of the GNU General Public License
411 * as published by the Free Software Foundation; either version 2
412 * of the License, or (at your option) any later version.
414 * This program is distributed in the hope that it will be useful,
415 * but WITHOUT ANY WARRANTY; without even the implied warranty of
416 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
417 * GNU General Public License for more details.
421 CREATE TABLE config.copy_status (
422 id SERIAL PRIMARY KEY,
423 name TEXT NOT NULL UNIQUE,
424 holdable BOOL NOT NULL DEFAULT FALSE,
425 opac_visible BOOL NOT NULL DEFAULT FALSE
427 COMMENT ON TABLE config.copy_status IS $$
429 * Copyright (C) 2005 Georgia Public Library Service
430 * Mike Rylander <mrylander@gmail.com>
434 * The available copy statuses, and whether a copy in that
435 * status is available for hold request capture. 0 (zero) is
436 * the only special number in this set, meaning that the item
437 * is available for immediate checkout, and is counted as available
440 * Statuses with an ID below 100 are not removable, and have special
441 * meaning in the code. Do not change them except to translate the
444 * You may add and remove statuses above 100, and these can be used
445 * to remove items from normal circulation without affecting the rest
446 * of the copy's values or its location.
450 * This program is free software; you can redistribute it and/or
451 * modify it under the terms of the GNU General Public License
452 * as published by the Free Software Foundation; either version 2
453 * of the License, or (at your option) any later version.
455 * This program is distributed in the hope that it will be useful,
456 * but WITHOUT ANY WARRANTY; without even the implied warranty of
457 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
458 * GNU General Public License for more details.
462 CREATE TABLE config.net_access_level (
463 id SERIAL PRIMARY KEY,
464 name TEXT NOT NULL UNIQUE
466 COMMENT ON TABLE config.net_access_level IS $$
468 * Copyright (C) 2005 Georgia Public Library Service
469 * Mike Rylander <mrylander@gmail.com>
471 * Patron Network Access level
473 * This will be used to inform the in-library firewall of how much
474 * internet access the using patron should be allowed.
478 * This program is free software; you can redistribute it and/or
479 * modify it under the terms of the GNU General Public License
480 * as published by the Free Software Foundation; either version 2
481 * of the License, or (at your option) any later version.
483 * This program is distributed in the hope that it will be useful,
484 * but WITHOUT ANY WARRANTY; without even the implied warranty of
485 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
486 * GNU General Public License for more details.
491 CREATE TABLE config.remote_account (
492 id SERIAL PRIMARY KEY,
494 host TEXT NOT NULL, -- name or IP, :port optional
495 username TEXT, -- optional, since we could default to $USER
496 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
497 account TEXT, -- aka profile or FTP "account" command
498 path TEXT, -- aka directory
499 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
500 last_activity TIMESTAMP WITH TIME ZONE
503 CREATE TABLE config.audience_map (
504 code TEXT PRIMARY KEY,
509 CREATE TABLE config.lit_form_map (
510 code TEXT PRIMARY KEY,
515 CREATE TABLE config.language_map (
516 code TEXT PRIMARY KEY,
520 CREATE TABLE config.item_form_map (
521 code TEXT PRIMARY KEY,
525 CREATE TABLE config.item_type_map (
526 code TEXT PRIMARY KEY,
530 CREATE TABLE config.bib_level_map (
531 code TEXT PRIMARY KEY,
535 CREATE TABLE config.marc21_rec_type_map (
536 code TEXT PRIMARY KEY,
537 type_val TEXT NOT NULL,
538 blvl_val TEXT NOT NULL
541 CREATE TABLE config.marc21_ff_pos_map (
542 id SERIAL PRIMARY KEY,
543 fixed_field TEXT NOT NULL,
545 rec_type TEXT NOT NULL,
546 start_pos INT NOT NULL,
548 default_val TEXT NOT NULL DEFAULT ' '
551 CREATE TABLE config.marc21_physical_characteristic_type_map (
552 ptype_key TEXT PRIMARY KEY,
553 label TEXT NOT NULL -- I18N
556 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
557 id SERIAL PRIMARY KEY,
558 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
559 subfield TEXT NOT NULL,
560 start_pos INT NOT NULL,
562 label TEXT NOT NULL -- I18N
565 CREATE TABLE config.marc21_physical_characteristic_value_map (
566 id SERIAL PRIMARY KEY,
568 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
569 label TEXT NOT NULL -- I18N
573 CREATE TABLE config.z3950_source (
574 name TEXT PRIMARY KEY,
575 label TEXT NOT NULL UNIQUE,
579 record_format TEXT NOT NULL DEFAULT 'FI',
580 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
581 auth BOOL NOT NULL DEFAULT TRUE
584 COMMENT ON TABLE config.z3950_source IS $$
587 Each row in this table represents a database searchable via Z39.50.
590 COMMENT ON COLUMN config.z3950_source.record_format IS $$
594 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
595 Z39.50 preferred record syntax..
599 CREATE TABLE config.z3950_attr (
600 id SERIAL PRIMARY KEY,
601 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
606 truncation INT NOT NULL DEFAULT 0,
607 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
610 CREATE TABLE config.i18n_locale (
611 code TEXT PRIMARY KEY,
612 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
613 name TEXT UNIQUE NOT NULL,
617 CREATE TABLE config.i18n_core (
618 id BIGSERIAL PRIMARY KEY,
619 fq_field TEXT NOT NULL,
620 identity_value TEXT NOT NULL,
621 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
625 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
627 CREATE TABLE config.billing_type (
628 id SERIAL PRIMARY KEY,
630 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
631 default_price NUMERIC(6,2),
632 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
635 CREATE TABLE config.org_unit_setting_type (
636 name TEXT PRIMARY KEY,
637 label TEXT UNIQUE NOT NULL,
639 datatype TEXT NOT NULL DEFAULT 'string',
644 -- define valid datatypes
646 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
647 ( 'bool', 'integer', 'float', 'currency', 'interval',
648 'date', 'string', 'object', 'array', 'link' ) ),
650 -- fm_class is meaningful only for 'link' datatype
652 CONSTRAINT coust_no_empty_link CHECK
653 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
654 ( datatype <> 'link' AND fm_class IS NULL ) )
657 CREATE TABLE config.usr_setting_type (
659 name TEXT PRIMARY KEY,
660 opac_visible BOOL NOT NULL DEFAULT FALSE,
661 label TEXT UNIQUE NOT NULL,
663 datatype TEXT NOT NULL DEFAULT 'string',
667 -- define valid datatypes
669 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
670 ( 'bool', 'integer', 'float', 'currency', 'interval',
671 'date', 'string', 'object', 'array', 'link' ) ),
674 -- fm_class is meaningful only for 'link' datatype
676 CONSTRAINT coust_no_empty_link CHECK
677 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
678 ( datatype <> 'link' AND fm_class IS NULL ) )
682 -- Some handy functions, based on existing ones, to provide optional ingest normalization
684 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
685 SELECT SUBSTRING($1,$2);
686 $func$ LANGUAGE SQL STRICT IMMUTABLE;
688 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
689 SELECT SUBSTRING($1,1,$2);
690 $func$ LANGUAGE SQL STRICT IMMUTABLE;
692 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
693 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
694 $func$ LANGUAGE SQL STRICT IMMUTABLE;
696 -- And ... a table in which to register them
698 CREATE TABLE config.index_normalizer (
699 id SERIAL PRIMARY KEY,
700 name TEXT UNIQUE NOT NULL,
703 param_count INT NOT NULL DEFAULT 0
706 CREATE TABLE config.metabib_field_index_norm_map (
707 id SERIAL PRIMARY KEY,
708 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
709 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
711 pos INT NOT NULL DEFAULT 0
714 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
721 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
723 SELECT n.func AS func,
724 n.param_count AS param_count,
726 FROM config.index_normalizer n
727 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
728 WHERE field = NEW.field
731 EXECUTE 'SELECT ' || normalizer.func || '(' ||
732 quote_literal( value ) ||
734 WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
742 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
743 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
745 NEW.index_vector = to_tsvector(TG_ARGV[0], value);