Docendo discimus


  • Calendar

    September 2009
    M T W T F S S
    « Aug   Oct »
  • Archives

  • Recent Posts

  • Bling

Catalyst is easy – using the database

Posted by brunorc on September 19, 2009

There was not too much about doing the Catalyst things recently. I went into some musings about Perl: why it is cool, relevant and blah.

But recently I had a nice conversation with a friend, who works in the same company. He has a strong Java background, but frankly it didn’t turn him into a brainless, corporative ant. It’s quite interesting, but even now, when I work in the Perl-driven company, I find myself having conversations with Java people :-) And those conversations are mostly nice, so maybe that’s why I keep having them. Or maybe just those people are interesting and nice, regardless of their favourite programming language? Nevertheless, this guy also plays Magic – and he’s quite good at this. So there’s at least one thing that really unites us.

Anyway, I like his pragmatic point of view. And this conversation gave me the impulse to once again think about coolness and relevancy. A programming language is cool and relevant, if it helps you in writing programs, that solve your problems. If you can do it in a fast and consistent, elegant manner, then everything is OK. This compels me to cut my musings and go back to the real Perl.

First “useful” application shown here was a non-persistent life counter for a duel of Magic planeswalkers. If someone liked to make it more persistent, he would probably think about the database – you know, those huge things Oracle used to sell before people discovered that SQL must die, because Erlang is cooler. But for the Magic counter we don’t need Oracle, SQLite will be quite enough.

Now let’s go to the application directory – I assume you are able to type in your console:

sqlite3 magicount.db

and get output similar to this one:

SQLite version 3.4.0
Enter ".help" for instructions

which roughly translates to it works! If it doesn’t, go to the link above, download the SQLite for your operating system, install it and try again. If it works for you as well, it is the time to create the database and populate it:

sqlite> CREATE TABLE player ( id INTEGER PRIMARY KEY, name TEXT );
sqlite> INSERT INTO player (name) VALUES ('Bruno');
sqlite> INSERT INTO player (name) VALUES ('Betka');
sqlite> INSERT INTO player (name) VALUES ('Chris');
sqlite> INSERT INTO player (name) VALUES ('Giel');
sqlite> .quit

In SQLite, INTEGER PRIMARY KEY means that field will be autoincremented, so we don’t need to specify the value for id. After quitting we can check if there’s something inside:

sqlite3 magicount.db
SQLite version 3.4.0
Enter ".help" for instructions
sqlite> select * from player;

Who would expect, eh?

Now it is the time to create the new Model for the database. It is achieved by the following incantation:

script/ model MyModelName DBIC::Schema MySchemaClass create=static dbi:DB_type:how_to_get_the_DB

MyModelName looks rather obvious – it is the name of the model; it will be used later to access it, so it should be meaningful. MySchemaClass is – sure – the name of the class, representing the DB schema. The create option can have two values: static and dynamic, but since nowadays you can recreate your DB-based classes without losing the code written by yourself, static is the best choice. The last argument is the DSN, used by underlying DBI module – it should include the type of the database (so DBI can use the appropriate driver) and the way to access it (if you want some more elaborated description, check the documentation of the DBIC::Schema helper). So, in the end, everything should look like this:

script/ model MCDB DBIC::Schema MagiCount::Schema create=static dbi:SQLite:magicount.db

MCDB stands for MagiCount DataBase – not very fancy, but quite short. We use the SQLite driver and give the name of the file, where the database is stored. If everything is OK and you have installed all necessary modules (you can check the list here – usually they will be installed with the most important one, Catalyst::Model::DBIC::Schema, but this list may be handy in case of obstacles), you should get the similar output:

exists "/Users/bruno/devel/MagiCount/script/../lib/MagiCount/Model"
exists "/Users/bruno/devel/MagiCount/script/../t"
Dumping manual schema for MagiCount::Schema to directory /Users/bruno/devel/MagiCount/script/../lib ...
Schema dump completed.
created "/Users/bruno/devel/MagiCount/script/../lib/MagiCount/Model/"
created "/Users/bruno/devel/MagiCount/script/../t/model_MCDB.t"

Go take a look at the created files – probably the most interesting one will be lib/MagiCount/Schema/Result/

package MagiCount::Schema::Result::Player;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("InflateColumn::DateTime", "Core");
    data_type => "INTEGER",
    default_value => undef,
    is_nullable => 1,
    size => undef,
    data_type => "TEXT",
    default_value => undef,
    is_nullable => 1,
    size => undef,

# Created by DBIx::Class::Schema::Loader v0.04006 @ 2009-09-19 15:06:06

# You can replace this text with custom content, and it will be preserved on regeneration

As you can see, it is the Perl representation of the SQL code. Thanks to this Perl is aware of the structure of tables and all fields included. Also, the library that powers this stuff – DBIx::Class – is not only able to create such files from the existing database; you can use it the other way round, shaping your database with the Perl code, as DBIx::Class will create the structure of tables described in modules (check the documentation for DBIC). Also, you can freely add your code below the MD5 sum line and it will be maintained.

This all looks cool, but there’s a lot of stuff behind and around it, so now I will only show how to use it on a very simple example. Instead of writing the names of players in the form, we’re going to use the HTML select elements. In the create.tt2 file we can change:

      <label for="player1">Name of the first player</label>
      <input type="text" name="player1" size="12" /><br />


      <label for="player1">Name of the first player</label>
      <select name="player1">
        [% FOREACH player IN c.model('MCDB::Player').all %]
        <option value="[% %]">[% %]</option>
        [% END %]
      </select><br />

doing the same for the second player. And if we want to have players ordered alphabetically:

        [% FOREACH player IN c.model('MCDB::Player').all.sort('name') %]
        <option value="[% %]">[% %]</option>
        [% END %]

Of course we’re accessing Model from the View and doubling the amount of database calls. But at least our players are persistent! In the next episode: updating the contents of the table.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: