/**
  *
  */
 public function createSluggardDB()
 {
     $tables = array("users", "usersSeen", "storage", "authentications");
     $tableCreateCode = array("users" => "\r\n            BEGIN;\r\n            CREATE TABLE IF NOT EXISTS `users` (\r\n                `id` INTEGER PRIMARY KEY AUTOINCREMENT,\r\n                `serverID` BIGINT(20) NOT NULL,\r\n                `userID` BIGINT(20) NOT NULL,\r\n                `discordID` BIGINT(20) NOT NULL,\r\n                `characterID` INT(16) NOT NULL,\r\n                `corporationID` VARCHAR(255) NOT NULL,\r\n                `allianceID` VARCHAR(255) NOT NULL,\r\n                `authString` VARCHAR(255) NOT NULL,\r\n                `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP\r\n            );\r\n            CREATE UNIQUE INDEX userID ON users (userID);\r\n            CREATE INDEX serverID ON users (serverID);\r\n            CREATE INDEX corporationID ON users (corporationID);\r\n            CREATE INDEX allianceID ON users (allianceID);\r\n            COMMIT;", "usersSeen" => "\r\n            BEGIN;\r\n            CREATE TABLE IF NOT EXISTS `usersSeen` (\r\n                `id` INTEGER PRIMARY KEY,\r\n                `name` VARCHAR(255) NOT NULL,\r\n                `isAdmin` TINYINT(1) NOT NULL DEFAULT '0',\r\n                `lastSeen` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\r\n                `lastSpoke` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',\r\n                `lastStatus` VARCHAR(50) NULL DEFAULT NULL,\r\n                `lastWritten` TEXT NULL\r\n            );\r\n            CREATE INDEX name ON usersSeen (name);\r\n            COMMIT;", "storage" => "\r\n            BEGIN;\r\n            CREATE TABLE IF NOT EXISTS `storage` (\r\n                `id` INTEGER PRIMARY KEY AUTOINCREMENT,\r\n                `key` VARCHAR(255) NOT NULL,\r\n                `value` VARCHAR(255) NOT NULL\r\n            );\r\n            CREATE UNIQUE INDEX key ON storage (key);\r\n            COMMIT;", "authentications" => "\r\n            BEGIN;\r\n            CREATE TABLE IF NOT EXISTS `authentications` (\r\n                `discordID` INTEGER PRIMARY KEY,\r\n                `characterID` BIGINT(40) NOT NULL,\r\n                `corporationID` BIGINT(40) NOT NULL,\r\n                `allianceID` BIGINT(40) NOT NULL,\r\n                `guildID` BIGINT(128) DEFAULT 0 NOT NULL\r\n            );\r\n            CREATE UNIQUE INDEX discordID ON authentications (discordID);\r\n            COMMIT;\r\n            ", "fittings" => "\r\n            BEGIN;\r\n            CREATE TABLE `fittings` (\r\n                `guildID` INTEGER PRIMARY KEY,\r\n                `fittingName` VARCHAR(255) NOT NULL,\r\n                `fittingURL` VARCHAR(255) NOT NULL\r\n            );\r\n            CREATE UNIQUE INDEX fittingName ON fittings (guildID, fittingName);\r\n            COMMIT;");
     $dbName = $this->config->get("botName", "bot");
     // Does the file exist?
     if (!file_exists(BASEDIR . "/config/database/{$dbName}.sqlite")) {
         touch(BASEDIR . "/config/database/{$dbName}.sqlite");
     }
     // Check if the tables exist, if not, create them
     foreach ($tables as $table) {
         $exists = $this->sluggardDB->queryField("SELECT name FROM sqlite_master WHERE type = 'table' AND name = :name", "name", array(":name" => $table));
         if (!$exists) {
             $this->log->warn("Creating {$table} in {$dbName}.sqlite, since it does not exist");
             $this->sluggardDB->execute(trim($tableCreateCode[$table]));
         }
     }
 }
Esempio n. 2
0
 /**
  * @param $url
  * @param $storagePath
  * @return bool
  */
 public function getLargeData($url, $storagePath)
 {
     try {
         $readHandle = fopen($url, "rb");
         $writeHandle = fopen($storagePath, "w+b");
         if (!$readHandle || !$writeHandle) {
             return false;
         }
         while (!feof($readHandle)) {
             if (fwrite($writeHandle, fread($readHandle, 4096)) == FALSE) {
                 return false;
             }
         }
         fclose($readHandle);
         fclose($writeHandle);
         return true;
     } catch (\Exception $e) {
         $this->log->warn("cURL Error: " . $e->getMessage());
         return false;
     }
 }
Esempio n. 3
0
 /**
  *
  */
 public function createCCPDB()
 {
     $ccpDataURL = "https://www.fuzzwork.co.uk/dump/sqlite-latest.sqlite.bz2";
     $ccpDataMD5URL = "https://www.fuzzwork.co.uk/dump/sqlite-latest.sqlite.bz2.md5";
     $dbLocation = BASEDIR . "/config/database/";
     $md5 = explode(" ", $this->curl->getData($ccpDataMD5URL))[0];
     $lastSeenMd5 = $this->storage->get("ccpDataMd5");
     // If the last seen md5, isn't equal the current seen md5, we'll update!
     if ($lastSeenMd5 !== $md5) {
         try {
             $this->log->notice("Updating CCP SQLite Database");
             $this->log->notice("Downloading bz2 file, and writing it to {$dbLocation}ccpData.sqlite.bz2");
             $downloadedData = $this->curl->getLargeData($ccpDataURL, "{$dbLocation}ccpData.sqlite.bz2");
             if ($downloadedData == false) {
                 $this->log->warn("Error: File not downloaded successfully!");
                 die;
             }
             $this->log->notice("Opening bz2 file");
             $sqliteData = bzopen("{$dbLocation}ccpData.sqlite.bz2", "r");
             $this->log->notice("Reading from bz2 file");
             $data = "";
             while (!feof($sqliteData)) {
                 $data .= bzread($sqliteData, 4096);
             }
             $this->log->notice("Writing bz2 file contents into .sqlite file");
             file_put_contents("{$dbLocation}ccpData.sqlite", $data);
             $this->log->notice("Deleting bz2 file");
             unlink("{$dbLocation}ccpData.sqlite.bz2");
             $this->log->notice("Creating mapCelestials view");
             $this->ccpDB->execute("CREATE VIEW mapAllCelestials AS SELECT itemID, itemName, typeName, mapDenormalize.typeID, solarSystemName, mapDenormalize.solarSystemID, mapDenormalize.constellationID, mapDenormalize.regionID, mapRegions.regionName, orbitID, mapDenormalize.x, mapDenormalize.y, mapDenormalize.z FROM mapDenormalize JOIN invTypes ON (mapDenormalize.typeID = invTypes.typeID) JOIN mapSolarSystems ON (mapSolarSystems.solarSystemID = mapDenormalize.solarSystemID) JOIN mapRegions ON (mapDenormalize.regionID = mapRegions.regionID) JOIN mapConstellations ON (mapDenormalize.constellationID = mapConstellations.constellationID)");
             $this->log->notice("CCP Database updated!");
             $this->storage->set("ccpDataMd5", $md5);
         } catch (\Exception $e) {
             $this->log->warn("Error updating the CCPDatabase. Bot can't run");
             die;
         }
     }
 }