c0b461369380ab8b9f77fddcca0eab7850d6fbcb
[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 ('1005', :eg_version); -- csharp/berick
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     is_available  BOOL    NOT NULL DEFAULT FALSE
400 );
401 COMMENT ON TABLE config.copy_status IS $$
402 Copy Statuses
403
404 The available copy statuses, and whether a copy in that
405 status is available for hold request capture.  0 (zero) is
406 the only special number in this set, meaning that the item
407 is available for immediate checkout, and is counted as available
408 in the OPAC.
409
410 Statuses with an ID below 100 are not removable, and have special
411 meaning in the code.  Do not change them except to translate the
412 textual name.
413
414 You may add and remove statuses above 100, and these can be used
415 to remove items from normal circulation without affecting the rest
416 of the copy's values or its location.
417 $$;
418
419 CREATE TABLE config.net_access_level (
420         id      SERIAL          PRIMARY KEY,
421         name    TEXT            NOT NULL UNIQUE
422 );
423 COMMENT ON TABLE config.net_access_level IS $$
424 Patron Network Access level
425
426 This will be used to inform the in-library firewall of how much
427 internet access the using patron should be allowed.
428 $$;
429
430
431 CREATE TABLE config.remote_account (
432     id          SERIAL  PRIMARY KEY,
433     label       TEXT    NOT NULL,
434     host        TEXT    NOT NULL,   -- name or IP, :port optional
435     username    TEXT,               -- optional, since we could default to $USER
436     password    TEXT,               -- optional, since we could use SSH keys, or anonymous login.
437     account     TEXT,               -- aka profile or FTP "account" command
438     path        TEXT,               -- aka directory
439     owner       INT     NOT NULL,   -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
440     last_activity TIMESTAMP WITH TIME ZONE
441 );
442
443 CREATE TABLE config.marc21_rec_type_map (
444     code        TEXT    PRIMARY KEY,
445     type_val    TEXT    NOT NULL,
446     blvl_val    TEXT    NOT NULL
447 );
448
449 CREATE TABLE config.marc21_ff_pos_map (
450     id          SERIAL  PRIMARY KEY,
451     fixed_field TEXT    NOT NULL,
452     tag         TEXT    NOT NULL,
453     rec_type    TEXT    NOT NULL,
454     start_pos   INT     NOT NULL,
455     length      INT     NOT NULL,
456     default_val TEXT    NOT NULL DEFAULT ' '
457 );
458
459 CREATE TABLE config.marc21_physical_characteristic_type_map (
460     ptype_key   TEXT    PRIMARY KEY,
461     label       TEXT    NOT NULL -- I18N
462 );
463
464 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
465     id          SERIAL  PRIMARY KEY,
466     ptype_key   TEXT    NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
467     subfield    TEXT    NOT NULL,
468     start_pos   INT     NOT NULL,
469     length      INT     NOT NULL,
470     label       TEXT    NOT NULL -- I18N
471 );
472
473 CREATE TABLE config.marc21_physical_characteristic_value_map (
474     id              SERIAL  PRIMARY KEY,
475     value           TEXT    NOT NULL,
476     ptype_subfield  INT     NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
477     label           TEXT    NOT NULL -- I18N
478 );
479
480
481 CREATE TABLE config.z3950_source (
482     name                TEXT    PRIMARY KEY,
483     label               TEXT    NOT NULL UNIQUE,
484     host                TEXT    NOT NULL,
485     port                INT     NOT NULL,
486     db                  TEXT    NOT NULL,
487     record_format       TEXT    NOT NULL DEFAULT 'FI',
488     transmission_format TEXT    NOT NULL DEFAULT 'usmarc',
489     auth                BOOL    NOT NULL DEFAULT TRUE,
490     use_perm            INT     -- REFERENCES permission.perm_list (id)
491 );
492
493 COMMENT ON TABLE config.z3950_source IS $$
494 Z39.50 Sources
495
496 Each row in this table represents a database searchable via Z39.50.
497 $$;
498
499 COMMENT ON COLUMN config.z3950_source.record_format IS $$
500 Z39.50 element set.
501 $$;
502
503 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
504 Z39.50 preferred record syntax..
505 $$;
506
507 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
508 If set, this permission is required for the source to be listed in the staff
509 client Z39.50 interface.  Similar to permission.grp_tree.application_perm.
510 $$;
511
512 CREATE TABLE config.z3950_attr (
513     id          SERIAL  PRIMARY KEY,
514     source      TEXT    NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
515     name        TEXT    NOT NULL,
516     label       TEXT    NOT NULL,
517     code        INT     NOT NULL,
518     format      INT     NOT NULL,
519     truncation  INT     NOT NULL DEFAULT 0,
520     CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
521 );
522
523 CREATE TABLE config.z3950_source_credentials (
524     id SERIAL PRIMARY KEY,
525     owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
526     source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
527     username TEXT,
528     password TEXT,
529     CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
530 );
531
532 CREATE TABLE config.i18n_locale (
533     code        TEXT    PRIMARY KEY,
534     marc_code   TEXT    NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
535     name        TEXT    UNIQUE NOT NULL,
536     description TEXT
537 );
538
539 CREATE TABLE config.i18n_core (
540     id              BIGSERIAL   PRIMARY KEY,
541     fq_field        TEXT        NOT NULL,
542     identity_value  TEXT        NOT NULL,
543     translation     TEXT        NOT NULL    REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
544     string          TEXT        NOT NULL
545 );
546
547 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
548
549 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
550 BEGIN
551
552     EXECUTE $$
553         UPDATE  config.i18n_core
554           SET   identity_value = $$ || quote_literal(new_ident) || $$ 
555           WHERE fq_field LIKE '$$ || hint || $$.%' 
556                 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
557
558     RETURN;
559
560 END;
561 $_$ LANGUAGE PLPGSQL;
562
563 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
564 BEGIN
565     PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
566     RETURN NEW;
567 END;
568 $_$ LANGUAGE PLPGSQL;
569
570 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
571 BEGIN
572     PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
573     RETURN NEW;
574 END;
575 $_$ LANGUAGE PLPGSQL;
576
577 CREATE TABLE config.billing_type (
578     id              SERIAL  PRIMARY KEY,
579     name            TEXT    NOT NULL,
580     owner           INT     NOT NULL, -- REFERENCES actor.org_unit (id)
581     default_price   NUMERIC(6,2),
582     CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
583 );
584
585 CREATE TABLE config.settings_group (
586     name    TEXT PRIMARY KEY,
587     label   TEXT UNIQUE NOT NULL -- I18N
588 );
589
590 CREATE TABLE config.org_unit_setting_type (
591     name            TEXT    PRIMARY KEY,
592     label           TEXT    UNIQUE NOT NULL,
593     grp             TEXT    REFERENCES config.settings_group (name),
594     description     TEXT,
595     datatype        TEXT    NOT NULL DEFAULT 'string',
596     fm_class        TEXT,
597     view_perm       INT,
598     update_perm     INT,
599     --
600     -- define valid datatypes
601     --
602     CONSTRAINT coust_valid_datatype CHECK ( datatype IN
603     ( 'bool', 'integer', 'float', 'currency', 'interval',
604       'date', 'string', 'object', 'array', 'link' ) ),
605     --
606     -- fm_class is meaningful only for 'link' datatype
607     --
608     CONSTRAINT coust_no_empty_link CHECK
609     ( ( datatype =  'link' AND fm_class IS NOT NULL ) OR
610       ( datatype <> 'link' AND fm_class IS NULL ) )
611 );
612
613 CREATE TABLE config.usr_setting_type (
614
615     name TEXT PRIMARY KEY,
616     opac_visible BOOL NOT NULL DEFAULT FALSE,
617     label TEXT UNIQUE NOT NULL,
618     description TEXT,
619     grp             TEXT    REFERENCES config.settings_group (name),
620     datatype TEXT NOT NULL DEFAULT 'string',
621     fm_class TEXT,
622     reg_default TEXT,
623
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     --
632     -- fm_class is meaningful only for 'link' datatype
633     --
634     CONSTRAINT coust_no_empty_link CHECK
635     ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
636         ( datatype <> 'link' AND fm_class IS NULL ) )
637
638 );
639
640 -- Some handy functions, based on existing ones, to provide optional ingest normalization
641
642 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
643         SELECT SUBSTRING($1,$2);
644 $func$ LANGUAGE SQL STRICT IMMUTABLE;
645
646 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
647         SELECT SUBSTRING($1,1,$2);
648 $func$ LANGUAGE SQL STRICT IMMUTABLE;
649
650 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
651         SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
652 $func$ LANGUAGE SQL STRICT IMMUTABLE;
653
654 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
655         SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
656 $func$ LANGUAGE SQL STRICT IMMUTABLE;
657
658 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
659         SELECT approximate_date( $1, '0');
660 $func$ LANGUAGE SQL STRICT IMMUTABLE;
661
662 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
663         SELECT approximate_date( $1, '9');
664 $func$ LANGUAGE SQL STRICT IMMUTABLE;
665
666 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
667         SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
668 $func$ LANGUAGE SQL STRICT IMMUTABLE;
669
670 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
671         SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
672 $func$ LANGUAGE SQL STRICT IMMUTABLE;
673
674 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
675     use Business::ISBN;
676     use strict;
677     use warnings;
678
679     # Find the first ISBN, force it to ISBN13 and return it
680
681     my $input = shift;
682
683     foreach my $word (split(/\s/, $input)) {
684         my $isbn = Business::ISBN->new($word);
685
686         # First check the checksum; if it is not valid, fix it and add the original
687         # bad-checksum ISBN to the output
688         if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
689             $isbn->fix_checksum();
690         }
691
692         # If we now have a valid ISBN, force it to ISBN13 and return it
693         return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
694     }
695     return undef;
696 $func$ LANGUAGE PLPERLU;
697
698 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
699 Inspired by translate_isbn1013
700
701 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
702 version without hypens and with a repaired checksum if the checksum was bad
703 $$;
704
705
706 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
707     use Business::ISBN;
708     use strict;
709     use warnings;
710
711     # For each ISBN found in a single string containing a set of ISBNs:
712     #   * Normalize an incoming ISBN to have the correct checksum and no hyphens
713     #   * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
714
715     my $input = shift;
716     my $output = '';
717
718     foreach my $word (split(/\s/, $input)) {
719         my $isbn = Business::ISBN->new($word);
720
721         # First check the checksum; if it is not valid, fix it and add the original
722         # bad-checksum ISBN to the output
723         if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
724             $output .= $isbn->isbn() . " ";
725             $isbn->fix_checksum();
726         }
727
728         # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
729         # and add the normalized original ISBN to the output
730         if ($isbn && $isbn->is_valid()) {
731             my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
732             $output .= $isbn->isbn . " ";
733
734             # If we successfully converted the ISBN to its counterpart, add the
735             # converted ISBN to the output as well
736             $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
737         }
738     }
739     return $output if $output;
740
741     # If there were no valid ISBNs, just return the raw input
742     return $input;
743 $func$ LANGUAGE PLPERLU;
744
745 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
746 The translate_isbn1013 function takes an input ISBN and returns the
747 following in a single space-delimited string if the input ISBN is valid:
748   - The normalized input ISBN (hyphens stripped)
749   - The normalized input ISBN with a fixed checksum if the checksum was bad
750   - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
751 $$;
752
753 -- And ... a table in which to register them
754
755 CREATE TABLE config.index_normalizer (
756         id              SERIAL  PRIMARY KEY,
757         name            TEXT    UNIQUE NOT NULL,
758         description     TEXT,
759         func            TEXT    NOT NULL,
760         param_count     INT     NOT NULL DEFAULT 0
761 );
762
763 CREATE TABLE config.metabib_field_index_norm_map (
764         id      SERIAL  PRIMARY KEY,
765         field   INT     NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
766         norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
767         params  TEXT,
768         pos     INT     NOT NULL DEFAULT 0
769 );
770
771 CREATE TABLE config.record_attr_definition (
772     name        TEXT    PRIMARY KEY,
773     label       TEXT    NOT NULL, -- I18N
774     description TEXT,
775     multi       BOOL    NOT NULL DEFAULT TRUE,  -- will store all values from a record
776     filter      BOOL    NOT NULL DEFAULT TRUE,  -- becomes QP filter if true
777     sorter      BOOL    NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
778     composite   BOOL    NOT NULL DEFAULT FALSE, -- its values are derived from others
779
780 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
781     tag         TEXT, -- LIKE format
782     sf_list     TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
783
784 -- This is used for both tag/sf and xpath entries
785     joiner      TEXT,
786
787 -- For xpath-extracted attrs
788     xpath       TEXT,
789     format      TEXT    REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
790     start_pos   INT,
791     string_len  INT,
792
793 -- For fixed fields
794     fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
795
796 -- For phys-char fields
797     phys_char_sf    INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
798
799 -- Source of vocabulary terms for this record attribute;
800 -- typically will be a URI referring to a SKOS vocabulary
801     vocabulary  TEXT
802 );
803
804 CREATE TABLE config.record_attr_index_norm_map (
805     id      SERIAL  PRIMARY KEY,
806     attr    TEXT    NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
807     norm    INT     NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
808     params  TEXT,
809     pos     INT     NOT NULL DEFAULT 0
810 );
811
812 CREATE TABLE config.coded_value_map (
813     id              SERIAL  PRIMARY KEY,
814     ctype           TEXT    NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
815     code            TEXT    NOT NULL,
816     value           TEXT    NOT NULL,
817     description     TEXT,
818     opac_visible    BOOL    NOT NULL DEFAULT TRUE, -- For TPac selectors
819     search_label    TEXT,
820     is_simple       BOOL    NOT NULL DEFAULT FALSE,
821     concept_uri     TEXT    -- URI expressing the SKOS concept that the
822                             -- coded value represents
823 );
824
825 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
826
827 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
828 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
829 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
830 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
831 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
832 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
833 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
834
835 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$
836 DECLARE
837     current_row config.coded_value_map%ROWTYPE;
838 BEGIN
839     -- Look for a current value
840     SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
841     -- If we have one..
842     IF FOUND AND NOT add_only THEN
843         -- Update anything we were handed
844         current_row.value := COALESCE(current_row.value, in_value);
845         current_row.description := COALESCE(current_row.description, in_description);
846         current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
847         current_row.search_label := COALESCE(current_row.search_label, in_search_label);
848         current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
849         UPDATE config.coded_value_map
850             SET
851                 value = current_row.value,
852                 description = current_row.description,
853                 opac_visible = current_row.opac_visible,
854                 search_label = current_row.search_label,
855                 is_simple = current_row.is_simple
856             WHERE id = current_row.id;
857     ELSE
858         INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
859             (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
860     END IF;
861 END;
862 $f$ LANGUAGE PLPGSQL;
863
864 CREATE TABLE config.composite_attr_entry_definition(
865     coded_value INT  PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
866     definition  TEXT    NOT NULL -- JSON
867 );
868
869 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
870 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
871     SELECT  DISTINCT l.version
872       FROM  config.upgrade_log l
873             JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
874       WHERE d.db_patch = $1
875 $$ LANGUAGE SQL;
876
877 -- List applied db patches that are superseded by (and block the application of) my_db_patch
878 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
879     SELECT  DISTINCT l.version
880       FROM  config.upgrade_log l
881             JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
882       WHERE d.db_patch = $1
883 $$ LANGUAGE SQL;
884
885 -- List applied db patches that deprecates (and block the application of) my_db_patch
886 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
887     SELECT  db_patch
888       FROM  config.db_patch_dependencies
889       WHERE ARRAY[$1]::TEXT[] && deprecates
890 $$ LANGUAGE SQL;
891
892 -- List applied db patches that supersedes (and block the application of) my_db_patch
893 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
894     SELECT  db_patch
895       FROM  config.db_patch_dependencies
896       WHERE ARRAY[$1]::TEXT[] && supersedes
897 $$ LANGUAGE SQL;
898
899 -- Make sure that no deprecated or superseded db patches are currently applied
900 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
901     SELECT  COUNT(*) = 0
902       FROM  (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
903                 UNION
904              SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
905                 UNION
906              SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
907                 UNION
908              SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
909 $$ LANGUAGE SQL;
910
911 -- Raise an exception if there are, in fact, dep/sup conflict
912 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
913 DECLARE 
914     deprecates TEXT;
915     supersedes TEXT;
916 BEGIN
917     IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
918         SELECT  STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
919         SELECT  STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
920         RAISE EXCEPTION '
921 Upgrade script % can not be applied:
922   applied deprecated scripts %
923   applied superseded scripts %
924   deprecated by %
925   superseded by %',
926             my_db_patch,
927             (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
928             (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
929             evergreen.upgrade_list_applied_deprecated(my_db_patch),
930             evergreen.upgrade_list_applied_superseded(my_db_patch);
931     END IF;
932
933     INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
934     RETURN TRUE;
935 END;
936 $$ LANGUAGE PLPGSQL;
937
938 CREATE TABLE config.barcode_completion (
939     id          SERIAL PRIMARY KEY,
940     active      BOOL NOT NULL DEFAULT true,
941     org_unit    INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
942     prefix      TEXT,
943     suffix      TEXT,
944     length      INT NOT NULL DEFAULT 0,
945     padding     TEXT,
946     padding_end BOOL NOT NULL DEFAULT false,
947     asset       BOOL NOT NULL DEFAULT true,
948     actor       BOOL NOT NULL DEFAULT true
949 );
950
951 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
952
953 -- Add support for logging, only keep the most recent five rows for each category. 
954
955
956 CREATE TABLE config.org_unit_setting_type_log (
957     id              BIGSERIAL   PRIMARY KEY,
958     date_applied    TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
959     org             INT,   --REFERENCES actor.org_unit (id),
960     original_value  TEXT,
961     new_value       TEXT,
962     field_name      TEXT      REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
963 );
964
965 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
966 Org Unit setting Logs
967
968 This table contains the most recent changes to each setting 
969 in actor.org_unit_setting, allowing for mistakes to be undone.
970 This is NOT meant to be an auditor, but rather an undo/redo.
971 $$;
972
973 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
974     BEGIN
975         -- Only keeps the most recent five settings changes.
976         DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN 
977         (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);
978         
979         IF (TG_OP = 'UPDATE') THEN
980             RETURN NEW;
981         ELSIF (TG_OP = 'INSERT') THEN
982             RETURN NEW;
983         END IF;
984         RETURN NULL;
985     END;
986 $oustl_limit$ LANGUAGE plpgsql;
987
988 CREATE TRIGGER limit_logs_oust
989     BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
990     FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
991
992 CREATE TABLE config.sms_carrier (
993     id              SERIAL PRIMARY KEY,
994     region          TEXT,
995     name            TEXT,
996     email_gateway   TEXT,
997     active          BOOLEAN DEFAULT TRUE
998 );
999
1000 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1001
1002 CREATE TABLE config.usr_activity_type (
1003     id          SERIAL                      PRIMARY KEY, 
1004     ewho        TEXT,
1005     ewhat       TEXT,
1006     ehow        TEXT,
1007     label       TEXT                        NOT NULL, -- i18n
1008     egroup      config.usr_activity_group   NOT NULL,
1009     enabled     BOOL                        NOT NULL DEFAULT TRUE,
1010     transient   BOOL                        NOT NULL DEFAULT TRUE,
1011     CONSTRAINT  one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1012 );
1013
1014 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type 
1015     (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1016
1017 CREATE TABLE config.filter_dialog_interface (
1018     key         TEXT                        PRIMARY KEY,
1019     description TEXT
1020 );  
1021
1022 CREATE TABLE config.filter_dialog_filter_set (
1023     id          SERIAL                      PRIMARY KEY,
1024     name        TEXT                        NOT NULL,
1025     owning_lib  INT                         NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1026     creator     INT                         NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1027     create_time TIMESTAMP WITH TIME ZONE    NOT NULL DEFAULT NOW(),
1028     interface   TEXT                        NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1029     filters     TEXT                        NOT NULL, -- CHECK (evergreen.is_json(filters))
1030     CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1031 );
1032
1033 CREATE TABLE config.best_hold_order(
1034     id          SERIAL      PRIMARY KEY,
1035     name        TEXT        UNIQUE,   -- i18n
1036     pprox       INT, -- copy capture <-> pickup lib prox
1037     hprox       INT, -- copy circ lib <-> request lib prox
1038     aprox       INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1039     approx      INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1040     priority    INT, -- group hold priority
1041     cut         INT, -- cut-in-line
1042     depth       INT, -- selection depth
1043     htime       INT, -- time since last home-lib circ exceeds org-unit setting
1044     rtime       INT, -- request time
1045     shtime      INT  -- time since copy last trip home exceeds org-unit setting
1046 );
1047
1048 -- At least one of these columns must contain a non-null value
1049 ALTER TABLE config.best_hold_order ADD CHECK ((
1050     pprox IS NOT NULL OR
1051     hprox IS NOT NULL OR
1052     aprox IS NOT NULL OR
1053     priority IS NOT NULL OR
1054     cut IS NOT NULL OR
1055     depth IS NOT NULL OR
1056     htime IS NOT NULL OR
1057     rtime IS NOT NULL
1058 ));
1059
1060 CREATE OR REPLACE FUNCTION 
1061     evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1062     BEGIN
1063
1064       PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1065
1066       IF FOUND THEN
1067         RETURN NULL;
1068       END IF;
1069
1070       RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1071
1072     END;
1073 $func$ LANGUAGE PLPGSQL STABLE;
1074
1075 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1076 Used by a config.z3950_index_field_map constraint trigger
1077 to verify z3950_attr_type maps.
1078 $$;
1079
1080 -- drop these in down here since they reference config.metabib_field
1081 -- and config.record_attr_definition
1082 CREATE TABLE config.z3950_index_field_map (
1083     id              SERIAL  PRIMARY KEY,
1084     label           TEXT    NOT NULL, -- i18n
1085     metabib_field   INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1086     record_attr     TEXT    REFERENCES config.record_attr_definition(name),
1087     z3950_attr      INTEGER REFERENCES config.z3950_attr(id),
1088     z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1089     CONSTRAINT metabib_field_or_record_attr CHECK (
1090         metabib_field IS NOT NULL OR 
1091         record_attr IS NOT NULL
1092     ),
1093     CONSTRAINT attr_or_attr_type CHECK (
1094         z3950_attr IS NOT NULL OR 
1095         z3950_attr_type IS NOT NULL
1096     )
1097 );
1098
1099 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1100     DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1101     EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1102
1103 CREATE TABLE config.marc_format (
1104     id                  SERIAL PRIMARY KEY,
1105     code                TEXT NOT NULL,
1106     name                TEXT NOT NULL
1107 );
1108 COMMENT ON TABLE config.marc_format IS $$
1109 List of MARC formats supported by this Evergreen
1110 database. This exists primarily as a hook for future
1111 support of UNIMARC, though whether that will ever
1112 happen remains to be seen.
1113 $$;
1114
1115 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1116
1117 CREATE TABLE config.marc_field (
1118     id                  SERIAL PRIMARY KEY,
1119     marc_format         INTEGER NOT NULL
1120                         REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1121     marc_record_type    config.marc_record_type NOT NULL,
1122     tag                 CHAR(3) NOT NULL,
1123     name                TEXT,
1124     description         TEXT,
1125     fixed_field         BOOLEAN,
1126     repeatable          BOOLEAN,
1127     mandatory           BOOLEAN,
1128     hidden              BOOLEAN,
1129     owner               INTEGER -- REFERENCES actor.org_unit (id)
1130                         -- if the owner is null, the data about the field is
1131                         -- assumed to come from the controlling MARC standard
1132 );
1133
1134 COMMENT ON TABLE config.marc_field IS $$
1135 This table stores a list of MARC fields recognized by the Evergreen
1136 instance.  Note that we're not aiming for completely generic ISO2709
1137 support: we're assuming things like three characters for a tag,
1138 one-character subfield labels, two indicators per variable data field,
1139 and the like, all of which are technically specializations of ISO2709.
1140
1141 Of particular significance is the owner column; if it's set to a null
1142 value, the field definition is assumed to come from a national
1143 standards body; if it's set to a non-null value, the field definition
1144 is an OU-level addition to or override of the standard.
1145 $$;
1146
1147 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1148 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1149
1150 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1151     ON config.marc_field(marc_format, marc_record_type, tag)
1152     WHERE owner IS NULL;
1153 ALTER TABLE config.marc_field
1154     ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1155     CHECK ((owner IS NOT NULL) OR
1156            (
1157                 owner IS NULL AND
1158                 repeatable IS NOT NULL AND
1159                 mandatory IS NOT NULL AND
1160                 hidden IS NOT NULL
1161            )
1162           );
1163
1164 CREATE TABLE config.marc_subfield (
1165     id                  SERIAL PRIMARY KEY,
1166     marc_format         INTEGER NOT NULL
1167                         REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1168     marc_record_type    config.marc_record_type NOT NULL,
1169     tag                 CHAR(3) NOT NULL,
1170     code                CHAR(1) NOT NULL,
1171     description         TEXT,
1172     repeatable          BOOLEAN,
1173     mandatory           BOOLEAN,
1174     hidden              BOOLEAN,
1175     value_ctype         TEXT
1176                         REFERENCES config.record_attr_definition (name)
1177                             DEFERRABLE INITIALLY DEFERRED,
1178     owner               INTEGER -- REFERENCES actor.org_unit (id)
1179                         -- if the owner is null, the data about the subfield is
1180                         -- assumed to come from the controlling MARC standard
1181 );
1182
1183 COMMENT ON TABLE config.marc_subfield IS $$
1184 This table stores the list of subfields recognized by this Evergreen
1185 instance.  As with config.marc_field, of particular significance is the
1186 owner column; if it's set to a null value, the subfield definition is
1187 assumed to come from a national standards body; if it's set to a non-null
1188 value, the subfield definition is an OU-level addition to or override
1189 of the standard.
1190 $$;
1191
1192 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1193 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1194     ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1195     WHERE owner IS NULL;
1196 ALTER TABLE config.marc_subfield
1197     ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1198     CHECK ((owner IS NOT NULL) OR
1199            (
1200                 owner IS NULL AND
1201                 repeatable IS NOT NULL AND
1202                 mandatory IS NOT NULL AND
1203                 hidden IS NOT NULL
1204            )
1205           );
1206
1207 COMMIT;