[Catalyst] Why it is better to use an ORM like DBIx::Class

Octavian Râşniţă orasnita at gmail.com
Wed May 27 19:23:37 GMT 2009


Hi,

I corrected (re-written) that message in which I tried to show why it is 
better to use DBIC, because it doesn't affect the efficiency. Here it is. I 
hope it could be helpful. Please tell me if you think it needs improvements:

Why it is better to use an ORM like DBIx::Class

There are many advantages of using an ORM, but I won't remember all of them 
here.
I just want to show how clean it could be a code that uses DBIx::Class 
instead of manually adding parts for creating SQL queries.

And I would also like to show that this doesn't affect the efficiency.

DBIx::Class works the same in all apps, but I will show an example using the 
Catalyst framework.

# 1. Create the application named OurApp:

catalyst.pl OurApp

# 2. Go to the application's home dir:

cd OurApp

#3. Create the tables in the "ourapp" database using the definitions shown 
below:

create table continent(
id int unsigned not null auto_increment primary key,
name varchar(20),
hemisphere varchar(10)
) engine=InnoDB;

create table country(
id int unsigned not null auto_increment primary key,
id_continent int unsigned not null,
name varchar(30),
national_day date,
foreign key(id_continent) references continent(id)
) engine=InnoDB;

create table city(
id int unsigned not null auto_increment primary key,
id_country int unsigned not null,
name varchar(30),
population int unsigned,
foreign key(id_country) references country(id)
) engine=InnoDB;

# 4. Generate the DBIx::Class result classes:

perl script/ourapp_create.pl model DB DBIC::Schema OurApp::Schema 
create=static dbi:mysql:database=ourapp root

# 5. Create a Template-Toolkit view, named TT

perl script/ourapp_create.pl view TT TT

# 6. Create a controller named Continent, and change its content:

perl script/ourapp_create.pl controller Continent

package OurApp::Controller::Continent;

use strict;
use warnings;
use parent 'Catalyst::Controller';

sub index :Path :Args(0) {
  my ( $self, $c ) = @_;

  #Get a variable from a submitted form:
  my $continent_name = $c->req->params->{continent_name};

  #Select the continents from the Northern hemisphere:
  my $continents = $c->model("DB::Continent")->search({
    hemisphere => 'North',
  });

  #Until this point, no query is sent to the database.

  #If the user asked for a certain continent in this hemisphere, add it as a 
filter:
  $continents = $continents->search({name => $continent_name}) if 
$continent_name;

  #Until this point, no query is sent to the DB yet and more such filters 
may be applied.

  #Add the $continents object to the stash, for printing data from it in the 
templates
  $c->stash->{continents} = $continents;

  #This last line of the subroutine also didn't send anything to the 
database.
}

#And here the action subroutine ended, without sending anything to the 
database.

#The default TT template root/continent/index.tt is processed by the TT view 
at this point.

1;

# 7. Create the content of the template file (root/continent/index.tt):

<html><head><title>Continents</title></head><body>

<form>
  Continent: <input type="text" name="continent_name">
  <input type="submit"><br><br>
</form>

<h1>Print the information for the selected continents:</h1>

[% FOREACH continent IN continents.all %]
  Continent name: [% continent.name %]<br>
  Continent hemisphere: [% continent.hemisphere %]<br><br>

  <h2>Print the information for the countries from [% continent.name 
%]:</h2>

  [% FOREACH country IN continent.countries %]
    Country name: [% country.name %]<br>
    Country national day localized: [% 
country.national_day.set_locale('fr').strftime('%e %b %Y') %]<br><br>

    <h3>Print here information for the cities from [% country.name %]:</h3>

    [% FOREACH city IN country.cities %]
      City name: [% city.name %]<br>
      city population: [% city.population %]<br><br>
    [% END %]
  [% END %]
[% END %]

</body></html>

# 8. Start the application using the development server:
perl script/ourapp_server.pl -p 80

# 9. And access the controller at the URL:
http://localhost/continent

By default, it will show the whole list of continents from the northern 
emisphere that were inserted in the database, their countries and their 
cities.

If you will filter the results and add the name of a certain continent in 
the form field, it will display only that continent, its countries and their 
cities.

Before starting the development server, set the environment variable 
DBIC_TRACE to 1, for printing the SQL queries that are generated on each 
request.

If you will type the name of the continent "NonExistent" in the text field 
of the form, the single SQL query that will be generated would be:

SELECT me.id, me.name, me.hemisphere FROM continent me WHERE ( ( name = ? 
AND hemisphere = ? ) ):
'NonExistent', 'North'

It won't find this continent, and it won't send the other SQL queries to the 
DB for getting information about countries and cities.

If the continent "Asia" is inserted in the DB, but no countries are inserted 
for it, the generated SQL queries will be:

SELECT me.id, me.name, me.hemisphere FROM continent me WHERE ( ( name = ? 
AND hemisphere = ? ) ):
'Asia', 'North'

SELECT me.id, me.id_continent, me.name, me.national_day FROM country me 
WHERE (
me.id_continent = ? ): '4'

The program found that continent, searched for its countries, but didn't 
found anyone, so it wasn't necessary to search for cities.

If the user searched for "Europe", the queries that were sent to the 
database were:

SELECT me.id, me.name, me.hemisphere FROM continent me WHERE ( ( name = ? 
AND he
misphere = ? ) ): 'Europe', 'North'

SELECT me.id, me.id_continent, me.name, me.national_day FROM country me 
WHERE (
me.id_continent = ? ): '3'

SELECT me.id, me.id_country, me.name, me.population FROM city me WHERE ( 
me.id_country = ? ): '1'

SELECT me.id, me.id_country, me.name, me.population FROM city me WHERE ( 
me.id_country = ? ): '2'

It found this continent and its countries, and the cities of those 
countries.

As you may see, it searched twice in the table city for getting the cities 
because there are 2 countries that were inserted in the DB. The program can 
be optimized to search only once for all the cities in a certain continent, 
than display only the cities for the current country, but what I wanted to 
show here is that DBIx::Class touches the database only when it is really 
necessary, and using a very clean code.

The result shows like:

Continent: [    ]
 Submit

Print the information for the selected continents:

Continent name: Europe
Continent hemisphere: North

Print the information for the countries from Europe:

Country name: Germany
Country national day localized: 5 mars 1900

Print here information for the cities from Germany:

City name: Berlin
city population: 10000000

City name: Frankfurt am Mein
city population: 200000

Country name: Romania
Country national day localized: 1 d? 1918

Print here information for the cities from Romania:

City name: Bucharest
city population: 2500000

City name: Cluj-Napoca
city population: 400000

------------

Of course, this is just sample data, and the dates are not valid.

As you might have seen, you made only a single search in the database, in 
the table "continent", but using the $continents object you are able to 
access much more data without needing to define new SQL queries.

Of course, there are many methods for accessing the data. For example you 
could even use in a template:

[% continents.all.first.countries.first.name %]<br>



Octavian




More information about the Catalyst mailing list