LP1816475: Booking module refresh
[working/Evergreen.git] / Open-ILS / src / sql / Pg / 095.schema.booking.sql
1 /*
2  * Copyright (C) 2009  Equinox Software, Inc.
3  * Scott McKellar <scott@esilibrary.com>
4  *
5  * This program is free software; you can redistribute it and/or
6  * modify it under the terms of the GNU General Public License
7  * as published by the Free Software Foundation; either version 2
8  * of the License, or (at your option) any later version.
9  *
10  * This program is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13  * GNU General Public License for more details.
14  *
15  */
16
17 BEGIN;
18
19 DROP SCHEMA IF EXISTS booking CASCADE;
20
21 CREATE SCHEMA booking;
22
23 CREATE TABLE booking.resource_type (
24         id             SERIAL          PRIMARY KEY,
25         name           TEXT            NOT NULL,
26         elbow_room     INTERVAL,
27         fine_interval  INTERVAL,
28         fine_amount    DECIMAL(8,2)    NOT NULL DEFAULT 0,
29         max_fine       DECIMAL(8,2),
30         owner          INT             NOT NULL
31                                        REFERENCES actor.org_unit( id )
32                                        DEFERRABLE INITIALLY DEFERRED,
33         catalog_item   BOOLEAN         NOT NULL DEFAULT FALSE,
34         transferable   BOOLEAN         NOT NULL DEFAULT FALSE,
35     record         BIGINT          REFERENCES biblio.record_entry (id)
36                                    DEFERRABLE INITIALLY DEFERRED,
37         CONSTRAINT brt_name_and_record_once_per_owner UNIQUE(owner, name, record)
38 );
39
40 CREATE TABLE booking.resource (
41         id             SERIAL           PRIMARY KEY,
42         owner          INT              NOT NULL
43                                         REFERENCES actor.org_unit(id)
44                                         DEFERRABLE INITIALLY DEFERRED,
45         type           INT              NOT NULL
46                                         REFERENCES booking.resource_type(id)
47                                         DEFERRABLE INITIALLY DEFERRED,
48         overbook       BOOLEAN          NOT NULL DEFAULT FALSE,
49         barcode        TEXT             NOT NULL,
50         deposit        BOOLEAN          NOT NULL DEFAULT FALSE,
51         deposit_amount DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
52         user_fee       DECIMAL(8,2)     NOT NULL DEFAULT 0.00,
53         CONSTRAINT br_unique UNIQUE(owner, barcode)
54 );
55
56 -- For non-catalog items: hijack barcode for name/description
57
58 CREATE TABLE booking.resource_attr (
59         id              SERIAL          PRIMARY KEY,
60         owner           INT             NOT NULL
61                                         REFERENCES actor.org_unit(id)
62                                         DEFERRABLE INITIALLY DEFERRED,
63         name            TEXT            NOT NULL,
64         resource_type   INT             NOT NULL
65                                         REFERENCES booking.resource_type(id)
66                                         ON DELETE CASCADE
67                                         DEFERRABLE INITIALLY DEFERRED,
68         required        BOOLEAN         NOT NULL DEFAULT FALSE,
69         CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
70 );
71
72 CREATE TABLE booking.resource_attr_value (
73         id               SERIAL         PRIMARY KEY,
74         owner            INT            NOT NULL
75                                         REFERENCES actor.org_unit(id)
76                                         DEFERRABLE INITIALLY DEFERRED,
77         attr             INT            NOT NULL
78                                         REFERENCES booking.resource_attr(id)
79                                         DEFERRABLE INITIALLY DEFERRED,
80         valid_value      TEXT           NOT NULL,
81         CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
82 );
83
84 -- Do we still need a name column?
85
86
87 CREATE TABLE booking.resource_attr_map (
88         id               SERIAL         PRIMARY KEY,
89         resource         INT            NOT NULL
90                                         REFERENCES booking.resource(id)
91                                         ON DELETE CASCADE
92                                         DEFERRABLE INITIALLY DEFERRED,
93         resource_attr    INT            NOT NULL
94                                         REFERENCES booking.resource_attr(id)
95                                         ON DELETE CASCADE
96                                         DEFERRABLE INITIALLY DEFERRED,
97         value            INT            NOT NULL
98                                         REFERENCES booking.resource_attr_value(id)
99                                         DEFERRABLE INITIALLY DEFERRED,
100         CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
101 );
102
103 CREATE TABLE booking.reservation (
104         request_time     TIMESTAMPTZ   NOT NULL DEFAULT now(),
105         start_time       TIMESTAMPTZ,
106         end_time         TIMESTAMPTZ,
107         capture_time     TIMESTAMPTZ,
108         cancel_time      TIMESTAMPTZ,
109         pickup_time      TIMESTAMPTZ,
110         return_time      TIMESTAMPTZ,
111         booking_interval INTERVAL,
112         fine_interval    INTERVAL,
113         fine_amount      DECIMAL(8,2),
114         max_fine         DECIMAL(8,2),
115         target_resource_type  INT       NOT NULL
116                                         REFERENCES booking.resource_type(id)
117                                         ON DELETE CASCADE
118                                         DEFERRABLE INITIALLY DEFERRED,
119         target_resource  INT            REFERENCES booking.resource(id)
120                                         ON DELETE CASCADE
121                                         DEFERRABLE INITIALLY DEFERRED,
122         current_resource INT            REFERENCES booking.resource(id)
123                                         ON DELETE CASCADE
124                                         DEFERRABLE INITIALLY DEFERRED,
125         request_lib      INT            NOT NULL
126                                         REFERENCES actor.org_unit(id)
127                                         DEFERRABLE INITIALLY DEFERRED,
128         pickup_lib       INT            REFERENCES actor.org_unit(id)
129                                         DEFERRABLE INITIALLY DEFERRED,
130         capture_staff    INT            REFERENCES actor.usr(id)
131                                         DEFERRABLE INITIALLY DEFERRED,
132         email_notify     BOOLEAN        NOT NULL DEFAULT FALSE,
133         note             TEXT
134 ) INHERITS (money.billable_xact);
135
136 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
137
138 ALTER TABLE booking.reservation
139         ADD CONSTRAINT booking_reservation_usr_fkey
140         FOREIGN KEY (usr) REFERENCES actor.usr (id)
141         DEFERRABLE INITIALLY DEFERRED;
142
143 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('reservation');
144 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
145 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
146
147
148 CREATE TABLE booking.reservation_attr_value_map (
149         id               SERIAL         PRIMARY KEY,
150         reservation      INT            NOT NULL
151                                         REFERENCES booking.reservation(id)
152                                         ON DELETE CASCADE
153                                         DEFERRABLE INITIALLY DEFERRED,
154         attr_value       INT            NOT NULL
155                                         REFERENCES booking.resource_attr_value(id)
156                                         ON DELETE CASCADE
157                                         DEFERRABLE INITIALLY DEFERRED,
158         CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)
159 );
160
161 CREATE TABLE action.reservation_transit_copy (
162     reservation    INT REFERENCES booking.reservation (id) ON DELETE SET NULL DEFERRABLE INITIALLY DEFERRED
163 ) INHERITS (action.transit_copy);
164 ALTER TABLE action.reservation_transit_copy ADD PRIMARY KEY (id);
165 ALTER TABLE action.reservation_transit_copy ADD CONSTRAINT artc_tc_fkey FOREIGN KEY (target_copy) REFERENCES booking.resource (id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED;
166 CREATE INDEX active_reservation_transit_dest_idx ON "action".reservation_transit_copy (dest);
167 CREATE INDEX active_reservation_transit_source_idx ON "action".reservation_transit_copy (source);
168 CREATE INDEX active_reservation_transit_cp_idx ON "action".reservation_transit_copy (target_copy);
169
170 CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check
171     AFTER INSERT ON action.reservation_transit_copy
172     FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
173
174 COMMIT;