SQLITE入门学习

1、SQLite介绍自几十年前出现的商业应用程序以来，数据库就成为软件应用程序的主要组成部分。正与数据库管理系统非常关键一样，它们也变得非常庞大，并占用了相当多的系统资源，增加了管理的复杂性。随着软件应用程序逐渐模块模块化，一种新型数据库会比大型复杂的传统数据库管理系统更适应。嵌入式数据库直接在应用程序进程中运行，提供了零配置（zero-configuration）运行模式，并且资源占用非常少。SQLite是一个开源的嵌入式关系数据库，它在2000年由D.RichardHipp发布，它的减少应用程序管理数据的开销，SQLite可移植性好，很容易使用，很小，高效而且可靠。SQLite嵌入到使用它的应用程序中，它们共用相同的进程空间，而不是单独的一个进程。从外部看，它并不像一个RDBMS，但在进程内部，它却是完整的，自包含的数据库引擎。嵌入式数据库的一大好处就是在你的程序内部不需要网络配置，也不需要管理。因为客户端和服务器在同一进程空间运行。SQLite的数据库权限只依赖于文件系统，没有用户帐户的概念。SQLite有数据库级锁定，没有网络服务器。它需要的内存，其它开销很小，适合用于嵌入式设备。你需要做的仅仅是把它正确的编译到你的程序。2、架构(architecture)SQLite采用了模块的设计，它由三个子系统，包括8个独立的模块构成。2.1、接口(Interface)接口由SQLiteCAPI组成，也就是说不管是程序、脚本语言还是库文件，最终都是通过它与SQLite交互的(我们通常用得较多的ODBC/JDBC最后也会转化为相应CAPI的调用)。2.2、编译器(Compiler)在编译器中，分词器（Tokenizer）和分析器(Parser)对SQL进行语法检查，然后把它转化为底层能更方便处理的分层的数据结构---语法树，然后把语法树传给代码生成器(codegenerator)进行处理。而代码生成器根据它生成一种针对SQLite的汇编代码，最后由虚拟机(VirtualMachine)执行。2.3、虚拟机(VirtualMachine)架构中最核心的部分是虚拟机，或者叫做虚拟数据库引擎(VirtualDatabaseEngine,VDBE)。它和Java虚拟机相似，解释执行字节代码。VDBE的字节代码由128个操作码(opcodes)构成，它们主要集中在数据库操作。它的每一条指令都用来完成特定的数据库操作(比如打开一个表的游标)或者为这些操作栈空间的准备(比如压入参数)。总之，所有的这些指令都是为了满足SQL命令的要求(关于VM，后面会做详细介绍)。2.4、后端(Back-End)后端由B-树(B-tree)，页缓存(pagecache，pager)和操作系统接口(即系统调用)构成。B-tree和pagecache共同对数据进行管理。B-tree的主要功能就是索引，它维护着各个页面之间的复杂的关系，便于快速找到所需数据。而pager的主要作用就是通过OS接口在B-tree和Disk之间传递页面。3、SQLite的特点(SQLite’sFeaturesandPhilosophy)3.1、零配置(ZeroConfiguration)3.2、可移植(Portability)：它是运行在Windows,Linux,BSD,MacOSX和一些商用Unix系统，比如Sun的Solaris,IBM的AIX，同样，它也可以工作在许多嵌入式操作系统下，比如QNX,VxWorks,PalmOS,Symbin和WindowsCE。3.3、Compactness：SQLite是被设计成轻量级，自包含的。oneheaderfile,onelibrary,andyou’rerelational,noexternaldatabaseserverrequired3.4、简单(Simplicity)3.5、灵活(Flexibility)3.6、可靠(Reliability)：SQLite的核心大约有3万行标准C代码，这些代码都是模块化的，很容易阅读。主要参考:TheDefinitiveGuidetoSQLite第二节设计与概念

1、API由两部分组成:核心API(coreAPI)和扩展API（extensionAPI）核心API的函数实现基本的数据库操作：连接数据库，处理SQL，遍历结果集。它也包括一些实用函数，比如字符串转换，操作控制，调试和错误处理。扩展API通过创建你自定义的SQL函数去扩展SQLite。1.1、SQLiteVersion3的一些新特点：(1)SQLite的API全部重新设计，由第二版的15个函数增加到88个函数。这些函数包括支持UTF-8和UTF-16编码的功能函数。(2)改进并发性能。加锁子系统引进一种锁升级模型(lockescalationmodel)，解决了第二版的写进程饿死的问题(该问题是任何一个DBMS必须面对的问题)。这种模型保证写进程按照先来先服务的算法得到排斥锁(ExclusiveLock)。甚至，写进程通过把结果写入临时缓冲区(TemporaryBuffer)，可以在得到排斥锁之前就能开始工作。这对于写要求较高的应用，性能可提高400%（引自参考文献）。(3)改进的B-树。对于表采用B+树，大大提高查询效率。(4)SQLite3最重要的改变是它的存储模型。由第二版只支持文本模型，扩展到支持5种本地数据类型。总之，SQLiteVersion3与SQLiteVertion2有很大的不同，在灵活性，特点和性能方面有很大的改进。1.2、主要的数据结构(ThePrincipalDataStructures)SQLite由很多部分组成－parser,tokenize,virtualmachine等等。但是从程序员的角度，最需要知道的是:connection,statements,B-tree和pager。它们之间的关系如下：上图告诉我们在编程需要知道的三个主要方面：API,事务(Transaction)和锁(Locks)。从技术上来说，B-tree和pager不是API的一部分。但是它们却在事务和锁上起着关键作用（稍后将讨论）。1.3、Connections和StatementsConnection和statement是执行SQL命令涉及的两个主要数据结构，几乎所有通过API进行的操作都要用到它们。一个连接(Connection)代表在一个独立的事务环境下的一个连接A(connectionrepresentsasingleconnectiontoadatabaseaswellasasingletransactioncontext)。每一个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)加载到页面缓冲区(pagecache)，之后，B-tree和与之关联的游标就可以访问位于page中的记录了。如果cursor改变了page，为了防止事务回滚，pager必须采取特殊的方式保存原来的page。总的来说，pager负责读写数据库，管理内存缓存和页面（page），以及管理事务，锁和崩溃恢复(这些在事务一节会详细介绍)。总之，关于connection和transaction，你必须知道两件事：(1)对数据库的任何操作，一个连接存在于一个事务下。(2)一个连接决不会同时存在多个事务下。wheneveraconnectiondoesanythingwithadatabase,italwaysoperatesunderexactlyonetransaction,nomore,noless.1.5、核心API核心API主要与执行SQL命令有关，本质上有两种方法执行SQL语句：preparedquery和wrappedquery。Preparedquery由三个阶段构成：preparation，execution和finalization。其实wrappedquery只是对preparedquery的三个过程包装而已，最终也会转化为preparedquery的执行。1.5.1、连接的生命周期(TheConnectionLifecycle)和大多数据库连接相同，由三个过程构成：（1）连接数据库(Connecttothedatabase)：每一个SQLite数据库都存储在单独的操作系统文件中，连接，打开数据库的CAPI为：sqlite3_open()，它的实现位于main.c文件中，如下：intsqlite3_open(constchar*zFilename,sqlite3**ppDb){returnopenDatabase(zFilename,ppDb,SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE,0);}当连接一个在磁盘上的数据库，如果数据库文件存在，SQLite打开一个文件；如果不存在，SQLite会假定你想创建一个新的数据库。在这种情况下，SQLite不会立即在磁盘上创建一个文件，只有当你向数据库写入数据时才会创建文件，比如：创建表、视图或者其它数据库对象。如果你打开一个数据，不做任何事，然后关闭它，SQLite会创建一个文件，只是一个空文件而已。另外一个不立即创建一个新文件的原因是，一些数据库的参数，比如：编码，页面大小等，只在在数据库创建前设置。默认情况下，页面大小为1024字节，但是你可以选择512-32768字节之间为2幂数的数字。有些时候，较大的页面能更有效的处理大量的数据。（2）执行事务(Performtransactions)：allcommandsareexecutedwithintransactions。默认情况下，事务自动提交，也就是每一个SQL语句都在一个独立的事务下运行。当然也可以通过使用BEGIN..COMMIT手动提交事务。（3）断开连接(Disconnectfromthedatabase)：主要是关闭数据库的文件。1.5.2、执行PreparedQuery前面提到，预处理查询(PreparedQuery)是SQLite执行所有SQL命令的方式，包括以下三个过程：(1)PreparedQuery：分析器（parser），分词器(tokenizer)和代码生成器(codegenerator)把SQLStatement编译成VDBE字节码，编译器会创建一个statement句柄(sqlite3_stmt)，它包括字节码以及其它执行命令和遍历结果集的所有资源。相应的CAPI为sqlite3_prepare()，位于prepare.c文件中，如下：intsqlite3_prepare(sqlite3*db,constchar*zSql,intnBytes,sqlite3_stmt**ppStmt,constchar**pzTail){intrc;rc=sqlite3LockAndPrepare(db,zSql,nBytes,0,ppStmt,pzTail);assert(rc==SQLITE_OK||ppStmt==0||*ppStmt==0);returnrc;}(2)Execution：虚拟机执行字节码，执行过程是一个步进(stepwise)的过程，每一步(step)由sqlite3_step()启动，并由VDBE执行一段字节码。由sqlite3_prepare编译字节代码，并由sqlite3_step()启动虚拟机执行。在遍历结果集的过程中，它返回SQLITE_ROW，当到达结果末尾时，返回SQLITE_DONE。(3)Finalization：VDBE关闭statement，释放资源。相应的CAPI为sqlite3_finalize()。通过下图可以更容易理解该过程：最后以一个具体的例子结束本节，下节讨论事务。#include#include#include\"sqlite3.h\"#pragmacomment(lib,”sqlite3.lib”)intmain(intargc,char**argv){intrc,i,ncols;sqlite3*db;sqlite3_stmt*stmt;char*sql;constchar*tail;//打开数据rc=sqlite3_open(\"foods.db\if(rc){fprintf(stderr,\"Can'topendatabase:%sn\sqlite3_close(db);exit(1);}sql=\"select*fromepisodes\";//预处理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;i1、虚拟机（VirtualMachine）VDBE是SQLite的核心，它的上层模块和下层模块都是本质上都是为它服务的。它的实现位于vbde.c,vdbe.h,vdbeapi.c,vdbeInt.h,和vdbemem.c几个文件中。它通过底层的基础设施B+Tree执行由编译器（Compiler）生成的字节代码，这种字节代码程序语言(bytecodeprogramminglauguage)是为了进行查询，读取和修改数据库而专门设计的。字节代码在内存中被封装成sqlite3_stmt对象(内部叫做Vdbe，见vdbeInt.h)，Vdbe（或者说statement）包含执行程序所需要的一切：a)abytecodeprogramb)namesanddatatypesforallresultcolumnsc)valuesboundtoinputparametersd)aprogramcountere)anexecutionstackofoperandsf)anarbitraryamountof\"numbered\"memorycellsg)otherrun-timestateinformation(suchasopenBTreeobjects,sorters,lists,sets)字节代码和汇编程序十分类似，每一条指令由操作码和三个操作数构成：。Opcode为一定功能的操作码，为了理解，可以看成一个函数。P1是32位的有符号整数，p2是31位的无符号整数，它通常是导致跳转(jump)的指令的目标地址（destination），当然这了有其它用途；p3为一个以null结尾的字符串或者其它结构体的指针。和CAPI不同的是，VDBE操作码经常变化，所以不应该用字节码写程序。下面的几个CAPI直接和VDBE交互：•sqlite3_bind_xxx()functions•sqlite3_step()•sqlite3_reset()•sqlite3_column_xxx()functions•sqlite3_finalize()为了有个感性，下面看一个具体的字节码程序：sqlite>.mcolsqlite>.honsqlite>.w4153315sqlite>explainselect*fromepisodes;addropcodep1p2p3----------------------------------------0Goto0121Integer002OpenRead02#episodes3SetNumColumns034Rewind0105Recno006Column017Column028Callback309Next0510Close0011Halt0012Transaction0013VerifyCookie01014Goto0115Noop001.1、栈(Stack)一个VDBE程序通常由不同完成特定任务的段（section）构成，每一个段中，都有一些操作栈的指令。这是由于不同的指令有不同个数的参数，一些指令只有一个参数；一些指令没有参数；一些指令有好几个参数，这种情况下，三个操作数就不能满足。考虑到这些情况，指令采用栈来传递参数。(注：从汇编的角度来看，传递参数的方式有好几种，比如：寄存器，全局变量，而堆栈是现代语言常用的方式，它具有很大的灵活性)。而这些指令不会自己做这些事情，所以在它们之前，需要其它一些指令的帮助。VDBE把计算的中间结果保存到内存单元(memorycells)中，其实，堆栈和内存单元都是基于Mem（见vdbeInt.h）数据结构(注：这里的栈，内存单元都是虚拟的，记得一位计算机科学家说过：计算机科学中90%以上的科学都是虚拟化问题。一点不假，OS本质上也是虚拟机，而在这里SQLite，我们也处处可见虚拟化的身影，到后面的OSInterface模块中再仔细讨论这个问题)。1.2、程序体(ProgramBody)这是一个打开episodes表的过程。第一条指令：Integer是为第二条指令作准备的，也就是把第二条指令执行需要的参数压入堆栈，OpenRead从堆栈中取出参数值然后执行。SQLite可以通过ATTACH命令在一个连接中打开多个数据库文件，每当SQLite打开一个数据，它就为之赋一个索引号(index)，maindatabase的索引为0，第一个数据库为1，依次如此。Integer指令数据库索引的值压入栈，而OpenRead从中取出值，并决定打开哪个数据，来看看SQLite文档中的解释：Openaread-onlycursorforthedatabasetablewhoserootpageisP2inadatabasefile.Thedatabasefileisdeterminedbyanintegerfromthetopofthestack.0meansthemaindatabaseand1meansthedatabaseusedfortemporarytables.GivethenewcursoranidentifierofP1.TheP1valuesneednotbecontiguousbutallP1valuesshouldbesmallintegers.ItisanerrorforP1tobenegative.IfP2==0thentaketherootpagenumberfromoffofthestack.Therewillbeareadlockonthedatabasewheneverthereisanopencursor.Ifthedata-basewasunlockedpriortothisinstructionthenareadlockisacquiredaspartofthisinstruction.Areadlockallowsotherprocessestoreadthedatabasebutprohibitsanyotherprocessfrommodifyingthedatabase.Thereadlockisreleasedwhenallcursorsareclosed.Ifthisinstructionattemptstogetareadlockbutfails,thescriptterminateswithanSQLITE_BUSYerrorcode.TheP3valueisapointertoaKeyInfostructurethatdefinesthecontentandcollatingsequenceofindices.P3isNULLforcursorsthatarenotpointingtoindices.再来看看SetNumColumns指令设置游标将指向的列。P1为游标的索引（这里为0，刚刚打开），P2为列的数目，episodes表有三列。继续Rewind指令，它将游标重新设置到表的开始，它会检查表是否为空（即没有记录），如果没有记录，它会导致指令指针跳到P2指定的指令处。在这里，P2为10，即Close指令。一旦Rewind设置游标，接下就执行5-9这几条指令，它们的主要功能是遍历结果集，Recno把由游标P1指定的记录的关键字压入堆栈。Column指令从由P1指定的游标，P2指定的列取值。5,6,7三条指令分别把id(primarykey),season和name字段的值压入栈。接下来，Callback指令从栈中取出三个值（P1），然后形成一个记录数组，存储在内存单元中(memorycell)。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命令可能会改变数据库模式（suchasALTERTABLE,DROPTABLE,orCREATETABLE）。一旦发生这种情况，之前编译的statement就会变得无效，数据库模式信息记录在数据库文件的根页面中。类似，每一个statement都有一份用来比较的在编译时刻该模式的备份，VerifyCookie的功能就是检查它们是否匹配，如果不匹配，将采取相关操作。如果两者匹配，会执行下一条指令Goto；它会跳到程序的主要部分，即第一条指令，打开表读取记录。这里有两点值得注意：(1)Transaction指令自己不会获取锁（TheTransactioninstructiondoesn’tacquireanylocksinitself）。它的功能相当于BEGIN，而实际是由OpenRead指令获取sharelock的。当事务关闭时释放锁，这取决于Halt指令，它会进行扫尾工作。(2)statement对象（VDBE程序）所需的存储空间在程序执行前就已经确定。这有原于两个重要事实：首先，栈的深度不会比指令的数目还多（通常少得多）。其次，在执行VDBE程序之前，SQLite可以计算出为分配资源所需要的内存。1.4指令的类型(InstructionTypes)每条指令都完成特定的任务，而且通常和别的指令有关。大体上来说，指令可分为三类：（1)Valuemanipulation：这些指令通常完成算术运算，比如：add,subtract,divide；逻辑运算，比如：AND和OR；还有字符串操作。（2)Datamanagement：这些指令操作在内存和磁盘上的数据。内存指令进行栈操作或者在内存单元之间传递数据。磁盘操作指令控制B-tree和pager打开或操作游标，开始或结束事务，等等。（3)Controlflow：控制指令主要是移动指令指针。1.5、程序的执行(Programexecution)最后我们来看VM解释器是如何实现以及字节代码大致是如何执行的。在vdbe.c文件中有一个很关键的函数：//执行VDBE程序intsqlite3VdbeExec(Vdbe*p/*TheVDBE*/)该函数是执行VDBE程序的入口。来看看它的内部实现：/*从这里开始执行指令**pc为程序计数器(int)*/for(pc=p->pc;rc==SQLITE_OK;pc++){//取得操作码pOp=&p->aOp[pc];switch(pOp->opcode){caseOP_Goto:{/*jump*/CHECK_FOR_INTERRUPT;pc=pOp->p2-1;break;}……}}从这段代码，我们大致可以推出VM执行的原理：VM解释器实际上是一个包含大量switch语句的for循环，每一个switch语句实现一个特定的操作指令。2、B-tree和PagerB-Tree使得VDBE可以在O(logN)下查询，插入和删除数据，以及O(1)下双向遍历结果集。B-Tree不会直接读写磁盘，它仅仅维护着页面(pages)之间的关系。当B-TREE需要页面或者修改页面时，它就会调用Pager。当修改页面时，pager保证原始页面首先写入日志文件，当它完成写操作时，pager根据事务状态决定如何做。B-tree不直接读写文件，而是通过pagecache这个缓冲模块读写文件对于性能是有重要意义的（注：这和操作系统读写文件类似，在Linux中,操作系统的上层模块并不直接调用设备驱动读写设备，而是通过一个高速缓冲模块调用设备驱动读写文件,并将结果存到高速缓冲区）。2.1、数据库文件格式（DatabaseFileFormat）数据库中所有的页面都按从1开始顺序标记。一个数据库由许多B-tree构成——每一个表和索引都有一个B-tree（注：索引采用B-tree，而表采用B+tree，这主要是表和索引的需求不同以及B-tree和B+tree的结构不同决定的：B+tree的所有叶子节点包含了全部关键字信息，而且可以有两种顺序查找——具体参见《数据结构》，严蔚敏。而B-tree更适合用来作索引）。所有表和索引的根页面都存储在sqlite_master表中。数据库中第一个页面（page1）有点特殊，page1的前100个字节包含一个描述数据库文件的特殊的文件头。它包括库的版本，模式的版本，页面大小，编码等所有创建数据库时设置的参数。这个特殊的文件头的内容在btree.c中定义，page1也是sqlite_master表的根页面。2.1、页面重用及回收(PageReuseandVacuum)SQLite利用一个空闲列表(freelist)进行页面回收。当一个页面的所有记录都被删除时，就被插入到该列表。当运行VACUUM命令时，会清除freelist，所以数据库会缩小，本质上它是在新的文件重新建立数据库，而所有使用的页在都被拷贝过去，而freelist却不会，结果就是一个新的，变小的数据库。当数据库的autovacuum开启时，SQLite不会使用freelist，而且在每一次commit时自动压缩数据库。2.2、B-Tree记录B-tree中页面由B-tree记录组成，也叫做payloads。每一个B-tree记录，或者payload有两个域：关键字域(keyfield)和数据域(datafield)。Keyfield就是ROWID的值，或者数据库中表的关键字的值。从B-tree的角度，datafield可以是任何无结构的数据。数据库的记录就保存在这些datafields中。B-tree的任务就是排序和遍历，它最需要就是关键字。Payloads的大小是不定的，这与内部的关键字和数据域有关，当一个payload太大不能存在一个页面内进便保存到多个页面。B+Tree按关键字排序，所有的关键字必须唯一。表采用B+tree，内部页面不包含数据，如下：B+tree中根页面(rootpage)和内部页面(internalpages)都是用来导航的，这些页面的数据域都是指向下级页面的指针，仅仅包含关键字。所有的数据库记录都存储在叶子页面(leafpages)内。在叶节点一级，记录和页面都是按照关键字的顺序的，所以B-tree可以水平方向遍历，时间复杂度为O(1)。2.3、记录和域（RecordsandFields）位于叶节点页面的数据域的记录由VDBE管理，数据库记录以二进制的形式存储，但有一定的数据格式。记录格式包括一个逻辑头（logicalheader）和一个数据区(datasegment)，headersegment包括header的大小和一个数据类型数组，数据类型用来在datasegment的数据的类型，如下：2.4、层次数据组织(HierarchicalDataOrganization)从上往下，数据越来越无序，从下向上，数据越来越结构化.2.5、B-TreeAPIB-Tree模块有它自己的API，它可以独立于CAPI使用。另一个特点就是它支持事务。由pager处理的事务，锁和日志都是为B-tree服务的。根据功能可以分为以下几类：2.5.1、访问和事务函数sqlite3BtreeOpen:Opensanewdatabasefile.ReturnsaB-treeobject.sqlite3BtreeClose:Closesadatabase.sqlite3BtreeBeginTrans:Startsanewtransaction.sqlite3BtreeCommit:Commitsthecurrenttransaction.sqlite3BtreeRollback:Rollsbackthecurrenttransaction.sqlite3BtreeBeginStmt:Startsastatementtransaction.sqlite3BtreeCommitStmt:Commitsastatementtransaction.sqlite3BtreeRollbackStmt:Rollsbackastatementtransaction.2.5.2、表函数sqlite3BtreeCreateTable:Createsanew,emptyB-treeinadatabasefile.sqlite3BtreeDropTable:DestroysaB-treeinadatabasefile.sqlite3BtreeClearTable:RemovesalldatafromaB-tree,butkeepstheB-treeintact.2.5.3、游标函数(CursorFunctions)sqlite3BtreeCursor:CreatesanewcursorpointingtoaparticularB-tree.sqlite3BtreeCloseCursor:ClosestheB-treecursor.sqlite3BtreeFirst:MovesthecursortothefirstelementinaB-tree.sqlite3BtreeLast:MovesthecursortothelastelementinaB-tree.sqlite3BtreeNext:Movesthecursortothenextelementaftertheoneitiscurrentlypointingto.sqlite3BtreePrevious:Movesthecursortothepreviouselementbeforetheoneitiscurrentlypointingto.sqlite3BtreeMoveto:Movesthecursortoanelementthatmatchesthekeyvaluepassedinasaparameter.2.5.4、记录函数(RecordFunctions)sqlite3BtreeDelete:Deletestherecordthatthecursorispointingto.sqlite3BtreeInsert:InsertsanewelementintheappropriateplaceoftheB-tree.sqlite3BtreeKeySize:Returnsthenumberofbytesinthekeyoftherecordthatthecursorispointingto.sqlite3BtreeKey:Returnsthekeyoftherecordthecursoriscurrentlypointingto.sqlite3BtreeDataSize:Returnsthenumberofbytesinthedatarecordthatthecursoriscurrentlypointingto.sqlite3BtreeData:Returnsthedataintherecordthecursoriscurrentlypointingto.2.5.5、配置函数(ConfigurationFunctions)sqlite3BtreeSetCacheSize:Controlsthepagecachesizeaswellasthesynchronouswrites(asdefinedinthesynchronouspragma).sqlite3BtreeSetSafetyLevel:ChangesthewaydataissyncedtodiskinordertoincreaseordecreasehowwellthedatabaseresistsdamageduetoOScrashesandpowerfailures.Level1isthesameasasynchronous(nosyncs()occurandthereisahighprobabilityofdamage).Thisistheequivalenttopragmasynchronous=OFF.Level2isthedefault.Thereisaverylowbutnon-zeroprobabilityofdamage.Thisistheequivalenttopragmasynchronous=NORMAL.Level3reducestheprobabilityofdamagetonearzerobutwithawriteperformancereduction.Thisistheequivalenttopragmasynchronous=FULL.sqlite3BtreeSetPageSize:Setsthedatabasepagesize.sqlite3BtreeGetPageSize:Returnsthedatabasepagesize.sqlite3BtreeSetAutoVacuum:Setstheautovacuumpropertyofthedatabase.sqlite3BtreeGetAutoVacuum:Returnswhetherthedatabaseusesautovacuum.sqlite3BtreeSetBusyHandler:Setsthebusyhandler2.6、实例分析最后以sqlite3_open的具体实现结束本节的讨论(参见Version3.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。一个简单的语法树：SELECTrowid,name,seasonFROMepisodesWHERErowid=1LIMIT13.3、代码生成器（CodeGenerator）代码生成器是SQLite中取庞大，最复杂的部分。它与Parser关系紧密，根据语法分析树生成VDBE程序执行SQL语句的功能。由诸多文件构成：select.c,update.c,insert.c,delete.c,trigger.c,where.c等文件。这些文件生成相应的VDBE程序指令，比如SELECT语句就由select.c生成。下面是一个读操作中打开表的代码的生成实现：/*Generatecodethatwillopenatableforreading.*/voidsqlite3OpenTableForReading(Vdbe*v,/*GeneratecodeintothisVDBE*/intiCur,/*Thecursornumberofthetable*/Table*pTab/*Thetabletobeopened*/){sqlite3VdbeAddOp(v,OP_Integer,pTab->iDb,0);sqlite3VdbeAddOp(v,OP_OpenRead,iCur,pTab->tnum);VdbeComment((v,\"#%s\pTab->zName));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()结束指令生成，如下：第四节PageCache之事务处理(1)

本节通过一个具体的例子来分析SQLite原子提交的实现（基于Version3.3.6的代码）。CREATETABLEepisodes(idintegerprimarykey,nametext,cidint)；插入一条记录：insertintoepisodes(name,cid)values(\"cat\;它经过编译器处理后生成的虚拟机代码如下：sqlite>explaininsertintoepisodes(name,cid)values(\"cat\0|Trace|0|0|0|explaininsertintoepisodes(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、初始状态（InitialState）当一个数据库连接第一次打开时，状态如图所示。图中最右边（“Disk”标注）表示保存在存储设备中的内容。每个方框代表一个扇区。蓝色的块表示这个扇区保存了原始数据。图中中间区域是操作系统的磁盘缓冲区。开始的时候，这些缓存是还没有被使用，因此这些方框是空白的。图中左边区域显示SQLite用户进程的内存。因为这个数据库连接刚刚打开，所以还没有任何数据记录被读入，所以这些内存也是空的。2、获取读锁(AcquiringAReadLock)在SQLite写数据库之前，它必须先从数据库中读取相关信息。比如，在插入新的数据时，SQLite会先从sqlite_master表中读取数据库模式(相当于数据字典)，以便编译器对INSERT语句进行分析，确定数据插入的位置。在进行读操作之前，必须先获取数据库的共享锁(sharedlock)，共享锁允许两个或更多的连接在同一时刻读取数据库。但是共享锁不允许其它连接对数据库进行写操作。sharedlock存在于操作系统磁盘缓存，而不是磁盘本身。文件锁的本质只是操作系统的内核数据结构，当操作系统崩溃或掉电时，这些内核数据也会随之消失。3、读取数据一旦得到sharedlock，就可以进行读操作。如图所示，数据先由OS从磁盘读取到OS缓存，然后再由OS移到用户进程空间。一般来说，数据库文件分为很多页，而一次读操作只读取一小部分页面。如图，从8个页面读取3个页面。4、获取ReservedLock在对数据进行修改操作之前，先要获取数据库文件的ReservedLock，ReservedLock和sharedlock的相似之处在于，它们都允许其它进程对数据库文件进行读操作。ReservedLock和SharedLock可以共存，但是只能是一个ReservedLock和多个SharedLock——多个ReservedLock不能共存。所以，在同一时刻，只能进行一个写操作。ReservedLock意味着当前进程(连接)想修改数据库文件，但是还没开始修改操作，所以其它的进程可以读数据库，但不能写数据库。5、创建恢复日志(CreatingARollbackJournalFile)在对数据库进行写操作之前，SQLite先要创建一个单独的日志文件，然后把要修改的页面的原始数据写入日志。回滚日志包含一个日志头(图中的绿色)——记录数据库文件的原始大小。所以即使数据库文件大小改变了，我们仍知道数据库的原始大小。从OS的角度来看，当一个文件创建时，大多数OS(Windows,Linux,MacOSX)不会向磁盘写入数据，新创建的文件此时位于磁盘缓存中，之后才会真正写入磁盘。如图，日志文件位于OS磁盘缓存中，而不是位于磁盘。上面5步的代码的实现：Code其实现过程如下图所示：主要参考：http://www.sqlite.org/atomiccommit.htmlPageCache之事务处理(2)个人认为pager层是SQLite实现最为核心的模块，它具有四大功能：I/O，页面缓存，并发控制和日志恢复。而这些功能不仅是上层Btree的基础，而且对系统的性能和健壮性有关至关重要的影响。其中并发控制和日志恢复是事务处理实现的基础。SQLite并发控制的机制非常简单——封锁机制；别外，它的查询优化机制也非常简单——基于索引。这一切使得整个SQLite的实现变得简单，SQLite变得很小，运行速度也非常快，所以，特别适合嵌入式设备。好了，接下来讨论事务的剩余部分。6、修改位于用户进程空间的页面(ChangingDatabasePagesInUserSpace)页面的原始数据写入日志之后，就可以修改页面了——位于用户进程空间。每个数据库连接都有自己私有的空间，所以页面的变化只对该连接可见，而对其它连接的数据仍然是磁盘缓存中的数据。从这里可以明白一件事：一个进程在修改页面数据的同时，其它进程可以继续进行读操作。图中的红色表示修改的页面。7、日志文件刷入磁盘(FlushingTheRollbackJournalFileToMassStorage)接下来把日志文件的内容刷入磁盘，这对于数据库从意外中恢复来说是至关重要的一步。而且这通常也是一个耗时的操作，因为磁盘I/O速度很慢。这个步骤不只把日志文件刷入磁盘那么简单，它的实现实际上分成两步：首先把日志文件的内容刷入磁盘（即页面数据）；然后把日志文件中页面的数目写入日志文件头，再把header刷入磁盘（这一过程在代码中清晰可见）。代码如下：Code8、获取排斥锁（ObtainingAnExclusiveLock）在对数据库文件进行修改之前(注：这里不是内存中的页面),我们必须得到数据库文件的排斥锁(ExclusiveLock)。得到排斥锁的过程可分为两步：首先得到Pendinglock；然后Pendinglock升级到exclusivelock。Pendinglock允许其它已经存在的Sharedlock继续读数据库文件，但是不允许产生新的sharedlock，这样做目的是为了防止写操作发生饿死情况。一旦所有的sharedlock完成操作，则pendinglock升级到exclusivelock。9、修改的页面写入文件(WritingChangesToTheDatabaseFile)一旦得到exclusivelock，其它的进程就不能进行读操作，此时就可以把修改的页面写回数据库文件，但是通常OS都把结果暂时保存到磁盘缓存中，直到某个时刻才会真正把结果写入磁盘。以上两步的实现代码：Code10、修改结果刷入存储设备(FlushingChangesToMassStorage)为了保证修改结果真正写入磁盘，这一步必不要少。对于数据库存的完整性，这一步也是关键的一步。由于要进行实际的I/O操作，所以和第7步一样，将花费较多的时间。最后来看看这几步是如何实现的：其实以上以上几步是在函数sqlite3BtreeSync()---btree.c中调用的(而关于该函数的调用后面再讲)。代码如下：Code下图可以进一步解释该过程：11、删除日志文件(DeletingTheRollbackJournal)一旦更改写入设备，日志文件将会被删除，这是事务真正提交的时刻。如果在这之前系统发生崩溃，就会进行恢复处理，使得数据库和没发生改变一样；如果在这之后系统发生崩溃，表明所有的更改都已经写入磁盘。SQLite就是根据日志存在情况决定是否对数据库进行恢复处理。删除文件本质上不是一个原子操作，但是从用户进程的角度来看是一个原子操作，所以一个事务看起来是一个原子操作。在许多系统中，删除文件也是一个高代价的操作。作为优化，SQLite可以配置成把日志文件的长度截为0或者把日志文件头清零。12、释放锁(ReleasingTheLock)作为原子提交的最后一步，释放排斥锁使得其它进程可以开始访问数据库了。下图中，我们指明了当锁被释放的时候用户空间所拥有的信息已经被清空了.对于老版本的SQLite你可这么认为。但最新的SQLite会保存些用户空间的缓存不会被清空—万一下一个事务开始的时候，这些数据刚好可以用上呢。重新利用这些内存要比再次从操作系统磁盘缓存或者硬盘中读取要来得轻松与快捷得多，何乐而不为呢？在再次使用这些数据之前，我们必须先取得一个共享锁，同时我们还不得不去检查一下，保证还没有其他进程在我们拥有共享锁之前对数据库文件进行了修改。数据库文件的第一页中有一个计数器，数据库文件每做一次修改，这个计数器就会增长一下。我们可以通过检查这个计数器就可得知是否有其他进程修改过数据库文件。如果数据库文件已经被修改过了，那么用户内存空间的缓存就不得不清空，并重新读入。大多数情况下，这种情况不大会发生，因此用户空间的内存缓存将是有效的，这对于性能提高来说作用是显著的。以上两步是在sqlite3BtreeCommit()---btree.c函数中实现的。代码如下：Code下图可进一步描述该过程：最后来看看sqlite3BtreeSync()和sqlite3BtreeCommit()是如何被调用的。一般来说，事务提交方式为自动提交的话，在虚拟机中的OP_Halt指令实现提交事务，相关代码如下：//虚拟机停机指令caseOP_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;returnSQLITE_BUSY;}returnp->rc?SQLITE_ERROR:SQLITE_DONE;}//当虚拟机要停机时,调用该函数,如果VDBE改变了数据库且为自动//提交模式，则提交这些改变intsqlite3VdbeHalt(Vdbe*p){sqlite3*db=p->db;inti;int(*xFunc)(Btree*pBt)=0;/*Functiontocalloneachbtreebackend*/intisSpecialError;/*SettotrueifSQLITE_NOMEMorIOERR*//*Thisfunctioncontainsthelogicthatdeterminesifastatementor**transactionwillbecommittedorrolledbackasaresultofthe**executionofthisvirtualmachine.****Specialerrors:****IfanSQLITE_NOMEMerrorhasoccuredinastatementthatwritesto**thedatabase,theneitherastatementortransactionmustberolled**backtoensurethetree-structuresareinaconsistentstate.A**statementtransactionisrolledbackifoneisopen,otherwisethe**entiretransactionmustberolledback.****IfanSQLITE_IOERRerrorhasoccuredinastatementthatwritesto**thedatabase,thentheentiretransactionmustberolledback.The**I/Oerrormayhavecausedgarbagetobewrittentothejournal**file.Werethetransactiontocontinueandeventuallyberolled**backthatgarbagemightendupinthedatabasefile.****Inbothoftheabovecases,theVdbe.errorActionvariableis**ignored.Ifthesqlite3.autoCommitflagisfalseandatransaction**isrolledback,itwillbesettotrue.****Othererrors:****Noerror:***/if(sqlite3MallocFailed()){p->rc=SQLITE_NOMEM;}if(p->magic!=VDBE_MAGIC_RUN){/*Alreadyhalted.Nothingtodo.*/assert(p->magic==VDBE_MAGIC_HALT);returnSQLITE_OK;}//释放虚拟机中所有的游标closeAllCursors(p);checkActiveVdbeCnt(db);/*Nocommitorrollbackneedediftheprogramneverstarted*/if(p->pc>=0){/*Checkforoneofthespecialerrors-SQLITE_NOMEMorSQLITE_IOERR*/isSpecialError=((p->rc==SQLITE_NOMEM||p->rc==SQLITE_IOERR)?1:0);if(isSpecialError){/*Thisloopdoesstaticanalysisofthequerytoseewhichofthe**followingthreecategoriesitfallsinto:****Read-only**Querywithstatementjournal**Querywithoutstatementjournal****Wecoulddosomethingmoreelegantthanthisstaticanalysis(i.e.**storethetypeofqueryaspartofthecompliationphase),but**handlingmalloc()orIOfailureisafairlyobscureedgecaseso**thisisprobablyeasier.Todo:Mightbeanopportunitytoreduce**codesizeaverysmallamountthough*/intisReadOnly=1;intisStatement=0;assert(p->aOp||p->nOp==0);for(i=0;inOp;i++){switch(p->aOp[i].opcode){caseOP_Transaction:isReadOnly=0;break;caseOP_Statement:isStatement=1;break;}}/*Ifthequerywasread-only,weneeddonorollbackatall.Otherwise,**proceedwiththespecialhandling.*/if(!isReadOnly){if(p->rc==SQLITE_NOMEM&&isStatement){xFunc=sqlite3BtreeRollbackStmt;}else{/*Weareforcedtorollbacktheactivetransaction.Beforedoing**so,abortanyotherstatementsthishandlecurrentlyhasactive.*/sqlite3AbortOtherActiveVdbes(db,p);sqlite3RollbackAll(db);db->autoCommit=1;}}}/*Iftheauto-commitflagissetandthisistheonlyactivevdbe,then**wedoeitheracommitorrollbackofthecurrenttransaction.****Note:Thisblockalsorunsifoneofthespecialerrorshandled**abovehasoccured.*///如果自动提交事务，则提交事务if(db->autoCommit&&db->activeVdbeCnt==1){if(p->rc==SQLITE_OK||(p->errorAction==OE_Fail&&!isSpecialError)){/*Theauto-commitflagistrue,andthevdbeprogramwas**successfulorhitan'ORFAIL'constraint.Thismeansacommit**isrequired.*///提交事务intrc=vdbeCommit(db);if(rc==SQLITE_BUSY){returnSQLITE_BUSY;}elseif(rc!=SQLITE_OK){p->rc=rc;sqlite3RollbackAll(db);}else{sqlite3CommitInternalChanges(db);}}else{sqlite3RollbackAll(db);}}elseif(!xFunc){if(p->rc==SQLITE_OK||p->errorAction==OE_Fail){xFunc=sqlite3BtreeCommitStmt;}elseif(p->errorAction==OE_Abort){xFunc=sqlite3BtreeRollbackStmt;}else{sqlite3AbortOtherActiveVdbes(db,p);sqlite3RollbackAll(db);db->autoCommit=1;}}/*IfxFuncisnotNULL,thenitisoneofsqlite3BtreeRollbackStmtor**sqlite3BtreeCommitStmt.Callitonceoneachbackend.Ifanerroroccurs**andthereturncodeisstillSQLITE_OK,setthereturncodetothenew**errorvalue.*/assert(!xFunc||xFunc==sqlite3BtreeCommitStmt||xFunc==sqlite3BtreeRollbackStmt);for(i=0;xFunc&&inDb;i++){intrc;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);}}}/*IfthiswasanINSERT,UPDATEorDELETEandthestatementwascommitted,echangecounter.*/if(p->changeCntOn&&p->pc>=0){if(!xFunc||xFunc==sqlite3BtreeCommitStmt){sqlite3VdbeSetChanges(db,p->nChange);}else{sqlite3VdbeSetChanges(db,0);}p->nChange=0;}/*Rollbackorcommitanyschemachangesthatoccurred.*/if(p->rc!=SQLITE_OK&&db->flags&SQLITE_InternChanges){sqlite3ResetInternalSchema(db,0);db->flags=(db->flags|SQLITE_InternChanges);}}/*WehavesuccessfullyhaltedandclosedtheVM.Recordthisfact.*/if(p->pc>=0){db->activeVdbeCnt--;}p->magic=VDBE_MAGIC_HALT;checkActiveVdbeCnt(db);returnSQLITE_OK;}//提交事务,主要调用://sqlite3BtreeSync()---同步btree,sqlite3BtreeCommit()---提交事务staticintvdbeCommit(sqlite3*db){inti;intnTrans=0;/*Numberofdatabaseswithanactivewrite-transaction*/intrc=SQLITE_OK;**setthintneedXcommit=0;for(i=0;inDb;i++){Btree*pBt=db->aDb[i].pBt;if(pBt&&sqlite3BtreeIsInTrans(pBt)){needXcommit=1;if(i!=1)nTrans++;}}/*Ifthereareanywrite-transactionsatall,invokethecommithook*/if(needXcommit&&db->xCommitCallback){sqlite3SafetyOff(db);rc=db->xCommitCallback(db->pCommitArg);sqlite3SafetyOn(db);if(rc){returnSQLITE_CONSTRAINT;}}/*Thesimplecase-nomorethanonedatabasefile(notcountingthe**TEMPdatabase)hasatransactionactive.Thereisnoneedforthe**master-journal.****Ifthereturnvalueofsqlite3BtreeGetFilename()isazerolength**string,itmeansthemaindatabaseis:memory:.Inthatcasewedo**notsupportatomicmulti-filecommits,sousethesimplecasethen**too.*///简单的情况,只有一个数据库文件,不需要master-journalif(0==strlen(sqlite3BtreeGetFilename(db->aDb[0].pBt))||nTrans<=1){for(i=0;rc==SQLITE_OK&&inDb;i++){Btree*pBt=db->aDb[i].pBt;if(pBt){//同步btreerc=sqlite3BtreeSync(pBt,0);}}/*Dothecommitonlyifalldatabasessuccessfullysynced*///commite事务if(rc==SQLITE_OK){for(i=0;inDb;i++){Btree*pBt=db->aDb[i].pBt;if(pBt){sqlite3BtreeCommit(pBt);}}}}/*Thecomplexcase-Thereisamulti-filewrite-transactionactive.**Thisrequiresamasterjournalfiletoensurethetransactionis**committedatomicly.*/#ifndefSQLITE_OMIT_DISKIOelse{intneedSync=0;char*zMaster=0;/*File-nameforthemasterjournal*/charconst*zMainFile=sqlite3BtreeGetFilename(db->aDb[0].pBt);OsFile*master=0;/*Selectamasterjournalfilename*/do{u32random;sqliteFree(zMaster);sqlite3Randomness(sizeof(random),&random);zMaster=sqlite3MPrintf(\"%s-mj%08X\",zMainFile,random&0x7fffffff);if(!zMaster){returnSQLITE_NOMEM;}}while(sqlite3OsFileExists(zMaster));/*Openthemasterjournal.*/rc=sqlite3OsOpenExclusive(zMaster,&master,0);if(rc!=SQLITE_OK){sqliteFree(zMaster);returnrc;}/*Writethenameofeachdatabasefileinthetransactionintothenew**masterjournalfile.Ifanerroroccursatthispointclose**anddeletethemasterjournalfile.Alltheindividualjournalfiles**stillhave'null'asthemasterjournalpointer,sotheywillroll**backindependentlyifafailureoccurs.*/for(i=0;inDb;i++){Btree*pBt=db->aDb[i].pBt;if(i==1)continue;/*IgnoretheTEMPdatabase*/if(pBt&&sqlite3BtreeIsInTrans(pBt)){charconst*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);returnrc;}}}/*Syncthemasterjournalfile.Beforedoingthis,openthedirectory**themasterjournalfileisstoreinsothatitgetssyncedtoo.*/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);returnrc;}/*Syncallthedbfilesinvolvedinthetransaction.Thesamecall**setsthemasterjournalpointerineachindividualjournal.If**anerroroccurshere,donotdeletethemasterjournalfile.****Iftheerroroccursduringthefirstcalltosqlite3BtreeSync(),**thenthereisachancethatthemasterjournalfilewillbe**orphaned.Butwecannotdeleteit,incasethemasterjournal**filenamewaswrittenintothejournalfilebeforethefailure**occured.*/for(i=0;inDb;i++){Btree*pBt=db->aDb[i].pBt;if(pBt&&sqlite3BtreeIsInTrans(pBt)){rc=sqlite3BtreeSync(pBt,zMaster);if(rc!=SQLITE_OK){sqlite3OsClose(&master);sqliteFree(zMaster);returnrc;}}}sqlite3OsClose(&master);/*Deletethemasterjournalfile.Thiscommitsthetransaction.After**doingthisthedirectoryissyncedagainbeforeanyindividual**transactionfilesaredeleted.*/rc=sqlite3OsDelete(zMaster);assert(rc==SQLITE_OK);sqliteFree(zMaster);zMaster=0;rc=sqlite3OsSyncDirectory(zMainFile);if(rc!=SQLITE_OK){/*Thisisnotgood.Themasterjournalfilehasbeendeleted,but**thedirectorysyncfailed.Thereisnocompletelysafecourseof**actionfromhere.Theindividualjournalscontainthenameofthe**masterjournalfile,butthereisnowayofknowingifthat**masterjournalexistsnoworifitwillexistaftertheoperating**systemcrashthatmayfollowthefsync()failure.*/returnrc;}/*Allfilesanddirectorieshavealreadybeensynced,sothefollowing**callstosqlite3BtreeCommit()areonlyclosingfilesanddeleting**journals.Ifsomethinggoeswrongwhilethisishappeningwedon't**reallycare.Theintegrityofthetransactionisalreadyguaranteed,**butsomestray'cold'journalsmaybelyingaround.Returningan**errorcodewon'thelpmatters.*/for(i=0;inDb;i++){Btree*pBt=db->aDb[i].pBt;if(pBt){sqlite3BtreeCommit(pBt);}}}#endifreturnrc;}第五节PageCache之并发控制

本节主要谈谈SQLite的锁机制，SQLite是基于锁来实现并发控制的，所以本节的内容实际上是属于事务处理的，但是SQLite的锁机制实现非常的简单而巧妙，所以在这里单独讨论一下。如果真正理解了它，对整个事务的实现也就理解了。而要真正理解SQLite的锁机制，最好方法就是阅读SQLite的源码，所以在阅读本文时，最好能结合源码。SQLite的锁机制很巧妙，尽管在本节中的源码中，我写了很多注释，也是我个人在研究时的一点心得，但是我发现仅仅用言语，似乎不能把问题说清楚，只有通过体会，才能真正理解SQLite的锁机制。好了，下面进入正题。SQLite的并发控制机制是采用加锁的方式，实现非常简单，但也非常的巧妙，本节将对其进行一个详细的解剖。请仔细阅读下图，它可以帮助更好的理解下面的内容。1、RESERVEDLOCKRESERVED锁意味着进程将要对数据库进行写操作。某一时刻只能有一个RESERVEDLock，但是RESERVED锁和SHARED锁可以共存，而且可以对数据库加新的SHARED锁。为什么要用RESERVED锁？主要是出于并发性的考虑。由于SQLite只有库级排斥锁（EXCLUSIVELOCK），如果写事务一开始就上EXCLUSIVE锁，然后再进行实际的数据更新，写磁盘操作，这会使得并发性大大降低。而SQLite一旦得到数据库的RESERVED锁，就可以对缓存中的数据进行修改，而与此同时，其它进程可以继续进行读操作。直到真正需要写磁盘时才对数据库加EXCLUSIVE锁。2、PENDINGLOCKPENDINGLOCK意味着进程已经完成缓存中的数据修改，并想立即将更新写入磁盘。它将等待此时已经存在的读锁事务完成，但是不允许对数据库加新的SHAREDLOCK(这与RESERVEDLOCK相区别)。为什么要有PENDINGLOCK?主要是为了防止出现写饿死的情况。由于写事务先要获取RESERVEDLOCK，所以可能一直产生新的SHAREDLOCK，使得写事务发生饿死的情况。3、加锁机制的具体实现SQLite在pager层获取锁的函数如下：CodeWindows下具体的实现如下：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)。此时事务获得SHAREDLOCK。第二个阶段：语句序列：（3）此时事务获得RESERVEDLOCK。第三个阶段：事务执行修改操作。第四个阶段：语句序列：（1）——>（4）——>（5）状态变化为：RESERVED→RESERVED、PENDING(1)→PENDING（4）→EXCLUSIVE（5）。此时事务获得排斥锁，就可以进行写磁盘操作了。注：在上面的过程中，由于（1）的执行，使得某些时刻SQLite处于两种状态，但它持续的时间很短，从某种程度上来说可以忽略，但是为了把问题说清楚，在这里描述了这一微妙而巧妙的过程。4、SQLite的死锁问题SQLite的加锁机制会不会出现死锁?这是一个很有意思的问题，对于任何采取加锁作为并发控制机制的DBMS都得考虑这个问题。有两种方式处理死锁问题：（1）死锁预防(deadlockprevention)（2）死锁检测(deadlockdetection)与死锁恢复(deadlockrecovery)。SQLite采取了第一种方式，如果一个事务不能获取锁，它会重试有限次（这个重试次数可以由应用程序运行预先设置，默认为1次）——这实际上是基本锁超时的机制。如果还是不能获取锁，SQLite返回SQLITE_BUSY错误给应用程序，应用程序此时应该中断，之后再重试；或者中止当前事务。虽然基于锁超时的机制简单，容易实现，但是它的缺点也是明显的——资源浪费。5、事务类型(TransactionTypes)既然SQLite采取了这种机制，所以应用程序得处理SQLITE_BUSY错误，先来看一个会产生SQLITE_BUSY错误的例子：所以应用程序应该尽量避免产生死锁，那么应用程序如何做可以避免死锁的产生呢？答案就是为你的程序选择正确合适的事务类型。SQLite有三种不同的事务类型，这不同于锁的状态。事务可以从DEFERRED，IMMEDIATE或者EXCLUSIVE，一个事务的类型在BEGIN命令中指定：BEGIN[DEFERRED|IMMEDIATE|EXCLUSIVE]TRANSACTION；一个deferred事务不获取任何锁，直到它需要锁的时候，而且BEGIN语句本身也不会做什么事情——它开始于UNLOCK状态；默认情况下是这样的。如果仅仅用BEGIN开始一个事务，那么事务就是DEFERRED的，同时它不会获取任何锁，当对数据库进行第一次读操作时，它会获取SHAREDLOCK；同样，当进行第一次写操作时，它会获取RESERVEDLOCK。由BEGIN开始的Immediate事务会试着获取RESERVEDLOCK。如果成功，BEGINIMMEDIATE保证没有别的连接可以写数据库。但是，别的连接可以对数据库进行读操作，但是RESERVEDLOCK会阻止其它的连接BEGINIMMEDIATE或者BEGINEXCLUSIVE命令，SQLite会返回SQLITE_BUSY错误。这时你就可以对数据库进行修改操作，但是你不能提交，当你COMMIT时，会返回SQLITE_BUSY错误，这意味着还有其它的读事务没有完成，得等它们执行完后才能提交事务。Exclusive事务会试着获取对数据库的EXCLUSIVE锁。这与IMMEDIATE类似，但是一旦成功，EXCLUSIVE事务保证没有其它的连接，所以就可对数据库进行读写操作了。上面那个例子的问题在于两个连接最终都想写数据库，但是他们都没有放弃各自原来的锁，最终，shared锁导致了问题的出现。如果两个连接都以BEGINIMMEDIATE开始事务，那么死锁就不会发生。在这种情况下，在同一时刻只能有一个连接进入BEGINIMMEDIATE，其它的连接就得等待。BEGINIMMEDIATE和BEGINEXCLUSIVE通常被写事务使用。就像同步机制一样，它防止了死锁的产生。基本的准则是：如果你在使用的数据库没有其它的连接，用BEGIN就足够了。但是，如果你使用的数据库在其它的连接也要对数据库进行写操作，就得使用BEGINIMMEDIATE或BEGINEXCLUSIVE开始你的事务。第六节再谈SQLite的锁

SQLite封锁机制的实现需要底层文件系统的支持，不管是Linux，还是Windows，都提供了文件锁的机制，而这为SQLite提供了强大的支持。本节就来谈谈SQLite使用到的文件锁——主要基于Linux和Windows平台。Linux的文件锁Linux支持的文件锁技术主要包括建议锁（advisorylock）和强制锁（mandatorylock）这两种。此外，Linux中还引入了两种强制锁的变种形式：共享模式强制锁（share-modemandatorylock）和租借锁（lease）。在这里，主要讨论建议锁（advisorylock）。建议锁并不由内核强制实行，也就是说如果有进程不遵守“游戏规则”，不检查目标文件是否已经由别的进程加了锁就往其中写入数据，那么内核是不会加以阻拦的。因此，建议锁并不能阻止进程对文件的访问，而只能依靠各个进程在访问文件之前检查该文件是否已经被其他进程加锁来实现并发控制。进程需要事先对锁的状态做一个约定，并根据锁的当前状态和相互关系来确定其他进程是否能对文件执行指定的操作。而强制锁是由内核强制采用的文件锁——由于内核对每个read()和write()操作都会检查相应的锁，所以会降低系统性能。对于建议锁，Linux提供两种实现方式：锁文件（lockfiles）和记录锁（recordlocking）。(1)锁文件(lockfiles)锁文件是最简单的对文件加锁的方法，每个需要加锁的数据文件都有一个锁文件（lockfile）。当锁文件存在时，就认为该数据文件已经被加锁，别的进程不应该访问（但是你非要访问，Linux也不会阻止）。当锁不存在，进程就可以创建一个锁文件，然后访问相应的数据文件。只要创建锁的过程是原子的，就能保证某一时刻只有一个进程拥有该锁，这种方法保证某一时刻只有一个进程访问文件。这种想法很简单，当一个进程想访问文件时，可以按如下方式对文件加锁：fd=open(\"somefile.lck\",O_RDONLY,0644);if(fd>=0){close(fd);printf(\"thefileisalreadylocked\");return1;}else{/*thelockfiledoesnotexist,wecanlockitandaccessit*/fd=open(\"somefile.lck\",O_CREAT|O_WRONLY,0644\");if(fd<0){perror(\"errorcreatinglockfile\");return1;}/*wecouldwriteourpidtothefile*/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(\"thefileisalreadylocked\");return1;}elseif(fd<0){perror(\"unexpectederrorcheckinglock\");return1;}/*wecouldwriteourpidtothefile*/close(fd);O_EXCL标志保证open()创建锁文件的过程是原子性的。注意以下几点：1、任何时刻只有一个进程可以拥有锁。2、O_EXCL标志只对本志文件系统是可靠的，对于网络文件系统并不能很好的支持。3、锁仅仅只是建议性的。4、如果一个持有锁的进程不正常结束，锁文件仍然存在。如果加锁进程的pid存储在锁文件中，其它进程可以检查锁进程是否存在，当它结束时就可以删除锁。但是，在检查的时候，如果pid被其它进程使用了，此时就无能为力了。(2)记录锁（RecordLocking）为了克服锁文件的缺点，SystemV和BSD4.3引入了记录锁，相应的系统调用为lockf()和flock()。而POSIX对于记录锁提供了另外一种机制，其系统调用为fcntl()。Linux提供三种接口，在这里仅讨论POSIX的接口。记录锁和锁文件有两个很重要的区别：首先，记录锁可以对文件的任何一部分加锁——这对于DBMS这样的应用程序，有极大的帮助，SQLite当然没有放过这样的好处。其次，记录锁的另一个优点就是它由内核持有，而不是文件系统持有。当进程结束时，所有的锁也随之释放。和锁文件一样，POSIX锁也是建议性的。记录锁有两种锁：读锁（readlocks）和写锁（writelocks）。读锁也就是共享锁（sharedlock），写锁也就是排它锁（exclusivelock）。对于一个记录，只能有一个进程持有写锁，读锁不能存在。对于一个进程本身而言，多个锁绝不会冲突。如果一个进程对文件的200-250字节持有读锁，然后对200-225字节数据加写锁，是会成功的。此时，200-225为写锁，而226-250字节数据为读锁，该规则主要是防止进程本身发生死锁（尽管多进程之间仍然可能发生死锁）。POSIX锁通过fcntl()系统来实现，如下：#includeintfcntl(intfd,intcommand,longarg);arg为指向flock结构体的指针：#includestructflockshortshortoff_toff_tpid_t{l_type;l_whence;l_start;l_len;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中的锁都是强制锁(mandatorylocks)，Windows中的共享文件通过以下几个机制来管理：(1)通过共享访问控制方式，应用程序可以指定整个文件进行共享读，写或者删除。(2)通过字节范围锁（byterangelocks）可以对文件的某一部分进行读写访问。(3)Windows文件系统不允许正在执行的文件被打开用来进行写或删除操作。文件的共享方式由WIN32API中的打开文件函数CreateFile()中的sharingmode参数确定：HANDLECreateFile(LPCTSTRlpFileName,DWORDdwDesiredAccess,DWORDdwShareMode,LPSECURITY_ATTRIBUTESlpSecurityAttributes,DWORDdwCreationDisposition,DWORDdwFlagsAndAttributes,HANDLEhTemplateFile);dwShareMode的取值通常为：FILE_SHARE_DELETE：Enablessubsequentopenoperationsonanobjecttorequestdeleteaccess.Otherwise,otherprocessescannotopentheobjectiftheyrequestdeleteaccess.Ifthisflagisnotspecified,buttheobjecthasbeenopenedfordeleteaccess,thefunctionfails.FILE_SHARE_READ：Enablessubsequentopenoperationsonanobjecttorequestreadaccess.Otherwise,otherprocessescannotopentheobjectiftheyrequestreadaccess.Ifthisflagisnotspecified,buttheobjecthasbeenopenedforreadaccess,thefunctionfails.FILE_SHARE_WRITE：Enablessubsequentopenoperationsonanobjecttorequestwriteaccess.Otherwise,otherprocessescannotopentheobjectiftheyrequestwriteaccess.Ifthisflagisnotspecified,buttheobjecthasbeenopenedforwriteaccess,thefunctionfails.具体的实现函数：BOOLLockFile(HANDLEhFile,DWORDdwFileOffsetLow,DWORDdwFileOffsetHigh,DWORDnNumberOfBytesToLockLow,DWORDnNumberOfBytesToLockHigh);SQLite封锁机制的几个注意点SQLite的lockbyte的定义如下：CodePENDING_BYTE为何设置为0X40000000（1GB）？在Windows文件中，被加锁的区域不要求有数据，并且它会阻止所有的进程写文件的该区域,包括第一个持有该锁的进程.为了防止出现由于对含有mandatorylock的页面进行读写操作而出现错误(这在Windows中是不允许的),SQLite完全忽略包含pendingbyte的页面,所以pendingbyte在数据库文件上产生一个”文件洞”。PENDING_BYTE设置得那么高,则大部分数据库文件不会遇到由于PENDING_BYTE产生”文件洞”引起的空间损失(除非文件特别大,超过1GB)。(2)对于Windows来说，文件中加锁的区域不能重叠，为了使两个读进程可以同时访问文件，对于SHAREDLOCK选择一个SHARED_FIRST——SHARED_FIRST+SHARED_SIZE范围内的随机数，所以有可能两个进程取得一样的lockbyte，所以对于Windows，SQLite的并发性就受到限制。SQLite3C/C++开发接口简介（API函数）一

1.0总览

SQLite3是SQLite一个全新的版本,它虽然是在SQLite2.8.13的代码基础之上开发的,但是使用了和之前的版本不兼容的数据库格式和API.SQLite3是为了满足以下的需求而开发的:支持UTF-16编码.用户自定义的文本排序方法.可以对BLOBs字段建立索引.因此为了支持这些特性我改变了数据库的格式,建立了一个与之前版本不兼容的3.0版.至于其他的兼容性的改变,例如全新的API等等,都将在理论介绍之后向你说明,这样可以使你最快的一次性摆脱兼容性问题.3.0版的和2.X版的API非常相似,但是有一些重要的改变需要注意.所有API接口函数和数据结构的前缀都由\"sqlite_\"改为了\"sqlite3_\".这是为了避免同时使用SQLite2.X和SQLite3.0这两个版本的时候发生链接冲突.由于对于C语言应该用什么数据类型来存放UTF-16编码的字符串并没有一致的规范.因此SQLite使用了普通的void*类型来指向UTF-16编码的字符串.客户端使用过程中可以把void*映射成适合他们的系统的任何数据类型.2.0C/C++接口

SQLite3.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如何打开关闭数据库typedefstructsqlite3sqlite3;intsqlite3_open(constchar*,sqlite3**);intsqlite3_open16(constvoid*,sqlite3**);intsqlite3_close(sqlite3*);constchar*sqlite3_errmsg(sqlite3*);constvoid*sqlite3_errmsg16(sqlite3*);intsqlite3_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的编码返回,并且在下一次调用任何SQLiteAPI函数的时候被清除.sqlite3_errmsg16()和sqlite3_errmsg()大体上相同,除了返回的错误信息将以UTF-16本机字节顺序编码.SQLite3的错误代码相比SQLite2没有任何的改变,它们分别是:#defineSQLITE_OK0/*Successfulresult*/#defineSQLITE_ERROR1/*SQLerrorormissingdatabase*/#defineSQLITE_INTERNAL2/*AninternallogicerrorinSQLite*/#defineSQLITE_PERM3/*Accesspermissiondenied*/#defineSQLITE_ABORT4/*Callbackroutinerequestedanabort*/#defineSQLITE_BUSY5/*Thedatabasefileislocked*/#defineSQLITE_LOCKED6/*Atableinthedatabaseislocked*/#defineSQLITE_NOMEM7/*Amalloc()failed*/#defineSQLITE_READONLY8/*Attempttowriteareadonlydatabase*/#defineSQLITE_INTERRUPT9/*Operationterminatedbysqlite_interrupt()*/#defineSQLITE_IOERR10/*SomekindofdiskI/Oerroroccurred*/#defineSQLITE_CORRUPT11/*Thedatabasediskimageismalformed*/#defineSQLITE_NOTFOUND12/*(InternalOnly)Tableorrecordnotfound*/#defineSQLITE_FULL13/*Insertionfailedbecausedatabaseisfull*/#defineSQLITE_CANTOPEN14/*Unabletoopenthedatabasefile*/#defineSQLITE_PROTOCOL15/*Databaselockprotocolerror*/#defineSQLITE_EMPTY16/*(InternalOnly)Databasetableisempty*/#defineSQLITE_SCHEMA17/*Thedatabaseschemachanged*/#defineSQLITE_TOOBIG18/*Toomuchdataforonerowofatable*/#defineSQLITE_CONSTRAINT19/*Abortduetocontraintviolation*/#defineSQLITE_MISMATCH20/*Datatypemismatch*/#defineSQLITE_MISUSE21/*Libraryusedincorrectly*/#defineSQLITE_NOLFS22/*UsesOSfeaturesnotsupportedonhost*/#defineSQLITE_AUTH#defineSQLITE_ROW#defineSQLITE_DONE23/*Authorizationdenied*/100/*sqlite_step()hasanotherrowready*/101/*sqlite_step()hasfinishedexecuting*/SQLite3C/C++开发接口简介（API函数）二

2.2执行SQL语句typedefint(*sqlite_callback)(void*,int,char**,char**);intsqlite3_exec(sqlite3*,constchar*sql,sqlite_callback,void*,char**);sqlite3_exec函数依然像它在SQLite2中一样承担着很多的工作.该函数的第二个参数中可以编译和执行零个或多个SQL语句.查询的结果返回给回调函数.更多地信息可以查看API参考.在SQLite3里,sqlite3_exec一般是被准备SQL语句接口封装起来使用的.typedefstructsqlite3_stmtsqlite3_stmt;intsqlite3_prepare(sqlite3*,constchar*,int,sqlite3_stmt**,constchar**);intsqlite3_prepare16(sqlite3*,constvoid*,int,sqlite3_stmt**,constvoid**);intsqlite3_finalize(sqlite3_stmt*);intsqlite3_reset(sqlite3_stmt*);sqlite3_prepare接口把一条SQL语句编译成字节码留给后面的执行函数.使用该接口访问数据库是当前比较好的的一种方法.sqlite3_prepare()处理的SQL语句应该是UTF-8编码的.而sqlite3_prepare16()则要求是UTF-16编码的.输入的参数中只有第一个SQL语句会被编译.第四个参数则用来指向输入参数中下一个需要编译的SQL语句存放的SQLitestatement对象的指针,任何时候如果调用sqlite3_finalize()将销毁一个准备好的SQL声明.在数据库关闭之前，所有准备好的声明都必须被释放销毁.sqlite3_reset()函数用来重置一个SQL声明的状态，使得它可以被再次执行.SQL声明可以包含一些型如\"?\"或\"?nnn\"或\":aaa\"的标记，其中\"nnn\"是一个整数，\"aaa\"是一个字符串.这些标记代表一些不确定的字符值（或者说是通配符），可以在后面用sqlite3_bind接口来填充这些值.每一个通配符都被分配了一个编号（由它在SQL声明中的位置决定，从1开始），此外也可以用\"nnn\"来表示\"?nnn\"这种情况.允许相同的通配符在同一个SQL声明中出现多次,在这种情况下所有相同的通配符都会被替换成相同的值.没有被绑定的通配符将自动取NULL值.intsqlite3_bind_blob(sqlite3_stmt*,int,constvoid*,intn,void(*)(void*));intsqlite3_bind_double(sqlite3_stmt*,int,double);intsqlite3_bind_int(sqlite3_stmt*,int,int);intsqlite3_bind_int64(sqlite3_stmt*,int,longlongint);intsqlite3_bind_null(sqlite3_stmt*,int);intsqlite3_bind_text(sqlite3_stmt*,int,constchar*,intn,void(*)(void*));intsqlite3_bind_text16(sqlite3_stmt*,int,constvoid*,intn,void(*)(void*));intsqlite3_bind_value(sqlite3_stmt*,int,constsqlite3_value*);以上是sqlite3_bind所包含的全部接口，它们是用来给SQL声明中的通配符赋值的.没有绑定的通配符则被认为是空值.绑定上的值不会被sqlite3_reset()函数重置.但是在调用了sqlite3_reset()之后所有的通配符都可以被重新赋值.在SQL声明准备好之后(其中绑定的步骤是可选的),需要调用以下的方法来执行:intsqlite3_step(sqlite3_stmt*);如果SQL返回了一个单行结果集，sqlite3_step()函数将返回SQLITE_ROW,如果SQL语句执行成功或者正常将返回SQLITE_DONE,否则将返回错误代码.如果不能打开数据库文件则会返回SQLITE_BUSY.如果函数的返回值是SQLITE_ROW,那么下边的这些方法可以用来获得记录集行中的数据:constvoid*sqlite3_column_blob(sqlite3_stmt*,intiCol);intsqlite3_column_bytes(sqlite3_stmt*,intiCol);intsqlite3_column_bytes16(sqlite3_stmt*,intiCol);intsqlite3_column_count(sqlite3_stmt*);constchar*sqlite3_column_decltype(sqlite3_stmt*,intiCol);constvoid*sqlite3_column_decltype16(sqlite3_stmt*,intiCol);doublesqlite3_column_double(sqlite3_stmt*,intiCol);intsqlite3_column_int(sqlite3_stmt*,intiCol);longlongintsqlite3_column_int64(sqlite3_stmt*,intiCol);constchar*sqlite3_column_name(sqlite3_stmt*,intiCol);constvoid*sqlite3_column_name16(sqlite3_stmt*,intiCol);constunsignedchar*sqlite3_column_text(sqlite3_stmt*,intiCol);constvoid*sqlite3_column_text16(sqlite3_stmt*,intiCol);intsqlite3_column_type(sqlite3_stmt*,intiCol);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列的值的数据类型.具体的返回值如下:#defineSQLITE_INTEGER1#defineSQLITE_FLOAT2#defineSQLITE_TEXT3#defineSQLITE_BLOB4#defineSQLITE_NULL5sqlite3_column_decltype()则用来返回该列在CREATETABLE语句中声明的类型.它可以用在当返回类型是空字符串的时候.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()接口返回的数据类型来获取数据.数据类型不同时软件将自动转换.SQLite3C/C++开发接口简介（API函数）三

2.3用户自定义函数可以使用以下的方法来创建用户自定义的SQL函数:typedefstructsqlite3_valuesqlite3_value;intsqlite3_create_function(sqlite3*,constchar*zFunctionName,intnArg,inteTextRep,void*,void(*xFunc)(sqlite3_context*,int,sqlite3_value**),void(*xStep)(sqlite3_context*,int,sqlite3_value**),void(*xFinal)(sqlite3_context*));intsqlite3_create_function16(sqlite3*,constvoid*zFunctionName,intnArg,inteTextRep,void*,void(*xFunc)(sqlite3_context*,int,sqlite3_value**),void(*xStep)(sqlite3_context*,int,sqlite3_value**),void(*xFinal)(sqlite3_context*));#defineSQLITE_UTF81#defineSQLITE_UTF162#defineSQLITE_UTF16BE3#defineSQLITE_UTF16LE4#defineSQLITE_ANY5nArg参数用来表明自定义函数的参数个数.如果参数值为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结构体指针替代了SQLiteversion2.X中的字符串指针.下面的函数用来从sqlite3_value结构体中提取数据:constvoid*sqlite3_value_blob(sqlite3_value*);intsqlite3_value_bytes(sqlite3_value*);intsqlite3_value_bytes16(sqlite3_value*);doublesqlite3_value_double(sqlite3_value*);intsqlite3_value_int(sqlite3_value*);longlongintsqlite3_value_int64(sqlite3_value*);constunsignedchar*sqlite3_value_text(sqlite3_value*);constvoid*sqlite3_value_text16(sqlite3_value*);intsqlite3_value_type(sqlite3_value*);上面的函数调用以下的API来获得上下文内容和返回结果:void*sqlite3_aggregate_context(sqlite3_context*,intnbyte);void*sqlite3_user_data(sqlite3_context*);voidsqlite3_result_blob(sqlite3_context*,constvoid*,intn,void(*)(void*));voidsqlite3_result_double(sqlite3_context*,double);voidsqlite3_result_error(sqlite3_context*,constchar*,int);voidsqlite3_result_error16(sqlite3_context*,constvoid*,int);voidsqlite3_result_int(sqlite3_context*,int);voidsqlite3_result_int64(sqlite3_context*,longlongint);voidsqlite3_result_null(sqlite3_context*);voidsqlite3_result_text(sqlite3_context*,constchar*,intn,void(*)(void*));voidsqlite3_result_text16(sqlite3_context*,constvoid*,intn,void(*)(void*));voidsqlite3_result_value(sqlite3_context*,sqlite3_value*);void*sqlite3_get_auxdata(sqlite3_context*,int);voidsqlite3_set_auxdata(sqlite3_context*,int,void*,void(*)(void*));2.4用户自定义排序规则下面的函数用来实现用户自定义的排序规则:sqlite3_create_collation(sqlite3*,constchar*zName,inteTextRep,void*,int(*xCompare)(void*,int,constvoid*,int,constvoid*));sqlite3_create_collation16(sqlite3*,constvoid*zName,inteTextRep,void*,int(*xCompare)(void*,int,constvoid*,int,constvoid*));sqlite3_collation_needed(sqlite3*,void*,void(*)(void*,sqlite3*,inteTextRep,constchar*));sqlite3_collation_needed16(sqlite3*,void*,void(*)(void*,sqlite3*,inteTextRep,constvoid*));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。这个包加密实现调用了windowsAPI的加密函数，所以只能在windows中使用。这个可加密的版本是在一个ADO.NET2.0SQLiteDataProvider的基础上改过来的（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;chartemp[256],FileRoot[256];charbuffer2[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,\"CREATETABLElist(flienamevarchar(128)UNIQUE,fziptext);\0,0,&zErrMsg);sqlite3_prepare(db,\"insertintolistvalues('中文GB2312编码',?);\-1,&stat,0);strcpy(temp,\"测试数据UTF-8的支持情况\");intlen=(int)strlen(temp);sqlite3_bind_text(stat,1,temp,len,NULL);sqlite3_step(stat);sqlite3_prepare(db,\"select*fromlist;\-1,&stat,0);sqlite3_step(stat);constunsignedchar*test=sqlite3_column_text(stat,1);intsize=sqlite3_column_bytes(stat,1);printf(\"%sest);sqlite3_finalize(stat);//sqlite3_rekey(db,\"\sqlite3_close(db);具体的源代码如下：SQLite3.3.7加密版源代码（VC2003）SQLite3.3.7加密版源代码（VC2005）例子1，例子2SQLite的类型亲和性分析

一、类型亲和性介绍SQLite不强制数据类型约束。任何数据都可以插入任何列。你可以向一个整型列中插入任意长度的字符串，向布尔型列中插入浮点数，或者向字符型列中插入日期型值。在CreateTABLE中所指定的数据类型不会限制在该列中插入任何数据。任何列均可接受任意长度的字符串（只有一种情况除外：标志为INTEGERPRIMARYKEY的列只能存储64位整数，当向这种列中插数据除整数以外的数据时，将会产生错误。）但SQLite确实使用声明的列类型来指示你所期望的格式。所以，例如你向一个整型列中插入字符串时，SQLite会试图将该字符串转换成一个整数。如果可以转换，它将插入该整数；否则，将插入字符串。这是一个特性，而不是一个bug。这种特性被称为类型或列亲和性(typeorcolumnaffinity).二、类型亲和性总结(优点)：1提高和其它DBMS的兼容性，让用户就像是在用一般的DBMS一样而使用它，提高了容错能力。2SQLite支持的数据类型只有五种，而其它的大型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=expr1ORexpr2=xORx=expr3此时由于对于OR，在SQLite中不能利用索引来优化，所以可以将它转换成带有IN操作符的子句：xIN(expr1,expr2,expr3)这样就可以用索引进行优化，效果很明显，但是如果在都没有索引的情况下OR语句执行效率会稍优于IN语句的效率。2．如果一个子句的操作符是BETWEEN，在SQLite中同样不能用索引进行优化，所以也要进行相应的等价转换：如：aBETWEENbANDc可以转换成：(aBETWEENbANDc)AND(a>=b)AND(a<=c)。在上面这个子句中，(a>=b)AND(a<=c)将被设为dynamic且是(aBETWEENbANDc)的子句，那么如果BETWEEN语句已经编码，那么子句就忽略不计，如果存在可利用的index使得子句已经满足条件，那么父句则被忽略。3．如果一个单元的操作符是LIKE，那么将做下面的转换：xLIKE‘abc%’，转换成：x>=‘abc’ANDx<‘abd’。因为在SQLite中的LIKE是不能用索引进行优化的，所以如果存在索引的话，则转换后和不转换相差很远，因为对LIKE不起作用，但如果不存在索引，那么LIKE在效率方面也还是比不上转换后的效率的。三、几种查询语句的处理（复合查询）1．查询语句为：ORDERBYORDERBY执行方法：isoneofUNIONALL,UNION,EXCEPT,orINTERSECT.这个语句的执行过程是先将selectA和selectB执行并且排序，再对两个结果扫描处理，对上面四种操作是不同的，将执行过程分成七个子过程：outA:将selectA的结果的一行放到最终结果集中outB:将selectA的结果的一行放到最终结果集中(只有UNION操作和UNIONALL操作，其它操作都不放入最终结果集中)AltB:当selectA的当前记录小于selectB的当前记录AeqB:当selectA的当前记录等于selectB的当前记录AgtB:当selectA的当前记录大于selectB的当前记录EofA:当selectA的结果遍历完EofB:当selectB的结果遍历完下面就是四种操作的执行过程：执行顺序AltB:AeqB:AgtB:EofA:EofB:UNIONALLoutA,nextAoutA,nextAoutB,nextBoutB,nextBoutA,nextAUNIONoutA,nextAnextAoutB,nextBoutB,nextBoutA,nextAEXCEPToutA,nextAnextAnextBhaltoutA,nextAINTERSECTnextAoutA,nextAnextBhalthalt2．如果可能的话，可以把一个用到GROUPBY查询的语句转换成DISTINCT语句来查询，因为GROUPBY有时候可能会用到index，而对于DISTINCT都不会用到索引的。四、子查询扁平化例子：SELECTaFROM(SELECTx+yASaFROMt1WHEREz<100)WHEREa>5对这个SQL语句的执行一般默认的方法就是先执行内查询，把结果放到一个临时表中，再对这个表进行外部查询，这就要对数据处理两次，另外这个临时表没有索引，所以对外部查询就不能进行优化了，如果对上面的SQL进行处理后可以得到如下SQL语句：SELECTx+yASaFROMt1WHEREz<100ANDa>5，这个结果显然和上面的一样，但此时只需要对数据进行查询一次就够了，另外如果在表t1上有索引的话就避免了遍历整个表。运用flatten方法优化SQL的条件：1.子查询和外查询没有都用集函数2.子查询没有用集函数或者外查询不是个表的连接3.子查询不是一个左外连接的右操作数4.子查询没有用DISTINCT或者外查询不是个表的连接5.子查询没有用DISTINCT或者外查询没有用集函数6.子查询没有用集函数或者外查询没有用关键字DISTINCT7.子查询有一个FROM语句8.子查询没有用LIMIT或者外查询不是表的连接9.子查询没有用LIMIT或者外查询没有用集函数10.子查询没有用集函数或者外查询没用LIMIT11.子查询和外查询不是同时是ORDERBY子句12.子查询和外查询没有都用LIMIT13.子查询没有用OFFSET14.外查询不是一个复合查询的一部分或者子查询没有同时用关键字ORDERBY和LIMIT15.外查询没有用集函数子查询不包含ORDERBY16.复合子查询的扁平化：子查询不是一个复合查询，或者他是一个UNIONALL复合查询，但他是都由若干个非集函数的查询构成，他的父查询不是一个复合查询的子查询，也没有用集函数或者是DISTINCT查询，并且在FROM语句中没有其它的表或者子查询，父查询和子查询可能会包含WHERE语句，这些都会受到上面11、12、13条件的限制。例：SELECTa+1FROM(SELECTxFROMtabUNIONALLSELECTyFROMtabUNIONALLSELECTabs(z*2)FROMtab2)WHEREa!=5ORDERBY1转换为：SELECTx+1FROMtabWHEREx+1!=5UNIONALLSELECTy+1FROMtabWHEREy+1!=5UNIONALLSELECTabs(z*2)+1FROMtab2WHEREabs(z*2)+1!=5ORDERBY117.如果子查询是一个复合查询，那么父查询的所有的ORDERBY语句必须是对子查询的列的简单引用18.子查询没有用LIMIT或者外查询不具有WHERE语句子查询扁平化是由专门一个函数实现的，函数为：staticintflattenSubquery(Parse*pParse,/*Parsingcontext*/Select*p,/*TheparentorouterSELECTstatement*/intiFrom,/*Indexinp->pSrc->a[]oftheinnersubquery*/intisAgg,/*TrueifouterSELECTusesaggregatefunctions*/intsubqueryIsAgg/*Trueifthesubqueryusesaggregatefunctions*/)它是在Select.c文件中实现的。显然对于一个比较复杂的查询，如果满足上面的条件时对这个查询语句进行扁平化处理后就可以实现对查询的优化。如果正好存在索引的话效果会更好！五、连接查询在返回查询结果之前，相关表的每行必须都已经连接起来，在SQLite中，这是用嵌套循环实现的，在早期版本中，最左边的是最外层循环，最右边的是最内层循环，连接两个或者更多的表时，如果有索引则放到内层循环中，也就是放到FROM最后面，因为对于前面选中的每行，找后面与之对应的行时，如果有索引则会很快，如果没有则要遍历整个表，这样效率就很低，但在新版本中，这个优化已经实现。优化的方法如下：对要查询的每个表，统计这个表上的索引信息，首先将代价赋值为SQLITE_BIG_DBL（一个系统已经定义的常量）：1)如果没有索引，则找有没有在这个表上对rowid的查询条件：1．如果有Rowid=EXPR，如果有的话则返回对这个表代价估计，代价计为零，查询得到的记录数为1，并完成对这个表的代价估计，2．如果没有Rowid=EXPR但有rowidIN(...)，而IN是一个列表，那么记录返回记录数为IN列表中元素的个数，估计代价为NlogN,3．如果IN不是一个列表而是一个子查询结果，那么由于具体这个子查询不能确定，所以只能估计一个值，返回记录数为100，代价为200。4．如果对rowid是范围的查询，那么就估计所有符合条件的记录是总记录的三分之一，总记录估计为1000000，并且估计代价也为记录数。5．如果这个查询还要求排序，则再另外加上排序的代价NlogN6．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。2)如果WHERE子句中存在OR操作符，那么要把这些OR连接的所有子句分开再进行分析。1．如果有子句是由AND连接符构成，那么再把由AND连接的子句再分别分析。2．如果连接的子句的形式是X，那么就再分析这个子句。3．接下来就是把整个对OR操作的总代价计算出来。4．如果这个查询要求排序，则再在上面总代价上再乘上排序代价NlogN5．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。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．同样，如果此查询要求排序的话，再在上面的总代价上加上NlogN7．如果此时得到的代价小于总代价，那么就更新总代价，否则不更新。4)通过上面的优化过程，可以得到对一个表查询的总代价（就是上面各个代价的总和），再对第二个表进行同样的操作，这样如此直到把FROM子句中所有的表都计算出各自的代价，最后取最小的，这将作为嵌套循环的最内层，依次可以得到整个嵌套循环的嵌套顺序，此时正是最优的，达到了优化的目的。5)所以循环的嵌套顺序不一定是与FROM子句中的顺序一致，因为在执行过程中会用索引优化来重新排列顺序。六、索引在SQLite中，有以下几种索引：1)单列索引2)多列索引3)唯一性索引4)对于声明为：INTEGERPRIMARYKEY的主键来说，这列会按默认方式排序，所以虽然在数据字典中没有对它生成索引，但它的功能就像个索引。所以如果在这个主键上在单独建立索引的话，这样既浪费空间也没有任何好处。运用索引的注意事项：1)对于一个很小的表来说没必要建立索引2)在一个表上如果经常做的是插入更新操作，那么就要节制使用索引3)也不要在一个表上建立太多的索引，如果建立太多的话那么在查询的时候SQLite可能不会选择最好的来执行查询，一个解决办法就是建立聚蔟索引索引的运用时机：1)操作符：=、>、<、IN等2)操作符BETWEEN、LIKE、OR不能用索引，如BETWEEN：SELECT*FROMmytableWHEREmyfieldBETWEEN10and20;这时就应该将其转换成：SELECT*FROMmytableWHEREmyfield>=10ANDmyfield<=20;此时如果在myfield上有索引的话就可以用了，大大提高速度再如LIKE：SELECT*FROMmytableWHEREmyfieldLIKE'sql%';此时应该将它转换成：SELECT*FROMmytableWHEREmyfield>='sql'ANDmyfield<'sqm';此时如果在myfield上有索引的话就可以用了，大大提高速度再如OR：SELECT*FROMmytableWHEREmyfield='abc'ORmyfield='xyz';此时应该将它转换成：SELECT*FROMmytableWHEREmyfieldIN('abc','xyz');此时如果在myfield上有索引的话就可以用了，大大提高速度3)有些时候索引都是不能用的，这时就应该遍历全表（程序演示）SELECT*FROMmytableWHEREmyfield%2=1;SELECT*FROMmytableWHEREsubstr(myfield,0,1)='w';SELECT*FROMmytableWHERElength(myfield)<5;SQLite查询优化的例子

上次讲到了SQLite的查询优化代码中的具体实现，现在来看一下它的几个实例：1#include\"stdio.h\"2#include\"sqlite3.h\"3#include4voidquery(sqlite3*db,sqlite3_stmt*stmt,char*sql);56intmain(intargc,char**argv)7{8sqlite3*db;9char*zErr;10intrc;11char*sql;12sqlite3_stmt*stmt=0;13rc=sqlite3_open(\"memory.db\&db);14if(rc){15fprintf(stderr,\"Can'topendatabase:%sn\sqlite3_errmsg(db));16sqlite3_close(db);17}1819//下面是所建的各个表的结构20sql=\"CREATETABLEt1(numint,wordTEXTNOTNULL)\";21//sql=\"CREATETABLEt4(numINTEGERNOTNULL,wordTEXTNOTNULL)\";22//sql=\"CREATETABLEt3(numINTEGERNOTNULL,wordTEXTNOTNULL)\";23rc=sqlite3_exec(db,sql,NULL,NULL,&zErr);24if(rc!=SQLITE_OK){25if(zErr!=NULL){26fprintf(stderr,\"SQLerror:%sn\zErr);27sqlite3_free(zErr);28}29}3031//下面是对所以插入进行手动提交，这样可以加快插入速度32//sqlite3_exec(db,\"BEGIN\33//插入1000000条记录34//for(inti=0;i<1000000;i++)35//{36//sql=sqlite3_mprintf(\"insertintot1values(%d,'%s')\37//rc=sqlite3_exec(db,sql,NULL,NULL,&zErr);38//}39//sqlite3_exec(db,\"COMMIT\4041sql=\"createindext1nwindexont1(num)\";42rc=sqlite3_exec(db,sql,NULL,NULL,&zErr);43if(rc!=SQLITE_OK){44if(zErr!=NULL){45fprintf(stderr,\"SQLerror:%sn\zErr);46sqlite3_free(zErr);47}48}4950//sql=\"dropindext1nwindex\";51//sql=\"dropindext3index\";52//sql=\"deletefromt2\";53rc=sqlite3_exec(db,sql,NULL,NULL,&zErr);54if(rc!=SQLITE_OK){55if(zErr!=NULL){56fprintf(stderr,\"SQLerror:%sn\zErr);57sqlite3_free(zErr);58}59}6061printf(\"查询结果是：n\");62//sql=\"select*fromt1wherenum=3000ornum=2000\";//有INTEGERPRIMARYKEY，快63//sql=\"select*fromt2wherenum=3000ornum=2000\";//没有索引，慢64//sql=\"select*fromt3wherenum=3000ornum=2000\";//有索引，快6566//这里交换位置了，但是结果用的时间想差比较大的原因是，t1是用索引存储的，但是它不是由createindex67//而创建的，所以系统还不会把它作为索引处理，所以这两个表就只是无索引的表，在内部优化计算代价只是对它68//进行估计，因为源代码中没有捕获到下面的查询条件，所以都是系统最大值（源代码中有），所以就嵌套顺序没69//变，所以出现下面的差异。70//sql=\"SELECTcount(*)FROMt3,t1WHEREt1.num=t3.num\";//比下面的快，由于内层少71//sql=\"SELECTcount(*)FROMt1,t3WHEREt1.num=t3.num\";//比上面的慢，由于内层多7273//下面这个已经内部实现优化，所以所用时间是相同的74//sql=\"SELECT*FROMt2,t3WHEREt2.num=t3.num\";//有索引，稍快75//sql=\"SELECT*FROMt3,t2WHEREt2.num=t3.num\";//同上，内部已经优化7677//sql=\"select*fromt3wherenum=8000\";//有索引，快78//sql=\"select*fromt1wherenum%2=0\";//有索引,但不能用，很慢79//sql=\"select*fromt1wherenum=8000\";//没有索引，慢8081//BETWEEN的转换优化---内部已经实现优化,如果有索引的话快一点82//sql=\"selectcount(*)fromt2wherewordbetween'goodl'and'goodm'\";//BETWEEN83//sql=\"selectcount(*)fromt2whereword>='goodl'andword<'goodm'\";//BETWEEN的转换8485//LIKE的转换优化---内部已经实现优化86//sql=\"selectcount(*)fromt2wherewordlike'goodl%'\";//有索引不起作用87//sql=\"selectcount(*)fromt2whereword>='goodl'andword<'goodm'\";//如果有索引会更快8889//IN的转换优化---内部没有实现优化,但此时如果可以用索引的话就会很好90//如果不用索引则在这里体现不出IN比OR优，而如果有索引则差别很明显91//sql=\"selectcount(*)fromt2wherewordin('goodllll','goodkkkk','goodaaaa')\";92//sql=\"selectcount(*)fromt2whereword='goodllll'orword='goodkkkk'orword='goodaaaa'\";9394intstart=GetTickCount();95query(db,stmt,sql);96printf(\"thetimehaspass:%dmsn\97sqlite3_close(db);98return0;99}100101voidquery(sqlite3*db,sqlite3_stmt*stmt,char*sql){102intrc,ncols,i;103constchar*tail;104rc=sqlite3_prepare(db,sql,-1,&stmt,&tail);105if(rc!=SQLITE_OK){106fprintf(stderr,\"SQLerror:%sn\sqlite3_errmsg(db));107}108rc=sqlite3_step(stmt);109ncols=sqlite3_column_count(stmt);110while(rc==SQLITE_ROW){111for(i=0;i收集SQLite与SqlServer的语法差异1.返回最后插入的标识值返回最后插入的标识值sqlserver用@@IDENTITYsqlite用标量函数LAST_INSERT_ROWID()返回通过当前的SQLConnection插入到数据库的最后一行的行标识符（生成的主键）。此值与SQLConnection.lastInsertRowID属性返回的值相同。2.topn在sqlserver中返回前2行可以这样：selecttop2*fromaaorderbyidsdescsqlite中用LIMIT，语句如下：select*fromaaorderbyidsdescLIMIT23.GETDATE()在sqlserver中GETDATE()返回当前系统日期和时间sqlite中没有4.EXISTS语句sqlserver中判断插入（不存在ids=5的就插入）IFNOTEXISTS(select*fromaawhereids=5)BEGINinsertintoaa(nickname)select't'END在sqlite中可以这样insertintoaa(nickname)select't'wherenotexists(select*fromaawhereids=5)5.嵌套事务sqlite仅允许单个活动的事务6.RIGHT和FULLOUTERJOINsqlite不支持RIGHTOUTERJOIN或FULLOUTERJOIN7.可更新的视图sqlite视图是只读的。不能对视图执行DELETE、INSERT或UPDATE语句，sqlserver是可以对视图DELETE、INSERT或UPDATEsqlite查询数据库中存在的所有表FromwithinaC/C++program(orascriptusingTcl/Ruby/Perl/Pythonbindings)youcangetaccesstotableandindexnamesbydoingaSELECTonaspecialtablenamed\"SQLITE_MASTER\".EverySQLitedatabasehasanSQLITE_MASTERtablethatdefinestheschemaforthedatabase.SQLcodeSELECTnameFROMsqlite_masterWHEREtype='table'ORDERBYname;