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.opac_visible_copies (
95 id BIGINT primary key, -- copy id
99 COMMENT ON TABLE asset.opac_visible_copies IS $$
100 Materialized view of copies that are visible in the OPAC, used by
101 search.query_parser_fts() to speed up OPAC visibility checks on large
102 databases. Contents are maintained by a set of triggers.
104 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
106 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
107 RETURNS TRIGGER AS $$
109 IF NEW.status <> OLD.status THEN
110 NEW.status_changed_time := now();
116 CREATE TRIGGER acp_status_changed_trig
117 BEFORE UPDATE ON asset.copy
118 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
120 CREATE TABLE asset.stat_cat_entry_transparency_map (
121 id BIGSERIAL PRIMARY KEY,
122 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
123 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
124 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
125 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
128 CREATE TABLE asset.stat_cat (
129 id SERIAL PRIMARY KEY,
131 opac_visible BOOL NOT NULL DEFAULT FALSE,
133 required BOOL NOT NULL DEFAULT FALSE,
134 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
137 CREATE TABLE asset.stat_cat_entry (
138 id SERIAL PRIMARY KEY,
139 stat_cat INT NOT NULL,
142 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
145 CREATE TABLE asset.stat_cat_entry_copy_map (
146 id BIGSERIAL PRIMARY KEY,
147 stat_cat INT NOT NULL,
148 stat_cat_entry INT NOT NULL,
149 owning_copy BIGINT NOT NULL,
150 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
152 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
154 CREATE TABLE asset.copy_note (
155 id BIGSERIAL PRIMARY KEY,
156 owning_copy BIGINT NOT NULL,
157 creator BIGINT NOT NULL,
158 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
159 pub BOOL NOT NULL DEFAULT FALSE,
163 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
164 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
166 CREATE TABLE asset.uri (
167 id SERIAL PRIMARY KEY,
170 use_restriction TEXT,
171 active BOOL NOT NULL DEFAULT TRUE
174 CREATE TABLE asset.call_number_class (
175 id bigserial PRIMARY KEY,
177 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
178 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
180 COMMENT ON TABLE asset.call_number_class IS $$
181 Defines the call number normalization database functions in the "normalizer"
182 column and the tag/subfield combinations to use to lookup the call number in
183 the "field" column for a given classification scheme. Tag/subfield combinations
184 are delimited by commas.
187 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
191 sortkey := NEW.label_sortkey;
193 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
194 quote_literal( NEW.label ) || ')'
195 FROM asset.call_number_class acnc
196 WHERE acnc.id = NEW.label_class
199 NEW.label_sortkey = sortkey;
203 $func$ LANGUAGE PLPGSQL;
205 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
206 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
207 # thus could probably be considered a derived work, although nothing was
208 # directly copied - but to err on the safe side of providing attribution:
209 # Copyright (C) 2007 LibLime
210 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
211 # Licensed under the GPL v2 or later
216 # Converts the callnumber to uppercase
217 # Strips spaces from start and end of the call number
218 # Converts anything other than letters, digits, and periods into spaces
219 # Collapses multiple spaces into a single underscore
220 my $callnum = uc(shift);
221 $callnum =~ s/^\s//g;
222 $callnum =~ s/\s$//g;
223 # NOTE: this previously used underscores, but this caused sorting issues
224 # for the "before" half of page 0 on CN browse, sorting CNs containing a
225 # decimal before "whole number" CNs
226 $callnum =~ s/[^A-Z0-9_.]/ /g;
227 $callnum =~ s/ {2,}/ /g;
230 $func$ LANGUAGE PLPERLU;
232 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
233 # Derived from the Koha C4::ClassSortRoutine::Dewey module
234 # Copyright (C) 2007 LibLime
235 # Licensed under the GPL v2 or later
240 my $init = uc(shift);
244 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
245 my @tokens = split /\.|\s+/, $init;
246 my $digit_group_count = 0;
247 for (my $i = 0; $i <= $#tokens; $i++) {
248 if ($tokens[$i] =~ /^\d+$/) {
249 $digit_group_count++;
250 if (2 == $digit_group_count) {
251 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
252 $tokens[$i] =~ tr/ /0/;
256 my $key = join("_", @tokens);
257 $key =~ s/[^\p{IsAlnum}_]//g;
261 $func$ LANGUAGE PLPERLU;
263 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
267 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
268 # The author hopes to upload it to CPAN some day, which would make our lives easier
269 use Library::CallNumber::LC;
271 my $callnum = Library::CallNumber::LC->new(shift);
272 return $callnum->normalize();
274 $func$ LANGUAGE PLPERLU;
276 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
277 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
278 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab'),
279 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab')
282 CREATE TABLE asset.call_number (
283 id bigserial PRIMARY KEY,
284 creator BIGINT NOT NULL,
285 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
286 editor BIGINT NOT NULL,
287 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
288 record bigint NOT NULL,
289 owning_lib INT NOT NULL,
291 deleted BOOL NOT NULL DEFAULT FALSE,
292 label_class BIGINT DEFAULT 1 NOT NULL
293 REFERENCES asset.call_number_class(id)
294 DEFERRABLE INITIALLY DEFERRED,
297 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
298 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
299 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
300 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
301 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
302 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
303 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE;
304 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;
305 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;
306 CREATE TRIGGER asset_label_sortkey_trigger
307 BEFORE UPDATE OR INSERT ON asset.call_number
308 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
310 CREATE TABLE asset.uri_call_number_map (
311 id BIGSERIAL PRIMARY KEY,
312 uri INT NOT NULL REFERENCES asset.uri (id),
313 call_number INT NOT NULL REFERENCES asset.call_number (id),
314 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
316 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
318 CREATE TABLE asset.call_number_note (
319 id BIGSERIAL PRIMARY KEY,
320 call_number BIGINT NOT NULL,
321 creator BIGINT NOT NULL,
322 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
323 pub BOOL NOT NULL DEFAULT FALSE,
327 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
329 CREATE TABLE asset.copy_template (
330 id SERIAL PRIMARY KEY,
331 owning_lib INT NOT NULL
332 REFERENCES actor.org_unit (id)
333 DEFERRABLE INITIALLY DEFERRED,
334 creator BIGINT NOT NULL
335 REFERENCES actor.usr (id)
336 DEFERRABLE INITIALLY DEFERRED,
337 editor BIGINT NOT NULL
338 REFERENCES actor.usr (id)
339 DEFERRABLE INITIALLY DEFERRED,
340 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
341 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
343 -- columns above this point are attributes of the template itself
344 -- columns after this point are attributes of the copy this template modifies/creates
345 circ_lib INT REFERENCES actor.org_unit (id)
346 DEFERRABLE INITIALLY DEFERRED,
347 status INT REFERENCES config.copy_status (id)
348 DEFERRABLE INITIALLY DEFERRED,
349 location INT REFERENCES asset.copy_location (id)
350 DEFERRABLE INITIALLY DEFERRED,
351 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
352 loan_duration IS NULL OR loan_duration IN (1,2,3)),
353 fine_level INT CONSTRAINT valid_fine_level CHECK (
354 fine_level IS NULL OR loan_duration IN (1,2,3)),
360 deposit_amount NUMERIC(6,2),
370 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$
375 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;
377 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
382 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
386 actor.org_unit_descendants(ans.id) d
387 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
388 JOIN asset.copy cp ON (cp.id = av.id)
392 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
399 $f$ LANGUAGE PLPGSQL;
401 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$
406 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;
408 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
413 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
417 actor.org_unit_descendants(ans.id) d
418 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
419 JOIN asset.copy cp ON (cp.id = av.id)
423 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
430 $f$ LANGUAGE PLPGSQL;
432 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$
437 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;
439 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
444 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
448 actor.org_unit_descendants(ans.id) d
449 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
450 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
454 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
461 $f$ LANGUAGE PLPGSQL;
463 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$
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.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso 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.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
481 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
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.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$
496 IF staff IS TRUE THEN
498 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
500 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
504 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
506 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
512 $f$ LANGUAGE PLPGSQL;
514 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$
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.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) 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.id)
533 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
537 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
544 $f$ LANGUAGE PLPGSQL;
546 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$
551 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;
553 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
558 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
562 actor.org_unit_descendants(ans.id) d
563 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
564 JOIN asset.copy cp ON (cp.id = av.id)
565 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
569 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
576 $f$ LANGUAGE PLPGSQL;
578 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$
583 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;
585 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
590 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
594 actor.org_unit_descendants(ans.id) d
595 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
596 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
597 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
601 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
608 $f$ LANGUAGE PLPGSQL;
610 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$
615 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;
617 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
622 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
626 actor.org_unit_descendants(ans.id) d
627 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
628 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
629 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
633 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
640 $f$ LANGUAGE PLPGSQL;
642 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$
644 IF staff IS TRUE THEN
646 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
648 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
652 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
654 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
660 $f$ LANGUAGE PLPGSQL;
662 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
664 IF NEW.barcode LIKE '@@%' THEN
665 NEW.barcode := '@@' || NEW.id;
669 $f$ LANGUAGE PLPGSQL;
671 CREATE TRIGGER autogenerate_placeholder_barcode
672 BEFORE INSERT OR UPDATE ON asset.copy
673 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();