Bug 626341 - Fix bogus bookmarks position values with PlacesDBUtils.
authorMarco Bonardo <mbonardo@mozilla.com>
Thu, 17 Feb 2011 13:39:54 +0100
changeset 62859 51b61720bc3507de21b1408ffd8dfeb15514d3a0
parent 62858 966e0dacbdb861732a7deb40b615c0b9f8537058
child 62860 e4b9604fdba59053c877aaf9e5c93d12dd89dd27
push id1
push userroot
push dateTue, 10 Dec 2013 15:46:25 +0000
bugs626341
milestone2.0b12pre
Bug 626341 - Fix bogus bookmarks position values with PlacesDBUtils. r+a=dietrich
toolkit/components/places/src/PlacesDBUtils.jsm
toolkit/components/places/tests/unit/test_preventive_maintenance.js
--- a/toolkit/components/places/src/PlacesDBUtils.jsm
+++ b/toolkit/components/places/src/PlacesDBUtils.jsm
@@ -298,360 +298,386 @@ let PlacesDBUtils = {
   },
 
   _getBoundCoherenceStatements: function PDBU__getBoundCoherenceStatements()
   {
     let cleanupStatements = [];
 
     // MOZ_ANNO_ATTRIBUTES
     // A.1 remove unused attributes
-    let deleteUnusedAnnoAttributes = DBConn.createStatement(
+    let deleteUnusedAnnoAttributes = DBConn.createAsyncStatement(
       "DELETE FROM moz_anno_attributes WHERE id IN ( " +
         "SELECT id FROM moz_anno_attributes n " +
         "WHERE NOT EXISTS " +
             "(SELECT id FROM moz_annos WHERE anno_attribute_id = n.id LIMIT 1) " +
           "AND NOT EXISTS " +
             "(SELECT id FROM moz_items_annos WHERE anno_attribute_id = n.id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteUnusedAnnoAttributes);
 
     // MOZ_ANNOS
     // B.1 remove annos with an invalid attribute
-    let deleteInvalidAttributeAnnos = DBConn.createStatement(
+    let deleteInvalidAttributeAnnos = DBConn.createAsyncStatement(
       "DELETE FROM moz_annos WHERE id IN ( " +
         "SELECT id FROM moz_annos a " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_anno_attributes " +
             "WHERE id = a.anno_attribute_id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteInvalidAttributeAnnos);
 
     // B.2 remove orphan annos
-    let deleteOrphanAnnos = DBConn.createStatement(
+    let deleteOrphanAnnos = DBConn.createAsyncStatement(
       "DELETE FROM moz_annos WHERE id IN ( " +
         "SELECT id FROM moz_annos a " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_places WHERE id = a.place_id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteOrphanAnnos);
 
     // MOZ_BOOKMARKS_ROOTS
     // C.1 fix missing Places root
     //     Bug 477739 shows a case where the root could be wrongly removed
     //     due to an endianness issue.  We try to fix broken roots here.
     let selectPlacesRoot = DBConn.createStatement(
       "SELECT id FROM moz_bookmarks WHERE id = :places_root");
     selectPlacesRoot.params["places_root"] = PlacesUtils.placesRootId;
     if (!selectPlacesRoot.executeStep()) {
       // We are missing the root, try to recreate it.
-      let createPlacesRoot = DBConn.createStatement(
+      let createPlacesRoot = DBConn.createAsyncStatement(
         "INSERT INTO moz_bookmarks (id, type, fk, parent, position, title, "
       +                            "guid) "
       + "VALUES (:places_root, 2, NULL, 0, 0, :title, GENERATE_GUID())");
       createPlacesRoot.params["places_root"] = PlacesUtils.placesRootId;
       createPlacesRoot.params["title"] = "";
       cleanupStatements.push(createPlacesRoot);
 
       // Now ensure that other roots are children of Places root.
-      let fixPlacesRootChildren = DBConn.createStatement(
+      let fixPlacesRootChildren = DBConn.createAsyncStatement(
         "UPDATE moz_bookmarks SET parent = :places_root WHERE id IN " +
           "(SELECT folder_id FROM moz_bookmarks_roots " +
             "WHERE folder_id <> :places_root)");
       fixPlacesRootChildren.params["places_root"] = PlacesUtils.placesRootId;
       cleanupStatements.push(fixPlacesRootChildren);
     }
     selectPlacesRoot.finalize();
 
     // C.2 fix roots titles
     //     some alpha version has wrong roots title, and this also fixes them if
     //     locale has changed.
     let updateRootTitleSql = "UPDATE moz_bookmarks SET title = :title " +
                              "WHERE id = :root_id AND title <> :title";
     // root
-    let fixPlacesRootTitle = DBConn.createStatement(updateRootTitleSql);
+    let fixPlacesRootTitle = DBConn.createAsyncStatement(updateRootTitleSql);
     fixPlacesRootTitle.params["root_id"] = PlacesUtils.placesRootId;
     fixPlacesRootTitle.params["title"] = "";
     cleanupStatements.push(fixPlacesRootTitle);
     // bookmarks menu
-    let fixBookmarksMenuTitle = DBConn.createStatement(updateRootTitleSql);
+    let fixBookmarksMenuTitle = DBConn.createAsyncStatement(updateRootTitleSql);
     fixBookmarksMenuTitle.params["root_id"] = PlacesUtils.bookmarksMenuFolderId;
     fixBookmarksMenuTitle.params["title"] =
       PlacesUtils.getString("BookmarksMenuFolderTitle");
     cleanupStatements.push(fixBookmarksMenuTitle);
     // bookmarks toolbar
-    let fixBookmarksToolbarTitle = DBConn.createStatement(updateRootTitleSql);
+    let fixBookmarksToolbarTitle = DBConn.createAsyncStatement(updateRootTitleSql);
     fixBookmarksToolbarTitle.params["root_id"] = PlacesUtils.toolbarFolderId;
     fixBookmarksToolbarTitle.params["title"] =
       PlacesUtils.getString("BookmarksToolbarFolderTitle");
     cleanupStatements.push(fixBookmarksToolbarTitle);
     // unsorted bookmarks
-    let fixUnsortedBookmarksTitle = DBConn.createStatement(updateRootTitleSql);
+    let fixUnsortedBookmarksTitle = DBConn.createAsyncStatement(updateRootTitleSql);
     fixUnsortedBookmarksTitle.params["root_id"] = PlacesUtils.unfiledBookmarksFolderId;
     fixUnsortedBookmarksTitle.params["title"] =
       PlacesUtils.getString("UnsortedBookmarksFolderTitle");
     cleanupStatements.push(fixUnsortedBookmarksTitle);
     // tags
-    let fixTagsRootTitle = DBConn.createStatement(updateRootTitleSql);
+    let fixTagsRootTitle = DBConn.createAsyncStatement(updateRootTitleSql);
     fixTagsRootTitle.params["root_id"] = PlacesUtils.tagsFolderId;
     fixTagsRootTitle.params["title"] =
       PlacesUtils.getString("TagsFolderTitle");
     cleanupStatements.push(fixTagsRootTitle);
 
     // MOZ_BOOKMARKS
     // D.1 remove items without a valid place
     // if fk IS NULL we fix them in D.7
-    let deleteNoPlaceItems = DBConn.createStatement(
+    let deleteNoPlaceItems = DBConn.createAsyncStatement(
       "DELETE FROM moz_bookmarks WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN (" +
         "SELECT b.id FROM moz_bookmarks b " +
         "WHERE fk NOT NULL AND b.type = :bookmark_type " +
           "AND NOT EXISTS (SELECT url FROM moz_places WHERE id = b.fk LIMIT 1) " +
       ")");
     deleteNoPlaceItems.params["bookmark_type"] = PlacesUtils.bookmarks.TYPE_BOOKMARK;
     cleanupStatements.push(deleteNoPlaceItems);
 
     // D.2 remove items that are not uri bookmarks from tag containers
-    let deleteBogusTagChildren = DBConn.createStatement(
+    let deleteBogusTagChildren = DBConn.createAsyncStatement(
       "DELETE FROM moz_bookmarks WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN (" +
         "SELECT b.id FROM moz_bookmarks b " +
         "WHERE b.parent IN " +
           "(SELECT id FROM moz_bookmarks WHERE parent = :tags_folder) " +
           "AND b.type <> :bookmark_type " +
       ")");
     deleteBogusTagChildren.params["tags_folder"] = PlacesUtils.tagsFolderId;
     deleteBogusTagChildren.params["bookmark_type"] = PlacesUtils.bookmarks.TYPE_BOOKMARK;
     cleanupStatements.push(deleteBogusTagChildren);
 
     // D.3 remove empty tags
-    let deleteEmptyTags = DBConn.createStatement(
+    let deleteEmptyTags = DBConn.createAsyncStatement(
       "DELETE FROM moz_bookmarks WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN (" +
         "SELECT b.id FROM moz_bookmarks b " +
         "WHERE b.id IN " +
           "(SELECT id FROM moz_bookmarks WHERE parent = :tags_folder) " +
           "AND NOT EXISTS " +
             "(SELECT id from moz_bookmarks WHERE parent = b.id LIMIT 1) " +
       ")");
     deleteEmptyTags.params["tags_folder"] = PlacesUtils.tagsFolderId;
     cleanupStatements.push(deleteEmptyTags);
 
     // D.4 move orphan items to unsorted folder
-    let fixOrphanItems = DBConn.createStatement(
+    let fixOrphanItems = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET parent = :unsorted_folder WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " +  // skip roots
       ") AND id IN (" +
         "SELECT b.id FROM moz_bookmarks b " +
         "WHERE b.parent <> 0 " + // exclude Places root
         "AND NOT EXISTS " +
           "(SELECT id FROM moz_bookmarks WHERE id = b.parent LIMIT 1) " +
       ")");
     fixOrphanItems.params["unsorted_folder"] = PlacesUtils.unfiledBookmarksFolderId;
     cleanupStatements.push(fixOrphanItems);
 
     // D.5 fix wrong keywords
-    let fixInvalidKeywords = DBConn.createStatement(
+    let fixInvalidKeywords = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET keyword_id = NULL WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN ( " +
         "SELECT id FROM moz_bookmarks b " +
         "WHERE keyword_id NOT NULL " +
           "AND NOT EXISTS " +
             "(SELECT id FROM moz_keywords WHERE id = b.keyword_id LIMIT 1) " +
       ")");
     cleanupStatements.push(fixInvalidKeywords);
 
     // D.6 fix wrong item types
     //     Folders, separators and dynamic containers should not have an fk.
     //     If they have a valid fk convert them to bookmarks. Later in D.9 we
     //     will move eventual children to unsorted bookmarks.
-    let fixBookmarksAsFolders = DBConn.createStatement(
+    let fixBookmarksAsFolders = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET type = :bookmark_type WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN ( " +
         "SELECT id FROM moz_bookmarks b " +
         "WHERE type IN (:folder_type, :separator_type, :dynamic_type) " +
           "AND fk NOTNULL " +
       ")");
     fixBookmarksAsFolders.params["bookmark_type"] = PlacesUtils.bookmarks.TYPE_BOOKMARK;
     fixBookmarksAsFolders.params["folder_type"] = PlacesUtils.bookmarks.TYPE_FOLDER;
     fixBookmarksAsFolders.params["separator_type"] = PlacesUtils.bookmarks.TYPE_SEPARATOR;
     fixBookmarksAsFolders.params["dynamic_type"] = PlacesUtils.bookmarks.TYPE_DYNAMIC_CONTAINER;
     cleanupStatements.push(fixBookmarksAsFolders);
 
     // D.7 fix wrong item types
     //     Bookmarks should have an fk, if they don't have any, convert them to
     //     folders.
-    let fixFoldersAsBookmarks = DBConn.createStatement(
+    let fixFoldersAsBookmarks = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET type = :folder_type WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN ( " +
         "SELECT id FROM moz_bookmarks b " +
         "WHERE type = :bookmark_type " +
           "AND fk IS NULL " +
       ")");
     fixFoldersAsBookmarks.params["bookmark_type"] = PlacesUtils.bookmarks.TYPE_BOOKMARK;
     fixFoldersAsBookmarks.params["folder_type"] = PlacesUtils.bookmarks.TYPE_FOLDER;
     cleanupStatements.push(fixFoldersAsBookmarks);
 
     // D.8 fix wrong item types
     //     Dynamic containers should have a folder_type, if they don't have any
     //     convert them to folders.
-    let fixFoldersAsDynamic = DBConn.createStatement(
+    let fixFoldersAsDynamic = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET type = :folder_type WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " + // skip roots
       ") AND id IN ( " +
         "SELECT id FROM moz_bookmarks b " +
         "WHERE type = :dynamic_type " +
           "AND folder_type IS NULL " +
       ")");
     fixFoldersAsDynamic.params["dynamic_type"] = PlacesUtils.bookmarks.TYPE_DYNAMIC_CONTAINER;
     fixFoldersAsDynamic.params["folder_type"] = PlacesUtils.bookmarks.TYPE_FOLDER;
     cleanupStatements.push(fixFoldersAsDynamic);
 
     // D.9 fix wrong parents
     //     Items cannot have dynamic containers, separators or other bookmarks
     //     as parent, if they have bad parent move them to unsorted bookmarks.
-    let fixInvalidParents = DBConn.createStatement(
+    let fixInvalidParents = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET parent = :unsorted_folder WHERE id NOT IN ( " +
         "SELECT folder_id FROM moz_bookmarks_roots " +  // skip roots
       ") AND id IN ( " +
         "SELECT id FROM moz_bookmarks b " +
         "WHERE EXISTS " +
           "(SELECT id FROM moz_bookmarks WHERE id = b.parent " +
             "AND type IN (:bookmark_type, :separator_type, :dynamic_type) " +
             "LIMIT 1) " +
       ")");
     fixInvalidParents.params["unsorted_folder"] = PlacesUtils.unfiledBookmarksFolderId;
     fixInvalidParents.params["bookmark_type"] = PlacesUtils.bookmarks.TYPE_BOOKMARK;
     fixInvalidParents.params["separator_type"] = PlacesUtils.bookmarks.TYPE_SEPARATOR;
     fixInvalidParents.params["dynamic_type"] = PlacesUtils.bookmarks.TYPE_DYNAMIC_CONTAINER;
     cleanupStatements.push(fixInvalidParents);
 
-/* XXX needs test
     // D.10 recalculate positions
     //      This requires multiple related statements.
     //      We can detect a folder with bad position values comparing the sum of
-    //      all position values with the triangular numbers obtained by the number
-    //      of children: (n * (n + 1) / 2). Starting from 0 is (n * (n - 1) / 2).
-    let detectWrongPositionsParents = DBConn.createStatement(
-      "SELECT parent FROM " +
-        "(SELECT parent, " +
-                "(SUM(position) - (count(*) * (count(*) - 1) / 2)) AS diff " +
+    //      all distinct position values (+1 since position is 0-based) with the
+    //      triangular numbers obtained by the number of children (n).
+    //      SUM(DISTINCT position + 1) == (n * (n + 1) / 2).
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "CREATE TEMP TABLE IF NOT EXISTS moz_bm_reindex_temp ( " +
+      "  id INTEGER PRIMARY_KEY " +
+      ", parent INTEGER " +
+      ", position INTEGER " +
+      ") "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "INSERT INTO moz_bm_reindex_temp " +
+      "SELECT id, parent, 0 " +
+      "FROM moz_bookmarks b " +
+      "WHERE parent IN ( " +
+        "SELECT parent " +
         "FROM moz_bookmarks " +
-        "GROUP BY parent) " +
-      "WHERE diff <> 0");
-    while (detectWrongPositionsParents.executeStep()) {
-      let parent = detectWrongPositionsParents.getInt64(0);
-      // We will lose the previous position values and reposition items based
-      // on the ROWID value. Not perfect, but we can't rely on position values.
-      let fixPositionsForParent = DBConn.createStatement(
-        "UPDATE moz_bookmarks SET position = ( " +
-          "SELECT " +
-          "((SELECT count(*) FROM moz_bookmarks WHERE parent = :parent) - " +
-           "(SELECT count(*) FROM moz_bookmarks " +
-            "WHERE parent = :parent AND ROWID >= b.ROWID)) " +
-          "FROM moz_bookmarks b WHERE parent = :parent AND id = moz_bookmarks.id " +
-        ") WHERE parent = :parent");
-      fixPositionsForParent.params["parent"] = parent;
-      cleanupStatements.push(fixPositionsForParent);
-    }
-*/
+        "GROUP BY parent " +
+        "HAVING (SUM(DISTINCT position + 1) - (count(*) * (count(*) + 1) / 2)) <> 0 " +
+      ") " +
+      "ORDER BY parent ASC, position ASC, ROWID ASC "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "CREATE INDEX IF NOT EXISTS moz_bm_reindex_temp_index " +
+      "ON moz_bm_reindex_temp(parent)"
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "UPDATE moz_bm_reindex_temp SET position = ( " +
+        "ROWID - (SELECT MIN(t.ROWID) FROM moz_bm_reindex_temp t " +
+                 "WHERE t.parent = moz_bm_reindex_temp.parent) " +
+      ") "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "CREATE TEMP TRIGGER IF NOT EXISTS moz_bm_reindex_temp_trigger " +
+      "BEFORE DELETE ON moz_bm_reindex_temp " +
+      "FOR EACH ROW " +
+      "BEGIN " +
+        "UPDATE moz_bookmarks SET position = OLD.position WHERE id = OLD.id; " +
+      "END "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "DELETE FROM moz_bm_reindex_temp "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "DROP INDEX moz_bm_reindex_temp_index "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "DROP TRIGGER moz_bm_reindex_temp_trigger "
+    ));
+    cleanupStatements.push(DBConn.createAsyncStatement(
+      "DROP TABLE moz_bm_reindex_temp "
+    ));
 
     // D.11 remove old livemarks status items
     //      Livemark status items are now static but some livemark has still old
     //      status items bookmarks inside it. We should remove them.
-    let removeLivemarkStaticItems = DBConn.createStatement(
+    let removeLivemarkStaticItems = DBConn.createAsyncStatement(
       "DELETE FROM moz_bookmarks WHERE type = :bookmark_type AND fk IN ( " +
         "SELECT id FROM moz_places WHERE url = :lmloading OR url = :lmfailed " +
       ")");
     removeLivemarkStaticItems.params["bookmark_type"] = PlacesUtils.bookmarks.TYPE_BOOKMARK;
     removeLivemarkStaticItems.params["lmloading"] = "about:livemark-loading";
     removeLivemarkStaticItems.params["lmfailed"] = "about:livemark-failed";
     cleanupStatements.push(removeLivemarkStaticItems);
 
     // D.12 Fix empty-named tags.
     //      Tags were allowed to have empty names due to a UI bug.  Fix them
     //      replacing their title with "(notitle)".
-    let fixEmptyNamedTags = DBConn.createStatement(
+    let fixEmptyNamedTags = DBConn.createAsyncStatement(
       "UPDATE moz_bookmarks SET title = :empty_title " +
       "WHERE length(title) = 0 AND type = :folder_type " +
         "AND parent = :tags_folder"
     );
     fixEmptyNamedTags.params["empty_title"] = "(notitle)";
     fixEmptyNamedTags.params["folder_type"] = PlacesUtils.bookmarks.TYPE_FOLDER;
     fixEmptyNamedTags.params["tags_folder"] = PlacesUtils.tagsFolderId;
     cleanupStatements.push(fixEmptyNamedTags);
 
     // MOZ_FAVICONS
     // E.1 remove orphan icons
-    let deleteOrphanIcons = DBConn.createStatement(
+    let deleteOrphanIcons = DBConn.createAsyncStatement(
       "DELETE FROM moz_favicons WHERE id IN (" +
         "SELECT id FROM moz_favicons f " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_places WHERE favicon_id = f.id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteOrphanIcons);
 
     // MOZ_HISTORYVISITS
     // F.1 remove orphan visits
-    let deleteOrphanVisits = DBConn.createStatement(
+    let deleteOrphanVisits = DBConn.createAsyncStatement(
       "DELETE FROM moz_historyvisits WHERE id IN (" +
         "SELECT id FROM moz_historyvisits v " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_places WHERE id = v.place_id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteOrphanVisits);
 
     // MOZ_INPUTHISTORY
     // G.1 remove orphan input history
-    let deleteOrphanInputHistory = DBConn.createStatement(
+    let deleteOrphanInputHistory = DBConn.createAsyncStatement(
       "DELETE FROM moz_inputhistory WHERE place_id IN (" +
         "SELECT place_id FROM moz_inputhistory i " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_places WHERE id = i.place_id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteOrphanInputHistory);
 
     // MOZ_ITEMS_ANNOS
     // H.1 remove item annos with an invalid attribute
-    let deleteInvalidAttributeItemsAnnos = DBConn.createStatement(
+    let deleteInvalidAttributeItemsAnnos = DBConn.createAsyncStatement(
       "DELETE FROM moz_items_annos WHERE id IN ( " +
         "SELECT id FROM moz_items_annos t " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_anno_attributes " +
             "WHERE id = t.anno_attribute_id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteInvalidAttributeItemsAnnos);
 
     // H.2 remove orphan item annos
-    let deleteOrphanItemsAnnos = DBConn.createStatement(
+    let deleteOrphanItemsAnnos = DBConn.createAsyncStatement(
       "DELETE FROM moz_items_annos WHERE id IN ( " +
         "SELECT id FROM moz_items_annos t " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_bookmarks WHERE id = t.item_id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteOrphanItemsAnnos);
 
     // MOZ_KEYWORDS
     // I.1 remove unused keywords
-    let deleteUnusedKeywords = DBConn.createStatement(
+    let deleteUnusedKeywords = DBConn.createAsyncStatement(
       "DELETE FROM moz_keywords WHERE id IN ( " +
         "SELECT id FROM moz_keywords k " +
         "WHERE NOT EXISTS " +
           "(SELECT id FROM moz_bookmarks WHERE keyword_id = k.id LIMIT 1) " +
       ")");
     cleanupStatements.push(deleteUnusedKeywords);
 
     // MOZ_PLACES
     // L.1 fix wrong favicon ids
-    let fixInvalidFaviconIds = DBConn.createStatement(
+    let fixInvalidFaviconIds = DBConn.createAsyncStatement(
       "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);
 
--- a/toolkit/components/places/tests/unit/test_preventive_maintenance.js
+++ b/toolkit/components/places/tests/unit/test_preventive_maintenance.js
@@ -689,27 +689,103 @@ tests.push({
     stmt.params["item_id"] = this._bookmarkId3;
     stmt.params["parent"] = bs.unfiledBookmarksFolder;
     do_check_true(stmt.executeStep());
     stmt.finalize();    
   }
 });
 
 //------------------------------------------------------------------------------
-//XXX TODO
+
 tests.push({
   name: "D.10",
   desc: "Recalculate positions",
 
+  _unfiledBookmarks: [],
+  _toolbarBookmarks: [],
+
   setup: function() {
+    const NUM_BOOKMARKS = 20;
+    bs.runInBatchMode({
+      runBatched: function (aUserData) {
+        // Add bookmarks to two folders to better perturbate the table.
+        for (let i = 0; i < NUM_BOOKMARKS; i++) {
+          bs.insertBookmark(PlacesUtils.unfiledBookmarksFolderId,
+                            NetUtil.newURI("http://example.com/"),
+                            bs.DEFAULT_INDEX, "testbookmark");
+        }
+        for (let i = 0; i < NUM_BOOKMARKS; i++) {
+          bs.insertBookmark(PlacesUtils.toolbarFolderId,
+                            NetUtil.newURI("http://example.com/"),
+                            bs.DEFAULT_INDEX, "testbookmark");
+        }
+      }
+    }, null);
 
+    function randomize_positions(aParent, aResultArray) {
+      let stmt = mDBConn.createStatement(
+        "UPDATE moz_bookmarks SET position = :rand " +
+        "WHERE id IN ( " +
+          "SELECT id FROM moz_bookmarks WHERE parent = :parent " +
+          "ORDER BY RANDOM() LIMIT 1 " +
+        ") "
+      );
+      for (let i = 0; i < (NUM_BOOKMARKS / 2); i++) {
+        stmt.params["parent"] = aParent;
+        stmt.params["rand"] = Math.round(Math.random() * (NUM_BOOKMARKS - 1));
+        stmt.execute();
+        stmt.reset();
+      }
+      stmt.finalize();
+
+      // Build the expected ordered list of bookmarks.
+      stmt = mDBConn.createStatement(
+        "SELECT id, position " +
+        "FROM moz_bookmarks WHERE parent = :parent " +
+        "ORDER BY position ASC, ROWID ASC "
+      );
+      stmt.params["parent"] = aParent;
+      while (stmt.executeStep()) {
+        aResultArray.push(stmt.row.id);
+        print(stmt.row.id + "\t" + stmt.row.position + "\t" +
+              (aResultArray.length - 1));
+      }
+      stmt.finalize();
+    }
+
+    // Set random positions for the added bookmarks.
+    randomize_positions(PlacesUtils.unfiledBookmarksFolderId,
+                        this._unfiledBookmarks);
+    randomize_positions(PlacesUtils.toolbarFolderId, this._toolbarBookmarks);
   },
 
   check: function() {
+    function check_order(aParent, aResultArray) {
+      // Build the expected ordered list of bookmarks.
+      let stmt = mDBConn.createStatement(
+        "SELECT id, position FROM moz_bookmarks WHERE parent = :parent " +
+        "ORDER BY position ASC"
+      );
+      stmt.params["parent"] = aParent;
+      let pass = true;
+      while (stmt.executeStep()) {
+        print(stmt.row.id + "\t" + stmt.row.position);
+        if (aResultArray.indexOf(stmt.row.id) != stmt.row.position) {
+          pass = false;
+        }
+      }
+      stmt.finalize();
+      if (!pass) {
+        dump_table("moz_bookmarks");
+        do_throw("Unexpected unfiled bookmarks order.");
+      }
+    }
 
+    check_order(PlacesUtils.unfiledBookmarksFolderId, this._unfiledBookmarks);
+    check_order(PlacesUtils.toolbarFolderId, this._toolbarBookmarks);
   }
 });
 
 //------------------------------------------------------------------------------
 
 tests.push({
   name: "D.11",
   desc: "Remove old livemarks status items",