Album duration possible?

[entdgc]

I currently use...

Album.displayFormat={$album^$date^ [^]}, Title.displayFormat={$title^$#AudioDuration^ [^]}

which works really well. It would be perfect if I could somehow add the total album duration in the same way as I do for each track. Can anyone think of a way of doing this? As far as I can tell this would require summing all the track timings either 1. within my metadata editor (normally Metadatics on IOS) or 2. within MinimServer. I suspect this is beyond MinimServer which leaves option 1. Anyone know of a meta editor that can do this AUTOMATICALLY - I don't fancy doing this on hundreds of albums manually!

Thanks

[simbun]

I guess it depends how bad you want it ;-)

If you have a Windows PC then there is a way to do it with foobar2000 and the foo_sqlite plugin. The plugin allows you to run SQL against your music collection (it stores all the tags internally during indexing) which is amazingly useful. I've used the plugin for various purposes before including to pull data for use in MinimServer tagUpdate scripts, but I hadn't tried updating tags before.

I've only run this against a small sample of albums so I have no idea how this would scale, but all you'd need to run is:

Code:

update PlaylistUpdatable
  set album_duration=(select cast(floor(sum(length_seconds)/60) as varchar) || ':' || cast(printf('%02.0f',floor(sum(length_seconds)%60)) as varchar)
                        from Playlist
                        where     playlist_index = active_playlist()
                              and PlaylistUpdatable.album = Playlist.album
                        group by album)
  where playlist_index = active_playlist();

There is another line that creates the PlaylistUpdateable virtual table, but it's causing the post to fail, so I'll leave it out for now just to keep it formatted nicely.

I doubt you want to go to these lengths, but I can't think of another way of doing it.

[entdgc]

That is remarkably interesting @simbun. Never used foobar though I have heard of it. As a bit of a gadget geek this will be a nice little project to investigate so thanks for the pointers. I use mp3tag on my PC and noticed it does have an export feature which I was going to investigate to see if I could export track timings to a csv file for Excel and go from there. Will report back if either method works...
Thanks again.

[simbun]

(27-02-2022 12:45)entdgc Wrote:  Never used foobar though I have heard of it. As a bit of a gadget geek this will be a nice little project to investigate so thanks for the pointers.

Well I was not expecting that :-) Most people shy away from it as it's not particularly 'ready to go'.

I was short of time this morning so when I had something working - which was surprisingly quickly, once I'd realised that one of my other foobar components was causing a conflict that caused foobar to crash every time I ran the sql - I just posted it thinking no one would really be interested in going down this route.

I'm off to golf soon, but was planning to spend a little bit of time on it this evening, just to see what's possible, so could put some basic instructions together to get it working, as it will need modifying for your purposes anyway.

[simbun]

Just in case you or someone else is interested...

Here's some basic instructions that should get you going if you're not already.

Firstly, create a directory containing copies of your music to play with. It doesn't have to be much, a dozen albums or so is probably enough (if you get comfortable with foobar it can produce truncated versions of your files useful for testing as they contain all the tags but only occupy a fraction of the space).

Download the latest foobar and the sqlite plugin.

• Run foobar2000_v1.6.10.exe, choose 'Custom', choose 'Portable installation' and select a path.
• Allow the installer to 'Run foobar2000' and pick the 'Album List + Properties (tabbed)' layout with whatever colour you want.
• Select the menu item 'File > Preferences > Media Library' and specify the path of your test music.
• Whilst still in the Preferences window, click 'Components > Install' and select foo_sqlite.fb2k-component.
• Click 'Apply', then OK to restart, and foobar should scan your library and display the contents on the left hand side.
• Go back to 'File > Preferences > Media Library > SQLite viewer > Playlist table' and add the tag that you want to store the album duration in (just put the name in the Name property and click OK)
• Drag some files from the left pane (Album List) to the right pane (playlist window).
• Select 'Library > SQLite console' and a popup window should appear.
• Copy the following code into the window and Execute it.
  

Code:

select album,
       format_length_hours(sum(length_seconds)) as album_duration
  ffrom Playlist
  where playlist_index = active_playlist()
  group by album

NOTE: Change ffrom to from (I had to change that to be able to post)

In the above example (and the final example) you'll obviously need to replace 'album' with whatever set of tags you use to uniquely identify an album so that the aggregation is correct e.g. MUSICBRAINZ_ALBUMID, 'albumartist, album, date' e.t.c

You should see the total duration of each album in the grid below the sql.

The query is using the tracks you've added to the Playlist window, so if you've only added half an album, the duration will reflect that. You can use MediaLibrary instead of Playlist in the query that will act on your whole library, but you need to be careful, and you'd need to add the tag to the 'SQLite viewer > MediaLibrary table' just as you did earlier to the 'Playlist table'.

Assuming that the sql above is working, now you just need to run the code that updates the tags on the files in the playlist.

Code:

drop table if exists PlaylistUpdatable;
ccreate virtual table `PlaylistUpdatable` using MetaDB_Module(no_multivalue_split, playlist);

drop table if exists albumSummary;
ccreate table albumSummary as
  select album,
         format_length_hours(sum(length_seconds)) as album_duration
  ffrom Playlist
  where playlist_index = active_playlist()
  group by album;

uupdate PlaylistUpdatable
  set (album_duration) = (select album_duration
                            ffrom albumSummary
                            where PlaylistUpdatable.album = albumSummary.album)
  where playlist_index = active_playlist();

drop table if exists PlaylistUpdatable;
drop table if exists albumSummary;

NOTE: I've had to use ccreate, ffrom and uupdate on this one.

At that point, check your files tags to make sure it hasn't changed anything unexpected. If you're using flac it should be fine, if mp3 then there are options to check to make sure it's writing the correct version (File > Preferences > Advanced > Tagging).

Before you run it on any of your real music make sure you have a backup (which you should have anyway).

Let me know if you have any problems.

[entdgc]

Followed your instructions and worked a treat....

   

Needs better formatting but getting late so that is for tomorrow...

The only 'fault' in your instructions was that foo_sqlite.fb2k-component was not on my system but a quick Google and I was able to download it from here

https://www.foobar2000.org/components/view/foo_sqlite

Was the code you have written in SQL language?

Once again @simbun that was really clear and useful info!

Dave

[simbun]

(01-03-2022 01:31)entdgc Wrote:  Followed your instructions and worked a treat....
Needs better formatting but getting late so that is for tomorrow...

That's good to hear.

I assume you mean the formatting in terms of the MinimServer .displayFormat? If you're talking about formatting the duration itself you're probably best to look at using printf e.g.

Code:

select pprintf('%02dh:%02dm:%02ds', sum(length_seconds)/3600, (sum(length_seconds)%3600)/60, sum(length_seconds)%60) as duration
  ffrom playlist
01h:47m:15s

NOTE: I had to use pprint and ffrom to get it to post.

If you have something specific in mind I can help you with that as it can get a bit messy.

(01-03-2022 01:31)entdgc Wrote:  The only 'fault' in your instructions was that foo_sqlite.fb2k-component was not on my system

RTFM ;-)

(27-02-2022 19:32)simbun Wrote:  Download the latest foobar and the sqlite plugin.

(01-03-2022 01:31)entdgc Wrote:  Was the code you have written in SQL language?

When you're in that console you're working within a SQLite (3.20.0) environment.
From the home page:

Quote:SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day.

Most of what you'll be able to use in that environment is native SQLite, but the plugin also comes with MetaDB_Module(s) which allow you to interact with foobar playlists and files, and a bunch of other custom functions that aren't available to SQLite.

If you run

Code:

select * from playlist

you'll see just how much metadata you have available to you, things like:

• Music Tags: All your music tags
• Audio format: bitrate, bitspersample, channels, codec, encoding
• File specifics: directoryname, filename, filesize, last_modified, length_seconds
• playlist: playlist_name, playlist_item, item_is_selected, item_is_playing
  

A couple of examples of what I've used this tool for:

• QA purposes: making sure each artist only has one corresponding artistsort value across the library
• Reporting: Aggregating to one record per album to build a spreadsheet of my music collection
• tagUpdate: For albums where only the last disc has a discsubtitle (typically a bonus disc), I use this to give the prior discs a discsubtitle (of album name) so I can play the non bonus disc separately. I know I could add it manually to the tags but if it's not on the official disc then it's not going in the tags :-)
  

as well as many others.

I'll subscribe to this thread so if you or anyone else has problems I'll (hopefully) be able to help, as I'm as passionate about foobar as I am about MinimServer.

[entdgc]

(01-03-2022 10:37)simbun Wrote:  I assume you mean the formatting in terms of the MinimServer .displayFormat?

Yes the minimserver display - which is a whole new world of pain! I simply cannot get my head around the logic of the formatting statement. More by luck than judgement I have ended up something I am happy with.

(01-03-2022 10:37)simbun Wrote:  RTFM ;-)

Doh! I did say it was getting late

(01-03-2022 10:37)simbun Wrote:  I'll subscribe to this thread so if you or anyone else has problems I'll (hopefully) be able to help, as I'm as passionate about foobar as I am about MinimServer.

Thanks again Sinbum for being so supportive of the community and going well above and beyond!

Dave

[lyapounov]

(27-02-2022 19:32)simbun Wrote:  Just in case you or someone else is interested...


Download the latest foobar and the sqlite plugin.

Hi simbun

just a weird question, as you seem to have a good knowledge of foobar.

All my library is on a synology, in a mariadb (7400 albums...). I access this db in php, using the getID3 library to extract infos from the tracks (including the duration BTW).

I would like to extract the foobar Dynamic Range meter for each track. Have you heard of any library which I could use ?

Thx !

[simbun]

(04-03-2022 23:39)lyapounov Wrote:  All my library is on a synology, in a mariadb (7400 albums...). I access this db in php, using the getID3 library to extract infos from the tracks (including the duration BTW).

Do you mean that your music is stored as normal audio files on a Synology, and that you're using php (via getID3) to populate a MariaDB with the track information, or are you writing from the database to your files? I've loaded up a MusicBrainz database dump into MariaDB before for cleaning up my existing tracks - writing via foobar - but I don't think I have a need to put it into a database - and don't want covincing that I do :-)

(04-03-2022 23:39)lyapounov Wrote:  I would like to extract the foobar Dynamic Range meter for each track. Have you heard of any library which I could use ?

Just so I'm clear, are you asking for a tool to extract the dynamic range tags, or are you wanting a Dynamic range library to use?
If the latter, I'm not aware of a library for this, but you could ask on the hydrogenaudio forums (or the foobar forum within that).