Introducing DBIx::Class::ResultSet::PrettyPrint

I seldom release modules to CPAN; mainly because there’s so much great stuff there already. An answer on StackOverflow about pretty printing DBIx::Class result sets got me thinking. I then climbed onto the shoulders of several giants to create a wee module which does just that. Introducing DBIx::Class::ResultSet::PrettyPrint!

Synopsis code and example output of DBIx::Class::ResultSet::PrettyPrint

Strangely enough, I’d released DBIx::Class::ResultSet::PrettyPrint last year but had never gotten around to mentioning it anywhere. This post rectifies that omission, gives some background about the module, and discusses a small usage example.

A bit of background

One could say that this is a delta-epsilon¹ module in that it doesn’t extend things very much. Although it doesn’t constitute a large change, it does make printing DBIx::Class result sets easier. It stands upon the shoulders of several giants, so all I can claim is to have bundled the idea into a module.

The original impetus for DBIx::Class::ResultSet::PrettyPrint came from wanting to pretty print result sets in a Perl project I’ve been working on.² I find that by seeing the data within a result set, I can get a feeling from what the data looks like and what kinds of information it contains. Searching for a pretty printing module, I stumbled across an answer on StackOverflow about pretty printing DBIx::Class result sets. I remember thinking that the proposed solution looked nice and I used the pattern a couple of times in my work. I eventually realised that the approach would be easier to use as a module. Since then, I’ve found it handy as a way to get an idea of the shape of the data that I’m playing with.

I made some small changes to the solution proposed on StackOverflow. For instance, it recommended using Text::Table, but I found the table output generated by Text::Table::Tiny nicer. This is why DBIx::Class::ResultSet::PrettyPrint uses Text::Table::Tiny to generate tables. For instance, the output has + symbols at the table cell corners, which is reminiscent of how Postgres displays tables within psql. This I found to be a nice touch.

Of course, if one has large database tables with many columns and/or rows, this module might not be so useful. Yet, since it operates on result sets, one can create a result set with a subset of a given table and then pretty print that.

Although one often talks about pretty printing database tables, really the module operates on DBIx::Class::ResultSet objects. Hence, there isn’t a strict one-to-one relationship between database tables and what the pretty printer operates on. This is why the module was useful in one of my current projects: sometimes there wasn’t a database table behind the ResultSet I was investigating. For instance, by querying the database directly with psql, it wasn’t (easily) possible to work out what form the data had and what kinds of information it contained. Using DBIx::Class::ResultSet::PrettyPrint made this investigative work much easier.

So, how to use the module? A small example should make things clear.

A simple usage example

Let’s see the module in action. First off, we’ll need to install it:

$ cpanm DBIx::Class::ResultSet::PrettyPrint

This will pull in several CPAN modules, so you’ll need to wait a bit until it’s finished. For instance, on my test system, it took 22 minutes to download, build, test, and install the necessary 79 distributions. It’ll probably take less time if you’ve already got many of the upstream dependencies installed on your system.

Once that’s done, we can set up an example project. We’ll need to set up a DBIx::Class project, so there’s a bit of upfront work to do.

I’m a book fan, so let’s create a project to store metadata about some of my books. We only need one database table in this small example, so it won’t take long to set up.

I’ve got lots of books about Perl and a few about Unix, so let’s call the project “Perl and Unix library”. To give you an idea of what I mean, here’s a “shelfie”:

Create a directory for the project and change into the new directory:

$ mkdir perl-and-unix-library
$ cd perl-and-unix-library

Now we need to create the directory structure for our DBIx::Class schema:

$ mkdir -p lib/Schema/Result/

We’ll need a stub Schema package that we can use later to inspect the database’s contents. So, create a file called lib/Schema.pm and fill it with this code:

package Schema;

use strict;
use warnings;

use base qw(DBIx::Class::Schema);

__PACKAGE__->load_namespaces();

1;

# vim: expandtab shiftwidth=4

We need to tell DBIx::Class about the structure of our books table, so create a file called lib/Schema/Result/Book.pm and fill it with this content:

package Schema::Result::Book;

use strict;
use warnings;

use base qw(DBIx::Class::Core);

use lib '.t/lib';

__PACKAGE__->table('books');
__PACKAGE__->add_columns(
    id => {
        data_type => 'integer',
        size => 16,
        is_nullable => 0,
        is_auto_increment => 1,
    },
    title => {
        data_type => 'varchar',
        size => 128,
        is_nullable => 0,
    },
    author => {
        data_type => 'varchar',
        size => 128,
        is_nullable => 0,
    },
    pub_date => {
        data_type => 'date',
        is_nullable => 0,
    },
    num_pages => {
        data_type => 'integer',
        size => 16,
        is_nullable => 0,
    },
    isbn => {
        data_type => 'varchar',
        size => 32,
        is_nullable => 0,
    },
);

__PACKAGE__->set_primary_key('id');

1;

# vim: expandtab shiftwidth=4

This defines our books database table in which we’re storing title, author, publication date, number of pages, and ISBN information about each of our books.

