20dbc958379fd51334dfd0e30af3a9a6337258a4
[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 ) 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         LIMIT 1;
674     IF FOUND THEN
675         RETURN true;
676     END IF;
677     RETURN FALSE;
678 END;
679 $f$ LANGUAGE PLPGSQL;
680
681 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$
682 DECLARE
683     ans RECORD;
684     trans INT;
685 BEGIN
686     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;
687
688     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
689         RETURN QUERY
690         SELECT  ans.depth,
691                 ans.id,
692                 COUNT( av.id ),
693                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
694                 COUNT( av.id ),
695                 trans
696           FROM  
697                 actor.org_unit_descendants(ans.id) d
698                 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
699                 JOIN asset.copy cp ON (cp.id = av.copy_id)
700                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
701           GROUP BY 1,2,6;
702
703         IF NOT FOUND THEN
704             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
705         END IF;
706
707     END LOOP;
708
709     RETURN;
710 END;
711 $f$ LANGUAGE PLPGSQL;
712
713 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$
714 DECLARE
715     ans RECORD;
716     trans INT;
717 BEGIN
718     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;
719
720     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
721         RETURN QUERY
722         SELECT  -1,
723                 ans.id,
724                 COUNT( av.id ),
725                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
726                 COUNT( av.id ),
727                 trans
728           FROM
729                 actor.org_unit_descendants(ans.id) d
730                 JOIN asset.opac_visible_copies av ON (av.circ_lib = d.id)
731                 JOIN asset.copy cp ON (cp.id = av.copy_id)
732                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
733           GROUP BY 1,2,6;
734
735         IF NOT FOUND THEN
736             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
737         END IF;
738
739     END LOOP;   
740                 
741     RETURN;     
742 END;            
743 $f$ LANGUAGE PLPGSQL;
744
745 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$
746 DECLARE         
747     ans RECORD; 
748     trans INT;
749 BEGIN
750     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;
751
752     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
753         RETURN QUERY
754         SELECT  ans.depth,
755                 ans.id,
756                 COUNT( cp.id ),
757                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
758                 COUNT( cp.id ),
759                 trans
760           FROM
761                 actor.org_unit_descendants(ans.id) d
762                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
763                 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
764                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
765           GROUP BY 1,2,6;
766
767         IF NOT FOUND THEN
768             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
769         END IF;
770
771     END LOOP;
772
773     RETURN;
774 END;
775 $f$ LANGUAGE PLPGSQL;
776
777 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$
778 DECLARE
779     ans RECORD;
780     trans INT;
781 BEGIN
782     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;
783
784     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
785         RETURN QUERY
786         SELECT  -1,
787                 ans.id,
788                 COUNT( cp.id ),
789                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
790                 COUNT( cp.id ),
791                 trans
792           FROM
793                 actor.org_unit_descendants(ans.id) d
794                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
795                 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
796                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
797           GROUP BY 1,2,6;
798
799         IF NOT FOUND THEN
800             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
801         END IF;
802
803     END LOOP;
804
805     RETURN;
806 END;
807 $f$ LANGUAGE PLPGSQL;
808
809 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$
810 BEGIN
811     IF staff IS TRUE THEN
812         IF place > 0 THEN
813             RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
814         ELSE
815             RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
816         END IF;
817     ELSE
818         IF place > 0 THEN
819             RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
820         ELSE
821             RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
822         END IF;
823     END IF;
824
825     RETURN;
826 END;
827 $f$ LANGUAGE PLPGSQL;
828
829 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
830 BEGIN
831     PERFORM 1
832         FROM
833             asset.copy acp
834             JOIN asset.call_number acn ON acp.call_number = acn.id
835             JOIN asset.copy_location acpl ON acp.location = acpl.id
836             JOIN config.copy_status ccs ON acp.status = ccs.id
837             JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
838         WHERE
839             mmsm.metarecord = rid
840             AND acp.holdable = true
841             AND acpl.holdable = true
842             AND ccs.holdable = true
843             AND acp.deleted = false
844         LIMIT 1;
845     IF FOUND THEN
846         RETURN true;
847     END IF;
848     RETURN FALSE;
849 END;
850 $f$ LANGUAGE PLPGSQL;
851
852 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
853 BEGIN
854         IF NEW.barcode LIKE '@@%' THEN
855                 NEW.barcode := '@@' || NEW.id;
856         END IF;
857         RETURN NEW;
858 END;
859 $f$ LANGUAGE PLPGSQL;
860
861 CREATE TRIGGER autogenerate_placeholder_barcode
862         BEFORE INSERT OR UPDATE ON asset.copy
863         FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
864
865 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
866 DECLARE
867     copy_id BIGINT;
868 BEGIN
869     EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
870     PERFORM * FROM asset.copy WHERE id = copy_id;
871     IF NOT FOUND THEN
872         RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
873     END IF;
874     RETURN NULL;
875 END;
876 $F$ LANGUAGE PLPGSQL;
877
878 COMMIT;
879