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 // Get the folder item count.
113 int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId);
115 // Instantiate an add bookmark query.
116 QSqlQuery addBookmarkQuery(bookmarksDatabase);
118 // Prepare the add bookmark query.
119 addBookmarkQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" +
120 BOOKMARK_NAME + ", " +
121 BOOKMARK_URL + ", " +
122 PARENT_FOLDER_ID + ", " +
123 DISPLAY_ORDER + ", " +
124 FAVORITE_ICON + ") " +
125 "VALUES (:bookmark_name, :bookmark_url, :parent_folder_id, :display_order, :favorite_icon)"
128 // Bind the query values.
129 addBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->name);
130 addBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->url);
131 addBookmarkQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId);
132 addBookmarkQuery.bindValue(":display_order", folderItemCount);
133 addBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
135 // Execute the add bookmark query.
136 addBookmarkQuery.exec();
139 void BookmarksDatabase::addFolder(const BookmarkStruct *bookmarkStructPointer)
141 // Get a handle for the bookmarks database.
142 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
144 // Get the folder item count.
145 int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId);
147 // Instantiate an add folder query.
148 QSqlQuery addFolderQuery(bookmarksDatabase);
150 // Prepare the add folder query.
151 addFolderQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" +
152 BOOKMARK_NAME + ", " +
153 PARENT_FOLDER_ID + ", " +
154 DISPLAY_ORDER + ", " +
157 FAVORITE_ICON + ") " +
158 "VALUES (:bookmark_name, :parent_folder_id, :display_order, :is_folder, :folder_id, :favorite_icon)"
161 // Bind the query values.
162 addFolderQuery.bindValue(":bookmark_name", bookmarkStructPointer->name);
163 addFolderQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId);
164 addFolderQuery.bindValue(":display_order", folderItemCount);
165 addFolderQuery.bindValue(":is_folder", 1);
166 addFolderQuery.bindValue(":folder_id", generateFolderId());
167 addFolderQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
169 // Execute the add folder query.
170 addFolderQuery.exec();
173 void BookmarksDatabase::deleteBookmark(const int databaseId)
175 // Get a handle for the bookmarks database.
176 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
178 // Instantiate a delete bookmark query.
179 QSqlQuery deleteBookmarkQuery(bookmarksDatabase);
181 // Prepare the delete bookmark query.
182 deleteBookmarkQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
184 // Bind the query values.
185 deleteBookmarkQuery.bindValue(":id", databaseId);
187 // Execute the query.
188 deleteBookmarkQuery.exec();
191 void BookmarksDatabase::deleteBookmarks(const QString url)
193 // Get a handle for the bookmarks database.
194 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
196 // Instantiate a parent folder IDs query.
197 QSqlQuery parentFolderIdsQuery(bookmarksDatabase);
199 // Prepare the parent folder IDs query.
200 parentFolderIdsQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url");
202 // Bind the query values.
203 parentFolderIdsQuery.bindValue(":url", url);
205 // Execute the query.
206 parentFolderIdsQuery.exec();
208 // Instantiate a delete bookmarks query.
209 QSqlQuery deleteBookmarksQuery(bookmarksDatabase);
211 // Prepare the delete bookmark query.
212 deleteBookmarksQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url");
214 // Bind the query values.
215 deleteBookmarksQuery.bindValue(":url", url);
217 // Execute the query.
218 deleteBookmarksQuery.exec();
220 // Create a parent folder IDs list. A standard list can be sorted and deduplicated.
221 std::list<double> parentFolderIdsList;
223 // Populate the parent folder IDs list.
224 while (parentFolderIdsQuery.next())
226 // Add the parent folder ID to the list.
227 parentFolderIdsList.push_back(parentFolderIdsQuery.value(PARENT_FOLDER_ID).toDouble());
230 // Sort the parent folder IDs list.
231 parentFolderIdsList.sort();
233 // Remove duplicate entries from the parent folder IDs list.
234 parentFolderIdsList.unique();
236 // Update the display order of each folder where a bookmark was deleted.
237 for (const double parentFolderId : parentFolderIdsList)
238 updateFolderContentsDisplayOrder(parentFolderId);
241 double BookmarksDatabase::generateFolderId()
243 // Get the current time in epoch format (milliseconds).
244 double possibleFolderId = std::chrono::duration_cast<std::chrono::milliseconds>(std::chrono::system_clock::now().time_since_epoch()).count();
246 // Get a handle for the bookmarks database.
247 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
249 // Instantiate a existing folder query.
250 QSqlQuery existingFolderQuery(bookmarksDatabase);
252 // Prepare the existing folder query.
253 existingFolderQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :possible_folder_id");
255 // Bind the query values.
256 existingFolderQuery.bindValue(":possible_folder_id", possibleFolderId);
258 // Execute the query.
259 existingFolderQuery.exec();
261 // Generate a new folder ID if this one is not unique. The existing folder query will only be valid if there is at least one item.
262 if (existingFolderQuery.isValid())
263 possibleFolderId = generateFolderId();
265 return possibleFolderId;
268 QList<QString>* BookmarksDatabase::getAllFolderUrls(const double folderId)
270 // Get a handle for the bookmarks database.
271 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
273 // Instantiate a folder URLs query.
274 QSqlQuery folderUrlsQuery(bookmarksDatabase);
276 // Set the query to be forward only, which is more performant.
277 folderUrlsQuery.setForwardOnly(true);
279 // Prepare the folder URLs query.
280 folderUrlsQuery.prepare("SELECT " + BOOKMARK_URL + ", " + IS_FOLDER + ", " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
282 // Bind the query values.
283 folderUrlsQuery.bindValue(":parent_folder_id", folderId);
285 // Execute the query.
286 folderUrlsQuery.exec();
288 // Create a folder URLs list.
289 QList<QString> *folderUrlsListPointer = new QList<QString>;
291 // Populate the folder URLs list.
292 while (folderUrlsQuery.next())
294 // Process the entry according to the type.
295 if (folderUrlsQuery.value(IS_FOLDER).toBool()) // This is a folder.
297 // Get the subfolder URLs to the list.
298 folderUrlsListPointer->append(*getAllFolderUrls(folderUrlsQuery.value(FOLDER_ID).toDouble()));
300 else // This is a bookmark.
302 // Add the URL to the list.
303 folderUrlsListPointer->append(folderUrlsQuery.value(BOOKMARK_URL).toString());
307 // Return the folder URLs list.
308 return folderUrlsListPointer;
311 BookmarkStruct* BookmarksDatabase::getBookmark(const int databaseId)
313 // Get a handle for the bookmarks database.
314 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
316 // Instantiate a bookmark query.
317 QSqlQuery bookmarkQuery(bookmarksDatabase);
319 // Set the query to be forward only, which is more performant.
320 bookmarkQuery.setForwardOnly(true);
322 // Prepare the bookmark query.
323 bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
325 // Bind the query values.
326 bookmarkQuery.bindValue(":id", databaseId);
328 // Execute the query.
329 bookmarkQuery.exec();
331 // Move to the first entry.
332 bookmarkQuery.first();
334 // Create a bookmark struct.
335 struct BookmarkStruct *bookmarkStructPointer = new BookmarkStruct();
337 // Get the favorite icon base 64 byte array.
338 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarkQuery.value(FAVORITE_ICON).toByteArray());
340 // Create a favorite icon pixmap.
341 QPixmap favoriteIconPixmap;
343 // Load the pixmap from byte array.
344 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
346 // Populate the bookmark struct.
347 bookmarkStructPointer->databaseId = bookmarkQuery.value(ID).toInt();
348 bookmarkStructPointer->name = bookmarkQuery.value(BOOKMARK_NAME).toString();
349 bookmarkStructPointer->url = bookmarkQuery.value(BOOKMARK_URL).toString();
350 bookmarkStructPointer->parentFolderId = bookmarkQuery.value(PARENT_FOLDER_ID).toDouble();
351 bookmarkStructPointer->displayOrder = bookmarkQuery.value(DISPLAY_ORDER).toInt();
352 bookmarkStructPointer->isFolder = bookmarkQuery.value(IS_FOLDER).toBool();
353 bookmarkStructPointer->folderId = bookmarkQuery.value(FOLDER_ID).toDouble();
354 bookmarkStructPointer->favoriteIcon = QIcon(favoriteIconPixmap);
356 // Return the bookmark struct pointer.
357 return bookmarkStructPointer;
360 std::list<BookmarkStruct>* BookmarksDatabase::getBookmarks()
362 // Get a handle for the bookmarks database.
363 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
365 // Instantiate a bookmarks query.
366 QSqlQuery bookmarksQuery(bookmarksDatabase);
368 // Set the query to be forward only, which is more performant.
369 bookmarksQuery.setForwardOnly(true);
371 // Prepare the bookmarks query.
372 bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
374 // Execute the query.
375 bookmarksQuery.exec();
377 // Create a bookmark list.
378 std::list<BookmarkStruct> *bookmarkListPointer = new std::list<BookmarkStruct>;
380 // Populate the bookmark list.
381 while (bookmarksQuery.next())
383 // Create a bookmark struct.
384 struct BookmarkStruct bookmarkStruct;
386 // Get the favorite icon base 64 byte array.
387 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
389 // Create a favorite icon pixmap.
390 QPixmap favoriteIconPixmap;
392 // Load the pixmap from byte array.
393 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
395 // Populate the bookmark struct.
396 bookmarkStruct.databaseId = bookmarksQuery.value(ID).toInt();
397 bookmarkStruct.name = bookmarksQuery.value(BOOKMARK_NAME).toString();
398 bookmarkStruct.url = bookmarksQuery.value(BOOKMARK_URL).toString();
399 bookmarkStruct.parentFolderId = bookmarksQuery.value(PARENT_FOLDER_ID).toDouble();
400 bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
401 bookmarkStruct.isFolder = bookmarksQuery.value(IS_FOLDER).toBool();
402 bookmarkStruct.folderId = bookmarksQuery.value(FOLDER_ID).toDouble();
403 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
405 // Add the bookmark to the list.
406 bookmarkListPointer->push_back(bookmarkStruct);
409 // Return the bookmark list.
410 return bookmarkListPointer;
413 QList<BookmarkStruct>* BookmarksDatabase::getBookmarksInFolderExcept(const double folderId, QList<int> *exceptDatabaseIdsListPointer)
415 // Get a handle for the bookmarks database.
416 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
418 // Instantiate a bookmarks query.
419 QSqlQuery bookmarksQuery(bookmarksDatabase);
421 // Set the query to be forward only, which is more performant.
422 bookmarksQuery.setForwardOnly(true);
424 // Create an IDs not to get string.
425 QString idsNotToGetString;
427 for (const int databaseId : *exceptDatabaseIdsListPointer)
429 // Check to see if there the string already has at least one number.
430 if (!idsNotToGetString.isEmpty())
432 // This is not the first number, so add a `,`.
433 idsNotToGetString.append(QLatin1Char(','));
436 // Append the database ID.
437 idsNotToGetString.append(QString::number(databaseId));
440 // Prepare the bookmarks query.
441 bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id AND " + ID + " NOT IN (" + idsNotToGetString + ") ORDER BY " + DISPLAY_ORDER + " ASC");
443 // Bind the query values.
444 bookmarksQuery.bindValue(":parent_folder_id", folderId);
446 // Execute the query.
447 bookmarksQuery.exec();
449 // Create a bookmark list.
450 QList<BookmarkStruct> *bookmarkListPointer = new QList<BookmarkStruct>;
452 // Populate the bookmark list.
453 while (bookmarksQuery.next())
455 // Create a bookmark struct.
456 struct BookmarkStruct bookmarkStruct;
458 // Get the favorite icon base 64 byte array.
459 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
461 // Create a favorite icon pixmap.
462 QPixmap favoriteIconPixmap;
464 // Load the pixmap from byte array.
465 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
467 // Populate the bookmark struct.
468 bookmarkStruct.databaseId = bookmarksQuery.value(ID).toInt();
469 bookmarkStruct.name = bookmarksQuery.value(BOOKMARK_NAME).toString();
470 bookmarkStruct.url = bookmarksQuery.value(BOOKMARK_URL).toString();
471 bookmarkStruct.parentFolderId = bookmarksQuery.value(PARENT_FOLDER_ID).toDouble();
472 bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
473 bookmarkStruct.isFolder = bookmarksQuery.value(IS_FOLDER).toBool();
474 bookmarkStruct.folderId = bookmarksQuery.value(FOLDER_ID).toDouble();
475 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
477 // Add the bookmark to the list.
478 bookmarkListPointer->push_back(bookmarkStruct);
481 // Return the bookmark list.
482 return bookmarkListPointer;
485 QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon)
487 // Get a favorite icon pixmap.
488 QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
490 // Create a favorite icon byte array.
491 QByteArray favoriteIconByteArray;
493 // Create a favorite icon buffer.
494 QBuffer favoriteIconBuffer(&favoriteIconByteArray);
497 favoriteIconBuffer.open(QIODevice::WriteOnly);
499 // Convert the favorite icon pixmap into a byte array in PNG format.
500 favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
503 favoriteIconBuffer.close();
505 // Convert the favorite icon byte array to a base 64 string.
506 QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
508 // Return the favorite icon base 64 string.
509 return favoriteIconBase64String;
512 QList<BookmarkStruct>* BookmarksDatabase::getFolderContents(const double folderId)
514 // Get a handle for the bookmarks database.
515 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
517 // Instantiate a folder contents query.
518 QSqlQuery folderContentsQuery(bookmarksDatabase);
520 // Set the query to be forward only, which is more performant.
521 folderContentsQuery.setForwardOnly(true);
523 // Prepare the folder contents query.
524 folderContentsQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC");
526 // Bind the query values.
527 folderContentsQuery.bindValue(":parent_folder_id", folderId);
529 // Execute the query.
530 folderContentsQuery.exec();
532 // Create a folder contents list.
533 QList<BookmarkStruct> *folderContentsListPointer = new QList<BookmarkStruct>;
535 // Populate the folder contents list.
536 while (folderContentsQuery.next())
538 // Create a bookmark struct.
539 struct BookmarkStruct bookmarkStruct;
541 // Get the favorite icon base 64 byte array.
542 QByteArray favoriteIconByteArray = QByteArray::fromBase64(folderContentsQuery.value(FAVORITE_ICON).toByteArray());
544 // Create a favorite icon pixmap.
545 QPixmap favoriteIconPixmap;
547 // Load the pixmap from byte array.
548 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
550 // Populate the bookmark struct.
551 bookmarkStruct.databaseId = folderContentsQuery.value(ID).toInt();
552 bookmarkStruct.name = folderContentsQuery.value(BOOKMARK_NAME).toString();
553 bookmarkStruct.url = folderContentsQuery.value(BOOKMARK_URL).toString();
554 bookmarkStruct.parentFolderId = folderContentsQuery.value(PARENT_FOLDER_ID).toDouble();
555 bookmarkStruct.displayOrder = folderContentsQuery.value(DISPLAY_ORDER).toInt();
556 bookmarkStruct.isFolder = folderContentsQuery.value(IS_FOLDER).toBool();
557 bookmarkStruct.folderId = folderContentsQuery.value(FOLDER_ID).toDouble();
558 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
560 // Add the item to the list.
561 folderContentsListPointer->append(bookmarkStruct);
564 // Return the folder contents list.
565 return folderContentsListPointer;
568 QList<int>* BookmarksDatabase::getFolderContentsDatabaseIds(const double folderId)
570 // Get a handle for the bookmarks database.
571 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
573 // Instantiate a folder contents query.
574 QSqlQuery folderContentsQuery(bookmarksDatabase);
576 // Set the query to be forward only, which is more performant.
577 folderContentsQuery.setForwardOnly(true);
579 // Prepare the folder contents query.
580 folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
582 // Bind the query values.
583 folderContentsQuery.bindValue(":parent_folder_id", folderId);
585 // Execute the query.
586 folderContentsQuery.exec();
588 // Create a folder contents database ID list.
589 QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
591 // Populate the folder contents list.
592 while (folderContentsQuery.next())
594 // Add the database ID to the list.
595 folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
598 // Return the folder contents database ID list.
599 return folderContentsDatabaseIdsListPointer;
602 QList<int> *BookmarksDatabase::getFolderContentsDatabaseIdsRecursively(const double folderId)
604 // Get a handle for the bookmarks database.
605 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
607 // Instantiate a folder contents query.
608 QSqlQuery folderContentsQuery(bookmarksDatabase);
610 // Set the query to be forward only, which is more performant.
611 folderContentsQuery.setForwardOnly(true);
613 // Prepare the folder contents query.
614 folderContentsQuery.prepare("SELECT " + ID + ", " + IS_FOLDER + ", " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
616 // Bind the query values.
617 folderContentsQuery.bindValue(":parent_folder_id", folderId);
619 // Execute the query.
620 folderContentsQuery.exec();
622 // Create a folder contents database ID list.
623 QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
625 // Populate the folder contents list.
626 while (folderContentsQuery.next())
628 // Add the database ID to the list.
629 folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
631 // Recursively get the contents if this is a subfolder.
632 if (folderContentsQuery.value(IS_FOLDER).toBool())
633 folderContentsDatabaseIdsListPointer->append(*getFolderContentsDatabaseIdsRecursively(folderContentsQuery.value(FOLDER_ID).toDouble()));
636 // Return the folder contents database ID list.
637 return folderContentsDatabaseIdsListPointer;
640 int BookmarksDatabase::getFolderDatabaseId(const double folderId)
642 // Get a handle for the bookmarks database.
643 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
645 // Instantiate a folder database ID query.
646 QSqlQuery folderDatabaseIdQuery(bookmarksDatabase);
648 // Set the query to be forward only, which is more performant.
649 folderDatabaseIdQuery.setForwardOnly(true);
651 // Prepare the folder database ID query.
652 folderDatabaseIdQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :folder_id");
654 // Bind the query values.
655 folderDatabaseIdQuery.bindValue(":folder_id", folderId);
657 // Execute the query.
658 folderDatabaseIdQuery.exec();
660 // Move to the first entry.
661 folderDatabaseIdQuery.first();
663 // Return the folder database ID.
664 return folderDatabaseIdQuery.value(ID).toInt();
667 double BookmarksDatabase::getFolderId(const int databaseId)
669 // Get a handle for the bookmarks database.
670 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
672 // Instantiate a folder ID query.
673 QSqlQuery folderIdQuery(bookmarksDatabase);
675 // Set the query to be forward only, which is more performant.
676 folderIdQuery.setForwardOnly(true);
678 // Prepare the folder ID query.
679 folderIdQuery.prepare("SELECT " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
681 // Bind the query values.
682 folderIdQuery.bindValue(":database_id", databaseId);
684 // Execute the query.
685 folderIdQuery.exec();
687 // Move to the first entry.
688 folderIdQuery.first();
690 // Return the folder ID.
691 return folderIdQuery.value(FOLDER_ID).toDouble();
694 int BookmarksDatabase::getFolderItemCount(const double folderId)
696 // Get a handle for the bookmarks database.
697 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
699 // Instantiate a folder contents query.
700 QSqlQuery folderContentsQuery(bookmarksDatabase);
702 // Set the query to be forward only, which is more performant.
703 folderContentsQuery.setForwardOnly(true);
705 // Prepare the folder contents query.
706 folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
708 // Bind the query values.
709 folderContentsQuery.bindValue(":parent_folder_id", folderId);
711 // Execute the query.
712 folderContentsQuery.exec();
714 // Move to the last row.
715 folderContentsQuery.last();
717 // Initialize an item count variable.
720 // Check to see if the query is valid (there is at least one item).
721 if (folderContentsQuery.isValid())
723 // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks.
724 itemCount = folderContentsQuery.at() + 1;
727 // Return the item count.
731 double BookmarksDatabase::getParentFolderId(const int databaseId)
733 // Get a handle for the bookmarks database.
734 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
736 // Instantiate a parent folder ID query.
737 QSqlQuery parentFolderIdQuery(bookmarksDatabase);
739 // Set the query to be forward only, which is more performant.
740 parentFolderIdQuery.setForwardOnly(true);
742 // Prepare the parent folder ID query.
743 parentFolderIdQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
745 // Bind the query values.
746 parentFolderIdQuery.bindValue(":database_id", databaseId);
748 // Execute the query.
749 parentFolderIdQuery.exec();
751 // Move to the first entry.
752 parentFolderIdQuery.first();
754 // Return the parent folder ID.
755 return parentFolderIdQuery.value(PARENT_FOLDER_ID).toDouble();
758 QList<BookmarkStruct>* BookmarksDatabase::getSubfolders(const double folderId)
760 // Get a handle for the bookmarks database.
761 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
763 // Instantiate a subfolders query.
764 QSqlQuery subfoldersQuery(bookmarksDatabase);
766 // Set the query to be forward only, which is more performant.
767 subfoldersQuery.setForwardOnly(true);
769 // Prepare the subfolders query.
770 subfoldersQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 1 AND " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC");
772 // Bind the query values.
773 subfoldersQuery.bindValue(":parent_folder_id", folderId);
775 // Execute the query.
776 subfoldersQuery.exec();
778 // Create a subfolder list.
779 QList<BookmarkStruct> *subfoldersListPointer = new QList<BookmarkStruct>;
781 // Populate the subfolder list.
782 while (subfoldersQuery.next())
784 // Create a bookmark struct.
785 struct BookmarkStruct bookmarkStruct;
787 // Get the favorite icon base 64 byte array.
788 QByteArray favoriteIconByteArray = QByteArray::fromBase64(subfoldersQuery.value(FAVORITE_ICON).toByteArray());
790 // Create a favorite icon pixmap.
791 QPixmap favoriteIconPixmap;
793 // Load the pixmap from byte array.
794 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
796 // Populate the bookmark struct.
797 bookmarkStruct.databaseId = subfoldersQuery.value(ID).toInt();
798 bookmarkStruct.name = subfoldersQuery.value(BOOKMARK_NAME).toString();
799 bookmarkStruct.parentFolderId = subfoldersQuery.value(PARENT_FOLDER_ID).toDouble();
800 bookmarkStruct.displayOrder = subfoldersQuery.value(DISPLAY_ORDER).toInt();
801 bookmarkStruct.isFolder = subfoldersQuery.value(IS_FOLDER).toBool();
802 bookmarkStruct.folderId = subfoldersQuery.value(FOLDER_ID).toDouble();
803 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
805 // Add the subfolder to the list.
806 subfoldersListPointer->append(bookmarkStruct);
809 // Return the subfolders list.
810 return subfoldersListPointer;
813 bool BookmarksDatabase::isBookmarked(const QString url)
815 // Get a handle for the bookmarks database.
816 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
818 // Instantiate an is bookmarked query.
819 QSqlQuery isBookmarkedQuery(bookmarksDatabase);
821 // Set the query to be forward only, which is more performant.
822 isBookmarkedQuery.setForwardOnly(true);
824 // Prepare the is bookmarked query.
825 isBookmarkedQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url");
827 // Bind the query values.
828 isBookmarkedQuery.bindValue(":url", url);
830 // Execute the query.
831 isBookmarkedQuery.exec();
833 // Move to the first entry.
834 isBookmarkedQuery.first();
836 // Return true if the query is valid (there is at least one item).
837 return isBookmarkedQuery.isValid();
840 bool BookmarksDatabase::isFolder(const int databaseId)
842 // Get a handle for the bookmarks database.
843 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
845 // Instantiate an is folder query.
846 QSqlQuery isFolderQuery(bookmarksDatabase);
848 // Set the query to be forward only, which is more performant.
849 isFolderQuery.setForwardOnly(true);
851 // Prepare the is folder query.
852 isFolderQuery.prepare("SELECT " + IS_FOLDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
854 // Bind the query values.
855 isFolderQuery.bindValue(":id", databaseId);
857 // Execute the query.
858 isFolderQuery.exec();
860 // Move to the first entry.
861 isFolderQuery.first();
863 // Return the folder status.
864 return isFolderQuery.value(IS_FOLDER).toBool();
867 void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer)
869 // Get a handle for the bookmarks database.
870 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
872 // Instantiate an update bookmark query.
873 QSqlQuery updateBookmarkQuery(bookmarksDatabase);
875 // Prepare the update bookmark query.
876 updateBookmarkQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " +
877 BOOKMARK_NAME + " = :bookmark_name, " +
878 BOOKMARK_URL + " = :bookmark_url, " +
879 PARENT_FOLDER_ID + " = :parent_folder_id, " +
880 DISPLAY_ORDER + " = :display_order, " +
881 FAVORITE_ICON + "= :favorite_icon " +
882 "WHERE " + ID + " = :id");
884 // Bind the query values.
885 updateBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->name);
886 updateBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->url);
887 updateBookmarkQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId);
888 updateBookmarkQuery.bindValue(":display_order", bookmarkStructPointer->displayOrder);
889 updateBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
890 updateBookmarkQuery.bindValue(":id", bookmarkStructPointer->databaseId);
892 // Execute the query.
893 updateBookmarkQuery.exec();
896 void BookmarksDatabase::updateBookmarkName(const int databaseId, const QString &bookmarkName)
898 // Get a handle for the bookmarks database.
899 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
901 // Instantiate an update bookmark name query.
902 QSqlQuery updateBookmarkNameQuery(bookmarksDatabase);
904 // Prepare the update bookmark name query.
905 updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
906 " SET " + BOOKMARK_NAME + " = :bookmark_name " +
907 "WHERE " + ID + " = :id");
909 // Bind the query values.
910 updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName);
911 updateBookmarkNameQuery.bindValue(":id", databaseId);
913 // Execute the query.
914 updateBookmarkNameQuery.exec();
917 void BookmarksDatabase::updateBookmarkUrl(const int databaseId, const QString &bookmarkUrl)
919 // Get a handle for the bookmarks database.
920 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
922 // Instantiate an update bookmark URL query.
923 QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase);
925 // Prepare the update bookmark URL query.
926 updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
927 " SET " + BOOKMARK_URL + " = :bookmark_url " +
928 "WHERE " + ID + " = :id");
930 // Bind the query values.
931 updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl);
932 updateBookmarkUrlQuery.bindValue(":id", databaseId);
934 // Execute the query.
935 updateBookmarkUrlQuery.exec();
938 void BookmarksDatabase::updateDisplayOrder(const int databaseId, const int displayOrder)
940 // Get a handle for the bookmarks database.
941 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
943 // Instantiate an update bookmark display order query.
944 QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
946 // Prepare the update bookmark display order query.
947 updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
948 " SET " + DISPLAY_ORDER + " = :display_order " +
949 "WHERE " + ID + " = :id");
951 // Bind the query values.
952 updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
953 updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
955 // Execute the query.
956 updateBookmarkDisplayOrderQuery.exec();
959 void BookmarksDatabase::updateFolderContentsDisplayOrder(const double folderId)
961 // Get a handle for the bookmarks database.
962 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
964 // Instantiate a folder contents query.
965 QSqlQuery folderContentsQuery(bookmarksDatabase);
967 // Set the query to be forward only, which is more performant.
968 folderContentsQuery.setForwardOnly(true);
970 // Prepare the folder contents query.
971 folderContentsQuery.prepare("SELECT " + ID + ", " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC");
973 // Bind the query values.
974 folderContentsQuery.bindValue(":parent_folder_id", folderId);
976 // Execute the query.
977 folderContentsQuery.exec();
979 // Define a new display order int.
980 int newDisplayOrder = 0;
982 // Populate the subfolder list.
983 while (folderContentsQuery.next())
985 // Update the display order if it has changed.
986 if (folderContentsQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder)
987 updateDisplayOrder(folderContentsQuery.value(ID).toInt(), newDisplayOrder);
989 // Increment the new display order.
994 void BookmarksDatabase::updateParentFolderAndDisplayOrder(const int databaseId, const double parentFolderId, const int displayOrder)
996 // Get a handle for the bookmarks database.
997 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
999 // Instantiate an update bookmark display order query.
1000 QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
1002 // Prepare the update bookmark display order query.
1003 updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
1004 " SET " + PARENT_FOLDER_ID + " = :parent_folder_id " +
1005 ", " + DISPLAY_ORDER + " = :display_order " +
1006 "WHERE " + ID + " = :id");
1008 // Bind the query values.
1009 updateBookmarkDisplayOrderQuery.bindValue(":parent_folder_id", parentFolderId);
1010 updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
1011 updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
1013 // Execute the query.
1014 updateBookmarkDisplayOrderQuery.exec();