Chris@0: setName('dump-database-d8-mysql') Chris@0: ->setDescription('Dump the current database to a generation script') Chris@0: ->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: parent::configure(); Chris@0: } Chris@0: Chris@0: /** Chris@0: * {@inheritdoc} Chris@0: */ Chris@0: protected function execute(InputInterface $input, OutputInterface $output) { Chris@0: $connection = $this->getDatabaseConnection($input); Chris@0: Chris@0: // If not explicitly set, disable ANSI which will break generated php. Chris@0: if ($input->hasParameterOption(['--ansi']) !== TRUE) { Chris@0: $output->setDecorated(FALSE); Chris@0: } Chris@0: Chris@0: $schema_tables = $input->getOption('schema-only'); Chris@0: $schema_tables = explode(',', $schema_tables); Chris@0: Chris@0: $output->writeln($this->generateScript($connection, $schema_tables), OutputInterface::OUTPUT_RAW); Chris@0: } Chris@0: Chris@0: /** Chris@0: * Generates the database script. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param array $schema_only Chris@0: * Table patterns for which to only dump the schema, no data. Chris@0: * @return string Chris@0: * The PHP script. Chris@0: */ Chris@0: protected function generateScript(Connection $connection, array $schema_only = []) { Chris@0: $tables = ''; Chris@0: Chris@0: $schema_only_patterns = []; Chris@0: foreach ($schema_only as $match) { Chris@0: $schema_only_patterns[] = '/^' . $match . '$/'; Chris@0: } Chris@0: Chris@0: foreach ($this->getTables($connection) as $table) { Chris@0: $schema = $this->getTableSchema($connection, $table); Chris@0: // Check for schema only. Chris@0: if (empty($schema_only_patterns) || preg_replace($schema_only_patterns, '', $table)) { Chris@0: $data = $this->getTableData($connection, $table); Chris@0: } Chris@0: else { Chris@0: $data = []; Chris@0: } Chris@0: $tables .= $this->getTableScript($table, $schema, $data); Chris@0: } Chris@0: $script = $this->getTemplate(); Chris@0: // Substitute in the tables. Chris@0: $script = str_replace('{{TABLES}}', trim($tables), $script); Chris@0: return trim($script); Chris@0: } Chris@0: Chris@0: /** Chris@0: * Returns a list of tables, not including those set to be excluded. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @return array Chris@0: * An array of table names. Chris@0: */ Chris@0: protected function getTables(Connection $connection) { Chris@0: $tables = array_values($connection->schema()->findTables('%')); Chris@0: Chris@0: foreach ($tables as $key => $table) { Chris@0: // Remove any explicitly excluded tables. Chris@0: foreach ($this->excludeTables as $pattern) { Chris@0: if (preg_match('/^' . $pattern . '$/', $table)) { Chris@0: unset($tables[$key]); Chris@0: } Chris@0: } Chris@0: } Chris@0: Chris@0: return $tables; Chris@0: } Chris@0: Chris@0: /** Chris@0: * Returns a schema array for a given table. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $table Chris@0: * The table name. Chris@0: * Chris@0: * @return array Chris@0: * A schema array (as defined by hook_schema()). Chris@0: * Chris@0: * @todo This implementation is hard-coded for MySQL. Chris@0: */ Chris@0: protected function getTableSchema(Connection $connection, $table) { Chris@0: // Check this is MySQL. Chris@0: if ($connection->databaseType() !== 'mysql') { Chris@0: throw new \RuntimeException('This script can only be used with MySQL database backends.'); Chris@0: } Chris@0: Chris@0: $query = $connection->query("SHOW FULL COLUMNS FROM {" . $table . "}"); Chris@0: $definition = []; Chris@0: while (($row = $query->fetchAssoc()) !== FALSE) { Chris@0: $name = $row['Field']; Chris@0: // Parse out the field type and meta information. Chris@0: preg_match('@([a-z]+)(?:\((\d+)(?:,(\d+))?\))?\s*(unsigned)?@', $row['Type'], $matches); Chris@17: $type = $this->fieldTypeMap($connection, $matches[1]); Chris@0: if ($row['Extra'] === 'auto_increment') { Chris@0: // If this is an auto increment, then the type is 'serial'. Chris@0: $type = 'serial'; Chris@0: } Chris@0: $definition['fields'][$name] = [ Chris@0: 'type' => $type, Chris@0: 'not null' => $row['Null'] === 'NO', Chris@0: ]; Chris@0: if ($size = $this->fieldSizeMap($connection, $matches[1])) { Chris@0: $definition['fields'][$name]['size'] = $size; Chris@0: } Chris@0: if (isset($matches[2]) && $type === 'numeric') { Chris@0: // Add precision and scale. Chris@0: $definition['fields'][$name]['precision'] = $matches[2]; Chris@0: $definition['fields'][$name]['scale'] = $matches[3]; Chris@0: } Chris@17: elseif ($type === 'time') { Chris@0: // @todo Core doesn't support these, but copied from `migrate-db.sh` for now. Chris@0: // Convert to varchar. Chris@0: $definition['fields'][$name]['type'] = 'varchar'; Chris@0: $definition['fields'][$name]['length'] = '100'; Chris@0: } Chris@17: elseif ($type === 'datetime') { Chris@17: // Adjust for other database types. Chris@17: $definition['fields'][$name]['mysql_type'] = 'datetime'; Chris@17: $definition['fields'][$name]['pgsql_type'] = 'timestamp without time zone'; Chris@17: $definition['fields'][$name]['sqlite_type'] = 'varchar'; Chris@17: $definition['fields'][$name]['sqlsrv_type'] = 'smalldatetime'; Chris@17: } Chris@0: elseif (!isset($definition['fields'][$name]['size'])) { Chris@0: // Try use the provided length, if it doesn't exist default to 100. It's Chris@0: // not great but good enough for our dumps at this point. Chris@0: $definition['fields'][$name]['length'] = isset($matches[2]) ? $matches[2] : 100; Chris@0: } Chris@0: Chris@0: if (isset($row['Default'])) { Chris@0: $definition['fields'][$name]['default'] = $row['Default']; Chris@0: } Chris@0: Chris@0: if (isset($matches[4])) { Chris@0: $definition['fields'][$name]['unsigned'] = TRUE; Chris@0: } Chris@0: Chris@0: // Check for the 'varchar_ascii' type that should be 'binary'. Chris@0: if (isset($row['Collation']) && $row['Collation'] == 'ascii_bin') { Chris@0: $definition['fields'][$name]['type'] = 'varchar_ascii'; Chris@0: $definition['fields'][$name]['binary'] = TRUE; Chris@0: } Chris@0: Chris@0: // Check for the non-binary 'varchar_ascii'. Chris@0: if (isset($row['Collation']) && $row['Collation'] == 'ascii_general_ci') { Chris@0: $definition['fields'][$name]['type'] = 'varchar_ascii'; Chris@0: } Chris@0: Chris@0: // Check for the 'utf8_bin' collation. Chris@0: if (isset($row['Collation']) && $row['Collation'] == 'utf8_bin') { Chris@0: $definition['fields'][$name]['binary'] = TRUE; Chris@0: } Chris@0: } Chris@0: Chris@0: // Set primary key, unique keys, and indexes. Chris@0: $this->getTableIndexes($connection, $table, $definition); Chris@0: Chris@0: // Set table collation. Chris@0: $this->getTableCollation($connection, $table, $definition); Chris@0: Chris@0: return $definition; Chris@0: } Chris@0: Chris@0: /** Chris@0: * Adds primary key, unique keys, and index information to the schema. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $table Chris@0: * The table to find indexes for. Chris@0: * @param array &$definition Chris@0: * The schema definition to modify. Chris@0: */ Chris@0: protected function getTableIndexes(Connection $connection, $table, &$definition) { Chris@0: // Note, this query doesn't support ordering, so that is worked around Chris@0: // below by keying the array on Seq_in_index. Chris@0: $query = $connection->query("SHOW INDEX FROM {" . $table . "}"); Chris@0: while (($row = $query->fetchAssoc()) !== FALSE) { Chris@0: $index_name = $row['Key_name']; Chris@0: $column = $row['Column_name']; Chris@0: // Key the arrays by the index sequence for proper ordering (start at 0). Chris@0: $order = $row['Seq_in_index'] - 1; Chris@0: Chris@0: // If specified, add length to the index. Chris@0: if ($row['Sub_part']) { Chris@0: $column = [$column, $row['Sub_part']]; Chris@0: } Chris@0: Chris@0: if ($index_name === 'PRIMARY') { Chris@0: $definition['primary key'][$order] = $column; Chris@0: } Chris@0: elseif ($row['Non_unique'] == 0) { Chris@0: $definition['unique keys'][$index_name][$order] = $column; Chris@0: } Chris@0: else { Chris@0: $definition['indexes'][$index_name][$order] = $column; Chris@0: } Chris@0: } Chris@0: } Chris@0: Chris@0: /** Chris@0: * Set the table collation. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $table Chris@0: * The table to find indexes for. Chris@0: * @param array &$definition Chris@0: * The schema definition to modify. Chris@0: */ Chris@0: protected function getTableCollation(Connection $connection, $table, &$definition) { Chris@0: $query = $connection->query("SHOW TABLE STATUS LIKE '{" . $table . "}'"); Chris@0: $data = $query->fetchAssoc(); Chris@0: Chris@17: // Map the collation to a character set. For example, 'utf8mb4_general_ci' Chris@17: // (MySQL 5) or 'utf8mb4_0900_ai_ci' (MySQL 8) will be mapped to 'utf8mb4'. Chris@17: list($charset,) = explode('_', $data['Collation'], 2); Chris@17: Chris@0: // Set `mysql_character_set`. This will be ignored by other backends. Chris@17: $definition['mysql_character_set'] = $charset; Chris@0: } Chris@0: Chris@0: /** Chris@0: * Gets all data from a given table. Chris@0: * Chris@0: * If a table is set to be schema only, and empty array is returned. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $table Chris@0: * The table to query. Chris@0: * Chris@0: * @return array Chris@0: * The data from the table as an array. Chris@0: */ Chris@0: protected function getTableData(Connection $connection, $table) { Chris@0: $order = $this->getFieldOrder($connection, $table); Chris@0: $query = $connection->query("SELECT * FROM {" . $table . "} " . $order); Chris@0: $results = []; Chris@0: while (($row = $query->fetchAssoc()) !== FALSE) { Chris@0: $results[] = $row; Chris@0: } Chris@0: return $results; Chris@0: } Chris@0: Chris@0: /** Chris@0: * Given a database field type, return a Drupal type. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $type Chris@0: * The MySQL field type. Chris@0: * Chris@0: * @return string Chris@0: * The Drupal schema field type. If there is no mapping, the original field Chris@0: * type is returned. Chris@0: */ Chris@0: protected function fieldTypeMap(Connection $connection, $type) { Chris@0: // Convert everything to lowercase. Chris@0: $map = array_map('strtolower', $connection->schema()->getFieldTypeMap()); Chris@0: $map = array_flip($map); Chris@0: Chris@0: // The MySql map contains type:size. Remove the size part. Chris@0: return isset($map[$type]) ? explode(':', $map[$type])[0] : $type; Chris@0: } Chris@0: Chris@0: /** Chris@0: * Given a database field type, return a Drupal size. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $type Chris@0: * The MySQL field type. Chris@0: * Chris@0: * @return string Chris@0: * The Drupal schema field size. Chris@0: */ Chris@0: protected function fieldSizeMap(Connection $connection, $type) { Chris@0: // Convert everything to lowercase. Chris@0: $map = array_map('strtolower', $connection->schema()->getFieldTypeMap()); Chris@0: $map = array_flip($map); Chris@0: Chris@0: $schema_type = explode(':', $map[$type])[0]; Chris@0: // Only specify size on these types. Chris@0: if (in_array($schema_type, ['blob', 'float', 'int', 'text'])) { Chris@0: // The MySql map contains type:size. Remove the type part. Chris@0: return explode(':', $map[$type])[1]; Chris@0: } Chris@0: } Chris@0: Chris@0: /** Chris@0: * Gets field ordering for a given table. Chris@0: * Chris@0: * @param \Drupal\Core\Database\Connection $connection Chris@0: * The database connection to use. Chris@0: * @param string $table Chris@0: * The table name. Chris@0: * Chris@0: * @return string Chris@0: * The order string to append to the query. Chris@0: */ Chris@0: protected function getFieldOrder(Connection $connection, $table) { Chris@0: // @todo this is MySQL only since there are no Database API functions for Chris@0: // table column data. Chris@0: // @todo this code is duplicated in `core/scripts/migrate-db.sh`. Chris@0: $connection_info = $connection->getConnectionOptions(); Chris@0: // Order by primary keys. Chris@0: $order = ''; Chris@0: $query = "SELECT `COLUMN_NAME` FROM `information_schema`.`COLUMNS` Chris@0: WHERE (`TABLE_SCHEMA` = '" . $connection_info['database'] . "') Chris@0: AND (`TABLE_NAME` = '{" . $table . "}') AND (`COLUMN_KEY` = 'PRI') Chris@0: ORDER BY COLUMN_NAME"; Chris@0: $results = $connection->query($query); Chris@0: while (($row = $results->fetchAssoc()) !== FALSE) { Chris@0: $order .= $row['COLUMN_NAME'] . ', '; Chris@0: } Chris@0: if (!empty($order)) { Chris@0: $order = ' ORDER BY ' . rtrim($order, ', '); Chris@0: } Chris@0: return $order; Chris@0: } Chris@0: Chris@0: /** Chris@0: * The script template. Chris@0: * Chris@0: * @return string Chris@0: * The template for the generated PHP script. Chris@0: */ Chris@0: protected function getTemplate() { Chris@0: // The template contains an instruction for the file to be ignored by PHPCS. Chris@0: // This is because the files can be huge and coding standards are Chris@0: // irrelevant. Chris@0: $script = <<<'ENDOFSCRIPT' Chris@0: schema()->createTable('" . $table . "', " . Variable::export($schema) . ");\n\n"; Chris@0: if (!empty($data)) { Chris@0: $insert = ''; Chris@0: foreach ($data as $record) { Chris@0: $insert .= "->values(" . Variable::export($record) . ")\n"; Chris@0: } Chris@18: $fields = Variable::export(array_keys($schema['fields'])); Chris@18: $output .= <<insert('$table') Chris@18: ->fields($fields) Chris@18: {$insert}->execute(); Chris@18: Chris@18: EOT; Chris@0: } Chris@0: return $output; Chris@0: } Chris@0: Chris@0: }