LP#1066326: use internal flags; sync with master
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 002.schema.config.sql
1 /*
2  * Copyright (C) 2004-2008  Georgia Public Library Service
3  * Copyright (C) 2008-2011  Equinox Software, Inc.
4  * Mike Rylander <miker@esilibrary.com>
5  * Copyright (C) 2010 Merrimack Valley Library Consortium
6  * Jason Stephenson <jstephenson@mvlc.org>
7  * Copyright (C) 2010 Laurentian University
8  * Dan Scott <dscott@laurentian.ca>
9  *
10  * This program is free software; you can redistribute it and/or
11  * modify it under the terms of the GNU General Public License
12  * as published by the Free Software Foundation; either version 2
13  * of the License, or (at your option) any later version.
14  *
15  * This program is distributed in the hope that it will be useful,
16  * but WITHOUT ANY WARRANTY; without even the implied warranty of
17  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
18  * GNU General Public License for more details.
19  *
20  */
21
22
23
24 DROP SCHEMA IF EXISTS stats CASCADE;
25 DROP SCHEMA IF EXISTS config CASCADE;
26
27 BEGIN;
28 CREATE SCHEMA stats;
29
30 CREATE SCHEMA config;
31 COMMENT ON SCHEMA config IS $$
32 The config schema holds static configuration data for the
33 Evergreen installation.
34 $$;
35
36 CREATE TABLE config.internal_flag (
37     name    TEXT    PRIMARY KEY,
38     value   TEXT,
39     enabled BOOL    NOT NULL DEFAULT FALSE
40 );
41 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_insert');
42 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_update');
43 INSERT INTO config.internal_flag (name) VALUES ('ingest.reingest.force_on_same_marc');
44 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri');
45 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec');
46 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor');
47 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_field_entry');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
52 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
53
54 CREATE TABLE config.global_flag (
55     label   TEXT    NOT NULL
56 ) INHERITS (config.internal_flag);
57 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
58
59 CREATE TABLE config.upgrade_log (
60     version         TEXT    PRIMARY KEY,
61     install_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
62     applied_to      TEXT
63 );
64
65 CREATE TABLE config.db_patch_dependencies (
66   db_patch      TEXT PRIMARY KEY,
67   supersedes    TEXT[],
68   deprecates    TEXT[]
69 );
70
71 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
72 DECLARE
73     fld     TEXT;
74     cnt     INT;
75 BEGIN
76     fld := TG_ARGV[1];
77     EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
78     IF cnt > 0 THEN
79         RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
80     END IF;
81     RETURN NEW;
82 END;
83 $$ LANGUAGE PLPGSQL;
84
85 CREATE TRIGGER no_overlapping_sups
86     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
87     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
88
89 CREATE TRIGGER no_overlapping_deps
90     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
91     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
92
93 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0760', :eg_version); -- senator/miker
94
95 CREATE TABLE config.bib_source (
96         id              SERIAL  PRIMARY KEY,
97         quality         INT     CHECK ( quality BETWEEN 0 AND 100 ),
98         source          TEXT    NOT NULL UNIQUE,
99         transcendant    BOOL    NOT NULL DEFAULT FALSE,
100         can_have_copies BOOL    NOT NULL DEFAULT TRUE
101 );
102 COMMENT ON TABLE config.bib_source IS $$
103 This is table is used to set up the relative "quality" of each
104 MARC source, such as OCLC.  Also identifies "transcendant" sources,
105 i.e., sources of bib records that should display in the OPAC
106 even if no copies or located URIs are attached. Also indicates if
107 the source is allowed to have actual copies on its bibs. Volumes
108 for targeted URIs are unaffected by this setting.
109 $$;
110
111 CREATE TABLE config.standing (
112         id              SERIAL  PRIMARY KEY,
113         value           TEXT    NOT NULL UNIQUE
114 );
115 COMMENT ON TABLE config.standing IS $$
116 Patron Standings
117
118 This table contains the values that can be applied to a patron
119 by a staff member.  These values should not be changed, other
120 than for translation, as the ID column is currently a "magic
121 number" in the source. :(
122 $$;
123
124 CREATE TABLE config.standing_penalty (
125         id                      SERIAL  PRIMARY KEY,
126         name            TEXT    NOT NULL UNIQUE,
127         label           TEXT    NOT NULL,
128         block_list      TEXT,
129         staff_alert     BOOL    NOT NULL DEFAULT FALSE,
130         org_depth       INTEGER
131 );
132
133 CREATE TABLE config.xml_transform (
134         name            TEXT    PRIMARY KEY,
135         namespace_uri   TEXT    NOT NULL,
136         prefix          TEXT    NOT NULL,
137         xslt            TEXT    NOT NULL
138 );
139
140 CREATE TABLE config.biblio_fingerprint (
141         id                      SERIAL  PRIMARY KEY,
142         name            TEXT    NOT NULL, 
143         xpath           TEXT    NOT NULL,
144     first_word  BOOL    NOT NULL DEFAULT FALSE,
145         format          TEXT    NOT NULL DEFAULT 'marcxml'
146 );
147
148 INSERT INTO config.biblio_fingerprint (name, xpath, format)
149     VALUES (
150         'Title',
151         '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
152             '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
153             '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
154             '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
155             '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
156         'marcxml'
157     );
158
159 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
160     VALUES (
161         'Author',
162         '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
163             '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
164             '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
165             '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
166             '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
167         'marcxml',
168         TRUE
169     );
170
171 CREATE TABLE config.metabib_class (
172     name     TEXT    PRIMARY KEY,
173     label    TEXT    NOT NULL UNIQUE,
174     buoyant  BOOL    DEFAULT FALSE NOT NULL,
175     restrict BOOL    DEFAULT FALSE NOT NULL,
176     a_weight NUMERIC  DEFAULT 1.0 NOT NULL,
177     b_weight NUMERIC  DEFAULT 0.4 NOT NULL,
178     c_weight NUMERIC  DEFAULT 0.2 NOT NULL,
179     d_weight NUMERIC  DEFAULT 0.1 NOT NULL
180 );
181
182 CREATE TABLE config.metabib_field (
183         id              SERIAL  PRIMARY KEY,
184         field_class     TEXT    NOT NULL REFERENCES config.metabib_class (name),
185         name            TEXT    NOT NULL,
186         label           TEXT    NOT NULL,
187         xpath           TEXT    NOT NULL,
188         weight          INT     NOT NULL DEFAULT 1,
189         format          TEXT    NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
190         search_field    BOOL    NOT NULL DEFAULT TRUE,
191         facet_field     BOOL    NOT NULL DEFAULT FALSE,
192         browse_field    BOOL    NOT NULL DEFAULT TRUE,
193         browse_xpath   TEXT,
194         facet_xpath     TEXT,
195         restrict        BOOL    DEFAULT FALSE NOT NULL
196 );
197 COMMENT ON TABLE config.metabib_field IS $$
198 XPath used for record indexing ingest
199
200 This table contains the XPath used to chop up MODS into its
201 indexable parts.  Each XPath entry is named and assigned to
202 a "class" of either title, subject, author, keyword, series
203 or identifier.
204 $$;
205
206 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
207
208 CREATE TABLE config.ts_config_list (
209         id                      TEXT PRIMARY KEY,
210         name            TEXT NOT NULL
211 );
212 COMMENT ON TABLE config.ts_config_list IS $$
213 Full Text Configs
214
215 A list of full text configs with names and descriptions.
216 $$;
217
218 CREATE TABLE config.metabib_class_ts_map (
219         id                              SERIAL PRIMARY KEY,
220         field_class             TEXT NOT NULL REFERENCES config.metabib_class (name),
221         ts_config               TEXT NOT NULL REFERENCES config.ts_config_list (id),
222         active                  BOOL NOT NULL DEFAULT TRUE,
223         index_weight    CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
224         index_lang              TEXT NULL,
225         search_lang             TEXT NULL,
226         always                  BOOL NOT NULL DEFAULT true
227 );
228 COMMENT ON TABLE config.metabib_class_ts_map IS $$
229 Text Search Configs for metabib class indexing
230
231 This table contains text search config definitions for
232 storing index_vector values.
233 $$;
234
235 CREATE TABLE config.metabib_field_ts_map (
236         id                              SERIAL PRIMARY KEY,
237         metabib_field   INT NOT NULL REFERENCES config.metabib_field (id),
238         ts_config               TEXT NOT NULL REFERENCES config.ts_config_list (id),
239         active                  BOOL NOT NULL DEFAULT TRUE,
240         index_weight    CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
241         index_lang              TEXT NULL,
242         search_lang             TEXT NULL
243 );
244 COMMENT ON TABLE config.metabib_field_ts_map IS $$
245 Text Search Configs for metabib field indexing
246
247 This table contains text search config definitions for
248 storing index_vector values.
249 $$;
250
251 CREATE TABLE config.metabib_search_alias (
252     alias       TEXT    PRIMARY KEY,
253     field_class TEXT    NOT NULL REFERENCES config.metabib_class (name),
254     field       INT     REFERENCES config.metabib_field (id)
255 );
256
257 CREATE TABLE config.non_cataloged_type (
258         id              SERIAL          PRIMARY KEY,
259         owning_lib      INT             NOT NULL, -- REFERENCES actor.org_unit (id),
260         name            TEXT            NOT NULL,
261         circ_duration   INTERVAL        NOT NULL DEFAULT '14 days'::INTERVAL,
262         in_house        BOOL            NOT NULL DEFAULT FALSE,
263         CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
264 );
265 COMMENT ON TABLE config.non_cataloged_type IS $$
266 Types of valid non-cataloged items.
267 $$;
268
269 CREATE TABLE config.identification_type (
270         id              SERIAL  PRIMARY KEY,
271         name            TEXT    NOT NULL UNIQUE
272 );
273 COMMENT ON TABLE config.identification_type IS $$
274 Types of valid patron identification.
275
276 Each patron must display at least one valid form of identification
277 in order to get a library card.  This table lists those forms.
278 $$;
279
280 CREATE TABLE config.rule_circ_duration (
281         id              SERIAL          PRIMARY KEY,
282         name            TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
283         extended        INTERVAL        NOT NULL,
284         normal          INTERVAL        NOT NULL,
285         shrt            INTERVAL        NOT NULL,
286         max_renewals    INT             NOT NULL
287 );
288 COMMENT ON TABLE config.rule_circ_duration IS $$
289 Circulation Duration rules
290
291 Each circulation is given a duration based on one of these rules.
292 $$;
293
294 CREATE TABLE config.hard_due_date (
295     id                  SERIAL      PRIMARY KEY,
296     name                TEXT        NOT NULL UNIQUE,
297     ceiling_date        TIMESTAMPTZ NOT NULL,
298     forceto             BOOL        NOT NULL,
299     owner               INT         NOT NULL   -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
300 );
301
302 CREATE TABLE config.hard_due_date_values (
303     id                  SERIAL      PRIMARY KEY,
304     hard_due_date       INT         NOT NULL REFERENCES config.hard_due_date (id)
305                                     DEFERRABLE INITIALLY DEFERRED,
306     ceiling_date        TIMESTAMPTZ NOT NULL,
307     active_date         TIMESTAMPTZ NOT NULL
308 );
309
310 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
311 DECLARE
312     temp_value  config.hard_due_date_values%ROWTYPE;
313     updated     INT := 0;
314 BEGIN
315     FOR temp_value IN
316       SELECT  DISTINCT ON (hard_due_date) *
317         FROM  config.hard_due_date_values
318         WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
319         ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
320    LOOP
321         UPDATE  config.hard_due_date
322           SET   ceiling_date = temp_value.ceiling_date
323           WHERE id = temp_value.hard_due_date
324                 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
325
326         IF FOUND THEN
327             updated := updated + 1;
328         END IF;
329     END LOOP;
330
331     RETURN updated;
332 END;
333 $func$ LANGUAGE plpgsql;
334
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
340 );
341 COMMENT ON TABLE config.rule_max_fine IS $$
342 Circulation Max Fine rules
343
344 Each circulation is given a maximum fine based on one of
345 these rules.
346 $$;
347
348 CREATE TABLE config.rule_recurring_fine (
349         id                      SERIAL          PRIMARY KEY,
350         name                    TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
351         high                    NUMERIC(6,2)    NOT NULL,
352         normal                  NUMERIC(6,2)    NOT NULL,
353         low                     NUMERIC(6,2)    NOT NULL,
354         recurrence_interval     INTERVAL        NOT NULL DEFAULT '1 day'::INTERVAL,
355     grace_period       INTERVAL         NOT NULL DEFAULT '1 day'::INTERVAL
356 );
357 COMMENT ON TABLE config.rule_recurring_fine IS $$
358 Circulation Recurring Fine rules
359
360 Each circulation is given a recurring fine amount based on one of
361 these rules.  Note that it is recommended to run the fine generator
362 (from cron) at least as frequently as the lowest recurrence interval
363 used by your circulation rules so that accrued fines will be up
364 to date.
365 $$;
366
367
368 CREATE TABLE config.rule_age_hold_protect (
369         id      SERIAL          PRIMARY KEY,
370         name    TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
371         age     INTERVAL        NOT NULL,
372         prox    INT             NOT NULL
373 );
374 COMMENT ON TABLE config.rule_age_hold_protect IS $$
375 Hold Item Age Protection rules
376
377 A hold request can only capture new(ish) items when they are
378 within a particular proximity of the pickup_lib of the request.
379 The proximity ('prox' column) is calculated by counting
380 the number of tree edges between the pickup_lib and either the
381 owning_lib or circ_lib of the copy that could fulfill the hold,
382 as determined by the distance_is_from_owner value of the hold matrix
383 rule controlling the hold request.
384 $$;
385
386 CREATE TABLE config.copy_status (
387         id              SERIAL  PRIMARY KEY,
388         name            TEXT    NOT NULL UNIQUE,
389         holdable        BOOL    NOT NULL DEFAULT FALSE,
390         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
391     copy_active  BOOL    NOT NULL DEFAULT FALSE,
392         restrict_copy_delete BOOL         NOT NULL DEFAULT FALSE
393 );
394 COMMENT ON TABLE config.copy_status IS $$
395 Copy Statuses
396
397 The available copy statuses, and whether a copy in that
398 status is available for hold request capture.  0 (zero) is
399 the only special number in this set, meaning that the item
400 is available for immediate checkout, and is counted as available
401 in the OPAC.
402
403 Statuses with an ID below 100 are not removable, and have special
404 meaning in the code.  Do not change them except to translate the
405 textual name.
406
407 You may add and remove statuses above 100, and these can be used
408 to remove items from normal circulation without affecting the rest
409 of the copy's values or its location.
410 $$;
411
412 CREATE TABLE config.net_access_level (
413         id      SERIAL          PRIMARY KEY,
414         name    TEXT            NOT NULL UNIQUE
415 );
416 COMMENT ON TABLE config.net_access_level IS $$
417 Patron Network Access level
418
419 This will be used to inform the in-library firewall of how much
420 internet access the using patron should be allowed.
421 $$;
422
423
424 CREATE TABLE config.remote_account (
425     id          SERIAL  PRIMARY KEY,
426     label       TEXT    NOT NULL,
427     host        TEXT    NOT NULL,   -- name or IP, :port optional
428     username    TEXT,               -- optional, since we could default to $USER
429     password    TEXT,               -- optional, since we could use SSH keys, or anonymous login.
430     account     TEXT,               -- aka profile or FTP "account" command
431     path        TEXT,               -- aka directory
432     owner       INT     NOT NULL,   -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
433     last_activity TIMESTAMP WITH TIME ZONE
434 );
435
436 CREATE TABLE config.marc21_rec_type_map (
437     code        TEXT    PRIMARY KEY,
438     type_val    TEXT    NOT NULL,
439     blvl_val    TEXT    NOT NULL
440 );
441
442 CREATE TABLE config.marc21_ff_pos_map (
443     id          SERIAL  PRIMARY KEY,
444     fixed_field TEXT    NOT NULL,
445     tag         TEXT    NOT NULL,
446     rec_type    TEXT    NOT NULL,
447     start_pos   INT     NOT NULL,
448     length      INT     NOT NULL,
449     default_val TEXT    NOT NULL DEFAULT ' '
450 );
451
452 CREATE TABLE config.marc21_physical_characteristic_type_map (
453     ptype_key   TEXT    PRIMARY KEY,
454     label       TEXT    NOT NULL -- I18N
455 );
456
457 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
458     id          SERIAL  PRIMARY KEY,
459     ptype_key   TEXT    NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
460     subfield    TEXT    NOT NULL,
461     start_pos   INT     NOT NULL,
462     length      INT     NOT NULL,
463     label       TEXT    NOT NULL -- I18N
464 );
465
466 CREATE TABLE config.marc21_physical_characteristic_value_map (
467     id              SERIAL  PRIMARY KEY,
468     value           TEXT    NOT NULL,
469     ptype_subfield  INT     NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
470     label           TEXT    NOT NULL -- I18N
471 );
472
473
474 CREATE TABLE config.z3950_source (
475     name                TEXT    PRIMARY KEY,
476     label               TEXT    NOT NULL UNIQUE,
477     host                TEXT    NOT NULL,
478     port                INT     NOT NULL,
479     db                  TEXT    NOT NULL,
480     record_format       TEXT    NOT NULL DEFAULT 'FI',
481     transmission_format TEXT    NOT NULL DEFAULT 'usmarc',
482     auth                BOOL    NOT NULL DEFAULT TRUE,
483     use_perm            INT     -- REFERENCES permission.perm_list (id)
484 );
485
486 COMMENT ON TABLE config.z3950_source IS $$
487 Z39.50 Sources
488
489 Each row in this table represents a database searchable via Z39.50.
490 $$;
491
492 COMMENT ON COLUMN config.z3950_source.record_format IS $$
493 Z39.50 element set.
494 $$;
495
496 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
497 Z39.50 preferred record syntax..
498 $$;
499
500 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
501 If set, this permission is required for the source to be listed in the staff
502 client Z39.50 interface.  Similar to permission.grp_tree.application_perm.
503 $$;
504
505 CREATE TABLE config.z3950_attr (
506     id          SERIAL  PRIMARY KEY,
507     source      TEXT    NOT NULL REFERENCES config.z3950_source (name) DEFERRABLE INITIALLY DEFERRED,
508     name        TEXT    NOT NULL,
509     label       TEXT    NOT NULL,
510     code        INT     NOT NULL,
511     format      INT     NOT NULL,
512     truncation  INT     NOT NULL DEFAULT 0,
513     CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
514 );
515
516 CREATE TABLE config.i18n_locale (
517     code        TEXT    PRIMARY KEY,
518     marc_code   TEXT    NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
519     name        TEXT    UNIQUE NOT NULL,
520     description TEXT
521 );
522
523 CREATE TABLE config.i18n_core (
524     id              BIGSERIAL   PRIMARY KEY,
525     fq_field        TEXT        NOT NULL,
526     identity_value  TEXT        NOT NULL,
527     translation     TEXT        NOT NULL    REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
528     string          TEXT        NOT NULL
529 );
530
531 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
532
533 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
534 BEGIN
535
536     EXECUTE $$
537         UPDATE  config.i18n_core
538           SET   identity_value = $$ || quote_literal(new_ident) || $$ 
539           WHERE fq_field LIKE '$$ || hint || $$.%' 
540                 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
541
542     RETURN;
543
544 END;
545 $_$ LANGUAGE PLPGSQL;
546
547 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
548 BEGIN
549     PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
550     RETURN NEW;
551 END;
552 $_$ LANGUAGE PLPGSQL;
553
554 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
555 BEGIN
556     PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
557     RETURN NEW;
558 END;
559 $_$ LANGUAGE PLPGSQL;
560
561 CREATE TABLE config.billing_type (
562     id              SERIAL  PRIMARY KEY,
563     name            TEXT    NOT NULL,
564     owner           INT     NOT NULL, -- REFERENCES actor.org_unit (id)
565     default_price   NUMERIC(6,2),
566     CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
567 );
568
569 CREATE TABLE config.settings_group (
570     name    TEXT PRIMARY KEY,
571     label   TEXT UNIQUE NOT NULL -- I18N
572 );
573
574 CREATE TABLE config.org_unit_setting_type (
575     name            TEXT    PRIMARY KEY,
576     label           TEXT    UNIQUE NOT NULL,
577     grp             TEXT    REFERENCES config.settings_group (name),
578     description     TEXT,
579     datatype        TEXT    NOT NULL DEFAULT 'string',
580     fm_class        TEXT,
581     view_perm       INT,
582     update_perm     INT,
583     --
584     -- define valid datatypes
585     --
586     CONSTRAINT coust_valid_datatype CHECK ( datatype IN
587     ( 'bool', 'integer', 'float', 'currency', 'interval',
588       'date', 'string', 'object', 'array', 'link' ) ),
589     --
590     -- fm_class is meaningful only for 'link' datatype
591     --
592     CONSTRAINT coust_no_empty_link CHECK
593     ( ( datatype =  'link' AND fm_class IS NOT NULL ) OR
594       ( datatype <> 'link' AND fm_class IS NULL ) )
595 );
596
597 CREATE TABLE config.usr_setting_type (
598
599     name TEXT PRIMARY KEY,
600     opac_visible BOOL NOT NULL DEFAULT FALSE,
601     label TEXT UNIQUE NOT NULL,
602     description TEXT,
603     grp             TEXT    REFERENCES config.settings_group (name),
604     datatype TEXT NOT NULL DEFAULT 'string',
605     fm_class TEXT,
606
607     --
608     -- define valid datatypes
609     --
610     CONSTRAINT coust_valid_datatype CHECK ( datatype IN
611     ( 'bool', 'integer', 'float', 'currency', 'interval',
612         'date', 'string', 'object', 'array', 'link' ) ),
613
614     --
615     -- fm_class is meaningful only for 'link' datatype
616     --
617     CONSTRAINT coust_no_empty_link CHECK
618     ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
619         ( datatype <> 'link' AND fm_class IS NULL ) )
620
621 );
622
623 -- Some handy functions, based on existing ones, to provide optional ingest normalization
624
625 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
626         SELECT SUBSTRING($1,$2);
627 $func$ LANGUAGE SQL STRICT IMMUTABLE;
628
629 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
630         SELECT SUBSTRING($1,1,$2);
631 $func$ LANGUAGE SQL STRICT IMMUTABLE;
632
633 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
634         SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
635 $func$ LANGUAGE SQL STRICT IMMUTABLE;
636
637 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
638         SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
639 $func$ LANGUAGE SQL STRICT IMMUTABLE;
640
641 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
642         SELECT approximate_date( $1, '0');
643 $func$ LANGUAGE SQL STRICT IMMUTABLE;
644
645 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
646         SELECT approximate_date( $1, '9');
647 $func$ LANGUAGE SQL STRICT IMMUTABLE;
648
649 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
650         SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
651 $func$ LANGUAGE SQL STRICT IMMUTABLE;
652
653 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
654         SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
655 $func$ LANGUAGE SQL STRICT IMMUTABLE;
656
657 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
658     use Business::ISBN;
659     use strict;
660     use warnings;
661
662     # Find the first ISBN, force it to ISBN13 and return it
663
664     my $input = shift;
665
666     foreach my $word (split(/\s/, $input)) {
667         my $isbn = Business::ISBN->new($word);
668
669         # First check the checksum; if it is not valid, fix it and add the original
670         # bad-checksum ISBN to the output
671         if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
672             $isbn->fix_checksum();
673         }
674
675         # If we now have a valid ISBN, force it to ISBN13 and return it
676         return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
677     }
678     return undef;
679 $func$ LANGUAGE PLPERLU;
680
681 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
682 Inspired by translate_isbn1013
683
684 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
685 version without hypens and with a repaired checksum if the checksum was bad
686 $$;
687
688
689 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
690     use Business::ISBN;
691     use strict;
692     use warnings;
693
694     # For each ISBN found in a single string containing a set of ISBNs:
695     #   * Normalize an incoming ISBN to have the correct checksum and no hyphens
696     #   * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
697
698     my $input = shift;
699     my $output = '';
700
701     foreach my $word (split(/\s/, $input)) {
702         my $isbn = Business::ISBN->new($word);
703
704         # First check the checksum; if it is not valid, fix it and add the original
705         # bad-checksum ISBN to the output
706         if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
707             $output .= $isbn->isbn() . " ";
708             $isbn->fix_checksum();
709         }
710
711         # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
712         # and add the normalized original ISBN to the output
713         if ($isbn && $isbn->is_valid()) {
714             my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
715             $output .= $isbn->isbn . " ";
716
717             # If we successfully converted the ISBN to its counterpart, add the
718             # converted ISBN to the output as well
719             $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
720         }
721     }
722     return $output if $output;
723
724     # If there were no valid ISBNs, just return the raw input
725     return $input;
726 $func$ LANGUAGE PLPERLU;
727
728 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
729 The translate_isbn1013 function takes an input ISBN and returns the
730 following in a single space-delimited string if the input ISBN is valid:
731   - The normalized input ISBN (hyphens stripped)
732   - The normalized input ISBN with a fixed checksum if the checksum was bad
733   - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
734 $$;
735
736 -- And ... a table in which to register them
737
738 CREATE TABLE config.index_normalizer (
739         id              SERIAL  PRIMARY KEY,
740         name            TEXT    UNIQUE NOT NULL,
741         description     TEXT,
742         func            TEXT    NOT NULL,
743         param_count     INT     NOT NULL DEFAULT 0
744 );
745
746 CREATE TABLE config.metabib_field_index_norm_map (
747         id      SERIAL  PRIMARY KEY,
748         field   INT     NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
749         norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
750         params  TEXT,
751         pos     INT     NOT NULL DEFAULT 0
752 );
753
754 CREATE TABLE config.record_attr_definition (
755     name        TEXT    PRIMARY KEY,
756     label       TEXT    NOT NULL, -- I18N
757     description TEXT,
758     filter      BOOL    NOT NULL DEFAULT TRUE,  -- becomes QP filter if true
759     sorter      BOOL    NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
760
761 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
762     tag         TEXT, -- LIKE format
763     sf_list     TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
764
765 -- This is used for both tag/sf and xpath entries
766     joiner      TEXT,
767
768 -- For xpath-extracted attrs
769     xpath       TEXT,
770     format      TEXT    REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
771     start_pos   INT,
772     string_len  INT,
773
774 -- For fixed fields
775     fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
776
777 -- For phys-char fields
778     phys_char_sf    INT REFERENCES config.marc21_physical_characteristic_subfield_map (id)
779 );
780
781 CREATE TABLE config.record_attr_index_norm_map (
782     id      SERIAL  PRIMARY KEY,
783     attr    TEXT    NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
784     norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
785     params  TEXT,
786     pos     INT     NOT NULL DEFAULT 0
787 );
788
789 CREATE TABLE config.coded_value_map (
790     id              SERIAL  PRIMARY KEY,
791     ctype           TEXT    NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
792     code            TEXT    NOT NULL,
793     value           TEXT    NOT NULL,
794     description     TEXT,
795     opac_visible    BOOL    NOT NULL DEFAULT TRUE, -- For TPac selectors
796     search_label    TEXT,
797     is_simple       BOOL    NOT NULL DEFAULT FALSE
798 );
799
800 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
801 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
802 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
803 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
804 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
805 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
806 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
807
808 CREATE OR REPLACE FUNCTION config.update_coded_value_map(in_ctype TEXT, in_code TEXT, in_value TEXT, in_description TEXT DEFAULT NULL, in_opac_visible BOOL DEFAULT NULL, in_search_label TEXT DEFAULT NULL, in_is_simple BOOL DEFAULT NULL, add_only BOOL DEFAULT FALSE) RETURNS VOID AS $f$
809 DECLARE
810     current_row config.coded_value_map%ROWTYPE;
811 BEGIN
812     -- Look for a current value
813     SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
814     -- If we have one..
815     IF FOUND AND NOT add_only THEN
816         -- Update anything we were handed
817         current_row.value := COALESCE(current_row.value, in_value);
818         current_row.description := COALESCE(current_row.description, in_description);
819         current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
820         current_row.search_label := COALESCE(current_row.search_label, in_search_label);
821         current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
822         UPDATE config.coded_value_map
823             SET
824                 value = current_row.value,
825                 description = current_row.description,
826                 opac_visible = current_row.opac_visible,
827                 search_label = current_row.search_label,
828                 is_simple = current_row.is_simple
829             WHERE id = current_row.id;
830     ELSE
831         INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
832             (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
833     END IF;
834 END;
835 $f$ LANGUAGE PLPGSQL;
836
837 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
838 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
839     SELECT  DISTINCT l.version
840       FROM  config.upgrade_log l
841             JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.deprecates)
842       WHERE d.db_patch = $1
843 $$ LANGUAGE SQL;
844
845 -- List applied db patches that are superseded by (and block the application of) my_db_patch
846 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
847     SELECT  DISTINCT l.version
848       FROM  config.upgrade_log l
849             JOIN config.db_patch_dependencies d ON (l.version::TEXT[] && d.supersedes)
850       WHERE d.db_patch = $1
851 $$ LANGUAGE SQL;
852
853 -- List applied db patches that deprecates (and block the application of) my_db_patch
854 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
855     SELECT  db_patch
856       FROM  config.db_patch_dependencies
857       WHERE ARRAY[$1]::TEXT[] && deprecates
858 $$ LANGUAGE SQL;
859
860 -- List applied db patches that supersedes (and block the application of) my_db_patch
861 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
862     SELECT  db_patch
863       FROM  config.db_patch_dependencies
864       WHERE ARRAY[$1]::TEXT[] && supersedes
865 $$ LANGUAGE SQL;
866
867 -- Make sure that no deprecated or superseded db patches are currently applied
868 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
869     SELECT  COUNT(*) = 0
870       FROM  (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
871                 UNION
872              SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
873                 UNION
874              SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
875                 UNION
876              SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
877 $$ LANGUAGE SQL;
878
879 -- Raise an exception if there are, in fact, dep/sup conflict
880 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
881 DECLARE 
882     deprecates TEXT;
883     supersedes TEXT;
884 BEGIN
885     IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
886         SELECT  STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
887         SELECT  STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
888         RAISE EXCEPTION '
889 Upgrade script % can not be applied:
890   applied deprecated scripts %
891   applied superseded scripts %
892   deprecated by %
893   superseded by %',
894             my_db_patch,
895             ARRAY_AGG(evergreen.upgrade_list_applied_deprecates(my_db_patch)),
896             ARRAY_AGG(evergreen.upgrade_list_applied_supersedes(my_db_patch)),
897             evergreen.upgrade_list_applied_deprecated(my_db_patch),
898             evergreen.upgrade_list_applied_superseded(my_db_patch);
899     END IF;
900
901     INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
902     RETURN TRUE;
903 END;
904 $$ LANGUAGE PLPGSQL;
905
906 CREATE TABLE config.barcode_completion (
907     id          SERIAL PRIMARY KEY,
908     active      BOOL NOT NULL DEFAULT true,
909     org_unit    INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
910     prefix      TEXT,
911     suffix      TEXT,
912     length      INT NOT NULL DEFAULT 0,
913     padding     TEXT,
914     padding_end BOOL NOT NULL DEFAULT false,
915     asset       BOOL NOT NULL DEFAULT true,
916     actor       BOOL NOT NULL DEFAULT true
917 );
918
919 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
920
921 -- Add support for logging, only keep the most recent five rows for each category. 
922
923
924 CREATE TABLE config.org_unit_setting_type_log (
925     id              BIGSERIAL   PRIMARY KEY,
926     date_applied    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
927     org             INT,   --REFERENCES actor.org_unit (id),
928     original_value  TEXT,
929     new_value       TEXT,
930     field_name      TEXT      REFERENCES config.org_unit_setting_type (name)
931 );
932
933 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
934 Org Unit setting Logs
935
936 This table contains the most recent changes to each setting 
937 in actor.org_unit_setting, allowing for mistakes to be undone.
938 This is NOT meant to be an auditor, but rather an undo/redo.
939 $$;
940
941 CREATE OR REPLACE FUNCTION limit_oustl() RETURNS TRIGGER AS $oustl_limit$
942     BEGIN
943         -- Only keeps the most recent five settings changes.
944         DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND date_applied NOT IN 
945         (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name ORDER BY date_applied DESC LIMIT 4);
946         
947         IF (TG_OP = 'UPDATE') THEN
948             RETURN NEW;
949         ELSIF (TG_OP = 'INSERT') THEN
950             RETURN NEW;
951         END IF;
952         RETURN NULL;
953     END;
954 $oustl_limit$ LANGUAGE plpgsql;
955
956 CREATE TRIGGER limit_logs_oust
957     BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
958     FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
959
960 CREATE TABLE config.sms_carrier (
961     id              SERIAL PRIMARY KEY,
962     region          TEXT,
963     name            TEXT,
964     email_gateway   TEXT,
965     active          BOOLEAN DEFAULT TRUE
966 );
967
968 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
969
970 CREATE TABLE config.usr_activity_type (
971     id          SERIAL                      PRIMARY KEY, 
972     ewho        TEXT,
973     ewhat       TEXT,
974     ehow        TEXT,
975     label       TEXT                        NOT NULL, -- i18n
976     egroup      config.usr_activity_group   NOT NULL,
977     enabled     BOOL                        NOT NULL DEFAULT TRUE,
978     transient   BOOL                        NOT NULL DEFAULT FALSE,
979     CONSTRAINT  one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
980 );
981
982 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type 
983     (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
984
985 CREATE TABLE config.filter_dialog_interface (
986     key         TEXT                        PRIMARY KEY,
987     description TEXT
988 );  
989
990 CREATE TABLE config.filter_dialog_filter_set (
991     id          SERIAL                      PRIMARY KEY,
992     name        TEXT                        NOT NULL,
993     owning_lib  INT                         NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
994     creator     INT                         NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
995     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
996     interface   TEXT                        NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
997     filters     TEXT                        NOT NULL, -- CHECK (evergreen.is_json(filters))
998     CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
999 );
1000
1001 CREATE TABLE config.best_hold_order(
1002     id          SERIAL      PRIMARY KEY,
1003     name        TEXT        UNIQUE,   -- i18n
1004     pprox       INT, -- copy capture <-> pickup lib prox
1005     hprox       INT, -- copy circ lib <-> request lib prox
1006     aprox       INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1007     approx      INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1008     priority    INT, -- group hold priority
1009     cut         INT, -- cut-in-line
1010     depth       INT, -- selection depth
1011     htime       INT, -- time since last home-lib circ exceeds org-unit setting
1012     rtime       INT, -- request time
1013     shtime      INT  -- time since copy last trip home exceeds org-unit setting
1014 );
1015
1016 -- At least one of these columns must contain a non-null value
1017 ALTER TABLE config.best_hold_order ADD CHECK ((
1018     pprox IS NOT NULL OR
1019     hprox IS NOT NULL OR
1020     aprox IS NOT NULL OR
1021     priority IS NOT NULL OR
1022     cut IS NOT NULL OR
1023     depth IS NOT NULL OR
1024     htime IS NOT NULL OR
1025     rtime IS NOT NULL
1026 ));
1027
1028 COMMIT;