Beispiel #1
0
        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);
                }
            }
        }
Beispiel #2
0
        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);
            }
        }
Beispiel #3
0
        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());
        }
Beispiel #4
0
        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());
            }
        }
Beispiel #6
0
        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);
        }
Beispiel #7
0
        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());
        }
Beispiel #8
0
        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 });
            }
        }
Beispiel #10
0
        //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);
            }
        }
Beispiel #11
0
        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());
        }
Beispiel #12
0
        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());
            }
        }
Beispiel #15
0
        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());
        }
Beispiel #16
0
        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);
            }
        }
Beispiel #17
0
        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));
            }
        }
Beispiel #19
0
        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);
            }
        }
Beispiel #22
0
        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);
        }