Chapter 16
Data Persistence Using SQLite
Key Skills & Concepts
• Creating a database and adding data to it
• Including the database in Xcode
• Reading from a database
• Making a database writable
• Inserting a record
• Updating a record
• Deleting a record
The SQLite database is a popular open-source database written in C. The database is small and designed for embedding in an application, unlike a database such as Oracle that was designed to run on a separate, large server. SQLite is part of the standard open-source Linux/BSD server stack, and as OS X is essentially FreeBSD, it was only natural Apple chose SQLite as the iOS’s embedded database.
Adding a SQLite Database
Adding a SQLite database to your project involves two steps. First, you must create the database. In this chapter’s first task, you create a database using the Firefox SQLite Manager plug-in. Second, you must add the SQLite library to your Xcode project. The first task also illustrates adding the SQLite library to your Xcode project. After creating the database and loading it, you can then use the database programmatically via its C programming interface.
Try This
Creating a Simple Database Using FireFox SQLite Manager
1. If you don’t already have Firefox, download and install it.
2. Select Add-ons from the Tools menu (Figure 16-1).
3. Select Get Add-ons, type SQLite in the search box, and install SQLite Manager.
4. Once installed and you have restarted Firefox, select Tools | SQLite Manager.
Figure 16-1 Adding SQLite Manager to Firefox
5. Select the New icon (the blank paper graphic), and create a new database named myDatabase. Save the database file some place where you can easily find it later. Note SQLite Manager automatically adds the .sqlite extension.
6. Click Create Table and create a new table named photos.
7. Add three columns: id, name, and photo. Make id an INTEGER and check Primary Key and Autoinc check boxes.
8. Make name a VARCHAR and check only Allow Null.
9. Make photo a BLOB and check only Allow Null.
10. Your screen should resemble Figure 16-2.
11. Click OK and the SQLite Manager generates the database table.
Figure 16-2 Creating a database using SQLite Manager
NOTE
SQLite does not enforce foreign key relationships. You must instead write triggers manually to enforce foreign key relationships. SQLite does not support right outer joins or full outer joins. SQLite views are read-only.
12. Click the Browse & Search tab, and then click the Add Record button.
13. In the action sheet, leave id blank. Type Icon One for the name. Notice the small paper clip beside photo. Move your mouse over the paper clip, and the tooltip should say “Add File as a Blob” (Figure 16-3). Click the paper clip and add any photo from your computer. If the photo column doesn’t say something like BLOB (Size: 65984), the file was not correctly added as a blob.
14. Click OK, and the record will be added. Add another record, selecting any other image from your computer.
Figure 16-3 Adding a record using SQLite Manager
15. From the menu, select Database | Close Database from the main menus to close the database. You can now exit SQLite Manager and quit Firefox.
16. Open Xcode and create a new View-based Application. Name the application MyDBProject.
17. Expand Frameworks in the Navigator and right-click on any one of the frameworks (e.g., UIKit) and select Show in Finder from the pop-up menu. This will get you to the folder with all of your frameworks on your computer. Move up two folder levels from there to the SDK folder and then open user/lib.
18. Drag and drop the libsqlite3.0.dylib framework from that folder to Frameworks in the Navigator in Xcode. In the dialog that pops up, be sure to uncheck “Copy items into destination group’s folder” and click Finish.
19. Add the database file that you created earlier to the Resources folder. Be sure to check the Copy Items check box so that the database file gets copied into MyDBProject.
20. This task is now complete with the SQLite library added to the project and the database file copied into the project’s resources. Do not delete the project or database, as you will use them for the remainder of this chapter.
NOTE
Adding binary data using SQLite Manager in Firefox seems to be buggy. Sometimes it works, sometimes not. If after adding a record the photo column is blank, just edit the row and add the photo file’s content again. Updating the blob seems to be more stable in SQLite Manager.
Basic SQLite Database Manipulation
If you have ever used a database from within a programming language, SQLite database manipulation using C should seem intuitive. You open the database. You create a prepared statement containing a SQL string. That statement might have one or more parameters you bind values to. After binding, you execute the statement. If the statement returns results, you loop through each record and load the record’s column values into your program’s variables. After looping through all records, you finalize the statement, and, if you are finished with the database, you close the database. The steps are similar for most languages and databases.
Opening the Database
You open a database using the sqlite3_open, sqlite_open16, or sqlite3_open_v2 commands. This chapter uses the sqlite3_open command exclusively. The sqlite3_open command takes a database filename as a UTF-8 string and opens the database. Listing 16-1, taken from the SQLite’s online documentation, lists the sqlite_open3 method signature.
Listing 16-1 The sqlite3_open method signature (from SQLite online documentation)
int sqlite3_open( const char *filename, /* Database filename (UTF-8) */ sqlite3 **ppDb /* OUT: SQLite db handle */ );
The method returns an integer as the method’s success or failure code. Listing 16-2, from the SQLite online documentation, lists several common result codes.
Listing 16-2 SQLite return codes (taken from SQLite online documentation)
#define SQLITE_OK 0 /* Successful result */ #define SQLITE_ERROR 1 /* SQL error or missing database */ #define SQLITE_READONLY 8 /* Attempt to write a readonly database */ #define SQLITE_INTERRUPT 9 /* Operation terminated by #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ #define SQLITE_CANTOPEN 14 /* Unable to open the database file */ #define SQLITE_MISMATCH 20 /* Data type mismatch */ #define SQLITE_ROW 100 /* sqlite3_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite3_step() has finished executing */
Statements, Preparing Statements, and Executing Statements
There are two primary ways of executing SQL statements using SQLite’s C interface. One method is the sqlite3_exec method. Although a powerful method, it is more advanced C programming, and so this chapter uses the sqlite3_stmt structure and the sqlite3_prepare_v2 and sqlite3_step statements instead of the sqlite3_exec function.
The SQLite sqlite3_stmt
The sqlite3_stmt encapsulates a SQL statement. For instance, “select * from photos” is a SQL statement. In your program, you encapsulate this SQL string using a statement. For instance, the following code snippet illustrates creating a SQL string, initializing a statement, and loading the statement (Listing 16-3).
Listing 16-3 Using a sqlite3_stmt in a C program
const char *sqlselect = "SELECT id,name,photo FROM photos"; static sqlite3_stmt *statement = nil; sqlite3_prepare_v2(database, sqlselect, -1, &statement, NULL);
The SQLite sqlite3_prepare_v2 Method
You load a SQL string into a statement using sqlite3_prepare methods. The prepare methods are sqlite3_prepare, sqlite3_prepare_v2, sqlite3_prepare_16, and sqlite3_prepare16_v2. This chapter uses only the sqlite3_prepare_v2 method. Notice the prepare statement takes a C string, not an NString, but getting the C string from an NString is not difficult—simply call the NSString’s UTF8String method. The sqlite3_prepare_v2 method’s signature is in Listing 16-4. Notice, like the open statements, the prepare statement returns an integer result code you should check when calling the method.
Listing 16-4 The sqlite3_prepare_v2 method signature (taken from the SQLite online documentation)
int sqlite3_prepare_v2( sqlite3 *db, /* Database handle */ const char *zSql, /* SQL statement, UTF-8 encoded */ int nByte, /* Maximum length of zSql in bytes. */ sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ );
After preparing the statement, you execute it and step through the results.
The SQLite sqlite3_step Method
The sqlite3_step method executes a prepared statement. You must call this method at least once. For instance, when calling insert or update, you call sqlite3_step once. You only call it once because these statements do not result in a record set being returned from the database. When selecting data, you typically call this method multiple times until you receive no more results. The following is the method’s signature.
int sqlite3_step(sqlite3_stmt*);
Like the other SQLite methods, this method returns a response code you should check after calling the method.
Select
You select one or more records from a SQL database using a select statement. Because a select statement usually returns multiple rows, you must loop through the row set if you wish to obtain all records.
while (sqlite3_step(statement) == SQLITE_ROW){
//process row here
}
Obtaining SQLite Column Values
You obtain column values through a method in Listing 16-5. Using these methods will become more apparent after the next task.
Listing 16-5 Methods for obtaining column data (from SQLite online documentation)
const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); sqlite3_int64 sqlite3_column_int64(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
NOTE
The int iCol arguments in the methods in Listing 16-5 are a zero-based index into the columns in the results of the sqlite3_stmt, not an index into the columns of a SQLite database table.
Try This
Opening and Querying a Database
1. Return to your MyDBProject in Xcode.
2. In Classes, create a new group called Model.
3. Create a new Objective-C class in the Model group called PhotosDAO. Create another Objective-C class in the same group called PhotoDAO.
4. Add a name, photoID, and photo property to PhotoDAO.h and PhotoDAO.m (Listings 16-6 and 16-7).
#import <Foundation/Foundation.h>
@interface PhotoDAO : NSObject {
NSString * name;
NSInteger photoID;
UIImage * photo;
}
@property (nonatomic, retain) NSString * name;
@property (nonatomic, assign) NSInteger photoID;
@property (nonatomic, retain) UIImage * photo;
@end
#import "PhotoDAO.h"
@implementation PhotoDAO
@synthesize name;
@synthesize photoID;
@synthesize photo;
- (void) dealloc {
[name release];
[photo release];
[super dealloc];
}
@end
5. Open PhotosDAO.h and import SQLite3. Add a reference to the database you will use (Listing 16-8).
#import <Foundation/Foundation.h>
#import <sqlite3.h>
@interface PhotosDAO : NSObject {
sqlite3 *database;
}
- (NSMutableArray *) getAllPhotos;
@end
6. Add a getAllPhotos method to PhotosDAO and implement the method (Listing 16-9).
#import "PhotosDAO.h"
#import "PhotoDAO.h"
@implementation PhotosDAO
- (NSMutableArray *) getAllPhotos {
NSMutableArray * photosArray = [[NSMutableArray alloc] init];
@try {
NSFileManager *fileManager = [NSFileManager defaultManager];
NSString *theDBPath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:@"myDatabase.sqlite"];
BOOL success = [fileManager fileExistsAtPath:theDBPath];
if (!success) {
NSLog(@"Failed to find database file '%@'.", theDBPath);
}
if (!(sqlite3_open([theDBPath UTF8String], &database) == SQLITE_OK)) {
NSLog(@"An error opening database, normally handle error here.");
}
const char *sql = "SELECT id,name,photo FROM photos";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) !=
SQLITE_OK){
NSLog(@"Error, failed to prepare statement, handle error here.");
}
while (sqlite3_step(statement) == SQLITE_ROW) {
PhotoDAO * aPhoto = [[PhotoDAO alloc] init];
aPhoto.photoID = sqlite3_column_int(statement, 0);
aPhoto.name = [NSString stringWithUTF8String:(char *)
sqlite3_column_text(statement, 1)];
const char * rawData = sqlite3_column_blob(statement, 2);
int rawDataLength = sqlite3_column_bytes(statement, 2);
NSData *data = [NSData dataWithBytes:rawData length: rawDataLength];
aPhoto.photo = [[UIImage alloc] initWithData:data];
[photosArray addObject:aPhoto];
[aPhoto release];
}
if(sqlite3_finalize(statement) != SQLITE_OK){
NSLog(@"Failed to finalize data statement, error handling here.");
}
if (sqlite3_close(database) != SQLITE_OK) {
NSLog(@"Failed to close database, normally error handling here.");
}
} @catch (NSException *e) {
NSLog(@"An exception occurred: %@", [e reason]);
return nil;
}
return photosArray;
}
@end
7. Open MyDBProjectViewController.h and add an NSMutableArray property to hold the photos. Add an IBOutlet for a UIImageView. Add a UILabel named theLabel, add an IBAction, and name the method changeImage (Listing 16-10).
Listing 16-10 MyDBProjectViewController.h
#import <UIKit/UIKit.h>
@interface MyDBProjectViewController : UIViewController {
NSMutableArray * photos;
UIImageView * theImageView;
UILabel * theLabel;
}
@property (nonatomic, retain) NSMutableArray * photos;
@property (nonatomic, retain) IBOutlet UIImageView * theImageView;
@property (nonatomic, retain) IBOutlet UILabel * theLabel;
- (IBAction) changeImage: (id) sender;
@end
8. Open MyDBProjectViewController.m and synthesize photos and theImageView (Listing 16-11).
Listing 16-11 MyDBProjectViewController.m
#import "MyDBProjectViewController.h"
#import "PhotoDAO.h";
#import "PhotosDAO.h";
@implementation MyDBProjectViewController
@synthesize photos;
@synthesize theImageView;
@synthesize theLabel;
- (void)viewDidLoad {
PhotosDAO * myPhotos = [[PhotosDAO alloc] init];
self.photos = [myPhotos getAllPhotos];
[self.theImageView setImage:((PhotoDAO *)[self.photos
objectAtIndex:0]).photo];
[self.theLabel setText:((PhotoDAO *)
[self.photos objectAtIndex:0]).name];
[myPhotos release];
[super viewDidLoad];
}
- (IBAction) changeImage: (id) sender {
static NSInteger currentElement = 0;
if(++currentElement == [self.photos count]) currentElement = 0;
PhotoDAO * aPhoto =
(PhotoDAO *) [self.photos objectAtIndex: currentElement];
[self.theLabel setText:aPhoto.name];
[self.theImageView setImage:aPhoto.photo];
}
- (void)dealloc {
[photos release];
[theImageView release];
[theLabel release];
[super dealloc];
}
@end
9. Implement the viewDidLoad and changeImage methods so that they match Listing 16-11.
10. Save your changes and open MyDBProjectViewController.xib. Add a toolbar, a label, and a UIImageView (Figure 16-4). Change the button’s title to Next. Remove the text from the label.
11. Connect the File’s Owner theLabel outlet to the label added to the toolbar. Connect the theImageView outlet to the UIImageView. Connect the changeImage action to the Next button. Save your changes.
12. Run the application in iPhone Simulator, as shown in Figures 16-5 and 16-6.
Figure 16-4 Adding a UIImageView and a UIToolBar to the view’s canvas
Figure 16-5 Running the application (first image)
Figure 16-6 Running the application (second image)
NOTE
You would normally never load an entire database at once in a real application, especially when using large blobs, like this example. Memory is limited in an iOS device—only load what you need when you need it.
The Model-View-Controller
When writing a program for any platform, you should adhere to the MVC design pattern as closely as possible. Rather than placing the database logic in a view or view controller, you created separate classes, insulating the view and controller layers from the database layer. The MyDBProjectViewController knows nothing about the underlying SQLite3 library; the view controller only knows about PhotosDAO and PhotoDAO. Notice you further separated the code by placing it in its own group, Model, under Classes. All this separation makes debugging and maintaining the program easier. It also makes reading and understanding this chapter’s example code easier.
Opening the Database
To keep the task’s length manageable and focused, rather than creating several data access methods in PhotosDAO, you only created one.
- (NSMutableArray *) getAllPhotos;
This method returns an array of PhotoDAO objects. The getAllPhotos method first finds the database and opens it. Because the database is in the resources folder, you can access it directly using the bundle’s resourcePath. (When you want to create an application that uses canned [predefined] data, this task illustrated how to create that data in advance [using SQLite Manager in Firefox] and then embed it in your application.)
NSFileManager *fileManager = [NSFileManager defaultManager]; NSString *theDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: @"myDatabase.sqlite"];
After obtaining the database’s path, you open it.
if (!(sqlite3_open([theDBPath UTF8String], &database) == SQLITE_OK))
Notice that you obtain the UTF8String from the NSString before passing the sqlite3_open method the path. Since opening the database is a common activity, you might want to move that portion of the code into its own method for easy reuse.
Querying the Data
After opening the database, you query it for the photo records. If you have ever worked with a database using code, for instance, Java Database Connectivity (JDBC), then this code should look familiar. The getAllPhotos method first creates the SQL select string. Next, the method places the string in a statement and then queries the database. After obtaining the data, getAllPhotos loops through each record.
For each new record, getAllPhotos creates a new PhotoDAO. The newly created PhotoDAO object’s values are then set to the appropriate values from the current record. After initializing the PhotoDAO object, getAllPhotos places the object into PhotosDAO’s photosArray.
Loading a Blob into NSData
This code snippet is useful. It shows you a quick, easy way to load a blob, any blob, into an NSData object. First, load the blob into a C string.
const char * rawData = sqlite3_column_blob(statement, 2);
Second, obtain the blob’s byte size.
int rawDataLength = sqlite3_column_bytes(statement, 2);
Third, create an NSData class using the C string and size variables.
NSData *data = [NSData dataWithBytes:rawData length:rawDataLength];
As you already know the database blob is an image, you initialize the PhotoDAO’s photo property using the UIImage’s initWithData method.
aPhoto.photo = [[UIImage alloc] initWithData:data];
This same technique works for other binary data as well (replacing UIImage with the appropriate class).
Closing the Database
When finished using a statement, you release its resources by finalizing the statement.
if(sqlite3_finalize(statement) != SQLITE_OK)
After you no longer need the database, you close it.
if (sqlite3_close(database) != SQLITE_OK)
Selecting all records only has limited value. Rarely will you use SQL statements where you do not wish to limit the results returned. For this, you typically add parameters to your SQL statements and then replace the parameters with values in your program. This is called binding your program’s values to the statements’ parameters. Programs usually also allow more than simply selecting data; most applications allow users to add, edit, and delete records. In the next section, you learn about binding, inserting, updating, and deleting records.
SQLite Binding, Inserting, Updating, and Deleting
SQL allows limiting data to only the data needed via the where clause. For instance, the following statement only selects records whose age column is greater than 30.
select * from mytable where age > 30
When placing SQL statements like this into a SQLite statement, you can parameterize the where clause’s value. For instance, to parameterize age’s value, write the following code.
select * from mytable where age > ?
You then bind your program’s value to the SQL statement’s parameter.
Binding
You bind one of your program’s values to a SQL statement’s parameter using a bind method (Listing 16-12). Different data types have different bind methods.
Listing 16-12 SQLite bind methods (from the SQLite online documentation)
int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, sqlite3_int64); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); int sqlite3_bind_zeroblob(sqlite3_stmt*, int, int n);
NOTE
Bindings start with 1 rather than 0.
For instance, the following code snippet shows a SQL statement and its subsequent binding (without the error checking shown).
const char * select = "Select * from photos where name = ?"; sqlite3_stmt *select_statement; sqlite3_prepare_v2(database, select, -1, &select_statement, NULL); sqlite3_bind_text(&select_statement, 1, [photo.name UTF8String], -1, SQLITE_TRANSIENT);
The first argument is a pointer to the prepared statement. The second argument is the SQL statement’s parameter number. The third argument is the value that should be bound to the SQL statement’s parameter. The fourth argument is the number of bytes in the value—if negative, the length is automatically determined from the C string.
Insert, Update, and Delete
There is little difference between the steps for inserting, updating, or deleting records using the SQLite C library. The primary difference is you only call the sqlite3_step method once. Usually, you use insert, update, or delete with bindings. For instance,
insert into customers (name, age, company, location) values (?, ?, ?, ?);
or
update customers set location = ? where company = ?;
or
delete customers where company = ?;
In the following task, you insert, update, and delete a record.
Try This
Inserting, Updating, and Deleting Records
1. Open the MyDBProject project in Xcode.
2. Add a class method named moveDatabase to PhotosDAO. Remember, a class method uses a plus rather than a minus.
3. Implement the method in PhotosDAO.m as in Listing 16-13.
Listing 16-13 The moveDatabase and getAllPhotos methods
+ (void) moveDatabase {
NSFileManager *fileManager = [NSFileManager defaultManager];
NSString *theDBPath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:@"myDatabase.sqlite"];
NSError *error;
BOOL success;
NSArray * paths =
NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString * docsDir = [paths objectAtIndex:0];
NSString * newPath =
[docsDir stringByAppendingPathComponent:@"myDatabase.sqlite"];
[fileManager removeItemAtPath:newPath error: &error];
success = [fileManager copyItemAtPath:theDBPath
toPath:newPath error: &error];
if (!success) {
NSLog(@"Failed to copy database...error handling here %@.",
[error localizedDescription]);
}
}
- (NSMutableArray *) getAllPhotos {
NSMutableArray * photosArray = [[NSMutableArray alloc] init];
@try {
NSFileManager *fileManager = [NSFileManager defaultManager];
NSArray * paths = NSSearchPathForDirectoriesInDomains
(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * docsDir = [paths objectAtIndex:0];
NSString * theDBPath = [docsDir stringByAppendingPathComponent:
@"myDatabase.sqlite"];
BOOL success = [fileManager fileExistsAtPath:theDBPath];
if (!success) {
NSLog(@"Failed to find database file '%@'.");
}
if (!(sqlite3_open([theDBPath UTF8String], &database) ==
SQLITE_OK)) {
NSLog(@"An error opening database, handle error here.");
}
const char *sql = "SELECT id,name,photo FROM photos";
sqlite3_stmt *statement;
if (sqlite3_prepare_v2(database, sql, -1, &statement, NULL) !=
SQLITE_OK) {
NSLog(@"Error, failed to prepare statement, handle error here.");
}
while (sqlite3_step(statement) == SQLITE_ROW) {
PhotoDAO * aPhoto = [[PhotoDAO alloc] init];
aPhoto.photoID = sqlite3_column_int(statement, 0);
aPhoto.name = [NSString stringWithUTF8String:(char *)
sqlite3_column_text(statement, 1)];
const char * rawData = sqlite3_column_blob(statement, 2);
int rawDataLength = sqlite3_column_bytes(statement, 2);
NSData *data = [NSData dataWithBytes:rawData length:
rawDataLength];
aPhoto.photo = [[UIImage alloc] initWithData:data];
[photosArray addObject:aPhoto];
}
if(sqlite3_finalize(statement) != SQLITE_OK){
NSLog(@"Failed to finalize data statement, error handling here.");
}
if (sqlite3_close(database) != SQLITE_OK) {
NSLog(@"Failed to close database, error handling here.");
}
} @catch (NSException *e) {
NSLog(@"An exception occurred: %@", [e reason]);
return nil;
}
return photosArray;
}
4. Modify the getAllPhotos method in PhotosDAO to obtain the records from the documents directory (Listing 16-13).
5. Open MYDBProjectViewController.m and add a call to the moveDatabase method to the first line of viewDidLoad (Listing 16-14).
Listing 16-14 The viewDidLoad method
- (void)viewDidLoad {
[PhotosDAO moveDatabase];
PhotosDAO * myPhotos = [[PhotosDAO alloc] init];
self.photos = [myPhotos getAllPhotos];
[self.theImageView setImage:((PhotoDAO *)[self.photos
objectAtIndex:0]).photo];
[self.theLabel setText:((PhotoDAO *)
[self.photos objectAtIndex:0]).name];
[myPhotos release];
[super viewDidLoad];
}
The first thing you did was make the database writable. The Resources folder is read-only. Saving changes requires the database to be writable, so you copied the database to your documents directory. You also modified the getAllPhotos method so that it obtained the database from the application’s document directory rather than the resources directory.
1. Add any photo from your computer to the project’s Resources group and remember the photo’s name so that you can use it later in the addThirdPhoto method.
2. Add a new method to PhotosDAO called addPhoto. Implement the method (Listing 16-15).
Listing 16-15 The addPhoto method
- (void) addPhoto : (PhotoDAO *) photo {
const char * sql = "insert into photos (name, photo) values (?, ?)";
sqlite3_stmt *insert_statement = nil;
NSArray * paths = NSSearchPathForDirectoriesInDomains
(NSDocumentDirectory, NSUserDomainMask, YES);
NSString * docsDir = [paths objectAtIndex:0];
NSString * thePath = [docsDir stringByAppendingPathComponent:
@"myDatabase.sqlite"];
sqlite3_open([thePath UTF8String], &database);
sqlite3_prepare_v2(database, sql, -1, &insert_statement, NULL);
sqlite3_bind_text(insert_statement, 1, [photo.name UTF8String], -1,
SQLITE_TRANSIENT);
NSData * binData = UIImagePNGRepresentation(photo.photo);
sqlite3_bind_blob(insert_statement, 2, [binData bytes],
[binData length], SQLITE_TRANSIENT);
sqlite3_step(insert_statement);
sqlite3_finalize(insert_statement);
sqlite3_close(database);
}
3. Create a new IBAction in MyDBProjectViewController called addThirdPhoto (Listing 16-16).
Listing 16-16 The addThirdPhoto IBAction
- (IBAction) addThirdPhoto: (id) sender {
static BOOL wasAdded;
if (!wasAdded) {
PhotosDAO * myPhotos = [[PhotosDAO alloc] init];
PhotoDAO * aPhoto = [[PhotoDAO alloc] init];
// Use the name of your photo in the next line
NSString * imgPath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:@"photo3.png"];
aPhoto.name = @"Another Photo";
aPhoto.photo = [[UIImage alloc] initWithContentsOfFile:imgPath];
[myPhotos addPhoto:aPhoto];
[self.photos release];
self.photos = [myPhotos getAllPhotos];
[myPhotos release];
wasAdded = YES;
}
}
4. Save your changes and open MyDBProjectViewController.xib and add a new Bar Button item to the toolbar. Change the bar button’s title to Add.
5. Connect the addThirdPhoto action to the Add button.
6. Save your changes and click Run to view the application in the iPhone Simulator (Figure 16-7).
Figure 16-7 Running the application with an add button
The addPhoto method (Listing 16-15) allows new photos to be inserted. To keep this example simple, the add button invokes the addThirdPhoto method that merely gets the photo from your resources group. The addPhoto method first creates a SQL string with parameters. The method then replaces the question marks by binding them to the appropriate value. For instance, the name column is text, so addPhoto binds it to a C string. The UIImage is binary, so it is bound to a blob. After binding, addPhoto then inserts the record by calling the sqlite3_step method. This method is called only once, as no data is returned from the insert statement. Notice, for brevity, an examination of the return code is omitted, as is other error handling from Listing 16-6 forward.
1. Return to the Xcode project.
2. Add another photo to your Resources folder and remember its name to use in the changePhotosImage method added in Step 5.
3. Add a new NSInteger called currentID to MyDBProjectViewController.m. Change the changeImage method to update this new variable with the current photo’s id from the database (Listing 16-17).
Listing 16-17 The currentID variable, and modified changeImage
NSInteger currentID = 0;
- (IBAction) changeImage: (id) sender {
static NSInteger currentElement = 0;
if(++currentElement == [self.photos count])
currentElement = 0;
PhotoDAO * aPhoto = (PhotoDAO *)
[self.photos objectAtIndex: currentElement];
currentID = aPhoto.photoID;
[self.theLabel setText:aPhoto.name];
[self.theImageView setImage:aPhoto.photo];
}
4. Add a new method called changeAPhotoImage to PhotosDAO (Listing 16-18).
Listing 16-18 The changeAPhotoImage method
- (void) changeAPhotoImage: (UIImage *) image theID: (NSInteger) photoID {
const char * sql = "update photos set photo = ? where id = ?";
sqlite3_stmt *update_statement = nil;
NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString * docsDir = [paths objectAtIndex:0];
NSString * thePath =
[docsDir stringByAppendingPathComponent:@"myDatabase.sqlite"];
sqlite3_open([thePath UTF8String], &database);
sqlite3_prepare_v2(database, sql, -1, &update_statement, NULL);
NSData * binData = UIImagePNGRepresentation(image);
sqlite3_bind_blob(update_statement, 1, [binData bytes],
[binData length],SQLITE_TRANSIENT);
sqlite3_bind_int(update_statement, 2, photoID);
sqlite3_step(update_statement);
sqlite3_finalize(update_statement);
sqlite3_close(database);
}
5. Add a new IBAction called changePhotosImage to MyDBProjectViewController (Listing 16-19). Save your changes.
Listing 16-19 The changePhotosImage method
-(IBAction) changePhotosImage: (id) sender {
PhotosDAO * myPhotos = [[PhotosDAO alloc] init];
NSString * imgPath = [[[NSBundle mainBundle] resourcePath]
stringByAppendingPathComponent:@"photo4.png"];
[myPhotos changeAPhotoImage:[[UIImage alloc] initWithContentsOfFile:
imgPath] theID: currentID];
[self.photos release];
self.photos = [myPhotos getAllPhotos];
[myPhotos release];
}
6. Open MyDBProjectViewController.xib and add another bar button to the toolbar. Change the button’s title to Change.
7. Connect the changePhotosImage action to the Change button.
8. Save and exit Interface Builder. Click Build And Go to run the application in the iPhone Simulator (Figure 16-8).
Figure 16-8 Changing the image
Updating a record is as straightforward as inserting it. The changeAPhotoImage first creates a SQL string with parameters. It then binds a file’s binary data to photo and an integer to id. After binding, it then calls the step function once, finalizes the statement, and closes the database. Notice that updating requires the record’s id, as SQLite uses the id to update the correct record. To accommodate this requirement, you added a currentID variable and changed the changeImage method to set the currentID with the currently selected photo record.
1. Quit the application and return to Xcode.
2. Add a new method called deletePhoto to PhotosDAO (Listing 16-20).
Listing 16-20 The deletePhoto method in PhotosDAO
- (void) deletePhoto: (NSInteger) photoID {
const char * sql = "delete from photos where id = ?";
sqlite3_stmt *delete_statement = nil;
NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
NSUserDomainMask, YES);
NSString * docsDir = [paths objectAtIndex:0];
NSString * thePath = [docsDir stringByAppendingPathComponent:
@"myDatabase.sqlite"];
sqlite3_open([thePath UTF8String], &database);
sqlite3_prepare_v2(database, sql, -1, &delete_statement, NULL);
sqlite3_bind_int(delete_statement, 1, photoID);
sqlite3_step(delete_statement);
sqlite3_finalize(delete_statement);
sqlite3_close(database);
}
3. Create a new IBAction called deletePhoto to MyDBProjectViewController (Listing 16-21).
Listing 16-21 The deletePhoto IBAction in MyDBProjectViewController
- (IBAction) deletePhoto : (id) sender {
PhotosDAO * myPhotos = [[PhotosDAO alloc] init];
[myPhotos deletePhoto:currentID];
[self.photos release];
self.photos = [myPhotos getAllPhotos];
currentElement = 0;
[myPhotos release];
}
4. Move the static NSInteger currentElement from the changeImage method in Listing 16-11 to just below the currentID variable (Listing 16-22). Remove the static qualifier.
Listing 16-22 Placing currentElement at the class’s top so it’s shared in the class
@implementation MyDBProjectViewController @synthesize photos; @synthesize theImageView; @synthesize theLabel; NSInteger currentID = 0; NSInteger currentElement = 0;
5. Save your changes and open MyDBProjectViewController.xib.
6. Add another button to the toolbar and change its title to Delete.
7. Resize the image and move the label to above the toolbar, as you are running out of space on the toolbar (Figure 16-9).
8. Connect the button to the deletePhoto action.
9. Click Run and try deleting a photo.
Figure 16-9 The MyDBProjectViewController view’s canvas
The delete statement follows the same pattern as insert and update. The only real difference is the SQL string.
const char * sql = "delete from photos where id = ?";
Summary
If your application needs to store and retrieve complex object hierarchies, you’ll want to consider using CoreData, which we’ll be covering in the next chapter. However, for simple data storage needs, or for a database that you can use in a non-iOS application, SQLite is an efficient, powerful solution.
This chapter covered all of the basics for adding a database to your application. You learned how to create a new database, embed it in your application and retrieve values from the database. Then you learned how to make the database writable and implement insert, update, and delete operations.
If you do much programming for iOS, you will quickly find that all but the simplest applications need to store data. For that reason, the techniques that you learned in this chapter are very important. You will need to understand and master the techniques of this chapter or the next one on CoreData if you’re going to do any significant iOS development.