/// <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();
}
}
}
/// <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;
}
/// <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());
}
}
/// <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();
}
}
}