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