/** * Created by anandgaurav on 12/02/18. */ @Entity(tableName = "users") public class User { @PrimaryKey public Long id; public String name; }
public UserDBHelper(Context context) { appDatabase = Room.databaseBuilder(context, AppDatabase.class, "User.db") .allowMainThreadQueries() .build(); inMemoryAppDatabase = Room.inMemoryDatabaseBuilder(context, AppDatabase.class) .allowMainThreadQueries() .build(); }
@Transaction @Query("SELECT * FROM " + STREAM_TABLE + " INNER JOIN " + // get ids of streams of the given playlist "(SELECT " + JOIN_STREAM_ID + "," + JOIN_INDEX + " FROM " + PLAYLIST_STREAM_JOIN_TABLE + " WHERE " + JOIN_PLAYLIST_ID + " = :playlistId)" + // then merge with the stream metadata " ON " + STREAM_ID + " = " + JOIN_STREAM_ID + " ORDER BY " + JOIN_INDEX + " ASC") public abstract Flowable<List<PlaylistStreamEntry>> getOrderedStreamsOf(long playlistId);
@Query("SELECT * FROM " + STREAM_TABLE + " INNER JOIN " + STREAM_HISTORY_TABLE + " ON " + STREAM_ID + " = " + JOIN_STREAM_ID + " ORDER BY " + STREAM_ACCESS_DATE + " DESC") public abstract Flowable<List<StreamHistoryEntry>> getHistory();
private static AppDatabase getDatabase(Context context) { return Room .databaseBuilder(context.getApplicationContext(), AppDatabase.class, DATABASE_NAME) .addMigrations(MIGRATION_11_12) .fallbackToDestructiveMigration() .build(); }
/** * Created by anandgaurav on 12/02/18. */ @Database(entities = {User.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { public abstract UserDao userDao(); }
@Query("DELETE FROM " + STREAM_TABLE + " WHERE " + STREAM_ID + " NOT IN " + "(SELECT DISTINCT " + STREAM_ID + " FROM " + STREAM_TABLE + " LEFT JOIN " + STREAM_HISTORY_TABLE + " ON " + STREAM_ID + " = " + StreamHistoryEntity.STREAM_HISTORY_TABLE + "." + StreamHistoryEntity.JOIN_STREAM_ID + " LEFT JOIN " + PLAYLIST_STREAM_JOIN_TABLE + " ON " + STREAM_ID + " = " + PlaylistStreamEntity.PLAYLIST_STREAM_JOIN_TABLE + "." + PlaylistStreamEntity.JOIN_STREAM_ID + ")") public abstract int deleteOrphans(); }
@Transaction @Query("SELECT " + PLAYLIST_ID + ", " + PLAYLIST_NAME + ", " + PLAYLIST_THUMBNAIL_URL + ", " + "COALESCE(COUNT(" + JOIN_PLAYLIST_ID + "), 0) AS " + PLAYLIST_STREAM_COUNT + " FROM " + PLAYLIST_TABLE + " LEFT JOIN " + PLAYLIST_STREAM_JOIN_TABLE + " ON " + PLAYLIST_ID + " = " + JOIN_PLAYLIST_ID + " GROUP BY " + JOIN_PLAYLIST_ID + " ORDER BY " + PLAYLIST_NAME + " COLLATE NOCASE ASC") public abstract Flowable<List<PlaylistMetadataEntry>> getPlaylistMetadata(); }
@Query("SELECT * FROM " + STREAM_TABLE + // Select the latest entry and watch count for each stream id on history table " INNER JOIN " + "(SELECT " + JOIN_STREAM_ID + ", " + " MAX(" + STREAM_ACCESS_DATE + ") AS " + STREAM_LATEST_DATE + ", " + " SUM(" + STREAM_REPEAT_COUNT + ") AS " + STREAM_WATCH_COUNT + " FROM " + STREAM_HISTORY_TABLE + " GROUP BY " + JOIN_STREAM_ID + ")" + " ON " + STREAM_ID + " = " + JOIN_STREAM_ID) public abstract Flowable<List<StreamStatisticsEntry>> getStatistics(); }
@Query("SELECT * FROM " + SUBSCRIPTION_TABLE + " WHERE " + SUBSCRIPTION_URL + " LIKE :url AND " + SUBSCRIPTION_SERVICE_ID + " = :serviceId") public abstract Flowable<List<SubscriptionEntity>> getSubscription(int serviceId, String url);
@Override @Query("SELECT * FROM " + REMOTE_PLAYLIST_TABLE + " WHERE " + REMOTE_PLAYLIST_SERVICE_ID + " = :serviceId") public abstract Flowable<List<PlaylistRemoteEntity>> listByService(int serviceId);
@Query("SELECT " + SUBSCRIPTION_UID + " FROM " + SUBSCRIPTION_TABLE + " WHERE " + SUBSCRIPTION_URL + " LIKE :url AND " + SUBSCRIPTION_SERVICE_ID + " = :serviceId") abstract Long getSubscriptionIdInternal(int serviceId, String url);
@Query("SELECT * FROM " + STREAM_TABLE + " WHERE " + STREAM_URL + " = :url AND " + STREAM_SERVICE_ID + " = :serviceId") public abstract Flowable<List<StreamEntity>> getStream(long serviceId, String url);
@Query("SELECT * FROM " + REMOTE_PLAYLIST_TABLE + " WHERE " + REMOTE_PLAYLIST_URL + " = :url AND " + REMOTE_PLAYLIST_SERVICE_ID + " = :serviceId") public abstract Flowable<List<PlaylistRemoteEntity>> getPlaylist(long serviceId, String url);
@Query("SELECT COALESCE(MAX(" + JOIN_INDEX + "), -1)" + " FROM " + PLAYLIST_STREAM_JOIN_TABLE + " WHERE " + JOIN_PLAYLIST_ID + " = :playlistId") public abstract Flowable<Integer> getMaximumIndexOf(final long playlistId);
@Query("DELETE FROM " + REMOTE_PLAYLIST_TABLE + " WHERE " + REMOTE_PLAYLIST_ID + " = :playlistId") public abstract int deletePlaylist(final long playlistId); }
@Query("SELECT " + REMOTE_PLAYLIST_ID + " FROM " + REMOTE_PLAYLIST_TABLE + " WHERE " + REMOTE_PLAYLIST_URL + " = :url AND " + REMOTE_PLAYLIST_SERVICE_ID + " = :serviceId") abstract Long getPlaylistIdInternal(long serviceId, String url);
@Query("SELECT " + STREAM_ID + " FROM " + STREAM_TABLE + " WHERE " + STREAM_URL + " = :url AND " + STREAM_SERVICE_ID + " = :serviceId") abstract Long getStreamIdInternal(long serviceId, String url);
@Override @Query("DELETE FROM " + PLAYLIST_STREAM_JOIN_TABLE) public abstract int deleteAll();
@Override @Query("DELETE FROM " + STREAM_TABLE) public abstract int deleteAll();