發行項
08/18/2015

本文章是由機器翻譯。

孜孜不倦的程式師

SQLite 內幕

Ted Neward

為了與本刊主題保持一致，現在應該回過頭來介紹一下 SQL 和關聯式資料庫本質方面的內容。很自然地，我們似乎應該寫一些有關 SQL Server 的內容，即有關它的新功能集或性能改進之類的內容，但這並不是我的風格。請不要誤解我的意思，SQL Server 是一種優秀的大型資料庫，因而強烈建議在那些“巨無霸式”企業級方案中使用它，但用一位朋友的話說，並不是每個問題都需要借助“航母級的集中式資料庫”來解決。

事實上，開發人員長期以來使用關聯式資料庫僅僅是將其作為“存放內容以備下次使用”的位置，諸如配置選項、使用者設置、國際化值這樣的內容。雖然有時將這些內容存放在集中式 SQL Server 實例十分方便，但在某些情況下，具體來說是在富用戶端方案（特別是 Microsoft Silverlight 或 Windows Phone 7 富用戶端方案）中，保持與 SQL Server 實例的固定連接並非切實可行的，通常就是根本無法實現。

開發人員不一定需要放棄關聯式資料庫的強大功能和靈活性，但即使是 SQL Server Express 有時安裝規模也過大。那我們應該怎麼辦？

當然是輕裝上陣：準確的說，是使用 SQLite。

SQLite 簡介

SQLite 的網站 (sqlite.org) 對它進行了如下描述：“SQLite 是一個可實現獨立、無伺服器、零配置、事務性 SQL 資料庫引擎的軟體庫”。這句話中的關鍵要素都圍繞著“庫”這一名詞。與使用用戶端程式集向伺服器發送請求以供分析和執行的 SQL Server 不同的是，SQLite 完全駐留在用戶端進程中，這使之成為一種“嵌入式”資料庫。在使用期間，SQLite 資料庫的運行空間是一個存儲在用戶端檔案系統的某一位置中的單個檔，並且安裝空間通常也相當小。

儘管如此，SQLite 資料庫的功能卻是極其豐富的，因為它支援大部分 SQL-92 規範，只是去除了 RIGHT 和 FULL OUTER JOIN、ALTER TABLE、某一觸發器支援、GRANT/REVOKE 以及寫入 VIEW 等幾項內容（更詳細的說明請參見 SQLite 網站）。令人印象深刻的是支援的功能數量，包括事務和各種資料類型。雖然不加修改就將 SQL Server 資料庫方案全盤遷移到 SQLite 是不足為信的，但有一點是合乎情理的，即在遷移非常簡單（即未利用 SQL Server 特定的類型或功能）的方案時幾乎不會遇到什麼麻煩。這使 SQLite 十分適合只需“輕型 SQL”的情形。

為了消除人們對其適用性或穩定性的擔心，SQLite 正慢慢地融入各種“輕型”環境中，它已出現在 Mozilla Firefox 流覽器（用於支援 HTML 5）以及 Symbian、iOS 和 Android 等環境中。換句話說，這就是“另一半”的開發領域（即不以 Microsoft 為中心）實現輕型資料庫的方式。SQLite 正在不斷地發展和修復錯誤，使得這種微型 SQL 引擎有了足夠的安全保障。

當然，資料庫必須具有某種管理員介面才是完整的，而 SQLite 也不例外。SQLite 資料庫具有一個用於對其進行訪問和操作的命令列主控台工具，但您的系統管理員可能對它沒有太多興趣。慶倖的是，開源社區提供了許多 SQLite 工具（在 SQLite 網站上提供了這些工具的完整清單），但如果只需類似查詢分析器的快速工具，請嘗試使用 SQLite Administrator，這是一個可從以下位置下載的免費工具：sqliteadmin.orbmu2k.de。

本機開發

從一開始，SQLite 就立足于面向本機代碼開發人員的資料庫，這就是它為何作為本機 C/C++ DLL 實施的原因。This native flavor of SQLite is both a blessing and a curse:SQLite 這種本機特色的利弊鮮明：有利的是，它從執行指定 SQL 語句所需的總時間中省去了許多開銷（例如穿過網路到達伺服器，然後重新返回）；而弊端在於，由於原始 SQLite 資料庫是本機 C/C++ DLL，因此從基於 Microsoft .NET Framework 的應用程式訪問它會是一項不小的挑戰。

慶倖的是，技術精湛的 .NET Framework 開發人員認識到訪問本機 DLL 實際上只是練習使用 P/Invoke 聲明，而圍繞 SQLite DLL 中公開的本機聲明創建包裝類則相對比較容易。事實上，對於基本功能來說，就像開源社區中提供的眾多內容一樣，它們也已經實現；導航到 switchonthecode.com/tutorials/csharp-tutorial-writing-a-dotnet-wrapper-for-sqlite，我們會發現已創建好的 P/Invoke 聲明的工作集，如圖 1 中所示。

圖 1 P/Invoke 聲明

namespace SQLiteWrapper
{
  public class SQLiteException : Exception
  {
    public SQLiteException(string message) :
      base(message)
      { }
  }

