In this article, we will start by setting up and seeding the database. A Minimum Viable Product specification of a forum is:
→ Threads
→ Replies
→ Users
→ Database to store information

Once the database is setup, we need routes, to view the database data on our web application.

For our Project, we are using:
→ Dancer2 as framework
→ Template Toolkit (TT) for developing and rendering HTML views
→ Bootstrap for front-end
→ MySQL as a database (It is unrestricted to MySQL, you can use any database)

Database Setup

Let’s start by creating the database in MySQL first.

We want our forum application DB isolated from other applications, therefore we create a new database in MySQL.

If you have phpmyadmin/MySQL Workbench/SQL pro, you can use these tools to set up a database and it’s tables. Otherwise, use the command line terminal.

mysql -u root -p

Enter the administrative password and you will be at MySQL Command prompt.

Create a new database by typing the following command:

CREATE DATABASE PForums;

PForums is our database name. Anyways, you are not limited to use this name. You are liable to select any name for your application database.

Select the database for sequential operations.

USE PForums;

Select the database for successive operations.

Next step is to create Users, Threads, and Replies table.

The figure illustrates the relationship between Users, Threads, and Replies table.

We can understand it as:

→ Users may have threads.

→ Users may add replies to threads.

→ Replies belong to thread.

We need to establish a relationship between:

→ Users and Threads table
→ Users and Replies table
→ Threads and Replies table

Create Users table.

CREATE TABLE `Users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `first_name` varchar(25) NOT NULL DEFAULT ‘’,
 `middle_name` varchar(25) DEFAULT NULL,
 `last_name` varchar(25) NOT NULL DEFAULT ‘’,
 `email` varchar(50) DEFAULT NULL,
 `password` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Threads Table.

CREATE TABLE `Threads` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `body` mediumtext,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `threads_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Replies Table.

