Skip to content

Instantly share code, notes, and snippets.

@rnewman
Created March 16, 2012 00:57
Show Gist options
  • Save rnewman/2047992 to your computer and use it in GitHub Desktop.
Save rnewman/2047992 to your computer and use it in GitHub Desktop.
Possible query
UPDATED:
-- Obviously select something more useful than favicon_url, which will always be null.
SELECT bid, url, title, visits, hid, i.favicon_url AS favicon_url
FROM
(
-- Bookmarks without history.
SELECT b._id AS bid, b.url AS url, b.title AS title, -1 AS visits, -1 AS hid
FROM bookmarks b
WHERE b.folder = 0 AND b.url NOT IN (SELECT url FROM history)
UNION ALL
-- History with and without bookmark.
SELECT b._id AS bid, h.url AS url, b.title AS title, h.visits AS visits, h._id AS hid
FROM history h LEFT OUTER JOIN bookmarks b ON b.url = h.url
WHERE h.url IS NOT NULL
AND b.folder IS NULL or b.folder = 0
) LEFT OUTER JOIN images i ON url = i.url_key;
We want to include:
* Folders. These will never be visited.
SELECT b.id AS bid, b.url AS url, b.title AS title, -1 AS visits, -1 AS hid
FROM bookmarks b WHERE b.is_folder = 0;
Obviously this will change when we support more types of bookmarks, but you get the idea.
* Bookmarks that have been visited, and those that have not.
SELECT b.id AS bid, b.url AS url, b.title AS title, h.visits AS visits, h.id AS hid
FROM bookmarks b LEFT OUTER JOIN history h ON b.url = h.url
WHERE b.is_folder = 0;
* History items that have been bookmarked, and those that have not.
SELECT b.id AS bid, b.url AS url, b.title AS title, h.visits AS visits, h.id AS hid
FROM history h LEFT OUTER JOIN bookmarks b ON b.url = h.url;
WHERE b.is_folder IS NULL or b.is_folder = 0;
Finally, join these with images:
SELECT bid, url, title, visits, hid, i.favicon_url AS favicon_url
FROM (
(
SELECT b.id AS bid, b.url AS url, b.title AS title, -1 AS visits, -1 AS hid
FROM bookmarks b WHERE b.is_folder = 0;
)
UNION ALL
(
SELECT b.id AS bid, b.url AS url, b.title AS title, h.visits AS visits, h.id AS hid
FROM bookmarks b LEFT OUTER JOIN history h ON b.url = h.url
WHERE b.is_folder = 0;
)
UNION ALL
(
SELECT b.id AS bid, b.url AS url, b.title AS title, h.visits AS visits, h.id AS hid
FROM history h LEFT OUTER JOIN bookmarks b ON b.url = h.url;
WHERE b.is_folder IS NULL or b.is_folder = 0;
)
) LEFT OUTER JOIN images i ON url = i.url_key;
I haven't tested this at all.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment