QueryBuilder::insert() - Code Metrics - Inspection of "Explicitly cast varbinary to work around MSSQL PDO..." - yiisoft/yii2 - Measure and Improve Code Quality continuously with Scrutinizer

Completed

Push — mssql-12599 ( cc56e9 )

by Alexander

created 2017-01-22 22:12 UTC

QueryBuilder::insert() A

↳ Parent: QueryBuilder

Complexity

Conditions	1
Paths	1

Size

Total Lines	4
Code Lines	2

Duplication

Lines	0
Ratio	0 %

Importance

Changes

Metric	Value
dl	0
loc	4
rs	10
c	0
b	0
f	0
cc	1
eloc	2
nc	1
nop	3

<?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\InvalidParamException;
use yii\base\NotSupportedException;
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',
        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
     */
    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 ($this->isOldMssql()) {
            return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
        } else {
            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;
    }

    /**
     * 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. Defaults to empty string, meaning the current or default schema.
     * @param string $table the table name. Defaults to empty string, meaning that no table will be changed.
     * @return string the SQL statement for checking integrity
     * @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
     */
    public function checkIntegrity($check = true, $schema = '', $table = '')
    {
        if ($schema !== '') {
            $table = "{$schema}.{$table}";
        }
        $table = $this->db->quoteTableName($table);
        if ($this->db->getTableSchema($table) === null) {
            throw new InvalidParamException("Table not found: $table");
        }
        $enable = $check ? 'CHECK' : 'NOCHECK';

        return "ALTER TABLE {$table} {$enable} CONSTRAINT ALL";
    }

    /**
     * @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 $model \yii\db\ActiveRecord */
        $model = new $modelClass;
        $schema = $model->getTableSchema();
        return array_keys($schema->columns);
    }

    /**
     * @var bool whether MSSQL used is old.
     */
    private $_oldMssql;

    /**
     * @return bool whether the version of the MSSQL being used is older than 2012.
     * @throws \yii\base\InvalidConfigException
     * @throws \yii\db\Exception
     */
    protected function isOldMssql()
    {
        if ($this->_oldMssql === null) {
            $pdo = $this->db->getSlavePdo();
            $version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));
            $this->_oldMssql = $version[0] < 11;
        }
        return $this->_oldMssql;
    }

    /**
     * @inheritdoc
     * @throws NotSupportedException if `$columns` is an array
     */
    protected function buildSubqueryInCondition($operator, $columns, $values, &$params)
    {
        if (is_array($columns)) {
            throw new NotSupportedException(__METHOD__ . ' is not supported by MSSQL.');
        }
        return parent::buildSubqueryInCondition($operator, $columns, $values, $params);
    }

    /**
     * Builds SQL for IN condition
     *
     * @param string $operator
     * @param array $columns
     * @param array $values
     * @param array $params
     * @return string SQL
     */
    protected function buildCompositeInCondition($operator, $columns, $values, &$params)
    {
        $quotedColumns = [];
        foreach ($columns as $i => $column) {
            $quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
        }
        $vss = [];
        foreach ($values as $value) {
            $vs = [];
            foreach ($columns as $i => $column) {
                if (isset($value[$column])) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $params[$phName] = $value[$column];
                    $vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
                } else {
                    $vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
                }
            }
            $vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
        }

        return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
    }

    /**
     * @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)) {
                    $phName = self::PARAM_PREFIX . count($params);
                    $columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", [$phName => $value]);
                }
            }
        }
        return $columns;
    }

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

    /**
     * @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\InvalidParamException;
11			use yii\base\NotSupportedException;
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',
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			/**
50			* @inheritdoc
51			*/
52			public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset)
53			{
54			if (!$this->hasOffset($offset) && !$this->hasLimit($limit)) {
55			$orderBy = $this->buildOrderBy($orderBy);
56			return $orderBy === '' ? $sql : $sql . $this->separator . $orderBy;
57			}
58
59			if ($this->isOldMssql()) {
60			return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
61			} else {
62			return $this->newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset);
63			}
64			}
65
66			/**
67			* Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2012 or newer.
68			* @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
69			* @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
70			* @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
71			* @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
72			* @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
73			*/
74			protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
75			{
76			$orderBy = $this->buildOrderBy($orderBy);
77			if ($orderBy === '') {
78			// ORDER BY clause is required when FETCH and OFFSET are in the SQL
79			$orderBy = 'ORDER BY (SELECT NULL)';
80			}
81			$sql .= $this->separator . $orderBy;
82
83			// http://technet.microsoft.com/en-us/library/gg699618.aspx
84			$offset = $this->hasOffset($offset) ? $offset : '0';
85			$sql .= $this->separator . "OFFSET $offset ROWS";
86			if ($this->hasLimit($limit)) {
87			$sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY";
88			}
89
90			return $sql;
91			}
92
93			/**
94			* Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2005 to 2008.
95			* @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET)
96			* @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter.
97			* @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details.
98			* @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details.
99			* @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any)
100			*/
101			protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
102			{
103			$orderBy = $this->buildOrderBy($orderBy);
104			if ($orderBy === '') {
105			// ROW_NUMBER() requires an ORDER BY clause
106			$orderBy = 'ORDER BY (SELECT NULL)';
107			}
108
109			$sql = preg_replace('/^([\s(])SELECT(\s+DISTINCT)?(?!\sTOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);
110
111			if ($this->hasLimit($limit)) {
112			$sql = "SELECT TOP $limit * FROM ($sql) sub";
113			} else {
114			$sql = "SELECT * FROM ($sql) sub";
115			}
116			if ($this->hasOffset($offset)) {
117			$sql .= $this->separator . "WHERE rowNum > $offset";
118			}
119
120			return $sql;
121			}
122
123			/**
124			* Builds a SQL statement for renaming a DB table.
125			* @param string $oldName the table to be renamed. The name will be properly quoted by the method.
126			* @param string $newName the new table name. The name will be properly quoted by the method.
127			* @return string the SQL statement for renaming a DB table.
128			*/
129			public function renameTable($oldName, $newName)
130			{
131			return 'sp_rename ' . $this->db->quoteTableName($oldName) . ', ' . $this->db->quoteTableName($newName);
132			}
133
134			/**
135			* Builds a SQL statement for renaming a column.
136			* @param string $table the table whose column is to be renamed. The name will be properly quoted by the method.
137			* @param string $oldName the old name of the column. The name will be properly quoted by the method.
138			* @param string $newName the new name of the column. The name will be properly quoted by the method.
139			* @return string the SQL statement for renaming a DB column.
140			*/
141			public function renameColumn($table, $oldName, $newName)
142			{
143			$table = $this->db->quoteTableName($table);
144			$oldName = $this->db->quoteColumnName($oldName);
145			$newName = $this->db->quoteColumnName($newName);
146			return "sp_rename '{$table}.{$oldName}', {$newName}, 'COLUMN'";
147			}
148
149			/**
150			* Builds a SQL statement for changing the definition of a column.
151			* @param string $table the table whose column is to be changed. The table name will be properly quoted by the method.
152			* @param string $column the name of the column to be changed. The name will be properly quoted by the method.
153			* @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any)
154			* into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
155			* For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
156			* @return string the SQL statement for changing the definition of a column.
157			*/
158			public function alterColumn($table, $column, $type)
159			{
160			$type = $this->getColumnType($type);
161			$sql = 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ALTER COLUMN '
162			. $this->db->quoteColumnName($column) . ' '
163			. $this->getColumnType($type);
164
165			return $sql;
166			}
167
168			/**
169			* Builds a SQL statement for enabling or disabling integrity check.
170			* @param bool $check whether to turn on or off the integrity check.
171			* @param string $schema the schema of the tables. Defaults to empty string, meaning the current or default schema.
172			* @param string $table the table name. Defaults to empty string, meaning that no table will be changed.
173			* @return string the SQL statement for checking integrity
174			* @throws InvalidParamException if the table does not exist or there is no sequence associated with the table.
175			*/
176			public function checkIntegrity($check = true, $schema = '', $table = '')
177			{
178			if ($schema !== '') {
179			$table = "{$schema}.{$table}";
180			}
181			$table = $this->db->quoteTableName($table);
182			if ($this->db->getTableSchema($table) === null) {
183			throw new InvalidParamException("Table not found: $table");
184			}
185			$enable = $check ? 'CHECK' : 'NOCHECK';
186
187			return "ALTER TABLE {$table} {$enable} CONSTRAINT ALL";
188			}
189
190			/**
191			* @inheritdoc
192			* @since 2.0.8
193			*/
194			public function addCommentOnColumn($table, $column, $comment)
195			{
196			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)}";
197			}
198
199			/**
200			* @inheritdoc
201			* @since 2.0.8
202			*/
203			public function addCommentOnTable($table, $comment)
204			{
205			return "sp_updateextendedproperty @name = N'MS_Description', @value = {$this->db->quoteValue($comment)}, @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}";
206			}
207
208			/**
209			* @inheritdoc
210			* @since 2.0.8
211			*/
212			public function dropCommentFromColumn($table, $column)
213			{
214			return "sp_dropextendedproperty @name = N'MS_Description', @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}, @level2type = N'Column', @level2name = {$this->db->quoteColumnName($column)}";
215			}
216
217			/**
218			* @inheritdoc
219			* @since 2.0.8
220			*/
221			public function dropCommentFromTable($table)
222			{
223			return "sp_dropextendedproperty @name = N'MS_Description', @level1type = N'Table', @level1name = {$this->db->quoteTableName($table)}";
224			}
225
226			/**
227			* Returns an array of column names given model name
228			*
229			* @param string $modelClass name of the model class
230			* @return array\|null array of column names
231			*/
232			protected function getAllColumnNames($modelClass = null)
233			{
234			if (!$modelClass) {
			0 ignored issues – show Bug Best Practice introduced 2015-11-20 21:52 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...
235			return null;
236			}
237			/* @var $model \yii\db\ActiveRecord */
238			$model = new $modelClass;
239			$schema = $model->getTableSchema();
240			return array_keys($schema->columns);
241			}
242
243			/**
244			* @var bool whether MSSQL used is old.
245			*/
246			private $_oldMssql;
247
248			/**
249			* @return bool whether the version of the MSSQL being used is older than 2012.
250			* @throws \yii\base\InvalidConfigException
251			* @throws \yii\db\Exception
252			*/
253			protected function isOldMssql()
254			{
255			if ($this->_oldMssql === null) {
256			$pdo = $this->db->getSlavePdo();
257			$version = explode('.', $pdo->getAttribute(\PDO::ATTR_SERVER_VERSION));
258			$this->_oldMssql = $version[0] < 11;
259			}
260			return $this->_oldMssql;
261			}
262
263			/**
264			* @inheritdoc
265			* @throws NotSupportedException if `$columns` is an array
266			*/
267			protected function buildSubqueryInCondition($operator, $columns, $values, &$params)
268			{
269			if (is_array($columns)) {
270			throw new NotSupportedException(__METHOD__ . ' is not supported by MSSQL.');
271			}
272			return parent::buildSubqueryInCondition($operator, $columns, $values, $params);
273			}
274
275			/**
276			* Builds SQL for IN condition
277			*
278			* @param string $operator
279			* @param array $columns
280			* @param array $values
281			* @param array $params
282			* @return string SQL
283			*/
284			protected function buildCompositeInCondition($operator, $columns, $values, &$params)
285			{
286			$quotedColumns = [];
287			foreach ($columns as $i => $column) {
288			$quotedColumns[$i] = strpos($column, '(') === false ? $this->db->quoteColumnName($column) : $column;
289			}
290			$vss = [];
291			foreach ($values as $value) {
292			$vs = [];
293			foreach ($columns as $i => $column) {
294			if (isset($value[$column])) {
295			$phName = self::PARAM_PREFIX . count($params);
296			$params[$phName] = $value[$column];
297			$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' = ' : ' != ') . $phName;
298			} else {
299			$vs[] = $quotedColumns[$i] . ($operator === 'IN' ? ' IS' : ' IS NOT') . ' NULL';
300			}
301			}
302			$vss[] = '(' . implode($operator === 'IN' ? ' AND ' : ' OR ', $vs) . ')';
303			}
304
305			return '(' . implode($operator === 'IN' ? ' OR ' : ' AND ', $vss) . ')';
306			}
307
308			/**
309			* @inheritdoc
310			* @since 2.0.8
311			*/
312			public function selectExists($rawSql)
313			{
314			return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END';
315			}
316
317			/**
318			* Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary.
319			* @param string $table the table that data will be saved into.
320			* @param array $columns the column data (name => value) to be saved into the table.
321			* @return array normalized columns
322			*/
323			private function normalizeTableRowData($table, $columns, &$params)
324			{
325			if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) {
326			$columnSchemas = $tableSchema->columns;
327			foreach ($columns as $name => $value) {
328			// @see https://github.com/yiisoft/yii2/issues/12599
329			if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && is_string($value)) {
330			$phName = self::PARAM_PREFIX . count($params);
331			$columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", [$phName => $value]);
332			}
333			}
334			}
335			return $columns;
336			}
337
338			/**
339			* @inheritdoc
340			*/
341			public function insert($table, $columns, &$params)
342			{
343			return parent::insert($table, $this->normalizeTableRowData($table, $columns, $params), $params);
344			}
345
346			/**
347			* @inheritdoc
348			*/
349			public function update($table, $columns, $condition, &$params)
350			{
351			return parent::update($table, $this->normalizeTableRowData($table, $columns, $params), $condition, $params);
352			}
353			}
354

yiisoft / yii2

Push — mssql-12599 ( cc56e9 )

QueryBuilder::insert() A

Complexity

Size

Duplication

Importance

Duplication Side-by-Side

Filter issues like