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 AND NEW.circ_lib = OLD.circ_lib 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 NULL THEN
184 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 name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
186 IF new_copy_location IS NOT NULL THEN
187 NEW.location = new_copy_location;
193 CREATE TRIGGER acp_location_fixer_trig
194 BEFORE INSERT OR UPDATE OF location, call_number, circ_lib ON asset.copy
195 FOR EACH ROW EXECUTE PROCEDURE asset.acp_location_fixer();
197 CREATE TABLE asset.stat_cat_sip_fields (
198 field CHAR(2) PRIMARY KEY,
200 one_only BOOL NOT NULL DEFAULT FALSE
202 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
203 Asset Statistical Category SIP Fields
205 Contains the list of valid SIP Field identifiers for
206 Statistical Categories.
209 CREATE TABLE asset.stat_cat_entry_transparency_map (
210 id BIGSERIAL PRIMARY KEY,
211 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
212 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
213 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
214 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
217 CREATE TABLE asset.stat_cat (
218 id SERIAL PRIMARY KEY,
220 opac_visible BOOL NOT NULL DEFAULT FALSE,
222 required BOOL NOT NULL DEFAULT FALSE,
223 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
225 checkout_archive BOOL NOT NULL DEFAULT FALSE,
226 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
229 CREATE TABLE asset.stat_cat_entry (
230 id SERIAL PRIMARY KEY,
231 stat_cat INT NOT NULL,
234 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
237 CREATE TABLE asset.stat_cat_entry_copy_map (
238 id BIGSERIAL PRIMARY KEY,
239 stat_cat INT NOT NULL,
240 stat_cat_entry INT NOT NULL,
241 owning_copy BIGINT NOT NULL,
242 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
244 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
246 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
248 sipfield asset.stat_cat_sip_fields%ROWTYPE;
251 IF NEW.sip_field IS NOT NULL THEN
252 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
253 IF sipfield.one_only THEN
254 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
255 IF use_count > 0 THEN
256 RAISE EXCEPTION 'Sip field cannot be used twice';
262 $func$ LANGUAGE PLPGSQL;
264 CREATE TRIGGER asset_stat_cat_sip_update_trigger
265 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
266 EXECUTE PROCEDURE asset.stat_cat_check();
268 CREATE TABLE asset.copy_note (
269 id BIGSERIAL PRIMARY KEY,
270 owning_copy BIGINT NOT NULL,
271 creator BIGINT NOT NULL,
272 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
273 pub BOOL NOT NULL DEFAULT FALSE,
277 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
278 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
280 CREATE TABLE asset.uri (
281 id SERIAL PRIMARY KEY,
284 use_restriction TEXT,
285 active BOOL NOT NULL DEFAULT TRUE
288 CREATE TABLE asset.call_number_class (
289 id bigserial PRIMARY KEY,
291 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
292 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
294 COMMENT ON TABLE asset.call_number_class IS $$
295 Defines the call number normalization database functions in the "normalizer"
296 column and the tag/subfield combinations to use to lookup the call number in
297 the "field" column for a given classification scheme. Tag/subfield combinations
298 are delimited by commas.
301 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
305 sortkey := NEW.label_sortkey;
307 IF NEW.label_class IS NULL THEN
308 NEW.label_class := COALESCE(
310 SELECT substring(value from E'\\d+')::integer
311 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
316 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
317 quote_literal( NEW.label ) || ')'
318 FROM asset.call_number_class acnc
319 WHERE acnc.id = NEW.label_class
321 NEW.label_sortkey = sortkey;
324 $func$ LANGUAGE PLPGSQL;
326 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
327 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
328 # thus could probably be considered a derived work, although nothing was
329 # directly copied - but to err on the safe side of providing attribution:
330 # Copyright (C) 2007 LibLime
331 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
332 # Licensed under the GPL v2 or later
337 # Converts the callnumber to uppercase
338 # Strips spaces from start and end of the call number
339 # Converts anything other than letters, digits, and periods into spaces
340 # Collapses multiple spaces into a single underscore
341 my $callnum = uc(shift);
342 $callnum =~ s/^\s//g;
343 $callnum =~ s/\s$//g;
344 # NOTE: this previously used underscores, but this caused sorting issues
345 # for the "before" half of page 0 on CN browse, sorting CNs containing a
346 # decimal before "whole number" CNs
347 $callnum =~ s/[^A-Z0-9_.]/ /g;
348 $callnum =~ s/ {2,}/ /g;
351 $func$ LANGUAGE PLPERLU IMMUTABLE;
353 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
354 # Derived from the Koha C4::ClassSortRoutine::Dewey module
355 # Copyright (C) 2007 LibLime
356 # Licensed under the GPL v2 or later
361 my $init = uc(shift);
365 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
366 my @tokens = split /\.|\s+/, $init;
367 my $digit_group_count = 0;
368 my $first_digit_group_idx;
369 for (my $i = 0; $i <= $#tokens; $i++) {
370 if ($tokens[$i] =~ /^\d+$/) {
371 $digit_group_count++;
372 if ($digit_group_count == 1) {
373 $first_digit_group_idx = $i;
375 if (2 == $digit_group_count) {
376 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
377 $tokens[$i] =~ tr/ /0/;
381 # Pad the first digit_group if there was only one
382 if (1 == $digit_group_count) {
383 $tokens[$first_digit_group_idx] .= '_000000000000000'
385 my $key = join("_", @tokens);
386 $key =~ s/[^\p{IsAlnum}_]//g;
390 $func$ LANGUAGE PLPERLU IMMUTABLE;
393 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
397 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
398 # The author hopes to upload it to CPAN some day, which would make our lives easier
399 use Library::CallNumber::LC;
401 my $callnum = Library::CallNumber::LC->new(shift);
402 return $callnum->normalize();
404 $func$ LANGUAGE PLPERLU IMMUTABLE;
406 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
407 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
408 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
409 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
412 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
414 NEW.label_sortkey := REGEXP_REPLACE(
415 evergreen.lpad_number_substrings(
416 naco_normalize(NEW.label),
428 CREATE TABLE asset.call_number_prefix (
429 id SERIAL PRIMARY KEY,
430 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
431 label TEXT NOT NULL, -- i18n
434 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
435 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
436 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
438 CREATE TABLE asset.call_number_suffix (
439 id SERIAL PRIMARY KEY,
440 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
441 label TEXT NOT NULL, -- i18n
444 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
445 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
446 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
448 CREATE TABLE asset.call_number (
449 id bigserial PRIMARY KEY,
450 creator BIGINT NOT NULL,
451 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
452 editor BIGINT NOT NULL,
453 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
454 record bigint NOT NULL,
455 owning_lib INT NOT NULL,
457 deleted BOOL NOT NULL DEFAULT FALSE,
458 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
459 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
460 label_class BIGINT NOT NULL
461 REFERENCES asset.call_number_class(id)
462 DEFERRABLE INITIALLY DEFERRED,
465 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
466 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
467 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
468 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
469 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
470 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
471 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;
472 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;
473 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;
474 CREATE TRIGGER asset_label_sortkey_trigger
475 BEFORE UPDATE OR INSERT ON asset.call_number
476 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
478 CREATE TABLE asset.uri_call_number_map (
479 id BIGSERIAL PRIMARY KEY,
480 uri INT NOT NULL REFERENCES asset.uri (id),
481 call_number INT NOT NULL REFERENCES asset.call_number (id),
482 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
484 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
486 CREATE TABLE asset.call_number_note (
487 id BIGSERIAL PRIMARY KEY,
488 call_number BIGINT NOT NULL,
489 creator BIGINT NOT NULL,
490 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
491 pub BOOL NOT NULL DEFAULT FALSE,
495 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
497 CREATE TABLE asset.copy_template (
498 id SERIAL PRIMARY KEY,
499 owning_lib INT NOT NULL
500 REFERENCES actor.org_unit (id)
501 DEFERRABLE INITIALLY DEFERRED,
502 creator BIGINT NOT NULL
503 REFERENCES actor.usr (id)
504 DEFERRABLE INITIALLY DEFERRED,
505 editor BIGINT NOT NULL
506 REFERENCES actor.usr (id)
507 DEFERRABLE INITIALLY DEFERRED,
508 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
509 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
511 -- columns above this point are attributes of the template itself
512 -- columns after this point are attributes of the copy this template modifies/creates
513 circ_lib INT REFERENCES actor.org_unit (id)
514 DEFERRABLE INITIALLY DEFERRED,
515 status INT REFERENCES config.copy_status (id)
516 DEFERRABLE INITIALLY DEFERRED,
517 location INT REFERENCES asset.copy_location (id)
518 DEFERRABLE INITIALLY DEFERRED,
519 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
520 loan_duration IS NULL OR loan_duration IN (1,2,3)),
521 fine_level INT CONSTRAINT valid_fine_level CHECK (
522 fine_level IS NULL OR loan_duration IN (1,2,3)),
528 deposit_amount NUMERIC(6,2),
538 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$
543 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;
545 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
550 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
554 actor.org_unit_descendants(ans.id) d
555 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
556 JOIN asset.copy cp ON (cp.id = av.copy_id)
560 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
567 $f$ LANGUAGE PLPGSQL;
569 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$
574 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;
576 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
581 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
585 actor.org_unit_descendants(ans.id) d
586 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
587 JOIN asset.copy cp ON (cp.id = av.copy_id)
591 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
598 $f$ LANGUAGE PLPGSQL;
600 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$
605 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;
607 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
612 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
613 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
616 actor.org_unit_descendants(ans.id) d
617 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
618 JOIN asset.copy_location cl ON (cp.location = cl.id)
619 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
623 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
630 $f$ LANGUAGE PLPGSQL;
632 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$
637 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;
639 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
644 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
645 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
648 actor.org_unit_descendants(ans.id) d
649 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
650 JOIN asset.copy_location cl ON (cp.location = cl.id)
651 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
655 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
662 $f$ LANGUAGE PLPGSQL;
664 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$
666 IF staff IS TRUE THEN
668 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
670 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
674 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
676 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
682 $f$ LANGUAGE PLPGSQL;
684 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
689 JOIN asset.call_number acn ON acp.call_number = acn.id
690 JOIN asset.copy_location acpl ON acp.location = acpl.id
691 JOIN config.copy_status ccs ON acp.status = ccs.id
694 AND acp.holdable = true
695 AND acpl.holdable = true
696 AND ccs.holdable = true
697 AND acp.deleted = false
698 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
705 $f$ LANGUAGE PLPGSQL;
707 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$
712 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;
714 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
719 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
723 actor.org_unit_descendants(ans.id) d
724 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
725 JOIN asset.copy cp ON (cp.id = av.copy_id)
726 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
730 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
737 $f$ LANGUAGE PLPGSQL;
739 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$
744 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;
746 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
751 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
755 actor.org_unit_descendants(ans.id) d
756 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
757 JOIN asset.copy cp ON (cp.id = av.copy_id)
758 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
762 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
769 $f$ LANGUAGE PLPGSQL;
771 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$
776 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;
778 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
783 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
787 actor.org_unit_descendants(ans.id) d
788 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
789 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
790 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
794 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
801 $f$ LANGUAGE PLPGSQL;
803 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$
808 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;
810 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
815 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
819 actor.org_unit_descendants(ans.id) d
820 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
821 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
822 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
826 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
833 $f$ LANGUAGE PLPGSQL;
835 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$
837 IF staff IS TRUE THEN
839 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
841 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
845 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
847 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
853 $f$ LANGUAGE PLPGSQL;
855 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
860 JOIN asset.call_number acn ON acp.call_number = acn.id
861 JOIN asset.copy_location acpl ON acp.location = acpl.id
862 JOIN config.copy_status ccs ON acp.status = ccs.id
863 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
865 mmsm.metarecord = rid
866 AND acp.holdable = true
867 AND acpl.holdable = true
868 AND ccs.holdable = true
869 AND acp.deleted = false
870 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
877 $f$ LANGUAGE PLPGSQL;
879 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
881 IF NEW.barcode LIKE '@@%' THEN
882 NEW.barcode := '@@' || NEW.id;
886 $f$ LANGUAGE PLPGSQL;
888 CREATE TRIGGER autogenerate_placeholder_barcode
889 BEFORE INSERT OR UPDATE ON asset.copy
890 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
892 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
896 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
897 PERFORM * FROM asset.copy WHERE id = copy_id;
899 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
903 $F$ LANGUAGE PLPGSQL;