SQL Editor 1.0.1

Get help for different MediaMonkey 5 Addons.

Moderators: jiri, drakinite, Addon Administrators

Barry4679
Posts: 1828
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL Editor 1.0.1

Post by Barry4679 »

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
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
dtsig
Posts: 3475
Joined: Mon Jan 24, 2011 6:34 pm

Re: SQL Editor 1.0.1

Post by dtsig »

Interesting .. thanks for that

Dsig
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig
IanRTaylorUK
Posts: 289
Joined: Fri Dec 27, 2019 4:41 pm

Re: SQL Editor 1.0.1

Post by IanRTaylorUK »

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....
Ian Taylor
MM4W 4.1.31.1919 (Normal 12Feb2021) on Windows 10 Business (v21H2 Build 19044.1348)
Add-Ons: Export / Import Playlists for Child Nodes; Magic Nodes; RegExp Find and Replace; Tagging Inconsistencies
Music library of 34900 files (D:\) backed up to OneDrive
MMA 1.4.5.0960 on Sony Xperia XZ with Android 8 & ~15,500 Tracks in 975 Playlists
MMA 1.4.5.0960 on Samsung Tab S5e with Android 9 & ~15,500 Tracks in 975 Playlists
XZ synchronised with MM4W using USB; S5e synchronised with MM5W 2519 using USB or WiFi
Barry4679
Posts: 1828
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL Editor 1.0.1

Post by Barry4679 »

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
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
IanRTaylorUK
Posts: 289
Joined: Fri Dec 27, 2019 4:41 pm

Re: SQL Editor 1.0.1

Post by IanRTaylorUK »

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
Ian Taylor
MM4W 4.1.31.1919 (Normal 12Feb2021) on Windows 10 Business (v21H2 Build 19044.1348)
Add-Ons: Export / Import Playlists for Child Nodes; Magic Nodes; RegExp Find and Replace; Tagging Inconsistencies
Music library of 34900 files (D:\) backed up to OneDrive
MMA 1.4.5.0960 on Sony Xperia XZ with Android 8 & ~15,500 Tracks in 975 Playlists
MMA 1.4.5.0960 on Samsung Tab S5e with Android 9 & ~15,500 Tracks in 975 Playlists
XZ synchronised with MM4W using USB; S5e synchronised with MM5W 2519 using USB or WiFi
Barry4679
Posts: 1828
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL Editor 1.0.1

Post by Barry4679 »

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.
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
Andre_H
Posts: 231
Joined: Thu Jan 21, 2021 2:04 pm
Location: Germany

Re: SQL Editor 1.0.1

Post by Andre_H »

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.
- MMW 5.0.2.2510 (non-portable, shared DB & files) on Windows 2016 # only essential addons # my 24/7 media server
- MMW 5.0.2.2510 (non-portable, shared DB & files) on Windows 10 # playing, testing skins & addons # my desktop app
- various MMW 5.xx (portable, local DB & a few media files) on Windows 10 & 11 # ripping CDs, testing new builds in alpha state, trying some scripting things
- MMA Pro (latest build) on Android 10 Phone and Android 11 Tab # WiFi Sync # playing

- MP3Tag, MP3Diags, MP3DirectCut, IrfanView
ITgreybeard
Posts: 33
Joined: Sat Feb 04, 2012 6:52 pm
Location: WA USA

Re: SQL Editor 1.0.1

Post by ITgreybeard »

:-? 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...
ITgreybeard : MM Lifetime Gold on Win10Pro, always the latest production release unless noted otherwise. My carryaround is an AMD-based Lenovo Yoga 6 w 8c/16t, 16GB mem and .5 TB ssd. Server is a rescued and customized DELL T7400 (Qchan memory, PCI-e II, SATA II/III, on-board RAID): ++Dual QCore Xeon 3.0GHz, 112GB(!) ECC, Gigabyte Nvidia GTX 1650, SATA III and USB3.0 via cards, SSD and HDD aplenty. Win10 Pro +Ubuntu. Fallback= Custom ASUS i7-860 2.8GHz w/8GB mem, AMD W5000 wk graphics, multiple disks. Yes they're old :wink: but so am I ...
dtsig
Posts: 3475
Joined: Mon Jan 24, 2011 6:34 pm

Re: SQL Editor 1.0.1

Post by dtsig »

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)
Where's the db and ini stored
Reporting Bugs
Where tags are stored

Not affiliated with MediaMonkey ... just a RABID user/lover
DTSig
ITgreybeard
Posts: 33
Joined: Sat Feb 04, 2012 6:52 pm
Location: WA USA

Re: SQL Editor 1.0.1

Post by ITgreybeard »

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. :roll:
ITgreybeard : MM Lifetime Gold on Win10Pro, always the latest production release unless noted otherwise. My carryaround is an AMD-based Lenovo Yoga 6 w 8c/16t, 16GB mem and .5 TB ssd. Server is a rescued and customized DELL T7400 (Qchan memory, PCI-e II, SATA II/III, on-board RAID): ++Dual QCore Xeon 3.0GHz, 112GB(!) ECC, Gigabyte Nvidia GTX 1650, SATA III and USB3.0 via cards, SSD and HDD aplenty. Win10 Pro +Ubuntu. Fallback= Custom ASUS i7-860 2.8GHz w/8GB mem, AMD W5000 wk graphics, multiple disks. Yes they're old :wink: but so am I ...
drakinite
Posts: 647
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

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. :roll:
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)
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
ITgreybeard
Posts: 33
Joined: Sat Feb 04, 2012 6:52 pm
Location: WA USA

Re: SQL Editor 1.0.1

Post by ITgreybeard »

Hi Drakinite! Thanks for the response, and please accept my apologies if my comments were at all harsh :oops: ; 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. :D

Regards,
ITG
ITgreybeard : MM Lifetime Gold on Win10Pro, always the latest production release unless noted otherwise. My carryaround is an AMD-based Lenovo Yoga 6 w 8c/16t, 16GB mem and .5 TB ssd. Server is a rescued and customized DELL T7400 (Qchan memory, PCI-e II, SATA II/III, on-board RAID): ++Dual QCore Xeon 3.0GHz, 112GB(!) ECC, Gigabyte Nvidia GTX 1650, SATA III and USB3.0 via cards, SSD and HDD aplenty. Win10 Pro +Ubuntu. Fallback= Custom ASUS i7-860 2.8GHz w/8GB mem, AMD W5000 wk graphics, multiple disks. Yes they're old :wink: but so am I ...
drakinite
Posts: 647
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

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.
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
Barry4679
Posts: 1828
Joined: Fri Sep 11, 2009 8:07 am
Location: Australia
Contact:

Re: SQL Editor 1.0.1

Post by Barry4679 »

Thanks drakinite,

nothing appears to be broken. :D

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".
Want a dark skin for MM5? This is the one that works best for me .. elegant, compact & clear.
drakinite
Posts: 647
Joined: Tue May 12, 2020 10:06 am
Contact:

Re: SQL Editor 1.0.1

Post by drakinite »

Hmm, what would be the benefit of that?
Image
Student electrical-computer engineer, web programmer, part-time MediaMonkey developer, full-time MediaMonkey enthusiast
Post Reply