SQL editor addon usage?

[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]

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.

[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!

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]

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.

[Andre_H]

if you only want to VIEW your data, there's another option (and MS Access has already been mentioned by Ian):

• install the SQLite ODBC driver. e.g. from http://www.ch-werner.de/sqliteodbc

• 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.

[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.

[Barry4679]

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?

[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.