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;