Re: SQL queries from weird data

[choroba]

in reply to SQL queries from weird data

For the examples you gave, the following works. However, if the actual values are even more complex, you might need to write a real parser for the expressions. For that, I would recommend Parse::RecDescent or Marpa::R2.

#!/usr/bin/perl
use warnings;
use strict;

use Test::More;

sub simple_value {
    my ($name, $val) = @_;
    my $return;
    if (not length $val) {
        $return = q();
    } elsif (1 + index $val, '|') {
        my @vals = split /\|/, $val;
        $return = "$name in(" . join(',' => map "'$_'", @vals) . ")";
    } elsif (1 + index $val, '%') {
        $return = "$name like '$val'";
    } else {
        return "$name='$val'";
    }
}


sub complex_value {
    my $value = shift;
    my @simple = split /&/, $value;
    return glue(map {
        my ($name, $val) = split /=/;
        simple_value(lc $name, $val);
    } @simple);
}


sub glue {
    join ' and ', grep length, @_;
}


while(not eof DATA) {
    chomp(my ($type, $stype, $other, $result) = (map scalar <DATA>, 1 
+.. 5));
    $type  = simple_value('type',  $type);
    $stype = simple_value('stype', $stype);
    $other = complex_value($other);
    is(glue($type, $stype, $other), $result, $result);
}
done_testing();


__DATA__
AAA
BBB
X=CCC
type='AAA' and stype='BBB' and x='CCC'

AA|BB
BB%
X=CC%&Y=DDD
type in('AA','BB') and stype like 'BB%' and x like 'CC%' and y='DDD'

AA|BB

X=CC%&Y=DDD
type in('AA','BB') and x like 'CC%' and y='DDD'

AA
BB%
X=CC%&Y=DD%
type='AA' and stype like 'BB%' and x like 'CC%' and y like 'DD%'

[Anonymous Monk]

Thank you for this reply. I will run this with some production data and report back. Thanks again. Have you any experience of the SQL::Abstract module? I'm looking at it right now and it claims to "Generate SQL from Perl data structures". Thank you once more.