Database Details - iOS

As with the prior article, it's necessary to create a service for nearly every business object. For simplicity, I've focused on the Location (FishingSpot) service, and have left the other service implementations as an exercise for the reader.

The concrete implementation of the LocationService protocol for databases is contained in the class LocationServiceDB:

LocationServiceDB.h

  1. #import "AbstractServiceDB.h"
  2. #import "LocationService.h"
  3.  
  4. @interface LocationServiceDB : AbstractServiceDB<LocationService>
  5.  
  6. @end

As with the web concrete class, nothing special exists in the header / interface file.

LocationServiceDB.m

  1. #import "LocationServiceDB.h"
  2. #import "ContentBroker.h"
  3.  
  4. @implementation LocationServiceDB {
  5. id<ContentBrokerDelegate> _delegate;
  6. }
  7.  
  8. -(void) setDelegate:(id<ContentBrokerDelegate>) delegate {
  9. _delegate = delegate;
  10. }
  11.  
  12. -(BOOL) create:(FishingSpot *)x error:(NSError **)error {
  13. [self open];
  14. ContentResponse *response = [[ContentResponse alloc] initWithRc:0 andDescr:@"Success" andAction:0];
  15.  
  16. sqlite3_stmt *stmt;
  17.  
  18. sqlite3_prepare_v2(mDb, "insert into locations (id, name, author, latitude, longitude, ispublic, comments, createdlocally) values (?, ?, ?, ?, ?, ?, ?, ?)", -1, &stmt, NULL);
  19. sqlite3_bind_null(stmt, 1);
  20. sqlite3_bind_text(stmt, 2, [x.name UTF8String], -1, SQLITE_TRANSIENT);
  21. sqlite3_bind_text(stmt, 3, [self.userId UTF8String], -1, SQLITE_TRANSIENT);
  22. sqlite3_bind_double(stmt, 4, [x latitude]);
  23. sqlite3_bind_double(stmt, 5, [x longitude]);
  24. sqlite3_bind_int(stmt, 6, [x isPublicSpot]);
  25. sqlite3_bind_text(stmt, 7, [[x comments] UTF8String], -1, SQLITE_TRANSIENT);
  26. sqlite3_bind_int(stmt, 8, 1);
  27. int res = sqlite3_step(stmt);
  28. if (res != SQLITE_DONE) {
  29. NSLog(@"Error on location insert: %i", res);
  30. }
  31. sqlite3_finalize(stmt);
  32.  
  33. [self close];
  34. NSMutableArray *items = [[NSMutableArray alloc] init];
  35. [items addObject:x];
  36. [response setItems:items];
  37. [response setAction: CB_CREATE_JSON];
  38. [_delegate contentLoaded: response];
  39. return YES;
  40. }
  41.  
  42. -(BOOL) update:(FishingSpot *)x error:(NSError **)error {
  43. [self open];
  44. ContentResponse *response = [[ContentResponse alloc] initWithRc:0 andDescr:@"Success" andAction:0];
  45.  
  46. sqlite3_stmt *stmt;
  47.  
  48. // Now, add the tour
  49. sqlite3_prepare_v2(mDb, "update locations set name=?, latitude=?, longitude=?, ispublic=?, createdlocally=?, comments=? where id=?", -1, &stmt, NULL);
  50. sqlite3_bind_text(stmt, 1, [[x name] UTF8String], -1, SQLITE_TRANSIENT);
  51. sqlite3_bind_double(stmt, 2, [x latitude]);
  52. sqlite3_bind_double(stmt, 3, [x longitude]);
  53. sqlite3_bind_int(stmt, 4, [x isPublicSpot]);
  54. sqlite3_bind_int(stmt, 5, 1);
  55. sqlite3_bind_text(stmt, 6, [[x comments] UTF8String], -1, SQLITE_TRANSIENT);
  56. sqlite3_bind_int(stmt, 7, (int)[x ID]);
  57. int res = sqlite3_step(stmt);
  58. if (res != SQLITE_DONE) {
  59. NSLog(@"Error on location update: %i", res);
  60. }
  61. sqlite3_finalize(stmt);
  62.  
  63. [self close];
  64. NSMutableArray *items = [[NSMutableArray alloc] init];
  65. [items addObject:x];
  66. [response setItems:items];
  67. [response setAction:CB_UPDATE];
  68. [_delegate contentLoaded: response];
  69. return YES;
  70. }
  71.  
  72. -(BOOL) delete:(FishingSpot *)x error:(NSError **)error {
  73. [self open];
  74. ContentResponse *response = [[ContentResponse alloc] initWithRc:0 andDescr:@"Success" andAction:0];
  75. [response setAction:CB_DELETE];
  76.  
  77. sqlite3_stmt *stmt;
  78.  
  79. sqlite3_prepare_v2(mDb, "delete from locations where id = ?", -1, &stmt, NULL);
  80. sqlite3_bind_int(stmt, 1, (int)[x ID]);
  81. int res = sqlite3_step(stmt);
  82. if (res != SQLITE_DONE) {
  83. NSLog(@"Error on location delete: %i", res);
  84. }
  85. sqlite3_finalize(stmt);
  86. [self close];
  87. [_delegate contentLoaded: response];
  88. return YES;
  89. }
  90.  
  91. -(BOOL) get:(long)x error:(NSError **)error {
  92. [self open];
  93. sqlite3_stmt *stmt;
  94. NSMutableArray *items = [[NSMutableArray alloc] init];
  95.  
  96. sqlite3_prepare_v2(mDb, "select id, name, author, latitude, longitude, ispublic, comments from locations order by name where id=?", -1, &stmt, NULL);
  97. sqlite3_bind_int(stmt, 1, (int)x);
  98.  
  99. if (sqlite3_step(stmt) == SQLITE_ROW) {
  100. FishingSpot *x = [self parseFishingSpot:stmt];
  101. [items addObject:x];
  102. }
  103. sqlite3_finalize(stmt);
  104. [self close];
  105.  
  106. ContentResponse *response = [[ContentResponse alloc] initWithRc:0 andDescr:@"Success" andAction:0];
  107. [response setItems:items];
  108. [response setAction:CB_READ];
  109. [_delegate contentLoaded: response];
  110. return YES;
  111. }
  112.  
  113. -(BOOL) getMany:(NSString *)criteria error:(NSError **)error {
  114. [self open];
  115. sqlite3_stmt *stmt;
  116. NSMutableArray *items = [[NSMutableArray alloc] init];
  117.  
  118. sqlite3_prepare_v2(mDb, "select id, name, author, latitude, longitude, ispublic, comments from locations order by name", -1, &stmt, NULL);
  119.  
  120. while (sqlite3_step(stmt) == SQLITE_ROW) {
  121. FishingSpot *x = [self parseFishingSpot:stmt];
  122. [items addObject:x];
  123. }
  124. sqlite3_finalize(stmt);
  125. [self close];
  126.  
  127. ContentResponse *response = [[ContentResponse alloc] initWithRc:0 andDescr:@"Success" andAction:0];
  128. [response setItems:items];
  129. [response setAction:CB_LIST];
  130. [_delegate contentLoaded: response];
  131. return YES;
  132. }
  133.  
  134. -(FishingSpot *) parseFishingSpot: (sqlite3_stmt *) stmt {
  135. FishingSpot *spot = [[FishingSpot alloc] init];
  136.  
  137. spot.ID = sqlite3_column_int(stmt, 0);
  138. NSString *val = [self getSqlString:stmt atIndex:1];
  139. spot.name = val;
  140. val = [self getSqlString:stmt atIndex:2];
  141. User *u = [[User alloc] init];
  142. [u setUserId:val];
  143. spot.owner = u;
  144.  
  145. spot.latitude = sqlite3_column_double(stmt, 3);
  146. spot.longitude = sqlite3_column_double(stmt, 4);
  147. spot.isPublicSpot = sqlite3_column_int(stmt, 5);
  148.  
  149. val = [self getSqlString:stmt atIndex:6];
  150. spot.comments = val;
  151.  
  152. return spot;
  153. }
  154.  
  155. @end

