5.2 - Improve schema reflection

[markstory]

Description

Currently both migrations and cakephp/database include logic for schema reflection. While I've been able to use cakephp/database for most schema reflection, there still are some gaps and awkwardness in the APIs that cakephp/database provides. As a result, migrations still requires some of its own schema reflection logic.

We could improve this situation and provide a more complete suite of schema reflection methods by expanding the API of Cake\Database\Schema\SchemaDialect to include higher-level reflection methods.

// These methods would be extracted/moved from SchemaCollection to SchemaDialect.
public function describeTable(string $tableName): Schema\TableSchema
public function listTables(): array
public function listTablesWithoutViews(): array

// New granular reflection methods. The arrays returned by these methods
// would be compatible with Schema\TableSchema.
public function describeColumns(string $tableName): array
public function describeIndexes(string $tableName): array
public function describeConstraints(string $tableName): array
public function describeOptions(string $tableName): array

// Individual existence checks.
public function hasTable(string $tableName): bool
public function hasColumn(string $tableName, string $column): bool
public function hasIndex(string $tableName, string|array $columns = [], ?string $name = null): bool
public function hasForeignKey(string $tableName, string|array $columns = [], ?string $name = null): bool

Why more array return values?

The current methods on TableSchema for operating on columns, indexes, and constraints all use arrays. The new methods would also use arrays to be consistent and compatible with other methods in TableSchema.

Adding object return values to existing methods is hard because of backwards compatibility. Adding objects for columns, indexes and constraints is an option for the new methods. However, it would result in an inconsistent API for both TableSchema and SchemaDialect.

Breaking changes and deprecations

There would be no breaking changes required in 5.x as none of the existing public methods would be changed. With new higher-level
methods being added, we could deprecate the existing describe* and convert* methods:

• listTablesSql()
• listTablesWithoutViewsSql()
• describeColumnSql()
• describeIndexSql()
• describeForeignKeySql()
• describeOptionsSql()
• convertColumnDescription()
• convertIndexDescription()
• convertForeignKeyDescription()
• convertOptionsDescription()

The usage of these methods can be replaced with usage of the new higher-level methods. This would improve the public API of SchemaDialect as the proposed methods are simpler to operate than the existing describe* and convert* methods.

CakePHP Version

5.2