Thursday, June 7, 2012

Translating user queries into SQL::Abstract (using Regexp::Grammars)

As mentioned in my previous post , I've been quite interested in  Regexp::Grammars recently.

My latest project with it is SQL::Abstract::FromQuery , just released to CPAN. This is a module 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 SQL::Abstract; that module will in turn produce the SQL statement and bind parameters to query the database.Users can type regular values, comparison operators, patterns, etc 


Technically, this uses many advanced features of Regexp::Grammars : list results,"autoactions", named grammars, multiple inheritance of grammars and of actions, etc.; so if you ever wondered what these features are good for, here is an example. It also uses some great features of Perl, like dynamic loading of components, dynamic building of an anonymous subclass ... I really feel happy to work with a language that has such a wonderful toolbox.

Nate Wiger, the original author of  SQL::Abstract, already had this vision of translating Web forms into SQL queries; here we elaborate on that idea and support some more details.

The module is still in early infancy, so avoid using it in production (the API probably needs some improvements); suggestions are welcome. Enjoy!