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 CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
38 CREATE TABLE asset.copy_location_order
40 id SERIAL PRIMARY KEY,
42 REFERENCES asset.copy_location
44 DEFERRABLE INITIALLY DEFERRED,
46 REFERENCES actor.org_unit
48 DEFERRABLE INITIALLY DEFERRED,
49 position INT NOT NULL DEFAULT 0,
50 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
53 CREATE TABLE asset.copy_location_group (
54 id SERIAL PRIMARY KEY,
55 name TEXT NOT NULL, -- i18n
56 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
57 pos INT NOT NULL DEFAULT 0,
58 top BOOL NOT NULL DEFAULT FALSE,
59 opac_visible BOOL NOT NULL DEFAULT TRUE,
60 CONSTRAINT lgroup_once_per_owner UNIQUE (owner,name)
63 CREATE TABLE asset.copy_location_group_map (
64 id SERIAL PRIMARY KEY,
65 location INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
66 lgroup INT NOT NULL REFERENCES asset.copy_location_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
67 CONSTRAINT lgroup_once_per_group UNIQUE (lgroup,location)
71 CREATE TABLE asset.copy (
72 id BIGSERIAL PRIMARY KEY,
73 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
74 creator BIGINT NOT NULL,
75 call_number BIGINT NOT NULL,
76 editor BIGINT NOT NULL,
77 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
78 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
80 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
81 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
82 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
83 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
85 circulate BOOL NOT NULL DEFAULT TRUE,
86 deposit BOOL NOT NULL DEFAULT FALSE,
87 ref BOOL NOT NULL DEFAULT FALSE,
88 holdable BOOL NOT NULL DEFAULT TRUE,
89 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
91 barcode TEXT NOT NULL,
97 opac_visible BOOL NOT NULL DEFAULT TRUE,
98 deleted BOOL NOT NULL DEFAULT FALSE,
101 status_changed_time TIMESTAMP WITH TIME ZONE,
102 active_date TIMESTAMP WITH TIME ZONE,
103 mint_condition BOOL NOT NULL DEFAULT TRUE,
106 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
107 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
108 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
109 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
110 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
111 CREATE INDEX cp_create_date ON asset.copy (create_date);
112 CREATE INDEX cp_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7);
113 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;
115 CREATE TABLE asset.copy_part_map (
116 id SERIAL PRIMARY KEY,
117 target_copy BIGINT NOT NULL, -- points o asset.copy
118 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
120 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
122 CREATE TABLE asset.opac_visible_copies (
123 id BIGSERIAL primary key,
124 copy_id BIGINT, -- copy id
128 COMMENT ON TABLE asset.opac_visible_copies IS $$
129 Materialized view of copies that are visible in the OPAC, used by
130 search.query_parser_fts() to speed up OPAC visibility checks on large
131 databases. Contents are maintained by a set of triggers.
133 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
134 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
135 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
137 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
138 RETURNS TRIGGER AS $$
140 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
141 NEW.status_changed_time := now();
142 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
143 NEW.active_date := now();
150 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
151 CREATE OR REPLACE FUNCTION asset.acp_created()
152 RETURNS TRIGGER AS $$
154 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
155 NEW.active_date := now();
157 IF NEW.status_changed_time IS NULL THEN
158 NEW.status_changed_time := now();
164 CREATE TRIGGER acp_status_changed_trig
165 BEFORE UPDATE ON asset.copy
166 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
168 CREATE TRIGGER acp_created_trig
169 BEFORE INSERT ON asset.copy
170 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
172 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
173 RETURNS TRIGGER AS $$
175 new_copy_location INT;
177 IF (TG_OP = 'UPDATE') THEN
178 IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number THEN
182 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 name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
183 IF new_copy_location IS NOT NULL THEN
184 NEW.location = new_copy_location;
190 CREATE TRIGGER acp_location_fixer_trig
191 BEFORE INSERT OR UPDATE ON asset.copy
192 FOR EACH ROW EXECUTE PROCEDURE asset.acp_location_fixer();
194 CREATE TABLE asset.stat_cat_sip_fields (
195 field CHAR(2) PRIMARY KEY,
197 one_only BOOL NOT NULL DEFAULT FALSE
199 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
200 Asset Statistical Category SIP Fields
202 Contains the list of valid SIP Field identifiers for
203 Statistical Categories.
206 CREATE TABLE asset.stat_cat_entry_transparency_map (
207 id BIGSERIAL PRIMARY KEY,
208 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
209 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
210 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
211 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
214 CREATE TABLE asset.stat_cat (
215 id SERIAL PRIMARY KEY,
217 opac_visible BOOL NOT NULL DEFAULT FALSE,
219 required BOOL NOT NULL DEFAULT FALSE,
220 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
222 checkout_archive BOOL NOT NULL DEFAULT FALSE,
223 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
226 CREATE TABLE asset.stat_cat_entry (
227 id SERIAL PRIMARY KEY,
228 stat_cat INT NOT NULL,
231 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
234 CREATE TABLE asset.stat_cat_entry_copy_map (
235 id BIGSERIAL PRIMARY KEY,
236 stat_cat INT NOT NULL,
237 stat_cat_entry INT NOT NULL,
238 owning_copy BIGINT NOT NULL,
239 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
241 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
243 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
245 sipfield asset.stat_cat_sip_fields%ROWTYPE;
248 IF NEW.sip_field IS NOT NULL THEN
249 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
250 IF sipfield.one_only THEN
251 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
252 IF use_count > 0 THEN
253 RAISE EXCEPTION 'Sip field cannot be used twice';
259 $func$ LANGUAGE PLPGSQL;
261 CREATE TRIGGER asset_stat_cat_sip_update_trigger
262 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
263 EXECUTE PROCEDURE asset.stat_cat_check();
265 CREATE TABLE asset.copy_note (
266 id BIGSERIAL PRIMARY KEY,
267 owning_copy BIGINT NOT NULL,
268 creator BIGINT NOT NULL,
269 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
270 pub BOOL NOT NULL DEFAULT FALSE,
274 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
275 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
277 CREATE TABLE asset.uri (
278 id SERIAL PRIMARY KEY,
281 use_restriction TEXT,
282 active BOOL NOT NULL DEFAULT TRUE
285 CREATE TABLE asset.call_number_class (
286 id bigserial PRIMARY KEY,
288 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
289 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
291 COMMENT ON TABLE asset.call_number_class IS $$
292 Defines the call number normalization database functions in the "normalizer"
293 column and the tag/subfield combinations to use to lookup the call number in
294 the "field" column for a given classification scheme. Tag/subfield combinations
295 are delimited by commas.
298 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
302 sortkey := NEW.label_sortkey;
304 IF NEW.label_class IS NULL THEN
305 NEW.label_class := COALESCE(
307 SELECT substring(value from E'\\d+')::integer
308 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
313 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
314 quote_literal( NEW.label ) || ')'
315 FROM asset.call_number_class acnc
316 WHERE acnc.id = NEW.label_class
318 NEW.label_sortkey = sortkey;
321 $func$ LANGUAGE PLPGSQL;
323 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
324 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
325 # thus could probably be considered a derived work, although nothing was
326 # directly copied - but to err on the safe side of providing attribution:
327 # Copyright (C) 2007 LibLime
328 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
329 # Licensed under the GPL v2 or later
334 # Converts the callnumber to uppercase
335 # Strips spaces from start and end of the call number
336 # Converts anything other than letters, digits, and periods into spaces
337 # Collapses multiple spaces into a single underscore
338 my $callnum = uc(shift);
339 $callnum =~ s/^\s//g;
340 $callnum =~ s/\s$//g;
341 # NOTE: this previously used underscores, but this caused sorting issues
342 # for the "before" half of page 0 on CN browse, sorting CNs containing a
343 # decimal before "whole number" CNs
344 $callnum =~ s/[^A-Z0-9_.]/ /g;
345 $callnum =~ s/ {2,}/ /g;
348 $func$ LANGUAGE PLPERLU;
350 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
351 # Derived from the Koha C4::ClassSortRoutine::Dewey module
352 # Copyright (C) 2007 LibLime
353 # Licensed under the GPL v2 or later
358 my $init = uc(shift);
362 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
363 my @tokens = split /\.|\s+/, $init;
364 my $digit_group_count = 0;
365 my $first_digit_group_idx;
366 for (my $i = 0; $i <= $#tokens; $i++) {
367 if ($tokens[$i] =~ /^\d+$/) {
368 $digit_group_count++;
369 if ($digit_group_count == 1) {
370 $first_digit_group_idx = $i;
372 if (2 == $digit_group_count) {
373 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
374 $tokens[$i] =~ tr/ /0/;
378 # Pad the first digit_group if there was only one
379 if (1 == $digit_group_count) {
380 $tokens[$first_digit_group_idx] .= '_000000000000000'
382 my $key = join("_", @tokens);
383 $key =~ s/[^\p{IsAlnum}_]//g;
387 $func$ LANGUAGE PLPERLU;
390 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
394 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
395 # The author hopes to upload it to CPAN some day, which would make our lives easier
396 use Library::CallNumber::LC;
398 my $callnum = Library::CallNumber::LC->new(shift);
399 return $callnum->normalize();
401 $func$ LANGUAGE PLPERLU;
403 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
404 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
405 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
406 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
409 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
411 NEW.label_sortkey := REGEXP_REPLACE(
412 evergreen.lpad_number_substrings(
413 naco_normalize(NEW.label),
425 CREATE TABLE asset.call_number_prefix (
426 id SERIAL PRIMARY KEY,
427 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
428 label TEXT NOT NULL, -- i18n
431 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
432 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
433 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
435 CREATE TABLE asset.call_number_suffix (
436 id SERIAL PRIMARY KEY,
437 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
438 label TEXT NOT NULL, -- i18n
441 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
442 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
443 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
445 CREATE TABLE asset.call_number (
446 id bigserial PRIMARY KEY,
447 creator BIGINT NOT NULL,
448 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
449 editor BIGINT NOT NULL,
450 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
451 record bigint NOT NULL,
452 owning_lib INT NOT NULL,
454 deleted BOOL NOT NULL DEFAULT FALSE,
455 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
456 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
457 label_class BIGINT NOT NULL
458 REFERENCES asset.call_number_class(id)
459 DEFERRABLE INITIALLY DEFERRED,
462 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
463 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
464 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
465 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
466 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
467 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
468 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;
469 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;
470 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;
471 CREATE TRIGGER asset_label_sortkey_trigger
472 BEFORE UPDATE OR INSERT ON asset.call_number
473 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
475 CREATE TABLE asset.uri_call_number_map (
476 id BIGSERIAL PRIMARY KEY,
477 uri INT NOT NULL REFERENCES asset.uri (id),
478 call_number INT NOT NULL REFERENCES asset.call_number (id),
479 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
481 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
483 CREATE TABLE asset.call_number_note (
484 id BIGSERIAL PRIMARY KEY,
485 call_number BIGINT NOT NULL,
486 creator BIGINT NOT NULL,
487 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
488 pub BOOL NOT NULL DEFAULT FALSE,
492 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
494 CREATE TABLE asset.copy_template (
495 id SERIAL PRIMARY KEY,
496 owning_lib INT NOT NULL
497 REFERENCES actor.org_unit (id)
498 DEFERRABLE INITIALLY DEFERRED,
499 creator BIGINT NOT NULL
500 REFERENCES actor.usr (id)
501 DEFERRABLE INITIALLY DEFERRED,
502 editor BIGINT NOT NULL
503 REFERENCES actor.usr (id)
504 DEFERRABLE INITIALLY DEFERRED,
505 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
506 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
508 -- columns above this point are attributes of the template itself
509 -- columns after this point are attributes of the copy this template modifies/creates
510 circ_lib INT REFERENCES actor.org_unit (id)
511 DEFERRABLE INITIALLY DEFERRED,
512 status INT REFERENCES config.copy_status (id)
513 DEFERRABLE INITIALLY DEFERRED,
514 location INT REFERENCES asset.copy_location (id)
515 DEFERRABLE INITIALLY DEFERRED,
516 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
517 loan_duration IS NULL OR loan_duration IN (1,2,3)),
518 fine_level INT CONSTRAINT valid_fine_level CHECK (
519 fine_level IS NULL OR loan_duration IN (1,2,3)),
525 deposit_amount NUMERIC(6,2),
535 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$
540 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;
542 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
547 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
551 actor.org_unit_descendants(ans.id) d
552 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
553 JOIN asset.copy cp ON (cp.id = av.copy_id)
557 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
564 $f$ LANGUAGE PLPGSQL;
566 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$
571 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;
573 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
578 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
582 actor.org_unit_descendants(ans.id) d
583 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
584 JOIN asset.copy cp ON (cp.id = av.copy_id)
588 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
595 $f$ LANGUAGE PLPGSQL;
597 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$
602 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;
604 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
609 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
610 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
613 actor.org_unit_descendants(ans.id) d
614 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
615 JOIN asset.copy_location cl ON (cp.location = cl.id)
616 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
620 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
627 $f$ LANGUAGE PLPGSQL;
629 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$
634 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;
636 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
641 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
642 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
645 actor.org_unit_descendants(ans.id) d
646 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
647 JOIN asset.copy_location cl ON (cp.location = cl.id)
648 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
652 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
659 $f$ LANGUAGE PLPGSQL;
661 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$
663 IF staff IS TRUE THEN
665 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
667 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
671 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
673 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
679 $f$ LANGUAGE PLPGSQL;
681 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
686 JOIN asset.call_number acn ON acp.call_number = acn.id
687 JOIN asset.copy_location acpl ON acp.location = acpl.id
688 JOIN config.copy_status ccs ON acp.status = ccs.id
691 AND acp.holdable = true
692 AND acpl.holdable = true
693 AND ccs.holdable = true
694 AND acp.deleted = false
695 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
702 $f$ LANGUAGE PLPGSQL;
704 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$
709 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;
711 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
716 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
720 actor.org_unit_descendants(ans.id) d
721 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
722 JOIN asset.copy cp ON (cp.id = av.copy_id)
723 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
727 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
734 $f$ LANGUAGE PLPGSQL;
736 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$
741 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;
743 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
748 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
752 actor.org_unit_descendants(ans.id) d
753 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
754 JOIN asset.copy cp ON (cp.id = av.copy_id)
755 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
759 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
766 $f$ LANGUAGE PLPGSQL;
768 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$
773 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;
775 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
780 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
784 actor.org_unit_descendants(ans.id) d
785 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
786 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
787 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
791 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
798 $f$ LANGUAGE PLPGSQL;
800 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$
805 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;
807 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
812 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
816 actor.org_unit_descendants(ans.id) d
817 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
818 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
819 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
823 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
830 $f$ LANGUAGE PLPGSQL;
832 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$
834 IF staff IS TRUE THEN
836 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
838 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
842 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
844 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
850 $f$ LANGUAGE PLPGSQL;
852 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
857 JOIN asset.call_number acn ON acp.call_number = acn.id
858 JOIN asset.copy_location acpl ON acp.location = acpl.id
859 JOIN config.copy_status ccs ON acp.status = ccs.id
860 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
862 mmsm.metarecord = rid
863 AND acp.holdable = true
864 AND acpl.holdable = true
865 AND ccs.holdable = true
866 AND acp.deleted = false
867 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
874 $f$ LANGUAGE PLPGSQL;
876 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
878 IF NEW.barcode LIKE '@@%' THEN
879 NEW.barcode := '@@' || NEW.id;
883 $f$ LANGUAGE PLPGSQL;
885 CREATE TRIGGER autogenerate_placeholder_barcode
886 BEFORE INSERT OR UPDATE ON asset.copy
887 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
889 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
893 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
894 PERFORM * FROM asset.copy WHERE id = copy_id;
896 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
900 $F$ LANGUAGE PLPGSQL;