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