SQLite 第三版总览
SQLite 第三版主要主要介绍关于类库的一些变化, 包括:
- 介绍了一个关于数据库文件的更紧凑的格式.
- 若类型和 BLOB 支持.
- 支持 UTF-8 and UTF-16 文本.
- 用户定义的文件排列顺序.
- 64字节的行编号.
- 针对并发性的一些改良.
这篇文档简易的介绍了SQlite3.0版针对于2.8版的一些改进，适用于对SQlite2.8版比较了解的用户。
命名上的变化
在可预见的未来，错误修正这项功能将继续支持SQLite 2.8版。为了保证这两个版本可以共存，在3.0版本中，一些主要文件的名称和API的名称中都加了个”3“。例如, c程序内含文件的名称已经从"sqlite.h"改为"sqlite3.h".还有，用来和数据库一起操作的数据命令解释程序也从"sqlite.exe" 改为 "sqlite3.exe"。有了这些命名上的区别，SQLite 2.8版和SQLite 3.0版就可以同时安装在同一个系统下了。另外，名称上的区别也使同一个C程序可以同时和2.8，3.0两个版本同时相连并使用同一个类库。
新的文件格式
SQlite的数据库文件格式已被完全更新， 2.1版的格式和3.0版的格式是互不兼容的。比如，2.8版的SQLite 是无法读取3.0版的SQlite数据库文件的，同样，3.0版的SQLite也是无法读取2.8版的 SQlite数据库文件的。
如想把SQLite 2.8版的数据库转换成3.0版的数据库的话，你可以用一些现成的命令行操作，比如输入下面的命令：
sqlite OLD.DB .dump | sqlite3 NEW.DB
新的数据库文件格式使用B+树型数据表格。在B+树中，所有的数据都被存储在(数据结构中)树结构端结点，而不是既在(数据结构中)树结构端结点又在树的分支节点。B+树型数据表格具有很好的测量性，并且可以存储比较大的数据组。此外，传统的B-树也仍被应用在SQlite3.0的许多目录中。
新的文件格式可以存储的可变页的长度在512和32768字节之间。每页的文件长度都可以在页眉显示出来，所以从理论上来说，同一个类库可以读取不同长度的数据库，但实际上这一点还没有完全实现。
新的文件格式在磁盘映像中省略了没有被应用的区域。例如，目录仅仅显示B-树所存储的主要部分而不是显示所有的数据。也就是说，记录数据长度的区域被省略了。整数值，比如说关键字的长度和关键数据可以用变长量来编译，这样一来，最常见的数据就可以只用一两个字节来显示了，还有，如果需要的话，最高64字节的数据信息也是可以编译的。 在3.0版中，整数和浮动的点数据是用二进制来记录的，但在2.0版中，它们则是被转换成ASCII码的。所以，同一个数据库文件，如果是记录在SQlite3.0中，就可以比记录在SQlite2.8中少占用25%至30%的磁盘空间。
关于 SQLite3.0版所采用的B-树的文件格式具体细节，你可以点击 btree.c 看标题注释。
弱类型和BLOB技术支持
SQLite2.8在数据库内部可以用不同的文件格式处理文件，但是，如果想把信息写到硬盘或是通过API和其它数据相连，所有文件格式都必须被转换成ASCII文本格式。SQLite 3.0则不同，它可以把数据可内部的主要数据，也就是上文所提到的用变长量所表示的数据显示给用户，并在适当的时候用二进制的形式在磁盘中显示。为了支持BLOB，非ASCII格式的数据也可以在磁盘中显示。
SQLite 2.8版有一个特点，就是任何类型的数据都可以存储在任意的数据列中，不受数据列所要求存储的文件类型的限制。这个特性被保留在3.0版中并有所改进。虽然数据文件的格式决定了文件的类型，每列所存储的文件都要有规定的属性，但在3.0中每个列都是可以存储不同类型的数据的。当数据被存入一个数据列的时候，这个列将尽全力把存入的数据的文件格式转换成该列所要求的文件格式。所有的SQL数据库引擎都是这样的。所不同的是，SQLite 3.0 将存储数据即使转换该数据的文件类型是不可能的。
例如，一个数据列要求所存储的文件的类型是 "INTEGER"，你输入一个字符串，这个列将自动检查所输入的字符串是否是数字，如果它确实看起来像是数字，字符串将被转换成数字，然后，如果这个数字没有分数部分的话，它将被转换成整数存储起来。但是如果这个字符串不是一个规则的数字的话，它将仍被保存为一个字符串。如果一个列要求所存储的文件的类型是"TEXT"的话，在存储数据之前，列将尝试把数字转换成 ASCII-Text来表示数据。但是，BLOBS在文本列仍然被保存成BLOBS，因为在通常情况下你是不可能把BLOB转换成文本的。
在大部分的其他的SQL数据库引擎中，数据类型是和他该数据所处列的类型紧密相连的，但在SQlite3.0中，一个数据的类型只和自身有关，和所属列所要求的数据类型没有任何关联。 Paul Graham 在他的 ANSI Common Lisp一书中称这个特性"manifest typing"为"弱类型". 其他的作者对"manifest typing"有不同的定义，我们不要混淆，不管它的名称是什么，我们知道它是3.0 的一个特性就好。
关于SQLite3.0版的数据类型的更多内容，点击separately.
支持UTF-8和UTF-16
在SQLite 3.0中，API中的程序可以识别UTF-8和UTF-16文本，并不改变主机中原来字节的顺序。每个数据库文件都可以用UTF-8, UTF-16BE (big-endian)和 UTF-16LE (little-endian)两种方式处理文本。在磁盘文件的内部，到处可见到同样的文本显示。如果数据库文件中所记载的文本显示（在文件标题中）和接口程序所要求的文本显示不相符合的话，文本将被转换成当前所要求的文本格式。经常转换文本格式对于程序来说是非常麻烦的，所以建议程序员在一个应用程序中自始至终使用一种文本格式。
当前，在执行SQLite的时候，SQL的语法只能识别UTF-8文本，所以如果是UTF-16文本的话将被转换成UTF-8文本。当然这只是程序执行的问题，在不远的将来，更新版本的SQLite将完全有实力识别用SQL所编译的UTF-16文本。
当开发用户所定义的SQL函数和分类排序功能的时候，不管是UTF-8，UTF-16be或是 UTF-16le 文本格式，每个函数和分类排序都能清晰的识别文件。程序所执行的每一步都被完整无误的记录下来以便编译。当SQL函数或分类排序要求相应的格式，但当前版本的文件编译又无能为力的时候，文本将自动被转换。像以前一样，这种转换需要一定的时间，所以建议程序员挑选一种编码并自始至终使用以避免不必要的文本格式转换。
不只是对于一个完整的文本，即使是某个文本中的格式不规则的字符串和格式不规则的UTF-8和UTF-16文本行，SQLite也要竭尽全力把他们编译成规则的文件格式。所以如果你想存储IS08859数据的话，你可以使用UTF-8接口程序。只要不使用UTF-16分类排序或SQL函数，文本的字节顺序将不会被变更。
用户定义的分类排序
所谓的分类排序是指文本的排列顺序。当SQLite 3.0分类的时候（比如使用比较操作符"<" or ">="），它是按照数据类型分类的。
- 首先把空行分类
- 按数字的顺序把数值分类
- 再把文本分类
- 最后把BLOBs分类
当比较两个文本行的时候使用分类排序。分类排序不改变空行，数字和BLOBS的顺序，它只改变文本的顺序。
分类排序功能是作为一个函数来执行的，它使用两个字符串来表示，如果第一个字符串小于第二个字符串，则显示为负数，如等于则显示为零，如大于则显示为正数。 SQLite 3.0版设置了一个名叫BINARY的独立的内置分类排序功能，它是依靠标准C类库中的memcmp（）程序来实现的。 BINARY分类排序功能很好的支持英文文档。对于其他语言或者在其他场合，程序会根据需要来选择合适的分类排序功能。
使用哪种分类排序是由SQL中的COLLATE语句来决定的。一个COLLATE语句可分辨一种表格，它可以定义并默认表格中的每列该使用哪种分类排序，目录的每个区段该使用哪种分类排序，或SELECTED语句中ORDER BY 字句该使用哪种分类排序。按照计划，SQLite还将使用标准的CAST()句法来具体定义每个分类排序。
64字节的行编号
表格中的每一行都有行标识符。如果表格中的某一列被定义为整数初级键控，那么那一列就成为其所属行的别名。但不管有没有定义这样一个列，每个行都是有行编号的。
在SQLite 3.0版中，行编号是64字节的带符号整数。而在SQLite2.8中，行编号是32字节的。
为了占有最小的存储空间，64字节的行编号被存储为变长量。行编号在0和127之间的都只占有一个字节的空间。行编号在128和16383之间的只占用2个字节的空间。 行编号在16383和2097152之间的占有三个字节的空间。依此类推。也可以使用负行编号，担负行编号通常要占用9个字节的空间， 所以建议不要使用。SQLite所自动生成的行编号通常不是负行编号。
改良的并发性
SQLite 2.8允许多个进程同时读取或一个进程读取，但不允许读取和写入同时进行。SQLite 3.0则允许一个进程写入和其它多个进程同时读取。为了确认所更新的数据，输入者必须为数据库中的简短区间设置一个额外的锁，但这个锁不再像以前那样必须贯穿在整个输入过程中。可以点击找到关于SQLite3.0 版中的锁的具体信息。
SQLite中也有一个在格式上有局限性的表格极的锁. 如果每个表格都存储了不同类型的文件，这些文件可以被连接到主数据库（使用ATTACH命令），这个被整合在一起的数据库依然可以作为一个整体来运行。但各个文件将按照需要生成各自的锁。所以如果你把“数据库”重新定义成两个或更多数据库文件，那么在同一个数据库中同时对两个进程写入则是完全可能的。为了更好的支持这项功能，同时处理两个或更多的ATTACHed型数据库是最基本的了。
致谢
SQLite3.0版的成功发行得利于AOL开发者的大力支持和开源软件的帮助。
SQLite第三版中的数据类型
1.存储类别
第二版把所有列的值都存储成ASCII文本格式。第三版则可以把数据存储成整数和实数,还可以存储BLOB数据.
Each value stored in an SQLite数据库中存储的每个值都有一个属性,都属于下面所列类中的一种,(被数据库引擎所控制)
· 空.这个值为空值
· 整数.值被标识为整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8.
· 实数. 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号.
· 文本. 值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
· BLOB. 值是BLOB数据,如何输入就如何存储,不改变格式.
像SQLite2.0版一样,在3.0版中,除了INTEGER PRIMARY KEY,数据库中的任何列都可以存储任何类型的数据.这一规则也有例外,在下面的"严格相似模式"中将描述.
输入SQLite的所有值,不管它是嵌入 SQL语句中的文字还是提前编译好的绑定在SQL语句中的值,在SQL语句执行前都被存储为一个类.在下面所描述的情况下,数据库引擎将在执行时检查并把值在数字存储类(整数和实数)和文本类间转换.
存储的类别最初被分类为如下:
· 具体的值比如SQL语句部分的带双引号或单引号的文字被定义为文本,如果文字没带引号并没有小数点或指数则被定义为整数,如果文字没带引号但有小数点或指数则被定义为实数,如果值是空则被定义为空值.BLOB数据使用符号X'ABCD'来标识.
· Values supplied using the 被输入的值使用sqlite3_bind_* APIs的被分类一个存储等级,这等级是和原来的类基本相一致的. (比如sqlite3_bind_blob()绑定一个BLOB的值).
值的分类是SQL分等级操作的结果,决定于最远的操作表达式.用户定义的功能也许会把值返回任意的类.在编译的时候来确定表达式的存储类基本是不可能的.
2. 列之间的亲和性
在SQLite3.0版中,值被定义为什么类型只和值自身有关,和列没有关系,和变量也没有关系. (这有时被称作 弱类型.)所有其它的我们所使用的数据库引擎都受静态类型系统的限制,其中的所有值的类是由其所属列的属性决定的,而和值无关.
为了最大限度的增加SQLite数据库和其他数据库的兼容性,SQLite支持列的"类型亲和性". 列的亲和性是为该列所存储的数据建议一个类型.我们要注意是建议而不是强迫.在理论上来讲,任何列依然是可以存储任何类型的数据的. 只是针对某些列,如果给建议类型的话,数据库将按所建议的类型存储.这个被优先使用的数据类型则被称为"亲和类型".
在SQLite3.0版中,数据库中的每一列都被定义为以下亲和类型中的一种:
一个具有类型亲和性的列按照无类型,文本,或BLOB存储所有的数据.如果数字数据被插入一个具有文本类型亲和性的列,在存储之前数字将被转换成文本.
一个具有数字类型亲和性的列也许使用所有的五个存储类型存储值.当文本数据被插入一个数字列时,在存储之前,数据库将尝试着把文本转换成整数或实数.如果能成功转换的话,值将按证书活实数的类型被存储. 如果不能 成功转换的话,值则只能按文本类型存储了,而不会被转换成无类型或BLOB类型来存储.
一个具有整数亲和力的列在转换方面和具有数字亲和力的列是一样的,但也有些区别 ,比如没有浮动量的实值(文本值转换的值)被插入具有整数亲和力的列时,它将被转换成整数并按整数类型存储.
一个具有无类型亲和力的列不会优先选择使用哪个类型.在数据被输入前它不会强迫数据转换类型.
2.1 列的亲和性的决定
一个列的亲和类型是由该列所宣称的类型决定的.遵守以下规则:
1. 如果数据类型包括字符串"INT"那么它被定义成具有整数亲和性.
2. 如果列中的数据类型包括以下任何的字符串 "CHAR", "CLOB", or "TEXT" 那么这个列则具有文本亲和性.要注意VARCHAR类型包括字符串"CHAR"因此也具有文本类型亲和性.
3. 如果一个列的数据类型包括字符串"BLOB"或者如果数据类型被具体化了,那么这个列具有无类型亲和性.
4. 否则就具有数字类型亲和性.
如果表格使用If "CREATE TABLE
AS SELECT..."语句生成的,那么所有的列则都没有具体的数据类型,则没有类型亲和性.
2.2 列的亲和性的例子
CREATE TABLE t1(
t TEXT,
nu NUMERIC,
i INTEGER,
no BLOB
);
-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, TEXT
INSERT INTO t1 VALUES('500.0', '500.0', '500.0', '500.0');
-- Storage classes for the following row:
-- TEXT, REAL, INTEGER, REAL
INSERT INTO t1 VALUES(500.0, 500.0, 500.0, 500.0);
3.比较表达式
像SQLite2.0版一样,3.0版的一个特性是二进制比较符'=', '<', '<=', '>=' and '!=',一个操作'IN'可以测试固定的成员资格, 三重的比较操作符'BETWEEN'.
比较的结果决定于被比较的两个值的存储类型。遵循以下规则：
· 一个具有空存储类型的值被认为小于任何值（包括另外一个具有空存储类型的值）。
· 一个整数值或实数值小于任何文本值和BLOB值。当一个整数或实数和另一个整数或实数相比较的时候，则按照实际数值来比较。
· 一个文本值小于BLOB值。当两个文本值相比较的时候，则用C语言类库中的memcmp()函数来比较。然而，有时候也不是这样的，比如在下面所描述的“用户定义的整理顺序”情况下。
· 当两个BLOB文本被比较的时候，结果决定于memcmp()函数。
在开始比较前，SQLite尝试着把值在数字存储级（整数和实数）和文本之间相互转换。下面列举了关于如何比较二进制值的例子。在着重号below中使用的表达式可以表示SQL标量表达式或是文本但不是一个列值。
· 当一个列值被比拟为表达式结果的时候，在比较开始前，列的亲和性将被应用在表达结果中。
· 当两个列值比较的时候，如果一个列有整数或数字亲和性的时候，而另外一列却没有，那么数字亲和性适用于从非数字列提取的任何具有文本存储类型的值. P>
· 当比较两个表达式的结果时,不发生任何转换,直接比较结果.如果一个字符串和一个数字比较, 数字总是小于字符串.
在SQLite中, 表达式"a BETWEEN b AND c"等于表达式 "a >= b AND a <= c",在比较表达式时,a可以是具有任何亲和性.
表达式 "a IN (SELECT b ....)" 在比较时遵循上面所提到的三条规则,是二进制比较.(例如, 在一个相似的样式 "a = b"). 例如,如果'b'是一个列值, 'a' 是一个表达式,那么,在开始比较前,'b'的亲和性就被转换为'a'的亲和性了.
SQLite把表达式 "a IN (x, y, z)" 和 "a = z OR a = y OR a = z"视为相等.
3.1 比较例子
CREATE TABLE t1(
a TEXT,
b NUMERIC,
c BLOB
);
-- Storage classes for the following row:
-- TEXT, REAL, TEXT
INSERT INTO t1 VALUES('500', '500', '500');
-- 60 and 40 are converted to '60' and '40' and values are compared as TEXT.
SELECT a < 60, a < 40 FROM t1;
1|0
-- Comparisons are numeric. No conversions are required.
SELECT b < 60, b < 600 FROM t1;
0|1
-- Both 60 and 600 (storage class NUMERIC) are less than '500'
-- (storage class TEXT).
SELECT c < 60, c < 600 FROM t1;
0|0
4. 运算符
所有的数学运算符(所有的运算符而不是连锁作用标记符"||")运算对象首先具有数字亲和性, 如果一个或是两个都不能被转换为数字那么操作的结果将是空值。
对于连接作用操作符，所有操作符将首先具有文本亲和性。如果其中任何一个操作符不能被转换为文本（因为它是空值或是BLOB）连接作用操作符将是空值。
5. 分类，排序，混合挑选
当用子句ORDER挑选值时，空值首先被挑选出来, 然后是整数和实数按顺序被挑选出来, 然后是文本值按memcmp()顺序被挑选出来, 最后是BLOB值按memcmp()顺序被挑选出来.在挑选之前, 没有存储类型的值都被转换了.
When grouping values with the 当用GROUP BY子句给值分组时,具有不同存储类型的值被认为是不同的, 但也有例外, 比如,一个整数值和一个实数值从数字角度来说是相等的,那么它们则是相等的.用GROUP by 子句比较完后,值不具有任何亲和性.
混合挑选操作符UNION, INTERSECT and EXCEPT 在值之间实行绝对的比较,同样的亲和性将被应用于所有的值,这些值将被存储在一个单独的具有混合SELECT的结果组的列中. 被赋予的亲和性是该列的亲和性,这个亲和性是由剩下的大部分的混合SELECTS返回的,这些混合SELECTS在那个位置上有列值(而不是其它类型的表达式). 如果一个给定的混合SELECT列没有SELECTS的量, 那么在比较前,该列的值将不具有任何亲和性.
6. 其它亲和性模式
以上的部分所描述的都是数据库引擎在正常亲和性模式下所进行的操作, SQLite将描述其它两种亲和性模式,如下:
· 严格亲和性模式.在这种模式下,如果需要值之间相互转换数据存储类型的话,数据库引擎将发送错误报告,当前语句也将会重新运行.
· 无亲和性模式.在这种模式下,值的数据存储类型不发生转换.具有不同存储类型的值之间不能比较,但整数和实数之间可以比较.
7.用户定义的校对顺序
By default, when 当SQLite比较两个文本值的时候,通过系统设定,不管字符串的编码是什么,用memcmp()来比较. SQLite第三版允许用户提供任意的函数来代替memcmp(),也就是用户定义的比较顺序.
除了系统预设的BINARY比较顺序,它是用memcmp()函数比较,SQLite还包含了两个额外的内置比较顺序函数, NOCASE和REVERSE:
- BINARY -使用memcmp()比较字符串数据, 不考虑文本编码.
- REVERSE -用倒序比较二进制文本.
- NOCASE - 和二进制一样,但在比较之前,26位的大写字母盘要被折合成相应的小写字母盘.
7.1 分配比较顺序
每个表格中的每个列都有一个预设的比较类型.如果一个比较类型不是二进制所要求的,比较的子句将被具体化为 列的定义 来定义该列.
当用SQLite比较两个文本值时,比较顺序将按照以下的规则来决定比较的结果.文档的第三部分和第五部分描述在何种场合下发生这种比较.
对于二进制比较符(=, <, >, <= and >=),如果每个操作数是一列的话,那么该列的默认比较类型决定于所使用的比较顺序. 如果两个操作数都是列的话,那么左边的操作数的比较类型决定了所要使用的比较顺序.如果两个操作数都不是一列,将使用二进制来比较.
表达式"x BETWEEN y and z"和 "x >= y AND x <= z"是相同的. 表达式"x IN (SELECT y ...)" 和表达式 "x = y" 使用同样的方法来操作,这是为了决定所要使用的比较顺序.如果X是一列或者二进制的,则"x IN (y, z ...)" 形式的表达式所使用的比较顺序是X的默认的比较类型.
ORDER BY clause that is part of a SELECT statement may be assigned a collation sequence to be used for the sort operation explicitly. In this case the explicit collation sequence is always used. Otherwise, if the expression sorted by an ORDER BY clause is a column, then the default collation type of the column is used to determine sort order. If the expression is not a column, then the BINARY collation sequence is used.
7.2 比较顺序的例子
下面的例子介绍了The examples below identify the collation sequences that would be used to determine the results of text comparisons that may be performed by various SQL statements. Note that a text comparison may not be required, and no collation sequence used, in the case of numeric, blob or NULL values.
CREATE TABLE t1(
a, -- default collation type BINARY
b COLLATE BINARY, -- default collation type BINARY
c COLLATE REVERSE, -- default collation type REVERSE
d COLLATE NOCASE -- default collation type NOCASE
);
-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = b) FROM t1;
-- Text comparison is performed using the NOCASE collation sequence.
SELECT (d = a) FROM t1;
-- Text comparison is performed using the BINARY collation sequence.
SELECT (a = d) FROM t1;
-- Text comparison is performed using the REVERSE collation sequence.
SELECT ('abc' = c) FROM t1;
-- Text comparison is performed using the REVERSE collation sequence.
SELECT (c = 'abc') FROM t1;
-- Grouping is performed using the NOCASE collation sequence (i.e. values
-- 'abc' and 'ABC' are placed in the same group).
SELECT count(*) GROUP BY d FROM t1;
-- Grouping is performed using the BINARY collation sequence.
SELECT count(*) GROUP BY (d || '') FROM t1;
-- Sorting is performed using the REVERSE collation sequence.
SELECT * FROM t1 ORDER BY c;
-- Sorting is performed using the BINARY collation sequence.
SELECT * FROM t1 ORDER BY (c || '');
-- Sorting is performed using the NOCASE collation sequence.
SELECT * FROM t1 ORDER BY c COLLATE NOCASE;