<a class="badge" href="https://circleci.com/gh/aimeos/upscheme"><img src="https://circleci.com/gh/aimeos/upscheme.svg?style=shield" alt="Build Status" height="20"></a> <a class="badge" href="https://coveralls.io/github/aimeos/upscheme"><img src="https://coveralls.io/repos/github/aimeos/upscheme/badge.svg" alt="Coverage Status" height="20"></a> <a class="badge" href="https://packagist.org/packages/aimeos/upscheme"><img src="https://poser.pugx.org/aimeos/upscheme/license.svg" alt="License" height="20"></a>

Upscheme: Database schema updates made easy

Easy to use PHP package for updating the database schema of your application and migrate data between versions.

composer req aimeos/upscheme

Table of contents

• Why Upscheme
• Database support
• Integrating Upscheme
• Writing migrations
  • Naming
  • Dependencies
  • Messages
  • Schemas
  • Generate from database
• Database
  • Accessing objects
  • Checking existence
  • Renaming objects
  • Removing objects
  • Query/modify table rows
  • Executing custom SQL
  • Database methods
• Tables
  • Creating tables
  • Setting table options
  • Checking table existence
  • Changing tables
  • Renaming tables
  • Dropping tables
  • Table methods
• Columns
  • Adding columns
  • Available column types
  • Column modifiers
  • Checking column existence
  • Changing columns
  • Renaming columns
  • Dropping columns
  • Column methods
• Foreign keys
  • Creating foreign keys
  • Checking foreign key existence
  • Dropping foreign keys
  • Foreign key methods
• Sequences
  • Adding sequences
  • Checking sequence existence
  • Dropping sequences
  • Sequence methods
• Indexes
  • Adding indexes
  • Checking index existence
  • Renaming indexes
  • Dropping indexes
  • Custom index naming
• Customizing Upscheme
  • Adding custom methods
  • Implementing custom columns
• Upgrade Upscheme

Why Upscheme

Migrations are like version control for your database. They allow you to get the exact same state in every installation. Using Upscheme, you get:

• one place for defining tables, columns, indexes, etc. easily
• upgrades from any state in between to the expected schema
• consistent, reliable and hassle-free schema upgrades
• minimal code required for writing migrations
• perfect solution for continuous deployments
• best package for cloud-based PHP applications

Here's an example of a table definition that you can adapt whenever your table layout must change. Then, Upscheme will automatically add and modify existing columns and table properties (but don't delete anything for safety reasons):

$this->db()->table( 'test', function( $t ) {
	$t->engine = 'InnoDB';

	$t->id();
	$t->string( 'domain', 32 );
	$t->string( 'code', 64 )->opt( 'charset', 'binary', ['mariadb', 'mysql'] );
	$t->string( 'label', 255 );
	$t->int( 'pos' )->default( 0 );
	$t->smallint( 'status' );
	$t->default();

	$t->unique( ['domain', 'code'] );
	$t->index( ['status', 'pos'] );
} );

For upgrading relational database schemas, two packages are currently used most often: Doctrine DBAL and Doctrine migrations. While Doctrine DBAL does a good job in abstracting the differences of several database implementations, it's API requires writing a lot of code. Doctrine migrations on the other site has some drawbacks which make it hard to use in all applications that support 3rd party extensions.

Doctrine DBAL drawbacks

The API of DBAL is very verbose and you need to write lots of code even for simple things. Upscheme uses Doctrine DBAL to offer an easy to use API for upgrading the database schema of your application with minimal code. For the Upscheme example above, these lines of code are the equivalent for DBAL in a migration:

$dbalManager = $conn->createSchemaManager();
$from = $manager->createSchema();
$to = $manager->createSchema();

if( $to->hasTable( 'test' ) ) {
	$table = $to->getTable( 'test' );
} else {
	$table = $to->createTable( 'test' );
}

$table->addOption( 'engine', 'InnoDB' );

$table->addColumn( 'id', 'integer', ['autoincrement' => true] );
$table->addColumn( 'domain', 'string', ['length' => 32] );

$platform = $conn->getDatabasePlatform();
if( $platform instanceof \Doctrine\DBAL\Platform\MySQLPlatform
	|| $platform instanceof \Doctrine\DBAL\Platform\MariaDBPlatform
) {
	$table->addColumn( 'code', 'string', ['length' => 64, 'customSchemaOptions' => ['charset' => 'binary']] );
} else {
	$table->addColumn( 'code', 'string', ['length' => 64]] );
}

