This is the second Titanium database tutorial. Please read the first Titanium database tutorial if you haven’t already done so, since this tutorial builds on the first one.

In the first tutorial, we created a database with two related tables – categories and items. We then built a Titanium app that displayed the data in the database. In this tutorial, we will re-use the database from tutorial 1 and add remote database synchronization. That is, we will pull XML data from a remote web site and then update the local database with the contents of the XML. I used XML only due to personal preference, you could just as easily use JSON for the remote data feed (and then modify the Titanium code to read the JSON).

The remote XML will look like this:

<?xml version="1.0" encoding="utf-8"?>
<feed>
<status>OK</status>
<retrieved>1294075736</retrieved>
<item>
<id>11</id>
<category_id>2</category_id>
<name>Oval</name>
<description>Squishing a circle turns it into an oval.</description>
</item>
</feed>

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 XML feed and be pulled down to the mobile device.

Step 1: Create A New Titanium Application

Open Titanium and click on New Project. Fill in the form so it looks similar to this:

Then click Create Project. Make sure you note where you are creating your new application.

Step 2: Download Resources

You don’t need to re-create the database from Tutorial 1, just download all of the Resource 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 and move the contents of the zip into your project Resources directory. Do not create a Resources folder under your existing Resources folder. If you are prompted to overwrite files, click yes or OK. When you’re done, the directory should now look like this (you will have other default project files if you didn’t delete them first):

Compared to Tutorial 1, app.js has been changed, and a new file tab_main.js exists along with an appropriate navigation image.

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, click the Test & Package tab. Wait for Titanium to locate your iPhone development environment… eventually the little box to the right of “SDK” should be filled in with a number like 4.1 (the version of XCode tools). After that, click on “Launch” and wait patiently. The iPhone simulator will open and the application should run, resulting in this:

Notice that there is a new 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 should contain information similar to this:

[INFO] DB needs updating from remote
[INFO] Open: URL
[INFO] Updating database...
[INFO] Found 1 items to update
[INFO] Item [11] = Sphere
[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 <- clicked
[INFO] Found item: Circles [5]
[INFO] Found item: Sphere [11]
[INFO] Found item: Squares [4]
[INFO] Found item: Triangles [6]
[INFO] Application has exited from Simulator

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. Let’s see how that got there.

Step 4: Examine the Code

1. app.js
Using your favorite text editor, open app.js. There is a new 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
Again in a text editor open tab_home.js. 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;
showModalWindow();
updateDatabaseFromRemote(xhrURL,currentTS);
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;
xhr.setTimeout(25000);
xhr.open('GET',remoteURL);
xhr.send();

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.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 XML 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 item with primary key 11. (There are five different shapes coded into update.php: Pentagon, Oval, Cone, Cube, Sphere)

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 XML content is retrieved before acting on it. The following line checks for a status of “OK” in the XML that was received (there could also be a status of “AUTHERROR” in case of a bad auth key, or perhaps no status at all if the document was blank):

if (doc.getElementsByTagName("status").item(0).text=='OK') {

If that passes, then we have valid XML from the remote site. Parsing through that XML, the application then inserts or updates the local database:

var items = doc.getElementsByTagName("item");
var db = Titanium.Database.open('contentDB');
for (var c=0;c<items.length;c++) {
var item = items.item(c);
var item_id = item.getElementsByTagName("id").item(0).text;
var item_cid = item.getElementsByTagName("category_id").item(0).text;
var item_name = item.getElementsByTagName("name").item(0).text;
var item_desc = item.getElementsByTagName("description").item(0).text;
db.execute('REPLACE INTO items (item_id,category_id,item_name,item_description)
VALUES (?,?,?,?)', item_id,item_cid,item_name,item_desc);
}
db.close();

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

Titanium.App.Properties.setInt('lastUpdatedTS',nowTS);
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).

If you’re interested in seeing a real life example of this that you can download, check out Just Clean Jokes. It uses ideas from these first two tutorials.

To see all of our applications, 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.