Tuturial: Catalyst Models with DBIx::Simple

Version: 1.11

Audience

This document is targeted to people learning Catalyst, but who are not experienced with DBIx::Class, or otherwise interested in working with DBI Models. The presumption is that readers have already gained some familiarity with Catalyst. If you haven't stop now and work through at least the "Catalyst Basics" and "More Catalyst Basics" chapter of the Catalyst Tutorial. This document presumes a familiarity with SQL on the part of the reader, without which there will be nothing simple about it.

Why Choose a DBI Model?

Why Choose DBIx::Simple?

DBIx::Simple is a very easy and intuitive wrapper for those already familiar with Object Oritented Programming and DBI. DBIx::Simple is dependent on DBI, it provides a bunch of convenient methods to take some of the tedium out of DBI and also offers support for SQL Abstract. The techniques for using DBIx::Simple can also be applied to other DBI wrappers. It is convenient for me (I even put something similar up on CPAN myself), if you don't like, it is easy enough to strip back to bare DBI or replace it with a different wrapper.

Pre-Requisites

Lesson I: Switching More Catalyst Basics to DBIx::Simple

First, add these two lines to your Makefile.PL

 requires 'DBIx::Simple';
 requires 'Catalyst::Model::DBI';

The Syntax for creating a DBI Model is: scriptname_create.pl model Goofball DBI.

Create the Model: ./script/myapp_create.pl model DBI DBI

Hint: although I call this model DBI, you do not have to call yours that, consider this a lack of imagination on my part. "Goofball" can be replaced with just about any simple string.

Edit your new model.

 package MyApp::Model::DBI;
 
 use strict;
 use warnings;
 use DBIx::Simple ;
 use parent 'Catalyst::Model::DBI';
 
 __PACKAGE__->config(
     dsn           => 'dbi:SQLite:myapp.db',
     user          => '',
     password      => '',
     options       => q{PRAGMA foreign_keys = ON},
 );

Test the application.

Initially we're only going to retrieve the list of books. Open the Controller, Books.pm.

 # Retrieve all of the book records as book model objects and store
 # in the stash where they can be accessed by the TT template

Replace $c->stash(books => [$c->model('DB::Book')->all]); with the following lines:

 my $db = DBIx::Simple->new( $c->model('DBI')->dbh ) ;
 my @result = $db->query( 'SELECT * FROM book' )->hashes ;
 $c->stash(books => \@result );

The new code starts by Creating a new DBIx::Simple object from the dbh handle created by Model::DBI. The second line executes a query and via the hashes method returns an array of references to hashes of the rows returned by the query. The third line places a reference to the same array into the stash. The last two lines can be condensed as: $c->stash( books => [ $db->query( 'SELECT * FROM book' )->hashes ] );.

In this case our Controller has a little more code, which is a pretty fair trade-off for not having to do anything about a DBIC Schema. But there is a big problem with this approach, all of our Model logic is going to wind up in our Controllers. If we are working on something where data access is trivial or where we are building our application in Stored Procedures and all we ever want to do is execute a Stored Procedure, this would be fine. However, not allowing any SQL in your Controllers is a good best practice. So next we're going to put the query in the Model.

Add to the Model:

 sub List_Books {
    my $self = shift ;
    my $qs = qq / SELECT * FROM book / ;
    my $db = DBIx::Simple->new( $self->dbh ) ;
    my @results = $db->query( $qs )->hashes ;
    return @results ;
 }

And then replace the code in the Controller we added previously with just one line.

 $c->stash( books => [ $c->model('DBI')->List_Books() ] );

When you run myapp_server.pl http://localhost:3000/books/list should show the books, but we are still missing the authors.

Even though this is the first chapter in the tutorial that touched the model, the final presentation requries 3 tables and includes two calculated fields. The example is doing calculations in the view. We'd like to entirely reimpliment it as a single SQL statement or hide complex SQL in a Stored_Procedure or View, which would then drive the complexity back into the database. SQLite3 doesn't support variables, cursors and the like, so we'll have to implement the logic in the Model.

You may notice below that @Books = ... executes the exact same query as List_Books. We'll talk about this later.

In the Model add a second subroutine.

 # Provides a list of Books plus their authors and the number of authors for the book.    
 sub Books_With_Authors {
     my $self = shift ;
     my $db = DBIx::Simple->new( $self->dbh ) ;   
     my @Books = $db->query( 'SELECT * FROM book' )->hashes ; 
     my $qs_get_author = qq /
 SELECT author.last_name 
 from author 
 inner join book_author
 on book_author.author_id = author.id
 where
 book_author.book_id = ? / ;
        foreach my $book (@Books) { 
                my @authors = $db->query( $qs_get_author, $book->{ id } )->flat() ;
                $book->{ author } = join ( ', ', @authors ) ;
                $book->{ author_count } = scalar @authors ;      
                }
 return @Books ;
    } #  Books_With_Authors -- in longer codeblocks I often make a note at the closing }.

In the SQL here I used the wildcard feature so that I could reuse the same query for each book, and then I used the flat method which is a DBIS method to return a single column result as an array.

Change the line in the controller from

  $c->stash( books => [ $c->model('DBI')->List_Books() ] );

to

 $c->stash( books => [ $c->model('DBI')->Books_With_Authors() ] );

Now edit the View.

 [% # This is a TT comment.  The '-' at the end "chomps" the newline.  You won't -%]
 [% # see this "chomping" in your browser because HTML ignores blank lines, but  -%]
 [% # it WILL eliminate a blank line if you view the HTML source.  It's purely   -%]
 [%- # optional, but both the beginning and the ending TT tags support chomping. -%] 
 
 [% # Provide a title -%]
 [% META title = 'Book List' -%]
 
 <table>
 <tr><th>Title</th><th>Rating</th><th>Author(s)</th></tr>
 [% # Display each book in a table row %]
 [% FOREACH book IN books -%]
   <tr>
     <td>[% book.title %]</td>
     <td>[% book.rating %]</td>
     <td>([% book.author_count %]) [% book.author %] </td>
   </tr>
 [% END -%]
 </table>
 
Congratulations. You've converted a very small demo application to using a DBIx::Simple Model.

Lesson II: Sub Models

How to make the DBIX::Simple Object Permanent in your Model

So far every time we wanted DBIx::Simple we created a new instance of it. In a larger Model this will get cumbersome. We can use Moose to create a $self->db object accessible throughout our model, that can even be called directly from a Controller (remember that possible and advisable are not synonyms).

 use Moose ; #use Moose immediately before calling on Moose to extend the object.
 has db=>(
        is =>'ro',
        isa=>'DBIx::Simple',
        lazy_build=> 1,
 # If we don't want to handle all dbis methods, specify those that we want.     
 #      handles=> [qw/query flat /],
        );
 sub _build_db {
        my $self = shift ;
        return DBIx::Simple->connect($self->dbh);
 }

Breaking our Model into Sub-Models

Every time we have a new query to execute from a Controller a new Model Method has to get built, after a while our model could get quite enormous. The solution is to break up the model. So now go to the Model directory and create a new file Books.pm. You can either execute the helper script: U<./script/myapp_create.pl model DBI::Book DBI>, or just create a new file, the DBI Model Helper doesn't create the right skeleton for the sub-model, but will put it in the right place and create a test skeleton.


Now create or replace the contents of DBI/Book.pm with:
 package MyApp::Model::DBI::Book;
 use strict;
 use warnings;
 use Moose;
 use namespace::autoclean;
 extends 'MyApp::Model::DBI';
 sub List_Books {
    my $self = shift ;
    my $qs = qq / SELECT * FROM book / ;
    my $db = DBIx::Simple->new( $self->dbh ) ;
    my @results = $db->query( $qs )->hashes ;
    return @results ;
 }
 
Remember to remove List_Books from the top level DBI model.

Then do the exact same thing with Books_With_Authors as DBI::Book_Author_BookAuthor. Be sure to use an underscore instead of a dash, as a dash might get misinterpreted.

 package MyApp::Model::DBI::Book_Author_BookAuthor;
 use strict;
 use warnings;
 use Moose;
 use namespace::autoclean;
 extends 'MyApp::Model::DBI';
 # Provides a list of Books plus their authors and the number of authors for the book.    
 sub Books_With_Authors {
     my $self = shift ;
     my $db = $self->db ;  # Visual convenience to make code more readable.  
     my @Books = $db->query( 'SELECT * FROM book' )->hashes ;
     my $qs_get_author = qq /
 SELECT author.last_name 
 from author 
 inner join book_author
 on book_author.author_id = author.id
 where
 book_author.book_id = ? / ;
    foreach my $book (@Books) { 
        my @authors = $db->query( $qs_get_author, $book->{ id } )->flat() ;
        $book->{ author } = join ( ', ', @authors ) ;
        $book->{ author_count } = scalar @authors ;      
        }
 return @Books ;
 } #  Books_With_Authors -- in longer codeblocks I often make a note at the closing }.

Remove Books_With_Authors from DBI and finally in your controller there is a line to change:

 $c->stash( books => [ $c->model('DBI::Book_Author_BookAuthor')->Books_With_Authors() ] );

We're organizing our Model, methods for the book table will go in DBI::Book, methods for the author table would go in DBI::Author, and things that result from joining all the tables go in DBI::Book_Author_BookAuthor.

A ways back we noticed that Book::List_Books and Book_Author_BookAuthor::Books_With_Authors repeated a line of SQL. In this case the line was trivial, and isn't worth putting any effort into. But let's say we had a much more substantial code block. If we try to call DBI::Book::List_Books from DBI::Book_Author_BookAuthor we can't do so, because both Modules are sperate child objects they inherit all of MyApp::Model:DBI, but not their siblings. One solution would be to place shared methods in a higher level Module, or to place the shared SQL statements in a higher level variable so that all children could access it. Another approach might be to put your methods into Moose::Roles and then assemble those roles in your Model.

However, my answer is to choose a real database and make a Stored Procedure or View. Don't worry about repeating trivial statements, but do move your complex statements into the database so that they are accessible in your model via trivial statements.

Lesson III Testing Your Model

In practice you'll find that it is often easier to write your DBI Model Methods directly against a test than against your controller. So I'm going to make some tests and show you what I'm doing. Once you know what your method needs to return it is very easy to write a test to use while developing the method, then plug it into the controller that needed the data.

It should be obvious, but the first thing you need is to bring the data to a consistant known state to test against. With SQLite just keep a Master copy of your test database and then copy it over your test database at the beginning of each testing round. With a real database you'll probably need to backup and then restore that Master backup for each round of testing. Our demo application never writes anything so this is pretty easy.

The sample code is in Lesson2, because all we are doing is adding the tests to Lesson2.

Create a test.

Open or create t/model_DBI.t

 use strict; use warnings; use Test::More;
 use MyApp ;
 BEGIN { use_ok 'MyApp::Model::DBI' }
 my $M = MyApp->new() ;
 ok( $M , 'Our catalyst object evaluates as true, which means we probably have one.' ) ;
 done_testing();

Run your test with

 prove -v -I./lib t/model_DBI.t

Two easy mistakes to make are to forget to 'use MyApp;' (because Begin use_ok is like eval), and to try make a new instance of MyApp::Model::DBI instead of MyApp->new(), which does not result in a usable object. If Catalyst stuff came up, you are ready to make a real test. As a precaution you might want to add Test::Deep to your Makefile, since isn't a Core Module.

Create Some Useful Tests.

Open or create t/model/Book.t and t/model_DBI-Book_Author_BookAuthor.t

t/model/Book.t

 use strict; use warnings; use Test::More; use Test::Deep
 use MyApp ;
 BEGIN { use_ok 'MyApp::Model::DBI::Book' }
 my $M = MyApp->new() ;
 my @booklist = $M->model('DBI::Book')->List_Books ;
 is( @booklist , 5, '5 Books in our list.' ) ;
 my @booknames = () ;
 my @bookids = () ;
 my @bookratings = () ;
 foreach my $book (@booklist)   { 
        push @booknames, ( $book->{ title } ) ;
        push @bookids, ( $book->{ id } ) ;
        push @bookratings, ( $book->{ rating } ) ;
        }
 is( grep( /Perl Cookbook/, @booknames), 1 , 'Everyone should have a Perl Cookbook' ) ;
 is( grep( /4/, @bookratings), 1 , '1 book only merited a 4 rating' ) ;
 is( grep( /5/, @bookratings), 4 , 'but 4 of them earned a 5' ) ;
 cmp_deeply( \@bookids, set( 1, 2, 3, 4, 5 ), 'Check that all 5 bookids came through.' ) ;
 done_testing();

Book has only one useful method, which returns an array of hashreferences. It is easy enough to count the elements in the array. Checking the values is a little more complex, here I chose to put each column of the table into an array and run tests on the array. I bring in Test::Deep to use cmp_deeply to compare an array of numbers to its expected contents, since we don't care what order our elements are in I use set.

In this case the tests for the other model will be very similar, you can see them in the source samples.

Lesson IV Best Practices for DBI Models

About the Author

John Karr is a professional Systems Administrator. Before discovering DBIx::Simple he wrote a similar utility: IhasQuery, which isn't nearly as complete or useful and is no longer developed.

I would like to thank the following who helped me in some way in writing this tutorial: Hans Staugaard.