Can I get the table names from an SQL query with Perl's DBI?

Question

I am writing small snippets in Perl and DBI (SQLite yay!)

I would like to log some specific queries to text files having the same filename as that of the table name(s) on which the query is run.

Here is the code I use to dump results to a text file :

sub dumpResultsToFile {
    my ( $query ) = @_;

    # Prepare and execute the query
    my $sth = $dbh->prepare( $query );
    $sth->execute();

    # Open the output file
    open FILE, ">results.txt" or die "Can't open results output file: $!";

    # Dump the formatted results to the file
    $sth->dump_results( 80, "\n", ", ", \*FILE );

    # Close the output file
    close FILE or die "Error closing result file: $!\n";
}

Here is how I can call this :

dumpResultsToFile ( <<"    END_SQL" );
    SELECT TADA.fileName, TADA.labelName
    FROM   TADA
    END_SQL

What I effectively want is, instead of stuff going to "results.txt" ( that is hardcoded above ), it should now go to "TADA.txt".

Had this been a join between tables "HAI" and "LOL", then the resultset should be written to "HAI.LOL.txt"

Is what I am saying even possible using some magic in DBI?

I would rather do without parsing the SQL query for tables, but if there is a widely used and debugged function to grab source table names in a SQL query, that would work for me too.

What I want is just to have a filename that gives some hint as to what query output it holds. Seggregating based on table name seems a nice way for now.

Answer 1

Why can't you just extract the table name from the query?

$query =~ /FROM\s+(\w+)/;
my $table_name = $1;

Answer 2

Probably not. Your SQL generation code takes the wrong approach. You are hiding too much information from your program. At some point, your program knows which table to select from. Instead of throwing that information away and embedding it inside an opaque SQL command, you should keep it around. Then your logger function doesn't have to guess where the log data should go; it knows.

Maybe this is clearer with some code. Your code looks like:

sub make_query {
    my ($table, $columns, $conditions) = @_;
    return "SELECT $columns FROM $table WHERE $conditions";
}

sub run_query {
    my ($query) = @_;
    $dbh->prepare($query);
    ...
}

run_query( make_query( 'foo', '*', '1=1' ) );

This doesn't let you do what you want to do. So you should structure your program to do something like:

sub make_query {
    my ($table, $columns, $conditions) = @_;
    return +{
        query => "SELECT $columns FROM $table WHERE $conditions",
        table => $table,
    } # an object might not be a bad idea
}

sub run_query {
    my ($query) = @_;

    $dbh->prepare($query->{query});
    log_to_file( $query->{table}.'.log', ... );

    ...
}

run_query( make_query( 'foo', '*', '1=1' ) );

The API is the same, but now you have the information you need to log the way you want.

Also, consider SQL::Abstract for dynamic SQL generation. My code above is just an example.

Edit: OK, so you say you're using SQLite. It has an EXPLAIN command which you could parse the output of:

sqlite> explain select * from test;
0|Trace|0|0|0|explain select * from test;|00|
1|Goto|0|11|0||00|
2|SetNumColumns|0|2|0||00|
3|OpenRead|0|2|0||00|
4|Rewind|0|9|0||00|
5|Column|0|0|1||00|
6|Column|0|1|2||00|
7|ResultRow|1|2|0||00|
8|Next|0|5|0||00|
9|Close|0|0|0||00|
10|Halt|0|0|0||00|
11|Transaction|0|0|0||00|
12|VerifyCookie|0|1|0||00|
13|TableLock|0|2|0|test|00|
14|Goto|0|2|0||00|

Looks like TableLock is what you would want to look for. YMMV, this is a bad idea.

Answer 3

In general, in SQL, you cannot reliably deduce table names from result set, both for theoretical reasons (the result set may only consist of computed columns) and practical (the result set never includes table names - only column names - in its data).

So the only way to figure out the tables used is to stored them with (or deduce them from) the original query.

Answer 4

I've heard good things about the parsing ability of SQL::Statement but never used it before now myself.

use SQL::Statement;
use strict;
use warnings;

my $sql = <<"    END_SQL";
    SELECT TADA.fileName, TADA.labelName
    FROM   TADA
    END_SQL
my $parser = SQL::Parser->new();
$parser->{RaiseError} = 1;
$parser->{PrintError} = 0;
my $stmt = eval { SQL::Statement->new($sql, $parser) }
    or die "parse error: $@";
print join',',map{$_->name}$stmt->tables;