mqPbuNsx

1/* We cant name the table User, postgres complains about syntax errors */
2DROP TABLE CostLog;
3DROP TABLE ReservationMember;
4DROP TABLE Reservation;
5DROP TABLE Resource;
6DROP TABLE Employee;
7DROP TABLE PartnerEmployee;
8DROP TABLE Team;
9DROP TABLE Users;
10DROP TABLE PartnerCompany;
11
12CREATE TABLE IF NOT EXISTS Users (
13    id SERIAL PRIMARY KEY,
14    name VARCHAR(100) NOT NULL
15);
16
17/*Such table, very EN3 and EN2 and EN1*/
18/*
19User(name)
20Empoyee(userId,position,team)
21PartnerEmployee(userId,represent,position)
22PartnerCompany(name)
23Resource(id,)
24
25*/
26
27CREATE TABLE IF NOT EXISTS Team (
28    id SERIAL PRIMARY KEY,
29    name VARCHAR(100) NOT NULL
30);
31
32CREATE TABLE IF NOT EXISTS Employee (
33    id INTEGER REFERENCES Users(id),
34    position VARCHAR(100) NOT NULL,
35    teamId INTEGER REFERENCES Team(id)
36);
37
38CREATE TABLE IF NOT EXISTS PartnerCompany (
39    id SERIAL PRIMARY KEY,
40    name VARCHAR(100) NOT NULL
41);
42
43CREATE TABLE IF NOT EXISTS PartnerEmployee (
44    id INTEGER REFERENCES Users(id),
45    representId INTEGER REFERENCES PartnerCompany(id),
46    position VARCHAR(100) NOT NULL
47);
48
49CREATE TABLE IF NOT EXISTS Resource (
50    id SERIAL PRIMARY KEY,
51    name VARCHAR(100) NOT NULL,
52    costPerHour INTEGER NOT NULL
53);
54
55CREATE TABLE IF NOT EXISTS Reservation (
56    id SERIAL PRIMARY KEY,
57    name VARCHAR(100) NOT NULL,
58    userId INTEGER REFERENCES Users(id),
59    bookerTeamId INTEGER REFERENCES Team(id), /* People might switch teams, costs should not move. Without this the triggers would all need  an extra select to find the team of the user */  
60    resourceId INTEGER REFERENCES Resource(id),
61    timeslotStart TIMESTAMP NOT NULL,
62    timeslotEnd TIMESTAMP NOT NULL
63);
64
65/*
66    Function for ensuring that reservations can not overlap
67*/
68CREATE OR REPLACE FUNCTION CheckReservationNotOverlap() RETURNS TRIGGER AS $reservation_stamp$
69    DECLARE
70        _count integer;
71    BEGIN
72        IF NOW() > NEW.timeslotStart THEN
73            RAISE EXCEPTION 'Can not do a reservation back in time';
74        END IF;
75        SELECT COUNT(*) into _count FROM Reservation WHERE
76            NEW.resourceId = Reservation.resourceId AND 
77                (NEW.timeslotEnd >= Reservation.timeslotStart AND NEW.timeslotEnd <= Reservation.timeslotStart) OR
78                (NEW.timeslotStart >= Reservation.timeslotStart AND NEW.timeslotStart <= Reservation.timeslotStart);
79        IF _count != 0 THEN
80            RAISE EXCEPTION 'Reservations for the same resource can not overlap';
81        END IF;
82        RETURN NEW;
83
84    END;
85$reservation_stamp$ LANGUAGE plpgsql;
86
87CREATE TRIGGER OneReservationPerTimeslot BEFORE INSERT OR UPDATE ON Reservation 
88    FOR EACH ROW EXECUTE PROCEDURE CheckReservationNotOverlap();
89
90
91CREATE OR REPLACE FUNCTION UpdateCostLog() RETURNS TRIGGER AS $update_trig$
92    DECLARE 
93        _cost integer;
94    BEGIN
95        IF NEW.bookerTeamId != OLD.bookerTeamId THEN
96            RAISE EXCEPTION 'You are not allowed to change team ids of a booking';
97        END IF;
98        SELECT costPerHour into _cost FROM Resource WHERE id = NEW.resourceId;
99        INSERT INTO CostLog (teamId, cost, eventDescription) VALUES (OLD.bookerTeamId, _cost * (EXTRACT(epoch FROM (NEW.timeslotEnd - NEW.timeslotStart)) / 3600 - EXTRACT(epoch FROM (OLD.timeslotEnd - OLD.timeslotStart)) / 3600), 'Updated time of reservation');
100        RETURN NEW;
101    END;
102$update_trig$ LANGUAGE plpgsql;
103
104
105CREATE TRIGGER UpdateCostLogTrigger BEFORE UPDATE ON Reservation 
106    FOR EACH ROW EXECUTE PROCEDURE UpdateCostLog();
107
108
109CREATE OR REPLACE FUNCTION InsertCostLog() RETURNS TRIGGER AS $insert_trig$
110    DECLARE
111        _cost integer;
112    BEGIN
113        SELECT costPerHour into _cost FROM Resource WHERE id = NEW.resourceId;
114        INSERT INTO CostLog(teamId, cost, eventDescription) VALUES (NEW.bookerTeamId, _cost * EXTRACT(epoch FROM NEW.timeslotEnd - NEW.timeslotStart) / 3600, 'Booked resource');
115        RETURN NEW;
116    END;
117$insert_trig$ LANGUAGE plpgsql;
118
119
120CREATE TRIGGER InsertCostLogTrigger AFTER INSERT ON Reservation
121    FOR EACH ROW EXECUTE PROCEDURE InsertCostLog();
122
123
124CREATE OR REPLACE FUNCTION DeleteCostLog() RETURNS TRIGGER AS $delete_trig$
125    DECLARE
126        _cost integer;
127    BEGIN
128        IF OLD.timeslotStart > NOW() THEN
129            RAISE EXCEPTION 'Can not remove reservation that has already happened';
130        END IF;
131        SELECT costPerHour into _cost FROM Resource WHERE id = OLD.resourceId;
132        INSERT INTO CostLog(teamId, cost, eventDescription) VALUES (OLD.bookerTeamId, _cost * EXTRACT(epoch FROM OLD.timeslotEnd - OLD.timeslotStart), 'Canceld reservation of resource');
133        RETURN OLD;
134    END;
135$delete_trig$ LANGUAGE plpgsql;
136
137
138CREATE TRIGGER DeleteCostLogTrigger BEFORE DELETE ON Reservation
139    FOR EACH ROW EXECUTE PROCEDURE DeleteCostLog();
140
141
142CREATE TABLE IF NOT EXISTS ReservationMember (
143    id INTEGER REFERENCES Reservation(id),
144    userId INTEGER REFERENCES Users(id)
145);
146
147
148CREATE TABLE IF NOT EXISTS CostLog (
149    teamId INTEGER REFERENCES Team(id),
150    cost INTEGER NOT NULL,
151    eventDescription TEXT NOT NULL
152);