File indexing completed on 2024-04-28 15:35:50
0001 // SPDX-FileCopyrightText: 2017 Dan Leinir Turthra Jensen <admin@leinir.dk> 0002 // SPDX-License-Identifier: LGPL-2.1-only or LGPL-3.0-only or LicenseRef-KDE-Accepted-LGPL 0003 0004 #include "bookdatabase.h" 0005 0006 #include "categoryentriesmodel.h" 0007 0008 #include <QSqlDatabase> 0009 #include <QSqlError> 0010 #include <QSqlQuery> 0011 #include <QSqlRecord> 0012 #include <QStandardPaths> 0013 0014 #include <QDir> 0015 0016 #include <arianna_debug.h> 0017 #include <qstringliteral.h> 0018 0019 class BookDatabase::Private 0020 { 0021 public: 0022 Private() 0023 { 0024 db = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE")); 0025 0026 QDir location{QStandardPaths::writableLocation(QStandardPaths::AppDataLocation)}; 0027 if (!location.exists()) 0028 location.mkpath(QStringLiteral(".")); 0029 0030 dbfile = location.absoluteFilePath(QStringLiteral("library.sqlite")); 0031 db.setDatabaseName(dbfile); 0032 } 0033 0034 QSqlDatabase db; 0035 QString dbfile; 0036 QStringList fieldNames; 0037 0038 bool prepareDb() 0039 { 0040 if (!db.open()) { 0041 qCDebug(ARIANNA_LOG) << QStringLiteral("Failed to open the book database file") << dbfile << db.lastError(); 0042 return false; 0043 } 0044 0045 const QStringList tables = db.tables(); 0046 if (tables.contains(QStringLiteral("books"), Qt::CaseInsensitive)) { 0047 if (fieldNames.isEmpty()) { 0048 QSqlQuery qu(QStringLiteral("SELECT * FROM books")); 0049 for (int i = 0; i < qu.record().count(); i++) { 0050 fieldNames.append(qu.record().fieldName(i)); 0051 } 0052 qCDebug(ARIANNA_LOG) << Q_FUNC_INFO << ": opening database with following fieldNames:" << fieldNames; 0053 } 0054 return true; 0055 } 0056 0057 QSqlQuery q; 0058 QStringList entryNames; 0059 // clang-format off 0060 entryNames << QStringLiteral("fileName varchar primary key") 0061 << QStringLiteral("fileTitle varchar") 0062 << QStringLiteral("title varchar") 0063 << QStringLiteral("genres varchar") 0064 << QStringLiteral("keywords varchar") 0065 << QStringLiteral("characters varchar") 0066 << QStringLiteral("description varchar") 0067 << QStringLiteral("series varchar") 0068 << QStringLiteral("seriesNumbers varchar") 0069 << QStringLiteral("seriesVolumes varchar") 0070 << QStringLiteral("author varchar") 0071 << QStringLiteral("publisher varchar") 0072 << QStringLiteral("created datetime") 0073 << QStringLiteral("lastOpenedTime datetime") 0074 << QStringLiteral("thumbnail varchar") 0075 << QStringLiteral("comment varchar") 0076 << QStringLiteral("tags varchar") 0077 << QStringLiteral("rating varchar") 0078 << QStringLiteral("locations text") 0079 << QStringLiteral("currentLocation varchar") 0080 << QStringLiteral("currentProgress int") 0081 << QStringLiteral("rights varchar") 0082 << QStringLiteral("source varchar") 0083 << QStringLiteral("identifier varchar") 0084 << QStringLiteral("language varchar"); 0085 // clang-format on 0086 0087 if (!q.exec(QStringLiteral("create table books(") + entryNames.join(QStringLiteral(", ")) + QLatin1Char(')'))) { 0088 qCDebug(ARIANNA_LOG) << "Database could not create the table books" << q.lastError(); 0089 return false; 0090 } 0091 for (int i = 0; i < entryNames.size(); i++) { 0092 QString fieldName = entryNames.at(i).split(QLatin1Char(' ')).first(); 0093 fieldNames.append(fieldName); 0094 } 0095 qCDebug(ARIANNA_LOG) << Q_FUNC_INFO << ": making database with following fieldNames:" << fieldNames; 0096 0097 return true; 0098 } 0099 0100 void closeDb() 0101 { 0102 db.close(); 0103 } 0104 0105 BookEntry fromSqlQuery(const QSqlQuery &query) 0106 { 0107 BookEntry entry; 0108 entry.filename = query.value(fieldNames.indexOf(QStringLiteral("fileName"))).toString(); 0109 entry.filetitle = query.value(fieldNames.indexOf(QStringLiteral("fileTitle"))).toString(); 0110 entry.title = query.value(fieldNames.indexOf(QStringLiteral("title"))).toString(); 0111 entry.series = query.value(fieldNames.indexOf(QStringLiteral("series"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0112 entry.author = query.value(fieldNames.indexOf(QStringLiteral("author"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0113 entry.publisher = query.value(fieldNames.indexOf(QStringLiteral("publisher"))).toString(); 0114 entry.created = query.value(fieldNames.indexOf(QStringLiteral("created"))).toDateTime(); 0115 entry.lastOpenedTime = query.value(fieldNames.indexOf(QStringLiteral("lastOpenedTime"))).toDateTime(); 0116 entry.currentLocation = query.value(fieldNames.indexOf(QStringLiteral("currentLocation"))).toString(); 0117 entry.currentProgress = query.value(fieldNames.indexOf(QStringLiteral("currentProgress"))).toInt(); 0118 entry.thumbnail = query.value(fieldNames.indexOf(QStringLiteral("thumbnail"))).toString(); 0119 entry.description = query.value(fieldNames.indexOf(QStringLiteral("description"))).toString().split(QLatin1Char('\n'), Qt::SkipEmptyParts); 0120 entry.comment = query.value(fieldNames.indexOf(QStringLiteral("comment"))).toString(); 0121 entry.tags = query.value(fieldNames.indexOf(QStringLiteral("tags"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0122 entry.rating = query.value(fieldNames.indexOf(QStringLiteral("rating"))).toInt(); 0123 entry.seriesNumbers = query.value(fieldNames.indexOf(QStringLiteral("seriesNumbers"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0124 entry.seriesVolumes = query.value(fieldNames.indexOf(QStringLiteral("seriesVolumes"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0125 entry.genres = query.value(fieldNames.indexOf(QStringLiteral("genres"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0126 entry.keywords = query.value(fieldNames.indexOf(QStringLiteral("keywords"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0127 entry.characters = query.value(fieldNames.indexOf(QStringLiteral("characters"))).toString().split(QLatin1Char(','), Qt::SkipEmptyParts); 0128 entry.locations = query.value(fieldNames.indexOf(QStringLiteral("locations"))).toString(); 0129 entry.language = query.value(fieldNames.indexOf(QStringLiteral("language"))).toString(); 0130 entry.identifier = query.value(fieldNames.indexOf(QStringLiteral("identifier"))).toString(); 0131 entry.rights = query.value(fieldNames.indexOf(QStringLiteral("rights"))).toString(); 0132 entry.source = query.value(fieldNames.indexOf(QStringLiteral("source"))).toString(); 0133 return entry; 0134 } 0135 }; 0136 0137 BookDatabase::BookDatabase(QObject *parent) 0138 : QObject(parent) 0139 , d(std::make_unique<Private>()) 0140 { 0141 } 0142 0143 BookDatabase::~BookDatabase() = default; 0144 0145 QList<BookEntry> BookDatabase::loadEntries() 0146 { 0147 if (!d->prepareDb()) { 0148 return {}; 0149 } 0150 0151 QList<BookEntry> entries; 0152 QStringList entryNames = d->fieldNames; 0153 QSqlQuery allEntries(QStringLiteral("SELECT ") + d->fieldNames.join(QStringLiteral(", ")) + QStringLiteral(" FROM books")); 0154 0155 while (allEntries.next()) { 0156 entries.append(d->fromSqlQuery(allEntries)); 0157 } 0158 0159 d->closeDb(); 0160 return entries; 0161 } 0162 0163 std::optional<BookEntry> BookDatabase::loadEntry(const QString &fileName) 0164 { 0165 if (!d->prepareDb()) { 0166 return std::nullopt; 0167 } 0168 QSqlQuery entry; 0169 entry.prepare(QStringLiteral("SELECT ") + d->fieldNames.join(QStringLiteral(", ")) + QStringLiteral(" FROM books WHERE fileName = :fileName LIMIT 1")); 0170 entry.bindValue(QLatin1String(":fileName"), fileName); 0171 if (entry.exec()) { 0172 entry.first(); 0173 return d->fromSqlQuery(entry); 0174 } 0175 return std::nullopt; 0176 } 0177 0178 void BookDatabase::addEntry(const BookEntry &entry) 0179 { 0180 if (!d->prepareDb()) { 0181 return; 0182 } 0183 qCDebug(ARIANNA_LOG) << "Adding newly discovered book to the database" << entry.filename; 0184 0185 QStringList valueNames; 0186 for (int i = 0; i < d->fieldNames.size(); i++) { 0187 valueNames.append(QStringLiteral(":").append(d->fieldNames.at(i))); 0188 } 0189 QSqlQuery newEntry; 0190 newEntry.prepare(QStringLiteral("INSERT INTO books (") + d->fieldNames.join(QStringLiteral(", ")) + QStringLiteral(") ") + QStringLiteral("VALUES (") 0191 + valueNames.join(QStringLiteral(", ")) + QLatin1Char(')')); 0192 newEntry.bindValue(QStringLiteral(":fileName"), entry.filename); 0193 newEntry.bindValue(QStringLiteral(":fileTitle"), entry.filetitle); 0194 newEntry.bindValue(QStringLiteral(":title"), entry.title); 0195 newEntry.bindValue(QStringLiteral(":series"), entry.series.join(QLatin1Char(','))); 0196 newEntry.bindValue(QStringLiteral(":author"), entry.author.join(QLatin1Char(','))); 0197 newEntry.bindValue(QStringLiteral(":publisher"), entry.publisher); 0198 newEntry.bindValue(QStringLiteral(":publisher"), entry.publisher); 0199 newEntry.bindValue(QStringLiteral(":created"), entry.created); 0200 newEntry.bindValue(QStringLiteral(":lastOpenedTime"), entry.lastOpenedTime); 0201 newEntry.bindValue(QStringLiteral(":currentLocation"), entry.currentLocation); 0202 newEntry.bindValue(QStringLiteral(":currentProgress"), entry.currentProgress); 0203 newEntry.bindValue(QStringLiteral(":thumbnail"), entry.thumbnail); 0204 newEntry.bindValue(QStringLiteral(":description"), entry.description.join(QLatin1Char('\n'))); 0205 newEntry.bindValue(QStringLiteral(":comment"), entry.comment); 0206 newEntry.bindValue(QStringLiteral(":tags"), entry.tags.join(QLatin1Char(','))); 0207 newEntry.bindValue(QStringLiteral(":rating"), entry.rating); 0208 newEntry.bindValue(QStringLiteral(":seriesNumbers"), entry.seriesNumbers.join(QLatin1Char(','))); 0209 newEntry.bindValue(QStringLiteral(":seriesVolumes"), entry.seriesVolumes.join(QLatin1Char(','))); 0210 newEntry.bindValue(QStringLiteral(":genres"), entry.genres.join(QLatin1Char(','))); 0211 newEntry.bindValue(QStringLiteral(":keywords"), entry.keywords.join(QLatin1Char(','))); 0212 newEntry.bindValue(QStringLiteral(":characters"), entry.characters.join(QLatin1Char(','))); 0213 newEntry.bindValue(QStringLiteral(":locations"), entry.locations); 0214 newEntry.bindValue(QStringLiteral(":rights"), entry.rights); 0215 newEntry.bindValue(QStringLiteral(":source"), entry.source); 0216 newEntry.bindValue(QStringLiteral(":identifier"), entry.identifier); 0217 newEntry.bindValue(QStringLiteral(":language"), entry.language); 0218 newEntry.exec(); 0219 0220 d->closeDb(); 0221 } 0222 0223 void BookDatabase::removeEntry(const BookEntry &entry) 0224 { 0225 if (!d->prepareDb()) { 0226 return; 0227 } 0228 qCDebug(ARIANNA_LOG) << "Removing book from the database" << entry.filename; 0229 0230 QSqlQuery removeEntry; 0231 removeEntry.prepare(QStringLiteral("DELETE FROM books WHERE fileName='") + entry.filename + QStringLiteral("';")); 0232 removeEntry.exec(); 0233 0234 d->closeDb(); 0235 } 0236 0237 void BookDatabase::updateEntry(const QString &fileName, const QString &property, const QVariant &value) 0238 { 0239 if (!d->prepareDb()) { 0240 return; 0241 } 0242 // qCDebug(QTQUICK_LOG) << "Updating book in the database" << fileName << property << value; 0243 0244 if (!d->fieldNames.contains(property)) { 0245 return; 0246 } 0247 0248 QStringList stringListValues; 0249 // clang-format off 0250 stringListValues << QStringLiteral("series") 0251 << QStringLiteral("author") 0252 << QStringLiteral("characters") 0253 << QStringLiteral("genres") 0254 << QStringLiteral("keywords") 0255 << QStringLiteral("tags"); 0256 // clang-format on 0257 QString val; 0258 if (stringListValues.contains(property)) { 0259 val = value.toStringList().join(QLatin1Char(',')); 0260 } else if (property == QStringLiteral("description")) { 0261 val = value.toStringList().join(QLatin1Char('\n')); 0262 } 0263 0264 QSqlQuery updateEntry; 0265 updateEntry.prepare(QStringLiteral("UPDATE books SET %1=:value WHERE fileName=:filename ").arg(property)); 0266 updateEntry.bindValue(QStringLiteral(":value"), value); 0267 if (!val.isEmpty()) { 0268 updateEntry.bindValue(QStringLiteral(":value"), val); 0269 } 0270 updateEntry.bindValue(QStringLiteral(":filename"), fileName); 0271 if (!updateEntry.exec()) { 0272 qCDebug(ARIANNA_LOG) << updateEntry.lastError(); 0273 qCDebug(ARIANNA_LOG) << "Query failed, string:" << updateEntry.lastQuery(); 0274 qCDebug(ARIANNA_LOG) << updateEntry.boundValue(QStringLiteral(":value")); 0275 qCDebug(ARIANNA_LOG) << updateEntry.boundValue(QStringLiteral(":filename")); 0276 qCDebug(ARIANNA_LOG) << d->db.lastError(); 0277 } 0278 0279 d->closeDb(); 0280 } 0281 0282 #include "moc_bookdatabase.cpp"