/// <summary> /// Gets all kanji with minimal info. /// </summary> /// <returns>All kanji with minimal info.</returns> public IEnumerable <KanjiEntity> GetAllKanji() { DaoConnection connection = null; try { // Create and open synchronously the primary Kanji connection. connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); IEnumerable <NameValueCollection> results = connection.Query( string.Format("SELECT * FROM {0}", SqlHelper.Table_Kanji)); KanjiBuilder kanjiBuilder = new KanjiBuilder(); foreach (NameValueCollection nvcKanji in results) { KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanji, null); yield return(kanji); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Creates and opens a new connection. /// </summary> /// <param name="endpoint">Connection endpoint.</param> /// <returns>Newly created connection in an open state.</returns> public static DaoConnection Open(DaoConnectionEnum endpoint) { DaoConnection connection = new DaoConnection(endpoint); connection.Open(); return(connection); }
/// <summary> /// Retrieves all radicals. /// </summary> public IEnumerable <RadicalEntity> GetAllRadicals() { DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); IEnumerable <NameValueCollection> results = connection.Query( string.Format("SELECT * FROM {0}", SqlHelper.Table_Radical)); RadicalBuilder radicalBuilder = new RadicalBuilder(); foreach (NameValueCollection nvcRadical in results) { RadicalEntity radical = radicalBuilder.BuildEntity(nvcRadical, null); IncludeKanji(connection, radical); yield return(radical); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Retrieves the category with the given label. /// </summary> /// <param name="label">Label of the category to retrieve.</param> /// <returns>Matching category if any. Null otherwise.</returns> public VocabCategory GetCategoryByLabel(string label) { DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); IEnumerable <NameValueCollection> results = connection.Query( string.Format("SELECT * FROM {0} WHERE {1}=@label", SqlHelper.Table_VocabCategory, SqlHelper.Field_VocabCategory_Label), new DaoParameter("@label", label)); VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder(); if (results.Any()) { return(categoryBuilder.BuildEntity(results.First(), null)); } return(null); } finally { if (connection != null) { connection.Dispose(); } } }
public void UpdateFrequencyRank(VocabEntity vocab, int rank) { DaoConnection connection = null; try { //connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); connection = _connection; connection.ExecuteNonQuery( string.Format( "UPDATE {0} SET {1}=@rank WHERE {2}=@id", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_FrequencyRank, SqlHelper.Field_Vocab_Id), new DaoParameter("@rank", rank), new DaoParameter("@id", vocab.ID)); } finally { //if (connection != null) //{ // connection.Dispose(); //} } }
/// <summary> /// Gets all reviews due for the current date. /// </summary> /// <returns>Reviews due for the current date.</returns> public IEnumerable <SrsEntry> GetReviews() { DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); IEnumerable <NameValueCollection> results = connection.Query( "SELECT * FROM " + SqlHelper.Table_SrsEntry + " se WHERE se." + SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se." + SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date" + " ORDER BY RANDOM()", new DaoParameter("@date", DateTime.UtcNow.Ticks)); SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder(); foreach (NameValueCollection nvcEntry in results) { yield return(srsEntryBuilder.BuildEntity(nvcEntry, null)); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Gets the number of reviews due for the current date. /// </summary> /// <returns>Number of reviews due for the current date.</returns> public long GetReviewsCount() { long result = -1; DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); result = (long)connection.QueryScalar( "SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se." + SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se." + SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date", new DaoParameter("@date", DateTime.UtcNow.Ticks)); } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Applies the given value to the given field of the given entries. /// </summary> /// <param name="entities">Entries to edit.</param> /// <param name="fieldName">Name of the field to set.</param> /// <param name="value">Value to set for all entities.</param> /// <returns>Number of entities edited.</returns> private long BulkEditStringField(IEnumerable <SrsEntry> entities, string fieldName, string value) { if (!entities.Any()) { return(0); } DaoConnection connection = null; long result = -1; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); int i = 0; result = 0; while (i < entities.Count()) { List <DaoParameter> parameters = new List <DaoParameter>(); string inStatement = string.Empty; int nMax = Math.Min(entities.Count(), i + BulkBatchCount); for (int n = i; n < nMax; n++) { SrsEntry entry = entities.ElementAt(n); string paramName = "@p" + entry.ID; inStatement += paramName + ","; parameters.Add(new DaoParameter(paramName, entry.ID)); } inStatement = inStatement.TrimEnd(new char[] { ',' }); // Add the "value" parameter. parameters.Add(new DaoParameter("@Value", value)); // Add the "LastUpdateDate" parameter. parameters.Add(new DaoParameter("@LastUpdateDate", DateTime.UtcNow.Ticks)); // Execute the query. result += connection.ExecuteNonQuery("UPDATE " + SqlHelper.Table_SrsEntry + " SET " + fieldName + "=@Value, " + SqlHelper.Field_SrsEntry_LastUpdateDate + "=@LastUpdateDate " + "WHERE " + SqlHelper.Field_SrsEntry_Id + " IN (" + inStatement + ")", parameters.ToArray()); i = nMax; } } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Retrieves and returns the collection of vocab matching the /// given filters. /// </summary> /// <param name="kanji">Kanji filter. Only vocab containing this /// kanji will be filtered in.</param> /// <param name="readingFilter">Reading filter. Only vocab containing /// this string in their kana or kanji reading will be filtered in.</param> /// <param name="meaningFilter">Meaning filter. Only vocab containing /// this string as part of at least one of their meaning entries will /// be filtered in.</param> /// <param name="categoryFilter">If not null, this category is used as the filter.</param> /// <param name="jlptLevel">The JLPT level to filter /// (1-5, where a lower value means it is not covered on the JLPT /// and a higher value means that this filter will be ignored).</param> /// <param name="wkLevel">The WaniKani level to filter /// (1-60, where a higher value means it is not taught by WaniKani /// and a lower value means that this filter will be ignored).</param> /// <param name="isCommonFirst">Indicates if common vocab should be /// presented first. If false, results are sorted only by the length /// of their writing (asc or desc depending on the parameter)</param> /// <param name="isShortWritingFirst">Indicates if results should /// be sorted by ascending or descending writing length. /// If True, short readings come first. If False, long readings /// come first.</param> /// <returns>Vocab entities matching the filters.</returns> public IEnumerable <VocabEntity> GetFilteredVocab(KanjiEntity kanji, string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel, bool isCommonFirst, bool isShortWritingFirst) { List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji, readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel); string sortClause = "ORDER BY "; if (isCommonFirst) { sortClause += string.Format("v.{0} DESC,", SqlHelper.Field_Vocab_IsCommon); } sortClause += string.Format("length(v.{0}) {1}", SqlHelper.Field_Vocab_KanaWriting, (isShortWritingFirst ? "ASC" : "DESC")); DaoConnection connection = null; DaoConnection srsConnection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); IEnumerable <NameValueCollection> vocabs = connection.Query( string.Format("SELECT DISTINCT v.* FROM {0} v {1}{2}", SqlHelper.Table_Vocab, sqlFilterClauses, sortClause), parameters.ToArray()); VocabBuilder vocabBuilder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in vocabs) { VocabEntity vocab = vocabBuilder.BuildEntity(nvcVocab, null); IncludeCategories(connection, vocab); IncludeMeanings(connection, vocab); IncludeKanji(connection, srsConnection, vocab); IncludeSrsEntries(srsConnection, vocab); IncludeVariants(connection, vocab); yield return(vocab); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Gets a set of kanji matching the given filters. /// </summary> /// <param name="radicals">Filters out kanji which do not contain all /// of the contained radicals.</param> /// <param name="textFilter">If set, filters out all kanji that are not /// contained in the string.</param> /// <param name="meaningFilter">Filter for the meaning of the kanji.</param> /// <param name="anyReadingFilter">Filter matching any reading of the kanji. /// <remarks>If set, this parameter will override the three reading filters. /// </remarks></param> /// <param name="onYomiFilter">Filter for the on'yomi reading of the kanji. /// <remarks>This parameter will be ignored if /// <paramref name="anyReadingFilter"/> is set.</remarks></param> /// <param name="kunYomiFilter">Filter for the kun'yomi reading of the kanji. /// <remarks>This parameter will be ignored if /// <paramref name="anyReadingFilter"/> is set.</remarks></param> /// <param name="nanoriFilter">Filter for the nanori reading of the kanji. /// <remarks>This parameter will be ignored if /// <paramref name="anyReadingFilter"/> is set.</remarks></param> /// <returns>Kanji matching the given filters.</returns> public IEnumerable <KanjiEntity> GetFilteredKanji(RadicalGroup[] radicals, string textFilter, string meaningFilter, string anyReadingFilter, string onYomiFilter, string kunYomiFilter, string nanoriFilter, int jlptLevel, int wkLevel) { List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter, meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter, jlptLevel, wkLevel); DaoConnection connection = null; DaoConnection srsConnection = null; try { // Create and open synchronously the primary Kanji connection. connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); // Create the secondary Srs connection and open it asynchronously. srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); // FILTERS COMPUTED. // Execute the final request. IEnumerable <NameValueCollection> results = connection.Query(string.Format( "SELECT * FROM {0} k {1}ORDER BY (k.{2} IS NULL),(k.{2});", SqlHelper.Table_Kanji, sqlFilter, SqlHelper.Field_Kanji_MostUsedRank), parameters.ToArray()); KanjiBuilder kanjiBuilder = new KanjiBuilder(); foreach (NameValueCollection nvcKanji in results) { KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanji, null); IncludeKanjiMeanings(connection, kanji); IncludeRadicals(connection, kanji); IncludeSrsEntries(srsConnection, kanji); yield return(kanji); } } finally { if (connection != null) { connection.Dispose(); } if (srsConnection != null) { srsConnection.Dispose(); } } }
/// <summary> /// Gets the first vocab that exactly matches the given reading. /// </summary> /// <param name="reading">Reading to match.</param> /// <returns>First matching vocab, or null if not found.</returns> public IEnumerable <VocabEntity> GetMatchingVocab(string reading) { DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); IEnumerable <NameValueCollection> vocabs = connection.Query( string.Format("SELECT v.* FROM {0} v WHERE v.{1}=@v ORDER BY v.{2} DESC", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_KanjiWriting, SqlHelper.Field_Vocab_IsCommon), new DaoParameter("@v", reading)); if (vocabs.Any()) { VocabBuilder builder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in vocabs) { VocabEntity result = builder.BuildEntity(nvcVocab, null); IncludeMeanings(connection, result); yield return(result); } } else { vocabs = connection.Query( string.Format("SELECT v.* FROM {0} v WHERE v.{1}=@v ORDER BY v.{2} DESC", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_KanaWriting, SqlHelper.Field_Vocab_IsCommon), new DaoParameter("@v", reading)); VocabBuilder builder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in vocabs) { VocabEntity result = builder.BuildEntity(nvcVocab, null); IncludeMeanings(connection, result); yield return(result); } } } finally { if (connection != null) { connection.Dispose(); } } }
public IEnumerable <VocabEntity> GetVocabByReadings(string kanjiReading, string kanaReading) { DaoConnection connection = null; try { //connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); connection = _connection; VocabBuilder builder = new VocabBuilder(); if (kanjiReading == kanaReading) { IEnumerable <NameValueCollection> vocabs = connection.Query( string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_KanaWriting), new DaoParameter("@kanaWriting", kanaReading)); if (vocabs.Count() == 1) { var result = builder.BuildEntity(vocabs.First(), null); IncludeMeanings(connection, result); yield return(result); yield break; } } IEnumerable <NameValueCollection> fullMatch = connection.Query( string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting AND {2}=@kanjiWriting", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_KanaWriting, SqlHelper.Field_Vocab_KanjiWriting), new DaoParameter("@kanaWriting", kanaReading), new DaoParameter("@kanjiWriting", kanjiReading)); foreach (NameValueCollection match in fullMatch) { var result = builder.BuildEntity(match, null); IncludeMeanings(connection, result); yield return(result); } } finally { //if (connection != null) //{ // connection.Dispose(); //} } }
/// <summary> /// Removes all the given entities from the database. /// </summary> /// <param name="entities">Entities to delete.</param> /// <returns>Number of items successfuly deleted.</returns> public long BulkDelete(IEnumerable <SrsEntry> entities) { if (!entities.Any()) { return(0); } DaoConnection connection = null; long result = -1; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); int i = 0; result = 0; while (i < entities.Count()) { List <DaoParameter> parameters = new List <DaoParameter>(); string inStatement = string.Empty; int nMax = Math.Min(entities.Count(), i + BulkBatchCount); for (int n = i; n < nMax; n++) { SrsEntry entry = entities.ElementAt(n); string paramName = "@p" + entry.ID; inStatement += paramName + ","; parameters.Add(new DaoParameter(paramName, entry.ID)); } inStatement = inStatement.TrimEnd(new char[] { ',' }); // Execute the query. result += connection.ExecuteNonQuery("DELETE FROM " + SqlHelper.Table_SrsEntry + " WHERE " + SqlHelper.Field_SrsEntry_Id + " IN (" + inStatement + ")", parameters.ToArray()); i = nMax; } } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Retrieves and includes the SRS entries matching the given kanji and includes /// them in the entity. /// </summary> internal static void IncludeSrsEntries(DaoConnection connection, KanjiEntity kanji) { IEnumerable <NameValueCollection> nvcEntries = connection.Query( string.Format("SELECT * FROM {0} srs WHERE srs.{1}=@k", SqlHelper.Table_SrsEntry, SqlHelper.Field_SrsEntry_AssociatedKanji), new DaoParameter("@k", kanji.Character)); SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder(); foreach (NameValueCollection nvcEntry in nvcEntries) { kanji.SrsEntries.Add(srsEntryBuilder.BuildEntity(nvcEntry, null)); } }
public IEnumerable <NameValueCollection> CustomQuery(string query) { DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); return(connection.Query(query).ToList()); } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Includes the vocab variants in the entity. /// </summary> private void IncludeVariants(DaoConnection connection, VocabEntity vocab) { IEnumerable <NameValueCollection> results = connection.Query( string.Format("SELECT * FROM {0} WHERE {1}=@gid AND {2}!=@id", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_GroupId, SqlHelper.Field_Vocab_Id), new DaoParameter("@gid", vocab.GroupId), new DaoParameter("@id", vocab.ID)); VocabBuilder builder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in results) { vocab.Variants.Add(builder.BuildEntity(nvcVocab, null)); } }
/// <summary> /// See <see cref="Kanji.Database.Dao.VocabDao.GetFilteredVocab"/>. /// Returns the results count. /// </summary> public long GetFilteredVocabCount(KanjiEntity kanji, string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel) { List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji, readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel); using (DaoConnection connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase)) { return((long)connection.QueryScalar( string.Format("SELECT count(1) FROM {0} v {1}", SqlHelper.Table_Vocab, sqlFilterClauses), parameters.ToArray())); } }
/// <summary> /// Gets a filtered set of SRS entries. /// </summary> /// <param name="filterClauses">Filter clauses.</param> /// <returns>Filtered SRS entries.</returns> public IEnumerable <SrsEntry> GetFilteredItems(FilterClause[] filterClauses) { List <DaoParameter> parameters = new List <DaoParameter>(); string whereClause = string.Empty; bool isFiltered = false; foreach (FilterClause clause in filterClauses) { if (clause != null) { string sqlClause = clause.GetSqlWhereClause(!isFiltered, parameters); if (!string.IsNullOrEmpty(sqlClause)) { whereClause += sqlClause + " "; isFiltered = true; } } } DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); IEnumerable <NameValueCollection> results = connection.Query( "SELECT * FROM " + SqlHelper.Table_SrsEntry + " se " + whereClause + "ORDER BY (se." + SqlHelper.Field_SrsEntry_CreationDate + ") DESC", parameters.ToArray()); SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder(); foreach (NameValueCollection nvcEntry in results) { yield return(srsEntryBuilder.BuildEntity(nvcEntry, null)); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Retrieves and includes the meanings of the given kanji in the entity. /// </summary> internal static void IncludeKanjiMeanings(DaoConnection connection, KanjiEntity kanji) { IEnumerable <NameValueCollection> nvcMeanings = connection.Query( string.Format("SELECT * FROM {0} km WHERE km.{1}=@kanjiId AND km.{2} IS NULL;", SqlHelper.Table_KanjiMeaning, SqlHelper.Field_KanjiMeaning_KanjiId, SqlHelper.Field_KanjiMeaning_Language), new DaoParameter("@kanjiId", kanji.ID)); KanjiMeaningBuilder meaningBuilder = new KanjiMeaningBuilder(); foreach (NameValueCollection nvcMeaning in nvcMeanings) { // For each meaning result : build a meaning and set the associations. KanjiMeaning meaning = meaningBuilder.BuildEntity(nvcMeaning, null); meaning.Kanji = kanji; kanji.Meanings.Add(meaning); } }
public IEnumerable <NameValueCollection> CustomQuery(string query) { DaoConnection connection = null; try { // Create and open synchronously the primary Kanji connection. connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); return(connection.Query(query).ToList()); } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Retrieves and includes kanji entities in the given radical entity. /// </summary> private void IncludeKanji(DaoConnection connection, RadicalEntity radical) { IEnumerable <NameValueCollection> results = connection.Query( string.Format( "SELECT kr.{0} {1} FROM {2} kr WHERE kr.{3}=@rid", SqlHelper.Field_Kanji_Radical_KanjiId, SqlHelper.Field_Kanji_Id, SqlHelper.Table_Kanji_Radical, SqlHelper.Field_Kanji_Radical_RadicalId), new DaoParameter("@rid", radical.ID)); KanjiBuilder kanjiBuilder = new KanjiBuilder(); foreach (NameValueCollection nvcKanjiRadical in results) { KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanjiRadical, null); radical.Kanji.Add(kanji); } }
/// <summary> /// See <see cref="Kanji.Database.Dao.KanjiDao.GetFilteredKanji"/>. /// Returns the result count. /// </summary> public long GetFilteredKanjiCount(RadicalGroup[] radicals, string textFilter, string meaningFilter, string anyReadingFilter, string onYomiFilter, string kunYomiFilter, string nanoriFilter, int jlptLevel, int wkLevel) { List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter, meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter, jlptLevel, wkLevel); using (DaoConnection connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase)) { return((long)connection.QueryScalar( string.Format("SELECT COUNT(1) FROM {0} k {1}", SqlHelper.Table_Kanji, sqlFilter), parameters.ToArray())); } }
/// <summary> /// Retrieves and includes the SRS entries matching the given vocab and includes /// them in the entity. /// </summary> private void IncludeSrsEntries(DaoConnection connection, VocabEntity vocab) { string value = string.IsNullOrEmpty(vocab.KanjiWriting) ? vocab.KanaWriting : vocab.KanjiWriting; IEnumerable <NameValueCollection> nvcEntries = connection.Query( string.Format("SELECT * FROM {0} srs WHERE srs.{1}=@k", SqlHelper.Table_SrsEntry, SqlHelper.Field_SrsEntry_AssociatedVocab), new DaoParameter("@k", value)); SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder(); foreach (NameValueCollection nvcEntry in nvcEntries) { vocab.SrsEntries.Add(srsEntryBuilder.BuildEntity(nvcEntry, null)); } }
/// <summary> /// Includes the categories of the given meaning in the entity. /// </summary> private void IncludeMeaningCategories(DaoConnection connection, VocabMeaning meaning) { IEnumerable <NameValueCollection> categories = connection.Query( string.Format("SELECT vc.* FROM {0} vmvc JOIN {1} vc ON (vmvc.{2}=vc.{3}) WHERE vmvc.{4}=@mid", SqlHelper.Table_VocabMeaning_VocabCategory, SqlHelper.Table_VocabCategory, SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId, SqlHelper.Field_VocabCategory_Id, SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId), new DaoParameter("@mid", meaning.ID)); VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder(); foreach (NameValueCollection nvcCategory in categories) { VocabCategory category = categoryBuilder.BuildEntity(nvcCategory, null); meaning.Categories.Add(category); } }
//public KanjiStrokes GetKanjiStrokes(long id) //{ // KanjiStrokes result = null; // DaoConnection connection = null; // try // { // // Create and open synchronously the primary Kanji connection. // connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); // // FILTERS COMPUTED. // // Execute the final request. // IEnumerable<NameValueCollection> results = connection.Query( // "SELECT * " // + "FROM " + SqlHelper.Table_KanjiStrokes + " ks " // + "WHERE ks." + SqlHelper.Field_KanjiStrokes_Id + "=@ks;", // new DaoParameter("@ks", id)); // if (results.Any()) // { // KanjiStrokesBuilder builder = new KanjiStrokesBuilder(); // result = builder.BuildEntity(results.First(), null); // } // } // finally // { // if (connection != null) // { // connection.Dispose(); // } // } // return result; //} public KanjiStrokes GetKanjiStrokes(long id) { KanjiStrokes result = new KanjiStrokes(); result.ID = id; result.FramesSvg = new byte[0]; DaoConnection connection = null; SQLiteDataReader reader = null; try { // Create and open synchronously the primary Kanji connection. connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); reader = connection.QueryDataReader( string.Format("SELECT {0} FROM {1} WHERE {2}=@id;", SqlHelper.Field_KanjiStrokes_FramesSvg, SqlHelper.Table_KanjiStrokes, SqlHelper.Field_KanjiStrokes_Id), new DaoParameter("@id", id)); while (reader.Read()) { result.FramesSvg = GetBytes(reader); } } finally { if (reader != null) { reader.Close(); reader.Dispose(); } if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Includes the meanings of the given vocab in the entity. /// </summary> private void IncludeMeanings(DaoConnection connection, VocabEntity vocab) { IEnumerable <NameValueCollection> meanings = connection.Query( string.Format("SELECT vm.* FROM {0} vvm JOIN {1} vm ON (vvm.{2}=vm.{3}) WHERE vvm.{4}=@vid", SqlHelper.Table_Vocab_VocabMeaning, SqlHelper.Table_VocabMeaning, SqlHelper.Field_Vocab_VocabMeaning_VocabMeaningId, SqlHelper.Field_VocabMeaning_Id, SqlHelper.Field_Vocab_VocabMeaning_VocabId), new DaoParameter("@vid", vocab.ID)); VocabMeaningBuilder meaningBuilder = new VocabMeaningBuilder(); foreach (NameValueCollection nvcMeaning in meanings) { VocabMeaning meaning = meaningBuilder.BuildEntity(nvcMeaning, null); IncludeMeaningCategories(connection, meaning); vocab.Meanings.Add(meaning); } }
/// <summary> /// Retrieves and includes the radicals of the given kanji in the entity. /// </summary> internal static void IncludeRadicals(DaoConnection connection, KanjiEntity kanji) { IEnumerable <NameValueCollection> nvcRadicals = connection.Query( string.Format("SELECT * FROM {0} r JOIN {1} kr ON (kr.{2}=r.{3}) WHERE kr.{4}=@kanjiId;", SqlHelper.Table_Radical, SqlHelper.Table_Kanji_Radical, SqlHelper.Field_Kanji_Radical_RadicalId, SqlHelper.Field_Radical_Id, SqlHelper.Field_Kanji_Radical_KanjiId), new DaoParameter("@kanjiId", kanji.ID)); RadicalBuilder radicalBuilder = new RadicalBuilder(); foreach (NameValueCollection nvcRadical in nvcRadicals) { // For each meaning result : build a radical and set the associations. RadicalEntity radical = radicalBuilder.BuildEntity(nvcRadical, null); kanji.Radicals.Add(radical); } }
/// <summary> /// Computes and returns which radicals can still be used in a kanji filter in complement to the /// given set of filters, and still return kanji results. /// </summary> public IEnumerable <RadicalEntity> GetAvailableRadicals(RadicalGroup[] radicals, string textFilter, string meaningFilter, string anyReadingFilter, string onYomiFilter, string kunYomiFilter, string nanoriFilter, int jlptLevel, int wkLevel) { // Compute the filters. List <DaoParameter> parameters = new List <DaoParameter>(); string sqlFilter = KanjiDao.BuildKanjiFilterClauses(parameters, radicals, textFilter, meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter, jlptLevel, wkLevel); DaoConnection connection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); IEnumerable <NameValueCollection> results = connection.Query( string.Format( "SELECT DISTINCT ckr.{0} Id " + "FROM {1} k JOIN {2} ckr " + "ON (ckr.{3}=k.{4}) {5}", SqlHelper.Field_Kanji_Radical_RadicalId, SqlHelper.Table_Kanji, SqlHelper.Table_Kanji_Radical, SqlHelper.Field_Kanji_Radical_KanjiId, SqlHelper.Field_Kanji_Id, sqlFilter), parameters.ToArray()); RadicalBuilder radicalBuilder = new RadicalBuilder(); foreach (NameValueCollection nvcRadical in results) { RadicalEntity radical = radicalBuilder.BuildEntity(nvcRadical, null); yield return(radical); } } finally { if (connection != null) { connection.Dispose(); } } }
/// <summary> /// Retrieves and returns the complete VocabEntity matching the given ID. /// </summary> /// <param name="id">Id to search.</param> /// <returns>The VocabEntity that matches the given ID, or null if not found.</returns> public VocabEntity GetVocabById(long id) { VocabEntity result = null; DaoConnection connection = null; DaoConnection srsConnection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); IEnumerable <NameValueCollection> vocabs = connection.Query( string.Format("SELECT * FROM {0} WHERE {1}=@id", SqlHelper.Table_Vocab, SqlHelper.Field_Vocab_Id), new DaoParameter("@id", id)); if (vocabs.Any()) { VocabBuilder builder = new VocabBuilder(); VocabEntity vocab = builder.BuildEntity(vocabs.First(), null); IncludeCategories(connection, vocab); IncludeMeanings(connection, vocab); IncludeKanji(connection, srsConnection, vocab); IncludeSrsEntries(srsConnection, vocab); IncludeVariants(connection, vocab); result = vocab; } } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Gets the number of items matching the given filter clauses. /// </summary> /// <param name="filterClauses">Filter clauses to match.</param> /// <returns>Number of items matching the filter clauses.</returns> public long GetFilteredItemsCount(FilterClause[] filterClauses) { List <DaoParameter> parameters = new List <DaoParameter>(); string whereClause = string.Empty; bool isFiltered = false; foreach (FilterClause clause in filterClauses) { if (clause != null) { string sqlClause = clause.GetSqlWhereClause(!isFiltered, parameters); if (!string.IsNullOrEmpty(sqlClause)) { whereClause += sqlClause + " "; isFiltered = true; } } } DaoConnection connection = null; long result = -1; try { connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase); result = (long)connection.QueryScalar( "SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se " + whereClause, parameters.ToArray()); } finally { if (connection != null) { connection.Dispose(); } } return(result); }
/// <summary> /// Retrieves and includes kanji entities in the given radical entity. /// </summary> private void IncludeKanji(DaoConnection connection, RadicalEntity radical) { IEnumerable<NameValueCollection> results = connection.Query( "SELECT kr." + SqlHelper.Field_Kanji_Radical_KanjiId + " " + SqlHelper.Field_Kanji_Id + " FROM " + SqlHelper.Table_Kanji_Radical + " kr WHERE kr." + SqlHelper.Field_Kanji_Radical_RadicalId + "=@rid", new DaoParameter("@rid", radical.ID)); KanjiBuilder kanjiBuilder = new KanjiBuilder(); foreach (NameValueCollection nvcKanjiRadical in results) { KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanjiRadical, null); radical.Kanji.Add(kanji); } }
/// <summary> /// Gets a set of kanji matching the given filters. /// </summary> /// <param name="radicals">Filters out kanji which do not contain all /// of the contained radicals.</param> /// <param name="textFilter">If set, filters out all kanji that are not /// contained in the string.</param> /// <param name="meaningFilter">Filter for the meaning of the kanji.</param> /// <param name="anyReadingFilter">Filter matching any reading of the kanji. /// <remarks>If set, this parameter will override the three reading filters. /// </remarks></param> /// <param name="onYomiFilter">Filter for the on'yomi reading of the kanji. /// <remarks>This parameter will be ignored if /// <paramref name="anyReadingFilter"/> is set.</remarks></param> /// <param name="kunYomiFilter">Filter for the kun'yomi reading of the kanji. /// <remarks>This parameter will be ignored if /// <paramref name="anyReadingFilter"/> is set.</remarks></param> /// <param name="nanoriFilter">Filter for the nanori reading of the kanji. /// <remarks>This parameter will be ignored if /// <paramref name="anyReadingFilter"/> is set.</remarks></param> /// <returns>Kanji matching the given filters.</returns> public IEnumerable<KanjiEntity> GetFilteredKanji(RadicalGroup[] radicals, string textFilter, string meaningFilter, string anyReadingFilter, string onYomiFilter, string kunYomiFilter, string nanoriFilter) { List<DaoParameter> parameters = new List<DaoParameter>(); string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter, meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter); DaoConnection connection = null; DaoConnection srsConnection = null; try { // Create and open synchronously the primary Kanji connection. connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); // Create the secondary Srs connection and open it asynchronously. srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); // FILTERS COMPUTED. // Execute the final request. IEnumerable<NameValueCollection> results = connection.Query( "SELECT * " + "FROM " + SqlHelper.Table_Kanji + " k " + sqlFilter + "ORDER BY (k." + SqlHelper.Field_Kanji_MostUsedRank + " IS NULL)," + "(k." + SqlHelper.Field_Kanji_MostUsedRank + ");", parameters.ToArray()); KanjiBuilder kanjiBuilder = new KanjiBuilder(); foreach (NameValueCollection nvcKanji in results) { KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanji, null); IncludeKanjiMeanings(connection, kanji); IncludeRadicals(connection, kanji); IncludeSrsEntries(srsConnection, kanji); yield return kanji; } } finally { if (connection != null) { connection.Dispose(); } if (srsConnection != null) { srsConnection.Dispose(); } } }
/// <summary> /// Retrieves and includes the meanings of the given kanji in the entity. /// </summary> internal static void IncludeKanjiMeanings(DaoConnection connection, KanjiEntity kanji) { IEnumerable<NameValueCollection> nvcMeanings = connection.Query( "SELECT * " + "FROM " + SqlHelper.Table_KanjiMeaning + " km " + "WHERE km." + SqlHelper.Field_KanjiMeaning_KanjiId + "=@kanjiId " + "AND km." + SqlHelper.Field_KanjiMeaning_Language + " IS NULL;", new DaoParameter("@kanjiId", kanji.ID)); KanjiMeaningBuilder meaningBuilder = new KanjiMeaningBuilder(); foreach (NameValueCollection nvcMeaning in nvcMeanings) { // For each meaning result : build a meaning and set the associations. KanjiMeaning meaning = meaningBuilder.BuildEntity(nvcMeaning, null); meaning.Kanji = kanji; kanji.Meanings.Add(meaning); } }
/// <summary> /// Retrieves and includes the radicals of the given kanji in the entity. /// </summary> internal static void IncludeRadicals(DaoConnection connection, KanjiEntity kanji) { IEnumerable<NameValueCollection> nvcRadicals = connection.Query( "SELECT * " + "FROM " + SqlHelper.Table_Radical + " r " + "JOIN " + SqlHelper.Table_Kanji_Radical + " kr " + "ON (kr." + SqlHelper.Field_Kanji_Radical_RadicalId + "=r." + SqlHelper.Field_Radical_Id + ") " + "WHERE kr." + SqlHelper.Field_Kanji_Radical_KanjiId + "=@kanjiId;", new DaoParameter("@kanjiId", kanji.ID)); RadicalBuilder radicalBuilder = new RadicalBuilder(); foreach (NameValueCollection nvcRadical in nvcRadicals) { // For each meaning result : build a radical and set the associations. RadicalEntity radical = radicalBuilder.BuildEntity(nvcRadical, null); kanji.Radicals.Add(radical); } }
/// <summary> /// Retrieves and includes the SRS entries matching the given kanji and includes /// them in the entity. /// </summary> internal static void IncludeSrsEntries(DaoConnection connection, KanjiEntity kanji) { IEnumerable<NameValueCollection> nvcEntries = connection.Query( "SELECT * " + "FROM " + SqlHelper.Table_SrsEntry + " srs " + "WHERE srs." + SqlHelper.Field_SrsEntry_AssociatedKanji + "=@k", new DaoParameter("@k", kanji.Character)); SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder(); foreach (NameValueCollection nvcEntry in nvcEntries) { kanji.SrsEntries.Add(srsEntryBuilder.BuildEntity(nvcEntry, null)); } }
/// <summary> /// Retrieves and returns the complete VocabEntity matching the given ID. /// </summary> /// <param name="id">Id to search.</param> /// <returns>The VocabEntity that matches the given ID, or null if not found.</returns> public VocabEntity GetVocabById(long id) { VocabEntity result = null; DaoConnection connection = null; DaoConnection srsConnection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); IEnumerable<NameValueCollection> vocabs = connection.Query( "SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_Id + "=@id", new DaoParameter("@id", id)); if (vocabs.Any()) { VocabBuilder builder = new VocabBuilder(); VocabEntity vocab = builder.BuildEntity(vocabs.First(), null); IncludeCategories(connection, vocab); IncludeMeanings(connection, vocab); IncludeKanji(connection, srsConnection, vocab); IncludeSrsEntries(srsConnection, vocab); IncludeVariants(connection, vocab); result = vocab; } } finally { if (connection != null) { connection.Dispose(); } } return result; }
/// <summary> /// Retrieves and returns the collection of vocab matching the /// given filters. /// </summary> /// <param name="kanji">Kanji filter. Only vocab containing this /// kanji will be filtered in.</param> /// <param name="readingFilter">Reading filter. Only vocab containing /// this string in their kana or kanji reading will be filtered in.</param> /// <param name="meaningFilter">Meaning filter. Only vocab containing /// this string as part of at least one of their meaning entries will /// be filtered in.</param> /// <param name="isCommonFirst">Indicates if common vocab should be /// presented first. If false, results are sorted only by the length /// of their writing (asc or desc depending on the parameter)</param> /// <param name="isShortWritingFirst">Indicates if results should /// be sorted by ascending or descending writing length. /// If True, short readings come first. If False, long readings /// come first.</param> /// <returns>Vocab entities matching the filters.</returns> public IEnumerable<VocabEntity> GetFilteredVocab(KanjiEntity kanji, string readingFilter, string meaningFilter, bool isCommonFirst, bool isShortWritingFirst) { List<DaoParameter> parameters = new List<DaoParameter>(); string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji, readingFilter, meaningFilter); string sortClause = "ORDER BY "; if (isCommonFirst) { sortClause += "v." + SqlHelper.Field_Vocab_IsCommon + " DESC,"; } sortClause += "length(v." + SqlHelper.Field_Vocab_KanaWriting + ") " + (isShortWritingFirst ? "ASC" : "DESC"); DaoConnection connection = null; DaoConnection srsConnection = null; try { connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); srsConnection = new DaoConnection(DaoConnectionEnum.SrsDatabase); srsConnection.OpenAsync(); IEnumerable<NameValueCollection> vocabs = connection.Query( "SELECT DISTINCT v.* FROM " + SqlHelper.Table_Vocab + " v " + sqlFilterClauses + sortClause, parameters.ToArray()); VocabBuilder vocabBuilder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in vocabs) { VocabEntity vocab = vocabBuilder.BuildEntity(nvcVocab, null); IncludeCategories(connection, vocab); IncludeMeanings(connection, vocab); IncludeKanji(connection, srsConnection, vocab); IncludeSrsEntries(srsConnection, vocab); IncludeVariants(connection, vocab); yield return vocab; } } finally { if (connection != null) { connection.Dispose(); } } }
public void OpenMassTransaction() { _connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase); }
/// <summary> /// Includes the kanji of the given vocab in the entity. /// </summary> private void IncludeKanji(DaoConnection connection, DaoConnection srsConnection, VocabEntity vocab) { IEnumerable<NameValueCollection> results = connection.Query( "SELECT k.* FROM " + SqlHelper.Table_Kanji_Vocab + " kv " + "JOIN " + SqlHelper.Table_Kanji + " k ON (k." + SqlHelper.Field_Kanji_Id + "=kv." + SqlHelper.Field_Kanji_Vocab_KanjiId + ") WHERE kv." + SqlHelper.Field_Kanji_Vocab_VocabId + "=@vid", new DaoParameter("@vid", vocab.ID)); KanjiBuilder kanjiBuilder = new KanjiBuilder(); foreach (NameValueCollection nvcKanji in results) { KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanji, null); KanjiDao.IncludeKanjiMeanings(connection, kanji); KanjiDao.IncludeRadicals(connection, kanji); KanjiDao.IncludeSrsEntries(srsConnection, kanji); vocab.Kanji.Add(kanji); } }
/// <summary> /// Includes the vocab variants in the entity. /// </summary> private void IncludeVariants(DaoConnection connection, VocabEntity vocab) { IEnumerable<NameValueCollection> results = connection.Query( "SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_GroupId + "=@gid AND " + SqlHelper.Field_Vocab_Id + "!=@id", new DaoParameter("@gid", vocab.GroupId), new DaoParameter("@id", vocab.ID)); VocabBuilder builder = new VocabBuilder(); foreach (NameValueCollection nvcVocab in results) { vocab.Variants.Add(builder.BuildEntity(nvcVocab, null)); } }
/// <summary> /// Include the categories of the given vocab in the entity. /// </summary> private void IncludeCategories(DaoConnection connection, VocabEntity vocab) { IEnumerable<NameValueCollection> categories = connection.Query( "SELECT vc.* FROM " + SqlHelper.Table_VocabCategory_Vocab + " vcv " + "JOIN " + SqlHelper.Table_VocabCategory + " vc ON (vcv." + SqlHelper.Field_VocabCategory_Vocab_VocabCategoryId + "=vc." + SqlHelper.Field_VocabCategory_Id + ") WHERE vcv." + SqlHelper.Field_VocabCategory_Vocab_VocabId + "=@vid", new DaoParameter("@vid", vocab.ID)); VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder(); foreach (NameValueCollection nvcCategory in categories) { VocabCategory category = categoryBuilder.BuildEntity(nvcCategory, null); vocab.Categories.Add(category); } }
/// <summary> /// Includes the meanings of the given vocab in the entity. /// </summary> private void IncludeMeanings(DaoConnection connection, VocabEntity vocab) { IEnumerable<NameValueCollection> meanings = connection.Query( "SELECT vm.* FROM " + SqlHelper.Table_Vocab_VocabMeaning + " vvm " + "JOIN " + SqlHelper.Table_VocabMeaning + " vm ON (vvm." + SqlHelper.Field_Vocab_VocabMeaning_VocabMeaningId + "=vm." + SqlHelper.Field_VocabMeaning_Id + ") WHERE vvm." + SqlHelper.Field_Vocab_VocabMeaning_VocabId + "=@vid", new DaoParameter("@vid", vocab.ID)); VocabMeaningBuilder meaningBuilder = new VocabMeaningBuilder(); foreach (NameValueCollection nvcMeaning in meanings) { VocabMeaning meaning = meaningBuilder.BuildEntity(nvcMeaning, null); IncludeMeaningCategories(connection, meaning); vocab.Meanings.Add(meaning); } }
/// <summary> /// Includes the categories of the given meaning in the entity. /// </summary> private void IncludeMeaningCategories(DaoConnection connection, VocabMeaning meaning) { IEnumerable<NameValueCollection> categories = connection.Query( "SELECT vc.* FROM " + SqlHelper.Table_VocabMeaning_VocabCategory + " vmvc " + "JOIN " + SqlHelper.Table_VocabCategory + " vc ON (vmvc." + SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId + "=vc." + SqlHelper.Field_VocabCategory_Id + ") WHERE vmvc." + SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId + "=@mid", new DaoParameter("@mid", meaning.ID)); VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder(); foreach (NameValueCollection nvcCategory in categories) { VocabCategory category = categoryBuilder.BuildEntity(nvcCategory, null); meaning.Categories.Add(category); } }
/// <summary> /// Retrieves and includes the SRS entries matching the given vocab and includes /// them in the entity. /// </summary> private void IncludeSrsEntries(DaoConnection connection, VocabEntity vocab) { string value = string.IsNullOrEmpty(vocab.KanjiWriting) ? vocab.KanaWriting : vocab.KanjiWriting; IEnumerable<NameValueCollection> nvcEntries = connection.Query( "SELECT * " + "FROM " + SqlHelper.Table_SrsEntry + " srs " + "WHERE srs." + SqlHelper.Field_SrsEntry_AssociatedVocab + "=@k", new DaoParameter("@k", value)); SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder(); foreach (NameValueCollection nvcEntry in nvcEntries) { vocab.SrsEntries.Add(srsEntryBuilder.BuildEntity(nvcEntry, null)); } }
/// <summary> /// Creates and opens a new connection. /// </summary> /// <param name="endpoint">Connection endpoint.</param> /// <returns>Newly created connection in an open state.</returns> public static DaoConnection Open(DaoConnectionEnum endpoint) { DaoConnection connection = new DaoConnection(endpoint); connection.Open(); return connection; }