Fieldtype Development: How to update the database schema.

[bernhard]

Today while working on RockCalendar I had the need to change the database schema of my daterange fieldtime to add support for recurring events. I didn't know how to do it, so I had to do some research and that took quite some time.

Here's the solution that I found in FieldtypeComments.module.

When developing the fieldtype you can add a database schema like this and PW will take care of creating the table and columns for you:

public function getDatabaseSchema(Field $field)
{
  $schema = parent::getDatabaseSchema($field);

  $schema['data'] = 'timestamp NOT NULL'; // the from timestamp
  $schema['foo'] = 'timestamp NOT NULL';

  return $schema;
}

This is quite easy, but I wanted to add another column and tried this:

public function getDatabaseSchema(Field $field)
{
  $schema = parent::getDatabaseSchema($field);

  $schema['data'] = 'timestamp NOT NULL'; // the from timestamp
  $schema['foo'] = 'timestamp NOT NULL';
  $schema['bar'] = 'timestamp NOT NULL';

  return $schema;
}

No luck. You will get an error that column "bar" does not exist.

Ok, so we have to modify the table somehow... But we also have to make sure that this is only done once. How to we do that?

The solution is to save the schema version to the field and use that to compare versions and conditionally update the schema:

public function getDatabaseSchema(Field $field)
{
  $schema = parent::getDatabaseSchema($field);

  $schema['data'] = 'timestamp NOT NULL'; // the from timestamp
  $schema['foo'] = 'timestamp NOT NULL';
  $schema['bar'] = 'timestamp NOT NULL';

  $schemaVersion = (int) $field->get('schemaVersion');
  $updateSchema = true;
  $table = $field->getTable();
  $database = wire()->database;

  if ($schemaVersion < 1 && $updateSchema) {
    try {
      if (!$database->columnExists($table, 'bar')) {
        $database->query("ALTER TABLE `$table` ADD bar " . $schema['bar']);
      }
      $field->set('schemaVersion', 1);
      $field->save();
    } catch (\Throwable $th) {
      $this->error($th->getMessage());
      $updateSchema = false;
    }
  }

  return $schema;
}

And maybe at a later point you want to add another column "baz":

public function getDatabaseSchema(Field $field)
{
  $schema = parent::getDatabaseSchema($field);

  $schema['data'] = 'timestamp NOT NULL'; // the from timestamp
  $schema['foo'] = 'timestamp NOT NULL';
  $schema['bar'] = 'timestamp NOT NULL';
  $schema['baz'] = 'timestamp NOT NULL';

  $schemaVersion = (int) $field->get('schemaVersion');
  $updateSchema = true;
  $table = $field->getTable();
  $database = wire()->database;

  if ($schemaVersion < 1 && $updateSchema) {
    try {
      if (!$database->columnExists($table, 'bar')) {
        $database->query("ALTER TABLE `$table` ADD bar " . $schema['bar']);
      }
      $field->set('schemaVersion', 1);
      $field->save();
    } catch (\Throwable $th) {
      $this->error($th->getMessage());
      $updateSchema = false;
    }
  }

  if ($schemaVersion < 2 && $updateSchema) {
    try {
      if (!$database->columnExists($table, 'baz')) {
        $database->query("ALTER TABLE `$table` ADD baz " . $schema['baz']);
      }
      $field->set('schemaVersion', 2);
      $field->save();
    } catch (\Throwable $th) {
      $this->error($th->getMessage());
      $updateSchema = false;
    }
  }

  return $schema;
}

😎

[Jan Romero]

Oh excellent, thanks! I’ve been asking myself this, but been too lazy to actually figure it out instead of just changing the table manually for project-specific modules 😄