Ejemplo n.º 1
0
        /// <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)
        {
            // Compute the filters.
            List<DaoParameter> parameters = new List<DaoParameter>();
            string sqlFilter = KanjiDao.BuildKanjiFilterClauses(parameters, radicals, textFilter,
                meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter);

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

                IEnumerable<NameValueCollection> results = connection.Query(
                  "SELECT DISTINCT ckr." + SqlHelper.Field_Kanji_Radical_RadicalId + " Id "
                + "FROM " + SqlHelper.Table_Kanji + " k "
                + "JOIN " + SqlHelper.Table_Kanji_Radical + " ckr "
                + "ON (ckr." + SqlHelper.Field_Kanji_Radical_KanjiId
                + "=k." + 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();
                }
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Builds the SQL filter clauses to retrieve filtered kanji.
        /// </summary>
        internal static string BuildKanjiFilterClauses(List<DaoParameter> parameters, RadicalGroup[] radicalGroups,
            string textFilter, string meaningFilter, string anyReadingFilter, string onYomiFilter,
            string kunYomiFilter, string nanoriFilter)
        {
            bool isFiltered = false;

            string sqlTextFilter = string.Empty;
            if (!string.IsNullOrWhiteSpace(textFilter))
            {
                // Build the text filter.
                // Example with textFilter="年生まれ" :
                // 
                // WHERE '1959年生まれ' LIKE '%' || k.Character || '%'

                sqlTextFilter += "WHERE @textFilter LIKE '%' || k."
                    + SqlHelper.Field_Kanji_Character + " || '%' ";
                isFiltered = true;

                // And add the parameter.
                parameters.Add(new DaoParameter("@textFilter", textFilter));
            }

            string sqlAnyReadingFilter = string.Empty;
            string sqlOnYomiFilter = string.Empty;
            string sqlKunYomiFilter = string.Empty;
            string sqlNanoriFilter = string.Empty;
            if (!string.IsNullOrWhiteSpace(anyReadingFilter))
            {
                // Build the any reading filter.
                // Example with anyReadingFilter="test" :
                //
                // WHERE (k.KunYomi LIKE '%test%' OR k.OnYomi LIKE '%test%'
                // OR k.Nanori LIKE '%test%')

                // Start with the AND or WHERE, depending on the existence of previous filters.
                sqlAnyReadingFilter = isFiltered ? "AND " : "WHERE ";
                isFiltered = true;

                // Then include the test.
                sqlAnyReadingFilter += "(k." + SqlHelper.Field_Kanji_KunYomi
                    + " LIKE @anyReadingFilter OR k." + SqlHelper.Field_Kanji_OnYomi
                    + " LIKE @anyReadingFilter OR k." + SqlHelper.Field_Kanji_Nanori
                    + " LIKE @anyReadingFilter) ";

                // And add the parameter.
                parameters.Add(new DaoParameter("@anyReadingFilter", "%" + anyReadingFilter + "%"));
            }
            else
            {
                // Any reading filter is not set. Browse the other reading filters.
                if (!string.IsNullOrWhiteSpace(onYomiFilter))
                {
                    // Start with the AND or WHERE, depending on the existence of previous filters.
                    sqlOnYomiFilter = isFiltered ? "AND " : "WHERE ";
                    isFiltered = true;

                    sqlOnYomiFilter += "k." + SqlHelper.Field_Kanji_OnYomi
                        + " LIKE @onYomiFilter ";

                    parameters.Add(new DaoParameter("@onYomiFilter", "%" + onYomiFilter + "%"));
                }
                if (!string.IsNullOrWhiteSpace(kunYomiFilter))
                {
                    // Start with the AND or WHERE, depending on the existence of previous filters.
                    sqlKunYomiFilter = isFiltered ? "AND " : "WHERE ";
                    isFiltered = true;

                    sqlKunYomiFilter += "k." + SqlHelper.Field_Kanji_KunYomi
                        + " LIKE @kunYomiFilter ";

                    parameters.Add(new DaoParameter("@kunYomiFilter", "%" + kunYomiFilter + "%"));
                }
                if (!string.IsNullOrWhiteSpace(nanoriFilter))
                {
                    // Start with the AND or WHERE, depending on the existence of previous filters.
                    sqlNanoriFilter = isFiltered ? "AND " : "WHERE ";
                    isFiltered = true;

                    sqlNanoriFilter += "k." + SqlHelper.Field_Kanji_Nanori
                        + " LIKE @nanoriFilter ";

                    parameters.Add(new DaoParameter("@nanoriFilter", "%" + nanoriFilter + "%"));
                }
            }

            string sqlRadicalFilter = string.Empty;
            if (radicalGroups.Any())
            {
                // Build the radical sql filter. For example with:
                // [0] = { 7974, 7975 }
                // [1] = { 7976 }
                // [2] = { 7977 }
                // ... we would want something like:
                //
                //WHERE (SELECT COUNT(*)
                //     FROM
                //     (
                //         SELECT 7976 UNION SELECT 7977
                //         INTERSECT
                //         SELECT kr.Radicals_Id
                //         FROM KanjiRadical kr
                //         WHERE kr.Kanji_Id = k.Id
                //     ))=2
                //     AND (SELECT COUNT(*)
                //     FROM
                //     (
                //         SELECT 7974 UNION SELECT 7975
                //         INTERSECT
                //         SELECT kr.Radicals_Id
                //         FROM KanjiRadical kr
                //         WHERE kr.Kanji_Id = k.Id
                //     )) >= 1

                // Get the mandatory radicals. In our example, these would be {7976,7977}.
                RadicalEntity[] mandatoryRadicals = radicalGroups
                    .Where(g => g.Radicals.Count() == 1)
                    .SelectMany(g => g.Radicals).ToArray();

                // Get the other radical groups. In our example, this would be {{7974,7975}}.
                RadicalGroup[] optionGroups = radicalGroups.Where(g => g.Radicals.Count() > 1).ToArray();

                // We need to build one request per option group,
                // and one request for all mandatory radicals.
                int idParamIndex = 0;

                // Start with the request for all mandatory radicals.
                if (mandatoryRadicals.Any())
                {
                    // Start with the AND or WHERE, depending on the existence of previous filters.
                    sqlRadicalFilter += isFiltered ? "AND " : "WHERE ";
                    isFiltered = true;

                    sqlRadicalFilter += "(SELECT COUNT(*) FROM (";
                    foreach (RadicalEntity radical in mandatoryRadicals)
                    {
                        sqlRadicalFilter += "SELECT @rid" + idParamIndex + " ";
                        if (mandatoryRadicals.Last() != radical)
                        {
                            sqlRadicalFilter += "UNION ";
                        }
                        parameters.Add(new DaoParameter("@rid" + idParamIndex++, radical.ID));
                    }
                    sqlRadicalFilter += "INTERSECT SELECT kr." + SqlHelper.Field_Kanji_Radical_RadicalId + " "
                                  + "FROM " + SqlHelper.Table_Kanji_Radical + " kr "
                                  + "WHERE kr." + SqlHelper.Field_Kanji_Radical_KanjiId
                                  + "=k." + SqlHelper.Field_Kanji_Id + "))=@radicalsCount ";
                    parameters.Add(new DaoParameter("@radicalsCount", mandatoryRadicals.Count()));
                }

                // Now build the requests for the option groups.
                foreach (RadicalGroup optionGroup in optionGroups)
                {
                    // Start with the AND or WHERE, depending on the existence of previous filters.
                    sqlRadicalFilter += isFiltered ? "AND " : "WHERE ";
                    isFiltered = true;

                    sqlRadicalFilter += "(SELECT COUNT(*) FROM (";
                    foreach (RadicalEntity radical in optionGroup.Radicals)
                    {
                        sqlRadicalFilter += "SELECT @rid" + idParamIndex + " ";
                        if (optionGroup.Radicals.Last() != radical)
                        {
                            sqlRadicalFilter += "UNION ";
                        }
                        parameters.Add(new DaoParameter("@rid" + idParamIndex++, radical.ID));
                    }
                    sqlRadicalFilter += "INTERSECT SELECT kr." + SqlHelper.Field_Kanji_Radical_RadicalId + " "
                                  + "FROM " + SqlHelper.Table_Kanji_Radical + " kr "
                                  + "WHERE kr." + SqlHelper.Field_Kanji_Radical_KanjiId
                                  + "=k." + SqlHelper.Field_Kanji_Id + "))>=1 ";
                }
            }

            string sqlMeaningFilter = string.Empty;
            if (!string.IsNullOrWhiteSpace(meaningFilter))
            {
                // Build the meaning filter.
                // Example with meaningFilter="test" :
                //
                // WHERE EXISTS (SELECT * FROM KanjiMeaningSet km
                // WHERE km.Kanji_Id=k.Id AND km.Language IS NULL
                // AND km.Meaning LIKE '%test%') 

                // Start with the AND or WHERE, depending on the existence of previous filters.
                sqlMeaningFilter = isFiltered ? "AND " : "WHERE ";
                isFiltered = true;

                // Then include the test.
                sqlMeaningFilter += "k." + SqlHelper.Field_Kanji_Id + " IN (SELECT km."
                    + SqlHelper.Field_KanjiMeaning_KanjiId + " FROM " + SqlHelper.Table_KanjiMeaning
                    + " km WHERE km." + SqlHelper.Field_KanjiMeaning_Language + " IS NULL "
                    + "AND km." + SqlHelper.Field_KanjiMeaning_Meaning + " "
                    + "LIKE @meaningFilter) ";

                // And add the parameter.
                parameters.Add(new DaoParameter("@meaningFilter", "%" + meaningFilter + "%"));
            }

            return sqlTextFilter
                + sqlAnyReadingFilter
                + sqlOnYomiFilter
                + sqlKunYomiFilter
                + sqlNanoriFilter
                + sqlRadicalFilter
                + sqlMeaningFilter;
        }
Ejemplo n.º 3
0
        /// <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)
        {
            List<DaoParameter> parameters = new List<DaoParameter>();
            string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter,
                meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter);

            using (DaoConnection connection =
                DaoConnection.Open(DaoConnectionEnum.KanjiDatabase))
            {
                return (long)connection.QueryScalar(
                    "SELECT COUNT(1) FROM " + SqlHelper.Table_Kanji + " k " + sqlFilter,
                    parameters.ToArray());
            }
        }
Ejemplo n.º 4
0
        /// <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();
                }
            }
        }