Yesterday we discussed templates features like layouts, partial templates, and content buffers. We motivated the discussion by introducing a fully functioning example application that tracked user's Christmas wishlists. That application did lack sophistication in the area of data storage, using DBM::Deep for quickly getting a basic persistence layer. While that worked well enough to demonstrate template functionality it was no triumph of the model layer. Indeed some very hack techniques are used, especially in manipulating wishlist items, since there was no unique record ids.

Well as promised I have created a repository for the application on Github. I have also added several tags. While development on the application may continue, those tags will remain for future readers.

The initial form of the application (as seen in yesterday's post) is tagged blog_post/dbm_deep. You are then invited to step through the commits from that one to blog_post/full_app to follow along as I port it from a Lite to a Full app; a practical demonstration of what we saw on Day 4.

This article will briefly discuss the application as it exists in the next tag, blog_post/sqlite_model. At this point I have replaced DBM::Deep with Mojo::SQLite, written a rudimentary model layer for it, and connected the two with the application via helpers. Let's see how that improves the application and in the meantime, get a look at idiomatic database access in Mojolicious!

Model View Controller

Most modern web applications adhere to a pattern called Model View Controller or MVC. Much has been written about MVC, more than could be conveyed here. Quickly though, the view is how data is displayed, the template. The model is the database, both read and write access, and how to manipulated it. This is usually thought of as the "business logic". Finally the controller is supposed to be the minimal amount of logic that can be used to connect the two and process web requests.

In Perl almost all relational database access is via DBI whether directly or indirectly. However this isn't a model as such, a model really needs to have defined data layout (schema) and manipulation.

Many Perl users turn to DBIx::Class, an Object-Relational Mapper (ORM), and this is a great choice. It hides most of the SQL and creates classes for the tables. However some developers like staying a little closer to the SQL.

Mojo-Flavored DBI

The Mojolicious community has several modules that live partway between DBI and DBIx::Class. I lovingly call them "Mojo-Flavored DBI" collectively. The first of these was Mojo::Pg for PostgreSQL. Quickly, copycat modules were made, Mojo::mysql for MySQL and Mojo::SQLite for the embedded database SQLite.

These are attractive because they are lightweight in comparison to ORMs. They feature schema migration management and similar fluent interfaces as Mojolicious. They handle connection pooling and nonblocking access (emulated in the case of SQLite). In recent versions, they also wrap SQL::Abstract which can be used to simplify certain common actions. SQL::Abstract is also used by DBIx::Class, so as you'd expect, these have a feel similar to an ORM.

Going forward with this article, I will use Mojo::SQLite since it doesn't require an external database.

The Schema

The first thing we need to establish is the database schema; the collection of tables and their columns. In Mojo-Flavored DBI these are collected into one file, broken up by comments. These comments and their following contents define how to move between schema version.

-- 1 up
PRAGMA foreign_keys = ON;

create table users (
  id integer primary key autoincrement,
  name text not null unique
);

create table items (
  id integer primary key autoincrement,
  title text,
  url text,
  purchased integer not null default 0,
  user_id integer not null,
  foreign key(user_id) references users(id)
);

-- 1 down

PRAGMA foreign_keys = OFF;
drop table if exists users;
drop table if exists items;

wishlist.sql

Here you can see how to move from the empty version 0 up to version 1. You can also define how to move back down though it is ok to ignore those and not support downgrading.

The schema we define mimics the one we used yesterday. Users have names. Items have titles, urls, purchased state (SQLite doesn't have a boolean) and a reference to the user that requested it.

The Model Class

I extracted the business logic from the original application's controller actions, anything that handled persistence, and moved them to a dedicated class, Wishlist::Model.

package Wishlist::Model;
use Mojo::Base -base;

use Carp ();

has sqlite => sub { Carp::croak 'sqlite is required' };

sub add_user {
  my ($self, $name) = @_;
  return $self
    ->sqlite
    ->db
    ->insert(
      'users',
      {name => $name},
    )->last_insert_id;
}

sub user {
  my ($self, $name) = @_;
  my $sql = <<'  SQL';
    select
      user.id,
      user.name,
      (
        select
          json_group_array(item)
        from (
          select json_object(
            'id',        items.id,
            'title',     items.title,
            'url',       items.url,
            'purchased', items.purchased
          ) as item
          from items
          where items.user_id=user.id
        )
      ) as items
    from users user
    where user.name=?
  SQL
  return $self
    ->sqlite
    ->db
    ->query($sql, $name)
    ->expand(json => 'items')
    ->hash;
}

sub list_user_names {
  my $self = shift;
  return $self
    ->sqlite
    ->db
    ->select(
      'users' => ['name'],
      undef,
      {-asc => 'name'},
    )
    ->arrays
    ->map(sub{ $_->[0] });
}

sub add_item {
  my ($self, $user, $item) = @_;
  $item->{user_id} = $user->{id};
  return $self
    ->sqlite
    ->db
    ->insert('items' => $item)
    ->last_insert_id;
}

sub update_item {
  my ($self, $item, $purchased) = @_;
  return $self
    ->sqlite
    ->db
    ->update(
      'items',
      {purchased => $purchased},
      {id => $item->{id}},
    )->rows;
}

sub remove_item {
  my ($self, $item) = @_;
  return $self
    ->sqlite
    ->db
    ->delete(
      'items',
      {id => $item->{id}},
    )->rows;
}

1;

lib/Wishlist/Model.pm

This class define the ways that the application can alter the data in the database. Rather than the familiar DBI methods like selectrow_arrayref, Mojo-Flavored DBI make a query and then ask for the result shape they want returned. The user can ask for a row as a hash or an array. They can also ask for and array of all thr rows, again as a hash or an array. Sometimes there are other data you want rather than the actual results, like the last_insert_id or the number of rows affected.

Most of the methods are simple enough to employ the SQL::Abstract forms: add, update, remove, even listing the users. However for getting a user we want to make a more complex query by hand. It looks up the user row by name, and aggregates the items that user is wishing for as JSON.

Before fetching the results we tell Mojo::SQLite that we would like to expand the JSON back into Perl data transparently. This expand method differs slightly from the other flavors since SQLite doesn't have metadata to give Mojo::SQLite hints about which column to expand. Once setup, when we call hash we get a nice Perl structure as a result.

The Application Class

The application class might look quite different but its behavior is very similar to yesterday. Don't fret over every line, I will only cover the important things for our purposes.

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

use Mojo::File;
use Mojo::SQLite;
use LinkEmbedder;
use Wishlist::Model;

has sqlite => sub {
  my $app = shift;

  # determine the storage location
  my $file = $app->config->{database} || 'wishlist.db';
  unless ($file =~ /^:/) {
    $file = Mojo::File->new($file);
    unless ($file->is_abs) {
      $file = $app->home->child("$file");
    }
  }

  my $sqlite = Mojo::SQLite->new
    ->from_filename("$file")
    ->auto_migrate(1);

  # attach migrations file
  $sqlite->migrations->from_file(
    $app->home->child('wishlist.sql')
  )->name('wishlist');

  return $sqlite;
};

sub startup {
  my $app = shift;

  $app->plugin('Config' => {
    default => {},
  });

  if (my $secrets = $app->config->{secrets}) {
    $app->secrets($secrets);
  }

  $app->helper(link => sub {
    my $c = shift;
    state $le = LinkEmbedder->new;
    return $le->get(@_);
  });

  $app->helper(model => sub {
    my $c = shift;
    return Wishlist::Model->new(
      sqlite => $c->app->sqlite,
    );
  });

  $app->helper(user => sub {
    my ($c, $name) = @_;
    $name ||= $c->stash->{name} || $c->session->{name};
    return {} unless $name;

    my $model = $c->model;
    my $user = $model->user($name);
    unless ($user) {
      $model->add_user($name);
      $user = $model->user($name);
    }
    return $user;
  });

  $app->helper(users => sub {
    my $c = shift;
    return $c->model->list_user_names;
  });

  my $r = $app->routes;
  $r->get('/' => sub {
    my $c = shift;
    my $template = $c->session->{name} ? 'list' : 'login';
    $c->render($template);
  });

  $r->get('/list/:name')->to(template => 'list')->name('list');

  $r->get('/add')->to('List#show_add')->name('show_add');
  $r->post('/add')->to('List#do_add')->name('do_add');

  $r->post('/update')->to('List#update')->name('update');
  $r->post('/remove')->to('List#remove')->name('remove');

  $r->post('/login')->to('Access#login')->name('login');
  $r->any('/logout')->to('Access#logout')->name('logout');

}

1;

lib/Wishlist.pm

There is an application attribute which holds the Mojo::SQLite instance. Its initializer pulls the name of the database file from configuration or defaults to wishlist.db as before. Unlike with DBM::Deep we now also have to tell it where to find the migrations file. To target these files we use the application's home object and Mojo::File which is a topic for another day.

The application's startup method establishes a model helper which creates an instance of Wishlist::Model and attaches the Mojo::SQLite instance to it. This is a very important concept because this very thin helper is what ties the model into the application as a whole. Any part of the application that needs data from the model ends up using this helper.

For example, there are still the user and users helpers that behave just as their counterparts from yesterday. This time however they work via the model to do their business.

Finally the routes use the Full app declaration style but they do basically the same thing as before once they dispatch to their controllers.

The List Controller

And speaking of controllers, let's see what a controller looks like now. This is the List controller that handles most of the pages.

package Wishlist::Controller::List;
use Mojo::Base 'Mojolicious::Controller';

sub show_add {
  my $c = shift;
  my $link = $c->link($c->param('url'));
  $c->render('add', link => $link);
}

sub do_add {
  my $c = shift;
  my %item = (
    title => $c->param('title'),
    url => $c->param('url'),
    purchased => 0,
  );
  $c->model->add_item($c->user, \%item);
  $c->redirect_to('/');
}

sub update {
  my $c = shift;
  $c->model->update_item(
    {id => $c->param('id')},
    $c->param('purchased')
  );
  $c->redirect_to('list', name => $c->param('name'));
}

sub remove {
  my $c = shift;
  $c->model->remove_item(
    {id => $c->param('id')},
  );
  $c->redirect_to('/');
}

1;

lib/Wishlist/Controller/List.pm

While all the same business logic is accomplished, this time the semantic model methods are used rather than manipulating the data directly. THe methods establish what they want to be done not how to do it. This is much better MVC and will serve you better in the long run.

So is this the end of our discussion of the Wishlist app? Who can say?