SQLite 揭秘

转自http://msdn.microsoft.com/zh-cn/magazine/ff898405.aspx

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 实施的原因。 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。

使用该项目时需要下载该项目的源代码，打开该项目，然后开始构建。 与许多开源项目一样，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 访问其博客。