Aimeos 2022 news

Since 2022.01 beta, the Aimeos core is using Upscheme for updating the database schema and migrating data between new releases. Upscheme is composer package for schema management based on Doctrine DBAL which offers an easy to use API. You can also integrate Upscheme it in your own application easily and this article explains the differences and how you can write migrations with only a few lines of code 🙂

Why Upscheme

Whenever you need to upgrade database schemas in more than one installation and your application supports more than one database (even MySQL and MariaDB differ in some details), you need a tool for schema management. And even if you only support one database vendor, it simplifies live because you don’t have to create messy custom scripts with raw SQL statements.

Aimeos used the Doctrine DBAL API directly up to 2021.10 LTS but DBAL has a very verbose API and requires a lot of code to do simple things. For example, creating/updating a table usually looks similar to these lines:

$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] );
 
if( $conn->getDatabasePlatform()->getName() === 'mysql' ) {
$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 );
}

Contrary, Upscheme offers a very terse API which does the same in only a few lines of code:

$this->db()->table( 'test', function( $t ) {
$t->engine = 'InnoDB';
 
$t->id();
$t->string( 'domain', 32 );
$t->string( 'code', 64 )->opt( 'charset', 'binary', 'mysql' );
$t->string( 'label', 255 );
$t->int( 'pos' )->default( 0 );
$t->smallint( 'status' );
$t->meta();
 
$t->unique( ['domain', 'code'] );
$t->index( ['status', 'pos'] );
} );

Upscheme also offers much better support for migrating data. Instead of using a “migration” table to keep track executed migrations which can get out of sync, Upscheme checks the current state of the schema and applies only necessary changes to update the tables, columns and other schema objects.

If your application allows 3rd party plugins you will love Upscheme’s dependency management based on tasks that must run before or after the current one. Thus, extensions can modify existing tables and migrate data without interfering with core tasks.

Create/update database schema

Creating and updating a table for example requires:

• an Upscheme task
• the table definition

The code below creates an Upscheme task named “Messages” by extending from the Upscheme base task and implements the up() method which is executed to perform the schema update:

namespace Aimeos\Upscheme\Task;
use Aimeos\Upscheme\Schema\Table;
 
class Messages extends Base
{
public function up()
{
$this->db( 'db-message' )->table( 'message', function( Table $t ) {
$t->id();
$t->type();
$t->string( 'label' );
$t->bool( 'status' );
$t->meta();
} );
}
}

Within the up() method, the database schema for the “db-message” database is retrieved (with a fall-back to the standard database if “db-message” isn’t explicitely configured) and the “message” table defined. It consists of an “id” autoincrement integer column (with primary key), a label and status column as well as “ctime”, “mtime” and “editor” columns automatically added by the meta() function offered by the Aimeos core.

The Table section in the Upscheme documentation contains the list of pre-defined methods for creating the most often used column types. Nevertheless, you can always use the Upscheme col() method to create custom columns too and there’s also a list of Upscheme column modifiers to customize any column according to your needs.

Whenever you add, change or remove a column in your task, Upscheme will update your table schema automatically without any need to write an additional line of code!

Migrate data

Upscheme tasks can also migrate data between new releases and offers simple methods to insert, update and delete records. Also, there are methods available to check for tables, columns and other schema object, to rename them and to drop them if they are not required any more:

$this->db()->delete(
'message',
['type' => 'custom']
)->dropColumn(
'message',
'type'
)->update(
'message',
['status' => false],
['status' => true, 'mtime' => date('Y-m-d H:i:s')]
)->renameColumn(
'message',
'label',
'content'
)->insert(
'message',
['label' => 'test message', 'status' => true, 'ctime' => date('Y-m-d H:i:s'), 'mtime' => date('Y-m-d H:i:s'), 'editor => 'setup']
);

This statements will delete the messages of type “custom”, drop the messages column afterwards, set the status to TRUE of all remaining records which updating the “mtime” column, rename the “label” column to “content” and insert a new test message into the table. All method calls can be concatenated because the Upscheme API offers a fluent interface and there’s also a select() method to query data:

$this->db()->select(
'message',
['status' => false, 'type' => 'old']
);

For more complex statement, Upscheme provides the stmt() method which gives you direct access to the Doctrine DBAL API:

$result = $this->db()->stmt()->select( 'id', 'content' )
->from( 'message' )
->where( 'status = 1' )
->execute();
 
while( $row = $result->fetchAssociative() ) {
$map[$row['id']] = $row['content'];
}

Summary

Compared to Doctrine DBAL, Upscheme is very easy to use and also contains some workarounds for pitfalls in DBAL. Creating and updating the database schema is extremely simple and Upscheme saved up to 80% of the code in some setup tasks compared to the previous DBAL code in the Aimeos core.