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 BIGSERIAL primary key,
103 copy_id BIGINT, -- copy id
107 COMMENT ON TABLE asset.opac_visible_copies IS $$
108 Materialized view of copies that are visible in the OPAC, used by
109 search.query_parser_fts() to speed up OPAC visibility checks on large
110 databases. Contents are maintained by a set of triggers.
112 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
113 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
114 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
116 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
117 RETURNS TRIGGER AS $$
119 IF NEW.status <> OLD.status THEN
120 NEW.status_changed_time := now();
126 CREATE TRIGGER acp_status_changed_trig
127 BEFORE UPDATE ON asset.copy
128 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
130 CREATE TABLE asset.stat_cat_entry_transparency_map (
131 id BIGSERIAL PRIMARY KEY,
132 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
133 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
134 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
135 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
138 CREATE TABLE asset.stat_cat (
139 id SERIAL PRIMARY KEY,
141 opac_visible BOOL NOT NULL DEFAULT FALSE,
143 required BOOL NOT NULL DEFAULT FALSE,
144 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
147 CREATE TABLE asset.stat_cat_entry (
148 id SERIAL PRIMARY KEY,
149 stat_cat INT NOT NULL,
152 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
155 CREATE TABLE asset.stat_cat_entry_copy_map (
156 id BIGSERIAL PRIMARY KEY,
157 stat_cat INT NOT NULL,
158 stat_cat_entry INT NOT NULL,
159 owning_copy BIGINT NOT NULL,
160 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
162 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
164 CREATE TABLE asset.copy_note (
165 id BIGSERIAL PRIMARY KEY,
166 owning_copy BIGINT NOT NULL,
167 creator BIGINT NOT NULL,
168 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
169 pub BOOL NOT NULL DEFAULT FALSE,
173 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
174 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
176 CREATE TABLE asset.uri (
177 id SERIAL PRIMARY KEY,
180 use_restriction TEXT,
181 active BOOL NOT NULL DEFAULT TRUE
184 CREATE TABLE asset.call_number_class (
185 id bigserial PRIMARY KEY,
187 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
188 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
190 COMMENT ON TABLE asset.call_number_class IS $$
191 Defines the call number normalization database functions in the "normalizer"
192 column and the tag/subfield combinations to use to lookup the call number in
193 the "field" column for a given classification scheme. Tag/subfield combinations
194 are delimited by commas.
197 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
201 sortkey := NEW.label_sortkey;
203 IF NEW.label_class IS NULL THEN
204 NEW.label_class := COALESCE(
206 SELECT substring(value from E'\\d+')::integer
207 FROM actor.org_unit_setting
208 WHERE name = 'cat.default_classification_scheme'
209 AND org_unit = NEW.owning_lib
214 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
215 quote_literal( NEW.label ) || ')'
216 FROM asset.call_number_class acnc
217 WHERE acnc.id = NEW.label_class
219 NEW.label_sortkey = sortkey;
222 $func$ LANGUAGE PLPGSQL;
224 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
225 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
226 # thus could probably be considered a derived work, although nothing was
227 # directly copied - but to err on the safe side of providing attribution:
228 # Copyright (C) 2007 LibLime
229 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
230 # Licensed under the GPL v2 or later
235 # Converts the callnumber to uppercase
236 # Strips spaces from start and end of the call number
237 # Converts anything other than letters, digits, and periods into spaces
238 # Collapses multiple spaces into a single underscore
239 my $callnum = uc(shift);
240 $callnum =~ s/^\s//g;
241 $callnum =~ s/\s$//g;
242 # NOTE: this previously used underscores, but this caused sorting issues
243 # for the "before" half of page 0 on CN browse, sorting CNs containing a
244 # decimal before "whole number" CNs
245 $callnum =~ s/[^A-Z0-9_.]/ /g;
246 $callnum =~ s/ {2,}/ /g;
249 $func$ LANGUAGE PLPERLU;
251 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
252 # Derived from the Koha C4::ClassSortRoutine::Dewey module
253 # Copyright (C) 2007 LibLime
254 # Licensed under the GPL v2 or later
259 my $init = uc(shift);
263 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
264 my @tokens = split /\.|\s+/, $init;
265 my $digit_group_count = 0;
266 for (my $i = 0; $i <= $#tokens; $i++) {
267 if ($tokens[$i] =~ /^\d+$/) {
268 $digit_group_count++;
269 if (2 == $digit_group_count) {
270 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
271 $tokens[$i] =~ tr/ /0/;
275 my $key = join("_", @tokens);
276 $key =~ s/[^\p{IsAlnum}_]//g;
280 $func$ LANGUAGE PLPERLU;
282 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
286 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
287 # The author hopes to upload it to CPAN some day, which would make our lives easier
288 use Library::CallNumber::LC;
290 my $callnum = Library::CallNumber::LC->new(shift);
291 return $callnum->normalize();
293 $func$ LANGUAGE PLPERLU;
295 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
296 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
297 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
298 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
301 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
303 NEW.label_sortkey := REGEXP_REPLACE(
304 evergreen.lpad_number_substrings(
305 naco_normalize(NEW.label),
317 CREATE TABLE asset.call_number_prefix (
318 id SERIAL PRIMARY KEY,
319 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
320 label TEXT NOT NULL, -- i18n
323 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
324 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
325 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
327 CREATE TABLE asset.call_number_suffix (
328 id SERIAL PRIMARY KEY,
329 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
330 label TEXT NOT NULL, -- i18n
333 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
334 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
335 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
337 CREATE TABLE asset.call_number (
338 id bigserial PRIMARY KEY,
339 creator BIGINT NOT NULL,
340 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
341 editor BIGINT NOT NULL,
342 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
343 record bigint NOT NULL,
344 owning_lib INT NOT NULL,
346 deleted BOOL NOT NULL DEFAULT FALSE,
347 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
348 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
349 label_class BIGINT NOT NULL
350 REFERENCES asset.call_number_class(id)
351 DEFERRABLE INITIALLY DEFERRED,
354 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
355 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
356 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
357 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
358 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
359 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
360 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;
361 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;
362 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;
363 CREATE TRIGGER asset_label_sortkey_trigger
364 BEFORE UPDATE OR INSERT ON asset.call_number
365 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
367 CREATE TABLE asset.uri_call_number_map (
368 id BIGSERIAL PRIMARY KEY,
369 uri INT NOT NULL REFERENCES asset.uri (id),
370 call_number INT NOT NULL REFERENCES asset.call_number (id),
371 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
373 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
375 CREATE TABLE asset.call_number_note (
376 id BIGSERIAL PRIMARY KEY,
377 call_number BIGINT NOT NULL,
378 creator BIGINT NOT NULL,
379 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
380 pub BOOL NOT NULL DEFAULT FALSE,
384 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
386 CREATE TABLE asset.copy_template (
387 id SERIAL PRIMARY KEY,
388 owning_lib INT NOT NULL
389 REFERENCES actor.org_unit (id)
390 DEFERRABLE INITIALLY DEFERRED,
391 creator BIGINT NOT NULL
392 REFERENCES actor.usr (id)
393 DEFERRABLE INITIALLY DEFERRED,
394 editor BIGINT NOT NULL
395 REFERENCES actor.usr (id)
396 DEFERRABLE INITIALLY DEFERRED,
397 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
398 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
400 -- columns above this point are attributes of the template itself
401 -- columns after this point are attributes of the copy this template modifies/creates
402 circ_lib INT REFERENCES actor.org_unit (id)
403 DEFERRABLE INITIALLY DEFERRED,
404 status INT REFERENCES config.copy_status (id)
405 DEFERRABLE INITIALLY DEFERRED,
406 location INT REFERENCES asset.copy_location (id)
407 DEFERRABLE INITIALLY DEFERRED,
408 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
409 loan_duration IS NULL OR loan_duration IN (1,2,3)),
410 fine_level INT CONSTRAINT valid_fine_level CHECK (
411 fine_level IS NULL OR loan_duration IN (1,2,3)),
417 deposit_amount NUMERIC(6,2),
427 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$
432 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;
434 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
439 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
443 actor.org_unit_descendants(ans.id) d
444 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
445 JOIN asset.copy cp ON (cp.id = av.copy_id)
449 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
456 $f$ LANGUAGE PLPGSQL;
458 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$
463 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;
465 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
470 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
474 actor.org_unit_descendants(ans.id) d
475 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
476 JOIN asset.copy cp ON (cp.id = av.copy_id)
480 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
487 $f$ LANGUAGE PLPGSQL;
489 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$
494 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;
496 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
501 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
505 actor.org_unit_descendants(ans.id) d
506 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
507 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
511 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
518 $f$ LANGUAGE PLPGSQL;
520 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$
525 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;
527 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
532 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
536 actor.org_unit_descendants(ans.id) d
537 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
538 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
542 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
549 $f$ LANGUAGE PLPGSQL;
551 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$
553 IF staff IS TRUE THEN
555 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
557 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
561 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
563 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
569 $f$ LANGUAGE PLPGSQL;
571 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$
576 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;
578 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
583 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
587 actor.org_unit_descendants(ans.id) d
588 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
589 JOIN asset.copy cp ON (cp.id = av.copy_id)
590 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
594 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
601 $f$ LANGUAGE PLPGSQL;
603 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$
608 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;
610 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
615 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
619 actor.org_unit_descendants(ans.id) d
620 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
621 JOIN asset.copy cp ON (cp.id = av.copy_id)
622 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
626 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
633 $f$ LANGUAGE PLPGSQL;
635 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$
640 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;
642 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
647 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
651 actor.org_unit_descendants(ans.id) d
652 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
653 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
654 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
658 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
665 $f$ LANGUAGE PLPGSQL;
667 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$
672 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;
674 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
679 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
683 actor.org_unit_descendants(ans.id) d
684 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
685 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
686 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
690 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
697 $f$ LANGUAGE PLPGSQL;
699 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$
701 IF staff IS TRUE THEN
703 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
705 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
709 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
711 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
717 $f$ LANGUAGE PLPGSQL;
719 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
721 IF NEW.barcode LIKE '@@%' THEN
722 NEW.barcode := '@@' || NEW.id;
726 $f$ LANGUAGE PLPGSQL;
728 CREATE TRIGGER autogenerate_placeholder_barcode
729 BEFORE INSERT OR UPDATE ON asset.copy
730 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
732 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
736 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
737 PERFORM * FROM asset.copy WHERE id = copy_id;
739 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
743 $F$ LANGUAGE PLPGSQL;