26. SQLite

Once upon a time, SQL was created to access data in relational databases. While powerful, SQL required a database infrastructure. One day, Richard Hipp decided to create a library that would store data in tables and store those tables in a file instead of a relational database. With this library, you could use SQL commands to fetch data from the tables without requiring a database server process. You could also use SQL commands to insert, update, and delete rows of data. Dr. Hipp released the code for this library into the public domain and called it SQLite. The SQLite libraries are part of the iPhone OS.

SQLite is a nifty C library. It has great performance and reliability. Both the source for the library and the data files it creates are portable to a large number of platforms.

In the Homepwner application, you stored your data using an archive. Archives are very easy to use and support arbitrary object models. The downside of an archive is that it is read and written in its entirety. With SQLite, you can fetch only the data you need. You can also update individual rows of data. Thus, if you are dealing with a lot of data, using SQLite can radically improve the speed and memory footprint of your application. If you are dealing with a small amount of data, say, less than a thousand rows, archiving is all you need.

Creating the Nayshunz Application

This chapter will show you how to open a SQLite file and fetch data from it. It is done using a C API, so you will also spend some time converting C strings into NSString objects and back again. You will create an application that displays the names of nations stored in a SQLite database, as shown in Figure 26.1.

Figure 26.1. Nayshunz

The first step is to create a SQLite database file on the desktop and copy it into the resources of the application. The first time the application runs, it will copy that starting database to the Documents directory. In this chapter, you are only reading from a database, so making a copy of the database is not strictly necessary. However, this step is crucial if you want to edit a database.

The second step is to create a table view with sections using a common data structure: a tree of dictionaries and arrays. The tree for Nayshunz is shown in Figure 26.2.

Figure 26.2. Tree

In Xcode, create a new Window-based Application called Nayshunz. Open NayshunzAppDelegate.h and add the following:

Open MainWindow.xib. Drop a search bar and a table view onto the window. Leave room under the table view for the keyboard as shown in Figure 26.3.

Figure 26.3. Layout

Make the connections shown in Figure 26.4. Your instance of NayshunzAppDelegate will be the delegate of the UISearchBar. It will also be the dataSource of the UITableView. Set the pointers searchBar and countryTable to point to the UISearchBar and the UITableView, respectively.

Figure 26.4. Connections

Creating the Database

Put the countries.sql file (downloaded from http://www.bignerdranch.com/solutions/iPhoneProgramming.zip) on your desktop. (Take a look at the file in a text editor; it is a collection of SQL commands.) Start up Terminal. To create the new SQLite database file, issue these commands:

$ cd ~/Desktop
$ sqlite3 countries.db < countries.sql

If you are familiar with SQL, you can now use the sqlite3 command line to access the database from Terminal. Here’s an example:

(If you’d like more help with SQL, check out Joe Celko’s SQL for Smarties.)

From Finder, drag countries.db into your project under the Resources group. When the sheet appears, check the box that says Copy items into destination group’s folder.

You will also need libsqlite3.0.dylib added to your project. Add it the same way you add a framework – from the Targets menu item in the project window.

Fetching Data

Now you are going fetch the rows, but the results will not appear in the table view yet. Open NayshunzAppDelegate.m and make it look like this:

Build and run the application. Watch the log. You will see a list of countries when the user types characters into the search bar.

Making and Using the Tree

Now that you are successfully getting the data, you need to put it into a tree so that you can access it with the table view data source methods.

In NayshunzAppDelegate.m, extend the searchBar:textDidChange: delegate method:

Now the table view data source methods must use the tree. Add (or replace) these methods in NayshunzAppDelegate.m:

Build and run the application.

Challenge: Fetching More Data

There is lots more data in the database. When someone selects a country, bring up another view with details about the selected country.

Challenge: Custom Objects

In this chapter, the information retrieved from the database was stored in the collection objects NSArray and NSDictionary. Try storing the data as an array of custom objects. The type of these objects will be called Nation, and you will write the implementation for it. It should have, at the very least, an NSString instance variable that stores the name of the nation.