Changeset 852

Timestamp:

Oct 25, 2017, 10:55:05 PM (7 years ago)

Author:

chronos

Message:

• Modified: Use better transaction mechanism for netwatch import and latency measurement.
• Fixed: Query duration mesurement using outdated microtime result.

Location:

trunk

Files:

• Install/deb/debian/control (modified) (1 diff)
• Modules/NetworkConfigLinux/Generators/Latency.php (modified) (1 diff)
• Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php (modified) (4 diffs)
• Packages/Common/Database.php (modified) (6 diffs)

trunk/Install/deb/debian/control

@@ -8 +8 @@
 Package: centrala
 Architecture: all
-Depends: ${shlibs:Depends}, ${misc:Depends}, apache2, php, dbconfig-mysql, fping, htmldoc
+Depends: ${shlibs:Depends}, ${misc:Depends}, apache2, php, dbconfig-mysql, fping, htmldoc, php-mbstring
 Description: User portal and network administration web system
 HomePage: http://svn.zdechov.net/trac/centrala

trunk/Modules/NetworkConfigLinux/Generators/Latency.php

@@ -22 +22 @@
     exec('fping -e -r '.$RetryLimit.' -t '.$Timeout.' -a '.implode(' ', $Hosts).' 2>/dev/null', $Output);
 
-    $this->Database->query('BEGIN');
-    foreach($Output as $Index => $Line) {
+    $Queries = array();
+    foreach($Output as $Index => $Line) 
+    {
       $IP = substr($Line, 0, strPos($Line, ' '));
       $Line = substr($Line, strpos($Line, '(') + 1);
       $Value = substr($Line, 0, strpos($Line, ' '));
       //echo($IP.' '.$Value."\n");
-      $this->Database->insert('NetworkInterfaceLatency', array('Interface' => $HostInterface[$IP],
+      $Queries[] = $this->Database->GetInsert('NetworkInterfaceLatency', array('Interface' => $HostInterface[$IP],
         'Value' => $Value, 'Time' => TimeToMysqlDateTime($Time)));
     }
-    $this->Database->query('COMMIT');
+    $this->Database->Transaction($Queries);
   }
 

trunk/Modules/NetworkConfigRouterOS/Generators/NetwatchImport.php

@@ -42 +42 @@
     }
 
-    $this->Database->query('BEGIN');
-
+    $Queries = array();
     foreach($Interfaces as $Index => $Interface)
     {
       // Update last online time if still online
       if($Interface['NewOnline'])
-        $DbResult = $this->Database->update('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
+        $Queries[] = $this->Database->GetUpdate('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
           array('LastOnline' => TimeToMysqlDateTime($StartTime)));
 
@@ -54 +53 @@
       {
         // Online state changed
-        $DbResult = $this->Database->query('INSERT INTO `NetworkInterfaceUpDown` (`Interface`,
+        $Queries[] = 'INSERT INTO `NetworkInterfaceUpDown` (`Interface`,
          `State`, `Time`, `Duration`) VALUES ('.$Interface['Id'].', '.$Interface['NewOnline'].', "'.
-          TimeToMysqlDateTime($StartTime).'", NULL)');
+          TimeToMysqlDateTime($StartTime).'", NULL)';
         // Update previous record duration in UpDown table
-        $this->Database->query('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND, '.
+        $Queries[] = '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'].')');
-        $this->Database->update('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
+          'WHERE (`TM`.`Duration` IS NULL) AND (`TM`.`Interface` ='.$Interface['Id'].')';
+        $Queries[] = $this->Database->GetUpdate('NetworkInterface', '`Id` = "'.$Interface['Id'].'"',
           array('Online' => $Interface['NewOnline']));
       }
     }
+    $this->Database->Transaction($Queries);
 
     // Set offline all interfaces which were not updated as online
@@ -81 +81 @@
     }
 
+    $Queries = array();
     // Update device online state
     $DbResult = $this->Database->select('NetworkInterface', '`Device`, SUM(`Online`) AS `SumOnline`', '`Online` = 1 GROUP BY `Device`');
@@ -86 +87 @@
     {
       if($Device['SumOnline'] > 0)
-        $this->Database->update('NetworkDevice', 'Id='.$Device['Device'], array('LastOnline' => TimeToMysqlDateTime($StartTime), 'Online' => 1));
+        $Queries[] = $this->Database->GetUpdate('NetworkDevice', 'Id='.$Device['Device'], array('LastOnline' => TimeToMysqlDateTime($StartTime), 'Online' => 1));
     }
-    $this->Database->update('NetworkDevice', '`LastOnline` < "'.TimeToMysqlDateTime($StartTime).'"', array('Online' => 0));
-
-    $this->Database->query('COMMIT');
+    $Queries[] = $this->Database->GetUpdate('NetworkDevice', '`LastOnline` < "'.TimeToMysqlDateTime($StartTime).'"', array('Online' => 0));
+    $this->Database->Transaction($Queries);
   }
 

trunk/Packages/Common/Database.php

@@ -3 +3 @@
 // Extended database class
 // Date: 2016-01-11
+
+function microtime_float()
+{
+  list($usec, $sec) = explode(" ", microtime());
+  return ((float)$usec + (float)$sec);
+}
 
 class DatabaseResult
@@ -51 +57 @@
     $this->LogFile = dirname(__FILE__).'/../../Query.log';
   }
+ 
 
   function Connect($Host, $User, $Password, $Database)
@@ -85 +92 @@
   {
     if(!$this->Connected()) throw new Exception(T('Not connected to database'));
-    if(($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime();
+    if(($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true)) $QueryStartTime = microtime_float();
     $this->LastQuery = $Query;
+    //echo('a'.$this->ShowSQLQuery.'<'.$QueryStartTime.', '.microtime_float());
     if(($this->ShowSQLQuery == true) or ($this->LogSQLQuery == true))
-      $Duration = ' ; '.round(microtime() - $QueryStartTime, 4). ' s';
+      $Duration = ' ; '.round(microtime_float() - $QueryStartTime, 4). ' s';
     if($this->LogSQLQuery == true)
       file_put_contents($this->LogFile, $Query.$Duration."\n", FILE_APPEND);
@@ -123 +131 @@
   function insert($Table, $Data)
   {
+    $this->query($this->GetInsert($Table, $Data));
+    $this->insert_id = $this->PDO->lastInsertId();
+  }
+  
+  function GetInsert($Table, $Data)
+  {
     $Name = '';
     $Values = '';
@@ -137 +151 @@
     $Name = substr($Name, 1);
     $Values = substr($Values, 1);
-    $this->query('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
-    $this->insert_id = $this->PDO->lastInsertId();
+    return('INSERT INTO `'.$this->Prefix.$Table.'` ('.$Name.') VALUES('.$Values.')');
   }
 
   function update($Table, $Condition, $Data)
+  {
+    $this->query($this->GetUpdate($Table, $Condition, $Data));
+  }
+  
+  function GetUpdate($Table, $Condition, $Data)
   {
     $Values = '';
@@ -154 +172 @@
     }
     $Values = substr($Values, 2);
-    //echo('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');
-    $this->query('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');
+    return('UPDATE `'.$this->Prefix.$Table.'` SET '.$Values.' WHERE ('.$Condition.')');
   }