Sqlite3

发表于 更新于 分类于
结合SQLite体系结构的组成，对SQLite3的prepare、step过程及结果输出进行更深入的理解

体系结构

SQLite 拥有一个简洁的、模块化的体系结构，并引进了一些独特的方法进行关系型数据库管理。它由可以划分为3个子系统(编译器(Compiler)、核心(Core)、后端(Backend))的8个独立模块组成(详见下图)。这些模块将查询过程划分为几个独立的任务，就像在流水线上工作一样。在体系结构栈的顶部编译查询语句，在中部执行，在底部处理存储并与操作系统交互。

接口(Interface)

接口处于栈的顶端，由SQLite C API组成。程序、脚本语言还有与SQLite交互的库文件最终都是通过它与SQLite交互的。从表面上理解，这里是开发者、管理员等与SQLite通信的地方。

编译器(Compiler)

编译过程从词法分析器(Tokenizer)和语法分析器(Parser)开始。它们协同处理文本形式的结构化查询语句(Structured Query Language, SQL)，分析其语法有效性，然后转化为底层能更方便地处理的层次化数据结构。SQLite词法分析器是手动编码实现的，它的语法分析器是由SQLite特定的语法分析生成器Lemon(专门有一本书讲解此生成器)产生的。
代码生成器(Code Generator)将语法树翻译成一种SQLite专用的汇编代码，这些汇编语言由一些最终由虚拟机执行的指令组成。代码生成器的唯一工作是将语法树转换为完全由这种汇编语言编写的微程序并交给虚拟机处理。

虚拟机(Virtual Machine)

架构栈的中心部分是虚拟机(Virtual Machine)，也叫做虚拟数据库引擎(Virtual DataBase Engine,VDBE)。VDBE是基于寄存器的虚拟机，在字节码上工作，这使得它可以独立顶层操作系统、CPU和系统体系结构。VDBE的字节代码(称为虚拟机语言)由100多个被称为操作码(opcodes)的可能的任务构成，所有这些操作都是围绕数据库进行的。
VDBE是一个专为数据处理设计的虚拟机。它的指令集中所有的指令或者用来完成具体的数据库操作(比如打开一个表的游标、做记录、提取一列或者开始一个事务等)，或者以某种方式控制栈为完成这些操作做准备。SQLite中的所有SQL语句—-从选择和更新记录到创建表、视图以及索引—-都是首先编译成虚拟机语言，形成一个独立的定义了如何完成给定的命令的指令集。
VDBE是SQLite的核心，它之前的所有模块都是用于创建VDBE程序的，它之后的所有模块都是用于执行VDBE程序的，每次执行一条指令。

后端(Backend)

后端由B-tree、页缓存(page cache)以及操作系统接口组成。B-tree和pager一起作为信息代理。它们使用的数据源是数据库页，这些页是具有相同大小的数据块、就像用于运输的集装箱。页里面的”货物”是表示信息的大量位(bit)，这些信息包括记录、字段和索引项等。B-tree和pager不需要知道信息的内容，它们只负责移动和排列这些页。

B-tree的职责就是排序。它维护着多个页之间错综复杂的关系，这些关系能保证快速定位并找到一切有联系的数据。B-tree将页面组织成树状结构(这也是它叫做B-tree的原因)，这种组织结构很适合搜索，页面就是树的叶子。

pager(SQLite的一种数据结构)帮助B-tree管理页面，它负责传输。pager根据B-tree的请求从磁盘读取页面或者向磁盘写入页面。磁盘操作是目前计算机必须做的工作中最慢的事情之一。因此pager试图通过将频繁使用的页面缓存在内存中来加速这一操作，从而最小化与硬盘直接交互所花费的时间。pager的功能描述页包含事务管理、数据库锁以及崩溃恢复，其中许多功能是通过OS接口(OS Interface)实现的。

像文件锁一样的很多事情在不同的操作系统上实现是不同的。OS接口(OS Interface)为SQLite其他模块提供了屏蔽这些差异的抽象层。最终的结果就是其他模块看到的是一个一致的对外的系统接口。所以，pager不用担心在Windows上以一种方式锁文件，而在其他不同操作系统上(例如UNIX)上使用另一种方式。这就使得SQLite很容易移植到不同的操作系统上。

