Bug 449086 - Create temporary tables for frequently used places tables
authorShawn Wilsher <sdwilsh@shawnwilsher.com>
Fri, 17 Oct 2008 06:12:50 -0400
changeset 20568 4939cc030c3b7236aa5fe7c3a1856668e709910c
parent 20567 4ffb7e96343f32342434f3bc5f2cf78a7e91cab6
child 20569 ef5b27855c762859872a8d84bb24bd2d3272324f
push id2972
push usertmielczarek@mozilla.com
push dateFri, 17 Oct 2008 10:35:06 +0000
treeherdermozilla-central@f31b66fd4192 [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
bugs449086
milestone1.9.1b2pre
Bug 449086 - Create temporary tables for frequently used places tables This changeset creates two temporary tables that will be used when inserting data into moz_places and moz_historyvisits. This will reduce the number of writes as well as the number of fsyncs that happen. r=dietrich
toolkit/components/places/src/nsNavHistory.cpp
toolkit/components/places/src/nsNavHistory.h
toolkit/components/places/src/nsPlacesTables.h
toolkit/components/places/src/nsPlacesTriggers.h
--- a/toolkit/components/places/src/nsNavHistory.cpp
+++ b/toolkit/components/places/src/nsNavHistory.cpp
@@ -878,16 +878,20 @@ nsNavHistory::InitDB(PRInt16 *aMadeChang
 
   rv = transaction.Commit();
   NS_ENSURE_SUCCESS(rv, rv);
 
   // --- PUT SCHEMA-MODIFYING THINGS (like create table) ABOVE THIS LINE ---
 
   // DO NOT PUT ANY SCHEMA-MODIFYING THINGS HERE
 
+  rv = InitTempTables();
+  NS_ENSURE_SUCCESS(rv, rv);
+  rv = InitViews();
+  NS_ENSURE_SUCCESS(rv, rv);
   rv = InitFunctions();
   NS_ENSURE_SUCCESS(rv, rv);
   rv = InitStatements();
   NS_ENSURE_SUCCESS(rv, rv);
 
   return NS_OK;
 }
 
@@ -957,16 +961,98 @@ NS_IMETHODIMP mozStorageFunctionGetUnrev
   } else {
     result->SetAsAString(NS_LITERAL_STRING(""));
   }
   NS_ADDREF(*_retval = result);
   return NS_OK;
 }
 
 nsresult
