Sql – get the table names from an SQL query with Perl’s DBI

dbiperlsqlite

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.

Best Answer

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.

Related Topic