1 /*
2 * SPDX-FileCopyrightText: 2012 Sebastian Gottfried <sebastiangottfried@web.de>
3 *
4 * SPDX-License-Identifier: GPL-2.0-or-later
5 */
6
7 #include "userdataaccess.h"
8
9 #include <QDebug>
10 #include <QVariant>
11 #include <QSqlDatabase>
12 #include <QSqlQuery>
13 #include <QSqlError>
14
15 #include <KLocalizedString>
16
17 #include "core/dataindex.h"
18 #include "core/course.h"
19 #include "core/lesson.h"
20 #include "core/keyboardlayout.h"
21 #include "core/key.h"
22 #include "core/keychar.h"
23 #include "core/specialkey.h"
24
25 enum KeyTypeId
26 {
27 KeyId = 1,
28 SpecialKeyId
29 };
30
UserDataAccess(QObject * parent)31 UserDataAccess::UserDataAccess(QObject* parent) :
32 DbAccess(parent)
33 {
34 }
35
fillDataIndex(DataIndex * target)36 bool UserDataAccess::fillDataIndex(DataIndex* target)
37 {
38 QSqlDatabase db = database();
39
40 if (!db.isOpen())
41 return false;
42
43 QSqlQuery courseQuery = db.exec(QStringLiteral("SELECT id, title, description, keyboard_layout_name FROM courses"));
44
45 if (courseQuery.lastError().isValid())
46 {
47 qWarning() << courseQuery.lastError().text();
48 raiseError(courseQuery.lastError());
49 return false;
50 }
51
52 while (courseQuery.next())
53 {
54 DataIndexCourse* course = new DataIndexCourse();
55
56 course->setId(courseQuery.value(0).toString());
57 course->setTitle(courseQuery.value(1).toString());
58 course->setDescription(courseQuery.value(2).toString());
59 course->setKeyboardLayoutName(courseQuery.value(3).toString());
60 course->setSource(DataIndex::UserResource);
61
62 target->addCourse(course);
63 }
64
65 QSqlQuery keyboardLayoutQuery = db.exec(QStringLiteral("SELECT id, title, name FROM keyboard_layouts"));
66
67 if (keyboardLayoutQuery.lastError().isValid())
68 {
69 qWarning() << keyboardLayoutQuery.lastError().text();
70 raiseError(keyboardLayoutQuery.lastError());
71 return false;
72 }
73
74 while (keyboardLayoutQuery.next())
75 {
76 DataIndexKeyboardLayout* keyboardLayout = new DataIndexKeyboardLayout();
77
78 keyboardLayout->setId(keyboardLayoutQuery.value(0).toString());
79 keyboardLayout->setTitle(keyboardLayoutQuery.value(1).toString());
80 keyboardLayout->setName(keyboardLayoutQuery.value(2).toString());
81 keyboardLayout->setSource(DataIndex::UserResource);
82
83 target->addKeyboardLayout(keyboardLayout);
84 }
85
86 return true;
87 }
88
loadCourse(const QString & id,Course * target)89 bool UserDataAccess::loadCourse(const QString& id, Course* target)
90 {
91 target->setIsValid(false);
92
93 QSqlDatabase db = database();
94
95 if (!db.isOpen())
96 return false;
97
98 QSqlQuery courseQuery(db);
99
100 courseQuery.prepare(QStringLiteral("SELECT title, description, keyboard_layout_name FROM courses WHERE id = ? LIMIT 1"));
101 courseQuery.bindValue(0, id);
102 courseQuery.exec();
103
104 if (courseQuery.lastError().isValid())
105 {
106 qWarning() << courseQuery.lastError().text();
107 raiseError(courseQuery.lastError());
108 return false;
109 }
110
111 if (!courseQuery.next())
112 {
113 const QString warning = i18n("No course with ID %1", id);
114 qWarning() << warning;
115 raiseError(warning);
116 }
117
118 target->setId(id);
119 target->setTitle(courseQuery.value(0).toString());
120 target->setDescription(courseQuery.value(1).toString());
121 target->setKeyboardLayoutName(courseQuery.value(2).toString());
122 target->setKind(Course::SequentialCourse);
123 target->clearLessons();
124
125 QSqlQuery lessonsQuery(db);
126
127 lessonsQuery.prepare(QStringLiteral("SELECT id, title, new_characters, text FROM course_lessons WHERE course_id = ?"));
128 lessonsQuery.bindValue(0, id);
129 lessonsQuery.exec();
130
131 if (lessonsQuery.lastError().isValid())
132 {
133 qWarning() << lessonsQuery.lastError().text();
134 raiseError(lessonsQuery.lastError());
135 return false;
136 }
137
138 while (lessonsQuery.next())
139 {
140 Lesson* lesson = new Lesson();
141
142 lesson->setId(lessonsQuery.value(0).toString());
143 lesson->setTitle(lessonsQuery.value(1).toString());
144 lesson->setNewCharacters(lessonsQuery.value(2).toString());
145 lesson->setText(lessonsQuery.value(3).toString());
146
147 target->addLesson(lesson);
148 }
149
150 target->setIsValid(true);
151
152 return true;
153 }
154
storeCourse(Course * course)155 bool UserDataAccess::storeCourse(Course* course)
156 {
157 QSqlDatabase db = database();
158
159 if (!db.isOpen())
160 return false;
161
162 if (!db.transaction())
163 {
164 qWarning() << db.lastError().text();
165 raiseError(db.lastError());
166 return false;
167 }
168
169 QSqlQuery cleanUpCourseQuery(db);
170
171 cleanUpCourseQuery.prepare(QStringLiteral("DELETE FROM courses WHERE id = ?"));
172 cleanUpCourseQuery.bindValue(0, course->id());
173 cleanUpCourseQuery.exec();
174
175 if (cleanUpCourseQuery.lastError().isValid())
176 {
177 qWarning() << cleanUpCourseQuery.lastError().text();
178 raiseError(cleanUpCourseQuery.lastError());
179 db.rollback();
180 return false;
181 }
182
183 QSqlQuery cleanUpLessonsQuery(db);
184
185 cleanUpLessonsQuery.prepare(QStringLiteral("DELETE FROM course_lessons WHERE course_id = ?"));
186 cleanUpLessonsQuery.bindValue(0, course->id());
187 cleanUpLessonsQuery.exec();
188
189 if (cleanUpLessonsQuery.lastError().isValid())
190 {
191 qWarning() << cleanUpLessonsQuery.lastError().text();
192 raiseError(cleanUpLessonsQuery.lastError());
193 db.rollback();
194 return false;
195 }
196
197 QSqlQuery insertCourseQuery(db);
198
199 insertCourseQuery.prepare(QStringLiteral("INSERT INTO courses (id, title, description, keyboard_layout_name) VALUES (?, ?, ?, ?)"));
200 insertCourseQuery.bindValue(0, course->id());
201 insertCourseQuery.bindValue(1, course->title());
202 insertCourseQuery.bindValue(2, course->description());
203 insertCourseQuery.bindValue(3, course->keyboardLayoutName());
204 insertCourseQuery.exec();
205
206 if (insertCourseQuery.lastError().isValid())
207 {
208 qWarning() << insertCourseQuery.lastError().text();
209 raiseError(insertCourseQuery.lastError());
210 db.rollback();
211 return false;
212 }
213
214 QSqlQuery insertLessonsQuery(db);
215
216 insertLessonsQuery.prepare(QStringLiteral("INSERT INTO course_lessons (id, title, new_characters, text, course_id) VALUES(?, ?, ?, ?, ?)"));
217
218 insertLessonsQuery.bindValue(4, course->id());
219
220 for (int i = 0; i < course->lessonCount(); i++)
221 {
222 Lesson* lesson = course->lesson(i);
223
224 insertLessonsQuery.bindValue(0, lesson->id());
225 insertLessonsQuery.bindValue(1, lesson->title());
226 insertLessonsQuery.bindValue(2, lesson->newCharacters());
227 insertLessonsQuery.bindValue(3, lesson->text());
228 insertLessonsQuery.exec();
229
230 if (insertLessonsQuery.lastError().isValid())
231 {
232 qWarning() << insertLessonsQuery.lastError().text();
233 raiseError(insertLessonsQuery.lastError());
234 db.rollback();
235 return false;
236 }
237 }
238
239 if(!db.commit())
240 {
241 qWarning() << db.lastError().text();
242 raiseError(db.lastError());
243 db.rollback();
244 return false;
245 }
246
247 return true;
248 }
249
deleteCourse(Course * course)250 bool UserDataAccess::deleteCourse(Course* course)
251 {
252 QSqlDatabase db = database();
253
254 if (!db.isOpen())
255 return false;
256
257 if (!db.transaction())
258 {
259 qWarning() << db.lastError().text();
260 raiseError(db.lastError());
261 return false;
262 }
263
264 QSqlQuery deleteCourseQuery(db);
265
266 deleteCourseQuery.prepare(QStringLiteral("DELETE FROM courses WHERE id = ?"));
267 deleteCourseQuery.bindValue(0, course->id());
268 deleteCourseQuery.exec();
269
270 if (deleteCourseQuery.lastError().isValid())
271 {
272 qWarning() << deleteCourseQuery.lastError().text();
273 raiseError(deleteCourseQuery.lastError());
274 db.rollback();
275 return false;
276 }
277
278 QSqlQuery deleteLessonsQuery(db);
279
280 deleteLessonsQuery.prepare(QStringLiteral("DELETE FROM course_lessons WHERE course_id = ?"));
281 deleteLessonsQuery.bindValue(0, course->id());
282 deleteLessonsQuery.exec();
283
284 if (deleteLessonsQuery.lastError().isValid())
285 {
286 qWarning() << deleteLessonsQuery.lastError().text();
287 raiseError(deleteLessonsQuery.lastError());
288 db.rollback();
289 return false;
290 }
291
292 if(!db.commit())
293 {
294 qWarning() << db.lastError().text();
295 raiseError(db.lastError());
296 db.rollback();
297 return false;
298 }
299
300 return true;
301 }
302
loadKeyboardLayout(const QString & id,KeyboardLayout * target)303 bool UserDataAccess::loadKeyboardLayout(const QString& id, KeyboardLayout* target)
304 {
305 target->setIsValid(false);
306
307 QSqlDatabase db = database();
308
309 if (!db.isOpen())
310 return false;
311
312 QSqlQuery keyboardLayoutQuery(db);
313
314 keyboardLayoutQuery.prepare(QStringLiteral("SELECT title, name, width, height FROM keyboard_layouts WHERE id = ? LIMIT 1"));
315 keyboardLayoutQuery.bindValue(0, id);
316 keyboardLayoutQuery.exec();
317
318
319 if (keyboardLayoutQuery.lastError().isValid())
320 {
321 qWarning() << keyboardLayoutQuery.lastError().text();
322 raiseError(keyboardLayoutQuery.lastError());
323 return false;
324 }
325
326 if (!keyboardLayoutQuery.next())
327 {
328 const QString warning = i18n("No keyboard layout with ID %1", id);
329 qWarning() << warning;
330 raiseError(warning);
331 }
332
333 target->setId(id);
334 target->setTitle(keyboardLayoutQuery.value(0).toString());
335 target->setName(keyboardLayoutQuery.value(1).toString());
336 target->setWidth(keyboardLayoutQuery.value(2).toInt());
337 target->setHeight(keyboardLayoutQuery.value(3).toInt());
338 target->clearKeys();
339
340 QSqlQuery keysQuery(db);
341
342 keysQuery.prepare(QStringLiteral("SELECT id, left, top, width, height, type, finger_index, has_haptic_marker, special_key_type, modifier_id, label FROM keyboard_layout_keys WHERE keyboard_layout_id = ?"));
343 keysQuery.bindValue(0, id);
344 keysQuery.exec();
345
346 QSqlQuery keyCharsQuery(db);
347
348 keyCharsQuery.prepare(QStringLiteral("SELECT position, character, modifier FROM keyboard_layout_key_chars WHERE key_id = ?"));
349
350 if (keysQuery.lastError().isValid())
351 {
352 qWarning() << keysQuery.lastError().text();
353 raiseError(keysQuery.lastError());
354 return false;
355 }
356
357 while (keysQuery.next())
358 {
359 AbstractKey* abstractKey;
360
361 KeyTypeId keyType = static_cast<KeyTypeId>(keysQuery.value(5).toInt());
362
363 if (keyType == KeyId)
364 {
365 Key* key = new Key();
366
367 key->setFingerIndex(keysQuery.value(6).toInt());
368 key->setHasHapticMarker(keysQuery.value(7).toBool());
369
370 keyCharsQuery.bindValue(0, keysQuery.value(0));
371 keyCharsQuery.exec();
372
373 if (keyCharsQuery.lastError().isValid())
374 {
375 qWarning() << keyCharsQuery.lastError().text();
376 raiseError(keyCharsQuery.lastError());
377 return false;
378 }
379
380 while (keyCharsQuery.next())
381 {
382 KeyChar* keyChar = new KeyChar();
383
384 keyChar->setPosition(static_cast<KeyChar::Position>(keyCharsQuery.value(0).toInt()));
385 keyChar->setValue(keyCharsQuery.value(1).toString().at(0));
386 keyChar->setModifier(keyCharsQuery.value(2).toString());
387
388 key->addKeyChar(keyChar);
389 }
390
391 abstractKey = key;
392 }
393 else
394 {
395 SpecialKey* specialKey = new SpecialKey();
396
397 specialKey->setTypeStr(keysQuery.value(8).toString());
398 specialKey->setModifierId(keysQuery.value(9).toString());
399 specialKey->setLabel(keysQuery.value(10).toString());
400
401 abstractKey = specialKey;
402 }
403
404 abstractKey->setLeft(keysQuery.value(1).toInt());
405 abstractKey->setTop(keysQuery.value(2).toInt());
406 abstractKey->setWidth(keysQuery.value(3).toInt());
407 abstractKey->setHeight(keysQuery.value(4).toInt());
408
409 target->addKey(abstractKey);
410 }
411
412 target->setIsValid(true);
413
414 return true;
415 }
416
storeKeyboardLayout(KeyboardLayout * keyboardLayout)417 bool UserDataAccess::storeKeyboardLayout(KeyboardLayout* keyboardLayout)
418 {
419 QSqlDatabase db = database();
420
421 if (!db.isOpen())
422 return false;
423
424 if (!db.transaction())
425 {
426 qWarning() << db.lastError().text();
427 raiseError(db.lastError());
428 return false;
429 }
430
431 QSqlQuery cleanUpKeyCharsQuery(db);
432
433 cleanUpKeyCharsQuery.prepare(QStringLiteral("DELETE FROM keyboard_layout_key_chars WHERE key_id IN (SELECT id FROM keyboard_layout_keys WHERE keyboard_layout_id = ?)"));
434 cleanUpKeyCharsQuery.bindValue(0, keyboardLayout->id());
435 cleanUpKeyCharsQuery.exec();
436
437 if (cleanUpKeyCharsQuery.lastError().isValid())
438 {
439 qWarning() << cleanUpKeyCharsQuery.lastError().text();
440 raiseError(cleanUpKeyCharsQuery.lastError());
441 db.rollback();
442 return false;
443 }
444
445
446 QSqlQuery cleanUpKeysQuery(db);
447
448 cleanUpKeysQuery.prepare(QStringLiteral("DELETE FROM keyboard_layout_keys WHERE keyboard_layout_id = ?"));
449 cleanUpKeysQuery.bindValue(0, keyboardLayout->id());
450 cleanUpKeysQuery.exec();
451
452 if (cleanUpKeysQuery.lastError().isValid())
453 {
454 qWarning() << cleanUpKeysQuery.lastError().text();
455 raiseError(cleanUpKeysQuery.lastError());
456 db.rollback();
457 return false;
458 }
459
460 QSqlQuery cleanUpKeyboardLayoutQuery(db);
461
462 cleanUpKeyboardLayoutQuery.prepare(QStringLiteral("DELETE FROM keyboard_layouts WHERE id = ?"));
463 cleanUpKeyboardLayoutQuery.bindValue(0, keyboardLayout->id());
464 cleanUpKeyboardLayoutQuery.exec();
465
466 if (cleanUpKeyboardLayoutQuery.lastError().isValid())
467 {
468 qWarning() << cleanUpKeyboardLayoutQuery.lastError().text();
469 raiseError(cleanUpKeyboardLayoutQuery.lastError());
470 db.rollback();
471 return false;
472 }
473
474 QSqlQuery insertKeyboardLayoutQuery(db);
475
476 insertKeyboardLayoutQuery.prepare(QStringLiteral("INSERT INTO keyboard_layouts (id, title, name, width, height) VALUES (?, ?, ?, ?, ?)"));
477 insertKeyboardLayoutQuery.bindValue(0, keyboardLayout->id());
478 insertKeyboardLayoutQuery.bindValue(1, keyboardLayout->title());
479 insertKeyboardLayoutQuery.bindValue(2, keyboardLayout->name());
480 insertKeyboardLayoutQuery.bindValue(3, keyboardLayout->width());
481 insertKeyboardLayoutQuery.bindValue(4, keyboardLayout->height());
482 insertKeyboardLayoutQuery.exec();
483
484 if (insertKeyboardLayoutQuery.lastError().isValid())
485 {
486 qWarning() << insertKeyboardLayoutQuery.lastError().text();
487 raiseError(insertKeyboardLayoutQuery.lastError());
488 db.rollback();
489 return false;
490 }
491
492 QSqlQuery insertKeyQuery(db);
493 QSqlQuery insertSpecialKeyQuery(db);
494 QSqlQuery insertKeyCharQuery(db);
495 QSqlQuery idQuery(db);
496
497 insertKeyQuery.prepare(QStringLiteral("INSERT INTO keyboard_layout_keys (keyboard_layout_id, left, top, width, height, type, finger_index, has_haptic_marker) VALUES (?, ?, ?, ?, ?, ?, ?, ?)"));
498 insertKeyQuery.bindValue(0, keyboardLayout->id());
499 insertKeyQuery.bindValue(5, KeyId);
500 insertSpecialKeyQuery.prepare(QStringLiteral("INSERT INTO keyboard_layout_keys (keyboard_layout_id, left, top, width, height, type, special_key_type, modifier_id, label) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)"));
501 insertSpecialKeyQuery.bindValue(0, keyboardLayout->id());
502 insertSpecialKeyQuery.bindValue(5, SpecialKeyId);
503 insertKeyCharQuery.prepare(QStringLiteral("INSERT INTO keyboard_layout_key_chars (key_id, position, character, modifier) VALUES (?, ?, ?, ?)"));
504 idQuery.prepare(QStringLiteral("SELECT last_insert_rowid()"));
505
506 for (int i = 0; i < keyboardLayout->keyCount(); i++)
507 {
508 AbstractKey* const abstractKey = keyboardLayout->key(i);
509
510 if (Key* const key = qobject_cast<Key*>(abstractKey))
511 {
512 insertKeyQuery.bindValue(1, key->left());
513 insertKeyQuery.bindValue(2, key->top());
514 insertKeyQuery.bindValue(3, key->width());
515 insertKeyQuery.bindValue(4, key->height());
516 insertKeyQuery.bindValue(6, key->fingerIndex());
517 insertKeyQuery.bindValue(7, key->hasHapticMarker());
518 insertKeyQuery.exec();
519
520 if (insertKeyQuery.lastError().isValid())
521 {
522 qWarning() << insertKeyQuery.lastError().text();
523 raiseError(insertKeyQuery.lastError());
524 db.rollback();
525 return false;
526 }
527
528 idQuery.exec();
529
530 if (idQuery.lastError().isValid())
531 {
532 qWarning() << idQuery.lastError().text();
533 raiseError(idQuery.lastError());
534 db.rollback();
535 return false;
536 }
537
538 idQuery.next();
539
540 const int keyId = idQuery.value(0).toInt();
541
542 insertKeyCharQuery.bindValue(0, keyId);
543
544 for (int j = 0; j < key->keyCharCount(); j++)
545 {
546 KeyChar * const keyChar = key->keyChar(j);
547
548 insertKeyCharQuery.bindValue(1, keyChar->position());
549 insertKeyCharQuery.bindValue(2, QString(keyChar->value()));
550 insertKeyCharQuery.bindValue(3, keyChar->modifier());
551 insertKeyCharQuery.exec();
552
553 if (insertKeyCharQuery.lastError().isValid())
554 {
555 qWarning() << insertKeyCharQuery.lastError().text();
556 raiseError(insertKeyCharQuery.lastError());
557 db.rollback();
558 return false;
559 }
560 }
561 }
562
563 if (SpecialKey* const specialKey = qobject_cast<SpecialKey*>(abstractKey))
564 {
565 insertSpecialKeyQuery.bindValue(1, specialKey->left());
566 insertSpecialKeyQuery.bindValue(2, specialKey->top());
567 insertSpecialKeyQuery.bindValue(3, specialKey->width());
568 insertSpecialKeyQuery.bindValue(4, specialKey->height());
569 insertSpecialKeyQuery.bindValue(6, specialKey->typeStr());
570 insertSpecialKeyQuery.bindValue(7, specialKey->modifierId());
571 insertSpecialKeyQuery.bindValue(8, specialKey->label());
572 insertSpecialKeyQuery.exec();
573
574 if (insertSpecialKeyQuery.lastError().isValid())
575 {
576 qWarning() << insertSpecialKeyQuery.lastError().text();
577 raiseError(insertSpecialKeyQuery.lastError());
578 db.rollback();
579 return false;
580 }
581 }
582 }
583
584 if(!db.commit())
585 {
586 qWarning() << db.lastError().text();
587 raiseError(db.lastError());
588 db.rollback();
589 return false;
590 }
591
592 return true;
593 }
594
deleteKeyboardLayout(KeyboardLayout * keyboardLayout)595 bool UserDataAccess::deleteKeyboardLayout(KeyboardLayout* keyboardLayout)
596 {
597 QSqlDatabase db = database();
598
599 if (!db.isOpen())
600 return false;
601
602 if (!db.transaction())
603 {
604 qWarning() << db.lastError().text();
605 raiseError(db.lastError());
606 return false;
607 }
608
609 QSqlQuery deleteKeyCharsQuery(db);
610
611 deleteKeyCharsQuery.prepare(QStringLiteral("DELETE FROM keyboard_layout_key_chars WHERE key_id IN (SELECT id FROM keyboard_layout_keys WHERE keyboard_layout_id = ?)"));
612 deleteKeyCharsQuery.bindValue(0, keyboardLayout->id());
613 deleteKeyCharsQuery.exec();
614
615 if (deleteKeyCharsQuery.lastError().isValid())
616 {
617 qWarning() << deleteKeyCharsQuery.lastError().text();
618 raiseError(deleteKeyCharsQuery.lastError());
619 db.rollback();
620 return false;
621 }
622
623
624 QSqlQuery deleteKeysQuery(db);
625
626 deleteKeysQuery.prepare(QStringLiteral("DELETE FROM keyboard_layout_keys WHERE keyboard_layout_id = ?"));
627 deleteKeysQuery.bindValue(0, keyboardLayout->id());
628 deleteKeysQuery.exec();
629
630 if (deleteKeysQuery.lastError().isValid())
631 {
632 qWarning() << deleteKeysQuery.lastError().text();
633 raiseError(deleteKeysQuery.lastError());
634 db.rollback();
635 return false;
636 }
637
638 QSqlQuery deleteKeyboardLayoutQuery(db);
639
640 deleteKeyboardLayoutQuery.prepare(QStringLiteral("DELETE FROM keyboard_layouts WHERE id = ?"));
641 deleteKeyboardLayoutQuery.bindValue(0, keyboardLayout->id());
642 deleteKeyboardLayoutQuery.exec();
643
644 if (deleteKeyboardLayoutQuery.lastError().isValid())
645 {
646 qWarning() << deleteKeyboardLayoutQuery.lastError().text();
647 raiseError(deleteKeyboardLayoutQuery.lastError());
648 db.rollback();
649 return false;
650 }
651
652 if(!db.commit())
653 {
654 qWarning() << db.lastError().text();
655 raiseError(db.lastError());
656 db.rollback();
657 return false;
658 }
659
660 return true;
661
662 }
663