michael@0: /* -*- Mode: Java; tab-width: 2; indent-tabs-mode: nil; c-basic-offset: 2 -*- */
michael@0: /* vim:set ts=2 sw=2 sts=2 et: */
michael@0: /* This Source Code Form is subject to the terms of the Mozilla Public
michael@0:  * License, v. 2.0. If a copy of the MPL was not distributed with this
michael@0:  * file, You can obtain one at http://mozilla.org/MPL/2.0/. */
michael@0: 
michael@0:  /**
michael@0:   * Test preventive maintenance
michael@0:   * For every maintenance query create an uncoherent db and check that we take
michael@0:   * correct fix steps, without polluting valid data.
michael@0:   */
michael@0: 
michael@0: // Include PlacesDBUtils module
michael@0: Components.utils.import("resource://gre/modules/PlacesDBUtils.jsm");
michael@0: 
michael@0: const FINISHED_MAINTENANCE_NOTIFICATION_TOPIC = "places-maintenance-finished";
michael@0: 
michael@0: // Get services and database connection
michael@0: let hs = PlacesUtils.history;
michael@0: let bs = PlacesUtils.bookmarks;
michael@0: let ts = PlacesUtils.tagging;
michael@0: let as = PlacesUtils.annotations;
michael@0: let fs = PlacesUtils.favicons;
michael@0: 
michael@0: let mDBConn = hs.QueryInterface(Ci.nsPIPlacesDatabase).DBConnection;
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: // Helpers
michael@0: 
michael@0: let defaultBookmarksMaxId = 0;
michael@0: function cleanDatabase() {
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_places");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_historyvisits");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_anno_attributes");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_annos");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_items_annos");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_inputhistory");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_keywords");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_favicons");
michael@0:   mDBConn.executeSimpleSQL("DELETE FROM moz_bookmarks WHERE id > " + defaultBookmarksMaxId);
michael@0: }
michael@0: 
michael@0: function addPlace(aUrl, aFavicon) {
michael@0:   let stmt = mDBConn.createStatement(
michael@0:     "INSERT INTO moz_places (url, favicon_id) VALUES (:url, :favicon)");
michael@0:   stmt.params["url"] = aUrl || "http://www.mozilla.org";
michael@0:   stmt.params["favicon"] = aFavicon || null;
michael@0:   stmt.execute();
michael@0:   stmt.finalize();
michael@0:   return mDBConn.lastInsertRowID;
michael@0: }
michael@0: 
michael@0: function addBookmark(aPlaceId, aType, aParent, aKeywordId, aFolderType, aTitle) {
michael@0:   let stmt = mDBConn.createStatement(
michael@0:     "INSERT INTO moz_bookmarks (fk, type, parent, keyword_id, folder_type, "
michael@0:   +                            "title, guid) "
michael@0:   + "VALUES (:place_id, :type, :parent, :keyword_id, :folder_type, :title, "
michael@0:   +         "GENERATE_GUID())");
michael@0:   stmt.params["place_id"] = aPlaceId || null;
michael@0:   stmt.params["type"] = aType || bs.TYPE_BOOKMARK;
michael@0:   stmt.params["parent"] = aParent || bs.unfiledBookmarksFolder;
michael@0:   stmt.params["keyword_id"] = aKeywordId || null;
michael@0:   stmt.params["folder_type"] = aFolderType || null;
michael@0:   stmt.params["title"] = typeof(aTitle) == "string" ? aTitle : null;
michael@0:   stmt.execute();
michael@0:   stmt.finalize();
michael@0:   return mDBConn.lastInsertRowID;
michael@0: }
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: // Tests
michael@0: 
michael@0: let tests = [];
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "A.1",
michael@0:   desc: "Remove obsolete annotations from moz_annos",
michael@0: 
michael@0:   _obsoleteWeaveAttribute: "weave/test",
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid.
michael@0:     this._placeId = addPlace();
michael@0:     // Add an obsolete attribute.
michael@0:     let stmt = mDBConn.createStatement(
michael@0:       "INSERT INTO moz_anno_attributes (name) VALUES (:anno)"
michael@0:     );
michael@0:     stmt.params['anno'] = this._obsoleteWeaveAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement(
michael@0:       "INSERT INTO moz_annos (place_id, anno_attribute_id) "
michael@0:     + "VALUES (:place_id, "
michael@0:     +   "(SELECT id FROM moz_anno_attributes WHERE name = :anno)"
michael@0:     + ")"
michael@0:     );
michael@0:     stmt.params['place_id'] = this._placeId;
michael@0:     stmt.params['anno'] = this._obsoleteWeaveAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that the obsolete annotation has been removed.
michael@0:     let stmt = mDBConn.createStatement(
michael@0:       "SELECT id FROM moz_anno_attributes WHERE name = :anno"
michael@0:     );
michael@0:     stmt.params['anno'] = this._obsoleteWeaveAttribute;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: tests.push({
michael@0:   name: "A.2",
michael@0:   desc: "Remove obsolete annotations from moz_items_annos",
michael@0: 
michael@0:   _obsoleteSyncAttribute: "sync/children",
michael@0:   _obsoleteGuidAttribute: "placesInternal/GUID",
michael@0:   _obsoleteWeaveAttribute: "weave/test",
michael@0:   _placeId: null,
michael@0:   _bookmarkId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid.
michael@0:     this._placeId = addPlace();
michael@0:     // Add a bookmark.
michael@0:     this._bookmarkId = addBookmark(this._placeId);
michael@0:     // Add an obsolete attribute.
michael@0:     let stmt = mDBConn.createStatement(
michael@0:       "INSERT INTO moz_anno_attributes (name) "
michael@0:     + "VALUES (:anno1), (:anno2), (:anno3)"
michael@0:     );
michael@0:     stmt.params['anno1'] = this._obsoleteSyncAttribute;
michael@0:     stmt.params['anno2'] = this._obsoleteGuidAttribute;
michael@0:     stmt.params['anno3'] = this._obsoleteWeaveAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement(
michael@0:       "INSERT INTO moz_items_annos (item_id, anno_attribute_id) "
michael@0:     + "SELECT :item_id, id "
michael@0:     + "FROM moz_anno_attributes "
michael@0:     + "WHERE name IN (:anno1, :anno2, :anno3)"
michael@0:     );
michael@0:     stmt.params['item_id'] = this._bookmarkId;
michael@0:     stmt.params['anno1'] = this._obsoleteSyncAttribute;
michael@0:     stmt.params['anno2'] = this._obsoleteGuidAttribute;
michael@0:     stmt.params['anno3'] = this._obsoleteWeaveAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that the obsolete annotations have been removed.
michael@0:     let stmt = mDBConn.createStatement(
michael@0:       "SELECT id FROM moz_anno_attributes "
michael@0:     + "WHERE name IN (:anno1, :anno2, :anno3)"
michael@0:     );
michael@0:     stmt.params['anno1'] = this._obsoleteSyncAttribute;
michael@0:     stmt.params['anno2'] = this._obsoleteGuidAttribute;
michael@0:     stmt.params['anno3'] = this._obsoleteWeaveAttribute;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: tests.push({
michael@0:   name: "A.3",
michael@0:   desc: "Remove unused attributes",
michael@0: 
michael@0:   _usedPageAttribute: "usedPage",
michael@0:   _usedItemAttribute: "usedItem",
michael@0:   _unusedAttribute: "unused",
michael@0:   _placeId: null,
michael@0:   _bookmarkId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // add a bookmark
michael@0:     this._bookmarkId = addBookmark(this._placeId);
michael@0:     // Add a used attribute and an unused one.
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_anno_attributes (name) VALUES (:anno)");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     stmt.params['anno'] = this._unusedAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0: 
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_annos (place_id, anno_attribute_id) VALUES(:place_id, (SELECT id FROM moz_anno_attributes WHERE name = :anno))");
michael@0:     stmt.params['place_id'] = this._placeId;
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_items_annos (item_id, anno_attribute_id) VALUES(:item_id, (SELECT id FROM moz_anno_attributes WHERE name = :anno))");
michael@0:     stmt.params['item_id'] = this._bookmarkId;
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that used attributes are still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_anno_attributes WHERE name = :anno");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that unused attribute has been removed
michael@0:     stmt.params['anno'] = this._unusedAttribute;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "B.1",
michael@0:   desc: "Remove annotations with an invalid attribute",
michael@0: 
michael@0:   _usedPageAttribute: "usedPage",
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add a used attribute.
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_anno_attributes (name) VALUES (:anno)");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_annos (place_id, anno_attribute_id) VALUES(:place_id, (SELECT id FROM moz_anno_attributes WHERE name = :anno))");
michael@0:     stmt.params['place_id'] = this._placeId;
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     // Add an annotation with a nonexistent attribute
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_annos (place_id, anno_attribute_id) VALUES(:place_id, 1337)");
michael@0:     stmt.params['place_id'] = this._placeId;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that used attribute is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_anno_attributes WHERE name = :anno");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // check that annotation with valid attribute is still there
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_annos WHERE anno_attribute_id = (SELECT id FROM moz_anno_attributes WHERE name = :anno)");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // Check that annotation with bogus attribute has been removed
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_annos WHERE anno_attribute_id = 1337");
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "B.2",
michael@0:   desc: "Remove orphan page annotations",
michael@0: 
michael@0:   _usedPageAttribute: "usedPage",
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add a used attribute.
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_anno_attributes (name) VALUES (:anno)");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_annos (place_id, anno_attribute_id) VALUES(:place_id, (SELECT id FROM moz_anno_attributes WHERE name = :anno))");
michael@0:     stmt.params['place_id'] = this._placeId;
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     // Add an annotation to a nonexistent page
michael@0:     stmt.params['place_id'] = 1337;
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that used attribute is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_anno_attributes WHERE name = :anno");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // check that annotation with valid attribute is still there
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_annos WHERE anno_attribute_id = (SELECT id FROM moz_anno_attributes WHERE name = :anno)");
michael@0:     stmt.params['anno'] = this._usedPageAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // Check that an annotation to a nonexistent page has been removed
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_annos WHERE place_id = 1337");
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: tests.push({
michael@0:   name: "C.1",
michael@0:   desc: "fix missing Places root",
michael@0: 
michael@0:   setup: function() {
michael@0:     // Sanity check: ensure that roots are intact.
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.placesRoot), 0);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.bookmarksMenuFolder), bs.placesRoot);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.tagsFolder), bs.placesRoot);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.unfiledBookmarksFolder), bs.placesRoot);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.toolbarFolder), bs.placesRoot);
michael@0: 
michael@0:     // Remove the root.
michael@0:     mDBConn.executeSimpleSQL("DELETE FROM moz_bookmarks WHERE parent = 0");
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE parent = 0");
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Ensure the roots have been correctly restored.
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.placesRoot), 0);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.bookmarksMenuFolder), bs.placesRoot);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.tagsFolder), bs.placesRoot);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.unfiledBookmarksFolder), bs.placesRoot);
michael@0:     do_check_eq(bs.getFolderIdForItem(bs.toolbarFolder), bs.placesRoot);
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: tests.push({
michael@0:   name: "C.2",
michael@0:   desc: "Fix roots titles",
michael@0: 
michael@0:   setup: function() {
michael@0:     // Sanity check: ensure that roots titles are correct. We can use our check.
michael@0:     this.check();
michael@0:     // Change some roots' titles.
michael@0:     bs.setItemTitle(bs.placesRoot, "bad title");
michael@0:     do_check_eq(bs.getItemTitle(bs.placesRoot), "bad title");
michael@0:     bs.setItemTitle(bs.unfiledBookmarksFolder, "bad title");
michael@0:     do_check_eq(bs.getItemTitle(bs.unfiledBookmarksFolder), "bad title");
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Ensure all roots titles are correct.
michael@0:     do_check_eq(bs.getItemTitle(bs.placesRoot), "");
michael@0:     do_check_eq(bs.getItemTitle(bs.bookmarksMenuFolder),
michael@0:                 PlacesUtils.getString("BookmarksMenuFolderTitle"));
michael@0:     do_check_eq(bs.getItemTitle(bs.tagsFolder),
michael@0:                 PlacesUtils.getString("TagsFolderTitle"));
michael@0:     do_check_eq(bs.getItemTitle(bs.unfiledBookmarksFolder),
michael@0:                 PlacesUtils.getString("UnsortedBookmarksFolderTitle"));
michael@0:     do_check_eq(bs.getItemTitle(bs.toolbarFolder),
michael@0:                 PlacesUtils.getString("BookmarksToolbarFolderTitle"));
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.1",
michael@0:   desc: "Remove items without a valid place",
michael@0: 
michael@0:   _validItemId: null,
michael@0:   _invalidItemId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this.placeId = addPlace();
michael@0:     // Insert a valid bookmark
michael@0:     this._validItemId = addBookmark(this.placeId);
michael@0:     // Insert a bookmark with an invalid place
michael@0:     this._invalidItemId = addBookmark(1337);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that valid bookmark is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id");
michael@0:     stmt.params["item_id"] = this._validItemId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that invalid bookmark has been removed
michael@0:     stmt.params["item_id"] = this._invalidItemId;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.2",
michael@0:   desc: "Remove items that are not uri bookmarks from tag containers",
michael@0: 
michael@0:   _tagId: null,
michael@0:   _bookmarkId: null,
michael@0:   _separatorId: null,
michael@0:   _folderId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Create a tag
michael@0:     this._tagId = addBookmark(null, bs.TYPE_FOLDER, bs.tagsFolder);
michael@0:     // Insert a bookmark in the tag
michael@0:     this._bookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, this._tagId);
michael@0:     // Insert a separator in the tag
michael@0:     this._separatorId = addBookmark(null, bs.TYPE_SEPARATOR, this._tagId);
michael@0:     // Insert a folder in the tag
michael@0:     this._folderId = addBookmark(null, bs.TYPE_FOLDER, this._tagId);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that valid bookmark is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE type = :type AND parent = :parent");
michael@0:     stmt.params["type"] = bs.TYPE_BOOKMARK;
michael@0:     stmt.params["parent"] = this._tagId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that separator is no more there
michael@0:     stmt.params["type"] = bs.TYPE_SEPARATOR;
michael@0:     stmt.params["parent"] = this._tagId;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that folder is no more there
michael@0:     stmt.params["type"] = bs.TYPE_FOLDER;
michael@0:     stmt.params["parent"] = this._tagId;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.3",
michael@0:   desc: "Remove empty tags",
michael@0: 
michael@0:   _tagId: null,
michael@0:   _bookmarkId: null,
michael@0:   _emptyTagId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Create a tag
michael@0:     this._tagId = addBookmark(null, bs.TYPE_FOLDER, bs.tagsFolder);
michael@0:     // Insert a bookmark in the tag
michael@0:     this._bookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, this._tagId);
michael@0:     // Create another tag (empty)
michael@0:     this._emptyTagId = addBookmark(null, bs.TYPE_FOLDER, bs.tagsFolder);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that valid bookmark is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :id AND type = :type AND parent = :parent");
michael@0:     stmt.params["id"] = this._bookmarkId;
michael@0:     stmt.params["type"] = bs.TYPE_BOOKMARK;
michael@0:     stmt.params["parent"] = this._tagId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["id"] = this._tagId;
michael@0:     stmt.params["type"] = bs.TYPE_FOLDER;
michael@0:     stmt.params["parent"] = bs.tagsFolder;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["id"] = this._emptyTagId;
michael@0:     stmt.params["type"] = bs.TYPE_FOLDER;
michael@0:     stmt.params["parent"] = bs.tagsFolder;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.4",
michael@0:   desc: "Move orphan items to unsorted folder",
michael@0: 
michael@0:   _orphanBookmarkId: null,
michael@0:   _orphanSeparatorId: null,
michael@0:   _orphanFolderId: null,
michael@0:   _bookmarkId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Insert an orphan bookmark
michael@0:     this._orphanBookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, 8888);
michael@0:     // Insert an orphan separator
michael@0:     this._orphanSeparatorId = addBookmark(null, bs.TYPE_SEPARATOR, 8888);
michael@0:     // Insert a orphan folder
michael@0:     this._orphanFolderId = addBookmark(null, bs.TYPE_FOLDER, 8888);
michael@0:     // Create a child of the last created folder
michael@0:     this._bookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, this._orphanFolderId);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that bookmarks are now children of a real folder (unsorted)
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id AND parent = :parent");
michael@0:     stmt.params["item_id"] = this._orphanBookmarkId;
michael@0:     stmt.params["parent"] = bs.unfiledBookmarksFolder;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["item_id"] = this._orphanSeparatorId;
michael@0:     stmt.params["parent"] = bs.unfiledBookmarksFolder;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["item_id"] = this._orphanFolderId;
michael@0:     stmt.params["parent"] = bs.unfiledBookmarksFolder;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["item_id"] = this._bookmarkId;
michael@0:     stmt.params["parent"] = this._orphanFolderId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.5",
michael@0:   desc: "Fix wrong keywords",
michael@0: 
michael@0:   _validKeywordItemId: null,
michael@0:   _invalidKeywordItemId: null,
michael@0:   _validKeywordId: 1,
michael@0:   _invalidKeywordId: 8888,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Insert a keyword
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_keywords (id, keyword) VALUES(:id, :keyword)");
michael@0:     stmt.params["id"] = this._validKeywordId;
michael@0:     stmt.params["keyword"] = "used";
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add a bookmark using the keyword
michael@0:     this._validKeywordItemId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, bs.unfiledBookmarksFolder, this._validKeywordId);
michael@0:     // Add a bookmark using a nonexistent keyword
michael@0:     this._invalidKeywordItemId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, bs.unfiledBookmarksFolder, this._invalidKeywordId);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that item with valid keyword is there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id AND keyword_id = :keyword");
michael@0:     stmt.params["item_id"] = this._validKeywordItemId;
michael@0:     stmt.params["keyword"] = this._validKeywordId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that item with invalid keyword has been corrected
michael@0:     stmt.params["item_id"] = this._invalidKeywordItemId;
michael@0:     stmt.params["keyword"] = this._invalidKeywordId;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // Check that item with invalid keyword has not been removed
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id");
michael@0:     stmt.params["item_id"] = this._invalidKeywordItemId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.6",
michael@0:   desc: "Fix wrong item types | bookmarks",
michael@0: 
michael@0:   _separatorId: null,
michael@0:   _folderId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add a separator with a fk
michael@0:     this._separatorId = addBookmark(this._placeId, bs.TYPE_SEPARATOR);
michael@0:     // Add a folder with a fk
michael@0:     this._folderId = addBookmark(this._placeId, bs.TYPE_FOLDER);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that items with an fk have been converted to bookmarks
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id AND type = :type");
michael@0:     stmt.params["item_id"] = this._separatorId;
michael@0:     stmt.params["type"] = bs.TYPE_BOOKMARK;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["item_id"] = this._folderId;
michael@0:     stmt.params["type"] = bs.TYPE_BOOKMARK;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.7",
michael@0:   desc: "Fix wrong item types | bookmarks",
michael@0: 
michael@0:   _validBookmarkId: null,
michael@0:   _invalidBookmarkId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add a bookmark with a valid place id
michael@0:     this._validBookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK);
michael@0:     // Add a bookmark with a null place id
michael@0:     this._invalidBookmarkId = addBookmark(null, bs.TYPE_BOOKMARK);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check valid bookmark
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id AND type = :type");
michael@0:     stmt.params["item_id"] = this._validBookmarkId;
michael@0:     stmt.params["type"] = bs.TYPE_BOOKMARK;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check invalid bookmark has been converted to a folder
michael@0:     stmt.params["item_id"] = this._invalidBookmarkId;
michael@0:     stmt.params["type"] = bs.TYPE_FOLDER;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.9",
michael@0:   desc: "Fix wrong parents",
michael@0: 
michael@0:   _bookmarkId: null,
michael@0:   _separatorId: null,
michael@0:   _bookmarkId1: null,
michael@0:   _bookmarkId2: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Insert a bookmark
michael@0:     this._bookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK);
michael@0:     // Insert a separator
michael@0:     this._separatorId = addBookmark(null, bs.TYPE_SEPARATOR);
michael@0:     // Create 3 children of these items
michael@0:     this._bookmarkId1 = addBookmark(this._placeId, bs.TYPE_BOOKMARK, this._bookmarkId);
michael@0:     this._bookmarkId2 = addBookmark(this._placeId, bs.TYPE_BOOKMARK, this._separatorId);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that bookmarks are now children of a real folder (unsorted)
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_bookmarks WHERE id = :item_id AND parent = :parent");
michael@0:     stmt.params["item_id"] = this._bookmarkId1;
michael@0:     stmt.params["parent"] = bs.unfiledBookmarksFolder;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["item_id"] = this._bookmarkId2;
michael@0:     stmt.params["parent"] = bs.unfiledBookmarksFolder;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.10",
michael@0:   desc: "Recalculate positions",
michael@0: 
michael@0:   _unfiledBookmarks: [],
michael@0:   _toolbarBookmarks: [],
michael@0: 
michael@0:   setup: function() {
michael@0:     const NUM_BOOKMARKS = 20;
michael@0:     bs.runInBatchMode({
michael@0:       runBatched: function (aUserData) {
michael@0:         // Add bookmarks to two folders to better perturbate the table.
michael@0:         for (let i = 0; i < NUM_BOOKMARKS; i++) {
michael@0:           bs.insertBookmark(PlacesUtils.unfiledBookmarksFolderId,
michael@0:                             NetUtil.newURI("http://example.com/"),
michael@0:                             bs.DEFAULT_INDEX, "testbookmark");
michael@0:         }
michael@0:         for (let i = 0; i < NUM_BOOKMARKS; i++) {
michael@0:           bs.insertBookmark(PlacesUtils.toolbarFolderId,
michael@0:                             NetUtil.newURI("http://example.com/"),
michael@0:                             bs.DEFAULT_INDEX, "testbookmark");
michael@0:         }
michael@0:       }
michael@0:     }, null);
michael@0: 
michael@0:     function randomize_positions(aParent, aResultArray) {
michael@0:       let stmt = mDBConn.createStatement(
michael@0:         "UPDATE moz_bookmarks SET position = :rand " +
michael@0:         "WHERE id IN ( " +
michael@0:           "SELECT id FROM moz_bookmarks WHERE parent = :parent " +
michael@0:           "ORDER BY RANDOM() LIMIT 1 " +
michael@0:         ") "
michael@0:       );
michael@0:       for (let i = 0; i < (NUM_BOOKMARKS / 2); i++) {
michael@0:         stmt.params["parent"] = aParent;
michael@0:         stmt.params["rand"] = Math.round(Math.random() * (NUM_BOOKMARKS - 1));
michael@0:         stmt.execute();
michael@0:         stmt.reset();
michael@0:       }
michael@0:       stmt.finalize();
michael@0: 
michael@0:       // Build the expected ordered list of bookmarks.
michael@0:       stmt = mDBConn.createStatement(
michael@0:         "SELECT id, position " +
michael@0:         "FROM moz_bookmarks WHERE parent = :parent " +
michael@0:         "ORDER BY position ASC, ROWID ASC "
michael@0:       );
michael@0:       stmt.params["parent"] = aParent;
michael@0:       while (stmt.executeStep()) {
michael@0:         aResultArray.push(stmt.row.id);
michael@0:         print(stmt.row.id + "\t" + stmt.row.position + "\t" +
michael@0:               (aResultArray.length - 1));
michael@0:       }
michael@0:       stmt.finalize();
michael@0:     }
michael@0: 
michael@0:     // Set random positions for the added bookmarks.
michael@0:     randomize_positions(PlacesUtils.unfiledBookmarksFolderId,
michael@0:                         this._unfiledBookmarks);
michael@0:     randomize_positions(PlacesUtils.toolbarFolderId, this._toolbarBookmarks);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     function check_order(aParent, aResultArray) {
michael@0:       // Build the expected ordered list of bookmarks.
michael@0:       let stmt = mDBConn.createStatement(
michael@0:         "SELECT id, position FROM moz_bookmarks WHERE parent = :parent " +
michael@0:         "ORDER BY position ASC"
michael@0:       );
michael@0:       stmt.params["parent"] = aParent;
michael@0:       let pass = true;
michael@0:       while (stmt.executeStep()) {
michael@0:         print(stmt.row.id + "\t" + stmt.row.position);
michael@0:         if (aResultArray.indexOf(stmt.row.id) != stmt.row.position) {
michael@0:           pass = false;
michael@0:         }
michael@0:       }
michael@0:       stmt.finalize();
michael@0:       if (!pass) {
michael@0:         dump_table("moz_bookmarks");
michael@0:         do_throw("Unexpected unfiled bookmarks order.");
michael@0:       }
michael@0:     }
michael@0: 
michael@0:     check_order(PlacesUtils.unfiledBookmarksFolderId, this._unfiledBookmarks);
michael@0:     check_order(PlacesUtils.toolbarFolderId, this._toolbarBookmarks);
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "D.12",
michael@0:   desc: "Fix empty-named tags",
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     let placeId = addPlace();
michael@0:     // Create a empty-named tag.
michael@0:     this._untitledTagId = addBookmark(null, bs.TYPE_FOLDER, bs.tagsFolder, null, null, "");
michael@0:     // Insert a bookmark in the tag, otherwise it will be removed.
michael@0:     addBookmark(placeId, bs.TYPE_BOOKMARK, this._untitledTagId);
michael@0:     // Create a empty-named folder.
michael@0:     this._untitledFolderId = addBookmark(null, bs.TYPE_FOLDER, bs.toolbarFolder, null, null, "");
michael@0:     // Create a titled tag.
michael@0:     this._titledTagId = addBookmark(null, bs.TYPE_FOLDER, bs.tagsFolder, null, null, "titledTag");
michael@0:     // Insert a bookmark in the tag, otherwise it will be removed.
michael@0:     addBookmark(placeId, bs.TYPE_BOOKMARK, this._titledTagId);
michael@0:     // Create a titled folder.
michael@0:     this._titledFolderId = addBookmark(null, bs.TYPE_FOLDER, bs.toolbarFolder, null, null, "titledFolder");
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that valid bookmark is still there
michael@0:     let stmt = mDBConn.createStatement(
michael@0:       "SELECT title FROM moz_bookmarks WHERE id = :id"
michael@0:     );
michael@0:     stmt.params["id"] = this._untitledTagId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     do_check_eq(stmt.row.title, "(notitle)");
michael@0:     stmt.reset();
michael@0:     stmt.params["id"] = this._untitledFolderId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     do_check_eq(stmt.row.title, "");
michael@0:     stmt.reset();
michael@0:     stmt.params["id"] = this._titledTagId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     do_check_eq(stmt.row.title, "titledTag");
michael@0:     stmt.reset();
michael@0:     stmt.params["id"] = this._titledFolderId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     do_check_eq(stmt.row.title, "titledFolder");
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "E.1",
michael@0:   desc: "Remove orphan icons",
michael@0: 
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Insert favicon entries
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_favicons (id, url) VALUES(:favicon_id, :url)");
michael@0:     stmt.params["favicon_id"] = 1;
michael@0:     stmt.params["url"] = "http://www1.mozilla.org/favicon.ico";
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     stmt.params["favicon_id"] = 2;
michael@0:     stmt.params["url"] = "http://www2.mozilla.org/favicon.ico";
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     // Insert a place using the existing favicon entry
michael@0:     this._placeId = addPlace("http://www.mozilla.org", 1);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that used icon is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_favicons WHERE id = :favicon_id");
michael@0:     stmt.params["favicon_id"] = 1;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that unused icon has been removed
michael@0:     stmt.params["favicon_id"] = 2;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "F.1",
michael@0:   desc: "Remove orphan visits",
michael@0: 
michael@0:   _placeId: null,
michael@0:   _invalidPlaceId: 1337,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add a valid visit and an invalid one
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_historyvisits(place_id) VALUES (:place_id)");
michael@0:     stmt.params["place_id"] = this._placeId;
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     stmt.params["place_id"] = this._invalidPlaceId;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that valid visit is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_historyvisits WHERE place_id = :place_id");
michael@0:     stmt.params["place_id"] = this._placeId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that invalid visit has been removed
michael@0:     stmt.params["place_id"] = this._invalidPlaceId;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "G.1",
michael@0:   desc: "Remove orphan input history",
michael@0: 
michael@0:   _placeId: null,
michael@0:   _invalidPlaceId: 1337,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Add input history entries
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_inputhistory (place_id, input) VALUES (:place_id, :input)");
michael@0:     stmt.params["place_id"] = this._placeId;
michael@0:     stmt.params["input"] = "moz";
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     stmt.params["place_id"] = this._invalidPlaceId;
michael@0:     stmt.params["input"] = "moz";
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that inputhistory on valid place is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT place_id FROM moz_inputhistory WHERE place_id = :place_id");
michael@0:     stmt.params["place_id"] = this._placeId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that inputhistory on invalid place has gone
michael@0:     stmt.params["place_id"] = this._invalidPlaceId;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "H.1",
michael@0:   desc: "Remove item annos with an invalid attribute",
michael@0: 
michael@0:   _usedItemAttribute: "usedItem",
michael@0:   _bookmarkId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Insert a bookmark
michael@0:     this._bookmarkId = addBookmark(this._placeId);
michael@0:     // Add a used attribute.
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_anno_attributes (name) VALUES (:anno)");
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_items_annos (item_id, anno_attribute_id) VALUES(:item_id, (SELECT id FROM moz_anno_attributes WHERE name = :anno))");
michael@0:     stmt.params['item_id'] = this._bookmarkId;
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     // Add an annotation with a nonexistent attribute
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_items_annos (item_id, anno_attribute_id) VALUES(:item_id, 1337)");
michael@0:     stmt.params['item_id'] = this._bookmarkId;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that used attribute is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_anno_attributes WHERE name = :anno");
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // check that annotation with valid attribute is still there
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_items_annos WHERE anno_attribute_id = (SELECT id FROM moz_anno_attributes WHERE name = :anno)");
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // Check that annotation with bogus attribute has been removed
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_items_annos WHERE anno_attribute_id = 1337");
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "H.2",
michael@0:   desc: "Remove orphan item annotations",
michael@0: 
michael@0:   _usedItemAttribute: "usedItem",
michael@0:   _bookmarkId: null,
michael@0:   _invalidBookmarkId: 8888,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Insert a bookmark
michael@0:     this._bookmarkId = addBookmark(this._placeId);
michael@0:     // Add a used attribute.
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_anno_attributes (name) VALUES (:anno)");
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     stmt = mDBConn.createStatement("INSERT INTO moz_items_annos (item_id, anno_attribute_id) VALUES (:item_id, (SELECT id FROM moz_anno_attributes WHERE name = :anno))");
michael@0:     stmt.params["item_id"] = this._bookmarkId;
michael@0:     stmt.params["anno"] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     // Add an annotation to a nonexistent item
michael@0:     stmt.params["item_id"] = this._invalidBookmarkId;
michael@0:     stmt.params["anno"] = this._usedItemAttribute;
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that used attribute is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_anno_attributes WHERE name = :anno");
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // check that annotation with valid attribute is still there
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_items_annos WHERE anno_attribute_id = (SELECT id FROM moz_anno_attributes WHERE name = :anno)");
michael@0:     stmt.params['anno'] = this._usedItemAttribute;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // Check that an annotation to a nonexistent page has been removed
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_items_annos WHERE item_id = 8888");
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "I.1",
michael@0:   desc: "Remove unused keywords",
michael@0: 
michael@0:   _bookmarkId: null,
michael@0:   _placeId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Insert 2 keywords
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_keywords (id, keyword) VALUES(:id, :keyword)");
michael@0:     stmt.params["id"] = 1;
michael@0:     stmt.params["keyword"] = "used";
michael@0:     stmt.execute();
michael@0:     stmt.reset();
michael@0:     stmt.params["id"] = 2;
michael@0:     stmt.params["keyword"] = "unused";
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     // Add a place to ensure place_id = 1 is valid
michael@0:     this._placeId = addPlace();
michael@0:     // Insert a bookmark using the "used" keyword
michael@0:     this._bookmarkId = addBookmark(this._placeId, bs.TYPE_BOOKMARK, bs.unfiledBookmarksFolder, 1);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that "used" keyword is still there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_keywords WHERE keyword = :keyword");
michael@0:     stmt.params["keyword"] = "used";
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that "unused" keyword has gone
michael@0:     stmt.params["keyword"] = "unused";
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "L.1",
michael@0:   desc: "Fix wrong favicon ids",
michael@0: 
michael@0:   _validIconPlaceId: null,
michael@0:   _invalidIconPlaceId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // Insert a favicon entry
michael@0:     let stmt = mDBConn.createStatement("INSERT INTO moz_favicons (id, url) VALUES(1, :url)");
michael@0:     stmt.params["url"] = "http://www.mozilla.org/favicon.ico";
michael@0:     stmt.execute();
michael@0:     stmt.finalize();
michael@0:     // Insert a place using the existing favicon entry
michael@0:     this._validIconPlaceId = addPlace("http://www1.mozilla.org", 1);
michael@0: 
michael@0:     // Insert a place using a nonexistent favicon entry
michael@0:     this._invalidIconPlaceId = addPlace("http://www2.mozilla.org", 1337);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     // Check that bogus favicon is not there
michael@0:     let stmt = mDBConn.createStatement("SELECT id FROM moz_places WHERE favicon_id = :favicon_id");
michael@0:     stmt.params["favicon_id"] = 1337;
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     // Check that valid favicon is still there
michael@0:     stmt.params["favicon_id"] = 1;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:     // Check that place entries are there
michael@0:     stmt = mDBConn.createStatement("SELECT id FROM moz_places WHERE id = :place_id");
michael@0:     stmt.params["place_id"] = this._validIconPlaceId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.reset();
michael@0:     stmt.params["place_id"] = this._invalidIconPlaceId;
michael@0:     do_check_true(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "L.2",
michael@0:   desc: "Recalculate visit_count and last_visit_date",
michael@0: 
michael@0:   setup: function() {
michael@0:     function setVisitCount(aURL, aValue) {
michael@0:       let stmt = mDBConn.createStatement(
michael@0:         "UPDATE moz_places SET visit_count = :count WHERE url = :url"
michael@0:       );
michael@0:       stmt.params.count = aValue;
michael@0:       stmt.params.url = aURL;
michael@0:       stmt.execute();
michael@0:       stmt.finalize();
michael@0:     }
michael@0:     function setLastVisitDate(aURL, aValue) {
michael@0:       let stmt = mDBConn.createStatement(
michael@0:         "UPDATE moz_places SET last_visit_date = :date WHERE url = :url"
michael@0:       );
michael@0:       stmt.params.date = aValue;
michael@0:       stmt.params.url = aURL;
michael@0:       stmt.execute();
michael@0:       stmt.finalize();
michael@0:     }
michael@0: 
michael@0:     let now = Date.now() * 1000;
michael@0:     // Add a page with 1 visit.
michael@0:     let url = "http://1.moz.org/";
michael@0:     yield promiseAddVisits({ uri: uri(url), visitDate: now++ });
michael@0:     // Add a page with 1 visit and set wrong visit_count.
michael@0:     url = "http://2.moz.org/";
michael@0:     yield promiseAddVisits({ uri: uri(url), visitDate: now++ });
michael@0:     setVisitCount(url, 10);
michael@0:     // Add a page with 1 visit and set wrong last_visit_date.
michael@0:     url = "http://3.moz.org/";
michael@0:     yield promiseAddVisits({ uri: uri(url), visitDate: now++ });
michael@0:     setLastVisitDate(url, now++);
michael@0:     // Add a page with 1 visit and set wrong stats.
michael@0:     url = "http://4.moz.org/";
michael@0:     yield promiseAddVisits({ uri: uri(url), visitDate: now++ });
michael@0:     setVisitCount(url, 10);
michael@0:     setLastVisitDate(url, now++);
michael@0: 
michael@0:     // Add a page without visits.
michael@0:     let url = "http://5.moz.org/";
michael@0:     addPlace(url);
michael@0:     // Add a page without visits and set wrong visit_count.
michael@0:     url = "http://6.moz.org/";
michael@0:     addPlace(url);
michael@0:     setVisitCount(url, 10);
michael@0:     // Add a page without visits and set wrong last_visit_date.
michael@0:     url = "http://7.moz.org/";
michael@0:     addPlace(url);
michael@0:     setLastVisitDate(url, now++);
michael@0:     // Add a page without visits and set wrong stats.
michael@0:     url = "http://8.moz.org/";
michael@0:     addPlace(url);
michael@0:     setVisitCount(url, 10);
michael@0:     setLastVisitDate(url, now++);
michael@0:   },
michael@0: 
michael@0:   check: function() {
michael@0:     let stmt = mDBConn.createStatement(
michael@0:       "SELECT h.id FROM moz_places h " +
michael@0:       "JOIN moz_historyvisits v ON v.place_id = h.id AND visit_type NOT IN (0,4,7,8) " +
michael@0:       "GROUP BY h.id HAVING h.visit_count <> count(*) " +
michael@0:       "UNION ALL " +
michael@0:       "SELECT h.id FROM moz_places h " +
michael@0:       "JOIN moz_historyvisits v ON v.place_id = h.id " +
michael@0:       "GROUP BY h.id HAVING h.last_visit_date <> MAX(v.visit_date) "
michael@0:     );
michael@0:     do_check_false(stmt.executeStep());
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "L.3",
michael@0:   desc: "recalculate hidden for redirects.",
michael@0: 
michael@0:   setup: function() {
michael@0:     promiseAddVisits([
michael@0:       { uri: NetUtil.newURI("http://l3.moz.org/"),
michael@0:         transition: TRANSITION_TYPED },
michael@0:       { uri: NetUtil.newURI("http://l3.moz.org/redirecting/"),
michael@0:         transition: TRANSITION_TYPED },
michael@0:       { uri: NetUtil.newURI("http://l3.moz.org/redirecting2/"),
michael@0:         transition: TRANSITION_REDIRECT_TEMPORARY,
michael@0:         referrer: NetUtil.newURI("http://l3.moz.org/redirecting/") },
michael@0:       { uri: NetUtil.newURI("http://l3.moz.org/target/"),
michael@0:         transition: TRANSITION_REDIRECT_PERMANENT,
michael@0:         referrer: NetUtil.newURI("http://l3.moz.org/redirecting2/") },
michael@0:     ]);
michael@0:   },
michael@0: 
michael@0:   asyncCheck: function(aCallback) {
michael@0:     let stmt = mDBConn.createAsyncStatement(
michael@0:       "SELECT h.url FROM moz_places h WHERE h.hidden = 1"
michael@0:     );
michael@0:     stmt.executeAsync({
michael@0:       _count: 0,
michael@0:       handleResult: function(aResultSet) {
michael@0:         for (let row; (row = aResultSet.getNextRow());) {
michael@0:           let url = row.getResultByIndex(0);
michael@0:           do_check_true(/redirecting/.test(url));
michael@0:           this._count++;
michael@0:         }
michael@0:       },
michael@0:       handleError: function(aError) {
michael@0:       },
michael@0:       handleCompletion: function(aReason) {
michael@0:         dump_table("moz_places");
michael@0:         dump_table("moz_historyvisits");
michael@0:         do_check_eq(aReason, Ci.mozIStorageStatementCallback.REASON_FINISHED);
michael@0:         do_check_eq(this._count, 2);
michael@0:         aCallback();
michael@0:       }
michael@0:     });
michael@0:     stmt.finalize();
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: tests.push({
michael@0:   name: "Z",
michael@0:   desc: "Sanity: Preventive maintenance does not touch valid items",
michael@0: 
michael@0:   _uri1: uri("http://www1.mozilla.org"),
michael@0:   _uri2: uri("http://www2.mozilla.org"),
michael@0:   _folderId: null,
michael@0:   _bookmarkId: null,
michael@0:   _separatorId: null,
michael@0: 
michael@0:   setup: function() {
michael@0:     // use valid api calls to create a bunch of items
michael@0:     yield promiseAddVisits([
michael@0:       { uri: this._uri1 },
michael@0:       { uri: this._uri2 },
michael@0:     ]);
michael@0: 
michael@0:     this._folderId = bs.createFolder(bs.toolbarFolder, "testfolder",
michael@0:                                      bs.DEFAULT_INDEX);
michael@0:     do_check_true(this._folderId > 0);
michael@0:     this._bookmarkId = bs.insertBookmark(this._folderId, this._uri1,
michael@0:                                          bs.DEFAULT_INDEX, "testbookmark");
michael@0:     do_check_true(this._bookmarkId > 0);
michael@0:     this._separatorId = bs.insertSeparator(bs.unfiledBookmarksFolder,
michael@0:                                            bs.DEFAULT_INDEX);
michael@0:     do_check_true(this._separatorId > 0);
michael@0:     ts.tagURI(this._uri1, ["testtag"]);
michael@0:     fs.setAndFetchFaviconForPage(this._uri2, SMALLPNG_DATA_URI, false,
michael@0:                                  PlacesUtils.favicons.FAVICON_LOAD_NON_PRIVATE);
michael@0:     bs.setKeywordForBookmark(this._bookmarkId, "testkeyword");
michael@0:     as.setPageAnnotation(this._uri2, "anno", "anno", 0, as.EXPIRE_NEVER);
michael@0:     as.setItemAnnotation(this._bookmarkId, "anno", "anno", 0, as.EXPIRE_NEVER);
michael@0:   },
michael@0: 
michael@0:   asyncCheck: function (aCallback) {
michael@0:     // Check that all items are correct
michael@0:     PlacesUtils.asyncHistory.isURIVisited(this._uri1, function(aURI, aIsVisited) {
michael@0:       do_check_true(aIsVisited);
michael@0:       PlacesUtils.asyncHistory.isURIVisited(this._uri2, function(aURI, aIsVisited) {
michael@0:         do_check_true(aIsVisited);
michael@0: 
michael@0:         do_check_eq(bs.getBookmarkURI(this._bookmarkId).spec, this._uri1.spec);
michael@0:         do_check_eq(bs.getItemIndex(this._folderId), 0);
michael@0: 
michael@0:         do_check_eq(bs.getItemType(this._folderId), bs.TYPE_FOLDER);
michael@0:         do_check_eq(bs.getItemType(this._separatorId), bs.TYPE_SEPARATOR);
michael@0: 
michael@0:         do_check_eq(ts.getTagsForURI(this._uri1).length, 1);
michael@0:         do_check_eq(bs.getKeywordForBookmark(this._bookmarkId), "testkeyword");
michael@0:         do_check_eq(as.getPageAnnotation(this._uri2, "anno"), "anno");
michael@0:         do_check_eq(as.getItemAnnotation(this._bookmarkId, "anno"), "anno");
michael@0: 
michael@0:         fs.getFaviconURLForPage(this._uri2, function (aFaviconURI) {
michael@0:           do_check_true(aFaviconURI.equals(SMALLPNG_DATA_URI));
michael@0:           aCallback();
michael@0:         });
michael@0:       }.bind(this));
michael@0:     }.bind(this));
michael@0:   }
michael@0: });
michael@0: 
michael@0: //------------------------------------------------------------------------------
michael@0: 
michael@0: // main
michael@0: function run_test()
michael@0: {
michael@0:   run_next_test();
michael@0: }
michael@0: 
michael@0: add_task(function test_preventive_maintenance()
michael@0: {
michael@0:   // Force initialization of the bookmarks hash. This test could cause
michael@0:   // it to go out of sync due to direct queries on the database.
michael@0:   yield promiseAddVisits(uri("http://force.bookmarks.hash"));
michael@0:   do_check_false(bs.isBookmarked(uri("http://force.bookmarks.hash")));
michael@0: 
michael@0:   // Get current bookmarks max ID for cleanup
michael@0:   let stmt = mDBConn.createStatement("SELECT MAX(id) FROM moz_bookmarks");
michael@0:   stmt.executeStep();
michael@0:   defaultBookmarksMaxId = stmt.getInt32(0);
michael@0:   stmt.finalize();
michael@0:   do_check_true(defaultBookmarksMaxId > 0);
michael@0: 
michael@0:   for ([, test] in Iterator(tests)) {
michael@0:     dump("\nExecuting test: " + test.name + "\n" + "*** " + test.desc + "\n");
michael@0:     yield test.setup();
michael@0: 
michael@0:     let promiseMaintenanceFinished =
michael@0:         promiseTopicObserved(FINISHED_MAINTENANCE_NOTIFICATION_TOPIC);
michael@0:     PlacesDBUtils.maintenanceOnIdle();
michael@0:     yield promiseMaintenanceFinished;
michael@0: 
michael@0:     // Check the lastMaintenance time has been saved.
michael@0:     do_check_neq(Services.prefs.getIntPref("places.database.lastMaintenance"), null);
michael@0: 
michael@0:     if (test.asyncCheck) {
michael@0:       let deferred = Promise.defer();
michael@0:       test.asyncCheck(deferred.resolve);
michael@0:       yield deferred.promise;
michael@0:     } else {
michael@0:       test.check();
michael@0:     }
michael@0: 
michael@0:     cleanDatabase();
michael@0:   }
michael@0: 
michael@0:   // Sanity check: all roots should be intact
michael@0:   do_check_eq(bs.getFolderIdForItem(bs.placesRoot), 0);
michael@0:   do_check_eq(bs.getFolderIdForItem(bs.bookmarksMenuFolder), bs.placesRoot);
michael@0:   do_check_eq(bs.getFolderIdForItem(bs.tagsFolder), bs.placesRoot);
michael@0:   do_check_eq(bs.getFolderIdForItem(bs.unfiledBookmarksFolder), bs.placesRoot);
michael@0:   do_check_eq(bs.getFolderIdForItem(bs.toolbarFolder), bs.placesRoot);
michael@0: });