DbDumpCommand.php 13.8 KB
Newer Older
1 2 3 4 5 6 7
<?php

namespace Drupal\Core\Command;

use Drupal\Component\Utility\Variable;
use Drupal\Core\Database\Connection;
use Symfony\Component\Console\Input\InputInterface;
8
use Symfony\Component\Console\Input\InputOption;
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
use Symfony\Component\Console\Output\OutputInterface;

/**
 * Provides a command to dump the current database to a script.
 *
 * This script exports all tables in the given database, and all data (except
 * for tables denoted as schema-only). The resulting script creates the tables
 * and populates them with the exported data.
 *
 * @todo This command is currently only compatible with MySQL. Making it
 *   backend-agnostic will require \Drupal\Core\Database\Schema support the
 *   ability to retrieve table schema information. Note that using a raw
 *   SQL dump file here (eg, generated from mysqldump or pg_dump) is not an
 *   option since these tend to still be database-backend specific.
 * @see https://www.drupal.org/node/301038
 *
 * @see \Drupal\Core\Command\DbDumpApplication
 */
27
class DbDumpCommand extends DbCommandBase {
28 29 30 31 32 33 34 35

  /**
   * An array of table patterns to exclude completely.
   *
   * This excludes any lingering simpletest tables generated during test runs.
   *
   * @var array
   */
36
  protected $excludeTables = ['test[0-9]+'];
37 38 39 40 41 42

  /**
   * {@inheritdoc}
   */
  protected function configure() {
    $this->setName('dump-database-d8-mysql')
43 44 45
      ->setDescription('Dump the current database to a generation script')
      ->addOption('schema-only', NULL, InputOption::VALUE_OPTIONAL, 'A comma separated list of tables to only export the schema without data.', 'cache.*,sessions,watchdog');
    parent::configure();
46 47 48 49 50 51
  }

  /**
   * {@inheritdoc}
   */
  protected function execute(InputInterface $input, OutputInterface $output) {
52 53
    $connection = $this->getDatabaseConnection($input);

54 55 56 57 58
    // If not explicitly set, disable ANSI which will break generated php.
    if ($input->hasParameterOption(['--ansi']) !== TRUE) {
      $output->setDecorated(FALSE);
    }

59 60 61 62
    $schema_tables = $input->getOption('schema-only');
    $schema_tables = explode(',', $schema_tables);

    $output->writeln($this->generateScript($connection, $schema_tables), OutputInterface::OUTPUT_RAW);
63 64 65 66 67
  }

  /**
   * Generates the database script.
   *
68 69 70 71
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
   * @param array $schema_only
   *   Table patterns for which to only dump the schema, no data.
72
   * @return string
73 74
   *   The PHP script.
   */
75
  protected function generateScript(Connection $connection, array $schema_only = []) {
76
    $tables = '';
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

    $schema_only_patterns = [];
    foreach ($schema_only as $match) {
      $schema_only_patterns[] = '/^' . $match . '$/';
    }

    foreach ($this->getTables($connection) as $table) {
      $schema = $this->getTableSchema($connection, $table);
      // Check for schema only.
      if (empty($schema_only_patterns) || preg_replace($schema_only_patterns, '', $table)) {
        $data = $this->getTableData($connection, $table);
      }
      else {
        $data = [];
      }
92 93 94 95 96 97 98 99 100 101 102
      $tables .= $this->getTableScript($table, $schema, $data);
    }
    $script = $this->getTemplate();
    // Substitute in the tables.
    $script = str_replace('{{TABLES}}', trim($tables), $script);
    return trim($script);
  }

  /**
   * Returns a list of tables, not including those set to be excluded.
   *
103 104
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
105
   * @return array
106 107
   *   An array of table names.
   */
108 109
  protected function getTables(Connection $connection) {
    $tables = array_values($connection->schema()->findTables('%'));
110

111
    foreach ($tables as $key => $table) {
112 113 114 115 116 117 118
      // Remove any explicitly excluded tables.
      foreach ($this->excludeTables as $pattern) {
        if (preg_match('/^' . $pattern . '$/', $table)) {
          unset($tables[$key]);
        }
      }
    }
119

120 121 122 123 124 125
    return $tables;
  }

