X-Git-Url: https://gitweb.stoutner.com/?a=blobdiff_plain;f=src%2Fdatabases%2FBookmarksDatabase.cpp;h=1050ba7e903d9ba4fe0ecbdce7efe9aa7dbb03bf;hb=234200f6e94439df6133a8eb61552a68dc9b3a5e;hp=fdcb0fdaee31af77131b0492045827c8e8f70bea;hpb=f18185adbdce9891be0cbd2197838441aaa5ed3e;p=PrivacyBrowserPC.git diff --git a/src/databases/BookmarksDatabase.cpp b/src/databases/BookmarksDatabase.cpp index fdcb0fd..1050ba7 100644 --- a/src/databases/BookmarksDatabase.cpp +++ b/src/databases/BookmarksDatabase.cpp @@ -109,30 +109,8 @@ void BookmarksDatabase::addBookmark(const BookmarkStruct *bookmarkStructPointer) // Get a handle for the bookmarks database. QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); - // Instantiate a count bookmarks query. TODO: This needs to be updated to only count the bookmarks in the current folder. - QSqlQuery countBookmarksQuery(bookmarksDatabase); - - // Set the query to be forward only, which is more performant. - countBookmarksQuery.setForwardOnly(true); - - // Prepare the count bookmarks query. - countBookmarksQuery.prepare("SELECT " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE); - - // Execute the count bookmarks query. - countBookmarksQuery.exec(); - - // Move to the last row. - countBookmarksQuery.last(); - - // Initialize a bookmarks count variable. - int bookmarksCount = 0; - - // 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; - } + // Get the folder item count. + int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId); // Instantiate an add bookmark query. QSqlQuery addBookmarkQuery(bookmarksDatabase); @@ -141,22 +119,58 @@ void BookmarksDatabase::addBookmark(const BookmarkStruct *bookmarkStructPointer) addBookmarkQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" + BOOKMARK_NAME + ", " + BOOKMARK_URL + ", " + - FAVORITE_ICON + ", " + - DISPLAY_ORDER + ") " + - "VALUES (:bookmark_name, :bookmark_url, :favorite_icon, :display_order)" + PARENT_FOLDER_ID + ", " + + DISPLAY_ORDER + ", " + + FAVORITE_ICON + ") " + + "VALUES (:bookmark_name, :bookmark_url, :parent_folder_id, :display_order, :favorite_icon)" ); // Bind the query values. - addBookmarkQuery.bindValue(":bookmark_name", bookmarkStructPointer->bookmarkName); - addBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->bookmarkUrl); + 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)); - addBookmarkQuery.bindValue(":display_order", bookmarksCount); // Execute the add bookmark query. addBookmarkQuery.exec(); } -void BookmarksDatabase::deleteBookmark(const int bookmarkId) +void BookmarksDatabase::addFolder(const BookmarkStruct *bookmarkStructPointer) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Get the folder item count. + int folderItemCount = getFolderItemCount(bookmarkStructPointer->parentFolderId); + + // Instantiate an add folder query. + QSqlQuery addFolderQuery(bookmarksDatabase); + + // Prepare the add folder query. + addFolderQuery.prepare("INSERT INTO " + BOOKMARKS_TABLE + " (" + + BOOKMARK_NAME + ", " + + 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 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); @@ -168,55 +182,133 @@ void BookmarksDatabase::deleteBookmark(const int bookmarkId) deleteBookmarkQuery.prepare("DELETE FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id"); // Bind the query values. - deleteBookmarkQuery.bindValue(":id", bookmarkId); + deleteBookmarkQuery.bindValue(":id", databaseId); // 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. +void BookmarksDatabase::deleteBookmarks(const QString url) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); - // Instantiate a bookmarks query. - QSqlQuery bookmarksQuery(bookmarksDatabase); + // Instantiate a parent folder IDs query. + QSqlQuery parentFolderIdsQuery(bookmarksDatabase); - // Set the query to be forward only, which is more performant. - bookmarksQuery.setForwardOnly(true); + // Prepare the parent folder IDs query. + parentFolderIdsQuery.prepare("SELECT " + PARENT_FOLDER_ID + " FROM " + BOOKMARKS_TABLE + " WHERE " + IS_FOLDER + " = 0 AND " + BOOKMARK_URL + " = :url"); - // Prepare the bookmarks query. - bookmarksQuery.prepare("SELECT " + ID + ", " + DISPLAY_ORDER + " FROM " + BOOKMARKS_TABLE + " ORDER BY " + DISPLAY_ORDER + " ASC"); + // Bind the query values. + parentFolderIdsQuery.bindValue(":url", url); // Execute the query. - bookmarksQuery.exec(); + parentFolderIdsQuery.exec(); - // Create a new display order int. - int newDisplayOrder = 0; + // Instantiate a delete bookmarks query. + QSqlQuery deleteBookmarksQuery(bookmarksDatabase); - // Update the display order for each bookmark. - while (bookmarksQuery.next()) + // 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()) { - // 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); + // Add the parent folder ID to the list. + parentFolderIdsList.push_back(parentFolderIdsQuery.value(PARENT_FOLDER_ID).toDouble()); + } - // Prepare the update display order query. - updateDisplayOrderQuery.prepare("UPDATE " + BOOKMARKS_TABLE + " SET " + DISPLAY_ORDER + " = :display_order WHERE " + ID + " = :id"); + // Sort the parent folder IDs list. + parentFolderIdsList.sort(); - // Bind the query values. - updateDisplayOrderQuery.bindValue(":display_order", newDisplayOrder); - updateDisplayOrderQuery.bindValue(":id", bookmarksQuery.value(ID).toInt()); + // Remove duplicate entries from the parent folder IDs list. + parentFolderIdsList.unique(); - // Execute the query. - updateDisplayOrderQuery.exec(); - } + // Update the display order of each folder where a bookmark was deleted. + for (const double parentFolderId : parentFolderIdsList) + updateFolderContentsDisplayOrder(parentFolderId); +} - // Increment the new display order. - ++newDisplayOrder; +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(int bookmarkId) +BookmarkStruct* BookmarksDatabase::getBookmark(const int databaseId) { // Get a handle for the bookmarks database. QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); @@ -231,7 +323,7 @@ BookmarkStruct *BookmarksDatabase::getBookmark(int bookmarkId) bookmarkQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " = :id"); // Bind the query values. - bookmarkQuery.bindValue(":id", bookmarkId); + bookmarkQuery.bindValue(":id", databaseId); // Execute the query. bookmarkQuery.exec(); @@ -252,10 +344,13 @@ BookmarkStruct *BookmarksDatabase::getBookmark(int bookmarkId) 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->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. @@ -298,10 +393,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. @@ -312,7 +410,7 @@ std::list* BookmarksDatabase::getBookmarks() return bookmarkListPointer; } -QList* BookmarksDatabase::getBookmarksExcept(QList *exceptDatabaseIdsListPointer) +QList* BookmarksDatabase::getBookmarksInFolderExcept(const double folderId, QList *exceptDatabaseIdsListPointer) { // Get a handle for the bookmarks database. QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); @@ -340,7 +438,10 @@ QList* BookmarksDatabase::getBookmarksExcept(QList *exceptD } // Prepare the bookmarks query. - bookmarksQuery.prepare("SELECT * FROM " + BOOKMARKS_TABLE + " WHERE " + ID + " NOT IN (" + idsNotToGetString + ") ORDER BY " + DISPLAY_ORDER + " ASC"); + 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(); @@ -364,10 +465,13 @@ QList* BookmarksDatabase::getBookmarksExcept(QList *exceptD 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. @@ -405,34 +509,433 @@ QString BookmarksDatabase::getFavoriteIconBase64String(const QIcon &favoriteIcon 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 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->bookmarkName); - updateBookmarkQuery.bindValue(":bookmark_url", bookmarkStructPointer->bookmarkUrl); + 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->id); + updateBookmarkQuery.bindValue(":id", bookmarkStructPointer->databaseId); // Execute the query. updateBookmarkQuery.exec(); } -void BookmarksDatabase::updateDisplayOrder(const int bookmarkId, const int displayOrder) +void BookmarksDatabase::updateBookmarkName(const int databaseId, const QString &bookmarkName) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate an update bookmark name query. + QSqlQuery updateBookmarkNameQuery(bookmarksDatabase); + + // Prepare the update bookmark name query. + updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE + + " SET " + BOOKMARK_NAME + " = :bookmark_name " + + "WHERE " + ID + " = :id"); + + // Bind the query values. + updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName); + updateBookmarkNameQuery.bindValue(":id", databaseId); + + // Execute the query. + updateBookmarkNameQuery.exec(); +} + +void BookmarksDatabase::updateBookmarkUrl(const int databaseId, const QString &bookmarkUrl) +{ + // Get a handle for the bookmarks database. + QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); + + // Instantiate an update bookmark URL query. + QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase); + + // Prepare the update bookmark URL query. + updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE + + " SET " + BOOKMARK_URL + " = :bookmark_url " + + "WHERE " + ID + " = :id"); + + // Bind the query values. + updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl); + 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); @@ -447,50 +950,66 @@ void BookmarksDatabase::updateDisplayOrder(const int bookmarkId, const int displ // Bind the query values. updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder); - updateBookmarkDisplayOrderQuery.bindValue(":id", bookmarkId); + updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId); // Execute the query. updateBookmarkDisplayOrderQuery.exec(); } -void BookmarksDatabase::updateBookmarkName(const int bookmarkId, const QString &bookmarkName) +void BookmarksDatabase::updateFolderContentsDisplayOrder(const double folderId) { // Get a handle for the bookmarks database. QSqlDatabase bookmarksDatabase = QSqlDatabase::database(CONNECTION_NAME); - // Instantiate an update bookmark name query. - QSqlQuery updateBookmarkNameQuery(bookmarksDatabase); + // Instantiate a folder contents query. + QSqlQuery folderContentsQuery(bookmarksDatabase); - // Prepare the update bookmark name query. - updateBookmarkNameQuery.prepare("UPDATE " + BOOKMARKS_TABLE + - " SET " + BOOKMARK_NAME + " = :bookmark_name " + - "WHERE " + ID + " = :id"); + // 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. - updateBookmarkNameQuery.bindValue(":bookmark_name", bookmarkName); - updateBookmarkNameQuery.bindValue(":id", bookmarkId); + folderContentsQuery.bindValue(":parent_folder_id", folderId); // Execute the query. - updateBookmarkNameQuery.exec(); + 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::updateBookmarkUrl(const int bookmarkId, const QString &bookmarkUrl) +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 URL query. - QSqlQuery updateBookmarkUrlQuery(bookmarksDatabase); + // Instantiate an update bookmark display order query. + QSqlQuery updateBookmarkDisplayOrderQuery(bookmarksDatabase); - // Prepare the update bookmark URL query. - updateBookmarkUrlQuery.prepare("UPDATE " + BOOKMARKS_TABLE + - " SET " + BOOKMARK_URL + " = :bookmark_url " + - "WHERE " + ID + " = :id"); + // 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. - updateBookmarkUrlQuery.bindValue(":bookmark_url", bookmarkUrl); - updateBookmarkUrlQuery.bindValue(":id", bookmarkId); + updateBookmarkDisplayOrderQuery.bindValue(":parent_folder_id", parentFolderId); + updateBookmarkDisplayOrderQuery.bindValue(":display_order", displayOrder); + updateBookmarkDisplayOrderQuery.bindValue(":id", databaseId); // Execute the query. - updateBookmarkUrlQuery.exec(); + updateBookmarkDisplayOrderQuery.exec(); }