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\db\CheckConstraint; |
||
11 | use yii\db\ColumnSchema; |
||
0 ignored issues
–
show
|
|||
12 | use yii\db\Constraint; |
||
13 | use yii\db\ConstraintFinderInterface; |
||
14 | use yii\db\ConstraintFinderTrait; |
||
15 | use yii\db\DefaultValueConstraint; |
||
16 | use yii\db\ForeignKeyConstraint; |
||
17 | use yii\db\IndexConstraint; |
||
18 | use yii\db\ViewFinderTrait; |
||
19 | use yii\helpers\ArrayHelper; |
||
20 | |||
21 | /** |
||
22 | * Schema is the class for retrieving metadata from MS SQL Server databases (version 2008 and above). |
||
23 | * |
||
24 | * @author Timur Ruziev <[email protected]> |
||
25 | * @since 2.0 |
||
26 | */ |
||
27 | class Schema extends \yii\db\Schema implements ConstraintFinderInterface |
||
28 | { |
||
29 | use ViewFinderTrait; |
||
30 | use ConstraintFinderTrait; |
||
31 | |||
32 | /** |
||
33 | * {@inheritdoc} |
||
34 | */ |
||
35 | public $columnSchemaClass = 'yii\db\mssql\ColumnSchema'; |
||
36 | /** |
||
37 | * @var string the default schema used for the current session. |
||
38 | */ |
||
39 | public $defaultSchema = 'dbo'; |
||
40 | /** |
||
41 | * @var array mapping from physical column types (keys) to abstract column types (values) |
||
42 | */ |
||
43 | public $typeMap = [ |
||
44 | // exact numbers |
||
45 | 'bigint' => self::TYPE_BIGINT, |
||
46 | 'numeric' => self::TYPE_DECIMAL, |
||
47 | 'bit' => self::TYPE_SMALLINT, |
||
48 | 'smallint' => self::TYPE_SMALLINT, |
||
49 | 'decimal' => self::TYPE_DECIMAL, |
||
50 | 'smallmoney' => self::TYPE_MONEY, |
||
51 | 'int' => self::TYPE_INTEGER, |
||
52 | 'tinyint' => self::TYPE_TINYINT, |
||
53 | 'money' => self::TYPE_MONEY, |
||
54 | // approximate numbers |
||
55 | 'float' => self::TYPE_FLOAT, |
||
56 | 'double' => self::TYPE_DOUBLE, |
||
57 | 'real' => self::TYPE_FLOAT, |
||
58 | // date and time |
||
59 | 'date' => self::TYPE_DATE, |
||
60 | 'datetimeoffset' => self::TYPE_DATETIME, |
||
61 | 'datetime2' => self::TYPE_DATETIME, |
||
62 | 'smalldatetime' => self::TYPE_DATETIME, |
||
63 | 'datetime' => self::TYPE_DATETIME, |
||
64 | 'time' => self::TYPE_TIME, |
||
65 | // character strings |
||
66 | 'char' => self::TYPE_CHAR, |
||
67 | 'varchar' => self::TYPE_STRING, |
||
68 | 'text' => self::TYPE_TEXT, |
||
69 | // unicode character strings |
||
70 | 'nchar' => self::TYPE_CHAR, |
||
71 | 'nvarchar' => self::TYPE_STRING, |
||
72 | 'ntext' => self::TYPE_TEXT, |
||
73 | // binary strings |
||
74 | 'binary' => self::TYPE_BINARY, |
||
75 | 'varbinary' => self::TYPE_BINARY, |
||
76 | 'image' => self::TYPE_BINARY, |
||
77 | // other data types |
||
78 | // 'cursor' type cannot be used with tables |
||
79 | 'timestamp' => self::TYPE_TIMESTAMP, |
||
80 | 'hierarchyid' => self::TYPE_STRING, |
||
81 | 'uniqueidentifier' => self::TYPE_STRING, |
||
82 | 'sql_variant' => self::TYPE_STRING, |
||
83 | 'xml' => self::TYPE_STRING, |
||
84 | 'table' => self::TYPE_STRING, |
||
85 | ]; |
||
86 | |||
87 | /** |
||
88 | * {@inheritdoc} |
||
89 | */ |
||
90 | protected $tableQuoteCharacter = ['[', ']']; |
||
91 | /** |
||
92 | * {@inheritdoc} |
||
93 | */ |
||
94 | protected $columnQuoteCharacter = ['[', ']']; |
||
95 | |||
96 | |||
97 | /** |
||
98 | 89 | * Resolves the table name and schema name (if any). |
|
99 | * @param string $name the table name |
||
100 | 89 | * @return TableSchema resolved table, schema, etc. names. |
|
101 | 89 | */ |
|
102 | 89 | protected function resolveTableName($name) |
|
103 | 89 | { |
|
104 | $resolvedName = new TableSchema(); |
||
105 | $parts = $this->getTableNameParts($name); |
||
106 | $partCount = count($parts); |
||
107 | if ($partCount === 4) { |
||
108 | // server name, catalog name, schema name and table name passed |
||
109 | 89 | $resolvedName->catalogName = $parts[1]; |
|
110 | $resolvedName->schemaName = $parts[2]; |
||
111 | $resolvedName->name = $parts[3]; |
||
112 | $resolvedName->fullName = $resolvedName->catalogName . '.' . $resolvedName->schemaName . '.' . $resolvedName->name; |
||
113 | } elseif ($partCount === 3) { |
||
114 | // catalog name, schema name and table name passed |
||
115 | 89 | $resolvedName->catalogName = $parts[0]; |
|
116 | $resolvedName->schemaName = $parts[1]; |
||
117 | $resolvedName->name = $parts[2]; |
||
118 | $resolvedName->fullName = $resolvedName->catalogName . '.' . $resolvedName->schemaName . '.' . $resolvedName->name; |
||
119 | } elseif ($partCount === 2) { |
||
120 | // only schema name and table name passed |
||
121 | $resolvedName->schemaName = $parts[0]; |
||
122 | 89 | $resolvedName->name = $parts[1]; |
|
123 | 89 | $resolvedName->fullName = ($resolvedName->schemaName !== $this->defaultSchema ? $resolvedName->schemaName . '.' : '') . $resolvedName->name; |
|
124 | } else { |
||
125 | // only table name passed |
||
126 | 89 | $resolvedName->schemaName = $this->defaultSchema; |
|
127 | $resolvedName->fullName = $resolvedName->name = $parts[0]; |
||
128 | } |
||
129 | |||
130 | return $resolvedName; |
||
131 | } |
||
132 | |||
133 | /** |
||
134 | * {@inheritDoc} |
||
135 | 268 | * @param string $name |
|
136 | * @return array |
||
137 | 268 | * @since 2.0.22 |
|
138 | 268 | */ |
|
139 | 268 | protected function getTableNameParts($name) |
|
140 | 268 | { |
|
141 | $parts = [$name]; |
||
142 | preg_match_all('/([^.\[\]]+)|\[([^\[\]]+)\]/', $name, $matches); |
||
143 | 268 | if (isset($matches[0]) && is_array($matches[0]) && !empty($matches[0])) { |
|
144 | $parts = $matches[0]; |
||
145 | 268 | } |
|
146 | |||
147 | $parts = str_replace(['[', ']'], '', $parts); |
||
148 | |||
149 | return $parts; |
||
150 | } |
||
151 | |||
152 | 1 | /** |
|
153 | * {@inheritdoc} |
||
154 | 1 | * @see https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql |
|
155 | */ |
||
156 | protected function findSchemaNames() |
||
157 | { |
||
158 | static $sql = <<<'SQL' |
||
159 | SELECT [s].[name] |
||
160 | FROM [sys].[schemas] AS [s] |
||
161 | INNER JOIN [sys].[database_principals] AS [p] ON [p].[principal_id] = [s].[principal_id] |
||
162 | 1 | WHERE [p].[is_fixed_role] = 0 AND [p].[sid] IS NOT NULL |
|
163 | ORDER BY [s].[name] ASC |
||
164 | SQL; |
||
165 | |||
166 | return $this->db->createCommand($sql)->queryColumn(); |
||
167 | } |
||
168 | 3 | ||
169 | /** |
||
170 | 3 | * {@inheritdoc} |
|
171 | 3 | */ |
|
172 | protected function findTableNames($schema = '') |
||
173 | { |
||
174 | if ($schema === '') { |
||
175 | 3 | $schema = $this->defaultSchema; |
|
176 | } |
||
177 | |||
178 | $sql = <<<'SQL' |
||
179 | SELECT [t].[table_name] |
||
180 | 3 | FROM [INFORMATION_SCHEMA].[TABLES] AS [t] |
|
181 | 3 | WHERE [t].[table_schema] = :schema AND [t].[table_type] IN ('BASE TABLE', 'VIEW') |
|
182 | 3 | ORDER BY [t].[table_name] |
|
183 | 3 | SQL; |
|
184 | $tables = $this->db->createCommand($sql, [':schema' => $schema])->queryColumn(); |
||
185 | 3 | $tables = array_map(static function ($item) { |
|
186 | return '[' . $item . ']'; |
||
187 | }, $tables); |
||
188 | |||
189 | return $tables; |
||
190 | } |
||
191 | 201 | ||
192 | /** |
||
193 | 201 | * {@inheritdoc} |
|
194 | 201 | */ |
|
195 | 201 | protected function loadTableSchema($name) |
|
196 | 201 | { |
|
197 | 194 | $table = new TableSchema(); |
|
198 | 194 | $this->resolveTableNames($table, $name); |
|
199 | $this->findPrimaryKeys($table); |
||
200 | if ($this->findColumns($table)) { |
||
201 | 16 | $this->findForeignKeys($table); |
|
202 | return $table; |
||
203 | } |
||
204 | |||
205 | return null; |
||
206 | } |
||
207 | 36 | ||
208 | /** |
||
209 | 36 | * {@inheritdoc} |
|
210 | */ |
||
211 | protected function loadTablePrimaryKey($tableName) |
||
212 | { |
||
213 | return $this->loadTableConstraints($tableName, 'primaryKey'); |
||
214 | } |
||
215 | 4 | ||
216 | /** |
||
217 | 4 | * {@inheritdoc} |
|
218 | */ |
||
219 | protected function loadTableForeignKeys($tableName) |
||
220 | { |
||
221 | return $this->loadTableConstraints($tableName, 'foreignKeys'); |
||
222 | } |
||
223 | 33 | ||
224 | /** |
||
225 | 33 | * {@inheritdoc} |
|
226 | */ |
||
227 | protected function loadTableIndexes($tableName) |
||
228 | { |
||
229 | static $sql = <<<'SQL' |
||
230 | SELECT |
||
231 | [i].[name] AS [name], |
||
232 | [iccol].[name] AS [column_name], |
||
233 | [i].[is_unique] AS [index_is_unique], |
||
234 | [i].[is_primary_key] AS [index_is_primary] |
||
235 | FROM [sys].[indexes] AS [i] |
||
236 | INNER JOIN [sys].[index_columns] AS [ic] |
||
237 | ON [ic].[object_id] = [i].[object_id] AND [ic].[index_id] = [i].[index_id] |
||
238 | INNER JOIN [sys].[columns] AS [iccol] |
||
239 | ON [iccol].[object_id] = [ic].[object_id] AND [iccol].[column_id] = [ic].[column_id] |
||
240 | 33 | WHERE [i].[object_id] = OBJECT_ID(:fullName) |
|
241 | 33 | ORDER BY [ic].[key_ordinal] ASC |
|
242 | 33 | SQL; |
|
243 | 33 | ||
244 | 33 | $resolvedName = $this->resolveTableName($tableName); |
|
245 | 33 | $indexes = $this->db->createCommand($sql, [ |
|
246 | 33 | ':fullName' => $resolvedName->fullName, |
|
247 | 33 | ])->queryAll(); |
|
248 | 30 | $indexes = $this->normalizePdoRowKeyCase($indexes, true); |
|
249 | 30 | $indexes = ArrayHelper::index($indexes, null, 'name'); |
|
250 | 30 | $result = []; |
|
251 | 30 | foreach ($indexes as $name => $index) { |
|
252 | 30 | $result[] = new IndexConstraint([ |
|
253 | 'isPrimary' => (bool)$index[0]['index_is_primary'], |
||
254 | 'isUnique' => (bool)$index[0]['index_is_unique'], |
||
255 | 'name' => $name, |
||
256 | 33 | 'columnNames' => ArrayHelper::getColumn($index, 'column_name'), |
|
257 | ]); |
||
258 | } |
||
259 | |||
260 | return $result; |
||
261 | } |
||
262 | 13 | ||
263 | /** |
||
264 | 13 | * {@inheritdoc} |
|
265 | */ |
||
266 | protected function loadTableUniques($tableName) |
||
267 | { |
||
268 | return $this->loadTableConstraints($tableName, 'uniques'); |
||
269 | } |
||
270 | 13 | ||
271 | /** |
||
272 | 13 | * {@inheritdoc} |
|
273 | */ |
||
274 | protected function loadTableChecks($tableName) |
||
275 | { |
||
276 | return $this->loadTableConstraints($tableName, 'checks'); |
||
277 | } |
||
278 | 13 | ||
279 | /** |
||
280 | 13 | * {@inheritdoc} |
|
281 | */ |
||
282 | protected function loadTableDefaultValues($tableName) |
||
283 | { |
||
284 | return $this->loadTableConstraints($tableName, 'defaults'); |
||
285 | } |
||
286 | 1 | ||
287 | /** |
||
288 | 1 | * {@inheritdoc} |
|
289 | 1 | */ |
|
290 | public function createSavepoint($name) |
||
291 | { |
||
292 | $this->db->createCommand("SAVE TRANSACTION $name")->execute(); |
||
293 | } |
||
294 | |||
295 | /** |
||
296 | * {@inheritdoc} |
||
297 | */ |
||
298 | public function releaseSavepoint($name) |
||
299 | { |
||
300 | // does nothing as MSSQL does not support this |
||
301 | } |
||
302 | 1 | ||
303 | /** |
||
304 | 1 | * {@inheritdoc} |
|
305 | 1 | */ |
|
306 | public function rollBackSavepoint($name) |
||
307 | { |
||
308 | $this->db->createCommand("ROLLBACK TRANSACTION $name")->execute(); |
||
309 | } |
||
310 | |||
311 | 180 | /** |
|
312 | * Creates a query builder for the MSSQL database. |
||
313 | 180 | * @return QueryBuilder query builder interface. |
|
314 | */ |
||
315 | public function createQueryBuilder() |
||
316 | { |
||
317 | return new QueryBuilder($this->db); |
||
318 | } |
||
319 | |||
320 | /** |
||
321 | 201 | * Resolves the table name and schema name (if any). |
|
322 | * @param TableSchema $table the table metadata object |
||
323 | 201 | * @param string $name the table name |
|
324 | 201 | */ |
|
325 | 201 | protected function resolveTableNames($table, $name) |
|
326 | { |
||
327 | $parts = $this->getTableNameParts($name); |
||
328 | $partCount = count($parts); |
||
329 | if ($partCount === 4) { |
||
330 | // server name, catalog name, schema name and table name passed |
||
331 | 201 | $table->catalogName = $parts[1]; |
|
332 | $table->schemaName = $parts[2]; |
||
333 | $table->name = $parts[3]; |
||
334 | $table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name; |
||
335 | } elseif ($partCount === 3) { |
||
336 | // catalog name, schema name and table name passed |
||
337 | 201 | $table->catalogName = $parts[0]; |
|
338 | $table->schemaName = $parts[1]; |
||
339 | 3 | $table->name = $parts[2]; |
|
340 | 3 | $table->fullName = $table->catalogName . '.' . $table->schemaName . '.' . $table->name; |
|
341 | 3 | } elseif ($partCount === 2) { |
|
342 | // only schema name and table name passed |
||
343 | $table->schemaName = $parts[0]; |
||
344 | 198 | $table->name = $parts[1]; |
|
345 | 198 | $table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name; |
|
346 | } else { |
||
347 | 201 | // only table name passed |
|
348 | $table->schemaName = $this->defaultSchema; |
||
349 | $table->fullName = $table->name = $parts[0]; |
||
350 | } |
||
351 | } |
||
352 | |||
353 | /** |
||
354 | 194 | * Loads the column information into a [[ColumnSchema]] object. |
|
355 | * @param array $info column information |
||
356 | 194 | * @return ColumnSchema the column schema object |
|
357 | */ |
||
358 | 194 | protected function loadColumnSchema($info) |
|
359 | 194 | { |
|
360 | 194 | $column = $this->createColumnSchema(); |
|
361 | 194 | ||
362 | 194 | $column->name = $info['column_name']; |
|
363 | 194 | $column->allowNull = $info['is_nullable'] === 'YES'; |
|
364 | 194 | $column->dbType = $info['data_type']; |
|
365 | 194 | $column->enumValues = []; // mssql has only vague equivalents to enum |
|
366 | $column->isPrimaryKey = null; // primary key will be determined in findColumns() method |
||
367 | 194 | $column->autoIncrement = $info['is_identity'] == 1; |
|
368 | 194 | $column->unsigned = stripos($column->dbType, 'unsigned') !== false; |
|
369 | 194 | $column->comment = $info['comment'] === null ? '' : $info['comment']; |
|
370 | 194 | ||
371 | 194 | $column->type = self::TYPE_STRING; |
|
372 | if (preg_match('/^(\w+)(?:\(([^\)]+)\))?/', $column->dbType, $matches)) { |
||
373 | 194 | $type = $matches[1]; |
|
374 | 170 | if (isset($this->typeMap[$type])) { |
|
375 | 170 | $column->type = $this->typeMap[$type]; |
|
376 | 170 | } |
|
377 | if (!empty($matches[2])) { |
||
378 | $values = explode(',', $matches[2]); |
||
379 | 170 | $column->size = $column->precision = (int)$values[0]; |
|
380 | if (isset($values[1])) { |
||
381 | 170 | $column->scale = (int)$values[1]; |
|
382 | } |
||
383 | if ($column->size === 1 && ($type === 'tinyint' || $type === 'bit')) { |
||
384 | $column->type = 'boolean'; |
||
385 | } elseif ($type === 'bit') { |
||
386 | if ($column->size > 32) { |
||
387 | $column->type = 'bigint'; |
||
388 | } elseif ($column->size === 32) { |
||
389 | $column->type = 'integer'; |
||
390 | } |
||
391 | 194 | } |
|
392 | } |
||
393 | 194 | } |
|
394 | 6 | ||
395 | $column->phpType = $this->getColumnPhpType($column); |
||
396 | 194 | ||
397 | 194 | if ($info['column_default'] === '(NULL)') { |
|
398 | $info['column_default'] = null; |
||
399 | } |
||
400 | 194 | if (!$column->isPrimaryKey && ($column->type !== 'timestamp' || $info['column_default'] !== 'CURRENT_TIMESTAMP')) { |
|
401 | $column->defaultValue = $column->phpTypecast($info['column_default']); |
||
402 | } |
||
403 | |||
404 | return $column; |
||
405 | } |
||
406 | |||
407 | /** |
||
408 | 201 | * Collects the metadata of table columns. |
|
409 | * @param TableSchema $table the table metadata |
||
410 | 201 | * @return bool whether the table exists in the database |
|
411 | 201 | */ |
|
412 | 201 | protected function findColumns($table) |
|
413 | { |
||
414 | $columnsTableName = 'INFORMATION_SCHEMA.COLUMNS'; |
||
415 | $whereSql = "[t1].[table_name] = '{$table->name}'"; |
||
416 | 201 | if ($table->catalogName !== null) { |
|
417 | 201 | $columnsTableName = "{$table->catalogName}.{$columnsTableName}"; |
|
418 | $whereSql .= " AND [t1].[table_catalog] = '{$table->catalogName}'"; |
||
419 | 201 | } |
|
420 | if ($table->schemaName !== null) { |
||
421 | $whereSql .= " AND [t1].[table_schema] = '{$table->schemaName}'"; |
||
422 | } |
||
423 | $columnsTableName = $this->quoteTableName($columnsTableName); |
||
424 | |||
425 | $sql = <<<SQL |
||
426 | SELECT |
||
427 | [t1].[column_name], |
||
428 | [t1].[is_nullable], |
||
429 | CASE WHEN [t1].[data_type] IN ('char','varchar','nchar','nvarchar','binary','varbinary') THEN |
||
430 | CASE WHEN [t1].[character_maximum_length] = NULL OR [t1].[character_maximum_length] = -1 THEN |
||
431 | [t1].[data_type] |
||
432 | ELSE |
||
433 | [t1].[data_type] + '(' + LTRIM(RTRIM(CONVERT(CHAR,[t1].[character_maximum_length]))) + ')' |
||
434 | END |
||
435 | ELSE |
||
436 | [t1].[data_type] |
||
437 | END AS 'data_type', |
||
438 | [t1].[column_default], |
||
439 | COLUMNPROPERTY(OBJECT_ID([t1].[table_schema] + '.' + [t1].[table_name]), [t1].[column_name], 'IsIdentity') AS is_identity, |
||
440 | ( |
||
441 | SELECT CONVERT(VARCHAR, [t2].[value]) |
||
442 | FROM [sys].[extended_properties] AS [t2] |
||
443 | WHERE |
||
444 | [t2].[class] = 1 AND |
||
445 | [t2].[class_desc] = 'OBJECT_OR_COLUMN' AND |
||
446 | 201 | [t2].[name] = 'MS_Description' AND |
|
447 | 201 | [t2].[major_id] = OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[table_name]) AND |
|
448 | [t2].[minor_id] = COLUMNPROPERTY(OBJECT_ID([t1].[TABLE_SCHEMA] + '.' + [t1].[TABLE_NAME]), [t1].[COLUMN_NAME], 'ColumnID') |
||
449 | ) as comment |
||
450 | FROM {$columnsTableName} AS [t1] |
||
451 | 201 | WHERE {$whereSql} |
|
452 | 201 | SQL; |
|
453 | 201 | ||
454 | try { |
||
455 | $columns = $this->db->createCommand($sql)->queryAll(); |
||
456 | if (empty($columns)) { |
||
457 | return false; |
||
458 | 194 | } |
|
459 | 194 | } catch (\Exception $e) { |
|
460 | 194 | return false; |
|
461 | 177 | } |
|
462 | 177 | foreach ($columns as $column) { |
|
463 | 177 | $column = $this->loadColumnSchema($column); |
|
464 | foreach ($table->primaryKey as $primaryKey) { |
||
465 | if (strcasecmp($column->name, $primaryKey) === 0) { |
||
466 | 194 | $column->isPrimaryKey = true; |
|
467 | 168 | break; |
|
468 | } |
||
469 | 194 | } |
|
470 | if ($column->isPrimaryKey && $column->autoIncrement) { |
||
471 | $table->sequenceName = ''; |
||
472 | 194 | } |
|
473 | $table->columns[$column->name] = $column; |
||
474 | } |
||
475 | |||
476 | return true; |
||
477 | } |
||
478 | |||
479 | /** |
||
480 | * Collects the constraint details for the given table and constraint type. |
||
481 | * @param TableSchema $table |
||
482 | 201 | * @param string $type either PRIMARY KEY or UNIQUE |
|
483 | * @return array each entry contains index_name and field_name |
||
484 | 201 | * @since 2.0.4 |
|
485 | 201 | */ |
|
486 | 201 | protected function findTableConstraints($table, $type) |
|
487 | { |
||
488 | $keyColumnUsageTableName = 'INFORMATION_SCHEMA.KEY_COLUMN_USAGE'; |
||
489 | $tableConstraintsTableName = 'INFORMATION_SCHEMA.TABLE_CONSTRAINTS'; |
||
490 | 201 | if ($table->catalogName !== null) { |
|
491 | 201 | $keyColumnUsageTableName = $table->catalogName . '.' . $keyColumnUsageTableName; |
|
492 | $tableConstraintsTableName = $table->catalogName . '.' . $tableConstraintsTableName; |
||
493 | } |
||
494 | $keyColumnUsageTableName = $this->quoteTableName($keyColumnUsageTableName); |
||
495 | $tableConstraintsTableName = $this->quoteTableName($tableConstraintsTableName); |
||
496 | |||
497 | 201 | $sql = <<<SQL |
|
498 | 201 | SELECT |
|
499 | [kcu].[constraint_name] AS [index_name], |
||
500 | [kcu].[column_name] AS [field_name] |
||
501 | FROM {$keyColumnUsageTableName} AS [kcu] |
||
502 | LEFT JOIN {$tableConstraintsTableName} AS [tc] ON |
||
503 | [kcu].[table_schema] = [tc].[table_schema] AND |
||
504 | [kcu].[table_name] = [tc].[table_name] AND |
||
505 | [kcu].[constraint_name] = [tc].[constraint_name] |
||
506 | WHERE |
||
507 | [tc].[constraint_type] = :type AND |
||
508 | 201 | [kcu].[table_name] = :tableName AND |
|
509 | 201 | [kcu].[table_schema] = :schemaName |
|
510 | 201 | SQL; |
|
511 | 201 | ||
512 | 201 | return $this->db |
|
513 | ->createCommand($sql, [ |
||
514 | 201 | ':tableName' => $table->name, |
|
515 | ':schemaName' => $table->schemaName, |
||
516 | ':type' => $type, |
||
517 | ]) |
||
518 | ->queryAll(); |
||
519 | } |
||
520 | |||
521 | 201 | /** |
|
522 | * Collects the primary key column details for the given table. |
||
523 | 201 | * @param TableSchema $table the table metadata |
|
524 | 201 | */ |
|
525 | 177 | protected function findPrimaryKeys($table) |
|
526 | { |
||
527 | 201 | $result = []; |
|
528 | 201 | foreach ($this->findTableConstraints($table, 'PRIMARY KEY') as $row) { |
|
529 | $result[] = $row['field_name']; |
||
530 | } |
||
531 | $table->primaryKey = $result; |
||
532 | } |
||
533 | |||
534 | 194 | /** |
|
535 | * Collects the foreign key column details for the given table. |
||
536 | 194 | * @param TableSchema $table the table metadata |
|
537 | 194 | */ |
|
538 | 194 | protected function findForeignKeys($table) |
|
539 | { |
||
540 | 194 | $object = $table->name; |
|
541 | if ($table->schemaName !== null) { |
||
542 | $object = $table->schemaName . '.' . $object; |
||
543 | } |
||
544 | if ($table->catalogName !== null) { |
||
545 | $object = $table->catalogName . '.' . $object; |
||
546 | } |
||
547 | 194 | ||
548 | // please refer to the following page for more details: |
||
549 | // http://msdn2.microsoft.com/en-us/library/aa175805(SQL.80).aspx |
||
550 | $sql = <<<'SQL' |
||
551 | SELECT |
||
552 | [fk].[name] AS [fk_name], |
||
553 | [cp].[name] AS [fk_column_name], |
||
554 | OBJECT_NAME([fk].[referenced_object_id]) AS [uq_table_name], |
||
555 | [cr].[name] AS [uq_column_name] |
||
556 | FROM |
||
557 | [sys].[foreign_keys] AS [fk] |
||
558 | INNER JOIN [sys].[foreign_key_columns] AS [fkc] ON |
||
559 | [fk].[object_id] = [fkc].[constraint_object_id] |
||
560 | INNER JOIN [sys].[columns] AS [cp] ON |
||
561 | [fk].[parent_object_id] = [cp].[object_id] AND |
||
562 | [fkc].[parent_column_id] = [cp].[column_id] |
||
563 | INNER JOIN [sys].[columns] AS [cr] ON |
||
564 | [fk].[referenced_object_id] = [cr].[object_id] AND |
||
565 | [fkc].[referenced_column_id] = [cr].[column_id] |
||
566 | 194 | WHERE |
|
567 | 194 | [fk].[parent_object_id] = OBJECT_ID(:object) |
|
568 | 194 | SQL; |
|
569 | |||
570 | 194 | $rows = $this->db->createCommand($sql, [ |
|
571 | 194 | ':object' => $object, |
|
572 | 4 | ])->queryAll(); |
|
573 | |||
574 | 194 | $table->foreignKeys = []; |
|
575 | foreach ($rows as $row) { |
||
576 | $table->foreignKeys[$row['fk_name']] = [$row['uq_table_name'], $row['fk_column_name'] => $row['uq_column_name']]; |
||
577 | } |
||
578 | } |
||
579 | |||
580 | /** |
||
581 | * {@inheritdoc} |
||
582 | */ |
||
583 | protected function findViewNames($schema = '') |
||
584 | { |
||
585 | if ($schema === '') { |
||
586 | $schema = $this->defaultSchema; |
||
587 | } |
||
588 | |||
589 | $sql = <<<'SQL' |
||
590 | SELECT [t].[table_name] |
||
591 | FROM [INFORMATION_SCHEMA].[TABLES] AS [t] |
||
592 | WHERE [t].[table_schema] = :schema AND [t].[table_type] = 'VIEW' |
||
593 | ORDER BY [t].[table_name] |
||
594 | SQL; |
||
595 | |||
596 | return $this->db->createCommand($sql, [':schema' => $schema])->queryColumn(); |
||
597 | } |
||
598 | |||
599 | /** |
||
600 | * Returns all unique indexes for the given table. |
||
601 | * |
||
602 | * Each array element is of the following structure: |
||
603 | * |
||
604 | * ```php |
||
605 | * [ |
||
606 | * 'IndexName1' => ['col1' [, ...]], |
||
607 | * 'IndexName2' => ['col2' [, ...]], |
||
608 | * ] |
||
609 | * ``` |
||
610 | * |
||
611 | * @param TableSchema $table the table metadata |
||
612 | * @return array all unique indexes for the given table. |
||
613 | * @since 2.0.4 |
||
614 | */ |
||
615 | public function findUniqueIndexes($table) |
||
616 | { |
||
617 | $result = []; |
||
618 | foreach ($this->findTableConstraints($table, 'UNIQUE') as $row) { |
||
619 | $result[$row['index_name']][] = $row['field_name']; |
||
620 | } |
||
621 | |||
622 | return $result; |
||
623 | } |
||
624 | |||
625 | /** |
||
626 | * Loads multiple types of constraints and returns the specified ones. |
||
627 | * @param string $tableName table name. |
||
628 | * @param string $returnType return type: |
||
629 | * - primaryKey |
||
630 | * - foreignKeys |
||
631 | * - uniques |
||
632 | 79 | * - checks |
|
633 | * - defaults |
||
634 | 79 | * @return mixed constraints. |
|
635 | */ |
||
636 | private function loadTableConstraints($tableName, $returnType) |
||
637 | { |
||
638 | static $sql = <<<'SQL' |
||
639 | SELECT |
||
640 | [o].[name] AS [name], |
||
641 | COALESCE([ccol].[name], [dcol].[name], [fccol].[name], [kiccol].[name]) AS [column_name], |
||
642 | RTRIM([o].[type]) AS [type], |
||
643 | OBJECT_SCHEMA_NAME([f].[referenced_object_id]) AS [foreign_table_schema], |
||
644 | OBJECT_NAME([f].[referenced_object_id]) AS [foreign_table_name], |
||
645 | [ffccol].[name] AS [foreign_column_name], |
||
646 | [f].[update_referential_action_desc] AS [on_update], |
||
647 | [f].[delete_referential_action_desc] AS [on_delete], |
||
648 | [c].[definition] AS [check_expr], |
||
649 | [d].[definition] AS [default_expr] |
||
650 | FROM (SELECT OBJECT_ID(:fullName) AS [object_id]) AS [t] |
||
651 | INNER JOIN [sys].[objects] AS [o] |
||
652 | ON [o].[parent_object_id] = [t].[object_id] AND [o].[type] IN ('PK', 'UQ', 'C', 'D', 'F') |
||
653 | LEFT JOIN [sys].[check_constraints] AS [c] |
||
654 | ON [c].[object_id] = [o].[object_id] |
||
655 | LEFT JOIN [sys].[columns] AS [ccol] |
||
656 | ON [ccol].[object_id] = [c].[parent_object_id] AND [ccol].[column_id] = [c].[parent_column_id] |
||
657 | LEFT JOIN [sys].[default_constraints] AS [d] |
||
658 | ON [d].[object_id] = [o].[object_id] |
||
659 | LEFT JOIN [sys].[columns] AS [dcol] |
||
660 | ON [dcol].[object_id] = [d].[parent_object_id] AND [dcol].[column_id] = [d].[parent_column_id] |
||
661 | LEFT JOIN [sys].[key_constraints] AS [k] |
||
662 | ON [k].[object_id] = [o].[object_id] |
||
663 | LEFT JOIN [sys].[index_columns] AS [kic] |
||
664 | ON [kic].[object_id] = [k].[parent_object_id] AND [kic].[index_id] = [k].[unique_index_id] |
||
665 | LEFT JOIN [sys].[columns] AS [kiccol] |
||
666 | ON [kiccol].[object_id] = [kic].[object_id] AND [kiccol].[column_id] = [kic].[column_id] |
||
667 | LEFT JOIN [sys].[foreign_keys] AS [f] |
||
668 | ON [f].[object_id] = [o].[object_id] |
||
669 | LEFT JOIN [sys].[foreign_key_columns] AS [fc] |
||
670 | ON [fc].[constraint_object_id] = [o].[object_id] |
||
671 | LEFT JOIN [sys].[columns] AS [fccol] |
||
672 | ON [fccol].[object_id] = [fc].[parent_object_id] AND [fccol].[column_id] = [fc].[parent_column_id] |
||
673 | LEFT JOIN [sys].[columns] AS [ffccol] |
||
674 | 79 | ON [ffccol].[object_id] = [fc].[referenced_object_id] AND [ffccol].[column_id] = [fc].[referenced_column_id] |
|
675 | 79 | ORDER BY [kic].[key_ordinal] ASC, [fc].[constraint_column_id] ASC |
|
676 | 79 | SQL; |
|
677 | 79 | ||
678 | 79 | $resolvedName = $this->resolveTableName($tableName); |
|
679 | 79 | $constraints = $this->db->createCommand($sql, [ |
|
680 | ':fullName' => $resolvedName->fullName, |
||
681 | 79 | ])->queryAll(); |
|
682 | $constraints = $this->normalizePdoRowKeyCase($constraints, true); |
||
683 | $constraints = ArrayHelper::index($constraints, null, ['type', 'name']); |
||
684 | $result = [ |
||
685 | 'primaryKey' => null, |
||
686 | 'foreignKeys' => [], |
||
687 | 79 | 'uniques' => [], |
|
688 | 79 | 'checks' => [], |
|
689 | 'defaults' => [], |
||
690 | 79 | ]; |
|
691 | 60 | foreach ($constraints as $type => $names) { |
|
692 | 60 | foreach ($names as $name => $constraint) { |
|
693 | 60 | switch ($type) { |
|
694 | case 'PK': |
||
695 | 60 | $result['primaryKey'] = new Constraint([ |
|
696 | 72 | 'name' => $name, |
|
697 | 16 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
|
698 | 16 | ]); |
|
699 | 16 | break; |
|
700 | 16 | case 'F': |
|
701 | 16 | $result['foreignKeys'][] = new ForeignKeyConstraint([ |
|
702 | 16 | 'name' => $name, |
|
703 | 16 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
|
704 | 16 | 'foreignSchemaName' => $constraint[0]['foreign_table_schema'], |
|
705 | 'foreignTableName' => $constraint[0]['foreign_table_name'], |
||
706 | 16 | 'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'), |
|
707 | 57 | 'onDelete' => str_replace('_', '', $constraint[0]['on_delete']), |
|
708 | 55 | 'onUpdate' => str_replace('_', '', $constraint[0]['on_update']), |
|
709 | 55 | ]); |
|
710 | 55 | break; |
|
711 | case 'UQ': |
||
712 | 55 | $result['uniques'][] = new Constraint([ |
|
713 | 31 | 'name' => $name, |
|
714 | 13 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
|
715 | 13 | ]); |
|
716 | 13 | break; |
|
717 | 13 | case 'C': |
|
718 | $result['checks'][] = new CheckConstraint([ |
||
719 | 13 | 'name' => $name, |
|
720 | 30 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
|
721 | 30 | 'expression' => $constraint[0]['check_expr'], |
|
722 | 30 | ]); |
|
723 | 30 | break; |
|
724 | 30 | case 'D': |
|
725 | $result['defaults'][] = new DefaultValueConstraint([ |
||
726 | 79 | 'name' => $name, |
|
727 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
||
728 | 'value' => $constraint[0]['default_expr'], |
||
729 | ]); |
||
730 | 79 | break; |
|
731 | 79 | } |
|
732 | } |
||
733 | } |
||
734 | 79 | foreach ($result as $type => $data) { |
|
735 | $this->setTableMetadata($tableName, $type, $data); |
||
736 | } |
||
737 | |||
738 | return $result[$returnType]; |
||
739 | } |
||
740 | } |
||
741 |
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: