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.upgrade_log (
59 version TEXT PRIMARY KEY,
60 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
63 INSERT INTO config.upgrade_log (version) VALUES ('0219'); -- Scott McKellar
65 CREATE TABLE config.bib_source (
66 id SERIAL PRIMARY KEY,
67 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
68 source TEXT NOT NULL UNIQUE,
69 transcendant BOOL NOT NULL DEFAULT FALSE
71 COMMENT ON TABLE config.bib_source IS $$
73 * Copyright (C) 2005 Georgia Public Library Service
74 * Mike Rylander <mrylander@gmail.com>
76 * Valid sources of MARC records
78 * This is table is used to set up the relative "quality" of each
79 * MARC source, such as OCLC.
83 * This program is free software; you can redistribute it and/or
84 * modify it under the terms of the GNU General Public License
85 * as published by the Free Software Foundation; either version 2
86 * of the License, or (at your option) any later version.
88 * This program is distributed in the hope that it will be useful,
89 * but WITHOUT ANY WARRANTY; without even the implied warranty of
90 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
91 * GNU General Public License for more details.
95 CREATE TABLE config.standing (
96 id SERIAL PRIMARY KEY,
97 value TEXT NOT NULL UNIQUE
99 COMMENT ON TABLE config.standing IS $$
101 * Copyright (C) 2005 Georgia Public Library Service
102 * Mike Rylander <mrylander@gmail.com>
106 * This table contains the values that can be applied to a patron
107 * by a staff member. These values should not be changed, other
108 * than for translation, as the ID column is currently a "magic
109 * number" in the source. :(
113 * This program is free software; you can redistribute it and/or
114 * modify it under the terms of the GNU General Public License
115 * as published by the Free Software Foundation; either version 2
116 * of the License, or (at your option) any later version.
118 * This program is distributed in the hope that it will be useful,
119 * but WITHOUT ANY WARRANTY; without even the implied warranty of
120 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
121 * GNU General Public License for more details.
125 CREATE TABLE config.standing_penalty (
126 id SERIAL PRIMARY KEY,
127 name TEXT NOT NULL UNIQUE,
132 INSERT INTO config.standing_penalty (id,name,label,block_list)
133 VALUES (1,'PATRON_EXCEEDS_FINES','Patron exceeds fine threshold','CIRC|HOLD|RENEW');
134 INSERT INTO config.standing_penalty (id,name,label,block_list)
135 VALUES (2,'PATRON_EXCEEDS_OVERDUE_COUNT','Patron exceeds max overdue item threshold','CIRC|HOLD|RENEW');
136 INSERT INTO config.standing_penalty (id,name,label,block_list)
137 VALUES (3,'PATRON_EXCEEDS_CHECKOUT_COUNT','Patron exceeds max checked out item threshold','CIRC');
138 INSERT INTO config.standing_penalty (id,name,label,block_list)
139 VALUES (4,'PATRON_EXCEEDS_COLLECTIONS_WARNING','Patron exceeds pre-collections warning fine threshold','CIRC|HOLD|RENEW');
141 INSERT INTO config.standing_penalty (id,name,label) VALUES (20,'ALERT_NOTE','Alerting Note, no blocks');
142 INSERT INTO config.standing_penalty (id,name,label) VALUES (21,'SILENT_NOTE','Note, no blocks');
143 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (22,'STAFF_C','Alerting block on Circ','CIRC');
144 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (23,'STAFF_CH','Alerting block on Circ and Hold','CIRC|HOLD');
145 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (24,'STAFF_CR','Alerting block on Circ and Renew','CIRC|RENEW');
146 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (25,'STAFF_CHR','Alerting block on Circ, Hold and Renew','CIRC|HOLD|RENEW');
147 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (26,'STAFF_HR','Alerting block on Hold and Renew','HOLD|RENEW');
148 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (27,'STAFF_H','Alerting block on Hold','HOLD');
149 INSERT INTO config.standing_penalty (id,name,label,block_list) VALUES (28,'STAFF_R','Alerting block on Renew','RENEW');
150 INSERT INTO config.standing_penalty (id,name,label) VALUES (29,'INVALID_PATRON_ADDRESS','Patron has an invalid address');
151 INSERT INTO config.standing_penalty (id,name,label) VALUES (30,'PATRON_IN_COLLECTIONS','Patron has been referred to a collections agency');
153 SELECT SETVAL('config.standing_penalty_id_seq', 100);
155 CREATE TABLE config.xml_transform (
156 name TEXT PRIMARY KEY,
157 namespace_uri TEXT NOT NULL,
158 prefix TEXT NOT NULL,
162 CREATE TABLE config.biblio_fingerprint (
163 id SERIAL PRIMARY KEY,
166 first_word BOOL NOT NULL DEFAULT FALSE,
167 format TEXT NOT NULL DEFAULT 'marcxml'
170 INSERT INTO config.biblio_fingerprint (name, xpath, format)
173 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
174 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
175 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
176 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
177 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
181 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
184 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
185 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
186 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
187 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
188 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
193 CREATE TABLE config.metabib_class (
194 name TEXT PRIMARY KEY,
195 label TEXT NOT NULL UNIQUE
198 CREATE TABLE config.metabib_field (
199 id SERIAL PRIMARY KEY,
200 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
204 weight INT NOT NULL DEFAULT 1,
205 format TEXT NOT NULL DEFAULT 'mods33',
206 search_field BOOL NOT NULL DEFAULT TRUE,
207 facet_field BOOL NOT NULL DEFAULT FALSE,
210 COMMENT ON TABLE config.metabib_field IS $$
212 * Copyright (C) 2005 Georgia Public Library Service
213 * Mike Rylander <mrylander@gmail.com>
215 * XPath used for record indexing ingest
217 * This table contains the XPath used to chop up MODS into its
218 * indexable parts. Each XPath entry is named and assigned to
219 * a "class" of either title, subject, author, keyword or series.
224 * This program is free software; you can redistribute it and/or
225 * modify it under the terms of the GNU General Public License
226 * as published by the Free Software Foundation; either version 2
227 * of the License, or (at your option) any later version.
229 * This program is distributed in the hope that it will be useful,
230 * but WITHOUT ANY WARRANTY; without even the implied warranty of
231 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
232 * GNU General Public License for more details.
236 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
238 CREATE TABLE config.metabib_search_alias (
239 alias TEXT PRIMARY KEY,
240 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
241 field INT REFERENCES config.metabib_field (id)
244 CREATE TABLE config.non_cataloged_type (
245 id SERIAL PRIMARY KEY,
246 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
248 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
249 in_house BOOL NOT NULL DEFAULT FALSE,
250 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
252 COMMENT ON TABLE config.non_cataloged_type IS $$
254 * Copyright (C) 2005 Georgia Public Library Service
255 * Mike Rylander <mrylander@gmail.com>
257 * Types of valid non-cataloged items.
262 * This program is free software; you can redistribute it and/or
263 * modify it under the terms of the GNU General Public License
264 * as published by the Free Software Foundation; either version 2
265 * of the License, or (at your option) any later version.
267 * This program is distributed in the hope that it will be useful,
268 * but WITHOUT ANY WARRANTY; without even the implied warranty of
269 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
270 * GNU General Public License for more details.
274 CREATE TABLE config.identification_type (
275 id SERIAL PRIMARY KEY,
276 name TEXT NOT NULL UNIQUE
278 COMMENT ON TABLE config.identification_type IS $$
280 * Copyright (C) 2005 Georgia Public Library Service
281 * Mike Rylander <mrylander@gmail.com>
283 * Types of valid patron identification.
285 * Each patron must display at least one valid form of identification
286 * in order to get a library card. This table lists those forms.
291 * This program is free software; you can redistribute it and/or
292 * modify it under the terms of the GNU General Public License
293 * as published by the Free Software Foundation; either version 2
294 * of the License, or (at your option) any later version.
296 * This program is distributed in the hope that it will be useful,
297 * but WITHOUT ANY WARRANTY; without even the implied warranty of
298 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
299 * GNU General Public License for more details.
303 CREATE TABLE config.rule_circ_duration (
304 id SERIAL PRIMARY KEY,
305 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
306 extended INTERVAL NOT NULL,
307 normal INTERVAL NOT NULL,
308 shrt INTERVAL NOT NULL,
309 max_renewals INT NOT NULL
311 COMMENT ON TABLE config.rule_circ_duration IS $$
313 * Copyright (C) 2005 Georgia Public Library Service
314 * Mike Rylander <mrylander@gmail.com>
316 * Circulation Duration rules
318 * Each circulation is given a duration based on one of these rules.
323 * This program is free software; you can redistribute it and/or
324 * modify it under the terms of the GNU General Public License
325 * as published by the Free Software Foundation; either version 2
326 * of the License, or (at your option) any later version.
328 * This program is distributed in the hope that it will be useful,
329 * but WITHOUT ANY WARRANTY; without even the implied warranty of
330 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
331 * GNU General Public License for more details.
335 CREATE TABLE config.rule_max_fine (
336 id SERIAL PRIMARY KEY,
337 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
338 amount NUMERIC(6,2) NOT NULL,
339 is_percent BOOL NOT NULL DEFAULT FALSE
341 COMMENT ON TABLE config.rule_max_fine IS $$
343 * Copyright (C) 2005 Georgia Public Library Service
344 * Mike Rylander <mrylander@gmail.com>
346 * Circulation Max Fine rules
348 * Each circulation is given a maximum fine based on one of
354 * This program is free software; you can redistribute it and/or
355 * modify it under the terms of the GNU General Public License
356 * as published by the Free Software Foundation; either version 2
357 * of the License, or (at your option) any later version.
359 * This program is distributed in the hope that it will be useful,
360 * but WITHOUT ANY WARRANTY; without even the implied warranty of
361 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
362 * GNU General Public License for more details.
366 CREATE TABLE config.rule_recurring_fine (
367 id SERIAL PRIMARY KEY,
368 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
369 high NUMERIC(6,2) NOT NULL,
370 normal NUMERIC(6,2) NOT NULL,
371 low NUMERIC(6,2) NOT NULL,
372 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
374 COMMENT ON TABLE config.rule_recurring_fine IS $$
376 * Copyright (C) 2005 Georgia Public Library Service
377 * Mike Rylander <mrylander@gmail.com>
379 * Circulation Recurring Fine rules
381 * Each circulation is given a recurring fine amount based on one of
382 * these rules. The recurrence_interval should not be any shorter
383 * than the interval between runs of the fine_processor.pl script
384 * (which is run from CRON), or you could miss fines.
389 * This program is free software; you can redistribute it and/or
390 * modify it under the terms of the GNU General Public License
391 * as published by the Free Software Foundation; either version 2
392 * of the License, or (at your option) any later version.
394 * This program is distributed in the hope that it will be useful,
395 * but WITHOUT ANY WARRANTY; without even the implied warranty of
396 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
397 * GNU General Public License for more details.
402 CREATE TABLE config.rule_age_hold_protect (
403 id SERIAL PRIMARY KEY,
404 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
405 age INTERVAL NOT NULL,
408 COMMENT ON TABLE config.rule_age_hold_protect IS $$
410 * Copyright (C) 2005 Georgia Public Library Service
411 * Mike Rylander <mrylander@gmail.com>
413 * Hold Item Age Protection rules
415 * A hold request can only capture new(ish) items when they are
416 * within a particular proximity of the home_ou of the requesting
417 * user. The proximity ('prox' column) is calculated by counting
418 * the number of tree edges between the user's home_ou and the owning_lib
419 * of the copy that could fulfill the hold.
424 * This program is free software; you can redistribute it and/or
425 * modify it under the terms of the GNU General Public License
426 * as published by the Free Software Foundation; either version 2
427 * of the License, or (at your option) any later version.
429 * This program is distributed in the hope that it will be useful,
430 * but WITHOUT ANY WARRANTY; without even the implied warranty of
431 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
432 * GNU General Public License for more details.
436 CREATE TABLE config.copy_status (
437 id SERIAL PRIMARY KEY,
438 name TEXT NOT NULL UNIQUE,
439 holdable BOOL NOT NULL DEFAULT FALSE,
440 opac_visible BOOL NOT NULL DEFAULT FALSE
442 COMMENT ON TABLE config.copy_status IS $$
444 * Copyright (C) 2005 Georgia Public Library Service
445 * Mike Rylander <mrylander@gmail.com>
449 * The available copy statuses, and whether a copy in that
450 * status is available for hold request capture. 0 (zero) is
451 * the only special number in this set, meaning that the item
452 * is available for immediate checkout, and is counted as available
455 * Statuses with an ID below 100 are not removable, and have special
456 * meaning in the code. Do not change them except to translate the
459 * You may add and remove statuses above 100, and these can be used
460 * to remove items from normal circulation without affecting the rest
461 * of the copy's values or its location.
465 * This program is free software; you can redistribute it and/or
466 * modify it under the terms of the GNU General Public License
467 * as published by the Free Software Foundation; either version 2
468 * of the License, or (at your option) any later version.
470 * This program is distributed in the hope that it will be useful,
471 * but WITHOUT ANY WARRANTY; without even the implied warranty of
472 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
473 * GNU General Public License for more details.
477 CREATE TABLE config.net_access_level (
478 id SERIAL PRIMARY KEY,
479 name TEXT NOT NULL UNIQUE
481 COMMENT ON TABLE config.net_access_level IS $$
483 * Copyright (C) 2005 Georgia Public Library Service
484 * Mike Rylander <mrylander@gmail.com>
486 * Patron Network Access level
488 * This will be used to inform the in-library firewall of how much
489 * internet access the using patron should be allowed.
493 * This program is free software; you can redistribute it and/or
494 * modify it under the terms of the GNU General Public License
495 * as published by the Free Software Foundation; either version 2
496 * of the License, or (at your option) any later version.
498 * This program is distributed in the hope that it will be useful,
499 * but WITHOUT ANY WARRANTY; without even the implied warranty of
500 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
501 * GNU General Public License for more details.
506 CREATE TABLE config.remote_account (
507 id SERIAL PRIMARY KEY,
509 host TEXT NOT NULL, -- name or IP, :port optional
510 username TEXT, -- optional, since we could default to $USER
511 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
512 account TEXT, -- aka profile or FTP "account" command
513 path TEXT, -- aka directory
514 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
515 last_activity TIMESTAMP WITH TIME ZONE
518 CREATE TABLE config.audience_map (
519 code TEXT PRIMARY KEY,
524 CREATE TABLE config.lit_form_map (
525 code TEXT PRIMARY KEY,
530 CREATE TABLE config.language_map (
531 code TEXT PRIMARY KEY,
535 CREATE TABLE config.item_form_map (
536 code TEXT PRIMARY KEY,
540 CREATE TABLE config.item_type_map (
541 code TEXT PRIMARY KEY,
545 CREATE TABLE config.bib_level_map (
546 code TEXT PRIMARY KEY,
550 CREATE TABLE config.marc21_rec_type_map (
551 code TEXT PRIMARY KEY,
552 type_val TEXT NOT NULL,
553 blvl_val TEXT NOT NULL
556 CREATE TABLE config.marc21_ff_pos_map (
557 id SERIAL PRIMARY KEY,
558 fixed_field TEXT NOT NULL,
560 rec_type TEXT NOT NULL,
561 start_pos INT NOT NULL,
563 default_val TEXT NOT NULL DEFAULT ' '
566 CREATE TABLE config.marc21_physical_characteristic_type_map (
567 ptype_key TEXT PRIMARY KEY,
568 label TEXT NOT NULL -- I18N
571 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
572 id SERIAL PRIMARY KEY,
573 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
574 subfield TEXT NOT NULL,
575 start_pos INT NOT NULL,
577 label TEXT NOT NULL -- I18N
580 CREATE TABLE config.marc21_physical_characteristic_value_map (
581 id SERIAL PRIMARY KEY,
583 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
584 label TEXT NOT NULL -- I18N
588 CREATE TABLE config.z3950_source (
589 name TEXT PRIMARY KEY,
590 label TEXT NOT NULL UNIQUE,
594 record_format TEXT NOT NULL DEFAULT 'FI',
595 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
596 auth BOOL NOT NULL DEFAULT TRUE
599 COMMENT ON TABLE config.z3950_source IS $$
602 Each row in this table represents a database searchable via Z39.50.
605 COMMENT ON COLUMN config.z3950_source.record_format IS $$
609 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
610 Z39.50 preferred record syntax..
614 CREATE TABLE config.z3950_attr (
615 id SERIAL PRIMARY KEY,
616 source TEXT NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
621 truncation INT NOT NULL DEFAULT 0,
622 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
625 CREATE TABLE config.i18n_locale (
626 code TEXT PRIMARY KEY,
627 marc_code TEXT NOT NULL REFERENCES config.language_map (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
628 name TEXT UNIQUE NOT NULL,
632 CREATE TABLE config.i18n_core (
633 id BIGSERIAL PRIMARY KEY,
634 fq_field TEXT NOT NULL,
635 identity_value TEXT NOT NULL,
636 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
640 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
642 CREATE TABLE config.billing_type (
643 id SERIAL PRIMARY KEY,
645 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
646 default_price NUMERIC(6,2),
647 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
650 CREATE TABLE config.org_unit_setting_type (
651 name TEXT PRIMARY KEY,
652 label TEXT UNIQUE NOT NULL,
654 datatype TEXT NOT NULL DEFAULT 'string',
659 -- define valid datatypes
661 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
662 ( 'bool', 'integer', 'float', 'currency', 'interval',
663 'date', 'string', 'object', 'array', 'link' ) ),
665 -- fm_class is meaningful only for 'link' datatype
667 CONSTRAINT coust_no_empty_link CHECK
668 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
669 ( datatype <> 'link' AND fm_class IS NULL ) )
672 CREATE TABLE config.usr_setting_type (
674 name TEXT PRIMARY KEY,
675 opac_visible BOOL NOT NULL DEFAULT FALSE,
676 label TEXT UNIQUE NOT NULL,
678 datatype TEXT NOT NULL DEFAULT 'string',
682 -- define valid datatypes
684 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
685 ( 'bool', 'integer', 'float', 'currency', 'interval',
686 'date', 'string', 'object', 'array', 'link' ) ),
689 -- fm_class is meaningful only for 'link' datatype
691 CONSTRAINT coust_no_empty_link CHECK
692 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
693 ( datatype <> 'link' AND fm_class IS NULL ) )
697 -- Some handy functions, based on existing ones, to provide optional ingest normalization
699 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
700 SELECT SUBSTRING($1,$2);
701 $func$ LANGUAGE SQL STRICT IMMUTABLE;
703 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
704 SELECT SUBSTRING($1,1,$2);
705 $func$ LANGUAGE SQL STRICT IMMUTABLE;
707 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
708 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
709 $func$ LANGUAGE SQL STRICT IMMUTABLE;
711 -- And ... a table in which to register them
713 CREATE TABLE config.index_normalizer (
714 id SERIAL PRIMARY KEY,
715 name TEXT UNIQUE NOT NULL,
718 param_count INT NOT NULL DEFAULT 0
721 CREATE TABLE config.metabib_field_index_norm_map (
722 id SERIAL PRIMARY KEY,
723 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
724 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
726 pos INT NOT NULL DEFAULT 0
729 CREATE OR REPLACE FUNCTION oils_tsearch2 () RETURNS TRIGGER AS $$
734 IF NEW.index_vector = ''::tsvector THEN
740 IF TG_TABLE_NAME::TEXT ~ 'field_entry$' THEN
742 SELECT n.func AS func,
743 n.param_count AS param_count,
745 FROM config.index_normalizer n
746 JOIN config.metabib_field_index_norm_map m ON (m.norm = n.id)
747 WHERE field = NEW.field
750 EXECUTE 'SELECT ' || normalizer.func || '(' ||
751 quote_literal( value ) ||
753 WHEN normalizer.param_count > 0 THEN ',' || BTRIM(normalizer.params,'[]')
761 IF REGEXP_REPLACE(VERSION(),E'^.+?(\\d+\\.\\d+).*?$',E'\\1')::FLOAT > 8.2 THEN
762 NEW.index_vector = to_tsvector((TG_ARGV[0])::regconfig, value);
764 NEW.index_vector = to_tsvector(TG_ARGV[0], value);