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