3 INSERT INTO config.upgrade_log (version) VALUES ('0086');
5 DROP SCHEMA IF EXISTS booking CASCADE;
9 CREATE TABLE booking.resource_type (
10 id SERIAL PRIMARY KEY,
12 fine_interval INTERVAL,
13 fine_amount DECIMAL(8,2) NOT NULL DEFAULT 0,
15 REFERENCES actor.org_unit( id )
16 DEFERRABLE INITIALLY DEFERRED,
17 catalog_item BOOLEAN NOT NULL DEFAULT FALSE,
18 transferable BOOLEAN NOT NULL DEFAULT FALSE,
19 CONSTRAINT brt_name_once_per_owner UNIQUE(owner, name)
22 CREATE TABLE booking.resource (
23 id SERIAL PRIMARY KEY,
25 REFERENCES actor.org_unit(id)
26 DEFERRABLE INITIALLY DEFERRED,
28 REFERENCES booking.resource_type(id)
29 DEFERRABLE INITIALLY DEFERRED,
30 overbook BOOLEAN NOT NULL DEFAULT FALSE,
31 barcode TEXT NOT NULL,
32 deposit BOOLEAN NOT NULL DEFAULT FALSE,
33 deposit_amount DECIMAL(8,2) NOT NULL DEFAULT 0.00,
34 user_fee DECIMAL(8,2) NOT NULL DEFAULT 0.00,
35 CONSTRAINT br_unique UNIQUE(owner, type, barcode)
38 -- For non-catalog items: hijack barcode for name/description
40 CREATE TABLE booking.resource_attr (
41 id SERIAL PRIMARY KEY,
43 REFERENCES actor.org_unit(id)
44 DEFERRABLE INITIALLY DEFERRED,
46 resource_type INT NOT NULL
47 REFERENCES booking.resource_type(id)
49 DEFERRABLE INITIALLY DEFERRED,
50 required BOOLEAN NOT NULL DEFAULT FALSE,
51 CONSTRAINT bra_name_once_per_type UNIQUE(resource_type, name)
54 CREATE TABLE booking.resource_attr_value (
55 id SERIAL PRIMARY KEY,
57 REFERENCES actor.org_unit(id)
58 DEFERRABLE INITIALLY DEFERRED,
60 REFERENCES booking.resource_attr(id)
61 DEFERRABLE INITIALLY DEFERRED,
62 valid_value TEXT NOT NULL,
63 CONSTRAINT brav_logical_key UNIQUE(owner, attr, valid_value)
66 -- Do we still need a name column?
69 CREATE TABLE booking.resource_attr_map (
70 id SERIAL PRIMARY KEY,
72 REFERENCES booking.resource(id)
74 DEFERRABLE INITIALLY DEFERRED,
75 resource_attr INT NOT NULL
76 REFERENCES booking.resource_attr(id)
78 DEFERRABLE INITIALLY DEFERRED,
80 REFERENCES booking.resource_attr_value(id)
81 DEFERRABLE INITIALLY DEFERRED,
82 CONSTRAINT bram_one_value_per_attr UNIQUE(resource, resource_attr)
85 CREATE TABLE booking.reservation (
86 request_time TIMESTAMPTZ NOT NULL DEFAULT now(),
87 start_time TIMESTAMPTZ,
89 capture_time TIMESTAMPTZ,
90 cancel_time TIMESTAMPTZ,
91 pickup_time TIMESTAMPTZ,
92 return_time TIMESTAMPTZ,
93 booking_interval INTERVAL,
94 fine_interval INTERVAL,
95 fine_amount DECIMAL(8,2),
96 target_resource_type INT NOT NULL
97 REFERENCES booking.resource_type(id)
99 DEFERRABLE INITIALLY DEFERRED,
100 target_resource INT REFERENCES booking.resource(id)
102 DEFERRABLE INITIALLY DEFERRED,
103 current_resource INT REFERENCES booking.resource(id)
105 DEFERRABLE INITIALLY DEFERRED,
106 request_lib INT NOT NULL
107 REFERENCES actor.org_unit(id)
108 DEFERRABLE INITIALLY DEFERRED,
109 pickup_lib INT REFERENCES actor.org_unit(id)
110 DEFERRABLE INITIALLY DEFERRED,
111 capture_staff INT REFERENCES actor.usr(id)
112 DEFERRABLE INITIALLY DEFERRED
113 ) INHERITS (money.billable_xact);
115 ALTER TABLE booking.reservation ADD PRIMARY KEY (id);
117 ALTER TABLE booking.reservation
118 ADD CONSTRAINT booking_reservation_usr_fkey
119 FOREIGN KEY (usr) REFERENCES actor.usr (id)
120 DEFERRABLE INITIALLY DEFERRED;
122 CREATE TABLE booking.reservation_attr_value_map (
123 id SERIAL PRIMARY KEY,
124 reservation INT NOT NULL
125 REFERENCES booking.reservation(id)
127 DEFERRABLE INITIALLY DEFERRED,
128 attr_value INT NOT NULL
129 REFERENCES booking.resource_attr_value(id)
131 DEFERRABLE INITIALLY DEFERRED,
132 CONSTRAINT bravm_logical_key UNIQUE(reservation, attr_value)