A little more about migrations. PHP version

Freely quoting the introduction to the relevant article on RailsGuides,

Migrations are a convenient way to manage the structure and changes of the database schema.

Of course, you can do business the old-fashioned way, using many SQL files, or, oh, horror !, editing pieces of SQL code in one large file, which is the actual database schema.

However, following these changes, starting from a certain point, it becomes very difficult, not to mention the application of the corresponding changes on the production machine: here you need to have the skill of a cheetah, the strength of a bear and the wisdom of all the Eastern sages combined to make everything right do not drop.
But what if you do not possess any of the above qualities? That's right, you need to systematize and automate the process, shifting most of the work to the machine.

If you are already interested, or are still not sure, but the prospect of having a transparent history of changes and the ability to roll back to any version of the scheme using one or two console commands sounds tempting, I ask under the cat.

Having started working with Ruby on Rails, you quickly get to know the migration mechanism, and after a while you don’t understand how you could do without this incredibly convenient tool.
Having come to a project that was developed in PHP, I tried to bring into it at least a minimal set of useful tools, familiar from the experience of communicating with Ruby on Rails. One of the points was a system to support migrations.
After some searching, the choice was made in favor of Ruckusing Migrations , as most similar to what I saw in the tracks. I report that after more than half a year the flight is normal!
')

Installation

It is assumed that you use Composer to manage dependencies. If not, be sure to try, it's worth it!
Also, if you wish, you can clone the repository with the GitHub example: github.com/ArtemPyanykh/php_migrations_example

First, add to your composer.json:

"require" : { "ruckusing/ruckusing-migrations": "dev-master" }

and follow

 ~/dev/php_migrations_example(master)$ php composer.phar install

Composer will pull up Ruckusing Migrations and install into the vendors directory.

Next, I advise you to do the following:

First, create the following directory structure:
```
 . └── db ├── logs ├── migrations │  ├── main │  ├── php_migrations_example -> main/ │  └── php_migrations_example_test -> main/ └── utility 
```
Your migrations, configs and other useful things will be stored in the db directory. Consider this directory a point of reference for everything related to migrations.
The migration files themselves will be stored in db/migrations/main .
php_migrations_example and php_migrations_example_test are the names of the development and test databases.
Directories with corresponding names are just symbolic links to the main directory, since you are unlikely to have different migrations for different environments.

Create a db/ruckus with the following contents:

 #!/bin/bash ruckus_dir="./../vendor/ruckusing/ruckusing-migrations" if [ ! -d $ruckus_dir ]; then echo "Ruckusing-Migrations wasn't detected. Please, use Composer to install the library." exit fi if [ ! -f "ruckusing.conf.php" ]; then echo "Ruckusing conf. file wasn't detected. Please, create proper ruckusing.conf.php." exit fi if [ "$#" -lt 1 ]; then echo "At least 1 argument required" echo "See ./ruckus --help" exit fi if [ "$1" == "--help" -o "$1" == "-h" ]; then echo "Usage: ./ruckus [--help] <task-name> [<parameter1>[ <parameter2> [...]]]" echo "" echo "The available ruckus commands are:" echo " ./ruckus db:migrate Ruckus scenario for tasks such as db:migrate, db:setup, etc." echo " ./ruckus db:generate <migration_name> Ruckus scenarion for generating migration scaffolding" echo "" exit fi php $ruckus_dir"/ruckus.php" "$1" "${@:2}"

This is just a small script that I allowed myself to write in order to simplify working with Ruckusing Migrations.

