public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
if (value is IEnumerable<VocabCategory>)
{
IEnumerable<VocabCategory> categories = (IEnumerable<VocabCategory>)value;
StringBuilder fullString = new StringBuilder();
foreach (VocabCategory category in categories)
{
if (CategoryDictionary.ContainsKey(category.ID))
{
fullString.Append(CategoryDictionary[category.ID].Label).Append(" ; ");
}
}
return fullString.ToString().Trim(new char[] { ' ', ';' });
}
else if (value is VocabCategory)
{
VocabCategory category = (VocabCategory)value;
if (CategoryDictionary.ContainsKey(category.ID))
{
return CategoryDictionary[category.ID].Label;
}
}
return null;
}
/// <summary>
/// Includes the categories of the given meaning in the entity.
/// </summary>
private void IncludeMeaningCategories(DaoConnection connection, VocabMeaning meaning)
{
IEnumerable <NameValueCollection> categories = connection.Query(
string.Format("SELECT vc.* FROM {0} vmvc JOIN {1} vc ON (vmvc.{2}=vc.{3}) WHERE vmvc.{4}=@mid",
SqlHelper.Table_VocabMeaning_VocabCategory,
SqlHelper.Table_VocabCategory,
SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId,
SqlHelper.Field_VocabCategory_Id,
SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId),
new DaoParameter("@mid", meaning.ID));
VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder();
foreach (NameValueCollection nvcCategory in categories)
{
VocabCategory category = categoryBuilder.BuildEntity(nvcCategory, null);
meaning.Categories.Add(category);
}
}
/// <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);
}
private static VocabCategoryInfo GetInfo(VocabCategory c)
{
switch (c.ShortName)
{
case "MA":
return new VocabCategoryInfo(R.VocabCat_MartialArts, Colors.Black);
case "X":
return new VocabCategoryInfo(R.VocabCat_X, Colors.Black);
case "abbr":
return new VocabCategoryInfo(R.VocabCat_Abbreviation, Colors.Black);
case "adj-i":
return new VocabCategoryInfo(R.VocabCat_IAdjective, Colors.Black);
case "adj-na":
return new VocabCategoryInfo(R.VocabCat_NaAdjective, Colors.Black);
case "adj-no":
return new VocabCategoryInfo(R.VocabCat_NoAdjective, Colors.Black);
case "adj-pn":
return new VocabCategoryInfo(R.VocabCat_PrenominalAdjective, Colors.Black);
case "adj-t":
return new VocabCategoryInfo(R.VocabCat_TaruAdjective, Colors.Black);
case "adj-f":
return new VocabCategoryInfo(R.VocabCat_PrenominalNounVerb, Colors.Black);
case "adj":
return new VocabCategoryInfo(R.VocabCat_Adjective, Colors.Black);
case "adv":
return new VocabCategoryInfo(R.VocabCat_Adverb, Colors.Black);
case "adv-to":
return new VocabCategoryInfo(R.VocabCat_ToAdverb, Colors.Black);
case "arch":
return new VocabCategoryInfo(R.VocabCat_Archaism, Colors.Black);
case "ateji":
return new VocabCategoryInfo(R.VocabCat_Ateji, Colors.Black);
case "aux":
return new VocabCategoryInfo(R.VocabCat_Auxiliary, Colors.Black);
case "aux-v":
return new VocabCategoryInfo(R.VocabCat_AuxiliaryVerb, Colors.Black);
case "aux-adj":
return new VocabCategoryInfo(R.VocabCat_AuxiliaryAdjective, Colors.Black);
case "Buddh":
return new VocabCategoryInfo(R.VocabCat_BuddhistTerm, Colors.Black);
case "chem":
return new VocabCategoryInfo(R.VocabCat_Chemistry, Colors.Black);
case "chn":
return new VocabCategoryInfo(R.VocabCat_Children, Colors.Black);
case "col":
return new VocabCategoryInfo(R.VocabCat_Colloquialism, Colors.Black);
case "comp":
return new VocabCategoryInfo(R.VocabCat_ComputerTerminology, Colors.Black);
case "conj":
return new VocabCategoryInfo(R.VocabCat_Conjunction, Colors.Black);
case "ctr":
return new VocabCategoryInfo(R.VocabCat_Counter, Colors.Black);
case "derog":
return new VocabCategoryInfo(R.VocabCat_Derogatory, Colors.Black);
case "eK":
return new VocabCategoryInfo(R.VocabCat_ExclusivelyKanji, Colors.Black);
case "ek":
return new VocabCategoryInfo(R.VocabCat_ExclusivelyKana, Colors.Black);
case "exp":
return new VocabCategoryInfo(R.VocabCat_Expression, Colors.Black);
case "fam":
return new VocabCategoryInfo(R.VocabCat_Familiar, Colors.Black);
case "fem":
return new VocabCategoryInfo(R.VocabCat_Female, Colors.Black);
case "food":
return new VocabCategoryInfo(R.VocabCat_Food, Colors.Black);
case "geom":
return new VocabCategoryInfo(R.VocabCat_Geometry, Colors.Black);
case "gikun":
return new VocabCategoryInfo(R.VocabCat_Gikun, Colors.Black);
case "hon":
return new VocabCategoryInfo(R.VocabCat_Honorific, Colors.Black);
case "hum":
return new VocabCategoryInfo(R.VocabCat_Humble, Colors.Black);
case "iK":
return new VocabCategoryInfo(R.VocabCat_IrregularKanjiUsage, Colors.Black);
case "id":
return new VocabCategoryInfo(R.VocabCat_Idiomatic, Colors.Black);
case "ik":
return new VocabCategoryInfo(R.VocabCat_IrregularKanaUsage, Colors.Black);
case "int":
return new VocabCategoryInfo(R.VocabCat_Interjection, Colors.Black);
case "io":
return new VocabCategoryInfo(R.VocabCat_IrregularOkuriganaUsage, Colors.Black);
case "iv":
return new VocabCategoryInfo(R.VocabCat_IrregularVerb, Colors.Black);
case "ling":
return new VocabCategoryInfo(R.VocabCat_Linguistics, Colors.Black);
case "m-sl":
return new VocabCategoryInfo(R.VocabCat_MangaSlang, Colors.Black);
case "male":
return new VocabCategoryInfo(R.VocabCat_Male, Colors.Black);
case "male-sl":
return new VocabCategoryInfo(R.VocabCat_MaleSlang, Colors.Black);
case "math":
return new VocabCategoryInfo(R.VocabCat_Mathematics, Colors.Black);
case "mil":
return new VocabCategoryInfo(R.VocabCat_Military, Colors.Black);
case "n":
return new VocabCategoryInfo(R.VocabCat_Noun, Colors.Black);
case "n-adv":
return new VocabCategoryInfo(R.VocabCat_AdverbialNoun, Colors.Black);
case "n-suf":
return new VocabCategoryInfo(R.VocabCat_SuffixNoun, Colors.Black);
case "n-pref":
return new VocabCategoryInfo(R.VocabCat_PrefixNoun, Colors.Black);
case "n-t":
return new VocabCategoryInfo(R.VocabCat_TemporalNoun, Colors.Black);
case "num":
return new VocabCategoryInfo(R.VocabCat_Numeric, Colors.Black);
case "oK":
return new VocabCategoryInfo(R.VocabCat_OutdatedKanji, Colors.Black);
case "obs":
return new VocabCategoryInfo(R.VocabCat_Obsolete, Colors.Black);
case "obsc":
return new VocabCategoryInfo(R.VocabCat_Obscure, Colors.Black);
case "ok":
return new VocabCategoryInfo(R.VocabCat_OutdatedKana, Colors.Black);
case "oik":
return new VocabCategoryInfo(R.VocabCat_OldOrIrregularKana, Colors.Black);
case "on-mim":
return new VocabCategoryInfo(R.VocabCat_Onomatopoeic, Colors.Black);
case "pn":
return new VocabCategoryInfo(R.VocabCat_Pronoun, Colors.Black);
case "poet":
return new VocabCategoryInfo(R.VocabCat_Poetical, Colors.Black);
case "pol":
return new VocabCategoryInfo(R.VocabCat_Polite, Colors.Black);
case "pref":
return new VocabCategoryInfo(R.VocabCat_Prefix, Colors.Black);
case "proverb":
return new VocabCategoryInfo(R.VocabCat_Proverb, Colors.Black);
case "prt":
return new VocabCategoryInfo(R.VocabCat_Particle, Colors.Black);
case "physics":
return new VocabCategoryInfo(R.VocabCat_Physics, Colors.Black);
case "rare":
return new VocabCategoryInfo(R.VocabCat_Rare, Colors.Black);
case "sens":
return new VocabCategoryInfo(R.VocabCat_Sensitive, Colors.Black);
case "sl":
return new VocabCategoryInfo(R.VocabCat_Slang, Colors.Black);
case "suf":
return new VocabCategoryInfo(R.VocabCat_Suffix, Colors.Black);
case "uK":
return new VocabCategoryInfo(R.VocabCat_UsuallyKanji, Colors.Black);
case "uk":
return new VocabCategoryInfo(R.VocabCat_UsuallyKana, Colors.Black);
case "v1":
return new VocabCategoryInfo(R.VocabCat_IchidanVerb, Colors.Black);
case "v2a-s":
return new VocabCategoryInfo(R.VocabCat_NidanVerbU, Colors.Black);
case "v4h":
return new VocabCategoryInfo(R.VocabCat_YodanVerbFu, Colors.Black);
case "v4r":
return new VocabCategoryInfo(R.VocabCat_YodanVerbRu, Colors.Black);
case "v5":
return new VocabCategoryInfo(R.VocabCat_GodanVerb, Colors.Black);
case "v5aru":
return new VocabCategoryInfo(R.VocabCat_GodanVerbAru, Colors.Black);
case "v5b":
return new VocabCategoryInfo(R.VocabCat_GodanVerbBu, Colors.Black);
case "v5g":
return new VocabCategoryInfo(R.VocabCat_GodanVerbGu, Colors.Black);
case "v5k":
return new VocabCategoryInfo(R.VocabCat_GodanVerbKu, Colors.Black);
case "v5k-s":
return new VocabCategoryInfo(R.VocabCat_GodanVerbIku, Colors.Black);
case "v5m":
return new VocabCategoryInfo(R.VocabCat_GodanVerbMu, Colors.Black);
case "v5n":
return new VocabCategoryInfo(R.VocabCat_GodanVerbNu, Colors.Black);
case "v5r":
return new VocabCategoryInfo(R.VocabCat_GodanVerbRu, Colors.Black);
case "v5r-i":
return new VocabCategoryInfo(R.VocabCat_GodanVerbRuIrregular, Colors.Black);
case "v5s":
return new VocabCategoryInfo(R.VocabCat_GodanVerbSu, Colors.Black);
case "v5t":
return new VocabCategoryInfo(R.VocabCat_GodanVerbTsu, Colors.Black);
case "v5u":
return new VocabCategoryInfo(R.VocabCat_GodanVerbU, Colors.Black);
case "v5u-s":
return new VocabCategoryInfo(R.VocabCat_GodanVerbUIrregular, Colors.Black);
case "v5uru":
return new VocabCategoryInfo(R.VocabCat_GodanVerbUru, Colors.Black);
case "vz":
return new VocabCategoryInfo(R.VocabCat_IchidanVerbZuru, Colors.Black);
case "vi":
return new VocabCategoryInfo(R.VocabCat_IntransitiveVerb, Colors.Black);
case "vk":
return new VocabCategoryInfo(R.VocabCat_KuruVerb, Colors.Black);
case "vn":
return new VocabCategoryInfo(R.VocabCat_NuVerb, Colors.Black);
case "vr":
return new VocabCategoryInfo(R.VocabCat_RuVerb, Colors.Black);
case "vs":
return new VocabCategoryInfo(R.VocabCat_SuruVerbNoun, Colors.Black);
case "vs-c":
return new VocabCategoryInfo(R.VocabCat_SuVerb, Colors.Black);
case "vs-s":
return new VocabCategoryInfo(R.VocabCat_SuruVerb, Colors.Black);
case "vs-i":
return new VocabCategoryInfo(R.VocabCat_SuruVerbIrregular, Colors.Black);
case "kyb":
return new VocabCategoryInfo(R.VocabCat_KyotoBen, Colors.Black);
case "osb":
return new VocabCategoryInfo(R.VocabCat_OsakaBen, Colors.Black);
case "ksb":
return new VocabCategoryInfo(R.VocabCat_KansaiBen, Colors.Black);
case "ktb":
return new VocabCategoryInfo(R.VocabCat_KantouBen, Colors.Black);
case "tsb":
return new VocabCategoryInfo(R.VocabCat_TosaBen, Colors.Black);
case "thb":
return new VocabCategoryInfo(R.VocabCat_TouhokuBen, Colors.Black);
case "tsug":
return new VocabCategoryInfo(R.VocabCat_TsugaruBen, Colors.Black);
case "kyu":
return new VocabCategoryInfo(R.VocabCat_KyuushuuBen, Colors.Black);
case "rkb":
return new VocabCategoryInfo(R.VocabCat_RyuukyuuBen, Colors.Black);
case "nab":
return new VocabCategoryInfo(R.VocabCat_NaganoBen, Colors.Black);
case "hob":
return new VocabCategoryInfo(R.VocabCat_HokkaidoBen, Colors.Black);
case "vt":
return new VocabCategoryInfo(R.VocabCat_TransitiveVerb, Colors.Black);
case "vulg":
return new VocabCategoryInfo(R.VocabCat_Vulgar, Colors.Black);
case "adj-kari":
return new VocabCategoryInfo(R.VocabCat_KariAdjective, Colors.Black);
case "adj-ku":
return new VocabCategoryInfo(R.VocabCat_KuAdjective, Colors.Black);
case "adj-shiku":
return new VocabCategoryInfo(R.VocabCat_ShikuAdjective, Colors.Black);
case "adj-nari":
return new VocabCategoryInfo(R.VocabCat_NariAdjective, Colors.Black);
case "n-pr":
return new VocabCategoryInfo(R.VocabCat_ProperNoun, Colors.Black);
case "v-unspec":
return new VocabCategoryInfo(R.VocabCat_Verb, Colors.Black);
case "v4k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v4g":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v4s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v4t":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v4n":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v4b":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v4m":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2k-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2g-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2t-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2d-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2h-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2b-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2m-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2y-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2r-k":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2k-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2g-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2s-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2z-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2t-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2d-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2n-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2h-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2b-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2m-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2y-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2r-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "v2w-s":
return new VocabCategoryInfo(R.VocabCat_ArchaicVerb, Colors.Black);
case "archit":
return new VocabCategoryInfo(R.VocabCat_Architecture, Colors.Black);
case "anat":
return new VocabCategoryInfo(R.VocabCat_Anatomy, Colors.Black);
case "astron":
return new VocabCategoryInfo(R.VocabCat_Astronomy, Colors.Black);
case "baseb":
return new VocabCategoryInfo(R.VocabCat_Baseball, Colors.Black);
case "biol":
return new VocabCategoryInfo(R.VocabCat_Biology, Colors.Black);
case "bot":
return new VocabCategoryInfo(R.VocabCat_Botany, Colors.Black);
case "bus":
return new VocabCategoryInfo(R.VocabCat_Business, Colors.Black);
case "econ":
return new VocabCategoryInfo(R.VocabCat_Economy, Colors.Black);
case "engr":
return new VocabCategoryInfo(R.VocabCat_Engineering, Colors.Black);
case "finc":
return new VocabCategoryInfo(R.VocabCat_Finance, Colors.Black);
case "geol":
return new VocabCategoryInfo(R.VocabCat_Geology, Colors.Black);
case "law":
return new VocabCategoryInfo(R.VocabCat_Law, Colors.Black);
case "med":
return new VocabCategoryInfo(R.VocabCat_Medicine, Colors.Black);
case "music":
return new VocabCategoryInfo(R.VocabCat_Music, Colors.Black);
case "Shinto":
return new VocabCategoryInfo(R.VocabCat_Shinto, Colors.Black);
case "sports":
return new VocabCategoryInfo(R.VocabCat_Sports, Colors.Black);
case "sumo":
return new VocabCategoryInfo(R.VocabCat_Sumo, Colors.Black);
case "zool":
return new VocabCategoryInfo(R.VocabCat_Zoology, Colors.Black);
case "joc":
return new VocabCategoryInfo(R.VocabCat_Jocular, Colors.Black);
default:
return null;
}
}
/// <summary>
/// Builds and returns the vocab filter SQL clauses from the given
/// filters.
/// </summary>
internal string BuildVocabFilterClauses(List <DaoParameter> parameters,
KanjiEntity kanji,
string readingFilter, string meaningFilter, VocabCategory categoryFilter,
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("v.{0}=@jlpt ",
SqlHelper.Field_Vocab_JlptLevel);
parameters.Add(new DaoParameter("@jlpt", jlptLevel));
}
else if (jlptLevel < minJlptLevel)
{
sqlJlptFilter = string.Format("v.{0} IS NULL ",
SqlHelper.Field_Vocab_JlptLevel);
}
string sqlWkFilter = string.Empty;
if (wkLevel >= minWkLevel && wkLevel <= maxWkLevel)
{
sqlWkFilter = string.Format("v.{0}=@wk ",
SqlHelper.Field_Vocab_WaniKaniLevel);
parameters.Add(new DaoParameter("@wk", wkLevel));
}
else if (wkLevel > maxWkLevel)
{
sqlWkFilter = string.Format("v.{0} IS NULL ",
SqlHelper.Field_Vocab_WaniKaniLevel);
}
string sqlKanjiFilter = string.Empty;
if (kanji != null)
{
// Build the sql kanji filter clause.
// Example with the kanji '達' :
//
// WHERE v.KanjiWriting LIKE '%達%'
sqlKanjiFilter = string.Format("v.{0} LIKE @kanji ",
SqlHelper.Field_Vocab_KanjiWriting);
parameters.Add(new DaoParameter("@kanji", "%" + kanji.Character + "%"));
}
string sqlReadingFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(readingFilter))
{
// Build the sql reading filter clause.
// Example with readingFilter="かな" :
//
// WHERE v.KanaWriting LIKE '%かな%' OR
// v.KanjiWriting LIKE '%かな%'
sqlReadingFilter = string.Format("(v.{0} LIKE @reading OR v.{1} LIKE @reading) ",
SqlHelper.Field_Vocab_KanaWriting,
SqlHelper.Field_Vocab_KanjiWriting);
parameters.Add(new DaoParameter("@reading", "%" + readingFilter + "%"));
}
string sqlSharedJoins = string.Empty;
string sqlMeaningFilterJoins = string.Empty;
string sqlMeaningFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(meaningFilter))
{
// Build the sql meaning filter clause and join clauses.
// Example of filter clause with meaningFilter="test" :
//
// WHERE vm.Meaning LIKE '%test%'
// First, build the join clause if it does not already exist. This will be included before the filters.
if (string.IsNullOrEmpty(sqlSharedJoins))
{
sqlSharedJoins = joinString_VocabEntity_VocabMeaning;
}
sqlMeaningFilterJoins = joinString_VocabMeaningSet;
// Once the join clauses are done, build the filter itself.
sqlMeaningFilter = string.Format("vm.{0} LIKE @meaning ",
SqlHelper.Field_VocabMeaning_Meaning);
parameters.Add(new DaoParameter("@meaning", "%" + meaningFilter + "%"));
}
string sqlCategoryFilterJoins = string.Empty;
string sqlCategoryFilter = string.Empty;
if (categoryFilter != null)
{
// Build the filter clause for the vocab category.
// Note that the category is actually associated either with the vocab itself or with a MEANING,
// so we need to grab any vocab which itself has said category, or of which ONE OF THE MEANINGS
// is of said category.
// Example of filter clause with category.ID=42 :
//
// WHERE vc.Categories_ID=42 OR mc.Categories_ID=42
if (string.IsNullOrEmpty(sqlSharedJoins))
{
sqlSharedJoins = joinString_VocabEntity_VocabMeaning;
}
/* TODO: Currently, only the meanings are checked for category matches, not vocab items themselves.
* This is because of several reasons:
*
* 1) In the current database, not a single vocab has a category attached.
* Only meanings do.
* 2) Saving some performance by not doing a check that does not do anything.
*
* This does mean that this query must be changed if vocab items ever
* get a category attached to them.
*
* Actually, scratch that. Ateji DO have a category attached to the vocab themselves.
*/
string subQuery = string.Format("(SELECT m.{0} FROM {1} m WHERE m.{2}=@cat)",
SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId,
SqlHelper.Table_VocabMeaning_VocabCategory,
SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId);
// First, build the join clause if it does not already exist. This will be included before the filters.
sqlCategoryFilterJoins = string.IsNullOrEmpty(sqlMeaningFilterJoins) ? joinString_VocabMeaningSet : null;
// Once the join clauses are done, build the filter itself.
sqlCategoryFilter = string.Format("vm.{0} IN {1} ",
SqlHelper.Field_VocabMeaning_Id, subQuery);
parameters.Add(new DaoParameter("@cat", categoryFilter.ID));
}
string[] sqlArgs =
{
sqlSharedJoins,
sqlMeaningFilterJoins,
sqlCategoryFilterJoins,
sqlJlptFilter,
sqlWkFilter,
sqlKanjiFilter,
sqlReadingFilter,
sqlMeaningFilter,
sqlCategoryFilter
};
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));
}
/// <summary>
/// See <see cref="Kanji.Database.Dao.VocabDao.GetFilteredVocab"/>.
/// Returns the results count.
/// </summary>
public long GetFilteredVocabCount(KanjiEntity kanji,
string readingFilter, string meaningFilter, VocabCategory categoryFilter, int jlptLevel, int wkLevel)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilterClauses = BuildVocabFilterClauses(parameters, kanji,
readingFilter, meaningFilter, categoryFilter, jlptLevel, wkLevel);
using (DaoConnection connection
= DaoConnection.Open(DaoConnectionEnum.KanjiDatabase))
{
return((long)connection.QueryScalar(
string.Format("SELECT count(1) FROM {0} v {1}",
SqlHelper.Table_Vocab,
sqlFilterClauses),
parameters.ToArray()));
}
}
/// <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();
}
}
}
/// <summary>
/// Builds and returns the vocab filter SQL clauses from the given
/// filters.
/// </summary>
internal string BuildVocabFilterClauses(List <DaoParameter> parameters,
KanjiEntity kanji,
string readingFilter, string meaningFilter, VocabCategory categoryFilter,
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("v.{0}=@jlpt ",
SqlHelper.Field_Vocab_JlptLevel);
parameters.Add(new DaoParameter("@jlpt", jlptLevel));
}
else if (jlptLevel < minJlptLevel)
{
sqlJlptFilter = string.Format("v.{0} IS NULL ",
SqlHelper.Field_Vocab_JlptLevel);
}
string sqlWkFilter = string.Empty;
if (wkLevel >= minWkLevel && wkLevel <= maxWkLevel)
{
sqlWkFilter = string.Format("v.{0}=@wk ",
SqlHelper.Field_Vocab_WaniKaniLevel);
parameters.Add(new DaoParameter("@wk", wkLevel));
}
else if (wkLevel > maxWkLevel)
{
sqlWkFilter = string.Format("v.{0} IS NULL ",
SqlHelper.Field_Vocab_WaniKaniLevel);
}
string sqlKanjiFilter = string.Empty;
if (kanji != null)
{
// Build the sql kanji filter clause.
// Example with the kanji '達' :
//
// WHERE v.KanjiWriting LIKE '%達%'
sqlKanjiFilter = string.Format("v.{0} LIKE @kanji ",
SqlHelper.Field_Vocab_KanjiWriting);
parameters.Add(new DaoParameter("@kanji", "%" + kanji.Character + "%"));
}
string sqlReadingFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(readingFilter))
{
// Build the sql reading filter clause.
// Example with readingFilter="かな" :
//
// WHERE v.KanaWriting LIKE '%かな%' OR
// v.KanjiWriting LIKE '%かな%'
sqlReadingFilter = string.Format("(v.{0} LIKE @reading OR v.{1} LIKE @reading) ",
SqlHelper.Field_Vocab_KanaWriting,
SqlHelper.Field_Vocab_KanjiWriting);
parameters.Add(new DaoParameter("@reading", "%" + readingFilter + "%"));
}
string sqlSharedJoins = string.Empty;
string sqlMeaningFilterJoins = string.Empty;
string sqlMeaningFilter = string.Empty;
if (!string.IsNullOrWhiteSpace(meaningFilter))
{
// Build the sql meaning filter clause and join clauses.
// Example of filter clause with meaningFilter="test" :
//
// WHERE vm.Meaning LIKE '%test%'
// First, build the join clause. This will be included before the filters.
if (string.IsNullOrEmpty(sqlSharedJoins))
{
sqlSharedJoins = string.Format("JOIN {0} vvm ON (vvm.{1}=v.{2}) ",
SqlHelper.Table_Vocab_VocabMeaning,
SqlHelper.Field_Vocab_VocabMeaning_VocabId,
SqlHelper.Field_Vocab_Id);
}
sqlMeaningFilterJoins = string.Format("JOIN {0} vm ON (vm.{1}=vvm.{2}) ",
SqlHelper.Table_VocabMeaning,
SqlHelper.Field_VocabMeaning_Id,
SqlHelper.Field_Vocab_VocabMeaning_VocabMeaningId);
// Ouch... it looks kinda like an obfuscated string... Sorry.
// Basically, you just join the vocab to its meaning entries.
// Once the join clauses are done, build the filter itself.
sqlMeaningFilter = string.Format("vm.{0} LIKE @meaning ",
SqlHelper.Field_VocabMeaning_Meaning);
parameters.Add(new DaoParameter("@meaning", "%" + meaningFilter + "%"));
}
string sqlCategoryFilterJoins = string.Empty;
string sqlCategoryFilter = string.Empty;
if (categoryFilter != null)
{
// Build the filter clause for the vocab category.
// Note that the category is actually associated either with the vocab itself or with a MEANING,
// so we need to grab any vocab which itself has said category, or of which ONE OF THE MEANINGS
// is of said category.
// Example of filter clause with category.ID=42 :
//
// WHERE vc.Categories_ID=42 OR mc.Categories_ID=42
if (string.IsNullOrEmpty(sqlSharedJoins))
{
sqlSharedJoins = string.Format("JOIN {0} vvm ON (vvm.{1}=v.{2}) ",
SqlHelper.Table_Vocab_VocabMeaning,
SqlHelper.Field_Vocab_VocabMeaning_VocabId,
SqlHelper.Field_Vocab_Id);
}
sqlCategoryFilterJoins = string.Format(
"JOIN {0} vc ON (vc.{1}=v.{2}) JOIN {3} mc ON (mc.{4}=vvm.{5}) ",
SqlHelper.Table_VocabCategory_Vocab,
SqlHelper.Field_VocabCategory_Vocab_VocabId,
SqlHelper.Field_VocabCategory_Id,
SqlHelper.Table_VocabMeaning_VocabCategory,
SqlHelper.Field_VocabMeaning_VocabCategory_VocabMeaningId,
SqlHelper.Field_Vocab_VocabMeaning_VocabMeaningId);
sqlCategoryFilter = string.Format("(vc.{0}=@cat OR mc.{1}=@cat) ",
SqlHelper.Field_VocabCategory_Vocab_VocabCategoryId,
SqlHelper.Field_VocabMeaning_VocabCategory_VocabCategoryId);
parameters.Add(new DaoParameter("@cat", categoryFilter.ID));
}
string[] sqlArgs =
{
sqlSharedJoins,
sqlMeaningFilterJoins,
sqlCategoryFilterJoins,
sqlJlptFilter,
sqlWkFilter,
sqlKanjiFilter,
sqlReadingFilter,
sqlMeaningFilter,
sqlCategoryFilter
};
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));
}