/**
*
*/
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]));
}
}
}
/**
* @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;
}
}
/**
*
*/
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;
}
}
}
