[ic] Nifty enhancement to [query ...]

[Peter Jakl]

FWIW, I wanted to pass on a nice enhancement that allows me to run any
command line utility to generate data in place of a "select ." statement
in the [query] tag. It's not very elegant but it solved my speed problem
when doing a keyword search against a large database of over 150,000
products.

In Vend::Table::DBI.pm the sub query () does all the work for the sql
param in the [query] tag. Ultimately, a standard DBI function is called
to retrieve the results, which is  $sth->fetchall_arrayref . It's easy
enough to bypass the $db->prepare and $sth->execute above this call, the
somewhat tricky part is to replace this with the data presented in the
same way, i.e. a reference to any array of rows.

Once I modified code to identify that I'm running a command, I call my
own fetch function as follows:

          myfetch($query);

where $query contains the contents of the sql='...' param. The myfetch()
function goes like this:

my @kwdata;
sub myfetch() {
    my $cmd = shift;
    undef @kwdata;
    open(KWCH, "$cmd |");
    while (<KWCH>) {
        chomp $_;
        push @kwdata, [ split(/\t/, $_) ];
    }
    close(KWCH);
    return \@kwdata;
}

For my purposes, the data was in a tab-delimited text file. There's a
little more to it when it comes to identifying column names, but it all
worked out very well. This allowed me to take advantage of the more and
list params using tags like:

[perl]
$n = 0;
$Scratch->{command} = "grep -fsku:description:price ";
for $kw (split(/\s+/, $CGI->{keywords}) {
	$n++;
	$Scratch->{command} .= "| grep " if $n > 1;
	$Scratch->{command} .= " \"$kw\" ";
	$Scratch->{command} .= "products/kwdata.txt " if $n == 1;
}
$Scratch->{command} .= "| cut -f1,3,4";
return "";
[/perl]
[query st=db list=1 more=1 ml=20 sql="[scratch command]"] 

In sub query(), the -f is extracted and parsed to produce the column
names. I use the grep utility on a Linux system for now. It improved the
speed on the same search from 25+ seconds down to just 2 or 3 seconds.

I'm sure there's a more elegant way to do this, but maybe it will
provide some help.

Peter Jakl
peter@jakl.net