QueryBuilder::buildOrderByAndLimit() - Code Metrics - Inspection of "Fixes #14859: Fixed OCI DB `defaultSchema` failure..." - rob006/yii2-dev - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — master ( ecf3ef...78a151 )

by Robert

created 2018-02-11 18:58 UTC

QueryBuilder::buildOrderByAndLimit() B

↳ Parent: QueryBuilder

Complexity

Conditions	5
Paths	4

Size

Total Lines	13
Code Lines	7

Duplication

Lines	0
Ratio	0 %

Code Coverage

Tests	0
CRAP Score	30

Importance

Changes

Metric	Value
dl	0
loc	13
ccs	0
cts	11
cp	0
rs	8.8571
c	0
b	0
f	0
cc	5
eloc	7
nc	4
nop	4
crap	30

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace yii\db\mssql;

use yii\base\InvalidArgumentException;
use yii\db\Constraint;
use yii\db\Expression;

/**
 * QueryBuilder is the query builder for MS SQL Server databases (version 2008 and above).
 *
 * @author Timur Ruziev <[email protected]>
 * @since 2.0
 */
class QueryBuilder extends \yii\db\QueryBuilder
{
    /**
     * @var array mapping from abstract column types (keys) to physical column types (values).
     */
    public $typeMap = [
        Schema::TYPE_PK => 'int IDENTITY PRIMARY KEY',
        Schema::TYPE_UPK => 'int IDENTITY PRIMARY KEY',
        Schema::TYPE_BIGPK => 'bigint IDENTITY PRIMARY KEY',
        Schema::TYPE_UBIGPK => 'bigint IDENTITY PRIMARY KEY',
        Schema::TYPE_CHAR => 'nchar(1)',
        Schema::TYPE_STRING => 'nvarchar(255)',
        Schema::TYPE_TEXT => 'nvarchar(max)',
        Schema::TYPE_SMALLINT => 'smallint',
        Schema::TYPE_INTEGER => 'int',
        Schema::TYPE_BIGINT => 'bigint',
        Schema::TYPE_FLOAT => 'float',
        Schema::TYPE_DOUBLE => 'float',
        Schema::TYPE_DECIMAL => 'decimal(18,0)',
        Schema::TYPE_DATETIME => 'datetime',
        Schema::TYPE_TIMESTAMP => 'datetime',
        Schema::TYPE_TIME => 'time',
        Schema::TYPE_DATE => 'date',
        Schema::TYPE_BINARY => 'varbinary(max)',
        Schema::TYPE_BOOLEAN => 'bit',
        Schema::TYPE_MONEY => 'decimal(19,4)',
    ];

    /**
     * {@inheritdoc}
     */
    protected function defaultExpressionBuilders()
    {
        return array_merge(parent::defaultExpressionBuilders(), [
            'yii\db\conditions\InCondition' => 'yii\db\mssql\conditions\InConditionBuilder',
            'yii\db\conditions\LikeCondition' => 'yii\db\mssql\conditions\LikeConditionBuilder',
        ]);
    }

    /**
     * {@inheritdoc}
     */
    public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        if (!$this->hasOffset($offset) && !$this->hasLimit($limit)) {
            $orderBy = $this->buildOrderBy($orderBy);
            return $orderBy === '' ? $sql : $sql . $this->separator . $orderBy;
        }

        if (version_compare($this->db->getSchema()->getServerVersion(), '11', '<')) {
            return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
        }

