Is there a wrapper script for database interaction?

[jpers36]

I'm just getting started with MM. I'm looking to write a SQL script that runs periodically against the Songs database to update one of the Custom fields with an aggregated Album Rating value. Is there a wrapper script that someone's written where I can just add the SQL query and publish?

[Peke]

Hi,
I guess a starting point for you should be this addon https://www.mediamonkey.com/addons/brow ... ql-editor/

[drakinite]

Unfortunately I don't think there is yet an addon/script which does that, though it may be a good idea to make one. (That, or adding an API hook built into MM to run certain code on a specified schedule.)

What sort of frequency would you want that SQL query to run at? Every launch? every day?

[jpers36]

Thanks for the link, Peke! And thanks for creating this addon, drakinite!

I just transitioned from iTunes/iSyncr and this is the biggest functional gap. I rely heavily on album rating to determine which unrated songs to prioritize for listening and rating.

iTunes performed the calculation nearly instantaneously. I'm not looking for that responsiveness. Good times for updates would include on launch and on nightly schedule, as you suggest, but also on sync with my phone. I was figuring on testing out various schedules and figuring out what works over time.

[Peke]

Hi,
Hmmmm, May I ask you for one step by step working example how you want it work for you.

This maybe do not need complete new addon, maybe that can be partially solved with Auto-playlists that can refresh based on need.

Sorry if I ask too much ask, but I am really curious how you solved that as I would personally like to improve how I choose what non played new unrated tracks I would like to hear in car that I want to rate.

[jpers36]

If Album Rating is already exposed as a filterable attribute in an auto-playlist I would love that but I didn't see it.

The SQL I'm using is below (spaces added to avoid looking like URLs):

Code: Select all

UPDATE SONGS
       SET CUSTOM1 = COALESCE(R.ALBUMRATING,'U'),
           CUSTOM2 = COALESCE(R.ARTISTRATING, 'U')
FROM 
(SELECT SONGS. ID, AR.ALBUMRATING, ArR.ARTISTRATING
FROM SONGS
LEFT JOIN
(
SELECT IDALBUM, ROUND(SUM(RATING) / (COUNT(*) * 20.0),0) AS ALBUMRATING, COUNT(*) AS SONGCOUNT
FROM SONGS
WHERE RATING <> -1
GROUP BY IDALBUM
) AR ON SONGS.IDALBUM = AR.IDALBUM
LEFT JOIN
(
SELECT ARTIST, ROUND(SUM(RATING) / (COUNT(*) * 20.0),0) AS ARTISTRATING, COUNT(*) AS SONGCOUNT
FROM SONGS
WHERE RATING <> -1
GROUP BY ARTIST
) ArR ON SONGS.ARTIST= ArR.ARTIST
) R
WHERE SONGS.GENRE <> 'PODCAST'
AND (SONGS.CUSTOM1 <> COALESCE(R.ALBUMRATING,'U') OR SONGS.CUSTOM2 <> COALESCE(R.ARTISTRATING, 'U'))
AND SONGS. ID = R. ID;

[Lowlander]

The problem is that Album Rating is a calculated average of all its tracks Ratings, not a tagged value.

[ILC]

I want to execute 360 lines of SQL that look like this:

UPDATE songs SET Custom1 = '009' WHERE albumartist = 'Coldplay' AND album = 'Viva la Vida';

I have this program that did not work:

Code: Select all

Module mainModule
    Sub Main()
        Dim sdb As Object
        sdb = CreateObject("SongsDB5.SDBApplication")
        Dim dbo As Object
        dbo = sdb.Database
        Dim v = dbo.ExecSQL("UPDATE songs SET Custom1 = '019' WHERE albumartist = 'London Symphony' AND album = 'Favorite Classics';")
    End Sub
End Module

The SQL editor does not work because it only accepts one statement at a time and fails after two or three statements have been entered.

Can you help me get the VB working correctly?

This would help produce a good wrapper template for this thread.

[Barry4679]

I want to execute 360 lines of SQL that look like this:

UPDATE songs SET Custom1 = '009' WHERE albumartist = 'Coldplay' AND album = 'Viva la Vida';

I have this program that did not work:

You can do this easily.
You just have to do it outside of MM5.

You should be able to do it easily inside MM5, but you can't:

• No technical Wiki as there was with previous versions of MM5
• No response to your question; it has sat here unanswered for a week

It is dead simple outside MM5.
Here is an example of what you want to achieve.
And here is the proof that it works.
Special tools that you require: none that you have to pay for.

I did exaggerate slightly about it being dead simple. There are a couple of challenges, but the workaround is simple.
The challenges arise from the fact that MM5 modified the standard SQL engine. with their own collation, and a tokeniser.
They don't ship the former, but it is available on their web site.
The later is not available.

You need to download a copy of the free open source tool named DB Browser for Sqlite.

Use this tool to open your MM5 database (close the MM5 app first).
Take the Tools>LoadExtension option to load the SQLite3MMExt.dll file that you downloaded from MM.
That step have fixed the missing collation.

The missing tokeniser will bite you when you try and update any MM5 tag that is covered by their Full Text Search facility.
The Customx tags are affected by this.

What you have to do is delete the SQL trigger, in the MM5 db, that is monitoring changes to custom tag that you want to update.
My query above updates the Custom8 tag. The name of the trigger that I deleted was 'update_songs_custom8'

What this trigger does it is monitor the custom8 tag for any change that you make, and puts your new value in their full text search files. Now that it is deleted you won't be able to find any newly assigned value, in that custom tag column, using MM5 Advanced Search facility facility.
You can still see your tags in MM5, you sort the display by that column and everything looks ok. You can use the MM5 Column Browser facility as filter for those tag values OK. You can use the custom tag when for criteria for playlists, or Custom Collections.

the only thing that is affected AFAIK is queries like xxx, where MM5 finds every track that contains xxx in any tag.

In my case I am OK with this. I am putting album play count in one custom tag, and date album last played in another.
I don't want those numbers in my free form search anyway. ... You illustration shows you putting an integer in a custom tag, so you are probably in the same situation.

Delete the trigger using the same open source tool mentioned above. Use the Database Structure tab. Scroll to find the trigger, and delete it using the context menu. ... Someone is going to warn against this action. I have been doing it for many years, and haven't been burnt yet.

I don't ever bother to recreate the deleted triggers.
But you could do so after you have made all your updates.
in MM5 use the "rebuild database" option Files>ManageDatabase. This option is non-destructive to your database. It will recreate the missing trigger.

The rebuild fulltext search option at the same place will push your integers into into the indices, but I don't imagine you would want that.

More information in this thread, including location of download from MM web site

Make sure that you download the 32 bit version of DB Browser for Sqlite, not the 64 bit version.
MM only make a 32 bit version available, which is really annoying.

[ILC]

When I open a copy of the MM5.DB and then load the extension SQLite3MMExt.dll from a folder and copy of it I created separately from MM5, I get the message:

Error loading extension: The specific procedure could not be found.

Ian.

[Barry4679]

Did you make sure to get the 32 bit version of DB Browser for Sqlite?

See the last paragraph in my previous post.
I originally installed the 64 bit version, and it would not load the MM dll file, which is stuck back using 32 bit technology.
I don't remember the error message, but that would be the 1st place to start looking for your problem.

The file that you downloaded from MM looks like this?

When I open a copy of the MM5.DB and then load the extension SQLite3MMExt.dll from a folder and copy of it I created separately from MM5, I get the message:

This just means that you did not put the downloaded dll inside any of the MM folders?
That's OK. That what I did also.