/ 
SQL-Abstract-FromQuery-0.11
River stage one • 1 direct dependent • 1 total dependent
/ 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
      IGNORE   => qr/^(..)/,      # fields that should be ignored
   }
);

# 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.

INPUT GRAMMAR

By default, form fields must conform to the standard grammar, which accepts

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

    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. Times may be entered as hh:mm or hh:mm:ss. Datetimes may be entered as a date followed by a time, separated by spaces or separated by a 'T', according to the ISO format. Returned values from dates, times or datetimes are in ISO format, i.e. yyyy-mm-dd, hh:mm:ss or yyyy-mm-ddThh:mm:ss.

  • 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

Fields can be explicitly associated with other grammar rules, different from standard (see below).

The precise syntax for grammar rules is expressed in Regexp::Grammars format within the source code of this module. Grammar 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.

Key IGNORE in the fields hashref is a reserved word; it should be associated with a regex, and user fields found in the query that match this regex will be ignored. This is useful if the HTML form contains additional information useful for the application, but which should not participate in the generated SQL.

parse

my $criteria = $parser->parse($data);

Parses the collection of fields in $data, and returns a $criteria hashref in SQL::Abstract format, ready to be injected as a "where" clause.

Input $data can be supplied either as a plain hashref, or as an object that possesses a CGI-compatible param() method (such as CGI, Catalyst::Request or Plack::Request).

Each field in $data is parsed according to its corresponding grammar rule, as specified in the -fields argument to the "new" method. Fields without any explicit grammar rule are parsed through the standard rule.

In case of parse errors, an exception is raised, which stringifies to a list of faulty fields and their associated errors. Internally this is an object with an arrayref of arrayrefs of error messages -- see the source code if you need to walk through that structure.

INHERITANCE

Components use inheritance from the present class in two ways : they can extend/override the syntax rules, and they can extend/override the methods. See the source code of SQL::Abstract::FromQuery::FR for an example.

When writing subclasses, beware that action rules hooked to the grammar cannot use regex operations : this would cause a segfault (because Regexp::Grammars runs inside the perl regexp engine, and this is not re-entrant).

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 (...)