[Request] Compile back result array to sql statement

[GoogleCodeExporter]

After some modification on the array.

Original issue reported on code.google.com by saku...@gmail.com on 26 Aug 2011 at 4:31

[GoogleCodeExporter]

I too would really like this functionality.  It seems like it would be a lot 
easier to implement, because it's a matter of pulling the stuff back together 
and inserting missing keywords.

Also, it might be possible to implement something with 
"PREG_SPLIT_OFFSET_CAPTURE" option, which would allow capturing the indexes of 
the original keyword locations.  So once you have the index, you can find and 
replace specific data elements.  I'm trying to implement this solution myself 
for my own usage - I need to rename table aliases in a query on-the-fly.

I'll post updates to my attempts to implement here.

Original comment by kbacht...@gmail.com on 20 Oct 2011 at 3:55

[GoogleCodeExporter]

Attempting to use PREG_SPLIT_OFFSET_CAPTURE requires many, many changes to 
handle all the tokens now being arrays instead of strings.  Nix on that route.

Reverse parsing, i.e., combining looks to be a promising route.  I have a 
proto-type concept begun here which works for really base INSERT and SELECT 
statements (no FROM yet or any other clause).  Would basically need to be 
completed for each clause by reverse-engineering the parsing process.

function combine($result) {
    $combine = '';
    foreach ($result as $key => $value)
        if ($key == "INSERT") {
            $table = $value['table'];
            $cols = $value['cols'];

            $combine.= 'INSERT INTO ';
            $combine.= '`'.$table.'`';
            if (is_array($cols))
                $combine.=' (`'.join('`, `', $cols).'`) ';
            else
                $combine.=' ';
        } else if ($key == "VALUES") {
            $combine.= 'VALUES '.join('', $value);
        } else if ($key == "SELECT") {
            $combine.='SELECT ';
            $combine.=combine_select($value);
        }

    var_dump($combine);
    return $combine;
}

function combine_select($arr) {
    $combine='';
    $first=true;
    foreach ($arr as $fieldref) {
        $expr_type = $fieldref['expr_type'];
        $base_expr = trim($fieldref['base_expr']);
        $alias = $fieldref['alias'];
        $sub_tree = $fieldref['sub_tree'];
        if (!$first) $combine.=', ';
        if (!$sub_tree) {
            $combine.=$base_expr;
        } else {
            $combine.=combine_subtree($sub_tree);
        }
        $combine.=' AS '.$alias;
        $first=false;
    }
    return $combine;
}

function combine_subtree($subtree) {
    $combine='';
    foreach ($subtree as $fieldref) {
        $expr_type = $fieldref['expr_type'];
        $base_expr = trim($fieldref['base_expr']);
        $sub_tree = $fieldref['sub_tree'];
        if (!$sub_tree)
            $combine.=$base_expr;
        else
            $combine.=combine_subtree($sub_tree);
    }
    return $combine;
}

Original comment by kbacht...@gmail.com on 20 Oct 2011 at 4:48

[GoogleCodeExporter]

However, this method will have a number of side-effects.  First off, the query 
will have a lot of changes from the original, even if all you do is parse and 
re-combine without any changes.  This is because the parser removes a lot of 
contextual information, such as which column names were backticked, spacing of 
the SQL code, etc.  Also, there look to be a few miscellaneous bugs that will 
have to be resolved (such as select * showing up as `*` being the alias for an 
operator *).  If these things are okay, then this is a promising route - would 
be a lot easier if the original author of the code would step in and build 
something like this :-)

Original comment by kbacht...@gmail.com on 20 Oct 2011 at 4:51

[GoogleCodeExporter]

Maybe it is possible with the new version, because the output tree contains 
more information. I use the parser to translate the MySQL dialect into an 
Oracle dialect, at the moment I use the new "position" to replace some parts 
within the original statement. It could be simpler to create a complete new 
statement.

Original comment by pho...@gmx.de on 2 Feb 2012 at 8:30

[GoogleCodeExporter]

I have created a builder called php-sql-creator.php Maybe it is useful for you. 
I think, there can be a problem on expressions like "(a or b) and c". The 
parser doesn't save the parenthesis in the current version, so the re-created 
SQL can be wrong on that point.

Check it out from 
https://www.phosco.info/publicsvn/php-sql-parser/tags/20120215 or try the 
bleeding edge on trunk.

Original comment by pho...@gmx.de on 15 Feb 2012 at 4:12

[GoogleCodeExporter]

Can I close this issue (try the phpsqlcreator class)?

Original comment by pho...@gmx.de on 13 Mar 2012 at 12:07

• Changed state: Started
• Added labels: Type-Enhancement
• Removed labels: Type-Defect

[GoogleCodeExporter]

Use PhPSQLCreator class to create an SQl statement from the parser result.

Original comment by pho...@gmx.de on 21 Mar 2012 at 11:23

• Changed state: Fixed