Query SQLite Through Third-party "proxy" With Laravel Eloquent
I am attempting to query (manipulation optional) a SQLite database using Laravel Eloquent. A driver already exists for this an is delightfully easy to use.
However, the database is remote and part of a video game. The game supports RCON which allows me to send commands and in this case, I can send SQL statements.
My current state:
- Send a SQL statement prefixed with "sql" to remote machine via third party library:
sql SELECT id, level, guild, isAlive FROM characters
- Receive a line delimited string, prefixed by record number:
id | level | guild | isAlive |
#0 1183 | 14 | 60 | 1 |
#1 636 | 10 | 60 | 1 |
#2 41 | 30 | 60 | 1 |
#3 47 | 27 | 60 | 1 |
#4 49 | 38 | 60 | 1 |
#5 403 | 32 | 60 | 1 |
#6 50 | 31 | 60 | 1 |
#7 1389 | 44 | 60 | 1 |
- Parse the output line by line in a particularly unsavory method and manually assign them to a custom built model/class.
I would really like to incorporate Eloquent in any capacity rather than use my own custom classes. As I typed this post out, I realized I don't believe I'll be able to "piggy back" off of the existing SQLite driver and this would likely be a completely new driver altogether.
However, to those more experienced than myself, do you have any suggestions or methods of approaching this situation?
Answer
Ultimately I ended up processing the output of the RCON command. The downside is that it is particularly static. I have to build around things like the columns being selected with the SQL statement and adjusting the results to proper types.
I am using https://github.com/gorcon/rcon-cli and wrapped a query class around it:
class RconDatabase
{
const RCON_EXECUTABLE = __DIR__ . '/../bin/rcon';
public function __construct()
{
}
public function query($sql)
{
if (!is_executable(self::RCON_EXECUTABLE)) throw new FilePermissionException('Unable to execute RCON');
$cmd = self::RCON_EXECUTABLE.' -a '.Config::get('rcon.host').':'.Config::get('rcon.port').' -p '.Config::get('rcon.password').' -c "sql '. $sql .'"';
$output = shell_exec($cmd);
if ($output == null) throw new RconConnectionException('No response from RCON server');
if (strpos($output, 'authentication failed') !== false) throw new RconAuthenticationException();
if (strpos($output, 'dial tcp') !== false) throw new RconNetworkException();
$lines = preg_split("/((\r?\n)|(\r\n?))/", $output);
$results = array();
$last_column = 0;
for ($i = 0; $i < count($lines); $i++) {
if (empty($lines[$i])) continue;
$columns = str_getcsv($lines[$i], '|');
for ($x = 0; $x < count($columns); $x++) {
if ($i == 0 && empty($columns[$x])) {
$last_column = $x;
continue;
}
if ($x == 0 && preg_match('/^#[0-9]+\s+(\S+)/', $columns[0], $match))
$columns[$x] = $match[1];
$columns[$x] = trim($columns[$x]);
}
if ($i == 0) continue;
if ($last_column > 0) unset($columns[$last_column]);
array_push($results, $columns);
}
return $results;
}
}
Related Questions
- → "failed to open stream" error when executing "migrate:make"
- → October CMS Plugin Routes.php not registering
- → OctoberCMS Migrate Table
- → OctoberCMS Rain User plugin not working or redirecting
- → October CMS Custom Mail Layout
- → October CMS - How to correctly route
- → October CMS create a multi select Form field
- → October CMS - Conditionally Load a Different Page
- → How to disable assets combining on development in OctoberCMS
- → October CMS - Radio Button Ajax Click Twice in a Row Causes Content to disappear
- → OctoberCms component: How to display all ID(items) instead of sorting only one ID?
- → In OctoberCMS how do you find the hint path?
- → How to register middlewares in OctoberCMS plugin?