/// <summary>
/// 关闭数据库
/// </summary>
public void CloseDatabase()
{
if (null != database)
{
database.Close();
database = null;
}
}
private static void closeSqliteDatabase()
{
if (database != null)
{
database.Close();
database = null;
}
}
// Use this for initialization
void Start()
{
#if UNITY_ANDROID
MyLibs.InitializeSQLCipher();
#endif
database = new SqliteDatabase(dbFilePath, dbDirectoryPath);
database.Open();
if (!string.IsNullOrEmpty(dbPassword))
{
database.Key(dbPassword);
}
Write("Open Database at " + database.pathDB);
database.ExecuteNonQuery("delete from UserData;");
Write("Clear Table UserData");
object[] paramVal = new object[] { 1, "Joko", "Jakarta", 28 };
string query = "insert into UserData values ({0}, {1}, {2}, {3});";
Write("Execute: " + query, paramVal);
database.ExecuteNonQuery(query, paramVal);
QueryTable(1);
paramVal = new object[] { "Joko Update", 1 };
query = "update UserData set name = {0} where Id = {1}";
Write("Execute: " + query, paramVal);
database.ExecuteNonQuery(query, paramVal);
QueryTable(1);
database.Close();
#if UNITY_ANDROID
MyLibs.ToastMessage("SQLCipher Done");
#endif
}
/// <summary>
/// Opens the MBTiles database file located at Filepath.
/// </summary>
private void Open ()
{
if (db != null)
db.Close ();
db = new SqliteDatabase ();
db.Open (filepath);
DataTable dt = db.ExecuteQuery ("SELECT * FROM metadata");
#if DEBUG_LOG
string dbg = String.Empty;
foreach (DataRow dbgRow in dt.Rows)
{
foreach (string col in dt.Columns)
{
dbg += "\t" + dbgRow[col];
}
dbg += "\n";
}
Debug.Log("DEBUG: MBTilesLayer.Update: metadata:\n" + dbg);
#endif
metadataRowNameLookedFor = "version";
DataRow row = dt.Rows.Find (metadataMatchPredicate);
if (row == null) {
db.Close ();
throw new SqliteException ("missing 'version' in metadata");
}
version = row ["value"] as string;
switch (version) {
case "1.0.0":
metadataRowNameLookedFor = "bounds";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
string[] tokens = (row ["value"] as string).Split (new Char[] { ',' });
bounds = new Rect (Single.Parse (tokens [0]), Single.Parse (tokens [1]), Single.Parse (tokens [2]), Single.Parse (tokens [3]));
}
metadataRowNameLookedFor = "center";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
string[] tokens = (row ["value"] as string).Split (new Char[] { ',' });
center = new Vector3 (Single.Parse (tokens [0]), Single.Parse (tokens [1]), Single.Parse (tokens [2]));
}
metadataRowNameLookedFor = "minzoom";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
minZoom = Single.Parse (row ["value"] as string);
}
metadataRowNameLookedFor = "maxzoom";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
maxZoom = Single.Parse (row ["value"] as string);
}
metadataRowNameLookedFor = "name";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
_name = row ["value"] as string;
}
metadataRowNameLookedFor = "description";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
description = row ["value"] as string;
}
metadataRowNameLookedFor = "attribution";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
attribution = row ["value"] as string;
}
metadataRowNameLookedFor = "template";
row = dt.Rows.Find (metadataMatchPredicate);
if (row != null) {
template = row ["value"] as string;
}
break;
default:
throw new SqliteException ("unsupported SQLite version: " + version);
}
isReadyToBeQueried = true;
}
/// <summary>
/// Opens the MBTiles database file located at Filepath.
/// </summary>
private void Open()
{
if (db != null)
{
db.Close();
}
db = new SqliteDatabase();
db.Open(filepath);
DataTable dt = db.ExecuteQuery("SELECT * FROM metadata");
#if DEBUG_LOG
string dbg = String.Empty;
foreach (DataRow dbgRow in dt.Rows)
{
foreach (string col in dt.Columns)
{
dbg += "\t" + dbgRow[col];
}
dbg += "\n";
}
Debug.Log("DEBUG: MBTilesLayer.Update: metadata:\n" + dbg);
#endif
metadataRowNameLookedFor = "version";
DataRow row = dt.Rows.Find(metadataMatchPredicate);
if (row == null)
{
db.Close();
throw new SqliteException("missing 'version' in metadata");
}
version = row["value"] as string;
switch (version)
{
case "1.0.0":
metadataRowNameLookedFor = "bounds";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
string[] tokens = (row["value"] as string).Split(new Char[] { ',' });
bounds = new Rect(Single.Parse(tokens[0]), Single.Parse(tokens[1]), Single.Parse(tokens[2]), Single.Parse(tokens[3]));
}
metadataRowNameLookedFor = "center";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
string[] tokens = (row["value"] as string).Split(new Char[] { ',' });
center = new Vector3(Single.Parse(tokens[0]), Single.Parse(tokens[1]), Single.Parse(tokens[2]));
}
metadataRowNameLookedFor = "minzoom";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
minZoom = Single.Parse(row["value"] as string);
}
metadataRowNameLookedFor = "maxzoom";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
maxZoom = Single.Parse(row["value"] as string);
}
metadataRowNameLookedFor = "name";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
_name = row["value"] as string;
}
metadataRowNameLookedFor = "description";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
description = row["value"] as string;
}
metadataRowNameLookedFor = "attribution";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
attribution = row["value"] as string;
}
metadataRowNameLookedFor = "template";
row = dt.Rows.Find(metadataMatchPredicate);
if (row != null)
{
template = row["value"] as string;
}
break;
default:
throw new SqliteException("unsupported SQLite version: " + version);
}
isReadyToBeQueried = true;
}
public void InsertTextureInfo(string tableName)
{
sql.Open(dbPathName);
query = "delete from " + tableName;
print(query);
sql.ExecuteQuery(query);
if (tableName == "TextureInfo_f")
{
query = "insert into TextureInfo_f \n";
query += " select \n";
query += " 1 as volNum \n";
query += ",b.stageNum as stageNum \n";
query += ",b.turnTexNum as turnTexNum \n";
query += ",b.largeCode as largeCode \n";
query += ",b.middleCode as middleCode \n";
query += ",b.texName as texName \n";
query += ",b.texName as texShowName \n";
query += ",b.meaningKind as texShowKind \n";
query += ",a.photoNum as texNum \n";
query += ",a.photoImgURL as texUrl \n";
//query += ",a.photoTbURL as texUrl \n";
query += ",'Y' as texUrlYn \n";
query += ",'N' as downYn \n";
query += ",a.photoHref as photoLink \n";
query += ",c.photoOwner as photoOwner \n";
query += ",c.photoLicense as photoLicense \n";
query += ",'flickr' as photoSource \n";
query += ",strftime('%Y%m%d','now','localtime') as createDate \n";
query += ",strftime('%H%M%S','now','localtime') as createTime \n";
query += ",'' as updateDate \n";
query += ",'' as updateTime \n";
query += "from PhotoSearch a, TextureMeaning b, PhotoGetInfo c \n";
//query += " ,(select largeCode,middleCode,texName,count(*) cnt \n";
//query += " from PhotoSearch \n";
//query += " where useYn = 'Y' \n";
//query += " group by largeCode,middleCode,texName \n";
//query += " having count(*) = 4) d \n";
query += "where a.largeCode = b.largeCode \n";
query += "and a.middleCode = b.middleCode \n";
query += "and replace(replace(a.texName,'_',''),' ','') = replace(replace(b.texName,'_',''),' ','') \n";
query += "and a.photoId = c.photoId \n";
//query += "and a.largeCode = d.largeCode \n";
//query += "and a.middleCode = d.middleCode \n";
//query += "and a.texName = d.texName \n";
query += "and a.useYn = 'Y' \n";
query += "and b.language = 'Korean' \n";
query += "and b.languageClass = 1 \n";
query += "and b.largeCode > 0 \n";
query += "order by b.stageNum, b.turnTexNum, a.texName \n";
}
else
{
query = "insert into TextureInfo_m \n";
query += " select \n";
query += " 1 as volNum \n";
query += ",b.stageNum as stageNum \n";
query += ",b.turnTexNum as turnTexNum \n";
query += ",b.largeCode as largeCode \n";
query += ",b.middleCode as middleCode \n";
query += ",b.texName as texName \n";
query += ",b.texName as texShowName \n";
query += ",b.meaningKind as texShowKind \n";
query += ",a.texNum as texNum \n";
query += ",a.texUrl as texUrl \n";
query += ",'Y' as texUrlYn \n";
query += ",'N' as downYn \n";
query += ",a.photoLink as photoLink \n";
query += ",a.photoOwner as photoOwner \n";
query += ",a.photoLicense as photoLicense \n";
query += ",'manual' as photoSource \n";
query += ",strftime('%Y%m%d','now','localtime') as createDate \n";
query += ",strftime('%H%M%S','now','localtime') as createTime \n";
query += ",'' as updateDate \n";
query += ",'' as updateTime \n";
query += "from PhotoManual a, TextureMeaning b \n";
query += "where a.largeCode = b.largeCode \n";
query += "and a.middleCode = b.middleCode \n";
query += "and replace(replace(a.texName,'_',''),' ','') = replace(replace(b.texName,'_',''),' ','') \n";
query += "and b.language = 'Korean' \n";
query += "and b.languageClass = 1 \n";
query += "and b.largeCode > 0 \n";
query += "order by b.stageNum, b.turnTexNum, a.texName \n";
}
print(query);
sql.ExecuteQuery(query);
sql.ExecuteQuery("drop table if exists TextureInfoTexNum01");
//---------------------------
// update texNum
//---------------------------
query = " create table TextureInfoTexNum01 as \n";
query += " select * \n";
query += " from \n";
query += " (select stageNum,turnTexNum,texName,count(*) cnt \n";
query += " from " + tableName + " \n";
query += " group by stageNum,turnTexNum,texName \n";
query += " order by stageNum,turnTexNum,texName \n";
query += " ) a, numtab b \n";
query += " where b.num <= a.cnt \n";
print(query);
sql.ExecuteQuery(query);
sql.ExecuteQuery("drop table if exists TextureInfo2");
query = " create table TextureInfo2 as \n";
query += " select rowid rnum,* \n";
query += " from " + tableName + " \n";
query += " order by stageNum,turnTexNum,texName \n";
print(query);
sql.ExecuteQuery(query);
sql.ExecuteQuery("delete from " + tableName);
query = "insert into " + tableName + " \n";
query += "select \n";
query += " 1 as volNum \n";
query += ",a.stageNum as stageNum \n";
query += ",a.turnTexNum as turnTexNum \n";
query += ",a.largeCode as largeCode \n";
query += ",a.middleCode as middleCode \n";
query += ",a.texName as texName \n";
query += ",a.texName as texShowName \n";
query += ",a.texShowKind as texShowKind \n";
query += ",b.num as texNum \n";
query += ",a.texUrl as texUrl \n";
query += ",a.texUrlYn as texUrlYn \n";
query += ",a.downYn as downYn \n";
query += ",a.photoLink as photoLink \n";
query += ",a.photoOwner as photoOwner \n";
query += ",a.photoLicense as photoLicense\n";
query += ",a.photoSource as photoSource \n";
query += ",strftime('%Y%m%d','now','localtime') as createDate \n";
query += ",strftime('%H%M%S','now','localtime') as createTime \n";
query += ",'' as updateDate \n";
query += ",'' as updateTime \n";
query += " from TextureInfo2 a, \n";
query += " (select rowid rnum,* \n";
query += " from TextureInfoTexNum01 \n";
query += " order by stageNum,turnTexNum,texName) b \n";
query += " where a.rnum = b.rnum \n";
print(query);
sql.ExecuteQuery(query);
//if(tableName == "TextureInfo_f") {
// //---------------------------
// // delete TextureInfo_f
// //---------------------------
// sql.ExecuteQuery("drop table if exists TextureInfo_fd");
// query = "create table TextureInfo_fd as \n";
// query += "select largeCode,middleCode,texName \n";
// query += "from TextureInfo_f \n";
// query += "group by largeCode,middleCode,texName \n";
// query += "having count(*) < 4 \n";
// print(query);
// sql.ExecuteQuery(query);
// query = "delete from TextureInfo_f \n";
// query += " where exists ( \n";
// query += " select largeCode,middleCode,texName \n";
// query += " from TextureInfo_fd \n";
// query += " where largeCode = TextureInfo_f.largeCode \n";
// query += " and middleCode = TextureInfo_f.middleCode \n";
// query += " and texName = TextureInfo_f.texName \n";
// query += " group by largeCode,middleCode,texName \n";
// query += " having count(*) < 4 \n";
// query += " ) \n";
// print(query);
// sql.ExecuteQuery(query);
//}
//---------------------------
// delete TextureInfo
//---------------------------
query = "delete from TextureInfo \n";
query += " where exists ( \n";
query += " select largeCode,middleCode,texName,texNum \n";
query += " from " + tableName + " \n";
query += " where largeCode = TextureInfo.largeCode \n";
query += " and middleCode = TextureInfo.middleCode \n";
query += "and replace(replace(texName,'_',''),' ','') = replace(replace(TextureInfo.texName,'_',''),' ','') \n";
query += "and texNum = TextureInfo.texNum \n";
query += " group by largeCode,middleCode,texName \n";
query += " ) \n";
print(query);
sql.ExecuteQuery(query);
//---------------------------
// insert TextureInfo
//---------------------------
query = "insert into TextureInfo select * from " + tableName;
print(query);
sql.ExecuteQuery(query);
//sql.ExecuteQuery("insert into TextureInfo select * from TextureInfoGana");
sql.Close();
}
IEnumerator addSkip()
{
yield return(new WaitForSeconds(1f));
//g.hpAddTime--;
//Debug.Log("hpAddTime1="+g.hpAddTime);
sql.Open(dbPathName);
query = "select strftime('%s',datetime('now','localtime')) - strftime('%s',startTime) as addSkipSecond from AddSkip";
//Debug.Log(query);
_data = sql.ExecuteQuery(query); dr = _data.Rows[0];
addSkipSecond = int.Parse(dr["addSkipSecond"].ToString());
addFreeSkip = addSkipSecond / (minute1 * g.freeSkipMinuteTime); // freeSkipMinuteTime:32
if (addFreeSkip + g.allSkip() > g.maxBonusSkipCnt)
{
addFreeSkip = g.maxBonusSkipCnt - g.allSkip();
}
//Debug.Log("addSkipSecond="+addSkipSecond);
//Debug.Log("addFreeSkip="+addFreeSkip);
//Debug.Log("g.freeSkip="+g.freeSkip);
//Debug.Log("g.allSkip()="+g.allSkip());
//Debug.Log("g.maxFreeSkipCnt="+g.maxFreeSkipCnt);
if (addFreeSkip > 0 && g.allSkip() < g.maxBonusSkipCnt) // maxFreeSkipCnt:3
//Debug.Log("g.addFreeSkip="+g.addFreeSkip);
{
g.freeSkip += addFreeSkip;
//Debug.Log("g.freeSkip="+g.freeSkip);
if (g.freeSkip > g.maxBonusSkipCnt)
{
g.freeSkip = g.maxBonusSkipCnt;
}
//g.hpAddTime = 5 * 60;
//SELECT strftime('%s','2014-08-08 23:29:12') - strftime('%s','2014-08-08 23:29:09');
query = "update UserInfo set ";
query += "freeSkip = " + g.freeSkip + ",";
query += "updateDate = strftime('%Y%m%d','now','localtime'),updateTime = strftime('%H%M%S','now','localtime')";
//Debug.Log(query);
sql.ExecuteQuery(query);
query = "update AddSkip set startTime = datetime('now','localtime')";
//Debug.Log(query);
sql.ExecuteQuery(query);
}
else
{
g.skipAddTime = (minute1 * g.freeSkipMinuteTime) - addSkipSecond;
if (g.skipAddTime < 0)
{
g.skipAddTime = 0;
}
//Debug.Log("g.skipAddTime="+g.skipAddTime);
}
sql.Close();
StartCoroutine(addSkip());
}
