Use of SQL Editor

[sonos]

I am trying to use SQL Editor, but I do not know how to specify individual field names of MM5. So a small example query would be very helpful for me:

SELECT all albums FROM type Music WHERE Artist = Name AND Date < '2014-02-15' AND >= '2004-01-01'.

Thanks in advance

[IanRTaylorUK]

Suggest you take a look at Ludek's Custom Nodes add-on here:

viewtopic.php?f=30&t=99158&p=490410&hil ... es#p490410

This has some "sample" SQL queries. A bit more info here:

viewtopic.php?f=32&t=99442&hilit=custom+nodes

Also, a lot can be done with Collections and if you run the debug version of MMW5 you will also get some information on the SQL.

Hope this helps.

[IanRTaylorUK]

Yet another approach is to use the File / Reports / File List, then select your columns.

You can then pull this into Microsoft Access or Excel and query / report etc by either selecting the entire library or you selection.

For example, I wanted to see what the overlap was between albums of a particular artist as I suspected some albums were simply a "re-issue" of a previous album. In this case it was Creedance Clearwater Revival! Created a table with Title / Album / Length / Year / Genre / Rating / Bitrate / Path and Media called tblCCR.

Then ran this query:

TRANSFORM First(tblCCR.Bitrate) AS FirstOfBitrate
SELECT tblCCR.Title
FROM tblCCR
GROUP BY tblCCR.Title
ORDER BY tblCCR.Title
PIVOT tblCCR.Album;

to cross tabulated tracks vs albums with the bitrate in the "cells". I was then able to "prune" a few albums - typically the lower bit-rate tracks. It also highlighted some track title inconsistencies e.g. Cotton Fields vs Cottonfields.

e.g. https://octoberclub-my.sharepoint.com/: ... g?e=YvOrig (should be available in a few hours as a bit of an upload que!).

[sonos]

Is it possible to query via SQL editor when a single song (exist a datetime stamp?) was played?
Example output
song name played at:
03.05. 2016 13:00
03.05. 2016 09:15
08.12. 2019 17:00
13.10. 2021 20:15

[IanRTaylorUK]

Hi Sonos,

I do not think MMW keeps a "Play History" it has just the single "Last Played" date/time.

In your example you would see 13/10/2021 20:15 as the 3 previous values for Last Played would have been overwritten.

Now the "content" of the field is a number representing the date and time e.g. Songs.LastTimePlayed>=44197 and Songs.LastTimePlayed<='44559.7449054861 is saying "Last Played" between 01Jan2021 and ~Now. This number is formatted into something more useful e.g. Today hh:mm or dd/mm/yy hh:mm.

In MMW4 I would have a Collection with the Criteria "Type Equals Music" and make sure the "Last Played" field is ticked in the columns. Then use the search box e.g. artist:10cc or artist:10cc title:wall to display the tracks containing wall in the title and show the last played date (or null). So in my case this show I have played The Wall Street Shuffle from "The Very Best of 10cc" album today, but have never played the version from the "Sheet Music" album.

Is this the sort of information you are looking to see?

[sonos]

Thank you for the reply!
Yes that was the information I was looking for. Personally, I would have been interested to know if there are any temporal clusters with regard to certain music. But it is no big deal.

[IanRTaylorUK]

I do not think MMW keeps a "Play History" it has just the single "Last Played" date/time.

I might be a little incorrect here. MMW does not "display" a "Play History" but the "library" table called "Played" does have multiple entries for a given IDSong, for example:
IDP IDS Playdate
522 121218 44427.422599769
653 121218 44439.356683275
716 121218 44439.799865556
where IDS detail can be found in Songs table:
121218 Ryan Adams 4937 Big Colors Ryan Adams 1 09 Showtime :\OneDrive - THE OCTOBER CLUB\Music\Ryan Adams\Big Colors\Showtime.mp3

I am wondering if the MMW4 addon can help! Might take a look in the New Year.

[sonos]

IanRTaylorUK wrote:
I might be a little incorrect here. MMW does not "display" a "Play History" but the "library" table called "Played" does have multiple entries for a given IDSong, for example:
IDP IDS Playdate
522 121218 44427.422599769
653 121218 44439.356683275
716 121218 44439.799865556

With the query: SELECT * FROM Played WHERE IDSong = 219893
I got a table with 10 entries in the column Playdate. This matches with the value Played#. I wonder how to get the Playdate e.g. 44427.422599769 in a readable form?
SELECT * FROM Played WHERE IDSong = 219893 ORDER BY strftime('%d.%m.%Y', playdate) does not work.

Any idea? Is this a SQLite DB?

[sonos]

And not to forget UTCOFFSET

[IanRTaylorUK]

Hi Sonos,

I think you are on the right track:

viewtopic.php?p=131973#131973

This may also help a little - but does not mention / define UTCOFFSET:

https://www.mediamonkey.com/wiki/Databa ... _%28MM4%29

[Barry4679]

Hi Sonos .. Hi Ian

yes MM database is a a SQLITE database.
and the PLAYED table contains details of every play, but there needs to be some caution taken before relying on it.
MM does not appear to use this data except for in the Statistics report AFAIKS.
It if a bit like a midden pit where they dump their waste and by-products.

The database appears to have no referential integrity measures that ensure the sum of Played plays bears any relation to the counts in the Songs table. The User Community tools that import external plays into MM, or allow play history corrections, typically ignore the Played table.

MM date formats in the database are a bit of a jumble for historic reasons. The date played fields are in Julian Date format.
SQLITE has function that will deal with Julain Dates: https://www.sqlite.org/lang_datefunc.html

eg.
select strftime('%Y/%m/%d %H:%M:%S',(julianday(LastTimePlayed) + 2415018.5),'localtime')
from songs
where album collate nocase = 'Citizen of Glass'

It is easier to read them, than write them.
I add my Sonos plays, and my away-from-home plays from my Last.fm account, into MM, just for the sake of completeness.
So I now have 340,000 records of bloat sitting in my Played table, accumulated over 12+ years ... but maybe you are creating some purpose for them?

[sonos]

Hi Barry4679

Thanks for your reply!

I was able to create a table from the DB with all PLAYDATE values applying e.g.
SELECT PLAYDATE FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')
or
SELECT PLAYDATE + 2415018.5 FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')

However I'm struggling to 'feed in' this table into

SELECT strftime('%Y/%m/%d %H:%M:%S',(julianday(PLAYDATE Table) + 2415018.5),'localtime')

to finally create a table with the individual playdate data (in readable form)

Any hint is gratefully welcome

[Barry4679]

I was able to create a table from the DB with all PLAYDATE values applying e.g.
SELECT PLAYDATE FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')
or
SELECT PLAYDATE + 2415018.5 FROM Played WHERE IDSong = (SELECT ID FROM Songs WHERE Songtitle = 'The Wedding' AND Album ='Kristallen')

However I'm struggling to 'feed in' this table into

SELECT strftime('%Y/%m/%d %H:%M:%S',(julianday(PLAYDATE Table) + 2415018.5),'localtime')

to finally create a table with the individual playdate data (in readable form)

I don't understand exactly what you are trying to achieve, ie what do you mean by " create a table from the DB", and also "feed in this table"?

The last sql fragment I posted only showed how to get the last play date for a album's track.
You are after all play dates I think.

To achieve that you need to join the Songs table (to get the track or album that you are interested in), and the Played table (to get all of the play dates).

eg:

Code: Select all

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 COLLATE nocase = 'Citizen of Glass'
       AND songtitle COLLATE nocase = 'Trojan Horses';

or if you are only running this inside the SQL Editor running in the MM5 addon, it is a bit easier:

Code: Select all

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 = 'Citizen of Glass'
       AND songtitle = 'Trojan Horses';

There is a limit to how much automation you can achieve just with SQL.
To achieve automation and operational flexibility you need to call the SQL from inside some programming languauge.

[sonos]

Hi Barry4679
This is exactly what I was looking for!

Thanks a lot

[sonos]

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.