diff options
Diffstat (limited to 'vendor/ipl/sql')
35 files changed, 4040 insertions, 0 deletions
diff --git a/vendor/ipl/sql/LICENSE b/vendor/ipl/sql/LICENSE new file mode 100644 index 0000000..e179593 --- /dev/null +++ b/vendor/ipl/sql/LICENSE @@ -0,0 +1,21 @@ +The MIT License + +Copyright (c) 2017 Icinga GmbH https://www.icinga.com + +Permission is hereby granted, free of charge, to any person obtaining a copy +of this software and associated documentation files (the "Software"), to deal +in the Software without restriction, including without limitation the rights +to use, copy, modify, merge, publish, distribute, sublicense, and/or sell +copies of the Software, and to permit persons to whom the Software is +furnished to do so, subject to the following conditions: + +The above copyright notice and this permission notice shall be included in +all copies or substantial portions of the Software. + +THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR +IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, +FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE +AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER +LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, +OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN +THE SOFTWARE. diff --git a/vendor/ipl/sql/composer.json b/vendor/ipl/sql/composer.json new file mode 100644 index 0000000..5c662b1 --- /dev/null +++ b/vendor/ipl/sql/composer.json @@ -0,0 +1,29 @@ +{ + "name": "ipl/sql", + "type": "library", + "description": "Icinga PHP Library - SQL abstraction layer", + "keywords": ["sql", "database"], + "homepage": "https://github.com/Icinga/ipl-sql", + "license": "MIT", + "require": { + "php": ">=7.2", + "ext-pdo": "*", + "ipl/stdlib": ">=0.12.0" + }, + "require-dev": { + "ipl/stdlib": "dev-main" + }, + "autoload": { + "psr-4": { + "ipl\\Sql\\": "src" + } + }, + "autoload-dev": { + "psr-4": { + "ipl\\Tests\\Sql\\": "tests" + } + }, + "scripts": { + "test": "vendor/bin/phpunit" + } +} diff --git a/vendor/ipl/sql/src/Adapter/BaseAdapter.php b/vendor/ipl/sql/src/Adapter/BaseAdapter.php new file mode 100644 index 0000000..f062f63 --- /dev/null +++ b/vendor/ipl/sql/src/Adapter/BaseAdapter.php @@ -0,0 +1,120 @@ +<?php + +namespace ipl\Sql\Adapter; + +use DateTime; +use DateTimeZone; +use ipl\Sql\Config; +use ipl\Sql\Connection; +use ipl\Sql\Contract\Adapter; +use ipl\Sql\QueryBuilder; +use ipl\Sql\Select; +use PDO; +use UnexpectedValueException; + +abstract class BaseAdapter implements Adapter +{ + /** + * Quote character to use for quoting identifiers + * + * The default quote character is the double quote (") which is used by databases that behave close to ANSI SQL. + * + * @var array + */ + protected $quoteCharacter = ['"', '"']; + + /** @var string Character to use for escaping quote characters */ + protected $escapeCharacter = '\\"'; + + /** @var array Default PDO connect options */ + protected $options = [ + PDO::ATTR_CASE => PDO::CASE_NATURAL, + PDO::ATTR_EMULATE_PREPARES => false, + PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, + PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL, + PDO::ATTR_STRINGIFY_FETCHES => false + ]; + + public function getDsn(Config $config) + { + $dsn = "{$config->db}:"; + + $parts = []; + + foreach (['host', 'dbname', 'port'] as $part) { + if (! empty($config->$part)) { + $parts[] = "{$part}={$config->$part}"; + } + } + + return $dsn . implode(';', $parts); + } + + public function getOptions(Config $config) + { + if (is_array($config->options)) { + return $config->options + $this->options; + } + + return $this->options; + } + + public function setClientTimezone(Connection $db) + { + return $this; + } + + public function quoteIdentifier($identifiers) + { + if (is_string($identifiers)) { + $identifiers = explode('.', $identifiers); + } + + foreach ($identifiers as $i => $identifier) { + if ($identifier === '*') { + continue; + } + + $identifiers[$i] = $this->quoteCharacter[0] + . str_replace($this->quoteCharacter[0], $this->escapeCharacter, $identifier) + . $this->quoteCharacter[1]; + } + + return implode('.', $identifiers); + } + + public function registerQueryBuilderCallbacks(QueryBuilder $queryBuilder) + { + $queryBuilder->on(QueryBuilder::ON_ASSEMBLE_SELECT, function (Select $select): void { + if ($select->hasOrderBy()) { + foreach ($select->getOrderBy() as list($_, $direction)) { + switch (strtolower($direction ?? '')) { + case '': + case 'asc': + case 'desc': + break; + default: + throw new UnexpectedValueException( + sprintf('Invalid direction "%s" in ORDER BY', $direction) + ); + } + } + } + }); + + return $this; + } + + protected function getTimezoneOffset() + { + $tz = new DateTimeZone(date_default_timezone_get()); + $offset = $tz->getOffset(new DateTime()); + $prefix = $offset >= 0 ? '+' : '-'; + $offset = abs($offset); + + $hours = (int) floor($offset / 3600); + $minutes = (int) floor(($offset % 3600) / 60); + + return sprintf('%s%d:%02d', $prefix, $hours, $minutes); + } +} diff --git a/vendor/ipl/sql/src/Adapter/Mssql.php b/vendor/ipl/sql/src/Adapter/Mssql.php new file mode 100644 index 0000000..c9f11ce --- /dev/null +++ b/vendor/ipl/sql/src/Adapter/Mssql.php @@ -0,0 +1,80 @@ +<?php + +namespace ipl\Sql\Adapter; + +use ipl\Sql\Config; +use ipl\Sql\QueryBuilder; +use ipl\Sql\Select; +use PDO; +use RuntimeException; + +class Mssql extends BaseAdapter +{ + protected $quoteCharacter = ['[', ']']; + + protected $escapeCharacter = '[[]'; + + public function getDsn(Config $config) + { + $drivers = array_intersect(['sqlsrv', 'dblib', 'mssql', 'sybase'], PDO::getAvailableDrivers()); + + if (empty($drivers)) { + throw new RuntimeException('No PDO driver available for connecting to a Microsoft SQL Server'); + } + + $driver = reset($drivers); // array_intersect preserves keys, so the first may not be indexed at 0 + + $isSqlSrv = $driver === 'sqlsrv'; + if ($isSqlSrv) { + $hostOption = 'Server'; + $dbOption = 'Database'; + } else { + $hostOption = 'host'; + $dbOption = 'dbname'; + } + + $dsn = "{$driver}:{$hostOption}={$config->host}"; + + if (! empty($config->port)) { + if ($isSqlSrv || strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') { + $seperator = ','; + } else { + $seperator = ':'; + } + + $dsn .= "{$seperator}{$config->port}"; + } + + $dsn .= ";{$dbOption}={$config->dbname}"; + + if (! empty($config->charset) && ! $isSqlSrv) { + $dsn .= ";charset={$config->charset}"; + } + + if (isset($config->useSsl) && $isSqlSrv) { + $dsn .= ';Encrypt=' . ($config->useSsl ? 'true' : 'false'); + } + + if (isset($config->sslDoNotVerifyServerCert) && $isSqlSrv) { + $dsn .= ';TrustServerCertificate=' . ($config->sslDoNotVerifyServerCert ? 'true' : 'false'); + } + + return $dsn; + } + + public function registerQueryBuilderCallbacks(QueryBuilder $queryBuilder) + { + parent::registerQueryBuilderCallbacks($queryBuilder); + + $queryBuilder->on(QueryBuilder::ON_ASSEMBLE_SELECT, function (Select $select) { + if ( + ($select->hasLimit() || $select->hasOffset()) + && ! $select->hasOrderBy() + ) { + $select->orderBy(1); + } + }); + + return $this; + } +} diff --git a/vendor/ipl/sql/src/Adapter/Mysql.php b/vendor/ipl/sql/src/Adapter/Mysql.php new file mode 100644 index 0000000..2421cae --- /dev/null +++ b/vendor/ipl/sql/src/Adapter/Mysql.php @@ -0,0 +1,57 @@ +<?php + +namespace ipl\Sql\Adapter; + +use ipl\Sql\Config; +use ipl\Sql\Connection; +use PDO; + +class Mysql extends BaseAdapter +{ + protected $quoteCharacter = ['`', '`']; + + protected $escapeCharacter = '``'; + + public function setClientTimezone(Connection $db) + { + $db->exec('SET time_zone = ' . $db->quote($this->getTimezoneOffset())); + + return $this; + } + + public function getOptions(Config $config) + { + $options = parent::getOptions($config); + + if (! empty($config->useSsl)) { + if (! empty($config->sslKey)) { + $options[PDO::MYSQL_ATTR_SSL_KEY] = $config->sslKey; + } + + if (! empty($config->sslCert)) { + $options[PDO::MYSQL_ATTR_SSL_CERT] = $config->sslCert; + } + + if (! empty($config->sslCa)) { + $options[PDO::MYSQL_ATTR_SSL_CA] = $config->sslCa; + } + + if (! empty($config->sslCapath)) { + $options[PDO::MYSQL_ATTR_SSL_CAPATH] = $config->sslCapath; + } + + if (! empty($config->sslCipher)) { + $options[PDO::MYSQL_ATTR_SSL_CIPHER] = $config->sslCipher; + } + + if ( + defined('PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT') + && ! empty($config->sslDoNotVerifyServerCert) + ) { + $options[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false; + } + } + + return $options; + } +} diff --git a/vendor/ipl/sql/src/Adapter/Oracle.php b/vendor/ipl/sql/src/Adapter/Oracle.php new file mode 100644 index 0000000..de0aee5 --- /dev/null +++ b/vendor/ipl/sql/src/Adapter/Oracle.php @@ -0,0 +1,39 @@ +<?php + +namespace ipl\Sql\Adapter; + +use ipl\Sql\Config; +use ipl\Sql\Connection; + +class Oracle extends BaseAdapter +{ + public function getDsn(Config $config) + { + $dsn = 'oci:dbname='; + + if (! empty($config->host)) { + $dsn .= "//{$config->host}"; + + if (! empty($config->port)) { + $dsn .= ":{$config->port}/"; + } + + $dsn .= '/'; + } + + $dsn .= $config->dbname; + + if (! empty($config->charset)) { + $dsn .= ";charset={$config->charset}"; + } + + return $dsn; + } + + public function setClientTimezone(Connection $db) + { + $db->prepexec('ALTER SESSION SET TIME_ZONE = ?', [$this->getTimezoneOffset()]); + + return $this; + } +} diff --git a/vendor/ipl/sql/src/Adapter/Pgsql.php b/vendor/ipl/sql/src/Adapter/Pgsql.php new file mode 100644 index 0000000..18bf15d --- /dev/null +++ b/vendor/ipl/sql/src/Adapter/Pgsql.php @@ -0,0 +1,15 @@ +<?php + +namespace ipl\Sql\Adapter; + +use ipl\Sql\Connection; + +class Pgsql extends BaseAdapter +{ + public function setClientTimezone(Connection $db) + { + $db->exec(sprintf('SET TIME ZONE INTERVAL %s HOUR TO MINUTE', $db->quote($this->getTimezoneOffset()))); + + return $this; + } +} diff --git a/vendor/ipl/sql/src/Adapter/Sqlite.php b/vendor/ipl/sql/src/Adapter/Sqlite.php new file mode 100644 index 0000000..9f4e209 --- /dev/null +++ b/vendor/ipl/sql/src/Adapter/Sqlite.php @@ -0,0 +1,13 @@ +<?php + +namespace ipl\Sql\Adapter; + +use ipl\Sql\Config; + +class Sqlite extends BaseAdapter +{ + public function getDsn(Config $config) + { + return "sqlite:{$config->dbname}"; + } +} diff --git a/vendor/ipl/sql/src/CommonTableExpression.php b/vendor/ipl/sql/src/CommonTableExpression.php new file mode 100644 index 0000000..596ec39 --- /dev/null +++ b/vendor/ipl/sql/src/CommonTableExpression.php @@ -0,0 +1,53 @@ +<?php + +namespace ipl\Sql; + +/** + * Implementation for the {@link CommonTableExpressionInterface} to allow CTEs via {@link with()} + */ +trait CommonTableExpression +{ + /** + * All CTEs + * + * [ + * [$query, $alias, $recursive], + * ... + * ] + * + * @var array[] + */ + protected $with = []; + + public function getWith() + { + return $this->with; + } + + public function with(Select $query, $alias, $recursive = false) + { + $this->with[] = [$query, $alias, $recursive]; + + return $this; + } + + public function resetWith() + { + $this->with = []; + + return $this; + } + + /** + * Clone the properties provided by this trait + * + * Shall be called by using classes in their __clone() + */ + protected function cloneCte() + { + foreach ($this->with as &$cte) { + $cte[0] = clone $cte[0]; + } + unset($cte); + } +} diff --git a/vendor/ipl/sql/src/CommonTableExpressionInterface.php b/vendor/ipl/sql/src/CommonTableExpressionInterface.php new file mode 100644 index 0000000..7e93bc8 --- /dev/null +++ b/vendor/ipl/sql/src/CommonTableExpressionInterface.php @@ -0,0 +1,39 @@ +<?php + +namespace ipl\Sql; + +/** + * Interface for CTEs via {@link with()} + */ +interface CommonTableExpressionInterface +{ + /** + * Get all CTEs + * + * [ + * [$query, $alias, $columns, $recursive], + * ... + * ] + * + * @return array[] + */ + public function getWith(); + + /** + * Add a CTE + * + * @param Select $query + * @param string $alias + * @param bool $recursive + * + * @return $this + */ + public function with(Select $query, $alias, $recursive = false); + + /** + * Reset all CTEs + * + * @return $this + */ + public function resetWith(); +} diff --git a/vendor/ipl/sql/src/Compat/FilterProcessor.php b/vendor/ipl/sql/src/Compat/FilterProcessor.php new file mode 100644 index 0000000..6835e25 --- /dev/null +++ b/vendor/ipl/sql/src/Compat/FilterProcessor.php @@ -0,0 +1,127 @@ +<?php + +namespace ipl\Sql\Compat; + +use InvalidArgumentException; +use ipl\Sql\Filter\Exists; +use ipl\Sql\Filter\In; +use ipl\Sql\Filter\NotExists; +use ipl\Sql\Filter\NotIn; +use ipl\Sql\Select; +use ipl\Sql\Sql; +use ipl\Stdlib\Filter; + +class FilterProcessor +{ + public static function assembleFilter(Filter\Rule $filter, $level = 0) + { + $condition = null; + + if ($filter instanceof Filter\Chain) { + if ($filter instanceof Filter\All) { + $operator = Sql::ALL; + } elseif ($filter instanceof Filter\Any) { + $operator = Sql::ANY; + } elseif ($filter instanceof Filter\None) { + $operator = Sql::NOT_ALL; + } + + if (! isset($operator)) { + throw new InvalidArgumentException(sprintf('Cannot render filter: %s', get_class($filter))); + } + + if (! $filter->isEmpty()) { + foreach ($filter as $filterPart) { + $part = static::assembleFilter($filterPart, $level + 1); + if ($part) { + if ($condition === null) { + $condition = [$operator, [$part]]; + } else { + if ($condition[0] === $operator) { + $condition[1][] = $part; + } elseif ($operator === Sql::NOT_ALL) { + $condition = [Sql::ALL, [$condition, [$operator, [$part]]]]; + } elseif ($operator === Sql::NOT_ANY) { + $condition = [Sql::ANY, [$condition, [$operator, [$part]]]]; + } else { + $condition = [$operator, [$condition, $part]]; + } + } + } + } + } else { + // TODO(el): Explicitly return the empty string due to the FilterNot case? + } + } else { + /** @var Filter\Condition $filter */ + $condition = [Sql::ALL, static::assemblePredicate($filter)]; + } + + return $condition; + } + + public static function assemblePredicate(Filter\Condition $filter) + { + $column = $filter->getColumn(); + $expression = $filter->getValue(); + + if (is_array($expression) || $expression instanceof Select) { + $nullVerification = true; + if (is_array($column)) { + if (count($column) === 1) { + $column = $column[0]; + } else { + $nullVerification = false; + $column = '( ' . implode(', ', $column) . ' )'; + } + } + + if ($filter instanceof Filter\Unequal || $filter instanceof NotIn) { + return [sprintf($nullVerification + ? '(%s NOT IN (?) OR %1$s IS NULL)' + : '%s NOT IN (?)', $column) => $expression]; + } elseif ($filter instanceof Filter\Equal || $filter instanceof In) { + return ["$column IN (?)" => $expression]; + } + + throw new InvalidArgumentException( + 'Unable to render array expressions with operators other than equal/in or not equal/not in' + ); + } elseif ( + ($filter instanceof Filter\Like || $filter instanceof Filter\Unlike) + && strpos($expression, '*') !== false + ) { + if ($expression === '*') { + return ["$column IS " . ($filter instanceof Filter\Like ? 'NOT ' : '') . 'NULL']; + } elseif ($filter instanceof Filter\Unlike) { + return [ + "($column NOT LIKE ? OR $column IS NULL)" => str_replace(['%', '*'], ['\\%', '%'], $expression) + ]; + } else { + return ["$column LIKE ?" => str_replace(['%', '*'], ['\\%', '%'], $expression)]; + } + } elseif ($filter instanceof Filter\Unequal || $filter instanceof Filter\Unlike) { + return ["($column != ? OR $column IS NULL)" => $expression]; + } else { + if ($filter instanceof Filter\Like || $filter instanceof Filter\Equal) { + $operator = '='; + } elseif ($filter instanceof Filter\GreaterThan) { + $operator = '>'; + } elseif ($filter instanceof Filter\GreaterThanOrEqual) { + $operator = '>='; + } elseif ($filter instanceof Filter\LessThan) { + $operator = '<'; + } elseif ($filter instanceof Filter\LessThanOrEqual) { + $operator = '<='; + } elseif ($filter instanceof Exists) { + $operator = 'EXISTS'; + } elseif ($filter instanceof NotExists) { + $operator = 'NOT EXISTS'; + } else { + throw new InvalidArgumentException(sprintf('Cannot render filter: %s', get_class($filter))); + } + + return ["$column $operator ?" => $expression]; + } + } +} diff --git a/vendor/ipl/sql/src/Config.php b/vendor/ipl/sql/src/Config.php new file mode 100644 index 0000000..5fa103e --- /dev/null +++ b/vendor/ipl/sql/src/Config.php @@ -0,0 +1,99 @@ +<?php + +namespace ipl\Sql; + +use InvalidArgumentException; +use ipl\Stdlib\Str; +use OutOfRangeException; + +use function ipl\Stdlib\get_php_type; + +/** + * SQL connection configuration + */ +class Config +{ + /** @var string Type of the DBMS */ + public $db; + + /** @var string Database host */ + public $host; + + /** @var int Database port */ + public $port; + + /** @var string Database name */ + public $dbname; + + /** @var string Username to use for authentication */ + public $username; + + /** @var string Password to use for authentication */ + public $password; + + /** + * Character set for the connection + * + * If you want to use the default charset as configured by the database, don't set this property. + * + * @var string + */ + public $charset; + + /** + * PDO connect options + * + * Array of key-value pairs that should be set when calling {@link Connection::connect()} in order to establish a DB + * connection. + * + * @var array + */ + public $options; + + /** @var array Extra settings e.g. for SQL SSL connections */ + protected $extraSettings = []; + + /** + * Create a new SQL connection configuration from the given configuration key-value pairs + * + * Keys will be converted to camelCase, e.g. use_ssl → useSsl. + * + * @param iterable $config Configuration key-value pairs + * + * @throws InvalidArgumentException If $config is not iterable + */ + public function __construct($config) + { + if (! is_iterable($config)) { + throw new InvalidArgumentException(sprintf( + '%s expects parameter one to be iterable, got %s instead', + __METHOD__, + get_php_type($config) + )); + } + + foreach ($config as $key => $value) { + $key = Str::camel($key); + $this->$key = $value; + } + } + + public function __isset(string $name): bool + { + return isset($this->extraSettings[$name]); + } + + public function __get(string $name) + { + if (array_key_exists($name, $this->extraSettings)) { + return $this->extraSettings[$name]; + } + + throw new OutOfRangeException(sprintf('Property %s does not exist', $name)); + } + + public function __set(string $name, $value): void + { + $this->extraSettings[$name] = $value; + } +} diff --git a/vendor/ipl/sql/src/Connection.php b/vendor/ipl/sql/src/Connection.php new file mode 100644 index 0000000..de84c72 --- /dev/null +++ b/vendor/ipl/sql/src/Connection.php @@ -0,0 +1,554 @@ +<?php + +namespace ipl\Sql; + +use BadMethodCallException; +use Exception; +use InvalidArgumentException; +use ipl\Sql\Contract\Adapter; +use ipl\Sql\Contract\Quoter; +use ipl\Stdlib\Plugins; +use PDO; +use PDOStatement; + +/** + * Connection to a SQL database using the native PDO for database access + */ +class Connection implements Quoter +{ + use Plugins; + + /** @var Config */ + protected $config; + + /** @var ?PDO */ + protected $pdo; + + /** @var QueryBuilder */ + protected $queryBuilder; + + /** @var Adapter */ + protected $adapter; + + /** + * Create a new database connection using the given config for initialising the options for the connection + * + * {@link init()} is called after construction. + * + * @param Config|iterable $config + * + * @throws InvalidArgumentException If there's no adapter for the given database available + */ + public function __construct($config) + { + $config = $config instanceof Config ? $config : new Config($config); + + $this->addPluginLoader('adapter', __NAMESPACE__ . '\\Adapter'); + + $adapter = $this->loadPlugin('adapter', $config->db); + + if (! $adapter) { + throw new InvalidArgumentException("Can't load database adapter for '{$config->db}'."); + } + + $this->adapter = new $adapter(); + $this->config = $config; + + $this->init(); + } + + /** + * Proxy PDO method calls + * + * @param string $name The name of the PDO method to call + * @param array $arguments Arguments for the method to call + * + * @return mixed + * + * @throws BadMethodCallException If the called method does not exist + * + */ + public function __call($name, array $arguments) + { + $this->connect(); + + if (! method_exists($this->pdo, $name)) { + $class = get_class($this); + $message = "Call to undefined method $class::$name"; + + throw new BadMethodCallException($message); + } + + return call_user_func_array([$this->pdo, $name], $arguments); + } + + /** + * Initialise the database connection + * + * If you have to adjust the connection after construction, override this method. + */ + public function init() + { + } + + /** + * Get the database adapter + * + * @return Adapter + */ + public function getAdapter() + { + return $this->adapter; + } + + /** + * Get the connection configuration + * + * @return Config + */ + public function getConfig() + { + return $this->config; + } + + /** + * Get the query builder for the database connection + * + * @return QueryBuilder + */ + public function getQueryBuilder() + { + if ($this->queryBuilder === null) { + $this->queryBuilder = new QueryBuilder($this->adapter); + } + + return $this->queryBuilder; + } + + /** + * Create and return the PDO instance + * + * This method is called via {@link connect()} to establish a database connection. + * If the default PDO needs to be adjusted for a certain DBMS, override this method. + * + * @return PDO + */ + protected function createPdoAdapter() + { + $adapter = $this->getAdapter(); + + $config = $this->getConfig(); + + return new PDO( + $adapter->getDsn($config), + $config->username, + $config->password, + $adapter->getOptions($config) + ); + } + + /** + * Connect to the database, if not already connected + * + * @return $this + */ + public function connect() + { + if ($this->pdo !== null) { + return $this; + } + + $this->pdo = $this->createPdoAdapter(); + + if (! empty($this->config->charset)) { + $this->exec(sprintf('SET NAMES %s', $this->pdo->quote($this->config->charset))); + } + + $this->adapter->setClientTimezone($this); + + return $this; + } + + /** + * Disconnect from the database + * + * @return $this + */ + public function disconnect() + { + $this->pdo = null; + + return $this; + } + + /** + * Check whether the connection to the database is still available + * + * @param bool $reconnect Whether to automatically reconnect + * + * @return bool + */ + public function ping($reconnect = true) + { + try { + $this->query('SELECT 1')->closeCursor(); + } catch (Exception $e) { + if (! $reconnect) { + return false; + } + + $this->disconnect(); + + return $this->ping(false); + } + + return true; + } + + /** + * Fetch and return all result rows as sequential array + * + * @param Select|string $stmt The SQL statement to prepare and execute. + * @param array $values Values to bind to the statement + * + * @return array + */ + public function fetchAll($stmt, array $values = null) + { + return $this->prepexec($stmt, $values) + ->fetchAll(); + } + + /** + * Fetch and return the first column of all result rows as sequential array + * + * @param Select|string $stmt The SQL statement to prepare and execute. + * @param array $values Values to bind to the statement + * + * @return array + */ + public function fetchCol($stmt, array $values = null) + { + return $this->prepexec($stmt, $values) + ->fetchAll(PDO::FETCH_COLUMN, 0); + } + + /** + * Fetch and return the first row of the result rows + * + * @param Select|string $stmt The SQL statement to prepare and execute. + * @param array $values Values to bind to the statement + * + * @return array + */ + public function fetchOne($stmt, array $values = null) + { + return $this->prepexec($stmt, $values) + ->fetch(); + } + + /** + * Alias of {@link fetchOne()} + */ + public function fetchRow($stmt, array $values = null) + { + return $this->prepexec($stmt, $values) + ->fetch(); + } + + /** + * Fetch and return all result rows as an array of key-value pairs + * + * First column is the key and the second column is the value. + * + * @param Select|string $stmt The SQL statement to prepare and execute. + * @param array $values Values to bind to the statement + * + * @return array + */ + public function fetchPairs($stmt, array $values = null) + { + return $this->prepexec($stmt, $values) + ->fetchAll(PDO::FETCH_KEY_PAIR); + } + + /** + * Fetch and return the first column of the first result row + * + * @param Select|string $stmt The SQL statement to prepare and execute. + * @param array $values Values to bind to the statement + * + * @return string + */ + public function fetchScalar($stmt, array $values = null) + { + return $this->prepexec($stmt, $values) + ->fetchColumn(0); + } + + /** + * Yield each result row + * + * `Connection::yieldAll(Select|string $stmt [[, array $values], int $fetchMode [, mixed ...$fetchModeOptions]])` + * + * @param Select|string $stmt The SQL statement to prepare and execute. + * @param mixed ...$args Values to bind to the statement, fetch mode for the statement, fetch mode options + * + * @return \Generator + */ + public function yieldAll($stmt, ...$args) + { + $values = null; + + if (! empty($args)) { + if (is_array($args[0])) { + $values = array_shift($args); + } + } + + $fetchMode = null; + + if (! empty($args)) { + $fetchMode = array_shift($args); + + switch ($fetchMode) { + case PDO::FETCH_KEY_PAIR: + foreach ($this->yieldPairs($stmt, $values) as $key => $value) { + yield $key => $value; + } + + return; + case PDO::FETCH_COLUMN: + if (empty($args)) { + $args[] = 0; + } + + break; + } + } + + $sth = $this->prepexec($stmt, $values); + + if ($fetchMode !== null) { + $sth->setFetchMode($fetchMode, ...$args); + } + + foreach ($sth as $key => $row) { + yield $key => $row; + } + } + + /** + * Yield the first column of each result row + * + * @param Select|string $stmt The SQL statement to prepare and execute + * @param array $values Values to bind to the statement + * + * @return \Generator + */ + public function yieldCol($stmt, array $values = null) + { + $sth = $this->prepexec($stmt, $values); + + $sth->setFetchMode(PDO::FETCH_COLUMN, 0); + + foreach ($sth as $key => $row) { + yield $key => $row; + } + } + + /** + * Yield key-value pairs with the first column as key and the second column as value for each result row + * + * @param Select|string $stmt The SQL statement to prepare and execute + * @param array $values Values to bind to the statement + * + * @return \Generator + */ + public function yieldPairs($stmt, array $values = null) + { + $sth = $this->prepexec($stmt, $values); + + $sth->setFetchMode(PDO::FETCH_NUM); + + foreach ($sth as $row) { + list($key, $value) = $row; + + yield $key => $value; + } + } + + /** + * Prepare and execute the given statement + * + * @param Delete|Insert|Select|Update|string $stmt The SQL statement to prepare and execute + * @param string|array $values Values to bind to the statement, if any + * + * @return PDOStatement + */ + public function prepexec($stmt, $values = null) + { + if ($values !== null && ! is_array($values)) { + $values = [$values]; + } + + if (is_object($stmt)) { + list($stmt, $values) = $this->getQueryBuilder()->assemble($stmt); + } + + $this->connect(); + + $sth = $this->pdo->prepare($stmt); + $sth->execute($values); + + return $sth; + } + + /** + * Prepare and execute the given Select query + * + * @param Select $select + * + * @return PDOStatement + */ + public function select(Select $select) + { + list($stmt, $values) = $this->getQueryBuilder()->assembleSelect($select); + + return $this->prepexec($stmt, $values); + } + + /** + * Insert a table row with the specified data + * + * @param string $table The table to insert data into. The table specification must be in + * one of the following formats: 'table' or 'schema.table' + * @param iterable $data Row data in terms of column-value pairs + * + * @return PDOStatement + * + * @throws InvalidArgumentException If data type is invalid + */ + public function insert($table, $data) + { + $insert = (new Insert()) + ->into($table) + ->values($data); + + return $this->prepexec($insert); + } + + /** + * Update table rows with the specified data, optionally based on a given condition + * + * @param string|array $table The table to update. The table specification must be in one of + * the following formats: + * 'table', 'table alias', ['alias' => 'table'] + * @param iterable $data The columns to update in terms of column-value pairs + * @param mixed $condition The WHERE condition + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return PDOStatement + * + * @throws InvalidArgumentException If data type is invalid + */ + public function update($table, $data, $condition = null, $operator = Sql::ALL) + { + $update = (new Update()) + ->table($table) + ->set($data); + + if ($condition !== null) { + $update->where($condition, $operator); + } + + return $this->prepexec($update); + } + + /** + * Delete table rows, optionally based on a given condition + * + * @param string|array $table The table to delete data from. The table specification must be in one of the + * following formats: 'table', 'table alias', ['alias' => 'table'] + * @param mixed $condition The WHERE condition + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return PDOStatement + */ + public function delete($table, $condition = null, $operator = Sql::ALL) + { + $delete = (new Delete()) + ->from($table); + + if ($condition !== null) { + $delete->where($condition, $operator); + } + + return $this->prepexec($delete); + } + + /** + * Begin a transaction + * + * @return bool Whether the transaction was started successfully + */ + public function beginTransaction() + { + $this->connect(); + + return $this->pdo->beginTransaction(); + } + + /** + * Commit a transaction + * + * @return bool Whether the transaction was committed successfully + */ + public function commitTransaction() + { + return $this->pdo->commit(); + } + + /** + * Roll back a transaction + * + * @return bool Whether the transaction was rolled back successfully + */ + public function rollBackTransaction() + { + return $this->pdo->rollBack(); + } + + /** + * Run the given callback in a transaction + * + * @param callable $callback The callback to run in a transaction. + * This connection instance is passed as parameter to the callback + * + * @return mixed The return value of the callback + * + * @throws Exception If an error occurs when running the callback + */ + public function transaction(callable $callback) + { + $this->beginTransaction(); + + try { + $result = call_user_func($callback, $this); + $this->commitTransaction(); + } catch (Exception $e) { + $this->rollBackTransaction(); + + throw $e; + } + + return $result; + } + + public function quoteIdentifier($identifier) + { + return $this->getAdapter()->quoteIdentifier($identifier); + } +} diff --git a/vendor/ipl/sql/src/Contract/Adapter.php b/vendor/ipl/sql/src/Contract/Adapter.php new file mode 100644 index 0000000..6142626 --- /dev/null +++ b/vendor/ipl/sql/src/Contract/Adapter.php @@ -0,0 +1,46 @@ +<?php + +namespace ipl\Sql\Contract; + +use ipl\Sql\Config; +use ipl\Sql\Connection; +use ipl\Sql\QueryBuilder; + +interface Adapter extends Quoter +{ + /** + * Get the DSN string from the given connection configuration + * + * @param Config $config + * + * @return string + */ + public function getDsn(Config $config); + + /** + * Get the PDO connect options based on the specified connection configuration + * + * @param Config $config + * + * @return array + */ + public function getOptions(Config $config); + + /** + * Set the client time zone + * + * @param Connection $db + * + * @return $this + */ + public function setClientTimezone(Connection $db); + + /** + * Register callbacks for query builder events + * + * @param QueryBuilder $queryBuilder + * + * @return $this + */ + public function registerQueryBuilderCallbacks(QueryBuilder $queryBuilder); +} diff --git a/vendor/ipl/sql/src/Contract/Quoter.php b/vendor/ipl/sql/src/Contract/Quoter.php new file mode 100644 index 0000000..79c4c78 --- /dev/null +++ b/vendor/ipl/sql/src/Contract/Quoter.php @@ -0,0 +1,21 @@ +<?php + +namespace ipl\Sql\Contract; + +interface Quoter +{ + /** + * Quote an identifier so that it can be safely used as table or column name, even if it is a reserved name + * + * If a string is passed that contains dots, the parts separated by them are quoted individually. + * (e.g. `myschema.mytable` turns into `"myschema"."mytable"`) If an array is passed, the entries + * are quoted as-is. (e.g. `[myschema.my, table]` turns into `"myschema.my"."table"`) + * + * The quote character depends on the underlying database adapter that is being used. + * + * @param string|string[] $identifiers + * + * @return string + */ + public function quoteIdentifier($identifiers); +} diff --git a/vendor/ipl/sql/src/Cursor.php b/vendor/ipl/sql/src/Cursor.php new file mode 100644 index 0000000..85c5b1c --- /dev/null +++ b/vendor/ipl/sql/src/Cursor.php @@ -0,0 +1,106 @@ +<?php + +namespace ipl\Sql; + +use ipl\Stdlib\Contract\Paginatable; +use IteratorAggregate; +use Traversable; + +/** + * Cursor for ipl SQL queries + */ +class Cursor implements IteratorAggregate, Paginatable +{ + /** @var Connection */ + protected $db; + + /** @var Select */ + protected $select; + + /** @var array */ + protected $fetchModeAndArgs = []; + + /** + * Create a new cursor for the given connection and query + * + * @param Connection $db + * @param Select $select + */ + public function __construct(Connection $db, Select $select) + { + $this->db = $db; + $this->select = $select; + } + + /** + * Get the fetch mode + * + * @return array + */ + public function getFetchMode() + { + return $this->fetchModeAndArgs; + } + + /** + * Set the fetch mode + * + * @param int $fetchMode Fetch mode as one of the PDO fetch mode constants. + * Please see {@link https://www.php.net/manual/en/pdostatement.setfetchmode} for details + * @param mixed ...$args Fetch mode arguments + * + * @return $this + */ + public function setFetchMode($fetchMode, ...$args) + { + array_unshift($args, $fetchMode); + + $this->fetchModeAndArgs = $args; + + return $this; + } + + public function getIterator(): Traversable + { + return $this->db->yieldAll($this->select, ...$this->getFetchMode()); + } + + public function hasLimit() + { + return $this->select->hasLimit(); + } + + public function getLimit() + { + return $this->select->getLimit(); + } + + public function limit($limit) + { + $this->select->limit($limit); + + return $this; + } + + public function hasOffset() + { + return $this->select->hasOffset(); + } + + public function getOffset() + { + return $this->select->getOffset(); + } + + public function offset($offset) + { + $this->select->offset($offset); + + return $this; + } + + public function count(): int + { + return $this->db->select($this->select->getCountQuery())->fetchColumn(0); + } +} diff --git a/vendor/ipl/sql/src/Delete.php b/vendor/ipl/sql/src/Delete.php new file mode 100644 index 0000000..53736b8 --- /dev/null +++ b/vendor/ipl/sql/src/Delete.php @@ -0,0 +1,52 @@ +<?php + +namespace ipl\Sql; + +/** + * SQL DELETE query + */ +class Delete implements CommonTableExpressionInterface, WhereInterface +{ + use CommonTableExpression; + use Where; + + /** @var array|null The FROM part of the DELETE query */ + protected $from; + + /** + * Get the FROM part of the DELETE query + * + * @return array|null + */ + public function getFrom() + { + return $this->from; + } + + /** + * Set the FROM part of the DELETE query + * + * Note that this method does NOT quote the table you specify for the DELETE FROM. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the table names passed to this method. + * If you are using special table names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param string|array $table The table to delete data from. The table specification must be in one of the + * following formats: 'table', 'table alias', ['alias' => 'table'] + * + * @return $this + */ + public function from($table) + { + $this->from = ! is_array($table) ? [$table] : $table; + + return $this; + } + + public function __clone() + { + $this->cloneCte(); + $this->cloneWhere(); + } +} diff --git a/vendor/ipl/sql/src/Expression.php b/vendor/ipl/sql/src/Expression.php new file mode 100644 index 0000000..83c10bd --- /dev/null +++ b/vendor/ipl/sql/src/Expression.php @@ -0,0 +1,54 @@ +<?php + +namespace ipl\Sql; + +/** + * A database expression that does need quoting or escaping, e.g. new Expression('NOW()'); + */ +class Expression implements ExpressionInterface +{ + /** @var string The statement of the expression */ + protected $statement; + + /** @var array The columns used by the expression */ + protected $columns; + + /** @var array The values for the expression */ + protected $values; + + /** + * Create a new database expression + * + * @param string $statement The statement of the expression + * @param array $columns The columns used by the expression + * @param mixed ...$values The values for the expression + */ + public function __construct($statement, array $columns = null, ...$values) + { + $this->statement = $statement; + $this->columns = $columns; + $this->values = $values; + } + + public function getStatement() + { + return $this->statement; + } + + public function getColumns() + { + return $this->columns ?: []; + } + + public function setColumns(array $columns) + { + $this->columns = $columns; + + return $this; + } + + public function getValues() + { + return $this->values; + } +} diff --git a/vendor/ipl/sql/src/ExpressionInterface.php b/vendor/ipl/sql/src/ExpressionInterface.php new file mode 100644 index 0000000..9ebe5ee --- /dev/null +++ b/vendor/ipl/sql/src/ExpressionInterface.php @@ -0,0 +1,39 @@ +<?php + +namespace ipl\Sql; + +/** + * Interface for database expressions that do need quoting or escaping, e.g. new Expression('NOW()'); + */ +interface ExpressionInterface +{ + /** + * Get the statement of the expression + * + * @return string + */ + public function getStatement(); + + /** + * Get the columns used by the expression + * + * @return array + */ + public function getColumns(); + + /** + * Set the columns to use by the expression + * + * @param array $columns + * + * @return $this + */ + public function setColumns(array $columns); + + /** + * Get the values for the expression + * + * @return array + */ + public function getValues(); +} diff --git a/vendor/ipl/sql/src/Filter/Exists.php b/vendor/ipl/sql/src/Filter/Exists.php new file mode 100644 index 0000000..e1951d0 --- /dev/null +++ b/vendor/ipl/sql/src/Filter/Exists.php @@ -0,0 +1,14 @@ +<?php + +namespace ipl\Sql\Filter; + +use ipl\Sql\Select; +use ipl\Stdlib\Filter; + +class Exists extends Filter\Condition +{ + public function __construct(Select $select) + { + parent::__construct('', $select); + } +} diff --git a/vendor/ipl/sql/src/Filter/In.php b/vendor/ipl/sql/src/Filter/In.php new file mode 100644 index 0000000..c126af6 --- /dev/null +++ b/vendor/ipl/sql/src/Filter/In.php @@ -0,0 +1,24 @@ +<?php + +namespace ipl\Sql\Filter; + +use ipl\Sql\Select; +use ipl\Stdlib\Filter; + +class In extends Filter\Condition +{ + use InAndNotInUtils; + + /** + * Create a new sql IN condition + * + * @param string[]|string $column + * @param Select $select + */ + public function __construct($column, Select $select) + { + $this + ->setColumn($column) + ->setValue($select); + } +} diff --git a/vendor/ipl/sql/src/Filter/InAndNotInUtils.php b/vendor/ipl/sql/src/Filter/InAndNotInUtils.php new file mode 100644 index 0000000..6f26de1 --- /dev/null +++ b/vendor/ipl/sql/src/Filter/InAndNotInUtils.php @@ -0,0 +1,62 @@ +<?php + +namespace ipl\Sql\Filter; + +use ipl\Sql\Select; + +trait InAndNotInUtils +{ + /** @var string[]|string */ + protected $column; + + /** @var Select */ + protected $value; + + /** + * Get the columns of this condition + * + * @return string[]|string + */ + public function getColumn() + { + return $this->column; + } + + /** + * Set the columns of this condition + * + * @param string[]|string $column + * + * @return $this + */ + public function setColumn($column): self + { + $this->column = $column; + + return $this; + } + + /** + * Get the value of this condition + * + * @return Select + */ + public function getValue(): Select + { + return $this->value; + } + + /** + * Set the value of this condition + * + * @param Select $value + * + * @return $this + */ + public function setValue($value): self + { + $this->value = $value; + + return $this; + } +} diff --git a/vendor/ipl/sql/src/Filter/NotExists.php b/vendor/ipl/sql/src/Filter/NotExists.php new file mode 100644 index 0000000..bb8be35 --- /dev/null +++ b/vendor/ipl/sql/src/Filter/NotExists.php @@ -0,0 +1,14 @@ +<?php + +namespace ipl\Sql\Filter; + +use ipl\Sql\Select; +use ipl\Stdlib\Filter; + +class NotExists extends Filter\Condition +{ + public function __construct(Select $select) + { + parent::__construct('', $select); + } +} diff --git a/vendor/ipl/sql/src/Filter/NotIn.php b/vendor/ipl/sql/src/Filter/NotIn.php new file mode 100644 index 0000000..cdf6241 --- /dev/null +++ b/vendor/ipl/sql/src/Filter/NotIn.php @@ -0,0 +1,24 @@ +<?php + +namespace ipl\Sql\Filter; + +use ipl\Sql\Select; +use ipl\Stdlib\Filter; + +class NotIn extends Filter\Condition +{ + use InAndNotInUtils; + + /** + * Create a new sql NOT IN condition + * + * @param string[]|string $column + * @param Select $select + */ + public function __construct($column, Select $select) + { + $this + ->setColumn($column) + ->setValue($select); + } +} diff --git a/vendor/ipl/sql/src/Insert.php b/vendor/ipl/sql/src/Insert.php new file mode 100644 index 0000000..738a842 --- /dev/null +++ b/vendor/ipl/sql/src/Insert.php @@ -0,0 +1,172 @@ +<?php + +namespace ipl\Sql; + +use InvalidArgumentException; + +use function ipl\Stdlib\arrayval; + +/** + * SQL INSERT query + */ +class Insert implements CommonTableExpressionInterface +{ + use CommonTableExpression; + + /** @var string|null The table for the INSERT INTO query */ + protected $into; + + /** @var array|null The columns for which the query provides values */ + protected $columns; + + /** @var array|null The values to insert */ + protected $values; + + /** @var Select|null The select query for INSERT INTO ... SELECT queries */ + protected $select; + + /** + * Get the table for the INSERT INTo query + * + * @return string|null + */ + public function getInto() + { + return $this->into; + } + + /** + * Set the table for the INSERT INTO query + * + * Note that this method does NOT quote the table you specify for the INSERT INTO. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the table name passed to this method. + * If you are using special table names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param string $table The table to insert data into. The table specification must be in one of the following + * formats: 'table' or 'schema.table' + * + * @return $this + */ + public function into($table) + { + $this->into = $table; + + return $this; + } + + /** + * Get the columns for which the statement provides values + * + * @return array + */ + public function getColumns() + { + if (! empty($this->columns)) { + return array_keys($this->columns); + } + + if (! empty($this->values)) { + return array_keys($this->values); + } + + return []; + } + + /** + * Set the columns for which the query provides values + * + * Note that this method does NOT quote the columns you specify for the INSERT INTO. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the column names passed to this method. + * If you are using special column names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * If you do not set the columns for which the query provides values using this method, you must pass the values to + * {@link values()} in terms of column-value pairs in order to provide the column names. + * + * @param array $columns + * + * @return $this + */ + public function columns(array $columns) + { + $this->columns = array_flip($columns); + + return $this; + } + + /** + * Get the values to insert + * + * @return array + */ + public function getValues() + { + return array_values($this->values ?: []); + } + + /** + * Set the values to INSERT INTO - either plain values or expressions or scalar subqueries + * + * If you do not set the columns for which the query provides values using {@link columns()}, you must specify + * the values in terms of column-value pairs in order to provide the column names. Please note that the same + * restriction regarding quoting applies here. If you use {@link columns()} to set the columns and specify the + * values in terms of column-value pairs, the columns from {@link columns()} will be used nonetheless. + * + * @param iterable $values List of values or associative set of column-value pairs + * + * @return $this + * + * @throws InvalidArgumentException If values type is invalid + */ + public function values($values) + { + $this->values = arrayval($values); + + return $this; + } + + /** + * Create a INSERT INTO ... SELECT statement + * + * @param Select $select + * + * @return $this + */ + public function select(Select $select) + { + $this->select = $select; + + return $this; + } + + /** + * Get the select query for the INSERT INTO ... SELECT statement + * + * @return Select|null + */ + public function getSelect() + { + return $this->select; + } + + public function __clone() + { + $this->cloneCte(); + + if ($this->values !== null) { + foreach ($this->values as &$value) { + if ($value instanceof ExpressionInterface || $value instanceof Select) { + $value = clone $value; + } + } + unset($value); + } + + if ($this->select !== null) { + $this->select = clone $this->select; + } + } +} diff --git a/vendor/ipl/sql/src/LimitOffset.php b/vendor/ipl/sql/src/LimitOffset.php new file mode 100644 index 0000000..99c30a2 --- /dev/null +++ b/vendor/ipl/sql/src/LimitOffset.php @@ -0,0 +1,89 @@ +<?php + +namespace ipl\Sql; + +/** + * Implementation for the {@link LimitOffsetInterface} to allow pagination via {@link limit()} and {@link offset()} + */ +trait LimitOffset +{ + /** + * The maximum number of how many items to return + * + * If unset or lower than 0, no limit will be applied. + * + * @var int|null + */ + protected $limit; + + /** + * Offset from where to start the result set + * + * If unset or lower than 0, the result set will start from the beginning. + * + * @var int|null + */ + protected $offset; + + public function hasLimit() + { + return $this->limit !== null; + } + + public function getLimit() + { + return $this->limit; + } + + public function limit($limit) + { + if ($limit !== null) { + $limit = (int) $limit; + if ($limit < 0) { + $limit = null; + } + } + + $this->limit = $limit; + + return $this; + } + + public function resetLimit() + { + $this->limit = null; + + return $this; + } + + public function hasOffset() + { + return $this->offset !== null; + } + + public function getOffset() + { + return $this->offset; + } + + public function offset($offset) + { + if ($offset !== null) { + $offset = (int) $offset; + if ($offset <= 0) { + $offset = null; + } + } + + $this->offset = $offset; + + return $this; + } + + public function resetOffset() + { + $this->offset = null; + + return $this; + } +} diff --git a/vendor/ipl/sql/src/LimitOffsetInterface.php b/vendor/ipl/sql/src/LimitOffsetInterface.php new file mode 100644 index 0000000..94628c4 --- /dev/null +++ b/vendor/ipl/sql/src/LimitOffsetInterface.php @@ -0,0 +1,71 @@ +<?php + +namespace ipl\Sql; + +/** + * Interface for pagination via {@link limit()} and {@link offset()} + */ +interface LimitOffsetInterface +{ + /** + * Get whether a limit is configured + * + * @return bool + */ + public function hasLimit(); + + /** + * Get the limit + * + * @return int|null + */ + public function getLimit(); + + /** + * Set the limit + * + * @param int|null $limit Maximum number of items to return. + * If you want to disable the limit, use null or a negative value + * + * @return $this + */ + public function limit($limit); + + /** + * Reset the limit + * + * @return $this + */ + public function resetLimit(); + + /** + * Get whether an offset is configured + * + * @return bool + */ + public function hasOffset(); + + /** + * Get the offset + * + * @return int|null + */ + public function getOffset(); + + /** + * Set the offset + * + * @param int|null $offset Start result set after this many rows. + * If you want to disable the offset, use null, 0, or a negative value + * + * @return $this + */ + public function offset($offset); + + /** + * Reset the offset + * + * @return $this + */ + public function resetOffset(); +} diff --git a/vendor/ipl/sql/src/OrderBy.php b/vendor/ipl/sql/src/OrderBy.php new file mode 100644 index 0000000..a19d7c5 --- /dev/null +++ b/vendor/ipl/sql/src/OrderBy.php @@ -0,0 +1,74 @@ +<?php + +namespace ipl\Sql; + +/** + * Trait for the ORDER BY part of a query + */ +trait OrderBy +{ + /** @var ?array ORDER BY part of the query */ + protected $orderBy; + + public function hasOrderBy() + { + return $this->orderBy !== null; + } + + public function getOrderBy() + { + return $this->orderBy; + } + + public function orderBy($orderBy, $direction = null) + { + if (! is_array($orderBy)) { + $orderBy = [$orderBy]; + } + + foreach ($orderBy as $column => $dir) { + if (is_int($column)) { + $column = $dir; + $dir = $direction; + } + + if (is_array($column) && count($column) === 2) { + list($column, $dir) = $column; + } + + if ($dir === SORT_ASC) { + $dir = 'ASC'; + } elseif ($dir === SORT_DESC) { + $dir = 'DESC'; + } + + $this->orderBy[] = [$column, $dir]; + } + + return $this; + } + + public function resetOrderBy() + { + $this->orderBy = null; + + return $this; + } + + /** + * Clone the properties provided by this trait + * + * Shall be called by using classes in their __clone() + */ + protected function cloneOrderBy() + { + if ($this->orderBy !== null) { + foreach ($this->orderBy as &$orderBy) { + if ($orderBy[0] instanceof ExpressionInterface || $orderBy[0] instanceof Select) { + $orderBy[0] = clone $orderBy[0]; + } + } + unset($orderBy); + } + } +} diff --git a/vendor/ipl/sql/src/OrderByInterface.php b/vendor/ipl/sql/src/OrderByInterface.php new file mode 100644 index 0000000..0ee0dda --- /dev/null +++ b/vendor/ipl/sql/src/OrderByInterface.php @@ -0,0 +1,51 @@ +<?php + +namespace ipl\Sql; + +/** + * Interface for the ORDER BY part of a query + */ +interface OrderByInterface +{ + /** + * Get whether a ORDER BY part is configured + * + * @return bool + */ + public function hasOrderBy(); + + /** + * Get the ORDER BY part of the query + * + * @return array|null + */ + public function getOrderBy(); + + /** + * Set the ORDER BY part of the query - either plain columns or expressions or scalar subqueries + * + * Note that this method does not override an already set ORDER BY part. Instead, each call to this function + * appends the specified ORDER BY part to an already existing one. + * + * This method does NOT quote the columns you specify for the ORDER BY. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the field names passed to this method. + * If you are using special field names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param string|int|array $orderBy The ORDER BY part. The items can be in any format of the following: + * ['column', 'column' => 'DESC', 'column' => SORT_DESC, ['column', 'DESC']] + * @param string|int $direction The default direction. Can be any of the following: + * 'ASC', 'DESC', SORT_ASC, SORT_DESC + * + * @return $this + */ + public function orderBy($orderBy, $direction = null); + + /** + * Reset the ORDER BY part of the query + * + * @return $this + */ + public function resetOrderBy(); +} diff --git a/vendor/ipl/sql/src/QueryBuilder.php b/vendor/ipl/sql/src/QueryBuilder.php new file mode 100644 index 0000000..07b5e3e --- /dev/null +++ b/vendor/ipl/sql/src/QueryBuilder.php @@ -0,0 +1,907 @@ +<?php + +namespace ipl\Sql; + +use InvalidArgumentException; +use ipl\Sql\Adapter\Mssql; +use ipl\Sql\Contract\Adapter; +use ipl\Stdlib\Events; + +use function ipl\Stdlib\get_php_type; + +class QueryBuilder +{ + use Events; + + /** + * Event raised when a {@link Select} object is assembled into a SQL statement string + * + * The {@link Select} object is passed as parameter to the event callbacks. + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_ASSEMBLE_SELECT, function (Select $select) { + * // ... + * }); + * ``` + */ + public const ON_ASSEMBLE_SELECT = 'assembleSelect'; + + /** + * Event raised after a {@link Select} object is assembled into a SQL statement string + * + * The assembled SQL statement string and the values to bind to the statement are passed as parameters by reference + * to the event callbacks. + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_SELECT_ASSEMBLED, function (&$sql, &$values) { + * // ... + * }); + * ``` + */ + public const ON_SELECT_ASSEMBLED = 'selectAssembled'; + + /** + * Event raised before an {@see Insert} object is assembled into a SQL statement string + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_ASSEMBLE_INSERT, function (Insert $insert) { + * // ... + * }); + * ``` + * + * @var string + */ + public const ON_ASSEMBLE_INSERT = 'assembleInsert'; + + /** + * Event raised after an {@see Insert} object is assembled into a SQL statement string + * + * The assembled SQL statement string and the prepared values are passed by reference to the event callbacks + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_INSERT_ASSEMBLED, function (&$sql, &$values) { + * // ... + * }); + * ``` + * + * @var string + */ + public const ON_INSERT_ASSEMBLED = 'insertAssembled'; + + /** + * Event raised before an {@see Update} object is assembled into a SQL statement string + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_ASSEMBLE_UPDATE, function (Update $update) { + * // ... + * }); + * ``` + * + * @var string + */ + public const ON_ASSEMBLE_UPDATE = 'assembleUpdate'; + + /** + * Event raised after an {@see Update} object is assembled into a SQL statement string + * + * The assembled SQL statement string and the prepared values are passed by reference to the event callbacks + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_UPDATE_ASSEMBLED, function (&$sql, &$values) { + * // ... + * }); + * ``` + * + * @var string + */ + public const ON_UPDATE_ASSEMBLED = 'updateAssembled'; + + /** + * Event raised before a {@see Delete} object is assembled into a SQL statement string + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_ASSEMBLE_DELETE, function (Delete $delete) { + * // ... + * }); + * ``` + * + * @var string + */ + public const ON_ASSEMBLE_DELETE = 'assembleDelete'; + + /** + * Event raised after a {@see Delete} object is assembled into a SQL statement string + * + * The assembled SQL statement string and the prepared values are passed by reference to the event callbacks + * + * **Example usage:** + * + * ``` + * $queryBuilder->on(QueryBuilder::ON_DELETE_ASSEMBLED, function (&$sql, &$values) { + * // ... + * }); + * ``` + * + * @var string + */ + public const ON_DELETE_ASSEMBLED = 'deleteAssembled'; + + /** @var Adapter */ + protected $adapter; + + protected $separator = " "; + + /** + * Create a new query builder for the specified database adapter + * + * @param Adapter $adapter + */ + public function __construct(Adapter $adapter) + { + $adapter->registerQueryBuilderCallbacks($this); + + $this->adapter = $adapter; + } + + /** + * Assemble the given statement + * + * @param Delete|Insert|Select|Update $stmt + * + * @return array + * + * @throw InvalidArgumentException If statement type is invalid + */ + public function assemble($stmt) + { + switch (true) { + case $stmt instanceof Delete: + return $this->assembleDelete($stmt); + case $stmt instanceof Insert: + return $this->assembleInsert($stmt); + case $stmt instanceof Select: + return $this->assembleSelect($stmt); + case $stmt instanceof Update: + return $this->assembleUpdate($stmt); + default: + throw new InvalidArgumentException(sprintf( + __METHOD__ . ' expects instances of Delete, Insert, Select or Update. Got %s instead.', + get_php_type($stmt) + )); + } + } + + /** + * Assemble a DELETE query + * + * @param Delete $delete + * + * @return array + */ + public function assembleDelete(Delete $delete) + { + $values = []; + + $this->emit(self::ON_ASSEMBLE_DELETE, [$delete]); + + $sql = array_filter([ + $this->buildWith($delete->getWith(), $values), + $this->buildDeleteFrom($delete->getFrom()), + $this->buildWhere($delete->getWhere(), $values) + ]); + + $sql = implode($this->separator, $sql); + + $this->emit(static::ON_DELETE_ASSEMBLED, [&$sql, &$values]); + + return [$sql, $values]; + } + + /** + * Assemble a INSERT statement + * + * @param Insert $insert + * + * @return array + */ + public function assembleInsert(Insert $insert) + { + $values = []; + + $this->emit(static::ON_ASSEMBLE_INSERT, [$insert]); + + $select = $insert->getSelect(); + + $sql = array_filter([ + $this->buildWith($insert->getWith(), $values), + $this->buildInsertInto($insert->getInto()), + $select + ? $this->buildInsertIntoSelect($insert->getColumns(), $select, $values) + : $this->buildInsertColumnsAndValues($insert->getColumns(), $insert->getValues(), $values) + ]); + + $sql = implode($this->separator, $sql); + + $this->emit(static::ON_INSERT_ASSEMBLED, [&$sql, &$values]); + + return [$sql, $values]; + } + + /** + * Assemble a SELECT query + * + * @param Select $select + * @param array $values + * + * @return array + */ + public function assembleSelect(Select $select, array &$values = []) + { + $select = clone $select; + + $this->emit(static::ON_ASSEMBLE_SELECT, [$select]); + + $sql = array_filter([ + $this->buildWith($select->getWith(), $values), + $this->buildSelect($select->getColumns(), $select->getDistinct(), $values), + $this->buildFrom($select->getFrom(), $values), + $this->buildJoin($select->getJoin(), $values), + $this->buildWhere($select->getWhere(), $values), + $this->buildGroupBy($select->getGroupBy(), $values), + $this->buildHaving($select->getHaving(), $values), + $this->buildOrderBy($select->getOrderBy(), $values), + $this->buildLimitOffset($select->getLimit(), $select->getOffset()) + ]); + + $sql = implode($this->separator, $sql); + + $unions = $this->buildUnions($select->getUnion(), $values); + if ($unions) { + list($unionKeywords, $selects) = $unions; + + if ($sql) { + $sql = "($sql)"; + + $requiresUnionKeyword = true; + } else { + $requiresUnionKeyword = false; + } + + do { + $unionKeyword = array_shift($unionKeywords); + $select = array_shift($selects); + + if ($requiresUnionKeyword) { + $sql .= "{$this->separator}$unionKeyword{$this->separator}"; + } + + $sql .= "($select)"; + + $requiresUnionKeyword = true; + } while (! empty($unionKeywords)); + } + + $this->emit(static::ON_SELECT_ASSEMBLED, [&$sql, &$values]); + + return [$sql, $values]; + } + + /** + * Assemble a UPDATE query + * + * @param Update $update + * + * @return array + */ + public function assembleUpdate(Update $update) + { + $values = []; + + $this->emit(self::ON_ASSEMBLE_UPDATE, [$update]); + + $sql = array_filter([ + $this->buildWith($update->getWith(), $values), + $this->buildUpdateTable($update->getTable()), + $this->buildUpdateSet($update->getSet(), $values), + $this->buildWhere($update->getWhere(), $values) + ]); + + $sql = implode($this->separator, $sql); + + $this->emit(static::ON_UPDATE_ASSEMBLED, [&$sql, &$values]); + + return [$sql, $values]; + } + + /** + * Build the WITH part of a query + * + * @param array $with + * @param array $values + * + * @return string The WITH part of a query + */ + public function buildWith(array $with, array &$values) + { + if (empty($with)) { + return ''; + } + + $ctes = []; + $hasRecursive = false; + + foreach ($with as $cte) { + list($query, $alias, $recursive) = $cte; + list($cteSql, $cteValues) = $this->assembleSelect($query); + + $ctes[] = "$alias AS ($cteSql)"; + + $values = array_merge($values, $cteValues); + $hasRecursive |= $recursive; + } + + return ($hasRecursive ? 'WITH RECURSIVE ' : 'WITH ') . implode(', ', $ctes); + } + + /** + * Build the DELETE FROM part of a query + * + * @param array $from + * + * @return string The DELETE FROM part of a query + */ + public function buildDeleteFrom(array $from = null) + { + if ($from === null) { + return ''; + } + + $deleteFrom = 'DELETE FROM'; + + reset($from); + $alias = key($from); + $table = current($from); + + if (is_int($alias)) { + $deleteFrom .= " $table"; + } else { + $deleteFrom .= " $table $alias"; + } + + return $deleteFrom; + } + + /** + * Outsourced logic of {@link buildCondition()} + * + * @param string $expression + * @param array $values + * + * @return array + */ + public function unpackCondition($expression, array $values) + { + $placeholders = preg_match_all('/(\?)/', $expression, $matches, PREG_OFFSET_CAPTURE | PREG_SET_ORDER); + + if ($placeholders === 0) { + return [$expression, []]; + } + + if ($placeholders === 1) { + $offset = $matches[0][1][1]; + $expression = substr($expression, 0, $offset) + . implode(', ', array_fill(0, count($values), '?')) + . substr($expression, $offset + 1); + + return [$expression, $values]; + } + + $unpackedExpression = []; + $unpackedValues = []; + $offset = 0; + + foreach ($matches as $match) { + $value = array_shift($values); + $unpackedExpression[] = substr($expression, $offset, $match[1][1] - $offset); + if (is_array($value)) { + $unpackedExpression[] = implode(', ', array_fill(0, count($value), '?')); + $unpackedValues = array_merge($unpackedValues, $value); + } else { + $unpackedExpression[] = '?'; + $unpackedValues[] = $value; + } + $offset = $match[1][1] + 1; // 1 is the length of '?' + } + + $unpackedExpression[] = substr($expression, $offset); + + return [implode('', array_filter($unpackedExpression)), $unpackedValues]; + } + + /** + * Outsourced logic {@link buildWhere()} and {@link buildHaving()} have in common + * + * @param array $condition + * @param array $values + * + * @return string + */ + public function buildCondition(array $condition, array &$values) + { + $sql = []; + + $operator = array_shift($condition); + $conditions = array_shift($condition); + + foreach ($conditions as $expression => $value) { + if (is_array($value)) { + if (is_int($expression)) { + // Operator format + $sql[] = $this->buildCondition($value, $values); + } else { + list($unpackedExpression, $unpackedValues) = $this->unpackCondition($expression, $value); + $sql[] = $unpackedExpression; + $values = array_merge($values, $unpackedValues); + } + } else { + if ($value instanceof ExpressionInterface) { + $sql[] = $this->buildExpression($value, $values); + } elseif ($value instanceof Select) { + $stmt = '(' . $this->assembleSelect($value, $values)[0] . ')'; + if (is_int($expression)) { + $sql[] = $stmt; + } else { + $sql[] = str_replace('?', $stmt, $expression); + } + } elseif (is_int($expression)) { + $sql[] = $value; + } else { + $sql[] = $expression; + $values[] = $value; + } + } + } + + if ($operator === Sql::NOT_ALL || $operator === Sql::NOT_ANY) { + return 'NOT (' . implode(") $operator (", $sql) . ')'; + } + + return count($sql) === 1 ? $sql[0] : '(' . implode(") $operator (", $sql) . ')'; + } + + /** + * Build the WHERE part of a query + * + * @param array $where + * @oaram array $values + * + * @return string The WHERE part of the query + */ + public function buildWhere(array $where = null, array &$values = []) + { + if ($where === null) { + return ''; + } + + return 'WHERE ' . $this->buildCondition($where, $values); + } + + /** + * Build the INSERT INTO part of a INSERT INTO ... statement + * + * @param string|null $into + * + * @return string The INSERT INTO part of a INSERT INTO ... statement + */ + public function buildInsertInto($into) + { + if (empty($into)) { + return ''; + } + + return "INSERT INTO $into"; + } + + /** + * Build the columns and SELECT part of a INSERT INTO ... SELECT statement + * + * @param array $columns + * @param Select $select + * @param array $values + * + * @return string The columns and SELECT part of the INSERT INTO ... SELECT statement + */ + public function buildInsertIntoSelect(array $columns, Select $select, array &$values) + { + $sql = [ + '(' . implode(',', $columns) . ')', + $this->assembleSelect($select, $values)[0] + ]; + + return implode($this->separator, $sql); + } + + /** + * Build the columns and values part of a INSERT INTO ... statement + * + * @param array $columns + * @param array $insertValues + * @param array $values + * + * @return string The columns and values part of a INSERT INTO ... statement + */ + public function buildInsertColumnsAndValues(array $columns, array $insertValues, array &$values) + { + $sql = ['(' . implode(',', $columns) . ')']; + + $preparedValues = []; + + foreach ($insertValues as $value) { + if ($value instanceof ExpressionInterface) { + $preparedValues[] = $this->buildExpression($value, $values); + } elseif ($value instanceof Select) { + $preparedValues[] = "({$this->assembleSelect($value, $values)[0]})"; + } else { + $preparedValues[] = '?'; + $values[] = $value; + } + } + + $sql[] = 'VALUES(' . implode(',', $preparedValues) . ')'; + + return implode($this->separator, $sql); + } + + /** + * Build the SELECT part of a query + * + * @param array $columns + * @param bool $distinct + * @param array $values + * + * @return string The SELECT part of the query + */ + public function buildSelect(array $columns, $distinct, array &$values) + { + if (empty($columns)) { + return ''; + } + + $select = 'SELECT'; + + if ($distinct) { + $select .= ' DISTINCT'; + } + + $sql = []; + + foreach ($columns as $alias => $column) { + if ($column instanceof ExpressionInterface) { + $column = "({$this->buildExpression($column, $values)})"; + } elseif ($column instanceof Select) { + $column = "({$this->assembleSelect($column, $values)[0]})"; + } + + if (is_int($alias)) { + $sql[] = $column; + } else { + $sql[] = "$column AS $alias"; + } + } + + return "$select " . implode(', ', $sql); + } + + /** + * Build the FROM part of a query + * + * @param array $from + * @param array $values + * + * @return string The FROM part of the query + */ + public function buildFrom(array $from = null, array &$values = []) + { + if ($from === null) { + return ''; + } + + $sql = []; + + foreach ($from as $alias => $table) { + if ($table instanceof Select) { + $table = "({$this->assembleSelect($table, $values)[0]})"; + } + + if (is_int($alias) || $alias === $table) { + $sql[] = $table; + } else { + $sql[] = "$table $alias"; + } + } + + return 'FROM ' . implode(', ', $sql); + } + + /** + * Build the JOIN part(s) of a query + * + * @param array $joins + * @oaram array $values + * + * @return string The JOIN part(s) of the query + */ + public function buildJoin($joins, array &$values) + { + if ($joins === null) { + return ''; + } + + $sql = []; + foreach ($joins as $join) { + list($joinType, $table, $condition) = $join; + + if (is_array($table)) { + $tableName = null; + foreach ($table as $alias => $tableName) { + break; + } + } else { + $alias = null; + $tableName = $table; + } + + if ($tableName instanceof Select) { + $tableName = "({$this->assembleSelect($tableName, $values)[0]})"; + } + + if (is_array($condition)) { + $condition = $this->buildCondition($condition, $values); + } + + if (empty($alias) || $alias === $tableName) { + $sql[] = "$joinType JOIN $tableName ON $condition"; + } else { + $sql[] = "$joinType JOIN $tableName $alias ON $condition"; + } + } + + return implode($this->separator, $sql); + } + + /** + * Build the GROUP BY part of a query + * + * @param array $groupBy + * @param array $values + * + * @return string The GROUP BY part of the query + */ + public function buildGroupBy(array $groupBy = null, array &$values = []) + { + if ($groupBy === null) { + return ''; + } + + foreach ($groupBy as &$column) { + if ($column instanceof ExpressionInterface) { + $column = $this->buildExpression($column, $values); + } elseif ($column instanceof Select) { + $column = "({$this->assembleSelect($column, $values)[0]})"; + } + } + + return 'GROUP BY ' . implode(', ', $groupBy); + } + + /** + * Build the HAVING part of a query + * + * @param array $having + * @param array $values + * + * @return string The HAVING part of the query + */ + public function buildHaving(array $having = null, array &$values = []) + { + if ($having === null) { + return ''; + } + + return 'HAVING ' . $this->buildCondition($having, $values); + } + + /** + * Build the ORDER BY part of a query + * + * @param array $orderBy + * @param array $values + * + * @return string The ORDER BY part of the query + */ + public function buildOrderBy(array $orderBy = null, array &$values = []) + { + if ($orderBy === null) { + return ''; + } + + $sql = []; + + foreach ($orderBy as $column) { + list($column, $direction) = $column; + + if ($column instanceof ExpressionInterface) { + $column = $this->buildExpression($column, $values); + } elseif ($column instanceof Select) { + $column = "({$this->assembleSelect($column, $values)[0]})"; + } + + if ($direction !== null) { + $sql[] = "$column $direction"; + } else { + $sql[] = $column; + } + } + + return 'ORDER BY ' . implode(', ', $sql); + } + + /** + * Build the LIMIT and OFFSET part of a query + * + * @param int $limit + * @param int $offset + * + * @return string The LIMIT and OFFSET part of the query + */ + public function buildLimitOffset($limit = null, $offset = null) + { + $sql = []; + + if ($this->adapter instanceof Mssql) { + if ($offset !== null || $limit !== null) { + // If offset is null, sprintf will convert it to 0 + $sql[] = sprintf('OFFSET %d ROWS', $offset); + } + + if ($limit !== null) { + // FETCH FIRST n ROWS ONLY for OFFSET 0 would be an alternative here + $sql[] = "FETCH NEXT $limit ROWS ONLY"; + } + } else { + if ($limit !== null) { + $sql[] = "LIMIT $limit"; + } + + if ($offset !== null) { + $sql[] = "OFFSET $offset"; + } + } + + return implode($this->separator, $sql); + } + + /** + * Build the UNION parts of a query + * + * @param array $unions + * @param array $values + * + * @return array|null The UNION parts of the query + */ + public function buildUnions(array $unions = null, array &$values = []) + { + if ($unions === null) { + return null; + } + + $unionKeywords = []; + $selects = []; + + foreach ($unions as $union) { + list($select, $all) = $union; + + if ($select instanceof Select) { + list($select, $values) = $this->assembleSelect($select, $values); + } + + $unionKeywords[] = ($all ? 'UNION ALL' : 'UNION'); + $selects[] = $select; + } + + return [$unionKeywords, $selects]; + } + + /** + * Build the UPDATE {table} part of a query + * + * @param array $updateTable The table to UPDATE + * + * @return string The UPDATE {table} part of the query + */ + public function buildUpdateTable(array $updateTable = null) + { + if ($updateTable === null) { + return ''; + } + + $update = 'UPDATE'; + + reset($updateTable); + $alias = key($updateTable); + $table = current($updateTable); + + if (is_int($alias)) { + $update .= " $table"; + } else { + $update .= " $table $alias"; + } + + return $update; + } + + /** + * Build the SET part of a UPDATE query + * + * @param array $set + * @param array $values + * + * @return string The SET part of a UPDATE query + */ + public function buildUpdateSet(array $set = null, array &$values = []) + { + if (empty($set)) { + return ''; + } + + $sql = []; + + foreach ($set as $column => $value) { + if ($value instanceof ExpressionInterface) { + $sql[] = "$column = {$this->buildExpression($value, $values)}"; + } elseif ($value instanceof Select) { + $sql[] = "$column = ({$this->assembleSelect($value, $values)[0]})"; + } else { + $sql[] = "$column = ?"; + $values[] = $value; + } + } + + return 'SET ' . implode(', ', $sql); + } + + /** + * Build expression + * + * @param ExpressionInterface $expression + * @param array $values + * + * @return string The expression's statement + */ + public function buildExpression(ExpressionInterface $expression, array &$values = []) + { + $stmt = $expression->getStatement(); + $columns = $expression->getColumns(); + if (! empty($columns)) { + $stmt = vsprintf($stmt, $columns); + } + + $values = array_merge($values, $expression->getValues()); + + return $stmt; + } +} diff --git a/vendor/ipl/sql/src/Select.php b/vendor/ipl/sql/src/Select.php new file mode 100644 index 0000000..f56a131 --- /dev/null +++ b/vendor/ipl/sql/src/Select.php @@ -0,0 +1,562 @@ +<?php + +namespace ipl\Sql; + +/** + * SQL SELECT query + */ +class Select implements CommonTableExpressionInterface, LimitOffsetInterface, OrderByInterface, WhereInterface +{ + use CommonTableExpression; + use LimitOffset; + use OrderBy; + use Where; + + /** @var bool Whether the query is DISTINCT */ + protected $distinct = false; + + /** @var array|null The columns for the SELECT query */ + protected $columns; + + /** @var array|null FROM part of the query, i.e. the table names to select data from */ + protected $from; + + /** + * The tables to JOIN + * + * [ + * [ $joinType, $tableName, $condition ], + * ... + * ] + * + * @var ?array + */ + protected $join; + + /** @var array|null The columns for the GROUP BY part of the query */ + protected $groupBy; + + /** @var array|null Internal representation for the HAVING part of the query */ + protected $having; + + /** + * The queries to UNION + * + * [ + * [ new Select(), (bool) 'UNION ALL' ], + * ... + * ] + * + * @var ?array + */ + protected $union; + + /** + * Get whether to SELECT DISTINCT + * + * @return bool + */ + public function getDistinct() + { + return $this->distinct; + } + + /** + * Set whether to SELECT DISTINCT + * + * @param bool $distinct + * + * @return $this + */ + public function distinct($distinct = true) + { + $this->distinct = $distinct; + + return $this; + } + + /** + * Get the columns for the SELECT query + * + * @return array + */ + public function getColumns() + { + return $this->columns ?: []; + } + + /** + * Add columns to the SELECT query + * + * Multiple calls to this method will not overwrite the previous set columns but append the columns to the query. + * + * Note that this method does NOT quote the columns you specify for the SELECT. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the column names passed to this method. + * If you are using special column names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param string|ExpressionInterface|Select|array $columns The column(s) to add to the SELECT. + * The items can be any mix of the following: 'column', + * 'column as alias', ['alias' => 'column'] + * + * @return $this + */ + public function columns($columns) + { + if (! is_array($columns)) { + $columns = [$columns]; + } + + $this->columns = array_merge($this->columns ?: [], $columns); + + return $this; + } + + /** + * Get the FROM part of the query + * + * @return array|null + */ + public function getFrom() + { + return $this->from; + } + + /** + * Add a FROM part to the query + * + * Multiple calls to this method will not overwrite the previous set FROM part but append the tables to the FROM. + * + * Note that this method does NOT quote the tables you specify for the FROM. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the table names passed to this method. + * If you are using special table names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param string|Select|array $tables The table(s) to add to the FROM part. The items can be any mix of the + * following: ['table', 'table alias', 'alias' => 'table'] + * + * @return $this + */ + public function from($tables) + { + if (! is_array($tables)) { + $tables = [$tables]; + } + + $this->from = array_merge($this->from ?: [], $tables); + + return $this; + } + + /** + * Get the JOIN part(s) of the query + * + * @return array|null + */ + public function getJoin() + { + return $this->join; + } + + /** + * Add a INNER JOIN part to the query + * + * @param string|Select|array $table The table to be joined, can be any of the following: + * 'table' 'table alias' ['alias' => 'table'] + * @param string|ExpressionInterface|Select|array $condition The join condition, i.e. the ON part of the JOIN. + * Please see {@link WhereInterface::where()} + * for the supported formats and + * restrictions regarding quoting of the field names. + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function join($table, $condition, $operator = Sql::ALL) + { + $this->join[] = ['INNER', $table, $this->buildCondition($condition, $operator)]; + + return $this; + } + + /** + * Add a LEFT JOIN part to the query + * + * @param string|Select|array $table The table to be joined, can be any of the following: + * 'table' 'table alias' ['alias' => 'table'] + * @param string|ExpressionInterface|Select|array $condition The join condition, i.e. the ON part of the JOIN. + * Please see {@link WhereInterface::where()} + * for the supported formats and + * restrictions regarding quoting of the field names. + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function joinLeft($table, $condition, $operator = Sql::ALL) + { + $this->join[] = ['LEFT', $table, $this->buildCondition($condition, $operator)]; + + return $this; + } + + /** + * Add a RIGHT JOIN part to the query + * + * @param string|Select|array $table The table to be joined, can be any of the following: + * 'table' 'table alias' ['alias' => 'table'] + * @param string|ExpressionInterface|Select|array $condition The join condition, i.e. the ON part of the JOIN. + * Please see {@link WhereInterface::where()} + * for the supported formats and + * restrictions regarding quoting of the field names. + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function joinRight($table, $condition, $operator = Sql::ALL) + { + $this->join[] = ['RIGHT', $table, $this->buildCondition($condition, $operator)]; + + return $this; + } + + /** + * Get the GROUP BY part of the query + * + * @return array|null + */ + public function getGroupBy() + { + return $this->groupBy; + } + + /** + * Add a GROUP BY part to the query - either plain columns or expressions or scalar subqueries + * + * This method does NOT quote the columns you specify for the GROUP BY. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the field names passed to this method. + * If you are using special field names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * Note that this method does not override an already set GROUP BY part. Instead, multiple calls to this function + * add the specified GROUP BY part. + * + * @param string|ExpressionInterface|Select|array $groupBy + * + * @return $this + */ + public function groupBy($groupBy) + { + $this->groupBy = array_merge( + $this->groupBy === null ? [] : $this->groupBy, + is_array($groupBy) ? $groupBy : [$groupBy] + ); + + return $this; + } + + /** + * Get the HAVING part of the query + * + * @return array|null + */ + public function getHaving() + { + return $this->having; + } + + /** + * Add a HAVING part of the query + * + * This method lets you specify the HAVING part of the query using one of the two following supported formats: + * * String format, e.g. 'id = 1' + * * Array format, e.g. ['id' => 1, ...] + * + * This method does NOT quote the columns you specify for the HAVING. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the field names passed to this method. + * If you are using special field names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * Note that this method does not override an already set HAVING part. Instead, multiple calls to this function add + * the specified HAVING part using the AND operator. + * + * @param string|ExpressionInterface|Select|array $condition The HAVING condition + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function having($condition, $operator = Sql::ALL) + { + $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::ALL); + + return $this; + } + + /** + * Add a OR part to the HAVING part of the query + * + * Please see {@link having()} for the supported formats and restrictions regarding quoting of the field names. + * + * @param string|ExpressionInterface|Select|array $condition The HAVING condition + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function orHaving($condition, $operator = Sql::ALL) + { + $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::ANY); + + return $this; + } + + /** + * Add a AND NOT part to the HAVING part of the query + * + * Please see {@link having()} for the supported formats and restrictions regarding quoting of the field names. + * + * @param string|ExpressionInterface|Select|array $condition The HAVING condition + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function notHaving($condition, $operator = Sql::ALL) + { + $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::NOT_ALL); + + return $this; + } + + /** + * Add a OR NOT part to the HAVING part of the query + * + * Please see {@link having()} for the supported formats and restrictions regarding quoting of the field names. + * + * @param string|ExpressionInterface|Select|array $condition The HAVING condition + * @param string $operator The operator to combine multiple conditions with, + * if the condition is in the array format + * + * @return $this + */ + public function orNotHaving($condition, $operator = Sql::ALL) + { + $this->mergeCondition($this->having, $this->buildCondition($condition, $operator), Sql::NOT_ANY); + + return $this; + } + + /** + * Get the UNION parts of the query + * + * @return array|null + */ + public function getUnion() + { + return $this->union; + } + + /** + * Combine a query with UNION + * + * @param Select|string $query + * + * @return $this + */ + public function union($query) + { + $this->union[] = [$query, false]; + + return $this; + } + + /** + * Combine a query with UNION ALL + * + * @param Select|string $query + * + * @return $this + */ + public function unionAll($query) + { + $this->union[] = [$query, true]; + + return $this; + } + + /** + * Reset the DISTINCT part of the query + * + * @return $this + */ + public function resetDistinct() + { + $this->distinct = false; + + return $this; + } + + /** + * Reset the columns of the query + * + * @return $this + */ + public function resetColumns() + { + $this->columns = null; + + return $this; + } + + /** + * Reset the FROM part of the query + * + * @return $this + */ + public function resetFrom() + { + $this->from = null; + + return $this; + } + + /** + * Reset the JOIN parts of the query + * + * @return $this + */ + public function resetJoin() + { + $this->join = null; + + return $this; + } + + /** + * Reset the GROUP BY part of the query + * + * @return $this + */ + public function resetGroupBy() + { + $this->groupBy = null; + + return $this; + } + + /** + * Reset the HAVING part of the query + * + * @return $this + */ + public function resetHaving() + { + $this->having = null; + + return $this; + } + + /** + * Reset queries combined with UNION and UNION ALL + * + * @return $this + */ + public function resetUnion() + { + $this->union = null; + + return $this; + } + + /** + * Get the count query + * + * @return Select + */ + public function getCountQuery() + { + $countQuery = clone $this; + + $countQuery->orderBy = null; + $countQuery->limit = null; + $countQuery->offset = null; + + if (! empty($countQuery->groupBy) || $countQuery->getDistinct()) { + $countQuery = (new Select())->from(['s' => $countQuery]); + $countQuery->distinct(false); + } + + $countQuery->columns = ['cnt' => 'COUNT(*)']; + + return $countQuery; + } + + public function __clone() + { + $this->cloneCte(); + $this->cloneOrderBy(); + $this->cloneWhere(); + + if ($this->columns !== null) { + foreach ($this->columns as &$value) { + if ($value instanceof ExpressionInterface || $value instanceof Select) { + $value = clone $value; + } + } + unset($value); + } + + if ($this->from !== null) { + foreach ($this->from as &$from) { + if ($from instanceof Select) { + $from = clone $from; + } + } + unset($from); + } + + if ($this->join !== null) { + foreach ($this->join as &$join) { + if (is_array($join[1])) { + foreach ($join[1] as &$table) { + if ($table instanceof Select) { + $table = clone $table; + } + } + unset($table); + } elseif ($join[1] instanceof Select) { + $join[1] = clone $join[1]; + } + + $this->cloneCondition($join[2]); + } + unset($join); + } + + if ($this->groupBy !== null) { + foreach ($this->groupBy as &$value) { + if ($value instanceof ExpressionInterface || $value instanceof Select) { + $value = clone $value; + } + } + unset($value); + } + + if ($this->having !== null) { + $this->cloneCondition($this->having); + } + + if ($this->union !== null) { + foreach ($this->union as &$union) { + $union[0] = clone $union[0]; + } + unset($union); + } + } +} diff --git a/vendor/ipl/sql/src/Sql.php b/vendor/ipl/sql/src/Sql.php new file mode 100644 index 0000000..000a43a --- /dev/null +++ b/vendor/ipl/sql/src/Sql.php @@ -0,0 +1,70 @@ +<?php + +namespace ipl\Sql; + +/** + * The SQL helper provides a set of static methods for quoting and escaping identifiers to make their use safe in SQL + * queries or fragments + */ +class Sql +{ + /** + * SQL AND operator + */ + public const ALL = 'AND'; + + /** + * SQL OR operator + */ + public const ANY = 'OR'; + + /** + * SQL AND NOT operator + */ + public const NOT_ALL = 'AND NOT'; + + /** + * SQL OR NOT operator + */ + public const NOT_ANY = 'OR NOT'; + + /** + * Create and return a DELETE statement + * + * @return Delete + */ + public static function delete() + { + return new Delete(); + } + + /** + * Create and return a INSERT statement + * + * @return Insert + */ + public static function insert() + { + return new Insert(); + } + + /** + * Create and return a SELECT statement + * + * @return Select + */ + public static function select() + { + return new Select(); + } + + /** + * Create and return a UPDATE statement + * + * @return Update + */ + public static function update() + { + return new Update(); + } +} diff --git a/vendor/ipl/sql/src/Update.php b/vendor/ipl/sql/src/Update.php new file mode 100644 index 0000000..356a610 --- /dev/null +++ b/vendor/ipl/sql/src/Update.php @@ -0,0 +1,100 @@ +<?php + +namespace ipl\Sql; + +use InvalidArgumentException; + +use function ipl\Stdlib\arrayval; + +/** + * SQL UPDATE query + */ +class Update implements CommonTableExpressionInterface, WhereInterface +{ + use CommonTableExpression; + use Where; + + /** @var array|null The table for the UPDATE query */ + protected $table; + + /** @var array|null The columns to update in terms of column-value pairs */ + protected $set = []; + + /** + * Get the table for the UPDATE query + * + * @return array|null + */ + public function getTable() + { + return $this->table; + } + + /** + * Set the table for the UPDATE query + * + * Note that this method does NOT quote the table you specify for the UPDATE. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the table names passed to this method. + * If you are using special table names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param string|array $table The table to update. The table specification must be in one of the + * following formats: 'table', 'table alias', ['alias' => 'table'] + * + * @return $this + */ + public function table($table) + { + $this->table = is_array($table) ? $table : [$table]; + + return $this; + } + + /** + * Get the columns to update in terms of column-value pairs + * + * @return array|null + */ + public function getSet() + { + return $this->set; + } + + /** + * Set the columns to update in terms of column-value pairs + * + * Values may either be plain or expressions or scalar subqueries. + * + * Note that this method does NOT quote the columns you specify for the UPDATE. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the column names passed to this method. + * If you are using special column names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * @param iterable $set Associative set of column-value pairs + * + * @return $this + * + * @throws InvalidArgumentException If set type is invalid + */ + public function set($set) + { + $this->set = arrayval($set); + + return $this; + } + + public function __clone() + { + $this->cloneCte(); + $this->cloneWhere(); + + foreach ($this->set as &$value) { + if ($value instanceof ExpressionInterface || $value instanceof Select) { + $value = clone $value; + } + } + unset($value); + } +} diff --git a/vendor/ipl/sql/src/Where.php b/vendor/ipl/sql/src/Where.php new file mode 100644 index 0000000..f862846 --- /dev/null +++ b/vendor/ipl/sql/src/Where.php @@ -0,0 +1,158 @@ +<?php + +namespace ipl\Sql; + +/** + * Implementation for the {@link WhereInterface} + */ +trait Where +{ + /** @var array|null Internal representation for the WHERE part of the query */ + protected $where; + + public function getWhere() + { + return $this->where; + } + + public function where($condition, ...$args) + { + list($condition, $operator) = $this->prepareConditionArguments($condition, $args); + $this->mergeCondition($this->where, $this->buildCondition($condition, $operator), Sql::ALL); + + return $this; + } + + public function orWhere($condition, ...$args) + { + list($condition, $operator) = $this->prepareConditionArguments($condition, $args); + $this->mergeCondition($this->where, $this->buildCondition($condition, $operator), Sql::ANY); + + return $this; + } + + public function notWhere($condition, ...$args) + { + list($condition, $operator) = $this->prepareConditionArguments($condition, $args); + $this->mergeCondition($this->where, $this->buildCondition($condition, $operator), Sql::NOT_ALL); + + return $this; + } + + public function orNotWhere($condition, ...$args) + { + list($condition, $operator) = $this->prepareConditionArguments($condition, $args); + $this->mergeCondition($this->where, $this->buildCondition($condition, $operator), Sql::NOT_ANY); + + return $this; + } + + public function resetWhere() + { + $this->where = null; + + return $this; + } + + /** + * Make $condition an array and build an array like this: [$operator, [$condition]] + * + * If $condition is empty, replace it with a boolean constant depending on the operator. + * + * @param string|array $condition + * @param string $operator + * + * @return array + */ + protected function buildCondition($condition, $operator) + { + if (is_array($condition)) { + if (empty($condition)) { + $condition = [$operator === Sql::ALL ? '1' : '0']; + } elseif (in_array(reset($condition), [Sql::ALL, Sql::ANY, Sql::NOT_ALL, Sql::NOT_ANY], true)) { + return $condition; + } + } else { + $condition = [$condition]; + } + + return [$operator, $condition]; + } + + /** + * Merge the given condition with ours via the given operator + * + * @param mixed $base Our condition + * @param array $condition As returned by {@link buildCondition()} + * @param string $operator + */ + protected function mergeCondition(&$base, array $condition, $operator) + { + if ($base === null) { + $base = [$operator, [$condition]]; + } else { + if ($base[0] === $operator) { + $base[1][] = $condition; + } elseif ($operator === Sql::NOT_ALL) { + $base = [Sql::ALL, [$base, [$operator, [$condition]]]]; + } elseif ($operator === Sql::NOT_ANY) { + $base = [Sql::ANY, [$base, [$operator, [$condition]]]]; + } else { + $base = [$operator, [$base, $condition]]; + } + } + } + + /** + * Prepare condition arguments from the different supported where styles + * + * @param mixed $condition + * @param array $args + * + * @return array + */ + protected function prepareConditionArguments($condition, array $args) + { + // Default operator + $operator = Sql::ALL; + + if (! is_array($condition) && ! empty($args)) { + // Variadic + $condition = [(string) $condition => $args]; + } else { + // Array or string format + $operator = array_shift($args) ?: $operator; + } + + return [$condition, $operator]; + } + + /** + * Clone the properties provided by this trait + * + * Shall be called by using classes in their __clone() + */ + protected function cloneWhere() + { + if ($this->where !== null) { + $this->cloneCondition($this->where); + } + } + + /** + * Clone a condition in-place + * + * @param array $condition As returned by {@link buildCondition()} + */ + protected function cloneCondition(array &$condition) + { + foreach ($condition as &$subCondition) { + if (is_array($subCondition)) { + $this->cloneCondition($subCondition); + } elseif ($subCondition instanceof ExpressionInterface || $subCondition instanceof Select) { + $subCondition = clone $subCondition; + } + } + unset($subCondition); + } +} diff --git a/vendor/ipl/sql/src/WhereInterface.php b/vendor/ipl/sql/src/WhereInterface.php new file mode 100644 index 0000000..e724465 --- /dev/null +++ b/vendor/ipl/sql/src/WhereInterface.php @@ -0,0 +1,84 @@ +<?php + +namespace ipl\Sql; + +/** + * Interface for the WHERE part of a query + */ +interface WhereInterface +{ + /** + * Get the WHERE part of the query + * + * @return array|null + */ + public function getWhere(); + + /** + * Add a WHERE part of the query + * + * This method lets you specify the WHERE part of the query using one of the two following supported formats: + * * String format, e.g. 'id = 1', i.e. `where(string $condition [, mixed ...$args])` + * * Array format, e.g. ['id = ?' => 1, ...], i.e. `where(array $condition [, string $operator])` + * + * This method does NOT quote the columns you specify for the WHERE. + * If you allow user input here, you must protected yourself against SQL injection using + * {@link Connection::quoteIdentifier()} for the field names passed to this method. + * If you are using special field names, e.g. reserved keywords for your DBMS, you are required to use + * {@link Connection::quoteIdentifier()} as well. + * + * Note that this method does not override an already set WHERE part. Instead, multiple calls to this function add + * the specified WHERE part using the AND operator. + * + * @param string|ExpressionInterface|Select|array $condition The WHERE condition + * @param mixed $args If condition is a string, parameter values for placeholders in the condition can be passed. + * If condition is an array, the only argument that is allowed is the operator to use to combine + * these conditions. By default, this operator is {@link Sql::ALL} (AND) + * + * @return $this + */ + public function where($condition, ...$args); + + /** + * Add a OR part to the WHERE part of the query + * + * Please see {@link where()} for the supported formats and restrictions regarding quoting of the field names. + * + * @param string|ExpressionInterface|Select|array $condition The WHERE condition + * @param mixed ...$args Please see {@link where()} for details + * + * @return $this + */ + public function orWhere($condition, ...$args); + + /** + * Add a AND NOT part to the WHERE part of the query + * + * Please see {@link where()} for the supported formats and restrictions regarding quoting of the field names. + * + * @param string|ExpressionInterface|Select|array $condition The WHERE condition + * @param mixed ...$args Please see {@link where()} for details + * + * @return $this + */ + public function notWhere($condition, ...$args); + + /** + * Add a OR NOT part to the WHERE part of the query + * + * Please see {@link where()} for the supported formats and restrictions regarding quoting of the field names. + * + * @param string|ExpressionInterface|Select|array $condition The WHERE condition + * @param mixed ...$args Please see {@link where()} for details + * + * @return $this + */ + public function orNotWhere($condition, ...$args); + + /** + * Reset the WHERE part of the query + * + * @return $this + */ + public function resetWhere(); +} |