LP#1053397: Add optional org filter to "has holdable copies", and use that in open...
[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         CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
36 );
37
38 CREATE TABLE asset.copy_location_order
39 (
40         id              SERIAL           PRIMARY KEY,
41         location        INT              NOT NULL
42                                              REFERENCES asset.copy_location
43                                              ON DELETE CASCADE
44                                              DEFERRABLE INITIALLY DEFERRED,
45         org             INT              NOT NULL
46                                              REFERENCES actor.org_unit
47                                              ON DELETE CASCADE
48                                              DEFERRABLE INITIALLY DEFERRED,
49         position        INT              NOT NULL DEFAULT 0,
50         CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
51 );
52
53 CREATE TABLE asset.copy_location_group (
54     id              SERIAL  PRIMARY KEY,
55     name            TEXT    NOT NULL, -- i18n
56     owner           INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
57     pos             INT     NOT NULL DEFAULT 0,
58     top             BOOL    NOT NULL DEFAULT FALSE,
59     opac_visible    BOOL    NOT NULL DEFAULT TRUE,
60     CONSTRAINT lgroup_once_per_owner UNIQUE (owner,name)
61 );
62
63 CREATE TABLE asset.copy_location_group_map (
64     id       SERIAL PRIMARY KEY,
65     location    INT     NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
66     lgroup      INT     NOT NULL REFERENCES asset.copy_location_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
67     CONSTRAINT  lgroup_once_per_group UNIQUE (lgroup,location)
68 );
69
70
71 CREATE TABLE asset.copy (
72         id              BIGSERIAL                       PRIMARY KEY,
73         circ_lib        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
74         creator         BIGINT                          NOT NULL,
75         call_number     BIGINT                          NOT NULL,
76         editor          BIGINT                          NOT NULL,
77         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
78         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
79         copy_number     INT,
80         status          INT                             NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
81         location        INT                             NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
82         loan_duration   INT                             NOT NULL CHECK ( loan_duration IN (1,2,3) ),
83         fine_level      INT                             NOT NULL CHECK ( fine_level IN (1,2,3) ),
84         age_protect     INT,
85         circulate       BOOL                            NOT NULL DEFAULT TRUE,
86         deposit         BOOL                            NOT NULL DEFAULT FALSE,
87         ref             BOOL                            NOT NULL DEFAULT FALSE,
88         holdable        BOOL                            NOT NULL DEFAULT TRUE,
89         deposit_amount  NUMERIC(6,2)                    NOT NULL DEFAULT 0.00,
90         price           NUMERIC(8,2),
91         barcode         TEXT                            NOT NULL,
92         circ_modifier   TEXT,
93         circ_as_type    TEXT,
94         dummy_title     TEXT,
95         dummy_author    TEXT,
96         alert_message   TEXT,
97         opac_visible    BOOL                            NOT NULL DEFAULT TRUE,
98         deleted         BOOL                            NOT NULL DEFAULT FALSE,
99         floating        INT,
100         dummy_isbn      TEXT,
101         status_changed_time TIMESTAMP WITH TIME ZONE,
102         active_date TIMESTAMP WITH TIME ZONE,
103         mint_condition      BOOL        NOT NULL DEFAULT TRUE,
104     cost    NUMERIC(8,2)
105 );
106 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
107 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
108 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
109 CREATE INDEX cp_creator_idx  ON asset.copy ( creator );
110 CREATE INDEX cp_editor_idx   ON asset.copy ( editor );
111 CREATE INDEX cp_create_date  ON asset.copy (create_date);
112 CREATE INDEX cp_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7);
113 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;
114
115 CREATE TABLE asset.copy_part_map (
116     id          SERIAL  PRIMARY KEY,
117     target_copy BIGINT  NOT NULL, -- points o asset.copy
118     part        INT     NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
119 );
120 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
121
122 CREATE TABLE asset.opac_visible_copies (
123   id        BIGSERIAL primary key,
124   copy_id   BIGINT, -- copy id
125   record    BIGINT,
126   circ_lib  INTEGER
127 );
128 COMMENT ON TABLE asset.opac_visible_copies IS $$
129 Materialized view of copies that are visible in the OPAC, used by
130 search.query_parser_fts() to speed up OPAC visibility checks on large
131 databases.  Contents are maintained by a set of triggers.
132 $$;
133 CREATE INDEX opac_visible_copies_idx1 on asset.opac_visible_copies (record, circ_lib);
134 CREATE INDEX opac_visible_copies_copy_id_idx on asset.opac_visible_copies (copy_id);
135 CREATE UNIQUE INDEX opac_visible_copies_once_per_record_idx on asset.opac_visible_copies (copy_id, record);
136
137 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
138 RETURNS TRIGGER AS $$
139 BEGIN
140         IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
141         NEW.status_changed_time := now();
142         IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
143             NEW.active_date := now();
144         END IF;
145     END IF;
146     RETURN NEW;
147 END;
148 $$ LANGUAGE plpgsql;
149
150 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
151 CREATE OR REPLACE FUNCTION asset.acp_created()
152 RETURNS TRIGGER AS $$
153 BEGIN
154     IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
155         NEW.active_date := now();
156     END IF;
157     IF NEW.status_changed_time IS NULL THEN
158         NEW.status_changed_time := now();
159     END IF;
160     RETURN NEW;
161 END;
162 $$ LANGUAGE plpgsql;
163
164 CREATE TRIGGER acp_status_changed_trig
165     BEFORE UPDATE ON asset.copy
166     FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
167
168 CREATE TRIGGER acp_created_trig
169     BEFORE INSERT ON asset.copy
170     FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
171
172 CREATE TABLE asset.stat_cat_sip_fields (
173     field   CHAR(2) PRIMARY KEY,
174     name    TEXT    NOT NULL,
175     one_only BOOL    NOT NULL DEFAULT FALSE
176 );
177 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
178 Asset Statistical Category SIP Fields
179
180 Contains the list of valid SIP Field identifiers for
181 Statistical Categories.
182 $$;
183
184 CREATE TABLE asset.stat_cat_entry_transparency_map (
185         id                      BIGSERIAL       PRIMARY KEY,
186         stat_cat                INT             NOT NULL, -- needs ON DELETE CASCADE
187         stat_cat_entry          INT             NOT NULL, -- needs ON DELETE CASCADE
188         owning_transparency     INT             NOT NULL, -- needs ON DELETE CASCADE
189         CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
190 );
191
192 CREATE TABLE asset.stat_cat (
193         id              SERIAL  PRIMARY KEY,
194         owner           INT     NOT NULL,
195         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
196         name            TEXT    NOT NULL,
197         required        BOOL    NOT NULL DEFAULT FALSE,
198     sip_field   CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
199     sip_format  TEXT,
200     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
201         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
202 );
203
204 CREATE TABLE asset.stat_cat_entry (
205         id              SERIAL  PRIMARY KEY,
206         stat_cat        INT     NOT NULL,
207         owner           INT     NOT NULL,
208         value           TEXT    NOT NULL,
209         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
210 );
211
212 CREATE TABLE asset.stat_cat_entry_copy_map (
213         id              BIGSERIAL       PRIMARY KEY,
214         stat_cat        INT             NOT NULL,
215         stat_cat_entry  INT             NOT NULL,
216         owning_copy     BIGINT          NOT NULL,
217         CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
218 );
219 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
220
221 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
222 DECLARE
223     sipfield asset.stat_cat_sip_fields%ROWTYPE;
224     use_count INT;
225 BEGIN
226     IF NEW.sip_field IS NOT NULL THEN
227         SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
228         IF sipfield.one_only THEN
229             SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
230             IF use_count > 0 THEN
231                 RAISE EXCEPTION 'Sip field cannot be used twice';
232             END IF;
233         END IF;
234     END IF;
235     RETURN NEW;
236 END;
237 $func$ LANGUAGE PLPGSQL;
238
239 CREATE TRIGGER asset_stat_cat_sip_update_trigger
240     BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
241     EXECUTE PROCEDURE asset.stat_cat_check();
242
243 CREATE TABLE asset.copy_note (
244         id              BIGSERIAL                       PRIMARY KEY,
245         owning_copy     BIGINT                          NOT NULL,
246         creator         BIGINT                          NOT NULL,
247         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
248         pub             BOOL                            NOT NULL DEFAULT FALSE,
249         title           TEXT                            NOT NULL,
250         value           TEXT                            NOT NULL
251 );
252 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
253 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
254
255 CREATE TABLE asset.uri (
256     id  SERIAL  PRIMARY KEY,
257     href    TEXT    NOT NULL,
258     label   TEXT,
259     use_restriction TEXT,
260     active  BOOL    NOT NULL DEFAULT TRUE
261 );
262
263 CREATE TABLE asset.call_number_class (
264     id             bigserial     PRIMARY KEY,
265     name           TEXT          NOT NULL,
266     normalizer     TEXT          NOT NULL DEFAULT 'asset.normalize_generic',
267     field          TEXT          NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
268 );
269 COMMENT ON TABLE asset.call_number_class IS $$
270 Defines the call number normalization database functions in the "normalizer"
271 column and the tag/subfield combinations to use to lookup the call number in
272 the "field" column for a given classification scheme. Tag/subfield combinations
273 are delimited by commas.
274 $$;
275
276 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
277 DECLARE
278     sortkey        TEXT := '';
279 BEGIN
280     sortkey := NEW.label_sortkey;
281
282     IF NEW.label_class IS NULL THEN
283             NEW.label_class := COALESCE(
284             (
285                 SELECT substring(value from E'\\d+')::integer
286                 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
287             ), 1
288         );
289     END IF;
290
291     EXECUTE 'SELECT ' || acnc.normalizer || '(' || 
292        quote_literal( NEW.label ) || ')'
293        FROM asset.call_number_class acnc
294        WHERE acnc.id = NEW.label_class
295        INTO sortkey;
296     NEW.label_sortkey = sortkey;
297     RETURN NEW;
298 END;
299 $func$ LANGUAGE PLPGSQL;
300
301 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
302     # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
303     # thus could probably be considered a derived work, although nothing was
304     # directly copied - but to err on the safe side of providing attribution:
305     # Copyright (C) 2007 LibLime
306     # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
307     # Licensed under the GPL v2 or later
308
309     use strict;
310     use warnings;
311
312     # Converts the callnumber to uppercase
313     # Strips spaces from start and end of the call number
314     # Converts anything other than letters, digits, and periods into spaces
315     # Collapses multiple spaces into a single underscore
316     my $callnum = uc(shift);
317     $callnum =~ s/^\s//g;
318     $callnum =~ s/\s$//g;
319     # NOTE: this previously used underscores, but this caused sorting issues
320     # for the "before" half of page 0 on CN browse, sorting CNs containing a
321     # decimal before "whole number" CNs
322     $callnum =~ s/[^A-Z0-9_.]/ /g;
323     $callnum =~ s/ {2,}/ /g;
324
325     return $callnum;
326 $func$ LANGUAGE PLPERLU;
327
328 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
329     # Derived from the Koha C4::ClassSortRoutine::Dewey module
330     # Copyright (C) 2007 LibLime
331     # Licensed under the GPL v2 or later
332
333     use strict;
334     use warnings;
335
336     my $init = uc(shift);
337     $init =~ s/^\s+//;
338     $init =~ s/\s+$//;
339     $init =~ s!/!!g;
340     $init =~ s/^([\p{IsAlpha}]+)/$1 /;
341     my @tokens = split /\.|\s+/, $init;
342     my $digit_group_count = 0;
343     my $first_digit_group_idx;
344     for (my $i = 0; $i <= $#tokens; $i++) {
345         if ($tokens[$i] =~ /^\d+$/) {
346             $digit_group_count++;
347             if ($digit_group_count == 1) {
348                 $first_digit_group_idx = $i;
349             }
350             if (2 == $digit_group_count) {
351                 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
352                 $tokens[$i] =~ tr/ /0/;
353             }
354         }
355     }
356     # Pad the first digit_group if there was only one
357     if (1 == $digit_group_count) {
358         $tokens[$first_digit_group_idx] .= '_000000000000000'
359     }
360     my $key = join("_", @tokens);
361     $key =~ s/[^\p{IsAlnum}_]//g;
362
363     return $key;
364
365 $func$ LANGUAGE PLPERLU;
366
367
368 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
369     use strict;
370     use warnings;
371
372     # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
373     # The author hopes to upload it to CPAN some day, which would make our lives easier
374     use Library::CallNumber::LC;
375
376     my $callnum = Library::CallNumber::LC->new(shift);
377     return $callnum->normalize();
378
379 $func$ LANGUAGE PLPERLU;
380
381 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES 
382     ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
383     ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
384     ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
385 ;
386
387 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
388 BEGIN
389     NEW.label_sortkey := REGEXP_REPLACE(
390         evergreen.lpad_number_substrings(
391             naco_normalize(NEW.label),
392             '0',
393             10
394         ),
395         E'\\s+',
396         '',
397         'g'
398     );
399     RETURN NEW;
400 END;
401 $$ LANGUAGE PLPGSQL;
402
403 CREATE TABLE asset.call_number_prefix (
404         id                      SERIAL   PRIMARY KEY,
405         owning_lib          INT                 NOT NULL REFERENCES actor.org_unit (id),
406         label               TEXT                NOT NULL, -- i18n
407         label_sortkey   TEXT
408 );
409 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
410 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
411 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
412
413 CREATE TABLE asset.call_number_suffix (
414         id                      SERIAL   PRIMARY KEY,
415         owning_lib          INT                 NOT NULL REFERENCES actor.org_unit (id),
416         label               TEXT                NOT NULL, -- i18n
417         label_sortkey   TEXT
418 );
419 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
420 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
421 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
422
423 CREATE TABLE asset.call_number (
424         id              bigserial PRIMARY KEY,
425         creator         BIGINT                          NOT NULL,
426         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
427         editor          BIGINT                          NOT NULL,
428         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
429         record          bigint                          NOT NULL,
430         owning_lib      INT                                 NOT NULL,
431         label           TEXT                            NOT NULL,
432         deleted         BOOL                            NOT NULL DEFAULT FALSE,
433         prefix          INT                                 NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
434         suffix          INT                                 NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
435         label_class     BIGINT                          NOT NULL
436                                                         REFERENCES asset.call_number_class(id)
437                                                         DEFERRABLE INITIALLY DEFERRED,
438         label_sortkey   TEXT
439 );
440 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
441 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
442 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
443 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
444 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
445 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
446 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;
447 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;
448 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;
449 CREATE TRIGGER asset_label_sortkey_trigger
450     BEFORE UPDATE OR INSERT ON asset.call_number
451     FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
452
453 CREATE TABLE asset.uri_call_number_map (
454     id          BIGSERIAL   PRIMARY KEY,
455     uri         INT         NOT NULL REFERENCES asset.uri (id),
456     call_number INT         NOT NULL REFERENCES asset.call_number (id),
457     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
458 );
459 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
460
461 CREATE TABLE asset.call_number_note (
462         id              BIGSERIAL                       PRIMARY KEY,
463         call_number     BIGINT                          NOT NULL,
464         creator         BIGINT                          NOT NULL,
465         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
466         pub             BOOL                            NOT NULL DEFAULT FALSE,
467         title           TEXT                            NOT NULL,
468         value           TEXT                            NOT NULL
469 );
470 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
471
472 CREATE TABLE asset.copy_template (
473         id             SERIAL   PRIMARY KEY,
474         owning_lib     INT      NOT NULL
475                                 REFERENCES actor.org_unit (id)
476                                 DEFERRABLE INITIALLY DEFERRED,
477         creator        BIGINT   NOT NULL
478                                 REFERENCES actor.usr (id)
479                                 DEFERRABLE INITIALLY DEFERRED,
480         editor         BIGINT   NOT NULL
481                                 REFERENCES actor.usr (id)
482                                 DEFERRABLE INITIALLY DEFERRED,
483         create_date    TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
484         edit_date      TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
485         name           TEXT     NOT NULL,
486         -- columns above this point are attributes of the template itself
487         -- columns after this point are attributes of the copy this template modifies/creates
488         circ_lib       INT      REFERENCES actor.org_unit (id)
489                                 DEFERRABLE INITIALLY DEFERRED,
490         status         INT      REFERENCES config.copy_status (id)
491                                 DEFERRABLE INITIALLY DEFERRED,
492         location       INT      REFERENCES asset.copy_location (id)
493                                 DEFERRABLE INITIALLY DEFERRED,
494         loan_duration  INT      CONSTRAINT valid_loan_duration CHECK (
495                                     loan_duration IS NULL OR loan_duration IN (1,2,3)),
496         fine_level     INT      CONSTRAINT valid_fine_level CHECK (
497                                     fine_level IS NULL OR loan_duration IN (1,2,3)),
498         age_protect    INT,
499         circulate      BOOL,
500         deposit        BOOL,
501         ref            BOOL,
502         holdable       BOOL,
503         deposit_amount NUMERIC(6,2),
504         price          NUMERIC(8,2),
505         circ_modifier  TEXT,
506         circ_as_type   TEXT,
507         alert_message  TEXT,
508         opac_visible   BOOL,
509         floating       INT,
510         mint_condition BOOL
511 );
512
513 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$
514 DECLARE
515     ans RECORD;
516     trans INT;
517 BEGIN
518     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;
519
520     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
521         RETURN QUERY
522         SELECT  ans.depth,
523                 ans.id,
524                 COUNT( av.id ),
525                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
526                 COUNT( av.id ),
527                 trans
528           FROM  
529                 actor.org_unit_descendants(ans.id) d
530                 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
531                 JOIN asset.copy cp ON (cp.id = av.copy_id)
532           GROUP BY 1,2,6;
533
534         IF NOT FOUND THEN
535             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
536         END IF;
537
538     END LOOP;
539
540     RETURN;
541 END;
542 $f$ LANGUAGE PLPGSQL;
543
544 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$
545 DECLARE
546     ans RECORD;
547     trans INT;
548 BEGIN
549     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;
550
551     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
552         RETURN QUERY
553         SELECT  -1,
554                 ans.id,
555                 COUNT( av.id ),
556                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
557                 COUNT( av.id ),
558                 trans
559           FROM
560                 actor.org_unit_descendants(ans.id) d
561                 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
562                 JOIN asset.copy cp ON (cp.id = av.copy_id)
563           GROUP BY 1,2,6;
564
565         IF NOT FOUND THEN
566             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
567         END IF;
568
569     END LOOP;   
570                 
571     RETURN;     
572 END;            
573 $f$ LANGUAGE PLPGSQL;
574
575 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$
576 DECLARE         
577     ans RECORD; 
578     trans INT;
579 BEGIN           
580     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;
581
582     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
583         RETURN QUERY
584         SELECT  ans.depth,
585                 ans.id,
586                 COUNT( cp.id ),
587                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
588                 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
589                 trans
590           FROM
591                 actor.org_unit_descendants(ans.id) d
592                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
593                 JOIN asset.copy_location cl ON (cp.location = cl.id)
594                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
595           GROUP BY 1,2,6;
596
597         IF NOT FOUND THEN
598             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
599         END IF;
600
601     END LOOP;
602
603     RETURN;
604 END;
605 $f$ LANGUAGE PLPGSQL;
606
607 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$
608 DECLARE
609     ans RECORD;
610     trans INT;
611 BEGIN
612     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;
613
614     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
615         RETURN QUERY
616         SELECT  -1,
617                 ans.id,
618                 COUNT( cp.id ),
619                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
620                 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
621                 trans
622           FROM
623                 actor.org_unit_descendants(ans.id) d
624                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
625                 JOIN asset.copy_location cl ON (cp.location = cl.id)
626                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
627           GROUP BY 1,2,6;
628
629         IF NOT FOUND THEN
630             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
631         END IF;
632
633     END LOOP;
634
635     RETURN;
636 END;
637 $f$ LANGUAGE PLPGSQL;
638
639 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$
640 BEGIN
641     IF staff IS TRUE THEN
642         IF place > 0 THEN
643             RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
644         ELSE
645             RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
646         END IF;
647     ELSE
648         IF place > 0 THEN
649             RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
650         ELSE
651             RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
652         END IF;
653     END IF;
654
655     RETURN;
656 END;
657 $f$ LANGUAGE PLPGSQL;
658
659 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
660 BEGIN
661     PERFORM 1
662         FROM
663             asset.copy acp
664             JOIN asset.call_number acn ON acp.call_number = acn.id
665             JOIN asset.copy_location acpl ON acp.location = acpl.id
666             JOIN config.copy_status ccs ON acp.status = ccs.id
667         WHERE
668             acn.record = rid
669             AND acp.holdable = true
670             AND acpl.holdable = true
671             AND ccs.holdable = true
672             AND acp.deleted = false
673             AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
674         LIMIT 1;
675     IF FOUND THEN
676         RETURN true;
677     END IF;
678     RETURN FALSE;
679 END;
680 $f$ LANGUAGE PLPGSQL;
681
682 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$
683 DECLARE
684     ans RECORD;
685     trans INT;
686 BEGIN
687     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;
688
689     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
690         RETURN QUERY
691         SELECT  ans.depth,
692                 ans.id,
693                 COUNT( av.id ),
694                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
695                 COUNT( av.id ),
696                 trans
697           FROM  
698                 actor.org_unit_descendants(ans.id) d
699                 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
700                 JOIN asset.copy cp ON (cp.id = av.copy_id)
701                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
702           GROUP BY 1,2,6;
703
704         IF NOT FOUND THEN
705             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
706         END IF;
707
708     END LOOP;
709
710     RETURN;
711 END;
712 $f$ LANGUAGE PLPGSQL;
713
714 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$
715 DECLARE
716     ans RECORD;
717     trans INT;
718 BEGIN
719     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;
720
721     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
722         RETURN QUERY
723         SELECT  -1,
724                 ans.id,
725                 COUNT( av.id ),
726                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
727                 COUNT( av.id ),
728                 trans
729           FROM
730                 actor.org_unit_descendants(ans.id) d
731                 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
732                 JOIN asset.copy cp ON (cp.id = av.copy_id)
733                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
734           GROUP BY 1,2,6;
735
736         IF NOT FOUND THEN
737             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
738         END IF;
739
740     END LOOP;   
741                 
742     RETURN;     
743 END;            
744 $f$ LANGUAGE PLPGSQL;
745
746 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$
747 DECLARE         
748     ans RECORD; 
749     trans INT;
750 BEGIN
751     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;
752
753     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
754         RETURN QUERY
755         SELECT  ans.depth,
756                 ans.id,
757                 COUNT( cp.id ),
758                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
759                 COUNT( cp.id ),
760                 trans
761           FROM
762                 actor.org_unit_descendants(ans.id) d
763                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
764                 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
765                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
766           GROUP BY 1,2,6;
767
768         IF NOT FOUND THEN
769             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
770         END IF;
771
772     END LOOP;
773
774     RETURN;
775 END;
776 $f$ LANGUAGE PLPGSQL;
777
778 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$
779 DECLARE
780     ans RECORD;
781     trans INT;
782 BEGIN
783     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;
784
785     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
786         RETURN QUERY
787         SELECT  -1,
788                 ans.id,
789                 COUNT( cp.id ),
790                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
791                 COUNT( cp.id ),
792                 trans
793           FROM
794                 actor.org_unit_descendants(ans.id) d
795                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
796                 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
797                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
798           GROUP BY 1,2,6;
799
800         IF NOT FOUND THEN
801             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
802         END IF;
803
804     END LOOP;
805
806     RETURN;
807 END;
808 $f$ LANGUAGE PLPGSQL;
809
810 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$
811 BEGIN
812     IF staff IS TRUE THEN
813         IF place > 0 THEN
814             RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
815         ELSE
816             RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
817         END IF;
818     ELSE
819         IF place > 0 THEN
820             RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
821         ELSE
822             RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
823         END IF;
824     END IF;
825
826     RETURN;
827 END;
828 $f$ LANGUAGE PLPGSQL;
829
830 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
831 BEGIN
832     PERFORM 1
833         FROM
834             asset.copy acp
835             JOIN asset.call_number acn ON acp.call_number = acn.id
836             JOIN asset.copy_location acpl ON acp.location = acpl.id
837             JOIN config.copy_status ccs ON acp.status = ccs.id
838             JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
839         WHERE
840             mmsm.metarecord = rid
841             AND acp.holdable = true
842             AND acpl.holdable = true
843             AND ccs.holdable = true
844             AND acp.deleted = false
845             AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
846         LIMIT 1;
847     IF FOUND THEN
848         RETURN true;
849     END IF;
850     RETURN FALSE;
851 END;
852 $f$ LANGUAGE PLPGSQL;
853
854 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
855 BEGIN
856         IF NEW.barcode LIKE '@@%' THEN
857                 NEW.barcode := '@@' || NEW.id;
858         END IF;
859         RETURN NEW;
860 END;
861 $f$ LANGUAGE PLPGSQL;
862
863 CREATE TRIGGER autogenerate_placeholder_barcode
864         BEFORE INSERT OR UPDATE ON asset.copy
865         FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
866
867 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
868 DECLARE
869     copy_id BIGINT;
870 BEGIN
871     EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
872     PERFORM * FROM asset.copy WHERE id = copy_id;
873     IF NOT FOUND THEN
874         RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
875     END IF;
876     RETURN NULL;
877 END;
878 $F$ LANGUAGE PLPGSQL;
879
880 COMMIT;
881