Personal ReplayGuide

Combination of XMLTV, Perl scripts, SQL Server (or MySQL), ReplaySchedule.exe and web pages to create list of shows, recordings, and allow scheduling of recordings on Replay 5000-series.

Trying to get all this working on my Windows XP box, running SQL Server 2000. Since based on Perl, had some things to learn when installing.

First some links:

• Personal ReplayGuide - the web/perl based software that shows tv schedule, combined with currently scheduled recordings, and even lets you set new recordings on the replay box in real time (don't have to wait until myreplaytv.com does the 24-hour update)
  
• Perl for Windows (ActivePerl) - need this to work with the ReplayGuide perl scripts. Hooks into IIS web server very easily.
  
• ReplaySchedule - extracts currently scheduled programming info from the replay box.
  
• XMLTV - SourceForge site, or the project home page
  

NOTE: See Perl Monks for good starting tutorial information on Perl.

Installation notes

See Install.txt with ReplayGuide for info on installation steps. Using SQL Server, so followed those steps.
Had some trouble with XMLTV, needed latest version since apparently zap2it has changed their xml feed. Using XMLTV 0.5.15 from SourceForge.net, along with v115.19 of replaySchedule.exe (see below for links)

xmltv tv_grab_na --configure
xmltv tv_grab_na --days 7 > na.xml (update number of days - grabbing 7 here)

perl choked on the DBI stuff. poked around on the web and found to install the other modules you have to do this through Perl "PPM interactive shell":

ppm

PPM> install DBI

This goes out to web somewhere and downloads the DBI modules and installs them. Use query command in PPM to see what modules you have.

Ran install DBD::ODBC as well to setup the module for ODBC / SQL Server.

Some things that had to change to get things working:

• Get latest XMLTV (v 05.15), since apparently zap2it.com has changed data format, rendering older versions invalid -- barf.
  
• With this, get latest ReplaySchedule.exe (v 115.19, 7/16/2003 ) -- works with 05.15 version of XMLTV.
  
• DB connection related code in perl scripts - to work with SQL Server 2000, had to change format of db connection string -- previous: DSN => "DBI:$db_driver:host=$db_host;database=$db_name", changed to: DSN => "DBI:$db_driver:$db_name" (this is located in 5 or so perl files -- search for it)
  
• Changed DB table "schedule". After getting overflow error in schedule2sql.pl, found that the schedule table had "created" column that was int4, however the SQL Stmt insert was sending 'character' data. Changed col type to varchar(50) for now.
  

Some info from author and others at avsforum
in this LONG thread (look for responses to my volleynerd post on 7/23)

• '0030721210000' was causing "overflow" error from one of the original DB insert calls. Answer: shouldn't be a problem, since it should be converted to epoch seconds before inserting into the db. (although this doesn't answer why it doesn't work as is)
  
• turns out you can set $created=0 before the insert since this value is currently not used anywhere
  
• call to system("$getschedule $replayaddress"); from rg_scheduler.pl was not working. print statements in that module weren't coming out, so knew it wasn't getting called. suggestions were to change {scriptloc} variable in rg_info.pl to "", "./", or ".\\" -- none of these helped. Finally got this working by adding a "perl " in the front of the module name like this: $getschedule = "perl schedule2sql.pl";
  
• tidbits from author: builds after 180 call external executable (another pl script) to build the list of scheduled shows and do conflict checking, builds 180 and earlier do not do this.
  
• alternative to adding "perl " in front of cmd is to add ".pl" to the PATHEXT environment var. (didn't try this, just left it as "perl xxxx" for now)
  
• getting things to work without the "perl " added to SYSTEM command -- explorer, tools, folder options, file types, ensure that .pl is associated with "<perlDir>\perl.exe %1 %* (see this cmd line stuff in "advanced" section )
  
• permissions are tricky - basically IIS process needs write permissions to the cgi-bin directory and to the logfile directory (if applicable). replaySchedule.exe needs the current diretory to be writable so it can write the .asc and .bin output files. (also be careful about allowing overwrite of the .asc and .bin files by the IIS process if you have run replaySchedule.exe by hand)
  
• couldn't get permissions exactly correct - still was getting 255 return code from open( LISTINGS, "replaySchedule.exe ....") -- ended up giving write privs to EVERYONE on the main dir where replaySchedule.exe sits for now and things worked. What userID is running the perl stuff? How to tell?
  
• why SYSTEM call to schedule2sql.pl? originally written to be a nightly job. now should probably be integrated like other .pl modules, but then have to worry about global conflicts, etc.
  
• re: writeLogFile not working in schedule2sql.pl? it uses xmltv2sql.conf for debug settings, so set $debug and $logfile in there
  
• Unexpected memcmp(rs->reserved5_2, lots_of_zeros, sizeof rs->reserved5_2)== 0 guide.c:326 error shows up from call to ReplaySchedule.exe if you have Received shows in your list. This is just a warning, and doesn't affect the results. Apparently the contents of the show structure for Received shows is a bit different, using a field that otherwise is all zeros.
  

Embedded Database Calls
Final fix to get things to work was to handle the embedded db calls. See my post for details on the problem. Basically, there is an outer loop that walks the tvlistings for the given time frame. Then for each show, a function is called to get the program details, which makes another SQL stmt.

Apparently, the combination of some db drivers with some perl db packages don't allow for multiple SQL stmts to be open at once. The fix:

Added a SECOND dsn in the StartDSN function, and close it down in the EndDSN. Use this DSN name ($DSNLink2) in the inner loop when getting show details.

See the thread quoted above for more info - the author and others say this 2nd DSN may not be necessary -- either use tweaked scripts (newer than the 187 version I have), or updated db drivers and perl db packages.

SWEET!! I finally see the tv listings grid --- and it shows any recordings currently scheduled.

Scheduling Recordings
Ugh...now it's on to the recordings page - getting "slot not available" on all requests.
Permissions problem? Well even pasting the big URL into the interactive browser returns same answer - 0 slots.

Wahoo - got recordings working
Well, first off, I didn't realize that you needed to enter a show title in the proper field. The Replay box ends up doing a "search" on this information (including the time, etc) for a matching show (aka "slot"). If you just put garbage in the title field, you get "no slots available".

Only problem now was my channel lineup of 2-555 and then the 1000-series of the straight cable channels. At first, the second set of channels was not showing up for basic searches like "Today" show.

Found problem in the schedule.pl file, ~line 420 (added some of my own debug lines, so line #s are prob off a bit). do...while loop for me needs to be from 1 to and including n_slots

do...while ( c_slot <= n_slots )

Sweet! Now I get both 7 and 1007 channels listed as matching the query, and they both work for setting the channel.

Next task: how to cancel a recording.

Before that, need to get 1000-series channels correctly displaying in TO-DO list
Some background: My replay unit has two sets of channels from the two inputs.
2-555 are coming from the digital cable box.
1002-1115 are coming straight from the coax from the wall.
(This setup allows me to record most channels [non digital/scrambled] off the second set (1000-series) and then watch tv through the digital cable box at the same time.

The issue: even when a recording is scheduled on a 1000+ channel, it would always show up in the PRG as in the 2-555 channels.

The goal: give an indication that a given recording is coming from what actual channel, the 2-555 or the 1000+ channels.

Grabbed ReplaySchedule.exe source code, and checked it out. How things work:
ReplaySchedule gets the data from the Replay unit, then looks for matches in the channels and descriptions from the XMLTV output xml file. The data from the Replay box has the "internal" channel numbers (including 1000+), but this info is lost when taking most of the "matching data" from the XMLTV output file. This data is then stored in the SQL DB, then used to display the channel, etc. for the recording. So...even when the recording is for say channel 1008, the PRG recording listing shows "KFMB 8".

With minor code changes, I was able to grab the "internal" channel number from the replay guide data, and output it in the ReplaySchedule output. (See ReplaySchedule.c, look for //dhdh comments). Basically added a member to the "channel" structure to store the internal channel, then propogate that int through the copies, and outputs.

DB change: added "internalChannel" to schedule table. int 4, allow NULLS.

Change to schedule2sql.pl (look for "dhdh"). In open(<LISTINGS>), use regex just like other lines to pull out the internalChannel attribute from ReplaySchedule.exe xml output. In PostSQL, add the internalChannel column to the insert stmt.

Note

Posted 10:48 PM    |    0 comments    |    Permalink