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();
        }
Exemple #2
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);
        }