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