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