Example #1
0
        /// <summary>
        /// 获取数据库中的单词数据[0:simple 1:middle 2:master]
        /// </summary>
        /// <returns>The word records in data base.</returns>
        /// <param name="wordType">Word type.</param>
        private List <HLHWord> GetWordRecordsInDataBase(MySQLiteHelper sql, int wordType)
        {
            List <HLHWord> wordsList = new List <HLHWord>();

            string query = string.Empty;

            switch (wordType)
            {
            case 0:
                query = string.Format("SELECT * FROM {0} WHERE learnedTimes>0", CommonData.simpleWordsTable);
                break;

            case 1:
                query = string.Format("SELECT * FROM {0} WHERE learnedTimes>0", CommonData.mediumWordsTabel);;
                break;

            case 2:
                query = string.Format("SELECT * FROM {0} WHERE learnedTimes>0", CommonData.masterWordsTabel);
                break;
            }


            IDataReader reader = sql.ExecuteQuery(query);

            while (reader.Read())
            {
                int wordId = reader.GetInt32(0);

                string spell = reader.GetString(1);

                string phoneticSymble = reader.GetString(2);

                string explaination = reader.GetString(3);

                string sentenceEN = reader.GetString(4);

                string sentenceCH = reader.GetString(5);

                string pronounciationURL = reader.GetString(6);

                int wordLength = reader.GetInt16(7);

                int learnedTimes = reader.GetInt16(8);

                int ungraspTimes = reader.GetInt16(9);

                bool isFamiliar = reader.GetInt16(10) == 1;

                HLHWord word = new HLHWord(wordId, spell, phoneticSymble, explaination, sentenceEN, sentenceCH, pronounciationURL,
                                           wordLength, learnedTimes, ungraspTimes, isFamiliar, "");

                wordsList.Add(word);
            }
            return(wordsList);
        }
Example #2
0
        /// <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);
        }
Example #3
0
        private HLHWord GetAValidWord()
        {
            MySQLiteHelper mySql = MySQLiteHelper.Instance;

            mySql.GetConnectionWith(CommonData.dataBaseName);

            string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();

            string[] condition = new string[] { "wordLength <= 7 ORDER BY RANDOM() LIMIT 1" };

            IDataReader reader = mySql.ReadSpecificRowsOfTable(currentWordsTableName, null, condition, true);

            reader.Read();

            return(HLHWord.GetWordFromReader(reader));
        }
        public static void InitSimpleLevelWords()
        {
            WordDataHelper wdh = new WordDataHelper();

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            sql.GetConnectionWith(CommonData.dataBaseName, CommonData.originDataPath);

            wdh.WordsDataToDataBase(filePathOfSimpleLevelWords, CommonData.simpleWordsTable, sql);

            wdh.WordsDataToDataBase(filePathOfMediumLevelWords, CommonData.mediumWordsTabel, sql);

            wdh.WordsDataToDataBase(filePathOfMasterLevelWords, CommonData.masterWordsTabel, sql);

            sql.CloseConnection(CommonData.dataBaseName);
        }
Example #5
0
        public List <LearnWord> GetAllUngraspedWords()
        {
            List <LearnWord> ungraspWords = new List <LearnWord>();

            string tableName = GetCurrentLearningWordsTabelName();

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            // 连接数据库
            sql.GetConnectionWith(CommonData.dataBaseName);

            string[] ungraspedCondition = new string[] { "ungraspTimes>0" };

            // 读取器
            IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, ungraspedCondition, true);

            // 从表中读取数据
            while (reader.Read())
            {
                if (reader == null)
                {
                    return(null);
                }

                int    wordId         = reader.GetInt32(0);
                string spell          = reader.GetString(1);
                string explaination   = reader.GetString(2);
                string phoneticSymble = reader.GetString(3);
                string example        = reader.GetString(4);
                int    learnedTimes   = reader.GetInt16(5);
                int    ungraspTimes   = reader.GetInt16(6);

                LearnWord w = new LearnWord(wordId, spell, explaination, phoneticSymble, example, learnedTimes, ungraspTimes);

                ungraspWords.Add(w);
            }

            sql.CloseAllConnections();

            return(ungraspWords);
        }
