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\oci; |
||||||
9 | |||||||
10 | use yii\base\InvalidCallException; |
||||||
11 | use yii\base\NotSupportedException; |
||||||
12 | use yii\db\CheckConstraint; |
||||||
13 | use yii\db\ColumnSchema; |
||||||
14 | use yii\db\Connection; |
||||||
15 | use yii\db\Constraint; |
||||||
16 | use yii\db\ConstraintFinderInterface; |
||||||
17 | use yii\db\ConstraintFinderTrait; |
||||||
18 | use yii\db\Expression; |
||||||
19 | use yii\db\ForeignKeyConstraint; |
||||||
20 | use yii\db\IndexConstraint; |
||||||
21 | use yii\db\TableSchema; |
||||||
22 | use yii\helpers\ArrayHelper; |
||||||
23 | |||||||
24 | /** |
||||||
25 | * Schema is the class for retrieving metadata from an Oracle database. |
||||||
26 | * |
||||||
27 | * @property-read string $lastInsertID The row ID of the last row inserted, or the last value retrieved from |
||||||
28 | * the sequence object. |
||||||
29 | * |
||||||
30 | * @author Qiang Xue <[email protected]> |
||||||
31 | * @since 2.0 |
||||||
32 | */ |
||||||
33 | class Schema extends \yii\db\Schema implements ConstraintFinderInterface |
||||||
34 | { |
||||||
35 | use ConstraintFinderTrait; |
||||||
36 | |||||||
37 | /** |
||||||
38 | * @var array map of DB errors and corresponding exceptions |
||||||
39 | * If left part is found in DB error message exception class from the right part is used. |
||||||
40 | */ |
||||||
41 | public $exceptionMap = [ |
||||||
42 | 'ORA-00001: unique constraint' => 'yii\db\IntegrityException', |
||||||
43 | ]; |
||||||
44 | |||||||
45 | /** |
||||||
46 | * {@inheritdoc} |
||||||
47 | */ |
||||||
48 | protected $tableQuoteCharacter = '"'; |
||||||
49 | |||||||
50 | |||||||
51 | /** |
||||||
52 | * {@inheritdoc} |
||||||
53 | */ |
||||||
54 | public function init() |
||||||
55 | { |
||||||
56 | parent::init(); |
||||||
57 | if ($this->defaultSchema === null) { |
||||||
58 | $username = $this->db->username; |
||||||
59 | if (empty($username)) { |
||||||
60 | $username = isset($this->db->masters[0]['username']) ? $this->db->masters[0]['username'] : ''; |
||||||
61 | } |
||||||
62 | $this->defaultSchema = strtoupper($username); |
||||||
63 | } |
||||||
64 | } |
||||||
65 | |||||||
66 | /** |
||||||
67 | * {@inheritdoc} |
||||||
68 | */ |
||||||
69 | protected function resolveTableName($name) |
||||||
70 | { |
||||||
71 | $resolvedName = new TableSchema(); |
||||||
72 | $parts = explode('.', str_replace('"', '', $name)); |
||||||
73 | if (isset($parts[1])) { |
||||||
74 | $resolvedName->schemaName = $parts[0]; |
||||||
75 | $resolvedName->name = $parts[1]; |
||||||
76 | } else { |
||||||
77 | $resolvedName->schemaName = $this->defaultSchema; |
||||||
78 | $resolvedName->name = $name; |
||||||
79 | } |
||||||
80 | $resolvedName->fullName = ($resolvedName->schemaName !== $this->defaultSchema ? $resolvedName->schemaName . '.' : '') . $resolvedName->name; |
||||||
81 | return $resolvedName; |
||||||
82 | } |
||||||
83 | |||||||
84 | /** |
||||||
85 | * {@inheritdoc} |
||||||
86 | * @see https://docs.oracle.com/cd/B28359_01/server.111/b28337/tdpsg_user_accounts.htm |
||||||
87 | */ |
||||||
88 | protected function findSchemaNames() |
||||||
89 | { |
||||||
90 | static $sql = <<<'SQL' |
||||||
91 | SELECT "u"."USERNAME" |
||||||
92 | FROM "DBA_USERS" "u" |
||||||
93 | WHERE "u"."DEFAULT_TABLESPACE" NOT IN ('SYSTEM', 'SYSAUX') |
||||||
94 | ORDER BY "u"."USERNAME" ASC |
||||||
95 | SQL; |
||||||
96 | |||||||
97 | return $this->db->createCommand($sql)->queryColumn(); |
||||||
98 | } |
||||||
99 | |||||||
100 | /** |
||||||
101 | * {@inheritdoc} |
||||||
102 | */ |
||||||
103 | protected function findTableNames($schema = '') |
||||||
104 | { |
||||||
105 | if ($schema === '') { |
||||||
106 | $sql = <<<'SQL' |
||||||
107 | SELECT |
||||||
108 | TABLE_NAME |
||||||
109 | FROM USER_TABLES |
||||||
110 | UNION ALL |
||||||
111 | SELECT |
||||||
112 | VIEW_NAME AS TABLE_NAME |
||||||
113 | FROM USER_VIEWS |
||||||
114 | UNION ALL |
||||||
115 | SELECT |
||||||
116 | MVIEW_NAME AS TABLE_NAME |
||||||
117 | FROM USER_MVIEWS |
||||||
118 | ORDER BY TABLE_NAME |
||||||
119 | SQL; |
||||||
120 | $command = $this->db->createCommand($sql); |
||||||
121 | } else { |
||||||
122 | $sql = <<<'SQL' |
||||||
123 | SELECT |
||||||
124 | OBJECT_NAME AS TABLE_NAME |
||||||
125 | FROM ALL_OBJECTS |
||||||
126 | WHERE |
||||||
127 | OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') |
||||||
128 | AND OWNER = :schema |
||||||
129 | ORDER BY OBJECT_NAME |
||||||
130 | SQL; |
||||||
131 | $command = $this->db->createCommand($sql, [':schema' => $schema]); |
||||||
132 | } |
||||||
133 | |||||||
134 | $rows = $command->queryAll(); |
||||||
135 | $names = []; |
||||||
136 | foreach ($rows as $row) { |
||||||
137 | if ($this->db->slavePdo->getAttribute(\PDO::ATTR_CASE) === \PDO::CASE_LOWER) { |
||||||
138 | $row = array_change_key_case($row, CASE_UPPER); |
||||||
139 | } |
||||||
140 | $names[] = $row['TABLE_NAME']; |
||||||
141 | } |
||||||
142 | |||||||
143 | return $names; |
||||||
144 | } |
||||||
145 | |||||||
146 | /** |
||||||
147 | * {@inheritdoc} |
||||||
148 | */ |
||||||
149 | protected function loadTableSchema($name) |
||||||
150 | { |
||||||
151 | $table = new TableSchema(); |
||||||
152 | $this->resolveTableNames($table, $name); |
||||||
153 | if ($this->findColumns($table)) { |
||||||
154 | $this->findConstraints($table); |
||||||
155 | return $table; |
||||||
156 | } |
||||||
157 | |||||||
158 | return null; |
||||||
159 | } |
||||||
160 | |||||||
161 | /** |
||||||
162 | * {@inheritdoc} |
||||||
163 | */ |
||||||
164 | protected function loadTablePrimaryKey($tableName) |
||||||
165 | { |
||||||
166 | return $this->loadTableConstraints($tableName, 'primaryKey'); |
||||||
167 | } |
||||||
168 | |||||||
169 | /** |
||||||
170 | * {@inheritdoc} |
||||||
171 | */ |
||||||
172 | protected function loadTableForeignKeys($tableName) |
||||||
173 | { |
||||||
174 | return $this->loadTableConstraints($tableName, 'foreignKeys'); |
||||||
175 | } |
||||||
176 | |||||||
177 | /** |
||||||
178 | * {@inheritdoc} |
||||||
179 | */ |
||||||
180 | protected function loadTableIndexes($tableName) |
||||||
181 | { |
||||||
182 | static $sql = <<<'SQL' |
||||||
183 | SELECT |
||||||
184 | /*+ PUSH_PRED("ui") PUSH_PRED("uicol") PUSH_PRED("uc") */ |
||||||
185 | "ui"."INDEX_NAME" AS "name", |
||||||
186 | "uicol"."COLUMN_NAME" AS "column_name", |
||||||
187 | CASE "ui"."UNIQUENESS" WHEN 'UNIQUE' THEN 1 ELSE 0 END AS "index_is_unique", |
||||||
188 | CASE WHEN "uc"."CONSTRAINT_NAME" IS NOT NULL THEN 1 ELSE 0 END AS "index_is_primary" |
||||||
189 | FROM "SYS"."USER_INDEXES" "ui" |
||||||
190 | LEFT JOIN "SYS"."USER_IND_COLUMNS" "uicol" |
||||||
191 | ON "uicol"."INDEX_NAME" = "ui"."INDEX_NAME" |
||||||
192 | LEFT JOIN "SYS"."USER_CONSTRAINTS" "uc" |
||||||
193 | ON "uc"."OWNER" = "ui"."TABLE_OWNER" AND "uc"."CONSTRAINT_NAME" = "ui"."INDEX_NAME" AND "uc"."CONSTRAINT_TYPE" = 'P' |
||||||
194 | WHERE "ui"."TABLE_OWNER" = :schemaName AND "ui"."TABLE_NAME" = :tableName |
||||||
195 | ORDER BY "uicol"."COLUMN_POSITION" ASC |
||||||
196 | SQL; |
||||||
197 | |||||||
198 | $resolvedName = $this->resolveTableName($tableName); |
||||||
199 | $indexes = $this->db->createCommand($sql, [ |
||||||
200 | ':schemaName' => $resolvedName->schemaName, |
||||||
201 | ':tableName' => $resolvedName->name, |
||||||
202 | ])->queryAll(); |
||||||
203 | $indexes = $this->normalizePdoRowKeyCase($indexes, true); |
||||||
204 | $indexes = ArrayHelper::index($indexes, null, 'name'); |
||||||
205 | $result = []; |
||||||
206 | foreach ($indexes as $name => $index) { |
||||||
207 | $result[] = new IndexConstraint([ |
||||||
208 | 'isPrimary' => (bool) $index[0]['index_is_primary'], |
||||||
209 | 'isUnique' => (bool) $index[0]['index_is_unique'], |
||||||
210 | 'name' => $name, |
||||||
211 | 'columnNames' => ArrayHelper::getColumn($index, 'column_name'), |
||||||
212 | ]); |
||||||
213 | } |
||||||
214 | |||||||
215 | return $result; |
||||||
216 | } |
||||||
217 | |||||||
218 | /** |
||||||
219 | * {@inheritdoc} |
||||||
220 | */ |
||||||
221 | protected function loadTableUniques($tableName) |
||||||
222 | { |
||||||
223 | return $this->loadTableConstraints($tableName, 'uniques'); |
||||||
224 | } |
||||||
225 | |||||||
226 | /** |
||||||
227 | * {@inheritdoc} |
||||||
228 | */ |
||||||
229 | protected function loadTableChecks($tableName) |
||||||
230 | { |
||||||
231 | return $this->loadTableConstraints($tableName, 'checks'); |
||||||
232 | } |
||||||
233 | |||||||
234 | /** |
||||||
235 | * {@inheritdoc} |
||||||
236 | * @throws NotSupportedException if this method is called. |
||||||
237 | */ |
||||||
238 | protected function loadTableDefaultValues($tableName) |
||||||
0 ignored issues
–
show
|
|||||||
239 | { |
||||||
240 | throw new NotSupportedException('Oracle does not support default value constraints.'); |
||||||
241 | } |
||||||
242 | |||||||
243 | /** |
||||||
244 | * {@inheritdoc} |
||||||
245 | */ |
||||||
246 | public function releaseSavepoint($name) |
||||||
247 | { |
||||||
248 | // does nothing as Oracle does not support this |
||||||
249 | } |
||||||
250 | |||||||
251 | /** |
||||||
252 | * {@inheritdoc} |
||||||
253 | */ |
||||||
254 | public function quoteSimpleTableName($name) |
||||||
255 | { |
||||||
256 | return strpos($name, '"') !== false ? $name : '"' . $name . '"'; |
||||||
257 | } |
||||||
258 | |||||||
259 | /** |
||||||
260 | * {@inheritdoc} |
||||||
261 | */ |
||||||
262 | public function createQueryBuilder() |
||||||
263 | { |
||||||
264 | return new QueryBuilder($this->db); |
||||||
265 | } |
||||||
266 | |||||||
267 | /** |
||||||
268 | * {@inheritdoc} |
||||||
269 | */ |
||||||
270 | public function createColumnSchemaBuilder($type, $length = null) |
||||||
271 | { |
||||||
272 | return new ColumnSchemaBuilder($type, $length, $this->db); |
||||||
273 | } |
||||||
274 | |||||||
275 | /** |
||||||
276 | * Resolves the table name and schema name (if any). |
||||||
277 | * |
||||||
278 | * @param TableSchema $table the table metadata object |
||||||
279 | * @param string $name the table name |
||||||
280 | */ |
||||||
281 | protected function resolveTableNames($table, $name) |
||||||
282 | { |
||||||
283 | $parts = explode('.', str_replace('"', '', $name)); |
||||||
284 | if (isset($parts[1])) { |
||||||
285 | $table->schemaName = $parts[0]; |
||||||
286 | $table->name = $parts[1]; |
||||||
287 | } else { |
||||||
288 | $table->schemaName = $this->defaultSchema; |
||||||
289 | $table->name = $name; |
||||||
290 | } |
||||||
291 | |||||||
292 | $table->fullName = $table->schemaName !== $this->defaultSchema ? $table->schemaName . '.' . $table->name : $table->name; |
||||||
293 | } |
||||||
294 | |||||||
295 | /** |
||||||
296 | * Collects the table column metadata. |
||||||
297 | * @param TableSchema $table the table schema |
||||||
298 | * @return bool whether the table exists |
||||||
299 | */ |
||||||
300 | protected function findColumns($table) |
||||||
301 | { |
||||||
302 | $sql = <<<'SQL' |
||||||
303 | SELECT |
||||||
304 | A.COLUMN_NAME, |
||||||
305 | A.DATA_TYPE, |
||||||
306 | A.DATA_PRECISION, |
||||||
307 | A.DATA_SCALE, |
||||||
308 | ( |
||||||
309 | CASE A.CHAR_USED WHEN 'C' THEN A.CHAR_LENGTH |
||||||
310 | ELSE A.DATA_LENGTH |
||||||
311 | END |
||||||
312 | ) AS DATA_LENGTH, |
||||||
313 | A.NULLABLE, |
||||||
314 | A.DATA_DEFAULT, |
||||||
315 | COM.COMMENTS AS COLUMN_COMMENT |
||||||
316 | FROM ALL_TAB_COLUMNS A |
||||||
317 | INNER JOIN ALL_OBJECTS B ON B.OWNER = A.OWNER AND LTRIM(B.OBJECT_NAME) = LTRIM(A.TABLE_NAME) |
||||||
318 | LEFT JOIN ALL_COL_COMMENTS COM ON (A.OWNER = COM.OWNER AND A.TABLE_NAME = COM.TABLE_NAME AND A.COLUMN_NAME = COM.COLUMN_NAME) |
||||||
319 | WHERE |
||||||
320 | A.OWNER = :schemaName |
||||||
321 | AND B.OBJECT_TYPE IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW') |
||||||
322 | AND B.OBJECT_NAME = :tableName |
||||||
323 | ORDER BY A.COLUMN_ID |
||||||
324 | SQL; |
||||||
325 | |||||||
326 | try { |
||||||
327 | $columns = $this->db->createCommand($sql, [ |
||||||
328 | ':tableName' => $table->name, |
||||||
329 | ':schemaName' => $table->schemaName, |
||||||
330 | ])->queryAll(); |
||||||
331 | } catch (\Exception $e) { |
||||||
332 | return false; |
||||||
333 | } |
||||||
334 | |||||||
335 | if (empty($columns)) { |
||||||
336 | return false; |
||||||
337 | } |
||||||
338 | |||||||
339 | foreach ($columns as $column) { |
||||||
340 | if ($this->db->slavePdo->getAttribute(\PDO::ATTR_CASE) === \PDO::CASE_LOWER) { |
||||||
341 | $column = array_change_key_case($column, CASE_UPPER); |
||||||
342 | } |
||||||
343 | $c = $this->createColumn($column); |
||||||
344 | $table->columns[$c->name] = $c; |
||||||
345 | } |
||||||
346 | |||||||
347 | return true; |
||||||
348 | } |
||||||
349 | |||||||
350 | /** |
||||||
351 | * Sequence name of table. |
||||||
352 | * |
||||||
353 | * @param string $tableName |
||||||
354 | * @internal param \yii\db\TableSchema $table->name the table schema |
||||||
355 | * @return string|null whether the sequence exists |
||||||
356 | */ |
||||||
357 | protected function getTableSequenceName($tableName) |
||||||
358 | { |
||||||
359 | $sequenceNameSql = <<<'SQL' |
||||||
360 | SELECT |
||||||
361 | UD.REFERENCED_NAME AS SEQUENCE_NAME |
||||||
362 | FROM USER_DEPENDENCIES UD |
||||||
363 | JOIN USER_TRIGGERS UT ON (UT.TRIGGER_NAME = UD.NAME) |
||||||
364 | WHERE |
||||||
365 | UT.TABLE_NAME = :tableName |
||||||
366 | AND UD.TYPE = 'TRIGGER' |
||||||
367 | AND UD.REFERENCED_TYPE = 'SEQUENCE' |
||||||
368 | SQL; |
||||||
369 | $sequenceName = $this->db->createCommand($sequenceNameSql, [':tableName' => $tableName])->queryScalar(); |
||||||
370 | return $sequenceName === false ? null : $sequenceName; |
||||||
371 | } |
||||||
372 | |||||||
373 | /** |
||||||
374 | * @Overrides method in class 'Schema' |
||||||
375 | * @see https://www.php.net/manual/en/function.PDO-lastInsertId.php -> Oracle does not support this |
||||||
376 | * |
||||||
377 | * Returns the ID of the last inserted row or sequence value. |
||||||
378 | * @param string $sequenceName name of the sequence object (required by some DBMS) |
||||||
379 | * @return string the row ID of the last row inserted, or the last value retrieved from the sequence object |
||||||
380 | * @throws InvalidCallException if the DB connection is not active |
||||||
381 | */ |
||||||
382 | public function getLastInsertID($sequenceName = '') |
||||||
383 | { |
||||||
384 | if ($this->db->isActive) { |
||||||
385 | // get the last insert id from the master connection |
||||||
386 | $sequenceName = $this->quoteSimpleTableName($sequenceName); |
||||||
387 | return $this->db->useMaster(function (Connection $db) use ($sequenceName) { |
||||||
388 | return $db->createCommand("SELECT {$sequenceName}.CURRVAL FROM DUAL")->queryScalar(); |
||||||
389 | }); |
||||||
390 | } else { |
||||||
391 | throw new InvalidCallException('DB Connection is not active.'); |
||||||
392 | } |
||||||
393 | } |
||||||
394 | |||||||
395 | /** |
||||||
396 | * Creates ColumnSchema instance. |
||||||
397 | * |
||||||
398 | * @param array $column |
||||||
399 | * @return ColumnSchema |
||||||
400 | */ |
||||||
401 | protected function createColumn($column) |
||||||
402 | { |
||||||
403 | $c = $this->createColumnSchema(); |
||||||
404 | $c->name = $column['COLUMN_NAME']; |
||||||
405 | $c->allowNull = $column['NULLABLE'] === 'Y'; |
||||||
406 | $c->comment = $column['COLUMN_COMMENT'] === null ? '' : $column['COLUMN_COMMENT']; |
||||||
407 | $c->isPrimaryKey = false; |
||||||
408 | $this->extractColumnType($c, $column['DATA_TYPE'], $column['DATA_PRECISION'], $column['DATA_SCALE'], $column['DATA_LENGTH']); |
||||||
409 | $this->extractColumnSize($c, $column['DATA_TYPE'], $column['DATA_PRECISION'], $column['DATA_SCALE'], $column['DATA_LENGTH']); |
||||||
410 | |||||||
411 | $c->phpType = $this->getColumnPhpType($c); |
||||||
412 | |||||||
413 | if (!$c->isPrimaryKey) { |
||||||
414 | if (stripos((string) $column['DATA_DEFAULT'], 'timestamp') !== false) { |
||||||
415 | $c->defaultValue = null; |
||||||
416 | } else { |
||||||
417 | $defaultValue = $column['DATA_DEFAULT']; |
||||||
418 | if ($c->type === 'timestamp' && $defaultValue === 'CURRENT_TIMESTAMP') { |
||||||
419 | $c->defaultValue = new Expression('CURRENT_TIMESTAMP'); |
||||||
420 | } else { |
||||||
421 | if ($defaultValue !== null) { |
||||||
422 | if ( |
||||||
423 | strlen($defaultValue) > 2 |
||||||
424 | && strncmp($defaultValue, "'", 1) === 0 |
||||||
425 | && substr($defaultValue, -1) === "'" |
||||||
426 | ) { |
||||||
427 | $defaultValue = substr($defaultValue, 1, -1); |
||||||
428 | } else { |
||||||
429 | $defaultValue = trim($defaultValue); |
||||||
430 | } |
||||||
431 | } |
||||||
432 | $c->defaultValue = $c->phpTypecast($defaultValue); |
||||||
433 | } |
||||||
434 | } |
||||||
435 | } |
||||||
436 | |||||||
437 | return $c; |
||||||
438 | } |
||||||
439 | |||||||
440 | /** |
||||||
441 | * Finds constraints and fills them into TableSchema object passed. |
||||||
442 | * @param TableSchema $table |
||||||
443 | */ |
||||||
444 | protected function findConstraints($table) |
||||||
445 | { |
||||||
446 | $sql = <<<'SQL' |
||||||
447 | SELECT |
||||||
448 | /*+ PUSH_PRED(C) PUSH_PRED(D) PUSH_PRED(E) */ |
||||||
449 | D.CONSTRAINT_NAME, |
||||||
450 | D.CONSTRAINT_TYPE, |
||||||
451 | C.COLUMN_NAME, |
||||||
452 | C.POSITION, |
||||||
453 | D.R_CONSTRAINT_NAME, |
||||||
454 | E.TABLE_NAME AS TABLE_REF, |
||||||
455 | F.COLUMN_NAME AS COLUMN_REF, |
||||||
456 | C.TABLE_NAME |
||||||
457 | FROM ALL_CONS_COLUMNS C |
||||||
458 | INNER JOIN ALL_CONSTRAINTS D ON D.OWNER = C.OWNER AND D.CONSTRAINT_NAME = C.CONSTRAINT_NAME |
||||||
459 | LEFT JOIN ALL_CONSTRAINTS E ON E.OWNER = D.R_OWNER AND E.CONSTRAINT_NAME = D.R_CONSTRAINT_NAME |
||||||
460 | LEFT JOIN ALL_CONS_COLUMNS F ON F.OWNER = E.OWNER AND F.CONSTRAINT_NAME = E.CONSTRAINT_NAME AND F.POSITION = C.POSITION |
||||||
461 | WHERE |
||||||
462 | C.OWNER = :schemaName |
||||||
463 | AND C.TABLE_NAME = :tableName |
||||||
464 | ORDER BY D.CONSTRAINT_NAME, C.POSITION |
||||||
465 | SQL; |
||||||
466 | $command = $this->db->createCommand($sql, [ |
||||||
467 | ':tableName' => $table->name, |
||||||
468 | ':schemaName' => $table->schemaName, |
||||||
469 | ]); |
||||||
470 | $constraints = []; |
||||||
471 | foreach ($command->queryAll() as $row) { |
||||||
472 | if ($this->db->slavePdo->getAttribute(\PDO::ATTR_CASE) === \PDO::CASE_LOWER) { |
||||||
473 | $row = array_change_key_case($row, CASE_UPPER); |
||||||
474 | } |
||||||
475 | |||||||
476 | if ($row['CONSTRAINT_TYPE'] === 'P') { |
||||||
477 | $table->columns[$row['COLUMN_NAME']]->isPrimaryKey = true; |
||||||
478 | $table->primaryKey[] = $row['COLUMN_NAME']; |
||||||
479 | if (empty($table->sequenceName)) { |
||||||
480 | $table->sequenceName = $this->getTableSequenceName($table->name); |
||||||
481 | } |
||||||
482 | } |
||||||
483 | |||||||
484 | if ($row['CONSTRAINT_TYPE'] !== 'R') { |
||||||
485 | // this condition is not checked in SQL WHERE because of an Oracle Bug: |
||||||
486 | // see https://github.com/yiisoft/yii2/pull/8844 |
||||||
487 | continue; |
||||||
488 | } |
||||||
489 | |||||||
490 | $name = $row['CONSTRAINT_NAME']; |
||||||
491 | if (!isset($constraints[$name])) { |
||||||
492 | $constraints[$name] = [ |
||||||
493 | 'tableName' => $row['TABLE_REF'], |
||||||
494 | 'columns' => [], |
||||||
495 | ]; |
||||||
496 | } |
||||||
497 | $constraints[$name]['columns'][$row['COLUMN_NAME']] = $row['COLUMN_REF']; |
||||||
498 | } |
||||||
499 | |||||||
500 | foreach ($constraints as $constraint) { |
||||||
501 | $name = current(array_keys($constraint)); |
||||||
502 | |||||||
503 | $table->foreignKeys[$name] = array_merge([$constraint['tableName']], $constraint['columns']); |
||||||
504 | } |
||||||
505 | } |
||||||
506 | |||||||
507 | /** |
||||||
508 | * Returns all unique indexes for the given table. |
||||||
509 | * Each array element is of the following structure:. |
||||||
510 | * |
||||||
511 | * ```php |
||||||
512 | * [ |
||||||
513 | * 'IndexName1' => ['col1' [, ...]], |
||||||
514 | * 'IndexName2' => ['col2' [, ...]], |
||||||
515 | * ] |
||||||
516 | * ``` |
||||||
517 | * |
||||||
518 | * @param TableSchema $table the table metadata |
||||||
519 | * @return array all unique indexes for the given table. |
||||||
520 | * @since 2.0.4 |
||||||
521 | */ |
||||||
522 | public function findUniqueIndexes($table) |
||||||
523 | { |
||||||
524 | $query = <<<'SQL' |
||||||
525 | SELECT |
||||||
526 | DIC.INDEX_NAME, |
||||||
527 | DIC.COLUMN_NAME |
||||||
528 | FROM ALL_INDEXES DI |
||||||
529 | INNER JOIN ALL_IND_COLUMNS DIC ON DI.TABLE_NAME = DIC.TABLE_NAME AND DI.INDEX_NAME = DIC.INDEX_NAME |
||||||
530 | WHERE |
||||||
531 | DI.UNIQUENESS = 'UNIQUE' |
||||||
532 | AND DIC.TABLE_OWNER = :schemaName |
||||||
533 | AND DIC.TABLE_NAME = :tableName |
||||||
534 | ORDER BY DIC.TABLE_NAME, DIC.INDEX_NAME, DIC.COLUMN_POSITION |
||||||
535 | SQL; |
||||||
536 | $result = []; |
||||||
537 | $command = $this->db->createCommand($query, [ |
||||||
538 | ':tableName' => $table->name, |
||||||
539 | ':schemaName' => $table->schemaName, |
||||||
540 | ]); |
||||||
541 | foreach ($command->queryAll() as $row) { |
||||||
542 | $result[$row['INDEX_NAME']][] = $row['COLUMN_NAME']; |
||||||
543 | } |
||||||
544 | |||||||
545 | return $result; |
||||||
546 | } |
||||||
547 | |||||||
548 | /** |
||||||
549 | * Extracts the data types for the given column. |
||||||
550 | * @param ColumnSchema $column |
||||||
551 | * @param string $dbType DB type |
||||||
552 | * @param string $precision total number of digits. |
||||||
553 | * This parameter is available since version 2.0.4. |
||||||
554 | * @param string $scale number of digits on the right of the decimal separator. |
||||||
555 | * This parameter is available since version 2.0.4. |
||||||
556 | * @param string $length length for character types. |
||||||
557 | * This parameter is available since version 2.0.4. |
||||||
558 | */ |
||||||
559 | protected function extractColumnType($column, $dbType, $precision, $scale, $length) |
||||||
0 ignored issues
–
show
The parameter
$length is not used and could be removed.
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.
Loading history...
The parameter
$precision is not used and could be removed.
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.
Loading history...
|
|||||||
560 | { |
||||||
561 | $column->dbType = $dbType; |
||||||
562 | |||||||
563 | if (strpos($dbType, 'FLOAT') !== false || strpos($dbType, 'DOUBLE') !== false) { |
||||||
564 | $column->type = 'double'; |
||||||
565 | } elseif (strpos($dbType, 'NUMBER') !== false) { |
||||||
566 | if ($scale === null || $scale > 0) { |
||||||
567 | $column->type = 'decimal'; |
||||||
568 | } else { |
||||||
569 | $column->type = 'integer'; |
||||||
570 | } |
||||||
571 | } elseif (strpos($dbType, 'INTEGER') !== false) { |
||||||
572 | $column->type = 'integer'; |
||||||
573 | } elseif (strpos($dbType, 'BLOB') !== false) { |
||||||
574 | $column->type = 'binary'; |
||||||
575 | } elseif (strpos($dbType, 'CLOB') !== false) { |
||||||
576 | $column->type = 'text'; |
||||||
577 | } elseif (strpos($dbType, 'TIMESTAMP') !== false) { |
||||||
578 | $column->type = 'timestamp'; |
||||||
579 | } else { |
||||||
580 | $column->type = 'string'; |
||||||
581 | } |
||||||
582 | } |
||||||
583 | |||||||
584 | /** |
||||||
585 | * Extracts size, precision and scale information from column's DB type. |
||||||
586 | * @param ColumnSchema $column |
||||||
587 | * @param string $dbType the column's DB type |
||||||
588 | * @param string $precision total number of digits. |
||||||
589 | * This parameter is available since version 2.0.4. |
||||||
590 | * @param string $scale number of digits on the right of the decimal separator. |
||||||
591 | * This parameter is available since version 2.0.4. |
||||||
592 | * @param string $length length for character types. |
||||||
593 | * This parameter is available since version 2.0.4. |
||||||
594 | */ |
||||||
595 | protected function extractColumnSize($column, $dbType, $precision, $scale, $length) |
||||||
0 ignored issues
–
show
The parameter
$dbType is not used and could be removed.
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.
Loading history...
|
|||||||
596 | { |
||||||
597 | $column->size = trim((string) $length) === '' ? null : (int) $length; |
||||||
598 | $column->precision = trim((string) $precision) === '' ? null : (int) $precision; |
||||||
599 | $column->scale = trim((string) $scale) === '' ? null : (int) $scale; |
||||||
600 | } |
||||||
601 | |||||||
602 | /** |
||||||
603 | * {@inheritdoc} |
||||||
604 | */ |
||||||
605 | public function insert($table, $columns) |
||||||
606 | { |
||||||
607 | $params = []; |
||||||
608 | $returnParams = []; |
||||||
609 | $sql = $this->db->getQueryBuilder()->insert($table, $columns, $params); |
||||||
610 | $tableSchema = $this->getTableSchema($table); |
||||||
611 | $returnColumns = $tableSchema->primaryKey; |
||||||
612 | if (!empty($returnColumns)) { |
||||||
613 | $columnSchemas = $tableSchema->columns; |
||||||
614 | $returning = []; |
||||||
615 | foreach ((array) $returnColumns as $name) { |
||||||
616 | $phName = QueryBuilder::PARAM_PREFIX . (count($params) + count($returnParams)); |
||||||
617 | $returnParams[$phName] = [ |
||||||
618 | 'column' => $name, |
||||||
619 | 'value' => '', |
||||||
620 | ]; |
||||||
621 | if (!isset($columnSchemas[$name]) || $columnSchemas[$name]->phpType !== 'integer') { |
||||||
622 | $returnParams[$phName]['dataType'] = \PDO::PARAM_STR; |
||||||
623 | } else { |
||||||
624 | $returnParams[$phName]['dataType'] = \PDO::PARAM_INT; |
||||||
625 | } |
||||||
626 | $returnParams[$phName]['size'] = isset($columnSchemas[$name]->size) ? $columnSchemas[$name]->size : -1; |
||||||
627 | $returning[] = $this->quoteColumnName($name); |
||||||
628 | } |
||||||
629 | $sql .= ' RETURNING ' . implode(', ', $returning) . ' INTO ' . implode(', ', array_keys($returnParams)); |
||||||
630 | } |
||||||
631 | |||||||
632 | $command = $this->db->createCommand($sql, $params); |
||||||
633 | $command->prepare(false); |
||||||
634 | |||||||
635 | foreach ($returnParams as $name => &$value) { |
||||||
636 | $command->pdoStatement->bindParam($name, $value['value'], $value['dataType'], $value['size']); |
||||||
637 | } |
||||||
638 | |||||||
639 | if (!$command->execute()) { |
||||||
640 | return false; |
||||||
641 | } |
||||||
642 | |||||||
643 | $result = []; |
||||||
644 | foreach ($returnParams as $value) { |
||||||
645 | $result[$value['column']] = $value['value']; |
||||||
646 | } |
||||||
647 | |||||||
648 | return $result; |
||||||
649 | } |
||||||
650 | |||||||
651 | /** |
||||||
652 | * Loads multiple types of constraints and returns the specified ones. |
||||||
653 | * @param string $tableName table name. |
||||||
654 | * @param string $returnType return type: |
||||||
655 | * - primaryKey |
||||||
656 | * - foreignKeys |
||||||
657 | * - uniques |
||||||
658 | * - checks |
||||||
659 | * @return mixed constraints. |
||||||
660 | */ |
||||||
661 | private function loadTableConstraints($tableName, $returnType) |
||||||
662 | { |
||||||
663 | static $sql = <<<'SQL' |
||||||
664 | SELECT |
||||||
665 | /*+ PUSH_PRED("uc") PUSH_PRED("uccol") PUSH_PRED("fuc") */ |
||||||
666 | "uc"."CONSTRAINT_NAME" AS "name", |
||||||
667 | "uccol"."COLUMN_NAME" AS "column_name", |
||||||
668 | "uc"."CONSTRAINT_TYPE" AS "type", |
||||||
669 | "fuc"."OWNER" AS "foreign_table_schema", |
||||||
670 | "fuc"."TABLE_NAME" AS "foreign_table_name", |
||||||
671 | "fuccol"."COLUMN_NAME" AS "foreign_column_name", |
||||||
672 | "uc"."DELETE_RULE" AS "on_delete", |
||||||
673 | "uc"."SEARCH_CONDITION" AS "check_expr" |
||||||
674 | FROM "USER_CONSTRAINTS" "uc" |
||||||
675 | INNER JOIN "USER_CONS_COLUMNS" "uccol" |
||||||
676 | ON "uccol"."OWNER" = "uc"."OWNER" AND "uccol"."CONSTRAINT_NAME" = "uc"."CONSTRAINT_NAME" |
||||||
677 | LEFT JOIN "USER_CONSTRAINTS" "fuc" |
||||||
678 | ON "fuc"."OWNER" = "uc"."R_OWNER" AND "fuc"."CONSTRAINT_NAME" = "uc"."R_CONSTRAINT_NAME" |
||||||
679 | LEFT JOIN "USER_CONS_COLUMNS" "fuccol" |
||||||
680 | ON "fuccol"."OWNER" = "fuc"."OWNER" AND "fuccol"."CONSTRAINT_NAME" = "fuc"."CONSTRAINT_NAME" AND "fuccol"."POSITION" = "uccol"."POSITION" |
||||||
681 | WHERE "uc"."OWNER" = :schemaName AND "uc"."TABLE_NAME" = :tableName |
||||||
682 | ORDER BY "uccol"."POSITION" ASC |
||||||
683 | SQL; |
||||||
684 | |||||||
685 | $resolvedName = $this->resolveTableName($tableName); |
||||||
686 | $constraints = $this->db->createCommand($sql, [ |
||||||
687 | ':schemaName' => $resolvedName->schemaName, |
||||||
688 | ':tableName' => $resolvedName->name, |
||||||
689 | ])->queryAll(); |
||||||
690 | $constraints = $this->normalizePdoRowKeyCase($constraints, true); |
||||||
691 | $constraints = ArrayHelper::index($constraints, null, ['type', 'name']); |
||||||
692 | $result = [ |
||||||
693 | 'primaryKey' => null, |
||||||
694 | 'foreignKeys' => [], |
||||||
695 | 'uniques' => [], |
||||||
696 | 'checks' => [], |
||||||
697 | ]; |
||||||
698 | foreach ($constraints as $type => $names) { |
||||||
699 | foreach ($names as $name => $constraint) { |
||||||
700 | switch ($type) { |
||||||
701 | case 'P': |
||||||
702 | $result['primaryKey'] = new Constraint([ |
||||||
703 | 'name' => $name, |
||||||
704 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
||||||
705 | ]); |
||||||
706 | break; |
||||||
707 | case 'R': |
||||||
708 | $result['foreignKeys'][] = new ForeignKeyConstraint([ |
||||||
709 | 'name' => $name, |
||||||
710 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
||||||
711 | 'foreignSchemaName' => $constraint[0]['foreign_table_schema'], |
||||||
712 | 'foreignTableName' => $constraint[0]['foreign_table_name'], |
||||||
713 | 'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'), |
||||||
714 | 'onDelete' => $constraint[0]['on_delete'], |
||||||
715 | 'onUpdate' => null, |
||||||
716 | ]); |
||||||
717 | break; |
||||||
718 | case 'U': |
||||||
719 | $result['uniques'][] = new Constraint([ |
||||||
720 | 'name' => $name, |
||||||
721 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
||||||
722 | ]); |
||||||
723 | break; |
||||||
724 | case 'C': |
||||||
725 | $result['checks'][] = new CheckConstraint([ |
||||||
726 | 'name' => $name, |
||||||
727 | 'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
||||||
728 | 'expression' => $constraint[0]['check_expr'], |
||||||
729 | ]); |
||||||
730 | break; |
||||||
731 | } |
||||||
732 | } |
||||||
733 | } |
||||||
734 | foreach ($result as $type => $data) { |
||||||
735 | $this->setTableMetadata($tableName, $type, $data); |
||||||
736 | } |
||||||
737 | |||||||
738 | return $result[$returnType]; |
||||||
739 | } |
||||||
740 | } |
||||||
741 |
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.