※当メディアのリンクにはアフィリエイト広告が含まれています

UnityでSQLiteを使用する場合、無料で使えるSQLiteUnityKitが個人の開発者に大人気です。
スクリプトとDLLを配置すればすぐにSQLiteが使用できるようになるので本当に便利ですよね。
ただこのSQLiteUnityKitですが、実は問題がいくつか存在します。

今回はその問題点と対応方法について簡単にご紹介します。

SQLiteUnityKitの問題点

SQLiteUnityKitの問題点ですが、すでに何名かの開発者によって指摘されています。
その問題点とは以下の４つです。

• マルチバイト文字列を扱えない
• アプリの更新時に端末内のDBが初期化される
• トランザクション管理ができない
• SQLにバインド処理を行えない

SQLのバインド処理以外はなかなかクリティカルな問題な気がします。
特にDBの初期化とかめっちゃ怖いやつですよね。
マスタデータ以外の情報を保持するようにしてたらと思うとゾッとします。

１.マルチバイト文字列を扱えない

原因

SQLiteUnityKitで全角文字を含むSQLを実行するとSqliteExceptionが発生します。
その問題となっている箇所はSqliteDatabase.csの以下のメソッドです。

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

sqlite3_prepare_v2の第３引数にqueryのLengthを渡しています。
マルチバイト文字列の場合、LengthとByte数が異なるため例外が発生しているようです。
単純にマルチバイト文字列を考慮していないだけですね。

対応方法

対応方法は２パターンあるようです。

1. LengthではなくByte数を渡す
2. 固定で-1を渡す

それぞれの対応方法は以下の通りです。

・LengthではなくByte数を渡す場合

private IntPtr Prepare (string query)
{
    IntPtr stmHandle;
    int byteCnt= System.Text.Encoding.GetEncoding("UTF-8").GetByteCount(query);
    if (sqlite3_prepare_v2 (_connection, query, byteCnt, out stmHandle, IntPtr.Zero) != SQLITE_OK) {
        IntPtr errorMsg = sqlite3_errmsg (_connection);
        throw new SqliteException (Marshal.PtrToStringAnsi (errorMsg));
    }
        
    return stmHandle;
}

・固定で-1を渡す場合

private IntPtr Prepare (string query)
{
    IntPtr stmHandle;
    
    if (sqlite3_prepare_v2 (_connection, query, -1, out stmHandle, IntPtr.Zero) != SQLITE_OK) { // ←第３引数に-1を指定
        IntPtr errorMsg = sqlite3_errmsg (_connection);
        throw new SqliteException (Marshal.PtrToStringAnsi (errorMsg));
    }
    
    return stmHandle;
}

ちゃんと対応したい場合はByte数渡しの方でしょうね。
ただ、面倒な場合は-1でもいいと思います(笑)

２.アプリの更新時に端末内のDBが初期化される

原因

これについてはおそらくSQLiteUnityKitの仕様と言ってもいいと思います。
SQLiteUnityKitではSQLiteのDBファイルの更新処理が以下のようになっています。

1.端末にDBファイルが存在するかをチェック。存在しなかった場合、StreamingAssetsのDBファイルを端末にコピー。
2.端末にDBファイルが存在した場合、StreamingAssetsのDBファイルと更新日付を比較。StreamingAssetsのDBファイルが新しかったら端末にコピー。

DBファイル新しくなってるんだから、新しいので置き換えちゃいましょうっていう単純な作りですね。
でもこれってマスタデータ以外も扱うDBファイルだったら大損害を与えかねない恐ろしい仕様です。

また、ちょっと別の問題なのですが、端末によってはStreamingAssetsのDBファイルと更新日付が正常に取得できないことがあるらしいです。
＞＞[Unity,SQLiteUnityKit]Android＆SQLiteUnityKitで、DBが更新されない問題 じーにあす

そのため、端末にDBファイルをコピーする際の挙動を整理する必要があります。

対応方法

これは開発者毎に対応方法が異なるので、やらなければいけないことだけをざっくりと書きます。
余裕があれば別記事で細かくまとめようかと。

対応方法として、まずはDBファイルの新旧チェック箇所を以下のように修正します。

修正前：StreamingAssetsのDBファイルの更新日付が新しい場合
修正後：StreamingAssetsのDBファイルのバージョンが端末のDBファイルのバージョンより新しい場合
（DBファイルのバージョン管理については後記）

次にStreamingAssetsのDBファイルのコピー条件を変更します。
修正前：端末にDBファイルが存在しない場合 or StreamingAssetsのDBファイルの更新日付が新しい場合
修正後：端末にDBファイルが存在しない場合
StreamingAssetsのDBファイルのコピーは端末のDBファイルが存在しないときだけ行うようにします。

DBファイルのバージョン管理ですが、DBファイルのバージョンをバージョン管理テーブルで保持します。
アプリの更新時にDBに対しても更新が必要な場合、StreamingAssetsのDBファイルのバージョン管理テーブルの情報を更新します。
端末のDBファイルのバージョンよりも新しいバージョンの場合、新バージョンにするためのマージ処理を行います。
マージの方法についてですが、旧DBから新DBになるにあたり、変更される内容をDDLやSQLで用意してそれを順番に実行していくイメージです。
（この部分が開発者毎に異なる部分です。）

めっちゃざっくりですが、流れ的にはこんな感じです。

３.トランザクション管理ができない

原因

クエリの発行前にコネクションを開き、発行後にコネクションをクローズしているためトランザクションの管理が行えません。
トランザクション管理が行えないと、更新系クエリを複数回投げてる途中で例外が発生した場合にデータの不整合が発生します。
動作に影響しない程度の不整合なら別にいいかもしれませんが（いいわけない）

対応方法

