1 | <?php |
||
2 | /** |
||
3 | * @link https://www.yiiframework.com/ |
||
4 | * @copyright Copyright (c) 2008 Yii Software LLC |
||
5 | * @license https://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; |
||
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 | */ |
||
55 | protected function defaultExpressionBuilders() |
||
56 | { |
||
57 | 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 | */ |
||
66 | public function buildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
||
67 | { |
||
68 | if (!$this->hasOffset($offset) && !$this->hasLimit($limit)) { |
||
69 | $orderBy = $this->buildOrderBy($orderBy); |
||
70 | return $orderBy === '' ? $sql : $sql . $this->separator . $orderBy; |
||
71 | } |
||
72 | |||
73 | if (version_compare($this->db->getSchema()->getServerVersion(), '11', '<')) { |
||
74 | return $this->oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset); |
||
75 | } |
||
76 | |||
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 | */ |
||
88 | protected function newBuildOrderByAndLimit($sql, $orderBy, $limit, $offset) |
||
89 | { |
||
90 | $orderBy = $this->buildOrderBy($orderBy); |
||
91 | if ($orderBy === '') { |
||
92 | // ORDER BY clause is required when FETCH and OFFSET are in the SQL |
||
93 | $orderBy = 'ORDER BY (SELECT NULL)'; |
||
94 | } |
||
95 | $sql .= $this->separator . $orderBy; |
||
96 | |||
97 | // http://technet.microsoft.com/en-us/library/gg699618.aspx |
||
98 | $offset = $this->hasOffset($offset) ? $offset : '0'; |
||
99 | $sql .= $this->separator . "OFFSET $offset ROWS"; |
||
100 | if ($this->hasLimit($limit)) { |
||
101 | $sql .= $this->separator . "FETCH NEXT $limit ROWS ONLY"; |
||
102 | } |
||
103 | |||
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 | */ |
||
146 | public function renameTable($oldName, $newName) |
||
147 | { |
||
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 | */ |
||
176 | public function alterColumn($table, $column, $type) |
||
177 | { |
||
178 | $sqlAfter = [$this->dropConstraintsForColumn($table, $column, 'D')]; |
||
179 | |||
180 | $columnName = $this->db->quoteColumnName($column); |
||
181 | $tableName = $this->db->quoteTableName($table); |
||
182 | $constraintBase = preg_replace('/[^a-z0-9_]/i', '', $table . '_' . $column); |
||
183 | |||
184 | if ($type instanceof \yii\db\mssql\ColumnSchemaBuilder) { |
||
185 | $type->setAlterColumnFormat(); |
||
186 | |||
187 | |||
188 | $defaultValue = $type->getDefaultValue(); |
||
189 | if ($defaultValue !== null) { |
||
190 | $sqlAfter[] = $this->addDefaultValue( |
||
191 | "DF_{$constraintBase}", |
||
192 | $table, |
||
193 | $column, |
||
194 | $defaultValue instanceof Expression ? $defaultValue : new Expression($defaultValue) |
||
195 | ); |
||
196 | } |
||
197 | |||
198 | $checkValue = $type->getCheckValue(); |
||
199 | if ($checkValue !== null) { |
||
200 | $sqlAfter[] = "ALTER TABLE {$tableName} ADD CONSTRAINT " . |
||
201 | $this->db->quoteColumnName("CK_{$constraintBase}") . |
||
202 | " CHECK (" . ($defaultValue instanceof Expression ? $checkValue : new Expression($checkValue)) . ")"; |
||
203 | } |
||
204 | |||
205 | if ($type->isUnique()) { |
||
206 | $sqlAfter[] = "ALTER TABLE {$tableName} ADD CONSTRAINT " . $this->db->quoteColumnName("UQ_{$constraintBase}") . " UNIQUE ({$columnName})"; |
||
207 | } |
||
208 | } |
||
209 | |||
210 | return 'ALTER TABLE ' . $tableName . ' ALTER COLUMN ' |
||
211 | . $columnName . ' ' |
||
212 | . $this->getColumnType($type) . "\n" |
||
213 | . implode("\n", $sqlAfter); |
||
214 | } |
||
215 | |||
216 | /** |
||
217 | * {@inheritdoc} |
||
218 | */ |
||
219 | public function addDefaultValue($name, $table, $column, $value) |
||
220 | { |
||
221 | return 'ALTER TABLE ' . $this->db->quoteTableName($table) . ' ADD CONSTRAINT ' |
||
222 | . $this->db->quoteColumnName($name) . ' DEFAULT ' . $this->db->quoteValue($value) . ' FOR ' |
||
223 | . $this->db->quoteColumnName($column); |
||
224 | } |
||
225 | |||
226 | /** |
||
227 | * {@inheritdoc} |
||
228 | */ |
||
229 | public function dropDefaultValue($name, $table) |
||
230 | { |
||
231 | 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 | */ |
||
245 | public function resetSequence($tableName, $value = null) |
||
246 | { |
||
247 | $table = $this->db->getTableSchema($tableName); |
||
248 | if ($table !== null && $table->sequenceName !== null) { |
||
249 | $tableName = $this->db->quoteTableName($tableName); |
||
250 | if ($value === null) { |
||
251 | $key = $this->db->quoteColumnName(reset($table->primaryKey)); |
||
252 | $value = "(SELECT COALESCE(MAX({$key}),0) FROM {$tableName})+1"; |
||
253 | } else { |
||
254 | $value = (int) $value; |
||
255 | } |
||
256 | |||
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|null $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 | */ |
||
302 | protected function buildAddCommentSql($comment, $table, $column = null) |
||
303 | { |
||
304 | $tableSchema = $this->db->schema->getTableSchema($table); |
||
305 | |||
306 | if ($tableSchema === null) { |
||
307 | throw new InvalidArgumentException("Table not found: $table"); |
||
308 | } |
||
309 | |||
310 | $schemaName = $tableSchema->schemaName ? "N'" . $tableSchema->schemaName . "'": 'SCHEMA_NAME()'; |
||
311 | $tableName = "N" . $this->db->quoteValue($tableSchema->name); |
||
312 | $columnName = $column ? "N" . $this->db->quoteValue($column) : null; |
||
313 | $comment = "N" . $this->db->quoteValue($comment); |
||
314 | |||
315 | $functionParams = " |
||
316 | @name = N'MS_description', |
||
317 | @value = $comment, |
||
318 | @level0type = N'SCHEMA', @level0name = $schemaName, |
||
319 | @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 | N'MS_description', |
||
327 | 'SCHEMA', $schemaName, |
||
328 | 'TABLE', $tableName, |
||
329 | " . ($column ? "'COLUMN', $columnName " : ' DEFAULT, DEFAULT ') . " |
||
330 | ) |
||
331 | ) |
||
332 | EXEC sys.sp_addextendedproperty $functionParams |
||
333 | ELSE |
||
334 | EXEC sys.sp_updateextendedproperty $functionParams |
||
335 | "; |
||
336 | } |
||
337 | |||
338 | /** |
||
339 | * {@inheritdoc} |
||
340 | * @since 2.0.8 |
||
341 | */ |
||
342 | public function addCommentOnColumn($table, $column, $comment) |
||
343 | { |
||
344 | return $this->buildAddCommentSql($comment, $table, $column); |
||
345 | } |
||
346 | |||
347 | /** |
||
348 | * {@inheritdoc} |
||
349 | * @since 2.0.8 |
||
350 | */ |
||
351 | public function addCommentOnTable($table, $comment) |
||
352 | { |
||
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|null $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 | */ |
||
368 | protected function buildRemoveCommentSql($table, $column = null) |
||
369 | { |
||
370 | $tableSchema = $this->db->schema->getTableSchema($table); |
||
371 | |||
372 | if ($tableSchema === null) { |
||
373 | throw new InvalidArgumentException("Table not found: $table"); |
||
374 | } |
||
375 | |||
376 | $schemaName = $tableSchema->schemaName ? "N'" . $tableSchema->schemaName . "'": 'SCHEMA_NAME()'; |
||
377 | $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 | N'MS_description', |
||
385 | 'SCHEMA', $schemaName, |
||
386 | 'TABLE', $tableName, |
||
387 | " . ($column ? "'COLUMN', $columnName " : ' DEFAULT, DEFAULT ') . " |
||
388 | ) |
||
389 | ) |
||
390 | EXEC sys.sp_dropextendedproperty |
||
391 | @name = N'MS_description', |
||
392 | @level0type = N'SCHEMA', @level0name = $schemaName, |
||
393 | @level1type = N'TABLE', @level1name = $tableName" |
||
394 | . ($column ? ", @level2type = N'COLUMN', @level2name = $columnName" : '') . ';'; |
||
395 | } |
||
396 | |||
397 | /** |
||
398 | * {@inheritdoc} |
||
399 | * @since 2.0.8 |
||
400 | */ |
||
401 | public function dropCommentFromColumn($table, $column) |
||
402 | { |
||
403 | return $this->buildRemoveCommentSql($table, $column); |
||
404 | } |
||
405 | |||
406 | /** |
||
407 | * {@inheritdoc} |
||
408 | * @since 2.0.8 |
||
409 | */ |
||
410 | public function dropCommentFromTable($table) |
||
411 | { |
||
412 | return $this->buildRemoveCommentSql($table); |
||
413 | } |
||
414 | |||
415 | /** |
||
416 | * Returns an array of column names given model name. |
||
417 | * |
||
418 | * @param string|null $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 | */ |
||
446 | public function selectExists($rawSql) |
||
447 | { |
||
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 | */ |
||
457 | private function normalizeTableRowData($table, $columns, &$params) |
||
0 ignored issues
–
show
|
|||
458 | { |
||
459 | if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) { |
||
460 | $columnSchemas = $tableSchema->columns; |
||
461 | foreach ($columns as $name => $value) { |
||
462 | // @see https://github.com/yiisoft/yii2/issues/12599 |
||
463 | if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && (is_string($value))) { |
||
464 | // @see https://github.com/yiisoft/yii2/issues/12599 |
||
465 | $columns[$name] = new Expression('CONVERT(VARBINARY(MAX), ' . ('0x' . bin2hex($value)) . ')'); |
||
466 | } |
||
467 | } |
||
468 | } |
||
469 | |||
470 | return $columns; |
||
471 | } |
||
472 | |||
473 | /** |
||
474 | * {@inheritdoc} |
||
475 | * Added OUTPUT construction for getting inserted data (for SQL Server 2005 or later) |
||
476 | * OUTPUT clause - The OUTPUT clause is new to SQL Server 2005 and has the ability to access |
||
477 | * the INSERTED and DELETED tables as is the case with a trigger. |
||
478 | */ |
||
479 | public function insert($table, $columns, &$params) |
||
480 | { |
||
481 | $columns = $this->normalizeTableRowData($table, $columns, $params); |
||
482 | |||
483 | $version2005orLater = version_compare($this->db->getSchema()->getServerVersion(), '9', '>='); |
||
484 | |||
485 | list($names, $placeholders, $values, $params) = $this->prepareInsertValues($table, $columns, $params); |
||
486 | $cols = []; |
||
487 | $outputColumns = []; |
||
488 | if ($version2005orLater) { |
||
489 | /* @var $schema TableSchema */ |
||
490 | $schema = $this->db->getTableSchema($table); |
||
491 | foreach ($schema->columns as $column) { |
||
492 | if ($column->isComputed) { |
||
493 | continue; |
||
494 | } |
||
495 | |||
496 | $dbType = $column->dbType; |
||
497 | if (in_array($dbType, ['char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary'])) { |
||
498 | $dbType .= '(MAX)'; |
||
499 | } |
||
500 | if ($column->dbType === Schema::TYPE_TIMESTAMP) { |
||
501 | $dbType = $column->allowNull ? 'varbinary(8)' : 'binary(8)'; |
||
502 | } |
||
503 | |||
504 | $quoteColumnName = $this->db->quoteColumnName($column->name); |
||
505 | $cols[] = $quoteColumnName . ' ' . $dbType . ' ' . ($column->allowNull ? "NULL" : ""); |
||
506 | $outputColumns[] = 'INSERTED.' . $quoteColumnName; |
||
507 | } |
||
508 | } |
||
509 | |||
510 | $countColumns = count($outputColumns); |
||
511 | |||
512 | $sql = 'INSERT INTO ' . $this->db->quoteTableName($table) |
||
513 | . (!empty($names) ? ' (' . implode(', ', $names) . ')' : '') |
||
514 | . (($version2005orLater && $countColumns) ? ' OUTPUT ' . implode(',', $outputColumns) . ' INTO @temporary_inserted' : '') |
||
515 | . (!empty($placeholders) ? ' VALUES (' . implode(', ', $placeholders) . ')' : $values); |
||
516 | |||
517 | if ($version2005orLater && $countColumns) { |
||
518 | $sql = 'SET NOCOUNT ON;DECLARE @temporary_inserted TABLE (' . implode(', ', $cols) . ');' . $sql . |
||
519 | ';SELECT * FROM @temporary_inserted'; |
||
520 | } |
||
521 | |||
522 | return $sql; |
||
523 | } |
||
524 | |||
525 | /** |
||
526 | * {@inheritdoc} |
||
527 | * @see https://docs.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql |
||
528 | * @see https://weblogs.sqlteam.com/dang/2009/01/31/upsert-race-condition-with-merge/ |
||
529 | */ |
||
530 | public function upsert($table, $insertColumns, $updateColumns, &$params) |
||
531 | { |
||
532 | $insertColumns = $this->normalizeTableRowData($table, $insertColumns, $params); |
||
533 | |||
534 | /** @var Constraint[] $constraints */ |
||
535 | list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints); |
||
536 | if (empty($uniqueNames)) { |
||
537 | return $this->insert($table, $insertColumns, $params); |
||
538 | } |
||
539 | if ($updateNames === []) { |
||
540 | // there are no columns to update |
||
541 | $updateColumns = false; |
||
542 | } |
||
543 | |||
544 | $onCondition = ['or']; |
||
545 | $quotedTableName = $this->db->quoteTableName($table); |
||
546 | foreach ($constraints as $constraint) { |
||
547 | $constraintCondition = ['and']; |
||
548 | foreach ($constraint->columnNames as $name) { |
||
549 | $quotedName = $this->db->quoteColumnName($name); |
||
550 | $constraintCondition[] = "$quotedTableName.$quotedName=[EXCLUDED].$quotedName"; |
||
551 | } |
||
552 | $onCondition[] = $constraintCondition; |
||
553 | } |
||
554 | $on = $this->buildCondition($onCondition, $params); |
||
555 | list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params); |
||
556 | |||
557 | /** |
||
558 | * Fix number of select query params for old MSSQL version that does not support offset correctly. |
||
559 | * @see QueryBuilder::oldBuildOrderByAndLimit |
||
560 | */ |
||
561 | $insertNamesUsing = $insertNames; |
||
562 | if (strstr($values, 'rowNum = ROW_NUMBER()') !== false) { |
||
563 | $insertNamesUsing = array_merge(['[rowNum]'], $insertNames); |
||
564 | } |
||
565 | |||
566 | $mergeSql = 'MERGE ' . $this->db->quoteTableName($table) . ' WITH (HOLDLOCK) ' |
||
567 | . 'USING (' . (!empty($placeholders) ? 'VALUES (' . implode(', ', $placeholders) . ')' : ltrim($values, ' ')) . ') AS [EXCLUDED] (' . implode(', ', $insertNamesUsing) . ') ' |
||
568 | . "ON ($on)"; |
||
569 | $insertValues = []; |
||
570 | foreach ($insertNames as $name) { |
||
571 | $quotedName = $this->db->quoteColumnName($name); |
||
572 | if (strrpos($quotedName, '.') === false) { |
||
573 | $quotedName = '[EXCLUDED].' . $quotedName; |
||
574 | } |
||
575 | $insertValues[] = $quotedName; |
||
576 | } |
||
577 | $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')' |
||
578 | . ' VALUES (' . implode(', ', $insertValues) . ')'; |
||
579 | if ($updateColumns === false) { |
||
580 | return "$mergeSql WHEN NOT MATCHED THEN $insertSql;"; |
||
581 | } |
||
582 | |||
583 | if ($updateColumns === true) { |
||
584 | $updateColumns = []; |
||
585 | foreach ($updateNames as $name) { |
||
586 | $quotedName = $this->db->quoteColumnName($name); |
||
587 | if (strrpos($quotedName, '.') === false) { |
||
588 | $quotedName = '[EXCLUDED].' . $quotedName; |
||
589 | } |
||
590 | $updateColumns[$name] = new Expression($quotedName); |
||
591 | } |
||
592 | } |
||
593 | $updateColumns = $this->normalizeTableRowData($table, $updateColumns, $params); |
||
594 | |||
595 | list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params); |
||
596 | $updateSql = 'UPDATE SET ' . implode(', ', $updates); |
||
597 | return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql;"; |
||
598 | } |
||
599 | |||
600 | /** |
||
601 | * {@inheritdoc} |
||
602 | */ |
||
603 | public function update($table, $columns, $condition, &$params) |
||
604 | { |
||
605 | return parent::update($table, $this->normalizeTableRowData($table, $columns, $params), $condition, $params); |
||
606 | } |
||
607 | |||
608 | /** |
||
609 | * {@inheritdoc} |
||
610 | */ |
||
611 | public function getColumnType($type) |
||
612 | { |
||
613 | $columnType = parent::getColumnType($type); |
||
614 | // remove unsupported keywords |
||
615 | $columnType = preg_replace("/\s*comment '.*'/i", '', $columnType); |
||
616 | $columnType = preg_replace('/ first$/i', '', $columnType); |
||
617 | |||
618 | return $columnType; |
||
619 | } |
||
620 | |||
621 | /** |
||
622 | * {@inheritdoc} |
||
623 | */ |
||
624 | protected function extractAlias($table) |
||
625 | { |
||
626 | if (preg_match('/^\[.*\]$/', $table)) { |
||
627 | return false; |
||
628 | } |
||
629 | |||
630 | return parent::extractAlias($table); |
||
631 | } |
||
632 | |||
633 | /** |
||
634 | * Builds a SQL statement for dropping constraints for column of table. |
||
635 | * |
||
636 | * @param string $table the table whose constraint is to be dropped. The name will be properly quoted by the method. |
||
637 | * @param string $column the column whose constraint is to be dropped. The name will be properly quoted by the method. |
||
638 | * @param string $type type of constraint, leave empty for all type of constraints(for example: D - default, 'UQ' - unique, 'C' - check) |
||
639 | * @see https://docs.microsoft.com/sql/relational-databases/system-catalog-views/sys-objects-transact-sql |
||
640 | * @return string the DROP CONSTRAINTS SQL |
||
641 | */ |
||
642 | private function dropConstraintsForColumn($table, $column, $type='') |
||
643 | { |
||
644 | return "DECLARE @tableName VARCHAR(MAX) = '" . $this->db->quoteTableName($table) . "' |
||
645 | DECLARE @columnName VARCHAR(MAX) = '{$column}' |
||
646 | |||
647 | WHILE 1=1 BEGIN |
||
648 | DECLARE @constraintName NVARCHAR(128) |
||
649 | SET @constraintName = (SELECT TOP 1 OBJECT_NAME(cons.[object_id]) |
||
650 | FROM ( |
||
651 | SELECT sc.[constid] object_id |
||
652 | FROM [sys].[sysconstraints] sc |
||
653 | JOIN [sys].[columns] c ON c.[object_id]=sc.[id] AND c.[column_id]=sc.[colid] AND c.[name]=@columnName |
||
654 | WHERE sc.[id] = OBJECT_ID(@tableName) |
||
655 | UNION |
||
656 | SELECT object_id(i.[name]) FROM [sys].[indexes] i |
||
657 | JOIN [sys].[columns] c ON c.[object_id]=i.[object_id] AND c.[name]=@columnName |
||
658 | 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] |
||
659 | WHERE i.[is_unique_constraint]=1 and i.[object_id]=OBJECT_ID(@tableName) |
||
660 | ) cons |
||
661 | JOIN [sys].[objects] so ON so.[object_id]=cons.[object_id] |
||
662 | " . (!empty($type) ? " WHERE so.[type]='{$type}'" : "") . ") |
||
663 | IF @constraintName IS NULL BREAK |
||
664 | EXEC (N'ALTER TABLE ' + @tableName + ' DROP CONSTRAINT [' + @constraintName + ']') |
||
665 | END"; |
||
666 | } |
||
667 | |||
668 | /** |
||
669 | * Drop all constraints before column delete |
||
670 | * {@inheritdoc} |
||
671 | */ |
||
672 | public function dropColumn($table, $column) |
||
673 | { |
||
674 | return $this->dropConstraintsForColumn($table, $column) . "\nALTER TABLE " . $this->db->quoteTableName($table) |
||
675 | . " DROP COLUMN " . $this->db->quoteColumnName($column); |
||
676 | } |
||
677 | } |
||
678 |
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.