Tuesday, March 27, 2018

A Simple Mojolicious Application Using DBI Showcasing Insertion, Updation And Deletion Of Records

In the following example we are building a simple application using Mojolicious which is one of the relatively new, 
light-weight, modern web application frameworks of Perl. 

Here we shall be using the 'Mojolicious::Lite' CPAN module specifically.

Here the following Application or Program name is 'hello_mojo.pl'

#!/usr/bin/perl
use Mojolicious::Lite;

# connect to database
use DBI;
my $dbh = DBI->connect("dbi:SQLite:database.mydb","","") or die "Could not connect";

# creating table if same already not existing
$dbh->do('CREATE TABLE IF NOT EXISTS people (emp_id INTEGER PRIMARY KEY AUTOINCREMENT, name varchar(255),
 age int)');

# shortcut for use in template and inside routes
helper db => sub {$dbh};

# The following blocks are called routes where respective functionalities are defined to be executed 

# when '/' or base url is called by any method (GET/ POST) 
any '/' => sub {
	my $self = shift;
	my $message;

	# assigning a template variable
	$self->stash(message => '');

	# calling a template by it's first name to render output
	$self->render('index');
};

# when '/insert' url is called by any method (GET/ POST) 
any '/insert' => sub {
	my $self = shift;
	my $name = $self->param('nm');
	my $age  = $self->param('age');

	$self->db->do(qq{INSERT INTO people(name, age) VALUES(?,?)}, undef, $name, $age);
	$self->redirect_to('/');
};

# when '/edit' url is called by any method (GET/ POST) 
any '/edit' => sub {
	my $self = shift;
	my $emp_id = $self->param('emp_id');
	
	# assigning a template variable with respective value
	$self->stash(emp_id => $emp_id);
	$self->render('edit_form');

};

# when '/edit_record' url is called by any method (GET/ POST) 
any '/edit_record' => sub {
	my $self = shift;	
	my $emp_id = $self->param('emp_id');
	my $name = $self->param('nm');
	my $age  = $self->param('age');

	$self->db->do(q[UPDATE people SET name = ?, age = ? WHERE emp_id = ?], undef, $name, $age, $emp_id);
	$self->redirect_to('/');
};

# when '/delete' url is called by any method (GET/ POST) 
any '/delete' => sub {
	my $self = shift;
	my $emp_id = $self->param('emp_id');

	$self->db->do(q[DELETE FROM people WHERE emp_id = ?], undef, $emp_id);
	
	my $message;
	$self->stash(message => 'Record Deleted');	
	$self->render('index');
};

# staring the mojolicious application, should be the last expression in the application
app->start;


# Following segment defines the embedded template definitions, here we have used 2 templates 
one 'index.html.ep' and the other 'edit_form.html.ep'
__DATA__

@@ index.html.ep
% my $sth = db->prepare(qq{select emp_id, name, age from people order by emp_id});


	My Mojolicious Example


	<%= $message %>
	
Enter Name :
Enter Age :


% $sth->execute(); % my $i=0; % while(my ($emp_id, $name, $age) = $sth->fetchrow_array()) { % $i++; % } % $sth->finish();
Sl No:NameAgeAction
<%= $i %> <%= $name %> <%= $age %> Edit / Delete
@@ edit_form.html.ep % my $sth = db->prepare(q{select name, age from people where emp_id = ?}); % $sth->execute($emp_id); % my ($name, $age) = $sth->fetchrow_array(); % $sth->finish(); My Form
Edit Record
Enter Name :
Enter Age :

Steps To Run The Application:
-----------------------------
1) On the command prompt goto the directory where the application is created, type the following and hit enter.

morbo hello_mojo.pl

Output of the above command: Server available at http://127.0.0.1:3000

2) Now the application is running and the desired html output can be viewed in the web browser using 
http://127.0.0.1:3000 or http://localhost:3000 or whatever is the server address using port 3000.