SQLite小记

静以修身，俭以养德

原文链接

一、移动端数据库方案

1、关系型数据库

• SQLite：轻量级的关系数据库, 占用资源非常少，目前广泛应用于Android、iOS等手机操作系统。iOS使用时SQLite，只需要加入libSQLite3.0.tbd依赖以及引入SQLite3.h头文件即可。
• Apple内建的CoreData底层的持久化方式可以是SQLite数据库，也可以是XML文件、甚至是内存; 比较流行的第三方框架FMDB是对SQLite操作的封装

2、非关系数据库

• Realm：适用于iOS (同样适用于Swift&Objective-C)和Android的跨平台移动数据库，是NoSQL框架，官方定位是取代SQLite。具体可参考Realm(Java)那些事

• Realm非常的特色是数据变更通知，查询，存储性能比SQLite好，但是体积大、存入Realm的对象必须继承RealmObject，侵入性强，Realm中存储对象不允跨线程访问

• 非关系型数据库还有LevelDB、RocksDB

3、其他

• 16年左右，Realm兴起，部分客户端团队开始使用Realm，但是更多的团队还是继续使用SQLite及其衍生方案；使用Realm并非就一定Cool，使用SQLite并非就一定Out，方案的选择应该是基于业务本身和团队的技术积累。
• 在16年时候，微信分享了自己对优化SQLite的源码，具体可见微信iOS SQLite源码优化实践，随后推出了自己的数据库方案WCDB(基于SQLite)

二、SQLite的线程模式

1、三种线程模式

• 单线程模式（Single-thread）：所有互斥锁都被禁用，SQLite连接不能在多个线程中使用（多线程使用不安全）。
• 多线程模式（Multi-thread）：在多线程中使用单个数据库连接是不安全的，否则就是安全的 (不能在多个线程中共享数据库连接)
• 串行模式（Serialized），是线程安全的（即使在多个线程中不加互斥的使用同一个数据库连接）。

说明：线程模式可以在编译时（通过源码编译SQLite库时）、启动时（使用SQLite的应用程序初始化时）或者运行时（创建数据库连接时）来指定。一般而言，运行时指定的模式将覆盖启动时的指定模式，启动时指定的模式将覆盖编译时指定的模式。但是，单线程模式一旦被指定，将无法被覆盖。默认的线程模式是串行模式。

2、编译时选择线程模式

• 通过定义SQLite_THREADSAFE宏来指定线程模式。如果没有指定，默认为串行模式。

//0：单线程模式；
//1：串行模式;
//2：多线程模式

• SQLite3_threadsafe()返回值可以确定编译时指定的线程模式。如果指定了单线程模式，函数返回false。如果指定了串行或者多线程模式，函数返回true。
• 由于SQLite3_threadsafe()函数要早于多线程模式以及启动时和运行时的模式选择，所以它既不能区别多线程模式和串行模式，也不能区别启动时和运行时的模式。

//FMDB 中代码
+ (BOOL)isSQLiteThreadSafe {
    // make sure to read the SQLite headers on this guy!
    return SQLite3_threadsafe() != 0;
}

• 如果编译时指定了单线程模式，那么临界互斥逻辑在构造时就被省略，因此也就无法在启动时或运行时指定串行模式或多线程模式。

3、启动时选择线程模式

• 假如在编译时没有指定单线程模式，就可以在应用程序初始化时使用SQLite3_config()函数修改线程模式。

  SQLite_CONFIG_SINGLETHREAD  //单线程模式
  SQLite_CONFIG_MULTITHREAD   //多线程模式
  SQLite_CONFIG_SERIALIZED    //串行模式
  

4、运行时选择线程模式

• 如果没有在编译时 和 启动时指定为单线程模式，那么每个数据库连接在创建时，可单独的被指定为多线程模式或者串行模式，但是不能指定为单线程模式。
• 如果在编译时或启动时指定为单线程模式，就无法在创建连接时指定多线程或者串行模式。
• 创建连接时可以用SQLite3_open_v2()函数的第三个参数来指定线程模式。

SQLite_OPEN_NOMUTEX    //创建多线程模式的连接(没有指定单线程模式的情况下)
SQLite_OPEN_FULLMUTEX  //创建串行模式的连接

5、模式的选择和处理

要保证数据库使用安全，一般可以采用如下几种模式

• SQLite 采用单线程模型，用专门的线程（同时只能有一个任务执行访问） 进行访问
• SQLite 采用多线程模型，每个线程都使用各自的数据库连接 （即 SQLite3 *）
• SQLite 采用串行模型，所有线程都公用同一个数据库连接。

6、SQLite使用建议

​ 写操作的并发性并不好，当多线程进行访问时实际上仍旧需要互相等待，而读操作所需要的 SHARED 锁是可以共享的，所以为了保证最高的并发性，推荐

• 使用多线程模式
• 使用 WAL 模式
• 单线程写，多线程读 （各线程都持有自己对应的数据库连接)
• 避免长时间事务
• 缓存 SQLite3_prepare 编译结果
• 多语句通过 BEGIN 和 COMMIT 做显示事务，减少多次的自动事务消耗

三、SQLite基础操作

1、基础概念

• 表：是数据库中一个非常重要的对象，是其他对象的基础。根据信息的分类情况，一个数据库中可能包含若干个数据表
• 字段：表的“列”称为“字段”，每个字段包含某一专题的信息
• 记录：是指对应于数据表中一行信息的一组完整的相关信息
• iOS使用SQLite，需要引入libSQLite3.0.tbd框架，并引入<SQLite3.h>头文件

2、关键API-打开数据库

//打开数据库连接 定义
SQLite_API int SQLite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  SQLite3 **ppDb          /* OUT: SQLite db handle */
);

//使用数据库连接
//db是SQLite3对象，SQLite3 *db = nil;
 SQLite3_open([sqlPath UTF8String], &db);   
 
//打开
int SQLite3_open_v2(
	const char *filename, /* Database filename (UTF-8) */
	SQLite3 **ppDb, /* OUT: SQLite db handle */
	int flags, /* Flags */
	const char *zVfs /* Name of VFS module to use */
);

• 参数1：数据库的路径（因为需要的是C语言的字符串，而不是NSString所以必须进行转换）

• 参数2：SQLite的数据库的操作句柄（指向指针的指针）

3、关键API - 执行sql语句

//执行sql语句 定义
SQLite_API int SQLite3_exec(
  SQLite3*,                                  /* An open database */
  const char *sql,                           /* SQL to be evaluated */
  int (*callback)(void*,int,char**,char**),  /* Callback function */
  void *,                                    /* 1st argument to callback */
  char **errmsg                              /* Error msg written here */
);

//使用
 int result = SQLite3_exec(db, sql.UTF8String, nil, nil, nil);   
 if (result == SQLite_OK) {
     //exec ok
 } else {
     //exec failed
 }

• 参数1：SQLite3对象
• 参数2：sql语句
• 参数3：sql执行后回调函数
• 参数4：回调函数的参数
• 参数5：错误信息

4、关键API - 执行查询语句

//将sql文本转换成一个准备语句（prepared statement）对象，同时返回这个对象的指针,它实际上并不执行（evaluate）这个SQL语句，它仅仅为执行准备这个sql语句。
SQLite_API int SQLite3_prepare_v2(
  SQLite3 *db,            /* Database handle */
  const char *zSql,       /* SQL statement, UTF-8 encoded */
  int nByte,              /* Maximum length of zSql in bytes. */
  SQLite3_stmt **ppStmt,  /* OUT: Statement handle */
  const char **pzTail     /* OUT: Pointer to unused portion of zSql */
);

//使用
result = SQLite3_prepare_v2(_db, [sql UTF8String], -1, &pStmt, 0);
if (result == SQLite_OK) {
     //exec ok
} else {
     //exec failed
}

5、关键API - 关闭数据库

//关闭数据库 定义
SQLite_API int SQLite3_close(SQLite3*);

//使用
SQLite3_close(db);

说明：具体API参考C-language Interface Specification for SQLite，FMDB中对SQLite3的操作做了很好的封装，具体可参考FMDB的FMDatabase文件

参考 SQLite线程模式探讨

四、FMDB

FMDB是iOS平台的SQLite数据库框架，iOS项目中使用十分广泛。

1、源码组成

• FMDatabase : 对SQLite3的封装，可以看做是SQLite3数据库操作实例，通过它可以对SQLite3进行增删改查等等操作。
• FMResultSet : FMDatabase执行查询之后的结果集。
• FMDatabaseAdditions : FMDatabase的Extension，新增对查询结果只返回单个值的方法进行简化，对表、列是否存在，版本号，校验SQL等等功能。
• FMDatabaseQueue : 使用GCD串行队列保证线程安全，所有的线程共用一个SQLite Handle(单句柄)，在多线程并发时，能够使各个线程的数据库操作按顺序同步进行，但正是因为各线程同步进行，导致后来的线程会被阻塞较长时间，无论是读操作还是写操作，都必须等待前面的线程执行完毕，使得性能无法得到更好的保障
• FMDatabasePool : 使用任务池的形式，对多线程的操作提供支持。（不过官方对这种方式并不推荐使用，优先选择FMDatabaseQueue的方式）

