Just a Theory

Black lives matter

Posts about Catalyst

Execute SQL Code on Connect

I’ve been writing a fair bit of PL/Perl for a client, and one of the things I’ve been doing is eliminating a ton of duplicate code by creating utility functions in the %_SHARED hash. This is great, as long as the code that creates those functions gets executed at the beginning of every database connection. So I put the utility generation code into a single function, called prepare_perl_utils(). It looks something like this:

CREATE OR REPLACE FUNCTION prepare_perl_utils(
) RETURNS bool LANGUAGE plperl IMMUTABLE AS $$
    # Don't bother if we've already loaded.
    return 1 if $_SHARED{escape_literal};

    $_SHARED{escape_literal} = sub {
        $_[0] =~ s/'/''/g; $_[0] =~ s/\\/\\\\/g; $_[0];
    };

    # Create other code refs in %_SHARED…
$$;

So now all I have to do is make sure that all the client’s apps execute this function as soon as they connect, so that the utilities will all be loaded up and ready to go. Here’s how I did it.

First, for the Perl app, I just took advantage of the DBI’s callbacks to execute the SQL I need when the DBI connects to the database. That link might not work just yet, as the DBI’s callbacks have only just been documented and that documentation appears only in dev releases so far. Once 1.611 drops, the link should work. At any rate, the use of callbacks I’m exploiting here has been in the DBI since 1.49, which was released in November 2005.

The approach is the same as I’ve described before: Just specify the Callbacks parameter to DBI->connect, like so:

my $dbh = DBI->connect_cached($dsn, $user, $pass, {
    PrintError     => 0,
    RaiseError     => 1,
    AutoCommit     => 1,
    Callbacks      => {
        connected => sub { shift->do('SELECT prepare_perl_utils()' },
    },
});

That’s it. The connected method is a no-op in the DBI that gets called to alert subclasses that they can do any post-connection initialization. Even without a subclass, we can take advantage of it to do our own initialization.

It was a bit trickier to make the same thing happen for the client’s Rails app. Rails, alas, provides no on-connection callbacks. So we instead have to monkey-patch Rails to do what we want. With some help from “dfr|mac” on #rubyonrails (I haven’t touched Rails in 3 years!), I got it worked down to this:

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
    def initialize_with_perl_utils(*args)
    returning(initialize_without_perl_utils(*args)) do
        execute('SELECT prepare_perl_utils()')
    end
    end
    alias_method_chain :initialize, :perl_utils
end

Basically, we overpower the PostgreSQL adapter’s initialize method and have it call initialize_with_perl_utils before it returns. It’s a neat trick; if you’re going to practice fuck typing, alias_method_chain makes it about as clean as can be, albeit a little too magical for my tastes.

Anyway, recorded here for posterity (my blog is my other brain!).

Looking for the comments? Try the old layout.

Testing Catalyst Template::Declare Views

Now that we have our default Catalyst tests passing, let’s have a look at testing the views we’ve created. You can follow along via the Part 6 tag tag in the GitHub repository. Start by looking at the default test script for our HTML view, t/view_HTML.t. It should look something like this:

use strict;
use warnings;
use Test::More tests => 3;
# use Test::XPath;

BEGIN {
    use_ok 'MyApp::View::HTML' or die;
    use_ok 'MyApp' or die;
}

ok my $view = MyApp->view('HTML'), 'Get HTML view object';

# ok my $output = $view->render(undef, 'hello', { user => 'Theory' }),
#     'Render the "hello" template';

# Test output using Test::XPath or similar.
# my $tx = Test::XPath->new( xml => $output, is_html => 1);
# $tx->ok('/html', 'Should have root html element');
# $tx->is('/html/head/title', 'Hello, Theory', 'Title should be correct');

Yeah, this looks a bit different that the view test created for Template Toolkit or Mason views. That’s because Catalyst::View::TD ships with its own test script template. One of the advantage is that it shows off testing the view without having to instantiate the entire app or send mock HTTP requests. These are unit tests, after all: we want to make sure that the view templates do what they want, not test an entire request process. The latter is more appropriate for integration tests, which I’ll cover later.

So let’s have a look at this test script. The first commented-out statement is:

# ok my $output = $view->render(undef, 'hello', { user => 'Theory' }),
#     'Render the "hello" template';

What this is showing us is that one can use the view’s render() method to execute a view without a context object, thus saving the expense of initializing the application. And if you have templates that don’t rely on it, I highly recommend this approach for keeping your tests fast. Even if the use of the the context object is fairly minimal, you can use Test::MockObject to mock up a context object like so:

use Test::MockObject;
my $c = Test::MockObject->new;
$c->mock(uri_for => sub { $_[1] });
$c->mock(config  => sub { { name => 'MyApp' } });
$c->mock(debug   => sub { });
$c->mock(log     => sub { });

ok my $output = $view->render($c, 'hello', { user => 'Theory' }),
    'Render the "hello" template';

Then you can use the mock() method to mock more methods as your template uses them.

Alas, our app has already passed the point where that seems worthwhile. So far we have just one template, books/list, and it requires that there also be a database statement handle available. Sure we could create a database connection and prepare a statement handle. But that would start to require a fair bit more code to set up. So let’s just instantiate the application object and be done with it. Change the test plan to 5:

use Test::More tests => 5;

Then change the test body after the BEGIN block to:

# Instantiate the context object and the view.
ok my $c = MyApp->new, 'Create context object';
ok my $view = $c->view('HTML'), 'Get HTML view object';

# Create a statement handle for books/list.
my $sth = $c->conn->run(sub { $_->prepare(q{
    SELECT isbn, title, rating, authors FROM books_with_authors
}) });
$sth->execute;

# Render books/list.
ok my $output = $view->render($c, 'books/list', {
    title => 'Book List',
    books => $sth,
}), 'Render the "books/list" template';

This allows us to get a full test of the view.

% prove --lib --verbose t/view_HTML.t
t/view_HTML.t .. 
1..5
ok 1 - use MyApp::View::HTML;
ok 2 - use MyApp;
ok 3 - Create context object
ok 4 - Get HTML view object
Explicit blessing to '' (assuming package main) at /usr/local/lib/perl5/site_perl/5.10.1/Catalyst.pm line 1281.
Explicit blessing to '' (assuming package main) at /usr/local/lib/perl5/site_perl/5.10.1/Catalyst.pm line 1281.
Explicit blessing to '' (assuming package main) at /usr/local/lib/perl5/site_perl/5.10.1/Catalyst.pm line 1281.
Explicit blessing to '' (assuming package main) at /usr/local/lib/perl5/site_perl/5.10.1/Catalyst.pm line 1281.
ok 5 - Render the "books/list" template
ok
All tests successful.
Files=1, Tests=5,  1 wallclock secs ( 0.02 usr  0.00 sys +  0.69 cusr  0.06 csys =  0.77 CPU)
Result: PASS

Hrm. Those warnings are rather annoying. Looking at Catalyst.pm I see that they come from the uri_for() method. I expect that they somehow result from a lack of state in the context object. That’s not really important for our unit tests, so let’s just mock that one method to do something reasonable. Add this code after instantiating the context object but before rendering the view:

use Test::MockObject::Extends;
my $mocker = Test::MockObject::Extends->new($c);
$mocker->mock( uri_for => sub { $_[1]} );

And now we get:

% prove --lib --verbose t/view_HTML.t
t/view_HTML.t .. 
1..5
ok 1 - use MyApp::View::HTML;
ok 2 - use MyApp;
ok 3 - Create context object
ok 4 - Get HTML view object
ok 5 - Render the "books/list" template
ok
All tests successful.
Files=1, Tests=5,  1 wallclock secs ( 0.02 usr  0.01 sys +  0.77 cusr  0.07 csys =  0.87 CPU)
Result: PASS

Ah, much better! And thanks to our mock, we also have a much better idea of what will be returned from uri_for(), which will be important for later tests.

Now that we have things properly mocked up and the objects created such that we can actually get the template to render, it’s time to test the output from the template. For HTML and XML format, I like the Test::XPath module. In fact, it’s for this very use that I wrote Test::XPath. It’s great because it allows me to effectively test the correctness of the template output. Here’s the basic outline:

# Test output using Test::XPath.
my $tx = Test::XPath->new( xml => $output, is_html => 1);
test_basics($tx, 'Book List');

# Call this function for every request to make sure that they all
# have the same basic structure.
sub test_basics {
    my ($tx, $title) = @_;

    # Some basic sanity-checking.
    $tx->is( 'count(/html)',      1, 'Should have 1 html element' );
    $tx->is( 'count(/html/head)', 1, 'Should have 1 head element' );
    $tx->is( 'count(/html/body)', 1, 'Should have 1 body element' );

    # Check the head element.
    $tx->is(
        '/html/head/title',
        $title,
        'Title should be corect'
    );
    $tx->is(
        '/html/head/link[@type="text/css"][@rel="stylesheet"]/@href',
        '/static/css/main.css',
        'Should load the CSS',
    );
}

I’ve set up the test_basics() function to test the things that should be mostly the same for every request. This will mainly cover the output of the wrapper, and includes things like making sure that there is just one <html> tag, one <head> tag, and one <body> tag; and that the title and CSS-related elements are output properly. Running this (with the test plan set to no_plan as I develop), I get:

% prove --lib t/view_HTML.tt
t/view_HTML.t .. 2/? 
#   Failed test 'Should load the CSS'
#   at t/view_HTML.t line 52.
#          got: ''
#     expected: '/static/css/main.css'
# Looks like you failed 1 test of 10.
t/view_HTML.t .. Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/10 subtests 

Test Summary Report
-------------------
t/view_HTML.t (Wstat: 256 Tests: 10 Failed: 1)
  Failed test:  10
  Non-zero exit status: 1
Files=1, Tests=10,  1 wallclock secs ( 0.02 usr  0.01 sys +  0.79 cusr  0.08 csys =  0.90 CPU)
Result: FAIL

Hrm. Let’s stick a diag $output in there and see what we get. Now the output includes this bit:

# <html>
#  <head>
#   <title>Book List</title>
#   <link rel="stylesheet" href="/static/css/main.css" />
#  </head>

Ah! the <link> element for the stylesheet is missing the type attribute. So let’s add it. Edit lib/MyApp/Templates/HTML.pm and change the proper bit of the wrapper template to:

link {
    rel is 'stylesheet';
    type is 'text/css';
    href is $c->uri_for('/static/css/main.css' );
};

Note the addition of the type attribute. Now when we run the tests (removing the diag), we get:

% prove --lib t/view_HTML.t
t/view_HTML.t .. ok    
All tests successful.
Files=1, Tests=10,  1 wallclock secs ( 0.02 usr  0.00 sys +  0.78 cusr  0.07 csys =  0.87 CPU)
Result: PASS

Ah, much better! A lot more testing should go in there to make sure that the wrapper is doing things right. I’ve committed such testing, so check it out.

Now we need to test the output specific to the books/list template. Below the call to test_basics(), add this code:

$tx->ok('/html/body/div[@id="bodyblock"]/div[@id="content"]/table', sub {
    $_->is('count(./tr)', 6, 'Should have seven rows' );
    $_->ok('./tr[1]', sub {
        $_->is('count(./th)', 3, 'Should have three table headers');
        $_->is('./th[1]', 'Title', '... first is "Title"');
        $_->is('./th[2]', 'Rating', '... second is "Rating"');
        $_->is('./th[3]', 'Authors', '... third is "Authors"');
    }, 'Should have first table row')
}, 'Should have a table');

Notice the nested block there? Test::XPath supports passing blocks to its ok() method, so that you can naturally scope your tests to blocks of XML and HTML. Neat, huh? If you don’t like the use of $_, the test object is also passed as the sole argument to such blocks.

Anyway, these tests makes sure that the table is where it should be, has the proper number of rows, and that the first row has three headers with their proper values. The test outputs:

% prove --lib t/view_HTML.tt
t/view_HTML.t .. 1/? 
#   Failed test '... third is "Authors"'
#   at t/view_HTML.t line 42.
#          got: 'Author'
#     expected: 'Authors'
# Looks like you failed 1 test of 28.
t/view_HTML.t .. Dubious, test returned 1 (wstat 256, 0x100)
Failed 1/28 subtests 

Test Summary Report
-------------------
t/view_HTML.t (Wstat: 256 Tests: 28 Failed: 1)
  Failed test:  28
  Non-zero exit status: 1
Files=1, Tests=28,  1 wallclock secs ( 0.03 usr  0.01 sys +  0.79 cusr  0.08 csys =  0.91 CPU)
Result: FAIL

Whoops! Looks like I forgot to change the header when I changed the template to output a list of authors last week. So edit lib/MyApp/Templates/HTML/Books.pm and change the template to output “Authors” instead of “Author”:

row {
    th { 'Title'   };
    th { 'Rating'  };
    th { 'Authors' };
};

And now all tests pass again:

% prove --lib t/view_HTML.t
t/view_HTML.t .. ok    
All tests successful.
Files=1, Tests=28,  1 wallclock secs ( 0.02 usr  0.01 sys +  0.78 cusr  0.09 csys =  0.90 CPU)
Result: PASS

Great. So let’s finish testing the rest of the output. Ah, but wait! We have on ORDER BY clause on the query, so the order in which the books will be output is undefined. So let’s add an ORDER BY clause. Change the creation of the statement handle in the test file to:

my $sth = $c->conn->run(sub { $_->prepare(q{
    SELECT isbn, title, rating, authors
        FROM books_with_authors
        ORDER BY title
}) });

And now you can start to see why I use the q{} operator for SQL queries. You should also note that the inputs for the view test are now different than those from the controller, which still has no ORDER BY clause. It’s likely that we’ll want to go back and change that later, but I bring it up here to highlight the difference from integration tests – and to emphasize that we’ll need to write those integration tests at some point!

But back to the view unit tests. We can now test the contents of the table by adding code after the test for ./tr[1]. Here’s what the test for the next row looks like:

$_->ok('./tr[2]', sub {
    $_->is('count(./td)', 3, 'Should have three cells');
    $_->is(
        './td[1]',
        'CCSP SNRS Exam Certification Guide',
        '... first is "CCSP SNRS Exam Certification Guide"'
    );
    $_->is('./td[2]', 5, '... second is "5"');
    $_->is(
        './td[3]',
        'Bastien, Nasseh, Degu',
        '... third is "Bastien, Nasseh, Degu"',
    );
}, 'Should have second table row');

The other rows can be similarly tested; have a look at the commit to see all the new tests.

This reminds me, however, that we never created an order for the list of authors. So it’s possible that this test could fail, as the order of the author last names is undefined. We should go back and fix that, probably by listing the authors as they are actually listed on the cover of the book. But in the meantime, our test of this view is done.

Next up, I think I’ll hit controller tests. So come on back!

Looking for the comments? Try the old layout.

Testing the Tutorial App

Yet another entry in my ongoing attempt to rewrite the Catalyst tutorial in my own coding style.

So far, I’ve been following the original tutorial pretty closely. But now I want to skip ahead a bit to chapter 8: testing. I skip because, really, we should be writing tests from the very beginning. They shouldn’t be an afterthought stuck in the penultimate chapter of a tutorial. So let’s write some tests. You can follow along in the Part 5 tag in the GitHub repository.

Oops, A Missing Dependency

Oh, wait! I forgot to tell the build system that we now depend on Catalyst::View::TD and DBIx::Connector. So add these two lines to Makefile.PL:

requires 'Catalyst::View::TD' => '0.11';
requires 'DBIx::Connector' => '0.30';

Okay, now we can write some tests.

STFU

Well, no, actually, let’s start by running the tests we have:

perl Makefile.PL
make test

You should see some output after this — lots of stuff, actually — ending something like this:

[debug] Loaded Path actions:
.-------------------------------------+--------------------------------------.
| Path                                | Private                              |
+-------------------------------------+--------------------------------------+
| /                                   | /index                               |
| /                                   | /default                             |
| /books                              | /books/index                         |
| /books/list                         | /books/list                          |
'-------------------------------------+--------------------------------------'

[info] MyApp powered by Catalyst 5.80013
t/view_HTML.t ......... ok   
All tests successful.
Files=5, Tests=8,  3 wallclock secs ( 0.04 usr  0.02 sys +  2.19 cusr  0.25 csys =  2.50 CPU)
Result: PASS

I don’t know about you, but having all that debugging crap just drives me nuts while I’m running tests. It’s helpful while doing development, but mainly just gets in the way of the tests. So let’s get rid of them. Open up lib/MyApp.pm and change the use Catalyst statement to:

use Catalyst (qw(
    ConfigLoader
    Static::Simple
    StackTrace
), $ENV{HARNESS_ACTIVE} ? () : '-Debug');

Essentially, we’re just turning on the debugging output only if the test harness is not active. Now when we run the tests, we get:

t/01app.t ............. ok   
t/02pod.t ............. skipped: set TEST_POD to enable this test
t/03podcoverage.t ..... skipped: set TEST_POD to enable this test
t/controller_Books.t .. ok   
t/view_HTML.t ......... ok   
All tests successful.
Files=5, Tests=8,  3 wallclock secs ( 0.04 usr  0.02 sys +  2.15 cusr  0.23 csys =  2.44 CPU)
Result: PASS

Much better. Now I can actually see other stuff, such as the fact that I’m skipping POD tests. Personally, I like to make sure that POD tests run all the time, as I’m likely to forget to set the environment variable. So let’s edit t/02pod.t and t/03podcoverage.t and delete this line from each:

plan skip_all => 'set TEST_POD to enable this test' unless $ENV{TEST_POD};

So what does that get us?

t/01app.t ............. ok   
t/02pod.t ............. ok     
t/03podcoverage.t ..... 1/6 
#   Failed test 'Pod coverage on MyApp::Controller::Books'
#   at /usr/local/lib/perl5/site_perl/5.10.1/Test/Pod/Coverage.pm line 126.
# Coverage for MyApp::Controller::Books is 50.0%, with 1 naked subroutine:
#   list

#   Failed test 'Pod coverage on MyApp::Controller::Root'
#   at /usr/local/lib/perl5/site_perl/5.10.1/Test/Pod/Coverage.pm line 126.
# Coverage for MyApp::Controller::Root is 66.7%, with 1 naked subroutine:
#   default
# Looks like you failed 2 tests of 6.
t/03podcoverage.t ..... Dubious, test returned 2 (wstat 512, 0x200)
Failed 2/6 subtests 
t/controller_Books.t .. ok   
t/view_HTML.t ......... ok   

Test Summary Report
-------------------
t/03podcoverage.t   (Wstat: 512 Tests: 6 Failed: 2)
  Failed tests:  2-3
  Non-zero exit status: 2
Files=5, Tests=25,  3 wallclock secs ( 0.05 usr  0.02 sys +  2.82 cusr  0.29 csys =  3.18 CPU)
Result: FAIL
Failed 1/5 test programs. 2/25 subtests failed.

Well that figures, doesn’t it? We added the list action to MyApp::Controller Books but never documented it. And for some reason, Catalyst creates the default action in MyApp::Controller::Root with no documentation. Such a shame. So let’s document those methods. Add this to t/lib/MyApp/Controller/Root.pm:

=head2 default

The default action. Just returns a 404/NOT FOUND error. Might want to update
later with a template to format the error like the rest of our site.

=cut

While there, I notice that the index action has a doc header, but nothing to actually describe what it does. Let’s fix that, too:

The default Catalyst action, which just displays the welcome message. This is
the "Yay it worked!" page. Consider changing to a real home page for our app.

Great. Now open t/lib/MyApp/Controller/Books.pm and document the list action:

=head2 list

Looks up all of the books in the system and executes a template to display
them in a nice table. The data includes the title, rating, and authors of each
book

=cut

Oh hey, look at that. There’s an index method that doesn’t do anything. And it has a POD header and no docs, too. So let’s document it:

The default method for the books controller. Currently just says that it
matches the request; we'll likely want to change it to something more
reasonable down the line.

Okay, so how do the tests look now?

t/01app.t ............. ok   
t/02pod.t ............. ok     
t/03podcoverage.t ..... ok   
t/controller_Books.t .. ok   
t/view_HTML.t ......... ok   
All tests successful.
Files=5, Tests=25,  3 wallclock secs ( 0.05 usr  0.02 sys +  2.82 cusr  0.31 csys =  3.20 CPU)
Result: PASS

Excellent! Now, the truth is that we didn’t document our templates, either. Test::Pod doesn’t cotton on to that fact because they’re not installed like normal subroutines in the test classes. So it’s up to us to document them ourselves. (Note to self: Consider adding a module to test that all Template::Declare classes have docs for all of their templates.) I’ll wait here while you do that.

All done? Great! I had actually planned to start testing the view next, but I think this is enough for today. Stay tuned for more testing goodness.

Looking for the comments? Try the old layout.

Tutorial on GitHub

Following a very good suggestion from Pedro Melo, I’ve created a Git repository for this tutorial and put it on GitHub. I replayed each step, making each into its own commit, and tagged the state of the code for each entry:

So as I continue to make modifications, I’ll keep this repository up-to-date, and tag things as of each blog entry. This will make it easy for you to follow along; you can simply clone the repository and git pull for each post.

More soon.

Looking for the comments? Try the old layout.

My Catalyst Tutorial: Add Authors to the View

Another post in my ongoing series of posts on using Catalyst with Template::Declare and DBIx::Connector. This will be the last post covering material from chapter 3, I promise. This is a fun one, though, because we continue to use this really nice DSL called “SQL,” which I think is more expressive than an ORM would be.

To whit, the next task is to add the missing list of authors to the book list. The thing is, the more I work with databases, the more I’m inclined to think about them not only as the “M” in “MVC”, but also the “V”. I’ll show you what I mean.

A Quick Fix

But first, a diversion. In the second post in this series, I created an SQL statement to insert book authors, but I made a mistake: the values for surnames and given names were reversed. Oops. Furthermore, I included explicit author IDs, even though the id column uses a sequence for it’s default value. So first we need to fix these issues. Change the INSERT INTO authors statement in sql/001-books.sql to:

INSERT INTO authors (surname, given_name)
VALUES ('Bastien',      'Greg'),
       ('Nasseh',       'Sara'),
       ('Degu',         'Christian'),
       ('Stevens',      'Richard'),
       ('Comer',        'Douglas'),
       ('Christiansen', 'Tom'),
       ('Torkington',   'Nathan'),
       ('Zeldman',      'Jeffrey')
;

This time, we’re letting the sequence populate the id column. Fortunately, it starts from 1 just like we did, so we don’t need to update the values in the INSERT INTO book_author statement. Now let’s fix the database:

DELETE FROM book_author;
DELETE FROM authors;

Then run the above SQL query to restore the authors with their proper names, and then run the INSERT INTO book_author statement. That will get us back in business.

Constructing our Query

Now it’s time for the fun. The original SQL query we wrote to get the list of books was:

SELECT isbn, title, rating FROM books;

Nothing unusual there. But to get at the authors, we need to join to book_author and from there to authors. Our first cut looks like this:

SELECT b.isbn, b.title, b.rating, a.surname
  FROM books       b
  JOIN book_author ba ON b.isbn       = ba.isbn
  JOIN authors     a  ON ba.author_id = a.id;

Which yields this data:

       isbn        |               title                | rating |   surname    
-------------------+------------------------------------+--------+--------------
 978-1-58720-153-0 | CCSP SNRS Exam Certification Guide |      5 | Bastien
 978-1-58720-153-0 | CCSP SNRS Exam Certification Guide |      5 | Nasseh
 978-1-58720-153-0 | CCSP SNRS Exam Certification Guide |      5 | Degu
 978-0-201-63346-7 | TCP/IP Illustrated, Volume 1       |      5 | Stevens
 978-0-13-018380-4 | Internetworking with TCP/IP Vol.1  |      4 | Comer
 978-1-56592-243-3 | Perl Cookbook                      |      5 | Christiansen
 978-1-56592-243-3 | Perl Cookbook                      |      5 | Torkington
 978-0-7357-1201-0 | Designing with Web Standards       |      5 | Zeldman

Good start, but note how we now have three rows for “CCSP SNRS Exam Certification Guideâ€? and two for “Perl Cookbookâ€?. We could of course modify our Perl code to look at the ISBN in each row and combine as appropriate, but it’s better to get the database to do that work, since it’s designed for that sort of thing. So let’s use an aggregate function to combine the values over multiple rows into a single row. All we have to do is use the column that changes (surname) in an aggregate function and tell PostgreSQL to use the other columns to group rows into one. PostgreSQL 8.4 introduces a really nice aggregate function, array_agg(), for pulling a series of strings together into an array. Let’s put it to use:

SELECT b.isbn, b.title, b.rating, array_agg(a.surname) as authors
  FROM books       b
  JOIN book_author ba ON b.isbn     = ba.isbn
  JOIN authors     a  ON ba.author_id = a.id
 GROUP BY b.isbn, b.title, b.rating;

Now the output is:

       isbn        |               title                | rating |          authors         
-------------------+------------------------------------+--------+--------------------------
 978-0-201-63346-7 | TCP/IP Illustrated, Volume 1       |      5 | {Stevens}
 978-0-13-018380-4 | Internetworking with TCP/IP Vol.1  |      4 | {Comer}
 978-1-56592-243-3 | Perl Cookbook                      |      5 | {Christiansen,Torkington}
 978-1-58720-153-0 | CCSP SNRS Exam Certification Guide |      5 | {Bastien,Nasseh,Degu}
 978-0-7357-1201-0 | Designing with Web Standards       |      5 | {Zeldman}

Much better. We now have a single row for each book, and the authors are all grouped into a single column. Cool. But we can go one step further. Although we could use Perl to turn the array of author surnames into a comma-delimited string, there’s a PostgreSQL function for that, too: array_to_string(). Check it out:

SELECT b.isbn, b.title, b.rating,
       array_to_string(array_agg(a.surname), ', ') as authors
  FROM books       b
  JOIN book_author ba ON b.isbn     = ba.isbn
  JOIN authors     a  ON ba.author_id = a.id
 GROUP BY b.isbn, b.title, b.rating;

Now the rows will be:

       isbn        |               title                | rating |          authors          
-------------------+------------------------------------+--------+--------------------------
 978-0-201-63346-7 | TCP/IP Illustrated, Volume 1       |      5 | Stevens
 978-0-13-018380-4 | Internetworking with TCP/IP Vol.1  |      4 | Comer
 978-1-56592-243-3 | Perl Cookbook                      |      5 | Christiansen, Torkington
 978-1-58720-153-0 | CCSP SNRS Exam Certification Guide |      5 | Bastien, Nasseh, Degu
 978-0-7357-1201-0 | Designing with Web Standards       |      5 | Zeldman

Create a Database View

Cool! All the formatting work is done! But since it’s likely what we’ll often need to fetch book titles along with their authors, let’s create an SQL view for this query. That way, we don’t have to write the same SQL in different places in the application: we can just use the view. So create a new file, sql/002-books_with_authors.sql, and add this SQL:

CREATE VIEW books_with_authors AS
SELECT b.isbn, b.title, b.rating,
       array_to_string(array_agg(a.surname), ', ') as authors
  FROM books       b
  JOIN book_author ba ON b.isbn     = ba.isbn
  JOIN authors     a  ON ba.author_id = a.id
 GROUP BY b.isbn, b.title, b.rating;

Now install this view in the database:

psql -U postgres -d myapp -f sql/002-books_with_authors.sql

And now we can make use of the view any time we want and get the results of the full query. It’s time to do that in our controller. Edit lib/MyApp/Controller/Books.pm and change this line in the list action:

my $sth = $_->prepare('SELECT isbn, title, rating FROM books');

To:

my $sth = $_->prepare(q{
    SELECT isbn, title, rating, authors FROM books_with_authors
});

The use of the q{} operator is a style I use for SQL queries in Perl code; you can use whatever style you like. Since this is a very short SQL statement (thanks to the view), it’s not really necessary to have it on multiple lines, but I like to be fairly consistent about this sort of thing.

The last thing we need to do is a a very simple change to the list template in lib/MyApp/Templates/HTML/Books.pm. In previous posts, I was referring to the non-existent “author” key in the each hash reference fetched from the database. In the new view, however, I’ve named that column “authors”. So change this line:

cell { $book->{author} };

To

cell { $book->{authors} };

And that’s it. Restart the server and reload http://localhost:3000/books/list and you should now see all of the books listed with their authors.

Notes

I think you can appreciate why, to a certain degree, I’m starting to think of the database as handling both the “M” and the “V” in “MVC”. It’s no mistake that the database object we created is known as a “view”. It was written in such a way that it not only expressed the relationship between books and authors in a compact but clear way, but it formatted the appropriate data for publishing on the site—all in a single, efficient query. All the Template::Declare view does is wrap it all up in the appropriate HTML.

PostgreSQL isn’t the only database to support feature such as this, by the way. All of the databases I’ve used support views, and many offer useful aggregate functions, as well. Among the MySQL aggregates, for example, is group_concat(), which sort of combines the array_to_string(array_agg()) PostgreSQL syntax into a single function. And I’ve personally written a custom aggregate for SQLite in Perl. So although I use PostgreSQL for these examples and make use of its functionality, you can do much the same thing in most other databases.

Either way, I find this to be a lot less work than using an ORM or other abstraction layer between my app and the database. Frankly, SQL provides just the right level of abstraction.

Looking for the comments? Try the old layout.

Create a Template::Declare Wrapper

Next in my ongoing series of posts on using Catalyst with Template::Declare and DBIx::Connector, we pick up again in chapter 3 to create a wrapper for the view. I added the wrapper support to Template::Declare over a year ago, and while the idea is sound, the interface makes it feel like it’s bolted on. See if you agree with me.

Returning to the MyApp project, open lib/MyApp/Templates/HTML.pm and implement a wrapper like so:

use Sub::Exporter -setup => { exports => [qw(wrapper) ] };

create_wrapper wrapper => sub {
    my ($code, $c, $args) = @_;
    xml_decl { 'xml', version => '1.0' };
    outs_raw '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" '
            . '"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">';
    html {
        head {
            title { $args->{title} || 'My Catalyst App!' };
            link {
                rel is 'stylesheet';
                href is $c->uri_for('/static/css/main.css' );
            };

        };

        body {
            div {
                id is 'header';
                # Your logo can go here.
                img {
                    src is $c->uri_for('/static/images/btn_88x31_powered.png');
                };
                # Page title.
                h1 { $args->{title} || $c->config->{name} };
            }; # end header.

            div {
                id is 'bodyblock';
                div {
                    id is 'menu';
                    h3 { 'Navigation' };
                    ul {
                        li {
                            a {
                                href is $c->uri_for('/books/list');
                                'Home';
                            };
                        };
                        li {
                            a {
                                href is $c->uri_for('/');
                                title is 'Catalyst Welcome Page';
                                'Welcome';
                            };
                        };
                    };
                }; # end menu

                div {
                    id is 'content';
                    # Status and error messages.
                    if (my $msg = $args->{status_msg}) {
                        span { class is 'message'; $msg };
                    }
                    if (my $err = $args->{error_msg}) {
                        span { class is 'error'; $err };
                    }

                    # Output the template contents.
                    $code->($args);
                }; # end content

            }; # end bodyblock
        };
    };
};

This looks like more work than it is because of the copious use of whitespace I’ve used. Personally, I find the pure Perl syntax easier to read than the mix of HTML and TT syntax in the Template Toolkit wrapper. Anyway, this is a nearly direct port of the Template Toolkit wrapper from the tutorial. Template::Declare wrappers expect at least one argument: a code reference that will output the content of the main template. You can see it used here near the end of the code, with the line $code->($args).

Unfortunately, Template::Declare doesn’t make such wrappers easily available to templates. So we have to add the Sub::Exporter line at the top to export the wrapper function it creates, named wrapper.

Next, open up lib/MyApp/Templates/HTML/Books.pm and edit the list template to take advantage of the wrapper. The new code looks like this:

use MyApp::Templates::HTML 'wrapper';

template list => sub {
    my ($self, $args) = @_;
    wrapper {
        table {
            row {
                th { 'Title'  };
                th { 'Rating' };
                th { 'Author' };
            };
            my $sth = $args->{books};
            while (my $book = $sth->fetchrow_hashref) {
                row {
                    cell { $book->{title}  };
                    cell { $book->{rating} };
                    cell { $book->{author} };
                };
            };
        };
    } $self->c, $args;
};

First we import the wrapper function from MyApp::Templates::HTML, and then we simply use it to wrap the contents of our template. Note that the context object and template arguments must be passed on to the wrapper; they’re not provided to the wrapper by Template::Declare. That’s something else I’d like to adjust.

In the meantime, contrary to the tutorial, I don’t think the template should set the title of the page. It seems to me that’s more the responsibility of the controller. So while this template could easily add a title key to the $args hash before passing it on to the wrapper, I recommend editing the list action in MyApp::Controller::Books instead:

sub list : Local {
    my ($self, $c) = @_;
    my $stash = $c->stash;
    $stash->{title} = 'Book List';
    $stash->{books} = $c->conn->run(fixup => sub {
        my $sth = $_->prepare('SELECT isbn, title, rating FROM books');
        $sth->execute;
        $sth;
    });
}

So, with the wrapper in place, let’s create the stylesheet the wrapper uses:

$ mkdir root/static/css

Then open root/static/css/main.css and add the following content:

#header {
    text-align: center;
}
#header h1 {
    margin: 0;
}
#header img {
    float: right;
}
#footer {
    text-align: center;
    font-style: italic;
    padding-top: 20px;
}
#menu {
    font-weight: bold;
    background-color: #ddd;
    float: left;
    padding: 0 0 50% 5px;
}
#menu ul {
    margin: 0;
    padding: 0;
    list-style: none;
    font-weight: normal;
    background-color: #ddd;
    width: 100px;
}
#content {
    margin-left: 120px;
}
.message {
    color: #390;
}
.error {
    color: #f00;
}

Now restart the app as usual and reload the books list at http://localhost:3000/books/list. You should now see a nicely formatted page with the navigation and header stuff, as well as the book list. You can change the CSS and the wrapper to modify the overall look of your site, and then use the wrapper in all of your page request templates to get the same look and feel across your site.

While this works, I’m not satisfied with the overall interface for Template::Declare wrappers. The need to explicitly export them and pass arguments is annoying. Maybe the Jifty guys have some other approach that works better. But if not, I’ll likely go back to the drawing board on wrappers and see how they can be made better.

Next up: More database fun!

Looking for the comments? Try the old layout.

Catalyst with DBIx::Connector and Template::Declare

Following up on my post yesterday introducing Catalyst::View::TD, today I’d like to continue with the next step in chapter 3 of the Catalyst tutorial. The twist here is that I’m going to use PostgreSQL for the database back-end and start introducing some database best practices. I’m also going to make use of my DBIx::Connector module to interact with the database.

Create the Database

Picking up with the database creation section of the tutorial, the first change I’d like to make is to use a natural key for the books table. All books have unique identifiers, known as ISBNs, so creating a surrogate key (the typical id column in ORM-managed applications) is redundant. One of the nice things about PostgreSQL is that it ships with a contributed library, isn, which validates ISBN and other international identifiers. So we use this contrib module (usually included in package-installed PostgreSQL servers) for the primary key for books. If you need to install it from source, it’s pretty easy:

