[mythtv-users] Join DB table recordedprogram to recorded

[George Nassas]

On Dec 28, 2014, at 1:16 PM, Nicolas Krzywinski <myth at site7even.de> wrote:

> I just found out that the database table's fields recordedprogram.starttime and recorded.starttime does not qualify for joining, as it is documented in the wiki: http://www.mythtv.org/wiki/Recordedprogram_table.
> Whereas recorded.starttime seems to represent the real start of recording, recordedprogram.starttime seems to tell the original start of the broadcast.
> 
> Instead, recordedprogram.title = recorded.title and recordedprogram.subtitle = recorded.subtitle seem to be suitable for joining the tables (additionally to chanid, of course).

Not all recordings have a subtitle so in that case you’d get an outer join of every record with a matching title and chanid. Even if you have subtitles you might have several recordings of an episode so you’re back to outer joining. The only reliable way to join is over their primary keys which are chanid and starttime.

That said, it’s curious why recordedprogram has entries which aren’t in recorded. I just checked my db and that’s true for me too. Given that recordedprogram is defined as an adjunct to recorded there should never be rows there without matching recorded rows.

Maybe there’s a good explanation or maybe you’ve uncovered a bug.

- George

P.S. This will change in the next release as recorded.recordedid is replacing chanid/starttime as the way to reference recordings. Not a moment too soon!