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         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         floating                BOOL                            NOT NULL DEFAULT FALSE,
  79         dummy_isbn      TEXT,
  80         status_changed_time TIMESTAMP WITH TIME ZONE,
  81         mint_condition      BOOL        NOT NULL DEFAULT TRUE
  82 );
  83 CREATE UNIQUE INDEX copy_barcode_key ON asset.copy (barcode) WHERE deleted = FALSE OR deleted IS FALSE;
  84 CREATE INDEX cp_cn_idx ON asset.copy (call_number);
  85 CREATE INDEX cp_avail_cn_idx ON asset.copy (call_number);
  86 CREATE INDEX cp_creator_idx  ON asset.copy ( creator );
  87 CREATE INDEX cp_editor_idx   ON asset.copy ( editor );
  88 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;
  89
  90 CREATE OR REPLACE FUNCTION asset.acp_status_changed()
  91 RETURNS TRIGGER AS $$
  92 BEGIN
  93     IF NEW.status <> OLD.status THEN
  94         NEW.status_changed_time := now();
  95     END IF;
  96     RETURN NEW;
  97 END;
  98 $$ LANGUAGE plpgsql;
  99
 100 CREATE TRIGGER acp_status_changed_trig
 101     BEFORE UPDATE ON asset.copy
 102     FOR EACH ROW EXECUTE PROCEDURE asset.acp_status_changed();
 103
 104 CREATE TABLE asset.stat_cat_entry_transparency_map (
 105         id                      BIGSERIAL       PRIMARY KEY,
 106         stat_cat                INT             NOT NULL, -- needs ON DELETE CASCADE
 107         stat_cat_entry          INT             NOT NULL, -- needs ON DELETE CASCADE
 108         owning_transparency     INT             NOT NULL, -- needs ON DELETE CASCADE
 109         CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
 110 );
 111
 112 CREATE TABLE asset.stat_cat (
 113         id              SERIAL  PRIMARY KEY,
 114         owner           INT     NOT NULL,
 115         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
 116         name            TEXT    NOT NULL,
 117         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
 118 );
 119
 120 CREATE TABLE asset.stat_cat_entry (
 121         id              SERIAL  PRIMARY KEY,
 122         stat_cat        INT     NOT NULL,
 123         owner           INT     NOT NULL,
 124         value           TEXT    NOT NULL,
 125         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
 126 );
 127
 128 CREATE TABLE asset.stat_cat_entry_copy_map (
 129         id              BIGSERIAL       PRIMARY KEY,
 130         stat_cat        INT             NOT NULL,
 131         stat_cat_entry  INT             NOT NULL,
 132         owning_copy     BIGINT          NOT NULL,
 133         CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
 134 );
 135 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
 136
 137 CREATE TABLE asset.copy_note (
 138         id              BIGSERIAL                       PRIMARY KEY,
 139         owning_copy     BIGINT                          NOT NULL,
 140         creator         BIGINT                          NOT NULL,
 141         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 142         pub             BOOL                            NOT NULL DEFAULT FALSE,
 143         title           TEXT                            NOT NULL,
 144         value           TEXT                            NOT NULL
 145 );
 146 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
 147
 148 CREATE TABLE asset.uri (
 149     id  SERIAL  PRIMARY KEY,
 150     href    TEXT    NOT NULL,
 151     label   TEXT,
 152     use_restriction TEXT,
 153     active  BOOL    NOT NULL DEFAULT TRUE
 154 );
 155
 156 CREATE TABLE asset.call_number (
 157         id              bigserial PRIMARY KEY,
 158         creator         BIGINT                          NOT NULL,
 159         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 160         editor          BIGINT                          NOT NULL,
 161         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 162         record          bigint                          NOT NULL,
 163         owning_lib      INT                             NOT NULL,
 164         label           TEXT                            NOT NULL,
 165         deleted         BOOL                            NOT NULL DEFAULT FALSE
 166 );
 167 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
 168 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
 169 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
 170 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
 171 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
 172 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE;
 173 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;
 174
 175 CREATE TABLE asset.uri_call_number_map (
 176     id          BIGSERIAL   PRIMARY KEY,
 177     uri         INT         NOT NULL REFERENCES asset.uri (id),
 178     call_number INT         NOT NULL REFERENCES asset.call_number (id),
 179     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
 180 );
 181 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
 182
 183 CREATE TABLE asset.call_number_note (
 184         id              BIGSERIAL                       PRIMARY KEY,
 185         call_number     BIGINT                          NOT NULL,
 186         creator         BIGINT                          NOT NULL,
 187         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
 188         pub             BOOL                            NOT NULL DEFAULT FALSE,
 189         title           TEXT                            NOT NULL,
 190         value           TEXT                            NOT NULL
 191 );
 192 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
 193
 194 CREATE TABLE asset.copy_template (
 195         id             SERIAL   PRIMARY KEY,
 196         owning_lib     INT      NOT NULL
 197                                 REFERENCES actor.org_unit (id)
 198                                 DEFERRABLE INITIALLY DEFERRED,
 199         creator        BIGINT   NOT NULL
 200                                 REFERENCES actor.usr (id)
 201                                 DEFERRABLE INITIALLY DEFERRED,
 202         editor         BIGINT   NOT NULL
 203                                 REFERENCES actor.usr (id)
 204                                 DEFERRABLE INITIALLY DEFERRED,
 205         create_date    TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 206         edit_date      TIMESTAMP WITH TIME ZONE    DEFAULT NOW(),
 207         name           TEXT     NOT NULL,
 208         -- columns above this point are attributes of the template itself
 209         -- columns after this point are attributes of the copy this template modifies/creates
 210         circ_lib       INT      REFERENCES actor.org_unit (id)
 211                                 DEFERRABLE INITIALLY DEFERRED,
 212         status         INT      REFERENCES config.copy_status (id)
 213                                 DEFERRABLE INITIALLY DEFERRED,
 214         location       INT      REFERENCES asset.copy_location (id)
 215                                 DEFERRABLE INITIALLY DEFERRED,
 216         loan_duration  INT      CONSTRAINT valid_loan_duration CHECK (
 217                                     loan_duration IS NULL OR loan_duration IN (1,2,3)),
 218         fine_level     INT      CONSTRAINT valid_fine_level CHECK (
 219                                     fine_level IS NULL OR loan_duration IN (1,2,3)),
 220         age_protect    INT,
 221         circulate      BOOL,
 222         deposit        BOOL,
 223         ref            BOOL,
 224         holdable       BOOL,
 225         deposit_amount NUMERIC(6,2),
 226         price          NUMERIC(8,2),
 227         circ_modifier  TEXT,
 228         circ_as_type   TEXT,
 229         alert_message  TEXT,
 230         opac_visible   BOOL,
 231         floating       BOOL,
 232         mint_condition BOOL
 233 );
 234
 235 CREATE VIEW stats.fleshed_copy AS
 236         SELECT  cp.*,
 237                 CAST(cp.create_date AS DATE) AS create_date_day,
 238                 CAST(cp.edit_date AS DATE) AS edit_date_day,
 239                 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
 240                 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
 241                 cn.label AS call_number_label,
 242                 cn.owning_lib,
 243                 rd.item_lang,
 244                 rd.item_type,
 245                 rd.item_form
 246         FROM    asset.copy cp
 247                 JOIN asset.call_number cn ON (cp.call_number = cn.id)
 248                 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
 249
 250 CREATE VIEW stats.fleshed_call_number AS
 251         SELECT  cn.*,
 252                 CAST(cn.create_date AS DATE) AS create_date_day,
 253                 CAST(cn.edit_date AS DATE) AS edit_date_day,
 254                 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
 255                 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
 256                 rd.item_lang,
 257                 rd.item_type,
 258                 rd.item_form
 259         FROM    asset.call_number cn
 260                 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
 261
 262 COMMIT;
 263