SQL Migration with variable

[trejjam]

Hi, is it possible execute SQL migration with variable?

For example:

SELECT @adminRoleId := id
FROM `user_role`
WHERE `name` = 'administrator';

INSERT INTO `user_role` (`parent_id`, `name`)
VALUES (@adminRoleId, 'faq');

It will fail, because N/Migrations exec single queries from file.

Is there some workaround or solution?

[hrach]

Well, I'm not sure how this works with http://php.net/mysqli_query. If you can put both queries at once as one call, then possible workaround is to use php handler and manually call this query. But, I'm afraid this is not possible from php side. Or, am I wrong? Yes, I was wrong :(

[trejjam]

I was use Kdyby/Doctrine connection in migration. If i switch to Nette/BD it works well.

Funny is they both share PDOConnection from Doctrine.

If I call manually:

/** @var Kdyby\Doctrine\Connection $connection */
$connection = $em->getConnection();

$connection->exec('SELECT @adminRoleId := id
FROM `user_role`
WHERE `name` = \'administrator\';

INSERT INTO `user_role` (`parent_id`, `name`)
VALUES (@adminRoleId, \'faq\');');

It is OK

---

$connection = $em->getConnection();

$connection->exec('SELECT @adminRoleId := id
FROM `user_role`
WHERE `name` = \'administrator\';');
$connection->exec('INSERT INTO `user_role` (`parent_id`, `name`)
VALUES (@adminRoleId, \'faq\');');

This will fail

Is it possible introduce some mechanism in Nextras\Migrations\Drivers\BaseDriver::loadFile to not explode input from file into multiple queries?

[JanTvrdik]

Partially related is #52

[JanTvrdik]

But IMHO solution to your problem is to set session-level variable.

[JanTvrdik]

Maybe https://dev.mysql.com/doc/refman/5.7/en/user-variables.html will help.

[trejjam]

Thanks!
I was thinking that SET @foo = and SELECT @foo := are equal.

Rewrite migration into

SET @adminRoleId = (SELECT id
FROM `user_role`
WHERE `name` = 'administrator');

INSERT INTO `user_role` (`parent_id`, `name`)
VALUES (@adminRoleId, 'faq');

Work as expected => done without fail
(under nette and doctrine DBAL with MySQL)