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