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 ('1294', :eg_version); -- mmorgan / tlittle / 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
200 CREATE TABLE config.metabib_field (
201 id SERIAL PRIMARY KEY,
202 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
206 weight INT NOT NULL DEFAULT 1,
207 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
208 search_field BOOL NOT NULL DEFAULT TRUE,
209 facet_field BOOL NOT NULL DEFAULT FALSE,
210 browse_field BOOL NOT NULL DEFAULT TRUE,
212 browse_sort_xpath TEXT,
215 authority_xpath TEXT,
217 restrict BOOL DEFAULT FALSE NOT NULL,
218 display_field BOOL NOT NULL DEFAULT TRUE
220 COMMENT ON TABLE config.metabib_field IS $$
221 XPath used for record indexing ingest
223 This table contains the XPath used to chop up MODS into its
224 indexable parts. Each XPath entry is named and assigned to
225 a "class" of either title, subject, author, keyword, series
229 CREATE TABLE config.metabib_field_virtual_map (
230 id SERIAL PRIMARY KEY,
231 real INT NOT NULL REFERENCES config.metabib_field (id),
232 virtual INT NOT NULL REFERENCES config.metabib_field (id),
233 weight INT NOT NULL DEFAULT 1
235 COMMENT ON TABLE config.metabib_field_virtual_map IS $$
236 Maps between real (physically extracted) index definitions
237 and virtual (target sync, no required extraction of its own)
240 The virtual side may not extract any data of its own, but
241 will collect data from all of the real fields. This reduces
242 extraction (ingest) overhead by eliminating duplcated extraction,
243 and allows for searching across novel combinations of fields, such
244 as names used as either subjects or authors. By preserving this
245 mapping rather than defining duplicate extractions, information
246 about the originating, "real" index definitions can be used
247 in interesting ways, such as highlighting in search results.
250 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
252 CREATE TABLE config.display_field_map (
253 name TEXT PRIMARY KEY,
254 field INTEGER REFERENCES config.metabib_field (id),
255 multi BOOLEAN DEFAULT FALSE
258 CREATE TABLE config.ts_config_list (
262 COMMENT ON TABLE config.ts_config_list IS $$
265 A list of full text configs with names and descriptions.
268 CREATE TABLE config.metabib_class_ts_map (
269 id SERIAL PRIMARY KEY,
270 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
271 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
272 active BOOL NOT NULL DEFAULT TRUE,
273 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
274 index_lang TEXT NULL,
275 search_lang TEXT NULL,
276 always BOOL NOT NULL DEFAULT true
278 COMMENT ON TABLE config.metabib_class_ts_map IS $$
279 Text Search Configs for metabib class indexing
281 This table contains text search config definitions for
282 storing index_vector values.
285 CREATE TABLE config.metabib_field_ts_map (
286 id SERIAL PRIMARY KEY,
287 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
288 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
289 active BOOL NOT NULL DEFAULT TRUE,
290 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
291 index_lang TEXT NULL,
292 search_lang TEXT NULL
294 COMMENT ON TABLE config.metabib_field_ts_map IS $$
295 Text Search Configs for metabib field indexing
297 This table contains text search config definitions for
298 storing index_vector values.
301 CREATE TABLE config.metabib_search_alias (
302 alias TEXT PRIMARY KEY,
303 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
304 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
307 CREATE TABLE config.non_cataloged_type (
308 id SERIAL PRIMARY KEY,
309 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
311 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
312 in_house BOOL NOT NULL DEFAULT FALSE,
313 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
315 COMMENT ON TABLE config.non_cataloged_type IS $$
316 Types of valid non-cataloged items.
319 CREATE TABLE config.identification_type (
320 id SERIAL PRIMARY KEY,
321 name TEXT NOT NULL UNIQUE
323 COMMENT ON TABLE config.identification_type IS $$
324 Types of valid patron identification.
326 Each patron must display at least one valid form of identification
327 in order to get a library card. This table lists those forms.
330 CREATE TABLE config.rule_circ_duration (
331 id SERIAL PRIMARY KEY,
332 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
333 extended INTERVAL NOT NULL,
334 normal INTERVAL NOT NULL,
335 shrt INTERVAL NOT NULL,
336 max_renewals INT NOT NULL,
337 max_auto_renewals INTEGER
339 COMMENT ON TABLE config.rule_circ_duration IS $$
340 Circulation Duration rules
342 Each circulation is given a duration based on one of these rules.
345 CREATE TABLE config.hard_due_date (
346 id SERIAL PRIMARY KEY,
347 name TEXT NOT NULL UNIQUE,
348 ceiling_date TIMESTAMPTZ NOT NULL,
349 forceto BOOL NOT NULL,
350 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
353 CREATE TABLE config.hard_due_date_values (
354 id SERIAL PRIMARY KEY,
355 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
356 DEFERRABLE INITIALLY DEFERRED,
357 ceiling_date TIMESTAMPTZ NOT NULL,
358 active_date TIMESTAMPTZ NOT NULL
361 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
363 temp_value config.hard_due_date_values%ROWTYPE;
367 SELECT DISTINCT ON (hard_due_date) *
368 FROM config.hard_due_date_values
369 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
370 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
372 UPDATE config.hard_due_date
373 SET ceiling_date = temp_value.ceiling_date
374 WHERE id = temp_value.hard_due_date
375 AND ceiling_date <> temp_value.ceiling_date -- Time is equal if we've already updated the chdd
376 AND temp_value.ceiling_date >= NOW(); -- Don't update ceiling dates to the past
379 updated := updated + 1;
385 $func$ LANGUAGE plpgsql;
387 CREATE TABLE config.rule_max_fine (
388 id SERIAL PRIMARY KEY,
389 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
390 amount NUMERIC(6,2) NOT NULL,
391 is_percent BOOL NOT NULL DEFAULT FALSE
393 COMMENT ON TABLE config.rule_max_fine IS $$
394 Circulation Max Fine rules
396 Each circulation is given a maximum fine based on one of
400 CREATE TABLE config.rule_recurring_fine (
401 id SERIAL PRIMARY KEY,
402 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
403 high NUMERIC(6,2) NOT NULL,
404 normal NUMERIC(6,2) NOT NULL,
405 low NUMERIC(6,2) NOT NULL,
406 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
407 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
409 COMMENT ON TABLE config.rule_recurring_fine IS $$
410 Circulation Recurring Fine rules
412 Each circulation is given a recurring fine amount based on one of
413 these rules. Note that it is recommended to run the fine generator
414 (from cron) at least as frequently as the lowest recurrence interval
415 used by your circulation rules so that accrued fines will be up
420 CREATE TABLE config.rule_age_hold_protect (
421 id SERIAL PRIMARY KEY,
422 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
423 age INTERVAL NOT NULL,
426 COMMENT ON TABLE config.rule_age_hold_protect IS $$
427 Hold Item Age Protection rules
429 A hold request can only capture new(ish) items when they are
430 within a particular proximity of the pickup_lib of the request.
431 The proximity ('prox' column) is calculated by counting
432 the number of tree edges between the pickup_lib and either the
433 owning_lib or circ_lib of the copy that could fulfill the hold,
434 as determined by the distance_is_from_owner value of the hold matrix
435 rule controlling the hold request.
438 CREATE TABLE config.copy_status (
439 id SERIAL PRIMARY KEY,
440 name TEXT NOT NULL UNIQUE,
441 holdable BOOL NOT NULL DEFAULT FALSE,
442 opac_visible BOOL NOT NULL DEFAULT FALSE,
443 copy_active BOOL NOT NULL DEFAULT FALSE,
444 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
445 is_available BOOL NOT NULL DEFAULT FALSE,
446 hopeless_prone BOOL NOT NULL DEFAULT FALSE
448 COMMENT ON TABLE config.copy_status IS $$
451 The available copy statuses, and whether a copy in that
452 status is available for hold request capture. 0 (zero) is
453 the only special number in this set, meaning that the item
454 is available for immediate checkout, and is counted as available
457 Statuses with an ID below 100 are not removable, and have special
458 meaning in the code. Do not change them except to translate the
461 You may add and remove statuses above 100, and these can be used
462 to remove items from normal circulation without affecting the rest
463 of the copy's values or its location.
466 CREATE TABLE config.net_access_level (
467 id SERIAL PRIMARY KEY,
468 name TEXT NOT NULL UNIQUE
470 COMMENT ON TABLE config.net_access_level IS $$
471 Patron Network Access level
473 This will be used to inform the in-library firewall of how much
474 internet access the using patron should be allowed.
478 CREATE TABLE config.remote_account (
479 id SERIAL PRIMARY KEY,
481 host TEXT NOT NULL, -- name or IP, :port optional
482 username TEXT, -- optional, since we could default to $USER
483 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
484 account TEXT, -- aka profile or FTP "account" command
485 path TEXT, -- aka directory
486 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
487 last_activity TIMESTAMP WITH TIME ZONE
490 CREATE TABLE config.marc21_rec_type_map (
491 code TEXT PRIMARY KEY,
492 type_val TEXT NOT NULL,
493 blvl_val TEXT NOT NULL
496 CREATE TABLE config.marc21_ff_pos_map (
497 id SERIAL PRIMARY KEY,
498 fixed_field TEXT NOT NULL,
500 rec_type TEXT NOT NULL,
501 start_pos INT NOT NULL,
503 default_val TEXT NOT NULL DEFAULT ' '
506 CREATE TABLE config.marc21_physical_characteristic_type_map (
507 ptype_key TEXT PRIMARY KEY,
508 label TEXT NOT NULL -- I18N
511 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
512 id SERIAL PRIMARY KEY,
513 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
514 subfield TEXT NOT NULL,
515 start_pos INT NOT NULL,
517 label TEXT NOT NULL -- I18N
520 CREATE TABLE config.marc21_physical_characteristic_value_map (
521 id SERIAL PRIMARY KEY,
523 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
524 label TEXT NOT NULL -- I18N
528 CREATE TABLE config.z3950_source (
529 name TEXT PRIMARY KEY,
530 label TEXT NOT NULL UNIQUE,
534 record_format TEXT NOT NULL DEFAULT 'FI',
535 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
536 auth BOOL NOT NULL DEFAULT TRUE,
537 use_perm INT -- REFERENCES permission.perm_list (id)
540 COMMENT ON TABLE config.z3950_source IS $$
543 Each row in this table represents a database searchable via Z39.50.
546 COMMENT ON COLUMN config.z3950_source.record_format IS $$
550 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
551 Z39.50 preferred record syntax..
554 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
555 If set, this permission is required for the source to be listed in the staff
556 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
559 CREATE TABLE config.z3950_attr (
560 id SERIAL PRIMARY KEY,
561 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
566 truncation INT NOT NULL DEFAULT 0,
567 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
570 CREATE TABLE config.z3950_source_credentials (
571 id SERIAL PRIMARY KEY,
572 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
573 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
576 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
579 CREATE TABLE config.i18n_locale (
580 code TEXT PRIMARY KEY,
581 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
582 name TEXT UNIQUE NOT NULL,
584 rtl BOOL NOT NULL DEFAULT FALSE
587 CREATE TABLE config.i18n_core (
588 id BIGSERIAL PRIMARY KEY,
589 fq_field TEXT NOT NULL,
590 identity_value TEXT NOT NULL,
591 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
595 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
597 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
601 UPDATE config.i18n_core
602 SET identity_value = $$ || quote_literal(new_ident) || $$
603 WHERE fq_field LIKE '$$ || hint || $$.%'
604 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
609 $_$ LANGUAGE PLPGSQL;
611 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
613 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
616 $_$ LANGUAGE PLPGSQL;
618 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
620 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
623 $_$ LANGUAGE PLPGSQL;
625 CREATE TABLE config.billing_type (
626 id SERIAL PRIMARY KEY,
628 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
629 default_price NUMERIC(6,2),
630 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
633 CREATE TABLE config.settings_group (
634 name TEXT PRIMARY KEY,
635 label TEXT UNIQUE NOT NULL -- I18N
638 CREATE TABLE config.org_unit_setting_type (
639 name TEXT PRIMARY KEY,
640 label TEXT UNIQUE NOT NULL,
641 grp TEXT REFERENCES config.settings_group (name),
643 datatype TEXT NOT NULL DEFAULT 'string',
648 -- define valid datatypes
650 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
651 ( 'bool', 'integer', 'float', 'currency', 'interval',
652 'date', 'string', 'object', 'array', 'link' ) ),
654 -- fm_class is meaningful only for 'link' datatype
656 CONSTRAINT coust_no_empty_link CHECK
657 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
658 ( datatype <> 'link' AND fm_class IS NULL ) )
661 CREATE TABLE config.usr_setting_type (
663 name TEXT PRIMARY KEY,
664 opac_visible BOOL NOT NULL DEFAULT FALSE,
665 label TEXT UNIQUE NOT NULL,
667 grp TEXT REFERENCES config.settings_group (name),
668 datatype TEXT NOT NULL DEFAULT 'string',
673 -- define valid datatypes
675 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
676 ( 'bool', 'integer', 'float', 'currency', 'interval',
677 'date', 'string', 'object', 'array', 'link' ) ),
680 -- fm_class is meaningful only for 'link' datatype
682 CONSTRAINT coust_no_empty_link CHECK
683 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
684 ( datatype <> 'link' AND fm_class IS NULL ) )
688 CREATE TABLE config.workstation_setting_type (
689 name TEXT PRIMARY KEY,
690 label TEXT UNIQUE NOT NULL,
691 grp TEXT REFERENCES config.settings_group (name),
693 datatype TEXT NOT NULL DEFAULT 'string',
696 -- define valid datatypes
698 CONSTRAINT cwst_valid_datatype CHECK ( datatype IN
699 ( 'bool', 'integer', 'float', 'currency', 'interval',
700 'date', 'string', 'object', 'array', 'link' ) ),
702 -- fm_class is meaningful only for 'link' datatype
704 CONSTRAINT cwst_no_empty_link CHECK
705 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
706 ( datatype <> 'link' AND fm_class IS NULL ) )
709 -- Prevent setting types from being both user and workstation settings.
710 CREATE OR REPLACE FUNCTION config.setting_is_user_or_ws()
711 RETURNS TRIGGER AS $FUNC$
714 IF TG_TABLE_NAME = 'usr_setting_type' THEN
715 PERFORM TRUE FROM config.workstation_setting_type cwst
716 WHERE cwst.name = NEW.name;
722 IF TG_TABLE_NAME = 'workstation_setting_type' THEN
723 PERFORM TRUE FROM config.usr_setting_type cust
724 WHERE cust.name = NEW.name;
731 '% Cannot be used as both a user setting and a workstation setting.',
734 $FUNC$ LANGUAGE PLPGSQL STABLE;
736 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
737 AFTER INSERT OR UPDATE ON config.usr_setting_type
738 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
740 CREATE CONSTRAINT TRIGGER check_setting_is_usr_or_ws
741 AFTER INSERT OR UPDATE ON config.workstation_setting_type
742 FOR EACH ROW EXECUTE PROCEDURE config.setting_is_user_or_ws();
746 -- Some handy functions, based on existing ones, to provide optional ingest normalization
748 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
749 SELECT SUBSTRING($1,$2);
750 $func$ LANGUAGE SQL STRICT IMMUTABLE;
752 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
753 SELECT SUBSTRING($1,1,$2);
754 $func$ LANGUAGE SQL STRICT IMMUTABLE;
756 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
757 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
758 $func$ LANGUAGE SQL STRICT IMMUTABLE;
760 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
761 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
762 $func$ LANGUAGE SQL STRICT IMMUTABLE;
764 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
765 SELECT approximate_date( $1, '0');
766 $func$ LANGUAGE SQL STRICT IMMUTABLE;
768 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
769 SELECT approximate_date( $1, '9');
770 $func$ LANGUAGE SQL STRICT IMMUTABLE;
772 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
773 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
774 $func$ LANGUAGE SQL STRICT IMMUTABLE;
776 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
777 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
778 $func$ LANGUAGE SQL STRICT IMMUTABLE;
780 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
785 # Find the first ISBN, force it to ISBN13 and return it
789 foreach my $word (split(/\s/, $input)) {
790 my $isbn = Business::ISBN->new($word);
792 # First check the checksum; if it is not valid, fix it and add the original
793 # bad-checksum ISBN to the output
794 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
795 $isbn->fix_checksum();
798 # If we now have a valid ISBN, force it to ISBN13 and return it
799 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
802 $func$ LANGUAGE PLPERLU;
804 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
805 Inspired by translate_isbn1013
807 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
808 version without hypens and with a repaired checksum if the checksum was bad
812 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
817 # For each ISBN found in a single string containing a set of ISBNs:
818 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
819 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
824 foreach my $word (split(/\s/, $input)) {
825 my $isbn = Business::ISBN->new($word);
827 # First check the checksum; if it is not valid, fix it and add the original
828 # bad-checksum ISBN to the output
829 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
830 $output .= $isbn->isbn() . " ";
831 $isbn->fix_checksum();
834 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
835 # and add the normalized original ISBN to the output
836 if ($isbn && $isbn->is_valid()) {
837 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
838 $output .= $isbn->isbn . " ";
840 # If we successfully converted the ISBN to its counterpart, add the
841 # converted ISBN to the output as well
842 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
845 return $output if $output;
847 # If there were no valid ISBNs, just return the raw input
849 $func$ LANGUAGE PLPERLU;
851 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
852 The translate_isbn1013 function takes an input ISBN and returns the
853 following in a single space-delimited string if the input ISBN is valid:
854 - The normalized input ISBN (hyphens stripped)
855 - The normalized input ISBN with a fixed checksum if the checksum was bad
856 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
859 -- And ... a table in which to register them
861 CREATE TABLE config.index_normalizer (
862 id SERIAL PRIMARY KEY,
863 name TEXT UNIQUE NOT NULL,
866 param_count INT NOT NULL DEFAULT 0
869 CREATE TABLE config.metabib_field_index_norm_map (
870 id SERIAL PRIMARY KEY,
871 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
872 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
874 pos INT NOT NULL DEFAULT 0
877 CREATE TABLE config.record_attr_definition (
878 name TEXT PRIMARY KEY,
879 label TEXT NOT NULL, -- I18N
881 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
882 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
883 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
884 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
886 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
887 tag TEXT, -- LIKE format
888 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
890 -- This is used for both tag/sf and xpath entries
893 -- For xpath-extracted attrs
895 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
900 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
902 -- For phys-char fields
903 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
905 -- Source of vocabulary terms for this record attribute;
906 -- typically will be a URI referring to a SKOS vocabulary
910 CREATE TABLE config.record_attr_index_norm_map (
911 id SERIAL PRIMARY KEY,
912 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
913 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
915 pos INT NOT NULL DEFAULT 0
918 CREATE TABLE config.coded_value_map (
919 id SERIAL PRIMARY KEY,
920 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
924 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
926 is_simple BOOL NOT NULL DEFAULT FALSE,
927 concept_uri TEXT -- URI expressing the SKOS concept that the
928 -- coded value represents
931 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
933 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
934 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
935 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
936 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
937 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
938 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
939 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
941 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$
943 current_row config.coded_value_map%ROWTYPE;
945 -- Look for a current value
946 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
948 IF FOUND AND NOT add_only THEN
949 -- Update anything we were handed
950 current_row.value := COALESCE(current_row.value, in_value);
951 current_row.description := COALESCE(current_row.description, in_description);
952 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
953 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
954 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
955 UPDATE config.coded_value_map
957 value = current_row.value,
958 description = current_row.description,
959 opac_visible = current_row.opac_visible,
960 search_label = current_row.search_label,
961 is_simple = current_row.is_simple
962 WHERE id = current_row.id;
964 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
965 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
968 $f$ LANGUAGE PLPGSQL;
970 CREATE TABLE config.composite_attr_entry_definition(
971 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
972 definition TEXT NOT NULL -- JSON
975 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
976 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
977 SELECT DISTINCT l.version
978 FROM config.upgrade_log l
979 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
980 WHERE d.db_patch = $1
983 -- List applied db patches that are superseded by (and block the application of) my_db_patch
984 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
985 SELECT DISTINCT l.version
986 FROM config.upgrade_log l
987 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
988 WHERE d.db_patch = $1
991 -- List applied db patches that deprecates (and block the application of) my_db_patch
992 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
994 FROM config.db_patch_dependencies
995 WHERE ARRAY[$1]::TEXT[] && deprecates
998 -- List applied db patches that supersedes (and block the application of) my_db_patch
999 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
1001 FROM config.db_patch_dependencies
1002 WHERE ARRAY[$1]::TEXT[] && supersedes
1005 -- Make sure that no deprecated or superseded db patches are currently applied
1006 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
1008 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
1010 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
1012 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
1014 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
1017 -- Raise an exception if there are, in fact, dep/sup conflict
1018 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
1023 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
1024 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
1025 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
1027 Upgrade script % can not be applied:
1028 applied deprecated scripts %
1029 applied superseded scripts %
1033 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
1034 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
1035 evergreen.upgrade_list_applied_deprecated(my_db_patch),
1036 evergreen.upgrade_list_applied_superseded(my_db_patch);
1039 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
1042 $$ LANGUAGE PLPGSQL;
1044 CREATE TABLE config.barcode_completion (
1045 id SERIAL PRIMARY KEY,
1046 active BOOL NOT NULL DEFAULT true,
1047 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
1050 length INT NOT NULL DEFAULT 0,
1052 padding_end BOOL NOT NULL DEFAULT false,
1053 asset BOOL NOT NULL DEFAULT true,
1054 actor BOOL NOT NULL DEFAULT true
1057 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
1059 -- Add support for logging, only keep the most recent five rows for each category.
1062 CREATE TABLE config.org_unit_setting_type_log (
1063 id BIGSERIAL PRIMARY KEY,
1064 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1065 org INT, --REFERENCES actor.org_unit (id),
1066 original_value TEXT,
1068 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
1071 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
1072 Org Unit setting Logs
1074 This table contains the most recent changes to each setting
1075 in actor.org_unit_setting, allowing for mistakes to be undone.
1076 This is NOT meant to be an auditor, but rather an undo/redo.
1079 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
1081 -- Only keeps the most recent five settings changes.
1082 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
1083 (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);
1085 IF (TG_OP = 'UPDATE') THEN
1087 ELSIF (TG_OP = 'INSERT') THEN
1092 $oustl_limit$ LANGUAGE plpgsql;
1094 CREATE TRIGGER limit_logs_oust
1095 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
1096 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
1098 CREATE TABLE config.sms_carrier (
1099 id SERIAL PRIMARY KEY,
1103 active BOOLEAN DEFAULT TRUE
1106 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1108 CREATE TABLE config.usr_activity_type (
1109 id SERIAL PRIMARY KEY,
1113 label TEXT NOT NULL, -- i18n
1114 egroup config.usr_activity_group NOT NULL,
1115 enabled BOOL NOT NULL DEFAULT TRUE,
1116 transient BOOL NOT NULL DEFAULT TRUE,
1117 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1120 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1121 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1123 CREATE TABLE config.filter_dialog_interface (
1124 key TEXT PRIMARY KEY,
1128 CREATE TABLE config.filter_dialog_filter_set (
1129 id SERIAL PRIMARY KEY,
1131 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1132 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1133 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1134 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1135 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1136 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1139 CREATE TABLE config.best_hold_order(
1140 id SERIAL PRIMARY KEY,
1141 name TEXT UNIQUE, -- i18n
1142 pprox INT, -- copy capture <-> pickup lib prox
1143 hprox INT, -- copy circ lib <-> request lib prox
1144 owning_lib_to_home_lib_prox INT, -- copy owning lib <-> user home lib prox
1145 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1146 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1147 priority INT, -- group hold priority
1148 cut INT, -- cut-in-line
1149 depth INT, -- selection depth
1150 htime INT, -- time since last home-lib circ exceeds org-unit setting
1151 rtime INT, -- request time
1152 shtime INT -- time since copy last trip home exceeds org-unit setting
1155 -- At least one of these columns must contain a non-null value
1156 ALTER TABLE config.best_hold_order ADD CHECK ((
1157 pprox IS NOT NULL OR
1158 hprox IS NOT NULL OR
1159 owning_lib_to_home_lib_prox IS NOT NULL OR
1160 aprox IS NOT NULL OR
1161 priority IS NOT NULL OR
1163 depth IS NOT NULL OR
1164 htime IS NOT NULL OR
1168 CREATE OR REPLACE FUNCTION
1169 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1172 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1178 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1181 $func$ LANGUAGE PLPGSQL STABLE;
1183 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1184 Used by a config.z3950_index_field_map constraint trigger
1185 to verify z3950_attr_type maps.
1188 -- drop these in down here since they reference config.metabib_field
1189 -- and config.record_attr_definition
1190 CREATE TABLE config.z3950_index_field_map (
1191 id SERIAL PRIMARY KEY,
1192 label TEXT NOT NULL, -- i18n
1193 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1194 record_attr TEXT REFERENCES config.record_attr_definition(name),
1195 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1196 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1197 CONSTRAINT metabib_field_or_record_attr CHECK (
1198 metabib_field IS NOT NULL OR
1199 record_attr IS NOT NULL
1201 CONSTRAINT attr_or_attr_type CHECK (
1202 z3950_attr IS NOT NULL OR
1203 z3950_attr_type IS NOT NULL
1207 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1208 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1209 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1211 CREATE TABLE config.marc_format (
1212 id SERIAL PRIMARY KEY,
1216 COMMENT ON TABLE config.marc_format IS $$
1217 List of MARC formats supported by this Evergreen
1218 database. This exists primarily as a hook for future
1219 support of UNIMARC, though whether that will ever
1220 happen remains to be seen.
1223 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1225 CREATE TABLE config.marc_field (
1226 id SERIAL PRIMARY KEY,
1227 marc_format INTEGER NOT NULL
1228 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1229 marc_record_type config.marc_record_type NOT NULL,
1230 tag CHAR(3) NOT NULL,
1233 fixed_field BOOLEAN,
1237 owner INTEGER -- REFERENCES actor.org_unit (id)
1238 -- if the owner is null, the data about the field is
1239 -- assumed to come from the controlling MARC standard
1242 COMMENT ON TABLE config.marc_field IS $$
1243 This table stores a list of MARC fields recognized by the Evergreen
1244 instance. Note that we're not aiming for completely generic ISO2709
1245 support: we're assuming things like three characters for a tag,
1246 one-character subfield labels, two indicators per variable data field,
1247 and the like, all of which are technically specializations of ISO2709.
1249 Of particular significance is the owner column; if it's set to a null
1250 value, the field definition is assumed to come from a national
1251 standards body; if it's set to a non-null value, the field definition
1252 is an OU-level addition to or override of the standard.
1255 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1256 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1258 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1259 ON config.marc_field(marc_format, marc_record_type, tag)
1260 WHERE owner IS NULL;
1261 ALTER TABLE config.marc_field
1262 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1263 CHECK ((owner IS NOT NULL) OR
1266 repeatable IS NOT NULL AND
1267 mandatory IS NOT NULL AND
1272 CREATE TABLE config.marc_subfield (
1273 id SERIAL PRIMARY KEY,
1274 marc_format INTEGER NOT NULL
1275 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1276 marc_record_type config.marc_record_type NOT NULL,
1277 tag CHAR(3) NOT NULL,
1278 code CHAR(1) NOT NULL,
1284 REFERENCES config.record_attr_definition (name)
1285 DEFERRABLE INITIALLY DEFERRED,
1286 owner INTEGER -- REFERENCES actor.org_unit (id)
1287 -- if the owner is null, the data about the subfield is
1288 -- assumed to come from the controlling MARC standard
1291 COMMENT ON TABLE config.marc_subfield IS $$
1292 This table stores the list of subfields recognized by this Evergreen
1293 instance. As with config.marc_field, of particular significance is the
1294 owner column; if it's set to a null value, the subfield definition is
1295 assumed to come from a national standards body; if it's set to a non-null
1296 value, the subfield definition is an OU-level addition to or override
1300 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1301 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1302 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1303 WHERE owner IS NULL;
1304 ALTER TABLE config.marc_subfield
1305 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1306 CHECK ((owner IS NOT NULL) OR
1309 repeatable IS NOT NULL AND
1310 mandatory IS NOT NULL AND
1315 CREATE TABLE config.copy_tag_type (
1316 code TEXT NOT NULL PRIMARY KEY,
1317 label TEXT NOT NULL,
1318 owner INTEGER NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED
1321 CREATE INDEX config_copy_tag_type_owner_idx
1322 ON config.copy_tag_type (owner);
1324 CREATE TABLE config.hold_type (
1326 hold_type TEXT UNIQUE,
1330 INSERT INTO config.hold_type (hold_type,description) VALUES
1332 ('V','Volume Hold'),
1334 ('M','Metarecord Hold'),
1335 ('R','Recall Hold'),
1337 ('I','Issuance Hold'),
1341 CREATE TABLE config.print_template (
1342 id SERIAL PRIMARY KEY,
1344 label TEXT NOT NULL, -- i18n
1345 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
1346 active BOOLEAN NOT NULL DEFAULT FALSE,
1347 locale TEXT REFERENCES config.i18n_locale(code)
1348 ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1349 content_type TEXT NOT NULL DEFAULT 'text/html',
1350 template TEXT NOT NULL,
1351 CONSTRAINT name_once_per_lib UNIQUE (owner, name),
1352 CONSTRAINT label_once_per_lib UNIQUE (owner, label)
1355 CREATE TABLE config.carousel_type (
1356 id SERIAL PRIMARY KEY,
1358 automatic BOOLEAN NOT NULL DEFAULT TRUE,
1359 filter_by_age BOOLEAN NOT NULL DEFAULT FALSE,
1360 filter_by_copy_owning_lib BOOLEAN NOT NULL DEFAULT FALSE,
1361 filter_by_copy_location BOOLEAN NOT NULL DEFAULT FALSE
1364 INSERT INTO config.carousel_type
1365 (id, name, automatic, filter_by_age, filter_by_copy_owning_lib, filter_by_copy_location)
1367 (1, 'Manual', FALSE, FALSE, FALSE, FALSE),
1368 (2, 'Newly Catalogued Items', TRUE, TRUE, TRUE, TRUE),
1369 (3, 'Recently Returned Items', TRUE, TRUE, TRUE, TRUE),
1370 (4, 'Top Circulated Items', TRUE, TRUE, TRUE, FALSE),
1371 (5, 'Newest Items By Shelving Location', TRUE, TRUE, TRUE, FALSE)
1374 SELECT SETVAL('config.carousel_type_id_seq'::TEXT, 100);
1376 CREATE TABLE config.geolocation_service (
1377 id SERIAL PRIMARY KEY,
1379 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)