src/Expression.php
<?php
declare(strict_types=1);
namespace Atk4\Dsql;
use Doctrine\DBAL\Connection as DbalConnection;
use Doctrine\DBAL\Exception as DbalException;
use Doctrine\DBAL\Platforms\PostgreSQL94Platform;
use Doctrine\DBAL\Result as DbalResult;
/**
* @phpstan-implements \ArrayAccess<int|string, mixed>
*/
class Expression implements Expressionable, \ArrayAccess
{
/** @const string "[]" in template, escape as parameter */
protected const ESCAPE_PARAM = 'param';
/** @const string "{}" in template, escape as identifier */
protected const ESCAPE_IDENTIFIER = 'identifier';
/** @const string "{{}}" in template, escape as identifier, but keep input with special characters like "." or "(" unescaped */
protected const ESCAPE_IDENTIFIER_SOFT = 'identifier-soft';
/** @const string keep input as is */
protected const ESCAPE_NONE = 'none';
/** @var string */
protected $template;
/**
* Configuration accumulated by calling methods such as Query::field(), Query::table(), etc.
*
* $args['custom'] is used to store hash of custom template replacements.
*
* This property is made public to ease customization and make it accessible
* from Connection class for example.
*
* @var array<int|string, mixed>
*/
public $args = ['custom' => []];
/**
* As per PDO, escapeParam() will convert value into :a, :b, :c .. :aa .. etc.
*
* @var string
*/
protected $paramBase = 'a';
/**
* Identifier (table, column, ...) escaping symbol. By SQL Standard it's double
* quote, but MySQL uses backtick.
*
* @var string
*/
protected $escape_char = '"';
/** @var string|null used for linking */
private $_paramBase;
/** @var array Populated with actual values by escapeParam() */
public $params = [];
/** @var Connection|null */
public $connection;
/** @var bool Wrap the expression in parentheses when consumed by another expression or not. */
public $wrapInParentheses = false;
/**
* Specifying options to constructors will override default
* attribute values of this class.
*
* If $properties is passed as string, then it's treated as template.
*
* @param string|array $properties
* @param array $arguments
*/
public function __construct($properties = [], $arguments = null)
{
// save template
if (is_string($properties)) {
$properties = ['template' => $properties];
} elseif (!is_array($properties)) {
throw (new Exception('Incorrect use of Expression constructor'))
->addMoreInfo('properties', $properties)
->addMoreInfo('arguments', $arguments);
}
// supports passing template as property value without key 'template'
if (isset($properties[0])) {
$properties['template'] = $properties[0];
unset($properties[0]);
}
// save arguments
if ($arguments !== null) {
if (!is_array($arguments)) {
throw (new Exception('Expression arguments must be an array'))
->addMoreInfo('properties', $properties)
->addMoreInfo('arguments', $arguments);
}
$this->args['custom'] = $arguments;
}
// deal with remaining properties
foreach ($properties as $key => $val) {
$this->{$key} = $val;
}
}
/**
* @deprecated will be removed in v2.5
*/
public function __toString()
{
'trigger_error'('Method is deprecated. Use $this->getOne() instead', \E_USER_DEPRECATED);
return $this->getOne();
}
/**
* @return $this
*/
public function getDsqlExpression(self $expression): self
{
return $this;
}
/**
* Whether or not an offset exists.
*
* @param int|string $offset
*/
public function offsetExists($offset): bool
{
return array_key_exists($offset, $this->args['custom']);
}
/**
* Returns the value at specified offset.
*
* @param int|string $offset
*
* @return mixed
*/
public function offsetGet($offset)
{
return $this->args['custom'][$offset];
}
/**
* Assigns a value to the specified offset.
*
* @param int|string|null $offset
* @param mixed $value The value to set
*/
public function offsetSet($offset, $value): void
{
if ($offset === null) {
$this->args['custom'][] = $value;
} else {
$this->args['custom'][$offset] = $value;
}
}
/**
* Unsets an offset.
*
* @param int|string $offset
*/
public function offsetUnset($offset): void
{
unset($this->args['custom'][$offset]);
}
/**
* Use this instead of "new Expression()" if you want to automatically bind
* new expression to the same connection as the parent.
*
* @param string|array $properties
* @param array $arguments
*
* @return Expression
*/
public function expr($properties = [], $arguments = null)
{
if ($this->connection !== null) {
// TODO - condition above always satisfied when connection is set - adjust tests,
// so connection is always set and remove the code below
return $this->connection->expr($properties, $arguments);
}
// make a smart guess :) when connection is not set
if ($this instanceof Query) {
$e = new self($properties, $arguments);
} else {
$e = new static($properties, $arguments);
}
$e->escape_char = $this->escape_char;
return $e;
}
/**
* Resets arguments.
*
* @return $this
*/
public function reset(string $tag = null)
{
// unset all arguments
if ($tag === null) {
$this->args = ['custom' => []];
return $this;
}
// unset custom/argument or argument if such exists
if ($this->offsetExists($tag)) {
$this->offsetUnset($tag);
} elseif (isset($this->args[$tag])) {
unset($this->args[$tag]);
}
return $this;
}
/**
* Recursively renders sub-query or expression, combining parameters.
*
* @param mixed $expression Expression
* @param string $escapeMode Fall-back escaping mode - using one of the Expression::ESCAPE_* constants
*
* @return string Quoted expression
*/
protected function consume($expression, string $escapeMode = self::ESCAPE_PARAM)
{
if (!is_object($expression)) {
switch ($escapeMode) {
case self::ESCAPE_PARAM:
return $this->escapeParam($expression);
case self::ESCAPE_IDENTIFIER:
return $this->escapeIdentifier($expression);
case self::ESCAPE_IDENTIFIER_SOFT:
return $this->escapeIdentifierSoft($expression);
case self::ESCAPE_NONE:
return $expression;
}
throw (new Exception('$escapeMode value is incorrect'))
->addMoreInfo('escapeMode', $escapeMode);
}
if ($expression instanceof Expressionable) {
$expression = $expression->getDsqlExpression($this);
}
if (!$expression instanceof self) {
throw (new Exception('Only Expressionable object type may be used in Expression'))
->addMoreInfo('object', $expression);
}
// at this point $sql_code is instance of Expression
$expression->params = $this->params;
$expression->_paramBase = $this->_paramBase;
try {
$ret = $expression->render();
$this->params = $expression->params;
$this->_paramBase = $expression->_paramBase;
} finally {
$expression->params = [];
$expression->_paramBase = null;
}
// Wrap in parentheses if expression requires so
if ($expression->wrapInParentheses === true) {
$ret = '(' . $ret . ')';
}
return $ret;
}
/**
* Creates new expression where $value appears escaped. Use this
* method as a conventional means of specifying arguments when you
* think they might have a nasty back-ticks or commas in the field
* names.
*
* @param string $value
*
* @return Expression
*/
public function escape($value)
{
return $this->expr('{}', [$value]);
}
/**
* Converts value into parameter and returns reference. Use only during
* query rendering. Consider using `consume()` instead, which will
* also handle nested expressions properly.
*
* @param string|int|float $value
*
* @return string Name of parameter
*/
protected function escapeParam($value): string
{
$name = ':' . $this->_paramBase;
++$this->_paramBase;
$this->params[$name] = $value;
return $name;
}
/**
* Escapes argument by adding backticks around it.
* This will allow you to use reserved SQL words as table or field
* names such as "table" as well as other characters that SQL
* permits in the identifiers (e.g. spaces or equation signs).
*/
protected function escapeIdentifier(string $value): string
{
// in all other cases we should escape
$c = $this->escape_char;
return $c . str_replace($c, $c . $c, $value) . $c;
}
/**
* Soft-escaping SQL identifier. This method will attempt to put
* escaping char around the identifier, however will not do so if you
* are using special characters like ".", "(" or escaping char.
*
* It will smartly escape table.field type of strings resulting
* in "table"."field".
*/
protected function escapeIdentifierSoft(string $value): string
{
// in some cases we should not escape
if ($this->isUnescapablePattern($value)) {
return $value;
}
if (strpos($value, '.') !== false) {
return implode('.', array_map(__METHOD__, explode('.', $value)));
}
return $this->escape_char . trim($value) . $this->escape_char;
}
/**
* Given the string parameter, it will detect some "deal-breaker" for our
* soft escaping, such as "*" or "(".
* Those will typically indicate that expression is passed and shouldn't
* be escaped.
*
* @param self|string $value
*/
protected function isUnescapablePattern($value): bool
{
return is_object($value)
|| $value === '*'
|| strpos($value, '(') !== false
|| strpos($value, $this->escape_char) !== false;
}
/**
* Render expression and return it as string.
*/
public function render(): string
{
$hadUnderscoreParamBase = $this->_paramBase !== null;
if (!$hadUnderscoreParamBase) {
$hadUnderscoreParamBase = false;
$this->_paramBase = $this->paramBase;
}
if ($this->template === null) {
throw new Exception('Template is not defined for Expression');
}
$nameless_count = 0;
// - [xxx] = param
// - {xxx} = escape
// - {{xxx}} = escapeSoft
$res = preg_replace_callback(
<<<'EOF'
~
'(?:[^'\\]+|\\.|'')*'\K
|"(?:[^"\\]+|\\.|"")*"\K
|`(?:[^`\\]+|\\.|``)*`\K
|\[\w*\]
|\{\w*\}
|\{\{\w*\}\}
~xs
EOF,
function ($matches) use (&$nameless_count) {
if ($matches[0] === '') {
return '';
}
$identifier = substr($matches[0], 1, -1);
$escaping = null;
if (substr($matches[0], 0, 1) === '[') {
$escaping = self::ESCAPE_PARAM;
} elseif (substr($matches[0], 0, 1) === '{') {
if (substr($matches[0], 1, 1) === '{') {
$escaping = self::ESCAPE_IDENTIFIER_SOFT;
$identifier = substr($identifier, 1, -1);
} else {
$escaping = self::ESCAPE_IDENTIFIER;
}
}
// allow template to contain []
if ($identifier === '') {
$identifier = $nameless_count++;
// use rendering only with named tags
}
$fx = '_render_' . $identifier;
if (array_key_exists($identifier, $this->args['custom'])) {
$value = $this->consume($this->args['custom'][$identifier], $escaping);
} elseif (method_exists($this, $fx)) {
$value = $this->{$fx}();
} else {
throw (new Exception('Expression could not render tag'))
->addMoreInfo('tag', $identifier);
}
return $value;
},
$this->template
);
if (!$hadUnderscoreParamBase) {
$this->_paramBase = null;
}
return trim($res);
}
/**
* Return formatted debug SQL query.
*/
public function getDebugQuery(): string
{
$result = $this->render();
foreach (array_reverse($this->params) as $key => $val) {
if (is_int($key)) {
continue;
}
if ($val === null) {
$replacement = 'NULL';
} elseif (is_bool($val)) {
$replacement = $val ? '1' : '0';
} elseif (is_int($val) || is_float($val)) {
$replacement = (string) $val;
} elseif (is_string($val)) {
$replacement = '\'' . addslashes($val) . '\'';
} else {
continue;
}
$result = preg_replace('~' . $key . '(?!\w)~', $replacement, $result);
}
if (class_exists('SqlFormatter')) { // requires optional "jdorn/sql-formatter" package
$result = \SqlFormatter::format($result, false);
}
return $result;
}
public function __debugInfo(): array
{
$arr = [
'R' => false,
'template' => $this->template,
'params' => $this->params,
// 'connection' => $this->connection,
'args' => $this->args,
];
try {
$arr['R'] = $this->getDebugQuery();
} catch (\Exception $e) {
$arr['R'] = $e->getMessage();
}
return $arr;
}
/**
* @param DbalConnection|Connection $connection
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function execute(object $connection = null): object
{
if ($connection === null) {
$connection = $this->connection;
}
// If it's a DBAL connection, we're cool
if ($connection instanceof DbalConnection) {
$query = $this->render();
try {
$statement = $connection->prepare($query);
foreach ($this->params as $key => $val) {
if (is_int($val)) {
$type = \PDO::PARAM_INT;
} elseif (is_bool($val)) {
if ($this->connection->getDatabasePlatform() instanceof PostgreSQL94Platform) {
$type = \PDO::PARAM_STR;
$val = $val ? '1' : '0';
} else {
$type = \PDO::PARAM_INT;
$val = $val ? 1 : 0;
}
} elseif ($val === null) {
$type = \PDO::PARAM_NULL;
} elseif (is_string($val) || is_float($val)) {
$type = \PDO::PARAM_STR;
} elseif (is_resource($val)) {
throw new Exception('Resource type is not supported, set value as string instead');
} else {
throw (new Exception('Incorrect param type'))
->addMoreInfo('key', $key)
->addMoreInfo('value', $val)
->addMoreInfo('type', gettype($val));
}
$bind = $statement->bindValue($key, $val, $type);
if ($bind === false) {
throw (new Exception('Unable to bind parameter'))
->addMoreInfo('param', $key)
->addMoreInfo('value', $val)
->addMoreInfo('type', $type);
}
}
$result = $statement->execute();
if (Connection::isComposerDbal2x()) {
return $statement; // @phpstan-ignore-line
}
return $result;
} catch (DbalException | \Doctrine\DBAL\DBALException $e) { // @phpstan-ignore-line for DBAL 2.x
$firstException = $e;
while ($firstException->getPrevious() !== null) {
$firstException = $firstException->getPrevious();
}
$errorInfo = $firstException instanceof \PDOException ? $firstException->errorInfo : null;
$new = (new ExecuteException('Dsql execute error', $errorInfo[1] ?? 0, $e))
->addMoreInfo('error', $errorInfo[2] ?? 'n/a (' . $errorInfo[0] . ')')
->addMoreInfo('query', $this->getDebugQuery());
throw $new;
}
}
return $connection->execute($this);
}
/**
* TODO drop method once we support DBAL 3.x only.
*
* @return \Traversable<array<mixed>>
*/
public function getIterator(): \Traversable
{
if (Connection::isComposerDbal2x()) {
return $this->execute();
}
return $this->execute()->iterateAssociative();
}
// {{{ Result Querying
/**
* @param string|int|float|bool|null $v
*/
private function getCastValue($v): ?string
{
if (is_int($v) || is_float($v)) {
return (string) $v;
} elseif (is_bool($v)) {
return $v ? '1' : '0';
}
// for Oracle CLOB/BLOB datatypes and PDO driver
if (is_resource($v) && get_resource_type($v) === 'stream'
&& $this->connection->getDatabasePlatform() instanceof \Doctrine\DBAL\Platforms\OraclePlatform) {
$v = stream_get_contents($v);
}
return $v; // throw a type error if not null nor string
}
/**
* @deprecated use "getRows" method instead - will be removed in v2.5
*/
public function get(): array
{
'trigger_error'('Method is deprecated. Use getRows instead', \E_USER_DEPRECATED);
return $this->getRows();
}
/**
* Executes expression and return whole result-set in form of array of hashes.
*
* @return string[][]|null[][]
*/
public function getRows(): array
{
if (Connection::isComposerDbal2x()) {
$rows = $this->execute()->fetchAll();
} else {
$rows = $this->execute()->fetchAllAssociative();
}
return array_map(function ($row) {
return array_map(function ($v) {
return $this->getCastValue($v);
}, $row);
}, $rows);
}
/**
* Executes expression and returns first row of data from result-set as a hash.
*
* @return string[]|null[]|null
*/
public function getRow(): ?array
{
if (Connection::isComposerDbal2x()) {
$row = $this->execute()->fetch();
} else {
$row = $this->execute()->fetchAssociative();
}
if ($row === false) {
return null;
}
return array_map(function ($v) {
return $this->getCastValue($v);
}, $row);
}
/**
* Executes expression and return first value of first row of data from result-set.
*/
public function getOne(): ?string
{
$row = $this->getRow();
if ($row === null || count($row) === 0) {
throw (new Exception('Unable to fetch single cell of data for getOne from this query'))
->addMoreInfo('result', $row)
->addMoreInfo('query', $this->getDebugQuery());
}
return reset($row);
}
// }}}
}