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         -- Unique ID
  15                              PRIMARY KEY,
  16     rrb_room       TEXT      NOT NULL         -- Name of the room
  17                              REFERENCES rooms(name)
  18                              ON DELETE CASCADE
  19                              ON UPDATE CASCADE,
  20     rrb_date       DATE      NOT NULL,        -- Date of the booking
  21     rrb_tsfirst    SMALLINT  NOT NULL,        -- Number of the first timeslice
  22     rrb_tslast     SMALLINT  NOT NULL         -- Number of the last timeslice
  23                              CHECK(rrb_tsfirst <= rrb_tslast),
  24     rrb_act        TEXT      NOT NULL         -- Owner of the booking
  25                              REFERENCES users(Act)
  26                              ON DELETE CASCADE
  27                              ON UPDATE CASCADE,
  28     rrb_reason     TEXT      NOT NULL,        -- Reason
  29     rrb_interval   SMALLINT  NOT NULL         -- Interval in weeks for
  30                              DEFAULT 0        --   recurring bookings
  31 );
  32
  33
  34 --
  35 -- Table with booking interruptions
  36 --
  37 CREATE TABLE mod_roomreservation_intermissions (
  38     rri_uid        SERIAL    NOT NULL         -- Unique ID of intermissions
  39                              PRIMARY KEY,
  40     rri_rrb_uid    INT       NOT NULL         -- The boooking to intermit
  41                              REFERENCES mod_roomreservation_bookings(rrb_uid)
  42                              ON UPDATE CASCADE ON DELETE CASCADE,
  43     -- Note: start and end date form an closed interval, i.e. the start and
  44     -- end date are part of the interval.
  45     rri_start      DATE      NOT NULL,        -- Start date
  46     rri_end        DATE      NOT NULL         -- End date
  47                              CHECK(rri_start <= rri_end)
  48 );
  49
  50 --
  51 -- Permissions
  52 --
  53 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings,
  54   mod_roomreservation_bookings_rrb_uid_seq TO webusr;
  55 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_bookings,
  56   mod_roomreservation_bookings_rrb_uid_seq TO webadm;
  57 GRANT SELECT ON mod_roomreservation_roomswhitelist TO webusr;
  58 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_roomswhitelist
  59   TO webadm;
  60
  61 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_intermissions
  62   TO webusr;
  63 GRANT SELECT, INSERT, UPDATE, DELETE ON mod_roomreservation_intermissions
  64   TO webadm;
  65