Use of SQL Editor

To discuss development of addons / skins / customization of MediaMonkey.

Moderators: jiri, drakinite, Addon Administrators

sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Use of SQL Editor

Post by 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
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: Use of SQL Editor

Post by 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.
Ian Taylor
IanRTaylorUK
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: Use of SQL Editor

Post by 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!).
Ian Taylor
sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by 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
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: Use of SQL Editor

Post by 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?
Ian Taylor
sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by 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
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: Use of SQL Editor

Post by IanRTaylorUK »

IanRTaylorUK wrote: Wed Dec 29, 2021 1:06 pm 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.
Ian Taylor
sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by 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
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by sonos »

And not to forget UTCOFFSET
IanRTaylorUK
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: Use of SQL Editor

Post by 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
Ian Taylor
Barry4679
Posts: 2408
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: Use of SQL Editor

Post by 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?
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by 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
Posts: 2408
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: Use of SQL Editor

Post by Barry4679 »

sonos wrote: Sun Jan 02, 2022 1:38 pm 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.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by sonos »

Hi Barry4679
This is exactly what I was looking for!

Thanks a lot :D
sonos
Posts: 171
Joined: Wed Aug 07, 2013 11:54 am

Re: Use of SQL Editor

Post by 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. :o
Post Reply