Amazon.com Widgets

Agile Otaku

Using SQLite Database with Titanium Mobile

If you came to this post through Google or through other means, it would be best to download the code as shown in my github account.

$ git clone git@github.com:HugeAim/titanium-list-app.git
$ git checkout part4

So far, in this series, you were able to create a simple list application with a group header and an add and delete functionality.

In this post, you are going to learn how to save the added and deleted items into a sqlite database.

I assume that you already have the SQLite installed. If not, please go ahead and install it now.

After installation, create a new database, name it as lists.sqlite, then save it in the current List Applications' Resources folder as shown below

$ sqlite3 Resources/lists.sqlite
SQLite version 3.7.7 2011-06-25 16:35:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

That command will open a sqlite prompt. Create a table using it as shown below.

sqlite> CREATE TABLE IF NOT EXISTS lists (id INTEGER PRIMARY KEY, content TEXT NOT NULL);
sqlite>

You can do it also in the terminal tab of Titanium Studio.

Now, modify the current app.js that you have created so far. This time, you will no longer need the "data" variable. That data will come from the database.

//var data = [
//    {title:'List Content 1', hasChild:true, toUrl:'window2.js', header:"Header 1"},
//    {title:'List Content 2'},
//   {title:'List Content 3',header:"Header 2"},
//    {title:'List Content 4'}
//];

Add a database installer in the line after that. Then, query the lists table.

var db = Ti.Database.install('lists.sqlite','lists.sqlite');
var rows = db.execute('SELECT * FROM LISTS;');
var data = [];
while (rows.isValidRow())
{
    data.push({
      title:rows.fieldByName('content'),
      id:rows.fieldByName('id')
    });
    rows.next();
};
rows.close();

Then modify the addBtn click listener located in the buttom of the file.

var count = data.length;
addBtn.addEventListener('click',function(){
        row = Ti.UI.createTableViewRow( {title:'List Content Appended' + count,id:count});
    db.execute('INSERT INTO lists (content) VALUES (?)',row.title);
    tableview.appendRow(row);
    updateRowCount();
});
function updateRowCount(){
    rows  = db.execute('select count(*) as count from lists');
    while (rows.isValidRow())
    {
        count = rows.fieldByName("count");
        rows.next();
    };
    rows.close();
}

Those highlighted rows basically appends a single row per click then updates the row count in preparation for the next add button operation.

Try to add some rows, close the application and open it again. Notice now that the state is saved to the database.

Deleting

So far you were able to save state of the added rows to the database. Now, modify the delete action by adding the following in the bottom of the app.js file.

 tableview.addEventListener('delete', function(e) {
    db.execute("DELETE FROM lists WHERE id=?",e.row.id);
    updateRowCount();
 });

That code cascades the deleted item from the table view in the database.

The application should work as desired now.

To download the code specific to this Part 5, do.

$ git checkout part5

Titanium

Comments