sh1’s diary

プログラミング、読んだ本、資格試験、ゲームとか私を記録するところ

Unity SQLiteUnityKit のスクリプト修正・変更のポイント

f:id:shikaku_sh:20200206133654p:plain
SQLite

SQLiteUnityKit から、SQLite の実用を目指すときは、注意点がいくつかあります。

今回は、いくつか挙げる注意点から SqliteDatabase.cs ファイルの修正を(個人的に)整理して、サンプルを公開した内容の記事です。

修正箇所は下の記事がよくまとまっていたため、ここの内容を(私の勉強のため)書き直しただけの記事かもしれないです。

修正1 マルチバイト文字の対応

DB に書き込む文字に(日本語など)マルチバイト文字を利用する場合、1文字1バイトというわけにはいかないので修正が必要になっています。

SqliteDatabase.cs ファイルの Prepare メソッドで query.Lenght を設定して実行していた部分を、バイト数に修正することでエラーを修正できます。

private IntPtr Prepare(string query)
    {
        IntPtr stmHandle;

        // クエリのバイト数を取得します
        int byteCount = System.Text.Encoding.UTF8.GetByteCount(query);

        if (sqlite3_prepare_v2(_connection, query, byteCount, out stmHandle, IntPtr.Zero) != SQLITE_OK)
        {
            IntPtr errorMsg = sqlite3_errmsg(_connection);
            throw new SqliteException(Marshal.PtrToStringAnsi(errorMsg));
        }

        return stmHandle;
    }

修正2 SQLiteKit の DB ファイルの置換条件

SQLiteKit は、端末の DB ファイルと、StreamingAssets の DB ファイルの GetLastWriteTimeUtc を比較して新しいほうを利用する仕様を持っています。

SqliteDatabase.cs ファイルの SqliteDatabase コンストラクターが該当のコードブロックです。

/// <summary>
/// Initializes a new instance of the <see cref="SqliteDatabase"/> class.
/// </summary>
/// <param name='dbName'> 
/// Data Base name. (the file needs exist in the streamingAssets folder)
/// </param>
public SqliteDatabase(string dbName)
{

    pathDB = System.IO.Path.Combine(Application.persistentDataPath, dbName);
    //original path
    string sourcePath = System.IO.Path.Combine(Application.streamingAssetsPath, dbName);

    //if DB does not exist in persistent data folder (folder "Documents" on iOS) or source DB is newer then copy it
    if (!System.IO.File.Exists(pathDB)
        // 問題のファイル置換条件
        /* || (System.IO.File.GetLastWriteTimeUtc(sourcePath) > System.IO.File.GetLastWriteTimeUtc(pathDB))*/
        )
    {
        // 省略
    }
}

コード中のコメントに書いてある「if DB does not exist in persistent data folder (folder "Documents" on iOS) or source DB is newer then copy it」の「source DB is newer then copy it」が問題の部分ですね。

