Database Schema Helper Reference

The CiviMix\Schema\SchemaHelperInterface is the unified PHP interface for managing database tables, columns, indexes, and foreign keys in CiviCRM.

It is used in two different contexts:

1. Extensions: Accessed via the upgrader's helper E::schema(). (See Database Upgraders for details on writing extension upgrades).
2. CiviCRM Core: Accessed via Civi::schemaHelper().

Core Upgrade Integration

In CiviCRM Core, database schema changes are typically handled in incremental PHP upgrade classes (such as CRM_Upgrade_Incremental_php_SixFourteen).

Tasks can be registered using base helper methods on CRM_Upgrade_Incremental_Base which internally delegate to Civi::schemaHelper(). For example:

class CRM_Upgrade_Incremental_php_SixFourteen extends CRM_Upgrade_Incremental_Base {

  public function upgrade_6_14_alpha1($rev): void {
    $this->addTask(ts('Upgrade DB to %1: SQL', [1 => $rev]), 'runSql', $rev);

    // Add column using core task wrapper
    $this->addTask('Add File.is_public', 'alterSchemaField', 'File', 'is_public', [
      'title' => ts('File Is Public'),
      'sql_type' => 'boolean',
      'input_type' => 'Toggle',
      'required' => TRUE,
      'description' => ts('Controls whether file is stored in public or private directory.'),
      'default' => FALSE,
    ], 'AFTER `description`');

    // Drop index using core task wrapper
    $this->addTask('Drop civicrm_activity.is_current_revision index', 'dropIndex', 'civicrm_activity', 'index_is_current_revision');
  }

}

You can also retrieve and invoke Civi::schemaHelper() directly in custom static helper methods for complex checks or schema updates:

    // Example: verify if a table exists
    if (\Civi::schemaHelper()->tableExists('civicrm_my_table')) {
      // ...
    }

API Reference

The schema helper object implements CiviMix\Schema\SchemaHelperInterface and provides the following methods for schema management:

Core Methods

• hasSchema(): bool Checks whether the extension/core defines any schema files in its schema/ directory.

• install(): void Installs all SQL tables defined in the schema/ directory.

• uninstall(): void Uninstalls/drops all SQL tables defined in the schema/ directory.

• generateInstallSql(): ?string Generates the raw SQL queries needed to install the tables.

• generateUninstallSql(): string Generates the raw SQL queries needed to drop the tables.

• arrayToSql(array $defn): string Converts an entity or field definition array to its corresponding SQL statement.

Table & Field Methods

• createEntityTable(string $filePath): bool Creates a database table based on a PHP schema file (relative to the root directory). E.g. upgrade/schema/10001-myEntity.entityType.php.

• alterSchemaField(string $entityName, string $fieldName, array $fieldSpec, ?string $position = NULL): bool Adds or modifies a column definition on the table associated with the given entity, based on the provided field specification array.

• dropSchemaField(string $entityName, string $fieldName): bool Drops a column from the table associated with the given entity.

• schemaFieldExists(string $entityName, string $fieldName): bool Checks whether a specific column exists in the table associated with the given entity.

• getTableName(string $entityName): ?string Resolves the database table name associated with the given entity name.

• tableExists(string $tableName): bool Checks whether a database table with the given name exists (case-insensitive).

• getExistingTables(array $tableNames): array Given a list of table names, filters and returns the ones that actually exist in the database (matching case-insensitively but returning the canonical lowercase names).

• dropTable(string $tableName): bool Drops a table with the given name.

Index Methods

• indexExists(string $tableName, string $indexName): bool Checks whether a specific index exists on a table.

• createIndex(string $tableName, string $indexName, array $indexDef): bool Creates an index on a table using the definition format (e.g. ['fields' => ['col1' => TRUE], 'unique' => TRUE]).

• dropIndex(string $tableName, string $indexName): bool Drops a specific index from a table.

Foreign Key Methods

• foreignKeyExists(string $tableName, string $foreignKeyName): bool Checks whether a foreign key constraint exists on a table.

• createForeignKey(string $tableName, string $fieldName, array $fieldSpec): bool Creates a foreign key constraint on a table for a field based on its schema specification.

• dropForeignKey(string $tableName, string $foreignKeyName): bool Drops a specific foreign key constraint from a table.