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\base\NotSupportedException; |
||
12 | use yii\db\Constraint; |
||
13 | use yii\db\Expression; |
||
14 | use yii\db\TableSchema; |
||
0 ignored issues
–
show
|
|||
15 | |||
16 | /** |
||
17 | * QueryBuilder is the query builder for MS SQL Server databases (version 2008 and above). |
||
18 | * |
||
19 | * @author Timur Ruziev <[email protected]> |
||
20 | * @since 2.0 |
||
21 | */ |
||
22 | class QueryBuilder extends \yii\db\QueryBuilder |
||
23 | { |
||
24 | /** |
||
25 | * @var array mapping from abstract column types (keys) to physical column types (values). |
||
26 | */ |
||
27 | public $typeMap = [ |
||
28 | Schema::TYPE_PK => 'int IDENTITY PRIMARY KEY', |
||
29 | Schema::TYPE_UPK => 'int IDENTITY PRIMARY KEY', |
||
30 | Schema::TYPE_BIGPK => 'bigint IDENTITY PRIMARY KEY', |
||
31 | Schema::TYPE_UBIGPK => 'bigint IDENTITY PRIMARY KEY', |
||
32 | Schema::TYPE_CHAR => 'nchar(1)', |
||
33 | Schema::TYPE_STRING => 'nvarchar(255)', |
||
34 | Schema::TYPE_TEXT => 'nvarchar(max)', |
||
35 | Schema::TYPE_TINYINT => 'tinyint', |
||
36 | Schema::TYPE_SMALLINT => 'smallint', |
||
37 | Schema::TYPE_INTEGER => 'int', |
||
38 | Schema::TYPE_BIGINT => 'bigint', |
||
39 | Schema::TYPE_FLOAT => 'float', |
||
40 | Schema::TYPE_DOUBLE => 'float', |
||
41 | Schema::TYPE_DECIMAL => 'decimal(18,0)', |
||
42 | Schema::TYPE_DATETIME => 'datetime', |
||
43 | Schema::TYPE_TIMESTAMP => 'datetime', |
||
44 | Schema::TYPE_TIME => 'time', |
||
45 | Schema::TYPE_DATE => 'date', |
||
46 | Schema::TYPE_BINARY => 'varbinary(max)', |
||
47 | Schema::TYPE_BOOLEAN => 'bit', |
||
48 | Schema::TYPE_MONEY => 'decimal(19,4)', |
||
49 | ]; |
||
50 | |||
51 | |||
52 | /** |
||
53 | * {@inheritdoc} |
||
54 | 906 | */ |
|
55 | protected function defaultExpressionBuilders() |
||
56 | 906 | { |
|
57 | 906 | return array_merge(parent::defaultExpressionBuilders(), [ |
|
58 | 'yii\db\conditions\InCondition' => 'yii\db\mssql\conditions\InConditionBuilder', |
||
59 | 'yii\db\conditions\LikeCondition' => 'yii\db\mssql\conditions\LikeConditionBuilder', |
||
60 | ]); |
||
61 | } |
||
62 | |||
63 | /** |
||
64 | * {@inheritdoc} |
||
65 | 706 | */ |
|
66 | public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
||
67 | 706 | { |
|
68 | 684 | if (!$this->hasOffset($offset) && !$this->hasLimit($limit)) { |
|
69 | 684 | $orderBy = $this->buildOrderBy($orderBy); |
|
70 | return $orderBy === '' ? $sql : $sql . $this->separator . $orderBy; |
||
71 | } |
||
72 | 64 | ||
73 | if (version_compare($this->db->getSchema()->getServerVersion(), '11', '<')) { |
||
74 | return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset); |
||
75 | } |
||
76 | 64 | ||
77 | return $this->newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset); |
||
78 | } |
||
79 | |||
80 | /** |
||
81 | * Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2012 or newer. |
||
82 | * @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET) |
||
83 | * @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter. |
||
84 | * @param int $limit the limit number. See [[\yii\db\Query::limit]] for more details. |
||
85 | * @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details. |
||
86 | * @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any) |
||
87 | 64 | */ |
|
88 | protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
||
89 | 64 | { |
|
90 | 64 | $orderBy = $this->buildOrderBy($orderBy); |
|
91 | if ($orderBy === '') { |
||
92 | 46 | // ORDER BY clause is required when FETCH and OFFSET are in the SQL |
|
93 | $orderBy = 'ORDER BY (SELECT NULL)'; |
||
94 | 64 | } |
|
95 | $sql .= $this->separator . $orderBy; |
||
96 | |||
97 | 64 | // http://technet.microsoft.com/en-us/library/gg699618.aspx |
|
98 | 64 | $offset = $this->hasOffset($offset) ? $offset : '0'; |
|
99 | 64 | $sql .= $this->separator . "OFFSET $offset ROWS"; |
|
100 | 60 | if ($this->hasLimit($limit)) { |
|
101 | $sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY"; |
||
102 | } |
||
103 | 64 | ||
104 | return $sql; |
||
105 | } |
||
106 | |||
107 | /** |
||
108 | * Builds the ORDER BY/LIMIT/OFFSET clauses for SQL SERVER 2005 to 2008. |
||
109 | * @param string $sql the existing SQL (without ORDER BY/LIMIT/OFFSET) |
||
110 | * @param array $orderBy the order by columns. See [[\yii\db\Query::orderBy]] for more details on how to specify this parameter. |
||
111 | * @param int|Expression $limit the limit number. See [[\yii\db\Query::limit]] for more details. |
||
112 | * @param int $offset the offset number. See [[\yii\db\Query::offset]] for more details. |
||
113 | * @return string the SQL completed with ORDER BY/LIMIT/OFFSET (if any) |
||
114 | */ |
||
115 | protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
||
116 | { |
||
117 | $orderBy = $this->buildOrderBy($orderBy); |
||
118 | if ($orderBy === '') { |
||
119 | // ROW_NUMBER() requires an ORDER BY clause |
||
120 | $orderBy = 'ORDER BY (SELECT NULL)'; |
||
121 | } |
||
122 | |||
123 | $sql = preg_replace('/^([\s(])*SELECT(\s+DISTINCT)?(?!\s*TOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql); |
||
124 | |||
125 | if ($this->hasLimit($limit)) { |
||
126 | if ($limit instanceof Expression) { |
||
127 | $limit = '('. (string)$limit . ')'; |
||
128 | } |
||
129 | $sql = "SELECT TOP $limit * FROM ($sql) sub"; |
||
130 | } else { |
||
131 | $sql = "SELECT * FROM ($sql) sub"; |
||
132 | } |
||
133 | if ($this->hasOffset($offset)) { |
||
134 | $sql .= $this->separator . "WHERE rowNum > $offset"; |
||
135 | } |
||
136 | |||
137 | return $sql; |
||
138 | } |
||
139 | |||
140 | /** |
||
141 | * Builds a SQL statement for renaming a DB table. |
||
142 | * @param string $oldName the table to be renamed. The name will be properly quoted by the method. |
||
143 | * @param string $newName the new table name. The name will be properly quoted by the method. |
||
144 | * @return string the SQL statement for renaming a DB table. |
||
145 | 4 | */ |
|
146 | public function renameTable($oldName, $newName) |
||
147 | 4 | { |
|
148 | return 'sp_rename ' . $this->db->quoteTableName($oldName) . ', ' . $this->db->quoteTableName($newName); |
||
149 | } |
||
150 | |||
151 | /** |
||
152 | * Builds a SQL statement for renaming a column. |
||
153 | * @param string $table the table whose column is to be renamed. The name will be properly quoted by the method. |
||
154 | * @param string $oldName the old name of the column. The name will be properly quoted by the method. |
||
155 | * @param string $newName the new name of the column. The name will be properly quoted by the method. |
||
156 | * @return string the SQL statement for renaming a DB column. |
||
157 | */ |
||
158 | public function renameColumn($table, $oldName, $newName) |
||
159 | { |
||
160 | $table = $this->db->quoteTableName($table); |
||
161 | $oldName = $this->db->quoteColumnName($oldName); |
||
162 | $newName = $this->db->quoteColumnName($newName); |
||
163 | return "sp_rename '{$table}.{$oldName}', {$newName}, 'COLUMN'"; |
||
164 | } |
||
165 | |||
166 | /** |
||
167 | * Builds a SQL statement for changing the definition of a column. |
||
168 | * @param string $table the table whose column is to be changed. The table name will be properly quoted by the method. |
||
169 | * @param string $column the name of the column to be changed. The name will be properly quoted by the method. |
||
170 | * @param string $type the new column type. The [[getColumnType]] method will be invoked to convert abstract column type (if any) |
||
171 | * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL. |
||
172 | * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'. |
||
173 | * @return string the SQL statement for changing the definition of a column. |
||
174 | * @throws NotSupportedException if this is not supported by the underlying DBMS. |
||
175 | 18 | */ |
|
176 | public function alterColumn($table, $column, $type) |
||
177 | 18 | { |
|
178 | $sqlAfter = [$this->dropConstraintsForColumn($table, $column, 'D')]; |
||
179 | 18 | ||
180 | 18 | $columnName = $this->db->quoteColumnName($column); |
|
181 | 18 | $tableName = $this->db->quoteTableName($table); |
|
182 | $constraintBase = preg_replace('/[^a-z0-9_]/i', '', $table . '_' . $column); |
||
183 | 18 | ||
184 | 16 | if ($type instanceof \yii\db\mssql\ColumnSchemaBuilder) { |
|
185 | $type->setAlterColumnFormat(); |
||
186 | |||
187 | 16 | ||
188 | 16 | $defaultValue = $type->getDefaultValue(); |
|
189 | 10 | if ($defaultValue !== null) { |
|
190 | 10 | $sqlAfter[] = $this->addDefaultValue( |
|
191 | 10 | "DF_{$constraintBase}", |
|
192 | 10 | $table, |
|
193 | 10 | $column, |
|
194 | $defaultValue instanceof Expression ? $defaultValue : new Expression($defaultValue) |
||
195 | ); |
||
196 | } |
||
197 | 16 | ||
198 | 16 | $checkValue = $type->getCheckValue(); |
|
199 | 8 | if ($checkValue !== null) { |
|
200 | 8 | $sqlAfter[] = "ALTER TABLE {$tableName} ADD CONSTRAINT " . |
|
201 | 8 | $this->db->quoteColumnName("CK_{$constraintBase}") . |
|
202 | " CHECK (" . ($defaultValue instanceof Expression ? $checkValue : new Expression($checkValue)) . ")"; |
||
203 | } |
||
204 | 16 | ||
205 | 6 | if ($type->isUnique()) { |
|
206 | $sqlAfter[] = "ALTER TABLE {$tableName} ADD CONSTRAINT " . $this->db->quoteColumnName("UQ_{$constraintBase}") . " UNIQUE ({$columnName})"; |
||
207 | } |
||
208 | } |
||
209 | 18 | ||
210 | 18 | return 'ALTER TABLE ' . $tableName . ' ALTER COLUMN ' |
|
211 | 18 | . $columnName . ' ' |
|
212 | 18 | . $this->getColumnType($type) . "\n" |
|
213 | . implode("\n", $sqlAfter); |
||
214 | } |
||
215 | |||
216 | /** |
||
217 | * {@inheritdoc} |
||
218 | 14 | */ |
|
219 | public function addDefaultValue($name, $table, $column, $value) |
||
220 | 14 | { |
|
221 | 14 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT ' |
|
222 | 14 | . $this->db->quoteColumnName($name) . ' DEFAULT ' . $this->db->quoteValue($value) . ' FOR ' |
|
223 | . $this->db->quoteColumnName($column); |
||
224 | } |
||
225 | |||
226 | /** |
||
227 | * {@inheritdoc} |
||
228 | 4 | */ |
|
229 | public function dropDefaultValue($name, $table) |
||
230 | 4 | { |
|
231 | 4 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) |
|
232 | . ' DROP CONSTRAINT ' . $this->db->quoteColumnName($name); |
||
233 | } |
||
234 | |||
235 | /** |
||
236 | * Creates a SQL statement for resetting the sequence value of a table's primary key. |
||
237 | * The sequence will be reset such that the primary key of the next new row inserted |
||
238 | * will have the specified value or 1. |
||
239 | * @param string $tableName the name of the table whose primary key sequence will be reset |
||
240 | * @param mixed $value the value for the primary key of the next new row inserted. If this is not set, |
||
241 | * the next new row's primary key will have a value 1. |
||
242 | * @return string the SQL statement for resetting sequence |
||
243 | * @throws InvalidArgumentException if the table does not exist or there is no sequence associated with the table. |
||
244 | 12 | */ |
|
245 | public function resetSequence($tableName, $value = null) |
||
246 | 12 | { |
|
247 | 12 | $table = $this->db->getTableSchema($tableName); |
|
248 | 12 | if ($table !== null && $table->sequenceName !== null) { |
|
249 | 12 | $tableName = $this->db->quoteTableName($tableName); |
|
250 | 2 | if ($value === null) { |
|
251 | 2 | $key = $this->db->quoteColumnName(reset($table->primaryKey)); |
|
252 | $value = "(SELECT COALESCE(MAX({$key}),0) FROM {$tableName})+1"; |
||
253 | 12 | } else { |
|
254 | $value = (int) $value; |
||
255 | } |
||
256 | 12 | ||
257 | return "DBCC CHECKIDENT ('{$tableName}', RESEED, {$value})"; |
||
258 | } elseif ($table === null) { |
||
259 | throw new InvalidArgumentException("Table not found: $tableName"); |
||
260 | } |
||
261 | |||
262 | throw new InvalidArgumentException("There is not sequence associated with table '$tableName'."); |
||
263 | } |
||
264 | |||
265 | /** |
||
266 | * Builds a SQL statement for enabling or disabling integrity check. |
||
267 | * @param bool $check whether to turn on or off the integrity check. |
||
268 | * @param string $schema the schema of the tables. |
||
269 | * @param string $table the table name. |
||
270 | * @return string the SQL statement for checking integrity |
||
271 | */ |
||
272 | public function checkIntegrity($check = true, $schema = '', $table = '') |
||
273 | { |
||
274 | $enable = $check ? 'CHECK' : 'NOCHECK'; |
||
275 | $schema = $schema ?: $this->db->getSchema()->defaultSchema; |
||
276 | $tableNames = $this->db->getTableSchema($table) ? [$table] : $this->db->getSchema()->getTableNames($schema); |
||
277 | $viewNames = $this->db->getSchema()->getViewNames($schema); |
||
278 | $tableNames = array_diff($tableNames, $viewNames); |
||
279 | $command = ''; |
||
280 | |||
281 | foreach ($tableNames as $tableName) { |
||
282 | $tableName = $this->db->quoteTableName("{$schema}.{$tableName}"); |
||
283 | $command .= "ALTER TABLE $tableName $enable CONSTRAINT ALL; "; |
||
284 | } |
||
285 | |||
286 | return $command; |
||
287 | } |
||
288 | |||
289 | /** |
||
290 | * Builds a SQL command for adding or updating a comment to a table or a column. The command built will check if a comment |
||
291 | * already exists. If so, it will be updated, otherwise, it will be added. |
||
292 | * |
||
293 | * @param string $comment the text of the comment to be added. The comment will be properly quoted by the method. |
||
294 | * @param string $table the table to be commented or whose column is to be commented. The table name will be |
||
295 | * properly quoted by the method. |
||
296 | * @param string $column optional. The name of the column to be commented. If empty, the command will add the |
||
297 | * comment to the table instead. The column name will be properly quoted by the method. |
||
298 | * @return string the SQL statement for adding a comment. |
||
299 | * @throws InvalidArgumentException if the table does not exist. |
||
300 | * @since 2.0.24 |
||
301 | 8 | */ |
|
302 | protected function buildAddCommentSql($comment, $table, $column = null) |
||
303 | 8 | { |
|
304 | $tableSchema = $this->db->schema->getTableSchema($table); |
||
305 | 8 | ||
306 | if ($tableSchema === null) { |
||
307 | throw new InvalidArgumentException("Table not found: $table"); |
||
308 | } |
||
309 | 8 | ||
310 | 8 | $schemaName = $tableSchema->schemaName ? "N'" . $tableSchema->schemaName . "'": 'SCHEMA_NAME()'; |
|
311 | 8 | $tableName = "N" . $this->db->quoteValue($tableSchema->name); |
|
312 | 8 | $columnName = $column ? "N" . $this->db->quoteValue($column) : null; |
|
313 | $comment = "N" . $this->db->quoteValue($comment); |
||
314 | |||
315 | $functionParams = " |
||
316 | 8 | @name = N'MS_description', |
|
317 | 8 | @value = $comment, |
|
318 | 8 | @level0type = N'SCHEMA', @level0name = $schemaName, |
|
319 | 8 | @level1type = N'TABLE', @level1name = $tableName" |
|
320 | . ($column ? ", @level2type = N'COLUMN', @level2name = $columnName" : '') . ';'; |
||
321 | |||
322 | return " |
||
323 | IF NOT EXISTS ( |
||
324 | SELECT 1 |
||
325 | FROM fn_listextendedproperty ( |
||
326 | 8 | N'MS_description', |
|
327 | 8 | 'SCHEMA', $schemaName, |
|
328 | 8 | 'TABLE', $tableName, |
|
329 | " . ($column ? "'COLUMN', $columnName " : ' DEFAULT, DEFAULT ') . " |
||
330 | ) |
||
331 | 8 | ) |
|
332 | EXEC sys.sp_addextendedproperty $functionParams |
||
333 | 8 | ELSE |
|
334 | EXEC sys.sp_updateextendedproperty $functionParams |
||
335 | "; |
||
336 | } |
||
337 | |||
338 | /** |
||
339 | * {@inheritdoc} |
||
340 | * @since 2.0.8 |
||
341 | 8 | */ |
|
342 | public function addCommentOnColumn($table, $column, $comment) |
||
343 | 8 | { |
|
344 | return $this->buildAddCommentSql($comment, $table, $column); |
||
345 | } |
||
346 | |||
347 | /** |
||
348 | * {@inheritdoc} |
||
349 | * @since 2.0.8 |
||
350 | 8 | */ |
|
351 | public function addCommentOnTable($table, $comment) |
||
352 | 8 | { |
|
353 | return $this->buildAddCommentSql($comment, $table); |
||
354 | } |
||
355 | |||
356 | /** |
||
357 | * Builds a SQL command for removing a comment from a table or a column. The command built will check if a comment |
||
358 | * already exists before trying to perform the removal. |
||
359 | * |
||
360 | * @param string $table the table that will have the comment removed or whose column will have the comment removed. |
||
361 | * The table name will be properly quoted by the method. |
||
362 | * @param string $column optional. The name of the column whose comment will be removed. If empty, the command |
||
363 | * will remove the comment from the table instead. The column name will be properly quoted by the method. |
||
364 | * @return string the SQL statement for removing the comment. |
||
365 | * @throws InvalidArgumentException if the table does not exist. |
||
366 | * @since 2.0.24 |
||
367 | 4 | */ |
|
368 | protected function buildRemoveCommentSql($table, $column = null) |
||
369 | 4 | { |
|
370 | $tableSchema = $this->db->schema->getTableSchema($table); |
||
371 | 4 | ||
372 | if ($tableSchema === null) { |
||
373 | throw new InvalidArgumentException("Table not found: $table"); |
||
374 | } |
||
375 | 4 | ||
376 | 4 | $schemaName = $tableSchema->schemaName ? "N'" . $tableSchema->schemaName . "'": 'SCHEMA_NAME()'; |
|
377 | 4 | $tableName = "N" . $this->db->quoteValue($tableSchema->name); |
|
378 | $columnName = $column ? "N" . $this->db->quoteValue($column) : null; |
||
379 | |||
380 | return " |
||
381 | IF EXISTS ( |
||
382 | SELECT 1 |
||
383 | FROM fn_listextendedproperty ( |
||
384 | 4 | N'MS_description', |
|
385 | 4 | 'SCHEMA', $schemaName, |
|
386 | 4 | 'TABLE', $tableName, |
|
387 | " . ($column ? "'COLUMN', $columnName " : ' DEFAULT, DEFAULT ') . " |
||
388 | ) |
||
389 | ) |
||
390 | EXEC sys.sp_dropextendedproperty |
||
391 | 4 | @name = N'MS_description', |
|
392 | 4 | @level0type = N'SCHEMA', @level0name = $schemaName, |
|
393 | 4 | @level1type = N'TABLE', @level1name = $tableName" |
|
394 | . ($column ? ", @level2type = N'COLUMN', @level2name = $columnName" : '') . ';'; |
||
395 | } |
||
396 | |||
397 | /** |
||
398 | * {@inheritdoc} |
||
399 | * @since 2.0.8 |
||
400 | 4 | */ |
|
401 | public function dropCommentFromColumn($table, $column) |
||
402 | 4 | { |
|
403 | return $this->buildRemoveCommentSql($table, $column); |
||
404 | } |
||
405 | |||
406 | /** |
||
407 | * {@inheritdoc} |
||
408 | * @since 2.0.8 |
||
409 | 4 | */ |
|
410 | public function dropCommentFromTable($table) |
||
411 | 4 | { |
|
412 | return $this->buildRemoveCommentSql($table); |
||
413 | } |
||
414 | |||
415 | /** |
||
416 | * Returns an array of column names given model name. |
||
417 | * |
||
418 | * @param string $modelClass name of the model class |
||
419 | * @return array|null array of column names |
||
420 | */ |
||
421 | protected function getAllColumnNames($modelClass = null) |
||
422 | { |
||
423 | if (!$modelClass) { |
||
424 | return null; |
||
425 | } |
||
426 | /* @var $modelClass \yii\db\ActiveRecord */ |
||
427 | $schema = $modelClass::getTableSchema(); |
||
428 | return array_keys($schema->columns); |
||
429 | } |
||
430 | |||
431 | /** |
||
432 | * @return bool whether the version of the MSSQL being used is older than 2012. |
||
433 | * @throws \yii\base\InvalidConfigException |
||
434 | * @throws \yii\db\Exception |
||
435 | * @deprecated 2.0.14 Use [[Schema::getServerVersion]] with [[\version_compare()]]. |
||
436 | */ |
||
437 | protected function isOldMssql() |
||
438 | { |
||
439 | return version_compare($this->db->getSchema()->getServerVersion(), '11', '<'); |
||
440 | } |
||
441 | |||
442 | /** |
||
443 | * {@inheritdoc} |
||
444 | * @since 2.0.8 |
||
445 | 44 | */ |
|
446 | public function selectExists($rawSql) |
||
447 | 44 | { |
|
448 | return 'SELECT CASE WHEN EXISTS(' . $rawSql . ') THEN 1 ELSE 0 END'; |
||
449 | } |
||
450 | |||
451 | /** |
||
452 | * Normalizes data to be saved into the table, performing extra preparations and type converting, if necessary. |
||
453 | * @param string $table the table that data will be saved into. |
||
454 | * @param array $columns the column data (name => value) to be saved into the table. |
||
455 | * @return array normalized columns |
||
456 | 212 | */ |
|
457 | private function normalizeTableRowData($table, $columns, &$params) |
||
458 | 212 | { |
|
459 | 212 | if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) { |
|
460 | 212 | $columnSchemas = $tableSchema->columns; |
|
461 | foreach ($columns as $name => $value) { |
||
462 | 210 | // @see https://github.com/yiisoft/yii2/issues/12599 |
|
463 | 36 | if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && (is_string($value) || $value === null)) { |
|
464 | $phName = $this->bindParam($value, $params); |
||
465 | 210 | // @see https://github.com/yiisoft/yii2/issues/12599 |
|
466 | $columns[$name] = new Expression("CONVERT(VARBINARY(MAX), $phName)", $params); |
||
467 | } |
||
468 | } |
||
469 | } |
||
470 | 212 | ||
471 | return $columns; |
||
472 | } |
||
473 | |||
474 | /** |
||
475 | * {@inheritdoc} |
||
476 | * Added OUTPUT construction for getting inserted data (for SQL Server 2005 or later) |
||
477 | * OUTPUT clause - The OUTPUT clause is new to SQL Server 2005 and has the ability to access |
||
478 | * the INSERTED and DELETED tables as is the case with a trigger. |
||
479 | 128 | */ |
|
480 | public function insert($table, $columns, &$params) |
||
481 | 128 | { |
|
482 | $columns = $this->normalizeTableRowData($table, $columns, $params); |
||
483 | 128 | ||
484 | $version2005orLater = version_compare($this->db->getSchema()->getServerVersion(), '9', '>='); |
||
485 | 128 | ||
486 | 122 | list($names, $placeholders, $values, $params) = $this->prepareInsertValues($table, $columns, $params); |
|
487 | 122 | $cols = []; |
|
488 | 122 | $columns = []; |
|
489 | 122 | if ($version2005orLater) { |
|
490 | 122 | /* @var $schema TableSchema */ |
|
491 | 122 | $schema = $this->db->getTableSchema($table); |
|
492 | 2 | foreach ($schema->columns as $column) { |
|
493 | if ($column->isComputed) { |
||
494 | 122 | continue; |
|
495 | 122 | } |
|
496 | 122 | $quoteColumnName = $this->db->quoteColumnName($column->name); |
|
497 | 122 | $cols[] = $quoteColumnName . ' ' |
|
498 | 122 | . $column->dbType |
|
499 | 122 | . (in_array($column->dbType, ['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary']) ? "(MAX)" : "") |
|
500 | . ' ' . ($column->allowNull ? "NULL" : ""); |
||
501 | $columns[] = 'INSERTED.' . $quoteColumnName; |
||
502 | 122 | } |
|
503 | } |
||
504 | 122 | $countColumns = count($columns); |
|
505 | 122 | ||
506 | 122 | $sql = 'INSERT INTO ' . $this->db->quoteTableName($table) |
|
507 | 122 | . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '') |
|
508 | . (($version2005orLater && $countColumns) ? ' OUTPUT ' . implode(',', $columns) . ' INTO @temporary_inserted' : '') |
||
509 | 122 | . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values); |
|
510 | 122 | ||
511 | 122 | if ($version2005orLater && $countColumns) { |
|
512 | $sql = 'SET NOCOUNT ON;DECLARE @temporary_inserted TABLE (' . implode(', ', $cols) . ');' . $sql . |
||
513 | ';SELECT * FROM @temporary_inserted'; |
||
514 | 122 | } |
|
515 | |||
516 | return $sql; |
||
517 | } |
||
518 | |||
519 | /** |
||
520 | * {@inheritdoc} |
||
521 | * @see https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql |
||
522 | 84 | * @see http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx |
|
523 | */ |
||
524 | 84 | public function upsert($table, $insertColumns, $updateColumns, &$params) |
|
525 | { |
||
526 | $insertColumns = $this->normalizeTableRowData($table, $insertColumns, $params); |
||
527 | 84 | ||
528 | 84 | /** @var Constraint[] $constraints */ |
|
529 | 6 | list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints); |
|
530 | if (empty($uniqueNames)) { |
||
531 | 78 | return $this->insert($table, $insertColumns, $params); |
|
532 | } |
||
533 | 2 | if ($updateNames === []) { |
|
534 | // there are no columns to update |
||
535 | $updateColumns = false; |
||
536 | 78 | } |
|
537 | 78 | ||
538 | 78 | $onCondition = ['or']; |
|
539 | 78 | $quotedTableName = $this->db->quoteTableName($table); |
|
540 | 78 | foreach ($constraints as $constraint) { |
|
541 | 78 | $constraintCondition = ['and']; |
|
542 | 78 | foreach ($constraint->columnNames as $name) { |
|
543 | $quotedName = $this->db->quoteColumnName($name); |
||
544 | 78 | $constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName"; |
|
545 | } |
||
546 | 78 | $onCondition[] = $constraintCondition; |
|
547 | 78 | } |
|
548 | $on = $this->buildCondition($onCondition, $params); |
||
549 | list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params); |
||
550 | |||
551 | /** |
||
552 | * Fix number of select query params for old MSSQL version that does not support offset correctly. |
||
553 | 78 | * @see QueryBuilder::oldBuildOrderByAndLimit |
|
554 | 78 | */ |
|
555 | $insertNamesUsing = $insertNames; |
||
556 | if (strstr($values, 'rowNum = ROW_NUMBER()') !== false) { |
||
557 | $insertNamesUsing = array_merge(['[rowNum]'], $insertNames); |
||
558 | 78 | } |
|
559 | 78 | ||
560 | 78 | $mergeSql = 'MERGE ' . $this->db->quoteTableName($table) . ' WITH (HOLDLOCK) ' |
|
561 | 78 | . 'USING (' . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNamesUsing) . ') ' |
|
562 | 78 | . "ON ($on)"; |
|
563 | 78 | $insertValues = []; |
|
564 | 78 | foreach ($insertNames as $name) { |
|
565 | 78 | $quotedName = $this->db->quoteColumnName($name); |
|
566 | if (strrpos($quotedName, '.') === false) { |
||
567 | 78 | $quotedName = '[EXCLUDED].' . $quotedName; |
|
568 | } |
||
569 | 78 | $insertValues[] = $quotedName; |
|
570 | 78 | } |
|
571 | 78 | $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' |
|
572 | 10 | . ' VALUES (' . implode(', ', $insertValues) . ')'; |
|
573 | if ($updateColumns === false) { |
||
574 | return "$mergeSql WHEN NOT MATCHED THEN $insertSql;"; |
||
575 | 68 | } |
|
576 | 54 | ||
577 | 54 | if ($updateColumns === true) { |
|
578 | 54 | $updateColumns = []; |
|
579 | 54 | foreach ($updateNames as $name) { |
|
580 | 54 | $quotedName = $this->db->quoteColumnName($name); |
|
581 | if (strrpos($quotedName, '.') === false) { |
||
582 | 54 | $quotedName = '[EXCLUDED].' . $quotedName; |
|
583 | } |
||
584 | $updateColumns[$name] = new Expression($quotedName); |
||
585 | 68 | } |
|
586 | } |
||
587 | 68 | $updateColumns = $this->normalizeTableRowData($table, $updateColumns, $params); |
|
588 | 68 | ||
589 | 68 | list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params); |
|
590 | $updateSql = 'UPDATE SET ' . implode(', ', $updates); |
||
591 | return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;"; |
||
592 | } |
||
593 | |||
594 | /** |
||
595 | 36 | * {@inheritdoc} |
|
596 | */ |
||
597 | 36 | public function update($table, $columns, $condition, &$params) |
|
598 | { |
||
599 | return parent::update($table, $this->normalizeTableRowData($table, $columns, $params), $condition, $params); |
||
600 | } |
||
601 | |||
602 | /** |
||
603 | 102 | * {@inheritdoc} |
|
604 | */ |
||
605 | 102 | public function getColumnType($type) |
|
606 | { |
||
607 | 102 | $columnType = parent::getColumnType($type); |
|
608 | 102 | // remove unsupported keywords |
|
609 | $columnType = preg_replace("/\s*comment '.*'/i", '', $columnType); |
||
610 | 102 | $columnType = preg_replace('/ first$/i', '', $columnType); |
|
611 | |||
612 | return $columnType; |
||
613 | } |
||
614 | |||
615 | /** |
||
616 | 478 | * {@inheritdoc} |
|
617 | */ |
||
618 | 478 | protected function extractAlias($table) |
|
619 | 12 | { |
|
620 | if (preg_match('/^\[.*\]$/', $table)) { |
||
621 | return false; |
||
622 | 466 | } |
|
623 | |||
624 | return parent::extractAlias($table); |
||
625 | } |
||
626 | |||
627 | /** |
||
628 | * Builds a SQL statement for dropping constraints for column of table. |
||
629 | * |
||
630 | * @param string $table the table whose constraint is to be dropped. The name will be properly quoted by the method. |
||
631 | * @param string $column the column whose constraint is to be dropped. The name will be properly quoted by the method. |
||
632 | * @param string $type type of constraint, leave empty for all type of constraints(for example: D - default, 'UQ' - unique, 'C' - check) |
||
633 | * @see https://docs.microsoft.com/sql/relational-databases/system-catalog-views/sys-objects-transact-sql |
||
634 | 20 | * @return string the DROP CONSTRAINTS SQL |
|
635 | */ |
||
636 | 20 | private function dropConstraintsForColumn($table, $column, $type='') |
|
637 | 20 | { |
|
638 | return "DECLARE @tableName VARCHAR(MAX) = '" . $this->db->quoteTableName($table) . "' |
||
639 | DECLARE @columnName VARCHAR(MAX) = '{$column}' |
||
640 | |||
641 | WHILE 1=1 BEGIN |
||
642 | DECLARE @constraintName NVARCHAR(128) |
||
643 | SET @constraintName = (SELECT TOP 1 OBJECT_NAME(cons.[object_id]) |
||
644 | FROM ( |
||
645 | SELECT sc.[constid] object_id |
||
646 | FROM [sys].[sysconstraints] sc |
||
647 | JOIN [sys].[columns] c ON c.[object_id]=sc.[id] AND c.[column_id]=sc.[colid] AND c.[name]=@columnName |
||
648 | WHERE sc.[id] = OBJECT_ID(@tableName) |
||
649 | UNION |
||
650 | SELECT object_id(i.[name]) FROM [sys].[indexes] i |
||
651 | JOIN [sys].[columns] c ON c.[object_id]=i.[object_id] AND c.[name]=@columnName |
||
652 | JOIN [sys].[index_columns] ic ON ic.[object_id]=i.[object_id] AND i.[index_id]=ic.[index_id] AND c.[column_id]=ic.[column_id] |
||
653 | WHERE i.[is_unique_constraint]=1 and i.[object_id]=OBJECT_ID(@tableName) |
||
654 | 20 | ) cons |
|
655 | JOIN [sys].[objects] so ON so.[object_id]=cons.[object_id] |
||
656 | " . (!empty($type) ? " WHERE so.[type]='{$type}'" : "") . ") |
||
657 | IF @constraintName IS NULL BREAK |
||
658 | EXEC (N'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT [' + @constraintName + ']') |
||
659 | END"; |
||
660 | } |
||
661 | |||
662 | /** |
||
663 | * Drop all constraints before column delete |
||
664 | 4 | * {@inheritdoc} |
|
665 | */ |
||
666 | 4 | public function dropColumn($table, $column) |
|
667 | 4 | { |
|
668 | return $this->dropConstraintsForColumn($table, $column) . "\nALTER TABLE " . $this->db->quoteTableName($table) |
||
669 | . " DROP COLUMN " . $this->db->quoteColumnName($column); |
||
670 | } |
||
671 | } |
||
672 |
Let?s assume that you have a directory layout like this:
and let?s assume the following content of
Bar.php
:If both files
OtherDir/Foo.php
andSomeDir/Foo.php
are loaded in the same runtime, you will see a PHP error such as the following:PHP Fatal error: Cannot use SomeDir\Foo as Foo because the name is already in use in OtherDir/Foo.php
However, as
OtherDir/Foo.php
does not necessarily have to be loaded and the error is only triggered if it is loaded beforeOtherDir/Bar.php
, this problem might go unnoticed for a while. In order to prevent this error from surfacing, you must import the namespace with a different alias: