11 minute read Published: 14 Sep, 2021 Author: Matt Parker
Drupal Planet , Migrations

Easy commit credits with migrations, part 6: Migrating data from a custom table

This is the sixth in a series of blog posts on writing migrations for contrib modules:

• In part 1, we set up a simple core migration.
• In part 2, I covered how to review and manually tested patches.
• In part 3, I demonstrated how to write automated migration tests
• In part 4, I demonstrated how to migrate simple configuration variables
• In part 5, I explained how to declare a module’s migration status to the migrate wizard
• In this part, I will show you how to migrate data from a custom database table in D7.

Stay tuned for more in this series!

Background

While migrating off Drupal 7 Core is very easy, there are still many contrib modules without any migrations. Any sites built using a low-code approach likely use a lot of contrib modules, and are likely blocked from migrating because of contrib. But — as of this writing — Drupal 7 still makes up 60% of all Drupal sites, and time is running out to migrate them!

If we are to make Drupal the go-to technology for site builders, we need to remember that migrating contrib is part of the Site Builder experience too. If we make migrating easy, then fewer site builders will put off the upgrade or abandon Drupal. Plus, contributing to migrations gives us the opportunity to gain recognition in the Drupal community with contribution credits.

Problem / motivation

In D7, some modules define their own database table to store data in (if the D7 module implemented hook_schema(), then there’s a pretty good chance it defines its own database table).

In order to fully finish writing migrations for contrib modules that define custom tables, we need to know how to migrate data out of those custom tables.

For example, Environment Indicator version 7.x-2.x stores data about each of its environments in a table named environment_indicator_environment, which has the following structure:

… while the 4.x version of Environment Indicator (for D9) stores this data as configuration entities.

We need to migrate data out of the D7 custom table and into the D9 config entities.

Proposed resolution

You may be wondering “What is a configuration entity? I thought nodes, taxonomy terms and user accounts were entities?” Nodes, taxonomy terms, and/or user accounts group data with fields and are now called content entities. Similarly, a configuration entity in D9 groups configuration data with fields. But unlike content entities (whose IDs are usually numbers, e.g.: node 1, etc.), configuration entity IDs are usually machine names (e.g.: production, etc.). Also, while a content entity’s field names usually begin with field_; a configuration entity’s fields usually do not. Due to the similarities between content and configuration entities, they share a lot of code in D9; including the migrate destination plugin entity, which we will use.

But what about migrating out of a custom table? The plugin in the list of core source classes that looks the closest to what we want to do is SqlBase… but SqlBase is marked as “abstract” (meaning that we cannot use it directly), because it’s query() function is abstract. In English, this means that the SqlBase class doesn’t know how to get the data that we want out of the custom table we want! We need to write our own custom Source plugin, which extends SqlBase, and implements its query() function.

Steps to complete

Let’s start by mapping the destination fields in the D9 configuration entity to the source fields in the D7 custom table, as we did for simple configuration:

Given this information, we can write a migration configuration at migrations/d7_environment_indicator_hostname_environments.yml:

id: d7_environment_indicator_hostname_environments
label: Environment indicator hostname environments
migration_tags:
  - Drupal 7
  - Configuration
source:
  plugin: d7_environment_indicator_hostname_environment
process:
  machine: machine
  name: name
  url: regexurl
  fg_color: text_color
  bg_color: color
  description:
    - plugin: default_value
      default_value: ''
destination:
  plugin: 'entity:environment_indicator'

Note the custom source plugin, d7_environment_indicator_hostname_environment: this plugin doesn’t exist yet — we will write it shortly.

The migration’s process configuration should look familiar: we simply map source variables to destination variables; although you’ll notice we’re leaving out the settings. prefix that we used in the mapping table for D7’s settings.text_color and settings.color — because we are writing our own source plugin, we can name the data fields whatever we want.

Finally, we specify the destination plugin entity:environment_indicator. This is the entity migration destination plugin that we mentioned earlier; plus the destination entity ID environment_indicator. We get this ID from the entity type that we’re migrating into — in this case, the configuration entity defined in environment_indicator-4.x’s Drupal\environment_indicator\Entity\EnvironmentIndicator class.

Writing a test

Before we get too much further; we should write a test for the migration we just wrote. In tests/src/Kernel/Migrate/d7/MigrateHostnameEnvironmentsTest.php:

<?php

namespace Drupal\Tests\environment_indicator\Kernel\Migrate\d7;

use Drupal\Core\Database\Database;
use Drupal\environment_indicator\Entity\EnvironmentIndicator;
use Drupal\Tests\migrate_drupal\Kernel\d7\MigrateDrupal7TestBase;

/**
 * Tests migration of environment_indicator hostname environments.
 *
 * @group environment_indicator
 */
