/// <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);
}
/// <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 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);
}
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);
}
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);
}
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);
}
/// <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);
}
/// <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);
}
/// <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);
}
// 从指定文件（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);
}
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);
}
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;
}
