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
Database
Tables
Columns
Foreign keys
Sequences
Indexes
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['password']
	] );
} );

Upscheme also supports several database connections which you can distinguish by their key name:

$config = [
	'db' => [
		'driver' => 'pdo_mysql',
		'host' => '127.0.0.1',
		'dbname' => '<database>',
		'user' => '<dbuser>',
		'password' => '<secret>'
	],
	'temp' => [
		'driver' => 'pdo_sqlite',
		'path' => '/tmp/mydb.sqlite'
	]
];

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

Of course, you can also pass several migration paths to the Up class:

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

To enable (debugging) output, use the verbose() method:

\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose()->up(); // most important only
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose( 'vv' )->up(); // more verbose
\Aimeos\Upscheme\Up::use( $config, 'src/migrations' )->verbose( 'vvv' )->up(); // debugging

Writing migrations

A migration task only requires implementing the up() method and must be stored in one of the directories passed to the Up class:

<?php

namespace Aimeos\Upscheme\Task;
use Aimeos\Upscheme\Schema\Table;


return new class( $this ) extends Base {

	public function up()
	{
		$this->db()->table( 'test', function( Table $t ) {
			$t->id();
			$t->string( 'label' );
			$t->bool( 'status' );
		} );
	}
};

In your PHP file, always include the namespace statement first. The use statement is optional and only needed as shortcut for the type hint for the closure function argument. Your class also has to extend from the "Base" task class or implement the "Iface" task interface.

Alternatively to anonymous classes, you can use named classes for migration tasks:

<?php

namespace Aimeos\Upscheme\Task;
use Aimeos\Upscheme\Schema\Table;


class TestTable extends Base
{
	public function up()
	{
		$this->db()->table( 'test', function( Table $t ) {
			$t->id();
			$t->string( 'label' );
			$t->bool( 'status' );
		} );
	}
}

The file your class is stored in must have the same name (case sensitive) as the class itself and the .php suffix, e.g:

class TestTable -> TestTable.php

Naming migrations

There's no strict convention how to name migration task files. You can either name them by what they do (e.g. "CreateTestTable.php"), what they operate on (e.g. "TestTable.php") or even use a timestamp (e.g. "20201231_Test.php").

If the tasks doesn't contain dependencies, they are sorted and executed in alphabethical order according to the file name and the sorting would be:

20201231_Test.php
CreateTestTable.php
TestTable.php

Dependencies

To specify dependencies to other migration tasks, use the after() and before() methods. Your task is executed after the tasks returned by after() and before the tasks returned by before():

return new class( $this ) extends Base {

	public function after() : array
	{
		return ['CreateRefTable'];
	}

	public function before() : array
	{
		return ['InsertTestData'];
	}
}

The task names are the file names of the tasks without the .php suffix. If the example migration is stored in the file TestTable.php, the order of execution would be:

CreateRefTable.php -> TestTable.php -> InsertTestData.php

Messages

To output messages in your migration task use the info() method:

$this->info( 'some message' );
$this->info( 'more verbose message', 'vv' );
$this->info( 'very verbose debug message', 'vvv' );

The second parameter is the verbosity level and none or v are standard messages, vv are messages that are only displayed if more verbosity is wanted while vvv is for debugging messages. There's also a third parameter for indenting the messages:

$this->info( 'some message' );
$this->info( 'second level message', 'v', 1 );
$this->info( 'third level message', 'v', 2 );

This will display:

some message
  second level message
    third level message

Prerequisite is that the verbose() method of the Up class has been called before:

\Aimeos\Upscheme\Up::use( $config, '...' )->verbose()->up();

Schemas

In the up() method, you have access to the database schema using the db() method. In case you've passed more than one database configuration to Up::use(), you can access the different schemas by their configuration key:

// $config = ['db' => [...], 'temp' => [...]];
// \Aimeos\Upscheme\Up::use( $config, '...' )->up();

$this->db();
$this->db( 'db' );
$this->db( 'temp' );

If you pass no config key or one that doesn't exist, the first configuration is returned ("db" in this case). By using the available methods of the database schema object, you can add, update or drop tables, columns, indexes and other database objects. Also, you can use insert(), select(), update(), delete() and stmt() to manipulate the records of the tables.

After each migration task, the schema updates made in the task are automatically applied to the database. If you need to persist a change immediately because you want to insert data, call $this->db()->up() yourself. The up() method is also available in any table, sequence, and column object so you can call up() everywhere.

In cases you need two different database connections because you want to execute SELECT and INSERT/UPDATE/DELETE statements at the same time, pass TRUE as second parameter to db() to get the database schema including a new connection:

$db1 = $this->db();
$db2 = $this->db( 'db', true );

foreach( $db1->select( 'users', ['status' => false] ) as $row ) {
	$db2->insert( 'oldusers', $row );
}

$db2->delete( 'users', ['status' => false] );

All schema changes made are applied to the database before the schema with the new connection is returned. To avoid database connections to pile up until the database server rejects new connections, always calll close() for new connections created by db( '<name>', true ):

$db2->close();

Generate from database

Instead of writing migrations for your database objects by hand, you can generate the migration files automatically using:

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

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

This will generate one file for each sequence, table and view in the passed directory (./migrations/ in this example). If you have several databases and want to create migrations for all of them at once, pass the connection keys from the configuration to create():

$config = [
	'db' => [
		'driver' => 'pdo_mysql',
		// ...
	],
	'order' => [
		'driver' => 'pdo_oci',
		// ...
	]
];

\Aimeos\Upscheme\Up::use( $config, 'migrations' )->create( ['db', 'order'] );

Database

Accessing objects

You get the database schema object in your task by calling $this->db() as described in the schema section. It gives you full access to the database schema including all tables, sequences and other schema objects:

$table = $this->db()->table( 'users' );
$seq = $this->db()->sequence( 'seq_users' );

If the table or seqence doesn't exist, it will be created. Otherwise, the existing table or sequence object is returned. In both cases, you can modify the objects afterwards and add e.g. new columns to the table.

Checking existence

You can test for tables, columns, indexes, foreign keys and sequences using the database schema returned by $this->db():

$db = $this->db();

if( $db->hasTable( 'users' ) ) {
    // The "users" table exists
}

if( $db->hasColumn( 'users', 'name' ) ) {
    // The "name" column in the "users" table exists
}

if( $db->hasIndex( 'users', 'idx_name' ) ) {
    // The "idx_name" index in the "users" table exists
}

if( $db->hasForeign( 'users_address', 'fk_users_id' ) ) {
    // The foreign key "fk_users_id" in the "users_address" table exists
}

if( $db->hasSequence( 'seq_users' ) ) {
    // The "seq_users" sequence exists
}

if( $db->hasView( 'testview' ) ) {
    // The "testview" view exists
}

Renaming objects

The database object returned by $this->db() offers the possibility to rename tables, columns and indexes using the renameTable(), renameColumn() and renameIndex():

$db = $this->db();

// Renames the table "users" to "accounts"
$db->renameTable( 'users', 'account' );

// Renames the column "label" to "name" in the "users" table
$db->renameColumn( 'users', 'label', 'name' );

// Renames the column "idx_label" to "idx_name" in the "users" table
$db->renameIndex( 'users', 'idx_label', 'idx_name' );

Removing objects

The database object returned by $this->db() also has methods for dropping tables, columns, indexes, foreign keys and sequences:

$db = $this->db();

// Drops the foreign key "fk_users_id" from the "users_address" table
$db->dropForeign( 'users_address', 'fk_users_id' );

// Drops the "idx_name" index from the "users" table
$db->dropIndex( 'users', 'idx_name' );

// Drops the "name" column from the "users" table
$db->dropColumn( 'users', 'name' );

// Drops the "seq_users" sequence
$db->dropSequence( 'seq_users' );

// Drops the "users" table
$db->dropTable( 'users' );

// Drops the "testview" view
$db->dropView( 'testview' );

If the table, column, index, foreign key or sequence doesn't exist, it is silently ignored. For cases where you need to know if they exist, use the hasTable(), hasColumn(), hasIndex(), hasForeign() and hasSeqence() methods before like described in the "Checking for existence" section.

Query/modify table rows

The insert(), select(), update() and delete() methods are an easy way to add, retrieve, modify and remove rows in any table:

$this->db()->transaction( function( $db ) {

	$db2 = $this->db( 'db', true );

	foreach( $db2->select( 'users', ['status' => false] ) as $row )
	{
		$db->insert( 'newusers', ['userid' => $row['id'], 'status' => true] );
		$db->update( 'users', ['refid' => $db->lastId()], ['id' => $row['id']] );
	}

	$db->delete( 'newusers', ['status' => false] );
	$db2->close();
} );

If you use select() simultaniously with insert(), update() or delete(), you must create a second database connection because the select() statement will return rows while you send new commands to the database server. This only works on separate connections, not on the same.

To wrap all delete/insert/update operations into a transaction, you must use the transaction() method of the database object:

$this->db()->transaction( function( $db ) {
	// $db->insert( ... )
	// $db->update( ... )
	// $db->delete( ... )
} );

This ensures that all write operations are performed atomically or none of them in case of an error. The transaction() method ensures that the transaction is committed or rolled back automatically after your anonymous function returns control to the method.

If you need additional parameters within your anonymous function, you can hand them over in the use list of your function:

$userid = 123;
$this->db()->transaction( function( $db ) use ( $userid ) {
	$db->insert( 'newusers', ['userid' => userid, 'status' => true] );
} );

You can only pass simple key/value pairs for conditions to the methods which are combined by AND. If you need more complex queries, use the stmt() instead:

$db = $this->db();

$result = $db->stmt()->select( 'id', 'name' )
	->from( 'users' )
	->where( 'status != ?' )
	->setParameter( 0, false )
	->execute();

$db->stmt()->delete( 'users' )
	->where( 'status != ?' )
	->setParameter( 0, false )
	->execute();

$db->stmt()->update( 'users' )
	->set( 'status', '?' )
	->where( 'status != ?' )
	->setParameters( [true, false] )
	->execute();

The stmt() method returns a Doctrine\DBAL\Query\QueryBuilder object which enables you to build more advanced statement. Please have a look into the Doctrine Query Builder documentation for more details.

If you want to use values directly in a SQL statement (use prepared statements for security reasons whenever possible!), you have to quote the values using the q() method:

$db = $this->db();

$result = $db->stmt()->select( '*' )->from( 'products' )
	->where( 'status = ' . $db->q( $_GET['status'] ) )->execute();

Similarly, if your schema contains reserved keywords, e.g. as column names, you have to quote them as well using the qi() method:

$db = $this->db();

$result = $db->stmt()->select( $db->qi( 'key' ) )->from( 'products' )->execute();

Executing custom SQL

Doctrine only supports a common subset of SQL statements and not all possibilities the database vendors have implemented. To remove that limit, Upscheme offers the exec(), for() and query() methods to execute custom SQL statements not supported by Doctrine DBAL.

To execute custom SQL queries use the query() method which returns a result set you can iterate over:

