Changeset 226:770ed82afa5f in SMSSender


Ignore:
Timestamp:
May 30, 2012 5:12:52 PM (8 years ago)
Author:
Sämy Zehnder <saemy.zehnder@…>
Branch:
default
Message:

Schoolnet:

  • Implemented new is-alive flag of questions. This allows removing questions which are not accessible on the schoolnet website anymore.
Location:
gateways/Schoolnet/src
Files:
13 edited

Legend:

Unmodified
Added
Removed
  • gateways/Schoolnet/src/business/deepthought/datatypes.cpp

    r220 r226  
    6464    return d->hint;
    6565}
     66bool Question::isAlive() const {
     67    return d->isAlive;
     68}
    6669QList<Answer> Question::answers() const {
    6770    return d->answers;
     
    98101    SET_IF_DIFFERENT(d->hint, hint.trimmed())
    99102}
     103void Question::setIsAlive(bool isAlive) {
     104    SET_IF_DIFFERENT(d->isAlive, isAlive);
     105}
     106
    100107void Question::setAnswers(const QList<Answer>& answers) {
    101108    SET_IF_DIFFERENT(d->answers, answers)
  • gateways/Schoolnet/src/business/deepthought/datatypes.h

    r220 r226  
    6060    QString        text() const;
    6161    QString        hint() const;
     62    bool           isAlive() const;
    6263
    6364    QList<Answer>  answers() const;
     
    7172    void           setHint(const QString& hint);
    7273    void           setAnswers(const QList<Answer>& answers);
     74    void           setIsAlive(bool isAlive);
    7375
    7476    void           addAnswer(const Answer& answer);
  • gateways/Schoolnet/src/business/deepthought/datatypes_p.h

    r220 r226  
    2626        , text(other.text)
    2727        , hint(other.hint)
     28        , isAlive(other.isAlive)
    2829        , answers(other.answers)
    2930    {}
     
    3637    QString        text;
    3738    QString        hint;
     39    bool           isAlive;
    3840    QList<Answer>  answers;
    3941};
  • gateways/Schoolnet/src/business/deepthought/deepthought_daemon_deepthought.cpp

    r222 r226  
    3636            this, SLOT(onAnswerCorrectnessProvided(const Answer&)));
    3737
    38     connect(Library::instance()->managerFactory()->accountManager(), SIGNAL(onAccountAdded(IAccount*)),
     38    connect(Library::instance()->managerFactory()->accountManager(), SIGNAL(accountAdded(IAccount*)),
    3939            this, SLOT(onAccountAdded(IAccount*)));
    4040    connect(Library::instance()->managerFactory()->accountManager(), SIGNAL(accountDataChanged(IAccount*, int)),
  • gateways/Schoolnet/src/business/deepthought/deepthought_daemon_schoolnet.cpp

    r220 r226  
    3131    : QObject(parent)
    3232    , account_(account)
     33    , checkStatusAtSchoolnetTaskStarted_(false)
     34    , deepThoughtDataFetchFinished_(false)
    3335{
    3436    connect(QuestionManager::instance(), SIGNAL(answerCorrectnessProvided(const Answer&)),
     
    8587
    8688void SchoolnetDaemon::onAccountLoggedIn() {
     89    QMutexLocker locker(&mutex_);
     90
    8791    sendCorrectQuestionsToSchoolnet();
    8892
    89     STask* cqsas = new CheckQuestionStatusAtSchoolnetTask(account());
    90     cqsas->start();
     93    if (deepThoughtDataFetchFinished_ && !checkStatusAtSchoolnetTaskStarted_) {
     94        checkStatusAtSchoolnetTaskStarted_ = true;
     95
     96        STask* cqsas = new CheckQuestionStatusAtSchoolnetTask(account());
     97        cqsas->start();
     98    }
    9199}
    92100
    93101void SchoolnetDaemon::onDeepThoughtDataFetchFinished(STask* task) {
    94     Q_UNUSED(task)
    95     /* Don't do this, since we reload the next time, the account loggs in.
    96      * The problem with this here is, that we login twice (ensureLoggedIn)
    97      * and call CheckQuestionStatusAtSchoolnetTask twice at startup (since
    98      * we are listening at the loggin event of the account there).
    99      */
    100 
    101     /*if (task->taskResult() != STask::rSuccess) {
     102    if (task->taskResult() != STask::rSuccess) {
    102103        return;
    103104    }
     
    107108    }
    108109
    109     STask* cqsas = new CheckQuestionStatusAtSchoolnetTask(account());
    110     cqsas->start();*/
     110    QMutexLocker locker(&mutex_);
     111
     112    if (account()->isLoggedIn() && !checkStatusAtSchoolnetTaskStarted_) {
     113        checkStatusAtSchoolnetTaskStarted_ = true;
     114
     115        STask* cqsas = new CheckQuestionStatusAtSchoolnetTask(account());
     116        cqsas->start();
     117    }
     118
     119    deepThoughtDataFetchFinished_ = true;
    111120}
    112121
  • gateways/Schoolnet/src/business/deepthought/deepthought_daemons.h

    r222 r226  
    3939
    4040public:
    41     explicit SchoolnetDaemon(QObject* parent, BCAccount* account);
     41    SchoolnetDaemon(QObject* parent, BCAccount* account);
    4242
    4343    STask* proposeAnswer(const Answer& answer);
     
    5454private:
    5555    BCAccount* account_;
     56    bool checkStatusAtSchoolnetTaskStarted_;
     57    bool deepThoughtDataFetchFinished_;
     58    QMutex mutex_;
    5659};
    5760
  • gateways/Schoolnet/src/business/deepthought/deepthought_task_answertoschoolnet.cpp

    r225 r226  
    4747    if (answer().question().hasCorrectAnswer() && !answer().isCorrect()) {
    4848        // Don't waste effort on already correctly answered questions!
     49        setSpecialResult(aCancelled);
     50        return;
     51    }
     52    if (!answer().question().isAlive()) {
    4953        setSpecialResult(aCancelled);
    5054        return;
  • gateways/Schoolnet/src/business/deepthought/deepthought_task_check_questionstatus_at_schoolnet.cpp

    r225 r226  
    4949        setProgress(pos++ / (double)questions.size() * 100);
    5050
     51        if (!question.isAlive()) {
     52            continue;
     53        }
     54
    5155        if (QuestionManager::instance()->isStatusAtSchoolnetChecked(account(), question)) {
    5256            continue;
  • gateways/Schoolnet/src/business/deepthought/deepthought_task_fetchdatafromdeepthought.cpp

    r220 r226  
    6363
    6464void FetchDataFromDeepThoughtTask::processQuestionUpdate(const QList<Question>& questions) {
     65    if (questions.isEmpty()) {
     66        return;
     67    }
     68
    6569    QList<Question> dbQuestions = QuestionManager::instance()->questions();
    6670
     
    9296            dbQuestion.setHint(question.hint());
    9397        }
     98
     99        dbQuestion.setIsAlive(question.isAlive());
    94100
    95101        QMap<QString, Answer> dbAnswerMap;
     
    182188            question.setHint(element.firstChild().nodeValue());
    183189        }
     190        if (element.tagName().toLower() == "isalive") {
     191            question.setIsAlive(element.firstChild().nodeValue() != "0");
     192        }
    184193        if (element.tagName().toLower() == "answers") {
    185194            question.setAnswers(parseAnswers(element, question));
  • gateways/Schoolnet/src/business/deepthought/questionmanager.cpp

    r225 r226  
    6060    foreach (Question question, questions()) {
    6161        if (acceptedLanguages.contains(question.language()) &&  // Correct language?
     62            question.isAlive() &&                               // Alive?
    6263            !question.hasCorrectAnswer() &&                     // Not already answered?
    6364            isStatusAtSchoolnetChecked(account, question) &&    // Has the status at schoolnet been checked?
  • gateways/Schoolnet/src/persistence/deepthought/dadeepthoughtstorage.cpp

    r222 r226  
    3030S_SINGLETON_IMPL(DADeepThoughtStorage)
    3131
    32 const uint DADeepThoughtStorage::ACTUAL_DB_VERSION = 3;
     32const uint DADeepThoughtStorage::ACTUAL_DB_VERSION = 4;
    3333
    3434DADeepThoughtStorage::DADeepThoughtStorage() {
     
    4949}
    5050
     51
     52int DADeepThoughtStorage::_getQuestionId(const Question &question) {
     53    QSqlQuery query = createQuery();
     54
     55    query.prepare("SELECT id "
     56                    "FROM t_question "
     57                    "WHERE (nrNodeGuId = :nrNodeGuId) "
     58                      "AND (eventTarget = :eventTarget);");
     59    query.bindValue(":nrNodeGuId", question.nrNodeGuId());
     60    query.bindValue(":eventTarget", question.eventTarget());
     61
     62    if (!query.exec()) {
     63        EException(QObject::tr("Could not fetch the question from the database."))
     64             .addDebugInfo("sql-query", query.lastQuery())
     65             .addDebugInfo("sql-response", query.lastError().text())
     66             .raise();
     67    }
     68
     69    if (query.next()) {
     70        return query.value(0).toInt();
     71    } else {
     72        return 0;
     73    }
     74}
     75
     76void DADeepThoughtStorage::updateQuestion(Question &question) {
     77    QSqlQuery query = createQuery();
     78    if (question.id() > 0) {
     79        query.prepare("UPDATE t_question SET "
     80                          "language=:language, "
     81                          "questionText=:questionText, "
     82                          "isAlive=:isAlive "
     83                        "WHERE (id = :id); ");
     84        query.bindValue(":id", question.id());
     85    } else {
     86        query.prepare("INSERT INTO t_question (nrNodeGuId, eventTarget, language, questionText, isAlive) "
     87                        "VALUES(:nrNodeGuId, :eventTarget, :language, :questionText, :isAlive); ");
     88        query.bindValue(":nrNodeGuId", question.nrNodeGuId());
     89        query.bindValue(":eventTarget", question.eventTarget());
     90    }
     91    query.bindValue(":language", question.language());
     92    query.bindValue(":questionText", question.text());
     93    query.bindValue(":isAlive", question.isAlive());
     94
     95    if (!query.exec()) {
     96        EException(QObject::tr("Failed to update/insert the question in the database."))
     97             .addDebugInfo("sql-query", query.lastQuery())
     98             .addDebugInfo("sql-response", query.lastError().text())
     99             .raise();
     100    }
     101
     102    if (question.id() <= 0) {
     103        int questionId = _getQuestionId(question);
     104        if (questionId > 0) {
     105            question.setId(questionId);
     106        } else {
     107            EException(QObject::tr("Failed to read the inserted question from the database."))
     108                 .addDebugInfo("sql-query", query.lastQuery())
     109                 .addDebugInfo("sql-response", query.lastError().text())
     110                 .raise();
     111        }
     112    }
     113
     114    // Update the attached answers
     115    QList<QString> attachedAnswers;
     116    foreach(Answer answer, question.answers()) {
     117        updateAnswer(answer);
     118        attachedAnswers.append(answer.id());
     119    }
     120
     121    // Remove old answers
     122    foreach (Answer answer, getAnswers(question)) {
     123        if (!attachedAnswers.contains(answer.id())) {
     124            query.prepare("DELETE FROM t_answer "
     125                            "WHERE (questionId = :questionId) "
     126                              "AND (id = :id);");
     127            query.bindValue(":questionId", question.id());
     128            query.bindValue(":id", answer.id());
     129
     130            if (!query.exec()) {
     131                EException(QObject::tr("Failed to remove an old answer of a question from the database."))
     132                     .addDebugInfo("sql-query", query.lastQuery())
     133                     .addDebugInfo("sql-response", query.lastError().text())
     134                     .addDebugInfo("question", question.id())
     135                     .addDebugInfo("answer", answer.id())
     136                     .raise();
     137            }
     138        }
     139    }
     140}
     141
     142void DADeepThoughtStorage::updateAnswer(const Answer &answer) {
     143    QSqlQuery query = createQuery();
     144
     145    if (answer.question().id() <= 0) {
     146        EException(QObject::tr("The question of this answer is not yet in the database. Please insert it first."))
     147             .raise();
     148    }
     149
     150    query.prepare("SELECT id "
     151                    "FROM t_answer "
     152                    "WHERE (id = :id) "
     153                      "AND (questionId = :questionId);");
     154    query.bindValue(":id", answer.id());
     155    query.bindValue(":questionId", answer.question().id());
     156
     157    if (query.exec() && query.next()) {
     158        query.prepare("UPDATE t_answer SET answerText=:answerText, answered=:answered, correct=:correct, deepThoughtKnowsCorrectness=:deepThought "
     159                        "WHERE (id = :id) "
     160                          "AND (questionId = :questionId);");
     161    } else {
     162        query.prepare("INSERT INTO t_answer (id, questionId, answerText, answered, correct, deepThoughtKnowsCorrectness) "
     163                        "VALUES (:id, :questionId, :answerText, :answered, :correct, :deepThought); ");
     164    }
     165    query.bindValue(":id", answer.id());
     166    query.bindValue(":questionId", answer.question().id());
     167    query.bindValue(":answerText", answer.text());
     168    query.bindValue(":answered", answer.isAnswered());
     169    query.bindValue(":correct", answer.isCorrect());
     170    query.bindValue(":deepThought", answer.doesDeepThoughtKnowCorrectness());
     171
     172    if (!query.exec()) {
     173        EException(QObject::tr("Failed to update the answer in the database."))
     174             .addDebugInfo("sql-query", query.lastQuery())
     175             .addDebugInfo("sql-response", query.lastError().text())
     176             .raise();
     177    }
     178}
     179
     180void DADeepThoughtStorage::removeQuestion(Question &question) {
     181    QSqlQuery query = createQuery();
     182
     183    if (question.id() < 0) {
     184        query.prepare("DELETE FROM t_question "
     185                        "WHERE (nrNodeGuId = :nrNodeGuId) "
     186                          "AND (eventTarget = :eventTarget);");
     187        query.bindValue(":nrNodeGuId", question.nrNodeGuId());
     188        query.bindValue(":eventTarget", question.eventTarget());
     189    } else {
     190        query.prepare("DELETE FROM t_question "
     191                        "WHERE (id = :id);");
     192        query.bindValue(":id", question.id());
     193    }
     194
     195    if (!query.exec()) {
     196        EException(QObject::tr("Could not remove the question from the database."))
     197             .addDebugInfo("sql-query", query.lastQuery())
     198             .addDebugInfo("sql-response", query.lastError().text())
     199             .raise();
     200    }
     201    question.setId(0);
     202}
     203
     204QList<Question> DADeepThoughtStorage::getQuestions() {
     205    QSqlQuery query = createQuery();
     206
     207    if (!query.exec("SELECT id "
     208                      "FROM t_question; ")) {
     209        qWarning() << query.lastError().text();
     210        EException(QObject::tr("Could not get the questions from the database."))
     211            .addDebugInfo("sql-query", query.lastQuery())
     212            .addDebugInfo("sql-response", query.lastError().text())
     213            .raise();
     214    }
     215
     216    QList<Question> questions;
     217    while (query.next()) {
     218        questions.append(getQuestion(query.value(0).toInt()));
     219    }
     220
     221    return questions;
     222}
     223Question DADeepThoughtStorage::getQuestion(int questionId) {
     224    QSqlQuery query = createQuery();
     225
     226    query.prepare("SELECT nrNodeGuId, eventTarget, language, questionText, isAlive "
     227                    "FROM t_question "
     228                    "WHERE (id = :id);");
     229    query.bindValue(":id", questionId);
     230    if (!query.exec()) {
     231        qWarning() << query.lastError().text();
     232        EException(QObject::tr("Could not get the question from the database."))
     233            .addDebugInfo("sql-query", query.lastQuery())
     234            .addDebugInfo("sql-response", query.lastError().text())
     235            .addDebugInfo("questionId", questionId)
     236            .raise();
     237    }
     238    if (!query.next()) {
     239        EException(QObject::tr("The requested question could not be found in the database."))
     240            .addDebugInfo("questionId", questionId)
     241            .raise();
     242    }
     243
     244    Question question;
     245    question.setId(questionId);
     246    question.setNrNodeGuId(query.value(0).toString());
     247    question.setEventTarget(query.value(1).toString());
     248    question.setLanguage(query.value(2).toString());
     249    question.setText(query.value(3).toString());
     250    question.setIsAlive(query.value(4).toBool());
     251
     252    question.setAnswers(getAnswers(question));
     253
     254    return question;
     255}
     256
     257QList<Answer> DADeepThoughtStorage::getAnswers(const Question &question) {
     258    QSqlQuery query = createQuery();
     259
     260    query.prepare("SELECT id "
     261                       "FROM t_answer "
     262                       "WHERE (questionId = :questionId); ");
     263    query.bindValue(":questionId", question.id());
     264
     265    if (!query.exec()) {
     266        EException(QObject::tr("Failed to fetch the answers of the question from the database."))
     267             .addDebugInfo("sql-query", query.lastQuery())
     268             .addDebugInfo("sql-response", query.lastError().text())
     269             .addDebugInfo("questionId", question.id())
     270             .raise();
     271    }
     272
     273    QList<Answer> answers;
     274    while (query.next()) {
     275        answers.append(getAnswer(question, query.value(0).toString()));
     276    }
     277
     278    return answers;
     279}
     280Answer DADeepThoughtStorage::getAnswer(const Question &question, const QString &answerId) {
     281    QSqlQuery query = createQuery();
     282
     283    query.prepare("SELECT answerText, answered, correct, deepThoughtKnowsCorrectness "
     284                    "FROM t_answer "
     285                    "WHERE (questionId = :questionId) "
     286                      "AND (id = :id);");
     287    query.bindValue(":questionId", question.id());
     288    query.bindValue(":id", answerId);
     289
     290    if (!query.exec()) {
     291        EException(QObject::tr("Could not get the answer from the database."))
     292            .addDebugInfo("sql-query", query.lastQuery())
     293            .addDebugInfo("sql-response", query.lastError().text())
     294            .addDebugInfo("questionId", question.id())
     295            .addDebugInfo("answerId", answerId)
     296            .raise();
     297    }
     298    if (!query.next()) {
     299        EException(QObject::tr("The requested answer could not be found in the database."))
     300            .addDebugInfo("questionId", question.id())
     301            .addDebugInfo("answerId", answerId)
     302            .raise();
     303    }
     304
     305    Answer answer(question);
     306    answer.setId(answerId);
     307    answer.setText(query.value(0).toString());
     308    answer.setAnswered(query.value(1).toBool());
     309    answer.setCorrect(query.value(2).toBool());
     310    answer.setDeepThoughtKnowsCorrectness(query.value(3).toBool());
     311
     312    return answer;
     313}
     314
     315/**
     316 * Returns the status of this question at the given schoolnet account.
     317 * ATTENTION: This does not check the real state of the question at schoolnet. It justs reads
     318 *            the state from the database. When the user answered some questions directly at
     319 *            schoolnet, it won't be detected here.
     320 *
     321 * @param account The schoolnet account
     322 * @param question The question
     323 * @return @see Question::Status
     324 */
     325uint DADeepThoughtStorage::questionStatusAtSchoolnet(BCAccount *account, const Question &question) const {
     326    QSqlQuery query = createQuery();
     327
     328    query.prepare("SELECT status "
     329                    "FROM t_status_at_schoolnet "
     330                    "WHERE (account = :account) "
     331                      "AND (questionId = :questionId);");
     332    query.bindValue(":account", account->username());
     333    query.bindValue(":questionId", question.id());
     334
     335    if (!query.exec()) {
     336        EException("Could not read the schoolnet-status of this question from the database.")
     337                .addDebugInfo("sql-query", query.lastQuery())
     338                .addDebugInfo("sql-response", query.lastError().text())
     339                .addDebugInfo("accountId", account->username())
     340                .addDebugInfo("questionId", question.id())
     341                .raise();
     342    }
     343
     344    if (!query.next()) {
     345        return 0;
     346    } else {
     347        return query.value(0).toInt();
     348    }
     349}
     350
     351void DADeepThoughtStorage::setQuestionStatusAtSchoolnet(BCAccount *account, const Question &question, uint status) const {
     352    QSqlQuery query = createQuery();
     353
     354    query.prepare("REPLACE INTO t_status_at_schoolnet (account, questionId, status) VALUES (:account, :questionId, :status);");
     355    query.bindValue(":account", account->username());
     356    query.bindValue(":questionId", question.id());
     357    query.bindValue(":status", status);
     358
     359    if (!query.exec()) {
     360        EException(QObject::tr("Failed to update/insert the question status in the database."))
     361                .addDebugInfo("sql-query", query.lastQuery())
     362                .addDebugInfo("sql-response", query.lastError().text())
     363                .addDebugInfo("accountId", account->username())
     364                .addDebugInfo("questionId", question.id())
     365                .raise();
     366    }
     367}
     368
     369/**
     370 * Returns true, if the given status of the given question has been checked for the given schoolnet account.
     371 * @see {@link #getQuestionStatusAtSchoolnet(BCAccount*, const Question&) const}
     372 *
     373 * @param account The schoolnet account
     374 * @param question The question
     375 * @return True, If the question is answered
     376 */
     377bool DADeepThoughtStorage::isQuestionStatusAtSchoolnetChecked(BCAccount *account, const Question &question) const {
     378    return questionStatusAtSchoolnet(account, question) & Question::StatusAtSchoolnetChecked;
     379}
     380
     381/**
     382 * Marks the given question status as checked from schoolnet.
     383 *
     384 * @param account The schoolnet account
     385 * @param question The question
     386 */
     387void DADeepThoughtStorage::setQuestionStatusAtSchoolnetChecked(BCAccount *account, const Question &question) const {
     388    uint newStatus = questionStatusAtSchoolnet(account, question) | Question::StatusAtSchoolnetChecked;
     389    setQuestionStatusAtSchoolnet(account, question, newStatus);
     390}
     391
     392
     393/**
     394 * Returns true, if the given question is already answered at schoolnet for the given account.
     395 * @see {@link #getQuestionStatusAtSchoolnet(BCAccount*, const Question&) const}
     396 *
     397 * @param account The schoolnet account
     398 * @param question The question
     399 * @return True, If the question is answered
     400 */
     401bool DADeepThoughtStorage::isQuestionAnsweredAtSchoolnet(BCAccount *account, const Question &question) const {
     402    return questionStatusAtSchoolnet(account, question) & Question::AnsweredAtSchoolnet;
     403}
     404
     405/**
     406 * Marks the given question as answered at schoolnet and that the status has been checked as well.
     407 *
     408 * @param account The schoolnet account
     409 * @param question The question
     410 */
     411void DADeepThoughtStorage::setQuestionAnsweredAtSchoolnet(BCAccount *account, const Question &question) const {
     412    uint newStatus = questionStatusAtSchoolnet(account, question)
     413            | Question::AnsweredAtSchoolnet
     414            | Question::StatusAtSchoolnetChecked;
     415    setQuestionStatusAtSchoolnet(account, question, newStatus);
     416}
     417
     418
     419QDateTime DADeepThoughtStorage::lastDeepThoughtRun() {
     420    QSqlQuery query = createQuery();
     421
     422    if (!query.exec("SELECT lastrun "
     423                      "FROM t_lastrun "
     424                      "ORDER BY lastrun DESC;"))
     425    {
     426        EException(QObject::tr("Coult not get the time of the last DeepThought run."))
     427                    .addDebugInfo("sql-query", query.lastQuery())
     428                    .addDebugInfo("sql-response", query.lastError().text())
     429                    .raise();
     430    }
     431
     432    QDateTime lastRun;
     433    if (query.next()) {
     434        lastRun = QDateTime::fromTime_t(query.value(0).toInt());
     435    }
     436    return lastRun;
     437}
     438void DADeepThoughtStorage::setLastDeepThoughtRun(QDateTime lastrun) {
     439    uint lr = lastrun.toTime_t();
     440
     441    QSqlQuery query = createQuery();
     442
     443    // Add new lastrun entry
     444    query.prepare("INSERT INTO t_lastrun (lastrun) VALUES (:lastrun);");
     445    query.bindValue(":lastrun", lr);
     446    if (!query.exec()) {
     447        EException(QObject::tr("Coult not save the time of the last DeepThought run."))
     448                    .addDebugInfo("sql-query", query.lastQuery())
     449                    .addDebugInfo("sql-response", query.lastError().text())
     450                    .raise();
     451    }
     452    query.clear();
     453
     454    // Remove old lastrun entries
     455    query.prepare("DELETE "
     456                    "FROM t_lastrun "
     457                    "WHERE (lastrun < :lastrun);");
     458    query.bindValue(":lastrun", lr);
     459    query.exec();
     460}
     461
     462
    51463void DADeepThoughtStorage::doVersionUpdate(uint storageVersion) {
    52464    connection().transaction();
     
    56468            case 1: version1To2(); // Added status flag to t_question
    57469            case 2: version2To3(); // Renamed t_answered_at_schoolnet into t_status_at_schoolnet; Moved status flag from t_question to t_status_at_schoolnet
    58 
     470            case 3: version3To4(); // Added isAlive flag to t_question
    59471
    60472            break;
     
    146558    }
    147559}
     560void DADeepThoughtStorage::version3To4() {
     561    QSqlQuery query = createQuery();
     562    try {
     563        if (!query.exec("ALTER TABLE t_question ADD COLUMN isAlive INTEGER NOT NULL DEFAULT 1;")) throw 0;
     564        if (!query.exec("DELETE FROM t_lastrun;")) throw 0; // to ensure we fetch the new isAlive data from all the questions
     565    } catch (...) {
     566        EException("Error in version3To4.")
     567                .addDebugInfo("sql-error", query.lastError().text())
     568                .raise();
     569    }
     570}
    148571
    149572QSqlDatabase DADeepThoughtStorage::connection() const {
     
    153576    return QSqlQuery(connection());
    154577}
    155 
    156 
    157 int DADeepThoughtStorage::_getQuestionId(const Question &question) {
    158     QSqlQuery query = createQuery();
    159 
    160     query.prepare("SELECT id "
    161                     "FROM t_question "
    162                     "WHERE (nrNodeGuId = :nrNodeGuId) "
    163                       "AND (eventTarget = :eventTarget);");
    164     query.bindValue(":nrNodeGuId", question.nrNodeGuId());
    165     query.bindValue(":eventTarget", question.eventTarget());
    166 
    167     if (!query.exec()) {
    168         EException(QObject::tr("Could not fetch the question from the database."))
    169              .addDebugInfo("sql-query", query.lastQuery())
    170              .addDebugInfo("sql-response", query.lastError().text())
    171              .raise();
    172     }
    173 
    174     if (query.next()) {
    175         return query.value(0).toInt();
    176     } else {
    177         return 0;
    178     }
    179 }
    180 
    181 void DADeepThoughtStorage::updateQuestion(Question &question) {
    182     QSqlQuery query = createQuery();
    183     if (question.id() > 0) {
    184         query.prepare("UPDATE t_question SET "
    185                           "language=:language, "
    186                           "questionText=:questionText "
    187                         "WHERE (id = :id); ");
    188         query.bindValue(":id", question.id());
    189     } else {
    190         query.prepare("INSERT INTO t_question (nrNodeGuId, eventTarget, language, questionText) "
    191                         "VALUES(:nrNodeGuId, :eventTarget, :language, :questionText); ");
    192         query.bindValue(":nrNodeGuId", question.nrNodeGuId());
    193         query.bindValue(":eventTarget", question.eventTarget());
    194     }
    195     query.bindValue(":language", question.language());
    196     query.bindValue(":questionText", question.text());
    197 
    198     if (!query.exec()) {
    199         EException(QObject::tr("Failed to update/insert the question in the database."))
    200              .addDebugInfo("sql-query", query.lastQuery())
    201              .addDebugInfo("sql-response", query.lastError().text())
    202              .raise();
    203     }
    204 
    205     if (question.id() <= 0) {
    206         int questionId = _getQuestionId(question);
    207         if (questionId > 0) {
    208             question.setId(questionId);
    209         } else {
    210             EException(QObject::tr("Failed to read the inserted question from the database."))
    211                  .addDebugInfo("sql-query", query.lastQuery())
    212                  .addDebugInfo("sql-response", query.lastError().text())
    213                  .raise();
    214         }
    215     }
    216 
    217     // Update the attached answers
    218     QList<QString> attachedAnswers;
    219     foreach(Answer answer, question.answers()) {
    220         updateAnswer(answer);
    221         attachedAnswers.append(answer.id());
    222     }
    223 
    224     // Remove old answers
    225     foreach (Answer answer, getAnswers(question)) {
    226         if (!attachedAnswers.contains(answer.id())) {
    227             query.prepare("DELETE FROM t_answer "
    228                             "WHERE (questionId = :questionId) "
    229                               "AND (id = :id);");
    230             query.bindValue(":questionId", question.id());
    231             query.bindValue(":id", answer.id());
    232 
    233             if (!query.exec()) {
    234                 EException(QObject::tr("Failed to remove an old answer of a question from the database."))
    235                      .addDebugInfo("sql-query", query.lastQuery())
    236                      .addDebugInfo("sql-response", query.lastError().text())
    237                      .addDebugInfo("question", question.id())
    238                      .addDebugInfo("answer", answer.id())
    239                      .raise();
    240             }
    241         }
    242     }
    243 }
    244 
    245 void DADeepThoughtStorage::updateAnswer(const Answer &answer) {
    246     QSqlQuery query = createQuery();
    247 
    248     if (answer.question().id() <= 0) {
    249         EException(QObject::tr("The question of this answer is not yet in the database. Please insert it first."))
    250              .raise();
    251     }
    252 
    253     query.prepare("SELECT id "
    254                     "FROM t_answer "
    255                     "WHERE (id = :id) "
    256                       "AND (questionId = :questionId);");
    257     query.bindValue(":id", answer.id());
    258     query.bindValue(":questionId", answer.question().id());
    259 
    260     if (query.exec() && query.next()) {
    261         query.prepare("UPDATE t_answer SET answerText=:answerText, answered=:answered, correct=:correct, deepThoughtKnowsCorrectness=:deepThought "
    262                         "WHERE (id = :id) "
    263                           "AND (questionId = :questionId);");
    264     } else {
    265         query.prepare("INSERT INTO t_answer (id, questionId, answerText, answered, correct, deepThoughtKnowsCorrectness) "
    266                         "VALUES (:id, :questionId, :answerText, :answered, :correct, :deepThought); ");
    267     }
    268     query.bindValue(":id", answer.id());
    269     query.bindValue(":questionId", answer.question().id());
    270     query.bindValue(":answerText", answer.text());
    271     query.bindValue(":answered", answer.isAnswered());
    272     query.bindValue(":correct", answer.isCorrect());
    273     query.bindValue(":deepThought", answer.doesDeepThoughtKnowCorrectness());
    274 
    275     if (!query.exec()) {
    276         EException(QObject::tr("Failed to update the answer in the database."))
    277              .addDebugInfo("sql-query", query.lastQuery())
    278              .addDebugInfo("sql-response", query.lastError().text())
    279              .raise();
    280     }
    281 }
    282 
    283 void DADeepThoughtStorage::removeQuestion(Question &question) {
    284     QSqlQuery query = createQuery();
    285 
    286     if (question.id() < 0) {
    287         query.prepare("DELETE FROM t_question "
    288                         "WHERE (nrNodeGuId = :nrNodeGuId) "
    289                           "AND (eventTarget = :eventTarget);");
    290         query.bindValue(":nrNodeGuId", question.nrNodeGuId());
    291         query.bindValue(":eventTarget", question.eventTarget());
    292     } else {
    293         query.prepare("DELETE FROM t_question "
    294                         "WHERE (id = :id);");
    295         query.bindValue(":id", question.id());
    296     }
    297 
    298     if (!query.exec()) {
    299         EException(QObject::tr("Could not remove the question from the database."))
    300              .addDebugInfo("sql-query", query.lastQuery())
    301              .addDebugInfo("sql-response", query.lastError().text())
    302              .raise();
    303     }
    304     question.setId(0);
    305 }
    306 
    307 QList<Question> DADeepThoughtStorage::getQuestions() {
    308     QSqlQuery query = createQuery();
    309 
    310     if (!query.exec("SELECT id "
    311                       "FROM t_question; ")) {
    312         qWarning() << query.lastError().text();
    313         EException(QObject::tr("Could not get the questions from the database."))
    314             .addDebugInfo("sql-query", query.lastQuery())
    315             .addDebugInfo("sql-response", query.lastError().text())
    316             .raise();
    317     }
    318 
    319     QList<Question> questions;
    320     while (query.next()) {
    321         questions.append(getQuestion(query.value(0).toInt()));
    322     }
    323 
    324     return questions;
    325 }
    326 Question DADeepThoughtStorage::getQuestion(int questionId) {
    327     QSqlQuery query = createQuery();
    328 
    329     query.prepare("SELECT nrNodeGuId, eventTarget, language, questionText "
    330                     "FROM t_question "
    331                     "WHERE (id = :id);");
    332     query.bindValue(":id", questionId);
    333     if (!query.exec()) {
    334         qWarning() << query.lastError().text();
    335         EException(QObject::tr("Could not get the question from the database."))
    336             .addDebugInfo("sql-query", query.lastQuery())
    337             .addDebugInfo("sql-response", query.lastError().text())
    338             .addDebugInfo("questionId", questionId)
    339             .raise();
    340     }
    341     if (!query.next()) {
    342         EException(QObject::tr("The requested question could not be found in the database."))
    343             .addDebugInfo("questionId", questionId)
    344             .raise();
    345     }
    346 
    347     Question question;
    348     question.setId(questionId);
    349     question.setNrNodeGuId(query.value(0).toString());
    350     question.setEventTarget(query.value(1).toString());
    351     question.setLanguage(query.value(2).toString());
    352     question.setText(query.value(3).toString());
    353 
    354     question.setAnswers(getAnswers(question));
    355 
    356     return question;
    357 }
    358 
    359 QList<Answer> DADeepThoughtStorage::getAnswers(const Question &question) {
    360     QSqlQuery query = createQuery();
    361 
    362     query.prepare("SELECT id "
    363                        "FROM t_answer "
    364                        "WHERE (questionId = :questionId); ");
    365     query.bindValue(":questionId", question.id());
    366 
    367     if (!query.exec()) {
    368         EException(QObject::tr("Failed to fetch the answers of the question from the database."))
    369              .addDebugInfo("sql-query", query.lastQuery())
    370              .addDebugInfo("sql-response", query.lastError().text())
    371              .addDebugInfo("questionId", question.id())
    372              .raise();
    373     }
    374 
    375     QList<Answer> answers;
    376     while (query.next()) {
    377         answers.append(getAnswer(question, query.value(0).toString()));
    378     }
    379 
    380     return answers;
    381 }
    382 Answer DADeepThoughtStorage::getAnswer(const Question &question, const QString &answerId) {
    383     QSqlQuery query = createQuery();
    384 
    385     query.prepare("SELECT answerText, answered, correct, deepThoughtKnowsCorrectness "
    386                     "FROM t_answer "
    387                     "WHERE (questionId = :questionId) "
    388                       "AND (id = :id);");
    389     query.bindValue(":questionId", question.id());
    390     query.bindValue(":id", answerId);
    391 
    392     if (!query.exec()) {
    393         EException(QObject::tr("Could not get the answer from the database."))
    394             .addDebugInfo("sql-query", query.lastQuery())
    395             .addDebugInfo("sql-response", query.lastError().text())
    396             .addDebugInfo("questionId", question.id())
    397             .addDebugInfo("answerId", answerId)
    398             .raise();
    399     }
    400     if (!query.next()) {
    401         EException(QObject::tr("The requested answer could not be found in the database."))
    402             .addDebugInfo("questionId", question.id())
    403             .addDebugInfo("answerId", answerId)
    404             .raise();
    405     }
    406 
    407     Answer answer(question);
    408     answer.setId(answerId);
    409     answer.setText(query.value(0).toString());
    410     answer.setAnswered(query.value(1).toBool());
    411     answer.setCorrect(query.value(2).toBool());
    412     answer.setDeepThoughtKnowsCorrectness(query.value(3).toBool());
    413 
    414     return answer;
    415 }
    416 
    417 /**
    418  * Returns the status of this question at the given schoolnet account.
    419  * ATTENTION: This does not check the real state of the question at schoolnet. It justs reads
    420  *            the state from the database. When the user answered some questions directly at
    421  *            schoolnet, it won't be detected here.
    422  *
    423  * @param account The schoolnet account
    424  * @param question The question
    425  * @return @see Question::Status
    426  */
    427 uint DADeepThoughtStorage::questionStatusAtSchoolnet(BCAccount *account, const Question &question) const {
    428     QSqlQuery query = createQuery();
    429 
    430     query.prepare("SELECT status "
    431                     "FROM t_status_at_schoolnet "
    432                     "WHERE (account = :account) "
    433                       "AND (questionId = :questionId);");
    434     query.bindValue(":account", account->username());
    435     query.bindValue(":questionId", question.id());
    436 
    437     if (!query.exec()) {
    438         EException("Could not read the schoolnet-status of this question from the database.")
    439                 .addDebugInfo("sql-query", query.lastQuery())
    440                 .addDebugInfo("sql-response", query.lastError().text())
    441                 .addDebugInfo("accountId", account->username())
    442                 .addDebugInfo("questionId", question.id())
    443                 .raise();
    444     }
    445 
    446     if (!query.next()) {
    447         return 0;
    448     } else {
    449         return query.value(0).toInt();
    450     }
    451 }
    452 
    453 void DADeepThoughtStorage::setQuestionStatusAtSchoolnet(BCAccount *account, const Question &question, uint status) const {
    454     QSqlQuery query = createQuery();
    455 
    456     query.prepare("REPLACE INTO t_status_at_schoolnet (account, questionId, status) VALUES (:account, :questionId, :status);");
    457     query.bindValue(":account", account->username());
    458     query.bindValue(":questionId", question.id());
    459     query.bindValue(":status", status);
    460 
    461     if (!query.exec()) {
    462         EException(QObject::tr("Failed to update/insert the question status in the database."))
    463                 .addDebugInfo("sql-query", query.lastQuery())
    464                 .addDebugInfo("sql-response", query.lastError().text())
    465                 .addDebugInfo("accountId", account->username())
    466                 .addDebugInfo("questionId", question.id())
    467                 .raise();
    468     }
    469 }
    470 
    471 /**
    472  * Returns true, if the given status of the given question has been checked for the given schoolnet account.
    473  * @see {@link #getQuestionStatusAtSchoolnet(BCAccount*, const Question&) const}
    474  *
    475  * @param account The schoolnet account
    476  * @param question The question
    477  * @return True, If the question is answered
    478  */
    479 bool DADeepThoughtStorage::isQuestionStatusAtSchoolnetChecked(BCAccount *account, const Question &question) const {
    480     return questionStatusAtSchoolnet(account, question) & Question::StatusAtSchoolnetChecked;
    481 }
    482 
    483 /**
    484  * Marks the given question status as checked from schoolnet.
    485  *
    486  * @param account The schoolnet account
    487  * @param question The question
    488  */
    489 void DADeepThoughtStorage::setQuestionStatusAtSchoolnetChecked(BCAccount *account, const Question &question) const {
    490     uint newStatus = questionStatusAtSchoolnet(account, question) | Question::StatusAtSchoolnetChecked;
    491     setQuestionStatusAtSchoolnet(account, question, newStatus);
    492 }
    493 
    494 
    495 /**
    496  * Returns true, if the given question is already answered at schoolnet for the given account.
    497  * @see {@link #getQuestionStatusAtSchoolnet(BCAccount*, const Question&) const}
    498  *
    499  * @param account The schoolnet account
    500  * @param question The question
    501  * @return True, If the question is answered
    502  */
    503 bool DADeepThoughtStorage::isQuestionAnsweredAtSchoolnet(BCAccount *account, const Question &question) const {
    504     return questionStatusAtSchoolnet(account, question) & Question::AnsweredAtSchoolnet;
    505 }
    506 
    507 /**
    508  * Marks the given question as answered at schoolnet and that the status has been checked as well.
    509  *
    510  * @param account The schoolnet account
    511  * @param question The question
    512  */
    513 void DADeepThoughtStorage::setQuestionAnsweredAtSchoolnet(BCAccount *account, const Question &question) const {
    514     uint newStatus = questionStatusAtSchoolnet(account, question)
    515             | Question::AnsweredAtSchoolnet
    516             | Question::StatusAtSchoolnetChecked;
    517     setQuestionStatusAtSchoolnet(account, question, newStatus);
    518 }
    519 
    520 
    521 QDateTime DADeepThoughtStorage::lastDeepThoughtRun() {
    522     QSqlQuery query = createQuery();
    523 
    524     if (!query.exec("SELECT lastrun "
    525                       "FROM t_lastrun "
    526                       "ORDER BY lastrun DESC;"))
    527     {
    528         EException(QObject::tr("Coult not get the time of the last DeepThought run."))
    529                     .addDebugInfo("sql-query", query.lastQuery())
    530                     .addDebugInfo("sql-response", query.lastError().text())
    531                     .raise();
    532     }
    533 
    534     QDateTime lastRun;
    535     if (query.next()) {
    536         lastRun = QDateTime::fromTime_t(query.value(0).toInt());
    537     }
    538     return lastRun;
    539 }
    540 void DADeepThoughtStorage::setLastDeepThoughtRun(QDateTime lastrun) {
    541     uint lr = lastrun.toTime_t();
    542 
    543     QSqlQuery query = createQuery();
    544 
    545     // Add new lastrun entry
    546     query.prepare("INSERT INTO t_lastrun (lastrun) VALUES (:lastrun);");
    547     query.bindValue(":lastrun", lr);
    548     if (!query.exec()) {
    549         EException(QObject::tr("Coult not save the time of the last DeepThought run."))
    550                     .addDebugInfo("sql-query", query.lastQuery())
    551                     .addDebugInfo("sql-response", query.lastError().text())
    552                     .raise();
    553     }
    554     query.clear();
    555 
    556     // Remove old lastrun entries
    557     query.prepare("DELETE "
    558                     "FROM t_lastrun "
    559                     "WHERE (lastrun < :lastrun);");
    560     query.bindValue(":lastrun", lr);
    561     query.exec();
    562 }
    563 
    564 }
     578}
  • gateways/Schoolnet/src/persistence/deepthought/dadeepthoughtstorage.h

    r220 r226  
    7878    void version1To2();
    7979    void version2To3();
     80    void version3To4();
    8081
    8182private:
  • gateways/Schoolnet/src/ui/models/questiontreemodel.cpp

    r222 r226  
    4949        items_.clear();
    5050        foreach (Question question, QuestionManager::instance()->questions()) {
     51            if (!question.isAlive()) {
     52                continue;
     53            }
     54
    5155            if (isQuestionStatusAtSchoolnetCheckedAtSomeLoggedInAccount(question)) {
    5256                items_.append(question);
Note: See TracChangeset for help on using the changeset viewer.