We’ve now got enough structure for DBIx::Class to create and query a database. That means we can add some books to the database.

Create a file in the project’s root directory called create-books-db.pl and fill it with this content:

use strict;
use warnings;

use lib './lib';

use Schema;

my $schema = Schema->connect("dbi:SQLite:books.db");
$schema->deploy( { add_drop_table => 1 } );

my $books = $schema->resultset('Book');
$books->create(
    {
        title     => "Programming Perl",
        author    => "Tom Christiansen, brian d foy, Larry Wall, Jon Orwant",
        pub_date  => "2012-03-18",
        num_pages => 1174,
        isbn      => "9780596004927"
    }
);
$books->create(
    {
        title     => "Perl by Example",
        author    => "Ellie Quigley",
        pub_date  => "1994-01-01",
        num_pages => 200,
        isbn      => "9780131228399"
    }
);
$books->create(
    {
        title     => "Perl in a Nutshell",
        author    => "Nathan Patwardhan, Ellen Siever and Stephen Spainhour",
        pub_date  => "1999-01-01",
        num_pages => 654,
        isbn      => "9781565922860"
    }
);
$books->create(
    {
        title     => "Perl Best Practices",
        author    => "Damian Conway",
        pub_date  => "2005-07-01",
        num_pages => 517,
        isbn      => "9780596001735"
    }
);
$books->create(
    {
        title     => "Learning Perl, 7th Edition",
        author    => "Randal L. Schwartz, brian d foy, Tom Phoenix",
        pub_date  => "2016-10-05",
        num_pages => 369,
        isbn      => "9781491954324"
    }
);
$books->create(
    {
        title     => "UNIX Shell Programming",
        author    => "Stephen G. Kochan and Patrick H. Wood",
        pub_date  => "1990",
        num_pages => 502,
        isbn      => "067248448X"
    }
);

# vim: expandtab shiftwidth=4

Running this file will create an SQLite database called books.db in the same directory as the script. I.e. after running

$ perl create-books-db.pl

you should see a file called books.db in the project’s root directory.

Now we can query the data in our books database. Create a file called show-books.pl in the project base directory with this content:

use strict;
use warnings;

use lib './lib';

use DBIx::Class::ResultSet::PrettyPrint;
use Schema;  # load your DBIx::Class schema

# load your database and fetch a result set
my $schema = Schema->connect( 'dbi:SQLite:books.db' );
my $books = $schema->resultset( 'Book' );

print "Title of first entry: ", $books->find(1)->title, "\n";
print "Authors of UNIX-related titles: ",
    $books->search({ title => { -like => "%UNIX%" }})->first->author, "\n";

# vim: expandtab shiftwidth=4

Running this script will give this output:

$ perl show-books.pl
Title of first entry: Programming Perl
Authors of UNIX-related titles: Stephen G. Kochan and Patrick H. Wood

That’s all very well and good, but wouldn’t it be nice to view the database table all in one go? This is the niche task that DBIx::Class::ResultSet::PrettyPrint performs.

Change the print statements in the show-books.pl script to this:

# pretty print the result set
my $pp = DBIx::Class::ResultSet::PrettyPrint->new();
$pp->print_table( $books );

Now, when we run the script, we get this output:

$ perl show-books.pl
+----+----------------------------+-------------------------------------------------------+------------+-----------+---------------+
| id | title                      | author                                                | pub_date   | num_pages | isbn          |
+----+----------------------------+-------------------------------------------------------+------------+-----------+---------------+
| 1  | Programming Perl           | Tom Christiansen, brian d foy, Larry Wall, Jon Orwant | 2012-03-18 | 1174      | 9780596004927 |
| 2  | Perl by Example            | Ellie Quigley                                         | 1994-01-01 | 200       | 9780131228399 |
| 3  | Perl in a Nutshell         | Nathan Patwardhan, Ellen Siever and Stephen Spainhour | 1999-01-01 | 654       | 9781565922860 |
| 4  | Perl Best Practices        | Damian Conway                                         | 2005-07-01 | 517       | 9780596001735 |
| 5  | Learning Perl, 7th Edition | Randal L. Schwartz, brian d foy, Tom Phoenix          | 2016-10-05 | 369       | 9781491954324 |
| 6  | UNIX Shell Programming     | Stephen G. Kochan and Patrick H. Wood                 | 1990       | 502       | 067248448X    |
+----+----------------------------+-------------------------------------------------------+------------+-----------+---------------+

Isn’t that nice?

As I mentioned earlier, I’ve found the module quite handy when using Perl to dig around in database tables in my daily work. Maybe it can help make your work easier too!

1. This is in reference to delta-epsilon proofs in mathematics where the values delta and epsilon are very small. ↩

2. If you need someone who is stubbornly thorough, give me a yell! I’m available for freelance Python/Perl backend development and maintenance work. Contact me at paul@peateasea.de and let’s discuss how I can help solve your business’ hairiest problems. ↩

Support

If you liked this post and want to see more, please buy me a coffee!