Mojo Storage Database
From WebOS101
|
This page was created to help others by giving a basic example of creating a new database for your application and storing some data in it.
NOTE: For an easy to use Database class, see below.
SQL Overview
It needs to be mentioned that SQLite (what HTML5 uses), is not as controlling as other databases. For one it's not going to give you an error when you insert text into an integer, or anything like that so regex all input for users or limit it in some way.
| Data Type | Example |
| INTEGER | '0' '123' '3939' |
| REAL | '1.1' '10.0' |
| TEXT | 'foo' 'bar' |
| BLOB | [binary data / images] |
| NULL | absolutely nothing |
Creating a Database and a Table
var name = "MyDB"; // required
var version = "0.1"; // required
var displayName = "My Mojo-Driven database"; // optional
var size = 200000; // optional
var db = openDatabase(name, version, displayName, size);
if (!db) {
Mojo.Log.error("Could not open database");
} else {
var sql = "CREATE TABLE IF NOT EXISTS 'my_table' (id INTEGER PRIMARY KEY, num REAL, data TEXT)"; // check sqlite data types for other values
db.transaction( function (transaction) {
transaction.executeSql(sql, // SQL to execute
[], // array of substitution values (if you were inserting, for example)
function(transaction, results) { // success handler
Mojo.Log.info("Successfully created table");
},
function(transaction, error) { // error handler
Mojo.Log.error("Could not create table: " + error.message);
}
);
}.bind(this));
}
In order to use non-anonymous event handlers, the function supplied to db.transaction has to be bound to the current context, as in the folling, modified example:
SceneAssistant.prototype.createMyTable = function(){
var name = "MyDB"; // required
var version = "0.1"; // required
var displayName = "My Mojo-Driven database"; // optional
var size = 200000; // optional
var db = openDatabase(name, version, displayName, size);
if (!db) {
Mojo.Log.error("Could not open database");
} else {
var sql = "CREATE TABLE IF NOT EXISTS 'my_table' (id INTEGER PRIMARY KEY, num REAL, data TEXT)"; // check sqlite data types for other values
db.transaction(
function (transaction) {
transaction.executeSql(sql, [],
this.dbSuccessHandler.bind(this),
this.dbErrorHandler.bind(this));
}
).bind(this)); //this is important!
}
SceneAssistant.prototype.dbSuccessHandler = function(transaction, results){}
SceneAssistant.prototype.dbErrorHandler = function(transaction, errors){}
openDatabase
If you try to look for MyDB on the filesystem, you won't find it. The openDatabase method creates an entry in the Databases table in /var/usr/home/root/html5-databases/Databases.db which points to the actual location of your database. Note that the details in the Databases table are what you specified in openDatabase except version. Version is contained in the __WebKitDatabaseInfoTable__ table in the actual database.
In the examples contained in this page, the database that is created has a maximum storage capacity of 1 MB. If you need a larger database, then append ext: to the beginning of the database name. For example:
var db = openDatabase("ext:MyDB", "0.1");
This will create the database in /media/internal/.app-storage.
Inserting a Row
var myNum = 512.785;
var test = "I'm test data!";
var db = openDatabase("MyDB", "0.1"); // this is all that is required to open an existing DB
var sql = "INSERT INTO 'my_table' (num, data) VALUES (?, ?)";
db.transaction( function (transaction) {
transaction.executeSql(sql, [myNum, test],
function(transaction, results) { // success handler
Mojo.Log.info("Successfully inserted record");
},
function(transaction, error) { // error handler
Mojo.Log.error("Could not insert record: " + error.message);
}
);
});
Inserting Multiple Rows
With asynchronous database methods, you can really screw things up if you try to do too many transacations in rapid succession. Luckily, you can run as many executeSql methods inside a single transaction as you need.
var dataArray = [];
for(var i = 0; i < 100; i++) {
dataArray[i] = i;
}
var db = openDatabase("MyDB", "0.1");
db.transaction( function (transaction) {
for(var i=0; i < dataArray.length; i++) {
var sql = "INSERT INTO 'my_table' (num, data) VALUES (?, ?)";
transaction.executeSql(sql, [dataArray[i], dataArray[i]],
function(transaction, results) { // success handler
Mojo.Log.info("Successfully inserted record");
},
function(transaction, error) { // error handler
Mojo.Log.error("Could not insert record: " + error.message);
}
);
}
});
Retrieving Data
When a query returns results to the success handler, the rows are contained in .rows.
var db = openDatabase("MyDB", "0.1");
var sql = "SELECT * FROM 'my_table'";
db.transaction(function(transaction) {
transaction.executeSql(sql, [],
function(transaction, results) {
// results.rows holds the rows returned by the query
var my_num = results.rows.item(0).num; // returns value of column num from first row
},
function(transaction, error) {
Mojo.Log.error("Could not read: " + error.message);
});
});
Easy Database Class
This database class makes using databases 100x times easier. Add this as a javascript file to your sources.json and you should be good to go!
See the comment block for how to use this class.
/*
* Database Class
*
* Class written by Brad Ball
* http://www.bradball.net
* development[at]bradball.net
*
* This class is freely distributable as
* long as this comment block is included
* in full.
*
* CLASS USAGE:
* ============
*
* -- INSTANTIATE A DATABASE OBJECT
* var this.myDb = new Database(string name, string version, [int size]);
*
* -- RUN A SINGLE QUERY
* this.myDb.runQuery(sql, [this.mySuccessCallback.bind(this)], [this.myFailCallback.bind(this)]);
*
* -- RUN A QUERY BATCH
* var sql = [];
* sql.push("first sql statement");
* sql.push("2nd sql statement");
* sql.push("3rd sql statement");
*
* this.myDb.runBatch(sql, [this.mySuccessCallback.bind(this)], [this.myFailCallback.bind(this)]);
*
*
* -- SETUP CALLBACKS
* prototype.mySuccessCallback = function(results) {
* // ... results parameter contains the query results object
* }
*
* prototype.myFailCallback = function(error) {
* // ... error parameter contains "code" and "message" properties
* // error.code
* // error.message
* }
*
*/
function Database(dbName, dbVersion, showErrors, dbSize) {
this.name = dbName;
this.version = dbVersion;
if (dbSize == null)
dbSize = 65536;
if (showErrors == null)
showErrors = false;
this.showErrorDialog = showErrors;
this.size = dbSize;
this.db = openDatabase(this.name, this.version, this.size);
}
Database.prototype.executeHandler = function(callback, transaction, results) {
//PUT CODE HERE TO BE EXECUTED EACH TIME A QUERY IS SUCCESSFUL.
//Don't modify this, or your callback will not be called.
if (callback)
callback(results);
}
Database.prototype.errorHandler = function(callback, transaction, error) {
this.logError(error, "A database error occured. " + error.message);
//PUT CODE HERE TO BE EXECUTED EACH TIME A QUERY FAILS.
//Don't modify this, or your callback will not be called.
if (callback)
callback(error);
}
Database.prototype.runQuery = function(sql, successCallback, failCallback) {
try {
this.db.transaction(
(function (transaction) {
transaction.executeSql(sql, [], this.executeHandler.bind(this, successCallback), this.errorHandler.bind(this, failCallback));
}).bind(this)
);
}
catch (e)
{
this.logError(e, "Error running query: " + sql);
}
}
Database.prototype.runBatch = function(sql, successCallback, failCallback) {
var i = -1;
try {
this.db.transaction(
(function (transaction) {
for (i=0; i< sql.length; i++) {
if (i < sql.length - 1)
transaction.executeSql(sql[i], []);
else
transaction.executeSql(sql[i], [], this.executeHandler.bind(this, successCallback), this.errorHandler.bind(this, failCallback));
}
}).bind(this)
);
}
catch (e)
{
this.logError(e, "error running query batch. Query index: " + i);
}
}
Database.prototype.logError = function(logError, displayMsg) {
if (this.showErrorDialog && displayMsg != null) //handle optional parameter
Mojo.Controller.errorDialog(displayMsg);
Mojo.Log.logException(logError, displayMsg);
}
