The other component that many web applications require is a SQL relational database. Technically whatever ways are available to Perl for accessing databases, Catalyst has them all. The most popular is via an Object-relational mapper (ORM) called DBIx::Class (DBIC). Object-relational mappers allow you to perform operations on your database as though each database object were a Perl object. This means that instead of writing SQL like SELECT * FROM table, you can instead say @results = $table_resultset->all. The advantage of this approach is that DBIC handles the SQL for you, so you can switch from SQLite to DB2 without modifying any of your code. The resulting code in your Controller is also more readable; everything looks like a manipulation of Perl objects and data structures. We'll see the power of this approach throughout the book, but for now, let's just create a simple DBIC Model.
You can use any database that you like for this, but I recommend SQLite for development. SQLite is an "embedded" database, so the database exists as a single file and requires no server to run.
The first step is to install SQLite from the SQLite website at http://sqlite.org/ (or through your distribution's package manager, the package is usually called "sqlite3"). You'll also need to install the Perl version of SQLite, called DBD::SQLite, and DBIx::Class itself as well as an adapter that will link our database schema definition to the Catalyst application. You can install these, except for SQLite, using cpan with the following:
$ cpan -i DBD::SQLite DBIx::Class Catalyst::Model::DBIC::Schema
Once the dependencies are installed, we'll create a sample database with the sqlite3 command-line utility:
$ sqlite3 tmp/database
SQLite version 3.3.8
Enter ".help" for instructions
sqlite> CREATE TABLE test (id INTEGER PRIMARY KEY, subject TEXT, message
TEXT, date DATETIME);
sqlite> INSERT INTO test (id, subject, message, date) VALUES(NULL, "test message", "this is a test message", "2005-01-01 00:00");
sqlite> INSERT INTO test (id, subject, message, date) VALUES(NULL, "another test message", "this is a another test message, hooray", "2005-
01-01 00:01");
sqlite> SELECT * FROM test;
1|test message|this is a test message|2005-01-01 00:00
2|another test message|this is a another test message, hooray|2005-01-01
00:01
sqlite> .quit
$
Here we created a test table in a database file called tmp/database and added two rows to it.
Note
SQLite has unusual semantics for auto-incrementing primary keys. You must declare the auto-increment column exactly as "INTEGER PRIMARY KEY" and then assign NULL during INSERT operations. DBIC will handle this automatically, but you should be aware of this unconventional approach when interacting with the database from within the sqlite3 utility.
To use this database from Catalyst, we need to create a Catalyst Model. We can do this using the following command line:
$ perl script/myapp_create.pl model TestDatabase DBIC::Schema MyApp:: Schema::TestDatabase create=dynamic dbi:SQLite:tmp/database
If you are on Windows, you may want to be careful in replacing / with whenever filesystems are referred.
The first argument is the name of the Catalyst Model (TestDatabase). DBIC::Schema is what sort of model we're creating. MyApp::Schema::TestDatabase is where the schema definition will be stored (we won't use the Schema in this example, but real applications will). create=dynamic tells DBIC to read the database every time the application is started to determine the schema (layout of tables, foreign key relations, and so on.). The final argument is the DBI connect string for the database.
We'll also create another Controller to learn to create a database Model as follows:
$ perl script/myapp_create.pl controller Database
Now, we'll want to create code to show this template in the Database.pm Controller. In this case, using the index action that's automatically generated will be fine.
We will need to populate a variable called messages with the data from our database. The code to do this looks like the following:
sub index :Path Args(0) { # same as index :Private my ( $self, $c ) = @_;
$c->stash->{messages} = $c->model('TestDatabase::test')->search({});
}
This is almost the same as our hello action, but in this case we fill the messages variable with the entries in the test table (in the TestDatabase Model). The $c->model(...) syntax allows you to access any Model by name. DBIC::Schema supports extra syntax to allow you to name a table in the database directly (the ::test part), so we use that to create a ResultSet object. The ResultSet is an iterator object that will only access the database when it is required; this is to make sure it doesn't fetch any data that isn't required by the application. The ->search({}) part will make sure that you receive a fresh ResultSet object.
Once we have this, we'll create a template to show data from the database in a file called root/database/index.tt. It should look like the following:
<?xml version="1.0" encoding="utf-8"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <htmlxmlns="http://www.w3.org/1999/xhtml" xml:lang="en"> <head> <title>Hello, database!</title> </head> <body> <h1>Database</h1> <p>Here's what the database looks like: </p> <ol> [% WHILE (message = messages.next) %] <li> <p>Message <b>[% message.subject | html %]</b> (#[% message.id | html %]):</p> <p>[% message.message | html %]</p> <p>Written at <i>[% message.date | html %]</i>.</p> </li> [% END %] </ol> </body> </html>
The [% WHILE (message = messages.next) %] command loops over each row in the ResultSet for the test table, and creates a variable called message that holds that row. The other [% ... %] commands in the template simply extract the data from the row via the column name. That's all there is to it—Catalyst handles the hard stuff for you!
To view your database as an HTML page, start the development server again and browse to http://localhost:3000/database.
You will see something like the following:
