SQLite的使用介紹，SQLite使用介紹

SQLite的使用介紹，SQLite使用介紹

一、為什麼要使用SQLite

SQLite是目前最流行的開源嵌入式資料庫,和很多其他嵌入式儲存引擎相比(NoSQL),如BerkeleyDB、MemBASE等,SQLite可以很好的支援關係型資料庫所具備的一些基本特徵,如標準SQL文法、事務、資料表和索引等。事實上,儘管SQLite擁有諸多關係型資料庫的基本特徵,然而由於應用情境的不同,它們之間並沒有更多的可比性。下面我們將列舉一下SQLite的主要特徵:

1. 管理簡單,甚至可以認為無需管理。
2. 操作方便,SQLite產生的資料庫檔案可以在各個平台無縫移植。
3. 可以非常方便的以多種形式嵌入到其他應用程式中,如靜態庫、動態庫等。

4. 易於維護。

綜上所述,SQLite的主要優勢在於靈巧、快速和可靠性高。SQLite的設計者們為了達到這一目標,在功能上作出了很多關鍵性的取捨,與此同時,也失去了一些對RDBMS關鍵性功能的支援,如高並發、細粒度存取控制(如行級鎖)、豐富的內建函數、預存程序和複雜的SQL語句等。正是因為這些功能的犧牲才換來了簡單,而簡單又換來了高效性和高可靠性。 

    我們選擇使用SQLite的其他理由

　　如果你考慮在公司的商業軟體項目中使用SQLite。還需要根據“如何選擇開源項目”裡面提到的幾個參考因素，再評估一下。
1.授權協議（License）
 SQLite使用的是Public Domain協議，這是最爽一種，可以放心大膽地用。
2.使用者的普及程度
最近這幾年，使用SQLite的人越來越多（從Google Trends可以反應出來）。包括一些大公司也開始把它整合到產品中（比如Google的Gears、Apple的Safari、Adobe的AIR）。這說明它的健壯性、穩定性等方面不會有太大問題。
3.開發的活躍程度
如果到SQLite的Change Log上大致瞭解一下，可以看出最近5年基本上每1-2個月都會有更新。說明開發的活躍度還是非常高的。
從上述幾個非技術因素來看，SQLite用於商業公司的軟體項目還是非常靠譜的。

二、SQLite 優缺點

     A.SQLite技術上的優點和特性

　　SQLite是一個輕量級、跨平台的關係型資料庫。既然號稱關係型資料庫，支援SQL92標準中常用的玩意兒（比如視圖、事務、觸發器等）就是理所當然的了，咱今天就不細說了。今天主要聊聊一些有點特色的玩意兒。

　　1.輕量級
　　先說它的第一個特色：輕量級。想必SQLite的作者很看重這個特性，連它的Logo都是用的“羽毛”，來顯擺它的輕飄飄。
SQLite和C/S模式的資料庫軟體不同，它是進程內的資料庫引擎，因此不存在資料庫的用戶端和伺服器。使用SQLite一般只需要帶上它的一個動態庫，就可以享受它的全部功能。而且那個動態庫的尺寸也挺小，3.6.27版本也就幾百K

　　3.綠色軟體
SQLite的另外一個特點是綠色：它的核心引擎本身不依賴第三方的軟體，使用它也不需要“安裝環境”（如：Oledb等）。所以在部署的時候能夠省去不少麻煩。

　　4.單一檔案
所謂的“單一檔案”，就是資料庫中所有的資訊（比如表、視圖、觸發器、等）都包含在一個檔案內。這個檔案可以copy到其它目錄或其它機器上，也照用不誤。

　　5.跨平台/可移植性
　　如果光支援主流作業系統（Windows,Linux），那就沒啥好吹噓的了。除了主流作業系統，SQLite還支援了很多小型嵌入式系統，比如Android、Windows Mobile、Symbin、Palm、VxWorks等，也就是說iPhone，Android等手機上都可以用。

　　6.記憶體資料庫（in-memory database）
這年頭，記憶體越來越便宜，很多普通PC都開始以GB為單位來衡量記憶體（伺服器就更甭提了）。這時候，SQLite的記憶體資料庫特性就越發顯得好用。
SQLite的API不區分當前操作的資料庫是在記憶體還是在檔案（對於儲存介質是透明的）。所以如果你覺得磁碟I/O有可能成為瓶頸的話，可以考慮切換為記憶體方式。切換的時候，操作SQLite的代碼基本不用大改，只要在開始時把檔案Load到記憶體，結束時把記憶體的資料庫Dump迴文件就OK了。在這種情況下，前面提到的“online backup API”就派上用場了，聰明的同學應該明白我為啥這麼期待backup功能了吧？

 

B. 技術上的缺點和不足

　　前面光聊了特性和優點，為了避免槍手寫軟文的嫌疑，再來說說SQLite的一些缺點。列位看官將來如果想用它，這些缺點要權衡一下。

