Mojolicious::Lite RESTful CRUD

Data Model 

affix       definition              etymology
---------------------------------------------
ab          from, away              latin
bio         life                    greek
o           combining               NULL
ortho       straight, right angles  greek
phile?      loving, thriving        greek
ic          belonging               greek
...

"orthophilic" - belonging or thriving at right angles

REST

"Representational State Transfer"

Architectural style of networked systems

Representation of a web resource places the client in a state

The client application changes (transfers) state with each resource representation

REST (cont.)

While REST is not a standard, it does use standards:

REST (cont.)

Verb - Behavior

get 

# curl http://127.0.0.1:3000/affix/fug?token=ABC123

get '/affix/:affix' => sub {
    my $self = shift;

    my $affix = $self->stash('affix');

    my $sql = 'SELECT * FROM fragment WHERE affix LIKE ?';
    my $sth = $self->db->prepare($sql);
    $sth->execute( '%'.$affix.'%' );
    my $part = $sth->fetchall_arrayref;
    $sth->finish;
    $self->db_disconnect;

    if ( @$part ) {
        return $self->render( json => { part => $part } );
    }
    else {
        return $self->render( json => { result => 0, message => 'No such part' } );
    }
};

get by definition 

# curl http://127.0.0.1:3000/defn/stun?token=ABC123

get '/defn/:defn' => sub {
    my $self = shift;

    my $defn = $self->stash('defn');

    my $sql = 'SELECT * FROM fragment WHERE definition LIKE ?';
    my $sth = $self->db->prepare($sql);
    $sth->execute( '%'.$defn.'%' );
    my $part = $sth->fetchall_arrayref;
    $sth->finish;
    $self->db_disconnect;

    if ( @$part ) {
        return $self->render( json => { part => $part } );
    }
    else {
        return $self->render( json => { result => 0, message => 'No such definition' } );
    }
};

post 

# curl --data 'token=ABC123&json={"affix":"XXX","defn":"xyz"}' http://127.0.0.1:3000/add

post '/add/' => sub {
    my $self = shift;

    ...

    my $sql = 'INSERT INTO fragment (affix, definition, etymology) VALUES (?, ?, ?)';
    my $sth = $self->db->prepare($sql);
    $sth->execute( $affix, $defn, $etym );
    my $new_id = $sth->{mysql_insertid};
    $sth->finish;
    $self->db_disconnect;

    if ( $new_id )
    {
        return $self->render( json => { result => 1, message => 'OK' } );
    }
    else {
        return $self->render( json => { result => 0, message => 'Insert Failure' } );
    }
};

put 

# curl -X PUT --data 'token=ABC123&json={"id":"942","affix":"XXX","defn":"abc","etym":"foo"}' http://127.0.0.1:3000/update

put '/update/' => sub {
    my $self = shift;

    ...

    my $sql = 'UPDATE fragment SET affix=?, definition=?, etymology=? WHERE id=?';
    my $sth = $self->db->prepare($sql);
    my $rv = $sth->execute( $affix, $defn, $etym, $id );
    $sth->finish;
    $self->db_disconnect;

    if ( $rv )
    {
        return $self->render( json => { result => 1, message => 'OK' } );
    }
    else {
        return $self->render( json => { result => 0, message => 'Update Failure' } );
    }
};

del 

# curl -X DELETE http://127.0.0.1:3000/delete/42?token=ABC123

del '/delete/:id' => sub {
    my $self = shift;

    my $id = $self->stash('id');

    my $sql = 'DELETE FROM fragment WHERE id=?';
    my $sth = $self->db->prepare($sql);
    my $rv = $sth->execute($id);
    $sth->finish;
    $self->db_disconnect;

    if ( $rv && $rv ne '0E0' )
    {
        return $self->render( json => { result => 1, message => 'OK' } );
    }
    else {
        return $self->render( json => { result => 0, message => 'Delete Failure' } );
    }
};

Authorization 

under sub {
   my $self = shift;

   my $token = $self->param('token');

   my $sql = 'SELECT username FROM api_access WHERE token=? AND active=1';
   my $sth = $self->db->prepare($sql);
   $sth->execute($token);
   my ($username) = $sth->fetchrow;
   $sth->finish;

   if ($username)
   {
      return 1;
   }
   else {
      $self->render( text => 'Access denied' );
      $self->db_disconnect;
      return;
   }
};

Helper Methods 

my $dbh; # Global handle

helper db => sub {
   if ( $dbh ) {
     return $dbh;
   }
   else {
     $dbh = DBI->connect( 'DBI:mysql:database=word_part;host=localhost', 'root', 'abc123' )
       or die $DBI::errstr;
     return $dbh;
   }
};

helper db_disconnect => sub {
   my $self = shift;
   $self->db->disconnect;
   $dbh = "";
};

Methods (cont.)

Call a helper as an object method: 

get '/part/:affix' => sub {
   ...
   $self->db_disconnect;
   ...

post '/add/' => sub {
    ...
    my $hash;
    $hash = $self->json_data if $self->param('json');
    my $affix  = $hash->{affix}  || $self->param('affix');
    ...

Web Enable

put and del become posts

edit Route & Template

Could use the LWP::UserAgent get, post, put and delete methods.

edit 

# http://127.0.0.1:3000/edit/1?token=ABC123

get '/edit/:id' => sub {
    my $self = shift;

    my $id = $self->stash('id');

    my $sql = 'SELECT * FROM fragment WHERE id = ?';
    my $sth = $self->db->prepare($sql);
    $sth->execute($id);
    my $part = $sth->fetchall_hashref('id');
    $sth->finish;
    $self->db_disconnect;

    $self->stash( affix => '' );
    $self->stash( definition => '' );
    $self->stash( etymology => '' );
    $self->stash( method => 'add' );
    my ($p) = values %$part;
    if ( keys %$part )
    {
        $self->stash( $_ => $p->{$_} ) for keys %$p;
        $self->stash( method => 'update' );
    }

    $self->render('edit');
};

edit Template 

__DATA__

@@ edit.html.ep
<!DOCTYPE html>
<html>
<head><title>Edit a Fragment</title></head>
<body>
<form action="/<%= $method %>/" method="post">
<input type="hidden" name="token" value="ABC123"/>
<input type="hidden" name="id" value="<%= $id %>"/>
Follows: <input type="checkbox" name="prefix"/>
Affix: <input type="text" name="affix" value="<%= $affix %>"/>
Precedes: <input type="checkbox" name="suffix"/>
Defn: <input type="text" name="defn" value="<%= $definition %>"/>
Etym: <input type="text" name="etym" value="<%= $etymology %>"/>
<input type="submit" name="<%= $method %>" value="<%= $method %>"/>
</form>
<!--
<form action="/delete/<%= $id %>" method="delete">
<input type="hidden" name="token" value="ABC123"/>
<input type="submit" name="delete" value="delete"/>
</form>
-->
</body>
</html>

Links

Logic lifted from here:

http://code-tricks.com/build-a-simple-restful-api-using-mojolicious

The code behind this presentation:

https://github.com/ology/Lex/blob/master/mojo-word-part-api

Building web services the REST way:

http://www.xfront.com/REST-Web-Services.html