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 string $lastInsertID The row ID of the last row inserted, or the last value retrieved from the |
28
|
|
|
* 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) |
|
|
|
|
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 (($len = strlen($defaultValue)) > 2 && $defaultValue[0] === "'" |
423
|
|
|
&& $defaultValue[$len - 1] === "'" |
424
|
|
|
) { |
425
|
|
|
$defaultValue = substr($column['DATA_DEFAULT'], 1, -1); |
426
|
|
|
} else { |
427
|
|
|
$defaultValue = trim($defaultValue); |
428
|
|
|
} |
429
|
|
|
} |
430
|
|
|
$c->defaultValue = $c->phpTypecast($defaultValue); |
431
|
|
|
} |
432
|
|
|
} |
433
|
|
|
} |
434
|
|
|
|
435
|
|
|
return $c; |
436
|
|
|
} |
437
|
|
|
|
438
|
|
|
/** |
439
|
|
|
* Finds constraints and fills them into TableSchema object passed. |
440
|
|
|
* @param TableSchema $table |
441
|
|
|
*/ |
442
|
|
|
protected function findConstraints($table) |
443
|
|
|
{ |
444
|
|
|
$sql = <<<'SQL' |
445
|
|
|
SELECT |
446
|
|
|
/*+ PUSH_PRED(C) PUSH_PRED(D) PUSH_PRED(E) */ |
447
|
|
|
D.CONSTRAINT_NAME, |
448
|
|
|
D.CONSTRAINT_TYPE, |
449
|
|
|
C.COLUMN_NAME, |
450
|
|
|
C.POSITION, |
451
|
|
|
D.R_CONSTRAINT_NAME, |
452
|
|
|
E.TABLE_NAME AS TABLE_REF, |
453
|
|
|
F.COLUMN_NAME AS COLUMN_REF, |
454
|
|
|
C.TABLE_NAME |
455
|
|
|
FROM ALL_CONS_COLUMNS C |
456
|
|
|
INNER JOIN ALL_CONSTRAINTS D ON D.OWNER = C.OWNER AND D.CONSTRAINT_NAME = C.CONSTRAINT_NAME |
457
|
|
|
LEFT JOIN ALL_CONSTRAINTS E ON E.OWNER = D.R_OWNER AND E.CONSTRAINT_NAME = D.R_CONSTRAINT_NAME |
458
|
|
|
LEFT JOIN ALL_CONS_COLUMNS F ON F.OWNER = E.OWNER AND F.CONSTRAINT_NAME = E.CONSTRAINT_NAME AND F.POSITION = C.POSITION |
459
|
|
|
WHERE |
460
|
|
|
C.OWNER = :schemaName |
461
|
|
|
AND C.TABLE_NAME = :tableName |
462
|
|
|
ORDER BY D.CONSTRAINT_NAME, C.POSITION |
463
|
|
|
SQL; |
464
|
|
|
$command = $this->db->createCommand($sql, [ |
465
|
|
|
':tableName' => $table->name, |
466
|
|
|
':schemaName' => $table->schemaName, |
467
|
|
|
]); |
468
|
|
|
$constraints = []; |
469
|
|
|
foreach ($command->queryAll() as $row) { |
470
|
|
|
if ($this->db->slavePdo->getAttribute(\PDO::ATTR_CASE) === \PDO::CASE_LOWER) { |
471
|
|
|
$row = array_change_key_case($row, CASE_UPPER); |
472
|
|
|
} |
473
|
|
|
|
474
|
|
|
if ($row['CONSTRAINT_TYPE'] === 'P') { |
475
|
|
|
$table->columns[$row['COLUMN_NAME']]->isPrimaryKey = true; |
476
|
|
|
$table->primaryKey[] = $row['COLUMN_NAME']; |
477
|
|
|
if (empty($table->sequenceName)) { |
478
|
|
|
$table->sequenceName = $this->getTableSequenceName($table->name); |
479
|
|
|
} |
480
|
|
|
} |
481
|
|
|
|
482
|
|
|
if ($row['CONSTRAINT_TYPE'] !== 'R') { |
483
|
|
|
// this condition is not checked in SQL WHERE because of an Oracle Bug: |
484
|
|
|
// see https://github.com/yiisoft/yii2/pull/8844 |
485
|
|
|
continue; |
486
|
|
|
} |
487
|
|
|
|
488
|
|
|
$name = $row['CONSTRAINT_NAME']; |
489
|
|
|
if (!isset($constraints[$name])) { |
490
|
|
|
$constraints[$name] = [ |
491
|
|
|
'tableName' => $row['TABLE_REF'], |
492
|
|
|
'columns' => [], |
493
|
|
|
]; |
494
|
|
|
} |
495
|
|
|
$constraints[$name]['columns'][$row['COLUMN_NAME']] = $row['COLUMN_REF']; |
496
|
|
|
} |
497
|
|
|
|
498
|
|
|
foreach ($constraints as $constraint) { |
499
|
|
|
$name = current(array_keys($constraint)); |
500
|
|
|
|
501
|
|
|
$table->foreignKeys[$name] = array_merge([$constraint['tableName']], $constraint['columns']); |
502
|
|
|
} |
503
|
|
|
} |
504
|
|
|
|
505
|
|
|
/** |
506
|
|
|
* Returns all unique indexes for the given table. |
507
|
|
|
* Each array element is of the following structure:. |
508
|
|
|
* |
509
|
|
|
* ```php |
510
|
|
|
* [ |
511
|
|
|
* 'IndexName1' => ['col1' [, ...]], |
512
|
|
|
* 'IndexName2' => ['col2' [, ...]], |
513
|
|
|
* ] |
514
|
|
|
* ``` |
515
|
|
|
* |
516
|
|
|
* @param TableSchema $table the table metadata |
517
|
|
|
* @return array all unique indexes for the given table. |
518
|
|
|
* @since 2.0.4 |
519
|
|
|
*/ |
520
|
|
|
public function findUniqueIndexes($table) |
521
|
|
|
{ |
522
|
|
|
$query = <<<'SQL' |
523
|
|
|
SELECT |
524
|
|
|
DIC.INDEX_NAME, |
525
|
|
|
DIC.COLUMN_NAME |
526
|
|
|
FROM ALL_INDEXES DI |
527
|
|
|
INNER JOIN ALL_IND_COLUMNS DIC ON DI.TABLE_NAME = DIC.TABLE_NAME AND DI.INDEX_NAME = DIC.INDEX_NAME |
528
|
|
|
WHERE |
529
|
|
|
DI.UNIQUENESS = 'UNIQUE' |
530
|
|
|
AND DIC.TABLE_OWNER = :schemaName |
531
|
|
|
AND DIC.TABLE_NAME = :tableName |
532
|
|
|
ORDER BY DIC.TABLE_NAME, DIC.INDEX_NAME, DIC.COLUMN_POSITION |
533
|
|
|
SQL; |
534
|
|
|
$result = []; |
535
|
|
|
$command = $this->db->createCommand($query, [ |
536
|
|
|
':tableName' => $table->name, |
537
|
|
|
':schemaName' => $table->schemaName, |
538
|
|
|
]); |
539
|
|
|
foreach ($command->queryAll() as $row) { |
540
|
|
|
$result[$row['INDEX_NAME']][] = $row['COLUMN_NAME']; |
541
|
|
|
} |
542
|
|
|
|
543
|
|
|
return $result; |
544
|
|
|
} |
545
|
|
|
|
546
|
|
|
/** |
547
|
|
|
* Extracts the data types for the given column. |
548
|
|
|
* @param ColumnSchema $column |
549
|
|
|
* @param string $dbType DB type |
550
|
|
|
* @param string $precision total number of digits. |
551
|
|
|
* This parameter is available since version 2.0.4. |
552
|
|
|
* @param string $scale number of digits on the right of the decimal separator. |
553
|
|
|
* This parameter is available since version 2.0.4. |
554
|
|
|
* @param string $length length for character types. |
555
|
|
|
* This parameter is available since version 2.0.4. |
556
|
|
|
*/ |
557
|
|
|
protected function extractColumnType($column, $dbType, $precision, $scale, $length) |
|
|
|
|
558
|
|
|
{ |
559
|
|
|
$column->dbType = $dbType; |
560
|
|
|
|
561
|
|
|
if (strpos($dbType, 'FLOAT') !== false || strpos($dbType, 'DOUBLE') !== false) { |
562
|
|
|
$column->type = 'double'; |
563
|
|
|
} elseif (strpos($dbType, 'NUMBER') !== false) { |
564
|
|
|
if ($scale === null || $scale > 0) { |
565
|
|
|
$column->type = 'decimal'; |
566
|
|
|
} else { |
567
|
|
|
$column->type = 'integer'; |
568
|
|
|
} |
569
|
|
|
} elseif (strpos($dbType, 'INTEGER') !== false) { |
570
|
|
|
$column->type = 'integer'; |
571
|
|
|
} elseif (strpos($dbType, 'BLOB') !== false) { |
572
|
|
|
$column->type = 'binary'; |
573
|
|
|
} elseif (strpos($dbType, 'CLOB') !== false) { |
574
|
|
|
$column->type = 'text'; |
575
|
|
|
} elseif (strpos($dbType, 'TIMESTAMP') !== false) { |
576
|
|
|
$column->type = 'timestamp'; |
577
|
|
|
} else { |
578
|
|
|
$column->type = 'string'; |
579
|
|
|
} |
580
|
|
|
} |
581
|
|
|
|
582
|
|
|
/** |
583
|
|
|
* Extracts size, precision and scale information from column's DB type. |
584
|
|
|
* @param ColumnSchema $column |
585
|
|
|
* @param string $dbType the column's DB type |
586
|
|
|
* @param string $precision total number of digits. |
587
|
|
|
* This parameter is available since version 2.0.4. |
588
|
|
|
* @param string $scale number of digits on the right of the decimal separator. |
589
|
|
|
* This parameter is available since version 2.0.4. |
590
|
|
|
* @param string $length length for character types. |
591
|
|
|
* This parameter is available since version 2.0.4. |
592
|
|
|
*/ |
593
|
|
|
protected function extractColumnSize($column, $dbType, $precision, $scale, $length) |
|
|
|
|
594
|
|
|
{ |
595
|
|
|
$column->size = trim($length) === '' ? null : (int) $length; |
596
|
|
|
$column->precision = trim($precision) === '' ? null : (int) $precision; |
597
|
|
|
$column->scale = trim($scale) === '' ? null : (int) $scale; |
598
|
|
|
} |
599
|
|
|
|
600
|
|
|
/** |
601
|
|
|
* {@inheritdoc} |
602
|
|
|
*/ |
603
|
|
|
public function insert($table, $columns) |
604
|
|
|
{ |
605
|
|
|
$params = []; |
606
|
|
|
$returnParams = []; |
607
|
|
|
$sql = $this->db->getQueryBuilder()->insert($table, $columns, $params); |
608
|
|
|
$tableSchema = $this->getTableSchema($table); |
609
|
|
|
$returnColumns = $tableSchema->primaryKey; |
610
|
|
|
if (!empty($returnColumns)) { |
611
|
|
|
$columnSchemas = $tableSchema->columns; |
612
|
|
|
$returning = []; |
613
|
|
|
foreach ((array) $returnColumns as $name) { |
614
|
|
|
$phName = QueryBuilder::PARAM_PREFIX . (count($params) + count($returnParams)); |
615
|
|
|
$returnParams[$phName] = [ |
616
|
|
|
'column' => $name, |
617
|
|
|
'value' => null, |
618
|
|
|
]; |
619
|
|
|
if (!isset($columnSchemas[$name]) || $columnSchemas[$name]->phpType !== 'integer') { |
620
|
|
|
$returnParams[$phName]['dataType'] = \PDO::PARAM_STR; |
621
|
|
|
} else { |
622
|
|
|
$returnParams[$phName]['dataType'] = \PDO::PARAM_INT; |
623
|
|
|
} |
624
|
|
|
$returnParams[$phName]['size'] = isset($columnSchemas[$name]->size) ? $columnSchemas[$name]->size : -1; |
625
|
|
|
$returning[] = $this->quoteColumnName($name); |
626
|
|
|
} |
627
|
|
|
$sql .= ' RETURNING ' . implode(', ', $returning) . ' INTO ' . implode(', ', array_keys($returnParams)); |
628
|
|
|
} |
629
|
|
|
|
630
|
|
|
$command = $this->db->createCommand($sql, $params); |
631
|
|
|
$command->prepare(false); |
632
|
|
|
|
633
|
|
|
foreach ($returnParams as $name => &$value) { |
634
|
|
|
$command->pdoStatement->bindParam($name, $value['value'], $value['dataType'], $value['size']); |
635
|
|
|
} |
636
|
|
|
|
637
|
|
|
if (!$command->execute()) { |
638
|
|
|
return false; |
639
|
|
|
} |
640
|
|
|
|
641
|
|
|
$result = []; |
642
|
|
|
foreach ($returnParams as $value) { |
643
|
|
|
$result[$value['column']] = $value['value']; |
644
|
|
|
} |
645
|
|
|
|
646
|
|
|
return $result; |
647
|
|
|
} |
648
|
|
|
|
649
|
|
|
/** |
650
|
|
|
* Loads multiple types of constraints and returns the specified ones. |
651
|
|
|
* @param string $tableName table name. |
652
|
|
|
* @param string $returnType return type: |
653
|
|
|
* - primaryKey |
654
|
|
|
* - foreignKeys |
655
|
|
|
* - uniques |
656
|
|
|
* - checks |
657
|
|
|
* @return mixed constraints. |
658
|
|
|
*/ |
659
|
|
|
private function loadTableConstraints($tableName, $returnType) |
660
|
|
|
{ |
661
|
|
|
static $sql = <<<'SQL' |
662
|
|
|
SELECT |
663
|
|
|
/*+ PUSH_PRED("uc") PUSH_PRED("uccol") PUSH_PRED("fuc") */ |
664
|
|
|
"uc"."CONSTRAINT_NAME" AS "name", |
665
|
|
|
"uccol"."COLUMN_NAME" AS "column_name", |
666
|
|
|
"uc"."CONSTRAINT_TYPE" AS "type", |
667
|
|
|
"fuc"."OWNER" AS "foreign_table_schema", |
668
|
|
|
"fuc"."TABLE_NAME" AS "foreign_table_name", |
669
|
|
|
"fuccol"."COLUMN_NAME" AS "foreign_column_name", |
670
|
|
|
"uc"."DELETE_RULE" AS "on_delete", |
671
|
|
|
"uc"."SEARCH_CONDITION" AS "check_expr" |
672
|
|
|
FROM "USER_CONSTRAINTS" "uc" |
673
|
|
|
INNER JOIN "USER_CONS_COLUMNS" "uccol" |
674
|
|
|
ON "uccol"."OWNER" = "uc"."OWNER" AND "uccol"."CONSTRAINT_NAME" = "uc"."CONSTRAINT_NAME" |
675
|
|
|
LEFT JOIN "USER_CONSTRAINTS" "fuc" |
676
|
|
|
ON "fuc"."OWNER" = "uc"."R_OWNER" AND "fuc"."CONSTRAINT_NAME" = "uc"."R_CONSTRAINT_NAME" |
677
|
|
|
LEFT JOIN "USER_CONS_COLUMNS" "fuccol" |
678
|
|
|
ON "fuccol"."OWNER" = "fuc"."OWNER" AND "fuccol"."CONSTRAINT_NAME" = "fuc"."CONSTRAINT_NAME" AND "fuccol"."POSITION" = "uccol"."POSITION" |
679
|
|
|
WHERE "uc"."OWNER" = :schemaName AND "uc"."TABLE_NAME" = :tableName |
680
|
|
|
ORDER BY "uccol"."POSITION" ASC |
681
|
|
|
SQL; |
682
|
|
|
|
683
|
|
|
$resolvedName = $this->resolveTableName($tableName); |
684
|
|
|
$constraints = $this->db->createCommand($sql, [ |
685
|
|
|
':schemaName' => $resolvedName->schemaName, |
686
|
|
|
':tableName' => $resolvedName->name, |
687
|
|
|
])->queryAll(); |
688
|
|
|
$constraints = $this->normalizePdoRowKeyCase($constraints, true); |
689
|
|
|
$constraints = ArrayHelper::index($constraints, null, ['type', 'name']); |
690
|
|
|
$result = [ |
691
|
|
|
'primaryKey' => null, |
692
|
|
|
'foreignKeys' => [], |
693
|
|
|
'uniques' => [], |
694
|
|
|
'checks' => [], |
695
|
|
|
]; |
696
|
|
|
foreach ($constraints as $type => $names) { |
697
|
|
|
foreach ($names as $name => $constraint) { |
698
|
|
|
switch ($type) { |
699
|
|
|
case 'P': |
700
|
|
|
$result['primaryKey'] = new Constraint([ |
701
|
|
|
'name' => $name, |
702
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
703
|
|
|
]); |
704
|
|
|
break; |
705
|
|
|
case 'R': |
706
|
|
|
$result['foreignKeys'][] = new ForeignKeyConstraint([ |
707
|
|
|
'name' => $name, |
708
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
709
|
|
|
'foreignSchemaName' => $constraint[0]['foreign_table_schema'], |
710
|
|
|
'foreignTableName' => $constraint[0]['foreign_table_name'], |
711
|
|
|
'foreignColumnNames' => ArrayHelper::getColumn($constraint, 'foreign_column_name'), |
712
|
|
|
'onDelete' => $constraint[0]['on_delete'], |
713
|
|
|
'onUpdate' => null, |
714
|
|
|
]); |
715
|
|
|
break; |
716
|
|
|
case 'U': |
717
|
|
|
$result['uniques'][] = new Constraint([ |
718
|
|
|
'name' => $name, |
719
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
720
|
|
|
]); |
721
|
|
|
break; |
722
|
|
|
case 'C': |
723
|
|
|
$result['checks'][] = new CheckConstraint([ |
724
|
|
|
'name' => $name, |
725
|
|
|
'columnNames' => ArrayHelper::getColumn($constraint, 'column_name'), |
726
|
|
|
'expression' => $constraint[0]['check_expr'], |
727
|
|
|
]); |
728
|
|
|
break; |
729
|
|
|
} |
730
|
|
|
} |
731
|
|
|
} |
732
|
|
|
foreach ($result as $type => $data) { |
733
|
|
|
$this->setTableMetadata($tableName, $type, $data); |
734
|
|
|
} |
735
|
|
|
|
736
|
|
|
return $result[$returnType]; |
737
|
|
|
} |
738
|
|
|
} |
739
|
|
|
|
This check looks for parameters that have been defined for a function or method, but which are not used in the method body.