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 deleted BOOL NOT NULL DEFAULT FALSE,
38 CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE;
40 CREATE TABLE asset.copy_location_order
42 id SERIAL PRIMARY KEY,
44 REFERENCES asset.copy_location
46 DEFERRABLE INITIALLY DEFERRED,
48 REFERENCES actor.org_unit
50 DEFERRABLE INITIALLY DEFERRED,
51 position INT NOT NULL DEFAULT 0,
52 CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
55 CREATE TABLE asset.copy_location_group (
56 id SERIAL PRIMARY KEY,
57 name TEXT NOT NULL, -- i18n
58 owner INT NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
59 pos INT NOT NULL DEFAULT 0,
60 top BOOL NOT NULL DEFAULT FALSE,
61 opac_visible BOOL NOT NULL DEFAULT TRUE,
62 CONSTRAINT lgroup_once_per_owner UNIQUE (owner,name)
65 CREATE TABLE asset.copy_location_group_map (
66 id SERIAL PRIMARY KEY,
67 location INT NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
68 lgroup INT NOT NULL REFERENCES asset.copy_location_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
69 CONSTRAINT lgroup_once_per_group UNIQUE (lgroup,location)
72 CREATE TABLE asset.copy (
73 id BIGSERIAL PRIMARY KEY,
74 circ_lib INT NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
75 creator BIGINT NOT NULL,
76 call_number BIGINT NOT NULL,
77 editor BIGINT NOT NULL,
78 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
79 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
81 status INT NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
82 location INT NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
83 loan_duration INT NOT NULL CHECK ( loan_duration IN (1,2,3) ),
84 fine_level INT NOT NULL CHECK ( fine_level IN (1,2,3) ),
86 circulate BOOL NOT NULL DEFAULT TRUE,
87 deposit BOOL NOT NULL DEFAULT FALSE,
88 ref BOOL NOT NULL DEFAULT FALSE,
89 holdable BOOL NOT NULL DEFAULT TRUE,
90 deposit_amount NUMERIC(6,2) NOT NULL DEFAULT 0.00,
92 barcode TEXT NOT NULL,
98 opac_visible BOOL NOT NULL DEFAULT TRUE,
99 deleted BOOL NOT NULL DEFAULT FALSE,
102 status_changed_time TIMESTAMP WITH TIME ZONE,
103 active_date TIMESTAMP WITH TIME ZONE,
104 mint_condition BOOL NOT NULL DEFAULT TRUE,
107 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
108 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
109 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
110 CREATE INDEX cp_creator_idx ON asset.copy ( creator );
111 CREATE INDEX cp_editor_idx ON asset.copy ( editor );
112 CREATE INDEX cp_create_date ON asset.copy (create_date);
113 CREATE INDEX cp_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7);
114 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;
116 CREATE TABLE asset.copy_part_map (
117 id SERIAL PRIMARY KEY,
118 target_copy BIGINT NOT NULL, -- points o asset.copy
119 part INT NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
121 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
123 CREATE TABLE asset.latest_inventory (
124 id SERIAL PRIMARY KEY,
125 inventory_workstation INTEGER REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
126 inventory_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
129 CREATE INDEX latest_inventory_copy_idx ON asset.latest_inventory (copy);
131 CREATE TABLE asset.opac_visible_copies (
132 id BIGSERIAL primary key,
133 copy_id BIGINT, -- copy id
137 COMMENT ON TABLE asset.opac_visible_copies IS $$
138 Materialized view of copies that are visible in the OPAC, used by
139 search.query_parser_fts() to speed up OPAC visibility checks on large
140 databases. Contents are maintained by a set of triggers.
142 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
143 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
144 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
146 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
147 RETURNS TRIGGER AS $$
149 IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
150 NEW.status_changed_time := now();
151 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
152 NEW.active_date := now();
159 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
160 CREATE OR REPLACE FUNCTION asset.acp_created()
161 RETURNS TRIGGER AS $$
163 IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
164 NEW.active_date := now();
166 IF NEW.status_changed_time IS NULL THEN
167 NEW.status_changed_time := now();
173 CREATE TRIGGER acp_status_changed_trig
174 BEFORE UPDATE ON asset.copy
175 FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
177 CREATE TRIGGER acp_created_trig
178 BEFORE INSERT ON asset.copy
179 FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
181 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
182 RETURNS TRIGGER AS $$
184 new_copy_location INT;
186 IF (TG_OP = 'UPDATE') THEN
187 IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN
191 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance((SELECT owning_lib FROM asset.call_number WHERE id = NEW.call_number)) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
192 IF new_copy_location IS NULL THEN
193 SELECT INTO new_copy_location acpl.id FROM asset.copy_location acpl JOIN actor.org_unit_ancestors_distance(NEW.circ_lib) aouad ON acpl.owning_lib = aouad.id WHERE deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
195 IF new_copy_location IS NOT NULL THEN
196 NEW.location = new_copy_location;
202 CREATE TRIGGER acp_location_fixer_trig
203 BEFORE INSERT OR UPDATE OF location, call_number, circ_lib ON asset.copy
204 FOR EACH ROW EXECUTE PROCEDURE asset.acp_location_fixer();
206 CREATE TABLE asset.stat_cat_sip_fields (
207 field CHAR(2) PRIMARY KEY,
209 one_only BOOL NOT NULL DEFAULT FALSE
211 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
212 Asset Statistical Category SIP Fields
214 Contains the list of valid SIP Field identifiers for
215 Statistical Categories.
218 CREATE TABLE asset.stat_cat_entry_transparency_map (
219 id BIGSERIAL PRIMARY KEY,
220 stat_cat INT NOT NULL, -- needs ON DELETE CASCADE
221 stat_cat_entry INT NOT NULL, -- needs ON DELETE CASCADE
222 owning_transparency INT NOT NULL, -- needs ON DELETE CASCADE
223 CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
226 CREATE TABLE asset.stat_cat (
227 id SERIAL PRIMARY KEY,
229 opac_visible BOOL NOT NULL DEFAULT FALSE,
231 required BOOL NOT NULL DEFAULT FALSE,
232 sip_field CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
234 checkout_archive BOOL NOT NULL DEFAULT FALSE,
235 CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
238 CREATE TABLE asset.stat_cat_entry (
239 id SERIAL PRIMARY KEY,
240 stat_cat INT NOT NULL,
243 CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
246 CREATE TABLE asset.stat_cat_entry_copy_map (
247 id BIGSERIAL PRIMARY KEY,
248 stat_cat INT NOT NULL,
249 stat_cat_entry INT NOT NULL,
250 owning_copy BIGINT NOT NULL,
251 CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
253 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
255 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
257 sipfield asset.stat_cat_sip_fields%ROWTYPE;
260 IF NEW.sip_field IS NOT NULL THEN
261 SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
262 IF sipfield.one_only THEN
263 SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
264 IF use_count > 0 THEN
265 RAISE EXCEPTION 'Sip field cannot be used twice';
271 $func$ LANGUAGE PLPGSQL;
273 CREATE TRIGGER asset_stat_cat_sip_update_trigger
274 BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
275 EXECUTE PROCEDURE asset.stat_cat_check();
277 CREATE TABLE asset.copy_note (
278 id BIGSERIAL PRIMARY KEY,
279 owning_copy BIGINT NOT NULL,
280 creator BIGINT NOT NULL,
281 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
282 pub BOOL NOT NULL DEFAULT FALSE,
286 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
287 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
289 CREATE TABLE asset.uri (
290 id SERIAL PRIMARY KEY,
293 use_restriction TEXT,
294 active BOOL NOT NULL DEFAULT TRUE
297 CREATE TABLE asset.call_number_class (
298 id bigserial PRIMARY KEY,
300 normalizer TEXT NOT NULL DEFAULT 'asset.normalize_generic',
301 field TEXT NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
303 COMMENT ON TABLE asset.call_number_class IS $$
304 Defines the call number normalization database functions in the "normalizer"
305 column and the tag/subfield combinations to use to lookup the call number in
306 the "field" column for a given classification scheme. Tag/subfield combinations
307 are delimited by commas.
310 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
314 sortkey := NEW.label_sortkey;
316 IF NEW.label_class IS NULL THEN
317 NEW.label_class := COALESCE(
319 SELECT substring(value from E'\\d+')::integer
320 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
325 EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
326 quote_literal( NEW.label ) || ')'
327 FROM asset.call_number_class acnc
328 WHERE acnc.id = NEW.label_class
330 NEW.label_sortkey = sortkey;
333 $func$ LANGUAGE PLPGSQL;
335 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
336 # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
337 # thus could probably be considered a derived work, although nothing was
338 # directly copied - but to err on the safe side of providing attribution:
339 # Copyright (C) 2007 LibLime
340 # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
341 # Licensed under the GPL v2 or later
346 # Converts the callnumber to uppercase
347 # Strips spaces from start and end of the call number
348 # Converts anything other than letters, digits, and periods into spaces
349 # Collapses multiple spaces into a single underscore
350 my $callnum = uc(shift);
351 $callnum =~ s/^\s//g;
352 $callnum =~ s/\s$//g;
353 # NOTE: this previously used underscores, but this caused sorting issues
354 # for the "before" half of page 0 on CN browse, sorting CNs containing a
355 # decimal before "whole number" CNs
356 $callnum =~ s/[^A-Z0-9_.]/ /g;
357 $callnum =~ s/ {2,}/ /g;
360 $func$ LANGUAGE PLPERLU IMMUTABLE;
362 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
363 # Derived from the Koha C4::ClassSortRoutine::Dewey module
364 # Copyright (C) 2007 LibLime
365 # Licensed under the GPL v2 or later
370 my $init = uc(shift);
374 $init =~ s/^([\p{IsAlpha}]+)/$1 /;
375 my @tokens = split /\.|\s+/, $init;
376 my $digit_group_count = 0;
377 my $first_digit_group_idx;
378 for (my $i = 0; $i <= $#tokens; $i++) {
379 if ($tokens[$i] =~ /^\d+$/) {
380 $digit_group_count++;
381 if ($digit_group_count == 1) {
382 $first_digit_group_idx = $i;
384 if (2 == $digit_group_count) {
385 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
386 $tokens[$i] =~ tr/ /0/;
390 # Pad the first digit_group if there was only one
391 if (1 == $digit_group_count) {
392 $tokens[$first_digit_group_idx] .= '_000000000000000'
394 my $key = join("_", @tokens);
395 $key =~ s/[^\p{IsAlnum}_]//g;
399 $func$ LANGUAGE PLPERLU IMMUTABLE;
402 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
406 # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
407 # The author hopes to upload it to CPAN some day, which would make our lives easier
408 use Library::CallNumber::LC;
410 my $callnum = Library::CallNumber::LC->new(shift);
411 return $callnum->normalize();
413 $func$ LANGUAGE PLPERLU IMMUTABLE;
415 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
416 ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
417 ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
418 ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
421 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
423 NEW.label_sortkey := REGEXP_REPLACE(
424 evergreen.lpad_number_substrings(
425 naco_normalize(NEW.label),
437 CREATE TABLE asset.call_number_prefix (
438 id SERIAL PRIMARY KEY,
439 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
440 label TEXT NOT NULL, -- i18n
443 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
444 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
445 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
447 CREATE TABLE asset.call_number_suffix (
448 id SERIAL PRIMARY KEY,
449 owning_lib INT NOT NULL REFERENCES actor.org_unit (id),
450 label TEXT NOT NULL, -- i18n
453 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
454 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
455 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
457 CREATE TABLE asset.call_number (
458 id bigserial PRIMARY KEY,
459 creator BIGINT NOT NULL,
460 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
461 editor BIGINT NOT NULL,
462 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
463 record bigint NOT NULL,
464 owning_lib INT NOT NULL,
466 deleted BOOL NOT NULL DEFAULT FALSE,
467 prefix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
468 suffix INT NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
469 label_class BIGINT NOT NULL
470 REFERENCES asset.call_number_class(id)
471 DEFERRABLE INITIALLY DEFERRED,
474 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
475 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
476 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
477 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
478 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
479 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
480 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;
481 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;
482 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;
483 CREATE TRIGGER asset_label_sortkey_trigger
484 BEFORE UPDATE OR INSERT ON asset.call_number
485 FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
487 CREATE TABLE asset.uri_call_number_map (
488 id BIGSERIAL PRIMARY KEY,
489 uri INT NOT NULL REFERENCES asset.uri (id),
490 call_number INT NOT NULL REFERENCES asset.call_number (id),
491 CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
493 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
495 CREATE TABLE asset.call_number_note (
496 id BIGSERIAL PRIMARY KEY,
497 call_number BIGINT NOT NULL,
498 creator BIGINT NOT NULL,
499 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
500 pub BOOL NOT NULL DEFAULT FALSE,
504 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
506 CREATE TABLE asset.copy_template (
507 id SERIAL PRIMARY KEY,
508 owning_lib INT NOT NULL
509 REFERENCES actor.org_unit (id)
510 DEFERRABLE INITIALLY DEFERRED,
511 creator BIGINT NOT NULL
512 REFERENCES actor.usr (id)
513 DEFERRABLE INITIALLY DEFERRED,
514 editor BIGINT NOT NULL
515 REFERENCES actor.usr (id)
516 DEFERRABLE INITIALLY DEFERRED,
517 create_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
518 edit_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
520 -- columns above this point are attributes of the template itself
521 -- columns after this point are attributes of the copy this template modifies/creates
522 circ_lib INT REFERENCES actor.org_unit (id)
523 DEFERRABLE INITIALLY DEFERRED,
524 status INT REFERENCES config.copy_status (id)
525 DEFERRABLE INITIALLY DEFERRED,
526 location INT REFERENCES asset.copy_location (id)
527 DEFERRABLE INITIALLY DEFERRED,
528 loan_duration INT CONSTRAINT valid_loan_duration CHECK (
529 loan_duration IS NULL OR loan_duration IN (1,2,3)),
530 fine_level INT CONSTRAINT valid_fine_level CHECK (
531 fine_level IS NULL OR loan_duration IN (1,2,3)),
537 deposit_amount NUMERIC(6,2),
547 CREATE TABLE asset.copy_vis_attr_cache (
548 id BIGSERIAL PRIMARY KEY,
549 record BIGINT NOT NULL, -- No FKEYs, managed by user triggers.
550 target_copy BIGINT NOT NULL,
551 vis_attr_vector INT[]
553 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
554 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
556 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$
561 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;
563 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
565 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
566 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
567 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
571 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
577 asset.copy_vis_attr_cache av
578 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
579 JOIN asset.call_number cn ON (cp.call_number = cn.id AND not cn.deleted)
580 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
584 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
591 $f$ LANGUAGE PLPGSQL;
593 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$
598 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;
600 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
602 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
603 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
604 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
608 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
613 asset.copy_vis_attr_cache av
614 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
615 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
619 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
626 $f$ LANGUAGE PLPGSQL;
628 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count(org integer, rid bigint)
629 RETURNS TABLE(depth integer, org_unit integer, visible bigint, available bigint, unshadow bigint, transcendant integer)
636 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;
638 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
640 WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
643 (cp.status = ANY (available_statuses.ids))::INT as available,
644 (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
647 actor.org_unit_descendants(ans.id) d
648 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
649 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
650 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
654 (cp.status = ANY (available_statuses.ids))::INT as available,
655 (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
658 actor.org_unit_descendants(ans.id) d
659 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
660 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
661 JOIN biblio.peer_bib_copy_map bp ON (bp.peer_record = rid AND bp.target_copy = cp.id)
663 select ans.depth, ans.id, count(id), sum(x.available::int), sum(x.opac_visible::int), trans
664 from ((select * from cp) union (select * from peer)) x
668 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
676 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$
681 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;
683 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
688 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
689 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
692 actor.org_unit_descendants(ans.id) d
693 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
694 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
695 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
699 RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
706 $f$ LANGUAGE PLPGSQL;
708 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$
710 IF staff IS TRUE THEN
712 RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
714 RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
718 RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
720 RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
726 $f$ LANGUAGE PLPGSQL;
728 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
733 JOIN asset.call_number acn ON acp.call_number = acn.id
734 JOIN asset.copy_location acpl ON acp.location = acpl.id
735 JOIN config.copy_status ccs ON acp.status = ccs.id
738 AND acp.holdable = true
739 AND acpl.holdable = true
740 AND ccs.holdable = true
741 AND acp.deleted = false
742 AND acpl.deleted = false
743 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
750 $f$ LANGUAGE PLPGSQL;
752 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$
757 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
759 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
761 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
762 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
763 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
767 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
773 asset.copy_vis_attr_cache av
774 JOIN asset.copy cp ON (cp.id = av.target_copy)
775 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
776 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
780 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
787 $f$ LANGUAGE PLPGSQL;
789 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$
794 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
796 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
798 WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
799 available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
800 mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
804 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
810 asset.copy_vis_attr_cache av
811 JOIN asset.copy cp ON (cp.id = av.target_copy)
812 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
813 WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
817 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
824 $f$ LANGUAGE PLPGSQL;
826 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$
831 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
833 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
838 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
842 actor.org_unit_descendants(ans.id) d
843 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
844 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
845 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
849 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
856 $f$ LANGUAGE PLPGSQL;
858 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$
863 SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) JOIN metabib.metarecord_source_map m ON (m.source = b.id) WHERE src.transcendant AND m.metarecord = rid;
865 FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
870 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
874 actor.org_unit_descendants(ans.id) d
875 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
876 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
877 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
881 RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
888 $f$ LANGUAGE PLPGSQL;
890 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$
892 IF staff IS TRUE THEN
894 RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
896 RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
900 RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
902 RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
908 $f$ LANGUAGE PLPGSQL;
910 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
915 JOIN asset.call_number acn ON acp.call_number = acn.id
916 JOIN asset.copy_location acpl ON acp.location = acpl.id
917 JOIN config.copy_status ccs ON acp.status = ccs.id
918 JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
920 mmsm.metarecord = rid
921 AND acp.holdable = true
922 AND acpl.holdable = true
923 AND ccs.holdable = true
924 AND acp.deleted = false
925 AND acpl.deleted = false
926 AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
933 $f$ LANGUAGE PLPGSQL;
935 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
937 IF NEW.barcode LIKE '@@%' THEN
938 NEW.barcode := '@@' || NEW.id;
942 $f$ LANGUAGE PLPGSQL;
944 CREATE TRIGGER autogenerate_placeholder_barcode
945 BEFORE INSERT OR UPDATE ON asset.copy
946 FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
948 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
952 EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
953 PERFORM * FROM asset.copy WHERE id = copy_id;
955 RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
959 $F$ LANGUAGE PLPGSQL;
961 CREATE TABLE asset.copy_tag (
962 id SERIAL PRIMARY KEY,
963 tag_type TEXT REFERENCES config.copy_tag_type (code)
964 ON UPDATE CASCADE ON DELETE CASCADE,
967 index_vector tsvector NOT NULL,
969 pub BOOLEAN DEFAULT TRUE,
970 owner INTEGER NOT NULL REFERENCES actor.org_unit (id),
974 CREATE INDEX asset_copy_tag_label_idx
975 ON asset.copy_tag (label);
976 CREATE INDEX asset_copy_tag_label_lower_idx
977 ON asset.copy_tag (evergreen.lowercase(label));
978 CREATE INDEX asset_copy_tag_index_vector_idx
980 USING GIN(index_vector);
981 CREATE INDEX asset_copy_tag_tag_type_idx
982 ON asset.copy_tag (tag_type);
983 CREATE INDEX asset_copy_tag_owner_idx
984 ON asset.copy_tag (owner);
986 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
988 IF NEW.value IS NULL THEN
989 NEW.value = NEW.label;
994 $$ LANGUAGE 'plpgsql';
996 -- name of following trigger chosen to ensure it runs first
997 CREATE TRIGGER asset_copy_tag_do_value
998 BEFORE INSERT OR UPDATE ON asset.copy_tag
999 FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
1000 CREATE TRIGGER asset_copy_tag_fti_trigger
1001 BEFORE UPDATE OR INSERT ON asset.copy_tag
1002 FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
1004 CREATE TABLE asset.copy_tag_copy_map (
1005 id BIGSERIAL PRIMARY KEY,
1007 tag INTEGER REFERENCES asset.copy_tag (id)
1008 ON UPDATE CASCADE ON DELETE CASCADE
1011 CREATE INDEX asset_copy_tag_copy_map_copy_idx
1012 ON asset.copy_tag_copy_map (copy);
1013 CREATE INDEX asset_copy_tag_copy_map_tag_idx
1014 ON asset.copy_tag_copy_map (tag);
1016 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
1018 last_circ_stop TEXT;
1019 the_copy asset.copy%ROWTYPE;
1022 SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
1023 IF NOT FOUND THEN RETURN NULL; END IF;
1025 IF the_copy.status = 3 THEN -- Lost
1027 ELSIF the_copy.status = 4 THEN -- Missing
1029 ELSIF the_copy.status = 14 THEN -- Damaged
1031 ELSIF the_copy.status = 17 THEN -- Lost and paid
1032 RETURN 'LOST_AND_PAID';
1035 SELECT stop_fines INTO last_circ_stop
1036 FROM action.circulation
1037 WHERE target_copy = cid AND checkin_time IS NULL
1038 ORDER BY xact_start DESC LIMIT 1;
1041 IF last_circ_stop IN (
1042 'CLAIMSNEVERCHECKEDOUT',
1046 RETURN last_circ_stop;
1052 $$ LANGUAGE PLPGSQL;
1054 CREATE TYPE config.copy_alert_type_state AS ENUM (
1062 'CLAIMSNEVERCHECKEDOUT'
1065 CREATE TYPE config.copy_alert_type_event AS ENUM (
1070 CREATE TABLE config.copy_alert_type (
1071 id serial primary key, -- reserve 1-100 for system
1072 scope_org int not null references actor.org_unit (id) on delete cascade,
1073 active bool not null default true,
1074 name text not null unique,
1075 state config.copy_alert_type_state,
1076 event config.copy_alert_type_event,
1078 invert_location bool not null default false,
1083 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1085 CREATE TABLE actor.copy_alert_suppress (
1086 id serial primary key,
1087 org int not null references actor.org_unit (id) on delete cascade,
1088 alert_type int not null references config.copy_alert_type (id) on delete cascade
1091 CREATE TABLE asset.copy_alert (
1092 id bigserial primary key,
1093 alert_type int not null references config.copy_alert_type (id) on delete cascade,
1094 copy bigint not null,
1095 temp bool not null default false,
1096 create_time timestamptz not null default now(),
1097 create_staff bigint not null references actor.usr (id) on delete set null,
1099 ack_time timestamptz,
1100 ack_staff bigint references actor.usr (id) on delete set null
1103 CREATE VIEW asset.active_copy_alert AS
1105 FROM asset.copy_alert
1106 WHERE ack_time IS NULL;