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 CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
37 CREATE TABLE asset.copy_location_order
39 id SERIAL PRIMARY KEY,
41 REFERENCES asset.copy_location
43 DEFERRABLE INITIALLY DEFERRED,
45 REFERENCES actor.org_unit
47 DEFERRABLE INITIALLY DEFERRED,
48 position INT NOT NULL DEFAULT 0,
49 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
52 CREATE TABLE asset.copy (
53 id BIGSERIAL PRIMARY KEY,
54 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
55 creator BIGINT NOT NULL,
56 call_number BIGINT NOT NULL,
57 editor BIGINT NOT NULL,
58 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
59 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
61 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
62 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
63 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
64 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
66 circulate BOOL NOT NULL DEFAULT TRUE,
67 deposit BOOL NOT NULL DEFAULT FALSE,
68 ref BOOL NOT NULL DEFAULT FALSE,
69 holdable BOOL NOT NULL DEFAULT TRUE,
70 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
72 barcode TEXT NOT NULL,
78 opac_visible BOOL NOT NULL DEFAULT TRUE,
79 deleted BOOL NOT NULL DEFAULT FALSE,
80 floating BOOL NOT NULL DEFAULT FALSE,
82 status_changed_time TIMESTAMP WITH TIME ZONE,
83 mint_condition BOOL NOT NULL DEFAULT TRUE,
86 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
87 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
88 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
89 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
90 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
91 CREATE INDEX cp_create_date ON asset.copy (create_date);
92 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;
94 CREATE TABLE asset.copy_part_map (
95 id SERIAL PRIMARY KEY,
96 target_copy BIGINT NOT NULL, -- points o asset.copy
97 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
99 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
101 CREATE TABLE asset.opac_visible_copies (
102 id BIGINT primary key, -- copy id
106 COMMENT ON TABLE asset.opac_visible_copies IS $$
107 Materialized view of copies that are visible in the OPAC, used by
108 search.query_parser_fts() to speed up OPAC visibility checks on large
109 databases. Contents are maintained by a set of triggers.
111 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
113 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
114 RETURNS TRIGGER AS $$
116 IF NEW.status <> OLD.status THEN
117 NEW.status_changed_time := now();
123 CREATE TRIGGER acp_status_changed_trig
124 BEFORE UPDATE ON asset.copy
125 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
127 CREATE TABLE asset.stat_cat_entry_transparency_map (
128 id BIGSERIAL PRIMARY KEY,
129 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
130 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
131 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
132 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
135 CREATE TABLE asset.stat_cat (
136 id SERIAL PRIMARY KEY,
138 opac_visible BOOL NOT NULL DEFAULT FALSE,
140 required BOOL NOT NULL DEFAULT FALSE,
141 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
144 CREATE TABLE asset.stat_cat_entry (
145 id SERIAL PRIMARY KEY,
146 stat_cat INT NOT NULL,
149 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
152 CREATE TABLE asset.stat_cat_entry_copy_map (
153 id BIGSERIAL PRIMARY KEY,
154 stat_cat INT NOT NULL,
155 stat_cat_entry INT NOT NULL,
156 owning_copy BIGINT NOT NULL,
157 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
159 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
161 CREATE TABLE asset.copy_note (
162 id BIGSERIAL PRIMARY KEY,
163 owning_copy BIGINT NOT NULL,
164 creator BIGINT NOT NULL,
165 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
166 pub BOOL NOT NULL DEFAULT FALSE,
170 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
171 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
173 CREATE TABLE asset.uri (
174 id SERIAL PRIMARY KEY,
177 use_restriction TEXT,
178 active BOOL NOT NULL DEFAULT TRUE
181 CREATE TABLE asset.call_number_class (
182 id bigserial PRIMARY KEY,
184 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
185 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
187 COMMENT ON TABLE asset.call_number_class IS $$
188 Defines the call number normalization database functions in the "normalizer"
189 column and the tag/subfield combinations to use to lookup the call number in
190 the "field" column for a given classification scheme. Tag/subfield combinations
191 are delimited by commas.
194 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
198 sortkey := NEW.label_sortkey;
200 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
201 quote_literal( NEW.label ) || ')'
202 FROM asset.call_number_class acnc
203 WHERE acnc.id = NEW.label_class
206 NEW.label_sortkey = sortkey;
210 $func$ LANGUAGE PLPGSQL;
212 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
213 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
214 # thus could probably be considered a derived work, although nothing was
215 # directly copied - but to err on the safe side of providing attribution:
216 # Copyright (C) 2007 LibLime
217 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
218 # Licensed under the GPL v2 or later
223 # Converts the callnumber to uppercase
224 # Strips spaces from start and end of the call number
225 # Converts anything other than letters, digits, and periods into spaces
226 # Collapses multiple spaces into a single underscore
227 my $callnum = uc(shift);
228 $callnum =~ s/^\s//g;
229 $callnum =~ s/\s$//g;
230 # NOTE: this previously used underscores, but this caused sorting issues
231 # for the "before" half of page 0 on CN browse, sorting CNs containing a
232 # decimal before "whole number" CNs
233 $callnum =~ s/[^A-Z0-9_.]/ /g;
234 $callnum =~ s/ {2,}/ /g;
237 $func$ LANGUAGE PLPERLU;
239 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
240 # Derived from the Koha C4::ClassSortRoutine::Dewey module
241 # Copyright (C) 2007 LibLime
242 # Licensed under the GPL v2 or later
247 my $init = uc(shift);
251 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
252 my @tokens = split /\.|\s+/, $init;
253 my $digit_group_count = 0;
254 for (my $i = 0; $i <= $#tokens; $i++) {
255 if ($tokens[$i] =~ /^\d+$/) {
256 $digit_group_count++;
257 if (2 == $digit_group_count) {
258 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
259 $tokens[$i] =~ tr/ /0/;
263 my $key = join("_", @tokens);
264 $key =~ s/[^\p{IsAlnum}_]//g;
268 $func$ LANGUAGE PLPERLU;
270 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
274 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
275 # The author hopes to upload it to CPAN some day, which would make our lives easier
276 use Library::CallNumber::LC;
278 my $callnum = Library::CallNumber::LC->new(shift);
279 return $callnum->normalize();
281 $func$ LANGUAGE PLPERLU;
283 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
284 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
285 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
286 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
289 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
291 NEW.label_sortkey := REGEXP_REPLACE(
292 evergreen.lpad_number_substrings(
293 naco_normalize(NEW.label),
305 CREATE TABLE asset.call_number_prefix (
306 id SERIAL PRIMARY KEY,
307 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
308 label TEXT NOT NULL, -- i18n
311 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
312 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
313 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
315 CREATE TABLE asset.call_number_suffix (
316 id SERIAL PRIMARY KEY,
317 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
318 label TEXT NOT NULL, -- i18n
321 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
322 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
323 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
325 CREATE TABLE asset.call_number (
326 id bigserial PRIMARY KEY,
327 creator BIGINT NOT NULL,
328 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
329 editor BIGINT NOT NULL,
330 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
331 record bigint NOT NULL,
332 owning_lib INT NOT NULL,
334 deleted BOOL NOT NULL DEFAULT FALSE,
335 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
336 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
337 label_class BIGINT DEFAULT 1 NOT NULL
338 REFERENCES asset.call_number_class(id)
339 DEFERRABLE INITIALLY DEFERRED,
342 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
343 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
344 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
345 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
346 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
347 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
348 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;
349 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;
350 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;
351 CREATE TRIGGER asset_label_sortkey_trigger
352 BEFORE UPDATE OR INSERT ON asset.call_number
353 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
355 CREATE TABLE asset.uri_call_number_map (
356 id BIGSERIAL PRIMARY KEY,
357 uri INT NOT NULL REFERENCES asset.uri (id),
358 call_number INT NOT NULL REFERENCES asset.call_number (id),
359 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
361 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
363 CREATE TABLE asset.call_number_note (
364 id BIGSERIAL PRIMARY KEY,
365 call_number BIGINT NOT NULL,
366 creator BIGINT NOT NULL,
367 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
368 pub BOOL NOT NULL DEFAULT FALSE,
372 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
374 CREATE TABLE asset.copy_template (
375 id SERIAL PRIMARY KEY,
376 owning_lib INT NOT NULL
377 REFERENCES actor.org_unit (id)
378 DEFERRABLE INITIALLY DEFERRED,
379 creator BIGINT NOT NULL
380 REFERENCES actor.usr (id)
381 DEFERRABLE INITIALLY DEFERRED,
382 editor BIGINT NOT NULL
383 REFERENCES actor.usr (id)
384 DEFERRABLE INITIALLY DEFERRED,
385 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
386 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
388 -- columns above this point are attributes of the template itself
389 -- columns after this point are attributes of the copy this template modifies/creates
390 circ_lib INT REFERENCES actor.org_unit (id)
391 DEFERRABLE INITIALLY DEFERRED,
392 status INT REFERENCES config.copy_status (id)
393 DEFERRABLE INITIALLY DEFERRED,
394 location INT REFERENCES asset.copy_location (id)
395 DEFERRABLE INITIALLY DEFERRED,
396 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
397 loan_duration IS NULL OR loan_duration IN (1,2,3)),
398 fine_level INT CONSTRAINT valid_fine_level CHECK (
399 fine_level IS NULL OR loan_duration IN (1,2,3)),
405 deposit_amount NUMERIC(6,2),
415 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$
420 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;
422 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
427 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
431 actor.org_unit_descendants(ans.id) d
432 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
433 JOIN asset.copy cp ON (cp.id = av.id)
437 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
444 $f$ LANGUAGE PLPGSQL;
446 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$
451 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;
453 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
458 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
462 actor.org_unit_descendants(ans.id) d
463 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
464 JOIN asset.copy cp ON (cp.id = av.id)
468 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
475 $f$ LANGUAGE PLPGSQL;
477 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$
482 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;
484 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
489 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
493 actor.org_unit_descendants(ans.id) d
494 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
495 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
499 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
506 $f$ LANGUAGE PLPGSQL;
508 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$
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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso 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.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
526 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
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.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$
541 IF staff IS TRUE THEN
543 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
545 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
549 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
551 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
557 $f$ LANGUAGE PLPGSQL;
559 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$
564 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;
566 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
571 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
575 actor.org_unit_descendants(ans.id) d
576 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
577 JOIN asset.copy cp ON (cp.id = av.id)
578 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
582 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
589 $f$ LANGUAGE PLPGSQL;
591 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$
596 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;
598 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
603 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
607 actor.org_unit_descendants(ans.id) d
608 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
609 JOIN asset.copy cp ON (cp.id = av.id)
610 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
614 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
621 $f$ LANGUAGE PLPGSQL;
623 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$
628 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;
630 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
635 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
639 actor.org_unit_descendants(ans.id) d
640 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
641 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
642 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
646 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
653 $f$ LANGUAGE PLPGSQL;
655 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$
660 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;
662 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
667 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
671 actor.org_unit_descendants(ans.id) d
672 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
673 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
674 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
678 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
685 $f$ LANGUAGE PLPGSQL;
687 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$
689 IF staff IS TRUE THEN
691 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
693 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
697 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
699 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
705 $f$ LANGUAGE PLPGSQL;
707 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
709 IF NEW.barcode LIKE '@@%' THEN
710 NEW.barcode := '@@' || NEW.id;
714 $f$ LANGUAGE PLPGSQL;
716 CREATE TRIGGER autogenerate_placeholder_barcode
717 BEFORE INSERT OR UPDATE ON asset.copy
718 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();