public static function verifyTable($database) { require_once realpath(dirname(__FILE__) . '/UserManager.php'); require_once realpath(dirname(__FILE__) . '/AddonManager.php'); UserManager::verifyTable($database); AddonManager::verifyTable($database); if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_ratings` (\n\t\t\t`id` INT AUTO_INCREMENT,\n\t\t\t`blid` INT NOT NULL,\n\t\t\t`aid` INT NOT NULL,\n\t\t\t`rating` TINYINT NOT NULL,\n\t\t\tFOREIGN KEY (`blid`)\n\t\t\t\tREFERENCES users(`blid`)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE,\n\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE,\n\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Unable to create table addon_ratings: " . $database->error()); } }
public static function loadBasicDummyData() { TestManager::clearDatabase(); $database = new DatabaseManager(); UserManager::verifyTable($database); AddonManager::verifyTable($database); BoardManager::verifyTable($database); TagManager::verifyTable($database); GroupManager::verifyTable($database); DependencyManager::verifyTable($database); CommentManager::verifyTable($database); RatingManager::verifyTable($database); BuildManager::verifyTable($database); StatManager::verifyTable($database); ScreenshotManager::verifyTable($database); if (!$database->query("INSERT INTO `addon_boards` (name, video, description) VALUES ('General Content', 'general_content_bg', 'Bricks, Events, Sounds, Prints, Environments, and much more!')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `addon_boards` (name, video, description) VALUES ('Minigames', 'minigames_bg', 'Weapons, Vehicles, Gamemodes, and all your gaming needs!')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `addon_boards` (name, video, description) VALUES ('Client Mods', 'client_mods_bg', 'Mods that run on your client.')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `addon_boards` (name, video, description) VALUES ('Bargain Bin', 'bargain_bin_bg', 'A home for \\'special\\' content.')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `users` (username, blid, password, email, salt, verified) VALUES ('testuser', '4833', '1d8436e97ef95a7a6151f47b909167c77cfe1985ee5500efa8d46cfe825abc59', '*****@*****.**', '273eb4', '1')")) { throw new Exception("Database error: " . $database->error()); } //the default json types likely need to be reworked if (!$database->query("INSERT INTO `addon_addons` (board, blid, name, filename, description, approved, versionInfo, authorInfo, reviewInfo) VALUES ('1', '4833', 'crapy adon', 'sciprt_hax.zip', 'bad addone pls delete', '1', '{}', '[]', '[]')")) { throw new Exception("Database error: " . $database->error()); } StatManager::addStatsToAddon(1); if (!$database->query("INSERT INTO `addon_tags` (name, base_color, icon) VALUES ('dum tag', 'ff6600', 'brokenimage')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `addon_tagmap` (aid, tid) VALUES ('1', '1')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `group_groups` (leader, name, description, color, icon) VALUES ('4833', 'legion of dumies', 'a group for people who just want to be in a group', '00ff00', 'brokenimage')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `group_usermap` (gid, blid, administrator) VALUES ('1', '4833', '1')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `addon_comments` (blid, aid, comment) VALUES ('4833', '1', 'glorious addon comrade')")) { throw new Exception("Database error: " . $database->error()); } if (!$database->query("INSERT INTO `addon_ratings` (blid, aid, rating) VALUES ('4833', '1', '1')")) { throw new Exception("Database error: " . $database->error()); } }
public static function verifyTable($database) { UserManager::verifyTable($database); AddonManager::verifyTable($database); if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_stats` (\n\t\t\t`aid` INT NOT NULL,\n\t\t\t`rating` FLOAT,\n\n\t\t\t`totalDownloads` INT NOT NULL DEFAULT 0,\n\n\t\t\t`iterationDownloads` INT NOT NULL DEFAULT 0,\n\n\t\t\t`webDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t`ingameDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t`updateDownloads` INT NOT NULL DEFAULT 0,\n\n\t\t\tKEY (`totalDownloads`),\n\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE)")) { throw new Exception("Failed to create addon stats table: " . $database->error()); } if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_stats_hist` (\n\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n\t\t\t`aid` INT NOT NULL DEFAULT 0,\n\n\t\t\t`webDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t`ingameDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t`updateDownloads` INT NOT NULL DEFAULT 0,\n\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE,\n\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Failed to create addon stat history table: " . $database->error()); } }
public static function getNewAddons($count = 10) { $count += 0; $newestAddonIDs = apc_fetch('newestAddonIDs_' . $count, $success); if ($success === false) { $database = new DatabaseManager(); AddonManager::verifyTable($database); $resource = $database->query("SELECT * FROM `addon_addons` ORDER BY `uploadDate` DESC LIMIT " . $database->sanitize($count)); if (!$resource) { throw new Exception("Database error: " . $database->error()); } $newestAddonIDs = []; while ($row = $resource->fetch_object()) { $newestAddonIDs[] = AddonManager::getFromID($row->id, $row)->getID(); } $resource->close(); apc_store('newestAddonIDs_' . $count, $newestAddonIDs, AddonManager::$searchCacheTime); } return $newestAddonIDs; }
public static function submitRating($aid, $blid, $rating) { if ($rating < 1) { $rating = 1; } if ($rating > 5) { $rating = 5; } $rating = ceil($rating); $db = new DatabaseManager(); AddonManager::verifyTable($db); $res = $db->query($sq = "SELECT COUNT(*) FROM `addon_ratings` WHERE `blid`='" . $db->sanitize($blid) . "' AND `aid`='" . $db->sanitize($aid) . "'"); $ret = $res->fetch_row(); if (!isset($ret[0]) || $ret[0] == 0) { $res = $db->query($sq = "INSERT INTO `addon_ratings` (`blid`, `aid`, `rating`) VALUES (\n '" . $db->sanitize($blid) . "',\n '" . $db->sanitize($aid) . "',\n '" . $db->sanitize($rating) . "')"); } else { $db->update("addon_ratings", ["blid" => $blid, "aid" => $aid], ["rating" => $rating]); } //recalculate total $res = $db->query("SELECT * FROM `addon_ratings` WHERE `aid`='" . $db->sanitize($aid) . "'"); $ratings = array(); while ($obj = $res->fetch_object()) { $ratings[] = $obj->rating; } $avg = array_sum($ratings) / sizeof($ratings); $db->update("addon_addons", ["id" => $aid], ["rating" => $avg]); echo $db->error(); return $avg; }
public static function verifyTable($database) { if ($database->debug()) { AddonManager::verifyTable($database); //to do: change addon_tags to something more general so build and stuff can be tagged if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_tags` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`name` varchar(16) NOT NULL,\n\t\t\t\t`base_color` varchar(6) NOT NULL,\n\t\t\t\t`icon` text NOT NULL,\n\t\t\t\t`important` TINYINT NOT NULL DEFAULT 0,\n\t\t\t\tKEY (`name`),\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating tag table: " . $database->error()); } //this table might not need a primary key if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_tagmap` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`aid` INT NOT NULL,\n\t\t\t\t`tid` INT NOT NULL,\n\t\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\t\tREFERENCES addon_addons(id)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tFOREIGN KEY (`tid`)\n\t\t\t\t\tREFERENCES addon_tags(id)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating tagmap table: " . $database->error()); } } }
public static function verifyTable($database) { if ($database->debug()) { UserManager::verifyTable($database); AddonManager::verifyTable($database); TagManager::verifyTable($database); BuildManager::verifyTable($database); GroupManager::verifyTable($database); if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_stats` (\n\t\t\t\t`aid` INT NOT NULL,\n\t\t\t\t`rating` FLOAT,\n\t\t\t\t`totalDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`iterationDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`webDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`ingameDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`updateDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\tKEY (`totalDownloads`),\n\t\t\t\tKEY (`iterationDownloads`),\n\t\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE)")) { throw new Exception("Failed to create addon stats table: " . $database->error()); } if (!$database->query("CREATE TABLE IF NOT EXISTS `build_stats` (\n\t\t\t\t`bid` INT NOT NULL,\n\t\t\t\t`rating` FLOAT,\n\t\t\t\t`totalDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`iterationDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\tKEY (`totalDownloads`),\n\t\t\t\tKEY (`iterationDownloads`),\n\t\t\t\tFOREIGN KEY (`bid`)\n\t\t\t\t\tREFERENCES build_builds(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE)")) { throw new Exception("Failed to create build stats table: " . $database->error()); } if (!$database->query("CREATE TABLE IF NOT EXISTS `tag_stats` (\n\t\t\t\t`tid` INT NOT NULL,\n\t\t\t\t`totalDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`iterationDownloads` INT NOT NULL DEFAULT 0,\n\t\t\t\tKEY (`totalDownloads`),\n\t\t\t\tKEY (`iterationDownloads`),\n\t\t\t\tFOREIGN KEY (`tid`)\n\t\t\t\t\tREFERENCES addon_tags(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE)")) { throw new Exception("Failed to create tag stats table: " . $database->error()); } //includes a lot of foreign keys, not sure if it is a good idea to include them all if (!$database->query("CREATE TABLE IF NOT EXISTS `statistics` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n\t\t\t\t`users` INT NOT NULL DEFAULT 0,\n\t\t\t\t`addons` INT NOT NULL DEFAULT 0,\n\t\t\t\t`downloads` INT NOT NULL DEFAULT 0,\n\t\t\t\t`groups` INT NOT NULL DEFAULT 0,\n\t\t\t\t`comments` INT NOT NULL DEFAULT 0,\n\t\t\t\t`builds` INT NOT NULL DEFAULT 0,\n\t\t\t\t`tags` INT NOT NULL DEFAULT 0,\n\t\t\t\t`addon0` INT NOT NULL,\n\t\t\t\t`addon1` INT NOT NULL,\n\t\t\t\t`addon2` INT NOT NULL,\n\t\t\t\t`addon3` INT NOT NULL,\n\t\t\t\t`addon4` INT NOT NULL,\n\t\t\t\t`addon5` INT NOT NULL,\n\t\t\t\t`addon6` INT NOT NULL,\n\t\t\t\t`addon7` INT NOT NULL,\n\t\t\t\t`addon8` INT NOT NULL,\n\t\t\t\t`addon9` INT NOT NULL,\n\t\t\t\t`addonDownloads0` INT NOT NULL,\n\t\t\t\t`addonDownloads1` INT NOT NULL,\n\t\t\t\t`addonDownloads2` INT NOT NULL,\n\t\t\t\t`addonDownloads3` INT NOT NULL,\n\t\t\t\t`addonDownloads4` INT NOT NULL,\n\t\t\t\t`addonDownloads5` INT NOT NULL,\n\t\t\t\t`addonDownloads6` INT NOT NULL,\n\t\t\t\t`addonDownloads7` INT NOT NULL,\n\t\t\t\t`addonDownloads8` INT NOT NULL,\n\t\t\t\t`addonDownloads9` INT NOT NULL,\n\t\t\t\t`tag0` INT NOT NULL,\n\t\t\t\t`tag1` INT NOT NULL,\n\t\t\t\t`tag2` INT NOT NULL,\n\t\t\t\t`tag3` INT NOT NULL,\n\t\t\t\t`tag4` INT NOT NULL,\n\t\t\t\t`tagDownloads0` INT NOT NULL,\n\t\t\t\t`tagDownloads1` INT NOT NULL,\n\t\t\t\t`tagDownloads2` INT NOT NULL,\n\t\t\t\t`tagDownloads3` INT NOT NULL,\n\t\t\t\t`tagDownloads4` INT NOT NULL,\n\t\t\t\t`build0` INT NOT NULL,\n\t\t\t\t`build1` INT NOT NULL,\n\t\t\t\t`build2` INT NOT NULL,\n\t\t\t\t`buildDownloads0` INT NOT NULL,\n\t\t\t\t`buildDownloads1` INT NOT NULL,\n\t\t\t\t`buildDownloads2` INT NOT NULL,\n\t\t\t\tKEY (`date`),\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Failed to create stat history table: " . $database->error()); } } }
public static function verifyTable($database) { if ($database->debug()) { require_once realpath(dirname(__FILE__) . '/UserManager.php'); require_once realpath(dirname(__FILE__) . '/AddonManager.php'); UserManager::verifyTable($database); //we need users table to exist before we can create this one AddonManager::verifyTable($database); if (!$database->query("CREATE TABLE IF NOT EXISTS `build_builds` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`blid` INT NOT NULL,\n\t\t\t\t`name` VARCHAR(60) NOT NULL,\n\t\t\t\t`filename` VARCHAR(60) NOT NULL,\n\t\t\t\t`bricks` INT NOT NULL DEFAULT 0,\n\t\t\t\t`description` TEXT NOT NULL,\n\t\t\t\tFOREIGN KEY (`blid`)\n\t\t\t\t\tREFERENCES users(`blid`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tKEY (`name`),\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating builds table: " . $database->error()); } //to do: probably should move this to another class, maybe make dependencyManager more general if (!$database->query("CREATE TABLE IF NOT EXISTS `build_dependency` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`bid` INT NOT NULL,\n\t\t\t\t`aid` INT NOT NULL,\n\t\t\t\tFOREIGN KEY (`bid`)\n\t\t\t\t\tREFERENCES build_builds(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("unable to create build dependency table: " . $database->error()); } } }
public static function verifyTable($database) { if ($database->debug()) { require_once realpath(dirname(__FILE__) . '/UserManager.php'); require_once realpath(dirname(__FILE__) . '/AddonManager.php'); require_once realpath(dirname(__FILE__) . '/BuildManager.php'); UserManager::verifyTable($database); //we need users table to exist before we can create this one AddonManager::verifyTable($database); BuildManager::verifyTable($database); //we need to be able to build a url out of this data too // UNIQUE KEY (`filename`), if (!$database->query("CREATE TABLE IF NOT EXISTS `screenshots` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`blid` INT NOT NULL,\n\t\t\t\t`name` VARCHAR(60),\n\t\t\t\t`filename` VARCHAR(60),\n\t\t\t\t`description` TEXT,\n\t\t\t\tFOREIGN KEY (`blid`)\n\t\t\t\t\tREFERENCES users(`blid`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tKEY (`name`),\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating screenshots table: " . $database->error()); } if (!$database->query("CREATE TABLE IF NOT EXISTS `build_screenshotmap` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`sid` INT NOT NULL,\n\t\t\t\t`bid` INT NOT NULL,\n\t\t\t\t`primary` TINYINT NOT NULL DEFAULT 0,\n\t\t\t\tFOREIGN KEY (`sid`)\n\t\t\t\t\tREFERENCES screenshots(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tFOREIGN KEY (`bid`)\n\t\t\t\t\tREFERENCES build_builds(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating build_screenshotmap table: " . $database->error()); } if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_screenshotmap` (\n\t\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t\t`sid` INT NOT NULL,\n\t\t\t\t`aid` INT NOT NULL,\n\t\t\t\tFOREIGN KEY (`sid`)\n\t\t\t\t\tREFERENCES screenshots(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\t\tON UPDATE CASCADE\n\t\t\t\t\tON DELETE CASCADE,\n\t\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating addon_screenshotmap table: " . $database->error()); } } }
public static function verifyTable($database) { AddonManager::verifyTable($database); //this table might not need a primary key if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_dependencies` (\n\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t`target` INT NOT NULL,\n\t\t\t`requirement` INT NOT NULL,\n\t\t\tFOREIGN KEY (`target`)\n\t\t\t\tREFERENCES addon_addons(id)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE,\n\t\t\tFOREIGN KEY (`requirement`)\n\t\t\t\tREFERENCES addon_addons(id)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE,\n\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Error creating dependency table: " . $database->error()); } }
public static function verifyTable($database) { require_once realpath(dirname(__FILE__) . '/UserManager.php'); require_once realpath(dirname(__FILE__) . '/AddonManager.php'); UserManager::verifyTable($database); AddonManager::verifyTable($database); if (!$database->query("CREATE TABLE IF NOT EXISTS `addon_comments` (\n\t\t\t`id` INT NOT NULL AUTO_INCREMENT,\n\t\t\t`blid` INT NOT NULL,\n\t\t\t`aid` INT NOT NULL,\n\t\t\t`comment` TEXT NOT NULL,\n\t\t\t`timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n\t\t\t`lastedit` TIMESTAMP NULL,\n\t\t\tKEY (`timestamp`),\n\t\t\tFOREIGN KEY (`aid`)\n\t\t\t\tREFERENCES addon_addons(`id`)\n\t\t\t\tON UPDATE CASCADE\n\t\t\t\tON DELETE CASCADE,\n\t\t\tPRIMARY KEY (`id`))")) { throw new Exception("Unable to create table addon_comments: " . $database->error()); } }