ikhoyo (Bill Donahue) works in the publishing industry on UI development for the web and mobile devices, like the iPhone and iPad. You can read more on my blog. I also have lot’s of open source iOS software available at GitHub.
A version of the SQLite database is integrated into iOS. For many apps, the version that exists isn’t sufficient. The most common reason is the existing version does not support full text search, a very powerful feature of SQLite. But there are other reasons, which include:
- Performance improvements.
- Threading concerns.
- Concurrent operation handling.
- Using a more up to date version of SQLite.
I’ll show you how to compile your own version of SQLite with recommendations on what compilation options are important, which options I use, and why. I’ll also introduce you to a static library that simplifies SQLite programming. All of the source code for this article can be found here. You can also get the SQLite source code directly here.
After you’ve cloned the ikhoyo-public repo, open the ikhoyo-public workspace in the workspaces directory (if you’re using Xcode 4). Then look in the ikhoyo-sqlite project. The three files of interest are:
Look in sqlite3.c. This is where we specify the compilation options for our customized version of SQLite. Detailed information about all the options can be found here. Here’s a peek inside sqlite.c:
The two options we are concerned with are SQLITE_THREADSAFE and SQLITE_ENABLE_FTS4. SQLITE_THREADSAFE specifies the threading model used by SQLite. The values can be 0, 1, or 2. 1 and 2 are multithreaded modes, and 0 specifies single threaded. There are many discussions about the pros and cons of multithreaded vs. single threaded operation in SQLite. After much research, I concluded that single threaded mode is the simplest choice, and the best choice, for the following reasons:
- Maximum performance. There is no mutex handling code compiled in SQLite, so performance is increased.
- Multihreading was tacked onto SQLite at a late stage in it’s development, and isn’t optimal. In other words, you won’t get much bang for the buck using multithreading (and may even suffer performance degradation in some cases).
- We’ll be using SQLite on a personal device, so even if there is a performance boost due to multithreading, it won’t be seen on a device such as the iPhone or iPad.
- Using SQLite in multithreaded mode introduces several complications at the programming level. We’ll have to handle SQLITE_BUSY and SQLITE_LOCKED return codes from database operations.
- We don’t want ANY database operations executing on the main thread. Database operations are slow, and under no circumstance do we want them to execute on the main thread. This would make the UI unresponsive, resulting in a very bad user experience.
The only caveat to using SQLite in single threaded mode is that we have to make sure all our requests go through a single thread. This is easy to do in iOS, and I’ve done this for you with the supplementary classes included in the ikhoyo-sqlite project. All database operations are executed under a low priority thread that we create, so there is no impact on the responsiveness of your app. I’ll be discussing these classes briefly below, and in more detail in a future post.
The other compilation option we include is SQLITE_ENABLE_FTS4 (or the equivalent SQLITE_ENABLE_FTS3). This enables full text search, a very powerful feature of SQLite.
That’s all there is to it. No special compiler flags or anything like that. Build this project, and it’s ready to go.
As I mentioned, included in the ikhoyo-sqlite project are some classes that make it easy to interface with SQLite. The classes handle all the threading issues, and greatly simplify using SQLite in your apps. iOS 4 or higher is required, since blocks are used heavily. I’ll make a few comments now, and expect more details in a future post. Here is a peek inside the project:
The only class you have to use is IkhoyoDatabase. The IkhoyoThread class is used to handle the threading, and can be used standalone. IkhoyoDatabase contains all the methods you’ll need for operating on the database, including opening, querying, updating, and closing the database. Since all operations are executed on a separate thread, most of the methods accept a block as the last parameter. The block gets called on the main thread when the operation completes. Here is an example of opening a database:
Many thanks. I’ll be showing you more about IkhoyoDatabase in my next post. You can read about other open source iOS software from ikhoyo here.
iTunes 10.3 is now 
Apple has added the 