Modifying Database schema with Entity Schema v2

This will guide developers through how to modify their database schema when working with entity schema version 2

Creating a table for a new entity

This is about working with a extension that is already in installed in a site and you are now creating a new custom entity and you have defined your entity in the schema/<entity_name>.php file. To ensure that your extension upgrades correctly.

First you need to create a new folder for historical copies of your schema files. It is recommended to use [your_ext]/upgrade/schema. Then copy the current version of schema/<entity_name>.entityType.php and into the newly created schema folder with a reference to your upgrade function number in the file name e.g. [your_ext]/upgrade/schema/10001-myEntity.entityType.php

You then want to create an upgrade function in your upgrader.php file similar to the following which will install

  public static function upgrade_10001(): bool {
    E::schema()->createEntityTable('upgrade/schema/10001-myEntity.entityType.php');
    return TRUE;
  }

This assumes E is a reference to your extensions's extensionUtils class. The reason for a copy rather than reading from the schema folder is so that there is a consistent defintion of the entity.

Upgrading a CiviEntity

Adding a new column or Modifying an existing

To add a new column into an existing custom entity using Entity Schema v2 or modify an exisiting column, you want to define your new column or update your column definition in your entitytype file (schema/<entity_name>.entityType.php). Once you have done that you will then want to add an upgrade function similar to this

  public static function upgrade_900(): bool {
    E::schema()->alterSchemaField('<entity_name>', '<field_name>', [
      Field spec definition copied from the entityType.php for the field.
    ])
    return TRUE;
  }

Dropping a column

If you find you no longer need a column in your schema definition, you want to remove the column from within your entity's entityType.php file in the schema folder and then write an upgrade step like the following

  public static function upgrade_910(): bool {
    E::schema()->dropSchemaField('<entity_name>', '<field_name>');
    return TRUE;
  }

Adding a new index to a table

Sometimes you need to create a new index on your table, perhaps you find that your using one column more than expected. So what you would need to is to define the index appropriately in the getIndices function of the entityType.php file. Then you would need to write an upgrade step similar to the following

  public static function upgrade_920(): bool {
    CRM_Core_BAO_SchemaHandler::createIndexes([
      '<table name>' => [
        either '<fieldName>' OR ['<fieldName1>', '<fieldName2>', ...],
      ]
    ], <then pass in 'UI' if you want the index to be a unique index or not>)
    return TRUE;
  }

Dropping a table

If in your extension you ever need to drop your table, you would need to remove the entityType.php file from the schema folder and then in your extension create an upgrade step like the following. Note it uses the table name and not the name of the entity.

  public static function upgrade_1000(): bool {
    E::schema()->dropTable('<table name>');
    return TRUE;
  }