1 #include "db_helper.h"
2
3 #include <QMap>
4 #include <QString>
5 #include <QList>
6 #include <QDateTime>
7 #include <QFile>
8 #include <QFileInfo>
9 #include <QCoreApplication>
10 #include <QTextStream>
11 #include <QSqlDatabase>
12 #include <QSqlRecord>
13 #include <QSqlQuery>
14
15 #include <algorithm>
16 #include <limits>
17
18 #include "reading_list.h"
19 #include "library_item.h"
20 #include "comic_db.h"
21 #include "data_base_management.h"
22 #include "folder.h"
23 #include "yacreader_libraries.h"
24
25 #include "qnaturalsorting.h"
26
27 #include "QsLog.h"
28 //server
29
getLibraries()30 YACReaderLibraries DBHelper::getLibraries()
31 {
32 YACReaderLibraries libraries;
33 libraries.load();
34 return libraries;
35 }
36
getFolderSubfoldersFromLibrary(qulonglong libraryId,qulonglong folderId)37 QList<LibraryItem *> DBHelper::getFolderSubfoldersFromLibrary(qulonglong libraryId, qulonglong folderId)
38 {
39 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
40 QString connectionName = "";
41 QList<LibraryItem *> list;
42 {
43 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
44 list = DBHelper::getFoldersFromParent(folderId, db, false);
45
46 connectionName = db.connectionName();
47 }
48 QSqlDatabase::removeDatabase(connectionName);
49 return list;
50 }
51
getFolderComicsFromLibrary(qulonglong libraryId,qulonglong folderId)52 QList<LibraryItem *> DBHelper::getFolderComicsFromLibrary(qulonglong libraryId, qulonglong folderId)
53 {
54 return DBHelper::getFolderComicsFromLibrary(libraryId, folderId, false);
55 }
56
getFolderComicsFromLibraryForReading(qulonglong libraryId,qulonglong folderId)57 QList<LibraryItem *> DBHelper::getFolderComicsFromLibraryForReading(qulonglong libraryId, qulonglong folderId)
58 {
59 auto list = DBHelper::getFolderComicsFromLibrary(libraryId, folderId, false);
60
61 std::sort(list.begin(), list.end(), [](LibraryItem *i1, LibraryItem *i2) {
62 auto c1 = static_cast<ComicDB *>(i1);
63 auto c2 = static_cast<ComicDB *>(i2);
64
65 if (c1->info.number.isNull() && c2->info.number.isNull()) {
66 return naturalSortLessThanCI(c1->name, c2->name);
67 } else {
68 if (c1->info.number.isNull() == false && c2->info.number.isNull() == false) {
69 return c1->info.number.toInt() < c2->info.number.toInt();
70 } else {
71 return c2->info.number.isNull();
72 }
73 }
74 });
75
76 return list;
77 }
78
getFolderComicsFromLibrary(qulonglong libraryId,qulonglong folderId,bool sort)79 QList<LibraryItem *> DBHelper::getFolderComicsFromLibrary(qulonglong libraryId, qulonglong folderId, bool sort)
80 {
81 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
82 QString connectionName = "";
83 QList<LibraryItem *> list;
84 {
85 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
86 list = DBHelper::getComicsFromParent(folderId, db, sort);
87
88 connectionName = db.connectionName();
89 }
90 QSqlDatabase::removeDatabase(connectionName);
91 return list;
92 }
93
getNumChildrenFromFolder(qulonglong libraryId,qulonglong folderId)94 quint32 DBHelper::getNumChildrenFromFolder(qulonglong libraryId, qulonglong folderId)
95 {
96 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
97 quint32 result = 0;
98 QString connectionName = "";
99
100 {
101 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
102
103 QSqlQuery selectQuery(db);
104 selectQuery.prepare("SELECT count(*) FROM folder WHERE parentId = :parentId and id <> 1");
105 selectQuery.bindValue(":parentId", folderId);
106 selectQuery.exec();
107
108 result += selectQuery.record().value(0).toULongLong();
109
110 selectQuery.prepare("SELECT count(*) FROM comic c WHERE c.parentId = :parentId");
111 selectQuery.bindValue(":parentId", folderId);
112 selectQuery.exec();
113
114 result += selectQuery.record().value(0).toULongLong();
115 connectionName = db.connectionName();
116 }
117
118 QSqlDatabase::removeDatabase(connectionName);
119
120 return result;
121 }
122
getParentFromComicFolderId(qulonglong libraryId,qulonglong id)123 qulonglong DBHelper::getParentFromComicFolderId(qulonglong libraryId, qulonglong id)
124 {
125 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
126 QString connectionName = "";
127 Folder f;
128 {
129 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
130
131 f = DBHelper::loadFolder(id, db);
132 connectionName = db.connectionName();
133 }
134
135 QSqlDatabase::removeDatabase(connectionName);
136 return f.parentId;
137 }
getComicInfo(qulonglong libraryId,qulonglong id)138 ComicDB DBHelper::getComicInfo(qulonglong libraryId, qulonglong id)
139 {
140 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
141 QString connectionName = "";
142 ComicDB comic;
143 {
144 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
145
146 comic = DBHelper::loadComic(id, db);
147 connectionName = db.connectionName();
148 }
149 QSqlDatabase::removeDatabase(connectionName);
150 return comic;
151 }
152
getSiblings(qulonglong libraryId,qulonglong parentId)153 QList<ComicDB> DBHelper::getSiblings(qulonglong libraryId, qulonglong parentId)
154 {
155 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
156 QString connectionName = "";
157 QList<ComicDB> comics;
158 {
159 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
160 comics = DBHelper::getSortedComicsFromParent(parentId, db);
161 connectionName = db.connectionName();
162 }
163
164 QSqlDatabase::removeDatabase(connectionName);
165 return comics;
166 }
167
getFolderName(qulonglong libraryId,qulonglong id)168 QString DBHelper::getFolderName(qulonglong libraryId, qulonglong id)
169 {
170 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
171
172 QString name = "";
173 QString connectionName = "";
174
175 {
176 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
177 QSqlQuery selectQuery(db); //TODO check
178 selectQuery.prepare("SELECT name FROM folder WHERE id = :id");
179 selectQuery.bindValue(":id", id);
180 selectQuery.exec();
181
182 if (selectQuery.next()) {
183 name = selectQuery.value(0).toString();
184 }
185 connectionName = db.connectionName();
186 }
187
188 QSqlDatabase::removeDatabase(connectionName);
189 return name;
190 }
getLibrariesNames()191 QList<QString> DBHelper::getLibrariesNames()
192 {
193 auto names = getLibraries().getNames();
194 std::sort(names.begin(), names.end(), naturalSortLessThanCI);
195 return names;
196 }
getLibraryName(int id)197 QString DBHelper::getLibraryName(int id)
198 {
199 return getLibraries().getName(id);
200 }
201
getLabelComics(qulonglong libraryId,qulonglong labelId)202 QList<ComicDB> DBHelper::getLabelComics(qulonglong libraryId, qulonglong labelId)
203 {
204 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
205
206 QList<ComicDB> list;
207 QString connectionName = "";
208
209 {
210 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
211 QSqlQuery selectQuery(db);
212 selectQuery.prepare("SELECT c.id,c.fileName,ci.title,ci.currentPage,ci.numPages,ci.hash,ci.read,ci.coverSizeRatio "
213 "FROM comic c INNER JOIN comic_info ci ON (c.comicInfoId = ci.id) "
214 "INNER JOIN comic_label cl ON (c.id == cl.comic_id) "
215 "WHERE cl.label_id = :parentLabelId "
216 "ORDER BY cl.ordering");
217 selectQuery.bindValue(":parentLabelId", labelId);
218 selectQuery.exec();
219
220 while (selectQuery.next()) {
221 ComicDB comic;
222
223 comic.id = selectQuery.value(0).toULongLong();
224 comic.parentId = labelId;
225 comic.name = selectQuery.value(1).toString();
226 comic.info.title = selectQuery.value(2).toString();
227 comic.info.currentPage = selectQuery.value(3).toInt();
228 comic.info.numPages = selectQuery.value(4).toInt();
229 comic.info.hash = selectQuery.value(5).toString();
230 comic.info.read = selectQuery.value(6).toBool();
231 comic.info.coverSizeRatio = selectQuery.value(7).toFloat();
232
233 list.append(comic);
234 }
235 connectionName = db.connectionName();
236 }
237 QSqlDatabase::removeDatabase(connectionName);
238
239 return list;
240 }
241
getFavorites(qulonglong libraryId)242 QList<ComicDB> DBHelper::getFavorites(qulonglong libraryId)
243 {
244 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
245 QList<ComicDB> list;
246
247 const int FAV_ID = 1;
248 QString connectionName = "";
249
250 {
251 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
252 QSqlQuery selectQuery(db);
253 selectQuery.prepare("SELECT c.id,c.fileName,ci.title,ci.currentPage,ci.numPages,ci.hash,ci.read,ci.coverSizeRatio "
254 "FROM comic c INNER JOIN comic_info ci ON (c.comicInfoId = ci.id) "
255 "INNER JOIN comic_default_reading_list cdrl ON (c.id == cdrl.comic_id) "
256 "WHERE cdrl.default_reading_list_id = :parentDefaultListId "
257 "ORDER BY cdrl.ordering");
258 selectQuery.bindValue(":parentDefaultListId", FAV_ID);
259 selectQuery.exec();
260
261 while (selectQuery.next()) {
262 ComicDB comic;
263
264 comic.id = selectQuery.value(0).toULongLong();
265 comic.parentId = FAV_ID;
266 comic.name = selectQuery.value(1).toString();
267 comic.info.title = selectQuery.value(2).toString();
268 comic.info.currentPage = selectQuery.value(3).toInt();
269 comic.info.numPages = selectQuery.value(4).toInt();
270 comic.info.hash = selectQuery.value(5).toString();
271 comic.info.read = selectQuery.value(6).toBool();
272 comic.info.coverSizeRatio = selectQuery.value(7).toFloat();
273
274 list.append(comic);
275 }
276
277 connectionName = db.connectionName();
278 }
279 //TODO ?
280 QSqlDatabase::removeDatabase(connectionName);
281
282 return list;
283 }
284
getReading(qulonglong libraryId)285 QList<ComicDB> DBHelper::getReading(qulonglong libraryId)
286 {
287 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
288 QList<ComicDB> list;
289 QString connectionName = "";
290
291 {
292 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
293 QSqlQuery selectQuery(db);
294 selectQuery.prepare("SELECT c.id,c.parentId,c.fileName,ci.title,ci.currentPage,ci.numPages,ci.hash,ci.read,ci.coverSizeRatio "
295 "FROM comic c INNER JOIN comic_info ci ON (c.comicInfoId = ci.id) "
296 "WHERE ci.hasBeenOpened = 1 AND ci.read = 0 "
297 "ORDER BY ci.lastTimeOpened DESC");
298 selectQuery.exec();
299
300 while (selectQuery.next()) {
301 ComicDB comic;
302
303 comic.id = selectQuery.value(0).toULongLong();
304 comic.parentId = selectQuery.value(1).toULongLong();
305 comic.name = selectQuery.value(2).toString();
306 comic.info.title = selectQuery.value(3).toString();
307 comic.info.currentPage = selectQuery.value(4).toInt();
308 comic.info.numPages = selectQuery.value(5).toInt();
309 comic.info.hash = selectQuery.value(6).toString();
310 comic.info.read = selectQuery.value(7).toBool();
311 comic.info.coverSizeRatio = selectQuery.value(8).toFloat();
312
313 list.append(comic);
314 }
315 connectionName = db.connectionName();
316 }
317 //TODO ?
318 QSqlDatabase::removeDatabase(connectionName);
319
320 return list;
321 }
322
getReadingLists(qulonglong libraryId)323 QList<ReadingList> DBHelper::getReadingLists(qulonglong libraryId)
324 {
325 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
326 QString connectionName = "";
327 QList<ReadingList> list;
328
329 {
330 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
331
332 QSqlQuery selectQuery("SELECT * from reading_list WHERE parentId IS NULL ORDER BY name DESC", db);
333
334 selectQuery.exec();
335
336 QSqlRecord record = selectQuery.record();
337
338 int name = record.indexOf("name");
339 int id = record.indexOf("id");
340 int ordering = record.indexOf("ordering");
341
342 while (selectQuery.next()) {
343 ReadingList item(selectQuery.value(name).toString(), selectQuery.value(id).toLongLong(), selectQuery.value(ordering).toInt());
344
345 if (list.isEmpty()) {
346 list.append(item);
347 } else {
348 int i = 0;
349 while (i < list.length() && naturalSortLessThanCI(list.at(i).getName(), item.getName()))
350 i++;
351 list.insert(i, item);
352 }
353 }
354 connectionName = db.connectionName();
355 }
356 //TODO ?
357 QSqlDatabase::removeDatabase(connectionName);
358
359 return list;
360 }
361
getReadingListFullContent(qulonglong libraryId,qulonglong readingListId,bool getFullComicInfoFields)362 QList<ComicDB> DBHelper::getReadingListFullContent(qulonglong libraryId, qulonglong readingListId, bool getFullComicInfoFields)
363 {
364 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
365 QList<ComicDB> list;
366 QString connectionName = "";
367
368 {
369 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
370 QList<qulonglong> ids;
371 ids << readingListId;
372
373 QSqlQuery subfolders(db);
374 subfolders.prepare("SELECT id "
375 "FROM reading_list "
376 "WHERE parentId = :parentId "
377 "ORDER BY ordering ASC");
378 subfolders.bindValue(":parentId", readingListId);
379 subfolders.exec();
380 while (subfolders.next())
381 ids << subfolders.value(0).toULongLong();
382
383 foreach (qulonglong id, ids) {
384 QSqlQuery selectQuery(db);
385
386 QString params;
387 if (getFullComicInfoFields) {
388 params = "*";
389 } else {
390 params = "c.id,c.parentId,c.fileName,c.path,ci.title,ci.currentPage,ci.numPages,ci.hash,ci.read,ci.coverSizeRatio";
391 }
392
393 selectQuery.prepare("SELECT " + params + " "
394 "FROM comic c INNER JOIN comic_info ci ON (c.comicInfoId = ci.id) "
395 "INNER JOIN comic_reading_list crl ON (c.id == crl.comic_id) "
396 "WHERE crl.reading_list_id = :parentReadingList "
397 "ORDER BY crl.ordering");
398 selectQuery.bindValue(":parentReadingList", id);
399 selectQuery.exec();
400
401 auto record = selectQuery.record();
402
403 int idComicIndex = record.indexOf("id");
404 int parentIdIndex = record.indexOf("parentId");
405 int fileName = record.indexOf("fileName");
406 int path = record.indexOf("path");
407
408 while (selectQuery.next()) {
409 ComicDB comic;
410
411 if (getFullComicInfoFields) {
412 comic.id = selectQuery.value(idComicIndex).toULongLong();
413 comic.parentId = selectQuery.value(parentIdIndex).toULongLong();
414 comic.name = selectQuery.value(fileName).toString();
415 comic.path = selectQuery.value(path).toString();
416
417 comic.info = getComicInfoFromQuery(selectQuery, "comicInfoId");
418 } else {
419 comic.id = selectQuery.value(0).toULongLong();
420 comic.parentId = selectQuery.value(1).toULongLong();
421 comic.name = selectQuery.value(2).toString();
422 comic.path = selectQuery.value(3).toString();
423
424 comic.info.title = selectQuery.value(4).toString();
425 comic.info.currentPage = selectQuery.value(5).toInt();
426 comic.info.numPages = selectQuery.value(6).toInt();
427 comic.info.hash = selectQuery.value(7).toString();
428 comic.info.read = selectQuery.value(8).toBool();
429 comic.info.coverSizeRatio = selectQuery.value(9).toFloat();
430 }
431
432 list.append(comic);
433 }
434 }
435 connectionName = db.connectionName();
436 }
437
438 //TODO ?
439 QSqlDatabase::removeDatabase(connectionName);
440
441 return list;
442 }
443
444 //objects management
445 //deletes
removeFromDB(LibraryItem * item,QSqlDatabase & db)446 void DBHelper::removeFromDB(LibraryItem *item, QSqlDatabase &db)
447 {
448 if (item->isDir())
449 DBHelper::removeFromDB(dynamic_cast<Folder *>(item), db);
450 else
451 DBHelper::removeFromDB(dynamic_cast<ComicDB *>(item), db);
452 }
removeFromDB(Folder * folder,QSqlDatabase & db)453 void DBHelper::removeFromDB(Folder *folder, QSqlDatabase &db)
454 {
455 QSqlQuery query(db);
456 query.prepare("DELETE FROM folder WHERE id = :id");
457 query.bindValue(":id", folder->id);
458 query.exec();
459 }
removeFromDB(ComicDB * comic,QSqlDatabase & db)460 void DBHelper::removeFromDB(ComicDB *comic, QSqlDatabase &db)
461 {
462 QSqlQuery query(db);
463 query.prepare("DELETE FROM comic WHERE id = :id");
464 query.bindValue(":id", comic->id);
465 query.exec();
466 }
467
removeLabelFromDB(qulonglong id,QSqlDatabase & db)468 void DBHelper::removeLabelFromDB(qulonglong id, QSqlDatabase &db)
469 {
470 QSqlQuery query(db);
471 query.prepare("DELETE FROM label WHERE id = :id");
472 query.bindValue(":id", id);
473 query.exec();
474 }
475
removeListFromDB(qulonglong id,QSqlDatabase & db)476 void DBHelper::removeListFromDB(qulonglong id, QSqlDatabase &db)
477 {
478 QSqlQuery query(db);
479 query.prepare("DELETE FROM reading_list WHERE id = :id");
480 query.bindValue(":id", id);
481 query.exec();
482 }
483
deleteComicsFromFavorites(const QList<ComicDB> & comicsList,QSqlDatabase & db)484 void DBHelper::deleteComicsFromFavorites(const QList<ComicDB> &comicsList, QSqlDatabase &db)
485 {
486 db.transaction();
487
488 QLOG_DEBUG() << "deleteComicsFromFavorites----------------------------------";
489
490 QSqlQuery query(db);
491 query.prepare("DELETE FROM comic_default_reading_list WHERE comic_id = :comic_id AND default_reading_list_id = 1");
492 foreach (ComicDB comic, comicsList) {
493 query.bindValue(":comic_id", comic.id);
494 query.exec();
495 }
496
497 db.commit();
498 }
499
500 //a.k.a set comics as unread by reverting the conditions used to load the comics -> void ComicModel::setupReadingModelData(const QString &databasePath)
deleteComicsFromReading(const QList<ComicDB> & comicsList,QSqlDatabase & db)501 void DBHelper::deleteComicsFromReading(const QList<ComicDB> &comicsList, QSqlDatabase &db)
502 {
503 db.transaction();
504
505 QLOG_DEBUG() << "deleteComicsFromReading----------------------------------";
506
507 for (auto comic : comicsList) {
508 comic.info.hasBeenOpened = false;
509 comic.info.currentPage = 0; //update sets hasBeenOpened to true if currentPage > 0;
510 DBHelper::update(&comic.info, db);
511 }
512
513 db.commit();
514 }
515
deleteComicsFromLabel(const QList<ComicDB> & comicsList,qulonglong labelId,QSqlDatabase & db)516 void DBHelper::deleteComicsFromLabel(const QList<ComicDB> &comicsList, qulonglong labelId, QSqlDatabase &db)
517 {
518 db.transaction();
519
520 QLOG_DEBUG() << "deleteComicsFromLabel----------------------------------";
521
522 QSqlQuery query(db);
523 query.prepare("DELETE FROM comic_label WHERE comic_id = :comic_id AND label_id = :label_id");
524 foreach (ComicDB comic, comicsList) {
525 query.bindValue(":comic_id", comic.id);
526 query.bindValue(":label_id", labelId);
527 query.exec();
528
529 QLOG_DEBUG() << "cid = " << comic.id << "lid = " << labelId;
530 QLOG_DEBUG() << query.lastError().databaseText() << "-" << query.lastError().driverText();
531 }
532
533 db.commit();
534 }
535
deleteComicsFromReadingList(const QList<ComicDB> & comicsList,qulonglong readingListId,QSqlDatabase & db)536 void DBHelper::deleteComicsFromReadingList(const QList<ComicDB> &comicsList, qulonglong readingListId, QSqlDatabase &db)
537 {
538 db.transaction();
539
540 QLOG_DEBUG() << "deleteComicsFromReadingList----------------------------------";
541
542 QSqlQuery query(db);
543 query.prepare("DELETE FROM comic_reading_list WHERE comic_id = :comic_id AND reading_list_id = :reading_list_id");
544 foreach (ComicDB comic, comicsList) {
545 query.bindValue(":comic_id", comic.id);
546 query.bindValue(":reading_list_id", readingListId);
547 query.exec();
548 }
549
550 db.commit();
551 }
552
553 //updates
update(ComicDB * comic,QSqlDatabase & db)554 void DBHelper::update(ComicDB *comic, QSqlDatabase &db)
555 {
556 Q_UNUSED(comic)
557 Q_UNUSED(db)
558 //do nothing
559 }
560
update(qulonglong libraryId,ComicInfo & comicInfo)561 void DBHelper::update(qulonglong libraryId, ComicInfo &comicInfo)
562 {
563 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
564 QString connectionName = "";
565 {
566 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
567 DBHelper::update(&comicInfo, db);
568 connectionName = db.connectionName();
569 }
570 QSqlDatabase::removeDatabase(connectionName);
571 }
572
update(ComicInfo * comicInfo,QSqlDatabase & db)573 void DBHelper::update(ComicInfo *comicInfo, QSqlDatabase &db)
574 {
575 if (comicInfo == nullptr)
576 return;
577
578 QSqlQuery updateComicInfo(db);
579 updateComicInfo.prepare("UPDATE comic_info SET "
580 "title = :title,"
581
582 "coverPage = :coverPage,"
583 "numPages = :numPages,"
584
585 "number = :number,"
586 "isBis = :isBis,"
587 "count = :count,"
588
589 "volume = :volume,"
590 "storyArc = :storyArc,"
591 "arcNumber = :arcNumber,"
592 "arcCount = :arcCount,"
593
594 "genere = :genere,"
595
596 "writer = :writer,"
597 "penciller = :penciller,"
598 "inker = :inker,"
599 "colorist = :colorist,"
600 "letterer = :letterer,"
601 "coverArtist = :coverArtist,"
602
603 "date = :date,"
604 "publisher = :publisher,"
605 "format = :format,"
606 "color = :color,"
607 "ageRating = :ageRating,"
608
609 "synopsis = :synopsis,"
610 "characters = :characters,"
611 "notes = :notes,"
612
613 "read = :read,"
614 "edited = :edited,"
615 //new 7.0 fields
616 "hasBeenOpened = :hasBeenOpened,"
617
618 "currentPage = :currentPage,"
619 "bookmark1 = :bookmark1,"
620 "bookmark2 = :bookmark2,"
621 "bookmark3 = :bookmark3,"
622 "brightness = :brightness,"
623 "contrast = :contrast, "
624 "gamma = :gamma,"
625 "rating = :rating,"
626
627 //new 7.1 fields
628 "comicVineID = :comicVineID,"
629
630 //new 9.5 fields
631 "lastTimeOpened = :lastTimeOpened,"
632
633 "coverSizeRatio = :coverSizeRatio,"
634 "originalCoverSize = :originalCoverSize,"
635 //--
636
637 //new 9.8 fields
638 "manga = :manga"
639 //--
640 " WHERE id = :id ");
641
642 updateComicInfo.bindValue(":title", comicInfo->title);
643
644 updateComicInfo.bindValue(":coverPage", comicInfo->coverPage);
645 updateComicInfo.bindValue(":numPages", comicInfo->numPages);
646
647 updateComicInfo.bindValue(":number", comicInfo->number);
648 updateComicInfo.bindValue(":isBis", comicInfo->isBis);
649 updateComicInfo.bindValue(":count", comicInfo->count);
650
651 updateComicInfo.bindValue(":volume", comicInfo->volume);
652 updateComicInfo.bindValue(":storyArc", comicInfo->storyArc);
653 updateComicInfo.bindValue(":arcNumber", comicInfo->arcNumber);
654 updateComicInfo.bindValue(":arcCount", comicInfo->arcCount);
655
656 updateComicInfo.bindValue(":genere", comicInfo->genere);
657
658 updateComicInfo.bindValue(":writer", comicInfo->writer);
659 updateComicInfo.bindValue(":penciller", comicInfo->penciller);
660 updateComicInfo.bindValue(":inker", comicInfo->inker);
661 updateComicInfo.bindValue(":colorist", comicInfo->colorist);
662 updateComicInfo.bindValue(":letterer", comicInfo->letterer);
663 updateComicInfo.bindValue(":coverArtist", comicInfo->coverArtist);
664
665 updateComicInfo.bindValue(":date", comicInfo->date);
666 updateComicInfo.bindValue(":publisher", comicInfo->publisher);
667 updateComicInfo.bindValue(":format", comicInfo->format);
668 updateComicInfo.bindValue(":color", comicInfo->color);
669 updateComicInfo.bindValue(":ageRating", comicInfo->ageRating);
670 updateComicInfo.bindValue(":manga", comicInfo->manga);
671
672 updateComicInfo.bindValue(":synopsis", comicInfo->synopsis);
673 updateComicInfo.bindValue(":characters", comicInfo->characters);
674 updateComicInfo.bindValue(":notes", comicInfo->notes);
675
676 bool read = comicInfo->read || comicInfo->currentPage == comicInfo->numPages.toInt(); //if current page is the las page, the comic is read(completed)
677 comicInfo->read = read;
678 updateComicInfo.bindValue(":read", read ? 1 : 0);
679 updateComicInfo.bindValue(":id", comicInfo->id);
680 updateComicInfo.bindValue(":edited", comicInfo->edited ? 1 : 0);
681
682 updateComicInfo.bindValue(":hasBeenOpened", comicInfo->hasBeenOpened ? 1 : 0 || comicInfo->currentPage > 1);
683 updateComicInfo.bindValue(":currentPage", comicInfo->currentPage);
684 updateComicInfo.bindValue(":bookmark1", comicInfo->bookmark1);
685 updateComicInfo.bindValue(":bookmark2", comicInfo->bookmark2);
686 updateComicInfo.bindValue(":bookmark3", comicInfo->bookmark3);
687 updateComicInfo.bindValue(":brightness", comicInfo->brightness);
688 updateComicInfo.bindValue(":contrast", comicInfo->contrast);
689 updateComicInfo.bindValue(":gamma", comicInfo->gamma);
690 updateComicInfo.bindValue(":rating", comicInfo->rating);
691
692 updateComicInfo.bindValue(":comicVineID", comicInfo->comicVineID);
693
694 updateComicInfo.bindValue(":lastTimeOpened", comicInfo->lastTimeOpened);
695
696 updateComicInfo.bindValue(":coverSizeRatio", comicInfo->coverSizeRatio);
697 updateComicInfo.bindValue(":originalCoverSize", comicInfo->originalCoverSize);
698
699 updateComicInfo.exec();
700 }
701
updateRead(ComicInfo * comicInfo,QSqlDatabase & db)702 void DBHelper::updateRead(ComicInfo *comicInfo, QSqlDatabase &db)
703 {
704 QSqlQuery updateComicInfo(db);
705 updateComicInfo.prepare("UPDATE comic_info SET "
706 "read = :read"
707 " WHERE id = :id ");
708
709 updateComicInfo.bindValue(":read", comicInfo->read ? 1 : 0);
710 updateComicInfo.bindValue(":id", comicInfo->id);
711 updateComicInfo.exec();
712 }
713
update(const Folder & folder,QSqlDatabase & db)714 void DBHelper::update(const Folder &folder, QSqlDatabase &db)
715 {
716 QSqlQuery updateFolderInfo(db);
717 updateFolderInfo.prepare("UPDATE folder SET "
718 "finished = :finished, "
719 "completed = :completed, "
720 "manga = :manga "
721 "WHERE id = :id ");
722 updateFolderInfo.bindValue(":finished", folder.isFinished() ? 1 : 0);
723 updateFolderInfo.bindValue(":completed", folder.isCompleted() ? 1 : 0);
724 updateFolderInfo.bindValue(":manga", folder.isManga() ? 1 : 0);
725 updateFolderInfo.bindValue(":id", folder.id);
726 updateFolderInfo.exec();
727 }
728
updateChildrenInfo(const Folder & folder,QSqlDatabase & db)729 void DBHelper::updateChildrenInfo(const Folder &folder, QSqlDatabase &db)
730 {
731 QSqlQuery updateFolderInfo(db);
732 updateFolderInfo.prepare("UPDATE folder SET "
733 "numChildren = :numChildren, "
734 "firstChildHash = :firstChildHash "
735 "WHERE id = :id ");
736 updateFolderInfo.bindValue(":numChildren", folder.getNumChildren());
737 updateFolderInfo.bindValue(":firstChildHash", folder.getFirstChildHash());
738 updateFolderInfo.bindValue(":id", folder.id);
739 updateFolderInfo.exec();
740 }
741
updateChildrenInfo(qulonglong folderId,QSqlDatabase & db)742 void DBHelper::updateChildrenInfo(qulonglong folderId, QSqlDatabase &db)
743 {
744 QList<LibraryItem *> subfolders = DBHelper::getFoldersFromParent(folderId, db, false);
745 QList<LibraryItem *> comics = DBHelper::getComicsFromParent(folderId, db, true);
746
747 ComicDB *firstComic = NULL;
748 if (comics.count() > 0)
749 firstComic = static_cast<ComicDB *>(comics.first());
750
751 QSqlQuery updateFolderInfo(db);
752 updateFolderInfo.prepare("UPDATE folder SET "
753 "numChildren = :numChildren, "
754 "firstChildHash = :firstChildHash "
755 "WHERE id = :id ");
756 updateFolderInfo.bindValue(":numChildren", subfolders.count() + comics.count());
757 updateFolderInfo.bindValue(":firstChildHash", firstComic != NULL ? firstComic->info.hash : "");
758 updateFolderInfo.bindValue(":id", folderId);
759 updateFolderInfo.exec();
760 }
761
updateChildrenInfo(QSqlDatabase & db)762 void DBHelper::updateChildrenInfo(QSqlDatabase &db)
763 {
764 QSqlQuery selectQuery(db); //TODO check
765 selectQuery.prepare("SELECT id FROM folder");
766 selectQuery.exec();
767
768 while (selectQuery.next()) {
769 DBHelper::updateChildrenInfo(selectQuery.value(0).toULongLong(), db);
770 }
771 }
772
updateProgress(qulonglong libraryId,const ComicInfo & comicInfo)773 void DBHelper::updateProgress(qulonglong libraryId, const ComicInfo &comicInfo)
774 {
775 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
776 QString connectionName = "";
777 {
778 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
779
780 ComicDB comic = DBHelper::loadComic(comicInfo.id, db);
781 comic.info.currentPage = comicInfo.currentPage;
782 comic.info.hasBeenOpened = comicInfo.currentPage > 0 || comic.info.hasBeenOpened;
783 comic.info.read = comic.info.read || comic.info.currentPage == comic.info.numPages;
784
785 DBHelper::updateReadingRemoteProgress(comic.info, db);
786
787 connectionName = db.connectionName();
788 }
789
790 QSqlDatabase::removeDatabase(connectionName);
791 }
792
setComicAsReading(qulonglong libraryId,const ComicInfo & comicInfo)793 void DBHelper::setComicAsReading(qulonglong libraryId, const ComicInfo &comicInfo)
794 {
795 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
796 QString connectionName = "";
797 {
798 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
799
800 ComicDB comic = DBHelper::loadComic(comicInfo.id, db);
801 comic.info.hasBeenOpened = true;
802 comic.info.read = comic.info.read || comic.info.currentPage == comic.info.numPages;
803
804 DBHelper::updateReadingRemoteProgress(comic.info, db);
805 connectionName = db.connectionName();
806 }
807 QSqlDatabase::removeDatabase(connectionName);
808 }
809
updateReadingRemoteProgress(const ComicInfo & comicInfo,QSqlDatabase & db)810 void DBHelper::updateReadingRemoteProgress(const ComicInfo &comicInfo, QSqlDatabase &db)
811 {
812 QSqlQuery updateComicInfo(db);
813 updateComicInfo.prepare("UPDATE comic_info SET "
814 "read = :read, "
815 "currentPage = :currentPage, "
816 "hasBeenOpened = :hasBeenOpened, "
817 "lastTimeOpened = :lastTimeOpened, "
818 "rating = :rating"
819 " WHERE id = :id ");
820
821 updateComicInfo.bindValue(":read", comicInfo.read ? 1 : 0);
822 updateComicInfo.bindValue(":currentPage", comicInfo.currentPage);
823 updateComicInfo.bindValue(":hasBeenOpened", comicInfo.hasBeenOpened ? 1 : 0);
824 updateComicInfo.bindValue(":lastTimeOpened", QDateTime::currentMSecsSinceEpoch() / 1000);
825 updateComicInfo.bindValue(":id", comicInfo.id);
826 updateComicInfo.bindValue(":rating", comicInfo.rating);
827 updateComicInfo.exec();
828
829 updateComicInfo.clear();
830 }
831
updateFromRemoteClient(qulonglong libraryId,const ComicInfo & comicInfo)832 void DBHelper::updateFromRemoteClient(qulonglong libraryId, const ComicInfo &comicInfo)
833 {
834 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
835 QString connectionName = "";
836 {
837 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
838
839 ComicDB comic = DBHelper::loadComic(comicInfo.id, db);
840
841 if (comic.info.hash == comicInfo.hash) {
842 if (comicInfo.currentPage > 0) {
843 comic.info.currentPage = comicInfo.currentPage;
844
845 if (comic.info.currentPage == comic.info.numPages)
846 comic.info.read = true;
847
848 comic.info.hasBeenOpened = true;
849
850 if (comic.info.lastTimeOpened.toULongLong() < comicInfo.lastTimeOpened.toULongLong())
851 comic.info.lastTimeOpened = comicInfo.lastTimeOpened;
852 }
853
854 if (comicInfo.rating > 0)
855 comic.info.rating = comicInfo.rating;
856
857 DBHelper::updateReadingRemoteProgress(comic.info, db);
858 }
859 connectionName = db.connectionName();
860 }
861 QSqlDatabase::removeDatabase(connectionName);
862 }
863
updateFromRemoteClientWithHash(const ComicInfo & comicInfo)864 void DBHelper::updateFromRemoteClientWithHash(const ComicInfo &comicInfo)
865 {
866 YACReaderLibraries libraries = DBHelper::getLibraries();
867
868 QStringList names = libraries.getNames();
869 QString connectionName = "";
870
871 foreach (QString name, names) {
872 QString libraryPath = DBHelper::getLibraries().getPath(libraries.getId(name));
873
874 {
875 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
876 ComicInfo info = loadComicInfo(comicInfo.hash, db);
877
878 if (!info.existOnDb) {
879 continue;
880 }
881
882 if (comicInfo.currentPage > 0) {
883 info.currentPage = comicInfo.currentPage;
884
885 if (info.currentPage == info.numPages)
886 info.read = true;
887
888 info.hasBeenOpened = true;
889
890 if (info.lastTimeOpened.toULongLong() < comicInfo.lastTimeOpened.toULongLong())
891 info.lastTimeOpened = comicInfo.lastTimeOpened;
892 }
893
894 if (comicInfo.rating > 0)
895 info.rating = comicInfo.rating;
896
897 DBHelper::update(&info, db);
898 connectionName = db.connectionName();
899 }
900 QSqlDatabase::removeDatabase(connectionName);
901 }
902 }
903
updateFromRemoteClient(const QMap<qulonglong,QList<ComicInfo>> & comics)904 QMap<qulonglong, QList<ComicDB>> DBHelper::updateFromRemoteClient(const QMap<qulonglong, QList<ComicInfo>> &comics)
905 {
906 QMap<qulonglong, QList<ComicDB>> moreRecentComics;
907
908 foreach (qulonglong libraryId, comics.keys()) {
909 QList<ComicDB> libraryMoreRecentComics;
910
911 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
912
913 QString connectionName = "";
914 {
915 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
916
917 db.transaction();
918
919 QSqlQuery updateComicInfo(db);
920 updateComicInfo.prepare("UPDATE comic_info SET "
921 "read = :read, "
922 "currentPage = :currentPage, "
923 "hasBeenOpened = :hasBeenOpened, "
924 "lastTimeOpened = :lastTimeOpened, "
925 "rating = :rating"
926 " WHERE id = :id ");
927
928 foreach (ComicInfo comicInfo, comics[libraryId]) {
929 ComicDB comic = DBHelper::loadComic(comicInfo.id, db);
930
931 if (comic.info.hash == comicInfo.hash) {
932 bool isMoreRecent = false;
933
934 //completion takes precedence over lastTimeOpened, if we just want to synchronize the lastest status we should use only lastTimeOpened
935 if ((comic.info.currentPage > 1 && comic.info.currentPage > comicInfo.currentPage) || comic.info.hasBeenOpened || (comic.info.read && !comicInfo.read)) {
936 isMoreRecent = true;
937 }
938
939 if (comic.info.lastTimeOpened.toULongLong() > 0 && comicInfo.lastTimeOpened.toULongLong() == 0) {
940 isMoreRecent = true;
941 }
942
943 comic.info.currentPage = qMax(comic.info.currentPage, comicInfo.currentPage);
944
945 if (comic.info.currentPage == comic.info.numPages)
946 comic.info.read = true;
947
948 comic.info.read = comic.info.read || comicInfo.read;
949
950 comic.info.hasBeenOpened = comic.info.hasBeenOpened || comicInfo.currentPage > 0;
951
952 if (comic.info.lastTimeOpened.toULongLong() < comicInfo.lastTimeOpened.toULongLong() && comicInfo.lastTimeOpened.toULongLong() > 0)
953 comic.info.lastTimeOpened = comicInfo.lastTimeOpened;
954
955 if (comicInfo.rating > 0)
956 comic.info.rating = comicInfo.rating;
957
958 updateComicInfo.bindValue(":read", comic.info.read ? 1 : 0);
959 updateComicInfo.bindValue(":currentPage", comic.info.currentPage);
960 updateComicInfo.bindValue(":hasBeenOpened", comic.info.hasBeenOpened ? 1 : 0);
961 updateComicInfo.bindValue(":lastTimeOpened", comic.info.lastTimeOpened);
962 updateComicInfo.bindValue(":id", comic.info.id);
963 updateComicInfo.bindValue(":rating", comic.info.rating);
964 updateComicInfo.exec();
965
966 if (isMoreRecent) {
967 libraryMoreRecentComics.append(comic);
968 }
969 }
970 }
971
972 if (!libraryMoreRecentComics.isEmpty()) {
973 moreRecentComics[libraryId] = libraryMoreRecentComics;
974 }
975
976 db.commit();
977 connectionName = db.connectionName();
978 }
979
980 QSqlDatabase::removeDatabase(connectionName);
981 }
982
983 return moreRecentComics;
984 }
985
updateFromRemoteClientWithHash(const QList<ComicInfo> & comics)986 void DBHelper::updateFromRemoteClientWithHash(const QList<ComicInfo> &comics)
987 {
988 YACReaderLibraries libraries = DBHelper::getLibraries();
989
990 QStringList names = libraries.getNames();
991
992 foreach (QString name, names) {
993 QString libraryPath = DBHelper::getLibraries().getPath(libraries.getId(name));
994 QString connectionName = "";
995 {
996 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
997
998 db.transaction();
999
1000 QSqlQuery updateComicInfo(db);
1001 updateComicInfo.prepare("UPDATE comic_info SET "
1002 "read = :read, "
1003 "currentPage = :currentPage, "
1004 "hasBeenOpened = :hasBeenOpened, "
1005 "lastTimeOpened = :lastTimeOpened, "
1006 "rating = :rating"
1007 " WHERE id = :id ");
1008
1009 foreach (ComicInfo comicInfo, comics) {
1010 ComicInfo info = loadComicInfo(comicInfo.hash, db);
1011
1012 if (!info.existOnDb) {
1013 continue;
1014 }
1015
1016 if (comicInfo.currentPage > 0) {
1017 info.currentPage = comicInfo.currentPage;
1018
1019 if (info.currentPage == info.numPages)
1020 info.read = true;
1021
1022 info.hasBeenOpened = true;
1023
1024 if (info.lastTimeOpened.toULongLong() < comicInfo.lastTimeOpened.toULongLong())
1025 info.lastTimeOpened = comicInfo.lastTimeOpened;
1026 }
1027
1028 if (comicInfo.rating > 0) {
1029 info.rating = comicInfo.rating;
1030 }
1031
1032 updateComicInfo.bindValue(":read", info.read ? 1 : 0);
1033 updateComicInfo.bindValue(":currentPage", info.currentPage);
1034 updateComicInfo.bindValue(":hasBeenOpened", info.hasBeenOpened ? 1 : 0);
1035 updateComicInfo.bindValue(":lastTimeOpened", QDateTime::currentMSecsSinceEpoch() / 1000);
1036 updateComicInfo.bindValue(":id", info.id);
1037 updateComicInfo.bindValue(":rating", info.rating);
1038 updateComicInfo.exec();
1039 }
1040
1041 db.commit();
1042 connectionName = db.connectionName();
1043 }
1044 QSqlDatabase::removeDatabase(connectionName);
1045 }
1046 }
1047
renameLabel(qulonglong id,const QString & name,QSqlDatabase & db)1048 void DBHelper::renameLabel(qulonglong id, const QString &name, QSqlDatabase &db)
1049 {
1050 QSqlQuery renameLabelQuery(db);
1051 renameLabelQuery.prepare("UPDATE label SET "
1052 "name = :name "
1053 "WHERE id = :id");
1054 renameLabelQuery.bindValue(":name", name);
1055 renameLabelQuery.bindValue(":id", id);
1056 renameLabelQuery.exec();
1057
1058 QLOG_DEBUG() << renameLabelQuery.lastError().databaseText();
1059 }
1060
renameList(qulonglong id,const QString & name,QSqlDatabase & db)1061 void DBHelper::renameList(qulonglong id, const QString &name, QSqlDatabase &db)
1062 {
1063 QSqlQuery renameLabelQuery(db);
1064 renameLabelQuery.prepare("UPDATE reading_list SET "
1065 "name = :name "
1066 "WHERE id = :id");
1067 renameLabelQuery.bindValue(":name", name);
1068 renameLabelQuery.bindValue(":id", id);
1069 renameLabelQuery.exec();
1070 }
1071
reasignOrderToSublists(QList<qulonglong> ids,QSqlDatabase & db)1072 void DBHelper::reasignOrderToSublists(QList<qulonglong> ids, QSqlDatabase &db)
1073 {
1074 QSqlQuery updateOrdering(db);
1075 updateOrdering.prepare("UPDATE reading_list SET "
1076 "ordering = :ordering "
1077 "WHERE id = :id");
1078 db.transaction();
1079 int order = 0;
1080 foreach (qulonglong id, ids) {
1081 updateOrdering.bindValue(":ordering", order++);
1082 updateOrdering.bindValue(":id", id);
1083 updateOrdering.exec();
1084 }
1085
1086 db.commit();
1087 }
1088
reasignOrderToComicsInFavorites(QList<qulonglong> comicIds,QSqlDatabase & db)1089 void DBHelper::reasignOrderToComicsInFavorites(QList<qulonglong> comicIds, QSqlDatabase &db)
1090 {
1091 QSqlQuery updateOrdering(db);
1092 updateOrdering.prepare("UPDATE comic_default_reading_list SET "
1093 "ordering = :ordering "
1094 "WHERE comic_id = :comic_id AND default_reading_list_id = 1");
1095 db.transaction();
1096 int order = 0;
1097 foreach (qulonglong id, comicIds) {
1098 updateOrdering.bindValue(":ordering", order++);
1099 updateOrdering.bindValue(":comic_id", id);
1100 updateOrdering.exec();
1101 }
1102
1103 db.commit();
1104 }
1105
reasignOrderToComicsInLabel(qulonglong labelId,QList<qulonglong> comicIds,QSqlDatabase & db)1106 void DBHelper::reasignOrderToComicsInLabel(qulonglong labelId, QList<qulonglong> comicIds, QSqlDatabase &db)
1107 {
1108 QSqlQuery updateOrdering(db);
1109 updateOrdering.prepare("UPDATE comic_label SET "
1110 "ordering = :ordering "
1111 "WHERE comic_id = :comic_id AND label_id = :label_id");
1112 db.transaction();
1113 int order = 0;
1114 foreach (qulonglong id, comicIds) {
1115 updateOrdering.bindValue(":ordering", order++);
1116 updateOrdering.bindValue(":comic_id", id);
1117 updateOrdering.bindValue(":label_id", labelId);
1118 updateOrdering.exec();
1119 }
1120
1121 db.commit();
1122 }
1123
reasignOrderToComicsInReadingList(qulonglong readingListId,QList<qulonglong> comicIds,QSqlDatabase & db)1124 void DBHelper::reasignOrderToComicsInReadingList(qulonglong readingListId, QList<qulonglong> comicIds, QSqlDatabase &db)
1125 {
1126 QSqlQuery updateOrdering(db);
1127 updateOrdering.prepare("UPDATE comic_reading_list SET "
1128 "ordering = :ordering "
1129 "WHERE comic_id = :comic_id AND reading_list_id = :reading_list_id");
1130 db.transaction();
1131 int order = 0;
1132 foreach (qulonglong id, comicIds) {
1133 updateOrdering.bindValue(":ordering", order++);
1134 updateOrdering.bindValue(":comic_id", id);
1135 updateOrdering.bindValue(":reading_list_id", readingListId);
1136 updateOrdering.exec();
1137 QLOG_TRACE() << updateOrdering.lastError().databaseText() << "-" << updateOrdering.lastError().driverText();
1138 }
1139
1140 db.commit();
1141 }
1142
1143 //inserts
insert(Folder * folder,QSqlDatabase & db)1144 qulonglong DBHelper::insert(Folder *folder, QSqlDatabase &db)
1145 {
1146 QSqlQuery query(db);
1147 query.prepare("INSERT INTO folder (parentId, name, path) "
1148 "VALUES (:parentId, :name, :path)");
1149 query.bindValue(":parentId", folder->parentId);
1150 query.bindValue(":name", folder->name);
1151 query.bindValue(":path", folder->path);
1152 query.exec();
1153
1154 return query.lastInsertId().toULongLong();
1155 }
1156
insert(ComicDB * comic,QSqlDatabase & db)1157 qulonglong DBHelper::insert(ComicDB *comic, QSqlDatabase &db)
1158 {
1159 if (!comic->info.existOnDb) {
1160 QSqlQuery comicInfoInsert(db);
1161 comicInfoInsert.prepare("INSERT INTO comic_info (hash,numPages,coverSizeRatio,originalCoverSize) "
1162 "VALUES (:hash,:numPages,:coverSizeRatio,:originalCoverSize)");
1163 comicInfoInsert.bindValue(":hash", comic->info.hash);
1164 comicInfoInsert.bindValue(":numPages", comic->info.numPages);
1165 comicInfoInsert.bindValue(":coverSizeRatio", comic->info.coverSizeRatio);
1166 comicInfoInsert.bindValue(":originalCoverSize", comic->info.originalCoverSize);
1167 comicInfoInsert.exec();
1168 comic->info.id = comicInfoInsert.lastInsertId().toULongLong();
1169 comic->_hasCover = false;
1170 } else
1171 comic->_hasCover = true;
1172
1173 QSqlQuery query(db);
1174 query.prepare("INSERT INTO comic (parentId, comicInfoId, fileName, path) "
1175 "VALUES (:parentId,:comicInfoId,:name, :path)");
1176 query.bindValue(":parentId", comic->parentId);
1177 query.bindValue(":comicInfoId", comic->info.id);
1178 query.bindValue(":name", comic->name);
1179 query.bindValue(":path", comic->path);
1180 query.exec();
1181
1182 return query.lastInsertId().toULongLong();
1183 }
1184
insertLabel(const QString & name,YACReader::LabelColors color,QSqlDatabase & db)1185 qulonglong DBHelper::insertLabel(const QString &name, YACReader::LabelColors color, QSqlDatabase &db)
1186 {
1187 QSqlQuery query(db);
1188 query.prepare("INSERT INTO label (name, color, ordering) "
1189 "VALUES (:name, :color, :ordering)");
1190 query.bindValue(":name", name);
1191 query.bindValue(":color", YACReader::colorToName(color));
1192 query.bindValue(":ordering", color);
1193 query.exec();
1194 return query.lastInsertId().toULongLong();
1195 }
1196
insertReadingList(const QString & name,QSqlDatabase & db)1197 qulonglong DBHelper::insertReadingList(const QString &name, QSqlDatabase &db)
1198 {
1199 QSqlQuery query(db);
1200 query.prepare("INSERT INTO reading_list (name) "
1201 "VALUES (:name)");
1202 query.bindValue(":name", name);
1203 query.exec();
1204 return query.lastInsertId().toULongLong();
1205 }
1206
insertReadingSubList(const QString & name,qulonglong parentId,int ordering,QSqlDatabase & db)1207 qulonglong DBHelper::insertReadingSubList(const QString &name, qulonglong parentId, int ordering, QSqlDatabase &db)
1208 {
1209 QSqlQuery query(db);
1210 query.prepare("INSERT INTO reading_list (name, parentId, ordering) "
1211 "VALUES (:name, :parentId, :ordering)");
1212 query.bindValue(":name", name);
1213 query.bindValue(":parentId", parentId);
1214 query.bindValue(":ordering", ordering);
1215 query.exec();
1216 return query.lastInsertId().toULongLong();
1217 }
1218
insertComicsInFavorites(const QList<ComicDB> & comicsList,QSqlDatabase & db)1219 void DBHelper::insertComicsInFavorites(const QList<ComicDB> &comicsList, QSqlDatabase &db)
1220 {
1221 QSqlQuery getNumComicsInFavoritesQuery("SELECT count(*) FROM comic_default_reading_list WHERE default_reading_list_id = 1;", db);
1222 getNumComicsInFavoritesQuery.next();
1223
1224 int numComics = getNumComicsInFavoritesQuery.value(0).toInt();
1225
1226 db.transaction();
1227
1228 QSqlQuery query(db);
1229 query.prepare("INSERT INTO comic_default_reading_list (default_reading_list_id, comic_id, ordering) "
1230 "VALUES (1, :comic_id, :ordering)");
1231
1232 foreach (ComicDB comic, comicsList) {
1233 query.bindValue(":comic_id", comic.id);
1234 query.bindValue(":ordering", numComics++);
1235 query.exec();
1236 }
1237
1238 QLOG_TRACE() << query.lastError();
1239
1240 db.commit();
1241 }
1242
insertComicsInLabel(const QList<ComicDB> & comicsList,qulonglong labelId,QSqlDatabase & db)1243 void DBHelper::insertComicsInLabel(const QList<ComicDB> &comicsList, qulonglong labelId, QSqlDatabase &db)
1244 {
1245 QSqlQuery getNumComicsInFavoritesQuery(QString("SELECT count(*) FROM comic_label WHERE label_id = %1;").arg(labelId), db);
1246 getNumComicsInFavoritesQuery.next();
1247
1248 int numComics = getNumComicsInFavoritesQuery.value(0).toInt();
1249
1250 db.transaction();
1251
1252 QSqlQuery query(db);
1253 query.prepare("INSERT INTO comic_label (label_id, comic_id, ordering) "
1254 "VALUES (:label_id, :comic_id, :ordering)");
1255
1256 foreach (ComicDB comic, comicsList) {
1257 query.bindValue(":label_id", labelId);
1258 query.bindValue(":comic_id", comic.id);
1259 query.bindValue(":ordering", numComics++);
1260 query.exec();
1261 }
1262
1263 QLOG_TRACE() << query.lastError();
1264
1265 db.commit();
1266 }
1267
insertComicsInReadingList(const QList<ComicDB> & comicsList,qulonglong readingListId,QSqlDatabase & db)1268 void DBHelper::insertComicsInReadingList(const QList<ComicDB> &comicsList, qulonglong readingListId, QSqlDatabase &db)
1269 {
1270 QSqlQuery getNumComicsInFavoritesQuery("SELECT count(*) FROM comic_reading_list;", db);
1271 getNumComicsInFavoritesQuery.next();
1272
1273 int numComics = getNumComicsInFavoritesQuery.value(0).toInt();
1274
1275 db.transaction();
1276
1277 QSqlQuery query(db);
1278 query.prepare("INSERT INTO comic_reading_list (reading_list_id, comic_id, ordering) "
1279 "VALUES (:reading_list_id, :comic_id, :ordering)");
1280
1281 foreach (ComicDB comic, comicsList) {
1282 query.bindValue(":reading_list_id", readingListId);
1283 query.bindValue(":comic_id", comic.id);
1284 query.bindValue(":ordering", numComics++);
1285 query.exec();
1286 }
1287
1288 db.commit();
1289 }
1290 //queries
getFoldersFromParent(qulonglong parentId,QSqlDatabase & db,bool sort)1291 QList<LibraryItem *> DBHelper::getFoldersFromParent(qulonglong parentId, QSqlDatabase &db, bool sort)
1292 {
1293 QList<LibraryItem *> list;
1294
1295 QSqlQuery selectQuery(db); //TODO check
1296 selectQuery.prepare("SELECT * FROM folder WHERE parentId = :parentId and id <> 1");
1297 selectQuery.bindValue(":parentId", parentId);
1298 selectQuery.exec();
1299
1300 QSqlRecord record = selectQuery.record();
1301
1302 int name = record.indexOf("name");
1303 int path = record.indexOf("path");
1304 int id = record.indexOf("id");
1305 int numChildren = record.indexOf("numChildren");
1306 int firstChildHash = record.indexOf("firstChildHash");
1307 int customImage = record.indexOf("customImage");
1308
1309 Folder *currentItem;
1310 while (selectQuery.next()) {
1311 //TODO sort by sort indicator and name
1312 currentItem = new Folder(selectQuery.value(id).toULongLong(), parentId, selectQuery.value(name).toString(), selectQuery.value(path).toString());
1313
1314 if (!selectQuery.value(numChildren).isNull() && selectQuery.value(numChildren).isValid())
1315 currentItem->setNumChildren(selectQuery.value(numChildren).toInt());
1316 currentItem->setFirstChildHash(selectQuery.value(firstChildHash).toString());
1317 currentItem->setCustomImage(selectQuery.value(customImage).toString());
1318
1319 int lessThan = 0;
1320
1321 if (list.isEmpty() || !sort)
1322 list.append(currentItem);
1323 else {
1324 auto last = static_cast<Folder *>(list.back());
1325 QString nameLast = last->name;
1326 QString nameCurrent = currentItem->name;
1327 QList<LibraryItem *>::iterator i;
1328 i = list.end();
1329 i--;
1330 while ((0 > (lessThan = naturalCompare(nameCurrent, nameLast, Qt::CaseInsensitive))) && i != list.begin()) {
1331 i--;
1332 nameLast = (*i)->name;
1333 }
1334 if (lessThan >= 0) //si se ha encontrado un elemento menor que current, se inserta justo después
1335 list.insert(++i, currentItem);
1336 else
1337 list.insert(i, currentItem);
1338 }
1339 }
1340
1341 return list;
1342 }
1343
getSortedComicsFromParent(qulonglong parentId,QSqlDatabase & db)1344 QList<ComicDB> DBHelper::getSortedComicsFromParent(qulonglong parentId, QSqlDatabase &db)
1345 {
1346 QList<ComicDB> list;
1347
1348 QSqlQuery selectQuery(db);
1349
1350 selectQuery.setForwardOnly(true);
1351 selectQuery.prepare("select * from comic c inner join comic_info ci on (c.comicInfoId = ci.id) where c.parentId = :parentId");
1352 selectQuery.bindValue(":parentId", parentId);
1353 selectQuery.exec();
1354
1355 QSqlRecord record = selectQuery.record();
1356
1357 int id = record.indexOf("id");
1358 //int parentIdIndex = record.indexOf("parentId");
1359 int fileName = record.indexOf("fileName");
1360 int path = record.indexOf("path");
1361
1362 ComicDB currentItem;
1363 while (selectQuery.next()) {
1364 currentItem.id = selectQuery.value(id).toULongLong();
1365 currentItem.parentId = parentId; //selectQuery.value(parentId).toULongLong();
1366 currentItem.name = selectQuery.value(fileName).toString();
1367 currentItem.path = selectQuery.value(path).toString();
1368
1369 currentItem.info = getComicInfoFromQuery(selectQuery, "comicInfoId");
1370
1371 list.append(currentItem);
1372 }
1373
1374 std::sort(list.begin(), list.end(), [](const ComicDB &c1, const ComicDB &c2) {
1375 if (c1.info.number.isNull() && c2.info.number.isNull()) {
1376 return naturalSortLessThanCI(c1.name, c2.name);
1377 } else {
1378 if (c1.info.number.isNull() == false && c2.info.number.isNull() == false) {
1379 return c1.info.number.toInt() < c2.info.number.toInt();
1380 } else {
1381 return c2.info.number.isNull();
1382 }
1383 }
1384 });
1385
1386 //selectQuery.finish();
1387 return list;
1388 }
getComicsFromParent(qulonglong parentId,QSqlDatabase & db,bool sort)1389 QList<LibraryItem *> DBHelper::getComicsFromParent(qulonglong parentId, QSqlDatabase &db, bool sort)
1390 {
1391 QList<LibraryItem *> list;
1392
1393 QSqlQuery selectQuery(db);
1394 selectQuery.prepare("select c.id,c.parentId,c.fileName,c.path,ci.hash from comic c inner join comic_info ci on (c.comicInfoId = ci.id) where c.parentId = :parentId");
1395 selectQuery.bindValue(":parentId", parentId);
1396 selectQuery.exec();
1397
1398 QSqlRecord record = selectQuery.record();
1399
1400 int id = record.indexOf("id");
1401
1402 ComicDB *currentItem;
1403 while (selectQuery.next()) {
1404 currentItem = new ComicDB();
1405 currentItem->id = selectQuery.value(id).toULongLong();
1406 currentItem->parentId = selectQuery.value(1).toULongLong();
1407 currentItem->name = selectQuery.value(2).toString();
1408 currentItem->path = selectQuery.value(3).toString();
1409 currentItem->info = DBHelper::loadComicInfo(selectQuery.value(4).toString(), db);
1410
1411 list.append(currentItem);
1412 }
1413
1414 if (sort) {
1415 std::sort(list.begin(), list.end(), [](const LibraryItem *c1, const LibraryItem *c2) {
1416 return c1->name.localeAwareCompare(c2->name) < 0;
1417 });
1418 }
1419
1420 return list;
1421 }
1422
getLabels(qulonglong libraryId)1423 QList<Label> DBHelper::getLabels(qulonglong libraryId)
1424 {
1425 QString libraryPath = DBHelper::getLibraries().getPath(libraryId);
1426 QString connectionName = "";
1427 QList<Label> labels;
1428 {
1429 QSqlDatabase db = DataBaseManagement::loadDatabase(libraryPath + "/.yacreaderlibrary");
1430
1431 QSqlQuery selectQuery("SELECT * FROM label ORDER BY ordering,name", db); //TODO add some kind of
1432 QSqlRecord record = selectQuery.record();
1433
1434 int name = record.indexOf("name");
1435 int id = record.indexOf("id");
1436 int ordering = record.indexOf("ordering");
1437
1438 while (selectQuery.next()) {
1439 Label item(selectQuery.value(name).toString(),
1440 selectQuery.value(id).toLongLong(),
1441 static_cast<YACReader::LabelColors>(selectQuery.value(ordering).toInt()));
1442
1443 if (labels.isEmpty()) {
1444 labels << item;
1445 } else {
1446 int i = 0;
1447
1448 while (i < labels.count() && (labels.at(i).getColorID() < item.getColorID()))
1449 i++;
1450
1451 if (i < labels.count()) {
1452 if (labels.at(i).getColorID() == item.getColorID()) //sort by name
1453 {
1454 while (i < labels.count() && labels.at(i).getColorID() == item.getColorID() && naturalSortLessThanCI(labels.at(i).getName(), item.getName()))
1455 i++;
1456 }
1457 }
1458 if (i >= labels.count()) {
1459 labels << item;
1460 } else {
1461 labels.insert(i, item);
1462 }
1463 }
1464 }
1465 connectionName = db.connectionName();
1466 }
1467 QSqlDatabase::removeDatabase(connectionName);
1468
1469 return labels;
1470 }
1471
updateFolderTreeManga(qulonglong id,QSqlDatabase & db,bool manga)1472 void DBHelper::updateFolderTreeManga(qulonglong id, QSqlDatabase &db, bool manga)
1473 {
1474 QSqlQuery updateFolderQuery(db);
1475 updateFolderQuery.prepare("UPDATE folder "
1476 "SET manga = :manga "
1477 "WHERE id = :id");
1478 updateFolderQuery.bindValue(":manga", manga ? 1 : 0);
1479 updateFolderQuery.bindValue(":id", id);
1480 updateFolderQuery.exec();
1481
1482 QSqlQuery updateComicInfo(db);
1483 updateComicInfo.prepare("UPDATE comic_info "
1484 "SET manga = :manga "
1485 "WHERE id IN (SELECT ci.id FROM comic c INNER JOIN comic_info ci ON (c.comicInfoId = ci.id) WHERE c.parentId = :parentId)");
1486 updateComicInfo.bindValue(":manga", manga ? 1 : 0);
1487 updateComicInfo.bindValue(":parentId", id);
1488 updateComicInfo.exec();
1489
1490 QSqlQuery getSubFoldersQuery(db);
1491 getSubFoldersQuery.prepare("SELECT id FROM folder WHERE parentId = :parentId AND id <> 1"); //do not select the root folder
1492 getSubFoldersQuery.bindValue(":parentId", id);
1493 getSubFoldersQuery.exec();
1494
1495 int childFolderIdPos = getSubFoldersQuery.record().indexOf("id");
1496
1497 while (getSubFoldersQuery.next()) {
1498 updateFolderTreeManga(getSubFoldersQuery.value(childFolderIdPos).toULongLong(), db, manga);
1499 }
1500 }
1501
1502 //loads
loadFolder(qulonglong id,QSqlDatabase & db)1503 Folder DBHelper::loadFolder(qulonglong id, QSqlDatabase &db)
1504 {
1505 Folder folder;
1506
1507 QSqlQuery query(db);
1508 query.prepare("SELECT * FROM folder WHERE id = :id");
1509 query.bindValue(":id", id);
1510 query.exec();
1511 folder.id = id;
1512 folder.parentId = 0;
1513
1514 QSqlRecord record = query.record();
1515
1516 int parentId = record.indexOf("parentId");
1517 int name = record.indexOf("name");
1518 int path = record.indexOf("path");
1519 int finished = record.indexOf("finished");
1520 int completed = record.indexOf("completed");
1521 int manga = record.indexOf("manga");
1522 int numChildren = record.indexOf("numChildren");
1523 int firstChildHash = record.indexOf("firstChildHash");
1524 int customImage = record.indexOf("customImage");
1525
1526 if (query.next()) {
1527 folder.parentId = query.value(parentId).toULongLong();
1528 folder.name = query.value(name).toString();
1529 folder.path = query.value(path).toString();
1530 folder.knownId = true;
1531
1532 //new 7.1
1533 folder.setFinished(query.value(finished).toBool());
1534 folder.setCompleted(query.value(completed).toBool());
1535
1536 //new 9.5
1537 if (!query.value(numChildren).isNull() && query.value(numChildren).isValid())
1538 folder.setNumChildren(query.value(numChildren).toInt());
1539 folder.setFirstChildHash(query.value(firstChildHash).toString());
1540 folder.setCustomImage(query.value(customImage).toString());
1541
1542 //new 9.8
1543 folder.setManga(query.value(manga).toBool());
1544 }
1545
1546 return folder;
1547 }
1548
loadFolder(const QString & folderName,qulonglong parentId,QSqlDatabase & db)1549 Folder DBHelper::loadFolder(const QString &folderName, qulonglong parentId, QSqlDatabase &db)
1550 {
1551 Folder folder;
1552
1553 QSqlQuery query(db);
1554 query.prepare("SELECT * FROM folder WHERE parentId = :parentId AND name = :folderName");
1555 query.bindValue(":parentId", parentId);
1556 query.bindValue(":folderName", folderName);
1557 query.exec();
1558
1559 QSqlRecord record = query.record();
1560
1561 int id = record.indexOf("id");
1562 int name = record.indexOf("name");
1563 int path = record.indexOf("path");
1564 int finished = record.indexOf("finished");
1565 int completed = record.indexOf("completed");
1566 int manga = record.indexOf("manga");
1567 int numChildren = record.indexOf("numChildren");
1568 int firstChildHash = record.indexOf("firstChildHash");
1569 int customImage = record.indexOf("customImage");
1570
1571 folder.parentId = parentId;
1572 if (query.next()) {
1573 folder.id = query.value(id).toULongLong();
1574 folder.name = query.value(name).toString();
1575 folder.path = query.value(path).toString();
1576 folder.knownId = true;
1577
1578 //new 7.1
1579 folder.setFinished(query.value(finished).toBool());
1580 folder.setCompleted(query.value(completed).toBool());
1581
1582 //new 9.5
1583 if (!query.value(numChildren).isNull() && query.value(numChildren).isValid())
1584 folder.setNumChildren(query.value(numChildren).toInt());
1585 folder.setFirstChildHash(query.value(firstChildHash).toString());
1586 folder.setCustomImage(query.value(customImage).toString());
1587
1588 //new 9.8
1589 folder.setManga(query.value(manga).toBool());
1590 }
1591
1592 return folder;
1593 }
1594
loadComic(qulonglong id,QSqlDatabase & db)1595 ComicDB DBHelper::loadComic(qulonglong id, QSqlDatabase &db)
1596 {
1597 ComicDB comic;
1598
1599 QSqlQuery selectQuery(db);
1600 selectQuery.prepare("select c.id,c.parentId,c.fileName,c.path,ci.hash from comic c inner join comic_info ci on (c.comicInfoId = ci.id) where c.id = :id");
1601 selectQuery.bindValue(":id", id);
1602 selectQuery.exec();
1603
1604 QSqlRecord record = selectQuery.record();
1605
1606 int parentId = record.indexOf("parentId");
1607 int name = record.indexOf("fileName");
1608 int path = record.indexOf("path");
1609 int hash = record.indexOf("hash");
1610
1611 comic.id = id;
1612 if (selectQuery.next()) {
1613 comic.parentId = selectQuery.value(parentId).toULongLong();
1614 comic.name = selectQuery.value(name).toString();
1615 comic.path = selectQuery.value(path).toString();
1616 comic.info = DBHelper::loadComicInfo(selectQuery.value(hash).toString(), db);
1617 }
1618
1619 return comic;
1620 }
1621
loadComic(QString cname,QString cpath,QString chash,QSqlDatabase & database)1622 ComicDB DBHelper::loadComic(QString cname, QString cpath, QString chash, QSqlDatabase &database)
1623 {
1624 ComicDB comic;
1625
1626 //comic.parentId = cparentId;
1627 comic.name = cname;
1628 comic.path = cpath;
1629
1630 comic.info = DBHelper::loadComicInfo(chash, database);
1631
1632 if (!comic.info.existOnDb) {
1633 comic.info.hash = chash;
1634 comic.info.coverPage = 1;
1635 comic._hasCover = false;
1636 } else
1637 comic._hasCover = true;
1638
1639 return comic;
1640 }
1641
loadComicInfo(QString hash,QSqlDatabase & db)1642 ComicInfo DBHelper::loadComicInfo(QString hash, QSqlDatabase &db)
1643 {
1644 ComicInfo comicInfo;
1645
1646 QSqlQuery findComicInfo(db);
1647 findComicInfo.prepare("SELECT * FROM comic_info WHERE hash = :hash");
1648 findComicInfo.bindValue(":hash", hash);
1649 findComicInfo.exec();
1650
1651 if (findComicInfo.next()) {
1652 comicInfo = getComicInfoFromQuery(findComicInfo);
1653 } else
1654 comicInfo.existOnDb = false;
1655
1656 return comicInfo;
1657 }
1658
getComicInfoFromQuery(QSqlQuery & query,const QString & idKey)1659 ComicInfo DBHelper::getComicInfoFromQuery(QSqlQuery &query, const QString &idKey)
1660 {
1661 QSqlRecord record = query.record();
1662
1663 int hash = record.indexOf("hash");
1664 int id = record.indexOf(idKey);
1665 int read = record.indexOf("read");
1666 int edited = record.indexOf("edited");
1667
1668 //new 7.0 fields
1669 int hasBeenOpened = record.indexOf("hasBeenOpened");
1670 int currentPage = record.indexOf("currentPage");
1671 int bookmark1 = record.indexOf("bookmark1");
1672 int bookmark2 = record.indexOf("bookmark2");
1673 int bookmark3 = record.indexOf("bookmark3");
1674 int brightness = record.indexOf("brightness");
1675 int contrast = record.indexOf("contrast");
1676 int gamma = record.indexOf("gamma");
1677 int rating = record.indexOf("rating");
1678 //--
1679
1680 int title = record.indexOf("title");
1681 int numPages = record.indexOf("numPages");
1682
1683 int coverPage = record.indexOf("coverPage");
1684
1685 int number = record.indexOf("number");
1686 int isBis = record.indexOf("isBis");
1687 int count = record.indexOf("count");
1688
1689 int volume = record.indexOf("volume");
1690 int storyArc = record.indexOf("storyArc");
1691 int arcNumber = record.indexOf("arcNumber");
1692 int arcCount = record.indexOf("arcCount");
1693
1694 int genere = record.indexOf("genere");
1695
1696 int writer = record.indexOf("writer");
1697 int penciller = record.indexOf("penciller");
1698 int inker = record.indexOf("inker");
1699 int colorist = record.indexOf("colorist");
1700 int letterer = record.indexOf("letterer");
1701 int coverArtist = record.indexOf("coverArtist");
1702
1703 int date = record.indexOf("date");
1704 int publisher = record.indexOf("publisher");
1705 int format = record.indexOf("format");
1706 int color = record.indexOf("color");
1707 int ageRating = record.indexOf("ageRating");
1708 int manga = record.indexOf("manga");
1709
1710 int synopsis = record.indexOf("synopsis");
1711 int characters = record.indexOf("characters");
1712 int notes = record.indexOf("notes");
1713
1714 int comicVineID = record.indexOf("comicVineID");
1715
1716 int lastTimeOpened = record.indexOf("lastTimeOpened");
1717
1718 int coverSizeRatio = record.indexOf("coverSizeRatio");
1719 int originalCoverSize = record.indexOf("originalCoverSize");
1720
1721 ComicInfo comicInfo;
1722
1723 comicInfo.hash = query.value(hash).toString();
1724 comicInfo.id = query.value(id).toULongLong();
1725 comicInfo.read = query.value(read).toBool();
1726 comicInfo.edited = query.value(edited).toBool();
1727
1728 //new 7.0 fields
1729 comicInfo.hasBeenOpened = query.value(hasBeenOpened).toBool();
1730 comicInfo.currentPage = query.value(currentPage).toInt();
1731 comicInfo.bookmark1 = query.value(bookmark1).toInt();
1732 comicInfo.bookmark2 = query.value(bookmark2).toInt();
1733 comicInfo.bookmark3 = query.value(bookmark3).toInt();
1734 comicInfo.brightness = query.value(brightness).toInt();
1735 comicInfo.contrast = query.value(contrast).toInt();
1736 comicInfo.gamma = query.value(gamma).toInt();
1737 comicInfo.rating = query.value(rating).toInt();
1738 //--
1739 comicInfo.title = query.value(title);
1740 comicInfo.numPages = query.value(numPages);
1741
1742 comicInfo.coverPage = query.value(coverPage);
1743
1744 comicInfo.number = query.value(number);
1745 comicInfo.isBis = query.value(isBis);
1746 comicInfo.count = query.value(count);
1747
1748 comicInfo.volume = query.value(volume);
1749 comicInfo.storyArc = query.value(storyArc);
1750 comicInfo.arcNumber = query.value(arcNumber);
1751 comicInfo.arcCount = query.value(arcCount);
1752
1753 comicInfo.genere = query.value(genere);
1754
1755 comicInfo.writer = query.value(writer);
1756 comicInfo.penciller = query.value(penciller);
1757 comicInfo.inker = query.value(inker);
1758 comicInfo.colorist = query.value(colorist);
1759 comicInfo.letterer = query.value(letterer);
1760 comicInfo.coverArtist = query.value(coverArtist);
1761
1762 comicInfo.date = query.value(date);
1763 comicInfo.publisher = query.value(publisher);
1764 comicInfo.format = query.value(format);
1765 comicInfo.color = query.value(color);
1766 comicInfo.ageRating = query.value(ageRating);
1767
1768 comicInfo.synopsis = query.value(synopsis);
1769 comicInfo.characters = query.value(characters);
1770 comicInfo.notes = query.value(notes);
1771
1772 comicInfo.comicVineID = query.value(comicVineID);
1773
1774 //new 9.5 fields
1775 comicInfo.lastTimeOpened = query.value(lastTimeOpened);
1776
1777 comicInfo.coverSizeRatio = query.value(coverSizeRatio);
1778 comicInfo.originalCoverSize = query.value(originalCoverSize);
1779 //--
1780
1781 //new 9.8 fields
1782 comicInfo.manga = query.value(manga);
1783 //--
1784
1785 comicInfo.existOnDb = true;
1786
1787 return comicInfo;
1788 }
1789
loadSubfoldersNames(qulonglong folderId,QSqlDatabase & db)1790 QList<QString> DBHelper::loadSubfoldersNames(qulonglong folderId, QSqlDatabase &db)
1791 {
1792 QList<QString> result;
1793 QSqlQuery selectQuery(db);
1794 selectQuery.prepare("SELECT name FROM folder WHERE parentId = :parentId AND id <> 1"); //do not select the root folder
1795 selectQuery.bindValue(":parentId", folderId);
1796 selectQuery.exec();
1797
1798 int name = selectQuery.record().indexOf("name");
1799
1800 while (selectQuery.next()) {
1801 result << selectQuery.value(name).toString();
1802 }
1803 return result;
1804 }
1805
isFavoriteComic(qulonglong id,QSqlDatabase & db)1806 bool DBHelper::isFavoriteComic(qulonglong id, QSqlDatabase &db)
1807 {
1808 QSqlQuery selectQuery(db);
1809 selectQuery.prepare("SELECT * FROM comic_default_reading_list cl WHERE cl.comic_id = :comic_id AND cl.default_reading_list_id = 1");
1810 selectQuery.bindValue(":comic_id", id);
1811 selectQuery.exec();
1812
1813 if (selectQuery.next()) {
1814 return true;
1815 }
1816
1817 return false;
1818 }
1819