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