1、SQLite介绍
自几十年前出现的商业应用程序以来，数据库就成为软件应用程序的主要组成部分。正与数据库管理系统非常关键一样，它们也变得非常庞大，并占用了相当多的系统资源，增加了管理的复杂性。随着软件应用程序逐渐模块模块化，一种新型数据库会比大型复杂的传统数据库管理系统更适应。嵌入式数据库直接在应用程序进程中运行，提供了零配置（zero-configuration）运行模式，并且资源占用非常少。
SQLite是一个开源的嵌入式关系数据库，它在2000年由D. Richard Hipp发布，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。 SQLite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。 嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。因为客户端和服务器在同一进程空间运行。SQLite 的数据库权限只依赖于文件系统，没有用户帐户的概念。SQLite 有数据库级锁定，没有网络服务器。它需要的内存，其它开销很小，适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。 2、架构(architecture)
SQLite采用了模块的设计，它由三个子系统，包括8个独立的模块构成。
2.1、接口(Interface)
接口由SQLite C API组成，也就是说不管是程序、脚本语言还是库文件，最终都是通过它与SQLite交互的(我们通常用得较多的ODBC/JDBC最后也会转化为相应C API的调用)。 2.2、编译器(Compiler)
在编译器中，分词器（Tokenizer）和分析器(Parser)对SQL进行语法检查，然后把它转化为底层能更方便处理的分层的数据结构---语法树，然后把语法树传给代码生成器(code generator)进行处理。而代码生成器根据它生成一种针对SQLite的汇编代码，最后由虚拟机(Virtual Machine)执行。 2.3、虚拟机(Virtual Machine)
架构中最核心的部分是虚拟机，或者叫做虚拟数据库引擎(Virtual Database
Engine,VDBE)。它和Java虚拟机相似，解释执行字节代码。VDBE的字节代码由128个操作码(opcodes)构成，它们主要集中在数据库操作。它的每一条指令都用来完成特定的数据库操作(比如打开一个表的游标)或者为这些操作栈空间的准备(比如压入参数)。总之，所有的这些指令都是为了满足SQL命令的要求(关于VM，后面会做详细介绍)。 2.4、后端(Back-End)
后端由B-树(B-tree)，页缓存(page cache，pager)和操作系统接口(即系统调用)构成。B-tree和page cache共同对数据进行管理。B-tree的主要功能就是索引，它维护着各个页面之间的复杂的关系，便于快速找到所需数据。而pager的主要作用就是通过OS接口在B-tree和Disk之间传递页面。
3、SQLite的特点(SQLite’s Features and Philosophy) 3.1、零配置(Zero Configuration) 3.2、可移植(Portability)：
它是运行在Windows,Linux,BSD,Mac OS X和一些商用Unix系统，比如Sun的Solaris,IBM的AIX，同样，它也可以工作在许多嵌入式操作系统下，比如QNX,VxWorks,Palm OS, Symbin和Windows CE。 3.3、Compactness：
SQLite是被设计成轻量级，自包含的。one header file, one library, and you’re relational, no external database server required 3.4、简单(Simplicity) 3.5、灵活(Flexibility) 3.6、可靠(Reliability)：
SQLite的核心大约有3万行标准C代码，这些代码都是模块化的，很容易阅读。 主要参考:The Definitive Guide to SQLite
第二节 设计与概念
1、API
由两部分组成: 核心API(core API) 和扩展API（extension API）
核心API的函数实现基本的数据库操作：连接数据库，处理SQL，遍历结果集。它也包括一些实用函数，比如字符串转换，操作控制，调试和错误处理。 扩展API通过创建你自定义的SQL函数去扩展SQLite。 1.1、SQLite Version 3的一些新特点：
(1)SQLite的API全部重新设计，由第二版的15个函数增加到88个函数。这些函数包括支持UTF-8和UTF-16编码的功能函数。
(2)改进并发性能。加锁子系统引进一种锁升级模型(lock escalation model)，解决了第二版的写进程饿死的问题(该问题是任何一个DBMS必须面对的问题)。这种模型保证写进程按照先来先服务的算法得到排斥锁(Exclusive Lock)。甚至，写进程通过把结果写入临时缓冲区(Temporary Buffer)，可以在得到排斥锁之前就能开始工作。这对于写要求较高的应用，性能可提高400%（引自参考文献）。
(3)改进的B-树。对于表采用B+树，大大提高查询效率。
(4)SQLite 3最重要的改变是它的存储模型。由第二版只支持文本模型，扩展到支持5种本地数据类型。
总之，SQLite Version 3与SQLite Vertion 2有很大的不同，在灵活性，特点和性能方面有很大的改进。
1.2、主要的数据结构(The Principal Data Structures) SQLite由很多部分组成－parser,tokenize,virtual machine等等。但是从程序员的角度，最需要知道的是:connection, statements, B-tree和pager。它们之间的关系如下：
上图告诉我们在编程需要知道的三个主要方面：API,事务(Transaction)和锁(Locks)。从技术上来说，B-tree和pager不是API的一部分。但是它们却在事务和锁上起着关键作用（稍后将讨论）。
1.3、Connections和Statements
Connection和statement是执行SQL命令涉及的两个主要数据结构，几乎所有通过API进行的操作都要用到它们。一个连接(Connection)代表在一个独立的事务环境下的一个连接A (connection represents a single connection to a database as well as a single transaction context)。每一个statement都和一个connection关联，它通常表示一个编译过的SQL语句，在内部，它以VDBE字节码表示。Statement包括执行一个命令所需要一切，包括保存VDBE程序执行状态所需的资源，指向硬盘记录的B-树游标，以及参数等等。 1.4、B-tree和pager
一个connection可以有多个database对象---一个主要的数据库以及附加的数据库，每一个数据库对象有一个B-tree对象，一个B-tree有一个pager对象(这里的对象不是面向对象的“对象”，只是为了说清楚问题)。
Statement最终都是通过connection的B-tree和pager从数据库读或者写数据，通过B-tree的游标(cursor)遍历存储在页面(page)中的记录。游标在访问页面之前要把数所从disk加载到内存，而这就是pager的任务。任何时候，如果B-tree需要页面，它都会请求pager从disk读取数据，然后把页面(page)加载到页面缓冲区(page cache)，之后，B-tree和与之关联的游标就可以访问位于page中的记录了。
如果cursor改变了page，为了防止事务回滚，pager必须采取特殊的方式保存原来的page。总的来说，pager负责读写数据库，管理内存缓存和页面（page），以及管理事务，锁和崩溃恢复(这些在事务一节会详细介绍)。
总之，关于connection和transaction，你必须知道两件事： (1)对数据库的任何操作，一个连接存在于一个事务下。 (2)一个连接决不会同时存在多个事务下。
whenever a connection does anything with a database, it always operates under exactly one
transaction, no more, no less. 1.5、核心API
核心API 主要与执行SQL命令有关，本质上有两种方法执行SQL语句：prepared query 和wrapped query。Prepared query由三个阶段构成：preparation，execution和
finalization。其实wrapped query只是对prepared query的三个过程包装而已，最终也会转化为prepared query的执行。
1.5.1、连接的生命周期(The Connection Lifecycle) 和大多数据库连接相同，由三个过程构成： （1）连接数据库(Connect to the database)：
每一个SQLite数据库都存储在单独的操作系统文件中，连接，打开数据库的C API为：sqlite3_open()，它的实现位于main.c文件中，如下： int sqlite3_open(const char *zFilename, sqlite3 **ppDb) {
return openDatabase(zFilename, ppDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0); }
当连接一个在磁盘上的数据库，如果数据库文件存在，SQLite打开一个文件；如果不存在，SQLite会假定你想创建一个新的数据库。在这种情况下，SQLite不会立即在磁盘上创建一个文件，只有当你向数据库写入数据时才会创建文件，比如：创建表、视图或者其它数据库对象。如果你打开一个数据，不做任何事，然后关闭它，SQLite会创建一个文件，只是一个空文件而已。
另外一个不立即创建一个新文件的原因是，一些数据库的参数，比如：编码，页面大小等，只在在数据库创建前设置。默认情况下，页面大小为1024字节，但是你可以选择
512-32768字节之间为 2幂数的数字。有些时候，较大的页面能更有效的处理大量的数据。 （2）执行事务(Perform transactions)：
all commands are executed within transactions。默认情况下，事务自动提交，也就是每一个SQL语句都在一个独立的事务下运行。当然也可以通过使用BEGIN..COMMIT手动提交事务。
（3）断开连接(Disconnect from the database)： 主要是关闭数据库的文件。 1.5.2、执行Prepared Query
前面提到，预处理查询(Prepared Query)是SQLite执行所有SQL命令的方式，包括以下三个过程：
(1)Prepared Query： 分析器（parser），分词器(tokenizer)和代码生成器(code generator)把SQL Statement编译成VDBE字节码，编译器会创建一个statement句柄(sqlite3_stmt)，它包括字节码以及其它执行命令和遍历结果集的所有资源。
相应的C API为sqlite3_prepare()，位于prepare.c文件中，如下： int sqlite3_prepare(
sqlite3 *db, const char *zSql, int nBytes, sqlite3_stmt **ppStmt, const char **pzTail ){
int rc;
rc = sqlite3LockAndPrepare(db,zSql,nBytes,0,ppStmt,pzTail); assert( rc==SQLITE_OK || ppStmt==0 || *ppStmt==0 ); return rc; }
(2)Execution：
虚拟机执行字节码，执行过程是一个步进(stepwise)的过程，每一步(step)由
sqlite3_step()启动，并由VDBE执行一段字节码。由sqlite3_prepare编译字节代码，并由sqlite3_step()启动虚拟机执行。在遍历结果集的过程中，它返回SQLITE_ROW，当到达结果末尾时，返回SQLITE_DONE。 (3)Finalization：
VDBE关闭statement，释放资源。相应的C API为sqlite3_finalize()。 通过下图可以更容易理解该过程：
最后以一个具体的例子结束本节，下节讨论事务。 #include #pragma comment(lib,”sqlite3.lib”) int main(int argc,char**argv) { int rc,i,ncols; sqlite3 *db; sqlite3_stmt *stmt; char *sql; const char*tail; //打开数据 rc=sqlite3_open(\"foods.db\ if(rc){ fprintf(stderr,\"Can't open database:%sn\ sqlite3_close(db); exit(1); } sql=\"select * from episodes\"; //预处理 rc=sqlite3_prepare(db,sql,(int)strlen(sql),&stmt,&tail); if(rc!=SQLITE_OK){ fprintf(stderr,\"SQLerror:%sn\ } rc=sqlite3_step(stmt); ncols=sqlite3_column_count(stmt); while(rc==SQLITE_ROW){ for(i=0;i fprintf(stderr,\"n\"); rc=sqlite3_step(stmt); } //释放statement sqlite3_finalize(stmt); //关闭数据库 sqlite3_close(db); return0; } 2、事务(Transaction) 2.1、事务的周期(Transaction Lifecycles) 程序与事务之间有两件事值得注意： （1） 哪些对象在事务下运行——这直接与API有关。 （2） 事务的生命周期，即什么时候开始，什么时候结束以及它在什么时候开始影响别的连接（这点对于并发性很重要）——这涉及到SQLite的具体实现。 一个连接（connection）可以包含多个(statement)，而且每个连接有一个与数据库关联的B-tree和一个pager。Pager在连接中起着很重要的作用，因为它管理事务、锁、内存缓存以及负责崩溃恢复(crash recovery)。当你进行数据库写操作时，记住最重要的一件事：在任何时候，只在一个事务下执行一个连接。这些回答了第一个问题。 一般来说，一个事务的生命和statement差不多，你也可以手动结束它。默认情况下，事务自动提交，当然你也可以通过BEGIN..COMMIT手动提交。接下来就是锁的问题。 2.2、锁的状态(Lock States) 锁对于实现并发访问很重要，而对于大型通用的DBMS，锁的实现也十分复杂，而SQLite相对较简单。通常情况下，它的持续时间和事务一致。一个事务开始，它会先加锁，事务结束，释放锁。但是系统在事务没有结束的情况下崩溃，那么下一个访问数据库的连接会处理这种情况。 在SQLite中有5种不同状态的锁，连接（connection）任何时候都处于其中的一个状态。下图显示了相应的状态以及锁的生命周期。 关于这个图有以下几点值得注意： （1） 一个事务可以在UNLOCKED，RESERVED或EXCLUSIVE三种状态下开始。默认情况下在UNLOCKED时开始。 （2） 白色框中的UNLOCKED, PENDING, SHARED和 RESERVED可以在一个数据库的同一时存在。 （3） 从灰色的PENDING开始，事情就变得严格起来，意味着事务想得到排斥锁 (EXCLUSIVE)（注意与白色框中的区别）。 虽然锁有这么多状态，但是从体质上来说，只有两种情况：读事务和写事务。 2.3、读事务(Read Transactions) 我们先来看看SELECT语句执行时锁的状态变化过程，非常简单：一个连接执行select语句，触发一个事务，从UNLOCKED到SHARED，当事务COMMIT时，又回到UNLOCKED，就这么简单。 考虑下面的例子(为了简单，这里用了伪码)： db = open('foods.db') db.exec('BEGIN') db.exec('SELECT * FROM episodes') db.exec('SELECT * FROM episodes') db.exec('COMMIT') db.close() 由于显式的使用了BEGIN和COMMIT，两个SELECT命令在一个事务下执行。第一个exec()执行时，connection处于SHARED，然后第二个exec()执行，当事务提交时，connection又从SHARED回到UNLOCKED状态，如下： UNLOCKED→PENDING→SHARED→UNLOCKED 如果没有BEGIN和COMMIT两行时如下： UNLOCKED→PENDING→SHARED→UNLOCKED→PENDING→ SHARED→UNLOCKED 2.4、写事务（Write Transactions） 下面我们来考虑写数据库，比如UPDATE。和读事务一样，它也会经历UNLOCKED→PENDING→SHARED，但接下来却是灰色的PENDING， 2.4.1、The Reserved States 当一个连接（connection）向数据库写数据时，从SHARED状态变为RESERVED状态，如果它得到RESERVED锁，也就意味着它已经准备好进行写操作了。即使它没有把修改写入数据库，也可以把修改保存到位于pager中缓存中（page cache）。 当一个连接进入RESERVED状态，pager就开始初始化恢复日志（rollback journal）。在RESERVED状态下，pager管理着三种页面： (1) Modified pages：包含被B-树修改的记录，位于page cache中。 (2) Unmodified pages：包含没有被B-tree修改的记录。 (3) Journal pages：这是修改页面以前的版本，这些并不存储在page cache中，而是在B-tree修改页面之前写入日志。 Page cache非常重要，正是因为它的存在，一个处于RESERVED状态的连接可以真正的开始工作，而不会干扰其它的（读）连接。所以，SQLite可以高效的处理在同一时刻的多个读连接和一个写连接。 2.4.2 、The Pending States 当一个连接完成修改，就真正开始提交事务，执行该过程的pager进入EXCLUSIVE状态。从RESERVED状态，pager试着获取 PENDING锁，一旦得到，就独占它，不允许任何其它连接获得PENDING锁（PENDING is a gateway lock）。既然写操作持有PENDING锁，其它任何连接都不能从UNLOCKED状态进入SHARED状态，即没有任何连接可以进入数据（no new readers, no new writers）。只有那些已经处于SHARED状态的连接可以继续工作。而处于PENDING状态的Writer会一直等到所有这些连接释放它们的锁，然后对数据库加EXCUSIVE锁，进入EXCLUSIVE状态，独占数据库(讨论到这里，对SQLite的加锁机制应该比较清晰了)。 2.4.3、The Exclusive State 在EXCLUSIVE状态下，主要的工作是把修改的页面从page cache写入数据库文件，这是真 正进行写操作的地方。 在pager写入modified pages之前，它还得先做一件事：写日志。它检查是否所有的日志都写入了磁盘，而这些通常位于操作的缓冲区中，所以pager得告诉OS把所有的文件写入磁盘，这是由程序synchronous(通过调用OS的相应的API实现)完成的。 日志是数据库进行恢复的惟一方法，所以日志对于DBMS非常重要。如果日志页面没有完全写入磁盘而发生崩溃，数据库就不能恢复到它原来的状态，此时数据库就处于不一致状态。日志写入完成后，pager就把所有的modified pages写入数据库文件。接下来就取决于事务提交的模式，如果是自动提交，那么pager清理日志，page cache，然后由EXCLUSIVE进入UNLOCKED。如果是手动提交，那么pager继续持有EXCLUSIVE锁和保存日志，直到COMMIT 或者ROLLBACK。 总之，从性能方面来说，进程占有排斥锁的时间应该尽可能的短，所以DBMS通常都是在真正写文件时才会占有排斥锁，这样能大大提高并发性能。 第三节 内核概述 1、虚拟机（Virtual Machine） VDBE是SQLite的核心，它的上层模块和下层模块都是本质上都是为它服务的。它的实现位于vbde.c, vdbe.h, vdbeapi.c, vdbeInt.h, 和vdbemem.c几个文件中。它通过底层的基础设施B+Tree执行由编译器（Compiler）生成的字节代码，这种字节代码程序语言 (bytecode programming lauguage)是为了进行查询，读取和修改数据库而专门设计的。 字节代码在内存中被封装成sqlite3_stmt对象(内部叫做Vdbe，见vdbeInt.h)，Vdbe（或者说statement）包含执行程序所需要的一切： a) a bytecode program b) names and data types for all result columns c) values bound to input parameters d) a program counter e) an execution stack of operands f) an arbitrary amount of \"numbered\" memory cells g) other run-time state information (such as open BTree objects, sorters, lists, sets) 字节代码和汇编程序十分类似，每一条指令由操作码和三个操作数构成： • sqlite3_column_xxx() functions • sqlite3_finalize() 为了有个感性，下面看一个具体的字节码程序： sqlite> .m col sqlite> .h on sqlite> .w 4 15 3 3 15 sqlite> explain select * from episodes; addr opcode p1 p2 p3 ---- --------------- --- --- --------------- 0 Goto 0 12 1 Integer 0 0 2 OpenRead 0 2 # episodes 3 SetNumColumns 0 3 4 Rewind 0 10 5 Recno 0 0 6 Column 0 1 7 Column 0 2 8 Callback 3 0 9 Next 0 5 10 Close 0 0 11 Halt 0 0 12 Transaction 0 0 13 VerifyCookie 0 10 14 Goto 0 1 15 Noop 0 0 1.1、 栈(Stack) 一个VDBE程序通常由不同完成特定任务的段（section）构成，每一个段中，都有一些操作栈的指令。这是由于不同的指令有不同个数的参数，一些指令只有一个参数；一些指令没有参数；一些指令有好几个参数，这种情况下，三个操作数就不能满足。 考虑到这些情况，指令采用栈来传递参数。(注：从汇编的角度来看，传递参数的方式有好几种，比如：寄存器，全局变量，而堆栈是现代语言常用的方式，它具有很大的灵活性)。而这些指令不会自己做这些事情，所以在它们之前，需要其它一些指令的帮助。VDBE把计算的中间结果保存到内存单元(memory cells)中，其实，堆栈和内存单元都是基于Mem（见vdbeInt.h）数据结构(注：这里的栈，内存单元都是虚拟的，记得一位计算机科学家说过：计算机科学中90%以上的科学都是虚拟化问题。一点不假，OS本质上也是虚拟机，而在这里SQLite，我们也处处可见虚拟化的身影，到后面的OS Interface模块中再仔细讨论这个问题)。 1.2、程序体(Program Body) 这是一个打开episodes表的过程。 第一条指令：Integer是为第二条指令作准备的，也就是把第二条指令执行需要的参数压入堆栈，OpenRead从堆栈中取出参数值然后执行。 SQLite可以通过ATTACH命令在一个连接中打开多个数据库文件，每当SQLite打开一个数据，它就为之赋一个索引号(index)，main database的索引为0，第一个数据库为1，依次如此。Integer指令数据库索引的值压入栈，而OpenRead从中取出值，并决定打开哪个数据，来看看SQLite文档中的解释： Open a read-only cursor for the database table whose root page is P2 in a database file. The database file is determined by an integer from the top of the stack. 0 means the main database and 1 means the database used for temporary tables. Give the new cursor an identifier of P1. The P1 values need not be contiguous but all P1 values should be small integers. It is an error for P1 to be negative. If P2==0 then take the root page number from off of the stack. There will be a read lock on the database whenever there is an open cursor. If the data- base was unlocked prior to this instruction then a read lock is acquired as part of this instruction. A read lock allows other processes to read the database but prohibits any other process from modifying the database. The read lock is released when all cursors are closed. If this instruction attempts to get a read lock but fails, the script terminates with an SQLITE_BUSY error code. The P3 value is a pointer to a KeyInfo structure that defines the content and collating sequence of indices. P3 is NULL for cursors that are not pointing to indices. 再来看看SetNumColumns指令设置游标将指向的列。P1为游标的索引（这里为0，刚刚打开），P2为列的数目，episodes表有三列。 继续Rewind指令，它将游标重新设置到表的开始，它会检查表是否为空（即没有记录），如果没有记录，它会导致指令指针跳到P2指定的指令处。在这里，P2为10，即Close指令。一旦Rewind设置游标，接下就执行5-9这几条指令，它们的主要功能是遍历结果集，Recno把由游标P1指定的记录的关键字压入堆栈。Column指令从由P1指定的游标，P2指定的列取值。5,6,7三条指令分别把id(primary key),season和name字段的值压入栈。接下来，Callback指令从栈中取出三个值（P1），然后形成一个记录数组，存储在内存单元中 (memory cell)。Callback会停止VDBE的操作，把控制权交给sqlite3_stemp()，该函数返回SQLITE_ROW。 一旦VDBE创建了记录结构，我们就可以通过sqlite3_column_xxx() functions从记录结构的域内取出值。当下次调用sqlite3_step()时，指令指针会指向Next指令，而Next指令会把游标向移向下一行，如果有其它的记录，它会跳到由P2指定的指令，在这里为指令5，创建一个新的记录结构，一直循环，直到结果集的最后。Close指令会关闭游标，然后执行Halt指令，结束VDBE程序。 1.3、程序开始与停止 现在来看看其余的指令，Goto指令是一条跳转指令，跳到P2处，即第12条指令。指令12是Transaction，它开始一个新的事务；然后执行 VerifyCookie，它的主要功能VDBE程序编译后，数据库模式是否改变（即是否进行过更新操作）。这在SQLite中是一个很重要的概念，在 SQL被sqlite3_prepare()编译成VDBE代码至程序调用sqlite3_step()执行字节码的这段时间，另一个SQL命令可能会改变数据库模式（such as ALTER TABLE, DROP TABLE, or CREATE TABLE）。一旦发生这种情况，之前编译的statement就会变得无效，数据库模 式信息记录在数据库文件的根页面中。类似，每一个 statement都有一份用来比较的在编译时刻该模式的备份，VerifyCookie的功能就是检查它们是否匹配，如果不匹配，将采取 相关操作。 如果两者匹配，会执行下一条指令Goto；它会跳到程序的主要部分，即第一条指令，打开表读取记录。这里有两点值得注意： (1)Transaction指令自己不会获取锁（ The Transaction instruction doesn’t acquire any locks in itself）。它的功能相当于BEGIN，而实际是由OpenRead指令获取share lock的。当事务关闭时释放锁，这取决于Halt指令，它会进行扫尾工作。 (2)statement对象（VDBE程序）所需的存储空间在程序执行前就已经确定。这有原于两个重要事实：首先，栈的深度不会比指令的数目还多（通常少得多）。其次，在执行VDBE程序之前，SQLite可以计算出为分配资源所需要的内存。 1.4指令的类型(Instruction Types) 每条指令都完成特定的任务，而且通常和别的指令有关。大体上来说，指令可分为三类： （1)Value manipulation：这些指令通常完成算术运算，比如：add, subtract, divide；逻辑运算，比如：AND和OR；还有字符串操作。 （2)Data management：这些指令操作在内存和磁盘上的数据。内存指令进行栈操作或者在内存单元之间传递数据。磁盘操作指令控制B-tree和pager打开或操作游标，开始或结束事务，等等。 （3)Control flow：控制指令主要是移动指令指针。 1.5、程序的执行(Program execution) 最后我们来看VM解释器是如何实现以及字节代码大致是如何执行的。在vdbe.c文件中有一个很关键的函数： //执行VDBE程序 int sqlite3VdbeExec( Vdbe *p /* The VDBE */ ) 该函数是执行VDBE程序的入口。来看看它的内部实现： /*从这里开始执行指令 **pc为程序计数器(int) */ for(pc=p->pc; rc==SQLITE_OK; pc++){ //取得操作码 pOp = &p->aOp[pc]; switch( pOp->opcode ){ case OP_Goto: { /* jump */ CHECK_FOR_INTERRUPT; pc = pOp->p2 - 1; break; } … … } } 从这段代码，我们大致可以推出VM执行的原理：VM解释器实际上是一个包含大量switch语句的for循环，每一个switch语句实现一个特定的操作指令。 2、B-tree和Pager B-Tree使得VDBE可以在O(logN)下查询，插入和删除数据，以及O(1)下双向遍历结果集。B-Tree不会直接读写磁盘，它仅仅维护着页面 (pages)之间的关系。当B-TREE需要页面或者修改页面时，它就会调用Pager。当修改页面时，pager保证原始页面首先写入日志文件，当它完成写操作时，pager根据事务状态决定如何做。B-tree不直接读写文件，而是通过page cache这个缓冲模块读写文件对于性能是有重要意义的（注：这和操作系统读写文件类似，在Linux中,操作系统的上层模块并不直接调用设备驱动读写设备，而是通过一个高速缓冲模块调用设备驱动读写文件,并将结果存到高速缓冲区）。 2.1、数据库文件格式（Database File Format） 数据库中所有的页面都按从1开始顺序标记。一个数据库由许多B-tree构成——每一个表和索引都有一个B-tree（注：索引采用B-tree，而表采用B+tree，这主要是表和索引的需求不同以及B-tree和B+tree的结构不同决定的：B+tree的所有叶子节点包含了全部关键字信息，而且可以有两种顺序查找——具体参见《数据结构》，严蔚敏。而B-tree更适合用来作索引）。所有表和索引的根页面都存储在sqlite_master表中。 数据库中第一个页面（page 1）有点特殊，page 1的前100个字节包含一个描述数据库文件的特殊的文件头。它包括库的版本，模式的版本，页面大小，编码等所有创建数据库时设置的参数。这个特殊的文件头的内容在btree.c中定义，page 1也是sqlite_master表的根页面。 2.1、页面重用及回收(Page Reuse and Vacuum ) SQLite利用一个空闲列表(free list)进行页面回收。当一个页面的所有记录都被删除时，就被插入到该列表。当运行VACUUM命令时，会清除free list，所以数据库会缩小，本质上它是在新的文件重新建立数据库，而所有使用的页在都被拷贝过去，而free list却不会，结果就是一个新的，变小的数据库。当数据库的autovacuum开启时，SQLite不会使用free list，而且在每一次commit时自动压缩数据库。 2.2、B-Tree记录 B-tree中页面由B-tree记录组成，也叫做payloads。每一个B-tree记录，或者payload有两个域：关键字域(key field)和数据域(data field)。Key field就是ROWID的值，或者数据库中表的关键字的值。从B-tree的角度，data field可以是任何无结构的数据。数据库的记录就保存在这些data fields中。B-tree的任务就是排序和遍历，它最需要就是关键字。Payloads的大小是不定的，这与内部的关键字和数据域有关，当一个 payload太 大不能存在一个页面内进便保存到多个页面。 B+Tree按关键字排序，所有的关键字必须唯一。表采用B+tree，内部页面不包含数据，如下： B+tree中根页面(root page)和内部页面(internal pages)都是用来导航的，这些页面的数据域都是指向下级页面的指针，仅仅包含关键字。所有的数据库记录都存储在叶子页面(leaf pages)内。在叶节点一级，记录和页面都是按照关键字的顺序的，所以B-tree可以水平方向遍历，时间复杂度为O(1)。 2.3、记录和域（Records and Fields） 位于叶节点页面的数据域的记录由VDBE管理，数据库记录以二进制的形式存储，但有一定的数据格式。记录格式包括一个逻辑头（logical header）和一个数据区(data segment)，header segment包括header的大小和一个数据类型数组，数据类型用来在data segment的数据的类型，如下： 2.4、层次数据组织(Hierarchical Data Organization) 从上往下，数据越来越无序，从下向上，数据越来越结构化. 2.5、B-Tree API B-Tree模块有它自己的API，它可以独立于C API使用。另一个特点就是它支持事务。由pager处理的事务，锁和日志都是为B-tree服务的。根据功能可以分为以下几类： 2.5.1、访问和事务函数 sqlite3BtreeOpen: Opens a new database file. Returns a B-tree object. sqlite3BtreeClose: Closes a database. sqlite3BtreeBeginTrans: Starts a new transaction. sqlite3BtreeCommit: Commits the current transaction. sqlite3BtreeRollback: Rolls back the current transaction. sqlite3BtreeBeginStmt: Starts a statement transaction. sqlite3BtreeCommitStmt: Commits a statement transaction. sqlite3BtreeRollbackStmt: Rolls back a statement transaction. 2.5.2、表函数 sqlite3BtreeCreateTable: Creates a new, empty B-tree in a database file. sqlite3BtreeDropTable: Destroys a B-tree in a database file. sqlite3BtreeClearTable: Removes all data from a B-tree, but keeps the B-tree intact. 2.5.3、游标函数(Cursor Functions) sqlite3BtreeCursor: Creates a new cursor pointing to a particular B-tree. sqlite3BtreeCloseCursor: Closes the B-tree cursor. sqlite3BtreeFirst: Moves the cursor to the first element in a B-tree. sqlite3BtreeLast: Moves the cursor to the last element in a B-tree. sqlite3BtreeNext: Moves the cursor to the next element after the one it is currently pointing to. sqlite3BtreePrevious: Moves the cursor to the previous element before the one it is currently pointing to. sqlite3BtreeMoveto: Moves the cursor to an element that matches the key value passed in as a parameter. 2.5.4、记录函数(Record Functions) sqlite3BtreeDelete: Deletes the record that the cursor is pointing to. sqlite3BtreeInsert: Inserts a new element in the appropriate place of the B-tree. sqlite3BtreeKeySize: Returns the number of bytes in the key of the record that the cursor is pointing to. sqlite3BtreeKey: Returns the key of the record the cursor is currently pointing to. sqlite3BtreeDataSize: Returns the number of bytes in the data record that the cursor is currently pointing to. sqlite3BtreeData: Returns the data in the record the cursor is currently pointing to. 2.5.5、配置函数(Configuration Functions) sqlite3BtreeSetCacheSize: Controls the page cache size as well as the synchronous writes (as defined in the synchronous pragma). sqlite3BtreeSetSafetyLevel: Changes the way data is synced to disk in order to increase or decrease how well the database resists damage due to OS crashes and power failures. Level 1 is the same as asynchronous (no syncs() occur and there is a high probability of damage). This is the equivalent to pragma synchronous=OFF. Level 2 is the default. There is a very low but non-zero probability of damage. This is the equivalent to pragma synchronous=NORMAL. Level 3 reduces the probability of damage to near zero but with a write performance reduction. This is the equivalent to pragma synchronous=FULL. sqlite3BtreeSetPageSize: Sets the database page size. sqlite3BtreeGetPageSize: Returns the database page size. sqlite3BtreeSetAutoVacuum: Sets the autovacuum property of the database. sqlite3BtreeGetAutoVacuum: Returns whether the database uses autovacuum. sqlite3BtreeSetBusyHandler: Sets the busy handler 2.6、实例分析 最后以sqlite3_open的具体实现结束本节的讨论(参见Version 3.6.10的源码)： 由上图可以知道，SQLite的所有IO操作，最终都转化为操作系统的系统调用(一名话：DBMS建立在痛苦的OS之上)。同时也可以看到SQLite的实现非常的层次化，模块化，使得SQLite更易扩展，可移植性非常强。 3、编译器（Compiler） 3.1、分词器(Tokenizer) 接口把要执行的SQL语句传递给Tokenizer,Tokenizer按照SQL的词法定义把它切分一个一个的词，并传递给分析器(Parser)进行语法分析。分词器是手工写的，主要在Tokenizer.c中实现。 3.2、分析器(Parser) SQLite的语法分析器是用Lemon——一个开源的LALR(1)语法分析器的生成器，生成的文件为parser.c。 一个简单的语法树： SELECT rowid, name, season FROM episodes WHERE rowid=1 LIMIT 1 3.3、代码生成器（Code Generator） 代码生成器是SQLite中取庞大，最复杂的部分。它与Parser关系紧密，根据语法分析树生成VDBE程序执行SQL语句的功能。由诸多文件构成： select.c,update.c,insert.c,delete.c,trigger.c,where.c等文件。这些文件生成相应的VDBE 程序指令，比如SELECT语句就由select.c生成。下面是一个读操作中打开表的代码的生成实现： /* Generate code that will open a table for reading. */ void sqlite3OpenTableForReading( Vdbe *v, /* Generate code into this VDBE */ int iCur, /* The cursor number of the table */ Table *pTab /* The table to be opened */ ){ sqlite3VdbeAddOp(v, OP_Integer, pTab->iDb, 0); sqlite3VdbeAddOp(v, OP_OpenRead, iCur, pTab->tnum); VdbeComment((v, \"# %s\ sqlite3VdbeAddOp(v, OP_SetNumColumns, iCur, pTab->nCol); } Sqlite3vdbeAddOp函数有三个参数：（1）VDBE实例（它将添加指令），（2）操作码（一条指令），（3）两个操作数。 3.4、查询优化 代码生成器不仅负责生成代码，也负责进行查询优化。主要的实现位于where.c中，生成的WHERE语句块通常被其它模块共享，比如 select.c，update.c以及delete.c。这些模块调用sqlite3WhereBegin()开始WHERE语句块的指令生成，然后加入它们自己的VDBE代码返回，最后调用sqlite3WhereEnd()结束指令生成，如下： 第四节 Page Cache之事务处理(1) 本节通过一个具体的例子来分析SQLite原子提交的实现（基于Version 3.3.6的代码）。 CREATE TABLE episodes( id integer primary key,name text, cid int) ； 插入一条记录：insert into episodes(name,cid) values(\"cat\它经过编译器处理后生成的虚拟机代码如下： sqlite> explain insert into episodes(name,cid) values(\"cat\ 0|Trace|0|0|0|explain insert into episodes(name,cid) values(\"cat\1|Goto|0|12|0||00| 2|SetNumColumns|0|3|0||00| 3|OpenWrite|0|2|0||00| 4|NewRowid|0|2|0||00| 5|Null|0|3|0||00| 6|String8|0|4|0|cat|00| 7|Integer|1|5|0||00| 8|MakeRecord|3|3|6|dad|00| 9|Insert|0|6|2|episodes|0b| 10|Close|0|0|0||00| 11|Halt|0|0|0||00| 12|Transaction|0|1|0||00| 13|VerifyCookie|0|1|0||00| 14|Transaction|1|1|0||00| 15|VerifyCookie|1|0|0||00| 16|TableLock|0|2|1|episodes|00| 17|Goto|0|2|0||00| 1、初始状态（Initial State） 当一个数据库连接第一次打开时，状态如图所示。图中最右边（“Disk”标注）表示保存在存储设备中的内容。每个方框代表一个扇区。蓝色的块表示这个扇区保存了原始数据。图中中间区域是操作系统的磁盘缓冲区。开始的时候，这些缓存是还没有被使用，因此这些方框是空白的。图中左边区域显示SQLite用户进程的内存。因为这个数据库连接刚刚打开，所以还没有任何数据记录被读入，所以这些内存也是空的。 2、获取读锁(Acquiring A Read Lock) 在SQLite写数据库之前，它必须先从数据库中读取相关信息。比如，在插入新的数据时，SQLite会先从sqlite_master表中读取数据库模式(相当于数据字典)，以便编译器对INSERT语句进行分析，确定数据插入的位置。 在进行读操作之前，必须先获取数据库的共享锁(shared lock)，共享锁允许两个或更多的连接在同一时刻读取数据库。但是共享锁不允许其它连接对数据库进行写操作。 shared lock存在于操作系统磁盘缓存，而不是磁盘本身。文件锁的本质只是操作系统的内核数据结构，当操作系统崩溃或掉电时，这些内核数据也会随之消失。 3、读取数据 一旦得到shared lock，就可以进行读操作。如图所示，数据先由OS从磁盘读取到OS缓存，然后再由OS移到用户进程空间。一般来说，数据库文件分为很多页，而一次读操作只读取一小部分页面。如图，从8个页面读取3个页面。 4、获取Reserved Lock 在对数据进行修改操作之前，先要获取数据库文件的Reserved Lock，Reserved Lock和shared lock的相似之处在于，它们都允许其它进程对数据库文件进行读操作。Reserved Lock和Shared Lock可以共存，但是只能是一个Reserved Lock和多个Shared Lock——多个Reserved Lock不能共存。所以，在同一时刻，只能进行一个写操作。 Reserved Lock意味着当前进程(连接)想修改数据库文件，但是还没开始修改操作，所以其它的进程可以读数据库，但不能写数据库。 5、创建恢复日志(Creating A Rollback Journal File) 在对数据库进行写操作之前，SQLite先要创建一个单独的日志文件，然后把要修改的页面的原始数据写入日志。回滚日志包含一个日志头(图中的绿色)——记录数据库文件的原始大小。所以即使数据库文件大小改变了，我们仍知道数据库的原始大小。 从OS的角度来看，当一个文件创建时，大多数OS(Windows,Linux,Mac OS X)不会向磁盘写入数据，新创建的文件此时位于磁盘缓存中，之后才会真正写入磁盘。如图，日志文件位于OS磁盘缓存中，而不是位于磁盘。 上面 5步的代码的实现： Code 其实现过程如下图所示： 主要参考：http://www.sqlite.org/atomiccommit.html Page Cache之事务处理(2) 个人认为pager层是SQLite实现最为核心的模块，它具有四大功能：I/O，页面缓存，并发控制和日志恢复。而这些功能不仅是上层Btree的基础，而且对系统的性能和健壮性有关至关重要的影响。其中并发控制和日志恢复是事务处理实现的基础。SQLite并发控制的机制非常简单——封锁机制；别外，它的查询优化机制也非常简单——基于索引。这一切使得整个SQLite的实现变得简单，SQLite变得很小，运行速度也非常快，所以，特别适合嵌入式设备。好了，接下来讨论事务的剩余部分。 6、修改位于用户进程空间的页面(Changing Database Pages In User Space) 页面的原始数据写入日志之后，就可以修改页面了——位于用户进程空间。每个数据库连接 都有自己私有的空间，所以页面的变化只对该连接可见，而对其它连接的数据仍然是磁盘缓存中的数据。从这里可以明白一件事：一个进程在修改页面数据的同时，其它进程可以继续进行读操作。图中的红色表示修改的页面。 7、日志文件刷入磁盘(Flushing The Rollback Journal File To Mass Storage) 接下来把日志文件的内容刷入磁盘，这对于数据库从意外中恢复来说是至关重要的一步。而且这通常也是一个耗时的操作，因为磁盘I/O速度很慢。 这个步骤不只把日志文件刷入磁盘那么简单，它的实现实际上分成两步：首先把日志文件的内容刷入磁盘（即页面数据）；然后把日志文件中页面的数目写入日志文件头，再把header刷入磁盘（这一过程在代码中清晰可见）。 代码如下： Code 8、获取排斥锁（Obtaining An Exclusive Lock） 在对数据库文件进行修改之前(注：这里不是内存中的页面),我们必须得到数据库文件的排斥锁(Exclusive Lock)。得到排斥锁的过程可分为两步：首先得到Pending lock；然后Pending lock升级到exclusive lock。 Pending lock允许其它已经存在的Shared lock继续读数据库文件，但是不允许产生新的shared lock，这样做目的是为了防止写操作发生饿死情况。一旦所有的shared lock完成操作，则pending lock升级到exclusive lock。 9、修改的页面写入文件(Writing Changes To The Database File) 一旦得到exclusive lock，其它的进程就不能进行读操作，此时就可以把修改的页面写回数据库文件，但是通常OS都把结果暂时保存到磁盘缓存中，直到某个时刻才会真正把结果写入磁盘。 以上两步的实现代码： Code 10、修改结果刷入存储设备(Flushing Changes To Mass Storage) 为了保证修改结果真正写入磁盘，这一步必不要少。对于数据库存的完整性，这一步也是关键的一步。由于要进行实际的I/O操作，所以和第7步一样，将花费较多的时间。 最后来看看这几步是如何实现的： 其实以上以上几步是在函数sqlite3BtreeSync()---btree.c中调用的(而关于该函数的调用后面再讲)。 代码如下： Code 下图可以进一步解释该过程： 11、删除日志文件(Deleting The Rollback Journal) 一旦更改写入设备，日志文件将会被删除，这是事务真正提交的时刻。如果在这之前系统发生崩溃，就会进行恢复处理，使得数据库和没发生改变一样；如果在这之后系统发生崩溃，表明所有的更改都已经写入磁盘。SQLite就是根据日志存在情况决定是否对数据库进行恢复处理。删除文件本质上不是一个原子操作，但是从用户进程的角度来看是一个原子操作， 所以一个事务看起来是一个原子操作。 在许多系统中，删除文件也是一个高代价的操作。作为优化，SQLite可以配置成把日志文件的长度截为0或者把日志文件头清零。 12、释放锁(Releasing The Lock) 作为原子提交的最后一步，释放排斥锁使得其它进程可以开始访问数据库了。 下图中，我们指明了当锁被释放的时候用户空间所拥有的信息已经被清空了.对于老版本的SQLite你可这么认为。但最新的SQLite会保存些用户空间的缓存不会被清空—万一下一个事务开始的时候，这些数据刚好可以用上呢。重新利用这些内存要比再次从操作系统磁盘缓存或者硬盘中读取要来得轻松与快捷得多，何乐而不为呢？在再次使用这些数据之前，我们必须先取得一个共享锁，同时我们还不得不去检查一下，保证还没有其他进程在我们拥有共享锁之前对数据库文件进行了修改。数据库文件的第一页中有一个计数器，数据库文件每做一次修改，这个计数器就会增长一下。我们可以通过检查这个计数器就可得知是否有其他进程修改过数据库文件。如果数据库文件已经被修改过了，那么用户内存空间的缓存就不得不清空，并重新读入。大多数情况下，这种情况不大会发生，因此用户空间的内存缓存将是有效的，这对于性能提高来说作用是显著的。 以上两步是在sqlite3BtreeCommit()---btree.c函数中实现的。 代码如下： Code 下图可进一步描述该过程： 最后来看看sqlite3BtreeSync()和sqlite3BtreeCommit()是如何被调用的。 一般来说，事务提交方式为自动提交的话，在虚拟机中的OP_Halt指令实现提交事务，相关代码如下： //虚拟机停机指令 case OP_Halt: { /* no-push */ p->pTos = pTos; p->rc = pOp->p1; p->pc = pc; p->errorAction = pOp->p2; if( pOp->p3 ){ sqlite3SetString(&p->zErrMsg, pOp->p3, (char*)0); } //设置虚拟机状态SQLITE_MAGIC_RUN 为 SQLITE_MAGIC_HALT, //并提交事务 rc = sqlite3VdbeHalt(p); assert( rc==SQLITE_BUSY || rc==SQLITE_OK ); if( rc==SQLITE_BUSY ){ p->rc = SQLITE_BUSY; return SQLITE_BUSY; } return p->rc ? SQLITE_ERROR : SQLITE_DONE; } //当虚拟机要停机时,调用该函数,如果VDBE改变了数据库且为自动 //提交模式，则提交这些改变 int sqlite3VdbeHalt(Vdbe *p){ sqlite3 *db = p->db; int i; int (*xFunc)(Btree *pBt) = 0; /* Function to call on each btree backend */ int isSpecialError; /* Set to true if SQLITE_NOMEM or IOERR */ /* This function contains the logic that determines if a statement or ** transaction will be committed or rolled back as a result of the ** execution of this virtual machine. ** ** Special errors: ** ** If an SQLITE_NOMEM error has occured in a statement that writes to ** the database, then either a statement or transaction must be rolled ** back to ensure the tree-structures are in a consistent state. A ** statement transaction is rolled back if one is open, otherwise the ** entire transaction must be rolled back. ** ** If an SQLITE_IOERR error has occured in a statement that writes to ** the database, then the entire transaction must be rolled back. The ** I/O error may have caused garbage to be written to the journal ** file. Were the transaction to continue and eventually be rolled ** back that garbage might end up in the database file. ** ** In both of the above cases, the Vdbe.errorAction variable is ** ignored. If the sqlite3.autoCommit flag is false and a transaction ** is rolled back, it will be set to true. ** ** Other errors: ** ** No error: ** */ if( sqlite3MallocFailed() ){ p->rc = SQLITE_NOMEM; } if( p->magic!=VDBE_MAGIC_RUN ){ /* Already halted. Nothing to do. */ assert( p->magic==VDBE_MAGIC_HALT ); return SQLITE_OK; } //释放虚拟机中所有的游标 closeAllCursors(p); checkActiveVdbeCnt(db); /* No commit or rollback needed if the program never started */ if( p->pc>=0 ){ /* Check for one of the special errors - SQLITE_NOMEM or SQLITE_IOERR */ isSpecialError = ((p->rc==SQLITE_NOMEM || p->rc==SQLITE_IOERR)?1:0); if( isSpecialError ){ /* This loop does static analysis of the query to see which of the ** following three categories it falls into: ** ** Read-only ** Query with statement journal ** Query without statement journal ** ** We could do something more elegant than this static analysis (i.e. ** store the type of query as part of the compliation phase), but ** handling malloc() or IO failure is a fairly obscure edge case so ** this is probably easier. Todo: Might be an opportunity to reduce ** code size a very small amount though */ int isReadOnly = 1; int isStatement = 0; assert(p->aOp || p->nOp==0); for(i=0; i case OP_Statement: isStatement = 1; break; } } /* If the query was read-only, we need do no rollback at all. Otherwise, ** proceed with the special handling. */ if( !isReadOnly ){ if( p->rc==SQLITE_NOMEM && isStatement ){ xFunc = sqlite3BtreeRollbackStmt; }else{ /* We are forced to roll back the active transaction. Before doing ** so, abort any other statements this handle currently has active. */ sqlite3AbortOtherActiveVdbes(db, p); sqlite3RollbackAll(db); db->autoCommit = 1; } } } /* If the auto-commit flag is set and this is the only active vdbe, then ** we do either a commit or rollback of the current transaction. ** ** Note: This block also runs if one of the special errors handled ** above has occured. */ //如果自动提交事务，则提交事务 if( db->autoCommit && db->activeVdbeCnt==1 ){ if( p->rc==SQLITE_OK || (p->errorAction==OE_Fail && !isSpecialError) ){ /* The auto-commit flag is true, and the vdbe program was ** successful or hit an 'OR FAIL' constraint. This means a commit ** is required. */ //提交事务 int rc = vdbeCommit(db); if( rc==SQLITE_BUSY ){ return SQLITE_BUSY; }else if( rc!=SQLITE_OK ){ p->rc = rc; sqlite3RollbackAll(db); }else{ sqlite3CommitInternalChanges(db); } }else{ sqlite3RollbackAll(db); } }else if( !xFunc ){ if( p->rc==SQLITE_OK || p->errorAction==OE_Fail ){ xFunc = sqlite3BtreeCommitStmt; }else if( p->errorAction==OE_Abort ){ xFunc = sqlite3BtreeRollbackStmt; }else{ sqlite3AbortOtherActiveVdbes(db, p); sqlite3RollbackAll(db); db->autoCommit = 1; } } /* If xFunc is not NULL, then it is one of sqlite3BtreeRollbackStmt or ** sqlite3BtreeCommitStmt. Call it once on each backend. If an error occurs ** and the return code is still SQLITE_OK, set the return code to the new ** error value. */ assert(!xFunc || xFunc==sqlite3BtreeCommitStmt || xFunc==sqlite3BtreeRollbackStmt ); for(i=0; xFunc && i Btree *pBt = db->aDb[i].pBt; if( pBt ){ rc = xFunc(pBt); if( rc && (p->rc==SQLITE_OK || p->rc==SQLITE_CONSTRAINT) ){ p->rc = rc; sqlite3SetString(&p->zErrMsg, 0); } } } /* If this was an INSERT, UPDATE or DELETE and the statement was committed, ** set the change counter. */ if( p->changeCntOn && p->pc>=0 ){ if( !xFunc || xFunc==sqlite3BtreeCommitStmt ){ sqlite3VdbeSetChanges(db, p->nChange); }else{ sqlite3VdbeSetChanges(db, 0); } p->nChange = 0; } /* Rollback or commit any schema changes that occurred. */ if( p->rc!=SQLITE_OK && db->flags&SQLITE_InternChanges ){ sqlite3ResetInternalSchema(db, 0); db->flags = (db->flags | SQLITE_InternChanges); } } /* We have successfully halted and closed the VM. Record this fact. */ if( p->pc>=0 ){ db->activeVdbeCnt--; } p->magic = VDBE_MAGIC_HALT; checkActiveVdbeCnt(db); return SQLITE_OK; } //提交事务,主要调用: //sqlite3BtreeSync()---同步btree, sqlite3BtreeCommit()---提交事务 static int vdbeCommit(sqlite3 *db){ int i; int nTrans = 0; /* Number of databases with an active write-transaction */ int rc = SQLITE_OK; int needXcommit = 0; for(i=0; i if( pBt && sqlite3BtreeIsInTrans(pBt) ){ needXcommit = 1; if( i!=1 ) nTrans++; } } /* If there are any write-transactions at all, invoke the commit hook */ if( needXcommit && db->xCommitCallback ){ sqlite3SafetyOff(db); rc = db->xCommitCallback(db->pCommitArg); sqlite3SafetyOn(db); if( rc ){ return SQLITE_CONSTRAINT; } } /* The simple case - no more than one database file (not counting the ** TEMP database) has a transaction active. There is no need for the ** master-journal. ** ** If the return value of sqlite3BtreeGetFilename() is a zero length ** string, it means the main database is :memory:. In that case we do ** not support atomic multi-file commits, so use the simple case then ** too. */ //简单的情况,只有一个数据库文件,不需要master-journal if( 0==strlen(sqlite3BtreeGetFilename(db->aDb[0].pBt)) || nTrans<=1 ){ for(i=0; rc==SQLITE_OK && i //同步btree rc = sqlite3BtreeSync(pBt, 0); } } /* Do the commit only if all databases successfully synced */ //commite事务 if( rc==SQLITE_OK ){ for(i=0; i sqlite3BtreeCommit(pBt); } } } } /* The complex case - There is a multi-file write-transaction active. ** This requires a master journal file to ensure the transaction is ** committed atomicly. */ #ifndef SQLITE_OMIT_DISKIO else{ int needSync = 0; char *zMaster = 0; /* File-name for the master journal */ char const *zMainFile = sqlite3BtreeGetFilename(db->aDb[0].pBt); OsFile *master = 0; /* Select a master journal file name */ do { u32 random; sqliteFree(zMaster); sqlite3Randomness(sizeof(random), &random); zMaster = sqlite3MPrintf(\"%s-mj%08X\", zMainFile, random&0x7fffffff); if( !zMaster ){ return SQLITE_NOMEM; } }while( sqlite3OsFileExists(zMaster) ); /* Open the master journal. */ rc = sqlite3OsOpenExclusive(zMaster, &master, 0); if( rc!=SQLITE_OK ){ sqliteFree(zMaster); return rc; } /* Write the name of each database file in the transaction into the new ** master journal file. If an error occurs at this point close ** and delete the master journal file. All the individual journal files ** still have 'null' as the master journal pointer, so they will roll ** back independently if a failure occurs. */ for(i=0; i if( i==1 ) continue; /* Ignore the TEMP database */ if( pBt && sqlite3BtreeIsInTrans(pBt) ){ char const *zFile = sqlite3BtreeGetJournalname(pBt); if( zFile[0]==0 ) continue; /* Ignore :memory: databases */ if( !needSync && !sqlite3BtreeSyncDisabled(pBt) ){ needSync = 1; } rc = sqlite3OsWrite(master, zFile, strlen(zFile)+1); if( rc!=SQLITE_OK ){ sqlite3OsClose(&master); sqlite3OsDelete(zMaster); sqliteFree(zMaster); return rc; } } } /* Sync the master journal file. Before doing this, open the directory ** the master journal file is store in so that it gets synced too. */ zMainFile = sqlite3BtreeGetDirname(db->aDb[0].pBt); rc = sqlite3OsOpenDirectory(master, zMainFile); if( rc!=SQLITE_OK || (needSync && (rc=sqlite3OsSync(master,0))!=SQLITE_OK) ){ sqlite3OsClose(&master); sqlite3OsDelete(zMaster); sqliteFree(zMaster); return rc; } /* Sync all the db files involved in the transaction. The same call ** sets the master journal pointer in each individual journal. If ** an error occurs here, do not delete the master journal file. ** ** If the error occurs during the first call to sqlite3BtreeSync(), ** then there is a chance that the master journal file will be ** orphaned. But we cannot delete it, in case the master journal ** file name was written into the journal file before the failure ** occured. */ for(i=0; i if( pBt && sqlite3BtreeIsInTrans(pBt) ){ rc = sqlite3BtreeSync(pBt, zMaster); if( rc!=SQLITE_OK ){ sqlite3OsClose(&master); sqliteFree(zMaster); return rc; } } } sqlite3OsClose(&master); /* Delete the master journal file. This commits the transaction. After ** doing this the directory is synced again before any individual ** transaction files are deleted. */ rc = sqlite3OsDelete(zMaster); assert( rc==SQLITE_OK ); sqliteFree(zMaster); zMaster = 0; rc = sqlite3OsSyncDirectory(zMainFile); if( rc!=SQLITE_OK ){ /* This is not good. The master journal file has been deleted, but ** the directory sync failed. There is no completely safe course of ** action from here. The individual journals contain the name of the ** master journal file, but there is no way of knowing if that ** master journal exists now or if it will exist after the operating ** system crash that may follow the fsync() failure. */ return rc; } /* All files and directories have already been synced, so the following ** calls to sqlite3BtreeCommit() are only closing files and deleting ** journals. If something goes wrong while this is happening we don't ** really care. The integrity of the transaction is already guaranteed, ** but some stray 'cold' journals may be lying around. Returning an ** error code won't help matters. */ for(i=0; i sqlite3BtreeCommit(pBt); } } } #endif return rc; } 第五节 Page Cache之并发控制 本节主要谈谈SQLite的锁机制，SQLite是基于锁来实现并发控制的，所以本节的内容实际上是属于事务处理的，但是SQLite的锁机制实现非常的简单而巧妙，所以在这里单独讨论一下。如果真正理解了它，对整个事务的实现也就理解了。而要真正理解SQLite的锁机制，最好方法就是阅读SQLite的源码，所以在阅读本文时，最好能结合源码。SQLite的锁机制很巧妙，尽管在本节中的源码中，我写了很多注释，也是我个人在研究时的一点心 得，但是我发现仅仅用言语，似乎不能把问题说清楚，只有通过体会，才能真正理解SQLite的锁机制。好了，下面进入正题。 SQLite的并发控制机制是采用加锁的方式，实现非常简单，但也非常的巧妙，本节将对其进行一个详细的解剖。请仔细阅读下图，它可以帮助更好的理解下面的内容。 1、RESERVED LOCK RESERVED锁意味着进程将要对数据库进行写操作。某一时刻只能有一个RESERVED Lock，但是RESERVED锁和SHARED锁可以共存，而且可以对数据库加新的SHARED锁。 为什么要用RESERVED锁？ 主要是出于并发性的考虑。由于SQLite只有库级排斥锁（EXCLUSIVE LOCK），如果写事务一开始就上EXCLUSIVE锁，然后再进行实际的数据更新，写磁盘操作，这会使得并发性大大降低。而SQLite一旦得到数据库的RESERVED锁，就可以对缓存中的数据进行修改，而与此同时，其它进程可以继续进行读操作。直到真正需要写磁盘时才对数据库加EXCLUSIVE 锁。 2、PENDING LOCK PENDING LOCK意味着进程已经完成缓存中的数据修改，并想立即将更新写入磁盘。它将等待此时已经存在的读锁事务完成，但是不允许对数据库加新的SHARED LOCK(这与RESERVED LOCK相区别)。 为什么要有PENDING LOCK? 主要是为了防止出现写饿死的情况。由于写事务先要获取RESERVED LOCK，所以可能一直产生新的SHARED LOCK，使得写事务发生饿死的情况。 3、加锁机制的具体实现 SQLite在pager层获取锁的函数如下： Code Windows下具体的实现如下： Code 在几个关键的部位标记数字。 (I)对于一个读事务会的完整经过： 语句序列：（1）——>（2）——>（6） 相应的状态真正的变化过程为：UNLOCKED→PENDING(1)→PENDING、SHARED(2)→SHARED（6）→UNLOCKED (II)对于一个写事务完整经过： 第一阶段： 语句序列：（1）——>（2）——>（6） 状态变化：UNLOCKED→PENDING(1)→PENDING、SHARED(2)→SHARED(6)。此时事务获得SHARED LOCK。 第二个阶段： 语句序列：（3） 此时事务获得RESERVED LOCK。 第三个阶段： 事务执行修改操作。 第四个阶段： 语句序列：（1）——>（4）——>（5） 状态变化为： RESERVED→ RESERVED 、PENDING(1)→PENDING（4）→EXCLUSIVE（5）。此时事务获得排斥锁，就可以进行写磁盘操作了。 注：在上面的过程中，由于（1）的执行，使得某些时刻SQLite处于两种状态，但它持续的时间很短，从某种程度上来说可以忽略，但是为了把问题说清楚，在这里描述了这一微妙而巧妙的过程。 4、SQLite的死锁问题 SQLite的加锁机制会不会出现死锁? 这是一个很有意思的问题，对于任何采取加锁作为并发控制机制的DBMS都得考虑这个问题。有两种方式处理死锁问题：（1）死锁预防(deadlock prevention)（2）死锁检测(deadlock detection)与死锁恢复(deadlock recovery)。SQLite采取了第一种方式，如果一个事务不能获取锁，它会重试有限次（这个重试次数可以由应用程序运行预先设置，默认为1次） ——这实际上是基本锁超时的机制。如果还是不能获取锁，SQLite返回 SQLITE_BUSY错误给应用程序，应用程序此时应该中断，之后再重试；或者中止当前事务。虽然基于锁超时的机制简单，容易实现，但是它的缺点也是明显的——资源浪费。 5、事务类型(Transaction Types) 既然SQLite采取了这种机制，所以应用程序得处理SQLITE_BUSY 错误，先来看一个会产生SQLITE_BUSY错误的例子： 所以应用程序应该尽量避免产生死锁，那么应用程序如何做可以避免死锁的产生呢？ 答案就是为你的程序选择正确合适的事务类型。 SQLite有三种不同的事务类型，这不同于锁的状态。事务可以从DEFERRED，IMMEDIATE或者EXCLUSIVE，一个事务的类型在BEGIN命令中指定： BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION； 一个deferred事务不获取任何锁，直到它需要锁的时候，而且BEGIN语句本身也不会做什么事情——它开始于UNLOCK状态；默认情况下是这样的。如果仅仅用BEGIN开始一个事务，那么事务就是DEFERRED的，同时它不会获取任何锁，当对数据库进行第一次读操作时，它会获取SHARED LOCK；同样，当进行第一次写操作时，它会获取RESERVED LOCK。 由BEGIN开始的Immediate事务会试着获取RESERVED LOCK。如果成功，BEGIN IMMEDIATE保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作，但是RESERVED LOCK会阻止其它的连接BEGIN IMMEDIATE或者BEGIN EXCLUSIVE命令，SQLite会返回SQLITE_BUSY错误。这时你就可以对数据库进行修改操作，但是你不能提交，当你COMMIT时，会返回SQLITE_BUSY错误，这意味着还有其它的读事务没有完成，得等它们执行完后才能提交事务。 Exclusive事务会试着获取对数据库的EXCLUSIVE锁。这与IMMEDIATE类似，但是一旦成功，EXCLUSIVE事务保证没有其它的连接，所以就可对数据库进行读写操作了。 上面那个例子的问题在于两个连接最终都想写数据库，但是他们都没有放弃各自原来的锁，最终，shared 锁导致了问题的出现。如果两个连接都以BEGIN IMMEDIATE开始事务，那么死锁就不会发生。在这种情况下，在同一时刻只能有一个连接进入BEGIN IMMEDIATE，其它的连接就得等待。BEGIN IMMEDIATE和BEGIN EXCLUSIVE通常被写事务使用。就像同步机制一样，它防止了死锁的产生。 基本的准则是：如果你在使用的数据库没有其它的连接，用BEGIN就足够了。但是，如果你使用的数据库在其它的连接也要对数据库进行写操作，就得使用BEGIN IMMEDIATE或BEGIN EXCLUSIVE开始你的事务。 第六节再谈SQLite的锁 SQLite封锁机制的实现需要底层文件系统的支持，不管是Linux，还是Windows，都提供了文件锁的机制，而这为SQLite提供了强大的支持。本节就来谈谈SQLite使用到的文件锁——主要基于Linux和Windows平台。 Linux的文件锁 Linux 支持的文件锁技术主要包括建议锁（advisory lock）和强制锁（mandatory lock）这两种。此外，Linux 中还引入了两种强制锁的变种形式：共享模式强制锁（share-mode mandatory lock）和租借锁（lease）。在这里，主要讨论建议锁（advisory lock）。 建议锁并不由内核强制实行，也就是说如果有进程不遵守“游戏规则”，不检查目标文件是否已经由别的进程加了锁就往其中写入数据，那么内核是不会加以阻拦的。因此，建议锁并不能阻止进程对文件的访问，而只能依靠各个进程在访问文件之前检查该文件是否已经被其他进程加锁来实现并发控制。进程需要事先对锁的状态做一个约定，并根据锁的当前状态和相互关系来确定其他进程是否能对文件执行指定的操作。而强制锁是由内核强制采用的文件锁——由于内核对每个read()和write()操作都会检查相应的锁，所以会降低系统性能。 对于建议锁，Linux提供两种实现方式：锁文件（lock files）和记录锁（ record locking）。 (1)锁文件(lock files) 锁文件是最简单的对文件加锁的方法，每个需要加锁的数据文件都有一个锁文件（lock file）。当锁文件存在时，就认为该数据文件已经被加锁，别的进程不应该访问（但是你非要访问，Linux也不会阻止）。当锁不存在，进程就可以创建一个锁文件，然后访问相应的数据文件。只要创建锁的过程是原子的，就能保证某一时刻只有一个进程拥有该锁，这种方法保证某一时刻只有一个进程访问文件。 这种想法很简单，当一个进程想访问文件时，可以按如下方式对文件加锁： fd = open(\"somefile.lck\", O_RDONLY, 0644); if (fd >= 0) { close(fd); printf(\"the file is already locked\"); return 1; } else { /* the lock file does not exist, we can lock it and access it */ fd = open(\"somefile.lck\", O_CREAT | O_WRONLY, 0644\"); if (fd < 0) { perror(\"error creating lock file\"); return 1; } /* we could write our pid to the file */ close(fd); } 当一个进程处理完文件后，就可以调用unlink(\"somefile.lck\")释放锁了——本质上是删除somefile.lck文件。 上面这段代码实际上存在竞争情况，原因在于if语句块不是原子性的，进入if语句块，内核可能调度别的进程运行。更好的方式如下： fd = open(\"somefile.lck\", O_WRONLY | O_CREAT | O_EXCL, 0644); if (fd < 0 && errno == EEXIST) { printf(\"the file is already locked\"); return 1; } else if (fd < 0) { perror(\"unexpected error checking lock\"); return 1; } /* we could write our pid to the file */ close(fd); O_EXCL标志保证open()创建锁文件的过程是原子性的。 注意以下几点： 1、任何时刻只有一个进程可以拥有锁。 2、O_EXCL标志只对本志文件系统是可靠的，对于网络文件系统并不能很好的支持。 3、锁仅仅只是建议性的。 4、如果一个持有锁的进程不正常结束，锁文件仍然存在。如果加锁进程的pid存储在锁文件中，其它进程可以检查锁进程是否存在，当它结束时就可以删除锁。但是，在检查的时候，如果pid被其它进程使用了，此时就无能为力了。 (2)记录锁（Record Locking） 为了克服锁文件的缺点，System V和BSD4.3引入了记录锁，相应的系统调用为lockf()和 flock()。而POSIX对于记录锁提供了另外一种机制，其系统调用为fcntl()。Linux提供三种接口，在这里仅讨论POSIX的接口。 记录锁和锁文件有两个很重要的区别：首先，记录锁可以对文件的任何一部分加锁——这对于DBMS这样的应用程序，有极大的帮助，SQLite当然没有放过这样的好处。其次，记录锁的另一个优点就是它由内核持有，而不是文件系统持有。当进程结束时，所有的锁也随之释放。 和锁文件一样，POSIX锁也是建议性的。记录锁有两种锁：读锁（read locks）和写锁（write locks）。读锁也就是共享锁（shared lock），写锁也就是排它锁（exclusive lock）。对于一个记录，只能有一个进程持有写锁，读锁不能存在。 对于一个进程本身而言，多个锁绝不会冲突。如果一个进程对文件的200-250字节持有读锁，然后对200-225字节数据加写锁，是会成功的。此时，200-225为写锁，而226-250字节数据为读锁，该规则主要是防止进程本身发生死锁（尽管多进程之间仍然可能发生死锁）。 POSIX锁通过fcntl()系统来实现，如下： #include int fcntl(int fd, int command, long arg); arg为指向flock结构体的指针： #include struct flock { short l_type; short l_whence; off_t l_start; off_t l_len; pid_t l_pid; }; 在 flock 结构中，l_type 用来指明创建的是共享锁还是排他锁，其取值有三种：F_RDLCK（共享锁）、F_WRLCK（排他锁）和F_UNLCK（删除之前建立的锁）；l_pid 指明了该锁的拥有者；l_whence、l_start 和l_end 这些字段指明了进程需要对文件的哪个区域进行加锁，这个区域是一个连续的字节集合。因此，进程可以对同一个文件的不同部分加不同的锁。l_whence 必须是 SEEK_SET、SEEK_CUR 或 SEEK_END 这几个值中的一个，它们分别对应着文件头、当前位置和文件尾。l_whence 定义了相对于 l_start 的偏移量，l_start 是从文件开始计算的。 可以执行的操作包括： * F_GETLK：进程可以通过它来获取通过 fd 打开的那个文件的加锁信息。执行该操作时，lock 指向的结构中就保存了希望对文件加的锁（或者说要查询的锁）。如果确实存在这样一把锁，它阻止 lock 指向的 flock 结构所给出的锁描述符，则把现存的锁的信息写到 lock 指向的 flock 结构中，并将该锁拥有者的 PID 写入 l_pid 字段中，然后返回；否则，就将 lock 指向的 flock 结构中的 l_type 设置为 F_UNLCK，并保持 flock 结构中其他信息不变返回，而不会对该文件真正加锁。 * F_SETLK：进程用它来对文件的某个区域进行加锁（l_type的值为 F_RDLCK 或 F_WRLCK）或者删除锁（l_type 的值为F_UNLCK），如果有其他锁阻止该锁被建立，那么 fcntl() 就出错返回 * F_SETLKW：与 F_SETLK 类似，唯一不同的是，如果有其他锁阻止该锁被建立，则调用进程进入睡眠状态，等待该锁释放。一旦这个调用开始了等待，就只有在能够进行加锁或者收到信号时才会返回 需要注意的是，F_GETLK 用于测试是否可以加锁，在 F_GETLK 测试可以加锁之后，F_SETLK 和 F_SETLKW 就会企图建立一把锁，但是这两者之间并不是一个原子操作，也就是说，在 F_SETLK 或者 F_SETLKW 还没有成功加锁之前，另外一个进程就有可能已经插进来加上了一把锁。而且，F_SETLKW 有可能导致程序长时间睡眠。还有，程序对某个文件拥有的各种锁会在相应的文件描述符被关闭时自动清除，程序运行结束后，其所加的各种锁也会自动清除。 Windows中的文件锁 Windows中的锁都是强制锁(mandatory locks)，Windows中的共享文件通过以下几个机制来管理： (1) 通过共享访问控制方式，应用程序可以指定整个文件进行共享读，写或者删除。 (2) 通过字节范围锁（byte range locks）可以对文件的某一部分进行读写访问。 (3) Windows文件系统不允许正在执行的文件被打开用来进行写或删除操作。 文件的共享方式由WIN32 API中的打开文件函数CreateFile()中的sharing mode参数确定： HANDLE CreateFile( LPCTSTR lpFileName, DWORD dwDesiredAccess, DWORD dwShareMode, LPSECURITY_ATTRIBUTES lpSecurityAttributes, DWORD dwCreationDisposition, DWORD dwFlagsAndAttributes, HANDLE hTemplateFile ); dwShareMode的取值通常为： FILE_SHARE_DELETE： Enables subsequent open operations on an object to request delete access. Otherwise, other processes cannot open the object if they request delete access. If this flag is not specified, but the object has been opened for delete access, the function fails. FILE_SHARE_READ： Enables subsequent open operations on an object to request read access. Otherwise, other processes cannot open the object if they request read access. If this flag is not specified, but the object has been opened for read access, the function fails. FILE_SHARE_WRITE： Enables subsequent open operations on an object to request write access. Otherwise, other processes cannot open the object if they request write access. If this flag is not specified, but the object has been opened for write access, the function fails. 具体的实现函数： BOOL LockFile( HANDLE hFile, DWORD dwFileOffsetLow, DWORD dwFileOffsetHigh, DWORD nNumberOfBytesToLockLow, DWORD nNumberOfBytesToLockHigh ); SQLite封锁机制的几个注意点 SQLite的lock byte的定义如下： Code PENDING_BYTE为何设置为0X4000 0000（1GB）？ 在Windows文件中，被加锁的区域不要求有数据，并且它会阻止所有的进程写文件的该区域,包括第一个持有该锁的进程.为了防止出现由于对含有mandatory lock的页面进行读写操作而出现错误(这在Windows中是不允许的),SQLite完全忽略包含pending byte的页面,所以pending byte在数据库文件上产生一个”文件洞”。PENDING_BYTE设置得那么高,则大部分数据库文件不会遇到由于PENDING_BYTE产生”文件洞”引起的空间损失(除非文件特别大,超过1GB)。 (2)对于Windows来说，文件中加锁的区域不能重叠，为了使两个读进程可以同时访问文件，对于SHARED LOCK选择一个SHARED_FIRST——SHARED_FIRST+ SHARED_SIZE范围内的随机数，所以有可能两个进程取得一样的lock byte，所以对于Windows，SQLite的并发性就受到限制。 SQLite3 C/C++ 开发接口简介（API函数） 一 1.0 总览 SQLite3是SQLite一个全新的版本,它虽然是在SQLite 2.8.13的代码基础之上开发的,但是使用了和之前的版本不兼容的数据库格式和API. SQLite3是为了满足以下的需求而开发的: 支持UTF-16编码. 用户自定义的文本排序方法. 可以对BLOBs字段建立索引. 因此为了支持这些特性我改变了数据库的格式,建立了一个与之前版本不兼容的3.0版. 至于其他的兼容性的改变,例如全新的API等等,都将在理论介绍之后向你说明,这样可以使你最快的一次性摆脱兼容性问题. 3.0版的和2.X版的API非常相似,但是有一些重要的改变需要注意. 所有API接口函数和数据结构的前缀都由\"sqlite_\"改为了\"sqlite3_\". 这是为了避免同时使用SQLite 2.X和SQLite 3.0这两个版本的时候发生链接冲突. 由于对于C语言应该用什么数据类型来存放UTF-16编码的字符串并没有一致的规范. 因此SQLite使用了普通的void* 类型来指向UTF-16编码的字符串. 客户端使用过程中可以把void*映射成适合他们的系统的任何数据类型. 2.0 C/C++ 接口 SQLite 3.0一共有83个API函数,此外还有一些数据结构和预定义(#defines). (完整的API介绍请参看另一份文档.) 不过你们可以放心,这些接口使用起来不会像它的数量所暗示的那么复杂. 最简单的程序仍然使用三个函数就可以完成: sqlite3_open(), sqlite3_exec(), 和 sqlite3_close(). 要是想更好的控制数据库引擎的执行,可以使用提供的sqlite3_prepare()函数把SQL语句编译成字节码,然后在使用sqlite3_step()函数来执行编译后的字节码. 以 sqlite3_column_开头的一组API函数用来获取查询结果集中的信息. 许多接口函数都是成对 出现的,同时有UTF-8和UTF-16两个版本. 并且提供了一组函数用来执行用户自定义的SQL函数和文本排序函数. 2.1 如何打开关闭数据库 typedef struct sqlite3 sqlite3; int sqlite3_open(const char*, sqlite3**); int sqlite3_open16(const void*, sqlite3**); int sqlite3_close(sqlite3*); const char *sqlite3_errmsg(sqlite3*); const void *sqlite3_errmsg16(sqlite3*); int sqlite3_errcode(sqlite3*); sqlite3_open() 函数返回一个整数错误代码,而不是像第二版中一样返回一个指向sqlite3结构体的指针. sqlite3_open() 和 sqlite3_open16() 的不同之处在于sqlite3_open16() 使用UTF-16编码(使用本地主机字节顺序)传递数据库文件名. 如果要创建新数据库, sqlite3_open16() 将内部文本转换为UTF-16编码, 反之sqlite3_open() 将文本转换为UTF-8编码. 打开或者创建数据库的命令会被缓存,直到这个数据库真正被调用的时候才会被执行. 而且允许使用PRAGMA声明来设置如本地文本编码或默认内存页面大小等选项和参数. sqlite3_errcode() 通常用来获取最近调用的API接口返回的错误代码. sqlite3_errmsg() 则用来得到这些错误代码所对应的文字说明. 这些错误信息将以 UTF-8 的编码返回,并且在下一次调用任何SQLite API函数的时候被清除. sqlite3_errmsg16() 和 sqlite3_errmsg() 大体上相同,除了返回的错误信息将以 UTF-16 本机字节顺序编码. SQLite3的错误代码相比SQLite2没有任何的改变,它们分别是: #define SQLITE_OK 0 /* Successful result */ #define SQLITE_ERROR 1 /* SQL error or missing database */ #define SQLITE_INTERNAL 2 /* An internal logic error in SQLite */ #define SQLITE_PERM 3 /* Access permission denied */ #define SQLITE_ABORT 4 /* Callback routine requested an abort */ #define SQLITE_BUSY 5 /* The database file is locked */ #define SQLITE_LOCKED 6 /* A table in the database is locked */ #define SQLITE_NOMEM 7 /* A malloc() failed */ #define SQLITE_READONLY 8 /* Attempt to write a readonly database */ #define SQLITE_INTERRUPT 9 /* Operation terminated by sqlite_interrupt() */ #define SQLITE_IOERR 10 /* Some kind of disk I/O error occurred */ #define SQLITE_CORRUPT 11 /* The database disk image is malformed */ #define SQLITE_NOTFOUND 12 /* (Internal Only) Table or record not found */ #define SQLITE_FULL 13 /* Insertion failed because database is full */ #define SQLITE_CANTOPEN 14 /* Unable to open the database file */ #define SQLITE_PROTOCOL 15 /* Database lock protocol error */ #define SQLITE_EMPTY 16 /* (Internal Only) Database table is empty */ #define SQLITE_SCHEMA 17 /* The database schema changed */ #define SQLITE_TOOBIG 18 /* Too much data for one row of a table */ #define SQLITE_CONSTRAINT 19 /* Abort due to contraint violation */ #define SQLITE_MISMATCH 20 /* Data type mismatch */ #define SQLITE_MISUSE 21 /* Library used incorrectly */ #define SQLITE_NOLFS 22 /* Uses OS features not supported on host */ #define SQLITE_AUTH 23 /* Authorization denied */ #define SQLITE_ROW 100 /* sqlite_step() has another row ready */ #define SQLITE_DONE 101 /* sqlite_step() has finished executing */ SQLite3 C/C++ 开发接口简介（API函数） 二 2.2 执行 SQL 语句 typedef int (*sqlite_callback)(void*,int,char**, char**); int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**); sqlite3_exec 函数依然像它在SQLite2中一样承担着很多的工作. 该函数的第二个参数中可以编译和执行零个或多个SQL语句. 查询的结果返回给回调函数. 更多地信息可以查看API 参考. 在SQLite3里,sqlite3_exec一般是被准备SQL语句接口封装起来使用的. typedef struct sqlite3_stmt sqlite3_stmt; int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**); int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**); int sqlite3_finalize(sqlite3_stmt*); int sqlite3_reset(sqlite3_stmt*); sqlite3_prepare 接口把一条SQL语句编译成字节码留给后面的执行函数. 使用该接口访问数据库是当前比较好的的一种方法. sqlite3_prepare() 处理的SQL语句应该是UTF-8编码的. 而sqlite3_prepare16() 则要求是UTF-16编码的. 输入的参数中只有第一个SQL语句会被编译. 第四个参数则用来指向输入参数中下一个需要编译的SQL语句存放的SQLite statement对象的指针, 任何时候如果调用 sqlite3_finalize() 将销毁一个准备好的SQL声明. 在数据库关闭之前，所有准备好的声明都必须被释放销毁. sqlite3_reset() 函数用来重置一个SQL声明的状态，使得它可以被再次执行. SQL声明可以包含一些型如\"?\" 或 \"?nnn\" 或 \":aaa\"的标记， 其中\"nnn\" 是一个整数，\"aaa\" 是一个字符串. 这些标记代表一些不确定的字符值（或者说是通配符），可以在后面用sqlite3_bind 接口来填充这些值. 每一个通配符都被分配了一个编号（由它在SQL声明中的位置决定，从1开始），此外也可以用 \"nnn\" 来表示 \"?nnn\" 这种情况. 允许相同的通配符在同一个SQL声明中出现多次, 在这种情况下所有相同的通配符都会被替换成相同的值. 没有被绑定的通配符将自动取NULL值. int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_double(sqlite3_stmt*, int, double); int sqlite3_bind_int(sqlite3_stmt*, int, int); int sqlite3_bind_int64(sqlite3_stmt*, int, long long int); int sqlite3_bind_null(sqlite3_stmt*, int); int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*)); int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*)); int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*); 以上是 sqlite3_bind 所包含的全部接口，它们是用来给SQL声明中的通配符赋值的. 没有绑定的通配符则被认为是空值. 绑定上的值不会被sqlite3_reset()函数重置. 但是在调用了sqlite3_reset()之后所有的通配符都可以被重新赋值. 在SQL声明准备好之后(其中绑定的步骤是可选的), 需要调用以下的方法来执行: int sqlite3_step(sqlite3_stmt*); 如果SQL返回了一个单行结果集，sqlite3_step() 函数将返回 SQLITE_ROW , 如果SQL语句执行成功或者正常将返回 SQLITE_DONE , 否则将返回错误代码. 如果不能打开数据库文件则会返回 SQLITE_BUSY . 如果函数的返回值是 SQLITE_ROW, 那么下边的这些方法可以用来获得记录集行中的数据: const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); int sqlite3_column_bytes(sqlite3_stmt*, int iCol); int sqlite3_column_bytes16(sqlite3_stmt*, int iCol); int sqlite3_column_count(sqlite3_stmt*); const char *sqlite3_column_decltype(sqlite3_stmt *, int iCol); const void *sqlite3_column_decltype16(sqlite3_stmt *, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); int sqlite3_column_int(sqlite3_stmt*, int iCol); long long int sqlite3_column_int64(sqlite3_stmt*, int iCol); const char *sqlite3_column_name(sqlite3_stmt*, int iCol); const void *sqlite3_column_name16(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); int sqlite3_column_type(sqlite3_stmt*, int iCol); sqlite3_column_count()函数返回结果集中包含的列数. sqlite3_column_count() 可以在执行了 sqlite3_prepare()之后的任何时刻调用. sqlite3_data_count()除了必需要在sqlite3_step()之后调用之外，其他跟sqlite3_column_count() 大同小异. 如果调用sqlite3_step() 返回值是 SQLITE_DONE 或者一个错误代码, 则此时调用sqlite3_data_count() 将返回 0 ，然而 sqlite3_column_count() 仍然会返回结果集中包含的列数. 返回的记录集通过使用其它的几个 sqlite3_column_***() 函数来提取, 所有的这些函数都把列的编号作为第二个参数. 列编号从左到右以零起始. 请注意它和之前那些从1起始的参数的不同. sqlite3_column_type()函数返回第N列的值的数据类型. 具体的返回值如下: #define SQLITE_INTEGER 1 #define SQLITE_FLOAT 2 #define SQLITE_TEXT 3 #define SQLITE_BLOB 4 #define SQLITE_NULL 5 sqlite3_column_decltype() 则用来返回该列在 CREATE TABLE 语句中声明的类型. 它可以用在当返回类型是空字符串的时候. sqlite3_column_name() 返回第N列的字段名. sqlite3_column_bytes() 用来返回 UTF-8 编码的BLOBs列的字节数或者TEXT字符串的字节数. sqlite3_column_bytes16() 对于BLOBs列返回同样的结果，但是对于TEXT字符串则按 UTF-16 的编码来计算字节数. sqlite3_column_blob() 返回 BLOB 数据. sqlite3_column_text() 返回 UTF-8 编码的 TEXT 数据. sqlite3_column_text16() 返回 UTF-16 编码的 TEXT 数据. sqlite3_column_int() 以本地主机的整数格式返回一个整数值. sqlite3_column_int64() 返回一个64位的整数. 最后, sqlite3_column_double() 返回浮点数. 不一定非要按照sqlite3_column_type()接口返回的数据类型来获取数据. 数据类型不同时软件将自动转换. SQLite3 C/C++ 开发接口简介（API函数） 三 2.3 用户自定义函数 可以使用以下的方法来创建用户自定义的SQL函数: typedef struct sqlite3_value sqlite3_value; int sqlite3_create_function( sqlite3 *, const char *zFunctionName, int nArg, int eTextRep, void*, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); int sqlite3_create_function16( sqlite3*, const void *zFunctionName, int nArg, int eTextRep, void*, void (*xFunc)(sqlite3_context*,int,sqlite3_value**), void (*xStep)(sqlite3_context*,int,sqlite3_value**), void (*xFinal)(sqlite3_context*) ); #define SQLITE_UTF8 1 #define SQLITE_UTF16 2 #define SQLITE_UTF16BE 3 #define SQLITE_UTF16LE 4 #define SQLITE_ANY 5 nArg 参数用来表明自定义函数的参数个数. 如果参数值为0，则表示接受任意个数的参数. 用 eTextRep 参数来表明传入参数的编码形式. 参数值可以是上面的五种预定义值. SQLite3 允许同一个自定义函数有多种不同的编码参数的版本. 数据库引擎会自动选择转换参数编码个数最少的版本使用. 普通的函数只需要设置 xFunc 参数，而把 xStep 和 xFinal 设为NULL. 聚合函数则需要设置 xStep 和 xFinal 参数，然后把 xFunc 设为NULL. 该方法和使用sqlite3_create_aggregate() API一样. sqlite3_create_function16()和sqlite_create_function()的不同就在于自定义的函数名一个要求是 UTF-16 编码，而另一个则要求是 UTF-8. 请注意自定函数的参数目前使用了sqlite3_value结构体指针替代了SQLite version 2.X中的字符串指针. 下面的函数用来从sqlite3_value结构体中提取数据: const void *sqlite3_value_blob(sqlite3_value*); int sqlite3_value_bytes(sqlite3_value*); int sqlite3_value_bytes16(sqlite3_value*); double sqlite3_value_double(sqlite3_value*); int sqlite3_value_int(sqlite3_value*); long long int sqlite3_value_int64(sqlite3_value*); const unsigned char *sqlite3_value_text(sqlite3_value*); const void *sqlite3_value_text16(sqlite3_value*); int sqlite3_value_type(sqlite3_value*); 上面的函数调用以下的API来获得上下文内容和返回结果: void *sqlite3_aggregate_context(sqlite3_context*, int nbyte); void *sqlite3_user_data(sqlite3_context*); void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_double(sqlite3_context*, double); void sqlite3_result_error(sqlite3_context*, const char*, int); void sqlite3_result_error16(sqlite3_context*, const void*, int); void sqlite3_result_int(sqlite3_context*, int); void sqlite3_result_int64(sqlite3_context*, long long int); void sqlite3_result_null(sqlite3_context*); void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*)); void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*)); void sqlite3_result_value(sqlite3_context*, sqlite3_value*); void *sqlite3_get_auxdata(sqlite3_context*, int); void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*)); 2.4 用户自定义排序规则 下面的函数用来实现用户自定义的排序规则: sqlite3_create_collation(sqlite3*, const char *zName, int eTextRep, void*, int(*xCompare)(void*,int,const void*,int,const void*)); sqlite3_create_collation16(sqlite3*, const void *zName, int eTextRep, void*, int(*xCompare)(void*,int,const void*,int,const void*)); sqlite3_collation_needed(sqlite3*, void*, void(*)(void*,sqlite3*,int eTextRep,const char*)); sqlite3_collation_needed16(sqlite3*, void*, void(*)(void*,sqlite3*,int eTextRep,const void*)); sqlite3_create_collation() 函数用来声明一个排序序列和实现它的比较函数. 比较函数只能用来做文本的比较. eTextRep 参数可以取如下的预定义值 SQLITE_UTF8, SQLITE_UTF16LE, SQLITE_UTF16BE, SQLITE_ANY，用来表示比较函数所处理的文本的编码方式. 同一个自定义的排序规则的同一个比较函数可以有 UTF-8, UTF-16LE 和 UTF-16BE 等多个编码的版本. sqlite3_create_collation16()和sqlite3_create_collation() 的区别也仅仅在于排序名称的编码是 UTF-16 还是 UTF-8. 可以使用 sqlite3_collation_needed() 函数来注册一个回调函数，当数据库引擎遇到未知的排序规则时会自动调用该函数. 在回调函数中可以查找一个相似的比较函数，并激活相应的sqlite_3_create_collation()函数. 回调函数的第四个参数是排序规则的名称，同样 sqlite3_collation_needed采用 UTF-8 编码. sqlite3_collation_need16() 采用 UTF-16 编码. SQLite 数据库加密的一种解决方案 SQLite是一个非常小巧的跨平台嵌入式数据库，它的数据库以文件的形式存放在本地磁盘上，但是在其开源的免费版中它却缺少了一个数据库中几乎是必备的功能，那就是对于数据库的加密。SQLite的数据库文件可以被任何的文本编辑工具打开，从而获取到其中的数据，这一点令很多开发者感到不安。 但是其实SQLite是支持数据库加密的，前些天看到了网友arris的帖子，具体如下： sqlite的源代码中原本就考虑了加密的实现，并且保留了接口sqlite3_key和 sqlite3_rekey，只是这两个函数在free版本中没有实现，但幸运的是，sqlite的源代码的代码是开放并允许修改，我们可以很方便的增加加密的实现。在 http://www.sqlite.com.cn/POParticle/3/216.Html链接的的代码包中就包含有可加密sqlite的源代码的实现，我根据这个包编译了一个可加密的sqlite。这个包加密实现调用了windows API 的加密函数，所以只能在windows中使用。 这个可加密的版本是在一个ADO.NET 2.0 SQLite Data Provider的基础上改过来的 （http://www.sqlite.com.cn/POParticle/3/216.Html），据原作者声称效率损失在千分之一以下。原始工程是基于VS2005的，但是考虑到其普及性还不是很广，所以重新建立了一个居于VC2003的工程。 其实SQLite的两个加密函数使用起来非常的简单，下面分情况说明： ① 给一个未加密的数据库添加密码：如果想要添加密码，则可以在打开数据库文件之后， 关闭数据库文件之前的任何时刻调用sqlite3_key函数即可，该函数有三个参数，其中第一个参数为数据库对象，第二个参数是要设定的密码，第三个是密码的长度。例如：sqlite3_key(db,\"1q2w3e4r\ //给数据库设定密码1q2w3e4r ② 读取一个加密数据库中的数据：完成这个任务依然十分简单，你只需要在打开数据库 之后，再次调用一下sqlite3_key函数即可，例如，但数据库密码是123456时，你只需要在代码中加入sqlite3_key(db,\"123456\ ① 更改数据库密码：首先你需要使用当前的密码正确的打开数据库，之后你可以调用 sqlite3_rekey(db,\"112233\来更改数据库密码。 ② 删除密码：也就是把数据库恢复到明文状态。这时你仍然只需要调用sqlite3_rekey 函数，并且把该函数的第二个参数置为NULL或者\"\或者把第三个参数设为0。 加密后数据库文件显示为乱码： 为此我建立了一个简单的示例： sqlite3 *db; sqlite3_stmt *stat; char *zErrMsg = 0; char temp[256], FileRoot[256]; char buffer2[1024]=\"0\"; sprintf(temp, _T(\"%s\"), _T(\"utf.db\")); CCodingConv::GB2312_2_UTF8(FileRoot, 256, temp, 0); sqlite3_open(FileRoot, &db); if(db == NULL) { return -1; } sqlite3_key(db,\"1q2w3e4r\ sqlite3_exec(db, \"CREATE TABLE list (fliename varchar(128) UNIQUE, fzip text);\&zErrMsg); sqlite3_prepare(db, \"insert into list values ('中文GB2312编码',?);\ strcpy(temp, \"测试数据UTF-8的支持情况\"); int len = (int)strlen(temp); sqlite3_bind_text(stat, 1, temp, len, NULL); sqlite3_step(stat); sqlite3_prepare(db, \"select * from list;\ sqlite3_step(stat); const unsigned char * test = sqlite3_column_text(stat, 1); int size = sqlite3_column_bytes(stat, 1); printf(\"%s\ sqlite3_finalize(stat); //sqlite3_rekey(db,\"\ sqlite3_close(db); 具体的源代码如下： SQLite3.3.7 加密版源代码（VC2003） SQLite3.3.7 加密版源代码（VC2005） 例子1，例子2 SQLite的类型亲和性分析 一、类型亲和性介绍 SQLite不强制数据类型约束。任何数据都可以插入任何列。你可以向一个整型列中插入任意长度的字符串，向布尔型列中插入浮点数，或者向字符型列中插入日期型值。在 Create TABLE 中所指定的数据类型不会限制在该列中插入任何数据。任何列均可接受任意长度的字符串（只有一种情况除外：标志为INTEGER PRIMARY KEY的列只能存储64位整数， 当向这种列中插数据除整数以外的数据时，将会产生错误。）但SQLite确实使用声明的列类型来指示你所期望的格式。所以，例如你向一个整型列中插入字符串时，SQLite会试图将该字符串转换成一个整数。如果可以转换，它将插入该整数；否则，将插入字符串。这是一个特性，而不是一个bug。这种特性被称为类型或列亲和性(type or column affinity). 二、类型亲和性总结(优点)： 1 提高和其它DBMS的兼容性，让用户就像是在用一般的DBMS一样而使用它，提高了容错能力。 2 SQLite支持的数据类型只有五种，而其它的大型DBMS支持的数据类型有几十种，那么如果要将其它的数据转换成SQLite下的数据就根本不能实现，所以就将它的数据类型设计为亲和性的，数据类型种类少了系统实现会简单很多，整个系统也就不会太庞大，因为如果有太多的数据类型限制的话，本身系统在实现方面也会困难些。然而，虽然它支持的类型虽然只有五种，可是实际上任何类型都支持了，这就是SQLite数据类型亲和性的巧妙之处。由此我个人认为这也就是将数据类型设计成为亲和性的初衷。 3 在插入数据的时候只要做一些检查和转换即可，实现容易 三、数据类型亲和性(缺点)： 1. 在对表中数据进行统计方面如果有不一致的数据存在则运算比较混乱，其实也就是放宽政策为的是让更多人去维护。不过它自己是有处理方法的，如果在运算时出现不同类型的数据时就忽略不计等(我认为这点也是很牵强，因为如果跳过就会得到一些不合乎人期望的结果，但我认为一般情况下，对于一列数据来说，基本上会是一致的，因为如果在很大程序上不一致的话就没什么意义的)。 2. 还有在数据比较方面也存在同样的问题，不过也有相应的补救措施，自己规定了比较准则： a) 一个具有空存储类型的值被认为小于任何值（包括另外一个具有空存储类型的值）。 b) 一个整数值或实数值小于任何文本值和BLOB值。 当一个整数或实数和另一个整数或实数相比较的时候，则按照实际数值来比较。 c) 一个文本值小于BLOB值。当两个文本值相比较的时候，则用C语言类库中的memcmp()函数来比较。然而，有时候也不是这样的，比如在下面所描述的“用户定义的整理顺序”情况下。 d) 当两个BLOB文本被比较的时候，结果决定于memcmp()函数。 SQLite的查询优化 SQLite是个典型的嵌入式DBMS，它有很多优点，它是轻量级的，在编译之后很小，其中一个原因就是在查询优化方面比较简单，它只是运用索引机制来进行优化的，经过对SQLite的查询优化的分析以及对源代码的研究，我将SQLite的查询优总结如下： 一、影响查询性能的因素： 1． 对表中行的检索数目，越小越好 2． 排序与否。 3． 是否要对一个索引。 4． 查询语句的形式 二、几个查询优化的转换 1． 对于单个表的单个列而言，如果都有形如T.C=expr这样的子句，并且都是用OR操作符连接起来，形如： x = expr1 OR expr2 = x OR x = expr3 此时由于对于OR，在SQLite中不能利用索引来优化，所以可以将它转换成带有IN操作符的子句：x IN(expr1,expr2,expr3)这样就可以用索引进行优化，效果很明显，但是如果在都没有索引的情况下OR语句执行效率会稍优于IN语句的效率。 2． 如果一个子句的操作符是BETWEEN，在SQLite中同样不能用索引进行优化，所以也要进行相应的等价转换： 如：a BETWEEN b AND c可以转换成：(a BETWEEN b AND c) AND (a>=b) AND (a<=c)。 在上面这个子句中， (a>=b) AND (a<=c)将被设为dynamic且是(a BETWEEN b AND c)的子句，那么如果BETWEEN语句已经编码，那么子句就忽略不计，如果存在可利用的index使得子句已经满足条件，那么父句则被忽略。 3． 如果一个单元的操作符是LIKE，那么将做下面的转换：x LIKE „abc%‟，转换成：x>=„abc‟ AND x<„abd‟。因为在SQLite中的LIKE是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对LIKE不起作用，但如果不存在索引，那么LIKE在效率方面也还是比不上转换后的效率的。 三、 几种查询语句的处理（复合查询） 1．查询语句为： 执行方法： is one of UNION ALL, UNION, EXCEPT, or INTERSECT. 这个语句的执行过程是先将selectA和selectB执行并且排序，再对两个结果扫描处理，对上面四种操作是不同的，将执行过程分成七个子过程： outA: 将selectA的结果的一行放到最终结果集中 outB: 将selectA的结果的一行放到最终结果集中(只有UNION操作和UNION ALL操作，其它操作都不放入最终结果集中) AltB: 当selectA的当前记录小于selectB的当前记录 AeqB: 当selectA的当前记录等于selectB的当前记录 AgtB: 当selectA的当前记录大于selectB的当前记录 EofA: 当selectA的结果遍历完 EofB: 当selectB的结果遍历完 下面就是四种操作的执行过程： UNION EXCEPT outA,nextA nextA INTERSECT nextA outA, nextA nextB halt halt 执行顺序 UNION ALL AltB: outA, nextA outA, nextA AeqB: AgtB: EofA: EofB: outA, nextA outB, nextB outB, nextB outA, nextA nextA outB, nextB nextB outB, nextB halt outA, nextA outA,nextA 2． 如果可能的话，可以把一个用到GROUP BY查询的语句转换成DISTINCT语句来查询，因为GROUP BY有时候可能会用到index，而对于DISTINCT都不会用到索引的 。 四、子查询扁平化 例子：SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5 对这个SQL语句的执行一般默认的方法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了，如果对上面的SQL进行处理后可以得到如下SQL语句：SELECT x+y AS a FROM t1 WHERE z<100 AND a>5，这个结果显然和上面的一样，但此时只需要对 数据进行查询一次就够了，另外如果在表t1上有索引的话就避免了遍历整个表。 运用flatten方法优化SQL的条件： 1.子查询和外查询没有都用集函数 2.子查询没有用集函数或者外查询不是个表的连接 3.子查询不是一个左外连接的右操作数 4.子查询没有用DISTINCT或者外查询不是个表的连接 5.子查询没有用DISTINCT或者外查询没有用集函数 6.子查询没有用集函数或者外查询没有用关键字DISTINCT 7.子查询有一个FROM语句 8.子查询没有用LIMIT或者外查询不是表的连接 9.子查询没有用LIMIT或者外查询没有用集函数 10.子查询没有用集函数或者外查询没用LIMIT 11.子查询和外查询不是同时是ORDER BY子句 12.子查询和外查询没有都用LIMIT 13.子查询没有用OFFSET 14.外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDER BY和LIMIT 15.外查询没有用集函数子查询不包含ORDER BY 16.复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNION ALL复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在FROM语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面11、12、13条件的限制。 例： SELECT a+1 FROM ( SELECT x FROM tab UNION ALL SELECT y FROM tab UNION ALL SELECT abs(z*2) FROM tab2 ) WHERE a!=5 ORDER BY 1 转换为： SELECT x+1 FROM tab WHERE x+1!=5 UNION ALL SELECT y+1 FROM tab WHERE y+1!=5 UNION ALL SELECT abs(z*2)+1 FROM tab2 WHERE abs(z*2)+1!=5 ORDER BY 1 17.如果子查询是一个复合查询，那么父查询的所有的ORDER BY语句必须是对子查询的列的简单引用 18.子查询没有用LIMIT或者外查询不具有WHERE语句 子查询扁平化是由专门一个函数实现的，函数为： static int flattenSubquery( Parse *pParse, /* Parsing context */ Select *p, /* The parent or outer SELECT statement */ int iFrom, /* Index in p->pSrc->a[] of the inner subquery */ int isAgg, /* True if outer SELECT uses aggregate functions */ int subqueryIsAgg /* True if the subquery uses aggregate functions */ ) 它是在Select.c文件中实现的。显然对于一个比较复杂的查询，如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好！ 五、连接查询 在返回查询结果之前，相关表的每行必须都已经连接起来，在SQLite中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到FROM最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低，但在新版本中，这个优化已经实现。 优化的方法如下： 对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为SQLITE_BIG_DBL（一个系统已经定义的常量）： 1) 如果没有索引，则找有没有在这个表上对rowid的查询条件： 1．如果有Rowid=EXPR，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为1，并完成对这个表的代价估计， 2．如果没有Rowid=EXPR 但有rowid IN (...)，而IN是一个列表，那么记录返回记录数为IN列表中元素的个数，估计代价为NlogN, 3．如果IN不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计一个值，返回记录数为100，代价为200。 4．如果对rowid是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为1000000，并且估计代价也为记录数。 5．如果这个查询还要求排序，则再另外加上排序的代价NlogN 6．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。 2) 如果WHERE子句中存在OR操作符，那么要把这些OR连接的所有子句分开再进行分析。 1． 如果有子句是由AND连接符构成，那么再把由AND连接的子句再分别分析。 2． 如果连接的子句的形式是X 4． 如果这个查询要求排序，则再在上面总代价上再乘上排序代价NlogN 5． 如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。 3) 如果有索引，则统计每个表的索引信息，对于每个索引： 1． 先找到这个索引对应的列号，再找到对应的能用到（操作符必须为=或者是IN（…））这个索引的WHERE子句，如果没有找到，则退出对每个索引的循环，如果找到，则判断这个子句的操作符是什么，如果是=，那么没有附加的代价，如果是IN（sub-select），那么估计它附加代价inMultiplier为25，如果是IN（list），那么附加代价就是N（N为list的列数）。 2． 再计算总的代价和总的查询结果记录数和代价。 3． nRow = pProbe->aiRowEst[i] * inMultiplier;/*计算行数*/ 4． cost = nRow * estLog(inMultiplier);/*统计代价*/ 5． 如果找不到操作符为=或者是IN（…）的子句，而是范围的查询，那么同样只好估计查询结果记录数为nRow/3，估计代价为cost/3。 6． 同样，如果此查询要求排序的话，再在上面的总代价上加上NlogN 7． 如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。 4) 通过上面的优化过程，可以得到对一个表查询的总代价（就是上面各个代价的总和），再对第二个表进行同样的操作，这样如此直到把FROM子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。 5) 所以循环的嵌套顺序不一定是与FROM子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。 六、索引 在SQLite中，有以下几种索引： 1) 单列索引 2) 多列索引 3) 唯一性索引 4) 对于声明为：INTEGER PRIMARY KEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。 运用索引的注意事项： 1) 对于一个很小的表来说没必要建立索引 2) 在一个表上如果经常做的是插入更新操作，那么就要节制使用索引 3) 也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引 索引的运用时机： 1) 操作符：=、>、<、IN等 2) 操作符BETWEEN、LIKE、OR不能用索引， 如BETWEEN：SELECT * FROM mytable WHERE myfield BETWEEN 10 and 20; 这时就应该将其转换成： SELECT * FROM mytable WHERE myfield >= 10 AND myfield <= 20; 此时如果在myfield上有索引的话就可以用了，大大提高速度 再如LIKE：SELECT * FROM mytable WHERE myfield LIKE 'sql%'; 此时应该将它转换成： SELECT * FROM mytable WHERE myfield >= 'sql' AND myfield < 'sqm'; 此时如果在myfield上有索引的话就可以用了，大大提高速度 再如OR：SELECT * FROM mytable WHERE myfield = 'abc' OR myfield = 'xyz'; 此时应该将它转换成： SELECT * FROM mytable WHERE myfield IN ('abc', 'xyz'); 此时如果在myfield上有索引的话就可以用了，大大提高速度 3) 有些时候索引都是不能用的，这时就应该遍历全表（程序演示） SELECT * FROM mytable WHERE myfield % 2 = 1; SELECT * FROM mytable WHERE substr(myfield, 0, 1) = 'w'; SELECT * FROM mytable WHERE length(myfield) < 5; SQLite查询优化的例子 上次讲到了SQLite的查询优化代码中的具体实现，现在来看一下它的几个实例： 1 #include \"stdio.h\" 2 #include \"sqlite3.h\" 3 #include 4 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql); 5 6 int main(int argc, char **argv) 7 { 8 sqlite3 *db; 9 char *zErr; 10 int rc; 11 char *sql; 12 sqlite3_stmt *stmt=0; 13 14 15 16 17 18 19 20 21 rc = sqlite3_open(\"memory.db\ if(rc) { fprintf(stderr, \"Can't open database: %sn\ sqlite3_close(db); } //下面是所建的各个表的结构 sql=\"CREATE TABLE t1 (num int,word TEXT NOT NULL)\"; //sql=\"CREATE TABLE t4 (num INTEGER NOT NULL,word TEXT NOT NULL)\"; 22 NULL)\"; 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 //sql=\"CREATE TABLE t3 (num INTEGER NOT NULL,word TEXT NOT rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); if(rc != SQLITE_OK) { if (zErr != NULL) { fprintf(stderr, \"SQL error: %sn\ sqlite3_free(zErr); } } //下面是对所以插入进行手动提交，这样可以加快插入速度 //sqlite3_exec(db,\"BEGIN\ //插入1000000条记录 //for (int i=0;i<1000000;i++) //{ // sql = sqlite3_mprintf(\"insert into t1 values(%d,'%s')\ // rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); //} //sqlite3_exec(db,\"COMMIT\ sql=\"create index t1nwindex on t1(num)\"; rc=sqlite3_exec(db, sql, NULL, NULL, &zErr); if(rc != SQLITE_OK) { if (zErr != NULL) { fprintf(stderr, \"SQL error: %sn\ sqlite3_free(zErr); } } //sql=\"drop index t1nwindex\"; //sql=\"drop index t3index\"; //sql=\"delete from t2\"; rc=sqlite3_exec(db, sql, NULL, NULL, &zErr); if(rc != SQLITE_OK) { 55 if (zErr != NULL) { 56 fprintf(stderr, \"SQL error: %sn\ 57 sqlite3_free(zErr); 58 } 59 } 60 61 printf(\"查询结果是：n\"); 62 //sql=\"select * from t1 where num=3000 or num=2000\";//有INTEGER PRIMARY KEY，快 63 //sql=\"select * from t2 where num=3000 or num=2000\";//没有索引，慢 64 //sql=\"select * from t3 where num=3000 or num=2000\";//有索引，快 65 66 //这里交换位置了，但是结果用的时间想差比较大的原因是，t1是用索引存储的，但是它不是由create index 67 //而创建的，所以系统还不会把它作为索引处理，所以这两个表就只是无索引的表，在内部优化计算代价只是对它 68 //进行估计，因为源代码中没有捕获到下面的查询条件，所以都是系统最大值（源代码中有），所以就嵌套顺序没 69 //变，所以出现下面的差异。 70 //sql=\"SELECT count(*) FROM t3, t1 WHERE t1.num = t3.num\";//比下面的快，由于内层少 71 //sql=\"SELECT count(*) FROM t1, t3 WHERE t1.num = t3.num\";//比上面的慢，由于内层多 72 73 //下面这个已经内部实现优化，所以所用时间是相同的 74 //sql=\"SELECT * FROM t2, t3 WHERE t2.num = t3.num\";//有索引，稍快 75 //sql=\"SELECT * FROM t3, t2 WHERE t2.num = t3.num\";//同上，内部已经优化 76 77 //sql=\"select * from t3 where num=8000\";//有索引，快 78 //sql=\"select * from t1 where num%2=0\";//有索引,但不能用，很慢 79 //sql=\"select * from t1 where num=8000\";//没有索引，慢 80 81 //BETWEEN的转换优化---内部已经实现优化,如果有索引的话快一点 82 //sql=\"select count(*) from t2 where word between 'goodl' and 'goodm'\";//BETWEEN 83 //sql=\"select count(*) from t2 where word >='goodl' and word<'goodm'\";//BETWEEN的转换 84 85 //LIKE的转换优化---内部已经实现优化 86 //sql=\"select count(*) from t2 where word like 'goodl%'\";//有索引不起作用 87 //sql=\"select count(*) from t2 where word >='goodl' and word <'goodm'\";//如果有索引会更快 88 89 //IN的转换优化---内部没有实现优化,但此时如果可以用索引的话就会很好 90 //如果不用索引则在这里体现不出IN比OR优，而如果有索引则差别很明显 91 //sql=\"select count(*) from t2 where word in('goodllll','goodkkkk','goodaaaa')\"; 92 //sql=\"select count(*) from t2 where word ='goodllll' or word ='goodkkkk' or word='goodaaaa'\"; 93 94 int start=GetTickCount(); 95 query(db,stmt,sql); 96 printf(\"the time has pass:%dmsn\ 97 sqlite3_close(db); 98 return 0; 99 } 100 101 void query(sqlite3 *db,sqlite3_stmt *stmt,char * sql){ 102 int rc,ncols,i; 103 const char *tail; 104 rc = sqlite3_prepare(db, sql, -1, &stmt, &tail); 105 if(rc != SQLITE_OK) { 106 fprintf(stderr, \"SQL error: %sn\ 107 } 108 rc = sqlite3_step(stmt); 109 ncols = sqlite3_column_count(stmt); 110 while(rc == SQLITE_ROW) { 111 for(i=0; i < ncols; i++) { 112 fprintf(stderr, \"'%s' \ 113 } 114 fprintf(stderr, \"n\"); 115 rc = sqlite3_step(stmt); 116 } 117 } SQLite小技巧 收集SQLite与Sql Server的语法差异 1.返回最后插入的标识值 返回最后插入的标识值sql server用@@IDENTITY sqlite用标量函数LAST_INSERT_ROWID() 返回通过当前的 SQLConnection 插入到数据库的最后一行的行标识符（生成的主键）。此值与 SQLConnection.lastInsertRowID 属性返回的值相同。 2.top n 在sql server中返回前2行可以这样： select top 2 * from aa order by ids desc sqlite中用LIMIT，语句如下： select * from aa order by ids desc LIMIT 2 3.GETDATE ( ) 在sql server中GETDATE ( )返回当前系统日期和时间 sqlite中没有 4.EXISTS语句 sql server中判断插入（不存在ids=5的就插入） IF NOT EXISTS (select * from aa where ids=5) BEGIN insert into aa(nickname) select 't' END 在sqlite中可以这样 insert into aa(nickname) select 't' where not exists(select * from aa where ids=5) 5.嵌套事务 sqlite仅允许单个活动的事务 6.RIGHT 和 FULL OUTER JOIN sqlite不支持 RIGHT OUTER JOIN 或 FULL OUTER JOIN 7.可更新的视图 sqlite视图是只读的。不能对视图执行 DELETE、INSERT 或 UPDATE 语句，sql server是可以对视图 DELETE、INSERT 或 UPDATE sqlite查询数据库中存在的所有表 From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named \"SQLITE_MASTER \". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. SQL code SELECT name FROM sqlite_master WHERE type= ' table ' ORDER BY name; 因篇幅问题不能全部显示，请点此查看更多更全内容