Firefox/Projects/Places DB Creation Scripts: Difference between revisions

Firefox/Projects/Places DB Creation Scripts (view source)

Revision as of 21:15, 25 February 2009

1,572 bytes added , 25 February 2009

→‎Design

Adw

Confirmed users

764

edits

@@ Line 121: / Line 121: @@
        h.frecency <> 0
        {ADDITIONAL_CONDITIONS}
--- ORDER BY h.frecency
+-- ORDER BY h.frecency, the 9th column
 ORDER BY 9 DESC LIMIT ?2 OFFSET ?3);
+</pre>
+mDBAdaptiveQuery:
+<pre>
+SELECT IFNULL(h_t.url, h.url),
+       IFNULL(h_t.title, 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,
+      IFNULL(h_t.visit_count, h.visit_count),
+      IFNULL(h_t.typed, h.typed),
+      rank
+FROM (SELECT ROUND(
+               MAX(
+                 ((i.input = ?2) + (SUBSTR(i.input, 1, LENGTH(?2)) = ?2)) *
+                 i.use_count
+               ), 1
+             ) AS rank,
+             place_id
+      FROM moz_inputhistory i
+      GROUP BY i.place_id HAVING rank > 0
+     ) AS i
+LEFT JOIN moz_places h ON h.id = i.place_id
+LEFT JOIN moz_places_temp h_t ON h_t.id = i.place_id
+LEFT JOIN moz_favicons f ON f.id = IFNULL(h_t.favicon_id, h.favicon_id)
+WHERE IFNULL(h_t.url, h.url) NOTNULL
+ORDER BY rank DESC, IFNULL(h_t.frecency, h.frecency) DESC
 </pre>