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');
57 CREATE TABLE config.upgrade_log (
58 version TEXT PRIMARY KEY,
59 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
62 INSERT INTO config.upgrade_log (version) VALUES ('0215'); -- Scott McKellar
64 CREATE TABLE config.bib_source (
65 id SERIAL PRIMARY KEY,
66 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
67 source TEXT NOT NULL UNIQUE,
68 transcendant BOOL NOT NULL DEFAULT FALSE
70 COMMENT ON TABLE config.bib_source IS $$
72 * Copyright (C) 2005 Georgia Public Library Service
73 * Mike Rylander <mrylander@gmail.com>
75 * Valid sources of MARC records
77 * This is table is used to set up the relative "quality" of each
78 * MARC source, such as OCLC.
82 * This program is free software; you can redistribute it and/or
83 * modify it under the terms of the GNU General Public License
84 * as published by the Free Software Foundation; either version 2
85 * of the License, or (at your option) any later version.
87 * This program is distributed in the hope that it will be useful,
88 * but WITHOUT ANY WARRANTY; without even the implied warranty of
89 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
90 * GNU General Public License for more details.
94 CREATE TABLE config.standing (
95 id SERIAL PRIMARY KEY,
96 value TEXT NOT NULL UNIQUE
98 COMMENT ON TABLE config.standing IS $$
100 * Copyright (C) 2005 Georgia Public Library Service
101 * Mike Rylander <mrylander@gmail.com>
105 * This table contains the values that can be applied to a patron
106 * by a staff member. These values should not be changed, other
107 * than for translation, as the ID column is currently a "magic
108 * number" in the source. :(
112 * This program is free software; you can redistribute it and/or
113 * modify it under the terms of the GNU General Public License
114 * as published by the Free Software Foundation; either version 2
115 * of the License, or (at your option) any later version.
117 * This program is distributed in the hope that it will be useful,
118 * but WITHOUT ANY WARRANTY; without even the implied warranty of
119 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
120 * GNU General Public License for more details.
124 CREATE TABLE config.standing_penalty (
125 id SERIAL PRIMARY KEY,
126 name TEXT NOT NULL UNIQUE,
131 INSERT INTO config.standing_penalty (id,name,label,block_list)
132 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
133 INSERT INTO config.standing_penalty (id,name,label,block_list)
134 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
135 INSERT INTO config.standing_penalty (id,name,label,block_list)
136 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
137 INSERT INTO config.standing_penalty (id,name,label,block_list)
138 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
140 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
141 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
142 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
143 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
144 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
145 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
146 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
147 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
148 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
149 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
150 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
152 SELECT SETVAL('config.standing_penalty_id_seq', 100);
154 CREATE TABLE config.xml_transform (
155 name TEXT PRIMARY KEY,
156 namespace_uri TEXT NOT NULL,
157 prefix TEXT NOT NULL,
161 CREATE TABLE config.biblio_fingerprint (
162 id SERIAL PRIMARY KEY,
165 first_word BOOL NOT NULL DEFAULT FALSE,
166 format TEXT NOT NULL DEFAULT 'marcxml'
169 INSERT INTO config.biblio_fingerprint (name, xpath, format)
172 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
173 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
174 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
175 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
176 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
180 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
183 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
184 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
185 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
186 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
187 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
192 CREATE TABLE config.metabib_class (
193 name TEXT PRIMARY KEY,
194 label TEXT NOT NULL UNIQUE
197 CREATE TABLE config.metabib_field (
198 id SERIAL PRIMARY KEY,
199 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
203 weight INT NOT NULL DEFAULT 1,
204 format TEXT NOT NULL DEFAULT 'mods33',
205 search_field BOOL NOT NULL DEFAULT TRUE,
206 facet_field BOOL NOT NULL DEFAULT FALSE
208 COMMENT ON TABLE config.metabib_field IS $$
210 * Copyright (C) 2005 Georgia Public Library Service
211 * Mike Rylander <mrylander@gmail.com>
213 * XPath used for record indexing ingest
215 * This table contains the XPath used to chop up MODS into its
216 * indexable parts. Each XPath entry is named and assigned to
217 * a "class" of either title, subject, author, keyword or series.
222 * This program is free software; you can redistribute it and/or
223 * modify it under the terms of the GNU General Public License
224 * as published by the Free Software Foundation; either version 2
225 * of the License, or (at your option) any later version.
227 * This program is distributed in the hope that it will be useful,
228 * but WITHOUT ANY WARRANTY; without even the implied warranty of
229 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
230 * GNU General Public License for more details.
234 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
236 CREATE TABLE config.metabib_search_alias (
237 alias TEXT PRIMARY KEY,
238 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
239 field INT REFERENCES config.metabib_field (id)
242 CREATE TABLE config.non_cataloged_type (
243 id SERIAL PRIMARY KEY,
244 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
246 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
247 in_house BOOL NOT NULL DEFAULT FALSE,
248 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
250 COMMENT ON TABLE config.non_cataloged_type IS $$
252 * Copyright (C) 2005 Georgia Public Library Service
253 * Mike Rylander <mrylander@gmail.com>
255 * Types of valid non-cataloged items.
260 * This program is free software; you can redistribute it and/or
261 * modify it under the terms of the GNU General Public License
262 * as published by the Free Software Foundation; either version 2
263 * of the License, or (at your option) any later version.
265 * This program is distributed in the hope that it will be useful,
266 * but WITHOUT ANY WARRANTY; without even the implied warranty of
267 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
268 * GNU General Public License for more details.
272 CREATE TABLE config.identification_type (
273 id SERIAL PRIMARY KEY,
274 name TEXT NOT NULL UNIQUE
276 COMMENT ON TABLE config.identification_type IS $$
278 * Copyright (C) 2005 Georgia Public Library Service
279 * Mike Rylander <mrylander@gmail.com>
281 * Types of valid patron identification.
283 * Each patron must display at least one valid form of identification
284 * in order to get a library card. This table lists those forms.
289 * This program is free software; you can redistribute it and/or
290 * modify it under the terms of the GNU General Public License
291 * as published by the Free Software Foundation; either version 2
292 * of the License, or (at your option) any later version.
294 * This program is distributed in the hope that it will be useful,
295 * but WITHOUT ANY WARRANTY; without even the implied warranty of
296 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
297 * GNU General Public License for more details.
301 CREATE TABLE config.rule_circ_duration (
302 id SERIAL PRIMARY KEY,
303 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
304 extended INTERVAL NOT NULL,
305 normal INTERVAL NOT NULL,
306 shrt INTERVAL NOT NULL,
307 max_renewals INT NOT NULL
309 COMMENT ON TABLE config.rule_circ_duration IS $$
311 * Copyright (C) 2005 Georgia Public Library Service
312 * Mike Rylander <mrylander@gmail.com>
314 * Circulation Duration rules
316 * Each circulation is given a duration based on one of these rules.
321 * This program is free software; you can redistribute it and/or
322 * modify it under the terms of the GNU General Public License
323 * as published by the Free Software Foundation; either version 2
324 * of the License, or (at your option) any later version.
326 * This program is distributed in the hope that it will be useful,
327 * but WITHOUT ANY WARRANTY; without even the implied warranty of
328 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
329 * GNU General Public License for more details.
333 CREATE TABLE config.rule_max_fine (
334 id SERIAL PRIMARY KEY,
335 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
336 amount NUMERIC(6,2) NOT NULL,
337 is_percent BOOL NOT NULL DEFAULT FALSE
339 COMMENT ON TABLE config.rule_max_fine IS $$
341 * Copyright (C) 2005 Georgia Public Library Service
342 * Mike Rylander <mrylander@gmail.com>
344 * Circulation Max Fine rules
346 * Each circulation is given a maximum fine based on one of
352 * This program is free software; you can redistribute it and/or
353 * modify it under the terms of the GNU General Public License
354 * as published by the Free Software Foundation; either version 2
355 * of the License, or (at your option) any later version.
357 * This program is distributed in the hope that it will be useful,
358 * but WITHOUT ANY WARRANTY; without even the implied warranty of
359 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
360 * GNU General Public License for more details.
364 CREATE TABLE config.rule_recurring_fine (
365 id SERIAL PRIMARY KEY,
366 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
367 high NUMERIC(6,2) NOT NULL,
368 normal NUMERIC(6,2) NOT NULL,
369 low NUMERIC(6,2) NOT NULL,
370 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
372 COMMENT ON TABLE config.rule_recurring_fine IS $$
374 * Copyright (C) 2005 Georgia Public Library Service
375 * Mike Rylander <mrylander@gmail.com>
377 * Circulation Recurring Fine rules
379 * Each circulation is given a recurring fine amount based on one of
380 * these rules. The recurrence_interval should not be any shorter
381 * than the interval between runs of the fine_processor.pl script
382 * (which is run from CRON), or you could miss fines.
387 * This program is free software; you can redistribute it and/or
388 * modify it under the terms of the GNU General Public License
389 * as published by the Free Software Foundation; either version 2
390 * of the License, or (at your option) any later version.
392 * This program is distributed in the hope that it will be useful,
393 * but WITHOUT ANY WARRANTY; without even the implied warranty of
394 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
395 * GNU General Public License for more details.
400 CREATE TABLE config.rule_age_hold_protect (
401 id SERIAL PRIMARY KEY,
402 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
403 age INTERVAL NOT NULL,
406 COMMENT ON TABLE config.rule_age_hold_protect IS $$
408 * Copyright (C) 2005 Georgia Public Library Service
409 * Mike Rylander <mrylander@gmail.com>
411 * Hold Item Age Protection rules
413 * A hold request can only capture new(ish) items when they are
414 * within a particular proximity of the home_ou of the requesting
415 * user. The proximity ('prox' column) is calculated by counting
416 * the number of tree edges between the user's home_ou and the owning_lib
417 * of the copy that could fulfill the hold.
422 * This program is free software; you can redistribute it and/or
423 * modify it under the terms of the GNU General Public License
424 * as published by the Free Software Foundation; either version 2
425 * of the License, or (at your option) any later version.
427 * This program is distributed in the hope that it will be useful,
428 * but WITHOUT ANY WARRANTY; without even the implied warranty of
429 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
430 * GNU General Public License for more details.
434 CREATE TABLE config.copy_status (
435 id SERIAL PRIMARY KEY,
436 name TEXT NOT NULL UNIQUE,
437 holdable BOOL NOT NULL DEFAULT FALSE,
438 opac_visible BOOL NOT NULL DEFAULT FALSE
440 COMMENT ON TABLE config.copy_status IS $$
442 * Copyright (C) 2005 Georgia Public Library Service
443 * Mike Rylander <mrylander@gmail.com>
447 * The available copy statuses, and whether a copy in that
448 * status is available for hold request capture. 0 (zero) is
449 * the only special number in this set, meaning that the item
450 * is available for immediate checkout, and is counted as available
453 * Statuses with an ID below 100 are not removable, and have special
454 * meaning in the code. Do not change them except to translate the
457 * You may add and remove statuses above 100, and these can be used
458 * to remove items from normal circulation without affecting the rest
459 * of the copy's values or its location.
463 * This program is free software; you can redistribute it and/or
464 * modify it under the terms of the GNU General Public License
465 * as published by the Free Software Foundation; either version 2
466 * of the License, or (at your option) any later version.
468 * This program is distributed in the hope that it will be useful,
469 * but WITHOUT ANY WARRANTY; without even the implied warranty of
470 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
471 * GNU General Public License for more details.
475 CREATE TABLE config.net_access_level (
476 id SERIAL PRIMARY KEY,
477 name TEXT NOT NULL UNIQUE
479 COMMENT ON TABLE config.net_access_level IS $$
481 * Copyright (C) 2005 Georgia Public Library Service
482 * Mike Rylander <mrylander@gmail.com>
484 * Patron Network Access level
486 * This will be used to inform the in-library firewall of how much
487 * internet access the using patron should be allowed.
491 * This program is free software; you can redistribute it and/or
492 * modify it under the terms of the GNU General Public License
493 * as published by the Free Software Foundation; either version 2
494 * of the License, or (at your option) any later version.
496 * This program is distributed in the hope that it will be useful,
497 * but WITHOUT ANY WARRANTY; without even the implied warranty of
498 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
499 * GNU General Public License for more details.
504 CREATE TABLE config.remote_account (
505 id SERIAL PRIMARY KEY,
507 host TEXT NOT NULL, -- name or IP, :port optional
508 username TEXT, -- optional, since we could default to $USER
509 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
510 account TEXT, -- aka profile or FTP "account" command
511 path TEXT, -- aka directory
512 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
513 last_activity TIMESTAMP WITH TIME ZONE
516 CREATE TABLE config.audience_map (
517 code TEXT PRIMARY KEY,
522 CREATE TABLE config.lit_form_map (
523 code TEXT PRIMARY KEY,
528 CREATE TABLE config.language_map (
529 code TEXT PRIMARY KEY,
533 CREATE TABLE config.item_form_map (
534 code TEXT PRIMARY KEY,
538 CREATE TABLE config.item_type_map (
539 code TEXT PRIMARY KEY,
543 CREATE TABLE config.bib_level_map (
544 code TEXT PRIMARY KEY,
548 CREATE TABLE config.marc21_rec_type_map (
549 code TEXT PRIMARY KEY,
550 type_val TEXT NOT NULL,
551 blvl_val TEXT NOT NULL
554 CREATE TABLE config.marc21_ff_pos_map (
555 id SERIAL PRIMARY KEY,
556 fixed_field TEXT NOT NULL,
558 rec_type TEXT NOT NULL,
559 start_pos INT NOT NULL,
561 default_val TEXT NOT NULL DEFAULT ' '
564 CREATE TABLE config.marc21_physical_characteristic_type_map (
565 ptype_key TEXT PRIMARY KEY,
566 label TEXT NOT NULL -- I18N
569 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
570 id SERIAL PRIMARY KEY,
571 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
572 subfield TEXT NOT NULL,
573 start_pos INT NOT NULL,
575 label TEXT NOT NULL -- I18N
578 CREATE TABLE config.marc21_physical_characteristic_value_map (
579 id SERIAL PRIMARY KEY,
581 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
582 label TEXT NOT NULL -- I18N
586 CREATE TABLE config.z3950_source (
587 name TEXT PRIMARY KEY,
588 label TEXT NOT NULL UNIQUE,
592 record_format TEXT NOT NULL DEFAULT 'FI',
593 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
594 auth BOOL NOT NULL DEFAULT TRUE
597 COMMENT ON TABLE config.z3950_source IS $$
600 Each row in this table represents a database searchable via Z39.50.
603 COMMENT ON COLUMN config.z3950_source.record_format IS $$
607 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
608 Z39.50 preferred record syntax..
612 CREATE TABLE config.z3950_attr (
613 id SERIAL PRIMARY KEY,
614 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
619 truncation INT NOT NULL DEFAULT 0,
620 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
623 CREATE TABLE config.i18n_locale (
624 code TEXT PRIMARY KEY,
625 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
626 name TEXT UNIQUE NOT NULL,
630 CREATE TABLE config.i18n_core (
631 id BIGSERIAL PRIMARY KEY,
632 fq_field TEXT NOT NULL,
633 identity_value TEXT NOT NULL,
634 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
638 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
640 CREATE TABLE config.billing_type (
641 id SERIAL PRIMARY KEY,
643 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
644 default_price NUMERIC(6,2),
645 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
648 CREATE TABLE config.org_unit_setting_type (
649 name TEXT PRIMARY KEY,
650 label TEXT UNIQUE NOT NULL,
652 datatype TEXT NOT NULL DEFAULT 'string',
657 -- define valid datatypes
659 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
660 ( 'bool', 'integer', 'float', 'currency', 'interval',
661 'date', 'string', 'object', 'array', 'link' ) ),
663 -- fm_class is meaningful only for 'link' datatype
665 CONSTRAINT coust_no_empty_link CHECK
666 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
667 ( datatype <> 'link' AND fm_class IS NULL ) )
670 CREATE TABLE config.usr_setting_type (
672 name TEXT PRIMARY KEY,
673 opac_visible BOOL NOT NULL DEFAULT FALSE,
674 label TEXT UNIQUE NOT NULL,
676 datatype TEXT NOT NULL DEFAULT 'string',
680 -- define valid datatypes
682 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
683 ( 'bool', 'integer', 'float', 'currency', 'interval',
684 'date', 'string', 'object', 'array', 'link' ) ),
687 -- fm_class is meaningful only for 'link' datatype
689 CONSTRAINT coust_no_empty_link CHECK
690 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
691 ( datatype <> 'link' AND fm_class IS NULL ) )
695 -- Some handy functions, based on existing ones, to provide optional ingest normalization
697 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
698 SELECT SUBSTRING($1,$2);
699 $func$ LANGUAGE SQL STRICT IMMUTABLE;
701 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
702 SELECT SUBSTRING($1,1,$2);
703 $func$ LANGUAGE SQL STRICT IMMUTABLE;
705 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
706 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
707 $func$ LANGUAGE SQL STRICT IMMUTABLE;
709 -- And ... a table in which to register them
711 CREATE TABLE config.index_normalizer (
712 id SERIAL PRIMARY KEY,
713 name TEXT UNIQUE NOT NULL,
716 param_count INT NOT NULL DEFAULT 0
719 CREATE TABLE config.metabib_field_index_norm_map (
720 id SERIAL PRIMARY KEY,
721 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
722 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
724 pos INT NOT NULL DEFAULT 0
727 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
734 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
736 SELECT n.func AS func,
737 n.param_count AS param_count,
739 FROM config.index_normalizer n
740 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
741 WHERE field = NEW.field
744 EXECUTE 'SELECT ' || normalizer.func || '(' ||
745 quote_literal( value ) ||
747 WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
755 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
756 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
758 NEW.index_vector = to_tsvector(TG_ARGV[0], value);