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.disable_metabib_field_entry');
48 INSERT INTO config.internal_flag (name) VALUES ('ingest.assume_inserts_only');
49 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_browse_indexing');
50 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_search_indexing');
51 INSERT INTO config.internal_flag (name) VALUES ('ingest.skip_facet_indexing');
52 INSERT INTO config.internal_flag (name) VALUES ('serial.rematerialize_on_same_holding_code');
53 INSERT INTO config.internal_flag (name) VALUES ('ingest.metarecord_mapping.preserve_on_delete');
55 CREATE TABLE config.global_flag (
57 ) INHERITS (config.internal_flag);
58 ALTER TABLE config.global_flag ADD PRIMARY KEY (name);
60 CREATE TABLE config.upgrade_log (
61 version TEXT PRIMARY KEY,
62 install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
66 CREATE TABLE config.db_patch_dependencies (
67 db_patch TEXT PRIMARY KEY,
72 CREATE OR REPLACE FUNCTION evergreen.array_overlap_check (/* field */) RETURNS TRIGGER AS $$
78 EXECUTE 'SELECT COUNT(*) FROM '|| TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME ||' WHERE '|| fld ||' && ($1).'|| fld INTO cnt USING NEW;
80 RAISE EXCEPTION 'Cannot insert duplicate array into field % of table %', fld, TG_TABLE_SCHEMA ||'.'|| TG_TABLE_NAME;
86 CREATE TRIGGER no_overlapping_sups
87 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
88 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('supersedes');
90 CREATE TRIGGER no_overlapping_deps
91 BEFORE INSERT OR UPDATE ON config.db_patch_dependencies
92 FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates');
94 INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0991', :eg_version); -- dbwells/miker
96 CREATE TABLE config.bib_source (
97 id SERIAL PRIMARY KEY,
98 quality INT CHECK ( quality BETWEEN 0 AND 100 ),
99 source TEXT NOT NULL UNIQUE,
100 transcendant BOOL NOT NULL DEFAULT FALSE,
101 can_have_copies BOOL NOT NULL DEFAULT TRUE
103 COMMENT ON TABLE config.bib_source IS $$
104 This is table is used to set up the relative "quality" of each
105 MARC source, such as OCLC. Also identifies "transcendant" sources,
106 i.e., sources of bib records that should display in the OPAC
107 even if no copies or located URIs are attached. Also indicates if
108 the source is allowed to have actual copies on its bibs. Volumes
109 for targeted URIs are unaffected by this setting.
112 CREATE TABLE config.standing (
113 id SERIAL PRIMARY KEY,
114 value TEXT NOT NULL UNIQUE
116 COMMENT ON TABLE config.standing IS $$
119 This table contains the values that can be applied to a patron
120 by a staff member. These values should not be changed, other
121 than for translation, as the ID column is currently a "magic
122 number" in the source. :(
125 CREATE TABLE config.standing_penalty (
126 id SERIAL PRIMARY KEY,
127 name TEXT NOT NULL UNIQUE,
130 staff_alert BOOL NOT NULL DEFAULT FALSE,
132 ignore_proximity INTEGER
135 CREATE TABLE config.xml_transform (
136 name TEXT PRIMARY KEY,
137 namespace_uri TEXT NOT NULL,
138 prefix TEXT NOT NULL,
142 CREATE TABLE config.biblio_fingerprint (
143 id SERIAL PRIMARY KEY,
146 first_word BOOL NOT NULL DEFAULT FALSE,
147 format TEXT NOT NULL DEFAULT 'marcxml'
150 INSERT INTO config.biblio_fingerprint (name, xpath, format)
153 '//marc:datafield[@tag="700"]/marc:subfield[@code="t"]|' ||
154 '//marc:datafield[@tag="240"]/marc:subfield[@code="a"]|' ||
155 '//marc:datafield[@tag="242"]/marc:subfield[@code="a"]|' ||
156 '//marc:datafield[@tag="246"]/marc:subfield[@code="a"]|' ||
157 '//marc:datafield[@tag="245"]/marc:subfield[@code="a"]',
161 INSERT INTO config.biblio_fingerprint (name, xpath, format, first_word)
164 '//marc:datafield[@tag="700" and ./*[@code="t"]]/marc:subfield[@code="a"]|'
165 '//marc:datafield[@tag="100"]/marc:subfield[@code="a"]|'
166 '//marc:datafield[@tag="110"]/marc:subfield[@code="a"]|'
167 '//marc:datafield[@tag="111"]/marc:subfield[@code="a"]|'
168 '//marc:datafield[@tag="260"]/marc:subfield[@code="b"]',
173 CREATE TABLE config.metabib_class (
174 name TEXT PRIMARY KEY,
175 label TEXT NOT NULL UNIQUE,
176 buoyant BOOL DEFAULT FALSE NOT NULL,
177 restrict BOOL DEFAULT FALSE NOT NULL,
178 combined BOOL DEFAULT FALSE NOT NULL,
179 a_weight NUMERIC DEFAULT 1.0 NOT NULL,
180 b_weight NUMERIC DEFAULT 0.4 NOT NULL,
181 c_weight NUMERIC DEFAULT 0.2 NOT NULL,
182 d_weight NUMERIC DEFAULT 0.1 NOT NULL
185 CREATE TABLE config.metabib_field (
186 id SERIAL PRIMARY KEY,
187 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
191 weight INT NOT NULL DEFAULT 1,
192 format TEXT NOT NULL REFERENCES config.xml_transform (name) DEFAULT 'mods33',
193 search_field BOOL NOT NULL DEFAULT TRUE,
194 facet_field BOOL NOT NULL DEFAULT FALSE,
195 browse_field BOOL NOT NULL DEFAULT TRUE,
197 browse_sort_xpath TEXT,
199 authority_xpath TEXT,
201 restrict BOOL DEFAULT FALSE NOT NULL
203 COMMENT ON TABLE config.metabib_field IS $$
204 XPath used for record indexing ingest
206 This table contains the XPath used to chop up MODS into its
207 indexable parts. Each XPath entry is named and assigned to
208 a "class" of either title, subject, author, keyword, series
212 CREATE UNIQUE INDEX config_metabib_field_class_name_idx ON config.metabib_field (field_class, name);
214 CREATE TABLE config.ts_config_list (
218 COMMENT ON TABLE config.ts_config_list IS $$
221 A list of full text configs with names and descriptions.
224 CREATE TABLE config.metabib_class_ts_map (
225 id SERIAL PRIMARY KEY,
226 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
227 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
228 active BOOL NOT NULL DEFAULT TRUE,
229 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
230 index_lang TEXT NULL,
231 search_lang TEXT NULL,
232 always BOOL NOT NULL DEFAULT true
234 COMMENT ON TABLE config.metabib_class_ts_map IS $$
235 Text Search Configs for metabib class indexing
237 This table contains text search config definitions for
238 storing index_vector values.
241 CREATE TABLE config.metabib_field_ts_map (
242 id SERIAL PRIMARY KEY,
243 metabib_field INT NOT NULL REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
244 ts_config TEXT NOT NULL REFERENCES config.ts_config_list (id),
245 active BOOL NOT NULL DEFAULT TRUE,
246 index_weight CHAR(1) NOT NULL DEFAULT 'C' CHECK (index_weight IN ('A','B','C','D')),
247 index_lang TEXT NULL,
248 search_lang TEXT NULL
250 COMMENT ON TABLE config.metabib_field_ts_map IS $$
251 Text Search Configs for metabib field indexing
253 This table contains text search config definitions for
254 storing index_vector values.
257 CREATE TABLE config.metabib_search_alias (
258 alias TEXT PRIMARY KEY,
259 field_class TEXT NOT NULL REFERENCES config.metabib_class (name),
260 field INT REFERENCES config.metabib_field (id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED
263 CREATE TABLE config.non_cataloged_type (
264 id SERIAL PRIMARY KEY,
265 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id),
267 circ_duration INTERVAL NOT NULL DEFAULT '14 days'::INTERVAL,
268 in_house BOOL NOT NULL DEFAULT FALSE,
269 CONSTRAINT noncat_once_per_lib UNIQUE (owning_lib,name)
271 COMMENT ON TABLE config.non_cataloged_type IS $$
272 Types of valid non-cataloged items.
275 CREATE TABLE config.identification_type (
276 id SERIAL PRIMARY KEY,
277 name TEXT NOT NULL UNIQUE
279 COMMENT ON TABLE config.identification_type IS $$
280 Types of valid patron identification.
282 Each patron must display at least one valid form of identification
283 in order to get a library card. This table lists those forms.
286 CREATE TABLE config.rule_circ_duration (
287 id SERIAL PRIMARY KEY,
288 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
289 extended INTERVAL NOT NULL,
290 normal INTERVAL NOT NULL,
291 shrt INTERVAL NOT NULL,
292 max_renewals INT NOT NULL
294 COMMENT ON TABLE config.rule_circ_duration IS $$
295 Circulation Duration rules
297 Each circulation is given a duration based on one of these rules.
300 CREATE TABLE config.hard_due_date (
301 id SERIAL PRIMARY KEY,
302 name TEXT NOT NULL UNIQUE,
303 ceiling_date TIMESTAMPTZ NOT NULL,
304 forceto BOOL NOT NULL,
305 owner INT NOT NULL -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
308 CREATE TABLE config.hard_due_date_values (
309 id SERIAL PRIMARY KEY,
310 hard_due_date INT NOT NULL REFERENCES config.hard_due_date (id)
311 DEFERRABLE INITIALLY DEFERRED,
312 ceiling_date TIMESTAMPTZ NOT NULL,
313 active_date TIMESTAMPTZ NOT NULL
316 CREATE OR REPLACE FUNCTION config.update_hard_due_dates () RETURNS INT AS $func$
318 temp_value config.hard_due_date_values%ROWTYPE;
322 SELECT DISTINCT ON (hard_due_date) *
323 FROM config.hard_due_date_values
324 WHERE active_date <= NOW() -- We've passed (or are at) the rollover time
325 ORDER BY hard_due_date, active_date DESC -- Latest (nearest to us) active time
327 UPDATE config.hard_due_date
328 SET ceiling_date = temp_value.ceiling_date
329 WHERE id = temp_value.hard_due_date
330 AND ceiling_date <> temp_value.ceiling_date; -- Time is equal if we've already updated the chdd
333 updated := updated + 1;
339 $func$ LANGUAGE plpgsql;
341 CREATE TABLE config.rule_max_fine (
342 id SERIAL PRIMARY KEY,
343 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
344 amount NUMERIC(6,2) NOT NULL,
345 is_percent BOOL NOT NULL DEFAULT FALSE
347 COMMENT ON TABLE config.rule_max_fine IS $$
348 Circulation Max Fine rules
350 Each circulation is given a maximum fine based on one of
354 CREATE TABLE config.rule_recurring_fine (
355 id SERIAL PRIMARY KEY,
356 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
357 high NUMERIC(6,2) NOT NULL,
358 normal NUMERIC(6,2) NOT NULL,
359 low NUMERIC(6,2) NOT NULL,
360 recurrence_interval INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL,
361 grace_period INTERVAL NOT NULL DEFAULT '1 day'::INTERVAL
363 COMMENT ON TABLE config.rule_recurring_fine IS $$
364 Circulation Recurring Fine rules
366 Each circulation is given a recurring fine amount based on one of
367 these rules. Note that it is recommended to run the fine generator
368 (from cron) at least as frequently as the lowest recurrence interval
369 used by your circulation rules so that accrued fines will be up
374 CREATE TABLE config.rule_age_hold_protect (
375 id SERIAL PRIMARY KEY,
376 name TEXT NOT NULL UNIQUE CHECK ( name ~ E'^\\w+$' ),
377 age INTERVAL NOT NULL,
380 COMMENT ON TABLE config.rule_age_hold_protect IS $$
381 Hold Item Age Protection rules
383 A hold request can only capture new(ish) items when they are
384 within a particular proximity of the pickup_lib of the request.
385 The proximity ('prox' column) is calculated by counting
386 the number of tree edges between the pickup_lib and either the
387 owning_lib or circ_lib of the copy that could fulfill the hold,
388 as determined by the distance_is_from_owner value of the hold matrix
389 rule controlling the hold request.
392 CREATE TABLE config.copy_status (
393 id SERIAL PRIMARY KEY,
394 name TEXT NOT NULL UNIQUE,
395 holdable BOOL NOT NULL DEFAULT FALSE,
396 opac_visible BOOL NOT NULL DEFAULT FALSE,
397 copy_active BOOL NOT NULL DEFAULT FALSE,
398 restrict_copy_delete BOOL NOT NULL DEFAULT FALSE,
399 is_available BOOL NOT NULL DEFAULT FALSE
401 COMMENT ON TABLE config.copy_status IS $$
404 The available copy statuses, and whether a copy in that
405 status is available for hold request capture. 0 (zero) is
406 the only special number in this set, meaning that the item
407 is available for immediate checkout, and is counted as available
410 Statuses with an ID below 100 are not removable, and have special
411 meaning in the code. Do not change them except to translate the
414 You may add and remove statuses above 100, and these can be used
415 to remove items from normal circulation without affecting the rest
416 of the copy's values or its location.
419 CREATE TABLE config.net_access_level (
420 id SERIAL PRIMARY KEY,
421 name TEXT NOT NULL UNIQUE
423 COMMENT ON TABLE config.net_access_level IS $$
424 Patron Network Access level
426 This will be used to inform the in-library firewall of how much
427 internet access the using patron should be allowed.
431 CREATE TABLE config.remote_account (
432 id SERIAL PRIMARY KEY,
434 host TEXT NOT NULL, -- name or IP, :port optional
435 username TEXT, -- optional, since we could default to $USER
436 password TEXT, -- optional, since we could use SSH keys, or anonymous login.
437 account TEXT, -- aka profile or FTP "account" command
438 path TEXT, -- aka directory
439 owner INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
440 last_activity TIMESTAMP WITH TIME ZONE
443 CREATE TABLE config.marc21_rec_type_map (
444 code TEXT PRIMARY KEY,
445 type_val TEXT NOT NULL,
446 blvl_val TEXT NOT NULL
449 CREATE TABLE config.marc21_ff_pos_map (
450 id SERIAL PRIMARY KEY,
451 fixed_field TEXT NOT NULL,
453 rec_type TEXT NOT NULL,
454 start_pos INT NOT NULL,
456 default_val TEXT NOT NULL DEFAULT ' '
459 CREATE TABLE config.marc21_physical_characteristic_type_map (
460 ptype_key TEXT PRIMARY KEY,
461 label TEXT NOT NULL -- I18N
464 CREATE TABLE config.marc21_physical_characteristic_subfield_map (
465 id SERIAL PRIMARY KEY,
466 ptype_key TEXT NOT NULL REFERENCES config.marc21_physical_characteristic_type_map (ptype_key) ON DELETE CASCADE ON UPDATE CASCADE,
467 subfield TEXT NOT NULL,
468 start_pos INT NOT NULL,
470 label TEXT NOT NULL -- I18N
473 CREATE TABLE config.marc21_physical_characteristic_value_map (
474 id SERIAL PRIMARY KEY,
476 ptype_subfield INT NOT NULL REFERENCES config.marc21_physical_characteristic_subfield_map (id),
477 label TEXT NOT NULL -- I18N
481 CREATE TABLE config.z3950_source (
482 name TEXT PRIMARY KEY,
483 label TEXT NOT NULL UNIQUE,
487 record_format TEXT NOT NULL DEFAULT 'FI',
488 transmission_format TEXT NOT NULL DEFAULT 'usmarc',
489 auth BOOL NOT NULL DEFAULT TRUE,
490 use_perm INT -- REFERENCES permission.perm_list (id)
493 COMMENT ON TABLE config.z3950_source IS $$
496 Each row in this table represents a database searchable via Z39.50.
499 COMMENT ON COLUMN config.z3950_source.record_format IS $$
503 COMMENT ON COLUMN config.z3950_source.transmission_format IS $$
504 Z39.50 preferred record syntax..
507 COMMENT ON COLUMN config.z3950_source.use_perm IS $$
508 If set, this permission is required for the source to be listed in the staff
509 client Z39.50 interface. Similar to permission.grp_tree.application_perm.
512 CREATE TABLE config.z3950_attr (
513 id SERIAL PRIMARY KEY,
514 source TEXT NOT NULL REFERENCES config.z3950_source (name) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
519 truncation INT NOT NULL DEFAULT 0,
520 CONSTRAINT z_code_format_once_per_source UNIQUE (code,format,source)
523 CREATE TABLE config.z3950_source_credentials (
524 id SERIAL PRIMARY KEY,
525 owner INTEGER NOT NULL, -- REFERENCES actor.org_unit(id),
526 source TEXT NOT NULL REFERENCES config.z3950_source(name) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
529 CONSTRAINT czsc_source_once_per_lib UNIQUE (source, owner)
532 CREATE TABLE config.i18n_locale (
533 code TEXT PRIMARY KEY,
534 marc_code TEXT NOT NULL, -- should exist in config.coded_value_map WHERE ctype = 'item_lang'
535 name TEXT UNIQUE NOT NULL,
539 CREATE TABLE config.i18n_core (
540 id BIGSERIAL PRIMARY KEY,
541 fq_field TEXT NOT NULL,
542 identity_value TEXT NOT NULL,
543 translation TEXT NOT NULL REFERENCES config.i18n_locale (code) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
547 CREATE UNIQUE INDEX i18n_identity ON config.i18n_core (fq_field,identity_value,translation);
549 CREATE OR REPLACE FUNCTION oils_i18n_update_apply(old_ident TEXT, new_ident TEXT, hint TEXT) RETURNS VOID AS $_$
553 UPDATE config.i18n_core
554 SET identity_value = $$ || quote_literal(new_ident) || $$
555 WHERE fq_field LIKE '$$ || hint || $$.%'
556 AND identity_value = $$ || quote_literal(old_ident) || $$::TEXT;$$;
561 $_$ LANGUAGE PLPGSQL;
563 CREATE OR REPLACE FUNCTION oils_i18n_id_tracking(/* hint */) RETURNS TRIGGER AS $_$
565 PERFORM oils_i18n_update_apply( OLD.id::TEXT, NEW.id::TEXT, TG_ARGV[0]::TEXT );
568 $_$ LANGUAGE PLPGSQL;
570 CREATE OR REPLACE FUNCTION oils_i18n_code_tracking(/* hint */) RETURNS TRIGGER AS $_$
572 PERFORM oils_i18n_update_apply( OLD.code::TEXT, NEW.code::TEXT, TG_ARGV[0]::TEXT );
575 $_$ LANGUAGE PLPGSQL;
577 CREATE TABLE config.billing_type (
578 id SERIAL PRIMARY KEY,
580 owner INT NOT NULL, -- REFERENCES actor.org_unit (id)
581 default_price NUMERIC(6,2),
582 CONSTRAINT billing_type_once_per_lib UNIQUE (name, owner)
585 CREATE TABLE config.settings_group (
586 name TEXT PRIMARY KEY,
587 label TEXT UNIQUE NOT NULL -- I18N
590 CREATE TABLE config.org_unit_setting_type (
591 name TEXT PRIMARY KEY,
592 label TEXT UNIQUE NOT NULL,
593 grp TEXT REFERENCES config.settings_group (name),
595 datatype TEXT NOT NULL DEFAULT 'string',
600 -- define valid datatypes
602 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
603 ( 'bool', 'integer', 'float', 'currency', 'interval',
604 'date', 'string', 'object', 'array', 'link' ) ),
606 -- fm_class is meaningful only for 'link' datatype
608 CONSTRAINT coust_no_empty_link CHECK
609 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
610 ( datatype <> 'link' AND fm_class IS NULL ) )
613 CREATE TABLE config.usr_setting_type (
615 name TEXT PRIMARY KEY,
616 opac_visible BOOL NOT NULL DEFAULT FALSE,
617 label TEXT UNIQUE NOT NULL,
619 grp TEXT REFERENCES config.settings_group (name),
620 datatype TEXT NOT NULL DEFAULT 'string',
625 -- define valid datatypes
627 CONSTRAINT coust_valid_datatype CHECK ( datatype IN
628 ( 'bool', 'integer', 'float', 'currency', 'interval',
629 'date', 'string', 'object', 'array', 'link' ) ),
632 -- fm_class is meaningful only for 'link' datatype
634 CONSTRAINT coust_no_empty_link CHECK
635 ( ( datatype = 'link' AND fm_class IS NOT NULL ) OR
636 ( datatype <> 'link' AND fm_class IS NULL ) )
640 -- Some handy functions, based on existing ones, to provide optional ingest normalization
642 CREATE OR REPLACE FUNCTION public.left_trunc( TEXT, INT ) RETURNS TEXT AS $func$
643 SELECT SUBSTRING($1,$2);
644 $func$ LANGUAGE SQL STRICT IMMUTABLE;
646 CREATE OR REPLACE FUNCTION public.right_trunc( TEXT, INT ) RETURNS TEXT AS $func$
647 SELECT SUBSTRING($1,1,$2);
648 $func$ LANGUAGE SQL STRICT IMMUTABLE;
650 CREATE OR REPLACE FUNCTION public.split_date_range( TEXT ) RETURNS TEXT AS $func$
651 SELECT REGEXP_REPLACE( $1, E'(\\d{4})-(\\d{4})', E'\\1 \\2', 'g' );
652 $func$ LANGUAGE SQL STRICT IMMUTABLE;
654 CREATE OR REPLACE FUNCTION public.approximate_date( TEXT, TEXT ) RETURNS TEXT AS $func$
655 SELECT REGEXP_REPLACE( $1, E'\\D', $2, 'g' );
656 $func$ LANGUAGE SQL STRICT IMMUTABLE;
658 CREATE OR REPLACE FUNCTION public.approximate_low_date( TEXT ) RETURNS TEXT AS $func$
659 SELECT approximate_date( $1, '0');
660 $func$ LANGUAGE SQL STRICT IMMUTABLE;
662 CREATE OR REPLACE FUNCTION public.approximate_high_date( TEXT ) RETURNS TEXT AS $func$
663 SELECT approximate_date( $1, '9');
664 $func$ LANGUAGE SQL STRICT IMMUTABLE;
666 CREATE OR REPLACE FUNCTION public.content_or_null( TEXT ) RETURNS TEXT AS $func$
667 SELECT CASE WHEN $1 ~ E'^\\s*$' THEN NULL ELSE $1 END
668 $func$ LANGUAGE SQL STRICT IMMUTABLE;
670 CREATE OR REPLACE FUNCTION public.integer_or_null( TEXT ) RETURNS TEXT AS $func$
671 SELECT CASE WHEN $1 ~ E'^\\d+$' THEN $1 ELSE NULL END
672 $func$ LANGUAGE SQL STRICT IMMUTABLE;
674 CREATE OR REPLACE FUNCTION public.force_to_isbn13( TEXT ) RETURNS TEXT AS $func$
679 # Find the first ISBN, force it to ISBN13 and return it
683 foreach my $word (split(/\s/, $input)) {
684 my $isbn = Business::ISBN->new($word);
686 # First check the checksum; if it is not valid, fix it and add the original
687 # bad-checksum ISBN to the output
688 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
689 $isbn->fix_checksum();
692 # If we now have a valid ISBN, force it to ISBN13 and return it
693 return $isbn->as_isbn13->isbn if ($isbn && $isbn->is_valid());
696 $func$ LANGUAGE PLPERLU;
698 COMMENT ON FUNCTION public.force_to_isbn13(TEXT) IS $$
699 Inspired by translate_isbn1013
701 The force_to_isbn13 function takes an input ISBN and returns the ISBN13
702 version without hypens and with a repaired checksum if the checksum was bad
706 CREATE OR REPLACE FUNCTION public.translate_isbn1013( TEXT ) RETURNS TEXT AS $func$
711 # For each ISBN found in a single string containing a set of ISBNs:
712 # * Normalize an incoming ISBN to have the correct checksum and no hyphens
713 # * Convert an incoming ISBN10 or ISBN13 to its counterpart and return
718 foreach my $word (split(/\s/, $input)) {
719 my $isbn = Business::ISBN->new($word);
721 # First check the checksum; if it is not valid, fix it and add the original
722 # bad-checksum ISBN to the output
723 if ($isbn && $isbn->is_valid_checksum() == Business::ISBN::BAD_CHECKSUM) {
724 $output .= $isbn->isbn() . " ";
725 $isbn->fix_checksum();
728 # If we now have a valid ISBN, convert it to its counterpart ISBN10/ISBN13
729 # and add the normalized original ISBN to the output
730 if ($isbn && $isbn->is_valid()) {
731 my $isbn_xlated = ($isbn->type eq "ISBN13") ? $isbn->as_isbn10 : $isbn->as_isbn13;
732 $output .= $isbn->isbn . " ";
734 # If we successfully converted the ISBN to its counterpart, add the
735 # converted ISBN to the output as well
736 $output .= ($isbn_xlated->isbn . " ") if ($isbn_xlated);
739 return $output if $output;
741 # If there were no valid ISBNs, just return the raw input
743 $func$ LANGUAGE PLPERLU;
745 COMMENT ON FUNCTION public.translate_isbn1013(TEXT) IS $$
746 The translate_isbn1013 function takes an input ISBN and returns the
747 following in a single space-delimited string if the input ISBN is valid:
748 - The normalized input ISBN (hyphens stripped)
749 - The normalized input ISBN with a fixed checksum if the checksum was bad
750 - The ISBN converted to its ISBN10 or ISBN13 counterpart, if possible
753 -- And ... a table in which to register them
755 CREATE TABLE config.index_normalizer (
756 id SERIAL PRIMARY KEY,
757 name TEXT UNIQUE NOT NULL,
760 param_count INT NOT NULL DEFAULT 0
763 CREATE TABLE config.metabib_field_index_norm_map (
764 id SERIAL PRIMARY KEY,
765 field INT NOT NULL REFERENCES config.metabib_field (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
766 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
768 pos INT NOT NULL DEFAULT 0
771 CREATE TABLE config.record_attr_definition (
772 name TEXT PRIMARY KEY,
773 label TEXT NOT NULL, -- I18N
775 multi BOOL NOT NULL DEFAULT TRUE, -- will store all values from a record
776 filter BOOL NOT NULL DEFAULT TRUE, -- becomes QP filter if true
777 sorter BOOL NOT NULL DEFAULT FALSE, -- becomes QP sort() axis if true
778 composite BOOL NOT NULL DEFAULT FALSE, -- its values are derived from others
780 -- For pre-extracted fields. Takes the first occurance, uses naive subfield ordering
781 tag TEXT, -- LIKE format
782 sf_list TEXT, -- pile-o-values, like 'abcd' for a and b and c and d
784 -- This is used for both tag/sf and xpath entries
787 -- For xpath-extracted attrs
789 format TEXT REFERENCES config.xml_transform (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
794 fixed_field TEXT, -- should exist in config.marc21_ff_pos_map.fixed_field
796 -- For phys-char fields
797 phys_char_sf INT REFERENCES config.marc21_physical_characteristic_subfield_map (id),
799 -- Source of vocabulary terms for this record attribute;
800 -- typically will be a URI referring to a SKOS vocabulary
804 CREATE TABLE config.record_attr_index_norm_map (
805 id SERIAL PRIMARY KEY,
806 attr TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
807 norm INT NOT NULL REFERENCES config.index_normalizer (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
809 pos INT NOT NULL DEFAULT 0
812 CREATE TABLE config.coded_value_map (
813 id SERIAL PRIMARY KEY,
814 ctype TEXT NOT NULL REFERENCES config.record_attr_definition (name) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
818 opac_visible BOOL NOT NULL DEFAULT TRUE, -- For TPac selectors
820 is_simple BOOL NOT NULL DEFAULT FALSE,
821 concept_uri TEXT -- URI expressing the SKOS concept that the
822 -- coded value represents
825 CREATE INDEX config_coded_value_map_ctype_idx ON config.coded_value_map (ctype);
827 CREATE VIEW config.language_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_lang';
828 CREATE VIEW config.bib_level_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'bib_level';
829 CREATE VIEW config.item_form_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_form';
830 CREATE VIEW config.item_type_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'item_type';
831 CREATE VIEW config.lit_form_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'lit_form';
832 CREATE VIEW config.audience_map AS SELECT code, value, description FROM config.coded_value_map WHERE ctype = 'audience';
833 CREATE VIEW config.videorecording_format_map AS SELECT code, value FROM config.coded_value_map WHERE ctype = 'vr_format';
835 CREATE OR REPLACE FUNCTION config.update_coded_value_map(in_ctype TEXT, in_code TEXT, in_value TEXT, in_description TEXT DEFAULT NULL, in_opac_visible BOOL DEFAULT NULL, in_search_label TEXT DEFAULT NULL, in_is_simple BOOL DEFAULT NULL, add_only BOOL DEFAULT FALSE) RETURNS VOID AS $f$
837 current_row config.coded_value_map%ROWTYPE;
839 -- Look for a current value
840 SELECT INTO current_row * FROM config.coded_value_map WHERE ctype = in_ctype AND code = in_code;
842 IF FOUND AND NOT add_only THEN
843 -- Update anything we were handed
844 current_row.value := COALESCE(current_row.value, in_value);
845 current_row.description := COALESCE(current_row.description, in_description);
846 current_row.opac_visible := COALESCE(current_row.opac_visible, in_opac_visible);
847 current_row.search_label := COALESCE(current_row.search_label, in_search_label);
848 current_row.is_simple := COALESCE(current_row.is_simple, in_is_simple);
849 UPDATE config.coded_value_map
851 value = current_row.value,
852 description = current_row.description,
853 opac_visible = current_row.opac_visible,
854 search_label = current_row.search_label,
855 is_simple = current_row.is_simple
856 WHERE id = current_row.id;
858 INSERT INTO config.coded_value_map(ctype, code, value, description, opac_visible, search_label, is_simple) VALUES
859 (in_ctype, in_code, in_value, in_description, COALESCE(in_opac_visible, TRUE), in_search_label, COALESCE(in_is_simple, FALSE));
862 $f$ LANGUAGE PLPGSQL;
864 CREATE TABLE config.composite_attr_entry_definition(
865 coded_value INT PRIMARY KEY NOT NULL REFERENCES config.coded_value_map (id) ON UPDATE CASCADE ON DELETE CASCADE,
866 definition TEXT NOT NULL -- JSON
869 -- List applied db patches that are deprecated by (and block the application of) my_db_patch
870 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_deprecates ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
871 SELECT DISTINCT l.version
872 FROM config.upgrade_log l
873 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.deprecates))
874 WHERE d.db_patch = $1
877 -- List applied db patches that are superseded by (and block the application of) my_db_patch
878 CREATE OR REPLACE FUNCTION evergreen.upgrade_list_applied_supersedes ( my_db_patch TEXT ) RETURNS SETOF evergreen.patch AS $$
879 SELECT DISTINCT l.version
880 FROM config.upgrade_log l
881 JOIN config.db_patch_dependencies d ON (l.version = ANY(d.supersedes))
882 WHERE d.db_patch = $1
885 -- List applied db patches that deprecates (and block the application of) my_db_patch
886 CREATE FUNCTION evergreen.upgrade_list_applied_deprecated ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
888 FROM config.db_patch_dependencies
889 WHERE ARRAY[$1]::TEXT[] && deprecates
892 -- List applied db patches that supersedes (and block the application of) my_db_patch
893 CREATE FUNCTION evergreen.upgrade_list_applied_superseded ( my_db_patch TEXT ) RETURNS SETOF TEXT AS $$
895 FROM config.db_patch_dependencies
896 WHERE ARRAY[$1]::TEXT[] && supersedes
899 -- Make sure that no deprecated or superseded db patches are currently applied
900 CREATE OR REPLACE FUNCTION evergreen.upgrade_verify_no_dep_conflicts ( my_db_patch TEXT ) RETURNS BOOL AS $$
902 FROM (SELECT * FROM evergreen.upgrade_list_applied_deprecates( $1 )
904 SELECT * FROM evergreen.upgrade_list_applied_supersedes( $1 )
906 SELECT * FROM evergreen.upgrade_list_applied_deprecated( $1 )
908 SELECT * FROM evergreen.upgrade_list_applied_superseded( $1 ))x
911 -- Raise an exception if there are, in fact, dep/sup conflict
912 CREATE OR REPLACE FUNCTION evergreen.upgrade_deps_block_check ( my_db_patch TEXT, my_applied_to TEXT ) RETURNS BOOL AS $$
917 IF NOT evergreen.upgrade_verify_no_dep_conflicts( my_db_patch ) THEN
918 SELECT STRING_AGG(patch, ', ') INTO deprecates FROM evergreen.upgrade_list_applied_deprecates(my_db_patch);
919 SELECT STRING_AGG(patch, ', ') INTO supersedes FROM evergreen.upgrade_list_applied_supersedes(my_db_patch);
921 Upgrade script % can not be applied:
922 applied deprecated scripts %
923 applied superseded scripts %
927 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_deprecates(my_db_patch)),
928 (SELECT ARRAY_AGG(patch) FROM evergreen.upgrade_list_applied_supersedes(my_db_patch)),
929 evergreen.upgrade_list_applied_deprecated(my_db_patch),
930 evergreen.upgrade_list_applied_superseded(my_db_patch);
933 INSERT INTO config.upgrade_log (version, applied_to) VALUES (my_db_patch, my_applied_to);
938 CREATE TABLE config.barcode_completion (
939 id SERIAL PRIMARY KEY,
940 active BOOL NOT NULL DEFAULT true,
941 org_unit INT NOT NULL, -- REFERENCES actor.org_unit(id) DEFERRABLE INITIALLY DEFERRED,
944 length INT NOT NULL DEFAULT 0,
946 padding_end BOOL NOT NULL DEFAULT false,
947 asset BOOL NOT NULL DEFAULT true,
948 actor BOOL NOT NULL DEFAULT true
951 CREATE TYPE evergreen.barcode_set AS (type TEXT, id BIGINT, barcode TEXT);
953 -- Add support for logging, only keep the most recent five rows for each category.
956 CREATE TABLE config.org_unit_setting_type_log (
957 id BIGSERIAL PRIMARY KEY,
958 date_applied TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
959 org INT, --REFERENCES actor.org_unit (id),
962 field_name TEXT REFERENCES config.org_unit_setting_type (name) DEFERRABLE INITIALLY DEFERRED
965 COMMENT ON TABLE config.org_unit_setting_type_log IS $$
966 Org Unit setting Logs
968 This table contains the most recent changes to each setting
969 in actor.org_unit_setting, allowing for mistakes to be undone.
970 This is NOT meant to be an auditor, but rather an undo/redo.
973 CREATE OR REPLACE FUNCTION evergreen.limit_oustl() RETURNS TRIGGER AS $oustl_limit$
975 -- Only keeps the most recent five settings changes.
976 DELETE FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org AND date_applied NOT IN
977 (SELECT date_applied FROM config.org_unit_setting_type_log WHERE field_name = NEW.field_name AND org = NEW.org ORDER BY date_applied DESC LIMIT 4);
979 IF (TG_OP = 'UPDATE') THEN
981 ELSIF (TG_OP = 'INSERT') THEN
986 $oustl_limit$ LANGUAGE plpgsql;
988 CREATE TRIGGER limit_logs_oust
989 BEFORE INSERT OR UPDATE ON config.org_unit_setting_type_log
990 FOR EACH ROW EXECUTE PROCEDURE limit_oustl();
992 CREATE TABLE config.sms_carrier (
993 id SERIAL PRIMARY KEY,
997 active BOOLEAN DEFAULT TRUE
1000 CREATE TYPE config.usr_activity_group AS ENUM ('authen','authz','circ','hold','search');
1002 CREATE TABLE config.usr_activity_type (
1003 id SERIAL PRIMARY KEY,
1007 label TEXT NOT NULL, -- i18n
1008 egroup config.usr_activity_group NOT NULL,
1009 enabled BOOL NOT NULL DEFAULT TRUE,
1010 transient BOOL NOT NULL DEFAULT FALSE,
1011 CONSTRAINT one_of_wwh CHECK (COALESCE(ewho,ewhat,ehow) IS NOT NULL)
1014 CREATE UNIQUE INDEX unique_wwh ON config.usr_activity_type
1015 (COALESCE(ewho,''), COALESCE (ewhat,''), COALESCE(ehow,''));
1017 CREATE TABLE config.filter_dialog_interface (
1018 key TEXT PRIMARY KEY,
1022 CREATE TABLE config.filter_dialog_filter_set (
1023 id SERIAL PRIMARY KEY,
1025 owning_lib INT NOT NULL, -- REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
1026 creator INT NOT NULL, -- REFERENCES actor.usr (id) DEFERRABLE INITIALLY DEFERRED,
1027 create_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
1028 interface TEXT NOT NULL REFERENCES config.filter_dialog_interface (key) DEFERRABLE INITIALLY DEFERRED,
1029 filters TEXT NOT NULL, -- CHECK (evergreen.is_json(filters))
1030 CONSTRAINT cfdfs_name_once_per_lib UNIQUE (name, owning_lib)
1033 CREATE TABLE config.best_hold_order(
1034 id SERIAL PRIMARY KEY,
1035 name TEXT UNIQUE, -- i18n
1036 pprox INT, -- copy capture <-> pickup lib prox
1037 hprox INT, -- copy circ lib <-> request lib prox
1038 aprox INT, -- copy circ lib <-> pickup lib ADJUSTED prox on ahcm
1039 approx INT, -- copy capture <-> pickup lib ADJUSTED prox from function
1040 priority INT, -- group hold priority
1041 cut INT, -- cut-in-line
1042 depth INT, -- selection depth
1043 htime INT, -- time since last home-lib circ exceeds org-unit setting
1044 rtime INT, -- request time
1045 shtime INT -- time since copy last trip home exceeds org-unit setting
1048 -- At least one of these columns must contain a non-null value
1049 ALTER TABLE config.best_hold_order ADD CHECK ((
1050 pprox IS NOT NULL OR
1051 hprox IS NOT NULL OR
1052 aprox IS NOT NULL OR
1053 priority IS NOT NULL OR
1055 depth IS NOT NULL OR
1056 htime IS NOT NULL OR
1060 CREATE OR REPLACE FUNCTION
1061 evergreen.z3950_attr_name_is_valid() RETURNS TRIGGER AS $func$
1064 PERFORM * FROM config.z3950_attr WHERE name = NEW.z3950_attr_type;
1070 RAISE EXCEPTION '% is not a valid Z39.50 attribute type', NEW.z3950_attr_type;
1073 $func$ LANGUAGE PLPGSQL STABLE;
1075 COMMENT ON FUNCTION evergreen.z3950_attr_name_is_valid() IS $$
1076 Used by a config.z3950_index_field_map constraint trigger
1077 to verify z3950_attr_type maps.
1080 -- drop these in down here since they reference config.metabib_field
1081 -- and config.record_attr_definition
1082 CREATE TABLE config.z3950_index_field_map (
1083 id SERIAL PRIMARY KEY,
1084 label TEXT NOT NULL, -- i18n
1085 metabib_field INTEGER REFERENCES config.metabib_field(id) ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
1086 record_attr TEXT REFERENCES config.record_attr_definition(name),
1087 z3950_attr INTEGER REFERENCES config.z3950_attr(id),
1088 z3950_attr_type TEXT,-- REFERENCES config.z3950_attr(name)
1089 CONSTRAINT metabib_field_or_record_attr CHECK (
1090 metabib_field IS NOT NULL OR
1091 record_attr IS NOT NULL
1093 CONSTRAINT attr_or_attr_type CHECK (
1094 z3950_attr IS NOT NULL OR
1095 z3950_attr_type IS NOT NULL
1099 CREATE CONSTRAINT TRIGGER valid_z3950_attr_type AFTER INSERT OR UPDATE ON config.z3950_index_field_map
1100 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (NEW.z3950_attr_type IS NOT NULL)
1101 EXECUTE PROCEDURE evergreen.z3950_attr_name_is_valid();
1103 CREATE TABLE config.marc_format (
1104 id SERIAL PRIMARY KEY,
1108 COMMENT ON TABLE config.marc_format IS $$
1109 List of MARC formats supported by this Evergreen
1110 database. This exists primarily as a hook for future
1111 support of UNIMARC, though whether that will ever
1112 happen remains to be seen.
1115 CREATE TYPE config.marc_record_type AS ENUM ('biblio', 'authority', 'serial');
1117 CREATE TABLE config.marc_field (
1118 id SERIAL PRIMARY KEY,
1119 marc_format INTEGER NOT NULL
1120 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1121 marc_record_type config.marc_record_type NOT NULL,
1122 tag CHAR(3) NOT NULL,
1125 fixed_field BOOLEAN,
1129 owner INTEGER -- REFERENCES actor.org_unit (id)
1130 -- if the owner is null, the data about the field is
1131 -- assumed to come from the controlling MARC standard
1134 COMMENT ON TABLE config.marc_field IS $$
1135 This table stores a list of MARC fields recognized by the Evergreen
1136 instance. Note that we're not aiming for completely generic ISO2709
1137 support: we're assuming things like three characters for a tag,
1138 one-character subfield labels, two indicators per variable data field,
1139 and the like, all of which are technically specializations of ISO2709.
1141 Of particular significance is the owner column; if it's set to a null
1142 value, the field definition is assumed to come from a national
1143 standards body; if it's set to a non-null value, the field definition
1144 is an OU-level addition to or override of the standard.
1147 CREATE INDEX config_marc_field_tag_idx ON config.marc_field (tag);
1148 CREATE INDEX config_marc_field_owner_idx ON config.marc_field (owner);
1150 CREATE UNIQUE INDEX config_standard_marc_tags_are_unique
1151 ON config.marc_field(marc_format, marc_record_type, tag)
1152 WHERE owner IS NULL;
1153 ALTER TABLE config.marc_field
1154 ADD CONSTRAINT config_standard_marc_tags_are_fully_specified
1155 CHECK ((owner IS NOT NULL) OR
1158 repeatable IS NOT NULL AND
1159 mandatory IS NOT NULL AND
1164 CREATE TABLE config.marc_subfield (
1165 id SERIAL PRIMARY KEY,
1166 marc_format INTEGER NOT NULL
1167 REFERENCES config.marc_format (id) DEFERRABLE INITIALLY DEFERRED,
1168 marc_record_type config.marc_record_type NOT NULL,
1169 tag CHAR(3) NOT NULL,
1170 code CHAR(1) NOT NULL,
1176 REFERENCES config.record_attr_definition (name)
1177 DEFERRABLE INITIALLY DEFERRED,
1178 owner INTEGER -- REFERENCES actor.org_unit (id)
1179 -- if the owner is null, the data about the subfield is
1180 -- assumed to come from the controlling MARC standard
1183 COMMENT ON TABLE config.marc_subfield IS $$
1184 This table stores the list of subfields recognized by this Evergreen
1185 instance. As with config.marc_field, of particular significance is the
1186 owner column; if it's set to a null value, the subfield definition is
1187 assumed to come from a national standards body; if it's set to a non-null
1188 value, the subfield definition is an OU-level addition to or override
1192 CREATE INDEX config_marc_subfield_tag_code_idx ON config.marc_subfield (tag, code);
1193 CREATE UNIQUE INDEX config_standard_marc_subfields_are_unique
1194 ON config.marc_subfield(marc_format, marc_record_type, tag, code)
1195 WHERE owner IS NULL;
1196 ALTER TABLE config.marc_subfield
1197 ADD CONSTRAINT config_standard_marc_subfields_are_fully_specified
1198 CHECK ((owner IS NOT NULL) OR
1201 repeatable IS NOT NULL AND
1202 mandatory IS NOT NULL AND