--- a/mobile/android/base/java/org/mozilla/gecko/db/BrowserProvider.java
+++ b/mobile/android/base/java/org/mozilla/gecko/db/BrowserProvider.java
@@ -1098,16 +1098,17 @@ public class BrowserProvider extends Sha
try {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_TOPSITES);
db.execSQL("CREATE TEMP TABLE " + TABLE_TOPSITES + " AS" +
" SELECT " +
Bookmarks._ID + ", " +
Combined.BOOKMARK_ID + ", " +
Combined.HISTORY_ID + ", " +
+ Combined.HISTORY_GUID + ", " +
Bookmarks.URL + ", " +
Bookmarks.TITLE + ", " +
Combined.HISTORY_ID + ", " +
TopSites.TYPE_TOP + " AS " + TopSites.TYPE +
" FROM " + Combined.VIEW_NAME +
" WHERE " + ignoreForTopSitesWhereClause +
" ORDER BY " + BrowserContract.getCombinedFrecencySortOrder(true, false) +
" LIMIT " + totalLimit,
@@ -1118,16 +1119,17 @@ public class BrowserProvider extends Sha
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 + ", " +
" NULL " + " AS " + Combined.BOOKMARK_ID + ", " +
" -1 AS " + Combined.HISTORY_ID + ", " +
+ " NULL AS " + Combined.HISTORY_GUID + ", " +
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 " +
@@ -1141,68 +1143,91 @@ public class BrowserProvider extends Sha
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._ID + " AS " + Combined.BOOKMARK_ID + ", " +
" -1 AS " + Combined.HISTORY_ID + ", " +
+ " NULL AS " + Combined.HISTORY_GUID + ", " +
Bookmarks.URL + ", " +
Bookmarks.TITLE + ", " +
"NULL AS " + Combined.HISTORY_ID + ", " +
TopSites.TYPE_BLANK + " as " + TopSites.TYPE +
" FROM ( " + blanksBuilder.toString() + " )" +
blanksLimitClause + " )");
}
// If we retrieve more topsites than we have free positions for in the freeIdSubquery,
// we will have topsites that don't receive a position when joining TABLE_TOPSITES
// with freeIdSubquery. Hence we need to coalesce the position with a generated position.
// We know that the difference in positions will be at most suggestedGridLimit, hence we
// can add that to the rowid to generate a safe position.
// I.e. if we have 6 pinned sites then positions 0..5 are filled, the JOIN results in
// the first N rows having positions 6..(N+6), so row N+1 should receive a position that is at
// least N+1+6, which is equal to rowid + 6.
+ final String selectTopSites =
+ "SELECT " +
+ Bookmarks._ID + ", " +
+ TopSites.BOOKMARK_ID + ", " +
+ TopSites.HISTORY_ID + ", " +
+ Combined.HISTORY_GUID + ", " +
+ Bookmarks.URL + ", " +
+ Bookmarks.TITLE + ", " +
+ "COALESCE(" + Bookmarks.POSITION + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, "rowid") + " + " + suggestedGridLimit +
+ ")" + " AS " + 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._ID + " AS " + TopSites.BOOKMARK_ID + ", " +
+ " -1 AS " + TopSites.HISTORY_ID + ", " +
+ " NULL AS " + Combined.HISTORY_GUID + ", " +
+ Bookmarks.URL + ", " +
+ Bookmarks.TITLE + ", " +
+ Bookmarks.POSITION + ", " +
+ "NULL AS " + Combined.HISTORY_ID + ", " +
+ TopSites.TYPE_PINNED + " as " + TopSites.TYPE +
+ " " + pinnedSitesFromClause;
+
+ // In order to join the PageMetadata with our `SELECT ... UNION ALL SELECT ...` for top sites, the top sites
+ // SELECT must be a subquery (see https://stackoverflow.com/a/19110809/2219998).
final SQLiteCursor c = (SQLiteCursor) db.rawQuery(
- "SELECT " +
- Bookmarks._ID + ", " +
- TopSites.BOOKMARK_ID + ", " +
- TopSites.HISTORY_ID + ", " +
- Bookmarks.URL + ", " +
- Bookmarks.TITLE + ", " +
- "COALESCE(" + Bookmarks.POSITION + ", " +
- DBUtils.qualifyColumn(TABLE_TOPSITES, "rowid") + " + " + suggestedGridLimit +
- ")" + " AS " + 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._ID + " AS " + TopSites.BOOKMARK_ID + ", " +
- " -1 AS " + TopSites.HISTORY_ID + ", " +
- Bookmarks.URL + ", " +
- Bookmarks.TITLE + ", " +
- Bookmarks.POSITION + ", " +
- "NULL AS " + Combined.HISTORY_ID + ", " +
- TopSites.TYPE_PINNED + " as " + TopSites.TYPE +
- " " + pinnedSitesFromClause +
-
- // In case position is non-unique (as in Activity Stream pins, whose position
- // is always zero), we need to ensure we get stable ordering.
- " ORDER BY " + Bookmarks.POSITION + ", " + Bookmarks.URL,
-
- null);
+ // Specify a projection so we don't take the whole PageMetadata table, or the joining columns, with us.
+ "SELECT " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, Bookmarks._ID) + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, TopSites.BOOKMARK_ID) + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, TopSites.HISTORY_ID) + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, Bookmarks.URL) + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, Bookmarks.TITLE) + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, Bookmarks.POSITION) + ", " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, TopSites.TYPE) + ", " +
+ PageMetadata.JSON + " AS " + TopSites.PAGE_METADATA_JSON +
+
+ " FROM (" + selectTopSites + ") AS " + TABLE_TOPSITES +
+
+ " LEFT OUTER JOIN " + TABLE_PAGE_METADATA + " ON " +
+ DBUtils.qualifyColumn(TABLE_TOPSITES, Combined.HISTORY_GUID) + " = " +
+ DBUtils.qualifyColumn(TABLE_PAGE_METADATA, PageMetadata.HISTORY_GUID) +
+
+ // In case position is non-unique (as in Activity Stream pins, whose position
+ // is always zero), we need to ensure we get stable ordering.
+ " ORDER BY " + Bookmarks.POSITION + ", " + Bookmarks.URL,
+
+ null);
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