| 1 |
/* |
| 2 |
SPDX-FileCopyrightText: 2013-2016 Andreas Cord-Landwehr <[email protected]> |
| 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 |
|
| 22 |
Storage::Storage(QObject *parent) |
| 23 |
: QObject(parent) |
| 24 |
, m_databasePath(QStandardPaths::writableLocation(QStandardPaths::AppLocalDataLocation) + QStringLiteral("/learnerdata.db")) |
| 25 |
, m_errorMessage(QString()) |
| 26 |
{ |
| 27 |
} |
| 28 |
|
| 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 |
|
| 37 |
QString Storage::errorMessage() const |
| 38 |
{ |
| 39 |
return m_errorMessage; |
| 40 |
} |
| 41 |
|
| 42 |
void Storage::raiseError(const QSqlError &error) |
| 43 |
{ |
| 44 |
m_errorMessage = QStringLiteral("%1 : %2").arg(error.driverText(), error.databaseText()); |
| 45 |
Q_EMIT errorMessageChanged(); |
| 46 |
} |
| 47 |
|
| 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 |
for (LearningGoal *goal : learner->goals()) { |
| 94 |
QSqlQuery relationExistsQuery(db); |
| 95 |
relationExistsQuery.prepare( |
| 96 |
QLatin1String("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 |
|
| 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 |
|
| 161 |
bool Storage::removeRelation(Learner *learner, LearningGoal *goal) |
| 162 |
{ |
| 163 |
QSqlDatabase db = database(); |
| 164 |
QSqlQuery removeGoalRelationQuery(db); |
| 165 |
removeGoalRelationQuery.prepare( |
| 166 |
QLatin1String("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 |
|
| 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 |
for (Learner *cmpProfile : std::as_const(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 |
for (LearningGoal *cmpGoal : std::as_const(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 |
|
| 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 |
|
| 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 |
|
| 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 |
QLatin1String("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 |
|
| 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 |
QLatin1String("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 |
|
| 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 |
QLatin1String("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 |
QLatin1String("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 |
QLatin1String("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 |
|
| 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 |
QLatin1String("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 |
|
| 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 |
QLatin1String("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 |
|
| 511 |
QSqlDatabase Storage::database() |
| 512 |
{ |
| 513 |
if (QSqlDatabase::contains(QLatin1String(QSqlDatabase::defaultConnection))) { |
| 514 |
return QSqlDatabase::database(QLatin1String(QSqlDatabase::defaultConnection)); |
| 515 |
} |
| 516 |
|
| 517 |
// create data directory if it does not exist |
| 518 |
QDir dir = QDir(QStandardPaths::writableLocation(QStandardPaths::AppLocalDataLocation)); |
| 519 |
if (!dir.exists()) { |
| 520 |
dir.mkpath(QStandardPaths::writableLocation(QStandardPaths::AppLocalDataLocation)); |
| 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 |
|
| 541 |
bool Storage::updateSchema() |
| 542 |
{ |
| 543 |
QSqlDatabase db = database(); |
| 544 |
|
| 545 |
// check database version format |
| 546 |
QSqlQuery formatQuery(db); |
| 547 |
formatQuery.exec( |
| 548 |
QLatin1String("CREATE TABLE IF NOT EXISTS metadata (" |
| 549 |
"key TEXT PRIMARY KEY, " |
| 550 |
"value TEXT" |
| 551 |
")")); |
| 552 |
if (formatQuery.lastError().isValid()) { |
| 553 |
qCritical() << formatQuery.lastError().text(); |
| 554 |
raiseError(formatQuery.lastError()); |
| 555 |
return false; |
| 556 |
} |
| 557 |
|
| 558 |
QSqlQuery versionQuery(db); |
| 559 |
versionQuery.exec(QStringLiteral("SELECT value FROM metadata WHERE key = 'version'")); |
| 560 |
if (versionQuery.lastError().isValid()) { |
| 561 |
qCritical() << versionQuery.lastError().text(); |
| 562 |
raiseError(versionQuery.lastError()); |
| 563 |
return false; |
| 564 |
} |
| 565 |
|
| 566 |
if (versionQuery.next()) { |
| 567 |
QString version = versionQuery.value(0).toString(); |
| 568 |
if (version != QLatin1String("1")) { |
| 569 |
m_errorMessage = i18n("Invalid database version '%1'.", version); |
| 570 |
Q_EMIT errorMessageChanged(); |
| 571 |
return false; |
| 572 |
} |
| 573 |
} else { |
| 574 |
if (!db.transaction()) { |
| 575 |
qCWarning(LIBLEARNER_LOG) << db.lastError().text(); |
| 576 |
raiseError(db.lastError()); |
| 577 |
return false; |
| 578 |
} |
| 579 |
QSqlQuery insertMetadataQuery(db); |
| 580 |
versionQuery.exec(QStringLiteral("INSERT INTO metadata (key, value) VALUES ('version', '1')")); |
| 581 |
if (versionQuery.lastError().isValid()) { |
| 582 |
qCritical() << versionQuery.lastError().text(); |
| 583 |
raiseError(versionQuery.lastError()); |
| 584 |
return false; |
| 585 |
} |
| 586 |
if (!db.commit()) { |
| 587 |
qCritical() << db.lastError().text(); |
| 588 |
raiseError(db.lastError()); |
| 589 |
return false; |
| 590 |
} |
| 591 |
} |
| 592 |
|
| 593 |
// table for learner profiles |
| 594 |
QSqlQuery createTableQuery(db); |
| 595 |
createTableQuery.exec( |
| 596 |
QLatin1String("CREATE TABLE IF NOT EXISTS profiles (" |
| 597 |
"id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 598 |
"name TEXT" |
| 599 |
")")); |
| 600 |
if (createTableQuery.lastError().isValid()) { |
| 601 |
qCritical() << createTableQuery.lastError().text(); |
| 602 |
raiseError(createTableQuery.lastError()); |
| 603 |
return false; |
| 604 |
} |
| 605 |
|
| 606 |
// table for registered learning goals |
| 607 |
createTableQuery.exec( |
| 608 |
QLatin1String("CREATE TABLE IF NOT EXISTS goals (" |
| 609 |
"category INTEGER, " // LearningGoal::Category |
| 610 |
"identifier TEXT, " // identifier, unique per Category |
| 611 |
"name TEXT, " // name |
| 612 |
"PRIMARY KEY ( category, identifier )" |
| 613 |
")")); |
| 614 |
if (createTableQuery.lastError().isValid()) { |
| 615 |
qCritical() << createTableQuery.lastError().text(); |
| 616 |
raiseError(createTableQuery.lastError()); |
| 617 |
return false; |
| 618 |
} |
| 619 |
|
| 620 |
// table for learner - learningGoal relations |
| 621 |
createTableQuery.exec( |
| 622 |
QLatin1String("CREATE TABLE IF NOT EXISTS learner_goals (" |
| 623 |
"id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 624 |
"goal_category INTEGER, " // LearningGoal::Category |
| 625 |
"goal_identifier TEXT, " // LearningGoal::Identifier |
| 626 |
"profile_id INTEGER " // Learner::Identifier |
| 627 |
")")); |
| 628 |
if (createTableQuery.lastError().isValid()) { |
| 629 |
qCritical() << createTableQuery.lastError().text(); |
| 630 |
raiseError(createTableQuery.lastError()); |
| 631 |
return false; |
| 632 |
} |
| 633 |
|
| 634 |
// table for full progress data log |
| 635 |
createTableQuery.exec( |
| 636 |
QLatin1String("CREATE TABLE IF NOT EXISTS learner_progress_log (" |
| 637 |
"id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 638 |
"goal_category INTEGER, " // LearningGoal::Category |
| 639 |
"goal_identifier TEXT, " // LearningGoal::Identifier |
| 640 |
"profile_id INTEGER, " // Learner::Identifier |
| 641 |
"item_container TEXT, " |
| 642 |
"item TEXT, " |
| 643 |
"payload INTEGER, " |
| 644 |
"date TEXT" |
| 645 |
")")); |
| 646 |
if (createTableQuery.lastError().isValid()) { |
| 647 |
qCritical() << createTableQuery.lastError().text(); |
| 648 |
raiseError(createTableQuery.lastError()); |
| 649 |
return false; |
| 650 |
} |
| 651 |
|
| 652 |
// table for progress data quick access |
| 653 |
createTableQuery.exec( |
| 654 |
QLatin1String("CREATE TABLE IF NOT EXISTS learner_progress_value (" |
| 655 |
"id INTEGER PRIMARY KEY AUTOINCREMENT, " |
| 656 |
"goal_category INTEGER, " // LearningGoal::Category |
| 657 |
"goal_identifier TEXT, " // LearningGoal::Identifier |
| 658 |
"profile_id INTEGER, " // Learner::Identifier |
| 659 |
"item_container TEXT, " |
| 660 |
"item TEXT, " |
| 661 |
"payload INTEGER" |
| 662 |
")")); |
| 663 |
if (createTableQuery.lastError().isValid()) { |
| 664 |
qCritical() << createTableQuery.lastError().text(); |
| 665 |
raiseError(createTableQuery.lastError()); |
| 666 |
return false; |
| 667 |
} |
| 668 |
|
| 669 |
return true; |
| 670 |
} |