Introduction
Two features have to be incorporated when simulating a database within the browser:
- Simulating database tables with JavaScript
- 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 DIV
s 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>
<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
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