The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.
 / 
Dancer-Plugin-SQLSearch-0.03
++
/ Dancer::Plugin::SQLSearch

NAME

Dancer::Plugin::SQLSearch - Search helper for relational databases

SYNOPSIS

 #!/usr/bin/perl
 
 use Dancer;
 use Dancer::Plugin::SQLSearch;

 get '/search' => sub {
     my @search_fields = qw( name constellation type ); 
     search (
        query   => param('query'),
        page    => param('page'),
        back    => param('back'),
        fields  => \@search_fields,
        execute => \&actual_search, 
     );
     
     return template 'search';
 };    

 sub actual_search {
     my ($where, $offset, $limit) = @_;
     
     # Perform the actual search in the database
     ....
     
     return $count, \@results;
 }

DESCRIPTION

This plugin implements a simple search helper for relational databases.

Normally, you first present a search page with an input field asking the user for a query string. You then look for the string in your database and present a page with the input field, a table with the first page of results, and pagination links to navigate through the full set of search results. If the user goes to one of the results, you can also have a link back to the search results.

In more detail, the plugin will take a search query, split it into words, and create a data structure suitable for SQL::Abstract. It will also calculate the proper offset and limit for the requested results page, so that you can build the proper SQL query. The actual database query is executed by a subroutine that you will provide.

SQL::Abstract is a simple way to produce SQL. It is the underlying tool for popular ORMs like DBIx::DataModel and DBIx::Class, and they can take the data structure directly and perform the database search for you.

CONFIGURATION

The main job of this plugin is to build the data structure that SQL::Abstract will turn into an SQL "where" clause. For example, if you entered the search query "orion belt" to the example in the synopsis, the data structure would translate into a where clause equivalent to:

 WHERE name LIKE '%orion%' OR name LIKE '%belt%'
    OR constellation LIKE '%orion%' OR constellation LIKE '%belt%'
    OR type LIKE '%orion%' OR type LIKE '%belt%'

The plugin needs or could use the following configuration options:

  • Search fields

    This is the list of fields where the SQL query will look for the search terms. Required.

  • Search operator

    LIKE by default; optional.

  • Number of results per page

    10 by default. Optional, too.

Your configuration file might look like this:

 plugins:
    SQLSearch:
        fields:
            - name
            - constellation
            - type
        search_operator: ILIKE
        results_per_page: 15

The three configuration options can also be fed to the search method, like fields in the synopsis example.

METHODS

There is only one method exported by this plugin: search.

Besides the configuration options, which can be entered as arguments for search, we have the following arguments:

query

This is the string that we will look for in the database. It will be split into words using white space. If the search string is entered between " (double quotes), it will not be split thus allowing for exact phrase matches.

page

Page of results to fetch from the database. It is used to calculate the offset for your SQL query.

back

If set to true, and if query and page are not defined, the plugin will run the previous search query. The last search query is always saved in the session object.

execute

A reference to the subroutine that performs the actual search in the database. It will receive the data structure, an offset and a limit to build an SQL query. It must return the total number of results in the database and the current page of results.

advanced

Have you seen search pages that include advanced search parameters? May be they let you restrict your search to a certain period of time, or fix the value of certain database fields.

This argument will take in a hash or array reference that represents these restrictions. This data structure will be "ANDed" to the generated one. Please see the documentation for SQL::Abstract for detailed instructions on how to build your data structure. See the example at the end of this document as well as the test files.

Output hash reference

The search method will return a hash reference with the following keys:

query

The entered search query.

page

The requested page number.

first_page

Either number 1 or not defined. It is not defined for the initial, blank search page.

prev_page

Either the current page number minus one, or undef.

next_page

Either the current page plus one, or undef.

last_page

Either the number of the last page of results, or undef.

count

The number of results for the searched query.

results

An array reference of individual results. Results may be whatever you decide, but hash references work nicely within templates. Undef for empty search pages.

Tokens available in templates

All of the keys returned by search are available within the template. Use them as:

 <% search.query %>
 <% FOREACH result IN search.results %> <% result.field %> <% END %>
 <% search.next_page %>

EXAMPLE

This is a full example of the search routine that you must provide. Note that it uses Dancer::Plugin::Database and SQL::Abstract:

 sub actual_search {
    my ($data_structure, $offset, $limit) = @_;
    
    my $sql = SQL::Abstract->new;
    my ($where_clause, @bind) = $sql->where($data_structure);
    
    my $sql_statement = qq{
        SELECT name, description, latitude, longitude, 
               diameter, depth, colongitude, eponym
        FROM craters
        $where_clause
        LIMIT ?
        OFFSET ?
    };
        
    my $count_statement = qq{
        SELECT count(*) FROM craters $where_clause
    };
    
    my $results = database->selectall_arrayref(
        $sql_statement,
        { Slice => {} },
        @bind, $limit, $offset
    );
    
    my $count = database->selectall_arrayref(
        $count_statement, undef, @bind
    );
    
    return $count->[0][0], $results;
 }

CREDITS

Many years ago I was looking for a module like this in CPAN, and I found it: Text::SQLSearch::SQL by Chisel Wright. However, that module is part of a larger distribution (actually, it is a full application, Parley) and so I stole a couple of routines from it. These routines are the heart of this plugin.

Naveed Massjouni suggested that the search terms are AND'ed when there is only one database field to include in the database query.

AUTHOR

Julio Fraire, julio.fraire at gmail.com

COPYRIGHT

All rights reserved. This module is free software; you are free to use it, modify it and distribute it under the same terms as Perl itself.