Comments:

  • Lines 8 - 10: It's possible to make all DB calls synchronously. However, to remain consistent with the web implementation, it's advantageous to notify a delegate when an operation has completed.
  • Lines 12 - 40: Create a new FishingSpot in the database. I'll provide additional detail for this method, but many of the same principals apply to all methods in this class.
    • Line 13: Open a connection to the database.
    • Line 18: I nearly always use prepared statements; this helps avoid SQL injection vulnerabilities. In this case, it's a simple SQL insert statement.
    • Line 19: Specifying NULL for the primary key will instruct SQLite to treat this as an autoincrement.
    • Line 20: Specify a string as one of the parameters to the prepared statement.
    • Line 22: Specify a double as one of the parameters to the prepared statement.
    • Line 24: Specify an integer as one of the parameters to the prepared statement.
    • Lines 27 - 31: Execute the SQL statement. Report any errors, and then finalize the statement.
    • Line 33: Close the connection to the database.
    • Lines 34 - 39: Assemble the results, and notify the delegate.
  • Lines 42 - 70: Update an existing FishingSpot in the database. This code is very similar to the create method.
  • Lines 72 - 89: Delete an existing FishingSpot from the database. This code is very similar to the create method. (Note: For production grade systems, it's necessary to preserve referential integrity; if a FishingSpot is referenced in a Catch then the user should not be able to delete the FishingSpot.)
  • Lines 91 - 111: Retrieve an existing FishingSpot from the database. On lines 99 - 102, the sqlite_step() method should return a value of SQLITE_ROW instead of SQLITE_DONE. If this is the case, parse out the retrieved data.
  • Lines 113 - 132: Retrieve all FishingSpot objects in the database. Note that this method iterates over the results as long as the step command returns SQLLITE_ROW.
  • Lines 134 - 153: Given an SQL row, parse out the contents of a FishingSpot. IMPORTANT:The index starts at 0 - even though the sqlite3_bind_*() methods start their index at 1.

This completes the implementation of the concrete database class. With this in place, it's time to update the main code and the ServiceFactory to handle toggling between online and offline mode.