Using SQLite with iOS

ikhoyo (Bill Donahue) works in the publishing industry on UI’s for the internet and mobile devices (like the iPhone and iPad). You can see more on my blog. All of the code for this post can be found at GitHub.

In my last post, I described how to compile your own version of SQLite on the iPhone. In that post, I briefly described the IkhoyoDatabase class. IkhoyoDatabase is an Objective C class that wraps SQLite for iOS applications. For this post, I’ll flesh out the details.

If you haven’t already, clone the ikhoyo-public repository at GitHub. If you are using Xcode 4, open the ikhoyo-public workspace in the workspaces directory.

Look in the ikhoyo-top project, which contains the UI that demonstrates all of the ikhoyo technologies. IkhoyoAppDelegate contains the startup code for the app, and it’s here that we’ll open a database, create a database table, and insert some data into the table.

One quick note. The startup code that we execute in our app delegate has to execute quickly (the app will fail to initialize on the device if this takes too long). But creating and populating a database table is quite slow, right? We solve this by wrapping our code in a dispatch_async block, which executes asynchronously under another thread. This is a handy trick in many scenarios, but is especially important in the app delegate startup code.

The data we will be using comes from Socrata. Socrata is a very useful source of government (and other) data. It has a very easy to use and open REST API. We’ll use another ikhoyo class (IkhoyoSocrata) to get the data. IkhoyoSocrata is in the ikhoyo-socrata project, and wraps the IkhoyoURLManager class, which is in ikhoyo-net. IkhoyoURLManager is a complete solution for getting (or posting) data on the internet in a variety of ways. We’ll talk more about IkhoyoSocrata and IkhoyoURLManager in a future post.

Let’s get back to getting some data from Socrata. First look in the application:didFinishLaunchingWithOptions method (in IkhoyoAppDelegate). Here is the relevant code:

We get the document directory for this app and open an SQLite database called ikhoyo.sqlite. After the database is opened, we call finishInit. finishInit gets the data from the n5m4-mism table at Socrata. This particular table contains information about nominations and appointments for the White House. (Socrata contains all kinds of interesting data, and it’s kept up to date.)

Below, we get the data with the IkhoyoSocrata get method (in the ikhoyo-socrata project). Socrata returns data as either xml or json. We request json, which we parse with the handy objectFromJSONString method. This is from the JSONKit project, a very fast and lightweight JSON parser. We include JSONKit in the ikhoyo-jsonkit project in our workspace.

The data returned by Socrata contains meta data that describes the columns for the data, as well as the data itself. We use this information to construct a CREATE TABLE statement that we’ll use to create our database table.

After the CREATE TABLE statement is constructed, we use this code to DROP and CREATE a new database table::

First, a few quick notes about IkhoyoDatabase that you may remember from my last post. We compile SQLite in single threaded mode, which is optimal for most things. The only caveat is that we must execute all database operations on the same thread. IkhoyoDatabase does this for you automatically. All database operations execute on a low priority thread, so the responsiveness of your app is not affected. Each method has a block associated with it that gets called on the main thread when the operation completes.

The execOnDatabaseThread method is another way to use IkhoyoDatabase. This method executes the given block on the database thread. In this case, we need to drop a table, create a new one, and then insert a bunch of rows into the table. execOnDatabaseThread allows us to do all these operations sequentially on the database thread without having to write a bunch of blocks for each separate operation. For complicated operations or transactions, this method is the preferred one to use.

The data itself is loaded in loadTable. Here we construct an INSERT statement for each row, and insert the rows in the table. Here is the code:

Again, execFromDatabaseThread tells IkhoyoDatabase that we are already on the database thread, so the statement can get executed sequentially. The return value is either nil (success) or an instance of IkhoyoError (failure).

Now let’s move to querying this table and displaying it in our UI. The data is displayed in the SocrataTableViewController class (in ikhoyo-top). After the table is loaded, a named notification called IkhoyoSocrataReady is sent. This is observed in the SocrataTableViewController class. When the table is loaded, the onReady method will get executed. Look at the onReady method in SocrataTableViewController:

Here we are selecting the name column from the table we just created (for simplicity we are only selecting one column). The query method accepts three parameters: the query itself, a class name that will hold the results, and a block that gets executed on the main thread when the query completes. The second parameter (the class name) is the name of a class that we create to hold the results. In this case the class name we use is Socrata. Here is what Socrata looks like:

Notice that there is one property in Socrata called name. This is the same column we are selecting in our query statement. The IkhoyoDatabase query method use key-value coding to populate instances of Socrata for each row in the result. The Socrata class needs a matching property for each column returned by the query. The only requirement imposed on us is that the property types in the Socrata class need to be the same as in the database. The name property is a string, hence the type, NSString. If it were a number (real or integer), we would use NSNumber.

The block that gets called when the operation completes is passed an NSArray of Socrata instances containing the results (or an IkhoyoError instance if it failed). We take each name and put it on our rows array that the table view uses.

If you run this workspace and select Socrata Table from the master view on the left, it will display the names of the nominations and appointments for the White House.

That’s it for now. In my next post, I’ll talk about the IkhoyoURLManager class, which is a complete solution for getting and posting data on the internet.