A bit niche this, apologies…
I’ve spent a bit of time getting Media Monkey set up, tags ok, artwork etc. I want to get a report by artist with all their albums which I have in MM. Ideally in release date order. It looks like this is possible in the Reports section but what you get off you run the report is Artist then the album title duplicated by the number of tracks on the album. As if it’s listing the tracks but using the album name each time.
Any ideas? I could load it into Excel and do a pivot table but it’s a lot of data and surely it’s a basic requirement so I suspect operator error.
Any ideas? I’m on the latest version, Gold.
Check out Beekeeper Studio – a free to try SQL front-end that will read the underlying MediaMonkey 5 database (its a SQLite file called MM5.db tucked away inside your c: drive). With a little bit of SQL code you should be able to write a query that will extract what you want. The internal schema of Media Monkey stores the data across a number of tables, so you’ll need to be able to do a multi-table join, then select SQLite as the database engine to use, run the query and export the data to Excel or as a .csv file.
What’s your day rate?
If you have to ask….
I used to say “It’s worth it”.
Here is the core of the MM5 database showing the tables that cover the key Artist-Album-Song information. It’s a pretty simplistic structure – all the fancy dancing is being done in the application code layer rather than by logical constraints within the database layer.
(I’ve reverse engineered the schema diagram using DBeaver, an open source DBMS app).
It would be quite simple to query just the info you need from these few tables (there’s loads more in the MM5.db file beyond these) without duplicating stuff all over the place as you describe.
I’d offer to have a play and see what I could come up with, but sadly I don’t have any Artists or Albums or Songs in the tables within my own MM5 database, as I only use it for Podcasts, which are held in different tables with different relationships etc.
Does Twang Jnr. have any SQL coding experience?
Twang Jr is a proud luddite. He loathes technology!
Ta for the research. I think I’ll try the MM user forum – I can’t be the first to want to do this. I’m just mystified why the on board utility is so useless.
This looks handy.
https://www.riklewis.com/mediamonkey/scripts.php?id=2
@Twang – did you try any of these out?
When I tried one it just said the scripts only worked with MediaMonkey version 4 – so some way out of date.
🙁
Not yet. I don’t know where to start TBH. Running SQL scripts is outside my limited skill set so I need to read up.
AI would probably help if you want to go down that path
I am an AI virgin and plan to stay that way but it does make me curious.
@Twang – any idea how big your MM5.db file is?
I only use MM for Podcasts but there are quite a lot them, some with oodles of episodes (e.g. Desert Island Discs, From Our Own Correspondent, In Our Time etc). My own MM5.db file weighs in at about 169Mb.
If your MM5.db is small enough to send to me via WeTransfer, I’ll happily have a play and see if I can come up with some Report output to meet your modest reporting requirements.
I’ll have a look. That would be great!
I just remembered that for the new version of MM they migrated their scripts from Visual Basic to Javascript. God knows why, it’s the worst development language I’ve ever seen. And I’ve used COBOL.
So the scripts on that site won’t work if you have the latest version of MM.
It’s a real step back in usability in my view.
I’d guess that the VB used for the MM scripts is the old style Visual Basic that Microsoft dumped years back. Before renaming their newer .Net code base with the same name…
…The ‘last updated’ dates against each Report script tell us that most of them haven’t been tweaked for up to 15 years, so sadly they are basically dead in the water.
Now you guys are really taking the piss….
You used to be able to open a DB viewer from within MM itself (Ctrl+F9 IIRC), but it’s not there in the new version. So a separate sqlite DB front-end is the way to go it seems as VV mentions above. Once you’re in there I suggest something like
SELECT
AlbumArtist AS Artist,
Album,
COUNT(*) AS Tracks
FROM Songs
WHERE Album IS NOT NULL
GROUP BY AlbumArtist, Album
ORDER BY AlbumArtist COLLATE NOCASE,
Album COLLATE NOCASE;
(the COLLATE bit is some sqlite-specific additions to standard SQL)
I am in awe. Thank you.
Not at all, it was good to dredge up this after 3 years retired. Looking at the diagram above, I notice that all the fields you need aren’t in Songs, they are in the Album table. I was going by some old scripts I had.
So it’s even easier:
SELECT
Artist,
Album,
Tracks
FROM Albums
GROUP BY Artist, Album,
ORDER BY Artist COLLATE NOCASE, Album COLLATE NOCASE;
I think that’s right.
@Twang: critical question; do you want to see the names of the tracks (on each album for each artist) in your report output,
or
just the names of the albums for each artist and how many tracks there are on each?
I just want artist then titles eg
Jethro Tull Aqualung
War Child
Passion Play
etc
So everything you want in your report is likely held in the ‘Albums’ table.
However, the table is organised on a simple numeric identifier – i.e. not stored in any meaningful Artist or Album order, so hence the need to do a little extra work to get the data returned in the required sequence.
A small fly in the ointment is that to get the albums in order of release date, the best you can do is for the output to be sorted in order of ‘Year’ as the table doesn’t hold any finer granularity of release* than the year. So where more than one Album was released by an Artist in the same year, there’s no way for the query to organise the output to reflect the actual release dates.
By the way, the internal structure of the database (at least that bit I illustrated above) is organised for the benefit of performance by the front end application rather than in a strictly logical form (there’s a lot of data redundancy built in).
*Assumes that ‘Year’ in that table means release year, and not the year you added it to your database – this is probably a fair assumption, but the database author’s appallingly poor, lazy data naming standards do not allow us to be sure of this.
“I’ve reverse engineered the schema diagram using DBeaver, an open source DBMS app” – this thread is just one giant pisstake, isn’t it ??
I am moist.
*Rubs thighs*
It’s very real, it works really well, and it’s free:
https://dbeaver.io/
I’m with Lodes on this.
Absolutely waaaay beyond my comprehension
This is just like some of Fents’ technobabble. I feel impelled to read it knowing comprehension will be zip.
I thought we were supposed to listen to music?
In order to do that it’s sometimes useful to have a note of whether you have a digital copy of any album not on an actual physical shelf. In these shelf-free days of streaming, a simple report can be a useful thing to have to hand, in order to check if something is lurking on a hard drive somewhere.
And useful to prevent 3 purchases of the same album…… not that I would ever do such a thing…..
I very nearly bought a 33p CD of William Orbit’s Strange Cargo on Friday morning, until I remembered that I already have two copies.
Ever bought a digital copy of an album, “Amazeballs” perhaps, from, let’s say, Bandcamp or wherever, and then copied the downloaded zip file over to your ‘Music’ folder, only to be confronted by the pop-up that asks, “The file ‘Amazeballs.zip’ already exists, overwrite it?”.
No? It’s just me then.
Guilty as charged
I’m even worse when it comes to 7″ singles – I can’t remember what I already have and, at 50p or less, I can’t be bothered to try to remember.
Which is why I have four copies of Reasons to be Cheerful, Part 3. I really need a trip to the chazza to donate some, and not to buy* some.
(*) who am I kidding?
Reasons to be Cheerful, Part 4: Owning multiple copies of Ian Dury singles.
Not that I understood a word of the technobabble but as someone who gave up physical product a long time ago, one thing I do miss is just perusing the shelves and thinking “Ah, The Congos, haven’t played that in a while…”
…which is why I only ever buy a digital copy if that’s all the artist has made available.
Very true – which is why I hardly ever listen to my digital library and only really CDs or Spotify on the phone. Since the demise of the iPod I haven’t bothered to sync it up though I know I could with MM. Life is too short to mess about with it.
It isn’t just old farts like us. I was in a charity shop and a young (20-something) nurse was explaining to her pal how she is going “off-grid” (has cancelled her Spotify and Netflix subs). She was buying books, DVDs and CDs.
I’m reminded of a scene from a late episode of ‘The Survivors’ where some ‘kids’, gathered together in a shed, are peddling furiously on a dynamo-equipped bicycle rigged up to a Dansette playing some loud youth music. Meanwhile the adults argue and fight over who’s in charge, some time after a global pandemic has destroyed all ‘civilisation’.
She’ll be there, that girl, with a little DVD/CD player and a boyfriend willing to sweat while she blasts out chazza hits on the stereo or watches ironic Terminator movies.