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