Episode — 2 : Dancer2 MySQL Database Setup and Seeding

In this Episode, 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 will 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 Command line terminal.

Sign in to MySQL database from command prompt:

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 restricted 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 belongs 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 and tables, and Columns. We also establish the relationship between tables. Following task is to seed the database with data.

You can download the seeding file from here.

To import 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_](https://github.com/akuks/PerlForum/blob/master/Utilities/export_all.sql)' | mysql -u username-p PForums

Summary of the tutorial:
 ✔ Create Database
 ✔ Create tables
 ✔ Establish the relationship between tables.
 ✔ Seed the database with raw data.

In the following tutorial, we will discuss Dancer2 Routes.

If you have any questions regarding this tutorial, please put it in comments section.