generate-where-bind.pm

# $self->query_to_where({name => "Bruce"}, {name => "eq"})   => ["name = ?"], ["Bruce"]
# $self->query_to_where({name => "Bruce"}, {name => "like"}) => ["name LIKE '%?%'"], ["Bruce"]
# $self->query_to_where({age => ">=42"},   {age => "num"})   => ["name >= ?"], [42]
sub query_to_where {
  my ($self, $q, $rules) = @_;
  my (@where, @bind);

  for my $col (sort keys %$rules) {
    if (!length $q->{$col} // '') {
      1;    # next
    }
    elsif ($rules->{$col} eq 'like') {
      my $v = $q->{$col};
      $v =~ s!\*!%!g;
      $v = sprintf '%%%s%%', $v unless $v =~ /\%/;
      push @where, sprintf '%s like ?', $col;
      push @bind, $v;
    }
    elsif ($rules->{$col} eq 'num') {
      $q->{$col} =~ /^([<=>]+)?(\w+)/;
      push @where, sprintf '%s %s ?', $col, $1 || '=';
      push @bind, $2;
    }
    else {
      push @where, sprintf '%s = ?', $col;
      push @bind, $q->{$col};
    }
  }

  return \@where, \@bind;
}

# $self->query_from_string("foo>=bar  what ever") => {foo => ">=bar", _ => "what ever"}
# $self->query_from_string("foo=bar what ever  ") => {foo => "bar",   _ => "what ever"}
sub query_from_string {
  my ($self, $str) = @_;
  my %q;

  while ($str =~ s/(?<!['"\*\%])(\w+)([<=>])(\S+)//) {
    $q{$1} = "$2$3";
    $q{$1} =~ s!^=!!;
  }

  $str =~ s!^\s*!!;
  $str =~ s!\s*$!!;
  $str =~ s!["']$!! if $str =~ s!^["']!!;
  $q{_} = $str;
  return \%q;
}