Phinx: Idea: Allow to execute plain sql files instead of php migration

[georgwaechter]

What do you think about the following idea: Some users may not need the undo feature, so it would suffice for them to have the ability to store and execute plain sql files?

Detailed suggestions:

1. Typing phinx create MyNewMigration.sql would create a YYYYMMDDHHMMSS_my_new_migration.sql file
2. The phinx rollback command should stop when reaching a sql file
3. It should be allowed to mix php and sql migrations within one project

Georg

[robmorgan]

Hi @georgwaechter if you really want to execute plain SQL files I'd suggest using the DBDeploy task shipped with Phing. This is what I used for year before I wrote Phinx. You can use the execute and query methods in Phinx if you really want to run raw SQL or you could read in a SQL file inside your migration. Phinx is primarily focused on a PHP API.

[georgwaechter]

Ok, thanks for the hint.

[robmorgan]

I am getting a lot of requests for this feature. It might be time to reconsider. Must haves for me though are rollback support and also stressing to the user that their migrations will most likely not be portable between database vendors.

[dkmuir]

This should work as a work-around for me:

public function up() 
{
    $this->execute(strstr(file_get_contents('<timestamp>_some_migration.sql'), "--//@UNDO", true);
}

public function down() 
{
    $this->execute(strstr(file_get_contents('<timestamp>_some_migration.sql'), "--//@UNDO");
}

And in your SQL you could have:

INSERT INTO sometable (id, value) VALUES (1,1);
--//@UNDO
DELETE FROM sometable WHERE id = 1;

[zacek]

You workaround has 2 limitations:
1) PDO will execute only the first query if you put more of them above/below the UNDO line. You should split the sections to single statements (problems with multiline statements parsing etc.)
2) very useful mysql binary internals like DELIMITER, SOURCE etc. are not supported in PDO, so it would sometimes be difficult to create e.g. stored procedures. Thus, it is not fully compatible with piping the sql to the mysql binary.

[smilesrg]

Still not resolved?

[davidjdixon]

There's nothing to resolve here, this is a feature request, not a bug report.

[marcin-jozwikowski]

I've figured out some kind of workaround of this problem.

$options = $this->adapter->getOptions();
exec("mysql -u {$options['user']} -p{$options['pass']} < ".str_replace(".php", ".sql", __FILE__));

will execute pure SQL from file named just like migration file only with .sql extension.
It's far away from being perfect but it allows to bypass PDO and create triggers and procedures.
Use at your own risk.

[rquadling]

Using the --class option, I have a ByDirectory and ByDirection class that does just that. ByDirection is expecting an up.sql/down.sql, ByDirectory is expecting an up and down directory with the contents being SQL files executed in alphabetic order (so 01 - Drop Table.sql, 02 - Create Table.sql sort of thing).

By having individual SQL files, you can also get around the issue of a failure in a batch of SQL statements not being able to be captured as an exception. I think this is a PDO/Mysql issue and not Phinx.

Anyhow.

Send my your email and I'll send you what I can.

[aalexgabi]

@zacek I just did a test and it seems that multiple queries have been executed. You are right that there are still limitations with advanced features in SQL files but @dkmuir solution may work for most use cases as a workaround.

Anybody knows why this works?

Query:

$this->execute('CREATE TABLE `ph0` (
            `id` int(10) NOT NULL,
            `patterns` text,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        CREATE TABLE `ph1` (
            `id` int(10) NOT NULL,
            `patterns` text,
            PRIMARY KEY (`id`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8');

And then:

mysql> select * from ph0;
Empty set (0.00 sec)

mysql> select * from ph1;
Empty set (0.00 sec)

[othercorey]

Closing due to lack of activity or out of scope. If there is new interest, please open a PR.