SOLite
簡易データベースを構築するさいにSQLiteを使うことがある。Unityでのsetupの方法をひとつ共有。
Plugin
SQLite4Unity3d
コミット履歴も新しくUnity5でも動きそうなのでこれを選んでみた。
Android Platform
初めはPCでまず動かそうと思って、"Unity SQLite"で検索して初めにヒットしたこれを使った。SQLQueryがSQLコマンドそのまま使えるのでPCアプリケーションならこちらが便利だと思う。Androidタブレットで動かなかったので今回は使っていない。
Development
Assets
githubのREADMEのままに必要な物をimportする。YOUTUBEでTutorialまであり分かりやすい。
- 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.
- Copy the SQLite.cs file into your scripts folder.
- Don’t forget to copy your SQLite database file in your Assets/StreamingAssets folder if you’re shipping one.
- 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