# Call create() on the book model object. Pass the table
# columns/field values we want to set as hash values
- my $book = $c->model('DB::Books')->create({
+ my $book = $c->model('DB::Book')->create({
title => $title,
rating => $rating
});
# Add a record to the join table for this book, mapping to
# appropriate author
- $book->add_to_book_authors({author_id => $author_id});
+ $book->add_to_book_author({author_id => $author_id});
# Note: Above is a shortcut for this:
- # $book->create_related('book_authors', {author_id => $author_id});
+ # $book->create_related('book_author', {author_id => $author_id});
# Assign the Book object to the stash for display in the view
$c->stash->{book} = $book;
- # This is a hack to disable XSUB processing in Data::Dumper
- # (it's used in the view). This is a work-around for a bug in
- # the interaction of some versions or Perl, Data::Dumper & DBIC.
- # You won't need this if you aren't using Data::Dumper (or if
- # you are running DBIC 0.06001 or greater), but adding it doesn't
- # hurt anything either.
- $Data::Dumper::Useperl = 1;
-
# Set the TT template to use
$c->stash->{template} = 'books/create_done.tt2';
}
URL and passes it as arguments in C<@_>. The C<url_create> action then
uses a simple call to the DBIC C<create> method to add the requested
information to the database (with a separate call to
-C<add_to_book_authors> to update the join table). As do virtually all
+C<add_to_book_author> to update the join table). As do virtually all
controller methods (at least the ones that directly handle user input),
it then sets the template that should handle this request.
[% # Output the last name of the first author. This is complicated by an -%]
[% # issue in TT 2.15 where blessed hash objects are not handled right. -%]
- [% # First, fetch 'book.authors' from the DB once. -%]
- [% authors = book.authors %]
+ [% # First, fetch 'book.author' from the DB once. -%]
+ [% authors = book.author %]
[% # Now use IF statements to test if 'authors.first' is "working". If so, -%]
[% # we use it. Otherwise we use a hack that seems to keep TT 2.15 happy. -%]
by '[% authors.first.last_name IF authors.first;
DBIC debug messages displayed in the development server log messages
if you have DBIC_TRACE set:
- INSERT INTO books (rating, title) VALUES (?, ?): `5', `TCPIP_Illustrated_Vol-2'
- INSERT INTO book_authors (author_id, book_id) VALUES (?, ?): `4', `6'
- SELECT author.id, author.first_name, author.last_name
- FROM book_authors me JOIN authors author
- ON ( author.id = me.author_id ) WHERE ( me.book_id = ? ): '6'
+ INSERT INTO book (rating, title) VALUES (?, ?): `5', `TCPIP_Illustrated_Vol-2'
+ INSERT INTO book_author (author_id, book_id) VALUES (?, ?): `4', `6'
The C<INSERT> statements are obviously adding the book and linking it to
the existing record for Richard Stevens. The C<SELECT> statement results
If you then click the "Return to list" link, you should find that
there are now six books shown (if necessary, Shift+Reload or
-Ctrl+Reload your browser at the C</books/list> page).
+Ctrl+Reload your browser at the C</books/list> page). You should now see
+the following six DBIC debug messages displayed for N=1-6:
+
+ SELECT author.id, author.first_name, author.last_name \
+ FROM book_author me JOIN author author \
+ ON ( author.id = me.author_id ) WHERE ( me.book_id = ? ): 'N'
=head1 CONVERT TO A CHAINED ACTION
my ($self, $c) = @_;
# Store the ResultSet in stash so it's available for other methods
- $c->stash->{resultset} = $c->model('DB::Books');
+ $c->stash->{resultset} = $c->model('DB::Book');
# Print a message to the debug log
$c->log->debug('*** INSIDE BASE METHOD ***');
my $author_id = $c->request->params->{author_id} || '1';
# Create the book
- my $book = $c->model('DB::Books')->create({
+ my $book = $c->model('DB::Book')->create({
title => $title,
rating => $rating,
});
# Handle relationship with author
- $book->add_to_book_authors({author_id => $author_id});
+ $book->add_to_book_author({author_id => $author_id});
# Store new model object in stash
$c->stash->{book} = $book;
[% # 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 to root/lib/site/header -%]
+ [% # Provide a title -%]
[% META title = 'Book List' -%]
<table>
[% # authors into the list. Note that the 'push' TT vmethod doesn't return -%]
[% # a value, so nothing will be printed here. But, if you have something -%]
[% # in TT that does return a value and you don't want it printed, you can -%]
- [% # 1) assign it to a bogus value, or -%]
- [% # 2) use the CALL keyword to call it and discard the return value. -%]
+ [% # 1) assign it to a bogus value, or # 2) use the CALL keyword to -%]
+ [% # call it and discard the return value. -%]
[% tt_authors = [ ];
- tt_authors.push(author.last_name) FOREACH author = book.authors %]
+ tt_authors.push(author.last_name) FOREACH author = book.author %]
[% # Now use a TT 'virtual method' to display the author count in parens -%]
- ([% tt_authors.size %])
+ [% # Note the use of the TT filter "| html" to escape dangerous characters -%]
+ ([% tt_authors.size | html %])
[% # Use another TT vmethod to join & print the names & comma separators -%]
- [% tt_authors.join(', ') %]
+ [% tt_authors.join(', ') | html %]
</td>
<td>
[% # Add a link to delete a book %]
my ($self, $c) = @_;
# Use the book object saved by 'object' and delete it along
- # with related 'book_authors' entries
+ # with related 'book_author' entries
$c->stash->{object}->delete;
# Set a status message to be displayed at the top of the view
This method first deletes the book object saved by the C<object> method.
However, it also removes the corresponding entry from the
-C<book_authors> table with a cascading delete.
+C<book_author> table with a cascading delete.
Then, rather than forwarding to a "delete done" page as we did with the
earlier create example, it simply sets the C<status_msg> to display a
along with a list of the eight remaining books. You will also see the
cascading delete operation via the DBIC_TRACE output:
- SELECT me.id, me.title, me.rating FROM books me WHERE ( ( me.id = ? ) ): '6'
- DELETE FROM books WHERE ( id = ? ): '6'
- SELECT me.book_id, me.author_id FROM book_authors me WHERE ( me.book_id = ? ): '6'
- DELETE FROM book_authors WHERE ( author_id = ? AND book_id = ? ): '4', '6'
+ SELECT me.id, me.title, me.rating FROM book me WHERE ( ( me.id = ? ) ): '6'
+ DELETE FROM book WHERE ( id = ? ): '6'
+ SELECT me.book_id, me.author_id FROM book_author me WHERE ( me.book_id = ? ): '6'
+ DELETE FROM book_author WHERE ( author_id = ? AND book_id = ? ): '4', '6'
=head2 Fixing a Dangerous URL
my ($self, $c) = @_;
# Use the book object saved by 'object' and delete it along
- # with related 'book_authors' entries
+ # with related 'book_author' entries
$c->stash->{object}->delete;
# Set a status message to be displayed at the top of the view
my ($self, $c) = @_;
# Use the book object saved by 'object' and delete it along
- # with related 'book_authors' entries
+ # with related 'book_author' entries
$c->stash->{object}->delete;
# Redirect the user back to the list page with status msg as an arg
each book was added and when each book is updated:
$ sqlite3 myapp.db
- sqlite> ALTER TABLE books ADD created INTEGER;
- sqlite> ALTER TABLE books ADD updated INTEGER;
- sqlite> UPDATE books SET created = DATETIME('NOW'), updated = DATETIME('NOW');
- sqlite> SELECT * FROM books;
+ sqlite> ALTER TABLE book ADD created INTEGER;
+ sqlite> ALTER TABLE book ADD updated INTEGER;
+ sqlite> UPDATE book SET created = DATETIME('NOW'), updated = DATETIME('NOW');
+ sqlite> SELECT * FROM book;
1|CCSP SNRS Exam Certification Guide|5|2009-03-08 16:26:35|2009-03-08 16:26:35
2|TCP/IP Illustrated, Volume 1|5|2009-03-08 16:26:35|2009-03-08 16:26:35
3|Internetworking with TCP/IP Vol.1|4|2009-03-08 16:26:35|2009-03-08 16:26:35
and populate those fields with the current time.
-=head2 Update DBIC to Automatically Handle the Datetime Columns
+=head2 Update DBIx::Class to Automatically Handle the Datetime Columns
Next, we should re-run the DBIC helper to update the Result Classes
with the new fields:
it to include the L<DBIx::Class::TimeStamp|DBIx::Class::TimeStamp>
in the C<load_components> line of the Result Classes.
-If you open C<lib/MyApp/Schema/Result/Books.pm> in your editor you
+If you open C<lib/MyApp/Schema/Result/Book.pm> in your editor you
should see that the C<created> and C<updated> fields are now included
in the call to C<add_columns()>, but our relationship information below
the "C<# DO NOT MODIFY...>" line was automatically preserved.
{ data_type => 'datetime', set_on_create => 1, set_on_update => 1 },
);
-This will override the definition for these fields that Schema::Loader
-placed at the top of the file. The C<set_on_create> and
-C<set_on_update> options will cause DBIC to automatically update the
-timestamps in these columns whenever a row is created or modified.
+This will override the definition for these fields that Schema::Loader
+placed at the top of the file. The C<set_on_create> and
+C<set_on_update> options will cause DBIx::Class to automatically
+update the timestamps in these columns whenever a row is created or
+modified.
To test this out, restart the development server using the
C<DBIC_TRACE=1> option:
you will see that the new book we added has an appropriate date and
time entered for it (see the last line in the listing below):
- sqlite3 myapp.db "select * from books"
+ sqlite3 myapp.db "select * from book"
1|CCSP SNRS Exam Certification Guide|5|2009-03-08 16:26:35|2009-03-08 16:26:35
2|TCP/IP Illustrated, Volume 1|5|2009-03-08 16:26:35|2009-03-08 16:26:35
3|Internetworking with TCP/IP Vol.1|4|2009-03-08 16:26:35|2009-03-08 16:26:35
Notice in the debug log that the SQL DBIC generated has changed to
incorporate the datetime logic:
- INSERT INTO books (created, rating, title, updated) VALUES (?, ?, ?, ?):
+ INSERT INTO book (created, rating, title, updated) VALUES (?, ?, ?, ?):
'2009-03-08 16:29:08', '5', 'TCPIP_Illustrated_Vol-2', '2009-03-08 16:29:08'
- INSERT INTO book_authors (author_id, book_id) VALUES (?, ?): '4', '10'
+ INSERT INTO book_author (author_id, book_id) VALUES (?, ?): '4', '10'
=head2 Create a ResultSet Class
To illustrate the concept with a fairly simple example, let's create a
method that returns books added in the last 10 minutes. Start by
-making a directory where DBIC will look for our ResultSet Class:
+making a directory where DBIx::Class will look for our ResultSet Class:
mkdir lib/MyApp/Schema/ResultSet
-Then open C<lib/MyApp/Schema/ResultSet/Books.pm> and enter the following:
+Then open C<lib/MyApp/Schema/ResultSet/Book.pm> and enter the following:
- package MyApp::Schema::ResultSet::Books;
+ package MyApp::Schema::ResultSet::Book;
use strict;
use warnings;
1;
Then we need to tell the Result Class to to treat this as a ResultSet
-Class. Open C<lib/MyApp/Schema/Result/Books.pm> and add the following
+Class. Open C<lib/MyApp/Schema/Result/Book.pm> and add the following
above the "C<1;>" at the bottom of the file:
#
# Set ResultSet Class
#
- __PACKAGE__->resultset_class('MyApp::Schema::ResultSet::Books');
+ __PACKAGE__->resultset_class('MyApp::Schema::ResultSet::Book');
Then add the following method to the C<lib/MyApp/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, but only
# retrieve books created within the last $min number of minutes
- $c->stash->{books} = [$c->model('DB::Books')
+ $c->stash->{books} = [$c->model('DB::Book')
->created_after(DateTime->now->subtract(minutes => $mins))];
# Set the TT template to use. You will almost always want to do this
=head2 Chaining ResultSets
-One of the most helpful and powerful features in DBIC is that it allows
-you to "chain together" a series of queries (note that this has nothing
-to do with the "Chained Dispatch" for Catalyst that we were discussing
-above). Because each ResultSet returns another ResultSet, you can take
-an initial query and immediately feed that into a second query (and so
-on for as many queries you need). Note that no matter how many
-ResultSets you chain together, the database itself will not be hit until
-you use a method that attempts to access the data. And, because this
-technique carries over to the ResultSet Class feature we implemented in
-the previous section for our "canned search", we can combine the two
-capabilities. For example, let's add an action to our C<Books>
-controller that lists books that are both recent I<and> have "TCP" in
-the title. Open up C<lib/MyApp/Controller/Books.pm> and add the
-following method:
+One of the most helpful and powerful features in DBIx::Class is that
+it allows you to "chain together" a series of queries (note that this
+has nothing to do with the "Chained Dispatch" for Catalyst that we
+were discussing above). Because each ResultSet returns another
+ResultSet, you can take an initial query and immediately feed that
+into a second query (and so on for as many queries you need). Note
+that no matter how many ResultSets you chain together, the database
+itself will not be hit until you use a method that attempts to access
+the data. And, because this technique carries over to the ResultSet
+Class feature we implemented in the previous section for our "canned
+search", we can combine the two capabilities. For example, let's add
+an action to our C<Books> controller that lists books that are both
+recent I<and> have "TCP" in the title. Open up
+C<lib/MyApp/Controller/Books.pm> and add the following method:
=head2 list_recent_tcp
# stash where they can be accessed by the TT template, but only
# retrieve books created within the last $min number of minutes
# AND that have 'TCP' in the title
- $c->stash->{books} = [$c->model('DB::Books')
+ $c->stash->{books} = [$c->model('DB::Book')
->created_after(DateTime->now->subtract(minutes => $mins))
->search({title => {'like', '%TCP%'}})
];
Take a look at the DBIC_TRACE output in the development server log for
the first URL and you should see something similar to the following:
- SELECT me.id, me.title, me.rating, me.created, me.updated FROM books me
+ SELECT me.id, me.title, me.rating, me.created, me.updated FROM book me
WHERE ( ( ( title LIKE ? ) AND ( created > ? ) ) ): '%TCP%', '2009-03-08 14:52:54'
However, let's not pollute our controller code with this raw "TCP"
query -- it would be cleaner to encapsulate that code in a method on
our ResultSet Class. To do this, open
-C<lib/MyApp/Schema/ResultSet/Books.pm> and add the following method:
+C<lib/MyApp/Schema/ResultSet/Book.pm> and add the following method:
=head2 title_like
# stash where they can be accessed by the TT template, but only
# retrieve books created within the last $min number of minutes
# AND that have 'TCP' in the title
- $c->stash->{books} = [$c->model('DB::Books')
+ $c->stash->{books} = [$c->model('DB::Book')
->created_after(DateTime->now->subtract(minutes => $mins))
->title_like('TCP')
];
=head2 Adding Methods to Result Classes
-In the previous two sections we saw a good example of how we could use
-DBIC ResultSet Classes to clean up our code for an entire query (for
-example, our "canned searches" that filtered the entire query). We
-can do a similar improvement when working with individual rows as
-well. Whereas the ResultSet construct is used in DBIC to correspond
-to an entire query, the Result Class construct is used to represent a
-row. Therefore, we can add row-specific "helper methods" to our Result
-Classes stored in C<lib/MyApp/Schema/Result/>. For example, open
-C<lib/MyApp/Schema/Result/Authors.pm> and add the following method
-(as always, it must be above the closing "C<1;>"):
+In the previous two sections we saw a good example of how we could use
+DBIx::Class ResultSet Classes to clean up our code for an entire query
+(for example, our "canned searches" that filtered the entire query).
+We can do a similar improvement when working with individual rows as
+well. Whereas the ResultSet construct is used in DBIC to correspond
+to an entire query, the Result Class construct is used to represent a
+row. Therefore, we can add row-specific "helper methods" to our Result
+Classes stored in C<lib/MyApp/Schema/Result/>. For example, open
+C<lib/MyApp/Schema/Result/Author.pm> and add the following method (as
+always, it must be above the closing "C<1;>"):
#
# Helper methods
...
[% tt_authors = [ ];
- tt_authors.push(author.last_name) FOREACH author = book.authors %]
+ tt_authors.push(author.last_name) FOREACH author = book.author %]
...
to:
...
[% tt_authors = [ ];
- tt_authors.push(author.full_name) FOREACH author = book.authors %]
+ tt_authors.push(author.full_name) FOREACH author = book.author %]
...
(Only C<author.last_name> was changed to C<author.full_name> -- the