PostgreSqlSchemaManager.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481
  1. <?php
  2. namespace Doctrine\DBAL\Schema;
  3. use Doctrine\DBAL\Exception\DriverException;
  4. use Doctrine\DBAL\FetchMode;
  5. use Doctrine\DBAL\Platforms\PostgreSqlPlatform;
  6. use Doctrine\DBAL\Types\Type;
  7. use const CASE_LOWER;
  8. use function array_change_key_case;
  9. use function array_filter;
  10. use function array_keys;
  11. use function array_map;
  12. use function array_shift;
  13. use function assert;
  14. use function explode;
  15. use function implode;
  16. use function in_array;
  17. use function preg_match;
  18. use function preg_replace;
  19. use function sprintf;
  20. use function str_replace;
  21. use function stripos;
  22. use function strlen;
  23. use function strpos;
  24. use function strtolower;
  25. use function trim;
  26. /**
  27. * PostgreSQL Schema Manager.
  28. */
  29. class PostgreSqlSchemaManager extends AbstractSchemaManager
  30. {
  31. /** @var string[] */
  32. private $existingSchemaPaths;
  33. /**
  34. * Gets all the existing schema names.
  35. *
  36. * @return string[]
  37. */
  38. public function getSchemaNames()
  39. {
  40. $statement = $this->_conn->executeQuery("SELECT nspname FROM pg_namespace WHERE nspname !~ '^pg_.*' AND nspname != 'information_schema'");
  41. return $statement->fetchAll(FetchMode::COLUMN);
  42. }
  43. /**
  44. * Returns an array of schema search paths.
  45. *
  46. * This is a PostgreSQL only function.
  47. *
  48. * @return string[]
  49. */
  50. public function getSchemaSearchPaths()
  51. {
  52. $params = $this->_conn->getParams();
  53. $schema = explode(',', $this->_conn->fetchColumn('SHOW search_path'));
  54. if (isset($params['user'])) {
  55. $schema = str_replace('"$user"', $params['user'], $schema);
  56. }
  57. return array_map('trim', $schema);
  58. }
  59. /**
  60. * Gets names of all existing schemas in the current users search path.
  61. *
  62. * This is a PostgreSQL only function.
  63. *
  64. * @return string[]
  65. */
  66. public function getExistingSchemaSearchPaths()
  67. {
  68. if ($this->existingSchemaPaths === null) {
  69. $this->determineExistingSchemaSearchPaths();
  70. }
  71. return $this->existingSchemaPaths;
  72. }
  73. /**
  74. * Sets or resets the order of the existing schemas in the current search path of the user.
  75. *
  76. * This is a PostgreSQL only function.
  77. *
  78. * @return void
  79. */
  80. public function determineExistingSchemaSearchPaths()
  81. {
  82. $names = $this->getSchemaNames();
  83. $paths = $this->getSchemaSearchPaths();
  84. $this->existingSchemaPaths = array_filter($paths, static function ($v) use ($names) {
  85. return in_array($v, $names);
  86. });
  87. }
  88. /**
  89. * {@inheritdoc}
  90. */
  91. public function dropDatabase($database)
  92. {
  93. try {
  94. parent::dropDatabase($database);
  95. } catch (DriverException $exception) {
  96. // If we have a SQLSTATE 55006, the drop database operation failed
  97. // because of active connections on the database.
  98. // To force dropping the database, we first have to close all active connections
  99. // on that database and issue the drop database operation again.
  100. if ($exception->getSQLState() !== '55006') {
  101. throw $exception;
  102. }
  103. assert($this->_platform instanceof PostgreSqlPlatform);
  104. $this->_execSql(
  105. [
  106. $this->_platform->getDisallowDatabaseConnectionsSQL($database),
  107. $this->_platform->getCloseActiveDatabaseConnectionsSQL($database),
  108. ]
  109. );
  110. parent::dropDatabase($database);
  111. }
  112. }
  113. /**
  114. * {@inheritdoc}
  115. */
  116. protected function _getPortableTableForeignKeyDefinition($tableForeignKey)
  117. {
  118. $onUpdate = null;
  119. $onDelete = null;
  120. $localColumns = null;
  121. $foreignColumns = null;
  122. $foreignTable = null;
  123. if (preg_match('(ON UPDATE ([a-zA-Z0-9]+( (NULL|ACTION|DEFAULT))?))', $tableForeignKey['condef'], $match)) {
  124. $onUpdate = $match[1];
  125. }
  126. if (preg_match('(ON DELETE ([a-zA-Z0-9]+( (NULL|ACTION|DEFAULT))?))', $tableForeignKey['condef'], $match)) {
  127. $onDelete = $match[1];
  128. }
  129. if (preg_match('/FOREIGN KEY \((.+)\) REFERENCES (.+)\((.+)\)/', $tableForeignKey['condef'], $values)) {
  130. // PostgreSQL returns identifiers that are keywords with quotes, we need them later, don't get
  131. // the idea to trim them here.
  132. $localColumns = array_map('trim', explode(',', $values[1]));
  133. $foreignColumns = array_map('trim', explode(',', $values[3]));
  134. $foreignTable = $values[2];
  135. }
  136. return new ForeignKeyConstraint(
  137. $localColumns,
  138. $foreignTable,
  139. $foreignColumns,
  140. $tableForeignKey['conname'],
  141. ['onUpdate' => $onUpdate, 'onDelete' => $onDelete]
  142. );
  143. }
  144. /**
  145. * {@inheritdoc}
  146. */
  147. protected function _getPortableTriggerDefinition($trigger)
  148. {
  149. return $trigger['trigger_name'];
  150. }
  151. /**
  152. * {@inheritdoc}
  153. */
  154. protected function _getPortableViewDefinition($view)
  155. {
  156. return new View($view['schemaname'] . '.' . $view['viewname'], $view['definition']);
  157. }
  158. /**
  159. * {@inheritdoc}
  160. */
  161. protected function _getPortableUserDefinition($user)
  162. {
  163. return [
  164. 'user' => $user['usename'],
  165. 'password' => $user['passwd'],
  166. ];
  167. }
  168. /**
  169. * {@inheritdoc}
  170. */
  171. protected function _getPortableTableDefinition($table)
  172. {
  173. $schemas = $this->getExistingSchemaSearchPaths();
  174. $firstSchema = array_shift($schemas);
  175. if ($table['schema_name'] === $firstSchema) {
  176. return $table['table_name'];
  177. }
  178. return $table['schema_name'] . '.' . $table['table_name'];
  179. }
  180. /**
  181. * {@inheritdoc}
  182. *
  183. * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
  184. */
  185. protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
  186. {
  187. $buffer = [];
  188. foreach ($tableIndexes as $row) {
  189. $colNumbers = array_map('intval', explode(' ', $row['indkey']));
  190. $columnNameSql = sprintf(
  191. 'SELECT attnum, attname FROM pg_attribute WHERE attrelid=%d AND attnum IN (%s) ORDER BY attnum ASC',
  192. $row['indrelid'],
  193. implode(' ,', $colNumbers)
  194. );
  195. $stmt = $this->_conn->executeQuery($columnNameSql);
  196. $indexColumns = $stmt->fetchAll();
  197. // required for getting the order of the columns right.
  198. foreach ($colNumbers as $colNum) {
  199. foreach ($indexColumns as $colRow) {
  200. if ($colNum !== $colRow['attnum']) {
  201. continue;
  202. }
  203. $buffer[] = [
  204. 'key_name' => $row['relname'],
  205. 'column_name' => trim($colRow['attname']),
  206. 'non_unique' => ! $row['indisunique'],
  207. 'primary' => $row['indisprimary'],
  208. 'where' => $row['where'],
  209. ];
  210. }
  211. }
  212. }
  213. return parent::_getPortableTableIndexesList($buffer, $tableName);
  214. }
  215. /**
  216. * {@inheritdoc}
  217. */
  218. protected function _getPortableDatabaseDefinition($database)
  219. {
  220. return $database['datname'];
  221. }
  222. /**
  223. * {@inheritdoc}
  224. */
  225. protected function _getPortableSequencesList($sequences)
  226. {
  227. $sequenceDefinitions = [];
  228. foreach ($sequences as $sequence) {
  229. if ($sequence['schemaname'] !== 'public') {
  230. $sequenceName = $sequence['schemaname'] . '.' . $sequence['relname'];
  231. } else {
  232. $sequenceName = $sequence['relname'];
  233. }
  234. $sequenceDefinitions[$sequenceName] = $sequence;
  235. }
  236. $list = [];
  237. foreach ($this->filterAssetNames(array_keys($sequenceDefinitions)) as $sequenceName) {
  238. $list[] = $this->_getPortableSequenceDefinition($sequenceDefinitions[$sequenceName]);
  239. }
  240. return $list;
  241. }
  242. /**
  243. * {@inheritdoc}
  244. */
  245. protected function getPortableNamespaceDefinition(array $namespace)
  246. {
  247. return $namespace['nspname'];
  248. }
  249. /**
  250. * {@inheritdoc}
  251. */
  252. protected function _getPortableSequenceDefinition($sequence)
  253. {
  254. if ($sequence['schemaname'] !== 'public') {
  255. $sequenceName = $sequence['schemaname'] . '.' . $sequence['relname'];
  256. } else {
  257. $sequenceName = $sequence['relname'];
  258. }
  259. if (! isset($sequence['increment_by'], $sequence['min_value'])) {
  260. /** @var string[] $data */
  261. $data = $this->_conn->fetchAssoc('SELECT min_value, increment_by FROM ' . $this->_platform->quoteIdentifier($sequenceName));
  262. $sequence += $data;
  263. }
  264. return new Sequence($sequenceName, (int) $sequence['increment_by'], (int) $sequence['min_value']);
  265. }
  266. /**
  267. * {@inheritdoc}
  268. */
  269. protected function _getPortableTableColumnDefinition($tableColumn)
  270. {
  271. $tableColumn = array_change_key_case($tableColumn, CASE_LOWER);
  272. if (strtolower($tableColumn['type']) === 'varchar' || strtolower($tableColumn['type']) === 'bpchar') {
  273. // get length from varchar definition
  274. $length = preg_replace('~.*\(([0-9]*)\).*~', '$1', $tableColumn['complete_type']);
  275. $tableColumn['length'] = $length;
  276. }
  277. $matches = [];
  278. $autoincrement = false;
  279. if (preg_match("/^nextval\('(.*)'(::.*)?\)$/", $tableColumn['default'], $matches)) {
  280. $tableColumn['sequence'] = $matches[1];
  281. $tableColumn['default'] = null;
  282. $autoincrement = true;
  283. }
  284. if (preg_match("/^['(](.*)[')]::.*$/", $tableColumn['default'], $matches)) {
  285. $tableColumn['default'] = $matches[1];
  286. }
  287. if (stripos($tableColumn['default'], 'NULL') === 0) {
  288. $tableColumn['default'] = null;
  289. }
  290. $length = $tableColumn['length'] ?? null;
  291. if ($length === '-1' && isset($tableColumn['atttypmod'])) {
  292. $length = $tableColumn['atttypmod'] - 4;
  293. }
  294. if ((int) $length <= 0) {
  295. $length = null;
  296. }
  297. $fixed = null;
  298. if (! isset($tableColumn['name'])) {
  299. $tableColumn['name'] = '';
  300. }
  301. $precision = null;
  302. $scale = null;
  303. $jsonb = null;
  304. $dbType = strtolower($tableColumn['type']);
  305. if (strlen($tableColumn['domain_type']) && ! $this->_platform->hasDoctrineTypeMappingFor($tableColumn['type'])) {
  306. $dbType = strtolower($tableColumn['domain_type']);
  307. $tableColumn['complete_type'] = $tableColumn['domain_complete_type'];
  308. }
  309. $type = $this->_platform->getDoctrineTypeMapping($dbType);
  310. $type = $this->extractDoctrineTypeFromComment($tableColumn['comment'], $type);
  311. $tableColumn['comment'] = $this->removeDoctrineTypeFromComment($tableColumn['comment'], $type);
  312. switch ($dbType) {
  313. case 'smallint':
  314. case 'int2':
  315. $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
  316. $length = null;
  317. break;
  318. case 'int':
  319. case 'int4':
  320. case 'integer':
  321. $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
  322. $length = null;
  323. break;
  324. case 'bigint':
  325. case 'int8':
  326. $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
  327. $length = null;
  328. break;
  329. case 'bool':
  330. case 'boolean':
  331. if ($tableColumn['default'] === 'true') {
  332. $tableColumn['default'] = true;
  333. }
  334. if ($tableColumn['default'] === 'false') {
  335. $tableColumn['default'] = false;
  336. }
  337. $length = null;
  338. break;
  339. case 'text':
  340. $fixed = false;
  341. break;
  342. case 'varchar':
  343. case 'interval':
  344. case '_varchar':
  345. $fixed = false;
  346. break;
  347. case 'char':
  348. case 'bpchar':
  349. $fixed = true;
  350. break;
  351. case 'float':
  352. case 'float4':
  353. case 'float8':
  354. case 'double':
  355. case 'double precision':
  356. case 'real':
  357. case 'decimal':
  358. case 'money':
  359. case 'numeric':
  360. $tableColumn['default'] = $this->fixVersion94NegativeNumericDefaultValue($tableColumn['default']);
  361. if (preg_match('([A-Za-z]+\(([0-9]+)\,([0-9]+)\))', $tableColumn['complete_type'], $match)) {
  362. $precision = $match[1];
  363. $scale = $match[2];
  364. $length = null;
  365. }
  366. break;
  367. case 'year':
  368. $length = null;
  369. break;
  370. // PostgreSQL 9.4+ only
  371. case 'jsonb':
  372. $jsonb = true;
  373. break;
  374. }
  375. if ($tableColumn['default'] && preg_match("('([^']+)'::)", $tableColumn['default'], $match)) {
  376. $tableColumn['default'] = $match[1];
  377. }
  378. $options = [
  379. 'length' => $length,
  380. 'notnull' => (bool) $tableColumn['isnotnull'],
  381. 'default' => $tableColumn['default'],
  382. 'precision' => $precision,
  383. 'scale' => $scale,
  384. 'fixed' => $fixed,
  385. 'unsigned' => false,
  386. 'autoincrement' => $autoincrement,
  387. 'comment' => isset($tableColumn['comment']) && $tableColumn['comment'] !== ''
  388. ? $tableColumn['comment']
  389. : null,
  390. ];
  391. $column = new Column($tableColumn['field'], Type::getType($type), $options);
  392. if (isset($tableColumn['collation']) && ! empty($tableColumn['collation'])) {
  393. $column->setPlatformOption('collation', $tableColumn['collation']);
  394. }
  395. if (in_array($column->getType()->getName(), [Type::JSON_ARRAY, Type::JSON], true)) {
  396. $column->setPlatformOption('jsonb', $jsonb);
  397. }
  398. return $column;
  399. }
  400. /**
  401. * PostgreSQL 9.4 puts parentheses around negative numeric default values that need to be stripped eventually.
  402. *
  403. * @param mixed $defaultValue
  404. *
  405. * @return mixed
  406. */
  407. private function fixVersion94NegativeNumericDefaultValue($defaultValue)
  408. {
  409. if (strpos($defaultValue, '(') === 0) {
  410. return trim($defaultValue, '()');
  411. }
  412. return $defaultValue;
  413. }
  414. }