OS接口(OS Interface)

系统接口就是系统提供的I/O接口

SQLite接口应用

以下代码就是利用SQLite提供的接口，进行数据库操作的完整过程，：创建数据库->创建表->执行增删改查操作->查询结果读取->关闭数据库：

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
{
    // 分配资源，初始化一些必要的数据结构
    sqlite3_initialize();
    sqlite3 *db = NULL;
    // 以指定方式打开path路径下的数据库文件，并返回数据库指针
    sqlite3_open_v2(path, &db, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
    
    // 创建名称为dbtable的表，
    // 字段包括key,size,inline_data,modification_time,last_access_time,extended_data
    NSString *sql = @"pragma journal_mode = wal;\
                    pragma synchronous = normal;\
                    create table if not exists dbtable (key 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 dbtable(last_access_time);";
    
    char *error = NULL;
    int rc = sqlite3_exec(_db, sql.UTF8String, NULL, NULL, &error);
    if (error) {
        sqlite3_free(error);
    }
    
    NSString *sql = @"select * from dbtable where key = ?1;";
    NSArray *keys = @[@"1", @"2", @"3"];
    sqlite3_stmt *stmt = NULL;
    // 将一个SQL命令字符串转换成能在VDBE中运行的一系列操作码，存储在sqlite3_stmt类型结构体中
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    
    for (NSString *key in keys) {
        // 绑定SQL中指定位置的参数
        // sql语句变为"select * from dbtable where key = 1;"
        sqlite3_bind_text(stmt, 1, key.UTF8String, -1, NULL);
        // 每次调用都会返回结果集中的其中一行(返回SQLITE_ROW)，直到再没有有效数据行了(返回SQLITE_OK)
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            // 获取执行结果的列数
            int col_count = sqlite3_column_count(stmt);
            // 逐列的获取数据
            while(col_count-- >= 0){
                // 根据类型使用对应的"sqlite3_column_xxx"函数取值
                int col_data = sqlite3_column_int(stmt, col_count);
            }
        }
        // 重置stmt结构的状态，尽量对stmt进行复用，提高性能
        sqlite3_reset(stmt);
        // 清楚绑定参数信息，方便再次进行参数绑定
        sqlite3_clear_bindings(stmt);
    }
    // 释放申请的stmt结构的内存
    sqlite3_finalize(stmt);
    stmt = NULL;
    
    // 关闭数据库连接，释放数据结构所关联的内存，删除所有的临时数据项
    // 如果遇到返回SQLITE_BUSY或SQLITE_LOCKED的情况，需要执行完所有的stmt后再关闭，参见fmdb
    sqlite3_close(_db);
    // 释放由sqlite3_initialize分配的资源
    sqlite3_shutdown();
}

SQLite接口函数中，有关执行操作及结果输出的函数比较难理解，这需要我们首先了解SQLite整体的体系结构，然后结合各个子模块的职责再去理解操作及结果输出接口，这样会理解的深刻一些。下面针对比较难懂的函数给出我的一些理解：

  • sqlite3_prepare_v2函数的功能是由编译器中的词法分析器(Tokenizer)、语法分析器(Parser)和代码生成器(Code Generator)三个子模块来完成的，最终将生成的操作码(Operation code)存储在sqlite3_stmt结构中。
  • sqlite3_step函数的功能是通过虚拟数据库引擎(Virtual DataBase Engine,VDBE)读取stmt中的操作码，调动后台子模块来完成指定的查询或修改操作，当进行查询操作时，该函数的返回结果如果为SQLITE_ROW，则代表查询的结果不止一行，每执行一次sqlite3_step函数，会输出一行结果到stmt结构中，此时我们可以通过sqlite3_column_count函数获取结果行的列数，然后通过sqlite3_column_xxx函数对stmt中存储的结果行，逐列的进行读取，读取完一行结果后再次执行sqlite3_step函数，将下一行结果写入到stmt结构中，以此类推，直到sqlite3_step函数返回SQLITE_OK，证明已经输出了所有结果，最后如果不需要进行任何操作了，需要对申请的stmt结构进行释放。

Reference

SQLite源码分析
SQLite教程
SQlite数据库的C编程接口

0%