test_prep_export_sql

--------------------------------------------------------
--  File created - Sunday-December-18-2016
--------------------------------------------------------
--------------------------------------------------------
--  DDL for View MOST_PLAYED_GAMES
--------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "MOST_PLAYED_GAMES" ("GAME_ID", "TIMES") 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;
--------------------------------------------------------
--  DDL for Table GAMEPLAY_SESSIONS
--------------------------------------------------------

  CREATE TABLE "GAMEPLAY_SESSIONS"
   (	"ID" NUMBER(20,0),
	"LENGTH" NUMBER(20,0),
	"POINTS" NUMBER(20,0),
	"START_TIME" TIMESTAMP (6),
	"USER_GAME_ID" NUMBER(20,0)
   ) ;
--------------------------------------------------------
--  DDL for Table GAMES
--------------------------------------------------------

  CREATE TABLE "GAMES"
   (	"GAME_ID" NUMBER,
	"GAME_NAME" VARCHAR2(20 BYTE)
   ) ;
--------------------------------------------------------
--  DDL for Table USERS
--------------------------------------------------------

  CREATE TABLE "USERS"
   (	"USER_ID" NUMBER(20,0),
	"USER_NAME" VARCHAR2(20 BYTE)
   ) ;
--------------------------------------------------------
--  DDL for Table USER_GAMES
--------------------------------------------------------

  CREATE TABLE "USER_GAMES"
   (	"ID" NUMBER(20,0),
	"USER_ID" NUMBER(20,0),
	"GAME_ID" NUMBER(20,0)
   ) ;
REM INSERTING into GAMEPLAY_SESSIONS
SET DEFINE OFF;
Insert 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);
Insert 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);
Insert 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);
Insert 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);
Insert 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);
Insert 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);
Insert 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);
REM INSERTING into GAMES
SET DEFINE OFF;
Insert into GAMES (GAME_ID,GAME_NAME) values (1,'DOTA');
Insert into GAMES (GAME_ID,GAME_NAME) values (2,'CS');
Insert into GAMES (GAME_ID,GAME_NAME) values (3,'Tetris');
Insert into GAMES (GAME_ID,GAME_NAME) values (4,'Activity');
Insert into GAMES (GAME_ID,GAME_NAME) values (5,'WoW');
REM INSERTING into USERS
SET DEFINE OFF;
Insert into USERS (USER_ID,USER_NAME) values (1,'Mihai');
Insert into USERS (USER_ID,USER_NAME) values (2,'Radu');
Insert into USERS (USER_ID,USER_NAME) values (3,'Alice');
Insert into USERS (USER_ID,USER_NAME) values (4,'Bobica');
Insert into USERS (USER_ID,USER_NAME) values (5,'Marinel');
Insert into USERS (USER_ID,USER_NAME) values (6,'Titu');
REM INSERTING into USER_GAMES
SET DEFINE OFF;
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (1,1,1);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (2,2,2);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (3,1,3);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (4,1,4);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (5,3,5);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (6,5,1);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (7,4,1);
Insert into USER_GAMES (ID,USER_ID,GAME_ID) values (8,1,4);
--------------------------------------------------------
--  DDL for Index USER_GAMES_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "USER_GAMES_PK" ON "USER_GAMES" ("ID")
  ;
--------------------------------------------------------
--  DDL for Index USERS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "USERS_PK" ON "USERS" ("USER_ID")
  ;
--------------------------------------------------------
--  DDL for Index GAMEPLAY_SESSIONS_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "GAMEPLAY_SESSIONS_PK" ON "GAMEPLAY_SESSIONS" ("ID")
  ;
--------------------------------------------------------
--  DDL for Index GAMES_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "GAMES_PK" ON "GAMES" ("GAME_ID")
  ;
--------------------------------------------------------
--  DDL for Procedure INSERT_GAMEPLAY_SESSION
--------------------------------------------------------
set define off;

  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;

/
--------------------------------------------------------
--  DDL for Procedure MOST_PLAYED_TOP
--------------------------------------------------------
set define off;

  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;

/
--------------------------------------------------------
--  DDL for Procedure insert_gameplay_session
--------------------------------------------------------
set define off;

  CREATE OR REPLACE PROCEDURE "insert_gameplay_session" (
    userID     IN NUMBER,
    gameID     IN NUMBER,
    len        IN NUMBER,
    points     IN NUMBER,
    start_time IN TIMESTAMP)
AS
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
  FROM USER_GAMES ug,
    GAMEPLAY_SESSIONS gs
  WHERE ug.user_id    =userID
  AND gs.USER_GAME_ID = ug.ID
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE(gameplaysession.start_time);
    -- verify time interval
  END LOOP;
END "insert_gameplay_session";

/
--------------------------------------------------------
--  DDL for Function COMPUTE_END_TIME
--------------------------------------------------------

  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;

/
--------------------------------------------------------
--  DDL for Function COUNT_GAME_ENTRIES
--------------------------------------------------------

  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;

/
--------------------------------------------------------
--  DDL for Function COUNT_USER_ENTRIES
--------------------------------------------------------

  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;

/
--------------------------------------------------------
--  DDL for Function GAME_TOTAL_POINTS
--------------------------------------------------------

  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;

/
--------------------------------------------------------
--  DDL for Function count_user_entries
--------------------------------------------------------

  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;

/
--------------------------------------------------------
--  Constraints for Table USERS
--------------------------------------------------------

  ALTER TABLE "USERS" ADD CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID") ENABLE;
  ALTER TABLE "USERS" MODIFY ("USER_ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table GAMES
--------------------------------------------------------

  ALTER TABLE "GAMES" ADD CONSTRAINT "GAMES_PK" PRIMARY KEY ("GAME_ID") ENABLE;
  ALTER TABLE "GAMES" MODIFY ("GAME_ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table USER_GAMES
--------------------------------------------------------

  ALTER TABLE "USER_GAMES" ADD CONSTRAINT "USER_GAMES_PK" PRIMARY KEY ("ID") ENABLE;
  ALTER TABLE "USER_GAMES" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Constraints for Table GAMEPLAY_SESSIONS
--------------------------------------------------------

  ALTER TABLE "GAMEPLAY_SESSIONS" ADD CONSTRAINT "GAMEPLAY_SESSIONS_PK" PRIMARY KEY ("ID") ENABLE;
  ALTER TABLE "GAMEPLAY_SESSIONS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
--  Ref Constraints for Table GAMEPLAY_SESSIONS
--------------------------------------------------------

  ALTER TABLE "GAMEPLAY_SESSIONS" ADD CONSTRAINT "GAMEPLAY_SESSIONS_FK1" FOREIGN KEY ("USER_GAME_ID")
	  REFERENCES "USER_GAMES" ("ID") ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table USER_GAMES
--------------------------------------------------------

  ALTER TABLE "USER_GAMES" ADD CONSTRAINT "USER_GAMES_FK1" FOREIGN KEY ("GAME_ID")
	  REFERENCES "GAMES" ("GAME_ID") ENABLE;
  ALTER TABLE "USER_GAMES" ADD CONSTRAINT "USER_GAMES_FK2" FOREIGN KEY ("USER_ID")
	  REFERENCES "USERS" ("USER_ID") ENABLE;