v0.9.7
Makes using SQL on the client side is easy!
html5sql is a light JavaScript module (3k compressed) that makes working with the HTML5 Web Database much easier. html5sql is powerful enough to process thousands of sql statements in sequence but also simple enough to process single statements with ease.
If you have tried using an HTML5 web database you know how complex even getting a single statement to execute. However, with html5sql it really can be as simple as opening the database and the executing the following JavaScript:
html5sql.process("INSERT INTO table (row) VALUES (value);");
Now if you want to know if the statement was successfully processed you can specify some additional callbacks but at its core it can be this simple.
In the real world database needs are rarely this simple and using the HTML5 Web Database becomes especially troublesome when you are setting up your tables as there are several statements which need to be executed in a particular order.
This is where html5sql becomes especially useful. With html5sql you can just create a separate text file with just your SQL statements. An easy way to do this may be to create your database elsewhere and then do a SQL Dump of the sql statements necessary to recreate it. This file would probably look something like this:
CREATE TABLE example (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO example (data) VALUES ('First');
INSERT INTO example (data) VALUES ('Second');
INSERT INTO example (data) VALUES ('Third');
CREATE TABLE example2 (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO example2 (data) VALUES ('First');
INSERT INTO example2 (data) VALUES ('Second');
INSERT INTO example2 (data) VALUES ('Third');
With html5sql, to sequentially process each of these SQL statements in order and create your table(s) all you would need to do is open your database and then add a snippet of code like this:
$.get('Setup-Tables.SQL',function(sqlStatements){
html5sql.process(
sqlStatements, //text of the SQL file you retrieved
function(){
// Success Function
// executed after all statements are processed
},
function(error){
// Handle any errors here
}
);
});
With the the jQuery get function your list of SQL statements is retrieved from your separate file, split into individual statements and then processed sequentially in the order they appear and wala, your database is setup and populated.
As you can see html5sql makes this process a lot easier but there are several other features built into html5sql which will make it a useful tool for any database interaction.
While all this sounds great, but how fast is html5sql? Really fast.
For example, html5sql was able to create a table and sequentially insert 10,000 records into that table varying amounts of time but averaging somewhere between 2 and 6 seconds using the Google Chrome browser on my desktop. Performance varied based on available computing capacity but by and large statements are executed at a breakneck pace.
The secret behind this speed is that html5sql executes all of the statements within the same transaction. This allows the statements to be executed quickly but will also cause all the statements to be rolled back if there is an error with one of them.
At its very core, SQL is designed to be a sequentially processed language. Certain statements must be processed before other statements. For example, a table must be created before data is inserted into it. Conversely, JavaScript is a very asynchronous, event driven language. This asynchronous nature is very present in the HTML5 client side database spec and introduces a high degree of complexity for the programmer. It is for this reason that this module was written.
This library was written with the understanding that the W3C has ceased to maintain the spec for the Web SQL Database. Even though they have withdrawn from the spec, because webkit has incorporated it the number if internet users with a compatible browser is still significant, especially on mobile devices.
While this module decreases the complexity of using an HTML5 SQL database, it does not attempt to simplify the SQL itself. This is intentional. SQL is a powerful language and attempts to simplify it seem to only decrease it power and utility. In my experience, a better option is to just learn SQL better so it becomes more natural. An excellent resource for learning SQL is the SQLite website.
There are 3 general functions built into the html5sql module.
html5sql.openDatabase(databaseName, displayName, estimatedSize)
The html5sql.openDatabase()
is a light wrapper for the native
openDatabase
function. It opens a connection to your
database and saves a reference to that connection for you.
It needs to be called before you can process any SQL statements.
This function has 3 arguments.
If you are familiar with the native openDatabase function you may notice that version is missing. Database versions are powerful things when you need to change the structure of your database tables, but using this functionality is implemented within the changeVersion function of html5sql. For right now we just open a generic connection to the database.
So an example could be something like this:
html5sql.openDatabase(
"com.mycompany.appdb",
"The App Database"
3*1024*1024);
html5sql.process(SQL, finalSuccessCallback, errorCallback)
This function has 3 arguments:
The html5sql.process()
function is the workhorse of the
functions. Once you have opened your database you can
pass this function SQL and it will make sure that SQL is
executed in a sequential manner.
The first argument which is passed to html5sql.process()
is the SQL. It can accept SQL statements in many forms:
String - You can pass the process function a single SQL statement in a string like this:
"SELECT * FROM table;"
or a bunch of SQL statements in a single string, as long as each of them ends in a semicolon like this:
"CREATE TABLE example (id INTEGER PRIMARY KEY, data TEXT);" +
"INSERT INTO example (data) VALUES ('One');" +
"INSERT INTO example (data) VALUES ('Two');" +
"INSERT INTO example (data) VALUES ('Three');" +
"INSERT INTO example (data) VALUES ('Four');" +
"INSERT INTO example (data) VALUES ('Five');"
Array of SQL Statement Strings - You can pass the process function an array of SQL statement strings like this:
[
"CREATE TABLE example (id INTEGER PRIMARY KEY, data TEXT);",
"INSERT INTO example (data) VALUES ('One');",
"INSERT INTO example (data) VALUES ('Two');",
"INSERT INTO example (data) VALUES ('Three');",
"INSERT INTO example (data) VALUES ('Four');",
"INSERT INTO example (data) VALUES ('Five');"
]
A Single SQL Statement Object - The most functional
method of providing SQL is by using the SQL Statement
Object. The structure of the SQL Statement object is
essentially the same as the arguments you pass to the
native executeSQL
function and has three parts:
"String"
- A string containing a single SQL
statement. This string can optionally include ?
in
place of data.[Array]
- An array of data which will be
sequentially inserted into the SQL statement to replace
the ?
characters. The number of ?
characters and
elements in this data array must match.(function)
- A function which will be
called upon successful execution of the SQL statement.
It has access to the results of the SQL statement.
Additionally, if this function returns an array, it will
be used as the data parameter for the next SQL statement
to be processed. This allows you to use the results
of one SQL statement as data for the following SQL
statements as is commonly needed for foreign keys.Probably the easiest way to define and use this object is by defining an object literal. So a general template for this SQL Statement Object would be something like this:
{ "SQL": "", "data": [], "success": function(transaction, results){
}
}
An Array of SQL Statement Objects - You can also put many SQL Statement Objects in an array to be processed sequentially. Using the array and object literal syntax this could look something like this:
[
{
"SQL": "INSERT INTO contacts (name, phone) VALUES (?, ?),
"data": ["Joe Bob", "555-555-5555"],
"success": function(transaction, results){
//Just Added Bob to contacts table
},
},
{
"SQL": "INSERT INTO contacts (name, phone) VALUES (?, ?),
"data": ["Mary Bob", "555-555-5555"],
"success": function(){
//Just Added Mary to contacts table
},
}
]
Putting this all together. An example usage of html5sql.process()
could be:
html5sql.process(
[
"DROP TABLE table1",
"DROP TABLE table2",
"DROP TABLE table3",
"DROP TABLE table4"
],
function(){
console.log("Success Dropping Tables");
},
function(error, statement){
console.error("Error: " + error.message + " when processing " + statement);
}
);
html5sql.changeVersion("oldVersion","newVersion",SQL,successCallback,errorCallback)
The html5sql.changeVersion()
function is what you should
use to set up your database and handle version and
migrations. This function works by testing if the
database's version matches the oldVersion parameter you
provide. If it does this function will process the SQL
and change the database's version to the newVersion
value you specify.
""
.html5sql.process()
function description for more
detail.I would like to continue to expand this library and incorporate more ideas to help programmers use HTML5 web databases. Eventually I would love to see this library used by everyone who wishes to utilize the HTML5 Web Database.