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