[mythtv] Channel Management Ideas and Plans

[Gary Buhrmaster]

On Sun, Jan 27, 2019 at 9:09 PM David Engel <david at istwok.net> wrote:
> Please provide comments or other suggestions here.

I am in transit to another location, so when this
gets posted is unclear (next time wifi is close by).
Anyway, I do have a few thoughts (surprise), even
if I don't have time right at the moment to get
everything written down I would like to have
considered.  So this is just some immediate ideas.

First, I philosophically do not like duplicate data
in tables.  One table is the authoritative table, or
there are none (I don't have the ability to check
if that is a direct rule of Codd while I am writing
this, but it certainly is derivable from those rules).

The right answer for some of these issues is often
what are called effective dated tables (with the
additional of effective sequences for slightly more
complex update scenarios).  That means every row
has a date it starts to be valid.  So (using John's
example of free promos for a limited time period),
the row is currently marked with the last update
date with no visibility.  There is an additional row
marked as being visible with the effective date of
(say) next Friday, and then there is another row
marked as invisible on the Monday after.  At any
date one can determine visibility, as there is a
complete history and future for each row.  To use
this table in many cases, one has to do what is
called a correlated sub-query, looking for the row
which is applicable for the date in question (often
on multiple different tables at the same time using
the appropriately dated rows for the query).  This
means that the recordings on the promo channels
will be scheduled only during the visible period.  It is
an elegant solution to such requirements.  However,
it has a couple of substantial problems.  First there
is the likelihood that probably only a very small
number of MythTV DB experts could write and
understand such queries.  The second is that I am
not sure the current state of the mysql optimizer
performance on such selects and last I knew it
performed badly (as I understand it, it is a hard
problem in the general case (slightly over 20 years
ago, Oracle, which at the time was generally
considered to have one of the best query optimizers
on the planet, could end up performing full table
scans for certain correlated sub-queries for a
very popular ERP solution.  Oracle fixed it when it
was identified, but it was apparently not something
trivial to get right, and the suggestion at the time
was that it was a targeted fix identifying the specific
type of correlated sub-query.)).

Since you are not likely going to effective date the
tables (you need more than just a few experts on
such things to be able to maintain such codes into
the future), I would expect you will need to copy
the data, and you should want 100% of the
information you will want to present as part of the
(old)recorded be copied.  That would include at
least the channel name, number, channel icon, etc.
since (at least for cable) channel 123 may have
been ABC KABC today, but have been CBS KCBS
last week, and you want the data presented to be
valid for when the show was recorded.

btw, the icon data is differently problematic.  It
is not (currently) identified by videosource, so
if one has multiple videosources, and the guide
data provides channel icon names that are the
same (perfectly possible, especially over time,
and has the same effective dated challenges),
you get collisions and incorrect over-writes.
There are a couple of options that have occured
to me (the easiest might be to prefix the icon
file names with the videosource number and a
date for uniqueness, but I think the more elegant
solution is an icon blob table indexed by a uuid,
which could be lazy trimmed when no references
from the (old)recorded and channel tables exist).
That might be a bridge (or db table) too far at
the moment.

That is all I have time for right now (hope this
makes it out to the ether at some point....)