iPhone in Action

SKDatabase 1.1: A SQLite Library for the iPhone

On pp.303-313 of iPhone in Action we talk about SQLite. In the middle of that is an example of a very rudimentary database class for the iPhone, called SKDatabase. It takes the somewhat nonintuitive functions that are built into SQLite3 and turns them into slightly more intuitive methods.

Since the publication of that book, I've dramatically expanded the library for use in my own iPhone Store Apps. I'd always planned to publish it here as another "SDK Class Recipe", but then something new came along: iPhone OS 3.0 and with it support for Core Data, a more robust and better integrated way to store and change data.

Because of the release of  Core Data for the iPhone, I've opted to abandon new work on my SQLite library. However, rather than have it go entirely to waste, I'm publishing it here, for your usage. Understand that even more than normal, this is offered with no guarantees or warranties.

I haven't really dug through the library to polish it up. I also don't believe that it row lookup methods work correctly if one or more of the columns is actually a calculation (like MAX, SUM, COUNT). Finally, I haven't made sure that every parallel method exists.

I will say that it seems stable and that I did use it in a program released through the App Store, so it's probably a fine starting place if you want to do SQLite work of your own.

Here's a zip file of the library:
Download SKDatabase

Core Data v. SQLite

Before I get into the API for my SQLite Library, I want to briefly address the question of whether you should be using Core Data or SQLite.

Core Data is broadly a tool that you can use to manage, store, and recover data. Most of the time you don't set any requirements as to how the back end of Core Data works. It could be a database, or a XML file, or a plain text file. As part of the standard Apple programming paradigm, Core Data comes with an attractive GUI which you can use to set up your data structures. Then you can use some standard APIs to access and retrieve your data.

I generally think that Core Data is scads more complex than SQLite. There are all sorts of abstractions with weird names. I don't find it very programmer friendly to learn. However, learning should be a one-time process, so if you're planning to spend a lot of time programming on iPhones you can amortize your educational costs.

The benefit is that I expect Core Data to be more robust and better able to take advantage of the specific hardware of the iPhone than SQLite. That's why I'm moving over, despite 20+ years of SQL experience going back to CS186 at Berkeley.

You'll need to make that same trade-off calculation when deciding what to use in your own program. If you want to choose the easy route, the quick route, or the one-time use route, then SQLite should still be viable. Use my library here. However if you have the time to spend and want to become an iPhone guru, start learning Core Data instead (and I hope to offer some articles to make that process easier sometime in the future on this blog).

The SQLite3 Library API

If you've stuck around, it's probably because you're planning to use my API for SQLite.Here's all the  methods that are available:

init Methods

- (id)initWithFile:(NSString *)dbFile;
- (id)initWithDynamicFile:(NSString *)dbFile;

There are two ways to create a database object. Use "initWithFile:" if you're just reading from a database and "initWithDynamicFile:" if you are reading and writing. The latter function copies your database from the main bundle to the doc folder if it's not there already, as you can't modify objects in the main bundle folder without corrupting your program's checksum (see iPhone in Action pp.299-300).

It also sets a flag that says you're allowed to make changes to the database. If you try and make a change to a non-dynamic database, your program will crash with a "Tried to use a dynamic function on a static database" error.

Lookup Methods

- (id)lookupColForSQL:(NSString *)sql;
- (NSDictionary *)lookupRowForSQL:(NSString *)sql;
- (NSArray *)lookupAllForSQL:(NSString *)sql;

There are three simple methods that you can use to lookup data using this library. They could be named better. You can lookupCol, which just returns one element of data; you can lookupRow, for multiple columns in the same row; or you can lookupAll, which returns multiple rows which each might contain multiple elements of data.

Note that multiple rows are returned as an NSArray which contains one or more NSDictionaries. The keys for each element in the NSDictionary are set to the database column name of that datum. The type of each element is set as best the library can determine (which is an improvement over the library in the book, where you had to use different method calls to retrieve different sorts of data, but still isn't as robust as it should be; problems figuring out type are why MAX, COUNT, and SUM don't work as part of these larger calls). Expect to see NSNumbers set to floats, ints, or booleans, and NSStrings.

In these and future methods, SQL should be the plain text string of a complete SQL call. It's automatically turned into a prepared statement as part of the lookup method, just as was the case in our simpler example on pp.307-308 of iPhone in Action.

Calculated Lookup Methods

- (int)lookupCountWhere:(NSString *)where forTable:(NSString *)table;
- (int)lookupMax:(NSString *)key Where:(NSString *)where forTable:(NSString *)table;
- (int)lookupSum:(NSString *)key Where:(NSString *)where forTable:(NSString *)table;

If you want to use calculated lookups, the library supports these three single-lookup method calls. For a polished library, I would have offered a few more iterations of these method calls, but for now you got what I used; it'd be easy to expand them.

Rather than requesting full SQL input, these methods put together a complete SQL statement for you. In each case, WHERE is what would go after the WHERE clause of an SQL statement (what Core Data would call a predicate). Count works differently from the rest because it does a COUNT(*) where the others do MAX($key) and SUM($key).

Insert Methods

- (void)insertDictionary:(NSDictionary *)dbData forTable:(NSString *)table;

If you use this method to hand the database class a dictionary where the keys match columns in your database table, it will insert one row of data appropriately.

There's also an insertArray:forTable: method in the code which instead lets you insert an NSArray full of NSDictionary with "key" and "value" entries. I ultimately decided it wasn't as useful as insertDictionary:forTable:, and thus it never even got tested. I've left it in case it's more useful to you, but haven't included it in my API listing because of its entirely untested nature.

Update Methods

- (void)updateDictionary:(NSDictionary *)dbData forTable:(NSString *)table;
- (void)updateDictionary:(NSDictionary *)dbData forTable:(NSString *)table where:(NSString *)where;
- (void)updateSQL:(NSString *)sql forTable:(NSString *)table;

There are three options here. updateSQL:forTable: just allows you to enter your entire update SQL statement. The forTable: argument is redundant, but is included so that the database protocol will work (on which, more shortly). updateDictionary:forTable:where: replaces all the keyed elements in the NSDictionary based on the where statement. Usually you'll set the where statement to define a singular row in your database. updateDictionary:forTable: does it for everything in the table.

Of these three, I used updateSQL:forTable: the most. It's not abstracted like the others, but it was sure easier to write an SQL statement than put together a whole NSDictionary each time. That tends to be a constant war in OOP: simplicity versus abstraction.

There are also updateArray: methods, and I haven't included them in the listing for the same reason as the insertArray: methods: they're totally untested, and I suspect not as useful.

Delete Methods

- (void)deleteWhere:(NSString *)where forTable:(NSString *)table;

There's just one way to delete: you hand off a where predicate, and appropriate rows are removed from the table.

Internal Methods

- (BOOL)runDynamicSQL:(NSString *)sql forTable:(NSString *)table;
- (sqlite3_stmt *)prepare:(NSString *)sql;
- (void)close;

These methods are used only internally, and should be of no interest to you. prepare: is used by just about every other method to create a prepared statement. runDynamicSQL: is used by all the update, insert, and delete method calls, mainly to get all of the SQLite3 and dynamic-testing logic in one place. close is used by dealloc; it shuts down the database.

The Database Delegate Protocol

@protocol SKDatabaseDelegate <NSObject>
@optional
- (void)databaseTableWasUpdated:(NSString *)table;
@end

There is a database protocol which has one optional method: you can be alerted whenever a database table is updated, which is very useful if you've got a UITableview built on the database table in question.

Conclusion

That's the entirety of my SQLite3 library, warts and all. It should generally work, aside from the select issues I mention. If there are more bugs, however, I leave them to you, as I'm no longer actively working on SQLite code.

I hope you find it useful!

Administrivia: Links of Note

We appreciate all the readers we get to this blog from various searches. It's always interesting to see what articles searchers come to, and it's helped to decide which new articles we write. However, we also hope that many of you will stick around, to get more iPhone development advice every week, building directly on what we wrote for iPhone in Action. As such, we'd like to point out a few links of note.

First, if you use an RSS reader, you can plug in our own RSS feed to get notified whenever we write something new:
http://feeds.feedburner.com/typepad/iphone_in_action

Second, we tend to twitter whenever we write a major new article. Just follow @iphoneinaction and you'll get the newest info every week (and perhaps some shorter thought on iPhone programming, if they strike us). We've got 613 followers to date. Help us break 1000!

If you've found this blog and the book itself useful, we also encourage you to write a review of the book at your favorite online book site. Our Amazon page is here:
http://www.amazon.com/iPhone-Action-Introduction-Web-Development/dp/193398886X/ref=sr_1_1?ie=UTF8&s=books&qid=1248115301&sr=8-1

Thanks for your support for the book and your interest in this blog, which are what keep us writing about our new iPhone topics (almost) every week!

Beautiful Section Heads

In the last couple of months, I've talked a lot about tables. In Beautiful Tables, I discussed how to make your tables look more attractive and in Table Tricks for iPhone OS 3.0, I updated some of that information for 3.0.

