Пример #1
0
        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();
                //}
            }
        }
Пример #2
0
        /// <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();
                }
            }
        }
Пример #3
0
        /// <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();
                }
            }
        }
Пример #4
0
        /// <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));
            }
        }
Пример #5
0
        /// <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);
        }
Пример #6
0
        public IEnumerable<VocabEntity> GetAllVocab()
        {
            DaoConnection connection = null;
            try
            {
                connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
                IEnumerable<NameValueCollection> vocabs = connection.Query(
                      "SELECT * FROM " + SqlHelper.Table_Vocab);

                VocabBuilder builder = new VocabBuilder();
                foreach (NameValueCollection nvcVocab in vocabs)
                {
                    yield return builder.BuildEntity(nvcVocab, null);
                }
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
        }
Пример #7
0
        public VocabEntity GetSingleVocabByKanaReading(string kanaReading)
        {
            DaoConnection connection = null;

            try
            {
                //connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
                connection = _connection;

                long count = (long)connection.QueryScalar(
                    string.Format("SELECT COUNT(1) FROM {0} WHERE {1}=@kanaWriting",
                                  SqlHelper.Table_Vocab,
                                  SqlHelper.Field_Vocab_KanaWriting),
                    new DaoParameter("@kanaWriting", kanaReading));

                if (count == 1)
                {
                    IEnumerable <NameValueCollection> vocabs = connection.Query(
                        string.Format("SELECT * FROM {0} WHERE {1}=@kanaWriting",
                                      SqlHelper.Table_Vocab,
                                      SqlHelper.Field_Vocab_KanaWriting),
                        new DaoParameter("@kanaWriting", kanaReading));

                    VocabBuilder builder = new VocabBuilder();
                    return(builder.BuildEntity(vocabs.First(), null));
                }
            }
            finally
            {
                //if (connection != null)
                //{
                //    connection.Dispose();
                //}
            }

            return(null);
        }
Пример #8
0
        public IEnumerable <VocabEntity> GetAllVocab()
        {
            DaoConnection connection = null;

            try
            {
                connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
                IEnumerable <NameValueCollection> vocabs = connection.Query(
                    string.Format("SELECT * FROM {0}", SqlHelper.Table_Vocab));

                VocabBuilder builder = new VocabBuilder();
                foreach (NameValueCollection nvcVocab in vocabs)
                {
                    yield return(builder.BuildEntity(nvcVocab, null));
                }
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
        }
Пример #9
0
        public IEnumerable<VocabEntity> GetVocabByReadings(string kanjiReading, string kanaReading)
        {
            DaoConnection connection = null;
            try
            {
                //connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
                connection = _connection;

                string requestString = string.Empty;
                VocabBuilder builder = new VocabBuilder();
                if (kanjiReading == kanaReading)
                {
                    IEnumerable<NameValueCollection> vocabs = connection.Query(
                      "SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting",
                      new DaoParameter("@kanaWriting", kanaReading));

                    if (vocabs.Count() == 1)
                    {
                        yield return builder.BuildEntity(vocabs.First(), null);
                        yield break;
                    }
                }

                IEnumerable<NameValueCollection> fullMatch = connection.Query(
                      "SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting"
                      + " AND " + SqlHelper.Field_Vocab_KanjiWriting + "=@kanjiWriting",
                      new DaoParameter("@kanaWriting", kanaReading), new DaoParameter("@kanjiWriting", kanjiReading));

                foreach (NameValueCollection match in fullMatch)
                {
                    yield return builder.BuildEntity(match, null);
                }
            }
            finally
            {
                //if (connection != null)
                //{
                //    connection.Dispose();
                //}
            }
        }
Пример #10
0
        /// <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));
            }
        }
Пример #11
0
        /// <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();
                }
            }
        }
Пример #12
0
        /// <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;
        }
Пример #13
0
        /// <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(
                      "SELECT v.* FROM " + SqlHelper.Table_Vocab + " v "
                    + "WHERE v." + SqlHelper.Field_Vocab_KanjiWriting + "=@v "
                    + "ORDER BY v." + SqlHelper.Field_Vocab_IsCommon + " DESC",
                    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(
                          "SELECT v.* FROM " + SqlHelper.Table_Vocab + " v "
                        + "WHERE v." + SqlHelper.Field_Vocab_KanaWriting + "=@v "
                        + "ORDER BY v." + SqlHelper.Field_Vocab_IsCommon + " DESC",
                        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();
                }
            }
        }
Пример #14
0
        public VocabEntity GetSingleVocabByKanaReading(string kanaReading)
        {
            DaoConnection connection = null;
            try
            {
                //connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
                connection = _connection;

                string requestString = string.Empty;

                long count = (long)connection.QueryScalar(
                    "SELECT COUNT(1) FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting",
                    new DaoParameter("@kanaWriting", kanaReading));

                if (count == 1)
                {

                    IEnumerable<NameValueCollection> vocabs = connection.Query(
                        "SELECT * FROM " + SqlHelper.Table_Vocab + " WHERE " + SqlHelper.Field_Vocab_KanaWriting + "=@kanaWriting",
                        new DaoParameter("@kanaWriting", kanaReading));

                    VocabBuilder builder = new VocabBuilder();
                    return builder.BuildEntity(vocabs.First(), null);
                }
            }
            finally
            {
                //if (connection != null)
                //{
                //    connection.Dispose();
                //}
            }

            return null;
        }