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>

Although templates are primarily used with HTML when writing programs in Mojolicious, it is entirely possible and quite simple to use them to respond to any of the MIME types that Mojolicious::Types defines. You merely use the appropriate one-word type name along with .ep, like this Mojolicious::Lite example:

@@ index.json.ep
% use Mojo::JSON qw(to_json);
%== to_json { foo => 3 };

Here, we permit the /index route to respond with JSON by using Mojo's convenient module. We use the double-equal percent-print form so that quote characters and other HTML-ish bits are output as-is, not ampersand-encoded.

It is important to not have empty lines above or below the content, as the newlines would appear in the output.

Also note the use of to_json (which returns characters) rather than encode_json (which returns bytes) to avoid double-encoding UTF8 characters.

With that in mind, you should be able to produce any file format you desire.

Combining several of my previous posts, let's build a quick site with Mojolicious and Zurb Foundation. We will use the AssetPack plugin. Although we are serving fairly simple Javascript and CSS files now, later we can switch to using Coffeescript, Less or Sass or anything which AssetPack can pre-process.

First, install the AssetPack plugin and create a fresh "app" −

$ cpanm Mojolicious::Plugin::AssetPack
$ mojo generate app FoundationExample

Method 1 − Local copy of Foundation

Download the latest Foundation and put the files under a foundation/ directory under our "app" directory:

$ mkdir foundation
$ cd foundation
$  wget http://foundation.zurb.com/cdn/releases/foundation-5.5.2.zip
$ unzip foundation-5.5.2.zip

Now let's create a set of relative symbolic links for each file in the Foundation directory:

