SQL editor addon usage?

Get help for different MediaMonkey 5 Addons.

Moderators: jiri, drakinite, Addon Administrators

zzaj2023
Posts: 3
Joined: Tue Jul 11, 2023 9:47 am

SQL editor addon usage?

Post by zzaj2023 »

I've installed the SQL editor addon, but I can't find any place to run the SQL query? Can someone post a screenshot sequence that shows how to get to the place where you add your query, please?
IanRTaylorUK
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: SQL editor addon usage?

Post by IanRTaylorUK »

After installing an Add-On, it is good practise to come out of MMW5 and restart the program.

The SQL Editor icon is found on the 2nd "line" of the main screen and to the right hand side. The exact location will depend on how many Add-Ons and Icons are installed. If you have a tool bar set - it might the be 3rd line.

Best to move the cursor over this "line" and read to Tool-Tip text that shows after about 1 second. The SQL Editor Icon is a rectangle with 3 dots to the right and SQL within the main box. The icon is only around 5mm high and 7mm wide (within my screen / skin layout.

Hope this helps.
Ian Taylor
zzaj2023
Posts: 3
Joined: Tue Jul 11, 2023 9:47 am

Re: SQL editor addon usage?

Post by zzaj2023 »

It did help! Two other questions, though... the query only selects 1001 rows... is there a place where I can change that to a higher amount? ALSO, what's an example of a query that will EXPORT the results to a flat file? Thanks for your time!
IanRTaylorUK wrote: Tue Jul 11, 2023 2:30 pm After installing an Add-On, it is good practise to come out of MMW5 and restart the program.

The SQL Editor icon is found on the 2nd "line" of the main screen and to the right hand side. The exact location will depend on how many Add-Ons and Icons are installed. If you have a tool bar set - it might the be 3rd line.

Best to move the cursor over this "line" and read to Tool-Tip text that shows after about 1 second. The SQL Editor Icon is a rectangle with 3 dots to the right and SQL within the main box. The icon is only around 5mm high and 7mm wide (within my screen / skin layout.

Hope this helps.
IanRTaylorUK
Posts: 536
Joined: Fri Dec 27, 2019 4:41 pm

Re: SQL editor addon usage?

Post by IanRTaylorUK »

Hi,

To export a flat file I would use the Menu option - File / Menu / Reports / File List.

This exports the current selection. So you may want to go the the "Entire Library" node and click on "All Tracks". Don't forget to select everything. For example, use Ctrl+A.

Next you want to choose the "Columns" (or just select all columns) and "drag N drop" to change the order.

In the File Explorer style dialogue that follows, I normally choose CSV as the File Type. This is because you can open with - for example - Microsoft Excel. Alternatively, you can import into Microsoft Access (External Data / New Data Source / From File / Text).

You may need to change the Code Page to e.g. UTF8 using the advanced options to get the import to present well. I use Comma as the field separator, double quotes as the text qualifier AND tick the box for first row contains field names.

Most fields carry across OK but keep an eye out for "Long Text" fields like Comment / Filename / Folder / Path and Lyrics

Now you can write quite useful but slightly complex SQL e.g.

TRANSFORM Count([MMW5 File Export].[Album volume]) AS [CountOfAlbum volume]
SELECT [MMW5 File Export].[Album Artist] AS Art, First([MMW5 File Export].Album) AS FirstOfAlbum
FROM [MMW5 File Export]
WHERE ((([MMW5 File Export].Type)="Music" Or ([MMW5 File Export].Type)="Classical"))
GROUP BY [MMW5 File Export].[Album Artist], [MMW5 File Export].Type
PIVOT [MMW5 File Export].Extension;

This gives a table of Artist / Album vs columns for the file types. The cells under these columns have a count of the number of tracks for the artist / album. I can therefore see which albums have the most MP3s so I can target these for upgrade to FLAC.

NOTE: The initial selection can be your MMW5 library OR the track listing from a device!

Of course you may prefer to use a Spreadsheet if this is where your experience etc lies.
Ian Taylor
Andre_H
Posts: 415
Joined: Thu Jan 21, 2021 2:04 pm
Location: Germany

Re: SQL editor addon usage?

Post by Andre_H »

if you only want to VIEW your data, there's another option (and MS Access has already been mentioned by Ian):
  • share the folder where your MM5.DB is located
  • start a new MS Access file, link the MM5.DB in
  • scroll the tables, create your views.
that way you get all the MS Access tools to create all sorts of lists and reports. there are a few "unusual" responses to filters in queries that must be based on SQLite, but so far I've had read access to all data. I haven't tried writing back yet.
- MMW 5.0.4.2690 (non-portable, shared DB & files) on Windows 2016 # only essential addons # my 24/7 media server
- MMW MMW 5.0.4.2690 (non-portable, shared DB & files) on Windows 10 # playing, testing skins & addons # my desktop app
- MMA Pro (2.0.0.1063) on Android 10, 11, 12 Phones & Tabs # WiFi Sync # playing

- MP3Tag, MP3Diags, MP3DirectCut, IrfanView
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL editor addon usage?

Post by drakinite »

As another option, I'm personally fond of https://sqlitebrowser.org/

But when opening MM5.DB in another app, be very careful. What I'd strongly recommend is to first close MM (to make sure there are no pending transactions), then make a copy of MM.DB, then open that copy with the sqlite viewer of your choice.

As for the problem of only selection 1001 rows: I'll try and remember to check and see if I can reproduce on my own system, when I'm back at my computer.
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.
Barry4679
Posts: 2386
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL editor addon usage?

Post by Barry4679 »

drakinite wrote: Thu Aug 24, 2023 8:52 pm As for the problem of only selection 1001 rows: I'll try and remember to check and see if I can reproduce on my own system, when I'm back at my computer.
The limitation is coded into your script;

Code: Select all

 if (rows > 1000) break;
The limitor was intended only while testing?
Or is it there as a protection for MM5, in case someone writes a query which generates a humongous result?
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
drakinite
Posts: 965
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL editor addon usage?

Post by drakinite »

ah, that'd definitely do it, good catch! I forgot about checking the addon, but I'll bet that was put in to prevent the rendering from taking too long, because rendering 1000 rows with all of the columns from Songs takes quite a long time; and if you have a database of 100,000 tracks, it would get insane. When processing a lot of data, I think it would be a better idea to use a dedicated SQLite viewer program in my opinion, since the SQL editor is relatively barebones and isn't optimized for big queries or lots of data.
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.
Post Reply