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