An interesting innovation introduced with HTML5 is the ability to save data permanently (till the next browser cache clear) in a local database using SQLite commands. The direct use of this API is, however, "uncomfortable" and repetitive, overburdening code with logics that should instead be collected in lower-level procedures. So I developed a Javascript class that simplifies versioning, writing and reading operations, to make the Web SQL Database API easier to use.
/**
* HTML5 Web SQL Database framework
* Author: Stefano Storti
* Licence: GNU General Public License version 3.0 or later <http://www.gnu.org/licenses/gpl-3.0.html>
*/
var db = new function Database() {
this._db = null;
/**
* Open database
*/
this.open = function(name, descr, ver, size) {
if (ver == undefined)
ver = "";
this._db = window.openDatabase(name, ver, descr, size);
}
/**
* Database version
*/
this.version = function() {
return this._db.version;
}
/**
* Execute an SQL command or an array of commands
*/
this.execute = function(sql, onSuccess, onError) {
this._db.transaction(function(tx) {
if (sql.constructor == Array) {
for (i = 0; i < sql.length; i++)
tx.executeSql(sql[i]);
}
else {
tx.executeSql(sql);
}
}, onError, onSuccess);
}
/**
* Execute an SQL query
*/
this.query = function(sql, onSuccess, onError) {
this._db.transaction(function(tx) {
tx.executeSql(sql, [], onSuccess, onError);
}, onError);
}
/**
* Upgrade to new version
* sqlVers = [{ver: "1", sql: "sql command 1"}, {ver: "2.0", sql: "sql command 2.0"}, {ver: "3.1", sql: "sql command 3.1"}, ...]
*/
this.upgrade = function(sqlVers, onSuccess, onError) {
var currentVer = 0;
if (this._db.version)
currentVer = parseFloat(this._db.version);
var sqlExec = new Array();
for (i = 0; i < sqlVers.length; i++) {
var ver = parseFloat(sqlVers[i].ver);
var sql = sqlVers[i].sql;
if (ver > currentVer) {
sqlExec.push(sql);
}
}
var currentVer = this._db.version;
var lastVer = (sqlVers[sqlVers.length - 1]).ver;
if (sqlExec.length > 0) {
this.changeVersion(currentVer, lastVer, sqlExec, onSuccess, onError);
}
}
/**
* Change database version
* sql = single SQL command or array of SQL commands
*/
this.changeVersion = function(oldVer, newVer, sql, onSuccess, onError) {
this._db.changeVersion(oldVer, newVer, function (tx) {
if (sql.constructor == Array) {
for (i = 0; i < sql.length; i++)
tx.executeSql(sql[i]);
}
else {
tx.executeSql(sql);
}
}, onError, onSuccess);
}
/**
* Check whether can use HTML5 SQLite web database
*/
this.webDbImplemented = function() {
return (window.openDatabase != undefined);
}
}Usage
Create/open database:
// db.open(name, descr, ver, size)
db.open("MyWebDatabase", "My Web Database", "", 1024 * 1024);
Check database version:
var ver = db.version();First creation:
// Full database creation
var sql = [
"CREATE TABLE BRANDS (id primary key, name)"
, "CREATE TABLE CARS (id primary key, name, brand_id)"
];
// db.changeVersion(oldVer, newVer, sql, onSuccess, onError)
db.changeVersion("", "1", sql,
function() { alert("Database created!"); }, function(err) { alert("Error in database creation: "+err.message); } );Upgrade database:
// Upgrade database (database diff): skip SQL command if current database version >= .ver property
var sqlVers = [
{ver: "2", sql: "ALTER TABLE BRANDS ADD COLUMN country"}
, {ver: "3", sql: "ALTER TABLE CARS ADD COLUMN production_started"}
, {ver: "3", sql: "ALTER TABLE CARS ADD COLUMN production_ended"}
// future upgrades here...
];
// db.upgrade(sqlVers, onSuccess, onError)
db.upgrade(sqlVers, function() { alert("Database upgraded!"); }, function(err) { alert("Error in database upgrade: "+err.message); } );Execute an SQL command or an array of commands:
var sqlInsert = [
"INSERT INTO BRANDS(id, name, country) VALUES (1, 'Fiat', 'Italy')"
, "INSERT INTO BRANDS(id, name, country) VALUES (2, 'Alfa Romeo', 'Italy')"
, "INSERT INTO CARS(id, name, brand_id, production_started) VALUES (1, '500', 1, 2007)"
, "INSERT INTO CARS(id, name, brand_id, production_started) VALUES (2, 'MiTo', 2, 2008)"
, "INSERT INTO CARS(id, name, brand_id, production_started) VALUES (2, 'Giulietta', 2, 2010)"
];
// db.execute(sql, onSuccess, onError)
db.execute(sql, function() { alert("Database has been populated!"); }, function(err) { alert("Error in executing SQLite command: "+err.message); });Execute an SQL query:
// db.query(sql, onSuccess, onError)
db.query('SELECT CARS.name AS car_name, BRANDS.name AS brand_name
FROM CARS INNER JOIN BRANDS ON CARS.brand_id = BRANDS.id
ORDER BY CARS.name',
function(tx, results) {
for (i = 0; i < results.rows.length; i++)
alert('Car:'+results.rows.item(i).car_name+', brand: '+results.rows.item(i).brand_name);
}, function(tx, err) {
alert("Error in executing SQLite query: "+err.message);
}
);Check whether Web SQL Database API is available:
var canUseWebDb = db.webDbImplemented();That's all! I hope this library will be useful to you!
Italiano
English