Laurent Dami > SQL-Abstract-FromQuery-0.05 > SQL::Abstract::FromQuery

Download:
SQL-Abstract-FromQuery-0.05.tar.gz

Dependencies

Annotate this POD

View/Report Bugs
Module Version: 0.05   Source  

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.

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 ^

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

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 asoociated 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 (...)
syntax highlighting: