Personal ReplayGuide (C) 2003 by Lee Thompson Includes contributions by Philip Van Baren, Kanji T. Bates, Kevin J. Moye and Rick Quartarone Thanks to Todd Larason and Matthew Linehan Personal ReplayGuide is a package of Perl scripts and utilities to provide a local, personal, web-based tvlistings service directly integrated with ReplayTVs running the 5.0 software. (4.x versions of the software cannot remotely schedule programming.) If you just want to use this for a personal tvlistings application, the ReplayTV support is actually optional. NOTE: All cross platform files are LF only and thus may not format properly in notepad. http://www.winvi.de is a text editor for Win32 that can handle both formats and convert between them. ________________ PROJECT HOMEPAGE http://replayguide.sourceforge.net http://sourceforge.net/projects/replayguide/ _____ LEGAL This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA _________ IMPORTANT This document as well as the installation and configuration of this software is not for novice users. While I'm happy to help if you should run into a bug or even have a feature request or two I cannot help you install and configure the software beyond this document. For additional assistance you might try the appropriate forums at http://www.avsforum.com or http://www.planetreplay.com Please do not email me with support questions regarding Personal ReplayGuide (bug reports and feature requests are ok, however). ____________ REQUIREMENTS Personal ReplayGuide is dependant on four other software packages: 1. Perl 2. A supported database server (such as MySQL or Microsoft SQL; see Databases for more info) 3. A web server 4. XMLTV* These must all be installed and properly configured before Personal ReplayGuide can be installed and configured. In general the best install order is: Web Server, Perl, Database, XMLTV. *XMLTV is currently the only supported method of importing listings into the database. Other sources of TV listing data may be adapted to this program using xmltv2sql as a guide. _________ DATABASES I am currently only supporting MySQL and Microsoft SQL databases in terms of testing, making SQL statements and providing .sql scripts to create the tables. Other DBMS packages will probably work but may require manual creation of tables and/or modifications to the SQL statements. Consult Perl documentation for more information on DBI and DBD (and what drivers are available). Tips on Modifications: 1. All SQL statements are always set up in a $Stmt variable before being passed. 2. The DBI/DBD database driver is always available in the '$db_driver' variable, this allows for conditional $Stmt building. For example, in xmltv2sql we need to do a conditional Stmt build because of a difference between MSSQL and MySQL's SELECT with a limited number of records syntax. if ($db_driver eq "mysql") { $Stmt = "SELECT * FROM tvlistings WHERE (programid <> '$programid' AND starttime > '$starttime') AND tuning = $tuning ORDER BY tuning, starttime LIMIT 1;"; } if ($db_driver eq "ODBC") { $Stmt = "SELECT TOP 1 * FROM tvlistings WHERE (programid <> '$programid' AND starttime > '$starttime') AND tuning = $tuning ORDER BY tuning, starttime;"; } ____________________ REPLAYTV INTEGRATION If you happen to have a ReplayTV you can define them to be available to Personal ReplayGuide. Versions running 5.0 of the ReplayTV OS can remotely schedule programming, prior versions will only show what is scheduled in the Personal ReplayGuide channel grid. ________________________________ WHERE TO FIND DEPENDANT SOFTWARE Perl can be found at http://www.perl.com or http://www.activeperl.com. Personal ReplayGuide was designed and tested with ActivePerl 5.6.1 Build 635. MySQL is a database server that can be found at http://www.mysql.com. It is free for personal use. Personal ReplayGuide was designed and tested with MySQL 3.22. XMLTV can be found at http://membled.com/work/apps/xmltv/. It has been ported to most operating systems including Win32. XMLTV v6 will be changing the file format, this will break Personal ReplayGuide which is developed with XMLTV v0.5.10. Personal ReplayGuide was tested and designed on an IIS 5.0 web server. It should also work just fine with Apache and others. _____________ FILE MANIFEST LICENSE.TXT Gnu Public License (GPL) INSTALL.TXT This Document FUTURE.TXT Possible Future Enhancements README.TXT Project Description replayguide.conf.dist Sample Personal ReplayGuide Configuration File schedule.conf.dist Sample ReplayTV Recording Scheduler Configuration File xmltv2sql.conf.dist Sample XMLTV to SQL Converter Configuration File rg_common.pl Common Function Library for Personal ReplayGuide rg_database.pl Database Function Library for Personal ReplayGuide rg_guide.pl ReplayTV Function Library for Personal ReplayGuide replayguide.pl Script for Personal ReplayGuide schedule.pl Script for ReplayTV Recording Scheduler xmltv2sql.pl Script for Converting XMLTV to SQL configure.pl Script for Configurating ReplayTVs for Personal ReplayGuide geticons.bat Batch File for Downloading ReplayTV Icons using WGET update.bat Batch File to Run XMLTV and XMLTV2SQL configure.bat Batch File to Run Configure.pl createdb.sql SQL Script for Creating the TVLISTINGS Database tvlistings.sql SQL Script for Creating the TVLISTINGS Database Tables tvlistings_mssql.sql SQL Script for Creating the TVLISTINGS Database Tables for Microsoft SQL s_n_r.pl Main part of xmlfix utility exceptions.dat.dist Sample exceptions.dat for xmlfix utility _____________________ REQUIRED PERL MODULES POSIX* CGI* Time::Local* LWP* DBI DBD::mysql (Required if using MySQL) DBD::ODBC (Required if using any other DB engine other than MySQL if using ODBC) DBD::?? (Not supported officially, good luck ;) - see "Databases") * denotes standard with ActiveState ActivePerl. Consult your Perl distribution's documentation for how to install modules. NOTE: Other modules may be required for XMLTV (on non-win32 platforms). NOTE: There is no harm in having both DBD::MySQL and DBD::ODBC installed. WIN32: Change "::" to "-" when trying to install modules with ActivePerl. TIP: Some of the beta testers for this program have said that you're better off using stock Perl 5.6.1 instead of ActivePerl on Linux. ____________ INSTALLATION NOTE: If transferring to a Unix based system, you will want to FTP the files in ASCII mode, not BINARY. This will strip the extra CRLF pairs. Step 1: Copying Files 1. The .pl files will all need to be copied into a directory where appropriate permissions have been set to allow the execution of scripts. The files "rg_common.pl", "rg_database.pl", "rg_info.pl" and "rg_guide.pl" can be located anywhere in Perl's @INC path. If you don't know what I'm talking about, leave them with the others. 2. Copy the .conf.dist files to the same directory as step 1. 3. Next, copy or rename "replayguide.conf.dist" to "replayguide.conf" and "schedule.conf.dist" to "schedule.conf". The .conf files need to be in the same directory as their counterparts. (i.e. replayguide.conf must be in the same directory as replayguide.pl). Step 2A: MySQL Setup Next, we need to set up the MySQL database and tables. 1. Login to your MySQL server (consult MySQL documentation) 2. Create the database. Type "source /createdb.sql". Substitute path for where your Personal ReplayGuide files are located. (eg. "source c:\install\replayguide\createdb.sql") 3. Create the tables. Type "source /tvlistings.sql". Again, substitute path for where your Personal ReplayGuide files are located. 4. Edit "rg_info.pl" and change the db_user and db_pass values to match your MySQL installation. example: $db_driver = "mysql"; $db_user = "myuser"; # MySQL User $db_pass = "mypass"; # MySQL Password Step 2B: Microsoft SQL Setup Next, we need to set up the Microsoft SQL database and tables. 1. Connect to the database with the Microsoft SQL Query Analyzer 2. run 'createdb.sql' (or create a database called 'tvlistings' manually) 3. run 'tvlistings_mssql.sql' 4. Create an ODBC System DSN called 'tvlistings' 5. Edit "rg_info.pl" and change the db_user and db_pass values to match your MS SQL installation. Change "db_driver" to "ODBC" (all caps). example: $db_driver = "ODBC"; $db_user = "myuser"; # MSSQL User $db_pass = "mypass"; # MSSQL Password NOTE: Other ODBC based databases may work too but the scripts may require some editing. Step 3: Load Data You will want to run XMLTV and XMLTV2SQL, the easiest way is to run the update.bat file. xmltv tv_grab_na --days 12 > na.xml perl xmltv2sql.pl This process will likely take between 1-2 hours (yes, hours) depending on the speed of your internet connection and computer. If you already have a complete set of listings you can skip running xmltv again. IMPORTANT: Personal ReplayGuide needs a minimum of 2 days of data to function properly. NOTE: You might want to start with only 2 days of data, to do this invoke xmltv with --days 2 instead of --days 12. NOTE: Each time you run xmltvsql it will delete all content of the channel and tvlistings tables. NOTE: You will need to edit xmltv2sql.conf to configure the filename for the .xml file if it's not "na.xml". If you have multiple headends (i.e. antenna + directv etc), you can specify multiple .xml files in the xmltv2sql.conf - separate them with a comma (eg. antenna.xml,directv.xml) WARNING: In various tests on Windows, MySQL consumed a lot of CPU when processing multiple headends. Step 4: Configure Personal ReplayGuide First we need to set up Personal ReplayGuide to integrate with your ReplayTVs. NOTE: Should you add (or remove) ReplayTVs in the future, you will need to rerun this script. 1. From a command window, run the configure.bat file (or invoke the configure.pl script yourself). Next we need to prepare the .conf files. 3. You will need to edit the .conf files. At the very least, the scripts directory needs to be the virtual directory where your web server is configured to run scripts from. NOTE: If you don't have a ReplayTV, you can skip this step. Step 5: Optional but Recommended 1. You will, at some point, probably want to set up a scheduled event to run XMLTV and XMLTV2SQL at an interval of your choosing. 2. It might be wise to limit those that can access Personal ReplayGuide. Even if you've locked down who can schedule a program on your ReplayTVs, you might not want others to see what you have scheduled. 3. On Win32 with IIS you may get better performance if you rename "schedule.pl" and "replayguide.pl" to "schedule.plx" and "replayguide.plx". The .conf files will need to be edited reflecting this change. 4. If your web server is open to the internet "at large" be sure to secure it properly and keep up on patches. You are now ready to run Personal ReplayGuide! ____________________________ RUNNING PERSONAL REPLAYGUIDE Simply load up your favorite web browser and type in: http://YOUR_WEB_SERVER_ADDRESS/YOUR_CGI_SCRIPT_DIR/replayguide.pl eg. http://192.168.0.1/cgi/replayguide.pl or http://myserver/cgi/replayguide.pl _______________ TROUBLESHOOTING ALL PLATFORMS 1. Try to run "replayguide.pl" directly from a command line prompt, see if there are any errors. If it appears to have valid data and is complete, the problem is more likely with the web server settings. 2. Check Personal ReplayGuide's configuration files. replayguide.conf, schedule.conf, xmltv2sql.conf and rg_info.pl. Ensure that rg_info.pl is using the proper driver (mySQL should use "mysql", everything else should use "ODBC") and that the login credentials are right. 3. If it freezes at the header in both the web browser and when you run it from the command line chances are it is having trouble contacting the database server. 4. Enable and check the log file. 5. Enable debug mode. WINDOWS 1. Make sure the virtual directory you created for replayguide's scripts has read and execute permissions. 2. If you installed ActivePerl before IIS, make sure .pl is mapped properly to the Perl executeable (check ActivePerl documentation for specifics). 3. If you're using MS SQL, make sure that you have a system DSN called "tvlistings" that is pointing to the SQL server (does not have to be on the same box) and that it automatically changes the active database to "tvlistings". 4. If you change to the ISAPI filter (.plx) be sure to update the .conf to reflect the proper filenames. I've noticed that DLLHOST leaks memory with ActiveState's Perl. Use caution. UNIX 1. This was developed on a Windows platform so the various files may have CRLF patterns. You'll need to use a utility to strip the CR characters. 2. You will probably need to adjust the #! line at the top of the scripts to point to your Perl interpreter. 3. Some Perl modules may not install directly from CPAN, check your ports collection if you experience a failed install. _____________ LISTINGS NOTE Sometimes shows differ slightly in name on zap2it's listings and on the Replay. If this occurs you will not be able to schedule it by just clicking on the title. For example, zap2it lists Lois & Clark as "Lois & Clark: The New Adventures of Superman" but on the ReplayTV channel guide, it's listed as "Lois & Clark: New Adventures of Superman". XMLFIX Rick Quartarone has written a pre-processor (in the xmlfix directory) that can correct these automatically. Specifically, the s_n_r.pl and find.pl scripts can be used to update the .xml files before being injected into the database. There is an exceptions.dat file in the following format: TV Listing|Replay Listing|Path to XML File Example: Lois & Clark: The New Adventures of Superman|Lois & Clark: New Adventures of Superman|c:/xmltv/na.xml The update batch job would run something like this: xmltv tv_grab_na --days 14 >na.xml perl s_n_r.pl perl xmltv2sql.pl NOTE: The s_n_r.pl script looks for the exceptions.dat in the directory where it is run from, if you wish to have the exception file in another location you will need to edit the s_n_r.pl script. Simply change the $cnf_excpt variable on line 9 to reflect the proper path. Example: $cnf_excpt = "c:/myfiles/exceptions.dat"; This utility has a dependency on "File::Find" (not included). find.pl comes with ActiveState's Perl and is located in the Perl library directory. If you're on a unix system you may need to make your own find.pl: use File::Find (); *name = *File::Find::name; *prune = *File::Find::prune; *dir = *File::Find::dir; *topdir = *File::Find::topdir; *topdev = *File::Find::topdev; *topino = *File::Find::topino; *topmode = *File::Find::topmode; *topnlink = *File::Find::topnlink; sub find { &File::Find::find(\&wanted, @_); } 1; XMLTV2SQL ADVANCED OPTIONS In addition to the utility in 'xmlfix', if you wish to leave the .xml file intact but remap in the database there are two optional mapping files that can be used in conjunction with xmltv2sql. To enable them, check Advanced Options in xmltv2sql.conf _____________ titlemap.conf This is similar in format to exceptions.dat but does not include the filename for the .xml and uses = instead of |. It is OLD TITLE=NEW TITLE, one per line. Example: To change "Lois & Clark: The New Adventures of Superman" to "Lois & Clark: New Adventures of Superman" you would add the following to your titlemap file: Lois & Clark: The New Adventures of Superman=Lois & Clark: New Adventures of Superman _______________ channelmap.conf This allows for a channel number to be remapped and/or the call letters to be altered. Format is OLD_CHANNELID,OLD_TUNING=NEW_CHANNELID,NEW_TUNING Example: To remap "57 TNN" to "69 TNNP" you would add the following to your channelmap file: TNN,57=TNNP,69 ________ APPENDIX _____ ICONS Not included within this package are icons to be used within the tvlistings of Personal ReplayGuide. I have not included these since that would be a violation of copyright. By default, Personal ReplayGuide simply links to the images, but you may find better performance if you use them locally. The batch file "geticons.bat" does the following (you'll need WGET installed): wget http://my.replaytv.com/images/x_+00-.gif wget http://my.replaytv.com/images/x_-00+.gif wget http://my.replaytv.com/images/x_+00+.gif wget http://my.replaytv.com/images/x_-00-.gif wget http://my.replaytv.com/images/x_-oo-.gif wget http://my.replaytv.com/images/x_+oo-.gif wget http://my.replaytv.com/images/x_-oo+.gif wget http://my.replaytv.com/images/x_+oo+.gif wget http://my.replaytv.com/images/x_-0-.gif wget http://my.replaytv.com/images/x_-o-.gif wget http://my.replaytv.com/images/x_+0-.gif wget http://my.replaytv.com/images/x_-0+.gif wget http://my.replaytv.com/images/x_+0+.gif wget http://my.replaytv.com/images/x_+o-.gif wget http://my.replaytv.com/images/x_-o+.gif wget http://my.replaytv.com/images/x_+o+.gif You can create your own, download them manually or edit the replayguide.conf file to use the full url for each icon instead of trying to use them locally. You are only entitled to use these icons if you are a registered MyReplayTV.com user. The legend of the symbols are: 00 Guaranteed, Recurring oo Not Guaranteed, Recurring 0 Guaranteed o Not Guaranteed + Padding (if before the 0 or o it's before padding) - No Padding Examples: -00+ Guaranteed, Recurring, After Padding +o+ Not Guaranteed with Padding Both Before and After If you elect to use local icons, you will need to edit replayguide.conf: image_bpgr=http://my.replaytv.com/images/x_+00-.gif ... to image_bpgr=x_+00-.gif image_apgr=x_-00+.gif image_ppgr=x_+00+.gif image_gr=x_-00-.gif image_r=x_-oo-.gif image_bpr=x_+oo-.gif image_apr=x_-oo+.gif image_ppr=x_+oo+.gif image_gs=x_-0-.gif image_s=x_-o-.gif image_bpgs=x_+0-.gif image_apgs=x_-0+.gif image_ppgs=x_+0+.gif image_bps=x_+o-.gif image_aps=x_-o+.gif image_pps=x_+o+.gif You'll want to make sure that the 'imagedir' option points to the virtual directory where the images can be found. imagedir=/rtvimages Other icons are supported as well: image_stereo is the image to use for stereo programming. image_repeat is the image to use for repeats. image_cc is the image to use for closed captioning. image_tvg is the image to use for TV-G rated programs. image_tvpg is the image to use for TV-PG rated programs. image_tv14 is the image to use for TV-14 rated programs. image_tvma is the image to use for TV-MA rated programs. image_tvy is the image to use for TV-Y rated programs. image_tvy7 is the image to use for TV-Y7 rated programs. image_mpaag is the image to use for MPAA G rated programs. image_mpaapg is the image to use for MPAA PG rated programs. image_mpaapg13 is the image to use for MPAA PG13 rated programs. image_mpaar is the image to use for MPAA R rated programs. image_mpaanc17 is the image to use for MPAA NC17 rated programs. image_mpaanr is the image to use for movies that aren't rated. image_tl is the image to use for a theme that lost conflict rules. image_tw is the image to use for a theme that won conflict rules. _______________ DATABASE SCHEMA This schema is provided for database administrators and to provide enough information so that you can manually create tables if need be (or using an "unsupported" DBMS). NOTE: Some databases use a "tinyint(1)" for bits. Basically only a '0' or a '1' value is stored. NOTE: (uniqueid) just needs to be unique id of some kind. This can be an int(10), a rowguid... it will vary between databases. table: replayunits replayid int ID Number replayname char 16 Replay Name replayaddress char 65 fqdn or IP defaultquality int default quality (0 high to 2 standard) defaultkeep int default number of eps to keep lastsnapshot int last time a guide snapshot request was made Key field is "replayid" which in turn just needs to be an auto_incrementing identity field. table: tvlistings programid (uniqueid) unique number for fast lookups starttime datetime start time of the program endtime datetime end time of the program tuning int channel # channel char 10 call letters title char 255 program title subtitle char 255 episode title description text description text category char 255 categories (comma delimited if more than one) captions char 32 closed captioning system 'teletext' etc episodenum char 16 episode x of y vchiprating char 16 TV-?? rating mpaarating char 16 MPAA rating starrating char 16 eg. 2.5/4 stars movieyear char 16 movie release year repeat bit 1 = yes stereo bit 1 = yes Key field is "programid" which needs to be a unique identifier for each record. table: channels channelid (uniqueid) channelid tuning int channel # channel char 16 call letters display char 64 display name iconsrc char 255 url to the icon hidden bit is hidden Key field is "channelid" which needs to be a unique identifier for each record. _______________________ DEVELOPMENT ENVIRONMENT This describes the environment where Personal ReplayGuide was developed. Windows 2000 IIS 5.0 ActivePerl 5.6.1 Build 635 Microsoft SQL Server 2000 MySQL 3.22 WindowsXP Professional IIS 5.1 ActivePerl 5.6.1 Build 635 Microsoft SQL Server 2000 via System DSN to separate machine _______________ TIPS AND TRICKS Forcing a ReplayTV Guide Refresh You can force a ReplayTV guide refresh by adding "UPDATE=rtvaddress" to the query string. example 1: http://192.168.0.1/scripts/replayguide.pl?UPDATE=192.168.0.100 example 2: http://192.168.0.1/scripts/replayguide.pl?STATEDATE=20030608&UPDATE=192.168.0.100