/*
* UPSERTS the world in database, if not exists - insert it, if exists - update, but only if last_update_date is bigger
*/
public void upsertWord(DictionaryItem item)
{
logger.Trace("upserting one word, id: " + item.Id);
try {
openConnection();
SQLiteCommand updateSQL = new SQLiteCommand(
"INSERT INTO words(id, word, translation, correct_answers, iteration, next_show_date, last_update_date) " +
"VALUES(@id,@word,@tran,@answ,@iter,@nextDate,@updateDate) " +
"ON CONFLICT(id) DO UPDATE SET " +
"word = excluded.word, " +
"translation = excluded.translation, " +
"correct_answers = excluded.correct_answers, " +
"iteration = excluded.iteration, " +
"next_show_date = excluded.next_show_date " +
"last_update_date = excluded.last_update_date " +
"WHERE (id = @id) and (last_update_date <= excluded.last_update_date) ", dbConnection);
updateSQL.Parameters.AddWithValue("@word", item.Word);
updateSQL.Parameters.AddWithValue("@tran", item.Translation);
updateSQL.Parameters.AddWithValue("@answ", item.CorrectAnswers);
updateSQL.Parameters.AddWithValue("@iter", item.Iteration);
updateSQL.Parameters.AddWithValue("@id", item.Id);
DateTime nextDate = item.NextShowDate;
if (!Object.Equals(nextDate, default(DateTime))) //if date != null
{
string nextDateStr = nextDate.ToString("yyyy-MM-dd HH:mm:ss");
updateSQL.Parameters.AddWithValue("@nextDate", nextDateStr);
}
else
{
updateSQL.Parameters.AddWithValue("@nextDate", null);
}
DateTime lastUpdateDate = item.LastUpdateDate;
if (!Object.Equals(lastUpdateDate, default(DateTime))) //if date != null
{
string lastUpdateDateStr = lastUpdateDate.ToString("yyyy-MM-dd HH:mm:ss");
updateSQL.Parameters.AddWithValue("@updateDate", lastUpdateDateStr);
}
else
{
updateSQL.Parameters.AddWithValue("@updateDate", null);
}
updateSQL.ExecuteNonQuery();
} catch (Exception ex) {
logger.Error(ex, "Error while upserting word with ID " + item.Id);
throw new Exception(ex.Message);
} finally {
closeConnection();
}
}
/*
* insert new word into table
*/
private void insertWord(SQLiteConnection connection, DictionaryItem item)
{
//logger.Debug("inserting one word, id: " + item.Id);
DateTime dt = DateTime.Now;
try {
SQLiteCommand updateSQL = new SQLiteCommand(
"INSERT INTO words(id, word, translation, correct_answers, iteration, next_show_date, last_update_date) " +
"VALUES(@id,@word,@tran,@answ,@iter,@nextDate,@updateDate) ", connection);
updateSQL.Parameters.AddWithValue("@id", item.Id);
updateSQL.Parameters.AddWithValue("@word", item.Word);
updateSQL.Parameters.AddWithValue("@tran", item.Translation);
updateSQL.Parameters.AddWithValue("@answ", item.CorrectAnswers);
updateSQL.Parameters.AddWithValue("@iter", item.Iteration);
DateTime nextDate = item.NextShowDate;
if (!Object.Equals(nextDate, default(DateTime))) //if date != null
{
string nextDateStr = nextDate.ToString("yyyy-MM-dd HH:mm:ss");
updateSQL.Parameters.AddWithValue("@nextDate", nextDateStr);
}
else
{
updateSQL.Parameters.AddWithValue("@nextDate", null);
}
DateTime lastUpdateDate = item.LastUpdateDate;
if (!Object.Equals(lastUpdateDate, default(DateTime))) //if date != null
{
string lastUpdateDateStr = lastUpdateDate.ToString("yyyy-MM-dd HH:mm:ss");
updateSQL.Parameters.AddWithValue("@updateDate", lastUpdateDateStr);
}
else
{
updateSQL.Parameters.AddWithValue("@updateDate", null);
}
updateSQL.ExecuteNonQuery();
TimeSpan ts = DateTime.Now - dt;
Console.WriteLine("inserting took: " + ts);
} catch (Exception ex) {
logger.Error(ex, "Error while inserting word with ID " + item.Id);
throw new Exception(ex.Message);
}
}
/*
* completely update info for given dictionary item in database
*/
public void updateWord(DictionaryItem item)
{
logger.Trace("updating one word");
try {
openConnection();
SQLiteCommand updateSQL = new SQLiteCommand(
"UPDATE words SET " +
"word = @word, " +
"translation = @tran, " +
"correct_answers = @answ, " +
"iteration = @iter, " +
"next_show_date = @nextDate, " +
"last_update_date = @updateDate " +
"WHERE id = @id", dbConnection);
updateSQL.Parameters.AddWithValue("@word", item.Word);
updateSQL.Parameters.AddWithValue("@tran", item.Translation);
updateSQL.Parameters.AddWithValue("@answ", item.CorrectAnswers);
updateSQL.Parameters.AddWithValue("@iter", item.Iteration);
updateSQL.Parameters.AddWithValue("@id", item.Id);
DateTime nextDate = item.NextShowDate;
if (!Object.Equals(nextDate, default(DateTime))) //if date != null
{
string nextDateStr = nextDate.ToString("yyyy-MM-dd HH:mm:ss");
updateSQL.Parameters.AddWithValue("@nextDate", nextDateStr);
}
else
{
updateSQL.Parameters.AddWithValue("@nextDate", null);
}
DateTime currentDate = DateTime.Now;
string updateDateStr = currentDate.ToString("yyyy-MM-dd HH:mm:ss");
updateSQL.Parameters.AddWithValue("@updateDate", updateDateStr);
updateSQL.ExecuteNonQuery();
} catch (Exception ex) {
logger.Error(ex, "Error while updating one word in database with ID " + item.Id);
throw new Exception(ex.Message);
} finally {
closeConnection();
}
}
/*
* update number of correct answers for word
*/
public void UpdateCorrectAnswers(DictionaryItem dItem)
{
logger.Trace("Updating correct answers");
string sql = "UPDATE words SET correct_answers = @answers WHERE id = @id";
try {
openConnection();
SQLiteCommand updateSQL = new SQLiteCommand(sql, dbConnection);
updateSQL.Parameters.AddWithValue("@answers", dItem.CorrectAnswers);
updateSQL.Parameters.AddWithValue("@Id", dItem.Id);
updateSQL.ExecuteNonQuery();
} catch (Exception ex) {
logger.Error(ex, "Error while updating correct answers in database for id " + dItem.Id);
throw new Exception(ex.Message);
} finally {
closeConnection();
}
}