$sql = 'SELECT id, label, status FROM product WHERE label LIKE ?';
$result = $this->db()->query( $sql, ['test%'] );

foreach( $result->iterateAssociative() as $row ) {
	// ...
}

For all other SQL statements use the exec() method wich returns the number of affected rows:

$sql = 'UPDATE product SET status=? WHERE status=?';
$num = $this->db()->exec( $sql, [1, 0] );

Using the for() method, you can also execute statements depending on the database platform:

$this->db()->for( 'mysql', 'CREATE FULLTEXT INDEX idx_text ON product (text)' );

Specifying the database platform is very useful for creating special types of indexes where the syntax differs between the database implementations.

Database methods

Database

__call()
close()
delete()
dropColumn()
dropForeign()
dropIndex()
dropSequence()
dropTable()
dropView()
exec()
for()
hasColumn()
hasForeign()
hasIndex()
hasSequence()
hasTable()
hasView()
insert()
lastId()
name()
q()
qi()
query()
renameColumn()
renameIndex()
renameTable()
reset()
select()
sequence()
stmt()
table()
toArray()
transaction()
type()
up()
update()
view()

DB::__call()

Calls custom methods or passes unknown method calls to the Doctrine schema object

public function __call( string $method, array $args )

@param string $method Name of the method
@param array<mixed> $args Method parameters
@return mixed Return value of the called method

Examples:

You can register custom methods that have access to the class properties of the Upscheme DB object:

\Aimeos\Upscheme\Schema\DB::macro( 'hasFkIndexes', function( $val ) {
	return $this->to->hasExplicitForeignKeyIndexes();
} );

$db->hasFkIndexes();
// returns true/false

Available class properties are:

$this->from : Original Doctrine database schema representing the current database

$this->to : Doctrine database schema containing the changes made up to now

$this->conn : Doctrine database connection

$this->up : Upscheme object

Furthermore, you can call any Doctrine schema method directly, e.g.:

$db->hasExplicitForeignKeyIndexes();

DB::close()

Closes the database connection

public function close() : void

Call close() only for DB schema objects created with $this->db( '...', true ). Otherwise, you will close the main connection and DBAL has to reconnect to the server which will degrade performance!

Examples:

$db = $this->db( 'temp', true );
$db->dropTable( 'test' );
$db->close();

DB::delete()

Deletes the records from the given table

public function delete( string $table, array $conditions = [] ) : self

@param string $table Name of the table
@param array<string,mixed> $conditions Key/value pairs of column names and value to compare with
@return self Same object for fluid method calls

Warning: The condition values are escaped but the table name and condition column names are not! Only use fixed strings for table name and condition column names but no external input!

Examples:

$db->delete( 'test', ['status' => false, 'type' => 'old'] );
$db->delete( 'test' );

Several conditions passed in the second parameter are combined by "AND". If you need more complex statements, use the stmt() method instead.

DB::dropColumn()

Drops the column given by its name if it exists

public function dropColumn( string $table, $name ) : self

@param string $table Name of the table the column belongs to
@param array<string>|string $name Name of the column or columns
@return self Same object for fluid method calls

Examples:

$db->dropColumn( 'test', 'oldcol' );
$db->dropColumn( 'test', ['oldcol', 'oldcol2'] );

If the column or one of the columns doesn't exist, it will be silently ignored.

DB::dropForeign()

Drops the foreign key constraint given by its name if it exists

public function dropForeign( string $table, $name ) : self

@param string $table Name of the table the foreign key constraint belongs to
@param array<string>|string $name Name of the foreign key constraint or constraints
@return self Same object for fluid method calls

Examples:

$db->dropForeign( 'test', 'fk_old' );
$db->dropForeign( 'test', ['fk_old', 'fk_old2'] );

If the foreign key constraint or one of the constraints doesn't exist, it will be silently ignored.

DB::dropIndex()

Drops the index given by its name if it exists

public function dropIndex( string $table, $name ) : self

@param string $table Name of the table the index belongs to
@param array<string>|string $name Name of the index or indexes
@return self Same object for fluid method calls

Examples:

$db->dropIndex( 'test', 'idx_old' );
$db->dropIndex( 'test', ['idx_old', 'idx_old2'] );

If the index or one of the indexes doesn't exist, it will be silently ignored.

DB::dropSequence()

Drops the sequence given by its name if it exists

public function dropSequence( $name ) : self

@param array<string>|string $name Name of the sequence or sequences
@return self Same object for fluid method calls

Examples:

$db->dropSequence( 'seq_old' );
$db->dropSequence( ['seq_old', 'seq_old2'] );

If the sequence or one of the sequences doesn't exist, it will be silently ignored.

DB::dropTable()

Drops the table given by its name if it exists

public function dropTable( $name ) : self

@param array<string>|string $name Name of the table or tables
@return self Same object for fluid method calls

Examples:

$db->dropTable( 'test' );
$db->dropTable( ['test', 'test2'] );

If the table or one of the tables doesn't exist, it will be silently ignored.

DB::dropView()

Drops the view given by its name if it exists

public function dropView( $name ) : self

@param array<string>|string $name Name of the view or views
@return self Same object for fluid method calls

Examples:

$db->dropView( 'test' );
$db->dropView( ['test', 'test2'] );

If the view or one of the views doesn't exist, it will be silently ignored.

DB::exec()

Executes a custom SQL statement

public function exec( string $sql, array $params = [], array $types = [] ) : int

@param string $sql Custom SQL statement
@param array<int|string,mixed> $params List of positional parameters or associative list of placeholders and parameters
@param array<int|string,mixed> $types List of DBAL data types for the positional or associative placeholder parameters
@return int Number of affected rows

The database changes are not applied immediately so always call up() before executing custom statements to make sure that the tables you want to use has been created before!

Examples:

$sql = 'UPDATE product SET status=? WHERE status=?';
$num = $this->db()->exec( $sql, [1, 0] );

DB::for()

Executes a custom SQL statement if the database is of the given type

public function for( $type, $sql ) : self

@param array<string>|string $type Database type the statement should be executed for
@param array<string>|string $sql Custom SQL statement or statements
@return self Same object for fluid method calls

Available database platform types are:

mysql
mariadb
postgresql
sqlite
sqlserver
oracle
db2

The database changes are not applied immediately so always call up() before executing custom statements to make sure that the tables you want to use has been created before!

Examples:

$db->for( 'mysql', 'CREATE INDEX idx_test_label ON test (label(16))' );

$db->for( ['mysql', 'sqlite'], [
	'DROP INDEX unq_test_status',
	'UPDATE test SET status = 0 WHERE status IS NULL',
] );

DB::hasColumn()

Checks if the column or columns exists

public function hasColumn( string $table, $name ) : bool

@param string $table Name of the table the column belongs to
@param array<string>|string $name Name of the column or columns
@return bool TRUE if the columns exists, FALSE if not

Examples:

$db->hasColumn( 'test', 'testcol' );
$db->hasColumn( 'test', ['testcol', 'testcol2'] );

DB::hasForeign()

Checks if the foreign key constraints exists

public function hasForeign( string $table, $name ) : bool

@param string $table Name of the table the foreign key constraint belongs to
@param array<string>|string $name Name of the foreign key constraint or constraints
@return bool TRUE if the foreign key constraint exists, FALSE if not

Examples:

$db->hasForeign( 'test', 'fk_testcol' );
$db->hasForeign( 'test', ['fk_testcol', 'fk_testcol2'] );

DB::hasIndex()

Checks if the indexes exists

public function hasIndex( string $table, $name ) : bool

@param string $table Name of the table the index belongs to
@param array<string>|string $name Name of the index or indexes
@return bool TRUE if the index exists, FALSE if not

Examples:

$db->hasIndex( 'test', 'idx_test_col' );
$db->hasIndex( 'test', ['idx_test_col', 'idx_test_col2'] );

DB::hasSequence()

Checks if the sequences exists

public function hasSequence( $name ) : bool

@param array<string>|string $name Name of the sequence or sequences
@return bool TRUE if the sequence exists, FALSE if not

Examples:

$db->hasSequence( 'seq_test' );
$db->hasSequence( ['seq_test', 'seq_test2'] );

DB::hasTable()

Checks if the tables exists

public function hasTable( $name ) : bool

@param array<string>|string $name Name of the table or tables
@return bool TRUE if the table exists, FALSE if not

Examples:

$db->hasTable( 'test' );
$db->hasTable( ['test', 'test2'] );

DB::hasView()

Checks if the views exists

public function hasView( $name ) : bool

@param array<string>|string $name Name of the view or views
@return bool TRUE if the view exists, FALSE if not

Examples:

$db->hasView( 'test' );
$db->hasView( ['test', 'test2'] );

DB::insert()

Inserts a record into the given table

	public function insert( string $table, array $data ) : self

@param string $table Name of the table
@param array<string,mixed> $data Key/value pairs of column name/value to insert
@return self Same object for fluid method calls

Examples:

$db->insert( 'test', ['label' => 'myvalue', 'status' => true] );

DB::lastId()

Returns the ID of the last inserted row into any database table

public function lastId() : string

@return string Generated ID from the database

Caution: This doesn't work for the Oracle platform because Doctrine DBAL doesn't support Oracle IDENTITY columns at the moment.

Examples:

$db->lastId();

DB::name()

Returns the name of the database

public function name() : string

@return string Database name

Examples:

$db->name();

DB::q()

Quotes a value

public function q( $value, $type = \Doctrine\DBAL\ParameterType::STRING ) : string

@param mixed $value Value to use in a non-prepared SQL query
@param mixed $type DBAL parameter type
@return string Quoted value

Examples:

$result = $db->stmt()->select( '*' )->from( 'products' )
	->where( 'status = ' . $db->q( $_GET['status'] ) )->execute();

DB::qi()

Quotes a database identifier

public function qi( string $identifier ) : string

@param string $identifier Identifier like table or column name
@return string Quoted identifier

Examples:

$result = $db->stmt()->select( $db->qi( 'key' ) )->from( 'products' )->execute();

DB::query()

Executes a custom SQL query

public function query( string $sql, array $params = [], array $types = [] ) : \Doctrine\DBAL\Result

@param string $sql Custom SQL statement
@param array<int|string,mixed> $params List of positional parameters or associative list of placeholders and parameters
@param array<int|string,mixed> $types List of DBAL data types for the positional or associative placeholder parameters
@return \Doctrine\DBAL\Result DBAL result set object

Examples:

$result = $db->query( 'SELECT id, label, status FROM product WHERE label LIKE ?', ['test%'] );

foreach( $result->iterateAssociative() as $row ) {
	// ...
}

Tip: Check the DBAL methods for retrieving data for more information.

DB::renameColumn()

Renames a column or a list of columns

public function renameColumn( string $table, $from, string $to = null ) : self

@param string $table Name of the table
@param array<string,string>|string $from Column name or array of old/new column names
@param string|null $to New column name ignored if first parameter is an array
@return self Same object for fluid method calls

