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_sip_fields (
131 field CHAR(2) PRIMARY KEY,
133 one_only BOOL NOT NULL DEFAULT FALSE
135 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
136 Asset Statistical Category SIP Fields
138 Contains the list of valid SIP Field identifiers for
139 Statistical Categories.
142 CREATE TABLE asset.stat_cat_entry_transparency_map (
143 id BIGSERIAL PRIMARY KEY,
144 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
145 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
146 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
147 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
150 CREATE TABLE asset.stat_cat (
151 id SERIAL PRIMARY KEY,
153 opac_visible BOOL NOT NULL DEFAULT FALSE,
155 required BOOL NOT NULL DEFAULT FALSE,
156 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
158 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
161 CREATE TABLE asset.stat_cat_entry (
162 id SERIAL PRIMARY KEY,
163 stat_cat INT NOT NULL,
166 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
169 CREATE TABLE asset.stat_cat_entry_copy_map (
170 id BIGSERIAL PRIMARY KEY,
171 stat_cat INT NOT NULL,
172 stat_cat_entry INT NOT NULL,
173 owning_copy BIGINT NOT NULL,
174 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
176 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
178 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
180 sipfield asset.stat_cat_sip_fields%ROWTYPE;
183 IF NEW.sip_field IS NOT NULL THEN
184 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
185 IF sipfield.one_only THEN
186 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
187 IF use_count > 0 THEN
188 RAISE EXCEPTION 'Sip field cannot be used twice';
194 $func$ LANGUAGE PLPGSQL;
196 CREATE TRIGGER asset_stat_cat_sip_update_trigger
197 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
198 EXECUTE PROCEDURE asset.stat_cat_check();
200 CREATE TABLE asset.copy_note (
201 id BIGSERIAL PRIMARY KEY,
202 owning_copy BIGINT NOT NULL,
203 creator BIGINT NOT NULL,
204 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
205 pub BOOL NOT NULL DEFAULT FALSE,
209 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
210 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
212 CREATE TABLE asset.uri (
213 id SERIAL PRIMARY KEY,
216 use_restriction TEXT,
217 active BOOL NOT NULL DEFAULT TRUE
220 CREATE TABLE asset.call_number_class (
221 id bigserial PRIMARY KEY,
223 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
224 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
226 COMMENT ON TABLE asset.call_number_class IS $$
227 Defines the call number normalization database functions in the "normalizer"
228 column and the tag/subfield combinations to use to lookup the call number in
229 the "field" column for a given classification scheme. Tag/subfield combinations
230 are delimited by commas.
233 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
237 sortkey := NEW.label_sortkey;
239 IF NEW.label_class IS NULL THEN
240 NEW.label_class := COALESCE(
242 SELECT substring(value from E'\\d+')::integer
243 FROM actor.org_unit_setting
244 WHERE name = 'cat.default_classification_scheme'
245 AND org_unit = NEW.owning_lib
250 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
251 quote_literal( NEW.label ) || ')'
252 FROM asset.call_number_class acnc
253 WHERE acnc.id = NEW.label_class
255 NEW.label_sortkey = sortkey;
258 $func$ LANGUAGE PLPGSQL;
260 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
261 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
262 # thus could probably be considered a derived work, although nothing was
263 # directly copied - but to err on the safe side of providing attribution:
264 # Copyright (C) 2007 LibLime
265 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
266 # Licensed under the GPL v2 or later
271 # Converts the callnumber to uppercase
272 # Strips spaces from start and end of the call number
273 # Converts anything other than letters, digits, and periods into spaces
274 # Collapses multiple spaces into a single underscore
275 my $callnum = uc(shift);
276 $callnum =~ s/^\s//g;
277 $callnum =~ s/\s$//g;
278 # NOTE: this previously used underscores, but this caused sorting issues
279 # for the "before" half of page 0 on CN browse, sorting CNs containing a
280 # decimal before "whole number" CNs
281 $callnum =~ s/[^A-Z0-9_.]/ /g;
282 $callnum =~ s/ {2,}/ /g;
285 $func$ LANGUAGE PLPERLU;
287 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
288 # Derived from the Koha C4::ClassSortRoutine::Dewey module
289 # Copyright (C) 2007 LibLime
290 # Licensed under the GPL v2 or later
295 my $init = uc(shift);
299 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
300 my @tokens = split /\.|\s+/, $init;
301 my $digit_group_count = 0;
302 for (my $i = 0; $i <= $#tokens; $i++) {
303 if ($tokens[$i] =~ /^\d+$/) {
304 $digit_group_count++;
305 if (2 == $digit_group_count) {
306 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
307 $tokens[$i] =~ tr/ /0/;
311 my $key = join("_", @tokens);
312 $key =~ s/[^\p{IsAlnum}_]//g;
316 $func$ LANGUAGE PLPERLU;
318 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
322 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
323 # The author hopes to upload it to CPAN some day, which would make our lives easier
324 use Library::CallNumber::LC;
326 my $callnum = Library::CallNumber::LC->new(shift);
327 return $callnum->normalize();
329 $func$ LANGUAGE PLPERLU;
331 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
332 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
333 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
334 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
337 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
339 NEW.label_sortkey := REGEXP_REPLACE(
340 evergreen.lpad_number_substrings(
341 naco_normalize(NEW.label),
353 CREATE TABLE asset.call_number_prefix (
354 id SERIAL PRIMARY KEY,
355 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
356 label TEXT NOT NULL, -- i18n
359 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
360 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
361 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
363 CREATE TABLE asset.call_number_suffix (
364 id SERIAL PRIMARY KEY,
365 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
366 label TEXT NOT NULL, -- i18n
369 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
370 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
371 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
373 CREATE TABLE asset.call_number (
374 id bigserial PRIMARY KEY,
375 creator BIGINT NOT NULL,
376 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
377 editor BIGINT NOT NULL,
378 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
379 record bigint NOT NULL,
380 owning_lib INT NOT NULL,
382 deleted BOOL NOT NULL DEFAULT FALSE,
383 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
384 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
385 label_class BIGINT NOT NULL
386 REFERENCES asset.call_number_class(id)
387 DEFERRABLE INITIALLY DEFERRED,
390 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
391 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
392 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
393 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
394 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
395 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
396 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;
397 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;
398 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;
399 CREATE TRIGGER asset_label_sortkey_trigger
400 BEFORE UPDATE OR INSERT ON asset.call_number
401 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
403 CREATE TABLE asset.uri_call_number_map (
404 id BIGSERIAL PRIMARY KEY,
405 uri INT NOT NULL REFERENCES asset.uri (id),
406 call_number INT NOT NULL REFERENCES asset.call_number (id),
407 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
409 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
411 CREATE TABLE asset.call_number_note (
412 id BIGSERIAL PRIMARY KEY,
413 call_number BIGINT NOT NULL,
414 creator BIGINT NOT NULL,
415 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
416 pub BOOL NOT NULL DEFAULT FALSE,
420 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
422 CREATE TABLE asset.copy_template (
423 id SERIAL PRIMARY KEY,
424 owning_lib INT NOT NULL
425 REFERENCES actor.org_unit (id)
426 DEFERRABLE INITIALLY DEFERRED,
427 creator BIGINT NOT NULL
428 REFERENCES actor.usr (id)
429 DEFERRABLE INITIALLY DEFERRED,
430 editor BIGINT NOT NULL
431 REFERENCES actor.usr (id)
432 DEFERRABLE INITIALLY DEFERRED,
433 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
434 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
436 -- columns above this point are attributes of the template itself
437 -- columns after this point are attributes of the copy this template modifies/creates
438 circ_lib INT REFERENCES actor.org_unit (id)
439 DEFERRABLE INITIALLY DEFERRED,
440 status INT REFERENCES config.copy_status (id)
441 DEFERRABLE INITIALLY DEFERRED,
442 location INT REFERENCES asset.copy_location (id)
443 DEFERRABLE INITIALLY DEFERRED,
444 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
445 loan_duration IS NULL OR loan_duration IN (1,2,3)),
446 fine_level INT CONSTRAINT valid_fine_level CHECK (
447 fine_level IS NULL OR loan_duration IN (1,2,3)),
453 deposit_amount NUMERIC(6,2),
463 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$
468 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;
470 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
475 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
479 actor.org_unit_descendants(ans.id) d
480 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
481 JOIN asset.copy cp ON (cp.id = av.copy_id)
485 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
492 $f$ LANGUAGE PLPGSQL;
494 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$
499 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;
501 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
506 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
510 actor.org_unit_descendants(ans.id) d
511 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
512 JOIN asset.copy cp ON (cp.id = av.copy_id)
516 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
523 $f$ LANGUAGE PLPGSQL;
525 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$
530 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;
532 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
537 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
541 actor.org_unit_descendants(ans.id) d
542 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
543 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
547 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
554 $f$ LANGUAGE PLPGSQL;
556 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$
561 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;
563 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
568 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
572 actor.org_unit_descendants(ans.id) d
573 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
574 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
578 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
585 $f$ LANGUAGE PLPGSQL;
587 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$
589 IF staff IS TRUE THEN
591 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
593 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
597 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
599 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
605 $f$ LANGUAGE PLPGSQL;
607 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$
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.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
619 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
623 actor.org_unit_descendants(ans.id) d
624 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
625 JOIN asset.copy cp ON (cp.id = av.copy_id)
626 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
630 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
637 $f$ LANGUAGE PLPGSQL;
639 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$
644 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;
646 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
651 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
655 actor.org_unit_descendants(ans.id) d
656 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
657 JOIN asset.copy cp ON (cp.id = av.copy_id)
658 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
662 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
669 $f$ LANGUAGE PLPGSQL;
671 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$
676 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;
678 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
683 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
687 actor.org_unit_descendants(ans.id) d
688 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
689 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
690 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
694 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
701 $f$ LANGUAGE PLPGSQL;
703 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$
708 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;
710 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
715 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
719 actor.org_unit_descendants(ans.id) d
720 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
721 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
722 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
726 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
733 $f$ LANGUAGE PLPGSQL;
735 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$
737 IF staff IS TRUE THEN
739 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
741 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
745 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
747 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
753 $f$ LANGUAGE PLPGSQL;
755 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
757 IF NEW.barcode LIKE '@@%' THEN
758 NEW.barcode := '@@' || NEW.id;
762 $f$ LANGUAGE PLPGSQL;
764 CREATE TRIGGER autogenerate_placeholder_barcode
765 BEFORE INSERT OR UPDATE ON asset.copy
766 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
768 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
772 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
773 PERFORM * FROM asset.copy WHERE id = copy_id;
775 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
779 $F$ LANGUAGE PLPGSQL;