SQL Database Simulator in the Browser

• Download source code and examples - 4.6 MB

Introduction

Two features have to be incorporated when simulating a database within the browser:

1. Simulating database tables with JavaScript
2. Simulating SQL commands using JavaScript to encompass both CRUD operations and metacommands to create the database tables

In this article, additional features will also be incorporated in extended SQL commands so as to automatically return formatted HTML objects such as sortable tables, listviews, dropdowns, trees, menus, galleries, XML data, etc. Commands will also be extended so as include placements within DIVs on the Webpage. At present, only single table SQLs will be available.

A side-benefit of SQL as a programming language in the browser is that it is extremely parsimonious and greatly simplifies the programming of Webpages. Binding becomes intuitive and database REQUESTS/RESPONSES can easily be integrated.

An educational advantage of this software is that it can be used to teach basic SQL language without any database connection.

Background

This article assumes basic knowledge of SQL database language.

No external libraries will be used in the code.

Using the Code

¶ § The following 2 scripts are mandatory and are to be included in any Webpage using SQL commands:

• Script to extend methods of built-in String object:

<SCRIPT TYPE="text/javascript" SRC="rsrcs/js/js_string.js"></SCRIPT>

• SQL commands Parser:

<SCRIPT TYPE="text/javascript" SRC="rsrcs/js/jsSQLdb.js"></SCRIPT>

¶ § In addition, any table data files which are used on the Webpage must be included:

<SCRIPT TYPE="text/javascript" SRC=data/db/dog.js></SCRIPT>

Tables

If you look at the table data file, dog.js, you will see it begins with a SQL definition command to create the table, followed by a list of SQL inserts to populate the table:

• SQL Create Table definition command:

eval(SQLexecute('CREATE TABLE dog (name varchar, family varchar, instinct varchar, height varchar)'));

• SQL Insert commands to populate the table:

SQLexecute("INSERT INTO dog VALUES('Huskie',       'wolf',    'snowdog',   'medium')");
SQLexecute("INSERT INTO dog VALUES('Borjoi',       'afghan',  'racing',    'tall')");
SQLexecute("INSERT INTO dog VALUES('Boxer',        'african', 'attacking', 'medium')");
SQLexecute("INSERT INTO dog VALUES('Pitball',      'pits',    'attacking', 'small')");
SQLexecute("INSERT INTO dog VALUES('Afghan Hound', 'afghan',  'racing',    'tall')");
SQLexecute("INSERT INTO dog VALUES('Pincher',      'pincher', 'alarming',  'tiny')");

SQLs

In order to display data on a page, a simple SQL command is used with an additional DIV clause where on the page to display the result.

(Note: The DIV clause is optional. Without it, the data is returned directly to the user code in the format requested and can be manipulated in any way required.)

var ls_sql;
ls_sql = " SELECT name, instinct, family, height FROM dog DIV box1"
 
SQLdo('', ls_sql);

If no explicit FORMAT clause is used in the SQL, the default display format is a table:

Additional SQL conditions can be added and an explicit FORMAT can be used such as a sortable table with a TableHeader:

var ls_sql;
ls_sql = " SELECT * FROM dog WHERE instinct<>'hunting' ORDER BY instinct 
           FORMAT sorttable
      TABLEHEADER My Dogs Table
           DIV    box1"

SQLdo('', ls_sql);

A table can be UPDATED in situ by using the format databox.
Clicking on a row in the table displays the databox editor and any changes made in the editor will be reflected in the table.

var ls_sql;
ls_sql = " SELECT name, instinct, family, height 
             FROM dog 
            WHERE instinct<>'hunting' 
         ORDER BY instinct 
           FORMAT databox
      TABLEHEADER My Own Databox
           DIV    box1"

SQLdo('', ls_sql);

Texts and Styles of column headers and cells can also be customized.
Following the column name:

• Column header texts and styles are customized between | lines
• Cells styles are customized between { } parentheses

