Unity: SQLite setup

# SOLite
簡易データベースを構築するさいにSQLiteを使うことがある。Unityでのsetupの方法をひとつ共有。
## Plugin
[SQLite4Unity3d](https://github.com/codecoding/SQLite4Unity3d/)
コミット履歴も新しくUnity5でも動きそうなのでこれを選んでみた。

## Android Platform
初めはPCでまず動かそうと思って、"Unity SQLite"で検索して初めにヒットした[これ](http://answers.unity3d.com/questions/743400/database-sqlite-setup-for-unity.html)を使った。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

![Screen Shot 2016-02-18 at 15.01.42.png](https://qiita-image-store.s3.amazonaws.com/0/53282/929be851-4bf1-3239-2f50-e84288b44e08.png)


### Code
簡単な処理ならgithubの[scripts](https://github.com/codecoding/SQLite4Unity3d/tree/master/Example/Assets/Scripts)を元にすればできるかと思う。person.csではなくquiz.csを作って、任意のidの欲しいcolumnの情報を取り出せるscriptを追加しただけ。

```csharp: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);
	}
}

```

```csharp: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);
	} 
}
```

```csharp: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](https://www.sqlite.org/foreignkeys.html)
仮で用意したTABLEには"order"というcolumnがあったためか、errorが出ていた。SQLQueryでORDER methodがあるせいなんだろうか。

**予約語だから使えなかった。[sqlite preserved words](https://www.sqlite.org/lang_keywords.html)**