If the column doesn't exist yet, the method will succeed but nothing will happen. No call to up() is required.

Limitations

SQLite since 3.25.0

Examples:

// single column
$db->renameColumn( 'testtable', 'test_col', 'test_column' );

// rename several columns at once
$db->renameColumn( 'testtable', ['tcol' => 'testcol', 'tcol2' => 'testcol2'] );

DB::renameIndex()

Renames a column or a list of columns

public function renameIndex( string $table, $from, string $to = null ) : self

@param string $table Name of the table
@param array<string,string>|string $from Index name or array of old/new index names
@param string|null $to New index name ignored if first parameter is an array
@return self Same object for fluid method calls

If the index doesn't exist yet, the method will succeed but nothing will happen. No call to up() is required.

Examples:

// single index
$db->renameIndex( 'testtable', 'idxcol', 'idx_column' );

// rename several indexes at once
$db->renameIndex( 'testtable', ['idxcol' => 'idx_column', 'idxcol2' => 'idx_column2'] );

DB::renameTable()

Renames a table or a list of tables

public function renameTable( $from, string $to = null ) : self

@param array<string,string>|string $from Table name or array of old/new table names
@param string|null $to New table name ignored if first parameter is an array
@return self Same object for fluid method calls
@throws \RuntimeException If an error occured

If the table doesn't exist yet, the method will succeed but nothing will happen. No call to up() is required.

Examples:

// single table
$db->renameTable( 'testtable', 'newtable' );

// rename several tables at once
$db->renameTable( ['testtable' => 'newtable', 'oldtable' => 'testtable2'] );

DB::reset()

Reloads the actual Doctrine schema for the current database

public function reset() : self

@return self Same object for fluid method calls

Examples:

$db->reset();

DB::select()

Returns the records from the given table

public function select( string $table, array $conditions = null ) : array

@param string $table Name of the table
@param array<string>|null $conditions Key/value pairs of column names and value to compare with
@return array<int,array<string,mixed>> List of associative arrays containing column name/value pairs

Examples:

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

Several conditions passed in the second parameter are combined by "AND". If you need more complex statements, use the stmt() method instead.

DB::sequence()

Returns the sequence object for the given name

public function sequence( string $name, \Closure $fcn = null ) : Sequence

@param string $name Name of the sequence
@param \Closure|null $fcn Anonymous function with ($sequence) parameter creating or updating the sequence definition
@return \Aimeos\Upscheme\Schema\Sequence Sequence object

If the sequence doesn't exist yet, it will be created. To persist the changes in the database, you have to call up().

Examples:

$sequence = $db->sequence( 'seq_test' );

$sequence = $db->sequence( 'seq_test', function( $seq ) {
	$seq->start( 1000 )->step( 2 )->cache( 100 );
} )->up();

DB::stmt()

Returns the query builder for a new SQL statement

public function stmt() : \Doctrine\DBAL\Query\QueryBuilder

@return \Doctrine\DBAL\Query\QueryBuilder Query builder object

Examples:

$db->stmt()->delete( 'test' )->where( 'stat = ?' )->setParameter( 0, false )->execute();
$db->stmt()->update( 'test' )->set( 'stat', '?' )->setParameter( 0, true )->execute();
$result = $db->stmt()->select( 'id', 'code' )->from( 'test' )->where( 'stat = 1' )->execute();

while( $row = $result->fetchAssociative() ) {
    $id = $row['id'];
}

For more details about the available Doctrine QueryBuilder methods, please have a look at the Doctrine documentation.

DB::table()

Returns the table object for the given name

public function table( string $name, \Closure $fcn = null ) : Table

@param string $name Name of the table
@param \Closure|null $fcn Anonymous function with ($table) parameter creating or updating the table definition
@return \Aimeos\Upscheme\Schema\Table Table object

If the table doesn't exist yet, it will be created. To persist the changes in the database, you have to call up().

Examples:

$table = $db->table( 'test' );

$table = $db->table( 'test', function( $t ) {
	$t->id();
	$t->string( 'label' );
	$t->bool( 'status' );
} )->up();

DB::transaction()

Executes the given closure within a transaction

public function transaction( \Closure $fcn ) : self

@param \Closure $fcn Anonymous function with (\Aimeos\Upscheme\Schema $db) parameter
@return self Same object for fluid method calls
@throws \Exception If an error occurred

Examples:

$this->db()->transaction( function( $db ) {
	// $db->insert( ... )
	// $db->update( ... )
	// $db->delete( ... )
} );

DB::toArray()

Returns the objects as array from the database

public function toArray() : array

@return array Associative list of sequences, tables and views

Examples:

$this->db()->toArray();

The structure of the returned array is:

[
	'sequence' => [
		'testseq' => [
			'name' => 'testseq',
			'cache' => null,
			'start' => 1000,
			'step' => 1
		]
	],
	'table' => [
		'testtable' => [
			'name' => 'testtable',
			'opt' => [
				'engine' => 'InnoDB',
				'collation' => 'utf8mb4_unicode_ci',
				'charset' => 'utf8mb4',
				'autoincrement' => 1,
				'comment' => ''
			],
			'col' => [
				'id' => [
					'name' => 'id',
					'type' => 'integer',
					'length' => null,
					'precision' => null,
					'scale' => 0,
					'null' => false,
					'seq' => 1
					'default' => null,
					'fixed' => false,
					'unsigned' => false,
					'comment' => '',
					'opt' => []
				],
				'parentid' => [
					'name' => 'parentid',
					'type' => 'bigint',
					'length' => null,
					'precision' => null,
					'scale' => 0,
					'null' => false,
					'seq' => false,
					'default' => null,
					'fixed' => false,
					'unsigned' => false,
					'comment' => '',
					'opt' => []
				],
				'label' => [
					'name' => 'label',
					'type' => 'string',
					'length' => 255,
					'precision' => null,
					'scale' => 0,
					'null' => false,
					'seq' => false,
					'default' => null,
					'fixed' => false,
					'unsigned' => false,
					'comment' => '',
					'opt' => [
						'charset' => 'utf8mb4',
						'collation' => 'utf8mb4_unicode_ci'
					]
				]
			],
			'index' => [
				'PRIMARY' => [
					'columns' => [
							0 => 'id'
						],
					'name' => 'PRIMARY',
					'flags' => [],
					'options' => [
							'lengths' => [
									0 => null
								]
						],
					'unique' => 1,
					'primary' => 1
				],
			],
			'foreign' => [
				'FK_6C73FFCA343B91AE' => [
					'localcol' => [
						0 => 'parentid'
					],
					'fktable' => 'test',
					'fkcol' => [
						0 => 'id'
					],
					'name' => 'FK_6C73FFCA343B91AE',
					'onDelete' => 'CASCADE',
					'onUpdate' => 'CASCADE'
				]
			]
		]
	],
	'view' => [
		'testview' => [
			'name' => 'testview',
			'sql' => 'select `testtable`.`id` AS `id`,`testtable`.`label` AS `label` from `testtable`'
		]
	]
]

DB::type()

Returns the type of the database

public function type() : string

@return string Database type

Possible values are:

db2
mariadb
mysql
oracle
postgresql
sqlite
sqlserver

Examples:

$type = $db->type();

DB::up()

Applies the changes to the database schema

public function up() : self

@return self Same object for fluid method calls

Examples:

$db->up();

DB::update()

Updates the records from the given table

public function update( string $table, array $data, array $conditions = [] ) : self

@param string $table Name of the table
@param array<string,mixed> $data Key/value pairs of column name/value to update
@param array<string,mixed> $conditions Key/value pairs of column names and value to compare with
@return self Same object for fluid method calls

Examples:

$db->update( 'test', ['status' => true] );
$db->update( 'test', ['status' => true], ['status' => false, 'type' => 'new'] );

Several conditions passed in the second parameter are combined by "AND". If you need more complex statements, use the stmt() method instead.

DB::view()

Creates a view with the given name if it doesn't exist yet

public function view( string $name, string $sql, $for = null ) : self

@param string $name Name of the view
@param string $sql SELECT statement for populating the view
@param array<string>|string|null $for Database type this SQL should be used for ("mysql", "mariadb", "postgresql", "sqlite", "sqlserver", "oracle", "db2")
@return self Same object for fluid method calls

If the view doesn't exist yet, it will be created. Otherwise, nothing will happen.

Examples:

$db->view( 'testview', 'SELECT * FROM testtable' );
$db->view( 'testview', 'SELECT id, label, status FROM testtable WHERE status = 1' );
$db->view( 'testview', 'SELECT * FROM `testtable` WHERE `status` = 1', 'mysql' );

Tables

Creating tables

The table scheme object you get by calling table() in your migration task gives you full access to the table and you can add, change or remove columns, indexes and foreign keys, e.g.:

$this->db()->table( 'test', function( $table ) {
	$table->id();
	$table->string( 'label' );
	$table->col( 'status', 'tinyint' )->default( 0 );
} );

Besides the col() method which can add columns of arbitrary types, there are some shortcut methods for types available in all database server implementations:

Column type	Description
bigid	BIGINT column with a sequence/autoincrement and a primary key assigned
bigint	BIGINT column with a range from −9223372036854775808 to 9223372036854775807
binary	VARBINARY column with up to 255 bytes
blob	BLOB column with up to 2GB
bool	BOOLEAN/BIT/NUMBER colum, alias for "boolean"
boolean	BOOLEAN/BIT/NUMBER colum for TRUE/FALSE resp. 0/1 values
char	CHAR column with a fixed number of characters
date	DATE column in ISO date format ("YYYY-MM-DD) without time and timezone
datetime	DATETIME column in ISO date/time format ("YYYY-MM-DD HH:mm:ss" )
tablesdatetimetz	DATETIMETZ column in ISO date/time format but with varying timezone format
decimal	DECIMAL column for numeric data with fixed-point precision (string in PHP)
float	FLOAT column for numeric data with a 8-byte floating-point precision
guid	Globally unique identifier with 36 bytes
id	INTEGER column with a sequence/autoincrement and a primary key assigned
int	INTEGER colum, alias for "integer"
integer	INTEGER colum with a range from −2147483648 to 2147483647
json	JSON column for UTF-8 encoded JSON data
smallint	INTEGER colum with a range from −32768 to 32767
string	VARCHAR column with up to 255 characters
text	TEXT/CLOB column with up to 2GB characters
time	TIME column in 24 hour "HH:MM" fromat, e.g. "05:30" or "22:15"
uuid	Globally unique identifier with 36 bytes, alias for "guid"

Setting table options

MySQL (or MariaDB, etc.) supports a few options to define aspects of the table. The engine option will specify the storage engine used for the table:

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

As a shortcut, it's also possible to set the option as property:

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

To create a temporary table, use:

$this->db()->table( 'test', function( $table ) {
	$table->temporary = true;
} );

It's also possible to set the default charset and collation for string and text columns:

$this->db()->table( 'test', function( $table ) {
	$table->charset = 'utf8mb4';
	$table->collation = 'utf8mb4_unicode_ci';
} );

Note: Collations are also supported by PostgreSQL and SQL Server but their values are different. Thus, it's not possible to use the same value for all server types. To circumvent that problem, use the column opt() method and pass the database server type as third parameter:

$this->db()->table( 'test', function( $table ) {
	$table->opt( 'charset', 'utf8mb4', 'mysql' );
	$table->opt( 'collation', 'utf8mb4_unicode_ci', 'mysql' );
} );

Now, the default charset and collation will be only set for MySQL database servers (or MariaDB and similar forks).

In case you need to know the current values of the table options:

$this->db()->table( 'test', function( $table ) {
	// return the used table engine (only MySQL, MariaDB, etc.)
	$engine = $table->engine;

	// returns TRUE if it's a temporary table
	$isTemp = $table->temporary;

	// return the current charset
	$charset = $table->charset;

	// return the current collation
	$collation = $table->collation;
} );

Checking table existence

To check if a table already exists, use the hasTable() method:

if( $this->db()->hasTable( 'users' ) ) {
    // The "users" table exists
}

You can check for several tables at once too:

if( $this->db()->hasTable( ['users', 'addresses'] ) ) {
    // The "users" and "addresses" tables exist
}

The hasTable() method will only return TRUE if all tables exist.

Changing tables

Besides creating and accessing tables, the table() method from the schema object can be used to update a table schema too. It accepts the table name and a closure that will receive the table schema object.

Let's create a table named test first including three columns:

$this->db()->table( 'test', function( $table ) {
	$table->id();
	$table->string( 'label' );
	$table->col( 'status', 'tinyint' )->default( 0 );
} );

Now, we want to update the table in another migration by adding a code column and changing the default value of the existing status column:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'code' );
	$table->col( 'status', 'tinyint' )->default( 1 );
} );

