Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

SQL Parsing

by moo.mike.moo (Initiate)
on Jul 10, 2007 at 14:41 UTC ( [id://625835]=perlquestion: print w/replies, xml ) Need Help??

moo.mike.moo has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am writing a program that can find all columns and the tables on which they reside given SQL code. For example:
SELECT a, b FROM tbl WHERE c > 10 and d == 5
The program should return "a, b, c, and d from table tbl" or something along those lines. I'm using Jeff Zucker's SQL::Statement module but I can get only the columns and tables, not which tables the columns belong to. It also returns a and b but not c and d. However, it does a rather good job at parsing the SQL. Is there any way I can perhaps extend it to do what I want? Thanks in advance!

Replies are listed 'Best First'.
Re: SQL Parsing
by jZed (Prior) on Jul 10, 2007 at 15:17 UTC
    Actually, if I'm remembering my own code correctly, all of the information you want is returned by SQL::Parser, I just haven't supplied methods to access it easily. Use Data::Dumper and you'll see that columns are associated with tables and that the columns in the WHERE clause are listed in addition to the columns in the SELECT clause. It shouldn't be too hard to make methods to access that extra information and I would very happily accept a patch for that :-).
Re: SQL Parsing
by menolly (Hermit) on Jul 10, 2007 at 17:09 UTC

    In general, this isn't possible from SQL alone -- you have to look at the database.

    Given table foo containing columns (id, a, b) and table bar containing columns (id, c, d), what would you expect your program to return from this SQL?

    SELECT a, b, c, d from foo, bar where foo.id = bar.id;
    What about this?
    SELECT * from foo, bar where foo.id = bar.id;

    Of course, your codebase may not contain these types of statement.

      Good points. SQL::Statement only checks those kinds of things on execute(), not on prepare(). For example it dies on prepare() if you use a column name that isn't a valid SQL identifier but prepare() succeeds if you use a non-existant column name - it only finds that out later. For example, it errors if a column name exists in two tables and your SQL doesn't specigy which table the name refers to, but it only checks for that and dies on execute(). If you have the database available and wanted to use it to check the column names without actually executing anything you'd probably be able to just grab the parts of execute() that check the database tables for actual column names.

      update :Another option might be to create a hash structure of the table/columns you expect to encounter and tweak the return from SQL::Statement to fill in table information from that when the module was not able to get it from the SQL.

Re: SQL Parsing
by moo.mike.moo (Initiate) on Jul 11, 2007 at 15:26 UTC
    Hello!

    I tried to read all the code but because I am a beginner I was not able to understand it much. With SQL::Parser, Data::Dumper showed that things were parsed well on the sample commands I tried, however I don't understand how the data structures are designed so I don't know how to write methods to grab the relevant information. Can you please give a short explanation or just a list of the data structures (like where_cols, etc.)?

    Thanks a lot!

      Here ya go:
      #!/usr/bin/perl use warnings; use strict; use SQL::Statement; my $stmt = SQL::Statement->new(" SELECT a, b FROM tblA LEFT JOIN tblB WHERE c > 10 and tblB.d = 5 "); printf "Tables : %s\nSelect Cols : %s\nWhere Cols : %s\n", join( ', ', @ { $stmt->{org_table_names} } ), join( ', ', map {$_->display_name} $stmt->columns ), join( ', ', keys %{ $stmt->{where_cols} } ), ; # OUTPUT # Tables : tblA, tblB # Select Cols : a, b # Where Cols : tblB.d, c

      Note, that as menolly++ mentioned, columns are only associated with tables if explicitly specified in the SQL.

      And on a different (off) topic : Congratulations! You are customer number 1,000 at jZedHacksRus. Pick up your door prize on the way out.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://625835]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2024-03-28 21:09 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found