Schema subsystem - markstory/cakephp GitHub Wiki

CakeSchema is a bit of a beast handling multiple responsibilities and roles. Ideally there would be a more well defined subsystem that not-only handled converting abstract schema data into concrete dialect specific SQL. But also handled migrations and reflecting abstract schema from concrete platform SQL.

Possible class list involved

  • Cake\Database\Schema\Table = The container for abstract schema definitions.
  • Cake\Database\Schema\Collection = A collection of schema objects (a set of tables in a database)
  • Cake\Database\Schema\MysqlSchema = MySQL related bits for schema creation/reflection.
  • Cake\Database\Schema\SqliteSchema = Sqlite related bits for schema creation/reflection.
  • Cake\Database\Schema\PostgresSchema = Postgres related bits for schema creation/reflection.
  • Cake\Database\Schema\SqlserverSchema = Sqlserver related bits for schema creation/reflection.

The existing schema related features would be removed from the driver/dialect classes and added to the new Schema dialects. However, the functions related to SQL generation would remain on the Driver dialects.

The additional classes may seem like bloat, however they allow the driver and connection objects to stay unburdened by needing to know about the internals of each platforms table structures. This should help keep memory usage lower & run-time performance faster.

How the schema subsystem would be used

Whenever a model/repo/fixture/migration/etc needs to get either a list of tables, or a table schema the schema system would be used. Methods like Connection::describe() would be re-factored to use the schema system. Instead of returning an array a Schema\Table object would be returned which would allow richer interactions to be performed.

Order of work

  1. Create table reflection functionality.
  2. Create table creation functionality.
  3. Update fixtures to use new features.
  4. Create schema migration features (not complete).
  5. Create schema migration console tool (unplanned).

Converting schema data into SQL

The schema subsystem will take declarative array data allow conversion into into CREATE TABLE statements. This allows fixtures to remain declarative and provide an imperative fluent interface.

Schema\Table objects

The schema subsystem could use a simple Schema object to hold data about a schema:

<?php
use Cake\Database\Schema\Table;

// Create a table one column at a time.
$t = new Table('posts');
$t->addColumn('id', [
  'type' => 'integer',
  'length' => 11,
  'null' => false,
  'default' => null,
]);
$t->addColumn('title', [
  'type' => 'string',
  'length' => 255,
  'fixed' => true // Create a fixed length (char field)
]);
$t->addConstraint('primary', [
  'type' => 'primary',
  'columns' => ['id']
]);

// Schema\Table classes could also be created with array data
$t = new Table('posts', $columns);

Schema\Table objects allow you build up a table's schema information in a containing object, that normalizes the various levels of detail CakePHP allows in schema definitions. For example

<?php
$t->addColumn('title', 'string');
// and
$t->addColumn('title', [
  'type' => 'string'
]);

Are equivalent, but the 2nd form allows more detail and control. This emulates the existing features available in Schema files + Fixture schema for 2.x.

Accessing column data

Columns are either added as constructor arguments, or via addColumn() or addColumns(). Once fields are added information can be fetched using column() or columns()

<?php
// Get data about a column
$c = $t->column('title');

// Get the name of all columns.
$cols = $t->columns();

// Get an index
$idx = $t->index('primary');

// Get all the index names.
$idxs = $t->indexes();

Indexes and foreign keys

Adding indexes can be done via constructor arguments, or using the addIndex(). Indexes cannot be added for columns that do not exist, as it would result in an invalid state.

<?php
$t = new Table('posts');
$t->addColumn('id', 'integer')
  ->addColumn('author_id', 'integer')
  ->addColumn('title', 'string')
  ->addColumn('slug', 'string');

// Add a primary key.
$t->addConstraint('primary', [
  'type' => 'primary',
  'columns' => ['id']
]);
// Add a unique key
$t->addConstraint('slug_idx', [
  'columns' => ['slug'],
  'type' => 'unique',
]);
// Add index
$t->addIndex('slug_title', [
  'columns' => ['slug', 'title'],
  'type' => 'index'
]);
// Add a foreign key
$t->addConstraint('author_id_idx', [
  'columns' => ['author_id'],
  'type' => 'foreign',
  'references' => ['authors', 'id'],
  'update' => 'cascade',
  'delete' => 'cascade'
]);

