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 bookings
  29                              DEFAULT 0
  30 );
  31
  32 --
  33 -- Permissions
  34 --
  35 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webusr;
  36 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings, mod_roomreservation_bookings_rrb_uid_seq TO webadm;
  37 GRANT SELECT ON mod_roomreservation_roomswhitelist TO webusr;
  38 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_roomswhitelist TO webadm;