* EPG/TIMERS/RECORDS : SQL-Query improve

3 files changed, 187 insertions, 100 deletions

diff --git a/lib/XXV/MODULES/EPG.pm b/lib/XXV/MODULES/EPG.pm
index a5adc78..8b2fe39 100644
--- a/lib/XXV/MODULES/EPG.pm
+++ b/lib/XXV/MODULES/EPG.pm
@@ -592,8 +592,6 @@ sub search {
     my $data = shift;
     my $params = shift;
 
-    my $tim = main::getModule('TIMERS');
-
     # Textsearch
     my $search;
     if($data) {
@@ -645,7 +643,22 @@ sub search {
         DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(e.starttime) + e.duration), '%H:%i') as Stop,
         UNIX_TIMESTAMP(e.starttime) as Day,
         e.description,
-        IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC
+        IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC,
+        ( SELECT 
+            t.id
+            FROM TIMERS as t
+            WHERE t.eventid = e.eventid
+            LIMIT 1) as __timerid,
+        ( SELECT 
+            (t.flags & 1) 
+            FROM TIMERS as t
+            WHERE t.eventid = e.eventid
+            LIMIT 1) as __timeractiv,
+        ( SELECT 
+            NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+            FROM TIMERS as t
+            WHERE t.eventid = e.eventid
+            LIMIT 1) as __running
     from
         EPG as e,
         CHANNELS as c
@@ -664,11 +677,7 @@ sub search {
 
         unshift(@$erg, $fields);
     }
-    $console->table($erg, {
-                            timers => $tim->getEvents,
-                            runningTimer => $tim->getRunningTimer('eventid'),
-                          }
-                    );
+    $console->table($erg);
 }
 
 # ------------------
