[mythtvnz] TV One & BBC World listings

[Neil Henwood]

Hi All

Tonight was the night that I decided to get rid of my extra channel 
which only has BBC world on it and only there due to TVNZ only listing 
'BBC World' in there program guide, and not the full BBC World listings.

Now I was originally going to post this as a somewhat complete solution, 
but I have become unstuck and need some guidance from a MySQL guru that 
I am sure will be on here.

Basically what works,  works Ok but is by far not a very elegant 
solution due to my limited knowledge of SQL. It is by no means in any 
way ready to actually be used as there are a couple of things that I 
need help with to actually get it working.

1)  I need to know how to loop the whole thing while there are still 
results from this SQL (select * from program where chanid=1081 and title 
='BBC World')
2 In steps 4 & 5 I need to know how to update the start and finish time 
for the BBC Shows so that they fit nicely into the TV One listings 
rather than just overlapping. The start and end time are located in test 
table as part of Step 2.
    I was trying things like joins and the like but I haven't figured it 
out as yet. Will try again later when my brain is less fried.
3)  I need to tidy up how I delete the existing 'BBC World' listing from 
TV One in Step 6
    Its NASTY but it works
4)  I have no idea as to how step 2 works, and I dont understand why the 
'group by chanid' line works in this case, but it does exactly what I 
want it to do.
5)  I need to figure out a more generic way of defining what chanid that 
it should use, rather than my hard coded values of TVOne=1081 and BBC=1090


Please note everyone this is still in Alpha stage and has the 
possibility of really breaking you system to a very large extent. IT 
still does not run on my system properly. I was originally not going to 
post the whole script yet, but thought it wise to fully understand what 
I was doing rather than second guess.

If someone could give me some pointers to get this (initially steps 4 & 
5) sorted I will release it to the wider MythTVNZ users to that they can 
all have nice populated listings :)


Thanks

Neil




==============8<-----------------------------------
# Step1
#Create Temp Table to do my work in.
Create Table if not exists test like program;

# Step2
#Populate Temp Table with TV One BBC World Start and end times for one 
days showing
insert into test
(chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop)
    (select
chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop
 from program where chanid=1081 and title ='BBC World'
group by chanid
);

# Step3
#Select Program listings from BBC World where times inbetween times 
above and populate temp table
insert into test
(chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop)
    (select
chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop
 from program where chanid=1090 and endtime > (select starttime from 
test where chanid=1081) and starttime < (select endtime from test where 
chanid=1081)
);
# Step4
#Update first program each day with new start time reflecting when TV 
one starts showing BBC
update

#
#I need to figure out how to do this
#
# Step5
#Update last program each day with new end time reflecting when TV one 
finishes showing BBC

#
#I need to figure out how to do this
#
# Step6
#Remove TV One BBC Listing from normal program table

#Have to rename as I don't know how to do it gracefully - This needs 
fixing, but it works
update program join test on program.chanid = test.chanid and 
program.chanid = test.chanid and program.starttime=test.starttime
set program.title = 'You should never see this as it should get deleted 
straight away'
where program.chanid=1081;


#Actually remove the listing - This needs fixing but it works
delete from program where title = 'You should never see this as it 
should get deleted straight away';

# Step7
#Remove TV One BBC Listing from test program table
delete from test where chanid=1081;

# Step8
#Update temp table channel id to reflect change from BBC to TV one
update test set chanid=1081 where chanid=1090;

# Step9
#Populate normal program table with new listings of BBC
insert into program
(chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop)
    (select
chanid, starttime, endtime, title, subtitle, description, category, 
category_type, airdate, stars, previouslyshown, title_pronounce, stereo, 
subtitled, hdtv, closecaptioned, partnumber, parttotal, seriesid, 
originalairdate, showtype, colorcode, syndicatedepisodenumber, 
programid, manualid, generic, listingsource, first, last, audioprop, 
subtitletypes, videoprop
 from test where chanid=1081
);

# Step10
#Clean up test table
drop table test;