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 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$
544 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;
546 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
551 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
555 actor.org_unit_descendants(ans.id) d
556 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
557 JOIN asset.copy cp ON (cp.id = av.copy_id)
561 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
568 $f$ LANGUAGE PLPGSQL;
570 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$
575 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;
577 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
582 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
586 actor.org_unit_descendants(ans.id) d
587 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
588 JOIN asset.copy cp ON (cp.id = av.copy_id)
592 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
599 $f$ LANGUAGE PLPGSQL;
601 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$
606 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;
608 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
613 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
614 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
617 actor.org_unit_descendants(ans.id) d
618 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
619 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
620 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
624 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
631 $f$ LANGUAGE PLPGSQL;
633 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$
638 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;
640 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
645 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
646 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
649 actor.org_unit_descendants(ans.id) d
650 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
651 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
652 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
656 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
663 $f$ LANGUAGE PLPGSQL;
665 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$
667 IF staff IS TRUE THEN
669 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
671 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
675 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
677 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
683 $f$ LANGUAGE PLPGSQL;
685 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
690 JOIN asset.call_number acn ON acp.call_number = acn.id
691 JOIN asset.copy_location acpl ON acp.location = acpl.id
692 JOIN config.copy_status ccs ON acp.status = ccs.id
695 AND acp.holdable = true
696 AND acpl.holdable = true
697 AND ccs.holdable = true
698 AND acp.deleted = false
699 AND acpl.deleted = false
700 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
707 $f$ LANGUAGE PLPGSQL;
709 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$
714 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;
716 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
721 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
725 actor.org_unit_descendants(ans.id) d
726 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
727 JOIN asset.copy cp ON (cp.id = av.copy_id)
728 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
732 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
739 $f$ LANGUAGE PLPGSQL;
741 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$
746 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;
748 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
753 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
757 actor.org_unit_descendants(ans.id) d
758 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
759 JOIN asset.copy cp ON (cp.id = av.copy_id)
760 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
764 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
771 $f$ LANGUAGE PLPGSQL;
773 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$
778 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;
780 FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
785 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
789 actor.org_unit_descendants(ans.id) d
790 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
791 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
792 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
796 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
803 $f$ LANGUAGE PLPGSQL;
805 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$
810 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;
812 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
817 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
821 actor.org_unit_descendants(ans.id) d
822 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
823 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
824 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
828 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
835 $f$ LANGUAGE PLPGSQL;
837 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$
839 IF staff IS TRUE THEN
841 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
843 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
847 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
849 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
855 $f$ LANGUAGE PLPGSQL;
857 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
862 JOIN asset.call_number acn ON acp.call_number = acn.id
863 JOIN asset.copy_location acpl ON acp.location = acpl.id
864 JOIN config.copy_status ccs ON acp.status = ccs.id
865 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
867 mmsm.metarecord = rid
868 AND acp.holdable = true
869 AND acpl.holdable = true
870 AND ccs.holdable = true
871 AND acp.deleted = false
872 AND acpl.deleted = false
873 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
880 $f$ LANGUAGE PLPGSQL;
882 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
884 IF NEW.barcode LIKE '@@%' THEN
885 NEW.barcode := '@@' || NEW.id;
889 $f$ LANGUAGE PLPGSQL;
891 CREATE TRIGGER autogenerate_placeholder_barcode
892 BEFORE INSERT OR UPDATE ON asset.copy
893 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
895 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
899 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
900 PERFORM * FROM asset.copy WHERE id = copy_id;
902 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
906 $F$ LANGUAGE PLPGSQL;