var ls_sql
ls_sql = "SELECT name |<SPAN STYLE='color: #FF0000; background: #FFFF00;'>breed</SPAN>|,
             instinct {background: #000000; color: #FFFFFF; font-weight: 800;}, 
               family |<SPAN STYLE='background: steelblue; color: #FFFFFF'>pedigree</SPAN>|, 
              height  |<SPAN STYLE='border: 1px solid #0000FF;'>weight</SPAN>|   
           FROM dog 
          WHERE instinct<>'hunting' 
       ORDER BY instinct 
         FORMAT table
         DIV    box1"
SQLdo('', ls_sql)"

Trees can also be created using the Format clause.
The table for a tree has an mandatory fixed hierarchal structure.
The hierarchal tree table is very flexible and contains options often missing in trees such as:
individualized icons, comments under titles, links, and more.

var ls_sql;
ls_sql = "SELECT * FROM tree1 FORMAT tree DIV box1"

SQLdo('', ls_sql);

Galleries of images can also be created using the Format clause.
The table for a gallery has a mandatory fixed structure.

var ls_sql;
ls_sql  = " SELECT * FROM galleries WHERE myparent='gallery4' FORMAT gallery DIV box1";

SQLdo('', ls_sql);

Other formats available: record, dropdown, listbox, menu, toolbar, icons, csv, xml, etc.
Examples of these can be found in the attached zip file.
New formats can be added using the js_custom.js file

SQL Bonuses

Besides the extension clauses, a few special SQL commands have been created for simplicity such as:

SELECT *** FROM table:   Returns field names from a table

How It Works

Tables

The SQL parser converts a Create Table command into a class which defines the fields in the table.

eval(SQLexecute('CREATE TABLE dog (name varchar, family varchar, instinct varchar, height varchar)'));

is parsed into the following class (Note: Recall that classes in JavaScript are written as functions):

function class_dog (name, family, instinct, height)
{
 this.name     = name;
 this.family   = family;
 this.instinct = instinct;
 this.height   = height;
} 

An array is declared where each row in the array will contain an instance of this class.

val dog = new Array();

Given these SQL Insert commands:

SQLexecute("INSERT INTO dog VALUES('Huskie', 'wolf',   'snowdog', 'medium')");
SQLexecute("INSERT INTO dog VALUES('Borjoi', 'afghan', 'racing',  'tall')");
:
:

The SQL Insert commands can now be parsed into the array:

dog[dog.length] = new class_dog('Huskie', 'wolf',   'snowdog', 'medium');
dog[dog.length] = new class_dog('Borjoi', 'afghan', 'racing',  'tall');
:
:

Of course, individual tables could have been created as hardcode without the SQL commands. However, the parser is dynamic and can create any table on the fly. New tables can be added-on-demand at runtime either in the browser or even downloaded from the server on demand.

Why Class and not Object Literals

Classes are essential for the definition of SQL tables.

Object literals could not be used to define tables since they are already instances. JSON doesn't define types.

In addition, SQL features such as ORDER BY or WHEN would be well nigh impossible to handle without arrays containing tables.

SQL

The parser, jsSQLdb, handles all the various types of SQL commands.

The class class_SQL holds the SQL parameters when parsing.
Only a single instance of class_SQL is required. (In C#, this would have been defined as a static class.)

class_SQL

Parameter: sql

Fields

sql                     // The sql to be parsed
verb                    // SELECT, UPDATE, INSERT, CREATE TABLE, etc
fields                  // Fields selected
headers                 // Field headers
from                    // TABLE
where                   // WHERE clause
orderby                 // ORDER BY clause
groupby                 // GROUP BY clause
having                  // HAVING clause
format                  //  Format to be displayed
display                 // User for dropdowns, listboxes, galleries and icons
tableheader             // A box title that can be used for display
div                     // Name of the <DIV> on the WebPage where to display data

Methods

• SQLcreate
• SQLclear

Besides parser functions, jsSQLdb also contains many functions both to handle SQL manipulation as well as other general purpose functions.

js_string.js

This is an ancillary library of useful String functions missing in JavaScript which extends the built-in String object.
It was not essential for SQL processing but it made writing jsSQLdb.js much easier. It contains such functions as trim, ltrim, rtrim, lpad, rpad, format and more.

Points of Interest

Recently, I have been involved in writing the specifications for a commercial Web application.
The customer wanted to view a prototype of the application with samples of the database functionality where no database was as yet available.

Since I had written this SQL infrastructure some years ago, I adapted it to enable the customer to get a look
and feel of what segments of the application functionality without a database. Even though all changes affected on the Webpage using jsSQLdb are lost once the page is closed, this gave the customer a satisfactory picture of the functionality of the application.

History

• 20^th January, 2016: Initial version