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 😄

[eelkenet]

Excuse me if I am wrong, but is this not what Module::upgrade($fromVersion, $toVersion) was intended for? Or do you specifically not want to bump a version number?

[bernhard]

That's a very good question 😅

I think that should work as well. One benefit I see with the approach above is that we can define the $schema[] array in one place and then reuse this for the ALTER TABLE statement. Ok you could extract that array into a dedicated method as well, then you'd have basically the same with a Module::upgrade() approach.

Another thing is that I find it easier to read like this:

if($schemaVersion < 2) ...

compared to this:

if(version_compare($oldVersion, $newVersion) < 1) ...

I never know which operator to use here 🙂 And module versions can be confusing, as they can be integers like 104 or version strings like 1.0.4

And last but not least this approach is what I found in FieldtypeComments which is built by Ryan, which should be argument enough 😄 

But thanks for the question! If you want to try another route please let us know what you find out 🙂