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>
/// Background task work method.
/// Retrieves the first matching vocab.
/// </summary>
private void DoGetAssociatedVocab(object sender, DoWorkEventArgs e)
{
IEnumerable <VocabEntity> results = _vocabDao.GetMatchingVocab(AssociatedVocabString);
if (results.Any())
{
VocabEntity entity = new VocabEntity();
StringBuilder meanings = new StringBuilder();
StringBuilder readings = new StringBuilder();
foreach (VocabEntity result in results)
{
foreach (VocabMeaning meaning in result.Meanings)
{
meanings.Append(MultiValueFieldHelper.ReplaceSeparator(meaning.Meaning)
.Replace(';', MultiValueFieldHelper.ValueSeparator)
+ MultiValueFieldHelper.ValueSeparator);
}
readings.Append(result.KanaWriting + MultiValueFieldHelper.ValueSeparator);
}
entity.Meanings.Add(new VocabMeaning()
{
Meaning = MultiValueFieldHelper.Expand(MultiValueFieldHelper.Distinct(meanings.ToString()))
});
entity.KanaWriting = MultiValueFieldHelper.Expand(MultiValueFieldHelper.Distinct(readings.ToString()));
AssociatedVocab = entity;
}
else
{
AssociatedVocab = null;
}
}
/// <summary>
/// Loads properties from the given vocab entity.
/// </summary>
/// <param name="se">Target SRS entry.</param>
/// <param name="v">Vocab to load.</param>
public static void LoadFromVocab(this SrsEntry se, VocabEntity v)
{
// Compute the meaning string.
string meaningString = string.Empty;
foreach (VocabMeaning vm in v.Meanings)
{
meaningString += MultiValueFieldHelper.ReplaceSeparator(vm.Meaning)
.Replace(" ;", MultiValueFieldHelper.ValueSeparator.ToString())
+ MultiValueFieldHelper.ValueSeparator;
}
meaningString = meaningString.Trim(
new char[] { MultiValueFieldHelper.ValueSeparator });
meaningString = MultiValueFieldHelper.Trim(meaningString);
meaningString = MultiValueFieldHelper.Expand(meaningString);
// Set values.
se.Meanings = meaningString;
se.Readings = v.KanaWriting;
se.AssociatedVocab = v.KanjiWriting;
if (string.IsNullOrEmpty(se.AssociatedVocab))
{
se.AssociatedVocab = v.KanaWriting;
}
}
private KanjiWritingCharacter MakeCharacter(VocabEntity vocab, char c)
{
return(new KanjiWritingCharacter()
{
Character = c,
Kanji = vocab.Kanji.Where(k => k.Character == c.ToString()).FirstOrDefault(),
OriginalVocab = vocab
});
}
/// <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();
}
}
}
private VocabWritingPart MakeKanaPart(VocabEntity vocab, string reading)
{
VocabWritingPart part = new VocabWritingPart();
part.Furigana = string.Empty;
part.OriginalVocab = vocab;
part.Characters = new List <KanjiWritingCharacter>();
foreach (char c in reading)
{
part.Characters.Add(MakeCharacter(vocab, c));
}
return(part);
}
/// <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();
}
}
}
private VocabWritingPart MakeFuriganaPart(VocabEntity vocab, FuriganaPart furiganaPart)
{
VocabWritingPart part = new VocabWritingPart();
part.OriginalVocab = vocab;
part.Furigana = furiganaPart.Value;
part.Characters = new List <KanjiWritingCharacter>();
for (int i = furiganaPart.StartIndex; i <= furiganaPart.EndIndex; i++)
{
part.Characters.Add(MakeCharacter(vocab, vocab.KanjiWriting[i]));
}
return(part);
}
/// <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>
/// Include the categories of the given vocab in the entity.
/// </summary>
private void IncludeCategories(DaoConnection connection, VocabEntity vocab)
{
IEnumerable <NameValueCollection> categories = connection.Query(
string.Format("SELECT vc.* FROM {0} vcv JOIN {1} vc ON (vcv.{2}=vc.{3}) WHERE vcv.{4}=@vid",
SqlHelper.Table_VocabCategory_Vocab,
SqlHelper.Table_VocabCategory,
SqlHelper.Field_VocabCategory_Vocab_VocabCategoryId,
SqlHelper.Field_VocabCategory_Id,
SqlHelper.Field_VocabCategory_Vocab_VocabId),
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>
/// 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));
}
}
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
if (value is VocabEntity)
{
VocabEntity vocab = (VocabEntity)value;
if (vocab.Categories.Where(c => c.ShortName == "oK" || c.ShortName == "ok" || c.ShortName == "arch").Any())
{
return(new SolidColorBrush(Color.FromArgb(255, 96, 96, 96)));
}
Color destColor = vocab.IsCommon ? Color.FromArgb(255, 239, 255, 222) : Colors.Transparent;
return(new SolidColorBrush(destColor));
}
else
{
throw new ArgumentException("This converter takes a vocab entity.");
}
}
/// <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 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>
/// 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(
string.Format("SELECT k.* FROM {0} kv JOIN {1} k ON (k.{2}=kv.{3}) WHERE kv.{4}=@vid",
SqlHelper.Table_Kanji_Vocab,
SqlHelper.Table_Kanji,
SqlHelper.Field_Kanji_Id,
SqlHelper.Field_Kanji_Vocab_KanjiId,
SqlHelper.Field_Kanji_Vocab_VocabId),
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>
/// Parses a kanji element node.
/// Updates the list with the available info.
/// </summary>
/// <param name="xkanjiElement">Element to parse.</param>
/// <param name="vocabList">Vocab list to be updated.</param>
/// <param name="seq">Sequence number of corresponding vocab item</param>
private void ParseKanji(XElement xkanjiElement, List <VocabEntity> vocabList, long seq)
{
// Create a new vocab with the associated writing.
VocabEntity vocab = new VocabEntity();
vocab.Seq = seq;
vocab.KanjiWriting = xkanjiElement.Element(XmlNode_KanjiReading).Value;
vocab.IsCommon = IsCommonWord(xkanjiElement, XmlNode_KanjiVocabReference);
// For each kanji info node
foreach (XElement xkanjiInf in xkanjiElement.Elements(XmlNode_KanjiInfo))
{
// Associate the vocab with the category referred by the info.
VocabCategory category = GetCategoryByLabel(xkanjiInf.Value);
if (category != null && !vocab.Categories.Contains(category))
{
vocab.Categories.Add(category);
}
}
// Add the created vocab to the list.
vocabList.Add(vocab);
}
public VocabAudio(VocabEntity vocab)
{
KanjiReading = vocab.KanjiWriting;
KanaReading = vocab.KanaWriting;
}
/// <summary>
/// Parses a reading element node.
/// Updates the list with the available info.
/// </summary>
/// <param name="xreadingElement">Element to parse.</param>
/// <param name="vocabList">Vocab list to be updated.</param>
private void ParseReading(XElement xreadingElement, List <VocabEntity> vocabList, long seq)
{
// First, we have to determine the target of the reading node.
// Two possible cases:
// - Scenario 1: There were no kanji readings. In that case, the reading should
// add a new vocab element which has no kanji reading.
// - Scenario 2: There was at least one kanji reading. In that case, the reading
// node targets a set of existing vocabs. They may be filtered by kanji reading
// with the reading constraint nodes.
VocabEntity[] targets;
if (!vocabList.Any())
{
// Scenario 1. Create a new kanji reading, add it to the list, and set it as target.
VocabEntity newVocab = new VocabEntity();
newVocab.Seq = seq;
vocabList.Add(newVocab);
targets = new VocabEntity[] { newVocab };
}
else
{
// Scenario 2. Check constraint nodes to filter the targets.
// Get all reading constraints in an array.
string[] readingConstraints = xreadingElement.Elements(XmlNode_ReadingConstraint)
.Select(x => x.Value).ToArray();
// Filter from the vocab list.
if (readingConstraints.Any())
{
targets = vocabList.Where(v => readingConstraints.Contains(v.KanjiWriting)).ToArray();
}
else
{
targets = vocabList.ToArray();
}
}
// Now that we have the target vocabs, we can get the proper information from the node.
string kanaReading = xreadingElement.Element(XmlNode_KanaReading).Value;
bool isCommon = IsCommonWord(xreadingElement, XmlNode_ReadingVocabReference);
// Get the optional categories defined by the "reading info" nodes.
VocabCategory[] categories = xreadingElement.Elements(XmlNode_ReadingInfo)
.Select(x => GetCategoryByLabel(x.Value))
.Where(c => c != null).ToArray();
// We have the info. Now we can apply it to the targets.
// For each target
foreach (VocabEntity target in targets)
{
// Set the kana reading if not already set.
if (string.IsNullOrEmpty(target.KanaWriting))
{
target.KanaWriting = kanaReading;
// Set the common flag to false only if both the kanji and the reading values are false.
target.IsCommon = target.IsCommon || isCommon;
// Append the categories to the existing collection.
target.Categories = target.Categories.Concat(categories).ToArray();
}
else if (!vocabList.Where(v => v.KanaWriting == kanaReading).Any() && target == targets.Last())
{
// If a target already has a kana reading, we need to create a new vocab.
VocabEntity newVocab = new VocabEntity()
{
Seq = target.Seq,
KanjiWriting = target.KanjiWriting, // Assign the old kanji reading,
IsCommon = target.IsCommon || isCommon, // combined common flag,
FrequencyRank = target.FrequencyRank, // same frequency rank,
KanaWriting = kanaReading, // new kana reading,
Categories = target.Categories.Concat(categories).ToArray() // combined categories.
};
vocabList.Add(newVocab);
}
}
}
public void LoadFromVocab(VocabEntity v)
{
Reference.LoadFromVocab(v);
RaisePropertyChanged("Meanings");
RaisePropertyChanged("Readings");
}
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
if (value is VocabEntity)
{
// Converts a vocab in a list of writing parts.
VocabEntity vocab = (VocabEntity)value;
if (string.IsNullOrWhiteSpace(vocab.Furigana))
{
// No furigana. Make just one part.
VocabWritingPart p = new VocabWritingPart();
p.OriginalVocab = vocab;
if (string.IsNullOrEmpty(vocab.KanjiWriting))
{
// No kanji writing. Use the kana writing.
p.Characters = new List <KanjiWritingCharacter>(vocab.KanaWriting.Length);
foreach (char c in vocab.KanaWriting)
{
p.Characters.Add(MakeCharacter(vocab, c));
}
}
else
{
// Existing kanji writing. Set the furigana to be the whole kana writing.
p.Furigana = p.Furigana = vocab.KanaWriting;
p.Characters = new List <KanjiWritingCharacter>(vocab.KanjiWriting.Length);
foreach (char c in vocab.KanjiWriting)
{
p.Characters.Add(MakeCharacter(vocab, c));
}
}
return(new List <VocabWritingPart>()
{
p
});
}
else
{
// Furigana! Cut it.
List <FuriganaPart> furiganaParts = CutFurigana(vocab.Furigana);
List <VocabWritingPart> parts = new List <VocabWritingPart>();
string currentPart = string.Empty;
// Browse each character to build the vocab writing parts.
for (int i = 0; i < vocab.KanjiWriting.Length; i++)
{
char c = vocab.KanjiWriting[i];
FuriganaPart cover = furiganaParts.FirstOrDefault(f => f.CoversIndex(i));
if (cover == null)
{
// No furigana case.
// Accumulate kana characters.
currentPart += c;
}
else
{
// A furigana covering this character exists.
// Make a new part with the accumulated kana string if not empty.
if (currentPart.Length > 0)
{
parts.Add(MakeKanaPart(vocab, currentPart));
}
currentPart = string.Empty;
// Make a new part for the furigana.
parts.Add(MakeFuriganaPart(vocab, cover));
// Advance the index to the end index.
i = cover.EndIndex;
}
}
if (currentPart.Length > 0)
{
parts.Add(MakeKanaPart(vocab, currentPart));
}
return(parts);
}
}
else
{
throw new ArgumentException("This converter takes a vocab entity value.");
}
}
public ExtendedVocab(VocabEntity dbVocab)
: this(dbVocab, null)
{
}
public ExtendedVocab(VocabEntity dbVocab, ExtendedSrsEntry srsEntry)
{
DbVocab = dbVocab;
Audio = new VocabAudio(dbVocab);
SrsEntry = srsEntry;
}
public VocabVariant(ExtendedVocab parent, VocabEntity variant)
{
Parent = parent;
Variant = variant;
}
