1 /*
2     SPDX-FileCopyrightText: 2013-2016 Andreas Cord-Landwehr <cordlandwehr@kde.org>
3 
4     SPDX-License-Identifier: LGPL-2.1-only OR LGPL-3.0-only OR LicenseRef-KDE-Accepted-LGPL
5 */
6 
7 #include "storage.h"
8 #include "learner.h"
9 #include "liblearner_debug.h"
10 
11 #include <KLocalizedString>
12 
13 #include <QDateTime>
14 #include <QDir>
15 #include <QSqlDatabase>
16 #include <QSqlError>
17 #include <QSqlQuery>
18 #include <QStandardPaths>
19 
20 using namespace LearnerProfile;
21 
Storage(QObject * parent)22 Storage::Storage(QObject *parent)
23     : QObject(parent)
24     , m_databasePath(QStandardPaths::writableLocation(QStandardPaths::DataLocation) + QLatin1Char('/') + "learnerdata.db")
25     , m_errorMessage(QString())
26 {
27 }
28 
Storage(const QString & databasePath,QObject * parent)29 Storage::Storage(const QString &databasePath, QObject *parent)
30     : QObject(parent)
31     , m_databasePath(databasePath)
32     , m_errorMessage(QString())
33 {
34     qCDebug(LIBLEARNER_LOG) << "Initialize with custom DB path:" << m_databasePath;
35 }
36 
errorMessage() const37 QString Storage::errorMessage() const
38 {
39     return m_errorMessage;
40 }
41 
raiseError(const QSqlError & error)42 void Storage::raiseError(const QSqlError &error)
43 {
44     m_errorMessage = QStringLiteral("%1 : %2").arg(error.driverText(), error.databaseText());
45     emit errorMessageChanged();
46 }
47 
storeProfile(Learner * learner)48 bool Storage::storeProfile(Learner *learner)
49 {
50     QSqlDatabase db = database();
51 
52     // test whether ID is present
53     QSqlQuery idExistsQuery(db);
54     idExistsQuery.prepare(QStringLiteral("SELECT COUNT(*) FROM profiles WHERE id = :id"));
55     idExistsQuery.bindValue(QStringLiteral(":id"), learner->identifier());
56     idExistsQuery.exec();
57     if (db.lastError().isValid()) {
58         qCritical() << "ExistsQuery: " << db.lastError().text();
59         raiseError(db.lastError());
60         return false;
61     }
62     // go to first result row that contains the count
63     idExistsQuery.next();
64     if (idExistsQuery.value(0).toInt() < 1) {
65         // in case learner ID is not found in database
66         QSqlQuery insertProfileQuery(db);
67         insertProfileQuery.prepare(QStringLiteral("INSERT INTO profiles (id, name) VALUES (?, ?)"));
68         insertProfileQuery.bindValue(0, learner->identifier());
69         insertProfileQuery.bindValue(1, learner->name());
70         insertProfileQuery.exec();
71 
72         if (insertProfileQuery.lastError().isValid()) {
73             raiseError(insertProfileQuery.lastError());
74             db.rollback();
75             return false;
76         }
77     } else {
78         // update name otherwise
79         QSqlQuery updateProfileQuery(db);
80         updateProfileQuery.prepare(QStringLiteral("UPDATE profiles SET name = :name WHERE id = :id"));
81         updateProfileQuery.bindValue(QStringLiteral(":id"), learner->identifier());
82         updateProfileQuery.bindValue(QStringLiteral(":name"), learner->name());
83         updateProfileQuery.exec();
84         if (updateProfileQuery.lastError().isValid()) {
85             qCritical() << updateProfileQuery.lastError().text();
86             raiseError(updateProfileQuery.lastError());
87             db.rollback();
88             return false;
89         }
90     }
91 
92     // store existing learning goal relations
93     foreach (LearningGoal *goal, learner->goals()) {
94         QSqlQuery relationExistsQuery(db);
95         relationExistsQuery.prepare(
96             "SELECT COUNT(*) FROM learner_goals "
97             "WHERE goal_category = :goalCategory "
98             "AND goal_identifier = :goalIdentifier "
99             "AND profile_id = :profileId ");
100         relationExistsQuery.bindValue(QStringLiteral(":goalCategory"), goal->category());
101         relationExistsQuery.bindValue(QStringLiteral(":goalIdentifier"), goal->identifier());
102         relationExistsQuery.bindValue(QStringLiteral(":profileId"), learner->identifier());
103         relationExistsQuery.exec();
104         if (db.lastError().isValid()) {
105             qCritical() << "ExistsQuery: " << db.lastError().text();
106             raiseError(db.lastError());
107             return false;
108         }
109         // go to first result row that contains the count
110         relationExistsQuery.next();
111         if (relationExistsQuery.value(0).toInt() < 1) {
112             QSqlQuery insertProfileQuery(db);
113             insertProfileQuery.prepare(QStringLiteral("INSERT INTO learner_goals (goal_category, goal_identifier, profile_id) VALUES (?, ?, ?)"));
114             insertProfileQuery.bindValue(0, goal->category());
115             insertProfileQuery.bindValue(1, goal->identifier());
116             insertProfileQuery.bindValue(2, learner->identifier());
117             insertProfileQuery.exec();
118         }
119     }
120     // remove deleted relations
121     QSqlQuery cleanupRelations(db);
122     cleanupRelations.prepare(QStringLiteral("DELETE FROM learner_goals WHERE "));
123     // TODO change creation of relations to same way as remove-relations: explicit connections
124 
125     return true;
126 }
127 
removeProfile(Learner * learner)128 bool Storage::removeProfile(Learner *learner)
129 {
130     QSqlDatabase db = database();
131     QSqlQuery removeProfileQuery(db);
132 
133     // delete learner
134     removeProfileQuery.prepare(QStringLiteral("DELETE FROM profiles WHERE id = ?"));
135     removeProfileQuery.bindValue(0, learner->identifier());
136     removeProfileQuery.exec();
137 
138     if (removeProfileQuery.lastError().isValid()) {
139         qCritical() << removeProfileQuery.lastError().text();
140         raiseError(removeProfileQuery.lastError());
141         db.rollback();
142         return false;
143     }
144 
145     // delete learning goal relations
146     QSqlQuery removeGoalRelationQuery(db);
147     removeGoalRelationQuery.prepare(QStringLiteral("DELETE FROM learner_goals WHERE profile_id = ?"));
148     removeGoalRelationQuery.bindValue(0, learner->identifier());
149     removeGoalRelationQuery.exec();
150 
151     if (removeGoalRelationQuery.lastError().isValid()) {
152         qCritical() << removeGoalRelationQuery.lastError().text();
153         raiseError(removeGoalRelationQuery.lastError());
154         db.rollback();
155         return false;
156     }
157 
158     return true;
159 }
160 
removeRelation(Learner * learner,LearningGoal * goal)161 bool Storage::removeRelation(Learner *learner, LearningGoal *goal)
162 {
163     QSqlDatabase db = database();
164     QSqlQuery removeGoalRelationQuery(db);
165     removeGoalRelationQuery.prepare(
166         "DELETE FROM learner_goals "
167         "WHERE goal_category = :goalCategory "
168         "AND goal_identifier = :goalIdentifier "
169         "AND profile_id = :profileId ");
170     removeGoalRelationQuery.bindValue(QStringLiteral(":goalCategory"), goal->category());
171     removeGoalRelationQuery.bindValue(QStringLiteral(":goalIdentifier"), goal->identifier());
172     removeGoalRelationQuery.bindValue(QStringLiteral(":profileId"), learner->identifier());
173     removeGoalRelationQuery.exec();
174     if (db.lastError().isValid()) {
175         qCritical() << "ExistsQuery: " << db.lastError().text();
176         raiseError(db.lastError());
177         return false;
178     }
179 
180     return true;
181 }
182 
loadProfiles(QList<LearningGoal * > goals)183 QList<Learner *> Storage::loadProfiles(QList<LearningGoal *> goals)
184 {
185     QSqlDatabase db = database();
186     QSqlQuery profileQuery(db);
187     profileQuery.prepare(QStringLiteral("SELECT id, name FROM profiles"));
188     profileQuery.exec();
189     if (profileQuery.lastError().isValid()) {
190         qCritical() << profileQuery.lastError().text();
191         raiseError(profileQuery.lastError());
192         return QList<Learner *>();
193     }
194     QList<Learner *> profiles;
195     while (profileQuery.next()) {
196         Learner *profile = new Learner();
197         profile->setIdentifier(profileQuery.value(0).toInt());
198         profile->setName(profileQuery.value(1).toString());
199         profiles.append(profile);
200     }
201 
202     // associate to goals
203     QSqlQuery goalRelationQuery(db);
204     goalRelationQuery.prepare(QStringLiteral("SELECT goal_category, goal_identifier, profile_id FROM learner_goals"));
205     goalRelationQuery.exec();
206     if (goalRelationQuery.lastError().isValid()) {
207         qCritical() << goalRelationQuery.lastError().text();
208         raiseError(goalRelationQuery.lastError());
209         return QList<Learner *>();
210     }
211     while (goalRelationQuery.next()) {
212         Learner *learner = nullptr;
213         LearningGoal *goal = nullptr;
214 
215         foreach (Learner *cmpProfile, profiles) {
216             if (cmpProfile->identifier() == goalRelationQuery.value(2).toInt()) {
217                 learner = cmpProfile;
218                 break;
219             }
220         }
221         if (!learner) {
222             qCCritical(LIBLEARNER_LOG) << "Could not retrieve learner from database.";
223             return QList<Learner *>();
224         }
225         foreach (LearningGoal *cmpGoal, goals) {
226             if (cmpGoal->category() == goalRelationQuery.value(0).toInt() && cmpGoal->identifier() == goalRelationQuery.value(1).toString()) {
227                 goal = cmpGoal;
228                 break;
229             }
230         }
231 
232         if (learner->goals().contains(goal)) {
233             continue;
234         }
235         if (goal) {
236             learner->addGoal(goal);
237         }
238     }
239 
240     return profiles;
241 }
242 
storeGoal(LearningGoal * goal)243 bool Storage::storeGoal(LearningGoal *goal)
244 {
245     QSqlDatabase db = database();
246 
247     // test whether ID is present
248     QSqlQuery goalExistsQuery(db);
249     goalExistsQuery.prepare(QStringLiteral("SELECT COUNT(*) FROM goals WHERE category = :category AND identifier = :identifier"));
250     goalExistsQuery.bindValue(QStringLiteral(":identifier"), goal->identifier());
251     goalExistsQuery.bindValue(QStringLiteral(":category"), static_cast<int>(goal->category()));
252     goalExistsQuery.exec();
253     if (db.lastError().isValid()) {
254         qCritical() << "ExistsQuery: " << db.lastError().text();
255         raiseError(db.lastError());
256         return false;
257     }
258     // go to first result row that contains the count
259     goalExistsQuery.next();
260     if (goalExistsQuery.value(0).toInt() < 1) {
261         // in case learner ID is not found in database
262         QSqlQuery insertGoalQuery(db);
263         insertGoalQuery.prepare(QStringLiteral("INSERT INTO goals (category, identifier, name) VALUES (?, ?, ?)"));
264         insertGoalQuery.bindValue(0, static_cast<int>(goal->category()));
265         insertGoalQuery.bindValue(1, goal->identifier());
266         insertGoalQuery.bindValue(2, goal->name());
267         insertGoalQuery.exec();
268         if (insertGoalQuery.lastError().isValid()) {
269             raiseError(insertGoalQuery.lastError());
270             db.rollback();
271             return false;
272         }
273         return true;
274     } else {
275         // update name otherwise
276         QSqlQuery updateGoalQuery(db);
277         updateGoalQuery.prepare(QStringLiteral("UPDATE goals SET name = :name WHERE category = :category AND identifier = :identifier"));
278         updateGoalQuery.bindValue(QStringLiteral(":category"), static_cast<int>(goal->category()));
279         updateGoalQuery.bindValue(QStringLiteral(":identifier"), goal->identifier());
280         updateGoalQuery.bindValue(QStringLiteral(":name"), goal->name());
281         updateGoalQuery.exec();
282         if (updateGoalQuery.lastError().isValid()) {
283             qCritical() << updateGoalQuery.lastError().text();
284             raiseError(updateGoalQuery.lastError());
285             db.rollback();
286             return false;
287         }
288         return true;
289     }
290 }
291 
loadGoals()292 QList<LearningGoal *> Storage::loadGoals()
293 {
294     QSqlDatabase db = database();
295     QSqlQuery goalQuery(db);
296     goalQuery.prepare(QStringLiteral("SELECT category, identifier, name FROM goals"));
297     goalQuery.exec();
298     if (goalQuery.lastError().isValid()) {
299         qCritical() << goalQuery.lastError().text();
300         raiseError(goalQuery.lastError());
301         return QList<LearningGoal *>();
302     }
303 
304     QList<LearningGoal *> goals;
305     while (goalQuery.next()) {
306         LearningGoal::Category category = static_cast<LearningGoal::Category>(goalQuery.value(0).toInt());
307         QString identifier = goalQuery.value(1).toString();
308         QString name = goalQuery.value(2).toString();
309         LearningGoal *goal = new LearningGoal(category, identifier);
310         goal->setName(name);
311         goals.append(goal);
312     }
313     return goals;
314 }
315 
storeProgressLog(Learner * learner,LearningGoal * goal,const QString & container,const QString & item,int payload,const QDateTime & time)316 bool Storage::storeProgressLog(Learner *learner, LearningGoal *goal, const QString &container, const QString &item, int payload, const QDateTime &time)
317 {
318     QSqlDatabase db = database();
319     QSqlQuery insertQuery(db);
320     insertQuery.prepare(
321         "INSERT INTO learner_progress_log "
322         "(goal_category, goal_identifier, profile_id, item_container, item, payload, date) "
323         "VALUES (:gcategory, :gidentifier, :pid, :container, :item, :payload, :date)");
324     insertQuery.bindValue(QStringLiteral(":gcategory"), static_cast<int>(goal->category()));
325     insertQuery.bindValue(QStringLiteral(":gidentifier"), goal->identifier());
326     insertQuery.bindValue(QStringLiteral(":pid"), learner->identifier());
327     insertQuery.bindValue(QStringLiteral(":container"), container);
328     insertQuery.bindValue(QStringLiteral(":item"), item);
329     insertQuery.bindValue(QStringLiteral(":payload"), payload);
330     insertQuery.bindValue(QStringLiteral(":date"), time.toString(Qt::ISODate));
331     insertQuery.exec();
332 
333     if (insertQuery.lastError().isValid()) {
334         raiseError(insertQuery.lastError());
335         qCCritical(LIBLEARNER_LOG) << "DB Error:" << m_errorMessage;
336         db.rollback();
337         return false;
338     }
339     return true;
340 }
341 
readProgressLog(Learner * learner,LearningGoal * goal,const QString & container,const QString & item)342 QList<QPair<QDateTime, int>> Storage::readProgressLog(Learner *learner, LearningGoal *goal, const QString &container, const QString &item)
343 {
344     QSqlDatabase db = database();
345     QSqlQuery logQuery(db);
346     logQuery.prepare(
347         "SELECT date, payload FROM learner_progress_log "
348         "WHERE goal_category = :goalcategory "
349         "AND goal_identifier = :goalid "
350         "AND profile_id = :profileid "
351         "AND item_container = :container "
352         "AND item = :item");
353     logQuery.bindValue(QStringLiteral(":goalcategory"), static_cast<int>(goal->category()));
354     logQuery.bindValue(QStringLiteral(":goalid"), goal->identifier());
355     logQuery.bindValue(QStringLiteral(":profileid"), learner->identifier());
356     logQuery.bindValue(QStringLiteral(":container"), container);
357     logQuery.bindValue(QStringLiteral(":item"), item);
358     logQuery.exec();
359     if (logQuery.lastError().isValid()) {
360         qCritical() << logQuery.lastError().text();
361         raiseError(logQuery.lastError());
362         return QList<QPair<QDateTime, int>>();
363     }
364 
365     QList<QPair<QDateTime, int>> log;
366     while (logQuery.next()) {
367         const QDateTime date {logQuery.value(0).toDateTime()};
368         int payload {logQuery.value(1).toInt()};
369         log.append(qMakePair(date, payload));
370     }
371     return log;
372 }
373 
storeProgressValue(Learner * learner,LearningGoal * goal,const QString & container,const QString & item,int payload)374 bool Storage::storeProgressValue(Learner *learner, LearningGoal *goal, const QString &container, const QString &item, int payload)
375 {
376     QSqlDatabase db = database();
377     QSqlQuery query(db);
378 
379     // test if already payload stored
380     query.prepare(
381         "SELECT payload FROM learner_progress_value "
382         "WHERE goal_category = :gcategory "
383         "AND goal_identifier = :gidentifier "
384         "AND profile_id = :pid "
385         "AND item_container = :container "
386         "AND item = :item");
387     query.bindValue(QStringLiteral(":gcategory"), static_cast<int>(goal->category()));
388     query.bindValue(QStringLiteral(":gidentifier"), goal->identifier());
389     query.bindValue(QStringLiteral(":pid"), learner->identifier());
390     query.bindValue(QStringLiteral(":container"), container);
391     query.bindValue(QStringLiteral(":item"), item);
392     query.exec();
393     if (query.lastError().isValid()) {
394         qCritical() << query.lastError().text();
395         raiseError(query.lastError());
396         return false;
397     }
398     // if query contains values, perform update query
399     if (query.next()) {
400         query.finish(); // release resources from previous query
401         query.prepare(
402             "UPDATE learner_progress_value "
403             "SET payload = :payload "
404             "WHERE goal_category = :gcategory "
405             "AND goal_identifier = :gidentifier "
406             "AND profile_id = :pid "
407             "AND item_container = :container "
408             "AND item = :item");
409         query.bindValue(QStringLiteral(":payload"), static_cast<int>(payload));
410         query.bindValue(QStringLiteral(":gcategory"), static_cast<int>(goal->category()));
411         query.bindValue(QStringLiteral(":gidentifier"), goal->identifier());
412         query.bindValue(QStringLiteral(":pid"), learner->identifier());
413         query.bindValue(QStringLiteral(":container"), container);
414         query.bindValue(QStringLiteral(":item"), item);
415         query.exec();
416 
417         if (query.lastError().isValid()) {
418             qCritical() << query.lastError().text();
419             raiseError(query.lastError());
420             db.rollback();
421             return false;
422         }
423         return true;
424     }
425     // else insert new row
426     else {
427         query.finish(); // release resources from previous query
428         query.prepare(
429             "INSERT INTO learner_progress_value "
430             "(goal_category, goal_identifier, profile_id, item_container, item, payload) "
431             "VALUES (:gcategory, :gidentifier, :pid, :container, :item, :payload)");
432         query.bindValue(QStringLiteral(":gcategory"), static_cast<int>(goal->category()));
433         query.bindValue(QStringLiteral(":gidentifier"), goal->identifier());
434         query.bindValue(QStringLiteral(":pid"), learner->identifier());
435         query.bindValue(QStringLiteral(":container"), container);
436         query.bindValue(QStringLiteral(":item"), item);
437         query.bindValue(QStringLiteral(":payload"), static_cast<int>(payload));
438         query.exec();
439 
440         if (query.lastError().isValid()) {
441             qCritical() << query.lastError().text();
442             raiseError(query.lastError());
443             db.rollback();
444             return false;
445         }
446         return true;
447     }
448     Q_UNREACHABLE();
449     return false;
450 }
451 
readProgressValues(Learner * learner,LearningGoal * goal,const QString & container)452 QHash<QString, int> Storage::readProgressValues(Learner *learner, LearningGoal *goal, const QString &container)
453 {
454     QSqlDatabase db = database();
455     QSqlQuery query(db);
456     query.prepare(
457         "SELECT item, payload FROM learner_progress_value "
458         "WHERE goal_category = :goalcategory "
459         "AND goal_identifier = :goalid "
460         "AND profile_id = :profileid "
461         "AND item_container = :container");
462     query.bindValue(QStringLiteral(":goalcategory"), static_cast<int>(goal->category()));
463     query.bindValue(QStringLiteral(":goalid"), goal->identifier());
464     query.bindValue(QStringLiteral(":profileid"), learner->identifier());
465     query.bindValue(QStringLiteral(":container"), container);
466     query.exec();
467     if (query.lastError().isValid()) {
468         qCritical() << query.lastError().text();
469         raiseError(query.lastError());
470         return QHash<QString, int>();
471     }
472 
473     QHash<QString, int> values;
474     while (query.next()) {
475         const QString item {query.value(0).toString()};
476         const int payload {query.value(1).toInt()};
477         values.insert(item, payload);
478     }
479     return values;
480 }
481 
readProgressValue(Learner * learner,LearningGoal * goal,const QString & container,const QString & item)482 int Storage::readProgressValue(Learner *learner, LearningGoal *goal, const QString &container, const QString &item)
483 {
484     QSqlDatabase db = database();
485     QSqlQuery query(db);
486     query.prepare(
487         "SELECT payload FROM learner_progress_value "
488         "WHERE goal_category = :goalcategory "
489         "AND goal_identifier = :goalid "
490         "AND profile_id = :profileid "
491         "AND item_container = :container "
492         "AND item = :item");
493     query.bindValue(QStringLiteral(":goalcategory"), static_cast<int>(goal->category()));
494     query.bindValue(QStringLiteral(":goalid"), goal->identifier());
495     query.bindValue(QStringLiteral(":profileid"), learner->identifier());
496     query.bindValue(QStringLiteral(":container"), container);
497     query.bindValue(QStringLiteral(":item"), item);
498     query.exec();
499     if (query.lastError().isValid()) {
500         qCritical() << query.lastError().text();
501         raiseError(query.lastError());
502         return -1;
503     }
504 
505     if (query.next()) {
506         return query.value(0).toInt();
507     }
508     return -1;
509 }
510 
database()511 QSqlDatabase Storage::database()
512 {
513     if (QSqlDatabase::contains(QSqlDatabase::defaultConnection)) {
514         return QSqlDatabase::database(QSqlDatabase::defaultConnection);
515     }
516 
517     // create data directory if it does not exist
518     QDir dir = QDir(QStandardPaths::writableLocation(QStandardPaths::DataLocation));
519     if (!dir.exists()) {
520         dir.mkpath(QStandardPaths::writableLocation(QStandardPaths::DataLocation));
521     }
522     qCDebug(LIBLEARNER_LOG) << "Database path: " << m_databasePath;
523 
524     QSqlDatabase db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"));
525     db.setDatabaseName(m_databasePath);
526     if (!db.open()) {
527         qCritical() << "Could not open database: " << db.lastError().text();
528         raiseError(db.lastError());
529         return db;
530     }
531 
532     if (!updateSchema()) {
533         qCritical() << "Database scheme not correct.";
534         return db;
535     }
536 
537     // return correctly set up database
538     return db;
539 }
540 
updateSchema()541 bool Storage::updateSchema()
542 {
543     QSqlDatabase db = database();
544 
545     // check database version format
546     db.exec(
547         "CREATE TABLE IF NOT EXISTS metadata ("
548         "key TEXT PRIMARY KEY, "
549         "value TEXT"
550         ")");
551     if (db.lastError().isValid()) {
552         qCritical() << db.lastError().text();
553         raiseError(db.lastError());
554         return false;
555     }
556 
557     QSqlQuery versionQuery = db.exec(QStringLiteral("SELECT value FROM metadata WHERE key = 'version'"));
558     if (db.lastError().isValid()) {
559         qCritical() << db.lastError().text();
560         raiseError(db.lastError());
561         return false;
562     }
563 
564     if (versionQuery.next()) {
565         QString version = versionQuery.value(0).toString();
566         if (version != QLatin1String("1")) {
567             m_errorMessage = i18n("Invalid database version '%1'.", version);
568             emit errorMessageChanged();
569             return false;
570         }
571     } else {
572         if (!db.transaction()) {
573             qCWarning(LIBLEARNER_LOG) << db.lastError().text();
574             raiseError(db.lastError());
575             return false;
576         }
577         db.exec(QStringLiteral("INSERT INTO metadata (key, value) VALUES ('version', '1')"));
578         if (db.lastError().isValid()) {
579             qCritical() << db.lastError().text();
580             raiseError(db.lastError());
581             return false;
582         }
583         if (!db.commit()) {
584             qCritical() << db.lastError().text();
585             raiseError(db.lastError());
586             return false;
587         }
588     }
589 
590     // table for learner profiles
591     db.exec(
592         "CREATE TABLE IF NOT EXISTS profiles ("
593         "id INTEGER PRIMARY KEY AUTOINCREMENT, "
594         "name TEXT"
595         ")");
596     if (db.lastError().isValid()) {
597         qCritical() << db.lastError().text();
598         raiseError(db.lastError());
599         return false;
600     }
601 
602     // table for registered learning goals
603     db.exec(
604         "CREATE TABLE IF NOT EXISTS goals ("
605         "category INTEGER, " // LearningGoal::Category
606         "identifier TEXT, "  // identifier, unique per Category
607         "name TEXT, "        // name
608         "PRIMARY KEY ( category, identifier )"
609         ")");
610     if (db.lastError().isValid()) {
611         qCritical() << db.lastError().text();
612         raiseError(db.lastError());
613         return false;
614     }
615 
616     // table for learner - learningGoal relations
617     db.exec(
618         "CREATE TABLE IF NOT EXISTS learner_goals ("
619         "id INTEGER PRIMARY KEY AUTOINCREMENT, "
620         "goal_category INTEGER, " // LearningGoal::Category
621         "goal_identifier TEXT, "  // LearningGoal::Identifier
622         "profile_id INTEGER "     // Learner::Identifier
623         ")");
624     if (db.lastError().isValid()) {
625         qCritical() << db.lastError().text();
626         raiseError(db.lastError());
627         return false;
628     }
629 
630     // table for full progress data log
631     db.exec(
632         "CREATE TABLE IF NOT EXISTS learner_progress_log ("
633         "id INTEGER PRIMARY KEY AUTOINCREMENT, "
634         "goal_category INTEGER, " // LearningGoal::Category
635         "goal_identifier TEXT, "  // LearningGoal::Identifier
636         "profile_id INTEGER, "    // Learner::Identifier
637         "item_container TEXT, "
638         "item TEXT, "
639         "payload INTEGER, "
640         "date TEXT"
641         ")");
642     if (db.lastError().isValid()) {
643         qCritical() << db.lastError().text();
644         raiseError(db.lastError());
645         return false;
646     }
647 
648     // table for progress data quick access
649     db.exec(
650         "CREATE TABLE IF NOT EXISTS learner_progress_value ("
651         "id INTEGER PRIMARY KEY AUTOINCREMENT, "
652         "goal_category INTEGER, " // LearningGoal::Category
653         "goal_identifier TEXT, "  // LearningGoal::Identifier
654         "profile_id INTEGER, "    // Learner::Identifier
655         "item_container TEXT, "
656         "item TEXT, "
657         "payload INTEGER"
658         ")");
659     if (db.lastError().isValid()) {
660         qCritical() << db.lastError().text();
661         raiseError(db.lastError());
662         return false;
663     }
664 
665     return true;
666 }
667