　　1.並發訪問的鎖機制
SQLite在並發（包括多進程和多線程）讀寫方面的效能一直不太理想。資料庫可能會被寫操作獨佔，從而導致其它讀寫操作阻塞或出錯。

　　2.SQL標準支援不全
在它的官方網站上，具體列舉了不支援哪些SQL92標準。我個人感覺比較不爽的是不支援外鍵約束。

　　3.網路檔案系統（以下簡稱NFS）
　　有時候需要訪問其它機器上的SQLite資料庫檔案，就會把資料庫檔案放置到網際網路共用目錄上。這時候你就要小心了。當SQLite檔案放置於NFS時，在並發讀寫的情況下可能會出問題（比如資料損毀）。原因據說是由於某些NFS的檔案鎖實現上有Bug。

三、程式設計語言介面

　　SQLite支援很多種語言的編程介面。這對於我這種喜歡混用多種程式設計語言的人來說，是很爽的。下面我大概介紹一下。

　　◇C/C++
　　由於SQLite本身是C寫的，它內建的API也是C介面的。所以C/C++用起來最直接了。假如你不喜歡面向過程的C API風格，可以另外找個C++的封裝庫。想重新發明輪子的同學，也可以自己封裝一個。
◇Java
如果要用Java訪問SQLite，可以通過SQLite的JDBC驅動，或者通過專門的SQLite封裝庫。我個人建議走JDBC方式，萬一將來要換資料庫，代碼就不用大改。
◇Python
pysqlite是Python操作SQLite的首選。從Python 2.5開始，它已經被整合到Python的標準庫中。看來Python社區還是蠻喜歡SQLite嘛。
◇.net
對於喜歡.net的同學，可以通過System.Data.SQLite來訪問。
◇Ruby
Ruby可以通過SQLite-Ruby操作SQLite資料庫，不過我沒用過。
◇Perl
在CPAN上有DBD::SQLite，不過我也沒用過。

*****下面詳細介紹C/C++介面，畢竟其他語言庫的介面都是基於C實現的*****

A、概述:

在SQLite提供的C/C++介面中,其中5個APIs屬於核心介面。在這篇部落格中我們將主要介紹它們的用法,以及它們所涉及到的核心SQLite對象,如database_connection和prepared_statement。相比於其它資料庫引擎提供的APIs,如OCI、MySQL API等,SQLite提供的介面還是非常易於理解和掌握的。

B、核心對象和介面:

1. 核心對象:在SQLite中最主要的兩個對象是,database_connection和prepared_statement。database_connection對象是由sqlite3_open()介面函數建立並返回的,在應用程式使用任何其他SQLite介面函數之前,必須先調用該函數以便獲得database_connnection對象,在隨後的其他APIs調用中,都需要該對象作為輸入參數以完成相應的工作。至於prepare_statement,我們可以簡單的將它視為編譯後的SQL語句,因此,所有和SQL語句執行相關的函數也都需要該對象作為輸入參數以完成指定的SQL操作。

2. 核心介面:
1).sqlite3_open

上面已經提到過這個函數了,它是操作SQLite資料庫的入口函數。該函數返回的database_connection對象是很多其他SQLite APIs的控制代碼參數。注意,我們通過該函數既可以開啟已經存在的資料庫檔案,也可以建立新的資料庫檔案。對於該函數返回的database_connection對象,我們可以在多個線程之間共用該對象的指標,以便完成和資料庫相關的任意操作。然而在多線程情況下,我們更為推薦的使用方式是,為每個線程建立獨立的database_connection對象。對於該函數還有一點也需要額外說明,我們沒有必要為了訪問多個資料庫而建立多個資料庫連接對象,因為通過SQLite內建的ATTACH命令可以在一個串連中方便的訪問多個資料庫。

2).sqlite3_prepare

該函數將SQL文本轉換為prepared_statement對象,並在函數執行後返回該對象的指標。事實上,該函數並不會評估參數指定SQL語句,它僅僅是將SQL文本初始化為待執行的狀態。最後需要指出的,對於新的應用程式我們可以使用sqlite3_prepare_v2介面函數來替代該函數以完成相同的工作。

3).sqlite3_step

該函數用於評估sqlite3_prepare函數返回的prepared_statement對象,在執行完該函數之後,prepared_statement對象的內部指標將指向其返回的結果集的第一行。如果打算進一步迭代其後的資料行,就需要不斷的調用該函數,直到所有的資料行都遍曆完畢。然而對於INSERT、UPDATE和DELETE等DML語句,該函數執行一次即可完成。

4).sqlite3_column

該函數用於擷取當前行指定列的資料,然而嚴格意義上講,此函數在SQLite的介面函數中並不存在,而是由一組相關的介面函數來完成該功能,其中每個函數都返回不同類型的資料,如:

sqlite3_column_blob
sqlite3_column_bytes
sqlite3_column_bytes16
sqlite3_column_double
sqlite3_column_int
sqlite3_column_int64
sqlite3_column_text
sqlite3_column_text16
sqlite3_column_type
sqlite3_column_value
sqlite3_column_count

