1865165 - Should deleting the last bookmark for a URL also delete all its tags and keywords?

Status: NEW

(Application Services :: Places, enhancement, P3)

Description

[Mark Hammond [:markh] [:mhammond]]

From github: https://github.com/mozilla/application-services/issues/2782.

Under the hood, we associate tags and keywords with URLs, not bookmarks, but all our UI exposes them on bookmarks. It might be surprising for a user to delete a bookmark, and find that typing the search keyword in the awesomebar still opens it.

Fixing this involves changing delete_bookmark to check if the foreign count for the deleted bookmark's URL is equal to the sum of the counts of its tags and keywords.

Here's how I think that would work for tags; keywords will need something similar, but swapping moz_keywords and moz_tags_relation:

DELETE FROM moz_tags_relation
WHERE (tag_id, place_id) = (
    SELECT r.tag_id, r.place_id
    FROM moz_tags_relation r
    JOIN moz_places h ON h.id = r.place_id
    WHERE h.id = :deletedBookmarkPlaceId AND
          h.foreign_count = COUNT(*) + (SELECT COUNT(*) FROM moz_keywords k
                                        WHERE k.place_id = h.id)
)

(The h.foreign_count = ... expression checks if the only remaining foreign key references come from tags and bookmarks, and needs to be inverted when deleting from moz_keywords).

Since delete_bookmark_in_tx fetches the bookmark before deleting it, we already know the deleted bookmark's place ID.

For delete_everything, it's a bit trickier. Probably the easiest way to do that without keeping track of the deleted bookmark URLs is a full scan on moz_places:

DELETE FROM moz_tags_relation
WHERE (tag_id, place_id) = (
    SELECT r.tag_id, r.place_id
    FROM moz_tags_relation r
    JOIN moz_places h ON h.id = r.place_id
    GROUP BY h.id
    HAVING h.foreign_count = COUNT(*) + (SELECT COUNT(*) FROM moz_keywords k
                                         WHERE k.place_id = h.id)
)

(And ditto for moz_keywords).

We'll probably need to tweak the SQL a bit, and make sure we have good test coverage—including multiple bookmarks with the same URL—but this could be a great second or third bug.

┆Issue is synchronized with this Jira Task
┆Epic: Important backlog

Change performed by the Move to Bugzilla add-on.