sql/mod_room-reservation.sql

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