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. This property is read-only. |
||
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://secure.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($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 | && strpos($defaultValue, "'") === 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) |
||
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) |
||
596 | { |
||
597 | $column->size = trim($length) === '' ? null : (int) $length; |
||
598 | $column->precision = trim($precision) === '' ? null : (int) $precision; |
||
599 | $column->scale = trim($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.