private void UpdateDataBase() { mySql.GetConnectionWith(CommonData.dataBaseName); mySql.BeginTransaction(); for (int i = 0; i < singleLearnWordsCount; i++) { LearnWord word = wordsToLearnArray [i]; string condition = string.Format("wordId={0}", word.wordId); string newLearnedTime = (word.learnedTimes).ToString(); string newUngraspTime = (word.ungraspTimes).ToString(); // 更新数据库中当前背诵单词的背诵次数和背错次数 mySql.UpdateValues(currentWordsTableName, new string[] { "learnedTimes", "ungraspTimes" }, new string[] { newLearnedTime, newUngraspTime }, new string[] { condition }, true); } mySql.EndTransaction(); mySql.CloseConnection(CommonData.dataBaseName); }
/// <summary> /// 更新单词数据库 /// </summary> public void UpdateWordDataBase() { // 连接到数据库 MySQLiteHelper sql = MySQLiteHelper.Instance; sql.GetConnectionWith(CommonData.dataBaseName); // 开启事务 sql.BeginTransaction(); // 获取当前单词的表单 string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName(); // 更新项 string[] colFields = { "learnedTimes", "ungraspTimes", "isFamiliar" }; HLHWord word = null; // 正确单词列表中所有单词数据更新进数据库 for (int i = 0; i < correctWordList.Count; i++) { word = correctWordList[i]; string familiarStr = word.isFamiliar ? "1" : "0"; string[] conditions = { "wordId=" + word.wordId }; string[] values = { word.learnedTimes.ToString(), word.ungraspTimes.ToString(), familiarStr }; sql.UpdateValues(currentWordsTableName, colFields, values, conditions, true); } // 错误单词列表中所有单词数据更新进数据库 for (int i = 0; i < wrongWordList.Count; i++) { word = wrongWordList[i]; string familiarStr = word.isFamiliar ? "1" : "0"; string[] conditions = { "wordId=" + word.wordId }; string[] values = { word.learnedTimes.ToString(), word.ungraspTimes.ToString(), familiarStr }; sql.UpdateValues(currentWordsTableName, colFields, values, conditions, true); } // 清理工作 correctWordList.Clear(); wrongWordList.Clear(); sql.EndTransaction(); sql.CloseConnection(CommonData.dataBaseName); }
private void WordsDataToDataBase(string wordsFilePath, string wordsTableName, MySQLiteHelper sql) { bool isSimpleWordsTableExist = sql.CheckTableExist(wordsTableName); if (isSimpleWordsTableExist) { sql.DeleteTable(wordsTableName);; } // isFamiliar 是否熟悉,熟悉为1,不熟悉为0 sql.CreateTable(wordsTableName, new string[] { "wordId", "spell", "phoneticSymbol", "explaination", "sentenceEN", "sentenceCH", "pronouncationURL", "wordLength", "learnedTimes", "ungraspTimes", "isFamiliar", "backupPronounciationURL" }, new string[] { "PRIMARY KEY NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL" }, new string[] { "INTEGER", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "TEXT", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0", "TEXT" }); string simpleWordsData = DataHandler.LoadDataString(wordsFilePath); string[] simpleWordsDataArray = simpleWordsData.Split(new char[] { '\n' }, System.StringSplitOptions.RemoveEmptyEntries); sql.BeginTransaction(); for (int i = 1; i < simpleWordsDataArray.Length; i++) { string singleWordData = simpleWordsDataArray [i]; string[] datas = singleWordData.Split(new char[] { ',' }, System.StringSplitOptions.RemoveEmptyEntries); string wordId = (i - 1).ToString(); string spell = "'" + datas [0].Replace("'", "''") + "'"; string explaination = "'" + ExplainationNormalization(datas [1]) + "'"; string phoneticSymbol = "'" + datas [2].Replace("'", "''") + "'"; string sentenceEN = "'" + datas [3].Replace("'", "''").Replace('+', ',') + "'"; string sentenceCH = "'" + datas [4].Replace("'", "''").Replace('+', ',') + "'"; string pronounciationURL = "'" + datas [5] + "'"; string backupPronounciationURL = string.Format("'https://ssl.gstatic.com/dictionary/static/sounds/oxford/{0}--_gb_1.mp3'", datas[0]); string wordLength = (spell.Length - 2).ToString(); string[] wordInput = new string[] { wordId, spell, phoneticSymbol, explaination, sentenceEN, sentenceCH, pronounciationURL, wordLength, "0", "0", "0", backupPronounciationURL }; sql.InsertValues(wordsTableName, wordInput); } sql.EndTransaction(); }
/// <summary> /// 持久化数据 /// </summary> public void PersistData() { DirectoryInfo persistDi = new DirectoryInfo(CommonData.persistDataPath); IEnumerator initDataCoroutine = null; CheckDataModel checkData = new CheckDataModel(); MySQLiteHelper sql = MySQLiteHelper.Instance; // 检查数据的完整性 bool dataComplete = CheckDataComplete(); // 如果原始玩家数据存在的话 if (File.Exists(CommonData.playerDataFilePath)) { // 记录原始玩家数据 checkData.playerData = DataHandler.LoadDataToSingleModelWithPath <PlayerData>(CommonData.playerDataFilePath); checkData.playerData.needChooseDifficulty = false; if (ApplicationInfo.Instance != null) { checkData.versionUpdate = ApplicationInfo.Instance.currentVersion + 0.001f < GameManager.Instance.currentVersion; } else { checkData.versionUpdate = true; } } if (checkData.versionUpdate) { if (File.Exists(CommonData.dataBaseFilePath)) { sql.GetConnectionWith(CommonData.dataBaseName); sql.BeginTransaction(); checkData.learnedWordsInSimple = GetWordRecordsInDataBase(sql, 0); checkData.learnedWordsInMedium = GetWordRecordsInDataBase(sql, 1); checkData.learnedWordsInMaster = GetWordRecordsInDataBase(sql, 2); sql.EndTransaction(); sql.CloseConnection(CommonData.dataBaseName); } checkData.playerData.isNewPlayer = true; if (File.Exists(CommonData.buyRecordFilePath)) { checkData.buyRecord = BuyRecord.Instance; } if (File.Exists(CommonData.chatRecordsFilePath)) { checkData.chatRecords = GameManager.Instance.gameDataCenter.chatRecords; } if (File.Exists(CommonData.mapEventsRecordFilePath)) { checkData.mapEventsRecords = GameManager.Instance.gameDataCenter.mapEventsRecords; } if (File.Exists(CommonData.gameSettingsDataFilePath)) { checkData.gameSettings = GameManager.Instance.gameDataCenter.gameSettings; } if (File.Exists(CommonData.miniMapRecordFilePath)) { checkData.miniMapRecord = GameManager.Instance.gameDataCenter.currentMapMiniMapRecord; } if (File.Exists(CommonData.currentMapEventsRecordFilePath)) { checkData.currentMapEventsRecord = GameManager.Instance.gameDataCenter.currentMapEventsRecord; } if (File.Exists(CommonData.playRecordsFilePath)) { checkData.playRecords = GameManager.Instance.gameDataCenter.allPlayRecords; } } #if UNITY_IOS if (!persistDi.Exists || checkData.versionUpdate || !dataComplete) { bool dataValidate = CheckDataValidate(); if (!dataValidate) { return; } GameManager.Instance.persistDataManager.BackUpDataWhenUpdataVersion(checkData); DataHandler.CopyDirectory(CommonData.originDataPath, CommonData.persistDataPath, true); if (!persistDi.Exists) { OnNewInstall(); } else if (checkData.versionUpdate || !dataComplete) { GameManager.Instance.persistDataManager.versionUpdateWhenLoad = checkData.versionUpdate; OnVersionUpdate(checkData, sql); } initDataCoroutine = InitData(); StartCoroutine(initDataCoroutine); return; } else if (alwaysPersistData) { bool dataValidate = CheckDataValidate(); if (!dataValidate) { return; } GameManager.Instance.persistDataManager.BackUpDataWhenUpdataVersion(checkData); DataHandler.CopyDirectory(CommonData.originDataPath, CommonData.persistDataPath, true); if (!persistDi.Exists) { OnNewInstall(); } else if (checkData.versionUpdate || !dataComplete) { GameManager.Instance.persistDataManager.versionUpdateWhenLoad = checkData.versionUpdate; OnVersionUpdate(checkData, sql); } } initDataCoroutine = InitData(); StartCoroutine(initDataCoroutine); #elif UNITY_ANDROID if (!persistDi.Exists || checkData.versionUpdate || !dataComplete) { bool dataValidate = CheckDataValidate(); if (!dataValidate) { return; } GameManager.Instance.persistDataManager.BackUpDataWhenUpdataVersion(checkData); IEnumerator copyDataCoroutine = CopyDataForPersist(delegate { if (!persistDi.Exists) { OnNewInstall(); } else if (checkData.versionUpdate || !dataComplete) { GameManager.Instance.persistDataManager.versionUpdateWhenLoad = checkData.versionUpdate; OnVersionUpdate(checkData, sql); } }); StartCoroutine(copyDataCoroutine); return; } else if (alwaysPersistData) { bool dataValidate = CheckDataValidate(); if (!dataValidate) { return; } if (DataHandler.DirectoryExist(CommonData.persistDataPath + "/Data")) { DataHandler.DeleteDirectory(CommonData.persistDataPath + "/Data"); } GameManager.Instance.persistDataManager.BackUpDataWhenUpdataVersion(checkData); IEnumerator copyDataCoroutine = CopyDataForPersist(delegate { if (!persistDi.Exists) { OnNewInstall(); } else if (checkData.versionUpdate || !dataComplete) { GameManager.Instance.persistDataManager.versionUpdateWhenLoad = checkData.versionUpdate; OnVersionUpdate(checkData, sql); } }); StartCoroutine(copyDataCoroutine); } else { initDataCoroutine = InitData(); StartCoroutine(initDataCoroutine); } #endif }
/// <summary> /// 版本更新时数据处理 /// </summary> /// <param name="checkData">Check data.</param> /// <param name="sql">Sql.</param> private void OnVersionUpdate(CheckDataModel checkData, MySQLiteHelper sql) { //更新原始数据 if (checkData.chatRecords != null && checkData.chatRecords.Count > 0) { DataHandler.SaveInstanceListToFile <HLHNPCChatRecord>(checkData.chatRecords, CommonData.chatRecordsFilePath); } if (checkData.mapEventsRecords != null && checkData.mapEventsRecords.Count > 0) { DataHandler.SaveInstanceListToFile <MapEventsRecord>(checkData.mapEventsRecords, CommonData.mapEventsRecordFilePath); } if (checkData.gameSettings != null) { DataHandler.SaveInstanceDataToFile <GameSettings>(checkData.gameSettings, CommonData.gameSettingsDataFilePath); } if (checkData.miniMapRecord != null) { DataHandler.SaveInstanceDataToFile <MiniMapRecord>(checkData.miniMapRecord, CommonData.miniMapRecordFilePath); } if (checkData.currentMapEventsRecord != null) { DataHandler.SaveInstanceDataToFile <CurrentMapEventsRecord>(checkData.currentMapEventsRecord, CommonData.currentMapEventsRecordFilePath); } if (checkData.playRecords != null) { DataHandler.SaveInstanceListToFile <PlayRecord>(checkData.playRecords, CommonData.playRecordsFilePath); } if (checkData.buyRecord != null) { DataHandler.SaveInstanceDataToFile <BuyRecord>(checkData.buyRecord, CommonData.buyRecordFilePath, true); } sql.GetConnectionWith(CommonData.dataBaseName); sql.BeginTransaction(); if (checkData.learnedWordsInSimple.Count > 0) { Debug.Log(checkData.learnedWordsInSimple.Count); UpdateWordsDataBase(sql, 0, checkData.learnedWordsInSimple); } if (checkData.learnedWordsInMedium.Count > 0) { UpdateWordsDataBase(sql, 1, checkData.learnedWordsInMedium); } if (checkData.learnedWordsInMaster.Count > 0) { UpdateWordsDataBase(sql, 2, checkData.learnedWordsInMaster); } sql.EndTransaction(); sql.CloseConnection(CommonData.dataBaseName); WordType wordType = WordType.Simple; if (checkData.gameSettings != null) { wordType = checkData.gameSettings.wordType; } // 更新版本信息 ApplicationInfo.Instance.currentVersion = GameManager.Instance.currentVersion; DataHandler.SaveInstanceDataToFile <ApplicationInfo>(ApplicationInfo.Instance, CommonData.applicationInfoFilePath); if (checkData.playerData == null) { GameManager.Instance.persistDataManager.SaveCompletePlayerData(); return; } if (checkData.playerData.currentExploreStartDateString == null || checkData.playerData.currentExploreStartDateString == string.Empty) { string dateString = DateTime.Now.ToShortDateString(); Player.mainPlayer.currentExploreStartDateString = dateString; checkData.playerData.currentExploreStartDateString = dateString; } // 更新单词数据 if (checkData.playerData.maxWordContinuousRightRecord > 0) { switch (wordType) { case WordType.Simple: Player.mainPlayer.maxSimpleWordContinuousRightRecord = checkData.playerData.maxWordContinuousRightRecord; checkData.playerData.maxSimpleWordContinuousRightRecord = checkData.playerData.maxWordContinuousRightRecord; break; case WordType.Medium: Player.mainPlayer.maxMediumWordContinuousRightRecord = checkData.playerData.maxWordContinuousRightRecord; checkData.playerData.maxMediumWordContinuousRightRecord = checkData.playerData.maxWordContinuousRightRecord; break; case WordType.Master: Player.mainPlayer.maxMasterWordContinuousRightRecord = checkData.playerData.maxWordContinuousRightRecord; checkData.playerData.maxMasterWordContinuousRightRecord = checkData.playerData.maxWordContinuousRightRecord; break; } checkData.playerData.maxWordContinuousRightRecord = 0; } if (checkData.playerData.wordContinuousRightRecord > 0) { switch (wordType) { case WordType.Simple: Player.mainPlayer.simpleWordContinuousRightRecord = checkData.playerData.wordContinuousRightRecord; checkData.playerData.simpleWordContinuousRightRecord = checkData.playerData.wordContinuousRightRecord; break; case WordType.Medium: Player.mainPlayer.mediumWordContinuousRightRecord = checkData.playerData.wordContinuousRightRecord; checkData.playerData.mediumWordContinuousRightRecord = checkData.playerData.wordContinuousRightRecord; break; case WordType.Master: Player.mainPlayer.masterWordContinuousRightRecord = checkData.playerData.wordContinuousRightRecord; checkData.playerData.masterWordContinuousRightRecord = checkData.playerData.wordContinuousRightRecord; break; } checkData.playerData.wordContinuousRightRecord = 0; } if (checkData.playerData.titleQualifications != null) { bool hasOldVersionTitle = false; for (int i = 0; i < checkData.playerData.titleQualifications.Length; i++) { if (checkData.playerData.titleQualifications[i]) { hasOldVersionTitle = true; break; } } if (checkData.playerData.titleQualificationsOfSimple == null || checkData.playerData.titleQualificationsOfSimple.Length == 0) { checkData.playerData.titleQualificationsOfSimple = new bool[] { false, false, false, false, false, false }; } if (checkData.playerData.titleQualificationsOfMedium == null || checkData.playerData.titleQualificationsOfMedium.Length == 0) { checkData.playerData.titleQualificationsOfMedium = new bool[] { false, false, false, false, false, false }; } if (checkData.playerData.titleQualificationsOfMaster == null || checkData.playerData.titleQualificationsOfMaster.Length == 0) { checkData.playerData.titleQualificationsOfMaster = new bool[] { false, false, false, false, false, false }; } Player.mainPlayer.titleQualificationsOfSimple = checkData.playerData.titleQualificationsOfSimple; Player.mainPlayer.titleQualificationsOfMedium = checkData.playerData.titleQualificationsOfMedium; Player.mainPlayer.titleQualificationsOfMaster = checkData.playerData.titleQualificationsOfMaster; if (hasOldVersionTitle) { switch (wordType) { case WordType.Simple: Player.mainPlayer.titleQualificationsOfSimple = checkData.playerData.titleQualifications; checkData.playerData.titleQualificationsOfSimple = checkData.playerData.titleQualifications; break; case WordType.Medium: Player.mainPlayer.titleQualificationsOfMedium = checkData.playerData.titleQualifications; checkData.playerData.titleQualificationsOfMedium = checkData.playerData.titleQualifications; break; case WordType.Master: Player.mainPlayer.titleQualificationsOfMaster = checkData.playerData.titleQualifications; checkData.playerData.titleQualificationsOfMaster = checkData.playerData.titleQualifications; break; } checkData.playerData.titleQualifications = new bool[] { false, false, false, false, false, false }; } } DataHandler.SaveInstanceDataToFile <PlayerData>(checkData.playerData, CommonData.playerDataFilePath, true); }
public static void BuildItemsDataBase() { MySQLiteHelper sql = MySQLiteHelper.Instance; sql.GetConnectionWith(CommonData.dataBaseName, "/Users/houlianghong/Desktop/Unityfolder/TestOnSkills/Assets/StreamingAssets/Data"); // sql.CreatTable (CommonData.itemsTable, // new string[] {"itemId","itemName","itemGeneralDescription","spriteName","itemType","itemNameInEnglish", // "attackGain","powerGain","magicGain","critGain","armorGain","manaResistGain", // "dodgeGain","healthGain","strengthGain"}, // new string[] {"PRIMARY Key","UNIQUE NOT NULL","NOT NULL","NOT NULL","","UNIQUE","","","","","","","","",""}, // new string[] {"INTEGER","TEXT","TEXT","TEXT","INTEGER","TEXT","INTEGER","INTEGER","INTEGER", // "INTEGER","INTEGER","INTEGER","INTEGER","INTEGER","INTEGER" }); // // int[] stringTypeCols = new int[]{ 1, 2, 3, 5 }; // // itemsProperties.Clear (); // // LoadItemsData ("itemsData.csv"); // // sql.CheckFiledNames (CommonData.itemsTable,fieldNames); // // sql.DeleteAllDataFromTable (CommonData.itemsTable); // // for(int i = 0;i<itemsProperties.Count;i++){ // string[] values = itemsProperties [i]; // // foreach (int j in stringTypeCols) { // values [j] = "'" + values[j] + "'"; // // } // // sql.InsertValues (CommonData.itemsTable, values); // } // if (sql.CheckTableExist(CommonData.CET4Table)) { sql.DeleteTable(CommonData.CET4Table); } sql.CreateTable(CommonData.CET4Table, new string[] { "wordId", "spell", "phoneticSymbol", "explaination", "example", "learnedTimes", "ungraspTimes" }, new string[] { "PRIMARY KEY NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL" }, new string[] { "INTEGER", "TEXT", "TEXT", "TEXT", "TEXT", "INTEGER DEFAULT 0", "INTEGER DEFAULT 0" }); // sql.CreateTable (CommonData.CET4Table, // new string[]{ "wordId", "spell", "phoneticSymbol", "explaination", "example","learnedTimes","ungraspTimes" }, // new string[]{ "PRIMARY KEY NOT NULL", "UNIQUE NOT NULL", "", "NOT NULL", "","",""}, // new string[]{ "INTEGER", "TEXT", "TEXT", "TEXT", "TEXT","INTEGER DEFAULT 0","INTEGER DEFAULT 0" }); // 为单词表创建索引,以id为索引 sql.CreateIndex("wordId_index", CommonData.CET4Table, new string[] { "wordId" }, true); // 为单词表创建索引,以学习次数为索引 sql.CreateIndex("learnedTimes_index", CommonData.CET4Table, new string[] { "learnedTimes" }, false); // 为单词表创建索引,以点击 不熟悉&选错 的次数 为索引 sql.CreateIndex("ungraspTimes_index", CommonData.CET4Table, new string[] { "ungraspTimes" }, false); int[] stringTypeCols = new int[] { 1, 2, 3, 4 }; itemsProperties.Clear(); LoadCET4WordsData(); // sql.CheckFiledNames (CommonData.CET4Table, fieldNames); sql.BeginTransaction(); for (int i = 0; i < itemsProperties.Count; i++) { string[] values = itemsProperties [i]; foreach (int j in stringTypeCols) { // string sqliteStr = SqliteEscape (values [j]); string sqliteStr = values [j]; values [j] = "'" + sqliteStr + "'"; } sql.InsertValues(CommonData.CET4Table, values); } sql.EndTransaction(); sql.CloseConnection(CommonData.dataBaseName); }
/// <summary> /// 初始化本次要学习的单词数组 /// </summary> private void InitWordsToLearn() { ungraspedWordsList.Clear(); mySql = MySQLiteHelper.Instance; mySql.GetConnectionWith(CommonData.dataBaseName); // int totalLearnTimeCount = GameManager.Instance.gameDataCenter.learnInfo.totalLearnTimeCount; // // int totalWordsCount = mySql.GetItemCountOfTable (CommonData.CET4Table,null,true); // // // 大循环的次数 // int bigCycleCount = totalLearnTimeCount * singleLearnWordsCount / (totalWordsCount * recycleLearnTimeBase); // // currentWordsLearnedTime = totalLearnTimeCount % (recycleLearnTimeBase * recycleGroupBase) / recycleGroupBase + recycleLearnTimeBase * bigCycleCount; mySql.BeginTransaction(); // 边界条件 string[] condition = new string[] { "learnedTimes=0" }; IDataReader reader = mySql.ReadSpecificRowsOfTable(currentWordsTableName, null, condition, true); // 从数据库中读取当前要学习的单词 for (int i = 0; i < singleLearnWordsCount; i++) { reader.Read(); if (reader == null) { string[] colFields = new string[] { "learnedTimes" }; string[] values = new string[] { "0" }; string[] conditions = new string[] { "learnedTimes=1" }; mySql.UpdateValues(currentWordsTableName, colFields, values, conditions, true); mySql.EndTransaction(); mySql.CloseAllConnections(); InitWordsToLearn(); return; } int wordId = reader.GetInt32(0); string spell = reader.GetString(1); string phoneticSymble = reader.GetString(2); string explaination = reader.GetString(3); string example = reader.GetString(4); int learnedTimes = reader.GetInt16(5); int ungraspTimes = reader.GetInt16(6); LearnWord word = new LearnWord(wordId, spell, phoneticSymble, explaination, example, learnedTimes, ungraspTimes); Debug.LogFormat("{0}---{1}次", word, learnedTimes); wordsToLearnArray [i] = word; } mySql.EndTransaction(); mySql.CloseAllConnections(); // 当前要学习的单词全部加入到未掌握单词列表中,用户选择掌握或者学习过该单词后从未掌握单词列表中移除 for (int i = 0; i < wordsToLearnArray.Length; i++) { ungraspedWordsList.Add(wordsToLearnArray [i]); } // firstIdOfCurrentLearningWords = wordsToLearnArray [0].wordId; }