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 staged search 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     for (my $i = 0; $i <= $#tokens; $i++) {
 343         if ($tokens[$i] =~ /^\d+$/) {
 344             $digit_group_count++;
 345             if (2 == $digit_group_count) {
 346                 $tokens[$i] = sprintf("%-15.15s", $tokens[$i]);
 347                 $tokens[$i] =~ tr/ /0/;
 348             }
 349         }
 350     }
 351     # Pad the first digit_group if there was only one
 352     if (1 == $digit_group_count) {
 353         $tokens[0] .= '_000000000000000'
 354     }
 355     my $key = join("_", @tokens);
 356     $key =~ s/[^\p{IsAlnum}_]//g;
 357
 358     return $key;
 359
 360 $func$ LANGUAGE PLPERLU;
 361
 362 CREATE OR REPLACE FUNCTION asset.label_normalizer_lc(TEXT) RETURNS TEXT AS $func$
 363     use strict;
 364     use warnings;
 365
 366     # Library::CallNumber::LC is currently hosted at http://code.google.com/p/library-callnumber-lc/
 367     # The author hopes to upload it to CPAN some day, which would make our lives easier
 368     use Library::CallNumber::LC;
 369
 370     my $callnum = Library::CallNumber::LC->new(shift);
 371     return $callnum->normalize();
 372
 373 $func$ LANGUAGE PLPERLU;
 374
 375 INSERT INTO asset.call_number_class (name, normalizer, field) VALUES
 376     ('Generic', 'asset.label_normalizer_generic', '050ab,055ab,060ab,070ab,080ab,082ab,086ab,088ab,090,092,096,098,099'),
 377     ('Dewey (DDC)', 'asset.label_normalizer_dewey', '080ab,082ab,092abef'),
 378     ('Library of Congress (LC)', 'asset.label_normalizer_lc', '050ab,055ab,090abef')
 379 ;
 380
 381 CREATE OR REPLACE FUNCTION asset.normalize_affix_sortkey () RETURNS TRIGGER AS $$
 382 BEGIN
 383     NEW.label_sortkey := REGEXP_REPLACE(
 384         evergreen.lpad_number_substrings(
 385             naco_normalize(NEW.label),
 386             '0',
 387             10
 388         ),
 389         E'\\s+',
 390         '',
 391         'g'
 392     );
 393     RETURN NEW;
 394 END;
 395 $$ LANGUAGE PLPGSQL;
 396
 397 CREATE TABLE asset.call_number_prefix (
 398         id                      SERIAL   PRIMARY KEY,
 399         owning_lib          INT                 NOT NULL REFERENCES actor.org_unit (id),
 400         label               TEXT                NOT NULL, -- i18n
 401         label_sortkey   TEXT
 402 );
 403 CREATE TRIGGER prefix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_prefix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
 404 CREATE UNIQUE INDEX asset_call_number_prefix_once_per_lib ON asset.call_number_prefix (label, owning_lib);
 405 CREATE INDEX asset_call_number_prefix_sortkey_idx ON asset.call_number_prefix (label_sortkey);
 406
 407 CREATE TABLE asset.call_number_suffix (
 408         id                      SERIAL   PRIMARY KEY,
 409         owning_lib          INT                 NOT NULL REFERENCES actor.org_unit (id),
 410         label               TEXT                NOT NULL, -- i18n
 411         label_sortkey   TEXT
 412 );
 413 CREATE TRIGGER suffix_normalize_tgr BEFORE INSERT OR UPDATE ON asset.call_number_suffix FOR EACH ROW EXECUTE PROCEDURE asset.normalize_affix_sortkey();
 414 CREATE UNIQUE INDEX asset_call_number_suffix_once_per_lib ON asset.call_number_suffix (label, owning_lib);
 415 CREATE INDEX asset_call_number_suffix_sortkey_idx ON asset.call_number_suffix (label_sortkey);
 416
 417 CREATE TABLE asset.call_number (
 418         id              bigserial PRIMARY KEY,
 419         creator         BIGINT                          NOT NULL,
 420         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 421         editor          BIGINT                          NOT NULL,
 422         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 423         record          bigint                          NOT NULL,
 424         owning_lib      INT                                 NOT NULL,
 425         label           TEXT                            NOT NULL,
 426         deleted         BOOL                            NOT NULL DEFAULT FALSE,
 427         prefix          INT                                 NOT NULL DEFAULT -1 REFERENCES asset.call_number_prefix(id) DEFERRABLE INITIALLY DEFERRED,
 428         suffix          INT                                 NOT NULL DEFAULT -1 REFERENCES asset.call_number_suffix(id) DEFERRABLE INITIALLY DEFERRED,
 429         label_class     BIGINT                          NOT NULL
 430                                                         REFERENCES asset.call_number_class(id)
 431                                                         DEFERRABLE INITIALLY DEFERRED,
 432         label_sortkey   TEXT
 433 );
 434 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
 435 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
 436 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
 437 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
 438 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib);
 439 CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey));
 440 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;
 441 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;
 442 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;
 443 CREATE TRIGGER asset_label_sortkey_trigger
 444     BEFORE UPDATE OR INSERT ON asset.call_number
 445     FOR EACH ROW EXECUTE PROCEDURE asset.label_normalizer();
 446
 447 CREATE TABLE asset.uri_call_number_map (
 448     id          BIGSERIAL   PRIMARY KEY,
 449     uri         INT         NOT NULL REFERENCES asset.uri (id),
 450     call_number INT         NOT NULL REFERENCES asset.call_number (id),
 451     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
 452 );
 453 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
 454
 455 CREATE TABLE asset.call_number_note (
 456         id              BIGSERIAL                       PRIMARY KEY,
 457         call_number     BIGINT                          NOT NULL,
 458         creator         BIGINT                          NOT NULL,
 459         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 460         pub             BOOL                            NOT NULL DEFAULT FALSE,
 461         title           TEXT                            NOT NULL,
 462         value           TEXT                            NOT NULL
 463 );
 464 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
 465
 466 CREATE TABLE asset.copy_template (
 467         id             SERIAL   PRIMARY KEY,
 468         owning_lib     INT      NOT NULL
 469                                 REFERENCES actor.org_unit (id)
 470                                 DEFERRABLE INITIALLY DEFERRED,
 471         creator        BIGINT   NOT NULL
 472                                 REFERENCES actor.usr (id)
 473                                 DEFERRABLE INITIALLY DEFERRED,
 474         editor         BIGINT   NOT NULL
 475                                 REFERENCES actor.usr (id)
 476                                 DEFERRABLE INITIALLY DEFERRED,
 477         create_date    TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 478         edit_date      TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 479         name           TEXT     NOT NULL,
 480         -- columns above this point are attributes of the template itself
 481         -- columns after this point are attributes of the copy this template modifies/creates
 482         circ_lib       INT      REFERENCES actor.org_unit (id)
 483                                 DEFERRABLE INITIALLY DEFERRED,
 484         status         INT      REFERENCES config.copy_status (id)
 485                                 DEFERRABLE INITIALLY DEFERRED,
 486         location       INT      REFERENCES asset.copy_location (id)
 487                                 DEFERRABLE INITIALLY DEFERRED,
 488         loan_duration  INT      CONSTRAINT valid_loan_duration CHECK (
 489                                     loan_duration IS NULL OR loan_duration IN (1,2,3)),
 490         fine_level     INT      CONSTRAINT valid_fine_level CHECK (
 491                                     fine_level IS NULL OR loan_duration IN (1,2,3)),
 492         age_protect    INT,
 493         circulate      BOOL,
 494         deposit        BOOL,
 495         ref            BOOL,
 496         holdable       BOOL,
 497         deposit_amount NUMERIC(6,2),
 498         price          NUMERIC(8,2),
 499         circ_modifier  TEXT,
 500         circ_as_type   TEXT,
 501         alert_message  TEXT,
 502         opac_visible   BOOL,
 503         floating       BOOL,
 504         mint_condition BOOL
 505 );
 506
 507 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$
 508 DECLARE
 509     ans RECORD;
 510     trans INT;
 511 BEGIN
 512     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;
 513
 514     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
 515         RETURN QUERY
 516         SELECT  ans.depth,
 517                 ans.id,
 518                 COUNT( av.id ),
 519                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 520                 COUNT( av.id ),
 521                 trans
 522           FROM
 523                 actor.org_unit_descendants(ans.id) d
 524                 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
 525                 JOIN asset.copy cp ON (cp.id = av.copy_id)
 526           GROUP BY 1,2,6;
 527
 528         IF NOT FOUND THEN
 529             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 530         END IF;
 531
 532     END LOOP;
 533
 534     RETURN;
 535 END;
 536 $f$ LANGUAGE PLPGSQL;
 537
 538 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$
 539 DECLARE
 540     ans RECORD;
 541     trans INT;
 542 BEGIN
 543     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;
 544
 545     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 546         RETURN QUERY
 547         SELECT  -1,
 548                 ans.id,
 549                 COUNT( av.id ),
 550                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 551                 COUNT( av.id ),
 552                 trans
 553           FROM
 554                 actor.org_unit_descendants(ans.id) d
 555                 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
 556                 JOIN asset.copy cp ON (cp.id = av.copy_id)
 557           GROUP BY 1,2,6;
 558
 559         IF NOT FOUND THEN
 560             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 561         END IF;
 562
 563     END LOOP;
 564
 565     RETURN;
 566 END;
 567 $f$ LANGUAGE PLPGSQL;
 568
 569 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$
 570 DECLARE
 571     ans RECORD;
 572     trans INT;
 573 BEGIN
 574     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;
 575
 576     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
 577         RETURN QUERY
 578         SELECT  ans.depth,
 579                 ans.id,
 580                 COUNT( cp.id ),
 581                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 582                 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
 583                 trans
 584           FROM
 585                 actor.org_unit_descendants(ans.id) d
 586                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 587                 JOIN asset.copy_location cl ON (cp.location = cl.id)
 588                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
 589           GROUP BY 1,2,6;
 590
 591         IF NOT FOUND THEN
 592             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 593         END IF;
 594
 595     END LOOP;
 596
 597     RETURN;
 598 END;
 599 $f$ LANGUAGE PLPGSQL;
 600
 601 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$
 602 DECLARE
 603     ans RECORD;
 604     trans INT;
 605 BEGIN
 606     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;
 607
 608     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 609         RETURN QUERY
 610         SELECT  -1,
 611                 ans.id,
 612                 COUNT( cp.id ),
 613                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 614                 SUM( CASE WHEN cl.opac_visible AND cp.opac_visible THEN 1 ELSE 0 END),
 615                 trans
 616           FROM
 617                 actor.org_unit_descendants(ans.id) d
 618                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 619                 JOIN asset.copy_location cl ON (cp.location = cl.id)
 620                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
 621           GROUP BY 1,2,6;
 622
 623         IF NOT FOUND THEN
 624             RETURN QUERY SELECT -1, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 625         END IF;
 626
 627     END LOOP;
 628
 629     RETURN;
 630 END;
 631 $f$ LANGUAGE PLPGSQL;
 632
 633 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$
 634 BEGIN
 635     IF staff IS TRUE THEN
 636         IF place > 0 THEN
 637             RETURN QUERY SELECT * FROM asset.staff_ou_record_copy_count( place, rid );
 638         ELSE
 639             RETURN QUERY SELECT * FROM asset.staff_lasso_record_copy_count( -place, rid );
 640         END IF;
 641     ELSE
 642         IF place > 0 THEN
 643             RETURN QUERY SELECT * FROM asset.opac_ou_record_copy_count( place, rid );
 644         ELSE
 645             RETURN QUERY SELECT * FROM asset.opac_lasso_record_copy_count( -place, rid );
 646         END IF;
 647     END IF;
 648
 649     RETURN;
 650 END;
 651 $f$ LANGUAGE PLPGSQL;
 652
 653 CREATE OR REPLACE FUNCTION asset.record_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
 654 BEGIN
 655     PERFORM 1
 656         FROM
 657             asset.copy acp
 658             JOIN asset.call_number acn ON acp.call_number = acn.id
 659             JOIN asset.copy_location acpl ON acp.location = acpl.id
 660             JOIN config.copy_status ccs ON acp.status = ccs.id
 661         WHERE
 662             acn.record = rid
 663             AND acp.holdable = true
 664             AND acpl.holdable = true
 665             AND ccs.holdable = true
 666             AND acp.deleted = false
 667         LIMIT 1;
 668     IF FOUND THEN
 669         RETURN true;
 670     END IF;
 671     RETURN FALSE;
 672 END;
 673 $f$ LANGUAGE PLPGSQL;
 674
 675 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$
 676 DECLARE
 677     ans RECORD;
 678     trans INT;
 679 BEGIN
 680     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;
 681
 682     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
 683         RETURN QUERY
 684         SELECT  ans.depth,
 685                 ans.id,
 686                 COUNT( av.id ),
 687                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 688                 COUNT( av.id ),
 689                 trans
 690           FROM
 691                 actor.org_unit_descendants(ans.id) d
 692                 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
 693                 JOIN asset.copy cp ON (cp.id = av.copy_id)
 694                 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
 695           GROUP BY 1,2,6;
 696
 697         IF NOT FOUND THEN
 698             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 699         END IF;
 700
 701     END LOOP;
 702
 703     RETURN;
 704 END;
 705 $f$ LANGUAGE PLPGSQL;
 706
 707 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$
 708 DECLARE
 709     ans RECORD;
 710     trans INT;
 711 BEGIN
 712     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;
 713
 714     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 715         RETURN QUERY
 716         SELECT  -1,
 717                 ans.id,
 718                 COUNT( av.id ),
 719                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 720                 COUNT( av.id ),
 721                 trans
 722           FROM
 723                 actor.org_unit_descendants(ans.id) d
 724                 JOIN asset.opac_visible_copies av ON (av.record = rid AND av.circ_lib = d.id)
 725                 JOIN asset.copy cp ON (cp.id = av.copy_id)
 726                 JOIN metabib.metarecord_source_map m ON (m.source = av.record)
 727           GROUP BY 1,2,6;
 728
 729         IF NOT FOUND THEN
 730             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 731         END IF;
 732
 733     END LOOP;
 734
 735     RETURN;
 736 END;
 737 $f$ LANGUAGE PLPGSQL;
 738
 739 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$
 740 DECLARE
 741     ans RECORD;
 742     trans INT;
 743 BEGIN
 744     SELECT 1 INTO trans FROM biblio.record_entry b JOIN config.bib_source src ON (b.source = src.id) WHERE src.transcendant AND b.id = rid;
 745
 746     FOR ans IN SELECT u.id, t.depth FROM actor.org_unit_ancestors(org) AS u JOIN actor.org_unit_type t ON (u.ou_type = t.id) LOOP
 747         RETURN QUERY
 748         SELECT  ans.depth,
 749                 ans.id,
 750                 COUNT( cp.id ),
 751                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 752                 COUNT( cp.id ),
 753                 trans
 754           FROM
 755                 actor.org_unit_descendants(ans.id) d
 756                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 757                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
 758                 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
 759           GROUP BY 1,2,6;
 760
 761         IF NOT FOUND THEN
 762             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 763         END IF;
 764
 765     END LOOP;
 766
 767     RETURN;
 768 END;
 769 $f$ LANGUAGE PLPGSQL;
 770
 771 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$
 772 DECLARE
 773     ans RECORD;
 774     trans INT;
 775 BEGIN
 776     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;
 777
 778     FOR ans IN SELECT u.org_unit AS id FROM actor.org_lasso_map AS u WHERE lasso = i_lasso LOOP
 779         RETURN QUERY
 780         SELECT  -1,
 781                 ans.id,
 782                 COUNT( cp.id ),
 783                 SUM( CASE WHEN cp.status IN (0,7,12) THEN 1 ELSE 0 END ),
 784                 COUNT( cp.id ),
 785                 trans
 786           FROM
 787                 actor.org_unit_descendants(ans.id) d
 788                 JOIN asset.copy cp ON (cp.circ_lib = d.id AND NOT cp.deleted)
 789                 JOIN asset.call_number cn ON (cn.record = rid AND cn.id = cp.call_number AND NOT cn.deleted)
 790                 JOIN metabib.metarecord_source_map m ON (m.source = cn.record)
 791           GROUP BY 1,2,6;
 792
 793         IF NOT FOUND THEN
 794             RETURN QUERY SELECT ans.depth, ans.id, 0::BIGINT, 0::BIGINT, 0::BIGINT, trans;
 795         END IF;
 796
 797     END LOOP;
 798
 799     RETURN;
 800 END;
 801 $f$ LANGUAGE PLPGSQL;
 802
 803 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$
 804 BEGIN
 805     IF staff IS TRUE THEN
 806         IF place > 0 THEN
 807             RETURN QUERY SELECT * FROM asset.staff_ou_metarecord_copy_count( place, rid );
 808         ELSE
 809             RETURN QUERY SELECT * FROM asset.staff_lasso_metarecord_copy_count( -place, rid );
 810         END IF;
 811     ELSE
 812         IF place > 0 THEN
 813             RETURN QUERY SELECT * FROM asset.opac_ou_metarecord_copy_count( place, rid );
 814         ELSE
 815             RETURN QUERY SELECT * FROM asset.opac_lasso_metarecord_copy_count( -place, rid );
 816         END IF;
 817     END IF;
 818
 819     RETURN;
 820 END;
 821 $f$ LANGUAGE PLPGSQL;
 822
 823 CREATE OR REPLACE FUNCTION asset.metarecord_has_holdable_copy ( rid BIGINT ) RETURNS BOOL AS $f$
 824 BEGIN
 825     PERFORM 1
 826         FROM
 827             asset.copy acp
 828             JOIN asset.call_number acn ON acp.call_number = acn.id
 829             JOIN asset.copy_location acpl ON acp.location = acpl.id
 830             JOIN config.copy_status ccs ON acp.status = ccs.id
 831             JOIN metabib.metarecord_source_map mmsm ON acn.record = mmsm.source
 832         WHERE
 833             mmsm.metarecord = rid
 834             AND acp.holdable = true
 835             AND acpl.holdable = true
 836             AND ccs.holdable = true
 837             AND acp.deleted = false
 838         LIMIT 1;
 839     IF FOUND THEN
 840         RETURN true;
 841     END IF;
 842     RETURN FALSE;
 843 END;
 844 $f$ LANGUAGE PLPGSQL;
 845
 846 CREATE OR REPLACE FUNCTION asset.autogenerate_placeholder_barcode ( ) RETURNS TRIGGER AS $f$
 847 BEGIN
 848         IF NEW.barcode LIKE '@@%' THEN
 849                 NEW.barcode := '@@' || NEW.id;
 850         END IF;
 851         RETURN NEW;
 852 END;
 853 $f$ LANGUAGE PLPGSQL;
 854
 855 CREATE TRIGGER autogenerate_placeholder_barcode
 856         BEFORE INSERT OR UPDATE ON asset.copy
 857         FOR EACH ROW EXECUTE PROCEDURE asset.autogenerate_placeholder_barcode();
 858
 859 CREATE OR REPLACE FUNCTION evergreen.fake_fkey_tgr () RETURNS TRIGGER AS $F$
 860 DECLARE
 861     copy_id BIGINT;
 862 BEGIN
 863     EXECUTE 'SELECT ($1).' || quote_ident(TG_ARGV[0]) INTO copy_id USING NEW;
 864     PERFORM * FROM asset.copy WHERE id = copy_id;
 865     IF NOT FOUND THEN
 866         RAISE EXCEPTION 'Key (%.%=%) does not exist in asset.copy', TG_TABLE_SCHEMA, TG_TABLE_NAME, copy_id;
 867     END IF;
 868     RETURN NULL;
 869 END;
 870 $F$ LANGUAGE PLPGSQL;
 871
 872 COMMIT;
 873