働くプログラマ

[アーティクル]
12/19/2016

SQLite の内部

Ted Neward

Ted Neward 今月のコラムのテーマに合わせるならば、SQL とリレーショナルデータベースのルーツを振り返るべきでしょう。当然のことながら、SQL Server の概要、新しい機能セット、パフォーマンスの向上などについて説明するのが妥当でしょうが、それは私の流儀ではありません。誤解しないでください。SQL Server はすばらしいデータベースです。"大規模な" エンタープライズクラスのシナリオでの使用は強くお勧めします。しかし、あらゆる問題に、(かつて友人が言ったように) 「どでかい一元的なデータベース」が求められているわけではありません。

実際のところ開発者は、単に、構成オプション、ユーザー設定、インターナショナル設定の値など、「次回使用するものを置いておく」場所としてリレーショナルデータベースを長い間使用してきました。これらを一元管理される SQL Server のインスタンスに置いておくと便利な場合もありますが、リッチクライアントのシナリオ (特に、Microsoft Silverlight や Windows Phone 7 のリッチクライアントのシナリオ) では、SQL Server のインスタンスへの常時接続を維持することが、場合によっては可能とは言い難かったり、まったく実行不可能なことがよくあります。

開発者は、リレーショナルデータベースの機能と柔軟性をあきらめたくないとは思いますが、SQL Server Express ですらインストールが面倒と感じることがあります。では、どうすればよいでしょう。

もちろん、軽量なデータベースを使用すればよいのです。つまり、SQLite を使用すればよいのです。

SQLite の概要

SQLite の Web サイト (sqlite.org) には、「SQLite は、自己完結型で、サーバーを必要とせず、構成が不要で、トランザクション処理可能な SQL データベースエンジンを実装するソフトウェアライブラリである」と記載されています。この説明の主な要素は、「ライブラリ」という名詞を中心に展開されます。解析と実行のために、クライアント側のアセンブリを使用して要求をサーバーに送信する SQL Server とは異なり、SQLite は、完全にクライアントプロセス内に存在する、"埋め込み型の" データベースです。使用中の SQLite データベースの実行フットプリントは、クライアントファイルシステムのどこかに格納される 1 つのファイルだけで、一般には、インストールのフットプリントも同様に小さくなります。

とはいえ、SQLite データベースの機能は非常に豊富で、SQL-92 の仕様の大部分がサポートされます。ただし、RIGHT OUTER JOIN、FULL OUTER JOIN、ALTER TABLE、トリガーサポートの一部、GRANT と REVOKE、VIEW への書き込みなど、使用できない機能もいくつかあります (詳細については、SQLite の Web サイトを参照してください)。トランザクションや広範なデータ型など、多くのものがサポートされているのが印象的です。SQL Server データベーススキーマを変更することなく SQLite に移植できるとは信じられないかもしれませんが、かなり単純なスキーマであれば、つまり、SQL Server 固有の型や機能を使用していなければ、問題を最小限に抑えて移植できる、と想定することはそれほど不自然なことではありません。そのため、SQLite は、単に "軽量な SQL" が必要なシナリオには最適です。

適用可能性や安定性について懸念がありながらも、SQLite はさまざまな "軽量な" 環境にゆっくりと広がりつつあります。既に、(HTML 5 をサポートする) Mozilla Firefox ブラウザー内でも使用されているほか、Symbian、iOS、Android などの環境でも使用されています。言い換えれば、開発の世界の残り半分 (つまり、マイクロソフトが中心ではない世界) で、軽量データベースが使用されていることを示しています。SQLite は、現在も開発とバグの修正を引き続き進めながら、最小の SQL エンジンとしてかなり確固たる製品になりつつあります。

もちろん、データベースはある種の管理者インターフェイスなしでは成り立ちません。その点でも、SQLite は失望させられることはありません。SQLite には、SQLite データベースにアクセスして操作するためのコマンドラインコンソールツールがあります。ただし、システム管理者であればそれほど感動しないかもしれません。さいわい、オープンソースコミュニティから多数の SQLite ツールを入手できます (これらのツールを一覧した長いリストは SQLite Web サイトにあります)。しかし、簡単なクエリアナライザーのようなツールだけが必要であれば、SQLite Administrator をお試しください。これは、sqliteadmin.orbmu2k.de (英語) からダウンロードして入手できる、無償のツールです。

ネイティブにする

SQLite は、当初、ネイティブコード開発者向けのデータベースとして設計されました。ネイティブ C/C++ DLL として実装されるのはこのためです。この SQLite のネイティブ性は、功罪あわせ持っています。特定の SQL ステートメントを実行するのに必要な合計時間から多くのオーバーヘッド (ネットワーク経由のサーバーとのやり取りなど) が削減されることはメリットですが、元の SQLite データベースがネイティブ C/C++ DLL であるため、Microsoft .NET Framework ベースのアプリケーションから SQLite データベースにアクセスするのが難しくなることがあります。

さいわい、知識のある .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);
  }
}

P/Invoke が C/C++ API を高い忠実度で再現することで、これは比較的シンプルなプロセスになります。つまり、SQLite API では、データベース自体を表すのにロウポインターを使用します。P/Invoke はこのポインターを System.IntPtr と見なすため、多くの場合、SQLite API は int へのポインターをパラメーターとして使用して、P/Invoke と C# の "out" キーワードによって表される内容を変更できます (P/Invoke の詳細については、pinvoke.codeplex.com (英語) を参照してください)。

SQLite API の使用方法に関する詳細のほとんどについては、SQLite Web サイトを参照してください。ここでは、データベースを開いて、クエリを実行し、データベースを閉じる方法を図 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

この API に関して最も特筆すべきことは、この API がやや低レベルの側にあることです。私のように古くからの C++ 好きにとっては、これはすばらしいことかもしれません。なぜなら、我々に古き良き日を回想するすばらしい機会を与えてくれるからです。かつて、男たちは男らしく、メモリは手動で管理されていて、カクテルパーティでは、Windows 95 という名の荒野で野生のポインターを追いかけたおっかない話に女たちが夢中になっていたものです。しかし、他の C# に口うるさい人々や、実際に生産性よく作業を完了したい新米たちにとっては、これはまったく低レベルすぎるのです。必要なのは、その API の周囲に優れた抽象化ラッパーを作成することにより、API の管理を容易にするだけでなく、その API を使用するのに必要なコードの行数を減らすことです。

これを 1 つのクラスにラップするのはそれほど難しくありません。具体的に言うと、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 データベースを使用する際に生じる手間がかなり軽くなることを意味します。ラッパークラスを追加して、SQLite DLL をプログラムからアクセス可能な場所に置けば (通常は、実行可能ファイルが存在するディレクトリ)、勝者のごとく SQL ステートメントを記述できるようになります。もちろん、それが皆さんの望むことであれば、ですが。

しかし、かなり大多数の .NET Framework 開発者は、コンソール API を使用して完全に "手動で" SQL データベースを管理するのが苦手か、ずっとその方法を知らなかったか、単純に SQL の世界を忘れ去りたいかのいずれかのようです。現在の .NET Framework 環境では、リレーショナルスキーマを作成して管理するための豊富なツールが提供されるので、こういった手動のアプローチを再び使用することは当然原始的に感じられ、何より、生産的ではないと感じられるのです。

