rtpegjvE

1<?php
2require_once ("MySQLDB.php");
3include("connect.php");
4
5$host = 'localhost';
6$dbUser = 'root';
7$dbPass = '';
8$dbName = 'ScoreboardDatabase';
9
10
11// create a new empty database 
12$dbErrorHandling = new DBErrorHandling($host , $dbUser , $dbPass , $dbName );
13$dbErrorHandling->createDatabase();
14$dbErrorHandling->selectDatabase();
15
16$sql = "drop table if exists leaderBoardGaming";
17$result = $dbErrorHandling->query($sql);
18
19$sql = "drop table if exists userActive";
20$result = $dbErrorHandling->query($sql);
21
22$sql = "drop table if exists userRegistry";
23$result = $dbErrorHandling->query($sql);
24
25$sql = "drop procedure if exists passwordVerify";
26$result = $dbErrorHandling->query($sql);
27// create tables
28$table="userRegistry";
29$sql = "CREATE TABLE `ScoreboardDatabase`.`userRegistry` (
30  `userID` INT NOT NULL AUTO_INCREMENT,
31  `userName` VARCHAR(45) NOT NULL,
32  `userEmail` VARCHAR(45) NOT NULL,
33  `userPassword` VARCHAR(255) NOT NULL,
34  `userIDFriends` INT NULL,
35  `userCountry` VARCHAR(45) NOT NULL,
36  PRIMARY KEY (`userID`));"  ;
37$dbErrorHandling->createTable($table, $sql);
38
39$table="userActiveTable";
40$sql = "CREATE TABLE `ScoreboardDatabase`.`userActive` (
41  `activeID` INT NOT NULL AUTO_INCREMENT,
42  `userID` INT NULL,
43  PRIMARY KEY (`activeID`),
44  UNIQUE INDEX `userID_UNIQUE` (`userID`),
45  CONSTRAINT `userID`
46    FOREIGN KEY (`userID`)
47    REFERENCES `scoreboarddatabase`.`userregistry` (`userID`)
48    ON DELETE NO ACTION
49    ON UPDATE NO ACTION);"  ;
50$dbErrorHandling->createTable($table, $sql);
51
52$table="leaderBoardGamingTable";
53$sql = "CREATE TABLE `ScoreboardDatabase`.`leaderBoardGaming` (
54  `leaderBoardID` INT NOT NULL AUTO_INCREMENT,
55  `userIDL` INT NOT NULL,
56  `userWin` INT NULL,
57  `userLoss` INT NULL,
58  `userScore` INT NULL,
59  PRIMARY KEY (`leaderBoardID`),
60  UNIQUE INDEX `userID_LeaderBoardGaming` (`userIDL`),
61  CONSTRAINT `userIDL`
62    FOREIGN KEY (`userIDL`)
63    REFERENCES `scoreboarddatabase`.`userregistry` (`userID`)
64    ON DELETE NO ACTION
65    ON UPDATE NO ACTION);"  ;
66$dbErrorHandling->createTable($table, $sql);
67
68// Hash passwords 
69$clementsHashedPassword = password_hash('clementspassword123', PASSWORD_DEFAULT);
70$johnsHashedPassword = password_hash('Pa$$w0rd', PASSWORD_DEFAULT);
71
72// insert data
73$sql = "INSERT INTO `userRegistry` (`userName`, `userEmail`, `userPassword`,  `userIDFriends`, `userCountry`)
74VALUES ('Clement', 'Testmail.com', '$clementsHashedPassword', '2', 'New Zealand'), ('John', 'Testmail.com', '$johnsHashedPassword', '1', 'America');";
75$dbErrorHandling->insertRow($sql);
76
77$sql = "INSERT INTO `leaderBoardGaming` (`userIDL`, `userWin`, `userLoss`, `userScore`)
78VALUES ('1', '2', '1', '1'), ('2', '3', '1', '2');";
79
80$dbErrorHandling->insertRow($sql);
81
82// insert procedure
83$dbErrorHandling->query("CREATE PROCEDURE `passwordVerify` (IN `name` VARCHAR(45), IN `password` VARCHAR(45), OUT `passwordVerification` BOOLEAN)
84BEGIN
85SELECT IF(`userRegistry`.`userPassword` = `password`, TRUE, FALSE) AS 'verify'
86FROM `userRegistry`	
87WHERE `userName` = `name`;
88END;");
89
90// insert triggers
91$sql = "CREATE TRIGGER `ScoreboardDatabase`.`leaderBoardGaming_BEFORE_INSERT` BEFORE INSERT ON `leaderBoardGaming` FOR EACH ROW
92BEGIN
93	SET NEW.userScore = NEW.userWin / NEW.userLoss;
94END;";
95
96$dbErrorHandling->insertRow($sql);
97
98$sql = "CREATE TRIGGER `ScoreboardDatabase`.`leaderBoardGaming_BEFORE_UPDATE` BEFORE UPDATE ON `leaderBoardGaming` FOR EACH ROW
99BEGIN
100	SET NEW.userScore = NEW.userWin / NEW.userLoss;
101END;";
102
103$dbErrorHandling->insertRow($sql);
104?> 
105<br><br>