annotate core/lib/Drupal/Core/Command/DbDumpCommand.php @ 19:fa3358dc1485 tip

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