/**
* delete all expired signatures on "inactive" systems
* >> php index.php "/cron/deleteSignatures"
* @param \Base $f3
*/
function deleteSignatures(\Base $f3)
{
$signatureExpire = (int) $f3->get('PATHFINDER.CACHE.EXPIRE_SIGNATURES');
if ($signatureExpire > 0) {
$pfDB = DB\Database::instance()->getDB('PF');
$sqlDeleteExpiredSignatures = "DELETE `sys` FROM\n `system_signature` `sys` INNER JOIN\n `system` ON \n `system`.`id` = `sys`.`systemId`\n WHERE\n `system`.`active` = 0 AND\n TIMESTAMPDIFF(SECOND, `sys`.`updated`, NOW() ) > :lifetime\n ";
$pfDB->exec($sqlDeleteExpiredSignatures, ['lifetime' => $signatureExpire]);
}
}
/**
* delete all deactivated maps
* >> php index.php "/cron/deleteMapData"
* @param $f3
*/
function deleteMapData($f3)
{
$pfDB = DB\Database::instance()->getDB('PF');
$sqlDeleteDisabledMaps = "DELETE FROM\n map\n WHERE\n map.active = 0 AND\n TIMESTAMPDIFF(DAY, map.updated, NOW() ) > :deletion_time";
$pfDB->exec($sqlDeleteDisabledMaps, ['deletion_time' => self::DAYS_UNTIL_MAP_DELETION]);
$deletedMapsCount = $pfDB->count();
// Log ------------------------
$log = Controller\LogController::getLogger('cron_' . __FUNCTION__);
$log->write(sprintf(self::LOG_TEXT_MAPS, __FUNCTION__, $deletedMapsCount));
}
/**
* delete all character log data
* >> php index.php "/cron/deleteLogData"
* @param $f3
*/
function deleteLogData($f3)
{
DB\Database::instance()->getDB('PF');
$characterLogModel = Model\BasicModel::getNew('CharacterLogModel', 0);
// find "old" character logs
$characterLogs = $characterLogModel->find(['TIMESTAMPDIFF(SECOND, updated, NOW() ) > :lifetime', ':lifetime' => (int) $f3->get('PATHFINDER.CACHE.CHARACTER_LOG')]);
if (is_object($characterLogs)) {
foreach ($characterLogs as $characterLog) {
// delete log and all cached values
$characterLog->erase();
}
}
}
/**
* delete expired character authentication data
* authentication data is used for cookie based login
* >> php index.php "/cron/deleteAuthenticationData"
* @param $f3
*/
function deleteAuthenticationData($f3)
{
DB\Database::instance()->getDB('PF');
/**
* @var $authenticationModel Model\CharacterAuthenticationModel
*/
$authenticationModel = Model\BasicModel::getNew('CharacterAuthenticationModel', 0);
// find expired authentication data
$authentications = $authenticationModel->find(['(expires - NOW()) <= 0']);
if (is_object($authentications)) {
foreach ($authentications as $authentication) {
$authentication->erase();
}
}
}
/**
* check all system log tables for the correct number of system entries that will be locked
* @return array
*/
private function prepareSystemLogTables()
{
$f3 = \Base::instance();
// get information for all systems from CCP DB
$systemController = new Controller\Api\System();
$systemsData = $systemController->getSystems();
// switch DB back to pathfinder
DB\Database::instance()->setDB('PF');
// insert systems into each log table if not exist
$f3->get('DB')->begin();
foreach ($this->logTables as $tableName) {
// insert systems into jump log table
$sqlInsertSystem = "INSERT IGNORE INTO " . $tableName . " (systemId)\n VALUES(:systemId)";
foreach ($systemsData as $systemData) {
// skip WH systems -> no jump data available
if ($systemData['type']['name'] == 'k-space') {
$f3->get('DB')->exec($sqlInsertSystem, array(':systemId' => $systemData['systemId']), 0, false);
}
}
}
$f3->get('DB')->commit();
return $systemsData;
}
/**
* set "updated" field to current timestamp
* this is useful to mark a row as "changed"
*/
protected function setUpdated()
{
if ($this->_id > 0) {
$pfDB = DB\Database::instance()->getDB('PF');
$pfDB->exec(["UPDATE " . $this->table . " SET updated=NOW() WHERE id=:id"], [[':id' => $this->_id]]);
}
}
/**
* set change the DB connection
* @param string $database
* @return DB\SQL
*/
protected function getDB($database = 'PF')
{
return DB\Database::instance()->getDB($database);
}
/**
* search systems by name
* @param $f3
* @param $params
*/
public function search($f3, $params)
{
// switch DB
\DB\Database::instance();
$this->setDB('CCP');
$searchToken = '';
// check for search parameter
if (isset($params['arg1'])) {
$searchToken = $params['arg1'];
}
$this->whereQuery = "WHERE\n map_sys.solarSystemName LIKE '%" . $searchToken . "%'";
$query = $this->_getQuery();
$rows = $f3->get('DB')->exec($query);
// format result
$mapper = new Mapper\CcpSystemsMapper($rows);
$data = $mapper->getData();
echo json_encode($data);
}
/**
* set change the DB connection
* @param string $database
*/
protected function setDB($database = 'PF')
{
DB\Database::instance()->setDB($database);
}
/**
* imports all relevant map stats from CCPs API
* >> php index.php "/cron/importSystemData"
* @param $f3
*/
function importSystemData($f3)
{
$time_start = microtime(true);
// prepare system jump log table
$systemsData = $this->prepareSystemLogTables();
$time_end = microtime(true);
$execTimePrepareSystemLogTables = $time_end - $time_start;
$pfDB = DB\Database::instance()->getDB('PF');
// get current jump Data -------------------------------------------------------
$time_start = microtime(true);
$apiPath = $f3->get('PATHFINDER.API.CCP_XML') . '/map/Jumps.xml.aspx';
$apiResponse = \Web::instance()->request($apiPath, $this->apiRequestOptions);
$jumpData = [];
$updateJumps = false;
if ($apiResponse['body']) {
$xml = simplexml_load_string($apiResponse['body']);
$rowApiData = $xml->result->rowset;
foreach ($rowApiData->children() as $systemApiData) {
$attributeApiData = $systemApiData->attributes();
$systemId = $attributeApiData->solarSystemID->__toString();
$shipJumps = $attributeApiData->shipJumps->__toString();
$jumpData[$systemId] = $shipJumps;
}
$updateJumps = true;
}
$time_end = microtime(true);
$execTimeGetJumpData = $time_end - $time_start;
// get current kill Data -------------------------------------------------------
$time_start = microtime(true);
$apiPath = $f3->get('PATHFINDER.API.CCP_XML') . '/map/Kills.xml.aspx';
$apiResponse = \Web::instance()->request($apiPath, $this->apiRequestOptions);
$killData = [];
$updateKills = false;
if ($apiResponse['body']) {
$xml = simplexml_load_string($apiResponse['body']);
$rowApiData = $xml->result->rowset;
foreach ($rowApiData->children() as $systemApiData) {
$attributeApiData = $systemApiData->attributes();
$systemId = $attributeApiData->solarSystemID->__toString();
$shipKills = $attributeApiData->shipKills->__toString();
$podKills = $attributeApiData->podKills->__toString();
$factionKills = $attributeApiData->factionKills->__toString();
$killData[$systemId] = ['shipKills' => $shipKills, 'podKills' => $podKills, 'factionKills' => $factionKills];
}
$updateKills = true;
}
$time_end = microtime(true);
$execTimeGetKillData = $time_end - $time_start;
// update system log tables -----------------------------------------------------
$time_start = microtime(true);
// make sure last update is (at least) 1h ago
$pfDB->begin();
foreach ($this->logTables as $key => $tableName) {
$sql = "UPDATE\n " . $tableName . "\n SET\n value24 = value23,\n value23 = value22,\n value22 = value21,\n value21 = value20,\n value20 = value19,\n value19 = value18,\n value18 = value17,\n value17 = value16,\n value16 = value15,\n value15 = value14,\n value14 = value13,\n value13 = value12,\n value12 = value11,\n value11 = value10,\n value10 = value9,\n value9 = value8,\n value8 = value7,\n value7 = value6,\n value6 = value5,\n value5 = value4,\n value4 = value3,\n value3 = value2,\n value2 = value1,\n value1 = :value\n WHERE\n systemId = :systemId\n ";
foreach ($systemsData as $systemData) {
if ($key == 'jumps' && $updateJumps) {
// update jump data (if available)
$currentJumps = 0;
if (array_key_exists($systemData['systemId'], $jumpData)) {
$currentJumps = $jumpData[$systemData['systemId']];
}
$pfDB->exec($sql, array(':systemId' => $systemData['systemId'], ':value' => $currentJumps), 0, false);
} else {
if ($updateKills) {
// update kill data (if available)
$currentKills = 0;
if (array_key_exists($systemData['systemId'], $killData)) {
$currentKillData = $killData[$systemData['systemId']];
$currentKills = $currentKillData[$key];
}
$pfDB->exec($sql, array(':systemId' => $systemData['systemId'], ':value' => $currentKills), 0, false);
}
}
}
}
$pfDB->commit();
$time_end = microtime(true);
$execTimeUpdateTables = $time_end - $time_start;
// Log ------------------------
$log = Controller\LogController::getLogger('cron_' . __FUNCTION__);
$log->write(sprintf(self::LOG_TEXT, __FUNCTION__, $execTimePrepareSystemLogTables, $execTimeGetJumpData, $execTimeGetKillData, $execTimeUpdateTables));
}
/**
* delete all character log data
* >> php index.php "/cron/deleteLogData"
* @param $f3
*/
function deleteLogData($f3)
{
DB\Database::instance()->setDB('PF');
$sqlDeleteCharacterLogs = "TRUNCATE TABLE character_log";
$f3->get('DB')->exec($sqlDeleteCharacterLogs);
}
/**
* init the complete database
* - create tables
* - create indexes
* - set default static values
* @param $dbKey
* @return array
*/
protected function bootstrapDB($dbKey)
{
$db = DB\Database::instance()->getDB($dbKey);
$checkTables = [];
if ($db) {
// set/change default "character set" and "collation"
$db->exec('ALTER DATABASE ' . $db->name() . ' CHARACTER SET ' . self::getRequiredMySqlVariables('CHARACTER_SET_DATABASE') . ' COLLATE ' . self::getRequiredMySqlVariables('COLLATION_DATABASE'));
// setup tables
foreach ($this->databases[$dbKey]['models'] as $modelClass) {
$checkTables[] = call_user_func($modelClass . '::setup');
}
}
return $checkTables;
}
/** get indexed (cache) data information
* @return array
*/
protected function getIndexData()
{
// active DB and tables are required for obtain index data
if ($this->databaseCheck) {
$indexInfo = ['SystemNeighbourModel' => ['action' => [['task' => 'buildIndex', 'label' => 'build', 'icon' => 'fa-refresh', 'btn' => 'btn-primary']], 'table' => Model\BasicModel::getNew('SystemNeighbourModel')->getTable(), 'count' => DB\Database::instance()->getRowCount(Model\BasicModel::getNew('SystemNeighbourModel')->getTable())], 'WormholeModel' => ['action' => [['task' => 'exportTable', 'label' => 'export', 'icon' => 'fa-download', 'btn' => 'btn-default'], ['task' => 'importTable', 'label' => 'import', 'icon' => 'fa-upload', 'btn' => 'btn-primary']], 'table' => Model\BasicModel::getNew('WormholeModel')->getTable(), 'count' => DB\Database::instance()->getRowCount(Model\BasicModel::getNew('WormholeModel')->getTable())], 'SystemWormholeModel' => ['action' => [['task' => 'exportTable', 'label' => 'export', 'icon' => 'fa-download', 'btn' => 'btn-default'], ['task' => 'importTable', 'label' => 'import', 'icon' => 'fa-upload', 'btn' => 'btn-primary']], 'table' => Model\BasicModel::getNew('SystemWormholeModel')->getTable(), 'count' => DB\Database::instance()->getRowCount(Model\BasicModel::getNew('SystemWormholeModel')->getTable())], 'ConstellationWormholeModel' => ['action' => [['task' => 'exportTable', 'label' => 'export', 'icon' => 'fa-download', 'btn' => 'btn-default'], ['task' => 'importTable', 'label' => 'import', 'icon' => 'fa-upload', 'btn' => 'btn-primary']], 'table' => Model\BasicModel::getNew('ConstellationWormholeModel')->getTable(), 'count' => DB\Database::instance()->getRowCount(Model\BasicModel::getNew('ConstellationWormholeModel')->getTable())]];
} else {
$indexInfo = ['SystemNeighbourModel' => ['action' => [], 'table' => 'Fix database errors first!']];
}
return $indexInfo;
}
public function __construct()
{
$this->db = Database::getInstance(DB_HOST, DB_USER, DB_PASSWORD);
}