Finally, create a db/ruckusing.conf.php config file:

 <?php //---------------------------- // DATABASE CONFIGURATION //---------------------------- /* Valid types (adapters) are Postgres & MySQL: 'type' must be one of: 'pgsql' or 'mysql' */ return array( 'db' => array( 'development' => array( 'type' => 'mysql', 'host' => 'localhost', 'port' => 3306, 'database' => 'php_migrations_example', 'user' => 'root', 'password' => 'root' ), 'test' => array( 'type' => 'mysql', 'host' => 'localhost', 'port' => 3306, 'database' => 'php_migrations_example_test', 'user' => 'root', 'password' => 'root' ) ), 'migrations_dir' => RUCKUSING_WORKING_BASE . '/migrations', 'db_dir' => RUCKUSING_WORKING_BASE . '/utility', 'log_dir' => RUCKUSING_WORKING_BASE . '/logs' ); ?>

Everything, no more settings are required, you have successfully integrated your own migration system!

Using

In general, everything is very simple. Let's start by generating the migration:

 ~/dev/php_migrations_example(master)$ cd db ~/dev/php_migrations_example/db(master)$ ./ruckus db:generate CreateTestTable Created migration: 20130508145210_CreateTestTable.php

You may notice that in the db / migrations / main directory after this, a file with approximately the same name will be added (the timestamp will be different) with the following content:

 <?php class CreateTestTable extends Ruckusing_Migration_Base { public function up() { }//up() public function down() { }//down() }

Migrations have the property that they can be not only applied but also canceled if there is an adequate way to roll back the changes. This is exactly the semantics of the methods up () (applying changes) and down () (rollback changes). Let's create a test table with several fields and a pair of indexes. Complement the file as follows:

 <?php class CreateTestTable extends Ruckusing_Migration_Base { public function up() { $table = $this->create_table('test', array('options' => 'ENGINE=InnoDB DEFAULT CHARSET=utf8')); $table->column('this', 'integer', array('unsigned' => true, 'null' => false, 'default' => '42')); $table->column('that', 'string', array('limit' => '7')); $table->column('those', 'datetime'); $table->finish(); $this->add_index('test', array('this', 'that'), array('unique' => true)); }//up() public function down() { $this->drop_table('test'); }//down() }

and run the migrations:

 ~/dev/php_migrations_example/db(master)$ ./ruckus db:migrate Started: 2013-05-08 7:05pm MSK [db:migrate]: Schema version table does not exist. Auto-creating. Creating schema version table: schema_migrations Migrating UP: ========= CreateTestTable ======== (0.31) Finished: 2013-05-08 7:05pm MSK

You will notice that in addition to creating the test table, which fully complies with the specification described above, the schema_migrations table has also been created. This is normal - it is here that Ruckusing Migrations stores information about which migrations were applied and which were not.

You can also just roll back or start migrations for another environment:

 ~/dev/php_migrations_example/db(master)$ ./ruckus db:migrate VERSION=-1 Started: 2013-05-08 7:13pm MSK [db:migrate]: Migrating DOWN: ========= CreateTestTable ======== (0.21) Finished: 2013-05-08 7:13pm MSK ~/dev/php_migrations_example/db(master)$ ./ruckus db:migrate ENV=test Started: 2013-05-08 7:14pm MSK [db:migrate]: Schema version table does not exist. Auto-creating. Creating schema version table: schema_migrations Migrating UP: ========= CreateTestTable ======== (0.24) Finished: 2013-05-08 7:14pm MSK

This is just a small demonstration of the system's capabilities. However, I think it is already clear how much easier and more convenient is the versioning of the database using the correct migration mechanism.

Remarks

If you try to run the ruckus script not from the db/ directory, you will get an error. This is due to the fact that all the paths in the script are relative, and if desired, this is easily corrected. It is necessary, however, to take into account one thing: by default, the config is searched in the working directory.
When applying migrations in production, you need to be very careful: if you have a fairly weighty table, say a few gigabytes, and you apply a migration to it, which somehow changes the scheme, most likely there will be trouble. Although this may not be a lack of migrations per se, but rather a lack of a DBMS, nevertheless it somewhat limits the possibilities of using the system. To update large tables, you need to use specialized tools, such as the Percona Toolkit .

A little more about migrations. PHP version

Installation

Using

Remarks

Links

More articles: