iPhone SQLite Database Basics - Part 2

After writing the article on iPhone SQLite Database Basics, I received lots of followup questions and comments, some of which I will discuss here.

SQLite Database Creation

In my first article I wrote that the easiest way to work with SQLite databases on the mac was the SQLite extension for Firefox.

Since then, my attention has been brought to Menial’s Base application. This is the application that I now use for all of my SQLite work. It is super fast, and has an interface that is extremely easy to use. It also has a lot of nice touches that make it more Mac-like than using a Firefox extension. If you’re doing SQLite work on the Mac, I strongly recommend that you check it out.

Making a Writable Copy of the Database

In the first part of this tutorial, I mentioned that if you needed to write to your database you needed to copy it to a location that you could write to.

A couple of people have asked how to do this. To answer that, I first want to talk a little bit about how to control SQLite access in Cocoa applications. There are really three ways to do this:

1. Have DB access code in each class that needs it (bad)
2. Have you application delegate handle all DB access for you (better)
3. Use a singleton object for your DB access (best)

For more about the tradeoffs between application delegate usage and singleton objects see this post from Cocoa with Love.

If you are using the singleton pattern, then you can make a writable copy of your database in your singleton object’s allocation method, using something like this:

1. -(NSString*)createWriteableDB
2. {
3. // See if writeable database already exists
4. NSFileManager *fileManager = [NSFileManager defaultManager];
5. NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
6. NSString *documentsDirectory = [paths objectAtIndex:0];




7. NSString *readOnlyDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:self.databaseFileName];
8. NSString *writableDBPath = [[documentsDirectory stringByAppendingPathComponent:self.databaseFileName] retain];




9. // First time install
10. if(![fileManager fileExistsAtPath:writableDBPath])
11. {
12. if(![fileManager copyItemAtPath:readOnlyDBPath toPath:writableDBPath error:&self.lastError])
13. {
14. NSAssert1(0, @”Failed to create writable database file with message ‘%@’.”, [self.lastError localizedDescription]);
15. return nil;
16. }
17. }




18. return [writableDBPath autorelease];
19. }

A couple of important things to note here:

If you have a very large database and the things that you need to allow the user to update, (i.e. bookmarks, or annotations) are small in comparison, it may be better for you to have two databases files, one that contains the data that won’t change, and the other that contains the data used for annotations. This way you won’t have two large copies of the database on the user’s device.

This will of course have a large impact on your query design, and some database purists may be lining up to lynch me, but as I mentioned in the first article, there are some SQLite considerations that are unique to mobile devices like the iPhone.

Another thing to consider is versioning. You need to be very careful how you handle application updates that change the database structure and/or content when you are allowing the user to write data (that presumably they want to persist between updates).

One way to handle this is to simply have a table in your writable database called “Version” which contains one row and one column with the version number of the database schema.

Then in the above code you can add some logic to transfer the data from a pervious schema version to the new writable database file.

Third Party SQLite Wrappers

There are lots of SQLite wrappers available for iPhone development. I’ve never used any so I can’t say if any one is better than another. If the idea of using c-based SQLite calls makes you anxious, you may want to check them out.

Security

Someone suggested that I point out that SQLite databases on the iPhone aren’t really secure. I’m not a database security expert, but that statement is true of databases on any platform. The database is only as secure as the device itself. If someone gets ahold of your iPhone, they can most likely get the data off of it.