[DaveBlake]

I'm guessing that the music library data model has moved on since some of these functions (and their related nodes) were designed. I think we need to review the queries we use to get the recently played or added things.

I started looking at them because now the song.strArtists field is not always just a concantonated string denormalisation of the artist names in the song_artist table, we need to query the song_artist table as well if trying to return accurate artist data not just a description (same for albums and album_artist). But there are other issues.

a) Recently added songs or albums should be determined using the song.dateAdded field. Currently it uses song or album ID, presuming that the most recent have the highest ID. This isn't always the case, and as a result the list of recently added songs displayed is not in date added order (and not always the most recent).

It makes sense to say that the date an album is added (album does not have a date added field) is the last date that a song from that album was added.

b) Recently played.... when do we consider something to be played? Personally I would like to flag a song (last played date and playcount) when I have started to play it regardless of if I got to the end. Currently we do the opposite, unless you get to the end it does not count.

For an album the play count and last played date are determined from the songs. But again it is pessimistic in that all the songs must be played before an album is considered to be played - albumview.iTimesPlayed = min(song.iTimesPlayed). Hence the silly situation that if there is one song that you never listen to completely, maybe you skip the end, that album is never listed as played!

Personally I would like to flag an album as played if I started playing just one song. Am I odd?

Perhaps there is a requirement for both started and completed approaches? What music have a completely listened to lots, or what have I never even sampled?

Changing for a) is necessary, correcting GetRecentlyAddedAlbumSongs and GetRecentlyAddedAlbums.

Just wondering what people's thoughts are about b) changing when song.lastplayed and iTimesPlayed is set, how albumview.iTimesPlayed is calculated (max(song.iTimesPlayed) and the results of GetTop100Albums, GetTop100AlbumSongs, GetRecentlyPlayedAlbums, GetRecentlyPlayedAlbumSongs.

[Martijn]

a) Recently added songs or albums should be determined using the song.dateAdded field. Currently it uses song or album ID, presuming that the most recent have the highest ID. This isn't always the case, and as a result the list of recently added songs displayed is not in date added order (and not always the most recent).

Recently things changed for music and we actually are using filedate for added date. https://github.com/xbmc/xbmc/pull/7336
Not sure in what way this currently affects albums but it should follow the dateadded and not the ID. Since you bring this up i'm suspecting it doesn't really do this yet and getting this changed is certainly welcomed

b) Recently played.... when do we consider something to be played? Personally I would like to flag a song (last played date and playcount) when I have started to play it regardless of if I got to the end. Currently we do the opposite, unless you get to the end it does not count.

Playing a song for a second or so doesn't warrant it to be marked as played. Not sure what threshold is now used for music but for videos we use 95% before marking it watched. Something similar should happen for music. Certainly it you should have listened to a large part of the song before it should be considered played (maybe a bit less than 95% is acceptable).

Personally I would like to flag an album as played if I started playing just one song. Am I odd?

Probably
You cannot consider an entire album played if you only played a single song from it. You will have to finish playing ALL or at least a very large part of the songs before you can even consider an album played.

[jjd-uk]

For video the percentage needed to be played before being marked "watched" can be adjusted with the "playcountminimumpercent" Advanced Setting, see http://kodi.wiki/view/HOW-TO:Modify_auto...ume_points

Perhaps similar could be done for music.

[DaveBlake]

For video the percentage needed to be played before being marked "watched" can be adjusted with the "playcountminimumpercent" Advanced Setting, see http://kodi.wiki/view/HOW-TO:Modify_auto...ume_points

Perhaps similar could be done for music.

Yes, that would be one way that music could be usefully made more consistent with video.

[DaveBlake]

Recently things changed for music and we actually are using filedate for added date. https://github.com/xbmc/xbmc/pull/7336
Not sure in what way this currently affects albums but it should follow the dateadded and not the ID. Since you bring this up i'm suspecting it doesn't really do this yet and getting this changed is certainly welcomed

Ah. Since I have been editing tags and rescanning to library I hadn't noticed that the "added date" was just the file date. Perhaps we should rename the field "filedate" if that is what it is? It means that if I finally add some old music files, created and tagged months ago, to my music library they won't show a recent date. In that case the only way, and I don't like doing it that way, to identify recently added songs (or albums) is using ID.

