Schema System

CakePHP features a schema system that is capable of reflecting and generatingschema information for tables in SQL datastores. The schema system cangenerate/reflect a schema for any SQL platform that CakePHP supports.

The main pieces of the schema system are Cake\Database\Schema\Collection andCake\Database\Schema\TableSchema. These classes give you access todatabase-wide and individual Table object features respectively.

The primary use of the schema system is for Fixtures. However, itcan also be used in your application if required.

Schema\TableSchema Objects

• class Cake\Database\Schema\TableSchema
• The schema subsystem provides a simple TableSchema object to hold data about atable in a database. This object is returned by the schema reflectionfeatures:

use Cake\Database\Schema\TableSchema;
 
// Create a table one column at a time.
$schema = new TableSchema('posts');
$schema->addColumn('id', [
  'type' => 'integer',
  'length' => 11,
  'null' => false,
  'default' => null,
])->addColumn('title', [
  'type' => 'string',
  'length' => 255,
  // Create a fixed length (char field)
  'fixed' => true
])->addConstraint('primary', [
  'type' => 'primary',
  'columns' => ['id']
]);
 
// Schema\TableSchema classes could also be created with array data
$schema = new TableSchema('posts', $columns);

Schema\TableSchema objects allow you to build up information about a table’s schema. It helps tonormalize and validate the data used to describe a table. For example, thefollowing two forms are equivalent:

$schema->addColumn('title', 'string');
// and
$schema->addColumn('title', [
  'type' => 'string'
]);

While equivalent, the 2nd form allows more detail and control. This emulatesthe existing features available in Schema files + the fixture schema in 2.x.

Accessing Column Data

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

// Get the array of data about a column
$c = $schema->column('title');
 
// Get the list of all columns.
$cols = $schema->columns();

Indexes and Constraints

Indexes are added using the addIndex(). Constraints are added usingaddConstraint(). Indexes and constraints cannot be added for columns that donot exist, as it would result in an invalid state. Indexes are different fromconstraints, and exceptions will be raised if you try to mix types between themethods. An example of both methods is:

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

If you add a primary key constraint to a single integer column it will automaticallybe converted into a auto-increment/serial column depending on the databaseplatform:

$schema = new TableSchema('posts');
$schema->addColumn('id', 'integer')
->addConstraint('primary', [
    'type' => 'primary',
    'columns' => ['id']
]);

In the above example the id column would generate the following SQL inMySQL:

CREATE TABLE `posts` (
    `id` INTEGER AUTO_INCREMENT,
    PRIMARY KEY (`id`)
)

If your primary key contains more than one column, none of them willautomatically be converted to an auto-increment value. Instead you will need totell the table object which column in the composite key you want toauto-increment:

$schema = new TableSchema('posts');
$schema->addColumn('id', [
    'type' => 'integer',
    'autoIncrement' => true,
])
->addColumn('account_id', 'integer')
->addConstraint('primary', [
    'type' => 'primary',
    'columns' => ['id', 'account_id']
]);

The autoIncrement option only works with integer and bigintegercolumns.

Reading Indexes and Constraints

Indexes and constraints can be read out of a table object using accessormethods. Assuming that $schema is a populated TableSchema instance you could do thefollowing:

// Get contraints. Will return the
// names of all constraints.
$constraints = $schema->constraints()
 
// Get data about a single constraint.
$constraint = $schema->constraint('author_id_idx')
 
// Get indexes. Will return the
// names of all indexes.
$indexes = $schema->indexes()
 
// Get data about a single index.
$index = $schema->index('author_id_idx')

Adding Table Options

Some drivers (primarily MySQL) support and require additional table metadata. Inthe case of MySQL the CHARSET, COLLATE and ENGINE properties arerequired for maintaining a table’s structure in MySQL. The following could beused to add table options:

$schema->options([
  'engine' => 'InnoDB',
  'collate' => 'utf8_unicode_ci',
]);

Platform dialects only handle the keys they are interested inand ignore the rest. Not all options are supported on all platforms.

Converting Tables into SQL

Using the createSql() or dropSql() you can getplatform specific SQL for creating or dropping a specific table:

$db = ConnectionManager::get('default');
$schema = new TableSchema('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 platformspecific SQL. The return of createSql and dropSql is a list of SQLqueries required to create a table and the required indexes. Some platforms mayrequire multiple statements to create tables with comments and/or indexes. Anarray of queries is always returned.

Schema Collections

• class Cake\Database\Schema\Collection
• Collection provides access to the various tables available on a connection.You can use it to get the list of tables or reflect tables intoTableSchema objects. Basic usage of the class looks like:

$db = ConnectionManager::get('default');
 
// Create a schema collection.
$collection = $db->schemaCollection();
 
// Get the table names
$tables = $collection->listTables();
 
// Get a single table (instance of Schema\TableSchema)
$tableSchema = $collection->describe('posts');