Mailing List Archive: MythTV: Mythtvnz

TV One & BBC World listings

Index | Next | Previous | View Threaded

nhenwood.lists at gmail

Aug 25, 2008, 3:05 AM

Post #1 of 4 (372 views)
Permalink

TV One & BBC World listings

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;

_______________________________________________
mythtvnz mailing list
mythtvnz[at]lists.linuxnut.co.nz
http://lists.ourshack.com/mailman/listinfo/mythtvnz
Archives http://www.gossamer-threads.com/lists/mythtv/mythtvnz/

mike at mikedilger

Aug 25, 2008, 4:19 AM

Post #2 of 4 (357 views)
Permalink

Re: TV One & BBC World listings [In reply to]

Neil Henwood wrote:
> 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')
>

If you are inserting multiple rows, try using two sets of parens around
the select. I can't remember if that works.

If not, you can use a MySQL procedure to do the loop. Here's a sample
bare-bones MySQL procedure, that can loop around multiple output lines
from a select.

1a - replace "ALPHA" with the type for chanid, and BETA with the type
for title
1b - replace gamma,delta,epsilon with the specific field names of the
select "*" construct, and define
containers to catch the fetched select results in (fgamma,
fdelta, fepsilon)
1b - fill in what you want to do
1c - rename the procedure something better.

I didn't read your whole email. If this is helpful and you want to ask
more questions, go right ahead.

-Mike

delimiter //
DROP PROCEDURE IF EXISTS procedurename
//
CREATE PROCEDURE procedurename(inchanid ALPHA, intitle BETA)
# If you want a function instead, add this here: "RETURNS type"
MODIFIES SQL DATA
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT gamma,delta,epsilon FROM program WHERE
chanid<=>inchanid AND title<=>intitle;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DECLARE fgamma BLAH;
DECLARE fdelta BLAH;
DECLARE fepsilon BLAH;

OPEN cur;

mainloop: LOOP
FETCH cur INTO fgamma, fdelta, fepsilon;
IF done THEN
LEAVE mainloop;
END IF;

# If you need to move to the next loop iteration:
# ITERATE mainloop;

# if you are done
# LEAVE mainloop;

# Do something interesting right about here.

END LOOP mainloop;

CLOSE cur;

# If you make this a function instead:
# RETURN blah

END;
//
delimiter ;

_______________________________________________
mythtvnz mailing list
mythtvnz[at]lists.linuxnut.co.nz
http://lists.ourshack.com/mailman/listinfo/mythtvnz
Archives http://www.gossamer-threads.com/lists/mythtv/mythtvnz/

nhenwood.lists at gmail

Aug 25, 2008, 12:49 PM

Post #3 of 4 (345 views)
Permalink

Re: TV One & BBC World listings [In reply to]

Michael Dilger wrote:
> I didn't read your whole email. If this is helpful and you want to ask
> more questions, go right ahead.
>
> -Mike
>
>
Thanks Mike. I will have a closer look tonight when I get home and see
if I can get it working in my system.

Cheers

Neil

_______________________________________________
mythtvnz mailing list
mythtvnz[at]lists.linuxnut.co.nz
http://lists.ourshack.com/mailman/listinfo/mythtvnz
Archives http://www.gossamer-threads.com/lists/mythtv/mythtvnz/

mythtvnz at hotblack

Aug 25, 2008, 2:05 PM

Post #4 of 4 (345 views)
Permalink

Re: TV One & BBC World listings [In reply to]

Neil Henwood wrote:
> 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
>

I have something hacky that I've been using for a while, it doesn't try
and tidy up the small gaps at the begining or end. It might have the
answer to Step 6.

My chanids are 1001 One & 2093 BBC World

REPLACE 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
1001, new.starttime, new.endtime, new.title, new.subtitle,
new.description, new.category, new.category_type, new.airdate,
new.stars, new.previouslyshown, new.title_pronounce, new.stereo,
new.subtitled, new.hdtv, new.closecaptioned, new.partnumber,
new.parttotal, new.seriesid, new.originalairdate, new.showtype,
new.colorcode, new.syndicatedepisodenumber, new.programid, new.manualid,
new.generic, new.listingsource, new.first, new.last, new.audioprop,
new.subtitletypes, new.videoprop
FROM program as new JOIN program as old
ON (new.starttime>=old.starttime AND new.endtime<=old.endtime)
WHERE (old.chanid=1001 AND old.title='BBC World') AND new.chanid=2093;

DELETE FROM program WHERE title='BBC World' AND chanid=1001;

- Wade

_______________________________________________
mythtvnz mailing list
mythtvnz[at]lists.linuxnut.co.nz
http://lists.ourshack.com/mailman/listinfo/mythtvnz
Archives http://www.gossamer-threads.com/lists/mythtv/mythtvnz/

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact lists@gossamer-threads.com