mysql -umythtv mythconverg -B --exec "INSERT INTO channel SET chanid = 2004, channum ='Year' , callsign = 2004 , visible =0;"

ln -s /myth/video /myth/tv/video

mysql -umythtv mythconverg -B --exec "SELECT * FROM `videocategory`;"

#!/usr/bin/perl
#
# Mythtv

# Adds video's to recording table
# No error control !

# by chris <mailme@chrisjoyce.id.au>
#
# To use: ./addvideos.pl
#


use strict;
use warnings;

use DBI;
use Time::Local;
use Getopt::Long;

my $path = "video";
my $embed;
my $dsn = 'dbi:mysql:mythconverg';
my $user = 'mythtv';
my $pass;
my $mySQL = "";
my $hostname ="mythtv";
my $i;
my $test_mode =0;
my $inumber = 0 ;

# get database password
unless (defined $pass) {
  $pass = `grep DBPassword /usr/share/mythtv/mysql.txt | cut -f2 -d'='`;
  chomp($pass);
}

my $dbh = DBI->connect($dsn, $user, $pass) || die "$DBI::errstr";
my $host_query = "SELECT SUM(CHAR_LENGTH(data)+1) as LenData FROM settings ";
my $host_query = "$host_query where value like 'VideoStartupDir' and hostname like '$hostname';";
my $sth;
my $LenData;
$sth = $dbh->prepare( $host_query );
$sth->execute || die "Could not execute ($host_query)";
if (my @row = $sth->fetchrow_array) {
        $LenData = $row[0];
}

$mySQL = "SELECT  videometadata.year as chanid ,QUOTE(CONCAT('Video ',videocategory.category)) as title, ";
$mySQL = "$mySQL  QUOTE(videometadata.title) as subtitle , ";
$mySQL = "$mySQL QUOTE(videometadata.plot) as description, videometadata.length as length, ";
$mySQL = "$mySQL QUOTE(CONCAT('$path',SUBSTRING(videometadata.filename FROM $LenData))) as filename , ";
$mySQL = "$mySQL videometadata.userrating as stars ";
$mySQL = "$mySQL FROM videometadata, videocategory,channel WHERE ";
 $mySQL = "$mySQL videometadata.category = videocategory.intid ";
$mySQL = "$mySQL AND videometadata.year = channel.chanid ORDER by title , subtitle;";

$sth = $dbh->prepare( $mySQL );
$sth->execute || die "Unable to execute query: $dbh->errstr\n";

my $isth;
while(my $row = $sth->fetchrow_arrayref()) {
   my ($chanid, $title, $subtitle, $description, $length, $filename, $stars) =@$row;

## add records to db
$i = "INSERT INTO recorded SET chanid ='$chanid' , starttime= DATE_ADD('$chanid-01-01 01:00:00', INTERVAL '$inumber' HOUR),  ";
$i = "$i endtime= DATE_ADD('$chanid-01-01 01:00:00', INTERVAL '$inumber:$length' HOUR_MINUTE), title=$title, ";
$i = "$i subtitle=$subtitle,description= $description, category='video',hostname='$hostname', ";
$i = "$i autoexpire= 0, basename = $filename,stars='$stars'; ";
         print " $i \n\n";

        if ($test_mode=="0") {
                $isth = $dbh->prepare($i);
                $isth->execute ||  die "Could not execute ($i)\n";
        } else {
                print("Test mode: insert would have been done\n");
                print("  Query: '$i'\n");
        }
  $inumber++ ;
  next unless -f $filename;

}

mysql -umythtv mythconverg -B --exec "DELETE FROM category WHERE title like 'Video %';"