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