public int SaveToUserDictionary(int pairId, int userId)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
using (var transaction = cnn.BeginTransaction())
{
var metricId = cnn.ExecuteScalar <int>(
@"INSERT INTO QuestionMetric (ElaspedMs, Result, Type, Revision, PreviousExam,
LastExam, ExamsPassed, Examed, PassedScore, AggregateScore, AggregateScoreBefore, PassedScoreBefore)
VALUES(@ElaspedMs, @Result, @Type, @Revision, @PreviousExam, @LastExam, @ExamsPassed,
@Examed, @PassedScore, @AggregateScore, @AggregateScoreBefore, @PassedScoreBefore);
select last_insert_rowid()", new QuestionMetric());
var result = cnn.ExecuteScalar <int>(
@"INSERT INTO UserPairs ( UserId , PairId, MetricId, Created, IsPhrase)
VALUES( @UserId, @PairId, @MetricId, @Created, @IsPhrase);
select last_insert_rowid()", new UserPair(userId, pairId, metricId, false));
transaction.Commit();
return(result);
}
}
}
public void AddQuestionMetric(QuestionMetric metric)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
cnn.Execute(
@"INSERT INTO QuestionMetric (
WordId,
Created,
ElaspedMs,
Result,
Type,
PreviousExam,
ExamsPassed,
AggregateScoreBefore,
PassedScoreBefore)
Values(
@WordId,
@Created,
@ElaspedMs,
@Result,
@Type,
@PreviousExam,
@ExamsPassed,
@AggregateScoreBefore,
@PassedScoreBefore)", metric);
}
}
public UserPair[] GetWorstTestWordsForUser(int count, int learnRate, int userId)
{
CheckDbFile.Check(DbFile);
using var cnn = SimpleDbConnection();
cnn.Open();
var lookup = new Dictionary <string, UserPair>();
//TODO make easier when change db(adding word to userPairs)
//Then you don't need PairDicionary
//TODO it's the same with GetWorstForUser sqlQuestion except LearnRate!!!
//make it easier using GetWorstForUser method + aggregate by learnRate
cnn.Query <UserPair, QuestionMetric, WordDictionary, UserPair>(
@"Select * FROM
(Select * FROM UserPairs WHERE UserId=@userId) up
JOIN QuestionMetric q on q.MetricId=up.MetricId
JOIN PairDictionary dw on dw.PairId=up.PairId
where PassedScore > @learnRate order by AggregateScore desc limit @count
",
(up, dw, q) =>
{
var p = up.Created;
if (!lookup.TryGetValue(q.EnWord, out var pair))
{
lookup.Add(q.EnWord, up);
}
return(up);
}, new { userId, learnRate, count },
splitOn: "MetricId,PairId"
);
return(lookup.Values.ToArray());
}
public UserPair[] GetWorstForUser(int userId, int count)
{
CheckDbFile.Check(DbFile);
using var cnn = SimpleDbConnection();
cnn.Open();
var lookup = new Dictionary <string, UserPair>();
//TODO make easier when change db(adding word to userPairs)
//Then you don't need PairDicionary
cnn.Query <UserPair, QuestionMetric, WordDictionary, UserPair>(
@"Select * FROM
(Select * FROM UserPairs WHERE UserId=@userId) up
JOIN QuestionMetric q on q.MetricId=up.MetricId
JOIN PairDictionary dw on dw.PairId=up.PairId
order by q.AggregateScore desc limit @count
",
(up, dw, q) =>
{
var p = up.Created;
if (!lookup.TryGetValue(q.EnWord, out var pair))
{
lookup.Add(q.EnWord, up);
}
return(up);
}, new { userId, count },
splitOn: "MetricId,PairId"
);
return(lookup.Values.ToArray());
}
public Phrase[] GetAllPhrasesByPairId(int wordPairId)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
return(cnn.Query <Phrase>(@"Select * From Phrases WHERE PairId = @wordPairId", new{ wordPairId }).ToArray());
}
}
public void AddExam(Exam exam)
{
CheckDbFile.Check(DbFile);
using var cnn = SimpleDbConnection();
cnn.Open();
cnn.Execute(
@"INSERT INTO Exams (UserId, Count, Passed, Failed, Started, Finished)
Values(@UserId, @Count, @Passed, @Failed,@Started, @Finished)", exam);
}
public UserWordForLearning[] GetAllUserWordsForLearning(int userId)
{
CheckDbFile.Check(DbFile);
using var cnn = SimpleDbConnection();
cnn.Open();
return(cnn.Query <UserWordForLearning>(
@"Select * from UserWords WHERE UserId=userId
", new { userId }).ToArray());
}
public QuestionMetric FindMetricOrNull(int metricId)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
return(cnn.Query <QuestionMetric>(
@"SELECT * FROM QuestionMetric WHERE MetricId=@metricId", new{ metricId }).FirstOrDefault());
}
}
public void RemovePhrase(int phraseId)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var sqlQuery = "DELETE FROM Phrases WHERE Id = @phrasesId";
cnn.Execute(sqlQuery, new { phraseId });
}
}
//TODO additional methods
public User[] GetAllUsers()
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var user = cnn.Query <User>(@"Select * from Users order by Login").ToArray();
return(user);
}
}
public UserWordForLearning[] GetWorstTestWordsForUser(int count, int learnRate, int userId)
{
CheckDbFile.Check(DbFile);
using var cnn = SimpleDbConnection();
cnn.Open();
return(cnn.Query <UserWordForLearning>(
@"Select * FROM UserWords WHERE UserId=@userId AND PassedScore > @learnRate
order by AggregateScore desc limit @count
", new { userId, learnRate, count }).ToArray());
}
public UserPair GetPairByDicIdOrNull(int userId, int id)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
return(cnn.Query <UserPair>(
@"SELECT * FROM UserPairs
WHERE UserId = @UserId AND PairId=@id", new { userId, id }).FirstOrDefault());
}
}
//TODO Additional methods
public WordDictionary GetPairByIdOrNull(int?id)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var result = cnn.Query <WordDictionary>(
@"SELECT * FROM PairDictionary
WHERE PairId = @id", new { id }).FirstOrDefault();
return(result);
}
}
public Phrase[] FindSeveralPhraseById(int[] allPhrasesIdForUser)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var result = cnn.Query <Phrase>(
@"SELECT * FROM Phrases
WHERE PairId in @allPhrasesIdForUser", allPhrasesIdForUser);
return(result.ToArray());
}
}
public UserWordForLearning[] GetWorstForUser(int userId, int count)
{
CheckDbFile.Check(DbFile);
using var cnn = SimpleDbConnection();
cnn.Open();
var result = cnn.Query <UserWordForLearning>(
@"Select * FROM UserWords WHERE UserId=@userId
order by AggregateScore desc limit @count
", new { userId, count });
return(result.ToArray());
}
public User GetUserByLoginOrNull(string login)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var user = cnn.Query <User>(
@"SELECT UserId, Name, Login, Password,Email,Created,Online
FROM Users
WHERE Login = @login", new { login }).FirstOrDefault();
return(user);
}
}
public int AddUser(User user)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var id = cnn.ExecuteScalar <int>(
@"INSERT INTO Users ( Name, Login, Password, Email, Created, Online)
VALUES( @Name, @Login, @Password, @Email, @Created, @Online);
SELECT last_insert_rowid();", user);
return(id);
}
}
public int AddPhrase(int pairId, string enWord, string ruWord, string enPhrase, string ruTranslate)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
var phrase = new Phrase(pairId, enWord, ruWord, enPhrase, ruTranslate);
cnn.Open();
return(cnn.ExecuteScalar <int>(
@"INSERT INTO Phrases ( PairId , EnWord, WordTranslate, EnPhrase, PhraseRuTranslate)
VALUES( @PairId, @EnWord, @WordTranslate, @EnPhrase, @PhraseRuTranslate);
select last_insert_rowid()", phrase));
}
}
public string[] GetAllTranslatesForWordForUser(int userId, string word)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
return(cnn.Query <string>(
@"SELECT pd.RuWord FROM PairDictionary pd
JOIN UserPairs up on up.PairId=pd.PairId
WHERE up.UserId = @UserId AND pd.EnWord=@word", new { userId, word }).ToArray());
}
}
public string[] GetAllTranslate(string word)
{
//TODO find by RuWord or by EnWord.
//if word == RuWord(английские символы), ищем в таблице WordPairDictionary по русскому слову все переводы
//if word == EnWord(русские символы), ищем в таблице WordPairDictionary по английскому слову все переводы
CheckDbFile.Check(DbFile);
//TODO Is it need distinct?
using (var cnn = SimpleDbConnection())
{
cnn.Open();
return(cnn.Query <string>(
@"SELECT RuWord FROM PairDictionary WHERE EnWord=@word", new { word }).ToArray());
}
}
public int AddWordPair(WordDictionary word)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
word.PairId = cnn.ExecuteScalar <int>(
@"INSERT INTO PairDictionary ( EnWord, Transcription, RuWord, Sourse)
VALUES( @EnWord, @Transcription, @RuWord, @Sourse);
select last_insert_rowid()", word);
//TODO Add Phrase if word has them
return(word.PairId);
}
}
public int SaveToUserDictionary(UserWordForLearning userWordForLearning)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var result = cnn.ExecuteScalar <int>(
@"INSERT INTO UserWords (UserId, EnWord, UserTranslations, Transcription, Created, PhrasesIds, IsPhrase,
PassedScore, AggregateScore,LastExam,Examed,Revision)
VALUES(@UserId, @EnWord, @UserTranslations, @Transcription, @Created, @PhrasesIds, @IsPhrase,
@PassedScore, @AggregateScore,@LastExam,@Examed,@Revision)",
userWordForLearning);
return(result);
}
}
public WordDictionary GetPairWithPhrasesByIdOrNull(int id)
{
CheckDbFile.Check(DbFile);
using (var cnn = SimpleDbConnection())
{
cnn.Open();
var phrases = new List <Phrase>();
var result = cnn.Query <WordDictionary, Phrase, WordDictionary>(
@"SELECT * FROM
(SELECT * FROM PairDictionary WHERE PairId=@id) pd
JOIN Phrases ph ON ph.PairId = pd.PairId",
(pd, ph) =>
{
//TODO почему он не мапит Id для фразы???
phrases.Add(ph);
return(pd);
}, new { id },
splitOn: "PairId").FirstOrDefault();
result.Phrases = phrases;
return(result);
}
return(null);
}