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
631 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
632 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
635 actor.org_unit_descendants(ans.id) d
636 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
637 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
638 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
642 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
649 $f$ LANGUAGE PLPGSQL;
651 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$
656 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;
658 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
663 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
664 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
667 actor.org_unit_descendants(ans.id) d
668 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
669 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
670 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
674 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
681 $f$ LANGUAGE PLPGSQL;
683 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$
685 IF staff IS TRUE THEN
687 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
689 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
693 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
695 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
701 $f$ LANGUAGE PLPGSQL;
703 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
708 JOIN asset.call_number acn ON acp.call_number = acn.id
709 JOIN asset.copy_location acpl ON acp.location = acpl.id
710 JOIN config.copy_status ccs ON acp.status = ccs.id
713 AND acp.holdable = true
714 AND acpl.holdable = true
715 AND ccs.holdable = true
716 AND acp.deleted = false
717 AND acpl.deleted = false
718 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
725 $f$ LANGUAGE PLPGSQL;
727 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$
732 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;
734 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
736 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
737 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
738 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
742 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
748 asset.copy_vis_attr_cache av
749 JOIN asset.copy cp ON (cp.id = av.target_copy)
750 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
751 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
755 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
762 $f$ LANGUAGE PLPGSQL;
764 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$
769 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;
771 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
773 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
774 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
775 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
779 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
785 asset.copy_vis_attr_cache av
786 JOIN asset.copy cp ON (cp.id = av.target_copy)
787 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
788 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
792 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
799 $f$ LANGUAGE PLPGSQL;
801 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$
806 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;
808 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
813 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
817 actor.org_unit_descendants(ans.id) d
818 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
819 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
820 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
824 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
831 $f$ LANGUAGE PLPGSQL;
833 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$
838 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;
840 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
845 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
849 actor.org_unit_descendants(ans.id) d
850 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
851 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
852 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
856 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
863 $f$ LANGUAGE PLPGSQL;
865 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$
867 IF staff IS TRUE THEN
869 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
871 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
875 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
877 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
883 $f$ LANGUAGE PLPGSQL;
885 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
890 JOIN asset.call_number acn ON acp.call_number = acn.id
891 JOIN asset.copy_location acpl ON acp.location = acpl.id
892 JOIN config.copy_status ccs ON acp.status = ccs.id
893 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
895 mmsm.metarecord = rid
896 AND acp.holdable = true
897 AND acpl.holdable = true
898 AND ccs.holdable = true
899 AND acp.deleted = false
900 AND acpl.deleted = false
901 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
908 $f$ LANGUAGE PLPGSQL;
910 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
912 IF NEW.barcode LIKE '@@%' THEN
913 NEW.barcode := '@@' || NEW.id;
917 $f$ LANGUAGE PLPGSQL;
919 CREATE TRIGGER autogenerate_placeholder_barcode
920 BEFORE INSERT OR UPDATE ON asset.copy
921 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
923 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
927 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
928 PERFORM * FROM asset.copy WHERE id = copy_id;
930 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
934 $F$ LANGUAGE PLPGSQL;
936 CREATE TABLE asset.copy_tag (
937 id SERIAL PRIMARY KEY,
938 tag_type TEXT REFERENCES config.copy_tag_type (code)
939 ON UPDATE CASCADE ON DELETE CASCADE,
942 index_vector tsvector NOT NULL,
944 pub BOOLEAN DEFAULT TRUE,
945 owner INTEGER NOT NULL REFERENCES actor.org_unit (id)
948 CREATE INDEX asset_copy_tag_label_idx
949 ON asset.copy_tag (label);
950 CREATE INDEX asset_copy_tag_label_lower_idx
951 ON asset.copy_tag (evergreen.lowercase(label));
952 CREATE INDEX asset_copy_tag_index_vector_idx
954 USING GIN(index_vector);
955 CREATE INDEX asset_copy_tag_tag_type_idx
956 ON asset.copy_tag (tag_type);
957 CREATE INDEX asset_copy_tag_owner_idx
958 ON asset.copy_tag (owner);
960 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
962 IF NEW.value IS NULL THEN
963 NEW.value = NEW.label;
968 $$ LANGUAGE 'plpgsql';
970 -- name of following trigger chosen to ensure it runs first
971 CREATE TRIGGER asset_copy_tag_do_value
972 BEFORE INSERT OR UPDATE ON asset.copy_tag
973 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
974 CREATE TRIGGER asset_copy_tag_fti_trigger
975 BEFORE UPDATE OR INSERT ON asset.copy_tag
976 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
978 CREATE TABLE asset.copy_tag_copy_map (
979 id BIGSERIAL PRIMARY KEY,
981 tag INTEGER REFERENCES asset.copy_tag (id)
982 ON UPDATE CASCADE ON DELETE CASCADE
985 CREATE INDEX asset_copy_tag_copy_map_copy_idx
986 ON asset.copy_tag_copy_map (copy);
987 CREATE INDEX asset_copy_tag_copy_map_tag_idx
988 ON asset.copy_tag_copy_map (tag);
990 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
993 the_copy asset.copy%ROWTYPE;
996 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
997 IF NOT FOUND THEN RETURN NULL; END IF;
999 IF the_copy.status = 3 THEN -- Lost
1001 ELSIF the_copy.status = 4 THEN -- Missing
1003 ELSIF the_copy.status = 14 THEN -- Damaged
1005 ELSIF the_copy.status = 17 THEN -- Lost and paid
1006 RETURN 'LOST_AND_PAID';
1009 SELECT stop_fines INTO last_circ_stop
1010 FROM action.circulation
1011 WHERE target_copy = cid
1012 ORDER BY xact_start DESC LIMIT 1;
1015 IF last_circ_stop IN (
1016 'CLAIMSNEVERCHECKEDOUT',
1020 RETURN last_circ_stop;
1026 $$ LANGUAGE PLPGSQL;
1028 CREATE TYPE config.copy_alert_type_state AS ENUM (
1036 'CLAIMSNEVERCHECKEDOUT'
1039 CREATE TYPE config.copy_alert_type_event AS ENUM (
1044 CREATE TABLE config.copy_alert_type (
1045 id serial primary key, -- reserve 1-100 for system
1046 scope_org int not null references actor.org_unit (id) on delete cascade,
1047 active bool not null default true,
1048 name text not null unique,
1049 state config.copy_alert_type_state,
1050 event config.copy_alert_type_event,
1052 invert_location bool not null default false,
1057 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1059 CREATE TABLE actor.copy_alert_suppress (
1060 id serial primary key,
1061 org int not null references actor.org_unit (id) on delete cascade,
1062 alert_type int not null references config.copy_alert_type (id) on delete cascade
1065 CREATE TABLE asset.copy_alert (
1066 id bigserial primary key,
1067 alert_type int not null references config.copy_alert_type (id) on delete cascade,
1068 copy bigint not null,
1069 temp bool not null default false,
1070 create_time timestamptz not null default now(),
1071 create_staff bigint not null references actor.usr (id) on delete set null,
1073 ack_time timestamptz,
1074 ack_staff bigint references actor.usr (id) on delete set null
1077 CREATE VIEW asset.active_copy_alert AS
1079 FROM asset.copy_alert
1080 WHERE ack_time IS NULL;