2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS asset CASCADE;
24 CREATE TABLE asset.copy_location (
25 id SERIAL PRIMARY KEY,
27 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
28 holdable BOOL NOT NULL DEFAULT TRUE,
29 hold_verify BOOL NOT NULL DEFAULT FALSE,
30 opac_visible BOOL NOT NULL DEFAULT TRUE,
31 circulate BOOL NOT NULL DEFAULT TRUE,
34 checkin_alert BOOL NOT NULL DEFAULT FALSE,
35 deleted BOOL NOT NULL DEFAULT FALSE,
38 CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE;
40 CREATE TABLE asset.copy_location_order
42 id SERIAL PRIMARY KEY,
44 REFERENCES asset.copy_location
46 DEFERRABLE INITIALLY DEFERRED,
48 REFERENCES actor.org_unit
50 DEFERRABLE INITIALLY DEFERRED,
51 position INT NOT NULL DEFAULT 0,
52 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
55 CREATE TABLE asset.copy_location_group (
56 id SERIAL PRIMARY KEY,
57 name TEXT NOT NULL, -- i18n
58 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
59 pos INT NOT NULL DEFAULT 0,
60 top BOOL NOT NULL DEFAULT FALSE,
61 opac_visible BOOL NOT NULL DEFAULT TRUE,
62 CONSTRAINT lgroup_once_per_owner UNIQUE (owner,name)
65 CREATE TABLE asset.copy_location_group_map (
66 id SERIAL PRIMARY KEY,
67 location INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
68 lgroup INT NOT NULL REFERENCES asset.copy_location_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
69 CONSTRAINT lgroup_once_per_group UNIQUE (lgroup,location)
72 CREATE TABLE asset.copy (
73 id BIGSERIAL PRIMARY KEY,
74 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
75 creator BIGINT NOT NULL,
76 call_number BIGINT NOT NULL,
77 editor BIGINT NOT NULL,
78 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
79 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
81 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
82 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
83 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
84 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
86 circulate BOOL NOT NULL DEFAULT TRUE,
87 deposit BOOL NOT NULL DEFAULT FALSE,
88 ref BOOL NOT NULL DEFAULT FALSE,
89 holdable BOOL NOT NULL DEFAULT TRUE,
90 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
92 barcode TEXT NOT NULL,
98 opac_visible BOOL NOT NULL DEFAULT TRUE,
99 deleted BOOL NOT NULL DEFAULT FALSE,
102 status_changed_time TIMESTAMP WITH TIME ZONE,
103 active_date TIMESTAMP WITH TIME ZONE,
104 mint_condition BOOL NOT NULL DEFAULT TRUE,
107 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
108 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
109 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
110 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
111 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
112 CREATE INDEX cp_create_date ON asset.copy (create_date);
113 CREATE INDEX cp_extant_by_circ_lib_idx ON asset.copy(circ_lib) WHERE deleted = FALSE OR deleted IS FALSE;
114 CREATE RULE protect_copy_delete AS ON DELETE TO asset.copy DO INSTEAD UPDATE asset.copy SET deleted = TRUE WHERE OLD.id = asset.copy.id;
116 CREATE TABLE asset.copy_part_map (
117 id SERIAL PRIMARY KEY,
118 target_copy BIGINT NOT NULL, -- points o asset.copy
119 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
121 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
123 CREATE TABLE asset.copy_inventory (
124 id SERIAL PRIMARY KEY,
125 inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
126 inventory_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
129 CREATE INDEX copy_inventory_copy_idx ON asset.copy_inventory (copy);
130 CREATE UNIQUE INDEX asset_copy_inventory_date_once_per_copy ON asset.copy_inventory (inventory_date, copy);
132 CREATE OR REPLACE FUNCTION asset.copy_may_float_to_inventory_workstation() RETURNS TRIGGER AS $func$
134 copy asset.copy%ROWTYPE;
135 workstation actor.workstation%ROWTYPE;
137 SELECT * INTO copy FROM asset.copy WHERE id = NEW.copy;
139 SELECT * INTO workstation FROM actor.workstation WHERE id = NEW.inventory_workstation;
141 IF copy.floating IS NULL THEN
142 IF copy.circ_lib <> workstation.owning_lib THEN
143 RAISE EXCEPTION 'Inventory workstation owning lib (%) does not match copy circ lib (%).',
144 workstation.owning_lib, copy.circ_lib;
147 IF NOT evergreen.can_float(copy.floating, copy.circ_lib, workstation.owning_lib) THEN
148 RAISE EXCEPTION 'Copy (%) cannot float to inventory workstation owning lib (%).',
149 copy.id, workstation.owning_lib;
156 $func$ LANGUAGE PLPGSQL VOLATILE COST 50;
158 CREATE CONSTRAINT TRIGGER asset_copy_inventory_allowed_trig
159 AFTER UPDATE OR INSERT ON asset.copy_inventory
160 DEFERRABLE FOR EACH ROW EXECUTE PROCEDURE asset.copy_may_float_to_inventory_workstation();
162 CREATE VIEW asset.latest_inventory (id, inventory_workstation, inventory_date, copy) AS
163 SELECT DISTINCT ON (copy) id, inventory_workstation, inventory_date, copy
164 FROM asset.copy_inventory
165 ORDER BY copy, inventory_date DESC;
167 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
168 RETURNS TRIGGER AS $$
170 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
171 NEW.status_changed_time := now();
172 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
173 NEW.active_date := now();
180 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
181 CREATE OR REPLACE FUNCTION asset.acp_created()
182 RETURNS TRIGGER AS $$
184 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
185 NEW.active_date := now();
187 IF NEW.status_changed_time IS NULL THEN
188 NEW.status_changed_time := now();
194 CREATE TRIGGER acp_status_changed_trig
195 BEFORE UPDATE ON asset.copy
196 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
198 CREATE TRIGGER acp_created_trig
199 BEFORE INSERT ON asset.copy
200 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
202 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
203 RETURNS TRIGGER AS $$
205 new_copy_location INT;
207 IF (TG_OP = 'UPDATE') THEN
208 IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN
212 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance((SELECT owning_lib FROM asset.call_number WHERE id = NEW.call_number)) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
213 IF new_copy_location IS NULL THEN
214 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance(NEW.circ_lib) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
216 IF new_copy_location IS NOT NULL THEN
217 NEW.location = new_copy_location;
223 CREATE TRIGGER acp_location_fixer_trig
224 BEFORE INSERT OR UPDATE OF location, call_number, circ_lib ON asset.copy
225 FOR EACH ROW EXECUTE PROCEDURE asset.acp_location_fixer();
227 CREATE TABLE asset.stat_cat_sip_fields (
228 field CHAR(2) PRIMARY KEY,
230 one_only BOOL NOT NULL DEFAULT FALSE
232 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
233 Asset Statistical Category SIP Fields
235 Contains the list of valid SIP Field identifiers for
236 Statistical Categories.
239 CREATE TABLE asset.stat_cat_entry_transparency_map (
240 id BIGSERIAL PRIMARY KEY,
241 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
242 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
243 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
244 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
247 CREATE TABLE asset.stat_cat (
248 id SERIAL PRIMARY KEY,
250 opac_visible BOOL NOT NULL DEFAULT FALSE,
252 required BOOL NOT NULL DEFAULT FALSE,
253 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
255 checkout_archive BOOL NOT NULL DEFAULT FALSE,
256 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
259 CREATE TABLE asset.stat_cat_entry (
260 id SERIAL PRIMARY KEY,
261 stat_cat INT NOT NULL,
264 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
267 CREATE TABLE asset.stat_cat_entry_copy_map (
268 id BIGSERIAL PRIMARY KEY,
269 stat_cat INT NOT NULL,
270 stat_cat_entry INT NOT NULL,
271 owning_copy BIGINT NOT NULL,
272 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
274 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
276 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
278 sipfield asset.stat_cat_sip_fields%ROWTYPE;
281 IF NEW.sip_field IS NOT NULL THEN
282 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
283 IF sipfield.one_only THEN
284 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
285 IF use_count > 0 THEN
286 RAISE EXCEPTION 'Sip field cannot be used twice';
292 $func$ LANGUAGE PLPGSQL;
294 CREATE TRIGGER asset_stat_cat_sip_update_trigger
295 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
296 EXECUTE PROCEDURE asset.stat_cat_check();
298 CREATE TABLE asset.copy_note (
299 id BIGSERIAL PRIMARY KEY,
300 owning_copy BIGINT NOT NULL,
301 creator BIGINT NOT NULL,
302 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
303 pub BOOL NOT NULL DEFAULT FALSE,
307 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
308 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
310 CREATE TABLE asset.uri (
311 id SERIAL PRIMARY KEY,
314 use_restriction TEXT,
315 active BOOL NOT NULL DEFAULT TRUE
318 CREATE TABLE asset.call_number_class (
319 id bigserial PRIMARY KEY,
321 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
322 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
324 COMMENT ON TABLE asset.call_number_class IS $$
325 Defines the call number normalization database functions in the "normalizer"
326 column and the tag/subfield combinations to use to lookup the call number in
327 the "field" column for a given classification scheme. Tag/subfield combinations
328 are delimited by commas.
331 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
335 sortkey := NEW.label_sortkey;
337 IF NEW.label_class IS NULL THEN
338 NEW.label_class := COALESCE(
340 SELECT substring(value from E'\\d+')::integer
341 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
346 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
347 quote_literal( NEW.label ) || ')'
348 FROM asset.call_number_class acnc
349 WHERE acnc.id = NEW.label_class
351 NEW.label_sortkey = sortkey;
354 $func$ LANGUAGE PLPGSQL;
356 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
357 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
358 # thus could probably be considered a derived work, although nothing was
359 # directly copied - but to err on the safe side of providing attribution:
360 # Copyright (C) 2007 LibLime
361 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
362 # Licensed under the GPL v2 or later
367 # Converts the callnumber to uppercase
368 # Strips spaces from start and end of the call number
369 # Converts anything other than letters, digits, and periods into spaces
370 # Collapses multiple spaces into a single underscore
371 my $callnum = uc(shift);
372 $callnum =~ s/^\s//g;
373 $callnum =~ s/\s$//g;
374 # NOTE: this previously used underscores, but this caused sorting issues
375 # for the "before" half of page 0 on CN browse, sorting CNs containing a
376 # decimal before "whole number" CNs
377 $callnum =~ s/[^A-Z0-9_.]/ /g;
378 $callnum =~ s/ {2,}/ /g;
381 $func$ LANGUAGE PLPERLU IMMUTABLE;
383 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
384 # Derived from the Koha C4::ClassSortRoutine::Dewey module
385 # Copyright (C) 2007 LibLime
386 # Licensed under the GPL v2 or later
391 my $init = uc(shift);
395 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
396 my @tokens = split /\.|\s+/, $init;
397 my $digit_group_count = 0;
398 my $first_digit_group_idx;
399 for (my $i = 0; $i <= $#tokens; $i++) {
400 if ($tokens[$i] =~ /^\d+$/) {
401 $digit_group_count++;
402 if ($digit_group_count == 1) {
403 $first_digit_group_idx = $i;
405 if (2 == $digit_group_count) {
406 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
407 $tokens[$i] =~ tr/ /0/;
411 # Pad the first digit_group if there was only one
412 if (1 == $digit_group_count) {
413 $tokens[$first_digit_group_idx] .= '_000000000000000'
415 my $key = join("_", @tokens);
416 $key =~ s/[^\p{IsAlnum}_]//g;
420 $func$ LANGUAGE PLPERLU IMMUTABLE;
423 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
427 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
428 # The author hopes to upload it to CPAN some day, which would make our lives easier
429 use Library::CallNumber::LC;
431 my $callnum = Library::CallNumber::LC->new(shift);
432 return $callnum->normalize();
434 $func$ LANGUAGE PLPERLU IMMUTABLE;
436 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
437 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
438 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
439 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
442 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
444 NEW.label_sortkey := REGEXP_REPLACE(
445 evergreen.lpad_number_substrings(
446 naco_normalize(NEW.label),
458 CREATE TABLE asset.call_number_prefix (
459 id SERIAL PRIMARY KEY,
460 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
461 label TEXT NOT NULL, -- i18n
464 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
465 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
466 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
468 CREATE TABLE asset.call_number_suffix (
469 id SERIAL PRIMARY KEY,
470 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
471 label TEXT NOT NULL, -- i18n
474 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
475 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
476 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
478 CREATE TABLE asset.call_number (
479 id bigserial PRIMARY KEY,
480 creator BIGINT NOT NULL,
481 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
482 editor BIGINT NOT NULL,
483 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
484 record bigint NOT NULL,
485 owning_lib INT NOT NULL,
487 deleted BOOL NOT NULL DEFAULT FALSE,
488 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
489 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
490 label_class BIGINT NOT NULL
491 REFERENCES asset.call_number_class(id)
492 DEFERRABLE INITIALLY DEFERRED,
495 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
496 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
497 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
498 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
499 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
500 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
501 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label, prefix, suffix) WHERE deleted = FALSE OR deleted IS FALSE;
502 CREATE INDEX asset_call_number_label_sortkey_browse ON asset.call_number(oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE deleted IS FALSE OR deleted = FALSE;
503 CREATE RULE protect_cn_delete AS ON DELETE TO asset.call_number DO INSTEAD UPDATE asset.call_number SET deleted = TRUE WHERE OLD.id = asset.call_number.id;
504 CREATE RULE protect_acn_id_neg1 AS ON UPDATE TO asset.call_number WHERE OLD.id = -1 DO INSTEAD NOTHING;
506 CREATE TRIGGER asset_label_sortkey_trigger
507 BEFORE UPDATE OR INSERT ON asset.call_number
508 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
510 CREATE TABLE asset.uri_call_number_map (
511 id BIGSERIAL PRIMARY KEY,
512 uri INT NOT NULL REFERENCES asset.uri (id),
513 call_number INT NOT NULL REFERENCES asset.call_number (id),
514 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
516 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
518 CREATE TABLE asset.call_number_note (
519 id BIGSERIAL PRIMARY KEY,
520 call_number BIGINT NOT NULL,
521 creator BIGINT NOT NULL,
522 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
523 pub BOOL NOT NULL DEFAULT FALSE,
527 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
529 CREATE TABLE asset.copy_template (
530 id SERIAL PRIMARY KEY,
531 owning_lib INT NOT NULL
532 REFERENCES actor.org_unit (id)
533 DEFERRABLE INITIALLY DEFERRED,
534 creator BIGINT NOT NULL
535 REFERENCES actor.usr (id)
536 DEFERRABLE INITIALLY DEFERRED,
537 editor BIGINT NOT NULL
538 REFERENCES actor.usr (id)
539 DEFERRABLE INITIALLY DEFERRED,
540 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
541 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
543 -- columns above this point are attributes of the template itself
544 -- columns after this point are attributes of the copy this template modifies/creates
545 circ_lib INT REFERENCES actor.org_unit (id)
546 DEFERRABLE INITIALLY DEFERRED,
547 status INT REFERENCES config.copy_status (id)
548 DEFERRABLE INITIALLY DEFERRED,
549 location INT REFERENCES asset.copy_location (id)
550 DEFERRABLE INITIALLY DEFERRED,
551 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
552 loan_duration IS NULL OR loan_duration IN (1,2,3)),
553 fine_level INT CONSTRAINT valid_fine_level CHECK (
554 fine_level IS NULL OR fine_level IN (1,2,3)),
560 deposit_amount NUMERIC(6,2),
570 CREATE TABLE asset.copy_vis_attr_cache (
571 id BIGSERIAL PRIMARY KEY,
572 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
573 target_copy BIGINT NOT NULL,
574 vis_attr_vector INT[]
576 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
577 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
579 CREATE OR REPLACE FUNCTION asset.opac_ou_record_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
584 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
586 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
588 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
589 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
590 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
594 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
600 asset.copy_vis_attr_cache av
601 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
602 JOIN asset.call_number cn ON (cp.call_number = cn.id AND not cn.deleted)
603 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
607 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
614 $f$ LANGUAGE PLPGSQL;
616 CREATE OR REPLACE FUNCTION asset.opac_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
621 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
623 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
625 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
626 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
627 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
631 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
636 asset.copy_vis_attr_cache av
637 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
638 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
642 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
649 $f$ LANGUAGE PLPGSQL;
651 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count(org integer, rid bigint)
652 RETURNS TABLE(depth integer, org_unit integer, visible bigint, available bigint, unshadow bigint, transcendant integer)
659 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
661 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
663 WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
666 (cp.status = ANY (available_statuses.ids))::INT as available,
667 (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
670 actor.org_unit_descendants(ans.id) d
671 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
672 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
673 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
677 (cp.status = ANY (available_statuses.ids))::INT as available,
678 (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
681 actor.org_unit_descendants(ans.id) d
682 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
683 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
684 JOIN biblio.peer_bib_copy_map bp ON (bp.peer_record = rid AND bp.target_copy = cp.id)
686 select ans.depth, ans.id, count(id), sum(x.available::int), sum(x.opac_visible::int), trans
687 from ((select * from cp) union (select * from peer)) x
691 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
699 CREATE OR REPLACE FUNCTION asset.staff_lasso_record_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
704 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
706 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
711 SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available)
713 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
716 actor.org_unit_descendants(ans.id) d
717 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
718 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
719 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
723 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
730 $f$ LANGUAGE PLPGSQL;
732 CREATE OR REPLACE FUNCTION asset.record_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
734 IF staff IS TRUE THEN
736 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
738 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
742 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
744 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
750 $f$ LANGUAGE PLPGSQL;
752 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
757 JOIN asset.call_number acn ON acp.call_number = acn.id
758 JOIN asset.copy_location acpl ON acp.location = acpl.id
759 JOIN config.copy_status ccs ON acp.status = ccs.id
762 AND acp.holdable = true
763 AND acpl.holdable = true
764 AND ccs.holdable = true
765 AND acp.deleted = false
766 AND acpl.deleted = false
767 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
774 $f$ LANGUAGE PLPGSQL;
776 CREATE OR REPLACE FUNCTION asset.opac_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
781 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
783 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
785 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
786 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
787 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
791 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
797 asset.copy_vis_attr_cache av
798 JOIN asset.copy cp ON (cp.id = av.target_copy)
799 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
800 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
804 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
811 $f$ LANGUAGE PLPGSQL;
813 CREATE OR REPLACE FUNCTION asset.opac_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
818 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
820 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
822 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
823 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
824 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
828 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
834 asset.copy_vis_attr_cache av
835 JOIN asset.copy cp ON (cp.id = av.target_copy)
836 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
837 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
841 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
848 $f$ LANGUAGE PLPGSQL;
850 CREATE OR REPLACE FUNCTION asset.staff_ou_metarecord_copy_count (org INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
855 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
857 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
862 SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN 1 ELSE 0 END ),
866 actor.org_unit_descendants(ans.id) d
867 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
868 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
869 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
873 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
880 $f$ LANGUAGE PLPGSQL;
882 CREATE OR REPLACE FUNCTION asset.staff_lasso_metarecord_copy_count (i_lasso INT, rid BIGINT) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
887 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
889 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
894 SUM( CASE WHEN cp.status IN (SELECT id FROM config.copy_status WHERE holdable AND is_available) THEN 1 ELSE 0 END ),
898 actor.org_unit_descendants(ans.id) d
899 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
900 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
901 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
905 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
912 $f$ LANGUAGE PLPGSQL;
914 CREATE OR REPLACE FUNCTION asset.metarecord_copy_count ( place INT, rid BIGINT, staff BOOL) RETURNS TABLE (depth INT, org_unit INT, visible BIGINT, available BIGINT, unshadow BIGINT, transcendant INT) AS $f$
916 IF staff IS TRUE THEN
918 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
920 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
924 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
926 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
932 $f$ LANGUAGE PLPGSQL;
934 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
939 JOIN asset.call_number acn ON acp.call_number = acn.id
940 JOIN asset.copy_location acpl ON acp.location = acpl.id
941 JOIN config.copy_status ccs ON acp.status = ccs.id
942 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
944 mmsm.metarecord = rid
945 AND acp.holdable = true
946 AND acpl.holdable = true
947 AND ccs.holdable = true
948 AND acp.deleted = false
949 AND acpl.deleted = false
950 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
957 $f$ LANGUAGE PLPGSQL;
959 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
961 IF NEW.barcode LIKE '@@%' THEN
962 NEW.barcode := '@@' || NEW.id;
966 $f$ LANGUAGE PLPGSQL;
968 CREATE TRIGGER autogenerate_placeholder_barcode
969 BEFORE INSERT OR UPDATE ON asset.copy
970 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
972 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
976 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
977 IF copy_id IS NOT NULL THEN
978 PERFORM * FROM asset.copy WHERE id = copy_id;
980 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
985 $F$ LANGUAGE PLPGSQL;
987 CREATE TABLE asset.copy_tag (
988 id SERIAL PRIMARY KEY,
989 tag_type TEXT REFERENCES config.copy_tag_type (code)
990 ON UPDATE CASCADE ON DELETE CASCADE,
993 index_vector tsvector NOT NULL,
995 pub BOOLEAN DEFAULT TRUE,
996 owner INTEGER NOT NULL REFERENCES actor.org_unit (id),
1000 CREATE INDEX asset_copy_tag_label_idx
1001 ON asset.copy_tag (label);
1002 CREATE INDEX asset_copy_tag_label_lower_idx
1003 ON asset.copy_tag (evergreen.lowercase(label));
1004 CREATE INDEX asset_copy_tag_index_vector_idx
1006 USING GIN(index_vector);
1007 CREATE INDEX asset_copy_tag_tag_type_idx
1008 ON asset.copy_tag (tag_type);
1009 CREATE INDEX asset_copy_tag_owner_idx
1010 ON asset.copy_tag (owner);
1012 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
1014 IF NEW.value IS NULL THEN
1015 NEW.value = NEW.label;
1020 $$ LANGUAGE 'plpgsql';
1022 -- name of following trigger chosen to ensure it runs first
1023 CREATE TRIGGER asset_copy_tag_do_value
1024 BEFORE INSERT OR UPDATE ON asset.copy_tag
1025 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
1026 CREATE TRIGGER asset_copy_tag_fti_trigger
1027 BEFORE UPDATE OR INSERT ON asset.copy_tag
1028 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
1030 CREATE TABLE asset.copy_tag_copy_map (
1031 id BIGSERIAL PRIMARY KEY,
1033 tag INTEGER REFERENCES asset.copy_tag (id)
1034 ON UPDATE CASCADE ON DELETE CASCADE
1037 CREATE INDEX asset_copy_tag_copy_map_copy_idx
1038 ON asset.copy_tag_copy_map (copy);
1039 CREATE INDEX asset_copy_tag_copy_map_tag_idx
1040 ON asset.copy_tag_copy_map (tag);
1042 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
1044 last_circ_stop TEXT;
1045 the_copy asset.copy%ROWTYPE;
1048 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
1049 IF NOT FOUND THEN RETURN NULL; END IF;
1051 IF the_copy.status = 3 THEN -- Lost
1053 ELSIF the_copy.status = 4 THEN -- Missing
1055 ELSIF the_copy.status = 14 THEN -- Damaged
1057 ELSIF the_copy.status = 17 THEN -- Lost and paid
1058 RETURN 'LOST_AND_PAID';
1061 SELECT stop_fines INTO last_circ_stop
1062 FROM action.circulation
1063 WHERE target_copy = cid AND checkin_time IS NULL
1064 ORDER BY xact_start DESC LIMIT 1;
1067 IF last_circ_stop IN (
1068 'CLAIMSNEVERCHECKEDOUT',
1072 RETURN last_circ_stop;
1078 $$ LANGUAGE PLPGSQL;
1080 CREATE TYPE config.copy_alert_type_state AS ENUM (
1088 'CLAIMSNEVERCHECKEDOUT'
1091 CREATE TYPE config.copy_alert_type_event AS ENUM (
1096 CREATE TABLE config.copy_alert_type (
1097 id serial primary key, -- reserve 1-100 for system
1098 scope_org int not null references actor.org_unit (id) on delete cascade,
1099 active bool not null default true,
1100 name text not null unique,
1101 state config.copy_alert_type_state,
1102 event config.copy_alert_type_event,
1104 invert_location bool not null default false,
1109 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1111 CREATE TABLE actor.copy_alert_suppress (
1112 id serial primary key,
1113 org int not null references actor.org_unit (id) on delete cascade,
1114 alert_type int not null references config.copy_alert_type (id) on delete cascade
1117 CREATE TABLE asset.copy_alert (
1118 id bigserial primary key,
1119 alert_type int not null references config.copy_alert_type (id) on delete cascade,
1120 copy bigint not null,
1121 temp bool not null default false,
1122 create_time timestamptz not null default now(),
1123 create_staff bigint not null references actor.usr (id) on delete set null,
1125 ack_time timestamptz,
1126 ack_staff bigint references actor.usr (id) on delete set null
1129 CREATE VIEW asset.active_copy_alert AS
1131 FROM asset.copy_alert
1132 WHERE ack_time IS NULL;
1134 CREATE TABLE asset.course_module_course (
1135 id SERIAL PRIMARY KEY,
1137 course_number TEXT NOT NULL,
1138 section_number TEXT,
1139 owning_lib INT REFERENCES actor.org_unit (id),
1140 is_archived BOOLEAN DEFAULT false
1143 CREATE TABLE asset.course_module_role (
1144 id SERIAL PRIMARY KEY,
1145 name TEXT UNIQUE NOT NULL,
1146 is_public BOOLEAN NOT NULL DEFAULT false
1149 CREATE TABLE asset.course_module_course_users (
1150 id SERIAL PRIMARY KEY,
1151 course INT NOT NULL REFERENCES asset.course_module_course (id),
1152 usr INT NOT NULL REFERENCES actor.usr (id),
1153 usr_role INT REFERENCES asset.course_module_role (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1156 CREATE TABLE asset.course_module_course_materials (
1157 id SERIAL PRIMARY KEY,
1158 course INT NOT NULL REFERENCES asset.course_module_course (id),
1159 item INT REFERENCES asset.copy (id),
1161 record INT REFERENCES biblio.record_entry (id),
1162 temporary_record BOOLEAN,
1163 original_location INT REFERENCES asset.copy_location,
1164 original_status INT REFERENCES config.copy_status,
1165 original_circ_modifier TEXT, --REFERENCES config.circ_modifier
1166 original_callnumber INT REFERENCES asset.call_number,
1167 original_circ_lib INT REFERENCES actor.org_unit (id),
1168 unique (course, item, record)
1171 CREATE TABLE asset.course_module_term (
1172 id SERIAL PRIMARY KEY,
1174 owning_lib INT REFERENCES actor.org_unit (id),
1175 start_date TIMESTAMP WITH TIME ZONE,
1176 end_date TIMESTAMP WITH TIME ZONE,
1177 CONSTRAINT cmt_once_per_owning_lib UNIQUE (owning_lib, name)
1180 CREATE TABLE asset.course_module_term_course_map (
1181 id BIGSERIAL PRIMARY KEY,
1182 term INT NOT NULL REFERENCES asset.course_module_term (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1183 course INT NOT NULL REFERENCES asset.course_module_course (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED