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