Small database efficiency tweaks (#7058)

- When checking whether there is a subscription, there is no need to create feed objects (plus counters etc). Just the number of episodes is enough.
- Downloads section only needs to load the items it actually displays.
- No need to load FeedMedia, just to load FeedItem including the same FeedMedia afterwards.
- No need to convert columns to Strings and back to Longs.
- No need to join favorites when we are only interested in the list of IDs anyway.

2 files changed, 20 insertions, 33 deletions

diff --git a/storage/database/src/main/java/de/danoeh/antennapod/storage/database/DBReader.java b/storage/database/src/main/java/de/danoeh/antennapod/storage/database/DBReader.java
index 7335e056d..4fdcee987 100644
--- a/storage/database/src/main/java/de/danoeh/antennapod/storage/database/DBReader.java
+++ b/storage/database/src/main/java/de/danoeh/antennapod/storage/database/DBReader.java
@@ -29,7 +29,6 @@ import de.danoeh.antennapod.storage.database.mapper.ChapterCursor;
 import de.danoeh.antennapod.storage.database.mapper.DownloadResultCursor;
 import de.danoeh.antennapod.storage.database.mapper.FeedCursor;
 import de.danoeh.antennapod.storage.database.mapper.FeedItemCursor;
-import de.danoeh.antennapod.storage.database.mapper.FeedMediaCursor;
 
 /**
  * Provides methods for reading data from the AntennaPod database.
@@ -223,7 +222,7 @@ public final class DBReader {
 
         PodDBAdapter adapter = PodDBAdapter.getInstance();
         adapter.open();
-        try (Cursor cursor = adapter.getFavoritesIdsCursor(0, Integer.MAX_VALUE)) {
+        try (Cursor cursor = adapter.getFavoritesIdsCursor()) {
             LongList favoriteIDs = new LongList(cursor.getCount());
             while (cursor.moveToNext()) {
                 favoriteIDs.add(cursor.getLong(0));
@@ -511,20 +510,13 @@ public final class DBReader {
         PodDBAdapter adapter = PodDBAdapter.getInstance();
         adapter.open();
 
-        try (FeedMediaCursor mediaCursor = new FeedMediaCursor(adapter.getSingleFeedMediaCursor(mediaId))) {
-            if (!mediaCursor.moveToFirst()) {
+        try (FeedItemCursor itemCursor = new FeedItemCursor(adapter.getFeedItemFromMediaIdCursor(mediaId))) {
+            if (!itemCursor.moveToFirst()) {
                 return null;
             }
-
-            int indexFeedItem = mediaCursor.getColumnIndex(PodDBAdapter.KEY_FEEDITEM);
-            long itemId = mediaCursor.getLong(indexFeedItem);
-            FeedMedia media = mediaCursor.getFeedMedia();
-            FeedItem item = getFeedItem(itemId);
-            if (item != null) {
-                media.setItem(item);
-                item.setMedia(media);
-            }
-            return media;
+            FeedItem item = itemCursor.getFeedItem();
+            loadAdditionalFeedItemListData(Collections.singletonList(item));
+            return item.getMedia();
         } finally {
             adapter.close();
         }
@@ -623,13 +615,13 @@ public final class DBReader {
             while (cursor.moveToNext()) {
                 Feed feed = cursor.getFeed();
 
-                long feedPlayedTime = Long.parseLong(cursor.getString(indexPlayedTime)) / 1000;
-                long feedTotalTime = Long.parseLong(cursor.getString(indexTotalTime)) / 1000;
-                long episodes = Long.parseLong(cursor.getString(indexNumEpisodes));
-                long episodesStarted = Long.parseLong(cursor.getString(indexEpisodesStarted));
-                long totalDownloadSize = Long.parseLong(cursor.getString(indexDownloadSize));
-                long episodesDownloadCount = Long.parseLong(cursor.getString(indexNumDownloaded));
-                long oldestDate = Long.parseLong(cursor.getString(indexOldestDate));
+                long feedPlayedTime = cursor.getLong(indexPlayedTime) / 1000;
+                long feedTotalTime = cursor.getLong(indexTotalTime) / 1000;
+                long episodes = cursor.getLong(indexNumEpisodes);
+                long episodesStarted = cursor.getLong(indexEpisodesStarted);
+                long totalDownloadSize = cursor.getLong(indexDownloadSize);
+                long episodesDownloadCount = cursor.getLong(indexNumDownloaded);
+                long oldestDate = cursor.getLong(indexOldestDate);
 
                 if (episodes > 0 && oldestDate < Long.MAX_VALUE) {
                     result.oldestDate = Math.min(result.oldestDate, oldestDate);
@@ -648,7 +640,7 @@ public final class DBReader {
         adapter.open();
         try (Cursor cursor = adapter.getTimeBetweenReleaseAndPlayback(timeFilterFrom, timeFilterTo)) {
             cursor.moveToFirst();
-            long result = Long.parseLong(cursor.getString(0));
+            long result = cursor.getLong(0);
             adapter.close();
             return result;
         }
diff --git a/storage/database/src/main/java/de/danoeh/antennapod/storage/database/PodDBAdapter.java b/storage/database/src/main/java/de/danoeh/antennapod/storage/database/PodDBAdapter.java
index 4e8d8133f..b77cd06d2 100644
--- a/storage/database/src/main/java/de/danoeh/antennapod/storage/database/PodDBAdapter.java
+++ b/storage/database/src/main/java/de/danoeh/antennapod/storage/database/PodDBAdapter.java
@@ -1043,14 +1043,9 @@ public class PodDBAdapter {
         return db.rawQuery(query, null);
     }
 
-    public final Cursor getFavoritesIdsCursor(int offset, int limit) {
-        // Way faster than selecting all columns
-        final String query = "SELECT " + TABLE_NAME_FEED_ITEMS + "." + KEY_ID
-                + " FROM " + TABLE_NAME_FEED_ITEMS
-                + " INNER JOIN " + TABLE_NAME_FAVORITES
-                + " ON " + TABLE_NAME_FEED_ITEMS + "." + KEY_ID + " = " + TABLE_NAME_FAVORITES + "." + KEY_FEEDITEM
-                + " ORDER BY " + TABLE_NAME_FEED_ITEMS + "." + KEY_PUBDATE + " DESC"
-                + " LIMIT " + offset + ", " + limit;
+    public final Cursor getFavoritesIdsCursor() {
+        final String query = "SELECT " + TABLE_NAME_FAVORITES + "." + KEY_FEEDITEM
+                + " FROM " + TABLE_NAME_FAVORITES;
         return db.rawQuery(query, null);
     }
 
@@ -1109,9 +1104,9 @@ public class PodDBAdapter {
         return "((" + SELECT_KEY_ITEM_ID + " * " + seed + ") % 46471)";
     }
 
-    public final Cursor getSingleFeedMediaCursor(long id) {
-        final String query = "SELECT " + KEYS_FEED_MEDIA + " FROM " + TABLE_NAME_FEED_MEDIA
-                + " WHERE " + KEY_ID + "=" + id;
+    public final Cursor getFeedItemFromMediaIdCursor(long mediaId) {
+        final String query = SELECT_FEED_ITEMS_AND_MEDIA
+                + " WHERE " + SELECT_KEY_MEDIA_ID + " = " + mediaId;
         return db.rawQuery(query, null);
     }