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