概述
SQLite介紹
自幾十年前出現的商業應用程序以來，數據庫就成為軟件應用程序的主要組成部分。正與數據庫管理系統非常關鍵一樣，它們也變得非常龐大，并占用了相當多的系統資源，增加了管理的復雜性。隨著軟件應用程序逐漸模塊模塊化，一種新型數據庫會比大型復雜的傳統數據庫管理系統更適應。嵌入式數據庫直接在應用程序進程中運行，提供了零配置（zero-configuration）運行模式，并且資源占用非常少。
SQLite是一個開源的嵌入式關系數據庫，它在2000年由D. Richard Hipp發布，它的減少應用程序管理數據的開銷，SQLite可移植性好，很容易使用，很小，高效而且可靠。
SQLite嵌入到使用它的應用程序中，它們共用相同的進程空間，而不是單獨的一個進程。從外部看，它并不像一個RDBMS，但在進程內部，它卻是完整的，自包含的數據庫引擎。
嵌入式數據庫的一大好處就是在你的程序內部不需要網絡配置，也不需要管理。因為客戶端和服務器在同一進程空間運行。SQLite 的數據庫權限只依賴于文件系統，沒有用戶帳戶的概念。SQLite 有數據庫級鎖定，沒有網絡服務器。它需要的內存，其它開銷很小，適合用于嵌入式設備。你需要做的僅僅是把它正確的編譯到你的程序。
架構(architecture)
SQLite采用了模塊的設計，它由三個子系統，包括8個獨立的模塊構成。
接口(Interface)
接口由SQLite C API組成，也就是說不管是程序、腳本語言還是庫文件，最終都是通過它與SQLite交互的(我們通常用得較多的ODBC/JDBC最后也會轉化為相應C API的調用)。
編譯器(Compiler)
在編譯器中，分詞器（Tokenizer）和分析器(Parser)對SQL進行語法檢查，然后把它轉化為底層能更方便處理的分層的數據結構---語法樹，然后把語法樹傳給代碼生成器(code generator)進行處理。而代碼生成器根據它生成一種針對SQLite的匯編代碼，最后由虛擬機(Virtual Machine)執行。
虛擬機(Virtual Machine)
架構中最核心的部分是虛擬機，或者叫做虛擬數據庫引擎(Virtual Database Engine,VDBE)。它和Java虛擬機相似，解釋執行字節代碼。VDBE的字節代碼由128個操作碼(opcodes)構成，它們主要集中在數據庫操作。它的每一條指令都用來完成特定的數據庫操作(比如打開一個表的游標)或者為這些操作?？臻g的準備(比如壓入參數)?？傊?，所有的這些指令都是為了滿足SQL命令的要求(關于VM，后面會做詳細介紹)。
后端(Back-End)
后端由B-樹(B-tree)，頁緩存(page cache，pager)和操作系統接口(即系統調用)構成。B-tree和page cache共同對數據進行管理。B-tree的主要功能就是索引，它維護著各個頁面之間的復雜的關系，便于快速找到所需數據。而pager的主要作用就是通過OS接口在B-tree和Disk之間傳遞頁面。
SQLite的特點(SQLite's Features and Philosophy)
零配置(Zero Configuration)
可移植(Portability)：
它是運行在Windows,Linux,BSD,Mac OS X和一些商用Unix系統，比如Sun的Solaris,IBM的AIX，同樣，它也可以工作在許多嵌入式操作系統下，比如QNX,VxWorks,Palm OS, Symbin和Windows CE。
Compactness：
SQLite是被設計成輕量級，自包含的。one header file, one library, and you're relational, no external database server required
簡單(Simplicity)
靈活(Flexibility)
可靠(Reliability)：
SQLite的核心大約有3萬行標準C代碼，這些代碼都是模塊化的，很容易閱讀。
事務(Transaction)
事務的周期(Transaction Lifecycles)
程序與事務之間有兩件事值得注意：
A、哪些對象在事務下運行——這直接與API有關。
B、事務的生命周期，即什么時候開始，什么時候結束以及它在什么時候開始影響別的連接（這點對于并發性很重要）——這涉及到SQLite的具體實現。
一個連接（connection）可以包含多個(statement)，而且每個連接有一個與數據庫關聯的B-tree和一個pager。Pager在連接中起著很重要的作用，因為它管理事務、鎖、內存緩存以及負責崩潰恢復(crash recovery)。當你進行數據庫寫操作時，記住最重要的一件事：在任何時候，只在一個事務下執行一個連接。這些回答了第一個問題。
一般來說，一個事務的生命和statement差不多，你也可以手動結束它。默認情況下，事務自動提交，當然你也可以通過BEGIN..COMMIT手動提交。接下來就是鎖的問題。
關于這個圖有以下幾點值得注意：
A、一個事務可以在UNLOCKED，RESERVED或EXCLUSIVE三種狀態下開始。默認情況下在UNLOCKED時開始。
B、白色框中的UNLOCKED, PENDING, SHARED和 RESERVED可以在一個數據庫的同一時存在。
C、從灰色的PENDING開始，事情就變得嚴格起來，意味著事務想得到排斥鎖(EXCLUSIVE)（注意與白色框中的區別）。
雖然鎖有這么多狀態，但是從體質上來說，只有兩種情況：讀事務和寫事務。
讀者可以從http://www.sqlite.org/下載SQLite 3.3.4的版本
Cmd 進入命令行
創建數據庫文件:
>SQLite3 d:\test.db 回車
就生成了一個test.db在d盤。
這樣同時也SQLite3掛上了這個test.db
用.help可以看看有什么命令
>.help 回車即可
看看有創建了多少表
>.tables
看表結構
>.schema 表名
看看目前掛的數據庫
>.database
如果要把查詢輸出到文件
>.output 文件名
> 查詢語句；
把查詢結果用屏幕輸出
>.output stdout
把表結構輸出，同時索引也會輸出
> .dump 表名
退出
>.exit 或者.quit
從http://sqlite.phxsoftware.com/下載Ado.net驅動。
下載了安裝，在安裝目錄中存在System.Data.SQLite.dll
我們只需要拷貝這個文件到引用目錄，并添加引用即可對SQLite數據庫操作了
所有的Ado.net對象都是以SQLite開頭的，比如SQLiteConnection
連接串只需要如下方式
Data Source=d:\test.db 或者DataSource=test.db--應用在和應用程序或者.net能夠自動找到的目錄
剩下的就很簡單了~~
SQL語法
由于以前用SQLServer或者ISeries，所以DDL的語法很汗顏
創建一個單個Primary Key的table
CREATE TABLE [Admin] (
[UserName] [nvarchar] (20) PRIMARY KEY NOT NULL ,
[Password] [nvarchar] (50) NOT NULL ,
[Rank] [smallint] NOT NULL ,
[MailServer] [nvarchar] (50) NOT NULL ,
[MailUser] [nvarchar] (50) NOT NULL ,
[MailPassword] [nvarchar] (50) NOT NULL ,
[Mail] [nvarchar] (50) NOT NULL
) ;
創建一個多個Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext] NOT NULL ,
[CdString2] [ntext] NOT NULL ,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;
創建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck], [UserName]);
還可以視圖等等。
SQLite 分頁查詢
寫法1:
SELECT * FROM TABLE1 LIMIT 20 OFFSET 20 ;
寫法2:
SELECT * FROM TABLE1 LIMIT 20 , 20;
SQLite 文件的壓縮
在多次刪除數據、插入數據、更新數據后，數據庫體積增大，但實際有效數據量很小，則需要對數據庫進行壓縮、整理，把已經刪除的數據從物理文件中移除。調用一下SQL命令即可：
VACUUM
VACUUM的實現
數據插入與更新
使用REPLACE替代INSERT、UPDATE命令。在無滿足條件記錄，則執行Insert，有滿足條件記錄，則執行UPDATE。
REPLACE INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
Insert or Replace Into 和Replace Into 的效果是一樣的上面這句話也可以這樣寫
Insert or Replace INTO TABLE1(col1, col2, col3) VALUES(val1, val2,val3);
字符編碼轉換
sqlite3的源碼中，提供了utf8ToUnicode()、unicodeToUtf8()、mbcsToUnicode()、unicodeToMbcs()、sqlite3_win32_mbcs_to_utf8 ()、utf8ToMbcs ()等8個函數進行字符在不同編碼間的轉換，但未在sqlite3.def、sqlite3.h文件中列出，即未對外公開。這些函數中，都使用了MultiByteToWideChar()、WideCharToMultiByte()兩個函數實現字符間轉換。
開發示例
using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;
using System.IO;
namespace FileSystemWatcthrDemo.DataHelper
{
public class SqLiteHelper
{
/// <summary>
/// ConnectionString樣例：Datasource=Test.db3;Pooling=true;FailIfMissing=false
/// </summary>
public static string ConnectionString
{
get
{
return @"Data source= "+DataBasePath+";";
}
set { throw new NotImplementedException(); }
}
public static string DataBasePath
{
get { return "SpringYang.db";};
}
private static object lockObject = new object();
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, List<SQLiteParameter> parameters)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Parameters.Clear();
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 30;
foreach (var parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
public static DataSet ExecuteQuery(string cmdText, List<SQLiteParameter> parameters)
{
lock (lockObject)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
DataSet ds = new DataSet();
PrepareCommand(command, conn, cmdText, parameters);
SQLiteDataAdapter da = new SQLiteDataAdapter(command);
da.Fill(ds);
return ds;
}
}
}
}
public static int ExecuteNonQuery(string cmdText, List<SQLiteParameter> parameters)
{
lock (lockObject)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, parameters);
return command.ExecuteNonQuery();
}
}
}
}
public static SQLiteDataReader ExecuteReader(string cmdText, List<SQLiteParameter> parameters)
{
lock (lockObject)
{
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
SQLiteCommand command = new SQLiteCommand();
PrepareCommand(command, conn, cmdText, parameters);
SQLiteDataReader sqLiteDataReader = command.ExecuteReader();
return sqLiteDataReader;
}
}
public static object ExecuteScalar(string cmdText, List<SQLiteParameter> parameters)
{
lock (lockObject)
{
using (SQLiteConnection conn = new SQLiteConnection(ConnectionString))
{
using (SQLiteCommand command = new SQLiteCommand())
{
PrepareCommand(command, conn, cmdText, parameters);
return command.ExecuteScalar();
}
}
}
}
public static void CreateDataBase()
{
if (!File.Exists(DataBasePath))
SQLiteConnection.CreateFile(DataBasePath);
CreateTable();
}
public static void CreateTable()
{
ExecuteNonQuery(CodeDetailTabale, null);
}
private static string CodeDetailTabale
{
get
{
return @"CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL ,
[CdCode] [nvarchar] (20) NOT NULL ,
[CdString1] [ntext] NOT NULL ,
[CdString2] [ntext] NOT NULL ,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)
) ;";
}
}
}
}
示例講解
A、使用到自己定義的鎖private static object lockObject = new object();
B、使用完連接后都進行關閉操作。使用了using
C、創建數據庫命令：SQLiteConnection.CreateFile(DataBasePath);
最后再講解個Insert or Replace into的經典用法
Insert or Replace INTO User(ID, Name,Age) Select old.ID,new.Name,new.Age From
(select 'Spring Yang' as Name, '25' as Age) as new left join (Select ID,Name from User where Name = 'Spring Yang' ) as old on old.Name = new.Name