class MigrateHostnameEnvironmentsTest extends MigrateDrupal7TestBase {

  /** {@inheritdoc} */
  protected static $modules = ['environment_indicator'];

  /** {@inheritdoc} */
  protected function setUp(): void {
    parent::setUp();

    // Create the environment_indicator_environment table in the D7 database.
    // The schema definition here was copied from version 7.x-2.9.
    Database::getConnection('default', 'migrate')
      ->schema()
      ->createTable('environment_indicator_environment', [
        'fields' => [
          'machine' => [
            'type' => 'varchar',
            'length' => '32',
            'description' => 'Unique ID for environments.',
          ],
          'name' => [
            'type' => 'varchar',
            'length' => '255',
            'description' => 'Name for the environments.',
          ],
          'envid' => [
            'type' => 'serial',
            'unsigned' => TRUE,
            'not null' => TRUE,
            'description' => 'Primary ID field for the table. Not used for anything except internal lookups.',
            'no export' => TRUE,
          ],
          'regexurl' => [
            'type' => 'varchar',
            'length' => '255',
            'description' => 'A regular expression to match against the url.',
          ],
          'settings' => [
            'type' => 'text',
            'size' => 'big',
            'serialize' => TRUE,
            'description' => 'Serialized array with the configuration for the environment.',
          ],
        ],
        'primary key' => ['envid'],
        'unique keys' => [
          'name' => ['machine'],
        ],
      ]);

    $this->setUpD7EnableExtension('module', 'environment_indicator', 7202, 0);
  }

  /** Simulate enabling an extension in the D7 database. */
  protected function setUpD7EnableExtension($type, $extensionName, $schemaVersion, $weight) {
    $extensionName = strval($extensionName);
    Database::getConnection('default', 'migrate')
      ->upsert('system')
      ->key('name')
      ->fields([
        'filename',
        'name',
        'type',
        'owner',
        'status',
        'bootstrap',
        'schema_version',
        'weight',
      ])
      ->values([
        'filename' => sprintf('sites/all/modules/%s/%s.module', $extensionName, $extensionName),
        'name' => $extensionName,
        'type' => strval($type),
        'owner' => '',
        'status' => 1,
        'bootstrap' => 0,
        'schema_version' => intval($schemaVersion),
        'weight' => intval($weight),
      ])
      ->execute();
  }

  /** Tests migrating hostname environments. */
  public function testHostnameEnvironmentMigration() {
    // Fixtures we can verify.
    $machine = 'production';
    $name = 'Production';
    $url = 'https://prod.example.com';
    $bgColor = '#abc123';
    $fgColor = '#def456';

    // Fixtures that won't be migrated.
    $envid = 17;

    // Set up the D7 environment and run the migration.
    $this->setUpD7HostnameEnvironment($machine, $name, $envid, $url, $bgColor, $fgColor, 11, 'top', FALSE);
    $this->executeMigrations(['d7_environment_indicator_hostname_environments']);

    // Load the D8 environment indicator and verify against the fixtures.
    $env = EnvironmentIndicator::load($machine);
    $this->assertInstanceOf('Drupal\environment_indicator\Entity\EnvironmentIndicator', $env);
    $this->assertSame($env->label(), $name);
    $this->assertSame($env->getUrl(), $url);
    $this->assertSame($env->getBgColor(), $bgColor);
    $this->assertSame($env->getFgColor(), $fgColor);
  }

  /** Add a D7 hostname environment to be migrated. */
  protected function setUpD7HostnameEnvironment($machine, $name, $envid, $regexUrl, $bgColor, $textColor, $weight, $position, $fixed) {
    $this->assertIsString($machine, 'Machine name must be a string.');
    $this->assertIsString($name, 'Name must be a string.');
    $this->assertIsInt($envid, 'Envid must be an integer.');
    $this->assertIsString($regexUrl, 'RegexURL must be a string.');

    $settings = [
      'color' => strval($bgColor),
      'text_color' => strval($textColor),
      'weight' => strval($weight),
      'position' => strval($position),
      'fixed' => boolval($fixed),
    ];

    Database::getConnection('default', 'migrate')
      ->upsert('environment_indicator_environment')
      ->key('machine')
      ->fields(['machine', 'name', 'envid', 'regexurl', 'settings'])
      ->values([
        'machine' => $machine,
        'name' => $name,
        'envid' => $envid,
        'regexurl' => $regexUrl,
        'settings' => serialize($settings),
      ])
      ->execute();
  }

}

