removed account data from debian/copyright (Google cache sucks :P), updated image...

[iserv-mod-room-reservation.git] / sql / mod_room-reservation.sql

diff --git a/sql/mod_room-reservation.sql b/sql/mod_room-reservation.sql
index a4cf33f..3f0375e 100644 (file)
--- a/sql/mod_room-reservation.sql
+++ b/sql/mod_room-reservation.sql
@@ -1,27 +1,65 @@
---\r
--- Table with bookings for module iserv-room-reservation\r
---\r
-CREATE TABLE mod_roomreservation_bookings (\r
-    rrb_uid        SERIAL    NOT NULL PRIMARY KEY, -- Unique ID \r
-    rrb_room       TEXT      NOT NULL              -- Name of the room\r
-                             REFERENCES rooms(name)\r
-                             ON DELETE CASCADE\r
-                             ON UPDATE CASCADE,\r
-    rrb_date       DATE      NOT NULL,             -- Date of the booking\r
-    rrb_tsfirst    SMALLINT  NOT NULL,             -- Number of the first timeslice\r
-    rrb_tslast     SMALLINT  NOT NULL              -- Number of the last timeslice\r
-                             CHECK(rrb_tsfirst <= rrb_tslast),\r
-    rrb_act        TEXT      NOT NULL              -- Owner of the booking\r
-                             REFERENCES users(Act)\r
-                             ON DELETE CASCADE\r
-                             ON UPDATE CASCADE,\r
-    rrb_reason     TEXT      NOT NULL,             -- Reason\r
-    rrb_interval   SMALLINT  NOT NULL              -- Interval in weeks for recurring bookings\r
-                             DEFAULT 0\r
-);\r
-\r
---\r
--- Permissions\r
---\r
-GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webusr;\r
-GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webadm;\r
+--
+-- Table with rooms allowed for booking
+-- 
+CREATE TABLE mod_roomreservation_roomswhitelist (
+               rrr_name                         TEXT                    UNIQUE REFERENCES rooms(name)
+                             ON DELETE CASCADE
+                             ON UPDATE CASCADE
+);
+
+--
+-- Table with bookings
+--
+CREATE TABLE mod_roomreservation_bookings (
+    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
+                             CHECK(rrb_tsfirst <= rrb_tslast),
+    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 
+                             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 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_intermissions 
+  TO webusr;
+GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_intermissions 
+  TO webadm;
+  
\ No newline at end of file