  /**
   * Returns a schema array for a given table.
   *
126 127
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
128 129 130 131 132 133 134 135
   * @param string $table
   *   The table name.
   *
   * @return array
   *   A schema array (as defined by hook_schema()).
   *
   * @todo This implementation is hard-coded for MySQL.
   */
136 137 138 139 140 141 142
  protected function getTableSchema(Connection $connection, $table) {
    // Check this is MySQL.
    if ($connection->databaseType() !== 'mysql') {
      throw new \RuntimeException('This script can only be used with MySQL database backends.');
    }

    $query = $connection->query("SHOW FULL COLUMNS FROM {" . $table . "}");
143 144 145 146 147
    $definition = [];
    while (($row = $query->fetchAssoc()) !== FALSE) {
      $name = $row['Field'];
      // Parse out the field type and meta information.
      preg_match('@([a-z]+)(?:\((\d+)(?:,(\d+))?\))?\s*(unsigned)?@', $row['Type'], $matches);
148
      $type  = $this->fieldTypeMap($connection, $matches[1]);
149 150 151 152 153 154 155 156
      if ($row['Extra'] === 'auto_increment') {
        // If this is an auto increment, then the type is 'serial'.
        $type = 'serial';
      }
      $definition['fields'][$name] = [
        'type' => $type,
        'not null' => $row['Null'] === 'NO',
      ];
157
      if ($size = $this->fieldSizeMap($connection, $matches[1])) {
158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202
        $definition['fields'][$name]['size'] = $size;
      }
      if (isset($matches[2]) && $type === 'numeric') {
        // Add precision and scale.
        $definition['fields'][$name]['precision'] = $matches[2];
        $definition['fields'][$name]['scale'] = $matches[3];
      }
      elseif ($type === 'time' || $type === 'datetime') {
        // @todo Core doesn't support these, but copied from `migrate-db.sh` for now.
        // Convert to varchar.
        $definition['fields'][$name]['type'] = 'varchar';
        $definition['fields'][$name]['length'] = '100';
      }
      elseif (!isset($definition['fields'][$name]['size'])) {
        // Try use the provided length, if it doesn't exist default to 100. It's
        // not great but good enough for our dumps at this point.
        $definition['fields'][$name]['length'] = isset($matches[2]) ? $matches[2] : 100;
      }

      if (isset($row['Default'])) {
        $definition['fields'][$name]['default'] = $row['Default'];
      }

      if (isset($matches[4])) {
        $definition['fields'][$name]['unsigned'] = TRUE;
      }

      // Check for the 'varchar_ascii' type that should be 'binary'.
      if (isset($row['Collation']) && $row['Collation'] == 'ascii_bin') {
        $definition['fields'][$name]['type'] = 'varchar_ascii';
        $definition['fields'][$name]['binary'] = TRUE;
      }

      // Check for the non-binary 'varchar_ascii'.
      if (isset($row['Collation']) && $row['Collation'] == 'ascii_general_ci') {
        $definition['fields'][$name]['type'] = 'varchar_ascii';
      }

      // Check for the 'utf8_bin' collation.
      if (isset($row['Collation']) && $row['Collation'] == 'utf8_bin') {
        $definition['fields'][$name]['binary'] = TRUE;
      }
    }

    // Set primary key, unique keys, and indexes.
203
    $this->getTableIndexes($connection, $table, $definition);
204

205
    // Set table collation.
206
    $this->getTableCollation($connection, $table, $definition);
207

208 209 210 211 212 213
    return $definition;
  }

  /**
   * Adds primary key, unique keys, and index information to the schema.
   *
214 215
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
216 217 218 219 220
   * @param string $table
   *   The table to find indexes for.
   * @param array &$definition
   *   The schema definition to modify.
   */
221
  protected function getTableIndexes(Connection $connection, $table, &$definition) {
222 223
    // Note, this query doesn't support ordering, so that is worked around
    // below by keying the array on Seq_in_index.
224
    $query = $connection->query("SHOW INDEX FROM {" . $table . "}");
225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247
    while (($row = $query->fetchAssoc()) !== FALSE) {
      $index_name = $row['Key_name'];
      $column = $row['Column_name'];
      // Key the arrays by the index sequence for proper ordering (start at 0).
      $order = $row['Seq_in_index'] - 1;

      // If specified, add length to the index.
      if ($row['Sub_part']) {
        $column = [$column, $row['Sub_part']];
      }

      if ($index_name === 'PRIMARY') {
        $definition['primary key'][$order] = $column;
      }
      elseif ($row['Non_unique'] == 0) {
        $definition['unique keys'][$index_name][$order] = $column;
      }
      else {
        $definition['indexes'][$index_name][$order] = $column;
      }
    }
  }

248 249 250
  /**
   * Set the table collation.
   *
251 252
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
253 254 255 256 257
   * @param string $table
   *   The table to find indexes for.
   * @param array &$definition
   *   The schema definition to modify.
   */
258 259
  protected function getTableCollation(Connection $connection, $table, &$definition) {
    $query = $connection->query("SHOW TABLE STATUS LIKE '{" . $table . "}'");
260 261 262 263 264 265
    $data = $query->fetchAssoc();

    // Set `mysql_character_set`. This will be ignored by other backends.
    $definition['mysql_character_set'] = str_replace('_general_ci', '', $data['Collation']);
  }

266 267 268 269 270
  /**
   * Gets all data from a given table.
   *
   * If a table is set to be schema only, and empty array is returned.
   *
271 272
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
273 274 275 276 277 278
   * @param string $table
   *   The table to query.
   *
   * @return array
   *   The data from the table as an array.
   */
279 280 281
  protected function getTableData(Connection $connection, $table) {
    $order = $this->getFieldOrder($connection, $table);
    $query = $connection->query("SELECT * FROM {" . $table . "} " . $order);
282 283 284 285 286 287 288 289 290 291
    $results = [];
    while (($row = $query->fetchAssoc()) !== FALSE) {
      $results[] = $row;
    }
    return $results;
  }

