Versioning the database structure in MySQL: MySQL Migration with PHP

When a project's database grows beyond three to five tables, while continuing to change constantly, the inconvenience of exchanging changes between developers is born. The problem is as old as the world, but I did not manage to find an instrument satisfying my requirements in November 2009.

My tool requirements are very simple:

• No matter how much I scoff at the data structure in the application, the tool should be able to change the structure in another installation of the application so that it is identical to mine.
• System requirements: PHP and MySQL - no more.
• Free
• Openness

')

Similar tools

Let's look at existing implementations. If I have missed something - write in the comments - I will be grateful, I will add to the article.

• Ruby on Rails - Extras dependence on RoR. UPD: details in the comments.
• Doctrine - as I understand it - you must first change the schema.yml, then generate the migration
• MySQL Migration Toolkit - requires Java installation (did not understand)
• MySQL Workbench - requires a graphical shell and a few mouse clicks, can generate alter scripts that can be automatically rolled up later.
• habrahabr.ru/blogs/php/63585 - article about Phing.
• code.google.com/p/mygrate - Python
• code.google.com/p/mysql-php-migrations - can generate empty classes of migrations, queries fit into them
• svn.limb-project.com/misc/migration - could not be started, the database connection parameters are hardcoded in all 3 or 4 scripts. How things are now do not know. I know that work was being done in this direction. Korchasa ! Would you comment?
• Paid Tools

Having considered all these options, I decided to create my own tool that would satisfy all these requirements. Two projects influenced my decisions when building a bicycle writing tool: code.google.com/p/mysql-php-migrations and svn.limb-project.com/misc/migration

What we can do:

• Work only in CLI mode.
• Create an initialization schema.
• Initialize the DB.
• Create a PHP class of migration, in which you no longer need to write requests with your hands - everything is there !!!
• Roll migration to a specific date.
• Roll back migration to a specific date (be careful, you can lose data forever)
• Show the list of available migrations by marking the current one.
• Store data on the database versioning in a table with a user-specified name.

What we do not know how:

1. Create ALTER-scripts - everything is stored inside classes.
2. Roll dumps and ALTER scripts.
3. Work with PDO - we need MySQLi.
4. Run for a beer.

What we have?

Only one config file

config.ini
host=localhost
user=root
password=
db=mmpi_test
savedir=db ;
verbose=On
versiontable=db_version ; -


A small library of code.

Only one executable file: ./migration.php

Several teams:

• help : Show HELP
• schema : Create an initialization schema.
• init : Download initialization schema (install database)
• create : Create a new migration
• list : Show a list of available migrations. The current is marked with three ***
• migrate : Migrate the database to the specified time or to the latest version, if the time is not specified

It's simple. You can experiment on a test base and get to work.

System requirements:

• PHP> = 5.3 with MySQLi
• MySQL> = 5.0 (I just did not try it on the four)
• The MySQL user must have rights to create the database.

What you need to know

The migrate command works with parameters that are recognized by the strtotime function. If no parameters are specified, the current time is taken. The name of the migration class, as well as the variable inside it stores the timestamp of its creation. The MySQL user should have the right to create a new database - the tool uses this when generating a new migration and when rolling in / away from the migrations, after work the script deletes the temporary database.

Work mechanism

When creating a migration: A temporary database is created, the schema.php is merged into it (there are initialization schema requests), then the migrations are sequentially merged into the most recent one. A snapshot array of each of the bases is removed, differences are identified, a new migration class is created. If you need any data manipulations during upgrade / downgrade, edit the class.
When applying migration : a list of migrations is read, the migration to which an upgrade / downgrade is needed is performed; all migrations from the current to the target one are sequentially performed.

Migration class : contains two arrays up and down, requests from which are sequentially executed when applying this migration in the corresponding direction.

Where to get?

hg clone bitbucket.org/idler/mmp

PS Well, here it is necessary to write that this is the alpha version! Please code for kicking, but not to death. Bugreport and funkvesty welcome. It works for me, but it does not mean that it will work for everyone.

PPS I'm thinking about the version for SQLite.