I'm now trying to figure out how to insert, update, or delete a Playlist
Playlist is a POJO and is intended (when used with Embedded/@Relation) for extracting data from the database not for modifying data in the database. i.e. it's one way.
However, if it were not a Data Class but a Class (I think) then you could add functions to perform such actions. e.g.
class Playlist {
@Embedded
var playlistInfo: PlaylistInfo? = null
@Relation(
parentColumn = "playlistId",
entityColumn = "songId",
associateBy = Junction(PlaylistSongCrossRef::class)
)
var songs: MutableList<Song>? = null
fun deletePlayList(dao: AllDao, playlist: Playlist, deleteOrphanedSongs: Boolean = false) {
val currentPlaylistInfo: PlaylistInfo? = playlist.playlistInfo
if (currentPlaylistInfo != null) {
dao.deleteByPlayListId(currentPlaylistInfo.playlistId)
dao.delete(currentPlaylistInfo)
if (deleteOrphanedSongs) {
dao.deleteManySongs(playlist.songs!!)
}
}
}
}
What you can do, with an Extracted Playlist is to say delete Playlist and Songs such as like:-
@Delete()
fun deletePlayListInfoAndSongs(playlistInfo: PlaylistInfo, songs: List<Song>): Int
- i.e. with an extracted PlayList you'd have the Embedded PlaylistInfo and the related Songs
However, be WARNED, the above will not delete the associated PlaylistSongCrossRef rows, so you would end up with orphans which could then be problematic.
You may wish to consider defining Foreign keys and using the onDelete and/or onUpdate actions to CASCADE deletions.
Example/Demo
The following is an example based upon your Entities (Song path changed to String
rather than URI for convenience) and the Playlist class as above.
The Dao's for your consideration are :-
@Dao
interface AllDao {
@Insert
fun insert(playlistInfo: PlaylistInfo): Long
@Insert
fun insertManyPlaylistInfos(playlistInfoList: List<PlaylistInfo>): LongArray
@Insert
fun insert(song: Song): Long
@Insert
fun insertManySongs(songList: List<Song>): LongArray
@Insert
fun insert(playlistSongCrossRef: PlaylistSongCrossRef): Long
@Insert
fun insertManyPlaylistSongCrossrefs(playlistSongCrossRefList: List<PlaylistSongCrossRef>): LongArray
@Query("SELECT * FROM song")
fun getAllSongs(): List<Song>
@Query("SELECT * FROM song WHERE song.name = :songname LIMIT 1")
fun getFirstSongByName(songname: String): Song
@Query("SELECT * FROM playlistInfo")
fun getAllPlaylistInfos(): List<PlaylistInfo>
@Query("SELECT * FROM playlistinfo WHERE playlistinfo.name = :playlistname LIMIT 1")
fun getFirstPlaylistInfoByName(playlistname: String): PlaylistInfo
@Query("SELECT * FROM playlistsongcrossref")
fun getAllPlaylistSongCrossRefs(): List<PlaylistSongCrossRef>
@Transaction
@Query("SELECT * FROM playlistinfo")
fun getAllPlaylists(): List<Playlist>
@Query("SELECT count(*) FROM song")
fun getNumberOfSongs(): Long
@Query("SELECT count(*) FROM playlistinfo")
fun getNumberOfPlaylistInfos(): Long
@Query("SELECT count(*) FROM playlistsongcrossref")
fun getNumberOfSongsInPlaylists(): Long
@Update
fun update(song: Song)
@Update
fun update(playlistInfo: PlaylistInfo)
/* Suggested NEVER to use */
@Update
fun update(playlistSongCrossRef: PlaylistSongCrossRef)
@Delete
fun delete(playlistInfo: PlaylistInfo): Int
@Delete()
fun delete(song: Song): Int
@Delete
fun deleteManySongs(songs: List<Song>): Int
@Delete
fun delete(playlistSongCrossRef: PlaylistSongCrossRef): Int
@Delete()
fun deletePlayListInfoAndSongs(playlistInfo: PlaylistInfo, songs: List<Song>): Int
//@Delete
//fun deletePlayListInfoAndSongs(playlist: Playlist): Int
@Query("DELETE FROM playlistsongcrossref WHERE playlistId = :playlistId")
fun deleteByPlayListId(playlistId: Long): Int
@Query("DELETE FROM playlistsongcrossref WHERE playlistId = :playlistId AND songId = :songId")
fun deleteSongFromPlaylist(playlistId: Long, songId: Long)
}
The perhaps consider the following in an Activity that shows examples that update, delete and insert :-
class MainActivity : AppCompatActivity() {
private val TAG = "PLDBINFO"
lateinit var db: TheDatabase
lateinit var dao: AllDao
val songList = listOf<Song>(
Song(1L,"My Song","x"),
Song(2L,"Your Song","Y"),
Song(3L,"Her Song","Z"),
Song(4L,"His Song","A"),
Song(5L,"Their Song","B"),
Song(6L,"Nobody's Song","C")
)
val playlistInfoList = listOf<PlaylistInfo>(
PlaylistInfo("My PlayList"),
PlaylistInfo("Your PlayList"),
PlaylistInfo("Her PlayList")
)
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
// Get the Database and the Dao
db = Room.databaseBuilder(this,TheDatabase::class.java,"playlist.db")
.allowMainThreadQueries()
.build()
dao = db.getDao()
// Add all the Playlist and Songs and add every Song to all the Playlists
// ONLY IF no data exists
if (dao.getNumberOfPlaylistInfos() + dao.getNumberOfPlaylistInfos() + dao.getNumberOfSongsInPlaylists() < 1) {
dao.insertManySongs(songList)
dao.insertManyPlaylistInfos(playlistInfoList)
for(p: PlaylistInfo in dao.getAllPlaylistInfos()) {
for(s: Song in dao.getAllSongs()) {
dao.insert(PlaylistSongCrossRef(p.playlistId,s.songId))
}
}
}
// Write the counts to the Log
logStats()
// Extract All Core Objects
var allPlaylistInfos = dao.getAllPlaylists()
var allSongs = dao.getAllSongs()
var allPlaylists = dao.getAllPlaylists()
var allPlaylistSongCrossRefs = dao.getAllPlaylistSongCrossRefs()
Log.d(TAG,"Extracted PlaylistInfos = ${allPlaylistInfos.size} " +
"Extacted Songs = ${allSongs.size} " +
"Extracted Playlists = ${allPlaylists.size} " +
"Extracted PlaylistSongCrossRefs = ${allPlaylistSongCrossRefs.size}"
)
// Add a new song and also put it into the My PlayList playlist
dao.insert(PlaylistSongCrossRef(dao.getFirstPlaylistInfoByName("My PlayList").playlistId,dao.insert(Song(10L,"Bert's Song","D"))))
// ASame as above BUT using function getPlaylistInfoIdByName below that gets the ID
dao.insert(PlaylistSongCrossRef(getPlaylistInfoIdByName("My PlayList"),dao.insert(Song(20L,"Mary's Song","E"))))
//
var otherPlTodelete = dao.getFirstPlaylistInfoByName("Her PlayList")
for(pl: Playlist in allPlaylistInfos) {
/* Best to not use this
if (pl.playlistInfo!!.playlistId == plToDelete.playlistId ) {
dao.deletePlayListInfoAndSongs(pl.playlistInfo!!,pl.songs!!)
}
*/
if (pl.playlistInfo!!.playlistId == otherPlTodelete.playlistId) {
pl.deletePlayList(dao,pl,false) /* best to only use false */
}
}
var playlistToUpdate = dao.getFirstPlaylistInfoByName("Your PlayList")
playlistToUpdate.name = "PlayList that is for Your but it was Your PlayList"
dao.update(playlistToUpdate)
logStats()
}
private fun logStats() {
Log.d(TAG," Playlist Count = " + dao.getNumberOfPlaylistInfos()
+ " Song Count = " + dao.getNumberOfSongs()
+ " Song/Playlist count = " + dao.getNumberOfSongsInPlaylists()
)
}
private fun getSongIdByName(songName: String): Long {
return dao.getFirstSongByName(songName).songId
}
private fun getPlaylistInfoIdByName(playlistInfoName: String): Long {
return dao.getFirstPlaylistInfoByName(playlistInfoName).playlistId
}
}
First if no data exists (example of getting row counts via a query) then 3 Playlists and 6 songs are added with each play list containing all songs (so 18 cross reference rows).
- Obviously this would not be the real case scenario (nut it's pretty to convenient/easy for testing)
Then the stats (rows per table are output to the log).
Then all types (PlaylistInfo's, Song's, PlaylistSongCrossref's and PlayList's) are extracted and the counts displayed (should tally with stats).
Then two new songs are added not only as Songs but also to the PlayList named "My PlayList"
The Playlistinfo (aka PlayList) named "Her PlayList" is found according to the name. It is deleted along with the PlayListSongCrossRef's, thus not leaving orphaned Songs, via the function in the Playlists Class.
Then the Playlist named "Your PlayList" has it's named updated to "PlayList that is for Your but it was Your PlayList".
