FMResultSet用来执行SQL语句结果的查询

FMDatabaseQueue用不同的线程来执行若干个查询和更新操作

FMDatabasePool和FMDatabaseQueue类似，但是只是用在数据库只读操作上

FMResultSet

主要是根据columnName列名/columnIdx列索引获取到相应数据格式的数据，以及使用- next方法，将查询结果逐行输入

初始化

1
2
3
4
5
6
7
8
9
10
11
12
+ (instancetype)resultSetWithStatement:(FMStatement *)statement usingParentDatabase:(FMDatabase*)aDB {
    // 初始化FMResultSet对象
    FMResultSet *rs = [[FMResultSet alloc] init];
    // 设置prepared语句和db
    [rs setStatement:statement];
    [rs setParentDB:aDB];
    // 设置prepared语句正在使用
    NSParameterAssert(![statement inUse]);
    [statement setInUse:YES]; // weak reference
    
    return FMDBReturnAutoreleased(rs);
}

获取结果

返回结果的数据类型有很多种：除了基本数据类型之外，还有NSString、NSDate、NSData

比如以下两个接口- intForColumn:实际上调用的还是- intForColumnIndex:

1
2
3
4
5
6
7
- (int)intForColumn:(NSString*)columnName {
    return [self intForColumnIndex:[self columnIndexForName:columnName]];
}

- (int)intForColumnIndex:(int)columnIdx {
    return sqlite3_column_int([_statement statement], columnIdx);
}

通过调用- columnIndexForName:方法，用列名来获得列索引，这层映射关系都是存在_columnNameToIndexMap字典里

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
- (int)columnIndexForName:(NSString*)columnName {
    columnName = [columnName lowercaseString];
    
    NSNumber *n = [[self columnNameToIndexMap] objectForKey:columnName];
    
    if (n) {
        return [n intValue];
    }
    
    NSLog(@"Warning: I could not find the column named '%@'.", columnName);
    
    return -1;
}

- (NSMutableDictionary *)columnNameToIndexMap {
    if (!_columnNameToIndexMap) {
        int columnCount = sqlite3_column_count([_statement statement]);
        _columnNameToIndexMap = [[NSMutableDictionary alloc] initWithCapacity:(NSUInteger)columnCount];
        int columnIdx = 0;
        for (columnIdx = 0; columnIdx < columnCount; columnIdx++) {
            // 列名作为key，对应的列号作为value
            [_columnNameToIndexMap setObject:[NSNumber numberWithInt:columnIdx]
                                      forKey:[[NSString stringWithUTF8String:sqlite3_column_name([_statement statement], columnIdx)] lowercaseString]];
        }
    }
    return _columnNameToIndexMap;
}

逐行返回结果

- next方法本质是调用- nextWithError，FMDB里面有的注释写的还是挺逗的，连wtf都有…哈哈

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
- (BOOL)nextWithError:(NSError **)outErr {
    /* Call sqlite3_step() to run the virtual machine. Since the SQL being
     ** executed is not a SELECT statement, we assume no data will be returned.
     */
    // sqlite3_prepare函数将SQL命令字符串解析并转换为一系列的命令字节码，这些字节码最终被传送到SQlite3的虚拟数据库引擎（VDBE: Virtual Database Engine）中执行，完成这项工作的是sqlite3_step函数。比如一个SELECT查询操作，sqlite3_step函数的每次调用都会返回结果集中的其中一行，直到再没有有效数据行了。每次调用sqlite3_step函数如果返回SQLITE_ROW，代表获得了有效数据行，可以通过sqlite3_column函数提取某列的值。如果调用sqlite3_step函数返回SQLITE_DONE，则代表prepared语句已经执行到终点了，没有有效数据了。很多命令第一次调用sqlite3_step函数就会返回SQLITE_DONE，因为这些SQL命令不会返回数据。对于INSERT，UPDATE，DELETE命令，会返回它们所修改的行号——一个单行单列的值。
    /**
     SQLITE_BUSY 数据库文件有锁
     SQLITE_LOCKED 数据库中的某张表有锁
     SQLITE_DONE sqlite3_step()执行完毕
     SQLITE_ROW sqlite3_step()获取到下一行数据
     SQLITE_ERROR 一般用于没有特别指定错误码的错误，就是说函数在执行过程中发生了错误，但无法知道错误发生的原因。
     SQLITE_MISUSE 没有正确使用SQLite接口，比如一条语句在sqlite3_step函数执行之后，没有被重置之前，再次给其绑定参数，这时bind函数就会返回SQLITE_MISUSE。
     **/
    int rc = sqlite3_step([_statement statement]);
    
    if (SQLITE_BUSY == rc || SQLITE_LOCKED == rc) {
        NSLog(@"%s:%d Database busy (%@)", __FUNCTION__, __LINE__, [_parentDB databasePath]);
        NSLog(@"Database busy");
        if (outErr) {
            *outErr = [_parentDB lastError];
        }
    }
    else if (SQLITE_DONE == rc || SQLITE_ROW == rc) {
        // all is well, let's return.
    }
    else if (SQLITE_ERROR == rc) {
        NSLog(@"Error calling sqlite3_step (%d: %s) rs", rc, sqlite3_errmsg([_parentDB sqliteHandle]));
        if (outErr) {
            *outErr = [_parentDB lastError];
        }
    }
    else if (SQLITE_MISUSE == rc) {
        // uh oh.
        NSLog(@"Error calling sqlite3_step (%d: %s) rs", rc, sqlite3_errmsg([_parentDB sqliteHandle]));
        if (outErr) {
            if (_parentDB) {
                *outErr = [_parentDB lastError];
            }
            else {
                // If 'next' or 'nextWithError' is called after the result set is closed,
                // we need to return the appropriate error.
                NSDictionary* errorMessage = [NSDictionary dictionaryWithObject:@"parentDB does not exist" forKey:NSLocalizedDescriptionKey];
                *outErr = [NSError errorWithDomain:@"FMDatabase" code:SQLITE_MISUSE userInfo:errorMessage];
            }
            
        }
    }
    else {
        // wtf?
        NSLog(@"Unknown error calling sqlite3_step (%d: %s) rs", rc, sqlite3_errmsg([_parentDB sqliteHandle]));
        if (outErr) {
            *outErr = [_parentDB lastError];
        }
    }
    
    
    if (rc != SQLITE_ROW) {
        [self close];
    }
    
    return (rc == SQLITE_ROW);
}

使用：

1
2
3
4
while ([rs next]) {
    NSString *file = [rs stringForColumn:@"file"];
    NSLog(@"database_list: %@", file);
}

FMDatabaseQueue

是用来多线程并行执行SQL语句

初始化

头文件里提供了很多种初始化方法，但是最终调用的还是(instancetype)initWithPath:(NSString*)aPath flags:(int)openFlags vfs:(NSString *)vfsName方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
- (instancetype)initWithPath:(NSString*)aPath flags:(int)openFlags vfs:(NSString *)vfsName {
    
    self = [super init];
    
    if (self != nil) {
        // 根据指定路径寻找数据库，如果已存在则返回db，不存在则创建
        _db = [[[self class] databaseClass] databaseWithPath:aPath];
        FMDBRetain(_db);
        
#if SQLITE_VERSION_NUMBER >= 3005000
        BOOL success = [_db openWithFlags:openFlags vfs:vfsName];
#else
        BOOL success = [_db open];
#endif
        if (!success) {
            NSLog(@"Could not create database queue for path %@", aPath);
            FMDBRelease(self);
            return 0x00;
        }
        
        _path = FMDBReturnRetained(aPath);
        // 创建串行队列
        _queue = dispatch_queue_create([[NSString stringWithFormat:@"fmdb.%@", self] UTF8String], NULL);
        // 给_queue队列指定了kDispatchQueueSpecificKey字符串，并和self绑定，后面可以通过kDispatchQueueSpecificKey获取到self，但要保证正在执行的队列是这个_queue
        dispatch_queue_set_specific(_queue, kDispatchQueueSpecificKey, (__bridge void *)self, NULL);
        _openFlags = openFlags;
        _vfsName = [vfsName copy];
    }
    
    return self;
}

使用：

1
FMDatabaseQueue *queue = [FMDatabaseQueue databaseQueueWithPath:dbPath];

