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 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 my $first_digit_group_idx;
344 for (my $i = 0; $i <= $#tokens; $i++) {
345 if ($tokens[$i] =~ /^\d+$/) {
346 $digit_group_count++;
347 if ($digit_group_count == 1) {
348 $first_digit_group_idx = $i;
350 if (2 == $digit_group_count) {
351 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
352 $tokens[$i] =~ tr/ /0/;
356 # Pad the first digit_group if there was only one
357 if (1 == $digit_group_count) {
358 $tokens[$first_digit_group_idx] .= '_000000000000000'
360 my $key = join("_", @tokens);
361 $key =~ s/[^\p{IsAlnum}_]//g;
365 $func$ LANGUAGE PLPERLU;
368 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
372 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
373 # The author hopes to upload it to CPAN some day, which would make our lives easier
374 use Library::CallNumber::LC;
376 my $callnum = Library::CallNumber::LC->new(shift);
377 return $callnum->normalize();
379 $func$ LANGUAGE PLPERLU;
381 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
382 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
383 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
384 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
387 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
389 NEW.label_sortkey := REGEXP_REPLACE(
390 evergreen.lpad_number_substrings(
391 naco_normalize(NEW.label),
403 CREATE TABLE asset.call_number_prefix (
404 id SERIAL PRIMARY KEY,
405 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
406 label TEXT NOT NULL, -- i18n
409 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
410 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
411 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
413 CREATE TABLE asset.call_number_suffix (
414 id SERIAL PRIMARY KEY,
415 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
416 label TEXT NOT NULL, -- i18n
419 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
420 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
421 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
423 CREATE TABLE asset.call_number (
424 id bigserial PRIMARY KEY,
425 creator BIGINT NOT NULL,
426 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
427 editor BIGINT NOT NULL,
428 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
429 record bigint NOT NULL,
430 owning_lib INT NOT NULL,
432 deleted BOOL NOT NULL DEFAULT FALSE,
433 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
434 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
435 label_class BIGINT NOT NULL
436 REFERENCES asset.call_number_class(id)
437 DEFERRABLE INITIALLY DEFERRED,
440 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
441 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
442 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
443 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
444 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
445 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
446 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;
447 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;
448 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;
449 CREATE TRIGGER asset_label_sortkey_trigger
450 BEFORE UPDATE OR INSERT ON asset.call_number
451 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
453 CREATE TABLE asset.uri_call_number_map (
454 id BIGSERIAL PRIMARY KEY,
455 uri INT NOT NULL REFERENCES asset.uri (id),
456 call_number INT NOT NULL REFERENCES asset.call_number (id),
457 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
459 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
461 CREATE TABLE asset.call_number_note (
462 id BIGSERIAL PRIMARY KEY,
463 call_number BIGINT NOT NULL,
464 creator BIGINT NOT NULL,
465 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
466 pub BOOL NOT NULL DEFAULT FALSE,
470 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
472 CREATE TABLE asset.copy_template (
473 id SERIAL PRIMARY KEY,
474 owning_lib INT NOT NULL
475 REFERENCES actor.org_unit (id)
476 DEFERRABLE INITIALLY DEFERRED,
477 creator BIGINT NOT NULL
478 REFERENCES actor.usr (id)
479 DEFERRABLE INITIALLY DEFERRED,
480 editor BIGINT NOT NULL
481 REFERENCES actor.usr (id)
482 DEFERRABLE INITIALLY DEFERRED,
483 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
484 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
486 -- columns above this point are attributes of the template itself
487 -- columns after this point are attributes of the copy this template modifies/creates
488 circ_lib INT REFERENCES actor.org_unit (id)
489 DEFERRABLE INITIALLY DEFERRED,
490 status INT REFERENCES config.copy_status (id)
491 DEFERRABLE INITIALLY DEFERRED,
492 location INT REFERENCES asset.copy_location (id)
493 DEFERRABLE INITIALLY DEFERRED,
494 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
495 loan_duration IS NULL OR loan_duration IN (1,2,3)),
496 fine_level INT CONSTRAINT valid_fine_level CHECK (
497 fine_level IS NULL OR loan_duration IN (1,2,3)),
503 deposit_amount NUMERIC(6,2),
513 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$
518 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;
520 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
525 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
529 actor.org_unit_descendants(ans.id) d
530 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
531 JOIN asset.copy cp ON (cp.id = av.copy_id)
535 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
542 $f$ LANGUAGE PLPGSQL;
544 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$
549 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;
551 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
556 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
560 actor.org_unit_descendants(ans.id) d
561 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
562 JOIN asset.copy cp ON (cp.id = av.copy_id)
566 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
573 $f$ LANGUAGE PLPGSQL;
575 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$
580 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;
582 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
587 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
588 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
591 actor.org_unit_descendants(ans.id) d
592 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
593 JOIN asset.copy_location cl ON (cp.location = cl.id)
594 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
598 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
605 $f$ LANGUAGE PLPGSQL;
607 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$
612 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;
614 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
619 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
620 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
623 actor.org_unit_descendants(ans.id) d
624 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
625 JOIN asset.copy_location cl ON (cp.location = cl.id)
626 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
630 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
637 $f$ LANGUAGE PLPGSQL;
639 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$
641 IF staff IS TRUE THEN
643 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
645 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
649 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
651 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
657 $f$ LANGUAGE PLPGSQL;
659 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
664 JOIN asset.call_number acn ON acp.call_number = acn.id
665 JOIN asset.copy_location acpl ON acp.location = acpl.id
666 JOIN config.copy_status ccs ON acp.status = ccs.id
669 AND acp.holdable = true
670 AND acpl.holdable = true
671 AND ccs.holdable = true
672 AND acp.deleted = false
679 $f$ LANGUAGE PLPGSQL;
681 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$
686 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;
688 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
693 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
697 actor.org_unit_descendants(ans.id) d
698 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
699 JOIN asset.copy cp ON (cp.id = av.copy_id)
700 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
704 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
711 $f$ LANGUAGE PLPGSQL;
713 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$
718 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;
720 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
725 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
729 actor.org_unit_descendants(ans.id) d
730 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
731 JOIN asset.copy cp ON (cp.id = av.copy_id)
732 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
736 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
743 $f$ LANGUAGE PLPGSQL;
745 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$
750 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;
752 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
757 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
761 actor.org_unit_descendants(ans.id) d
762 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
763 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
764 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
768 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
775 $f$ LANGUAGE PLPGSQL;
777 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$
782 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;
784 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
789 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
793 actor.org_unit_descendants(ans.id) d
794 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
795 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
796 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
800 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
807 $f$ LANGUAGE PLPGSQL;
809 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$
811 IF staff IS TRUE THEN
813 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
815 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
819 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
821 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
827 $f$ LANGUAGE PLPGSQL;
829 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
834 JOIN asset.call_number acn ON acp.call_number = acn.id
835 JOIN asset.copy_location acpl ON acp.location = acpl.id
836 JOIN config.copy_status ccs ON acp.status = ccs.id
837 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
839 mmsm.metarecord = rid
840 AND acp.holdable = true
841 AND acpl.holdable = true
842 AND ccs.holdable = true
843 AND acp.deleted = false
850 $f$ LANGUAGE PLPGSQL;
852 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
854 IF NEW.barcode LIKE '@@%' THEN
855 NEW.barcode := '@@' || NEW.id;
859 $f$ LANGUAGE PLPGSQL;
861 CREATE TRIGGER autogenerate_placeholder_barcode
862 BEFORE INSERT OR UPDATE ON asset.copy
863 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
865 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
869 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
870 PERFORM * FROM asset.copy WHERE id = copy_id;
872 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
876 $F$ LANGUAGE PLPGSQL;