CHAPTER 13: Basic Data Persistence

463

[data writeToFile:[self dataFilePath] atomically:YES];

}

...

Save your changes and take this version of Persistence for a spin.

Not very much has changed, really. We started off by specifying a new file name so that our program doesn’t try to load the old property list as an archive. We also defined a new constant that will be the key value we use to encode and decode our object. Then we redefined the loading and saving, using BIDFourLines to hold the data, and using the NSCoding methods to do the actual loading and saving. The GUI is identical to the previous version.

This new version takes several more lines of code to implement than property list serialization, so you might be wondering if there really is an advantage to using archiving over just serializing property lists. For this application, the answer is simple: no, there really isn’t any advantage. But think back to the last example in Chapter 9, where we allowed the user to edit a list of presidents, and each president had four different fields that could be edited. To handle archiving that list of presidents with a property list would involve iterating through the list of presidents, creating an NSDictionary instance for each president, copying the value from each of their fields over to the NSDictionary instance, and adding that instance to another array, which could then be written to a plist file. And that’s assuming that we restricted ourselves to using only serializable properties. If we didn’t, using property list serialization wouldn’t even be an option without doing a lot of conversion work.

On the other hand, if we had an array of archivable objects, such as the BIDFourLines class that we just built, we could archive the entire array by archiving the array instance itself. Collection classes like NSArray, when archived, archive all of the objects they contain. As long as every object you put into an array or dictionary conforms to NSCoding, you can archive the array or dictionary and restore it, so that all the objects that were in it when you archived it will be in the restored array or dictionary.

In other words, this approach scales beautifully (in terms of code size, at least). No matter how many objects you add, the work to write those objects to disk (assuming you’re using single-file persistence) is exactly the same. With property lists, the amount of work increases with every object you add.

Using iOS’s Embedded SQLite3

The third persistence option we’re going to discuss is using iOS’s embedded SQL database, called SQLite3. SQLite3 is very efficient at storing and retrieving large amounts of data. It’s also capable of doing complex aggregations on your data, with much faster results than you would get doing the same thing using objects.

For example, if your application needs to calculate the sum of a particular field across all the objects in your application, or if you need the sum from just the objects that meet certain criteria, SQLite3 allows you to do that without loading every object into memory. Getting aggregations from SQLite3 is several orders of magnitude faster than loading all

www.it-ebooks.info

464	CHAPTER 13: Basic Data Persistence

the objects into memory and summing their values. Being a full-fledged embedded database, SQLite3 contains tools to make it even faster by, for example, creating table indexes that can speed up your queries.

NOTE: There are several schools of thought about the pronunciation of “SQL” and “SQLite.” Most official documentation says to pronounce “SQL” as “Ess-Queue-Ell” and “SQLite” as “Ess-

Queue-Ell-Light.” Many people pronounce them, respectively, as “Sequel” and “Sequel Light.” A small cadre of hardened rebels prefer “Squeal” and “Squeal Light.” Pick whatever works best for you (and be prepared to be mocked and shunned by the infidels if you choose to join the

“Squeal” movement).

