php-spark/plugins/com.noccy.pdo/Commands/PdoQueryCommand.php

<?php

namespace SparkPlug\Com\Noccy\Pdo\Commands;

use Spark\Commands\Command;
use Symfony\Component\Console\Helper\Table;
use Symfony\Component\Console\Helper\TableSeparator;
use Symfony\Component\Console\Input\InputArgument;
use Symfony\Component\Console\Input\InputInterface;
use Symfony\Component\Console\Input\InputOption;
use Symfony\Component\Console\Output\OutputInterface;

class PdoQueryCommand extends Command {
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $source = $input->getOption("res");
        $sourcePdo = get_resource($source)->getPDO();
        if (!$sourcePdo) {
            $output->writeln("<error>Invalid resource: {$source}</>");
            return Command::INVALID;
        }

        $box = $input->getOption('box');
        $query = $input->getArgument('query');
        $vert = $input->getOption("vertical");
        $unserialize = $input->getOption("unserialize");

        $stmt = $sourcePdo->query($query);
        $stmt->execute();

        $csv = $input->getOption("csv");

        $table = new Table($output);
        $table->setStyle($box?"box":"compact");
        $hasColumns = false;
        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
            if ($csv) {
                $output->writeln(
                    join(",", array_map(function($v) {
                        return str_contains(',',$v) ? var_export($v,true) : $v;
                    }, $row))
                );
                continue;
            }
            if (!$hasColumns) {
                if ($vert) {
                    $table->setHeaders([ "Field", "VarType", "Value" ]);
                } else {
                    $table->setHeaders(array_keys($row));
                }
                $hasColumns = true;
            } else {
                if ($vert) {
                    if ($box) {
                        $table->addRow(new TableSeparator());
                    } else {
                        $table->addRow(["","","-----"]);
                    }
                }
            }
            if ($vert) {
                foreach ($row as $k=>$v) {
                    $vv = $v;
                    if ($unserialize) {
                        $j = @json_decode($v);
                        $p = @unserialize($v);
                        if ($j) {
                            $v = $j;
                            $vv = json_encode($v, JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES);
                        } elseif ($p) {
                            $v = $p;
                            $vv = json_encode($p, JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES);
                        }
                    }
                    $table->addRow([ $k, gettype($v), $vv ]);
                }
            } else {
                $table->addRow($row);
            }
        }
        $table->render();

        return Command::SUCCESS;
    }
    protected function configure() {
        $this->setName("pdo:query");
        $this->setDescription("Run a query against a defined PDO connection");
        $this->addOption("res", "r", InputOption::VALUE_REQUIRED, "Resource to query", "db");
        $this->addOption("csv",null, InputOption::VALUE_NONE, "Output as CSV");
        $this->addOption("vertical", "l", InputOption::VALUE_NONE, "Print result as rows instead of columns");
        $this->addOption("box", null, InputOption::VALUE_NONE, "Use boxed table");
        $this->addOption("unserialize", "u", InputOption::VALUE_NONE, "Attempt to unserialize serialized data");
        $this->addArgument("query", InputArgument::REQUIRED, "SQL query to execute");
    }
}