X-Git-Url: https://gitweb.stoutner.com/?a=blobdiff_plain;f=src%2Fdatabases%2FBookmarksDatabase.cpp;h=1050ba7e903d9ba4fe0ecbdce7efe9aa7dbb03bf;hb=refs%2Fheads%2Fmaster;hp=c290682b3113dd652836ad2353460186ac27a882;hpb=7c6edb3608791950c6146ac242e2b6f493ca8e8c;p=PrivacyBrowserPC.git diff --git a/src/databases/BookmarksDatabase.cpp b/src/databases/BookmarksDatabase.cpp index c290682..1050ba7 100644 --- a/src/databases/BookmarksDatabase.cpp +++ b/src/databases/BookmarksDatabase.cpp @@ -104,50 +104,257 @@ void BookmarksDatabase::addDatabase() } }; -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); + // Get the folder item count. + int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId); - // Create a favorite icon byte array. - QByteArray favoriteIconByteArray; + // Instantiate an add bookmark query. + QSqlQuery addBookmarkQuery(bookmarksDatabase); - // Create a favorite icon buffer. - QBuffer favoriteIconBuffer(&favoriteIconByteArray); + // Prepare the add bookmark query. + addBookmarkQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" + + BOOKMARK_NAME + ", " + + BOOKMARK_URL + ", " + + PARENT_FOLDER_ID + ", " + + DISPLAY_ORDER + ", " + + FAVORITE_ICON + ") " + + "VALUES (:bookmark_name, :bookmark_url, :parent_folder_id, :display_order, :favorite_icon)" + ); - // Open the buffer. - favoriteIconBuffer.open(QIODevice::WriteOnly); + // Bind the query values. + addBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->name); + addBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->url); + addBookmarkQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId); + addBookmarkQuery.bindValue(":display_order", folderItemCount); + addBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon)); - // Convert the favorite icon pixmap into a byte array in PNG format. - favoriteIconPixmap.save(&favoriteIconBuffer, "PNG"); + // Execute the add bookmark query. + addBookmarkQuery.exec(); +} - // Close the buffer. - favoriteIconBuffer.close(); +void BookmarksDatabase::addFolder(const BookmarkStruct *bookmarkStructPointer) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); - // Convert the favorite icon byte array to a base 64 string. - QString favoriteIconBase64String = favoriteIconByteArray.toBase64(); + // Get the folder item count. + int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId); - // Instantiate an add bookmark query. - QSqlQuery addBookmarkQuery(bookmarksDatabase); + // Instantiate an add folder query. + QSqlQuery addFolderQuery(bookmarksDatabase); - // Prepare the add bookmark query. - addBookmarkQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" + + // Prepare the add folder query. + addFolderQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" + BOOKMARK_NAME + ", " + - BOOKMARK_URL + ", " + - FAVORITE_ICON + ") " - "VALUES (:bookmark_name, :bookmark_url, :favorite_icon)" + PARENT_FOLDER_ID + ", " + + DISPLAY_ORDER + ", " + + IS_FOLDER + ", " + + FOLDER_ID + ", " + + FAVORITE_ICON + ") " + + "VALUES (:bookmark_name, :parent_folder_id, :display_order, :is_folder, :folder_id, :favorite_icon)" ); - // Bind the values. - addBookmarkQuery.bindValue(":bookmark_name", bookmarkName); - addBookmarkQuery.bindValue(":bookmark_url", bookmarkUrl); - addBookmarkQuery.bindValue(":favorite_icon", favoriteIconBase64String); + // Bind the query values. + addFolderQuery.bindValue(":bookmark_name", bookmarkStructPointer->name); + addFolderQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId); + addFolderQuery.bindValue(":display_order", folderItemCount); + addFolderQuery.bindValue(":is_folder", 1); + addFolderQuery.bindValue(":folder_id", generateFolderId()); + addFolderQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon)); + + // Execute the add folder query. + addFolderQuery.exec(); +} + +void BookmarksDatabase::deleteBookmark(const int databaseId) +{ + // 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", databaseId); // Execute the query. - addBookmarkQuery.exec(); + deleteBookmarkQuery.exec(); +} + +void BookmarksDatabase::deleteBookmarks(const QString url) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a parent folder IDs query. + QSqlQuery parentFolderIdsQuery(bookmarksDatabase); + + // Prepare the parent folder IDs query. + parentFolderIdsQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url"); + + // Bind the query values. + parentFolderIdsQuery.bindValue(":url", url); + + // Execute the query. + parentFolderIdsQuery.exec(); + + // Instantiate a delete bookmarks query. + QSqlQuery deleteBookmarksQuery(bookmarksDatabase); + + // Prepare the delete bookmark query. + deleteBookmarksQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url"); + + // Bind the query values. + deleteBookmarksQuery.bindValue(":url", url); + + // Execute the query. + deleteBookmarksQuery.exec(); + + // Create a parent folder IDs list. A standard list can be sorted and deduplicated. + std::list parentFolderIdsList; + + // Populate the parent folder IDs list. + while (parentFolderIdsQuery.next()) + { + // Add the parent folder ID to the list. + parentFolderIdsList.push_back(parentFolderIdsQuery.value(PARENT_FOLDER_ID).toDouble()); + } + + // Sort the parent folder IDs list. + parentFolderIdsList.sort(); + + // Remove duplicate entries from the parent folder IDs list. + parentFolderIdsList.unique(); + + // Update the display order of each folder where a bookmark was deleted. + for (const double parentFolderId : parentFolderIdsList) + updateFolderContentsDisplayOrder(parentFolderId); +} + +double BookmarksDatabase::generateFolderId() +{ + // Get the current time in epoch format (milliseconds). + double possibleFolderId = std::chrono::duration_cast(std::chrono::system_clock::now().time_since_epoch()).count(); + + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a existing folder query. + QSqlQuery existingFolderQuery(bookmarksDatabase); + + // Prepare the existing folder query. + existingFolderQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :possible_folder_id"); + + // Bind the query values. + existingFolderQuery.bindValue(":possible_folder_id", possibleFolderId); + + // Execute the query. + existingFolderQuery.exec(); + + // 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. + if (existingFolderQuery.isValid()) + possibleFolderId = generateFolderId(); + + return possibleFolderId; +} + +QList* BookmarksDatabase::getAllFolderUrls(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder URLs query. + QSqlQuery folderUrlsQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderUrlsQuery.setForwardOnly(true); + + // Prepare the folder URLs query. + folderUrlsQuery.prepare("SELECT " + BOOKMARK_URL + ", " + IS_FOLDER + ", " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id"); + + // Bind the query values. + folderUrlsQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + folderUrlsQuery.exec(); + + // Create a folder URLs list. + QList *folderUrlsListPointer = new QList; + + // Populate the folder URLs list. + while (folderUrlsQuery.next()) + { + // Process the entry according to the type. + if (folderUrlsQuery.value(IS_FOLDER).toBool()) // This is a folder. + { + // Get the subfolder URLs to the list. + folderUrlsListPointer->append(*getAllFolderUrls(folderUrlsQuery.value(FOLDER_ID).toDouble())); + } + else // This is a bookmark. + { + // Add the URL to the list. + folderUrlsListPointer->append(folderUrlsQuery.value(BOOKMARK_URL).toString()); + } + } + + // Return the folder URLs list. + return folderUrlsListPointer; +} + +BookmarkStruct* BookmarksDatabase::getBookmark(const int databaseId) +{ + // 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", databaseId); + + // 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->databaseId = bookmarkQuery.value(ID).toInt(); + bookmarkStructPointer->name = bookmarkQuery.value(BOOKMARK_NAME).toString(); + bookmarkStructPointer->url = bookmarkQuery.value(BOOKMARK_URL).toString(); + bookmarkStructPointer->parentFolderId = bookmarkQuery.value(PARENT_FOLDER_ID).toDouble(); + bookmarkStructPointer->displayOrder = bookmarkQuery.value(DISPLAY_ORDER).toInt(); + bookmarkStructPointer->isFolder = bookmarkQuery.value(IS_FOLDER).toBool(); + bookmarkStructPointer->folderId = bookmarkQuery.value(FOLDER_ID).toDouble(); + bookmarkStructPointer->favoriteIcon = QIcon(favoriteIconPixmap); + + // Return the bookmark struct pointer. + return bookmarkStructPointer; } std::list* BookmarksDatabase::getBookmarks() @@ -162,7 +369,7 @@ std::list* BookmarksDatabase::getBookmarks() 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(); @@ -176,7 +383,79 @@ std::list* BookmarksDatabase::getBookmarks() // 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. + QPixmap favoriteIconPixmap; + + // Load the pixmap from byte array. + favoriteIconPixmap.loadFromData(favoriteIconByteArray); + + // Populate the bookmark struct. + bookmarkStruct.databaseId = bookmarksQuery.value(ID).toInt(); + bookmarkStruct.name = bookmarksQuery.value(BOOKMARK_NAME).toString(); + bookmarkStruct.url = bookmarksQuery.value(BOOKMARK_URL).toString(); + bookmarkStruct.parentFolderId = bookmarksQuery.value(PARENT_FOLDER_ID).toDouble(); + bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt(); + bookmarkStruct.isFolder = bookmarksQuery.value(IS_FOLDER).toBool(); + bookmarkStruct.folderId = bookmarksQuery.value(FOLDER_ID).toDouble(); + bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap); + + // Add the bookmark to the list. + bookmarkListPointer->push_back(bookmarkStruct); + } + + // Return the bookmark list. + return bookmarkListPointer; +} + +QList* BookmarksDatabase::getBookmarksInFolderExcept(const double folderId, QList *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 " + PARENT_FOLDER_ID + " = :parent_folder_id AND " + ID + " NOT IN (" + idsNotToGetString + ") ORDER BY " + DISPLAY_ORDER + " ASC"); + + // Bind the query values. + bookmarksQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + bookmarksQuery.exec(); + + // Create a bookmark list. + QList *bookmarkListPointer = new QList; + + // 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. @@ -186,9 +465,13 @@ std::list* BookmarksDatabase::getBookmarks() 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.databaseId = bookmarksQuery.value(ID).toInt(); + bookmarkStruct.name = bookmarksQuery.value(BOOKMARK_NAME).toString(); + bookmarkStruct.url = bookmarksQuery.value(BOOKMARK_URL).toString(); + bookmarkStruct.parentFolderId = bookmarksQuery.value(PARENT_FOLDER_ID).toDouble(); + bookmarkStruct.displayOrder = bookmarksQuery.value(DISPLAY_ORDER).toInt(); + bookmarkStruct.isFolder = bookmarksQuery.value(IS_FOLDER).toBool(); + bookmarkStruct.folderId = bookmarksQuery.value(FOLDER_ID).toDouble(); bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap); // Add the bookmark to the list. @@ -199,7 +482,418 @@ std::list* BookmarksDatabase::getBookmarks() return bookmarkListPointer; } -void BookmarksDatabase::updateBookmarkName(const int bookmarkId, const QString &bookmarkName) +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; +} + +QList* BookmarksDatabase::getFolderContents(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder contents query. + QSqlQuery folderContentsQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderContentsQuery.setForwardOnly(true); + + // Prepare the folder contents query. + folderContentsQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC"); + + // Bind the query values. + folderContentsQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + folderContentsQuery.exec(); + + // Create a folder contents list. + QList *folderContentsListPointer = new QList; + + // Populate the folder contents list. + while (folderContentsQuery.next()) + { + // Create a bookmark struct. + struct BookmarkStruct bookmarkStruct; + + // Get the favorite icon base 64 byte array. + QByteArray favoriteIconByteArray = QByteArray::fromBase64(folderContentsQuery.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.databaseId = folderContentsQuery.value(ID).toInt(); + bookmarkStruct.name = folderContentsQuery.value(BOOKMARK_NAME).toString(); + bookmarkStruct.url = folderContentsQuery.value(BOOKMARK_URL).toString(); + bookmarkStruct.parentFolderId = folderContentsQuery.value(PARENT_FOLDER_ID).toDouble(); + bookmarkStruct.displayOrder = folderContentsQuery.value(DISPLAY_ORDER).toInt(); + bookmarkStruct.isFolder = folderContentsQuery.value(IS_FOLDER).toBool(); + bookmarkStruct.folderId = folderContentsQuery.value(FOLDER_ID).toDouble(); + bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap); + + // Add the item to the list. + folderContentsListPointer->append(bookmarkStruct); + } + + // Return the folder contents list. + return folderContentsListPointer; +} + +QList* BookmarksDatabase::getFolderContentsDatabaseIds(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder contents query. + QSqlQuery folderContentsQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderContentsQuery.setForwardOnly(true); + + // Prepare the folder contents query. + folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id"); + + // Bind the query values. + folderContentsQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + folderContentsQuery.exec(); + + // Create a folder contents database ID list. + QList *folderContentsDatabaseIdsListPointer = new QList; + + // Populate the folder contents list. + while (folderContentsQuery.next()) + { + // Add the database ID to the list. + folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt()); + } + + // Return the folder contents database ID list. + return folderContentsDatabaseIdsListPointer; +} + +QList *BookmarksDatabase::getFolderContentsDatabaseIdsRecursively(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder contents query. + QSqlQuery folderContentsQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderContentsQuery.setForwardOnly(true); + + // Prepare the folder contents query. + folderContentsQuery.prepare("SELECT " + ID + ", " + IS_FOLDER + ", " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id"); + + // Bind the query values. + folderContentsQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + folderContentsQuery.exec(); + + // Create a folder contents database ID list. + QList *folderContentsDatabaseIdsListPointer = new QList; + + // Populate the folder contents list. + while (folderContentsQuery.next()) + { + // Add the database ID to the list. + folderContentsDatabaseIdsListPointer->append(folderContentsQuery.value(ID).toInt()); + + // Recursively get the contents if this is a subfolder. + if (folderContentsQuery.value(IS_FOLDER).toBool()) + folderContentsDatabaseIdsListPointer->append(*getFolderContentsDatabaseIdsRecursively(folderContentsQuery.value(FOLDER_ID).toDouble())); + } + + // Return the folder contents database ID list. + return folderContentsDatabaseIdsListPointer; +} + +int BookmarksDatabase::getFolderDatabaseId(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder database ID query. + QSqlQuery folderDatabaseIdQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderDatabaseIdQuery.setForwardOnly(true); + + // Prepare the folder database ID query. + folderDatabaseIdQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + FOLDER_ID + " = :folder_id"); + + // Bind the query values. + folderDatabaseIdQuery.bindValue(":folder_id", folderId); + + // Execute the query. + folderDatabaseIdQuery.exec(); + + // Move to the first entry. + folderDatabaseIdQuery.first(); + + // Return the folder database ID. + return folderDatabaseIdQuery.value(ID).toInt(); +} + +double BookmarksDatabase::getFolderId(const int databaseId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder ID query. + QSqlQuery folderIdQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderIdQuery.setForwardOnly(true); + + // Prepare the folder ID query. + folderIdQuery.prepare("SELECT " + FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id"); + + // Bind the query values. + folderIdQuery.bindValue(":database_id", databaseId); + + // Execute the query. + folderIdQuery.exec(); + + // Move to the first entry. + folderIdQuery.first(); + + // Return the folder ID. + return folderIdQuery.value(FOLDER_ID).toDouble(); +} + +int BookmarksDatabase::getFolderItemCount(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder contents query. + QSqlQuery folderContentsQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderContentsQuery.setForwardOnly(true); + + // Prepare the folder contents query. + folderContentsQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id"); + + // Bind the query values. + folderContentsQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + folderContentsQuery.exec(); + + // Move to the last row. + folderContentsQuery.last(); + + // Initialize an item count variable. + int itemCount = 0; + + // Check to see if the query is valid (there is at least one item). + if (folderContentsQuery.isValid()) + { + // Get the number of rows (which is zero based) and add one to calculate the number of bookmarks. + itemCount = folderContentsQuery.at() + 1; + } + + // Return the item count. + return itemCount; +} + +double BookmarksDatabase::getParentFolderId(const int databaseId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a parent folder ID query. + QSqlQuery parentFolderIdQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + parentFolderIdQuery.setForwardOnly(true); + + // Prepare the parent folder ID query. + parentFolderIdQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :database_id"); + + // Bind the query values. + parentFolderIdQuery.bindValue(":database_id", databaseId); + + // Execute the query. + parentFolderIdQuery.exec(); + + // Move to the first entry. + parentFolderIdQuery.first(); + + // Return the parent folder ID. + return parentFolderIdQuery.value(PARENT_FOLDER_ID).toDouble(); +} + +QList* BookmarksDatabase::getSubfolders(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a subfolders query. + QSqlQuery subfoldersQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + subfoldersQuery.setForwardOnly(true); + + // Prepare the subfolders query. + subfoldersQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 1 AND " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC"); + + // Bind the query values. + subfoldersQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + subfoldersQuery.exec(); + + // Create a subfolder list. + QList *subfoldersListPointer = new QList; + + // Populate the subfolder list. + while (subfoldersQuery.next()) + { + // Create a bookmark struct. + struct BookmarkStruct bookmarkStruct; + + // Get the favorite icon base 64 byte array. + QByteArray favoriteIconByteArray = QByteArray::fromBase64(subfoldersQuery.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.databaseId = subfoldersQuery.value(ID).toInt(); + bookmarkStruct.name = subfoldersQuery.value(BOOKMARK_NAME).toString(); + bookmarkStruct.parentFolderId = subfoldersQuery.value(PARENT_FOLDER_ID).toDouble(); + bookmarkStruct.displayOrder = subfoldersQuery.value(DISPLAY_ORDER).toInt(); + bookmarkStruct.isFolder = subfoldersQuery.value(IS_FOLDER).toBool(); + bookmarkStruct.folderId = subfoldersQuery.value(FOLDER_ID).toDouble(); + bookmarkStruct.favoriteIcon = QIcon(favoriteIconPixmap); + + // Add the subfolder to the list. + subfoldersListPointer->append(bookmarkStruct); + } + + // Return the subfolders list. + return subfoldersListPointer; +} + +bool BookmarksDatabase::isBookmarked(const QString url) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate an is bookmarked query. + QSqlQuery isBookmarkedQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + isBookmarkedQuery.setForwardOnly(true); + + // Prepare the is bookmarked query. + isBookmarkedQuery.prepare("SELECT " + ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url"); + + // Bind the query values. + isBookmarkedQuery.bindValue(":url", url); + + // Execute the query. + isBookmarkedQuery.exec(); + + // Move to the first entry. + isBookmarkedQuery.first(); + + // Return true if the query is valid (there is at least one item). + return isBookmarkedQuery.isValid(); +} + +bool BookmarksDatabase::isFolder(const int databaseId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate an is folder query. + QSqlQuery isFolderQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + isFolderQuery.setForwardOnly(true); + + // Prepare the is folder query. + isFolderQuery.prepare("SELECT " + IS_FOLDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id"); + + // Bind the query values. + isFolderQuery.bindValue(":id", databaseId); + + // Execute the query. + isFolderQuery.exec(); + + // Move to the first entry. + isFolderQuery.first(); + + // Return the folder status. + return isFolderQuery.value(IS_FOLDER).toBool(); +} + +void BookmarksDatabase::updateBookmark(const BookmarkStruct *bookmarkStructPointer) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate an update bookmark query. + QSqlQuery updateBookmarkQuery(bookmarksDatabase); + + // Prepare the update bookmark query. + updateBookmarkQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " + + BOOKMARK_NAME + " = :bookmark_name, " + + BOOKMARK_URL + " = :bookmark_url, " + + PARENT_FOLDER_ID + " = :parent_folder_id, " + + DISPLAY_ORDER + " = :display_order, " + + FAVORITE_ICON + "= :favorite_icon " + + "WHERE " + ID + " = :id"); + + // Bind the query values. + updateBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->name); + updateBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->url); + updateBookmarkQuery.bindValue(":parent_folder_id", bookmarkStructPointer->parentFolderId); + updateBookmarkQuery.bindValue(":display_order", bookmarkStructPointer->displayOrder); + updateBookmarkQuery.bindValue(":favorite_icon", getFavoriteIconBase64String(bookmarkStructPointer->favoriteIcon)); + updateBookmarkQuery.bindValue(":id", bookmarkStructPointer->databaseId); + + // Execute the query. + updateBookmarkQuery.exec(); +} + +void BookmarksDatabase::updateBookmarkName(const int databaseId, const QString &bookmarkName) { // Get a handle for the bookmarks database. QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); @@ -212,15 +906,15 @@ void BookmarksDatabase::updateBookmarkName(const int bookmarkId, const QString & " SET " + BOOKMARK_NAME + " = :bookmark_name " + "WHERE " + ID + " = :id"); - // Bind the values. + // Bind the query values. updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName); - updateBookmarkNameQuery.bindValue(":id", bookmarkId); + updateBookmarkNameQuery.bindValue(":id", databaseId); // Execute the query. updateBookmarkNameQuery.exec(); } -void BookmarksDatabase::updateBookmarkUrl(const int bookmarkId, const QString &bookmarkUrl) +void BookmarksDatabase::updateBookmarkUrl(const int databaseId, const QString &bookmarkUrl) { // Get a handle for the bookmarks database. QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); @@ -233,10 +927,89 @@ void BookmarksDatabase::updateBookmarkUrl(const int bookmarkId, const QString &b " SET " + BOOKMARK_URL + " = :bookmark_url " + "WHERE " + ID + " = :id"); - // Bind the values. + // Bind the query values. updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl); - updateBookmarkUrlQuery.bindValue(":id", bookmarkId); + updateBookmarkUrlQuery.bindValue(":id", databaseId); // Execute the query. updateBookmarkUrlQuery.exec(); } + +void BookmarksDatabase::updateDisplayOrder(const int databaseId, 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", databaseId); + + // Execute the query. + updateBookmarkDisplayOrderQuery.exec(); +} + +void BookmarksDatabase::updateFolderContentsDisplayOrder(const double folderId) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate a folder contents query. + QSqlQuery folderContentsQuery(bookmarksDatabase); + + // Set the query to be forward only, which is more performant. + folderContentsQuery.setForwardOnly(true); + + // Prepare the folder contents query. + folderContentsQuery.prepare("SELECT " + ID + ", " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE + " WHERE " + PARENT_FOLDER_ID + " = :parent_folder_id ORDER BY " + DISPLAY_ORDER + " ASC"); + + // Bind the query values. + folderContentsQuery.bindValue(":parent_folder_id", folderId); + + // Execute the query. + folderContentsQuery.exec(); + + // Define a new display order int. + int newDisplayOrder = 0; + + // Populate the subfolder list. + while (folderContentsQuery.next()) + { + // Update the display order if it has changed. + if (folderContentsQuery.value(DISPLAY_ORDER).toInt() != newDisplayOrder) + updateDisplayOrder(folderContentsQuery.value(ID).toInt(), newDisplayOrder); + + // Increment the new display order. + ++newDisplayOrder; + } +} + +void BookmarksDatabase::updateParentFolderAndDisplayOrder(const int databaseId, const double parentFolderId, 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 " + PARENT_FOLDER_ID + " = :parent_folder_id " + + ", " + DISPLAY_ORDER + " = :display_order " + + "WHERE " + ID + " = :id"); + + // Bind the query values. + updateBookmarkDisplayOrderQuery.bindValue(":parent_folder_id", parentFolderId); + updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder); + updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId); + + // Execute the query. + updateBookmarkDisplayOrderQuery.exec(); +}