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         deleted         BOOL    NOT NULL DEFAULT FALSE,
  36         url             TEXT
  37 );
  38 CREATE UNIQUE INDEX acl_name_once_per_lib ON asset.copy_location (name, owning_lib) WHERE deleted = FALSE OR deleted IS FALSE;
  39
  40 CREATE TABLE asset.copy_location_order
  41 (
  42         id              SERIAL           PRIMARY KEY,
  43         location        INT              NOT NULL
  44                                              REFERENCES asset.copy_location
  45                                              ON DELETE CASCADE
  46                                              DEFERRABLE INITIALLY DEFERRED,
  47         org             INT              NOT NULL
  48                                              REFERENCES actor.org_unit
  49                                              ON DELETE CASCADE
  50                                              DEFERRABLE INITIALLY DEFERRED,
  51         position        INT              NOT NULL DEFAULT 0,
  52         CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
  53 );
  54
  55 CREATE TABLE asset.copy_location_group (
  56     id              SERIAL  PRIMARY KEY,
  57     name            TEXT    NOT NULL, -- i18n
  58     owner           INT     NOT NULL REFERENCES actor.org_unit (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  59     pos             INT     NOT NULL DEFAULT 0,
  60     top             BOOL    NOT NULL DEFAULT FALSE,
  61     opac_visible    BOOL    NOT NULL DEFAULT TRUE,
  62     CONSTRAINT lgroup_once_per_owner UNIQUE (owner,name)
  63 );
  64
  65 CREATE TABLE asset.copy_location_group_map (
  66     id       SERIAL PRIMARY KEY,
  67     location    INT     NOT NULL REFERENCES asset.copy_location (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  68     lgroup      INT     NOT NULL REFERENCES asset.copy_location_group (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
  69     CONSTRAINT  lgroup_once_per_group UNIQUE (lgroup,location)
  70 );
  71
  72 CREATE TABLE asset.copy (
  73         id              BIGSERIAL                       PRIMARY KEY,
  74         circ_lib        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  75         creator         BIGINT                          NOT NULL,
  76         call_number     BIGINT                          NOT NULL,
  77         editor          BIGINT                          NOT NULL,
  78         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
  79         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
  80         copy_number     INT,
  81         status          INT                             NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
  82         location        INT                             NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
  83         loan_duration   INT                             NOT NULL CHECK ( loan_duration IN (1,2,3) ),
  84         fine_level      INT                             NOT NULL CHECK ( fine_level IN (1,2,3) ),
  85         age_protect     INT,
  86         circulate       BOOL                            NOT NULL DEFAULT TRUE,
  87         deposit         BOOL                            NOT NULL DEFAULT FALSE,
  88         ref             BOOL                            NOT NULL DEFAULT FALSE,
  89         holdable        BOOL                            NOT NULL DEFAULT TRUE,
  90         deposit_amount  NUMERIC(6,2)                    NOT NULL DEFAULT 0.00,
  91         price           NUMERIC(8,2),
  92         barcode         TEXT                            NOT NULL,
  93         circ_modifier   TEXT,
  94         circ_as_type    TEXT,
  95         dummy_title     TEXT,
  96         dummy_author    TEXT,
  97         alert_message   TEXT,
  98         opac_visible    BOOL                            NOT NULL DEFAULT TRUE,
  99         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 100         floating        INT,
 101         dummy_isbn      TEXT,
 102         status_changed_time TIMESTAMP WITH TIME ZONE,
 103         active_date TIMESTAMP WITH TIME ZONE,
 104         mint_condition      BOOL        NOT NULL DEFAULT TRUE,
 105     cost    NUMERIC(8,2)
 106 );
 107 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
 108 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
 109 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
 110 CREATE INDEX cp_creator_idx  ON asset.copy ( creator );
 111 CREATE INDEX cp_editor_idx   ON asset.copy ( editor );
 112 CREATE INDEX cp_create_date  ON asset.copy (create_date);
 113 CREATE INDEX cp_available_by_circ_lib_idx ON asset.copy (circ_lib) WHERE status IN (0,7);
 114 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;
 115
 116 CREATE TABLE asset.copy_part_map (
 117     id          SERIAL  PRIMARY KEY,
 118     target_copy BIGINT  NOT NULL, -- points o asset.copy
 119     part        INT     NOT NULL REFERENCES biblio.monograph_part (id) ON DELETE CASCADE
 120 );
 121 CREATE UNIQUE INDEX copy_part_map_cp_part_idx ON asset.copy_part_map (target_copy, part);
 122
 123 CREATE TABLE asset.latest_inventory (
 124     id                          SERIAL                      PRIMARY KEY,
 125     inventory_workstation       INTEGER                     REFERENCES actor.workstation (id) DEFERRABLE INITIALLY DEFERRED,
 126     inventory_date              TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 127     copy                        BIGINT                                  NOT NULL
 128 );
 129 CREATE INDEX latest_inventory_copy_idx ON asset.latest_inventory (copy);
 130
 131 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
 132 RETURNS TRIGGER AS $$
 133 BEGIN
 134         IF NEW.status <> OLD.status AND NOT (NEW.status = 0 AND OLD.status = 7) THEN
 135         NEW.status_changed_time := now();
 136         IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
 137             NEW.active_date := now();
 138         END IF;
 139     END IF;
 140     RETURN NEW;
 141 END;
 142 $$ LANGUAGE plpgsql;
 143
 144 -- Need to check on initial create. Fast adds, manual edit of status at create, etc.
 145 CREATE OR REPLACE FUNCTION asset.acp_created()
 146 RETURNS TRIGGER AS $$
 147 BEGIN
 148     IF NEW.active_date IS NULL AND NEW.status IN (SELECT id FROM config.copy_status WHERE copy_active = true) THEN
 149         NEW.active_date := now();
 150     END IF;
 151     IF NEW.status_changed_time IS NULL THEN
 152         NEW.status_changed_time := now();
 153     END IF;
 154     RETURN NEW;
 155 END;
 156 $$ LANGUAGE plpgsql;
 157
 158 CREATE TRIGGER acp_status_changed_trig
 159     BEFORE UPDATE ON asset.copy
 160     FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
 161
 162 CREATE TRIGGER acp_created_trig
 163     BEFORE INSERT ON asset.copy
 164     FOR EACH ROW EXECUTE PROCEDURE asset.acp_created();
 165
 166 CREATE OR REPLACE FUNCTION asset.acp_location_fixer()
 167 RETURNS TRIGGER AS $$
 168 DECLARE
 169     new_copy_location INT;
 170 BEGIN
 171     IF (TG_OP = 'UPDATE') THEN
 172         IF NEW.location = OLD.location AND NEW.call_number = OLD.call_number AND NEW.circ_lib = OLD.circ_lib THEN
 173             RETURN NEW;
 174         END IF;
 175     END IF;
 176     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 deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
 177     IF new_copy_location IS NULL THEN
 178         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 deleted IS FALSE AND name = (SELECT name FROM asset.copy_location WHERE id = NEW.location) ORDER BY distance LIMIT 1;
 179     END IF;
 180     IF new_copy_location IS NOT NULL THEN
 181         NEW.location = new_copy_location;
 182     END IF;
 183     RETURN NEW;
 184 END;
 185 $$ LANGUAGE plpgsql;
 186
 187 CREATE TRIGGER acp_location_fixer_trig
 188     BEFORE INSERT OR UPDATE OF location, call_number, circ_lib ON asset.copy
 189     FOR EACH ROW EXECUTE PROCEDURE asset.acp_location_fixer();
 190
 191 CREATE TABLE asset.stat_cat_sip_fields (
 192     field   CHAR(2) PRIMARY KEY,
 193     name    TEXT    NOT NULL,
 194     one_only BOOL    NOT NULL DEFAULT FALSE
 195 );
 196 COMMENT ON TABLE asset.stat_cat_sip_fields IS $$
 197 Asset Statistical Category SIP Fields
 198
 199 Contains the list of valid SIP Field identifiers for
 200 Statistical Categories.
 201 $$;
 202
 203 CREATE TABLE asset.stat_cat_entry_transparency_map (
 204         id                      BIGSERIAL       PRIMARY KEY,
 205         stat_cat                INT             NOT NULL, -- needs ON DELETE CASCADE
 206         stat_cat_entry          INT             NOT NULL, -- needs ON DELETE CASCADE
 207         owning_transparency     INT             NOT NULL, -- needs ON DELETE CASCADE
 208         CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
 209 );
 210
 211 CREATE TABLE asset.stat_cat (
 212         id              SERIAL  PRIMARY KEY,
 213         owner           INT     NOT NULL,
 214         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
 215         name            TEXT    NOT NULL,
 216         required        BOOL    NOT NULL DEFAULT FALSE,
 217     sip_field   CHAR(2) REFERENCES asset.stat_cat_sip_fields(field) ON UPDATE CASCADE ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED,
 218     sip_format  TEXT,
 219     checkout_archive    BOOL NOT NULL DEFAULT FALSE,
 220         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 221 );
 222
 223 CREATE TABLE asset.stat_cat_entry (
 224         id              SERIAL  PRIMARY KEY,
 225         stat_cat        INT     NOT NULL,
 226         owner           INT     NOT NULL,
 227         value           TEXT    NOT NULL,
 228         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 229 );
 230
 231 CREATE TABLE asset.stat_cat_entry_copy_map (
 232         id              BIGSERIAL       PRIMARY KEY,
 233         stat_cat        INT             NOT NULL,
 234         stat_cat_entry  INT             NOT NULL,
 235         owning_copy     BIGINT          NOT NULL,
 236         CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
 237 );
 238 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
 239
 240 CREATE FUNCTION asset.stat_cat_check() RETURNS trigger AS $func$
 241 DECLARE
 242     sipfield asset.stat_cat_sip_fields%ROWTYPE;
 243     use_count INT;
 244 BEGIN
 245     IF NEW.sip_field IS NOT NULL THEN
 246         SELECT INTO sipfield * FROM asset.stat_cat_sip_fields WHERE field = NEW.sip_field;
 247         IF sipfield.one_only THEN
 248             SELECT INTO use_count count(id) FROM asset.stat_cat WHERE sip_field = NEW.sip_field AND id != NEW.id;
 249             IF use_count > 0 THEN
 250                 RAISE EXCEPTION 'Sip field cannot be used twice';
 251             END IF;
 252         END IF;
 253     END IF;
 254     RETURN NEW;
 255 END;
 256 $func$ LANGUAGE PLPGSQL;
 257
 258 CREATE TRIGGER asset_stat_cat_sip_update_trigger
 259     BEFORE INSERT OR UPDATE ON asset.stat_cat FOR EACH ROW
 260     EXECUTE PROCEDURE asset.stat_cat_check();
 261
 262 CREATE TABLE asset.copy_note (
 263         id              BIGSERIAL                       PRIMARY KEY,
 264         owning_copy     BIGINT                          NOT NULL,
 265         creator         BIGINT                          NOT NULL,
 266         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 267         pub             BOOL                            NOT NULL DEFAULT FALSE,
 268         title           TEXT                            NOT NULL,
 269         value           TEXT                            NOT NULL
 270 );
 271 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
 272 CREATE INDEX asset_copy_note_owning_copy_idx ON asset.copy_note ( owning_copy );
 273
 274 CREATE TABLE asset.uri (
 275     id  SERIAL  PRIMARY KEY,
 276     href    TEXT    NOT NULL,
 277     label   TEXT,
 278     use_restriction TEXT,
 279     active  BOOL    NOT NULL DEFAULT TRUE
 280 );
 281
 282 CREATE TABLE asset.call_number_class (
 283     id             bigserial     PRIMARY KEY,
 284     name           TEXT          NOT NULL,
 285     normalizer     TEXT          NOT NULL DEFAULT 'asset.normalize_generic',
 286     field          TEXT          NOT NULL DEFAULT '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'
 287 );
 288 COMMENT ON TABLE asset.call_number_class IS $$
 289 Defines the call number normalization database functions in the "normalizer"
 290 column and the tag/subfield combinations to use to lookup the call number in
 291 the "field" column for a given classification scheme. Tag/subfield combinations
 292 are delimited by commas.
 293 $$;
 294
 295 CREATE OR REPLACE FUNCTION asset.label_normalizer() RETURNS TRIGGER AS $func$
 296 DECLARE
 297     sortkey        TEXT := '';
 298 BEGIN
 299     sortkey := NEW.label_sortkey;
 300
 301     IF NEW.label_class IS NULL THEN
 302             NEW.label_class := COALESCE(
 303             (
 304                 SELECT substring(value from E'\\d+')::integer
 305                 FROM actor.org_unit_ancestor_setting('cat.default_classification_scheme', NEW.owning_lib)
 306             ), 1
 307         );
 308     END IF;
 309
 310     EXECUTE 'SELECT ' || acnc.normalizer || '(' ||
 311        quote_literal( NEW.label ) || ')'
 312        FROM asset.call_number_class acnc
 313        WHERE acnc.id = NEW.label_class
 314        INTO sortkey;
 315     NEW.label_sortkey = sortkey;
 316     RETURN NEW;
 317 END;
 318 $func$ LANGUAGE PLPGSQL;
 319
 320 CREATE OR REPLACE FUNCTION asset.label_normalizer_generic(TEXT) RETURNS TEXT AS $func$
 321     # Created after looking at the Koha C4::ClassSortRoutine::Generic module,
 322     # thus could probably be considered a derived work, although nothing was
 323     # directly copied - but to err on the safe side of providing attribution:
 324     # Copyright (C) 2007 LibLime
 325     # Copyright (C) 2011 Equinox Software, Inc (Steve Callendar)
 326     # Licensed under the GPL v2 or later
 327
 328     use strict;
 329     use warnings;
 330
 331     # Converts the callnumber to uppercase
 332     # Strips spaces from start and end of the call number
 333     # Converts anything other than letters, digits, and periods into spaces
 334     # Collapses multiple spaces into a single underscore
 335     my $callnum = uc(shift);
 336     $callnum =~ s/^\s//g;
 337     $callnum =~ s/\s$//g;
 338     # NOTE: this previously used underscores, but this caused sorting issues
 339     # for the "before" half of page 0 on CN browse, sorting CNs containing a
 340     # decimal before "whole number" CNs
 341     $callnum =~ s/[^A-Z0-9_.]/ /g;
 342     $callnum =~ s/ {2,}/ /g;
 343
 344     return $callnum;
 345 $func$ LANGUAGE PLPERLU IMMUTABLE;
 346
 347 CREATE OR REPLACE FUNCTION asset.label_normalizer_dewey(TEXT) RETURNS TEXT AS $func$
 348     # Derived from the Koha C4::ClassSortRoutine::Dewey module
 349     # Copyright (C) 2007 LibLime
 350     # Licensed under the GPL v2 or later
 351
 352     use strict;
 353     use warnings;
 354
 355     my $init = uc(shift);
 356     $init =~ s/^\s+//;
 357     $init =~ s/\s+$//;
 358     $init =~ s!/!!g;
 359     $init =~ s/^([\p{IsAlpha}]+)/$1 /;
 360     my @tokens = split /\.|\s+/, $init;
 361     my $digit_group_count = 0;
 362     my $first_digit_group_idx;
 363     for (my $i = 0; $i <= $#tokens; $i++) {
 364         if ($tokens[$i] =~ /^\d+$/) {
 365             $digit_group_count++;
 366             if ($digit_group_count == 1) {
 367                 $first_digit_group_idx = $i;
 368             }
 369             if (2 == $digit_group_count) {
 370                 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
 371                 $tokens[$i] =~ tr/ /0/;
 372             }
 373         }
 374     }
 375     # Pad the first digit_group if there was only one
 376     if (1 == $digit_group_count) {
 377         $tokens[$first_digit_group_idx] .= '_000000000000000'
 378     }
 379     my $key = join("_", @tokens);
 380     $key =~ s/[^\p{IsAlnum}_]//g;
 381
 382     return $key;
 383
 384 $func$ LANGUAGE PLPERLU IMMUTABLE;
 385
 386
 387 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
 388     use strict;
 389     use warnings;
 390
 391     # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
 392     # The author hopes to upload it to CPAN some day, which would make our lives easier
 393     use Library::CallNumber::LC;
 394
 395     my $callnum = Library::CallNumber::LC->new(shift);
 396     return $callnum->normalize();
 397
 398 $func$ LANGUAGE PLPERLU IMMUTABLE;
 399
 400 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
 401     ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
 402     ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
 403     ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
 404 ;
 405
 406 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
 407 BEGIN
 408     NEW.label_sortkey := REGEXP_REPLACE(
 409         evergreen.lpad_number_substrings(
 410             naco_normalize(NEW.label),
 411             '0',
 412             10
 413         ),
 414         E'\\s+',
 415         '',
 416         'g'
 417     );
 418     RETURN NEW;
 419 END;
 420 $$ LANGUAGE PLPGSQL;
 421
 422 CREATE TABLE asset.call_number_prefix (
 423         id                      SERIAL   PRIMARY KEY,
 424         owning_lib          INT                 NOT NULL REFERENCES actor.org_unit (id),
 425         label               TEXT                NOT NULL, -- i18n
 426         label_sortkey   TEXT
 427 );
 428 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
 429 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
 430 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
 431
 432 CREATE TABLE asset.call_number_suffix (
 433         id                      SERIAL   PRIMARY KEY,
 434         owning_lib          INT                 NOT NULL REFERENCES actor.org_unit (id),
 435         label               TEXT                NOT NULL, -- i18n
 436         label_sortkey   TEXT
 437 );
 438 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
 439 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
 440 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
 441
 442 CREATE TABLE asset.call_number (
 443         id              bigserial PRIMARY KEY,
 444         creator         BIGINT                          NOT NULL,
 445         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 446         editor          BIGINT                          NOT NULL,
 447         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 448         record          bigint                          NOT NULL,
 449         owning_lib      INT                                 NOT NULL,
 450         label           TEXT                            NOT NULL,
 451         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 452         prefix          INT                                 NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
 453         suffix          INT                                 NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
 454         label_class     BIGINT                          NOT NULL
 455                                                         REFERENCES asset.call_number_class(id)
 456                                                         DEFERRABLE INITIALLY DEFERRED,
 457         label_sortkey   TEXT
 458 );
 459 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
 460 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
 461 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
 462 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
 463 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
 464 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
 465 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;
 466 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;
 467 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;
 468 CREATE RULE protect_acn_id_neg1 AS ON UPDATE TO asset.call_number WHERE OLD.id = -1 DO INSTEAD NOTHING;
 469
 470 CREATE TRIGGER asset_label_sortkey_trigger
 471     BEFORE UPDATE OR INSERT ON asset.call_number
 472     FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
 473
 474 CREATE TABLE asset.uri_call_number_map (
 475     id          BIGSERIAL   PRIMARY KEY,
 476     uri         INT         NOT NULL REFERENCES asset.uri (id),
 477     call_number INT         NOT NULL REFERENCES asset.call_number (id),
 478     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
 479 );
 480 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
 481
 482 CREATE TABLE asset.call_number_note (
 483         id              BIGSERIAL                       PRIMARY KEY,
 484         call_number     BIGINT                          NOT NULL,
 485         creator         BIGINT                          NOT NULL,
 486         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 487         pub             BOOL                            NOT NULL DEFAULT FALSE,
 488         title           TEXT                            NOT NULL,
 489         value           TEXT                            NOT NULL
 490 );
 491 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
 492
 493 CREATE TABLE asset.copy_template (
 494         id             SERIAL   PRIMARY KEY,
 495         owning_lib     INT      NOT NULL
 496                                 REFERENCES actor.org_unit (id)
 497                                 DEFERRABLE INITIALLY DEFERRED,
 498         creator        BIGINT   NOT NULL
 499                                 REFERENCES actor.usr (id)
 500                                 DEFERRABLE INITIALLY DEFERRED,
 501         editor         BIGINT   NOT NULL
 502                                 REFERENCES actor.usr (id)
 503                                 DEFERRABLE INITIALLY DEFERRED,
 504         create_date    TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 505         edit_date      TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 506         name           TEXT     NOT NULL,
 507         -- columns above this point are attributes of the template itself
 508         -- columns after this point are attributes of the copy this template modifies/creates
 509         circ_lib       INT      REFERENCES actor.org_unit (id)
 510                                 DEFERRABLE INITIALLY DEFERRED,
 511         status         INT      REFERENCES config.copy_status (id)
 512                                 DEFERRABLE INITIALLY DEFERRED,
 513         location       INT      REFERENCES asset.copy_location (id)
 514                                 DEFERRABLE INITIALLY DEFERRED,
 515         loan_duration  INT      CONSTRAINT valid_loan_duration CHECK (
 516                                     loan_duration IS NULL OR loan_duration IN (1,2,3)),
 517         fine_level     INT      CONSTRAINT valid_fine_level CHECK (
 518                                     fine_level IS NULL OR loan_duration IN (1,2,3)),
 519         age_protect    INT,
 520         circulate      BOOL,
 521         deposit        BOOL,
 522         ref            BOOL,
 523         holdable       BOOL,
 524         deposit_amount NUMERIC(6,2),
 525         price          NUMERIC(8,2),
 526         circ_modifier  TEXT,
 527         circ_as_type   TEXT,
 528         alert_message  TEXT,
 529         opac_visible   BOOL,
 530         floating       INT,
 531         mint_condition BOOL
 532 );
 533
 534 CREATE TABLE asset.copy_vis_attr_cache (
 535     id              BIGSERIAL   PRIMARY KEY,
 536     record          BIGINT      NOT NULL, -- No FKEYs, managed by user triggers.
 537     target_copy     BIGINT      NOT NULL,
 538     vis_attr_vector INT[]
 539 );
 540 CREATE INDEX copy_vis_attr_cache_record_idx ON asset.copy_vis_attr_cache (record);
 541 CREATE INDEX copy_vis_attr_cache_copy_idx ON asset.copy_vis_attr_cache (target_copy);
 542
 543 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$
 544 DECLARE
 545     ans RECORD;
 546     trans INT;
 547 BEGIN
 548     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;
 549
 550     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
 551         RETURN QUERY
 552         WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
 553              available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
 554              mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
 555         SELECT  ans.depth,
 556                 ans.id,
 557                 COUNT( av.id ),
 558                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
 559                 COUNT( av.id ),
 560                 trans
 561           FROM  mask,
 562                 available_statuses,
 563                 org_list,
 564                 asset.copy_vis_attr_cache av
 565                 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
 566                 JOIN asset.call_number cn ON (cp.call_number = cn.id AND not cn.deleted)
 567           WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
 568           GROUP BY 1,2,6;
 569
 570         IF NOT FOUND THEN
 571             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 572         END IF;
 573
 574     END LOOP;
 575
 576     RETURN;
 577 END;
 578 $f$ LANGUAGE PLPGSQL;
 579
 580 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$
 581 DECLARE
 582     ans RECORD;
 583     trans INT;
 584 BEGIN
 585     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;
 586
 587     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 588         RETURN QUERY
 589         WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
 590              available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
 591              mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
 592         SELECT  -1,
 593                 ans.id,
 594                 COUNT( av.id ),
 595                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
 596                 COUNT( av.id ),
 597                 trans
 598           FROM  mask,
 599                 org_list,
 600                 asset.copy_vis_attr_cache av
 601                 JOIN asset.copy cp ON (cp.id = av.target_copy AND av.record = rid)
 602           WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
 603           GROUP BY 1,2,6;
 604
 605         IF NOT FOUND THEN
 606             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 607         END IF;
 608
 609     END LOOP;
 610
 611     RETURN;
 612 END;
 613 $f$ LANGUAGE PLPGSQL;
 614
 615 CREATE OR REPLACE FUNCTION asset.staff_ou_record_copy_count(org integer, rid bigint)
 616  RETURNS TABLE(depth integer, org_unit integer, visible bigint, available bigint, unshadow bigint, transcendant integer)
 617  LANGUAGE plpgsql
 618 AS $function$
 619 DECLARE
 620     ans RECORD;
 621     trans INT;
 622 BEGIN
 623     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;
 624
 625     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
 626         RETURN QUERY
 627         WITH available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
 628             cp AS(
 629                 SELECT  cp.id,
 630                         (cp.status = ANY (available_statuses.ids))::INT as available,
 631                         (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
 632                   FROM
 633                         available_statuses,
 634                         actor.org_unit_descendants(ans.id) d
 635                         JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 636                         JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
 637                         JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
 638             ),
 639             peer AS (
 640                 select  cp.id,
 641                         (cp.status = ANY  (available_statuses.ids))::INT as available,
 642                         (cl.opac_visible AND cp.opac_visible)::INT as opac_visible
 643                 FROM
 644                         available_statuses,
 645                         actor.org_unit_descendants(ans.id) d
 646                         JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 647                         JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
 648                         JOIN biblio.peer_bib_copy_map bp ON (bp.peer_record = rid AND bp.target_copy = cp.id)
 649             )
 650         select ans.depth, ans.id, count(id), sum(x.available::int), sum(x.opac_visible::int), trans
 651         from ((select * from cp) union (select * from peer)) x
 652         group by 1,2,6;
 653
 654         IF NOT FOUND THEN
 655             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 656         END IF;
 657
 658     END LOOP;
 659     RETURN;
 660 END;
 661 $function$;
 662
 663 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$
 664 DECLARE
 665     ans RECORD;
 666     trans INT;
 667 BEGIN
 668     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;
 669
 670     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 671         RETURN QUERY
 672         SELECT  -1,
 673                 ans.id,
 674                 COUNT( cp.id ),
 675                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 676                 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
 677                 trans
 678           FROM
 679                 actor.org_unit_descendants(ans.id) d
 680                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 681                 JOIN asset.copy_location cl ON (cp.location = cl.id AND NOT cl.deleted)
 682                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
 683           GROUP BY 1,2,6;
 684
 685         IF NOT FOUND THEN
 686             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 687         END IF;
 688
 689     END LOOP;
 690
 691     RETURN;
 692 END;
 693 $f$ LANGUAGE PLPGSQL;
 694
 695 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$
 696 BEGIN
 697     IF staff IS TRUE THEN
 698         IF place > 0 THEN
 699             RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
 700         ELSE
 701             RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
 702         END IF;
 703     ELSE
 704         IF place > 0 THEN
 705             RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
 706         ELSE
 707             RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
 708         END IF;
 709     END IF;
 710
 711     RETURN;
 712 END;
 713 $f$ LANGUAGE PLPGSQL;
 714
 715 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
 716 BEGIN
 717     PERFORM 1
 718         FROM
 719             asset.copy acp
 720             JOIN asset.call_number acn ON acp.call_number = acn.id
 721             JOIN asset.copy_location acpl ON acp.location = acpl.id
 722             JOIN config.copy_status ccs ON acp.status = ccs.id
 723         WHERE
 724             acn.record = rid
 725             AND acp.holdable = true
 726             AND acpl.holdable = true
 727             AND ccs.holdable = true
 728             AND acp.deleted = false
 729             AND acpl.deleted = false
 730             AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
 731         LIMIT 1;
 732     IF FOUND THEN
 733         RETURN true;
 734     END IF;
 735     RETURN FALSE;
 736 END;
 737 $f$ LANGUAGE PLPGSQL;
 738
 739 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$
 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.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
 747         RETURN QUERY
 748         WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
 749              available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
 750              mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
 751         SELECT  ans.depth,
 752                 ans.id,
 753                 COUNT( av.id ),
 754                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
 755                 COUNT( av.id ),
 756                 trans
 757           FROM  mask,
 758                 org_list,
 759                 available_statuses,
 760                 asset.copy_vis_attr_cache av
 761                 JOIN asset.copy cp ON (cp.id = av.target_copy)
 762                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
 763           WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
 764           GROUP BY 1,2,6;
 765
 766         IF NOT FOUND THEN
 767             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 768         END IF;
 769
 770     END LOOP;
 771
 772     RETURN;
 773 END;
 774 $f$ LANGUAGE PLPGSQL;
 775
 776 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$
 777 DECLARE
 778     ans RECORD;
 779     trans INT;
 780 BEGIN
 781     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;
 782
 783     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 784         RETURN QUERY
 785         WITH org_list AS (SELECT ARRAY_AGG(id)::BIGINT[] AS orgs FROM actor.org_unit_descendants(ans.id) x),
 786              available_statuses AS (SELECT ARRAY_AGG(id) AS ids FROM config.copy_status WHERE is_available),
 787              mask AS (SELECT c_attrs FROM asset.patron_default_visibility_mask() x)
 788         SELECT  -1,
 789                 ans.id,
 790                 COUNT( av.id ),
 791                 SUM( (cp.status = ANY (available_statuses.ids))::INT ),
 792                 COUNT( av.id ),
 793                 trans
 794           FROM  mask,
 795                 org_list,
 796                 available_statuses,
 797                 asset.copy_vis_attr_cache av
 798                 JOIN asset.copy cp ON (cp.id = av.target_copy)
 799                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = av.record)
 800           WHERE cp.circ_lib = ANY (org_list.orgs) AND av.vis_attr_vector @@ mask.c_attrs::query_int
 801           GROUP BY 1,2,6;
 802
 803         IF NOT FOUND THEN
 804             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 805         END IF;
 806
 807     END LOOP;
 808
 809     RETURN;
 810 END;
 811 $f$ LANGUAGE PLPGSQL;
 812
 813 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$
 814 DECLARE
 815     ans RECORD;
 816     trans INT;
 817 BEGIN
 818     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;
 819
 820     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
 821         RETURN QUERY
 822         SELECT  ans.depth,
 823                 ans.id,
 824                 COUNT( cp.id ),
 825                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 826                 COUNT( cp.id ),
 827                 trans
 828           FROM
 829                 actor.org_unit_descendants(ans.id) d
 830                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 831                 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
 832                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
 833           GROUP BY 1,2,6;
 834
 835         IF NOT FOUND THEN
 836             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 837         END IF;
 838
 839     END LOOP;
 840
 841     RETURN;
 842 END;
 843 $f$ LANGUAGE PLPGSQL;
 844
 845 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$
 846 DECLARE
 847     ans RECORD;
 848     trans INT;
 849 BEGIN
 850     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;
 851
 852     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 853         RETURN QUERY
 854         SELECT  -1,
 855                 ans.id,
 856                 COUNT( cp.id ),
 857                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 858                 COUNT( cp.id ),
 859                 trans
 860           FROM
 861                 actor.org_unit_descendants(ans.id) d
 862                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 863                 JOIN asset.call_number cn ON (cn.id = cp.call_number AND NOT cn.deleted)
 864                 JOIN metabib.metarecord_source_map m ON (m.metarecord = rid AND m.source = cn.record)
 865           GROUP BY 1,2,6;
 866
 867         IF NOT FOUND THEN
 868             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 869         END IF;
 870
 871     END LOOP;
 872
 873     RETURN;
 874 END;
 875 $f$ LANGUAGE PLPGSQL;
 876
 877 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$
 878 BEGIN
 879     IF staff IS TRUE THEN
 880         IF place > 0 THEN
 881             RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
 882         ELSE
 883             RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
 884         END IF;
 885     ELSE
 886         IF place > 0 THEN
 887             RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
 888         ELSE
 889             RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
 890         END IF;
 891     END IF;
 892
 893     RETURN;
 894 END;
 895 $f$ LANGUAGE PLPGSQL;
 896
 897 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT, ou INT DEFAULT NULL) RETURNS BOOL AS $f$
 898 BEGIN
 899     PERFORM 1
 900         FROM
 901             asset.copy acp
 902             JOIN asset.call_number acn ON acp.call_number = acn.id
 903             JOIN asset.copy_location acpl ON acp.location = acpl.id
 904             JOIN config.copy_status ccs ON acp.status = ccs.id
 905             JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
 906         WHERE
 907             mmsm.metarecord = rid
 908             AND acp.holdable = true
 909             AND acpl.holdable = true
 910             AND ccs.holdable = true
 911             AND acp.deleted = false
 912             AND acpl.deleted = false
 913             AND acp.circ_lib IN (SELECT id FROM actor.org_unit_descendants(COALESCE($2,(SELECT id FROM evergreen.org_top()))))
 914         LIMIT 1;
 915     IF FOUND THEN
 916         RETURN true;
 917     END IF;
 918     RETURN FALSE;
 919 END;
 920 $f$ LANGUAGE PLPGSQL;
 921
 922 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
 923 BEGIN
 924         IF NEW.barcode LIKE '@@%' THEN
 925                 NEW.barcode := '@@' || NEW.id;
 926         END IF;
 927         RETURN NEW;
 928 END;
 929 $f$ LANGUAGE PLPGSQL;
 930
 931 CREATE TRIGGER autogenerate_placeholder_barcode
 932         BEFORE INSERT OR UPDATE ON asset.copy
 933         FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
 934
 935 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
 936 DECLARE
 937     copy_id BIGINT;
 938 BEGIN
 939     EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
 940     IF copy_id IS NOT NULL THEN
 941         PERFORM * FROM asset.copy WHERE id = copy_id;
 942         IF NOT FOUND THEN
 943             RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
 944         END IF;
 945     END IF;
 946     RETURN NULL;
 947 END;
 948 $F$ LANGUAGE PLPGSQL;
 949
 950 CREATE TABLE asset.copy_tag (
 951     id              SERIAL PRIMARY KEY,
 952     tag_type        TEXT REFERENCES config.copy_tag_type (code)
 953                     ON UPDATE CASCADE ON DELETE CASCADE,
 954     label           TEXT NOT NULL,
 955     value           TEXT NOT NULL,
 956     index_vector    tsvector NOT NULL,
 957     staff_note      TEXT,
 958     pub             BOOLEAN DEFAULT TRUE,
 959     owner           INTEGER NOT NULL REFERENCES actor.org_unit (id),
 960     url             TEXT
 961 );
 962
 963 CREATE INDEX asset_copy_tag_label_idx
 964     ON asset.copy_tag (label);
 965 CREATE INDEX asset_copy_tag_label_lower_idx
 966     ON asset.copy_tag (evergreen.lowercase(label));
 967 CREATE INDEX asset_copy_tag_index_vector_idx
 968     ON asset.copy_tag
 969     USING GIN(index_vector);
 970 CREATE INDEX asset_copy_tag_tag_type_idx
 971     ON asset.copy_tag (tag_type);
 972 CREATE INDEX asset_copy_tag_owner_idx
 973     ON asset.copy_tag (owner);
 974
 975 CREATE OR REPLACE FUNCTION asset.set_copy_tag_value () RETURNS TRIGGER AS $$
 976 BEGIN
 977     IF NEW.value IS NULL THEN
 978         NEW.value = NEW.label;
 979     END IF;
 980
 981     RETURN NEW;
 982 END;
 983 $$ LANGUAGE 'plpgsql';
 984
 985 -- name of following trigger chosen to ensure it runs first
 986 CREATE TRIGGER asset_copy_tag_do_value
 987     BEFORE INSERT OR UPDATE ON asset.copy_tag
 988     FOR EACH ROW EXECUTE PROCEDURE asset.set_copy_tag_value();
 989 CREATE TRIGGER asset_copy_tag_fti_trigger
 990     BEFORE UPDATE OR INSERT ON asset.copy_tag
 991     FOR EACH ROW EXECUTE PROCEDURE oils_tsearch2('default');
 992
 993 CREATE TABLE asset.copy_tag_copy_map (
 994     id              BIGSERIAL PRIMARY KEY,
 995     copy            BIGINT,
 996     tag             INTEGER REFERENCES asset.copy_tag (id)
 997                     ON UPDATE CASCADE ON DELETE CASCADE
 998 );
 999
1000 CREATE INDEX asset_copy_tag_copy_map_copy_idx
1001     ON asset.copy_tag_copy_map (copy);
1002 CREATE INDEX asset_copy_tag_copy_map_tag_idx
1003     ON asset.copy_tag_copy_map (tag);
1004
1005 CREATE OR REPLACE FUNCTION asset.copy_state (cid BIGINT) RETURNS TEXT AS $$
1006 DECLARE
1007     last_circ_stop      TEXT;
1008     the_copy        asset.copy%ROWTYPE;
1009 BEGIN
1010
1011     SELECT * INTO the_copy FROM asset.copy WHERE id = cid;
1012     IF NOT FOUND THEN RETURN NULL; END IF;
1013
1014     IF the_copy.status = 3 THEN -- Lost
1015         RETURN 'LOST';
1016     ELSIF the_copy.status = 4 THEN -- Missing
1017         RETURN 'MISSING';
1018     ELSIF the_copy.status = 14 THEN -- Damaged
1019         RETURN 'DAMAGED';
1020     ELSIF the_copy.status = 17 THEN -- Lost and paid
1021         RETURN 'LOST_AND_PAID';
1022     END IF;
1023
1024     SELECT stop_fines INTO last_circ_stop
1025       FROM  action.circulation
1026       WHERE target_copy = cid AND checkin_time IS NULL
1027       ORDER BY xact_start DESC LIMIT 1;
1028
1029     IF FOUND THEN
1030         IF last_circ_stop IN (
1031             'CLAIMSNEVERCHECKEDOUT',
1032             'CLAIMSRETURNED',
1033             'LONGOVERDUE'
1034         ) THEN
1035             RETURN last_circ_stop;
1036         END IF;
1037     END IF;
1038
1039     RETURN 'NORMAL';
1040 END;
1041 $$ LANGUAGE PLPGSQL;
1042
1043 CREATE TYPE config.copy_alert_type_state AS ENUM (
1044     'NORMAL',
1045     'LOST',
1046     'LOST_AND_PAID',
1047     'MISSING',
1048     'DAMAGED',
1049     'CLAIMSRETURNED',
1050     'LONGOVERDUE',
1051     'CLAIMSNEVERCHECKEDOUT'
1052 );
1053
1054 CREATE TYPE config.copy_alert_type_event AS ENUM (
1055     'CHECKIN',
1056     'CHECKOUT'
1057 );
1058
1059 CREATE TABLE config.copy_alert_type (
1060     id      serial  primary key, -- reserve 1-100 for system
1061     scope_org   int not null references actor.org_unit (id) on delete cascade,
1062     active      bool    not null default true,
1063     name        text    not null unique,
1064     state       config.copy_alert_type_state,
1065     event       config.copy_alert_type_event,
1066     in_renew    bool,
1067     invert_location bool    not null default false,
1068     at_circ     bool,
1069     at_owning   bool,
1070     next_status int[]
1071 );
1072 SELECT SETVAL('config.copy_alert_type_id_seq'::TEXT, 100);
1073
1074 CREATE TABLE actor.copy_alert_suppress (
1075     id          serial primary key,
1076     org         int not null references actor.org_unit (id) on delete cascade,
1077     alert_type  int not null references config.copy_alert_type (id) on delete cascade
1078 );
1079
1080 CREATE TABLE asset.copy_alert (
1081     id      bigserial   primary key,
1082     alert_type  int     not null references config.copy_alert_type (id) on delete cascade,
1083     copy        bigint  not null,
1084     temp        bool    not null default false,
1085     create_time timestamptz not null default now(),
1086     create_staff    bigint  not null references actor.usr (id) on delete set null,
1087     note        text,
1088     ack_time    timestamptz,
1089     ack_staff   bigint references actor.usr (id) on delete set null
1090 );
1091
1092 CREATE VIEW asset.active_copy_alert AS
1093     SELECT  *
1094       FROM  asset.copy_alert
1095       WHERE ack_time IS NULL;
1096
1097 CREATE TABLE asset.course_module_course (
1098     id              SERIAL PRIMARY KEY,
1099     name            TEXT NOT NULL,
1100     course_number   TEXT NOT NULL,
1101     section_number  TEXT,
1102     owning_lib      INT REFERENCES actor.org_unit (id),
1103     is_archived        BOOLEAN DEFAULT false
1104 );
1105
1106 CREATE TABLE asset.course_module_role (
1107     id              SERIAL  PRIMARY KEY,
1108     name            TEXT    UNIQUE NOT NULL,
1109     is_public       BOOLEAN NOT NULL DEFAULT false
1110 );
1111
1112 CREATE TABLE asset.course_module_course_users (
1113     id              SERIAL PRIMARY KEY,
1114     course          INT NOT NULL REFERENCES asset.course_module_course (id),
1115     usr             INT NOT NULL REFERENCES actor.usr (id),
1116     usr_role        INT REFERENCES asset.course_module_role (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1117 );
1118
1119 CREATE TABLE asset.course_module_course_materials (
1120     id              SERIAL PRIMARY KEY,
1121     course          INT NOT NULL REFERENCES asset.course_module_course (id),
1122     item            INT REFERENCES asset.copy (id),
1123     relationship    TEXT,
1124     record          INT REFERENCES biblio.record_entry (id),
1125     temporary_record       BOOLEAN,
1126     original_location      INT REFERENCES asset.copy_location,
1127     original_status        INT REFERENCES config.copy_status,
1128     original_circ_modifier TEXT, --REFERENCES config.circ_modifier
1129     original_callnumber    INT REFERENCES asset.call_number,
1130     unique (course, item, record)
1131 );
1132
1133 CREATE TABLE asset.course_module_term (
1134     id              SERIAL  PRIMARY KEY,
1135     name            TEXT    UNIQUE NOT NULL,
1136     owning_lib      INT REFERENCES actor.org_unit (id),
1137         start_date      TIMESTAMP WITH TIME ZONE,
1138         end_date        TIMESTAMP WITH TIME ZONE
1139 );
1140
1141 CREATE TABLE asset.course_module_term_course_map (
1142     id              BIGSERIAL  PRIMARY KEY,
1143     term            INT     NOT NULL REFERENCES asset.course_module_term (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
1144     course          INT     NOT NULL REFERENCES asset.course_module_course (id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED
1145 );
1146
1147 COMMIT;
1148