Support MySQL5.7

[SimonMellerin]

For now the DbToolsBundle does not work properly with MySQL5.7.

But as it is still common to encounter this version in the wild, it could be nice to fix issues we have and officially support this version.

For example for now, when I launch tests with this version I get this kind of errors:

1) MakinaCorpus\DbToolsBundle\Tests\Functional\Anonymizer\AnonymizatorTest::testMultipleAnonymizersAtOnce
Doctrine\DBAL\Exception\SyntaxErrorException: An exception occurred while executing a query: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS ```_db_tools_seq_table_test_get```() RETURNS BIGINT
DETERMINISTIC
' at line 1

/var/www/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:86
/var/www/vendor/doctrine/dbal/src/Connection.php:1943
/var/www/vendor/doctrine/dbal/src/Connection.php:1885
/var/www/vendor/doctrine/dbal/src/Connection.php:1213
/var/www/vendor/makinacorpus/query-builder/src/Bridge/Doctrine/DoctrineQueryBuilder.php:122
/var/www/vendor/makinacorpus/query-builder/src/Bridge/AbstractBridge.php:226
/var/www/src/Anonymization/Anonymizator.php:518
/var/www/src/Anonymization/Anonymizator.php:379
/var/www/tests/Functional/Anonymizer/AnonymizatorTest.php:95

Caused by
Doctrine\DBAL\Driver\PDO\Exception: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS ```_db_tools_seq_table_test_get```() RETURNS BIGINT
DETERMINISTIC
' at line 1

/var/www/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28
/var/www/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:39
/var/www/vendor/doctrine/dbal/src/Connection.php:1211
/var/www/vendor/makinacorpus/query-builder/src/Bridge/Doctrine/DoctrineQueryBuilder.php:122
/var/www/vendor/makinacorpus/query-builder/src/Bridge/AbstractBridge.php:226
/var/www/src/Anonymization/Anonymizator.php:518
/var/www/src/Anonymization/Anonymizator.php:379
/var/www/tests/Functional/Anonymizer/AnonymizatorTest.php:95

Caused by
PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS ```_db_tools_seq_table_test_get```() RETURNS BIGINT
DETERMINISTIC
' at line 1

/var/www/vendor/doctrine/dbal/src/Driver/PDO/Connection.php:33
/var/www/vendor/doctrine/dbal/src/Connection.php:1211
/var/www/vendor/makinacorpus/query-builder/src/Bridge/Doctrine/DoctrineQueryBuilder.php:122
/var/www/vendor/makinacorpus/query-builder/src/Bridge/AbstractBridge.php:226
/var/www/src/Anonymization/Anonymizator.php:518
/var/www/src/Anonymization/Anonymizator.php:379
/var/www/tests/Functional/Anonymizer/AnonymizatorTest.php:95

[pounard]

OK I fixed the CREATE FUNCTION statement, MySQL doesn't support IF NOT EXISTS and the return type cannot be BIGINT but must be INTEGER instead. Easy one.

Now, we have another much harder problem:

update `table_test`
inner join `table_test` as `_target_table`
    on (`table_test`.`_db_tools_id` = `_target_table`.`_db_tools_id`)
left outer join (
    select `value`,
    ROW_NUMBER() OVER (ORDER BY rand()) as `rownum`
    from `_db_tools_sample_662910cfef262`
    limit 3
) as `_db_tools_sample_662910cfef262_value`
    on (`_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910cfef262_value`.`rownum`
and `_target_table`.`value` is not null)
left outer join (
    select `iban`, `bic`,
    ROW_NUMBER() OVER (ORDER BY rand()) as `rownum`
    from `_db_tools_sample_662910d0090ed`
    limit 3
) as `_db_tools_sample_662910d0090ed_foo` on (
    `_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910d0090ed_foo`.`rownum`)
set
    `table_test`.`value` = `_db_tools_sample_662910cfef262_value`.`value`,
    `table_test`.`my_iban` = `_db_tools_sample_662910d0090ed_foo`.`iban`,
    `table_test`.`my_bic` = `_db_tools_sample_662910d0090ed_foo`.`bic
;

This cannot work because MySQL 5.7 has not implemented the WINDOW functions yet.

We could probably emulate it using this trick: https://stackoverflow.com/questions/1895110/row-number-in-mysql which would give then:

update `table_test`
inner join `table_test` as `_target_table`
    on (`table_test`.`_db_tools_id` = `_target_table`.`_db_tools_id`)
left outer join (
    select `value`,
    @rownum := @rownum + 1 AS `rownum`
    from `_db_tools_sample_662910cfef262`
    limit 3
) as `_db_tools_sample_662910cfef262_value`
    on (`_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910cfef262_value`.`rownum`
and `_target_table`.`value` is not null)
left outer join (
    select `iban`, `bic`,
    @rownum := @rownum + 1 AS `rownum`
    from `_db_tools_sample_662910d0090ed`
    limit 3
) as `_db_tools_sample_662910d0090ed_foo` on (
    `_target_table`.`_db_tools_id` % 3 + 1 = `_db_tools_sample_662910d0090ed_foo`.`rownum`)
set
    `table_test`.`value` = `_db_tools_sample_662910cfef262_value`.`value`,
    `table_test`.`my_iban` = `_db_tools_sample_662910d0090ed_foo`.`iban`,
    `table_test`.`my_bic` = `_db_tools_sample_662910d0090ed_foo`.`bic
;

Which would require to hack for MySQL 5.7 only.

Before we had one problem, now we have two, because the WINDOW function used here is generated by makinacorpus/query-builder and there is no easy solution for patching it, because ROW_NUMBER() OVER (ORDER BY rand()) cannot simply be emulated (it's a complex expression).

I guess we can target the exact place where we instanciate this expression in db-tools-bundle in order to replace it with a raw expression for MySQL 5.7, I have to test that and pray it works.

[pounard]

OK that should be easier than I thought, giving it a try now.

[pounard]

Well, all done! PR is waiting for approval.