/// <summary>
/// 更新数据库中的单词数据
/// </summary>
/// <param name="wordType">Word type.</param>
private void UpdateWordsDataBase(MySQLiteHelper sql, int wordType, List <HLHWord> wordsList)
{
string query = string.Empty;
for (int i = 0; i < wordsList.Count; i++)
{
HLHWord word = wordsList[i];
int wordId = word.wordId;
switch (wordType)
{
case 0:
query = string.Format("UPDATE {0} SET learnedTimes={1},ungraspTimes={2},isFamiliar={3} WHERE spell='{4}'",
CommonData.simpleWordsTable, word.learnedTimes, word.ungraspTimes, word.isFamiliar?1:0, word.spell);
break;
case 1:
query = string.Format("UPDATE {0} SET learnedTimes={1},ungraspTimes={2},isFamiliar={3} WHERE spell='{4}'",
CommonData.mediumWordsTabel, word.learnedTimes, word.ungraspTimes, word.isFamiliar ? 1 : 0, word.spell);
break;
case 2:
query = string.Format("UPDATE {0} SET learnedTimes={1},ungraspTimes={2},isFamiliar={3} WHERE spell='{4}'",
CommonData.masterWordsTabel, word.learnedTimes, word.ungraspTimes, word.isFamiliar ? 1 : 0, word.spell);
break;
}
sql.ExecuteQuery(query);
}
}
/// <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);
}
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);
}
/// <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);
}
