graph TD
A[schema 定义文件（PHP 数组）] --> B[Schema Builder 构建目标结构]
C[现有数据库] --> D[Doctrine SchemaManager 读取元数据]
D --> E[当前结构快照]
B --> F[Comparator 差异比对]
E --> F
F -->|"存在差异"| G[数据库方言适配层]
F -->|"结构一致"| H[跳过执行，保证幂等]
G --> I[生成 CREATE / ALTER / DROP 语句]
I --> J[事务内执行结构变更]
J --> K[按依赖顺序执行数据迁移 Task]
K --> L[写入迁移版本记录]
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):
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:
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:
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:
$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.:
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:
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:
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:
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:
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:
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:
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():
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:
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 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 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:
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:
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:
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
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:
$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 from src/Schema/Schema.php 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!
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!
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
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 ) {
// ...
}
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 occurred
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
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.:
Besides the col() method which can add columns of arbitrary types,
there are some shortcut methods for types available in all database server implementations:
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:
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:
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:
评论