[mythtv] [PATCH] Split program.{start, end}time into separate date and time columns

Matt Zimmerman mdz at debian.org
Fri Mar 19 11:43:45 EST 2004

On Fri, Mar 19, 2004 at 10:12:06AM -0600, David Engel wrote:

> On Thu, Mar 18, 2004 at 10:27:56PM -0800, Matt Zimmerman wrote:
> > Good news: this should speed up the scheduler query a lot, and probably a
> > number of other things as well
> I took a quick look through your patch.  I noticed two minor problems
> and one more significant one.
> 1. In a few DB queries, you replaced "ORDER BY starttime,..." with
>    "ORDER BY starttime,startdate,...".  The time and date should be
>    reversed.

It was only one instance in progfind.cpp that I got reversed; the rest are
correct.  Fixed.

> 2. In a few DB queries, you replaced something like "WHERE
>    ... starttime >= xyz" with "WHERE ...  startdate >= xyzdate AND
>    starttime >= xyztime".  This should be like "WHERE ... startdate >
>    xyzdate OR (startdate = xyzdate AND starttime >= xyztime)".

Hmm.  You're right, of course, but yuck. :-/

There are some conversion functions in mysql that might make this simpler,
but a) I'm not sure whether they are smart about indexes, and b) they're
only available in mysql 4.1.1+.

> 3. The recordoverride JOIN in AddNewRecords needs to be fixed to also
>    check the date.

I have a bad feeling that any mangling I do there is going to defeat the
indexing for this part of the query, and that we will need to split
date/time in the other tables in order to get the most out of this.

> > Bad news: I have no way to test it as yet.  It compiles, and the db
> > conversion code works correctly, but beyond that, I can make no
> > guarantees.  Anyone who wants to try it must be prepared to deal with (and
> > preferably fix) the breakage that awaits.
> Might I suggest a more conservative, iterative approach to such a big
> change.  Start by adding the split date/time as new columns without
> changing the old columns and making sure the new columns get populated
> in all of the appropriate places first.  Then, convert each use of the
> old columns to use the new columns one at a time where they can be
> tested without breaking anything else.

Might I suggest a database abstraction layer?  This is only a big change
because the code to access these columns is spread out across 17 files with
embedded literal SQL.  Something like this will happen again.

 - mdz

More information about the mythtv-dev mailing list