Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / web / HTML

SQL Database Simulator in the Browser

4.50/5 (9 votes)
19 Jan 2016CPOL5 min read 20.5K   498  
This article presents a client-side, JavaScript Relational Database Simulator in the browser, using SQL commands to Create, Read, Insert, Update and Delete in-browser table data, as well as formatting the output as HTML objects in a Webpage.

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:
JavaScript
<SCRIPT TYPE="text/javascript" SRC="rsrcs/js/js_string.js"></SCRIPT>
  • SQL commands Parser:
JavaScript
<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:

JavaScript
<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:
JavaScript
eval(SQLexecute('CREATE TABLE dog (name varchar, family varchar, instinct varchar, height varchar)'));
  • SQL Insert commands to populate the table:
JavaScript
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.)

JavaScript
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:

Simple Table

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

JavaScript
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);

Sort Table

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.

JavaScript
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);

Updater

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
JavaScript
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)"

Fancy Table

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.

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

SQLdo('', ls_sql);

Tree

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

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

SQLdo('', ls_sql);

Gallery

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:

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

JavaScript
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):

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

JavaScript
val dog = new Array();

Given these SQL Insert commands:

JavaScript
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:

JavaScript
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

  • 20th January, 2016: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)