However, neither article talked about how to make your table's section heads look more attractive, and if you've got a beautifully tinted navigation bar, a carefully colored table, and multi-view cells, that gray section header will probably stand out like a sore thumb. So, how do you make it more attractive too?

Rewriting tableView:viewForHeaderInSection:

Sadly,there isn't a simple tintColor property to set for your section header. (I think there should be, but I suspect that Apple is generally trying to discourage uses of colors in tables.) Instead you have to replace the tableView:viewForHeaderInSection: method. In doing so, you must create a view that both displays your section text and does so in a color that you want.

Here's an example:

- (UIView *) tableView:(UITableView *)tableView
  viewForHeaderInSection:(NSInteger)section {

   UIView* customView = [[[UIView alloc]
     initWithFrame:CGRectMake(10.0, 0.0, 300.0, 44.0)]
       autorelease];
   customView.backgroundColor =
     [UIColor colorWithRed:.6 green:.6 blue:1 alpha:.9];

   UILabel * headerLabel = [[[UILabel alloc]
     initWithFrame:CGRectZero] autorelease];
   headerLabel.backgroundColor = [UIColor clearColor];
   headerLabel.opaque = NO;
   headerLabel.textColor = [UIColor whiteColor];
   headerLabel.font = [UIFont boldSystemFontOfSize:16];
   headerLabel.frame = CGRectMake(0,-11, 320.0, 44.0);
   headerLabel.textAlignment = UITextAlignmentCenter;
   headerLabel.text = [NSString stringWithString:@"Your Text"];

   [customView addSubview:headerLabel];

   return customView;
}


This is a pretty simple three-step process. First, I create the actual view for the section, drawing its background color appropriately; then I add a label with the correct text, positioned just right, and make that a subview of the section view; and finally I return the section view.

Voila! You now have a colorful section head that should match the rest of a color table.

(I'll add one more comment: note that I decided to make the alpha transparency of my section header's background color 90%. That's because of the 3.0 feature where a header sticks to the top of a table when it scrolls past. I like the three-dimensional effect that produced when you can just barely see a bit of the table beneath your stuck section head; your mileage may vary.)

Dashcode: setCurrentViewWithTransition

I haven't offered much discussion of Dashcode or web development in this blog. I still remain convinced that it's a very viable method of iPhone app release, but it's not where my personal attention has been focused these last several months, and thus I've had much more opportunity to write about new SDK code than web extensions.

However, yesterday I got a Dashcode query, and I wanted to share the results.

Dashcode, of course, adds tons of functionality for web app developers, to the point where you can match the look-and-feel of a lot of pure SDK coding. We showed some of this off on on pp.137-138 of iPhone in Action where we explained how to use setCurrentView to allow a simple sliding transition. As is our wont, we also gave you a pointer to a more complex function, setCurrentViewWithTransition, even though our books didn't have space for that additional functionality. Today I'm going to explain that a bit more, since documentation of it online is lacking in any examples.

A setCurrentViewWithTransition Example

setCurrentViewWithTransition allows you to do change your view in Dashcode with much fancier transitions than simple slides. In order to use it, you must first create a "Transition" object, then pass that as an additional (second) argument to the setCurrentViewWithTransition method.

To create a transition, you'll send it three arguments: type, duration, and timing. Both type and timing are constants that can be found here, in the Dashcode documents under the "Stack Layout Transitions" section.

The type constant in particular lets you do a lot of cool stuff, like DISSOLVE, SWAP, and REVOLVE.

Here's an example of actually creating a transition, then applying it to setCurrentViewWithTransition. It's a variant of a function found in Listing 7.3, our Dashcode tab bar.

function gotoPageTwoFor(event)
{
  newTransition = new Transition(Transition.FLIP_TYPE,1,Transition.EASE_TIMING);
  document.getElementById('stackLayout').object.setCurrentViewWithTransition

    ('view2',newTransition,false);
}

That's it, a simple two-step process of creating the transition, then sending it as the second argument of setCurrentViewWithTransition.

Book Update: As of the current version of Dashcode, both setCurrentView and setCurrentViewWithTransition take an additional argument. The correct arg list for setCurrentView (updating what you see on p.137) is "View, Reverse, ToTop" while the correct arg list for setCurrentViewWithTransition is similarly "View, Transition, Reverse, ToTop".

The new "ToTop" variable is a boolean that says whether to scroll the new view to the top. If you omit it (as I did in the above example, to maintain clear consistency with the book example), Dashcode will do the right thing, but it's probably better to start using the updated function call.