iOS|iOS Sqlite3 的总结 iOSSqlite3的总结

最近在看YYCache,有许多sqlite3的语句,之前操作数据库都是用的第三方FMDB
总结一下:
常用方法 sqlite3*db, 数据库句柄，跟文件句柄FILE很类似
sqlite3_stmt*stmt, 用于保存编译好的SQL语句
sqlite3_open(),打开数据库，没有数据库时创建。
sqlite3_exec(),执行非查询的sql语句
Sqlite3_step(), 在调用sqlite3_prepare后，使用这个函数在记录集中移动。
Sqlite3_close(), 关闭数据库文件
还有一系列的函数，用于从记录集字段中获取数据，如
sqlite3_column_text(), 取text类型的数据。
sqlite3_column_blob（），取blob类型的数据
sqlite3_column_int(), 取int类型的数据
【注意】由于sqlite3是基于C语言编写的，而不是纯粹的object-c，所以有关字符串，我们不能使用NSString，因为它不识别，所以只能用c语言的字符串，char*，好在Nsstring提供了转换的方法，那就是 UTF8String。

1.创建或者打开数据库找到当前应用程序沙盒目录（也就是说希望数据库保存在哪里）

-(NSString *) dataFilePath{
_dbPath =NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *document = [_dbPath objectAtIndex:0];
return [document stringByAppendingPathComponent:TABLENAME]; //'manifest.sqlite'
}

SQLITE_OK是sqlite3的一个常量，代表操作执行成功
创建数据库
//打开数据库,参数一:数据库路径,二:指向指针的指针

int result = sqlite3_open(_dbPath.UTF8String, &_db);
if (result == SQLITE_OK) {
CFDictionaryKeyCallBacks keyCallBacks = kCFCopyStringDictionaryKeyCallBacks;
CFDictionaryValueCallBacks valueCallBacks = {0};
_dbStmtCache = CFDictionaryCreateMutable(CFAllocatorGetDefault(), 0, &keyCallBacks, &valueCallBacks);
_dbLastOpenErrorTime = 0;
_dbOpenErrorCount = 0;
return YES;
}

else
{
_db = NULL;
if (_dbStmtCache) CFRelease(_dbStmtCache);
_dbStmtCache = NULL;
_dbLastOpenErrorTime = CACurrentMediaTime();
_dbOpenErrorCount++;
if (_errorLogsEnabled) {
NSLog(@"%s line:%d sqlite open failed (%d).",__FUNCTION__,__LINE__,_errorLogsEnabled);
}

return NO;
}
2.创建一张表

NSString *sql = @"pragma journal_mode = wal; pragma synchronous = normal; create table if not exists manifest (key text, filename text, size integer, inline_data blob, modification_time integer, last_access_time integer, extended_data blob, primary key(key)); create index if not exists last_access_time_idx on manifest(last_access_time); ";
char*ERROR;
int result = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &error);
if(result !=SQLITE_OK){
sqlite3_close(database);
NSAssert(0, @"ceate table faild!");
NSLog(@"表创建失败");
}

3.查询表数据

NSString *sql = [NSString stringWithFormat:@"select filename from manifest where key in (%@)",[self _dbJoinedKeys:keys]];
sqlite3_stmt *stmt = NULL;
int result = sqlite3_prepare_v2(_db, sql.UTF8String, -1, &stmt, NULL);
if(result == SQLITE_OK) {
while(sqlite3_step(stmt)==SQLITE_ROW) {
char *filename = (char *)sqlite3_column_text(stmt, 0);
if (filename &&filename != 0) {
NSString *name = [NSString stringWithUTF8String:filename];
if (name) [filenames addObject:name];
}
}
sqlite3_finalize(stmt);
}
//用完了一定记得关闭，释放内存
sqlite3_close(database);

sqlite3_prepare_v2是执行查询的方法，当查询语句执行成功时，使用sqlite3_step当游标指向每一行SQLITE_ROW时，我们开始读取数据
sqlite_3_column_text可以读取字符串类型的数据，参数二为column号，sqlite_3column_int读取int类型数据，
4.保存，插入数据

NSString *sql = @"insert or replace into manifest (key, filename, size, inline_data, modification_time, last_access_time, extended_data) values (?1, ?2, ?3, ?4 ,?5, ?6, ?7); ";
//上边的update也可以这样写：
//NSString *insert = [NSString stringWithFormat:@"INSERT OR REPLACE INTO PERSIONINFO('%@','%@','%@','%@','%@')VALUES(?,?,?,?,?)",NAME,AGE,SEX,WEIGHT,ADDRESS];
char*errorMsg = NULL;
sqlite3_stmt *stmt;
if(sqlite3_prepare_v2(database, update, -1, &stmt, nil) == SQLITE_OK) {
//【插入数据】在这里我们使用绑定数据的方法，参数一：sqlite3_stmt，参数二：插入列号，参数三：插入的数据，参数四：数据长度（-1代表全部），参数五：是否需要回调
sqlite3_bind_text(stmt, 1, key.UTF8String, -1, NULL);
sqlite3_bind_text(stmt, 2, filename.UTF8String, -1, NULL);
sqlite3_bind_int(stmt, 3, (int)value.length);
}
if(sqlite3_step(stmt) != SQLITE_DONE)
NSLog(@"数据更新失败");
NSAssert(0, @"error updating :%s",errorMsg);
sqlite3_finalize(stmt);
sqlite3_close(database);