The changes will be persisted in the database as soon as the table() method returns so there's no need to call up() yourself afterwards. For the available column types and options, refer to the columns section.

Renaming tables

The database object returned by $this->db() can rename tables when using the renameTable() method:

// Renames the table "users" to "accounts"
$this->db()->renameTable( 'users', 'account' );

It's also possible to rename several tables at once if you pass an associative array which old and new names as key/value pairs:

// Renames the table "users" to "accounts" and "blog" to "posts"
$this->db()->renameTable( ['users' => 'account', 'blog' => 'posts'] );

Tables are only renamed if they exist. If a table doesn't exist any more, no error is reported:

$this->db()->renameTable( 'notexist', 'newtable' );

In that case, the method call will succeed but nothing will happen.

Dropping tables

To remove a table, you should use the dropTable() method from the database schema:

$this->db()->dropTable( 'users' );

You can also drop several tables at once by passing the list as array:

$this->db()->dropTable( ['users', 'addresses'] );

Tables are only removed if they exist. If a table doesn't exist any more, no error is reported:

$this->db()->dropTable( 'notexist' );

In that case, the method call will succeed but nothing will happen.

Table methods

Tables

__call()
__get()
__set()
bigid()
bigint()
binary()
blob()
bool()
boolean()
char()
col()
date()
datetime()
datetimetz()
decimal()
dropColumn()
dropIndex()
dropForeign()
dropPrimary()
float()
foreign()
guid()
hasColumn()
hasIndex()
hasForeign()
id()
index()
int()
integer()
json()
name()
opt()
primary()
renameColumn()
renameIndex()
smallint()
spatial()
string()
text()
time()
unique()
uuid()
up()

Table::__call()

Calls custom methods or passes unknown method calls to the Doctrine table object

public function __call( string $method, array $args )

@param string $method Name of the method
@param array<mixed> $args Method parameters
@return mixed Return value of the called method

Examples:

You can register custom methods that have access to the class properties of the Upscheme Table object:

\Aimeos\Upscheme\Schema\Table::macro( 'addConstraint', function( array $columns ) {
	return $this->to->addUniqueConstraint( $columns );
} );

$table->addConstraint( ['col1', 'col2'] );

Available class properties are:

$this->table : Doctrine table schema

$this->up : Upscheme object

Furthermore, you can call any Doctrine table method directly, e.g.:

$table->addUniqueConstraint( ['col1', 'col2'] );

Table::__get()

Returns the value for the given table option

public function __get( string $name )

@param string $name Table option name
@return mixed Table option value

The list of available table options are:

charset (MySQL)
collation (MySQL)
engine (MySQL)
temporary (MySQL)

Examples:

$engine = $table->engine;

// same as
$engine = $table->opt( 'engine' );

Table::__set()

Sets the new value for the given table option

public function __set( string $name, $value )

@param string $name Table option name
@param mixed Table option value

The list of available table options are:

charset (MySQL)
collation (MySQL)
engine (MySQL)
temporary (MySQL)

Examples:

$table->engine = 'InnoDB';

// same as
$table->opt( 'engine', 'InnoDB' );

Table::bigid()

Creates a new ID column of type "bigint" or returns the existing one

public function bigid( string $name = null ) : Column

@param string|null $name Name of the ID column
@return \Aimeos\Upscheme\Schema\Column Column object

The column gets a sequence (autoincrement) and a primary key assigned automatically. If the column doesn't exist yet, it will be created.

Examples:

$table->bigid();
$table->bigid( 'uid' );

Table::bigint()

Creates a new column of type "bigint" or returns the existing one

