pWMXKb29

1-- JWR assignment database;
2
3DROP TABLE IF EXISTS `bookmarks`;
4DROP TABLE IF EXISTS `reviews`;
5DROP TABLE IF EXISTS `games`;
6DROP TABLE IF EXISTS `genres`;
7DROP TABLE IF EXISTS `users`;
8
9CREATE TABLE `users` (
10    id INT AUTO_INCREMENT PRIMARY KEY,
11    uname VARCHAR(40),
12    pass VARCHAR(255),
13    salt VARCHAR(255),
14    is_admin BOOLEAN
15);
16
17CREATE TABLE `genres` (
18    id varchar(3) NOT NULL PRIMARY KEY,
19    title VARCHAR(50)
20);
21
22CREATE TABLE `games` (
23    id INT AUTO_INCREMENT PRIMARY KEY,
24    title VARCHAR(150),
25    image VARCHAR(255),
26    genre varchar(3) NOT NULL,
27    rating INT,
28    FOREIGN KEY (genre) REFERENCES genres(id) ON DELETE CASCADE
29);
30
31CREATE TABLE `bookmarks` (
32    user_id INT,
33    game_id INT,
34    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
35    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
36);
37
38CREATE TABLE `reviews` (
39    id INT AUTO_INCREMENT PRIMARY KEY,
40    user_id int,
41    game_id int,
42    rating INT,
43    title VARCHAR(150),
44    review TEXT,
45    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
46    FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
47);
48
49
50INSERT INTO `genres` VALUES ("str", "Strategy");
51INSERT INTO `genres` VALUES ("rpg", "Role-Playing Game");
52INSERT INTO `genres` VALUES ("fps", "First Person Shooter");
53INSERT INTO `genres` VALUES ("sim", "Simulation Game");
54INSERT INTO `genres` VALUES ("???", "Other");
55
56-- game list (yes, I'm including ones with strange letters on purpose)
57INSERT INTO `games` VALUES (NULL, "Sid Meier's Civilization V: Brave New World", "", "str", 85); -- 8 Jul 2013
58INSERT INTO `games` VALUES (NULL, "Crusader Kings II", "", "str", 82); -- 14 feb 2012
59INSERT INTO `games` VALUES (NULL, "Warcraft III: Reforged ", "", "str", 60); -- 28 jan 2020
60
61INSERT INTO `games` VALUES (NULL, "Else Heart.Break()", "", "rpg", 79); -- Sep 24 2015
62INSERT INTO `games` VALUES (NULL, "Shadowrun: Dragonfall - Director's Cut", "", "rpg", 87); -- 18 sep 2014
63INSERT INTO `games` VALUES (NULL, "Stardew Valley", "", "rpg", 89); -- 26 feb 2016 (it has the RPG tag on steam, it counts...)
64INSERT INTO `games` VALUES (NULL, "Disco Elysium", "", "rpg", 91); -- 15 oct 2019
65
66INSERT INTO `games` VALUES (NULL, "RimWorld", "", "sim", 87); -- 17 oct 2018
67INSERT INTO `games` VALUES (NULL, "Tom Clancy's Rainbow Six® Siege", "", "fps", 0); -- 1 dec 2015, metacritic score wasn't on steam page
68INSERT INTO `games` VALUES (NULL, "Euro Truck Simulator 2", "", "sim", 79); -- 18 oct 2012
69INSERT INTO `games` VALUES (NULL, "Farming Simulator 19", "", "sim", 73); -- 19 Nov, 2018
70INSERT INTO `games` VALUES (NULL, "Train Simulator 2020", "", "sim", 0); -- 12 jul 2009 *shrugs at release date on steam...*
71
72INSERT INTO `games` VALUES (NULL, "Project Zomboid", "", "rpg", 87); -- 8 nov 2013
73INSERT INTO `games` VALUES (NULL, "Shadowrun Returns", "", "rpg", 76); -- 25 july 2013
74INSERT INTO `games` VALUES (NULL, "Shadowrun: Hong Kong - Extended Edition", "", "rpg", 81); -- 20 aug 2015
75
76INSERT INTO `games` VALUES (NULL, "Cave Story+", "", "???", 81); -- 22 nov 2011
77INSERT INTO `games` VALUES (NULL, "Sorcery! Parts 1 & 2", "", "???", 69); -- 2 feb 2016
78INSERT INTO `games` VALUES (NULL, "Dwarf Fortress", "", "???", 0); -- 'time is subjective' isn't a valid release date...
79
80-- users
81-- salts should be random, using strings here, algorithm is sha1( $pass . $salt ); not secure, but it's an assignment.
82INSERT INTO `users` VALUES (NULL, "jwalto", "244cad413fa94db1c686ff5bfc6777241ceaa3ea", "abc123", 1); -- password42
83INSERT INTO `bookmarks` VALUES (1, 1);
84INSERT INTO `bookmarks` VALUES (1, 2);
85
86INSERT INTO `users` VALUES (NULL, "pwillic", "38bf8a5df0a227b697045c1b29a25a759e391f9b", "java123", 0); -- hanabi
87INSERT INTO `bookmarks` VALUES (2, 3);
88INSERT INTO `bookmarks` VALUES (2, 4);
89INSERT INTO `bookmarks` VALUES (2, 5);
90
91INSERT INTO `users` VALUES (NULL, "rpgs", "ba494cde63bd5d092e916b4083e27cda7c306d43", "html42", 0); -- rpgsftw
92INSERT INTO `bookmarks` VALUES (3, 4);
93INSERT INTO `bookmarks` VALUES (3, 5);
94INSERT INTO `bookmarks` VALUES (3, 6);
95INSERT INTO `bookmarks` VALUES (3, 7);
96INSERT INTO `bookmarks` VALUES (3, 13);
97
98INSERT INTO `users` VALUES (NULL, "sims", "c65e822545b8596c484112ac62a9194c6043c724", "eadlc", 0); -- simsftw
99INSERT INTO `bookmarks` VALUES (4, 8);
100INSERT INTO `bookmarks` VALUES (4, 10);
101INSERT INTO `bookmarks` VALUES (4, 11);
102INSERT INTO `bookmarks` VALUES (4, 12);