Handy Tricks with SQL::Abstract::More and Mojolicious

In writing REST style interfaces, we would like to minimize code and increase transparancy by passing sort, limit and offset parameters from the HTTP request into your SQL queries.

Note that SQL::Abstract::More throws an error if you specify -offset without -limit, so I always include my own defaults for those instead of relying on the database.

# In your model

use List::Util qw(pairmap);
use SQL::Abstract::More;

my $sqla = SQL::Abstract::More->new;

sub _query_params {
    my ($self, @params) = @_;

    # Select request query parameters, skipping any omitted.
    # Mogrify keys from e.g., 'offset' → '-offset' as per
    # SQL::Abstract::More.  Any non-word characters in the
    # parameter's value are excised to prevent injection
    # attacks. When building hash, provide default values (using
    # -offset style) before calling this.
    map { my $value = $self->param($_) =~ s/\W/ /gr;
          defined $value ? 
              pairmap { ('-'.$a) => $b } ( $_, $value) :
              ()  # empty list skips
        } @params;
}

# Example model method

sub get_something {
    my ($self) = @_;   # $self passed from your Controller
    …
    my %qp = ( -offset => 10, -limit => 3,
               $self->_query_params(qw(offset limit order_by)) );
    my ($sql, @bind) = $sqla->select(-from => 'mytable', %qp );
    …
}

Here is a complete example:

 #!/usr/bin/env perl
 use Mojolicious::Lite;

 ################
 #  Gazetteer browser
 #  Demonstrating
 #  1. Retrieval of tab-delimited file from U.S. Census Bureau into a SQLite database
 #  2. Using SQL::Abstract::More to build queries
 #  3. Safely passing Mojolicious query parameters to the database query
 #  4. Returning JSON
 ################

 use Mojo::UserAgent;

 # Fetch remote resource.
 my $ua = Mojo::UserAgent->new;
 # This is the list of place names in Vermont from the 2015 U.S. Census
 my $tx = $ua->get('http://www2.census.gov/geo/docs/maps-data/data/gazetteer/2015_Gazetteer/2015_gaz_place_50.txt');

 # For now, assume resource exists in the first chunk.
 my $data_text = $tx->res->content->asset->get_chunk(0);

 my @lines = split(/\s*\n/, $data_text);

 my @fields = map { lc } split(/\t/, shift @lines);

 # data as:
 #USPS  GEOID   ANSICODE    NAME    LSAD    FUNCSTAT    ALAND   AWATER  ALAND_SQMI  AWATER_SQMI INTPTLAT    INTPTLONG
 #VT    5000400 02378297    Albany village  47  A   3992913 25599          1.542           0.010     44.729932   -72.381758

 use Mojo::SQLite;

 # Create a table, using field names from first line of file
 my $sql = Mojo::SQLite->new('sqlite:gaz_place.db');
 $sql->db->query('drop table if exists places');
 $sql->db->query('create table places (id integer primary key autoincrement, ' .  join(',', map { "$_ text" } @fields ). ')');

 # Populate table
 foreach my $place_line (@lines) {
     my %place;
     @place{@fields} = map { s/^\s+//; s/\s+$//; $_; } split (/\t/, $place_line);
     if (defined $place{name}) {  # Remove trailing USGS designation
         $place{name} =~ s/\s+(village|city|CDP)//;
     }
     $sql->db->query('insert into places (' . join(',', keys %place ). ') '.
                     'values (' . join(',', (map {'?'} keys %place)) . ');',
                     values %place);
 }

 use SQL::Abstract::More;
 my $sa = SQL::Abstract::More->new();

 use List::Util qw(pairmap);

 sub _query_params {
     my ($self, @params) = @_;

     # Select request query parameters, skipping any omitted.
     # Mogrify keys from e.g., 'offset' → '-offset' as per
     # SQL::Abstract::More.  Any non-word characters in the
     # parameter's value are excised to prevent injection
     # attacks. When building hash, provide default values (using
     # -offset style) before calling this.
     map { my $value = $self->param($_) =~ s/\W/ /gr;
           defined $value ? 
               pairmap { ('-'.$a) => $b } ( $_, $value) :
               ()  # empty list skips
         } @params;
 }

 get '/places' => sub {
     my ($c) = @_;

     # Build a SQL query parameter list with defaults, with selected HTTP query parameters.
     # Invoke with $c->_query_params once you move that into a package
     my %qp = ( -offset => 0, -limit => 10, _query_params($c, qw(offset limit order_by)));

     $c->render(json => $sql->db->query($sa->select(-columns => '*', -from => 'places', %qp))->hashes);
 };

 get '/' => sub {
   my $c = shift;
   $c->render(template => 'index');
 };

 app->start;
 __DATA__

 @@ index.html.ep
 % layout 'default';
 % title 'Gazetteer';
 <h1>Gazetteer Place Name Browser</h1>
 This demonstrates passing parametersthe
 <%= link_to 'list of place names' => '/places' %>.

 To see how parameters are passed through to the SQL,
   try appending query strings like this:

 <ul>
 <li><a href="/places">Example 1.</a> No parameter passed.</li>
 <li><a href="/places?offset=0&limit=20&order_by=aland_sqmi+desc">Example 2</a>
   has offset 0, limit 20, sorting by land area in square miles, largest first</l1>
 <li><a href="/places?offset=20&limit=10&order_by=aland_sqmi+desc">Example 3</a>
   has offset 20, limit 10, like a page following Example 2.</li>
 <li><a href="/places?offset=0&limit=10&order_by=name+desc">Example 4</a>
   has offset 0, limit 10, sorting by name, reverse alphabetically</l1>
 </ul>

 @@ layouts/default.html.ep
 <!DOCTYPE html>
 <html>
   <head><title><%= title %></title></head>
   <body><%= content %></body>
 </html>