Migration

Index > Database > Migration

---

Schema migration provides an easy and tidy way to manage database tables. Migration consists of two components:

• the schema definition (i.e. table definitions)
• the migrations table, used to keep a history of the changes made to the schema

The Table class

A SnooPHP\Model\Table object represents a single table inside a schema. it has a name (i.e. the name of the table inside the database) and a set of SnooPHP\Model\Column objects, each one representing a column of the table.

To create a new Table object, pass a name to the constructor:

$users = new Table("users");

Columns can be added using methods like Table::string() or Table::int(). Read the docs for a complete list.

$users->id();
$users->string("username");
$users->string("email");
$users->string("password", 64);	// size of string
$users->timestamp("created_at");

The id() column is of particular importance, since many features of the Model class (next argument) require the presence of an "id" column (i.e. an integer, primary key, auto-increment column).

We can add properties to columns just like we would do in MySQL:

$users->uint("id", 16)->notNullable()->autoIncrement();	// Equivalent to id()
$users->string("username")->notNullable()->unique();
$users->string("email")->notNullable()->unique();
$users->string("password")->notNullable();

Read the docs for a complete list of column properties

Constraints

Constraints are treated as column properties.

Non-composite constraints are created with:

• Column::unique()
• Column::primary()
• Column::references(string $table, string $column, string|null $onDelete = "no action", string|null $onUpdate = "no action")

Composite constraints are defined with similar methods, but take an additional argument $closeChain = false:

$memberships = new Table("memberships");
$memberships->uint("user_id")->uniqueComposite()->references("users", "id", "cascade");		// Open unique chain
$memberships->uint("group_id")->uniqueComposite(true)->references("users", "id", "cascade");	// Close unique chain

Such methods are:

• Column::uniqueComposite(bool $closeChain = false)
• Column::primaryComposite(bool $closeChain = false)
• Column::referencesComposite(string $column, bool $closeChain = false, string $table = "table", string|null $onDelete = "no action", string|null $onUpdate = "no action")

In a composite foreign key, only the $table, $onDelete and $onUpdate of the closing column are considered.

SQL-esque table declaration (v1.0.1+)

Since v1.0.1 it's possible to declare tables like this:

$users = new Table("Model\User");
$users->generate("
	: id(32),
	: timestamps,
	username* U : string(127),
	email* U : string(127),
	password : string(72)
");

The table engine parses the declaration line-by-line and generates the table data required for migration:

• each column declaration must be on a single line in the form name : type.
• if name is immediately followed by * than the field is required and is not nullable
• a column references another column with the arrow operator ->

   "user_email -> Model\User::email(cascade, no action) : string(127)"

  foreign column, on delete and on update conditions are optional
• U or P after name indicate that the column is either a unique or a primary key.
• composite chains are created by appending a + to the key type and closed by appending ;
• the size of the type can be specified inside a pair of parenthesis after the type
• a default value is defined by appending = <default> at the end of the line

   "is_public : bool = 0"

• each line can be terminated by a comma, a semi-colon or nothing at all (the urge to put a comma or a semi-colon at the end was too strong)

Migrating

The migration logic for a single table is contained in the Table class. To run the migration supply the old table to the Table::migrate(Table $old = null, string $dbName = "master") method:

$status = $users->migrate($oldUsers, "master");

The second parameter is the name of the database connection to use ("master" by default).

If null is passed instead of the old table, the migration script will execute Table::create(string $dbName = "master") instead.

You can generate a create statement string using Table::createQuery().

Note: createQuery() doesn't create the table in the database, just returns the CREATE TABLE as a string.

To drop a single table, call Table::drop(string $dbName = "master").

The migration process is divided in 4 steps:

• for each column of the old table, drop constraints. This is required to perform some operations that would otherwise try to break the database integrity;
• for each column of the new table, determine if the column was created or changed. Create new columns and alter changed ones;
• readd constraints on new table columns;
• for each old column determine whether it was deleted. If so, drop the column.

It is possible that errors occur during the migration process. In that case a manual fix may be necessary.

The Migration class

Another class, the SnooPHP\Model\Migration class, manages migrations for all tables of the schema.

The constructor of the class takes the name of the database connection and an array of Table objects as parameters:

$migration = new Migration("master", [$users, $groups, $memberships]);

It is also possible to register a table using Migration::register(Table $table) method.

The Migration object currently exposes three commands that you can run:

• "migrate" run migration process on all tables
• "drop" drop all tables, drop migrations table
• "reset" first drop all, then migrate

We can run any of these commands calling Migration::run(string $command = "migrate"):

$migration->run($argv[1] ?? "none");

You don't need to worry about supplying the old tables. The Migration class mantains an history in a migrations table of the database.

In a newly created project, you can find a master migration file in the /database folder.

<?php

require_once __DIR__."/../bootstrap.php";

use SnooPHP\Model\Table;
use SnooPHP\Model\Migration;

/*********************
 * MASTER DATABASE
 * 
 * Here you can define
 * the master database
 *********************/
// <-- Table definitions

$tables = [
	/**
	 * Register tables here
	 */
];

/**************
 * RUN SCRIPT
 * 
 * don't modify
 **************/
$migration = new Migration(basename($argv[0], ".php"), $tables);
for ($i = 1; $i < $argc; $i++) $migration->run($argv[$i]);

After defining and registering the tables, simply run:

$ php database/master.php migrate # or drop/reset

You can copy and rename this file with the name of a different database connection if you want to perform the migration on another database.

---

The Db class >