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,
29 REFERENCES actor.org_unit( id )
30 DEFERRABLE INITIALLY DEFERRED,
31 catalog_item BOOLEAN NOT NULL DEFAULT FALSE,
32 transferable BOOLEAN NOT NULL DEFAULT FALSE,
33 CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name)
36 CREATE TABLE booking.resource (
37 id SERIAL PRIMARY KEY,
39 REFERENCES actor.org_unit(id)
40 DEFERRABLE INITIALLY DEFERRED,
42 REFERENCES booking.resource_type(id)
43 DEFERRABLE INITIALLY DEFERRED,
44 overbook BOOLEAN NOT NULL DEFAULT FALSE,
45 barcode TEXT NOT NULL,
46 deposit BOOLEAN NOT NULL DEFAULT FALSE,
47 deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00,
48 user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00,
49 CONSTRAINT br_unique UNIQUE(owner, type, barcode)
52 -- For non-catalog items: hijack barcode for name/description
54 CREATE TABLE booking.resource_attr (
55 id SERIAL PRIMARY KEY,
57 REFERENCES actor.org_unit(id)
58 DEFERRABLE INITIALLY DEFERRED,
60 resource_type INT NOT NULL
61 REFERENCES booking.resource_type(id)
63 DEFERRABLE INITIALLY DEFERRED,
64 required BOOLEAN NOT NULL DEFAULT FALSE,
65 CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
68 CREATE TABLE booking.resource_attr_value (
69 id SERIAL PRIMARY KEY,
71 REFERENCES actor.org_unit(id)
72 DEFERRABLE INITIALLY DEFERRED,
74 REFERENCES booking.resource_attr(id)
75 DEFERRABLE INITIALLY DEFERRED,
76 valid_value TEXT NOT NULL,
77 CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
80 -- Do we still need a name column?
83 CREATE TABLE booking.resource_attr_map (
84 id SERIAL PRIMARY KEY,
86 REFERENCES booking.resource(id)
88 DEFERRABLE INITIALLY DEFERRED,
89 resource_attr INT NOT NULL
90 REFERENCES booking.resource_attr(id)
92 DEFERRABLE INITIALLY DEFERRED,
94 REFERENCES booking.resource_attr_value(id)
95 DEFERRABLE INITIALLY DEFERRED,
96 CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
99 CREATE TABLE booking.reservation (
100 request_time TIMESTAMPTZ NOT NULL DEFAULT now(),
101 start_time TIMESTAMPTZ,
102 end_time TIMESTAMPTZ,
103 capture_time TIMESTAMPTZ,
104 cancel_time TIMESTAMPTZ,
105 pickup_time TIMESTAMPTZ,
106 return_time TIMESTAMPTZ,
107 booking_interval INTERVAL,
108 fine_interval INTERVAL,
109 fine_amount DECIMAL(8,2),
110 target_resource_type INT NOT NULL
111 REFERENCES booking.resource_type(id)
113 DEFERRABLE INITIALLY DEFERRED,
114 target_resource INT REFERENCES booking.resource(id)
116 DEFERRABLE INITIALLY DEFERRED,
117 current_resource INT REFERENCES booking.resource(id)
119 DEFERRABLE INITIALLY DEFERRED,
120 request_lib INT NOT NULL
121 REFERENCES actor.org_unit(id)
122 DEFERRABLE INITIALLY DEFERRED,
123 pickup_lib INT REFERENCES actor.org_unit(id)
124 DEFERRABLE INITIALLY DEFERRED,
125 capture_staff INT REFERENCES actor.usr(id)
126 DEFERRABLE INITIALLY DEFERRED
127 ) INHERITS (money.billable_xact);
129 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
131 ALTER TABLE booking.reservation
132 ADD CONSTRAINT booking_reservation_usr_fkey
133 FOREIGN KEY (usr) REFERENCES actor.usr (id)
134 DEFERRABLE INITIALLY DEFERRED;
136 CREATE TABLE booking.reservation_attr_value_map (
137 id SERIAL PRIMARY KEY,
138 reservation INT NOT NULL
139 REFERENCES booking.reservation(id)
141 DEFERRABLE INITIALLY DEFERRED,
142 attr_value INT NOT NULL
143 REFERENCES booking.resource_attr_value(id)
145 DEFERRABLE INITIALLY DEFERRED,
146 CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)