The SQL parser seems to analyse incorrectly the alter queries designed to add a new foreign key to a table.
After using API to analyse a request as follows :
$parsed_sql = PMA_SQP_parse($myQuery);
$sql_info = PMA_SQP_analyze($parsed_sql);
The sample query is :
ALTER TABLE `local_table2`
ADD CONSTRAINT `fk_local_table_foreign_table` FOREIGN KEY (`foreign_table_id_foreign_table`) REFERENCES `foreign_table2` (`id_foreign_table`) ON DELETE NO ACTION ON UPDATE NO ACTION;
The $sql_info contains the description of the ALTER, but a dump of the "foreign_key" part outputs :
[foreign_keys] => Array
(
[0] => Array
(
[constraint] => fk_local_table_foreign_table
[index_list] => Array
(
[0] => foreign_table_id_foreign_table
)
[ref_table_name] => id_foreign_table
[on_delete] => NO_ACTION
[on_update] => NO_ACTION
)
)
Note that the [ref_table_name] is the table key, it should contain in fact "foreign_table2"
After analysing the sqlparse.inc.php, I supposed this happens because ALTER queries have one less bracket level compared to CREATE queries.
I tried to correct the script,
On line 1758 I added :
if ($upper_data == 'ALTER') {
$seen_alter = TRUE;
}
On line I 1927 changed
if ( $brackets_level > 0) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
with :
if ($seen_alter && $brackets_level > 0) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
} elseif ($brackets_level > 1) {
$foreign[$foreign_key_number]['ref_index_list'][] = $identifier;
Now with the same query I have the following output :
[foreign_keys] => Array
(
[0] => Array
(
[constraint] => fk_local_table_foreign_table
[index_list] => Array
(
[0] => foreign_table_id_foreign_table
)
[ref_table_name] => foreign_table2
[ref_index_list] => Array
(
[0] => id_foreign_table
)
[on_delete] => NO_ACTION
[on_update] => NO_ACTION
)
)
Which is correct.
Is it the right way to correct this bug ? Is the bug due to some mis-use of the API ?
I am curious: where do you find a sqlparse.inc.php in phpMyAdmin?
Also, which action are you doing in phpMyAdmin that would trigger this bug?
oups my apologies, the file is :
libraries/sqlparser.lib.php
Actually I used the sqlparser api for another personal purpose, to analyse queries. However I assume this bug would appear if I tried to paste or import a sql query containing foreign keys in an "ALTER" sequence.
Ok, we also needed to initialize your $seen_alter to false. Patch accepted.
Merged in subversion, thanks.