Supporting Classes - iOS

Configuration

Before using SQLite with iOS, it's necessary for me to add a library to my application: libsqlite3.dylib. This is done by selecting the FutileFishing target, and then adding the library. When done, the project should look as follows:

Note: When adding this library, you may also see an entry named libsqlite3.0.dylib. As explained in this Stack Exchange question, you should not select the 3.0 entry.

Now that the project is configured to use SQLite, it's time to write the code.

AbstractServiceDB

Much like the prior article, there's some general purpose behavior that's common to all DB services. I'll place this in an abstract base class named AbstractServiceDB:

AbstractServiceDB.h

  1. #import <Foundation/Foundation.h>
  2. #import <sqlite3.h>
  3.  
  4. @interface AbstractServiceDB : NSObject {
  5. @protected
  6. sqlite3 *mDb;
  7. }
  8.  
  9. @property NSString *userId;
  10.  
  11. -(BOOL)open;
  12. -(void)close;
  13.  
  14. -(NSString *)getSqlString: (sqlite3_stmt *) stmt atIndex: (int) idx;
  15.  
  16. @end

Comments:

  • Line 6: Declare a protected property for the local database.

AbstractServiceDB.m

  1. #import "AbstractServiceDB.h"
  2.  
  3. @implementation AbstractServiceDB {
  4. @private
  5. NSString *dbFileName;
  6. }
  7.  
  8. @synthesize userId;
  9.  
  10. -(id) init {
  11. self = [super init];
  12. if (self) {
  13. // Check to see if the database exists (These next two lines technically return a single directory)
  14. NSArray *docDirectories = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
  15. NSString *docDirectory = [docDirectories objectAtIndex:0];
  16.  
  17. dbFileName = [docDirectory stringByAppendingPathComponent:@"futilefishing.db"];
  18. NSFileManager *fManager = [NSFileManager defaultManager];
  19.  
  20. if ([fManager fileExistsAtPath: dbFileName] == NO) {
  21. // The database doesn't exist. Create it
  22. const char *charDbPath = [dbFileName UTF8String];
  23. int somevar = sqlite3_open(charDbPath, &mDb);
  24. if (somevar == SQLITE_OK) {
  25. char *err;
  26.  
  27. const char *DB_CREATE_USERS = "create table users (userid text primary key, username text)";
  28.  
  29. const char *DB_CREATE_LOCATIONS =
  30. "create table locations (id INTEGER PRIMARY KEY, name text, author text, latitude real, longitude real, ispublic integer, comments text, createdlocally integer)";
  31.  
  32. const char *DB_CREATE_BAIT = "create table bait (id integer primary key, name text, author text, descr text, createdLocally integer, image blob)";
  33.  
  34. const char *DB_CREATE_FISH = "create table fish (id integer primary key, name text, author text, descr text, createdLocally integer, restrictions text)";
  35.  
  36. const char *DB_CREATE_CATCHES =
  37. "create table catches (id integer primary key, author text, catchdate integer, locationblurred integer, locationid integer, fishid integer, customlat real, customlng real, length real, weight real, comments text, createdLocally integer, imagepath text)";
  38.  
  39. if (sqlite3_exec(mDb, DB_CREATE_USERS, NULL, NULL, &err) != SQLITE_OK) {
  40. NSLog(@"DB Create error");
  41. }
  42.  
  43. if (sqlite3_exec(mDb, DB_CREATE_LOCATIONS, NULL, NULL, &err) != SQLITE_OK) {
  44. NSLog(@"DB Create error");
  45. }
  46.  
  47. if (sqlite3_exec(mDb, DB_CREATE_BAIT, NULL, NULL, &err) != SQLITE_OK) {
  48. NSLog(@"DB Create error");
  49. }
  50.  
  51. if (sqlite3_exec(mDb, DB_CREATE_FISH, NULL, NULL, &err) != SQLITE_OK) {
  52. NSLog(@"DB Create error");
  53. }
  54.  
  55. if (sqlite3_exec(mDb, DB_CREATE_CATCHES, NULL, NULL, &err) != SQLITE_OK) {
  56. NSLog(@"DB Create error");
  57. }
  58. sqlite3_close(mDb);
  59.  
  60. }
  61. }
  62. }
  63. return self;
  64. }
  65.  
  66. -(BOOL)open {
  67. const char *charDbPath = [dbFileName UTF8String];
  68. return (sqlite3_open(charDbPath, &mDb) == SQLITE_OK);
  69. }
  70. -(void)close {
  71. sqlite3_close(mDb);
  72. }
  73.  
  74. -(NSString *)getSqlString: (sqlite3_stmt *) stmt atIndex: (int) idx {
  75. char *foo = (char *)sqlite3_column_text(stmt, idx);
  76. if (foo == nil) {
  77. return @"";
  78. }
  79. return [NSString stringWithUTF8String:foo];
  80. }
  81.  
  82. @end

Comments:

  • Lines 10 - 65: Some necessary structural code to set up a database (and the associated tables) when the app is run for the first time. It's also possible to include a pre-populated database. And if the database had more than a handful of tables, that's exactly what I'd do. However, since this is an explanatory article, I thought I'd leave the more verbose approach in place.
  • Lines 14 - 20: Look in the iOS app-specific document directory and see if the database file exists. If so, no other action is required.
  • Lines 20 - 24: Create the database by issuing an sqlite3_open() call.
  • Lines 27 - 57: Define five tables: Users, Locations, Bait, Fish, and Catches. Log any errors that are encountered.
  • Line 58: Important! Close the database via an sqlite3_close() call.
  • Lines 66 - 72: Basic boilerplate code for opening (and closing) a connection to the database.
  • Lines 74 - 80: A helper function for returning a string instead of a nil when a value doesn't exist. (This is used by some of the concrete classes.)

The LocationService Protocol

There are no change to the LocationService protocol, but I'm displaying it below for parity. Also, the next section will provide a concrete implementation of the protocol, so it's convenient to have the protocol close by.

  1. #import <Foundation/Foundation.h>
  2. #import "FishingSpot.h"
  3. #import "ContentBrokerDelegate.h"
  4.  
  5. @protocol LocationService <NSObject>
  6.  
  7. -(BOOL) create:(FishingSpot *)x error:(NSError **)error;
  8. -(BOOL) update:(FishingSpot *)x error:(NSError **)error;
  9. -(BOOL) delete:(FishingSpot *)x error:(NSError **)error;
  10. -(BOOL) get:(long)x error:(NSError **)error;
  11. -(BOOL) getMany:(NSString *)criteria error:(NSError **)error;
  12.  
  13. -(void) setDelegate:(id<ContentBrokerDelegate>) delegate;
  14.  
  15. @end

Now that the superclass and the protocol have been described, it's time to move on to the database implementation details.