You’ll notice a few new things in this test: firstly, we use Drupal’s Upsert query to insert or update a row in the database — using an upsert here makes sure that the row we’re testing matches what we expect, without having to check whether a row with the same key already exists. Although we’re not doing it here for clarity; this is useful when randomizing test fixture data. The equivalent raw SQL for Drupal’s Upsert varies based on your database backend — it becomes INSERT ... ON DUPLICATE KEY UPDATE in MySQL; and INSERT ... ON CONFLICT (...) DO UPDATE in PostgreSQL and SQLite.

Also new in this test is a setUp() function — if you recall from PHPUnit’s documentation on writing tests (which was extra reading in part 3 of this blog series), setUp() is run before every test function. In this case, we use it to set up a database schema which we copied from the 7.x-2.9 version of Environment Indicator, i.e.: the version that we’re migrating from. We also have to give the D7 module a row in D7’s system table (which we do in our test’s setUpD7EnableExtension() function).

The test itself (testHostnameEnvironmentMigration()) should look pretty familiar by now: we set up fixtures (using the setUpD7HostnameEnvironment() function to clean things up a bit), run the migration, and test that the fixture data was migrated to the destinations that we expected.

Writing a custom source plugin

Finally, we need to write a custom source plugin… in src/Plugin/migrate/source/d7/D7HostnameEnvironment.php:

<?php

namespace Drupal\environment_indicator\Plugin\migrate\source\d7;

use Drupal\migrate\Row;
use Drupal\migrate_drupal\Plugin\migrate\source\DrupalSqlBase;

/**
 * Drupal 7 Environment Indicator Hostname Environment source from database.
 *
 * @MigrateSource(
 *   id = "d7_environment_indicator_hostname_environment",
 *   source_module = "environment_indicator"
 * )
 */
class D7HostnameEnvironment extends DrupalSqlBase {

  /** {@inheritdoc} */
  public function fields() {
    return [
      'machine' => $this->t('Unique ID for environments.'),
      'name' => $this->t('Name for the environments.'),
      'envid' => $this->t('Primary ID field for the table. Not used for anything except internal lookups.'),
      'regexurl' => $this->t('A regular expression to match against the url.'),
      'settings' => $this->t('Serialized array with the configuration for the environment.'),
      'text_color' => $this->t('The text color of the environment indicator.'),
      'color' => $this->t('The background color of the environment indicator.'),
    ];
  }

  /** {@inheritdoc} */
  public function getIds() {
    $ids['envid']['type'] = 'integer';
    return $ids;
  }

  /** {@inheritdoc} */
  public function query() {
    return $this->select('environment_indicator_environment', 'eie')
      ->fields('eie', [
        'machine',
        'name',
        'envid',
        'regexurl',
        'settings',
      ]);
  }

  /** {@inheritdoc} */
  public function prepareRow(Row $row) {
    $settings = unserialize($row->getSourceProperty('settings'));

    $row->setSourceProperty('text_color', $settings['text_color']);
    $row->setSourceProperty('color', $settings['color']);

    return parent::prepareRow($row);
  }

}

We declare the source plugin ID in the @MigrateSource annotation — this has to match the source plugin ID that we reference in the migration (migrations/d7_environment_indicator_hostname_environments.yml).

Our custom source plugin class extends DrupalSqlBase, which in turn extends the SqlBase class we found earlier when we were looking for source plugins (DrupalSqlBase adds a few Drupal-specific checks and logic).

In the fields() function, we declare which data fields we are going to pass from this custom source plugin to the process part of the migration (this is where we declare text_color and color without the settings prefix). We declare settings and envid even though our migration doesn’t use them, because we need to handle these fields internally in this class.

In the getIds() function, we return the field envid and its type. Drupal 9’s migration subsystem uses fields that you declare in getIds() to understand which data has been migrated, and which data still needs to be migrated (the data returned by this function is used when rolling-back and resuming migrations).

In the query() function, we return a simple Drupal Select query to get data out of the D7 table.

The prepareRow() function runs on each result from the query declared in query(). Here, we perform some post-processing, in this case, by unserializing the data in the settings column, and using it to populate the (unprefixed) text_color and color fields we declared in fields().

Next steps

When you’re writing a custom source plugin, it might be a good idea to make all of the D7 data fields available (provided that it doesn’t add too much additional complexity): another module might extend the one you’re working on, and might want to migrate data from the fields you decided to ignore.

For a more complete example, check out my patch to migrate configuration from environment_manager, which also includes a custom process plugin.

Starting next week, I’ll be taking a bit of a break from this blog series — new posts won’t be coming out as often — but I hope to eventually explore custom process and destination plugins, how to migrate content, and how to migrate data from custom field types defined by a module.

The article Easy commit credits with migrations, part 6: Migrating data from a custom table first appeared on the Consensus Enterprises blog.

We've disabled blog comments to prevent spam, but if you have questions or comments about this post, get in touch!