SQLite 权威指南_中文版

631348609

贡献于2013-10-10

字数:0 关键词: SQLite 数据库服务器

1 SQLite 权威指南 The Definitive Guide to SQLite (内容摘要) Michael Owens Copyright . 2006 by Michael Owens 本书的示例代码可到 http://www.apress.com 下载。 2 推荐者的话 最近对 SQLite 很感兴趣,认真学习有一个多月了。 学习时基本找不到既好又系统的中文文章,也买不到好的中文书籍,看来 SQLite 在国内还 是不够流行,这么好的东西,可惜了。 以我中等偏下的眼界,《 The Definitive Guide to SQLite》是我所见到的最好的“SQLite 入门+ 大全”了,可惜也是英文的。实在找不到别的,也只好看它了,尽管我英语很不好。 由于英语很不好,又因为是打字员出身的干部,所以多年来养成了一个更不好的毛病,就是 在不得不看英文资料时总喜欢一边看一边翻,主要是怕下次再看时还是看不懂。看《The Definitive Guide to SQLite》时这个毛病也没改,当然了,看的时候就是挑着看的,翻的也只 是书中的一小部分了。 一般情况下看完也就看完了,很 少 有“下次再看”的 机会,这 次 例 外 。由于越学越觉着 SQLite 好玩,就想向身边的人也介绍一下,就“再看”了。越看越羞愧,本来英语就差,还随看随 翻,结果可想而知。但由于没什么动力,也就无意再重新润色了,就这样吧,反正也没什么 人看,估计遗害不会太广。 SQLite 是没有版权的,但这本书却是受版权保护的,也不知我这样做是否合法。发到网上 也只是想做一个好事,响应 SQLite 的共享精神。估计不会有人来告我吧,反正我没钱。另 外,我也只翻译了书中很小的一部分,也许根本算不上翻译(不能乱抬高自己), 就 算 是对 SQLite 和《The Definitive Guide to SQLite》一书的一个推荐吧,同样对 SQLite 感兴趣但又 看不懂我的中文的兄弟,强烈建议看原文。感谢 Richard Hipp 编出这么好的程序,感谢 Michael Owens 写出这么好的书。 “空转”只是我的网名之一,网上网下知之者甚少,也就是一起骑车的几个人知道吧。如果 本文对您能有一点点帮助,也算是我对 SQLite 做了一点贡献吧。本文中带有“空注”的内 容是我个人所做的简单说明和忏悔,与原作者无关(以我的翻译水平,估计全文跟原作者都 没什么关)。 接触 SQLite 时间不长,所以本文难免会有很多错误,不是故意误导大家,是真的水平低。 如果有兄弟想对我提出指导,我的邮箱是:njgaoyi@yahoo.com.cn。如果我没有回信,不是 因为不想回,是因为我很少上网,在此先行谢过。 分析源程序时,发现每个 SQLite 源文件的头部都有这样一段话: The author disclaims copyright to this source code. In place of a legal notice, here is a blessing: May you do good and not evil. May you find forgiveness for yourself and forgive others. May you share freely, never taking more than you give. 这几句话我很喜欢,翻译不好,就拿原文出来吧,与大家共勉。 空转 Ver 1.00: 2009-11-07 于南京 (如果以后有时间、兴趣,就把翻译过的内容好好修改一下,或者再多翻一些。但愿还有以 后的版本) 3 总目录 ■前言 ■第 1 章 SQLite 介绍 ■第 2 章 入门 ■第 3 章 关系模型 ■第 4 章 SQL ■第 5 章 设计和概念 ■第 6 章 核心 C API ■第 7 章 扩充 C API ■第 8 章 语言扩展 ■第 9 章 SQLite 内核 ■附录 A SQL 参考 ■附录 B C API 参考 ■附录 C Codd 的 12 条准则 ■索引 4 目录 SQLite 权威指南..............................................................................................................................1 总目录 ..............................................................................................................................................3 目录 ..................................................................................................................................................4 前言 ..................................................................................................................................................1 第 1 章 SQLite 介绍......................................................................................................................2 内嵌式数据库 ..........................................................................................................................2 开发者的数据库 ......................................................................................................................3 管理员的数据库 ......................................................................................................................3 SQLite 的历史..........................................................................................................................3 谁使用 SQLite..........................................................................................................................4 体系结构 ..................................................................................................................................4 接口(Interface)..................................................................................................................5 编译器(Compiler).............................................................................................................5 虚拟机(Virtual Machine)..................................................................................................5 后端(Back-end)................................................................................................................6 工具和测试代码(Utilities and Test Code).......................................................................7 SQLite 的特色..........................................................................................................................7 零配置 ..............................................................................................................................7 兼容性 ..............................................................................................................................7 紧凑性 ..............................................................................................................................7 简单 ..................................................................................................................................8 适应性 ..............................................................................................................................8 不受拘束的授权 ..............................................................................................................8 可靠性 ..............................................................................................................................8 易用性 ..............................................................................................................................8 性能和限制 ..............................................................................................................................9 附加信息 ..................................................................................................................................9 第 2 章 入门 ................................................................................................................................10 从哪得到 SQLite....................................................................................................................10 在 Windows 上使用 SQLite...................................................................................................10 获得命令行程序 ............................................................................................................10 获得 SQLite 的动态链接库(DLL).................................................................................10 在 Windows 环境下编译 SQLite 源代码......................................................................10 用 Microsoft Visual C++构建 SQLite DLL...................................................................11 用 Microsoft Visual C++构建 SQLite CLP....................................................................11 使用 SQLite 数据库...............................................................................................................11 Shell 模式下使用 CLP...................................................................................................11 在命令行方式下执行 CLP............................................................................................15 数据库管理 ............................................................................................................................15 创建、备份和删除数据库.............................................................................................15 5 获得数据库文件的信息.................................................................................................15 其它 SQLite 工具...................................................................................................................16 第 3 章 关系模型 ........................................................................................................................17 第 4 章 SQL.................................................................................................................................18 关系模型 ................................................................................................................................18 查询语言 ........................................................................................................................18 SQL 的发展....................................................................................................................18 示例数据库 ............................................................................................................................18 建立 ................................................................................................................................19 运行示例 ........................................................................................................................19 语法 ........................................................................................................................................19 命令 ................................................................................................................................20 常量 ................................................................................................................................20 保留字和标识符 ............................................................................................................20 注释 ................................................................................................................................20 创建一个数据库 ....................................................................................................................21 创建表 ............................................................................................................................21 改变表 ............................................................................................................................21 在数据库中查询 ....................................................................................................................22 关系操作 ........................................................................................................................22 操作管道 ........................................................................................................................23 过滤 ................................................................................................................................23 限定和排序 ....................................................................................................................25 函数(Function)和聚合(Aggregate)................................................................................26 分组(Grouping)..............................................................................................................27 去掉重复 ........................................................................................................................27 多表连接 ........................................................................................................................27 名称和别名 ....................................................................................................................28 修改数据 ................................................................................................................................28 插入记录 ........................................................................................................................28 修改记录 ........................................................................................................................28 删除记录 ........................................................................................................................29 数据完整性 ............................................................................................................................29 实体完整性 ....................................................................................................................29 域完整性 ........................................................................................................................30 存储类(Storage Classes).................................................................................................31 弱类型(manifest typing).................................................................................................32 类型亲和性(Type Affinity)............................................................................................33 事务 ........................................................................................................................................35 事务的范围 ....................................................................................................................36 冲突解决 ........................................................................................................................36 数据库锁 ........................................................................................................................36 死锁 ................................................................................................................................37 事务的种类 ....................................................................................................................38 6 数据库管理 ............................................................................................................................38 视图 ................................................................................................................................38 索引 ................................................................................................................................39 触发器 ............................................................................................................................39 附加(Attaching)数据库..................................................................................................40 清洁数据库 ....................................................................................................................40 数据库配置 ....................................................................................................................40 系统表 ............................................................................................................................42 查看 Query 的执行.........................................................................................................42 第 5 章 设计和概念 ....................................................................................................................44 API..........................................................................................................................................44 SQLite 版本 3 的新特性................................................................................................44 主要的数据结构 ............................................................................................................45 核心 API.........................................................................................................................46 操作控制 ........................................................................................................................52 扩充 API.........................................................................................................................53 事务 ........................................................................................................................................54 事务的生命周期 ............................................................................................................54 锁的状态 ........................................................................................................................55 读事务 ............................................................................................................................56 写事务 ............................................................................................................................56 调整页缓冲区 ................................................................................................................58 等待加锁 ........................................................................................................................59 编码 ........................................................................................................................................60 使用多个连接 ................................................................................................................60 表锁 ................................................................................................................................61 有趣的临时表 ................................................................................................................62 定案的重要性 ................................................................................................................63 共享缓冲区模式 ............................................................................................................63 第 6 章 核心 C API......................................................................................................................65 封装的查询 ............................................................................................................................65 连接和断开连接 ............................................................................................................65 执行 Query.....................................................................................................................66 字符串处理 ....................................................................................................................69 Get Table 查询................................................................................................................70 预处理的查询 ........................................................................................................................71 取记录 ............................................................................................................................73 参数化的查询 ................................................................................................................76 错误和意外 ............................................................................................................................76 处理错误 ........................................................................................................................76 处理忙状态 ....................................................................................................................78 操作控制 ................................................................................................................................78 提交 Hook 函数..............................................................................................................78 回卷 Hook 函数..............................................................................................................78 7 修改 Hook 函数..............................................................................................................78 授权函数 ........................................................................................................................79 线程 ........................................................................................................................................84 共享缓冲区模式 ............................................................................................................85 线程和内存管理 ............................................................................................................85 第 7 章 扩充 C API......................................................................................................................86 API..........................................................................................................................................86 注册函数 ........................................................................................................................86 步进函数 ........................................................................................................................86 返回值 ............................................................................................................................86 函数 ........................................................................................................................................86 返回值 ............................................................................................................................86 一个完整的例子 ............................................................................................................86 一个实际的应用程序.....................................................................................................88 聚合 ........................................................................................................................................88 一个实际的例子 ............................................................................................................88 排序法 ....................................................................................................................................90 排序法定义 ....................................................................................................................90 一个简单的例子 ............................................................................................................90 按需排序(Collation on Demand)....................................................................................93 一个实际的应用程序.....................................................................................................93 第 8 章 语言扩展 ......................................................................................................................100 第 9 章 SQLite 内核..................................................................................................................101 虚拟数据库引擎(VDBE).....................................................................................................101 栈(Stack).......................................................................................................................103 程序体 ..........................................................................................................................103 程序开始与停止 ..........................................................................................................104 指令的类型 ..................................................................................................................105 B-Tree 和 Pager 模型 ...........................................................................................................105 数据库文件格式 ..........................................................................................................106 B-Tree API....................................................................................................................109 编译器 ..................................................................................................................................111 分词器(Tokenizer)........................................................................................................111 分析器(Parser)..............................................................................................................112 代码生成器(Code Generator).......................................................................................113 优化 ..............................................................................................................................114 1 前言 2000 年春天,当我刚开始编写 SQLite 时,根本没想到它会在编程社区受到如此强烈的认可。 今天,有成百万的 SQLite 拷贝在默默地运行,在计算机中,或在不同公司生产的各种各样 的小设备中。你可能已经在无意识的情况下使用过 SQLite,在你的手机、MP3 或机顶盒里 可能就有 SQLite。在你的计算机里也可能至少会有一个 SQLite 的拷贝,它可能来自 Apple 的 Mac OS X,或者在大多数的 Linux 版本中,或者在 Windows 中安装某个第三方软件时。 很多 Web 网站的后台都使用 SQLite,这要感谢它已经被包含为 PHP5 语言的一部分。SQLite 也被用于很多航空电子设备、建模和仿真程序、工业控制、智能卡、决策支持包、医药信息 系统等。因为没有 SQLite 使用的全面报告,所以,肯定还有很多我不知道的 SQLite 部署。 SQLite 的普及很大程度上应该归功于 Michael Owens。Mike 在 The Linux Journal (June 2003) 和 The C/C++ Users Journal (March 2004)上的文章吸引了无数程序员。每篇文章发表后, SQLite 网站的访问量都会显著上升。通过这本书你可以看到 Mike 的才华和他所做的大量工 作,相信你不会失望。本书包含了关于 SQLite 所需要了解的所有内容,你应该一直把它放 在伸手可及的地方。 SQLite 是自由软件。尽管我是它的架构师和代码的主要编写者,但 SQLite 并不是我的程序。 SQLite 不属于任何人,也不在版权的保护范围之内。所有曾经为 SQLite 项目贡献过代码的 人都签署过一个宣誓书将他们的贡献发布到公共域,我 把 这 些 宣誓书的原件保存在办公室的 保险箱里。我还尽力保证在 SQLite 中不使用专利算法,这些预防措施意味着你可以以任何 形式使用 SQLite,而不需要付版税、许可证费用或受到其它任何限制。 SQLite 仍然在发展。但我和其他开发者都坚守它的核心价值。我们将保持代码的小规模— —核心库不会超过 250KB。我们将保持公共 API 和文件格式的向上兼容性。我们将继续保 证 SQLite 是充分测试的和无 bug 的。我 们 希 望你总是能够将新版本的 SQLite 放到你老的程 序中,既得到它新的特性和优化,又不需要或仅需要很少的代码改动,且不需要做进一步的 调试。2004 年,我们将 SQLite 从版本 2 升级到版本 3 时确实没能保持向上兼容性,但从那 以后,我们已经能够达到上述所有目标并准备在将来继续这样做。没有 SQLite 版本 4 的计 划。 真诚希望你觉着 SQLite 是有用的,我代表 SQLite 的所有贡献者保证,使用 SQLite 你会: 做出美好的产品,你的产品将会是快速、稳定和易用的。寻求宽恕并宽恕他人。因为你已经 免费地得到了 SQLite,也请你免费地给予他人一些东西作为回报。做一回志愿者,贡献出 其它的软件项目或找到其它途径来回报。 Richard Hipp Charlotte, NC April 11, 2006 2 第 1 章 SQLite 介绍 SQLite 是一个开源的、内嵌式的关系型数据库。它最初发布于 2000 年,在便携性、易用性、 紧凑性、有效性和可靠性方面有突出的表现。 内嵌式数据库 SQLite 是一个内嵌式的数据库。 数据库服务器就在你的程序中,其好处是不需要网络配置和管理。数据库的服务器和客户端 运行在同一个进程中。这样可以减少网络访问的消耗,简化数据库管理,使你的程序部署起 来更容易。所有需要你做的都已经和你的程序一起编译好了。 如图 1-1 所示。一个 Perl 脚本、一个标准 C/C++程序和一个使用 PHP 编写的 Apache 进程都 使用 SQLite。Perl 脚本导入 DBI::SQLite 模板,并通过它来访问 C API。PHP 采用与 C 相似 的方式访问 C API。总之,它们都需要访问 C API。尽管它们每个进程中都有独立的数据库 服务器,但它们可以操作相同的数据库文件。SQLite 利用操作系统功能来完成数据的同步 和加锁。 图 1-1 内嵌的主进程中的 SQLite 目前市场上有多种为内嵌应用所设计的关系型数据库产品,如 Sybase SQL Anywhere、 InterSystems Caché、Pervasive PSQL 和微软的 Jet Engine。有些厂家从他们的大型数据库产 品翻新出内嵌式的变种,如 IBM 的 DB2 Everyplace、Oracle 的 10g 和微软的 SQL Server Desktop Engine。开源的数据库 MySQL 和 Firebird 都提供内嵌式的版本。在所有这些产品中, 3 仅有两个是完全开放源代码的且不收许可证费用——Firebird 和 SQLite。在这两个当中,仅 有一个是专门为内嵌式应用设计的——SQLite。 开发者的数据库 SQLite 具有多方面的特性。它是一个数据库,一个程序库,一个命令行工具,也是一个学 习关系型数据库的很好的工具。确实有很多途径可以使用它——内嵌环境、网站、操作系统 服务、脚本语言和应用程序。对于程序员来说,SQLite 就象一个数据传送带,提供了一种 方便的将应用程序绑定的数据的方法。就象传送带一样,对 SQLite 的使用没有终点。 除了仅仅作为一个存储容器,SQLite 还可以作为一个单纯的数据处理的工具。如果大小和 复杂性合适,使用 SQLite 可以很容易地将应用程序所使用的数据结构转化为表,并保存在 一个内在数据库中。用此方法,你可以操作互相关联的数据,可以完成很繁重的任务页不必 写自己的算法来对数据结构操作和排序。如果你是一个程序员,想像一下在你的程序中自行 完成下面 SQL 语句所代表的工作需要多少代码: SELECT AVG(z-y) FROM table GROUP BY x HAVING x > MIN(z) OR x < MAX(y) ORDER BY y DESC LIMIT 10 OFFSET 3; SQLite 还是一个很好的学习程序设计的工具,通过它可以研究很多计算机科学的课题。分 析器、分词器、虚拟机、Btree 算法、高整缓存、程序体系结构,通过这些内容可以搞清楚 很多计算机科学的经典概念。SQLite 的模块化、小型化和简易性,使你可以很容易地专门 研究其中的一个问题。 管理员的数据库 SQLite 不仅是程序员的数据库,它对系统管理员也很有用。它很小、紧凑而精致,就像一 些 Unix 的常用工具,如 find、rsync 或 grep。SQLite 提供了命令行工具供用户交互操作。 另外,对于关系型数据库的初学者来说,SQLite 是一个学习各种关系相关概念的方便的学 习工具。它可以很快很容易地安装在各类操作系统中,它的数据库文件可以自由共享页不需 要任何转换。它 具 有 关 系 型数据库的各种特色而又不令人生畏。它的程序和数据库文件仅用 U 盘就能传递。 SQLite 的历史 从某个角度来说,SQLite 最初的构思是在一条军舰上进行的。SQLite 的作者 D. Richard Hipp 当时正在为美国海军编制一种使用在导弹驱逐舰上的程序。那个程序最初是运行在 Hewlett-Packard Unix (HPUX)上,后台使用 Informix 数据库。对那个程序来说,Informix 有 点儿太强大了。一个有经验的数据库管理员(DBA)可能需要一整天来对它进行安装和升级, 如果没经验,这个工作就可能永远也做不完了。 2000 年一月,Hipp 开始和一个同事讨论关于创建一个简单的内嵌式 SQL 数据库的想法,这 个 数据库将使用 GNU DBM B-Tree library (gdbm)做后台,同时这个数据库将不需要安装和 管理支持。后来,当有些空闲时间时,Hipp 就开始实施这项工作,并在 2000 年的八月份发 布了 SQLite 的 1.0 版。 4 按照原定计划,SQLite 1.0 用 gdbm 来做存储管理。但后来,Hipp 很快就换成了自己的 B-tree, 以支持事务和记录按主键的存储。随着最初的升级,SQLite 在功能和用户数上都得到了稳 步的发展。在 2001 年中期,很多项目——开源的或商业的——都开始使用 SQLite。在那以 后的几年中,开源社区的其他成员开始为他们喜欢的程序设计语言编写 SQLite 扩展。SQLite 的 ODBC 接口可以为 Perl、Python、Ruby、Java 和其它主流的程序设计语言提供支持,这 证明了 SQLite 有广阔的应用前景。 2004 年,SQLite 从版本 2 升级到版本 3,这是一次大升级。主要目的是增加内置的对 UTF-8、 UTF-16 及用户定义字符集的支持。While 3.0 was originally slated for release in summer 2005, America Online provided the necessary funding to see that it was completed by July 2004. 除国 际化功能外,版本 3 的其它新特性包括:经过修补的 C API,更紧凑的数据库文件格式(比 原来节省 25%的空间),弱类型,大 二 进 制 对 象 (BLOB)的支持,64-bit 的 ROWID,autovacuum 和改进了的并发控制。尽管增加了这一系列新特性,版本 3 的运行库仍然小于 240K 字节。 Another improvement in version 3 was a good code cleanup—revisiting and rewriting, or otherwise throwing out extraneous stuff accumulated in the 2.x series. SQLite 持续增长并始终坚持其最初的设计目标:简单、弹性、紧凑、速度和彻底的易用。 本书出版时,SQLite 已经增加了 CHECK 约束,下面就要增加外键约束,再下面呢? 谁使用 SQLite 当前,SQLite 已经被多种软件和产品所使用。它被用在 Apple 的 Mac OS X 操作系统中,被 用作其 CoreData 应用程序架构的一部分。它还应用于 Safari 的 Web 浏览器、Mail.app 的电 子邮件程序、RSS 的管理、Apple 的 Aperture 照片软件。 尽管 SQLite 很少做广告,但它还是被用在了多种消费类产品中。 体系结构 SQLite 拥有一个精致的、模块化的体系结构,并引进了一些独特的方法进行关系型数据库 的管理。它由被组织在 3 个子系统中的 8 个独立的模块组成,如图 1-2 所示。这个模型将查 询过程划分为几个不连续的任务,就像在流水线上工作一样。在 体 系 结 构 栈 的 顶 部 编译查询 语句,在中部执行它,在底部处理操作系统的存储和接口。 5 图 1-2 SQLite 的体系结构 接口(Interface) 接口由 SQLite C API 组成,也就是说不管是程序、脚本语言还是库文件,最终都是通过它 与 SQLite 交互的(我们经常使用的 ODBC/JDBC 最后也会转化为相应 C API 的调用)。 编译器(Compiler) 编译过程从分词器(Tokenizer)和分析器(Parser)开始。它们协作处理文本形式的结构化查询 (Structured Query Language, SQL)语句,分析其语法有效性,转化为底层能更方便处理的层 次数据结构——语法树,然后把语法树传给代码生成器(code generator)进行处理。SQLite 分 词器的代码是手工编写的,分析器代码是由 SQLite 定制的分析器生成器(称为 Lemon)生成 的。The Lemon parser generator is designed for high performance and takes special precautions to guard against memory leaks. 一旦 SQL 语句被分解为串值并组织到语法树中,分析器就将该 树下传给代码生成器进行处理。而代码生成器根据它生成一种 SQLite 专用的汇编代码,最 后由虚拟机(Virtual Machine)执行。 虚拟机(Virtual Machine) 架构中最核心的部分是虚拟机,或者叫做虚拟数据库引擎(Virtual DataBase Engine,VDBE)。 它和 Java 虚拟机相似,解释执行字节代码。VDBE 的字节代码(称为虚拟机语言)由 128 个操 作码(opcodes)构成,主要是进行数据库操作。它的每一条指令或者用来完成特定的数据库操 作(比如打开一个表的游标、开始一个事务等),或者为完成这些操作做准备。总之,所有的 这些指令都是为了满足 SQL 命令的要求。VDBE 的指令集能满足任何复杂 SQL 命令的要求。 6 所有的 SQLite SQL 语句——从选择和修改记录到创建表、视图和索引——都是首先编译成 此种虚拟机语言,组成一个独立程序,定义如何完成给定的命令。例如,在 SQLite 的 CLP 中执行下面语句: sqlite> .m col sqlite> .h on sqlite> .w 4 15 3 3 3 10 3 sqlite> explain SELECT name FROM episodes LIMIT 10; SQLite 会显示编译后的 VDBE 汇编程序,如列表 1-1 所示。 列表 1-1 VDBE 汇编程序 addr opcode p1 p2 p3 p4 p5 comment ---- --------------- --- --- --- --------------- ---- ---------- 0 Trace 0 0 0 00 1 Integer 10 1 0 00 2 MustBeInt 1 0 0 00 3 IfZero 1 13 0 00 4 Goto 0 14 0 00 5 OpenRead 0 2 0 3 00 6 Rewind 0 12 0 00 7 Column 0 2 2 00 8 ResultRow 2 1 0 00 9 AddImm 1 -1 0 00 10 IfZero 1 12 0 00 11 Next 0 7 0 01 12 Close 0 0 0 00 13 Halt 0 0 0 00 14 Transaction 0 0 0 00 15 VerifyCookie 0 40 0 00 16 TableLock 0 2 0 episodes 00 17 Goto 0 5 0 00 程序由 17 条指令组成。通过对给定的操作数完成特别的操作,这些指令将会返回 episodes 表前 10 个记录的 name 字段的值。episodes 表是本书示例数据库的一部分。 从多个方面都可以看出,VDBE 是 SQLite 的核心:它上面的各模块都是用于创建 VDBE 程 序,它下面的各模块都是用于执行 VDBE 程序,每次执行一条指令。 后端(Back-end) 后端由 B-tree、页缓冲(page cache,pager)和操作系统接口(即系统调用)构成。B-tree 和 page cache 共同对数据进行管理。它 们操 作的是数据库页,这 些 页 具 有 相 同 的大小,就像集装箱。 页里面的“货物”是表示信息的大量 bit,这些信息包括记录、字段和索引入口等。B-tree 和 pager 都不知道信息的具体内容,它们只负责“运输”这些页,页不关心这些“集装箱” 里面是什么。 B-tree 的主要功能就是索引,它维护着各个页之间的复杂的关系,便于快速找到所需数据。 它把页组织成树型的结构(这是它名称的由来),这种树是为查询而高度优化了的。Page 为 B-tree 服务,为它提供页。Pager 的主要作用就是通过 OS 接口在 B-tree 和磁盘之间传递页。 7 磁盘操作是计算机到目前为止所必须做的最慢的事情。所以,pager 尽力提高速度,其方法 是把经常使用的页存放到内存当中的页缓冲区里,从而尽量减少操作磁盘的次数。它 使用特 殊的算法来预测下面要使用哪些页,从而使 B-tree 能够更快地工作。 工具和测试代码(Utilities and Test Code) 工具模块中包含各种各样的实用功能,还有一些如内存分配、字符串比较、Unicode 转换之 类的公共服务也在工具模块中。这个模块就是一个包罗万象的工具箱,很多其它模块都需要 调用和共享它。 测试模块中包含了无数的回归测试语句,用来检查数据库代码的每个细微角落。这个模块是 SQLite 性能如此可靠的原因之一。 SQLite 的特色 尽管 SQLite 是如此之小,却提供了如此之多的特色和性能。它支持 ANSI SQL92 的一个大 子集(包括事务、视图、检查约束、关联子查询和复合查询等),还支持其它很多关系型数据 库的特色,如触发器、索引、自动增长字段和 LIMIT/OFFSET 子句等。SQLite 还有很多独 特的特色,如内在数据库、动态类型和冲突解决(下面解释)。 如本章开始时所述,在 SQLite 的观念和实现中,都遵循着一系列指导原则。下面就来详述 这些原则。 零配置 从 SQLite 的设计之始,就没准备在应用时使用 DBA。配置和管理 SQLite 就像得到它一样 简单。SQLite 包含了正好适合于一个程序员的脑筋的特色。 兼容性 SQLite 在设计时特别注意了兼容性。它可以编译运行在 Windows、Linux、BSD、Mac OS X 及商用的 Unix 系统如 Solaris、HPUX 和 AIX,还可以应用于很多嵌入式平台如 QNX、 VxWorks、Symbian、Palm OS 和 Windows CE。它可以无缝地工作在 16-bit、32-bit 和 64-bit 体系结构中并且能同时适应字节的大端格式和小端格式。SQLite 的兼容性并不只表现在代 码上,还表现在其数据库文件上。SQLite 的数据库文件在其所支持的所有操作系统、硬件 体系结构和字节顺序上都是二进制一致的。你可以在 Sun SPARC 工作站上创建一个 SQLite 数据库然后在 Mac 或 Windows 的机器上——甚至移动电话上——使用它,而不需要做任何 转换和修改。此外,SQLite 数据库可以支撑 2TB 的数据量(受操作系统限制),还内置地同 时支持 UTF-8 和 UTF-16 编码。 紧凑性 SQLite 的设计可以说是功能齐全但体积很小:1 个头文件,1 个库,不需要扩展的数据库服 8 务。所有的东西,包括客户端、服务器和虚拟机等,都被打包在 1/4 兆大小之内。如果在编 译时去掉一些不需要的特性,程序库可以缩小至 170KB (在x86 硬件平台上使用 GNU C进 行编译)。此外,还有一个 SQLite 的私有版本,大小是 69KB,可以运行在智能卡上(参“附 加信息”一节)。 空注:我下载的 DLL 有 500 多 KB。 简单 作为程序库,SQLite 的 API 可以算是最简单最易用的了。SQLite 既有很好的文档又很容易 望文知意。 适应性 SQLite 的几个特性使其成为一个适应性极强的数据库。作为一个内嵌式的数据库,SQLite 在以下两个方面都做得最好:强有力而可伸缩的关系型数据库前端,简单而紧凑的 B-tree 后端。 不受拘束的授权 SQLite 的全部代码都在公共域中,不需要授权。SQLite 的任何一部分都没有附加版权要求。 所有曾经为 SQLite 项目贡献过代码的人都签署过一个宣誓书将他们的贡献发布到公共域。 也就是说,无论你如何使用 SQLite 的代码都不会有法律方面的限制。你可以修改、合并、 发布、出售或将这些代码用于任何目的,商业和中非商业的,不需要支付任何费用,不会受 到任何限制。 可靠性 SQLite 的源代码不但免费,还编写得很好。SQLite 源代码包含大约 30000 行标准 C 代码, 它是干净的、模块化的和完好注释的。SQLite 源代码易理解、易定制。 SQLite 的核心软件(库和工具)由约 30000 行代码组成,但分发的程序中还包含有超过 30000 行的回归测试代码,它们覆盖了 97%的核心代码。也就是说,超过一半的 SQLite 项目代码 是专门用于回归测试的,也 就 是说,差不多每写一行功能代码,都要写一行测试代码对它进 行测试。 易用性 SQLite 还提供一些独特的功能来提高易用性,包括动态类型、冲突解决和“附加”多个数 据库到一个连接的能力。 9 性能和限制 SQLite 是一个快速数据库。但“快速”这个词本身是一个主观的和不明确的词。诚实地讲, 有些事情 SQLite 能比其它数据库做得快,也有些事情不能。这么说吧,利用 SQLite 提供的 配置参数,SQLite 是足够快速和高效的。跟大多数其它数据库一样,SQLite 使用 B-tree 处 理索引,使用 B+tree 处理表数据。因此,在对单表进行查询时,SQLite 要快于(或至少相当 于)其它数据库的速度。 在一些情况下 SQLite 可能不如大型数据库快,但大多数这些情况是可理解的。SQLite 是一 个内嵌式的数据库,设计用于中小规模的应用程序。这些限制是符合设计目的的。很多新用 户错误地假设使用 SQLite 可以代替大型关系型数据库,这有时行,但有时不行,依赖于你 准备用 SQLite 来做什么。一般情况下,SQLite 在三个主要的方面具有局限性: l 并发。 l 数据库大小。 l 网络。 尽管 SQLite 做得已经很好了,但仍有部分特性未能实现,包括: l 外键约束 空注:SQLite 的最新版本 3.6.19 好像已经支持了。 l 完整的触发器支持。 l 完整的ALTER TABLE 支持。 l 事务嵌套。 l RIGHT 和 FULL OUTER JOIN。 l 可修改视图。 l GRANT 和 REVOKE。 附加信息 SQLite 网站有丰富的信息,包括官方文档、邮件列表、Wiki 和其它的一般信息,它的网址 是 www.sqlite.org。SQLite 社区也是很有帮助的,你可能从邮件列表中找到任何你所需要的 东西。另外,SQLite 的作者提供了 SQLite 的专业培训和支持,包括定制程序(如移植到嵌入 式平台)和增强的 SQLite 版本,这 些 版 本 包 括 内 置 了 加 密 功 能 的 版 本 和 为 嵌 入 式 应 用 优化的 极小化版本。更多的信息可以从 www.hwaci.com/sw/sqlite/prosupport.html 中找到。 10 第 2 章 入门 无论您使用何种操作系统,SQLite 都很容易上手。对大多数用户,安装 SQLite 并创建一个 新的数据库不会超过 5 分钟,且不需要任何经验。 空注:本章我只看了 Windows 操作系统下使用 VC 的内容。 从哪得到 SQLite SQLite 网站(www.sqlite.org)同时提供 SQLite 的已编译版本和源程序。编译版本可同时适用 于 Windows 和 Linux。 有几种形式的二进制包供选择,以适应 SQLite 的不同使用方式。包括: l 静态链接的命令行程序(CLP) l SQLite 动态链接库(DLL) l Tcl 扩展 SQLite 源代码以两种形式提供,以适应不同的平台。一种为了在 Windows 下编译,另一种 为了在 POSIX 平台(如 Linux, BSD, and Solaris)下编译,这两种形式下源代码本身是没有差 别的。 在Windows 上使用 SQLite 无论你是作为终端用户还是作为程序员来使用 SQLite,SQLite 都可以很容易地安装在 Windows 环境下。本 节 我 们将讨论所有相关的内容——安装二进制包或在最普通的编译环境 下使用源代码。 获得命令行程序 SQLite 命令行程序(CLP)是开始使用 SQLite 的一个比较好的选择。 略,参原文。 获得 SQLite 的动态链接库(DLL) SQLite 的 DLL 文件供编译好的程序动态连接 SQLite。大多数使用 SQLite 的软件都会拥有 自己的 SQLite DLL 拷贝并随软件自动安装。 在 Windows 环境下编译 SQLite 源代码 在 Windows 环境下编译 SQLite 源代码是很简单的。根据你所使用的编译器和你要做什么, 有几种方法来编译 SQLite。最常见的环境是 Microsoft Visual C++或 MinGW,本节都会加以 11 介绍。关于使用其它编译器编译 SQLite 的内容,可参考 SQLite Wiki (www.sqlite.org/cvstrac/wiki?p=HowToCompile)。 用 Microsoft Visual C++构建 SQLite DLL 通过以下步聚,可使用源代码,在 Visual C++上构建 SQLite DLL: 1. 启动 Visual Studio。在解包的 SQLite 源程序目录中创建一个新的 DLL“空”项目。 高:不同版本操作略有不同,不详细解释了。 2. 将全部 SQLite 源文件加入到项目中来。包括所有的.c 文件和.h 文件。除了: shell.c:该文件包括 main()函数,用于创建 CLP 可执行程序。 tclsqlite.c:该文件用于 TCL 支持。 空注:我使用的版本(sqlite-source-3_6_18.zip)有些函数有重复定义,还得去掉两个文件,不 知会引起什么后果,它们是 fts3.c 和 fts3_tokenizer.c。 3. 执行构建(Build)命令,OK。 还可以选择构建线程完全的 DLL 或发布 (Release) 版的 DLL,参原文。 用 Microsoft Visual C++构建 SQLite CLP 方法基本同上。 创建项目时选择 Win32 Console Application,添加文件时把 shell.c 也加上,即可。 使用 SQLite 数据库 SQLite 的 CLP 是使用和管理 SQLite 数据库最常用的方法。 它可运行于多种平台,学会使用 CLP,可以保证你永远有一个通用和熟悉的途径来管理你 的数据库。CLP 其实是两个程序。它可以运行在命令行模式下完成各种数据库管理任务, 也可以运行在 Shell 模式下,以交互的方式执行查询操作。 Shell 模式下使用 CLP 运行 DOS shell,进入工作目录,在命令行上键入 sqlite3 命令,命令后跟随一个可选的数据 库文件名。如果在命令行上不指定数据库名,SQLite 将会使用一个内存数据库,其内容在 退出 CLP 时将会丢失。 CLP 以交互形式运行,你可以在其上执行查询、获得 schema 信息、导入/导出数据和执行其 它各种各样的数据库任务。CLP 认为你输入的任何语句都是一个查询命令(query),除非命令 是以点(.)开始,这些命令用于特殊操作。键入.help 或.h 可以得到这些操作的完整列表。键 入.exit 或.e 退出 CLP。 让我们从创建一个称为 test.db 的数据库开始。在 DOS shell 下键入: sqlite3 test.db 尽管我们提供了数据库名,但如果这个数据库并不存在,SQLite 并不会真正地创建它。SQLite 会等到你真正地向其中增加了数据库对象之后才创建它,比如在其中创建了表或视图。这 样 做 的 原 因是给你机会在将数据库写到外部文件之前对数据库做一些永久性的设置,如页的大 12 小等。有些设置,如页大小、字符集(UTF-8 或 UTF-16)等,一旦数据库创建之后就不能再 修改了。这个中间期是你能改它们的唯一机会。我们采用默认设置,因此,要将数据库写到 磁盘,我们仅需要在其中创建一个表。输入如下语句: sqlite> create table test (id integer primary key, value text); 现在你有了一个称为 test.db 的数据库文件,其中包含一个表 test,该表包含两个字段。 l 一个称为 id 的主键字段,它带有自动增长属性。无论何时你定义一个整型主键字段, SQLite 都会对该字段应用自动增长属性。 l 一个简单的称为 value 的文本字段。 向表中插入几行数据: sqlite> insert into test (value) values('eenie'); sqlite> insert into test (value) values('meenie'); sqlite> insert into test (value) values('miny'); sqlite> insert into test (value) values('mo'); 将插入的数据取回: sqlite> .mode col sqlite> .headers on sqlite> SELECT * FROM test; 系统显示: id value 1 eenie 2 meenie 3 miny 4 mo SELECT 语句前的两个命令(.headers and .mode)用于改进输出的格式。可以看到 SQLite 为 id 字段赋予了连接的整数值,而这些值我们在 INSERT 语句中并没的提供。对于自动增长的字 段,你可能会关心最后插入的一条记录该字段的取值,此值可以用SQL函数last_insert_rowid() 得到。 sqlite> select last_insert_rowid(); last_insert_rowid() 4 在退出CLP 之前,让我们来为数据库创建一个索引和一个视图,后面的内容中将会用到它 们。 sqlite> create index test_idx on test (value); sqlite> create view schema as select * from sqlite_master; 使用.exit 命令退出 CLP。 sqlite> .exit C:\Temp> 获得数据库的 Schema 信息 有几个 shell 命令用于获得有关数据库内容的信息。你可以键入命令.tables [pattern]来得到所 有表和视图的列表,其中[pattern]可以是任何类 SQL 的操作符。执行上述命令会返回符合条 件的所有表和视图,如果没有 pattern 项,返回所有表和视图。 sqlite> .tables 13 schema test 可以看到我们创建的表 test 和视图 schema。同样的,要显示一个表的索引,可以键入命 令.indices [table name]: sqlite> .indices test test_idx 可以看到我们为表 test 所创建的名为 test_idx 的索引。使用.schema [table name]可以得到一 个表或视图的定义(DDL)语句。如果没提供表名,则返回所有数据库对象(包括 table、indexe、 view 和 index)的定义语句: sqlite> .schema test CREATE TABLE test (id integer primary key, value text); CREATE INDEX test_idx on test (value); sqlite> .schema CREATE TABLE test (id integer primary key, value text); CREATE VIEW schema as select * from sqlite_master; CREATE INDEX test_idx on test (value); 更详细的 schema 信息可以通过 SQLite 唯一的一个系统视图 sqlite_master 得到。这个视图是 一个系统目录,它的结构如表 2-1 所示。 表 2-1 sqlite_master 表结构 编号 字段 说明 1 type 值为"table"、 "index"、 "trigger"或"view"之一。 2 name 对象名称,值为字符串。 3 tbl_name 如果是表或视图对象,此字段值与字段 2 相同。如果是索引或触 发器对象,此字段值为与其相关的表名。 4 rootpage 对触发器或视图对象,此字段值为 0。对表或索引对象,此字段 值为其根页的编号。 5 SQL 字符串,创建此对象时所使用的 SQL 语句。 查询当前数据库的 sqlite_master 表,返回: sqlite> .mode col sqlite> .headers on sqlite> select type, name, tbl_name, sql from sqlite_master order by type; type name tbl_name sql --------- index test_idx test CREATE INDEX test_idx on test (value) table test test CREATE TABLE test (id integer primary view schema schema CREATE VIEW schema as select * from s ■Tip:使用向上的箭头键可以回滚到前面输入过的命令。 数据导出 可以使用.dump 命令将数据库导出为 SQL 格式的文件。不 使用任何参数,.dump 将导出整个 数据库。如果提供参数,CLP 把参数理解为表名或视图名。 sqlite> .output file.sql 14 sqlite> .dump sqlite> .output stdout 数据导入 有两种方法可以导入数据,用哪种方法决定于要导入的文件的格式。如果文件由 SQL 语句 构成,可以使用.read 命令导入(执行)文件。如果文件是由逗号或其它定界符分隔的值 (comma-separated values, CSV)组成,可使用.import [file][table]命令。此命令将解析指定的文 件并尝试将数据插入到指定的表中。 sqlite> .show echo: off explain: off headers: on mode: column nullvalue: "" output: stdout separator: "|" width: .read 命令用来导入由.dump 命令创建的文件。如果要使用前面作为备份文件所导出的 file.sql,需要先移除已经存在的数据库对象(test 表和 schema 视图),然后用下面方法导入: sqlite> drop table test; sqlite> drop view schema; sqlite> .read file.sql 格式化 CLP 提供了几个格式化选项命令。最简单的是.echo, 如果设置.echo on,则新输入的命令在 执行前都会回显,默认值是 off。.headers 设置为 on 时,查询结果显示时带有字段名。当遇 到 NULL 值时,如果需要以一个字符串来显示,使用.nullvalue 命令设置,如: sqlite> .nullvalue NULL 默认情况下使用空串。如果要改变 CLP 的 shell 提示符,使用.prompt [value],如: sqlite> .prompt 'sqlite3> ' sqlite3> .mode 命令可以设置结果数据的几种输出格式。可选的格式为 csv、column、html、insert、 line、list、tabs 和 tcl。默认值是 list,在此模式下显示结果时列间以默认的分隔符分隔。如 果你想以 CSV 格式输出一个表的数据,可如下操作: sqlite3> .output file.csv sqlite3> .separator , sqlite3> select * from test; sqlite3> .output stdout 文件 file.csv 的内容为: 1,eenie 2,meenie 15 3,miny 4,mo 因为有一个 CSV 模式,所以下面的命令会得到相似的结果: sqlite3> .output file.csv sqlite3> .mode csv sqlite3> select * from test; sqlite3> .output stdout 在命令行方式下执行 CLP 在DOS 或 UNIX 的命令行方式下,直接执行 SQLite 的数据库操作。 数据库管理 所有的数据库管理任务都可以在 shell 和命令行模式下完成。 创建、备份和删除数据库 数据库的备份有两种方法。第 1 种是使用.dump,可得到 SQL 格式的文件。在命令行方式下 可如下做: sqlite3 test.db .dump > test.sql 在 CLP 中可如下做: sqlite> .output file.sql sqlite> .dump sqlite> .exit 相应地,导入一个 SQL 格式备份的数据库可如下做: sqlite3 test.db < test.sql 此处假设 test.db 不存在。如果它存在,则或许会因为数据库中有同名的对象而出错。 可以用复制的方法得到一个二进制的数据库文件拷贝。但也许在复制之前你想先抽空 (vacuum)它,也就是释放数据库文件中未使用的空间,以得到一个更小的数据库文件。可操 作如下: sqlite3 test.db VACUUM cp test.db test.backup 一般情况下,二进制的备份如不 SQL 备份兼容性好。尽管 SQLite 有很好的向上兼容性和各 操作系统间文件格式的一致性,但如果想要将备份文件保留很长时间,还是 SQL 格式保险 一些。 当一个数据库你不想再用时,简单地从操作系统中将其文件删除就行了。 获得数据库文件的信息 按前文所述,获得数据库信息的主要途径是使用 sqlite_master 视图,它提供一个数据库所包 含的所有对象的细节信息。 16 如果你想获得关于物理的数据库结构信息,可以使用一个称为 SQLite Analyzer 的工具,它 可以在 SQLite 网站上下载得到。SQLite Analyzer 可以提供磁盘 SQLite 数据库的详细技术信 息。 (输出结果略) 其它 SQLite 工具 有很多其它开源的或商业的程序可工作于 SQLite,其中具有优秀图形化界面且跨平台的有: l SQLite Database Browser (http://sqlitebrowser.sourceforge.net) l SQLite Control Center (http://bobmanc.home.comcast.net/sqlitecc.html) l SQLiteManager (www.sqlabs.net/sqlitemanager.php) 17 第 3 章 关系模型 SQL 具有非常实用的外观和非常理论化的内涵,这个内涵就是关系模型。关系模型早于 SQL 出现并对 SQL 的出现提出了需求。SQL 的原动力不在语言本身,而是深藏在关系模型的概 念当中。这些概念构成了 SQL 设计和操作的基础。 空注:数据库基本理论,参考其它书吧。 18 第 4 章 SQL 本章介绍 SQL 的基本内容和 SQLite 的特殊实现。本章内容的编排假设你没有 SQL 和关系 模型的基础知识。如果你是 SQL 新手,SQLite 将带你进入关系模型的精彩世界。 空注:使用过很多种数据库,所以本章只关注 SQLite 与其它 DBMS 不同的地方,如弱类型 什么的。 关系模型 如第 3 章所述,SQL 是关系模型的产物,关系模型是由 E. F. Codd 在 1969 年提出的。关系 模型要求关系型数据库能够提供一种查询语言,几年后,SQL 应运而生。 关系模型由三部分构成:表单(form)、功能(function)和一致性(consistency)。表单表示信息的 结构。在关系模型中只使用一种单独的数据结构来表达所有信息,这种结构称为关系 (relation,在 SQL 中被称为表、table)。关系由多个元组(tuples,在 SQL 中被称为行、记录、 rows)构成,每个元组又由多个属性(attributes,在 SQL 中被称为列、字段、columns)构成。 查询语言 查询语言将外部世界和数据的逻辑表现联系在一起,并使它们能够交互。它 提 供 了 取 数据和 修改数据的途径,是关系模型的动态部分。 SQL 的发展 第一个被采用的此类查询语言可能是在 IBM 的 System R 当中。System R 是一个关系型数据 库的研究项目,此项目直接派生出了 Codd 的论文。这个语言开始时被称作 SEQUEL,是 “Structured English Query Language”的缩写。后来被缩短为 SQL,或“Structured Query Language”。 示例数据库 示例数据库在本章和后面的章节中将会用到,其中存储了 Seinfeld 所有 episode(约 180 个) 的食品(约 412 种)。数据库中的表如图 4-1 所示。 图 4-1 Seinfeld 食品数据库 数据库的 schema 定义如下: 19 create table episodes ( id integer primary key, season int, name text ); create table foods( id integer primary key, type_id integer, name text ); create table food_types( id integer primary key, name text ); create table foods_episodes( food_id integer, episode_id integer ); 主表是 foods。foods 中的每条记录代表一种食品,其名称存储于 name 字段。type_id 参照 food_types,food_types 表存储了食品的分类(如烘烤食品、饮品、垃圾食品等)。foods_episodes 表是 foods 和 episodes 的关联表。 建立 示例数据库文件可在随书的 zip 文件中找到。 运行示例 为了您的方便,本章的所有 SQL 示例都保存在随书 zip 文件根目录的 sql.sql 文件中。 对于长 SQL 语句,一个方便的执行方法是将其复制到一个单独的文本文件,如 test.sql 中, 然后执行: sqlite3 foods.db < test.sql 为了增加输出的易读性,你应用把下面内容包含在文件中: .echo on .mode col .headers on .nullvalue NULL 语法 SQL 的语法很像自然语言。每个语句都是一个祈使句,以动词开头,表示所要做的动作。 后面跟的是主题和谓词,如图 4-2 所示。 图 4-2 一般的 SQL 语法结构 20 命令 SQL 由命令组成,每个命令以分号(;)结束。如下面是 3 个独立的命令: SELECT id, name FROM foods; INSERT INTO foods VALUES (NULL, 'Whataburger'); DELETE FROM foods WHERE id=413; 常量 也称为 Literals,表示确切的值,有 3 种:字符串常量、数据常量和二进制常量。字符串常 量如: 'Jerry' 'Newman' 'JujyFruit' 字符串值用单引号(')括起来,如果字符串中本身包含单引号,需要双写。如“Kenny’s chicken” 需要写成: 'Kenny''s chicken' 数字常量有整数、十进制数和科学记数法表示的数,如: -1 3.142 6.0221415E23 二进制值用如 x'0000'的表示法,其中每个数据是一个 16 进制数。二进制值必须由两个两个 的 16 进制数(8 bits)组成,如: x'01' X'0fff' x'0F0EFF' X'0f0effab' 保留字和标识符 保留字由 SQL 保留用做特殊的用途,如 SELECT、UPDATE、INSERT、CREATE、DROP 和 BEGIN 等。标识符指明数据库里的具体对象,如表或索引。保留字预定义,不能用做标 识符。SQL 不区分大小写,下面是相同的语句: SELECT * from foo; SeLeCt * FrOm FOO; 为清楚起见,本章中保留字都用大写,标识符都用小写。 但是,SQLite 对字符串的值是大小写敏感的。 注释 SQL 中单行注释用双减号开始,多行注释采用 C 风格的/* */形式。 21 创建一个数据库 数据库中所有的工作都围绕表进行。表由行和列组成,看起来简单,但其实并非如此。表跟 其它所有的概念有关,涉及本章的大部分篇幅。在此我们用 2 分钟的时间给出一个预览。 创建表 在 SQL 中,创建和删除数据库对象的语句一般被称为数据定义语言(data definition language, DDL),操作这些对象中数据的语句称为数据操作语言(data manipulation language,DML)。 创建表的语句属于 DDL,用 CREATE TABLE 命令,如下定义: CREATE [TEMP] TABLE table_name (column_definitions [, constraints]); 用TEMP 或 TEMPORARY 保留字声明的表为临时表,只存活于当前会话,一旦连接断开, 就会被自动删除。 中括号表示可选项。 另外,竖线表示在多个中选一,如: CREATE [TEMP|TEMPORARY] TABLE … ; 如果没有指明创建临时表,则创建的是基本表,将会在数据库中持久存在。 数据库中还有其它类型的表,如系统表和视图,现在先不介绍。 CREATE TABLE 命令至少需要一个表名和一个字段名。命令中 table_name 表示表名,必须 与其它所有的标识符不同。column_definitions 表示一个用逗号分隔的字段列表。每个字段定 义包括一个名称、一个域和一个逗号分隔的字段约束表。“ 域 ”一般情况下是一个类型,与 编程语言中的数据类型同名,指明存储在该列的数据的类型。在 SQLite 中有 5 种本地类型: INTEGER、REAL、TEXT、BLOB 和 NULL,所有这些域将在本章后面的“存储类”一节 中介绍。“ 约 束 ” 用 来 控 制 什 么 样 的 值 可以存储在表中或特定的字段中。例如,你可以用 UNIQUE 约束来规定所有记录中某个字段的值要各不相同。约束将会在“数据完整性”一 节中介绍。 在字段列表后面,可以跟随一个附加的字段约束,如下例: CREATE TABLE contacts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL COLLATE NOCASE, phone TEXT NOT NULL DEFAULT 'UNKNOWN', UNIQUE (name,phone) ); 改变表 你可以用 ALTER TABLE 命令改变表的结构。SQLite 版的 ALTER TABLE 命令既可以改变 表名,也可以增加字段。一般格式为: ALTER TABLE table { RENAME TO name | ADD COLUMN column_def } 注意这里又出现了新的符号{}。花括号括起来一个选项列表,必须从各选项中选择一个。此 处,我 们 或 者 ALTER TABLE table RENAME…,或者 ALTERTABLE table ADD COLUMN…。 That is, you can either rename the table using the RENAME clause, or add a column with the ADDCOLUMN clause. To rename a table, you simply provide the new name given by name. If you add a column, the column definition, denoted by column_def, follows the form in the 22 CREATE TABLE statement. It is a name, followed by an optional domain and list of constraints. 例如: sqlite> ALTER TABLE contacts ADD COLUMN email TEXT NOT NULL DEFAULT '' COLLATE NOCASE; sqlite> .schema contacts CREATE TABLE contacts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL COLLATE NOCASE, phone TEXT NOT NULL DEFAULT 'UNKNOWN', email TEXT NOT NULL DEFAULT '' COLLATE NOCASE, UNIQUE (name,phone) ); 显示了当前的表定义。 表还可以由 SELECT 语句创建,你可以在创建表结构的同时创建数据。这 种 特 别的 CREATE TABLE 语句将在“插入记录”一节中介绍。 在数据库中查询 SELECT 是 SQL 命令中最大最复杂的命令。SELECT 的很多操作都来源于关系代数。 关系操作 SELECT 中使用 3 大类 13 种关系操作: . 基本的操作 . Restriction(限制) . Projection . Cartesian Product(笛卡尔积) . Union(联合) . Difference(差) . Rename(重命名) . 附加的操作 . Intersection(交叉) . Natural Join(自然连接) . Assign(指派 OR 赋值) . 扩展的操作 . Generalized Projection . Left Outer Join . Right Outer Join . Full Outer Join 基本的关系操作,除重命名外,在 集 合 论 中 都 有 相 应 的 理 论 基 础 。附加操作是为了方便, 它 们可以用基本操作来完成,一般情况下,附加操作可以作为常用基本操作序列的快捷方式。 扩展操作为基本操作和附加操作增加特性。 ANSI SQL 的 SELECT 可以完成上述所有的关系操作。这些操作覆盖了 Codd 最初定义的所 有关系运算符,只有一个例外——divide。SQLite 支持 ANSI SQL 中除 right 和 full outer join 之外的所有操作(这些操作可用其它间接的方法完成)。 23 操作管道 从语法上来说,SELECT 命令用一系列子句将很多关系操作组合在一起。每个子句代表一种 特定的关系操作。几乎所有这些子句都是可选的,你可以只选你所需要的操作。 SELECT 是一个很大的命令。下面是 SELECT 的一个简单形式: SELECT DISTINCT heading FROM tables WHERE predicate GROUP BY columns HAVING predicate ORDER BY columns LIMIT count,offset; 每个保留字——DISTINCT、FROM、WHERE 和 HAVING——都是一个单独的子句。每个 子句由保留字和跟随的参数构成。 表 4-1 SELECT 的子句 编号 子句 操作 输入 1 FROM Join List of tables 2 WHERE Restriction Logical predicate 3 ORDER BY List of columns 4 GROUP BY Restriction List of columns 5 HAVING Restriction Logical predicate 6 SELECT Restriction List of columns or expressions 7 DISTINCT Restriction List of columns 8 LIMIT Restriction Integer value 9 OFFSET Restriction Integer value 图 4-3 SELECT phases 过滤 如果 SELECT 是 SQL 中最复杂的命令,那么 WHERE 就是 SELECT 中最复杂的子句。 值 “值”可以按它们所属的域(或类型)来分类,如数字值(1, 2, 3, etc.)或字符串值(“Jujy-Fruit”)。 值可以表现为文字的值(1, 2, 3 or “JujyFruit”)、变量(一般是如 foods.name 的列名)、表达式 (3+2/5)或函数的结果(COUNT(foods.name))值。 24 操作符 操作符使用一个或多个值做为输入并产生一个新值做为输出。这所以叫“操作符”是因为它 完成某种操作并产生某种结果。二目操作符操作两个输入值(或称操作数),三目操作符操作 三个操作数,单目操作符操作一个操作数,等等。 图 4-7 单目、二目和三目操作符 二目操作符 二目操作符是最常用的 SQL 操作符。表 4-2 列出了 SQLite 所支持的二目操作符。表中按优 先级从高到低的次序排列,同色的一组中具有相同的优先级,圆括号可以覆盖原有的优先级。 表 4-2 二目操作符 操作符 类型 作用 || String Concatenation * Arithmetic Multiply / Arithmetic Divide % Arithmetic Modulus + Arithmetic Add – Arithmetic Subtract << Bitwise Right shift >> Bitwise Left shift & Logical And | Logical Or < Relational Less than <= Relational Less than or equal to > Relational Greater than >= Relational Greater than or equal to = Relational Equal to == Relational Equal to <> Relational Not equal to != Relational Not equal to IN Logical In AND Logical And OR Logical Or LIKE Relational String matching 25 GLOB Relational Filename matching LIKE 操作符 一个很有用的关系操作符是 LIKE。LIKE 的作用与相等(=)很像,但却是通过一个模板来进 行字符串匹配。例如,要查询所有名称以字符“J”开始的食品,可使用如下语句: sqlite> SELECT id, name FROM foods WHERE name LIKE 'J%'; id name 156 Juice box 236 Juicy Fruit Gum 243 Jello with Bananas 244 JujyFruit 245 Junior Mints 370 Jambalaya 模板中的百分号(%)可与任意 0 到多个字符匹配。下划线(_)可与任意单个字符匹配。 sqlite> SELECT id, name FROM foods WHERE name LIKE '%ac%P%'; id name 127 Guacamole Dip 168 Peach Schnapps 198 Mackinaw Peaches 另一个有用的窍门是使用 NOT: sqlite> SELECT id, name FROM foods WHERE name like '%ac%P%' AND name NOT LIKE '%Sch%' id name 38 Pie (Blackberry) Pie 127 Guacamole Dip 198 Mackinaw peaches 限定和排序 可以用 LIMIT 和 OFFSET 保留字限定结果集的大小和范围。LIMIT 指定返回记录的最大数 量。OFFSET 指定偏移的记录数。例如,下面的命令返回 food_types 表中 id 排第 2 的记录: SELECT * FROM food_types LIMIT 1 OFFSET 1 ORDER BY id; 保留字OFFSET 在结果集中跳过一行(Bakery),保留字 LIMIT 限制最多返回一行(Cereal)。 上面语句中还有一个 ORDER BY 子句,它 使 记 录 集 在 返 回之前按一个或多个字段的值排序。 例如: sqlite> SELECT * FROM foods WHERE name LIKE 'B%' ORDER BY type_id DESC, name LIMIT 10; id type_id name 382 15 Baked Beans 383 15 Baked Potato w/Sour 384 15 Big Salad 385 15 Broccoli 26 362 14 Bouillabaisse 328 12 BLT 327 12 Bacon Club (no turke 326 12 Bologna 329 12 Brisket Sandwich 274 10 Bacon 函数(Function)和聚合(Aggregate) SQLite 提供了多种内置的函数和聚合,可以用在不同的子句中。函数的种类包括:数学函 数,如 ABS()计算绝对值;字符串格式函数,如 UPPER()和 LOWER(),它们将字符串的值 转化为大写或小写。例如: sqlite> SELECT UPPER('hello newman'), LENGTH('hello newman'), ABS(-12); UPPER('hello newman') LENGTH('hello newman') ABS(-12) HELLO NEWMAN 12 12 函数名是不分大小写的(或 upper()和 UPPER()是同一个函数)。函数可以接受字段值作为参 数: sqlite> SELECT id, UPPER(name), LENGTH(name) FROM foods WHERE type_id=1 LIMIT 10; id UPPER(name) LENGTH(name) ----------- 1 BAGELS 6 2 BAGELS, RAISIN 14 3 BAVARIAN CREAM PIE 18 4 BEAR CLAWS 10 5 BLACK AND WHITE COOKIES 23 6 BREAD (WITH NUTS) 17 7 BUTTERFINGERS 13 8 CARROT CAKE 11 9 CHIPS AHOY COOKIES 18 10 CHOCOLATE BOBKA 15 因为函数可以是任意表达式的一部分,所以函数也可以用在 WHERE 子句中: sqlite> SELECT id, UPPER(name), LENGTH(name) FROM foods WHERE LENGTH(name) < 5 LIMIT 5; id upper(name) length(name) 36PIE 3 48 BRAN 4 56KIX 3 57 LIFE 4 80 DUCK 4 聚合是一类特殊的函数,它 从 一 组记录中计算聚合值。标准的聚合函数包括 SUM()、AVG()、 COUNT()、MIN()和 MAX()。例如,要得到烘烤食品(type_id=1)的数量,可使用如下语句: sqlite> SELECT COUNT(*) FROM foods WHERE type_id=1; count 27 47 分组(Grouping) 聚合的精华部分是分组。聚合不只是能够计算整个结果集的聚合值,你还可以把结果集分成 多个组,然后计算每个组的聚合值。这 些 都 可以在一步当中完成,方法就是使用 GROUP BY 子句,如: sqlite> SELECT type_id FROM foods GROUP BY type_id; type_id 1 2 3 . . . 15 去掉重复 操作管道中的下一个限制是 DISTINCT。DISTINCT 处理 SELECT 的结果并过滤掉其中重复 的行。例如,你想从 foods 表中取得所有不同的 type_id 值: sqlite> SELECT DISTINCT type_id FROM foods; type_id 1 2 3 . . . 15 多表连接 连接(join)是 SELECT 命令的第一个操作,它产生初始的信息,供语句的其它部分过滤和处 理。连接的结果是一个合成的关系(或表),它是 SELECT 后继操作的输入。 也许从一个例子开始是最简单的。 sqlite> SELECT foods.name, food_types.name FROM foods, food_types WHERE foods.type_id=food_types.id LIMIT 10; name name Bagels Bakery Bagels, raisin Bakery 28 Bavarian Cream Pie Bakery Bear Claws Bakery Black and White cookies Bakery Bread (with nuts) Bakery Butterfingers Bakery Carrot Cake Bakery Chips Ahoy Cookies Bakery Chocolate Bobka Bakery 名称和别名 当把多个表连接在一起时,字段可能重名。 SELECT B.name FROM A JOIN B USING (a); 修改数据 跟 SELECT 命令相比,用于修改数据的语句就太简单太容易理解了。有 3 个 DML 语句用于 修改数据——INSERT、UPDATE 和 DELETE。 插入记录 使用 INSERT 命令向表中插入记录。使用 INSERT 命令可以一次插入 1 条记录,也可以使用 SELECT 命令一次插入多条记录。INSERT 语句的一般格式为: INSERT INTO table (column_list) VALUES (value_list); Table 指明数据插入到哪个表中。column_list 是用逗号分隔的字段名表,这些字段必须是表 中存在的。value_list 是用逗号分隔的值表,这些值与 column_list 中的字段一一对应。例如, 下面语句向 foods 表插入数据: sqlite> INSERT INTO foods (name, type_id) VALUES ('Cinnamon Bobka', 1); 修改记录 UPDATE 命令用于修改一个表中的记录。UPDATE 命令可以修改一个表中一行或多行中的 一个或多个字段。UPDATE 语句的一般格式为: UPDATE table SET update_list WHERE predicate; update_list 是一个或多个“字段赋值”的列表,字段赋值的格式为 column_name=value。 WHERE 子句的用法与 SELECT 语句相同,确定需要进行修改的记录。如: UPDATE foods SET name='CHOCOLATE BOBKA' WHERE name='Chocolate Bobka'; SELECT * FROM foods WHERE name LIKE 'CHOCOLATE%'; id type_ name 10 1 CHOCOLATE BOBKA 11 1 Chocolate Eclairs 29 12 1 Chocolate Cream Pie 222 9 Chocolates, box of 223 9 Chocolate Chip Mint 224 9 Chocolate Covered Cherries 删除记录 DELETE 用于删除一个表中的记录。DELETE 语句的一般格式为: DELETE FROM table WHERE predicate; 同样,WHERE 子句的用法与 SELECT 语句相同,确定需要被删除的记录。如: DELETE FROM foods WHERE name='CHOCOLATE BOBKA'; 数据完整性 数据完整性用于定义和保护表内部或表之间数据的关系。有四种完整性:域完整性、实体完 整性、参照完整性和用户定义完整性。 实体完整性 唯一约束 因为唯一(UNIQUE)约束是主键的基础,所以先介绍它。一个唯一约束要求一个字段或一组 字段的所有值互不相同,或者说唯一。如果你试图插入一个重复值,或将一个值改成一个已 存在的值,数据库将引发一个约束非法,并取消操作。唯一约束可以在字段级或表级定义。 NULL 和 UNIQUE: 问题:如果一个字段已经声明为 UNIQUE,可以向这个字段插入多少个 NULL 值? 回答:与 数据库的种类有关。PostgreSQL 和 Oracle 可以插入多个。Informix 和 Microsoft SQL Server 只能一个。DB2、SQL Anywhere 和 Borland Inter-Base不能。SQLite 采用了与 PostgreSQL 和 Oracle 相同的解决方案。 另一个困扰大家的关于 NULL 的经典问题是:两个 NULL 值是否相等?你没有足够的信息 来证明它们相等,但也没有足够的信息证明它们不等。SQLite 的观点是假设所有的 NULL 都是不同的。所以你可以向唯一字段中插入任意多个 NULL 值。 主键约束 在 SQLite 中,当你定义一个表时总要确定一个主键,不管你自己有没有定义。这个字段是 一个 64-bit 整型字段,称为 ROWID。它还有两个别名——_ROWID_和 OID,用这两个别名 同样可以取到它的值。它的默认取值按照增序自动生成。SQLite 为主键字段提供自动增长 特性。 30 域完整性 默认值 保留字 DEFAULT 为字段提供一个默认值。如果用 INSERT 语句插入记录时没有为该定做指 定值,则为它赋默认值。DEFAULT 不是一个约束(constraint),因为它没有强制任何事情。 这所以把它归为域完整性,是因为它提供了处理 NULL 值的一个策略。如果一个字段没有 指定默认址,在插入时也没有为该字段指定值,SQLite 将向该字段插入一个 NULL。例如, contacts.name 字段有一个默认值'UNKNOWN',请看下面例子: sqlite> INSERT INTO contacts (name) VALUES ('Jerry'); sqlite> SELECT * FROM contacts; id name phone Jerry UNKNOWN DEFAULT 还可以接受 3 种预定义格式的 ANSI/ISO 预定字用于生成日期和时间值。 CURRENT_TIME 将会生成 ANSI/ISO 格式(HH:MM:SS)的当前时间。CURRENT_DATE 会 生成当前日期(格式为 YYYY-MM-DD)。CURRENT_TIMESTAMP 会生成一个日期时间的组 合(格式为 YYYY-MM-DD HH:MM:SS)。例如: CREATE TABLE times ( id int, date NOT NULL DEFAULT CURRENT_DATE, time NOT NULL DEFAULT CURRENT_TIME, timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); INSERT INTO times(1); INSERT INTO times(2); SELECT * FROMS times; id date time timestamp 1 2006-03-15 23:30:25 2006-03-15 23:30:25 2 2006-03-15 23:30:40 2006-03-15 23:30:40 NOT NULL 约束 CHECK 约束 排序法(Collation) 排序法定义如何唯一地确定文本的值。排序法主要用于规定文本值如何进行比较。不 同 的 排 序 法 有不同的比较方法。例 如 ,某 种排序法是大小写不敏感的,于是'JujyFruit'和'JUJYFRUIT' 被认为是相等的。另外一个排序法或许是大小写敏感的,这时上面两个字符串就不相等了。 SQLite 有 3 种内置的排序法。默认为 BINARY,它 使用一个 C 函数 memcmp()来对文本进行 逐字节的比较。这很适合于大多数西方语言,如英语。NOCASE 对 26 个字母是大小写不敏 感的。Finally there is REVERSE, which is the reverse of the BINARY collation. REVERSE is 31 more for testing (and perhaps illustration) than anything else. SQLite C API 提供了一种创建定制排序法的手段,详见第 7 章。 存储类(Storage Classes) 如前文所述,SQLite 在处理数据类型时与其它的数据库不同。区别在于它所支持的类型以 及这些类型是如何存储、比较、强化(enforc)和指派(assign)。下面各节介绍 SQLite 处理数据 类型的独特方法和它与域完整性的关系。 对于数据类型,SQLite 的域完整性被称为域亲和性(affinity)更合适。在 SQLite 中,它被称 为类型亲和性(type affinity)。为了理解类型亲和性,你必须先要理解存储类和弱类型(manifest typing)。 SQLite 有 5 个原始的数据类型,被称为存储类。存储类这个词表明了一个值在磁盘上存储 的格式,其实就是类型或数据类型的同义词。这 5 个存储类在表 4-6 中描述。 表 4-6 SQLite 存储类 名称 说明 INTEGER 整数值是全数字(包括正和负)。整数可以是 1, 2, 3, 4, 6 或 8字节。整数的 最大范围(8 bytes)是{-9223372036854775808, 0, +9223372036854775807}。 SQLite 根据数字的值自动控制整数所占的字节数。 空注:参可变长整数的概念。 REAL 实数是 10 进制的数值。SQLite 使用 8 字节的符点数来存储实数。 TEXT 文本(TEXT)是字符数据。SQLite 支持几种字符编码,包括UTF-8 和 UTF-16。 字符串的大小没有限制。 BLOB 二进制大对象(BLOB)是任意类型的数据。BLOB 的大小没有限制。 NULL NULL 表示没有值。SQLite 具有对 NULL 的完全支持。 SQLite 通过值的表示法来判断其类型,下面就是 SQLite 的推理方法: l SQL 语句中用单引号或双引号括起来的文字被指派为 TEXT。 l 如果文字是未用引号括起来的数据,并且没有小数点和指数,被指派为 INTEGER。 l 如果文字是未用引号括起来的数据,并且带有小数点或指数,被指派为 REAL。 l 用 NULL 说明的值被指派为 NULL 存储类。 l 如果一个值的格式为 X'ABCD',其中 ABCD 为 16 进制数字,则该值被指派为 BLOB。 X 前缀大小写皆可。 SQL 函数 typeof()根据值的表示法返回其存储类。使用这个函数,下 面 SQL 语句返回的结果 为: sqlite> select typeof(3.14), typeof('3.14'), typeof(314), typeof(x'3142'), typeof(NULL); typeof(3.14) typeof('3.14') typeof(314) typeof(x'3142') typeof(NULL) real text integer blob null SQLite 单独的一个字段可能包含不同存储类的值。请看下面的示例: sqlite> DROP TABLE domain; sqlite> CREATE TABLE domain(x); sqlite> INSERT INTO domain VALUES (3.142); sqlite> INSERT INTO domain VALUES ('3.142'); sqlite> INSERT INTO domain VALUES (3142); 32 sqlite> INSERT INTO domain VALUES (x'3142'); sqlite> INSERT INTO domain VALUES (NULL); sqlite> SELECT ROWID, x, typeof(x) FROM domain; 返回结果为: rowid x typeof(x) 1 3.142 real 2 3.142 text 3 3142 integer 4 1B blob 5 NULL null 这带来一些问题。这种字段中的值如何存储和比较?如何对一个包含了 INTEGER、REAL、 TEXT、BLOB 和 NULL 值的字段排序?一个整数和一个 BLOB 如何比较?哪个更大?它们 能相等吗? 答案是:具有不同存储类的值可以存储在同一个字段中。可以被排序,因为这些值可以相互 比较。有完善定义的规则来做这件事。不同存储类的值可以通过它们各自类的“类值”进行 排序,定义如下: 1. NULL 存储类具有最低的类值。一个具有 NULL 存储类的值比所有其它值都小(包括其它 具有 NULL 存储类的值)。在 NULL 值之间,没有特别的可排序值。 2. INTEGER 或 REAL 存储类值高于 NULL,它们的类值相等。INTEGER 值和 REAL 值通 过其数值进行比较。 3. TEXT 存储类的值比 INTEGER 和 REAL 高。数值永远比字符串的值低。当两个 TEXT 值 进行比较时,其值大小由“排序法”决定。 4. BLOB 存储类具有最高的类值。具有 BLOB 类的值大于其它所有类的值。BLOB 值之间在 比较时使用 C 函数 memcmp()。 所以,当 SQLite 对一个字段进行排序时,首先按存储类排序,然后再进行类内的排序 (NULL 类内部各值不必排序) 。下面的 SQL 说明了存储类值的不同: sqlite> SELECT 3 < 3.142, 3.142 < '3.142', '3.142' < x'3000', x'3000' < x'3001'; 返回: 3 < 3.142 3.142 < '3.142' '3.142' < x'3000' x'3000' < x'3001' 1 1 1 1 弱类型(manifest typing) SQLite 使用弱类型。 看下面的表: CREATE TABLE foo( x integer, y text, z real ); 向该表插入一条记录: INSERT INTO foo VALUES ('1', '1', '1'); 当SQLite 创建这条记录时,x、y 和 z 这 3 个字段中存储的是什么类型呢?答案是 INTEGER, TEXT 和 REAL。 再看下面例子: CREATE TABLE foo(x, y, z); 33 现在执行同样的插入语句: INSERT INTO foo VALUES ('1', '1', '1'); 现在,x、y 和 z 中存储的是什么类型呢?答案是 TEXT、TEXT 和 TEXT。 那么,是否 SQLite 的字段类型默认为 TEXT 呢?再看,还是第 2 个表,执行如下插入语句: INSERT INTO foo VALUES (1, 1.0, x'10'); 现在,x、y 和 z 中存储的是什么类型呢?答案是 INTEGER、REAL 和 BLOB。 如果你愿意,可以为 SQLite 的字段定义类型,这看起来跟其它数据库一样。但这不是必须 的,你可以尽管违反类型定义。这是因为在任何情况下,SQLite 都可以接受一个值并推断 它的类型。 总之,SQLite 的弱类型可表示为:1)字段可以有类型,2)类型可以通过值来推断。类型亲和 性介绍这两个规定如何相互关联。所谓类型亲和性就是在强类型(strict typing)和动态类型 (dynamic typing)之间的平衡艺术。 类型亲和性(Type Affinity) 在 SQLite 中,字段没有类型或域。当给一个字段声明了类型,该字段实际上仅仅具有了该 类型的新和性。声明类型和类型亲和性是两回事。类型亲和性预定 SQLite 用什么存储类在 字段中存储值。在存储一个给定的值时到底 SQLite 会在该字段中用什么存储类决定于值的 存储类和字段亲和性的结合。我们先来介绍一下字段如何获得它的亲和性。 字段类型和亲和性 首先,每个字段都具有一种亲和性。共有四种亲和性:NUMERIC、INTEGER、TEXT 和 NONE。一个字段的亲和性由它预声明的类型决定。所以,当你为字段声明了类型,从根本 上说是为字段指定了亲和性。SQLite 按下面的规则为字段指派亲和性: l 默认的,一个字段默认的亲和性是 NUMERIC。如果一个字段不是 INTEGER、TEXT 或 NONE 的,那它自动地被指派为 NUMERIC 亲和性。 l 如果为字段声明的类型中包含了'INT'(无论大小写),该字段被指派为 INTEGER 亲和性。 l 如果为字段声明的类型中包含了'CHAR'、'CLOB'或'TEXT'(无论大小写),该字段被指派 为 TEXT 亲和性。如'VARCHAR'包含了'CHAR',所以被指派为 TEXT 亲和性。 l 如果为字段声明的类型中包含了'BLOB'(无论大小写),或者没有为该字段声明类型,该 字段被指派为 NONE 亲和性。 注意:如果没有为字段声明类型,该字段的亲和性为 NONE,在这种情况下,所有的值都将 以它们本身的(或从它们的表示法中推断的)存储类存储。如果你暂时还不确定要往一个字段 里放什么内容,或准备将来修改,用 NONE 亲和性是一个好的选择。但 SQLite 默认的亲和 性是 NUMERIC。例如,如果为一定字段声明了类型 JUJYFRUIT,该字段的亲和性不是 NONE,因为 SQLite 不认识这种类型,会给它指派默认的 NUMERIC 亲和性。所以,与其 用一个不认识的类型最终得到 NUMERIC 亲和性,还不如不为它指定类型,从而使它得到 NONE 亲和性。 34 亲和性和存储 亲和性对值如何存储到字段有影响,规则如下: l 一个 NUMERIC 字段可能包括所有 5 种存储类。一个 NUMERIC 字段具有数字存储类 的偏好(INTEGER 和 REAL)。当一个 TEXT 值被插入到一个 NUMERIC 字段,将会试 图将其转化为 INTEGER 存储类;如果转化失败,将会试图将其转化为 REAL 存储类; 如果还是失败,将会用 TEXT 存储类来存储。 l 一个 INTEGER 字段的处理很像 NUMERIC 字段。一个 INTEGER 字段会将 REAL 值按 REAL存储类存储。也 就 是 说 ,如 果 这个REAL值没有小数部分,就会被转化为INTEGER 存储类。INTEGER 字段将会试着将 TEXT 值按 REAL 存储;如果转化失败,将会试图 将其转化为 INTEGER 存储类;如果还是失败,将会用 TEXT 存储类来存储。 l 一个 TEXT 字段将会把所有的 INTEGER 或 REAL 值转化为 TEXT。 l 一个 NONE 字段不试图做任何类型转化。所有值按它们本身的存储类存储。 l 没有字段试图向 NULL 或 BLOB 值转化——如无论用什么亲和性。NULL 和 BLOB 值 永远都按本来的方式存储在所有字段。 这些规则初看起来比较复杂,但总的设计目标很简单,就是:如果你需要,SQLite 会尽量 模仿其它的关系型数据库。也就是说,如果你将 SQLite 看成是一个传统数据库,类型亲和 性将会按你的期望来存储值。如果你声明了一个 INTEGER 字段,并向里面放一个整数,就 会按整数来存储。如果你声明了一个具有 TEXT, CHAR 或 VARCHAR 类型的字段并向里放 一个整数,整数将会转化为 TEXT。可是,如果你不遵守这些规定,SQLite 也会找到办法来 存储你的值。 亲和性的运行 让我们看一些例子来了解亲和性是如何工作的: sqlite> CREATE TABLE domain(i int, n numeric, t text, b blob); sqlite> INSERT INTO domain VALUES (3.142,3.142,3.142,3.142); sqlite> INSERT INTO domain VALUES ('3.142','3.142','3.142','3.142'); sqlite> INSERT INTO domain VALUES (3142,3142,3142,3142); sqlite> INSERT INTO domain VALUES (x'3142',x'3142',x'3142',x'3142'); sqlite> INSERT INTO domain VALUES (null,null,null,null); sqlite> SELECT ROWID,typeof(i),typeof(n),typeof(t),typeof(b) FROM domain; 返回: rowid typeof(i) typeof(n) typeof(t) typeof(b) 1 real real text real 2 real real text text 3 integer integer text integer 4 blob blob blob blob 5 null null null null 下面的SQL 说明存储类的排序情况: sqlite> SELECT ROWID, b, typeof(b) FROM domain ORDER BY b; 返回: rowid b typeof(b) 35 5 NULL null 1 3.142 real 3 3142 integer 2 3.142 text 4 1B blob sqlite> SELECT ROWID, b, typeof(b), b<1000 FROM domain ORDER BY b; 返回: rowid b typeof(b) b<1000 NULL null NULL 1 3.142 real 1 3 3142 integer 1 2 3.142 text 0 4 1B blob 0 存储类和类型转换 关于存储类,需要关注的另一件事是:存储类有时会影响到值如何进行比较。特别是 SQLite 有时在进行比较之前,会将值在数字存储类(INTEGER 和 REAL)和 TEXT 之间进行转换。 为进行二进制的比较,遵循如下规则: l 当一个字段值与一个表达式的结果进行比较,字 段 的 亲 和 性 会 在 比较之前应用于表达式 的结果。 l 当两个字段值进行比较,如果一个字段拥有 INTEGER 或 NUMERIC 亲和性而另一个没 有,NUMERIC 亲和性会应用于非 NUMERIC 字段的 TEXT 值。 l 当两个表达式进行比较,SQLite 不做任何转换。如果两个表达式有相似的存储类,则 直接按它们的值进行比较;否则按类值进行比较。 请看下面例子: sqlite> select ROWID,b,typeof(i),i>'2.9' from domain ORDER BY b; rowid b typeof(i i>'2.9' 5 NULL null NULL 1 3.142 real 1 3 3142 integer 1 2 3.142 real 1 4 1B blob 1 也算是“强类型(STRICT TYPING)” 如果你需要比类型亲和性更强的域完整性,可以使用 CHECK 约束。你可以使用一个单独的 内置函数和一个 CHECK 约束来实现一个“假的”强类型。 事务 事务定义了一组 SQL 命令的边界,这 组 命令或者作为一个整体被全部执行,或者都不执行。 36 事务的典型实例是转帐。 事务的范围 事务由 3 个命令控制:BEGIN、COMMIT 和 ROLLBACK。BEGIN 开始一个事务,之后的 所有操作都可以取消。COMMIT 使 BEGIN 后的所有命令得到确认;而 ROLLBACK 还原 BEGIN 之后的所有操作。如: sqlite> BEGIN; sqlite> DELETE FROM foods; sqlite> ROLLBACK; sqlite> SELECT COUNT(*) FROM foods; COUNT(*) 412 上面开始了一个事务,先删除了 foods 表的所有行,但是又用 ROLLBACK 进行了回卷。再 执行 SELECT 时发现表中没发生任何改变。 SQLite 默认情况下,每条 SQL 语句自成事务(自动提交模式)。 冲突解决 如前所述,违反约束会导致事务的非法结束。大多数数据库(管理系统)都是简单地将前面所 做的修改全部取消。 SQLite 有其独特的方法来处理约束违反(或说从约束违反中恢复),被称为冲突解决。 如: sqlite> UPDATE foods SET id=800-id; SQL error: PRIMARY KEY must be unique SQLite 提供 5 种冲突解决方案:REPLACE、IGNORE、FAIL、ABORT 和 ROLLBACK。 l REPLACE: 当发违反了唯一完整性,SQLite 将造成这种违反的记录删除,替代以新插 入或修改的新记录,SQL 继续执行,不报错。 l IGNORE l FAIL l ABORT l ROLLBACK 数据库锁 在 SQLite 中,锁和事务是紧密联系的。为了有效地使用事务,需要了解一些关于如何加锁 的知识。 SQLite 采用粗放型的锁。当一个连接要写数据库,所有其它的连接被锁住,直到写连接结 束了它的事务。SQLite 有一个加锁表,来帮助不同的写数据库都能够在最后一刻再加锁, 以保证最大的并发性。 SQLite 使用锁逐步上升机制,为了写数据库,连接需要逐级地获得排它锁。SQLite 有 5 个 不同的锁状态:未加锁(UNLOCKED)、共享(SHARED)、保留(RESERVED)、未决(PENDING) 37 和排它(EXCLUSIVE)。每个数据库连接在同一时刻只能处于其中一个状态。每种状态(未加 锁状态除外)都有一种锁与之对应。 最初的状态是未加锁状态,在此状态下,连接还没有存取数据库。当连接到了一个数据库, 甚至已经用 BEGIN 开始了一个事务时,连接都还处于未加锁状态。 未加锁状态的下一个状态是共享状态。为了能够从数据库中读(不写)数据,连接必须首先进 入共享状态,也 就 是说 首先 要 获得 一个共享锁。多个连接可以同时获得并保持共享锁,也就 是说多个连接可以同时从同一个数据库中读数据。但哪怕只有一个共享锁还没有释放,也不 允许任何连接写数据库。 如果一个连接想要写数据库,它 必 须首先获得一个保留锁。一个数据库上同时只能有一个保 留锁。保留锁可以与共享锁共存,保留锁是写数据库的第 1 阶段。保留锁即不阻止其它拥有 共享锁的连接继续读数据库,也不阻止其它连接获得新的共享锁。 一旦一个连接获得了保留锁,它 就 可以开始处理数据库修改操作了,尽管这些修改只能在缓 冲区中进行,而不是实际地写到磁盘。对读出内容所做的修改保存在内存缓冲区中。 当连接想要提交修改(或事务)时,需要将保留锁提升为排它锁。为了得到排它锁,还必须首 先将保留锁提升为未决锁。获得未决锁之后,其它连接就不能再获得新的共享锁了,但已经 拥有共享锁的连接仍然可以继续正常读数据库。此时,拥有未决锁的连接等待其它拥有共享 锁的连接完成工作并释放其共享锁。 一旦所有其它共享锁都被释放,拥有未决锁的连接就可以将其锁提升至排它锁,此时就可以 自由地对数据库进行修改了。所有以前对缓冲区所做的修改都会被写到数据库文件。 死锁 为什么需要了解锁的机制呢?为了避免死锁。 考虑下面表 4-7 所假设的情况。两个连接——A 和 B——同时但完全独立地工作于同一个数 据库。A 执行第 1 条命令,B 执行第 2、3 条,等等。 表 4-7 一个死锁的假设情况 A 连接 B 连接 sqlite> BEGIN; sqlite> BEGIN; sqlite> INSERT INTO foo VALUES ('x'); sqlite> SELECT * FROM foo; sqlite> COMMIT; SQL error: database is locked sqlite> INSERT INTO foo VALUES ('x'); SQL error: database is locked 两个连接都在死锁中结束。B 首先尝试写数据库,也就拥有了一个未决锁。A 再试图写,但 当其 INSERT 语句试图将共享锁提升为保留锁时失败。 为了讨论的方便,假设连接 A 和 B 都一直等待数据库可写。那么此时,其它的连接甚至都 不能够再读数据库了,因为 B 拥有未决锁(它能阻止其它连接获得共享锁)。那么时此,不仅 A 和 B 不能工作,其它所有进程都不能再操作此数据库了。 如果避免此情况呢?当然不能让 A 和 B 通过谈判解决,因为它们甚至不知道彼此的存在。 答案是采用正确的事务类型来完成工作。 38 事务的种类 SQLite 有三种不同的事务,使用不同的锁状态。事务可以开始于:DEFERRED、MMEDIATE 或 EXCLUSIVE。事务类型在 BEGIN 命令中指定: BEGIN [ DEFERRED | IMMEDIATE | EXCLUSIVE ] TRANSACTION; 一个 DEFERRED 事务不获取任何锁(直到它需要锁的时候),BEGIN 语句本身也不会做什么 事情——它开始于 UNLOCK 状态。默认情况下就是这样的,如果仅仅用 BEGIN 开始一个 事务,那么事务就是 DEFERRED 的,同时它不会获取任何锁;当对数据库进行第一次读操 作时,它会获取 SHARED 锁;同样,当进行第一次写操作时,它会获取 RESERVED 锁。 由 BEGIN 开始的 IMMEDIATE 事务会尝试获取 RESERVED 锁。如果成功,BEGIN IMMEDIATE 保证没有别的连接可以写数据库。但是,别的连接可以对数据库进行读操作; 但是,RESERVED 锁会阻止其它连接的 BEGIN IMMEDIATE 或者 BEGIN EXCLUSIVE 命 令,当其它连接执行上述命令时,会返回 SQLITE_BUSY 错误。这时你就可以对数据库进 行修改操作了,但是你还不能提交,当你 COMMIT 时,会返回 SQLITE_BUSY 错误,这意 味着还有其它的读事务没有完成,得等它们执行完后才能提交事务。 EXCLUSIVE 事务会试着获取对数据库的 EXCLUSIVE 锁。这 与 IMMEDIATE 类似,但是一 旦成功,EXCLUSIVE 事务保证没有其它的连接,所以就可对数据库进行读写操作了。 上节那个例子的问题在于两个连接最终都想写数据库,但是它们都没有放弃各自原来的锁, 最终,SHARED 锁导致了问题的出现。如果两个连接都以 BEGIN IMMEDIATE 开始事务, 那么死锁就不会发生。在这种情况下,在 同 一时刻只能有一个连接进入BEGIN IMMEDIATE, 其它的连接就得等待。BEGIN IMMEDIATE 和 BEGIN EXCLUSIVE 通常被写事务使用。就 像同步机制一样,它防止了死锁的产生。 基本的准则是:如果你正在使用的数据库没有其它的连接,用 BEGIN 就足够了。但是,如 果你使用的数据库有其它的连接也会对数据库进行写操作,就得使用 BEGIN IMMEDIATE 或 BEGIN EXCLUSIVE 开始你的事务。 数据库管理 数据库管理用于控制数据库如何操作。从 SQL 的角度,数据库管理包括一些主题如会视图 (view)、触发器(trigger)和索引(indexe)。另外,SQLite 包括自己一些独特的管理,如数据库 pragma,可以用来配置数据库参数。 视图 物化的视图 在关系模型中称为数据可修改的视图。 39 索引 索引的利用 理解索引何时被利用及何时不被利用是重要的。SQLite 有明确的条件来决定是否使用索引。 如果可能,在 WHERE 子名中有下列表达式时,SQLite 将使用单字段索引: column {=|>|>=|<=|<} expression expression {=|>|>=|<=|<} column column IN (expression-list) column IN (subquery) 多字段索引的使用有很明确的条件。这最好用例子来说。假设你有如下定义的一个表: CREATE TABLE foo (a,b,c,d); 触发器 当特定的表上发生特定的数据库事件时,触发器会执行特定的 SQL 命令。创建触发器的一 般语法如下: CREATE [TEMP|TEMPORARY] TRIGGER name [BEFORE|AFTER] [INSERT|DELETE|UPDATE|UPDATE OF columns] ON table action UPDATE 触发器 不同于 INSERT and DELETE 触发器,UPDATE 触发器可以定义在一个表的特定的字段上。 The general form of this kind of trigger is as follows: CREATE TRIGGER name [BEFORE|AFTER] UPDATE OF column ON table action The following is a SQL script that shows an UPDATE trigger in action: .h on .m col .w 50 .echo on CREATE TEMP TABLE log(x); CREATE TEMP TRIGGER foods_update_log UPDATE of name ON foods BEGIN INSERT INTO log VALUES('updated foods: new name=' || NEW.name); END; BEGIN; UPDATE foods set name='JUJYFRUIT' where name='JujyFruit'; 40 SELECT * FROM log; ROLLBACK; 错误处理 定义一个事件的 before 触发器给了你一个阻止事件发生的机会。before 触发器可以实现新的 完整性约束。SQLite 为触发器提供了一个称为 RAISE()的特殊 SQL 函数,可以在触发器体 中唤起一个错误。RAISE 如下定义: RAISE(resolution, error_message); 使用触发器的外键约束 在 SQLite 中,触发器最有趣的应用之一是实现外键约束。为了进一步了解触发器,我将用 这个想法在 foods 表和 food_types 表之间实现外键。 附加(Attaching)数据库 SQLite 允许你用 ATTACH 命令将多个数据库“附加”到当前连接上来。当你附加了一个数 据库,它的所有内容在当前数据库文件的全局范围内都是可存取的。ATTACH 的语法为: ATTACH [DATABASE] filename AS database_name; 清洁数据库 SQLite 有两个命令用于数据库清洁——REINDEX 和 VACUUM。REINDEX 用于重建索引, 有两种形式: REINDEX collation_name; REINDEX table_name|index_name; 第一种形式利用给定的排序法名称重新建立所有的索引。 VACUUM 通过重建数据库文件来清除数据库内所有的未用空间。 数据库配置 SQLite 没有配置文件。所有这些配置参数都是用 pragma 来实现。Pragma 以独特的方式工作, 有些像变量,又有些像命令。 连接缓冲区大小 缓冲区尺寸 pragma 控制一个连接可以在内存中使用多少个数据库页。要查看当前缓冲区大 小的默认值,执行: sqlite> PRAGMA cache_size; 41 cache_size 2000 要改变缓冲区大小,执行: sqlite> PRAGMA cache_size=10000; sqlite> PRAGMA cache_size; cache_size 10000 获得数据库信息 可以使用数据库的 schema pragma 来获得数据库信息,定义如下: l database_list: Lists information about all attached databases. l index_info: Lists information about the columns within an index. It takes an index name as an argument. l index_list: Lists information about the indexes in a table. It takes a table name as an argument. l table_info: Lists information about all columns in a table. 请看下面示例: sqlite> PRAGMA database_list; seq name file 0 main /tmp/foods.db 2 db2 /tmp/db sqlite> CREATE INDEX foods_name_type_idx ON foods(name,type_id); sqlite> PRAGMA index_info(foods_name_type_idx); seqn cid name 0 2 name 1 1 type_id sqlite> PRAGMA index_list(foods); seq name unique 0 foods_name_type_idx 0 sqlite> PRAGMA table_info(foods); cid name type notn dflt pk 0 id integer 0 1 1 type_id integer 0 0 2 name text 0 0 页大小、编码和自动排空 The database page size, encoding, and autovacuuming must be set before a database is created. That is, in order to alter the defaults, you must first set these pragmas before creating any database 42 objects in a new database. The defaults are a 1,024-byte page size and UTF-8 encoding. SQLite supports page sizes ranging from 512 to 32,786 bytes, in powers of 2. Supported encodings are UTF-8, UTF-16le (little-endian UTF-16 encoding), and UTF-16be (big-endian UTF-16 encoding). 如果使用 auto_vacuum pragma,可以使数据库自动维持最小。一般情况下,当一个事务从数 据库中删除了数据并提交后,数据库文件的大小保持不变。当使用了 auto_vacuum pragma 后,当删除事务提交时,数据库文件会自动缩小。 系统表 sqlite_master 表是一个系统表,它包含数据库中所有表、视图、索引和触发器的信息。例如, foods 的当前内容如下: sqlite> SELECT type, name, rootpage FROM sqlite_master; type name rootpage table episodes 2 table foods 3 table foods_episodes 4 table food_types 5 index foods_name_idx 30 table sqlite_sequence 50 trigger foods_update_trg 0 trigger foods_insert_trg 0 trigger foods_delete_trg 0 有关sqlite_master 表的结构请参考第 2 章的“获得数据库的 Schema 信息”一节。 sqlite_master 包含一个称为 sql 的字段,存储了创建对象的 DDL 命令,如: sqlite> SELECT sql FROM sqlite_master WHERE name='foods_update_trg'; 返回: CREATE TRIGGER foods_update_trg BEFORE UPDATE OF type_id ON foods BEGIN SELECT CASE WHEN (SELECT id FROM food_types WHERE id=NEW.type_id) IS NULL THEN RAISE( ABORT, 'Foreign Key Violation: foods.type_id is not in food_types.id') END; END 查看 Query 的执行 可以用 EXPLAIN 命令查看 SQLite 执行一个查询的方法。EXPLAIN 列出一个 SQL 命令编 译后的 VDBE 程序。 sqlite> .m col sqlite> .h on sqlite> .w 4 15 3 3 3 10 3 43 sqlite> EXPLAIN SELECT * FROM foods; addr opcode p1 p2 p3 p4 p5 comment ---- --------------- --- --- --- ---------- --- ------- 0 Trace 0 0 0 00 1 Goto 0 11 0 00 2 OpenRead 0 7 0 3 00 3 Rewind 0 9 0 00 4 Rowid 0 1 0 00 5 Column 0 1 2 00 6 Column 0 2 3 00 7 ResultRow 1 3 0 00 8 Next 0 4 0 01 9 Close 0 0 0 00 10 Halt 0 0 0 00 11 Transaction 0 0 0 00 12 VerifyCookie 0 40 0 00 13 TableLock 0 7 0 foods 00 14 Goto 0 2 0 00 44 第 5 章 设计和概念 本章为后面的 3 章打下基础,这几章专注于 SQLite 编程。这几章专注于作为程序员,在编 码时你所应该了解的有关 SQLite 的东西。无论你是用 C 语言对 SQLite 进行编程,还是用其 它的编程语言,这些内容都是重要的。它不仅帮助你了解 API,还包括部分有关 SQLite 的 体系结构和实现方法的内容。具备了这些知识,你就可以编出更好的代码,这 些 代码执行得 更快,且不会产生死锁、不可预知错误等问题。你会看到 SQLite 如何处理你的代码,你还 会变得更加自信,因为你知道自己正前进在解决问题的正确方向上。 你不需要从头到尾地读内部代码才能理解这些内容,你也不必是一个 C 程序员。SQLite 的 设计和概念都是非常直观和容易理解的。只有一小部分内容你需要知道,本章就介绍这些内 容。 1.明显地,你需要知道 API 是如何工作的。于是本章从一个对 API 概念性的介绍开始,图 示了主要的数据结构,API 的一般设计和它主要的函数。还可以看到 SQLite 的一些主要的 子系统,这些子系统在查询的处理过程中起着重要作用。 2.除了知道什么函数做什么,你还需要从比 API 高的角度来看问题,看看所有这些函数在 事务(transactions)中是如何操作的。SQLite 的所有的工作都是在事务中完成的。于是,你需 要知道在 API 之下,事务如何按照锁的约束来工作。如果你不知道锁是如何操作的,这些 锁就会导致问题。当对锁有所了解之后,你不仅可以避免潜在的并发问题,还可以通过编程 控制它们来优化你的查询。 3.最后,你还必须理解如何将这些内容应用于编码。本章的最后部分会将 3 个主题结合在 一起——API、事务和锁,并且看一看好代码与坏代码的区别。 空注:本章看得还是比较仔细的,翻译的也比较全。 API 从功能的角度来区分,SQLite 的 API 可分为两类:核心 API 的扩充 API。核心 API 由所有 完成基本数据库操作的函数构成,包括:连接数据库、执行 SQL 和遍历结果集。它还包括 一些功能函数,用来完成字符串格式化、操作控制、调试和错误处理等任务。扩充 API 提 供不同的方法来扩展 SQLite,它 使 你 能 够 创 建自定义的 SQL 扩展,并与 SQLite 本身的 SQL 相集成。 SQLite 版本 3 的新特性 在开始之前,我们先讨论一下 SQLite 版本 3 的新特色: 一、首先,SQLite 的 API 被彻底重新设计了,并具有了许多新特性。由第二版的 15 个函数 增加到 88 个函数。这些函数包括支持 UTF-8 和 UTF-16 编码的功能函数。SQLite3 有一个 更方便的查询模式,使查询的预处理更容易并且支持新的参数绑定方法。SQLite3 还增加了 用户定义的排序序列、CHECK 约束、64 位的键值和新的查询优化。 二、在后端大大地改进了并发性能。加锁子系统引进了一种新的锁升级模型,解决了第二版 中的写进程饿死的问题。这种模型保证写进程按照先来先服务的算法得到排它锁(Exclusive 45 Lock)。甚至,写进程通过把结果写入临时缓冲区(Temporary Buffer),可以在得到排它锁之 前就开始工作。这对于写要求较高的应用,性能可提高 400%。 三、SQLite 3 包含一个改进了的 B-tree 模型。现在对库表使用 B+tree,大大提高查询效率, 存储大数据字段更有效,并可以从磁盘上删除不用了的字段。其结果是数据库文件的体积减 小了 25–35%并改善了全面性能。B+tree 将在第 9 章介绍。 四、SQLite 3 最重要的改变是它的存储模型。由第二版只支持文本模型,扩展到支持 5 种本 地数据类型,如第 4 章所介绍的,还 增 强 了 弱类型和类型亲和性的概念。每种类型都被优化, 以得到更高的查询性能并战用更少的存储空间。例如,整数和浮点数以二进制的形式存储, 而不再是以 ASCII 形式存储,这样,就不必再对 WHERE 子句中的值进行转换(像第 2 版那 样)。弱类型使你能够在定义一个字段时选择是否预声明类型。亲和性确定一个值存储于字 段的格式——基于值的表示法和列的亲和性。类型亲和性与弱类型紧密关联——列的亲和性 由其类型的声明确定。 在很多方面,SQLite 3 是一个与 SQLite 2 完全不同的数据库,并且提供了很多在适应性、特 色 和 性能 方面 的改 进。 主要的数据结构 在第 1 章你看到了很多 SQLite 组件——分词器、分析器和虚拟机等等。但是从程序员的角 度,最需要知道的是:connection、statements、B-tree 和 pager。它们之间的关系如图 5-1 所 示。这些对象构成了编写优秀代码所必须知道的 3 个首要内容:API、事务和锁。 图 5-1 SQLite C API 对象模型 从技术上来说,B-tree 和 pager 不是 API 的一部分,但是它们却在事务和锁上起着关键作用。 这里只介绍关联的内容,详细内容将在“事务”一节介绍。 46 连接(Connection)和语句(Statement) 连接(Connection)和语句(Statement)是执行 SQL 命令涉及的两个主要数据结构,几乎所有通 过 API 进行的操作都要用到它们。连接代表在一个独立的事务环境下的一个单独的数据库 连接。每个语句都和一个连接关联,通常表示一个编译过的 SQL 语句。在内部,它以 VDBE 字节码表示。语句包括执行一个命令所需要一切,包括保存 VDBE 程序执行状态所需的资 源,指向硬盘记录的 B-tree 游标,以及参数等等。 B-tree 和 Pager 一个连接可以有多个 database 对象——一个主数据库和附加的数据库。每一个数据库对象有 一个 B-tree 对象,一个 B-tree 有一个 pager 对象(这里的对象不是面向对象的“对象”,只是 为了说清楚问题)。 语句最终都是通过连接的 B-tree 和 pager 从数据库读或者写数据,通过 B-tree 的游标(cursor) 遍历存储在页(page)中的记录。在 游 标 访问页之前,页必须从磁盘加载到内存,而这就是 pager 的任务。任何时候,如果 B-tree 需要页,它都会请求 pager 从磁盘读取数据,pager 把页加 载到页缓冲区(page cache)。之后,B-tree 和与之关联的游标就可以访问位于页中的记录了。 如果游标改变了页,为了防止事务回滚,pager 必须采取特殊的方式保存原来的页。总的来 说,pager 负责读写数据库,管理内存缓存和页,以及管理事务、锁和崩溃恢复(这些在“事 务”一节会详细介绍)。 总之,关于连接和事务,你必须知道两件事:(1)对数据库的任何操作,一个连接存在于一 个事务之下。(2)一个连接绝不会同时存在于多个事务之下。无论何时,一个连接在对数据 库做任何操作时,都总是在恰好一个事务之下,不会多,也不会少。 核心 API 核心 API 主要与执行 SQL 命令有关。有两种方法执行 SQL 语句:预编译查询和封装查询。 预编译查询由三个阶段构成:准备(preparation)、执行 (execution)和定案(finalization)。其实封 闭装查询只是对预编译查询的三个过程进行了包装而已,最终也会转化为预编译查询来执 行。 连接的生命周期(The Connection Lifecycle) 和大多数据库连接相同,其生命周期由三个阶段构成: 1. 连接数据库(Connect to the database)。 2. 处理事务(Perform transactions):如你所知,任何命令都在事务下执行。默认情况下,事 务自动提交,也就是每一个 SQL 语句都在一个独立的事务下运行。当然也可以通过使用 BEGIN..COMMIT 手动提交事务。 3. 断开连接(Disconnect from the database):关闭数据库文件。还要关闭所有附加的数据库文 件。 在查询的处理过程中还包括其它一些行为,如处理错误、“忙”句柄和 schema 改变等,所有 47 这些都将在 utility functions 一节中介绍。 连接数据库(Connect to the database): 连接数据库不只是打开一个文件。每个 SQLite 数据库都存储在单独的操作系统文件中—— 数据库与文件一一对应。连接、打开数据库的 C API 为 sqlite3_open(),它只是一个简单的系 统调用,来打开一个文件,它的实现位于 main.c 文件中。 SQLite 还可以创建内存数据库。如果你使用:memory:或一个空字符串做数据库名,数据库将 在 RAM 中创建。内存数据库将只能被创建它的连接所存取,不能与其它连接共享。另外, 内存数据库只能存活于连接期间,一旦连接关闭,数据库就将从内存中被删除。 当连接一个位于磁盘上的数据库时,如果数据库文件存在,则打开该文件;如果不存在, SQLite 会假定你想创建一个新的数据库。在这种情况下,SQLite 不会立即在磁盘上创建一 个文件,只有当你向数据库写入数据时才会创建文件,比如:创建表、视图或者其它数据库 对象。如果你打开一个数据库,不做任何事,然后关闭它,SQLite 会创建一个文件,但只 是一个长度为 0 的空文件而已。 另外一个不立即创建新文件的原因是,一些数据库的参数,比如:编码,页大小等,只能在 数据库创建之前设置。默认情况下,页大小为 1024 字节,但是你可以选择 512-32768 字节 之间为 2 幂数的数字。有些时候,较大的页能更有效地处理大量的数据。你可以使用 page_size pragma 来设置数据库页大小。 字符编码是数据库的另一个永久设置。你可以使用 encoding pragma 来设置字符编码,其值 可以是 UTF-8、UTF-16、UTF-16le (little endian)和 UTF-16be (big endian)。 执行预处理查询 前面提到,预处理查询(Prepared Query)是 SQLite 执行所有 SQL 命令的方式,包括以下三个 步聚: (1) 准备(preparation): 分词器(tokenizer) 、分析器(parser)和代码生成器(code generator)把 SQL 语句编译成 VDBE 字节码,编译器会创建一个语句句柄(sqlite3_stmt),它包括字节码以及其它执行命令和遍历 结果集所需的全部资源。相应的 C API 为 sqlite3_prepare(),位于 prepare.c 文件中。 (2) 执行(execution): 虚拟机执行字节码,执行过程是一个步进(stepwise)的过程,每一步(step)由 sqlite3_step()启 动,并由 VDBE 执行一段字节码。当第一次调用 sqlite3_step()时,一般会获得一种锁,锁的 种类由命令要做什么(读或写)决定。对于 SELECT 语句,每次调用 sqlite3_step()使用语句句 柄的游标移到结果集的下一行。对于结果集中的每一行,它返回 SQLITE_ROW,当到达结 果末尾时,返回 SQLITE_DONE。对于其它 SQL 语句(INSERT、UPDATE、DELETE 等), 第一次调用 sqlite3_step()就导致 VDBE 执行整个命令。 (3) 定案(finalization): VDBE 关闭语句,释放资源。相应的 C API 为 sqlite3_finalize(),它导致 VDBE 结束程序运 行并关闭语句句柄。如果事务是由人工控制开始的,它必须由人工控制进行提交或回卷,否 则 sqlite3_finalize()会返回一个错误。当 sqlite3_finalize()执行成功,所有与语句对象关联的 资源都将被释放。在自动提交模式下,还会释放关联的数据库锁。 每一步(preparation、execution 和 finalization)都关联于语句句柄的一种状态(prepared、active 和 finalized)。Pepared 表示所有资源都已分配,语句已经可以执行,但还没有执行。现在还 48 没有申请锁,一直到调用 sqlite3_step()时才会申请锁。Active 状态开始于对 sqlite3_step()的 调用,此时语句正在被执行并拥有某种锁。Finalized 意味着语句已经被关闭且所有相关资源 已经被释放。通过图 5-2 可以更容易地理解该过程: 图 5-2 语句处理 下面代码例示了在 SQLite 上执行一个 query 的一般过程。 #include #include #include"sqlite3.h" #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; 49 //打开数据 rc=sqlite3_open("foods.db",&db); if(rc){ fprintf(stderr,"Can'topendatabase:%sn",sqlite3_errmsg(db)); 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",sqlite3_errmsg(db)); } rc=sqlite3_step(stmt); ncols=sqlite3_column_count(stmt); while(rc==SQLITE_ROW){ for(i=0;i #include #include "util.h" #pragma comment(lib, "sqlite3.lib") int main(int argc, char **argv) { sqlite3 *db; char *zErr; int rc; char *sql; rc = sqlite3_open("test.db", &db); if (rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } sql = "create table episodes( id integer primary key," 67 " name text, cid int)"; rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); if (rc != SQLITE_OK) { if (zErr != NULL) { fprintf(stderr, "SQL error: %s\n", zErr); sqlite3_free(zErr); } } sql = "insert into episodes (name,id) values ('Cinnamon Babka2',1)"; rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); if (rc != SQLITE_OK) { if (zErr != NULL) { fprintf(stderr, "SQL error: %s\n", zErr); sqlite3_free(zErr); } } sqlite3_close(db); return 0; } 处理记录 如第5章所述,还是有可能从 sqlite3_exec()取得记录的。sqlite3_exec()包含一个回叫(callback) 机制,提供了一种从 SELECT 语句得到结果的方法。这个机制由 sqlite3_exec()函数的第 3 和第 4 个参数实现。第 3 个参数是一个指向回叫函数的指针,如果提供了回叫函数,SQLite 则会在执行 SELECT 语句期间在遇到每一条记录时调用回叫函数。回叫函数的声明如下: typedef int (*sqlite3_callback)( void*, /* Data provided in the 4th argument of sqlite3_exec() */ int, /* The number of columns in row */ char**, /* An array of strings representing fields in the row */ char** /* An array of strings representing column names */ ); 函数sqlite3_exec()的第 4 个参数是一个指向任何应用程序指定的数据的指针,这个数据是你 准备提供给回叫函数使用的。SQLite 将把这个数据作为回叫函数的第 1 个参数传递。 总之,sqlite3_exec()允许你处理一批命令,并且你可以使用回叫函数来收集所有返回的数据。 例如,先向 episodes 表插入一条记录,再从中查询所有记录,所有这些都在一个 sqlite3_exec() 调用中完成。完整的程序代码见列表 6-2,它来自 exec.c。 68 列表 6-2 将 sqlite3_exec()用于记录处理 #include #include #include "util.h" #pragma comment(lib, "sqlite3.lib") int callback(void* data, int ncols, char** values, char** headers); int main(int argc, char **argv) { sqlite3 *db; int rc; char *sql; char *zErr; char* data; rc = sqlite3_open("test.db", &db); if(rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } data = "Callback function called"; sql = "insert into episodes (name, cid) values ('Mackinaw Peaches', 1);" "select * from episodes;"; rc = sqlite3_exec(db, sql, callback, data, &zErr); if(rc != SQLITE_OK) { if (zErr != NULL) { fprintf(stderr, "SQL error: %s\n", zErr); sqlite3_free(zErr); } } sqlite3_close(db); return 0; } int callback(void* data, int ncols, char** values, char** headers) { int i; 69 fprintf(stderr, "%s: ", (const char*)data); for(i=0; i < ncols; i++) { fprintf(stderr, "%s=%s ", headers[i], values[i]); } fprintf(stderr, "\n"); return 0; } 字符串处理 int main(int argc, char **argv) { char *sql; char *trouble = "'Here's trouble'"; sql = sqlite3_mprintf("insert into x values('%q')", trouble); fprintf(stdout, "%s\n", sql); sqlite3_free(sql); return 0; } The result sql will contain insert into x values('''Here''s trouble''') Listing 6-3. Using sqlite3_vmprintf() int execute(sqlite3 *db, const char* sql, ...) { char *err, *tmp; va_list ap; va_start(ap, sql); tmp = sqlite3_vmprintf(sql, ap); va_end(ap); int rc = sqlite3_exec(db, tmp, NULL, NULL, &err); if(rc != SQLITE_OK) { if (err != NULL) { fprintf(stdout, "execute() : Error %i : %s\n", rc, err); sqlite3_free(err); } } 70 sqlite3_free(tmp); return rc; } Get Table 查询 int sqlite3_get_table( sqlite3*, /* An open database */ const char *sql, /* SQL to be executed */ char ***resultp, /* Result written to a char *[] that this points to */ int *nrow, /* Number of result rows written here */ int *ncolumn, /* Number of result columns written here */ char **errmsg /* Error msg written here */ ); Listing 6-4. Using sqlite3_get_table void main(int argc, char **argv) { sqlite3 *db; char *zErr; int rc,i; char *sql; char **result; int nrows, ncols; /* Connect to database, etc. */ rc = sqlite3_open("test.db", &db); sql = "select * from episodes;"; rc = sqlite3_get_table(db, sql, &result, &nrows, &ncols, &zErr); /* Do something with data */ printf("rows=%d,cols=%d\n",nrows,ncols); for (i=0;i<=nrows;i++) printf("%-5s%-20s%-5s\n",result[3*i],result[3*i+1],result[3*i+2]); /* Free memory */ sqlite3_free_table(result); } If, for example, the result set returned is of the form rows=2,cols=3 id name cid 71 1 Cinnamon Babka2 (null) 2 Mackinaw Peaches 1 预处理的查询 As you’ll recall from Chapter 5, prepared queries are performed in three basic steps: compilation, execution, and finalization. This process is illustrated in Figure 6-1. Figure 6-1. Prepared query processing Now that you’ve seen the whole process, let’s go through an example. A simple, complete program using a prepared query is listed in Listing 6-6. It is taken from select.c in the examples. Listing 6-6. Using Prepared Queries #include int main(int argc, char **argv) { int rc, i, ncols; sqlite3 *db; sqlite3_stmt *stmt; char *sql; 72 const char *tail; rc = sqlite3_open("test.db", &db); if(rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); 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, "SQL error: %s\n", sqlite3_errmsg(db)); } rc = sqlite3_step(stmt); ncols = sqlite3_column_count(stmt); while(rc == SQLITE_ROW) { for(i=0; i < ncols; i++) { fprintf(stderr, "'%s' ", sqlite3_column_text(stmt, i)); } fprintf(stderr, "\n"); rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); sqlite3_close(db); return 0; } 跟 sqlite3_exec()相似,sqlite3_prepare()也可以接受一个包括多个 SQL 语句的字符串。不同 的是 sqlite3_prepare()只处理字符串中的第 1 个语句。But it does make it easy for you to process subsequent SQL statements in the string by providing the pzTailout parameter. After you call sqlite3_prepare(), it will point this parameter (if provided) to the starting position of the next statement in the zSQL string. Using pzTail, processing a batch of SQL commands in a given string can be executed in a loop as follows: while(sqlite3_complete(sql) { rc = sqlite3_prepare(db, sql, strlen(sql), &stmt, &tail); /* Process query results */ /* Skip to next command in string. */ sql = tail; } 73 取记录 取字段信息 你可以使用 sqlite3_column_name()来取得各字段的名称: const char *sqlite3_column_name( sqlite3_stmt*, /* statement handle */ int iCol /* column ordinal */); 类似地,你可以使用 sqlite3_column_type()取得各字段的存储类: int sqlite3_column_type( sqlite3_stmt*, /* statement handle */ int iCol /* column ordinal */); 这个函数返回一个整数值,代表 5 个存储类的代码,定义如下: #define SQLITE_INTEGER 1 #define SQLITE_FLOAT 2 #define SQLITE_TEXT 3 #define SQLITE_BLOB 4 #define SQLITE_NULL 5 这些是SQLite 本身的类型,或称存储类,在 第 4 章有详细介绍。All data stored within a SQLite database is stored in one of these five forms, depending on its initial representation and the affinity of the column. For our purposes, the terms storage class and data type are synonymous. For more information on storage classes, see the sections “Storage Classes” and “Type Affinity” in Chapter 4. 你可以使用 sqlite3_column_decltype()函数获得字段声明的数据类型: const char *sqlite3_column_decltype( sqlite3_stmt*, /* statement handle */ int /* column ordinal */); 如果结果集中的一列不是来自一个实际的字段(如来自于表达式、函数或聚合的结果),这个 函数将返回 NULL。For example, suppose you have a table in your database defined as CREATE TABLE t1(c1 INTEGER); Then you execute the following query: SELECT c1 + 1, 0 FROM t1; In this case, sqlite3_column_decltype() will return INTEGER for the first column and NULL for the second. 还可以用下列函数获得字段的其它信息: const char *sqlite3_column_database_name(sqlite3_stmt *pStmt, int iCol); const char *sqlite3_column_table_name(sqlite3_stmt *pStmt, int iCol); const char *sqlite3_column_origin_name(sqlite3_stmt *pStmt, int iCol); The first function will return the database associated with a column, the second its table, and the last function returns the column’s actual name as defined in the schema. That is, if you assigned the column an alias in the SQL statement, sqlite3_column_origin_name() will return its actual name as defined in the schema. Note that these functions are only available if you compile SQLite with the SQLITE_ENABLE_COLUMN_METADATA preprocessor directive. 74 列元数据: 字段的详细信息可以从一个独立的 query 获得,使用 sqlite3_table_column_metadata()函数, 声明如下: SQLITE_API int sqlite3_table_column_metadata( sqlite3 *db, /* Connection handle */ const char *zDbName, /* Database name or NULL */ const char *zTableName, /* Table name */ const char *zColumnName, /* Column name */ char const **pzDataType, /* OUTPUT: Declared data type */ char const **pzCollSeq, /* OUTPUT: Collation sequence name */ int *pNotNull, /* OUTPUT: True if NOT NULL constraint exists */ int *pPrimaryKey, /* OUTPUT: True if column part of PK */ int *pAutoinc /* OUTPUT: True if column is auto-increment */ ); 这个函数包含输入和输出参数。它不在 statement 句柄下工作,但需要提供连接句柄、数据 库名、表名和列名。可选的数据库名指明附加的逻辑数据库名(一个连接上可能附加多个数 据库)。表名和字段名是必须的。 取字段值 可以使用 sqlite3_column_xxx()函数取当前记录中每个字段的值,其一般形式为: xxx sqlite3_column_xxx( sqlite3_stmt*, /* statement handle */ int iCol /* column ordinal */); xxx 表示你希望得到的数据类型。sqlite3_column_xxx()包括以下函数: int sqlite3_column_int(sqlite3_stmt*, int iCol); double sqlite3_column_double(sqlite3_stmt*, int iCol); long long int sqlite3_column_int64(sqlite3_stmt*, int iCol); const void *sqlite3_column_blob(sqlite3_stmt*, int iCol); const unsigned char *sqlite3_column_text(sqlite3_stmt*, int iCol); const void *sqlite3_column_text16(sqlite3_stmt*, int iCol); 对每个函数,SQLite 都会将字段值从存储类转化为函数指定的结果类型。Table 6-1 中是转 换规则。 Table 6-1. Column Type Conversion Rules Internal Type Requested Type Conversion NULL INTEGER Result is 0. NULL FLOAT Result is 0.0. NULL TEXT Result is a NULL pointer. 一个实际的例子 To help solidify all of these column functions, Listing 6-7 (taken from columns.c) illustrates using the functions we’ve described to retrieve column information and values for a simple SELECT statement. 75 Listing 6-7. Obtaining Column Information #include int main(int argc, char **argv) { int rc, i, ncols, id, cid; char *name, *sql; sqlite3 *db; sqlite3_stmt *stmt; sql = "select id,cid,name from episodes"; sqlite3_open("test.db", &db); sqlite3_prepare(db, sql, strlen(sql), &stmt, NULL); ncols = sqlite3_column_count(stmt); rc = sqlite3_step(stmt); /* Print column information */ for(i=0; i < ncols; i++) { fprintf(stdout, "Column: name=%s, storage class=%i, declared=%s\n", sqlite3_column_name(stmt, i), sqlite3_column_type(stmt, i), sqlite3_column_decltype(stmt, i)); } fprintf(stdout, "\n"); while(rc == SQLITE_ROW) { id = sqlite3_column_int(stmt, 0); cid = sqlite3_column_int(stmt, 1); name = (char *)sqlite3_column_text(stmt, 2); if(name != NULL){ fprintf(stderr, "Row: id=%i, cid=%i, name='%s'\n", id,cid,name); } else { /* Field is NULL */ fprintf(stderr, "Row: id=%i, cid=%i, name=NULL\n", id,cid); } rc = sqlite3_step(stmt); } sqlite3_finalize(stmt); sqlite3_close(db); return 0; } 76 参数化的查询 Figure 6-2. SQL parameter binding 错误和意外 有些 API 是很可能出错的,在编码时总要记得 3 件事:错误、忙状态和 schema 改变。 处理错误 很多 API 函数返回整数结果码,这表示它们可以返回错误码。在使用一个函数之前,应该 仔细阅读关于该函数的说明(见附录 B),看它可能引发什么错误。API 中定义了大约 23 种错 误。所有的 SQLite 返回码见表 6-2。所有能够返回这些码的函数包括: sqlite3_bind_xxx() sqlite3_close() sqlite3_create_collation() sqlite3_collation_needed() 77 sqlite3_create_function() sqlite3_prepare() sqlite3_exec() sqlite3_finalize() sqlite3_get_table() sqlite3_open() sqlite3_reset() sqlite3_step() sqlite3_transfer_bindings() 可以使用函数 sqlite3_errmsg()获得附加的错误信息,其声明如下: const char *sqlite3_errmsg(sqlite3 *); 它以一个连接句柄作参数,返回该连接最近的一条错误信息。如果还没有发生错误,它 返回 “ not an error”。 表 6-2 SQLit 的返回码 返回码 说明 SQLITE_OK The operation was successful. SQLITE_ERROR General SQL error or missing database. It may be possible to obtain more error information depending on the error condition (SQLITE_SCHEMA, for example). SQLITE_PERM Access permission denied. Cannot read or write to the database file. SQLITE_ABORT A callback routine requested an abort. SQLITE_BUSY The database file is locked. SQLITE_LOCKED A table in the database is locked. SQLITE_NOMEM A call to malloc() has failed within a database operation. SQLITE_READONLY An attempt was made to write to a read-only database. SQLITE_INTERRUPT Operation was terminated by sqlite3_interrupt(). SQLITE_IOERR Some kind of disk I/O error occurred. SQLITE_CORRUPT The database disk image is malformed. This will also occur if an attempt is made to open a non-SQLite database file as a SQLite database. SQLITE_FULL Insertion failed because the database is full. There is no more space on the file system or the database file cannot be expanded. SQLITE_CANTOPEN SQLite was unable to open the database file. SQLITE_PROTOCOL The database is locked or there has been a protocol error. SQLITE_EMPTY (Internal only) The database table is empty. SQLITE_SCHEMA The database schema has changed. SQLITE_CONSTRAINT Abort due to constraint violation. This constant is returned if the SQL statement would have violated a database constraint (such as attempting to insert a value into a unique index that already exists in the index). SQLITE_MISMATCH Data type mismatch. An example of this is an attempt to insert non-integer data into a column labeled INTEGER PRIMARY KEY. For most columns, SQLite ignores the data type and allows any kind of data to 78 be stored. But an INTEGER PRIMARY KEY column is only allowed to store integer data. SQLITE_MISUSE Library was used incorrectly. This error might occur if one or more of the SQLite API routines is used incorrectly. Examples of incorrect usage include calling sqlite3_exec() after the database has been closed using sqlite3_close() or calling sqlite3_exec() with the same database pointer simultaneously from two separate threads. SQLITE_NOLFS Uses OS features not supported on host. This value is returned if the SQLite library was compiled with large file support (LFS) enabled but LFS isn’t supported on the host operating system. SQLITE_AUTH Authorization denied. This occurs when a callback function installed using sqlite3_set_authorizer() returns SQLITE_DENY. SQLITE_ROW sqlite3_step() has another row ready. SQLITE_DONE sqlite3_step() has finished executing. 处理忙状态 操作控制 API 提供了几个函数,可以用来监视或管理编译期间和运行时的 SQL 命令。这些函数允许 你建立回叫函数,并以此对不同的数据库事件进行监视和控制(当事件发生时)。 提交 Hook 函数 使用 sqlite3_commit_hook()函数可以在特定连接提交事务时对其进行监视。其声明如下: void *sqlite3_commit_hook( sqlite3 *cnx, /* database handle */ int(*xCallback)(void *data), /* callback function */ void *data); /* application data */ 回卷 Hook 函数 回卷 Hook 函数与提交 Hook 函数相类似,但它在特定连接回卷事务时对其进行监视。 void *sqlite3_rollback_hook(sqlite3 *cnx, void(*xCallback)(void *data), void *data); 修改 Hook 函数 函数 sqlite3_update_hook()用来监视特定数据库连接所有的 UPDATE、INSERT 和 DELETE 操作,对这些操作中所涉及的每一行都进行监视,其声明如下: void *sqlite3_update_hook( sqlite3 *cnx, void(*)(void *, int, char const*, char const*, sqlite_int64), 79 void *data); The first argument of the callback function is a pointer to application-specific data, which you provide in the third argument. The callback function has the following form: void callback ( void * data, int operation_code, char const *db_name, char const *table_name, sqlite_int64 rowid), 授权函数 sqlite3_set_authorizer()是最强有力的事件过滤函数。用它可以在查询编译的时候对其进行监 视和控制。其声明如下: int sqlite3_set_authorizer( sqlite3*, int (*xAuth)( void*,int, const char*, const char*, const char*,const char*), void *pUserData ); 其中注册了一个 callback 函数,作为授权函数。SQLite 在一些数据库事件的命令编译阶段将 会调用它(不是在执行阶段)。这个函数的用意是使用 SQLite 能够安全地执行用户提供的 SQL(user-supplied SQL)。它提供了一种途径将这类 SQL 限制在特定的操作上或拒绝对某些 表或字段的存取。 Callback 的声明形式如下: int auth( void*, /* user data */ int, /* event code */ const char*, /* event specific */ const char*, /* event specific */ const char*, /* database name */ const char* /* trigger or view name */ ); 第1个参数是一个数据指针,它会传递给 sqlite3_set_authorizer()函数的第 4 个参数。第 2 个 参数是一个常量,可选值在表 6-3 中列出。这些常量值表示需要授权的是什么操作。第 3、 4 个函数的含义决定于事件代码(第 2 个参数,参表 6-3)。 第 5 个参数是数据库名。第 6 个参数是最内层触发器或视图的名称,就是这个触发器或视图 企图存取数据库。如果这个参数为 NULL,则说明这种存取的企图是直接由顶层的 SQL 引 发的。 授权函数的返回值应该是 SQLITE_OK、SQLITE_DENY 或 SQLITE_IGNORE 之一。前两个 值的含义对所有事件都是确定的——接受或拒绝 SQL。SQLITE_DENY 将会取消整个 SQL 语句的执行并生成一个错误。 SQLITE_IGNORE 的含义与事件有关。如果 SQL 语句是读或改记录,会在语句试图操作的 每个字段上产生 SQLITE_READ 或 SQLITE_UPDATE 事件。在这种情况下,如果回叫函数 返回 SQLITE_IGNORE,这些字段将从操作中被排除(高:别的字段继续操作,这些字段就 80 不操作了)。具体说,试图读的返回 NULL,试图写的则什么也不做(silently fail)。 表 6-3 SQLite 的授权事件 事件代码 参数 3 参数 4 SQLITE_CREATE_INDEX Index name Table name SQLITE_CREATE_TABLE Table name NULL SQLITE_CREATE_TEMP_INDEX Index name Table name SQLITE_CREATE_TEMP_TABLE Table name NULL SQLITE_CREATE_TEMP_TRIGGER Trigger name Table name SQLITE_CREATE_TEMP_VIEW View name NULL SQLITE_CREATE_TRIGGER Trigger name Table name SQLITE_CREATE_VIEW View name NULL SQLITE_DELETE Table name NULL SQLITE_DROP_INDEX Index name Table name SQLITE_DROP_TABLE Table name NULL SQLITE_DROP_TEMP_INDEX Index name Table name SQLITE_DROP_TEMP_TABLE Table name NULL SQLITE_DROP_TEMP_TRIGGER Trigger name Table name SQLITE_DROP_TEMP_VIEW View name NULL SQLITE_DROP_TRIGGER Trigger name Table name SQLITE_DROP_VIEW View name NULL SQLITE_INSERT Table name NULL SQLITE_PRAGMA Pragma name First argument or NULL SQLITE_READ Table name Column name SQLITE_SELECT NULL NULL SQLITE_TRANSACTION NULL NULL SQLITE_UPDATE Table name Column name SQLITE_ATTACH Filename NULL SQLITE_DETACH Database name NULL 下面例子说明授权函数的使用(完整的程序在 authorizer.c 中)。 这是一个很长的例子,会用授权函数对很多不同的数据库事件进行过滤,所以我们通过程序 片段来进行说明。见列表 6-10。 列表 6-10 授权函数示例 授权函数的一般形式为: int auth( void* x, int type, const char* a, const char* b, const char* c, const char* d ) { const char* operation = a; //printf( " %s ", event_description(type)); /* Filter for different database events ** from SQLITE_TRANSACTION to SQLITE_INSERT, 81 ** UPDATE, DELETE, ATTACH, etc. and either allow or deny ** them. */ return SQLITE_OK; } 授权函数做的第1件事是:看看事务状态是否改变;如果改变,则输出一个信息: if((a != NULL) && (type == SQLITE_TRANSACTION)) { printf(": %s ÊÂÎñ", operation); } 下一步是对引起 schema 改变的事件进行过滤: switch(type) { case SQLITE_CREATE_INDEX: case SQLITE_CREATE_TABLE: case SQLITE_CREATE_TRIGGER: case SQLITE_CREATE_VIEW: case SQLITE_DROP_INDEX: case SQLITE_DROP_TABLE: case SQLITE_DROP_TRIGGER: case SQLITE_DROP_VIEW: { printf(": Schema¸Ä±äÁË¡£"); } } 下一步是对读的企图进行检查,这种企图是基于字段的。这里,所有的读都被允许,除了 z 字段。当要读 z 字段时,函数返回 SQLITE_IGNORE,这将导致 SQLite 在读这个字段时返 回 NULL,从而有效地保护其数据。 if(type == SQLITE_READ) { printf(": Read of %s.%s ", a, b); /* Block attempts to read column z */ if(strcmp(b,"z")==0) { printf("-> DENIED\n"); return SQLITE_IGNORE; } } 下面是 INSERT 和 UPDATE 的过滤。所有的插入被允许。对 x 字段的修改被拒绝。这样不 会锁住 UPDATE 的执行,而是简单地过滤掉对 x 字段的修改企图。 if(type == SQLITE_INSERT) { printf(": ²åÈë¼Ç¼ into %s ", a); } if(type == SQLITE_UPDATE) { printf(": Update of %s.%s ", a, b); /* Block updates of column x */ 82 if(strcmp(b,"x")==0) { printf("-> DENIED\n"); return SQLITE_IGNORE; } } 最后,对 DELETE、ATTACH 和 DETACH 进行过滤,在遇到这些事件时只是简单地给出通 知。 if(type == SQLITE_DELETE) { printf(": Delete from %s ", a); } if(type == SQLITE_ATTACH) { printf(": %s", a); } if(type == SQLITE_DETACH) { printf("-> %s", a); } 下面是主程序,为了介绍的方便,也会分成多个片段。 int main(int argc, char **argv) { sqlite3 *db, *db2; char *zErr; int rc; /* ------------------------------------------------------------------------- ** Setup ** ------------------------------------------------------------------------- */ /* Connect to test.db */ rc = sqlite3_open("test.db", &db); if(rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db)); sqlite3_close(db); exit(1); } /* ------------------------------------------------------------------------- ** Authorize and test ** ------------------------------------------------------------------------- */ /* Register the authorizer function */ 83 sqlite3_set_authorizer(db, auth, NULL); /* Test transactions events */ printf("program : Starting transaction\n"); sqlite3_exec(db, "BEGIN", NULL, NULL, &zErr); printf("program : Committing transaction\n"); sqlite3_exec(db, "COMMIT", NULL, NULL, &zErr); printf("program : Starting transaction\n"); sqlite3_exec(db, "BEGIN", NULL, NULL, &zErr); printf("program : Aborting transaction\n"); sqlite3_exec(db, "ROLLBACK", NULL, NULL, &zErr); // Test table events printf("program : Creating table\n"); sqlite3_exec(db, "create table foo(x int, y int, z int)", NULL, NULL, &zErr); printf("program : Inserting record\n"); sqlite3_exec(db, "insert into foo values (1,2,3)", NULL, NULL, &zErr); printf("program : Selecting record (value for z should be NULL)\n"); print_sql_result(db, "select * from foo"); printf("program : Updating record (update of x should be denied)\n"); sqlite3_exec(db, "update foo set x=4, y=5, z=6", NULL, NULL, &zErr); printf("program : Selecting record (notice x was not updated)\n"); print_sql_result(db, "select * from foo"); printf("program : Deleting record\n"); sqlite3_exec(db, "delete from foo", NULL, NULL, &zErr); printf("program : Dropping table\n"); sqlite3_exec(db, "drop table foo", NULL, NULL, &zErr); Several things are going on here. The program selects all records in the table, one of which is column z. We should see in the output that column z’s value is NULL. All other fields should contain data from the table. Next, the program attempts to update all fields, the most important of which is column x. The update should succeed, but the value in column x should be unchanged, as the authorizer denies it. This is confirmed on the following SELECT statement, which shows that all columns were updated except for column x, which is unchanged. The 84 program then drops the foo table, which should issue a schema change notification from the previous filter. // Test ATTACH/DETACH // Connect to test2.db rc = sqlite3_open("test2.db", &db2); if(rc) { fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db2)); sqlite3_close(db2); exit(1); } // Drop table foo2 in test2 if exists sqlite3_exec(db2, "drop table foo2", NULL, NULL, &zErr); sqlite3_exec(db2, "create table foo2(x int, y int, z int)", NULL, NULL, &zErr); // Attach database test2.db to test.db printf("program : Attaching database test2.db\n"); sqlite3_exec(db, "attach 'test2.db' as test2", NULL, NULL, &zErr); // Select record from test2.db foo2 in test.db printf("program : Selecting record from attached database test2.db\n"); sqlite3_exec(db, "select * from foo2", NULL, NULL, &zErr); printf("program : Detaching table\n"); sqlite3_exec(db, "detach test2", NULL, NULL, &zErr); /* ------------------------------------------------------------------------- ** Cleanup ** ------------------------------------------------------------------------- */ sqlite3_close(db); sqlite3_close(db2); return 0; } 线程 如第 2 章所述,SQLite 支持线程。在多线程环境下使用 SQLite 时,有一些基本规则需要遵 守。 85 共享缓冲区模式 Figure 6-3. The shared cache model 线程和内存管理 共享缓冲区模式的目录是为了节省内在,SQLite 中有几个函数是与线程和内存管理有关的。 使用它们可以限制堆的尺寸或手工地发起内存清理。这些函数包括: void sqlite3_soft_heap_limit(int N); int sqlite3_release_memory(int N); void sqlite3_thread_cleanup(void); 86 第 7 章 扩充C API 本章介绍 SQLite 的新技巧。前一章涉及一般的数据库操作,本章将开始创新。扩充 API 提 供 3 种基本方法来扩展(或说定制)SQLite,包括:创建用户自定义函数、聚合和排序序列。 用户自定义函数是编写用于特定应用的 SQL 函数。一旦注册,就可以在 SQL 中被调用。 本章将涉及所有这 3 个用户定义的扩展工具及与之相关的 API 函数。你会看到,当与其它 工具,如触发器和冲突解决等结合在一起时,用户定义的扩充 API 是强有力的,并能为 SQLite 创造非凡特色。 空注:本章内容对编程还是很有用的,但我对这部分内容只是略做浏览。关心这部分内容的 兄弟还是得自己看原文。 API 用户自定义聚合、函数和排序法的生命同期是基于连接的。它 们不 存储在数据库中。有时你 可能会把它们当成存储过程看待,而忘记了它们是在数据库之外的。它们存在于程序库 (librarie)中,其生命周期严格地限制在你的程序之内。I 注册函数 步进函数 自定义函数和聚合的步进函数是一样的,可如下定义: void fn(sqlite3_context* ctx, int nargs, sqlite3_value** values) 返回值 函数 返回值 一个完整的例子 Listing 7-2. The main Function int main(int argc, char **argv) { 87 int rc; sqlite3 *db; const char* sql; sqlite3_open("test.db", &db); sqlite3_create_function( db, "function", -1, SQLITE_UTF8, NULL, function, NULL, NULL); /* Turn on SQL logging */ //log_sql(db, 1); /* Call function with one text argument. */ execute(db, "select function(1)"); /* Call function with several arguments of various types. */ execute(db, "select function(1, 2.71828)"); /* Call function with variable arguments, the first argument’s value ** being 'fail'. This will trigger the function to call ** sqlite3_result_error(). */ execute(db, "select function('fail', 1, 2.71828, 'three', X'0004', NULL)"); /* Done */ sqlite3_close(db); return 0; } Listing 7-3. A Vanilla User-Defined Function void function(sqlite3_context* ctx, int nargs, sqlite3_value** values) { int i; const char *msg; fprintf(stdout, "function() : Called with %i arguments\n", nargs); for(i=0; i < nargs; i++) { fprintf( stdout, " arg %i: value=%-7s type=%i\n", i, sqlite3_value_text(values[i]), sqlite3_value_type(values[i])); } if(strcmp((const char *)sqlite3_value_text(values[0]), "fail") == 0) { msg = "function() : Failing because you told me to."; sqlite3_result_error(ctx, msg, strlen(msg)); 88 fprintf(stdout, "\n"); return; } fprintf(stdout, "\n"); sqlite3_result_int(ctx, 0); } 一个实际的应用程序 聚合 Figure 7-1. Query processing with aggregates 一个实际的例子 Listing 7-9. The sum_int() Test Program int main(int argc, char **argv) { int rc; sqlite3 *db; char *sql; rc = sqlite3_open("test.db", &db); if(rc) { print_error(db, "Can't open database"); 89 exit(1); } /* Create aggregate table, add records. */ setup(db); /* Register aggregate. */ fprintf(stdout, "Registering aggregate sum_int()\n"); log_sql(db, 1); sqlite3_create_function( db, "sum_int", 1, SQLITE_UTF8, db, NULL, step, finalize); /* Test it. */ fprintf(stdout, "\nRunning query: \n"); sql = "select sum_int(id) from aggregate"; print_sql_result(db, sql); /* Done. */ sqlite3_close(db); return 0; } 步进函数 The step() function is shown in Listing 7-10. Listing 7-10. The sum_int() Step Function void step(sqlite3_context* ctx, int ncols, sqlite3_value** values) { sum* s; int x; s = (sum*)sqlite3_aggregate_context(ctx, sizeof(sum)); if(sqlite3_aggregate_count(ctx) == 1) { s->x = 0; } x = sqlite3_value_int(values[0]);; s->x += x; fprintf(stdout, "step() : value=%i, total=%i\n", x, s->x); 90 } The value sum is a struct that is specific to this example and is defined as follows: typedef struct { int x; } sum; 聚合的 Context Finalize 函数 Listing 7-11. The sum_int() Finalize Function void finalize(sqlite3_context* ctx) { sum* s; s = (sum*)sqlite3_aggregate_context(ctx, sizeof(sum)); sqlite3_result_int(ctx, s->x); fprintf(stdout, "finalize() : total=%i\n\n", s->x); } 排序法 排序法定义 排序法如何工作 标准的排序法类型 一个简单的例子 Compare 函数 Listing 7-12. The Political Collation Function int political_collation( void* data, int l1, const void* s1, int l2, const void* s2 ) { int value, opinion; struct tm* t; time_t rt; 91 /* Get the unpolitical value */ value = strcmp(s1,s2); /* Get the date and time */ time(&rt); t = localtime(&rt); /* Form an opinion */ switch(t->tm_wday) { case 0: /* Monday yes */ opinion = value; break; case 1: /* Tueday no */ opinion = -value; break; case 2: /* Wednesday bigger is better */ opinion = l1 >= l2 ? -1:1; break; case 3: /* Thursday strongly no */ opinion = -100; break; case 4: /* Friday strongly yes */ opinion = 100; break; case 5: /* Saturday golf, everything's the same */ opinion = 0; break; default: /* Sunday - Meet the Press, opinion changes by the hour */ opinion = 2*(int)sin(t->tm_hour*180); } /* Now change it on a whim */ opinion = rand()-(RAND_MAX/2) > 0 ? -1:1; return opinion; } 测试程序 Listing 7-13. The Political Collation Test Program int main(int argc, char **argv) { 92 int rc; sqlite3 *db; char *sql; /* For forming more consistent political opinions. */ srand((unsigned)time(NULL)); rc = sqlite3_open("test.db", &db); if(rc) { print_error(db, "Can't open database"); exit(1); } /* Create issues table, add records. */ setup(db); /* Register collating sequence. */ fprintf(stdout, "1. Register political collating sequence\n\n"); sqlite3_create_collation( db, "POLITICAL", SQLITE_UTF8, db, political_collation ); /* Turn SQL logging on. */ log_sql(db, 1); /* Test default collation. */ fprintf(stdout, "2. Select records using default collation.\n"); sql = "select * from issues order by issue"; print_sql_result(db, sql); /* Test Oracle collation. */ fprintf(stdout, "\nSelect records using political collation. \n"); sql = "select * from issues order by issue collate POLITICAL"; print_sql_result(db, sql); /* Done. */ sqlite3_close(db); return 0; } 93 按需排序(Collation on Demand) Listing 7-14. Collation Registration Function void crf( void* data, sqlite3* db, int eTextRep, const char* cname) { if(strcmp(collation_name, "POLITICAL") == 0) { /* Political collation has not been registered and is now needed */ sqlite3_create_collation( db, "POLITICAL", SQLITE_UTF8, db, political_collation ); } else { /* Punt: Use some default comparison function this collation. */ sqlite3_create_collation( db, collation_name, SQLITE_UTF8, db, default_collation ); } } 一个实际的应用程序 比较函数 Listing 7-15. Oracle Date Collation Function int oracle_date_collation( void* data, int len1, const void* arg1, int len2, const void* arg2 ) { int len; date d1; date d2; char zDate1[25]; char zDate2[25]; /* Copy date 1 */ if(len1 > 24) { len = 24; } else { len = len1; } 94 strncpy(&zDate1[0], arg1, len); zDate1[len] = '\0'; /* Copy date 2 */ if(len2 > 24) { len = 24; } else { len = len2; } strncpy(&zDate2[0], arg2, len); zDate2[len] = '\0'; /* Convert dates to date struct */ oracle_date_str_to_struct(zDate1, &d1); oracle_date_str_to_struct(zDate2, &d2); fprintf(stdout, "collate_fn() : date1=%s, date2=%s\n", zDate1, zDate2); /* Compare structs */ if(d1.year < d2.year) { return -1; } else if(d1.year > d2.year) { return 1; } /* If this far, years are equal. */ if(d1.month < d2.month) { return -1; } else if(d1.month > d2.month) { return 1; } /* If this far, months are equal. */ 95 if(d1.day < d2.day) { return -1; } else if(d1.day > d2.day) { return 1; } /* If this far, dates are equal. */ return 0; } 日期解析 Listing 7-16. The Oracle Date Parsing Function int oracle_date_str_to_struct(const char* value, date* d) { const char* date, *tmp; char *start, *end, zDay[3], zMonth[4], zYear[3]; date = get_date(value); if(date == NULL) { fprintf(stderr, "Invalid date\n"); return -1; } /* Find first '-' */ start = strchr(date,'-'); /* Find last '-' */ end = strchr(start+1,'-'); /* Extract day part, convert to int*/ strncpy(zDay, date,2); zDay[2] = '\0'; d->day = atoi(zDay); /* Extract month part, convert to int*/ strncpy(zMonth, start+1,3); zMonth[3] = 0; tmp = uppercase(zMonth); 96 d->month = month_num(tmp); free((void*)tmp); /* Extract year part, convert to int*/ strncpy(zYear, end+1,2); zYear[2] = '\0'; d->year = atoi(zYear); free((void*)date); return 0; } Listing 7-17. The get_date() Function #define ORACLE_DATE_REGEX "[0-9]{1,2}-[a-zA-Z]{3,3}-[0-9]{2,2}"; const char* get_date(const char* value) { pcre *re; const char *error, *pattern; int erroffset; int ovector[3]; int value_length; int rc, substring_length; char* result, *substring_start; pattern = ORACLE_DATE_REGEX; re = pcre_compile( pattern, /* the pattern */ 0, /* default options */ &error, /* for error message */ &erroffset, /* for error offset */ NULL); /* use default character tables */ /* Compilation failed */ if (re == NULL) { return NULL; } value_length = (int)strlen(value); rc = pcre_exec( re, /* the compiled pattern */ 97 NULL, /* no extra data - we didn't study the pattern */ value, /* the value string */ value_length, /* the length of the value */ 0, /* start at offset 0 in the value */ 0, /* default options */ ovector, /* output vector for substring information */ 3); /* number of elements in the output vector */ if (rc < 0) { /* Match error */ return NULL; } /* Match succeded */ substring_start = (char*)value + ovector[0]; substring_length = ovector[1] - ovector[0]; //printf("%.*s\n", substring_length, substring_start); result = malloc(substring_length+1); strncpy(result, substring_start, substring_length); result[substring_length] = '\0'; return result; } 测试程序 All three of the above functions work together to collate Oracle dates in chronological order. Our example program is shown in Listing 7-18. Listing 7-18. The Oracle Collation Test Program int main(int argc, char **argv) { int rc; sqlite3 *db; char *sql; rc = sqlite3_open("test.db", &db); if(rc) { print_error(db, "Can't open database"); exit(1); } 98 /* Install oracle related date functions. */ install_date_functions(db); /* Register collating sequence. */ fprintf(stdout, "Registering collation sequence oracle_date\n"); sqlite3_create_collation( db, "oracle_date", SQLITE_UTF8, db, oracle_date_collation ); /* Create dates table, add records. */ setup(db); /* Install date */ install_date_triggers(db); /* Turn SQL logging on. */ log_sql(db, 1); /* Test default collation. */ fprintf(stdout, "Select records. Use default collation.\n"); sql = "select * from dates order by date"; print_sql_result(db, sql); /* Test Oracle collation. */ fprintf(stdout, "\nSelect records. Use Oracle data collation. \n"); sql = "select * from dates order by date collate oracle_date"; print_sql_result(db, sql); /* Get ISO Date from Oracle date. */ fprintf(stdout, "\nConvert Oracle date to ISO format.\n"); sql = "select iso_from_oradate('01-APR-05') as 'ISO Date'"; print_sql_result(db, sql); /* Validate Oracle date. */ fprintf(stdout, "\nValidate Oracle format. Should fail.\n"); sql = "select validate_oradate('01-NOT-2005')"; execute(db, sql); /* Test Oracle date triggers. */ fprintf(stdout, "\nTest Oracle insert trigger -- should fail.\n"); sql = "insert into dates (date) values ('01-NOT-2005')"; execute(db, sql); 99 fprintf(stdout, "\nTest Oracle update trigger -- should succeed.\n"); sql = "update dates set date='01-JAN-2005'"; execute(db, sql); print_sql_result(db, "select * from dates"); /* Done. */ sqlite3_close(db); return 0; } 运行结果 略。 100 第 8 章 语言扩展 SQLite 本身是用 C 语言编写的,它有自己的 C API。但是,开源社区中提供了多种 SQLite 的扩展,使你在以在其它的编程语言或程序库中存取 SQLite 数据库,如 Per、Python、Ruby、 Java、Qt 和 ODBC。在很多情况下,每种编程语言可以有几个扩展可供选择,这些扩展由 不同的人为不同的需求而编写。 空注:我只在其它地方看了 Delphi 的扩展,本章基本没看。 101 第 9 章 SQLite 内核 本章是 SQLite 各主要子系统的一个概览。它的灵感来自一次会议上 Richard Hipp 对 SQLite 所做的介绍。即使你没有看过 SQLite 的源代码,你也会发现这些内容是如此的有趣。即使 SQLite 还在发展,但本章所介绍的概念一时不会改变。 现在,你应该已经熟悉 SQLite 的主要组件了。第 1 章有一个概述,第 5 章介绍了 B-tree 和 pager,这些概念本章就不再介绍了。本章会从虚拟机入手,它是 SQLite 的心脏;然后是存 储层;最后是编译器,它可能是系统中最复杂的部分。 虚拟数据库引擎(VDBE) VDBE 是 SQLite 的核心,它的上层模块和下层模块本质上都是为它服务的,它的实现位于 vbde.c、vdbe.h、vdbeapi.c、vdbeInt.h 和 vdbemem.c 等几个文件中。如第 5 章所述,一个语 句(statement)会编译为一个完整的 VDBE 程序,执行一条单独的 SQL 命令。它通过底层的 基础设施 B-tree 执行由编译器(Compiler)生成的字节代码,这种字节代码程序语言是为了进 行查询、读取和修改数据库而专门设计的。 字节代码在内存中被封装成 sqlite3_stmt 对象(内部叫做 Vdbe,见 vdbeInt.h),Vdbe(或者说 statement)包含执行程序所需要的一切,包括: l VDBE 程序 l 程序计数器 l 结果字段的名称和类型 l 参数的绑定值 l 运行栈和固定数量的编号的内在单元 l 其它的运行时状态信息,如 B-tree 游标 VDBE 是一个虚拟机,它的字节代码指令和汇编程序十分类似,每一条指令由操作码和三个 操作数构成:。Opcode 为一定功能的操作码,为了理解,可以看成一个 函数。p1 是 32 位的有符号整数,p2 是 31 位的无符号整数,它通常是跳转(jump)指令的目 标地址(destination),当然还有其它用途;p3 为一个以 null 结尾的字符串或者其它结构体的 指针。目前 SQLite 中有 128 个操作码。和 C API 不同的是,VDBE 操作码经常变化,所以 不应该用字节码编写自己的程序。 下面的几个 C API 直接和 VDBE 交互: l sqlite3_bind_xxx() functions l sqlite3_step() l sqlite3_reset() l sqlite3_column_xxx() functions l sqlite3_finalize() 一般情况下,所有的 API 都是用来执行一个查询并在 VDBE 相关的结果集中步进操作。它 们有一个共同点:都以一个语句句柄做参数。这是因为它们都需要句柄中的 VDBE 代码或 相关资源来完成任务。注意:sqlite3_prepare()工作于开始阶段,用于产生 VDBE 代码,它不 参与执行。 所有 SQL 命令的 VDBE 程序都可以通过 EXPLAIN 命令得到,如: 102 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 上面使用了 4 条命令,前面的命令用于调试和格式化。另外,我在编译 SQLite 时使用了 SQLITE_DEBUG 选项,这个选择可以提供运行栈更多的信息,比如包含在 p3 里面的表名。 空注:当前版本的 SQLite(3.6.18)确实有较大变化,现在执行 EXPLAIN 命令的结果如下。 addr opcode p1 p2 p3 p4 p5 comment ---- --------------- --- --- ----- ---------- ---- ---------- 0 Trace 0 0 0 00 1 Goto 0 11 0 00 2 OpenRead 0 2 0 3 00 3 Rewind 0 9 0 00 4 Rowid 0 1 0 00 5 Column 0 1 2 00 6 Column 0 2 3 00 7 ResultRow 1 3 0 00 8 Next 0 4 0 01 9 Close 0 0 0 00 10 Halt 0 0 0 00 11 Transaction 0 0 0 00 12 VerifyCookie 0 40 0 00 13 TableLock 0 2 0 episodes 00 14 Goto 0 2 0 00 空注:有关 VDBE 的最详细参考在 vbde.c 中,也可以参考 SQLite 网站提供的文档 http://www.sqlite.org/opcode.html。 103 空注:后面的内容还按原文翻译。 栈(Stack) 一个 VDBE 程序通常由几个完成特定任务的段(section)构成,每一个段中都有一些操作栈的 指令。这么做是因为不同的指令有不同数量的参数,有些指令只有一个参数;有些指令没有 参数;有些指令有好几个参数,这时三个操作数就不够了。 考虑到这些情况,指令采用栈来传递参数。而这些指令本身不会做这些工作,所以在它们之 前需要其它一些指令的帮助,以 取 得 需 要的参数。VDBE 把计算的中间结果保存到内存单元 (memory cell)中,其实堆栈和内存单元都基于 Mem 结构(见 vdbeInt.h)。 程序体 让我们来看前面打开 episodes 表的例子。它的第一个段主要包括指令 1~3。 第一条指令(Integer)是为第二条指令作准备的,也就是把第二条指令执行需要的参数压入堆 栈,OpenRead 从堆栈中取出参数值然后执行。 SQLite 可以通过 ATTACH 命令在一个连接中打开多个数据库文件,每当 SQLite 打开一个数 据库,它就为之赋一个索引号(index),主数据库的索引为 0,附加的第一个数据库为 1,依 次类推。Integer 指令将数据库索引的值压入栈(本例为 0,代表主数据库),而 OpenRead 从 中取出值,并决定操作哪个数据库。它用 P2 来确定需要打开表的根页(root page)。然后它打 开一个指定数据库中指定表的 B-tree 游标。所有这些在 VDBE 代码文档中都有解释,例如, OpenRead 命令在 SQLite 文档中的解释如下: 为数据库表打开一个只读游标,这个表的根页在数据库文件的 P2 处。数据库文件由栈 顶的一个整数指定。0 表示主数据库,1 表示用于存放临时表的数据库。新打开游标的 标识符在 P1 中。P1 的值不必是相邻的,但应该是一个小整数。如果其值为负,表示错 误。If P2==0 then take the root page number from off of the stack. 只要有游标打开,就会有一个读锁加载到数据库上。如果数据库本来是未加锁的,此命 令的部分工作包括获得一个读锁。读锁允许其它进程读数据库,但是禁止任何进程改数 据库。读锁在所有游标都关闭时释放。如果此指令在申请读锁时失败,程序结束并返回 SQLITE_BUSY 错误码。 P3 的值是指向一个结构的指针,该结构定义索引的内容和排序序列的关键信息。当不 指向索引时,P3 的内容为空。 这个关于 OpenRead 的文档与其它指令的文档一样,可以直接在源程序文件中找到,特别是 vdbe.c 中。 最终,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 字段 (游标 0 所指明的表 episodes 的全部 3 个字段)的值压入栈。接下来,Callback 指令从栈中取 104 出三个值(由 P1 指定),然后形成一个记录数组,存储在内存单元 (memory cell) 中。然后, Callback 会挂起 VDBE 的执行,把控制权交给 sqlite3_step(),该函数将返回 SQLITE_ROW。 图 9-1 VDBE 的步骤:Open 和 Read 一旦 VDBE 创建了记录结构(该结构同样关联于语句(statement)句柄),程序就可以通过 sqlite3_column_xxx() 函数从记录结构内取出字段值。当下次调用 sqlite3_step()时,指令指针 会指向 Next 指令。Next 指令会把游标移向表的下一行,如果有其它的记录,它会跳到由 P2 指定的指令,在这里为指令 5(Recno 指令),创建一个新的记录结构,进入下一次循环。如 果已经没有其它记录可读,Next 不跳转,而是执行下一条指令,这里是 Close 指令。Close 指令会关闭游标,然后执行 Halt 指令,结束 VDBE 程序,并且 sqlite3_step()函数会返回 SQLITE_DONE。 程序开始与停止 前面介绍了程序的核心部分,现在来看看其余的指令,这 些指 令与 启 动和 初始化有关,见图 9-2。第一条指令是 Goto 指令,它是一条跳转指令,跳到 P2 处,本例中是跳到第 12 条指令。 指令 12 是 Transaction,它开始一个新的事务;然后执行下一条指令 VerifyCookie,它的主 要功能是确定 VDBE 程序编译后,数据库 schema 是否改变(即是否进行过更新操作)。这在 SQLite 中是一个很重要的概念,在 SQL 被 sqlite3_prepare()编译成 VDBE 代码至程序调用 sqlite3_step()执行字节码的这段时间内,另一个 SQL 命令可能会改变数据库模式(比如 ALTER TABLE、DROP TABLE 或 CREATE TABLE)。一旦发生这种情况,schema 版本就会 改变,之前编译的语句(statement)就会变得无效。当前的数据库 schema 信息记录在数据库文 件的根页中。类似地,每个语句都有一份用于比较的在编译时刻该模式的备份,VerifyCookie 的功能就是检查它们是否匹配,如果不匹配,就要采取适当的措施。 105 图 9-2 VDBE 的步骤:程序开始 语句的版本号由 VerifyCookie 的 P2 参数指定,将它与磁盘上的数据库 schema 版本号进行比 较。如果 schema 没有改变,两个版本号应该一致。如果不一致,则 VDBE 程序失效。在此 情况下,VerifyCookie 将会终止程序并返回 SQLITE_SCHEMA 错误。在此情况下,应用程 序需要重新编译 SQL 语句,基于新的 schema 版本生成新的 VDBE 程序。 如果两者匹配,会执行下一条指令 Goto;它会跳到程序的主要部分,即第一条指令,打开 表读取记录。这里有两点值得注意: (1)Transaction 指令本身不会获取锁。它的功能相当于 BEGIN,而共享锁实际是由 OpenRead 指令获取的。当事务关闭时释放锁,由 Halt 指令完成,它会进行扫尾工作。 (2) 语句对象(VDBE 程序)所需的存储空间在程序执行前就已经确定。这 缘 于 两 个 重 要 事 实 : 首 先 , 栈 的 深 度 不 会 比 指 令 的 数目还多。其次,内存单元(memory cell)的数量永远不会多于 指令的数量(通常少得多)。在 执 行 VDBE 程序之前,SQLite 可以计算出分配资源所需要的内 存。 指令的类型 VDBE 同时只会执行一条指令。每条指令都完成一项简单的任务,而且通常和该指令前面、 后面的指令有关。大体上来说,指令可分为三类: (1)处理值:这些指令通常完成算术运算,比如加、减和除;逻辑运算,比如与和或;还有 字符串操作。 (2)数据管理:这些指令操作在内存和磁盘上的数据。内存指令进行栈操作或者在内存单元 之间传递数据。磁盘操作指令控制 B-tree 和 pager 打开或操作游标,开始或结束事务,等等。 (3)流程控制:控制指令主要是有条件地或无条件地移动指令指针。 一旦熟悉了指令集,就不难明白 VDBE 程序是如何工作的。至少你可以了解如何使用栈来 为后面指令的执行做准备。 B-Tree 和 Pager 模型 B-tree 使 VDBE 执行查找、插入和删除的效率达到 O(logN),以及在 O(1)的效率下双向遍历 结果集。它是自平衡的,可 自 动 地 执 行 碎 片 整 理 和空间回收。B-tree 本身不会直接读写磁盘, 106 它仅仅维护着页(page)之间的关系。当 B-tree 需要页或者修改页时,它 就会调用 pager。当修 改页时,pager 保证原始页首先写入日志文件。当它完成写操作时,pager 根据事务状态决定 如何做。 数据库文件格式 数据库中所有的页从 1 开始顺序编号。一个数据库由多个多重 B-tree 构成——B-tree 用于每 一个表和索引。每个表和索引的第 1 个页(地址)称为根页。所有表和索引的根页都存储在 sqlite_master 表中。 数据库中第一个页(page 1)有点特殊,page 1的前100 个字节是一个对数据库文件进行描述 的特殊文件头。它包括库的版本、格式的版本、页大小、编码等所有创建数据库时设置的永 久性参数。有关这个特殊文件头的文档在 btree.c 中,page 1 也是 sqlite_master 表的根页。 页重用及回收 SQLite 利用一个空闲页链表(free list)完成页的循环使用。当一个页的所有记录都被删除时, 就被插入到该链表。当有新信息需要进入数据库时,临近的空闲页先被选中,当没有空闲页 时,才创建新的页(会增加文件的大小)。当运行 VACUUM 命令时,会清空空闲页链表,所 以数据库会缩小。本 质 上 它是在新的文件中重新建立数据库,而所正使用的页都被拷贝过去, 而空闲页链表不拷,结果就是一个新的,变小了的数据库。当数据库的 autovacuum 开启时, SQLite 不会使用空闲页链表,而且在每一次事务提交时自动压缩数据库。 B-Tree 记录 B-tree 中的页由 B-tree 记录组成,也 叫 做 payload(有效载荷)。每一个 B-tree 记录(或 payload) 有两个域:关键字域(key field)和数据域(data field)。关键字域就是 ROWID 的值,也就是每 个数据库表都会提供的关键字的值。从 B-tree 的角度,数据域可以是任何无结构的数据。数 据库的记录就保存在这些数据域中。B-tree 的任务就是排序和遍历,这仅需要关键字段。 Payload 的大小是不定的,这与内部的关键字和数据域有关。平均情况下,每个页一般包含 多个 payload,当然也可能一个 payload 占用几个页(当一个 payload 太大不能存在一个页内)。 B+树 B-tree 按关键字的顺序存储,在一个 B-tree 中所有的关键字必须唯一(这一点自动地由 ROWID 主键字段保证)。表采用 B+tree,B+tree 的内部结点不包含表数据(数据库记录)。图 9-3 是一个表的 B+tree 的示例: 107 图 9-3 B+tree 的组织(表) B+tree 中的根页(root page)和内部页(internal page)都是用来导航的,这些页的数据域都是指 向下级页的指针,仅仅包含关键字。所有的数据库记录都存储在叶子页(leaf page)内。在叶 节点一级,记录和页都是按照关键字的顺序排列的,这 使 B-tree 游标只使用页结点就能正向 和反向(水平地)遍历记录,并使遍历的效率(时间复杂度)可能达到 O(1)。 记录和字段 数据库记录位于叶子页的数据域,由 VDBE 管理(前面在介绍 Callback 命令时介绍过)。数据 库记录以二进制的形式存储,但有一定的数据格式,这种格式描述了记录中的所有字段。记 录格式是连续的字节流,其组成包括一个逻辑头(logical header)和一个数据区(data segment), 逻辑头包括“头大小(可变长的 64 位整数)”和一个数据类型(也是可变长的 64 位整数)数组, 数据类型用来描述存储在数据区的字段的类型,如图 9-4 所示。64 位整数用 Huffman 编码 实现。 图 9-4 记录结构 类型入口的数量与字段数量相等。类型数组与字段数组的元素按下标相对应。一个类型入口 表明它对应字段的数据类型和宽度。类型入口的可能取值及其含义在表 9-1 中列出。 表 9-1 字段类型值 类型值 含义 数据宽度 0 NULL 0 N in 1..4 有符号整数 N 5 有符号整数 6 6 有符号整数 8 7 IEEE 符点数 8 8-11 未使用 N/A 108 N>12 的偶数 BLOB (N-12)/2 N>13 的奇数 TEXT (N-13)/2 例如,取 episodes 表的第 1 条记录: sqlite> SELECT * FROM episodes ORDER BY id LIMIT 1; id season name 0 1 Good News Bad News 这条记录的内部记录格式如图 9-5 所示。 表 9-5 episodes 表的第 1 条记录 记录头长 4 字节。头的大小反映头内各要素都是单字节编码。第一个类型,对应 id 字段, 是一个 1 字节有符号整数。第二个类型,对应 season 字段,也是一个 1 字节有符号整数。 Name 字段的类型入口是一个大于 13 的奇数,表示它是一个 text 值,该值占(49-13)/2=18 个 字节。通过这些信息,VDBE 可以解析记录的数据段并取出独立的字段值。 层次数据组织 SQLite 的层次数据组织模型如图 9-6 所示。在 模型中,每层处理特定的数据单元。从下向上, 数据越来越结构化;从上往下,数据越来越无序。C-API 处理字段值,VDBE 处理记录,B-tree 处理健值的数据,pager 处理页,OS 接口处理二进制的数据和原始存储器。 图 9-6 模型和各层所对应的数据 Each module takes part in managing its own specific portion of the data in the database, and relies on the layer below it to supply it with a more crude form from which to extract its respective 109 pieces. 溢出页 如前所述,B-tree 记录具有可变的大小,而页的大小是固定的。这就有可能一个 B-tree 记录 比一个单独的页还要大。这时,超大的 B-tree 记录就溢出到由溢出页组成的链表上,如图 9-7 所示。 在图中,第 4 个页太大,B-tree 模块就创建一个溢出页来容纳它。如果一个溢出页还不够, 就再链接第 2 个。这实际上也是二进制大对象的处理方法。请记住:当你使用大的 BLOB 时,它实际上是存储在页链表中的。如果 BLOB 实在太大,链表就会很长,操作就会很低 效。这种情况下,将 BLOB 存储在一个外部文件中而在数据库中只保存其文件名也许更好 一些。 图 9-7 溢出页 B-Tree API B-Tree 模块有它自己的 API,它可以独立于 C API 使用。也就是说,如果你愿意,你可以把 它当作一个独立的运行库来使用,或在 SQLite 中直接存取库表。SQLite B-tree 模块的另一 个好处就是它本身支持事务。由 pager 处理的事务、锁和日志都是为 B-tree 服务的。根据功 能,可将 B-Tree API 分为以下几类: 访问和事务函数 包括: l sqlite3BtreeOpen: 打开一个新的数据库文件,返回一个 B-tree 对象。 l sqlite3BtreeClose: 关闭一个数据库。 l sqlite3BtreeBeginTrans: 开始一个新的事务。 110 l sqlite3BtreeCommit: 提交当前事务。 l sqlite3BtreeRollback: 回卷当前事务。 l sqlite3BtreeBeginStmt: 开始一个 statement 事务。 l sqlite3BtreeCommitStmt: 提交一个 statement 事务。 l sqlite3BtreeRollbackStmt: 回卷一个 statement 事务。 表函数 包括: l sqlite3BtreeCreateTable: 在数据库文件中创建一个新的、空的 B-tree。其参数决定是采 用表格式(B+tree)还是索引格式(B-tree)。 l sqlite3BtreeDropTable: 从数据库中删除一个 B-tree。 l sqlite3BtreeClearTable: 从 B-tree 中删除所有数据,但保持 B-tree 的结构。 游标函数 包括: l sqlite3BtreeCursor: Creates a new cursor pointing to a particular B-tree. Cursors can be either a read cursor or a write cursor. Read and write cursors may not exist in the same B-tree at the same time. l sqlite3BtreeCloseCursor: Closes the B-tree cursor. l sqlite3BtreeFirst: Moves the cursor to the first element in a B-tree. l sqlite3BtreeLast: Moves the cursor to the last element in a B-tree. l sqlite3BtreeNext: Moves the cursor to the next element after the one it is currently pointing to. l sqlite3BtreePrevious: Moves the cursor to the previous element before the one it is currently pointing to. l sqlite3BtreeMoveto: Moves the cursor to an element that matches the key value passed in as a parameter. If there is no match, leaves the cursor pointing to an element that would be on either side of the matching element, had it existed. 记录函数 包括: l sqlite3BtreeDelete: Deletes the record that the cursor is pointing to. l sqlite3BtreeInsert: Inserts a new element in the appropriate place of the B-tree. l sqlite3BtreeKeySize: Returns the number of bytes in the key of the record that the cursor is pointing to. l sqlite3BtreeKey: Returns the key of the record the cursor is currently pointing to. l sqlite3BtreeDataSize: Returns the number of bytes in the data record that the cursor is currently pointing to. l sqlite3BtreeData: Returns the data in the record the cursor is currently pointing to. 111 配置函数 包括: l sqlite3BtreeSetCacheSize: Controls the page cache size as well as the synchronous writes (as defined in the synchronous pragma). l 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. l sqlite3BtreeSetPageSize: Sets the database page size. l sqlite3BtreeGetPageSize: Returns the database page size. l sqlite3BtreeSetAutoVacuum: Sets the autovacuum property of the database. l sqlite3BtreeGetAutoVacuum: Returns whether the database uses autovacuum. l sqlite3BtreeSetBusyHandler: Sets the busy handler. 还有其它的函数,所有这些函数在 btree.h 和 btree.c 中都有很完备的文档,但上面列出的函 数可以使你建立一个总体印象。 编译器 前面已经介绍了 VDBE 以下直到 OS 层的各层次。下面介绍 VDBE 程序是怎么来的。编译 器的输入是一个单独的 SQL 命令,输出是最终经过优化的 VDBE 程序,这些工作在 3 个阶 段上完成:分词器(Tokenizer)、分析器(Parser)和代码生成器(Code Generator)。 分词器(Tokenizer) 编译的第一步是对 SQL 命令分词。分词器将一个命令分解成一串单独的词汇(token)。词可 以是有特定含义的一个字符或一个字符序列。每个词都有其关联的词类(token class),词类 是一个数字标识,表明这个词是什么。例如左括号的词类是 TK_LP,保留字 SELECT 的词 类是 TK_SELECT。所有词类在 parse.h 中定义。例如下面的 SQL 语句: SELECT rowid FROM foo where name='bar' LIMIT 1 ORDER BY rowid; 经分词器处理之后的部分结果在表 9-2 中给出。 表 9-2 一个分词后 SELECT 语句 文本 词类 动作 SELECT TK_SELECT 发给分析器 " " TK_SPACE 丢弃 Rowid TK_ID 发给分析器 " " TK_SPACE 丢弃 FROM TK_FROM 发给分析器 " " TK_SPACE 丢弃 112 foo TK_ID 发给分析器 " " TK_SPACE 丢弃 WHERE TK_WHERE 发给分析器 " " TK_SPACE 丢弃 name TK_ID 发给分析器 = TK_EQ 发给分析器 … 总之,分词器按照 SQL 的词法定义把它切分为一个一个的词,并传递给分析器(Parser)进行 语法分析(忽略空格)。 保留字 分词器是手工编写的(hand-coded),主要在 Tokenize.c 中实现。因为是手工代码,不是用自 动生成的代码来对 SQL 保留字分类。保留字在 keywordhash.h 文件中定义。这个文件是一个 最优化的、将所有 SQL 保留字压缩到可能最小的缓冲区,方法是公共的字符序列重叠存放。 SQLite 使用指明了每个保留字偏移量和大小的数组来识别保留字入口。这种方法是一种空 间优化的方法,有利于内嵌式的应用程序。一个生成了的缓冲区的例子如下: static int keywordCode(const char *z, int n){ static const char zText[537] = "ABORTABLEFTEMPORARYADDATABASELECTHENDEFAULTRANSACTIONATURALTER" "AISEACHECKEYAFTEREFERENCESCAPELSEXCEPTRIGGEREGEXPLAINITIALLYANALYZE" "XCLUSIVEXISTSTATEMENTANDEFERRABLEATTACHAVINGLOBEFOREIGNOREINDEX" "AUTOINCREMENTBEGINNERENAMEBETWEENOTNULLIKEBYCASCADEFERREDELETE" "CASECASTCOLLATECOLUMNCOMMITCONFLICTCONSTRAINTERSECTCREATECROSS" "CURRENT_DATECURRENT_TIMESTAMPLANDESCDETACHDISTINCTDROPRAGMATCH" "FAILIMITFROMFULLGROUPDATEIFIMMEDIATEINSERTINSTEADINTOFFSETISNULL" "JOINORDEREPLACEOUTERESTRICTPRIMARYQUERYRIGHTROLLBACKROWHENUNION" "UNIQUEUSINGVACUUMVALUESVIEWHERE"; The keywordhash.h file includes a routine sqlite3KeywordCode(), which allows the tokenizer to quickly match the keyword with its appropriate token class with minimal space. So, the tokenizer first tries to match a token with what it knows, and failing that, it resorts to sqlite3KeywordCode(), which will return either a keyword token class or a generic TK_ID. The tokenizer and parser work hand in hand, one token at a time. As the tokenizer resolves each token, it passes the token to the parser. The parser takes the tokens and builds a parse tree, which is a hierarchical representation of the statement. 分析器(Parser) SQLite 的语法分析器是用 Lemon 生成的(Lemon 是一个开源的 LALR(1)语法分析器的生成 器,SQLite 在使用时进行了定制)。该分析器用 parse.c 内定义的语法规则将一串词组织成层 次结构的分析树(parse tree)。 The parse tree is primarily composed of expressions and lists of expressions. An expression itself is a recursive structure that can contain subexpressions under it. For example, the WHERE clause 113 in a SELECT parse tree is represented by a single expression. The SELECT clause, on the other hand, is represented as a list of expressions; each expression is a column that will be returned in the result set. 例如,如下简单的 SQL 语句: SELECT rowid, name, season FROM episodes WHERE rowid=1 LIMIT 1 可以组织成如图 9-8 所示的分析树。 图 9-8 简化了的分析树 一旦语句经过分词和分析,分析树作为一种结果会传送给代码生成器。 代码生成器(Code Generator) 代码生成器是 SQLite 中最庞大、最复杂的部分。与其它模块不同,代码生成器没有定义明 确的接口,但它与分析器关系紧密。代码生成器由多个源文件组成,这些源文件大多针对特 定的 SQL 操作。例 如 ,生 成 SELECT 语句的代码在 select.c 中,其它的源文件包括 update.c、 insert.c、delete.c 和 trigger.c 等等。 代码生成器根据语法分析树生成 VDBE 程序。树的每一部分生成一个 VDBE 指令序列来完 成特定的任务。The values for the operands are taken from the data structures associated with the parse tree. 例如,下面是一个读操作中打开表的代码的生成实现: /* 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); 114 VdbeComment((v, "# %s", pTab->zName)); sqlite3VdbeAddOp(v, OP_SetNumColumns, iCur, pTab->nCol); } Sqlite3vdbeAddOp 函数有三个参数:(1)VDBE 实例(它将添加指令),(2)操作码(一条指令), (3)两个操作数。它增加一条指令到 VDBE 程序。上例中,sqlite3OpenTableForReading 增加 了 3 条指令,即图 9-1 中的指令 1~3,功能是打开一个表的 B-tree 用于读。为方便起见,将 图 9-1 中的指令序列重列于此: 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 优化 代码生成器不仅负责生成代码,也负责进行查询优化。优化是代码生成的一部分,主要的实 现位于 where.c 中。生成的 WHERE 子句通常被其它模块共享,比如 select.c、update.c 和 delete.c。这些模块调用 sqlite3WhereBegin()开始 WHERE 语句块的指令生成,然后将返回的 代码加入到它们自己的 VDBE 代码中,最后调用 sqlite3WhereEnd(),生成结束 WHERE 子 句代码的 VDBE 指令。程序的一般结构如图 9-9 所示: 图 9-9 WHERE 子句的 VDBE 代码的生成 115 优化发生在 sqlite3WhereBegin()阶段。它在已完成工作的基础上,寻找可以使用的索引,寻 找可以重写的表达式,等等。 为了能对优化先有一个感觉,我们从一个不含 WHERE 子句的简单 SELECT 语句开始,如 图 9-10 所示: 图 9-10 一个不含 WHERE 子句的 SELECT 语句

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档

相关文档