1935 lines
57 KiB
ReStructuredText
1935 lines
57 KiB
ReStructuredText
.. index::
|
||
single: Writing Migrations
|
||
|
||
Writing Migrations
|
||
==================
|
||
|
||
Phinx relies on migrations in order to transform your database. Each migration
|
||
is represented by a PHP class in a unique file. It is preferred that you write
|
||
your migrations using the Phinx PHP API, but raw SQL is also supported.
|
||
|
||
Creating a New Migration
|
||
------------------------
|
||
Generating a skeleton migration file
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Let's start by creating a new Phinx migration. Run Phinx using the ``create``
|
||
command:
|
||
|
||
.. code-block:: bash
|
||
|
||
$ vendor/bin/phinx create MyNewMigration
|
||
|
||
This will create a new migration in the format
|
||
``YYYYMMDDHHMMSS_my_new_migration.php``, where the first 14 characters are
|
||
replaced with the current timestamp down to the second.
|
||
|
||
If you have specified multiple migration paths, you will be asked to select
|
||
which path to create the new migration in.
|
||
|
||
Phinx automatically creates a skeleton migration file with a single method:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Change Method.
|
||
*
|
||
* Write your reversible migrations using this method.
|
||
*
|
||
* More information on writing migrations is available here:
|
||
* https://book.cakephp.org/phinx/0/en/migrations.html#the-change-method
|
||
*
|
||
* Remember to call "create()" or "update()" and NOT "save()" when working
|
||
* with the Table class.
|
||
*
|
||
*/
|
||
public function change()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
All Phinx migrations extend from the ``AbstractMigration`` class. This class
|
||
provides the necessary support to create your database migrations. Database
|
||
migrations can transform your database in many ways, such as creating new
|
||
tables, inserting rows, adding indexes and modifying columns.
|
||
|
||
The Change Method
|
||
~~~~~~~~~~~~~~~~~
|
||
|
||
Phinx 0.2.0 introduced a new feature called reversible migrations. This feature
|
||
has now become the default migration method. With reversible migrations, you
|
||
only need to define the ``up`` logic, and Phinx can figure out how to migrate
|
||
down automatically for you. For example:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class CreateUserLoginsTable extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
// create the table
|
||
$table = $this->table('user_logins');
|
||
$table->addColumn('user_id', 'integer')
|
||
->addColumn('created', 'datetime')
|
||
->create();
|
||
}
|
||
}
|
||
|
||
When executing this migration, Phinx will create the ``user_logins`` table on
|
||
the way up and automatically figure out how to drop the table on the way down.
|
||
Please be aware that when a ``change`` method exists, Phinx will automatically
|
||
ignore the ``up`` and ``down`` methods. If you need to use these methods it is
|
||
recommended to create a separate migration file.
|
||
|
||
.. note::
|
||
|
||
When creating or updating tables inside a ``change()`` method you must use
|
||
the Table ``create()`` and ``update()`` methods. Phinx cannot automatically
|
||
determine whether a ``save()`` call is creating a new table or modifying an
|
||
existing one.
|
||
|
||
The following actions are reversible when done through the Table API in Phinx,
|
||
and will be automatically reversed:
|
||
|
||
- Creating a table
|
||
- Renaming a table
|
||
- Adding a column
|
||
- Renaming a column
|
||
- Adding an index
|
||
- Adding a foreign key
|
||
|
||
If a command cannot be reversed then Phinx will throw an
|
||
``IrreversibleMigrationException`` when it's migrating down. If you wish to
|
||
use a command that cannot be reversed in the change function, you can use an
|
||
if statement with ``$this->isMigratingUp()`` to only run things in the
|
||
up or down direction. For example:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class CreateUserLoginsTable extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
// create the table
|
||
$table = $this->table('user_logins');
|
||
$table->addColumn('user_id', 'integer')
|
||
->addColumn('created', 'datetime')
|
||
->create();
|
||
if ($this->isMigratingUp()) {
|
||
$table->insert([['user_id' => 1, 'created' => '2020-01-19 03:14:07']])
|
||
->save();
|
||
}
|
||
}
|
||
}
|
||
|
||
The Up Method
|
||
~~~~~~~~~~~~~
|
||
|
||
The up method is automatically run by Phinx when you are migrating up and it
|
||
detects the given migration hasn't been executed previously. You should use the
|
||
up method to transform the database with your intended changes.
|
||
|
||
The Down Method
|
||
~~~~~~~~~~~~~~~
|
||
|
||
The down method is automatically run by Phinx when you are migrating down and
|
||
it detects the given migration has been executed in the past. You should use
|
||
the down method to reverse/undo the transformations described in the up method.
|
||
|
||
The Init Method
|
||
~~~~~~~~~~~~~~~
|
||
|
||
The ``init()`` method is run by Phinx before the migration methods if it exists.
|
||
This can be used for setting common class properties that are then used within
|
||
the migration methods.
|
||
|
||
The Should Execute Method
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
The ``shouldExecute()`` method is run by Phinx before executing the migration.
|
||
This can be used to prevent the migration from being executed at this time. It always
|
||
returns true by default. You can override it in your custom ``AbstractMigration``
|
||
implementation.
|
||
|
||
Executing Queries
|
||
-----------------
|
||
|
||
Queries can be executed with the ``execute()`` and ``query()`` methods. The
|
||
``execute()`` method returns the number of affected rows whereas the
|
||
``query()`` method returns the result as a
|
||
`PDOStatement <https://php.net/manual/en/class.pdostatement.php>`_. Both methods
|
||
accept an optional second parameter ``$params`` which is an array of elements,
|
||
and if used will cause the underlying connection to use a prepared statement.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
// execute()
|
||
$count = $this->execute('DELETE FROM users'); // returns the number of affected rows
|
||
|
||
// query()
|
||
$stmt = $this->query('SELECT * FROM users'); // returns PDOStatement
|
||
$rows = $stmt->fetchAll(); // returns the result as an array
|
||
|
||
// using prepared queries
|
||
$count = $this->execute('DELETE FROM users WHERE id = ?', [5]);
|
||
$stmt = $this->query('SELECT * FROM users WHERE id > ?', [5]); // returns PDOStatement
|
||
$rows = $stmt->fetchAll();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
.. note::
|
||
|
||
These commands run using the PHP Data Objects (PDO) extension which
|
||
defines a lightweight, consistent interface for accessing databases
|
||
in PHP. Always make sure your queries abide with PDOs before using
|
||
the ``execute()`` command. This is especially important when using
|
||
DELIMITERs during insertion of stored procedures or triggers which
|
||
don't support DELIMITERs.
|
||
|
||
.. note::
|
||
|
||
If you wish to execute multiple queries at once, you may not also use the prepared
|
||
variant of these functions. When using prepared queries, PDO can only execute
|
||
them one at a time.
|
||
|
||
.. warning::
|
||
|
||
When using ``execute()`` or ``query()`` with a batch of queries, PDO doesn't
|
||
throw an exception if there is an issue with one or more of the queries
|
||
in the batch.
|
||
|
||
As such, the entire batch is assumed to have passed without issue.
|
||
|
||
If Phinx was to iterate any potential result sets, looking to see if one
|
||
had an error, then Phinx would be denying access to all the results as there
|
||
is no facility in PDO to get a previous result set
|
||
`nextRowset() <https://php.net/manual/en/pdostatement.nextrowset.php>`_ -
|
||
but no ``previousSet()``).
|
||
|
||
So, as a consequence, due to the design decision in PDO to not throw
|
||
an exception for batched queries, Phinx is unable to provide the fullest
|
||
support for error handling when batches of queries are supplied.
|
||
|
||
Fortunately though, all the features of PDO are available, so multiple batches
|
||
can be controlled within the migration by calling upon
|
||
`nextRowset() <https://php.net/manual/en/pdostatement.nextrowset.php>`_
|
||
and examining `errorInfo <https://php.net/manual/en/pdostatement.errorinfo.php>`_.
|
||
|
||
Fetching Rows
|
||
-------------
|
||
|
||
There are two methods available to fetch rows. The ``fetchRow()`` method will
|
||
fetch a single row, whilst the ``fetchAll()`` method will return multiple rows.
|
||
Both methods accept raw SQL as their only parameter.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
// fetch a user
|
||
$row = $this->fetchRow('SELECT * FROM users');
|
||
|
||
// fetch an array of messages
|
||
$rows = $this->fetchAll('SELECT * FROM messages');
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
Inserting Data
|
||
--------------
|
||
|
||
Phinx makes it easy to insert data into your tables. Whilst this feature is
|
||
intended for the :doc:`seed feature <seeding>`, you are also free to use the
|
||
insert methods in your migrations.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class NewStatus extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('status');
|
||
|
||
// inserting only one row
|
||
$singleRow = [
|
||
'id' => 1,
|
||
'name' => 'In Progress'
|
||
];
|
||
|
||
$table->insert($singleRow)->saveData();
|
||
|
||
// inserting multiple rows
|
||
$rows = [
|
||
[
|
||
'id' => 2,
|
||
'name' => 'Stopped'
|
||
],
|
||
[
|
||
'id' => 3,
|
||
'name' => 'Queued'
|
||
]
|
||
];
|
||
|
||
$table->insert($rows)->saveData();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
$this->execute('DELETE FROM status');
|
||
}
|
||
}
|
||
|
||
.. note::
|
||
|
||
You cannot use the insert methods inside a `change()` method. Please use the
|
||
`up()` and `down()` methods.
|
||
|
||
Working With Tables
|
||
-------------------
|
||
|
||
The Table Object
|
||
~~~~~~~~~~~~~~~~
|
||
|
||
The Table object is one of the most useful APIs provided by Phinx. It allows
|
||
you to easily manipulate database tables using PHP code. You can retrieve an
|
||
instance of the Table object by calling the ``table()`` method from within
|
||
your database migration.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('tableName');
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
You can then manipulate this table using the methods provided by the Table
|
||
object.
|
||
|
||
Saving Changes
|
||
~~~~~~~~~~~~~~
|
||
|
||
When working with the Table object, Phinx stores certain operations in a
|
||
pending changes cache. Once you have made the changes you want to the table,
|
||
you must save them. To perform this operation, Phinx provides three methods,
|
||
``create()``, ``update()``, and ``save()``. ``create()`` will first create
|
||
the table and then run the pending changes. ``update()`` will just run the
|
||
pending changes, and should be used when the table already exists. ``save()``
|
||
is a helper function that checks first if the table exists and if it does not
|
||
will run ``create()``, else it will run ``update()``.
|
||
|
||
As stated above, when using the ``change()`` migration method, you should always
|
||
use ``create()`` or ``update()``, and never ``save()`` as otherwise migrating
|
||
and rolling back may result in different states, due to ``save()`` calling
|
||
``create()`` when running migrate and then ``update()`` on rollback. When
|
||
using the ``up()``/``down()`` methods, it is safe to use either ``save()`` or
|
||
the more explicit methods.
|
||
|
||
When in doubt with working with tables, it is always recommended to call
|
||
the appropriate function and commit any pending changes to the database.
|
||
|
||
Creating a Table
|
||
~~~~~~~~~~~~~~~~
|
||
|
||
Creating a table is really easy using the Table object. Let's create a table to
|
||
store a collection of users.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$users = $this->table('users');
|
||
$users->addColumn('username', 'string', ['limit' => 20])
|
||
->addColumn('password', 'string', ['limit' => 40])
|
||
->addColumn('password_salt', 'string', ['limit' => 40])
|
||
->addColumn('email', 'string', ['limit' => 100])
|
||
->addColumn('first_name', 'string', ['limit' => 30])
|
||
->addColumn('last_name', 'string', ['limit' => 30])
|
||
->addColumn('created', 'datetime')
|
||
->addColumn('updated', 'datetime', ['null' => true])
|
||
->addIndex(['username', 'email'], ['unique' => true])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
Columns are added using the ``addColumn()`` method. We create a unique index
|
||
for both the username and email columns using the ``addIndex()`` method.
|
||
Finally calling ``create()`` commits the changes to the database.
|
||
|
||
.. note::
|
||
|
||
Phinx automatically creates an auto-incrementing primary key column called ``id`` for every
|
||
table.
|
||
|
||
The ``id`` option sets the name of the automatically created identity field, while the ``primary_key``
|
||
option selects the field or fields used for primary key. ``id`` will always override the ``primary_key``
|
||
option unless it's set to false. If you don't need a primary key set ``id`` to false without
|
||
specifying a ``primary_key``, and no primary key will be created.
|
||
|
||
To specify an alternate primary key, you can specify the ``primary_key`` option
|
||
when accessing the Table object. Let's disable the automatic ``id`` column and
|
||
create a primary key using two columns instead:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$table = $this->table('followers', ['id' => false, 'primary_key' => ['user_id', 'follower_id']]);
|
||
$table->addColumn('user_id', 'integer')
|
||
->addColumn('follower_id', 'integer')
|
||
->addColumn('created', 'datetime')
|
||
->create();
|
||
}
|
||
}
|
||
|
||
Setting a single ``primary_key`` doesn't enable the ``AUTO_INCREMENT`` option.
|
||
To simply change the name of the primary key, we need to override the default ``id`` field name:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function up()
|
||
{
|
||
$table = $this->table('followers', ['id' => 'user_id']);
|
||
$table->addColumn('follower_id', 'integer')
|
||
->addColumn('created', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
In addition, the MySQL adapter supports following options:
|
||
|
||
========== ===========
|
||
Option Description
|
||
========== ===========
|
||
comment set a text comment on the table
|
||
row_format set the table row format
|
||
engine define table engine *(defaults to ``InnoDB``)*
|
||
collation define table collation *(defaults to ``utf8mb4_unicode_ci``)*
|
||
signed whether the primary key is ``signed`` *(defaults to ``false``)*
|
||
limit set the maximum length for the primary key
|
||
========== ===========
|
||
|
||
By default, the primary key is ``unsigned``.
|
||
To simply set it to be signed just pass ``signed`` option with a ``true`` value:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$table = $this->table('followers', ['signed' => false]);
|
||
$table->addColumn('follower_id', 'integer')
|
||
->addColumn('created', 'timestamp', ['default' => 'CURRENT_TIMESTAMP'])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
|
||
The PostgreSQL adapter supports the following options:
|
||
|
||
========= ===========
|
||
Option Description
|
||
========= ===========
|
||
comment set a text comment on the table
|
||
========= ===========
|
||
|
||
To view available column types and options, see `Valid Column Types`_ for details.
|
||
|
||
Determining Whether a Table Exists
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
You can determine whether or not a table exists by using the ``hasTable()``
|
||
method.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$exists = $this->hasTable('users');
|
||
if ($exists) {
|
||
// do something
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
Dropping a Table
|
||
~~~~~~~~~~~~~~~~
|
||
|
||
Tables can be dropped quite easily using the ``drop()`` method. It is a
|
||
good idea to recreate the table again in the ``down()`` method.
|
||
|
||
Note that like other methods in the ``Table`` class, ``drop`` also needs ``save()``
|
||
to be called at the end in order to be executed. This allows phinx to intelligently
|
||
plan migrations when more than one table is involved.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$this->table('users')->drop()->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
$users = $this->table('users');
|
||
$users->addColumn('username', 'string', ['limit' => 20])
|
||
->addColumn('password', 'string', ['limit' => 40])
|
||
->addColumn('password_salt', 'string', ['limit' => 40])
|
||
->addColumn('email', 'string', ['limit' => 100])
|
||
->addColumn('first_name', 'string', ['limit' => 30])
|
||
->addColumn('last_name', 'string', ['limit' => 30])
|
||
->addColumn('created', 'datetime')
|
||
->addColumn('updated', 'datetime', ['null' => true])
|
||
->addIndex(['username', 'email'], ['unique' => true])
|
||
->save();
|
||
}
|
||
}
|
||
|
||
Renaming a Table
|
||
~~~~~~~~~~~~~~~~
|
||
|
||
To rename a table access an instance of the Table object then call the
|
||
``rename()`` method.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('users');
|
||
$table
|
||
->rename('legacy_users')
|
||
->update();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
$table = $this->table('legacy_users');
|
||
$table
|
||
->rename('users')
|
||
->update();
|
||
}
|
||
}
|
||
|
||
Changing the Primary Key
|
||
~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
To change the primary key on an existing table, use the ``changePrimaryKey()`` method.
|
||
Pass in a column name or array of columns names to include in the primary key, or ``null`` to drop the primary key.
|
||
Note that the mentioned columns must be added to the table, they will not be added implicitly.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$users = $this->table('users');
|
||
$users
|
||
->addColumn('username', 'string', ['limit' => 20, 'null' => false])
|
||
->addColumn('password', 'string', ['limit' => 40])
|
||
->save();
|
||
|
||
$users
|
||
->addColumn('new_id', 'integer', ['null' => false])
|
||
->changePrimaryKey(['new_id', 'username'])
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
Changing the Table Comment
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
To change the comment on an existing table, use the ``changeComment()`` method.
|
||
Pass in a string to set as the new table comment, or ``null`` to drop the existing comment.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$users = $this->table('users');
|
||
$users
|
||
->addColumn('username', 'string', ['limit' => 20])
|
||
->addColumn('password', 'string', ['limit' => 40])
|
||
->save();
|
||
|
||
$users
|
||
->changeComment('This is the table with users auth information, password should be encrypted')
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
Working With Columns
|
||
--------------------
|
||
|
||
.. _valid-column-types:
|
||
|
||
Valid Column Types
|
||
~~~~~~~~~~~~~~~~~~
|
||
|
||
Column types are specified as strings and can be one of:
|
||
|
||
- binary
|
||
- boolean
|
||
- char
|
||
- date
|
||
- datetime
|
||
- decimal
|
||
- float
|
||
- double
|
||
- smallinteger
|
||
- integer
|
||
- biginteger
|
||
- string
|
||
- text
|
||
- time
|
||
- timestamp
|
||
- uuid
|
||
|
||
In addition, the MySQL adapter supports ``enum``, ``set``, ``blob``, ``tinyblob``, ``mediumblob``, ``longblob``, ``bit`` and ``json`` column types
|
||
(``json`` in MySQL 5.7 and above). When providing a limit value and using ``binary``, ``varbinary`` or ``blob`` and its subtypes, the retained column
|
||
type will be based on required length (see `Limit Option and MySQL`_ for details);
|
||
|
||
In addition, the Postgres adapter supports ``interval``, ``json``, ``jsonb``, ``uuid``, ``cidr``, ``inet`` and ``macaddr`` column types
|
||
(PostgreSQL 9.3 and above).
|
||
|
||
Valid Column Options
|
||
~~~~~~~~~~~~~~~~~~~~
|
||
|
||
The following are valid column options:
|
||
|
||
For any column type:
|
||
|
||
======= ===========
|
||
Option Description
|
||
======= ===========
|
||
limit set maximum length for strings, also hints column types in adapters (see note below)
|
||
length alias for ``limit``
|
||
default set default value or action
|
||
null allow ``NULL`` values, defaults to false if `identity` option is set to true, else defaults to true
|
||
after specify the column that a new column should be placed after, or use ``\Phinx\Db\Adapter\MysqlAdapter::FIRST`` to place the column at the start of the table *(only applies to MySQL)*
|
||
comment set a text comment on the column
|
||
======= ===========
|
||
|
||
For ``decimal`` columns:
|
||
|
||
========= ===========
|
||
Option Description
|
||
========= ===========
|
||
precision combine with ``scale`` set to set decimal accuracy
|
||
scale combine with ``precision`` to set decimal accuracy
|
||
signed enable or disable the ``unsigned`` option *(only applies to MySQL)*
|
||
========= ===========
|
||
|
||
For ``enum`` and ``set`` columns:
|
||
|
||
========= ===========
|
||
Option Description
|
||
========= ===========
|
||
values Can be a comma separated list or an array of values
|
||
========= ===========
|
||
|
||
For ``smallinteger``, ``integer`` and ``biginteger`` columns:
|
||
|
||
======== ===========
|
||
Option Description
|
||
======== ===========
|
||
identity enable or disable automatic incrementing
|
||
signed enable or disable the ``unsigned`` option *(only applies to MySQL)*
|
||
======== ===========
|
||
|
||
For Postgres, when using ``identity``, it will utilize the ``serial`` type appropriate for the integer size, so that
|
||
``smallinteger`` will give you ``smallserial``, ``integer`` gives ``serial``, and ``biginteger`` gives ``bigserial``.
|
||
|
||
For ``timestamp`` columns:
|
||
|
||
======== ===========
|
||
Option Description
|
||
======== ===========
|
||
default set default value (use with ``CURRENT_TIMESTAMP``)
|
||
update set an action to be triggered when the row is updated (use with ``CURRENT_TIMESTAMP``) *(only applies to MySQL)*
|
||
timezone enable or disable the ``with time zone`` option for ``time`` and ``timestamp`` columns *(only applies to Postgres)*
|
||
======== ===========
|
||
|
||
You can add ``created_at`` and ``updated_at`` timestamps to a table using the ``addTimestamps()`` method. This method accepts
|
||
three arguments, where the first two allow setting alternative names for the columns while the third argument allows you to
|
||
enable the ``timezone`` option for the columns. The defaults for these arguments are ``created_at``, ``updated_at``, and ``false``
|
||
respectively. For the first and second argument, if you provide ``null``, then the default name will be used, and if you provide
|
||
``false``, then that column will not be created. Please note that attempting to set both to ``false`` will throw a
|
||
``\RuntimeException``. Additionally, you can use the ``addTimestampsWithTimezone()`` method, which is an alias to
|
||
``addTimestamps()`` that will always set the third argument to ``true`` (see examples below). The ``created_at`` column will
|
||
have a default set to ``CURRENT_TIMESTAMP``. For MySQL only, ``update_at`` column will have update set to
|
||
``CURRENT_TIMESTAMP``.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Change.
|
||
*/
|
||
public function change()
|
||
{
|
||
// Use defaults (without timezones)
|
||
$table = $this->table('users')->addTimestamps()->create();
|
||
// Use defaults (with timezones)
|
||
$table = $this->table('users')->addTimestampsWithTimezone()->create();
|
||
|
||
// Override the 'created_at' column name with 'recorded_at'.
|
||
$table = $this->table('books')->addTimestamps('recorded_at')->create();
|
||
|
||
// Override the 'updated_at' column name with 'amended_at', preserving timezones.
|
||
// The two lines below do the same, the second one is simply cleaner.
|
||
$table = $this->table('books')->addTimestamps(null, 'amended_at', true)->create();
|
||
$table = $this->table('users')->addTimestampsWithTimezone(null, 'amended_at')->create();
|
||
|
||
// Only add the created_at column to the table
|
||
$table = $this->table('books')->addTimestamps(null, false);
|
||
// Only add the updated_at column to the table
|
||
$table = $this->table('users')->addTimestamps(false);
|
||
// Note, setting both false will throw a \RuntimeError
|
||
}
|
||
}
|
||
|
||
For ``boolean`` columns:
|
||
|
||
======== ===========
|
||
Option Description
|
||
======== ===========
|
||
signed enable or disable the ``unsigned`` option *(only applies to MySQL)*
|
||
======== ===========
|
||
|
||
For ``string`` and ``text`` columns:
|
||
|
||
========= ===========
|
||
Option Description
|
||
========= ===========
|
||
collation set collation that differs from table defaults *(only applies to MySQL)*
|
||
encoding set character set that differs from table defaults *(only applies to MySQL)*
|
||
========= ===========
|
||
|
||
For foreign key definitions:
|
||
|
||
========== ===========
|
||
Option Description
|
||
========== ===========
|
||
update set an action to be triggered when the row is updated
|
||
delete set an action to be triggered when the row is deleted
|
||
constraint set a name to be used by foreign key constraint
|
||
========== ===========
|
||
|
||
You can pass one or more of these options to any column with the optional
|
||
third argument array.
|
||
|
||
Limit Option and MySQL
|
||
~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
When using the MySQL adapter, there are a couple things to consider when working with limits:
|
||
|
||
- When using a ``string`` primary key or index on MySQL 5.7 or below, or the MyISAM storage engine, and the default charset of ``utf8mb4_unicode_ci``, you must specify a limit less than or equal to 191, or use a different charset.
|
||
- Additional hinting of database column type can be made for ``integer``, ``text``, ``blob``, ``tinyblob``, ``mediumblob``, ``longblob`` columns. Using ``limit`` with one the following options will modify the column type accordingly:
|
||
|
||
============ ==============
|
||
Limit Column Type
|
||
============ ==============
|
||
BLOB_TINY TINYBLOB
|
||
BLOB_REGULAR BLOB
|
||
BLOB_MEDIUM MEDIUMBLOB
|
||
BLOB_LONG LONGBLOB
|
||
TEXT_TINY TINYTEXT
|
||
TEXT_REGULAR TEXT
|
||
TEXT_MEDIUM MEDIUMTEXT
|
||
TEXT_LONG LONGTEXT
|
||
INT_TINY TINYINT
|
||
INT_SMALL SMALLINT
|
||
INT_MEDIUM MEDIUMINT
|
||
INT_REGULAR INT
|
||
INT_BIG BIGINT
|
||
============ ==============
|
||
|
||
For ``binary`` or ``varbinary`` types, if limit is set greater than allowed 255 bytes, the type will be changed to the best matching blob type given the length.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Db\Adapter\MysqlAdapter;
|
||
|
||
//...
|
||
|
||
$table = $this->table('cart_items');
|
||
$table->addColumn('user_id', 'integer')
|
||
->addColumn('product_id', 'integer', ['limit' => MysqlAdapter::INT_BIG])
|
||
->addColumn('subtype_id', 'integer', ['limit' => MysqlAdapter::INT_SMALL])
|
||
->addColumn('quantity', 'integer', ['limit' => MysqlAdapter::INT_TINY])
|
||
->create();
|
||
|
||
Custom Column Types & Default Values
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Some DBMS systems provide additional column types and default values that are specific to them.
|
||
If you don't want to keep your migrations DBMS-agnostic you can use those custom types in your migrations
|
||
through the ``\Phinx\Util\Literal::from`` method, which takes a string as its only argument, and returns an
|
||
instance of ``\Phinx\Util\Literal``. When Phinx encounters this value as a column's type it knows not to
|
||
run any validation on it and to use it exactly as supplied without escaping. This also works for ``default``
|
||
values.
|
||
|
||
You can see an example below showing how to add a ``citext`` column as well as a column whose default value
|
||
is a function, in PostgreSQL. This method of preventing the built-in escaping is supported in all adapters.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
use Phinx\Util\Literal;
|
||
|
||
class AddSomeColumns extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$this->table('users')
|
||
->addColumn('username', Literal::from('citext'))
|
||
->addColumn('uniqid', 'uuid', [
|
||
'default' => Literal::from('uuid_generate_v4()')
|
||
])
|
||
->addColumn('creation', 'timestamp', [
|
||
'timezone' => true,
|
||
'default' => Literal::from('now()')
|
||
])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
User Defined Types (Custom Data Domain)
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Building upon the base types and column options you can define your custom
|
||
user defined types. Custom user defined types are configured in the
|
||
``data_domain`` root config option.
|
||
|
||
.. code-block:: yaml
|
||
|
||
data_domain:
|
||
phone_number:
|
||
type: string
|
||
length: 20
|
||
address_line:
|
||
type: string
|
||
length: 150
|
||
|
||
Each user defined type can hold any valid type and column option, they are just
|
||
used as "macros" and replaced at the time of migration.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
//...
|
||
|
||
$table = $this->table('user_data');
|
||
$table->addColumn('user_phone_number', 'phone_number')
|
||
->addColumn('user_address_line_1', 'address_line')
|
||
->addColumn('user_address_line_2', 'address_line', ['null' => true])
|
||
->create();
|
||
|
||
Specifying a data domain at the beginning of your project is crucial to have a
|
||
homogeneous data model. It avoids mistakes like having many ``contact_name``
|
||
columns with different lengths, mismatched integer types (long vs. bigint, etc).
|
||
|
||
.. note::
|
||
|
||
For ``integer``, ``text`` and ``blob`` columns you can use the special
|
||
constants from MySQL and Postgress adapter classes.
|
||
|
||
You can even customize some internal types to add your own default options,
|
||
but some column options can't be overriden in the data model (some options
|
||
are fixed like ``limit`` for the ``uuid`` special data type).
|
||
|
||
.. code-block:: yaml
|
||
|
||
# Some examples of custom data types
|
||
data_domain:
|
||
file:
|
||
type: blob
|
||
limit: BLOB_LONG # For MySQL DB. Uses MysqlAdapter::BLOB_LONG
|
||
boolean:
|
||
type: boolean # Customization of the boolean to be unsigned
|
||
signed: false
|
||
image_type:
|
||
type: enum # Enums can use YAML lists or a comma separated string
|
||
values:
|
||
- gif
|
||
- jpg
|
||
- png
|
||
|
||
Get a column list
|
||
~~~~~~~~~~~~~~~~~
|
||
|
||
To retrieve all table columns, simply create a `table` object and call `getColumns()`
|
||
method. This method will return an array of Column classes with basic info. Example below:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class ColumnListMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$columns = $this->table('users')->getColumns();
|
||
...
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
...
|
||
}
|
||
}
|
||
|
||
Get a column by name
|
||
~~~~~~~~~~~~~~~~~~~~
|
||
|
||
To retrieve one table column, simply create a `table` object and call the `getColumn()`
|
||
method. This method will return a Column class with basic info or NULL when the column doesn't exist. Example below:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class ColumnListMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$column = $this->table('users')->getColumn('email');
|
||
...
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
...
|
||
}
|
||
}
|
||
|
||
Checking whether a column exists
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
You can check if a table already has a certain column by using the
|
||
``hasColumn()`` method.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Change Method.
|
||
*/
|
||
public function change()
|
||
{
|
||
$table = $this->table('user');
|
||
$column = $table->hasColumn('username');
|
||
|
||
if ($column) {
|
||
// do something
|
||
}
|
||
|
||
}
|
||
}
|
||
|
||
Renaming a Column
|
||
~~~~~~~~~~~~~~~~~
|
||
|
||
To rename a column, access an instance of the Table object then call the
|
||
``renameColumn()`` method.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->renameColumn('bio', 'biography')
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->renameColumn('biography', 'bio')
|
||
->save();
|
||
}
|
||
}
|
||
|
||
Adding a Column After Another Column
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
When adding a column with the MySQL adapter, you can dictate its position using the ``after`` option,
|
||
where its value is the name of the column to position it after.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Change Method.
|
||
*/
|
||
public function change()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->addColumn('city', 'string', ['after' => 'email'])
|
||
->update();
|
||
}
|
||
}
|
||
|
||
This would create the new column ``city`` and position it after the ``email`` column. The
|
||
``\Phinx\Db\Adapter\MysqlAdapter::FIRST`` constant can be used to specify that the new column should be
|
||
created as the first column in that table.
|
||
|
||
Dropping a Column
|
||
~~~~~~~~~~~~~~~~~
|
||
|
||
To drop a column, use the ``removeColumn()`` method.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->removeColumn('short_name')
|
||
->save();
|
||
}
|
||
}
|
||
|
||
|
||
Specifying a Column Limit
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
You can limit the maximum length of a column by using the ``limit`` option.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Change Method.
|
||
*/
|
||
public function change()
|
||
{
|
||
$table = $this->table('tags');
|
||
$table->addColumn('short_name', 'string', ['limit' => 30])
|
||
->update();
|
||
}
|
||
}
|
||
|
||
Changing Column Attributes
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
To change column type or options on an existing column, use the ``changeColumn()`` method.
|
||
See :ref:`valid-column-types` and `Valid Column Options`_ for allowed values.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$users = $this->table('users');
|
||
$users->changeColumn('email', 'string', ['limit' => 255])
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
Working With Indexes
|
||
--------------------
|
||
|
||
To add an index to a table you can simply call the ``addIndex()`` method on the
|
||
table object.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->addColumn('city', 'string')
|
||
->addIndex(['city'])
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
By default Phinx instructs the database adapter to create a normal index. We
|
||
can pass an additional parameter ``unique`` to the ``addIndex()`` method to
|
||
specify a unique index. We can also explicitly specify a name for the index
|
||
using the ``name`` parameter, the index columns sort order can also be specified using
|
||
the ``order`` parameter. The order parameter takes an array of column names and sort order key/value pairs.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->addColumn('email', 'string')
|
||
->addColumn('username','string')
|
||
->addIndex(['email', 'username'], [
|
||
'unique' => true,
|
||
'name' => 'idx_users_email',
|
||
'order' => ['email' => 'DESC', 'username' => 'ASC']]
|
||
)
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
The MySQL adapter also supports ``fulltext`` indexes. If you are using a version before 5.6 you must
|
||
ensure the table uses the ``MyISAM`` engine.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$table = $this->table('users', ['engine' => 'MyISAM']);
|
||
$table->addColumn('email', 'string')
|
||
->addIndex('email', ['type' => 'fulltext'])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
In addition, MySQL adapter also supports setting the index length defined by limit option.
|
||
When you are using a multi-column index, you are able to define each column index length.
|
||
The single column index can define its index length with or without defining column name in limit option.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->addColumn('email', 'string')
|
||
->addColumn('username','string')
|
||
->addColumn('user_guid', 'string', ['limit' => 36])
|
||
->addIndex(['email','username'], ['limit' => ['email' => 5, 'username' => 2]])
|
||
->addIndex('user_guid', ['limit' => 6])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
The SQL Server and PostgreSQL adapters also supports ``include`` (non-key) columns on indexes.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->addColumn('email', 'string')
|
||
->addColumn('firstname','string')
|
||
->addColumn('lastname','string')
|
||
->addIndex(['email'], ['include' => ['firstname', 'lastname']])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
In addition PostgreSQL adapters also supports Generalized Inverted Index ``gin`` indexes.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
public function change()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->addColumn('address', 'string')
|
||
->addIndex('address', ['type' => 'gin'])
|
||
->create();
|
||
}
|
||
}
|
||
|
||
Removing indexes is as easy as calling the ``removeIndex()`` method. You must
|
||
call this method for each index.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('users');
|
||
$table->removeIndex(['email'])
|
||
->save();
|
||
|
||
// alternatively, you can delete an index by its name, ie:
|
||
$table->removeIndexByName('idx_users_email')
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
|
||
Working With Foreign Keys
|
||
-------------------------
|
||
|
||
Phinx has support for creating foreign key constraints on your database tables.
|
||
Let's add a foreign key to an example table:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('tags');
|
||
$table->addColumn('tag_name', 'string')
|
||
->save();
|
||
|
||
$refTable = $this->table('tag_relationships');
|
||
$refTable->addColumn('tag_id', 'integer', ['null' => true])
|
||
->addForeignKey('tag_id', 'tags', 'id', ['delete'=> 'SET_NULL', 'update'=> 'NO_ACTION'])
|
||
->save();
|
||
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
"On delete" and "On update" actions are defined with a 'delete' and 'update' options array. Possibles values are 'SET_NULL', 'NO_ACTION', 'CASCADE' and 'RESTRICT'. If 'SET_NULL' is used then the column must be created as nullable with the option ``['null' => true]``.
|
||
Constraint name can be changed with the 'constraint' option.
|
||
|
||
It is also possible to pass ``addForeignKey()`` an array of columns.
|
||
This allows us to establish a foreign key relationship to a table which uses a combined key.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('follower_events');
|
||
$table->addColumn('user_id', 'integer')
|
||
->addColumn('follower_id', 'integer')
|
||
->addColumn('event_id', 'integer')
|
||
->addForeignKey(['user_id', 'follower_id'],
|
||
'followers',
|
||
['user_id', 'follower_id'],
|
||
['delete'=> 'NO_ACTION', 'update'=> 'NO_ACTION', 'constraint' => 'user_follower_id'])
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
We can add named foreign keys using the ``constraint`` parameter. This feature is supported as of Phinx version 0.6.5
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('your_table');
|
||
$table->addForeignKey('foreign_id', 'reference_table', ['id'],
|
||
['constraint' => 'your_foreign_key_name']);
|
||
->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
We can also easily check if a foreign key exists:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('tag_relationships');
|
||
$exists = $table->hasForeignKey('tag_id');
|
||
if ($exists) {
|
||
// do something
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
Finally, to delete a foreign key, use the ``dropForeignKey`` method.
|
||
|
||
Note that like other methods in the ``Table`` class, ``dropForeignKey`` also needs ``save()``
|
||
to be called at the end in order to be executed. This allows phinx to intelligently
|
||
plan migrations when more than one table is involved.
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$table = $this->table('tag_relationships');
|
||
$table->dropForeignKey('tag_id')->save();
|
||
}
|
||
|
||
/**
|
||
* Migrate Down.
|
||
*/
|
||
public function down()
|
||
{
|
||
|
||
}
|
||
}
|
||
|
||
|
||
|
||
Using the Query Builder
|
||
-----------------------
|
||
|
||
It is not uncommon to pair database structure changes with data changes. For example, you may want to
|
||
migrate the data in a couple columns from the users to a newly created table. For this type of scenarios,
|
||
Phinx provides access to a Query builder object, that you may use to execute complex ``SELECT``, ``UPDATE``,
|
||
``INSERT`` or ``DELETE`` statements.
|
||
|
||
The Query builder is provided by the `cakephp/database <https://github.com/cakephp/database>`_ project, and should
|
||
be easy to work with as it resembles very closely plain SQL. Accesing the query builder is done by calling the
|
||
``getQueryBuilder()`` function:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
use Phinx\Migration\AbstractMigration;
|
||
|
||
class MyNewMigration extends AbstractMigration
|
||
{
|
||
/**
|
||
* Migrate Up.
|
||
*/
|
||
public function up()
|
||
{
|
||
$builder = $this->getQueryBuilder();
|
||
$statement = $builder->select('*')->from('users')->execute();
|
||
var_dump($statement->fetchAll());
|
||
}
|
||
}
|
||
|
||
Selecting Fields
|
||
~~~~~~~~~~~~~~~~
|
||
|
||
Adding fields to the SELECT clause:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
$builder->select(['id', 'title', 'body']);
|
||
|
||
// Results in SELECT id AS pk, title AS aliased_title, body ...
|
||
$builder->select(['pk' => 'id', 'aliased_title' => 'title', 'body']);
|
||
|
||
// Use a closure
|
||
$builder->select(function ($builder) {
|
||
return ['id', 'title', 'body'];
|
||
});
|
||
|
||
|
||
Where Conditions
|
||
~~~~~~~~~~~~~~~~
|
||
|
||
Generating conditions:
|
||
|
||
.. code-block:: php
|
||
|
||
// WHERE id = 1
|
||
$builder->where(['id' => 1]);
|
||
|
||
// WHERE id > 1
|
||
$builder->where(['id >' => 1]);
|
||
|
||
|
||
As you can see you can use any operator by placing it with a space after the field name. Adding multiple conditions is easy as well:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
$builder->where(['id >' => 1])->andWhere(['title' => 'My Title']);
|
||
|
||
// Equivalent to
|
||
$builder->where(['id >' => 1, 'title' => 'My title']);
|
||
|
||
// WHERE id > 1 OR title = 'My title'
|
||
$builder->where(['OR' => ['id >' => 1, 'title' => 'My title']]);
|
||
|
||
|
||
For even more complex conditions you can use closures and expression objects:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
// Coditions are tied together with AND by default
|
||
$builder
|
||
->select('*')
|
||
->from('articles')
|
||
->where(function ($exp) {
|
||
return $exp
|
||
->eq('author_id', 2)
|
||
->eq('published', true)
|
||
->notEq('spam', true)
|
||
->gt('view_count', 10);
|
||
});
|
||
|
||
|
||
Which results in:
|
||
|
||
.. code-block:: sql
|
||
|
||
SELECT * FROM articles
|
||
WHERE
|
||
author_id = 2
|
||
AND published = 1
|
||
AND spam != 1
|
||
AND view_count > 10
|
||
|
||
|
||
Combining expressions is also possible:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
$builder
|
||
->select('*')
|
||
->from('articles')
|
||
->where(function ($exp) {
|
||
$orConditions = $exp->or_(['author_id' => 2])
|
||
->eq('author_id', 5);
|
||
return $exp
|
||
->not($orConditions)
|
||
->lte('view_count', 10);
|
||
});
|
||
|
||
It generates:
|
||
|
||
.. code-block:: sql
|
||
|
||
SELECT *
|
||
FROM articles
|
||
WHERE
|
||
NOT (author_id = 2 OR author_id = 5)
|
||
AND view_count <= 10
|
||
|
||
|
||
When using the expression objects you can use the following methods to create conditions:
|
||
|
||
* ``eq()`` Creates an equality condition.
|
||
* ``notEq()`` Create an inequality condition
|
||
* ``like()`` Create a condition using the ``LIKE`` operator.
|
||
* ``notLike()`` Create a negated ``LIKE`` condition.
|
||
* ``in()`` Create a condition using ``IN``.
|
||
* ``notIn()`` Create a negated condition using ``IN``.
|
||
* ``gt()`` Create a ``>`` condition.
|
||
* ``gte()`` Create a ``>=`` condition.
|
||
* ``lt()`` Create a ``<`` condition.
|
||
* ``lte()`` Create a ``<=`` condition.
|
||
* ``isNull()`` Create an ``IS NULL`` condition.
|
||
* ``isNotNull()`` Create a negated ``IS NULL`` condition.
|
||
|
||
|
||
Aggregates and SQL Functions
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
// Results in SELECT COUNT(*) count FROM ...
|
||
$builder->select(['count' => $builder->func()->count('*')]);
|
||
|
||
A number of commonly used functions can be created with the func() method:
|
||
|
||
* ``sum()`` Calculate a sum. The arguments will be treated as literal values.
|
||
* ``avg()`` Calculate an average. The arguments will be treated as literal values.
|
||
* ``min()`` Calculate the min of a column. The arguments will be treated as literal values.
|
||
* ``max()`` Calculate the max of a column. The arguments will be treated as literal values.
|
||
* ``count()`` Calculate the count. The arguments will be treated as literal values.
|
||
* ``concat()`` Concatenate two values together. The arguments are treated as bound parameters unless marked as literal.
|
||
* ``coalesce()`` Coalesce values. The arguments are treated as bound parameters unless marked as literal.
|
||
* ``dateDiff()`` Get the difference between two dates/times. The arguments are treated as bound parameters unless marked as literal.
|
||
* ``now()`` Take either 'time' or 'date' as an argument allowing you to get either the current time, or current date.
|
||
|
||
When providing arguments for SQL functions, there are two kinds of parameters you can use,
|
||
literal arguments and bound parameters. Literal parameters allow you to reference columns or
|
||
other SQL literals. Bound parameters can be used to safely add user data to SQL functions. For example:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
// Generates:
|
||
// SELECT CONCAT(title, ' NEW') ...;
|
||
$concat = $builder->func()->concat([
|
||
'title' => 'literal',
|
||
' NEW'
|
||
]);
|
||
$query->select(['title' => $concat]);
|
||
|
||
|
||
Getting Results out of a Query
|
||
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Once you’ve made your query, you’ll want to retrieve rows from it. There are a few ways of doing this:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
// Iterate the query
|
||
foreach ($builder as $row) {
|
||
echo $row['title'];
|
||
}
|
||
|
||
// Get the statement and fetch all results
|
||
$results = $builder->execute()->fetchAll('assoc');
|
||
|
||
|
||
Creating an Insert Query
|
||
~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Creating insert queries is also possible:
|
||
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
$builder = $this->getQueryBuilder();
|
||
$builder
|
||
->insert(['first_name', 'last_name'])
|
||
->into('users')
|
||
->values(['first_name' => 'Steve', 'last_name' => 'Jobs'])
|
||
->values(['first_name' => 'Jon', 'last_name' => 'Snow'])
|
||
->execute();
|
||
|
||
|
||
For increased performance, you can use another builder object as the values for an insert query:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
|
||
$namesQuery = $this->getQueryBuilder();
|
||
$namesQuery
|
||
->select(['fname', 'lname'])
|
||
->from('users')
|
||
->where(['is_active' => true]);
|
||
|
||
$builder = $this->getQueryBuilder();
|
||
$st = $builder
|
||
->insert(['first_name', 'last_name'])
|
||
->into('names')
|
||
->values($namesQuery)
|
||
->execute();
|
||
|
||
var_dump($st->lastInsertId('names', 'id'));
|
||
|
||
|
||
The above code will generate:
|
||
|
||
.. code-block:: sql
|
||
|
||
INSERT INTO names (first_name, last_name)
|
||
(SELECT fname, lname FROM USERS where is_active = 1)
|
||
|
||
|
||
Creating an update Query
|
||
~~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Creating update queries is similar to both inserting and selecting:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
$builder = $this->getQueryBuilder();
|
||
$builder
|
||
->update('users')
|
||
->set('fname', 'Snow')
|
||
->where(['fname' => 'Jon'])
|
||
->execute();
|
||
|
||
|
||
Creating a Delete Query
|
||
~~~~~~~~~~~~~~~~~~~~~~~
|
||
|
||
Finally, delete queries:
|
||
|
||
.. code-block:: php
|
||
|
||
<?php
|
||
$builder = $this->getQueryBuilder();
|
||
$builder
|
||
->delete('users')
|
||
->where(['accepted_gdpr' => false])
|
||
->execute();
|