其中sqlite3_column_count函數用於擷取當前結果集中的欄位資料。下面是使用

sqlite3_step和sqlite3_column函數迭代結果集中每行資料的虛擬碼,注意這裡作為範例程式碼簡化了對欄位類型的判斷:

int fieldCount = sqlite3_column_count(...);
while (sqlite3_step(...) <> EOF) {

for (int i = 0; i < fieldCount; ++i) {
int v = sqlite3_column_int(...,i);

}}

5).sqlite3_finalize

該函數用於銷毀prepared statement對象,否則將會造成記憶體泄露。

6).sqlite3_close

該函數用於關閉之前開啟的database_connection對象,其中所有和該對象相關的prepared_statements對象都必須在此之前先被銷毀。

C、參數綁定:

和大多數關係型資料庫一樣,SQLite的SQL文本也支援變數綁定,以便減少SQL語句被動態解析的次數,從而提高資料查詢和資料操作的效率。要完成該操作,我們需要使用SQLite提供的另外兩個介面APIs,sqlite3_reset和sqlite3_bind。

見如下樣本:

void test_parameter_binding() {

//1. 不帶參數綁定的情況下插入多條資料。char strSQL[128];
for (int i = 0; i < MAX_ROWS; ++i) {

sprintf(strSQL,"insert into testtable values(%d)",i);sqlite3_prepare_v2(..., strSQL);sqlite3_step(prepared_stmt);sqlite3_finalize(prepared_stmt);

}

//2. 參數綁定的情況下插入多條資料。
string strSQLWithParameter = "insert into testtable values(?)";sqlite3_prepare_v2(..., strSQL);
for (int i = 0; i < MAX_ROWS; ++i) {

sqlite3_bind(...,i);sqlite3_step(prepared_stmt);sqlite3_reset(prepared_stmt);

}sqlite3_finalize(prepared_stmt);

}

這裡首先需要說明的是,SQL語句"insert into testtable values(?)"中的問號(?)表示參數變數的預留位置,該規則在很多關係型資料庫中都是一致的,因此這對於資料庫移植操作還是比較方便的。通過上面的範例程式碼可以顯而易見的看出,參數綁定寫法的執行效率要高於每次產生不同的SQL語句的寫法,即2)在效率上要明顯優於1),下面是針對這兩種寫法的具體比較:

1. 單單從程式表面來看,前者在for迴圈中執行了更多的任務,比如字串的填充、SQL語句的prepare,以及prepared_statement對象的釋放。

2. 在SQLite的官方文檔中明確的指出,sqlite3_prepare_v2的執行效率往往要低於sqlite3_step的效率。

3. 當插入的資料量較大時,後者帶來的效率提升還是相當可觀的。 

四、SQL常用函數：

<span style="font-size:14px;">//1.開啟資料庫int sqlite3_open(    const char *filename,   // 資料庫的檔案路徑    sqlite3 **ppDb          // 資料庫執行個體);//2.執行任何SQL語句int sqlite3_exec(    sqlite3*,                                  // 一個開啟的資料庫執行個體    const char *sql,                           // 需要執行的SQL語句    int (*callback)(void*,int,char**,char**),  // SQL語句執行完畢後的回調    void *,                                    // 回呼函數的第1個參數    char **errmsg                              // 錯誤資訊);//3.檢查SQL語句的合法性（查詢前的準備）int sqlite3_prepare_v2(    sqlite3 *db,            // 資料庫執行個體    const char *zSql,       // 需要檢查的SQL語句    int nByte,              // SQL語句的最大位元組長度    sqlite3_stmt **ppStmt,  // sqlite3_stmt執行個體，用來獲得資料庫資料    const char **pzTail);//4.查詢一行資料int sqlite3_step(sqlite3_stmt*); // 如果查詢到一行資料，就會返回SQLITE_ROW//5.利用stmt獲得某一欄位的值（欄位的下標從0開始）double sqlite3_column_double(sqlite3_stmt*, int iCol);  // 浮點數據int sqlite3_column_int(sqlite3_stmt*, int iCol); // 整型資料sqlite3_int64 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);  // 字串資料</span>

五、其他資源

SQLite資源地址：

SQLite的官方首頁：
http://www.sqlite.org/

SQLite中文站：
http://www.sqlite.com.cn/

System.Data.SQLite：
http://sqlite.phxsoftware.com/

sql學習筆記之 嵌入式資料庫(sqlite,firebird)
http://www.cnblogs.com/ljzforever/archive/2010/03/09/1681453.html

sql 學習筆記，博主寫的很詳細

http://www.cnblogs.com/stephen-liu74/category/348367.html

SQLite GUI圖形管理工具（推薦）：

Navicat for SQLite（匯入，匯出功能強大，功能實用，操作直觀，有些小缺陷，更新速度還行）：
http://www.navicat.com/en/download/download.html