public function bigint( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->bigint( 'testcol' );

Table::binary()

Creates a new column of type "binary" or returns the existing one

public function binary( string $name, int $length = 255 ) : Column

@param string $name Name of the column
@param int $length Length of the column in bytes
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->binary( 'testcol' );
$table->binary( 'testcol', 32 );

Table::blob()

Creates a new column of type "blob" or returns the existing one

public function blob( string $name, int $length = 0x7fff ) : Column

@param string $name Name of the column
@param int $length Length of the column in bytes
@return \Aimeos\Upscheme\Schema\Column Column object

The maximum length of a "blob" column is 2GB. If the column doesn't exist yet, it will be created.

Examples:

$table->blob( 'testcol' );
$table->blob( 'testcol', 0x7fffffff );

Table::bool()

Creates a new column of type "boolean" or returns the existing one

public function bool( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

This method is an alias for boolean(). If the column doesn't exist yet, it will be created.

Examples:

$table->bool( 'testcol' );

Table::boolean()

Creates a new column of type "boolean" or returns the existing one

public function boolean( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->boolean( 'testcol' );

Table::char()

Creates a new column of type "char" with a fixed type or returns the existing one

public function char( string $name, int $length ) : Column

@param string $name Name of the column
@param int $length Length of the column in characters
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->char( 'testcol', 3 );

Table::col()

Creates a new column or returns the existing one

public function col( string $name, string $type = null ) : Column

@param string $name Name of the column
@param string|null $type Type of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->col( 'testcol' );
$table->col( 'testcol', 'tinyint' );

Table::date()

Creates a new column of type "date" or returns the existing one

public function date( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->date( 'testcol' );

Table::datetime()

Creates a new column of type "datetime" or returns the existing one

public function datetime( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->datetime( 'testcol' );

Table::datetimetz()

Creates a new column of type "datetimetz" or returns the existing one

public function datetimetz( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->datetimetz( 'testcol' );

Table::decimal()

Creates a new column of type "decimal" or returns the existing one

public function decimal( string $name, int $digits, int $decimals = 2 ) : Column

@param string $name Name of the column
@param int $digits Total number of decimal digits including decimals
@param int $decimals Number of digits after the decimal point
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->decimal( 'testcol', 10 ); // 10 digits incl. 2 decimals
$table->decimal( 'testcol', 10, 4 ); // 10 digits incl. 4 decimals

Table::dropColumn()

Drops the column given by its name if it exists

public function dropColumn( $name ) : self

@param array<string>|string $name Name of the column or columns
@return self Same object for fluid method calls

If the column or one of the columns doesn't exist, it will be silently ignored. The change won't be applied until the migration task finishes or up() is called.

Examples:

$table->dropColumn( 'testcol' );
$table->dropColumn( ['testcol', 'testcol2'] );

Table::dropIndex()

Drops the index given by its name if it exists

public function dropIndex( $name ) : self

@param array<string>|string $name Name of the index or indexes
@return self Same object for fluid method calls

If the index or one of the indexes doesn't exist, it will be silently ignored. The change won't be applied until the migration task finishes or up() is called.

Examples:

$table->dropIndex( 'idx_test_col' );
$table->dropIndex( ['idx_test_col', 'idx_test_col2'] );

Table::dropForeign()

Drops the foreign key constraint given by its name if it exists

public function dropForeign( $name ) : self

@param array<string>|string $name Name of the foreign key constraint or constraints
@return self Same object for fluid method calls

If the foreign key constraint or one of the constraints doesn't exist, it will be silently ignored. The change won't be applied until the migration task finishes or up() is called.

Examples:

$table->dropForeign( 'fk_test_col' );
$table->dropForeign( ['fk_test_col', 'fk_test_col2'] );

Table::dropPrimary()

Drops the primary key if it exists

public function dropPrimary() : self

@return self Same object for fluid method calls

If the primary key doesn't exist, it will be silently ignored. The change won't be applied until the migration task finishes or up() is called.

Examples:

$table->dropPrimary();

Table::float()

Creates a new column of type "float" or returns the existing one

public function float( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->float( 'testcol' );

Table::foreign()

Creates a new foreign key or returns the existing one

public function foreign( $localcolumn, string $foreigntable, $foreigncolumn = 'id', string $name = null ) : Foreign

@param array<string>|string $localcolumn Name of the local column or columns
@param string $foreigntable Name of the referenced table
@param array<string>|string $foreigncolumn Name of the referenced column or columns
@param string|null $name Name of the foreign key constraint and foreign key index or NULL for autogenerated name
@return \Aimeos\Upscheme\Schema\Foreign Foreign key constraint object

The length of the foreign key name shouldn't be longer than 30 characters for maximum compatibility.

Examples:

$table->foreign( 'parentid', 'test' );
$table->foreign( 'parentid', 'test', 'uid' );
$table->foreign( 'parentid', 'test', 'id', 'fk_test_pid' );
$table->foreign( ['parentid', 'siteid'], 'test', ['uid', 'siteid'] );

Table::guid()

Creates a new column of type "guid" or returns the existing one

public function guid( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->guid( 'testcol' );

Table::hasColumn()

Checks if the column exists

public function hasColumn( $name ) : bool

@param array<string>|string $name Name of the column or columns
@return bool TRUE if the columns exists, FALSE if not

Examples:

$table->hasColumn( 'testcol' );
$table->hasColumn( ['testcol', 'testcol2'] );

Table::hasIndex()

Checks if the index exists

public function hasIndex( $name ) : bool

@param array<string>|string $name Name of the index or indexes
@return bool TRUE if the indexes exists, FALSE if not

Examples:

$table->hasIndex( 'idx_test_col' );
$table->hasIndex( ['idx_test_col', 'idx_test_col2'] );

Table::hasForeign()

Checks if the foreign key constraint exists

public function hasForeign( $name ) : bool

@param array<string>|string $name Name of the foreign key constraint or constraints
@return bool TRUE if the foreign key constraints exists, FALSE if not

Examples:

$table->hasForeign( 'fk_test_col' );
$table->hasForeign( ['fk_test_col', 'fk_test_col2'] );

Table::id()

Creates a new ID column of type "integer" or returns the existing one

public function id( string $name = null ) : Column

@param string|null $name Name of the ID column
@return \Aimeos\Upscheme\Schema\Column Column object

The column gets a sequence (autoincrement) and a primary key assigned automatically. If the column doesn't exist yet, it will be created.

Examples:

$table->id();
$table->id( 'uid' );

Table::index()

Creates a new index or replaces an existing one

public function index( $columns, string $name = null ) : self

@param array<string>|string $columns Name of the columns or columns spawning the index
@param string|null $name Index name or NULL for autogenerated name
@return self Same object for fluid method calls

The length of the index name shouldn't be longer than 30 characters for maximum compatibility.

Examples:

$table->index( 'testcol' );
$table->index( ['testcol', 'testcol2'] );
$table->index( 'testcol', 'idx_test_testcol );

Table::int()

Creates a new column of type "integer" or returns the existing one

public function int( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

This method is an alias for integer(). If the column doesn't exist yet, it will be created.

Examples:

$table->int( 'testcol' );

Table::integer()

Creates a new column of type "integer" or returns the existing one

public function integer( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->integer( 'testcol' );

Table::json()

Creates a new column of type "json" or returns the existing one

public function json( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->json( 'testcol' );

Table::name()

Returns the name of the table

public function name() : string

@return string Table name

Examples:

$tablename = $table->name();

Table::opt()

Sets a custom schema option or returns the current value

public function opt( string $name, $value = null )

@param string $name Name of the table-related custom schema option
@param mixed $value Value of the custom schema option
@return self|mixed Same object for setting value, current value without second parameter

Available custom schema options are:

charset (MySQL)
collation (MySQL)
engine (MySQL)
temporary (MySQL)

Examples:

$charset = $table->opt( 'charset' );
$table->opt( 'charset', 'utf8' )->opt( 'collation', 'utf8_bin' );

// Magic methods:
$charset = $table->charset;
$table->charset = 'binary';

Table::primary()

Creates a new primary index or replaces an existing one

public function primary( $columns, string $name = null ) : self

@param array<string>|string $columns Name of the columns or columns spawning the index
@param string|null $name Index name or NULL for autogenerated name
@return self Same object for fluid method calls

The length of the index name shouldn't be longer than 30 characters for maximum compatibility.

Examples:

$table->primary( 'testcol' );
$table->primary( ['testcol', 'testcol2'] );
$table->primary( 'testcol', 'pk_test_testcol' );

Table::renameColumn()

Renames a column or a list of columns

public function renameColumn( $from, string $to = null ) : self

@param array<string,string>|string $from Column name or array of old/new column names
@param string|null $to New column name ignored if first parameter is an array
@return self Same object for fluid method calls
@throws \RuntimeException If an error occured

Examples:

// single column
$table->renameColumn( 'test_col', 'test_column' );

// rename several columns at once
$table->renameColumn( ['tcol' => 'testcol', 'tcol2' => 'testcol2'] );

Table::renameIndex()

Renames an index or a list of indexes

public function renameIndex( $from, string $to = null ) : self

@param array|string $from Index name or array of old/new index names (if new index name is NULL, it will be generated)
@param string|null $to New index name or NULL for autogenerated name (ignored if first parameter is an array)
@return self Same object for fluid method calls

The length of the indexes name shouldn't be longer than 30 characters for maximum compatibility.

Examples:

// generate a new name automatically
$table->renameIndex( 'test_col_index' );

// custom name
$table->renameIndex( 'test_col_index', 'idx_test_col' );

// rename several indexes at once
$table->renameIndex( ['test_col_index' => null, 'test_index' => 'idx_test_col'] );

Table::smallint()

Creates a new column of type "smallint" or returns the existing one

public function smallint( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created.

Examples:

$table->smallint( 'testcol' );

Table::spatial()

Creates a new spatial index or replaces an existing one

public function spatial( $columns, string $name = null ) : self

@param array|string $columns Name of the columns or columns spawning the index
@param string|null $name Index name or NULL for autogenerated name
@return self Same object for fluid method calls

The length of the index name shouldn't be longer than 30 characters for maximum compatibility.

Examples:

$table->spatial( 'testcol' );
$table->spatial( ['testcol', 'testcol2'] );
$table->spatial( 'testcol', 'idx_test_testcol' );

Table::string()

Creates a new column of type "string" or returns the existing one

public function string( string $name, int $length = 255 ) : Column

@param string $name Name of the column
@param int $length Length of the column in characters
@return \Aimeos\Upscheme\Schema\Column Column object

This type should be used for up to 255 characters. For more characters, use the "text" type. If the column doesn't exist yet, it will be created.

Examples:

$table->string( 'testcol' );
$table->string( 'testcol', 32 );

Table::text()

Creates a new column of type "text" or returns the existing one

public function text( string $name, int $length = 0xffff ) : Column

@param string $name Name of the column
@param int $length Length of the column in characters
@return \Aimeos\Upscheme\Schema\Column Column object

The maximum length of a "text" column is 2GB. If the column doesn't exist yet, it will be created.

Examples:

$table->text( 'testcol' );
$table->text( 'testcol', 0x7fffffff );

Table::time()

Creates a new column of type "time" or returns the existing one

public function time( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

If the column doesn't exist yet, it will be created. This datatype is not available when using Oracle databases.

Examples:

$table->time( 'testcol' );

Table::unique()

Creates a new unique index or replaces an existing one

public function unique( $columns, string $name = null ) : self

@param array|string $columns Name of the columns or columns spawning the index
@param string|null $name Index name or NULL for autogenerated name
@return self Same object for fluid method calls

The length of the index name shouldn't be longer than 30 characters for maximum compatibility.

Examples:

$table->unique( 'testcol' );
$table->unique( ['testcol', 'testcol2'] );
$table->unique( 'testcol', 'unq_test_testcol' );

Table::uuid()

Creates a new column of type "guid" or returns the existing one

public function uuid( string $name ) : Column

@param string $name Name of the column
@return \Aimeos\Upscheme\Schema\Column Column object

This method is an alias for guid(). If the column doesn't exist yet, it will be created.

Examples:

$table->uuid( 'testcol' );

Table::up()

Applies the changes to the database schema

public function up() : self

@return self Same object for fluid method calls

Examples:

$table->up();

Columns

Adding columns

The column schema object you get by calling col() in your migration task gives you access to all column properties. There are also shortcuts available for column types supported by all databases. Each column can be changed by one or more modifier methods and you can also add indexes to single columns, e.g.:

$this->db()->table( 'test', function( $table ) {
	$table->id()->unsigned( true );
	$table->string( 'label' )->index();
	$table->col( 'status', 'tinyint' )->default( 0 );
} );

The example will add the following columns:

id of type integer with unsigend modifier
label of type string with 255 chars and an index
status of type tinyint (MySQL only) with a default value of zero

Available column types

There are some shortcut methods for column types available in all database server implementations:

Column type	Description
bigid	BIGINT column with a sequence/autoincrement and a primary key assigned
bigint	BIGINT column with a range from −9223372036854775808 to 9223372036854775807
binary	VARBINARY column with up to 255 bytes
blob	BLOB column with up to 2GB
bool	BOOLEAN/BIT/NUMBER colum, alias for "boolean"
boolean	BOOLEAN/BIT/NUMBER colum for TRUE/FALSE resp. 0/1 values
char	CHAR column with a fixed number of characters
date	DATE column in ISO date format ("YYYY-MM-DD) without time and timezone
datetime	DATETIME column in ISO date/time format ("YYYY-MM-DD HH:mm:ss" )
tablesdatetimetz	DATETIMETZ column in ISO date/time format but with varying timezone format
decimal	DECIMAL column for numeric data with fixed-point precision (string in PHP)
float	FLOAT column for numeric data with a 8-byte floating-point precision
guid	Globally unique identifier with 36 bytes
id	INTEGER column with a sequence/autoincrement and a primary key assigned
int	INTEGER colum, alias for "integer"
integer	INTEGER colum with a range from −2147483648 to 2147483647
json	JSON column for UTF-8 encoded JSON data
smallint	INTEGER colum with a range from −32768 to 32767
string	VARCHAR column with up to 255 characters
text	TEXT/CLOB column with up to 2GB characters
time	TIME column in 24 hour "HH:MM" fromat, e.g. "05:30" or "22:15"
uuid	Globally unique identifier with 36 bytes, alias for "guid"

To add database specific column types, use the col() method, e.g.:

$this->db()->table( 'test', function( $table ) {
	$table->col( 'status', 'tinyint' );
} );

Column modifiers

It's also possible to change column definitions by calling one or more column modifier methods:

$this->db()->table( 'test', function( $table ) {
	$table->int( 'number' )->null( true )->unsigned( true );
} );

The available column modifier methods are:

Column modifier	Description
autoincrement(true)	Set INTEGER columns as auto-incrementing (alias for `seq()`)
charset('utf8')	The character set used by the column (MySQL)
collation('binary')	The column collation (MySQL/PostgreSQL/Sqlite/SQLServer but not compatible)
comment('comment')	Add a comment to a column (MySQL/PostgreSQL/Oracle/SQLServer)
default(1)	Default value of the column if no value was specified (default: `NULL`)
fixed(true)	If string or binary columns should have a fixed length
index('idx_col')	Add an index to the column, index name is optional
length(32)	The max. length of string and binary columns
null(true)	Allow NULL values to be inserted into the column
precision(12)	The max. number of digits stored in DECIMAL and FLOAT columns incl. decimal digits
primary('pk_col')	Add a primary key to the column, primary key name is optional
scale(2)	The exact number of decimal digits used in DECIMAL and FLOAT columns
seq(true)	Set INTEGER columns as auto-incrementing if no value was specified
spatial('idx_col')	Add a spatial (geo) index to the column, index name is optional
unique('unq_col')	Add an unique index to the column, index name is optional
unsigned(true)	Allow unsigned INTEGER values only (MySQL)

To set custom schema options for columns, use the opt() method, e.g.:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'code' )->opt( 'collation', 'utf8mb4' );
} );

It's even possible to set column modifiers for a specific database implementation by passing the database type as third parameter:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'code' )->opt( 'collation', 'utf8mb4', 'mysql' );
} );

Checking column existence

To check if a column already exists, use the hasColumn() method:

if( $this->db()->hasColumn( 'users', 'name' ) ) {
    // The "name" column in the "users" table exists
}

You can check for several columns at once too. In that case, the hasColumn() method will only return TRUE if all columns exist:

if( $this->db()->hasColumn( 'users', ['name', 'status'] ) ) {
    // The "name" and "status" columns in the "users" table exists
}

If you already have a table object, you can use hasColumn() as well:

if( $table->hasColumn( 'name' ) ) {
    // The "name" column in the table exists
}

if( $table->hasColumn( ['name', 'status'] ) ) {
    // The "name" and "status" columns in the table exists
}

Besides columns, you can also check if column modifiers are set and which value they have:

if( $table->string( 'code' )->null() ) {
	// The "code" columns is nullable
}

Retrieving the current column modifier values is possible using these methods:

Column modifier	Description
autoincrement()	TRUE if the the column is auto-incrementing (alias for `seq()`)
charset()	Used character set (MySQL)
collation()	Used collation (MySQL/PostgreSQL/Sqlite/SQLServer but not compatible)
comment()	Comment associated to the column (MySQL/PostgreSQL/Oracle/SQLServer)
default()	Default value of the column
fixed()	TRUE if the string or binary column has a fixed length
length()	The maximum length of the string or binary column
null()	TRUE if NULL values are allowed
precision()	The maximum number of digits stored in DECIMAL and FLOAT columns incl. decimal digits
scale()	The exact number of decimal digits used in DECIMAL and FLOAT columns
seq()	TRUE if the column is auto-incrementing
unsigned()	TRUE if only unsigned INTEGER values are allowed (MySQL)

To check for non-standard column modifiers, use the opt() method without second parameter. Then, it will return the current value of the column modifier:

if( $table->string( 'code' )->opt( 'charset' ) === 'utf8' ) {
	// The "code" columns uses UTF-8 charset (MySQL only)
}

Changing columns

It's possible to change most column modifiers like the length of a string column:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'code' )->length( 64 );
} );

Some methods also offer additional parameters to set most often used modifiers directly:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'code', 64 );
} );

If you need to change the column modifiers immediately because you want to migrate the rows afterwards, use the up() method to persist the changes:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'code', 64 )->null( true )->up();
	// modify rows from "test" table
} );

Changing the column type is possible by using the new method for the appropriate type or the col() method:

$this->db()->table( 'test', function( $table ) {
	$table->text( 'code' );
	// or
	$table->col( 'code', 'text' );
} );

Be aware that not all column types can be changed into another type or at least not without data loss. You can change an INTEGER column to a BIGINT column without problem but the other way round will fail. The same happens if you want to change a VARCHAR column (string) into an INTEGER column.

Renaming columns

To rename columns, use the renameColumn() method of the DB schema:

// single column
$this->db()->renameColumn( 'testtable', 'label', 'name' );

// multiple columns
$this->db()->renameColumn( 'testtable', ['label' => 'name', 'stat' => 'status'] );

If a table object is already available, you can use its renameColumn() method to rename one or more columns:

$this->db()->table( 'testtable', function( $table ) {
	// single column
	$table->renameColumn( 'label', 'name' );

	// multiple columns
	$table->renameColumn( ['label' => 'name', 'stat' => 'status'] );
} );

In all cases, columns are only removed if they exist. No error is reported if one or more columns doesn't exist in the table.

Dropping columns

To drop columns, use the dropColumn() method from the DB schema object:

$this->db()->dropColumn( 'users', 'name' );

You can drop several columns at once if you pass the name of all columns you want to drop as array:

$this->db()->dropColumn( 'users', ['name', 'status'] );

If you already have a table object, you can use dropColumn() too:

// single column
$table->dropColumn( 'name' );

// multiple columns
$table->dropColumn( ['name', 'status'] );

In all cases, columns are only removed if they exist. No error is reported if one or more columns doesn't exist in the table.

Column methods

Columns

__call()
__get()
__set()
autoincrement()
charset()
collation()
comment()
default()
fixed()
index()
length()
name()
null()
opt()
precision()
primary()
scale()
seq()
spatial()
type()
unique()
unsigned()
up()

Column::__call()

Calls custom methods or passes unknown method calls to the Doctrine column object

public function __call( string $method, array $args )

@param string $method Name of the method
@param array<mixed> $args Method parameters
@return mixed Return value of the called method

Examples:

You can register custom methods that have access to the class properties of the Upscheme Column object:

\Aimeos\Upscheme\Schema\Column::macro( 'platform', function( array $options ) {
	return $this->to->setPlatformOptions( $options );
} );

$column->platform( ['option' => 'value'] );

Available class properties are:

$this->db : Upscheme DB object

$this->table : Doctrine table schema

$this->column : Doctrine column schema

Furthermore, you can call any Doctrine column method directly, e.g.:

$column->setPlatformOptions( ['option' => 'value'] );

Column::__get()

Returns the value for the given column option

public function __get( string $name )

@param string $name Column option name
@return mixed Column option value

The list of available column options are:

charset (MySQL)
collation (MySQL, PostgreSQL, Sqlite and SQL Server)
check
unique (All)

Examples:

$charset = $column->charset;

// same as
$charset = $column->opt( 'charset' );

Column::__set()

Sets the new value for the given column option

public function __set( string $name, $value )

@param string $name Column option name
@param mixed $value Column option value

The list of available column options are:

charset (MySQL)
collation (MySQL, PostgreSQL, Sqlite and SQL Server)
check
unique (All)

Examples:

$column->charset = 'utf8';

// same as
$column->opt( 'charset', 'utf8' );

Column::autoincrement()

Sets the column as autoincrement or returns the current value

public function autoincrement( bool $value = null )

@param bool|null $value New autoincrement flag or NULL to return current value
@return self|bool Same object for setting the value, current value without parameter

This method is an alias for the seq() method.

Examples:

$value = $column->autoincrement();
$column->autoincrement( true );

Column::charset()

Sets the column charset or returns the current value

public function charset( string $value = null )

@param string|null $value New column charset or NULL to return current value
@return self|string Same object for setting the value, current value without parameter

Examples:

$comment = $column->charset();
$column->charset( 'utf8' );

Column::collation()

Sets the column collation or returns the current value

public function collation( string $value = null )

@param string|null $value New column collation or NULL to return current value
@return self|string Same object for setting the value, current value without parameter

Examples:

$comment = $column->collation();
$column->collation( 'binary' );

Column::comment()

Sets the column comment or returns the current value

public function comment( string $value = null )

@param string|null $value New column comment or NULL to return current value
@return self|string Same object for setting the value, current value without parameter

Examples:

$comment = $column->comment();
$column->comment( 'column comment' );

Column::default()

Sets the column default value or returns the current value

public function default( $value = null )

@param mixed $value New column default value or NULL to return current value
@return self|mixed Same object for setting the value, current value without parameter

Examples:

$value = $column->default();
$column->default( 0 );

Column::fixed()

Sets the column fixed flag or returns the current value

public function fixed( bool $value = null )

@param bool|null $value New column fixed flag or NULL to return current value
@return self|bool Same object for setting the value, current value without parameter

Examples:

$value = $column->fixed();
$column->fixed( true );

Column::index()

Creates a regular index for the column

public function index( string $name = null ) : self

@param string|null $name Name of the index or NULL to generate automatically
@return self Same object for fluid method calls

Examples:

$column->index();
$column->index( 'idx_col' );

Column::length()

Sets the column length or returns the current value

public function length( int $value = null )

@param int|null $value New column length or NULL to return current value
@return self|int Same object for setting the value, current value without parameter

Examples:

$value = $column->length();
$column->length( 32 );

Column::name()

Returns the name of the column

public function name() : string

@return string Column name

Examples:

$name = $column->name();

Column::null()

Sets the column null flag or returns the current value

public function null( bool $value = null )

@param bool|null $value New column null flag or NULL to return current value
@return self|bool Same object for setting the value, current value without parameter

Examples:

$value = $column->null();
$column->null( true );

Column::opt()

Sets the column option value or returns the current value

public function opt( string $option, $value = null, $for = null )

@param string $option Column option name
@param mixed $value New column option value or NULL to return current value
@param array<string>|string|null $for Database type this option should be used for ("mysql", "mariadb", "postgresql", "sqlite", "sqlserver", "oracle", "db2")
@return self|mixed Same object for setting the value, current value without parameter

Examples:

$value = $column->opt( 'length' );
$column->opt( 'length', 64 );

Column::precision()

Sets the column precision or returns the current value

public function precision( int $value = null )

@param int|null $value New column precision value or NULL to return current value
@return self|int Same object for setting the value, current value without parameter

Examples:

$value = $column->precision();
$column->precision( 10 );

Column::primary()

Creates a primary index for the column

public function primary( string $name = null ) : self

@param string|null $name Name of the index or NULL to generate automatically
@return self Same object for fluid method calls

Examples:

$column->primary();
$column->primary( 'pk_col' );

Column::scale()

Sets the column scale or returns the current value

public function scale( int $value = null )

@param int|null $value New column scale value or NULL to return current value
@return self|int Same object for setting the value, current value without parameter

Examples:

$value = $column->scale();
$column->scale( 3 );

Column::seq()

Sets the column as autoincrement or returns the current value

public function seq( bool $value = null )

@param bool|null $value New autoincrement flag or NULL to return current value
@return self|bool Same object for setting the value, current value without parameter

Examples:

$value = $column->seq();
$column->seq( true );

Column::spatial()

Creates a spatial index for the column

public function spatial( string $name = null ) : self

@param string|null $name Name of the index or NULL to generate automatically
@return self Same object for fluid method calls

Examples:

$column->spatial();
$column->spatial( 'idx_col' );

Column::type()

Sets the column type or returns the current value

public function type( string $value = null )

@param string|null $value New column type or NULL to return current value
@return self|string Same object for setting the value, current value without parameter

Examples:

$value = $column->type();
$column->type( 'tinyint' );

Column::unique()

Creates an unique index for the column

public function unique( string $name = null ) : self

@param string|null $name Name of the index or NULL to generate automatically
@return self Same object for fluid method calls

Examples:

$column->unique();
$column->unique( 'unq_col' );

Column::unsigned()

Sets the column unsigned flag or returns the current value

public function unsigned( bool $value = null )

@param bool|null $value New column unsigned flag or NULL to return current value
@return self|bool Same object for setting the value, current value without parameter

Examples:

$value = $column->unsigned();
$column->unsigned( true );

Column::up()

Applies the changes to the database schema

public function up() : self

@return self Same object for fluid method calls

Examples:

$column->up();

Foreign keys

Creating foreign keys

Upscheme offers support for foreign key constraints, which enforce the integrity of data between two tables. For example, if the parentid column of the users_address table references the id column of the users table, there can be no rows in the users_address table without a matching row in the users table. Calling the foreign() method will create such a constraint:

$this->db()->table( 'users', function( $table ) {
	$table->id();
} );

$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( 'parentid', 'users' );
} );

Note: The column (parentid) will and must have the same data type and column modifiers as the referenced column (id). The foreign() method ensures that and will create a new index with the same name as the foreign key constraint automatically.

If the ID column in the users table is named differently, pass its name as third parameter to the foreign() method:

$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( 'parentid', 'users', 'uid' );
} );

It's recommended to pass the name of the foreign key constraint as forth parameter so it's easier to change or drop constraints later:

$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( 'parentid', 'users', 'id', 'fk_test_pid' );
} );

In case there's more than one column required to get the unique values required by foreign keys, pass the column names as array:

$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( ['parentid', 'siteid'], 'users_address', ['id', 'siteid'] );
} );

Foreign key constraints can perform different actions if the referenced column in the foreign table is deleted of updated. The standard action is to restrict deleting the row or updating the referenced ID value. To change the behaviour, use the onDelete() and onUpdate() methods:

$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( 'parentid', 'users' )->onDelete( 'SET NULL' )->onUpdate( 'RESTRICT' );
} );

There's a shortcut if you want to set both values to the same value:

$this->db()->table( 'users_address', function( $table ) {
	$table->foreign( 'parentid', 'users' )->do( 'SET NULL' );
} );

Possible values for both methods are:

CASCADE : Update referenced value
NO ACTION : No change in referenced value (same as RESTRICT)
RESTRICT : Forbid changing values
SET DEFAULT : Set referenced value to the default value
SET NULL : Set referenced value to NULL

