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