Unity: SQLite setup

SOLite

簡易データベースを構築するさいにSQLiteを使うことがある。Unityでのsetupの方法をひとつ共有。

Plugin

SQLite4Unity3d
コミット履歴も新しくUnity5でも動きそうなのでこれを選んでみた。

Android Platform

初めはPCでまず動かそうと思って、"Unity SQLite"で検索して初めにヒットしたこれを使った。SQLQueryがSQLコマンドそのまま使えるのでPCアプリケーションならこちらが便利だと思う。Androidタブレットで動かなかったので今回は使っていない。

Development

Assets

githubのREADMEのままに必要な物をimportする。YOUTUBEでTutorialまであり分かりやすい。
1. Download this zip, extract its content and copy the resulting folder to your Assets/Plugins folder. It contains the dlls that Unity3d will need to access sqlite.
2. Copy the SQLite.cs file into your scripts folder.
3. Don’t forget to copy your SQLite database file in your Assets/StreamingAssets folder if you’re shipping one.
4. You’re done! Now you can get access to your database using sqlite-net. ;P

Code

簡単な処理ならgithubのscriptsを元にすればできるかと思う。person.csではなくquiz.csを作って、任意のidの欲しいcolumnの情報を取り出せるscriptを追加しただけ。

quiz.cs

using SQLite4Unity3d;

public class quiz {

    [PrimaryKey, AutoIncrement]

    public int id { get; set; }
    public string category { get; set; }
    public string body { get; set; }
    public string answer { get; set; }
    public string choiceA { get; set; }
    public string choiceB { get; set; }
    public string choiceC { get; set; }
    public string commentary { get; set; }
    public string sourcefrom { get; set; }
    public string difficulty { get; set; }

    public override string ToString ()
    {
        return string.Format ("[quiz: id={0}, category={1}, body={2}, answer={3}, choiceA={4}, choiceB={5}, choiceC={6}, commentary={7}, sourcefrom={8}, difficulty={9}]",
            id, category, body, answer, choiceA, choiceB, choiceC, commentary, sourcefrom, difficulty);
    }
}

DataService.cs

using SQLite4Unity3d;
using UnityEngine;
#if !UNITY_EDITOR
using System.Collections;
using System.IO;
#endif
using System.Collections.Generic;

public class DataService  {

    private SQLiteConnection _connection;

    public DataService(string DatabaseName){

#if UNITY_EDITOR
            var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);
#else
        // check if file exists in Application.persistentDataPath
        var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);

        if (!File.Exists(filepath))
        {
            Debug.Log("Database not in Persistent path");
            // if it doesn't ->
            // open StreamingAssets directory and load the db ->

#if UNITY_ANDROID 
            var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
            while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
            // then save to Application.persistentDataPath
            File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
                 var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
#elif UNITY_WP8
                var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);

#elif UNITY_WINRT
        var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
        // then save to Application.persistentDataPath
        File.Copy(loadDb, filepath);
#else
    var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
    // then save to Application.persistentDataPath
    File.Copy(loadDb, filepath);

#endif

            Debug.Log("Database written");
        }

        var dbPath = filepath;
#endif
            _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
        Debug.Log("Final PATH: " + dbPath);     

    }

    public void CreateDB(){
        _connection.DropTable<Person> ();
        _connection.CreateTable<Person> ();

        _connection.InsertAll (new[]{
            new Person{
                Id = 1,
                Name = "Tom",
                Surname = "Perez",
                Age = 56
            },
            new Person{
                Id = 2,
                Name = "Fred",
                Surname = "Arthurson",
                Age = 16
            },
            new Person{
                Id = 3,
                Name = "John",
                Surname = "Doe",
                Age = 25
            },
            new Person{
                Id = 4,
                Name = "Roberto",
                Surname = "Huertas",
                Age = 37
            }
        });
    }

    public IEnumerable<Person> GetPersons(){
        return _connection.Table<Person>();
    }

    public IEnumerable<Person> GetPersonsNamedRoberto(){
        return _connection.Table<Person>().Where(x => x.Name == "Roberto");
    }

    public Person GetJohnny(){
        return _connection.Table<Person>().Where(x => x.Name == "Johnny").FirstOrDefault();
    }

    public Person CreatePerson(){
        var p = new Person{
                Name = "Johnny",
                Surname = "Mnemonic",
                Age = 21
        };
        _connection.Insert (p);
        return p;
    }

    /*
    methods added later..
    */
    public IEnumerable<quiz> GetQuizes(){
        return _connection.Table<quiz>();
    }

    public IEnumerable<quiz> GetSpecificQuiz(int targetInteger){
        return _connection.Table<quiz> ().Where (x => x.id == targetInteger);
    } 

    public IEnumerable<package> GetIndicesOfSpecificPackage(int targetInteger){
        return _connection.Table<package> ().Where (x => x.pid == targetInteger);
    } 
}

ExistingDBScript.cs

using UnityEngine;
using System.Collections.Generic;
using UnityEngine.UI;

public class ExistingDBScript : MonoBehaviour {

    //public Text DebugText;
    int q1 = 0, q2 = 0, q3 = 3, q4 = 0, q5 = 0;
    // Use this for initialization
    void Start () {
        //var ds = new DataService ("existing.db");
        var ds = new DataService ("YOURDATABASE.db3"); // your Database file name
        var quizes = ds.GetQuizes ();
        ToConsole (quizes);

        int targetId = getPackageId ();
        var indices = ds.GetIndicesOfSpecificPackage (targetId);
        ToConsoleIndex (indices);

        var quizA = ds.GetSpecificQuiz (q1);
        var quizB = ds.GetSpecificQuiz (q2);
        var quizC = ds.GetSpecificQuiz (q3);
        var quizD = ds.GetSpecificQuiz (q4);
        var quizE = ds.GetSpecificQuiz (q5);

        // set data
        setQuizObj(quizA, 0);
        setQuizObj(quizB, 1);
        setQuizObj(quizC, 2);
        setQuizObj(quizD, 3);
        setQuizObj(quizE, 4);

    }

    private void ToConsole(IEnumerable<quiz> quizes){
        foreach (var person in quizes) {
            ToConsole(person.ToString());
        }
    }

    void setQuizObj(IEnumerable<quiz> quizes, int arrIndex){
        foreach (var quiz in quizes) {
            GameController.quizObjs [arrIndex] = new GameController.QuizObj(quiz.id, quiz.body, quiz.answer, quiz.choiceA, quiz.choiceB, quiz.choiceC, quiz.commentary, 0, 0, survive.selectedLevel.ToString());
        }
    }

    private void ToConsoleIndex(IEnumerable<package> indices){
        foreach (var index in indices) {
            q1 = index.q1;
            q2 = index.q2;
            q3 = index.q3;
            q4 = index.q4;
            q5 = index.q5;

            ToConsole (index.ToString ());
        }
    }

    private void ToConsole(string msg){
        //DebugText.text += System.Environment.NewLine + msg;
        Debug.Log (msg);
    }

    public static int getPackageId(){
        int level = survive.selectedLevel;
        int pId=0;
        if (level == 1 || level == 0) {
            pId = UnityEngine.Random.Range (1, 4);
        } else if (level == 2) {
            pId = UnityEngine.Random.Range (5, 8);
        } else if (level == 3) {
            pId = UnityEngine.Random.Range (9, 12);
        }
        Debug.Log (pId.ToString());
        return pId;
    }

}

Why => Solved

sqlite foreign key
仮で用意したTABLEには"order"というcolumnがあったためか、errorが出ていた。SQLQueryでORDER methodがあるせいなんだろうか。

予約語だから使えなかった。sqlite preserved words