Patch from Galen Charlton: This resolves a performance issue found by EG Indiana...
[working/Evergreen.git] / 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         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 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.copy_transparency (
105         id              SERIAL          PRIMARY KEY,
106         deposit_amount  NUMERIC(6,2),
107         owner           INT             NOT NULL REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
108         circ_lib        INT             REFERENCES actor.org_unit (id) DEFERRABLE INITIALLY DEFERRED,
109         loan_duration   INT             CHECK ( loan_duration IN (1,2,3) ),
110         fine_level      INT             CHECK ( fine_level IN (1,2,3) ),
111         holdable        BOOL,
112         circulate       BOOL,
113         deposit         BOOL,
114         ref             BOOL,
115         opac_visible    BOOL,
116         circ_modifier   TEXT,
117         circ_as_type    TEXT,
118         name            TEXT            NOT NULL,
119         CONSTRAINT scte_name_once_per_lib UNIQUE (owner,name)
120 );
121
122 CREATE TABLE asset.copy_transparency_map (
123         id              BIGSERIAL       PRIMARY KEY,
124         transparency    INT     NOT NULL REFERENCES asset.copy_transparency (id) DEFERRABLE INITIALLY DEFERRED,
125         target_copy     INT     NOT NULL UNIQUE REFERENCES asset.copy (id) DEFERRABLE INITIALLY DEFERRED
126 );
127 CREATE INDEX cp_tr_cp_idx ON asset.copy_transparency_map (transparency);
128
129 CREATE TABLE asset.stat_cat_entry_transparency_map (
130         id                      BIGSERIAL       PRIMARY KEY,
131         stat_cat                INT             NOT NULL, -- needs ON DELETE CASCADE
132         stat_cat_entry          INT             NOT NULL, -- needs ON DELETE CASCADE
133         owning_transparency     INT             NOT NULL, -- needs ON DELETE CASCADE
134         CONSTRAINT scte_once_per_trans UNIQUE (owning_transparency,stat_cat)
135 );
136
137 CREATE TABLE asset.stat_cat (
138         id              SERIAL  PRIMARY KEY,
139         owner           INT     NOT NULL,
140         opac_visible    BOOL    NOT NULL DEFAULT FALSE,
141         name            TEXT    NOT NULL,
142         CONSTRAINT sc_once_per_owner UNIQUE (owner,name)
143 );
144
145 CREATE TABLE asset.stat_cat_entry (
146         id              SERIAL  PRIMARY KEY,
147         stat_cat        INT     NOT NULL,
148         owner           INT     NOT NULL,
149         value           TEXT    NOT NULL,
150         CONSTRAINT sce_once_per_owner UNIQUE (stat_cat,owner,value)
151 );
152
153 CREATE TABLE asset.stat_cat_entry_copy_map (
154         id              BIGSERIAL       PRIMARY KEY,
155         stat_cat        INT             NOT NULL,
156         stat_cat_entry  INT             NOT NULL,
157         owning_copy     BIGINT          NOT NULL,
158         CONSTRAINT sce_once_per_copy UNIQUE (owning_copy,stat_cat)
159 );
160 CREATE INDEX scecm_owning_copy_idx ON asset.stat_cat_entry_copy_map(owning_copy);
161
162 CREATE TABLE asset.copy_note (
163         id              BIGSERIAL                       PRIMARY KEY,
164         owning_copy     BIGINT                          NOT NULL,
165         creator         BIGINT                          NOT NULL,
166         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
167         pub             BOOL                            NOT NULL DEFAULT FALSE,
168         title           TEXT                            NOT NULL,
169         value           TEXT                            NOT NULL
170 );
171 CREATE INDEX asset_copy_note_creator_idx ON asset.copy_note ( creator );
172
173 CREATE TABLE asset.uri (
174     id  SERIAL  PRIMARY KEY,
175     href    TEXT    NOT NULL,
176     label   TEXT,
177     use_restriction TEXT,
178     active  BOOL    NOT NULL DEFAULT TRUE
179 );
180
181 CREATE TABLE asset.call_number (
182         id              bigserial PRIMARY KEY,
183         creator         BIGINT                          NOT NULL,
184         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
185         editor          BIGINT                          NOT NULL,
186         edit_date       TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
187         record          bigint                          NOT NULL,
188         owning_lib      INT                             NOT NULL,
189         label           TEXT                            NOT NULL,
190         deleted         BOOL                            NOT NULL DEFAULT FALSE
191 );
192 CREATE INDEX asset_call_number_record_idx ON asset.call_number (record);
193 CREATE INDEX asset_call_number_creator_idx ON asset.call_number (creator);
194 CREATE INDEX asset_call_number_editor_idx ON asset.call_number (editor);
195 CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_number_dewey(label));
196 CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (upper(label),id,owning_lib);
197 CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted IS FALSE;
198 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;
199
200 CREATE TABLE asset.uri_call_number_map (
201     id          BIGSERIAL   PRIMARY KEY,
202     uri         INT         NOT NULL REFERENCES asset.uri (id),
203     call_number INT         NOT NULL REFERENCES asset.call_number (id),
204     CONSTRAINT uri_cn_once UNIQUE (uri,call_number)
205 );
206 CREATE INDEX asset_uri_call_number_map_cn_idx ON asset.uri_call_number_map (call_number);
207
208 CREATE TABLE asset.call_number_note (
209         id              BIGSERIAL                       PRIMARY KEY,
210         call_number     BIGINT                          NOT NULL,
211         creator         BIGINT                          NOT NULL,
212         create_date     TIMESTAMP WITH TIME ZONE        DEFAULT NOW(),
213         pub             BOOL                            NOT NULL DEFAULT FALSE,
214         title           TEXT                            NOT NULL,
215         value           TEXT                            NOT NULL
216 );
217 CREATE INDEX asset_call_number_note_creator_idx ON asset.call_number_note ( creator );
218
219 CREATE VIEW stats.fleshed_copy AS 
220         SELECT  cp.*,
221                 CAST(cp.create_date AS DATE) AS create_date_day,
222                 CAST(cp.edit_date AS DATE) AS edit_date_day,
223                 DATE_TRUNC('hour', cp.create_date) AS create_date_hour,
224                 DATE_TRUNC('hour', cp.edit_date) AS edit_date_hour,
225                 cn.label AS call_number_label,
226                 cn.owning_lib,
227                 rd.item_lang,
228                 rd.item_type,
229                 rd.item_form
230         FROM    asset.copy cp
231                 JOIN asset.call_number cn ON (cp.call_number = cn.id)
232                 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
233
234 CREATE VIEW stats.fleshed_call_number AS 
235         SELECT  cn.*,
236                 CAST(cn.create_date AS DATE) AS create_date_day,
237                 CAST(cn.edit_date AS DATE) AS edit_date_day,
238                 DATE_TRUNC('hour', cn.create_date) AS create_date_hour,
239                 DATE_TRUNC('hour', cn.edit_date) AS edit_date_hour,
240                 rd.item_lang,
241                 rd.item_type,
242                 rd.item_form
243         FROM    asset.call_number cn
244                 JOIN metabib.rec_descriptor rd ON (rd.record = cn.record);
245
246 COMMIT;
247