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