SQLite设计与概念

SQLite设计与概念

了解了SQLite设计与概念有助于了解SQLite API和一些SQLite架构和实现方面的内容，掌握之后可以更好的编写代码、使得代码运行速度变快、避免死锁等。出来了解API做什么的之外，还需要从整体上了解API,从事务的角度来了解他们是如何运作的。
SQLite数据库的所有操作都是在事务上下文完成的，因此需要了解API背后的东西，从锁的角度理解事务是如何工作的。

一、API

从功能上讲API可分为两部分：核心API和扩展API,核心API用于基本的数据库操作，如：连接数据库、处理SQL语句、迭代查询结果等。扩展API提供tong’g创建用户自定义的SQL扩展来扩展SQLite的不同方式。

1. 主要数据结构：API、事务、锁

连接和语句：API中与查询处理有关的两个基本数据结构是连接和语句。连接对象和语句对象分别对应着sqlite3和sqlite_stmt句柄，他们用来执行查询，API中的主要操作都在使用这两个结构。一个连接对象代表到数据库的连接和事务的上下文，statement来自连接对象，每个statement都有一个关联的连接对象。一个statement代表了一个编译的SQL语句。statement可以理解为便利B-Tree的游标或者调用SQL命令的不透明句柄。连接始终在事务中运行，一个连接对象一次只能有一个打开事务。

B-Tree和Pager：一个连接对象可以连接到多个数据库（一个主数据库和多个附加数据库），每个数据库对象都有一个B-Tree和一个Pager对象。statement使用他们连接对象的B-Tree和一个Pager来读写数据。读取数据库的statement使用游标来遍历B-Tree。游标遍历记录，记录存储在页面内，当一个游标遍历记录时，他也在访问页面。若一个游标想要访问页面，必须先从内存加载到磁盘上，这是pager的工作，当B-Tree需要数据库中的一个特定页面时候，它就命令pager从磁盘中读取该页面。然后pager将页面加载到页面缓存中去，页面缓存是内存缓存，一旦页面在页面缓存，B-Tree就可以和它的游标获取到该页面中的记录。如果游标要修改页面，那么pager会保存原始页面，以确保发生事务可以回滚到原来状态。pager负责读写数据库，维护内存缓存和页面，管理事务以及管理锁和故障恢复。

2. 核心API
核心API是用来执行SQL命令的，它由用来执行查询和管理数据库的函数组成，有两个执行SQL的方法，准备查询和封装查询。在API和sqlite内部，准备查询有三个阶段：准备、执行、和完成三个阶段。每一个阶段都有一个单独的函数。与执行阶段对应的是用来从结果集中获取记录和列信息的函数。

1.连接数据库：连接磁盘数据库时，如果数据库存在则SQLite将打开该数据库，如果不存在则创建这个数据库，在这种情况下不会立即创建操作系统文件，只有当进行添加内容-创建表、索引等对象时，才会真正创建数据库文件，否则在退出时就会删除。

函数：int sqlite3_open(char *path,sqlite3 **db)；
功能: 打开数据库
参数：path： 数据库文件路径
	  db： 指向sqlite句柄的指针
返回值：成功返回0，失败返回错误码(非零值)

2.执行预查询：预查询是SQLite执行SQL语句的实际处理方法，执行一条SQL语句需要以下三步:准备、执行、完成。

准备：语法分析器、词法分析器以及代码生成器将命令编译成VDBE字节码，以准备SQL语句

	函数：int sqlite3_prepare(sqlite3 *db, const char *zSql, int nByte, sqlite3_stmt **ppStmt, const char **pzTail);
			  sqlite3_prepare_v2
	功能：这个函数将sql文本转换成一个准备语句（prepared statement）对象，同时返回这个对象的指针。
			  这个接口需要一个数据库连接指针以及一个要准备的包含SQL语句的文本。它实际上并不执行（evaluate）这个SQL语句，它仅仅为执行准备这个sql语句.
	参数：db：数据指针
		 zSql：sql语句，使用UTF-8编码
		 nByte：如果nByte小于0，则函数取出zSql中从开始到第一个0终止符的内容；如果nByte不是负的，那么它就是这个函数能从zSql中读取的字节数的最大值。如果nBytes非负，zSql在第一次遇见’/000/或’u000’的时候终止
		 pzTail：上面提到zSql在遇见终止符或者是达到设定的nByte之后结束，假如zSql还有剩余的内容，那么这些剩余的内容被存放到pZTail中，不包括终止符
		 ppStmt：能够使用sqlite3_step()执行的编译好的准备语句的指针，如果错误发生，它被置为NULL，如假如输入的文本不包括sql语句。调用过程必须负责在编译好的sql语句完成使用后使用sqlite3_finalize()删除它。
	返回值：如果执行成功，则返回SQLITE_OK，否则返回一个错误码。

3.参数绑定：使用sqlite3_bind_*()给宿主参数（host parameters）绑定值

sqlite3_bind_int(sqlite3_stmt*,int,int);
sqlite3_bind_double(sqlite3_stmt*,int,double);
sqlite3_bind_text(sqlite3_stmt*,int,const char*,int,void(*)(void *));
参数：sqlite3_stmt*：准备语句变量指针。
	 int:sqlite3_stmt变量参数的序号索引值，规定最左侧的sql参数的索引值为1，也就是说参数索引值从1开始。
	 第三个参数：要给第二个参数指向的变量的实际值，第二个参数可以指向不同的索引值。
	 第四个参数：第三个参数的长度。
	 第五个参数：一般为NULL。

4.执行：VDBE执行字节码，执行是一个逐步的过程，每一步都由sqlite_step()发起。

5. 使用sqlite3——reset()重置这个语句，然后回到第2步，这个过程做0次或多次，只释放语句资源，可以提供多次重复操作的性能

6.完成：VDBE关闭释放资源，使用sqlite3_finalize()销毁这个对象

7.关闭数据库：sqlite3_close:关闭sqlite数据库

函数：int sqlite3_close(sqlite3 *db)；
功能: 关闭数据库
参数：db： 指向sqlite句柄的指针
返回值：成功返回0，失败返回错误码(非零值)

每一步准备、执行、完成对应statement句柄自己的状态：准备状态、活动状态、完成状态。准备状态已经分配了所有必须的资源，但是没有获取到锁。活动状态从第一个sqlite_step开始，完成意味着使用完毕进行关闭。且所有资源都被释放。下图显示了处理过程

8.参数化SQL：SQL语句会包含参数，参数就是占位符，可能会在编译后提供值。
例子：insert into food (id, name) value (?,?);

9.执行封装查询：已经封装好准备查询过程的函数
sqlite3_exec: 使用回调执行SQL操作，它是执行insert、update、delete语句，或用于创建和销毁数据库对象的DDL语句的快速而简便的方法。它直接在数据库连接上工作，将打开的sqlite3句柄连同包含一个或多个SQL语句的字符串作为参数。如果一次执行多个语句组成的字符串命令，那么它就一个个执行，如果中间有一个发生错误，则函数停止执行。

函数：int sqlite3_exec(sqlite3 *db, const char *sql, sqlite3_callback callback, void *, char **errmsg);
功能：通常用来执行不返回结果的查询
参数：db：数据库句柄
	 sql：SQL语句
	 callback：回调函数
	 errmsg：错误信息指针的地址
返回值：成功返回0，失败返回错误码
		
typedef int (*sqlite3_callback)(void *para, int f_num, char **f_value, char **f_name);
功能：每找到一条记录自动执行一次回调函数
参数：para：传递给回调函数的参数
	  f_num：记录中包含的字段数目
	  f_value：包含每个字段值的指针数组
	  f_name：包含每个字段名称的指针数组
返回值：成功返回0，失败返回-1
		
sqlite3_get_table：不使用回调函数执行SQL语句，以表格形式返回select结果。
int sqlite3_get_table(sqlite3 *db, const  char *sql, char ***resultp, int*nrow, int *ncolumn, char **errmsg);
功能：执行返回数据的查询
参数： db：数据库句柄
	  sql：SQL语句
	  resultp：用来指向sql执行结果的指针
	  nrow：满足条件的记录的数目（行）
	  ncolumn：每条记录包含的字段数目（列）
	  errmsg：错误信息指针的地址
返回值：成功返回0，失败返回错误码

10.错误处理
一般错误，API会通过sqlite3_errcode函数提供最后一个被调用的返回代码。可以使用sqlite3_errmsg()函数活动错误信息。
函数：const char *sqlite3_errmsg(sqlite3 *db);
返回值：错误信息。

11.SQL语句格式化
sqlite3_mprintf();功能用法同sprintf()函数

12.可操作的控制
API中包含很多使您可以监视、控制或限制数据库中发生什么的命令，SQLite以过滤或者回调函数的形式实现该功能，可以为指定事件注册要调用的函数。共有三种”钩子”函数：下一节详细将这三个函数

sqlite3_commit_hook():用于监视连接上的事务提交
sqlite3_rollback_hook():用于监视回滚
sqlite3_update_hook():用于监视insert、update、delete命令更改操作

二、扩展API

扩展API支持用户自定义函数、聚合和排序规则。用户自定义函数就是映射到用户自己的C语言函数中。用户自定义扩展必须是一对一注册连接，因为他们存储在程序内存中。

1.创建用户自定义函数
实现用户自定义函数有两步，首先编写处理程序，该处理程序要实现在SQL执行的事情，其次注册处理程序，提供SQL名称、参数个数以及指向该处理程序的指针。

2.创建用户自定义聚合
实现用户自定义聚合需要三步，注册聚合、实现结果集中每个记录调用的步骤函数、实现结果处理后调用的完成函数。完成函数允许计算最终聚合值并执行必要的处理函数。

3.创建用户自定义排序

三、事务

1.事务生命周期
每一个连接都有一个B-Tree和与之关联的pager，pager比连接作用大，因为他管理事务、锁和高速缓存以及故障恢复，可以说连接对象处理的这一切都是pager在处理，数据库在写操作时，是在用一个连接，一次一个事务，因此所有语句都是运行在派生在他们自身连接的单个事务上下中。
事务持续时间和单个语句命令一样长。事务可以分为读事务和写事务。

2.锁状态
大多数情况下锁持续时间是隐藏在事务中的，不一定一起开始但是总是一起结束，事务结束释放锁。下图是锁的状态和转换

3.读事务
从select语句的锁进程开始。执行select语句的连接启动事务，从未锁定到共享锁，提交之后回到未锁定状态，结束操作。锁路径如下：

UNLOCKED->PENDNG->SHARED->UNLOCKED

4.写事务
写操作与读操作一样，必须先到共享锁，未加锁->待定锁->共享锁。

1.保留状态：连接尝试向数据库写入内容时，必须从共享锁转换到保留锁，如果获得保留锁则准备好开始修改数据，如果此时不能修改数据库，那么可以将修改内容存储在本地的pager内的内存缓存中去。当连接进入保留状态时，pager初始化回滚日志，回滚日志是一个文件可以用于回滚和恢复数据。就是将数据库还原成事务开始前状态的数据库页。当B-Tree修改页的时候，pager将这些数据库页存放到日志文件中。因此当撤销事务的时候，pager只需要将日志文件中的内容复制到数据库中。保留状态下，pager管理着三种页：已修改页、未修改页和日志页。已修改页是记录着B-Tree修改的页，这些页存储在页缓存中。未修改页是B-Tree读取但是未修改的页，日志是已修改页的原始版本。因为页面缓存，写操作可以在保留状态中完成实际工作，不用干扰其他连接（读操作），因此sqlite可以允许一个写操作和多个读操作同时存在。

2.待定状态：当完成写操作，并提交事务时，pager开始进入独占状态的过程。在进入待定状态，写操作拥有待定锁等待其他连接释放共享锁，当其他连接释放完共享锁后，数据库就属于写操作了，从待定状态进入独占状态。

3.独占状态：独占状态是将已修改的页的内容刷新到数据库文件中。在pager写入修改页前，首先要将日志文件提交到磁盘上，否则当pager写如数据库文件中崩溃则无法恢复数据库。当日志保存到磁盘之后，pager就可以将已修改的页写入到数据库文件中了。

四、调整页面缓存

当写操作导致修改页面填满了页面缓存会怎么样？
1.过渡到独占状态
软限制对应页面第一次填满，此时，缓存是已修改和未修改页的混合，这种情况下pager会试图清除未修改页，当再次填满后再次清除，重复此过程直到缓存完全由已修改的页面填满，pager没有其他资源只能进入独占状态。此时就是硬限制了。之前说cache_size控制缓存页大小，页缓存越大，pager可容纳的修改页越多。连接进入独占状态前的工作也就越多。通过在保留状态完成数据库的工作，可以最小化独占状态的时间。

五、 等待锁

如果写操作正在等待或者正在写时候，select命令将无法获取共享锁，遇到返回SQLITE_BUSY时就会重新调用一次。
繁忙处理：创建一个回调函数，当SQLITE无法获取到锁时就调用它，不让API返回QLITE_BUSY。