也可以直接把数据写在要执行的sql语句后面

NSString *insert = [NSString stringWithFormat:@"INSERT OR REPLACE INTO PERSIONINFO('%@','%@','%@','%@','%@')VALUES('%@','%d','%@','%d','%@')",NAME,AGE,SEX,WEIGHT,ADDRESS,@"小杨",23,@"man",65,@"中国北京,haidian,shangdi,xinxiRoad,100014"];
//执行语句
if(sqlite3_exec(database, [insert UTF8String], NULL, NULL, &errorMsg) != SQLITE_OK) {
sqlite3_close(database);
}

以下解释来自
数据库优化

- (BOOL)_dbInitialize {
NSString *sql = @"pragma journal_mode = wal; pragma synchronous = normal; create table if not exists manifest (key text, filename text, size integer, inline_data blob, modification_time integer, last_access_time integer, extended_data blob, primary key(key)); create index if not exists last_access_time_idx on manifest(last_access_time); ";
return [self _dbExecute:sql];
}

数据库索引
数据库提供了一种列表式的存储方式，数据会按照一定的规则组织在表中，每一行代表了一个数据。如果单行数据存在多列时，如下图所示

文章图片
其中rowid是升序排序的，这也意味着除了rowid之外的其他列基本排除了规则排序的可能性。如果现在搜索水果的价格：

【iOS|iOS Sqlite3 的总结】SELECT price FROM fruitsforsale WHERE fruit=‘Peach’

那么只能做一次全表查询搜索，最坏时间是O(N)，这在数据库存在大量数据的情况下开销相当的可观。

create index if not exists last_access_time_idx on manifest(last_access_time);

索引是一种提高搜索效率的方案，一般索引会建立一个树结构来对索引列进行排序，使得查找时间为O(logN)甚至更低。YYCache对最后访问时间建立了索引，提高淘汰算法的执行效率

文章图片
参考资料：sqlite索引的原理
设置数据库访问模式
从iOS4.3开始，sqlite提供了Write-Ahead Logging模式，在大部分情况下这种模式读写速度更快，且两者互不堵塞。使用这种模式时，改写操作不改动数据库文件，而是修改到WAL文件中。

pragma journal_mode = wal;

WAL的文件会在执行checkpoint操作时写回数据库文件，或者当文件大小达到某个阙值时（默认为1KB）会自动执行checkpoint操作

- (void)_dbCheckpoint {
if (![self _dbCheck]) return;
// Cause a checkpoint to occur, merge `sqlite-wal` file to `sqlite` file.
sqlite3_wal_checkpoint(_db, NULL);
}

磁盘同步等级
sqlite的磁盘写入速度分为三个等级：
PRAGMA synchronous = FULL; (2)
PRAGMA synchronous = NORMAL; (1)
PRAGMA synchronous = OFF; (0)
当synchronous为FULL时，数据库引擎会在紧急时刻暂停以确定数据写入磁盘，这样能保证在系统崩溃或者计算机死机的环境下数据库在重启后不会被损坏，代价是插入数据的速度会降低。
如果synchronous为OFF则不会暂停。除非计算机死机或者意外关闭的情况，否则即便是sqlite程序崩溃了，数据也不会损伤，这种等级的写入速度最高。YYCache采用了第二种，速度不那么慢又相对安全的同步等级：
pragma synchronous = normal;
缓存sql命令结构
sqlite3_stmt是操作数据库数据的辅助数据类型，每一个sql语句可以解析成对应的辅助数据结构，大量的sql语句解析同样会带来性能上的损耗，因此YYCache采用CFMutableDictionaryRef结构将解析后的辅助数据类型存储起来，每次执行sql语句前查询是否存在已缓存的数据：

- (sqlite3_stmt *)_dbPrepareStmt:(NSString *)sql {
if (![self _dbCheck] || sql.length == 0 || !_dbStmtCache) return NULL;
sqlite3_stmt *stmt = (sqlite3_stmt *)CFDictionaryGetValue(_dbStmtCache, (__bridge const void *)(sql));
if (!stmt) {
int result = sqlite3_prepare_v2(_db, sql.UTF8String, -1, &stmt, NULL);
if (result != SQLITE_OK) {
if (_errorLogsEnabled) NSLog(@"%s line:%d sqlite stmt prepare error (%d): %s", __FUNCTION__, __LINE__, result, sqlite3_errmsg(_db));
return NULL;
}
CFDictionarySetValue(_dbStmtCache, (__bridge const void *)(sql), stmt);
} else {
sqlite3_reset(stmt);
}
return stmt;
}