MediaMonkey forum

dtsig wrote: ↑Sun Oct 10, 2021 6:04 pm I know this is a BIG want ... but if the output of the SQL window could be Send To .. that would take care of most all the reporting requirements. No need for printer output I think.

You could partially workaround by

• setting some custom column to '0' for all tracks, eg. custom10

• and then setting it to 1 for tracks which meet your selection criteria using the SQL add-on

• close and restart MM5 ... needed because the SQL add-on and MM5 are running in isolation from each other

• open EntireLibrary node and type custom10:1

• Ctrl+A to select all

• Use Sendto>FileList

• getting something like this

But a couple of things:

• you are still screwed, because the export functions works with a fixed restricted tag list

• and the whole procedure is going to get old quickly, because the sql-add on does not allow you to save a recall queries statements

Interesting .. thanks for that

Dsig

Why not use ODBC Data Source Admin with SQLite3 ODBC Driver for MM5.DB and then use something like Microsoft Access to link to the data source by creating a linked table for Songs?

Then create a query to the linked table, perhaps something like:

SELECT Songs.Artist, Songs.AlbumArtist, Songs.Album, Songs.DiscNumber, Songs.TrackNumber, Songs.SongTitle, Songs.SongPath, Songs.Year, Songs.Genre, Songs.Rating, Songs.BPM, Songs.Lyrics, Songs.Producer, Songs.Publisher, Songs.Conductor, Songs.Author
FROM Songs
WHERE (((Songs.TrackType)=2 Or (Songs.TrackType)=0))
ORDER BY Songs.TrackType;

Then print....

Hi Ian,
yes you can query the MM database using an external tool, but it is better using the SQL addon, if you exclude the inability to save and recall queries, and the inability to print.

The advantages are:

1. MM has baked their own collation into the MM databases ... everything that you referred to the WHERE clause of your sample query was numeric. Try something like "WHERE ALBUM = 'Parklife'' ... you will get an error message due to the missing collation ... this affects all clauses except SELECT .. there are workarounds but it adds complexity
2. they also have a backed in a tokenizer ... try updating a field with text affinity ... it will fail due to the missing tokenizer
3. another small point is that you are guaranteed to be accessing the MM database with a SQL browser that has the small features as whatever MM level of SQLITE that they used when developing and shipping the MM5 version you are working with

Hi Barry, thanks for reply.

I guess it is as much about what you already use. I have given Access a little kick around today i.e. using the typical installed desktop software:

1). Basic Songs Information (qryArtistAlbumSong)

SELECT Left([AlbumArtist],255) AS AA, Left([Album],255) AS Albm, Val([DiscNumber]) AS DIsc, Val([TrackNumber]) AS Trck, Left([SongTitle],255) AS SngTtl, Val(Left([Year],4)) AS YR, Left([Genre],255) AS Gnr, Val([Rating]) AS Rate, Val([Bitrate]) AS BtRt, Val([BPM]) AS Beats, SongsTableImport.TrackType
FROM SongsTableImport
GROUP BY Left([AlbumArtist],255), Left([Album],255), Val([DiscNumber]), Val([TrackNumber]), Left([SongTitle],255), Val(Left([Year],4)), Left([Genre],255), Val([Rating]), Val([Bitrate]), Val([BPM]), SongsTableImport.TrackType
HAVING (((Val(Left([Year],4)))>1900) AND ((SongsTableImport.TrackType)=0))
ORDER BY Left([AlbumArtist],255), Left([Album],255), Val([DiscNumber]), Val([TrackNumber]);

2). Cross Tab to Highlight Albums with Multiple Genres and show number of tracks vs year (qryArtistAlbumSong_Crosstab)

TRANSFORM Count(qryArtistAlbumSong.SngTtl) AS CountOfSngTtl
SELECT qryArtistAlbumSong.AA, qryArtistAlbumSong.Albm, qryArtistAlbumSong.Gnr, Count(qryArtistAlbumSong.SngTtl) AS TotalSongs
FROM qryArtistAlbumSong
GROUP BY qryArtistAlbumSong.AA, qryArtistAlbumSong.Albm, qryArtistAlbumSong.Gnr
PIVOT qryArtistAlbumSong.YR;

