Confirmed users
764
edits
Firefox/Projects/Places DB Creation Scripts: Difference between revisions
Firefox/Projects/Places DB Creation Scripts (view source)
Revision as of 21:00, 25 February 2009
, 25 February 2009→Design
(→Design) |
(→Design) |
||
| Line 57: | Line 57: | ||
* BOOK_TAG_SQL defined in terms of SQL_STR_FRAGMENT_GET_BOOK_TAG http://mxr.mozilla.org/mozilla-central/source/toolkit/components/places/src/nsNavHistoryAutoComplete.cpp#94 which itself selects from moz_bookmarks x moz_bookmarks where TYPE_BOOKMARK; sometimes orders by lastModified; | * BOOK_TAG_SQL defined in terms of SQL_STR_FRAGMENT_GET_BOOK_TAG http://mxr.mozilla.org/mozilla-central/source/toolkit/components/places/src/nsNavHistoryAutoComplete.cpp#94 which itself selects from moz_bookmarks x moz_bookmarks where TYPE_BOOKMARK; sometimes orders by lastModified; | ||
* mDBAdaptiveQuery uses BOOK_TAG_SQL, moz_inputhistory... | * mDBAdaptiveQuery uses BOOK_TAG_SQL, moz_inputhistory... | ||
GetAutoCompleteBaseQuery() boils down to: | |||
<pre> | |||
SELECT h.url, | |||
h.title, | |||
f.url, | |||
(SELECT b.parent | |||
FROM moz_bookmarks b | |||
JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 | |||
WHERE b.type = nsINavBookmarksService::TYPE_BOOKMARK AND | |||
b.fk = h.id | |||
ORDER BY b.lastModified DESC LIMIT 1 | |||
) AS parent, | |||
(SELECT b.title | |||
FROM moz_bookmarks b | |||
JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 | |||
WHERE b.type = nsINavBookmarksService::TYPE_BOOKMARK AND | |||
b.fk = h.id | |||
ORDER BY b.lastModified DESC LIMIT 1 | |||
) AS bookmark, | |||
(SELECT GROUP_CONCAT(t.title, ',') | |||
FROM moz_bookmarks b | |||
JOIN moz_bookmarks t ON t.id = b.parent AND t.parent = ?1 | |||
WHERE b.type = nsINavBookmarksService::TYPE_BOOKMARK AND | |||
b.fk = h.id | |||
) AS tags, | |||
h.visit_count, h.typed, h.frecency | |||
FROM moz_places_temp h | |||
LEFT OUTER JOIN moz_favicons f ON f.id = h.favicon_id | |||
WHERE h.frecency <> 0 | |||
{ADDITIONAL_CONDITIONS} | |||
UNION ALL | |||
SELECT h.url, | |||
h.title, | |||
f.url, | |||
(SELECT b.parent | |||
FROM moz_bookmarks b | |||
JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 | |||
WHERE b.type = nsINavBookmarksService::TYPE_BOOKMARK AND | |||
b.fk = h.id | |||
ORDER BY b.lastModified DESC LIMIT 1 | |||
) AS parent, | |||
(SELECT b.title | |||
FROM moz_bookmarks b | |||
JOIN moz_bookmarks t ON t.id = b.parent AND t.parent != ?1 | |||
WHERE b.type = nsINavBookmarksService::TYPE_BOOKMARK AND | |||
b.fk = h.id | |||
ORDER BY b.lastModified DESC LIMIT 1 | |||
) AS bookmark, | |||
(SELECT GROUP_CONCAT(t.title, ',') | |||
FROM moz_bookmarks b | |||
JOIN moz_bookmarks t ON t.id = b.parent AND t.parent = ?1 | |||
WHERE b.type = nsINavBookmarksService::TYPE_BOOKMARK AND | |||
b.fk = h.id | |||
) AS tags, | |||
h.visit_count, | |||
h.typed, | |||
h.frecency | |||
FROM moz_places h | |||
LEFT OUTER JOIN moz_favicons f ON f.id = h.favicon_id | |||
WHERE h.id NOT IN (SELECT id FROM moz_places_temp) AND | |||
h.frecency <> 0 | |||
{ADDITIONAL_CONDITIONS} | |||
-- ORDER BY h.frecency | |||
ORDER BY 9 DESC LIMIT ?2 OFFSET ?3); | |||
</pre> | |||
= Implementation = | = Implementation = | ||