        return $this->newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
    }

    /**
     * Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2012 or newer.
     * @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
     * @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
     * @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
     * @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
     * @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
     */
    protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        $orderBy = $this->buildOrderBy($orderBy);
        if ($orderBy === '') {
            // ORDER BY clause is required when FETCH and OFFSET are in the SQL
            $orderBy = 'ORDER BY (SELECT NULL)';
        }
        $sql .= $this->separator . $orderBy;

        // http://technet.microsoft.com/en-us/library/gg699618.aspx
        $offset = $this->hasOffset($offset) ? $offset : '0';
        $sql .= $this->separator . "OFFSET $offset ROWS";
        if ($this->hasLimit($limit)) {
            $sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY";
        }

        return $sql;
    }

    /**
     * Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2005 to 2008.
     * @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
     * @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
     * @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
     * @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
     * @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
     */
    protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
    {
        $orderBy = $this->buildOrderBy($orderBy);
        if ($orderBy === '') {
            // ROW_NUMBER() requires an ORDER BY clause
            $orderBy = 'ORDER BY (SELECT NULL)';
        }

        $sql = preg_replace('/^([\s(])*SELECT(\s+DISTINCT)?(?!\s*TOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);

        if ($this->hasLimit($limit)) {
            $sql = "SELECT TOP $limit * FROM ($sql) sub";
        } else {
            $sql = "SELECT * FROM ($sql) sub";
        }
        if ($this->hasOffset($offset)) {
            $sql .= $this->separator . "WHERE rowNum > $offset";
        }

        return $sql;
    }

    /**
     * Builds a SQL statement for renaming a DB table.
     * @param string $oldName the table to be renamed. The name will be properly quoted by the method.
     * @param string $newName the new table name. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB table.
     */
    public function renameTable($oldName, $newName)
    {
        return 'sp_rename ' . $this->db->quoteTableName($oldName) . ', ' . $this->db->quoteTableName($newName);
    }

    /**
     * Builds a SQL statement for renaming a column.
     * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
     * @param string $oldName the old name of the column. The name will be properly quoted by the method.
     * @param string $newName the new name of the column. The name will be properly quoted by the method.
     * @return string the SQL statement for renaming a DB column.
     */
    public function renameColumn($table, $oldName, $newName)
    {
        $table = $this->db->quoteTableName($table);
        $oldName = $this->db->quoteColumnName($oldName);
        $newName = $this->db->quoteColumnName($newName);
        return "sp_rename '{$table}.{$oldName}', {$newName}, 'COLUMN'";
    }

    /**
     * Builds a SQL statement for changing the definition of a column.
     * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
     * @param string $column the name of the column to be changed. The name will be properly quoted by the method.
     * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
     * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
     * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
     * @return string the SQL statement for changing the definition of a column.
     */
    public function alterColumn($table, $column, $type)
    {
        $type = $this->getColumnType($type);
        $sql = 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
            . $this->db->quoteColumnName($column) . ' '
            . $this->getColumnType($type);

        return $sql;
    }

    /**
     * @inheritDoc
     */
    public function addDefaultValue($name, $table, $column, $value)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
            . $this->db->quoteColumnName($name) . ' DEFAULT ' . $this->db->quoteValue($value) . ' FOR '
            . $this->db->quoteColumnName($column);
    }

    /**
     * @inheritDoc
     */
    public function dropDefaultValue($name, $table)
    {
        return 'ALTER TABLE ' . $this->db->quoteTableName($table)
            . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
    }

    /**
     * Creates a SQL statement for resetting the sequence value of a table's primary key.
     * The sequence will be reset such that the primary key of the next new row inserted
     * will have the specified value or 1.
     * @param string $tableName the name of the table whose primary key sequence will be reset
     * @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
     * the next new row's primary key will have a value 1.
     * @return string the SQL statement for resetting sequence
     * @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table.
     */
    public function resetSequence($tableName, $value = null)
    {
        $table = $this->db->getTableSchema($tableName);
        if ($table !== null && $table->sequenceName !== null) {
            $tableName = $this->db->quoteTableName($tableName);
            if ($value === null) {
                $key = $this->db->quoteColumnName(reset($table->primaryKey));

                $value = "(SELECT COALESCE(MAX({$key}),0) FROM {$tableName})+1";
            } else {
                $value = (int) $value;
            }

            return "DBCC CHECKIDENT ('{$tableName}', RESEED, {$value})";
        } elseif ($table === null) {
            throw new InvalidArgumentException("Table not found: $tableName");
        }

        throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
    }

    /**
     * Builds a SQL statement for enabling or disabling integrity check.
     * @param bool $check whether to turn on or off the integrity check.
     * @param string $schema the schema of the tables.
     * @param string $table the table name.
     * @return string the SQL statement for checking integrity
     */
    public function checkIntegrity($check = true, $schema = '', $table = '')
    {
        $enable = $check ? 'CHECK' : 'NOCHECK';
        $schema = $schema ?: $this->db->getSchema()->defaultSchema;
        $tableNames = $this->db->getTableSchema($table) ? [$table] : $this->db->getSchema()->getTableNames($schema);
        $viewNames = $this->db->getSchema()->getViewNames($schema);
class ParentClass {
    private $data = array();

    public function __call($method, array $args) {
        if (0 === strpos($method, 'get')) {
            return $this->data[strtolower(substr($method, 3))];
        }

        throw new \LogicException(sprintf('Unsupported method: %s', $method));
    }
}

/**
 * If this class knows which fields exist, you can specify the methods here:
 *
 * @method string getName()
 */
class SomeClass extends ParentClass { }
        $tableNames = array_diff($tableNames, $viewNames);
        $command = '';

        foreach ($tableNames as $tableName) {
            $tableName = $this->db->quoteTableName("{$schema}.{$tableName}");
            $command .= "ALTER TABLE $tableName $enable CONSTRAINT ALL; ";
        }

        return $command;
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function addCommentOnColumn($table, $column, $comment)
    {
        return "sp_updateextendedproperty @name = N'MS_Description', @value = {$this->db->quoteValue($comment)}, @level1type = N'Table',  @level1name = {$this->db->quoteTableName($table)}, @level2type = N'Column', @level2name = {$this->db->quoteColumnName($column)}";
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function addCommentOnTable($table, $comment)
    {
        return "sp_updateextendedproperty @name = N'MS_Description', @value = {$this->db->quoteValue($comment)}, @level1type = N'Table',  @level1name = {$this->db->quoteTableName($table)}";
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function dropCommentFromColumn($table, $column)
    {
        return "sp_dropextendedproperty @name = N'MS_Description', @level1type = N'Table',  @level1name = {$this->db->quoteTableName($table)}, @level2type = N'Column', @level2name = {$this->db->quoteColumnName($column)}";
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function dropCommentFromTable($table)
    {
        return "sp_dropextendedproperty @name = N'MS_Description', @level1type = N'Table',  @level1name = {$this->db->quoteTableName($table)}";
    }

    /**
     * Returns an array of column names given model name.
     *
     * @param string $modelClass name of the model class
     * @return array|null array of column names
     */
    protected function getAllColumnNames($modelClass = null)
    {
        if (!$modelClass) {
''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
            return null;
        }
        /* @var $modelClass \yii\db\ActiveRecord */
        $schema = $modelClass::getTableSchema();
        return array_keys($schema->columns);
    }

    /**
     * @return bool whether the version of the MSSQL being used is older than 2012.
     * @throws \yii\base\InvalidConfigException
     * @throws \yii\db\Exception
     * @deprecated 2.0.14 Use [[Schema::getServerVersion]] with [[\version_compare()]].
     */
    protected function isOldMssql()
    {
        return version_compare($this->db->getSchema()->getServerVersion(), '11', '<');
    }

    /**
     * {@inheritdoc}
     * @since 2.0.8
     */
    public function selectExists($rawSql)
    {
        return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END';
    }

    /**
     * Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
     * @param string $table the table that data will be saved into.
     * @param array $columns the column data (name => value) to be saved into the table.
     * @return array normalized columns
     */
    private function normalizeTableRowData($table, $columns, &$params)

    {
        if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
            foreach ($columns as $name => $value) {
                // @see https://github.com/yiisoft/yii2/issues/12599
                if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && is_string($value)) {
                    $exParams = [];
                    $phName = $this->bindParam($value, $exParams);
                    $columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", $exParams);
                }
            }
        }

        return $columns;
    }

    /**
     * {@inheritdoc}
     */
    public function insert($table, $columns, &$params)
    {
        return parent::insert($table, $this->normalizeTableRowData($table, $columns, $params), $params);
    }

    /**
     * @inheritdoc
     * @see https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
     * @see http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
     */
    public function upsert($table, $insertColumns, $updateColumns, &$params)
    {
        /** @var Constraint[] $constraints */
        list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
        if (empty($uniqueNames)) {
            return $this->insert($table, $insertColumns, $params);
        }

        $onCondition = ['or'];
        $quotedTableName = $this->db->quoteTableName($table);
        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];
            foreach ($constraint->columnNames as $name) {
$collection = json_decode($data, true);
if ( ! is_array($collection)) {
    throw new \RuntimeException('$collection must be an array.');
}

foreach ($collection as $item) { /** ... */ }
                $quotedName = $this->db->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
            }
            $onCondition[] = $constraintCondition;
        }
        $on = $this->buildCondition($onCondition, $params);
        list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
        $mergeSql = 'MERGE ' . $this->db->quoteTableName($table) . ' WITH (HOLDLOCK) '
            . 'USING (' . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') '
            . 'ON ' . $on;
        $insertValues = [];
        foreach ($insertNames as $name) {
            $quotedName = $this->db->quoteColumnName($name);
            if (strrpos($quotedName, '.') === false) {
                $quotedName = '[EXCLUDED].' . $quotedName;
            }
            $insertValues[] = $quotedName;
        }
        $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
            . ' VALUES (' . implode(', ', $insertValues) . ')';
        if ($updateColumns === false) {
            return "$mergeSql WHEN NOT MATCHED THEN $insertSql;";
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '[EXCLUDED].' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);
        return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;";
    }

    /**
     * {@inheritdoc}
     */
    public function update($table, $columns, $condition, &$params)
    {
        return parent::update($table, $this->normalizeTableRowData($table, $columns, $params), $condition, $params);
    }
}


