[mythtv] Channel Management Ideas and Plans

[David Engel]

On Mon, Jan 28, 2019 at 09:54:35AM +0000, Gary Buhrmaster wrote:
> 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

I do just that with a database at work.  However, I think that's
overkill for MythTV and "the perfect is the enemy of the good."
Still, I will let it percolate for a while as this is not the first
part I expect to work ong.

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

For recorded, I'm almost completely in the option 3 camp now.  That is
to have a recordedchannel table.  We already have the precedents for
recordedprogram, et al and the code to populate and clean them up when
needed.

For oldrecorded, I'm still undecided.  I personally would be fine with
saying that anything beyonhd channem number, name and maybe icon
simply aren't there for uses where oldrecorded is the primary table.

David

> 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....)
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at mythtv.org
> http://lists.mythtv.org/mailman/listinfo/mythtv-dev
> http://wiki.mythtv.org/Mailing_List_etiquette
> MythTV Forums: https://forum.mythtv.org

-- 
David Engel
david at istwok.net