Example #6
0
        private HLHWord GetWordOfLength(int length)
        {
            string currentTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();

            string query = string.Format("SELECT * FROM {0} WHERE wordLength={1} ORDER BY RANDOM() LIMIT 1", currentTableName, length);

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            sql.GetConnectionWith(CommonData.dataBaseName);

            IDataReader reader = sql.ExecuteQuery(query);

            reader.Read();

            HLHWord word = HLHWord.GetWordFromReader(reader);

            GameManager.Instance.pronounceManager.DownloadPronounceCache(word);

            //sql.CloseConnection(CommonData.dataBaseName);

            return(word);
        }
Example #7
0
        private int GetTotalWordsCount()
        {
            string tableName = GetCurrentLearningWordsTabelName();

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            // 连接数据库
            sql.GetConnectionWith(CommonData.dataBaseName);

            // 检查存放指定单词类型的表是否存在(目前只做了测试用的CET4这一个表,添加表使用参考editor文件夹下的DataBaseManager)
            if (!sql.CheckTableExist(tableName))
            {
                Debug.Log("查询的表不存在");
                return(0);
            }

            // 查询当前学习的单词类型中的所有单词数量
            int count = sql.GetItemCountOfTable(tableName, null, true);

            sql.CloseAllConnections();

            return(count);
        }
Example #8
0
        /// <summary>
        /// 获取当前词库已学习单词总数
        /// </summary>
        /// <returns>The current learned words count.</returns>
        private int GetCurrentLearnedWordsCount()
        {
            string tableName = GetCurrentLearningWordsTabelName();

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            // 连接数据库
            sql.GetConnectionWith(CommonData.dataBaseName);

            if (!sql.CheckTableExist(tableName))
            {
                sql.CloseConnection(CommonData.dataBaseName);
                return(0);
            }

            // 查询当前学习的单词类型中的所有单词数量
            string[] learnedCondition = { "learnedTimes>0" };
            int      count            = sql.GetItemCountOfTable(tableName, learnedCondition, true);

            sql.CloseConnection(CommonData.dataBaseName);

            return(count);
        }
Example #9
0
        /// <summary>
        /// 获取当前词库错误过的单词总数
        /// </summary>
        /// <returns>The wrong words count.</returns>
        private int GetWrongWordsCount()
        {
            string tableName = GetCurrentLearningWordsTabelName();

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            // 连接数据库
            sql.GetConnectionWith(CommonData.dataBaseName);

            // 检查存放指定单词类型的表是否存在(目前只做了测试用的CET4这一个表,添加表使用参考editor文件夹下的DataBaseManager)
            if (!sql.CheckTableExist(tableName))
            {
                sql.CloseConnection(CommonData.dataBaseName);
                return(0);
            }

            // 查询当前学习的单词类型中所有背错过的单词数量
            string[] ungraspedCondition = { "ungraspTimes>=1" };
            int      count = sql.GetItemCountOfTable(tableName, ungraspedCondition, true);

            sql.CloseConnection(CommonData.dataBaseName);

            return(count);
        }
Example #10
0
        /// <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
        }
Example #11
0
        /// <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);
        }
Example #12
0
        // 从指定文件(txt/csv等文本文件)中读取数据 csv为从excel中导出的文本文件,导入unity之后需要选择结尾格式(mono里是这样的,在mono中打开csv文件后会有提示),否则在读取数据库时会报字段名不同的错误
//		private static void LoadWordsData(string dataPaths){
//
//			string itemsString = DataHandler.LoadDataString (dataPaths);
//
//			string[] stringsByLine = itemsString.Split (new string[]{ "\n" }, System.StringSplitOptions.RemoveEmptyEntries);
//
//			fieldNames = stringsByLine [0].Split (new char[]{ ',' });
//
//			for (int i = 1; i < stringsByLine.Length; i++) {
//				itemsProperties.Add(stringsByLine [i].Split (new char[]{ ',' }));
//			}
//
//		}



