Use of SQL Editor

[Peke]

Hi,
Are you sure that Played # was not increased during Sync as that way it will not showed in played table when using SQL?

[Barry4679]

After a few tests I sometimes see inconsistencies, e.g.
the main panel for a selected song and album shows: Played# = 15 with Added date = 2012/09/21.
Whereas the SQL query for this song and album shows only Played# = 13 entries and as first played date 2013/08/29.
Obviously some entries have been lost in the last 10 years.

Yes, like I said it is a midden pit ... ie, interesting to archaeologists, so that they can estimate historic track plays, but it is a long way short of being an official census.

They collect the data. They don't use it, and they make no attempt to validate nor protect it.

I have relieved MM from the task of collecting my play stats, somewhat because of the above issue, but mostly because it does not know what I play on my Sonos units, and it does not know what I stream when away from the house.

I have an application that collects the real data, and overwrites the play data in the MM database.

I only care about play count and date last played, because I use these as criteria in my playlists.
But I have also store all the individual plays into MM, because I have it, and one day I may get or make something that makes pretty pictures from the history.

[IanRTaylorUK]

Good discussion. Perhaps we can put together a few additions to Ludek's Custom Nodes add-on?

Under Ludek's track node of the files CustomNodesDefintion.js (found in C:\Program Files(x86)\MMW5 Debug\Scripts\Custom Nodes in my setup) I have previously added some simple SQL queries:

Code: Select all

title: 'Tracks with Artist same as Title',
sql: 'Songs.artist = Songs.SongTitle'
}, {
title: 'Tracks with Album Artist same as Title',
sql: 'Songs.albumArtist = Songs.SongTitle'
}, {
title: 'Tracks with BPM of 1 or Less',
sql: 'Songs.BPM <= 1'
}, {
title: 'Tracks with Bit Rate of 128k or less',
sql: 'Songs.Bitrate <= 128000'
}, {
title: 'Tracks with No Lyrics',
sql: 'Songs.Lyrics = ""'

I think it would be useful to have a few nodes to query when the PlayCount is at odds with the LastPlayedDate. Also when there is disparity between the LastPlayedDate and the Play History. But I think these are more complex SQL queries than I am comfortable with!

[sonos]

Peke wrote
Hi,
Are you sure that Played # was not increased during Sync as that way it will not showed in played table when using SQL?

I have done a few tests more and do not understand the discrepancies. I have compared the data:
Song added vs. first entry of the query and
Played# count vs listed number of query.
Song added vs. first entry of query and
Played# count vs listed plays of query.

Song ADDED : first date of entry : PLAYED# : listed number of Plays of query
21.09.2011 : 30.10.2011 : 15 : 11
24.04.2011 : 05.08.2012 : 14 : 21
24.01.2020 : 24.01.2020 : 8 : 6
20.04.2021 : 20.04.2021 : 27 : 27
no rule or trend can be observed

SQL query applied:
SELECT Strftime('%Y/%m/%d %H:%M:%S', ( Julianday(playdate) + 2415018.5 ), 'localtime')
AS plays
FROM played AS p
JOIN songs AS s
ON s.id = p.idsong
WHERE album = 'Name of Album'
AND songtitle = Name of song';

[Ludek]

Hi,
just reading this thread and I can confirm that Songs.playCounter value does not correspond to the sum in the Played table.

The reasons are:
1) Played # is editable field in MM5 so users can set any integer value as they like
2) The play count is synced with devices like iPods where we don't know the exact history, but just how many times the track has been played in iPod from the last sync operation (to increase in MM5)

[sonos]

What is the deeper reason why users can set the Played# date?

Another oddity is:
That Added date/time after ripping a CD is for the first track 2021/03/02 12:28 and for track 2 2021/03/02 12:29. This seems reasonable.
However according to query (see above) for track 1 2021/03/02 20:37 and for track 2 2021/03/02 17:31.
The date is correct but the times are very different and the order is reversed.
When will the PLAYDATE field be updated?

[Barry4679]

What is the deeper reason why users can set the Played# date?

I doubt that there is any deep reason, other than it is a non-static non-critical attribute that you you can "correct" if you see the need.

Played Date in MM5 is just an informed guess, because MM cannot see plays that you make with any tool that is not MMW or MMA, and as Ludek has informed us, even MMA is unable to see collect dates from some wretched Apple devices for some reason.

MM doesn't really "collect" play history as such. I see the "data" in the Played table as being some workings that they didn't throw away, just in case they are of use to any Addon developers in the community, rather than any serious attempt to collect all your play history.

Evidence is:

• the data in the Played table is not visible in MM, nor is it easily exported from MM

• the database has no referential integrity measures to keep the visible Played stats in sync with the Played table

• and, and you say, they offer facility to alter the "answer" without affecting the components from which it was built

[sonos]

@ Barry4679 Thanks for your comments (I missed a comment from the MM5 makers)

I did some more tests and found that the Played table does not give useful results. Not only that data and times are not always correct, but also how often the song was played (Played#). There are big inconsistencies. So there are big deviations e.g. Played# = 56 but in the table there are only 29 entries. As a conclusion one must say this table is not usable. So there is still a lot of potential left at least for the "played history".

Now to say something positive:
nevertheless MMW is for me the best music database (according to my limited knowledge). I also like the new concept of MM5. Thank you for the valuable work. I use MMW almost daily.