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>
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.
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.
24 DROP SCHEMA IF EXISTS stats CASCADE;
25 DROP SCHEMA IF EXISTS config CASCADE;
31 COMMENT ON SCHEMA config IS $$
32 The config schema holds static configuration data for the
33 Evergreen installation.
36 CREATE TABLE config.internal_flag (
37 name TEXT PRIMARY KEY,
39 enabled BOOL NOT NULL DEFAULT FALSE
41 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_insert');
42 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.skip_on_update');
43 INSERT INTO config.internal_flag (name) VALUES ('ingest.reingest.force_on_same_marc');
44 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_located_uri');
45 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_full_rec');
46 INSERT INTO config.internal_flag (name) VALUES ('ingest.disable_metabib_rec_descriptor');
47 INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_display_indexing');
52 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
53 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');
56 CREATE TABLE config.global_flag (
58 ) INHERITS (config.internal_flag);
59 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
61 CREATE TABLE config.upgrade_log (
62 version TEXT PRIMARY KEY,
63 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
67 CREATE TABLE config.db_patch_dependencies (
68 db_patch TEXT PRIMARY KEY,
73 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
79 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
81 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
87 CREATE TRIGGER no_overlapping_sups
88 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
89 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
91 CREATE TRIGGER no_overlapping_deps
92 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
93 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
95 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('1378', :eg_version); -- miker / gmcharlt / JBoyer
97 CREATE TABLE config.bib_source (
98 id SERIAL PRIMARY KEY,
99 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
100 source TEXT NOT NULL UNIQUE,
101 transcendant BOOL NOT NULL DEFAULT FALSE,
102 can_have_copies BOOL NOT NULL DEFAULT TRUE
104 COMMENT ON TABLE config.bib_source IS $$
105 This is table is used to set up the relative "quality" of each
106 MARC source, such as OCLC. Also identifies "transcendant" sources,
107 i.e., sources of bib records that should display in the OPAC
108 even if no copies or located URIs are attached. Also indicates if
109 the source is allowed to have actual copies on its bibs. Volumes
110 for targeted URIs are unaffected by this setting.
113 CREATE TABLE config.standing (
114 id SERIAL PRIMARY KEY,
115 value TEXT NOT NULL UNIQUE
117 COMMENT ON TABLE config.standing IS $$
120 This table contains the values that can be applied to a patron
121 by a staff member. These values should not be changed, other
122 than for translation, as the ID column is currently a "magic
123 number" in the source. :(
126 CREATE TABLE config.standing_penalty (
127 id SERIAL PRIMARY KEY,
128 name TEXT NOT NULL UNIQUE,
131 staff_alert BOOL NOT NULL DEFAULT FALSE,
133 ignore_proximity INTEGER
136 CREATE TABLE config.xml_transform (
137 name TEXT PRIMARY KEY,
138 namespace_uri TEXT NOT NULL,
139 prefix TEXT NOT NULL,
143 CREATE TABLE config.biblio_fingerprint (
144 id SERIAL PRIMARY KEY,
147 first_word BOOL NOT NULL DEFAULT FALSE,
148 format TEXT NOT NULL DEFAULT 'marcxml'
151 INSERT INTO config.biblio_fingerprint (name, xpath, format)
154 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
155 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
157 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
158 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
162 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
165 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
168 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
169 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
174 INSERT INTO config.biblio_fingerprint (name, xpath, format)
177 '//mods32:mods/mods32:titleInfo/mods32:partName',
181 INSERT INTO config.biblio_fingerprint (name, xpath, format)
184 '//mods32:mods/mods32:titleInfo/mods32:partNumber',
188 CREATE TABLE config.metabib_class (
189 name TEXT PRIMARY KEY,
190 label TEXT NOT NULL UNIQUE,
191 buoyant BOOL DEFAULT FALSE NOT NULL,
192 restrict BOOL DEFAULT FALSE NOT NULL,
193 combined BOOL DEFAULT FALSE NOT NULL,
194 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
195 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
196 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
197 d_weight NUMERIC DEFAULT 0.1 NOT NULL,
198 variant_authority_suggestion BOOL NOT NULL DEFAULT TRUE,
199 symspell_transfer_case BOOL NOT NULL DEFAULT TRUE,
200 symspell_skip_correct BOOL NOT NULL DEFAULT FALSE,
201 symspell_suggestion_verbosity INT NOT NULL DEFAULT 2,
202 max_phrase_edit_distance INT NOT NULL DEFAULT 2,
203 suggestion_word_option_count INT NOT NULL DEFAULT 5,
204 max_suggestions INT NOT NULL DEFAULT -1,
205 low_result_threshold INT NOT NULL DEFAULT 0,
206 min_suggestion_use_threshold INT NOT NULL DEFAULT 1,
207 soundex_weight INT NOT NULL DEFAULT 0,
208 pg_trgm_weight INT NOT NULL DEFAULT 0,
209 keyboard_distance_weight INT NOT NULL DEFAULT 0
212 CREATE TABLE config.metabib_field (
213 id SERIAL PRIMARY KEY,
214 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
218 weight INT NOT NULL DEFAULT 1,
219 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
220 search_field BOOL NOT NULL DEFAULT TRUE,
221 facet_field BOOL NOT NULL DEFAULT FALSE,
222 browse_field BOOL NOT NULL DEFAULT TRUE,
223 browse_nocase BOOL NOT NULL DEFAULT FALSE,
225 browse_sort_xpath TEXT,
228 authority_xpath TEXT,
230 restrict BOOL DEFAULT FALSE NOT NULL,
231 display_field BOOL NOT NULL DEFAULT TRUE
233 COMMENT ON TABLE config.metabib_field IS $$
234 XPath used for record indexing ingest
236 This table contains the XPath used to chop up MODS into its
237 indexable parts. Each XPath entry is named and assigned to
238 a "class" of either title, subject, author, keyword, series
242 CREATE TABLE config.metabib_field_virtual_map (
243 id SERIAL PRIMARY KEY,
244 real INT NOT NULL REFERENCES config.metabib_field (id),
245 virtual INT NOT NULL REFERENCES config.metabib_field (id),
246 weight INT NOT NULL DEFAULT 1
248 COMMENT ON TABLE config.metabib_field_virtual_map IS $$
249 Maps between real (physically extracted) index definitions
250 and virtual (target sync, no required extraction of its own)
253 The virtual side may not extract any data of its own, but
254 will collect data from all of the real fields. This reduces
255 extraction (ingest) overhead by eliminating duplcated extraction,
256 and allows for searching across novel combinations of fields, such
257 as names used as either subjects or authors. By preserving this
258 mapping rather than defining duplicate extractions, information
259 about the originating, "real" index definitions can be used
260 in interesting ways, such as highlighting in search results.
263 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
265 CREATE TABLE config.display_field_map (
266 name TEXT PRIMARY KEY,
267 field INTEGER REFERENCES config.metabib_field (id),
268 multi BOOLEAN DEFAULT FALSE
271 CREATE TABLE config.ts_config_list (
275 COMMENT ON TABLE config.ts_config_list IS $$
278 A list of full text configs with names and descriptions.
281 CREATE TABLE config.metabib_class_ts_map (
282 id SERIAL PRIMARY KEY,
283 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
284 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
285 active BOOL NOT NULL DEFAULT TRUE,
286 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
287 index_lang TEXT NULL,
288 search_lang TEXT NULL,
289 always BOOL NOT NULL DEFAULT true
291 COMMENT ON TABLE config.metabib_class_ts_map IS $$
292 Text Search Configs for metabib class indexing
294 This table contains text search config definitions for
295 storing index_vector values.
298 CREATE TABLE config.metabib_field_ts_map (
299 id SERIAL PRIMARY KEY,
300 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
301 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
302 active BOOL NOT NULL DEFAULT TRUE,
303 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
304 index_lang TEXT NULL,
305 search_lang TEXT NULL
307 COMMENT ON TABLE config.metabib_field_ts_map IS $$
308 Text Search Configs for metabib field indexing
310 This table contains text search config definitions for
311 storing index_vector values.
314 CREATE TABLE config.metabib_search_alias (
315 alias TEXT PRIMARY KEY,
316 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
317 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
320 CREATE TABLE config.non_cataloged_type (
321 id SERIAL PRIMARY KEY,
322 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
324 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
325 in_house BOOL NOT NULL DEFAULT FALSE,
326 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
328 COMMENT ON TABLE config.non_cataloged_type IS $$
329 Types of valid non-cataloged items.
332 CREATE TABLE config.identification_type (
333 id SERIAL PRIMARY KEY,
334 name TEXT NOT NULL UNIQUE
336 COMMENT ON TABLE config.identification_type IS $$
337 Types of valid patron identification.
339 Each patron must display at least one valid form of identification
340 in order to get a library card. This table lists those forms.
343 CREATE TABLE config.rule_circ_duration (
344 id SERIAL PRIMARY KEY,
345 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
346 extended INTERVAL NOT NULL,
347 normal INTERVAL NOT NULL,
348 shrt INTERVAL NOT NULL,
349 max_renewals INT NOT NULL,
350 max_auto_renewals INTEGER
352 COMMENT ON TABLE config.rule_circ_duration IS $$
353 Circulation Duration rules
355 Each circulation is given a duration based on one of these rules.
358 CREATE TABLE config.hard_due_date (
359 id SERIAL PRIMARY KEY,
360 name TEXT NOT NULL UNIQUE,
361 ceiling_date TIMESTAMPTZ NOT NULL,
362 forceto BOOL NOT NULL,
363 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
366 CREATE TABLE config.hard_due_date_values (
367 id SERIAL PRIMARY KEY,
368 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
369 DEFERRABLE INITIALLY DEFERRED,
370 ceiling_date TIMESTAMPTZ NOT NULL,
371 active_date TIMESTAMPTZ NOT NULL
374 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
376 temp_value config.hard_due_date_values%ROWTYPE;
380 SELECT DISTINCT ON (hard_due_date) *
381 FROM config.hard_due_date_values
382 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
383 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
385 UPDATE config.hard_due_date
386 SET ceiling_date = temp_value.ceiling_date
387 WHERE id = temp_value.hard_due_date
388 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
389 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
392 updated := updated + 1;
398 $func$ LANGUAGE plpgsql;
400 CREATE TABLE config.rule_max_fine (
401 id SERIAL PRIMARY KEY,
402 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
403 amount NUMERIC(6,2) NOT NULL,
404 is_percent BOOL NOT NULL DEFAULT FALSE
406 COMMENT ON TABLE config.rule_max_fine IS $$
407 Circulation Max Fine rules
409 Each circulation is given a maximum fine based on one of
413 CREATE TABLE config.rule_recurring_fine (
414 id SERIAL PRIMARY KEY,
415 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
416 high NUMERIC(6,2) NOT NULL,
417 normal NUMERIC(6,2) NOT NULL,
418 low NUMERIC(6,2) NOT NULL,
419 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
420 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
422 COMMENT ON TABLE config.rule_recurring_fine IS $$
423 Circulation Recurring Fine rules
425 Each circulation is given a recurring fine amount based on one of
426 these rules. Note that it is recommended to run the fine generator
427 (from cron) at least as frequently as the lowest recurrence interval
428 used by your circulation rules so that accrued fines will be up
433 CREATE TABLE config.rule_age_hold_protect (
434 id SERIAL PRIMARY KEY,
435 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
436 age INTERVAL NOT NULL,
439 COMMENT ON TABLE config.rule_age_hold_protect IS $$
440 Hold Item Age Protection rules
442 A hold request can only capture new(ish) items when they are
443 within a particular proximity of the pickup_lib of the request.
444 The proximity ('prox' column) is calculated by counting
445 the number of tree edges between the pickup_lib and either the
446 owning_lib or circ_lib of the copy that could fulfill the hold,
447 as determined by the distance_is_from_owner value of the hold matrix
448 rule controlling the hold request.
451 CREATE TABLE config.copy_status (
452 id SERIAL PRIMARY KEY,
453 name TEXT NOT NULL UNIQUE,
454 holdable BOOL NOT NULL DEFAULT FALSE,
455 opac_visible BOOL NOT NULL DEFAULT FALSE,
456 copy_active BOOL NOT NULL DEFAULT FALSE,
457 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
458 is_available BOOL NOT NULL DEFAULT FALSE,
459 hopeless_prone BOOL NOT NULL DEFAULT FALSE
461 COMMENT ON TABLE config.copy_status IS $$
464 The available copy statuses, and whether a copy in that
465 status is available for hold request capture. 0 (zero) is
466 the only special number in this set, meaning that the item
467 is available for immediate checkout, and is counted as available
470 Statuses with an ID below 100 are not removable, and have special
471 meaning in the code. Do not change them except to translate the
474 You may add and remove statuses above 100, and these can be used
475 to remove items from normal circulation without affecting the rest
476 of the copy's values or its location.
479 CREATE TABLE config.net_access_level (
480 id SERIAL PRIMARY KEY,
481 name TEXT NOT NULL UNIQUE
483 COMMENT ON TABLE config.net_access_level IS $$
484 Patron Network Access level
486 This will be used to inform the in-library firewall of how much
487 internet access the using patron should be allowed.
491 CREATE TABLE config.remote_account (
492 id SERIAL PRIMARY KEY,
494 host TEXT NOT NULL, -- name or IP, :port optional
495 username TEXT, -- optional, since we could default to $USER
496 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
497 account TEXT, -- aka profile or FTP "account" command
498 path TEXT, -- aka directory
499 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
500 last_activity TIMESTAMP WITH TIME ZONE
503 CREATE TABLE config.marc21_rec_type_map (
504 code TEXT PRIMARY KEY,
505 type_val TEXT NOT NULL,
506 blvl_val TEXT NOT NULL
509 CREATE TABLE config.marc21_ff_pos_map (
510 id SERIAL PRIMARY KEY,
511 fixed_field TEXT NOT NULL,
513 rec_type TEXT NOT NULL,
514 start_pos INT NOT NULL,
516 default_val TEXT NOT NULL DEFAULT ' '
519 CREATE TABLE config.marc21_physical_characteristic_type_map (
520 ptype_key TEXT PRIMARY KEY,
521 label TEXT NOT NULL -- I18N
524 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
525 id SERIAL PRIMARY KEY,
526 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
527 subfield TEXT NOT NULL,
528 start_pos INT NOT NULL,
530 label TEXT NOT NULL -- I18N
533 CREATE TABLE config.marc21_physical_characteristic_value_map (
534 id SERIAL PRIMARY KEY,
536 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
537 label TEXT NOT NULL -- I18N
541 CREATE TABLE config.z3950_source (
542 name TEXT PRIMARY KEY,
543 label TEXT NOT NULL UNIQUE,
547 record_format TEXT NOT NULL DEFAULT 'FI',
548 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
549 auth BOOL NOT NULL DEFAULT TRUE,
550 use_perm INT -- REFERENCES permission.perm_list (id)
553 COMMENT ON TABLE config.z3950_source IS $$
556 Each row in this table represents a database searchable via Z39.50.
559 COMMENT ON COLUMN config.z3950_source.record_format IS $$
563 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
564 Z39.50 preferred record syntax..
567 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
568 If set, this permission is required for the source to be listed in the staff
569 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
572 CREATE TABLE config.z3950_attr (
573 id SERIAL PRIMARY KEY,
574 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
579 truncation INT NOT NULL DEFAULT 0,
580 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
583 CREATE TABLE config.z3950_source_credentials (
584 id SERIAL PRIMARY KEY,
585 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
586 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
589 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
592 CREATE TABLE config.i18n_locale (
593 code TEXT PRIMARY KEY,
594 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
595 name TEXT UNIQUE NOT NULL,
597 rtl BOOL NOT NULL DEFAULT FALSE
600 CREATE TABLE config.i18n_core (
601 id BIGSERIAL PRIMARY KEY,
602 fq_field TEXT NOT NULL,
603 identity_value TEXT NOT NULL,
604 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
608 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
610 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
614 UPDATE config.i18n_core
615 SET identity_value = $$ || quote_literal(new_ident) || $$
616 WHERE fq_field LIKE '$$ || hint || $$.%'
617 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
622 $_$ LANGUAGE PLPGSQL;
624 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
626 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
629 $_$ LANGUAGE PLPGSQL;
631 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
633 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
636 $_$ LANGUAGE PLPGSQL;
638 CREATE TABLE config.billing_type (
639 id SERIAL PRIMARY KEY,
641 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
642 default_price NUMERIC(6,2),
643 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
646 CREATE TABLE config.settings_group (
647 name TEXT PRIMARY KEY,
648 label TEXT UNIQUE NOT NULL -- I18N
651 CREATE TABLE config.org_unit_setting_type (
652 name TEXT PRIMARY KEY,
653 label TEXT UNIQUE NOT NULL,
654 grp TEXT REFERENCES config.settings_group (name),
656 datatype TEXT NOT NULL DEFAULT 'string',
661 -- define valid datatypes
663 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
664 ( 'bool', 'integer', 'float', 'currency', 'interval',
665 'date', 'string', 'object', 'array', 'link' ) ),
667 -- fm_class is meaningful only for 'link' datatype
669 CONSTRAINT coust_no_empty_link CHECK
670 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
671 ( datatype <> 'link' AND fm_class IS NULL ) )
674 CREATE TABLE config.usr_setting_type (
676 name TEXT PRIMARY KEY,
677 opac_visible BOOL NOT NULL DEFAULT FALSE,
678 label TEXT UNIQUE NOT NULL,
680 grp TEXT REFERENCES config.settings_group (name),
681 datatype TEXT NOT NULL DEFAULT 'string',
686 -- define valid datatypes
688 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
689 ( 'bool', 'integer', 'float', 'currency', 'interval',
690 'date', 'string', 'object', 'array', 'link' ) ),
693 -- fm_class is meaningful only for 'link' datatype
695 CONSTRAINT coust_no_empty_link CHECK
696 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
697 ( datatype <> 'link' AND fm_class IS NULL ) )
701 CREATE TABLE config.workstation_setting_type (
702 name TEXT PRIMARY KEY,
703 label TEXT UNIQUE NOT NULL,
704 grp TEXT REFERENCES config.settings_group (name),
706 datatype TEXT NOT NULL DEFAULT 'string',
709 -- define valid datatypes
711 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
712 ( 'bool', 'integer', 'float', 'currency', 'interval',
713 'date', 'string', 'object', 'array', 'link' ) ),
715 -- fm_class is meaningful only for 'link' datatype
717 CONSTRAINT cwst_no_empty_link CHECK
718 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
719 ( datatype <> 'link' AND fm_class IS NULL ) )
722 -- Prevent setting types from being both user and workstation settings.
723 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
724 RETURNS TRIGGER AS $FUNC$
727 IF TG_TABLE_NAME = 'usr_setting_type' THEN
728 PERFORM TRUE FROM config.workstation_setting_type cwst
729 WHERE cwst.name = NEW.name;
735 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
736 PERFORM TRUE FROM config.usr_setting_type cust
737 WHERE cust.name = NEW.name;
744 '% Cannot be used as both a user setting and a workstation setting.',
747 $FUNC$ LANGUAGE PLPGSQL STABLE;
749 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
750 AFTER INSERT OR UPDATE ON config.usr_setting_type
751 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
753 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
754 AFTER INSERT OR UPDATE ON config.workstation_setting_type
755 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
759 -- Some handy functions, based on existing ones, to provide optional ingest normalization
761 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
762 SELECT SUBSTRING($1,$2);
763 $func$ LANGUAGE SQL STRICT IMMUTABLE;
765 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
766 SELECT SUBSTRING($1,1,$2);
767 $func$ LANGUAGE SQL STRICT IMMUTABLE;
769 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
770 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
771 $func$ LANGUAGE SQL STRICT IMMUTABLE;
773 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
774 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
775 $func$ LANGUAGE SQL STRICT IMMUTABLE;
777 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
778 SELECT approximate_date( $1, '0');
779 $func$ LANGUAGE SQL STRICT IMMUTABLE;
781 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
782 SELECT approximate_date( $1, '9');
783 $func$ LANGUAGE SQL STRICT IMMUTABLE;
785 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
786 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
787 $func$ LANGUAGE SQL STRICT IMMUTABLE;
789 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
790 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
791 $func$ LANGUAGE SQL STRICT IMMUTABLE;
793 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
798 # Find the first ISBN, force it to ISBN13 and return it
802 foreach my $word (split(/\s/, $input)) {
803 my $isbn = Business::ISBN->new($word);
805 # First check the checksum; if it is not valid, fix it and add the original
806 # bad-checksum ISBN to the output
807 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
808 $isbn->fix_checksum();
811 # If we now have a valid ISBN, force it to ISBN13 and return it
812 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
815 $func$ LANGUAGE PLPERLU;
817 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
818 Inspired by translate_isbn1013
820 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
821 version without hypens and with a repaired checksum if the checksum was bad
825 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
830 # For each ISBN found in a single string containing a set of ISBNs:
831 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
832 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
837 foreach my $word (split(/\s/, $input)) {
838 my $isbn = Business::ISBN->new($word);
840 # First check the checksum; if it is not valid, fix it and add the original
841 # bad-checksum ISBN to the output
842 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
843 $output .= $isbn->isbn() . " ";
844 $isbn->fix_checksum();
847 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
848 # and add the normalized original ISBN to the output
849 if ($isbn && $isbn->is_valid()) {
850 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
851 $output .= $isbn->isbn . " ";
853 # If we successfully converted the ISBN to its counterpart, add the
854 # converted ISBN to the output as well
855 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
858 return $output if $output;
860 # If there were no valid ISBNs, just return the raw input
862 $func$ LANGUAGE PLPERLU;
864 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
865 The translate_isbn1013 function takes an input ISBN and returns the
866 following in a single space-delimited string if the input ISBN is valid:
867 - The normalized input ISBN (hyphens stripped)
868 - The normalized input ISBN with a fixed checksum if the checksum was bad
869 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
872 -- And ... a table in which to register them
874 CREATE TABLE config.index_normalizer (
875 id SERIAL PRIMARY KEY,
876 name TEXT UNIQUE NOT NULL,
879 param_count INT NOT NULL DEFAULT 0
882 CREATE TABLE config.metabib_field_index_norm_map (
883 id SERIAL PRIMARY KEY,
884 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
885 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
887 pos INT NOT NULL DEFAULT 0
890 CREATE TABLE config.record_attr_definition (
891 name TEXT PRIMARY KEY,
892 label TEXT NOT NULL, -- I18N
894 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
895 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
896 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
897 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
899 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
900 tag TEXT, -- LIKE format
901 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
903 -- This is used for both tag/sf and xpath entries
906 -- For xpath-extracted attrs
908 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
913 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
915 -- For phys-char fields
916 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
918 -- Source of vocabulary terms for this record attribute;
919 -- typically will be a URI referring to a SKOS vocabulary
923 CREATE TABLE config.record_attr_index_norm_map (
924 id SERIAL PRIMARY KEY,
925 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
926 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
928 pos INT NOT NULL DEFAULT 0
931 CREATE TABLE config.coded_value_map (
932 id SERIAL PRIMARY KEY,
933 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
937 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
939 is_simple BOOL NOT NULL DEFAULT FALSE,
940 concept_uri TEXT -- URI expressing the SKOS concept that the
941 -- coded value represents
944 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
946 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
947 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
948 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
949 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
950 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
951 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
952 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
954 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$
956 current_row config.coded_value_map%ROWTYPE;
958 -- Look for a current value
959 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
961 IF FOUND AND NOT add_only THEN
962 -- Update anything we were handed
963 current_row.value := COALESCE(current_row.value, in_value);
964 current_row.description := COALESCE(current_row.description, in_description);
965 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
966 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
967 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
968 UPDATE config.coded_value_map
970 value = current_row.value,
971 description = current_row.description,
972 opac_visible = current_row.opac_visible,
973 search_label = current_row.search_label,
974 is_simple = current_row.is_simple
975 WHERE id = current_row.id;
977 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
978 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
981 $f$ LANGUAGE PLPGSQL;
983 CREATE TABLE config.composite_attr_entry_definition(
984 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
985 definition TEXT NOT NULL -- JSON
988 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
989 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
990 SELECT DISTINCT l.version
991 FROM config.upgrade_log l
992 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
993 WHERE d.db_patch = $1
996 -- List applied db patches that are superseded by (and block the application of) my_db_patch
997 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
998 SELECT DISTINCT l.version
999 FROM config.upgrade_log l
1000 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
1001 WHERE d.db_patch = $1
1004 -- List applied db patches that deprecates (and block the application of) my_db_patch
1005 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
1007 FROM config.db_patch_dependencies
1008 WHERE ARRAY[$1]::TEXT[] && deprecates
1011 -- List applied db patches that supersedes (and block the application of) my_db_patch
1012 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
1014 FROM config.db_patch_dependencies
1015 WHERE ARRAY[$1]::TEXT[] && supersedes
1018 -- Make sure that no deprecated or superseded db patches are currently applied
1019 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
1021 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
1023 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
1025 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
1027 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
1030 -- Raise an exception if there are, in fact, dep/sup conflict
1031 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
1036 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
1037 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
1038 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
1040 Upgrade script % can not be applied:
1041 applied deprecated scripts %
1042 applied superseded scripts %
1046 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
1047 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
1048 evergreen.upgrade_list_applied_deprecated(my_db_patch),
1049 evergreen.upgrade_list_applied_superseded(my_db_patch);
1052 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
1055 $$ LANGUAGE PLPGSQL;
1057 CREATE TABLE config.barcode_completion (
1058 id SERIAL PRIMARY KEY,
1059 active BOOL NOT NULL DEFAULT true,
1060 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
1063 length INT NOT NULL DEFAULT 0,
1065 padding_end BOOL NOT NULL DEFAULT false,
1066 asset BOOL NOT NULL DEFAULT true,
1067 actor BOOL NOT NULL DEFAULT true
1070 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
1072 -- Add support for logging, only keep the most recent five rows for each category.
1075 CREATE TABLE config.org_unit_setting_type_log (
1076 id BIGSERIAL PRIMARY KEY,
1077 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1078 org INT, --REFERENCES actor.org_unit (id),
1079 original_value TEXT,
1081 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1084 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1085 Org Unit setting Logs
1087 This table contains the most recent changes to each setting
1088 in actor.org_unit_setting, allowing for mistakes to be undone.
1089 This is NOT meant to be an auditor, but rather an undo/redo.
1092 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1094 -- Only keeps the most recent five settings changes.
1095 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1096 (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);
1098 IF (TG_OP = 'UPDATE') THEN
1100 ELSIF (TG_OP = 'INSERT') THEN
1105 $oustl_limit$ LANGUAGE plpgsql;
1107 CREATE TRIGGER limit_logs_oust
1108 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1109 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1111 CREATE TABLE config.sms_carrier (
1112 id SERIAL PRIMARY KEY,
1116 active BOOLEAN DEFAULT TRUE
1119 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1121 CREATE TABLE config.usr_activity_type (
1122 id SERIAL PRIMARY KEY,
1126 label TEXT NOT NULL, -- i18n
1127 egroup config.usr_activity_group NOT NULL,
1128 enabled BOOL NOT NULL DEFAULT TRUE,
1129 transient BOOL NOT NULL DEFAULT TRUE,
1130 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1133 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1134 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1136 CREATE TABLE config.filter_dialog_interface (
1137 key TEXT PRIMARY KEY,
1141 CREATE TABLE config.filter_dialog_filter_set (
1142 id SERIAL PRIMARY KEY,
1144 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1145 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1146 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1147 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1148 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1149 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1152 CREATE TABLE config.best_hold_order(
1153 id SERIAL PRIMARY KEY,
1154 name TEXT UNIQUE, -- i18n
1155 pprox INT, -- copy capture <-> pickup lib prox
1156 hprox INT, -- copy circ lib <-> request lib prox
1157 owning_lib_to_home_lib_prox INT, -- copy owning lib <-> user home lib prox
1158 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1159 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1160 priority INT, -- group hold priority
1161 cut INT, -- cut-in-line
1162 depth INT, -- selection depth
1163 htime INT, -- time since last home-lib circ exceeds org-unit setting
1164 rtime INT, -- request time
1165 shtime INT -- time since copy last trip home exceeds org-unit setting
1168 -- At least one of these columns must contain a non-null value
1169 ALTER TABLE config.best_hold_order ADD CHECK ((
1170 pprox IS NOT NULL OR
1171 hprox IS NOT NULL OR
1172 owning_lib_to_home_lib_prox IS NOT NULL OR
1173 aprox IS NOT NULL OR
1174 priority IS NOT NULL OR
1176 depth IS NOT NULL OR
1177 htime IS NOT NULL OR
1181 CREATE OR REPLACE FUNCTION
1182 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1185 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1191 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1194 $func$ LANGUAGE PLPGSQL STABLE;
1196 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1197 Used by a config.z3950_index_field_map constraint trigger
1198 to verify z3950_attr_type maps.
1201 -- drop these in down here since they reference config.metabib_field
1202 -- and config.record_attr_definition
1203 CREATE TABLE config.z3950_index_field_map (
1204 id SERIAL PRIMARY KEY,
1205 label TEXT NOT NULL, -- i18n
1206 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1207 record_attr TEXT REFERENCES config.record_attr_definition(name),
1208 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1209 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1210 CONSTRAINT metabib_field_or_record_attr CHECK (
1211 metabib_field IS NOT NULL OR
1212 record_attr IS NOT NULL
1214 CONSTRAINT attr_or_attr_type CHECK (
1215 z3950_attr IS NOT NULL OR
1216 z3950_attr_type IS NOT NULL
1220 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1221 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1222 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1224 CREATE TABLE config.marc_format (
1225 id SERIAL PRIMARY KEY,
1229 COMMENT ON TABLE config.marc_format IS $$
1230 List of MARC formats supported by this Evergreen
1231 database. This exists primarily as a hook for future
1232 support of UNIMARC, though whether that will ever
1233 happen remains to be seen.
1236 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1238 CREATE TABLE config.marc_field (
1239 id SERIAL PRIMARY KEY,
1240 marc_format INTEGER NOT NULL
1241 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1242 marc_record_type config.marc_record_type NOT NULL,
1243 tag CHAR(3) NOT NULL,
1246 fixed_field BOOLEAN,
1250 owner INTEGER -- REFERENCES actor.org_unit (id)
1251 -- if the owner is null, the data about the field is
1252 -- assumed to come from the controlling MARC standard
1255 COMMENT ON TABLE config.marc_field IS $$
1256 This table stores a list of MARC fields recognized by the Evergreen
1257 instance. Note that we're not aiming for completely generic ISO2709
1258 support: we're assuming things like three characters for a tag,
1259 one-character subfield labels, two indicators per variable data field,
1260 and the like, all of which are technically specializations of ISO2709.
1262 Of particular significance is the owner column; if it's set to a null
1263 value, the field definition is assumed to come from a national
1264 standards body; if it's set to a non-null value, the field definition
1265 is an OU-level addition to or override of the standard.
1268 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1269 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1271 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1272 ON config.marc_field(marc_format, marc_record_type, tag)
1273 WHERE owner IS NULL;
1274 ALTER TABLE config.marc_field
1275 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1276 CHECK ((owner IS NOT NULL) OR
1279 repeatable IS NOT NULL AND
1280 mandatory IS NOT NULL AND
1285 CREATE TABLE config.marc_subfield (
1286 id SERIAL PRIMARY KEY,
1287 marc_format INTEGER NOT NULL
1288 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1289 marc_record_type config.marc_record_type NOT NULL,
1290 tag CHAR(3) NOT NULL,
1291 code CHAR(1) NOT NULL,
1297 REFERENCES config.record_attr_definition (name)
1298 DEFERRABLE INITIALLY DEFERRED,
1299 owner INTEGER -- REFERENCES actor.org_unit (id)
1300 -- if the owner is null, the data about the subfield is
1301 -- assumed to come from the controlling MARC standard
1304 COMMENT ON TABLE config.marc_subfield IS $$
1305 This table stores the list of subfields recognized by this Evergreen
1306 instance. As with config.marc_field, of particular significance is the
1307 owner column; if it's set to a null value, the subfield definition is
1308 assumed to come from a national standards body; if it's set to a non-null
1309 value, the subfield definition is an OU-level addition to or override
1313 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1314 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1315 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1316 WHERE owner IS NULL;
1317 ALTER TABLE config.marc_subfield
1318 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1319 CHECK ((owner IS NOT NULL) OR
1322 repeatable IS NOT NULL AND
1323 mandatory IS NOT NULL AND
1328 CREATE TABLE config.copy_tag_type (
1329 code TEXT NOT NULL PRIMARY KEY,
1330 label TEXT NOT NULL,
1331 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1334 CREATE INDEX config_copy_tag_type_owner_idx
1335 ON config.copy_tag_type (owner);
1337 CREATE TABLE config.hold_type (
1339 hold_type TEXT UNIQUE,
1343 INSERT INTO config.hold_type (hold_type,description) VALUES
1345 ('V','Volume Hold'),
1347 ('M','Metarecord Hold'),
1348 ('R','Recall Hold'),
1350 ('I','Issuance Hold'),
1354 CREATE TABLE config.print_template (
1355 id SERIAL PRIMARY KEY,
1357 label TEXT NOT NULL, -- i18n
1358 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
1359 active BOOLEAN NOT NULL DEFAULT FALSE,
1360 locale TEXT REFERENCES config.i18n_locale(code)
1361 ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1362 content_type TEXT NOT NULL DEFAULT 'text/html',
1363 template TEXT NOT NULL,
1364 CONSTRAINT name_once_per_lib UNIQUE (owner, name),
1365 CONSTRAINT label_once_per_lib UNIQUE (owner, label)
1368 CREATE TABLE config.carousel_type (
1369 id SERIAL PRIMARY KEY,
1371 automatic BOOLEAN NOT NULL DEFAULT TRUE,
1372 filter_by_age BOOLEAN NOT NULL DEFAULT FALSE,
1373 filter_by_copy_owning_lib BOOLEAN NOT NULL DEFAULT FALSE,
1374 filter_by_copy_location BOOLEAN NOT NULL DEFAULT FALSE
1377 INSERT INTO config.carousel_type
1378 (id, name, automatic, filter_by_age, filter_by_copy_owning_lib, filter_by_copy_location)
1380 (1, 'Manual', FALSE, FALSE, FALSE, FALSE),
1381 (2, 'Newly Catalogued Items', TRUE, TRUE, TRUE, TRUE),
1382 (3, 'Recently Returned Items', TRUE, TRUE, TRUE, TRUE),
1383 (4, 'Top Circulated Items', TRUE, TRUE, TRUE, FALSE),
1384 (5, 'Newest Items By Shelving Location', TRUE, TRUE, TRUE, FALSE)
1387 SELECT SETVAL('config.carousel_type_id_seq'::TEXT, 100);
1389 CREATE TABLE config.geolocation_service (
1390 id SERIAL PRIMARY KEY,
1392 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
1398 CREATE TABLE config.ui_staff_portal_page_entry_type (
1399 code TEXT PRIMARY KEY,
1403 CREATE TABLE config.ui_staff_portal_page_entry (
1404 id SERIAL PRIMARY KEY,
1405 page_col INTEGER NOT NULL,
1406 col_pos INTEGER NOT NULL,
1407 entry_type TEXT NOT NULL, -- REFERENCES config.ui_staff_portal_page_entry_type(code)
1412 owner INT NOT NULL -- REFERENCES actor.org_unit (id)
1415 -- Add OpenAthens Integration
1416 CREATE TABLE config.openathens_uid_field (
1417 id SERIAL PRIMARY KEY,
1421 INSERT INTO config.openathens_uid_field
1428 SELECT SETVAL('config.openathens_uid_field_id_seq'::TEXT, 100);
1430 CREATE TABLE config.openathens_name_field (
1431 id SERIAL PRIMARY KEY,
1435 INSERT INTO config.openathens_name_field
1443 SELECT SETVAL('config.openathens_name_field_id_seq'::TEXT, 100);
1445 CREATE TABLE config.openathens_identity (
1446 id SERIAL PRIMARY KEY,
1447 active BOOL NOT NULL DEFAULT true,
1448 org_unit INT NOT NULL, -- REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1449 api_key TEXT NOT NULL,
1450 connection_id TEXT NOT NULL,
1451 connection_uri TEXT NOT NULL,
1452 auto_signon_enabled BOOL NOT NULL DEFAULT true,
1453 auto_signout_enabled BOOL NOT NULL DEFAULT false,
1454 unique_identifier INT NOT NULL REFERENCES config.openathens_uid_field (id) DEFAULT 1,
1455 display_name INT NOT NULL REFERENCES config.openathens_name_field (id) DEFAULT 1,
1456 release_prefix BOOL NOT NULL DEFAULT false,
1457 release_first_given_name BOOL NOT NULL DEFAULT false,
1458 release_second_given_name BOOL NOT NULL DEFAULT false,
1459 release_family_name BOOL NOT NULL DEFAULT false,
1460 release_suffix BOOL NOT NULL DEFAULT false,
1461 release_email BOOL NOT NULL DEFAULT false,
1462 release_home_ou BOOL NOT NULL DEFAULT false,
1463 release_barcode BOOL NOT NULL DEFAULT false