From dde4c7e1d75ded3c2a5e44e7fa89acde170a8a83 Mon Sep 17 00:00:00 2001 From: ByteHamster Date: Wed, 1 Apr 2020 13:58:05 +0200 Subject: Always join FeedMedia instead of doing two queries --- .../de/danoeh/antennapod/core/feed/FeedItem.java | 24 +- .../de/danoeh/antennapod/core/feed/FeedMedia.java | 22 +- .../danoeh/antennapod/core/storage/DBReader.java | 47 +--- .../antennapod/core/storage/FeedSearcher.java | 2 + .../antennapod/core/storage/PodDBAdapter.java | 242 ++++++++------------- 5 files changed, 120 insertions(+), 217 deletions(-) (limited to 'core/src/main') diff --git a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItem.java b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItem.java index ca387925f..24ba79b8b 100644 --- a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItem.java +++ b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedItem.java @@ -140,17 +140,17 @@ public class FeedItem extends FeedComponent implements ShownotesProvider, ImageR } public static FeedItem fromCursor(Cursor cursor) { - int indexId = cursor.getColumnIndex(PodDBAdapter.KEY_ID); - int indexTitle = cursor.getColumnIndex(PodDBAdapter.KEY_TITLE); - int indexLink = cursor.getColumnIndex(PodDBAdapter.KEY_LINK); - int indexPubDate = cursor.getColumnIndex(PodDBAdapter.KEY_PUBDATE); - int indexPaymentLink = cursor.getColumnIndex(PodDBAdapter.KEY_PAYMENT_LINK); - int indexFeedId = cursor.getColumnIndex(PodDBAdapter.KEY_FEED); - int indexHasChapters = cursor.getColumnIndex(PodDBAdapter.KEY_HAS_CHAPTERS); - int indexRead = cursor.getColumnIndex(PodDBAdapter.KEY_READ); - int indexItemIdentifier = cursor.getColumnIndex(PodDBAdapter.KEY_ITEM_IDENTIFIER); - int indexAutoDownload = cursor.getColumnIndex(PodDBAdapter.KEY_AUTO_DOWNLOAD); - int indexImageUrl = cursor.getColumnIndex(PodDBAdapter.KEY_IMAGE_URL); + int indexId = cursor.getColumnIndexOrThrow(PodDBAdapter.SELECT_KEY_ITEM_ID); + int indexTitle = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_TITLE); + int indexLink = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_LINK); + int indexPubDate = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_PUBDATE); + int indexPaymentLink = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_PAYMENT_LINK); + int indexFeedId = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_FEED); + int indexHasChapters = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_HAS_CHAPTERS); + int indexRead = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_READ); + int indexItemIdentifier = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_ITEM_IDENTIFIER); + int indexAutoDownload = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_AUTO_DOWNLOAD); + int indexImageUrl = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_IMAGE_URL); long id = cursor.getInt(indexId); String title = cursor.getString(indexTitle); @@ -359,7 +359,7 @@ public class FeedItem extends FeedComponent implements ShownotesProvider, ImageR public Callable loadShownotes() { return () -> { if (contentEncoded == null || description == null) { - DBReader.loadExtraInformationOfFeedItem(FeedItem.this); + DBReader.loadDescriptionOfFeedItem(FeedItem.this); } if (TextUtils.isEmpty(contentEncoded)) { return description; diff --git a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedMedia.java b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedMedia.java index 4414a03db..175f8e77b 100644 --- a/core/src/main/java/de/danoeh/antennapod/core/feed/FeedMedia.java +++ b/core/src/main/java/de/danoeh/antennapod/core/feed/FeedMedia.java @@ -98,17 +98,17 @@ public class FeedMedia extends FeedFile implements Playable { } public static FeedMedia fromCursor(Cursor cursor) { - int indexId = cursor.getColumnIndex(PodDBAdapter.KEY_ID); - int indexPlaybackCompletionDate = cursor.getColumnIndex(PodDBAdapter.KEY_PLAYBACK_COMPLETION_DATE); - int indexDuration = cursor.getColumnIndex(PodDBAdapter.KEY_DURATION); - int indexPosition = cursor.getColumnIndex(PodDBAdapter.KEY_POSITION); - int indexSize = cursor.getColumnIndex(PodDBAdapter.KEY_SIZE); - int indexMimeType = cursor.getColumnIndex(PodDBAdapter.KEY_MIME_TYPE); - int indexFileUrl = cursor.getColumnIndex(PodDBAdapter.KEY_FILE_URL); - int indexDownloadUrl = cursor.getColumnIndex(PodDBAdapter.KEY_DOWNLOAD_URL); - int indexDownloaded = cursor.getColumnIndex(PodDBAdapter.KEY_DOWNLOADED); - int indexPlayedDuration = cursor.getColumnIndex(PodDBAdapter.KEY_PLAYED_DURATION); - int indexLastPlayedTime = cursor.getColumnIndex(PodDBAdapter.KEY_LAST_PLAYED_TIME); + int indexId = cursor.getColumnIndexOrThrow(PodDBAdapter.SELECT_KEY_MEDIA_ID); + int indexPlaybackCompletionDate = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_PLAYBACK_COMPLETION_DATE); + int indexDuration = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_DURATION); + int indexPosition = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_POSITION); + int indexSize = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_SIZE); + int indexMimeType = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_MIME_TYPE); + int indexFileUrl = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_FILE_URL); + int indexDownloadUrl = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_DOWNLOAD_URL); + int indexDownloaded = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_DOWNLOADED); + int indexPlayedDuration = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_PLAYED_DURATION); + int indexLastPlayedTime = cursor.getColumnIndexOrThrow(PodDBAdapter.KEY_LAST_PLAYED_TIME); long mediaId = cursor.getLong(indexId); Date playbackCompletionDate = null; 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 2234fc35b..f30cbcd05 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 @@ -21,7 +21,6 @@ import de.danoeh.antennapod.core.feed.FeedMedia; import de.danoeh.antennapod.core.feed.FeedPreferences; import de.danoeh.antennapod.core.preferences.UserPreferences; import de.danoeh.antennapod.core.service.download.DownloadStatus; -import de.danoeh.antennapod.core.sync.model.EpisodeAction; import de.danoeh.antennapod.core.util.LongIntMap; import de.danoeh.antennapod.core.util.LongList; import de.danoeh.antennapod.core.util.comparator.DownloadStatusComparator; @@ -204,45 +203,15 @@ public final class DBReader { @NonNull private static List extractItemlistFromCursor(PodDBAdapter adapter, Cursor cursor) { List result = new ArrayList<>(cursor.getCount()); - - LongList itemIds = new LongList(cursor.getCount()); if (cursor.moveToFirst()) { + int indexMediaId = cursor.getColumnIndexOrThrow(PodDBAdapter.SELECT_KEY_MEDIA_ID); do { FeedItem item = FeedItem.fromCursor(cursor); result.add(item); - itemIds.add(item.getId()); - } while (cursor.moveToNext()); - Map medias = getFeedMedia(adapter, itemIds); - for (FeedItem item : result) { - FeedMedia media = medias.get(item.getId()); - item.setMedia(media); - if (media != null) { - media.setItem(item); + if (!cursor.isNull(indexMediaId)) { + item.setMedia(FeedMedia.fromCursor(cursor)); } - } - } - return result; - } - - private static Map getFeedMedia(PodDBAdapter adapter, LongList itemIds) { - List ids = new ArrayList<>(itemIds.size()); - for (long item : itemIds.toArray()) { - ids.add(String.valueOf(item)); - } - - Map result = new ArrayMap<>(itemIds.size()); - Cursor cursor = adapter.getFeedMediaCursor(ids.toArray(new String[0])); - try { - if (cursor.moveToFirst()) { - do { - int index = cursor.getColumnIndex(PodDBAdapter.KEY_FEEDITEM); - long itemId = cursor.getLong(index); - FeedMedia media = FeedMedia.fromCursor(cursor); - result.put(itemId, media); - } while (cursor.moveToNext()); - } - } finally { - cursor.close(); + } while (cursor.moveToNext()); } return result; } @@ -747,17 +716,17 @@ public final class DBReader { } /** - * Loads additional information about a FeedItem, e.g. shownotes + * Loads shownotes information about a FeedItem. * * @param item The FeedItem */ - public static void loadExtraInformationOfFeedItem(final FeedItem item) { - Log.d(TAG, "loadExtraInformationOfFeedItem() called with: " + "item = [" + item + "]"); + public static void loadDescriptionOfFeedItem(final FeedItem item) { + Log.d(TAG, "loadDescriptionOfFeedItem() called with: " + "item = [" + item + "]"); PodDBAdapter adapter = PodDBAdapter.getInstance(); adapter.open(); Cursor cursor = null; try { - cursor = adapter.getExtraInformationOfItem(item); + cursor = adapter.getDescriptionOfItem(item); if (cursor.moveToFirst()) { int indexDescription = cursor.getColumnIndex(PodDBAdapter.KEY_DESCRIPTION); String description = cursor.getString(indexDescription); diff --git a/core/src/main/java/de/danoeh/antennapod/core/storage/FeedSearcher.java b/core/src/main/java/de/danoeh/antennapod/core/storage/FeedSearcher.java index 9d75231d0..2f48cfc07 100644 --- a/core/src/main/java/de/danoeh/antennapod/core/storage/FeedSearcher.java +++ b/core/src/main/java/de/danoeh/antennapod/core/storage/FeedSearcher.java @@ -27,6 +27,7 @@ public class FeedSearcher { itemSearchTask.run(); return itemSearchTask.get(); } catch (ExecutionException | InterruptedException e) { + e.printStackTrace(); return Collections.emptyList(); } } @@ -38,6 +39,7 @@ public class FeedSearcher { feedSearchTask.run(); return feedSearchTask.get(); } catch (ExecutionException | InterruptedException e) { + e.printStackTrace(); return Collections.emptyList(); } } 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 81a1833ec..06ed59c26 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 @@ -7,7 +7,6 @@ import android.database.Cursor; import android.database.DatabaseErrorHandler; import android.database.DatabaseUtils; import android.database.DefaultDatabaseErrorHandler; -import android.database.MergeCursor; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; @@ -21,7 +20,6 @@ import org.apache.commons.io.FileUtils; import java.io.File; import java.io.IOException; -import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.Set; @@ -248,25 +246,6 @@ public class PodDBAdapter { TABLE_NAME_FEEDS + "." + KEY_FEED_PLAYBACK_SPEED }; - /** - * Select all columns from the feeditems-table except description and - * content-encoded. - */ - private static final String[] FEEDITEM_SEL_FI_SMALL = { - TABLE_NAME_FEED_ITEMS + "." + KEY_ID, - TABLE_NAME_FEED_ITEMS + "." + KEY_TITLE, - TABLE_NAME_FEED_ITEMS + "." + KEY_PUBDATE, - TABLE_NAME_FEED_ITEMS + "." + KEY_READ, - TABLE_NAME_FEED_ITEMS + "." + KEY_LINK, - TABLE_NAME_FEED_ITEMS + "." + KEY_PAYMENT_LINK, - TABLE_NAME_FEED_ITEMS + "." + KEY_MEDIA, - TABLE_NAME_FEED_ITEMS + "." + KEY_FEED, - TABLE_NAME_FEED_ITEMS + "." + KEY_HAS_CHAPTERS, - TABLE_NAME_FEED_ITEMS + "." + KEY_ITEM_IDENTIFIER, - TABLE_NAME_FEED_ITEMS + "." + KEY_IMAGE_URL, - TABLE_NAME_FEED_ITEMS + "." + KEY_AUTO_DOWNLOAD - }; - /** * All the tables in the database */ @@ -280,24 +259,51 @@ public class PodDBAdapter { TABLE_NAME_FAVORITES }; - /** - * Contains FEEDITEM_SEL_FI_SMALL as comma-separated list. Useful for raw queries. - */ - private static final String SEL_FI_SMALL_STR; - private static final String FEED_SEL_STD_STR; - - static { - String selFiSmall = Arrays.toString(FEEDITEM_SEL_FI_SMALL); - SEL_FI_SMALL_STR = selFiSmall.substring(1, selFiSmall.length() - 1); - String selFeedSmall = Arrays.toString(FEED_SEL_STD); - FEED_SEL_STD_STR = selFeedSmall.substring(1, selFeedSmall.length() - 1); - } - - /** - * Select id, description and content-encoded column from feeditems. - */ - private static final String[] SEL_FI_EXTRA = {KEY_ID, KEY_DESCRIPTION, - KEY_CONTENT_ENCODED, KEY_FEED}; + public static final String SELECT_KEY_ITEM_ID = "item_id"; + public static final String SELECT_KEY_MEDIA_ID = "media_id"; + + private static final String KEYS_FEED_ITEM_WITHOUT_DESCRIPTION = + TABLE_NAME_FEED_ITEMS + "." + KEY_ID + " AS " + SELECT_KEY_ITEM_ID + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_TITLE + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_PUBDATE + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_READ + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_LINK + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_PAYMENT_LINK + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_MEDIA + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_FEED + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_HAS_CHAPTERS + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_ITEM_IDENTIFIER + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_IMAGE_URL + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_AUTO_DOWNLOAD; + + private static final String KEYS_FEED_MEDIA = + TABLE_NAME_FEED_MEDIA + "." + KEY_ID + " AS " + SELECT_KEY_MEDIA_ID + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_DURATION + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_FILE_URL + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOAD_URL + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOADED + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_POSITION + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_SIZE + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_MIME_TYPE + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_PLAYBACK_COMPLETION_DATE + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_FEEDITEM + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_PLAYED_DURATION + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_HAS_EMBEDDED_PICTURE + ", " + + TABLE_NAME_FEED_MEDIA + "." + KEY_LAST_PLAYED_TIME; + + private static final String JOIN_FEED_ITEM_AND_MEDIA = " LEFT JOIN " + TABLE_NAME_FEED_MEDIA + + " ON " + TABLE_NAME_FEED_ITEMS + "." + KEY_ID + "=" + TABLE_NAME_FEED_MEDIA + "." + KEY_FEEDITEM + " "; + + private static final String SELECT_FEED_ITEMS_AND_MEDIA_WITH_DESCRIPTION = + "SELECT " + KEYS_FEED_ITEM_WITHOUT_DESCRIPTION + ", " + KEYS_FEED_MEDIA + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_DESCRIPTION + ", " + + TABLE_NAME_FEED_ITEMS + "." + KEY_CONTENT_ENCODED + + " FROM " + TABLE_NAME_FEED_ITEMS + + JOIN_FEED_ITEM_AND_MEDIA; + private static final String SELECT_FEED_ITEMS_AND_MEDIA = + "SELECT " + KEYS_FEED_ITEM_WITHOUT_DESCRIPTION + ", " + KEYS_FEED_MEDIA + + " FROM " + TABLE_NAME_FEED_ITEMS + + JOIN_FEED_ITEM_AND_MEDIA; private static Context context; @@ -892,23 +898,19 @@ public class PodDBAdapter { * @return The cursor of the query */ public final Cursor getAllItemsOfFeedCursor(final Feed feed) { - return getAllItemsOfFeedCursor(feed.getId()); - } - - private Cursor getAllItemsOfFeedCursor(final long feedId) { - return db.query(TABLE_NAME_FEED_ITEMS, FEEDITEM_SEL_FI_SMALL, KEY_FEED - + "=?", new String[]{String.valueOf(feedId)}, null, null, - null); + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + " WHERE " + TABLE_NAME_FEED_ITEMS + "." + KEY_FEED + "=" + feed.getId(); + return db.rawQuery(query, null); } /** - * Return a cursor with the SEL_FI_EXTRA selection of a single feeditem. + * Return the description and content_encoded of item */ - public final Cursor getExtraInformationOfItem(final FeedItem item) { - return db - .query(TABLE_NAME_FEED_ITEMS, SEL_FI_EXTRA, KEY_ID + "=?", - new String[]{String.valueOf(item.getId())}, null, - null, null); + public final Cursor getDescriptionOfItem(final FeedItem item) { + final String query = "SELECT " + KEY_DESCRIPTION + ", " + KEY_CONTENT_ENCODED + + " FROM " + TABLE_NAME_FEED_ITEMS + + " WHERE " + KEY_ID + "=" + item.getId(); + return db.rawQuery(query, null); } public final Cursor getSimpleChaptersOfFeedItemCursor(final FeedItem item) { @@ -936,13 +938,10 @@ public class PodDBAdapter { * cursor uses the FEEDITEM_SEL_FI_SMALL selection. */ public final Cursor getQueueCursor() { - Object[] args = new String[]{ - SEL_FI_SMALL_STR, - TABLE_NAME_FEED_ITEMS, TABLE_NAME_QUEUE, - TABLE_NAME_FEED_ITEMS + "." + KEY_ID, - TABLE_NAME_QUEUE + "." + KEY_FEEDITEM, - TABLE_NAME_QUEUE + "." + KEY_ID}; - String query = String.format("SELECT %s FROM %s INNER JOIN %s ON %s=%s ORDER BY %s", args); + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + " INNER JOIN " + TABLE_NAME_QUEUE + + " ON " + SELECT_KEY_ITEM_ID + " = " + TABLE_NAME_QUEUE + "." + KEY_FEEDITEM + + " ORDER BY " + TABLE_NAME_QUEUE + "." + KEY_ID; return db.rawQuery(query, null); } @@ -950,18 +949,12 @@ public class PodDBAdapter { return db.query(TABLE_NAME_QUEUE, new String[]{KEY_FEEDITEM}, null, null, null, null, KEY_ID + " ASC", null); } - public final Cursor getFavoritesCursor(int offset, int limit) { - Object[] args = new String[]{ - SEL_FI_SMALL_STR, - TABLE_NAME_FEED_ITEMS, TABLE_NAME_FAVORITES, - TABLE_NAME_FEED_ITEMS + "." + KEY_ID, - TABLE_NAME_FAVORITES + "." + KEY_FEEDITEM, - TABLE_NAME_FEED_ITEMS + "." + KEY_PUBDATE, - String.valueOf(offset), - String.valueOf(limit) - }; - String query = String.format("SELECT %s FROM %s INNER JOIN %s ON %s=%s ORDER BY %s DESC LIMIT %s, %s", args); + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + " INNER JOIN " + TABLE_NAME_FAVORITES + + " ON " + SELECT_KEY_ITEM_ID + " = " + TABLE_NAME_FAVORITES + "." + KEY_FEEDITEM + + " ORDER BY " + TABLE_NAME_FEED_ITEMS + "." + KEY_PUBDATE + " DESC" + + " LIMIT " + offset + ", " + limit; return db.rawQuery(query, null); } @@ -995,40 +988,31 @@ public class PodDBAdapter { * The returned cursor uses the FEEDITEM_SEL_FI_SMALL selection. */ public final Cursor getNewItemsCursor(int offset, int limit) { - Object[] args = new String[]{ - SEL_FI_SMALL_STR, - TABLE_NAME_FEED_ITEMS, - TABLE_NAME_FEEDS, - TABLE_NAME_FEED_ITEMS + "." + KEY_FEED + "=" + TABLE_NAME_FEEDS + "." + KEY_ID, - TABLE_NAME_FEED_ITEMS + "." + KEY_READ + "=" + FeedItem.NEW + " AND " + TABLE_NAME_FEEDS + "." + KEY_KEEP_UPDATED + " > 0", - KEY_PUBDATE + " DESC", - String.valueOf(offset), - String.valueOf(limit) - }; - final String query = String.format("SELECT %s FROM %s INNER JOIN %s ON %s WHERE %s " - + "ORDER BY %s LIMIT %s, %s", args); + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + " INNER JOIN " + TABLE_NAME_FEEDS + + " ON " + TABLE_NAME_FEED_ITEMS + "." + KEY_FEED + "=" + TABLE_NAME_FEEDS + "." + KEY_ID + + " WHERE " + TABLE_NAME_FEED_ITEMS + "." + KEY_READ + "=" + FeedItem.NEW + + " AND " + TABLE_NAME_FEEDS + "." + KEY_KEEP_UPDATED + " > 0" + + " ORDER BY " + TABLE_NAME_FEED_ITEMS + "." + KEY_PUBDATE + " DESC" + + " LIMIT " + offset + ", " + limit; return db.rawQuery(query, null); } public final Cursor getRecentlyPublishedItemsCursor(int offset, int limit) { - return db.query(TABLE_NAME_FEED_ITEMS, FEEDITEM_SEL_FI_SMALL, null, null, null, null, KEY_PUBDATE + " DESC LIMIT " + offset + ", " + limit); + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + "ORDER BY " + KEY_PUBDATE + " DESC LIMIT " + offset + ", " + limit; + return db.rawQuery(query, null); } public Cursor getDownloadedItemsCursor() { - final String query = "SELECT " + SEL_FI_SMALL_STR - + " FROM " + TABLE_NAME_FEED_ITEMS - + " INNER JOIN " + TABLE_NAME_FEED_MEDIA - + " ON " + TABLE_NAME_FEED_ITEMS + "." + KEY_ID + "=" + TABLE_NAME_FEED_MEDIA + "." + KEY_FEEDITEM - + " WHERE " + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOADED + ">0"; + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + "WHERE " + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOADED + " > 0"; return db.rawQuery(query, null); } public Cursor getPlayedItemsCursor() { - final String query = "SELECT " + SEL_FI_SMALL_STR - + " FROM " + TABLE_NAME_FEED_ITEMS - + " INNER JOIN " + TABLE_NAME_FEED_MEDIA - + " ON " + TABLE_NAME_FEED_ITEMS + "." + KEY_ID + "=" + TABLE_NAME_FEED_MEDIA + "." + KEY_FEEDITEM - + " WHERE " + TABLE_NAME_FEED_ITEMS + "." + KEY_READ + "=" + FeedItem.PLAYED; + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + "WHERE " + TABLE_NAME_FEED_ITEMS + "." + KEY_READ + "=" + FeedItem.PLAYED; return db.rawQuery(query, null); } @@ -1051,54 +1035,9 @@ public class PodDBAdapter { } public final Cursor getSingleFeedMediaCursor(long id) { - return db.query(TABLE_NAME_FEED_MEDIA, null, KEY_ID + "=?", new String[]{String.valueOf(id)}, null, null, null); - } - - public final Cursor getFeedMediaCursor(String... itemIds) { - int length = itemIds.length; - if (length > IN_OPERATOR_MAXIMUM) { - Log.w(TAG, "Length of id array is larger than " - + IN_OPERATOR_MAXIMUM + ". Creating multiple cursors"); - int numCursors = (int) (((double) length) / (IN_OPERATOR_MAXIMUM)) + 1; - Cursor[] cursors = new Cursor[numCursors]; - for (int i = 0; i < numCursors; i++) { - int neededLength; - String[] parts; - final int elementsLeft = length - i * IN_OPERATOR_MAXIMUM; - - if (elementsLeft >= IN_OPERATOR_MAXIMUM) { - neededLength = IN_OPERATOR_MAXIMUM; - parts = Arrays.copyOfRange(itemIds, i - * IN_OPERATOR_MAXIMUM, (i + 1) - * IN_OPERATOR_MAXIMUM); - } else { - neededLength = elementsLeft; - parts = Arrays.copyOfRange(itemIds, i - * IN_OPERATOR_MAXIMUM, (i * IN_OPERATOR_MAXIMUM) - + neededLength); - } - - cursors[i] = db.rawQuery("SELECT * FROM " - + TABLE_NAME_FEED_MEDIA + " WHERE " + KEY_FEEDITEM + " IN " - + buildInOperator(neededLength), parts); - } - Cursor result = new MergeCursor(cursors); - result.moveToFirst(); - return result; - } else { - return db.query(TABLE_NAME_FEED_MEDIA, null, KEY_FEEDITEM + " IN " - + buildInOperator(length), itemIds, null, null, null); - } - } - - /** - * Builds an IN-operator argument depending on the number of items. - */ - private String buildInOperator(int size) { - if (size == 1) { - return "(?)"; - } - return "(" + TextUtils.join(",", Collections.nCopies(size, "?")) + ")"; + final String query = "SELECT " + KEYS_FEED_MEDIA + " FROM " + TABLE_NAME_FEED_MEDIA + + " WHERE " + KEY_ID + "=" + id; + return db.rawQuery(query, null); } public final Cursor getFeedCursor(final long id) { @@ -1112,26 +1051,19 @@ public class PodDBAdapter { public final Cursor getFeedItemCursor(final String[] ids) { if (ids.length > IN_OPERATOR_MAXIMUM) { - throw new IllegalArgumentException( - "number of IDs must not be larger than " - + IN_OPERATOR_MAXIMUM - ); + throw new IllegalArgumentException("number of IDs must not be larger than " + IN_OPERATOR_MAXIMUM); } - - return db.query(TABLE_NAME_FEED_ITEMS, FEEDITEM_SEL_FI_SMALL, KEY_ID + " IN " - + buildInOperator(ids.length), ids, null, null, null); - + final String query = SELECT_FEED_ITEMS_AND_MEDIA + + " WHERE " + SELECT_KEY_ITEM_ID + " IN (" + TextUtils.join(",", ids) + ")"; + return db.rawQuery(query, null); } public final Cursor getFeedItemCursor(final String podcastUrl, final String episodeUrl) { String escapedPodcastUrl = DatabaseUtils.sqlEscapeString(podcastUrl); String escapedEpisodeUrl = DatabaseUtils.sqlEscapeString(episodeUrl); - final String query = "" - + "SELECT " + SEL_FI_SMALL_STR + " FROM " + TABLE_NAME_FEED_ITEMS + final String query = SELECT_FEED_ITEMS_AND_MEDIA + " INNER JOIN " + TABLE_NAME_FEEDS + " ON " + TABLE_NAME_FEED_ITEMS + "." + KEY_FEED + "=" + TABLE_NAME_FEEDS + "." + KEY_ID - + " INNER JOIN " + TABLE_NAME_FEED_MEDIA - + " ON " + TABLE_NAME_FEED_MEDIA + "." + KEY_FEEDITEM + "=" + TABLE_NAME_FEED_ITEMS + "." + KEY_ID + " WHERE " + TABLE_NAME_FEED_MEDIA + "." + KEY_DOWNLOAD_URL + "=" + escapedEpisodeUrl + " AND " + TABLE_NAME_FEEDS + "." + KEY_DOWNLOAD_URL + "=" + escapedPodcastUrl; Log.d(TAG, "SQL: " + query); @@ -1286,7 +1218,7 @@ public class PodDBAdapter { queryFeedId = "1 = 1"; } - String query = "SELECT " + SEL_FI_SMALL_STR + " FROM " + TABLE_NAME_FEED_ITEMS + String query = SELECT_FEED_ITEMS_AND_MEDIA_WITH_DESCRIPTION + " WHERE " + queryFeedId + " AND (" + KEY_DESCRIPTION + " LIKE '%" + preparedQuery + "%' OR " + KEY_CONTENT_ENCODED + " LIKE '%" + preparedQuery + "%' OR " @@ -1303,7 +1235,7 @@ public class PodDBAdapter { */ public Cursor searchFeeds(String searchQuery) { String preparedQuery = prepareSearchQuery(searchQuery); - String query = "SELECT " + FEED_SEL_STD_STR + " FROM " + TABLE_NAME_FEEDS + " WHERE " + String query = "SELECT * FROM " + TABLE_NAME_FEEDS + " WHERE " + KEY_TITLE + " LIKE '%" + preparedQuery + "%' OR " + KEY_CUSTOM_TITLE + " LIKE '%" + preparedQuery + "%' OR " + KEY_AUTHOR + " LIKE '%" + preparedQuery + "%' OR " -- cgit v1.2.3