操作数据库

想要多线程操作数据库时，不是直接使用FMDatabase对象，而是通过FMDatabaseQueue对象，调用- inDatabas:，通过block返回FMDatabase对象来操作，虽然- inDatabas:内是同步操作，但是线程之间是并行的，就相当于马路上有两条单行的路，虽然每条路上的车都是一辆接一辆的，但是两条路之间是互不影响的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
- (void)inDatabase:(void (^)(FMDatabase *db))block {
    // 通过kDispatchQueueSpecificKey来获取当前正在执行的队列，并且检查和self做比较，确保没有发生死锁，因为可以创建多个FMDatabaseQueue多个来执行不同的SQL语句
    FMDatabaseQueue *currentSyncQueue = (__bridge id)dispatch_get_specific(kDispatchQueueSpecificKey);
    assert(currentSyncQueue != self && "inDatabase: was called reentrantly on the same queue, which would lead to a deadlock");
    
    FMDBRetain(self);
    // 在当前queue中，同步执行block
    dispatch_sync(_queue, ^() {
        
        FMDatabase *db = [self database];
        block(db);
        
        if ([db hasOpenResultSets]) {
            NSLog(@"Warning: there is at least one open result set around after performing [FMDatabaseQueue inDatabase:]");
            
#if defined(DEBUG) && DEBUG
            NSSet *openSetCopy = FMDBReturnAutoreleased([[db valueForKey:@"_openResultSets"] copy]);
            for (NSValue *rsInWrappedInATastyValueMeal in openSetCopy) {
                FMResultSet *rs = (FMResultSet *)[rsInWrappedInATastyValueMeal pointerValue];
                NSLog(@"query: '%@'", [rs query]);
            }
#endif
        }
    });
    
    FMDBRelease(self);
}

使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[queue inDatabase:^(FMDatabase *adb) {
    [adb executeUpdate:@"create table qfoo (foo text)"];
    [adb executeUpdate:@"insert into qfoo values ('hi')"];
    [adb executeUpdate:@"insert into qfoo values ('hello')"];
    [adb executeUpdate:@"insert into qfoo values ('not')"];

	int count = 0;
    FMResultSet *rsl = [adb executeQuery:@"select * from qfoo where foo like 'h%'"];
    while ([rsl next]) {
        count++;
    }

    FMDBQuickCheck(count == 2);

    count = 0;
    rsl = [adb executeQuery:@"select * from qfoo where foo like ?", @"h%"];
    while ([rsl next]) {
        count++;
    }

    FMDBQuickCheck(count == 2);
}];

事务处理

和FMDatabase一样，也有事务的处理，分为exclusive事务和deferred事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
- (void)inDeferredTransaction:(void (^)(FMDatabase *db, BOOL *rollback))block {
    [self beginTransaction:YES withBlock:block];
}

- (void)inTransaction:(void (^)(FMDatabase *db, BOOL *rollback))block {
    [self beginTransaction:NO withBlock:block];
}

- (void)beginTransaction:(BOOL)useDeferred withBlock:(void (^)(FMDatabase *db, BOOL *rollback))block {
    FMDBRetain(self);
    dispatch_sync(_queue, ^() { 
        
        BOOL shouldRollback = NO;
        // 是否使用延迟事务
        if (useDeferred) {
            [[self database] beginDeferredTransaction];
        }
        else {
            [[self database] beginTransaction];
        }
        // 如果数据库操作出错了，你可以设置是否需要回滚，回滚到操作之前的内容
        block([self database], &shouldRollback);
        
        if (shouldRollback) {
            [[self database] rollback];
        }
        else {
            // 如果不需要回滚，则commit提交相应的sql操作
            [[self database] commit];
        }
    });
    
    FMDBRelease(self);
}

使用：

1
2
3
4
5
6
7
8
9
10
[queue inTransaction:^(FMDatabase *adb, BOOL *rollback) {
    NSLog(@"Starting query  %ld", nby);

    FMResultSet *rsl = [adb executeQuery:@"select * from qfoo where foo like 'h%'"];
    while ([rsl next]) {
        ;// whatever.
    }

    NSLog(@"Ending query    %ld", nby);
}];

FMDatabasePool

If you really really really know what you’re doing and FMDatabasePool is what you really really need (ie, you’re using a read only database), OK you can use it. But just be careful not to deadlock!

对，这就是FMDatabasePool的描述，只能用于数据库只读操作，如果进行了写操作，很有可能会出现死锁，乱用搞不好就跪了…

里面的方法基本和FMDatabaseQueue差不多，里面有两个特别的属性：_databaseInPool和_databaseOutPool，按我的理解，这两个属性的作用就像是一个用来存放闲置的db，一个是用来存放正在执行操作的db，在- inDatabase:等这些方法最后都调用了- pushDatabaseBackInPool:方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
// 将db放回InPool里面
- (void)pushDatabaseBackInPool:(FMDatabase*)db {
    
    if (!db) { // db can be null if we set an upper bound on the # of databases to create.
        return;
    }
    // 同步执行
    [self executeLocked:^() {
        // 如果InPool数组里面包含db，说明db已经在InPool里面，不需要再放回InPool里，并且抛出异常，下面的操作就不进行了
        if ([self->_databaseInPool containsObject:db]) {
            [[NSException exceptionWithName:@"Database already in pool" reason:@"The FMDatabase being put back into the pool is already present in the pool" userInfo:nil] raise];
        }
        // 如果db不在InPool里面，把db加到InPool数组，并且从OutPool中移除
        [self->_databaseInPool addObject:db];
        [self->_databaseOutPool removeObject:db];
        
    }];
}

- (FMDatabase*)db {
    
    __block FMDatabase *db;
    
    
    [self executeLocked:^() {
        // 从_databaseInPool里面取出最后一个FMDatabase对象
        db = [self->_databaseInPool lastObject];
        
        BOOL shouldNotifyDelegate = NO;
        // 如果db存在，则加到_databaseOutPool里，_databaseInPool移除掉，我的理解是_databaseOutPool是用于存放正在执行操作的db池，_databaseInPool则存放闲置的db池
        // 在需要使用的时候，从闲置的池里面取出来放到正在执行的池里面
        if (db) {
            [self->_databaseOutPool addObject:db];
            [self->_databaseInPool removeLastObject];
        }
        else {
            // 检查最大创建的db数量，超过了则返回，否则就根据路径，找到db
            if (self->_maximumNumberOfDatabasesToCreate) {
                NSUInteger currentCount = [self->_databaseOutPool count] + [self->_databaseInPool count];
                
                if (currentCount >= self->_maximumNumberOfDatabasesToCreate) {
                    NSLog(@"Maximum number of databases (%ld) has already been reached!", (long)currentCount);
                    return;
                }
            }
            
            db = [[[self class] databaseClass] databaseWithPath:self->_path];
            shouldNotifyDelegate = YES;
        }
        // 根据_openFlags和_vfsName打开db
        //This ensures that the db is opened before returning
#if SQLITE_VERSION_NUMBER >= 3005000
        BOOL success = [db openWithFlags:self->_openFlags vfs:self->_vfsName];
#else
        BOOL success = [db open];
#endif
        if (success) {
            // 如果代理方法响应了，但是db不允许被加到pool里面，那么db关闭释放
            if ([self->_delegate respondsToSelector:@selector(databasePool:shouldAddDatabaseToPool:)] && ![self->_delegate databasePool:self shouldAddDatabaseToPool:db]) {
                [db close];
                db = 0x00;
            }
            else {
                //It should not get added in the pool twice if lastObject was found
                // 对于新创建的db，需要加到_databaseOutPool里，而不需要加到_databaseInPool里
                if (![self->_databaseOutPool containsObject:db]) {
                    [self->_databaseOutPool addObject:db];
                    // 新创建的db需要响应delegate
                    if (shouldNotifyDelegate && [self->_delegate respondsToSelector:@selector(databasePool:didAddDatabase:)]) {
                        [self->_delegate databasePool:self didAddDatabase:db];
                    }
                }
            }
        }
        else {
            NSLog(@"Could not open up the database at path %@", self->_path);
            db = 0x00;
        }
    }];
    
    return db;
}