test_prep_sql

CREATE OR REPLACE PROCEDURE insert_gameplay_session(
    userID     IN NUMBER,
    gameID     IN NUMBER,
    len        IN NUMBER,
    points     IN NUMBER,
    starttime IN NUMBER)
AS
invalid_timestamp BOOLEAN := false;
BEGIN
  -- verify points
  IF points < 0 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Points value incorrect.');
  END IF;
  -- verify user existence (veriy if exists record in users table with that id)
  IF count_user_entries(userID) = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'No user found with that id.');
  END IF;
  -- verify game existence
  IF count_game_entries(gameID) = 0 THEN
    RAISE_APPLICATION_ERROR(-20002, 'No game found with that id.');
  END IF;
  FOR gameplaysession IN
  (SELECT gs.START_TIME, gs.LENGTH
  FROM USER_GAMES ug,
    GAMEPLAY_SESSIONS gs
  WHERE ug.user_id    =userID
  AND gs.USER_GAME_ID = ug.ID
  )
  LOOP
    -- verify time interval
   -- DBMS_OUTPUT.PUT_LINE(starttime);
    IF starttime >= gameplaysession.start_time
    THEN
      invalid_timestamp := true;
    END IF;
  END LOOP;
END insert_gameplay_session;

SET serveroutput ON
begin
  insert_gameplay_session(1,1,2,10,99);
end;


set define off;
CREATE OR REPLACE FUNCTION count_user_entries
(userid IN NUMBER)
return number IS
  nb_entries NUMBER(20) := 0;
BEGIN
SELECT count(*) INTO nb_entries from users
where user_id = userid;
return nb_entries;
END;
/
SET serveroutput ON
begin
 DBMS_OUTPUT.PUT_LINE(count_user_entries(1));
end;

set define off;
CREATE OR REPLACE FUNCTION count_game_entries
(gameId IN NUMBER)
return number IS
  nb_entries NUMBER(20) := 0;
BEGIN
SELECT count(*) INTO nb_entries from games
where game_id = gameId;
return nb_entries;
END;
/
SET serveroutput ON
begin
 DBMS_OUTPUT.PUT_LINE(count_game_entries(1));
end;

CREATE OR REPLACE FUNCTION compute_end_time
(
 starttime IN NUMBER,
 len IN NUMBER
)
return NUMBER IS
  endtime NUMBER;
BEGIN
 endtime := starttime + len;
 return endtime;
end;
/
SET serveroutput ON
begin
 DBMS_OUTPUT.PUT_LINE(compute_end_time(34,10));
end;


--d)
CREATE VIEW most_played_games AS
  SELECT  g.GAME_ID, COUNT(g.game_id) AS times
  FROM user_games g, gameplay_sessions gs
  WHERE g.GAME_ID = gs.USER_GAME_ID
  GROUP BY  g.GAME_ID
  ORDER BY COUNT(g.game_id) DESC
;
 select game_id from MOST_PLAYED_GAMES WHERE rownum <=1 ;

CREATE OR REPLACE FUNCTION game_total_points(
    userid IN NUMBER,
    gameid IN NUMBER )
  RETURN NUMBER
IS
  total_points NUMBER := 0;
  CURSOR c1
  IS
    SELECT points
    FROM gameplay_sessions
    WHERE user_game_id =
      (SELECT id FROM user_games WHERE user_id = userid AND game_id = gameid
      );
BEGIN
  FOR session_points IN c1
  LOOP
    total_points := total_points + session_points.points;
  END LOOP;
RETURN total_points;
END;
/
SET serveroutput ON
begin
 DBMS_OUTPUT.PUT_LINE(game_total_points(1,1));
end;

CREATE OR REPLACE PROCEDURE most_played_top IS
rc SYS_REFCURSOR;
BEGIN
  open rc for
  SELECT USERS.USER_ID, USERS.USER_NAME, USER_GAMES.game_id AS GAME_ID,
  game_total_points(users.user_id, user_games.game_id) AS TOTAL_POINTS
  FROM USERS,
    USER_GAMES
  WHERE USERS.USER_ID    = USER_GAMES.USER_ID
  AND USER_GAMES.GAME_ID =
    (SELECT game_id FROM MOST_PLAYED_GAMES WHERE rownum <=1
    ) ;
END;

SET serveroutput ON
begin
 most_played_top;
end;