Open-ILS/src/sql/Pg/040.schema.asset.sql

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