  public class SQLite
  {
    const int SQLITE_OK = 0;
    const int SQLITE_ROW = 100;
    const int SQLITE_DONE = 101;
    const int SQLITE_INTEGER = 1;
    const int SQLITE_FLOAT = 2;
    const int SQLITE_TEXT = 3;
    const int SQLITE_BLOB = 4;
    const int SQLITE_NULL = 5;

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_open")]
      static extern int sqlite3_open(string filename, out IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_close")]
      static extern int sqlite3_close(IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_prepare_v2")]
      static extern int sqlite3_prepare_v2(IntPtr db, string zSql,
        int nByte, out IntPtr ppStmpt, IntPtr pzTail);
    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_step")]
      static extern int sqlite3_step(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_finalize")]
      static extern int sqlite3_finalize(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_errmsg")]
      static extern string sqlite3_errmsg(IntPtr db);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_count")]
      static extern int sqlite3_column_count(IntPtr stmHandle);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_origin_name")]
      static extern string sqlite3_column_origin_name(
        IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_type")]
      static extern int sqlite3_column_type(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_int")]
      static extern int sqlite3_column_int(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_text")]
      static extern string sqlite3_column_text(IntPtr stmHandle, int iCol);

    [DllImport("sqlite3.dll", EntryPoint = "sqlite3_column_double")]
      static extern double sqlite3_column_double(IntPtr stmHandle, int iCol);
  }
}

面向 C/C++ 的 P/Invoke API 具有非常高的保真性，這使得該過程變得相對簡單；SQLite API 使用原始指標來表示資料庫本身，這在 P/Invoke 中通過 System.IntPtr 實現，並且 SQLite API 經常會將指向 int 的指標作為參數，這樣可以使用 C#“out”關鍵字修改 P/Invoke 描述的內容。（有關 P/Invoke 的詳細資訊，請參閱 pinvoke.codeplex.com。）

若要瞭解有關如何使用 SQLite API 的大部分詳細資訊，建議您訪問 SQLite 網站；但若要快速瞭解如何打開資料庫，執行查詢，然後關閉資料庫這一過程，請參閱圖 2，其中顯示了類似的內容。

圖 2 打開資料庫，執行查詢，然後關閉資料庫

static void NativeMain()
 {
   // Open the database--db is our "handle" to it
   IntPtr db;
   if (SQLiteNative.sqlite3_open(@"cities.sqlite", out db) 
     == SQLiteNative.SQLITE_OK)
     {
       // Prepare a simple DDL "CREATE TABLE" statement
       string query = 
         "CREATE TABLE City " + 
         "(name TEXT, state TEXT, population INTEGER)";
       IntPtr stmHandle;
       if (SQLiteNative.sqlite3_prepare_v2(db, query, query.Length,
         out stmHandle, IntPtr.Zero) != SQLiteNative.SQLITE_OK)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
       if (SQLiteNative.sqlite3_step(stmHandle) != 
         SQLiteNative.SQLITE_DONE)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }
       if (SQLiteNative.sqlite3_finalize(stmHandle) != 
         SQLiteNative.SQLITE_OK)
       {
         // Something went wrong--find out what
         var err = SQLiteNative.sqlite3_errmsg(db);
       }

     // ... Now that we've created a table, we can insert some
     // data, query it back and so on

     // Close the database back up
     SQLiteNative.sqlite3_close(db);
     }
  }

非常類似于底層開發

此 API 最引人注目的一點就是它有點類似于底層開發。如果您像我一樣也是一名 C++ 老程式師，這可能是一件好事。這讓我們追憶起過去的美好時光，那時男人都是頂天立地的漢子，記憶體都是手動管理的，而女人們則一邊品嘗著雞尾酒，一邊聆聽我們在 Windows 95 原野上駕馭難以馴服的指標這樣令人膽顫心驚的故事… 但對於其餘那些自以為是的 C# 年輕程式師，這些新手實際上只希望高效地完成任務，可以說與他們完全不在一個層次上。真正需要的是該 API 的出色抽象包裝，這使得它更易於管理並可以減少使用時所需的代碼行數。

將它包裝在單個類中並不太難，因為 System.Data 提供了一些很好的類，可處理大多數使用者 API 交互。名為 SQLite 的該包裝類的完整詳細資訊有點過長，將不在文本中提供，但圖 3 中顯示的聲明十分清晰地說明了應如何使用該類。

圖 3 SQLite 包裝類的聲明

public class SQLite : IDisposable
  {
    private IntPtr _db; //pointer to SQLite database
    private bool _open; //whether or not the database is open

    /// <summary>
    /// Opens or creates SQLite database with the specified path
    /// </summary>
    /// <param name="path">Path to SQLite database</param>
    public void OpenDatabase(string path);

    /// <summary>
    /// Closes the SQLite database
    /// </summary>
    public void CloseDatabase();

    /// <summary>
    /// Executes a query that returns no results
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    public void ExecuteNonQuery(string query);

    /// <summary>
    /// Executes a query and stores the results in
    /// a DataTable
    /// </summary>
    /// <param name="query">SQL query to execute</param>
    /// <returns>DataTable of results</returns>
    public DataTable ExecuteQuery(string query);
  }

然後，該包裝類的使用類似于圖 4 中的示例。

圖 4 使用 SQLite 包裝類

static void NativeWrapperMain()
  {
    using (SQLite db = new SQLite("persons.sqlite"))
    {
      db.ExecuteNonQuery("CREATE Table Persons " +
        "(first TEXT, last TEXT, age INTEGER)");

      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Aaron', 'Erickson', 38)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Rick', 'Minerich', 29)");
      db.ExecuteNonQuery("INSERT INTO Persons (first, last, age) " +
        "VALUES ('Talbott', 'Crowell', 35)");

      DataTable table = db.ExecuteQuery("SELECT * FROM Persons");

      foreach (DataRow row in table.Rows)
      {
        Console.WriteLine("{0} {1} {2}", row[0], row[1], row[2]);
      }
    }
  }

很明顯，有多個操作可添加到圖 4 中的 SQLite 包裝類，但現已得到必要的基本功能，這部分歸功於 System.Data 中 DataTable/DataRow/DataColumn 核心類的出色的資料庫無關性特性。

雙重抽象

從某些方面說，SQLite 資料庫的優勢就在於其底層設計和實施，但成為嵌入式資料庫意味著在利用其非常輕型的功能時會存在“衝突”。添加包裝類，確保 SQLite DLL 位於可供程式訪問的位置（通常將其放入包含可執行檔的目錄），現在您就可以十分成功地編寫 SQL 語句。當然，您確定自己確實要這樣做。

但是，絕大多數 .NET Framework 開發人員在通過主控台 API 完全“手動”管理 SQL 資料庫方面可能非常生疏，根本不知道如何執行此任務或完全想放棄這一領域。當今的 .NET Framework 環境為創建和管理關係方案提供了如此多的工具，這使得回到這一手動方法的確讓人感覺很原始，更重要的是生產率低下。

此外，Microsoft 已創建一個 API，它能夠有效地描述程式師希望對關聯式資料庫執行的大多數操作，其中許多工具（LINQ to SQL、實體框架以及 Visual Studio 設計器）均在該 API 的基礎上構建。當然，我指的是 ADO.NET 及其提供程式模型。如果無法將 SQLite 移入 ADO.NET“之下”，則意味著所有這些超酷功能對使用 SQLite 的開發人員都不可用，這似乎是相當明顯的缺陷。那麼，解決方案就是為 SQLite 構建 ADO.NET 提供程式。

正如我們所發現的那樣，開源社區的一大優點就是無論您想做什麼事情，都可能有人已經實現了，並且和您想要的沒有什麼不同。可以從 sqlite.phxsoftware.com 下載 System.Data.SQLite，這是一個完整的 ADO.NET 3.5 提供程式。這意味著開發人員可以使用傳統用戶端/伺服器關聯式資料庫提供程式執行的所有功能都可供 SQLite 開發人員使用，包括所有 Visual Studio 設計器支援以及 LINQ 和實體框架。

使用 System.Data.SQLite 極為簡單。下載程式（可以下載原始程式碼，這樣您便可以自行構建並研究代碼以瞭解工作原理 - 如果您感興趣，這是一個不錯的工作範例，可從中瞭解如何構建 ADO.NET 提供程式；如果只是希望更快地“完成工作”，可以只下載二進位檔案）。然後，將這些檔放在硬碟上的某一位置，從專案引用 System.Data.SQLite.dll，就大功告成了。毫無疑問，API 類位於 System.Data.SQLite 中，一旦引用它們，就可以針對資料庫編寫出色的舊式 ADO.NET 代碼，如圖 5 中所示。

圖 5 使用 System.Data.SQLite

static void ManagedWrapperMain()
{
  var connStr = new SQLiteConnectionStringBuilder() 
    { DataSource = "persons.sqlite" };
  using (SQLiteConnection conn = new SQLiteConnection(connStr.ToString()))
  {
    conn.Open();
    SQLiteCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM Persons";
    var ct = cmd.ExecuteScalar();
    Console.WriteLine("Count = {0}", ct);

    cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Persons";
    SQLiteDataReader reader = cmd.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(reader);
    foreach (DataRow row in dt.Rows)
    {
      Console.WriteLine("{0} {1} {2}", row[0], row[1], row[2]);
    }
  }
}

到目前為止一切順利。在從 Visual Studio 2005 或 2008 專案運行該代碼時，一切都運行良好。但在從 Visual Studio 2010 執行該代碼時，則會出現錯誤，指出“未處理的異常:System.IO.FileLoadException: 混合模式程式集是針對‘v2.0.50727’版的運行時生成的，在沒有配置其他資訊的情況下，無法在 4.0 運行時中載入該程式集”。對於從未聽說過混合模式程式集的人來說，對該術語的解釋是同時包含託管 Microsoft 中間語言和本機 x86 程式集指令的程式集。這當然是一個壞消息，主要體現在兩個方面：一方面是很明面的問題，即我們需要代碼才能工作；另一方面是，對於混合模式程式集，在 ASP.NET 等其他環境中使用 SQLite 時會出現一些問題。

通過添加一個指示 CLR 4.0 載入混合模式程式集的 app.config 檔，可以很容易地解決第一個問題：

<?xml version="1.0"encoding="utf-8" ?>
<configuration>
  <startup useLegacyV2RuntimeActivationPolicy="true">
    <supportedRuntime version="v4.0"/>
  </startup>
</configuration>

更大的問題是許多環境都不支援混合模式程式集，但無論如何，肯定會有一種適當的解決方法。出於各種原因，全部託管解決方案是首選，但由於 SQLite DLL 是本機代碼，因此這樣做比較棘手。最好是將 SQLite 代碼庫遷移到 C#，並盡可能地保持與原始 C 比較接近。

全部託管

再次重申一下，開源社區是有求必應的，在此示例中，它提供了一個名為“C#-SQLite”的專案，可從以下位置下載：code.google.com/p/csharp-sqlite。很明顯，該專案最初是一項通過遷移代碼“學習 C# 語言的練習”，相關的 wiki 提供了有關作者如何管理該遷移的討論，但現在我們已得到所需的確切結果，即：完全託管形式的 SQLite。an all-managed version of SQLite.

使用該專案時需要下載該專案的原始程式碼，打開該專案，然後開始構建。與許多開源專案一樣，C#-SQLite 也由多個專案組成，但其中每個專案都包含在其自己的解決方案檔中，因此您可能需要打開多個解決方案。（或者只是使用 MSBuild 從命令列開始構建，您可以使用其中最佳的那種方法。）

構建後，將 C#-SQLite 程式集 (Community.CSharpSQLite) 添加到專案；若要實現 ADO.NET 支援，還需要添加 C#-SQLite 用戶端程式集 (Community.CsharpSqlite.SQLiteClient.dll)。現在，SQLite 的所有功能可通過 ADO.NET 提供程式供我們使用，因此可以重新編寫與前面顯示的幾乎完全相同的代碼（請參閱圖 6）。

圖 6 使用 C#-SQLite

Static void AllManagedMain()
{
  SqliteConnection conn = 
    New SqliteConnection(@"Version=3,uri=file:persons.sqlite");
  conn.Open();
  try
  {
    SqliteCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT COUNT(*) FROM Persons";
    var ct = cmd.ExecuteScalar();
    Console.WriteLine("Count = {0}", ct);

    cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Persons";
    SqliteDataReader reader = cmd.ExecuteReader();
    while (reader.Read())
    {
      Console.WriteLine("{0} {1} {2}", reader[0], reader[1], reader[2]);
    }
  }
  finally
  {
    conn.Close();
  }
}

請注意，這些 API 與前面的混合模式版本幾乎相同（只是類名發生了更改，甚至只是一個大小寫問題：例如，作為首碼的“SQLite”與 “Sqlite”）。現在我們已經知道 SQLite 的所有優點，但尚未了解本機模式 DLL 的任何可能的潛在安全問題。

局限性

儘管 SQLite 具有出色的特性，但必須瞭解其局限性才能在 SQLite 和 SQL Server 之間做出明智的決策。 SQLite 不能提供 SQL Server 所具備的全部功能，但局限遠不止此。 SQLite 資料庫甚至不允許開發人員將其用於多個執行緒，更不用說從多個執行緒訪問該資料庫。事實上，也可以這樣說，如果有兩個程式要同時訪問 SQLite 資料庫，則可能應該升級到 SQL Server 實例（Express 或其他）。

SQLite 的主要“致勝”法寶在於許多方面與 Access 檔類似，幾乎完整的 SQL-92 語法支援，以及讀取其他環境（Python、Perl 等）所使用的資料庫檔的能力。從 Silverlight 或電話用戶端使用它也是一個高度引起關注的方面，對於本機存放區更是如此；例如，通過將 SQLite 資料庫插入 Silverlight 隔離存儲，可為開發人員提供一個用於存儲本地資料的可移植資料庫（因為它可以與 Silverlight 代碼一起移植）。請明智地使用 SQLite，它完美地實現了可提供許多重要功能選項的關聯式資料庫統一體。

同樣，如果您希望探討某個特定主題，歡迎給我留言。畢竟在真正意義上，這是你們的專欄。

祝您工作愉快！

Ted Neward 是 Neward &Associates 的負責人，這是一家專門研究企業 .NET Framework 系統和 Java 平臺系統的獨立公司。他曾寫過 100 多篇文章，是 C# 領域最優秀的專家之一併且是 INETA 發言人，著作或合著過十幾本書，包括即將出版的《Professional F# 2.0》(Wrox)。他定期提供諮詢和指導。您可通過 ted@tedneward.com 與他聯繫，也可通過 blogs.tedneward.com 訪問其博客。

共用方式為