[mythtv] patch: mythweb listings
Andy Davidoff
dert at pobox.com
Wed Mar 5 20:35:56 EST 2003
Erm, looks like I failed to remove some unrelated hackery. This version
is more subtle. Serves me right for trying to maintain multiple trees.
Apologies...
#if Andy Davidoff /* Mar 05, 18:44 */
> This patch dramatically improves the speed of the listings.php by issuing
> one query per timeslot versus 5 queries * number-of-channels * timeslots.
>
> My listings page used to take about 8 seconds to generate and now takes
> about 2.5 seconds. This could be faster if we altered the database, but...
#endif /* dert at pobox.com */
-------------- next part --------------
Index: mythweb/classes.php
===================================================================
RCS file: /var/lib/cvs/mythweb/classes.php,v
retrieving revision 1.6
diff -d -u -w -r1.6 classes.php
--- mythweb/classes.php 11 Dec 2002 20:57:44 -0000 1.6
+++ mythweb/classes.php 6 Mar 2003 01:34:10 -0000
@@ -137,7 +137,28 @@
var $duration;
var $recordColour;
var $typeColour;
+ var $whenRecord;
+ function willRecord()
+ {
+ return $this->whenRecord;
+ }
+ function alwaysRecord()
+ {
+ return $this->whenRecord & 1<<4;
+ }
+ function channelRecord()
+ {
+ return $this->whenRecord & 1<<3;
+ }
+ function singleRecord()
+ {
+ return $this->whenRecord & 1<<2;
+ }
+ function timeslotRecord()
+ {
+ return $this->whenRecord & 1<<1;
+ }
function setColours($a, $b)
{
$this->recordColour = $a;
Index: mythweb/functions.php
===================================================================
RCS file: /var/lib/cvs/mythweb/functions.php,v
retrieving revision 1.8
diff -d -u -w -r1.8 functions.php
--- mythweb/functions.php 2 Mar 2003 14:10:44 -0000 1.8
+++ mythweb/functions.php 6 Mar 2003 01:34:13 -0000
@@ -357,24 +357,42 @@
//
function setupPrograms ($channelarray, $timearray, $numbSlots)
{
- $channelindex = 0;
- while ($mychannel = $channelarray[$channelindex])
- {
for ($i = 0; $i < $numbSlots; $i += 1)
{
+ $channelindex = 0;
$mytime = $timearray[$i];
$sqltime = $mytime->sqltime;
-
- $chanid = $mychannel->chanid;
-
- $query = "SELECT channel.chanid, starttime, endtime, title, subtitle, description, category, ((UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(starttime)) / 60 ) as duration, channel.channum FROM program,channel WHERE program.chanid = " . $chanid . " AND program.chanid = channel.chanid AND " .
- "starttime < " . $sqltime . " AND endtime > " . $sqltime .
- " ORDER BY channel.channum + 0, starttime;";
+ $query = "SELECT ".
+ "sum(record.type = 4)>0 as 'type4', ".
+ "sum(record.type = 3 and record.chanid = program.chanid)>0 as 'type3', ".
+ "sum(record.type = 2 and record.chanid = program.chanid and ".
+ " record.starttime = sec_to_time(time_to_sec(program.starttime)) and ".
+ " record.endtime = sec_to_time(time_to_sec(program.endtime)))>0 as 'type2', " .
+ "sum(record.type = 1 and record.chanid = program.chanid and ".
+ " record.starttime = sec_to_time(time_to_sec(program.starttime)) and ".
+ " record.startdate = from_days(to_days(program.starttime)))>0 as 'type1', ".
+ "channel.chanid, ".
+ "channel.channum, ".
+ "program.starttime, ".
+ "program.endtime, ".
+ "program.title, ".
+ "program.subtitle, ".
+ "program.description, ".
+ "program.category, ".
+ "((UNIX_TIMESTAMP(program.endtime) - UNIX_TIMESTAMP(program.starttime)) / 60 ) as duration ".
+ "FROM channel left join program using (chanid) left join record using (title) ".
+ "WHERE program.starttime < " . $sqltime . " AND program.endtime > " . $sqltime." ".
+ "GROUP BY program.chanid ORDER BY channel.channum + 0;";
$result = mysql_query($query) or die("Gadzooks! I can't open the program table.");
while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
{
+ while ($line["channum"] != $channelarray[$channelindex]->channum)
+ {
+ $programarray[$channelindex][$i] = null;
+ $channelindex++;
+ }
$myprog = new ProgramInfo;
$myprog->title = $line["title"];
$myprog->subtitle = $line["subtitle"];
@@ -385,13 +403,16 @@
$myprog->progType = $line["category"];
$myprog->duration = $line["duration"];
$myprog->description = $line["description"];
+ $myprog->whenRecord += $line["type1"]<<1;
+ $myprog->whenRecord += $line["type2"]<<2;
+ $myprog->whenRecord += $line["type3"]<<3;
+ $myprog->whenRecord += $line["type4"]<<4;
$programarray[$channelindex][$i] = $myprog;
+ $channelindex++;
}
mysql_free_result($result);
- }
- $channelindex += 1;
}
return $programarray;
}
Index: mythweb/listings.php
===================================================================
RCS file: /var/lib/cvs/mythweb/listings.php,v
retrieving revision 1.11
diff -d -u -w -r1.11 listings.php
--- mythweb/listings.php 2 Mar 2003 14:10:44 -0000 1.11
+++ mythweb/listings.php 6 Mar 2003 01:34:13 -0000
@@ -210,34 +210,7 @@
}
$proginfo->setColours($finalColour, $finalColour);
$recordArrayIndex = 0;
- //
- // Check and see if this is in Record
- // Once data
- //
- $isBeingSingleRecorded = isInOnceRecord($proginfo->chanid, $proginfo->startts);
- if ($isBeingSingleRecorded)
- {
- $proginfo->setColours($list_reccolour, $finalColour);
- }
- //while($aRecord = $recordArray[$recordArrayIndex])
- //{
- // if($aRecord->chanid == $proginfo->chanid &&
- // $aRecord->starttime == $proginfo->startts)
- // {
- // $proginfo->setColours($list_reccolour, $finalColour);
- // }
- //
- // $recordArrayIndex += 1;
- //}
- //
- // Check and see if this is an
- // always record or a timeslot
- // record
- //
- if(isInAlwaysRecord($proginfo->title) ||
- isInChannelAlwaysRecord($mychannel->chanid, $proginfo->title) ||
- isInTimeslotRecord($proginfo->chanid, $proginfo->startts, $proginfo->endts, $proginfo->title))
- {
+ if($proginfo->willRecord()) {
$proginfo->setColours($list_reccolour, $finalColour);
}
More information about the mythtv-dev
mailing list