Changeset 260

Timestamp:

Nov 13, 2009, 6:33:13 PM (15 years ago)

Author:

george

Message:

• Opraveno: Při generování pravidel firewallu tabulky mangle a generování stromu front docházelo k posouvání značky Mark a tím nutnost smazat a vložit mnoho řádků. Nově vytvořena tabulka NetworkMark a jedinečné id značky Mark generovány přes databázi uchovávány dlouhodobě přímo v databázi.

Location:

trunk

Files:

• index.php (modified) (2 diffs)
• sql/updates/260.sql (modified) (1 diff)
• system/generators/traffic_shaping_routerboard.php (modified) (6 diffs)
• system/routerboard.php (modified) (1 diff)

trunk/index.php

@@ -26 +26 @@
       array('Mapa sítě', '/map/', 'map.png'),
       array('Otvírací doby', '/otevreno.php', 'otv_doby.png'),
+      array('Wiki', 'http://wiki.zdechov.net/', 'clear.png'),
       array('Topologie sítě', '/is/topologie.php', 'topologie.png'),
       array('Technické informace', '/network.php', 'tech.png'),
@@ -71 +72 @@
       array('Ulož.to', 'http://uloz.to/', 'uloz_to.png'),
     ),
-    'hosted' => array('Hostované weby',
+    'hosted' => array('Hostované stránky',
       array('Heroes of Fantasy', 'http://www.heroesoffantasy.cz/', 'cube.png'),
       array('WoW překlad', 'http://wowpreklad.zdechov.net/', 'cube.png'),
+      array('Archangel portál', 'http://archangel-portal.cz/', 'clear.png'),
       array('Temné časy', 'http://temnecasy.zdechov.net/', 'cube.png'),
       array('Sharp MZ community web', 'http://sharp.zdechov.net/', 'clear.png'),

trunk/sql/updates/260.sql

@@ -1 +1 @@
 ALTER TABLE `NetworkAP` ADD `ChannelWidth` INT NOT NULL DEFAULT '20' AFTER `Frequency` ;
+
+CREATE TABLE IF NOT EXISTS `NetworkMark` (
+  `Id` int(11) NOT NULL auto_increment,
+  `Comment` varchar(255) collate utf8_czech_ci NOT NULL,
+  PRIMARY KEY  (`Id`)
+) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

trunk/system/generators/traffic_shaping_routerboard.php

@@ -9 +9 @@
 $PathFirewall = array('ip', 'firewall', 'mangle');
 $PathQueue = array('queue', 'tree');
+
+function GetMarkByComment($Comment)
+{
+  global $Database;
+  
+  $DbResult = $Database->query('SELECT `Id` FROM `NetworkMark` WHERE `Comment`="'.$Comment.'"');
+  if($DbResult->num_rows > 0)
+  {
+    $DbRow = $DbResult->fetch_assoc();
+    return($DbRow['Id']);
+  } else
+  {
+    $DbResult = $Database->query('INSERT INTO `NetworkMark` (`Comment`) VALUES ("'.$Comment.'")');
+    return($Database->insert_id);
+  } 
+}
 
 $Routerboard = new Routerboard();
@@ -44 +60 @@
 
 // Root of tree and main limit
-$PacketMark = 1;
-$ItemsQueue[] = array('name' => 'main-out', 'limit-at' => $UsersMaxSpeedIn, 'max-limit' => $UsersMaxSpeedIn, 'parent' => 'global-out', 'queue' => 'wireless-default');
-$ItemsFirewall[] = array('chain' => 'forward', 'out-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-out');
-$ItemsQueue[] = array('name' => 'main-in', 'limit-at' => $UsersMaxSpeedOut, 'max-limit' => $UsersMaxSpeedOut, 'parent' => 'global-out', 'queue' => 'wireless-default');
-$ItemsFirewall[] = array('chain' => 'forward', 'in-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-in');
+$ItemsQueue[] = array('name' => 'main-out', 'limit-at' => $UsersMaxSpeedIn, 'max-limit' => $UsersMaxSpeedIn, 'parent' => 'global-out');
+$ItemsFirewall[] = array('chain' => 'forward', 'out-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-out', 'comment' => 'main-out');
+$ItemsQueue[] = array('name' => 'main-in', 'limit-at' => $UsersMaxSpeedOut, 'max-limit' => $UsersMaxSpeedOut, 'parent' => 'global-out');
+$ItemsFirewall[] = array('chain' => 'forward', 'in-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-in', 'comment' => 'main-in');
 
 // Divide rules by subnet number
-$DbResult = $Database->query('SELECT Id, AddressRange, Mask FROM NetworkSubnet');
+$DbResult = $Database->query('SELECT `Id`, `Name`, `AddressRange`, `Mask` FROM `NetworkSubnet`');
 while($Subnet = $DbResult->fetch_assoc())
 {
   $SubnetParts = explode('.', $Subnet['AddressRange']);
   $SubnetNumber = $SubnetParts[2];
-  $ItemsFirewall[] = array('chain' => 'inet-out', 'src-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'out-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-out-'.$SubnetNumber);
-  $ItemsFirewall[] = array('chain' => 'inet-in', 'dst-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'in-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-in-'.$SubnetNumber);
+  $ItemsFirewall[] = array('chain' => 'inet-out', 'src-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'out-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-out-'.$SubnetNumber, 'comment' => 'subnet-'.RouterOSIdent($Subnet['Name']).'-out');
+  $ItemsFirewall[] = array('chain' => 'inet-in', 'dst-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'in-interface' => $InetInterface, 'action' => 'jump', 'jump-target' => 'inet-in-'.$SubnetNumber, 'comment' => 'subnet-'.RouterOSIdent($Subnet['Name']).'-in');
 }
 
 // Slow free internet
-$ItemsFirewall[] = array('chain' => 'inet-out', 'out-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'comment' => 'Free_inet', 'passthrough' => 'no');
-$ItemsQueue[] = array('name' => 'free-out', 'limit-at' => $FreeInetSpeed, 'max-limit' => $FreeInetSpeed, 'parent' => 'main-out', 'packet-mark' => $PacketMark, 'queue' => 'wireless-default');
-$PacketMark++;
-$ItemsFirewall[] = array('chain' => 'inet-in', 'in-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'comment' => 'Free_inet', 'passthrough' => 'no');
-$ItemsQueue[] = array('name' => 'free-in', 'limit-at' => $FreeInetSpeed, 'max-limit' => $FreeInetSpeed, 'parent' => 'main-in', 'packet-mark' =>$PacketMark, 'queue' => 'wireless-default');
+$PacketMark = GetMarkByComment('free-out');
+$ItemsFirewall[] = array('chain' => 'inet-out', 'out-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'comment' => 'free-out', 'passthrough' => 'no');
+$ItemsQueue[] = array('name' => 'free-out', 'limit-at' => $FreeInetSpeed, 'max-limit' => $FreeInetSpeed, 'parent' => 'main-out', 'packet-mark' => $PacketMark);
+$PacketMark = GetMarkByComment('free-in');
+$ItemsFirewall[] = array('chain' => 'inet-in', 'in-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'comment' => 'free-in', 'passthrough' => 'no');
+$ItemsQueue[] = array('name' => 'free-in', 'limit-at' => $FreeInetSpeed, 'max-limit' => $FreeInetSpeed, 'parent' => 'main-in', 'packet-mark' => $PacketMark);
 $PacketMark++;
 
 // Process users
-$DbResult = $Database->query('SELECT Member.*, Subject.Name FROM Member LEFT JOIN Subject ON Subject.Id = Member.Subject');
+$DbResult = $Database->query('SELECT `Member`.*, `Subject`.`Name` FROM `Member` LEFT JOIN `Subject` ON `Subject`.`Id` = `Member`.`Subject`');
 while($Member = $DbResult->fetch_assoc())
 {
@@ -80 +96 @@
   $UserMaxSpeedIn = round($Tariff['SpeedMax'] / $InDivider);
   $UserMaxSpeedOut = round($Tariff['SpeedMax'] / $OutDivider);
-  $ItemsQueue[] = array('name' => $Member['Name'].'-out', 'limit-at' => $SpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => 'main-out', 'queue' => 'wireless-default');
-  $ItemsQueue[] = array('name' => $Member['Name'].'-in', 'limit-at' => $SpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => 'main-in', 'queue' => 'wireless-default');
+  $ItemsQueue[] = array('name' => $Member['Name'].'-out', 'limit-at' => $SpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => 'main-out');
+  $ItemsQueue[] = array('name' => $Member['Name'].'-in', 'limit-at' => $SpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => 'main-in');
 
   $DbResult2 = $Database->select('NetworkDevice', 'COUNT(*)', 'Used = 1 AND Member='.$Member['Id']);
@@ -96 +112 @@
   }
 
-  $DbResult2 = $Database->select('NetworkDevice', '*', 'Used = 1 AND Member = '.$Member['Id']);
+  $DbResult2 = $Database->select('NetworkDevice', '*', '`Used` = 1 AND `Member` = '.$Member['Id']);
   while($Device = $DbResult2->fetch_assoc())
   {
-    $DbResult3 = $Database->select('NetworkInterface', '*', 'Device = '.$Device['Id'].' AND LocalIP != ""');
+    $DbResult3 = $Database->select('NetworkInterface', '*', '`Device` = '.$Device['Id'].' AND `LocalIP` != ""');
     while($Interface = $DbResult3->fetch_assoc())
     {
@@ -108 +124 @@
       $IPParts = explode('.', $Interface['LocalIP']);
       $Subnet = $IPParts[2];
-      $ItemsFirewall[] = array('chain' => 'inet-out-'.$Subnet, 'src-address' => $Interface['LocalIP'], 'out-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Name);
-      $ItemsQueue[] = array('name' => $Name.'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark, 'queue' => 'wireless-default');
-      $PacketMark++;
-      $ItemsFirewall[] = array('chain' => 'inet-in-'.$Subnet, 'dst-address' => $Interface['LocalIP'], 'in-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Name);
-      $ItemsQueue[] = array('name' => $Name.'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark, 'queue' => 'wireless-default');
+      $PacketMark = GetMarkByComment($Name.'-out');
+      $ItemsFirewall[] = array('chain' => 'inet-out-'.$Subnet, 'src-address' => $Interface['LocalIP'], 'out-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Name.'-out');
+      $ItemsQueue[] = array('name' => $Name.'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark);
+      $PacketMark = GetMarkByComment($Name.'-in');
+      $ItemsFirewall[] = array('chain' => 'inet-in-'.$Subnet, 'dst-address' => $Interface['LocalIP'], 'in-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Name.'-in');
+      $ItemsQueue[] = array('name' => $Name.'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark);
       $PacketMark++;
     }
   }
   
-  $DbResult2 = $Database->select('NetworkSubnet', '*', 'Member='.$Member['Id']);
+  $DbResult2 = $Database->select('NetworkSubnet', '*', '`Member`='.$Member['Id']);
   while($Subnet = $DbResult2->fetch_assoc())
   {
@@ -124 +141 @@
     $IPParts = explode('.', $Subnet['AddressRange']);
     $SubnetNumber = $IPParts[2];
-    $ItemsFirewall[] = array('chain' => 'inet-out-'.$SubnetNumber, 'src-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'out-interface' =>  $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Subnet['Name']);
-    $ItemsQueue[] = array('name' => $Subnet['Name'].'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark, 'queue' => 'wireless-default');
-    $PacketMark++;
-    $ItemsFirewall[] = array('chain' => 'inet-in-'.$SubnetNumber, 'dst-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'in-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Subnet['Name']);
-    $ItemsQueue[] = array('name' => $Subnet['Name'].'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark, 'queue' => 'wireless-default');
-    $PacketMark++;
+    $PacketMark = GetMarkByComment($Subnet['Name'].'-out');
+    $ItemsFirewall[] = array('chain' => 'inet-out-'.$SubnetNumber, 'src-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'out-interface' =>  $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Subnet['Name'].'-out');
+    $ItemsQueue[] = array('name' => $Subnet['Name'].'-out', 'limit-at' => $HostSpeedIn, 'max-limit' => $UserMaxSpeedIn, 'parent' => $Member['Name'].'-out', 'packet-mark' => $PacketMark);
+    $PacketMark = GetMarkByComment($Subnet['Name'].'-in');
+    $ItemsFirewall[] = array('chain' => 'inet-in-'.$SubnetNumber, 'dst-address' => $Subnet['AddressRange'].'/'.$Subnet['Mask'], 'in-interface' => $InetInterface, 'action' => 'mark-packet', 'new-packet-mark' => $PacketMark, 'passthrough' => 'no', 'comment' => $Subnet['Name'].'-in');
+    $ItemsQueue[] = array('name' => $Subnet['Name'].'-in', 'limit-at' => $HostSpeedOut, 'max-limit' => $UserMaxSpeedOut, 'parent' => $Member['Name'].'-in', 'packet-mark' => $PacketMark);
   }
   echo("\n");
 }
 
-$Routerboard->ListUpdate($PathQueue, array('name', 'limit-at', 'max-limit', 'parent', 'packet-mark', 'queue'), $ItemsQueue, array(), true);
+//print_r($ItemsFirewall);
+$Routerboard->ListUpdate($PathQueue, array('name', 'limit-at', 'max-limit', 'parent', 'packet-mark'), $ItemsQueue, array(), true);
 $Routerboard->ListUpdate($PathFirewall, array('chain', 'dst-address', 'in-interface', 'action', 'new-packet-mark', 'passthrough', 'comment', 'out-interface', 'src-address', 'jump-target'), $ItemsFirewall, array(), true);
 

trunk/system/routerboard.php

@@ -152 +152 @@
   function ListUpdate($Path, $Properties, $Values, $Condition = array(), $UsePrint = false)
   {
+    // Get current list from routerboard
     if($UsePrint == 0) 
     {