//		private void ToLower(){
//			MySQLiteHelper sql = MySQLiteHelper.Instance;
//			sql.GetConnectionWith (CommonData.dataBaseName);
//
//			string tableName = "AllWordsTable";
//
//			int wordsCount = sql.GetItemCountOfTable (tableName,null,true);
//
//			for (int i = 0; i < 37336; i++) {
//
//				IDataReader reader = sql.ReadSpecificRowsOfTable (
//					"AllWordsData",
//					"Spell",
//					new string[]{ string.Format ("Id={0}", i) },
//					true);
//				reader.Read ();
//
//				string spell = reader.GetString (0);
//
//				string lowerSpell = spell.ToLower ();
//
//				if (lowerSpell == spell) {
//					continue;
//				}
//
//				lowerSpell = lowerSpell.Replace("'","''");
//
//				sql.UpdateValues ("AllWordsData",
//					new string[]{ "Spell" },
//					new string[]{ string.Format("'{0}'",lowerSpell) },
//					new string[]{string.Format("Id = {0}",i)},
//					true);
//
//				reader.Close ();
//
//			}
//
//
//
//			sql.CloseConnection (CommonData.dataBaseName);
//		}

        private void MoveData()
        {
            MySQLiteHelper sql = MySQLiteHelper.Instance;

            sql.GetConnectionWith(CommonData.dataBaseName);

            sql.CreateTable("AllWordsData",
                            new string[] { "wordId", "Spell", "Explaination", "Valid" },
                            new string[] { "PRIMARY KEY NOT NULL", "UNIQUE NOT NULL", "NOT NULL", "NOT NULL", "NOT NULL" },
                            new string[] { "INTEGER", "TEXT", "TEXT", "INTEGER DEFAULT 1" });

            sql.DeleteAllDataFromTable("AllWordsData");

            IDataReader reader = null;
            int         pad    = 0;

            for (int i = 0; i < 39286; i++)
            {
                if (i == 34250)
                {
                    pad++;
                    continue;
                }

                reader = sql.ReadSpecificRowsOfTable("AllWords", "*",
                                                     new string[] { string.Format("ID={0}", i) },
                                                     true);

                reader.Read();



                int    id           = i - pad;
                string spell        = reader.GetString(1);
                string explaination = reader.GetString(2);
                int    type         = 0;
                int    valid        = 1;

                if (spell == string.Empty || explaination == string.Empty || spell == null || explaination == null)
                {
                    pad++;
                    continue;
                }

                spell        = spell.Replace("'", "''");
                explaination = explaination.Replace("'", "''");

                sql.InsertValues("AllWordsData",
                                 new string[] { id.ToString(),
                                                "'" + spell + "'",
                                                "'" + explaination + "'",
                                                type.ToString(),
                                                valid.ToString() });

                reader.Close();
            }


            Debug.Log("Finished");

            sql.CloseConnection(CommonData.dataBaseName);
        }
Example #13
0
        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);
        }