+nsNavHistory::InitTempTables()
+{
+  nsresult rv;
+
+  // moz_places_temp
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_PLACES_TEMP);
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE UNIQUE INDEX moz_places_temp_url_uniqueindex ON moz_places_temp (url)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_places_temp_faviconindex ON moz_places_temp (favicon_id)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_places_temp_hostindex ON moz_places_temp (rev_host)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_places_temp_visitcount ON moz_places_temp (visit_count)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_places_temp_frecencyindex ON moz_places_temp (frecency)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+
+  // moz_historyvisits_temp
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_HISTORYVISITS_TEMP);
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_historyvisits_temp_placedateindex "
+    "ON moz_historyvisits_temp (place_id, visit_date)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_historyvisits_temp_fromindex "
+    "ON moz_historyvisits_temp (from_visit)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(NS_LITERAL_CSTRING(
+    "CREATE INDEX moz_historyvisits_temp_dateindex "
+    "ON moz_historyvisits_temp (visit_date)"));
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  return NS_OK;
+}
+
+nsresult
+nsNavHistory::InitViews()
+{
+  nsresult rv;
+
+  // moz_places_view
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_PLACES_VIEW);
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_PLACES_VIEW_INSERT_TRIGGER);
+  NS_ENSURE_SUCCESS(rv, rv);
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_PLACES_VIEW_DELETE_TRIGGER);
+  NS_ENSURE_SUCCESS(rv, rv);
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_PLACES_VIEW_UPDATE_TRIGGER);
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  // moz_historyvisits_view
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_MOZ_HISTORYVISITS_VIEW);
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_HISTORYVISITS_VIEW_INSERT_TRIGGER);
+  NS_ENSURE_SUCCESS(rv, rv);
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_HISTORYVISITS_VIEW_DELETE_TRIGGER);
+  NS_ENSURE_SUCCESS(rv, rv);
+  rv = mDBConn->ExecuteSimpleSQL(CREATE_HISTORYVISITS_VIEW_UPDATE_TRIGGER);
+  NS_ENSURE_SUCCESS(rv, rv);
+
+  return NS_OK;
+}
+
+nsresult
 nsNavHistory::InitFunctions()
 {
   nsresult rv;
 
   rv = mDBConn->CreateFunction(
       NS_LITERAL_CSTRING("get_unreversed_host"), 1, 
       new mozStorageFunctionGetUnreversedHost);
   NS_ENSURE_SUCCESS(rv, rv);
--- a/toolkit/components/places/src/nsNavHistory.h
+++ b/toolkit/components/places/src/nsNavHistory.h
@@ -458,16 +458,18 @@ protected:
    *        DB_MIGRATION_NONE
    *          No migration occurred.
    *        DB_MIGRATION_CREATED
    *          The database did not exist in the past, and was created.
    *        DB_MIGRATION_UPDATED
    *          The database was migrated to a new version.
    */
   nsresult InitDB(PRInt16 *aMadeChanges);
+  nsresult InitTempTables();
+  nsresult InitViews();
   nsresult InitFunctions();
   nsresult InitStatements();
   nsresult ForceMigrateBookmarksDB(mozIStorageConnection *aDBConn);
   nsresult MigrateV3Up(mozIStorageConnection *aDBConn);
   nsresult MigrateV6Up(mozIStorageConnection *aDBConn);
   nsresult MigrateV7Up(mozIStorageConnection *aDBConn);
   nsresult EnsureCurrentSchema(mozIStorageConnection* aDBConn, PRBool *aMadeChanges);
   nsresult CleanUpOnQuit();
--- a/toolkit/components/places/src/nsPlacesTables.h
+++ b/toolkit/components/places/src/nsPlacesTables.h
@@ -34,40 +34,60 @@
  * the provisions above, a recipient may use your version of this file under
  * the terms of any one of the MPL, the GPL or the LGPL.
  *
  * ***** END LICENSE BLOCK ***** */
 
 #ifndef __nsPlacesTables_h__
 #define __nsPlacesTables_h__
 
-#define CREATE_MOZ_PLACES NS_LITERAL_CSTRING( \
-  "CREATE TABLE moz_places ( " \
+#define CREATE_MOZ_PLACES_BASE(__name, __temporary) NS_LITERAL_CSTRING( \
+  "CREATE " __temporary " TABLE " __name " ( " \
     "  id INTEGER PRIMARY KEY" \
     ", url LONGVARCHAR" \
     ", title LONGVARCHAR" \
     ", rev_host LONGVARCHAR" \
     ", visit_count INTEGER DEFAULT 0" \
     ", hidden INTEGER DEFAULT 0 NOT NULL" \
     ", typed INTEGER DEFAULT 0 NOT NULL" \
     ", favicon_id INTEGER" \
     ", frecency INTEGER DEFAULT -1 NOT NULL" \
   ")" \
 )
+#define CREATE_MOZ_PLACES CREATE_MOZ_PLACES_BASE("moz_places", "")
+#define CREATE_MOZ_PLACES_TEMP CREATE_MOZ_PLACES_BASE("moz_places_temp", "TEMP")
+#define CREATE_MOZ_PLACES_VIEW NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY VIEW moz_places_view AS " \
+  "SELECT * FROM moz_places_temp " \
+  "UNION ALL " \
+  "SELECT * FROM moz_places " \
+  "WHERE id NOT IN (SELECT id FROM moz_places_temp) " \
+)
 
-#define CREATE_MOZ_HISTORYVISITS NS_LITERAL_CSTRING( \
-  "CREATE TABLE moz_historyvisits (" \
+#define CREATE_MOZ_HISTORYVISITS_BASE(__name, __temporary) NS_LITERAL_CSTRING( \
+  "CREATE " __temporary " TABLE " __name " (" \
     "  id INTEGER PRIMARY KEY" \
     ", from_visit INTEGER" \
     ", place_id INTEGER" \
     ", visit_date INTEGER" \
     ", visit_type INTEGER" \
     ", session INTEGER" \
   ")" \
 )
