Re: rewriting SQL

Replies are listed 'Best First'.
Re^2: rewriting SQL by locked_user sundialsvc4 (Abbot) on Jun 22, 2014 at 21:09 UTC
There are many parsers, including Parse::RecDescent, that can probably be made to do this job. (I’ve done things with that one that I was sure it couldn’t do ...) All of them, however, are grammar-driven general purpose parsing engines, vs. homebrew stuff that does not take that approach. A product like this one might be worth $150 to find out exactly how they did it, but it might be tangental to the task at hand. Their description strongly suggests that a C-capable parser engine is at the core of this product ... which probably means Lex/Yacc but not necessarily. It is not clear, though, whether they reveal all their secrets for less than $500, or even then. And I do wonder where their grammar actually did come from, since I see many online references to BNFs of various SQL dialects. In any case, I think – you’re going to have to find, or build, an honest-to-god grammar for your SQL, use it with an appropriate parser through a CPAN/Perl interface (there are several), and then do your actual comparisons by comparing parse-trees (or by getting clever with the exit-subroutines that you call from various key points within your grammar). I used this technique to rip-apart thousands of SAS scripts, Tivoli workload schedules, and Korn scripts to analyze what was a rather hosed-up production system that I didn’t write. It was an adventure.

Replies are listed 'Best First'.

Re^2: rewriting SQL
by locked_user sundialsvc4 (Abbot) on Jun 22, 2014 at 21:09 UTC

There are many parsers, including Parse::RecDescent, that can probably be made to do this job. (I’ve done things with that one that I was sure it couldn’t do ...) All of them, however, are grammar-driven general purpose parsing engines, vs. homebrew stuff that does not take that approach. A product like this one might be worth $150 to find out exactly how they did it, but it might be tangental to the task at hand. Their description strongly suggests that a C-capable parser engine is at the core of this product ... which probably means Lex/Yacc but not necessarily. It is not clear, though, whether they reveal all their secrets for less than $500, or even then. And I do wonder where their grammar actually did come from, since I see many online references to BNFs of various SQL dialects.

In any case, I think – you’re going to have to find, or build, an honest-to-god grammar for your SQL, use it with an appropriate parser through a CPAN/Perl interface (there are several), and then do your actual comparisons by comparing parse-trees (or by getting clever with the exit-subroutines that you call from various key points within your grammar).

I used this technique to rip-apart thousands of SAS scripts, Tivoli workload schedules, and Korn scripts to analyze what was a rather hosed-up production system that I didn’t write. It was an adventure.