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,
26 fine_interval INTERVAL,
27 fine_amount DECIMAL(8,2) NOT NULL DEFAULT 0,
28 max_fine DECIMAL(8,2),
30 REFERENCES actor.org_unit( id )
31 DEFERRABLE INITIALLY DEFERRED,
32 catalog_item BOOLEAN NOT NULL DEFAULT FALSE,
33 transferable BOOLEAN NOT NULL DEFAULT FALSE,
34 record INT REFERENCES biblio.record_entry (id)
35 DEFERRABLE INITIALLY DEFERRED,
36 CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name, record)
39 CREATE TABLE booking.resource (
40 id SERIAL PRIMARY KEY,
42 REFERENCES actor.org_unit(id)
43 DEFERRABLE INITIALLY DEFERRED,
45 REFERENCES booking.resource_type(id)
46 DEFERRABLE INITIALLY DEFERRED,
47 overbook BOOLEAN NOT NULL DEFAULT FALSE,
48 barcode TEXT NOT NULL,
49 deposit BOOLEAN NOT NULL DEFAULT FALSE,
50 deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00,
51 user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00,
52 CONSTRAINT br_unique UNIQUE(owner, type, barcode)
55 -- For non-catalog items: hijack barcode for name/description
57 CREATE TABLE booking.resource_attr (
58 id SERIAL PRIMARY KEY,
60 REFERENCES actor.org_unit(id)
61 DEFERRABLE INITIALLY DEFERRED,
63 resource_type INT NOT NULL
64 REFERENCES booking.resource_type(id)
66 DEFERRABLE INITIALLY DEFERRED,
67 required BOOLEAN NOT NULL DEFAULT FALSE,
68 CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
71 CREATE TABLE booking.resource_attr_value (
72 id SERIAL PRIMARY KEY,
74 REFERENCES actor.org_unit(id)
75 DEFERRABLE INITIALLY DEFERRED,
77 REFERENCES booking.resource_attr(id)
78 DEFERRABLE INITIALLY DEFERRED,
79 valid_value TEXT NOT NULL,
80 CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
83 -- Do we still need a name column?
86 CREATE TABLE booking.resource_attr_map (
87 id SERIAL PRIMARY KEY,
89 REFERENCES booking.resource(id)
91 DEFERRABLE INITIALLY DEFERRED,
92 resource_attr INT NOT NULL
93 REFERENCES booking.resource_attr(id)
95 DEFERRABLE INITIALLY DEFERRED,
97 REFERENCES booking.resource_attr_value(id)
98 DEFERRABLE INITIALLY DEFERRED,
99 CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
102 CREATE TABLE booking.reservation (
103 request_time TIMESTAMPTZ NOT NULL DEFAULT now(),
104 start_time TIMESTAMPTZ,
105 end_time TIMESTAMPTZ,
106 capture_time TIMESTAMPTZ,
107 cancel_time TIMESTAMPTZ,
108 pickup_time TIMESTAMPTZ,
109 return_time TIMESTAMPTZ,
110 booking_interval INTERVAL,
111 fine_interval INTERVAL,
112 fine_amount DECIMAL(8,2),
113 max_fine DECIMAL(8,2),
114 target_resource_type INT NOT NULL
115 REFERENCES booking.resource_type(id)
117 DEFERRABLE INITIALLY DEFERRED,
118 target_resource INT REFERENCES booking.resource(id)
120 DEFERRABLE INITIALLY DEFERRED,
121 current_resource INT REFERENCES booking.resource(id)
123 DEFERRABLE INITIALLY DEFERRED,
124 request_lib INT NOT NULL
125 REFERENCES actor.org_unit(id)
126 DEFERRABLE INITIALLY DEFERRED,
127 pickup_lib INT REFERENCES actor.org_unit(id)
128 DEFERRABLE INITIALLY DEFERRED,
129 capture_staff INT REFERENCES actor.usr(id)
130 DEFERRABLE INITIALLY DEFERRED
131 ) INHERITS (money.billable_xact);
133 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
135 ALTER TABLE booking.reservation
136 ADD CONSTRAINT booking_reservation_usr_fkey
137 FOREIGN KEY (usr) REFERENCES actor.usr (id)
138 DEFERRABLE INITIALLY DEFERRED;
140 CREATE TRIGGER mat_summary_create_tgr AFTER INSERT ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_create ('reservation');
141 CREATE TRIGGER mat_summary_change_tgr AFTER UPDATE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_update ();
142 CREATE TRIGGER mat_summary_remove_tgr AFTER DELETE ON booking.reservation FOR EACH ROW EXECUTE PROCEDURE money.mat_summary_delete ();
145 CREATE TABLE booking.reservation_attr_value_map (
146 id SERIAL PRIMARY KEY,
147 reservation INT NOT NULL
148 REFERENCES booking.reservation(id)
150 DEFERRABLE INITIALLY DEFERRED,
151 attr_value INT NOT NULL
152 REFERENCES booking.resource_attr_value(id)
154 DEFERRABLE INITIALLY DEFERRED,
155 CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)