SqliteSchemaManager.php 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390
  1. <?php
  2. /*
  3. * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
  4. * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
  5. * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
  6. * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
  7. * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
  8. * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
  9. * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  10. * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
  11. * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
  12. * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
  13. * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
  14. *
  15. * This software consists of voluntary contributions made by many individuals
  16. * and is licensed under the MIT license. For more information, see
  17. * <http://www.doctrine-project.org>.
  18. */
  19. namespace Doctrine\DBAL\Schema;
  20. use Doctrine\DBAL\DBALException;
  21. /**
  22. * Sqlite SchemaManager.
  23. *
  24. * @author Konsta Vesterinen <kvesteri@cc.hut.fi>
  25. * @author Lukas Smith <smith@pooteeweet.org> (PEAR MDB2 library)
  26. * @author Jonathan H. Wage <jonwage@gmail.com>
  27. * @author Martin Hasoň <martin.hason@gmail.com>
  28. * @since 2.0
  29. */
  30. class SqliteSchemaManager extends AbstractSchemaManager
  31. {
  32. /**
  33. * {@inheritdoc}
  34. */
  35. public function dropDatabase($database)
  36. {
  37. if (file_exists($database)) {
  38. unlink($database);
  39. }
  40. }
  41. /**
  42. * {@inheritdoc}
  43. */
  44. public function createDatabase($database)
  45. {
  46. $params = $this->_conn->getParams();
  47. $driver = $params['driver'];
  48. $options = array(
  49. 'driver' => $driver,
  50. 'path' => $database
  51. );
  52. $conn = \Doctrine\DBAL\DriverManager::getConnection($options);
  53. $conn->connect();
  54. $conn->close();
  55. }
  56. /**
  57. * {@inheritdoc}
  58. */
  59. public function renameTable($name, $newName)
  60. {
  61. $tableDiff = new TableDiff($name);
  62. $tableDiff->fromTable = $this->listTableDetails($name);
  63. $tableDiff->newName = $newName;
  64. $this->alterTable($tableDiff);
  65. }
  66. /**
  67. * {@inheritdoc}
  68. */
  69. public function createForeignKey(ForeignKeyConstraint $foreignKey, $table)
  70. {
  71. $tableDiff = $this->getTableDiffForAlterForeignKey($foreignKey, $table);
  72. $tableDiff->addedForeignKeys[] = $foreignKey;
  73. $this->alterTable($tableDiff);
  74. }
  75. /**
  76. * {@inheritdoc}
  77. */
  78. public function dropAndCreateForeignKey(ForeignKeyConstraint $foreignKey, $table)
  79. {
  80. $tableDiff = $this->getTableDiffForAlterForeignKey($foreignKey, $table);
  81. $tableDiff->changedForeignKeys[] = $foreignKey;
  82. $this->alterTable($tableDiff);
  83. }
  84. /**
  85. * {@inheritdoc}
  86. */
  87. public function dropForeignKey($foreignKey, $table)
  88. {
  89. $tableDiff = $this->getTableDiffForAlterForeignKey($foreignKey, $table);
  90. $tableDiff->removedForeignKeys[] = $foreignKey;
  91. $this->alterTable($tableDiff);
  92. }
  93. /**
  94. * {@inheritdoc}
  95. */
  96. public function listTableForeignKeys($table, $database = null)
  97. {
  98. if (null === $database) {
  99. $database = $this->_conn->getDatabase();
  100. }
  101. $sql = $this->_platform->getListTableForeignKeysSQL($table, $database);
  102. $tableForeignKeys = $this->_conn->fetchAll($sql);
  103. if ( ! empty($tableForeignKeys)) {
  104. $createSql = $this->_conn->fetchAll("SELECT sql FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type = 'table' AND name = '$table'");
  105. $createSql = isset($createSql[0]['sql']) ? $createSql[0]['sql'] : '';
  106. if (preg_match_all('#
  107. (?:CONSTRAINT\s+([^\s]+)\s+)?
  108. (?:FOREIGN\s+KEY[^\)]+\)\s*)?
  109. REFERENCES\s+[^\s]+\s+(?:\([^\)]+\))?
  110. (?:
  111. [^,]*?
  112. (NOT\s+DEFERRABLE|DEFERRABLE)
  113. (?:\s+INITIALLY\s+(DEFERRED|IMMEDIATE))?
  114. )?#isx',
  115. $createSql, $match)) {
  116. $names = array_reverse($match[1]);
  117. $deferrable = array_reverse($match[2]);
  118. $deferred = array_reverse($match[3]);
  119. } else {
  120. $names = $deferrable = $deferred = array();
  121. }
  122. foreach ($tableForeignKeys as $key => $value) {
  123. $id = $value['id'];
  124. $tableForeignKeys[$key]['constraint_name'] = isset($names[$id]) && '' != $names[$id] ? $names[$id] : $id;
  125. $tableForeignKeys[$key]['deferrable'] = isset($deferrable[$id]) && 'deferrable' == strtolower($deferrable[$id]) ? true : false;
  126. $tableForeignKeys[$key]['deferred'] = isset($deferred[$id]) && 'deferred' == strtolower($deferred[$id]) ? true : false;
  127. }
  128. }
  129. return $this->_getPortableTableForeignKeysList($tableForeignKeys);
  130. }
  131. /**
  132. * {@inheritdoc}
  133. */
  134. protected function _getPortableTableDefinition($table)
  135. {
  136. return $table['name'];
  137. }
  138. /**
  139. * {@inheritdoc}
  140. *
  141. * @license New BSD License
  142. * @link http://ezcomponents.org/docs/api/trunk/DatabaseSchema/ezcDbSchemaPgsqlReader.html
  143. */
  144. protected function _getPortableTableIndexesList($tableIndexes, $tableName=null)
  145. {
  146. $indexBuffer = array();
  147. // fetch primary
  148. $stmt = $this->_conn->executeQuery( "PRAGMA TABLE_INFO ('$tableName')" );
  149. $indexArray = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  150. foreach($indexArray as $indexColumnRow) {
  151. if($indexColumnRow['pk'] == "1") {
  152. $indexBuffer[] = array(
  153. 'key_name' => 'primary',
  154. 'primary' => true,
  155. 'non_unique' => false,
  156. 'column_name' => $indexColumnRow['name']
  157. );
  158. }
  159. }
  160. // fetch regular indexes
  161. foreach($tableIndexes as $tableIndex) {
  162. // Ignore indexes with reserved names, e.g. autoindexes
  163. if (strpos($tableIndex['name'], 'sqlite_') !== 0) {
  164. $keyName = $tableIndex['name'];
  165. $idx = array();
  166. $idx['key_name'] = $keyName;
  167. $idx['primary'] = false;
  168. $idx['non_unique'] = $tableIndex['unique']?false:true;
  169. $stmt = $this->_conn->executeQuery( "PRAGMA INDEX_INFO ( '{$keyName}' )" );
  170. $indexArray = $stmt->fetchAll(\PDO::FETCH_ASSOC);
  171. foreach ( $indexArray as $indexColumnRow ) {
  172. $idx['column_name'] = $indexColumnRow['name'];
  173. $indexBuffer[] = $idx;
  174. }
  175. }
  176. }
  177. return parent::_getPortableTableIndexesList($indexBuffer, $tableName);
  178. }
  179. /**
  180. * {@inheritdoc}
  181. */
  182. protected function _getPortableTableIndexDefinition($tableIndex)
  183. {
  184. return array(
  185. 'name' => $tableIndex['name'],
  186. 'unique' => (bool) $tableIndex['unique']
  187. );
  188. }
  189. /**
  190. * {@inheritdoc}
  191. */
  192. protected function _getPortableTableColumnList($table, $database, $tableColumns)
  193. {
  194. $list = parent::_getPortableTableColumnList($table, $database, $tableColumns);
  195. $autoincrementColumn = null;
  196. $autoincrementCount = 0;
  197. foreach ($tableColumns as $tableColumn) {
  198. if ('1' == $tableColumn['pk']) {
  199. $autoincrementCount++;
  200. if (null === $autoincrementColumn && 'integer' == strtolower($tableColumn['type'])) {
  201. $autoincrementColumn = $tableColumn['name'];
  202. }
  203. }
  204. }
  205. if (1 == $autoincrementCount && null !== $autoincrementColumn) {
  206. foreach ($list as $column) {
  207. if ($autoincrementColumn == $column->getName()) {
  208. $column->setAutoincrement(true);
  209. }
  210. }
  211. }
  212. return $list;
  213. }
  214. /**
  215. * {@inheritdoc}
  216. */
  217. protected function _getPortableTableColumnDefinition($tableColumn)
  218. {
  219. $e = explode('(', $tableColumn['type']);
  220. $tableColumn['type'] = $e[0];
  221. if (isset($e[1])) {
  222. $length = trim($e[1], ')');
  223. $tableColumn['length'] = $length;
  224. }
  225. $dbType = strtolower($tableColumn['type']);
  226. $length = isset($tableColumn['length']) ? $tableColumn['length'] : null;
  227. $unsigned = (boolean) isset($tableColumn['unsigned']) ? $tableColumn['unsigned'] : false;
  228. $fixed = false;
  229. $type = $this->_platform->getDoctrineTypeMapping($dbType);
  230. $default = $tableColumn['dflt_value'];
  231. if ($default == 'NULL') {
  232. $default = null;
  233. }
  234. if ($default !== null) {
  235. // SQLite returns strings wrapped in single quotes, so we need to strip them
  236. $default = preg_replace("/^'(.*)'$/", '\1', $default);
  237. }
  238. $notnull = (bool) $tableColumn['notnull'];
  239. if ( ! isset($tableColumn['name'])) {
  240. $tableColumn['name'] = '';
  241. }
  242. $precision = null;
  243. $scale = null;
  244. switch ($dbType) {
  245. case 'char':
  246. $fixed = true;
  247. break;
  248. case 'float':
  249. case 'double':
  250. case 'real':
  251. case 'decimal':
  252. case 'numeric':
  253. if (isset($tableColumn['length'])) {
  254. if (strpos($tableColumn['length'], ',') === false) {
  255. $tableColumn['length'] .= ",0";
  256. }
  257. list($precision, $scale) = array_map('trim', explode(',', $tableColumn['length']));
  258. }
  259. $length = null;
  260. break;
  261. }
  262. $options = array(
  263. 'length' => $length,
  264. 'unsigned' => (bool) $unsigned,
  265. 'fixed' => $fixed,
  266. 'notnull' => $notnull,
  267. 'default' => $default,
  268. 'precision' => $precision,
  269. 'scale' => $scale,
  270. 'autoincrement' => false,
  271. );
  272. return new Column($tableColumn['name'], \Doctrine\DBAL\Types\Type::getType($type), $options);
  273. }
  274. /**
  275. * {@inheritdoc}
  276. */
  277. protected function _getPortableViewDefinition($view)
  278. {
  279. return new View($view['name'], $view['sql']);
  280. }
  281. /**
  282. * {@inheritdoc}
  283. */
  284. protected function _getPortableTableForeignKeysList($tableForeignKeys)
  285. {
  286. $list = array();
  287. foreach ($tableForeignKeys as $value) {
  288. $value = array_change_key_case($value, CASE_LOWER);
  289. $name = $value['constraint_name'];
  290. if ( ! isset($list[$name])) {
  291. if ( ! isset($value['on_delete']) || $value['on_delete'] == "RESTRICT") {
  292. $value['on_delete'] = null;
  293. }
  294. if ( ! isset($value['on_update']) || $value['on_update'] == "RESTRICT") {
  295. $value['on_update'] = null;
  296. }
  297. $list[$name] = array(
  298. 'name' => $name,
  299. 'local' => array(),
  300. 'foreign' => array(),
  301. 'foreignTable' => $value['table'],
  302. 'onDelete' => $value['on_delete'],
  303. 'onUpdate' => $value['on_update'],
  304. 'deferrable' => $value['deferrable'],
  305. 'deferred'=> $value['deferred'],
  306. );
  307. }
  308. $list[$name]['local'][] = $value['from'];
  309. $list[$name]['foreign'][] = $value['to'];
  310. }
  311. $result = array();
  312. foreach($list as $constraint) {
  313. $result[] = new ForeignKeyConstraint(
  314. array_values($constraint['local']), $constraint['foreignTable'],
  315. array_values($constraint['foreign']), $constraint['name'],
  316. array(
  317. 'onDelete' => $constraint['onDelete'],
  318. 'onUpdate' => $constraint['onUpdate'],
  319. 'deferrable' => $constraint['deferrable'],
  320. 'deferred'=> $constraint['deferred'],
  321. )
  322. );
  323. }
  324. return $result;
  325. }
  326. /**
  327. * @param \Doctrine\DBAL\Schema\ForeignKeyConstraint $foreignKey
  328. * @param \Doctrine\DBAL\Schema\Table|string $table
  329. *
  330. * @return \Doctrine\DBAL\Schema\TableDiff
  331. *
  332. * @throws \Doctrine\DBAL\DBALException
  333. */
  334. private function getTableDiffForAlterForeignKey(ForeignKeyConstraint $foreignKey, $table)
  335. {
  336. if ( ! $table instanceof Table) {
  337. $tableDetails = $this->tryMethod('listTableDetails', $table);
  338. if (false === $table) {
  339. throw new DBALException(sprintf('Sqlite schema manager requires to modify foreign keys table definition "%s".', $table));
  340. }
  341. $table = $tableDetails;
  342. }
  343. $tableDiff = new TableDiff($table->getName());
  344. $tableDiff->fromTable = $table;
  345. return $tableDiff;
  346. }
  347. }