Versioning the database structure in MySQL: MySQL Migration with PHP

    When the project database grows beyond three to five tables, while continuing to constantly change, the inconvenience of sharing changes between developers is born. The problem is as old as the world, but in November 2009 I could not find a tool satisfying my requirements.

    My tool requirements are very simple:
    • No matter how I mock 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 - nothing more.
    • Free of charge.
    • Openness.

    Similar tools

    Let's look at existing tools. If I didn’t google something - write in the comments - I will be grateful, I will add it to the article.
    • Ruby on Rails - Add. dependence on RoR. UPD: details in the comments.
    • Doctrine - as I understand it - you need to modify schema.yml first, then generate the migration
    • MySQL Migration Toolkit - requires Java installation (not understood)
    • MySQL Workbench - requires a graphical shell and a few clicks of the mouse, can generate alter scripts, which can then be rolled automatically.
    • - article about Phing.
    • - Python
    • - knows how to generate empty migration classes, requests fit into them with your hands
    • - failed to start, database connection parameters are hardcoded in all 3 or 4 scripts. I don’t know how things are now. I know that work has been carried out in this direction. Korchasa ! Will you comment?
    • Paid Tools

    Having considered all these options, I decided to create my own tool that would satisfy all these requirements. Two decisions influenced my decisions when building a bike when writing a tool: and

    What we can do:

    • Only work in CLI mode.
    • Create an initialization circuit.
    • Initialize the database.
    • Create a PHP migration class, in which you no longer need to write queries manually - everything is there !!!
    • Roll migrations to a specific date.
    • Roll back migrations to a specific date (carefully, you can lose data permanently)
    • Show a list of available migrations by marking the current one.
    • Store database versioning data in a table with a user-defined 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?

    Just one config file

    config.ini A small code library. Just one executable file: ./migration.php
    savedir=db ; каталог для хранения классов миграций
    versiontable=db_version ; имя таблицы для хранения мета-данных о версионности

    Several teams:

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

    Everything is simple. You can experiment on a test base and get to work.

    System requirements:

    • PHP> = 5.3 with MySQLi
    • MySQL> = 5.0 (on the four I simply did not try)
    • The MySQL user must have permission 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 set, 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 must have rights to create a new database - the tool uses this when generating a new migration and when rolling / rolling migrations, after working, the script deletes the temporary database.

    Work mechanism

    When creating a migration: A temporary database is created, schema.php is poured into it (there are requests for an initialization scheme), then migrations are poured in turn to the very last one. The snapshot array of each database is removed, the differences are determined, a new migration class is created. If you need any data manipulation 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 determined, all migrations from the current to the target 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

    PS Well, here it should be written that this is an alpha version! Please kick for the code, but not to death. Bagreport and feature quests are welcome. It works for me, but that does not mean that it will work for everyone.

    PPS I'm thinking about the version for SQLite.

    Also popular now: