Open-ILS/src/sql/Pg/002.schema.config.sql

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