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 # Licensed under the GPL v2 or later
215 # Converts the callnumber to uppercase
216 # Strips spaces from start and end of the call number
217 # Converts anything other than letters, digits, and periods into underscores
218 # Collapses multiple underscores into a single underscore
219 my $callnum = uc(shift);
220 $callnum =~ s/^\s//g;
221 $callnum =~ s/\s$//g;
222 $callnum =~ s/[^A-Z0-9_.]/_/g;
223 $callnum =~ s/_{2,}/_/g;
226 $func$ LANGUAGE PLPERLU;
228 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
229 # Derived from the Koha C4::ClassSortRoutine::Dewey module
230 # Copyright (C) 2007 LibLime
231 # Licensed under the GPL v2 or later
236 my $init = uc(shift);
240 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
241 my @tokens = split /\.|\s+/, $init;
242 my $digit_group_count = 0;
243 for (my $i = 0; $i <= $#tokens; $i++) {
244 if ($tokens[$i] =~ /^\d+$/) {
245 $digit_group_count++;
246 if (2 == $digit_group_count) {
247 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
248 $tokens[$i] =~ tr/ /0/;
252 my $key = join("_", @tokens);
253 $key =~ s/[^\p{IsAlnum}_]//g;
257 $func$ LANGUAGE PLPERLU;
259 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
263 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
264 # The author hopes to upload it to CPAN some day, which would make our lives easier
265 use Library::CallNumber::LC;
267 my $callnum = Library::CallNumber::LC->new(shift);
268 return $callnum->normalize();
270 $func$ LANGUAGE PLPERLU;
272 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
273 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
274 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab'),
275 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab')
278 CREATE TABLE asset.call_number (
279 id bigserial PRIMARY KEY,
280 creator BIGINT NOT NULL,
281 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
282 editor BIGINT NOT NULL,
283 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
284 record bigint NOT NULL,
285 owning_lib INT NOT NULL,
287 deleted BOOL NOT NULL DEFAULT FALSE,
288 label_class BIGINT DEFAULT 1 NOT NULL
289 REFERENCES asset.call_number_class(id)
290 DEFERRABLE INITIALLY DEFERRED,
293 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
294 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
295 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
296 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
297 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
298 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
299 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;
300 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;
301 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;
302 CREATE TRIGGER asset_label_sortkey_trigger
303 BEFORE UPDATE OR INSERT ON asset.call_number
304 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
306 CREATE TABLE asset.uri_call_number_map (
307 id BIGSERIAL PRIMARY KEY,
308 uri INT NOT NULL REFERENCES asset.uri (id),
309 call_number INT NOT NULL REFERENCES asset.call_number (id),
310 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
312 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
314 CREATE TABLE asset.call_number_note (
315 id BIGSERIAL PRIMARY KEY,
316 call_number BIGINT NOT NULL,
317 creator BIGINT NOT NULL,
318 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
319 pub BOOL NOT NULL DEFAULT FALSE,
323 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
325 CREATE TABLE asset.copy_template (
326 id SERIAL PRIMARY KEY,
327 owning_lib INT NOT NULL
328 REFERENCES actor.org_unit (id)
329 DEFERRABLE INITIALLY DEFERRED,
330 creator BIGINT NOT NULL
331 REFERENCES actor.usr (id)
332 DEFERRABLE INITIALLY DEFERRED,
333 editor BIGINT NOT NULL
334 REFERENCES actor.usr (id)
335 DEFERRABLE INITIALLY DEFERRED,
336 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
337 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
339 -- columns above this point are attributes of the template itself
340 -- columns after this point are attributes of the copy this template modifies/creates
341 circ_lib INT REFERENCES actor.org_unit (id)
342 DEFERRABLE INITIALLY DEFERRED,
343 status INT REFERENCES config.copy_status (id)
344 DEFERRABLE INITIALLY DEFERRED,
345 location INT REFERENCES asset.copy_location (id)
346 DEFERRABLE INITIALLY DEFERRED,
347 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
348 loan_duration IS NULL OR loan_duration IN (1,2,3)),
349 fine_level INT CONSTRAINT valid_fine_level CHECK (
350 fine_level IS NULL OR loan_duration IN (1,2,3)),
356 deposit_amount NUMERIC(6,2),
366 CREATE VIEW stats.fleshed_copy AS
368 CAST(cp.create_date AS DATE) AS create_date_day,
369 CAST(cp.edit_date AS DATE) AS edit_date_day,
370 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
371 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
372 cn.label AS call_number_label,
378 JOIN asset.call_number cn ON (cp.call_number = cn.id)
379 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
381 CREATE VIEW stats.fleshed_call_number AS
383 CAST(cn.create_date AS DATE) AS create_date_day,
384 CAST(cn.edit_date AS DATE) AS edit_date_day,
385 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
386 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
390 FROM asset.call_number cn
391 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
393 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$
398 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;
400 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
405 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
409 actor.org_unit_descendants(ans.id) d
410 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
411 JOIN asset.copy cp ON (cp.id = av.id)
415 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
422 $f$ LANGUAGE PLPGSQL;
424 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$
429 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;
431 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
436 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
440 actor.org_unit_descendants(ans.id) d
441 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
442 JOIN asset.copy cp ON (cp.id = av.id)
446 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
453 $f$ LANGUAGE PLPGSQL;
455 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$
460 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;
462 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
467 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
471 actor.org_unit_descendants(ans.id) d
472 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
473 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
477 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
484 $f$ LANGUAGE PLPGSQL;
486 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$
491 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;
493 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
498 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
502 actor.org_unit_descendants(ans.id) d
503 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
504 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
508 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
515 $f$ LANGUAGE PLPGSQL;
517 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$
519 IF staff IS TRUE THEN
521 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
523 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
527 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
529 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
535 $f$ LANGUAGE PLPGSQL;
537 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$
542 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;
544 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
549 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
553 actor.org_unit_descendants(ans.id) d
554 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
555 JOIN asset.copy cp ON (cp.id = av.id)
556 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
560 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
567 $f$ LANGUAGE PLPGSQL;
569 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$
574 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;
576 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
581 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
585 actor.org_unit_descendants(ans.id) d
586 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
587 JOIN asset.copy cp ON (cp.id = av.id)
588 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
592 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
599 $f$ LANGUAGE PLPGSQL;
601 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$
606 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;
608 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
613 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
617 actor.org_unit_descendants(ans.id) d
618 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
619 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
620 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
624 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
631 $f$ LANGUAGE PLPGSQL;
633 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$
638 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;
640 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
645 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
649 actor.org_unit_descendants(ans.id) d
650 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
651 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
652 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
656 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
663 $f$ LANGUAGE PLPGSQL;
665 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$
667 IF staff IS TRUE THEN
669 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
671 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
675 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
677 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
683 $f$ LANGUAGE PLPGSQL;
685 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
687 IF NEW.barcode LIKE '@@%' THEN
688 NEW.barcode := '@@' || NEW.id;
692 $f$ LANGUAGE PLPGSQL;
694 CREATE TRIGGER autogenerate_placeholder_barcode
695 BEFORE INSERT OR UPDATE ON asset.copy
696 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();