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 double BookmarksDatabase::generateFolderId()
193 // Get the current time in epoch format (milliseconds).
194 double possibleFolderId = std::chrono::duration_cast<std::chrono::milliseconds>(std::chrono::system_clock::now().time_since_epoch()).count();
196 // Get a handle for the bookmarks database.
197 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
199 // Instantiate a existing folder query.
200 QSqlQuery existingFolderQuery(bookmarksDatabase);
202 // Prepare the existing folder query.
203 existingFolderQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :possible_folder_id");
205 // Bind the query values.
206 existingFolderQuery.bindValue(":possible_folder_id", possibleFolderId);
208 // Execute the query.
209 existingFolderQuery.exec();
211 // 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.
212 if (existingFolderQuery.isValid())
213 possibleFolderId = generateFolderId();
215 return possibleFolderId;
218 QList<QString>* BookmarksDatabase::getAllFolderUrls(const double folderId)
220 // Get a handle for the bookmarks database.
221 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
223 // Instantiate a folder URLs query.
224 QSqlQuery folderUrlsQuery(bookmarksDatabase);
226 // Set the query to be forward only, which is more performant.
227 folderUrlsQuery.setForwardOnly(true);
229 // Prepare the folder URLs query.
230 folderUrlsQuery.prepare("SELECT " + BOOKMARK_URL + ", " + IS_FOLDER + ", " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
232 // Bind the query values.
233 folderUrlsQuery.bindValue(":parent_folder_id", folderId);
235 // Execute the query.
236 folderUrlsQuery.exec();
238 // Create a folder URLs list.
239 QList<QString> *folderUrlsListPointer = new QList<QString>;
241 // Populate the folder URLs list.
242 while (folderUrlsQuery.next())
244 // Process the entry according to the type.
245 if (folderUrlsQuery.value(IS_FOLDER).toBool()) // This is a folder.
247 // Get the subfolder URLs to the list.
248 folderUrlsListPointer->append(*getAllFolderUrls(folderUrlsQuery.value(FOLDER_ID).toDouble()));
250 else // This is a bookmark.
252 // Add the URL to the list.
253 folderUrlsListPointer->append(folderUrlsQuery.value(BOOKMARK_URL).toString());
257 // Return the folder URLs list.
258 return folderUrlsListPointer;
261 BookmarkStruct* BookmarksDatabase::getBookmark(const int databaseId)
263 // Get a handle for the bookmarks database.
264 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
266 // Instantiate a bookmark query.
267 QSqlQuery bookmarkQuery(bookmarksDatabase);
269 // Set the query to be forward only, which is more performant.
270 bookmarkQuery.setForwardOnly(true);
272 // Prepare the bookmark query.
273 bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
275 // Bind the query values.
276 bookmarkQuery.bindValue(":id", databaseId);
278 // Execute the query.
279 bookmarkQuery.exec();
281 // Move to the first entry.
282 bookmarkQuery.first();
284 // Create a bookmark struct.
285 struct BookmarkStruct *bookmarkStructPointer = new BookmarkStruct();
287 // Get the favorite icon base 64 byte array.
288 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarkQuery.value(FAVORITE_ICON).toByteArray());
290 // Create a favorite icon pixmap.
291 QPixmap favoriteIconPixmap;
293 // Load the pixmap from byte array.
294 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
296 // Populate the bookmark struct.
297 bookmarkStructPointer->databaseId = bookmarkQuery.value(ID).toInt();
298 bookmarkStructPointer->name = bookmarkQuery.value(BOOKMARK_NAME).toString();
299 bookmarkStructPointer->url = bookmarkQuery.value(BOOKMARK_URL).toString();
300 bookmarkStructPointer->parentFolderId = bookmarkQuery.value(PARENT_FOLDER_ID).toDouble();
301 bookmarkStructPointer->displayOrder = bookmarkQuery.value(DISPLAY_ORDER).toInt();
302 bookmarkStructPointer->isFolder = bookmarkQuery.value(IS_FOLDER).toBool();
303 bookmarkStructPointer->folderId = bookmarkQuery.value(FOLDER_ID).toDouble();
304 bookmarkStructPointer->favoriteIcon = QIcon(favoriteIconPixmap);
306 // Return the bookmark struct pointer.
307 return bookmarkStructPointer;
310 std::list<BookmarkStruct>* BookmarksDatabase::getBookmarks()
312 // Get a handle for the bookmarks database.
313 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
315 // Instantiate a bookmarks query.
316 QSqlQuery bookmarksQuery(bookmarksDatabase);
318 // Set the query to be forward only, which is more performant.
319 bookmarksQuery.setForwardOnly(true);
321 // Prepare the bookmarks query.
322 bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
324 // Execute the query.
325 bookmarksQuery.exec();
327 // Create a bookmark list.
328 std::list<BookmarkStruct> *bookmarkListPointer = new std::list<BookmarkStruct>;
330 // Populate the bookmark list.
331 while (bookmarksQuery.next())
333 // Create a bookmark struct.
334 struct BookmarkStruct bookmarkStruct;
336 // Get the favorite icon base 64 byte array.
337 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
339 // Create a favorite icon pixmap.
340 QPixmap favoriteIconPixmap;
342 // Load the pixmap from byte array.
343 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
345 // Populate the bookmark struct.
346 bookmarkStruct.databaseId = bookmarksQuery.value(ID).toInt();
347 bookmarkStruct.name = bookmarksQuery.value(BOOKMARK_NAME).toString();
348 bookmarkStruct.url = bookmarksQuery.value(BOOKMARK_URL).toString();
349 bookmarkStruct.parentFolderId = bookmarksQuery.value(PARENT_FOLDER_ID).toDouble();
350 bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
351 bookmarkStruct.isFolder = bookmarksQuery.value(IS_FOLDER).toBool();
352 bookmarkStruct.folderId = bookmarksQuery.value(FOLDER_ID).toDouble();
353 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
355 // Add the bookmark to the list.
356 bookmarkListPointer->push_back(bookmarkStruct);
359 // Return the bookmark list.
360 return bookmarkListPointer;
363 QList<BookmarkStruct>* BookmarksDatabase::getBookmarksInFolderExcept(const double folderId, QList<int> *exceptDatabaseIdsListPointer)
365 // Get a handle for the bookmarks database.
366 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
368 // Instantiate a bookmarks query.
369 QSqlQuery bookmarksQuery(bookmarksDatabase);
371 // Set the query to be forward only, which is more performant.
372 bookmarksQuery.setForwardOnly(true);
374 // Create an IDs not to get string.
375 QString idsNotToGetString;
377 for (const int databaseId : *exceptDatabaseIdsListPointer)
379 // Check to see if there the string already has at least one number.
380 if (!idsNotToGetString.isEmpty())
382 // This is not the first number, so add a `,`.
383 idsNotToGetString.append(QLatin1Char(','));
386 // Append the database ID.
387 idsNotToGetString.append(QString::number(databaseId));
390 // Prepare the bookmarks query.
391 bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id AND " + ID + " NOT IN (" + idsNotToGetString + ") ORDER BY " + DISPLAY_ORDER + " ASC");
393 // Bind the query values.
394 bookmarksQuery.bindValue(":parent_folder_id", folderId);
396 // Execute the query.
397 bookmarksQuery.exec();
399 // Create a bookmark list.
400 QList<BookmarkStruct> *bookmarkListPointer = new QList<BookmarkStruct>;
402 // Populate the bookmark list.
403 while (bookmarksQuery.next())
405 // Create a bookmark struct.
406 struct BookmarkStruct bookmarkStruct;
408 // Get the favorite icon base 64 byte array.
409 QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
411 // Create a favorite icon pixmap.
412 QPixmap favoriteIconPixmap;
414 // Load the pixmap from byte array.
415 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
417 // Populate the bookmark struct.
418 bookmarkStruct.databaseId = bookmarksQuery.value(ID).toInt();
419 bookmarkStruct.name = bookmarksQuery.value(BOOKMARK_NAME).toString();
420 bookmarkStruct.url = bookmarksQuery.value(BOOKMARK_URL).toString();
421 bookmarkStruct.parentFolderId = bookmarksQuery.value(PARENT_FOLDER_ID).toDouble();
422 bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
423 bookmarkStruct.isFolder = bookmarksQuery.value(IS_FOLDER).toBool();
424 bookmarkStruct.folderId = bookmarksQuery.value(FOLDER_ID).toDouble();
425 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
427 // Add the bookmark to the list.
428 bookmarkListPointer->push_back(bookmarkStruct);
431 // Return the bookmark list.
432 return bookmarkListPointer;
435 QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon)
437 // Get a favorite icon pixmap.
438 QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
440 // Create a favorite icon byte array.
441 QByteArray favoriteIconByteArray;
443 // Create a favorite icon buffer.
444 QBuffer favoriteIconBuffer(&favoriteIconByteArray);
447 favoriteIconBuffer.open(QIODevice::WriteOnly);
449 // Convert the favorite icon pixmap into a byte array in PNG format.
450 favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
453 favoriteIconBuffer.close();
455 // Convert the favorite icon byte array to a base 64 string.
456 QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
458 // Return the favorite icon base 64 string.
459 return favoriteIconBase64String;
462 QList<BookmarkStruct>* BookmarksDatabase::getFolderContents(const double folderId)
464 // Get a handle for the bookmarks database.
465 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
467 // Instantiate a folder contents query.
468 QSqlQuery folderContentsQuery(bookmarksDatabase);
470 // Set the query to be forward only, which is more performant.
471 folderContentsQuery.setForwardOnly(true);
473 // Prepare the folder contents query.
474 folderContentsQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC");
476 // Bind the query values.
477 folderContentsQuery.bindValue(":parent_folder_id", folderId);
479 // Execute the query.
480 folderContentsQuery.exec();
482 // Create a folder contents list.
483 QList<BookmarkStruct> *folderContentsListPointer = new QList<BookmarkStruct>;
485 // Populate the folder contents list.
486 while (folderContentsQuery.next())
488 // Create a bookmark struct.
489 struct BookmarkStruct bookmarkStruct;
491 // Get the favorite icon base 64 byte array.
492 QByteArray favoriteIconByteArray = QByteArray::fromBase64(folderContentsQuery.value(FAVORITE_ICON).toByteArray());
494 // Create a favorite icon pixmap.
495 QPixmap favoriteIconPixmap;
497 // Load the pixmap from byte array.
498 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
500 // Populate the bookmark struct.
501 bookmarkStruct.databaseId = folderContentsQuery.value(ID).toInt();
502 bookmarkStruct.name = folderContentsQuery.value(BOOKMARK_NAME).toString();
503 bookmarkStruct.url = folderContentsQuery.value(BOOKMARK_URL).toString();
504 bookmarkStruct.parentFolderId = folderContentsQuery.value(PARENT_FOLDER_ID).toDouble();
505 bookmarkStruct.displayOrder = folderContentsQuery.value(DISPLAY_ORDER).toInt();
506 bookmarkStruct.isFolder = folderContentsQuery.value(IS_FOLDER).toBool();
507 bookmarkStruct.folderId = folderContentsQuery.value(FOLDER_ID).toDouble();
508 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
510 // Add the item to the list.
511 folderContentsListPointer->append(bookmarkStruct);
514 // Return the folder contents list.
515 return folderContentsListPointer;
518 QList<int>* BookmarksDatabase::getFolderContentsDatabaseIds(const double folderId)
520 // Get a handle for the bookmarks database.
521 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
523 // Instantiate a folder contents query.
524 QSqlQuery folderContentsQuery(bookmarksDatabase);
526 // Set the query to be forward only, which is more performant.
527 folderContentsQuery.setForwardOnly(true);
529 // Prepare the folder contents query.
530 folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
532 // Bind the query values.
533 folderContentsQuery.bindValue(":parent_folder_id", folderId);
535 // Execute the query.
536 folderContentsQuery.exec();
538 // Create a folder contents database ID list.
539 QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
541 // Populate the folder contents list.
542 while (folderContentsQuery.next())
544 // Add the database ID to the list.
545 folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
548 // Return the folder contents database ID list.
549 return folderContentsDatabaseIdsListPointer;
552 QList<int> *BookmarksDatabase::getFolderContentsDatabaseIdsRecursively(const double folderId)
554 // Get a handle for the bookmarks database.
555 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
557 // Instantiate a folder contents query.
558 QSqlQuery folderContentsQuery(bookmarksDatabase);
560 // Set the query to be forward only, which is more performant.
561 folderContentsQuery.setForwardOnly(true);
563 // Prepare the folder contents query.
564 folderContentsQuery.prepare("SELECT " + ID + ", " + IS_FOLDER + ", " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
566 // Bind the query values.
567 folderContentsQuery.bindValue(":parent_folder_id", folderId);
569 // Execute the query.
570 folderContentsQuery.exec();
572 // Create a folder contents database ID list.
573 QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
575 // Populate the folder contents list.
576 while (folderContentsQuery.next())
578 // Add the database ID to the list.
579 folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
581 // Recursively get the contents if this is a subfolder.
582 if (folderContentsQuery.value(IS_FOLDER).toBool())
583 folderContentsDatabaseIdsListPointer->append(*getFolderContentsDatabaseIdsRecursively(folderContentsQuery.value(FOLDER_ID).toDouble()));
586 // Return the folder contents database ID list.
587 return folderContentsDatabaseIdsListPointer;
590 int BookmarksDatabase::getFolderDatabaseId(const double folderId)
592 // Get a handle for the bookmarks database.
593 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
595 // Instantiate a folder database ID query.
596 QSqlQuery folderDatabaseIdQuery(bookmarksDatabase);
598 // Set the query to be forward only, which is more performant.
599 folderDatabaseIdQuery.setForwardOnly(true);
601 // Prepare the folder database ID query.
602 folderDatabaseIdQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :folder_id");
604 // Bind the query values.
605 folderDatabaseIdQuery.bindValue(":folder_id", folderId);
607 // Execute the query.
608 folderDatabaseIdQuery.exec();
610 // Move to the first entry.
611 folderDatabaseIdQuery.first();
613 // Return the folder database ID.
614 return folderDatabaseIdQuery.value(ID).toInt();
617 double BookmarksDatabase::getFolderId(const int databaseId)
619 // Get a handle for the bookmarks database.
620 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
622 // Instantiate a folder ID query.
623 QSqlQuery folderIdQuery(bookmarksDatabase);
625 // Set the query to be forward only, which is more performant.
626 folderIdQuery.setForwardOnly(true);
628 // Prepare the folder ID query.
629 folderIdQuery.prepare("SELECT " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
631 // Bind the query values.
632 folderIdQuery.bindValue(":database_id", databaseId);
634 // Execute the query.
635 folderIdQuery.exec();
637 // Move to the first entry.
638 folderIdQuery.first();
640 // Return the folder ID.
641 return folderIdQuery.value(FOLDER_ID).toDouble();
644 int BookmarksDatabase::getFolderItemCount(const double folderId)
646 // Get a handle for the bookmarks database.
647 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
649 // Instantiate a folder contents query.
650 QSqlQuery folderContentsQuery(bookmarksDatabase);
652 // Set the query to be forward only, which is more performant.
653 folderContentsQuery.setForwardOnly(true);
655 // Prepare the folder contents query.
656 folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
658 // Bind the query values.
659 folderContentsQuery.bindValue(":parent_folder_id", folderId);
661 // Execute the query.
662 folderContentsQuery.exec();
664 // Move to the last row.
665 folderContentsQuery.last();
667 // Initialize an item count variable.
670 // Check to see if the query is valid (there is at least one item).
671 if (folderContentsQuery.isValid())
673 // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks.
674 itemCount = folderContentsQuery.at() + 1;
677 // Return the item count.
681 double BookmarksDatabase::getParentFolderId(const int databaseId)
683 // Get a handle for the bookmarks database.
684 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
686 // Instantiate a parent folder ID query.
687 QSqlQuery parentFolderIdQuery(bookmarksDatabase);
689 // Set the query to be forward only, which is more performant.
690 parentFolderIdQuery.setForwardOnly(true);
692 // Prepare the parent folder ID query.
693 parentFolderIdQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
695 // Bind the query values.
696 parentFolderIdQuery.bindValue(":database_id", databaseId);
698 // Execute the query.
699 parentFolderIdQuery.exec();
701 // Move to the first entry.
702 parentFolderIdQuery.first();
704 // Return the parent folder ID.
705 return parentFolderIdQuery.value(PARENT_FOLDER_ID).toDouble();
708 QList<BookmarkStruct>* BookmarksDatabase::getSubfolders(const double folderId)
710 // Get a handle for the bookmarks database.
711 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
713 // Instantiate a subfolders query.
714 QSqlQuery subfoldersQuery(bookmarksDatabase);
716 // Set the query to be forward only, which is more performant.
717 subfoldersQuery.setForwardOnly(true);
719 // Prepare the subfolders query.
720 subfoldersQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 1 AND " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC");
722 // Bind the query values.
723 subfoldersQuery.bindValue(":parent_folder_id", folderId);
725 // Execute the query.
726 subfoldersQuery.exec();
728 // Create a subfolder list.
729 QList<BookmarkStruct> *subfoldersListPointer = new QList<BookmarkStruct>;
731 // Populate the subfolder list.
732 while (subfoldersQuery.next())
734 // Create a bookmark struct.
735 struct BookmarkStruct bookmarkStruct;
737 // Get the favorite icon base 64 byte array.
738 QByteArray favoriteIconByteArray = QByteArray::fromBase64(subfoldersQuery.value(FAVORITE_ICON).toByteArray());
740 // Create a favorite icon pixmap.
741 QPixmap favoriteIconPixmap;
743 // Load the pixmap from byte array.
744 favoriteIconPixmap.loadFromData(favoriteIconByteArray);
746 // Populate the bookmark struct.
747 bookmarkStruct.databaseId = subfoldersQuery.value(ID).toInt();
748 bookmarkStruct.name = subfoldersQuery.value(BOOKMARK_NAME).toString();
749 bookmarkStruct.parentFolderId = subfoldersQuery.value(PARENT_FOLDER_ID).toDouble();
750 bookmarkStruct.displayOrder = subfoldersQuery.value(DISPLAY_ORDER).toInt();
751 bookmarkStruct.isFolder = subfoldersQuery.value(IS_FOLDER).toBool();
752 bookmarkStruct.folderId = subfoldersQuery.value(FOLDER_ID).toDouble();
753 bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
755 // Add the subfolder to the list.
756 subfoldersListPointer->append(bookmarkStruct);
759 // Return the subfolders list.
760 return subfoldersListPointer;
763 bool BookmarksDatabase::isFolder(const int databaseId)
765 // Get a handle for the bookmarks database.
766 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
768 // Instantiate an is folder query.
769 QSqlQuery isFolderQuery(bookmarksDatabase);
771 // Set the query to be forward only, which is more performant.
772 isFolderQuery.setForwardOnly(true);
774 // Prepare the is folder query.
775 isFolderQuery.prepare("SELECT " + IS_FOLDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
777 // Bind the query values.
778 isFolderQuery.bindValue(":id", databaseId);
780 // Execute the query.
781 isFolderQuery.exec();
783 // Move to the first entry.
784 isFolderQuery.first();
786 // Return the folder status.
787 return isFolderQuery.value(IS_FOLDER).toBool();
790 void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer)
792 // Get a handle for the bookmarks database.
793 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
795 // Instantiate an update bookmark query.
796 QSqlQuery updateBookmarkQuery(bookmarksDatabase);
798 // Prepare the update bookmark query.
799 updateBookmarkQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " +
800 BOOKMARK_NAME + " = :bookmark_name, " +
801 BOOKMARK_URL + " = :bookmark_url, " +
802 PARENT_FOLDER_ID + " = :parent_folder_id, " +
803 DISPLAY_ORDER + " = :display_order, " +
804 FAVORITE_ICON + "= :favorite_icon " +
805 "WHERE " + ID + " = :id");
807 // Bind the query values.
808 updateBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->name);
809 updateBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->url);
810 updateBookmarkQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId);
811 updateBookmarkQuery.bindValue(":display_order", bookmarkStructPointer->displayOrder);
812 updateBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
813 updateBookmarkQuery.bindValue(":id", bookmarkStructPointer->databaseId);
815 // Execute the query.
816 updateBookmarkQuery.exec();
819 void BookmarksDatabase::updateBookmarkName(const int databaseId, const QString &bookmarkName)
821 // Get a handle for the bookmarks database.
822 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
824 // Instantiate an update bookmark name query.
825 QSqlQuery updateBookmarkNameQuery(bookmarksDatabase);
827 // Prepare the update bookmark name query.
828 updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
829 " SET " + BOOKMARK_NAME + " = :bookmark_name " +
830 "WHERE " + ID + " = :id");
832 // Bind the query values.
833 updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName);
834 updateBookmarkNameQuery.bindValue(":id", databaseId);
836 // Execute the query.
837 updateBookmarkNameQuery.exec();
840 void BookmarksDatabase::updateBookmarkUrl(const int databaseId, const QString &bookmarkUrl)
842 // Get a handle for the bookmarks database.
843 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
845 // Instantiate an update bookmark URL query.
846 QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase);
848 // Prepare the update bookmark URL query.
849 updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
850 " SET " + BOOKMARK_URL + " = :bookmark_url " +
851 "WHERE " + ID + " = :id");
853 // Bind the query values.
854 updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl);
855 updateBookmarkUrlQuery.bindValue(":id", databaseId);
857 // Execute the query.
858 updateBookmarkUrlQuery.exec();
861 void BookmarksDatabase::updateDisplayOrder(const int databaseId, const int displayOrder)
863 // Get a handle for the bookmarks database.
864 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
866 // Instantiate an update bookmark display order query.
867 QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
869 // Prepare the update bookmark display order query.
870 updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
871 " SET " + DISPLAY_ORDER + " = :display_order " +
872 "WHERE " + ID + " = :id");
874 // Bind the query values.
875 updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
876 updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
878 // Execute the query.
879 updateBookmarkDisplayOrderQuery.exec();
882 void BookmarksDatabase::updateFolderContentsDisplayOrder(const double folderId)
884 // Get a handle for the bookmarks database.
885 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
887 // Instantiate a folder contents query.
888 QSqlQuery folderContentsQuery(bookmarksDatabase);
890 // Set the query to be forward only, which is more performant.
891 folderContentsQuery.setForwardOnly(true);
893 // Prepare the folder contents query.
894 folderContentsQuery.prepare("SELECT " + ID + ", " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC");
896 // Bind the query values.
897 folderContentsQuery.bindValue(":parent_folder_id", folderId);
899 // Execute the query.
900 folderContentsQuery.exec();
902 // Define a new display order int.
903 int newDisplayOrder = 0;
905 // Populate the subfolder list.
906 while (folderContentsQuery.next())
908 // Update the display order if it has changed.
909 if (folderContentsQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder)
910 updateDisplayOrder(folderContentsQuery.value(ID).toInt(), newDisplayOrder);
912 // Increment the new display order.
917 void BookmarksDatabase::updateParentFolderAndDisplayOrder(const int databaseId, const double parentFolderId, const int displayOrder)
919 // Get a handle for the bookmarks database.
920 QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
922 // Instantiate an update bookmark display order query.
923 QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
925 // Prepare the update bookmark display order query.
926 updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
927 " SET " + PARENT_FOLDER_ID + " = :parent_folder_id " +
928 ", " + DISPLAY_ORDER + " = :display_order " +
929 "WHERE " + ID + " = :id");
931 // Bind the query values.
932 updateBookmarkDisplayOrderQuery.bindValue(":parent_folder_id", parentFolderId);
933 updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
934 updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
936 // Execute the query.
937 updateBookmarkDisplayOrderQuery.exec();