CREATE TABLE `Replies` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `thread_id` int(11) unsigned DEFAULT NULL,
  `body` mediumtext,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `thread_id` (`thread_id`),
  CONSTRAINT `replies_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `Users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `replies_ibfk_2` FOREIGN KEY (`thread_id`) REFERENCES `Threads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

We have successfully created the database, tables, and Columns. We also establish a relationship between tables. The following task is to seed the database with data.

You can download the seeding file from here.

To import the SQL file to your database.

mysql -u username -p PForums < export_all.sql

To import the file directly from git

curl 'https://github.com/akuks/PerlForum/blob/master/Utilities/export_all.sql' | 
mysql -u username-p PForums

we seed data to the database, but there is one concern with it:

→ How the end-user view data?

→ How to retrieve data from the database?

In this subsequent section, we will tackle these issues.

Dancer 2 Routes

By now, we know how to start the Dancer2 Web application.

Let’s understand how it works.

Whenever the Dancer2 App starts, it will read the contents of the app.psgi file under bin directory.
In the following step, it will load the PForums.pm Perl Package.
3rd step will load all the routes, (Take a look at get ‘/’ => sub { template ‘index’ => {‘title’ => ‘PForums’};}; At present, we have one default route added by the Dancer2. → It will upload the templates (under the views directory) and also pass the parameters to the template.

We will discuss templates and variable handling later.

To understand Routes, let’s add another route to PForums.pm

get ‘/hello’ => sub {
 return “Welcome to Dancer2 Web Application”;
};

It says, load hello route and instead of template return string “Welcome to Dancer2 Web Application”.

Start the app:

plackup -a bin/app.psgi

Open the URL http://localhost:5000/hello , will load

Dancer2 Routes Example

Isn’t it easy?

Let’s focus on our problem now. Just think about what we need to show from threads table to the frontend.

→ A database connection.

→ Query to show all thread data to the frontend.

Dancer2 Database Connection Setup

To setup, the DB connection efficiently, Install Dancer2::Plugin::Database Perl package.

Install the following Perl packages.

cpan install Moose
cpan install Config::General
cpan install MooseX::NonMoose
cpan install MooseX::MarkAsMethods
cpan install DBIx::Class
cpan install DBD::mysql
cpan install Dancer2::Plugin::Database

After installing the Packages, use dbicdump command, to create the Database Schema.

Create a new file db.conf under the root directory of Dancer2 Application.

Add the following content to the file.

schema_class PForums::Schema<connect_info>
    dsn dbi:mysql:PForums
    user root
    pass
</connect_info><loader_options>
    dump_directory  ./lib
    components  InflateColumn::DateTime
    use_moose   1
</loader_options>

Save the file and execute.

dbicdump db.conf

This will dump the DB Schema under the ‘lib’ folder. You need to install the dbicdump if it is not installed already.

Updated Structure of lib folder will look like the below figure.

New Structure of lib folder

Modify config.yml

Dancer2 doesn’t know the username and password of the database by default. And we don’t want to hardcode the Username and Password in any of the Perl Package.

We have config.yml, created when the Dancer2 Application was created.

Open the file and include:

plugins:
    DBIC:
        PForums:
            schema_class: PForums::Schema
            dsn: dbi:mysql:PForums
            user: root
            pass: ''

PForums → Name of our database. You can use yours.
user → DB username, usually it is not the recommended practice to use root user but for this tutorial we will employ it as an exception and off-course you can use your own db user name.
pass → DB password

Dancer2 uses, Simple template by-default. We desire to change to template-toolkit to induce modern features. Include the following content to the config.yml:

template: "template_toolkit"
engines:
    template:
        template_toolkit:
        start_tag: '[%'
        end_tag:   '%]'

It’s time to modify the templates to show our seed data.

Open the file index.tt under the views directory and delete all content in it.

Open file. main.tt under the views/layout directory and add

<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/css/bootstrap.min.css" integrity="sha384-WskhaSGFgHYWDcbwN70/dfYBj47jz9qbsMId/iRN3ewGhXQFZCSftd1LZCfmhktB" crossorigin="anonymous">

BootStrap added to main.tt template file

Add the following lines, just above the **** tag.

<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js" integrity="sha384-q8i/X+965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH+8abtTE1Pi6jizo" crossorigin="anonymous">
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.3/umd/popper.min.js" integrity="sha384-ZMP7rVo3mIykV+2+9J3UJ46jBk0WLaUAdn689aCwoqbBJiSnjAK/l8WvCWPIPm49" crossorigin="anonymous"></script><script src="https://stackpath.bootstrapcdn.com/bootstrap/4.1.1/js/bootstrap.min.js" integrity="sha384-smHYKdLADwkXOn1EmN1qk/HfnUcbVRZyYmZ4qpPea6sjB/pTJ0euyQp0Mk8ck+5T" crossorigin="anonymous">
</script>

Creating index.tt Layout

Open index.tt under views directory and add the following content

<nav class="navbar navbar-expand-sm bg-dark navbar-dark"><a class="navbar-brand" href="/">PForums</a><ul class="navbar-nav">
        <li class="nav-item">
            <a class="nav-link" href="">Topics</a>
        </li>
        <li class="nav-item">
            <a class="nav-link" href="#">Sign In</a>
        </li>
        <li class="nav-item">
            <a class="nav-link" href="#">Sign Up</a>
        </li>
    </ul>
</nav>

If you open the browser and hit the URL http://localhost:5000/, it will look like

Updating the index.tt

Topics represent all the threads created under PForums web application. Sign In and Sign Up are pretty obvious.

Next is to show the threads from the database on the index page.

In the PForums.pm file, replace get ‘/’ => sub {};

with

get '/' => sub {
    my @threads = schema->resultset('Thread')->all();template 'index'   => {
         'title'   => 'PForums' ,
         'threads' => \@threads
    };
};

Code Explanation:

my @threads = schema->resultset('Thread')->all();

Above line retrieve the results from the Thread table and stores in @threads array. Filtering of the result is not required as we need to show all the threads present in the database.

After retrieving the results from the database in an array, we need to pass @threads variable to the template i.e. index.tt

'threads' => \@threads

Make sure following packages are imported at the top of the file

package PForums;use Dancer2;
use Dancer2::Plugin::DBIC;
use strict;
use warnings;

At the moment, we are ready with the backend. We need to show the results on the frontend now.

Open the file index.tt and add the following content at the end of the file.

<strong>
    All Topics
</strong>
<hr>
[% FOREACH thread IN threads %]
    [% thread.title %]
[% END %]

threads → variable that we pass to the template

FOREACH → To iterate the threads variable

thread.title → display the title column of the thread table

After restarting the Web Services, our Web application will look like:

All Threads

We can view the threads on the page, but the display is badly organised, in order to tackle this issue *horizontal row * must be added after each thread title.

<strong>
    All Topics
</strong>
<hr>
[% FOREACH thread IN threads %]
    [% thread.title %]
    <hr>
[% END %]

Launch the web application again.

After adding Horizontal row after title of the thread.

Now, the look and feel of the Application is much better than before.

In this tutorial, we learn

✔ How the Dancer2 Routes works?

✔ How to modify the Dancer2 configuration file?

✔ How to use dbicbdump to load the schema to the Application?

✔ How template toolkit works?

✔ To show the data from database to the front end

Our Application looks satisfactory, but it is very far from production ready web application. But still it is a good point to start with.