From 85b897c7d778ae70d837ae0c2a9e7b4252ea8945 Mon Sep 17 00:00:00 2001 From: Sebastian Zeller Date: Tue, 20 Oct 2020 13:27:27 +0200 Subject: Filter the All Episodes tab via SQL Query Fixes #4414 --- .../antennapod/core/feed/FeedItemFilter.java | 52 ++++++++++++++++++++++ .../danoeh/antennapod/core/storage/DBReader.java | 25 +++++++++++ .../antennapod/core/storage/PodDBAdapter.java | 26 +++++++---- 3 files changed, 95 insertions(+), 8 deletions(-) (limited to 'core/src/main/java/de/danoeh') diff --git a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java index e8e478a86..b9d79715a 100644 --- a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java +++ b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItemFilter.java @@ -8,6 +8,7 @@ import java.util.Arrays; import java.util.List; import de.danoeh.antennapod.core.storage.DBReader; +import de.danoeh.antennapod.core.storage.PodDBAdapter; import de.danoeh.antennapod.core.util.LongList; import static de.danoeh.antennapod.core.feed.FeedItem.TAG_FAVORITE; @@ -15,6 +16,7 @@ import static de.danoeh.antennapod.core.feed.FeedItem.TAG_FAVORITE; public class FeedItemFilter { private final String[] mProperties; + private final String mQuery; private boolean showPlayed = false; private boolean showUnplayed = false; @@ -78,6 +80,45 @@ public class FeedItemFilter { break; } } + + mQuery = makeQuery(); + } + + private String makeQuery() { + // The keys used within this method, but explicitly combined with their table + String keyRead = PodDBAdapter.TABLE_NAME_FEED_ITEMS + "." + PodDBAdapter.KEY_READ; + String keyPosition = PodDBAdapter.TABLE_NAME_FEED_MEDIA + "." + PodDBAdapter.KEY_POSITION; + String keyDownloaded = PodDBAdapter.TABLE_NAME_FEED_MEDIA + "." + PodDBAdapter.KEY_DOWNLOADED; + String keyMediaId = PodDBAdapter.TABLE_NAME_FEED_MEDIA + "." + PodDBAdapter.KEY_ID; + String keyItemId = PodDBAdapter.TABLE_NAME_FEED_ITEMS + "." + PodDBAdapter.KEY_ID; + String keyFeedItem = PodDBAdapter.KEY_FEEDITEM; + String tableQueue = PodDBAdapter.TABLE_NAME_QUEUE; + String tableFavorites = PodDBAdapter.TABLE_NAME_FAVORITES; + + List statements = new ArrayList<>(); + if (showPlayed) statements.add(keyRead + " = 1 "); + if (showUnplayed) statements.add(" NOT " + keyRead + " = 1 "); // Match "New" items (read = -1) as well + if (showPaused) statements.add(" (" + keyPosition + " NOT NULL AND " + keyPosition + " > 0 " + ") "); + if (showNotPaused) statements.add(" (" + keyPosition + " IS NULL OR " + keyPosition + " = 0 " + ") "); + if (showQueued) statements.add(keyItemId + " IN (SELECT " + keyFeedItem + " FROM " + tableQueue + ") "); + if (showNotQueued) statements.add(keyItemId + " NOT IN (SELECT " + keyFeedItem + " FROM " + tableQueue + ") "); + if (showDownloaded) statements.add(keyDownloaded + " = 1 "); + if (showNotDownloaded) statements.add(keyDownloaded + " = 0 "); + if (showHasMedia) statements.add(keyMediaId + " NOT NULL "); + if (showNoMedia) statements.add(keyMediaId + " IS NULL "); + if (showIsFavorite) statements.add(keyItemId + " IN (SELECT " + keyFeedItem + " FROM " + tableFavorites + ") "); + if (showNotFavorite) statements.add(keyItemId + " NOT IN (SELECT " + keyFeedItem + " FROM " + tableFavorites + ") "); + + if (statements.isEmpty()) { + return ""; + } + StringBuilder query = new StringBuilder(" (" + statements.get(0)); + for (String r : statements.subList(1, statements.size())) { + query.append(" AND "); + query.append(r); + } + query.append(") "); + return query.toString(); } /** @@ -125,6 +166,17 @@ public class FeedItemFilter { return result; } + /** + * Express this filter using an SQL boolean statement that can be inserted into an SQL WHERE clause + * to yield output filtered according to the rules of this filter. + * + * @return An SQL boolean statement that matches the desired items, + * empty string if there is nothing to filter + */ + public String getQuery() { + return mQuery; + } + public String[] getValues() { return mProperties.clone(); } diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java b/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java index b218a73f9..2ba817b94 100644 --- a/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java +++ b/core/src/main/java/de/danoeh/antennapod/core/storage/DBReader.java @@ -17,6 +17,7 @@ import java.util.Map; import de.danoeh.antennapod.core.feed.Chapter; import de.danoeh.antennapod.core.feed.Feed; import de.danoeh.antennapod.core.feed.FeedItem; +import de.danoeh.antennapod.core.feed.FeedItemFilter; import de.danoeh.antennapod.core.feed.FeedMedia; import de.danoeh.antennapod.core.feed.FeedPreferences; import de.danoeh.antennapod.core.feed.SubscriptionsFilter; @@ -386,6 +387,30 @@ public final class DBReader { } } + /** + * Loads a filtered list of FeedItems sorted by pubDate in descending order. + * + * @param offset The first episode that should be loaded. + * @param limit The maximum number of episodes that should be loaded. + * @param filter The filter describing which episodes to filter out. + */ + @NonNull + public static List getRecentlyPublishedEpisodesFiltered(int offset, int limit, + FeedItemFilter filter) { + Log.d(TAG, "getRecentlyPublishedEpisodesFiltered() called with: " + + "offset = [" + offset + "]" + " limit = [" + limit + "]" + " filter = [" + filter.getQuery() + "]"); + + PodDBAdapter adapter = PodDBAdapter.getInstance(); + adapter.open(); + try (Cursor cursor = adapter.getRecentlyPublishedItemsCursorFiltered(offset, limit, filter)) { + List items = extractItemlistFromCursor(adapter, cursor); + loadAdditionalFeedItemListData(items); + return items; + } finally { + adapter.close(); + } + } + /** * Loads the playback history from the database. A FeedItem is in the playback history if playback of the correpsonding episode * has been completed at least once. diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java b/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java index 539bedd9f..a02cce504 100644 --- a/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java +++ b/core/src/main/java/de/danoeh/antennapod/core/storage/PodDBAdapter.java @@ -31,6 +31,7 @@ import java.util.Set; import de.danoeh.antennapod.core.feed.Chapter; import de.danoeh.antennapod.core.feed.Feed; import de.danoeh.antennapod.core.feed.FeedItem; +import de.danoeh.antennapod.core.feed.FeedItemFilter; import de.danoeh.antennapod.core.feed.FeedMedia; import de.danoeh.antennapod.core.feed.FeedPreferences; import de.danoeh.antennapod.core.preferences.UserPreferences; @@ -116,14 +117,14 @@ public class PodDBAdapter { public static final String KEY_FEED_SKIP_ENDING = "feed_skip_ending"; // Table names - static final String TABLE_NAME_FEEDS = "Feeds"; - static final String TABLE_NAME_FEED_ITEMS = "FeedItems"; - static final String TABLE_NAME_FEED_IMAGES = "FeedImages"; - static final String TABLE_NAME_FEED_MEDIA = "FeedMedia"; - static final String TABLE_NAME_DOWNLOAD_LOG = "DownloadLog"; - static final String TABLE_NAME_QUEUE = "Queue"; - static final String TABLE_NAME_SIMPLECHAPTERS = "SimpleChapters"; - static final String TABLE_NAME_FAVORITES = "Favorites"; + public static final String TABLE_NAME_FEEDS = "Feeds"; + public static final String TABLE_NAME_FEED_ITEMS = "FeedItems"; + public static final String TABLE_NAME_FEED_IMAGES = "FeedImages"; + public static final String TABLE_NAME_FEED_MEDIA = "FeedMedia"; + public static final String TABLE_NAME_DOWNLOAD_LOG = "DownloadLog"; + public static final String TABLE_NAME_QUEUE = "Queue"; + public static final String TABLE_NAME_SIMPLECHAPTERS = "SimpleChapters"; + public static final String TABLE_NAME_FAVORITES = "Favorites"; // SQL Statements for creating new tables private static final String TABLE_PRIMARY_KEY = KEY_ID @@ -1053,6 +1054,15 @@ public class PodDBAdapter { return db.rawQuery(query, null); } + public final Cursor getRecentlyPublishedItemsCursorFiltered(int offset, int limit, + FeedItemFilter filter) { + String filterQuery = filter.getQuery(); + String whereClause = "".equals(filterQuery) ? "" : " WHERE " + filter.getQuery(); + final String query = SELECT_FEED_ITEMS_AND_MEDIA + whereClause + + " ORDER BY " + KEY_PUBDATE + " DESC LIMIT " + offset + ", " + limit; + return db.rawQuery(query, null); + } + public Cursor getDownloadedItemsCursor() { final String query = SELECT_FEED_ITEMS_AND_MEDIA + "WHERE " + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOADED + " > 0"; -- cgit v1.2.3