Overlapping programs cleanup script for TVGuide (1 Viewer)

[Dirk]

Being an database adminstrator myself, I have written a sql script that removes overlapping programs and leaves the smaller ones. For instance things like 'Nederland 3' and 'Z@pp', CNBC, Jetix,
or 'Science Fiction week' are automatically removed.

I have made 2 sqlscripts;
This first sqlscript only shows the results,
the second one really removes them.

The scripts can be started within 'SQLite Database Browser' found in the Docs directory of mediaportal, use the execute SQL tab.
You need to open the file TVDatabaseV21.db3
in the subdirectory database of mediaportal
Don't forget to save it before exitting SQLite Database Browser.

This is also exactly my present problem: how can I automate this?
Or can it be incorporated in mediaportal tvguide import routine itself?

The first/(show) script:
The name of the overlapping program, the names of the first and the last program within the timeframe of the overlapping program are shown.
Followed by the times:
starttime of the overlapping show,
startime of first show,
endtime of last show and the
endtime of the overlapping show

Select p1.strTitle
,      p2.strTitle
,      p3.strTitle
,      p1.iStartTime
,      p2.iStartTime
,      p3.iEndTime
,      p1.iEndTime
From   tblPrograms p1
,      tblPrograms p2
,      tblPrograms p3
where  p2.iStartTime >=  p1.iStartTime
and    p2.iEndTime   <   p1.iEndTime
and    p2.idChannel  =   p1.idChannel
and    p3.iStartTime >   p1.iStartTime
and    p3.iEndTime   <=  p1.iEndTime
and    p3.idChannel  =   p1.idChannel

The second/(delete) script:

Delete tblPrograms p1
where exists (Select 0 
              from tblPrograms p2
              where p2.iStartTime >=  p1.iStartTime
              and   p2.iEndTime   <   p1.iEndTime
              and   p2.idChannel  =   p1.idChannel)
              and  exists (Select 0 
                           from tblPrograms p3
                           where p3.iStartTime >  p1.iStartTime
                           and   p3.iEndTime   <= p1.iEndTime
                           and   p3.idChannel  =  p1.idChannel)

I hope you like it!

 

[Dirk]

Both scripts are bound to the same conditions when finding overlapping programs.
The first script is only there to show you what overlapping programs are found in your tvguide.
The second script will remove them, if you save the file in sqlite browser.

 

[Lyxalig]

Ehm.. does not work

 

[Dirk]

The sql-scripts above are versions with a nice layout.
These here are the raw versions. (less spaces)
The first (show) script:

Select p1.strTitle,p2.strTitle,p3.strTitle, p1.iStartTime, p2.iStartTime,p3.iEndTime, p1.iEndTime
From tblPrograms p1
,    tblPrograms p2
,    tblPrograms p3
where p2.iStartTime >= p1.iStartTime
       and   p2.iEndTime   <  p1.iEndTime
       and   p2.idChannel  =  p1.idChannel
and   p3.iStartTime > p1.iStartTime
       and   p3.iEndTime   <= p1.iEndTime
       and   p3.idChannel  =  p1.idChannel

The second (delete) script

Delete tblPrograms p1
where exists (Select 0 from tblPrograms p2
       where p2.iStartTime >= p1.iStartTime
       and   p2.iEndTime   <  p1.iEndTime
       and   p2.idChannel  =  p1.idChannel)
and   exists (Select 0 from tblPrograms p3
       where p3.iStartTime >  p1.iStartTime
       and   p3.iEndTime   <= p1.iEndTime
       and   p3.idChannel  =  p1.idChannel)

This time I used the preview option!
:idea:

 

[Lyxalig]

Got it.. but it does not return ny data.

 

[Dirk]

The first script should show you the data.
If it shows nothing, then you don't have any overlapping programs.

In this case there is no need to run the second script wich removes the overlappers.

You need to run this second script each day.

If you are sure you have overlapping programs.
Check if you have opened the correct file 'TVDatabaseV21.db3'
Or look for error messages.