Is there a module that can parse SQL statements into an object, then turn that object back into SQL? I've tried looking at SQL::Statement, which parses ok but which doesn't have any facility I can find for re-emitting SQL, and at SQL::Translator, which is all about parsing things in one format and emitting them in another, but only handles table definition stuff (CREATE, ALTER).
What I hope to achieve is to parse a bunch of SQL statements from logs, and change them into a canonical form so that I can compare logs of doing the same work with two different versions of my code and see more easily where they diverge. That'll include things like reordering lists of fields in ASCII order (eg for UPDATE statements, with corresponding reordering of the bind variables), and also spotting references to volatile fields such as session ids (randomly generated on each run) to minimize useless noise in the diffs.
I think my best way forward right now is to use SQL::Statement, and sort out the emitting myself (maybe as Data::Dumper output rather than SQL, as long as all I want to do is get a signature to compare), but I'd love to hear of an easier way: it sounds like the sort of useful thing that somebody would already have written.
Hugo
Update: SQL::Parser also seems to have some issues, not sure right now whether it'd be easier to fix those or handroll my own. The first line it barfs on if like "insert into t1 (col1, col2) values (now(), 1)", in which it splits the values on parens without checking for balancing: while ( $val_str =~ m/\((.+?)\)(?:,|$)/g ) { ... }, then tries to parse "now(" as a complete row of data to insert.
In reply to rewriting SQL by hv
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |