SQLite is an open source library, written in C, that implements a fully self-contained SQL database engine. All of the data required to implement the database is stored in a single, cross-platform disk file. Because SQLite is self-contained, it requires few external libraries and little support from the operating system. This is the prime reason that it is ideal for a mobile platform like the iPhone.

SQLite has been adopted for use on the iPhone for other reasons as well, including its very small footprint. Weighing in at less than 300K, the library is small enough to use effectively on mobile devices with limited memory. What's more, SQLite requires no configuration files, has no setup procedure, and needs no administration. You can just drop your database file on the iPhone, include the SQLite library in your iPhone project, and you are ready to roll.

Because SQLite implements most of the SQL92 standard, you will find working with an SQLite database intuitive if you already know SQL. You should keep in mind that there are some features of SQL92 that are not currently supported in SQLite. These include RIGHT and FULL OUTER JOIN, complete support for ALTER TABLE, FOR EACH STATEMENT triggers, writeable VIEWs, and GRANT and REVOKE permissions. For more detail on unsupported functionality, take a look at the SQLite web site http://www.sqlite.org/omitted.html.

Because the interface to SQLite is written in C, and Objective-C is a superset of C, you can easily incorporate SQLite into your Objective-C–based iPhone projects.

When starting a data-centric application for the iPhone, there is a significant architectural decision that you need to make. Should you use SQLite or Core Data for your data management needs?

Let's take a quick look at what Core Data is and isn't. First, Core Data is not a relational database like SQLite. Core Data is an object persistence framework. Its primary purpose is to provide the developer with a framework to persist objects created by the application. Core Data allows you to model your data as objects using a convenient graphical interface built into Xcode. You can then manipulate those objects in code with an extensive set of APIs. Designing and defining your data objects using the graphical interface can simplify the creation of the Model portion of the MVC architecture.

Core Data can use SQLite, among other storage types, as a backing store for its data. This causes some confusion for developers. It is a common misconception that because Core Data can use SQLite to store data, Core Data is a relational database. This is not correct. As mentioned, Core Data is not an implementation of a relational database. Although Core Data uses SQLite in the background to store your data, it does not store the data in a way that is directly accessible to the developer. In fact, you should never attempt to manually modify the backing database structure or its data. Only the Core Data framework should manipulate the structure of the database and the data itself. You can feel free to open the SQLite database and take a look at it if you are curious, but making any modifications to the data or database structure will likely invalidate it and cause problems when trying to access it using Core Data.

While Core Data is the preferred framework for dealing with data that is created on the iPhone, SQLite remains a useful tool for iPhone developers. If you need the functionality provided by a relational database, you should strongly consider using SQLite directly. However, if you only need to persist objects created during the use of your application, you should consider using Core Data. You explore the Core Data framework in detail in Part II of the book.

While Core Data is the recommended framework for creating data on the iPhone, you may want to forego Core Data and use the SQLite API directly for several reasons.

First, if you are targeting devices that could be running a version of the iPhone OS prior to 3.0, you cannot use Core Data. Core Data became available with the 3.0 release of the iPhone OS.

You might also choose to use the SQLite database directly if you need to preload a large amount of data on the device. Take, for example, a GPS navigation application. Navigation applications need a great deal of data, including points of interest and the maps themselves. A good option for the architectural design is to create an SQLite database that contains all of the POI and map data. You can then deploy that database with your application and use SQLite APIs to access the database.

It is easy to create an SQLite database using desktop tools. You can then use the same tools, scripts, or a desktop application to load your data into the database. Then, you can simply deploy the database to the device with your application.

In this chapter, you are going to build the database for a catalog application that could be used by a mobile sales force. The catalog will need to be preloaded with data, not populated on the iPhone itself, so SQLite will be used as the back-end data store.

If you don't know, normalization is the process of breaking down your data in a way that makes it easy to query. Normalization helps to avoid common problems in database storage such as duplication of data. For example, when creating your database, a designer may want to store all of the data in a single table, as in Figure 2-1.

Figure 2.1. Storing the data in a single table

For those unfamiliar with the Entity-Relationship Diagram (ERD), the box represents an entity or table in the database. The ovals that are connected to the entity are the attributes of that entity or the fields of the table. So, this diagram shows one table with each attribute as a field in the table.

The problem with this database design is that it is not normalized. There will be duplication of data if there is more than one product in the catalog manufactured by the same manufacturer or if there is more than one product manufactured in a specific country. In that case, the data may look something like Figure 2-2.

Figure 2.2. Data in a single table

You can see that the same manufacturer has more than one product in the database. Also, there is more than one product made in a specific country. This design is a maintenance problem. What happens if the data entry person populating the database types in "Spirit Industries" for item 1 and "Spit Industries" for item 3? It will appear in the application that two different companies make these products, when in reality they are both manufactured by "Spirit Industries." This is a data integrity problem that can be avoided by normalizing the data. You can remove the manufacturer and country of origin from the product table and create new tables for these fields. Then, in the product table, you can just reference the value in the related tables.

Additionally, this new design could allow you to add more detail about the manufacturer such as address, contact name, and so on. For the sake of simplicity, you won't be doing that, but proper normalization helps make this type of flexibility possible.

The new design should look something like Figure 2-3.

Figure 2.3. Normalized database tables

Figure 2-4 shows what the new normalized database will look like.

Figure 2.4. Data in normalized tables

You can see that instead of specifying the manufacture and origin explicitly in the main Products table, you just reference the ID in the related tables. The fact that you can relate data in one table to data in another gives a relational database both its name and its power.

While normalization is important, it is possible to take it too far and over-normalize your data. For instance, you could create a separate table for price. In that case, all products that cost $1 would reference a row in a Price table that contains the value $1. While doing this would eliminate the duplication of data for all products that cost $1, it would be painful to write the code to maintain the relationship between the Product and Price tables. This would be considered over-normalized and should be avoided.

As important as normalization is, you should know that laying out the data in a way that optimizes its display is important as well. Optimizing the user experience on the iPhone is often a difficult and tedious process. Users expect a very fast and smooth user experience. If the data is over-normalized, it may be an optimal data storage strategy, but if accessing the data for display at runtime is too costly, the performance of the application will suffer. Remember that you are writing applications for a mobile platform with limited CPU capability. You will pay a penalty for using overly complex SQL to access your data. You are better off in some instances repeating data instead of using relationships. I am not advocating not normalizing your data at all; just keep in mind how the data will be used on the device as you design your database.

You can use a couple of different methods to create, modify, and populate your SQLite database. Let's first look at is the command-line interface.

Using a command-line interface may not seem optimal in these days of graphical interfaces, but the command line does have its advantages. One feature that stands out is that you can create and populate a database using the command-line interface and scripts. For example, you could write a PERL script that gets data out of an enterprise database such as Oracle or MySQL and then creates an SQLite database with a subset of the data. While scripting is beyond the scope of this book, I will show you how to create and populate a database using the command-line tool.

The command-line interface can also be used to import data from a file into a table, read in and execute a file that contains SQL, and output data from a database in a variety of formats including:

To start the command-line tool, you'll need to bring up a terminal window. Next, change to the directory where you want to store your database file. For this example, you create the database in the root of your home directory and then copy it into the Xcode project that you will create later.

Start the command-line tool and create your new database by typing sqlite3 catalog.db at the command prompt. This command will start the command-line tool and attach the database catalog.db. The ATTACH DATABASE command will either attach an existing database to the SQLite tool or create a new database if the specified file doesn't already exist. You can attach multiple databases to a single instance of the command-line tool and reference data in each database using dot notation in the form database-name.table-name. This powerful feature can be used to migrate data from one database to another.

Aside from being able to execute SQL at the command line, the command-line interface tool has various metacommands that are used to control the tool itself. These can be displayed by typing .help from the command line. You can see what databases you have attached to the current instance of the tool by typing .databases at the command line. You can quit the command-line tool by typing .exit or .quit.

To create your main Product table, type the CREATE TABLE statement at the SQLite command prompt as follows:

CREATE  TABLE "main"."Product"
  ("ID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
    "Name" TEXT, "ManufacturerID" INTEGER, "Details" TEXT,
    "Price" DOUBLE, "QuantityOnHand" INTEGER,
    "CountryOfOriginID" INTEGER, "Image" TEXT );

A full discussion of the SQL language is beyond the scope of this book. You should pick up a copy of SQL For Dummies by Allen Taylor (Wiley, 2010) if you are interested in learning more about the SQL language.

The previous SQL statement creates a table called Product in the main database. It adds the fields that you designed in your ERD. Finally, it specifies that the ID field is the PRIMARY KEY and that it is an AUTOINCREMENT field. This means that you do not have to supply ID values; the database engine will generate them for you.

Now that you have created the Product table, let's move on to creating the Manufacturer and CountryOfOrigin tables. Type the following SQL commands at the command prompt:

CREATE  TABLE "main"."Manufacturer"
  ("ManufacturerID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  "Name" TEXT NOT NULL );

CREATE  TABLE "main"."Country"
  ("CountryID" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL ,
  "Country" TEXT NOT NULL );

At the time of this writing, the SQLite engine provided with the Snow Leopard operating system is version 3.6.12. This version does not implement foreign key constraints. Foreign key constraints are planned for inclusion in SQLite version 3.6.19, but this is only a draft proposal. Thus, the developer is responsible for enforcing these constraints.

You have just successfully created your database. You should have a database file that contains three tables: Product, Manufacturer, and CountryOfOrigin. Now, let's get some data into the tables.

Having a database is great, but the data is what really counts. You can populate your database one item at a time from the command line using INSERT SQL statements.

Creating Records with the INSERT Command

Figure 2-5 shows the syntax for the INSERT statement.

Figure 2.5. The Insert statement syntax

In case you aren't sure, I'll quickly go over how to read SQL syntax diagrams.

The open circles at the beginning and end are terminators. They show where the SQL statement starts and ends. The arrow that comes out of the terminator indicates the main branch of the statement.

Keywords are indicated in all caps. Keywords on the main branch are required. So, for the INSERT statement, INSERT, INTO, and VALUES are required for an INSERT statement to be valid SQL. Anything that is not on the main branch is optional. Choices for optional keywords are left-aligned. For example, the OR after INSERT is optional. If you do use OR, you must pick one and only one of the options ROLLBACK, ABORT, REPLACE, FAIL, or IGNORE.

Text that is not in all caps is data provided by the user. So, the INSERT SQL Figure 2-5 indicates that the user needs to specify the database name and table name into which the data will be inserted. Additionally, the user must specify the columns into which the data will be inserted and, finally, the values to be inserted.

You can insert a row into the Product table using the following INSERT statement:

INSERT INTO "main"."Product"
  ("Name","ManufacturerID","Details","Price","QuantityOnHand",
  "CountryOfOriginID","Image")
VALUES ('Widget A','1','Details of Widget A','1.29','5','1', 'Canvas_1')

While it is possible, inserting data one row at a time using SQL is not very efficient. I mentioned earlier that the command-line tool has the ability to import text files into the database. This can come in very handy when dumping data from another database, Microsoft Excel, or simply a text file. Instead of typing in each INSERT statement, you can create a text file for each of the database tables and then use the import functionality to get the data into the database.

Create a text file in your home directory called products.txt and include the following data. Note that tabs are used between each field as a delimiter. You can also download the file from this book's companion web site.

1   Widget A   1   Details of Widget A   1.29    5   1   Canvas_1
2   Widget B   1   Details of Widget B   4.29    15  2   Canvas_2
3   Widget X   1   Details of Widget X   0.29    25  3   Canvas_3
4   Widget Y   1   Details of Widget Y   1.79    5   3   Canvas_4
5   Widget Z   1   Details of Widget Z   6.26    15  4   Canvas_5
6   Widget R   1   Details of Widget R   2.29    45  1   Canvas_6
7   Widget S   1   Details of Widget S   3.29    55  1   Canvas_7
8   Widget T   1   Details of Widget T   4.29    15  2   Canvas_8
9   Widget L   1   Details of Widget L   5.29    50  3   Canvas_9
10  Widget N   1   Details of Widget N   6.29    50  3   Canvas_10
11  Widget E   1   Details of Widget E   17.29   25  4   Canvas_11
12  Part Alpha 2   Details of Part Alpha 1.49    25  1   Canvas_12

13  Part Beta  2   Details of Part Beta  1.89    35  1   Canvas_13
14  Part Gamma 2   Details of Part Gamma 3.46    45  2   Canvas_14
15  Device N   3   Details of Device N   9.29    15  3   Canvas_15
16  Device O   3   Details of Device O   21.29   15  3   Canvas_16
17  Device P   3   Details of Device P   51.29   15  4   Canvas_17
18  Tool A     4   Details of Tool A     14.99   5   1   Canvas_18
19  Tool B     4   Details of Tool B     44.79   5   1   Canvas_19
20  Tool C     4   Details of Tool C     6.59    5   1   Canvas_20
21  Tool D     4   Details of Tool D     8.29    5   1   Canvas_21
                                                         

Each column, separated by a tab in the text file, represents a field in the database. The fields must be in the order that they were created using the CREATE TABLE command. So the order of the fields is ID, Name, ManufacturerID, Details, Price, QuantityOnHand, CountryOfOriginID, and Image.

To import your data file into the database, open up the SQLite command prompt if you do not still have it open. Type the command .separator " " to specify that you are using the tab character as the field separator in the data file. Then, type .import "products.txt" Product to import the file products.txt into the Product table. Your data should have been successfully imported into the database.

Reading Your Rows with the SELECT Command

To verify that your data was successfully imported, you can display it using the SQL SELECT statement. The syntax for the SELECT statement appears in Figure 2-6:

Figure 2.6. The Select statement syntax

Type select * from Product; to see all of the rows in your product table. The output should look like this:

1   Widget A   1   Details of Widget A   1.29    5   1   Canvas_1
2   Widget B   1   Details of Widget B   4.29    15  2   Canvas_2
3   Widget X   1   Details of Widget X   0.29    25  3   Canvas_3
4   Widget Y   1   Details of Widget Y   1.79    5   3   Canvas_4
5   Widget Z   1   Details of Widget Z   6.26    15  4   Canvas_5
6   Widget R   1   Details of Widget R   2.29    45  1   Canvas_6
7   Widget S   1   Details of Widget S   3.29    55  1   Canvas_7
8   Widget T   1   Details of Widget T   4.29    15  2   Canvas_8
9   Widget L   1   Details of Widget L   5.29    50  3   Canvas_9
10  Widget N   1   Details of Widget N   6.29    50  3   Canvas_10
11  Widget E   1   Details of Widget E   17.29   25  4   Canvas_11
12  Part Alpha 2   Details of Part Alpha 1.49    25  1   Canvas_12
13  Part Beta  2   Details of Part Beta  1.89    35  1   Canvas_13
14  Part Gamma 2   Details of Part Gamma 3.46    45  2   Canvas_14
15  Device N   3   Details of Device N   9.29    15  3   Canvas_15
16  Device O   3   Details of Device O   21.29   15  3   Canvas_16
17  Device P   3   Details of Device P   51.29   15  4   Canvas_17
18  Tool A     4   Details of Tool A     14.99   5   1   Canvas_18
19  Tool B     4   Details of Tool B     44.79   5   1   Canvas_19
20  Tool C     4   Details of Tool C     6.59    5   1   Canvas_20
21  Tool D     4   Details of Tool D     8.29    5   1   Canvas_21

This is identical to the input data file so you are ready to proceed.

Create another text file in your home directory called manufacturers.txt and include the following data:

1  Spirit Industries
2  Industrial Designs
3  Design Intl.
4  Tool Masters

Import the manufacturer data into the database by typing .import "manufacturers.txt" Manufacturer to import the file manufacturers.txt into the Manufacturer table. You can again use the SQL SELECT statement to verify that your data has been imported correctly by typing select * from manufacturer;.

Finally, create another text file in your home directory called countries.txt and include the following data:

1  USA
2  Taiwan
3  China
4  Singapore

Import the country data into the database by typing .import "countries.txt" Country to import the file countries.txt into the Country table. You can again use the SQL SELECT statement to verify that your data has been imported correctly by typing select * from country;.

Now that you have your data in the database, feel free to experiment with all of the standard SQL that you already know! For example, if you want to see all of the products ordered by price, you can type select name,price from product order by price;. The result of that query is as follows:

Widget X    0.29
Widget A    1.29
Part Alpha  1.49
Widget Y    1.79
Part Beta   1.89
Widget R    2.29
Widget S    3.29
Part Gamma  3.46
Widget B    4.29
Widget T    4.29
Widget L    5.29
Widget Z    6.26
Widget N    6.29
Tool C      6.59
Tool D      8.29
Device N    9.29
Tool A      14.99
Widget E    17.29
Device O    21.29
Tool B      44.79
Device P    51.29

You can join your tables using standard SQL syntax. For example, you can show each product and the name of the country of origin using this SQL statement SELECT name,country FROM Product,country where product.countryoforiginid=country.countryid. The results are as follows:

Widget A    USA
Widget B    Taiwan
Widget X    China
Widget Y    China
Widget Z    Singapore
Widget R    USA
Widget S    USA
Widget T    Taiwan
Widget L    China
Widget N    China
Widget E    Singapore
Part Alpha  USA
Part Beta   USA
Part Gamma  Taiwan
Device N    China
Device O    China
Device P    Singapore
Tool A      USA
Tool B      USA
Tool C      USA
Tool D      USA

You can also filter your data using a WHERE clause. To find all products manufactured in China, you can use the following query:

SELECT name, country FROM Product, country where
product.countryoforiginid=country.countryid and country.country="China".

The result of this query is a list of all of the products made in China:

Widget X    China
Widget Y    China
Widget L    China
Widget N    China
Device N    China
Device O    China

As powerful as the command-line interface to SQLite is, sometimes it is easier to use a GUI interface to examine the database. Many applications provide this functionality. You can find a list of them on the SQLite web site at http://www.sqlite.org/cvstrac/wiki?p=ManagementTools.

Feel free to try out any or all of the applications listed on the SQLite site. These applications range in price from free to hundreds of dollars and offer a variety of capabilities including import/export from various tools and commercial databases, graphical ER modeling, SQL editors with syntax highlighting, and many other advanced features. If you are going to use SQLite for enterprise applications, it may very well be worth it to purchase one of these applications.

For developing simple iPhone applications that do not require intense database development, I prefer to use the SQLite Manager plug-in for the Firefox web browser. This free plug-in, available at the Google code web site (http://code.google.com/p/sqlite-manager/) provides you with the following features:

The plug-in is very easy to install and use. You can use the plug-in to create new tables by simply clicking the Create Table icon. You are then presented with a dialog that contains all of the data that you need to create a new table, as shown in Figure 2-7. I have populated the dialog with the fields from the ERD diagram.

Figure 2.7. Creating a table with SQLite Manager

Click the disclosure triangle next to Tables in the left-hand pane of the interface to see a list of all of the tables in the database. Selecting a table, like Product in Figure 2-8, will reveal the details of the table. You can see the SQL that was originally used to create the table, the number of fields in the table, the number of records, and detailed information on all of the columns in the table. You can also add, alter, and drop columns from this view.

You can select the Browse & Search tab at the top of the right pane to view and edit the data in the selected table, as shown in Figure 2-8.

Figure 2.8. Browsing table data with SQLite Manager

Selecting the Execute SQL tab enables you to execute arbitrary SQL statements against the database. Finally, the DB Settings tab enables you to view and edit various database settings that are normally only available via pragma statements at the command prompt.

For this project, you are going to implement a master-detail interface. As seen in the mockup, the main screen will show the entire product catalog and tapping on an item should display a screen with the details for that item. The UINavigationController is perfect for building this kind of interface. To get started, open up Xcode and create a new project using the Navigation-based application template.

This template creates a project that contains two Interface Builder xib files. The MainWindow.xib contains a NavigationController that manages the navigation of the application. The NavigationController, in turn, contains the RootViewController. You can see this by inspecting the MainWindow.xib file using Interface Builder (see Figure 2-10).

Figure 2.10. MainWindow.xib

The UINavigationController

The Navigation Controller is used to display and manage a hierarchy of View Controllers. Any time that you need to display hierarchical data, consider using the UINavigationController. The Navigation Controller manages the state of the display using a "navigation stack." View controllers that you want to display are pushed on to the navigation stack when you are ready to display them. Pressing the Back button causes the current View Controller to be popped off the navigation stack. At the bottom of the stack is the Root View Controller.

You will implement navigation in the catalog application using the UINavigationController. The diagram in Figure 2-11 shows the application mockup along with the navigation stack.

Figure 2.11. Application screens and navigation stack state

The left side shows the product catalog displayed in the UITableView, which is included in the RootViewController. Selecting a row in the TableView causes a new View Controller that you will create called the ProductDetailViewController to be pushed onto the navigation stack. You see this in the image on the right. The status of the navigation stack appears at the bottom of the figure.

Tapping the catalog button in the navigation bar at the top of the screen will cause the ProductDetailViewController to be popped from the navigation stack, thus displaying the RootViewController again. The most important thing to remember is that the UINavigationController will always display the View Controller that is at the top of the navigation stack.

You can see what makes the Navigation Controller ideal for displaying hierarchical data. As a user navigates down a hierarchy, the application pushes View Controllers onto the stack. When the user presses the Back button, the View Controllers are popped back off of the stack, navigating back up the hierarchy.

By simply creating a project based on the Navigation template, you get a lot of functionality for free. In fact, if you build and run the application, you should get something that looks like Figure 2-12.

Figure 2.12. Running the Navigation template

You have added no code at all, yet you already have a navigation bar (the blue-gray area at the top) and a table view (the lines). Now, you need to fill the table view with data.

In keeping with the preferred application architecture on the iPhone, you'll design this application by following the Model-View-Controller design pattern. You already have your view and controller; you just need a model. You need to design a model class that represents your data. The model class should also have a method that returns the number of rows in the database and provides access to the data for a specific row.

For this application, your model will be based on the Product class. The Product class will mirror the fields in the Products table in the database. Your model will be a collection of Product objects.

To implement this model, create a new Objective-C class called Product. In the header, you will add a property for each database field. The following is the code for the header:

#import <Foundation/Foundation.h>

@interface Product : NSObject {
    int ID;
    NSString* name;

NSString* manufacturer;
    NSString* details;
    float price;
    int quantity;
    NSString* countryOfOrigin;
    NSString* image;
}

@property (nonatomic) int ID;
@property (retain, nonatomic) NSString *name;
@property (retain, nonatomic) NSString *manufacturer;
@property (retain, nonatomic) NSString *details;
@property (nonatomic) float price;
@property (nonatomic) int quantity;
@property (retain, nonatomic) NSString *countryOfOrigin;
@property (retain, nonatomic) NSString *image;

@end
                                                         

You can see that you simply declare a member variable for each database field and then create a property to access each field.

The implementation for this class is even easier:

#import "Product.h"

@implementation Product
@synthesize ID;
@synthesize name;
@synthesize manufacturer;
@synthesize details;
@synthesize price;
@synthesize quantity;
@synthesize countryOfOrigin;
@synthesize image;

@end
                                                         

Here, you just synthesize all of the properties declared in the header. At this point, it is a good idea to build and verify that there are no errors in your application.

Now that you have your model object completed, you need to write the code to get the data out of the database and into your model class. It is a good idea to abstract out the database access. This gives you flexibility in the event that you want to move to a different database engine later. To do this, you'll create a database access class that talks to the database. This class will have methods to initialize the database, close the database, and most important, build and return an array of Product objects.

Before you get started on coding the database access class, you need to add the SQLite database to the Xcode project. Add the SQLite database to the project's Resources folder by right-clicking on the Resources folder and selecting Add

Figure 2.13. Adding an existing file

To create the database access class, create a new Objective-C class called DBAccess. In the header, DBAccess.h, you will need to add an import statement for sqlite3.h as you intend to use functions from the sqlite3 library in the data access class.

You'll also need to add three method signatures for the methods that you plan on implementing: getAllProducts, closeDatabase, and initializeDatabase. closeDatabase and initializeDatabase are self-explanatory. The getAllProducts method will return an array of all of the Product objects in the catalog. Because you will be referencing the Product object in this class, you need to add an import statement for Product.h.

The DBAccess.h header file should look like this:

#import <Foundation/Foundation.h>

// This includes the header for the SQLite library.
#import <sqlite3.h>
#import "Product.h"

@interface DBAccess : NSObject {


}

- (NSMutableArray*) getAllProducts;
- (void) closeDatabase;
- (void)initializeDatabase;

@end
                                                         

In the implementation of the DBAccess class, add a class-level variable to hold a reference to the database:

// Reference to the SQLite database.
sqlite3* database;

You will populate this variable in the initializeDatabase function. Then, every other function in your class will have access to the database.

Now, you'll create the init function that is used by callers to initialize instances of this class. In init, you will make an internal call to initialize the database. The init function should look like this:

-(id) init
{
//  Call super init to invoke superclass initiation code
 if ((self = [super init]))
    {
        //  set the reference to the database
        [self initializeDatabase];
    }
    return self;
}
                                                         

Your initializeDatabase function will do just that. It will go out and get the path to the database and attempt to open it. Here is the code for initializeDatabase:

// Open the database connection
- (void)initializeDatabase {

// Get the database from the application bundle.
 NSString *path = [[NSBundle mainBundle]
        pathForResource:@"catalog"
        ofType:@"db"];

    // Open the database.
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
    {
        NSLog(@"Opening Database");
    }
    else
    {
        // Call close to properly clean up
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database: '%s'.",
            sqlite3_errmsg(database));
    }
}
                                                         

You can see that you need the path to the database file. Because you put the catalog.db file in the Resources folder, the database will get deployed to the device in the main bundle of the application. There is a handy class, NSBundle, for getting information about an application bundle. The mainBundle method returns a reference to the main application bundle and the pathForResource:ofType: method returns the path to the specified file in the bundle. Because you specified the resource as catalog and the type as db, the method will return the path to catalog.db.

Next, you use the C function sqlite3_open to open a connection to the database. You pass in the path to the database and the address to a variable of type sqlite3*. The second parameter will be populated with the handle to the database. The sqlite3_open function returns an int. It will return the constant SQLITE_OK if everything went fine. If not, it will return an error code. The most common errors that you will encounter are SQLITE_ERROR, indicating that the database cannot be found and SQLITE_CANTOPEN, indicating that there is some other reason that the database file cannot be opened. The full list of error codes can be found in the sqlite3.h include file.

You make sure that you got back SQLITE_OK and then log that you are opening the database. If you get an error, you close the database and then log the error message.

Next, you'll add a method to cleanly close the connection to the database. This is as simple as calling sqlite3_close, passing in a handle to the database like this:

-(void) closeDatabase
{
    // Close the database.
    if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database: '%s'.",
            sqlite3_errmsg(database));
    }
}
                                                         

The sqlite3_close will return a value just like sqlite3_open. If the call to sqlite3_close fails, you use the sqlite3_errmsg function to get a textual error message and print it to the console.

At this point, you should try to build the application. The build fails because the SQLite functions are not found. Although you included the proper header files, the compiler doesn't know where to find the binaries for the library. You need to add the libsqlite framework to your Xcode project. Right-click on the frameworks folder, select Add Existing Framework, and select libsqlite3.0.dylib. Now the compilation should succeed. You still receive a warning that tells you that the getAllProducts method is not implemented, but you can fix that by implementing the function.

Now comes the heart of the database access class, the getAllProducts method. You will implement the getAllProducts method to return an array of Product objects that represents the records in the product catalog database. The method will allocate an NSMutableArray to hold the list of products, construct your SQL statement, execute the statement, and loop through the results, constructing a Product object for each row returned from the query.

You'll start the method by declaring and initializing the array that will be used to hold the products. You'll use an NSMutableArray because you want to be able to add Product objects to the array one by one as you retrieve rows from the database. The regular NSArray class is immutable so you cannot add items to it on-the-fly.

Here is the beginning of your method:

- (NSMutableArray*) getAllProducts
{
    //  The array of products that we will create
    NSMutableArray *products = [[[NSMutableArray alloc] init] autorelease];
                                                         

You will notice that the products array is sent the autorelease message. This ensures that the memory allocated by creating the products array will eventually be freed. It is an accepted convention in Objective-C that methods that do not contain the word "copy," or start with "new" or "alloc" return autoreleased objects.

It is important that callers of the getAllProducts method retain the returned array. If retain is not called, the object will be freed and the caller will be left with a pointer to a freed object. This will certainly cause the application to crash. You handle this by storing the array returned from getAllProducts in a property with the retain attribute set. This will cause retain to be called when the value of the property is set.

If the concepts of memory management in Objective-C are confusing, you should read Apple's Memory Management Programming Guide for Cocoa, which can be found at http://developer.apple.com/iphone/library/documentation/Cocoa/Conceptual/MemoryMgmt/MemoryMgmt.html. This is an outstanding document that is beneficial for even advanced Cocoa programmers. If you haven't already read it, take a break and read it now.

The next step in implementing getAllProducts is to declare a char* variable and populate it with your SQL statement:

//  The SQL statement that we plan on executing against the database
    const char *sql = "SELECT product.ID,product.Name, 
        Manufacturer.name,product.details,product.price,
        product.quantityonhand, country.country, 
        product.image FROM Product,Manufacturer, 
        Country where manufacturer.manufacturerid=product.manufacturerid 
        and product.countryoforiginid=country.countryid";

The following is the SQL statement in a slightly more readable form:

SELECT product.ID,
    product.Name,
    Manufacturer.name,
    product.details,
    product.price,
    product.quantityonhand,
    country.country,
    product.image
FROM Product,Manufacturer, Country
WHERE manufacturer.manufacturerid=product.manufacturerid
    AND product.countryoforiginid=country.countryid

This book assumes that the reader already knows SQL, and thus doesn't provide full details. However, I will quickly note that this SQL statement gets data out of the three tables noted in the FROM clause: Product, Manufacturer, and Country.

You can see what fields are selected from each table in the SELECT portion of the query. The form for specifying fields to select is table.field. So, you are selecting the Name field from the Product table, then the Name field from the Manufacturer table.

Finally, you set up the joins in the WHERE clause. You only want data from the manufacturer table where the manufacturerID is the same as the manufacturerID in the product table. Likewise, you only want data from the country table where the countryID is the same as the countryoforiginID in the product table. This allows you to display the actual manufacturer name and country name in the query and in the application, instead of just displaying a meaningless ID number.

I recommend writing your queries in an application such as SQLite Manager or at the command line. It is much easier to develop your query when you can run it and instantly see the results, especially as you get into more complex queries. Figure 2-14 shows the query being run in SQLite Manager. You can see that you get the desired results when this query is executed.

Figure 2.14. Testing a query using SQLite Manager

In order to run this SQL in your code, you need to create an SQLite statement object. This object will be used to execute your SQL against the database. You then prepare the SQL statement:

//  The SQLite statement object that will hold our result set
sqlite3_stmt *statement;

// Prepare the statement to compile the SQL query into byte-code
 int sqlResult = sqlite3_prepare_v2(database, sql, −1, &statement, NULL);
                                                         

The parameters for the sqlite3_prepare_v2 function are a connection to the database, your SQL statement, the maximum length of your SQL or a −1 to read up to the first null terminator, the statement handle that will be used to iterate over the results, and a pointer to the first byte after the SQL statement, or NULL which you use here.

Like the other commands that you have run against SQLite in this chapter, sqlite3_prepare_v2 returns an int, which will either be SQLITE_OK or an error code.

It should be noted that preparing the SQL statement does not actually execute the statement. The statement is not executed until you call the sqlite3_step function to begin retrieving rows.

If the result is SQLITE_OK, you step through the results one row at a time using the sqlite3_step function:

if ( sqlResult== SQLITE_OK) {
    // Step through the results - once for each row.
    while (sqlite3_step(statement) == SQLITE_ROW) {

For each row, allocate a Product object:

//  allocate a Product object to add to products array
    Product  *product = [[Product alloc] init];

Now you have to retrieve the data from the row. A group of functions called the "result set" interface is used to get the field that you are interested in. The function that you use is based on the data type contained in the column that you are trying to retrieve. The following is a list of the available functions:

const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes(sqlite3_stmt*, int iCol);
int sqlite3_column_bytes16(sqlite3_stmt*, int iCol);
double sqlite3_column_double(sqlite3_stmt*, int iCol);
int sqlite3_column_int(sqlite3_stmt*, int iCol);
sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol);
const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol);
const void *sqlite3_column_text16(sqlite3_stmt*, int iCol);
int sqlite3_column_type(sqlite3_stmt*, int iCol);
sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);

The first parameter to each function is the prepared statement. The second parameter is the index in the SQL statement of the field that you are retrieving. The index is 0-based, so to get the first field in your SQL statement, the product ID, which is an int, you would use:

sqlite3_column_int(statement, 0);

To retrieve text or strings from the database, the sqlite3_column_text function is used. I like to create char* variables to hold the strings retrieved from the database. Then, I use the ?: operator to either use the string if it is not null, or use an empty string if it is null. Here is the code to get all of the data from the database and populate your product object:

// The second parameter is the column index (0 based) in
// the result set.
char *name = (char *)sqlite3_column_text(statement, 1);
char *manufacturer = (char *)sqlite3_column_text(statement, 2);
char *details = (char *)sqlite3_column_text(statement, 3);
char *countryOfOrigin = (char *)sqlite3_column_text(statement, 6);
char *image = (char *)sqlite3_column_text(statement, 7);

//  Set all the attributes of the product
product.ID = sqlite3_column_int(statement, 0);
product.name = (name) ? [NSString stringWithUTF8String:name] : @"";
product.manufacturer = (manufacturer) ? [NSString
    stringWithUTF8String:manufacturer] : @"";
product.details = (details) ? [NSString stringWithUTF8String:details] : @"";
product.price = sqlite3_column_double(statement, 4);
product.quantity = sqlite3_column_int(statement, 5);
product.countryOfOrigin = (countryOfOrigin) ? [NSString
    stringWithUTF8String:countryOfOrigin] : @"";
product.image = (image) ? [NSString stringWithUTF8String:image] : @"";

Finally, you add the product to the products array, release the memory associated with the product object, and move on to the next row.

The product object must be released in order to avoid a memory leak. When an object is added to an NSMutableArray, it is sent the retain message. If you did not send it a release message here, the object would have a retain count of 2 and its memory would not be freed if it were removed from the array.

// Add the product to the products array
[products addObject:product];

// Release the local product object because the object is retained
// when we add it to the array
 [product release];
}

After you are finished looping through the result set, you call sqlite3_finalize to release the resources associated with the prepared statement. Then you log any errors and return your products array.

// finalize the statement to release its resources
        sqlite3_finalize(statement);
    }
    else {
        NSLog(@"Problem with the database:");
        NSLog(@"%d",sqlResult);
    }

    return products;

}

The whole database access class should look like Listing 2-1.

//
//  DBAccess.m
//  Catalog
//
//  Created by Patrick Alessi on 12/31/09.
//  Copyright 2009 __MyCompanyName__. All rights reserved.
//

#import "DBAccess.h"


@implementation DBAccess

// Reference to the SQLite database.
sqlite3* database;


-(id) init
{
    //  Call super init to invoke superclass initiation code
    if ((self = [super init]))
    {
        //  set the reference to the database
        [self initializeDatabase];
    }
    return self;
}


// Open the database connection

- (void)initializeDatabase {

    // Get the database from the application bundle.
    NSString *path = [[NSBundle mainBundle] pathForResource:@"catalog"
                                                     ofType:@"db"];

    // Open the database.
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK)
    {
        NSLog(@"Opening Database");
    }
    else
    {
        // Call close to properly clean up
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database: '%s'.",
                  sqlite3_errmsg(database));
    }
}

-(void) closeDatabase
{
    // Close the database.
    if (sqlite3_close(database) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to close database: '%s'.",
                  sqlite3_errmsg(database));
    }
}


- (NSMutableArray*) getAllProducts
{
    //  The array of products that we will create
    NSMutableArray *products = [[[NSMutableArray alloc] init] autorelease];

    //  The SQL statement that we plan on executing against the database
    const char *sql = "SELECT product.ID,product.Name, 
        Manufacturer.name,product.details,product.price,
        product.quantityonhand, country.country, 
        product.image FROM Product,Manufacturer, 
        Country where manufacturer.manufacturerid=product.manufacturerid 
        and product.countryoforiginid=country.countryid";

    //  The SQLite statement object that will hold our result set
    sqlite3_stmt *statement;

    // Prepare the statement to compile the SQL query into byte-code
    int sqlResult = sqlite3_prepare_v2(database, sql, −1, &statement, NULL);

    if ( sqlResult== SQLITE_OK) {
        // Step through the results - once for each row.
        while (sqlite3_step(statement) == SQLITE_ROW) {

//  allocate a Product object to add to products array
            Product  *product = [[Product alloc] init];

            // The second parameter is the column index (0 based) in
            // the result set.
            char *name = (char *)sqlite3_column_text(statement, 1);
            char *manufacturer = (char *)sqlite3_column_text(statement, 2);
            char *details = (char *)sqlite3_column_text(statement, 3);
            char *countryOfOrigin = (char *)sqlite3_column_text(statement, 6);
            char *image = (char *)sqlite3_column_text(statement, 7);


            //  Set all the attributes of the product
            product.ID = sqlite3_column_int(statement, 0);
            product.name = (name) ? [NSString stringWithUTF8String:name] : @"";
            product.manufacturer =
                (manufacturer) ? [NSString
                                  stringWithUTF8String:manufacturer] : @"";
            product.details = (details) ? [NSString
                                           stringWithUTF8String:details] : @"";
            product.price = sqlite3_column_double(statement, 4);
            product.quantity = sqlite3_column_int(statement, 5);
            product.countryOfOrigin =
            (countryOfOrigin) ? [NSString
                                 stringWithUTF8String:countryOfOrigin] : @"";
            product.image = (image) ? [NSString
                                       stringWithUTF8String:image] : @"";


            [products addObject:product];
            [product release];
        }

        // finalize the statement to release its resources
        sqlite3_finalize(statement);
    }
    else {
        NSLog(@"Problem with the database:");
        NSLog(@"%d",sqlResult);
    }

    return products;

}


@end

Although I am not using them in this sample, it is possible and quite common to use parameterized queries.

For example, if you wanted to create a query that returned only products made in the USA, you could use the following SQL:

SELECT Product.name, country.country
FROM country,product
WHERE countryoforiginid=countryid and country='USA'

This query is perfectly fine if you always want a list of the products made in the USA. What if you wanted to decide at runtime which countries' products to display? You would need to use a parameterized query.

The first step in parameterizing this query is to replace the data that you want to parameterize with question marks (?). So your SQL will become:

SELECT Product.name, country.country
FROM country,product
WHERE countryoforiginid=countryid and country=?

After you prepare your statement with sqlite3_prepare_v2 but before you start stepping through your results with sqlite3_step, you need to bind your parameters. Remember that preparing the statement does not actually execute it. The statement is not executed until you begin iterating over the result set using sqlite3_step.

A series of functions is used to bind parameters in a manner similar to the way that you retrieve data fields. Here are the bind functions:

int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));
int sqlite3_bind_double(sqlite3_stmt*, int, double);
int sqlite3_bind_int(sqlite3_stmt*, int, int);
int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64);
int sqlite3_bind_null(sqlite3_stmt*, int);
int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));
int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*));
int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);
int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);

You need to use the bind function that corresponds with the data type that you are binding. The first parameter in each function is the prepared statement. The second is the index (1-based) of the parameter in your SQL statement. The rest of the parameters vary based on the type that you are trying to bind. Complete documentation of the bind functions is available on the SQLite web site.

In order to bind text at runtime, you use the sqlite3_bind_text function like this:

sqlite3_bind_text (statement,1,value,−1, SQLITE_TRANSIENT);

In the previous bind statement, value is the text that you would determine at runtime. In the example, that text would be "USA" but it could be changed dynamically at runtime. That is the advantage of using parameterized queries. Of course, you could just dynamically generate your SQL each time, but parameterized queries offer the performance advantage of preparing and compiling the statement once and the statement being cached for reuse.

If you modify the sample application, or create your own SQLite application that attempts to write to the database, you will have a problem. The version of the database that you are using in the sample code is located in the application bundle, but the application bundle is read-only, so attempting to write to this database will result in an error.

To be able to write to the database, you need to make an editable copy. On the iPhone, this editable copy should be placed in the documents directory. Each application on the iPhone is "sandboxed" and has access only to its own documents directory.

The following code snippet shows how to check to see if a writable database already exists, and if not, create an editable copy.

// Create a writable copy of the default database from the bundle
// in the application Documents directory.
- (void) createEditableDatabase {
    // Check to see if editable database already exists
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains
        (NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *writableDB = [documentsDir
        stringByAppendingPathComponent:@"ContactNotes.sqlite"];
    success = [fileManager fileExistsAtPath:writableDB];

    // The editable database already exists
    if (success) return;


    // The editable database does not exist
    // Copy the default DB to the application Documents directory.
    NSString *defaultPath = [[[NSBundle mainBundle] resourcePath]
        stringByAppendingPathComponent:@"Catalog.sqlite"];
    success = [fileManager copyItemAtPath:defaultPath
        toPath:writableDB error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file:'%@'.",
            [error localizedDescription]);
    }
}

You would then need to change your database access code to call this function and then refer to the editable copy of the database instead of the bundled copy.

Now that you have the DBAccess class done, you can get on with displaying the catalog. In the RootViewController, you will implement code to retrieve the products array from the DBAccess class and display it in the TableView.

In the header, RootViewController.h, add import statements for the Product and DBAccess classes:

#import "Product.h"
#import "DBAccess.h"

Add a property and its backing instance variable to hold your products array:

@interface RootViewController : UITableViewController {
    //  Instance variable to hold products array
    NSMutableArray *products;

}

@property (retain,nonatomic) NSMutableArray* products;

Notice that the products property is declared with the retain attribute. This means that the property setter method will call retain on the object that is passed in. You must specify the retain attribute to ensure that the products array is available for use when you need it. Recall that in the DBAccess class, you called autorelease on the array that is returned from the getAllProducts method. If you did not add the retain attribute, the products array would be released on the next pass through the application event loop. Trying to access the products property after the array is released would cause the application to crash. You avoid this by having the setter call retain on the array.

In the RootViewController.m implementation class, you need to synthesize the products property below the @implementation line:

@synthesize products;

Synthesizing a property causes the compiler to generate the setter and getter methods for the property. You can alternatively define the getter, setter, or both methods yourself. The compiler will fill in the blanks by defining either of these methods if you don't.

Continuing in the RootViewController.m implementation class, you'll add code to the viewDidLoad method to get your products array from the DBAccess class:

- (void)viewDidLoad {
    [super viewDidLoad];

    //  Get the DBAccess object;
    DBAccess *dbAccess = [[DBAccess alloc] init];

    //  Get the products array from the database
    self.products = [dbAccess getAllProducts];

//  Close the database because we are finished with it
    [dbAccess closeDatabase];

    //  Release the dbAccess object to free its memory
    [dbAccess release];

}
                                                         

Next, you have to implement your TableView methods. The first thing you have to do is tell the TableView how many rows there are by implementing the numberOfRowsInSection method as you did in the previous chapter. You will get the count of items to display in the TableView from your products array:

- (NSInteger)tableView:(UITableView *)tableView
    numberOfRowsInSection:(NSInteger)section {
    return [self.products count];
}
                                                         

Finally, you have to implement cellForRowAtIndexPath to provide the TableView with the TableViewCell that corresponds with the row that the TableView is asking for. The code is similar to the example from the previous chapter. The difference is that now you get the text for the cell from the Product object. You look up the Product object using the row that the TableView is asking for as the index into the array. The following is the cellForRowAtIndexPath method:

// Customize the appearance of table view cells.
- (UITableViewCell *)tableView:(UITableView *)tableView
         cellForRowAtIndexPath:(NSIndexPath *)indexPath {

    static NSString *CellIdentifier = @"Cell";

    UITableViewCell *cell = [tableView
                             dequeueReusableCellWithIdentifier:CellIdentifier];
    if (cell == nil) {
        cell = [[[UITableViewCell alloc]
                 initWithStyle:UITableViewCellStyleDefault
                 reuseIdentifier:CellIdentifier] autorelease];
    }

    // Configure the cell.
    // Get the Product object
    Product* product = [self.products objectAtIndex:[indexPath row]];

    cell.textLabel.text = product.name;
    return cell;
}
                                                         

To set the Catalog text at the top in the Navigation Controller, double-click MainWindow.xib. Then, click on the navigation item in Interface Builder and set the Title property to Catalog.

You should now be able to build and run the application. When you run it, you should see the product catalog as in Figure 2-15. When you touch items in the catalog, nothing happens, but the application should display the details of the product that is touched. To accomplish this, the TableView function didSelectRowAtIndexPath needs to be implemented. But before you do that, you need to build the view that you will display when a user taps a product in the table.

Figure 2.15. Running the Catalog application

When a user taps a product in the table, the application should navigate to a product detail screen. Because this screen will be used with the NavigationController, it needs to be implemented using a UIViewController. In Xcode, add a new UIViewController subclass to your project called ProductDetailViewController. Make sure that you select the option "With XIB for user interface." This creates your class along with a NIB file for designing the user interface for the class.

In the ProductDetailViewController.h header, add Interface Builder outlets for the data that you want to display. You need to add an outlet for each label control like this:

IBOutlet UILabel* nameLabel;
IBOutlet UILabel* manufacturerLabel;
IBOutlet UILabel* detailsLabel;
IBOutlet UILabel* priceLabel;
IBOutlet UILabel* quantityLabel;
IBOutlet UILabel* countryLabel;

You will use the outlets in the code to link to the UI widgets created in Interface Builder. The use of Interface Builder is beyond the scope of this book. There are several good books on building user interfaces for the iPhone including iPhone SDK Programming: Developing Mobile Applications for Apple iPhone and iPod Touch by Maher Ali (Wiley, 2009).

You will transfer the data about the product that the user selected to the detail view by passing a Product object from the RootViewController to the ProductDetailViewController. Because you will be referencing the Product object in your code, you need to add an import statement for the Product class to the ProductDetailViewController header. You also need to add the method signature for the setLabelsForProduct method that will be used to receive the Product object from the RootViewController and set the text of the labels. The complete header should look like this:

#import <UIKit/UIKit.h>
#import "Product.h"

@interface ProductDetailViewController : UIViewController {

    IBOutlet UILabel* nameLabel;
    IBOutlet UILabel* manufacturerLabel;
    IBOutlet UILabel* detailsLabel;
    IBOutlet UILabel* priceLabel;
    IBOutlet UILabel* quantityLabel;
    IBOutlet UILabel* countryLabel;

}
-(void) setLabelsForProduct: (Product*) theProduct;

@end
                                                         

Now, open up the ProductDetailViewController.xib file in Interface Builder. Here you will add a series of UILabels to the interface as you designed in the mockup. Your interface should look something like Figure 2-16.

Figure 2.16. Product detail view

Next, you'll need to hook up your labels in Interface Builder to the outlets that you created in the ProductDetailViewController.h header file. Make sure that you save the header file before you try to hook up the outlets or the outlets that you created in the header will not be available in Interface Builder.

To hook up the outlets, bring up the ProductDetailViewController.xib file in Interface Builder. Select File's Owner in the Document window. Press Cmd+2 to bring up the Connections Inspector. In this window, you can see all of the class's outlets and their connections. Click and drag from the open circle on the right side of the outlet name to the control to which that outlet should be connected in the user interface. You should see the name of the control displayed on the right side of the Connections Inspector as in Figure 2-17.

Figure 2.17. Building the detail view with Interface Builder

In ProductDetailViewController.m, implement setLabelsForProduct to set the text in the labels:

-(void) setLabelsForProduct: (Product*) theProduct
{
    //  Set the text of the labels to the values passed in the Product object
    [nameLabel setText:theProduct.name];
    [manufacturerLabel setText:theProduct.manufacturer];
    [detailsLabel setText:theProduct.details];
    [priceLabel setText:[NSString stringWithFormat:@"%.2f",theProduct.price]];
    [quantityLabel setText:[NSString stringWithFormat:@"%d",
                            theProduct.quantity]];
    [countryLabel setText:theProduct.countryOfOrigin];

}
                                                         

This code accepts a Product object and uses its properties to set the text that is displayed in the interface labels.

In order to be able to navigate to your new screen, you need to add code to the RootViewController to display this screen when a user selects a product.

In the RootViewController header, you must add an import for ProductDetailViewController because you will create an instance of this class to push onto the navigation stack:

#import "ProductDetailViewController.h"

In the RootViewController implementation, add code to the tableView:didSelectRowAtIndexPath: method to instantiate a ProductDetailViewController, populate the data, and push it onto the navigation stack:

- (void)tableView:(UITableView *)tableView
    didSelectRowAtIndexPath:(NSIndexPath *)indexPath {


    // Get the product that corresponds with the touched cell
    Product* product = [self.products objectAtIndex:[indexPath row]];

    //  Initialize the detail view controller from the NIB
    ProductDetailViewController *productDetailViewController =
        [[ProductDetailViewController alloc]
            initWithNibName:@"ProductDetailViewController" bundle:nil];

    //  Set the title of the detail page
    [productDetailViewController setTitle:product.name];

    //  Push the detail controller on to the stack
    [self.navigationController
        pushViewController:productDetailViewController animated:YES];

    //  Populate the details
    [productDetailViewController setLabelsForProduct:product];

    //  release the view controller becuase it is retained by the
    //  Navigation Controller
    [productDetailViewController release];
}
                                                         

That's all there is to it. Now you should be able to build and run your application. Try tapping on an item in the catalog. The application should take you to the detail page for that item. Tapping the Catalog button in the navigation bar should take you back to the catalog. Tapping another row in the TableView should take you to the data for that item.

You now have a fully functioning catalog application! I know that it doesn't look very nice, but you'll work on that in the next chapter where you dive into customizing the UITableView.