CHAPTER 13: Basic Data Persistence

467

The syntax that follows the bind statements may seem a little odd, since we’re doing an insert. When using bind variables, the same syntax is used for both queries and updates. If the SQL string had an SQL query, rather than an update, we would need to call sqlite3_step() multiple times until it returned SQLITE_DONE. Since this was an update, we call it only once.

The SQLite3 Application

We’ve covered the basics, so let’s see how this would work in practice. We’re going to retrofit our Persistence application again, this time storing its data using SQLite3. We’ll use a single table and store the field values in four different rows of that table. We’ll give each row a row number that corresponds to its field, so for example, the value from field1 will get stored in the table with a row number of 1. Let’s get started.

Linking to the SQLite3 Library

SQLite 3 is accessed through a procedural API that provides interfaces to a number of C function calls. To use this API, we’ll need to link our application to a dynamic library called libsqlite3.dylib, located in /usr/lib on both Mac OS X and iOS. The process of linking a dynamic library into your project is exactly the same as that of linking in a framework.

Use the Finder to make a copy of your last Persistence project directory, and then open the new copy’s .xcodeproj file. Select the Persistence item at the very top of the project navigator’s list (leftmost pane), and then select Persistence from the TARGETS section in the main area (middle pane; see Figure 13–4). Be careful that you have selected Persistence from the TARGETS section, and not from the PROJECT section.

Figure 13–4. Selecting the Persistence project in the project navigator, then selecting the Persistence target, and finally, selecting the Build Phases tab

With the Persistence target selected, click the Build Phases tab in the rightmost pane. You’ll see a list of items, initially all collapsed, which represent the various steps Xcode goes through to build the application. Expand the item labeled Link Binary With Libraries. You’ll see the standard frameworks that our application is set up to link with by default: UIKit.framework, Foundation.framework, and CoreGraphics.framework.

www.it-ebooks.info

468	CHAPTER 13: Basic Data Persistence

Now, let’s add the SQLite3 library to our project. Click the + button at the bottom of the linked frameworks list, and you’ll be presented with a sheet that lists all available frameworks and libraries. Find libsqlite3.dylib in the list (or use the handy search field), and click the Add button. Note that there may be several other entries in that directory that start with libsqlite3. Be sure you select libsqlite3.dylib. It is an alias that always points to the latest version of the SQLite3 library. Adding this to the project puts it at the top level of the project navigator. For the sake of keeping things organized, you may want to drag it to the project’s Frameworks folder.

Modifying the Persistence View Controller

Now, it’s time to change things around again. This time, we’ll replace the NSCoding code with its SQLite equivalent. Once again, we’ll change the file name so that we won’t be using the same file that we used in the previous version, and the file name properly reflects the type of data it holds. Then we’re going to change the methods that save and load the data.

Select BIDViewController.m, and make the following changes:

#import "BIDViewController.h" #import "BIDFourLines.h"

#import <sqlite3.h>

#define kFilename		@"archive.plist"
#define	kDataKey	@"Data"
#define	kFilename	@"data.sqlite3"

@implementation BIDViewController @synthesize field1;

@synthesize field2; @synthesize field3; @synthesize field4;

- (NSString *)dataFilePath {

NSArray *paths = NSSearchPathForDirectoriesInDomains( NSDocumentDirectory, NSUserDomainMask, YES);

NSString *documentsDirectory = [paths objectAtIndex:0];

return [documentsDirectory stringByAppendingPathComponent:kFilename];

}

#pragma mark -

