public int Get_upgrade_level(string a_data_code)
{
if (_db == null)
{
_db = new SQLiteDB();
}
int rtn = 0;
string filename = GetFileName_DB();
try{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_upgrade_level, a_data_code); // _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
rtn = qr.GetInteger("upgrade_level");
}
qr.Release();
_db.Close();
} catch (Exception e) {
if (_db != null)
{
_db.Close();
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rtn);
}
public void Update_score(int a_level_no, int a_score)
{
if (_db == null)
{
_db = new SQLiteDB();
}
try
{
_db.Open(GetFileName_DB());
SQLiteQuery qr;
string strsql = string.Format(_queryUpdate_pzlevel_score, a_level_no); //
qr = new SQLiteQuery(_db, strsql);
qr.Bind(a_score);
qr.Step();
qr.Release();
_db.Close();
}
catch (Exception e)
{
if (_db != null)
{
_db.Close();
_db = null;
}
UnityEngine.Debug.LogError(e.ToString());
}
return;
}
public float Get_upgrade_value(string a_data_code, int a_level)
{
if (_db == null)
{
_db = new SQLiteDB();
}
float rtn = 0f;
string filename = GetFileName_DB();
try{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_value, a_data_code, a_level); // _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
rtn = (float)qr.GetDouble("value");
}
qr.Release();
_db.Close();
} catch (Exception e) {
if (_db != null)
{
_db.Close();
_db = null;
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rtn);
}
public ST_B_UnitRec Get_UnitRec(string a_unit_code, int a_level, int a_class_no)
{
if (_db == null)
{
_db = new SQLiteDB();
}
ST_B_UnitRec rtn = new ST_B_UnitRec();
string filename = GetFileName_DB();
try{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_UnitRec, a_unit_code, a_level, a_class_no); // _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
try
{
rtn.attack = qr.GetInteger("attack");
}
catch
{
try
{
rtn.attack = (int)qr.GetDouble("attack");
}
catch
{
rtn.attack = 1;
}
}
rtn.gold_max = qr.GetInteger("gold_max");
rtn.gold_min = qr.GetInteger("gold_min");
rtn.hp = qr.GetInteger("hp");
rtn.class_no = qr.GetInteger("class_no");
rtn.level = a_level;
rtn.point_max = qr.GetInteger("point_max");
rtn.point_min = qr.GetInteger("point_min");
rtn.unit_code = a_unit_code;
}
qr.Release();
_db.Close();
} catch (Exception e) {
if (_db != null)
{
_db.Close();
_db = null;
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rtn);
}
public int Get_point_min(string a_model_name)
{
_db_filename = GetFileName_SaveDB();
if (_db == null)
{
_db = new SQLiteDB();
}
int rtn = 0; //---
try{
_db.Open(_db_filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_point_min, a_model_name); //--- _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
rtn = qr.GetInteger("point_min"); //---
}
qr.Release();
_db.Close();
} catch (Exception e) {
if (_db != null)
{
_db.Close();
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rtn); //---
}
public bool getStatus(int id)
{
bool result = false;
int num;
string dbFile = Application.persistentDataPath + "/optionDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT result FROM option_table WHERE id=?");
qr.Bind(id);
qr.Step();
num = qr.GetInteger("result");
switch (num)
{
case 0:
result = false;
break;
case 1:
result = true;
break;
}
qr.Release();
db.Close();
return result;
}
public void OnClickBtnGameStart()
{
string query = "SELECT COUNT(*) AS NUM FROM Player;";
int num = 0;
SQLiteQuery qr;
qr = new SQLiteQuery(db, query);
if (qr.Step())
{
num = qr.GetInteger("NUM");
}
if (num == 0)
{
btnGameStart.enabled = false;
btnOption.enabled = false;
btnQuit.enabled = false;
panelCreate.SetActive(true);
qr.Release();
}
else
{
db.Close();
GameObject.Find("GameDirector").GetComponent <AudioSource>().Stop();
Application.LoadLevel("GameScene");
}
}
public bool isUnlocked(int id)
{
bool result = false;
int num;
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT Unlocked FROM achieve_table WHERE id=?");
qr.Bind(id);
qr.Step();
num = qr.GetInteger("Unlocked");
switch (num)
{
case 0:
result = false;
break;
case 1:
result = true;
break;
}
qr.Release();
db.Close();
return(result);
}
public ST_B_Style_Info[] GetList_all_where_datacode_level(string a_datacode, int a_level)
{
if (_db == null)
{
_db = new SQLiteDB();
}
// ST_B_Style_Info rec;
List<ST_B_Style_Info> rtnlist = new List<ST_B_Style_Info>();
string filename = GetFileName_DB();
try
{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_all_level, a_datacode, a_level); //
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
ST_B_Style_Info rec = new ST_B_Style_Info();
rec._attack = qr.GetInteger("attack");
rec._critical = (float)qr.GetDouble("critical");
rec._speed = (float)qr.GetDouble("speed");
rec._up_gold = qr.GetInteger("up_gold");
rec._data_code = qr.GetString("data_code");
rec._upgrade_level = qr.GetInteger("upgrade_level");
rtnlist.Add(rec);
}
qr.Release();
_db.Close();
}
catch (Exception e)
{
if (_db != null)
{
_db.Close();
}
UnityEngine.Debug.LogError(e.ToString());
}
return rtnlist.ToArray();
}
public override void CloseDB()//关闭数据库连接
{
if (db != null)
{
db.Close();
}
}
public void OnClickBtnQuit()
{
if (GameDirector.inBossZone == false)
{
//레벨,최대HP,최대MP,최대EXP,HP,MP,EXP,X,Y,Z,STR,CON,AGI순서
string query = "UPDATE Player SET LEVEL=?,MAX_HP=?,MAX_MP=?,MAX_EXP=?,HP=?,MP=?,EXP=?,X=?,Y=?,Z=?,STR=?,CON=?,AGI=?,POINT=? WHERE NAME=?;";
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Bind(player.Level);
qr.Bind(player.MaxHp);
qr.Bind(player.MaxMp);
qr.Bind(player.MaxExp);
qr.Bind(player.Hp);
qr.Bind(player.Mp);
qr.Bind(player.Exp);
qr.Bind(player.X);
qr.Bind(player.Y);
qr.Bind(player.Z);
qr.Bind(player.Str);
qr.Bind(player.Con);
qr.Bind(player.Agi);
qr.Bind(player.Point);
qr.Bind(player.name.GetComponent <TextMesh>().text);
qr.Step();
qr.Release();
if (Inventory.items.Count > 0)
{
query = "UPDATE Inventory Set TYPE=?,EA=? WHERE SLOT=?;";
qr = new SQLiteQuery(db, query);
qr.Bind(Inventory.items[0].Type);
qr.Bind(Inventory.items[0].Ea);
qr.Bind("SLOT1");
qr.Step();
qr.Release();
}
if (Inventory.items.Count > 1)
{
query = "UPDATE Inventory Set TYPE=?,EA=? WHERE SLOT=?;";
qr = new SQLiteQuery(db, query);
qr.Bind(Inventory.items[1].Type);
qr.Bind(Inventory.items[1].Ea);
qr.Bind("SLOT2");
qr.Step();
qr.Release();
}
db.Close();
GameObject.Find("GameDirector").GetComponent <AudioSource>().Stop();
Application.LoadLevel("MainScene");
}
else
{
panelMessage.SetActive(true);
}
}
public JSONObject Get_JsonDataRec_BalanceDB(string v_query)
{
var obj_rtn = new JSONObject();
if (_db == null)
{
_db = new SQLiteDB();
}
string filename = DataManager.Instance.GetDataFilePath_BalanceDB();
try
{
_db.Open(filename);
SQLiteQuery qr;
// UnityEngine.Debug.Log("~~~~~~~~~~ Get_JsonDataRec_BalanceDB() v_query:" + v_query);
qr = new SQLiteQuery(_db, v_query);
// List<string> l_lst_row = new List<string>();
JSONArray list_value = new JSONArray();
while (qr.Step())
{
JSONObject obj_tmp = qr.GetRowJson();
// l_lst_row.Add(strtmp);
list_value.Add(obj_tmp);
}
obj_rtn.Add("rows", list_value);
qr.Release();
_db.Close();
}
catch (System.Exception e)
{
if (_db != null)
{
_db.Close();
_db = null;
}
UnityEngine.Debug.LogError(e.ToString());
}
// Debug.Log("~~~~~~~~~~~~~~~~~~~~~~ obj_rtn:" + obj_rtn.ToString());
return(obj_rtn);
}
public void Clear()
{
if (null == m_pSQLiteDB)
{
return;
}
m_pSQLiteDB.Close();
m_pSQLiteDB = null;
}
public ST_B_stage_spawnRec[] Get_stage_spawnData(int a_stage_idx)
{
if (_db == null)
{
_db = new SQLiteDB();
}
string filename = GetFileName_DB();
List <ST_B_stage_spawnRec> rtnlist = new List <ST_B_stage_spawnRec>();
try
{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_all_stage_spawn, a_stage_idx); // _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
ST_B_stage_spawnRec rec = new ST_B_stage_spawnRec();
rec.idx = qr.GetInteger("idx");
rec.level_max = qr.GetInteger("level_max");
rec.level_min = qr.GetInteger("level_min");
rec.spawn_cost = qr.GetInteger("spawn_cost");
rec.stage_idx = a_stage_idx;
rec.time_limit = qr.GetInteger("time_limit");
rtnlist.Add(rec);
}
qr.Release();
_db.Close();
}
catch (Exception e)
{
if (_db != null)
{
_db.Close();
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rtnlist.ToArray());
}
public void SQLiteDB_Close_void_ret_void()
{
// Prepara
IDataBase db = new SQLiteDB();
string closed = db.ConnectionState.ToString();
db.Close();
db.ConnectionString = "TestDataBase";
db.Open();
string opened = db.ConnectionState.ToString();
// Ejecuta
db.Close();
// Comprueba
db.Close();
Assert.AreEqual("Closed", closed);
Assert.AreEqual("Open", opened);
Assert.AreEqual("Closed", db.ConnectionState.ToString());
}
/// <summary>
/// 打开SQLITE数据库
/// </summary>
static void OpenSqlite()
{
if (db != null)
{
db.Close();
}
if (string.IsNullOrEmpty(dbfile))
{
Debug.LogError("数据库文件路径为空!!!");
return;
}
db = new SQLiteDB();
db.Open(@dbfile);
if (!string.IsNullOrEmpty(password))
{
db.Key(password); //Set Password
}
PlayerPrefs.SetString("Tool_SqliteFile", dbfile);
PlayerPrefs.SetString("Tool_SqlitePass", password);
Debug.Log("数据库链接打开成功，数据文件:>" + dbfile);
}
public void setStatus(int id, int choice)
{
string dbFile = Application.persistentDataPath + "/optionDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
string query = "UPDATE option_table SET Unlocked = " + choice.ToString() + " WHERE id =" + id.ToString();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
public SQLiteQuery(SQLiteDB sqliteDb, string query)
{
sqlDb = sqliteDb;
bindIndex = 1;
db = sqliteDb.Connection();
if (Sqlite3.sqlite3_prepare_v2(db, query, query.Length, ref vm, 0) != Sqlite3.SQLITE_OK)
{
sqliteDb.Close();
throw new Exception("Error with prepare query! error:" + Sqlite3.sqlite3_errmsg(db));
}
;
sqlDb.RegisterQuery(this);
}
public void SQLiteDB_Open_str_ConnStrName_ret_void()
{
// Prepara
IDataBase db = new SQLiteDB();
// Ejecuta
db.ConnectionString = "TestDataBase";
db.Open();
// Comprueba
Assert.AreEqual(DBConnectionState.Open, db.ConnectionState);
db.Close();
}
public ST_B_PlayerWeaponRec Get_PlayerWeaponRec(string a_datacode)
{
if (_db == null)
{
_db = new SQLiteDB();
}
ST_B_PlayerWeaponRec rec = new ST_B_PlayerWeaponRec();
string filename = GetFileName_DB();
try{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_PlayerWeaponRec, a_datacode); // _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
rec._datacode = qr.GetString("datacode");
rec._dur = qr.GetInteger("dur");
rec._attack = qr.GetInteger("attack");
rec._level = qr.GetInteger("level");
rec._critical_ratio = (float)qr.GetDouble("critical_ratio");
}
qr.Release();
_db.Close();
} catch (Exception e) {
if (_db != null)
{
_db.Close();
_db = null;
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rec);
}
public ST_B_stageRec Get_stageData(int a_stage_no, int a_round_no)
{
if (_db == null)
{
_db = new SQLiteDB();
}
string filename = GetFileName_DB();
ST_B_stageRec rec = new ST_B_stageRec();
try
{
_db.Open(filename);
SQLiteQuery qr;
string strsql = string.Format(_querySelect_all_stage_round, a_stage_no, a_round_no); // _querySelect_exp
qr = new SQLiteQuery(_db, strsql);
while (qr.Step())
{
rec.idx = qr.GetInteger("idx");
rec.stage_no = qr.GetInteger("stage_no");
rec.round_no = qr.GetInteger("round_no");
rec.boss_bonus_normal = qr.GetInteger("boss_bonus_normal");
rec.boss_bonus_special = qr.GetInteger("boss_bonus_special");
rec.boss_fail_ratio = (float)qr.GetDouble("boss_fail_ratio");
}
qr.Release();
_db.Close();
}
catch (Exception e)
{
if (_db != null)
{
_db.Close();
}
UnityEngine.Debug.LogError(e.ToString());
}
return(rec);
}
public void StoreValue (int id, int a_value)
{
SQLiteQuery qr;
string query = "UPDATE stats_table SET value = " + a_value.ToString() + " WHERE id =" + id.ToString();
SQLiteDB db = new SQLiteDB ();
string filename = Application.persistentDataPath + "/statsDB_playmaker.db";
db.Open (filename);
qr = new SQLiteQuery (db, query);
qr.Step ();
qr.Release();
db.Close ();
}
public void StoreValue(int id, int a_value)
{
SQLiteQuery qr;
string query = "UPDATE stats_table SET value = " + a_value.ToString() + " WHERE id =" + id.ToString();
SQLiteDB db = new SQLiteDB();
string filename = Application.persistentDataPath + "/statsDB_playmaker.db";
db.Open(filename);
qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
public void StoreProgress(int id, int progress)
{
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
string query = "UPDATE achieve_table SET Progress = " + progress.ToString() + " WHERE id =" + id.ToString();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
public int getDefense() {
int num = 0;
string dbFile = Application.persistentDataPath + "/playerDB.db";
SQLiteDB db = new SQLiteDB ();
db.Open (dbFile);
SQLiteQuery qr = new SQLiteQuery (db, "SELECT defense FROM player_info WHERE id=1");
qr.Step ();
num = qr.GetInteger ("defense");
qr.Release ();
db.Close ();
return num;
}
public int getMoney(int id) {
int num = 0;
string dbFile = Application.persistentDataPath + "/mobDB.db";
SQLiteDB db = new SQLiteDB ();
db.Open (dbFile);
SQLiteQuery qr = new SQLiteQuery (db, "SELECT money FROM mob_info WHERE id=?");
qr.Bind (id);
qr.Step ();
num = qr.GetInteger ("money");
qr.Release ();
db.Close ();
return num;
}
public void setStatus(int id, int choice)
{
string dbFile = Application.persistentDataPath + "/optionDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
string query = "UPDATE option_table SET Unlocked = " + choice.ToString() + " WHERE id =" + id.ToString();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
void PerformanceTest(SQLiteDB db, ref string log)
{
SQLiteQuery qr;
qr = new SQLiteQuery(db, "SELECT * FROM en WHERE word like 'a%' LIMIT 1");
qr.Step();
UnityEngine.Debug.Log(qr.GetString("word"));
qr.Release(); log += "\nSelect.";
//
// if we reach that point it's mean we pass the test!
db.Close(); log += "\nDatabase closed!\nTest succeeded!";
}
public string getName(int id) {
string name = "";
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT Name FROM achieve_table WHERE id=?");
qr.Bind(id);
while (qr.Step()) {
name = qr.GetString("Name");
}
qr.Release();
db.Close();
return name;
}
public void unlock(int id)
{
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
int yes = 1;
string query = "UPDATE achieve_table SET Unlocked = " + yes.ToString() + " WHERE id =" + id.ToString();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
public int getTarget(int id) {
int num = 0;
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT Target FROM achieve_table WHERE id=?");
qr.Bind(id);
qr.Step();
num = qr.GetInteger("Target");
qr.Release();
db.Close();
return num;
}
protected void Dispose(bool Diposing)
{
if (!mIsDisposed)
{
if (Diposing)
{
//Clean Up managed resources
if (mDbConnect != null)
{
mDbConnect.Close();
mDbConnect = null;
}
}
//Clean up unmanaged resources
}
mIsDisposed = true;
}
public string getDesc(int id) {
string description = "";
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT Description FROM achieve_table WHERE id=?");
qr.Bind(id);
while (qr.Step()) {
description = qr.GetString("Description");
}
qr.Release();
db.Close();
return description;
}
public string getName(int id)
{
string name = "";
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT Name FROM achieve_table WHERE id=?");
qr.Bind(id);
while (qr.Step())
{
name = qr.GetString("Name");
}
qr.Release();
db.Close();
return(name);
}
public int getTarget(int id)
{
int num = 0;
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, "SELECT Target FROM achieve_table WHERE id=?");
qr.Bind(id);
qr.Step();
num = qr.GetInteger("Target");
qr.Release();
db.Close();
return(num);
}
public int getValue (string name)
{
int num = 0;
string filename = Application.persistentDataPath + "/statsDB_playmaker.db";
SQLiteDB db = new SQLiteDB ();
SQLiteQuery qr;
string query = "SELECT value FROM stats_table WHERE name =?";
db.Open (filename);
qr = new SQLiteQuery (db, query);
qr.Bind (name);
qr.Step ();
num = qr.GetInteger ("value");
qr.Release ();
db.Close ();
return num;
}
private void CloseDatabase(ThreadQueue.TaskControl control, object state)
{
try
{
if (db != null)
{
db.Close();
db = null;
}
else
{
throw new Exception("Database not ready!");
}
}
catch (Exception ex)
{
Debug.LogError("SQLiteAsync : Exception : " + ex.Message);
}
}
public int getValue(string name)
{
int num = 0;
string filename = Application.persistentDataPath + "/statsDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
SQLiteQuery qr;
string query = "SELECT value FROM stats_table WHERE name =?";
db.Open(filename);
qr = new SQLiteQuery(db, query);
qr.Bind(name);
qr.Step();
num = qr.GetInteger("value");
qr.Release();
db.Close();
return(num);
}
void Test2(SQLiteDB db, ref string log)
{
SQLiteQuery qr;
//
// read strings
//
string testStringFromSelect = "";
qr = new SQLiteQuery(db, querySelect);
while (qr.Step())
{
testStringFromSelect = qr.GetString("str_field");
if (testStringFromSelect != testString)
{
throw new Exception("Test string are not equal!");
}
byte[] testBlobFromSelect = qr.GetBlob("blob_field");
if (testBlobFromSelect.Length != testBlob.Length)
{
throw new Exception("Test blobs are not equal!");
}
for (int i = 0; i < testBlobFromSelect.Length; i++)
{
if (testBlobFromSelect[i] != testBlob[i])
{
throw new Exception("Test blobs are not equal!");
}
}
}
if (testStringFromSelect == "")
{
throw new Exception("Unknowm problem!");
}
qr.Release(); log += "\nRead and test strings and blobs.";
//
// if we reach that point it's mean we pass the test!
db.Close(); log += "\nDatabase closed!\nTest succeeded!";
}
void OnGUI()
{
if( db == null )
{
if ( GUI.Button(new Rect (10,10,150,50), "Run SQLite Test \nat persistentDataPath") )
{
db = new SQLiteDB();
string filename = Application.persistentDataPath + "/demo_1.db";
log = "";
try{
//
// initialize database
//
db.Open(filename); log += "Database created! filename:"+filename;
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n on WebPlayer it must give an exception, it's normal.";
}
}
if ( GUI.Button(new Rect (10,70,150,70), "Run SQLite Test \nat :memory:\nbest for cache") )
{
db = new SQLiteDB();
log = "";
try{
//
// initialize database
//
db.OpenInMemory(); log += "Database created! in :memory:";
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
}
if ( GUI.Button(new Rect (10,150,150,70), "Run SQLite Test \nwith stream\nbest for WebPlayer") )
{
db = new SQLiteDB();
MemoryStream memStream = new MemoryStream();
log = "";
try{
//
// initialize database
//
db = new SQLiteDB();
db.OpenStream("stream1",memStream); log += "Database created! named stream: stream1";
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
}
if ( GUI.Button(new Rect (10,230,150,70), "Run SQLite Test \nwith streamingAssets") )
{
db = new SQLiteDB();
log = "";
string dbfilename = "test.db";
byte[] bytes = null;
#if UNITY_EDITOR || UNITY_STANDALONE_WIN || UNITY_STANDALONE_OSX
string dbpath = "file://" + Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#elif UNITY_WEBPLAYER
string dbpath = "StreamingAssets/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#elif UNITY_IPHONE
string dbpath = Application.dataPath + "/Raw/" + dbfilename; log += "asset path is: " + dbpath;
try{
using ( FileStream fs = new FileStream(dbpath, FileMode.Open, FileAccess.Read, FileShare.Read) ){
bytes = new byte[fs.Length];
fs.Read(bytes,0,(int)fs.Length);
}
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
#elif UNITY_ANDROID
string dbpath = Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#endif
if ( bytes != null )
{
try{
//
// create and write the asset into memory stream
//
MemoryStream memStream = new MemoryStream();
memStream.Write(bytes,0,bytes.Length);
//
// initialize database
//
db.OpenStream("stream2",memStream); log += "\nDatabase created! named stream: stream2";
Test2(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n\n Did you copy test.db into StreamingAssets ?\n";
}
}
}
if ( GUI.Button(new Rect (170,10,150,70), "Run SQLite Test \nfor streamingAssets db\nwhich moved to \npersistentDataPath") )
{
db = new SQLiteDB();
log = "";
string dbfilename = "test.db";
byte[] bytes = null;
#if UNITY_EDITOR || UNITY_STANDALONE_WIN || UNITY_STANDALONE_OSX
string dbpath = "file://" + Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#elif UNITY_WEBPLAYER
string dbpath = "StreamingAssets/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#elif UNITY_IPHONE
string dbpath = Application.dataPath + "/Raw/" + dbfilename; log += "asset path is: " + dbpath;
try{
using ( FileStream fs = new FileStream(dbpath, FileMode.Open, FileAccess.Read, FileShare.Read) ){
bytes = new byte[fs.Length];
fs.Read(bytes,0,(int)fs.Length);
}
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
#elif UNITY_ANDROID
string dbpath = Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#endif
if ( bytes != null )
{
try{
string filename = Application.persistentDataPath + "/demo_from_streamingAssets.db";
//
//
// copy database to real file into cache folder
using( FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write) )
{
fs.Write(bytes,0,bytes.Length); log += "\nCopy database from streaminAssets to persistentDataPath: " + filename;
}
//
// initialize database
//
db.Open(filename); log += "\nDatabase created! filename: " + filename;
Test2(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n\n Did you copy test.db into StreamingAssets ?\n";
}
}
}
//
// Very common scenario for most products.
//
if ( GUI.Button(new Rect (170,90,150,70), "Run SQLite\nfor real project scenario") )
{
db = new SQLiteDB();
log = "";
// a product persistant database path.
string filename = Application.persistentDataPath + "/demo_from_streamingAssets2.db";
// check if database already exists.
if(!File.Exists(filename))
{
// ok , this is first time application start!
// so lets copy prebuild dtabase from StreamingAssets and load store to persistancePath with Test2
string dbfilename = "test.db";
byte[] bytes = null;
#if UNITY_EDITOR || UNITY_STANDALONE_WIN || UNITY_STANDALONE_OSX
string dbpath = "file://" + Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#elif UNITY_WEBPLAYER
string dbpath = "StreamingAssets/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#elif UNITY_IPHONE
string dbpath = Application.dataPath + "/Raw/" + dbfilename; log += "asset path is: " + dbpath;
try{
using ( FileStream fs = new FileStream(dbpath, FileMode.Open, FileAccess.Read, FileShare.Read) ){
bytes = new byte[fs.Length];
fs.Read(bytes,0,(int)fs.Length);
}
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
#elif UNITY_ANDROID
string dbpath = Application.streamingAssetsPath + "/" + dbfilename; log += "asset path is: " + dbpath;
WWW www = new WWW(dbpath);
Download(www);
bytes = www.bytes;
#endif
if ( bytes != null )
{
try{
//
//
// copy database to real file into cache folder
using( FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write) )
{
fs.Write(bytes,0,bytes.Length); log += "\nCopy database from streaminAssets to persistentDataPath: " + filename;
}
//
// initialize database
//
db.Open(filename); log += "\nDatabase created! filename: " + filename;
Test2(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n\n Did you copy test.db into StreamingAssets ?\n";
}
}
}
else
{
// it mean we already download prebuild data base and store into persistantPath
// lest update, I will call Test
try{
//
// initialize database
//
db.Open(filename); log += "Database created! filename:"+filename;
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n on WebPlayer it must give an exception, it's normal.";
}
}
}
#if ENCRYPTION
//
// ENCRYPTION
//
if ( GUI.Button(new Rect (170,170,150,70), "Run SQLite\nfor ENCRYPTION scenario") )
{
try {
db = new SQLiteDB();
log = "";
// a product persistant database path.
string filename = Application.persistentDataPath + "/demo_from_encryption.db";
File.Delete(filename);
db.Open(filename);
//
// set ENCRYPTION
//
SQLiteQuery qr = new SQLiteQuery(db,"PRAGMA hexkey=\"0x0102030405060708090a0b0c0d0e0f10\";");
qr.Step();
qr.Release();
//
// create table
//
qr = new SQLiteQuery(db, queryCreate);
qr.Step();
qr.Release(); log += "\nTable created.";
//
// insert string and blob
//
qr = new SQLiteQuery(db, queryInsert);
qr.Bind(testString);
qr.Bind(testBlob);
qr.Step();
qr.Release(); log += "\nInsert test string and blob.";
// close
db.Close();
// and open again
db.Open(filename);
//
// set ENCRYPTION AGAIN, you could try change to see ENCRYPTION works
//
qr = new SQLiteQuery(db,"PRAGMA hexkey=\"0x0102030405060708090a0b0c0d0e0f10\";");
qr.Step();
qr.Release();
// do test
Test2( db, ref log );
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n on WebPlayer it must give an exception, it's normal.";
}
}
#endif
}
else
{
if ( GUI.Button(new Rect (10,10,150,50), "Back") )
{
db = null;
}
GUI.Label (new Rect (10,70,600,600), log);
}
}
public void StoreProgress(int id, int progress) {
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
string query = "UPDATE achieve_table SET Progress = " + progress.ToString() + " WHERE id =" + id.ToString();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
private void QuerySQL (SQLiteDB sqliteDB, string sql) {
Debug.Log ("sql = " + sql);
SQLiteQuery sqliteQuery = new SQLiteQuery (sqliteDB, sql);
sqliteQuery.Step ();
sqliteDB.Close ();
}
public void unlock(int id) {
string dbFile = Application.persistentDataPath + "/achieveDB_playmaker.db";
SQLiteDB db = new SQLiteDB();
int yes = 1;
string query = "UPDATE achieve_table SET Unlocked = " + yes.ToString() + " WHERE id =" + id.ToString();
db.Open(dbFile);
SQLiteQuery qr = new SQLiteQuery(db, query);
qr.Step();
qr.Release();
db.Close();
}
void OnGUI()
{
if( db == null )
{
if ( GUI.Button(new Rect (10,10,150,50), "Run SQLite Test \nat persistentDataPath") )
{
db = new SQLiteDB();
string filename = Application.persistentDataPath + "/demo_1.db";
log = "";
try{
//
// initialize database
//
db.Open(filename); log += "Database created! filename:"+filename;
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n on WebPlayer it must give an exception, it's normal.";
}
}
if ( GUI.Button(new Rect (10,70,150,70), "Run SQLite Test \nat :memory:\nbest for cache") )
{
db = new SQLiteDB();
log = "";
try{
//
// initialize database
//
db.OpenInMemory(); log += "Database created! in :memory:";
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
}
if ( GUI.Button(new Rect (10,150,150,70), "Run SQLite Test \nwith stream\nbest for WebPlayer") )
{
db = new SQLiteDB();
MemoryStream memStream = new MemoryStream();
log = "";
try{
//
// initialize database
//
db = new SQLiteDB();
db.OpenStream("stream1",memStream); log += "Database created! named stream: stream1";
Test(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
}
if ( GUI.Button(new Rect (10,230,150,70), "Run SQLite Test \nwith streamingAssets") )
{
StartCoroutine(TestWithStreamingAssets());
}
if ( GUI.Button(new Rect (170,10,150,70), "Run SQLite Test \nfor streamingAssets db\nwhich moved to \npersistentDataPath") )
{
StartCoroutine(TestWithStreamingAssetsCopiedToPersistentDataPath());
}
//
// Very common scenario for most products.
//
if ( GUI.Button(new Rect (170,90,150,70), "Run SQLite\nfor real project scenario") )
{
StartCoroutine(TestBestForRealProject());
}
#if ENCRYPTION
//
// ENCRYPTION
//
if ( GUI.Button(new Rect (170,170,150,70), "Run SQLite\nfor ENCRYPTION scenario") )
{
try {
db = new SQLiteDB();
log = "";
// a product persistant database path.
string filename = Application.persistentDataPath + "/demo_from_encryption.db";
File.Delete(filename);
db.Open(filename);
//
// set ENCRYPTION
//
SQLiteQuery qr = new SQLiteQuery(db,"PRAGMA hexkey=\"0x0102030405060708090a0b0c0d0e0f10\";");
qr.Step();
qr.Release();
//
// create table
//
qr = new SQLiteQuery(db, queryCreate);
qr.Step();
qr.Release(); log += "\nTable created.";
//
// insert string and blob
//
qr = new SQLiteQuery(db, queryInsert);
qr.Bind(testString);
qr.Bind(testBlob);
qr.Step();
qr.Release(); log += "\nInsert test string and blob.";
// close
db.Close();
// and open again
db.Open(filename);
//
// set ENCRYPTION AGAIN, you could try change to see ENCRYPTION works
//
qr = new SQLiteQuery(db,"PRAGMA hexkey=\"0x0102030405060708090a0b0c0d0e0f10\";");
qr.Step();
qr.Release();
// do test
Test2( db, ref log );
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n on WebPlayer it must give an exception, it's normal.";
}
}
#endif
//
// PlayerPrefs
//
if ( GUI.Button(new Rect (170,250,150,50), "Copy db to\n PlayerPrefs") )
{
StartCoroutine(CopyDB2PlayerPrefs());
}
if ( GUI.Button(new Rect (170,310,150,50), "Run SQLite Test \nfor PlayerPrefs") )
{
db = new SQLiteDB();
MemoryStream memStream = new MemoryStream();
log = "";
string testdbz = PlayerPrefs.GetString("sqlitedb");
log += "Read Database from PlayerPrefs! : " + testdbz.Substring(0,64) + "...\n";
if(testdbz.Length > 0)
{
for(int i=0; i*2<testdbz.Length; i++)
{
memStream.WriteByte(Convert.ToByte(testdbz.Substring(i*2,2),16));
}
try{
//
// initialize database
//
db = new SQLiteDB();
db.OpenStream("stream3",memStream); log += "Database created from PlayerPrefs! named stream: stream3";
Test2(db, ref log);
} catch (Exception e){
log += "\nTest Fail with Exception " + e.ToString();
log += "\n";
}
}
else
{
log += "Please push button above to put database into PlayerPrefs!\n";
}
}
}
else
{
if ( GUI.Button(new Rect (10,10,150,50), "Back") )
{
db = null;
}
GUI.Label (new Rect (10,70,600,600), log);
}
}
void Test2( SQLiteDB db, ref string log )
{
SQLiteQuery qr;
//
// read strings
//
string testStringFromSelect = "";
qr = new SQLiteQuery(db, querySelect);
while( qr.Step() )
{
testStringFromSelect = qr.GetString("str_field");
if( testStringFromSelect != testString )
{
throw new Exception( "Test string are not equal!" );
}
byte[] testBlobFromSelect = qr.GetBlob("blob_field");
if( testBlobFromSelect.Length != testBlob.Length )
{
throw new Exception( "Test blobs are not equal!" );
}
for (int i = 0; i < testBlobFromSelect.Length; i++)
{
if( testBlobFromSelect[i] != testBlob[i] )
{
throw new Exception( "Test blobs are not equal!" );
}
}
}
if( testStringFromSelect == "" )
{
throw new Exception( "Unknowm problem!" );
}
qr.Release(); log += "\nRead and test strings and blobs.";
//
// if we reach that point it's mean we pass the test!
db.Close(); log += "\nDatabase closed!\nTest succeeded!";
}
public int getInit(int id) {
int num = 0;
string dbFile = Application.persistentDataPath + "/playerDB.db";
SQLiteDB db = new SQLiteDB ();
db.Open (dbFile);
SQLiteQuery qr = new SQLiteQuery (db, "SELECT init FROM player_info WHERE id=?");
qr.Bind (id);
qr.Step ();
num = qr.GetInteger ("init");
qr.Release ();
db.Close ();
return num;
}
public void setMoney(int money) {
string dbFile = Application.persistentDataPath + "/playerDB.db";
SQLiteDB db = new SQLiteDB ();
string query = "UPDATE player_info SET money = " + money.ToString () + " WHERE id = 1";
db.Open (dbFile);
SQLiteQuery qr = new SQLiteQuery (db, query);
qr.Step ();
qr.Release ();
db.Close ();
}
public void setMaxHP_MP(int maxHP, int maxMP) {
string dbFile = Application.persistentDataPath + "/playerDB.db";
SQLiteDB db = new SQLiteDB ();
string query = "UPDATE player_info SET maxhp = " + maxHP.ToString () + ", maxmp = " + maxMP.ToString () + " WHERE id = 1";
db.Open (dbFile);
SQLiteQuery qr = new SQLiteQuery (db, query);
qr.Step ();
qr.Release ();
db.Close ();
}
void Update () {
if(test == true)
{
test = false;
SQLiteQuery qr = null;
SQLiteDB db = null;
try{
db = new SQLiteDB();
db.Open(FILE);
db.Key("0x0102030405060708090a0b0c0d0e0f10");
// or
//qr = new SQLiteQuery(db, queryKey);
//qr.Step();
//qr.Release();
//qr = null;
qr = new SQLiteQuery(db,queryCreate);
qr.Step();
qr.Release();
qr = null;
qr = new SQLiteQuery(db,queryCreate2);
qr.Step();
qr.Release();
qr = null;
qr = new SQLiteQuery(db, queryInsert);
qr.Bind(testString);
qr.Bind(testBlob);
qr.Step();
qr.Step();
qr.Step();
qr.Release();
qr = null;
db.Close();
db = null;
UnityEngine.Debug.Log("Close and open again.");
db = new SQLiteDB();
db.Open(FILE);
db.Key("0x0102030405060708090a0b0c0d0e0f10");
// or
//qr = new SQLiteQuery(db, queryKey);
//qr.Step();
//qr.Release();
//qr = null;
qr = new SQLiteQuery(db,queryCreate3);
qr.Step();
qr.Release();
qr = null;
qr = new SQLiteQuery(db, queryInsert);
qr.Bind(testString);
qr.Bind(testBlob);
qr.Step();
qr.Step();
qr.Step();
qr.Release();
qr = null;
String testString2;
qr = new SQLiteQuery(db, querySelect);
while(qr.Step())
{
testString2 = qr.GetString("str_field");
testString2.ToString();
}
qr.Release();
qr = null;
db.Close();
db = null;
UnityEngine.Debug.Log("Done");
}catch(Exception e)
{
UnityEngine.Debug.LogError(e.Message);
if(qr!=null)
qr.Release();
qr = null;
if(db!=null)
db.Close();
db=null;
}
}
}
protected void CloseDatabase(SQLiteDB sqliteDB,SQLiteQuery sqliteQuery){
sqliteQuery.Release();
sqliteDB.Close();
}
