Is there a wrapper script for database interaction?

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

Moderators: jiri, drakinite, Addon Administrators

jpers36
Posts: 6
Joined: Mon Oct 10, 2022 6:22 pm

Is there a wrapper script for database interaction?

Post by 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
Posts: 17891
Joined: Tue Jun 10, 2003 7:21 pm
Location: Earth
Contact:

Re: Is there a wrapper script for database interaction?

Post by Peke »

Hi,
I guess a starting point for you should be this addon https://www.mediamonkey.com/addons/brow ... ql-editor/
Best regards,
Peke
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
Image
How to attach PICTURE/SCREENSHOTS to forum posts
drakinite
Posts: 977
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: Is there a wrapper script for database interaction?

Post by 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?
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
I uploaded many addons to MM's addon page, but not all of those were created by me. "By drakinite, Submitted by drakinite" means I made it on my own time. "By Ventis Media, Inc., Submitted by drakinite" means it may have been made by me or another MediaMonkey developer, so instead of crediting/thanking me, please thank the team. You can still ask me for support on any of our addons.
jpers36
Posts: 6
Joined: Mon Oct 10, 2022 6:22 pm

Re: Is there a wrapper script for database interaction?

Post by 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
Posts: 17891
Joined: Tue Jun 10, 2003 7:21 pm
Location: Earth
Contact:

Re: Is there a wrapper script for database interaction?

Post by 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.
Best regards,
Peke
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
Image
How to attach PICTURE/SCREENSHOTS to forum posts
jpers36
Posts: 6
Joined: Mon Oct 10, 2022 6:22 pm

Re: Is there a wrapper script for database interaction?

Post by 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
Posts: 57575
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Re: Is there a wrapper script for database interaction?

Post by Lowlander »

The problem is that Album Rating is a calculated average of all its tracks Ratings, not a tagged value.
ILC
Posts: 16
Joined: Wed Oct 03, 2012 1:37 am

Re: Is there a wrapper script for database interaction?

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

Re: Is there a wrapper script for database interaction?

Post by Barry4679 »

ILC wrote: Tue Mar 12, 2024 1:06 am 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.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
ILC
Posts: 16
Joined: Wed Oct 03, 2012 1:37 am

Re: Is there a wrapper script for database interaction?

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

Re: Is there a wrapper script for database interaction?

Post by 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?
ILC wrote: Tue Mar 19, 2024 5:40 pm 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.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
Post Reply