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 ('1337', :eg_version); -- khuckins/tmccanna/cmorgan/willis/mmorgan
96 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('3.9.0', :eg_version);
98 CREATE TABLE config.bib_source (
99 id SERIAL PRIMARY KEY,
100 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
101 source TEXT NOT NULL UNIQUE,
102 transcendant BOOL NOT NULL DEFAULT FALSE,
103 can_have_copies BOOL NOT NULL DEFAULT TRUE
105 COMMENT ON TABLE config.bib_source IS $$
106 This is table is used to set up the relative "quality" of each
107 MARC source, such as OCLC. Also identifies "transcendant" sources,
108 i.e., sources of bib records that should display in the OPAC
109 even if no copies or located URIs are attached. Also indicates if
110 the source is allowed to have actual copies on its bibs. Volumes
111 for targeted URIs are unaffected by this setting.
114 CREATE TABLE config.standing (
115 id SERIAL PRIMARY KEY,
116 value TEXT NOT NULL UNIQUE
118 COMMENT ON TABLE config.standing IS $$
121 This table contains the values that can be applied to a patron
122 by a staff member. These values should not be changed, other
123 than for translation, as the ID column is currently a "magic
124 number" in the source. :(
127 CREATE TABLE config.standing_penalty (
128 id SERIAL PRIMARY KEY,
129 name TEXT NOT NULL UNIQUE,
132 staff_alert BOOL NOT NULL DEFAULT FALSE,
134 ignore_proximity INTEGER
137 CREATE TABLE config.xml_transform (
138 name TEXT PRIMARY KEY,
139 namespace_uri TEXT NOT NULL,
140 prefix TEXT NOT NULL,
144 CREATE TABLE config.biblio_fingerprint (
145 id SERIAL PRIMARY KEY,
148 first_word BOOL NOT NULL DEFAULT FALSE,
149 format TEXT NOT NULL DEFAULT 'marcxml'
152 INSERT INTO config.biblio_fingerprint (name, xpath, format)
155 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
156 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
157 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
158 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
159 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
163 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
166 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
168 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
169 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
170 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
175 INSERT INTO config.biblio_fingerprint (name, xpath, format)
178 '//mods32:mods/mods32:titleInfo/mods32:partName',
182 INSERT INTO config.biblio_fingerprint (name, xpath, format)
185 '//mods32:mods/mods32:titleInfo/mods32:partNumber',
189 CREATE TABLE config.metabib_class (
190 name TEXT PRIMARY KEY,
191 label TEXT NOT NULL UNIQUE,
192 buoyant BOOL DEFAULT FALSE NOT NULL,
193 restrict BOOL DEFAULT FALSE NOT NULL,
194 combined BOOL DEFAULT FALSE NOT NULL,
195 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
196 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
197 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
198 d_weight NUMERIC DEFAULT 0.1 NOT NULL
201 CREATE TABLE config.metabib_field (
202 id SERIAL PRIMARY KEY,
203 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
207 weight INT NOT NULL DEFAULT 1,
208 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
209 search_field BOOL NOT NULL DEFAULT TRUE,
210 facet_field BOOL NOT NULL DEFAULT FALSE,
211 browse_field BOOL NOT NULL DEFAULT TRUE,
212 browse_nocase BOOL NOT NULL DEFAULT FALSE,
214 browse_sort_xpath TEXT,
217 authority_xpath TEXT,
219 restrict BOOL DEFAULT FALSE NOT NULL,
220 display_field BOOL NOT NULL DEFAULT TRUE
222 COMMENT ON TABLE config.metabib_field IS $$
223 XPath used for record indexing ingest
225 This table contains the XPath used to chop up MODS into its
226 indexable parts. Each XPath entry is named and assigned to
227 a "class" of either title, subject, author, keyword, series
231 CREATE TABLE config.metabib_field_virtual_map (
232 id SERIAL PRIMARY KEY,
233 real INT NOT NULL REFERENCES config.metabib_field (id),
234 virtual INT NOT NULL REFERENCES config.metabib_field (id),
235 weight INT NOT NULL DEFAULT 1
237 COMMENT ON TABLE config.metabib_field_virtual_map IS $$
238 Maps between real (physically extracted) index definitions
239 and virtual (target sync, no required extraction of its own)
242 The virtual side may not extract any data of its own, but
243 will collect data from all of the real fields. This reduces
244 extraction (ingest) overhead by eliminating duplcated extraction,
245 and allows for searching across novel combinations of fields, such
246 as names used as either subjects or authors. By preserving this
247 mapping rather than defining duplicate extractions, information
248 about the originating, "real" index definitions can be used
249 in interesting ways, such as highlighting in search results.
252 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
254 CREATE TABLE config.display_field_map (
255 name TEXT PRIMARY KEY,
256 field INTEGER REFERENCES config.metabib_field (id),
257 multi BOOLEAN DEFAULT FALSE
260 CREATE TABLE config.ts_config_list (
264 COMMENT ON TABLE config.ts_config_list IS $$
267 A list of full text configs with names and descriptions.
270 CREATE TABLE config.metabib_class_ts_map (
271 id SERIAL PRIMARY KEY,
272 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
273 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
274 active BOOL NOT NULL DEFAULT TRUE,
275 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
276 index_lang TEXT NULL,
277 search_lang TEXT NULL,
278 always BOOL NOT NULL DEFAULT true
280 COMMENT ON TABLE config.metabib_class_ts_map IS $$
281 Text Search Configs for metabib class indexing
283 This table contains text search config definitions for
284 storing index_vector values.
287 CREATE TABLE config.metabib_field_ts_map (
288 id SERIAL PRIMARY KEY,
289 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
290 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
291 active BOOL NOT NULL DEFAULT TRUE,
292 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
293 index_lang TEXT NULL,
294 search_lang TEXT NULL
296 COMMENT ON TABLE config.metabib_field_ts_map IS $$
297 Text Search Configs for metabib field indexing
299 This table contains text search config definitions for
300 storing index_vector values.
303 CREATE TABLE config.metabib_search_alias (
304 alias TEXT PRIMARY KEY,
305 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
306 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
309 CREATE TABLE config.non_cataloged_type (
310 id SERIAL PRIMARY KEY,
311 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
313 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
314 in_house BOOL NOT NULL DEFAULT FALSE,
315 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
317 COMMENT ON TABLE config.non_cataloged_type IS $$
318 Types of valid non-cataloged items.
321 CREATE TABLE config.identification_type (
322 id SERIAL PRIMARY KEY,
323 name TEXT NOT NULL UNIQUE
325 COMMENT ON TABLE config.identification_type IS $$
326 Types of valid patron identification.
328 Each patron must display at least one valid form of identification
329 in order to get a library card. This table lists those forms.
332 CREATE TABLE config.rule_circ_duration (
333 id SERIAL PRIMARY KEY,
334 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
335 extended INTERVAL NOT NULL,
336 normal INTERVAL NOT NULL,
337 shrt INTERVAL NOT NULL,
338 max_renewals INT NOT NULL,
339 max_auto_renewals INTEGER
341 COMMENT ON TABLE config.rule_circ_duration IS $$
342 Circulation Duration rules
344 Each circulation is given a duration based on one of these rules.
347 CREATE TABLE config.hard_due_date (
348 id SERIAL PRIMARY KEY,
349 name TEXT NOT NULL UNIQUE,
350 ceiling_date TIMESTAMPTZ NOT NULL,
351 forceto BOOL NOT NULL,
352 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
355 CREATE TABLE config.hard_due_date_values (
356 id SERIAL PRIMARY KEY,
357 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
358 DEFERRABLE INITIALLY DEFERRED,
359 ceiling_date TIMESTAMPTZ NOT NULL,
360 active_date TIMESTAMPTZ NOT NULL
363 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
365 temp_value config.hard_due_date_values%ROWTYPE;
369 SELECT DISTINCT ON (hard_due_date) *
370 FROM config.hard_due_date_values
371 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
372 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
374 UPDATE config.hard_due_date
375 SET ceiling_date = temp_value.ceiling_date
376 WHERE id = temp_value.hard_due_date
377 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
378 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
381 updated := updated + 1;
387 $func$ LANGUAGE plpgsql;
389 CREATE TABLE config.rule_max_fine (
390 id SERIAL PRIMARY KEY,
391 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
392 amount NUMERIC(6,2) NOT NULL,
393 is_percent BOOL NOT NULL DEFAULT FALSE
395 COMMENT ON TABLE config.rule_max_fine IS $$
396 Circulation Max Fine rules
398 Each circulation is given a maximum fine based on one of
402 CREATE TABLE config.rule_recurring_fine (
403 id SERIAL PRIMARY KEY,
404 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
405 high NUMERIC(6,2) NOT NULL,
406 normal NUMERIC(6,2) NOT NULL,
407 low NUMERIC(6,2) NOT NULL,
408 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
409 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
411 COMMENT ON TABLE config.rule_recurring_fine IS $$
412 Circulation Recurring Fine rules
414 Each circulation is given a recurring fine amount based on one of
415 these rules. Note that it is recommended to run the fine generator
416 (from cron) at least as frequently as the lowest recurrence interval
417 used by your circulation rules so that accrued fines will be up
422 CREATE TABLE config.rule_age_hold_protect (
423 id SERIAL PRIMARY KEY,
424 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
425 age INTERVAL NOT NULL,
428 COMMENT ON TABLE config.rule_age_hold_protect IS $$
429 Hold Item Age Protection rules
431 A hold request can only capture new(ish) items when they are
432 within a particular proximity of the pickup_lib of the request.
433 The proximity ('prox' column) is calculated by counting
434 the number of tree edges between the pickup_lib and either the
435 owning_lib or circ_lib of the copy that could fulfill the hold,
436 as determined by the distance_is_from_owner value of the hold matrix
437 rule controlling the hold request.
440 CREATE TABLE config.copy_status (
441 id SERIAL PRIMARY KEY,
442 name TEXT NOT NULL UNIQUE,
443 holdable BOOL NOT NULL DEFAULT FALSE,
444 opac_visible BOOL NOT NULL DEFAULT FALSE,
445 copy_active BOOL NOT NULL DEFAULT FALSE,
446 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
447 is_available BOOL NOT NULL DEFAULT FALSE,
448 hopeless_prone BOOL NOT NULL DEFAULT FALSE
450 COMMENT ON TABLE config.copy_status IS $$
453 The available copy statuses, and whether a copy in that
454 status is available for hold request capture. 0 (zero) is
455 the only special number in this set, meaning that the item
456 is available for immediate checkout, and is counted as available
459 Statuses with an ID below 100 are not removable, and have special
460 meaning in the code. Do not change them except to translate the
463 You may add and remove statuses above 100, and these can be used
464 to remove items from normal circulation without affecting the rest
465 of the copy's values or its location.
468 CREATE TABLE config.net_access_level (
469 id SERIAL PRIMARY KEY,
470 name TEXT NOT NULL UNIQUE
472 COMMENT ON TABLE config.net_access_level IS $$
473 Patron Network Access level
475 This will be used to inform the in-library firewall of how much
476 internet access the using patron should be allowed.
480 CREATE TABLE config.remote_account (
481 id SERIAL PRIMARY KEY,
483 host TEXT NOT NULL, -- name or IP, :port optional
484 username TEXT, -- optional, since we could default to $USER
485 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
486 account TEXT, -- aka profile or FTP "account" command
487 path TEXT, -- aka directory
488 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
489 last_activity TIMESTAMP WITH TIME ZONE
492 CREATE TABLE config.marc21_rec_type_map (
493 code TEXT PRIMARY KEY,
494 type_val TEXT NOT NULL,
495 blvl_val TEXT NOT NULL
498 CREATE TABLE config.marc21_ff_pos_map (
499 id SERIAL PRIMARY KEY,
500 fixed_field TEXT NOT NULL,
502 rec_type TEXT NOT NULL,
503 start_pos INT NOT NULL,
505 default_val TEXT NOT NULL DEFAULT ' '
508 CREATE TABLE config.marc21_physical_characteristic_type_map (
509 ptype_key TEXT PRIMARY KEY,
510 label TEXT NOT NULL -- I18N
513 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
514 id SERIAL PRIMARY KEY,
515 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
516 subfield TEXT NOT NULL,
517 start_pos INT NOT NULL,
519 label TEXT NOT NULL -- I18N
522 CREATE TABLE config.marc21_physical_characteristic_value_map (
523 id SERIAL PRIMARY KEY,
525 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
526 label TEXT NOT NULL -- I18N
530 CREATE TABLE config.z3950_source (
531 name TEXT PRIMARY KEY,
532 label TEXT NOT NULL UNIQUE,
536 record_format TEXT NOT NULL DEFAULT 'FI',
537 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
538 auth BOOL NOT NULL DEFAULT TRUE,
539 use_perm INT -- REFERENCES permission.perm_list (id)
542 COMMENT ON TABLE config.z3950_source IS $$
545 Each row in this table represents a database searchable via Z39.50.
548 COMMENT ON COLUMN config.z3950_source.record_format IS $$
552 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
553 Z39.50 preferred record syntax..
556 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
557 If set, this permission is required for the source to be listed in the staff
558 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
561 CREATE TABLE config.z3950_attr (
562 id SERIAL PRIMARY KEY,
563 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
568 truncation INT NOT NULL DEFAULT 0,
569 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
572 CREATE TABLE config.z3950_source_credentials (
573 id SERIAL PRIMARY KEY,
574 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
575 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
578 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
581 CREATE TABLE config.i18n_locale (
582 code TEXT PRIMARY KEY,
583 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
584 name TEXT UNIQUE NOT NULL,
586 rtl BOOL NOT NULL DEFAULT FALSE
589 CREATE TABLE config.i18n_core (
590 id BIGSERIAL PRIMARY KEY,
591 fq_field TEXT NOT NULL,
592 identity_value TEXT NOT NULL,
593 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
597 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
599 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
603 UPDATE config.i18n_core
604 SET identity_value = $$ || quote_literal(new_ident) || $$
605 WHERE fq_field LIKE '$$ || hint || $$.%'
606 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
611 $_$ LANGUAGE PLPGSQL;
613 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
615 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
618 $_$ LANGUAGE PLPGSQL;
620 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
622 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
625 $_$ LANGUAGE PLPGSQL;
627 CREATE TABLE config.billing_type (
628 id SERIAL PRIMARY KEY,
630 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
631 default_price NUMERIC(6,2),
632 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
635 CREATE TABLE config.settings_group (
636 name TEXT PRIMARY KEY,
637 label TEXT UNIQUE NOT NULL -- I18N
640 CREATE TABLE config.org_unit_setting_type (
641 name TEXT PRIMARY KEY,
642 label TEXT UNIQUE NOT NULL,
643 grp TEXT REFERENCES config.settings_group (name),
645 datatype TEXT NOT NULL DEFAULT 'string',
650 -- define valid datatypes
652 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
653 ( 'bool', 'integer', 'float', 'currency', 'interval',
654 'date', 'string', 'object', 'array', 'link' ) ),
656 -- fm_class is meaningful only for 'link' datatype
658 CONSTRAINT coust_no_empty_link CHECK
659 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
660 ( datatype <> 'link' AND fm_class IS NULL ) )
663 CREATE TABLE config.usr_setting_type (
665 name TEXT PRIMARY KEY,
666 opac_visible BOOL NOT NULL DEFAULT FALSE,
667 label TEXT UNIQUE NOT NULL,
669 grp TEXT REFERENCES config.settings_group (name),
670 datatype TEXT NOT NULL DEFAULT 'string',
675 -- define valid datatypes
677 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
678 ( 'bool', 'integer', 'float', 'currency', 'interval',
679 'date', 'string', 'object', 'array', 'link' ) ),
682 -- fm_class is meaningful only for 'link' datatype
684 CONSTRAINT coust_no_empty_link CHECK
685 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
686 ( datatype <> 'link' AND fm_class IS NULL ) )
690 CREATE TABLE config.workstation_setting_type (
691 name TEXT PRIMARY KEY,
692 label TEXT UNIQUE NOT NULL,
693 grp TEXT REFERENCES config.settings_group (name),
695 datatype TEXT NOT NULL DEFAULT 'string',
698 -- define valid datatypes
700 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
701 ( 'bool', 'integer', 'float', 'currency', 'interval',
702 'date', 'string', 'object', 'array', 'link' ) ),
704 -- fm_class is meaningful only for 'link' datatype
706 CONSTRAINT cwst_no_empty_link CHECK
707 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
708 ( datatype <> 'link' AND fm_class IS NULL ) )
711 -- Prevent setting types from being both user and workstation settings.
712 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
713 RETURNS TRIGGER AS $FUNC$
716 IF TG_TABLE_NAME = 'usr_setting_type' THEN
717 PERFORM TRUE FROM config.workstation_setting_type cwst
718 WHERE cwst.name = NEW.name;
724 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
725 PERFORM TRUE FROM config.usr_setting_type cust
726 WHERE cust.name = NEW.name;
733 '% Cannot be used as both a user setting and a workstation setting.',
736 $FUNC$ LANGUAGE PLPGSQL STABLE;
738 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
739 AFTER INSERT OR UPDATE ON config.usr_setting_type
740 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
742 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
743 AFTER INSERT OR UPDATE ON config.workstation_setting_type
744 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
748 -- Some handy functions, based on existing ones, to provide optional ingest normalization
750 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
751 SELECT SUBSTRING($1,$2);
752 $func$ LANGUAGE SQL STRICT IMMUTABLE;
754 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
755 SELECT SUBSTRING($1,1,$2);
756 $func$ LANGUAGE SQL STRICT IMMUTABLE;
758 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
759 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
760 $func$ LANGUAGE SQL STRICT IMMUTABLE;
762 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
763 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
764 $func$ LANGUAGE SQL STRICT IMMUTABLE;
766 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
767 SELECT approximate_date( $1, '0');
768 $func$ LANGUAGE SQL STRICT IMMUTABLE;
770 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
771 SELECT approximate_date( $1, '9');
772 $func$ LANGUAGE SQL STRICT IMMUTABLE;
774 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
775 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
776 $func$ LANGUAGE SQL STRICT IMMUTABLE;
778 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
779 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
780 $func$ LANGUAGE SQL STRICT IMMUTABLE;
782 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
787 # Find the first ISBN, force it to ISBN13 and return it
791 foreach my $word (split(/\s/, $input)) {
792 my $isbn = Business::ISBN->new($word);
794 # First check the checksum; if it is not valid, fix it and add the original
795 # bad-checksum ISBN to the output
796 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
797 $isbn->fix_checksum();
800 # If we now have a valid ISBN, force it to ISBN13 and return it
801 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
804 $func$ LANGUAGE PLPERLU;
806 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
807 Inspired by translate_isbn1013
809 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
810 version without hypens and with a repaired checksum if the checksum was bad
814 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
819 # For each ISBN found in a single string containing a set of ISBNs:
820 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
821 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
826 foreach my $word (split(/\s/, $input)) {
827 my $isbn = Business::ISBN->new($word);
829 # First check the checksum; if it is not valid, fix it and add the original
830 # bad-checksum ISBN to the output
831 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
832 $output .= $isbn->isbn() . " ";
833 $isbn->fix_checksum();
836 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
837 # and add the normalized original ISBN to the output
838 if ($isbn && $isbn->is_valid()) {
839 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
840 $output .= $isbn->isbn . " ";
842 # If we successfully converted the ISBN to its counterpart, add the
843 # converted ISBN to the output as well
844 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
847 return $output if $output;
849 # If there were no valid ISBNs, just return the raw input
851 $func$ LANGUAGE PLPERLU;
853 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
854 The translate_isbn1013 function takes an input ISBN and returns the
855 following in a single space-delimited string if the input ISBN is valid:
856 - The normalized input ISBN (hyphens stripped)
857 - The normalized input ISBN with a fixed checksum if the checksum was bad
858 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
861 -- And ... a table in which to register them
863 CREATE TABLE config.index_normalizer (
864 id SERIAL PRIMARY KEY,
865 name TEXT UNIQUE NOT NULL,
868 param_count INT NOT NULL DEFAULT 0
871 CREATE TABLE config.metabib_field_index_norm_map (
872 id SERIAL PRIMARY KEY,
873 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
874 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
876 pos INT NOT NULL DEFAULT 0
879 CREATE TABLE config.record_attr_definition (
880 name TEXT PRIMARY KEY,
881 label TEXT NOT NULL, -- I18N
883 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
884 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
885 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
886 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
888 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
889 tag TEXT, -- LIKE format
890 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
892 -- This is used for both tag/sf and xpath entries
895 -- For xpath-extracted attrs
897 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
902 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
904 -- For phys-char fields
905 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
907 -- Source of vocabulary terms for this record attribute;
908 -- typically will be a URI referring to a SKOS vocabulary
912 CREATE TABLE config.record_attr_index_norm_map (
913 id SERIAL PRIMARY KEY,
914 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
915 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
917 pos INT NOT NULL DEFAULT 0
920 CREATE TABLE config.coded_value_map (
921 id SERIAL PRIMARY KEY,
922 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
926 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
928 is_simple BOOL NOT NULL DEFAULT FALSE,
929 concept_uri TEXT -- URI expressing the SKOS concept that the
930 -- coded value represents
933 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
935 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
936 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
937 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
938 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
939 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
940 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
941 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
943 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$
945 current_row config.coded_value_map%ROWTYPE;
947 -- Look for a current value
948 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
950 IF FOUND AND NOT add_only THEN
951 -- Update anything we were handed
952 current_row.value := COALESCE(current_row.value, in_value);
953 current_row.description := COALESCE(current_row.description, in_description);
954 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
955 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
956 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
957 UPDATE config.coded_value_map
959 value = current_row.value,
960 description = current_row.description,
961 opac_visible = current_row.opac_visible,
962 search_label = current_row.search_label,
963 is_simple = current_row.is_simple
964 WHERE id = current_row.id;
966 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
967 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
970 $f$ LANGUAGE PLPGSQL;
972 CREATE TABLE config.composite_attr_entry_definition(
973 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
974 definition TEXT NOT NULL -- JSON
977 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
978 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
979 SELECT DISTINCT l.version
980 FROM config.upgrade_log l
981 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
982 WHERE d.db_patch = $1
985 -- List applied db patches that are superseded by (and block the application of) my_db_patch
986 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
987 SELECT DISTINCT l.version
988 FROM config.upgrade_log l
989 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
990 WHERE d.db_patch = $1
993 -- List applied db patches that deprecates (and block the application of) my_db_patch
994 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
996 FROM config.db_patch_dependencies
997 WHERE ARRAY[$1]::TEXT[] && deprecates
1000 -- List applied db patches that supersedes (and block the application of) my_db_patch
1001 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
1003 FROM config.db_patch_dependencies
1004 WHERE ARRAY[$1]::TEXT[] && supersedes
1007 -- Make sure that no deprecated or superseded db patches are currently applied
1008 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
1010 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
1012 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
1014 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
1016 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
1019 -- Raise an exception if there are, in fact, dep/sup conflict
1020 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
1025 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
1026 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
1027 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
1029 Upgrade script % can not be applied:
1030 applied deprecated scripts %
1031 applied superseded scripts %
1035 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
1036 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
1037 evergreen.upgrade_list_applied_deprecated(my_db_patch),
1038 evergreen.upgrade_list_applied_superseded(my_db_patch);
1041 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
1044 $$ LANGUAGE PLPGSQL;
1046 CREATE TABLE config.barcode_completion (
1047 id SERIAL PRIMARY KEY,
1048 active BOOL NOT NULL DEFAULT true,
1049 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
1052 length INT NOT NULL DEFAULT 0,
1054 padding_end BOOL NOT NULL DEFAULT false,
1055 asset BOOL NOT NULL DEFAULT true,
1056 actor BOOL NOT NULL DEFAULT true
1059 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
1061 -- Add support for logging, only keep the most recent five rows for each category.
1064 CREATE TABLE config.org_unit_setting_type_log (
1065 id BIGSERIAL PRIMARY KEY,
1066 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1067 org INT, --REFERENCES actor.org_unit (id),
1068 original_value TEXT,
1070 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1073 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1074 Org Unit setting Logs
1076 This table contains the most recent changes to each setting
1077 in actor.org_unit_setting, allowing for mistakes to be undone.
1078 This is NOT meant to be an auditor, but rather an undo/redo.
1081 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1083 -- Only keeps the most recent five settings changes.
1084 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1085 (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);
1087 IF (TG_OP = 'UPDATE') THEN
1089 ELSIF (TG_OP = 'INSERT') THEN
1094 $oustl_limit$ LANGUAGE plpgsql;
1096 CREATE TRIGGER limit_logs_oust
1097 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1098 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1100 CREATE TABLE config.sms_carrier (
1101 id SERIAL PRIMARY KEY,
1105 active BOOLEAN DEFAULT TRUE
1108 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1110 CREATE TABLE config.usr_activity_type (
1111 id SERIAL PRIMARY KEY,
1115 label TEXT NOT NULL, -- i18n
1116 egroup config.usr_activity_group NOT NULL,
1117 enabled BOOL NOT NULL DEFAULT TRUE,
1118 transient BOOL NOT NULL DEFAULT TRUE,
1119 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1122 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1123 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1125 CREATE TABLE config.filter_dialog_interface (
1126 key TEXT PRIMARY KEY,
1130 CREATE TABLE config.filter_dialog_filter_set (
1131 id SERIAL PRIMARY KEY,
1133 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1134 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1135 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1136 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1137 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1138 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1141 CREATE TABLE config.best_hold_order(
1142 id SERIAL PRIMARY KEY,
1143 name TEXT UNIQUE, -- i18n
1144 pprox INT, -- copy capture <-> pickup lib prox
1145 hprox INT, -- copy circ lib <-> request lib prox
1146 owning_lib_to_home_lib_prox INT, -- copy owning lib <-> user home lib prox
1147 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1148 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1149 priority INT, -- group hold priority
1150 cut INT, -- cut-in-line
1151 depth INT, -- selection depth
1152 htime INT, -- time since last home-lib circ exceeds org-unit setting
1153 rtime INT, -- request time
1154 shtime INT -- time since copy last trip home exceeds org-unit setting
1157 -- At least one of these columns must contain a non-null value
1158 ALTER TABLE config.best_hold_order ADD CHECK ((
1159 pprox IS NOT NULL OR
1160 hprox IS NOT NULL OR
1161 owning_lib_to_home_lib_prox IS NOT NULL OR
1162 aprox IS NOT NULL OR
1163 priority IS NOT NULL OR
1165 depth IS NOT NULL OR
1166 htime IS NOT NULL OR
1170 CREATE OR REPLACE FUNCTION
1171 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1174 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1180 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1183 $func$ LANGUAGE PLPGSQL STABLE;
1185 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1186 Used by a config.z3950_index_field_map constraint trigger
1187 to verify z3950_attr_type maps.
1190 -- drop these in down here since they reference config.metabib_field
1191 -- and config.record_attr_definition
1192 CREATE TABLE config.z3950_index_field_map (
1193 id SERIAL PRIMARY KEY,
1194 label TEXT NOT NULL, -- i18n
1195 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1196 record_attr TEXT REFERENCES config.record_attr_definition(name),
1197 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1198 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1199 CONSTRAINT metabib_field_or_record_attr CHECK (
1200 metabib_field IS NOT NULL OR
1201 record_attr IS NOT NULL
1203 CONSTRAINT attr_or_attr_type CHECK (
1204 z3950_attr IS NOT NULL OR
1205 z3950_attr_type IS NOT NULL
1209 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1210 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1211 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1213 CREATE TABLE config.marc_format (
1214 id SERIAL PRIMARY KEY,
1218 COMMENT ON TABLE config.marc_format IS $$
1219 List of MARC formats supported by this Evergreen
1220 database. This exists primarily as a hook for future
1221 support of UNIMARC, though whether that will ever
1222 happen remains to be seen.
1225 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1227 CREATE TABLE config.marc_field (
1228 id SERIAL PRIMARY KEY,
1229 marc_format INTEGER NOT NULL
1230 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1231 marc_record_type config.marc_record_type NOT NULL,
1232 tag CHAR(3) NOT NULL,
1235 fixed_field BOOLEAN,
1239 owner INTEGER -- REFERENCES actor.org_unit (id)
1240 -- if the owner is null, the data about the field is
1241 -- assumed to come from the controlling MARC standard
1244 COMMENT ON TABLE config.marc_field IS $$
1245 This table stores a list of MARC fields recognized by the Evergreen
1246 instance. Note that we're not aiming for completely generic ISO2709
1247 support: we're assuming things like three characters for a tag,
1248 one-character subfield labels, two indicators per variable data field,
1249 and the like, all of which are technically specializations of ISO2709.
1251 Of particular significance is the owner column; if it's set to a null
1252 value, the field definition is assumed to come from a national
1253 standards body; if it's set to a non-null value, the field definition
1254 is an OU-level addition to or override of the standard.
1257 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1258 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1260 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1261 ON config.marc_field(marc_format, marc_record_type, tag)
1262 WHERE owner IS NULL;
1263 ALTER TABLE config.marc_field
1264 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1265 CHECK ((owner IS NOT NULL) OR
1268 repeatable IS NOT NULL AND
1269 mandatory IS NOT NULL AND
1274 CREATE TABLE config.marc_subfield (
1275 id SERIAL PRIMARY KEY,
1276 marc_format INTEGER NOT NULL
1277 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1278 marc_record_type config.marc_record_type NOT NULL,
1279 tag CHAR(3) NOT NULL,
1280 code CHAR(1) NOT NULL,
1286 REFERENCES config.record_attr_definition (name)
1287 DEFERRABLE INITIALLY DEFERRED,
1288 owner INTEGER -- REFERENCES actor.org_unit (id)
1289 -- if the owner is null, the data about the subfield is
1290 -- assumed to come from the controlling MARC standard
1293 COMMENT ON TABLE config.marc_subfield IS $$
1294 This table stores the list of subfields recognized by this Evergreen
1295 instance. As with config.marc_field, of particular significance is the
1296 owner column; if it's set to a null value, the subfield definition is
1297 assumed to come from a national standards body; if it's set to a non-null
1298 value, the subfield definition is an OU-level addition to or override
1302 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1303 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1304 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1305 WHERE owner IS NULL;
1306 ALTER TABLE config.marc_subfield
1307 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1308 CHECK ((owner IS NOT NULL) OR
1311 repeatable IS NOT NULL AND
1312 mandatory IS NOT NULL AND
1317 CREATE TABLE config.copy_tag_type (
1318 code TEXT NOT NULL PRIMARY KEY,
1319 label TEXT NOT NULL,
1320 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1323 CREATE INDEX config_copy_tag_type_owner_idx
1324 ON config.copy_tag_type (owner);
1326 CREATE TABLE config.hold_type (
1328 hold_type TEXT UNIQUE,
1332 INSERT INTO config.hold_type (hold_type,description) VALUES
1334 ('V','Volume Hold'),
1336 ('M','Metarecord Hold'),
1337 ('R','Recall Hold'),
1339 ('I','Issuance Hold'),
1343 CREATE TABLE config.print_template (
1344 id SERIAL PRIMARY KEY,
1346 label TEXT NOT NULL, -- i18n
1347 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
1348 active BOOLEAN NOT NULL DEFAULT FALSE,
1349 locale TEXT REFERENCES config.i18n_locale(code)
1350 ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1351 content_type TEXT NOT NULL DEFAULT 'text/html',
1352 template TEXT NOT NULL,
1353 CONSTRAINT name_once_per_lib UNIQUE (owner, name),
1354 CONSTRAINT label_once_per_lib UNIQUE (owner, label)
1357 CREATE TABLE config.carousel_type (
1358 id SERIAL PRIMARY KEY,
1360 automatic BOOLEAN NOT NULL DEFAULT TRUE,
1361 filter_by_age BOOLEAN NOT NULL DEFAULT FALSE,
1362 filter_by_copy_owning_lib BOOLEAN NOT NULL DEFAULT FALSE,
1363 filter_by_copy_location BOOLEAN NOT NULL DEFAULT FALSE
1366 INSERT INTO config.carousel_type
1367 (id, name, automatic, filter_by_age, filter_by_copy_owning_lib, filter_by_copy_location)
1369 (1, 'Manual', FALSE, FALSE, FALSE, FALSE),
1370 (2, 'Newly Catalogued Items', TRUE, TRUE, TRUE, TRUE),
1371 (3, 'Recently Returned Items', TRUE, TRUE, TRUE, TRUE),
1372 (4, 'Top Circulated Items', TRUE, TRUE, TRUE, FALSE),
1373 (5, 'Newest Items By Shelving Location', TRUE, TRUE, TRUE, FALSE)
1376 SELECT SETVAL('config.carousel_type_id_seq'::TEXT, 100);
1378 CREATE TABLE config.geolocation_service (
1379 id SERIAL PRIMARY KEY,
1381 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
1387 CREATE TABLE config.ui_staff_portal_page_entry_type (
1388 code TEXT PRIMARY KEY,
1392 CREATE TABLE config.ui_staff_portal_page_entry (
1393 id SERIAL PRIMARY KEY,
1394 page_col INTEGER NOT NULL,
1395 col_pos INTEGER NOT NULL,
1396 entry_type TEXT NOT NULL, -- REFERENCES config.ui_staff_portal_page_entry_type(code)
1401 owner INT NOT NULL -- REFERENCES actor.org_unit (id)