SqliteSchemaManager.php 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501
  1. <?php
  2. namespace Doctrine\DBAL\Schema;
  3. use Doctrine\DBAL\DBALException;
  4. use Doctrine\DBAL\DriverManager;
  5. use Doctrine\DBAL\FetchMode;
  6. use Doctrine\DBAL\Types\StringType;
  7. use Doctrine\DBAL\Types\TextType;
  8. use Doctrine\DBAL\Types\Type;
  9. use const CASE_LOWER;
  10. use function array_change_key_case;
  11. use function array_map;
  12. use function array_reverse;
  13. use function array_values;
  14. use function explode;
  15. use function file_exists;
  16. use function preg_match;
  17. use function preg_match_all;
  18. use function preg_quote;
  19. use function preg_replace;
  20. use function rtrim;
  21. use function sprintf;
  22. use function str_replace;
  23. use function strpos;
  24. use function strtolower;
  25. use function trim;
  26. use function unlink;
  27. use function usort;
  28. /**
  29. * Sqlite SchemaManager.
  30. */
  31. class SqliteSchemaManager extends AbstractSchemaManager
  32. {
  33. /**
  34. * {@inheritdoc}
  35. */
  36. public function dropDatabase($database)
  37. {
  38. if (! file_exists($database)) {
  39. return;
  40. }
  41. unlink($database);
  42. }
  43. /**
  44. * {@inheritdoc}
  45. */
  46. public function createDatabase($database)
  47. {
  48. $params = $this->_conn->getParams();
  49. $driver = $params['driver'];
  50. $options = [
  51. 'driver' => $driver,
  52. 'path' => $database,
  53. ];
  54. $conn = DriverManager::getConnection($options);
  55. $conn->connect();
  56. $conn->close();
  57. }
  58. /**
  59. * {@inheritdoc}
  60. */
  61. public function renameTable($name, $newName)
  62. {
  63. $tableDiff = new TableDiff($name);
  64. $tableDiff->fromTable = $this->listTableDetails($name);
  65. $tableDiff->newName = $newName;
  66. $this->alterTable($tableDiff);
  67. }
  68. /**
  69. * {@inheritdoc}
  70. */
  71. public function createForeignKey(ForeignKeyConstraint $foreignKey, $table)
  72. {
  73. $tableDiff = $this->getTableDiffForAlterForeignKey($foreignKey, $table);
  74. $tableDiff->addedForeignKeys[] = $foreignKey;
  75. $this->alterTable($tableDiff);
  76. }
  77. /**
  78. * {@inheritdoc}
  79. */
  80. public function dropAndCreateForeignKey(ForeignKeyConstraint $foreignKey, $table)
  81. {
  82. $tableDiff = $this->getTableDiffForAlterForeignKey($foreignKey, $table);
  83. $tableDiff->changedForeignKeys[] = $foreignKey;
  84. $this->alterTable($tableDiff);
  85. }
  86. /**
  87. * {@inheritdoc}
  88. */
  89. public function dropForeignKey($foreignKey, $table)
  90. {
  91. $tableDiff = $this->getTableDiffForAlterForeignKey($foreignKey, $table);
  92. $tableDiff->removedForeignKeys[] = $foreignKey;
  93. $this->alterTable($tableDiff);
  94. }
  95. /**
  96. * {@inheritdoc}
  97. */
  98. public function listTableForeignKeys($table, $database = null)
  99. {
  100. if ($database === null) {
  101. $database = $this->_conn->getDatabase();
  102. }
  103. $sql = $this->_platform->getListTableForeignKeysSQL($table, $database);
  104. $tableForeignKeys = $this->_conn->fetchAll($sql);
  105. if (! empty($tableForeignKeys)) {
  106. $createSql = $this->getCreateTableSQL($table);
  107. if ($createSql !== null && preg_match_all(
  108. '#
  109. (?:CONSTRAINT\s+([^\s]+)\s+)?
  110. (?:FOREIGN\s+KEY[^\)]+\)\s*)?
  111. REFERENCES\s+[^\s]+\s+(?:\([^\)]+\))?
  112. (?:
  113. [^,]*?
  114. (NOT\s+DEFERRABLE|DEFERRABLE)
  115. (?:\s+INITIALLY\s+(DEFERRED|IMMEDIATE))?
  116. )?#isx',
  117. $createSql,
  118. $match
  119. )) {
  120. $names = array_reverse($match[1]);
  121. $deferrable = array_reverse($match[2]);
  122. $deferred = array_reverse($match[3]);
  123. } else {
  124. $names = $deferrable = $deferred = [];
  125. }
  126. foreach ($tableForeignKeys as $key => $value) {
  127. $id = $value['id'];
  128. $tableForeignKeys[$key]['constraint_name'] = isset($names[$id]) && $names[$id] !== '' ? $names[$id] : $id;
  129. $tableForeignKeys[$key]['deferrable'] = isset($deferrable[$id]) && strtolower($deferrable[$id]) === 'deferrable';
  130. $tableForeignKeys[$key]['deferred'] = isset($deferred[$id]) && strtolower($deferred[$id]) === 'deferred';
  131. }
  132. }
  133. return $this->_getPortableTableForeignKeysList($tableForeignKeys);
  134. }
  135. /**
  136. * {@inheritdoc}
  137. */
  138. protected function _getPortableTableDefinition($table)
  139. {
  140. return $table['name'];
  141. }
  142. /**
  143. * {@inheritdoc}
  144. *
  145. * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
  146. */
  147. protected function _getPortableTableIndexesList($tableIndexes, $tableName = null)
  148. {
  149. $indexBuffer = [];
  150. // fetch primary
  151. $stmt = $this->_conn->executeQuery(sprintf(
  152. 'PRAGMA TABLE_INFO (%s)',
  153. $this->_conn->quote($tableName)
  154. ));
  155. $indexArray = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
  156. usort($indexArray, static function ($a, $b) {
  157. if ($a['pk'] === $b['pk']) {
  158. return $a['cid'] - $b['cid'];
  159. }
  160. return $a['pk'] - $b['pk'];
  161. });
  162. foreach ($indexArray as $indexColumnRow) {
  163. if ($indexColumnRow['pk'] === '0') {
  164. continue;
  165. }
  166. $indexBuffer[] = [
  167. 'key_name' => 'primary',
  168. 'primary' => true,
  169. 'non_unique' => false,
  170. 'column_name' => $indexColumnRow['name'],
  171. ];
  172. }
  173. // fetch regular indexes
  174. foreach ($tableIndexes as $tableIndex) {
  175. // Ignore indexes with reserved names, e.g. autoindexes
  176. if (strpos($tableIndex['name'], 'sqlite_') === 0) {
  177. continue;
  178. }
  179. $keyName = $tableIndex['name'];
  180. $idx = [];
  181. $idx['key_name'] = $keyName;
  182. $idx['primary'] = false;
  183. $idx['non_unique'] = $tableIndex['unique']?false:true;
  184. $stmt = $this->_conn->executeQuery(sprintf(
  185. 'PRAGMA INDEX_INFO (%s)',
  186. $this->_conn->quote($keyName)
  187. ));
  188. $indexArray = $stmt->fetchAll(FetchMode::ASSOCIATIVE);
  189. foreach ($indexArray as $indexColumnRow) {
  190. $idx['column_name'] = $indexColumnRow['name'];
  191. $indexBuffer[] = $idx;
  192. }
  193. }
  194. return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
  195. }
  196. /**
  197. * {@inheritdoc}
  198. */
  199. protected function _getPortableTableIndexDefinition($tableIndex)
  200. {
  201. return [
  202. 'name' => $tableIndex['name'],
  203. 'unique' => (bool) $tableIndex['unique'],
  204. ];
  205. }
  206. /**
  207. * {@inheritdoc}
  208. */
  209. protected function _getPortableTableColumnList($table, $database, $tableColumns)
  210. {
  211. $list = parent::_getPortableTableColumnList($table, $database, $tableColumns);
  212. // find column with autoincrement
  213. $autoincrementColumn = null;
  214. $autoincrementCount = 0;
  215. foreach ($tableColumns as $tableColumn) {
  216. if ($tableColumn['pk'] === '0') {
  217. continue;
  218. }
  219. $autoincrementCount++;
  220. if ($autoincrementColumn !== null || strtolower($tableColumn['type']) !== 'integer') {
  221. continue;
  222. }
  223. $autoincrementColumn = $tableColumn['name'];
  224. }
  225. if ($autoincrementCount === 1 && $autoincrementColumn !== null) {
  226. foreach ($list as $column) {
  227. if ($autoincrementColumn !== $column->getName()) {
  228. continue;
  229. }
  230. $column->setAutoincrement(true);
  231. }
  232. }
  233. // inspect column collation and comments
  234. $createSql = $this->getCreateTableSQL($table) ?? '';
  235. foreach ($list as $columnName => $column) {
  236. $type = $column->getType();
  237. if ($type instanceof StringType || $type instanceof TextType) {
  238. $column->setPlatformOption('collation', $this->parseColumnCollationFromSQL($columnName, $createSql) ?: 'BINARY');
  239. }
  240. $comment = $this->parseColumnCommentFromSQL($columnName, $createSql);
  241. if ($comment === null) {
  242. continue;
  243. }
  244. $type = $this->extractDoctrineTypeFromComment($comment, null);
  245. if ($type !== null) {
  246. $column->setType(Type::getType($type));
  247. $comment = $this->removeDoctrineTypeFromComment($comment, $type);
  248. }
  249. $column->setComment($comment);
  250. }
  251. return $list;
  252. }
  253. /**
  254. * {@inheritdoc}
  255. */
  256. protected function _getPortableTableColumnDefinition($tableColumn)
  257. {
  258. $parts = explode('(', $tableColumn['type']);
  259. $tableColumn['type'] = trim($parts[0]);
  260. if (isset($parts[1])) {
  261. $length = trim($parts[1], ')');
  262. $tableColumn['length'] = $length;
  263. }
  264. $dbType = strtolower($tableColumn['type']);
  265. $length = $tableColumn['length'] ?? null;
  266. $unsigned = false;
  267. if (strpos($dbType, ' unsigned') !== false) {
  268. $dbType = str_replace(' unsigned', '', $dbType);
  269. $unsigned = true;
  270. }
  271. $fixed = false;
  272. $type = $this->_platform->getDoctrineTypeMapping($dbType);
  273. $default = $tableColumn['dflt_value'];
  274. if ($default === 'NULL') {
  275. $default = null;
  276. }
  277. if ($default !== null) {
  278. // SQLite returns strings wrapped in single quotes, so we need to strip them
  279. $default = preg_replace("/^'(.*)'$/", '\1', $default);
  280. }
  281. $notnull = (bool) $tableColumn['notnull'];
  282. if (! isset($tableColumn['name'])) {
  283. $tableColumn['name'] = '';
  284. }
  285. $precision = null;
  286. $scale = null;
  287. switch ($dbType) {
  288. case 'char':
  289. $fixed = true;
  290. break;
  291. case 'float':
  292. case 'double':
  293. case 'real':
  294. case 'decimal':
  295. case 'numeric':
  296. if (isset($tableColumn['length'])) {
  297. if (strpos($tableColumn['length'], ',') === false) {
  298. $tableColumn['length'] .= ',0';
  299. }
  300. [$precision, $scale] = array_map('trim', explode(',', $tableColumn['length']));
  301. }
  302. $length = null;
  303. break;
  304. }
  305. $options = [
  306. 'length' => $length,
  307. 'unsigned' => (bool) $unsigned,
  308. 'fixed' => $fixed,
  309. 'notnull' => $notnull,
  310. 'default' => $default,
  311. 'precision' => $precision,
  312. 'scale' => $scale,
  313. 'autoincrement' => false,
  314. ];
  315. return new Column($tableColumn['name'], Type::getType($type), $options);
  316. }
  317. /**
  318. * {@inheritdoc}
  319. */
  320. protected function _getPortableViewDefinition($view)
  321. {
  322. return new View($view['name'], $view['sql']);
  323. }
  324. /**
  325. * {@inheritdoc}
  326. */
  327. protected function _getPortableTableForeignKeysList($tableForeignKeys)
  328. {
  329. $list = [];
  330. foreach ($tableForeignKeys as $value) {
  331. $value = array_change_key_case($value, CASE_LOWER);
  332. $name = $value['constraint_name'];
  333. if (! isset($list[$name])) {
  334. if (! isset($value['on_delete']) || $value['on_delete'] === 'RESTRICT') {
  335. $value['on_delete'] = null;
  336. }
  337. if (! isset($value['on_update']) || $value['on_update'] === 'RESTRICT') {
  338. $value['on_update'] = null;
  339. }
  340. $list[$name] = [
  341. 'name' => $name,
  342. 'local' => [],
  343. 'foreign' => [],
  344. 'foreignTable' => $value['table'],
  345. 'onDelete' => $value['on_delete'],
  346. 'onUpdate' => $value['on_update'],
  347. 'deferrable' => $value['deferrable'],
  348. 'deferred'=> $value['deferred'],
  349. ];
  350. }
  351. $list[$name]['local'][] = $value['from'];
  352. $list[$name]['foreign'][] = $value['to'];
  353. }
  354. $result = [];
  355. foreach ($list as $constraint) {
  356. $result[] = new ForeignKeyConstraint(
  357. array_values($constraint['local']),
  358. $constraint['foreignTable'],
  359. array_values($constraint['foreign']),
  360. $constraint['name'],
  361. [
  362. 'onDelete' => $constraint['onDelete'],
  363. 'onUpdate' => $constraint['onUpdate'],
  364. 'deferrable' => $constraint['deferrable'],
  365. 'deferred'=> $constraint['deferred'],
  366. ]
  367. );
  368. }
  369. return $result;
  370. }
  371. /**
  372. * @param Table|string $table
  373. *
  374. * @return TableDiff
  375. *
  376. * @throws DBALException
  377. */
  378. private function getTableDiffForAlterForeignKey(ForeignKeyConstraint $foreignKey, $table)
  379. {
  380. if (! $table instanceof Table) {
  381. $tableDetails = $this->tryMethod('listTableDetails', $table);
  382. if ($table === false) {
  383. throw new DBALException(sprintf('Sqlite schema manager requires to modify foreign keys table definition "%s".', $table));
  384. }
  385. $table = $tableDetails;
  386. }
  387. $tableDiff = new TableDiff($table->getName());
  388. $tableDiff->fromTable = $table;
  389. return $tableDiff;
  390. }
  391. private function parseColumnCollationFromSQL(string $column, string $sql) : ?string
  392. {
  393. $pattern = '{(?:\W' . preg_quote($column) . '\W|\W' . preg_quote($this->_platform->quoteSingleIdentifier($column))
  394. . '\W)[^,(]+(?:\([^()]+\)[^,]*)?(?:(?:DEFAULT|CHECK)\s*(?:\(.*?\))?[^,]*)*COLLATE\s+["\']?([^\s,"\')]+)}is';
  395. if (preg_match($pattern, $sql, $match) !== 1) {
  396. return null;
  397. }
  398. return $match[1];
  399. }
  400. private function parseColumnCommentFromSQL(string $column, string $sql) : ?string
  401. {
  402. $pattern = '{[\s(,](?:\W' . preg_quote($this->_platform->quoteSingleIdentifier($column)) . '\W|\W' . preg_quote($column)
  403. . '\W)(?:\(.*?\)|[^,(])*?,?((?:(?!\n))(?:\s*--[^\n]*\n?)+)}i';
  404. if (preg_match($pattern, $sql, $match) !== 1) {
  405. return null;
  406. }
  407. $comment = preg_replace('{^\s*--}m', '', rtrim($match[1], "\n"));
  408. return $comment === '' ? null : $comment;
  409. }
  410. private function getCreateTableSQL(string $table) : ?string
  411. {
  412. return $this->_conn->fetchColumn(
  413. <<<'SQL'
  414. SELECT sql
  415. FROM (
  416. SELECT *
  417. FROM sqlite_master
  418. UNION ALL
  419. SELECT *
  420. FROM sqlite_temp_master
  421. )
  422. WHERE type = 'table'
  423. AND name = ?
  424. SQL
  425. ,
  426. [$table]
  427. ) ?: null;
  428. }
  429. }