Changeset 862 for trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php

Timestamp:

Oct 4, 2019, 9:37:59 AM (5 years ago)

Author:

chronos

Message:

• Modified: Optimized insertion of new NetworkInterfaceUpDown state records.

File:

• trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php (modified) (3 diffs)

trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php

@@ -44 +44 @@
     $Queries = array();
     $QueriesInsert = array();
-    $QueriesUpdate = array();
     foreach($Interfaces as $Index => $Interface)
     {
@@ -56 +55 @@
         // Online state changed
         $QueriesInsert[] = 'INSERT INTO `NetworkInterfaceUpDown` (`Interface`,'.
-          '`State`, `Time`, `Duration`) VALUES ('.$Interface['Id'].', '.$Interface['NewOnline'].', "'.
-          TimeToMysqlDateTime($StartTime).'", NULL)';
-        // Update previous record duration in UpDown table
-        $QueriesUpdate[] = 'UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND, '.
-          '`TM`.`Time`, (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` > `TM`.`Time`) '.
-          'AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` ASC LIMIT 1)) '.
-          'WHERE (`TM`.`Duration` IS NULL) AND (`TM`.`Interface` ='.$Interface['Id'].')';
+          '`State`, `Time`, `Previous`) VALUES ('.$Interface['Id'].', '.$Interface['NewOnline'].', "'.
+          TimeToMysqlDateTime($StartTime).'", (SELECT MAX(T2.Id) FROM NetworkInterfaceUpDown AS T2 WHERE T2.Interface='.$Interface['Id'].'))';
         $Queries[] = $this->Database->GetUpdate('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
           array('Online' => $Interface['NewOnline']));
@@ -73 +67 @@
     $this->Database->Transaction($Queries);
     echo("done\n");
-    echo("transakce\n");
-    $this->Database->Transaction($QueriesUpdate);
-    echo("done\n");
+
+    // Update Duration for new items
+    $this->Database->query('UPDATE NetworkInterfaceUpDown AS T1, NetworkInterfaceUpDown AS T2 SET T1.Duration = TIMESTAMPDIFF(SECOND, T1.Time, T2.Time) WHERE (T2.Previous = T1.Id) AND (T2.Interface = T1.Interface) AND (T1.Duration IS NULL)');
 
     // Set offline all interfaces which were not updated as online