SQLite3 uses the Structured Query Language (SQL). SQL is the standard language used to interact with relational databases. Whole books have been written on the syntax of SQL (hundreds of them, in fact), as well as on SQLite itself. So, if you don’t already know SQL and you want to use SQLite3 in your application, you have a little work ahead of you. We’ll show you how to set up and interact with the SQLite database from your iOS applications, and you’ll see some of the basics of the syntax in this chapter. But to really make the most of SQLite3, you’ll need to do some additional research and exploration. A couple of good starting points are “An Introduction to the SQLite3 C/C++ Interface” (http://www.sqlite.org/cintro.html) and “SQL As Understood by SQLite” (http://www.sqlite.org/lang.html).

Relational databases, including SQLite3, and object-oriented programming languages use fundamentally different approaches to storing and organizing data. The approaches are different enough that numerous techniques and many libraries and tools for converting between the two have been developed. These different techniques are collectively called object-relational mapping (ORM). There are currently several ORM tools available for Cocoa Touch. In fact, we’ll look at one ORM solution provided by Apple, called Core Data, later in the chapter.

In this chapter, we’re going to focus on the SQLite3 basics, including setting it up, creating a table to hold your data, and using the database in an application. Obviously, in the real world, such a simple application as the one we’re working on wouldn’t warrant the investment in SQLite3. But this application’s simplicity is exactly what makes it a good learning example.

Creating or Opening the Database

Before you can use SQLite3, you must open the database. The function that’s used to do that, sqlite3_open(), will open an existing database, or if none exists at the specified location, it will create a new one. Here’s what the code to open a new database might look like:

sqlite3 *database;

int result = sqlite3_open("/path/to/database/file", &database);

www.it-ebooks.info

CHAPTER 13: Basic Data Persistence

465

If result is equal to the constant SQLITE_OK, then the database was successfully opened. Note that the path to the database file must be passed in as a C string, not as an NSString. SQLite3 was written in portable C, not Objective-C, and it has no idea what an NSString is. Fortunately, there is an NSString method that generates a C string from an NSString instance:

const char *stringPath = [pathString UTF8String];

When you’re finished with an SQLite3 database, close it:

sqlite3_close(database);

Databases store all their data in tables. You can create a new table by crafting an SQL CREATE statement and passing it in to an open database using the function sqlite3_exec, like so:

char *errorMsg;

const char *createSQL = "CREATE TABLE IF NOT EXISTS PEOPLE (ID INTEGER PRIMARY KEY AUTOINCREMENT, FIELD_DATA TEXT)";

int result = sqlite3_exec(database, createSQL, NULL, NULL, &errorMsg);

As you did before, you need to verify that result is equal to SQLITE_OK to make sure your command ran successfully. If it didn’t, errorMsg will contain a description of the problem that occurred.

The function sqlite3_exec is used to run any command against SQLite3 that doesn’t return data, including updates, inserts, and deletes. Retrieving data from the database is little more involved. You first need to prepare the statement by feeding it your SQL SELECT command:

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

int result = sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil);

NOTE: All of the SQLite3 functions that take strings require an old-fashioned C string. In the example, we created and passed a C string. We created an NSString and derived a C string by using one of NSString‘s methods called UTF8String. Either method is acceptable. If you need to do manipulation on the string, using NSString or NSMutableString will be easier, but converting from NSString to a C string incurs a bit of extra overhead.

If result equals SQLITE_OK, your statement was successfully prepared, and you can start stepping through the result set. Here is an example of stepping through a result set and retrieving an int and an NSString from the database:

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

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

NSString *fieldValue = [[NSString alloc] initWithUTF8String:rowData]; // Do something with the data here

}

sqlite3_finalize(statement);

www.it-ebooks.info

466	CHAPTER 13: Basic Data Persistence

Using Bind Variables

Although it’s possible to construct SQL strings to insert values, it is common practice to use something called bind variables for this purpose. Handling strings correctly— making sure they don’t have invalid characters and that quotes are inserted properly— can be quite a chore. With bind variables, those issues are taken care of for us.

To insert a value using a bind variable, you create your SQL statement as normal, but put a question mark (?) into the SQL string. Each question mark represents one variable that must be bound before the statement can be executed. Then you prepare the SQL statement, bind a value to each of the variables, and execute the command.

Here’s an example that prepares an SQL statement with two bind variables, binds an int to the first variable and a string to the second variable, and then executes and finalizes the statement:

char *sql = "insert into foo values (?, ?);"; sqlite3_stmt *stmt;

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

sqlite3_bind_text(stmt, 2, "Bar", -1, NULL);

}

if (sqlite3_step(stmt) != SQLITE_DONE) NSLog(@"This should be real error checking!");

sqlite3_finalize(stmt);

There are multiple bind statements available depending on the datatype you wish to use. Most bind functions take only three parameters:

The first parameter to any bind function, regardless of the datatype, is a pointer to the sqlite3_stmt used previously in the sqlite3_prepare_v2() call.

The second parameter is the index of the variable to which you’re binding. This is a one-indexed value, meaning that the first question mark in the SQL statement has index 1, and each one after it is one higher than the one to its left.

The third parameter is always the value that should be substituted for the question mark.

A few bind functions, such as those for binding text and binary data, have two additional parameters:

The first additional parameter is the length of the data being passed in the third parameter. In the case of C strings, you can pass -1 instead of the string’s length, and the function will use the entire string. In all other cases, you need to tell it the length of the data being passed in.

The final parameter is an optional function callback in case you need to do any memory cleanup after the statement is executed. Typically, such a function would be used to free memory allocated using malloc().

www.it-ebooks.info