現在はどうかわからないですが、過去の端末(によって)は、正しい日時を取得できないことがあったようです。(internet archive - Android&SQLiteUnityKitで、DBが更新されない問題

取得問題の対応策のひとつとして「差分のアップデート手法」もオススメされていました。

ここでは、上述のコードのように DB ファイルがすでに存在する場合は、コピーしない最小限の修正にしました。

修正3 トランザクション機能の追加

複数のクエリーをまとめて処理しないと、データに不整合が発生する場合は、トランザクションの中で処理しないといけません。

SQLiteUnityKit は、そもそもトランザクションのメソッドを用意していないため、新しく追加してやる必要があります。

インポートの追加

まず、DLL から sqlite3_exec の関数をインポートするコードを SqliteDatabase.cs ファイルに追加します。

[DllImport("libsqliteX", EntryPoint = "sqlite3_exec")]
private static extern int sqlite3_exec(IntPtr db, string sql, IntPtr callback, IntPtr args, out IntPtr errorMessage);

トランザクションの機能を追加するためのコードを SqliteDatabase.cs ファイルに追加します。

// クラス変数
private bool IsTransaction = false;

/// <summary>
/// Start a new transaction.
/// </summary>
public void TransactionStart()
{
    Open();

    IsTransaction = true;
    ExecuteQueryExec("BEGIN");
}

/// <summary>
/// Commits the current transaction, making its changes permanent.
/// </summary>
public void TransactionCommit()
{
    ExecuteQueryExec("COMMIT");

    IsTransaction = false;
    Close();
}

/// <summary>
/// Rolls back the current transaction, canceling its changes.
/// </summary>
public void TransactionRollBack()
{
    ExecuteQueryExec("ROLLBACK");
    Close();
}

/// <summary>
/// Executes a transaction query.
/// </summary>
/// <param name="query">Query.</param>
/// <exception cref='SqliteException'>
/// Is thrown when the sqlite exception.
/// </exception>
private void ExecuteQueryExec(string query)
{
    IntPtr stmHandle;

    if (!CanExQuery)
    {
        Debug.Log("ERROR: Can't execute the query, verify DB origin file");
        return;
    }

    if (!IsTransaction)
    {
        Debug.Log("ERROR: Haven't started a transaction.");
        return;
    }

    if (sqlite3_exec(_connection, query, IntPtr.Zero, IntPtr.Zero, out stmHandle) != SQLITE_OK)
    {
        throw new SqliteException("Could not execute SQL statement.");
    }
}

最後にトランザクション中にクエリを実行するためのメソッド ExecuteNonQuery を修正します。

トランザクションを使用しているときは、OpenClose のメソッドを実行しないように修正しただけです。(別にトランザクション中の専用メソッドを用意してもよいと思います)

/// <summary>
/// Executes a Update, Delete, etc  query.
/// </summary>
/// <param name='query'>
/// Query.
/// </param>
/// <exception cref='SqliteException'>
/// Is thrown when the sqlite exception.
/// </exception>
public void ExecuteNonQuery(string query)
{
    if (!CanExQuery)
    {
        Debug.Log("ERROR: Can't execute the query, verify DB origin file");
        return;
    }

    if (!IsTransaction)
    {
        Open();
    }

    if (!IsConnectionOpen)
    {
        throw new SqliteException("SQLite database is not open.");
    }

    IntPtr stmHandle = Prepare(query);

    if (sqlite3_step(stmHandle) != SQLITE_DONE)
    {
        throw new SqliteException("Could not execute SQL statement.");
    }

    Finalize(stmHandle);

    if (!IsTransaction)
    {
        Close();
    }
}

修正4 バインド機能の追加

SQL 文のバインド機能は、主に SQL インジェクション 対策のために用意されている機能といっていいと思います。

バインド機能をつかうことで、コードのセキュリティを高められるわけですが、そもそも DB ファイル自体の暗号化を SQLiteUnityKit は(というよりもデフォルトの SQLite は)考慮していないため、セキュリティとはいったい……といった状態です。

なので、私の意見は、あまりポジティブな機能追加にならないと思っています。データベースファイル自体の暗号化に取り組まないと片手落ちといった感じです。

バインド機能を使った記述のほうが好きなんだ! くらいの人にしかメリットを感じてないです。また、かるめの修正例などでは Dictionary クラスがよく利用されていますが、こういうクラスまで出てくると System.Data.SQLite にも近づくため、どこまでを SQLiteUnityKit の拡張記法として採用したものか微妙なところです。

なお、デフォルトの SQLite3 はファイルの暗号化に対応していません。公式では「SQLite SEE」を提供していますが、これは有償です。(US $2,000 からなので決して安くないです)1

フリーだと、「wxsqlite3」や「sqlcipher」あたりが候補になると思いますが、SQLite ファイルを自分で実際にコンパイルして生成する必要があります。

この詳細は、まだ日本語の記事もすくないみたいなので、テストして後の記事で書きたいと思います。(追記:書きました)

サンプル

修正したコードのサンプルを GitHub にて公開しています。(前回作成したプログラムのブランチですが)

バインド機能は、(上述の考えから)とりあえず未実装です。

参考

SQLite ポケットリファレンス

SQLite ポケットリファレンス

SQLite Forensics

SQLite Forensics

  • 作者:Paul Sanderson
  • 出版社/メーカー: Independently published
  • 発売日: 2018/05/12
  • メディア: ペーパーバック


  1. Some of the documentation on this website is open to viewing by the general public. However, access to the SEE source code and precompiled binaries requires a username and password, which is available only to licensees. Perpetual source-code licenses are available for purchase separately.