-- Table with bookings
--
CREATE TABLE mod_roomreservation_bookings (
- rrb_uid SERIAL NOT NULL PRIMARY KEY, -- Unique ID
- rrb_room TEXT NOT NULL -- Name of the room
+ rrb_uid SERIAL NOT NULL -- Unique ID
+ PRIMARY KEY,
+ rrb_room TEXT NOT NULL -- Name of the room
REFERENCES rooms(name)
ON DELETE CASCADE
ON UPDATE CASCADE,
- rrb_date DATE NOT NULL, -- Date of the booking
- rrb_tsfirst SMALLINT NOT NULL, -- Number of the first timeslice
- rrb_tslast SMALLINT NOT NULL -- Number of the last timeslice
+ rrb_date DATE NOT NULL, -- Date of the booking
+ rrb_tsfirst SMALLINT NOT NULL, -- Number of the first timeslice
+ rrb_tslast SMALLINT NOT NULL -- Number of the last timeslice
CHECK(rrb_tsfirst <= rrb_tslast),
- rrb_act TEXT NOT NULL -- Owner of the booking
+ rrb_act TEXT NOT NULL -- Owner of the booking
REFERENCES users(Act)
ON DELETE CASCADE
ON UPDATE CASCADE,
- rrb_reason TEXT NOT NULL, -- Reason
- rrb_interval SMALLINT NOT NULL -- Interval in weeks for recurring bookings
- DEFAULT 0
+ rrb_reason TEXT NOT NULL, -- Reason
+ rrb_interval SMALLINT NOT NULL -- Interval in weeks for
+ DEFAULT 0 -- recurring bookings
+);
+
+
+--
+-- Table with booking interruptions
+--
+CREATE TABLE mod_roomreservation_intermissions (
+ rri_uid SERIAL NOT NULL -- Unique ID of intermissions
+ PRIMARY KEY,
+ rri_rrb_uid INT NOT NULL -- The boooking to intermit
+ REFERENCES mod_roomreservation_bookings(rrb_uid)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ -- Note: start and end date form an closed interval, i.e. the start and
+ -- end date are part of the interval.
+ rri_start DATE NOT NULL, -- Start date
+ rri_end DATE NOT NULL -- End date
+ CHECK(rri_start <= rri_end)
);
--
-- Permissions
--
-GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webusr;
-GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webadm;
+GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings,
+ mod_roomreservation_bookings_rrb_uid_seq TO webusr;
+GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings,
+ mod_roomreservation_bookings_rrb_uid_seq TO webadm;
GRANT SELECT ON mod_roomreservation_roomswhitelist TO webusr;
-GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_roomswhitelist TO webadm;
+GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_roomswhitelist
+ TO webadm;
+
+GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_intermissions
+ TO webusr;
+GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_intermissions
+ TO webadm;
+
\ No newline at end of file