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 double BookmarksDatabase::generateFolderId()
 192 {
 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();
 195
 196     // Get a handle for the bookmarks database.
 197     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 198
 199     // Instantiate a existing folder query.
 200     QSqlQuery existingFolderQuery(bookmarksDatabase);
 201
 202     // Prepare the existing folder query.
 203     existingFolderQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :possible_folder_id");
 204
 205     // Bind the query values.
 206     existingFolderQuery.bindValue(":possible_folder_id", possibleFolderId);
 207
 208     // Execute the query.
 209     existingFolderQuery.exec();
 210
 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();
 214
 215     return possibleFolderId;
 216 }
 217
 218 QList<QString>* BookmarksDatabase::getAllFolderUrls(const double folderId)
 219 {
 220     // Get a handle for the bookmarks database.
 221     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 222
 223     // Instantiate a folder URLs query.
 224     QSqlQuery folderUrlsQuery(bookmarksDatabase);
 225
 226     // Set the query to be forward only, which is more performant.
 227     folderUrlsQuery.setForwardOnly(true);
 228
 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");
 231
 232     // Bind the query values.
 233     folderUrlsQuery.bindValue(":parent_folder_id", folderId);
 234
 235     // Execute the query.
 236     folderUrlsQuery.exec();
 237
 238     // Create a folder URLs list.
 239     QList<QString> *folderUrlsListPointer = new QList<QString>;
 240
 241     // Populate the folder URLs list.
 242     while (folderUrlsQuery.next())
 243     {
 244         // Process the entry according to the type.
 245         if (folderUrlsQuery.value(IS_FOLDER).toBool())  // This is a folder.
 246         {
 247             // Get the subfolder URLs to the list.
 248             folderUrlsListPointer->append(*getAllFolderUrls(folderUrlsQuery.value(FOLDER_ID).toDouble()));
 249         }
 250         else  // This is a bookmark.
 251         {
 252             // Add the URL to the list.
 253             folderUrlsListPointer->append(folderUrlsQuery.value(BOOKMARK_URL).toString());
 254         }
 255     }
 256
 257     // Return the folder URLs list.
 258     return folderUrlsListPointer;
 259 }
 260
 261 BookmarkStruct* BookmarksDatabase::getBookmark(const int databaseId)
 262 {
 263     // Get a handle for the bookmarks database.
 264     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 265
 266     // Instantiate a bookmark query.
 267     QSqlQuery bookmarkQuery(bookmarksDatabase);
 268
 269     // Set the query to be forward only, which is more performant.
 270     bookmarkQuery.setForwardOnly(true);
 271
 272     // Prepare the bookmark query.
 273     bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
 274
 275     // Bind the query values.
 276     bookmarkQuery.bindValue(":id", databaseId);
 277
 278     // Execute the query.
 279     bookmarkQuery.exec();
 280
 281     // Move to the first entry.
 282     bookmarkQuery.first();
 283
 284     // Create a bookmark struct.
 285     struct BookmarkStruct *bookmarkStructPointer = new BookmarkStruct();
 286
 287     // Get the favorite icon base 64 byte array.
 288     QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarkQuery.value(FAVORITE_ICON).toByteArray());
 289
 290     // Create a favorite icon pixmap.
 291     QPixmap favoriteIconPixmap;
 292
 293     // Load the pixmap from byte array.
 294     favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 295
 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);
 305
 306     // Return the bookmark struct pointer.
 307     return bookmarkStructPointer;
 308 }
 309
 310 std::list<BookmarkStruct>* BookmarksDatabase::getBookmarks()
 311 {
 312     // Get a handle for the bookmarks database.
 313     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 314
 315     // Instantiate a bookmarks query.
 316     QSqlQuery bookmarksQuery(bookmarksDatabase);
 317
 318     // Set the query to be forward only, which is more performant.
 319     bookmarksQuery.setForwardOnly(true);
 320
 321     // Prepare the bookmarks query.
 322     bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
 323
 324     // Execute the query.
 325     bookmarksQuery.exec();
 326
 327     // Create a bookmark list.
 328     std::list<BookmarkStruct> *bookmarkListPointer = new std::list<BookmarkStruct>;
 329
 330     // Populate the bookmark list.
 331     while (bookmarksQuery.next())
 332     {
 333         // Create a bookmark struct.
 334         struct BookmarkStruct bookmarkStruct;
 335
 336         // Get the favorite icon base 64 byte array.
 337         QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
 338
 339         // Create a favorite icon pixmap.
 340         QPixmap favoriteIconPixmap;
 341
 342         // Load the pixmap from byte array.
 343         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 344
 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);
 354
 355         // Add the bookmark to the list.
 356         bookmarkListPointer->push_back(bookmarkStruct);
 357     }
 358
 359     // Return the bookmark list.
 360     return bookmarkListPointer;
 361 }
 362
 363 QList<BookmarkStruct>* BookmarksDatabase::getBookmarksInFolderExcept(const double folderId, QList<int> *exceptDatabaseIdsListPointer)
 364 {
 365     // Get a handle for the bookmarks database.
 366     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 367
 368     // Instantiate a bookmarks query.
 369     QSqlQuery bookmarksQuery(bookmarksDatabase);
 370
 371     // Set the query to be forward only, which is more performant.
 372     bookmarksQuery.setForwardOnly(true);
 373
 374     // Create an IDs not to get string.
 375     QString idsNotToGetString;
 376
 377     for (const int databaseId : *exceptDatabaseIdsListPointer)
 378     {
 379         // Check to see if there the string already has at least one number.
 380         if (!idsNotToGetString.isEmpty())
 381         {
 382             // This is not the first number, so add a `,`.
 383             idsNotToGetString.append(QLatin1Char(','));
 384         }
 385
 386         // Append the database ID.
 387         idsNotToGetString.append(QString::number(databaseId));
 388     }
 389
 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");
 392
 393     // Bind the query values.
 394     bookmarksQuery.bindValue(":parent_folder_id", folderId);
 395
 396     // Execute the query.
 397     bookmarksQuery.exec();
 398
 399     // Create a bookmark list.
 400     QList<BookmarkStruct> *bookmarkListPointer = new QList<BookmarkStruct>;
 401
 402     // Populate the bookmark list.
 403     while (bookmarksQuery.next())
 404     {
 405         // Create a bookmark struct.
 406         struct BookmarkStruct bookmarkStruct;
 407
 408         // Get the favorite icon base 64 byte array.
 409         QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
 410
 411         // Create a favorite icon pixmap.
 412         QPixmap favoriteIconPixmap;
 413
 414         // Load the pixmap from byte array.
 415         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 416
 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);
 426
 427         // Add the bookmark to the list.
 428         bookmarkListPointer->push_back(bookmarkStruct);
 429     }
 430
 431     // Return the bookmark list.
 432     return bookmarkListPointer;
 433 }
 434
 435 QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon)
 436 {
 437     // Get a favorite icon pixmap.
 438     QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
 439
 440     // Create a favorite icon byte array.
 441     QByteArray favoriteIconByteArray;
 442
 443     // Create a favorite icon buffer.
 444     QBuffer favoriteIconBuffer(&favoriteIconByteArray);
 445
 446     // Open the buffer.
 447     favoriteIconBuffer.open(QIODevice::WriteOnly);
 448
 449     // Convert the favorite icon pixmap into a byte array in PNG format.
 450     favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
 451
 452     // Close the buffer.
 453     favoriteIconBuffer.close();
 454
 455     // Convert the favorite icon byte array to a base 64 string.
 456     QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
 457
 458     // Return the favorite icon base 64 string.
 459     return favoriteIconBase64String;
 460 }
 461
 462 QList<BookmarkStruct>* BookmarksDatabase::getFolderContents(const double folderId)
 463 {
 464     // Get a handle for the bookmarks database.
 465     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 466
 467     // Instantiate a folder contents query.
 468     QSqlQuery folderContentsQuery(bookmarksDatabase);
 469
 470     // Set the query to be forward only, which is more performant.
 471     folderContentsQuery.setForwardOnly(true);
 472
 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");
 475
 476     // Bind the query values.
 477     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 478
 479     // Execute the query.
 480     folderContentsQuery.exec();
 481
 482     // Create a folder contents list.
 483     QList<BookmarkStruct> *folderContentsListPointer = new QList<BookmarkStruct>;
 484
 485     // Populate the folder contents list.
 486     while (folderContentsQuery.next())
 487     {
 488         // Create a bookmark struct.
 489         struct BookmarkStruct bookmarkStruct;
 490
 491         // Get the favorite icon base 64 byte array.
 492         QByteArray favoriteIconByteArray = QByteArray::fromBase64(folderContentsQuery.value(FAVORITE_ICON).toByteArray());
 493
 494         // Create a favorite icon pixmap.
 495         QPixmap favoriteIconPixmap;
 496
 497         // Load the pixmap from byte array.
 498         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 499
 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);
 509
 510         // Add the item to the list.
 511         folderContentsListPointer->append(bookmarkStruct);
 512     }
 513
 514     // Return the folder contents list.
 515     return folderContentsListPointer;
 516 }
 517
 518 QList<int>* BookmarksDatabase::getFolderContentsDatabaseIds(const double folderId)
 519 {
 520     // Get a handle for the bookmarks database.
 521     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 522
 523     // Instantiate a folder contents query.
 524     QSqlQuery folderContentsQuery(bookmarksDatabase);
 525
 526     // Set the query to be forward only, which is more performant.
 527     folderContentsQuery.setForwardOnly(true);
 528
 529     // Prepare the folder contents query.
 530     folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
 531
 532     // Bind the query values.
 533     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 534
 535     // Execute the query.
 536     folderContentsQuery.exec();
 537
 538     // Create a folder contents database ID list.
 539     QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
 540
 541     // Populate the folder contents list.
 542     while (folderContentsQuery.next())
 543     {
 544         // Add the database ID to the list.
 545         folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
 546     }
 547
 548     // Return the folder contents database ID list.
 549     return folderContentsDatabaseIdsListPointer;
 550 }
 551
 552 QList<int> *BookmarksDatabase::getFolderContentsDatabaseIdsRecursively(const double folderId)
 553 {
 554     // Get a handle for the bookmarks database.
 555     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 556
 557     // Instantiate a folder contents query.
 558     QSqlQuery folderContentsQuery(bookmarksDatabase);
 559
 560     // Set the query to be forward only, which is more performant.
 561     folderContentsQuery.setForwardOnly(true);
 562
 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");
 565
 566     // Bind the query values.
 567     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 568
 569     // Execute the query.
 570     folderContentsQuery.exec();
 571
 572     // Create a folder contents database ID list.
 573     QList<int> *folderContentsDatabaseIdsListPointer = new QList<int>;
 574
 575     // Populate the folder contents list.
 576     while (folderContentsQuery.next())
 577     {
 578         // Add the database ID to the list.
 579         folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt());
 580
 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()));
 584     }
 585
 586     // Return the folder contents database ID list.
 587     return folderContentsDatabaseIdsListPointer;
 588 }
 589
 590 int BookmarksDatabase::getFolderDatabaseId(const double folderId)
 591 {
 592     // Get a handle for the bookmarks database.
 593     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 594
 595     // Instantiate a folder database ID query.
 596     QSqlQuery folderDatabaseIdQuery(bookmarksDatabase);
 597
 598     // Set the query to be forward only, which is more performant.
 599     folderDatabaseIdQuery.setForwardOnly(true);
 600
 601     // Prepare the folder database ID query.
 602     folderDatabaseIdQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :folder_id");
 603
 604     // Bind the query values.
 605     folderDatabaseIdQuery.bindValue(":folder_id", folderId);
 606
 607     // Execute the query.
 608     folderDatabaseIdQuery.exec();
 609
 610     // Move to the first entry.
 611     folderDatabaseIdQuery.first();
 612
 613     // Return the folder database ID.
 614     return folderDatabaseIdQuery.value(ID).toInt();
 615 }
 616
 617 double BookmarksDatabase::getFolderId(const int databaseId)
 618 {
 619     // Get a handle for the bookmarks database.
 620     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 621
 622     // Instantiate a folder ID query.
 623     QSqlQuery folderIdQuery(bookmarksDatabase);
 624
 625     // Set the query to be forward only, which is more performant.
 626     folderIdQuery.setForwardOnly(true);
 627
 628     // Prepare the folder ID query.
 629     folderIdQuery.prepare("SELECT " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
 630
 631     // Bind the query values.
 632     folderIdQuery.bindValue(":database_id", databaseId);
 633
 634     // Execute the query.
 635     folderIdQuery.exec();
 636
 637     // Move to the first entry.
 638     folderIdQuery.first();
 639
 640     // Return the folder ID.
 641     return folderIdQuery.value(FOLDER_ID).toDouble();
 642 }
 643
 644 int BookmarksDatabase::getFolderItemCount(const double folderId)
 645 {
 646     // Get a handle for the bookmarks database.
 647     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 648
 649     // Instantiate a folder contents query.
 650     QSqlQuery folderContentsQuery(bookmarksDatabase);
 651
 652     // Set the query to be forward only, which is more performant.
 653     folderContentsQuery.setForwardOnly(true);
 654
 655     // Prepare the folder contents query.
 656     folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id");
 657
 658     // Bind the query values.
 659     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 660
 661     // Execute the query.
 662     folderContentsQuery.exec();
 663
 664     // Move to the last row.
 665     folderContentsQuery.last();
 666
 667     // Initialize an item count variable.
 668     int itemCount = 0;
 669
 670     // Check to see if the query is valid (there is at least one item).
 671     if (folderContentsQuery.isValid())
 672     {
 673         // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks.
 674         itemCount = folderContentsQuery.at() + 1;
 675     }
 676
 677     // Return the item count.
 678     return itemCount;
 679 }
 680
 681 double BookmarksDatabase::getParentFolderId(const int databaseId)
 682 {
 683     // Get a handle for the bookmarks database.
 684     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 685
 686     // Instantiate a parent folder ID query.
 687     QSqlQuery parentFolderIdQuery(bookmarksDatabase);
 688
 689     // Set the query to be forward only, which is more performant.
 690     parentFolderIdQuery.setForwardOnly(true);
 691
 692     // Prepare the parent folder ID query.
 693     parentFolderIdQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id");
 694
 695     // Bind the query values.
 696     parentFolderIdQuery.bindValue(":database_id", databaseId);
 697
 698     // Execute the query.
 699     parentFolderIdQuery.exec();
 700
 701     // Move to the first entry.
 702     parentFolderIdQuery.first();
 703
 704     // Return the parent folder ID.
 705     return parentFolderIdQuery.value(PARENT_FOLDER_ID).toDouble();
 706 }
 707
 708 QList<BookmarkStruct>* BookmarksDatabase::getSubfolders(const double folderId)
 709 {
 710     // Get a handle for the bookmarks database.
 711     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 712
 713     // Instantiate a subfolders query.
 714     QSqlQuery subfoldersQuery(bookmarksDatabase);
 715
 716     // Set the query to be forward only, which is more performant.
 717     subfoldersQuery.setForwardOnly(true);
 718
 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");
 721
 722     // Bind the query values.
 723     subfoldersQuery.bindValue(":parent_folder_id", folderId);
 724
 725     // Execute the query.
 726     subfoldersQuery.exec();
 727
 728     // Create a subfolder list.
 729     QList<BookmarkStruct> *subfoldersListPointer = new QList<BookmarkStruct>;
 730
 731     // Populate the subfolder list.
 732     while (subfoldersQuery.next())
 733     {
 734         // Create a bookmark struct.
 735         struct BookmarkStruct bookmarkStruct;
 736
 737         // Get the favorite icon base 64 byte array.
 738         QByteArray favoriteIconByteArray = QByteArray::fromBase64(subfoldersQuery.value(FAVORITE_ICON).toByteArray());
 739
 740         // Create a favorite icon pixmap.
 741         QPixmap favoriteIconPixmap;
 742
 743         // Load the pixmap from byte array.
 744         favoriteIconPixmap.loadFromData(favoriteIconByteArray);
 745
 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);
 754
 755         // Add the subfolder to the list.
 756         subfoldersListPointer->append(bookmarkStruct);
 757     }
 758
 759     // Return the subfolders list.
 760     return subfoldersListPointer;
 761 }
 762
 763 bool BookmarksDatabase::isFolder(const int databaseId)
 764 {
 765     // Get a handle for the bookmarks database.
 766     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 767
 768     // Instantiate an is folder query.
 769     QSqlQuery isFolderQuery(bookmarksDatabase);
 770
 771     // Set the query to be forward only, which is more performant.
 772     isFolderQuery.setForwardOnly(true);
 773
 774     // Prepare the is folder query.
 775     isFolderQuery.prepare("SELECT " + IS_FOLDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
 776
 777     // Bind the query values.
 778     isFolderQuery.bindValue(":id", databaseId);
 779
 780     // Execute the query.
 781     isFolderQuery.exec();
 782
 783     // Move to the first entry.
 784     isFolderQuery.first();
 785
 786     // Return the folder status.
 787     return isFolderQuery.value(IS_FOLDER).toBool();
 788 }
 789
 790 void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer)
 791 {
 792     // Get a handle for the bookmarks database.
 793     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 794
 795     // Instantiate an update bookmark query.
 796     QSqlQuery updateBookmarkQuery(bookmarksDatabase);
 797
 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");
 806
 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);
 814
 815     // Execute the query.
 816     updateBookmarkQuery.exec();
 817 }
 818
 819 void BookmarksDatabase::updateBookmarkName(const int databaseId, const QString &bookmarkName)
 820 {
 821     // Get a handle for the bookmarks database.
 822     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 823
 824     // Instantiate an update bookmark name query.
 825     QSqlQuery updateBookmarkNameQuery(bookmarksDatabase);
 826
 827     // Prepare the update bookmark name query.
 828     updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
 829                                     " SET " + BOOKMARK_NAME + " = :bookmark_name " +
 830                                     "WHERE " + ID + " = :id");
 831
 832     // Bind the query values.
 833     updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName);
 834     updateBookmarkNameQuery.bindValue(":id", databaseId);
 835
 836     // Execute the query.
 837     updateBookmarkNameQuery.exec();
 838 }
 839
 840 void BookmarksDatabase::updateBookmarkUrl(const int databaseId, const QString &bookmarkUrl)
 841 {
 842     // Get a handle for the bookmarks database.
 843     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 844
 845     // Instantiate an update bookmark URL query.
 846     QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase);
 847
 848     // Prepare the update bookmark URL query.
 849     updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
 850                                    " SET " + BOOKMARK_URL + " = :bookmark_url " +
 851                                    "WHERE " + ID + " = :id");
 852
 853     // Bind the query values.
 854     updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl);
 855     updateBookmarkUrlQuery.bindValue(":id", databaseId);
 856
 857     // Execute the query.
 858     updateBookmarkUrlQuery.exec();
 859 }
 860
 861 void BookmarksDatabase::updateDisplayOrder(const int databaseId, const int displayOrder)
 862 {
 863     // Get a handle for the bookmarks database.
 864     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 865
 866     // Instantiate an update bookmark display order query.
 867     QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
 868
 869     // Prepare the update bookmark display order query.
 870     updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
 871                                             " SET " + DISPLAY_ORDER + " = :display_order " +
 872                                             "WHERE " + ID + " = :id");
 873
 874     // Bind the query values.
 875     updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
 876     updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
 877
 878     // Execute the query.
 879     updateBookmarkDisplayOrderQuery.exec();
 880 }
 881
 882 void BookmarksDatabase::updateFolderContentsDisplayOrder(const double folderId)
 883 {
 884     // Get a handle for the bookmarks database.
 885     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 886
 887     // Instantiate a folder contents query.
 888     QSqlQuery folderContentsQuery(bookmarksDatabase);
 889
 890     // Set the query to be forward only, which is more performant.
 891     folderContentsQuery.setForwardOnly(true);
 892
 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");
 895
 896     // Bind the query values.
 897     folderContentsQuery.bindValue(":parent_folder_id", folderId);
 898
 899     // Execute the query.
 900     folderContentsQuery.exec();
 901
 902     // Define a new display order int.
 903     int newDisplayOrder = 0;
 904
 905     // Populate the subfolder list.
 906     while (folderContentsQuery.next())
 907     {
 908         // Update the display order if it has changed.
 909         if (folderContentsQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder)
 910             updateDisplayOrder(folderContentsQuery.value(ID).toInt(), newDisplayOrder);
 911
 912         // Increment the new display order.
 913         ++newDisplayOrder;
 914     }
 915 }
 916
 917 void BookmarksDatabase::updateParentFolderAndDisplayOrder(const int databaseId, const double parentFolderId, const int displayOrder)
 918 {
 919     // Get a handle for the bookmarks database.
 920     QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
 921
 922     // Instantiate an update bookmark display order query.
 923     QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
 924
 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");
 930
 931     // Bind the query values.
 932     updateBookmarkDisplayOrderQuery.bindValue(":parent_folder_id", parentFolderId);
 933     updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
 934     updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId);
 935
 936     // Execute the query.
 937     updateBookmarkDisplayOrderQuery.exec();
 938 }