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