また、マイクロソフトでは既に、プログラマがリレーショナルデータベースに対して実行する必要があるほとんどの処理を効果的に記述する API の作成に労力を費やしており、多くのツール (LINQ to SQL、Entity Framework、および 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 と Entity Framework も含まれます。

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 または Visual Studio 2008 のプロジェクトからコードを実行すると、すべて問題なく動作します。しかし、Visual Studio 2010 からコードを実行すると、"Unhandled Exception: System.IO.FileLoadException: Mixed mode assembly is built against version 'v2.0.50727' of the runtime and cannot be loaded in the 4.0 runtime without additional configuration information." (ハンドルされていない例外: System.IO.FileLoadException: 混合モードアセンブリはバージョン 'v2.0.50727' のランタイムに対して作成されており、追加の構成情報がないと 4.0 ランタイムでは読み込めません。) というエラーが表示されます。"混合モードアセンブリ" という用語を聞いたことがない方のために説明すると、これは、Microsoft のマネージ中間言語命令とネイティブの x86 アセンブリ命令の両方を含むアセンブリのことを指します。もちろん、この場合は良くない点が 2 つあります。まず、明確な問題として、コードを動作させる必要があります。そして、これが混合モードアセンブリであれば、他の環境 (ASP.NET など) で SQLite を使用するときに、いくつかの問題が発生します。

1 つ目の問題は、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 に近い形を維持することをお勧めします。

すべてマネージコードの SQLite

繰り返しになりますが、オープンソースコミュニティでは、必要なときに必要なものが提供されます。今回は、"C#-SQLite" というプロジェクトがありました (code.google.com/p/csharp-sqlite、英語)。このプロジェクトはどうやら、コードの移植によって「C# 言語を学ぶための演習」として開始されたようです。関連付けられている Wiki には、移植を成し遂げるために作成者が実行した処理について説明が記載されていますが、要するに、まさに必要としていたものが手に入るということです。それはつまり、すべてマネージコードのバージョンの SQLite です。

これを使用するには、プロジェクトのソースをダウンロードし、プロジェクトを開いて、ビルドを開始する必要があります。たくさんのオープンソースプロジェクトと同様に、C#-SQLite はさまざまなプロジェクトで構成されますが、各プロジェクトは独自のソリューションファイルに含まれるため、1 つ以上のソリューションを開く必要があることがあります (または、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" などです)。しかし、ネイティブモードの DLL による潜在的なセキュリティ上の問題 (もしあれば) を発生させることなく、SQLite のすばらしい機能をすべて使用できるようになります。

制限事項

SQLite にはすばらしい特性がありますが、ある程度の健全さを持って SQLite と SQL Server のどちらを使用するかを決める場合は、SQLite の制限事項について理解しておくことが重要です。SQLite では、SQL Server の全機能を提供しようとはしていません。それどころか、SQLite データベースでは、開発者が複数のスレッドを使用したり、まして複数のスレッドからデータベースにアクセスしたりすることは望んでいません。実際に、2 つのプログラムから SQLite データベースに同時にアクセスしたくなったら、そろそろ SQL Server のインスタンス (Express Edition または別のバージョン) にアップグレードする時期だと言ってもよいでしょう。

SQLite が優れている主な領域は、かつて Access ファイルが占めていたのと同じ多くの領域です。そのような領域では、ほぼ完全な SQL-92 構文をサポートし、その他の環境 (Python、Perl など) で使用されているデータベースファイルを読み取ることができます。また、Silverlight や携帯電話のクライアントから SQLite を (特にローカルストレージとして) 使用するのも、非常に興味深い分野です。たとえば、SQLite データベースを Silverlight 分離ストレージに指定すると、開発者は、(Silverlight コードと共に配布できるという点で) 移植可能なデータベースを作成して、このデータベースにローカルデータを格納できます。慎重に使用すれば、SQLite は、機能から負荷までのリレーショナルデータベースの一連のオプションを提供します。

毎回のことですが、説明してほしい特定のトピックがある方は遠慮なくお寄せください。まさしく、これは皆さんのコラムですから。

コーディングを楽しんでください。

Ted Neward は、Microsoft .NET Framework および Java のエンタープライズプラットフォームシステムを専門とする独立企業 Neward & Associates の社長を務めています。これまでに 100 個を超える記事を執筆している Ted は、C# MVP であり、INETA の講演者でもあります。さまざまな書籍を執筆および共同執筆していて、『Professional F# 2.0』(Wrox、2010 年、英語) もその 1 つです。彼は定期的にコンサルティングを行い、開発者を指導しています。彼の連絡先は、ted@tedneward.com (英語のみ) です。ブログを blogs.tedneward.com (英語) に公開しています。