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