1<?php 2 3namespace Drupal\Core\Command; 4 5use Drupal\Component\Utility\Variable; 6use Drupal\Core\Database\Connection; 7use Symfony\Component\Console\Input\InputInterface; 8use Symfony\Component\Console\Input\InputOption; 9use Symfony\Component\Console\Output\OutputInterface; 10 11/** 12 * Provides a command to dump the current database to a script. 13 * 14 * This script exports all tables in the given database, and all data (except 15 * for tables denoted as schema-only). The resulting script creates the tables 16 * and populates them with the exported data. 17 * 18 * @todo This command is currently only compatible with MySQL. Making it 19 * backend-agnostic will require \Drupal\Core\Database\Schema support the 20 * ability to retrieve table schema information. Note that using a raw 21 * SQL dump file here (eg, generated from mysqldump or pg_dump) is not an 22 * option since these tend to still be database-backend specific. 23 * @see https://www.drupal.org/node/301038 24 * 25 * @see \Drupal\Core\Command\DbDumpApplication 26 */ 27class DbDumpCommand extends DbCommandBase { 28 29 /** 30 * An array of table patterns to exclude completely. 31 * 32 * This excludes any lingering simpletest tables generated during test runs. 33 * 34 * @var array 35 */ 36 protected $excludeTables = ['test[0-9]+']; 37 38 /** 39 * {@inheritdoc} 40 */ 41 protected function configure() { 42 $this->setName('dump-database-d8-mysql') 43 ->setDescription('Dump the current database to a generation script') 44 ->addOption('schema-only', NULL, InputOption::VALUE_OPTIONAL, 'A comma separated list of tables to only export the schema without data.', 'cache.*,sessions,watchdog'); 45 parent::configure(); 46 } 47 48 /** 49 * {@inheritdoc} 50 */ 51 protected function execute(InputInterface $input, OutputInterface $output) { 52 $connection = $this->getDatabaseConnection($input); 53 54 // If not explicitly set, disable ANSI which will break generated php. 55 if ($input->hasParameterOption(['--ansi']) !== TRUE) { 56 $output->setDecorated(FALSE); 57 } 58 59 $schema_tables = $input->getOption('schema-only'); 60 $schema_tables = explode(',', $schema_tables); 61 62 $output->writeln($this->generateScript($connection, $schema_tables), OutputInterface::OUTPUT_RAW); 63 return 0; 64 } 65 66 /** 67 * Generates the database script. 68 * 69 * @param \Drupal\Core\Database\Connection $connection 70 * The database connection to use. 71 * @param array $schema_only 72 * Table patterns for which to only dump the schema, no data. 73 * 74 * @return string 75 * The PHP script. 76 */ 77 protected function generateScript(Connection $connection, array $schema_only = []) { 78 $tables = ''; 79 80 $schema_only_patterns = []; 81 foreach ($schema_only as $match) { 82 $schema_only_patterns[] = '/^' . $match . '$/'; 83 } 84 85 foreach ($this->getTables($connection) as $table) { 86 $schema = $this->getTableSchema($connection, $table); 87 // Check for schema only. 88 if (empty($schema_only_patterns) || preg_replace($schema_only_patterns, '', $table)) { 89 $data = $this->getTableData($connection, $table); 90 } 91 else { 92 $data = []; 93 } 94 $tables .= $this->getTableScript($table, $schema, $data); 95 } 96 $script = $this->getTemplate(); 97 // Substitute in the version. 98 $script = str_replace('{{VERSION}}', \Drupal::VERSION, $script); 99 // Substitute in the tables. 100 $script = str_replace('{{TABLES}}', trim($tables), $script); 101 return trim($script); 102 } 103 104 /** 105 * Returns a list of tables, not including those set to be excluded. 106 * 107 * @param \Drupal\Core\Database\Connection $connection 108 * The database connection to use. 109 * 110 * @return array 111 * An array of table names. 112 */ 113 protected function getTables(Connection $connection) { 114 $tables = array_values($connection->schema()->findTables('%')); 115 116 foreach ($tables as $key => $table) { 117 // Remove any explicitly excluded tables. 118 foreach ($this->excludeTables as $pattern) { 119 if (preg_match('/^' . $pattern . '$/', $table)) { 120 unset($tables[$key]); 121 } 122 } 123 } 124 125 return $tables; 126 } 127 128 /** 129 * Returns a schema array for a given table. 130 * 131 * @param \Drupal\Core\Database\Connection $connection 132 * The database connection to use. 133 * @param string $table 134 * The table name. 135 * 136 * @return array 137 * A schema array (as defined by hook_schema()). 138 * 139 * @todo This implementation is hard-coded for MySQL. 140 */ 141 protected function getTableSchema(Connection $connection, $table) { 142 // Check this is MySQL. 143 if ($connection->databaseType() !== 'mysql') { 144 throw new \RuntimeException('This script can only be used with MySQL database backends.'); 145 } 146 147 $query = $connection->query("SHOW FULL COLUMNS FROM {" . $table . "}"); 148 $definition = []; 149 while (($row = $query->fetchAssoc()) !== FALSE) { 150 $name = $row['Field']; 151 // Parse out the field type and meta information. 152 preg_match('@([a-z]+)(?:\((\d+)(?:,(\d+))?\))?\s*(unsigned)?@', $row['Type'], $matches); 153 $type = $this->fieldTypeMap($connection, $matches[1]); 154 if ($row['Extra'] === 'auto_increment') { 155 // If this is an auto increment, then the type is 'serial'. 156 $type = 'serial'; 157 } 158 $definition['fields'][$name] = [ 159 'type' => $type, 160 'not null' => $row['Null'] === 'NO', 161 ]; 162 if ($size = $this->fieldSizeMap($connection, $matches[1])) { 163 $definition['fields'][$name]['size'] = $size; 164 } 165 if (isset($matches[2]) && $type === 'numeric') { 166 // Add precision and scale. 167 $definition['fields'][$name]['precision'] = $matches[2]; 168 $definition['fields'][$name]['scale'] = $matches[3]; 169 } 170 elseif ($type === 'time') { 171 // @todo Core doesn't support these, but copied from `migrate-db.sh` for now. 172 // Convert to varchar. 173 $definition['fields'][$name]['type'] = 'varchar'; 174 $definition['fields'][$name]['length'] = '100'; 175 } 176 elseif ($type === 'datetime') { 177 // Adjust for other database types. 178 $definition['fields'][$name]['mysql_type'] = 'datetime'; 179 $definition['fields'][$name]['pgsql_type'] = 'timestamp without time zone'; 180 $definition['fields'][$name]['sqlite_type'] = 'varchar'; 181 $definition['fields'][$name]['sqlsrv_type'] = 'smalldatetime'; 182 } 183 elseif (!isset($definition['fields'][$name]['size'])) { 184 // Try use the provided length, if it doesn't exist default to 100. It's 185 // not great but good enough for our dumps at this point. 186 $definition['fields'][$name]['length'] = isset($matches[2]) ? $matches[2] : 100; 187 } 188 189 if (isset($row['Default'])) { 190 $definition['fields'][$name]['default'] = $row['Default']; 191 } 192 193 if (isset($matches[4])) { 194 $definition['fields'][$name]['unsigned'] = TRUE; 195 } 196 197 // Check for the 'varchar_ascii' type that should be 'binary'. 198 if (isset($row['Collation']) && $row['Collation'] == 'ascii_bin') { 199 $definition['fields'][$name]['type'] = 'varchar_ascii'; 200 $definition['fields'][$name]['binary'] = TRUE; 201 } 202 203 // Check for the non-binary 'varchar_ascii'. 204 if (isset($row['Collation']) && $row['Collation'] == 'ascii_general_ci') { 205 $definition['fields'][$name]['type'] = 'varchar_ascii'; 206 } 207 208 // Check for the 'utf8_bin' collation. 209 if (isset($row['Collation']) && $row['Collation'] == 'utf8_bin') { 210 $definition['fields'][$name]['binary'] = TRUE; 211 } 212 } 213 214 // Set primary key, unique keys, and indexes. 215 $this->getTableIndexes($connection, $table, $definition); 216 217 // Set table collation. 218 $this->getTableCollation($connection, $table, $definition); 219 220 return $definition; 221 } 222 223 /** 224 * Adds primary key, unique keys, and index information to the schema. 225 * 226 * @param \Drupal\Core\Database\Connection $connection 227 * The database connection to use. 228 * @param string $table 229 * The table to find indexes for. 230 * @param array &$definition 231 * The schema definition to modify. 232 */ 233 protected function getTableIndexes(Connection $connection, $table, &$definition) { 234 // Note, this query doesn't support ordering, so that is worked around 235 // below by keying the array on Seq_in_index. 236 $query = $connection->query("SHOW INDEX FROM {" . $table . "}"); 237 while (($row = $query->fetchAssoc()) !== FALSE) { 238 $index_name = $row['Key_name']; 239 $column = $row['Column_name']; 240 // Key the arrays by the index sequence for proper ordering (start at 0). 241 $order = $row['Seq_in_index'] - 1; 242 243 // If specified, add length to the index. 244 if ($row['Sub_part']) { 245 $column = [$column, $row['Sub_part']]; 246 } 247 248 if ($index_name === 'PRIMARY') { 249 $definition['primary key'][$order] = $column; 250 } 251 elseif ($row['Non_unique'] == 0) { 252 $definition['unique keys'][$index_name][$order] = $column; 253 } 254 else { 255 $definition['indexes'][$index_name][$order] = $column; 256 } 257 } 258 } 259 260 /** 261 * Set the table collation. 262 * 263 * @param \Drupal\Core\Database\Connection $connection 264 * The database connection to use. 265 * @param string $table 266 * The table to find indexes for. 267 * @param array &$definition 268 * The schema definition to modify. 269 */ 270 protected function getTableCollation(Connection $connection, $table, &$definition) { 271 $query = $connection->query("SHOW TABLE STATUS LIKE '{" . $table . "}'"); 272 $data = $query->fetchAssoc(); 273 274 // Map the collation to a character set. For example, 'utf8mb4_general_ci' 275 // (MySQL 5) or 'utf8mb4_0900_ai_ci' (MySQL 8) will be mapped to 'utf8mb4'. 276 list($charset,) = explode('_', $data['Collation'], 2); 277 278 // Set `mysql_character_set`. This will be ignored by other backends. 279 $definition['mysql_character_set'] = $charset; 280 } 281 282 /** 283 * Gets all data from a given table. 284 * 285 * If a table is set to be schema only, and empty array is returned. 286 * 287 * @param \Drupal\Core\Database\Connection $connection 288 * The database connection to use. 289 * @param string $table 290 * The table to query. 291 * 292 * @return array 293 * The data from the table as an array. 294 */ 295 protected function getTableData(Connection $connection, $table) { 296 $order = $this->getFieldOrder($connection, $table); 297 $query = $connection->query("SELECT * FROM {" . $table . "} " . $order); 298 $results = []; 299 while (($row = $query->fetchAssoc()) !== FALSE) { 300 $results[] = $row; 301 } 302 return $results; 303 } 304 305 /** 306 * Given a database field type, return a Drupal type. 307 * 308 * @param \Drupal\Core\Database\Connection $connection 309 * The database connection to use. 310 * @param string $type 311 * The MySQL field type. 312 * 313 * @return string 314 * The Drupal schema field type. If there is no mapping, the original field 315 * type is returned. 316 */ 317 protected function fieldTypeMap(Connection $connection, $type) { 318 // Convert everything to lowercase. 319 $map = array_map('strtolower', $connection->schema()->getFieldTypeMap()); 320 $map = array_flip($map); 321 322 // The MySql map contains type:size. Remove the size part. 323 return isset($map[$type]) ? explode(':', $map[$type])[0] : $type; 324 } 325 326 /** 327 * Given a database field type, return a Drupal size. 328 * 329 * @param \Drupal\Core\Database\Connection $connection 330 * The database connection to use. 331 * @param string $type 332 * The MySQL field type. 333 * 334 * @return string 335 * The Drupal schema field size. 336 */ 337 protected function fieldSizeMap(Connection $connection, $type) { 338 // Convert everything to lowercase. 339 $map = array_map('strtolower', $connection->schema()->getFieldTypeMap()); 340 $map = array_flip($map); 341 342 $schema_type = explode(':', $map[$type])[0]; 343 // Only specify size on these types. 344 if (in_array($schema_type, ['blob', 'float', 'int', 'text'])) { 345 // The MySql map contains type:size. Remove the type part. 346 return explode(':', $map[$type])[1]; 347 } 348 } 349 350 /** 351 * Gets field ordering for a given table. 352 * 353 * @param \Drupal\Core\Database\Connection $connection 354 * The database connection to use. 355 * @param string $table 356 * The table name. 357 * 358 * @return string 359 * The order string to append to the query. 360 */ 361 protected function getFieldOrder(Connection $connection, $table) { 362 // @todo this is MySQL only since there are no Database API functions for 363 // table column data. 364 // @todo this code is duplicated in `core/scripts/migrate-db.sh`. 365 $connection_info = $connection->getConnectionOptions(); 366 // Order by primary keys. 367 $order = ''; 368 $query = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` 369 WHERE (`TABLE_SCHEMA` = '" . $connection_info['database'] . "') 370 AND (`TABLE_NAME` = '{" . $table . "}') AND (`COLUMN_KEY` = 'PRI') 371 ORDER BY COLUMN_NAME"; 372 $results = $connection->query($query); 373 while (($row = $results->fetchAssoc()) !== FALSE) { 374 $order .= $row['COLUMN_NAME'] . ', '; 375 } 376 if (!empty($order)) { 377 $order = ' ORDER BY ' . rtrim($order, ', '); 378 } 379 return $order; 380 } 381 382 /** 383 * The script template. 384 * 385 * @return string 386 * The template for the generated PHP script. 387 */ 388 protected function getTemplate() { 389 // The template contains an instruction for the file to be ignored by PHPCS. 390 // This is because the files can be huge and coding standards are 391 // irrelevant. 392 $script = <<<'ENDOFSCRIPT' 393<?php 394// @codingStandardsIgnoreFile 395/** 396 * @file 397 * A database agnostic dump for testing purposes. 398 * 399 * This file was generated by the Drupal {{VERSION}} db-tools.php script. 400 */ 401 402use Drupal\Core\Database\Database; 403 404$connection = Database::getConnection(); 405 406{{TABLES}} 407 408ENDOFSCRIPT; 409 return $script; 410 } 411 412 /** 413 * The part of the script for each table. 414 * 415 * @param string $table 416 * Table name. 417 * @param array $schema 418 * Drupal schema definition. 419 * @param array $data 420 * Data for the table. 421 * 422 * @return string 423 * The table create statement, and if there is data, the insert command. 424 */ 425 protected function getTableScript($table, array $schema, array $data) { 426 $output = ''; 427 $output .= "\$connection->schema()->createTable('" . $table . "', " . Variable::export($schema) . ");\n\n"; 428 if (!empty($data)) { 429 $insert = ''; 430 foreach ($data as $record) { 431 $insert .= "->values(" . Variable::export($record) . ")\n"; 432 } 433 $fields = Variable::export(array_keys($schema['fields'])); 434 $output .= <<<EOT 435\$connection->insert('$table') 436->fields($fields) 437{$insert}->execute(); 438 439EOT; 440 } 441 return $output; 442 } 443 444} 445