Lp 1746584: Stamping Upgrade Script
[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.assume_inserts_only');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_display_indexing');
52 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
53 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');
54
55
56 CREATE TABLE config.global_flag (
57     label   TEXT    NOT NULL
58 ) INHERITS (config.internal_flag);
59 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
60
61 CREATE TABLE config.upgrade_log (
62     version         TEXT    PRIMARY KEY,
63     install_date    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
64     applied_to      TEXT
65 );
66
67 CREATE TABLE config.db_patch_dependencies (
68   db_patch      TEXT PRIMARY KEY,
69   supersedes    TEXT[],
70   deprecates    TEXT[]
71 );
72
73 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
74 DECLARE
75     fld     TEXT;
76     cnt     INT;
77 BEGIN
78     fld := TG_ARGV[0];
79     EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
80     IF cnt > 0 THEN
81         RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
82     END IF;
83     RETURN NEW;
84 END;
85 $$ LANGUAGE PLPGSQL;
86
87 CREATE TRIGGER no_overlapping_sups
88     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
89     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
90
91 CREATE TRIGGER no_overlapping_deps
92     BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
93     FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
94
95 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1088', :eg_version); -- miker/dyrcona
96
97 CREATE TABLE config.bib_source (
98         id              SERIAL  PRIMARY KEY,
99         quality         INT     CHECK ( quality BETWEEN 0 AND 100 ),
100         source          TEXT    NOT NULL UNIQUE,
101         transcendant    BOOL    NOT NULL DEFAULT FALSE,
102         can_have_copies BOOL    NOT NULL DEFAULT TRUE
103 );
104 COMMENT ON TABLE config.bib_source IS $$
105 This is table is used to set up the relative "quality" of each
106 MARC source, such as OCLC.  Also identifies "transcendant" sources,
107 i.e., sources of bib records that should display in the OPAC
108 even if no copies or located URIs are attached. Also indicates if
109 the source is allowed to have actual copies on its bibs. Volumes
110 for targeted URIs are unaffected by this setting.
111 $$;
112
113 CREATE TABLE config.standing (
114         id              SERIAL  PRIMARY KEY,
115         value           TEXT    NOT NULL UNIQUE
116 );
117 COMMENT ON TABLE config.standing IS $$
118 Patron Standings
119
120 This table contains the values that can be applied to a patron
121 by a staff member.  These values should not be changed, other
122 than for translation, as the ID column is currently a "magic
123 number" in the source. :(
124 $$;
125
126 CREATE TABLE config.standing_penalty (
127         id                      SERIAL  PRIMARY KEY,
128         name            TEXT    NOT NULL UNIQUE,
129         label           TEXT    NOT NULL,
130         block_list      TEXT,
131         staff_alert     BOOL    NOT NULL DEFAULT FALSE,
132         org_depth       INTEGER,
133         ignore_proximity INTEGER
134 );
135
136 CREATE TABLE config.xml_transform (
137         name            TEXT    PRIMARY KEY,
138         namespace_uri   TEXT    NOT NULL,
139         prefix          TEXT    NOT NULL,
140         xslt            TEXT    NOT NULL
141 );
142
143 CREATE TABLE config.biblio_fingerprint (
144         id                      SERIAL  PRIMARY KEY,
145         name            TEXT    NOT NULL, 
146         xpath           TEXT    NOT NULL,
147     first_word  BOOL    NOT NULL DEFAULT FALSE,
148         format          TEXT    NOT NULL DEFAULT 'marcxml'
149 );
150
151 INSERT INTO config.biblio_fingerprint (name, xpath, format)
152     VALUES (
153         'Title',
154         '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
155             '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
156             '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
157             '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
158             '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
159         'marcxml'
160     );
161
162 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
163     VALUES (
164         'Author',
165         '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
166             '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
167             '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
168             '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
169             '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
170         'marcxml',
171         TRUE
172     );
173
174 INSERT INTO config.biblio_fingerprint (name, xpath, format)
175     VALUES (
176         'PartName',
177         '//mods32:mods/mods32:titleInfo/mods32:partName',
178         'mods32'
179     );
180
181 INSERT INTO config.biblio_fingerprint (name, xpath, format)
182     VALUES (
183         'PartNumber',
184         '//mods32:mods/mods32:titleInfo/mods32:partNumber',
185         'mods32'
186     );
187
188 CREATE TABLE config.metabib_class (
189     name     TEXT    PRIMARY KEY,
190     label    TEXT    NOT NULL UNIQUE,
191     buoyant  BOOL    DEFAULT FALSE NOT NULL,
192     restrict BOOL    DEFAULT FALSE NOT NULL,
193     combined BOOL    DEFAULT FALSE NOT NULL,
194     a_weight NUMERIC  DEFAULT 1.0 NOT NULL,
195     b_weight NUMERIC  DEFAULT 0.4 NOT NULL,
196     c_weight NUMERIC  DEFAULT 0.2 NOT NULL,
197     d_weight NUMERIC  DEFAULT 0.1 NOT NULL
198 );
199
200 CREATE TABLE config.metabib_field (
201         id              SERIAL  PRIMARY KEY,
202         field_class     TEXT    NOT NULL REFERENCES config.metabib_class (name),
203         name            TEXT    NOT NULL,
204         label           TEXT    NOT NULL,
205         xpath           TEXT    NOT NULL,
206         weight          INT     NOT NULL DEFAULT 1,
207         format          TEXT    NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
208         search_field    BOOL    NOT NULL DEFAULT TRUE,
209         facet_field     BOOL    NOT NULL DEFAULT FALSE,
210         browse_field    BOOL    NOT NULL DEFAULT TRUE,
211         browse_xpath   TEXT,
212         browse_sort_xpath TEXT,
213         facet_xpath     TEXT,
214         display_xpath   TEXT,
215         authority_xpath TEXT,
216         joiner      TEXT,
217         restrict        BOOL    DEFAULT FALSE NOT NULL,
218     display_field BOOL NOT NULL DEFAULT FALSE
219 );
220 COMMENT ON TABLE config.metabib_field IS $$
221 XPath used for record indexing ingest
222
223 This table contains the XPath used to chop up MODS into its
224 indexable parts.  Each XPath entry is named and assigned to
225 a "class" of either title, subject, author, keyword, series
226 or identifier.
227 $$;
228
229 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
230
231 CREATE TABLE config.display_field_map (
232     name    TEXT   PRIMARY KEY,
233     field   INTEGER REFERENCES config.metabib_field (id),
234     multi   BOOLEAN DEFAULT FALSE
235 );
236
237 CREATE TABLE config.ts_config_list (
238         id                      TEXT PRIMARY KEY,
239         name            TEXT NOT NULL
240 );
241 COMMENT ON TABLE config.ts_config_list IS $$
242 Full Text Configs
243
244 A list of full text configs with names and descriptions.
245 $$;
246
247 CREATE TABLE config.metabib_class_ts_map (
248         id                              SERIAL PRIMARY KEY,
249         field_class             TEXT NOT NULL REFERENCES config.metabib_class (name),
250         ts_config               TEXT NOT NULL REFERENCES config.ts_config_list (id),
251         active                  BOOL NOT NULL DEFAULT TRUE,
252         index_weight    CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
253         index_lang              TEXT NULL,
254         search_lang             TEXT NULL,
255         always                  BOOL NOT NULL DEFAULT true
256 );
257 COMMENT ON TABLE config.metabib_class_ts_map IS $$
258 Text Search Configs for metabib class indexing
259
260 This table contains text search config definitions for
261 storing index_vector values.
262 $$;
263
264 CREATE TABLE config.metabib_field_ts_map (
265         id                              SERIAL PRIMARY KEY,
266         metabib_field   INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
267         ts_config               TEXT NOT NULL REFERENCES config.ts_config_list (id),
268         active                  BOOL NOT NULL DEFAULT TRUE,
269         index_weight    CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
270         index_lang              TEXT NULL,
271         search_lang             TEXT NULL
272 );
273 COMMENT ON TABLE config.metabib_field_ts_map IS $$
274 Text Search Configs for metabib field indexing
275
276 This table contains text search config definitions for
277 storing index_vector values.
278 $$;
279
280 CREATE TABLE config.metabib_search_alias (
281     alias       TEXT    PRIMARY KEY,
282     field_class TEXT    NOT NULL REFERENCES config.metabib_class (name),
283     field       INT     REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
284 );
285
286 CREATE TABLE config.non_cataloged_type (
287         id              SERIAL          PRIMARY KEY,
288         owning_lib      INT             NOT NULL, -- REFERENCES actor.org_unit (id),
289         name            TEXT            NOT NULL,
290         circ_duration   INTERVAL        NOT NULL DEFAULT '14 days'::INTERVAL,
291         in_house        BOOL            NOT NULL DEFAULT FALSE,
292         CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
293 );
294 COMMENT ON TABLE config.non_cataloged_type IS $$
295 Types of valid non-cataloged items.
296 $$;
297
298 CREATE TABLE config.identification_type (
299         id              SERIAL  PRIMARY KEY,
300         name            TEXT    NOT NULL UNIQUE
301 );
302 COMMENT ON TABLE config.identification_type IS $$
303 Types of valid patron identification.
304
305 Each patron must display at least one valid form of identification
306 in order to get a library card.  This table lists those forms.
307 $$;
308
309 CREATE TABLE config.rule_circ_duration (
310         id              SERIAL          PRIMARY KEY,
311         name            TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
312         extended        INTERVAL        NOT NULL,
313         normal          INTERVAL        NOT NULL,
314         shrt            INTERVAL        NOT NULL,
315         max_renewals    INT             NOT NULL
316 );
317 COMMENT ON TABLE config.rule_circ_duration IS $$
318 Circulation Duration rules
319
320 Each circulation is given a duration based on one of these rules.
321 $$;
322
323 CREATE TABLE config.hard_due_date (
324     id                  SERIAL      PRIMARY KEY,
325     name                TEXT        NOT NULL UNIQUE,
326     ceiling_date        TIMESTAMPTZ NOT NULL,
327     forceto             BOOL        NOT NULL,
328     owner               INT         NOT NULL   -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
329 );
330
331 CREATE TABLE config.hard_due_date_values (
332     id                  SERIAL      PRIMARY KEY,
333     hard_due_date       INT         NOT NULL REFERENCES config.hard_due_date (id)
334                                     DEFERRABLE INITIALLY DEFERRED,
335     ceiling_date        TIMESTAMPTZ NOT NULL,
336     active_date         TIMESTAMPTZ NOT NULL
337 );
338
339 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
340 DECLARE
341     temp_value  config.hard_due_date_values%ROWTYPE;
342     updated     INT := 0;
343 BEGIN
344     FOR temp_value IN
345       SELECT  DISTINCT ON (hard_due_date) *
346         FROM  config.hard_due_date_values
347         WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
348         ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
349    LOOP
350         UPDATE  config.hard_due_date
351           SET   ceiling_date = temp_value.ceiling_date
352           WHERE id = temp_value.hard_due_date
353                 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
354                 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
355
356         IF FOUND THEN
357             updated := updated + 1;
358         END IF;
359     END LOOP;
360
361     RETURN updated;
362 END;
363 $func$ LANGUAGE plpgsql;
364
365 CREATE TABLE config.rule_max_fine (
366     id          SERIAL          PRIMARY KEY,
367     name        TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
368     amount      NUMERIC(6,2)    NOT NULL,
369     is_percent  BOOL            NOT NULL DEFAULT FALSE
370 );
371 COMMENT ON TABLE config.rule_max_fine IS $$
372 Circulation Max Fine rules
373
374 Each circulation is given a maximum fine based on one of
375 these rules.
376 $$;
377
378 CREATE TABLE config.rule_recurring_fine (
379         id                      SERIAL          PRIMARY KEY,
380         name                    TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
381         high                    NUMERIC(6,2)    NOT NULL,
382         normal                  NUMERIC(6,2)    NOT NULL,
383         low                     NUMERIC(6,2)    NOT NULL,
384         recurrence_interval     INTERVAL        NOT NULL DEFAULT '1 day'::INTERVAL,
385     grace_period       INTERVAL         NOT NULL DEFAULT '1 day'::INTERVAL
386 );
387 COMMENT ON TABLE config.rule_recurring_fine IS $$
388 Circulation Recurring Fine rules
389
390 Each circulation is given a recurring fine amount based on one of
391 these rules.  Note that it is recommended to run the fine generator
392 (from cron) at least as frequently as the lowest recurrence interval
393 used by your circulation rules so that accrued fines will be up
394 to date.
395 $$;
396
397
398 CREATE TABLE config.rule_age_hold_protect (
399         id      SERIAL          PRIMARY KEY,
400         name    TEXT            NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
401         age     INTERVAL        NOT NULL,
402         prox    INT             NOT NULL
403 );
404 COMMENT ON TABLE config.rule_age_hold_protect IS $$
405 Hold Item Age Protection rules
406
407 A hold request can only capture new(ish) items when they are
408 within a particular proximity of the pickup_lib of the request.
409 The proximity ('prox' column) is calculated by counting
410 the number of tree edges between the pickup_lib and either the
411 owning_lib or circ_lib of the copy that could fulfill the hold,
412 as determined by the distance_is_from_owner value of the hold matrix
413 rule controlling the hold request.
414 $$;
415
416 CREATE TABLE config.copy_status (
417         id              SERIAL  PRIMARY KEY,
418         name            TEXT    NOT NULL UNIQUE,
419         holdable        BOOL    NOT NULL DEFAULT FALSE,
420         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
421     copy_active  BOOL    NOT NULL DEFAULT FALSE,
422         restrict_copy_delete BOOL         NOT NULL DEFAULT FALSE,
423     is_available  BOOL    NOT NULL DEFAULT FALSE
424 );
425 COMMENT ON TABLE config.copy_status IS $$
426 Copy Statuses
427
428 The available copy statuses, and whether a copy in that
429 status is available for hold request capture.  0 (zero) is
430 the only special number in this set, meaning that the item
431 is available for immediate checkout, and is counted as available
432 in the OPAC.
433
434 Statuses with an ID below 100 are not removable, and have special
435 meaning in the code.  Do not change them except to translate the
436 textual name.
437
438 You may add and remove statuses above 100, and these can be used
439 to remove items from normal circulation without affecting the rest
440 of the copy's values or its location.
441 $$;
442
443 CREATE TABLE config.net_access_level (
444         id      SERIAL          PRIMARY KEY,
445         name    TEXT            NOT NULL UNIQUE
446 );
447 COMMENT ON TABLE config.net_access_level IS $$
448 Patron Network Access level
449
450 This will be used to inform the in-library firewall of how much
451 internet access the using patron should be allowed.
452 $$;
453
454
455 CREATE TABLE config.remote_account (
456     id          SERIAL  PRIMARY KEY,
457     label       TEXT    NOT NULL,
458     host        TEXT    NOT NULL,   -- name or IP, :port optional
459     username    TEXT,               -- optional, since we could default to $USER
460     password    TEXT,               -- optional, since we could use SSH keys, or anonymous login.
461     account     TEXT,               -- aka profile or FTP "account" command
462     path        TEXT,               -- aka directory
463     owner       INT     NOT NULL,   -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
464     last_activity TIMESTAMP WITH TIME ZONE
465 );
466
467 CREATE TABLE config.marc21_rec_type_map (
468     code        TEXT    PRIMARY KEY,
469     type_val    TEXT    NOT NULL,
470     blvl_val    TEXT    NOT NULL
471 );
472
473 CREATE TABLE config.marc21_ff_pos_map (
474     id          SERIAL  PRIMARY KEY,
475     fixed_field TEXT    NOT NULL,
476     tag         TEXT    NOT NULL,
477     rec_type    TEXT    NOT NULL,
478     start_pos   INT     NOT NULL,
479     length      INT     NOT NULL,
480     default_val TEXT    NOT NULL DEFAULT ' '
481 );
482
483 CREATE TABLE config.marc21_physical_characteristic_type_map (
484     ptype_key   TEXT    PRIMARY KEY,
485     label       TEXT    NOT NULL -- I18N
486 );
487
488 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
489     id          SERIAL  PRIMARY KEY,
490     ptype_key   TEXT    NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
491     subfield    TEXT    NOT NULL,
492     start_pos   INT     NOT NULL,
493     length      INT     NOT NULL,
494     label       TEXT    NOT NULL -- I18N
495 );
496
497 CREATE TABLE config.marc21_physical_characteristic_value_map (
498     id              SERIAL  PRIMARY KEY,
499     value           TEXT    NOT NULL,
500     ptype_subfield  INT     NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
501     label           TEXT    NOT NULL -- I18N
502 );
503
504
505 CREATE TABLE config.z3950_source (
506     name                TEXT    PRIMARY KEY,
507     label               TEXT    NOT NULL UNIQUE,
508     host                TEXT    NOT NULL,
509     port                INT     NOT NULL,
510     db                  TEXT    NOT NULL,
511     record_format       TEXT    NOT NULL DEFAULT 'FI',
512     transmission_format TEXT    NOT NULL DEFAULT 'usmarc',
513     auth                BOOL    NOT NULL DEFAULT TRUE,
514     use_perm            INT     -- REFERENCES permission.perm_list (id)
515 );
516
517 COMMENT ON TABLE config.z3950_source IS $$
518 Z39.50 Sources
519
520 Each row in this table represents a database searchable via Z39.50.
521 $$;
522
523 COMMENT ON COLUMN config.z3950_source.record_format IS $$
524 Z39.50 element set.
525 $$;
526
527 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
528 Z39.50 preferred record syntax..
529 $$;
530
531 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
532 If set, this permission is required for the source to be listed in the staff
533 client Z39.50 interface.  Similar to permission.grp_tree.application_perm.
534 $$;
535
536 CREATE TABLE config.z3950_attr (
537     id          SERIAL  PRIMARY KEY,
538     source      TEXT    NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
539     name        TEXT    NOT NULL,
540     label       TEXT    NOT NULL,
541     code        INT     NOT NULL,
542     format      INT     NOT NULL,
543     truncation  INT     NOT NULL DEFAULT 0,
544     CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
545 );
546
547 CREATE TABLE config.z3950_source_credentials (
548     id SERIAL PRIMARY KEY,
549     owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
550     source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
551     username TEXT,
552     password TEXT,
553     CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
554 );
555
556 CREATE TABLE config.i18n_locale (
557     code        TEXT    PRIMARY KEY,
558     marc_code   TEXT    NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
559     name        TEXT    UNIQUE NOT NULL,
560     description TEXT,
561     rtl         BOOL    NOT NULL DEFAULT FALSE
562 );
563
564 CREATE TABLE config.i18n_core (
565     id              BIGSERIAL   PRIMARY KEY,
566     fq_field        TEXT        NOT NULL,
567     identity_value  TEXT        NOT NULL,
568     translation     TEXT        NOT NULL    REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
569     string          TEXT        NOT NULL
570 );
571
572 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
573
574 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
575 BEGIN
576
577     EXECUTE $$
578         UPDATE  config.i18n_core
579           SET   identity_value = $$ || quote_literal(new_ident) || $$ 
580           WHERE fq_field LIKE '$$ || hint || $$.%' 
581                 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
582
583     RETURN;
584
585 END;
586 $_$ LANGUAGE PLPGSQL;
587
588 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
589 BEGIN
590     PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
591     RETURN NEW;
592 END;
593 $_$ LANGUAGE PLPGSQL;
594
595 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
596 BEGIN
597     PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
598     RETURN NEW;
599 END;
600 $_$ LANGUAGE PLPGSQL;
601
602 CREATE TABLE config.billing_type (
603     id              SERIAL  PRIMARY KEY,
604     name            TEXT    NOT NULL,
605     owner           INT     NOT NULL, -- REFERENCES actor.org_unit (id)
606     default_price   NUMERIC(6,2),
607     CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
608 );
609
610 CREATE TABLE config.settings_group (
611     name    TEXT PRIMARY KEY,
612     label   TEXT UNIQUE NOT NULL -- I18N
613 );
614
615 CREATE TABLE config.org_unit_setting_type (
616     name            TEXT    PRIMARY KEY,
617     label           TEXT    UNIQUE NOT NULL,
618     grp             TEXT    REFERENCES config.settings_group (name),
619     description     TEXT,
620     datatype        TEXT    NOT NULL DEFAULT 'string',
621     fm_class        TEXT,
622     view_perm       INT,
623     update_perm     INT,
624     --
625     -- define valid datatypes
626     --
627     CONSTRAINT coust_valid_datatype CHECK ( datatype IN
628     ( 'bool', 'integer', 'float', 'currency', 'interval',
629       'date', 'string', 'object', 'array', 'link' ) ),
630     --
631     -- fm_class is meaningful only for 'link' datatype
632     --
633     CONSTRAINT coust_no_empty_link CHECK
634     ( ( datatype =  'link' AND fm_class IS NOT NULL ) OR
635       ( datatype <> 'link' AND fm_class IS NULL ) )
636 );
637
638 CREATE TABLE config.usr_setting_type (
639
640     name TEXT PRIMARY KEY,
641     opac_visible BOOL NOT NULL DEFAULT FALSE,
642     label TEXT UNIQUE NOT NULL,
643     description TEXT,
644     grp             TEXT    REFERENCES config.settings_group (name),
645     datatype TEXT NOT NULL DEFAULT 'string',
646     fm_class TEXT,
647     reg_default TEXT,
648
649     --
650     -- define valid datatypes
651     --
652     CONSTRAINT coust_valid_datatype CHECK ( datatype IN
653     ( 'bool', 'integer', 'float', 'currency', 'interval',
654         'date', 'string', 'object', 'array', 'link' ) ),
655
656     --
657     -- fm_class is meaningful only for 'link' datatype
658     --
659     CONSTRAINT coust_no_empty_link CHECK
660     ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
661         ( datatype <> 'link' AND fm_class IS NULL ) )
662
663 );
664
665 -- Some handy functions, based on existing ones, to provide optional ingest normalization
666
667 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
668         SELECT SUBSTRING($1,$2);
669 $func$ LANGUAGE SQL STRICT IMMUTABLE;
670
671 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
672         SELECT SUBSTRING($1,1,$2);
673 $func$ LANGUAGE SQL STRICT IMMUTABLE;
674
675 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
676         SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
677 $func$ LANGUAGE SQL STRICT IMMUTABLE;
678
679 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
680         SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
681 $func$ LANGUAGE SQL STRICT IMMUTABLE;
682
683 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
684         SELECT approximate_date( $1, '0');
685 $func$ LANGUAGE SQL STRICT IMMUTABLE;
686
687 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
688         SELECT approximate_date( $1, '9');
689 $func$ LANGUAGE SQL STRICT IMMUTABLE;
690
691 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
692         SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
693 $func$ LANGUAGE SQL STRICT IMMUTABLE;
694
695 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
696         SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
697 $func$ LANGUAGE SQL STRICT IMMUTABLE;
698
699 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
700     use Business::ISBN;
701     use strict;
702     use warnings;
703
704     # Find the first ISBN, force it to ISBN13 and return it
705
706     my $input = shift;
707
708     foreach my $word (split(/\s/, $input)) {
709         my $isbn = Business::ISBN->new($word);
710
711         # First check the checksum; if it is not valid, fix it and add the original
712         # bad-checksum ISBN to the output
713         if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
714             $isbn->fix_checksum();
715         }
716
717         # If we now have a valid ISBN, force it to ISBN13 and return it
718         return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
719     }
720     return undef;
721 $func$ LANGUAGE PLPERLU;
722
723 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
724 Inspired by translate_isbn1013
725
726 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
727 version without hypens and with a repaired checksum if the checksum was bad
728 $$;
729
730
731 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
732     use Business::ISBN;
733     use strict;
734     use warnings;
735
736     # For each ISBN found in a single string containing a set of ISBNs:
737     #   * Normalize an incoming ISBN to have the correct checksum and no hyphens
738     #   * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
739
740     my $input = shift;
741     my $output = '';
742
743     foreach my $word (split(/\s/, $input)) {
744         my $isbn = Business::ISBN->new($word);
745
746         # First check the checksum; if it is not valid, fix it and add the original
747         # bad-checksum ISBN to the output
748         if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
749             $output .= $isbn->isbn() . " ";
750             $isbn->fix_checksum();
751         }
752
753         # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
754         # and add the normalized original ISBN to the output
755         if ($isbn && $isbn->is_valid()) {
756             my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
757             $output .= $isbn->isbn . " ";
758
759             # If we successfully converted the ISBN to its counterpart, add the
760             # converted ISBN to the output as well
761             $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
762         }
763     }
764     return $output if $output;
765
766     # If there were no valid ISBNs, just return the raw input
767     return $input;
768 $func$ LANGUAGE PLPERLU;
769
770 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
771 The translate_isbn1013 function takes an input ISBN and returns the
772 following in a single space-delimited string if the input ISBN is valid:
773   - The normalized input ISBN (hyphens stripped)
774   - The normalized input ISBN with a fixed checksum if the checksum was bad
775   - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
776 $$;
777
778 -- And ... a table in which to register them
779
780 CREATE TABLE config.index_normalizer (
781         id              SERIAL  PRIMARY KEY,
782         name            TEXT    UNIQUE NOT NULL,
783         description     TEXT,
784         func            TEXT    NOT NULL,
785         param_count     INT     NOT NULL DEFAULT 0
786 );
787
788 CREATE TABLE config.metabib_field_index_norm_map (
789         id      SERIAL  PRIMARY KEY,
790         field   INT     NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
791         norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
792         params  TEXT,
793         pos     INT     NOT NULL DEFAULT 0
794 );
795
796 CREATE TABLE config.record_attr_definition (
797     name        TEXT    PRIMARY KEY,
798     label       TEXT    NOT NULL, -- I18N
799     description TEXT,
800     multi       BOOL    NOT NULL DEFAULT TRUE,  -- will store all values from a record
801     filter      BOOL    NOT NULL DEFAULT TRUE,  -- becomes QP filter if true
802     sorter      BOOL    NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
803     composite   BOOL    NOT NULL DEFAULT FALSE, -- its values are derived from others
804
805 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
806     tag         TEXT, -- LIKE format
807     sf_list     TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
808
809 -- This is used for both tag/sf and xpath entries
810     joiner      TEXT,
811
812 -- For xpath-extracted attrs
813     xpath       TEXT,
814     format      TEXT    REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
815     start_pos   INT,
816     string_len  INT,
817
818 -- For fixed fields
819     fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
820
821 -- For phys-char fields
822     phys_char_sf    INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
823
824 -- Source of vocabulary terms for this record attribute;
825 -- typically will be a URI referring to a SKOS vocabulary
826     vocabulary  TEXT
827 );
828
829 CREATE TABLE config.record_attr_index_norm_map (
830     id      SERIAL  PRIMARY KEY,
831     attr    TEXT    NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
832     norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
833     params  TEXT,
834     pos     INT     NOT NULL DEFAULT 0
835 );
836
837 CREATE TABLE config.coded_value_map (
838     id              SERIAL  PRIMARY KEY,
839     ctype           TEXT    NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
840     code            TEXT    NOT NULL,
841     value           TEXT    NOT NULL,
842     description     TEXT,
843     opac_visible    BOOL    NOT NULL DEFAULT TRUE, -- For TPac selectors
844     search_label    TEXT,
845     is_simple       BOOL    NOT NULL DEFAULT FALSE,
846     concept_uri     TEXT    -- URI expressing the SKOS concept that the
847                             -- coded value represents
848 );
849
850 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
851
852 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
853 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
854 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
855 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
856 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
857 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
858 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
859
860 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$
861 DECLARE
862     current_row config.coded_value_map%ROWTYPE;
863 BEGIN
864     -- Look for a current value
865     SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
866     -- If we have one..
867     IF FOUND AND NOT add_only THEN
868         -- Update anything we were handed
869         current_row.value := COALESCE(current_row.value, in_value);
870         current_row.description := COALESCE(current_row.description, in_description);
871         current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
872         current_row.search_label := COALESCE(current_row.search_label, in_search_label);
873         current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
874         UPDATE config.coded_value_map
875             SET
876                 value = current_row.value,
877                 description = current_row.description,
878                 opac_visible = current_row.opac_visible,
879                 search_label = current_row.search_label,
880                 is_simple = current_row.is_simple
881             WHERE id = current_row.id;
882     ELSE
883         INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
884             (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
885     END IF;
886 END;
887 $f$ LANGUAGE PLPGSQL;
888
889 CREATE TABLE config.composite_attr_entry_definition(
890     coded_value INT  PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
891     definition  TEXT    NOT NULL -- JSON
892 );
893
894 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
895 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
896     SELECT  DISTINCT l.version
897       FROM  config.upgrade_log l
898             JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
899       WHERE d.db_patch = $1
900 $$ LANGUAGE SQL;
901
902 -- List applied db patches that are superseded by (and block the application of) my_db_patch
903 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
904     SELECT  DISTINCT l.version
905       FROM  config.upgrade_log l
906             JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
907       WHERE d.db_patch = $1
908 $$ LANGUAGE SQL;
909
910 -- List applied db patches that deprecates (and block the application of) my_db_patch
911 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
912     SELECT  db_patch
913       FROM  config.db_patch_dependencies
914       WHERE ARRAY[$1]::TEXT[] && deprecates
915 $$ LANGUAGE SQL;
916
917 -- List applied db patches that supersedes (and block the application of) my_db_patch
918 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
919     SELECT  db_patch
920       FROM  config.db_patch_dependencies
921       WHERE ARRAY[$1]::TEXT[] && supersedes
922 $$ LANGUAGE SQL;
923
924 -- Make sure that no deprecated or superseded db patches are currently applied
925 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
926     SELECT  COUNT(*) = 0
927       FROM  (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
928                 UNION
929              SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
930                 UNION
931              SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
932                 UNION
933              SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
934 $$ LANGUAGE SQL;
935
936 -- Raise an exception if there are, in fact, dep/sup conflict
937 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
938 DECLARE 
939     deprecates TEXT;
940     supersedes TEXT;
941 BEGIN
942     IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
943         SELECT  STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
944         SELECT  STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
945         RAISE EXCEPTION '
946 Upgrade script % can not be applied:
947   applied deprecated scripts %
948   applied superseded scripts %
949   deprecated by %
950   superseded by %',
951             my_db_patch,
952             (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
953             (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
954             evergreen.upgrade_list_applied_deprecated(my_db_patch),
955             evergreen.upgrade_list_applied_superseded(my_db_patch);
956     END IF;
957
958     INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
959     RETURN TRUE;
960 END;
961 $$ LANGUAGE PLPGSQL;
962
963 CREATE TABLE config.barcode_completion (
964     id          SERIAL PRIMARY KEY,
965     active      BOOL NOT NULL DEFAULT true,
966     org_unit    INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
967     prefix      TEXT,
968     suffix      TEXT,
969     length      INT NOT NULL DEFAULT 0,
970     padding     TEXT,
971     padding_end BOOL NOT NULL DEFAULT false,
972     asset       BOOL NOT NULL DEFAULT true,
973     actor       BOOL NOT NULL DEFAULT true
974 );
975
976 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
977
978 -- Add support for logging, only keep the most recent five rows for each category. 
979
980
981 CREATE TABLE config.org_unit_setting_type_log (
982     id              BIGSERIAL   PRIMARY KEY,
983     date_applied    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
984     org             INT,   --REFERENCES actor.org_unit (id),
985     original_value  TEXT,
986     new_value       TEXT,
987     field_name      TEXT      REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
988 );
989
990 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
991 Org Unit setting Logs
992
993 This table contains the most recent changes to each setting 
994 in actor.org_unit_setting, allowing for mistakes to be undone.
995 This is NOT meant to be an auditor, but rather an undo/redo.
996 $$;
997
998 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
999     BEGIN
1000         -- Only keeps the most recent five settings changes.
1001         DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN 
1002         (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org ORDER BY date_applied DESC LIMIT 4);
1003         
1004         IF (TG_OP = 'UPDATE') THEN
1005             RETURN NEW;
1006         ELSIF (TG_OP = 'INSERT') THEN
1007             RETURN NEW;
1008         END IF;
1009         RETURN NULL;
1010     END;
1011 $oustl_limit$ LANGUAGE plpgsql;
1012
1013 CREATE TRIGGER limit_logs_oust
1014     BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1015     FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1016
1017 CREATE TABLE config.sms_carrier (
1018     id              SERIAL PRIMARY KEY,
1019     region          TEXT,
1020     name            TEXT,
1021     email_gateway   TEXT,
1022     active          BOOLEAN DEFAULT TRUE
1023 );
1024
1025 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1026
1027 CREATE TABLE config.usr_activity_type (
1028     id          SERIAL                      PRIMARY KEY, 
1029     ewho        TEXT,
1030     ewhat       TEXT,
1031     ehow        TEXT,
1032     label       TEXT                        NOT NULL, -- i18n
1033     egroup      config.usr_activity_group   NOT NULL,
1034     enabled     BOOL                        NOT NULL DEFAULT TRUE,
1035     transient   BOOL                        NOT NULL DEFAULT TRUE,
1036     CONSTRAINT  one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1037 );
1038
1039 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type 
1040     (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1041
1042 CREATE TABLE config.filter_dialog_interface (
1043     key         TEXT                        PRIMARY KEY,
1044     description TEXT
1045 );  
1046
1047 CREATE TABLE config.filter_dialog_filter_set (
1048     id          SERIAL                      PRIMARY KEY,
1049     name        TEXT                        NOT NULL,
1050     owning_lib  INT                         NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1051     creator     INT                         NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1052     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
1053     interface   TEXT                        NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1054     filters     TEXT                        NOT NULL, -- CHECK (evergreen.is_json(filters))
1055     CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1056 );
1057
1058 CREATE TABLE config.best_hold_order(
1059     id          SERIAL      PRIMARY KEY,
1060     name        TEXT        UNIQUE,   -- i18n
1061     pprox       INT, -- copy capture <-> pickup lib prox
1062     hprox       INT, -- copy circ lib <-> request lib prox
1063     aprox       INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1064     approx      INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1065     priority    INT, -- group hold priority
1066     cut         INT, -- cut-in-line
1067     depth       INT, -- selection depth
1068     htime       INT, -- time since last home-lib circ exceeds org-unit setting
1069     rtime       INT, -- request time
1070     shtime      INT  -- time since copy last trip home exceeds org-unit setting
1071 );
1072
1073 -- At least one of these columns must contain a non-null value
1074 ALTER TABLE config.best_hold_order ADD CHECK ((
1075     pprox IS NOT NULL OR
1076     hprox IS NOT NULL OR
1077     aprox IS NOT NULL OR
1078     priority IS NOT NULL OR
1079     cut IS NOT NULL OR
1080     depth IS NOT NULL OR
1081     htime IS NOT NULL OR
1082     rtime IS NOT NULL
1083 ));
1084
1085 CREATE OR REPLACE FUNCTION 
1086     evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1087     BEGIN
1088
1089       PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1090
1091       IF FOUND THEN
1092         RETURN NULL;
1093       END IF;
1094
1095       RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1096
1097     END;
1098 $func$ LANGUAGE PLPGSQL STABLE;
1099
1100 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1101 Used by a config.z3950_index_field_map constraint trigger
1102 to verify z3950_attr_type maps.
1103 $$;
1104
1105 -- drop these in down here since they reference config.metabib_field
1106 -- and config.record_attr_definition
1107 CREATE TABLE config.z3950_index_field_map (
1108     id              SERIAL  PRIMARY KEY,
1109     label           TEXT    NOT NULL, -- i18n
1110     metabib_field   INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1111     record_attr     TEXT    REFERENCES config.record_attr_definition(name),
1112     z3950_attr      INTEGER REFERENCES config.z3950_attr(id),
1113     z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1114     CONSTRAINT metabib_field_or_record_attr CHECK (
1115         metabib_field IS NOT NULL OR 
1116         record_attr IS NOT NULL
1117     ),
1118     CONSTRAINT attr_or_attr_type CHECK (
1119         z3950_attr IS NOT NULL OR 
1120         z3950_attr_type IS NOT NULL
1121     )
1122 );
1123
1124 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1125     DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1126     EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1127
1128 CREATE TABLE config.marc_format (
1129     id                  SERIAL PRIMARY KEY,
1130     code                TEXT NOT NULL,
1131     name                TEXT NOT NULL
1132 );
1133 COMMENT ON TABLE config.marc_format IS $$
1134 List of MARC formats supported by this Evergreen
1135 database. This exists primarily as a hook for future
1136 support of UNIMARC, though whether that will ever
1137 happen remains to be seen.
1138 $$;
1139
1140 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1141
1142 CREATE TABLE config.marc_field (
1143     id                  SERIAL PRIMARY KEY,
1144     marc_format         INTEGER NOT NULL
1145                         REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1146     marc_record_type    config.marc_record_type NOT NULL,
1147     tag                 CHAR(3) NOT NULL,
1148     name                TEXT,
1149     description         TEXT,
1150     fixed_field         BOOLEAN,
1151     repeatable          BOOLEAN,
1152     mandatory           BOOLEAN,
1153     hidden              BOOLEAN,
1154     owner               INTEGER -- REFERENCES actor.org_unit (id)
1155                         -- if the owner is null, the data about the field is
1156                         -- assumed to come from the controlling MARC standard
1157 );
1158
1159 COMMENT ON TABLE config.marc_field IS $$
1160 This table stores a list of MARC fields recognized by the Evergreen
1161 instance.  Note that we're not aiming for completely generic ISO2709
1162 support: we're assuming things like three characters for a tag,
1163 one-character subfield labels, two indicators per variable data field,
1164 and the like, all of which are technically specializations of ISO2709.
1165
1166 Of particular significance is the owner column; if it's set to a null
1167 value, the field definition is assumed to come from a national
1168 standards body; if it's set to a non-null value, the field definition
1169 is an OU-level addition to or override of the standard.
1170 $$;
1171
1172 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1173 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1174
1175 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1176     ON config.marc_field(marc_format, marc_record_type, tag)
1177     WHERE owner IS NULL;
1178 ALTER TABLE config.marc_field
1179     ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1180     CHECK ((owner IS NOT NULL) OR
1181            (
1182                 owner IS NULL AND
1183                 repeatable IS NOT NULL AND
1184                 mandatory IS NOT NULL AND
1185                 hidden IS NOT NULL
1186            )
1187           );
1188
1189 CREATE TABLE config.marc_subfield (
1190     id                  SERIAL PRIMARY KEY,
1191     marc_format         INTEGER NOT NULL
1192                         REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1193     marc_record_type    config.marc_record_type NOT NULL,
1194     tag                 CHAR(3) NOT NULL,
1195     code                CHAR(1) NOT NULL,
1196     description         TEXT,
1197     repeatable          BOOLEAN,
1198     mandatory           BOOLEAN,
1199     hidden              BOOLEAN,
1200     value_ctype         TEXT
1201                         REFERENCES config.record_attr_definition (name)
1202                             DEFERRABLE INITIALLY DEFERRED,
1203     owner               INTEGER -- REFERENCES actor.org_unit (id)
1204                         -- if the owner is null, the data about the subfield is
1205                         -- assumed to come from the controlling MARC standard
1206 );
1207
1208 COMMENT ON TABLE config.marc_subfield IS $$
1209 This table stores the list of subfields recognized by this Evergreen
1210 instance.  As with config.marc_field, of particular significance is the
1211 owner column; if it's set to a null value, the subfield definition is
1212 assumed to come from a national standards body; if it's set to a non-null
1213 value, the subfield definition is an OU-level addition to or override
1214 of the standard.
1215 $$;
1216
1217 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1218 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1219     ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1220     WHERE owner IS NULL;
1221 ALTER TABLE config.marc_subfield
1222     ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1223     CHECK ((owner IS NOT NULL) OR
1224            (
1225                 owner IS NULL AND
1226                 repeatable IS NOT NULL AND
1227                 mandatory IS NOT NULL AND
1228                 hidden IS NOT NULL
1229            )
1230           );
1231
1232 CREATE TABLE config.copy_tag_type (
1233     code            TEXT NOT NULL PRIMARY KEY,
1234     label           TEXT NOT NULL,
1235     owner           INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1236 );
1237
1238 CREATE INDEX config_copy_tag_type_owner_idx
1239     ON config.copy_tag_type (owner);
1240
1241 COMMIT;