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 active_date TIMESTAMP WITH TIME ZONE,
84 mint_condition BOOL NOT NULL DEFAULT TRUE,
87 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
88 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
89 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
90 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
91 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
92 CREATE INDEX cp_create_date ON asset.copy (create_date);
93 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;
95 CREATE TABLE asset.copy_part_map (
96 id SERIAL PRIMARY KEY,
97 target_copy BIGINT NOT NULL, -- points o asset.copy
98 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
100 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
102 CREATE TABLE asset.opac_visible_copies (
103 id BIGSERIAL primary key,
104 copy_id BIGINT, -- copy id
108 COMMENT ON TABLE asset.opac_visible_copies IS $$
109 Materialized view of copies that are visible in the OPAC, used by
110 search.query_parser_fts() to speed up OPAC visibility checks on large
111 databases. Contents are maintained by a set of triggers.
113 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
114 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
115 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
117 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
118 RETURNS TRIGGER AS $$
120 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
121 NEW.status_changed_time := now();
122 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
123 NEW.active_date := now();
130 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
131 CREATE OR REPLACE FUNCTION asset.acp_created()
132 RETURNS TRIGGER AS $$
134 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
135 NEW.active_date := now();
137 IF NEW.status_changed_time IS NULL THEN
138 NEW.status_changed_time := now();
144 CREATE TRIGGER acp_status_changed_trig
145 BEFORE UPDATE ON asset.copy
146 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
148 CREATE TRIGGER acp_created_trig
149 BEFORE INSERT ON asset.copy
150 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
152 CREATE TABLE asset.stat_cat_sip_fields (
153 field CHAR(2) PRIMARY KEY,
155 one_only BOOL NOT NULL DEFAULT FALSE
157 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
158 Asset Statistical Category SIP Fields
160 Contains the list of valid SIP Field identifiers for
161 Statistical Categories.
164 CREATE TABLE asset.stat_cat_entry_transparency_map (
165 id BIGSERIAL PRIMARY KEY,
166 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
167 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
168 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
169 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
172 CREATE TABLE asset.stat_cat (
173 id SERIAL PRIMARY KEY,
175 opac_visible BOOL NOT NULL DEFAULT FALSE,
177 required BOOL NOT NULL DEFAULT FALSE,
178 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
180 checkout_archive BOOL NOT NULL DEFAULT FALSE,
181 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
184 CREATE TABLE asset.stat_cat_entry (
185 id SERIAL PRIMARY KEY,
186 stat_cat INT NOT NULL,
189 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
192 CREATE TABLE asset.stat_cat_entry_copy_map (
193 id BIGSERIAL PRIMARY KEY,
194 stat_cat INT NOT NULL,
195 stat_cat_entry INT NOT NULL,
196 owning_copy BIGINT NOT NULL,
197 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
199 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
201 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
203 sipfield asset.stat_cat_sip_fields%ROWTYPE;
206 IF NEW.sip_field IS NOT NULL THEN
207 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
208 IF sipfield.one_only THEN
209 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
210 IF use_count > 0 THEN
211 RAISE EXCEPTION 'Sip field cannot be used twice';
217 $func$ LANGUAGE PLPGSQL;
219 CREATE TRIGGER asset_stat_cat_sip_update_trigger
220 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
221 EXECUTE PROCEDURE asset.stat_cat_check();
223 CREATE TABLE asset.copy_note (
224 id BIGSERIAL PRIMARY KEY,
225 owning_copy BIGINT NOT NULL,
226 creator BIGINT NOT NULL,
227 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
228 pub BOOL NOT NULL DEFAULT FALSE,
232 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
233 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
235 CREATE TABLE asset.uri (
236 id SERIAL PRIMARY KEY,
239 use_restriction TEXT,
240 active BOOL NOT NULL DEFAULT TRUE
243 CREATE TABLE asset.call_number_class (
244 id bigserial PRIMARY KEY,
246 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
247 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
249 COMMENT ON TABLE asset.call_number_class IS $$
250 Defines the call number normalization database functions in the "normalizer"
251 column and the tag/subfield combinations to use to lookup the call number in
252 the "field" column for a given classification scheme. Tag/subfield combinations
253 are delimited by commas.
256 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
260 sortkey := NEW.label_sortkey;
262 IF NEW.label_class IS NULL THEN
263 NEW.label_class := COALESCE(
265 SELECT substring(value from E'\\d+')::integer
266 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
271 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
272 quote_literal( NEW.label ) || ')'
273 FROM asset.call_number_class acnc
274 WHERE acnc.id = NEW.label_class
276 NEW.label_sortkey = sortkey;
279 $func$ LANGUAGE PLPGSQL;
281 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
282 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
283 # thus could probably be considered a derived work, although nothing was
284 # directly copied - but to err on the safe side of providing attribution:
285 # Copyright (C) 2007 LibLime
286 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
287 # Licensed under the GPL v2 or later
292 # Converts the callnumber to uppercase
293 # Strips spaces from start and end of the call number
294 # Converts anything other than letters, digits, and periods into spaces
295 # Collapses multiple spaces into a single underscore
296 my $callnum = uc(shift);
297 $callnum =~ s/^\s//g;
298 $callnum =~ s/\s$//g;
299 # NOTE: this previously used underscores, but this caused sorting issues
300 # for the "before" half of page 0 on CN browse, sorting CNs containing a
301 # decimal before "whole number" CNs
302 $callnum =~ s/[^A-Z0-9_.]/ /g;
303 $callnum =~ s/ {2,}/ /g;
306 $func$ LANGUAGE PLPERLU;
308 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
309 # Derived from the Koha C4::ClassSortRoutine::Dewey module
310 # Copyright (C) 2007 LibLime
311 # Licensed under the GPL v2 or later
316 my $init = uc(shift);
320 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
321 my @tokens = split /\.|\s+/, $init;
322 my $digit_group_count = 0;
323 for (my $i = 0; $i <= $#tokens; $i++) {
324 if ($tokens[$i] =~ /^\d+$/) {
325 $digit_group_count++;
326 if (2 == $digit_group_count) {
327 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
328 $tokens[$i] =~ tr/ /0/;
332 # Pad the first digit_group if there was only one
333 if (1 == $digit_group_count) {
334 $tokens[0] .= '_000000000000000'
336 my $key = join("_", @tokens);
337 $key =~ s/[^\p{IsAlnum}_]//g;
341 $func$ LANGUAGE PLPERLU;
343 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
347 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
348 # The author hopes to upload it to CPAN some day, which would make our lives easier
349 use Library::CallNumber::LC;
351 my $callnum = Library::CallNumber::LC->new(shift);
352 return $callnum->normalize();
354 $func$ LANGUAGE PLPERLU;
356 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
357 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
358 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
359 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
362 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
364 NEW.label_sortkey := REGEXP_REPLACE(
365 evergreen.lpad_number_substrings(
366 naco_normalize(NEW.label),
378 CREATE TABLE asset.call_number_prefix (
379 id SERIAL PRIMARY KEY,
380 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
381 label TEXT NOT NULL, -- i18n
384 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
385 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
386 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
388 CREATE TABLE asset.call_number_suffix (
389 id SERIAL PRIMARY KEY,
390 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
391 label TEXT NOT NULL, -- i18n
394 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
395 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
396 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
398 CREATE TABLE asset.call_number (
399 id bigserial PRIMARY KEY,
400 creator BIGINT NOT NULL,
401 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
402 editor BIGINT NOT NULL,
403 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
404 record bigint NOT NULL,
405 owning_lib INT NOT NULL,
407 deleted BOOL NOT NULL DEFAULT FALSE,
408 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
409 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
410 label_class BIGINT NOT NULL
411 REFERENCES asset.call_number_class(id)
412 DEFERRABLE INITIALLY DEFERRED,
415 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
416 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
417 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
418 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
419 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
420 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
421 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;
422 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;
423 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;
424 CREATE TRIGGER asset_label_sortkey_trigger
425 BEFORE UPDATE OR INSERT ON asset.call_number
426 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
428 CREATE TABLE asset.uri_call_number_map (
429 id BIGSERIAL PRIMARY KEY,
430 uri INT NOT NULL REFERENCES asset.uri (id),
431 call_number INT NOT NULL REFERENCES asset.call_number (id),
432 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
434 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
436 CREATE TABLE asset.call_number_note (
437 id BIGSERIAL PRIMARY KEY,
438 call_number BIGINT NOT NULL,
439 creator BIGINT NOT NULL,
440 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
441 pub BOOL NOT NULL DEFAULT FALSE,
445 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
447 CREATE TABLE asset.copy_template (
448 id SERIAL PRIMARY KEY,
449 owning_lib INT NOT NULL
450 REFERENCES actor.org_unit (id)
451 DEFERRABLE INITIALLY DEFERRED,
452 creator BIGINT NOT NULL
453 REFERENCES actor.usr (id)
454 DEFERRABLE INITIALLY DEFERRED,
455 editor BIGINT NOT NULL
456 REFERENCES actor.usr (id)
457 DEFERRABLE INITIALLY DEFERRED,
458 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
459 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
461 -- columns above this point are attributes of the template itself
462 -- columns after this point are attributes of the copy this template modifies/creates
463 circ_lib INT REFERENCES actor.org_unit (id)
464 DEFERRABLE INITIALLY DEFERRED,
465 status INT REFERENCES config.copy_status (id)
466 DEFERRABLE INITIALLY DEFERRED,
467 location INT REFERENCES asset.copy_location (id)
468 DEFERRABLE INITIALLY DEFERRED,
469 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
470 loan_duration IS NULL OR loan_duration IN (1,2,3)),
471 fine_level INT CONSTRAINT valid_fine_level CHECK (
472 fine_level IS NULL OR loan_duration IN (1,2,3)),
478 deposit_amount NUMERIC(6,2),
488 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$
493 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;
495 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
500 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
504 actor.org_unit_descendants(ans.id) d
505 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
506 JOIN asset.copy cp ON (cp.id = av.copy_id)
510 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
517 $f$ LANGUAGE PLPGSQL;
519 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$
524 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;
526 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
531 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
535 actor.org_unit_descendants(ans.id) d
536 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
537 JOIN asset.copy cp ON (cp.id = av.copy_id)
541 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
548 $f$ LANGUAGE PLPGSQL;
550 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$
555 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;
557 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
562 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
563 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
566 actor.org_unit_descendants(ans.id) d
567 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
568 JOIN asset.copy_location cl ON (cp.location = cl.id)
569 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
573 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
580 $f$ LANGUAGE PLPGSQL;
582 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$
587 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;
589 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
594 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
595 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
598 actor.org_unit_descendants(ans.id) d
599 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
600 JOIN asset.copy_location cl ON (cp.location = cl.id)
601 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
605 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
612 $f$ LANGUAGE PLPGSQL;
614 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$
616 IF staff IS TRUE THEN
618 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
620 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
624 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
626 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
632 $f$ LANGUAGE PLPGSQL;
634 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$
639 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;
641 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
646 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
650 actor.org_unit_descendants(ans.id) d
651 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
652 JOIN asset.copy cp ON (cp.id = av.copy_id)
653 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
657 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
664 $f$ LANGUAGE PLPGSQL;
666 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$
671 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;
673 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
678 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
682 actor.org_unit_descendants(ans.id) d
683 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
684 JOIN asset.copy cp ON (cp.id = av.copy_id)
685 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
689 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
696 $f$ LANGUAGE PLPGSQL;
698 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$
703 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;
705 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
710 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
714 actor.org_unit_descendants(ans.id) d
715 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
716 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
717 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
721 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
728 $f$ LANGUAGE PLPGSQL;
730 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$
735 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;
737 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
742 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
746 actor.org_unit_descendants(ans.id) d
747 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
748 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
749 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
753 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
760 $f$ LANGUAGE PLPGSQL;
762 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$
764 IF staff IS TRUE THEN
766 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
768 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
772 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
774 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
780 $f$ LANGUAGE PLPGSQL;
782 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
784 IF NEW.barcode LIKE '@@%' THEN
785 NEW.barcode := '@@' || NEW.id;
789 $f$ LANGUAGE PLPGSQL;
791 CREATE TRIGGER autogenerate_placeholder_barcode
792 BEFORE INSERT OR UPDATE ON asset.copy
793 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
795 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
799 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
800 PERFORM * FROM asset.copy WHERE id = copy_id;
802 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
806 $F$ LANGUAGE PLPGSQL;