Query command

Query.class.php

<?php

use rdbms\DriverManager;
use rdbms\ResultSet;
use io\streams\Streams;
use util\log\LogObserver;
use util\log\LogCategory;
use util\log\StreamAppender;

/**
 * # Performs an SQL query
 *
 * - Execute query and display resultset
 *   ```sh
 *   $ xp cmd Query 'mysql://localhost/test' 'select * from account'
 *   ```
 * - Execute statement and print whether it succeeded
 *   ```sh
 *   $ xp cmd Query 'mysql://localhost/test' 'delete from account'
 *   ```
 * - Execute SQL from file
 *   ```sh
 *   $ cat input.sql | xp cmd Query 'mysql://localhost/test' -
 *   ```
 *
 * Returns a non-zero exitcode if no results are found or a statement
 * returns failure, **0** otherwise.
 */
class Query extends \util\cmd\Command {
  private $connection, $query;

  /**
   * Connection DSN, e.g. `mysql://user:pass@host[:port][/database]`
   *
   * @param   string dsn
   */
  #[@arg(position= 0)]
  public function setConnection($dsn) {
    $this->connection= DriverManager::getConnection($dsn);
    $this->connection->connect();
    $this->out->writeLine('@ ', $this->connection);
  }

  /**
   * SQL query. Use `-` to read from standard input.
   *
   * @param   string query
   */
  #[@arg(position= 1)]
  public function setQuery($query) {
    if ('-' === $query) {
      $this->query= Streams::readAll($this->in->getStream());
    } else {
      $this->query= $query;
    }
  }

  /**
   * Prints connection diagnostics to standard error
   */
  #[@arg]
  public function setDiagnostics() {
    $this->connection->addObserver(new LogObserver((new LogCategory())
      ->withAppender(new StreamAppender($this->err->getStream())))
    );
  }

  /** @return int */
  public function run() {
    $this->out->writeLine('>>> ', $this->query);

    $result= $this->connection->open($this->query);
    if ($result->isSuccess()) {
      $this->out->writeLinef('<<< OK, %d row(s) affected', $result->affected());
      return 0;
    } else {
      $this->out->writeLine('<<< Results');
      foreach ($result as $found => $record) {
        $this->out->writeLine($record);
      }
      return isset($found) ? 0 : 2;
    }
  }
}

See xp-framework/command#1