[mythtv-users] Big Beautiful Scheduler Query
Jay R. Ashworth
jra at baylink.com
Fri Sep 7 04:37:49 UTC 2007
As it happens, there *is* an online SQL pretty printer, at
www.sqlinform.com.
Herewith, then the BBSQ, courtesy of crs23 and the boyz:
SELECT DISTINCT channel.chanid ,
channel.sourceid ,
program.starttime ,
program.en dtime ,
program.title ,
program.subtitle ,
program.description ,
channel.channum ,
channel.callsign ,
channel.name ,
oldrecorded.endtime IS NOT NULL AS oldrecduplicate ,
program.category ,
record.recpriority ,
record.dupin ,
recorded.endtime IS NOT NULL AS recduplicate ,
oldfind.findid IS NOT NULL AS findduplicate ,
record.type ,
record.recordid ,
program.starttime - INTERVAL record.startoffset minute AS recstartts ,
program.endtime + INTERVAL record.endoffset minute AS recendts ,
program.previouslyshown ,
record.recgroup ,
record.dupmethod ,
channel.commfree ,
capturecard.cardid ,
cardinput.cardinputid ,
UPPER(cardinput.shareable) = 'Y' AS shareable ,
program.seriesid ,
program.programid ,
program.category_type ,
program.airdate ,
program.stars ,
program.originalairdate ,
record.inactive ,
record.parentid ,
(CASE record.type WHEN 6 THEN record.findid
WHEN 9 THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute))
WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday
WHEN 7 THEN record. findid ELSE 0
END) ,
record.playgroup ,
oldrecstatus.recstatus ,
oldrecstatus. reactivate ,
channel.recpriority + cardinput.recpriority,
record.prefinput ,
program.hdtv ,
program.closecaptioned ,
program.first ,
program.last ,
program.stereo FROM recordmatch
INNER JOIN record
ON (recordmatch.recordid = record.recordid)
INNER JOIN program
ON (recordmatch.chanid = program.chanid
AND recordmatch.starttime = program.starttime
AND recordmatch.manualid = program.manualid)
INNER JOIN channel
ON (channel.chanid = program.chanid)
INNER JOIN cardinput
ON (channel.sourceid = cardinput.sourceid)
INNER JOIN capturecard
ON (capturecard.cardid = cardinput.cardid)
LEFT JOIN oldrecorded as oldrecstatus
ON ( oldrecstatus.station = channel.callsign
AND oldrecstatus.starttime = program.starttime
AND oldrecstatus.title = program.title )
LEFT JOIN oldrecorded
ON ( record.dupmethod > 1
AND oldrecorded.duplicate <> 0
AND program.title = oldrecorded.title
AND ( (program.programid <> ''
AND program.generic = 0
AND progra m.programid = oldrecorded.programid)
OR (oldrecorded.findid <> 0 AN D oldrecorded.findid =
(
CASE record.type
WHEN 6
THEN record.findid
WHEN 9
THEN to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute))
WHEN 10
THEN floor((to _days(date_sub(program.starttime, interval time_format(record.fi ndtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record .findday
WHEN 7
THEN record.findid
ELSE 0
END
)
)
OR ( program.generic = 0
AND (program.programid = ''
OR oldrecorded. programid = '')
AND (((record.dupmethod & 0x02) = 0)
OR (program. subtitle <> ''
AND program.subtitle = oldrecorded.subtitle))
AND (((record.dupmethod & 0x04) = 0)
OR (program.description <> ''
AND program.description = oldrecorded.description)) ) ) )
LEFT JOIN recorded
ON ( record.dupmethod > 1
AND recorded.duplicate <> 0
AND program.title = recorded.title
AND recorded.recgroup <> 'LiveTV'
AND ( (program.programid <> ''
AND program.generic = 0
AND program.programid = recorded.programid)
OR (recorded.findid <> 0
AND recorded.findid =
(
CASE record.type
WHEN 6
THEN record.findid
WHEN 9
THEN to_days(date_sub(program.starttime, interval t ime_format(record.findtime, '%H:%i') hour_minute))
WHEN 10
THEN floor((to_ days(date_sub(program.starttime, interval time_format(record.fin dtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record. findday
WHEN 7
THEN record.findid
ELSE 0
END
)
)
OR ( program.generic = 0
AND (program.programid = ''
OR recorded.prog ramid = '')
AND (((record.dupmethod & 0x02) = 0)
OR (program.subt itle <> ''
AND program.subtitle = recorded.subtitle))
AND (((record.dupmethod & 0x04) = 0)
OR (program.description <> ''
AND program.description = recorded.description)) ) ) )
LEFT JOIN old find
ON (oldfind.recordid = recordmatch.recordid
AND oldfind.findid = (CASE record.type
WHEN 6 THEN record.findid
WHEN 9 THEN to_days(date_sub( program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute))
WHEN 10 THEN floor((to_days(date_sub(program.starttime, interval time_format(record.findtime, '%H:%i') hour_minute)) - record.findday)/7) * 7 + record.findday
WHEN 7 THEN record.findid ELSE 0
END) )
{ big finish! }
ORDER BY record.recordid DESC;
Bruce Markey? I lick the dust off your boots.
:-)
Cheers,
-- jra
--
Jay R. Ashworth Baylink jra at baylink.com
Designer The Things I Think RFC 2100
Ashworth & Associates http://baylink.pitas.com '87 e24
St Petersburg FL USA http://photo.imageinc.us +1 727 647 1274
More information about the mythtv-users
mailing list