SQLite Cookie Replacement
From WebOS101
Need cookies that can store more than 4k of data? Want a simple SQLite database example? This article shows how to use a SQLite database to store a global applications settings object (i.e. for application Preferences) in a manner similar to webOS cookies.
Instead of cookies, we'll call them cupcakes...
Contents |
Initialize Cupcakes
First, we'll show how to use the cupcakes in your code. The actual cupcake javascript code is shown later.
Start by initializing the cupcake object, preferably in your AppAssistant or StageAssistant setup() method. While in the StageAssistant, we'll also create some default values for our settings object, stored in a global variable called MyAPP.
// Create a global object to store preferences, etc.
// This is done outside the StageAssistant constructor function
// so that it can be accessed anywhere in the application
MyAPP = {};
MyAPP = {
firstSetting : true,
email : 'me@email.com',
numberOfThings : 50
};
// Add more settings to the object if you want...
MyAPP.secondSetting = false;
function StageAssistant() {
}
StageAssistant.prototype.setup = function() {
if (!cupcake.init()) {
Mojo.Log.info("Uh oh, cupcake initialization failed!");
}
this.controller.pushScene('myScene');
};
Retrieve a Cupcake
Since we're saving settings, we probably want to retrieve them from the database at some point. If we do this in the activate() of our first scene, we can then take whatever action is appropriate (changing values of widgets, etc.) after we retrieve the MyAPP object. Because all database queries are asynchronous, we can't retrieve values and use them directly. We have to pass a callback function to the database, and the object will be available in that call back function. In this example, we'll use "gotCupcake()" for the callback function.
FirstAssistant.prototype.activate = function (event) {
cupcake.retrieveCupcake('settings', this.gotCupcake.bind(this));
};
FirstAssistant.prototype.gotCupcake = function (response) {
Mojo.Log.info("Response %j", response);
if (response) {
for (value in response) {
MyAPP[value] = response[value];
//Mojo.Log.info("Pref: ", value, response[value], MyAPP[value]);
}
}
Mojo.Log.info("myObj %j", MyAPP);
};
Note that the gotCupcake function uses a little trick - the for (value in response) loop - that allows you to add defaults for new preferences to the object without worrying whether the preference key already exists or not. If a particular key does not exist in the object retrieved from the database, the default value loaded in StageAssistant setup() will be used. If you decide to add a new preference key, just add it in the StageAssistant.
Save a Cupcake
Cupcakes need to be saved whenever preferences are changed. Ideally, you would do this with a propertyChanged event listener on the individual preferences widgets so that the cupcake is updated whenever the user changes a preference setting. As a simple example, though, we'll just add saving the cupcake to a Preferences scene's deactivate() method - however, this means that if a user closes the app from the preferences scene, changes to the cupcake will likely not be saved because writing changes to the database doesn't finish before the app is closed (remember, database operations are asynchronous). They will be saved, though, if the user backswipes to return to the previous scene.
Preferences.prototype.deactivate = function (event) {
cupcake.updateCupcake('settings', MyAPP);
};
Cupcake database code explained
The meat of the cupcakes (so to speak) is the SQLite database code to create a database, add a table and add, retrieve, and delete cupcakes. We'll provide an explanation of each section of the cupcakes object individually, but the entire cupcakes object is provided below for pasting into your application.
First, create a new object that will contain the add/retrieve/delete methods. The databaseName uses the ext: modifier to store the database on the /media/internal partition - this probably isn't necessary unless your cupcakes are going to be HUGE, however it does provide the advantage of allowing you to copy the database to your PC simply by connecting the device in USB mode (look in the .app-storage directory). We also use Mojo.appInfo.title so that the database uses the applications title from appinfo.json.
function CUPCAKE(){
this.db = null;
var databaseName = "ext:" + Mojo.appInfo.title + "CupcakeDB", // required
version = "0.2", // required
displayName = Mojo.appInfo.title + " cupcake database"; // optional
.
.
.
}
Next up is the initialization of the database. The database is either created or opened (if it already exists) and a new table is created (if it doesn't already exist.
this.init = function(){
this.db = openDatabase(databaseName, version, displayName);
if (!this.db) {
Mojo.Log.info("DAO ERROR! - Could not Open Database!");
Mojo.Controller.errorDialog($L("DAO ERROR! - Could not Open Database!"));
return false;
}
// Create table
var sqlCreateCupcakeTable = "CREATE TABLE IF NOT EXISTS 'cupcakes' " +
"(label TEXT PRIMARY KEY, cupcakeobject TEXT); GO;";
this.db.transaction((function (inTransaction) {
inTransaction.executeSql( sqlCreateCupcakeTable,
[],
function() {
//Mojo.Log.info("Created Tasks Table");
},
this.errorHandler
);
}).bind(this));
return true;
};
Next is our updateCupcake() method. Instead of separate add and update functions, we'll combine them into one method by using SQLite's REPLACE INTO. Our cupcakes table includes a PRIMARY KEY which must be unique for each record. If the key value doesn't exist, REPLACE INTO will create a new record. If it does exist, REPLACE INTO will update the existing record.
Pass a label and an object to the updateCupcake() method. Since the cupcake is passed into the function as a javascript object, Prototype's Object.toJSON is used to convert it into a JSON string that can be stored as a single column in our cupcakes table record.
this.updateCupcake = function ( label, cupcake ) {
var sqlUpdateCupcake = "REPLACE INTO 'cupcakes' (label, cupcakeobject) " +
"VALUES (?, ?); GO;";
cupcake = Object.toJSON(cupcake);
this.db.transaction((function (inTransaction) {
inTransaction.executeSql( sqlUpdateCupcake,
[ label, cupcake ],
function(inTransaction, inResultSet){
Mojo.Log.info("DB results: %j", inResultSet);
},
this.errorHandler);
}).bind(this));
};
Retrieving cupcakes requires a callback function. You call the retrieveCupcake() method from your application code, and provide a function that will be called when the record is retrieved from the database. We also use Prototype's evalJSON() to convert the string back into a javascript object.
Variables retrieved from a SQLite query are immutable - they cannot be changed. Because we'll want to be able to change the preferences in our code, we use Prototype's Object.clone to create a copy of the object returned from the database.
this.retrieveCupcake = function (inLabel, inCallback) {
//Mojo.Log.info("Entering db retrieveCupcake()");
// Retrieve Cupcake with label inLabel
var sqlRetrieveCupcake = "SELECT label, cupcakeobject from cupcakes WHERE label=?; GO;";
this.db.transaction((function (inTransaction) {
inTransaction.executeSql( sqlRetrieveCupcake,
[inLabel],
function(inTransaction, inResultSet){
var results = [];
if (inResultSet.rows && inResultSet.rows.length != 0) {
results = Object.clone(inResultSet.rows.item(0));
}
//Mojo.Log.info("Cupcake Results in db: %j", results);
if (results.cupcakeobject != undefined) {
inCallback(results.cupcakeobject.evalJSON());
}
else {
inCallback(undefined);
}
},
this.errorHandler);
}).bind(this));
};
Deleting cupcakes is also supported, and an errorHandler function is used when things go wrong...
The final step is to instantiate an instance of the CUPCAKE object to use in our application code as follows:
var cupcake = new CUPCAKE();
Cupcake database code
Store the following code as cupcakes.js and include it in sources.json.
/*
* CUPCAKE.JS
*
* Simple SQLite database example for saving/retrieving a "cookie" like object that
* can be longer than 4k.
*
* Usage:
*
* ---In setup() of StageAssistant:---
*
* cupcake.init();
*
* ---Someplace you want to save the settings object:---
*
* myObject = { ... some settings object stuff ... }
* cupcake.updateCupcake('aLabel', myObject);
*
*
* ---Someplace you want to retrieve the object:---
*
* cupcake.retrieveCupcake ('aLabel', this.gotCupcake.bind(this));
*
* ---This is the callback function where the object will be retrieved---
*
* MyAssistant.prototype.gotCupcake = function (response) {
* this.myObject = response;
* }
*
* ---Someplace you want to delete a cupcake:---
*
* cupcake.deleteCupcake('aLabel');
*/
function CUPCAKE(){
this.db = null;
var databaseName = "ext:" + Mojo.appInfo.title + "CupcakeDB", // required
version = "0.2", // required
displayName = Mojo.appInfo.title + " cupcake database"; // optional
this.init = function(){
//Mojo.Log.info("Entering db init");
//Mojo.Log.info("Database info:", databaseName, version, displayName);
//Mojo.Log.info("SQL =", sqlCreateFoldersTable);
this.db = openDatabase(databaseName, version, displayName);
if (!this.db) {
Mojo.Log.info("DAO ERROR! - Could not Open Database!");
Mojo.Controller.errorDialog($L("DAO ERROR! - Could not Open Database!"));
return false;
}
// Create table
var sqlCreateCupcakeTable = "CREATE TABLE IF NOT EXISTS 'cupcakes' " +
"(label TEXT PRIMARY KEY, cupcakeobject TEXT); GO;";
this.db.transaction((function (inTransaction) {
inTransaction.executeSql( sqlCreateCupcakeTable,
[],
function() {
//Mojo.Log.info("Created Tasks Table");
},
this.errorHandler
);
}).bind(this));
return true;
};
this.updateCupcake = function ( label, cupcake ) {
var sqlUpdateCupcake = "REPLACE INTO 'cupcakes' (label, cupcakeobject) " +
"VALUES (?, ?); GO;";
cupcake = Object.toJSON(cupcake);
this.db.transaction((function (inTransaction) {
inTransaction.executeSql( sqlUpdateCupcake,
[ label, cupcake ],
function(inTransaction, inResultSet){
Mojo.Log.info("DB results: %j", inResultSet);
},
this.errorHandler);
}).bind(this));
};
this.retrieveCupcake = function (inLabel, inCallback) {
//Mojo.Log.info("Entering db retrieveCupcake()");
// Retrieve Cupcake with label inLabel
var sqlRetrieveCupcake = "SELECT label, cupcakeobject from cupcakes WHERE label=?; GO;";
this.db.transaction((function (inTransaction) {
inTransaction.executeSql( sqlRetrieveCupcake,
[inLabel],
function(inTransaction, inResultSet){
var results = [];
if (inResultSet.rows && inResultSet.rows.length != 0) {
results = Object.clone(inResultSet.rows.item(0));
}
//Mojo.Log.info("Cupcake Results in db: %j", results);
if (results.cupcakeobject != undefined) {
inCallback(results.cupcakeobject.evalJSON());
}
else {
inCallback(undefined);
}
},
this.errorHandler);
}).bind(this));
};
this.deleteCupcake = function (inLabel) {
//Mojo.Log.info("Entering db deleteCupcake()");
// Delete Cupcake with label inLabel
var sqlDeleteCupcake = "DELETE FROM cupcakes WHERE (label=?); GO;";
this.db.transaction((function (inTransaction) {
inTransaction.executeSql(sqlDeleteCupcake,
[inLabel],
function(inTransaction, inResultSet){
//Mojo.Log.info("Deleted Cupcake ", inLabel);
},
this.errorHandler);
}).bind(this));
};
// **********************************************
// Error Handler
// **********************************************
this.errorHandler = function(inTransaction, inError) {
Mojo.Log.info("DAO ERROR!", inError.message);
Mojo.Controller.errorDialog(
$L("DAO ERROR") + " - (" + inError.code + ") : " + inError.message
);
}; // End errorHandler().
}
var cupcake = new CUPCAKE();