+#define CREATE_MOZ_HISTORYVISITS \
+  CREATE_MOZ_HISTORYVISITS_BASE("moz_historyvisits", "")
+#define CREATE_MOZ_HISTORYVISITS_TEMP \
+  CREATE_MOZ_HISTORYVISITS_BASE("moz_historyvisits_temp", "TEMP")
+#define CREATE_MOZ_HISTORYVISITS_VIEW NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY VIEW moz_historyvisits_view AS " \
+  "SELECT * FROM moz_historyvisits_temp " \
+  "UNION ALL " \
+  "SELECT * FROM moz_historyvisits " \
+  "WHERE id NOT IN (SELECT id FROM moz_historyvisits_temp) " \
+)
 
 #define CREATE_MOZ_INPUTHISTORY NS_LITERAL_CSTRING( \
   "CREATE TABLE moz_inputhistory (" \
     "  place_id INTEGER NOT NULL" \
     ", input LONGVARCHAR NOT NULL" \
     ", use_count INTEGER" \
     ", PRIMARY KEY (place_id, input)" \
   ")" \
--- a/toolkit/components/places/src/nsPlacesTriggers.h
+++ b/toolkit/components/places/src/nsPlacesTriggers.h
@@ -88,9 +88,150 @@
       "FROM moz_bookmarks " \
       "WHERE keyword_id = OLD.keyword_id " \
       "AND id <> OLD.id " \
       "LIMIT 1 " \
     ");" \
   "END" \
 )
 
+/**
+ * This trigger allows for an insertion into moz_places_view.  It enters the new
+ * data into the temporary table, ensuring that the new id is one greater than
+ * the largest id value found.
+ */
+#define CREATE_PLACES_VIEW_INSERT_TRIGGER NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY TRIGGER moz_places_view_insert_trigger " \
+  "INSTEAD OF INSERT " \
+  "ON moz_places_view " \
+  "BEGIN " \
+    "INSERT INTO moz_places_temp ( " \
+      "id, url, title, rev_host, visit_count, hidden, typed, favicon_id, " \
+      "frecency " \
+    ") " \
+    "VALUES (MAX((SELECT IFNULL(MAX(id), 0) FROM moz_places_temp), " \
+                "(SELECT IFNULL(MAX(id), 0) FROM moz_places)) + 1, " \
+            "NEW.url, NEW.title, NEW.rev_host, " \
+            "IFNULL(NEW.visit_count, 0), " /* enforce having a value */ \
+            "NEW.hidden, NEW.typed, NEW.favicon_id, NEW.frecency);" \
+  "END" \
+)
+
+/**
+ * This trigger allows for the deletion of a record in moz_places_view.  It
+ * removes any entry in the temporary table, and any entry in the permanent
+ * table as well.
+ */
+#define CREATE_PLACES_VIEW_DELETE_TRIGGER NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY TRIGGER moz_places_view_delete_trigger " \
+  "INSTEAD OF DELETE " \
+  "ON moz_places_view " \
+  "BEGIN " \
+    "DELETE FROM moz_places_temp " \
+    "WHERE id = OLD.id; " \
+    "DELETE FROM moz_places " \
+    "WHERE id = OLD.id; " \
+  "END" \
+)
+
+/**
+ * This trigger allows for updates to a record in moz_places_view.  It first
+ * copies the row from the permanent table over to the temp table if it does not
+ * exist in the temporary table.  Then, it will update the temporary table with
+ * the new data.
+ */
+#define CREATE_PLACES_VIEW_UPDATE_TRIGGER NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY TRIGGER moz_places_view_update_trigger " \
+  "INSTEAD OF UPDATE " \
+  "ON moz_places_view " \
+  "BEGIN " \
+    "INSERT INTO moz_places_temp " \
+    "SELECT * " \
+    "FROM moz_places " \
+    "WHERE id = OLD.id " \
+    "AND id NOT IN (SELECT id FROM moz_places_temp); " \
+    "UPDATE moz_places_temp " \
+    "SET url = IFNULL(NEW.url, OLD.url), " \
+        "title = IFNULL(NEW.title, OLD.title), " \
+        "rev_host = IFNULL(NEW.rev_host, OLD.rev_host), " \
+        "visit_count = IFNULL(NEW.visit_count, OLD.visit_count), " \
+        "hidden = IFNULL(NEW.hidden, OLD.hidden), " \
+        "typed = IFNULL(NEW.typed, OLD.typed), " \
+        "favicon_id = IFNULL(NEW.favicon_id, OLD.favicon_id), " \
+        "frecency = IFNULL(NEW.frecency, OLD.frecency) " \
+    "WHERE id = OLD.id; " \
+  "END" \
+)
+
+/**
+ * This trigger allows for an insertion into  moz_historyvisits_view.  It enters
+ * the new data into the temporary table, ensuring that the new id is one
+ * greater than the largest id value found.  It then updates moz_places_view
+ * with the new visit count.
+ */
+#define CREATE_HISTORYVISITS_VIEW_INSERT_TRIGGER NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY TRIGGER moz_historyvisits_view_insert_trigger " \
+  "INSTEAD OF INSERT " \
+  "ON moz_historyvisits_view " \
+  "BEGIN " \
+    "INSERT INTO moz_historyvisits_temp ( " \
+      "id, from_visit, place_id, visit_date, visit_type, session " \
+    ") " \
+    "VALUES (MAX((SELECT IFNULL(MAX(id), 0) FROM moz_historyvisits_temp), " \
+                "(SELECT IFNULL(MAX(id), 0) FROM moz_historyvisits)) + 1, " \
+            "NEW.from_visit, NEW.place_id, NEW.visit_date, NEW.visit_type, " \
+            "NEW.session); " \
+    "UPDATE moz_places_view " \
+    "SET visit_count = visit_count + 1 " \
+    "WHERE id = NEW.place_id " \
+    "AND NEW.visit_type NOT IN (0, 4, 7); " /* invalid, EMBED, DOWNLOAD */ \
+  "END" \
+)
+
+/**
+ * This trigger allows for the deletion of a record in moz_historyvisits_view.
+ * It removes any entry in the temporary table, and removes any entry in the
+ * permanent table as well.  It then updates moz_places_view with the new visit
+ * count.
+ */
+#define CREATE_HISTORYVISITS_VIEW_DELETE_TRIGGER NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY TRIGGER moz_historyvisits_view_delete_trigger " \
+  "INSTEAD OF DELETE " \
+  "ON moz_historyvisits_view " \
+  "BEGIN " \
+    "DELETE FROM moz_historyvisits_temp " \
+    "WHERE id = OLD.id; " \
+    "DELETE FROM moz_historyvisits " \
+    "WHERE id = OLD.id; " \
+    "UPDATE moz_places_view " \
+    "SET visit_count = visit_count - 1 " \
+    "WHERE moz_places_view.id = OLD.place_id " \
+    "AND OLD.visit_type NOT IN (0, 4, 7); " /* invalid, EMBED, DOWNLOAD */ \
+  "END" \
+)
+
+/**
+ * This trigger allows for updates to a record in moz_historyvisits_view.  It
+ * first copies the row from the permanent table over to the temp table if it
+ * does not exist in the temporary table.  Then it will update the temporary
+ * table with the new data.
+ */
+#define CREATE_HISTORYVISITS_VIEW_UPDATE_TRIGGER NS_LITERAL_CSTRING( \
+  "CREATE TEMPORARY TRIGGER moz_historyvisits_view_update_trigger " \
+  "INSTEAD OF UPDATE " \
+  "ON moz_historyvisits_view " \
+  "BEGIN " \
+    "INSERT INTO moz_historyvisits_temp " \
+    "SELECT * " \
+    "FROM moz_historyvisits " \
+    "WHERE id = OLD.id " \
+    "AND id NOT IN (SELECT id FROM moz_historyvisits_temp); " \
+    "UPDATE moz_historyvisits_temp " \
+    "SET from_visit = IFNULL(NEW.from_visit, OLD.from_visit), " \
+        "place_id = IFNULL(NEW.place_id, OLD.place_id), " \
+        "visit_date = IFNULL(NEW.visit_date, OLD.visit_date), " \
+        "visit_type = IFNULL(NEW.visit_type, OLD.visit_type), " \
+        "session = IFNULL(NEW.session, OLD.session) " \
+    "WHERE id = OLD.id; " \
+  "END" \
+)
+
 #endif // __nsPlacesTriggers_h__