FMDB-一个sqlite的封装

FMDatabase* db = [FMDatabase databaseWithPath:@"/tmp/tmp.db"];
// open 和 close 是成對的 method
if (![db open]) {
  //Error handling -- can't open database
}

// 設定儲存 prepared statements
[db setShouldCacheStatements:YES];

// executeUpdate 用來執行
[db executeUpdate:@"create table test (a text, b text, c integer, d double, e double)"];

// 會執行 BEGIN EXCLUSIVE TRANSACTION 命令鎖住資料庫。
[db beginTransaction];
int i = 0;
while (i++ < 20) {
  // 類似 stringWithFormat: 的語法！
  [db executeUpdate:@"insert into test (a, b, c, d, e) values (?, ?, ?, ?, ?)" ,
         @"hi'", // 不需要把'寫成\'或是''
         [NSString stringWithFormat:@"number %d", i],
         [NSNumber numberWithInt:i],
         [NSDate date],
         [NSNumber numberWithFloat:2.2f]];
 }
 [db commit];
 [db close];

FMResultSet *rs = [db executeQuery:@"select rowid,* from test where a = ?", @"hi'"];
while ([rs next]) {

    NSLog(@"%d %@ %@ %@ %@ %f %f",
          [rs intForColumn:@"c"],
          [rs stringForColumn:@"b"],
          [rs stringForColumn:@"a"],
          [rs stringForColumn:@"rowid"],
          [rs dateForColumn:@"d"],
          [rs doubleForColumn:@"d"],
          [rs doubleForColumn:@"e"]);


    if (!([[rs columnNameForIndex:0] isEqualToString:@"rowid"] &&
          [[rs columnNameForIndex:1] isEqualToString:@"a"])
          ) {
        NSLog(@"WHOA THERE BUDDY, columnNameForIndex ISN'T WORKING!");
        return 7;
    }
}
[rs close];

// blob support.
// blob 處理的範例，這邊使用NSData包裝資料。
[db executeUpdate:@"create table blobTable (a text, b blob)"];

// let's read in an image from safari's app bundle.
NSData *safariCompass = [NSData dataWithContentsOfFile:@"/Applications/Safari.app/Contents/Resources/compass.icns"];
if (safariCompass) {
    [db executeUpdate:@"insert into blobTable (a, b) values (?,?)", @"safari's compass", safariCompass];

    rs = [db executeQuery:@"select b from blobTable where a = ?", @"safari's compass"];
    if ([rs next]) {
        safariCompass = [rs dataForColumn:@"b"];
        [safariCompass writeToFile:@"/tmp/compass.icns" atomically:NO];

        // let's look at our fancy image that we just wrote out..
        system("/usr/bin/open /tmp/compass.icns");

        // ye shall read the header for this function, or suffer the consequences.
        safariCompass = [rs dataNoCopyForColumn:@"b"];
        [safariCompass writeToFile:@"/tmp/compass_data_no_copy.icns" atomically:NO];
        system("/usr/bin/open /tmp/compass_data_no_copy.icns");
    }
    else {
        NSLog(@"Could not select image.");
    }

    [rs close];

}
else {
    NSLog(@"Can't find compass image..");
}

[db executeUpdate:@"create table t1 (a integer)"];
[db executeUpdate:@"insert into t1 values (?)", [NSNumber numberWithInt:5]];
int a = [db intForQuery:@"select a from t1 where a = ?", [NSNumber numberWithInt:5]];
if (a != 5) {
  NSLog(@"intForQuery didn't work (a != 5)");
}