$ mkdir  public/foundation/
$ ln -s -r foundation/* public/foundation/

In lib/FoundationExample.pm, change the startup to −

# This method will run once at server start
sub startup {
  my $self = shift;

  $self->plugin("AssetPack");

  my $source = "/foundation/";
  $self->asset("foundation.css" => $source."css/foundation.min.css");
  $self->asset("foundation.js" => $source."js/foundation.js");
  $self->asset("modernizr.js" => $source."js/vendor/modernizr.js");
  $self->asset("jquery.js" => $source."js/vendor/jquery.js");

  # Router
  my $r = $self->routes;

  # Normal route to controller
  $r->get('/')->to('example#welcome');
}

Continue with Both Methods, below.

Method 2 − CDN method

Use only the changes in lib/FoundationExample.pm shown above, but replace the source with −

  my $source = "https://cdnjs.cloudflare.com/ajax/libs/" .
    "foundation/5.5.2/";

Continue with Both Methods, below.

Both Methods.

In lib/FoundationExample/Controller/Example.pm − change the welcome routine to:

sub welcome {
  my $self = shift;

  $self->stash(copyright_holder => 'Someone');
  $self->stash(keywords => 'something, somebody, somewhere');
  $self->stash(author => 'William Lindley');

  # Render template "example/welcome.html.ep" with message
  $self->render(msg => 'Welcome to the Mojolicious real-time web framework!');
}

In templates/example/welcome.html.ep − change the layout to:

% layout 'zurbish';

and templates/layouts/zurbish.html.ep −

<!DOCTYPE html>
<html lang="en">
 <head>
 <meta charset="utf-8">
 <!-- Zurb Foundation stuff -->
 <meta name="viewport" content="width=device-width, initial-scale=1.0">
%= asset 'foundation.css'
%= asset 'modernizr.js'
 <!-- end Zurb -->
 <title><%= title %></title>
 <meta name="copyright" content="Copyright ⓒ <%= ((localtime())[5]+1900) %> <%= $copyright_holder %>" />
 <meta name="keywords" content="<%= $keywords %>" />
 <meta name="author" content="<%= $author %>" />
 </head>
 <body>
%= asset 'jquery.js'
%= asset 'foundation.js'
 <script>
 $(document).foundation();
 </script>
<%= content %>
 </body>
</html>

For more, see Jan Henning Thorsen's talk on Mojolicious::Plugin::AssetPack at MojoConf 2014.

Please note, THIS IS A WORK IN PROGRESS, not a final article.

First let's look at the skeleton version of the main script that gets created when you execute "mojo generate app Foo" − this is foo/script/foo −

#!/usr/bin/env perl

use strict;
use warnings;

use lib 'lib';

# Start command line interface for application
require Mojolicious::Commands;
Mojolicious::Commands->start_app('Foo');

what Mojolicious::Commands does:

• enables all the processing for Mojolicious commands like daemon, get, generate, *routes, test, *etc.

Then we do Mojolicious::Commands->start_app('Foo') which:

• handles all the switches and arguments in the invocation command-line

• Loads the application class (here, lib/Foo.pm)

• Calls the application

The source for start_app (in Mojolicious/Commands.pm) looks like this:

sub start_app {
  shift;
  Mojo::Server->new
    ->build_app(shift)
      ->start(@_)
}

This calls the new() method of Mojo::Server to create a Mojo server object (and subscribe to the "request" event with default request handling); the server object is returned and upon that we call build_app(). The argument to build_app will in this example be "Foo" which is the name of the application class for which we are building an instance.

→ Q: What does it mean, subscribe to the request event?

Then the call to start() runs as shown in Mojolicious.pm, namely by invoking the commands() and run() methods of Mojolicious, which have the effect of processing any commands and then running the event loop.

The only other code, aside from a stock Controller, is in lib/Foo.pm:

package Foo;
use Mojo::Base 'Mojolicious';

# This method will run once at server start
sub startup {
  my $self = shift;

  # Documentation browser under "/perldoc"
  $self->plugin('PODRenderer');

  # Router
  my $r = $self->routes;

  # Normal route to controller
  $r->get('/')->to('example#welcome');
}

What's going on here?

the argument is the 'app' (instance of _ class) that _ creates

From this we can call built-ins like plugin() and routes()

−−

https://en.wikipedia.org/wiki/Event-driven_architecture

from http://mojolicio.us/perldoc/Mojolicious/Guides/FAQ#What-is-an-event-loop

An event loop is basically a loop that continually tests for external events and executes the appropriate callbacks to handle them, it is often the main loop in a program. Non-blocking tests for readability/writability of file descriptors and timers are commonly used events for highly scalable network servers, because they allow a single process to handle thousands of client connections concurrently.

1. Set up all the routes, timers, and other reactions to be processed by the event engine.

2. The script calls app->start (for Lite) or −−− (for Mojolicious)

3. Event loop runs − Mojo::IOLoop (detail?)

from http://mojolicio.us/perldoc/Mojolicious/Guides/Cookbook#Timers

Timers, another primary feature of the event loop, are created with

[

"timer" in Mojo::IOLoop ](http://mojolicio.us/perldoc/Mojo/IOLoop#timer) and can for example be used to delay rendering of a response, and unlike sleep , won't block any other requests that might be processed concurrently… Recurring timers created with "recurring" in Mojo::IOLoop are slightly more powerful, but need to be stopped manually, or they would just keep getting emitted.

from http://mojolicio.us/perldoc/Mojolicious/Guides/Cookbook#Exceptions-in-events

Since timers and other non-blocking operations are running solely in the event loop, outside of the application, exceptions that get thrown in callbacks can't get caught and handled automatically. But you can handle them manually by subscribing to the event

[

"error" in Mojo::Reactor ](http://mojolicio.us/perldoc/Mojo/Reactor#error) or catching them inside the callback.

Marcus Ramberg wrote in the December 2014 Perl Advent Calendar about Mojo::Pg, the new DBI wrapper for the Mojolicious framework.

Particularly of interest are

• Automatic transaction rollback if the 'guard variable' goes out of scope. In practice this means if you just return or throw an exception in the middle of a routine, any transactions get rolled back without having to do anything yourself.

• Migrations. Written in plain SQL and easily embedded or attached to your program, this was one of the few things I liked about Rails, but it's here now.

• Asynchronous triggers. You could have two processes attached to the same database; when one does an INSERT, the other process gets a notification. This opens up all kinds of things that were nearly impossible before.

On top of the upcoming "INSERT … ON CONFLICT …" grammar in Postgres 9.5, which lets you do things like:

INSERT INTO distributors (did, dname)
  VALUES (5, 'Gizmo transglobal'), (6, 'Associated Computing, inc')
  ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

it's well worth considering whether to keep using MySQL at all.