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 JOIN asset.call_number cn ON (cp.call_number = cn.id AND not cn.deleted)
572 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
576 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
583 $f$ LANGUAGE PLPGSQL;
585 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$
590 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;
592 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
594 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
595 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
596 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
600 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
605 asset.copy_vis_attr_cache av
606 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
607 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
611 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
618 $f$ LANGUAGE PLPGSQL;
620 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count(org integer, rid bigint)
621 RETURNS TABLE(depth integer, org_unit integer, visible bigint, available bigint, unshadow bigint, transcendant integer)
628 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;
630 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
632 WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
635 (cp.status = ANY (available_statuses.ids))::INT as available,
636 (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
639 actor.org_unit_descendants(ans.id) d
640 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
641 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
642 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
646 (cp.status = ANY (available_statuses.ids))::INT as available,
647 (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
650 actor.org_unit_descendants(ans.id) d
651 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
652 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
653 JOIN biblio.peer_bib_copy_map bp ON (bp.peer_record = rid AND bp.target_copy = cp.id)
655 select ans.depth, ans.id, count(id), sum(x.available::int), sum(x.opac_visible::int), trans
656 from ((select * from cp) union (select * from peer)) x
660 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
668 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$
673 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;
675 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
680 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
681 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
684 actor.org_unit_descendants(ans.id) d
685 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
686 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
687 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
691 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
698 $f$ LANGUAGE PLPGSQL;
700 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$
702 IF staff IS TRUE THEN
704 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
706 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
710 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
712 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
718 $f$ LANGUAGE PLPGSQL;
720 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
725 JOIN asset.call_number acn ON acp.call_number = acn.id
726 JOIN asset.copy_location acpl ON acp.location = acpl.id
727 JOIN config.copy_status ccs ON acp.status = ccs.id
730 AND acp.holdable = true
731 AND acpl.holdable = true
732 AND ccs.holdable = true
733 AND acp.deleted = false
734 AND acpl.deleted = false
735 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
742 $f$ LANGUAGE PLPGSQL;
744 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$
749 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;
751 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
753 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
754 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
755 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
759 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
765 asset.copy_vis_attr_cache av
766 JOIN asset.copy cp ON (cp.id = av.target_copy)
767 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
768 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
772 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
779 $f$ LANGUAGE PLPGSQL;
781 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$
786 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;
788 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
790 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
791 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
792 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
796 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
802 asset.copy_vis_attr_cache av
803 JOIN asset.copy cp ON (cp.id = av.target_copy)
804 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
805 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
809 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
816 $f$ LANGUAGE PLPGSQL;
818 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$
823 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;
825 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
830 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
834 actor.org_unit_descendants(ans.id) d
835 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
836 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
837 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
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_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$
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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
862 SUM( CASE WHEN cp.status IN (0,7,12) 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.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$
884 IF staff IS TRUE THEN
886 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
888 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
892 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
894 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
900 $f$ LANGUAGE PLPGSQL;
902 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
907 JOIN asset.call_number acn ON acp.call_number = acn.id
908 JOIN asset.copy_location acpl ON acp.location = acpl.id
909 JOIN config.copy_status ccs ON acp.status = ccs.id
910 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
912 mmsm.metarecord = rid
913 AND acp.holdable = true
914 AND acpl.holdable = true
915 AND ccs.holdable = true
916 AND acp.deleted = false
917 AND acpl.deleted = false
918 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
925 $f$ LANGUAGE PLPGSQL;
927 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
929 IF NEW.barcode LIKE '@@%' THEN
930 NEW.barcode := '@@' || NEW.id;
934 $f$ LANGUAGE PLPGSQL;
936 CREATE TRIGGER autogenerate_placeholder_barcode
937 BEFORE INSERT OR UPDATE ON asset.copy
938 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
940 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
944 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
945 PERFORM * FROM asset.copy WHERE id = copy_id;
947 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
951 $F$ LANGUAGE PLPGSQL;
953 CREATE TABLE asset.copy_tag (
954 id SERIAL PRIMARY KEY,
955 tag_type TEXT REFERENCES config.copy_tag_type (code)
956 ON UPDATE CASCADE ON DELETE CASCADE,
959 index_vector tsvector NOT NULL,
961 pub BOOLEAN DEFAULT TRUE,
962 owner INTEGER NOT NULL REFERENCES actor.org_unit (id)
965 CREATE INDEX asset_copy_tag_label_idx
966 ON asset.copy_tag (label);
967 CREATE INDEX asset_copy_tag_label_lower_idx
968 ON asset.copy_tag (evergreen.lowercase(label));
969 CREATE INDEX asset_copy_tag_index_vector_idx
971 USING GIN(index_vector);
972 CREATE INDEX asset_copy_tag_tag_type_idx
973 ON asset.copy_tag (tag_type);
974 CREATE INDEX asset_copy_tag_owner_idx
975 ON asset.copy_tag (owner);
977 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
979 IF NEW.value IS NULL THEN
980 NEW.value = NEW.label;
985 $$ LANGUAGE 'plpgsql';
987 -- name of following trigger chosen to ensure it runs first
988 CREATE TRIGGER asset_copy_tag_do_value
989 BEFORE INSERT OR UPDATE ON asset.copy_tag
990 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
991 CREATE TRIGGER asset_copy_tag_fti_trigger
992 BEFORE UPDATE OR INSERT ON asset.copy_tag
993 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
995 CREATE TABLE asset.copy_tag_copy_map (
996 id BIGSERIAL PRIMARY KEY,
998 tag INTEGER REFERENCES asset.copy_tag (id)
999 ON UPDATE CASCADE ON DELETE CASCADE
1002 CREATE INDEX asset_copy_tag_copy_map_copy_idx
1003 ON asset.copy_tag_copy_map (copy);
1004 CREATE INDEX asset_copy_tag_copy_map_tag_idx
1005 ON asset.copy_tag_copy_map (tag);
1007 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
1009 last_circ_stop TEXT;
1010 the_copy asset.copy%ROWTYPE;
1013 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
1014 IF NOT FOUND THEN RETURN NULL; END IF;
1016 IF the_copy.status = 3 THEN -- Lost
1018 ELSIF the_copy.status = 4 THEN -- Missing
1020 ELSIF the_copy.status = 14 THEN -- Damaged
1022 ELSIF the_copy.status = 17 THEN -- Lost and paid
1023 RETURN 'LOST_AND_PAID';
1026 SELECT stop_fines INTO last_circ_stop
1027 FROM action.circulation
1028 WHERE target_copy = cid AND checkin_time IS NULL
1029 ORDER BY xact_start DESC LIMIT 1;
1032 IF last_circ_stop IN (
1033 'CLAIMSNEVERCHECKEDOUT',
1037 RETURN last_circ_stop;
1043 $$ LANGUAGE PLPGSQL;
1045 CREATE TYPE config.copy_alert_type_state AS ENUM (
1053 'CLAIMSNEVERCHECKEDOUT'
1056 CREATE TYPE config.copy_alert_type_event AS ENUM (
1061 CREATE TABLE config.copy_alert_type (
1062 id serial primary key, -- reserve 1-100 for system
1063 scope_org int not null references actor.org_unit (id) on delete cascade,
1064 active bool not null default true,
1065 name text not null unique,
1066 state config.copy_alert_type_state,
1067 event config.copy_alert_type_event,
1069 invert_location bool not null default false,
1074 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1076 CREATE TABLE actor.copy_alert_suppress (
1077 id serial primary key,
1078 org int not null references actor.org_unit (id) on delete cascade,
1079 alert_type int not null references config.copy_alert_type (id) on delete cascade
1082 CREATE TABLE asset.copy_alert (
1083 id bigserial primary key,
1084 alert_type int not null references config.copy_alert_type (id) on delete cascade,
1085 copy bigint not null,
1086 temp bool not null default false,
1087 create_time timestamptz not null default now(),
1088 create_staff bigint not null references actor.usr (id) on delete set null,
1090 ack_time timestamptz,
1091 ack_staff bigint references actor.usr (id) on delete set null
1094 CREATE VIEW asset.active_copy_alert AS
1096 FROM asset.copy_alert
1097 WHERE ack_time IS NULL;