Problem/Motivation

To write automated update tests for Drupal 8 & 9, one often needs to use a fixture with a database dump as explained in the doc
Writing Automated Update Tests for Drupal 8. The tool used to generate such a dump is only compatible with MySQL as explained in a comment in "DbDumpCommand.php":

 * @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

I'm participating to the development of a module that only works with PostgreSQL and I can't use the following command line to generate a dump:
php ./core/scripts/db-tools.php dump-database-d8-mysql --database-url='pgsql://login:password@127.0.0.1:5432/drupal_database'

Steps to reproduce

  • Install Drupal on a PostgreSQL database
  • On your shell, try php ./core/scripts/db-tools.php dump-database-d8-mysql --database-url='pgsql://login:password@127.0.0.1:5432/drupal_database'

You will get the exception This script can only be used with MySQL database backends..

Proposed resolution

I'm not sure having \Drupal\Core\Database\Schema support the ability to retrieve table schema information is a requirement. Maybe it is? However, I succeeded in generating a dump using the attached patch. This patch is only based on DB queries that use SQL information_schema and should be generic enough.

Remaining tasks

Test this patch against MySQL database to make sure the dumps are not altered compared with the original version of the scripts. Try on different environments (PostgreSQL versions) than mine.

API changes

A new protected member function "getDrupalSchemaName()" has been added to Drupal\Core\Command\DbDumpCommand.

CommentFileSizeAuthor
#5 dump_db_postgresql-3222157-5.patch11.42 KBguignonv
#3 dump_db_postgresql-000-1.patch6.72 KBguignonv
dump_db_postgresql-000-1.patch7.02 KBguignonv

Comments

guignonv created an issue. See original summary.

guignonv’s picture

guignonv
Primary language French
Location Montpellier
commented

I forgot to mention the patch changes the command line from:
php ./core/scripts/db-tools.php dump-database-d8-mysql --database-url='pgsql://login:password@127.0.0.1:5432/drupal_database'
to
php ./core/scripts/db-tools.php dump-database-d8-sql --database-url='pgsql://login:password@127.0.0.1:5432/drupal_database'
("my" removed).

guignonv’s picture

guignonv
Primary language French
Location Montpellier
commented
StatusFileSize
new dump_db_postgresql-000-1.patch6.72 KB
cilefen’s picture

cilefen commented

Can you alias the original command name for backwards compatibility?

guignonv’s picture

guignonv
Primary language French
Location Montpellier
commented
StatusFileSize
new dump_db_postgresql-3222157-5.patch11.42 KB

Sure, you're right. I didn't think about it. My bad.

mradcliffe’s picture

mradcliffe
he/him
Primary language English
commented

Thanks for the patch.

I was going to confirm on my local PostgreSQL 11.6 environment a diff of dumps specifically to confirm if there were any long key names by default that get hashed by pgsql driver and not mysql driver. But the script isn't generating any schema.

Additionally, even with --schema-only, the data is dumped, and whether or not I provide values to --schema-only, it generates a dump for all tables. I get the same result without the --schema-only option.

Example ./core/scripts/db-tools.php dump-database-d8-sql --schema-only --database-url=pgsql://drupal:drupal@postgresql/drupal. 

// phpcs:ignoreFile
/**
 * @file
 * A database agnostic dump for testing purposes.
 *
 * This file was generated by the Drupal 9.2.1-dev db-tools.php script.
 */

use Drupal\Core\Database\Database;

$connection = Database::getConnection();

$connection->schema()->createTable('user__roles', array());

$connection->insert('user__roles')
->fields(NULL)
->values(array(
  'bundle' => 'user',
  'deleted' => '0',
  'entity_id' => '1',
  'revision_id' => '1',
  'langcode' => 'en',
  'delta' => '0',
  'roles_target_id' => 'administrator',
))
->execute();
$connection->schema()->createTable('block_content_revision__body', array());

$connection->schema()->createTable('queue', array());

$connection->schema()->createTable('batch', array());

// Etc... until end

Version: 9.2.x-dev » 9.3.x-dev

Drupal 9.1.10 (June 4, 2021) and Drupal 9.2.10 (November 24, 2021) were the last bugfix releases of those minor version series. Drupal 9 bug reports should be targeted for the 9.3.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.4.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.3.x-dev » 9.4.x-dev

Drupal 9.3.15 was released on June 1st, 2022 and is the final full bugfix release for the Drupal 9.3.x series. Drupal 9.3.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.4.x-dev branch from now on, and new development or disruptive changes should be targeted for the 9.5.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.4.x-dev » 9.5.x-dev

Drupal 9.4.9 was released on December 7, 2022 and is the final full bugfix release for the Drupal 9.4.x series. Drupal 9.4.x will not receive any further development aside from security fixes. Drupal 9 bug reports should be targeted for the 9.5.x-dev branch from now on, and new development or disruptive changes should be targeted for the 10.1.x-dev branch. For more information see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

Version: 9.5.x-dev » 11.x-dev

Drupal core is moving towards using a “main” branch. As an interim step, a new 11.x branch has been opened, as Drupal.org infrastructure cannot currently fully support a branch named main. New developments and disruptive changes should now be targeted for the 11.x branch. For more information, see the Drupal core minor version schedule and the Allowed changes during the Drupal core release cycle.

quietone’s picture

quietone commented
Issue tags: +PostgreSQL

Version: 11.x-dev » main

Drupal core is now using the main branch as the primary development branch. New developments and disruptive changes should now be targeted to the main branch.

Read more in the announcement.