// Get indexes, will return the various indexes that have been added.
$indexes = $t->constraints()

// Get constraints with a matching name
$indexes = $t->constraints('foreign');

// Get a single constraint.
$index = $t->constraint('author_id_idx')

Adding table options

Some drivers (primarily MySQL) support & require additional table metadata. In the case of MySQL the CHARSET,COLLATE and ENGINE properties are required for maintaining a table's structure in MySQL. The following could be used to add table options:

<?php
$t->options([
  'engine' => 'InnoDB',
  'collate' => 'utf8_unicode_ci',
]);

Platform dialects will need to handle only the keys/data they are interested in and ignore the rest, as not all options are support on all platforms.

Converting a schema object into SQL

Once you've created a Schema\Table object the CREATE TABLE SQL can be generated by doing:

<?php
$db = ConnectionManager::getDataSource('default');
$schema = new Table('posts', $fields, $indexes);

// Create a table
$queries = $schema->createSql($db);
foreach ($queries as $sql) {
  $db->execute($sql);
}

// Drop a table
$sql = $schema->dropSql($db);
$db->execute($sql);

By using a connection's driver the schema data can be converted into platform specific SQL. The return of createSql is the set of SQL queries required to create a table and the required indexes. Some platforms may multiple statements to create tables with comments, or indexes, so an array of queries is always returned. This will also make the API usage the same as altering tables.

Schema Collections

SchemaCollections represent multiple tables or an entire application database schema. It provides methods for creating and dropping all tables, as well as comparing the tables from one SchemaCollection with another. SchemaCollections support a number of basic operations:

<?php
// Create a schema collection.
$collection = new SchemaCollection($db);

// Get the table names
$tables = $collection->listTables();

// Get a single table (instance of Schema\Table)
$table = $collection->describe('posts')

Schema Reflection

Schema reflection would be offered by the SchemaCollection object. Once created it could be used to describe the tables + indexes within it.

<?php
$collection = new SchemaCollection($db);

// Get column, index constraint data
$table = $collection->describe('posts');

Described tables are stored in the collection and not-redescribed. SchemaCollections would implement the require __sleep and __wakeup methods to support serialization which will be required for production environments where describing tables is not performant.

Schema management & migration related features

  • Cake\Database\Schema\Diff = A comparison between two schemas. Contains differences between two schema collections and co-ordinates the generation of SQL for migrations.

Schema\Collection features to be added

// Create tables
$collection->createTables();

// Drop tables
$collection->dropTables();

SchemaCollection would also support methods for building schema collections and comparing them. This could be used to create a basic migrations system.

<?php
// Add tables one at a time.
$collection->add($tableOne);
$collection->add($tableTwo);

// Remove a table
$collection->remove('posts');

// Compare with another schema collection.
$schemaDiff = $collection->compare($otherCollection);

In addition to tables, views and stored procedures could be created with the schema subsystem. Reflection and portability are probably not possible, and would be omitted.

<?php
$collection->addProcedure($name, $sql);
$collection->addView($name, $sql);

Comparing schemas

If you have two schema objects of the same tables you can create a diff between the two using the compare method:

<?php
$posts = new Table('posts', $oldSchema);
$updated = new Table('posts', $newSchema);

$diff = $posts->compare($updated);

SchemaDiff objects would provide ways to access the data they contain as well as execute the SQL queries that the differences represent:

<?php
$current = new SchemaCollection($db, $tables);
$new = new SchemaCollection($db, $tables);

// Get a SchemaDiff
$diff = $current->compare($new);

// Get added tables
$add = $diff->addedTables();

// Get Removed tables
$removed = $diff->removedTables();

// Changed tables
$changed = $diff->changedTables();

// Get the SQL statements
$queries = $diff->toSql($db);
foreach ($queries as $query) {
  $db->execute($query['query'], $query['params']);
}