Catalyst in 9 steps - step 9: SQLite DB accessed with DBIC in ResultSet classes, CLI scripts
Note: You can download the archive with the application created in this ninth step from:
WebApp-0.09.tar.gzThe application we made is a very simple one that just adds/edits/deletes/retrieves very small records so the advantages of the previous step are not obvious because for very simple SQL queries the ORMs can't improve the code too much. In the previous step we used business logic in the controller, because we accessed there the ORM directly, which theoreticly is not recommended.
Theoreticly it is better to create a standalone library which offers custom methods for what it needs to do, and only that library then use whatever technology we decide in a certain moment.
For example, for adding a record in the database we should be able to just initialize an object and use a common Perl method that we can use with exactly the same interface everywhere, in the Catalyst controllers, in command line programs, in test scripts. Something like:
$object->add( first_name => '...', last_name => '...', email => '...' );
And then in our standalone library make the add() subroutine to do what we want to do. Maybe sometimes we want it to add the records in a text file, other times we want to add the records in a relational database using plain SQL queries, other times we want to use DBIC, maybe once we will want to create another standalone library with the same interface that will use another ORM... but all those versions of the standalone library should use the same interface, so the add() subroutine should accept the same parameters.
The advantage would be that after changing the standalone library with a new one we wouldn't need to change anything else because all the controllers, CLI scripts, test files will send the same parameters and the new standalone library would work.
But this is theory, which is not always very practical.
If we use a lower level way to access a database, like when we use a plain text file as a database or when we access a relational database using SQL queries, we need to write some more custom code manually, and in that code we can easily return the result as any data structure we want. When we use a higher level way to access a database, like when we use an ORM, that ORM returns the data as objects which already offer many features and usually it is not very helpful to take off the data from those objects and transform it in a common Perl data structure just for using the same interface we used before.
The objects which are generated by ORMs can be also used in other higher level modules like form processors for example, and those modules can increase the productivity even more.
Another reason to change the interface and use those objects returned by the ORM is that many times the biggest part of the application is the one that does the business logic. So when we want to change the database from using a plain text to use a non-sql database, or from such a database to a relational database, or if we want to change the technology and start using an ORM, usually we actually want to change the entire application, not to just change the technology that powers the business logic and let the rest unchanged. So if we really want to change the entire application it doesn't matter too much if we will also change the interface used to access the database, especially that the new change would be probably an improvement.
In our small application that uses just very simple DBIC methods to access the database there is almost no reason to improve the way we use DBIC. Even though we use DBIC methods directly in the controller, and we would need to copy that code in other controllers if we would need it there, DBIC offers a very short and elegant solution.
For example, if we need to get an object with the record that has the primary key $id we need to write the following DBIC code:
my $record = $c->model( 'DB::Person' )->find( $id );
If we would create a standalone library and use a model that inherits from Catalyst::Model::Adaptor to use that library, we would need to use instead a code like:
my $record = $c->model( 'DB' )->find_person( $id );
But in addition we would need to manually create that standalone library and add the custom method find_person() using a code like:
sub find_person {
my ( $self, $id ) = @_;
return $self->schema->resultset( 'Person' )->find( $id );
}
So this would mean much more code which wouldn't be more clear than the interface offered directly by DBIC. And the result will still be a DBIC row object and not a common Perl data type that would be compatible with a previous version of the application that doesn't use DBIC. In order to return a common Perl data structure and not a DBIC object, some more code is needed and some features offered by DBIC will be lost.
So for the moment usually the best idea is to use directly the objects returned by DBIC.
OK, but what about the larger applications that use a much complicated business logic and much more complex DBIC commands to add/edit/delete/retrieve the data from the database?
Well, in case of those applications it is not a good idea to keep the entire business logic in the controllers, because if we would need to use the same code in more controllers, we would need to copy too much code and we will also need to maintain it in more places. We can add the code we want in the modules generated automaticly by that long Catalyst helper command.
Let's add some code as an example.
In our database table the first name and the last name of the persons are separated in 2 distinct columns. We've seen that in our template index.tt there is a line that display these 2 columns joined:
<td><a href="/manage/edit/[% m.id %]">[% m.last_name %], [% m.first_name %]</a></td>
These 2 Template-Toolkit directives join the last name and the first name:
[% m.last_name %], [% m.first_name %]
But let's say that we want to operate on the record, also called as DBIC row and return from the database a column named "last_first" in which the last name and the first name are already joined and we will display that new column in the template using just:
[% m.last_first %]
Of course, this is just a simple example, but many other things which are even more helpful can be done.
We should get an additional column named "last_first" which contains the last name and first name joined with a comma and a space.
If we use an SQLite database, the SQL command for getting that additional command is:
select id, first_name, last_name, email,
last_name||', '||first_name as last_first from persons;
If we use a MySQL database, the SQL command for getting that additional command is:
select id, first_name, last_name, email,
concat(last_name, ', ', first_name) as last_first from persons;
In other database types it could have other forms. If we use DBIC, our source code will be compatible with all supported databases.
We will add the code in the file lib/StandaloneApp3/Result/Person.pm below the text generated automaticly "# You can replace this text with custom code or comments, and it will be preserved on regeneration":
sub last_first {
my ( $self ) = @_;
return $self->last_name . ', ' . $self->first_name;
}
That's all. This subroutine with the name last_first from the Result class Person.pm will become a column with the name last_first which will get and concatenate the 2 columns and return the result. This column will be displayed by the index.tt template using:
[% m.last_first %]
OK, we've seen how we can operate on a record, also know as a DBIC row object, but how can we operate on a whole table, or better said to ResultSet (collection of row objects)?
We need to operate on a ResultSet when we want to add a new record in the table, or when we need to select something from a table or want to search in related tables.
Instead of adding this code in the controllers, and duplicate it in all controllers where we need it, we can add it in the ResultSet modules and then access it with a single method call.
But where are those ResultSet modules?
When we used that long Catalyst helper command that created the model DB.pm and the DBIC schema StandaloneApp3.pm, it also created the Result class Person.pm in the directory lib/StandaloneApp3/Result. The helper script doesn't create ResultSet classes, but only Result classes so we need to create them manually.
We need to create the directory lib/StandaloneApp3/ResultSet and in that directory we will create Perl modules that have the same name as the modules from the Result directory. Of course, we don't need to create ResultSet modules for all the Result modules we have.
In case of our application we have a single Result class named Result/Person.pm so we will create a ResultSet class ResultSet/Person.pm that will have the following content:
package StandaloneApp3::ResultSet::Person;
use strict;
use warnings;
use base 'DBIx::Class::ResultSet';
sub add {
my ( $self, $params ) = @_;
$self->create( $params );
}
sub edit {
my ( $self, $id, $params ) = @_;
$self->find( $id )->update( $params );
}
sub delete {
my ( $self, $id ) = @_;
$self->find( $id )->delete;
}
sub retrieve {
my ( $self, $id ) = @_;
my $members = $self->search( undef, { order_by => 'id' } );
$members = $members->search( { id => $id } ) if $id;
my @members = $members->all;
return \@members;
}
1;
As we can see the code is very simple, but of course, the DBIC documentation must be read in order to understand it.
Note that the ResultSet classes inherit from DBIx::Class::ResultSet.
Now if we want to access the database from the controller or from command line scripts, or in test scripts, we don't need to use the methods provided by DBIC directly, because it would be more easy to use these custom methods we created. In real life applications the business code uses more lines of code. It will be replaced by just a single line of code that calls a single method or chain of methods from our ResultSet module. In our application which is very simple we will not see this improvement because the business code is already small.
OK, let's modify the controller to use these custom methods instead of the methods offered by DBIC. In order to be more clear which is the diffrence, we will show below the lines that where changed and we will show the line before and after this change.
In the add() subroutine:
previous code:
$c->model( 'DB::Person' )->create( $c->request->params );
current code:
$c->model( 'DB::Person' )->add( $c->request->params );
In this case we changed just the name of the method used from create() to add() so it is not an advantage. In other applications, instead this previous code that's so small, we might have a bigger code, but the current code would still be just a single line or fewer lines.
In the edit() subroutine:
previous code:
$c->model( 'DB::Person' )->find( $wanted_id )->update( $c->request->params );
current code:
$c->model( 'DB::Person' )->edit( $wanted_id, $c->request->params );
This time instead of using a chain of 2 method calls to find() and update() which could have been put in 2 lines of code, we use just a single subroutine named edit().
In the edit() subroutine we also have a line that calls a DBIC method to get the record with the specified ID:
my $members = $c->model( 'DB::Person' )->find( $wanted_id );
This line of code just calls a single subroutine with a single parameter so we couldn't improve it by creating our own custom method in the Person.pm ResultSet class so we leave it as it is.
In the delete() subroutine:
previous code:
$c->model( 'DB::Person' )->find( $wanted_id )->delete;
current code:
$c->model( 'DB::Person' )->del( $wanted_id );
The previous code was calling 2 DBIC methods find() and delete() and the current code calls just our custom del() method we created. We name our custom method del() and not delete() to not conflict with the delete() method which is provided by DBIC.
In the index() subroutine:
previous code:
my @members = $c->model( 'DB::Person' )->search( undef, { order_by => 'id' } );
$c->stash( members => \@members );
current code:
my $members = $c->model( 'DB::Person' )->retrieve;
$c->stash( members => $members );
In the previous code we called the DBIC method search() with a long parameter, but the current code just calls the custom method retrieve() without any parameters and that method also returns an arrayref we can add directly to the stash.
But anyway, I say again that this code is too small for beeing obvious that it is better to add custom methods to Resultset classes.
So, in addition to what we done on the previous step, on this last step we created the lib/StandaloneApp3/ResultSet/Person.pm module and we modified the controller to use the methods we defined in this module. We also shown how to add a custom generated column in lib/StandaloneApp3/Result/Person.pm and also modified the template index.tt to display this new column.
As we did on each step, we will test the application using the same actions:
Run again the development server:
perl script/webapp_server.pl
And then access it at the following URL:
http://localhost:3000/manage
Click on the "Add new member". It will open the page with the add form. Add some data in the form and submit it. It will add that record in the database and it will redirect to the page with the persons. Click on the name of the person. It will open the edit form. Change some values and submit that form. It will make the change in the database and it will redirect to the page with the list of persons. Click on "Delete member" link for the person you want. It will delete that record from the database and it will redirect to the page with the list of persons.
Now that we created those custom methods in the ResultSet class and we are using them in the Manage controller, we can do the same changes in the command line scripts, and make them to also use those methods.
Here are the lines that were changed in the CLI scripts:
# add.pl:
previous code:
$app->resultset( 'Person' )->create( { first_name => $first_name,
last_name => $last_name, email => $email } );
current code:
$app->resultset( 'Person' )->add( { first_name => $first_name,
last_name => $last_name, email => $email } );
# edit.pl:
previous code:
$app->resultset( 'Person' )->find( $id )->update( { first_name => $first_name,
last_name => $last_name, email => $email } );
Current code:
$app->resultset( 'Person' )->edit( $id, { first_name => $first_name,
last_name => $last_name, email => $email } );
# delete.pl:
previous code:
$app->resultset( 'Person' )->find( $id )->delete;
current code:
$app->resultset( 'Person' )->del( $id );
# retrieve.pl:
previous code:
my $members = $app->resultset( 'Person' )->search( undef, { order_by => 'id' } );
$members = $members->search( { id => $id } ) if $id;
my @members = $members->all;
for my $m ( @members ) {
print $m->id, ', ', $m->first_name, ', ', $m->last_name, ', ', $m->email, "\n";
}
current code:
my $members = $app->resultset( 'Person' )->retrieve( $id );
for my $m ( @$members ) {
print $m->id, ', ', $m->first_name, ', ', $m->last_name, ', ', $m->email, "\n";
}
We can also use these scripts as a test of the standalone library (the DBIC schema) using commands like:
perl add.pl --first-name John --last-name Smith --email john@smith.com
perl edit.pl --id 1 --first-name George --last-name Smith --email j@smith.com
perl retrieve.pl --id 1
perl delete.pl --id 1
We have also created a test file t/06standalone_app3.t which tests if the DBIC schema works fine when we use the ResultSet methods we defined.
We can run all the tests with:
prove -l t
And it should finish with the results:
All tests successful.
Files=7, Tests=131, 9 wallclock secs ( 0.13 usr + 0.02 sys = 0.14 CPU)
Result: PASS
This was a short step by step tutorial for Catalyst which started with an application that use a plain text database and which has just a controller, but no model and no view.
Then we've seen that the application is easier to develop if we move the business code to the model because we can access the business code from more controllers.
In the third step we've seen that we can move the business code further, outside of the Catalyst app, in a standalone library that can be accessed not only from the Catalyst app by using a light model, but from other applications also.
In the next step we've presented the advantages of doing the formatting using a view, and not the controller, and we created a view which uses Template-Toolkit.
On the fifth step we decided to abandon the text file database and to use an SQLite relational database instead which is more powerful, and we change the application to access this database with plain SQL commands from the controller.
In the next step we moved the business code from the controller to the model for the same reasons... for beeing able to use that code in other controllers easier.
In the seventh step we moved again the business code from the fat model to a new standalone library in order to be able to use again that code in other applications than the web app.
In the eight step we presented the disadvantages of using plain SQL queries and the advantage of using an ORM, and we modified the controller to access the same SQLite database using DBIC.
In the current step we have shown how we can create custom methods in the DBIC Result and ResultSet classes in order to be able to use them in more controllers, test scripts and other applications.
The application can be improved further by adding more configuration and specify for example that the templates, config files, database use UTF-8, make it use a form processor, use internationalization, use REST and so on. I didn't add these things to the current application in order to make it appear as easy to understand as possible.
I also made all the modules which were manually created to not use Moose in order to be easier to understand by beginners.
Author:
Octavian Rasnita <orasnita@gmail.com>