sub base :Chained('/') :PathPart('books') :CaptureArgs(0) {
my ($self, $c) = @_;
- # Store the resultset in stash so it's available for other methods
+ # Store the ResultSet in stash so it's available for other methods
$c->stash->{resultset} = $c->model('DB::Books');
# Print a message to the debug log
$c->log->debug('*** INSIDE BASE METHOD ***');
}
-Here we print a log message and store the DBIC resultset in
+Here we print a log message and store the DBIC ResultSet in
C<$c-E<gt>stash-E<gt>{resultset}> so that it's automatically available
for other actions that chain off C<base>. If your controller always
needs a book ID as it's first argument, you could have the base method
=head2 Include a Delete Link in the List
-Edit C<root/src/books/list.tt2> and update it to the following (two
+Edit C<root/src/books/list.tt2> and update it to match the following (two
sections have changed: 1) the additional '<th>Links</th>' table header,
and 2) the four lines for the Delete link near the bottom).
links will be used instead of full HTML buttons).
Also notice that we are using a more advanced form of C<uri_for> than
-we have seen before. Here we use C<$c-E<gt>controller-
-E<gt>action_for> to automatically generate a URI appropriate for that
-action based on the method we want to link to while inserting the
-C<book.id> value into the appropriate place. Now, if you ever change
-C<:PathPart('delete')> in your controller method to
+we have seen before. Here we use
+C<$c-E<gt>controller-E<gt>action_for> to automatically generate a URI
+appropriate for that action based on the method we want to link to
+while inserting the C<book.id> value into the appropriate place. Now,
+if you ever change C<:PathPart('delete')> in your controller method to
C<:PathPart('kill')>, then your links will automatically update
without any changes to your .tt2 template file. As long as the name
of your method does not changed ("delete" here), then your links will
-still be correct. There are a few shortcuts and options when using
+still be correct. There are a few shortcuts and options when using
C<action_for()>:
=over 4
=back
-B<Note:> In general, you should use more than just a simple link with
-your applications. Consider using some sort of of confirmation page
-(typically with unique actions in your controller for both the
-confirmation and the actual delete operation). Also, you should try
-to use an HTTP POST operation (versus the GET used here) for
-operations that change the state of your application (e.g., the
-database).
+B<Note:> In practice you should B<never> use a GET request to delete a
+record -- always use POST for actions that will modify data. We are
+doing it here for illustrative and simplicity purposes only.
=head2 Add a Common Method to Retrieve a Book for the Chain
Now, any other method that chains off C<object> will automatically
have the appropriate book waiting for it in
-C<$c-E<gt>stash-Egt>{object}>.
+C<$c-E<gt>stash-E<gt>{object}>.
Also note that we are using different technique for setting
C<$c-E<gt>stash>. The advantage of this style is that it let's you
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'
Note the URL in your browser once you have performed the deletion in the
prior step -- it is still referencing the delete action:
- http://localhost:3000/books/delete/6
+ http://localhost:3000/books/id/6/delete
What if the user were to press reload with this URL still active? In
this case the redundant delete is harmless (although it does generate
to use the "query param" technique shown here in your applications.
+=head1 EXPLORING THE POWER OF DBIC
+
+In this section we will explore some additional capabilities offered
+by DBIx::Class. Although these features have relatively little to do
+with Catalyst per se, you will almost certainly want to take advantage
+of them in your applications.
+
+
+=head2 Convert to DBIC "load_namespaces"
+
+If you look back at
+L<Catalyst::Manual::Tutorial::MoreCatalystBasics/Create Static DBIC
+Schema Files> you will recall that we load our DBIC Result Classes
+(Books.pm, Authors.pm and BookAuthors.pm) with in
+C<lib/MyApp/Schema.pm> with the C<load_classes> feature. Although
+this method is perfectly valid, the DBIC community has migrated to a
+newer C<load_namespaces> technique because it more easily supports a
+variety of advanced features. Since we want to explore some of these
+features below, let's first migrate our configuration over to use
+C<load_namespaces>.
+
+If you are following along in Debian 5, you will need to upgrade your
+version of
+L<Catalyst::Model::DBIC::Schema|Catalyst::Model::DBIC::Schema> to 0.23
+or higher. To do this, we can install directly from CPAN via the
+following command:
+
+ $ sudo cpan Catalyst::Model::DBIC::Schema
+
+Then make sure you are running an appropriate version:
+
+ $ perl -MCatalyst::Model::DBIC::Schema -e \
+ 'print "$Catalyst::Model::DBIC::Schema::VERSION\n"'
+ 0.23
+
+Make sure you get version 0.23 or higher.
+
+B<Note:> Debian will automatically "do the right thing" and use the
+module we installed from CPAN and ignore the older version we picked
+up via the C<aptitude> command. If you are using a different
+environment, you will need to make sure you are using v0.23 or higher
+with the command above.
+
+While we are at it, let's install a few other modules from CPAN for
+some of the other work we will be doing below:
+
+ $ cpan Time::Warp DBICx::TestDatabase \
+ DBIx::Class::DynamicDefault DBIx::Class::TimeStamp
+
+Next, we need to delete the existing C<lib/MyApp/Schema.pm> so that
+the Catalyst DBIC helper will recreate it. Then we re-generate
+the model and schema information:
+
+ $ rm lib/MyApp/Schema.pm
+ $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
+ create=static components=TimeStamp dbi:SQLite:myapp.db
+ exists "/root/dev/MyApp/script/../lib/MyApp/Model"
+ exists "/root/dev/MyApp/script/../t"
+ Dumping manual schema for MyApp::Schema to directory /root/dev/MyApp/script/../lib ...
+ Schema dump completed.
+ exists "/root/dev/MyApp/script/../lib/MyApp/Model/DB.pm"
+ $
+ $ ls lib/MyApp/Schema
+ Authors.pm BookAuthors.pm Books.pm Result
+ $ ls lib/MyApp/Schema/Result
+ Authors.pm BookAuthors.pm Books.pm
+
+Notice that we now have a duplicate set of Result Class files. With
+the newer C<load_namespaces> feature, DBIC automatically looks for
+your Result Class files in a subdirectory of the Schema directory
+called C<Result> (the files in C<lib/MyApp/Schema> were already there
+from Part 3 of the tutorial; the files in C<lib/MyApp/Schema/Result>
+are new).
+
+If you are using SQLite, you will need to manually re-enter the
+relationship configuration as we did in Part 3 of the tutorial (if you
+are using different database, the relationships might have been auto-
+generated by Schema::Loader). One option is to use the following
+command-line perl script to migrate the information across
+automatically:
+
+ $ cd lib/MyApp/Schema
+ $ perl -MIO::All -e 'for (@ARGV) { my $s < io($_); $s =~ s/.*\n\# You can replace.*?\n//s;
+ $s =~ s/'MyApp::Schema::/'MyApp::Schema::Result::/g; my $d < io("Result/$_");
+ $d =~ s/1;\n?//; "$d$s" > io("Result/$_"); }' *.pm
+ $ cd ../../..
+
+If you prefer, you can do the migration by hand using "cut and paste"
+from the files in C<lib/MyApp/Schema> (or from
+L<Catalyst::Manual::Tutorial::MoreCatalystBasics/Updating the Generated DBIC Schema Files>)
+to the corresponding files in C<lib/MyApp/Schema/Result>. If you take
+this approach, be sure to add C<::Result> to the end of
+C<MyApp::Schema> in all three files (for example, in C<Books.pm>, the
+"peer class" in the C<has_many> relationship needs to be changed from
+C<MyApp::Schema::BookAuthors> to C<MyApp::Schema::BookAuthors::Result>).
+
+Now we can remove the original set of Result Class files that we no
+longer need:
+
+ $ rm lib/MyApp/Schema/*.pm
+ $ ls lib/MyApp/Schema
+ Result
+
+Finally, test the application to be sure everything is still
+working under our new configuration. Use the
+C<script/myapp_server.pl> command to start the development server and
+point your browser to L<http://localhost:3000/books/list>. Make sure
+you see the existing list of books.
+
+
+=head2 Add Datetime Columns to Our Existing Books Table
+
+Let's add two columns to our existing C<books> table to track when
+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;
+ 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
+ 4|Perl Cookbook|5|2009-03-08 16:26:35|2009-03-08 16:26:35
+ 5|Designing with Web Standards|5|2009-03-08 16:26:35|2009-03-08 16:26:35
+ 9|TCP/IP Illustrated, Vol 3|5|2009-03-08 16:26:35|2009-03-08 16:26:35
+ sqlite> .quit
+ $
+
+This will modify the C<books> table to include the two new fields
+and populate those fields with the current time.
+
+
+=head2 Update DBIC to Automatically Handle the Datetime Columns
+
+Next, we should re-run the DBIC helper to update the Result Classes
+with the new fields:
+
+ $ script/myapp_create.pl model DB DBIC::Schema MyApp::Schema \
+ create=static components=TimeStamp dbi:SQLite:myapp.db
+ exists "/root/dev/MyApp/script/../lib/MyApp/Model"
+ exists "/root/dev/MyApp/script/../t"
+ Dumping manual schema for MyApp::Schema to directory /root/dev/MyApp/script/../lib ...
+ Schema dump completed.
+ exists "/root/dev/MyApp/script/../lib/MyApp/Model/DB.pm"
+
+Notice that we modified our use of the helper slightly: we told
+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
+should see that the C<created> and C<updated> fields are now included
+in the call to add_columns(), but our relationship information below
+the "C<# DO NOT MODIFY...>" line was automatically preserved.
+
+While we have this file open, let's update it with some additional
+information to have DBIC automatically handle the updating of these
+two fields for us. Insert the following code at the bottom of the
+file (it B<must> be B<below> the "C<# DO NOT MODIFY...>" line and
+B<above> the C<1;> on the last line):
+
+ #
+ # Enable automatic date handling
+ #
+ __PACKAGE__->add_columns(
+ "created",
+ { data_type => 'datetime', set_on_create => 1 },
+ "updated",
+ { 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.
+
+To test this out, restart the development server using the
+C<DBIC_TRACE=1> option:
+
+ DBIC_TRACE=1 script/myapp_server.pl
+
+Then enter the following URL into your web browser:
+
+ http://localhost:3000/books/url_create/TCPIP_Illustrated_Vol-2/5/4
+
+You should get the same "Book Created" screen we saw above. However,
+if you now use the sqlite3 command-line tool to dump the C<books> table,
+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"
+ 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
+ 4|Perl Cookbook|5|2009-03-08 16:26:35|2009-03-08 16:26:35
+ 5|Designing with Web Standards|5|2009-03-08 16:26:35|2009-03-08 16:26:35
+ 9|TCP/IP Illustrated, Vol 3|5|2009-03-08 16:26:35|2009-03-08 16:26:35
+ 10|TCPIP_Illustrated_Vol-2|5|2009-03-08 16:29:08|2009-03-08 16:29:08
+
+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 (?, ?, ?, ?):
+ '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'
+
+
+=head2 Create a ResultSet Class
+
+An often overlooked but extremely powerful features of DBIC is that it
+allows you to supply your own subclasses of C<DBIx::Class::ResultSet>.
+It allows you to pull complex and unsightly "query code" out of your
+controllers and encapsulate it in a method of your ResultSet Class.
+These "canned queries" in your ResultSet Class can then be invoked
+via a single call, resulting in much cleaner and easier to read
+controller code.
+
+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:
+
+ mkdir lib/MyApp/Schema/ResultSet
+
+Then open C<lib/MyApp/Schema/ResultSet/Books.pm> and enter the following:
+
+ package MyApp::Schema::ResultSet::Books;
+
+ use strict;
+ use warnings;
+ use base 'DBIx::Class::ResultSet';
+
+ =head2 created_after
+
+ A predefined search for recently added books
+
+ =cut
+
+ sub created_after {
+ my ($self, $datetime) = @_;
+
+ my $date_str = $self->_source_handle->schema->storage
+ ->datetime_parser->format_datetime($datetime);
+
+ return $self->search({
+ created => { '>' => $date_str }
+ });
+ }
+
+ 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
+above the "C<1;>" at the bottom of the file:
+
+ #
+ # Set ResultSet Class
+ #
+ __PACKAGE__->resultset_class('MyApp::Schema::ResultSet::Books');
+
+Then add the following method to the C<lib/MyApp/Controller/Books.pm>:
+
+ =head2 list_recent
+
+ List recently created books
+
+ =cut
+
+ sub list_recent :Chained('base') :PathPart('list_recent') :Args(1) {
+ my ($self, $c, $mins) = @_;
+
+ # 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')
+ ->created_after(DateTime->now->subtract(minutes => $mins))];
+
+ # Set the TT template to use. You will almost always want to do this
+ # in your action methods (action methods respond to user input in
+ # your controllers).
+ $c->stash->{template} = 'books/list.tt2';
+ }
+
+Now start the development server with C<DBIC_TRACE=1> and try
+different values for the minutes argument (the final number value) for
+the URL C<http://localhost:3000/books/list_recent/10>. For example,
+this would list all books added in the last fifteen minutes:
+
+ http://localhost:3000/books/list_recent/15
+
+Depending on how recently you added books, you might want to
+try a higher or lower value.
+
+
+=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). 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
+
+ List recently created books
+
+ =cut
+
+ sub list_recent_tcp :Chained('base') :PathPart('list_recent_tcp') :Args(1) {
+ my ($self, $c, $mins) = @_;
+
+ # 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
+ # AND that have 'TCP' in the title
+ $c->stash->{books} = [$c->model('DB::Books')
+ ->created_after(DateTime->now->subtract(minutes => $mins))
+ ->search({title => {'like', '%TCP%'}})
+ ];
+
+ # Set the TT template to use. You will almost always want to do this
+ # in your action methods (action methods respond to user input in
+ # your controllers).
+ $c->stash->{template} = 'books/list.tt2';
+ }
+
+To try this out, restart the development server with:
+
+ DBIC_TRACE=1 script/myapp_server.pl
+
+And enter the following URL into your browser:
+
+ http://localhost:3000/books/list_recent_tcp/100
+
+And you should get a list of books added in the last 100 minutes that
+contain the string "TCP" in the title. However, if you look at all
+books within the last 100 minutes, you should get a longer list
+(again, you might have to adjust the number of minutes depending on
+how recently you added books to your database):
+
+ http://localhost:3000/books/list_recent/100
+
+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
+ 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:
+
+ =head2 title_like
+
+ A predefined search for books with a 'LIKE' search in the string
+
+ =cut
+
+ sub title_like {
+ my ($self, $title_str) = @_;
+
+ return $self->search({
+ title => { 'like' => "%$title_str%" }
+ });
+ }
+
+We defined the search string as C<$title_str> to make the method more
+flexible. Now update the C<list_recent_tcp> method in
+C<lib/MyApp/Controller/Books.pm> to match the following (we have
+replaced the C<-E<gt>search> line with the C<-E<gt>title_like> line
+shown here -- the rest of the method should be the same):
+
+ =head2 list_recent_tcp
+
+ List recently created books
+
+ =cut
+
+ sub list_recent_tcp :Chained('base') :PathPart('list_recent_tcp') :Args(1) {
+ my ($self, $c, $mins) = @_;
+
+ # 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
+ # AND that have 'TCP' in the title
+ $c->stash->{books} = [$c->model('DB::Books')
+ ->created_after(DateTime->now->subtract(minutes => $mins))
+ ->title_like('TCP')
+ ];
+
+ # Set the TT template to use. You will almost always want to do this
+ # in your action methods (action methods respond to user input in
+ # your controllers).
+ $c->stash->{template} = 'books/list.tt2';
+ }
+
+Then restart the development server and try out the C<list_recent_tcp>
+and C<list_recent> URL as we did above. It should work just the same,
+but our code is obviously cleaner and more modular, while also being
+more flexible at the same time.
+
+
+=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;>"):
+
+ #
+ # Helper methods
+ #
+ sub full_name {
+ my ($self) = @_;
+
+ return $self->first_name . ' ' . $self->last_name;
+ }
+
+This will allow us to conveniently retrieve both the first and last
+name for an author in one shot. Now open C<root/src/books/list.tt2>
+and change the definition of C<tt_authors> from this:
+
+ ...
+ [% tt_authors = [ ];
+ tt_authors.push(author.last_name) FOREACH author = book.authors %]
+ ...
+
+to:
+
+ ...
+ [% tt_authors = [ ];
+ tt_authors.push(author.full_name) FOREACH author = book.authors %]
+ ...
+
+(Only C<author.last_name> was changed to C<author.full_name> -- the
+rest of the file should remain the same.)
+
+Now restart the development server and go to the standard book list
+URL:
+
+ http://localhost:3000/books/list
+
+The "Author(s)" column will now contain both the first and last name.
+And, because the concatenation logic was encapsulated inside our
+Result Class, it keeps the code inside our .tt template nice and clean
+(remember, we want the templates to be as close to pure HTML markup as
+possible). Obviously, this capability becomes even more useful as you
+use to to remove even more complicated row-specific logic from your
+templates!
+
+
=head1 AUTHOR
Kennedy Clark, C<hkclark@gmail.com>
projects / catagits/Catalyst-Manual.git / blobdiff