raw sqlite vs fmdb

gistfile1.md

Small gist to discuss SQLite options. The example is extracted from AeroDoc where we have a list of My Leads which are locally saved.

FMDB create/insert/select

From github, fmdb is well maintained.

The code trial is available from AGIOS-25.read.write.with.fmdb branch in my AeroDoc-ios repo.

    // Get path from bundle doc space
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *path = [docsPath stringByAppendingPathComponent:@"aerodoc.sqlite"];
    
    FMDatabase *database = [FMDatabase databaseWithPath:path];
    
    //open DB
    [database open];
    
    // Create table if not already there
    [database executeUpdate:@"create table lead(name text, location text, phoneNumber text, saleAgent text);"];
    
    // Insert1: Building the string ourself
    NSString *query = [NSString stringWithFormat:@"insert into lead values ('%@', '%@', '%@', '%@')",
                       @"name1", @"location1", @"111", @"sa1"];
    [database executeUpdate:query];
    
    // Insert2: Let fmdb do the work
    [database executeUpdate:@"insert into lead values (?, ?, ?, ?)",
      @"name2", @"location2", @"222", @"sa2", nil];
    
    // Query and get an Array of Map
    NSMutableArray *results = [NSMutableArray array];   
    FMResultSet *myResults = [database executeQuery:@"select oid, name, location, phoneNumber, saleAgent from lead"];
    while ([myResults next]) {
        [results addObject:[myResults resultDictionary]];
    }
    
    // Close DB and release resources
    [database close];

sqlite3 API: select

The code trial is available from .AGIOS-25.read.with.sqlite.API branch in my AeroDoc-ios repo. Here I focus only on reading data. I haven't coded yet insert/create statement. as I haven't done that part you need to move the resource/aerodoc.sql3 into your iPhoneSimulator doc directory if you want to read a database with data.

// Query and get an Array of Map
- (NSArray *)retrieveLeads {
    
    NSMutableArray *retval = [[NSMutableArray alloc] init];

    NSString *query = @"SELECT oid, name, location, phoneNumber, saleAgent FROM lead";
    sqlite3_stmt *statement;
    int retCode = sqlite3_prepare_v2(_database, [query UTF8String], -1, &statement, nil);
    if (retCode == SQLITE_OK) {
        NSMutableDictionary *dict = nil;
        while (sqlite3_step(statement) == SQLITE_ROW) {
            NSUInteger num_cols = (NSUInteger)sqlite3_data_count(statement);
            if (num_cols > 0) {
                dict = [NSMutableDictionary dictionaryWithCapacity:num_cols];
                int columnIdx = 0;
                for (columnIdx = 0; columnIdx < num_cols; columnIdx++) {
                    NSString *columnName = [NSString stringWithUTF8String:sqlite3_column_name(statement, columnIdx)];
                    
                    if (sqlite3_column_type(statement, columnIdx) == SQLITE_NULL || (columnIdx < 0)) {
                        return nil;
                    }
                    
                    const char *c = (const char *)sqlite3_column_text(statement, columnIdx);
                    
                    if (!c) {
                        return nil;
                    }
                    // TODO need to deal with all types
                    NSString *objectValue = [NSString stringWithUTF8String:c];
                    [dict setObject:objectValue forKey:columnName];
                }
            } 
         [retval addObject:dict];
        }
        return retval;
    } else {
        return nil;
    }
}

Conclusion

Just by the lean code I see an advantage using FMDB. Easy to use jdbc like style with database resultset. Easy way of getting resultset returned as Array of Map. Good support for all SQLLite types.

TL;DR I think FMDB fullfills our needs to implement SQLITE store in an easy way to implement.

Just discovered this response on Stackoverflow that I found interesting: http://stackoverflow.com/questions/8723923/core-data-vs-sqlite-or-fmdb#answer-16149607

References

• interesting article on when to use SQLite instead of Core Data: http://www.objc.io/issue-4/SQLite-instead-of-core-data.html