Yii2: Creating migration from *.sql file

[m0onspell]

I wonder why there is no such feature in any framework. Let's say I want to bring some third-party module into my project, and there is *.sql file, which I should manually import to my database. Then I need to make some changes in the structure of imported tables, so I will create a new migration, define those changes and run my migration, and after some period of time - commit and push my changes to remote repository. When one of my collaborators will pull those changes to his local repo and try to run a new migrations - they would fail because they didn't imported *.sql file, so they don't have tables which I wanted to change in my migration.

[samdark]

It's up to you to execute SQL in a migration.

[m0onspell]

Oh... How could I miss that :)

[WadeShuler]

Sorry to dig an old topic, but my idea falls along these lines. I am not sure it's feasible, but it would be really nice.

I have client who has a Yii1 site. I am creating it fresh, on Yii2. He has a few tables, which store necessary data that needs to be re-imported just as it is. For sake of reason, it is formatting for each state and county, where each county has a different format for the numbers. There are 739 of them, they are a set of rules like: **-**-**.**-** or R**.*****-*

The property ID's are bounced on the rules based on the county, and formatted accordingly.

So either I just manually connect to the db myself and import the table and data, or I create a migration manually and add all 739 of them to the migration. While the 1st is the easiest, it would be an issue as the OP stated, other parties wouldn't have the SQL file. In my case, I am a 1 man show, so it isn't a big deal for me right now.

What would be nice, is the ability to include the SQL file in the migrations directory (which would/could be copied through git to the other parties). Then in the migration, reference the file, and import it.

Another option, would be a tool (like with GII) to parse the SQL file and convert it into a Yii2 migration file, or at least copy-n-paste code we can drop into our migration code.

If it is possible, I could create a new issue for the new enhancement idea. Just wanted to toss the idea out there before creating another issue.

[samdark]

Well, you can commit SQL file and run it in migration.

[diegotejadav]

@samdark, can you please tell me what's the way to execute a .sql file committed in my app through a migration?
Let's suppose it's on the web folder?

$this->execute('@web/sql/path-to-sql-file.sql');

[mikehaertl]

@diegotejadav In my projects I use this code in the safeUp() method:

        $sql = file_get_contents(__DIR__ . '/init.sql');
        $command = Yii::$app->db->createCommand($sql);
        $command->execute();

        // Make sure, we fetch all errors
        while ($command->pdoStatement->nextRowSet()) {}

[diegotejadav]

Nice solution @mikehaertl, I'll give it a try!

[diegotejadav]

@diegotejadav In my projects I use this code in the safeUp() method:

        $sql = file_get_contents(__DIR__ . '/init.sql');
        $command = Yii::$app->db->createCommand($sql);
        $command->execute();

        // Make sure, we fetch all errors
        while ($command->pdoStatement->nextRowSet()) {}

It worked perfectly @mikehaertl, thank you so much! 🙏

[1Luc1]

@diegotejadav In my projects I use this code in the safeUp() method:

        $sql = file_get_contents(__DIR__ . '/init.sql');
        $command = Yii::$app->db->createCommand($sql);
        $command->execute();

        // Make sure, we fetch all errors
        while ($command->pdoStatement->nextRowSet()) {}

Thanks for that.

I struggled with the execution of multiple queries within this command; probably caused from pgsql. So I used the following code:

        $sql = file_get_contents(__DIR__ . '/sql_create_pgsql.sql');
        Yii::$app->db->pdo->exec($sql);

See: https://github.com/yiisoft/yii2/issues/3744#issuecomment-45450174

[diegotejadav]

That's a good solution @1Luc1, thank you for sharing it!