src/Commands/Eve/Update/Sde.php
<?php
/*
* This file is part of SeAT
*
* Copyright (C) 2015 to present Leon Jacobs
*
* This program is free software; you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation; either version 2 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*/
namespace Seat\Eveapi\Commands\Eve\Update;
use GuzzleHttp\Client;
use Illuminate\Console\Command;
use Illuminate\Database\QueryException;
use Illuminate\Foundation\Bus\DispatchesJobs;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Schema;
use Seat\Eveapi\Models\Sde\MapDenormalize;
use Seat\Services\Helpers\AnalyticsContainer;
use Seat\Services\Jobs\Analytics;
use Seat\Services\Settings\Seat;
/**
* Class Sde.
*
* @package Seat\Eveapi\Commands\Eve\Update
*/
class Sde extends Command
{
use DispatchesJobs;
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'eve:update:sde
{--local : Check the local config file for the version string}
{--force : Force re-installation of an existing SDE version}';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Updates the EVE Online SDE Data';
/**
* The Guzzle Instance.
*
* @var
*/
protected $guzzle;
/**
* The response Json from the resources repo.
*
* @var
*/
protected $json;
/**
* The SDE file storage path.
*
* @var
*/
protected $storage_path;
/**
* Create a new command instance.
*/
public function __construct()
{
parent::__construct();
}
/**
* Query the eveseat/resources repository for SDE
* related information.
*
* @throws \Seat\Services\Exceptions\SettingException
*/
public function handle()
{
// Start by warning the user about the command that will be run
$this->comment('Warning! This Laravel command uses exec() to execute a ');
$this->comment('shell command to import an extracted dump. Due');
$this->comment('to the way the command is constructed, should someone ');
$this->comment('view the current running processes of your server, they ');
$this->comment('will be able to see your SeAT database users password.');
$this->line('');
$this->line('Ensure that you understand this before continuing.');
// Test that we have valid Database details. An exception
// will be thrown if this fails.
DB::connection()->getDatabaseName();
if (! $this->confirm('Are you sure you want to update to the latest EVE SDE?', true)) {
$this->warn('Exiting');
return $this::SUCCESS;
}
// Request the json from eveseat/resources
$this->json = $this->getJsonResource();
// Ensure we got a response, else fail.
if (! $this->json) {
$this->warn('Unable to reach the resources endpoint.');
return $this::FAILURE;
}
// Check if we should attempt getting the
// version string locally
if ($this->option('local')) {
$version_number = env('SDE_VERSION', null);
if (! is_null($version_number)) {
$this->comment('Using locally sourced version number of: ' . $version_number);
$this->json->version = env('SDE_VERSION');
} else {
$this->warn('Unable to determine the version number override. ' .
'Using remote version: ' . $this->json->version);
}
}
// add extra tables registered on behalf providers
$extra_tables = config('seat.sde.tables', []);
$this->json->tables = array_unique(array_merge($this->json->tables, $extra_tables));
sort($this->json->tables, SORT_STRING);
$all_sde_tables_exist = true;
foreach ($this->json->tables as $table) {
if(! Schema::hasTable($table)) {
$all_sde_tables_exist = false;
}
}
// Avoid an existing SDE to be accidentally installed again
// except if the user explicitly ask for it
if ($this->json->version == Seat::get('installed_sde') &&
$this->option('force') == false &&
$all_sde_tables_exist
) {
$this->warn('You are already running the latest SDE version.');
$this->warn('If you want to install it again, run this command with --force argument.');
return $this::SUCCESS;
}
// Ask for a confirmation before installing an existing SDE version
if ($this->option('force') == true) {
$this->warn('You will re-download and install the current SDE version.');
if (! $this->confirm('Are you sure ?', true)) {
$this->info('Nothing has been updated.');
return $this::SUCCESS;
}
}
// Show a final confirmation with some info on what
// we are going to be doing.
$this->info('The local SDE data will be updated to ' . $this->json->version);
$this->info(count($this->json->tables) . ' tables will be updated: ' .
implode(', ', $this->json->tables));
$this->info('Download format will be: ' . $this->json->format);
$this->line('');
if (DB::connection()->getDriverName() == 'mysql')
$this->info(sprintf('The SDE will be imported to mysql://%s@%s:%d/%s',
config('database.connections.mysql.username'),
config('database.connections.mysql.host'),
config('database.connections.mysql.port'),
config('database.connections.mysql.database')));
if (in_array(DB::connection()->getDriverName(), ['pgsql', 'postgresql']))
$this->info(sprintf('The SDE will be imported to pgsql://%s@%s:%d/%s',
config('database.connections.pgsql.username'),
config('database.connections.pgsql.host'),
config('database.connections.pgsql.port'),
config('database.connections.pgsql.database')));
if (! $this->confirm('Does the above look OK?', true)) {
$this->warn('Exiting');
return $this::SUCCESS;
}
if (! $this->isStorageOk()) {
$this->error('Storage path is not OK. Please check permissions');
return $this::INVALID;
}
// Download the SDE's
$this->getSde();
$this->importSde();
$this->explodeMap();
Seat::set('installed_sde', $this->json->version);
$this->line('SDE Update Command Complete');
// Analytics
dispatch(new Analytics((new AnalyticsContainer)
->set('type', 'event')
->set('ec', 'queues')
->set('ea', 'update_sde')
->set('el', 'console')
->set('ev', $this->json->version)));
return $this::SUCCESS;
}
/**
* Query the eveseat/resources repository for SDE
* related information.
*
* @return mixed
*/
public function getJsonResource()
{
$result = $this->getGuzzle()->request('GET',
'https://raw.githubusercontent.com/eveseat/resources/master/sde.json', [
'headers' => ['Accept' => 'application/json'],
]);
if ($result->getStatusCode() != 200)
return json_encode([]);
return json_decode($result->getBody());
}
/**
* Get an instance of Guzzle.
*
* @return \GuzzleHttp\Client
*/
public function getGuzzle()
{
if ($this->guzzle)
return $this->guzzle;
$this->guzzle = new Client();
return $this->guzzle;
}
/**
* Check that the storage path is ok. I needed it
* will be automatically created.
*
* @return bool
*/
public function isStorageOk()
{
$storage = storage_path() . '/sde/' . $this->json->version . '/';
$this->info('Storage path is: ' . $storage);
if (File::isWritable(storage_path())) {
// Check that the path exists
if (! File::exists($storage))
File::makeDirectory($storage, 0755, true);
// Set the storage path
$this->storage_path = $storage;
return true;
}
return false;
}
/**
* Download the EVE Sde from Fuzzwork and save it
* in the storage_path/sde folder.
*/
public function getSde()
{
$this->line('Downloading...');
if (DB::connection()->getDriverName() == 'mysql')
$this->downloadMysqlSde();
if (in_array(DB::connection()->getDriverName(), ['pgsql', 'postgresql']))
$this->downloadPgSqlSde();
$this->line('');
}
private function downloadMysqlSde()
{
$bar = $this->getProgressBar(count($this->json->tables));
foreach ($this->json->tables as $table) {
$url = str_replace(':version', $this->json->version, $this->json->url) .
$table . $this->json->format;
$destination = $this->storage_path . $table . $this->json->format;
$file_handler = fopen($destination, 'w');
$result = $this->getGuzzle()->request('GET', $url, [
'sink' => $file_handler, ]);
fclose($file_handler);
if ($result->getStatusCode() != 200)
$this->error('Unable to download ' . $url .
'. The HTTP response was: ' . $result->getStatusCode());
$bar->advance();
}
$bar->finish();
}
private function downloadPgSqlSde()
{
$dump_filename = str_replace('sde', 'postgres', $this->json->version) . '.dmp.bz2';
$url = str_replace(':version', $this->json->version, $this->json->url) . $dump_filename;
$destination = $this->storage_path . $dump_filename;
$file_handler = fopen($destination, 'w');
$result = $this->getGuzzle()->request('GET', $url, [
'sink' => $file_handler, ]);
fclose($file_handler);
if ($result->getStatusCode() != 200)
$this->error('Unable to download ' . $url .
'. The HTTP response was: ' . $result->getStatusCode());
}
/**
* Get a new progress bar to display based on the
* amount of iterations we expect to use.
*
* @param $iterations
* @return \Symfony\Component\Console\Helper\ProgressBar
*/
public function getProgressBar($iterations)
{
$bar = $this->output->createProgressBar($iterations);
$bar->setFormat(' %current%/%max% [%bar%] %percent:3s%% %elapsed:6s% %memory:6s%');
return $bar;
}
/**
* Extract the SDE files downloaded and run the MySQL command
* to import them into the database.
*/
public function importSde()
{
$this->line('Importing...');
if (DB::connection()->getDriverName() == 'mysql')
$this->importMysqlSde();
if (in_array(DB::connection()->getDriverName(), ['pgsql', 'postgresql']))
$this->importPgSqlSde();
$this->line('');
}
private function importMysqlSde()
{
$bar = $this->getProgressBar(count($this->json->tables));
foreach ($this->json->tables as $table) {
$archive_path = $this->storage_path . $table . $this->json->format;
$extracted_path = $this->storage_path . $table . '.sql';
if (! File::exists($archive_path)) {
$this->warn($archive_path . ' seems to be invalid. Skipping.');
continue;
}
$this->uncompressFile($archive_path, $extracted_path);
// With the output file ready, prepare the scary exec() command
// that should be run. A sample $import_command is:
// mysql -u root -h 127.0.0.1 seat < /tmp/sample.sql
$import_command = 'mysql -u ' . config('database.connections.mysql.username') .
// Check if the password is longer than 0. If not, don't specify the -p flag
(strlen(config('database.connections.mysql.password')) ? ' -p' : '')
// Append this regardless. Escape special chars in the password too.
. escapeshellcmd(config('database.connections.mysql.password')) .
' -h ' . config('database.connections.mysql.host') .
' -P ' . config('database.connections.mysql.port') .
' ' . config('database.connections.mysql.database') .
' < ' . $extracted_path;
// Run the command... (*scared_face*)
exec($import_command, $output, $exit_code);
if ($exit_code !== 0)
$this->error('Warning: Import failed with exit code ' .
$exit_code . ' and command outut: ' . implode('\n', $output));
$bar->advance();
}
$bar->finish();
}
private function importPgSqlSde()
{
$archive_path = $this->storage_path . str_replace('sde', 'postgres', $this->json->version) . '.dmp.bz2';
$extracted_path = $this->storage_path . str_replace('sde', 'postgres', $this->json->version) . '.dmp';
$this->uncompressFile($archive_path, $extracted_path);
try {
DB::statement('DROP OWNED BY yaml CASCADE');
} catch (QueryException $e) {
$this->warn('Unable to drop yaml role - role is not found.');
}
DB::statement('DROP ROLE IF EXISTS yaml');
$this->info('Spawning yaml role');
DB::statement('CREATE ROLE yaml WITH NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1');
DB::statement('GRANT yaml TO seat');
$import_command = 'PGPASSWORD=' . config('database.connections.pgsql.password') .
' pg_restore -d ' . config('database.connections.pgsql.database') .
' -h ' . config('database.connections.pgsql.host') .
' -p ' . config('database.connections.pgsql.port') .
' -U ' . config('database.connections.pgsql.username') .
' -t ' . implode(' -t ', $this->json->tables) .
' ' . $extracted_path;
exec($import_command, $output, $exit_code);
if ($exit_code !== 0)
$this->error('Warning: Import failed with exit code ' .
$exit_code . ' and command outut: ' . implode('\n', $output));
}
/**
* Explode mapDenormalize table into celestial sub-tables.
*/
private function explodeMap()
{
// extract regions
DB::table('regions')->truncate();
DB::table('regions')
->insertUsing([
'region_id', 'name',
], DB::table('mapDenormalize')->where('groupID', MapDenormalize::REGION)
->select('itemID', 'itemName'));
// extract constellations
DB::table('constellations')->truncate();
DB::table('constellations')
->insertUsing([
'constellation_id', 'region_id', 'name',
], DB::table('mapDenormalize')->where('groupID', MapDenormalize::CONSTELLATION)
->select('itemID', 'regionID', 'itemName'));
// extract solar systems
DB::table('solar_systems')->truncate();
DB::table('solar_systems')
->insertUsing([
'system_id', 'constellation_id', 'region_id', 'name', 'security',
], DB::table('mapDenormalize')->where('groupID', MapDenormalize::SYSTEM)
->select('itemID', 'constellationID', 'regionID', 'itemName', 'security'));
// extract stars
DB::table('stars')->truncate();
DB::table('stars')
->insertUsing([
'star_id', 'system_id', 'constellation_id', 'region_id', 'name', 'type_id',
], DB::table('mapDenormalize')->where('groupID', MapDenormalize::SUN)
->select('itemID', 'solarSystemID', 'constellationID', 'regionID', 'itemName', 'typeID'));
// extract planets
DB::table('planets')->truncate();
DB::table('planets')
->insertUsing([
'planet_id', 'system_id', 'constellation_id', 'region_id', 'name', 'type_id',
'x', 'y', 'z', 'radius', 'celestial_index',
], DB::table('mapDenormalize')->where('groupID', MapDenormalize::PLANET)
->select('itemID', 'solarSystemID', 'constellationID', 'regionID', 'itemName', 'typeID',
'x', 'y', 'z', 'radius', 'celestialIndex'));
// extract moons
DB::table('moons')->truncate();
DB::table('moons')
->insertUsing([
'moon_id', 'planet_id', 'system_id', 'constellation_id', 'region_id', 'name', 'type_id',
'x', 'y', 'z', 'radius', 'celestial_index', 'orbit_index',
], DB::table('mapDenormalize')->where('groupID', MapDenormalize::MOON)
->select('itemID', 'orbitID', 'solarSystemID', 'constellationID', 'regionID', 'itemName', 'typeID',
'x', 'y', 'z', 'radius', 'celestialIndex', 'orbitIndex'));
}
private function uncompressFile($archive_path, $extracted_target_path): void
{
// Get 2 handles ready for both the in and out files
$input_file = bzopen($archive_path, 'r');
$output_file = fopen($extracted_target_path, 'w');
// Write the $output_file in chunks
while ($chunk = bzread($input_file, 4096))
fwrite($output_file, $chunk, 4096);
// Close the files
bzclose($input_file);
fclose($output_file);
}
}