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, SQLite is a C library included when compiling a program. 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 iPhone’s embedded 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 using its C programming interface.
Figure 16-1 Adding SQLite Manager to Firefox
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.
Figure 16-3 Adding a record using SQLite Manager
NOTE On my computer, adding binary data using SQLite Manager is buggy. Sometimes it works, sometimes not. If getting the file’s content added is problematic, a workaround is to add the record and insert Null for the blob’s value. After inserting, update the row, and add the file’s content. Updating the blob seems more stable in SQLite Manager.
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 an 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.
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 #define SQLITE_ERROR #define SQLITE_READONLY database */ #define SQLITE_INTERRUPT #define SQLITE_IOERR occurred */ #define SQLITE_CANTOPEN #define SQLITE_MISMATCH #define SQLITE_ROW ready */ #define SQLITE_DONE executing */ 0 /* Successful result */ 1 /* SQL error or missing database */ 8/* Attempt to write a readonly 9/* Operation terminated by 10/* Some kind of disk I/O error 14 /* Unable to open the database file */ 20 /* Data type mismatch */ 100/* sqlite3_step() has another row 101/* sqlite3_step() has finished
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 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);
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, const char *zSql, int nByte, sqlite3_stmt **ppStmt, /* OUT: Statement handle */ const char **pzTail /* OUT: Pointer to unused portion of zSql */ ); /* Database handle */ /* SQL statement, UTF-8 encoded */ /* Maximum length of zSql in bytes. */
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.
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 }
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); Opening and Querying a Database
Listing 16-6 PhotoDAO.h #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 Listing 16-7 PhotoDAO.m #import "PhotoDAO.h" @implementation PhotoDAO @synthesize name; @synthesize photoID; @synthesize photo; - (void) dealloc { [name release]; [photo release]; [super dealloc]; } @end Listing 16-8 PhotosDAO.h #import <Foundation/Foundation.h> #import <sqlite3.h> #import "PhotoDAO.h" @interface PhotosDAO : NSObject { sqlite3 *database; } - (NSMutableArray *) getAllPhotos; @end Listing 16-9 PhotosDAO.m #import "PhotosDAO.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, normally 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, normally 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 Listing 16-10 MyDBProjectViewController.h #import <UIKit/UIKit.h> #import "PhotoDAO.h"; #import "PhotosDAO.h"; @interface MyDBProjectViewController : UIViewController { NSMutableArray * photos; IBOutlet UIImageView * theImageView; IBOutlet UILabel * theLabel; } @property (nonatomic, retain) NSMutableArray * photos; @property (nonatomic, retain) IBOutlet UIImageView * theImageView; @property (nonatomic, retain) IBOutlet UILabel * theLabel; - (IBAction) changeImage: (id) sender; @end Listing 16-11 MyDBProjectViewController.m #import "MyDBProjectViewController.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
Figure 16-4 Adding a UIImageView and a UIToolBar to the view’s canvas
NOTE You should never load an entire database at once when creating a real application, especially when using a large blob, like this example. Only load what you need. But for simplicity, you loaded all records in this task.
Figure 16-5 Running the application on my iphone Figure 16-6 Running the application on my iPhone
(first image) (secondimage)
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.
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. NSFileManager *fileManager = [NSFileManager defaultManager]; NSString *theDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent: @"chapter18.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.
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.
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).
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 must also usually 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.
You liked the article?
Like : 0
Vote for difficulty
Current difficulty (Avg): Medium
1/15
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
Stay Updated
Get stories of change makers and innovators from the startup ecosystem in your inbox