Changeset 862

Timestamp:

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

Author:

chronos

Message:

• Modified: Optimized insertion of new NetworkInterfaceUpDown state records.

Location:

trunk

Files:

• Application/UpdateTrace.php (modified) (2 diffs)
• Application/Version.php (modified) (1 diff)
• Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php (modified) (3 diffs)

trunk/Application/UpdateTrace.php

@@ -2056 +2056 @@
   $Manager->Execute('ALTER TABLE `NetworkDeviceLog`
     ADD CONSTRAINT `NetworkDeviceLog_ibfk_1` FOREIGN KEY (`Device`) REFERENCES `NetworkDevice` (`Id`);');
+}
+
+function UpdateTo862($Manager)
+{
+  $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` ADD `Previous` INT NULL AFTER `Duration`');
+  $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` ADD INDEX ( `Previous` )');
+  $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS T1, `NetworkInterfaceUpDown` AS T2 SET T1.Previous = T2.Id WHERE (T1.Previous IS NULL) AND (T2.Interface=T1.Interface) AND (T1.Time = DATE_ADD(T2.Time, INTERVAL T2.Duration SECOND))');
 }
 
@@ -2154 +2161 @@
       838 => array('Revision' => 844, 'Function' => 'UpdateTo844'),
       844 => array('Revision' => 855, 'Function' => 'UpdateTo855'),
+      855 => array('Revision' => 862, 'Function' => 'UpdateTo862'),
     ));
   }

trunk/Application/Version.php

@@ -1 +1 @@
 <?php
 
-$Revision = 857; // Subversion revision
-$DatabaseRevision = 855; // SQL structure revision
-$ReleaseTime = strtotime('2019-05-08');
+$Revision = 862; // Subversion revision
+$DatabaseRevision = 862; // SQL structure revision
+$ReleaseTime = strtotime('2019-10-04');

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