MVC: Dancing with a Model

Dancer is a RESTful framework like Sinatra but I want to use Dancer in a MVC application model because allow us to have more efficient code reusing components and separate the logic, the data management and the way the information is displayed.

What is MVC?

There are a lot of tutorials everywhere explaining what MVC is. As a quick explanation, MVC stand for Model View Controller. Model is the data, View is an interface to view the data that is changed or processed in the Controller. To know more about MVC you may read these:

• http://tomdalling.com/blog/software-design/model-view-controller-explained/
• http://www.sitepoint.com/getting-started-with-mvc/

The Model

This is all about this post will be: The Model. As mentioned above, the model does nothing else but data management. It does not depend of the controller or the view.

There are a lot of Perl modules to handle the data. I use Dancer::Plugin::Database which use DBI::db module, however there are others modules such as Rose::DB and Dancer::Plugin::DBIC which use DBIx::Class. I'll provide an example with Dancer::Plugin::Database and Dancer::Plugin::DBIC.

Dancer::Plugin::Database

I use Dancer::Plugin::Database for projects that do not require a complex database design. It handle the connection to the database and it support many database managers such as SQLite, MySQL, PostgresSQL, Oracle, etc... For development I use SQLite and it is the one I'll use in this post but you may consider MySQL or PostgresSQL.

First you have to do is to install the Dancer::Plugin::Database module executing this line and update the Modfile with same line. Also, make sure you have installed the module for the database manager you will use, in this case is SQLite

	cpanm Dancer::Plugin::Database
	cpanm DBD::SQLite

view raw Install_Dancer-Plugin-Database.sh hosted with ❤ by GitHub

Configure the connection to the DB. Open environments/development.yml to add these lines to the end:

	plugins:
	Database:
	driver: SQLite
	database: 'db/development.sqlite'

view raw Dancer-Plugin-Database_SQLite_Config.yml hosted with ❤ by GitHub

Remember to use space instead of tab in any YAML (.yml) file, Dancer will complain if you use tabs.

Create the directory db, where all the databases will be (just like Rails). Create the db/migrations directory to have the following SQL files to create the tables.

db/migration/001_up_teams.sql will be used to create the teams:

	BEGIN;
	CREATE DATABASE IF NOT EXISTS my_application;
	USE my_application;
	/*
	TABLE: Teams
	DESC: It is a real team such as UNIX Admins, DBAs or Human Resources.
	*/
	DROP TABLE IF EXISTS Teams;
	CREATE Teams (
	id INTEGER PRIMARY KEY NOT NULL,
	name TEXT NOT NULL,
	email TEXT,
	create_on TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	update_on TIMESTAMP NULL
	);
	COMMIT;

view raw 001_up_create_DB_and_Teams.sql hosted with ❤ by GitHub

.. and db/migration/001_down_teams.sql to eliminate them:

	BEGIN;
	USE my_application;
	DROP TABLE IF EXISTS Teams;
	DROP DATABASE IF EXISTS my_application;
	COMMIT;

view raw 001_down_drop_Teams_and_DB.sql hosted with ❤ by GitHub

Same for the Users, use db/migration/002_up_users.sql to create the users:

	PRAGMA foreign_keys=ON;
	BEGIN;
	/*
	TABLE: Users
	DESC: Users to access the application.
	*/
	DROP TABLE IF EXISTS Users;
	CREATE TABLE Users (
	id INTEGER PRIMARY KEY NOT NULL AUTO_INCREMENT,
	username TEXT NOT NULL,
	password TEXT NOT NULL,
	contact_id INTEGER,
	role_id INTEGER,
	create_on TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
	update_on TIMESTAMP NULL,
	FOREIGN KEY(contact_id) REFERENCES Contacts(id) ON UPDATE CASCADE,
	FOREIGN KEY(role_id) REFERENCES Roles(id) ON UPDATE CASCADE
	);
	COMMIT;