cd postgresql-8.4.1/contrib/isn
make
make install

Ideally I’d use a natural key for the authors table too, but despite some attempts to create universal identifiers for authors, nothing has really caught on as far as I know. So I’ll just stick to a surrogate key for now.

First step: create the database and install isn if it’s not already included in the template database:

createdb -U postgres myapp
psql -U postgres -d myapp -f /usr/local/pgsql/share/contrib/isn.sql

The isn.sql file may be somewhere else on your system. Now let’s create the database. Create sql/001-books.sql in the MyApp directory and paste this into it:

BEGIN;

CREATE TABLE books (
    isbn   ISBN13   PRIMARY KEY,
    title  TEXT     NOT NULL DEFAULT '',
    rating SMALLINT NOT NULL DEFAULT 0 CHECK (rating BETWEEN 0 AND 5)
);

CREATE TABLE authors (
    id         BIGSERIAL PRIMARY KEY,
    surname    TEXT NOT NULL DEFAULT '',
    given_name TEXT NOT NULL DEFAULT ''
);

CREATE TABLE book_author (
    isbn       ISBN13 REFERENCES books(isbn),
    author_id  BIGINT REFERENCES authors(id),
    PRIMARY KEY (isbn, author_id)
);

INSERT INTO books
VALUES ('1587201534',        'CCSP SNRS Exam Certification Guide', 5),
       ('978-0201633467',    'TCP/IP Illustrated, Volume 1',       5),
       ('978-0130183804',    'Internetworking with TCP/IP Vol.1',  4),
       ('978-1-56592-243-3', 'Perl Cookbook',                      5),
       ('978-0735712010',    'Designing with Web Standards',       5)
;

INSERT INTO authors
VALUES (1, 'Greg',      'Bastien'),
       (2, 'Sara',      'Nasseh'),
       (3, 'Christian', 'Degu'),
       (4, 'Richard',   'Stevens'),
       (5, 'Douglas',   'Comer'),
       (6, 'Tom',       'Christiansen'),
       (7, 'Nathan',    'Torkington'),
       (8, 'Jeffrey',   'Zeldman')
;

INSERT INTO book_author
VALUES ('1587201534',        1),
       ('1587201534',        2),
       ('1587201534',        3),
       ('978-0201633467',    4),
       ('978-0130183804',    5),
       ('978-1-56592-243-3', 6),
       ('978-1-56592-243-3', 7),
       ('978-0735712010',    8)
;

COMMIT;

Yeah, I Googled for the ISBNs for those books. I found the ISBN-13 number for most of them, but it handles the old ISBN-10 format, too, automatically upgrading it to ISBN-13. I also added a CHECK constraint for the rating column, to be sure that the value is always BETWEEN 0 AND 5. I also like to include default values where it’s sensible to do so, and that syntax for inserting multiple rows at once is pretty nice to have.

Go ahead and run this against your database:

psql -U postgres -d myapp -f sql/001-books.sql

Now if you connect to the server, you should be able to query things like so:

