Broad Network


Database of Web Live Chart Application using ECMAScript and MySQL

Web Live Text Chart Application using ECMAScript and MySQL - Part 3

Web Development with ECMAScript and MySQL in Node.js

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: 7 Oct 2016

Introduction

This is part 3 of my series, Web Live Text Chart Application with ECMAScript and MySQL. In this part of the series, I talk about the database and the tables for the web live chart application. You should have read the previous parts of the series before reaching here; this is a continuation.

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 ECMAScript 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.

Code for the Tables
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.js - see later.

The discussion Table
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.

Database Privileges for Client and Operator
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.js in your hard disk at c:/server/include. Create the directories if necessary. The server file, server.js is in c:/server, while db.js is in c:/server/include.

const mysql = require('../Mysql.js');

    con = new mysql.Connection("root", "secret", "localhost", 3306, function(err)
        {
            if (err)
                console.log(err);


            var crtDBStr = `create database chartdb`;
            con.query(crtDBStr, function(err)
                {
                    if (err)
                        console.log(err);
                    else
                        console.log('database created');
                });

            var db = `chartdb`;
            con.selectDB(db, function(err)
                {
                    if (err)
                        console.log(err);
                    else
                        console.log('database selected');
                });


            var SQL_cr_user1 = `CREATE USER 'client' IDENTIFIED BY 'clnt'`;
            con.query(SQL_cr_user1, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_cr_user2 = `CREATE USER 'operator' IDENTIFIED BY 'oprtr'`;
            con.query(SQL_cr_user2, function(err)
                {
                    if (err)
                        console.log(err);
                });
    
            var SQL_grnt_priv1 = `GRANT SHOW DATABASES ON *.* TO 'client'`;
            con.query(SQL_grnt_priv1, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv2 = `GRANT CREATE ON chartdb.* TO 'client'`;
            con.query(SQL_grnt_priv2, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv3 = `GRANT INSERT ON chartdb.* TO 'client'`;
            con.query(SQL_grnt_priv3, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv4 = `GRANT SELECT ON chartdb.* TO 'client'`;
            con.query(SQL_grnt_priv4, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv5 = `GRANT UPDATE ON chartdb.* TO 'client'`;
            con.query(SQL_grnt_priv5, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv11 = `GRANT SHOW DATABASES ON *.* TO 'operator'`;
            con.query(SQL_grnt_priv11, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv22 = `GRANT INSERT ON chartdb.* TO 'operator'`;
            con.query(SQL_grnt_priv22, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv33 = `GRANT SELECT ON chartdb.* TO 'operator'`;
            con.query(SQL_grnt_priv33, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv44 = `GRANT UPDATE ON chartdb.* TO 'operator'`;
            con.query(SQL_grnt_priv44, function(err)
                {
                    if (err)
                        console.log(err);
                });

            var SQL_grnt_priv55 = `GRANT DROP ON chartdb.* TO 'operator'`;
            con.query(SQL_grnt_priv55, function(err)
                {
                    if (err)
                        console.log(err);

                });
        
            con.close();

        });

Run the script at the command c:/ prompt.
You should have no error message; if you have, make the correction.

The Node Server
A basic Node.js server you can use to run this script, is given at the end of the series.

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 ECMAScript (JavaScript) and MySQL
ECMAScript (JavaScript) Validation of HTML Form Data
Web Live Text Chart Application using ECMAScript (JavaScript) and MySQL
Page Views with Ajax and ECMAScript (JavaScript) and MySQL
Search Within a Site using ECMAScript and MySQL
More Related Links
Node Mailsend with JavaScript
EMySQL API with JavaScript
Node.js Web Development Course with JavaScript
Major in Website Design
Low Level Programming - Writing ECMAScript (JavaScript) Module
ECMAScript (JavaScript) Course

BACK NEXT

Comments

Become the Writer's Follower
Send the Writer a Message