1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Doctrine\DBAL\Schema; |
4
|
|
|
|
5
|
|
|
use Doctrine\DBAL\DBALException; |
6
|
|
|
use Doctrine\DBAL\Driver\DriverException; |
7
|
|
|
use Doctrine\DBAL\Platforms\OraclePlatform; |
8
|
|
|
use Doctrine\DBAL\Types\Type; |
9
|
|
|
use const CASE_LOWER; |
10
|
|
|
use function array_change_key_case; |
11
|
|
|
use function array_values; |
12
|
|
|
use function assert; |
13
|
|
|
use function preg_match; |
14
|
|
|
use function sprintf; |
15
|
|
|
use function strpos; |
16
|
|
|
use function strtolower; |
17
|
|
|
use function strtoupper; |
18
|
|
|
use function trim; |
19
|
|
|
|
20
|
|
|
/** |
21
|
|
|
* Oracle Schema Manager. |
22
|
|
|
*/ |
23
|
|
|
class OracleSchemaManager extends AbstractSchemaManager |
24
|
|
|
{ |
25
|
|
|
/** |
26
|
|
|
* {@inheritdoc} |
27
|
|
|
*/ |
28
|
4 |
|
public function dropDatabase($database) |
29
|
|
|
{ |
30
|
|
|
try { |
31
|
4 |
|
parent::dropDatabase($database); |
32
|
4 |
|
} catch (DBALException $exception) { |
33
|
4 |
|
$exception = $exception->getPrevious(); |
34
|
|
|
|
35
|
4 |
|
if (! $exception instanceof DriverException) { |
36
|
|
|
throw $exception; |
37
|
|
|
} |
38
|
|
|
|
39
|
|
|
// If we have a error code 1940 (ORA-01940), the drop database operation failed |
40
|
|
|
// because of active connections on the database. |
41
|
|
|
// To force dropping the database, we first have to close all active connections |
42
|
|
|
// on that database and issue the drop database operation again. |
43
|
4 |
|
if ($exception->getErrorCode() !== 1940) { |
44
|
4 |
|
throw $exception; |
45
|
|
|
} |
46
|
|
|
|
47
|
2 |
|
$this->killUserSessions($database); |
48
|
|
|
|
49
|
2 |
|
parent::dropDatabase($database); |
50
|
|
|
} |
51
|
2 |
|
} |
52
|
|
|
|
53
|
|
|
/** |
54
|
|
|
* {@inheritdoc} |
55
|
|
|
*/ |
56
|
2 |
|
protected function _getPortableViewDefinition($view) |
57
|
|
|
{ |
58
|
2 |
|
$view = array_change_key_case($view, CASE_LOWER); |
59
|
|
|
|
60
|
2 |
|
return new View($this->getQuotedIdentifierName($view['view_name']), $view['text']); |
61
|
|
|
} |
62
|
|
|
|
63
|
|
|
/** |
64
|
|
|
* {@inheritdoc} |
65
|
|
|
*/ |
66
|
|
|
protected function _getPortableUserDefinition($user) |
67
|
|
|
{ |
68
|
|
|
$user = array_change_key_case($user, CASE_LOWER); |
69
|
|
|
|
70
|
|
|
return [ |
71
|
|
|
'user' => $user['username'], |
72
|
|
|
]; |
73
|
|
|
} |
74
|
|
|
|
75
|
|
|
/** |
76
|
|
|
* {@inheritdoc} |
77
|
|
|
*/ |
78
|
126 |
|
protected function _getPortableTableDefinition($table) |
79
|
|
|
{ |
80
|
126 |
|
$table = array_change_key_case($table, CASE_LOWER); |
81
|
|
|
|
82
|
126 |
|
return $this->getQuotedIdentifierName($table['table_name']); |
83
|
|
|
} |
84
|
|
|
|
85
|
|
|
/** |
86
|
|
|
* {@inheritdoc} |
87
|
|
|
* |
88
|
|
|
* @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html |
89
|
|
|
*/ |
90
|
54 |
|
protected function _getPortableTableIndexesList($tableIndexes, $tableName = null) |
91
|
|
|
{ |
92
|
54 |
|
$indexBuffer = []; |
93
|
54 |
|
foreach ($tableIndexes as $tableIndex) { |
94
|
30 |
|
$tableIndex = array_change_key_case($tableIndex, CASE_LOWER); |
95
|
|
|
|
96
|
30 |
|
$keyName = strtolower($tableIndex['name']); |
97
|
30 |
|
$buffer = []; |
98
|
|
|
|
99
|
30 |
|
if (strtolower($tableIndex['is_primary']) === 'p') { |
100
|
26 |
|
$keyName = 'primary'; |
101
|
26 |
|
$buffer['primary'] = true; |
102
|
26 |
|
$buffer['non_unique'] = false; |
103
|
|
|
} else { |
104
|
24 |
|
$buffer['primary'] = false; |
105
|
24 |
|
$buffer['non_unique'] = ! $tableIndex['is_unique']; |
106
|
|
|
} |
107
|
30 |
|
$buffer['key_name'] = $keyName; |
108
|
30 |
|
$buffer['column_name'] = $this->getQuotedIdentifierName($tableIndex['column_name']); |
109
|
30 |
|
$indexBuffer[] = $buffer; |
110
|
|
|
} |
111
|
|
|
|
112
|
54 |
|
return parent::_getPortableTableIndexesList($indexBuffer, $tableName); |
113
|
|
|
} |
114
|
|
|
|
115
|
|
|
/** |
116
|
|
|
* {@inheritdoc} |
117
|
|
|
*/ |
118
|
68 |
|
protected function _getPortableTableColumnDefinition($tableColumn) |
119
|
|
|
{ |
120
|
68 |
|
$tableColumn = array_change_key_case($tableColumn, CASE_LOWER); |
121
|
|
|
|
122
|
68 |
|
$dbType = strtolower($tableColumn['data_type']); |
123
|
68 |
|
if (strpos($dbType, 'timestamp(') === 0) { |
124
|
14 |
|
if (strpos($dbType, 'with time zone')) { |
125
|
6 |
|
$dbType = 'timestamptz'; |
126
|
|
|
} else { |
127
|
14 |
|
$dbType = 'timestamp'; |
128
|
|
|
} |
129
|
|
|
} |
130
|
|
|
|
131
|
68 |
|
$unsigned = $fixed = $precision = $scale = $length = null; |
132
|
|
|
|
133
|
68 |
|
if (! isset($tableColumn['column_name'])) { |
134
|
|
|
$tableColumn['column_name'] = ''; |
135
|
|
|
} |
136
|
|
|
|
137
|
|
|
// Default values returned from database sometimes have trailing spaces. |
138
|
68 |
|
$tableColumn['data_default'] = trim($tableColumn['data_default']); |
139
|
|
|
|
140
|
68 |
|
if ($tableColumn['data_default'] === '' || $tableColumn['data_default'] === 'NULL') { |
141
|
64 |
|
$tableColumn['data_default'] = null; |
142
|
|
|
} |
143
|
|
|
|
144
|
68 |
|
if ($tableColumn['data_default'] !== null) { |
145
|
|
|
// Default values returned from database are enclosed in single quotes. |
146
|
18 |
|
$tableColumn['data_default'] = trim($tableColumn['data_default'], "'"); |
147
|
|
|
} |
148
|
|
|
|
149
|
68 |
|
if ($tableColumn['data_precision'] !== null) { |
150
|
68 |
|
$precision = (int) $tableColumn['data_precision']; |
151
|
|
|
} |
152
|
68 |
|
|
153
|
68 |
|
if ($tableColumn['data_scale'] !== null) { |
154
|
68 |
|
$scale = (int) $tableColumn['data_scale']; |
155
|
|
|
} |
156
|
68 |
|
|
157
|
68 |
|
$type = $this->_platform->getDoctrineTypeMapping($dbType); |
158
|
60 |
|
$type = $this->extractDoctrineTypeFromComment($tableColumn['comments'], $type); |
159
|
|
|
$tableColumn['comments'] = $this->removeDoctrineTypeFromComment($tableColumn['comments'], $type); |
160
|
|
|
|
161
|
|
|
switch ($dbType) { |
162
|
60 |
|
case 'number': |
163
|
|
|
if ($precision === 20 && $scale === 0) { |
164
|
|
|
$type = 'bigint'; |
165
|
|
|
} elseif ($precision === 5 && $scale === 0) { |
166
|
60 |
|
$type = 'smallint'; |
167
|
|
|
} elseif ($precision === 1 && $scale === 0) { |
168
|
|
|
$type = 'boolean'; |
169
|
|
|
} elseif ($scale > 0) { |
170
|
60 |
|
$type = 'decimal'; |
171
|
10 |
|
} |
172
|
10 |
|
|
173
|
10 |
|
break; |
174
|
|
|
case 'varchar': |
175
|
60 |
|
case 'varchar2': |
176
|
60 |
|
case 'nvarchar2': |
177
|
38 |
|
$length = $tableColumn['char_length']; |
178
|
38 |
|
$fixed = false; |
179
|
|
|
break; |
180
|
|
|
case 'char': |
181
|
38 |
|
case 'nchar': |
182
|
38 |
|
$length = $tableColumn['char_length']; |
183
|
28 |
|
$fixed = true; |
184
|
22 |
|
break; |
185
|
22 |
|
} |
186
|
22 |
|
|
187
|
28 |
|
$options = [ |
188
|
24 |
|
'notnull' => (bool) ($tableColumn['nullable'] === 'N'), |
189
|
10 |
|
'fixed' => (bool) $fixed, |
190
|
10 |
|
'unsigned' => (bool) $unsigned, |
191
|
10 |
|
'default' => $tableColumn['data_default'], |
192
|
24 |
|
'length' => $length, |
193
|
24 |
|
'precision' => $precision, |
194
|
14 |
|
'scale' => $scale, |
195
|
14 |
|
'comment' => isset($tableColumn['comments']) && $tableColumn['comments'] !== '' |
196
|
23 |
|
? $tableColumn['comments'] |
197
|
23 |
|
: null, |
198
|
23 |
|
]; |
199
|
|
|
|
200
|
|
|
return new Column($this->getQuotedIdentifierName($tableColumn['column_name']), Type::getType($type), $options); |
201
|
|
|
} |
202
|
|
|
|
203
|
23 |
|
/** |
204
|
11 |
|
* {@inheritdoc} |
205
|
17 |
|
*/ |
206
|
17 |
|
protected function _getPortableTableForeignKeysList($tableForeignKeys) |
207
|
11 |
|
{ |
208
|
8 |
|
$list = []; |
209
|
6 |
|
foreach ($tableForeignKeys as $value) { |
210
|
6 |
|
$value = array_change_key_case($value, CASE_LOWER); |
211
|
9 |
|
if (! isset($list[$value['constraint_name']])) { |
212
|
9 |
|
if ($value['delete_rule'] === 'NO ACTION') { |
213
|
6 |
|
$value['delete_rule'] = null; |
214
|
6 |
|
} |
215
|
|
|
|
216
|
6 |
|
$list[$value['constraint_name']] = [ |
217
|
|
|
'name' => $this->getQuotedIdentifierName($value['constraint_name']), |
218
|
|
|
'local' => [], |
219
|
|
|
'foreign' => [], |
220
|
68 |
|
'foreignTable' => $value['references_table'], |
221
|
68 |
|
'onDelete' => $value['delete_rule'], |
222
|
68 |
|
]; |
223
|
68 |
|
} |
224
|
68 |
|
|
225
|
68 |
|
$localColumn = $this->getQuotedIdentifierName($value['local_column']); |
226
|
68 |
|
$foreignColumn = $this->getQuotedIdentifierName($value['foreign_column']); |
227
|
68 |
|
|
228
|
34 |
|
$list[$value['constraint_name']]['local'][$value['position']] = $localColumn; |
229
|
|
|
$list[$value['constraint_name']]['foreign'][$value['position']] = $foreignColumn; |
230
|
|
|
} |
231
|
|
|
|
232
|
68 |
|
$result = []; |
233
|
|
|
foreach ($list as $constraint) { |
234
|
|
|
$result[] = new ForeignKeyConstraint( |
235
|
|
|
array_values($constraint['local']), |
236
|
|
|
$this->getQuotedIdentifierName($constraint['foreignTable']), |
237
|
|
|
array_values($constraint['foreign']), |
238
|
48 |
|
$this->getQuotedIdentifierName($constraint['name']), |
239
|
|
|
['onDelete' => $constraint['onDelete']] |
240
|
48 |
|
); |
241
|
48 |
|
} |
242
|
18 |
|
|
243
|
18 |
|
return $result; |
244
|
18 |
|
} |
245
|
16 |
|
|
246
|
|
|
/** |
247
|
|
|
* {@inheritdoc} |
248
|
18 |
|
*/ |
249
|
18 |
|
protected function _getPortableSequenceDefinition($sequence) |
250
|
|
|
{ |
251
|
|
|
$sequence = array_change_key_case($sequence, CASE_LOWER); |
252
|
18 |
|
|
253
|
18 |
|
return new Sequence( |
254
|
|
|
$this->getQuotedIdentifierName($sequence['sequence_name']), |
255
|
|
|
(int) $sequence['increment_by'], |
256
|
|
|
(int) $sequence['min_value'] |
257
|
18 |
|
); |
258
|
18 |
|
} |
259
|
|
|
|
260
|
18 |
|
/** |
261
|
18 |
|
* {@inheritdoc} |
262
|
|
|
*/ |
263
|
|
|
protected function _getPortableFunctionDefinition($function) |
264
|
48 |
|
{ |
265
|
48 |
|
$function = array_change_key_case($function, CASE_LOWER); |
266
|
18 |
|
|
267
|
18 |
|
return $function['name']; |
268
|
18 |
|
} |
269
|
18 |
|
|
270
|
18 |
|
/** |
271
|
18 |
|
* {@inheritdoc} |
272
|
|
|
*/ |
273
|
|
|
protected function _getPortableDatabaseDefinition($database) |
274
|
|
|
{ |
275
|
48 |
|
$database = array_change_key_case($database, CASE_LOWER); |
276
|
|
|
|
277
|
|
|
return $database['username']; |
278
|
|
|
} |
279
|
|
|
|
280
|
|
|
/** |
281
|
9 |
|
* {@inheritdoc} |
282
|
|
|
*/ |
283
|
9 |
|
public function createDatabase($database = null) |
284
|
|
|
{ |
285
|
9 |
|
if ($database === null) { |
286
|
9 |
|
$database = $this->_conn->getDatabase(); |
287
|
9 |
|
} |
288
|
9 |
|
|
289
|
|
|
$params = $this->_conn->getParams(); |
290
|
|
|
$username = $database; |
291
|
|
|
$password = $params['password']; |
292
|
|
|
|
293
|
|
|
$query = 'CREATE USER ' . $username . ' IDENTIFIED BY ' . $password; |
294
|
|
|
$this->_conn->executeUpdate($query); |
295
|
|
|
|
296
|
|
|
$query = 'GRANT DBA TO ' . $username; |
297
|
|
|
$this->_conn->executeUpdate($query); |
298
|
|
|
} |
299
|
|
|
|
300
|
|
|
/** |
301
|
|
|
* @param string $table |
302
|
|
|
* |
303
|
|
|
* @return bool |
304
|
|
|
*/ |
305
|
4 |
|
public function dropAutoincrement($table) |
306
|
|
|
{ |
307
|
4 |
|
assert($this->_platform instanceof OraclePlatform); |
308
|
|
|
|
309
|
4 |
|
$sql = $this->_platform->getDropAutoincrementSql($table); |
310
|
|
|
foreach ($sql as $query) { |
311
|
|
|
$this->_conn->executeUpdate($query); |
312
|
|
|
} |
313
|
|
|
|
314
|
|
|
return true; |
315
|
4 |
|
} |
316
|
|
|
|
317
|
4 |
|
/** |
318
|
|
|
* {@inheritdoc} |
319
|
|
|
*/ |
320
|
|
|
public function dropTable($name) |
321
|
4 |
|
{ |
322
|
4 |
|
$this->tryMethod('dropAutoincrement', $name); |
323
|
4 |
|
|
324
|
|
|
parent::dropTable($name); |
325
|
4 |
|
} |
326
|
4 |
|
|
327
|
|
|
/** |
328
|
4 |
|
* Returns the quoted representation of the given identifier name. |
329
|
4 |
|
* |
330
|
4 |
|
* Quotes non-uppercase identifiers explicitly to preserve case |
331
|
|
|
* and thus make references to the particular identifier work. |
332
|
|
|
* |
333
|
|
|
* @param string $identifier The identifier to quote. |
334
|
|
|
* |
335
|
|
|
* @return string The quoted identifier. |
336
|
|
|
*/ |
337
|
183 |
|
private function getQuotedIdentifierName($identifier) |
338
|
|
|
{ |
339
|
183 |
|
if (preg_match('/[a-z]/', $identifier)) { |
340
|
|
|
return $this->_platform->quoteIdentifier($identifier); |
341
|
183 |
|
} |
342
|
183 |
|
|
343
|
183 |
|
return $identifier; |
344
|
|
|
} |
345
|
|
|
|
346
|
|
|
/** |
347
|
|
|
* Kills sessions connected with the given user. |
348
|
|
|
* |
349
|
|
|
* This is useful to force DROP USER operations which could fail because of active user sessions. |
350
|
|
|
* |
351
|
|
|
* @param string $user The name of the user to kill sessions for. |
352
|
183 |
|
* |
353
|
|
|
* @return void |
354
|
183 |
|
*/ |
355
|
|
|
private function killUserSessions($user) |
356
|
183 |
|
{ |
357
|
95 |
|
$sql = <<<SQL |
358
|
|
|
SELECT |
359
|
|
|
s.sid, |
360
|
|
|
s.serial# |
361
|
|
|
FROM |
362
|
|
|
gv\$session s, |
363
|
|
|
gv\$process p |
364
|
|
|
WHERE |
365
|
|
|
s.username = ? |
366
|
|
|
AND p.addr(+) = s.paddr |
367
|
|
|
SQL; |
368
|
|
|
|
369
|
129 |
|
$activeUserSessions = $this->_conn->fetchAll($sql, [strtoupper($user)]); |
370
|
|
|
|
371
|
129 |
|
foreach ($activeUserSessions as $activeUserSession) { |
372
|
103 |
|
$activeUserSession = array_change_key_case($activeUserSession, CASE_LOWER); |
373
|
|
|
|
374
|
|
|
$this->_execSql( |
375
|
129 |
|
sprintf( |
376
|
|
|
"ALTER SYSTEM KILL SESSION '%s, %s' IMMEDIATE", |
377
|
|
|
$activeUserSession['sid'], |
378
|
|
|
$activeUserSession['serial#'] |
379
|
|
|
) |
380
|
|
|
); |
381
|
|
|
} |
382
|
|
|
} |
383
|
|
|
} |
384
|
|
|
|