/// <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()); }