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,
99 floating 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 staged search 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 TABLE asset.stat_cat_sip_fields (
173 field CHAR(2) PRIMARY KEY,
175 one_only BOOL NOT NULL DEFAULT FALSE
177 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
178 Asset Statistical Category SIP Fields
180 Contains the list of valid SIP Field identifiers for
181 Statistical Categories.
184 CREATE TABLE asset.stat_cat_entry_transparency_map (
185 id BIGSERIAL PRIMARY KEY,
186 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
187 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
188 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
189 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
192 CREATE TABLE asset.stat_cat (
193 id SERIAL PRIMARY KEY,
195 opac_visible BOOL NOT NULL DEFAULT FALSE,
197 required BOOL NOT NULL DEFAULT FALSE,
198 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
200 checkout_archive BOOL NOT NULL DEFAULT FALSE,
201 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
204 CREATE TABLE asset.stat_cat_entry (
205 id SERIAL PRIMARY KEY,
206 stat_cat INT NOT NULL,
209 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
212 CREATE TABLE asset.stat_cat_entry_copy_map (
213 id BIGSERIAL PRIMARY KEY,
214 stat_cat INT NOT NULL,
215 stat_cat_entry INT NOT NULL,
216 owning_copy BIGINT NOT NULL,
217 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
219 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
221 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
223 sipfield asset.stat_cat_sip_fields%ROWTYPE;
226 IF NEW.sip_field IS NOT NULL THEN
227 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
228 IF sipfield.one_only THEN
229 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
230 IF use_count > 0 THEN
231 RAISE EXCEPTION 'Sip field cannot be used twice';
237 $func$ LANGUAGE PLPGSQL;
239 CREATE TRIGGER asset_stat_cat_sip_update_trigger
240 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
241 EXECUTE PROCEDURE asset.stat_cat_check();
243 CREATE TABLE asset.copy_note (
244 id BIGSERIAL PRIMARY KEY,
245 owning_copy BIGINT NOT NULL,
246 creator BIGINT NOT NULL,
247 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
248 pub BOOL NOT NULL DEFAULT FALSE,
252 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
253 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
255 CREATE TABLE asset.uri (
256 id SERIAL PRIMARY KEY,
259 use_restriction TEXT,
260 active BOOL NOT NULL DEFAULT TRUE
263 CREATE TABLE asset.call_number_class (
264 id bigserial PRIMARY KEY,
266 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
267 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
269 COMMENT ON TABLE asset.call_number_class IS $$
270 Defines the call number normalization database functions in the "normalizer"
271 column and the tag/subfield combinations to use to lookup the call number in
272 the "field" column for a given classification scheme. Tag/subfield combinations
273 are delimited by commas.
276 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
280 sortkey := NEW.label_sortkey;
282 IF NEW.label_class IS NULL THEN
283 NEW.label_class := COALESCE(
285 SELECT substring(value from E'\\d+')::integer
286 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
291 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
292 quote_literal( NEW.label ) || ')'
293 FROM asset.call_number_class acnc
294 WHERE acnc.id = NEW.label_class
296 NEW.label_sortkey = sortkey;
299 $func$ LANGUAGE PLPGSQL;
301 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
302 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
303 # thus could probably be considered a derived work, although nothing was
304 # directly copied - but to err on the safe side of providing attribution:
305 # Copyright (C) 2007 LibLime
306 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
307 # Licensed under the GPL v2 or later
312 # Converts the callnumber to uppercase
313 # Strips spaces from start and end of the call number
314 # Converts anything other than letters, digits, and periods into spaces
315 # Collapses multiple spaces into a single underscore
316 my $callnum = uc(shift);
317 $callnum =~ s/^\s//g;
318 $callnum =~ s/\s$//g;
319 # NOTE: this previously used underscores, but this caused sorting issues
320 # for the "before" half of page 0 on CN browse, sorting CNs containing a
321 # decimal before "whole number" CNs
322 $callnum =~ s/[^A-Z0-9_.]/ /g;
323 $callnum =~ s/ {2,}/ /g;
326 $func$ LANGUAGE PLPERLU;
328 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
329 # Derived from the Koha C4::ClassSortRoutine::Dewey module
330 # Copyright (C) 2007 LibLime
331 # Licensed under the GPL v2 or later
336 my $init = uc(shift);
340 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
341 my @tokens = split /\.|\s+/, $init;
342 my $digit_group_count = 0;
343 for (my $i = 0; $i <= $#tokens; $i++) {
344 if ($tokens[$i] =~ /^\d+$/) {
345 $digit_group_count++;
346 if (2 == $digit_group_count) {
347 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
348 $tokens[$i] =~ tr/ /0/;
352 # Pad the first digit_group if there was only one
353 if (1 == $digit_group_count) {
354 $tokens[0] .= '_000000000000000'
356 my $key = join("_", @tokens);
357 $key =~ s/[^\p{IsAlnum}_]//g;
361 $func$ LANGUAGE PLPERLU;
363 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
367 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
368 # The author hopes to upload it to CPAN some day, which would make our lives easier
369 use Library::CallNumber::LC;
371 my $callnum = Library::CallNumber::LC->new(shift);
372 return $callnum->normalize();
374 $func$ LANGUAGE PLPERLU;
376 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
377 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
378 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
379 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
382 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
384 NEW.label_sortkey := REGEXP_REPLACE(
385 evergreen.lpad_number_substrings(
386 naco_normalize(NEW.label),
398 CREATE TABLE asset.call_number_prefix (
399 id SERIAL PRIMARY KEY,
400 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
401 label TEXT NOT NULL, -- i18n
404 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
405 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
406 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
408 CREATE TABLE asset.call_number_suffix (
409 id SERIAL PRIMARY KEY,
410 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
411 label TEXT NOT NULL, -- i18n
414 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
415 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
416 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
418 CREATE TABLE asset.call_number (
419 id bigserial PRIMARY KEY,
420 creator BIGINT NOT NULL,
421 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
422 editor BIGINT NOT NULL,
423 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
424 record bigint NOT NULL,
425 owning_lib INT NOT NULL,
427 deleted BOOL NOT NULL DEFAULT FALSE,
428 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
429 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
430 label_class BIGINT NOT NULL
431 REFERENCES asset.call_number_class(id)
432 DEFERRABLE INITIALLY DEFERRED,
435 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
436 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
437 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
438 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
439 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
440 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
441 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;
442 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;
443 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;
444 CREATE TRIGGER asset_label_sortkey_trigger
445 BEFORE UPDATE OR INSERT ON asset.call_number
446 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
448 CREATE TABLE asset.uri_call_number_map (
449 id BIGSERIAL PRIMARY KEY,
450 uri INT NOT NULL REFERENCES asset.uri (id),
451 call_number INT NOT NULL REFERENCES asset.call_number (id),
452 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
454 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
456 CREATE TABLE asset.call_number_note (
457 id BIGSERIAL PRIMARY KEY,
458 call_number BIGINT NOT NULL,
459 creator BIGINT NOT NULL,
460 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
461 pub BOOL NOT NULL DEFAULT FALSE,
465 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
467 CREATE TABLE asset.copy_template (
468 id SERIAL PRIMARY KEY,
469 owning_lib INT NOT NULL
470 REFERENCES actor.org_unit (id)
471 DEFERRABLE INITIALLY DEFERRED,
472 creator BIGINT NOT NULL
473 REFERENCES actor.usr (id)
474 DEFERRABLE INITIALLY DEFERRED,
475 editor BIGINT NOT NULL
476 REFERENCES actor.usr (id)
477 DEFERRABLE INITIALLY DEFERRED,
478 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
479 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
481 -- columns above this point are attributes of the template itself
482 -- columns after this point are attributes of the copy this template modifies/creates
483 circ_lib INT REFERENCES actor.org_unit (id)
484 DEFERRABLE INITIALLY DEFERRED,
485 status INT REFERENCES config.copy_status (id)
486 DEFERRABLE INITIALLY DEFERRED,
487 location INT REFERENCES asset.copy_location (id)
488 DEFERRABLE INITIALLY DEFERRED,
489 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
490 loan_duration IS NULL OR loan_duration IN (1,2,3)),
491 fine_level INT CONSTRAINT valid_fine_level CHECK (
492 fine_level IS NULL OR loan_duration IN (1,2,3)),
498 deposit_amount NUMERIC(6,2),
508 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$
513 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;
515 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
520 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
524 actor.org_unit_descendants(ans.id) d
525 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
526 JOIN asset.copy cp ON (cp.id = av.copy_id)
530 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
537 $f$ LANGUAGE PLPGSQL;
539 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$
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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso 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 -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
568 $f$ LANGUAGE PLPGSQL;
570 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$
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.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
582 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
583 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
586 actor.org_unit_descendants(ans.id) d
587 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
588 JOIN asset.copy_location cl ON (cp.location = cl.id)
589 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
593 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
600 $f$ LANGUAGE PLPGSQL;
602 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$
607 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;
609 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
614 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
615 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
618 actor.org_unit_descendants(ans.id) d
619 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
620 JOIN asset.copy_location cl ON (cp.location = cl.id)
621 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
625 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
632 $f$ LANGUAGE PLPGSQL;
634 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$
636 IF staff IS TRUE THEN
638 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
640 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
644 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
646 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
652 $f$ LANGUAGE PLPGSQL;
654 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
659 JOIN asset.call_number acn ON acp.call_number = acn.id
660 JOIN asset.copy_location acpl ON acp.location = acpl.id
661 JOIN config.copy_status ccs ON acp.status = ccs.id
664 AND acp.holdable = true
665 AND acpl.holdable = true
666 AND ccs.holdable = true
667 AND acp.deleted = false
674 $f$ LANGUAGE PLPGSQL;
676 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$
681 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;
683 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
688 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
692 actor.org_unit_descendants(ans.id) d
693 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
694 JOIN asset.copy cp ON (cp.id = av.copy_id)
695 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
699 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
706 $f$ LANGUAGE PLPGSQL;
708 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$
713 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;
715 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
720 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
724 actor.org_unit_descendants(ans.id) d
725 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
726 JOIN asset.copy cp ON (cp.id = av.copy_id)
727 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
731 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
738 $f$ LANGUAGE PLPGSQL;
740 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$
745 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;
747 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
752 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
756 actor.org_unit_descendants(ans.id) d
757 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
758 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
759 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
763 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
770 $f$ LANGUAGE PLPGSQL;
772 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$
777 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;
779 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
784 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
788 actor.org_unit_descendants(ans.id) d
789 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
790 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
791 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
795 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
802 $f$ LANGUAGE PLPGSQL;
804 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$
806 IF staff IS TRUE THEN
808 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
810 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
814 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
816 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
822 $f$ LANGUAGE PLPGSQL;
824 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
829 JOIN asset.call_number acn ON acp.call_number = acn.id
830 JOIN asset.copy_location acpl ON acp.location = acpl.id
831 JOIN config.copy_status ccs ON acp.status = ccs.id
832 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
834 mmsm.metarecord = rid
835 AND acp.holdable = true
836 AND acpl.holdable = true
837 AND ccs.holdable = true
838 AND acp.deleted = false
845 $f$ LANGUAGE PLPGSQL;
847 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
849 IF NEW.barcode LIKE '@@%' THEN
850 NEW.barcode := '@@' || NEW.id;
854 $f$ LANGUAGE PLPGSQL;
856 CREATE TRIGGER autogenerate_placeholder_barcode
857 BEFORE INSERT OR UPDATE ON asset.copy
858 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
860 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
864 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
865 PERFORM * FROM asset.copy WHERE id = copy_id;
867 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
871 $F$ LANGUAGE PLPGSQL;