$table->addColumn( 'label', 'string', ['length' => 255] );
$table->addColumn( 'pos', 'integer', ['default' => 0] );
$table->addColumn( 'status', 'smallint', [] );
$table->addColumn( 'mtime', 'datetime', [] );
$table->addColumn( 'ctime', 'datetime', [] );
$table->addColumn( 'editor', 'string', ['length' => 255] );

$table->setPrimaryKey( ['id'] );
$table->addUniqueIndex( ['domain', 'code'] );
$table->addIndex( ['status', 'pos'] );

foreach( $from->getMigrateToSql( $to, $conn->getDatabasePlatform() ) as $sql ) {
	$conn->executeStatement( $sql );
}

Doctrine Migration drawbacks

Doctrine Migration relies on migration classes that are named by the time they have been created to ensure a certain order. Furthermore, it stores which migrations has been executed in a table of your database. There are three major problems that arise from that:

• dependencies between 3rd party extensions
• tracking changes is out of sync
• data loss when using down()

If your application supports 3rd party extensions, these extensions are likely to add columns to existing tables and migrate data themselves. As there's no way to define dependencies between migrations, it can get almost impossible to run migrations in an application with several 3rd party extensions without conflicts. To avoid that, Upscheme offers easy to use before() and after() methods in each migration task where the tasks can define its dependencies to other tasks.

Because Doctrine Migrations uses a database table to record which migration already has been executed, these records can get easily out of sync in case of problems. Contrary, Upscheme only relies on the actual schema so it's possible to upgrade from any state, regardless of what has happend before.

Doctrine Migrations also supports the reverse operations in down() methods so you can roll back migrations which Upscheme does not. Experience has shown that it's often impossible to roll back migrations, e.g. after adding a new colum, migrating the data of an existing column and dropping the old column afterwards. If the migration of the data was lossy, you can't recreate the same state in a down() method. The same is the case if you've dropped a table. Thus, Upscheme only offers scheme upgrading but no downgrading to avoid implicit data loss.

Database support

Upscheme uses Doctrine DBAL for abstracting from different database server implementations. DBAL supports all major relationsal database management systems (RDBMS) but with a different level of support for the available features:

Good support:

• MySQL
• MariaDB
• PostgreSQL
• SQLite
• SQL server

Limited support:

• DB2
• Oracle
• SQL Anywhere

Integrating Upscheme

After you've installed the aimeos/upscheme package using composer, you can use the Up class to execute your migration tasks:

$config = [
	'driver' => 'pdo_mysql',
	'host' => '127.0.0.1',
	'dbname' => '<database>',
	'user' => '<dbuser>',
	'password' => '<secret>'
];

\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->up();

The Up::use() method requires two parameters: The database configuration and the path(s) to the migration tasks. For the config, the array keys and the values for driver must be supported by Doctrine DBAL. Available drivers are:

• pdo_mysql
• pdo_pgsql
• pdo_sqlite
• pdo_sqlsrv
• pdo_oci
• ibm_db2
• mysqli
• oci8
• sqlanywhere
• sqlsrv

Some databases require different parameters, most notable SQLite and Oracle:

SQLite:

$config = [
	'driver' => 'pdo_sqlite',
	'path' => 'path/to/file.sq3'
];

Oracle:

$config = [
	'driver' => 'pdo_oci',
	'host' => '<host or IP>',
	'dbname' => '<SID or service name (Oracle 18+)>',
	'service' => true, // for Oracle 18+ only
	'user' => '<dbuser>',
	'password' => '<secret>'
];

If you didn't use Doctrine DBAL before, your database configuration may have a different structure and/or use different values for the database type. Upscheme allows you to register a custom method that transforms your configration into valid DBAL settings, e.g.:

\Aimeos\Upscheme\Up::macro( 'connect', function( array $cfg ) {

	return \Doctrine\DBAL\DriverManager::getConnection( [
		'driver' => $cfg['adapter'],
		'host' => $cfg['host'],
		'dbname' => $cfg['database'],
		'user' => $cfg['username'],
		'password' => $cfg