Album duration possible?

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