Need SQL Help

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

Moderators: jiri, drakinite, Addon Administrators

birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Need SQL Help

Post by birwin2 »

Hi Folks,

I'm looking for some help putting together a SQL statement to get the list of songs in the Playing List. The specific data I would like to retrieve are: IDSong, SongOder, SongTitle, Genre and BPM. Additionally I would like to get IDSong for the now playing song.

Also, is there any documentation as to the table relationships?

Although retired, I was a MySql database admin for 15+ years, so I do have some SQL knowledge. I will surely have more questions.

Bill Irwin
Ludek
Posts: 4945
Joined: Fri Mar 09, 2007 9:00 am

Re: Need SQL Help

Post by Ludek »

I am not sure that I understand the use-case correctly.

Are you looking for a way how to build a Playing list using SQL and put it into the Playing queue (or as a new list) or do you want rather get the current user's Playing list and get its metadata to put it somewhere into custom DB table?
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: Need SQL Help

Post by drakinite »

Ludek wrote: Fri Jun 18, 2021 3:29 pm I am not sure that I understand the use-case correctly.

Are you looking for a way how to build a Playing list using SQL and put it into the Playing queue (or as a new list) or do you want rather get the current user's Playing list and get its metadata to put it somewhere into custom DB table?
My guess, after reading their question, would be the latter.

In JavaScript, you can get the Playing list with the global Player object: https://www.mediamonkey.com/docs/api/cl ... layer.html
I'm not at my computer to check, but I think it would be its getSongList() method. But if you need to do direct SQL, Ludek would be much better suited to help with that ;)
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.
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

Ludek wrote: Fri Jun 18, 2021 3:29 pm I am not sure that I understand the use-case correctly.

Are you looking for a way how to build a Playing list using SQL and put it into the Playing queue (or as a new list) or do you want rather get the current user's Playing list and get its metadata to put it somewhere into custom DB table?
A little bit of background. I am a DJ and I use the Genre field to specify the "dance" associated with a song. For example, I have Santana's Smooth listed as a Cha Cha. I want to develop an application that i can open in a separate window to display a portion of the the Now Playing list. What gets displayed is the currently playing song/track (Order #, Genre, and SongTitle), at the top, plus the same for the next 3 or 4 songs/tracks. The purpose of the app is to display it on a separate screen that dancers could see.

I know how to query the Songs table, but what I need is to be able to retrieve the Now Playing list, and also to identify the currently playing track. This is what I need help with. I really appreciate you replying to my post.


Bill Irwin
Richmond, VA
Ludek
Posts: 4945
Joined: Fri Mar 09, 2007 9:00 am

Re: Need SQL Help

Post by Ludek »

Hi, and why you need it as separate application ?

It's much easier to open non-modal dialog directly in MM5 and display the info there using the existing API (i.e. pure JavaScript without a need for SQL) ? e.g. create a custom button on the toolbar that would open the non-modal window with the track's info. I can code a sample addon if you want.
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

Hi Ludek,

I don't need it to be a separate application. What you are describing would be perfect. Click a button in MM and a window launches that has the required data, and follows the player playlist (Playing), always showing the currently playing track at the top, and then the next 4 tracks on subsequent rows.

My programming skills were primarily in perl scripting, and some php. I only ever dabled in javascript, so it would take me quite a while to get up to speed. Thanks for your offer to code up an example as an Addon. I look forward to seeing how this can be implemented.

Bill Irwin
Richmond, VA
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

One more thing, Ludek. After downloading and reviewing MM5, I have found some changes and minor issues that will cause me to delay using that version in my DJ'ing. Can the example Addon be coded to work in both v4 and v5? If not, v4 would be my preference.

Thanks again for your help.

Bill Irwin
Richmond, VA
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: Need SQL Help

Post by drakinite »

The framework for addons has been completely overhauled from v4 to v5, so it wouldn't be possible to make one addon that's cross-compatible. What are the issues that are preventing you from using v5? Have you tried the beta versions of 5.0.1, to see if they've been fixed already?
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.
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

I understand that the overhaul from v4 to v5 prevents coding an addon to work in both. No problem, and coding the addon for v5 will be fine.

Right now my major issue in v5 has to do with Genres in the Media Tree (left pane). In v4 there was a little triangle, and when clicked I would get an alphabetical list of of Genres. When I clicked on a Genre, the center pane displayed a List View of all of my music for that genre. In v5 the triangle is gone. When I click on Genres, I get the Genre Grid View, which has Album Art associated with each Genre. This requires more scrolling to find the genre I want. Then when I click on a Genre, I get a Grid View with album art for each song. What I need is a list view, as in v4, that can be sorted by a given column. I need to be able to sort by Date Added, primarily, but also by Title or BPM. This change is going to require more time and effort for me to put together playlists.

Another issue I came across has to do with linking my Spotify library. When I tried to do this MM crashed before completing the Spotify library retrieval. Because I can only play 30 seconds of any Spotify song, this is a minor issue.

Bill Irwin
Richmond, VA
Peke
Posts: 17446
Joined: Tue Jun 10, 2003 7:21 pm
Location: Earth
Contact:

Re: Need SQL Help

Post by Peke »

Hi,
Use ⋮ from top right corner of Tree and enable option SHOW ALL SUBNODES which will give you same behavior as in MM4.
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
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

Thank you, thank you, thank you, Peke. This is huge for me.

Bill Irwin
Richmond, VA
Peke
Posts: 17446
Joined: Tue Jun 10, 2003 7:21 pm
Location: Earth
Contact:

Re: Need SQL Help

Post by Peke »

Hi,
birwin2 wrote: Tue Jun 29, 2021 6:53 am Thank you, thank you, thank you, Peke. This is huge for me.
Great, it was my pleasure.
birwin2 wrote: Mon Jun 28, 2021 5:37 pm Another issue I came across has to do with linking my Spotify library. When I tried to do this MM crashed before completing the Spotify library retrieval. Because I can only play 30 seconds of any Spotify song, this is a minor issue.
No that we solved that annoyance do you get and crashlog dialog or something? If not please make sure you use latest Debug version from viewtopic.php?f=30&t=86639&sd=d
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
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

Okay, so here's the latest. Installed portable version of MediaMonkey 5.0.1.2419 RC-4 (debug). Was eventually able to get the Exception Error while trying to Sync from Spotify and submitted it.

I was able to get a (somewhat) successful Sync. All Song records were brought over, but after I closed that window it started a Syncing Spotify process that was being displayed as a msg in the lower left corner. At the point where it was trying to copy/update playlists, an error occurred that brought up a window that indicated the error was with creating "Playlist". I saved the log, closed the window and everything else seemed fine.

In looking at the Spotify "Playlists" that came over, several had the same name. In Spotify I have Playlists under Folders. For Instance, I have a folder called "WCS", that contains several playlists and a few more folders. In Creating the name of a playlist, if you could include the Parent folders as part of the name, it would make it easier to tell specifically which playlist it is when playlists have the same base name. For example:

Playlist "100 BPM" in "WCS" folder becomes "WCS/100 BPM" (you could replace the slash with something else if you prefer).

Playlist "100 BPM" in "LATIN" subfolder of "WCS" becomes "WCS/LATIN/100 BPM"

Might be problematic if someone had long Folder/Song names and/or folder levels. But I thought I'd mention.

And I'm just wondering, has anyone else indicated that being able to tag a song with multiple Genre's is desirable?

Some good news now.
Since I've had some more time to work with MM5 now, I would like to say to everyone at MediaMonkey what a great job you have done. For me, getting the Genre list issue resolved was huge (thanks Peke). I tried some of the other Skins and the Metro M (Classic) is perfect for me. My sight isn't what is was years ago, but the colors, scrollbars, re-positioning of controls and elements in the bottom section all make MM easier to use. With regards to Spotify, even with just the 30 sec clips, this enhancement provides a tool that will be so helpful to me for cleaning up my Spotify playlists.


Again, Thanks to all.

Ludek - If you are still willing to code up that sample addon in MM5, that would just cement my transition to MM5. I do believe that there are many dance studios, and even more Ballroom Dance DJ's who would switch from whatever they are using to MM5 just to get this feature. I will promote MM5 to others. Thanks.
birwin2
Posts: 10
Joined: Sat May 09, 2015 4:47 pm

Re: Need SQL Help

Post by birwin2 »

Ok. Feeling a little stupid this morning. Just discovered that MM already supports tagging a song with multiple genres.
Post Reply