$ psql -U postgres myapp
psql (8.4.1)
Type "help" for help.

myapp=# select * from books;
       isbn        |               title                | rating 
-------------------+------------------------------------+--------
 978-1-58720-153-0 | CCSP SNRS Exam Certification Guide |      5
 978-0-201-63346-7 | TCP/IP Illustrated, Volume 1       |      5
 978-0-13-018380-4 | Internetworking with TCP/IP Vol.1  |      4
 978-1-56592-243-3 | Perl Cookbook                      |      5
 978-0-7357-1201-0 | Designing with Web Standards       |      5
(5 rows)

Setup the Database Connection

Great! The database is set. Now we need a way for the app to talk to it. I’ve not yet decided how I’m going to integrate DBIx::Connector into a Catalyst model class; maybe I’ll figure it out as I write these posts. But since my mantra is “the database is the model,â€? for now I won’t bother with a model at all. Instead, I’ll create a simple accessor in MyApp so we can easily get at the database connection wherever we need it. To do that, add these lines to lib/MyApp.pm:

use Moose;
use DBIx::Connector;
use Exception::Class::DBI;

has conn => (is => 'ro', lazy => 1, default => sub {
    DBIx::Connector->new( 'dbi:Pg:dbname=myapp', 'postgres', '', {
        PrintError     => 0,
        RaiseError     => 0,
        HandleError    => Exception::Class::DBI->handler,
        AutoCommit     => 1,
        pg_enable_utf8 => 1,
    });
});