1			<?php
2			/**
3			* @link http://www.yiiframework.com/
4			* @copyright Copyright (c) 2008 Yii Software LLC
5			* @license http://www.yiiframework.com/license/
6			*/
7
8			namespace yii\db\mssql;
9
10			use yii\base\InvalidArgumentException;
11			use yii\db\Constraint;
12			use yii\db\Expression;
13
14			/**
15			* QueryBuilder is the query builder for MS SQL Server databases (version 2008 and above).
16			*
17			* @author Timur Ruziev <[email protected]>
18			* @since 2.0
19			*/
20			class QueryBuilder extends \yii\db\QueryBuilder
21			{
22			/**
23			* @var array mapping from abstract column types (keys) to physical column types (values).
24			*/
25			public $typeMap = [
26			Schema::TYPE_PK => 'int IDENTITY PRIMARY KEY',
27			Schema::TYPE_UPK => 'int IDENTITY PRIMARY KEY',
28			Schema::TYPE_BIGPK => 'bigint IDENTITY PRIMARY KEY',
29			Schema::TYPE_UBIGPK => 'bigint IDENTITY PRIMARY KEY',
30			Schema::TYPE_CHAR => 'nchar(1)',
31			Schema::TYPE_STRING => 'nvarchar(255)',
32			Schema::TYPE_TEXT => 'nvarchar(max)',
33			Schema::TYPE_SMALLINT => 'smallint',
34			Schema::TYPE_INTEGER => 'int',
35			Schema::TYPE_BIGINT => 'bigint',
36			Schema::TYPE_FLOAT => 'float',
37			Schema::TYPE_DOUBLE => 'float',
38			Schema::TYPE_DECIMAL => 'decimal(18,0)',
39			Schema::TYPE_DATETIME => 'datetime',
40			Schema::TYPE_TIMESTAMP => 'datetime',
41			Schema::TYPE_TIME => 'time',
42			Schema::TYPE_DATE => 'date',
43			Schema::TYPE_BINARY => 'varbinary(max)',
44			Schema::TYPE_BOOLEAN => 'bit',
45			Schema::TYPE_MONEY => 'decimal(19,4)',
46			];
47
48			/**
49			* {@inheritdoc}
50			*/
51			protected function defaultExpressionBuilders()
52			{
53			return array_merge(parent::defaultExpressionBuilders(), [
54			'yii\db\conditions\InCondition' => 'yii\db\mssql\conditions\InConditionBuilder',
55			'yii\db\conditions\LikeCondition' => 'yii\db\mssql\conditions\LikeConditionBuilder',
56			]);
57			}
58
59			/**
60			* {@inheritdoc}
61			*/
62			public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
63			{
64			if (!$this->hasOffset($offset) && !$this->hasLimit($limit)) {
65			$orderBy = $this->buildOrderBy($orderBy);
66			return $orderBy === '' ? $sql : $sql . $this->separator . $orderBy;
67			}
68
69			if (version_compare($this->db->getSchema()->getServerVersion(), '11', '<')) {
70			return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
71			}
72
73			return $this->newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
74			}
75
76			/**
77			* Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2012 or newer.
78			* @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
79			* @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
80			* @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
81			* @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
82			* @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
83			*/
84			protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
85			{
86			$orderBy = $this->buildOrderBy($orderBy);
87			if ($orderBy === '') {
88			// ORDER BY clause is required when FETCH and OFFSET are in the SQL
89			$orderBy = 'ORDER BY (SELECT NULL)';
90			}
91			$sql .= $this->separator . $orderBy;
92
93			// http://technet.microsoft.com/en-us/library/gg699618.aspx
94			$offset = $this->hasOffset($offset) ? $offset : '0';
95			$sql .= $this->separator . "OFFSET $offset ROWS";
96			if ($this->hasLimit($limit)) {
97			$sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY";
98			}
99
100			return $sql;
101			}
102
103			/**
104			* Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2005 to 2008.
105			* @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
106			* @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
107			* @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
108			* @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
109			* @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
110			*/
111			protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
112			{
113			$orderBy = $this->buildOrderBy($orderBy);
114			if ($orderBy === '') {
115			// ROW_NUMBER() requires an ORDER BY clause
116			$orderBy = 'ORDER BY (SELECT NULL)';
117			}
118
119			$sql = preg_replace('/^([\s(])SELECT(\s+DISTINCT)?(?!\sTOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);
120
121			if ($this->hasLimit($limit)) {
122			$sql = "SELECT TOP $limit * FROM ($sql) sub";
123			} else {
124			$sql = "SELECT * FROM ($sql) sub";
125			}
126			if ($this->hasOffset($offset)) {
127			$sql .= $this->separator . "WHERE rowNum > $offset";
128			}
129
130			return $sql;
131			}
132
133			/**
134			* Builds a SQL statement for renaming a DB table.
135			* @param string $oldName the table to be renamed. The name will be properly quoted by the method.
136			* @param string $newName the new table name. The name will be properly quoted by the method.
137			* @return string the SQL statement for renaming a DB table.
138			*/
139			public function renameTable($oldName, $newName)
140			{
141			return 'sp_rename ' . $this->db->quoteTableName($oldName) . ', ' . $this->db->quoteTableName($newName);
142			}
143
144			/**
145			* Builds a SQL statement for renaming a column.
146			* @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
147			* @param string $oldName the old name of the column. The name will be properly quoted by the method.
148			* @param string $newName the new name of the column. The name will be properly quoted by the method.
149			* @return string the SQL statement for renaming a DB column.
150			*/
151			public function renameColumn($table, $oldName, $newName)
152			{
153			$table = $this->db->quoteTableName($table);
154			$oldName = $this->db->quoteColumnName($oldName);
155			$newName = $this->db->quoteColumnName($newName);
156			return "sp_rename '{$table}.{$oldName}', {$newName}, 'COLUMN'";
157			}
158
159			/**
160			* Builds a SQL statement for changing the definition of a column.
161			* @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
162			* @param string $column the name of the column to be changed. The name will be properly quoted by the method.
163			* @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
164			* into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
165			* For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
166			* @return string the SQL statement for changing the definition of a column.
167			*/
168			public function alterColumn($table, $column, $type)
169			{
170			$type = $this->getColumnType($type);
171			$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
172			. $this->db->quoteColumnName($column) . ' '
173			. $this->getColumnType($type);
174
175			return $sql;
176			}
177
178			/**
179			* @inheritDoc
180			*/
181			public function addDefaultValue($name, $table, $column, $value)
182			{
183			return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT '
184			. $this->db->quoteColumnName($name) . ' DEFAULT ' . $this->db->quoteValue($value) . ' FOR '
185			. $this->db->quoteColumnName($column);
186			}
187
188			/**
189			* @inheritDoc
190			*/
191			public function dropDefaultValue($name, $table)
192			{
193			return 'ALTER TABLE ' . $this->db->quoteTableName($table)
194			. ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name);
195			}
196
197			/**
198			* Creates a SQL statement for resetting the sequence value of a table's primary key.
199			* The sequence will be reset such that the primary key of the next new row inserted
200			* will have the specified value or 1.
201			* @param string $tableName the name of the table whose primary key sequence will be reset
202			* @param mixed $value the value for the primary key of the next new row inserted. If this is not set,
203			* the next new row's primary key will have a value 1.
204			* @return string the SQL statement for resetting sequence
205			* @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table.
206			*/
207			public function resetSequence($tableName, $value = null)
208			{
209			$table = $this->db->getTableSchema($tableName);
210			if ($table !== null && $table->sequenceName !== null) {
211			$tableName = $this->db->quoteTableName($tableName);
212			if ($value === null) {
213			$key = $this->db->quoteColumnName(reset($table->primaryKey));
			0 ignored issues – show Security Bug introduced 2017-03-01 11:23 UTC by Report Bug Copy Issue Report It seems like `reset($table->primaryKey)` targeting `reset()` can also be of type `false`; however, `yii\db\Connection::quoteColumnName()` does only seem to accept `string`, did you maybe forget to handle an error condition? Loading history...
214			$value = "(SELECT COALESCE(MAX({$key}),0) FROM {$tableName})+1";
215			} else {
216			$value = (int) $value;
217			}
218
219			return "DBCC CHECKIDENT ('{$tableName}', RESEED, {$value})";
220			} elseif ($table === null) {
221			throw new InvalidArgumentException("Table not found: $tableName");
222			}
223
224			throw new InvalidArgumentException("There is not sequence associated with table '$tableName'.");
225			}
226
227			/**
228			* Builds a SQL statement for enabling or disabling integrity check.
229			* @param bool $check whether to turn on or off the integrity check.
230			* @param string $schema the schema of the tables.
231			* @param string $table the table name.
232			* @return string the SQL statement for checking integrity
233			*/
234			public function checkIntegrity($check = true, $schema = '', $table = '')
235			{
236			$enable = $check ? 'CHECK' : 'NOCHECK';
237			$schema = $schema ?: $this->db->getSchema()->defaultSchema;
238			$tableNames = $this->db->getTableSchema($table) ? [$table] : $this->db->getSchema()->getTableNames($schema);
239			$viewNames = $this->db->getSchema()->getViewNames($schema);
			0 ignored issues – show Documentation Bug introduced 2017-03-01 11:23 UTC by Report Bug Copy Issue Report The method `getViewNames` does not exist on `object<yii\db\Schema>`? Since you implemented `__call`, maybe consider adding a @method annotation. If you implement `__call` and you know which methods are available, you can improve IDE auto-completion and static analysis by adding a @method annotation to the class. This is often the case, when `__call` is implemented by a parent class and only the child class knows which methods exist: class ParentClass { private $data = array(); public function __call($method, array $args) { if (0 === strpos($method, 'get')) { return $this->data[strtolower(substr($method, 3))]; } throw new \LogicException(sprintf('Unsupported method: %s', $method)); } } /** * If this class knows which fields exist, you can specify the methods here: * * @method string getName() */ class SomeClass extends ParentClass { } Loading history...
240			$tableNames = array_diff($tableNames, $viewNames);
241			$command = '';
242
243			foreach ($tableNames as $tableName) {
244			$tableName = $this->db->quoteTableName("{$schema}.{$tableName}");
245			$command .= "ALTER TABLE $tableName $enable CONSTRAINT ALL; ";
246			}
247
248			return $command;
249			}
250
251			/**
252			* {@inheritdoc}
253			* @since 2.0.8
254			*/
255			public function addCommentOnColumn($table, $column, $comment)
256			{
257			return "sp_updateextendedproperty @name = N'MS_Description', @value = {$this->db->quoteValue($comment)}, @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}, @level2type = N'Column', @level2name = {$this->db->quoteColumnName($column)}";
258			}
259
260			/**
261			* {@inheritdoc}
262			* @since 2.0.8
263			*/
264			public function addCommentOnTable($table, $comment)
265			{
266			return "sp_updateextendedproperty @name = N'MS_Description', @value = {$this->db->quoteValue($comment)}, @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}";
267			}
268
269			/**
270			* {@inheritdoc}
271			* @since 2.0.8
272			*/
273			public function dropCommentFromColumn($table, $column)
274			{
275			return "sp_dropextendedproperty @name = N'MS_Description', @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}, @level2type = N'Column', @level2name = {$this->db->quoteColumnName($column)}";
276			}
277
278			/**
279			* {@inheritdoc}
280			* @since 2.0.8
281			*/
282			public function dropCommentFromTable($table)
283			{
284			return "sp_dropextendedproperty @name = N'MS_Description', @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}";
285			}
286
287			/**
288			* Returns an array of column names given model name.
289			*
290			* @param string $modelClass name of the model class
291			* @return array\|null array of column names
292			*/
293			protected function getAllColumnNames($modelClass = null)
294			{
295			if (!$modelClass) {
			0 ignored issues – show Bug Best Practice introduced 2016-09-02 20:20 UTC by Report Bug Copy Issue Report The expression `$modelClass` of type `string\|null` is loosely compared to `false`; this is ambiguous if the string can be empty. You might want to explicitly use `=== null` instead. In PHP, under loose comparison (like `==`, or `!=`, or `switch` conditions), values of different types might be equal. For `string` values, the empty string `''` is a special case, in particular the following results might be unexpected: '' == false // true '' == null // true 'ab' == false // false 'ab' == null // false // It is often better to use strict comparison '' === false // false '' === null // false Loading history...
296			return null;
297			}
298			/* @var $modelClass \yii\db\ActiveRecord */
299			$schema = $modelClass::getTableSchema();
300			return array_keys($schema->columns);
301			}
302
303			/**
304			* @return bool whether the version of the MSSQL being used is older than 2012.
305			* @throws \yii\base\InvalidConfigException
306			* @throws \yii\db\Exception
307			* @deprecated 2.0.14 Use [[Schema::getServerVersion]] with [[\version_compare()]].
308			*/
309			protected function isOldMssql()
310			{
311			return version_compare($this->db->getSchema()->getServerVersion(), '11', '<');
312			}
313
314			/**
315			* {@inheritdoc}
316			* @since 2.0.8
317			*/
318			public function selectExists($rawSql)
319			{
320			return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END';
321			}
322
323			/**
324			* Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
325			* @param string $table the table that data will be saved into.
326			* @param array $columns the column data (name => value) to be saved into the table.
327			* @return array normalized columns
328			*/
329			private function normalizeTableRowData($table, $columns, &$params)
			0 ignored issues – show Unused Code introduced 2018-02-08 12:34 UTC by Report Bug Copy Issue Report The parameter `$params` is not used and could be removed. This check looks from parameters that have been defined for a function or method, but which are not used in the method body. Loading history...
330			{
331			if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) {
332			$columnSchemas = $tableSchema->columns;
333			foreach ($columns as $name => $value) {
334			// @see https://github.com/yiisoft/yii2/issues/12599
335			if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && is_string($value)) {
336			$exParams = [];
337			$phName = $this->bindParam($value, $exParams);
338			$columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", $exParams);
339			}
340			}
341			}
342
343			return $columns;
344			}
345
346			/**
347			* {@inheritdoc}
348			*/
349			public function insert($table, $columns, &$params)
350			{
351			return parent::insert($table, $this->normalizeTableRowData($table, $columns, $params), $params);
352			}
353
354			/**
355			* @inheritdoc
356			* @see https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql
357			* @see http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx
358			*/
359			public function upsert($table, $insertColumns, $updateColumns, &$params)
360			{
361			/** @var Constraint[] $constraints */
362			list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
363			if (empty($uniqueNames)) {
364			return $this->insert($table, $insertColumns, $params);
365			}
366
367			$onCondition = ['or'];
368			$quotedTableName = $this->db->quoteTableName($table);
369			foreach ($constraints as $constraint) {
370			$constraintCondition = ['and'];
371			foreach ($constraint->columnNames as $name) {
			0 ignored issues – show Bug introduced 2018-02-11 19:09 UTC by Report Bug Copy Issue Report The expression `$constraint->columnNames` of type `array<integer,string>\|null` is not guaranteed to be traversable. How about adding an additional type check? There are different options of fixing this problem. If you want to be on the safe side, you can add an additional type-check: $collection = json_decode($data, true); if ( ! is_array($collection)) { throw new \RuntimeException('$collection must be an array.'); } foreach ($collection as $item) { /** ... / } If you are sure that the expression is traversable, you might want to add a doc comment cast* to improve IDE auto-completion and static analysis: /** @var array $collection / $collection = json_decode($data, true); foreach ($collection as $item) { /* .. / } Mark the issue as a false-positive*: Just hover the remove button, in the top-right corner of this issue for more options. Loading history...
372			$quotedName = $this->db->quoteColumnName($name);
373			$constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName";
374			}
375			$onCondition[] = $constraintCondition;
376			}
377			$on = $this->buildCondition($onCondition, $params);
378			list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
379			$mergeSql = 'MERGE ' . $this->db->quoteTableName($table) . ' WITH (HOLDLOCK) '
380			. 'USING (' . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNames) . ') '
381			. 'ON ' . $on;
382			$insertValues = [];
383			foreach ($insertNames as $name) {
384			$quotedName = $this->db->quoteColumnName($name);
385			if (strrpos($quotedName, '.') === false) {
386			$quotedName = '[EXCLUDED].' . $quotedName;
387			}
388			$insertValues[] = $quotedName;
389			}
390			$insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
391			. ' VALUES (' . implode(', ', $insertValues) . ')';
392			if ($updateColumns === false) {
393			return "$mergeSql WHEN NOT MATCHED THEN $insertSql;";
394			}
395
396			if ($updateColumns === true) {
397			$updateColumns = [];
398			foreach ($updateNames as $name) {
399			$quotedName = $this->db->quoteColumnName($name);
400			if (strrpos($quotedName, '.') === false) {
401			$quotedName = '[EXCLUDED].' . $quotedName;
402			}
403			$updateColumns[$name] = new Expression($quotedName);
404			}
405			}
406			list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
407			$updateSql = 'UPDATE SET ' . implode(', ', $updates);
408			return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;";
409			}
410
411			/**
412			* {@inheritdoc}
413			*/
414			public function update($table, $columns, $condition, &$params)
415			{
416			return parent::update($table, $this->normalizeTableRowData($table, $columns, $params), $condition, $params);
417			}
418			}
419

rob006 / yii2-dev

GitHub Access Token became invalid

Push — master ( ecf3ef...78a151 )

QueryBuilder::buildOrderByAndLimit() B

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like