Schema System

The main pieces of the schema system are 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.

• 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:

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.

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

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:

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 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:

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
$indexes = $schema->indexes()
 
// Get data about a single index.
$index = $schema->index('author_id_idx')

Some drivers (primarily MySQL) support and require additional table metadata. Inthe case of MySQL the CHARSET, COLLATE and 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.

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

Schema Collections

• class Cake\Database\Schema\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();
 
$tableSchema = $collection->describe('posts');