iOS SQLite学习（中）

最近比较忙，所以现在才写第二篇学习笔记，废话不说，直入主题。上次讲到的都是非常基础的东西，这次主要是在数据库能正常操作的基础上，提高效率。(本人也是SQL方面的新手，SQLite更是如此，所以可能会有所错漏）

先总结一下数据库的一般操作流程：

打开数据库文件->编写好SQL语句->预编译语句->执行语句->查询结果（非查询语句跳过这一步）->释放语句->关闭数据库

需要注意的是，不再使用的数据库指针和语句必须释放掉。虽然我测试后发现，即使是已经打开了的数据库也是可以进行其他文件操作的，包括删除，复制等。

1.sqlite3_prepare_v2

sqlite3_prepare_v2是用来预编译SQL语句的，之前没有接触过的时候，真心觉得这个函数有点奇怪。为什么不直接把写好SQL语句传进去，让程序执行就好了，预编译的过程我不需要知道啊。其实这个函数颇为巧妙，你可以得到一个已经指向了sqlite3_stmt的指针，为下面的工作提供了很多便利。例如，只有利用预编译好的sqlite3_stmt指针，你可以查询到执行后的结果，而不需要其他复杂的属性。不过，其实还有一个很有用的功能，就是绑定数据。

实际操作中，你有可能一次需要插入大量的数据，由于每个数据的值都是不同的，一开始你可能会不断预编译新的语句，执行后又再释放掉。这样做看起来好像也没有什么不对，但问题是，sqlite3_prepare_v2这种预编译语句是有开销的，对于少量的数据，影响可能并不明显。但当数据量比较大的时候，也就会成为一种负担了。所以这里可以使用绑定，绑定的原理就是，只预编译一次语句，然后只改变语句中的数据，最后反复执行完成后再释放。因此，绑定比较适合重复但是语句结构不变的操作。

 1 const char *insertTempTable = "INSERT INTO "TEMPTABLE" VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 2 sqlite3_bind_text(insertTempTableStmt, 1, [SDObject.ID UTF8String], strlen([SDObject.ID UTF8String]), SQLITE_TRANSIENT);
 3 sqlite3_bind_text(insertTempTableStmt, 2, [SDObject.name UTF8String], strlen([SDObject.name UTF8String]), SQLITE_TRANSIENT);
 4 sqlite3_bind_text(insertTempTableStmt, 3, [SDObject.createdTime UTF8String], strlen([SDObject.createdTime UTF8String]), SQLITE_TRANSIENT);
 5 sqlite3_bind_text(insertTempTableStmt, 4, [SDObject.updatedTime UTF8String], strlen([SDObject.updatedTime UTF8String]), SQLITE_TRANSIENT);
 6 sqlite3_bind_text(insertTempTableStmt, 5, [SDObject.parentID UTF8String], strlen([SDObject.parentID UTF8String]), SQLITE_TRANSIENT);
 7 sqlite3_bind_text(insertTempTableStmt, 6, [SDObject.access UTF8String], strlen([SDObject.access UTF8String]), SQLITE_TRANSIENT);
 8 sqlite3_bind_text(insertTempTableStmt, 7, [SDObject.type UTF8String], strlen([SDObject.type UTF8String]), SQLITE_TRANSIENT);
 9 sqlite3_bind_text(insertTempTableStmt, 9, [SDObject.fileSize UTF8String], strlen([SDObject.fileSize UTF8String]), SQLITE_TRANSIENT);
10 sqlite3_step(insertTempTableStmt);
11 sqlite3_reset(insertTempTableStmt);

需要注意的是，绑定的索引从1开始而不是0，字符串有SQLITE_STATIC和SQLITE_TRANSIENT的选项。前者是说明传入的字符串是静态的，也就是可以认为指向的内存空间不会被意外释放。后者，则是说明，字符串的内存空间是可能会被释放的，估计这个函数会将重新申请空间将字符串复制过去。每一次执行语句以后，都要reset一次，才再进行下一次的绑定。绑定可以节省重新sprintf的次数，尽管可读性也不是太强，但是，开销的确是减少了。

2.关于事务

SQLite也具有事务的特性，而且还会用得非常多。首先，SQLite默认是自动提交事务，简单来说就是，每一次执行非查询语句之前都会开启一个事务，查询完成后就会提交。具体原理也不难理解，内存是比硬盘快很多的，特别是随机读写性能和每秒读写次数，事务就是利用先在内存写入好要储存的数据，然后再一并写入硬盘。那么就不用重复读写硬盘，造成性能低下了。所以，一秒内能够提交的事务是有限的，但是同一个事务内的插入、删除操作倒是可以达到上万次。我在实际操作中又真的遇到了这样的问题，就以删除操作为例，每一个文件对应只有一个父文件夹、因此如果，要删除一个文件夹以及它里面的所有文件，就要递归搜索文件夹里面的文件夹，再从最深层开始删除。递归的开销大家都知道的，不这样一来，删除一个文件夹就已经不是仅仅删除一条记录那么简单，可能一次就是删除上百上千条记录。刚开始时，我并没有注意到事务这个问题，但是在实际测试中发现，我删除文件夹的时候，界面的响应总是会卡顿一两秒。这种停顿让我觉得很不爽，因为iOS的优势就是界面的操作响应快，1、2秒卡顿确确实实是明显得很。在排除了相关操作因素影响，确定了是数据库的操作遇到了性能瓶颈。

事实上SQLite的性能并不会差，而是相当优秀（仅局限于嵌入式设备，只是支持的语句有所删减），以几百条数据的插入删除，根本不存在性能问题。所以我就直接手动管理事务了

 1 + (void) deleteFile:(const char *)ID andExt:(const char *)ext andTable:(const char *)table andCache:(const char *)cache withDataBase:(sqlite3 *)db
 2 {
 3     sqlite3_stmt *deleteSelfStmt = NULL, *deleteCacheDateStmt = NULL;
 4     char deleteSelf[100], deleteCacheDate[100];
 5     
 6     sprintf(deleteSelf, "DELETE FROM %s WHERE id = '%s'", table, ID);
 7     sprintf(deleteCacheDate, "DELETE FROM %s WHERE id = '%s'", cache, ID);
 8     
 9     if (strncmp((const char *)ID, "folder", 6) == 0) {
10         
11         char select[200];
12         sqlite3_stmt *selectStmt = NULL;
13         sprintf(select, "SELECT id FROM %s WHERE parentid = '%s'", table, ID);
14         
15         sqlite3_prepare_v2(db, select, strlen(select), &selectStmt, NULL);
16         while (sqlite3_step(selectStmt) != SQLITE_DONE)
17             [DataBaseClass deleteFile:(char *)sqlite3_column_text(selectStmt, 0) andExt:(char *)sqlite3_column_text(selectStmt, 9) andTable:table andCache:cache withDataBase:db];
19         
20     } else {
21         
22         sqlite3_stmt *searchFileExtStmt = NULL;
23         char searchFileExt[100];
24         sprintf(searchFileExt, "SELECT filetype FROM %s WHERE id = '%s'", table, ID);
25         
26         
27         NSArray *paths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
28         NSString *libraryDirectory = [paths objectAtIndex:0];
29         NSFileManager *fileManager = [NSFileManager defaultManager];
30         NSString *cache = [libraryDirectory stringByAppendingPathComponent:@"FilesCache"], *returnPath;
31         
32         if (ext == NULL) {
33             
34             sqlite3_prepare_v2(db, searchFileExt, strlen(searchFileExt), &searchFileExtStmt, NULL);
35             if (sqlite3_step(searchFileExtStmt) != SQLITE_DONE)
36                 returnPath = [cache stringByAppendingPathComponent:[NSString stringWithFormat:@"%s.%s", ID, sqlite3_column_text(searchFileExtStmt, 0)]];
37             sqlite3_finalize(searchFileExtStmt);
38             
39         } else
40             returnPath = [cache stringByAppendingPathComponent:[NSString stringWithFormat:@"%s.%s", ID, ext]];
41         
42         [fileManager removeItemAtPath:returnPath error:nil];
43     }
44     
45     sqlite3_prepare_v2(db, deleteSelf, strlen(deleteSelf), &deleteSelfStmt, NULL);
46     sqlite3_step(deleteSelfStmt);
47     sqlite3_finalize(deleteSelfStmt);
48     
49     sqlite3_prepare_v2(db, deleteCacheDate, strlen(deleteCacheDate), &deleteCacheDateStmt, NULL);
50     sqlite3_step(deleteCacheDateStmt);
51     sqlite3_finalize(deleteCacheDateStmt);
52 }

上面是尚未开启事务时的删除操作方法，是利用递归判断是否为文件夹，是的话就继续调用。

 1 + (void) transActionDeleteFile:(const char *)ID andTable:(const char *)table andCache:(const char *)cache withDataBase:(sqlite3 *)db
 2 {
 3     const char *begin = "BEGIN TRANSACTION", *end = "COMMIT";
 4     sqlite3_stmt *beginStmt = NULL, *endStmt = NULL;
 5     
 6     sqlite3_prepare_v2(db, begin, strlen(begin), &beginStmt, NULL);
 7     sqlite3_step(beginStmt);
 8     sqlite3_finalize(beginStmt);
 9     
10     [DataBaseClass deleteFile:ID andExt:NULL andTable:table andCache:cache withDataBase:db];
11     
12     sqlite3_prepare_v2(db, end, strlen(end), &endStmt, NULL);
13     sqlite3_step(endStmt);
14     sqlite3_finalize(endStmt);
15 }

这个就是使用了事务的方法，API封装得不好，可以忽略……其实就两句话"BEGIN TRANSACTION"和"COMMIT"，不过性能提升很明显，使用后界面就不会再有类似的卡顿了。

有时候难免会需要储存一些临时的数据，但又不想写到数据库文件去，这时就可以使用临时表了，语句是"CREATE TEMP TABLE"+名字+(列)。临时表是储存在内存中的，因此一般操作，可以忽略事务这个方法。只是考虑到内存的大小问题，我一般用完都会删除临时表。

3.数据库的中文问题

其实数据库已经可以完美支持中文了（就是UTF-8编码而已），不过在iOS上查询时也需要一些注意

1 [NSString stringWithFormat:@"%s", sqlite3_column_text(selectStmt, 0)];
2 [NSString stringWithCString:(const char *)sqlite3_column_text(selectStmt, 1) encoding:NSUTF8StringEncoding];

对于不包含中文的字段，可以用第一种方法；但是，如果包含了中文仍然使用第一种的话，最后得出来的必然是乱码。当然还有第三种方法

1 [NSString stringWithUTF8String:(const char *)chName];

这种也是可以的。

4.数据库的其他问题

SQLite的数据库文件是跨平台无缝兼容的，而且只是一个文件，可以在其他设备上使用。多线程环境下，是建议为每一个线程创建一个数据库实例，不过具体的加锁问题，我尚未研究好。但是，打开几个数据库文件，可以用同一个实例调用“ATTACH DATABASE”语句，而不需要创建好几个实例。

至于开发的问题是，应该如何观察数据表的数据变化呢？我推荐使用官方的SQLite命令行工具和Firefox的一个插件SQLite Manager，前者是纯SQL语句和一些命令函数操作的，后者就是傻瓜式操作。SQLite的命令行工具可以去官网下载，然后扔到“/bin”，需要用到管理员权限，以后就可以直接在终端使用了。至于，SQLite Manager没什么好说的，我平时都是直接用它的，也可以修改数据。