· 9 years ago · Dec 18, 2016, 09:14 PM
1--------------------------------------------------------
2-- File created - Sunday-December-18-2016
3--------------------------------------------------------
4--------------------------------------------------------
5-- DDL for View MOST_PLAYED_GAMES
6--------------------------------------------------------
7
8 CREATE OR REPLACE FORCE VIEW "MOST_PLAYED_GAMES" ("GAME_ID", "TIMES") AS
9 SELECT g.GAME_ID, COUNT(g.game_id) AS times
10 FROM user_games g, gameplay_sessions gs
11 WHERE g.GAME_ID = gs.USER_GAME_ID
12 GROUP BY g.GAME_ID
13 ORDER BY COUNT(g.game_id) DESC;
14--------------------------------------------------------
15-- DDL for Table GAMEPLAY_SESSIONS
16--------------------------------------------------------
17
18 CREATE TABLE "GAMEPLAY_SESSIONS"
19 ( "ID" NUMBER(20,0),
20 "LENGTH" NUMBER(20,0),
21 "POINTS" NUMBER(20,0),
22 "START_TIME" TIMESTAMP (6),
23 "USER_GAME_ID" NUMBER(20,0)
24 ) ;
25--------------------------------------------------------
26-- DDL for Table GAMES
27--------------------------------------------------------
28
29 CREATE TABLE "GAMES"
30 ( "GAME_ID" NUMBER,
31 "GAME_NAME" VARCHAR2(20 BYTE)
32 ) ;
33--------------------------------------------------------
34-- DDL for Table USERS
35--------------------------------------------------------
36
37 CREATE TABLE "USERS"
38 ( "USER_ID" NUMBER(20,0),
39 "USER_NAME" VARCHAR2(20 BYTE)
40 ) ;
41--------------------------------------------------------
42-- DDL for Table USER_GAMES
43--------------------------------------------------------
44
45 CREATE TABLE "USER_GAMES"
46 ( "ID" NUMBER(20,0),
47 "USER_ID" NUMBER(20,0),
48 "GAME_ID" NUMBER(20,0)
49 ) ;
50REM INSERTING into GAMEPLAY_SESSIONS
51SET DEFINE OFF;
52Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (1,10,1,to_timestamp('01-DEC-16 02.26.02.794000000 PM','DD-MON-RR HH.MI.SSXFF AM'),1);
53Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (2,21,2,to_timestamp('02-DEC-16 02.26.07.997000000 PM','DD-MON-RR HH.MI.SSXFF AM'),2);
54Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (3,22,3,to_timestamp('05-DEC-16 02.26.13.669000000 PM','DD-MON-RR HH.MI.SSXFF AM'),3);
55Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (4,33,4,to_timestamp('19-DEC-16 02.26.19.232000000 PM','DD-MON-RR HH.MI.SSXFF AM'),4);
56Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (5,44,5,to_timestamp('13-DEC-16 02.26.24.357000000 PM','DD-MON-RR HH.MI.SSXFF AM'),1);
57Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (6,55,6,to_timestamp('07-DEC-16 02.26.29.279000000 PM','DD-MON-RR HH.MI.SSXFF AM'),2);
58Insert into GAMEPLAY_SESSIONS (ID,LENGTH,POINTS,START_TIME,USER_GAME_ID) values (7,66,10,to_timestamp('29-DEC-16 02.26.33.639000000 PM','DD-MON-RR HH.MI.SSXFF AM'),8);
59REM INSERTING into GAMES
60SET DEFINE OFF;
61Insert into GAMES (GAME_ID,GAME_NAME) values (1,'DOTA');
62Insert into GAMES (GAME_ID,GAME_NAME) values (2,'CS');
63Insert into GAMES (GAME_ID,GAME_NAME) values (3,'Tetris');
64Insert into GAMES (GAME_ID,GAME_NAME) values (4,'Activity');
65Insert into GAMES (GAME_ID,GAME_NAME) values (5,'WoW');
66REM INSERTING into USERS
67SET DEFINE OFF;
68Insert into USERS (USER_ID,USER_NAME) values (1,'Mihai');
69Insert into USERS (USER_ID,USER_NAME) values (2,'Radu');
70Insert into USERS (USER_ID,USER_NAME) values (3,'Alice');
71Insert into USERS (USER_ID,USER_NAME) values (4,'Bobica');
72Insert into USERS (USER_ID,USER_NAME) values (5,'Marinel');
73Insert into USERS (USER_ID,USER_NAME) values (6,'Titu');
74REM INSERTING into USER_GAMES
75SET DEFINE OFF;
76Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (1,1,1);
77Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (2,2,2);
78Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (3,1,3);
79Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (4,1,4);
80Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (5,3,5);
81Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (6,5,1);
82Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (7,4,1);
83Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (8,1,4);
84--------------------------------------------------------
85-- DDL for Index USER_GAMES_PK
86--------------------------------------------------------
87
88 CREATE UNIQUE INDEX "USER_GAMES_PK" ON "USER_GAMES" ("ID")
89 ;
90--------------------------------------------------------
91-- DDL for Index USERS_PK
92--------------------------------------------------------
93
94 CREATE UNIQUE INDEX "USERS_PK" ON "USERS" ("USER_ID")
95 ;
96--------------------------------------------------------
97-- DDL for Index GAMEPLAY_SESSIONS_PK
98--------------------------------------------------------
99
100 CREATE UNIQUE INDEX "GAMEPLAY_SESSIONS_PK" ON "GAMEPLAY_SESSIONS" ("ID")
101 ;
102--------------------------------------------------------
103-- DDL for Index GAMES_PK
104--------------------------------------------------------
105
106 CREATE UNIQUE INDEX "GAMES_PK" ON "GAMES" ("GAME_ID")
107 ;
108--------------------------------------------------------
109-- DDL for Procedure INSERT_GAMEPLAY_SESSION
110--------------------------------------------------------
111set define off;
112
113 CREATE OR REPLACE PROCEDURE "INSERT_GAMEPLAY_SESSION" (
114 userID IN NUMBER,
115 gameID IN NUMBER,
116 len IN NUMBER,
117 points IN NUMBER,
118 starttime IN NUMBER)
119AS
120invalid_timestamp BOOLEAN := false;
121BEGIN
122 -- verify points
123 IF points < 0 THEN
124 RAISE_APPLICATION_ERROR(-20000, 'Points value incorrect.');
125 END IF;
126 -- verify user existence (veriy if exists record in users table with that id)
127 IF count_user_entries(userID) = 0 THEN
128 RAISE_APPLICATION_ERROR(-20001, 'No user found with that id.');
129 END IF;
130 -- verify game existence
131 IF count_game_entries(gameID) = 0 THEN
132 RAISE_APPLICATION_ERROR(-20002, 'No game found with that id.');
133 END IF;
134 FOR gameplaysession IN
135 (SELECT gs.START_TIME, gs.LENGTH
136 FROM USER_GAMES ug,
137 GAMEPLAY_SESSIONS gs
138 WHERE ug.user_id =userID
139 AND gs.USER_GAME_ID = ug.ID
140 )
141 LOOP
142 -- verify time interval
143 -- DBMS_OUTPUT.PUT_LINE(starttime);
144 IF starttime >= gameplaysession.start_time
145 THEN
146 invalid_timestamp := true;
147 END IF;
148 END LOOP;
149END insert_gameplay_session;
150
151/
152--------------------------------------------------------
153-- DDL for Procedure MOST_PLAYED_TOP
154--------------------------------------------------------
155set define off;
156
157 CREATE OR REPLACE PROCEDURE "MOST_PLAYED_TOP" IS
158rc SYS_REFCURSOR;
159BEGIN
160 open rc for
161 SELECT USERS.USER_ID, USERS.USER_NAME, USER_GAMES.game_id AS GAME_ID,
162 game_total_points(users.user_id, user_games.game_id) AS TOTAL_POINTS
163 FROM USERS,
164 USER_GAMES
165 WHERE USERS.USER_ID = USER_GAMES.USER_ID
166 AND USER_GAMES.GAME_ID =
167 (SELECT game_id FROM MOST_PLAYED_GAMES WHERE rownum <=1
168 ) ;
169END;
170
171/
172--------------------------------------------------------
173-- DDL for Procedure insert_gameplay_session
174--------------------------------------------------------
175set define off;
176
177 CREATE OR REPLACE PROCEDURE "insert_gameplay_session" (
178 userID IN NUMBER,
179 gameID IN NUMBER,
180 len IN NUMBER,
181 points IN NUMBER,
182 start_time IN TIMESTAMP)
183AS
184BEGIN
185 -- verify points
186 IF points < 0 THEN
187 RAISE_APPLICATION_ERROR(-20000, 'Points value incorrect.');
188 END IF;
189 -- verify user existence (veriy if exists record in users table with that id)
190 IF count_user_entries(userID) = 0 THEN
191 RAISE_APPLICATION_ERROR(-20001, 'No user found with that id.');
192 END IF;
193 -- verify game existence
194 IF count_game_entries(gameID) = 0 THEN
195 RAISE_APPLICATION_ERROR(-20002, 'No game found with that id.');
196 END IF;
197 FOR gameplaysession IN
198 (SELECT gs.START_TIME
199 FROM USER_GAMES ug,
200 GAMEPLAY_SESSIONS gs
201 WHERE ug.user_id =userID
202 AND gs.USER_GAME_ID = ug.ID
203 )
204 LOOP
205 DBMS_OUTPUT.PUT_LINE(gameplaysession.start_time);
206 -- verify time interval
207 END LOOP;
208END "insert_gameplay_session";
209
210/
211--------------------------------------------------------
212-- DDL for Function COMPUTE_END_TIME
213--------------------------------------------------------
214
215 CREATE OR REPLACE FUNCTION "COMPUTE_END_TIME"
216(
217 starttime IN NUMBER,
218 len IN NUMBER
219)
220return NUMBER IS
221 endtime NUMBER;
222BEGIN
223 endtime := starttime + len;
224 return endtime;
225end;
226
227/
228--------------------------------------------------------
229-- DDL for Function COUNT_GAME_ENTRIES
230--------------------------------------------------------
231
232 CREATE OR REPLACE FUNCTION "COUNT_GAME_ENTRIES"
233(gameId IN NUMBER)
234return number IS
235 nb_entries NUMBER(20) := 0;
236BEGIN
237SELECT count(*) INTO nb_entries from games
238where game_id = gameId;
239return nb_entries;
240END;
241
242/
243--------------------------------------------------------
244-- DDL for Function COUNT_USER_ENTRIES
245--------------------------------------------------------
246
247 CREATE OR REPLACE FUNCTION "COUNT_USER_ENTRIES"
248(userid IN NUMBER)
249return number IS
250 nb_entries NUMBER(20) := 0;
251BEGIN
252SELECT count(*) INTO nb_entries from users
253where user_id = userid;
254return nb_entries;
255END;
256
257/
258--------------------------------------------------------
259-- DDL for Function GAME_TOTAL_POINTS
260--------------------------------------------------------
261
262 CREATE OR REPLACE FUNCTION "GAME_TOTAL_POINTS" (
263 userid IN NUMBER,
264 gameid IN NUMBER )
265 RETURN NUMBER
266IS
267 total_points NUMBER := 0;
268 CURSOR c1
269 IS
270 SELECT points
271 FROM gameplay_sessions
272 WHERE user_game_id =
273 (SELECT id FROM user_games WHERE user_id = userid AND game_id = gameid
274 );
275BEGIN
276 FOR session_points IN c1
277 LOOP
278 total_points := total_points + session_points.points;
279 END LOOP;
280RETURN total_points;
281END;
282
283/
284--------------------------------------------------------
285-- DDL for Function count_user_entries
286--------------------------------------------------------
287
288 CREATE OR REPLACE FUNCTION "count_user_entries"
289(userid IN NUMBER)
290return number IS
291 nb_entries NUMBER(20) := 0;
292BEGIN
293SELECT count(*) INTO nb_entries from users
294where user_id = userid;
295return nb_entries;
296END;
297
298/
299--------------------------------------------------------
300-- Constraints for Table USERS
301--------------------------------------------------------
302
303 ALTER TABLE "USERS" ADD CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE;
304 ALTER TABLE "USERS" MODIFY ("USER_ID" NOT NULL ENABLE);
305--------------------------------------------------------
306-- Constraints for Table GAMES
307--------------------------------------------------------
308
309 ALTER TABLE "GAMES" ADD CONSTRAINT "GAMES_PK" PRIMARY KEY ("GAME_ID") ENABLE;
310 ALTER TABLE "GAMES" MODIFY ("GAME_ID" NOT NULL ENABLE);
311--------------------------------------------------------
312-- Constraints for Table USER_GAMES
313--------------------------------------------------------
314
315 ALTER TABLE "USER_GAMES" ADD CONSTRAINT "USER_GAMES_PK" PRIMARY KEY ("ID") ENABLE;
316 ALTER TABLE "USER_GAMES" MODIFY ("ID" NOT NULL ENABLE);
317--------------------------------------------------------
318-- Constraints for Table GAMEPLAY_SESSIONS
319--------------------------------------------------------
320
321 ALTER TABLE "GAMEPLAY_SESSIONS" ADD CONSTRAINT "GAMEPLAY_SESSIONS_PK" PRIMARY KEY ("ID") ENABLE;
322 ALTER TABLE "GAMEPLAY_SESSIONS" MODIFY ("ID" NOT NULL ENABLE);
323--------------------------------------------------------
324-- Ref Constraints for Table GAMEPLAY_SESSIONS
325--------------------------------------------------------
326
327 ALTER TABLE "GAMEPLAY_SESSIONS" ADD CONSTRAINT "GAMEPLAY_SESSIONS_FK1" FOREIGN KEY ("USER_GAME_ID")
328 REFERENCES "USER_GAMES" ("ID") ENABLE;
329--------------------------------------------------------
330-- Ref Constraints for Table USER_GAMES
331--------------------------------------------------------
332
333 ALTER TABLE "USER_GAMES" ADD CONSTRAINT "USER_GAMES_FK1" FOREIGN KEY ("GAME_ID")
334 REFERENCES "GAMES" ("GAME_ID") ENABLE;
335 ALTER TABLE "USER_GAMES" ADD CONSTRAINT "USER_GAMES_FK2" FOREIGN KEY ("USER_ID")
336 REFERENCES "USERS" ("USER_ID") ENABLE;