The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
SQL-Abstract-FromQuery-0.02
River stage one • 1 direct dependent • 1 total dependent
3 ++
/ SQL::Abstract::FromQuery

NAME

SQL::Abstract::FromQuery - Translating an HTTP Query into SQL::Abstract structure

SYNOPSIS

  use SQL::Abstract::FromQuery;
  use SQL::Abstract; # or SQL::Abstract::More

  # instantiate
  my $parser = SQL::Abstract::FromQuery->new(
    -components => [qw/FR Oracle/], # optional components
    -fields => {                    # optional grammar rules for specific fields
        standard => [qw/field1 field2 .../],
        bool     => [qw/bool_field1/],
        ...  # other field types
     }
  );

  # parse user input into a datastructure for SQLA "where" clause
  my $criteria   = $parser->parse($hashref);
  # OR
  my $http_query = acquire_some_object_with_a_param_method();
  my $criteria   = $parser->parse($http_query);

  # build the database query
  my $sqla = SQL::Abstract->new(@sqla_parameters);
  my ($sql, @bind) = $sqla->select($datasource, \@columns, $criteria);

  # OR, using SQL::Abstract::More
  my $sqlam = SQL::Abstract::More->new(@sqla_parameters);
  my ($sql, @bind) = $sqlam->select(
    -columns => \@columns,
    -from    => $datasource,
    -where   => $criteria,
   );

DESCRIPTION

This module is intended to help building Web applications with complex search forms. It translates user input, as obtained from an HTML form, into a datastructure suitable as a %where clause for the SQL::Abstract module; that module will in turn produce the SQL statement and bind parameters to query the database.

Search criteria entered by the user can be plain values, lists of values, comparison operators, etc. So for example if the form filled by the user looks like this :

   Name   : Smi*              Gender  : M
   Salary : > 4000            Job     : ! programmer, analyst
   Birth  : BETWEEN 01.01.1970 AND 31.12.1990

the module would produce a hashref like

   { Name      => {-like => 'Smi%'},
     Gender    => 'M',
     Salary    => {'>' => 4000},
     Job       => {-not_in => [qw/programmer analyst/]},
     Birth     => {-between => [qw/1970-01-01 1990-12-31/]},
 }

which, when fed to SQL::Abstract, would produce SQL more or less like this

  SELECT * FROM people
  WHERE Name LIKE 'Smi%'
    AND Gender = 'M'
    AND Salary > 4000
    AND Job NOT IN ('programmer', 'analyst')
    AND Birth BETWEEN 1970-01-01 AND 1990-12-31

Form fields can be associated to "types" that specify the admissible syntax and may implement security checks.

Note : this module is in beta state. Many features still need further study; the API and/or behaviour may change in future releases; the current documentation is incomplete, so you have to look at the source code to get all details.

INPUT GRAMMAR

Input accepted in a form field can be

  • a plain value (number, string, date or time).

    Strings may be optionally included in single or double quotes; such quotes are mandatory if you want to include spaces or commas within the string. Characters '*' are translated into '%' because this is the wildcard character for SQL queries with 'LIKE'.

    Dates may be entered either as yyyy-mm-dd or dd.mm.yyyy; two-digit years are automatically added to 2000. The returned date is always in yyyy-mm-dd format.

  • a list of values, separated by ','. This will generate a SQL clause of the form IN (val1, val2, ...).

  • a negated value or list of values; negation is expressed by ! or != or - or <>

  • a comparison operator <=, <, >=, > followed by a plain value

  • the special word NULL

  • BETWEEN val1 AND val2

  • boolean values YES, NO, TRUE or FALSE

Look at the source code of this module to see the precise syntax, expressed in Regexp::Grammars format. Syntax rules can be augmented or modified in subclasses -- see "INHERITANCE" below.

METHODS

new

Constructs an instance. Arguments to the constructor can be :

-components

Takes an arrayref of components to load within the parser. Technically, components are subclasses which may override or augment not only the methods, but also the parsing grammar and error messages. Component names are automatically prefixed by SQL::Abstract::FromQuery::, unless they contain an initial '+'.

-fields

Takes a hashref, in which keys are the names of grammar rules, and values are arrayrefs of field names. This defines which grammar will be applied to each field (so some fields may be forced to be numbers, strings, bools, or any other kind of user-defined rule). If a field has no explicit grammar, the standard rule is applied.

INHERITANCE

[explain]

See SQL::Abstract::FromQuery::FR for an example.

Particular points :

  - may reuse rules from the parent grammar, but beware of action rules
  - do not use regex ops in action rules

AUTHOR

Laurent Dami, <laurent.dami AT justice.ge.ch>

BUGS

Please report any bugs or feature requests to bug-sql-abstract-fromquery at rt.cpan.org, or through the web interface at http://rt.cpan.org/NoAuth/ReportBug.html?Queue=SQL-Abstract-FromQuery. I will be notified, and then you'll automatically be notified of progress on your bug as I make changes.

SUPPORT

You can find documentation for this module with the perldoc command.

    perldoc SQL::Abstract::FromQuery

You can also look for information at:

SEE ALSO

Class::C3::Componentised -- similar way to load plugins in.

LICENSE AND COPYRIGHT

Copyright 2012 Laurent Dami.

This program is free software; you can redistribute it and/or modify it under the terms of either: the GNU General Public License as published by the Free Software Foundation; or the Artistic License.

See http://dev.perl.org/licenses/ for more information.

TODO

  - arg to prevent string transform '*'=>'%' & -like
  - arg to control what happens when $query->param($field) is a list

Parameterized syntax:

  field : =~
  mixed : foo:junk AND bar>234 OR (...)