/// <summary>
/// Gets all kanji with minimal info.
/// </summary>
/// <returns>All kanji with minimal info.</returns>
public IEnumerable <KanjiEntity> GetAllKanji()
{
DaoConnection connection = null;
try
{
// Create and open synchronously the primary Kanji connection.
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
string.Format("SELECT * FROM {0}", SqlHelper.Table_Kanji));
KanjiBuilder kanjiBuilder = new KanjiBuilder();
foreach (NameValueCollection nvcKanji in results)
{
KanjiEntity kanji = kanjiBuilder.BuildEntity(nvcKanji, null);
yield return(kanji);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Gets the number of reviews due for the current date.
/// </summary>
/// <returns>Number of reviews due for the current date.</returns>
public long GetReviewsCount()
{
long result = -1;
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
result = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date",
new DaoParameter("@date", DateTime.UtcNow.Ticks));
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// Retrieves the category with the given label.
/// </summary>
/// <param name="label">Label of the category to retrieve.</param>
/// <returns>Matching category if any. Null otherwise.</returns>
public VocabCategory GetCategoryByLabel(string label)
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
string.Format("SELECT * FROM {0} WHERE {1}=@label",
SqlHelper.Table_VocabCategory,
SqlHelper.Field_VocabCategory_Label),
new DaoParameter("@label", label));
VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder();
if (results.Any())
{
return(categoryBuilder.BuildEntity(results.First(), null));
}
return(null);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Creates and opens a new connection.
/// </summary>
/// <param name="endpoint">Connection endpoint.</param>
/// <returns>Newly created connection in an open state.</returns>
public static DaoConnection Open(DaoConnectionEnum endpoint)
{
DaoConnection connection = new DaoConnection(endpoint);
connection.Open();
return(connection);
}
/// <summary>
/// Retrieves all radicals.
/// </summary>
public IEnumerable <RadicalEntity> GetAllRadicals()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
string.Format("SELECT * FROM {0}", SqlHelper.Table_Radical));
RadicalBuilder radicalBuilder = new RadicalBuilder();
foreach (NameValueCollection nvcRadical in results)
{
RadicalEntity radical = radicalBuilder.BuildEntity(nvcRadical, null);
IncludeKanji(connection, radical);
yield return(radical);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Gets all reviews due for the current date.
/// </summary>
/// <returns>Reviews due for the current date.</returns>
public IEnumerable <SrsEntry> GetReviews()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
"SELECT * FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date"
+ " ORDER BY RANDOM()",
new DaoParameter("@date", DateTime.UtcNow.Ticks));
SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder();
foreach (NameValueCollection nvcEntry in results)
{
yield return(srsEntryBuilder.BuildEntity(nvcEntry, null));
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Applies the given value to the given field of the given entries.
/// </summary>
/// <param name="entities">Entries to edit.</param>
/// <param name="fieldName">Name of the field to set.</param>
/// <param name="value">Value to set for all entities.</param>
/// <returns>Number of entities edited.</returns>
private long BulkEditStringField(IEnumerable <SrsEntry> entities,
string fieldName, string value)
{
if (!entities.Any())
{
return(0);
}
DaoConnection connection = null;
long result = -1;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
int i = 0;
result = 0;
while (i < entities.Count())
{
List <DaoParameter> parameters = new List <DaoParameter>();
string inStatement = string.Empty;
int nMax = Math.Min(entities.Count(), i + BulkBatchCount);
for (int n = i; n < nMax; n++)
{
SrsEntry entry = entities.ElementAt(n);
string paramName = "@p" + entry.ID;
inStatement += paramName + ",";
parameters.Add(new DaoParameter(paramName, entry.ID));
}
inStatement = inStatement.TrimEnd(new char[] { ',' });
// Add the "value" parameter.
parameters.Add(new DaoParameter("@Value", value));
// Add the "LastUpdateDate" parameter.
parameters.Add(new DaoParameter("@LastUpdateDate", DateTime.UtcNow.Ticks));
// Execute the query.
result += connection.ExecuteNonQuery("UPDATE " + SqlHelper.Table_SrsEntry
+ " SET " + fieldName + "=@Value, " + SqlHelper.Field_SrsEntry_LastUpdateDate
+ "=@LastUpdateDate " + "WHERE " + SqlHelper.Field_SrsEntry_Id
+ " IN (" + inStatement + ")",
parameters.ToArray());
i = nMax;
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <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>
/// 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, int jlptLevel, int wkLevel)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter,
meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter,
jlptLevel, wkLevel);
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(string.Format(
"SELECT * FROM {0} k {1}ORDER BY (k.{2} IS NULL),(k.{2});",
SqlHelper.Table_Kanji,
sqlFilter,
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();
}
}
}
/// <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();
}
}
}
/// <summary>
/// Removes all the given entities from the database.
/// </summary>
/// <param name="entities">Entities to delete.</param>
/// <returns>Number of items successfuly deleted.</returns>
public long BulkDelete(IEnumerable <SrsEntry> entities)
{
if (!entities.Any())
{
return(0);
}
DaoConnection connection = null;
long result = -1;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
int i = 0;
result = 0;
while (i < entities.Count())
{
List <DaoParameter> parameters = new List <DaoParameter>();
string inStatement = string.Empty;
int nMax = Math.Min(entities.Count(), i + BulkBatchCount);
for (int n = i; n < nMax; n++)
{
SrsEntry entry = entities.ElementAt(n);
string paramName = "@p" + entry.ID;
inStatement += paramName + ",";
parameters.Add(new DaoParameter(paramName, entry.ID));
}
inStatement = inStatement.TrimEnd(new char[] { ',' });
// Execute the query.
result += connection.ExecuteNonQuery("DELETE FROM " + SqlHelper.Table_SrsEntry
+ " WHERE " + SqlHelper.Field_SrsEntry_Id + " IN (" + inStatement + ")",
parameters.ToArray());
i = nMax;
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <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()));
}
}
public IEnumerable <NameValueCollection> CustomQuery(string query)
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
return(connection.Query(query).ToList());
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Gets a filtered set of SRS entries.
/// </summary>
/// <param name="filterClauses">Filter clauses.</param>
/// <returns>Filtered SRS entries.</returns>
public IEnumerable <SrsEntry> GetFilteredItems(FilterClause[] filterClauses)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string whereClause = string.Empty;
bool isFiltered = false;
foreach (FilterClause clause in filterClauses)
{
if (clause != null)
{
string sqlClause = clause.GetSqlWhereClause(!isFiltered, parameters);
if (!string.IsNullOrEmpty(sqlClause))
{
whereClause += sqlClause + " ";
isFiltered = true;
}
}
}
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
"SELECT * FROM " + SqlHelper.Table_SrsEntry + " se "
+ whereClause
+ "ORDER BY (se." + SqlHelper.Field_SrsEntry_CreationDate + ") DESC",
parameters.ToArray());
SrsEntryBuilder srsEntryBuilder = new SrsEntryBuilder();
foreach (NameValueCollection nvcEntry in results)
{
yield return(srsEntryBuilder.BuildEntity(nvcEntry, null));
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public IEnumerable <NameValueCollection> CustomQuery(string query)
{
DaoConnection connection = null;
try
{
// Create and open synchronously the primary Kanji connection.
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
return(connection.Query(query).ToList());
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <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, int jlptLevel, int wkLevel)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilter = BuildKanjiFilterClauses(parameters, radicals, textFilter,
meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter,
jlptLevel, wkLevel);
using (DaoConnection connection =
DaoConnection.Open(DaoConnectionEnum.KanjiDatabase))
{
return((long)connection.QueryScalar(
string.Format("SELECT COUNT(1) FROM {0} k {1}",
SqlHelper.Table_Kanji,
sqlFilter),
parameters.ToArray()));
}
}
//public KanjiStrokes GetKanjiStrokes(long id)
//{
// KanjiStrokes result = null;
// DaoConnection connection = null;
// try
// {
// // Create and open synchronously the primary Kanji connection.
// connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
// // FILTERS COMPUTED.
// // Execute the final request.
// IEnumerable<NameValueCollection> results = connection.Query(
// "SELECT * "
// + "FROM " + SqlHelper.Table_KanjiStrokes + " ks "
// + "WHERE ks." + SqlHelper.Field_KanjiStrokes_Id + "=@ks;",
// new DaoParameter("@ks", id));
// if (results.Any())
// {
// KanjiStrokesBuilder builder = new KanjiStrokesBuilder();
// result = builder.BuildEntity(results.First(), null);
// }
// }
// finally
// {
// if (connection != null)
// {
// connection.Dispose();
// }
// }
// return result;
//}
public KanjiStrokes GetKanjiStrokes(long id)
{
KanjiStrokes result = new KanjiStrokes();
result.ID = id;
result.FramesSvg = new byte[0];
DaoConnection connection = null;
SQLiteDataReader reader = null;
try
{
// Create and open synchronously the primary Kanji connection.
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
reader = connection.QueryDataReader(
string.Format("SELECT {0} FROM {1} WHERE {2}=@id;",
SqlHelper.Field_KanjiStrokes_FramesSvg,
SqlHelper.Table_KanjiStrokes,
SqlHelper.Field_KanjiStrokes_Id),
new DaoParameter("@id", id));
while (reader.Read())
{
result.FramesSvg = GetBytes(reader);
}
}
finally
{
if (reader != null)
{
reader.Close();
reader.Dispose();
}
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <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, int jlptLevel, int wkLevel)
{
// Compute the filters.
List <DaoParameter> parameters = new List <DaoParameter>();
string sqlFilter = KanjiDao.BuildKanjiFilterClauses(parameters, radicals, textFilter,
meaningFilter, anyReadingFilter, onYomiFilter, kunYomiFilter, nanoriFilter,
jlptLevel, wkLevel);
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
string.Format(
"SELECT DISTINCT ckr.{0} Id " + "FROM {1} k JOIN {2} ckr " + "ON (ckr.{3}=k.{4}) {5}",
SqlHelper.Field_Kanji_Radical_RadicalId,
SqlHelper.Table_Kanji,
SqlHelper.Table_Kanji_Radical,
SqlHelper.Field_Kanji_Radical_KanjiId,
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>
/// Gets the number of items matching the given filter clauses.
/// </summary>
/// <param name="filterClauses">Filter clauses to match.</param>
/// <returns>Number of items matching the filter clauses.</returns>
public long GetFilteredItemsCount(FilterClause[] filterClauses)
{
List <DaoParameter> parameters = new List <DaoParameter>();
string whereClause = string.Empty;
bool isFiltered = false;
foreach (FilterClause clause in filterClauses)
{
if (clause != null)
{
string sqlClause = clause.GetSqlWhereClause(!isFiltered, parameters);
if (!string.IsNullOrEmpty(sqlClause))
{
whereClause += sqlClause + " ";
isFiltered = true;
}
}
}
DaoConnection connection = null;
long result = -1;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
result = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se "
+ whereClause,
parameters.ToArray());
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <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>
/// Gets a similar item (same kanji reading and type) if found.
/// </summary>
/// <param name="entry">Reference entry.</param>
/// <returns>The first matching item if found. Null otherwise.</returns>
public SrsEntry GetSimilarItem(SrsEntry entry)
{
SrsEntry r = null;
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
List <DaoParameter> parameters = new List <DaoParameter>();
string request = "SELECT * FROM " + SqlHelper.Table_SrsEntry + " se WHERE se.";
if (!string.IsNullOrEmpty(entry.AssociatedKanji))
{
request += SqlHelper.Field_SrsEntry_AssociatedKanji;
parameters.Add(new DaoParameter("@kr", entry.AssociatedKanji));
}
else
{
request += SqlHelper.Field_SrsEntry_AssociatedVocab;
parameters.Add(new DaoParameter("@kr", entry.AssociatedVocab));
}
request += "=@kr";
NameValueCollection result = connection.Query(request, parameters.ToArray()).FirstOrDefault();
if (result != null)
{
SrsEntryBuilder builder = new SrsEntryBuilder();
r = builder.BuildEntity(result, null);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(r);
}
public void SelectAllVocab()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0}", SqlHelper.Table_Vocab));
foreach (NameValueCollection nvcVocab in vocabs)
{
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Gets the first kanji that matches the given character.
/// </summary>
/// <param name="character">Character to match.</param>
/// <returns>First kanji matching the given character.
/// Null if nothing was found.</returns>
public KanjiEntity GetFirstMatchingKanji(string character)
{
KanjiEntity result = null;
DaoConnection connection = null;
try
{
// Create and open synchronously the primary Kanji connection.
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
// FILTERS COMPUTED.
// Execute the final request.
IEnumerable <NameValueCollection> results = connection.Query(string.Format(
"SELECT * FROM {0} k WHERE k.{1}=@k ORDER BY (k.{2} IS NULL),(k.{2});",
SqlHelper.Table_Kanji,
SqlHelper.Field_Kanji_Character,
SqlHelper.Field_Kanji_MostUsedRank),
new DaoParameter("@k", character));
if (results.Any())
{
result = new KanjiBuilder()
.BuildEntity(results.First(), null);
IncludeKanjiMeanings(connection, result);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// Retrieves all vocab categories.
/// </summary>
/// <returns>All vocab categories.</returns>
public IEnumerable <VocabCategory> GetAllCategories()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> results = connection.Query(
string.Format("SELECT * FROM {0}", SqlHelper.Table_VocabCategory));
VocabCategoryBuilder categoryBuilder = new VocabCategoryBuilder();
foreach (NameValueCollection nvcCategory in results)
{
yield return(categoryBuilder.BuildEntity(nvcCategory, null));
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
public IEnumerable <VocabEntity> GetAllVocab()
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.KanjiDatabase);
IEnumerable <NameValueCollection> vocabs = connection.Query(
string.Format("SELECT * FROM {0}", SqlHelper.Table_Vocab));
VocabBuilder builder = new VocabBuilder();
foreach (NameValueCollection nvcVocab in vocabs)
{
yield return(builder.BuildEntity(nvcVocab, null));
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Updates the review date for all given entities.
/// The review date is assumed already modified in the entity.
/// </summary>
/// <param name="entities">Entities to update.</param>
/// <returns>Number of entities updated.</returns>
public long BulkEditReviewDate(IEnumerable <SrsEntry> entities)
{
if (!entities.Any())
{
return(0);
}
DaoConnection connection = null;
long result = -1;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
result = 0;
foreach (SrsEntry entry in entities)
{
// Execute the query.
result += connection.ExecuteNonQuery("UPDATE " + SqlHelper.Table_SrsEntry
+ " SET " + SqlHelper.Field_SrsEntry_NextAnswerDate + "=@Value, " + SqlHelper.Field_SrsEntry_LastUpdateDate
+ "=@LastUpdateDate WHERE " + SqlHelper.Field_SrsEntry_Id + "=@Id",
new DaoParameter("@Value", entry.NextAnswerDate.HasValue ? (object)entry.NextAnswerDate.Value.ToUniversalTime().Ticks : "null"),
new DaoParameter("@LastUpdateDate", DateTime.UtcNow.Ticks),
new DaoParameter("@Id", entry.ID));
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// Removes the entity from the database.
/// </summary>
/// <param name="entity">Entity to delete.</param>
/// <returns>True if the operation was successful. False otherwise.</returns>
public bool Delete(SrsEntry entity)
{
DaoConnection connection = null;
bool result = false;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
// Execute the query.
result = connection.ExecuteNonQuery("DELETE FROM " + SqlHelper.Table_SrsEntry
+ " WHERE " + SqlHelper.Field_SrsEntry_Id + "=@Id",
new DaoParameter("@Id", entity.ID)) == 1;
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// Inserts the given entity in the database.
/// Overrides the ID property of the given entity.
/// </summary>
/// <param name="entity">Entity to insert.</param>
public void Add(SrsEntry entity)
{
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
// Create a parameter list and two string builders that will
// be used to put the SQL request together.
List <DaoParameter> parameters = new List <DaoParameter>();
StringBuilder sqlQueryStart = new StringBuilder(
"INSERT INTO " + SqlHelper.Table_SrsEntry + "(");
StringBuilder sqlQueryEnd = new StringBuilder(
"VALUES(");
// CreationDate
if (entity.CreationDate != null)
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_CreationDate + ",");
sqlQueryEnd.Append("@CreationDate,");
parameters.Add(new DaoParameter(
"@CreationDate", entity.CreationDate.Value.ToUniversalTime().Ticks));
}
// NextAnswerDate
if (entity.NextAnswerDate != null)
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_NextAnswerDate + ",");
sqlQueryEnd.Append("@NextAnswerDate,");
parameters.Add(new DaoParameter(
"@NextAnswerDate", entity.NextAnswerDate.Value.ToUniversalTime().Ticks));
}
// Meanings
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_Meanings + ",");
sqlQueryEnd.Append("@Meanings,");
parameters.Add(new DaoParameter("@Meanings",
MultiValueFieldHelper.Trim(entity.Meanings ?? string.Empty)));
// Readings
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_Readings + ",");
sqlQueryEnd.Append("@Readings,");
parameters.Add(new DaoParameter("@Readings",
MultiValueFieldHelper.Trim(entity.Readings ?? string.Empty)));
// CurrentGrade
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_CurrentGrade + ",");
sqlQueryEnd.Append("@CurrentGrade,");
parameters.Add(new DaoParameter("@CurrentGrade", entity.CurrentGrade));
// FailureCount
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_FailureCount + ",");
sqlQueryEnd.Append("@FailureCount,");
parameters.Add(new DaoParameter("@FailureCount", entity.FailureCount));
// SuccessCount
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_SuccessCount + ",");
sqlQueryEnd.Append("@SuccessCount,");
parameters.Add(new DaoParameter("@SuccessCount", entity.SuccessCount));
// SuspensionDate
if (entity.SuspensionDate.HasValue)
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_SuspensionDate + ",");
sqlQueryEnd.Append("@SuspensionDate,");
parameters.Add(new DaoParameter("@SuspensionDate", entity.SuspensionDate.Value.ToUniversalTime().Ticks));
}
// AssociatedVocab
if (!string.IsNullOrWhiteSpace(entity.AssociatedVocab))
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_AssociatedVocab + ",");
sqlQueryEnd.Append("@AssociatedVocab,");
parameters.Add(new DaoParameter(
"@AssociatedVocab", entity.AssociatedVocab));
}
// AssociatedKanji
if (!string.IsNullOrWhiteSpace(entity.AssociatedKanji))
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_AssociatedKanji + ",");
sqlQueryEnd.Append("@AssociatedKanji,");
parameters.Add(new DaoParameter(
"@AssociatedKanji", entity.AssociatedKanji));
}
// MeaningNote
if (!string.IsNullOrWhiteSpace(entity.MeaningNote))
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_MeaningNote + ",");
sqlQueryEnd.Append("@MeaningNote,");
parameters.Add(new DaoParameter(
"@MeaningNote", entity.MeaningNote));
}
// ReadingNote
if (!string.IsNullOrWhiteSpace(entity.ReadingNote))
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_ReadingNote + ",");
sqlQueryEnd.Append("@ReadingNote,");
parameters.Add(new DaoParameter(
"@ReadingNote", entity.ReadingNote));
}
// Tags
if (!string.IsNullOrWhiteSpace(entity.Tags))
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_Tags + ",");
sqlQueryEnd.Append("@Tags,");
parameters.Add(new DaoParameter(
"@Tags", MultiValueFieldHelper.Trim(entity.Tags)));
}
// LastUpdateDate
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_LastUpdateDate + ",");
sqlQueryEnd.Append("@LastUpdateDate,");
parameters.Add(new DaoParameter(
"@LastUpdateDate", DateTime.UtcNow.Ticks));
// ServerId
if (entity.ServerId.HasValue)
{
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_ServerId + ",");
sqlQueryEnd.Append("@ServerId,");
parameters.Add(new DaoParameter(
"@ServerId", entity.ServerId));
}
// IsDeleted (because why not?)
sqlQueryStart.Append(SqlHelper.Field_SrsEntry_IsDeleted + ",");
sqlQueryEnd.Append("@IsDeleted,");
parameters.Add(new DaoParameter("@IsDeleted", entity.IsDeleted));
// We are done with the string builders.
// Bring the query pieces together.
string finalQuery =
sqlQueryStart.ToString().TrimEnd(new char[] { ',' }) + ") "
+ sqlQueryEnd.ToString().TrimEnd(new char[] { ',' }) + ")";
// Execute the query.
if (connection.ExecuteNonQuery(finalQuery, parameters.ToArray()) == 1)
{
// If the row was inserted, put the insert ID in the entity.
entity.ID = connection.GetLastInsertId();
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// Updates the given SRS entry.
/// </summary>
/// <param name="entity">Entity to update.</param>
/// <returns>True if the operation was sucessful. False otherwise.</returns>
public bool Update(SrsEntry entity)
{
DaoConnection connection = null;
bool result = false;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
// Create a parameter list and two string builders that will
// be used to put the SQL request together.
List <DaoParameter> parameters = new List <DaoParameter>();
StringBuilder sqlQueryBuilder = new StringBuilder(
"UPDATE " + SqlHelper.Table_SrsEntry + " SET ");
// NextAnswerDate
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_NextAnswerDate + "=");
if (entity.NextAnswerDate == null)
{
sqlQueryBuilder.Append("null");
}
else
{
sqlQueryBuilder.Append("@NextAnswerDate");
parameters.Add(new DaoParameter(
"@NextAnswerDate", entity.NextAnswerDate.Value.ToUniversalTime().Ticks));
}
sqlQueryBuilder.Append(",");
// Meanings
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_Meanings + "=@Meanings,");
parameters.Add(new DaoParameter("@Meanings",
MultiValueFieldHelper.Trim(entity.Meanings ?? string.Empty)));
// Readings
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_Readings + "=@Readings,");
parameters.Add(new DaoParameter("@Readings",
MultiValueFieldHelper.Trim(entity.Readings ?? string.Empty)));
// CurrentGrade
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_CurrentGrade + "=@CurrentGrade,");
parameters.Add(new DaoParameter("@CurrentGrade", entity.CurrentGrade));
// FailureCount
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_FailureCount + "=@FailureCount,");
parameters.Add(new DaoParameter("@FailureCount", entity.FailureCount));
// SuccessCount
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_SuccessCount + "=@SuccessCount,");
parameters.Add(new DaoParameter("@SuccessCount", entity.SuccessCount));
// SuspensionDate
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_SuspensionDate + "=");
if (entity.SuspensionDate == null)
{
sqlQueryBuilder.Append("null");
}
else
{
sqlQueryBuilder.Append("@SuspensionDate");
parameters.Add(new DaoParameter(
"@SuspensionDate", entity.SuspensionDate.Value.ToUniversalTime().Ticks));
}
sqlQueryBuilder.Append(",");
// AssociatedVocab
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_AssociatedVocab
+ "=@AssociatedVocab,");
parameters.Add(new DaoParameter(
"@AssociatedVocab", entity.AssociatedVocab));
// AssociatedKanji
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_AssociatedKanji
+ "=@AssociatedKanji,");
parameters.Add(new DaoParameter(
"@AssociatedKanji", entity.AssociatedKanji));
// MeaningNote
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_MeaningNote
+ "=@MeaningNote,");
parameters.Add(new DaoParameter(
"@MeaningNote", entity.MeaningNote));
// ReadingNote
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_ReadingNote
+ "=@ReadingNote,");
parameters.Add(new DaoParameter(
"@ReadingNote", entity.ReadingNote));
// ServerId
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_ServerId
+ "=@ServerId,");
parameters.Add(new DaoParameter(
"@ServerId", entity.ServerId));
// IsDeleted
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_IsDeleted
+ "=@IsDeleted,");
parameters.Add(new DaoParameter(
"@IsDeleted", entity.IsDeleted));
// LastUpdateDate
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_LastUpdateDate
+ "=@LastUpdateDate,");
parameters.Add(new DaoParameter(
"@LastUpdateDate", DateTime.UtcNow.Ticks));
// Tags
sqlQueryBuilder.Append(SqlHelper.Field_SrsEntry_Tags + "=@Tags");
parameters.Add(new DaoParameter(
"@Tags", MultiValueFieldHelper.Trim(entity.Tags)));
// We are done with the string builders.
// Bring the query pieces together.
string finalQuery =
sqlQueryBuilder.ToString() + " WHERE "
+ SqlHelper.Field_SrsEntry_Id + "=@Id";
parameters.Add(new DaoParameter("@Id", entity.ID));
// Execute the query.
result = connection.ExecuteNonQuery(finalQuery, parameters.ToArray()) == 1;
}
catch (Exception ex)
{
LogHelper.GetLogger(this.GetType().Name).Error(
"An error occured during SRS item update.", ex);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(result);
}
/// <summary>
/// Gets all review information for the current date.
/// </summary>
/// <returns>Review info for the current date.</returns>
public ReviewInfo GetReviewInfo()
{
ReviewInfo info = new ReviewInfo();
DaoConnection connection = null;
try
{
connection = DaoConnection.Open(DaoConnectionEnum.SrsDatabase);
// Query the review count for this date.
info.AvailableReviewsCount = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date",
new DaoParameter("@date", DateTime.UtcNow.Ticks));
// Query the review count for today.
DateTime endOfToday = DateTime.Now.Date.AddDays(1).ToUniversalTime();
info.TodayReviewsCount = (long)connection.QueryScalar(
"SELECT COUNT(1) FROM " + SqlHelper.Table_SrsEntry + " se WHERE se."
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND se."
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " <= @date",
new DaoParameter("@date", endOfToday.Ticks));
// Query the first review date.
object nextAnswerDate = connection.QueryScalar(
"SELECT MIN(" + SqlHelper.Field_SrsEntry_NextAnswerDate + ") FROM "
+ SqlHelper.Table_SrsEntry + " WHERE "
+ SqlHelper.Field_SrsEntry_SuspensionDate + " IS NULL AND "
+ SqlHelper.Field_SrsEntry_NextAnswerDate + " NOT NULL");
if (nextAnswerDate != null && nextAnswerDate is long)
{
info.FirstReviewDate = new DateTime((long)nextAnswerDate,
DateTimeKind.Utc);
}
// Query all counts/total info.
IEnumerable <NameValueCollection> results = connection.Query(
"SELECT COUNT(" + SqlHelper.Field_SrsEntry_AssociatedKanji + ") "
+ SqlKey_KanjiCount + ",COUNT("
+ SqlHelper.Field_SrsEntry_AssociatedVocab + ") " + SqlKey_VocabCount
+ ",SUM(" + SqlHelper.Field_SrsEntry_SuccessCount + ") "
+ SqlKey_SuccessCount + ",SUM(" + SqlHelper.Field_SrsEntry_FailureCount
+ ") " + SqlKey_FailureCount + " FROM " + SqlHelper.Table_SrsEntry);
if (results.Any())
{
NameValueCollection nvcInfo = results.First();
info.KanjiItemsCount = nvcInfo.ReadLong(SqlKey_KanjiCount) ?? 0;
info.VocabItemsCount = nvcInfo.ReadLong(SqlKey_VocabCount) ?? 0;
info.TotalSuccessCount = nvcInfo.ReadLong(SqlKey_SuccessCount) ?? 0;
info.TotalFailureCount = nvcInfo.ReadLong(SqlKey_FailureCount) ?? 0;
}
// Query item count by level.
results = connection.Query("SELECT " + SqlHelper.Field_SrsEntry_CurrentGrade
+ " " + SqlKey_Grade + ", SUM(1) " + SqlKey_ItemCount + " FROM "
+ SqlHelper.Table_SrsEntry + " GROUP BY "
+ SqlHelper.Field_SrsEntry_CurrentGrade);
foreach (NameValueCollection nvcGroup in results)
{
short grade = nvcGroup.ReadShort(SqlKey_Grade) ?? 0;
long itemCount = nvcGroup.ReadLong(SqlKey_ItemCount) ?? 0;
info.ReviewsPerLevel.Add(grade, itemCount);
}
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
return(info);
}
/// <summary>
/// Creates and opens a new connection.
/// </summary>
/// <param name="endpoint">Connection endpoint.</param>
/// <returns>Newly created connection in an open state.</returns>
public static DaoConnection Open(DaoConnectionEnum endpoint)
{
DaoConnection connection = new DaoConnection(endpoint);
connection.Open();
return connection;
}