Parsing of view definitions in MySQL

Hi Chris,

there seems to be still something fishy with the paranthesis behaviour of the parser. I need to extract the names of the involved database tables of view definitions in MySQL 5.x (see DB table information_schema.views, there column view_definition). These definitions look a bit "over-bracketed", for example:

select `bk`.`pos` AS `pos`,`bk`.`nummer` AS `nummer`,`bk`.`bonauftrag` AS `bonauftrag` from (`ssfest`.`Bonkopf` `bk` left join `ssfest`.`StornierteBons` `sb` on(((`bk`.`pos` = `sb`.`pos`) and (`bk`.`nummer` = `sb`.`nummer`)))) where (isnull(`sb`.`pos`) and isnull(`sb`.`nummer`))

A very similar thing happens also to functions in the column descriptor; they will look like:

select (if(isnull(x),y,z)) from ...

The parser then stops at the opening bracket with a parse exception. I tried to help myself with something like:

function removeBrackets($str) {
if (preg_match('/;$/', $str))
$str = substr($str, 0, -1);

while (preg_match('/^\((.*)\)$/', $str))
$str = substr($str, 1, -1);

return $str;
}

$something = "[\(\)\w\.=!` <>]*?";
$pattern = "^(.*?) from (".$something.")(?: (where) (.*)| (order by) (.*)| (having) (.*)| (group by) (.*)|;)$";
preg_match("/$pattern/i", $sqldef, $matches);

$sql = $matches[1]." from ".removeBrackets($matches[2])." ".$matches[3]." ".removeBrackets($matches[4]);
var_dump($sql);

$sqlDef = new Sql();
$parsedSQL = $sqlDef->parse($sql);

but this does not resolve all of the problems. Do think it is possible to relax the parser in this regard a bit such that the exagerated paranthsis are being ignored? In fact, they are not wrong, and it is a valid SQL statement. Yes, you are right - they are totally superflous and every time I need to rewrite a view statement, I am almost going nuts on them as well. I don't know what was on the MySQL's guys mind when they introduced them, but obviously they had some very good reasoning to do so.

Thanks for your feedback!

Kind regards
John