selecting the best bluray audio codec in postgres through sql order by statements

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!

Advertisements