说明：在FMDB中，SQLite运行在多线程模式，一个数据库连接在同一个时间只能在一个线程操作 ，应该是在编译时候确定的，当然也可以在打开数据库连接时候，指定线程模式是 多线程或串行。

2、数据库创建和打开

• FMDatabase通过一个 SQLite 数据库文件路径创建的，此路径可以是：

  一个文件的系统路径。磁盘中可以不存在此文件，因为如果不存在会自动为你创建。
  一个空的字符串 `@""`。会在临时位置创建一个空的数据库，当 `FMDatabase` 连接关闭时，该数据库会被删除。
  NULL`。会在内存中创建一个数据库，当 `FMDatabase` 连接关闭时，该数据库会被销毁。
  

• FMDatabase必须执行open，在这里才能正在创建并打开SQLite3对象。

  FMDatabase *db = [FMDatabase databaseWithPath:dbpath];
  [db open];
  //...
  
  //关闭
  [db close];
  

3、数据库查询

//数据库查询
FMResultSet *rs = [db executeQuery:@"select * from people"];
//利用next函数
while ([rs next]) {
    NSLog(@"%@ %@",[rs stringForColumn:@"name"],[rs stringForColumn:@"age"]);
}

• FMResultSet通过调用 -executeQuery... 方法之一执行 SELECT 语句返回数据库查询结果FMResultSet 对象，然后就可以遍历查询结果了。

4、数据库更新

• SQL 语句中除过 SELECT 语句都可以称之为更新操作。包括 CREATE，UPDATE，INSERT，ALTER，COMMIT，BEGIN，DETACH，DROP，END，EXPLAIN，VACUUM，REPLACE 等。

• 执行更新语句后会返回一个 BOOL 值，返回 YES 表示执行更新语句成功，返回 NO 表示出现错误，可以通过调用 -lastErrorMessage 和 -lastErrorCode 方法获取更多错误信息。

  //创建表
  [db executeUpdate:@"CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER DEFAULT 1)"];
  
  //插入操作
  [db executeUpdate:@"INSERT INTO people(name,age) VALUES (?,?)", @"LiLei",[NSNumber numberWithInteger:28]]
  

5、多线程数据库访问

• FMDatabase 本身不是线程安全的，不要实例化一个 FMDatabase 单例来跨线程使用，但是可以通过FMDatabaseQueue保证跨线程操作是同步的，是线程安全的。

FMDatabaseQueue *databaseQueue = [FMDatabaseQueue databaseQueueWithPath:dbpath];
[databaseQueue inDatabase:^(FMDatabase *db) {
        //
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER DEFAULT 1)"];
 }];
    
    dispatch_queue_t queue = dispatch_get_global_queue(DISPATCH_QUEUE_PRIORITY_DEFAULT, 0);
    
    dispatch_async(queue, ^{
        [databaseQueue inDatabase:^(FMDatabase *db) {
           BOOL isSuccess = [db executeUpdate:@"INSERT INTO people(name,age) VALUES (?,?)", @"LiLei",[NSNumber numberWithInteger:28]];
            if (isSuccess) {
                NSLog(@"插入成功1");
            }
        }];
    });
    
    dispatch_async(queue, ^{
        [databaseQueue inDatabase:^(FMDatabase *db) {
            BOOL isSuccess = [db executeUpdate:@"INSERT INTO people(name,age) VALUES (?,?)", @"LiLei",[NSNumber numberWithInteger:28]];
            if (isSuccess) {
                NSLog(@"插入成功2");
            }
        }];
    });
 

• FMDatabaseQueue 将块代码 block 运行在一个串行队列上，即使在多线程同时调用 FMDatabaseQueue 的方法，它们仍然还是顺序执行。这种查询和更新方式不会影响其它，是线程安全的。

五、其他

1、GYDataCenter

• 基于FMDB实现的ORM框架

• 具体参考GYDataCenter：高性能数据库框架

2、WCDB

• 微信开源的移动端数据库组件，基于SQLite，支持ORM（Object Relational Mapping）(将一个ObjC的类，映射到数据库的表和索引,将类的property，映射到数据库表的字段）
• 具体参考 微信移动端数据库组件WCDB系列（一）-iOS基础篇

3、Realm

• 具体参考 Realm数据库 从入门到“放弃”