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.
Examples
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.
Performance
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.
Rational
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.
General Guide for Using
There are 3 general functions built into the html5sql module.
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.
databaseName - This is a name for your database.
Usually it would be something like = com.yourcompany.yourapp.db
but it really can be anything you like.
displayName - This is the display name for your
database. A typical example would be "This App Database"
estimatedSize - This is the estimated size of your
database. You want to allow for plenty of space for your
needs. A common technique is to use some simple multiplication.
So for 5 Megabits you could just put 510241024.
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);
finalSuccessCallback - A final success callback
after your all of your statements have been processed.
errorCallback - A general error callback function
to be called if there are any errors in any part of
this process. Encountering an error rolls back the
entire transaction, so the version of the database is
not changed.
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:
SQL "String" - A string containing a single SQL
statement. This string can optionally include ? in
place of data.
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.
success (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:
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:
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.
oldVersion - The version of the database you are
looking to change. If you haven't changed the version
of your database yet it will be the value "".
newVersion - The version of the database you would
like to change to.
SQL - All the SQL statements necessary to update your database
schema and migrate your data. You have many options
on how you could provide the SQL statements. See the
above html5sql.process() function description for more
detail.
finalSuccessCallback - A final success callback
after your database's version has been updated.
errorCallback - A general error callback function
to be called if there are any errors in any part of
this process. Encountering an error rolls back the
entire transaction, so the version of the database is
not changed.
Goals for the future
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.
请发表评论