The default action when deleting or updating rows is CASCADE so the values of the foreign key column are updated to the same values as in the foreign table.

Checking foreign key existence

To check if a foreign key already exists, use the hasForeign() method:

if( $this->db()->hasForeign( 'users_address', 'fk_usrad_parentid' ) ) {
    // The "fk_usrad_parentid" foreign key in the "users_address" table exists
}

It's also possible checking for several foreign key constraints at once. Then, the hasForeign() method will only return TRUE if all constraints exist in the tables passed as first argument:

if( $this->db()->hasForeign( 'users_address', ['fk_usrad_parentid', 'fk_usrad_siteid'] ) ) {
    // The "fk_usrad_parentid" and "fk_usrad_siteid" foreign keys exist in the "users_address" table
}

If a table object available, the hasForeign() method of the table can be used instead:

$this->db()->table( 'users_address', function( $table ) {
	$table->hasForeign( 'fk_usrad_parentid' ) ) {
	    // The "fk_usrad_parentid" foreign key in the "users_address" table exists
	}
} );

$this->db()->table( 'users_address', function( $table ) {
	$table->hasForeign( ['fk_usrad_parentid', 'fk_usrad_siteid'] ) ) {
	    // The "fk_usrad_parentid" and "fk_usrad_siteid" foreign keys exist in the "users_address" table
	}
} );

In case you need the current values of an existing constraint:

$this->db()->table( 'users_address', function( $table ) {
	$fk = $table->foreign( 'parentid', 'users' );

	// returns the name of the constraint
	$name = $fk->name()

	// returns the action when deleting rows
	$action = $fk->onDelete;

	// returns the action when updating the foreign ID
	$action = $fk->onUpdate;
} );

Dropping foreign keys

To remove a foreign key constraint from a table, use the dropForeign() method and pass the name of the table and foreign key name as arguments:

$this->db()->dropForeign( 'users_address', 'fk_usrad_parentid' );

You can also pass several foreign key names to drop them at once:

$this->db()->dropForeign( 'users_address', ['fk_usrad_parentid', 'fk_usrad_siteid'] );

Within the anonymous function passed to the table() method, you can also use the dropForeign() method:

$this->db()->table( 'users_address', function( $table ) {
	$table->dropForeign( 'fk_usrad_parentid' );
} );

$this->db()->table( 'users_address', function( $table ) {
	$table->dropForeign( ['fk_usrad_parentid', 'fk_usrad_siteid'] );
} );

Foreign key methods

Foreign keys

__call()
__get()
__set()
name()
onDelete()
onUpdate()
up()

Foreign::__call()

Calls custom methods

public function __call( string $method, array $args )

@param string $method Name of the method
@param array<mixed> $args Method parameters
@return mixed Return value of the called method

Examples:

You can register custom methods that have access to the class properties of the Upscheme Foreign object:

\Aimeos\Upscheme\Schema\Foreign::macro( 'default', function() {
	$this->opts = ['onDelete' => 'SET NULL', 'onUpdate' => 'SET NULL'];
} );

$foreign->default();

Available class properties are:

$this->dbaltable : Doctrine table schema

$this->table : Upscheme Table object

$this->localcol : Local column name or names

$this->fktable : Foreign table name

$this->fkcol : Foreign column name or names

$this->name : Foreign key name

$this->opts : Associative list of foreign key options (mainly "onDelete" and "onUpdate")

Foreign::__get()

Returns the value for the given foreign key option

public function __get( string $name )

@param string $name Foreign key option name
@return mixed Foreign key option value

The list of available foreign key options are:

onDelete
onUpdate

Possible values for both options are:

CASCADE : Update referenced value
NO ACTION : No change in referenced value (same as RESTRICT)
RESTRICT : Forbid changing values
SET DEFAULT : Set referenced value to the default value
SET NULL : Set referenced value to NULL

Examples:

$value = $foreign->onDelete;
// same as
$value = $foreign->opt( 'onDelete' );

Foreign::__set()

Sets the new value for the given Foreign key option

public function __set( string $name, $value )

@param string $name Foreign key option name
@param mixed Foreign key option value

The list of available Foreign key options are:

onDelete
onUpdate

Possible values for both options are:

CASCADE : Update referenced value
NO ACTION : No change in referenced value (same as RESTRICT)
RESTRICT : Forbid changing values
SET DEFAULT : Set referenced value to the default value
SET NULL : Set referenced value to NULL

Examples:

$foreign->onDelete = 'SET NULL';
// same as
$foreign->onDelete( 'SET NULL' );
$foreign->opt( 'onDelete', 'SET NULL' );

Foreign::do()

Sets the new value for the given Foreign key option

public function do( string $action ) : self

@param string $action Performed action
@return self Same object for fluid method calls

Possible actions are:

CASCADE : Delete or update referenced value
NO ACTION : No change in referenced value (same as RESTRICT)
RESTRICT : Forbid changing values
SET DEFAULT : Set referenced value to the default value
SET NULL : Set referenced value to NULL

Examples:

$foreign->do( 'RESTRICT' );

Foreign::name()

Returns the current name of the foreign key constraint

public function name()

@return string|null Name of the constraint or NULL if no name is available

Examples:

$fkname = $foreign->name();

Foreign::onDelete()

Sets the action if the referenced row is deleted or returns the current value

public function onDelete( string $value = null )

@param string|null $value Performed action or NULL to return current value
@return self|string Same object for setting the value, current value without parameter
Available actions are:
- CASCADE : Delete referenced value
- NO ACTION : No change in referenced value
- RESTRICT : Forbid changing values
- SET DEFAULT : Set referenced value to the default value
- SET NULL : Set referenced value to NULL

Examples:

$value = $foreign->onDelete();

$foreign->onDelete( 'SET NULL' );
// same as
$foreign->onDelete = 'SET NULL';
// same as
$foreign->opt( 'onDelete', 'SET NULL' );

$foreign->onDelete( 'SET NULL' )->onUpdate( 'SET NULL' );

Foreign::onUpdate()

Sets the action if the referenced row is updated or returns the current value

public function onUpdate( string $value = null )

@param string|null $value Performed action or NULL to return current value
@return self|string Same object for setting the value, current value without parameter
Available actions are:
- CASCADE : Update referenced value
- NO ACTION : No change in referenced value
- RESTRICT : Forbid changing values
- SET DEFAULT : Set referenced value to the default value
- SET NULL : Set referenced value to NULL

Examples:

$value = $foreign->onUpdate();

$foreign->onUpdate( 'SET NULL' );
// same as
$foreign->onUpdate = 'SET NULL';
// same as
$foreign->opt( 'onUpdate', 'SET NULL' );

$foreign->onUpdate( 'SET NULL' )->onDelete( 'SET NULL' );

Foreign::up()

Applies the changes to the database schema

public function up() : self

@return self Same object for fluid method calls

Examples:

$foreign->up();

Sequences

Adding sequences

A few database implementations offer sequences instead of auto-increment/identity columns, namely Oracle and PostgreSQL. Sequences are functions which create sequentially increasing numbers that are applied to a table column when inserting new rows. To create a new sequence named seq_test use the sequence() method:

$this->db()->sequence( 'seq_test' );

To use a different start value and step width than 1, call the start() and step() methods:

$this->db()->sequence( 'seq_test', function( $seq ) {
	$seq->start( 1000 )->step( 2 );
} );

Checking sequence existence

To check if a sequence already exists, use the hasSequence() method:

if( $this->db()->hasSequence( 'seq_test' ) ) {
    // The "seq_test" sequence exists
}

It's also possible checking for several sequences at once. Then, the hasSequence() method will only return TRUE if all sequences exist:

if( $this->db()->hasSequence( ['seq_id', 'seq_test'] ) ) {
    // The "seq_id" and "seq_test" sequences exist
}

In case you need to know the current values of the table options:

$this->db()->sequence( 'seq_test', function( $seq ) {
	// returns how many generated numbers are cached
	$cache = $seq->cache;

	// returns the number the sequence has started from
	$start = $seq->start;

	// returns the step width for newly generated numbers
	$step = $seq->step;
} );

Dropping sequences

To remove a sequence, use the dropSequence() method and pass the name of the sequence as argument:

$this->db()->dropSequence( 'seq_id' );

You can also pass several sequence names to drop them at once:

$this->db()->dropSequence( ['seq_id', 'seq_test'] );

Sequence methods

Sequences

__call()
__get()
__set()
cache()
name()
start()
step()
up()

Sequence::__call()

Calls custom methods or passes unknown method calls to the Doctrine table object

public function __call( string $method, array $args )

@param string $method Name of the method
@param array<mixed> $args Method parameters
@return mixed Return value of the called method

Examples:

You can register custom methods that have access to the class properties of the Upscheme Sequence object:

\Aimeos\Upscheme\Schema\Sequence::macro( 'default', function() {
	$this->start( 1 )->step( 2 );
} );

$sequence->default();

Available class properties are:

$this->db : Upscheme DB object

$this->sequence : Doctrine sequence schema

Sequence::__get()

Returns the value for the given sequence option

public function __get( string $name )

@param string $name Sequence option name
@return mixed Sequence option value

Examples:

$value = $sequence->getInitialValue();
// same as
$value = $sequence->start();

Sequence::__set()

Sets the new value for the given sequence option

public function __set( string $name, $value )

@param string $name Sequence option name
@param mixed Sequence option value

Examples:

$value = $sequence->setInitialValue( 1000 );
// same as
$value = $sequence->start( 1000 );

Sequence::cache()

Sets the cached size of the sequence or returns the current value

public function cache( int $value = null )

@param int $value New number of sequence IDs cached by the client or NULL to return current value
@return self|int Same object for setting value, current value without parameter

Examples:

$value = $sequence->cache();
$sequence->cache( 100 );

Sequence::name()

Returns the name of the sequence

public function name()

@return string Sequence name

$name = $sequence->name();

Sequence::start()

Sets the new start value of the sequence or returns the current value

public function start( int $value = null )

@param int $value New start value of the sequence or NULL to return current value
@return self|int Same object for setting value, current value without parameter

