[mythtv-users] mythfilldatabase behavior

[Jon Larson]

Michael T. Dean wrote:
> On 09/26/2007 08:06 PM, Darren wrote:
>   
>> On Thursday 27 September 2007 04:01, Jon Larson wrote:
>>   
>>     
>>> I use mythfilldatabase --file to import program data from an XML file.
>>> Not being a database expert, I assumed that mythfilldatabase basically
>>> compares the mythtv listings to the XML file and does the following:
>>> Programs that are missing in MythTV would be added, programs with
>>> changed attributes would be updated, and programs that are missing in
>>> the XML would be deleted from MythTV as well.
>>>
>>>  Unfortunately I'm finding that programs that are removed from the XML
>>> file (like canceled programs) remain in Myth's upcoming program
>>> listings. Is there a way to fix this? Is there a command equivalent to
>>> "--refresh-all" for the --file function?
>>>
>>> I've looked at the output from mythfilldatabase using "-v all" From this
>>> I can see that the missing programs are not included in the 'MSqlQuery:
>>> SELECT * FROM program' phase, but they are updated when "Marking episode
>>> first showings" and "Marking episode first showings" are run.
>>>
>>> If anyone could help with this I'd really appreciate it.
>>>       
>> This mysql command will delete all records in the program table. Then all you 
>> need to do is import your xmltv file again.
>>
>> DELETE FROM program;
>>     
Thanks--That fixed it for now.
>
> Which, BTW, is basically already done by mythfilldatabase.  Rather than
> the approach that Jon expected, when mfdb is given new data for a
> particular day for a particular video source, all listings for that
> source on that day are deleted from the database (on a
> channel-by-channel basis), 
Thanks for the explanation If I'm viewing the output of mfdb -v all, 
what  would the delete command look like? I ran the command again and 
used grep DELETE and here's what I got:

root at AVPC1:~# mythfilldatabase -v all --file 1 30 
/myth/windows/listings/tv30.xml | grep DELETE
2007-09-27 07:09:08.766 MSqlQuery: DELETE FROM mythlog WHERE logid= 3141 ;
Updated programs: 0  Unchanged programs: 1239
2007-09-27 07:09:10.709 MSqlQuery: DELETE FROM oldprogram WHERE airdate 
< DATE_SUB(CURRENT_DATE, INTERVAL 320 DAY);
2007-09-27 07:09:10.718 MSqlQuery: DELETE FROM program WHERE starttime 
<= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2007-09-27 07:09:10.718 MSqlQuery: DELETE FROM programrating WHERE 
starttime <= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2007-09-27 07:09:10.719 MSqlQuery: DELETE FROM programgenres WHERE 
starttime <= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2007-09-27 07:09:10.719 MSqlQuery: DELETE FROM credits WHERE starttime 
<= DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY);
2007-09-27 07:09:10.719 MSqlQuery: DELETE FROM record WHERE (type = 1 OR 
type = 7 OR type = 8) AND enddate < NOW();
2007-09-27 07:09:10.720 MSqlQuery: DELETE FROM oldfind WHERE findid < 
TO_DAYS(NOW()) - 14;
2007-09-27 07:09:10.722 MSqlQuery: DELETE FROM oldrecorded WHERE 
recstatus <> -3 AND duplicate = 0 AND endtime < DATE_SUB(CURRENT_DATE, 
INTERVAL 10 DAY);
2007-09-27 07:09:10.730 MSqlQuery: DELETE FROM mythlog WHERE logid= 3142 ;

I don't see where the listings are deleted, so I'm either 
misunderstanding these commands, or the delete command is missing for 
some reason.
> then the new data is inserted.  mfdb does not
> update the listings data--it just deletes it then inserts the new.
> So, if programs are sticking around, they probably exist in some other
> video source or the mfdb command line is specifying a wrong video source
> or something.
>   
You can see the current command I'm using above (minus grep of course). 
I only use a single source, which I can confirm is source 1. I can also 
confirm that there are no other sources in the 'videosource' table. I 
run the command in a chron job using the root account.

Jon

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mythtv.org/pipermail/mythtv-users/attachments/20070927/5207cd9d/attachment.htm