As a library user I am more interested in when I added the song to my library than the date of the file itself. Rethinking I don't think change a) is a good idea after all. Or in addition to file date, we also need to store the date a song was added to library and use that for recently added.

Perhaps I should add a reminder that music files are added to the library without being scraped, so that last scraped date does not do the added date job.

[Razze]

I think this may be a problem, as the rows get deleted on rescan.
So you will just end up with all items on the date you did your last scan.

Video works this way too and that's actually one of the biggest problems I see at the moment in database land.


Please look into it, I haven't really looked which tables get hit by it.

[DaveBlake]

I think this may be a problem, as the rows get deleted on rescan.
So you will just end up with all items on the date you did your last scan.

Video works this way too and that's actually one of the biggest problems I see at the moment in database land.


Please look into it, I haven't really looked which tables get hit by it.

I see your point, re-scanned date would be rather meaningless. First scanned date would be useful. Meanwhile file date for a song tells you something, it just shouldn't be called "dateAdded"!

I will give it some more thought. Meanwhile for recently added all we can do is use albumID.

GetRecentlyPlayedAlbumSongs
Perhaps someone can tell me what we are trying to do with GetRecentlyPlayedAlbumSongs? The query it uses is

Code:

SELECT songview.*, albumview.* FROM songview JOIN albumview ON (songview.idAlbum = albumview.idAlbum)
JOIN (SELECT DISTINCT album.idAlbum FROM album JOIN song ON album.idAlbum = song.idAlbum
WHERE song.lastplayed IS NOT NULL ORDER BY song.lastplayed DESC LIMIT 25) AS _albumlimit
ON (albumview.idAlbum = _albumlimit.idAlbum)

We only display songview data so albumview.* is unneeded. Are we trying to get all the songs from the last say 25 albums where at least one song was played most recently? Can anyone clarify?

[Razze]

Yes, dateAdded is not correct. I think I just aligned it to the video implementation. There is also a switch to not use the creation date but the modified date. See the advanced settings.

[DaveBlake]

Seems we should look to rename "dateAdded" "filedate", just for future clarity. But also look at the possibility of a "first scanned" date. Updating the library does replace the song ID, but albums don't get deleted so album ID is a reasonably good indication of the order things were added in, just not when. Perhaps idAlbum is enough?

Meanwhile we have the recently played queries:

GetRecentlyPlayedAlbums uses

Code:

SELECT DISTINCT albumview.* FROM song JOIN albumview ON albumview.idAlbum=song.idAlbum
WHERE song.lastplayed IS NOT NULL
ORDER BY song.lastplayed DESC LIMIT %u ( RECENTLY_PLAYED_LIMIT)

This ignores the fact that we have an albumview.lastplayed field (set to the max song.lastplayed with that idAlbum). So it could be

Code:

SELECT albumview.* FROM albumview
WHERE albumview.lastplayed IS NOT NULL
ORDER BY albumview.lastplayed DESC LIMIT %u ( RECENTLY_PLAYED_LIMIT)

Or to return artist(s) ID, name and MBID for JSON, not just strArtists as currently,

Code:

SELECT albumview.*, albumartistview.* FROM
(SELECT idAlbum FROM albumview
WHERE albumview.lastplayed IS NOT NULL
ORDER BY albumview.lastplayed DESC LIMIT %u) as playedalbums
JOIN albumview ON albumview.idAlbum = playedalbums.idAlbum
LEFT JOIN albumartistview ON albumview.idAlbum = albumartistview.idAlbum
ORDER BY albumview.lastplayed DESC

Been as it is (top one) for years, but anyone mind if I update it?

GetRecentlyPlayedAlbumSongs
Are we trying to get all the songs from the last RECENTLY_PLAYED_LIMIT albums where at least one song was played most recently?

[Razze]

Lastplayed on albumview is pretty new.
https://github.com/xbmc/xbmc/commit/1161...923ae75a7d
So we had no other way before that.

[DaveBlake]

Lastplayed on albumview is pretty new.
https://github.com/xbmc/xbmc/commit/1161...923ae75a7d
So we had no other way before that.

Sorting by it is new, and very useful too. Thanks for that

The function GetRecentlyPlayedAlbum and query were in Eden. Guess it didn't get updated, but I will do so as part of the JSON fix. Have the data, might as well use it.

[Razze]

I meant the column is pretty new