Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Re: SQL queries from weird data

by choroba (Chancellor)
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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1019604]
help
Chatterbox?
[Corion]: marto: Oh ;) Well, I think you did right - more time, even if not spent with the kids directly, is still more time for you.
[Discipulus]: good morning monks! town holiday here.. but super busy
Discipulus time is not comarable with money.. when you have a roof and something to eat
[Corion]: Hi Discipulus! Busy making the town better? ;)

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (5)
As of 2017-06-29 08:34 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    How many monitors do you use while coding?















    Results (655 votes). Check out past polls.