SQLite权威指南(第二版）第五章 SQLite设计与概念

本章是后面几章的基础，阅读本章可以更好的理解SQLite API和一些SQLite架构与实现

一、API

核心API
- 用于执行基本数据库操作的函数（连接数据库、处理SQL、迭代查询结果）
- 实用函数（字符串格式化、运行控制、调试和错误处理）
扩展API
- 提供通过创建用户自定义的SQL扩展来扩展SQLite的不同方式，也可以将自定义扩展融入SQLite的SQL语言中

1、主要数据结构

程序员须知：
- 连接（Connection）
- 语句（STMT，Statement的缩写）
- B-tree（多路搜索树）
- pager
图（API、事务、锁）
- 虚拟数据库引擎或VDBE(The Virtual Database Engine of SQLite)
- Cursor是用来对某次操作进行操作标记的疑问
  
  SQLite C API object model

1.1 连接和语句

API中与查询处理有关的两个基本数据结构是连接（connection<=>sqlite3）和语句（statement<=>sqlite_stmt）

Connection:数据库的连接和事务上下文，只在事务里运行（每次只能有一个打开的事务）
statement：一个编译的SQL语句（有一个关联的Connection对象）

1.2 B-tree和Pager

一个Connection可以连接到多个数据库对象（一个主数据库和多个附加数据库）。每个数据库对象都有一个B-tree和相应的一个pager对象

statement使用管理的Connection对象的B-tree和pager对数据库进行数据读写
pager负责读写数据库、维护内存缓存or页面、管理事务、管理锁、故障恢复

2.核心API

执行SQL 准备查询+封装查询

2.1 连接数据库

若数据库存在，则直接连接
若数据库不存在，且向连接的数据库添加内容时，则创建数据库

2.2 执行预查询

准备(编译)>执行->完成 (Preparation Execution Finalization)

Statement precessing

伪代码

# 1. 打开数据库）Open the database, create a connection object (db)
db = open('foods.db')
# 2.A. 准备）Prepare a statement
stmt = db.prepare('select * from episodes')
# 2.B. 执行）Execute. Call step() is until cursor reaches end of result set.
while stmt.step() == SQLITE_ROW
print stmt.column('name')
end
# 2.C. 完成）Finalize. Release read lock.
stmt.finalize()
# 4. 插入一条记录）Insert a record
stmt = db.prepare('INSERT INTO foods VALUES (…)')
stmt.step()
stmt.finalize()
# 5. 关闭数据库）Close database connection.
db.close()

2.3 使用参数化SQL

SQL语句可以包含参数，参数是编译后的对应绑定值的占位对象

参数绑定优点
- 多次执行同一个statement无需重新编译
- SQLite会负责转义绑定到参数的字符串值

2.4 执行封装查询

执行不返还结果的查询exec()
执行返回结果的查询get_table()

2.5 错误处理

errorcode()/errormsg()

2.6 SQL语句格式化

sprintf()/sqlite3_mprintf()

*** `SQL注入攻击`*

参考逆向，然后字符串替换

可操作的控制

监听数据库操作
- sqlite3_commit_hook() 监听Connection的transaction提交
- sqlite3_rollback_hook() 监听 rollback
- sqlite3_update_hook() 监听 insert、update、delete

使用线程

详情看第六章

二、扩展API

1.创建用户自定义函数

编写处理程序
注册处理程序

2.创建用户自定义聚合

注册聚合
为结果集中的每个记录实现要调用的步骤函数
实现记录处理后要调用的finalize函数

3.创建用户自定义排序

SQLite默认有3种排序：BINARY、NOCASE、RTRIM
详情看第七章

三、事务

1.事务生命周期

哪些对象在事务中运行？（所有语句对象statements都是运行在派生他们自身连接的单个事务上下文中）
事务何时开始、结束？何时开始影响其它连接？（默认情况下，事务与statement同时开始、结束）

2.锁状态

锁与对应的事务不同时生，但是同时死
事务中的锁的对应状态
- UNLOCKED（未锁）
- PENDING（待定）
- SHARED（共享）
- RESERVED（保留）
- EXCLUSIVE（独占）
  
  SQLite lock transitions

3.读事务

数据库允许多读；UNLOCKED→PENDING →SHARED →UNLOCKED
在同一个SHARED时可以执行多次读操作，且读到的数据是相同的
若在不同的SHARED状态中读，不一定相同

4. 写事务

如图5-3，写操作会进入EXCLUSIVE状态

4.1 保留状态

数据库写入数据之前，会进入准备状态→SHARED →RESERVED
此时不会直接修改数据库，而会将修改内容放到内容缓存中
Connection进入RESERVED状态时，pager 初始化回滚日志（用于回滚、故障恢复的文件）。B-tree修改page时，pager将他们都存放到回滚日志
RESERVED状态，pager管理3种page
- 已修改page（包含B-tree已改变记录的page，存在PageCache中）
- 未修改page(B-tree读但未改的页面)
- 日志page（已修改page的原版，不在PageCache中，但是在回滚日志里）
PageCache可以使数据库多读单写操作同时执行（多写不行哦）
数据库同一时间只能有一个连接处于RESERVED、EXCLUSIVE

4.2 待定状态

此过程会等待其它Connection释放锁，然后PENDING →EXCLUSIVE

4.3 独占状态

pager检查日志的完整内容是否已写入磁盘（为了还原）
将PageCache中的要修改的page刷新到数据库文件中

4.4 自动提交与效率

UNLOCKED →PENDING →SHARED →RESERVED →PENDING →EXCLUSIVE →UNLOCKED

`LOCKING AND CRASH RECOVERY(锁与故障恢复)`

数据库文件中包含3种文件锁：保留字节、待定字节、共享区域

Snip20191029_6.png

四、跳转页面缓存

PageCache满会发生什么？

1.过渡到独占状态

pager从保留锁RESERVED到独占锁EXCLUSIVE；When？Why？
- 正常进入- UNLOCKED →PENDING →SHARED →RESERVED →PENDING →EXCLUSIVE →UNLOCKED
- PageCache满

2.调整页面缓存

多个Connections并发使用数据库才需要调整PageCache大小

五、Waiting For锁

写操作时，独占之前会PENDING →EXCLUSIVE，这个PENDING状态会等待一段时间，每当SQLite遇到不能回去锁的情况时，会返回SQLITE_BUSY，遇到SQLITE_BUSY需要重新调用吗？

1. 自定义繁忙回调

遇到SQLITE_BUSY不需要反复重调用，可以不返回SQLITE_BUSY，而返回一个busy handle
busy handle 是咱们码农自定义的一个回调方法，里面可以随便写点儿消磨时间的代码

2. 处理恰当的事务

注意不要引起死锁（并发操作）

六、代码

1.使用多个连接

SQLite中，同时使用多个连接可能会出问题。问题代码示例：

c1 = open('foods.db')
c2 = open('foods.db')
stmt = c1.prepare('select * from episodes')
while stmt.step()
print stmt.column('name')
c2.exec('update episodes set <some columns, criteria, etc.>)
end
stmt.finalize()
c1.close()
c2.close()

问题描述：在 while 循环中，c2 视图执行update操作，此时c1的共享锁依然打开，white跳出这个stmt才会完成，共享锁才能释放。所以，在while期间，写操作无法执行。修改代码：

c1 = open('foods.db')
# Keep trying until we get it
while c1.exec('begin immediate') != SQLITE_SUCCESS
end
stmt = c1.prepare('select * from episodes')
c1 = open('foods.db')
# Keep trying until we get it
while c1.exec('begin immediate') != SQLITE_SUCCESS
end
stmt = c1.prepare('select * from episodes')

使用单个连接可以避免锁冲突问题，但是上面代码仍然有问题，下面继续讨论

2. finalize()函数的重要性

具体代码，待总结

3. 共享缓存模式

单个线程中多个连接如何使用共享缓存模式操作
这些连接可以对同一个数据库多读单写，共享同一个PageCache
详情参看第六章

七、总结

API

Snip20191029_8.png

SQL命令

Snip20191029_9.png

SQLite并发模式与其他数据库不同
- SQLite如何管理事务与锁
- 事务与锁如何在后台工作
- 代码如何工作

SQLite权威指南(第二版）第五章 SQLite设计与概念

一、API

1、主要数据结构

1.1 连接和语句

1.2 B-tree和Pager

2.核心API

2.1 连接数据库

2.2 执行预查询

2.3 使用参数化SQL

2.4 执行封装查询

2.5 错误处理

2.6 SQL语句格式化

******* SQL注入攻击*****

可操作的控制

使用线程

二、扩展API

1.创建用户自定义函数

2.创建用户自定义聚合

3.创建用户自定义排序

三、事务

1.事务生命周期

2.锁状态

3.读事务

4. 写事务

4.1 保留状态

4.2 待定状态

4.3 独占状态

4.4 自动提交与效率

LOCKING AND CRASH RECOVERY(锁与故障恢复)

四、跳转页面缓存

1.过渡到独占状态

2.调整页面缓存

五、Waiting For锁

1. 自定义繁忙回调

2. 处理恰当的事务

六、代码

1.使用多个连接

2. finalize()函数的重要性

3. 共享缓存模式

七、总结

*** `SQL注入攻击`*

`LOCKING AND CRASH RECOVERY(锁与故障恢复)`