}
};
-void BookmarksDatabase::addBookmark(const QString &bookmarkName, const QString &bookmarkUrl, const QIcon &favoriteIcon)
+void BookmarksDatabase::addBookmark(const BookmarkStruct *bookmarkStructPointer)
{
// Get a handle for the bookmarks database.
QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
- // Get a favorite icon pixmap.
- QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
+ // Instantiate a count bookmarks query. TODO: This needs to be updated to only count the bookmarks in the current folder.
+ QSqlQuery countBookmarksQuery(bookmarksDatabase);
- // Create a favorite icon byte array.
- QByteArray favoriteIconByteArray;
+ // Set the query to be forward only, which is more performant.
+ countBookmarksQuery.setForwardOnly(true);
- // Create a favorite icon buffer.
- QBuffer favoriteIconBuffer(&favoriteIconByteArray);
+ // Prepare the count bookmarks query.
+ countBookmarksQuery.prepare("SELECT " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE);
- // Open the buffer.
- favoriteIconBuffer.open(QIODevice::WriteOnly);
+ // Execute the count bookmarks query.
+ countBookmarksQuery.exec();
- // Convert the favorite icon pixmap into a byte array in PNG format.
- favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
+ // Move to the last row.
+ countBookmarksQuery.last();
- // Close the buffer.
- favoriteIconBuffer.close();
+ // Initialize a bookmarks count variable.
+ int bookmarksCount = 0;
- // Convert the favorite icon byte array to a base 64 string.
- QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
+ // Check to see if the query is valid (there is at least one bookmark).
+ if (countBookmarksQuery.isValid())
+ {
+ // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks.
+ bookmarksCount = countBookmarksQuery.at() + 1;
+ }
// Instantiate an add bookmark query.
QSqlQuery addBookmarkQuery(bookmarksDatabase);
addBookmarkQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" +
BOOKMARK_NAME + ", " +
BOOKMARK_URL + ", " +
- FAVORITE_ICON + ") "
- "VALUES (:bookmark_name, :bookmark_url, :favorite_icon)"
+ FAVORITE_ICON + ", " +
+ DISPLAY_ORDER + ") " +
+ "VALUES (:bookmark_name, :bookmark_url, :favorite_icon, :display_order)"
);
- // Bind the values.
- addBookmarkQuery.bindValue(":bookmark_name", bookmarkName);
- addBookmarkQuery.bindValue(":bookmark_url", bookmarkUrl);
- addBookmarkQuery.bindValue(":favorite_icon", favoriteIconBase64String);
+ // Bind the query values.
+ addBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->bookmarkName);
+ addBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->bookmarkUrl);
+ addBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
+ addBookmarkQuery.bindValue(":display_order", bookmarksCount);
- // Execute the query.
+ // Execute the add bookmark query.
addBookmarkQuery.exec();
}
+void BookmarksDatabase::deleteBookmark(const int bookmarkId)
+{
+ // Get a handle for the bookmarks database.
+ QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
+
+ // Instantiate a delete bookmark query.
+ QSqlQuery deleteBookmarkQuery(bookmarksDatabase);
+
+ // Prepare the delete bookmark query.
+ deleteBookmarkQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
+
+ // Bind the query values.
+ deleteBookmarkQuery.bindValue(":id", bookmarkId);
+
+ // Execute the query.
+ deleteBookmarkQuery.exec();
+
+ // Reset the display order for the other items in the folder. TODO: make this folder aware.
+ // TODO: Perhaps, for performance reasons, this shouldn't run each time a bookmarks is deleted, but batched at the end.
+
+ // Instantiate a bookmarks query.
+ QSqlQuery bookmarksQuery(bookmarksDatabase);
+
+ // Set the query to be forward only, which is more performant.
+ bookmarksQuery.setForwardOnly(true);
+
+ // Prepare the bookmarks query.
+ bookmarksQuery.prepare("SELECT " + ID + ", " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
+
+ // Execute the query.
+ bookmarksQuery.exec();
+
+ // Create a new display order int.
+ int newDisplayOrder = 0;
+
+ // Update the display order for each bookmark.
+ while (bookmarksQuery.next())
+ {
+ // Check if the new display order is different than the current display order.
+ if (bookmarksQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder)
+ {
+ // Instantiate an update display order query.
+ QSqlQuery updateDisplayOrderQuery(bookmarksDatabase);
+
+ // Prepare the update display order query.
+ updateDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " + DISPLAY_ORDER + " = :display_order WHERE " + ID + " = :id");
+
+ // Bind the query values.
+ updateDisplayOrderQuery.bindValue(":display_order", newDisplayOrder);
+ updateDisplayOrderQuery.bindValue(":id", bookmarksQuery.value(ID).toInt());
+
+ // Execute the query.
+ updateDisplayOrderQuery.exec();
+ }
+
+ // Increment the new display order.
+ ++newDisplayOrder;
+ }
+}
+
+BookmarkStruct *BookmarksDatabase::getBookmark(int bookmarkId)
+{
+ // Get a handle for the bookmarks database.
+ QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
+
+ // Instantiate a bookmark query.
+ QSqlQuery bookmarkQuery(bookmarksDatabase);
+
+ // Set the query to be forward only, which is more performant.
+ bookmarkQuery.setForwardOnly(true);
+
+ // Prepare the bookmark query.
+ bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id");
+
+ // Bind the query values.
+ bookmarkQuery.bindValue(":id", bookmarkId);
+
+ // Execute the query.
+ bookmarkQuery.exec();
+
+ // Move to the first entry.
+ bookmarkQuery.first();
+
+ // Create a bookmark struct.
+ struct BookmarkStruct *bookmarkStructPointer = new BookmarkStruct();
+
+ // Get the favorite icon base 64 byte array.
+ QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarkQuery.value(FAVORITE_ICON).toByteArray());
+
+ // Create a favorite icon pixmap.
+ QPixmap favoriteIconPixmap;
+
+ // Load the pixmap from byte array.
+ favoriteIconPixmap.loadFromData(favoriteIconByteArray);
+
+ // Populate the bookmark struct.
+ bookmarkStructPointer->id = bookmarkQuery.value(ID).toInt();
+ bookmarkStructPointer->bookmarkName = bookmarkQuery.value(BOOKMARK_NAME).toString();
+ bookmarkStructPointer->bookmarkUrl = bookmarkQuery.value(BOOKMARK_URL).toString();
+ bookmarkStructPointer->displayOrder = bookmarkQuery.value(DISPLAY_ORDER).toInt();
+ bookmarkStructPointer->favoriteIcon = QIcon(favoriteIconPixmap);
+
+ // Return the bookmark struct pointer.
+ return bookmarkStructPointer;
+}
+
std::list<BookmarkStruct>* BookmarksDatabase::getBookmarks()
{
// Get a handle for the bookmarks database.
bookmarksQuery.setForwardOnly(true);
// Prepare the bookmarks query.
- bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE);
+ bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC");
// Execute the query.
bookmarksQuery.exec();
// Create a bookmark struct.
struct BookmarkStruct bookmarkStruct;
- // Get the favorite icon base 64 bute array.
+ // Get the favorite icon base 64 byte array.
QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
// Create a favorite icon pixmap.
bookmarkStruct.id = bookmarksQuery.value(ID).toInt();
bookmarkStruct.bookmarkName = bookmarksQuery.value(BOOKMARK_NAME).toString();
bookmarkStruct.bookmarkUrl = bookmarksQuery.value(BOOKMARK_URL).toString();
+ bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
// Add the bookmark to the list.
return bookmarkListPointer;
}
+QList<BookmarkStruct>* BookmarksDatabase::getBookmarksExcept(QList<int> *exceptDatabaseIdsListPointer)
+{
+ // Get a handle for the bookmarks database.
+ QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
+
+ // Instantiate a bookmarks query.
+ QSqlQuery bookmarksQuery(bookmarksDatabase);
+
+ // Set the query to be forward only, which is more performant.
+ bookmarksQuery.setForwardOnly(true);
+
+ // Create an IDs not to get string.
+ QString idsNotToGetString;
+
+ for (const int databaseId : *exceptDatabaseIdsListPointer)
+ {
+ // Check to see if there the string already has at least one number.
+ if (!idsNotToGetString.isEmpty())
+ {
+ // This is not the first number, so add a `,`.
+ idsNotToGetString.append(QLatin1Char(','));
+ }
+
+ // Append the database ID.
+ idsNotToGetString.append(QString::number(databaseId));
+ }
+
+ // Prepare the bookmarks query.
+ bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " NOT IN (" + idsNotToGetString + ") ORDER BY " + DISPLAY_ORDER + " ASC");
+
+ // Execute the query.
+ bookmarksQuery.exec();
+
+ // Create a bookmark list.
+ QList<BookmarkStruct> *bookmarkListPointer = new QList<BookmarkStruct>;
+
+ // Populate the bookmark list.
+ while (bookmarksQuery.next())
+ {
+ // Create a bookmark struct.
+ struct BookmarkStruct bookmarkStruct;
+
+ // Get the favorite icon base 64 byte array.
+ QByteArray favoriteIconByteArray = QByteArray::fromBase64(bookmarksQuery.value(FAVORITE_ICON).toByteArray());
+
+ // Create a favorite icon pixmap.
+ QPixmap favoriteIconPixmap;
+
+ // Load the pixmap from byte array.
+ favoriteIconPixmap.loadFromData(favoriteIconByteArray);
+
+ // Populate the bookmark struct.
+ bookmarkStruct.id = bookmarksQuery.value(ID).toInt();
+ bookmarkStruct.bookmarkName = bookmarksQuery.value(BOOKMARK_NAME).toString();
+ bookmarkStruct.bookmarkUrl = bookmarksQuery.value(BOOKMARK_URL).toString();
+ bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt();
+ bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap);
+
+ // Add the bookmark to the list.
+ bookmarkListPointer->push_back(bookmarkStruct);
+ }
+
+ // Return the bookmark list.
+ return bookmarkListPointer;
+}
+
+QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon)
+{
+ // Get a favorite icon pixmap.
+ QPixmap favoriteIconPixmap = favoriteIcon.pixmap(32, 32);
+
+ // Create a favorite icon byte array.
+ QByteArray favoriteIconByteArray;
+
+ // Create a favorite icon buffer.
+ QBuffer favoriteIconBuffer(&favoriteIconByteArray);
+
+ // Open the buffer.
+ favoriteIconBuffer.open(QIODevice::WriteOnly);
+
+ // Convert the favorite icon pixmap into a byte array in PNG format.
+ favoriteIconPixmap.save(&favoriteIconBuffer, "PNG");
+
+ // Close the buffer.
+ favoriteIconBuffer.close();
+
+ // Convert the favorite icon byte array to a base 64 string.
+ QString favoriteIconBase64String = favoriteIconByteArray.toBase64();
+
+ // Return the favorite icon base 64 string.
+ return favoriteIconBase64String;
+}
+
+void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer)
+{
+ // Get a handle for the bookmarks database.
+ QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
+
+ // Instantiate an update bookmark name.
+ QSqlQuery updateBookmarkQuery(bookmarksDatabase);
+
+ // Prepare the update bookmark query.
+ updateBookmarkQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " +
+ BOOKMARK_NAME + " = :bookmark_name, " +
+ BOOKMARK_URL + " = :bookmark_url, " +
+ DISPLAY_ORDER + " = :display_order, " +
+ FAVORITE_ICON + "= :favorite_icon " +
+ "WHERE " + ID + " = :id");
+
+ // Bind the query values.
+ updateBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->bookmarkName);
+ updateBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->bookmarkUrl);
+ updateBookmarkQuery.bindValue(":display_order", bookmarkStructPointer->displayOrder);
+ updateBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon));
+ updateBookmarkQuery.bindValue(":id", bookmarkStructPointer->id);
+
+ // Execute the query.
+ updateBookmarkQuery.exec();
+}
+
+void BookmarksDatabase::updateDisplayOrder(const int bookmarkId, const int displayOrder)
+{
+ // Get a handle for the bookmarks database.
+ QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME);
+
+ // Instantiate an update bookmark display order query.
+ QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase);
+
+ // Prepare the update bookmark display order query.
+ updateBookmarkDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE +
+ " SET " + DISPLAY_ORDER + " = :display_order " +
+ "WHERE " + ID + " = :id");
+
+ // Bind the query values.
+ updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder);
+ updateBookmarkDisplayOrderQuery.bindValue(":id", bookmarkId);
+
+ // Execute the query.
+ updateBookmarkDisplayOrderQuery.exec();
+}
+
void BookmarksDatabase::updateBookmarkName(const int bookmarkId, const QString &bookmarkName)
{
// Get a handle for the bookmarks database.
" SET " + BOOKMARK_NAME + " = :bookmark_name " +
"WHERE " + ID + " = :id");
- // Bind the values.
+ // Bind the query values.
updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName);
updateBookmarkNameQuery.bindValue(":id", bookmarkId);
" SET " + BOOKMARK_URL + " = :bookmark_url " +
"WHERE " + ID + " = :id");
- // Bind the values.
+ // Bind the query values.
updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl);
updateBookmarkUrlQuery.bindValue(":id", bookmarkId);