* contrib/upgrade-xxv-db.sql dump always database before update executed

* Bug #12986, upgrade failed if remote mysql server used
* CHANNELS: Merge twice SQL to single query
* RECORDS: Fix typo

5 files changed, 56 insertions, 61 deletions

diff --git a/Makefile b/Makefile
index 3aa1599..0424e4c 100644
--- a/Makefile
+++ b/Makefile
@@ -4,7 +4,7 @@
 # $Id$
 
 XXV = xxv
-VERSION = 1.0.1
+VERSION = 1.1
 
 ### The name of the distribution archive:
 
@@ -28,7 +28,7 @@ clean:
 
 tmpfolder:
 	@-rm -rf $(TMPDIR)/$(ARCHIVE)
-	@mkdir -p $(TMPDIR)/$(ARCHIVE)
+	@mkdir -p $(TMPDIR)/$(ARCHIVE)/contrib/
 
 copyfiles:
 	@for i in $(INCLUDE) ;\
@@ -55,7 +55,8 @@ updateversion:
 DBTABLES = $(shell cat ./contrib/update-xxv | grep tables= | cut -d '=' -f 2 | sed -e s/\'//g;)
 updatesql:
 	@echo Please type the DB-Password for root:
-	@mysqldump -p -n -d --add-drop-table --compatible=mysql40,no_table_options -p -u root xxv $(DBTABLES) -r ./contrib/upgrade-xxv-db.sql
+	@mysqldump --skip-opt -u root -p -n -d --compatible=mysql40,no_table_options xxv $(DBTABLES) -r $(TMPDIR)/$(ARCHIVE)/contrib/upgrade-xxv-db.sql
+	@sed -e "s/CREATE TABLE/CREATE TABLE IF NOT EXISTS/g" $(TMPDIR)/$(ARCHIVE)/contrib/upgrade-xxv-db.sql > ./contrib/upgrade-xxv-db.sql
 
 setpermission:
 	@find $(TMPDIR)/$(ARCHIVE) -type d -exec chmod 755 {} \;
diff --git a/contrib/update-xxv b/contrib/update-xxv
index 59fd18d..ea90e49 100755
--- a/contrib/update-xxv
+++ b/contrib/update-xxv
@@ -22,7 +22,7 @@ actualVersion=26
 
 # Specify tables to export before and import after update
 #
-tables='AUTOTIMER CHRONICLE MEDIALIB_ACTORS MEDIALIB_VIDEODATA MEDIALIB_VIDEOGENRE MOVETIMER USER'
+tables='AUTOTIMER CHRONICLE MEDIALIB_ACTORS MEDIALIB_VIDEODATA MEDIALIB_VIDEOGENRE MOVETIMER USER' 
 
 showTitle()
 {
@@ -34,7 +34,7 @@ showTitle()
 
 showHelp()
 {
-    echo 'Usage: update-xxv [-b] [-h hostname] [-P port] [-d database]'
+    echo 'Usage: update-xxv [-b] [-H hostname] [-P port] [-d database]'
     echo '                  [-u user] [-p password]'
     echo
     echo '-b <target>    : Make a backup first to <target>.gz'
@@ -71,7 +71,6 @@ showInfo()
 
 setDefaults()
 {
-    [ -z $isDoBackup ] && isDoBackup='false'
     [ -z $host ] && host='localhost'
     [ -z $database ] && database='xxv'
     [ -z $user ] && user='root'
@@ -114,7 +113,7 @@ checkServer()
 
 checkDatabase()
 {
-    showInfo 'Checking Database'
+    showInfo 'Checking database'
     if ! eval mysql $mysqlParams -f -B -e '"show databases;"' | \
       grep -e "^$database$" > /dev/null 2>&1 ; then
         showError "Can not access database '$database'"
@@ -125,24 +124,28 @@ checkDatabase()
 
 exportDb()
 {
-    showInfo 'Saving Data'
+    showInfo 'Export data'
     for table in $tables ; do
         savFile="$exportDir/$table.sav"
-        if ! eval mysql $mysqlParams -f -B $database -e \
-          "\"select * into outfile '$savFile' from $table;\"" ; then
+        if ! eval mysqldump $mysqlParams -n -t -c -r "$savFile" "$database" "$table" ; then
             showError "Can't save table '$table'"
+        else
+          if ! eval mysql $mysqlParams -f -B $database -e \
+            "\"DROP TABLE IF EXISTS $table;\"" ; then
+              showError "Can't drop table '$table'"
+              exit 1
+          fi
         fi
     done
 }
 
 importDb()
 {
-    showInfo 'Restoring Data'
+    showInfo 'Restoring data'
     for table in $tables ; do
         savFile="$exportDir/$table.sav"
         if [ -e "$savFile" ]; then
-          if ! eval mysql $mysqlParams -f -B $database -e \
-            "\"load data infile '$savFile' into table $table;\"" ; then
+          if ! eval mysql $mysqlParams -f -B $database < $savFile ; then
               showError "Can't load table '$table'"
               exit 1
           fi
@@ -153,10 +156,10 @@ importDb()
 
 updateDb()
 {
-    showInfo 'Updating Database'
+    showInfo 'Updating database'
     if ! eval mysql $mysqlParams -f -B $database < upgrade-xxv-db.sql ; then
         showError 'Update failed!' ;
-	exit 1
+    	  exit 1
     fi
 }
 
@@ -195,7 +198,7 @@ getVersion()
 
 updateVersion()
 {
-    showInfo 'Updateing Version'
+    showInfo 'Updateing version'
     for table in $tables ; do
         if ! eval mysql $mysqlParams -f -B $database -e \
           "\"ALTER TABLE $table COMMENT '$actualVersion';\"" ; then
@@ -286,21 +289,20 @@ if [ $actualVersion -lt $version ] ; then
     fi
 fi
 
+exportDir=/tmp/xxv-update.$$
+mkdir $exportDir
+chmod a+rwx $exportDir
 # backup
-if [ -n $backupTarget ] ; then
-    showInfo 'Performing backup...'
-    mysqldump $mysqlParams $database $tables | gzip >"$backupTarget.gz" 2>/dev/null
-    if [ $? -ne 0 ] ; then
-        showError 'Backup failed!!!'
-        exit 1
-    fi
+if [ -z $backupTarget ] ; then
+backupTarget=$exportDir/backup.sql
+fi
+showInfo "Performing backup to $backupTarget"
+if ! eval mysqldump $mysqlParams -r "$backupTarget" "$database" ; then
+    showError 'Backup failed!!!'
+    exit 1
 fi
-
 # now actually perform the update
 showInfo 'Starting update...'
-exportDir=/tmp/xxv-update.$$
-mkdir $exportDir
-chmod a+rwx $exportDir
 exportDb
 updateDb
 importDb
diff --git a/contrib/upgrade-xxv-db.sql b/contrib/upgrade-xxv-db.sql
index 785a5a5..35e209c 100644
--- a/contrib/upgrade-xxv-db.sql
+++ b/contrib/upgrade-xxv-db.sql
@@ -2,7 +2,7 @@
 --
 -- Host: localhost    Database: xxv
 -- ------------------------------------------------------
--- Server version	5.0.32-Debian_7etch1
+-- Server version	5.0.32-Debian_7etch3
 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
 /*!40103 SET TIME_ZONE='+00:00' */;
 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
@@ -14,8 +14,7 @@
 -- Table structure for table `AUTOTIMER`
 --
 
-DROP TABLE IF EXISTS `AUTOTIMER`;
-CREATE TABLE `AUTOTIMER` (
+CREATE TABLE IF NOT EXISTS `AUTOTIMER` (
   `Id` int(11) unsigned NOT NULL auto_increment,
   `Activ` enum('y','n') default 'y',
   `Done` set('timer','recording','chronicle') NOT NULL default 'timer',
@@ -42,8 +41,7 @@ CREATE TABLE `AUTOTIMER` (
 -- Table structure for table `CHRONICLE`
 --
 
-DROP TABLE IF EXISTS `CHRONICLE`;
-CREATE TABLE `CHRONICLE` (
+CREATE TABLE IF NOT EXISTS `CHRONICLE` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `hash` varchar(16) NOT NULL default '',
   `title` text NOT NULL,
@@ -58,8 +56,7 @@ CREATE TABLE `CHRONICLE` (
 -- Table structure for table `MEDIALIB_ACTORS`
 --
 
-DROP TABLE IF EXISTS `MEDIALIB_ACTORS`;
-CREATE TABLE `MEDIALIB_ACTORS` (
+CREATE TABLE IF NOT EXISTS `MEDIALIB_ACTORS` (
   `name` varchar(255) NOT NULL default '',
   `actorid` varchar(15) NOT NULL default '',
   `imgurl` varchar(255) NOT NULL default '',
@@ -71,8 +68,7 @@ CREATE TABLE `MEDIALIB_ACTORS` (
 -- Table structure for table `MEDIALIB_VIDEODATA`
 --
 
-DROP TABLE IF EXISTS `MEDIALIB_VIDEODATA`;
-CREATE TABLE `MEDIALIB_VIDEODATA` (
+CREATE TABLE IF NOT EXISTS `MEDIALIB_VIDEODATA` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `md5` varchar(32) default NULL,
   `title` varchar(255) default NULL,
@@ -119,8 +115,7 @@ CREATE TABLE `MEDIALIB_VIDEODATA` (
 -- Table structure for table `MEDIALIB_VIDEOGENRE`
 --
 
-DROP TABLE IF EXISTS `MEDIALIB_VIDEOGENRE`;
-CREATE TABLE `MEDIALIB_VIDEOGENRE` (
+CREATE TABLE IF NOT EXISTS `MEDIALIB_VIDEOGENRE` (
   `video_id` int(10) unsigned NOT NULL default '0',
   `genre_id` int(10) unsigned NOT NULL default '0',
   PRIMARY KEY  (`video_id`,`genre_id`)
@@ -130,8 +125,7 @@ CREATE TABLE `MEDIALIB_VIDEOGENRE` (
 -- Table structure for table `MOVETIMER`
 --
 
-DROP TABLE IF EXISTS `MOVETIMER`;
-CREATE TABLE `MOVETIMER` (
+CREATE TABLE IF NOT EXISTS `MOVETIMER` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `source` varchar(64) NOT NULL,
   `destination` varchar(64) NOT NULL,
@@ -145,8 +139,7 @@ CREATE TABLE `MOVETIMER` (
 -- Table structure for table `USER`
 --
 
-DROP TABLE IF EXISTS `USER`;
-CREATE TABLE `USER` (
+CREATE TABLE IF NOT EXISTS `USER` (
   `Id` int(11) unsigned NOT NULL auto_increment,
   `Name` varchar(100) NOT NULL default '',
   `Password` varchar(100) NOT NULL,
@@ -165,4 +158,4 @@ CREATE TABLE `USER` (
 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2007-12-28 10:05:06
+-- Dump completed on 2008-01-20 20:49:43
diff --git a/lib/XXV/MODULES/CHANNELS.pm b/lib/XXV/MODULES/CHANNELS.pm
index 438d46a..3713c24 100644
--- a/lib/XXV/MODULES/CHANNELS.pm
+++ b/lib/XXV/MODULES/CHANNELS.pm
@@ -603,18 +603,17 @@ sub getChannelType {
 # ------------------
     my $obj = shift || return error('No object defined!');
     my $id = shift || return undef;
-    my $pos = $obj->ChannelToPos($id);
-    if($pos and $pos >= 1)
-    {
-      my $data = $obj->ChannelHash('POS', sprintf('POS = %d', $pos));
-      if(exists $data->{$pos}) {
-        my $ch = $data->{$pos};
-        if($ch->{VPID}) {
+
+    my $sth = $obj->{dbh}->prepare('SELECT SQL_CACHE VPID,APID from CHANNELS where Id = ?');
+    $sth->execute($id)
+        or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
+    my $erg = $sth->fetchrow_hashref();
+    if($erg) {
+        if(exists $erg->{VPID}) {
           return 'TV';
-        } elsif($ch->{APID}) {
+        } elsif(exists $erg->{APID}) {
           return 'RADIO';
         }
-      }
     }
     error sprintf("Unknown channel! Couldn't identify type of channel with id: %s", $id);
     return 'UNKNOWN';
diff --git a/lib/XXV/MODULES/RECORDS.pm b/lib/XXV/MODULES/RECORDS.pm
index a4412ae..7b9f4d8 100644
--- a/lib/XXV/MODULES/RECORDS.pm
+++ b/lib/XXV/MODULES/RECORDS.pm
@@ -1663,8 +1663,8 @@ sub delete {
     my $answer  = shift || 0;
 
     my @rcs  = split(/_/, $record);
-    my @todelete;
-    my @md5delete;
+    my $todelete;
+    my $md5delete;
     my %rec;
         
     foreach my $item (@rcs) {
@@ -1711,8 +1711,8 @@ sub delete {
 
 
         $obj->{svdrp}->queue_cmds(sprintf("delr %s",$r->{Id}));
-        push(@todelete,$r->{Title}); # Remember title
-        push(@md5delete,$r->{MD5}); # Remember hash
+        push(@{$todelete},$r->{Title}); # Remember title
+        push(@{$md5delete},$r->{MD5}); # Remember hash
 
         # Delete recordings from request, if found in database
         my $i = 0;
@@ -1732,7 +1732,7 @@ sub delete {
 
     if($obj->{svdrp}->queue_cmds('COUNT')) {
 
-        my $msg = sprintf(gettext("Recording '%s' to delete"),join('\',\'',@todelete));
+        my $msg = sprintf(gettext("Recording '%s' to delete"),join('\',\'',@{$todelete}));
 
         my $erg = $obj->{svdrp}->queue_cmds("CALL"); # Aufrufen der Kommandos
 
@@ -1746,10 +1746,10 @@ sub delete {
           }else {
             con_msg($console,$msg);
           }
-
-          my $dsql = sprintf("DELETE FROM RECORDS WHERE RecordMD5 IN (%s)", join(',' => ('?') x @md5delete)); 
+#dumper($md5delete);
+          my $dsql = sprintf("DELETE FROM RECORDS WHERE RecordMD5 IN (%s)", join(',' => ('?') x @{$md5delete})); 
           my $dsth = $obj->{dbh}->prepare($dsql);
-            $sth->execute(@md5delete)
+            $sth->execute(@{$md5delete})
               or return con_err($console, sprintf("Couldn't execute query: %s.",$sth->errstr));
 
         }
@@ -1758,7 +1758,7 @@ sub delete {
           unless($obj->{inotify});
 
         if(ref $console && $console->typ eq 'HTML') {
-          my @t = split('~', $todelete[0]);
+          my @t = split('~', $todelete->[0]);
           if(scalar @t > 1) { # Remove subtitle
             delete $t[-1];
             $console->redirect({url => sprintf('?cmd=rlist&data=%s',url(join('~',@t))), wait => 1});