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