lp1777675 inventory date support
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 040.schema.asset.sql
1 /*
2  * Copyright (C) 2004-2008  Georgia Public Library Service
3  * Copyright (C) 2007-2008  Equinox Software, Inc.
4  * Mike Rylander <miker@esilibrary.com> 
5  *
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.
10  *
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.
15  *
16  */
17
18 DROP SCHEMA IF EXISTS asset CASCADE;
19
20 BEGIN;
21
22 CREATE SCHEMA asset;
23
24 CREATE TABLE asset.copy_location (
25         id              SERIAL  PRIMARY KEY,
26         name            TEXT    NOT NULL,
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,
32         label_prefix    TEXT,
33         label_suffix    TEXT,
34         checkin_alert   BOOL    NOT NULL DEFAULT FALSE,
35         deleted         BOOL    NOT NULL DEFAULT FALSE,
36         url             TEXT
37 );
38 CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE;
39
40 CREATE TABLE asset.copy_location_order
41 (
42         id              SERIAL           PRIMARY KEY,
43         location        INT              NOT NULL
44                                              REFERENCES asset.copy_location
45                                              ON DELETE CASCADE
46                                              DEFERRABLE INITIALLY DEFERRED,
47         org             INT              NOT NULL
48                                              REFERENCES actor.org_unit
49                                              ON DELETE CASCADE
50                                              DEFERRABLE INITIALLY DEFERRED,
51         position        INT              NOT NULL DEFAULT 0,
52         CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
53 );
54
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)
63 );
64
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)
70 );
71
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(),
80         copy_number     INT,
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) ),
85         age_protect     INT,
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,
91         price           NUMERIC(8,2),
92         barcode         TEXT                            NOT NULL,
93         circ_modifier   TEXT,
94         circ_as_type    TEXT,
95         dummy_title     TEXT,
96         dummy_author    TEXT,
97         alert_message   TEXT,
98         opac_visible    BOOL                            NOT NULL DEFAULT TRUE,
99         deleted         BOOL                            NOT NULL DEFAULT FALSE,
100         floating        INT,
101         dummy_isbn      TEXT,
102         status_changed_time TIMESTAMP WITH TIME ZONE,
103         active_date TIMESTAMP WITH TIME ZONE,
104         mint_condition      BOOL        NOT NULL DEFAULT TRUE,
105     cost    NUMERIC(8,2)
106 );
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;
115
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
120 );
121 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
122
123 CREATE TABLE asset.last_copy_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(),
127     copy                        BIGINT                                  NOT NULL
128 );
129 CREATE INDEX last_copy_inventory_copy_idx ON asset.last_copy_inventory (copy);
130
131 CREATE TABLE asset.opac_visible_copies (
132   id        BIGSERIAL primary key,
133   copy_id   BIGINT, -- copy id
134   record    BIGINT,
135   circ_lib  INTEGER
136 );
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.
141 $$;
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);
145
146 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
147 RETURNS TRIGGER AS $$
148 BEGIN
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();
153         END IF;
154     END IF;
155     RETURN NEW;
156 END;
157 $$ LANGUAGE plpgsql;
158
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 $$
162 BEGIN
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();
165     END IF;
166     IF NEW.status_changed_time IS NULL THEN
167         NEW.status_changed_time := now();
168     END IF;
169     RETURN NEW;
170 END;
171 $$ LANGUAGE plpgsql;
172
173 CREATE TRIGGER acp_status_changed_trig
174     BEFORE UPDATE ON asset.copy
175     FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
176
177 CREATE TRIGGER acp_created_trig
178     BEFORE INSERT ON asset.copy
179     FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
180
181 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
182 RETURNS TRIGGER AS $$
183 DECLARE
184     new_copy_location INT;
185 BEGIN
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
188             RETURN NEW;
189         END IF;
190     END IF;
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;
194     END IF;
195     IF new_copy_location IS NOT NULL THEN
196         NEW.location = new_copy_location;
197     END IF;
198     RETURN NEW;
199 END;
200 $$ LANGUAGE plpgsql;
201
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();
205
206 CREATE TABLE asset.stat_cat_sip_fields (
207     field   CHAR(2) PRIMARY KEY,
208     name    TEXT    NOT NULL,
209     one_only BOOL    NOT NULL DEFAULT FALSE
210 );
211 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
212 Asset Statistical Category SIP Fields
213
214 Contains the list of valid SIP Field identifiers for
215 Statistical Categories.
216 $$;
217
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)
224 );
225
226 CREATE TABLE asset.stat_cat (
227         id              SERIAL  PRIMARY KEY,
228         owner           INT     NOT NULL,
229         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
230         name            TEXT    NOT NULL,
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,
233     sip_format  TEXT,
234     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
235         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
236 );
237
238 CREATE TABLE asset.stat_cat_entry (
239         id              SERIAL  PRIMARY KEY,
240         stat_cat        INT     NOT NULL,
241         owner           INT     NOT NULL,
242         value           TEXT    NOT NULL,
243         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
244 );
245
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)
252 );
253 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
254
255 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
256 DECLARE
257     sipfield asset.stat_cat_sip_fields%ROWTYPE;
258     use_count INT;
259 BEGIN
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';
266             END IF;
267         END IF;
268     END IF;
269     RETURN NEW;
270 END;
271 $func$ LANGUAGE PLPGSQL;
272
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();
276
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,
283         title           TEXT                            NOT NULL,
284         value           TEXT                            NOT NULL
285 );
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 );
288
289 CREATE TABLE asset.uri (
290     id  SERIAL  PRIMARY KEY,
291     href    TEXT    NOT NULL,
292     label   TEXT,
293     use_restriction TEXT,
294     active  BOOL    NOT NULL DEFAULT TRUE
295 );
296
297 CREATE TABLE asset.call_number_class (
298     id             bigserial     PRIMARY KEY,
299     name           TEXT          NOT NULL,
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'
302 );
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.
308 $$;
309
310 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
311 DECLARE
312     sortkey        TEXT := '';
313 BEGIN
314     sortkey := NEW.label_sortkey;
315
316     IF NEW.label_class IS NULL THEN
317             NEW.label_class := COALESCE(
318             (
319                 SELECT substring(value from E'\\d+')::integer
320                 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
321             ), 1
322         );
323     END IF;
324
325     EXECUTE 'SELECT ' || acnc.normalizer || '(' || 
326        quote_literal( NEW.label ) || ')'
327        FROM asset.call_number_class acnc
328        WHERE acnc.id = NEW.label_class
329        INTO sortkey;
330     NEW.label_sortkey = sortkey;
331     RETURN NEW;
332 END;
333 $func$ LANGUAGE PLPGSQL;
334
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
342
343     use strict;
344     use warnings;
345
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;
358
359     return $callnum;
360 $func$ LANGUAGE PLPERLU IMMUTABLE;
361
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
366
367     use strict;
368     use warnings;
369
370     my $init = uc(shift);
371     $init =~ s/^\s+//;
372     $init =~ s/\s+$//;
373     $init =~ s!/!!g;
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;
383             }
384             if (2 == $digit_group_count) {
385                 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
386                 $tokens[$i] =~ tr/ /0/;
387             }
388         }
389     }
390     # Pad the first digit_group if there was only one
391     if (1 == $digit_group_count) {
392         $tokens[$first_digit_group_idx] .= '_000000000000000'
393     }
394     my $key = join("_", @tokens);
395     $key =~ s/[^\p{IsAlnum}_]//g;
396
397     return $key;
398
399 $func$ LANGUAGE PLPERLU IMMUTABLE;
400
401
402 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
403     use strict;
404     use warnings;
405
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;
409
410     my $callnum = Library::CallNumber::LC->new(shift);
411     return $callnum->normalize();
412
413 $func$ LANGUAGE PLPERLU IMMUTABLE;
414
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')
419 ;
420
421 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
422 BEGIN
423     NEW.label_sortkey := REGEXP_REPLACE(
424         evergreen.lpad_number_substrings(
425             naco_normalize(NEW.label),
426             '0',
427             10
428         ),
429         E'\\s+',
430         '',
431         'g'
432     );
433     RETURN NEW;
434 END;
435 $$ LANGUAGE PLPGSQL;
436
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
441         label_sortkey   TEXT
442 );
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);
446
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
451         label_sortkey   TEXT
452 );
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);
456
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,
465         label           TEXT                            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,
472         label_sortkey   TEXT
473 );
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();
486
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)
492 );
493 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
494
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,
501         title           TEXT                            NOT NULL,
502         value           TEXT                            NOT NULL
503 );
504 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
505
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(),
519         name           TEXT     NOT NULL,
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)),
532         age_protect    INT,
533         circulate      BOOL,
534         deposit        BOOL,
535         ref            BOOL,
536         holdable       BOOL,
537         deposit_amount NUMERIC(6,2),
538         price          NUMERIC(8,2),
539         circ_modifier  TEXT,
540         circ_as_type   TEXT,
541         alert_message  TEXT,
542         opac_visible   BOOL,
543         floating       INT,
544         mint_condition BOOL
545 );
546
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[]
552 );
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);
555
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$
557 DECLARE
558     ans RECORD;
559     trans INT;
560 BEGIN
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;
562
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
564         RETURN QUERY
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)
568         SELECT  ans.depth,
569                 ans.id,
570                 COUNT( av.id ),
571                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
572                 COUNT( av.id ),
573                 trans
574           FROM  mask,
575                 available_statuses,
576                 org_list,
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
581           GROUP BY 1,2,6;
582
583         IF NOT FOUND THEN
584             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
585         END IF;
586
587     END LOOP;
588
589     RETURN;
590 END;
591 $f$ LANGUAGE PLPGSQL;
592
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$
594 DECLARE
595     ans RECORD;
596     trans INT;
597 BEGIN
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;
599
600     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
601         RETURN QUERY
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)
605         SELECT  -1,
606                 ans.id,
607                 COUNT( av.id ),
608                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
609                 COUNT( av.id ),
610                 trans
611           FROM  mask,
612                 org_list,
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
616           GROUP BY 1,2,6;
617
618         IF NOT FOUND THEN
619             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
620         END IF;
621
622     END LOOP;   
623                 
624     RETURN;     
625 END;            
626 $f$ LANGUAGE PLPGSQL;
627
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)
630  LANGUAGE plpgsql
631 AS $function$
632 DECLARE
633     ans RECORD;
634     trans INT;
635 BEGIN
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;
637
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
639         RETURN QUERY
640         WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
641             cp AS(
642                 SELECT  cp.id,
643                         (cp.status = ANY (available_statuses.ids))::INT as available,
644                         (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
645                   FROM
646                         available_statuses,
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)
651             ),
652             peer AS (
653                 select  cp.id,
654                         (cp.status = ANY  (available_statuses.ids))::INT as available,
655                         (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
656                 FROM
657                         available_statuses,
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)
662             )
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
665         group by 1,2,6;
666
667         IF NOT FOUND THEN
668             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
669         END IF;
670
671     END LOOP;
672     RETURN;
673 END;
674 $function$;
675
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$
677 DECLARE
678     ans RECORD;
679     trans INT;
680 BEGIN
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;
682
683     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
684         RETURN QUERY
685         SELECT  -1,
686                 ans.id,
687                 COUNT( cp.id ),
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),
690                 trans
691           FROM
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)
696           GROUP BY 1,2,6;
697
698         IF NOT FOUND THEN
699             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
700         END IF;
701
702     END LOOP;
703
704     RETURN;
705 END;
706 $f$ LANGUAGE PLPGSQL;
707
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$
709 BEGIN
710     IF staff IS TRUE THEN
711         IF place > 0 THEN
712             RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
713         ELSE
714             RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
715         END IF;
716     ELSE
717         IF place > 0 THEN
718             RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
719         ELSE
720             RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
721         END IF;
722     END IF;
723
724     RETURN;
725 END;
726 $f$ LANGUAGE PLPGSQL;
727
728 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
729 BEGIN
730     PERFORM 1
731         FROM
732             asset.copy acp
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
736         WHERE
737             acn.record = rid
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()))))
744         LIMIT 1;
745     IF FOUND THEN
746         RETURN true;
747     END IF;
748     RETURN FALSE;
749 END;
750 $f$ LANGUAGE PLPGSQL;
751
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$
753 DECLARE
754     ans RECORD;
755     trans INT;
756 BEGIN
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;
758
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
760         RETURN QUERY
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)
764         SELECT  ans.depth,
765                 ans.id,
766                 COUNT( av.id ),
767                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
768                 COUNT( av.id ),
769                 trans
770           FROM  mask,
771                 org_list,
772                 available_statuses,
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
777           GROUP BY 1,2,6;
778
779         IF NOT FOUND THEN
780             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
781         END IF;
782
783     END LOOP;
784
785     RETURN;
786 END;
787 $f$ LANGUAGE PLPGSQL;
788
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$
790 DECLARE
791     ans RECORD;
792     trans INT;
793 BEGIN
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;
795
796     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
797         RETURN QUERY
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)
801         SELECT  -1,
802                 ans.id,
803                 COUNT( av.id ),
804                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
805                 COUNT( av.id ),
806                 trans
807           FROM  mask,
808                 org_list,
809                 available_statuses,
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
814           GROUP BY 1,2,6;
815
816         IF NOT FOUND THEN
817             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
818         END IF;
819
820     END LOOP;   
821                 
822     RETURN;     
823 END;            
824 $f$ LANGUAGE PLPGSQL;
825
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$
827 DECLARE         
828     ans RECORD; 
829     trans INT;
830 BEGIN
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;
832
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
834         RETURN QUERY
835         SELECT  ans.depth,
836                 ans.id,
837                 COUNT( cp.id ),
838                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
839                 COUNT( cp.id ),
840                 trans
841           FROM
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)
846           GROUP BY 1,2,6;
847
848         IF NOT FOUND THEN
849             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
850         END IF;
851
852     END LOOP;
853
854     RETURN;
855 END;
856 $f$ LANGUAGE PLPGSQL;
857
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$
859 DECLARE
860     ans RECORD;
861     trans INT;
862 BEGIN
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;
864
865     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
866         RETURN QUERY
867         SELECT  -1,
868                 ans.id,
869                 COUNT( cp.id ),
870                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
871                 COUNT( cp.id ),
872                 trans
873           FROM
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)
878           GROUP BY 1,2,6;
879
880         IF NOT FOUND THEN
881             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
882         END IF;
883
884     END LOOP;
885
886     RETURN;
887 END;
888 $f$ LANGUAGE PLPGSQL;
889
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$
891 BEGIN
892     IF staff IS TRUE THEN
893         IF place > 0 THEN
894             RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
895         ELSE
896             RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
897         END IF;
898     ELSE
899         IF place > 0 THEN
900             RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
901         ELSE
902             RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
903         END IF;
904     END IF;
905
906     RETURN;
907 END;
908 $f$ LANGUAGE PLPGSQL;
909
910 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
911 BEGIN
912     PERFORM 1
913         FROM
914             asset.copy acp
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
919         WHERE
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()))))
927         LIMIT 1;
928     IF FOUND THEN
929         RETURN true;
930     END IF;
931     RETURN FALSE;
932 END;
933 $f$ LANGUAGE PLPGSQL;
934
935 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
936 BEGIN
937         IF NEW.barcode LIKE '@@%' THEN
938                 NEW.barcode := '@@' || NEW.id;
939         END IF;
940         RETURN NEW;
941 END;
942 $f$ LANGUAGE PLPGSQL;
943
944 CREATE TRIGGER autogenerate_placeholder_barcode
945         BEFORE INSERT OR UPDATE ON asset.copy
946         FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
947
948 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
949 DECLARE
950     copy_id BIGINT;
951 BEGIN
952     EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
953     PERFORM * FROM asset.copy WHERE id = copy_id;
954     IF NOT FOUND THEN
955         RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
956     END IF;
957     RETURN NULL;
958 END;
959 $F$ LANGUAGE PLPGSQL;
960
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,
965     label           TEXT NOT NULL,
966     value           TEXT NOT NULL,
967     index_vector    tsvector NOT NULL,
968     staff_note      TEXT,
969     pub             BOOLEAN DEFAULT TRUE,
970     owner           INTEGER NOT NULL REFERENCES actor.org_unit (id)
971 );
972
973 CREATE INDEX asset_copy_tag_label_idx
974     ON asset.copy_tag (label);
975 CREATE INDEX asset_copy_tag_label_lower_idx
976     ON asset.copy_tag (evergreen.lowercase(label));
977 CREATE INDEX asset_copy_tag_index_vector_idx
978     ON asset.copy_tag
979     USING GIN(index_vector);
980 CREATE INDEX asset_copy_tag_tag_type_idx
981     ON asset.copy_tag (tag_type);
982 CREATE INDEX asset_copy_tag_owner_idx
983     ON asset.copy_tag (owner);
984
985 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
986 BEGIN
987     IF NEW.value IS NULL THEN
988         NEW.value = NEW.label;        
989     END IF;
990
991     RETURN NEW;
992 END;
993 $$ LANGUAGE 'plpgsql';
994
995 -- name of following trigger chosen to ensure it runs first
996 CREATE TRIGGER asset_copy_tag_do_value
997     BEFORE INSERT OR UPDATE ON asset.copy_tag
998     FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
999 CREATE TRIGGER asset_copy_tag_fti_trigger
1000     BEFORE UPDATE OR INSERT ON asset.copy_tag
1001     FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
1002
1003 CREATE TABLE asset.copy_tag_copy_map (
1004     id              BIGSERIAL PRIMARY KEY,
1005     copy            BIGINT,
1006     tag             INTEGER REFERENCES asset.copy_tag (id)
1007                     ON UPDATE CASCADE ON DELETE CASCADE
1008 );
1009
1010 CREATE INDEX asset_copy_tag_copy_map_copy_idx
1011     ON asset.copy_tag_copy_map (copy);
1012 CREATE INDEX asset_copy_tag_copy_map_tag_idx
1013     ON asset.copy_tag_copy_map (tag);
1014
1015 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
1016 DECLARE
1017     last_circ_stop      TEXT;
1018     the_copy        asset.copy%ROWTYPE;
1019 BEGIN
1020
1021     SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
1022     IF NOT FOUND THEN RETURN NULL; END IF;
1023
1024     IF the_copy.status = 3 THEN -- Lost
1025         RETURN 'LOST';
1026     ELSIF the_copy.status = 4 THEN -- Missing
1027         RETURN 'MISSING';
1028     ELSIF the_copy.status = 14 THEN -- Damaged
1029         RETURN 'DAMAGED';
1030     ELSIF the_copy.status = 17 THEN -- Lost and paid
1031         RETURN 'LOST_AND_PAID';
1032     END IF;
1033
1034     SELECT stop_fines INTO last_circ_stop
1035       FROM  action.circulation
1036       WHERE target_copy = cid AND checkin_time IS NULL
1037       ORDER BY xact_start DESC LIMIT 1;
1038
1039     IF FOUND THEN
1040         IF last_circ_stop IN (
1041             'CLAIMSNEVERCHECKEDOUT',
1042             'CLAIMSRETURNED',
1043             'LONGOVERDUE'
1044         ) THEN
1045             RETURN last_circ_stop;
1046         END IF;
1047     END IF;
1048
1049     RETURN 'NORMAL';
1050 END;
1051 $$ LANGUAGE PLPGSQL;
1052
1053 CREATE TYPE config.copy_alert_type_state AS ENUM (
1054     'NORMAL',
1055     'LOST',
1056     'LOST_AND_PAID',
1057     'MISSING',
1058     'DAMAGED',
1059     'CLAIMSRETURNED',
1060     'LONGOVERDUE',
1061     'CLAIMSNEVERCHECKEDOUT'
1062 );
1063
1064 CREATE TYPE config.copy_alert_type_event AS ENUM (
1065     'CHECKIN',
1066     'CHECKOUT'
1067 );
1068
1069 CREATE TABLE config.copy_alert_type (
1070     id      serial  primary key, -- reserve 1-100 for system
1071     scope_org   int not null references actor.org_unit (id) on delete cascade,
1072     active      bool    not null default true,
1073     name        text    not null unique,
1074     state       config.copy_alert_type_state,
1075     event       config.copy_alert_type_event,
1076     in_renew    bool,
1077     invert_location bool    not null default false,
1078     at_circ     bool,
1079     at_owning   bool,
1080     next_status int[]
1081 );
1082 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1083
1084 CREATE TABLE actor.copy_alert_suppress (
1085     id          serial primary key,
1086     org         int not null references actor.org_unit (id) on delete cascade,
1087     alert_type  int not null references config.copy_alert_type (id) on delete cascade
1088 );
1089
1090 CREATE TABLE asset.copy_alert (
1091     id      bigserial   primary key,
1092     alert_type  int     not null references config.copy_alert_type (id) on delete cascade,
1093     copy        bigint  not null,
1094     temp        bool    not null default false,
1095     create_time timestamptz not null default now(),
1096     create_staff    bigint  not null references actor.usr (id) on delete set null,
1097     note        text,
1098     ack_time    timestamptz,
1099     ack_staff   bigint references actor.usr (id) on delete set null
1100 );
1101
1102 CREATE VIEW asset.active_copy_alert AS
1103     SELECT  *
1104       FROM  asset.copy_alert
1105       WHERE ack_time IS NULL;
1106
1107 COMMIT;
1108