$value = $sequence->start();
$sequence->start( 1000 );

Sequence::step()

Sets the step size of new sequence values or returns the current value

public function step( int $value = null )

@param int $value New step size the sequence is incremented or decremented by or NULL to return current value
@return self|int Same object for setting value, current value without parameter

$value = $sequence->step();
$sequence->step( 2 );

Sequence::up()

Applies the changes to the database schema

public function up() : self

@return self Same object for fluid method calls

$sequence->up();

Indexes

Indexes speed up database queries and the time a query needs can drop from several minutes to milliseconds if used correctly. There are several index types available:

primary : All values must be unique, no NULL values and only one index per table is allowed
unique : Values must be unique but NULL values are allowed (and more than once)
index : Standard index with no restrictions
spatial : Fast lookup in coordinates systems like geographic maps

All indexes can consist of one or more columns but the order of the columns has a great impact if indexes are used for a query or not.

Adding indexes

All indexes are bound to the table which contains the columns the index covers. The simplest way to create an index over a single column is to use the index() method of the column object:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'label' )->index();
} );

The second parameter of the index() method allows you to set a custom name for the index:

$this->db()->table( 'test', function( $table ) {
	$table->string( 'label' )->index( 'idx_test_label' );
} );

Note: For a maximum compatibility between different database types, the length of the index names should be 30 characters or less.

The same is possible for primary, unique and spatial indexes:

$this->db()->table( 'test', function( $table ) {
	// primary key
	$table->int( 'id' )->primary();
	$table->int( 'id' )->primary( 'pk_test_id' ); // ignored by MySQL, MariaDB, etc.

	// unique key
	$table->string( 'code' )->unique();
	$table->string( 'code' )->unique( 'unq_test_code' );

	// spatial index
	$table->col( 'location', 'point' )->spatial();
	$table->col( 'location', 'point' )->spatial( 'idx_test_location' );
} );

For multi-column indexes, the primary(), unique() and index() methods are available in the table object:

$this->db()->table( 'test', function( $table ) {
	// primary composite index
	$table->primary( ['siteid', 'code'] );

	// unique composite index
	$table->unique( ['parentid', 'type'] );

	// regular composite index
	$table->index( ['label', 'status'] );
} );

Spatial indexes can NOT span multiple columns but creating them is also possible using the spatial() method of the table object:

$this->db()->table( 'test', function( $table ) {
	$table->spatial( 'location' );
} );

Checking index existence

To check if an index already exists, use the hasIndex() method:

if( $this->db()->hasIndex( 'users', 'idx_users_name' ) ) {
    // The "idx_users_name" index in the "users" table exists
}

You can check for several indexes at once too. In that case, the hasIndex() method will only return TRUE if all indexes exist:

if( $this->db()->hasIndex( 'users', ['idx_users_name', 'idx_users_status'] ) ) {
    // The "idx_users_name" and "idx_users_status" indexes in the "users" table exists
}

If you already have a table object, you can use hasIndex() as well:

if( $table->hasIndex( 'idx_users_name' ) ) {
    // The "idx_users_name" index in the table exists
}

if( $table->hasIndex( ['idx_users_name', 'idx_users_status'] ) ) {
    // The "idx_users_name" and "idx_users_status" indexes in the table exists
}

Renaming indexes

To rename indexes directly, using the renameIndex() method of the DB schema:

// single index
$this->db()->renameIndex( 'testtable', 'idx_test_label', 'idx_test_name' );

// multiple indexes
$this->db()->renameIndex( 'testtable', ['idx_test_label' => 'idx_test_name', 'idx_text_stat' => 'idx_test_status'] );

If a table object is already available, you can use its renameIndex() method to rename one or more indexes:

$this->db()->table( 'test', function( $table ) {
	// single index
	$table->renameIndex( 'idx_test_label', 'idx_test_name' );

	// multiple indexes
	$table->renameIndex( ['idx_test_label' => 'idx_test_name', 'idx_text_stat' => 'idx_test_status'] );
} );

In all cases, indexes are only renamed if they exist. No error is reported if one or more indexes doesn't exist in the table.

Dropping indexes

To drop indexes, use the dropIndex() method from the DB schema object:

$this->db()->dropIndex( 'users', 'idx_test_name' );

You can drop several indexes at once if you pass the name of all indexes you want to drop as array:

$this->db()->dropIndex( 'users', ['idx_test_name', 'idx_test_status'] );

If you already have a table object, you can use dropIndex() too:

$this->db()->table( 'test', function( $table ) {
	// single index
	$table->dropIndex( 'idx_test_name' );

	// multiple indexes
	$table->dropIndex( ['idx_test_name', 'idx_test_status'] );
} );

In all cases, indexes are only removed if they exist. No error is reported if one or more indexes doesn't exist in the table.

Custom index naming

It's not necessary to pass a custom index name when creating new indexes. Then, the index name is generated automatically but their name will consist of a hash that is hard to read. Also, you don't know which columns the indexes span from the index name.

Upscheme allows you to add your own naming function for indexes which is used if not index name is passed to the methods for creating indexes. Before running the migrations, register your nameing function using the macro() method in the table objects:

use \Aimeos\Upscheme\Schema\Table;

Table::marco( 'nameIndex', function( string $table, array $columns, string $type ) {
	return $type . '_' . $table . '_' . join( '_', $columns );
} );

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

For a table "testtable", a column "label" and the type "idx", this will return idx_testtable_label instead of a hash.

Available index types are:

idx : Regular and spatial indexes
fk : Foreign key index
pk : Primary key index
unq : Unique index

Note: For compatibility to all supported database types, the maximum length of the index names must be not longer than 30 characters!

Customizing Upscheme

Adding custom methods

You can add new methods to all Upscheme objects using the macro() method. Each custom method has access to the class properties and methods of the class it's registered for including the Doctrine DBAL objects.

To register a method named test() in the DB schema object with two parameters $arg1 and $arg2 which has access to the same class properties as the DB __call() method use:

\Aimeos\Upscheme\Schema\DB::marco( 'test', function( $arg1, $arg2 ) {
	// $this->conn : Doctrine connection
	// $this->from : Doctrine start schema
	// $this->to : Doctrine current schema
	// $this->up : Upscheme object
	// return $this or a value
} );

$db->test( 'key', 'value' );

Registering a method test() in the Table schema object with one parameter $arg1 which has access to the same class properties as the Table __call() method use:

\Aimeos\Upscheme\Schema\Table::marco( 'test', function( $arg1 ) {
	// $this->db : Upscheme DB object
	// $this->table : Doctrine Table object
	// return $this or a value
} );

$table->test( 'something' );

Same for a method test() in the Column schema object with an optional parameter $value which has access to the same class properties as the Column __call() method use:

\Aimeos\Upscheme\Schema\Column::marco( 'test', function( $value = null ) {
	// $this->db : Upscheme DB object
	// $this->table : Upscheme Table object
	// $this->column : Doctrine Column object
	// return $this or a value
} );

$column->test();

To extend the Foreign object for foreign key constraints with a test() method with no parameter having access to the same class properties as the Foreign __call() method use:

\Aimeos\Upscheme\Schema\Foreign::marco( 'test', function() {
	// $this->table : Upscheme Table object
	// $this->dbaltable : Doctrine Table object
	// $this->localcol : Array of local column names
	// $this->fktable : Foreign table name
	// $this->fkcol : Foreign table column names
	// $this->name : Foreign key name
	// $this->opts : Array of foreign key options ("onDelete" and "onUpdate")
	// return $this or a value
} );

$foreign->test();

Finally, extending the Sequence object with a test() method having no parameters and access to the same class properties as the Sequence __call() method use:

\Aimeos\Upscheme\Schema\Sequence::marco( 'test', function() {
	// $this->db : Upscheme DB object
	// $this->sequence : Doctrine Sequence object
	// return $this or a value
} );

$sequence->test();

Implementing custom columns

Instead of calling the col() method of the Table object with all parameters and modifiers each time, you can create your own shortcut methods, e.g.:

\Aimeos\Upscheme\Schema\Table::marco( 'utinyint', function( string $name ) {
	return $this->col( $name, 'tinyint' )->unsigned( true );
} );

It's also possible to create several columns at once if you want to add them to several tables:

\Aimeos\Upscheme\Schema\Table::marco( 'defaults', function() {
	$this->id();
	$this->datetime( 'ctime' );
	$this->datetime( 'mtime' );
	$this->string( 'editor' );
	return $this;
} );

Then, use your custom methods when creating or updating tables:

$this->db()->table( 'test', function( $table ) {
	$table->defaults();
	$table->utinyint( 'status' );
} );

Upgrade Upscheme

To 0.9.0

Version 0.9+ supports Doctrine DBAL 3.x/4.x and dropped support for Doctrine DBAL 2.x.

DB::type() returns mariadb instead of mysql for MariaDDB database
DB::type() returns sqlserver instead of mssql for Microsoft SQLServer database
DB::for(), DB::view() and Column::opt require ['mariadb', 'mysql'] to get the same results
DB::lastId() doesn't require/support passing a sequence name because Doctrine DBAL removed it but doesn't support Oracle IDENTITY columns at the moment

Name		Name	Last commit message	Last commit date
Latest commit History 305 Commits
.circleci		.circleci
_layouts		_layouts
assets		assets
src		src
stubs		stubs
tests		tests
.gitignore		.gitignore
CNAME		CNAME
LICENSE		LICENSE
README.md		README.md
_config.yml		_config.yml
composer.json		composer.json
phpunit.xml		phpunit.xml

License

aimeos/upscheme

Folders and files

Latest commit

History

Repository files navigation

Upscheme: Database schema updates made easy

Why Upscheme

Doctrine DBAL drawbacks

Doctrine Migration drawbacks

Database support

Integrating Upscheme

Writing migrations

Naming migrations

Dependencies

Messages

Schemas

Generate from database

Database

Accessing objects

Checking existence

Renaming objects

Removing objects

Query/modify table rows

Executing custom SQL

Database methods

DB::__call()

DB::close()

DB::delete()

DB::dropColumn()

DB::dropForeign()

DB::dropIndex()

DB::dropSequence()

DB::dropTable()

DB::dropView()

DB::exec()

DB::for()

DB::hasColumn()

DB::hasForeign()

DB::hasIndex()

DB::hasSequence()

DB::hasTable()

DB::hasView()

DB::insert()

DB::lastId()

DB::name()

DB::q()

DB::qi()

DB::query()

DB::renameColumn()

DB::renameIndex()

DB::renameTable()

DB::reset()

DB::select()

DB::sequence()

DB::stmt()

DB::table()

DB::transaction()

DB::toArray()

DB::type()

DB::up()

DB::update()

DB::view()

Tables

Creating tables

Setting table options

Checking table existence

Changing tables

Renaming tables

Dropping tables

Table methods

Table::__call()

Table::__get()

Table::__set()

Table::bigid()

Table::bigint()

Table::binary()

Table::blob()

Table::bool()

Table::boolean()