sql/mod_room-reservation.sql

   1 --
   2 -- Table with rooms allowed for booking
   3 --
   4 CREATE TABLE mod_roomreservation_roomswhitelist (
   5                 rrr_name                         TEXT                    UNIQUE REFERENCES rooms(name)
   6                              ON DELETE CASCADE
   7                              ON UPDATE CASCADE
   8 );
   9
  10 --
  11 -- Table with bookings
  12 --
  13 CREATE TABLE mod_roomreservation_bookings (
  14     rrb_uid        SERIAL    NOT NULL PRIMARY KEY, -- Unique ID
  15     rrb_room       TEXT      NOT NULL       -- Name of the room
  16                              REFERENCES rooms(name)
  17                              ON DELETE CASCADE
  18                              ON UPDATE CASCADE,
  19     rrb_date       DATE      NOT NULL,      -- Date of the booking
  20     rrb_tsfirst    SMALLINT  NOT NULL,      -- Number of the first timeslice
  21     rrb_tslast     SMALLINT  NOT NULL       -- Number of the last timeslice
  22                              CHECK(rrb_tsfirst <= rrb_tslast),
  23     rrb_act        TEXT      NOT NULL       -- Owner of the booking
  24                              REFERENCES users(Act)
  25                              ON DELETE CASCADE
  26                              ON UPDATE CASCADE,
  27     rrb_reason     TEXT      NOT NULL,      -- Reason
  28     rrb_interval   SMALLINT  NOT NULL       -- Interval in weeks for recurring
  29                                             -- bookings
  30                              DEFAULT 0
  31 );
  32
  33 --
  34 -- Permissions
  35 --
  36 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings TO webusr,
  37   webadm;
  38 GRANT SELECT, UPDATE ON mod_roomreservation_bookings_rrb_uid_seq TO webusr,
  39   webadm;
  40 GRANT SELECT ON mod_roomreservation_roomswhitelist TO webusr;
  41 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_roomswhitelist TO
  42   webadm;
  43