[mythtv] Re: Re: PATCH: mythfilldatabase v1.43 -- arbitrary end times

[Patrick Pflaum]

I just use this perl script, run after the fill.  It's typical run time 
is 2 seconds, feel free to steal the logic :-)

#!/usr/bin/perl

  use DBI;

  $driver = "mysql";
  $database = 'mythconverg';
  $hostname = 'localhost';
  $user = 'mythtv';
  $password = 'mythtv';
  $dsn = "DBI:$driver:database=$database;host=$hostname";
  $dbh = DBI->connect($dsn, $user, $password);
  $stmt = "select 
chanid,starttime,endtime,title,subtitle,description,category,starttime 
from program order by chanid,starttime";
  $sth = $dbh->prepare($stmt);
  $sth ->execute;

  while ( $ref = $sth->fetch() )
  {
          ( 
$chanid,$starttime,$endtime,$title,$subtitle,$description,$category,$newet 
) = @{$ref} ;

          if ( $contig != $starttime && $lastchan == $chanid  && $contig)
          {
                  $stmt = "update program set endtime = " . 
$dbh->quote($newet) ;
                  $stmt.= ", starttime = starttime where ";
                  $stmt.= "     chanid       = ". $dbh->quote(@last[0]);
                  $stmt.= " and starttime    = ". $dbh->quote(@last[1]);
                  $stmt.= " and endtime      = ". $dbh->quote(@last[2]);
                  $stmt.= " and title        = ". $dbh->quote(@last[3]);
                  $stmt.= " and subtitle     = ". $dbh->quote(@last[4]);
                  $stmt.= " and description  = ". $dbh->quote(@last[5]);
                  $stmt.= " and category     = ". $dbh->quote(@last[6]);
                  print "$stmt\n";
                  $dbh->do( $stmt );
          }

          @last = @{$ref};
          $contig = $endtime;
          $lastchan = $chanid;
  }


J wrote:

>
>>
>> On Monday 03 March 2003 06:45 am, J wrote:
>> > Yes, however when I was experimenting with the algorithm,  I found 
>> that the
>> > batch file to create the temp databases,  was both faster and less 
>> memory
>> > intensive.  Since this approach needs to have the entire program 
>> database
>> > indexed,  doing that without the temp databases would mean that the 
>> whole
>> > program database would have to be read into memory, indexed, and then,
>> > rather than doing comparisons on the very small subset of entries (in
>> > temp2), you would end up reviewing all the entries. For example, my 
>> program
>> > database has 17603 records, but temp2 only contains 465.  Since 
>> mysql is
>> > already optimized for this sort of operation, it seemed the most 
>> efficient
>> > and elegant solution.
>> >
>> > That being said,  is it the use of the temp tables, the batch file, 
>> or both
>> > that you find objectionable?
>>
>> The batch file.  But, why would you need the entire database for 
>> this?  Seems
>> like it'd be pretty simple to just make an internal list of programs you
>> didn't have endtimes for on a per-day basis (should be one per 
>> channel), and
>> then just hit the db for the all next programs' start time once the 
>> next day
>> has been populated.
>>
>> Isaac
>
>
> I had no luck designing a query that would just get the first program 
> on a given day on a given channel, (since there is no fixed start 
> time) hence the indexed, sorted temp table.  Additionally, in order to 
> adjust the end times for the last day captured, you have to revisit 
> them when the next day's programs are grabbed.
>
> I was planning on removing the need for the temp2 table using the 
> method you suggest, but I wanted to make sure that the approach was 
> sound before I started making extensive changes to the existing code.
>
> Joe.
>
>
> _______________________________________________
> mythtv-dev mailing list
> mythtv-dev at snowman.net
> http://www.snowman.net/mailman/listinfo/mythtv-dev