Use PHP in migration files (remove SQL statements)

[marcj]

While I'm trying out some fixes for the migration part I came over migration files generated from SQLite. I opened those files to see which stuff has been changed. Since SQLite doesn't support much ALTER TABLE queries we had unfortunately to develop a workaround with creating a temporarily table, move data, remove old, rename temporarily to final table etc.

Example:

PRAGMA foreign_keys = OFF;

CREATE TEMPORARY TABLE customer__temp__534c56d8dcc1c AS SELECT id,first_contest,second_contest,name,join_date FROM bookstore_schemas§customer;
DROP TABLE bookstore_schemas§customer;

CREATE TABLE bookstore_schemas§customer
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    first_contest INTEGER,
    second_contest INTEGER,
    name VARCHAR(255),
    join_date DATETIME,
    FOREIGN KEY (first_contest) REFERENCES contest§bookstore_contest (id),
    FOREIGN KEY (second_contest) REFERENCES contest§bookstore_contest (id)
);

INSERT INTO bookstore_schemas§customer (id, first_contest, second_contest, name, join_date) SELECT id, first_contest, second_contest, name, join_date FROM customer__temp__534c56d8dcc1c;
DROP TABLE customer__temp__534c56d8dcc1c;

CREATE TEMPORARY TABLE customer_account__temp__534c56d8dd962 AS SELECT customer_id,enabled,not_enabled,created FROM bookstore_schemas§customer_account;
DROP TABLE bookstore_schemas§customer_account;

Not really good to read or to extend. Basically, it's unable to review that migration file. This is what led me to the idea of having migration files based on PHP, not SQL. Actually the same approach as Phinx'

Since we already have model classes and model comparator we would only need to have a class that generates PHP calls based on a DatabaseDiff. Anything else is already on-board.

Example:

public function getUpSQL(Schema $schema)
{
    $database = $schema->getDatabase('bookstore');
    $database->removeTable('table_a');

    $database->getTable('table_b')->removeColumn('column_a');
    $database->getTable('table_b')->getColumn('column_b')->setType('float');

    $database->renameTable('table_c', 'customer');

    $schema->addSql('bookstore', array(
        'MY super CUSTOM sql HERE'
    ));
}

The change in the migration class would be marginal. We only pass a clone of our current $schema in the real database and see what has been changed by get*SQL (should be also renamed then) to the origin by calling DatabaseComparator at it. We generate in addition to the custom defined SQL all queries from the returning DatabaseDiff and thats it.

If someone prefers the old way of migration files I could make it optional available via command option.

Would max. 1 day work - I'd do it and schedule it to the first beta release if all agree. Any thoughts?

[marcj]

Another benefit would be that it's cross-database compatible as long as you don't use SQL queries. Makes it possible to fire migration files against different database vendors. Also migration files aren't worthless anymore when a project switches to a different database vendor.

[jaugustin]

👍

[gharlan]

nice idea 👍

[willdurand]

👍

[hhamon]

+1

[robin850]

A big 👍 here especially for the fact that migrations will be agnostic ; this is really cool when the DBMS used locally is no the same in production plus its easier to review/debug.

[mpscholten]

👍 but maybe we should still keep the "old" migration management, so it's easier to migrate from your propel1 project

[robin850]

maybe we should still keep the "old" migration management, so it's easier to migrate from your propel1 project

Sorry if I'm missing something but what would be the point ? If you are upgrading, your migrations have been run anyway.

[mpscholten]

In our vagrant-setup we're importing a static database backup every time we setup a new vagrant machine. After that we run our migrations over this backup so the dev-database is on the latest version. In case this isn't a common use case, we can just drop the old migration management. :-)

[marcj]

It will be backwards-compatible 👯

[mpscholten]

Nice 👍

[kosmodisk]

👍 It would be fantastic if there would be support for triggers and procedures and other objects too 👼

[motin]

+1

[spidfire]

Maybe nice to look into this project, sounds like this already has what you need https://github.com/lulco/phoenix
(No clue about stability)

[glensc]

For framework-agnostic migrator, I suggest phinx:

• https://phinx.org/

it's used in Eventum since 3.2.0 (2017-05-20):

• https://github.com/eventum/eventum/blob/5fa82d7b2b11a290d389ff77a0ed7b7c59f6a2db/CHANGELOG.md#320---2017-05-20
• phinx integration eventum/eventum#235

[marcj]

It doesn't make sense to use another library since Propel has already everything needed. It would be much much more work to remove that and use another library entirely, plus it would have a lot of duplicate code.