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 id18913
push usermak77@bonardo.net
push dateSat, 19 Feb 2011 12:52:30 +0000
treeherdermozilla-central@51b61720bc35 [default view] [failures only]
perfherder[talos] [build metrics] [platform microbench] (compared to previous push)
bugs626341
milestone2.0b12pre
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 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",