3). Cut and paste into Excel - it is now quite quick to see - for example:
a). Genre anomalies vs the artist (or within an album?). See Angus & Julie Stone, Ben Harper, Billie Eilish
b). Albums where songs spread over several years - which might be OK or might be a problem!

https://octoberclub-my.sharepoint.com/: ... Q?e=bB1Edp

IanRTaylorUK wrote: ↑Sun Oct 17, 2021 2:40 pm Hi Barry, thanks for reply.

I guess it is as much about what you already use. I have given Access a little kick around today i.e. using the typical installed desktop software:

Yes, I guess so.

BTW you should be able to simplify your workflow soon.

Michal is cooking up a change to the Export to CSV function. Haven't seen it yet, but you will be able to specify which fields will be exported, and in which sort sequence. So following on from dtsig's tip re the SendTo option, you should be able to use MM5 filtering capabilities, and then export via SendTo, directly to Excel or Access.

IanRTaylorUK wrote: ↑Sun Oct 17, 2021 2:40 pm I have given Access a little kick around today i.e. using the typical installed desktop software:

I built an small MS Access tool that can be used on demand or due the task planner to automaticly

• export the full table "songs" as CSV, including all custom fields,

• read it into an Access table, and to create a few statistical calculations.
  I then link those local Access tables to an MS Excel sheet as database for a few diagrams and pivots.

The synchronization is only read from MM to Access, no writing back.

if anyone maybe interested, I am happy to send you a copy; just give a short PM.

So...I have installed the SQL Editor Add-On, and see it in the list of Add-Ons, but where the F is it to actually run? None of the menus seems to possess that handle, and it isn't listed as an MM5 start menu item...

i thought the same thing when i installed ... it should be an icon on the top toward the center of the screen. It says Sql (llittle tiny letters)

Ha! Thanks! Found it, though the tooltip says 'Undefined' ... and ... THAT's IT? Just a query sub-window and a results sub-window? No schema drag-drop? Nuttin GUI? Wow...that's pretty primitive...though I appreciate that it could be valuable once someone knows the database topology and naming conventions etc. So, at least it's a window into the db...but I think I'll search for another. Having come from a corporate database world, my expectations were set too high.

ITgreybeard wrote: ↑Wed Nov 03, 2021 8:29 pm Ha! Thanks! Found it, though the tooltip says 'Undefined' ... and ... THAT's IT? Just a query sub-window and a results sub-window? No schema drag-drop? Nuttin GUI? Wow...that's pretty primitive...though I appreciate that it could be valuable once someone knows the database topology and naming conventions etc. So, at least it's a window into the db...but I think I'll search for another. Having come from a corporate database world, my expectations were set too high.

Apologies; It is pretty basic, yeah. I suppose there isn't a huge demand for a fully-featured database editor; but for browsing, and some queries, you can use DB Browser for SQLite. The only inconvenience I know of is that you shouldn't modify the database in the DB Browser while MM is open.

I haven't yet updated the database documentation () but I believe most of the database schema is unchanged from MM4: https://www.mediamonkey.com/wiki/Databa ... ture_(MM4)

Hi Drakinite! Thanks for the response, and please accept my apologies if my comments were at all harsh ; I really do appreciate having such a db window within MM5. If I make any progress with external tools, I will be pleased to pass the news and tools on to you.

Regards,
ITG

Inspired by the criticism, and because I like to procrastinate from my schoolwork, I've made an update to the sql editor, giving it a cleaner appearance & adding keyword highlighting. It's available here: https://www.mediamonkey.com/addons/brow ... ql-editor/

Please note that it's pretty basic keyword highlighting and it has no awareness of SQL syntax. It highlights SQL keywords as blue, database fields as green, and database names as red. Hopefully it assists with the user experience.

Thanks drakinite,

nothing appears to be broken.

Looks pretty, and gives a slight heads up re misspelling of field names.

I know that you said that it had no SQL awareness, but it would be improved if your field name match was turned off when immediately following "as".

Hmm, what would be the benefit of that?