2 * Copyright 2023 Soren Stoutner <soren@stoutner.com>.
4 * This file is part of Privacy Browser PC <https://www.stoutner.com/privacy-browser-pc>.
6 * Privacy Browser PC is free software: you can redistribute it and/or modify
7 * it under the terms of the GNU General Public License as published by
8 * the Free Software Foundation, either version 3 of the License, or
9 * (at your option) any later version.
11 * Privacy Browser PC is distributed in the hope that it will be useful,
12 * but WITHOUT ANY WARRANTY; without even the implied warranty of
13 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
14 * GNU General Public License for more details.
16 * You should have received a copy of the GNU General Public License
17 * along with Privacy Browser PC. If not, see <http://www.gnu.org/licenses/>.
20 // Application headers.
21 #include "BookmarksDatabase.h"
23 // Define the private static schema constants.
24 const int BookmarksDatabase::SCHEMA_VERSION = 0;
26 // Define the public static constants.
27 const QString BookmarksDatabase::CONNECTION_NAME = "bookmarks_database";
28 const QString BookmarksDatabase::BOOKMARK_NAME = "bookmark_name";
29 const QString BookmarksDatabase::BOOKMARKS_TABLE = "bookmarks";
30 const QString BookmarksDatabase::BOOKMARK_URL = "bookmark_url";
31 const QString BookmarksDatabase::DISPLAY_ORDER = "display_order";
32 const QString BookmarksDatabase::FAVORITE_ICON = "favorite_icon";
33 const QString BookmarksDatabase::FOLDER_ID = "folder_id";
34 const QString BookmarksDatabase::ID = "_id";
35 const QString BookmarksDatabase::IS_FOLDER = "is_folder";
36 const QString BookmarksDatabase::PARENT_FOLDER_ID = "parent_folder_id";
38 // Construct the class.
39 BookmarksDatabase::BookmarksDatabase() {}
41 void BookmarksDatabase::addDatabase()
43 // Add the bookmarks database.
44 QSqlDatabase bookmarksDatabase = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), CONNECTION_NAME);
46 // Set the database name.
47 bookmarksDatabase.setDatabaseName(QStandardPaths::writableLocation(QStandardPaths::AppDataLocation) + "/bookmarks.db");
50 if (bookmarksDatabase.open()) // Opening the database succeeded.
52 // Check to see if the bookmarks table already exists.
53 if (bookmarksDatabase.tables().contains(BOOKMARKS_TABLE)) // The bookmarks table already exists.
55 // Query the database schema version.
56 QSqlQuery schemaVersionQuery = bookmarksDatabase.exec(QStringLiteral("PRAGMA user_version"));
58 // Move to the first record.
59 schemaVersionQuery.first();
61 // Get the current schema version.
62 int currentSchemaVersion = schemaVersionQuery.value(0).toInt();
64 // Check to see if the schema has been updated.
65 if (currentSchemaVersion < SCHEMA_VERSION)
67 // Run the schema update code.
69 // Update the schema version.
70 bookmarksDatabase.exec("PRAGMA user_version = " + QString::number(SCHEMA_VERSION));
73 else // The bookmarks table does not exist.
75 // Instantiate a create table query.
76 QSqlQuery createTableQuery(bookmarksDatabase);
78 // Populate the create table query.
79 createTableQuery.prepare("CREATE TABLE " + BOOKMARKS_TABLE + "(" +
80 ID + " INTEGER PRIMARY KEY, " +
81 BOOKMARK_NAME + " TEXT, " +
82 BOOKMARK_URL + " TEXT, " +
83 PARENT_FOLDER_ID + " INTEGER DEFAULT 0, " +
84 DISPLAY_ORDER + " INTEGER DEFAULT 0, " +
85 IS_FOLDER + " BOOLEAN DEFAULT FALSE, " +
86 FOLDER_ID + " INTEGER DEFAULT 0, " +
87 FAVORITE_ICON + " BLOB)");
90 if (!createTableQuery.exec())
93 qDebug().noquote().nospace() << "Error creating table: " << bookmarksDatabase.lastError();
96 // Set the schema version.
97 bookmarksDatabase.exec("PRAGMA user_version = " + QString::number(SCHEMA_VERSION));
100 else // Opening the database failed.
102 // Write the last database error message to the debug output.
103 qDebug().noquote().nospace() << "Error opening database: " << bookmarksDatabase.lastError();
107 void BookmarksDatabase::addBookmark(const BookmarkStruct *bookmarkStructPointer)
109 // Get a handle for the bookmarks database.
110 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
112 // Instantiate a count bookmarks query. TODO: This needs to be updated to only count the bookmarks in the current folder.
113 QSqlQuery countBookmarksQuery(bookmarksDatabase);
115 // Set the query to be forward only, which is more performant.
116 countBookmarksQuery.setForwardOnly(true);
118 // Prepare the count bookmarks query.
119 countBookmarksQuery.prepare("SELECT " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE);
121 // Execute the count bookmarks query.
122 countBookmarksQuery.exec();
124 // Move to the last row.
125 countBookmarksQuery.last();
127 // Initialize a bookmarks count variable.
128 int bookmarksCount = 0;
130 // Check to see if the query is valid (there is at least one bookmark).
131 if (countBookmarksQuery.isValid())
133 // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks.
134 bookmarksCount = countBookmarksQuery.at() + 1;
137 // Instantiate an add bookmark query.
138 QSqlQuery addBookmarkQuery(bookmarksDatabase);
140 // Prepare the add bookmark query.
141 addBookmarkQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" +
142 BOOKMARK_NAME + ", " +
143 BOOKMARK_URL + ", " +
144 FAVORITE_ICON + ", " +
145 DISPLAY_ORDER + ") " +
146 "VALUES (:bookmark_name, :bookmark_url, :favorite_icon, :display_order)"
149 // Bind the query values.
150 addBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->bookmarkName);
151 addBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->bookmarkUrl);
152 addBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
153 addBookmarkQuery.bindValue(":display_order", bookmarksCount);
155 // Execute the add bookmark query.
156 addBookmarkQuery.exec();
159 void BookmarksDatabase::deleteBookmark(const int bookmarkId)
161 // Get a handle for the bookmarks database.
162 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
164 // Instantiate a delete bookmark query.
165 QSqlQuery deleteBookmarkQuery(bookmarksDatabase);
167 // Prepare the delete bookmark query.
168 deleteBookmarkQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
170 // Bind the query values.
171 deleteBookmarkQuery.bindValue(":id", bookmarkId);
173 // Execute the query.
174 deleteBookmarkQuery.exec();
176 // Reset the display order for the other items in the folder. TODO: make this folder aware.
177 // TODO: Perhaps, for performance reasons, this shouldn't run each time a bookmarks is deleted, but batched at the end.
179 // Instantiate a bookmarks query.
180 QSqlQuery bookmarksQuery(bookmarksDatabase);
182 // Set the query to be forward only, which is more performant.
183 bookmarksQuery.setForwardOnly(true);
185 // Prepare the bookmarks query.
186 bookmarksQuery.prepare("SELECT " + ID + ", " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
188 // Execute the query.
189 bookmarksQuery.exec();
191 // Create a new display order int.
192 int newDisplayOrder = 0;
194 // Update the display order for each bookmark.
195 while (bookmarksQuery.next())
197 // Check if the new display order is different than the current display order.
198 if (bookmarksQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder)
200 // Instantiate an update display order query.
201 QSqlQuery updateDisplayOrderQuery(bookmarksDatabase);
203 // Prepare the update display order query.
204 updateDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " + DISPLAY_ORDER + " = :display_order WHERE " + ID + " = :id");
206 // Bind the query values.
207 updateDisplayOrderQuery.bindValue(":display_order", newDisplayOrder);
208 updateDisplayOrderQuery.bindValue(":id", bookmarksQuery.value(ID).toInt());
210 // Execute the query.
211 updateDisplayOrderQuery.exec();
214 // Increment the new display order.
219 BookmarkStruct *BookmarksDatabase::getBookmark(int bookmarkId)
221 // Get a handle for the bookmarks database.
222 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
224 // Instantiate a bookmark query.
225 QSqlQuery bookmarkQuery(bookmarksDatabase);
227 // Set the query to be forward only, which is more performant.
228 bookmarkQuery.setForwardOnly(true);
230 // Prepare the bookmark query.
231 bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
233 // Bind the query values.
234 bookmarkQuery.bindValue(":id", bookmarkId);
236 // Execute the query.
237 bookmarkQuery.exec();
239 // Move to the first entry.
240 bookmarkQuery.first();
242 // Create a bookmark struct.
243 struct BookmarkStruct *bookmarkStructPointer = new BookmarkStruct();
245 // Get the favorite icon base 64 byte array.
246 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarkQuery.value(FAVORITE_ICON).toByteArray());
248 // Create a favorite icon pixmap.
249 QPixmap favoriteIconPixmap;
251 // Load the pixmap from byte array.
252 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
254 // Populate the bookmark struct.
255 bookmarkStructPointer->id = bookmarkQuery.value(ID).toInt();
256 bookmarkStructPointer->bookmarkName = bookmarkQuery.value(BOOKMARK_NAME).toString();
257 bookmarkStructPointer->bookmarkUrl = bookmarkQuery.value(BOOKMARK_URL).toString();
258 bookmarkStructPointer->displayOrder = bookmarkQuery.value(DISPLAY_ORDER).toInt();
259 bookmarkStructPointer->favoriteIcon = QIcon(favoriteIconPixmap);
261 // Return the bookmark struct pointer.
262 return bookmarkStructPointer;
265 std::list<BookmarkStruct>* BookmarksDatabase::getBookmarks()
267 // Get a handle for the bookmarks database.
268 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
270 // Instantiate a bookmarks query.
271 QSqlQuery bookmarksQuery(bookmarksDatabase);
273 // Set the query to be forward only, which is more performant.
274 bookmarksQuery.setForwardOnly(true);
276 // Prepare the bookmarks query.
277 bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
279 // Execute the query.
280 bookmarksQuery.exec();
282 // Create a bookmark list.
283 std::list<BookmarkStruct> *bookmarkListPointer = new std::list<BookmarkStruct>;
285 // Populate the bookmark list.
286 while (bookmarksQuery.next())
288 // Create a bookmark struct.
289 struct BookmarkStruct bookmarkStruct;
291 // Get the favorite icon base 64 byte array.
292 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
294 // Create a favorite icon pixmap.
295 QPixmap favoriteIconPixmap;
297 // Load the pixmap from byte array.
298 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
300 // Populate the bookmark struct.
301 bookmarkStruct.id = bookmarksQuery.value(ID).toInt();
302 bookmarkStruct.bookmarkName = bookmarksQuery.value(BOOKMARK_NAME).toString();
303 bookmarkStruct.bookmarkUrl = bookmarksQuery.value(BOOKMARK_URL).toString();
304 bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
305 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
307 // Add the bookmark to the list.
308 bookmarkListPointer->push_back(bookmarkStruct);
311 // Return the bookmark list.
312 return bookmarkListPointer;
315 QList<BookmarkStruct>* BookmarksDatabase::getBookmarksExcept(QList<int> *exceptDatabaseIdsListPointer)
317 // Get a handle for the bookmarks database.
318 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
320 // Instantiate a bookmarks query.
321 QSqlQuery bookmarksQuery(bookmarksDatabase);
323 // Set the query to be forward only, which is more performant.
324 bookmarksQuery.setForwardOnly(true);
326 // Create an IDs not to get string.
327 QString idsNotToGetString;
329 for (const int databaseId : *exceptDatabaseIdsListPointer)
331 // Check to see if there the string already has at least one number.
332 if (!idsNotToGetString.isEmpty())
334 // This is not the first number, so add a `,`.
335 idsNotToGetString.append(QLatin1Char(','));
338 // Append the database ID.
339 idsNotToGetString.append(QString::number(databaseId));
342 // Prepare the bookmarks query.
343 bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " NOT IN (" + idsNotToGetString + ") ORDER BY " + DISPLAY_ORDER + " ASC");
345 // Execute the query.
346 bookmarksQuery.exec();
348 // Create a bookmark list.
349 QList<BookmarkStruct> *bookmarkListPointer = new QList<BookmarkStruct>;
351 // Populate the bookmark list.
352 while (bookmarksQuery.next())
354 // Create a bookmark struct.
355 struct BookmarkStruct bookmarkStruct;
357 // Get the favorite icon base 64 byte array.
358 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
360 // Create a favorite icon pixmap.
361 QPixmap favoriteIconPixmap;
363 // Load the pixmap from byte array.
364 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
366 // Populate the bookmark struct.
367 bookmarkStruct.id = bookmarksQuery.value(ID).toInt();
368 bookmarkStruct.bookmarkName = bookmarksQuery.value(BOOKMARK_NAME).toString();
369 bookmarkStruct.bookmarkUrl = bookmarksQuery.value(BOOKMARK_URL).toString();
370 bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
371 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
373 // Add the bookmark to the list.
374 bookmarkListPointer->push_back(bookmarkStruct);
377 // Return the bookmark list.
378 return bookmarkListPointer;
381 QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon)
383 // Get a favorite icon pixmap.
384 QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
386 // Create a favorite icon byte array.
387 QByteArray favoriteIconByteArray;
389 // Create a favorite icon buffer.
390 QBuffer favoriteIconBuffer(&favoriteIconByteArray);
393 favoriteIconBuffer.open(QIODevice::WriteOnly);
395 // Convert the favorite icon pixmap into a byte array in PNG format.
396 favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
399 favoriteIconBuffer.close();
401 // Convert the favorite icon byte array to a base 64 string.
402 QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
404 // Return the favorite icon base 64 string.
405 return favoriteIconBase64String;
408 void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer)
410 // Get a handle for the bookmarks database.
411 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
413 // Instantiate an update bookmark name.
414 QSqlQuery updateBookmarkQuery(bookmarksDatabase);
416 // Prepare the update bookmark query.
417 updateBookmarkQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " +
418 BOOKMARK_NAME + " = :bookmark_name, " +
419 BOOKMARK_URL + " = :bookmark_url, " +
420 DISPLAY_ORDER + " = :display_order, " +
421 FAVORITE_ICON + "= :favorite_icon " +
422 "WHERE " + ID + " = :id");
424 // Bind the query values.
425 updateBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->bookmarkName);
426 updateBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->bookmarkUrl);
427 updateBookmarkQuery.bindValue(":display_order", bookmarkStructPointer->displayOrder);
428 updateBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
429 updateBookmarkQuery.bindValue(":id", bookmarkStructPointer->id);
431 // Execute the query.
432 updateBookmarkQuery.exec();
435 void BookmarksDatabase::updateDisplayOrder(const int bookmarkId, const int displayOrder)
437 // Get a handle for the bookmarks database.
438 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
440 // Instantiate an update bookmark display order query.
441 QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
443 // Prepare the update bookmark display order query.
444 updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
445 " SET " + DISPLAY_ORDER + " = :display_order " +
446 "WHERE " + ID + " = :id");
448 // Bind the query values.
449 updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
450 updateBookmarkDisplayOrderQuery.bindValue(":id", bookmarkId);
452 // Execute the query.
453 updateBookmarkDisplayOrderQuery.exec();
456 void BookmarksDatabase::updateBookmarkName(const int bookmarkId, const QString &bookmarkName)
458 // Get a handle for the bookmarks database.
459 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
461 // Instantiate an update bookmark name query.
462 QSqlQuery updateBookmarkNameQuery(bookmarksDatabase);
464 // Prepare the update bookmark name query.
465 updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
466 " SET " + BOOKMARK_NAME + " = :bookmark_name " +
467 "WHERE " + ID + " = :id");
469 // Bind the query values.
470 updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName);
471 updateBookmarkNameQuery.bindValue(":id", bookmarkId);
473 // Execute the query.
474 updateBookmarkNameQuery.exec();
477 void BookmarksDatabase::updateBookmarkUrl(const int bookmarkId, const QString &bookmarkUrl)
479 // Get a handle for the bookmarks database.
480 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
482 // Instantiate an update bookmark URL query.
483 QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase);
485 // Prepare the update bookmark URL query.
486 updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
487 " SET " + BOOKMARK_URL + " = :bookmark_url " +
488 "WHERE " + ID + " = :id");
490 // Bind the query values.
491 updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl);
492 updateBookmarkUrlQuery.bindValue(":id", bookmarkId);
494 // Execute the query.
495 updateBookmarkUrlQuery.exec();