OracleSchemaManager.php 12.5 KB
Newer Older
romanb's avatar
romanb committed
1 2
<?php

3
namespace Doctrine\DBAL\Schema;
romanb's avatar
romanb committed
4

5 6
use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Driver\DriverException;
7
use Doctrine\DBAL\Platforms\OraclePlatform;
8
use Doctrine\DBAL\Types\Type;
9 10 11
use const CASE_LOWER;
use function array_change_key_case;
use function array_values;
12
use function assert;
13 14 15 16 17 18
use function preg_match;
use function sprintf;
use function strpos;
use function strtolower;
use function strtoupper;
use function trim;
19

romanb's avatar
romanb committed
20
/**
Benjamin Morel's avatar
Benjamin Morel committed
21
 * Oracle Schema Manager.
romanb's avatar
romanb committed
22
 */
23
class OracleSchemaManager extends AbstractSchemaManager
24
{
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
    /**
     * {@inheritdoc}
     */
    public function dropDatabase($database)
    {
        try {
            parent::dropDatabase($database);
        } catch (DBALException $exception) {
            $exception = $exception->getPrevious();

            if (! $exception instanceof DriverException) {
                throw $exception;
            }

            // If we have a error code 1940 (ORA-01940), the drop database operation failed
            // because of active connections on the database.
            // To force dropping the database, we first have to close all active connections
            // on that database and issue the drop database operation again.
            if ($exception->getErrorCode() !== 1940) {
                throw $exception;
            }

            $this->killUserSessions($database);

            parent::dropDatabase($database);
        }
    }

Benjamin Morel's avatar
Benjamin Morel committed
53 54 55
    /**
     * {@inheritdoc}
     */
56
    protected function _getPortableViewDefinition($view)
romanb's avatar
romanb committed
57
    {
58
        $view = array_change_key_case($view, CASE_LOWER);
59

60
        return new View($this->getQuotedIdentifierName($view['view_name']), $view['text']);
romanb's avatar
romanb committed
61 62
    }

Benjamin Morel's avatar
Benjamin Morel committed
63 64 65
    /**
     * {@inheritdoc}
     */
66
    protected function _getPortableUserDefinition($user)
romanb's avatar
romanb committed
67
    {
68
        $user = array_change_key_case($user, CASE_LOWER);
69

70
        return [
71
            'user' => $user['username'],
72
        ];
73
    }
romanb's avatar
romanb committed
74

Benjamin Morel's avatar
Benjamin Morel committed
75 76 77
    /**
     * {@inheritdoc}
     */
78 79
    protected function _getPortableTableDefinition($table)
    {
80
        $table = array_change_key_case($table, CASE_LOWER);
81

82
        return $this->getQuotedIdentifierName($table['table_name']);
romanb's avatar
romanb committed
83 84
    }

85
    /**
Benjamin Morel's avatar
Benjamin Morel committed
86 87
     * {@inheritdoc}
     *
88 89
     * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
     */
90
    protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
romanb's avatar
romanb committed
91
    {
92
        $indexBuffer = [];
Steve Müller's avatar
Steve Müller committed
93
        foreach ($tableIndexes as $tableIndex) {
94
            $tableIndex = array_change_key_case($tableIndex, CASE_LOWER);
95 96

            $keyName = strtolower($tableIndex['name']);
97
            $buffer  = [];
98

99 100 101
            if (strtolower($tableIndex['is_primary']) === 'p') {
                $keyName              = 'primary';
                $buffer['primary']    = true;
102 103
                $buffer['non_unique'] = false;
            } else {
104
                $buffer['primary']    = false;
105
                $buffer['non_unique'] = ! $tableIndex['is_unique'];
106
            }
107
            $buffer['key_name']    = $keyName;
108
            $buffer['column_name'] = $this->getQuotedIdentifierName($tableIndex['column_name']);
109
            $indexBuffer[]         = $buffer;
110
        }
Benjamin Morel's avatar
Benjamin Morel committed
111

112
        return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
113
    }
romanb's avatar
romanb committed
114

Benjamin Morel's avatar
Benjamin Morel committed
115 116 117
    /**
     * {@inheritdoc}
     */
118 119
    protected function _getPortableTableColumnDefinition($tableColumn)
    {
120
        $tableColumn = array_change_key_case($tableColumn, CASE_LOWER);
121

122
        $dbType = strtolower($tableColumn['data_type']);
123 124 125
        if (strpos($dbType, 'timestamp(') === 0) {
            if (strpos($dbType, 'with time zone')) {
                $dbType = 'timestamptz';
126
            } else {
127
                $dbType = 'timestamp';
128
            }
129 130
        }

Benjamin Morel's avatar
Benjamin Morel committed
131
        $unsigned = $fixed = null;
romanb's avatar
romanb committed
132

133
        if (! isset($tableColumn['column_name'])) {
134 135
            $tableColumn['column_name'] = '';
        }
romanb's avatar
romanb committed
136

137 138 139 140
        // Default values returned from database sometimes have trailing spaces.
        $tableColumn['data_default'] = trim($tableColumn['data_default']);

        if ($tableColumn['data_default'] === '' || $tableColumn['data_default'] === 'NULL') {
141
            $tableColumn['data_default'] = null;
romanb's avatar
romanb committed
142 143
        }

144
        if ($tableColumn['data_default'] !== null) {
145
            // Default values returned from database are enclosed in single quotes.
146
            $tableColumn['data_default'] = trim($tableColumn['data_default'], "'");
147 148
        }

149
        $precision = null;
150
        $scale     = null;
151

152 153
        $type                    = $this->_platform->getDoctrineTypeMapping($dbType);
        $type                    = $this->extractDoctrineTypeFromComment($tableColumn['comments'], $type);
154 155
        $tableColumn['comments'] = $this->removeDoctrineTypeFromComment($tableColumn['comments'], $type);

156
        switch ($dbType) {
157
            case 'number':
158
                if ($tableColumn['data_precision'] === 20 && $tableColumn['data_scale'] === 0) {
159
                    $precision = 20;
160 161 162 163
                    $scale     = 0;
                    $type      = 'bigint';
                } elseif ($tableColumn['data_precision'] === 5 && $tableColumn['data_scale'] === 0) {
                    $type      = 'smallint';
164
                    $precision = 5;
165 166
                    $scale     = 0;
                } elseif ($tableColumn['data_precision'] === 1 && $tableColumn['data_scale'] === 0) {
167
                    $precision = 1;
168 169
                    $scale     = 0;
                    $type      = 'boolean';
170
                } elseif ($tableColumn['data_scale'] > 0) {
171
                    $precision = $tableColumn['data_precision'];
172 173
                    $scale     = $tableColumn['data_scale'];
                    $type      = 'decimal';
174
                }
175 176
                $length = null;
                break;
177 178
            case 'pls_integer':
            case 'binary_integer':
179
                $length = null;
180 181 182 183
                break;
            case 'varchar':
            case 'varchar2':
            case 'nvarchar2':
184
                $length = $tableColumn['char_length'];
185
                $fixed  = false;
186
                break;
187 188
            case 'char':
            case 'nchar':
189
                $length = $tableColumn['char_length'];
190
                $fixed  = true;
191 192 193 194 195 196
                break;
            case 'date':
            case 'timestamp':
                $length = null;
                break;
            case 'float':
197 198
            case 'binary_float':
            case 'binary_double':
199
                $precision = $tableColumn['data_precision'];
200 201
                $scale     = $tableColumn['data_scale'];
                $length    = null;
202 203 204
                break;
            case 'clob':
            case 'nclob':
205
                $length = null;
206 207 208 209 210 211
                break;
            case 'blob':
            case 'raw':
            case 'long raw':
            case 'bfile':
                $length = null;
212
                break;
213 214 215 216 217
            case 'rowid':
            case 'urowid':
            default:
                $length = null;
        }
romanb's avatar
romanb committed
218

219
        $options = [
220 221 222 223 224 225 226
            'notnull'    => (bool) ($tableColumn['nullable'] === 'N'),
            'fixed'      => (bool) $fixed,
            'unsigned'   => (bool) $unsigned,
            'default'    => $tableColumn['data_default'],
            'length'     => $length,
            'precision'  => $precision,
            'scale'      => $scale,
227
            'comment'    => isset($tableColumn['comments']) && $tableColumn['comments'] !== ''
228 229
                ? $tableColumn['comments']
                : null,
230
        ];
231

232
        return new Column($this->getQuotedIdentifierName($tableColumn['column_name']), Type::getType($type), $options);
233 234
    }

Benjamin Morel's avatar
Benjamin Morel committed
235 236 237
    /**
     * {@inheritdoc}
     */
238 239
    protected function _getPortableTableForeignKeysList($tableForeignKeys)
    {
240
        $list = [];
241
        foreach ($tableForeignKeys as $value) {
242 243 244
            $value = array_change_key_case($value, CASE_LOWER);
            if (! isset($list[$value['constraint_name']])) {
                if ($value['delete_rule'] === 'NO ACTION') {
245 246 247
                    $value['delete_rule'] = null;
                }

248
                $list[$value['constraint_name']] = [
249
                    'name' => $this->getQuotedIdentifierName($value['constraint_name']),
250 251
                    'local' => [],
                    'foreign' => [],
252
                    'foreignTable' => $value['references_table'],
253
                    'onDelete' => $value['delete_rule'],
254
                ];
255
            }
256

257
            $localColumn   = $this->getQuotedIdentifierName($value['local_column']);
258 259
            $foreignColumn = $this->getQuotedIdentifierName($value['foreign_column']);

260
            $list[$value['constraint_name']]['local'][$value['position']]   = $localColumn;
261
            $list[$value['constraint_name']]['foreign'][$value['position']] = $foreignColumn;
262 263
        }

264
        $result = [];
Steve Müller's avatar
Steve Müller committed
265
        foreach ($list as $constraint) {
266
            $result[] = new ForeignKeyConstraint(
267 268 269 270
                array_values($constraint['local']),
                $this->getQuotedIdentifierName($constraint['foreignTable']),
                array_values($constraint['foreign']),
                $this->getQuotedIdentifierName($constraint['name']),
271
                ['onDelete' => $constraint['onDelete']]
272 273 274 275 276 277
            );
        }

        return $result;
    }

Benjamin Morel's avatar
Benjamin Morel committed
278 279 280
    /**
     * {@inheritdoc}
     */
281 282
    protected function _getPortableSequenceDefinition($sequence)
    {
283
        $sequence = array_change_key_case($sequence, CASE_LOWER);
Benjamin Morel's avatar
Benjamin Morel committed
284

285 286
        return new Sequence(
            $this->getQuotedIdentifierName($sequence['sequence_name']),
287 288
            (int) $sequence['increment_by'],
            (int) $sequence['min_value']
289
        );
romanb's avatar
romanb committed
290 291
    }

Benjamin Morel's avatar
Benjamin Morel committed
292 293 294
    /**
     * {@inheritdoc}
     */
295 296
    protected function _getPortableFunctionDefinition($function)
    {
297
        $function = array_change_key_case($function, CASE_LOWER);
Benjamin Morel's avatar
Benjamin Morel committed
298

299 300
        return $function['name'];
    }
romanb's avatar
romanb committed
301

Benjamin Morel's avatar
Benjamin Morel committed
302 303 304
    /**
     * {@inheritdoc}
     */
305 306
    protected function _getPortableDatabaseDefinition($database)
    {
307
        $database = array_change_key_case($database, CASE_LOWER);
Benjamin Morel's avatar
Benjamin Morel committed
308

309 310
        return $database['username'];
    }
romanb's avatar
romanb committed
311

Benjamin Morel's avatar
Benjamin Morel committed
312 313 314
    /**
     * {@inheritdoc}
     */
315 316
    public function createDatabase($database = null)
    {
317
        if ($database === null) {
318 319
            $database = $this->_conn->getDatabase();
        }
romanb's avatar
romanb committed
320

321 322 323
        $params   = $this->_conn->getParams();
        $username = $database;
        $password = $params['password'];
romanb's avatar
romanb committed
324

325
        $query = 'CREATE USER ' . $username . ' IDENTIFIED BY ' . $password;
Benjamin Morel's avatar
Benjamin Morel committed
326
        $this->_conn->executeUpdate($query);
327

328
        $query = 'GRANT DBA TO ' . $username;
Benjamin Morel's avatar
Benjamin Morel committed
329
        $this->_conn->executeUpdate($query);
romanb's avatar
romanb committed
330
    }
331

Benjamin Morel's avatar
Benjamin Morel committed
332 333 334
    /**
     * @param string $table
     *
335
     * @return bool
Benjamin Morel's avatar
Benjamin Morel committed
336
     */
337
    public function dropAutoincrement($table)
romanb's avatar
romanb committed
338
    {
339 340
        assert($this->_platform instanceof OraclePlatform);

341 342
        $sql = $this->_platform->getDropAutoincrementSql($table);
        foreach ($sql as $query) {
343
            $this->_conn->executeUpdate($query);
344 345 346
        }

        return true;
romanb's avatar
romanb committed
347 348
    }

Benjamin Morel's avatar
Benjamin Morel committed
349 350 351
    /**
     * {@inheritdoc}
     */
romanb's avatar
romanb committed
352 353
    public function dropTable($name)
    {
354
        $this->tryMethod('dropAutoincrement', $name);
romanb's avatar
romanb committed
355

Benjamin Morel's avatar
Benjamin Morel committed
356
        parent::dropTable($name);
romanb's avatar
romanb committed
357
    }
358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376

    /**
     * Returns the quoted representation of the given identifier name.
     *
     * Quotes non-uppercase identifiers explicitly to preserve case
     * and thus make references to the particular identifier work.
     *
     * @param string $identifier The identifier to quote.
     *
     * @return string The quoted identifier.
     */
    private function getQuotedIdentifierName($identifier)
    {
        if (preg_match('/[a-z]/', $identifier)) {
            return $this->_platform->quoteIdentifier($identifier);
        }

        return $identifier;
    }
377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400

    /**
     * Kills sessions connected with the given user.
     *
     * This is useful to force DROP USER operations which could fail because of active user sessions.
     *
     * @param string $user The name of the user to kill sessions for.
     *
     * @return void
     */
    private function killUserSessions($user)
    {
        $sql = <<<SQL
SELECT
    s.sid,
    s.serial#
FROM
    gv\$session s,
    gv\$process p
WHERE
    s.username = ?
    AND p.addr(+) = s.paddr
SQL;

401
        $activeUserSessions = $this->_conn->fetchAll($sql, [strtoupper($user)]);
402 403

        foreach ($activeUserSessions as $activeUserSession) {
404
            $activeUserSession = array_change_key_case($activeUserSession, CASE_LOWER);
405 406 407 408 409 410 411 412 413 414

            $this->_execSql(
                sprintf(
                    "ALTER SYSTEM KILL SESSION '%s, %s' IMMEDIATE",
                    $activeUserSession['sid'],
                    $activeUserSession['serial#']
                )
            );
        }
    }
415
}