Example #14
0
        public static LearnWord RandomWord()
        {
            LearningInfo learnInfo = LearningInfo.Instance;

            int wordId = 0;

            if (learnInfo.learnedWordCount != 0)
            {
                wordId = Random.Range(0, learnInfo.learnedWordCount);

                List <LearnWord> learnedWords = learnInfo.GetAllLearnedWords();

                return(learnedWords [wordId]);
            }


            string tableName = string.Empty;

            WordType wt = GameManager.Instance.gameDataCenter.gameSettings.wordType;

            switch (wt)
            {
            case WordType.CET4:
                tableName = CommonData.CET4Table;
                break;

            case WordType.CET6:
                tableName = "CET6";
                break;

            case WordType.Daily:
                tableName = "Daily";
                break;

            case WordType.Bussiness:
                tableName = "Bussiness";
                break;
            }

            MySQLiteHelper sql = MySQLiteHelper.Instance;

            // 连接数据库
            sql.GetConnectionWith(CommonData.dataBaseName);

            int wordsCount = sql.GetItemCountOfTable(tableName, null, true);

            wordId = Random.Range(0, wordsCount);

            string[] conditions = new string[] { string.Format("wordId={0}", wordId) };

            IDataReader reader = sql.ReadSpecificRowsOfTable(tableName, null, conditions, true);

            reader.Read();

            string spell = reader.GetString(1);

            string explaination = reader.GetString(2);

            string phoneticSymbol = reader.GetString(3);

            string example = reader.GetString(4);

            int learnedTimes = reader.GetInt16(5);

            int ungraspTimes = reader.GetInt16(6);

            return(new LearnWord(wordId, spell, explaination, phoneticSymbol, example, learnedTimes, ungraspTimes));
        }
        /// <summary>
        /// 初始化指定数量的未学习单词
        /// 使用该方法需注意
        /// </summary>
        /// <returns>The learn words in map.</returns>
        public HLHWord[] GetUnlearnedWordsOfCount(int count)
        {
            MySQLiteHelper mySql = MySQLiteHelper.Instance;

            mySql.GetConnectionWith(CommonData.dataBaseName);

            HLHWord[] words = new HLHWord[count];

            string currentWordsTableName = LearningInfo.Instance.GetCurrentLearningWordsTabelName();

            string query = string.Format("SELECT learnedTimes FROM {0} ORDER BY learnedTimes ASC", currentWordsTableName);

            IDataReader reader = mySql.ExecuteQuery(query);

            reader.Read();

            int wholeLearnTime = reader.GetInt16(0);

            query = string.Format("SELECT COUNT(*) FROM {0} WHERE learnedTimes=ungraspTimes AND learnedTimes>0", currentWordsTableName);

            reader = mySql.ExecuteQuery(query);

            reader.Read();

            int wrongWordCount = reader.GetInt32(0);

            if (wrongWordCount >= count)
            {
                query = string.Format("SELECT * FROM {0} WHERE learnedTimes=ungraspTimes AND learnedTimes>0 LIMIT {1}", currentWordsTableName, count);

                int index = 0;

                reader = mySql.ExecuteQuery(query);

                for (int i = 0; i < count; i++)
                {
                    reader.Read();

                    HLHWord word = HLHWord.GetWordFromReader(reader);

                    words[index] = word;

                    index++;
                }
            }
            else
            {
                query = string.Format("SELECT COUNT(*) FROM {0} WHERE learnedTimes={1}", currentWordsTableName, wholeLearnTime);

                reader = mySql.ExecuteQuery(query);

                reader.Read();

                int validWordCount = reader.GetInt32(0);

                if (validWordCount < count - wrongWordCount)
                {
                    string[] colFields  = { "learnedTimes" };
                    string[] values     = { (wholeLearnTime + 1).ToString() };
                    string[] conditions = { "learnedTimes=" + wholeLearnTime.ToString() };

                    mySql.UpdateValues(currentWordsTableName, colFields, values, conditions, true);

                    wholeLearnTime++;
                }

                int index = 0;

                //Debug.Log(wrongWordCount);

                query = string.Format("SELECT * FROM {0} WHERE learnedTimes=ungraspTimes AND learnedTimes>0 LIMIT {1}", currentWordsTableName, wrongWordCount);

                reader = mySql.ExecuteQuery(query);

                for (int i = 0; i < wrongWordCount; i++)
                {
                    reader.Read();

                    HLHWord word = HLHWord.GetWordFromReader(reader);

                    words[index] = word;

                    index++;
                }

                // 边界条件
                string[] condition = { string.Format("learnedTimes={0} ORDER BY RANDOM() LIMIT {1}", wholeLearnTime, count - wrongWordCount) };

                reader = mySql.ReadSpecificRowsOfTable(currentWordsTableName, null, condition, true);

                while (reader.Read())
                {
                    HLHWord word = HLHWord.GetWordFromReader(reader);

                    words[index] = word;

                    index++;
                }
            }

            mySql.CloseConnection(CommonData.dataBaseName);


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