@@ -680,7 +689,6 @@ sub program {
     my $channel = shift || $obj->{dbh}->selectrow_arrayref("SELECT SQL_CACHE  POS from CHANNELS limit 1")->[0];
 
     my $mod = main::getModule('CHANNELS');
-    my $tim = main::getModule('TIMERS');
 
     my $cid;
     if($channel =~ /^\d+$/sig) {
@@ -702,7 +710,22 @@ SELECT SQL_CACHE
     e.description as __Description,
     e.video as __Video,
     e.audio as __Audio,
-    IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC
+    IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC,
+    ( SELECT 
+        t.id
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timerid,
+    ( SELECT 
+        (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timeractiv,
+    ( SELECT 
+        NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __running
 from
     EPG as e, CHANNELS as c
 where
@@ -722,9 +745,7 @@ order by
     $console->table($erg, {
                             channels => $mod->ChannelArray('Name'),
                             current => $mod->ChannelToPos($cid),
-                            timers => $tim->getEvents,
-                            runningTimer => $tim->getRunningTimer('eventid'),
-                           }
+                          }
                     );
 }
 
@@ -773,10 +794,25 @@ SELECT SQL_CACHE
     $stopp as \'$f{'Stop'}\',
     c.Name as \'$f{'Channel'}\',
     e.description as \'$f{'Description'}\',
-    e.image as __Image,
-    (unix_timestamp(e.starttime) + e.duration - unix_timestamp())/duration*100 as \'$f{'Percent'}\',
     e.video as __Video,
     e.audio as __Audio,
+    (unix_timestamp(e.starttime) + e.duration - unix_timestamp())/duration*100 as \'$f{'Percent'}\',
+    ( SELECT 
+        t.id
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timerid,
+    ( SELECT 
+        (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timeractiv,
+    ( SELECT 
+        NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __running,
+    e.image as __Image,
     IF(e.vpstime!=0,$vps,'') as __PDC,
     e.channel_id as __channel_id
 from
@@ -802,8 +838,7 @@ where
 
     unshift(@$erg, $fields);
 
-    my $tim = main::getModule('TIMERS');
-    $console->table($erg,{timers => $tim->getEvents});
+    $console->table($erg);
 }
 
 # ------------------
@@ -863,6 +898,21 @@ SELECT SQL_CACHE
     DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(starttime) + e.duration), "%H:%i") as \'$f{'Stop'}\',
     e.description as __Description,
     999 as __Percent,
+    ( SELECT 
+        t.id
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timerid,
+    ( SELECT 
+        (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timeractiv,
+    ( SELECT 
+        NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __running,
     IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC
 FROM
     EPG as e, CHANNELS as c, NEXTEPG as n, CHANNELGROUPS as g
@@ -881,12 +931,8 @@ ORDER BY
     my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
 
-    my $tim = main::getModule('TIMERS');
-
     $console->table($erg,
         {
-            timers => $tim->getEvents,
-            runningTimer => $tim->getRunningTimer('eventid'),
             periods => $obj->{periods},
             cgroups => $cgroups,
             channelgroup => $cgrp,
@@ -938,6 +984,21 @@ SELECT SQL_CACHE
     DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(starttime) + e.duration), "%H:%i") as \'$f{'Stop'}\',
     e.description as __Description,
     (unix_timestamp(e.starttime) + e.duration - unix_timestamp())/e.duration*100 as \'$f{'Percent'}\',
+    ( SELECT 
+        t.id
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timerid,
+    ( SELECT 
+        (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timeractiv,
+    ( SELECT 
+        NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __running,
     IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC
 FROM
     EPG as e, CHANNELS as c, CHANNELGROUPS as g
@@ -957,11 +1018,8 @@ ORDER BY
     my $erg = $sth->fetchall_arrayref();
     unshift(@$erg, $fields);
 
-    my $tim = main::getModule('TIMERS');
     $console->table($erg,
         {
-            timers => $tim->getEvents,
-            runningTimer => $tim->getRunningTimer('eventid'),
             zeit => $zeit,
             periods => $obj->{periods},
             cgroups => $cgroups,
@@ -993,7 +1051,22 @@ SELECT SQL_CACHE
     DATE_FORMAT(e.starttime, "%H:%i") as StartTime,
     (unix_timestamp(e.starttime) + e.duration - unix_timestamp())/e.duration*100 as __Percent,
     e.description as Description,
-    IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC
+    IF(e.vpstime!=0,DATE_FORMAT(e.vpstime, '%H:%i'),'') as __PDC,        
+    ( SELECT 
+        t.id
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timerid,
+    ( SELECT 
+        (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timeractiv,
+    ( SELECT 
+        NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __running
 FROM
     EPG as e, CHANNELS as c
 WHERE
@@ -1071,7 +1144,22 @@ SELECT SQL_CACHE
     UNIX_TIMESTAMP(starttime) + e.duration as second_stop,
     e.video as __video,
     e.audio as __audio,
-    e.image as __image
+    e.image as __image,        
+    ( SELECT 
+        t.id
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timerid,
+    ( SELECT 
+        (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __timeractiv,
+    ( SELECT 
+        NOW() between t.starttime and t.stoptime AND (t.flags & 1) 
+        FROM TIMERS as t
+        WHERE t.eventid = e.eventid
+        LIMIT 1) as __running
 FROM
     EPG as e, CHANNELS as c
 WHERE
@@ -1101,11 +1189,8 @@ ORDER BY
         push(@{$data->{$c->[4]}}, $c);
     }
 
-    my $tim = main::getModule('TIMERS');
     $console->table($data,
         {
-            timers => $tim->getEvents,
-            runningTimer => $tim->getRunningTimer('eventid'),
             zeitvon => $zeitvon,
             zeitbis => $zeitbis,
             periods => $obj->{periods},
diff --git a/lib/XXV/MODULES/RECORDS.pm b/lib/XXV/MODULES/RECORDS.pm
index f6b2448..a4412ae 100644
--- a/lib/XXV/MODULES/RECORDS.pm
+++ b/lib/XXV/MODULES/RECORDS.pm
@@ -1331,7 +1331,11 @@ sub createOldEventId {
 
     lg sprintf('Create event "%s" into OLDEPG', $subtitle ? $title .'~'. $subtitle : $title);
 
-    my $sth = $obj->{dbh}->prepare('REPLACE INTO OLDEPG(eventid, title, subtitle, description, channel_id, duration, tableid, starttime, video, audio, addtime) VALUES (?,?,?,?,?,?,?,FROM_UNIXTIME(?),?,?,NOW())');
+    my $sth = $obj->{dbh}->prepare(
+q|REPLACE INTO OLDEPG(eventid, title, subtitle, description, channel_id, 
+                      duration, tableid, starttime, video, audio, addtime) 
+  VALUES (?,?,?,?,?,?,?,FROM_UNIXTIME(?),?,?,NOW())|);
+
     $sth->execute(
         $attr->{eventid},
         $attr->{title},
@@ -1382,7 +1386,10 @@ SELECT SQL_CACHE
     e.description as Description,
     r.State as New,
     r.Type as Type,
-    e.channel_id
+    (SELECT Name
+      FROM CHANNELS as c
+      WHERE e.channel_id = c.Id
+      LIMIT 1) as Channel
 from
     RECORDS as r,OLDEPG as e
 where
@@ -1406,11 +1413,6 @@ where
         reccmds => [@{$obj->{reccmds}}],
     };
 
-    my $cmod = main::getModule('CHANNELS');
-    $rec->{Channel} = $cmod->ChannelToName($rec->{channel_id})
-        if($rec->{channel_id} && $rec->{channel_id} ne "<undef>");
-    delete $rec->{channel_id};
-
     $console->table($rec, $param);
 }
 
diff --git a/lib/XXV/MODULES/TIMERS.pm b/lib/XXV/MODULES/TIMERS.pm
index ae978cc..9dff05d 100644
--- a/lib/XXV/MODULES/TIMERS.pm
+++ b/lib/XXV/MODULES/TIMERS.pm
@@ -618,9 +618,13 @@ SELECT SQL_CACHE
     priority, 
     lifetime, 
     IF(flags & 1,'y','n') as activ,
-    IF(flags & 4,'y','n') as vps
+    IF(flags & 4,'y','n') as vps,
+    (SELECT description
+      FROM EPG as e
+      WHERE t.eventid = e.eventid
+      LIMIT 1) as description
 FROM
-    TIMERS
+    TIMERS as t
 WHERE
     id = ?
 |);
@@ -786,21 +790,15 @@ WHERE
         'aux' => {
             typ     => 'hidden',
             def   => $timerData->{aux},
+        },
+        'description' => {
+            msg       =>  gettext('Description'),
+            typ       => $timerData->{description} ? 'string' : 'hidden',
+            def       => $timerData->{description},
+            readonly  => 1
         }
     ];
 
-    if($timerData->{id} || $timerData->{description}) {
-      my $description = $timerData->{description} || $obj->getEpgDesc($timerData->{id});
-      if($description) {
-	  	  push(@$questions,
-	  	    'Description' => {
-              msg   =>  gettext('Description'),
-              typ     => 'string',
-              def   => $description,
-              readonly => 1
-          });
-      }
-    }
     # Ask Questions
     my $datasave = $console->question(($timerid ? gettext('Edit timer')
                                                 : gettext('New timer')), $questions, $data);
@@ -866,7 +864,7 @@ sub deleteTimer {
 
     my @timers  = split(/[^0-9a-f]/, $timerid);
 
-    my $sql = sprintf('SELECT SQL_CACHE id,pos,file,channel,starttime,IF(flags & 1 and NOW() between starttime and stoptime,1,0) FROM TIMERS where id in (%s) ORDER BY pos desc', join(',' => ('?') x @timers)); 
+    my $sql = sprintf('SELECT SQL_CACHE id,pos,file,channel,starttime,flags & 1 and NOW() between starttime and stoptime FROM TIMERS where id in (%s) ORDER BY pos desc', join(',' => ('?') x @timers)); 
     my $sth = $obj->{dbh}->prepare($sql);
     $sth->execute(@timers)
         or return error sprintf("Couldn't execute query: %s.",$sth->errstr);
@@ -1014,7 +1012,7 @@ sub toggleTimer {
         if(ref $console and $console->typ eq 'AJAX') {
           # { "data" : [ [ ID, ON, RUN, CONFLICT ], .... ] }
           # { "data" : [ [ 5, 1, 0, 0 ], .... ] }
-          my $sql = sprintf('SELECT SQL_CACHE id, flags & 1 as Active, IF(NOW() between starttime and stoptime,1,0) as Running, Collision from TIMERS where id in (%s) %s',
+          my $sql = sprintf('SELECT SQL_CACHE id, flags & 1 as Active, NOW() between starttime and stoptime as Running, Collision from TIMERS where id in (%s) %s',
                              join(',' => ('?') x @success),$ref); 
           my $sth = $obj->{dbh}->prepare($sql);
           $sth->execute(@success)
@@ -1067,7 +1065,10 @@ sub _insert {
           stop    => $nexttime->{stop},
         });
 
-    my $sth = $obj->{dbh}->prepare('REPLACE INTO TIMERS VALUES (MD5(CONCAT(?,?,?)),?,?,?,?,?,?,?,?,?,?,FROM_UNIXTIME(?), FROM_UNIXTIME(?),0,?,?,?,?,?,NOW())');
+    my $sth = $obj->{dbh}->prepare(
+q|REPLACE INTO TIMERS VALUES 
+  (MD5(CONCAT(?,?,?)),?,?,?,?,?,?,?,?,?,?,FROM_UNIXTIME(?), FROM_UNIXTIME(?),0,?,?,?,?,?,NOW())
+|);
     $sth->execute( 
          $timer->{channel},$nexttime->{start},$nexttime->{stop},
          $timer->{pos},
@@ -1204,23 +1205,15 @@ sub list {
     my $text    = shift || '';
 
 	  my $term;
-	  my $search1 = '';
-	  my $search2 = '';
+	  my $search = '';
 	  if($text and $text =~ /^[0-9a-f,_ ]+$/ and length($text) >= 32 ) {
       my @timers  = split(/[^0-9a-f]/, $text);
-      $search1 = sprintf(" AND t.id in (%s)",join(',' => ('?') x @timers));
+      $search = sprintf(" AND t.id in (%s)",join(',' => ('?') x @timers));
       foreach(@timers) { push(@{$term},$_); }
-      $search2 = sprintf(" AND t.id in (%s)",join(',' => ('?') x @timers));
-      foreach(@timers) { push(@{$term},$_); }
-
 	  } elsif($text) {
-      my $query1 = buildsearch("t.file,e.description",$text);
-      $search1 = sprintf('AND ( %s )', $query1->{query});
-      foreach(@{$query1->{term}}) { push(@{$term},$_); }
-
-      my $query2 = buildsearch("t.file",$text);
-      $search2 = sprintf('AND ( %s )', $query2->{query});
-      foreach(@{$query2->{term}}) { push(@{$term},$_); }
+      my $query = buildsearch("t.file,(SELECT description FROM EPG as e WHERE t.eventid = e.eventid LIMIT 1)",$text);
+      $search = sprintf('AND ( %s )', $query->{query});
+      foreach(@{$query->{term}}) { push(@{$term},$_); }
 	  }
 
     my %f = (
@@ -1250,43 +1243,18 @@ SELECT SQL_CACHE
     t.eventid as __eventid,
     t.autotimerid as __autotimerid,
 	  UNIX_TIMESTAMP(t.stoptime) - UNIX_TIMESTAMP(t.starttime) as __duration,
-    e.description as __description
+    (SELECT description
+      FROM EPG as e
+      WHERE t.eventid = e.eventid
+      LIMIT 1) as __description,
+    NOW() between starttime and stoptime AND (flags & 1) as __running 
 FROM
     TIMERS as t,
-    CHANNELS as c,
-    EPG as e
+    CHANNELS as c
 WHERE
     t.stoptime > NOW()
     AND t.channel = c.Id
-    AND (t.eventid = e.eventid)
-    $search1
-
-UNION 
-
-SELECT SQL_CACHE 
-    t.id as \'$f{'id'}\',
-    t.flags as \'$f{'flags'}\',
-    c.Name as \'$f{'channel'}\',
-    c.Pos as __pos,
-    t.day as \'$f{'day'}\',
-    DATE_FORMAT(t.starttime, '%H:%i') as \'$f{'start'}\',
-    DATE_FORMAT(t.stoptime, '%H:%i') as \'$f{'stop'}\',
-    t.file as \'$f{'title'}\',
-    t.priority as \'$f{'priority'}\',
-    UNIX_TIMESTAMP(t.starttime) as __day,
-    t.collision as __collision,
-    t.eventid as __eventid,
-    t.autotimerid as __autotimerid,
-	  UNIX_TIMESTAMP(t.stoptime) - UNIX_TIMESTAMP(t.starttime) as __duration,
-    "" as __description
-FROM
-    TIMERS as t,
-    CHANNELS as c
-WHERE
-    t.channel = c.Id
-    AND ((t.eventid = 0) or (t.eventid is null))
-    $search2
-
+    $search
 ORDER BY
     __day
 |;
@@ -1300,7 +1268,6 @@ ORDER BY
     unshift(@$erg, $fields);
 
     $console->table($erg, {
-        runningTimer => $obj->getRunningTimer,
         cards => $obj->{DVBCards},
 		    capacity => main::getModule('RECORDS')->{CapacityFree},
     });
@@ -1341,7 +1308,40 @@ WHERE
     return $sth->fetchrow_hashref();
 }
 
+# ------------------
+sub getTimerByPos {
+# ------------------
+    my $obj = shift  || return error('No object defined!');
+    my $tid = shift  || return error('No id defined!');
 
+    my $sql = qq|
+SELECT SQL_CACHE 
+    t.id,
+    t.flags,
+    c.Name as Channel,
+    c.Pos as __Pos,
+    t.day as Date,
+    t.start,
+    t.stop,
+    t.file,
+    t.priority,
+    UNIX_TIMESTAMP(t.starttime) as Day,
+    t.collision,
+    t.eventid,
+    t.autotimerid
+FROM
+    TIMERS as t,
+    CHANNELS as c
+WHERE
+    t.channel = c.Id
+    and t.pos = ?
+|;
+
+    my $sth = $obj->{dbh}->prepare($sql);
+    $sth->execute($tid)
+        or return error(sprintf("Timer '%s' does not exist in the database!",$tid));
+    return $sth->fetchrow_hashref();
+}
 # ------------------
 sub getRunningTimer {
 # ------------------
@@ -1624,7 +1624,7 @@ sub getNextTimer {
             or (ref $obj->{NextTimerEvent} and $obj->{NextTimerEvent}->at == $zeit)
         );
 
-        my $timer = $obj->getTimerById($nextTimer);
+        my $timer = $obj->getTimerByPos($nextTimer);
 
         $obj->{NextTimerEvent} = Event->timer(
             at  => $zeit,