Saving data locally using a SQLite database

Saving and updating data to our SQLite database is just a matter of creating a function for each Create, Read, Update, and Delete (CRUD) operation that we need, and forming the SQL statement before we execute it against the local database (our db object).

In this recipe, we'll edit the database.js module file to return a db object that contains two new functions, one for inserting a record into our favorites table and one for deleting a record. We'll also capture the click events on our table rows to allow the user to view the record in a detailed subwindow, and add a button for creating and deleting favorites.

How to do it...

Open the JavaScript file called database.js, and replace its contents with the following:

//create an instance of a database
module.exports = (function() {
  //create the database object
  var db = {};
  
  db.database = Ti.Database.open('mydb');
  db.database.execute('CREATE TABLE IF NOT EXISTS favorites (ID INTEGER  PRIMARY KEY AUTOINCREMENT, TITLE TEXT, LINK TEXT, DESCRIPTION TEXT)');
    
  db.insertFavorite = function(title, description, link) {
  var sql = "INSERT INTO favorites (title, description, link) VALUES (";
    sql = sql + "'" + title.replace("'", "''") + "', ";
    sql = sql + "'" + description.replace("'", "''") + "', ";
    sql = sql + "'" + link.replace("'", "''") + "')";
    db.database.execute(sql);
    return db.database.lastInsertRowId;
  };

  db.deleteFavorite = function(title) {
    var sql = "DELETE FROM favorites WHERE title = '" + title 
      + "'";
    db.database.execute(sql);
  };

  return db;
})();

Now, we need to make sure that we have access to the current tab of a Window (in order to be able to open a detail window later), so we must add a couple of lines of code to the app.js file, just after the line where we define tab2:

win1.tab = tab1;
win2.tab = tab2;

By assigning each tab to a tab property in the window, we're able to access it directly from the associated JavaScript file for that window, which will make it easier to access the current tab in order to open new windows.

Then, back in our recipes.js file, we are going to capture the click event of the tblRecipes TableView in order to get the tapped row's data and save it in our favorites table in SQLite. Add the following code after you have defined tblRecipes:

//create a new window and pass through data from the
    //tapped row
    tblRecipes.addEventListener('click', function(e) {
        var data = e.row.data;

        console.log(data)

        //row index clicked
        var detailWindow = Ti.UI.createWindow({
            title: data.title,
            link: data.link,
            backgroundColor: '#fff'
        });

        //add the favorite button
        var favButton = Ti.UI.createButton({
            title: 'Add Favorite',
            color: '#000',
            left: 10,
            top: 10,
            width: Ti.UI.SIZE,
            height: Ti.UI.SIZE
        });

        favButton.addEventListener('click', function(e) {

            var newId = db.insertFavorite(data.title, data.description, data.link);
            console.log('Newly created favorite id = ' + newId);
            detailWindow.id = newId;
            alert('This recipe has been added as a favorite!');
        });

        detailWindow.add(favButton);

        //let's also add a button to remove from favourites
        var deleteButton = Ti.UI.createButton({
            title: 'Remove favourite',
            color: '#000',
            right: 10,
            top: 10,
            width: Ti.UI.SIZE,
            height: Ti.UI.SIZE           
        });

        deleteButton.addEventListener('click', function(e) {            
            db.deleteFavorite(data.title);
            console.log('Deleted ' + db.database.rowsAffected + ' favorite records. (id ' + data.id + ')');
            alert('This recipe has been removed from favorites!');

        });

        detailWindow.add(deleteButton);

        //finally, add the full description so we can read the
        //whole recipe        

        var lblDescription = Ti.UI.createWebView({
            left: 10,
            top: 60,
            width: 300,
            height: Ti.UI.FILL,
            color: '#000',
            html: data.description
        });

        detailWindow.add(lblDescription);

        //open the detail window
        win.tab.open(detailWindow);
    });

How it works...

Firstly, we create functions that'll accept the parameters to insert a favorite record, create a SQL statement, and then execute that SQL query statement against our SQLite database. This is just a basic SQL query. However, take note that, just as you would with a desktop application or website, any input parameters should be escaped properly to avoid SQL injection! In our recipe, we used a simple mechanism to do this—replacing any occurrences of the apostrophe character with a double apostrophe.

The second half of our code defines a new Window and adds to it a couple of buttons and a label for displaying the full text of our recipe. You should refer to Chapter 1: Building Apps Using Native UI Components for more details about opening Windows and adding and customizing UI components in them.

There's more…

Android users can always press the back button on their device to return to the app after the browser is launched, but it's worth noting that iOS users have to switch back to the application manually.

The following screenshots show the detail view window for our recipe before and after we insert a favorite record into the SQLite database table: