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如何管理事务与锁
  • 事务与锁如何在后台工作
  • 代码如何工作