Bug 459934 - should lazy-load places autocomplete statements
authorMarco Bonardo <mak77@bonardo.net>
Fri, 17 Oct 2008 06:12:53 -0400
changeset 20583 f31b66fd4192555e74e66909b7e5423ec5b9a630
parent 20582 e399c10c38b38e6763dae177d3cd412e54e6a42a
child 20584 d7d64f68423b68a671f623f123e90057ebc49dac
push idunknown
push userunknown
push dateunknown
bugs459934
milestone1.9.1b2pre
Bug 459934 - should lazy-load places autocomplete statements r=dietrich r=sdwilsh
toolkit/components/places/src/nsNavHistory.h
toolkit/components/places/src/nsNavHistoryAutoComplete.cpp
--- a/toolkit/components/places/src/nsNavHistory.h
+++ b/toolkit/components/places/src/nsNavHistory.h
@@ -649,22 +649,26 @@ protected:
   static const PRInt32 kAutoCompleteIndex_Title;
   static const PRInt32 kAutoCompleteIndex_FaviconURL;
   static const PRInt32 kAutoCompleteIndex_ParentId;
   static const PRInt32 kAutoCompleteIndex_BookmarkTitle;
   static const PRInt32 kAutoCompleteIndex_Tags;
   static const PRInt32 kAutoCompleteIndex_VisitCount;
   nsCOMPtr<mozIStorageStatement> mDBCurrentQuery; //  kAutoCompleteIndex_* results
   nsCOMPtr<mozIStorageStatement> mDBAutoCompleteQuery; //  kAutoCompleteIndex_* results
+  mozIStorageStatement* GetDBAutoCompleteHistoryQuery();
   nsCOMPtr<mozIStorageStatement> mDBAutoCompleteHistoryQuery; //  kAutoCompleteIndex_* results
+  mozIStorageStatement* GetDBAutoCompleteStarQuery();
   nsCOMPtr<mozIStorageStatement> mDBAutoCompleteStarQuery; //  kAutoCompleteIndex_* results
+  mozIStorageStatement* GetDBAutoCompleteTagsQuery();
   nsCOMPtr<mozIStorageStatement> mDBAutoCompleteTagsQuery; //  kAutoCompleteIndex_* results
   nsCOMPtr<mozIStorageStatement> mDBPreviousQuery; //  kAutoCompleteIndex_* results
   nsCOMPtr<mozIStorageStatement> mDBAdaptiveQuery; //  kAutoCompleteIndex_* results
   nsCOMPtr<mozIStorageStatement> mDBKeywordQuery; //  kAutoCompleteIndex_* results
+  mozIStorageStatement* GetDBFeedbackIncrease();
   nsCOMPtr<mozIStorageStatement> mDBFeedbackIncrease;
 
   /**
    * AutoComplete word matching behavior to determine if words should match on
    * word boundaries or not or both.
    */
   enum MatchType {
     MATCH_ANYWHERE,
--- a/toolkit/components/places/src/nsNavHistoryAutoComplete.cpp
+++ b/toolkit/components/places/src/nsNavHistoryAutoComplete.cpp
@@ -21,16 +21,17 @@
  *
  * Contributor(s):
  *   Brett Wilson <brettw@gmail.com>
  *   Joe Hewitt <hewitt@netscape.com>
  *   Blake Ross <blaker@netscape.com>
  *   Seth Spitzer <sspitzer@mozilla.org>
  *   Dietrich Ayala <dietrich@mozilla.com>
  *   Edward Lee <edward.lee@engineering.uiuc.edu>
+ *   Marco Bonardo <mak77@bonardo.net>
  *
  * Alternatively, the contents of this file may be used under the terms of
  * either the GNU General Public License Version 2 or later (the "GPL"), or
  * the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
  * in which case the provisions of the GPL or the LGPL are applicable instead
  * of those above. If you wish to allow use of your version of this file only
  * under the terms of either the GPL or the LGPL, and not to allow others to
  * use your version of this file under the terms of the MPL, indicate your
@@ -111,16 +112,48 @@ const PRUnichar kTitleTagsSeparatorChars
 #define BEST_FAVICON_FOR_REVHOST( __table_name ) \
   "(SELECT f.url FROM " __table_name " " \
    "JOIN moz_favicons f ON f.id = favicon_id " \
    "WHERE rev_host = IFNULL( " \
      "(SELECT rev_host FROM moz_places_temp WHERE id = b.fk), " \
      "(SELECT rev_host FROM moz_places WHERE id = b.fk)) " \
    "ORDER BY frecency DESC LIMIT 1) "
 
+// Define common pieces of various queries
+// XXX bug 412736
+// in the case of a frecency tie, break it with h.typed and h.visit_count
+// which is better than nothing.  but this is slow, so not doing it yet.
+
+// Try to reduce size of compound table since with partitioning this became
+// slower. Limiting moz_places with OFFSET+LIMIT will mostly help speed
+// of first chunks, that are usually most wanted.
+// Can do this only if there aren't additional conditions on final resultset.
+
+// Note: h.frecency is selected because we need it for ordering, but will
+// not be read later and we don't have an associated kAutoCompleteIndex_
+const nsCString kAutoCompleteBaseQuery = NS_LITERAL_CSTRING(
+      "SELECT h.url, h.title, f.url") + BOOK_TAG_SQL + NS_LITERAL_CSTRING(", "
+        "h.visit_count, 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 * FROM ( "
+        "SELECT h.url, h.title, f.url") + BOOK_TAG_SQL + NS_LITERAL_CSTRING(", "
+          "h.visit_count, 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 DESC LIMIT (?2 + ?3) "
+      ") "
+      "ORDER BY 8 DESC LIMIT ?2 OFFSET ?3");
+
 ////////////////////////////////////////////////////////////////////////////////
 //// nsNavHistoryAutoComplete Helper Functions
 
 /**
  * Returns true if the string starts with javascript:
  */
 inline PRBool
 StartsWithJS(const nsAString &aString)
@@ -257,87 +290,123 @@ nsNavHistory::InitAutoComplete()
     return NS_ERROR_OUT_OF_MEMORY;
 
   if (!mLivemarkFeedURIs.Init(128))
     return NS_ERROR_OUT_OF_MEMORY;
 
   return NS_OK;
 }
 
+// nsNavHistory::GetDBAutoCompleteHistoryQuery()
+//
+//    Returns the auto complete statement used when autocomplete results are
+//    restricted to history entries.
+mozIStorageStatement*
+nsNavHistory::GetDBAutoCompleteHistoryQuery()
+{
+  if (mDBAutoCompleteHistoryQuery)
+    return mDBAutoCompleteHistoryQuery;
+
+  nsCString AutoCompleteHistoryQuery = kAutoCompleteBaseQuery;
+  AutoCompleteHistoryQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
+                                            "AND h.visit_count > 0");
+  nsresult rv = mDBConn->CreateStatement(AutoCompleteHistoryQuery,
+    getter_AddRefs(mDBAutoCompleteHistoryQuery));
+  NS_ENSURE_SUCCESS(rv, nsnull);
+
+  return mDBAutoCompleteHistoryQuery;
+}
+
+// nsNavHistory::GetDBAutoCompleteStarQuery()
+//
+//    Returns the auto complete statement used when autocomplete results are
+//    restricted to bookmarked entries.
+mozIStorageStatement*
+nsNavHistory::GetDBAutoCompleteStarQuery()
+{
+  if (mDBAutoCompleteStarQuery)
+    return mDBAutoCompleteStarQuery;
+
+  nsCString AutoCompleteStarQuery = kAutoCompleteBaseQuery;
+  AutoCompleteStarQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
+                                         "AND bookmark IS NOT NULL");
+  nsresult rv = mDBConn->CreateStatement(AutoCompleteStarQuery,
+    getter_AddRefs(mDBAutoCompleteStarQuery));
+  NS_ENSURE_SUCCESS(rv, nsnull);
+
+  return mDBAutoCompleteStarQuery;
+}
+
+// nsNavHistory::GetDBAutoCompleteTagsQuery()
+//
+//    Returns the auto complete statement used when autocomplete results are
+//    restricted to tagged entries.
+mozIStorageStatement*
+nsNavHistory::GetDBAutoCompleteTagsQuery()
+{
+  if (mDBAutoCompleteTagsQuery)
+    return mDBAutoCompleteTagsQuery;
+
+  nsCString AutoCompleteTagsQuery = kAutoCompleteBaseQuery;
+  AutoCompleteTagsQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
+                                         "AND tags IS NOT NULL");
+  nsresult rv = mDBConn->CreateStatement(AutoCompleteTagsQuery,
+    getter_AddRefs(mDBAutoCompleteTagsQuery));
+  NS_ENSURE_SUCCESS(rv, nsnull);
+
+  return mDBAutoCompleteTagsQuery;
+}
+
+// nsNavHistory::GetDBFeedbackIncrease()
+//
+//    Returns the statement to update the input history that keeps track of
+//    selections in the locationbar.  Input history is used for adaptive query.
+mozIStorageStatement*
+nsNavHistory::GetDBFeedbackIncrease()
+{
+  if (mDBFeedbackIncrease)
+    return mDBFeedbackIncrease;
+
+  nsresult rv = mDBConn->CreateStatement(NS_LITERAL_CSTRING(
+    // Leverage the PRIMARY KEY (place_id, input) to insert/update entries
+    "INSERT OR REPLACE INTO moz_inputhistory "
+      // use_count will asymptotically approach the max of 10
+      "SELECT h.id, IFNULL(i.input, ?1), IFNULL(i.use_count, 0) * .9 + 1 "
+      "FROM moz_places_temp h "
+      "LEFT JOIN moz_inputhistory i ON i.place_id = h.id AND i.input = ?1 "
+      "WHERE url = ?2 "
+      "UNION ALL "
+      "SELECT h.id, IFNULL(i.input, ?1), IFNULL(i.use_count, 0) * .9 + 1 "
+      "FROM moz_places h "
+      "LEFT JOIN moz_inputhistory i ON i.place_id = h.id AND i.input = ?1 "
+      "WHERE url = ?2 "
+        "AND h.id NOT IN (SELECT id FROM moz_places_temp)"),
+    getter_AddRefs(mDBFeedbackIncrease));
+  NS_ENSURE_SUCCESS(rv, nsnull);
+
+  return mDBFeedbackIncrease;
+}
 
 // nsNavHistory::CreateAutoCompleteQueries
 //
 //    The auto complete queries we use depend on options, so we have them in
 //    a separate function so it can be re-created when the option changes.
