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_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7);
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.opac_visible_copies (
124 id BIGSERIAL primary key,
125 copy_id BIGINT, -- copy id
129 COMMENT ON TABLE asset.opac_visible_copies IS $$
130 Materialized view of copies that are visible in the OPAC, used by
131 search.query_parser_fts() to speed up OPAC visibility checks on large
132 databases. Contents are maintained by a set of triggers.
134 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
135 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
136 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
138 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
139 RETURNS TRIGGER AS $$
141 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
142 NEW.status_changed_time := now();
143 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
144 NEW.active_date := now();
151 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
152 CREATE OR REPLACE FUNCTION asset.acp_created()
153 RETURNS TRIGGER AS $$
155 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
156 NEW.active_date := now();
158 IF NEW.status_changed_time IS NULL THEN
159 NEW.status_changed_time := now();
165 CREATE TRIGGER acp_status_changed_trig
166 BEFORE UPDATE ON asset.copy
167 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
169 CREATE TRIGGER acp_created_trig
170 BEFORE INSERT ON asset.copy
171 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
173 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
174 RETURNS TRIGGER AS $$
176 new_copy_location INT;
178 IF (TG_OP = 'UPDATE') THEN
179 IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN
183 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;
184 IF new_copy_location IS NULL THEN
185 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;
187 IF new_copy_location IS NOT NULL THEN
188 NEW.location = new_copy_location;
194 CREATE TRIGGER acp_location_fixer_trig
195 BEFORE INSERT OR UPDATE OF location, call_number, circ_lib ON asset.copy
196 FOR EACH ROW EXECUTE PROCEDURE asset.acp_location_fixer();
198 CREATE TABLE asset.stat_cat_sip_fields (
199 field CHAR(2) PRIMARY KEY,
201 one_only BOOL NOT NULL DEFAULT FALSE
203 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
204 Asset Statistical Category SIP Fields
206 Contains the list of valid SIP Field identifiers for
207 Statistical Categories.
210 CREATE TABLE asset.stat_cat_entry_transparency_map (
211 id BIGSERIAL PRIMARY KEY,
212 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
213 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
214 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
215 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
218 CREATE TABLE asset.stat_cat (
219 id SERIAL PRIMARY KEY,
221 opac_visible BOOL NOT NULL DEFAULT FALSE,
223 required BOOL NOT NULL DEFAULT FALSE,
224 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
226 checkout_archive BOOL NOT NULL DEFAULT FALSE,
227 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
230 CREATE TABLE asset.stat_cat_entry (
231 id SERIAL PRIMARY KEY,
232 stat_cat INT NOT NULL,
235 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
238 CREATE TABLE asset.stat_cat_entry_copy_map (
239 id BIGSERIAL PRIMARY KEY,
240 stat_cat INT NOT NULL,
241 stat_cat_entry INT NOT NULL,
242 owning_copy BIGINT NOT NULL,
243 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
245 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
247 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
249 sipfield asset.stat_cat_sip_fields%ROWTYPE;
252 IF NEW.sip_field IS NOT NULL THEN
253 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
254 IF sipfield.one_only THEN
255 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
256 IF use_count > 0 THEN
257 RAISE EXCEPTION 'Sip field cannot be used twice';
263 $func$ LANGUAGE PLPGSQL;
265 CREATE TRIGGER asset_stat_cat_sip_update_trigger
266 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
267 EXECUTE PROCEDURE asset.stat_cat_check();
269 CREATE TABLE asset.copy_note (
270 id BIGSERIAL PRIMARY KEY,
271 owning_copy BIGINT NOT NULL,
272 creator BIGINT NOT NULL,
273 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
274 pub BOOL NOT NULL DEFAULT FALSE,
278 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
279 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
281 CREATE TABLE asset.uri (
282 id SERIAL PRIMARY KEY,
285 use_restriction TEXT,
286 active BOOL NOT NULL DEFAULT TRUE
289 CREATE TABLE asset.call_number_class (
290 id bigserial PRIMARY KEY,
292 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
293 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
295 COMMENT ON TABLE asset.call_number_class IS $$
296 Defines the call number normalization database functions in the "normalizer"
297 column and the tag/subfield combinations to use to lookup the call number in
298 the "field" column for a given classification scheme. Tag/subfield combinations
299 are delimited by commas.
302 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
306 sortkey := NEW.label_sortkey;
308 IF NEW.label_class IS NULL THEN
309 NEW.label_class := COALESCE(
311 SELECT substring(value from E'\\d+')::integer
312 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
317 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
318 quote_literal( NEW.label ) || ')'
319 FROM asset.call_number_class acnc
320 WHERE acnc.id = NEW.label_class
322 NEW.label_sortkey = sortkey;
325 $func$ LANGUAGE PLPGSQL;
327 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
328 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
329 # thus could probably be considered a derived work, although nothing was
330 # directly copied - but to err on the safe side of providing attribution:
331 # Copyright (C) 2007 LibLime
332 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
333 # Licensed under the GPL v2 or later
338 # Converts the callnumber to uppercase
339 # Strips spaces from start and end of the call number
340 # Converts anything other than letters, digits, and periods into spaces
341 # Collapses multiple spaces into a single underscore
342 my $callnum = uc(shift);
343 $callnum =~ s/^\s//g;
344 $callnum =~ s/\s$//g;
345 # NOTE: this previously used underscores, but this caused sorting issues
346 # for the "before" half of page 0 on CN browse, sorting CNs containing a
347 # decimal before "whole number" CNs
348 $callnum =~ s/[^A-Z0-9_.]/ /g;
349 $callnum =~ s/ {2,}/ /g;
352 $func$ LANGUAGE PLPERLU IMMUTABLE;
354 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
355 # Derived from the Koha C4::ClassSortRoutine::Dewey module
356 # Copyright (C) 2007 LibLime
357 # Licensed under the GPL v2 or later
362 my $init = uc(shift);
366 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
367 my @tokens = split /\.|\s+/, $init;
368 my $digit_group_count = 0;
369 my $first_digit_group_idx;
370 for (my $i = 0; $i <= $#tokens; $i++) {
371 if ($tokens[$i] =~ /^\d+$/) {
372 $digit_group_count++;
373 if ($digit_group_count == 1) {
374 $first_digit_group_idx = $i;
376 if (2 == $digit_group_count) {
377 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
378 $tokens[$i] =~ tr/ /0/;
382 # Pad the first digit_group if there was only one
383 if (1 == $digit_group_count) {
384 $tokens[$first_digit_group_idx] .= '_000000000000000'
386 my $key = join("_", @tokens);
387 $key =~ s/[^\p{IsAlnum}_]//g;
391 $func$ LANGUAGE PLPERLU IMMUTABLE;
394 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
398 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
399 # The author hopes to upload it to CPAN some day, which would make our lives easier
400 use Library::CallNumber::LC;
402 my $callnum = Library::CallNumber::LC->new(shift);
403 return $callnum->normalize();
405 $func$ LANGUAGE PLPERLU IMMUTABLE;
407 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
408 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
409 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
410 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
413 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
415 NEW.label_sortkey := REGEXP_REPLACE(
416 evergreen.lpad_number_substrings(
417 naco_normalize(NEW.label),
429 CREATE TABLE asset.call_number_prefix (
430 id SERIAL PRIMARY KEY,
431 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
432 label TEXT NOT NULL, -- i18n
435 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
436 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
437 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
439 CREATE TABLE asset.call_number_suffix (
440 id SERIAL PRIMARY KEY,
441 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
442 label TEXT NOT NULL, -- i18n
445 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
446 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
447 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
449 CREATE TABLE asset.call_number (
450 id bigserial PRIMARY KEY,
451 creator BIGINT NOT NULL,
452 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
453 editor BIGINT NOT NULL,
454 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
455 record bigint NOT NULL,
456 owning_lib INT NOT NULL,
458 deleted BOOL NOT NULL DEFAULT FALSE,
459 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
460 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
461 label_class BIGINT NOT NULL
462 REFERENCES asset.call_number_class(id)
463 DEFERRABLE INITIALLY DEFERRED,
466 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
467 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
468 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
469 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
470 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
471 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
472 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;
473 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;
474 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;
475 CREATE TRIGGER asset_label_sortkey_trigger
476 BEFORE UPDATE OR INSERT ON asset.call_number
477 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
479 CREATE TABLE asset.uri_call_number_map (
480 id BIGSERIAL PRIMARY KEY,
481 uri INT NOT NULL REFERENCES asset.uri (id),
482 call_number INT NOT NULL REFERENCES asset.call_number (id),
483 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
485 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
487 CREATE TABLE asset.call_number_note (
488 id BIGSERIAL PRIMARY KEY,
489 call_number BIGINT NOT NULL,
490 creator BIGINT NOT NULL,
491 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
492 pub BOOL NOT NULL DEFAULT FALSE,
496 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
498 CREATE TABLE asset.copy_template (
499 id SERIAL PRIMARY KEY,
500 owning_lib INT NOT NULL
501 REFERENCES actor.org_unit (id)
502 DEFERRABLE INITIALLY DEFERRED,
503 creator BIGINT NOT NULL
504 REFERENCES actor.usr (id)
505 DEFERRABLE INITIALLY DEFERRED,
506 editor BIGINT NOT NULL
507 REFERENCES actor.usr (id)
508 DEFERRABLE INITIALLY DEFERRED,
509 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
510 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
512 -- columns above this point are attributes of the template itself
513 -- columns after this point are attributes of the copy this template modifies/creates
514 circ_lib INT REFERENCES actor.org_unit (id)
515 DEFERRABLE INITIALLY DEFERRED,
516 status INT REFERENCES config.copy_status (id)
517 DEFERRABLE INITIALLY DEFERRED,
518 location INT REFERENCES asset.copy_location (id)
519 DEFERRABLE INITIALLY DEFERRED,
520 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
521 loan_duration IS NULL OR loan_duration IN (1,2,3)),
522 fine_level INT CONSTRAINT valid_fine_level CHECK (
523 fine_level IS NULL OR loan_duration IN (1,2,3)),
529 deposit_amount NUMERIC(6,2),
539 CREATE TABLE asset.copy_vis_attr_cache (
540 id BIGSERIAL PRIMARY KEY,
541 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
542 target_copy BIGINT NOT NULL,
543 vis_attr_vector INT[]
545 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
546 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
548 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$
553 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;
555 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
557 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
558 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
559 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
563 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
569 asset.copy_vis_attr_cache av
570 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
571 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
575 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
582 $f$ LANGUAGE PLPGSQL;
584 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$
589 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;
591 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
593 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
594 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
595 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
599 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
604 asset.copy_vis_attr_cache av
605 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
606 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
610 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
617 $f$ LANGUAGE PLPGSQL;
619 CREATE OR REPLACE FUNCTION asset.staff_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$
624 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;
626 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
628 WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available)
632 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
633 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
637 actor.org_unit_descendants(ans.id) d
638 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
639 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
640 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
644 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
651 $f$ LANGUAGE PLPGSQL;
653 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$
658 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;
660 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
665 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
666 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
669 actor.org_unit_descendants(ans.id) d
670 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
671 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
672 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
676 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
683 $f$ LANGUAGE PLPGSQL;
685 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$
687 IF staff IS TRUE THEN
689 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
691 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
695 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
697 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
703 $f$ LANGUAGE PLPGSQL;
705 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
710 JOIN asset.call_number acn ON acp.call_number = acn.id
711 JOIN asset.copy_location acpl ON acp.location = acpl.id
712 JOIN config.copy_status ccs ON acp.status = ccs.id
715 AND acp.holdable = true
716 AND acpl.holdable = true
717 AND ccs.holdable = true
718 AND acp.deleted = false
719 AND acpl.deleted = false
720 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
727 $f$ LANGUAGE PLPGSQL;
729 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$
734 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;
736 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
738 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
739 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
740 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
744 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
750 asset.copy_vis_attr_cache av
751 JOIN asset.copy cp ON (cp.id = av.target_copy)
752 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
753 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
757 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
764 $f$ LANGUAGE PLPGSQL;
766 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$
771 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;
773 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
775 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
776 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
777 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
781 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
787 asset.copy_vis_attr_cache av
788 JOIN asset.copy cp ON (cp.id = av.target_copy)
789 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
790 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
794 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
801 $f$ LANGUAGE PLPGSQL;
803 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$
808 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;
810 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
815 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
819 actor.org_unit_descendants(ans.id) d
820 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
821 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
822 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
826 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
833 $f$ LANGUAGE PLPGSQL;
835 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$
840 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;
842 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
847 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
851 actor.org_unit_descendants(ans.id) d
852 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
853 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
854 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
858 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
865 $f$ LANGUAGE PLPGSQL;
867 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$
869 IF staff IS TRUE THEN
871 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
873 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
877 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
879 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
885 $f$ LANGUAGE PLPGSQL;
887 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
892 JOIN asset.call_number acn ON acp.call_number = acn.id
893 JOIN asset.copy_location acpl ON acp.location = acpl.id
894 JOIN config.copy_status ccs ON acp.status = ccs.id
895 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
897 mmsm.metarecord = rid
898 AND acp.holdable = true
899 AND acpl.holdable = true
900 AND ccs.holdable = true
901 AND acp.deleted = false
902 AND acpl.deleted = false
903 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
910 $f$ LANGUAGE PLPGSQL;
912 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
914 IF NEW.barcode LIKE '@@%' THEN
915 NEW.barcode := '@@' || NEW.id;
919 $f$ LANGUAGE PLPGSQL;
921 CREATE TRIGGER autogenerate_placeholder_barcode
922 BEFORE INSERT OR UPDATE ON asset.copy
923 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
925 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
929 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
930 PERFORM * FROM asset.copy WHERE id = copy_id;
932 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
936 $F$ LANGUAGE PLPGSQL;
938 CREATE TABLE asset.copy_tag (
939 id SERIAL PRIMARY KEY,
940 tag_type TEXT REFERENCES config.copy_tag_type (code)
941 ON UPDATE CASCADE ON DELETE CASCADE,
944 index_vector tsvector NOT NULL,
946 pub BOOLEAN DEFAULT TRUE,
947 owner INTEGER NOT NULL REFERENCES actor.org_unit (id)
950 CREATE INDEX asset_copy_tag_label_idx
951 ON asset.copy_tag (label);
952 CREATE INDEX asset_copy_tag_label_lower_idx
953 ON asset.copy_tag (evergreen.lowercase(label));
954 CREATE INDEX asset_copy_tag_index_vector_idx
956 USING GIN(index_vector);
957 CREATE INDEX asset_copy_tag_tag_type_idx
958 ON asset.copy_tag (tag_type);
959 CREATE INDEX asset_copy_tag_owner_idx
960 ON asset.copy_tag (owner);
962 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
964 IF NEW.value IS NULL THEN
965 NEW.value = NEW.label;
970 $$ LANGUAGE 'plpgsql';
972 -- name of following trigger chosen to ensure it runs first
973 CREATE TRIGGER asset_copy_tag_do_value
974 BEFORE INSERT OR UPDATE ON asset.copy_tag
975 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
976 CREATE TRIGGER asset_copy_tag_fti_trigger
977 BEFORE UPDATE OR INSERT ON asset.copy_tag
978 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
980 CREATE TABLE asset.copy_tag_copy_map (
981 id BIGSERIAL PRIMARY KEY,
983 tag INTEGER REFERENCES asset.copy_tag (id)
984 ON UPDATE CASCADE ON DELETE CASCADE
987 CREATE INDEX asset_copy_tag_copy_map_copy_idx
988 ON asset.copy_tag_copy_map (copy);
989 CREATE INDEX asset_copy_tag_copy_map_tag_idx
990 ON asset.copy_tag_copy_map (tag);
992 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
995 the_copy asset.copy%ROWTYPE;
998 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
999 IF NOT FOUND THEN RETURN NULL; END IF;
1001 IF the_copy.status = 3 THEN -- Lost
1003 ELSIF the_copy.status = 4 THEN -- Missing
1005 ELSIF the_copy.status = 14 THEN -- Damaged
1007 ELSIF the_copy.status = 17 THEN -- Lost and paid
1008 RETURN 'LOST_AND_PAID';
1011 SELECT stop_fines INTO last_circ_stop
1012 FROM action.circulation
1013 WHERE target_copy = cid AND checkin_time IS NULL
1014 ORDER BY xact_start DESC LIMIT 1;
1017 IF last_circ_stop IN (
1018 'CLAIMSNEVERCHECKEDOUT',
1022 RETURN last_circ_stop;
1028 $$ LANGUAGE PLPGSQL;
1030 CREATE TYPE config.copy_alert_type_state AS ENUM (
1038 'CLAIMSNEVERCHECKEDOUT'
1041 CREATE TYPE config.copy_alert_type_event AS ENUM (
1046 CREATE TABLE config.copy_alert_type (
1047 id serial primary key, -- reserve 1-100 for system
1048 scope_org int not null references actor.org_unit (id) on delete cascade,
1049 active bool not null default true,
1050 name text not null unique,
1051 state config.copy_alert_type_state,
1052 event config.copy_alert_type_event,
1054 invert_location bool not null default false,
1059 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1061 CREATE TABLE actor.copy_alert_suppress (
1062 id serial primary key,
1063 org int not null references actor.org_unit (id) on delete cascade,
1064 alert_type int not null references config.copy_alert_type (id) on delete cascade
1067 CREATE TABLE asset.copy_alert (
1068 id bigserial primary key,
1069 alert_type int not null references config.copy_alert_type (id) on delete cascade,
1070 copy bigint not null,
1071 temp bool not null default false,
1072 create_time timestamptz not null default now(),
1073 create_staff bigint not null references actor.usr (id) on delete set null,
1075 ack_time timestamptz,
1076 ack_staff bigint references actor.usr (id) on delete set null
1079 CREATE VIEW asset.active_copy_alert AS
1081 FROM asset.copy_alert
1082 WHERE ack_time IS NULL;