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