Bug 692493 - Add maintenance for visit_count and last_visit_date.
authorMarco Bonardo <mbonardo@mozilla.com>
Mon, 10 Oct 2011 12:15:27 +0200
changeset 78484 339749acfdbc23bfd3971abbe37e538baf4bdaf7
parent 78483 3176f14f0483f7af3dfd272e3bfc73c6432f1847
child 78485 efed4eae9cd24938b9dc82683766c9dc0b480294
push id21304
push usermbrubeck@mozilla.com
push dateMon, 10 Oct 2011 18:03:49 +0000
treeherdermozilla-central@3a8a23bd7708 [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
bugs692493
milestone10.0a1
first release with
nightly linux32
nightly linux64
nightly mac
nightly win32
nightly win64
last release without
nightly linux32
nightly linux64
nightly mac
nightly win32
nightly win64
Bug 692493 - Add maintenance for visit_count and last_visit_date. r=dietrich
toolkit/components/places/PlacesDBUtils.jsm
toolkit/components/places/tests/unit/test_preventive_maintenance.js
--- a/toolkit/components/places/PlacesDBUtils.jsm
+++ b/toolkit/components/places/PlacesDBUtils.jsm
@@ -700,34 +700,31 @@ let PlacesDBUtils = {
       "UPDATE moz_places SET favicon_id = NULL WHERE id IN ( " +
         "SELECT id FROM moz_places h " +
         "WHERE favicon_id NOT NULL " +
           "AND NOT EXISTS " +
             "(SELECT id FROM moz_favicons WHERE id = h.favicon_id LIMIT 1) " +
       ")");
     cleanupStatements.push(fixInvalidFaviconIds);
 
-/* XXX needs test
-    // L.2 recalculate visit_count
-    let detectWrongCountPlaces = DBConn.createStatement(
-      "SELECT id FROM moz_places h " +
-      "WHERE h.visit_count <> " +
-          "(SELECT count(*) FROM moz_historyvisits " +
-            "WHERE place_id = h.id AND visit_type NOT IN (0,4,7,8))");
-    while (detectWrongCountPlaces.executeStep()) {
-      let placeId = detectWrongCountPlaces.getInt64(0);
-      let fixCountForPlace = DBConn.createStatement(
-        "UPDATE moz_places SET visit_count = ( " +
-          "(SELECT count(*) FROM moz_historyvisits " +
-            "WHERE place_id = :place_id AND visit_type NOT IN (0,4,7,8)) + "
-        ") WHERE id = :place_id");
-      fixCountForPlace.params["place_id"] = placeId;
-      cleanupStatements.push(fixCountForPlace);
-    }
-*/
+    // L.2 recalculate visit_count and last_visit_date
+    let fixVisitStats = DBConn.createAsyncStatement(
+      "UPDATE moz_places " +
+      "SET visit_count = (SELECT count(*) FROM moz_historyvisits " +
+                         "WHERE place_id = moz_places.id AND visit_type NOT IN (0,4,7,8)), " +
+          "last_visit_date = (SELECT MAX(visit_date) FROM moz_historyvisits " +
+                             "WHERE place_id = moz_places.id) " +
+      "WHERE id IN ( " +
+        "SELECT h.id FROM moz_places h " +
+        "WHERE visit_count <> (SELECT count(*) FROM moz_historyvisits v " +
+                              "WHERE v.place_id = h.id AND visit_type NOT IN (0,4,7,8)) " +
+           "OR last_visit_date <> (SELECT MAX(visit_date) FROM moz_historyvisits v " +
+                                  "WHERE v.place_id = h.id) " +
+      ")");
+    cleanupStatements.push(fixVisitStats);
 
     // MAINTENANCE STATEMENTS SHOULD GO ABOVE THIS POINT!
 
     return cleanupStatements;
   },
 
   /**
    * Tries to vacuum the database.
--- a/toolkit/components/places/tests/unit/test_preventive_maintenance.js
+++ b/toolkit/components/places/tests/unit/test_preventive_maintenance.js
@@ -1160,27 +1160,89 @@ tests.push({
     stmt.reset();
     stmt.params["place_id"] = this._invalidIconPlaceId;
     do_check_true(stmt.executeStep());
     stmt.finalize();
   }
 });
 
 //------------------------------------------------------------------------------
-//XXX TODO
+
 tests.push({
   name: "L.2",
-  desc: "Recalculate visit_count",
+  desc: "Recalculate visit_count and last_visit_date",
 
   setup: function() {
+    function setVisitCount(aURL, aValue) {
+      let stmt = mDBConn.createStatement(
+        "UPDATE moz_places SET visit_count = :count WHERE url = :url"
+      );
+      stmt.params.count = aValue;
+      stmt.params.url = aURL;
+      stmt.execute();
+      stmt.finalize();
+    }
+    function setLastVisitDate(aURL, aValue) {
+      let stmt = mDBConn.createStatement(
+        "UPDATE moz_places SET last_visit_date = :date WHERE url = :url"
+      );
+      stmt.params.date = aValue;
+      stmt.params.url = aURL;
+      stmt.execute();
+      stmt.finalize();
+    }
 
+    let now = Date.now() * 1000;
+    // Add a page with 1 visit.
+    let url = "http://1.moz.org/";
+    hs.addVisit(NetUtil.newURI(url), now++, null, hs.TRANSITION_TYPED, false, 0);
+    // Add a page with 1 visit and set wrong visit_count.
+    url = "http://2.moz.org/";
+    hs.addVisit(NetUtil.newURI(url), now++, null, hs.TRANSITION_TYPED, false, 0);
+    setVisitCount(url, 10);
+    // Add a page with 1 visit and set wrong last_visit_date.
+    url = "http://3.moz.org/";
+    hs.addVisit(NetUtil.newURI(url), now++, null, hs.TRANSITION_TYPED, false, 0);
+    setLastVisitDate(url, now++);
+    // Add a page with 1 visit and set wrong stats.
+    url = "http://4.moz.org/";
+    hs.addVisit(NetUtil.newURI(url), now++, null, hs.TRANSITION_TYPED, false, 0);
+    setVisitCount(url, 10);
+    setLastVisitDate(url, now++);
+
+    // Add a page without visits.
+    let url = "http://5.moz.org/";
+    addPlace(url);
+    // Add a page without visits and set wrong visit_count.
+    url = "http://6.moz.org/";
+    addPlace(url);
+    setVisitCount(url, 10);
+    // Add a page without visits and set wrong last_visit_date.
+    url = "http://7.moz.org/";
+    addPlace(url);
+    setLastVisitDate(url, now++);
+    // Add a page without visits and set wrong stats.
+    url = "http://8.moz.org/";
+    addPlace(url);
+    setVisitCount(url, 10);
+    setLastVisitDate(url, now++);
   },
 
   check: function() {
-
+    let stmt = mDBConn.createStatement(
+      "SELECT h.id FROM moz_places h " +
+      "JOIN moz_historyvisits v ON v.place_id = h.id AND visit_type NOT IN (0,4,7,8) " +
+      "GROUP BY h.id HAVING h.visit_count <> count(*) " +
+      "UNION ALL " +
+      "SELECT h.id FROM moz_places h " +
+      "JOIN moz_historyvisits v ON v.place_id = h.id " +
+      "GROUP BY h.id HAVING h.last_visit_date <> MAX(v.visit_date) "
+    );
+    do_check_false(stmt.executeStep());
+    stmt.finalize();
   }
 });
 
 //------------------------------------------------------------------------------
 
 tests.push({
   name: "Z",
   desc: "Sanity: Preventive maintenance does not touch valid items",