Chart Operator Perl Interface Code for MySQL

Web Live Text Chart Application using Perl and MySQL - Part 5

Web Development with Perl and MySQL

Foreword: In this part of the series, I discuss the chart operator Perl interface code for MySQL.

By: Chrysanthus Date Published: 9 Sep 2016

Introduction

This is part 5 of my series, Web Live Text Chart Application using Perl and MySQL. In this part of the series, I discuss the chart operator Perl interface code for MySQL. I will not discus the equivalent client Perl interface code in this series. The two interfaces are similar, so it will be boring talking about both. At the end of the series, I give you the complete application code in zip format.

There are 4 Perl interface files for the operator. Remember, there are two tables in the database: chartMonitor and discussion. chartMonitor has the data that monitors the messages in the discussion table. The tables are created by the first document (Form document) of the client’s window.

The File to Read Message from Client
This file (program) is called by the operator web page, every 10 seconds. The name of the file whose content is a program is, operatorRead.pl. Remember that the chartMonitor table has just one row, with a cell for clientStart, a cell for clientMsgState, a cell for lastClientID, a cell for clientName, a cell for clientWriting, and a cell for clientEmail. The clientStart cell holds the value, “started” when the client fills the form with his name and email address and sends to the server. For simplicity, this cell is always read. The clientMsgState cell holds the value “new” if the message from the client is current (not been read) or “old” if the last message sent by the client had been read by this program (file) and sent to the operator. The lastClientID cell holds the last (maximum) ID in the discussion table of the last message sent by the client. The clientName cell holds the actual name of the client; for simplicity, this cell is always read. Whenever the client is typing (writing), the cell, clientWriting holds the value, “Yes”, otherwise it holds the value, “No”. The clientEmail cell holds the email address of the client. All these data are read every 10 seconds by this program (file).

The potion of the file that manipulates the database tables and returns (sends to operator browser) the message and relevant data is:

 #select all the row items of the only row of chartMonitor
 my $chartMonitorSelectStr = "select * from chartMonitor";
 if (!Mysql::query($chartMonitorSelectStr))
 {
 print "NULL";
 }
 else
 {
 my ($clientStart, $clientName, $clientEmail,$clientWriting);
 if ($Mysql::No_of_Rows == 1)
 {
 $clientStart = $Mysql::Result[0]{'clientStart'};
 $clientName = $Mysql::Result[0]{'clientName'};
 $clientEmail = $Mysql::Result[0]{'clientEmail'}; #read but not needed for this pedagogic project
 $clientWriting = $Mysql::Result[0]{'clientWriting'};
 }

 #update cells of the chartMonitor
 my $updateStr = "update chartMonitor SET clientStart=\"already\" WHERE tableID=1";
 if (!Mysql::query($updateStr))
 {
 print $Mysql::Error_msg, " ";
 }

 #read new message from client if available
 my $selectClientMsgStateStr = "select clientMsgState from chartMonitor WHERE tableID=1";
 if (!Mysql::query($selectClientMsgStateStr))
 {
 print $Mysql::Error_msg, " ";
 }
 my $clientMsgState;
 if ($Mysql::No_of_Rows == 1)
 {
 $clientMsgState = $Mysql::Result[0]{'clientMsgState'};
 }

 my $msg ;
 if ($clientMsgState == "new")
 {
 #select last operator discussID
 my $selectIDStr = "select lastClientID from chartMonitor WHERE tableID=1";
 if (!Mysql::query($selectIDStr))
 {
 print $Mysql::Error_msg, " ";
 }
 my $ID;
 if ($Mysql::No_of_Rows == 1)
 {
 $ID = $Mysql::Result[0]{'lastClientID'};
 }

 #now select message from discussion table
 my $selectMsgStr = "select msg from discussion WHERE discussID=$ID";
 if (!Mysql::query($selectMsgStr))
 {
 print $Mysql::Error_msg, " ";
 }
 if ($Mysql::No_of_Rows == 1)
 {
 $msg = $Mysql::Result[0]{'msg'};
 }
 }

 #update chartMonitor, form return string and send to operator
 if (($clientStart == "started")||($clientMsgState == "new")||($clientWriting=="Yes"))
 {
 my $updateStr = "update chartMonitor SET clientMsgState = \"old\" WHERE tableID=1";
 if (!Mysql::query($updateStr))
 {
 print $Mysql::Error_msg, " ";
 }

 #update clientWriting cell after successfull reading
 if ($clientMsgState == "new")
 {
 my $updateWStr = "update chartMonitor SET clientWriting=\"No\" WHERE tableID=1";
 if (!Mysql::query($updateWStr))
 {
 print $Mysql::Error_msg, " ";
 }
 }

 my $returnStr = "clientName=" . $clientName . "&" . "msg=" . "$msg" . "&" . "clientWriting=" . $clientWriting . "&" . "clientStart=" . $clientStart;
 print $returnStr;
 }

 }

The first statement of the above code selects all the data of the chartMonitor table. Remember, the chartMonitor table has just one row. Shortly after in the code, the values for clientStart, clientName, clientEmail and clientWriting are read from the row into corresponding variables. In this project clientEmail is not used, but would be used in a commercial project. The values of clientStart, clientName, and clientWriting will be sent to the operator web page. Next in the code, the value of clientStart is changed to “already”. This same value is sent to the cell every 10 seconds for simplicity. That is the value, “started” is read only once (the first time); after that, the value of “already” would always be read. The value of clientMsgState in the chartMonitor table is read shortly after. If this value is “new” then the next segment uses the value in lastClientID, which has the last (recent) ID of the client message in the discussion table, to read the recent message in the discussion table. Before this program (file) exists, it changes the value to “old”. If the value is “old”, no message from the discussion table is read.