-
+//    We are not lazy creating these queries because they will be most likely
+//    used on first search, and we don't want to lag on first autocomplete use.
 nsresult
 nsNavHistory::CreateAutoCompleteQueries()
 {
-  // Define common pieces of various queries
-  // XXX bug 412736
-  // in the case of a frecency tie, break it with h.typed and h.visit_count
-  // which is better than nothing.  but this is slow, so not doing it yet.
-
-  // Try to reduce size of compound table since with partitioning this became
-  // slower. Limiting moz_places with OFFSET+LIMIT will mostly help speed
-  // of first chunks, that are usually most wanted.
-  // Can do this only if there aren't additional conditions on final resultset.
-
-  // Note: h.frecency is selected because we need it for ordering, but will
-  // not be read later and we don't have an associated kAutoCompleteIndex_
-
-  nsCString sqlBase = NS_LITERAL_CSTRING(
-    "SELECT h.url, h.title, f.url") + BOOK_TAG_SQL + NS_LITERAL_CSTRING(", "
-      "h.visit_count, 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 * FROM ( "
-      "SELECT h.url, h.title, f.url") + BOOK_TAG_SQL + NS_LITERAL_CSTRING(", "
-        "h.visit_count, 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 DESC LIMIT (?2 + ?3) "
-    ") "
-    "ORDER BY 8 DESC LIMIT ?2 OFFSET ?3"); // ORDER BY frecency
-
-  nsCString AutoCompleteQuery = sqlBase;
+  nsCString AutoCompleteQuery = kAutoCompleteBaseQuery;
   AutoCompleteQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
                                      (mAutoCompleteOnlyTyped ?
                                         "AND h.typed = 1" : ""));
   nsresult rv = mDBConn->CreateStatement(AutoCompleteQuery,
                                 getter_AddRefs(mDBAutoCompleteQuery));
   NS_ENSURE_SUCCESS(rv, rv);
 
-  nsCString AutoCompleteHistoryQuery = sqlBase;
-  AutoCompleteHistoryQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
-                                            "AND h.visit_count > 0");
-  rv = mDBConn->CreateStatement(AutoCompleteHistoryQuery,
-                                getter_AddRefs(mDBAutoCompleteHistoryQuery));
-  NS_ENSURE_SUCCESS(rv, rv);
-
-  nsCString AutoCompleteStarQuery = sqlBase;
-  AutoCompleteStarQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
-                                         "AND bookmark IS NOT NULL");
-  rv = mDBConn->CreateStatement(AutoCompleteStarQuery,
-                                getter_AddRefs(mDBAutoCompleteStarQuery));
-  NS_ENSURE_SUCCESS(rv, rv);
-
-  nsCString AutoCompleteTagsQuery = sqlBase;
-  AutoCompleteTagsQuery.ReplaceSubstring("{ADDITIONAL_CONDITIONS}",
-                                         "AND tags IS NOT NULL");
-  rv = mDBConn->CreateStatement(AutoCompleteTagsQuery,
-                                getter_AddRefs(mDBAutoCompleteTagsQuery));
-  NS_ENSURE_SUCCESS(rv, rv);
-
   // In this query we are taking BOOK_TAG_SQL only for h.id because it
   // uses data from moz_bookmarks table and we sync tables on bookmark insert.
   // So, most likely, h.id will always be populated when we have any bookmark.
   // We still need to join on moz_places_temp for other data (eg. title).
   nsCString sql = NS_LITERAL_CSTRING(
     "SELECT IFNULL(h_t.url, h.url), IFNULL(h_t.title, h.title), f.url ") +
       BOOK_TAG_SQL + NS_LITERAL_CSTRING(", "
       "IFNULL(h_t.visit_count, h.visit_count), rank "
@@ -370,33 +439,16 @@ nsNavHistory::CreateAutoCompleteQueries(
     "LEFT JOIN moz_places AS h ON h.url = search_url "
     "LEFT JOIN moz_places_temp AS h_t ON h_t.url = search_url "
     "LEFT JOIN moz_favicons f ON f.id = IFNULL(h_t.favicon_id, h.favicon_id) "
     "WHERE LOWER(k.keyword) = LOWER(?1) "
     "ORDER BY IFNULL(h_t.frecency, h.frecency) DESC");
   rv = mDBConn->CreateStatement(sql, getter_AddRefs(mDBKeywordQuery));
   NS_ENSURE_SUCCESS(rv, rv);
 
-  sql = NS_LITERAL_CSTRING(
-    // Leverage the PRIMARY KEY (place_id, input) to insert/update entries
-    "INSERT OR REPLACE INTO moz_inputhistory "
-      // use_count will asymptotically approach the max of 10    
-      "SELECT h.id, IFNULL(i.input, ?1), IFNULL(i.use_count, 0) * .9 + 1 "
-      "FROM moz_places_temp h "
-      "LEFT JOIN moz_inputhistory i ON i.place_id = h.id AND i.input = ?1 "
-      "WHERE url = ?2 "
-      "UNION ALL "
-      "SELECT h.id, IFNULL(i.input, ?1), IFNULL(i.use_count, 0) * .9 + 1 "
-      "FROM moz_places h "
-      "LEFT JOIN moz_inputhistory i ON i.place_id = h.id AND i.input = ?1 "
-      "WHERE url = ?2 "
-        "AND h.id NOT IN (SELECT id FROM moz_places_temp)");
-  rv = mDBConn->CreateStatement(sql, getter_AddRefs(mDBFeedbackIncrease));
-  NS_ENSURE_SUCCESS(rv, rv);
-
   return NS_OK;
 }
 
 // nsNavHistory::StartAutoCompleteTimer
 
 nsresult
 nsNavHistory::StartAutoCompleteTimer(PRUint32 aMilliseconds)
 {
@@ -756,20 +808,20 @@ nsNavHistory::ProcessTokensForSpecialSea
 
     // Remove the token if it's special search token
     if (needToRemove)
       (void)mCurrentSearchTokens.RemoveStringAt(i);
   }
 
   // We can use optimized queries for restricts, so check for the most
   // restrictive query first
-  mDBCurrentQuery = mRestrictTag ? mDBAutoCompleteTagsQuery :
-    mRestrictBookmark ? mDBAutoCompleteStarQuery :
-    mRestrictHistory ? mDBAutoCompleteHistoryQuery :
-    mDBAutoCompleteQuery;
+  mDBCurrentQuery = mRestrictTag ? GetDBAutoCompleteTagsQuery() :
+    mRestrictBookmark ? GetDBAutoCompleteStarQuery() :
+    mRestrictHistory ? GetDBAutoCompleteHistoryQuery() :
+    static_cast<mozIStorageStatement *>(mDBAutoCompleteQuery);
 }
 
 nsresult
 nsNavHistory::AutoCompleteKeywordSearch()
 {
   mozStorageStatementScoper scope(mDBKeywordQuery);
 
   // Get the keyword parameters to replace the %s in the keyword search
@@ -1062,31 +1114,32 @@ nsNavHistory::OnValueRemoved(nsIAutoComp
 
   return NS_OK;
 }
 
 nsresult
 nsNavHistory::AutoCompleteFeedback(PRInt32 aIndex,
                                    nsIAutoCompleteController *aController)
 {
-  mozStorageStatementScoper scope(mDBFeedbackIncrease);
+  mozIStorageStatement* statement = GetDBFeedbackIncrease();
+  mozStorageStatementScoper scope(statement);
 
   nsAutoString input;
   nsresult rv = aController->GetSearchString(input);
   NS_ENSURE_SUCCESS(rv, rv);
-  rv = mDBFeedbackIncrease->BindStringParameter(0, input);
+  rv = statement->BindStringParameter(0, input);
   NS_ENSURE_SUCCESS(rv, rv);
 
   nsAutoString url;
   rv = aController->GetValueAt(aIndex, url);
   NS_ENSURE_SUCCESS(rv, rv);
-  rv = mDBFeedbackIncrease->BindStringParameter(1, url);
+  rv = statement->BindStringParameter(1, url);
   NS_ENSURE_SUCCESS(rv, rv);
 
-  rv = mDBFeedbackIncrease->Execute();
+  rv = statement->Execute();
   NS_ENSURE_SUCCESS(rv, rv);
 
   return NS_OK;
 }
 
 nsString
 nsNavHistory::FixupURIText(const nsAString &aURIText)
 {