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 record INT REFERENCES biblio.record_entry (id)
34 DEFERRABLE INITIALLY DEFERRED,
35 CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name, record)
38 CREATE TABLE booking.resource (
39 id SERIAL PRIMARY KEY,
41 REFERENCES actor.org_unit(id)
42 DEFERRABLE INITIALLY DEFERRED,
44 REFERENCES booking.resource_type(id)
45 DEFERRABLE INITIALLY DEFERRED,
46 overbook BOOLEAN NOT NULL DEFAULT FALSE,
47 barcode TEXT NOT NULL,
48 deposit BOOLEAN NOT NULL DEFAULT FALSE,
49 deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00,
50 user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00,
51 CONSTRAINT br_unique UNIQUE(owner, type, barcode)
54 -- For non-catalog items: hijack barcode for name/description
56 CREATE TABLE booking.resource_attr (
57 id SERIAL PRIMARY KEY,
59 REFERENCES actor.org_unit(id)
60 DEFERRABLE INITIALLY DEFERRED,
62 resource_type INT NOT NULL
63 REFERENCES booking.resource_type(id)
65 DEFERRABLE INITIALLY DEFERRED,
66 required BOOLEAN NOT NULL DEFAULT FALSE,
67 CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
70 CREATE TABLE booking.resource_attr_value (
71 id SERIAL PRIMARY KEY,
73 REFERENCES actor.org_unit(id)
74 DEFERRABLE INITIALLY DEFERRED,
76 REFERENCES booking.resource_attr(id)
77 DEFERRABLE INITIALLY DEFERRED,
78 valid_value TEXT NOT NULL,
79 CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
82 -- Do we still need a name column?
85 CREATE TABLE booking.resource_attr_map (
86 id SERIAL PRIMARY KEY,
88 REFERENCES booking.resource(id)
90 DEFERRABLE INITIALLY DEFERRED,
91 resource_attr INT NOT NULL
92 REFERENCES booking.resource_attr(id)
94 DEFERRABLE INITIALLY DEFERRED,
96 REFERENCES booking.resource_attr_value(id)
97 DEFERRABLE INITIALLY DEFERRED,
98 CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
101 CREATE TABLE booking.reservation (
102 request_time TIMESTAMPTZ NOT NULL DEFAULT now(),
103 start_time TIMESTAMPTZ,
104 end_time TIMESTAMPTZ,
105 capture_time TIMESTAMPTZ,
106 cancel_time TIMESTAMPTZ,
107 pickup_time TIMESTAMPTZ,
108 return_time TIMESTAMPTZ,
109 booking_interval INTERVAL,
110 fine_interval INTERVAL,
111 fine_amount DECIMAL(8,2),
112 target_resource_type INT NOT NULL
113 REFERENCES booking.resource_type(id)
115 DEFERRABLE INITIALLY DEFERRED,
116 target_resource INT REFERENCES booking.resource(id)
118 DEFERRABLE INITIALLY DEFERRED,
119 current_resource INT REFERENCES booking.resource(id)
121 DEFERRABLE INITIALLY DEFERRED,
122 request_lib INT NOT NULL
123 REFERENCES actor.org_unit(id)
124 DEFERRABLE INITIALLY DEFERRED,
125 pickup_lib INT REFERENCES actor.org_unit(id)
126 DEFERRABLE INITIALLY DEFERRED,
127 capture_staff INT REFERENCES actor.usr(id)
128 DEFERRABLE INITIALLY DEFERRED
129 ) INHERITS (money.billable_xact);
131 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
133 ALTER TABLE booking.reservation
134 ADD CONSTRAINT booking_reservation_usr_fkey
135 FOREIGN KEY (usr) REFERENCES actor.usr (id)
136 DEFERRABLE INITIALLY DEFERRED;
138 CREATE TABLE booking.reservation_attr_value_map (
139 id SERIAL PRIMARY KEY,
140 reservation INT NOT NULL
141 REFERENCES booking.reservation(id)
143 DEFERRABLE INITIALLY DEFERRED,
144 attr_value INT NOT NULL
145 REFERENCES booking.resource_attr_value(id)
147 DEFERRABLE INITIALLY DEFERRED,
148 CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)