Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

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]
[holli]: i was just thinking of the bare content.
[LanX]: Plenty of "templates" realised with CGI-HTML-function
[LanX]: well if you want you can fetch all XML and translate it to another board markup
[moritz]: at this time, a migration could consist of only migrating the rendered HTML of nodes
[moritz]: to a forum software that supports raw HTML input for legacy threads, and uses sane markup for everything else
[holli]: yeah, then vroom will personally stab me in the back at night
[holli]: exactly, moritz
[moritz]: with a big table of redirects to support the old links
[LanX]: do it
[holli]: well if you put some cleverness into the migration you can auto convert most of the links as well

How do I use this? | Other CB clients
Other Users?
Others imbibing at the Monastery: (10)
As of 2017-11-20 19:31 GMT
Find Nodes?
    Voting Booth?
    In order to be able to say "I know Perl", you must have:

    Results (292 votes). Check out past polls.