/// <summary>
/// Reads the radicals from the radicals file.
/// </summary>
/// <param name="directoryPath">Path to the base directory of the set.</param>
/// <returns>Radicals read.</returns>
protected override ExtendedRadical[] DoReadData(string directoryPath)
{
List <ExtendedRadical> extendedRadicals = new List <ExtendedRadical>();
string radicalsFilePath = Path.Combine(directoryPath, RadicalsFilePath);
XDocument xdoc = XDocument.Load(radicalsFilePath);
XElement xroot = xdoc.Root;
// Get radicals from DB.
RadicalEntity[] radicals = _radicalDao.GetAllRadicals().ToArray();
foreach (XElement xradical in xroot.Elements(XmlNode_Radical))
{
List <RadicalGroup> groups = new List <RadicalGroup>();
// Try to read the standard form.
XElement xgroups = xradical.Element(XmlNode_RadicalGroups);
if (xgroups != null)
{
// Read the groups.
foreach (XElement xgroup in xgroups.Elements(XmlNode_RadicalGroup))
{
RadicalGroup group = new RadicalGroup();
List <RadicalEntity> groupRadicals = new List <RadicalEntity>();
// Read the group items.
foreach (XElement xitem in xgroup.Elements(XmlNode_RadicalGroupItem))
{
string radicalCharacter =
xitem.ReadAttributeString(XmlAttribute_Radical);
// Find the matching radical.
if (!string.IsNullOrWhiteSpace(radicalCharacter))
{
RadicalEntity radical = radicals.Where(
r => r.Character == radicalCharacter).FirstOrDefault();
if (radical != null)
{
groupRadicals.Add(radical);
}
}
}
// Finalize and add the radical group.
if (groupRadicals.Any())
{
group.Radicals = groupRadicals.ToArray();
groups.Add(group);
}
}
}
else
{
// Try to read the short form.
// Start by reading the radical character.
string radicalCharacter = xradical
.ReadAttributeString(XmlAttribute_Character);
// Find the matching radical.
if (!string.IsNullOrWhiteSpace(radicalCharacter))
{
RadicalEntity radical = radicals.Where(
r => r.Character == radicalCharacter).FirstOrDefault();
if (radical != null)
{
groups.Add(new RadicalGroup()
{
Radicals = new RadicalEntity[1] {
radical
}
});
}
}
}
// Build the extended radical and return it.
if (groups.Any())
{
// Read the attributes.
string name = xradical.ReadAttributeString(XmlAttribute_Name);
string imageSource = xradical.ReadAttributeString(XmlAttribute_ImageSource);
if (imageSource != null)
{
string fullPath = Path.GetFullPath(Path.Combine(directoryPath, imageSource));
if (File.Exists(fullPath))
{
imageSource = new Uri(fullPath).ToString();
}
else
{
imageSource = null;
}
}
string character = xradical.ReadAttributeString(XmlAttribute_Character);
ExtendedRadical extendedRadical = new ExtendedRadical();
extendedRadical.Character = character;
extendedRadical.ImageUri = imageSource;
extendedRadical.Name = name;
extendedRadical.RadicalGroups = groups.ToArray();
// Get frequency.
int frequency = 0;
foreach (RadicalGroup group in extendedRadical.RadicalGroups)
{
foreach (RadicalEntity r in group.Radicals)
{
frequency += r.Kanji.Count;
}
// Not very exact (the AND link between groups is not taken in account)
// but let's do this anyway.
//todo: Compute exact value.
}
extendedRadical.Frequency = frequency;
extendedRadicals.Add(extendedRadical);
}
}
return(extendedRadicals.ToArray());
}
/// <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, int jlptLevel, int wkLevel)
{
const int minJlptLevel = Levels.MinJlptLevel;
const int maxJlptLevel = Levels.MaxJlptLevel;
const int minWkLevel = Levels.MinWkLevel;
const int maxWkLevel = Levels.MaxWkLevel;
string sqlJlptFilter = string.Empty;
if (jlptLevel >= minJlptLevel && jlptLevel <= maxJlptLevel)
{
sqlJlptFilter = string.Format("k.{0}=@jlpt ",
SqlHelper.Field_Vocab_JlptLevel);
parameters.Add(new DaoParameter("@jlpt", jlptLevel));
}
else if (jlptLevel < minJlptLevel)
{
sqlJlptFilter = string.Format("k.{0} IS NULL ",
SqlHelper.Field_Vocab_JlptLevel);
}
string sqlWkFilter = string.Empty;
if (wkLevel >= minWkLevel && wkLevel <= maxWkLevel)
{
sqlWkFilter = string.Format("k.{0}=@wk ",
SqlHelper.Field_Vocab_WaniKaniLevel);
parameters.Add(new DaoParameter("@wk", wkLevel));
}
else if (wkLevel > maxWkLevel)
{
sqlWkFilter = string.Format("k.{0} IS NULL ",
SqlHelper.Field_Vocab_WaniKaniLevel);
}
string sqlTextFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(textFilter))
{
// Build the text filter.
// Example with textFilter="年生まれ" :
//
// WHERE '1959年生まれ' LIKE '%' || k.Character || '%'
sqlTextFilter = string.Format("@textFilter LIKE '%' || k.{0} || '%' ",
SqlHelper.Field_Kanji_Character);
// 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%')
sqlAnyReadingFilter = string.Format(
"(k.{0} LIKE @anyReadingFilter OR k.{1} LIKE @anyReadingFilter OR k.{2} LIKE @anyReadingFilter) ",
SqlHelper.Field_Kanji_KunYomi,
SqlHelper.Field_Kanji_OnYomi,
SqlHelper.Field_Kanji_Nanori);
// And add the parameter.
parameters.Add(new DaoParameter("@anyReadingFilter", string.Format("%{0}%", anyReadingFilter)));
}
else
{
// Any reading filter is not set. Browse the other reading filters.
if (!string.IsNullOrWhiteSpace(onYomiFilter))
{
sqlOnYomiFilter = string.Format("k.{0} LIKE @onYomiFilter ", SqlHelper.Field_Kanji_OnYomi);
parameters.Add(new DaoParameter("@onYomiFilter", string.Format("%{0}%", onYomiFilter)));
}
if (!string.IsNullOrWhiteSpace(kunYomiFilter))
{
sqlKunYomiFilter = string.Format("k.{0} LIKE @kunYomiFilter ", SqlHelper.Field_Kanji_KunYomi);
parameters.Add(new DaoParameter("@kunYomiFilter", string.Format("%{0}%", kunYomiFilter)));
}
if (!string.IsNullOrWhiteSpace(nanoriFilter))
{
sqlNanoriFilter = string.Format("k.{0} LIKE @nanoriFilter ", SqlHelper.Field_Kanji_Nanori);
parameters.Add(new DaoParameter("@nanoriFilter", string.Format("%{0}%", nanoriFilter)));
}
}
StringBuilder sqlRadicalFilter = new StringBuilder();
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.Length > 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.
bool hasMandatoryRadicals = mandatoryRadicals.Any();
if (hasMandatoryRadicals)
{
sqlRadicalFilter.Append("(SELECT COUNT(*) FROM (");
for (int i = 0; i < mandatoryRadicals.Length; i++)
{
RadicalEntity radical = mandatoryRadicals[i];
sqlRadicalFilter.AppendFormat("SELECT @rid{0} ", idParamIndex);
if (i < mandatoryRadicals.Length - 1)
{
sqlRadicalFilter.Append("UNION ");
}
parameters.Add(new DaoParameter("@rid" + idParamIndex++, radical.ID));
}
sqlRadicalFilter.AppendFormat(
"INTERSECT SELECT kr.{0} FROM {1} kr WHERE kr.{2}=k.{3}))=@radicalsCount ",
SqlHelper.Field_Kanji_Radical_RadicalId,
SqlHelper.Table_Kanji_Radical,
SqlHelper.Field_Kanji_Radical_KanjiId,
SqlHelper.Field_Kanji_Id);
parameters.Add(new DaoParameter("@radicalsCount", mandatoryRadicals.Count()));
}
// Now build the requests for the option groups.
for (int i = 0; i < optionGroups.Length; i++)
{
RadicalGroup optionGroup = optionGroups[i];
if (hasMandatoryRadicals || i > 0)
{
sqlRadicalFilter.Append("AND ");
}
sqlRadicalFilter.Append("(SELECT COUNT(*) FROM (");
foreach (RadicalEntity radical in optionGroup.Radicals)
{
sqlRadicalFilter.AppendFormat("SELECT @rid{0} ", idParamIndex);
if (optionGroup.Radicals.Last() != radical)
{
sqlRadicalFilter.Append("UNION ");
}
parameters.Add(new DaoParameter("@rid" + idParamIndex++, radical.ID));
}
sqlRadicalFilter.AppendFormat(
"INTERSECT SELECT kr.{0} FROM {1} kr WHERE kr.{2}=k.{3}))>=1 ",
SqlHelper.Field_Kanji_Radical_RadicalId,
SqlHelper.Table_Kanji_Radical,
SqlHelper.Field_Kanji_Radical_KanjiId,
SqlHelper.Field_Kanji_Id);
}
}
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%')
sqlMeaningFilter = string.Format(
"k.{0} IN (SELECT km.{1} FROM {2} km WHERE km.{3} IS NULL AND km.{4} LIKE @meaningFilter) ",
SqlHelper.Field_Kanji_Id,
SqlHelper.Field_KanjiMeaning_KanjiId,
SqlHelper.Table_KanjiMeaning,
SqlHelper.Field_KanjiMeaning_Language,
SqlHelper.Field_KanjiMeaning_Meaning);
// And add the parameter.
parameters.Add(new DaoParameter("@meaningFilter", "%" + meaningFilter + "%"));
}
string[] sqlArgs =
{
sqlJlptFilter,
sqlWkFilter,
sqlTextFilter,
sqlAnyReadingFilter,
sqlOnYomiFilter,
sqlKunYomiFilter,
sqlNanoriFilter,
sqlRadicalFilter.ToString(),
sqlMeaningFilter
};
bool isFiltered = false;
for (int i = 0; i < sqlArgs.Length; i++)
{
string arg = sqlArgs[i];
if (string.IsNullOrEmpty(arg) || arg.StartsWith("JOIN"))
{
continue;
}
sqlArgs[i] = (isFiltered ? "AND " : "WHERE ") + arg;
isFiltered = true;
}
return(string.Concat(sqlArgs));
}
