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 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
183 CREATE TABLE asset.stat_cat_entry (
184 id SERIAL PRIMARY KEY,
185 stat_cat INT NOT NULL,
188 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
191 CREATE TABLE asset.stat_cat_entry_copy_map (
192 id BIGSERIAL PRIMARY KEY,
193 stat_cat INT NOT NULL,
194 stat_cat_entry INT NOT NULL,
195 owning_copy BIGINT NOT NULL,
196 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
198 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
200 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
202 sipfield asset.stat_cat_sip_fields%ROWTYPE;
205 IF NEW.sip_field IS NOT NULL THEN
206 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
207 IF sipfield.one_only THEN
208 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
209 IF use_count > 0 THEN
210 RAISE EXCEPTION 'Sip field cannot be used twice';
216 $func$ LANGUAGE PLPGSQL;
218 CREATE TRIGGER asset_stat_cat_sip_update_trigger
219 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
220 EXECUTE PROCEDURE asset.stat_cat_check();
222 CREATE TABLE asset.copy_note (
223 id BIGSERIAL PRIMARY KEY,
224 owning_copy BIGINT NOT NULL,
225 creator BIGINT NOT NULL,
226 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
227 pub BOOL NOT NULL DEFAULT FALSE,
231 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
232 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
234 CREATE TABLE asset.uri (
235 id SERIAL PRIMARY KEY,
238 use_restriction TEXT,
239 active BOOL NOT NULL DEFAULT TRUE
242 CREATE TABLE asset.call_number_class (
243 id bigserial PRIMARY KEY,
245 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
246 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
248 COMMENT ON TABLE asset.call_number_class IS $$
249 Defines the call number normalization database functions in the "normalizer"
250 column and the tag/subfield combinations to use to lookup the call number in
251 the "field" column for a given classification scheme. Tag/subfield combinations
252 are delimited by commas.
255 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
259 sortkey := NEW.label_sortkey;
261 IF NEW.label_class IS NULL THEN
262 NEW.label_class := COALESCE(
264 SELECT substring(value from E'\\d+')::integer
265 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
270 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
271 quote_literal( NEW.label ) || ')'
272 FROM asset.call_number_class acnc
273 WHERE acnc.id = NEW.label_class
275 NEW.label_sortkey = sortkey;
278 $func$ LANGUAGE PLPGSQL;
280 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
281 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
282 # thus could probably be considered a derived work, although nothing was
283 # directly copied - but to err on the safe side of providing attribution:
284 # Copyright (C) 2007 LibLime
285 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
286 # Licensed under the GPL v2 or later
291 # Converts the callnumber to uppercase
292 # Strips spaces from start and end of the call number
293 # Converts anything other than letters, digits, and periods into spaces
294 # Collapses multiple spaces into a single underscore
295 my $callnum = uc(shift);
296 $callnum =~ s/^\s//g;
297 $callnum =~ s/\s$//g;
298 # NOTE: this previously used underscores, but this caused sorting issues
299 # for the "before" half of page 0 on CN browse, sorting CNs containing a
300 # decimal before "whole number" CNs
301 $callnum =~ s/[^A-Z0-9_.]/ /g;
302 $callnum =~ s/ {2,}/ /g;
305 $func$ LANGUAGE PLPERLU;
307 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
308 # Derived from the Koha C4::ClassSortRoutine::Dewey module
309 # Copyright (C) 2007 LibLime
310 # Licensed under the GPL v2 or later
315 my $init = uc(shift);
319 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
320 my @tokens = split /\.|\s+/, $init;
321 my $digit_group_count = 0;
322 for (my $i = 0; $i <= $#tokens; $i++) {
323 if ($tokens[$i] =~ /^\d+$/) {
324 $digit_group_count++;
325 if (2 == $digit_group_count) {
326 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
327 $tokens[$i] =~ tr/ /0/;
331 my $key = join("_", @tokens);
332 $key =~ s/[^\p{IsAlnum}_]//g;
336 $func$ LANGUAGE PLPERLU;
338 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
342 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
343 # The author hopes to upload it to CPAN some day, which would make our lives easier
344 use Library::CallNumber::LC;
346 my $callnum = Library::CallNumber::LC->new(shift);
347 return $callnum->normalize();
349 $func$ LANGUAGE PLPERLU;
351 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
352 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
353 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
354 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
357 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
359 NEW.label_sortkey := REGEXP_REPLACE(
360 evergreen.lpad_number_substrings(
361 naco_normalize(NEW.label),
373 CREATE TABLE asset.call_number_prefix (
374 id SERIAL PRIMARY KEY,
375 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
376 label TEXT NOT NULL, -- i18n
379 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
380 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
381 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
383 CREATE TABLE asset.call_number_suffix (
384 id SERIAL PRIMARY KEY,
385 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
386 label TEXT NOT NULL, -- i18n
389 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
390 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
391 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
393 CREATE TABLE asset.call_number (
394 id bigserial PRIMARY KEY,
395 creator BIGINT NOT NULL,
396 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
397 editor BIGINT NOT NULL,
398 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
399 record bigint NOT NULL,
400 owning_lib INT NOT NULL,
402 deleted BOOL NOT NULL DEFAULT FALSE,
403 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
404 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
405 label_class BIGINT NOT NULL
406 REFERENCES asset.call_number_class(id)
407 DEFERRABLE INITIALLY DEFERRED,
410 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
411 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
412 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
413 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
414 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
415 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
416 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;
417 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;
418 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;
419 CREATE TRIGGER asset_label_sortkey_trigger
420 BEFORE UPDATE OR INSERT ON asset.call_number
421 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
423 CREATE TABLE asset.uri_call_number_map (
424 id BIGSERIAL PRIMARY KEY,
425 uri INT NOT NULL REFERENCES asset.uri (id),
426 call_number INT NOT NULL REFERENCES asset.call_number (id),
427 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
429 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
431 CREATE TABLE asset.call_number_note (
432 id BIGSERIAL PRIMARY KEY,
433 call_number BIGINT NOT NULL,
434 creator BIGINT NOT NULL,
435 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
436 pub BOOL NOT NULL DEFAULT FALSE,
440 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
442 CREATE TABLE asset.copy_template (
443 id SERIAL PRIMARY KEY,
444 owning_lib INT NOT NULL
445 REFERENCES actor.org_unit (id)
446 DEFERRABLE INITIALLY DEFERRED,
447 creator BIGINT NOT NULL
448 REFERENCES actor.usr (id)
449 DEFERRABLE INITIALLY DEFERRED,
450 editor BIGINT NOT NULL
451 REFERENCES actor.usr (id)
452 DEFERRABLE INITIALLY DEFERRED,
453 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
454 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
456 -- columns above this point are attributes of the template itself
457 -- columns after this point are attributes of the copy this template modifies/creates
458 circ_lib INT REFERENCES actor.org_unit (id)
459 DEFERRABLE INITIALLY DEFERRED,
460 status INT REFERENCES config.copy_status (id)
461 DEFERRABLE INITIALLY DEFERRED,
462 location INT REFERENCES asset.copy_location (id)
463 DEFERRABLE INITIALLY DEFERRED,
464 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
465 loan_duration IS NULL OR loan_duration IN (1,2,3)),
466 fine_level INT CONSTRAINT valid_fine_level CHECK (
467 fine_level IS NULL OR loan_duration IN (1,2,3)),
473 deposit_amount NUMERIC(6,2),
483 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$
488 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;
490 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
495 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
499 actor.org_unit_descendants(ans.id) d
500 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
501 JOIN asset.copy cp ON (cp.id = av.copy_id)
505 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
512 $f$ LANGUAGE PLPGSQL;
514 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$
519 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;
521 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
526 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
530 actor.org_unit_descendants(ans.id) d
531 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
532 JOIN asset.copy cp ON (cp.id = av.copy_id)
536 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
543 $f$ LANGUAGE PLPGSQL;
545 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$
550 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;
552 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
557 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
561 actor.org_unit_descendants(ans.id) d
562 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
563 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
567 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
574 $f$ LANGUAGE PLPGSQL;
576 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$
581 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;
583 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
588 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
592 actor.org_unit_descendants(ans.id) d
593 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
594 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
598 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
605 $f$ LANGUAGE PLPGSQL;
607 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$
609 IF staff IS TRUE THEN
611 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
613 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
617 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
619 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
625 $f$ LANGUAGE PLPGSQL;
627 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$
632 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;
634 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
639 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
643 actor.org_unit_descendants(ans.id) d
644 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
645 JOIN asset.copy cp ON (cp.id = av.copy_id)
646 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
650 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
657 $f$ LANGUAGE PLPGSQL;
659 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$
664 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;
666 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
671 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
675 actor.org_unit_descendants(ans.id) d
676 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
677 JOIN asset.copy cp ON (cp.id = av.copy_id)
678 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
682 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
689 $f$ LANGUAGE PLPGSQL;
691 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$
696 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;
698 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
703 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
707 actor.org_unit_descendants(ans.id) d
708 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
709 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
710 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
714 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
721 $f$ LANGUAGE PLPGSQL;
723 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$
728 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;
730 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
735 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
739 actor.org_unit_descendants(ans.id) d
740 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
741 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
742 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
746 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
753 $f$ LANGUAGE PLPGSQL;
755 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$
757 IF staff IS TRUE THEN
759 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
761 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
765 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
767 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
773 $f$ LANGUAGE PLPGSQL;
775 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
777 IF NEW.barcode LIKE '@@%' THEN
778 NEW.barcode := '@@' || NEW.id;
782 $f$ LANGUAGE PLPGSQL;
784 CREATE TRIGGER autogenerate_placeholder_barcode
785 BEFORE INSERT OR UPDATE ON asset.copy
786 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
788 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
792 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
793 PERFORM * FROM asset.copy WHERE id = copy_id;
795 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
799 $F$ LANGUAGE PLPGSQL;