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