Other pages

Things I like

NAME

DBIx::Class::Tutorial::AddingFunctionality - Expanding simple row objects into useful tools

CONTENTS

See "CONTENTS" in DBIx::Class::Tutorial

INTRODUCTION

You can already create and manipulate simple row objects with DBIx::Class. This chapter will teach you to add more functionality to those objects, such as row methods, value normalisation, validation, expanding columns values into full perl objects. We will also introduce how to access related objects such as a post for a user.

"In which the user gets an icon, creates a blog post and shows off a little"

GLOSSARY

Some terms needed for this chapter:

"Schema" in DBIx::Class::Manual::Glossary

"ResultSet" in DBIx::Class::Manual::Glossary

"Row" in DBIx::Class::Manual::Glossary

"Accessors" in DBIx::Class::Manual::Glossary

"Inflation and deflation" in DBIx::Class::Manual::Glossary

DESCRIPTION

Finding a gravatar for a user

When displaying information about your users in your applicaton, you will likely want to display an icon or image representing the user next to their name. It helps people identify each other easier, and users like that sorta thing ;)

http://www.gravatar.com is a site where users can sign up and assign an image to be associated with their email address. Application writers can request an image url from gravatar, by passing the user's email address.

The DBIx::Class::Row objects returned from create(), find() and next() are also subclasses of our Result class files. This means any methods added to the Result classes will be available. The first argument to a method, usually called $self, will be the Row object itself. This is emphasised in the examples by naming it $row.

This means we can add a method to our Row objects by creating one in Result/User.pm, something like this:

  33. use Gravatar::URL;

  34. sub gravatar {
  35.   my ($row) = @_;
  36.   return gravatar_url(
  37.                         $row->email(), 
  38.                         (default => 'identicon')
  39.                      );
  40. }

In your code, just call this method on your user objects:

  my $url = $user->gravatar();

Or in Template::Toolkit:

  <img src="[% user.gravatar %]">

Creating blog posts

So, the user is logged in and is writing a new blog post, we get them to type in a title and some text. For now we'll assume they're typing in HTML, or other suitable markup that we'll just directly display on the screen. Later you'll probably want to make sure it doesn't do anything evil, and/or use an existing markup language, like BBCode or Text::Markdown.

New posts, the long way

Creating the actual post row in the database is straight-forward, you might be thinking. We just do what we did with the user row, right? That is indeed one way to solve it:

  my $posts_rs = $schema->resultset('Post');
  my $post = $posts_rs->create(
    {
       user_id => $user->id(),
       created_date => '2008-10-18 13:00:00',
       title => $posttitle,
       post => $posttext,
    }
  );

Defaulting the created date to the current one

Clearly that date needs to be fixed though. You should probably let the database insert the correct current date for you, so lose that part of the hashref to make:

  my $post = $posts_rs->create(
    {
       user_id => $user->id(),
       title => $posttitle,
       post => $posttext,
    }
  );

And change your Result/Post.pm to the following for the created_date field:

  created_date => {
    data_type => 'datetime',
    default_value => \'CURRENT_TIMESTAMP()',
  }

The new key, default_value, is used when creating tables from the schema. When we run deploy it sets up a corresponding DEFAULT clause for the column.

Now remove the blogs.db and re-run $schema->deploy to fix the table in the database.

NB: CURRENT_TIMESTAMP is the correct default value for many databases, if yours complains, look in your database manual for help on the CREATE TABLE statement and find the correct one.

New posts, the quick way

We can also avoid manually supplying the $user->id() value, since we can use the relation we created when setting up the User class. Remember has_many('posts', ... ?

We can create the post directly belonging to the user:

  $user->posts->create(
    {
       title => $posttitle,
       post => $posttext,
    }
  );

The user_id field in the posts table will automatically get filled in as a result of the relationship between the two tables.

This can also be written as:

  $user->create_related('posts', 
    {
       title => $posttitle,
       post => $posttext,
    }
  );

For the same effect.

The post title as a unique url

We could, as many do, make our urls for blog posts just contain numbers, the IDs of the post entries. This makes it easy for the application, when a post link is clicked on, to find the correct post to display. But that's not very usable or memorable for our users, so lets make urls out of the titles instead.

To convert a title to a url, we can start by adding a method to the Result/Post.pm class to return a converted value:

  1.  sub title_as_url {
  2.    my ($row) = @_;
  3.    my $url = $row->title();
  4.    $url =~ s/\s+/ /; 
  5.    $url =~ s/\s/_/;
  6.    $url = lc($title);

  7.    return $url;
  8.  }

So, if the input is something like The DBIx-Class Tutorial it will be turned into the_dbix-class_tutorial. Not perfect, but good enough for now.

And in our template, we can use it like:

  <a href="/[% post.title_as_url %].html">

Not too hard at all. Unfortunately now we have no way of finding out which post that was, when we recieve the url and need to display the post. It would probably be better if we save the url in the database as well, and make sure it's always set to the correct normalisation of the title.

Replacing column accessors

We add the new column to the list in Result/Post.pm:

  title_as_url => {
    data_type => 'varchar',
    size => 255,
    default_value => '',
    accessor => '_title_as_url',
  }

Again you need to remove the blogs.db and re-run $schema->deploy to fix the table in the database.

I've added another new key to the column definition, accessor. This one tells DBIx::Class to create the accessor method for this column as _title_as_url. We will keep our implementation of title_as_url and this is the one that gets called whenever we fetch the value of that field.

We amend it a bit to write the new value to the database:

  sub title_as_url {
    my ($row) = @_;
    my $url = $row->title();
    $url =~ s/\s+/ /; 
    $url =~ s/\s/_/;
    $url = lc($title);

    $self->_title_as_url($url);
    $self->update() if($self->in_storage);

    return $url;
  }

The accessor protects the internal value of the url. It will ignore all attempts to set the title_as_url value directly. When called to return the correct url for the post, it will calculate the value from the title, and store it in the row object using our replaced accessor, _title_as_url.

The update call ensures that the new value is written to the database. First we check in_storage which returns true if this row is already in the database (it is true after create and find etc).

We also do not do unnecessary updates if the value has not changed, DBIx::Class does this for us, when setting a new value via an accessor, it checks to see if it is identical with the previous one. If they are not the same, the column is marked internally as dirty. When update runs, it only actually sends an UPDATE to the database, if at least one column is marked dirty.

More on update for row objects at update.

Searching for posts by unique url

Now all that's left to do is make sure we can search for each post by it's unique url, by creating a unique constraint on it.

  __PACKAGE__->add_unique_constraint('titleurl', ['title_as_url']);

When we get a request for a url now, we can get the title part of the url string, and look it up.

  my ($titleurl) = $request->uri =~ m{[^/]+\.html$};
  my $post = $posts_rs->find(
    {
       title_as_url => $titleurl
    },
    {
       key = 'titleurl'
    }
  );

Displaying blog posts

Now we have some blog posts for our user. We'd probably like to display a page of them.

Simple display

To get all the posts created by a particular user, we fetch the user object as usual, then use the relationship to the post table, posts. This returns a ResultSet object with the join condition between users and posts in it. So we can do:

  my $posts = $user->posts;
  while (my $post = $posts->next) {
    print $post->title, "\n";
  }

Or in Template Toolkit:

  [% SET posts = user.posts;
     WHILE (post = posts.next);
  %]
     <a href="/[% post.title_as_url %]">Permanent link</a>
     <h2>[% post.title %]</h2>
     <p>[% post.post %]</p>
  [% END %]

Avoid doing, however sensible it may seem:

  while (my $post = $user->posts->next) {
    print $post->title, "\n";
  }

As this will call posts to create a new related resultset every time, then call next on that new resultset, always returning the first post. So it will loop infinitely.

Fetching and displaying post dates

In "Defaulting the created date to the current one" we instructed the database to default the created_date of each post, to the date and time it was inserted into the database.

If you've experimented you might have noticed that the resulting $post object did not return a value for the created_date accessor. DBIx::Class only fetches defaulted primary key values for you, any other values will not be present in the object returned by create. To fix this we can re-fetch the entire objects data from the database:

  $post->discard_changes;

This issues an SQL SELECT statement to fetch all the columns, based on the primary key value in the object. It updates the object in place.

So now we can do:

  print $post->created_date;
  # 2008-10-30T13:00:02

You'll notice that the result is just a string, output in whatever format the database chooses to store it in. It will not necessarily be usefully formatted or corrected for the users timezone.

It would be much more useful if we could turn it into a DateTime object instead. We could just choose a formatter and apply it every time, or we can ask DBIx::Class to do it through the power of DBIx::Class::InflateColumn::DateTime.

We just need to amend our load_components statement in Result/Post.pm:

  __PACKAGE__->load_components('InflateColumn::DateTime', 'Core');

The InflateColumn::DateTime component will set up inflation and deflation for us for every column marked as type datetime, timestamp, or date. It even picks a suitable formatter for us based on the type of database we are using with our schema at any particular moment.

Just that one addition, and now we can do:

  print $post->created_date->ymd;
  # 2008-10-30

Very handy!

Paged display

After a while, hopefully, users will have written a fair number of blog posts. Using the "Simple display" method, the pages of posts for each user will just get longer and longer.

It would be a good idea to only display a set number of posts per page, luckily DBIx::Class helps us do that with some extra attributes to the search method. search is called on a resultset, and a has_many relationship accessor returns a resultset, which all means we can do:

  my $posts = $user->posts->search(
    {}, 
    { 
       rows => 10, 
       page => 1
    }
  );
  while (my $post = $posts->next) {
    print $post->title, "\n";
  }

The rows attribute limits the number of rows to return in total, the page attribute fetches the appropriate slice of the results for the given page number.

This prints just the first 10 items in the list. We probably want to get the most recent 10 items, so we need some ordering applied:

  my $posts = $user->posts->search(
    {}, 
    { 
      rows => 10,
      page => 1,
      order_by => [ 'created_date desc' ],
    }
  );
  while (my $post = $posts->next) {
    print $post->title, "\n";
    print "Posted on: ", $post->created_date->ymd;
  }

This is all very neat, but we can do more! It's also handy to know how many pages of posts there are, so we can output links to successive pages and the last page for ease of navigation.

To do this we can fetch a Data::Pager object containing information about the paged data, for example:

  my $posts = $user->posts->search(
   {}, 
   { 
      rows => 10,
      page => 1,
      order_by => [ 'created_date desc' ],
    }
  );
  my $pager = $posts->pager;

  while (my $post = $posts->next) {
    print $post->title, "\n";
    print "Posted on: ", $post->created_date->ymd;
  }

  if($pager->current_page < $pager->total_pages) {
    print "Next page: ", $pager->current_page + 1, "\n";
  }
  print "Last page: ", $pager->total_pages, "\n";

EXAMPLE

This example covers all the techniques outlined above, in a straight forward command-line based program. To prompt the user for information, we're using Term::Prompt. Install it using cpan Term::Prompt in your console.

/examples/creating_and_displaying_posts.pl

CONCLUSIONS

You should now be able to:

EXERCISES

Comments

Comments are related to posts as posts are related to users. Each post can have any number of comments. Each comment is also owned by a user.

Implement comment creation.

WHERE TO GO NEXT

User and post statistics

TODO

Patches and suggestions welcome.

AUTHOR

Jess Robinson <castaway@desert-island.me.uk>

Last modified:

Home