--- a/mobile/android/base/java/org/mozilla/gecko/db/BrowserProvider.java
+++ b/mobile/android/base/java/org/mozilla/gecko/db/BrowserProvider.java
@@ -5,37 +5,41 @@
package org.mozilla.gecko.db;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
+import org.mozilla.gecko.AboutPages;
+import org.mozilla.gecko.GeckoProfile;
import org.mozilla.gecko.db.BrowserContract.Bookmarks;
import org.mozilla.gecko.db.BrowserContract.Combined;
import org.mozilla.gecko.db.BrowserContract.FaviconColumns;
import org.mozilla.gecko.db.BrowserContract.Favicons;
import org.mozilla.gecko.db.BrowserContract.History;
import org.mozilla.gecko.db.BrowserContract.Schema;
import org.mozilla.gecko.db.BrowserContract.Tabs;
import org.mozilla.gecko.db.BrowserContract.Thumbnails;
+import org.mozilla.gecko.db.BrowserContract.TopSites;
import org.mozilla.gecko.db.DBUtils.UpdateOperation;
import org.mozilla.gecko.sync.Utils;
import android.content.ContentProviderOperation;
import android.content.ContentProviderResult;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.OperationApplicationException;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.MatrixCursor;
import android.database.SQLException;
+import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;
public class BrowserProvider extends SharedBrowserDatabaseProvider {
private static final String LOGTAG = "GeckoBrowserProvider";
@@ -93,16 +97,19 @@ public class BrowserProvider extends Sha
// Search Suggest matches. Obsolete.
static final int SEARCH_SUGGEST = 700;
// Thumbnail matches
static final int THUMBNAILS = 800;
static final int THUMBNAIL_ID = 801;
+ // Topsites matches
+ static final int TOPSITES = 900;
+
static final String DEFAULT_BOOKMARKS_SORT_ORDER = Bookmarks.TYPE
+ " ASC, " + Bookmarks.POSITION + " ASC, " + Bookmarks._ID
+ " ASC";
static final String DEFAULT_HISTORY_SORT_ORDER = History.DATE_LAST_VISITED + " DESC";
static final UriMatcher URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
@@ -217,16 +224,19 @@ public class BrowserProvider extends Sha
// Control
URI_MATCHER.addURI(BrowserContract.AUTHORITY, "control", CONTROL);
for (Table table : sTables) {
for (Table.ContentProviderInfo type : table.getContentProviderInfo()) {
URI_MATCHER.addURI(BrowserContract.AUTHORITY, type.name, type.id);
}
}
+
+ // Combined pinned sites, top visited sites, and suggested sites
+ URI_MATCHER.addURI(BrowserContract.AUTHORITY, "topsites", TOPSITES);
}
// Convenience accessor.
// Assumes structure of sTables!
private URLMetadataTable getURLMetadataTable() {
return (URLMetadataTable) sTables[0];
}
@@ -634,21 +644,239 @@ public class BrowserProvider extends Sha
}
}
}
debug("Updated " + updated + " rows for URI: " + uri);
return updated;
}
+ private Cursor getTopSites(final Uri uri) {
+ // In order to correctly merge the top and pinned sites we:
+ //
+ // 1. Generate a list of free ids for topsites - this is the positions that are NOT used by pinned sites.
+ // We do this using a subquery with a self-join in order to generate rowids, that allow us to join with
+ // the list of topsites.
+ // 2. Generate the list of topsites in order of frecency.
+ // 3. Join these, so that each topsite is given its resulting position
+ // 4. UNION all with the pinned sites, and order by position
+ //
+ // Suggested sites are placed after the topsites, but might still be interspersed with the suggested sites,
+ // hence we append these to the topsite list, and treat these identically to topsites from this point on.
+ //
+ // We require rowids to join the two lists, however subqueries aren't given rowids - hence we use two different
+ // tricks to generate these:
+ // 1. The list of free ids is small, hence we can do a self-join to generate rowids.
+ // 2. The topsites list is larger, hence we use a temporary table, which automatically provides rowids.
+
+ final SQLiteDatabase db = getWritableDatabase(uri);
+
+ final String TABLE_TOPSITES = "topsites";
+
+ final String totalLimit = uri.getQueryParameter(BrowserContract.PARAM_LIMIT);
+ final String suggestedGridLimit = uri.getQueryParameter(BrowserContract.PARAM_SUGGESTEDSITES_LIMIT);
+
+ final String[] suggestedGridLimitArgs = new String[] {
+ suggestedGridLimit
+ };
+
+ final String[] totalLimitArgs = new String[] {
+ totalLimit
+ };
+
+ final String pinnedSitesFromClause = "FROM " + TABLE_BOOKMARKS + " WHERE " + Bookmarks.PARENT + " == ?";
+ final String[] pinnedSitesArgs = new String[] {
+ String.valueOf(Bookmarks.FIXED_PINNED_LIST_ID)
+ };
+
+ // Ideally we'd use a recursive CTE to generate our sequence, e.g. something like this worked at one point:
+ // " WITH RECURSIVE" +
+ // " cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x < 6)" +
+ // However that requires SQLite >= 3.8.3 (available on Android >= 5.0), so in the meantime
+ // we use a temporary numbers table.
+ // Note: SQLite rowids are 1-indexed, whereas we're expecting 0-indexed values for the position. Our numbers
+ // table starts at position = 0, which ensures the correct results here.
+ final String freeIDSubquery =
+ " SELECT count(free_ids.position) + 1 AS rowid, numbers.position AS " + Bookmarks.POSITION +
+ " FROM (SELECT position FROM numbers WHERE position NOT IN (SELECT " + Bookmarks.POSITION + " " + pinnedSitesFromClause + ")) AS numbers" +
+ " LEFT OUTER JOIN " +
+ " (SELECT position FROM numbers WHERE position NOT IN (SELECT " + Bookmarks.POSITION + " " + pinnedSitesFromClause + ")) AS free_ids" +
+ " ON numbers.position > free_ids.position" +
+ " GROUP BY numbers.position" +
+ " ORDER BY numbers.position ASC" +
+ " LIMIT ?";
+
+ final String[] freeIDArgs = DBUtils.concatenateSelectionArgs(
+ pinnedSitesArgs,
+ pinnedSitesArgs,
+ suggestedGridLimitArgs);
+
+ // Filter out: unvisited pages (history_id == -1) pinned (and other special) sites, deleted sites,
+ // and about: pages.
+ final String ignoreForTopSitesWhereClause =
+ "(" + Combined.HISTORY_ID + " IS NOT -1)" +
+ " AND " +
+ Combined.URL + " NOT IN (SELECT " +
+ Bookmarks.URL + " FROM bookmarks WHERE " +
+ DBUtils.qualifyColumn("bookmarks", Bookmarks.PARENT) + " < ? AND " +
+ DBUtils.qualifyColumn("bookmarks", Bookmarks.IS_DELETED) + " == 0)" +
+ " AND " +
+ "(" + Combined.URL + " NOT LIKE ?)";
+
+ final String[] ignoreForTopSitesArgs = new String[] {
+ String.valueOf(Bookmarks.FIXED_ROOT_ID),
+ AboutPages.URL_FILTER
+ };
+
+
+ // Stuff the suggested sites into SQL: this allows us to filter pinned and topsites out of the suggested
+ // sites list as part of the final query (as opposed to walking cursors in java)
+ final SuggestedSites suggestedSites = GeckoProfile.get(getContext(), uri.getQueryParameter(BrowserContract.PARAM_PROFILE)).getDB().getSuggestedSites();
+
+ StringBuilder suggestedSitesBuilder = new StringBuilder();
+ // We could access the underlying data here, however SuggestedSites also performs filtering on the suggested
+ // sites list, which means we'd need to process the lists within SuggestedSites in any case. If we're doing
+ // that processing, there is little real between us using a MatrixCursor, or a Map (or List) instead of the
+ // MatrixCursor.
+ final Cursor suggestedSitesCursor = suggestedSites.get(Integer.parseInt(suggestedGridLimit));
+
+ String[] suggestedSiteArgs = new String[0];
+
+ boolean firstClause = true;
+
+ final int idColumnIndex = suggestedSitesCursor.getColumnIndexOrThrow(Bookmarks._ID);
+ final int urlColumnIndex = suggestedSitesCursor.getColumnIndexOrThrow(Bookmarks.URL);
+ final int titleColumnIndex = suggestedSitesCursor.getColumnIndexOrThrow(Bookmarks.TITLE);
+
+ while (suggestedSitesCursor.moveToNext()) {
+ // We'll be using this as a subquery, hence we need to avoid the preceding UNION ALL
+ if (!firstClause) {
+ suggestedSitesBuilder.append(" UNION ALL");
+ } else {
+ firstClause = false;
+ }
+ suggestedSitesBuilder.append(" SELECT" +
+ " ? AS " + Bookmarks._ID + "," +
+ " ? AS " + Bookmarks.URL + "," +
+ " ? AS " + Bookmarks.TITLE);
+
+ suggestedSiteArgs = DBUtils.appendSelectionArgs(suggestedSiteArgs,
+ new String[] {
+ suggestedSitesCursor.getString(idColumnIndex),
+ suggestedSitesCursor.getString(urlColumnIndex),
+ suggestedSitesCursor.getString(titleColumnIndex)
+ });
+ }
+
+ // To restrict suggested sites to the grid, we simply subtract the number of topsites (which have already had
+ // the pinned sites filtered out), and the number of pinned sites.
+ // SQLite completely ignores negative limits, hence we need to manually totalLimit to 0 in this case.
+ final String suggestedLimitClause = " LIMIT MAX(0, (? - (SELECT COUNT(*) FROM " + TABLE_TOPSITES + ") - (SELECT COUNT(*) " + pinnedSitesFromClause + "))) ";
+
+ final String[] suggestedLimitArgs = DBUtils.concatenateSelectionArgs(suggestedGridLimitArgs,
+ pinnedSitesArgs);
+
+ db.beginTransaction();
+ try {
+ db.execSQL("DROP TABLE IF EXISTS " + TABLE_TOPSITES);
+
+ db.execSQL("CREATE TEMP TABLE " + TABLE_TOPSITES + " AS" +
+ " SELECT " +
+ Bookmarks._ID + ", " +
+ Bookmarks.URL + ", " +
+ Bookmarks.TITLE + ", " +
+ Combined.HISTORY_ID + ", " +
+ TopSites.TYPE_TOP + " AS " + TopSites.TYPE +
+ " FROM " + Combined.VIEW_NAME +
+ " WHERE " + ignoreForTopSitesWhereClause +
+ " ORDER BY " + BrowserContract.getFrecencySortOrder(true, false) +
+ " LIMIT ?",
+
+ DBUtils.appendSelectionArgs(ignoreForTopSitesArgs,
+ totalLimitArgs));
+
+ db.execSQL("INSERT INTO " + TABLE_TOPSITES +
+ // We need to LIMIT _after_ selecting the relevant suggested sites, which requires us to
+ // use an additional internal subquery, since we cannot LIMIT a subquery that is part of UNION ALL.
+ // Hence the weird SELECT * FROM (SELECT ...relevant suggested sites... LIMIT ?)
+ " SELECT * FROM (SELECT " +
+ Bookmarks._ID + ", " +
+ Bookmarks.URL + ", " +
+ Bookmarks.TITLE + ", " +
+ "NULL AS " + Combined.HISTORY_ID + ", " +
+ TopSites.TYPE_SUGGESTED + " as " + TopSites.TYPE +
+ " FROM ( " + suggestedSitesBuilder.toString() + " )" +
+ " WHERE " +
+ Bookmarks.URL + " NOT IN (SELECT url FROM " + TABLE_TOPSITES + ")" +
+ " AND " +
+ Bookmarks.URL + " NOT IN (SELECT url " + pinnedSitesFromClause + ")" +
+ suggestedLimitClause + " )",
+
+ DBUtils.concatenateSelectionArgs(suggestedSiteArgs,
+ pinnedSitesArgs,
+ suggestedLimitArgs));
+
+ final SQLiteCursor c = (SQLiteCursor) db.rawQuery(
+ "SELECT " +
+ Bookmarks._ID + ", " +
+ Bookmarks.URL + ", " +
+ Bookmarks.TITLE + ", " +
+ Bookmarks.POSITION + ", " +
+ Combined.HISTORY_ID + ", " +
+ TopSites.TYPE +
+ " FROM " + TABLE_TOPSITES +
+ " LEFT OUTER JOIN " + // TABLE_IDS +
+ "(" + freeIDSubquery + ") AS id_results" +
+ " ON " + DBUtils.qualifyColumn(TABLE_TOPSITES, "rowid") +
+ " = " + DBUtils.qualifyColumn("id_results", "rowid") +
+
+ " UNION ALL " +
+
+ "SELECT " +
+ Bookmarks._ID + ", " +
+ Bookmarks.URL + ", " +
+ Bookmarks.TITLE + ", " +
+ Bookmarks.POSITION + ", " +
+ "NULL AS " + Combined.HISTORY_ID + ", " +
+ TopSites.TYPE_PINNED + " as " + TopSites.TYPE +
+ " FROM " + TABLE_BOOKMARKS +
+ " WHERE " + Bookmarks.PARENT + " == ? " +
+
+ " ORDER BY " + Bookmarks.POSITION,
+
+ DBUtils.appendSelectionArgs(freeIDArgs,
+ pinnedSitesArgs));
+
+ c.setNotificationUri(getContext().getContentResolver(),
+ BrowserContract.AUTHORITY_URI);
+
+ // Force the cursor to be compiled and the cursor-window filled now:
+ // (A) without compiling the cursor now we won't have access to the TEMP table which
+ // is removed as soon as we close our connection.
+ // (B) this might also mitigate the situation causing this crash where we're accessing
+ // a cursor and crashing in fillWindow.
+ c.moveToFirst();
+
+ db.setTransactionSuccessful();
+ return c;
+ } finally {
+ db.endTransaction();
+ }
+ }
+
@Override
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
+ final int match = URI_MATCHER.match(uri);
+
+ if (match == TOPSITES) {
+ return getTopSites(uri);
+ }
+
SQLiteDatabase db = getReadableDatabase(uri);
- final int match = URI_MATCHER.match(uri);
SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
String limit = uri.getQueryParameter(BrowserContract.PARAM_LIMIT);
String groupBy = null;
switch (match) {
case BOOKMARKS_FOLDER_ID:
case BOOKMARKS_ID: