[mythtv] conflict resolution

Edward Wildgoose Edward.Wildgoose at FRMHedge.com
Tue Nov 4 04:54:32 EST 2003

I think this is exactly right, but you may find execution speed is different if you move the +/- INTERVAL stuff on the other side of the < or > sign.  Certainly on SQLServer this is worth looking at because it will change whether it can use an indexed lookup into each table (can't use an index to help you evaluate a function!)

As you point out we need an order by so that we can be deterministic, otherwise the frontend might show that we are recording one thing but the backend records another...  I guess we need to order by the difference in dates and probably also by the starttime (ie pick earlier program if there are two choices exactly the same distance apart)

OK which schedule types should this apply to?  All that are date slot specific?  or only the regular week/day ones, ie not the single record?

Ed W

-----Original Message-----
From: Chris Pinkham [mailto:cpinkham at bc2va.org]
Sent: 03 November 2003 18:50
To: mythtv-dev at mythtv.org
Subject: Re: [mythtv] conflict resolution

> SQL is not great at ordered operations, it's a set based language, and trying
> to order by closest match is going to be slow and difficult I think.
> I need to have a think about how to make the SQL re-usable so we get the
> same query everywhere in the code otherwise it's going to get a little
> hairy working out what is actually going to be recorded...

To do the fuzzy match, couldn't you first compare something like this:

	((record.starttime > program.starttime - INTERVAL $fuzzy_minutes minutes) and
	 (record.starttime < program.starttime + INTERVAL $fuzzy_minutes minutes) and
	 (record.endtime > program.endtime - INTERVAL $fuzzy_minutes minutes) and
	 (record.endtime < program.endtime + INTERVAL $fuzzy_minutes minutes))

instead of the current

	((TO_DAYS(record.startdate) = TO_DAYS(program.starttime)) and
	 (TIME_TO_SEC(record.endtime) = TIME_TO_SEC(program.endtime)) and
	 (TO_DAYS(record.enddate) = TO_DAYS(program.endtime)))

Then to order the closest match use the absolute difference in the unixtimes
of record.starttime and program.starttime.

select blah, blah,
	abs(unix_timestamp(record.starttime) - unix_timestamp(program.starttime))
		as secs_diff
	from blah
	where blah
	order by secs_diff;

Hopefully I did that right.  Too many interruptions during my "lunch" "hour"
(both in quotes because both have fuzzy definitions). :)


More information about the mythtv-dev mailing list