  /**
   * Given a database field type, return a Drupal type.
   *
292 293
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
294 295 296 297 298 299 300
   * @param string $type
   *   The MySQL field type.
   *
   * @return string
   *   The Drupal schema field type. If there is no mapping, the original field
   *   type is returned.
   */
301
  protected function fieldTypeMap(Connection $connection, $type) {
302
    // Convert everything to lowercase.
303
    $map = array_map('strtolower', $connection->schema()->getFieldTypeMap());
304 305 306 307 308 309 310 311 312
    $map = array_flip($map);

    // The MySql map contains type:size. Remove the size part.
    return isset($map[$type]) ? explode(':', $map[$type])[0] : $type;
  }

  /**
   * Given a database field type, return a Drupal size.
   *
313 314
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
315 316 317 318 319 320
   * @param string $type
   *   The MySQL field type.
   *
   * @return string
   *   The Drupal schema field size.
   */
321
  protected function fieldSizeMap(Connection $connection, $type) {
322
    // Convert everything to lowercase.
323
    $map = array_map('strtolower', $connection->schema()->getFieldTypeMap());
324 325 326 327 328 329 330 331 332 333 334 335 336
    $map = array_flip($map);

    $schema_type = explode(':', $map[$type])[0];
    // Only specify size on these types.
    if (in_array($schema_type, ['blob', 'float', 'int', 'text'])) {
      // The MySql map contains type:size. Remove the type part.
      return explode(':', $map[$type])[1];
    }
  }

  /**
   * Gets field ordering for a given table.
   *
337 338
   * @param \Drupal\Core\Database\Connection $connection
   *   The database connection to use.
339 340 341 342 343 344
   * @param string $table
   *   The table name.
   *
   * @return string
   *   The order string to append to the query.
   */
345
  protected function getFieldOrder(Connection $connection, $table) {
346 347 348
    // @todo this is MySQL only since there are no Database API functions for
    // table column data.
    // @todo this code is duplicated in `core/scripts/migrate-db.sh`.
349
    $connection_info = $connection->getConnectionOptions();
350 351 352 353 354 355
    // Order by primary keys.
    $order = '';
    $query = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS`
    WHERE (`TABLE_SCHEMA` = '" . $connection_info['database'] . "')
    AND (`TABLE_NAME` = '{" . $table . "}') AND (`COLUMN_KEY` = 'PRI')
    ORDER BY COLUMN_NAME";
356
    $results = $connection->query($query);
357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372
    while (($row = $results->fetchAssoc()) !== FALSE) {
      $order .= $row['COLUMN_NAME'] . ', ';
    }
    if (!empty($order)) {
      $order = ' ORDER BY ' . rtrim($order, ', ');
    }
    return $order;
  }

  /**
   * The script template.
   *
   * @return string
   *   The template for the generated PHP script.
   */
  protected function getTemplate() {
373 374 375
    // The template contains an instruction for the file to be ignored by PHPCS.
    // This is because the files can be huge and coding standards are
    // irrelevant.
376 377
    $script = <<<'ENDOFSCRIPT'
<?php
378
// @codingStandardsIgnoreFile
379 380
/**
 * @file
381
 * A database agnostic dump for testing purposes.
382
 *
383
 * This file was generated by the Drupal 8.0 db-tools.php script.
384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425
 */

use Drupal\Core\Database\Database;

$connection = Database::getConnection();

{{TABLES}}

ENDOFSCRIPT;
    return $script;
  }

  /**
   * The part of the script for each table.
   *
   * @param string $table
   *   Table name.
   * @param array $schema
   *   Drupal schema definition.
   * @param array $data
   *   Data for the table.
   *
   * @return string
   *   The table create statement, and if there is data, the insert command.
   */
  protected function getTableScript($table, array $schema, array $data) {
    $output = '';
    $output .= "\$connection->schema()->createTable('" . $table . "', " . Variable::export($schema) . ");\n\n";
    if (!empty($data)) {
      $insert = '';
      foreach ($data as $record) {
        $insert .= "->values(" . Variable::export($record) . ")\n";
      }
      $output .= "\$connection->insert('" . $table . "')\n"
        . "->fields(" . Variable::export(array_keys($schema['fields'])) . ")\n"
        . $insert
        . "->execute();\n\n";
    }
    return $output;
  }

}