Today’s adventure in coding was to continue to moving Blu-ray support into my DVD ripper software. The next step was getting the priority audio codecs that I wanted when remuxing the exported tracks.
Blu-rays support a number of advanced audio codecs, including support for the same ones on DVDs. When I’m ripping DVDs or Blu-rays, I like to choose the preferred audio codec, based on the best format.
My DVD archiving tools (dart) use PostgreSQL as the backend. I won’t go into the details of my setup because that would take a very, very long time … but if you want to see the database schema for whatever reason, here it is.
In my audio table, I keep track of the formats with a short string. Here’s the names as well, plus this is in order of preferred codecs:
- lpcm – LPCM unompressed audio
- truhd – Dolby Digital TruHD (Atmos)
- dtshd-ma – DTS-HD Master Audio
- dtshd – DTS-HD
- DTS – DTS
- ac3 – Dolby Digital
Normally what happens in a disc structure is that the Blu-ray will have the audio tracks indexed by the best quality as well. So you’d have the first one be dtshd-ma, and then a dts with 6 channels, and then a stereo dts, etc.
That’s not always the case though, and since I’m pedantic, I hunt out the examples where they are not in that order.
I’m dumping the query here, because I’m likely going to want to know what it is — I’m looking for all examples where the audio index (ix) is set to one of the higher quality ones — implying that it is coming after a lower quality one (dts, ac3).
SELECT id, track_id, ix, format, language FROM audio WHERE track_id IN (SELECT track_id FROM audio WHERE format in(‘lpcm’, ‘dtshd-ma’, ‘truhd’, ‘dtshd’) and ix > 1) ORDER BY track_id, ix;
I’m trimming the results here to show samples. Here’s one where Dolby Digital is the first English track, and Dolby TruHD is the second.
id | track_id | ix | format | langcode -------+----------+----+--------+---------- 62551 | 48679 | 1 | ac3 | eng 62552 | 48679 | 2 | truhd | eng 62555 | 48679 | 5 | ac3 | eng
So, the question is, when selecting the audio track, how do I get the one with the best quality — or, more specifically, the one with the codec I want when multiple are available?
Normally if I was just doing a straight grab, without wanting too much complexity, I’d simply select the first English audio track and assume that’s the correct one. A safe assumption, but this is me.
As far as a dataset goes, I’ve got I think about 100 Blu-rays, and this query I’m pulling on all the audio tracks of all the Blu-rays, and I’ve only got 14 title tracks where they are “out of order.” So, very small chance of it happening.
Anyway………. this post is all about how to do a query so that I can get those in the preferred order I want.
And here’s the answer — in your ORDER BY clause, you add one for each format, and then descend based on whether that matches or not.
Writing the query will probably make more sense:
SELECT id, track_id, ix, format, langcode FROM audio WHERE track_id = 46879 AND langcode = ‘eng’ AND active = 1 ORDER BY format = ‘lpcm’ DESC, format = ‘truhd’ DESC, format = ‘dtshd-ma’ DESC, format = ‘dtshd’ DESC, format = ‘dts’ DESC, format = ‘ac3’ DESC, ix;
Doing an order by on a match of a column will return a 1 or 0 (true or false). By default it’s going to do it ascending (ASC), which would return the trues second (1 > 0), so flip it around to DESC.
Now if I ran that same query with the track id in my first example, here’s the new result set:
id | track_id | ix | format | langcode -------+----------+----+--------+---------- 62552 | 48679 | 2 | truhd | eng 62551 | 48679 | 1 | ac3 | eng 62555 | 48679 | 5 | ac3 | eng
You can see that the truhd one got bumped to the top!
Here’s a second example, where even though the audio track index is already in the order I prefer, I’m going to switch around my preferences for sake of this documentation.
Here’s the original result set:
id | track_id | ix | format | langcode -------+----------+----+----------+---------- 60444 | 46895 | 1 | truhd | eng 60445 | 46895 | 2 | dtshd-ma | eng 60446 | 46895 | 3 | ac3 | eng 60451 | 46895 | 8 | ac3 | eng
And this time I want to prioritize the DTS-HD audio codecs over Dolby’s.
The new query, where I just change the preference order:
SELECT id, track_id, ix, format, langcode FROM audio WHERE track_id = 46895 AND langcode = ‘eng’ AND active = 1 ORDER BY format = ‘lpcm’ DESC, format = ‘dtshd-ma’ DESC, format = ‘dtshd’ DESC, format = ‘truhd’ DESC, format = ‘dts’ DESC, format = ‘ac3’ DESC, ix;
And here’s the new result set:
id | track_id | ix | format | langcode -------+----------+----+----------+---------- 60445 | 46895 | 2 | dtshd-ma | eng 60444 | 46895 | 1 | truhd | eng 60446 | 46895 | 3 | ac3 | eng 60451 | 46895 | 8 | ac3 | eng
There is another way to do it, which is using CASE statements inside your query. That’s what I do on DVDs where there’s only DTS or Dolby Digital. That’s another story, though.
So, now when I rip and remux my Blu-rays, I can keep only the audio tracks with preferred codecs that I like.
Oh, and if you want to see my apps that I use to copy my Blu-rays, it’s in my bluray_info github repo, the bluray_copy app, which I also wrote.
The irony of all of this is my audio receiver only supports 5.1 channels. But I can’t imagine how that’s relevant. 😉 Enjoy your audio!