2 * Copyright (C) 2009 Equinox Software, Inc.
3 * Scott McKellar <scott@esilibrary.com>
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.
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.
19 DROP SCHEMA IF EXISTS booking CASCADE;
21 CREATE SCHEMA booking;
23 CREATE TABLE booking.resource_type (
24 id SERIAL PRIMARY KEY,
27 fine_interval INTERVAL,
28 fine_amount DECIMAL(8,2) NOT NULL DEFAULT 0,
29 max_fine DECIMAL(8,2),
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)
40 CREATE TABLE booking.resource (
41 id SERIAL PRIMARY KEY,
43 REFERENCES actor.org_unit(id)
44 DEFERRABLE INITIALLY DEFERRED,
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)
56 -- For non-catalog items: hijack barcode for name/description
58 CREATE TABLE booking.resource_attr (
59 id SERIAL PRIMARY KEY,
61 REFERENCES actor.org_unit(id)
62 DEFERRABLE INITIALLY DEFERRED,
64 resource_type INT NOT NULL
65 REFERENCES booking.resource_type(id)
67 DEFERRABLE INITIALLY DEFERRED,
68 required BOOLEAN NOT NULL DEFAULT FALSE,
69 CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
72 CREATE TABLE booking.resource_attr_value (
73 id SERIAL PRIMARY KEY,
75 REFERENCES actor.org_unit(id)
76 DEFERRABLE INITIALLY DEFERRED,
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)
84 -- Do we still need a name column?
87 CREATE TABLE booking.resource_attr_map (
88 id SERIAL PRIMARY KEY,
90 REFERENCES booking.resource(id)
92 DEFERRABLE INITIALLY DEFERRED,
93 resource_attr INT NOT NULL
94 REFERENCES booking.resource_attr(id)
96 DEFERRABLE INITIALLY DEFERRED,
98 REFERENCES booking.resource_attr_value(id)
99 DEFERRABLE INITIALLY DEFERRED,
100 CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
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)
118 DEFERRABLE INITIALLY DEFERRED,
119 target_resource INT REFERENCES booking.resource(id)
121 DEFERRABLE INITIALLY DEFERRED,
122 current_resource INT REFERENCES booking.resource(id)
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,
134 ) INHERITS (money.billable_xact);
136 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
138 ALTER TABLE booking.reservation
139 ADD CONSTRAINT booking_reservation_usr_fkey
140 FOREIGN KEY (usr) REFERENCES actor.usr (id)
141 DEFERRABLE INITIALLY DEFERRED;
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 ();
148 CREATE TABLE booking.reservation_attr_value_map (
149 id SERIAL PRIMARY KEY,
150 reservation INT NOT NULL
151 REFERENCES booking.reservation(id)
153 DEFERRABLE INITIALLY DEFERRED,
154 attr_value INT NOT NULL
155 REFERENCES booking.resource_attr_value(id)
157 DEFERRABLE INITIALLY DEFERRED,
158 CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)
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);
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();