デフォルトの状態ではトランザクション管理が行えないため、SqliteDatabase.csを改修します。

・DllImportを追記

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

※sqlite3_execは文字列のSQLをそのまま実行するための関数です。

・sqlite3_exec実行用メソッドを追加

public void ExecuteQueryExec(string query)
{
    IntPtr stmHandle;
    if (!CanExQuery) {
        Debug.Log ("ERROR: Can't execute the query, verify DB origin file");
        return;
    }
    if (sqlite3_exec (_connection, query, IntPtr.Zero, IntPtr.Zero, out stmHandle) != SQLITE_OK) {
        throw new SqliteException ("Could not execute SQL statement.");
    }
}

・トランザクション管理用のメソッドを追加

public void TransactionStart()
{
    this.Open ();
    this.ExecuteQueryExec("BEGIN");
}
public void TransactionCommit()
{
    this.ExecuteQueryExec("COMMIT");
    this.Close ();
}
public void TransactionRollBack()
{
    this.ExecuteQueryExec("ROLLBACK");
    this.Close ();
}

・トランザクション管理用更新系クエリ発行メソッドを追加

public void ExecuteNonQueryExt(string query, Dictionary<string, object> args)
{
    if (!CanExQuery)
    {
        Debug.Log("ERROR: Can't execute the query, verify DB origin file");
        return;
    }

    IntPtr stmHandle = Prepare(query);

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

既存のExecuteNonQueryメソッドからOpen処理とClose処理を消しただけです。
私は更新系のクエリを実行する場合は極力トランザクション管理をしたいので、既存のメソッドからOpen処理とClose処理を消して対応しました。

使用例

トランザクション管理の使用例です。

SqliteDatabase sdb = new SqliteDatabase("DBファイルパス");
sdb.TransactionStart(); // トランザクション開始
try
{
    string query = "update t_sample SET sample_data = 'Sample Data' where sample_id = 1";
    sdb.ExecuteNonQueryExt(query); // クエリ実行
}
catch (SqliteException ex)
{
    sdb.TransactionRollBack(); // 例外時、ロールバック
}
sdb.TransactionCommit(); // コミット

４.SQLにバインド処理を行えない

原因

SQLiteUnityKitはSQLへのバインド処理が実装されていません。
特に問題というわけではないのですが、今のままではSQLインジェクションの危険性があるため出来ればバインドできた方が安全です。
また、バインド処理を行うことで実装時のソースの可読性が上がります。

対応方法

SQLiteにはバインド用の関数が存在するため、SqliteDatabase.csにいろいろ追記します。

・DllImportを追記

[DllImport("sqlite3", EntryPoint = "sqlite3_bind_parameter_index")]
private static extern int sqlite3_bind_parameter_index(IntPtr stmHandle, string key);

[DllImport("sqlite3", EntryPoint = "sqlite3_bind_int")]
private static extern int sqlite3_bind_int(IntPtr stmHandle, int index, int val);

[DllImport("sqlite3", EntryPoint = "sqlite3_bind_text")]
private static extern int sqlite3_bind_text(IntPtr stmHandle, int index, byte[] value, int length, IntPtr freeType);

[DllImport("sqlite3", EntryPoint = "sqlite3_bind_double")]
private static extern int sqlite3_bind_double(IntPtr stmHandle, int index, double value);

とりあえず数値と文字列だけ対応。

・バインド用メソッドを追加

private void ParameterBind(IntPtr stmHandle, Dictionary<string, object> param)
{
    if (param != null)
    {
        object val;
        foreach (string key in param.Keys)
        {
            val = param[key];
            if (val is int)
            {
               sqlite3_bind_int(stmHandle,sqlite3_bind_parameter_index(stmHandle,key), int.Parse(val.ToString()));
            }
            else if(val is string)
            {
                int lenB = System.Text.Encoding.GetEncoding("UTF-8").GetByteCount(val.ToString());
                sqlite3_bind_text(stmHandle, sqlite3_bind_parameter_index(stmHandle, key), System.Text.Encoding.UTF8.GetBytes(val.ToString()), lenB, new IntPtr(-1));
            }else if(val is double)
            {
                sqlite3_bind_double(stmHandle, sqlite3_bind_parameter_index(stmHandle, key), double.Parse(val.ToString()));
            }
            else
            {
                Debug.Log("Unknown Type : " + val.GetType().ToString());
            }
        }
    }
}

・ExecuteNonQueryメソッドとExecuteQueryメソッドの引数にDictionaryを追加

public void ExecuteNonQuery (string query, Dictionary<string, object> param)
{
    ・・・
}
public void ExecuteQuery (string query, Dictionary<string, object> param)
{
    ・・・
}

・ExecuteNonQueryメソッドとExecuteQueryメソッド処理にバインド処理を追記

public void ExecuteNonQuery (string query, Dictionary<string, object> param)
{
    ・・・
    IntPtr stmHandle = Prepare (query);
    ParameterBind(stmHandle, args); // ←これを追記
    ・・・
}
public void ExecuteQuery (string query, Dictionary<string, object> param)
{
    ・・・
    IntPtr stmHandle = Prepare (query);
    ParameterBind(stmHandle, args); // ←これを追記
    ・・・
}

※トランザクション管理でクエリ発行のメソッドを追加している場合はそちらにも対応する必要があります。

使用例

バインド処理の使用例です。

SqliteDatabase sdb = new SqliteDatabase("DBファイルパス");
string query = "select * from t_sample where sample_id = :param1 and sample_data = :param2";
Dictionary param = new Dictionary();
param[":param1"] = 1;
param[":param2"] = "sample";
sdb.ExecuteNonQuery(query, param);

最後に

一応一通り動作確認はしていますが、動かなかったらごめんなさい。