Changeset 921

Timestamp:

Feb 25, 2022, 9:30:55 AM (3 years ago)

Author:

chronos

Message:

• Modified: NetworkSignal MAC column transformed into separate table NetworkMac.

Location:

trunk

Files:

• Application/UpdateTrace.php (modified) (2 diffs)
• Application/Version.php (modified) (1 diff)
• Modules/Network/Network.php (modified) (3 diffs)
• Modules/Network/NetworkModels.php (modified) (2 diffs)
• Modules/NetworkConfigAirOS/Generators/Signal.php (modified) (2 diffs)
• Modules/NetworkConfigRouterOS/Generators/Signal.php (modified) (4 diffs)

trunk/Application/UpdateTrace.php

@@ -2175 +2175 @@
 {
   $Manager->Execute('ALTER TABLE `NetworkSignal` ADD `Remote` INT NOT NULL DEFAULT "0" AFTER `Device`;');
+}
+
+function UpdateTo921(UpdateManager $Manager): void
+{
+  $Manager->Execute('CREATE TABLE `NetworkMac` (
+    `Id` int(11) NOT NULL,
+    `MAC` varchar(17) NOT NULL
+  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+  $Manager->Execute('ALTER TABLE `NetworkMac` ADD PRIMARY KEY (`Id`), ADD KEY `MAC` (`MAC`);');
+  $Manager->Execute('ALTER TABLE `NetworkMac` MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');  
+
+  // Convert NetworkSignal MAC from string into reference
+  $Manager->Execute('INSERT INTO `NetworkMac` (`MAC`) SELECT DISTINCT(`MAC`) FROM `NetworkSignal`;');
+  $Manager->Execute('ALTER TABLE `NetworkSignal` ADD `MACTemp` INT NOT NULL AFTER `Remote`;');
+  $Manager->Execute('UPDATE `NetworkSignal` SET `MACTemp` = (SELECT `Id` FROM `NetworkMac` WHERE `NetworkMac`.`MAC` = `NetworkSignal`.`MAC`);');
+  $Manager->Execute('ALTER TABLE `NetworkSignal` DROP `MAC`;');
+  $Manager->Execute('ALTER TABLE `NetworkSignal` CHANGE `MACTemp` `MAC` INT(11) NOT NULL;');
+  $Manager->Execute('ALTER TABLE `NetworkSignal` ADD KEY `MAC` (`MAC`);');
+  $Manager->Execute('ALTER TABLE `NetworkSignal` ADD FOREIGN KEY (`MAC`) REFERENCES `NetworkMac`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+
+  // IS menu item
+  $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
+    `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
+    NULL , "", "Fyzické adresy", "1", "/is/?t=NetworkMac&a=list", NULL , NULL , NULL , "1");');
+  $ActionId = $Manager->Database->insert_id;
+  $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Síť"');
+  if ($DbResult->num_rows > 0)
+  {
+    $DbRow = $DbResult->fetch_assoc();
+    $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
+        "VALUES (NULL , 'Fyzické adresy', ".$DbRow['Id'].", '".$ActionId."', '1');");
+  }
 }
 
@@ -2284 +2316 @@
       901 => array('Revision' => 907, 'Function' => 'UpdateTo907'),
       907 => array('Revision' => 911, 'Function' => 'UpdateTo911'),
+      911 => array('Revision' => 921, 'Function' => 'UpdateTo921'),
     );
   }

trunk/Application/Version.php

@@ -1 +1 @@
 <?php
 
-$Revision = 920; // Subversion revision
-$DatabaseRevision = 911; // SQL structure revision
-$ReleaseTime = strtotime('2022-02-23');
+$Revision = 921; // Subversion revision
+$DatabaseRevision = 921; // SQL structure revision
+$ReleaseTime = strtotime('2022-02-24');

trunk/Modules/Network/Network.php

@@ -120 +120 @@
       NetworkDomain::GetClassName(), NetworkDomainServer::GetClassName(), NetworkDomainView::GetClassName(),
       NetworkDomainItemFilter::GetClassName(), DeviceAPIType::GetClassName(), NetworkInterfaceWireless::GetClassName(),
-      NetworkInterfaceUpDown::GetClassName(), NetworkPortUpDown::GetClassName());
+      NetworkInterfaceUpDown::GetClassName(), NetworkPortUpDown::GetClassName(), NetworkMac::GetClassName());
 
     // TODO: Make notify time configurable
@@ -380 +380 @@
       'Items' => array(
         'Time' => array('Type' => 'DateTime', 'Caption' => 'Čas', 'Default' => ''),
-        'MAC' => array('Type' => 'MacAddress', 'Caption' => 'Fyzická adresa (MAC)', 'Default' => ''),
+        'MAC' => array('Type' => 'TNetworkMac', 'Caption' => 'Fyzická adresa (MAC)', 'Default' => ''),
         'Interface' => array('Type' => 'TNetworkInterface', 'Caption' => 'Rozhraní', 'Default' => '', 'Null' => true),
         'Value' => array('Type' => 'Integer', 'Caption' => 'Signál', 'Default' => '0', 'Suffix' => 'dBm'),
@@ -608 +608 @@
         'Duration' => array('Type' => 'TimeDiff', 'Caption' => 'Trvání', 'Default' => '', 'ReadOnly' => true),
       ),
+    ));
+    $this->System->FormManager->RegisterClass('NetworkMac', array(
+      'Title' => 'Fyzická síťová adresa',
+      'Table' => 'NetworkMac',
+      'Items' => array(
+        'MAC' => array('Type' => 'String', 'Caption' => 'Fyzická adresa', 'Default' => ''),
+        'Signal' => array('Type' => 'TNetworkSignalListMac', 'Caption' => 'Signál rozhraní', 'Default' => ''),        
+      ),
+    ));
+    $this->System->FormManager->RegisterFormType('TNetworkMac', array(
+      'Type' => 'Reference',
+      'Table' => 'NetworkMac',
+      'Id' => 'Id',
+      'Name' => 'MAC',
+      'Filter' => '1',
+    ));
+    $this->System->FormManager->RegisterFormType('TNetworkSignalListMac', array(
+      'Type' => 'ManyToOne',
+      'Table' => 'NetworkSignal',
+      'Id' => 'Id',
+      'Ref' => 'Mac',
+      'Filter' => '1',
     ));
     $this->System->FormManager->RegisterFormType('TNetworkProtocol', array(

trunk/Modules/Network/NetworkModels.php

@@ -192 +192 @@
     $Desc = new ModelDesc(self::GetClassName());
     $Desc->AddDateTime('Time');
-    $Desc->AddString('MAC');
+    $Desc->AddReference('MAC', NetworkMac::GetClassName());
     $Desc->AddReference('Interface', NetworkInterface::GetClassName());
     $Desc->AddInteger('Value');
@@ -342 +342 @@
   }
 }
+
+class NetworkMac extends Model
+{
+  static function GetModelDesc(): ModelDesc
+  {
+    $Desc = new ModelDesc(self::GetClassName());
+    $Desc->AddString('MAC'); // size=17
+    return $Desc;
+  }
+
+  function GetIndex(string $MAC)
+  {
+    $DbResult = $this->Database->select('NetworkMac', 'Id', 'MAC="'.$MAC.'"');
+    if ($DbResult->num_rows > 0)
+    {
+      $DbRow = $DbResult->fetch_assoc();
+      return $DbRow['Id'];
+    } else 
+    {
+      return $this->Database->insert('NetworkMac', array('MAC' => $MAC));
+    }
+  }
+}

trunk/Modules/NetworkConfigAirOS/Generators/Signal.php

@@ -8 +8 @@
   {
     $Time = time();
+    $NetworkMac = new NetworkMac($this->System);
 
     // Load netwatch status from all DHCP routers
@@ -38 +39 @@
           $RateRx = $Properties['rx'];
           $RateTx = $Properties['tx'];
-          $this->Database->insert('NetworkSignal', array('MAC' => $Properties['mac'],
+          $MacRef = $NetworkMac->GetIndex($Properties['mac']);
+          $this->Database->insert('NetworkSignal', array('MAC' => $MacRef,
             'Value' => $Strength, 'Remote' => $RemoteSignal, 'RateRx' => $RateRx, 'RateTx' => $RateTx,
             'Time' => TimeToMysqlDateTime($Time), 'Interface' => $Interface, 'Device' => $Device['Id']));

trunk/Modules/NetworkConfigRouterOS/Generators/Signal.php

@@ -39 +39 @@
     $Read = $Routerboard->Read(false);
     $Array = $Routerboard->ParseResponse($Read);
+    $NetworkMac = new NetworkMac($this->System);
     foreach ($Array as $Properties)
     {
@@ -75 +76 @@
       $RateRx = $this->StripUnits($Properties['rx-rate']);
       $RateTx = $this->StripUnits($Properties['tx-rate']);
+      $MacRef = $NetworkMac->GetIndex($Properties['mac-address']);
       $Queries[] = 'INSERT INTO `NetworkSignal` (`MAC`, `Value`, `Remote`, `RateRx`, `RateTx`, `Time`, `Interface`, `Device`) VALUES '.
-        '("'.$Properties['mac-address'].'", '.$Strength.', '.$RemoteSignal.', '.$RateRx.', '.$RateTx.', "'.
+        '('.$MacRef.', '.$Strength.', '.$RemoteSignal.', '.$RateRx.', '.$RateTx.', "'.
         TimeToMysqlDateTime($this->Time).'", '.$Interface.', '.$Device['Id'].')';              
     }
@@ -98 +100 @@
       return array();
     }    
+    $NetworkMac = new NetworkMac($this->System);
     foreach ($Array as $Properties)
     {
@@ -119 +122 @@
 
       $RateTx = $this->StripUnits($Properties['tx-phy-rate']);
+      
+      $MacRef = $NetworkMac->GetIndex($Properties['remote-address']);
       $Queries[] = 'INSERT INTO `NetworkSignal` (`MAC`, `Value`, `Remote`, `RateRx`, `RateTx`, `Time`, `Interface`, `Device`) VALUES '.
-        '("'.$Properties['remote-address'].'", '.$Strength.', 0, 0, '.round($RateTx / 1000000).', "'.
+        '('.$MacRef.', '.$Strength.', 0, 0, '.round($RateTx / 1000000).', "'.
         TimeToMysqlDateTime($this->Time).'", '.$Interface.', '.$Device['Id'].')';              
     }