This tutorial is about creating a iPhone mobile application in Titanium that uses a local database (the database is on the device). Data will be displayed in tableview format from two related tables.

You must already have downloaded and installed Titanium, and also Xcode tools for iPhone development.

The database technology used on the device is SQLite. If you’ve used other SQL databases in the past, the biggest thing to learn about SQlite is its lack of rigid data types. In preparation for this project, you need way to create and maintain a SQLite database. The tool used for this tutorial is the SQLite extension for Firefox. You should download and install that extension now. When you’ve done that, you will have a new menu item under “Tools”, like this:

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. After clicking Create Project, go find the new folder that just got created for your application. Inside the application folder there should be a new folder named Resources:

Step 2: Create The Database File

In the Firefox Tools menu, click on SQLite Manager. Click on “New Database”. It will ask you for a database name: call it “content”. It will next ask you where to save your database; choose the Resources folder that you found in step 1 above. You should now be able to look in the Resources folder and see a file named “content.sqlite”. If you do not see this file, then try again, you cannot proceed until the database file is created in the Resources folder.

Step 3: Create the Tables

The SQLite Manager should still be open from step 2. If not, open it again.
Paste this SQL into the top right window:

create table categories (
category_id INTEGER PRIMARY KEY,
category_name TEXT
);
create table items (
item_id INTEGER PRIMARY KEY,
category_id INTEGER,
item_name TEXT,
item_description TEXT
);

Then click the Run SQL button. You should see a message “not an error”, and then on the left hand side two new tables will show up in the list, like this:

Step 3: Populate the Tables with Data

This new database needs some data in it to be useful. Erase the SQL currently in the top right execution window, and then copy/paste the following SQL:

insert into categories(category_id,category_name) values (1,'Food');
insert into categories(category_id,category_name) values (2,'Shapes');
insert into categories(category_id,category_name) values (3,'Colors');
insert into items(category_id,item_name,item_description)
values (1,'Apples','Apples are usually red, green or yellow.');
insert into items(category_id,item_name,item_description)
values (1,'Oranges','Oranges are easy to peel.');
insert into items(category_id,item_name,item_description)
values (1,'Bananas','Bananas make great toy telephones.');
insert into items(category_id,item_name,item_description)
values (2,'Squares','Squares have four sides.');
insert into items(category_id,item_name,item_description)
values (2,'Circles','Circles are quite round.');
insert into items(category_id,item_name,item_description)
values (2,'Triangles','Triangles do not roll very well.');
insert into items(category_id,item_name,item_description)
values (3,'Blue','Blue is a soothing color.');
insert into items(category_id,item_name,item_description)
values (3,'Purple','Aubergine is deep purple.');
insert into items(category_id,item_name,item_description)
values (3,'Red','Wave some red in front of a bull today.');
insert into items(category_id,item_name,item_description)
values (3,'Green','Green vegetables are good for your kids.');

Again click the Run SQL button. Not much should appear to happen. Although it is again displaying “not an error”, you can’t tell because the message didn’t change. So in order to verify that the tables are populated with data, click on the items table on the left and then click on the Browse & Search tab. You should see the table data:

Step 4: Code the Application

I’m not a fan of tutorials that give me the program code a chunk at a time, so 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. When you’re done, the directory should now look like this:

The Resources folder now contains an updated app.js, two new Javascript files, and two new images – on top of all the files that Titanium created for you by default.

Step 5: 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:

Step 6: Examine the Code

Using your favorite text editor, open app.js. If you have looked at the code for the default project already, there are two new things in this code:
1. Initializing of the database

// Initialize database
Titanium.Database.install('content.sqlite','contentDB');

Notice that the first parameter, the name of the SQLite file, matches the file name you used above in creating the database.

2. The use of Ti.App.Properties to store the user’s confuration setting for font size.

Looking at tab_config.js next, you can see how a picker is used to allow the use to select the font size. This font size property is used when rendering the item description.

Finally the heart of the application is found in tab_categories.js.

This is the code that opens the database and loads the categories into an array:

if (categoryArray.length == 0) {
var db = Titanium.Database.open('contentDB');
var dbrows = db.execute('select category_id,
category_name from categories order by category_name asc');
while (dbrows.isValidRow()) {
categoryArray.push({
catid:dbrows.fieldByName('category_id'),
title:dbrows.fieldByName('category_name')
});
Ti.API.info("Found category: "+dbrows.fieldByName('category_name')+
" ["+dbrows.fieldByName('category_id')+"]");
dbrows.next();
}
dbrows.close();
db.close();
}

Because of the first line, which checks to see if the array is empty, this code will only be executed once (the first time the user renders this tab). After that, because the variable categoryArray is local to this Javascript context and is not overwritten at run time, the array data will simply be re-used on the second and later times that the user renders this tab. This is good because we know that the category array will never change while the application is running – simply because we designed it that way.

If you watch the Titanium console while the application starts up, you will see some debugging information like this:

[INFO] Found category: Colors [3]
[INFO] Found category: Food [1]
[INFO] Found category: Shapes [2]

It’s useful to put such debugging statements into your code so you can verify what is going on inside the application while it’s running.

When a category is clicked (touched) by the user, then the function showItemsInCategory is called and the item list is displayed in a new table view. Titanium handles creating the “back” button type of navigation for you (in the top left you should see a “Categories” button). So all you need to do is render the item rows. Here is the code that loads the data for the item rows:

var itemArray = [];
Ti.API.info("-> "+cname+" <- clicked");
// populate item array from database
// called every time a category row is clicked
var db = Titanium.Database.open('contentDB');
var dbrows = db.execute('select item_id,item_name,
item_description from items where category_id=? order by item_name asc',cid);
while (dbrows.isValidRow()) {
itemArray.push({
item_id:dbrows.fieldByName('item_id'),
title:dbrows.fieldByName('item_name'),
item_description:dbrows.fieldByName('item_description')
});
Ti.API.info("Found item: "+dbrows.fieldByName('item_name')
+" ["+dbrows.fieldByName('item_id')+"]");
dbrows.next();
}
dbrows.close();
db.close();

Again in the Titanium console you should see more debugging information. You can verify for yourself that each time you click into a different category the database is queried and the item data is refreshed.

Note the SQL placeholder “?” being used in this query. Titanium substitutes the value of cid into the placeholder at run time, so that the user will see only the items for the category ID that they selected.

Finally clicking on an item brings up a new window with the item description. Here a small helper function composeItemHTML is used to render the item description. The database could actually hold HTML instead of text descriptions, so far more detailed and nice descriptions could be used.

If you’re interested in seeing a real life example of this that you can download, check out Just Clean Jokes. It uses the ideas from this tutorial plus others.

To see all of the applications I have made with Titanium, please visit: http://www.prairiewest.net/applications.php.

UPDATE: after reading this tutorial, you should look at Tutorial 2j Titanium mobile database synchronization with JSON.