- (void)viewDidLoad { [super viewDidLoad];

// Do any additional setup after loading the view, typically from a nib. NSString *filePath = [self dataFilePath];

if ([[NSFileManager defaultManager] fileExistsAtPath:filePath])

{

NSData *data = [[NSMutableData alloc] initWithContentsOfFile:[self dataFilePath]];

NSKeyedUnarchiver *unarchiver =

[[NSKeyedUnarchiver alloc] initForReadingWithData:data]; BIDFourLines *fourLines = [unarchiver decodeObjectForKey:kDataKey]; [unarchiver finishDecoding];

www.it-ebooks.info

CHAPTER 13: Basic Data Persistence

469

field1.text = fourLines.field1; field2.text = fourLines.field2; field3.text = fourLines.field3; field4.text = fourLines.field4;

}

sqlite3 *database;

if (sqlite3_open([[self dataFilePath] UTF8String], &database) != SQLITE_OK) {

sqlite3_close(database);

NSAssert(0, @"Failed to open database");

}

//Useful C trivia: If two inline strings are separated by nothing

//but whitespace (including line breaks), they are concatenated into

//a single string:

NSString *createSQL = @"CREATE TABLE IF NOT EXISTS FIELDS "

"(ROW INTEGER PRIMARY KEY, FIELD_DATA TEXT);";

char *errorMsg;

if (sqlite3_exec (database, [createSQL UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) { sqlite3_close(database);

NSAssert(0, @"Error creating table: %s", errorMsg);

}

NSString *query = @"SELECT ROW, FIELD_DATA FROM FIELDS ORDER BY ROW"; sqlite3_stmt *statement;

if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {

while (sqlite3_step(statement) == SQLITE_ROW) { int row = sqlite3_column_int(statement, 0);

char *rowData = (char *)sqlite3_column_text(statement, 1);

NSString *fieldName = [[NSString alloc] initWithFormat:@"field%d", row]; NSString *fieldValue = [[NSString alloc]

initWithUTF8String:rowData];

UITextField *field = [self valueForKey:fieldName]; field.text = fieldValue;

}

sqlite3_finalize(statement);

}

sqlite3_close(database);

UIApplication *app = [UIApplication sharedApplication]; [[NSNotificationCenter defaultCenter] addObserver:self

selector:@selector(applicationWillResignActive:)

name:UIApplicationWillResignActiveNotification

object:app];

}

- (void)applicationWillResignActive:(NSNotification *)notification { BIDFourLines *fourLines = [[BIDFourLines alloc] init]; fourLines.field1 = field1.text;

fourLines.field2 = field2.text; fourLines.field3 = field3.text; fourLines.field4 = field4.text;

www.it-ebooks.info

470	CHAPTER 13: Basic Data Persistence
	NSMutableData *data = [[NSMutableData alloc] init];
	NSKeyedArchiver *archiver = [[NSKeyedArchiver alloc]
	initForWritingWithMutableData:data];
	[archiver encodeObject:fourLines forKey:kDataKey];
	[archiver finishEncoding];
	[data writeToFile:[self dataFilePath] atomically:YES];
	sqlite3 *database;
	if (sqlite3_open([[self dataFilePath] UTF8String], &database)
	!= SQLITE_OK) {
	sqlite3_close(database);
	NSAssert(0, @"Failed to open database");
	}
	for (int i = 1; i <= 4; i++) {
	NSString *fieldName = [[NSString alloc]
	initWithFormat:@"field%d", i];
	UITextField *field = [self valueForKey:fieldName];
	// Once again, inline string concatenation to the rescue:
	char *update = "INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA) "
	"VALUES (?, ?);";
	char *errorMsg;
	sqlite3_stmt *stmt;
	if (sqlite3_prepare_v2(database, update, -1, &stmt, nil)

== SQLITE_OK) { sqlite3_bind_int(stmt, 1, i);

sqlite3_bind_text(stmt, 2, [field.text UTF8String], -1, NULL);

}

if (sqlite3_step(stmt) != SQLITE_DONE)

NSAssert(0, @"Error updating table: %s", errorMsg); sqlite3_finalize(stmt);

}

sqlite3_close(database);

}

.

.

.

The first new code is in the viewDidLoad method. We begin by opening the database. If we hit a problem with opening the database, we close it and raise an assertion.

sqlite3 *database;

if (sqlite3_open([[self dataFilePath] UTF8String], &database) != SQLITE_OK) {

sqlite3_close(database);

NSAssert(0, @"Failed to open database");

}

Next, we need to make sure that we have a table to hold our data. We can use SQL CREATE TABLE to do that. By specifying IF NOT EXISTS, we prevent the database from overwriting existing data. If there is already a table with the same name, this command quietly exits without doing anything, so it’s safe to call every time our application launches without explicitly checking to see if a table exists.

www.it-ebooks.info

CHAPTER 13: Basic Data Persistence

471

NSString *createSQL = @"CREATE TABLE IF NOT EXISTS FIELDS "

"(ROW INTEGER PRIMARY KEY, FIELD_DATA TEXT);";

char *errorMsg;

if (sqlite3_exec (database, [createSQL UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {

sqlite3_close(database);

NSAssert1(0, @"Error creating table: %s", errorMsg);

}

Finally, we need to load our data. We do this using an SQL SELECT statement. In this simple example, we create an SQL SELECT that requests all the rows from the database and ask SQLite3 to prepare our SELECT. We also tell SQLite3 to order the rows by the row number, so that we always get them back in the same order. Absent this, SQLite3 will return the rows in the order in which they are stored internally.

NSString *query = @"SELECT ROW, FIELD_DATA FROM FIELDS ORDER BY ROW"; sqlite3_stmt *statement;

if (sqlite3_prepare_v2( database, [query UTF8String], -1, &statement, nil) == SQLITE_OK) {

Then we step through each of the returned rows:

while (sqlite3_step(statement) == SQLITE_ROW) {

We grab the row number and store it in an int, and then we grab the field data as a C string.

int row = sqlite3_column_int(statement, 0);

char *rowData = (char *)sqlite3_column_text(statement, 1);

Next, we create a field name based on the row number (such as field1 for row 1), convert the C string to an NSString, and use that to set the appropriate field with the value retrieved from the database.

NSString *fieldName = [[NSString alloc] initWithFormat:@"field%d", row]; NSString *fieldValue = [[NSString alloc]

initWithUTF8String:rowData];

UITextField *field = [self valueForKey:fieldName]; field.text = fieldValue;

Finally, we close the database connection, and we’re all finished.

}

sqlite3_finalize(statement);

}

sqlite3_close(database);

Note that we close the database connection as soon as we’re finished creating the table and loading any data it contains, rather than keeping it open the entire time the application is running. It’s the simplest way of managing the connection, and in this little app, we can just open the connection those few times we need it. In a more databaseintensive app, you might want to keep the connection open all the time,

The other changes we made are in the applicationWillResignActive: method, where we need to save our application data. Because the data in the database is stored in a table, our application’s data will look something like Table 13–1 when stored.

www.it-ebooks.info

472	CHAPTER 13: Basic Data Persistence
	Table 13–1. Data Stored in the FIELDS Table of the Database
	ROW	FIELD_DATA
	1	When in the course of human
	2	events, it becomes necessary
	3	for one people to dissolve the
	4	political bands which have…

The applicationWillResignActive: method starts off by once again opening the database.

sqlite3 *database;

if (sqlite3_open([[self dataFilePath] UTF8String], &database) != SQLITE_OK) {

sqlite3_close(database);

NSAssert(0, @"Failed to open database");

}

To save the data, we loop through all four fields and issue a separate command to update each row of the database.

for (int i = 1; i <= 4; i++) {

NSString *fieldName = [[NSString alloc] initWithFormat:@"field%d", i];

UITextField *field = [self valueForKey:fieldName];

The first thing we do in the loop is craft a field name so we can retrieve the correct text field outlet. Remember that valueForKey: allows you to retrieve a property based on its name. We also declare a pointer to be used for the error message if we encounter an error.

We craft an INSERT OR REPLACE SQL statement with two bind variables. The first represents the row that’s being stored; the second is for the actual string value to be stored. By using INSERT OR REPLACE instead of the more standard INSERT, we don’t need to worry about whether a row already exists.

char *update = "INSERT OR REPLACE INTO FIELDS (ROW, FIELD_DATA) " "VALUES (?, ?);";

Next, we declare a pointer to a statement, prepare our statement with the bind variables, and bind values to both of the bind variables.

sqlite3_stmt *stmt;

if (sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK) { sqlite3_bind_int(stmt, 1, i);

sqlite3_bind_text(stmt, 2, [field.text UTF8String], -1, NULL);

}

Then we call sqlite3_step to execute the update, check to make sure it worked, and finalize the statement, ending the loop.

www.it-ebooks.info