Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot

Re: SQL queries from weird data

by choroba (Bishop)
on Feb 19, 2013 at 16:02 UTC ( #1019604=note: print w/replies, xml ) Need Help??

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%'
لսႽ ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ

Replies are listed 'Best First'.
Re^2: SQL queries from weird data
by Anonymous Monk on Feb 19, 2013 at 16:10 UTC
    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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1019604]
and God said, "Let Newton be!"...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (6)
As of 2018-04-20 20:34 GMT
Find Nodes?
    Voting Booth?