2 * Copyright (C) 2004-2008 Georgia Public Library Service
3 * Copyright (C) 2007-2008 Equinox Software, Inc.
4 * Mike Rylander <miker@esilibrary.com>
6 * This program is free software; you can redistribute it and/or
7 * modify it under the terms of the GNU General Public License
8 * as published by the Free Software Foundation; either version 2
9 * of the License, or (at your option) any later version.
11 * This program is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
18 DROP SCHEMA IF EXISTS asset CASCADE;
24 CREATE TABLE asset.copy_location (
25 id SERIAL PRIMARY KEY,
27 owning_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
28 holdable BOOL NOT NULL DEFAULT TRUE,
29 hold_verify BOOL NOT NULL DEFAULT FALSE,
30 opac_visible BOOL NOT NULL DEFAULT TRUE,
31 circulate BOOL NOT NULL DEFAULT TRUE,
34 checkin_alert BOOL NOT NULL DEFAULT FALSE,
35 CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
38 CREATE TABLE asset.copy_location_order
40 id SERIAL PRIMARY KEY,
42 REFERENCES asset.copy_location
44 DEFERRABLE INITIALLY DEFERRED,
46 REFERENCES actor.org_unit
48 DEFERRABLE INITIALLY DEFERRED,
49 position INT NOT NULL DEFAULT 0,
50 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
53 CREATE TABLE asset.copy (
54 id BIGSERIAL PRIMARY KEY,
55 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
56 creator BIGINT NOT NULL,
57 call_number BIGINT NOT NULL,
58 editor BIGINT NOT NULL,
59 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
60 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
62 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
63 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
64 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
65 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
67 circulate BOOL NOT NULL DEFAULT TRUE,
68 deposit BOOL NOT NULL DEFAULT FALSE,
69 ref BOOL NOT NULL DEFAULT FALSE,
70 holdable BOOL NOT NULL DEFAULT TRUE,
71 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
73 barcode TEXT NOT NULL,
79 opac_visible BOOL NOT NULL DEFAULT TRUE,
80 deleted BOOL NOT NULL DEFAULT FALSE,
81 floating BOOL NOT NULL DEFAULT FALSE,
83 status_changed_time TIMESTAMP WITH TIME ZONE,
84 active_date TIMESTAMP WITH TIME ZONE,
85 mint_condition BOOL NOT NULL DEFAULT TRUE,
88 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
89 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
90 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
91 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
92 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
93 CREATE INDEX cp_create_date ON asset.copy (create_date);
94 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;
96 CREATE TABLE asset.copy_part_map (
97 id SERIAL PRIMARY KEY,
98 target_copy BIGINT NOT NULL, -- points o asset.copy
99 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
101 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
103 CREATE TABLE asset.opac_visible_copies (
104 id BIGSERIAL primary key,
105 copy_id BIGINT, -- copy id
109 COMMENT ON TABLE asset.opac_visible_copies IS $$
110 Materialized view of copies that are visible in the OPAC, used by
111 search.query_parser_fts() to speed up OPAC visibility checks on large
112 databases. Contents are maintained by a set of triggers.
114 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
115 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
116 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
118 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
119 RETURNS TRIGGER AS $$
121 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
122 NEW.status_changed_time := now();
123 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
124 NEW.active_date := now();
131 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
132 CREATE OR REPLACE FUNCTION asset.acp_created()
133 RETURNS TRIGGER AS $$
135 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
136 NEW.active_date := now();
138 IF NEW.status_changed_time IS NULL THEN
139 NEW.status_changed_time := now();
145 CREATE TRIGGER acp_status_changed_trig
146 BEFORE UPDATE ON asset.copy
147 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
149 CREATE TRIGGER acp_created_trig
150 BEFORE INSERT ON asset.copy
151 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
153 CREATE TABLE asset.stat_cat_sip_fields (
154 field CHAR(2) PRIMARY KEY,
156 one_only BOOL NOT NULL DEFAULT FALSE
158 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
159 Asset Statistical Category SIP Fields
161 Contains the list of valid SIP Field identifiers for
162 Statistical Categories.
165 CREATE TABLE asset.stat_cat_entry_transparency_map (
166 id BIGSERIAL PRIMARY KEY,
167 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
168 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
169 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
170 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
173 CREATE TABLE asset.stat_cat (
174 id SERIAL PRIMARY KEY,
176 opac_visible BOOL NOT NULL DEFAULT FALSE,
178 required BOOL NOT NULL DEFAULT FALSE,
179 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
181 checkout_archive BOOL NOT NULL DEFAULT FALSE,
182 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
185 CREATE TABLE asset.stat_cat_entry (
186 id SERIAL PRIMARY KEY,
187 stat_cat INT NOT NULL,
190 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
193 CREATE TABLE asset.stat_cat_entry_copy_map (
194 id BIGSERIAL PRIMARY KEY,
195 stat_cat INT NOT NULL,
196 stat_cat_entry INT NOT NULL,
197 owning_copy BIGINT NOT NULL,
198 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
200 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
202 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
204 sipfield asset.stat_cat_sip_fields%ROWTYPE;
207 IF NEW.sip_field IS NOT NULL THEN
208 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
209 IF sipfield.one_only THEN
210 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
211 IF use_count > 0 THEN
212 RAISE EXCEPTION 'Sip field cannot be used twice';
218 $func$ LANGUAGE PLPGSQL;
220 CREATE TRIGGER asset_stat_cat_sip_update_trigger
221 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
222 EXECUTE PROCEDURE asset.stat_cat_check();
224 CREATE TABLE asset.copy_note (
225 id BIGSERIAL PRIMARY KEY,
226 owning_copy BIGINT NOT NULL,
227 creator BIGINT NOT NULL,
228 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
229 pub BOOL NOT NULL DEFAULT FALSE,
233 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
234 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
236 CREATE TABLE asset.uri (
237 id SERIAL PRIMARY KEY,
240 use_restriction TEXT,
241 active BOOL NOT NULL DEFAULT TRUE
244 CREATE TABLE asset.call_number_class (
245 id bigserial PRIMARY KEY,
247 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
248 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
250 COMMENT ON TABLE asset.call_number_class IS $$
251 Defines the call number normalization database functions in the "normalizer"
252 column and the tag/subfield combinations to use to lookup the call number in
253 the "field" column for a given classification scheme. Tag/subfield combinations
254 are delimited by commas.
257 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
261 sortkey := NEW.label_sortkey;
263 IF NEW.label_class IS NULL THEN
264 NEW.label_class := COALESCE(
266 SELECT substring(value from E'\\d+')::integer
267 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
272 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
273 quote_literal( NEW.label ) || ')'
274 FROM asset.call_number_class acnc
275 WHERE acnc.id = NEW.label_class
277 NEW.label_sortkey = sortkey;
280 $func$ LANGUAGE PLPGSQL;
282 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
283 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
284 # thus could probably be considered a derived work, although nothing was
285 # directly copied - but to err on the safe side of providing attribution:
286 # Copyright (C) 2007 LibLime
287 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
288 # Licensed under the GPL v2 or later
293 # Converts the callnumber to uppercase
294 # Strips spaces from start and end of the call number
295 # Converts anything other than letters, digits, and periods into spaces
296 # Collapses multiple spaces into a single underscore
297 my $callnum = uc(shift);
298 $callnum =~ s/^\s//g;
299 $callnum =~ s/\s$//g;
300 # NOTE: this previously used underscores, but this caused sorting issues
301 # for the "before" half of page 0 on CN browse, sorting CNs containing a
302 # decimal before "whole number" CNs
303 $callnum =~ s/[^A-Z0-9_.]/ /g;
304 $callnum =~ s/ {2,}/ /g;
307 $func$ LANGUAGE PLPERLU;
309 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
310 # Derived from the Koha C4::ClassSortRoutine::Dewey module
311 # Copyright (C) 2007 LibLime
312 # Licensed under the GPL v2 or later
317 my $init = uc(shift);
321 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
322 my @tokens = split /\.|\s+/, $init;
323 my $digit_group_count = 0;
324 for (my $i = 0; $i <= $#tokens; $i++) {
325 if ($tokens[$i] =~ /^\d+$/) {
326 $digit_group_count++;
327 if (2 == $digit_group_count) {
328 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
329 $tokens[$i] =~ tr/ /0/;
333 # Pad the first digit_group if there was only one
334 if (1 == $digit_group_count) {
335 $tokens[0] .= '_000000000000000'
337 my $key = join("_", @tokens);
338 $key =~ s/[^\p{IsAlnum}_]//g;
342 $func$ LANGUAGE PLPERLU;
344 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
348 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
349 # The author hopes to upload it to CPAN some day, which would make our lives easier
350 use Library::CallNumber::LC;
352 my $callnum = Library::CallNumber::LC->new(shift);
353 return $callnum->normalize();
355 $func$ LANGUAGE PLPERLU;
357 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
358 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
359 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
360 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
363 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
365 NEW.label_sortkey := REGEXP_REPLACE(
366 evergreen.lpad_number_substrings(
367 naco_normalize(NEW.label),
379 CREATE TABLE asset.call_number_prefix (
380 id SERIAL PRIMARY KEY,
381 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
382 label TEXT NOT NULL, -- i18n
385 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
386 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
387 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
389 CREATE TABLE asset.call_number_suffix (
390 id SERIAL PRIMARY KEY,
391 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
392 label TEXT NOT NULL, -- i18n
395 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
396 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
397 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
399 CREATE TABLE asset.call_number (
400 id bigserial PRIMARY KEY,
401 creator BIGINT NOT NULL,
402 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
403 editor BIGINT NOT NULL,
404 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
405 record bigint NOT NULL,
406 owning_lib INT NOT NULL,
408 deleted BOOL NOT NULL DEFAULT FALSE,
409 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
410 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
411 label_class BIGINT NOT NULL
412 REFERENCES asset.call_number_class(id)
413 DEFERRABLE INITIALLY DEFERRED,
416 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
417 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
418 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
419 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
420 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
421 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
422 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;
423 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;
424 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;
425 CREATE TRIGGER asset_label_sortkey_trigger
426 BEFORE UPDATE OR INSERT ON asset.call_number
427 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
429 CREATE TABLE asset.uri_call_number_map (
430 id BIGSERIAL PRIMARY KEY,
431 uri INT NOT NULL REFERENCES asset.uri (id),
432 call_number INT NOT NULL REFERENCES asset.call_number (id),
433 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
435 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
437 CREATE TABLE asset.call_number_note (
438 id BIGSERIAL PRIMARY KEY,
439 call_number BIGINT NOT NULL,
440 creator BIGINT NOT NULL,
441 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
442 pub BOOL NOT NULL DEFAULT FALSE,
446 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
448 CREATE TABLE asset.copy_template (
449 id SERIAL PRIMARY KEY,
450 owning_lib INT NOT NULL
451 REFERENCES actor.org_unit (id)
452 DEFERRABLE INITIALLY DEFERRED,
453 creator BIGINT NOT NULL
454 REFERENCES actor.usr (id)
455 DEFERRABLE INITIALLY DEFERRED,
456 editor BIGINT NOT NULL
457 REFERENCES actor.usr (id)
458 DEFERRABLE INITIALLY DEFERRED,
459 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
460 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
462 -- columns above this point are attributes of the template itself
463 -- columns after this point are attributes of the copy this template modifies/creates
464 circ_lib INT REFERENCES actor.org_unit (id)
465 DEFERRABLE INITIALLY DEFERRED,
466 status INT REFERENCES config.copy_status (id)
467 DEFERRABLE INITIALLY DEFERRED,
468 location INT REFERENCES asset.copy_location (id)
469 DEFERRABLE INITIALLY DEFERRED,
470 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
471 loan_duration IS NULL OR loan_duration IN (1,2,3)),
472 fine_level INT CONSTRAINT valid_fine_level CHECK (
473 fine_level IS NULL OR loan_duration IN (1,2,3)),
479 deposit_amount NUMERIC(6,2),
489 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$
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.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
507 JOIN asset.copy cp ON (cp.id = av.copy_id)
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.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$
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.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
538 JOIN asset.copy cp ON (cp.id = av.copy_id)
542 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
549 $f$ LANGUAGE PLPGSQL;
551 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$
556 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;
558 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
563 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
564 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
567 actor.org_unit_descendants(ans.id) d
568 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
569 JOIN asset.copy_location cl ON (cp.location = cl.id)
570 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
574 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
581 $f$ LANGUAGE PLPGSQL;
583 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$
588 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;
590 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
595 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
596 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
599 actor.org_unit_descendants(ans.id) d
600 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
601 JOIN asset.copy_location cl ON (cp.location = cl.id)
602 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
606 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
613 $f$ LANGUAGE PLPGSQL;
615 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$
617 IF staff IS TRUE THEN
619 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
621 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
625 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
627 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
633 $f$ LANGUAGE PLPGSQL;
635 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$
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.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
653 JOIN asset.copy cp ON (cp.id = av.copy_id)
654 JOIN metabib.metarecord_source_map m ON (m.source = av.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.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$
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.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
685 JOIN asset.copy cp ON (cp.id = av.copy_id)
686 JOIN metabib.metarecord_source_map m ON (m.source = av.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.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$
704 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;
706 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
711 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
715 actor.org_unit_descendants(ans.id) d
716 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
717 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
718 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
722 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
729 $f$ LANGUAGE PLPGSQL;
731 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$
736 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;
738 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
743 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
747 actor.org_unit_descendants(ans.id) d
748 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
749 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
750 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
754 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
761 $f$ LANGUAGE PLPGSQL;
763 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$
765 IF staff IS TRUE THEN
767 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
769 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
773 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
775 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
781 $f$ LANGUAGE PLPGSQL;
783 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
785 IF NEW.barcode LIKE '@@%' THEN
786 NEW.barcode := '@@' || NEW.id;
790 $f$ LANGUAGE PLPGSQL;
792 CREATE TRIGGER autogenerate_placeholder_barcode
793 BEFORE INSERT OR UPDATE ON asset.copy
794 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
796 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
800 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
801 PERFORM * FROM asset.copy WHERE id = copy_id;
803 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
807 $F$ LANGUAGE PLPGSQL;