Transactions

I’ve become a big fan of SQLite on the iPhone. One nice feature of SQLite is its transaction support; this lets you commit application state changes to disk in an “all-or-nothing” manner. To use transactions effectively, however, you may need to share your DB connections; I provide a simple class for that purpose.

Background

When I first integrated SQLite into my app, my first instinct was to have each object create – with sqlite3_open() – and destroy its own DB connections. This was simple, and took advantage of SQLite’s multiple-connection feature. Unfortunately, this didn’t work very well once I introduced transactions that involved writes from multiple objects.

Transactions

A few points about SQLite transactions:

• Transactions are connection-specific
• Statements outside of transactions are wrapped in implicit transactions
• SQLite only permits one transaction to be open at a time

These points naturally cause difficulty if one is attempting to perform writes from two or more objects, each with its own DB connection. In fact, something like the following chain of events can occur:

• Object A opens a transaction on its DB connection cA
• Object A executes statements sA0 … sAN on its DB connection cA
• Object A passes a message to object B
• Object B executes statement sB0 on its DB connection cB
• Statement sB0 fails because it cannot run as part of object A’s transaction (different connection) and cannot open a new transaction (SQLite permits only one transaction at a time)

In this case, the failed sB0 statement may continue to cause problems, even after object A (presumably) rolls back its transaction; SQLite seems not to like unresolved statements of this sort.

Sharing

My solution was to open a single DB connection in the application delegate, and to have all objects share this connection. Since I didn’t like the idea of passing around naked pointers (even for something owned by the application delegate) I created a little wrapper class, shown below.

//
//  DbWrapper.h
//
//  Created by Michael Heyeck on 7/30/09.
//

#import <Foundation/Foundation.h>
#import	<sqlite3.h>


@interface DbWrapper : NSObject {
	sqlite3*	database;
}

- (id)initWithPath:(NSString*)path;
- (sqlite3*)db;

@end

//
//  DbWrapper.m
//
//  Created by Michael Heyeck on 7/30/09.
//

#import "DbWrapper.h"


@implementation DbWrapper

// New D.I.
- (id)initWithPath:(NSString*)path
{
	if (self = [super init])
	{
		if (!path) path = @"";
		if (sqlite3_open([path UTF8String], &database) != SQLITE_OK)
		{
			if (database) sqlite3_close(database);
			database = NULL;
		}
	}
	return self;
}


// Parent's D.I.
- (id)init
{
	return (self = [self initWithPath:nil]);
}


- (sqlite3*)db
{
	return database;
}


- (void)dealloc
{
	if (database) sqlite3_close(database);
	
	[super dealloc];
}

@end

Perhaps this class will be useful to you.