OracleSchemaManager.php 11 KB

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