view raw 002_up_create_Users.sql hosted with ❤ by GitHub

.. and db/migration/002_down_users.sql to eliminate them:

	BEGIN;
	USE my_application;
	DROP TABLE IF EXISTS Users;
	COMMIT;

view raw 002_down_drop_Users.sql hosted with ❤ by GitHub

Now, lets apply those migrations:

sqlite3 db/development.sqlite < db/migrations/001_up_teams.sql 
sqlite3 db/development.sqlite < db/migrations/002_up_users.sql

If something goes wrong you can always rollback with:

sqlite3 db/development.sqlite < db/migrations/002_down_users.sql 
sqlite3 db/development.sqlite < db/migrations/001_down_teams.sql 

IMPORTANT: For every statement you do in the UP sql file, you have to undo it in the DOWN sql file and in the opposite order.

In order to test your application you need to fill the database with some data. We can do that with a seeder sql file. Create the db/development.seed.sql file with this:

	-- Adding Teams
	INSERT INTO Teams (name, email) VALUES ('App Admin', 'app_admin@company.com');
	INSERT INTO Teams (name, email) VALUES ('Finance', 'finance@company.com' );
	-- Adding Users
	INSERT INTO Users (first_name, last_name, email, team_id) VALUES ('Johandry', 'Amador', 'johandry@company.com', 1);
	INSERT INTO Users (first_name, last_name, email, team_id) VALUES ('John', 'Smith', 'johnsmith@company.com', 2);
	INSERT INTO Users (first_name, last_name, email, team_id) VALUES ('Juan', 'Smith', 'juansmith@company.com', 2);

view raw development.seed.sql hosted with ❤ by GitHub

Confirm the database content with:

$ sqlite3 development.sqlite "SELECT * FROM Users;"
1|Johandry|Amador|johandry@company.com|1|2013-12-22 07:10:30
2|John|Smith|johnsmith@company.com|2|2013-12-22 07:10:30
3|Juan|Smith|juansmith@company.com|2|2013-12-22 07:10:30
$ sqlite3 development.sqlite "SELECT * FROM Teams;"
1|App Admin|app_admin@company.com|2013-12-22 07:09:47
2|Finance|finance@company.com|2013-12-22 07:09:47

In case you want to confirm the same from the application, create this temporal route in the lib/myapplication.pm file:

	use Dancer::Plugin::Database;
	get '/testdb' => sub {
	my @teams = database->quick_select('Teams', {});
	my @users = database->quick_select('Users', {});
	template 'testdb' => { teams => \@teams, users => \@users }
	};

view raw test_rule.pl hosted with ❤ by GitHub

And the template views/testdb.tt with this:

	<div id="page">
	<table class="table">
	<thead>
	<tr>
	<th>#</th>
	<th>Name</th>
	<th>Email</th>
	</tr>
	</thead>
	<tbody>
	<% FOREACH team IN teams %>
	<tr>
	<td><% team.id %></td>
	<td><% team.name %></td>
	<td><% team.email %></td>
	</tr>
	<% END %>
	</tbody>
	</table>
	<table class="table">
	<thead>
	<tr>
	<th>#</th>
	<th>Name</th>
	<th>Email</th>
	<th>Team</th>
	</tr>
	</thead>
	<tbody>
	<% FOREACH user IN users %>
	<tr>
	<td><% user.id %></td>
	<td><% user.first_name %> <% user.last_name %></td>
	<td><% user.email %></td>
	<td><% teams.${user.team_id}.name %> (<% teams.${user.team_id}.email %>)</td>
	</tr>
	<% END %>
	</tbody>
	</table>
	</div>

view raw test_rule_template.html hosted with ❤ by GitHub

IMPORTANT: You need to switch from Template::Simple to Template::Toolkit. I'll not explain this change here but basically you need to uncomment the template_toolkit lines and comment the template simple line in config.yml.

In a next post I'll write about Dancer::Plugin::DBIC but now you have at least one model to dance with.