src/databases/BookmarksDatabase.cpp

   1 /*
   2  * Copyright 2023 Soren Stoutner <soren@stoutner.com>.
   3  *
   4  * This file is part of Privacy Browser PC <https://www.stoutner.com/privacy-browser-pc>.
   5  *
   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.
  10  *
  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.
  15  *
  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/>.
  18  */
  19
  20 // Application headers.
  21 #include "BookmarksDatabase.h"
  22
  23 // Define the private static schema constants.
  24 const int BookmarksDatabase::SCHEMA_VERSION = 0;
  25
  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";
  37
  38 // Construct the class.
  39 BookmarksDatabase::BookmarksDatabase() {}
  40
  41 void BookmarksDatabase::addDatabase()
  42 {
  43     // Add the bookmarks database.
  44     QSqlDatabase bookmarksDatabase = QSqlDatabase::addDatabase(QStringLiteral("QSQLITE"), CONNECTION_NAME);
  45
  46     // Set the database name.
  47     bookmarksDatabase.setDatabaseName(QStandardPaths::writableLocation(QStandardPaths::AppDataLocation) + "/bookmarks.db");
  48
  49     // Open the database.
  50     if (bookmarksDatabase.open())  // Opening the database succeeded.
  51     {
  52         // Check to see if the bookmarks table already exists.
  53         if (bookmarksDatabase.tables().contains(BOOKMARKS_TABLE))  // The bookmarks table already exists.
  54         {
  55             // Query the database schema version.
  56             QSqlQuery schemaVersionQuery = bookmarksDatabase.exec(QStringLiteral("PRAGMA user_version"));
  57
  58             // Move to the first record.
  59             schemaVersionQuery.first();
  60
  61             // Get the current schema version.
  62             int currentSchemaVersion = schemaVersionQuery.value(0).toInt();
  63
  64             // Check to see if the schema has been updated.
  65             if (currentSchemaVersion < SCHEMA_VERSION)
  66             {
  67                 // Run the schema update code.
  68
  69                 // Update the schema version.
  70                 bookmarksDatabase.exec("PRAGMA user_version = " + QString::number(SCHEMA_VERSION));
  71             }
  72         }
  73         else  // The bookmarks table does not exist.
  74         {
  75             // Instantiate a create table query.
  76             QSqlQuery createTableQuery(bookmarksDatabase);
  77
  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)");
  88
  89             // Execute the query.
  90             if (!createTableQuery.exec())
  91             {
  92                 // Log any errors.
  93                 qDebug().noquote().nospace() << "Error creating table:  " << bookmarksDatabase.lastError();
  94             }
  95
  96             // Set the schema version.
  97             bookmarksDatabase.exec("PRAGMA user_version = " + QString::number(SCHEMA_VERSION));
  98         }
  99     }
 100     else  // Opening the database failed.
 101     {
 102         // Write the last database error message to the debug output.
 103         qDebug().noquote().nospace() << "Error opening database:  " << bookmarksDatabase.lastError();
 104     }
 105 };
 106
 107 void BookmarksDatabase::addBookmark(const BookmarkStruct *bookmarkStructPointer)
 108 {
 109     // Get a handle for the bookmarks database.
 110     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 111
 112     // Get the folder item count.
 113     int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId);
 114
 115     // Instantiate an add bookmark query.
 116     QSqlQuery addBookmarkQuery(bookmarksDatabase);
 117
 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)"
 126     );
 127
 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));
 134
 135     // Execute the add bookmark query.
 136     addBookmarkQuery.exec();
 137 }
 138
 139 void BookmarksDatabase::addFolder(const BookmarkStruct *bookmarkStructPointer)
 140 {
 141     // Get a handle for the bookmarks database.
 142     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 143
 144     // Get the folder item count.
 145     int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId);
 146
 147     // Instantiate an add folder query.
 148     QSqlQuery addFolderQuery(bookmarksDatabase);
 149
 150     // Prepare the add folder query.
 151     addFolderQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" +
 152                               BOOKMARK_NAME + ", " +
 153                               PARENT_FOLDER_ID + ", " +
 154                               DISPLAY_ORDER + ", " +
 155                               IS_FOLDER + ", " +
 156                               FOLDER_ID + ", " +
 157                               FAVORITE_ICON + ") " +
 158                               "VALUES (:bookmark_name, :parent_folder_id, :display_order, :is_folder, :folder_id, :favorite_icon)"
 159     );
 160
 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));
 168
 169     // Execute the add folder query.
 170     addFolderQuery.exec();
 171 }
 172
 173 void BookmarksDatabase::deleteBookmark(const int databaseId)
 174 {
 175     // Get a handle for the bookmarks database.
 176     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 177
 178     // Instantiate a delete bookmark query.
 179     QSqlQuery deleteBookmarkQuery(bookmarksDatabase);
 180
 181     // Prepare the delete bookmark query.
 182     deleteBookmarkQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
 183
 184     // Bind the query values.
 185     deleteBookmarkQuery.bindValue(":id", databaseId);
 186
 187     // Execute the query.
 188     deleteBookmarkQuery.exec();
 189 }
 190
 191 void BookmarksDatabase::deleteBookmarks(const QString url)
 192 {
 193     // Get a handle for the bookmarks database.
 194     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 195
 196     // Instantiate a parent folder IDs query.
 197     QSqlQuery parentFolderIdsQuery(bookmarksDatabase);
 198
 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");
 201
 202     // Bind the query values.
 203     parentFolderIdsQuery.bindValue(":url", url);
 204
 205     // Execute the query.
 206     parentFolderIdsQuery.exec();
 207
 208     // Instantiate a delete bookmarks query.
 209     QSqlQuery deleteBookmarksQuery(bookmarksDatabase);
 210
 211     // Prepare the delete bookmark query.
 212     deleteBookmarksQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url");
 213
 214     // Bind the query values.
 215     deleteBookmarksQuery.bindValue(":url", url);
 216
 217     // Execute the query.
 218     deleteBookmarksQuery.exec();
 219
 220     // Create a parent folder IDs list.  A standard list can be sorted and deduplicated.
 221     std::list<double> parentFolderIdsList;
 222
 223     // Populate the parent folder IDs list.
 224     while (parentFolderIdsQuery.next())
 225     {
 226         // Add the parent folder ID to the list.
 227         parentFolderIdsList.push_back(parentFolderIdsQuery.value(PARENT_FOLDER_ID).toDouble());
 228     }
 229
 230     // Sort the parent folder IDs list.
 231     parentFolderIdsList.sort();
 232
 233     // Remove duplicate entries from the parent folder IDs list.
 234     parentFolderIdsList.unique();
 235
 236     // Update the display order of each folder where a bookmark was deleted.
 237     for (const double parentFolderId : parentFolderIdsList)
 238         updateFolderContentsDisplayOrder(parentFolderId);
 239 }
 240
 241 double BookmarksDatabase::generateFolderId()
 242 {
 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();
 245
 246     // Get a handle for the bookmarks database.
 247     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 248
 249     // Instantiate a existing folder query.
 250     QSqlQuery existingFolderQuery(bookmarksDatabase);
 251
 252     // Prepare the existing folder query.
 253     existingFolderQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :possible_folder_id");
 254
 255     // Bind the query values.
 256     existingFolderQuery.bindValue(":possible_folder_id", possibleFolderId);
 257
 258     // Execute the query.
 259     existingFolderQuery.exec();
 260
 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();
 264
 265     return possibleFolderId;
 266 }
 267
 268 QList<QString>* BookmarksDatabase::getAllFolderUrls(const double folderId)
 269 {
 270     // Get a handle for the bookmarks database.
 271     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 272
 273     // Instantiate a folder URLs query.
 274     QSqlQuery folderUrlsQuery(bookmarksDatabase);
 275
 276     // Set the query to be forward only, which is more performant.
 277     folderUrlsQuery.setForwardOnly(true);
 278
 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");
 281
 282     // Bind the query values.
 283     folderUrlsQuery.bindValue(":parent_folder_id", folderId);
 284
 285     // Execute the query.
 286     folderUrlsQuery.exec();
 287
 288     // Create a folder URLs list.
 289     QList<QString> *folderUrlsListPointer = new QList<QString>;
 290
 291     // Populate the folder URLs list.
 292     while (folderUrlsQuery.next())
 293     {
 294         // Process the entry according to the type.
 295         if (folderUrlsQuery.value(IS_FOLDER).toBool())  // This is a folder.
 296         {
 297             // Get the subfolder URLs to the list.
 298             folderUrlsListPointer->append(*getAllFolderUrls(folderUrlsQuery.value(FOLDER_ID).toDouble()));
 299         }
 300         else  // This is a bookmark.
 301         {
 302             // Add the URL to the list.
 303             folderUrlsListPointer->append(folderUrlsQuery.value(BOOKMARK_URL).toString());
 304         }
 305     }
 306
 307     // Return the folder URLs list.
 308     return folderUrlsListPointer;
 309 }
 310
 311 BookmarkStruct* BookmarksDatabase::getBookmark(const int databaseId)
 312 {
 313     // Get a handle for the bookmarks database.
 314     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 315
 316     // Instantiate a bookmark query.
 317     QSqlQuery bookmarkQuery(bookmarksDatabase);
 318
 319     // Set the query to be forward only, which is more performant.
 320     bookmarkQuery.setForwardOnly(true);
 321
 322     // Prepare the bookmark query.
 323     bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
 324
 325     // Bind the query values.
 326     bookmarkQuery.bindValue(":id", databaseId);
 327
 328     // Execute the query.
 329     bookmarkQuery.exec();
 330
 331     // Move to the first entry.
 332     bookmarkQuery.first();
 333
 334     // Create a bookmark struct.
 335     struct BookmarkStruct *bookmarkStructPointer = new BookmarkStruct();
 336
 337     // Get the favorite icon base 64 byte array.
 338     QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarkQuery.value(FAVORITE_ICON).toByteArray());
 339
 340     // Create a favorite icon pixmap.
 341     QPixmap favoriteIconPixmap;
 342
 343     // Load the pixmap from byte array.
 344     favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 345
 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);
 355
 356     // Return the bookmark struct pointer.
 357     return bookmarkStructPointer;
 358 }
 359
 360 std::list<BookmarkStruct>* BookmarksDatabase::getBookmarks()
 361 {
 362     // Get a handle for the bookmarks database.
 363     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 364
 365     // Instantiate a bookmarks query.
 366     QSqlQuery bookmarksQuery(bookmarksDatabase);
 367
 368     // Set the query to be forward only, which is more performant.
 369     bookmarksQuery.setForwardOnly(true);
 370
 371     // Prepare the bookmarks query.
 372     bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
 373
 374     // Execute the query.
 375     bookmarksQuery.exec();
 376
 377     // Create a bookmark list.
 378     std::list<BookmarkStruct> *bookmarkListPointer = new std::list<BookmarkStruct>;
 379
 380     // Populate the bookmark list.
 381     while (bookmarksQuery.next())
 382     {
 383         // Create a bookmark struct.
 384         struct BookmarkStruct bookmarkStruct;
 385
 386         // Get the favorite icon base 64 byte array.
 387         QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
 388
 389         // Create a favorite icon pixmap.
 390         QPixmap favoriteIconPixmap;
 391
 392         // Load the pixmap from byte array.
 393         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 394
 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);
 404
 405         // Add the bookmark to the list.
 406         bookmarkListPointer->push_back(bookmarkStruct);
 407     }
 408
 409     // Return the bookmark list.
 410     return bookmarkListPointer;
 411 }
 412
 413 QList<BookmarkStruct>* BookmarksDatabase::getBookmarksInFolderExcept(const double folderId, QList<int> *exceptDatabaseIdsListPointer)
 414 {
 415     // Get a handle for the bookmarks database.
 416     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 417
 418     // Instantiate a bookmarks query.
 419     QSqlQuery bookmarksQuery(bookmarksDatabase);
 420
 421     // Set the query to be forward only, which is more performant.
 422     bookmarksQuery.setForwardOnly(true);
 423
 424     // Create an IDs not to get string.
 425     QString idsNotToGetString;
 426
 427     for (const int databaseId : *exceptDatabaseIdsListPointer)
 428     {
 429         // Check to see if there the string already has at least one number.
 430         if (!idsNotToGetString.isEmpty())
 431         {
 432             // This is not the first number, so add a `,`.
 433             idsNotToGetString.append(QLatin1Char(','));
 434         }
 435
 436         // Append the database ID.
 437         idsNotToGetString.append(QString::number(databaseId));
 438     }
 439
 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");
 442
 443     // Bind the query values.
 444     bookmarksQuery.bindValue(":parent_folder_id", folderId);
 445
 446     // Execute the query.
 447     bookmarksQuery.exec();
 448
 449     // Create a bookmark list.
 450     QList<BookmarkStruct> *bookmarkListPointer = new QList<BookmarkStruct>;
 451
 452     // Populate the bookmark list.
 453     while (bookmarksQuery.next())
 454     {
 455         // Create a bookmark struct.
 456         struct BookmarkStruct bookmarkStruct;
 457
 458         // Get the favorite icon base 64 byte array.
 459         QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
 460
 461         // Create a favorite icon pixmap.
 462         QPixmap favoriteIconPixmap;
 463
 464         // Load the pixmap from byte array.
 465         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 466
 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);
 476
 477         // Add the bookmark to the list.
 478         bookmarkListPointer->push_back(bookmarkStruct);
 479     }
 480
 481     // Return the bookmark list.
 482     return bookmarkListPointer;
 483 }
 484
 485 QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon)
 486 {
 487     // Get a favorite icon pixmap.
 488     QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
 489
 490     // Create a favorite icon byte array.
 491     QByteArray favoriteIconByteArray;
 492
 493     // Create a favorite icon buffer.
 494     QBuffer favoriteIconBuffer(&favoriteIconByteArray);
 495
 496     // Open the buffer.
 497     favoriteIconBuffer.open(QIODevice::WriteOnly);
 498
 499     // Convert the favorite icon pixmap into a byte array in PNG format.
 500     favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
 501
 502     // Close the buffer.
 503     favoriteIconBuffer.close();
 504
 505     // Convert the favorite icon byte array to a base 64 string.
 506     QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
 507
 508     // Return the favorite icon base 64 string.
 509     return favoriteIconBase64String;
 510 }
 511
 512 QList<BookmarkStruct>* BookmarksDatabase::getFolderContents(const double folderId)
 513 {
 514     // Get a handle for the bookmarks database.
 515     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 516
 517     // Instantiate a folder contents query.
 518     QSqlQuery folderContentsQuery(bookmarksDatabase);
 519
 520     // Set the query to be forward only, which is more performant.
 521     folderContentsQuery.setForwardOnly(true);
 522
 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");
 525
 526     // Bind the query values.
 527     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 528
 529     // Execute the query.
 530     folderContentsQuery.exec();
 531
 532     // Create a folder contents list.
 533     QList<BookmarkStruct> *folderContentsListPointer = new QList<BookmarkStruct>;
 534
 535     // Populate the folder contents list.
 536     while (folderContentsQuery.next())
 537     {
 538         // Create a bookmark struct.
 539         struct BookmarkStruct bookmarkStruct;
 540
 541         // Get the favorite icon base 64 byte array.
 542         QByteArray favoriteIconByteArray = QByteArray::fromBase64(folderContentsQuery.value(FAVORITE_ICON).toByteArray());
 543
 544         // Create a favorite icon pixmap.
 545         QPixmap favoriteIconPixmap;
 546
 547         // Load the pixmap from byte array.
 548         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 549
 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);
 559
 560         // Add the item to the list.
 561         folderContentsListPointer->append(bookmarkStruct);
 562     }
 563
 564     // Return the folder contents list.
 565     return folderContentsListPointer;
 566 }
 567
 568 QList<int>* BookmarksDatabase::getFolderContentsDatabaseIds(const double folderId)
 569 {
 570     // Get a handle for the bookmarks database.
 571     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 572
 573     // Instantiate a folder contents query.
 574     QSqlQuery folderContentsQuery(bookmarksDatabase);
 575
 576     // Set the query to be forward only, which is more performant.
 577     folderContentsQuery.setForwardOnly(true);
 578
 579     // Prepare the folder contents query.
 580     folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
 581
 582     // Bind the query values.
 583     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 584
 585     // Execute the query.
 586     folderContentsQuery.exec();
 587
 588     // Create a folder contents database ID list.
 589     QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
 590
 591     // Populate the folder contents list.
 592     while (folderContentsQuery.next())
 593     {
 594         // Add the database ID to the list.
 595         folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
 596     }
 597
 598     // Return the folder contents database ID list.
 599     return folderContentsDatabaseIdsListPointer;
 600 }
 601
 602 QList<int> *BookmarksDatabase::getFolderContentsDatabaseIdsRecursively(const double folderId)
 603 {
 604     // Get a handle for the bookmarks database.
 605     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 606
 607     // Instantiate a folder contents query.
 608     QSqlQuery folderContentsQuery(bookmarksDatabase);
 609
 610     // Set the query to be forward only, which is more performant.
 611     folderContentsQuery.setForwardOnly(true);
 612
 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");
 615
 616     // Bind the query values.
 617     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 618
 619     // Execute the query.
 620     folderContentsQuery.exec();
 621
 622     // Create a folder contents database ID list.
 623     QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
 624
 625     // Populate the folder contents list.
 626     while (folderContentsQuery.next())
 627     {
 628         // Add the database ID to the list.
 629         folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
 630
 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()));
 634     }
 635
 636     // Return the folder contents database ID list.
 637     return folderContentsDatabaseIdsListPointer;
 638 }
 639
 640 int BookmarksDatabase::getFolderDatabaseId(const double folderId)
 641 {
 642     // Get a handle for the bookmarks database.
 643     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 644
 645     // Instantiate a folder database ID query.
 646     QSqlQuery folderDatabaseIdQuery(bookmarksDatabase);
 647
 648     // Set the query to be forward only, which is more performant.
 649     folderDatabaseIdQuery.setForwardOnly(true);
 650
 651     // Prepare the folder database ID query.
 652     folderDatabaseIdQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :folder_id");
 653
 654     // Bind the query values.
 655     folderDatabaseIdQuery.bindValue(":folder_id", folderId);
 656
 657     // Execute the query.
 658     folderDatabaseIdQuery.exec();
 659
 660     // Move to the first entry.
 661     folderDatabaseIdQuery.first();
 662
 663     // Return the folder database ID.
 664     return folderDatabaseIdQuery.value(ID).toInt();
 665 }
 666
 667 double BookmarksDatabase::getFolderId(const int databaseId)
 668 {
 669     // Get a handle for the bookmarks database.
 670     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 671
 672     // Instantiate a folder ID query.
 673     QSqlQuery folderIdQuery(bookmarksDatabase);
 674
 675     // Set the query to be forward only, which is more performant.
 676     folderIdQuery.setForwardOnly(true);
 677
 678     // Prepare the folder ID query.
 679     folderIdQuery.prepare("SELECT " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
 680
 681     // Bind the query values.
 682     folderIdQuery.bindValue(":database_id", databaseId);
 683
 684     // Execute the query.
 685     folderIdQuery.exec();
 686
 687     // Move to the first entry.
 688     folderIdQuery.first();
 689
 690     // Return the folder ID.
 691     return folderIdQuery.value(FOLDER_ID).toDouble();
 692 }
 693
 694 int BookmarksDatabase::getFolderItemCount(const double folderId)
 695 {
 696     // Get a handle for the bookmarks database.
 697     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 698
 699     // Instantiate a folder contents query.
 700     QSqlQuery folderContentsQuery(bookmarksDatabase);
 701
 702     // Set the query to be forward only, which is more performant.
 703     folderContentsQuery.setForwardOnly(true);
 704
 705     // Prepare the folder contents query.
 706     folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
 707
 708     // Bind the query values.
 709     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 710
 711     // Execute the query.
 712     folderContentsQuery.exec();
 713
 714     // Move to the last row.
 715     folderContentsQuery.last();
 716
 717     // Initialize an item count variable.
 718     int itemCount = 0;
 719
 720     // Check to see if the query is valid (there is at least one item).
 721     if (folderContentsQuery.isValid())
 722     {
 723         // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks.
 724         itemCount = folderContentsQuery.at() + 1;
 725     }
 726
 727     // Return the item count.
 728     return itemCount;
 729 }
 730
 731 double BookmarksDatabase::getParentFolderId(const int databaseId)
 732 {
 733     // Get a handle for the bookmarks database.
 734     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 735
 736     // Instantiate a parent folder ID query.
 737     QSqlQuery parentFolderIdQuery(bookmarksDatabase);
 738
 739     // Set the query to be forward only, which is more performant.
 740     parentFolderIdQuery.setForwardOnly(true);
 741
 742     // Prepare the parent folder ID query.
 743     parentFolderIdQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
 744
 745     // Bind the query values.
 746     parentFolderIdQuery.bindValue(":database_id", databaseId);
 747
 748     // Execute the query.
 749     parentFolderIdQuery.exec();
 750
 751     // Move to the first entry.
 752     parentFolderIdQuery.first();
 753
 754     // Return the parent folder ID.
 755     return parentFolderIdQuery.value(PARENT_FOLDER_ID).toDouble();
 756 }
 757
 758 QList<BookmarkStruct>* BookmarksDatabase::getSubfolders(const double folderId)
 759 {
 760     // Get a handle for the bookmarks database.
 761     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 762
 763     // Instantiate a subfolders query.
 764     QSqlQuery subfoldersQuery(bookmarksDatabase);
 765
 766     // Set the query to be forward only, which is more performant.
 767     subfoldersQuery.setForwardOnly(true);
 768
 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");
 771
 772     // Bind the query values.
 773     subfoldersQuery.bindValue(":parent_folder_id", folderId);
 774
 775     // Execute the query.
 776     subfoldersQuery.exec();
 777
 778     // Create a subfolder list.
 779     QList<BookmarkStruct> *subfoldersListPointer = new QList<BookmarkStruct>;
 780
 781     // Populate the subfolder list.
 782     while (subfoldersQuery.next())
 783     {
 784         // Create a bookmark struct.
 785         struct BookmarkStruct bookmarkStruct;
 786
 787         // Get the favorite icon base 64 byte array.
 788         QByteArray favoriteIconByteArray = QByteArray::fromBase64(subfoldersQuery.value(FAVORITE_ICON).toByteArray());
 789
 790         // Create a favorite icon pixmap.
 791         QPixmap favoriteIconPixmap;
 792
 793         // Load the pixmap from byte array.
 794         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 795
 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);
 804
 805         // Add the subfolder to the list.
 806         subfoldersListPointer->append(bookmarkStruct);
 807     }
 808
 809     // Return the subfolders list.
 810     return subfoldersListPointer;
 811 }
 812
 813 bool BookmarksDatabase::isBookmarked(const QString url)
 814 {
 815     // Get a handle for the bookmarks database.
 816     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 817
 818     // Instantiate an is bookmarked query.
 819     QSqlQuery isBookmarkedQuery(bookmarksDatabase);
 820
 821     // Set the query to be forward only, which is more performant.
 822     isBookmarkedQuery.setForwardOnly(true);
 823
 824     // Prepare the is bookmarked query.
 825     isBookmarkedQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url");
 826
 827     // Bind the query values.
 828     isBookmarkedQuery.bindValue(":url", url);
 829
 830     // Execute the query.
 831     isBookmarkedQuery.exec();
 832
 833     // Move to the first entry.
 834     isBookmarkedQuery.first();
 835
 836     // Return true if the query is valid (there is at least one item).
 837     return isBookmarkedQuery.isValid();
 838 }
 839
 840 bool BookmarksDatabase::isFolder(const int databaseId)
 841 {
 842     // Get a handle for the bookmarks database.
 843     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 844
 845     // Instantiate an is folder query.
 846     QSqlQuery isFolderQuery(bookmarksDatabase);
 847
 848     // Set the query to be forward only, which is more performant.
 849     isFolderQuery.setForwardOnly(true);
 850
 851     // Prepare the is folder query.
 852     isFolderQuery.prepare("SELECT " + IS_FOLDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
 853
 854     // Bind the query values.
 855     isFolderQuery.bindValue(":id", databaseId);
 856
 857     // Execute the query.
 858     isFolderQuery.exec();
 859
 860     // Move to the first entry.
 861     isFolderQuery.first();
 862
 863     // Return the folder status.
 864     return isFolderQuery.value(IS_FOLDER).toBool();
 865 }
 866
 867 void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer)
 868 {
 869     // Get a handle for the bookmarks database.
 870     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 871
 872     // Instantiate an update bookmark query.
 873     QSqlQuery updateBookmarkQuery(bookmarksDatabase);
 874
 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");
 883
 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);
 891
 892     // Execute the query.
 893     updateBookmarkQuery.exec();
 894 }
 895
 896 void BookmarksDatabase::updateBookmarkName(const int databaseId, const QString &bookmarkName)
 897 {
 898     // Get a handle for the bookmarks database.
 899     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 900
 901     // Instantiate an update bookmark name query.
 902     QSqlQuery updateBookmarkNameQuery(bookmarksDatabase);
 903
 904     // Prepare the update bookmark name query.
 905     updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
 906                                     " SET " + BOOKMARK_NAME + " = :bookmark_name " +
 907                                     "WHERE " + ID + " = :id");
 908
 909     // Bind the query values.
 910     updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName);
 911     updateBookmarkNameQuery.bindValue(":id", databaseId);
 912
 913     // Execute the query.
 914     updateBookmarkNameQuery.exec();
 915 }
 916
 917 void BookmarksDatabase::updateBookmarkUrl(const int databaseId, const QString &bookmarkUrl)
 918 {
 919     // Get a handle for the bookmarks database.
 920     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 921
 922     // Instantiate an update bookmark URL query.
 923     QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase);
 924
 925     // Prepare the update bookmark URL query.
 926     updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
 927                                    " SET " + BOOKMARK_URL + " = :bookmark_url " +
 928                                    "WHERE " + ID + " = :id");
 929
 930     // Bind the query values.
 931     updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl);
 932     updateBookmarkUrlQuery.bindValue(":id", databaseId);
 933
 934     // Execute the query.
 935     updateBookmarkUrlQuery.exec();
 936 }
 937
 938 void BookmarksDatabase::updateDisplayOrder(const int databaseId, const int displayOrder)
 939 {
 940     // Get a handle for the bookmarks database.
 941     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 942
 943     // Instantiate an update bookmark display order query.
 944     QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
 945
 946     // Prepare the update bookmark display order query.
 947     updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
 948                                             " SET " + DISPLAY_ORDER + " = :display_order " +
 949                                             "WHERE " + ID + " = :id");
 950
 951     // Bind the query values.
 952     updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
 953     updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
 954
 955     // Execute the query.
 956     updateBookmarkDisplayOrderQuery.exec();
 957 }
 958
 959 void BookmarksDatabase::updateFolderContentsDisplayOrder(const double folderId)
 960 {
 961     // Get a handle for the bookmarks database.
 962     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 963
 964     // Instantiate a folder contents query.
 965     QSqlQuery folderContentsQuery(bookmarksDatabase);
 966
 967     // Set the query to be forward only, which is more performant.
 968     folderContentsQuery.setForwardOnly(true);
 969
 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");
 972
 973     // Bind the query values.
 974     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 975
 976     // Execute the query.
 977     folderContentsQuery.exec();
 978
 979     // Define a new display order int.
 980     int newDisplayOrder = 0;
 981
 982     // Populate the subfolder list.
 983     while (folderContentsQuery.next())
 984     {
 985         // Update the display order if it has changed.
 986         if (folderContentsQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder)
 987             updateDisplayOrder(folderContentsQuery.value(ID).toInt(), newDisplayOrder);
 988
 989         // Increment the new display order.
 990         ++newDisplayOrder;
 991     }
 992 }
 993
 994 void BookmarksDatabase::updateParentFolderAndDisplayOrder(const int databaseId, const double parentFolderId, const int displayOrder)
 995 {
 996     // Get a handle for the bookmarks database.
 997     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 998
 999     // Instantiate an update bookmark display order query.
1000     QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
1001
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");
1007
1008     // Bind the query values.
1009     updateBookmarkDisplayOrderQuery.bindValue(":parent_folder_id", parentFolderId);
1010     updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
1011     updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
1012
1013     // Execute the query.
1014     updateBookmarkDisplayOrderQuery.exec();
1015 }