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