/**
* {@inheritdoc}
*/
public function sqlStatement(QueryCompiler $compiler = null)
{
if (empty($compiler)) {
//We might need to throw an exception here in some cases
return parent::sqlStatement();
}
return $compiler->quote(parent::sqlStatement($compiler));
}
/**
* {@inheritdoc}
*/
public function prepareParameters($type, array $where = [], array $joins = [], array $having = [], array $columns = [])
{
if ($type == self::UPDATE_QUERY) {
//Where statement has pretty specific order
return array_merge($joins, $columns, $where);
}
return parent::prepareParameters($type, $where, $joins, $having, $columns);
}
/**
* Resolve operator value based on value value. ;)
*
* @param mixed $parameter
* @param string $operator
* @return string
*/
protected function prepareOperator($parameter, $operator)
{
if (!$parameter instanceof ParameterInterface) {
//Probably fragment
return $operator;
}
if ($parameter->getType() == \PDO::PARAM_NULL) {
switch ($operator) {
case '=':
return 'IS';
case '!=':
return 'IS NOT';
}
}
return parent::prepareOperator($parameter, $operator);
}
/**
* {@inheritdoc}
*
* Attention, limiting and ordering UNIONS will fail in SQL SERVER < 2012.
* For future upgrades: think about using top command.
*
* @link http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server
* @link http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server
*/
public function select(array $from, $distinct, array $columns, array $joins = [], array $where = [], array $having = [], array $groupBy = [], array $orderBy = [], $limit = 0, $offset = 0, array $unions = [])
{
if (empty($limit) && empty($offset) || $this->driver->getServerVersion() >= 12 && !empty($orderBy)) {
return call_user_func_array(['parent', 'select'], func_get_args());
}
if ($this->driver->getServerVersion() >= 12) {
$this->logger()->warning("You can't use query limiting without specifying ORDER BY statement, sql fallback used.");
} else {
$this->logger()->warning("You are using older version of SQLServer, " . "it has some limitation with query limiting and unions.");
}
if ($orderBy) {
$orderBy = 'ORDER BY ' . $this->orderBy($orderBy);
} else {
$orderBy = "ORDER BY (SELECT NULL)";
}
//Will be removed by QueryResult
$columns[] = new SQLFragment("ROW_NUMBER() OVER ({$orderBy}) AS " . $this->quote(QueryResult::ROW_NUMBER_COLUMN));
$selection = parent::select($from, $distinct, $columns, $joins, $where, $having, $groupBy, [], 0, 0, $unions);
return "SELECT * FROM (\n{$selection}\n) AS [selection_alias] " . $this->limit($limit, $offset, QueryResult::ROW_NUMBER_COLUMN);
}
/**
* {@inheritdoc}
*/
public function insert($table, array $columns, array $rowsets)
{
if (count($rowsets) == 1) {
return parent::insert($table, $columns, $rowsets);
}
//SQLite uses alternative syntax
$statement = [];
$statement[] = "INSERT INTO {$this->quote($table, true)} ({$this->columns($columns)})";
foreach ($rowsets as $rowset) {
if (count($statement) == 1) {
$selectColumns = [];
foreach ($columns as $column) {
$selectColumns[] = "? AS {$this->quote($column)}";
}
$statement[] = 'SELECT ' . join(', ', $selectColumns);
} else {
$statement[] = 'UNION SELECT ' . trim(str_repeat('?, ', count($columns)), ', ');
}
}
return join("\n", $statement);
}
/**
* {@inheritdoc}
*
* Attention, limiting and ordering UNIONS will fail in SQL SERVER < 2012.
* For future upgrades: think about using top command.
*
* @link http://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server
* @link http://stackoverflow.com/questions/971964/limit-10-20-in-sql-server
*/
public function compileSelect(array $fromTables, $distinct, array $columns, array $joinsStatement = [], array $whereTokens = [], array $havingTokens = [], array $grouping = [], array $ordering = [], $limit = 0, $offset = 0, array $unionTokens = [])
{
if (empty($limit) && empty($offset) || $this->driver->serverVersion() >= 12 && !empty($ordering)) {
//When no limits are specified we can use normal query syntax
return call_user_func_array(['parent', 'compileSelect'], func_get_args());
}
if ($this->driver->serverVersion() >= 12) {
$this->logger()->warning("You can't use query limiting without specifying ORDER BY statement, sql fallback used.");
} else {
$this->logger()->warning("You are using older version of SQLServer, " . "it has some limitation with query limiting and unions.");
}
if (!empty($ordering)) {
$ordering = "ORDER BY {$this->compileOrdering($ordering)}";
} else {
$ordering = "ORDER BY (SELECT NULL)";
}
//Will be removed by QueryResult
$columns[] = new Fragment("ROW_NUMBER() OVER ({$ordering}) AS {$this->quote(QueryResult::ROW_NUMBER_COLUMN)}");
//Let's compile MOST of our query :)
$selection = parent::compileSelect($fromTables, $distinct, $columns, $joinsStatement, $whereTokens, $havingTokens, $grouping, [], 0, 0, $unionTokens);
$limitStatement = $this->compileLimit($limit, $offset, QueryResult::ROW_NUMBER_COLUMN);
return "SELECT * FROM (\n{$selection}\n) AS [selection_alias] {$limitStatement}";
}
/**
* Query string associated with PDOStatement.
*
* @return string
*/
public function queryString()
{
return QueryCompiler::interpolate($this->statement->queryString, $this->parameters);
}
/**
* {@inheritdoc}
*/
public function compileInsert($table, array $columns, array $rowsets, $primaryKey = '')
{
return parent::compileInsert($table, $columns, $rowsets) . (!empty($primaryKey) ? ' RETURNING ' . $this->quote($primaryKey) : '');
}
/**
* Get interpolated (populated with parameters) SQL which will be run against database, please
* use this method for debugging purposes only.
*
* @return string
*/
public function queryString()
{
return $this->compiler->interpolate($this->sqlStatement(), $this->database->driver()->prepareParameters($this->getParameters()));
}