in reply to SQL Parsing with Parse::RecDescent

How would you proceed to get literals and join conditions from SQLs?

By running the program, and letting your database (or DBI) log the queries for you.

With your approach you won't find SQL that has interpolated variables, or that is dynamically created (think DBIx::Class).

Replies are listed 'Best First'.
Re^2: SQL Parsing with Parse::RecDescent
by DACONTI (Scribe) on Mar 05, 2008 at 13:52 UTC
    >>By running the program, and letting your database (or DBI) log the queries for you
    Do you mean report results from the tested ETL program itself?
    No I can't use this, because we test these programs at 1st development stage where we have no real productive data which matches their queries, so when we let them running they return
    ... no rows found ...
    But thanks for the advice, I'll have a look at DBIx::Class
    Davide.
[reply]
      You only need one row per table to be able to log many of the SQL statements.

      And let's try to be serious: which developer writes an application without having a single test data set, even if it's an inveted and not very natural one?

      Update: another idea is an incremental approach: Run the program once, and hope that it will run a SQL statement. Then generate some test data for that query, and run again.

      I just think that "blind" searching in source code for SQL statements will not work reliably, it will have both many false positives and false negatvies.

[reply]
        Hi moritz
        You only need one row per table to be able to log many of the SQL statements.
        And thats already a difficulty if your sql query is 3-4 pages long.
        Of course you can do the job manually by adding here and there in the tables proper values by hand, but this approach is tedious and it can take hours just to fill a few rows.
        Saluti, Davide
[reply]

In Section Seekers of Perl Wisdom