Database of Web Live Chart Application using Perl and MySQL
Web Live Text Chart Application using Perl and MySQL - Part 3
Web Development with Perl and MySQL
Foreword: In this part of the series, I talk about the database and the tables for the web live chart application.
By: Chrysanthus Date Published: 9 Sep 2016
Introduction
Operation of Application
When the client (user) wants to chart, he clicks a link in a normal web page and a small chart web window opens. He charts with the server operator using this window. At the other end, the server operator has a full web window that is always open (displayed). He carries out his conversation with the client in the window.
It is the client who initiates conversation as follows: The client small window that opens actually displays two documents, one followed by the other. In the first document, the client types in his first name and email address. When he submits this form, two tables called, chartMonitor and discussion are created in a database called, chartdb at the server. The database is already created and is always there. The creation of these tables initiates conversation. In your project, you can give whatever names you want for chartMonitor, discussion and chartdb.
The operator window is always checking for the existence of these tables, through a Perl code at the server. As soon as it sees them it knows that conversation has been initiated. It alerts the operator of this with an ECMAScript alert box. In good browsers this alert box produces a sound. After clicking the OK button of this alert box, the operator types and sends to the client, the first message. From there, conversation continues.
It is the operator who ends the conversation by clicking an End Conversation Button. When he clicks this button, the two tables of the database are erased. The database remains empty.
So, in this project, the client initiates conversation and the operator starts conversation; you will appreciate the meaning of this later.
The Create Table code for the two tables is:
create table discussion
(
discussID smallint NOT NULL AUTO_INCREMENT,
Person CHAR(40),
msg varchar(3000),
PRIMARY KEY (discussID ASC)
);
create table chartMonitor
(
tableID smallint default 1,
clientStart char(10),
operatorStart char(10),
clientMsgState char(5),
operatorMsgState char(5),
lastClientID smallint,
lastOperatorID smallint,
clientName varchar(30),
operatorName varchar(30),
clientWriting char(10),
operatorWriting char(10),
clientEmail varchar(40)
);
The discussion table has the messages. The chartMonitor table has data, which changes as the conversation progresses to coordinate the massages. These two tables are created by the file, newClient.pl - see later.
This table has an auto-increment primary key called, discussID. The name of the second column is Person. When the message for a row is from the operator, the column cell has the value, “operator”. When the message is from the client, the column cell, has the value, “client”.
The third column is msg. When a row has “operator”, the value in this cell is the message from the operator; when a row has “client”, the value in this cell is the message from the client. The conversation consists of messages and they are all in this table.
The chartMonitor Table
The chartMonitor table has only one row; the number of rows does not increase as the conversation continues; it remains at one. The first cell has the ID of the row. When the client initiates the conversation by submitting the first form document, the next cell, clientStart acquires the value “started”. When the operator sends his first message, the cell, operatorStart acquires the value “started”. This same value for the two cells are quickly changed as the conversation continues (see later).
When the operator has not yet read the current message of the client, the cell, clientMsgState acquires the value, “new”. After reading, this value is changed. For a similar reason and action, when the client has not yet read the current message from the operator, the cell, operatorMsgState acquires the value of “new”.
As the conversation continues, the auto-increment ID for the discussion table increases. The last client message ID of the discussion table is kept in the cell, lastClientID. The last operator message ID is kept in the cell, lastOperatorID. These values change with time accordingly.
The cell, clientName has the first name of the client; the cell, operatorName has the actual name of the operator; these values do not change with time.
When the client is typing a new message, the cell, clientWriting acquires the value, “Yes”. After the message is read from the discussion table, the cell acquires the value, “No”. For a similar reason and action, on the part of the operator, the cell operatorWriting would acquire “Yes” and “No”, accordingly. The cell, clientEmail holds the email address of the client. The email is not used in this project, but a commercial project would use it.
In the project, the root user (DBA) of the MySQL database server, grants privileges for the client and operator as follows:
CREATE USER 'client' IDENTIFIED BY 'clnt';
CREATE USER 'operator' IDENTIFIED BY 'oprtr';
GRANT SHOW DATABASES ON *.* TO 'client';
GRANT CREATE ON chartdb.* TO 'client';
GRANT INSERT ON chartdb.* TO 'client';
GRANT SELECT ON chartdb.* TO 'client';
GRANT UPDATE ON chartdb.* TO 'client';
GRANT SHOW DATABASES ON *.* TO 'operator';
GRANT INSERT ON chartdb.* TO 'operator';
GRANT SELECT ON chartdb.* TO 'operator';
GRANT UPDATE ON chartdb.* TO 'operator';
GRANT DROP ON chartdb.* TO 'operator';
Read through the statements. Remember, if privileges are not granted, a non-root user would not be able to access any table.
Creating the Database and Granting Privileges
You should be trying the project as we go alone. Type the following code in a text editor and save the file with the name, db.pl in your hard dick root directory (drive C:\)
#!C:/Perl/bin/perl5.18.2.exe
use Mysql;
use strict;
if (!Mysql::connect("root", "xxxxxx", "localhost", 3306))
{
print $Mysql::Error_msg;
}
else
{
my $crdb = "create database chartdb";
if (!Mysql::query($crdb))
{
print $Mysql::Error_msg, "\n";
}
else
{
if (!Mysql::select_db("chartdb"))
{
print $Mysql::Error_msg, "\n";
}
else
{
my $SQL_cr_user1 = "CREATE USER 'client' IDENTIFIED BY 'clnt'";
if (!Mysql::query($SQL_cr_user1))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_cr_user2 = "CREATE USER 'operator' IDENTIFIED BY 'oprtr'";
if (!Mysql::query($SQL_cr_user2))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv1 = "GRANT SHOW DATABASES ON *.* TO 'client'";
if (!Mysql::query($SQL_grnt_priv1))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv2 = "GRANT CREATE ON chartdb.* TO 'client'";
if (!Mysql::query($SQL_grnt_priv2))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv3 = "GRANT INSERT ON chartdb.* TO 'client'";
if (!Mysql::query($SQL_grnt_priv3))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv4 = "GRANT SELECT ON chartdb.* TO 'client'";
if (!Mysql::query($SQL_grnt_priv4))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv5 = "GRANT UPDATE ON chartdb.* TO 'client'";
if (!Mysql::query($SQL_grnt_priv5))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv11 = "GRANT SHOW DATABASES ON *.* TO 'operator'";
if (!Mysql::query($SQL_grnt_priv11))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv22 = "GRANT INSERT ON chartdb.* TO 'operator'";
if (!Mysql::query($SQL_grnt_priv22))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv33 = "GRANT SELECT ON chartdb.* TO 'operator'";
if (!Mysql::query($SQL_grnt_priv33))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv44 = "GRANT UPDATE ON chartdb.* TO 'operator'";
if (!Mysql::query($SQL_grnt_priv44))
{
print $Mysql::Error_msg, "\n";
}
my $SQL_grnt_priv55 = "GRANT DROP ON chartdb.* TO 'operator'";
if (!Mysql::query($SQL_grnt_priv55))
{
print $Mysql::Error_msg, "\n";
}
}
}
Mysql::close();
}
Run the script at the command c:\ prompt.
You should have no error message; if you have, make the correction.
That is it for this part of the series. We stop here and continue in the next part.
Chrys
Related Links
Web Development Basics with Perl and MySQLPerl Validation of HTML Form Data
Page Views with Ajax and Perl and MySQL
Web Live Text Chart Application using Perl and MySQL
Search Within a Site using Perl and MySQL
More Related Links
Perl Mailsend
PurePerl MySQL API
Perl Course - Professional and Advanced
Major in Website Design
Web Development Course
Producing a Pure Perl Library
MySQL Course
BACK NEXT