At the bottom of the program, the chartmonitor table is updated on condition that the value of clientStart is “started” or the value of clientMsgState is “new” or the value of clientWriting is “Yes”. If the value of clientWriting is “Yes”, then it is set to “No”; however the value of yes is sent to the operator. The value of “No” would be sent in the next reading.

Any program called by Ajax may or may not return a text string. This one called by Ajax in the operator web page, returns a text string (i.e. sends a string to Ajax in the operator web page). The text string returned is equivalent to:

“clientName=value&msg=value&clientWriting=value&clientStart=value”

For simplicity, the values for clientName and clientStart are always returned. The value for clientName is the actual name of the client. The value for msg is the recent message. If there is no recent message depending on the value of clientMsgState (new) the chartMonitor table is not updated for clientWriting. The value of clientWriting is “Yes” or “No”. The value of clientStart is “started” or “already”. The web page ECMAScipt uses these values accordingly (see previous part of the series). Note the return statement and the condition for which data is returned, in the code.

Operator Sending Message
The name of the Perl file whose content is the program that receives the message from the operator web page (Ajax function) is, operatorMessage.pl. The program receives as data, the actual name of the operator and the message from the operator. The main portion of the program is:

 #determine if conversation had already started
 my $selectDiscussionStr = "select * from discussion";
 if (!Mysql::query($selectDiscussionStr))
 {
 print $Mysql::Error_msg, " ";
 }
 my $operatorStart;
 if ($Mysql::No_of_Rows == 0)
 {
 $operatorStart = "started";
 }
 else
 {
 $operatorStart = "already";
 }

 #insert operator message into discussion table
 my $msgStr = "insert into discussion (Person, msg) values (\"operator\",\"$msg\")";
 if (!Mysql::query($msgStr))
 {
 print $Mysql::Error_msg, " ";
 }

 #select the last ID and use to update the chartMonitor table
 my $selectDiscussionIDStr = "select max(discussID) AS discussID from discussion";
 my $discussID;
 if (!Mysql::query($selectDiscussionIDStr))
 {
 print $Mysql::Error_msg, " ";
 }
 else
 {
 $discussID = $Mysql::Result[0]{'discussID'};
 }

 #update cells for operator in the chartMonitor table
 my $updateOpStr = "update chartMonitor SET operatorStart = \"$operatorStart\", operatorMsgState = \"new\", lastOperatorID = \"$discussID\", operatorName=\"$operator\" WHERE tableID=1";
 if (!Mysql::query($updateOpStr))
 {
 print $Mysql::Error_msg, " ";
 }

The program begins by reading all the data in the chartMonitor table. If the chartMonitor table does not exist (No_of_Rows == 0), then a new declared variable, $operatorStart is given the value “started” otherwise it is given the value “already”. Remember, it is the operator that initiates conversation through this file, operatorMessage.pl.

Next, the program inserts the message and the word, “operator” in the discussion table. The program goes on to determine the ID of the row of the message just inserted. The message from the client and that from the operator are not inserted in the same row. The message from each person has its row and its ID in the same discussion table.

The last code segment in the above code updates the chartMonitor table. It gives the operatorStart cell the appropriate value. It gives the operatorMsgState cell the value, “new” so that the client Perl script that reads messages would use it to know that a current (new) message from the operator is available for reading.

The Operator Typing
When the operator is typing, an Ajax function in his web page calls the file, operatorWriting.pl at the server. The data input to this file is the value, “Yes”. This file will not be called, if the operator is not typing. The main content of the file (program) is:

 #update clientWriting cell for client in the chartMonitor table
 my $updateOpStr = "update chartMonitor SET operatorWriting = \"$writing\" WHERE tableID=1";
 if (!Mysql::query($updateOpStr))
 {
 print $Mysql::Error_msg, " ";
 }

It updates the operatorWriting cell of the chartMonitor table with the word, “Yes”, which is the value of $writing read from the input. When the value of this cell is read by the client interface program, the client interface program changes the value to “No”, to avoid false reading, the next time. However, the client ECMAScript will erase its text, “Agent is typing. . .”, only after it has actually read the completely typed message of the operator, from the server.

File for Ending Conversation
The operator ends the conversation. He clicks a button at the operator web page. This button calls an Ajax function at the web page. The Ajax function calls the file, dropTables.pl at the server. The aim of this file (program) is to drop (erase) the two tables: chartMonitor and discussion. After deleting, it sends the feedback, “OK” to the operator web page. The operator web page uses this feedback to delete all the messages (conversation) that was displayed on its page. The main portion of the program at the server is:

 #drop tables
 my $dropchartMonitorStr = "drop table chartMonitor";
 my $returnVal1 = Mysql::query($dropchartMonitorStr);
 my $dropDiscussionStr = "drop table discussion";
 my $returnVal2 = Mysql::query($dropDiscussionStr);
 if (($returnVal1 == 1)&&($returnVal2 == 1))
 {
 print "OK";
 }

We have come to the end of this part of the series. The client also has a number of Perl interface files. The coding of the files is similar to those of the operator. So it would be boring to explain them in this series. However, I give you the complete code at the end of the series.

Let us take a break here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Chart Operator Perl Interface Code for MySQL

Web Live Text Chart Application using Perl and MySQL - Part 5

Web Development with Perl and MySQL

Introduction

Related Links

Comments