SQLite – a local database for your mobile apps

If you need relational storage for your Cordova-based app, SQLite provides a lightweight and feature-rich database.  I will walk you through the steps to use SQLite with Telerik’s Icenium IDE and build a demo Shopping List app that will select, insert, update, and delete records from a local database on the device.

First, add the SQLite plugin in Icenium.  Double-click your “Properties” folder, select the Plugins tab and check the “SQLite” plugin.  This will add the Plugins folder and SQLite scripts within that folder.

Database and Table Setup

Before you can use your SQLite database and tables, you need to make sure they have been created. You cannot access SQLite until after the deviceready event has been raised, so we will create an event listener for this:

document.addEventListener("deviceready", init, false);

 

var myApp = new kendo.mobile.Application(document.body, { transition: "slide", layout: "mobile-tabstrip" });

 

// Initialize the app

function init() {

    myApp.openDb();

    myApp.createTable();

    navigator.splashscreen.hide();

}

First, we determine whether the SQLitePlugin is available, or if we fall back to the native SQLite engine that is available in iOS and Android:

myApp.openDb = function() {

    if(window.sqlitePlugin !== undefined) {

        console.log("Using SQLite Plugin DB");

        myApp.db = window.sqlitePlugin.openDatabase("ShoppingListDB");

    } else {

        // For debuging in simulator fallback to native SQL Lite

        console.log("Use built in SQLite");

        myApp.db = window.openDatabase("ShoppingListDB", "1.0", "Shopping List Demo", 200000);

    }

}

The openDatabase call will create the database if it doesn’t exist, or open it if it already exists.

Now that we have the database open, let’s create our tables.  For our Shopping List app, we will only need one table, and we’ll call it shopping_list.  The only properties we need are a name for each item and whether it is done and marked off our list.

myApp.createTable = function() {

    var db = myApp.db;

    db.transaction(function(tx) {

        tx.executeSql("CREATE TABLE IF NOT EXISTS shopping_list(id INTEGER PRIMARY KEY ASC, item_name TEXT, is_done INTEGER)", [], myApp.onSuccess, myApp.onError);

    });

}

A few things to note about the code above.

1. We use the “IF NOT EXISTS” clause so we only create the table if it doesn’t already exist in the DB.
2. I am creating an “id” column which is an integer and our primary key.  Note that it isn’t necessary to create this column.  By default, all tables in SQLite will have an integer “rowid” column as the primary key.  If you specify a column as INTEGER PRIMARY KEY, it will become an alias of the rowid column.
3. We are using the executeSql method to execute commands against the database.  We will be using this same method for all following database operations.

Inserting Data

If this is the first time the app has run on the device, the table will be empty, so let’s insert some data into it.  My Shopping List app has a text field where you can enter a new item for your list.  When you click the ADD button, it calls our addItem function:

myApp.addItem = function(itemName){

    var db = myApp.db;

    db.transaction(function(tx) {

        tx.executeSql("INSERT INTO shopping_list(item_name, is_done) VALUES (?,?)",

                      [itemName, 0],

                      myApp.onSuccess,

                      myApp.onError);

    });

}

Notice that we are only passing in values for the item_name and is_done columns. The id column will automatically be generated similar to an identity column in SQL Server. I am initializing is_done to zero so it isn’t marked as completed.

Selecting Data

Now that we have data in our table, let’s look at the code to select rows from our table. This is part of my “refresh” function which gets called any time an item is inserted, updated or deleted so our ListViews are kept up to date:

var db = myApp.db;

db.transaction(function(tx) {

    tx.executeSql("SELECT * FROM shopping_list", [],

                  render,

                  myApp.onError);

});

Nothing fancy here, it simply calls my “render” function when the query is complete and “render” will receive both the transaction object and the resultset object that we can iterate through. You can look at the full source for this code, but the “render” method will refresh two ListView objects: one for items that aren’t completed (is_done == 0) and completed items (is_done == 1).

Updating Data

When the user marks an item as completed, it will update the is_done column and then refresh the ListViews. I pass in the id of the item to update. See the full code on how to use a Kendo Template to bind a ListView to an object and pass the ID of the object in the function call.

myApp.markComplete = function(itemId){

    var db = myApp.db;

    db.transaction(function(tx) {

        tx.executeSql("UPDATE shopping_list SET is_done = 1 WHERE id = ?",

                      [itemId],

                      myApp.onSuccess,

                      myApp.onError);

    });

}

Deleting Data

Finally, we give the user the option to “Clear Completed” items which will delete all rows where is_done == 1:

myApp.clearCompleted = function() {

    var db = myApp.db;

    db.transaction(function(tx) {

        tx.executeSql("DELETE FROM shopping_list WHERE is_done = 1",

                      [],

                      myApp.onSuccess,

                      myApp.onError);

    });

}

Now you can add new items, mark them as complete, and clear completed items from the list. You can close the app and reopen it to find all your data still intact. The database only gets removed when the app is uninstalled from the device.

Get more information about SQLite syntax at SQLite.org.

Get the full source code for this demo on GitHub.

Reference: http://blog.falafel.com/icenium-and-sqlite-a-local-database-for-your-mobile-apps/