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 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         CONSTRAINT acl_name_once_per_lib UNIQUE (name, owning_lib)
  33 );
  34
  35 CREATE TABLE asset.copy_location_order
  36 (
  37         id              SERIAL           PRIMARY KEY,
  38         location        INT              NOT NULL
  39                                              REFERENCES asset.copy_location
  40                                              ON DELETE CASCADE
  41                                              DEFERRABLE INITIALLY DEFERRED,
  42         org             INT              NOT NULL
  43                                              REFERENCES actor.org_unit
  44                                              ON DELETE CASCADE
  45                                              DEFERRABLE INITIALLY DEFERRED,
  46         position        INT              NOT NULL DEFAULT 0,
  47         CONSTRAINT acplo_once_per_org UNIQUE ( location, org )
  48 );
  49
  50 CREATE TABLE asset.copy (
  51         id              BIGSERIAL                       PRIMARY KEY,
  52         circ_lib        INT                             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
  53         creator         BIGINT                          NOT NULL,
  54         call_number     BIGINT                          NOT NULL,
  55         editor          BIGINT                          NOT NULL,
  56         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
  57         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
  58         copy_number     INT,
  59         status          INT                             NOT NULL DEFAULT 0 REFERENCES config.copy_status (id) DEFERRABLE INITIALLY DEFERRED,
  60         location        INT                             NOT NULL DEFAULT 1 REFERENCES asset.copy_location (id) DEFERRABLE INITIALLY DEFERRED,
  61         loan_duration   INT                             NOT NULL CHECK ( loan_duration IN (1,2,3) ),
  62         fine_level      INT                             NOT NULL CHECK ( fine_level IN (1,2,3) ),
  63         age_protect     INT,
  64         circulate       BOOL                            NOT NULL DEFAULT TRUE,
  65         deposit         BOOL                            NOT NULL DEFAULT FALSE,
  66         ref             BOOL                            NOT NULL DEFAULT FALSE,
  67         holdable        BOOL                            NOT NULL DEFAULT TRUE,
  68         deposit_amount  NUMERIC(6,2)                    NOT NULL DEFAULT 0.00,
  69         price           NUMERIC(8,2),
  70         barcode         TEXT                            NOT NULL,
  71         circ_modifier   TEXT,
  72         circ_as_type    TEXT,
  73         dummy_title     TEXT,
  74         dummy_author    TEXT,
  75         alert_message   TEXT,
  76         opac_visible    BOOL                            NOT NULL DEFAULT TRUE,
  77         deleted         BOOL                            NOT NULL DEFAULT FALSE,
  78         dummy_isbn      TEXT,
  79         status_changed_time TIMESTAMP WITH TIME ZONE
  80 );
  81 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted IS FALSE;
  82 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
  83 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
  84 CREATE INDEX cp_creator_idx  ON asset.copy ( creator );
  85 CREATE INDEX cp_editor_idx   ON asset.copy ( editor );
  86 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;
  87
  88 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
  89 RETURNS TRIGGER AS $$
  90 BEGIN
  91     IF NEW.status <> OLD.status THEN
  92         NEW.status_changed_time := now();
  93     END IF;
  94     RETURN NEW;
  95 END;
  96 $$ LANGUAGE plpgsql;
  97
  98 CREATE TRIGGER acp_status_changed_trig
  99     BEFORE UPDATE ON asset.copy
 100     FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
 101
 102 CREATE TABLE asset.copy_transparency (
 103         id              SERIAL          PRIMARY KEY,
 104         deposit_amount  NUMERIC(6,2),
 105         owner           INT             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 106         circ_lib        INT             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
 107         loan_duration   INT             CHECK ( loan_duration IN (1,2,3) ),
 108         fine_level      INT             CHECK ( fine_level IN (1,2,3) ),
 109         holdable        BOOL,
 110         circulate       BOOL,
 111         deposit         BOOL,
 112         ref             BOOL,
 113         opac_visible    BOOL,
 114         circ_modifier   TEXT,
 115         circ_as_type    TEXT,
 116         name            TEXT            NOT NULL,
 117         CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
 118 );
 119
 120 CREATE TABLE asset.copy_transparency_map (
 121         id              BIGSERIAL       PRIMARY KEY,
 122         transparency    INT     NOT NULL REFERENCES asset.copy_transparency (id) DEFERRABLE INITIALLY DEFERRED,
 123         target_copy     INT     NOT NULL UNIQUE REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED
 124 );
 125 CREATE INDEX cp_tr_cp_idx ON asset.copy_transparency_map (transparency);
 126
 127 CREATE TABLE asset.stat_cat_entry_transparency_map (
 128         id                      BIGSERIAL       PRIMARY KEY,
 129         stat_cat                INT             NOT NULL, -- needs ON DELETE CASCADE
 130         stat_cat_entry          INT             NOT NULL, -- needs ON DELETE CASCADE
 131         owning_transparency     INT             NOT NULL, -- needs ON DELETE CASCADE
 132         CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
 133 );
 134
 135 CREATE TABLE asset.stat_cat (
 136         id              SERIAL  PRIMARY KEY,
 137         owner           INT     NOT NULL,
 138         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
 139         name            TEXT    NOT NULL,
 140         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 141 );
 142
 143 CREATE TABLE asset.stat_cat_entry (
 144         id              SERIAL  PRIMARY KEY,
 145         stat_cat        INT     NOT NULL,
 146         owner           INT     NOT NULL,
 147         value           TEXT    NOT NULL,
 148         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 149 );
 150
 151 CREATE TABLE asset.stat_cat_entry_copy_map (
 152         id              BIGSERIAL       PRIMARY KEY,
 153         stat_cat        INT             NOT NULL,
 154         stat_cat_entry  INT             NOT NULL,
 155         owning_copy     BIGINT          NOT NULL,
 156         CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
 157 );
 158
 159 CREATE TABLE asset.copy_note (
 160         id              BIGSERIAL                       PRIMARY KEY,
 161         owning_copy     BIGINT                          NOT NULL,
 162         creator         BIGINT                          NOT NULL,
 163         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 164         pub             BOOL                            NOT NULL DEFAULT FALSE,
 165         title           TEXT                            NOT NULL,
 166         value           TEXT                            NOT NULL
 167 );
 168 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
 169
 170 CREATE TABLE asset.uri (
 171     id  SERIAL  PRIMARY KEY,
 172     href    TEXT    NOT NULL,
 173     label   TEXT,
 174     use_restriction TEXT,
 175     active  BOOL    NOT NULL DEFAULT TRUE
 176 );
 177
 178 CREATE TABLE asset.call_number (
 179         id              bigserial PRIMARY KEY,
 180         creator         BIGINT                          NOT NULL,
 181         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 182         editor          BIGINT                          NOT NULL,
 183         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 184         record          bigint                          NOT NULL,
 185         owning_lib      INT                             NOT NULL,
 186         label           TEXT                            NOT NULL,
 187         deleted         BOOL                            NOT NULL DEFAULT FALSE
 188 );
 189 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
 190 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
 191 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
 192 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
 193 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
 194 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
 195 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;
 196
 197 CREATE TABLE asset.uri_call_number_map (
 198     id          BIGSERIAL   PRIMARY KEY,
 199     uri         INT         NOT NULL REFERENCES asset.uri (id),
 200     call_number INT         NOT NULL REFERENCES asset.call_number (id),
 201     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
 202 );
 203 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
 204
 205 CREATE TABLE asset.call_number_note (
 206         id              BIGSERIAL                       PRIMARY KEY,
 207         call_number     BIGINT                          NOT NULL,
 208         creator         BIGINT                          NOT NULL,
 209         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 210         pub             BOOL                            NOT NULL DEFAULT FALSE,
 211         title           TEXT                            NOT NULL,
 212         value           TEXT                            NOT NULL
 213 );
 214 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
 215
 216 CREATE VIEW stats.fleshed_copy AS
 217         SELECT  cp.*,
 218                 CAST(cp.create_date AS DATE) AS create_date_day,
 219                 CAST(cp.edit_date AS DATE) AS edit_date_day,
 220                 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
 221                 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
 222                 cn.label AS call_number_label,
 223                 cn.owning_lib,
 224                 rd.item_lang,
 225                 rd.item_type,
 226                 rd.item_form
 227         FROM    asset.copy cp
 228                 JOIN asset.call_number cn ON (cp.call_number = cn.id)
 229                 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
 230
 231 CREATE VIEW stats.fleshed_call_number AS
 232         SELECT  cn.*,
 233                 CAST(cn.create_date AS DATE) AS create_date_day,
 234                 CAST(cn.edit_date AS DATE) AS edit_date_day,
 235                 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
 236                 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
 237                 rd.item_lang,
 238                 rd.item_type,
 239                 rd.item_form
 240         FROM    asset.call_number cn
 241                 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
 242
 243 COMMIT;
 244