We load Moose to get the has keyword, the officially sanctioned interface for defining attributes in Catalyst classes. Then I use that keyword to create the conn attribute. This attribute is read-only and has a DBIx::Connector object for its default value. The nice thing about this is that the DBIx::Connector object won’t be instantiated until it’s actually needed, and then it will be kept forever. We never have to do anything else to use it.

Oh, and I like to make sure that text data coming back from PostgreSQL is properly encoded as UTF-8, and I like to use Exception::Class::DBI to turn DBI errors into exception objects.

Now it’s time to update our controller and template to fetch actual data from the database. Edit lib/MyApp/Controller/Books.pm and change the list method to:

sub list : Local {
    my ($self, $c) = @_;
    $c->stash->{books} = $c->conn->run(fixup => sub {
        my $sth = $_->prepare('SELECT isbn, title, rating FROM books');
        $sth->execute;
        $sth;
    });
}

All we’re doing here is creating a statement handle for the query, executing the query, and storing the statement handle in the stash. Now we need to update the template to use the statement handle. Open up lib/MyApp/Templates/HTML/Books.pm and change the list template to:

template list => sub {
    my ($self, $args) = @_;
    table {
        row {
            th { 'Title'  };
            th { 'Rating' };
            th { 'Author' };
        };
        my $sth = $args->{books};
        while (my $book = $sth->fetchrow_hashref) {
            row {
                cell { $book->{title}  };
                cell { $book->{rating} };
                cell { $book->{author} };
            };
        };
    };
};

