Titanium Tutorial 2j – Database Sync With JSON

This is the second Titanium database tutorial, modified to use JSON. You may want to read the first Titanium database tutorial for reference first, however this tutorial is where you should start actually working with Titanium Studio.

The “old” first and second Titanium database tutorials may no longer work because they were based on the stand-alone Titanium compiler that existed before Titanium Studio. Here is an update for tutorial 2 that we will call “Tutorial 2j” because it uses JSON instead of XML for the remote update. In this tutorial we will pull JSON data from a remote web site and then update the local database with the contents of the JSON.

Why JSON? A few reasons:

  • It is more compact, so there will be less data that mobile users need to pull over potentially slow networks
  • Titanium is built on Javascript, and JSON is natively part of that. Because of this, the JSON routines are much faster than the XML routines.
  • I found that the XML parser will run out of memory once the XML document gets large enough! Very bad, this could crash the app.
  • And a non-technical reason: I find JSON easier and faster to read now that I am used to it

The remote JSON will look similar to this:

"items": [
"id": "11",
"catid": "2",
"name": "Oval",
"desc": "Squishing a circle turns it into an <span style='color:green'>oval</span>."
"id": "12",
"catid": "3",
"name": "Orange",
"desc": "<span style='color:orange'>Orange</span> you glad I didn't say banana"

By popular request I am including the PHP file that I used to generate this JSON on the server. Please note that you do NOT need to install or run the PHP file for the purpose of this tutorial, it is just there so you can see what is happening on the server side.

The assumption for this tutorial is that the database local to the device has been seeded from the master database used on the remote site. Therefore, the primary keys and all data on the device would match exactly the master database at some point in time when the project is built. All data modified after that time will show up in the JSON feed and be pulled down to the mobile device.

Step 1: Download The Files

You don’t need to re-create the database from Tutorial 1, just download all of the files for this project and the database will be included. here are the files that you will need to download (right-click and save). Please download the zip file and then unzip. You should have a folder with contents like this:

Step 2: Create a New Project

Open Titanium Studio. Click on File > Import:

Choose “Existing Folder as New Project”:

On the next page, make sure that Titanium Mobile is selected as primary:

Once the import is done, the new project will show up in the Project Explorer:

Step 3: Compile and Run the Application

Before you look at what the code is doing, let’s see if it will run. In Titanium Studio, with the new project highlighted, click the Run butten and select iPhone Simulator:

Eventually the iPhone simulator will open and the application should run, resulting in this:

Notice that there is a tab “Home”. This tab will display the item in the local database with the highest ID. We will examine the code for this tab in the next step. Click the Categories tab, then click Shapes. The debug console within Titanium Studio should contain information similar to this:

[INFO] DB needs updating from remote
[INFO] Open: URL
[INFO] Updating database...
[INFO] Found 2 items to update
[INFO] Item [11] = Sphere
[INFO] Item [12] = Brown
[INFO] Done.
[INFO] Set lastUpdatedTS to 1294078155
[INFO] Found category: Colors [3]
[INFO] Found category: Food [1]
[INFO] Found category: Shapes [2]
[INFO] Found item: Triangles [6]
[INFO] -> Shapes 

If you compare the data to what we inserted when creating the database in Tutorial 1, you will notice a new item with ID #11 and another with ID #12. Let’s see how those got there.

Step 4: Examine the Code

1. app.js

In Titanium Studio Project Explorer, open the Resources folder for this project, then double-click to open app.js. There is a section of code at the top:

var lastUpdatedTS = Titanium.App.Properties.getInt('lastUpdatedTS');
if (lastUpdatedTS == null || lastUpdatedTS < 1291208400) {
lastUpdatedTS = 1291208400;
Ti.App.Properties.setInt('lastUpdatedTS', 1291208400 );

The Titanium property lastUpdatedTS will contain a unix timestamp corresponding to when the local database was last updated. The initial timestamp of 1291208400 is not a random number, it should correspond to the timestamp when the local database and master database were in sync and contained the exact same data. There are many web sites that will convert dates and times into timestamps and vice-versa. Just Google unix timestamp if you don’t already have a favorite.

2. tab_home.js

Double-click to open tab_home.js.
(At this point I should mention: this is apparently bad coding style! I’ve read that “one javascript file per tab” is inefficient, and others have already demonstrated better ways to code a Titanium app. You should search for “Titanium programming standards” or something like that – others have already written about this better than I could have. I did not refactor the code just for this tutorial.)
Almost all of the code in this tab is dedicated to database operations. Scroll down around line 164 after all of the function definitions, to the following code which starts off the whole data update process:

// see if database should be refreshed
if (checkNeedsUpdating(currentTS)
&& (Titanium.Network.networkType != Titanium.Network.NETWORK_NONE)) {
busy = true;
setTimeout(checkBusy, 300);

Assuming we are updating the data, the modal window is drawn to prevent the user from interacting with the UI while the app is busy, and then the function updateDatabaseFromRemote is called:

// open the client and get the data
var lastUpdatedTS = Titanium.App.Properties.getInt('lastUpdatedTS');
remoteURL += "?since=" + lastUpdatedTS;
remoteURL += "&auth=" + xhrKey;

The auth parameter contains the authorization key for this app. The security model used here is trivial, and a real production app would use stronger security if you were concerned about your data being used by unauthorized applications. However, the PHP file update_json.php that I provide for you to use will check the key – change it within the app and recompile if you want to see what happens when the wrong auth key is passed to the remote web site.

The since parameter tells the remote database the timestamp of when our local device database was last updated. The JSON feed should then appropriately contain only the records that have changed in the master database since that timestamp. However for the purpose of this tutorial, the remote file will only update items with primary key 11 and 12. (There are five different shapes coded into update_json.php, and five different colors).

Due to the non-blocking nature of Titanium HTTPClient calls, the database operations are contained in the onload method of the HTTPClient that we created, so that the app waits until all of the JSON content is retrieved before acting on it.

After parsing the JSON and making sure there are more than zero items to process, the application then inserts or updates the local database:

var db = Titanium.Database.open('contentDB');
for (var c=0; c< jsonObject.items.length;c++) {
var item       = jsonObject.items[c];
var item_id    = item.id;
var item_catid = item.catid;
var item_name  = item.name;
var item_desc  = item.desc;
Ti.API.info("Item ["+item_id+"] = "+item_name);
db.execute('REPLACE INTO items (item_id,category_id,item_name,item_description)
VALUES (?,?,?,?)',item_id,item_catid,item_name,item_desc);

Since the database was just updated, we need to set The Titanium property lastUpdatedTS to reflect this:

Ti.API.info("Set lastUpdatedTS to "+nowTS);

Finally, the modal window is closed and the UI control returned to the user. At this point, the newly updated item should be displayed on the home tab of the app. Close the simulator and then recompile in Titanium to re-open the app, and watch the home tab change content (and watch the console as the data is pulled from the remote site).

To see all of the applications I have made with Titanium, please visit: http://www.prairiewest.net/applications.php. If you download any of them, an iTunes app store review would be greatly appreciated.

If you enjoyed this tutorial, or have any questions, please leave a comment or follow me on Twitter.