HTML5 Web Sql Database and Google Gears Support with Examples

We all value our time, and I want to help you (well, maybe not for you, but for sure) to save it.
It will be about client databases – Web Sql Database and Google Gears.

On duty, I am involved in a web application using Local Database and Web Workers.

So, what we need:

1. SQL queries that select all kinds of data from the local database
2. Downloading data through Ajax in the background and writing them to the local database
3. Support for Firefox, Google Chrome, Safari, IE
4. Support for Win, Linux, MacOS, iPad

At first glance, it’s okay, but from the second, problems begin.

Google gears

Support for Google Gears (SQL and Web Workers) is shown in the table below.
Main criteria:
± Local Database support,
± Web Workers
support ± Local Database support from Web Worker
X – not tested
(I’ll say right away – Google Gears in all supported browsers supports either all or nothing, but for clarity, we’ll leave three values)

	Chrome	Firefox	Safari	IE
Win	+++	+++	—	+++
MacOS	—	+++	—	X
iPad	X	X	—	X
Linux	—	+++	—	X

Also for Safari (MacOS) there is a separate custom plugin for Google Gears, which works only when Safari starts in 32-bit mode (Snow Leopard).

Everything is transparent and simple here, information and examples are a huge amount on the Internet.
We connect gears_init.js.

Examples of using Google Gears Sql Examples of using Google Gears Workers worker.js:

var connect = google.gears.factory.create('beta.database');
connect.open(dbName);
var result = connect.execute(query, fields);
while (result.isValidRow()) {
var id = result.fieldByName('id');
result.next();
}
connect.close();

var workerPool = google.gears.factory.create('beta.workerpool');
var childWorkerId = workerPool.createWorkerFromUrl('worker.js');
workerPool.onmessage = function(a, b, message) {
switch (message.body) {
case 'EVENT_1':
break;
case 'EVENT_2':
break;
default:
break;
}
};
workerPool.sendMessage({event: 'START'}, childWorkerId);

var worker = google.gears.workerPool
worker.onmessage = function(a, b, message) {
//event message.body.event
worker.sendMessage('EVENT_1', message.sender);
}

---

HTML5 Web Sql Database

HTML5 Web Sql Database support is shown in the table below.
Main criteria:
± Local Database support,
± Web Workers
support ± Local Database support from Web Worker
X – not tested

	Chrome	Firefox	Safari	IE
Win	+++	– + –	++ –	—
MacOS	+++	– + –	++ –	X
iPad	X	X	+ –	X
Linux	+++	– + –	X	X

Another problem suddenly appears – Web Sql Database runs asynchronously. Those. It’s just not possible to complete a query chain. Also, you can be 100% sure that when the next statement is executed after the sql request, the sql request will not be executed yet.
There are several solutions:

1) Perform nested actions. Those. we execute sql, after execution, a callback function is called in which we do the actions we need. Not very comfortable.

2) Build an event system. When executing a request, throw a specific event, successfully catch it and continue. Also not very convenient.

Examples of using Web Worker worker.js: Examples of using Web Sql Database outside of Web Worker Examples of using Web Sql Database in Web Worker

var worker = new Worker("worker.js");

worker.onmessage = function (evt) {
switch (evt.data) {
case 'EVENT_1':
break;
default:
break;
}
};
worker.onerror = function (evt) {
alert('error: ' + evt.data);
};

worker.postMessage('START');

onmessage = function (event) {
switch (event.data) {
case 'START':
break;
default:
break;
}
postMessage('EVENT_1');
};


var connect = window.openDatabase(dbName, "1.0", "", 1024*1024*5);
connect.transaction(function(db) {
//Асинхронно
db.executeSql("SELECT id FROM test", fields,
function(t, results) {
for (i = 0; i < results.rows.length; i++){
//results.rows.item(i)['id'];
}
},
function(t, error) {
alert(error.message);
}
);
});

var db = openDatabaseSync('db', "1.0", "", 1024*1024*5);
db.transaction(function(db) {
//Синхронно
var result = db.executeSql("SELECT id FROM test");
for (var i = 0; i < result.rows.length; i++) {
//result.rows.item(i)['id'];
}
});

There is also such a problem in Chrome – a hard limit of 5 mb for the database, which at the moment cannot be expanded with the usual js methods.