All we do is fetch each row from the statement handle and output it. The only thing that’s changed is the use of the statement handle as an iterator rather than an array reference.

And now we’re set! Restart your server with script/myapp_server.pl and point your browser at http://localhost:3000/books/list. Now you should see the book titles and ratings, though the authors still aren’t present. We’ll fix that in a later post.

Takeaway

The takeaway from this post: Use PostgreSQL’s support for custom data types to create validated natural keys for your data, and use a stable, persistent database connection to talk directly to the database. No need for an ORM here, as the DBI provides a very Perlish access to a very capable DSL for models called SQL.

More soon.

Looking for the comments? Try the old layout.

Create Catalyst Views with Template::Declare

Following up on last week’s release of Template::Declare 0.41, this week I’m pleased to announce the release of a new Catalyst view class, Catalyst::View::TD.

Yes, I’m aware of Catalyst::View::Template::Declare. As I mentioned last week, it doesn’t make very good use of Template::Declare. I don’t blame jrock for that, though; Template::Declare had very poor documentation before 0.41. But now that it is properly documented and I have a pretty solid grasp of how it works, I wanted to create a new Catalyst View that could take proper advantage of its features.

If you’re a Catalyst developer, chances are that you currently use Template Toolkit or Mason for your templating needs. So why should you consider Catalyst::View::TD for your next project? How about:

  • Feature-parity with Catalyst::View::TT, the view class for Template Toolkit
  • Includes a myapp_create.pl helper for creating new template classes.
  • Intuitive, easy-to-use HTML and XML templating in Perl
  • All templates loaded at server startup time (great for forking servers like mod_perl)
  • Template paths that correspond to Controller URIs.

If you weren’t convinced by the first three points, that forth one is the killer. It’s the reason I wrote a new view. But here’s an even better reason: I’m going to show you exactly how to use it, right here in this blog post.

A Simple Hello

I’m borrowing from chapter 3 of the Catalyst tutorial. First, create a new app:

$ catalyst.pl MyApp
cd MyApp

Then update the list of plugins in MyApp.pm:

use Catalyst qw/
    -Debug
    ConfigLoader
    Static::Simple
    StackTrace
/;

Now create a controller:

$ script/myapp_create.pl controller Books

Then edit it and add this controller (see chapter 3 if you need explanation about what this does):

sub list : Local {
    my ($self, $c) = @_;
    $c->stash->{books} = [];
    $c->stash->{template} = '/books/list';
}

And now, create a view and a new template class:

$ script/myapp_create.pl view HTML TD
$ script/myapp_create.pl TDClass HTML::Books

Open lib/MyApp/Templates/HTML/Books.pm and add the list template:

my ($self, $args) = @_;
table {
    row {
        th { 'Title'  };
        th { 'Rating' };
        th { 'Author' };
    };
    for my $book (@{ $args->{books} }) {
        row {
            cell { $book->{title}  };
            cell { $book->{rating} };
            cell { $book->{author} };
        };
    }
};

Then point your browser to http://localhost:3000/books/list. If you have everything working so far, you should see a web page that displays nothing other than our column headers for “Title”, “Rating”, and “Author(s)” — we won’t see any books until we get the database and model working below.

A Few Comments

The first thing I want to draw your attention to in this example is that list template. Isn’t it a thing of beauty? It’s so easy for Perl hackers to read. Compare it to the TT example from the tutorial (with the comments removed, just to be fair):

<tr><th>Title</th><th>Rating</th><th>Author(s)</th></tr>
[% FOREACH book IN books -%]
    <tr>
    <td>[% book.title %]</td>
    <td>[% book.rating %]</td>
    <td></td>
    </tr>
[% END -%]
</table>

I mean, which would you rather have to maintain? And this is an extremely simple example. The comparison only becomes more stark when the HTML becomes more complex.

The other thing I want to point out is the name of the template class we created, MyApp::Template::HTML::Books and its template, list. They correspond perfectly with the controller, MyApp::Controller::Books, and its action list. See the parity there? The URI for the action is /books/list, and the template path, by coincidence is also /books/list. Nice, huh? Thanks to this parity, you can even remove the template specification in the controller, since by default Catalyst will render a template with the same name as the action:

sub list : Local {
    my ($self, $c) = @_;
    $c->stash->{books} = [];
}

This is the primary way in which Catalyst::View::TD differs from its predecessor. Whereas the latter would load all of the modules under the view’s namespace and shove all of their templates into root path, the former imports templates under paths that correspond to their class names. Hence the match with controller names.

Stay Tuned

It was kind of fun to subvert the Catalyst tutorial for my nefarious purposes. Maybe I’ll keep it up with more blog posts in the coming weeks that continues to do so. Not only will it let me show off how nice Template::Declare templates can be, but it will let me continue my rant against ORMs as well. Stay tuned.

Looking for the comments? Try the old layout.