r1271 + completely rewrote getIPRange() to avoid nested joins and be more MySQL-4...
[racktables] / inc / functions.php
index b1a48109814ebb8ad103a8bb68528a1557b62150..3f443bd4a7fc57e21ddca6a519176c0a4978967b 100644 (file)
@@ -439,65 +439,67 @@ function getIPRange ($id = 0)
        $result = $dbxlink->query ($query);
        if ($result == NULL)
                return NULL;
-       $ret=array();
+       $ret = array();
        $row = $result->fetch (PDO::FETCH_ASSOC);
        if ($row == NULL)
                return $ret;
        $ret['id'] = $row['IPRanges_id'];
        $ret['ip'] = $row['IPRanges_ip'];
-       $ret['ip_bin'] = ip2long($ret['ip']);
+       $ret['ip_bin'] = $row['IPRanges_ip_bin'];
        $ret['mask_bin'] = binMaskFromDec($row['IPRanges_mask']);
        $ret['mask_bin_inv'] = binInvMaskFromDec($row['IPRanges_mask']);
        $ret['name'] = $row['IPRanges_name'];
        $ret['mask'] = $row['IPRanges_mask'];
        $ret['addrlist'] = array();
-
        $result->closeCursor();
+       $first_bin = sprintf ('%u', ($ret['ip_bin'] & $ret['mask_bin']));
+       $last_bin = sprintf ('%u', ($ret['ip_bin'] | ($ret['mask_bin_inv'])));
+
+       // Don't try to build up the whole structure in a single pass. Request
+       // the list of user comments and reservations and merge allocations in
+       // at a latter point.
        $query =
-               "select ".
-               "IPAddress.ip as ip_bin, ".
-               "INET_NTOA(IPAddress.ip) as ip, ".
-               "IPAddress.name as name, ".
-               "IPAddress.reserved as reserved, ".
-               "IPBonds.object_id as object_id, ".
-               "RackObject.name as object_name, ".
-               "IPBonds.name as bond_name, ".
-               "IPBonds.type as bond_type ".
-               "from IPAddress left join ".
-                       "(IPBonds join RackObject on IPBonds.object_id = RackObject.id) ".
-               "on IPAddress.ip = IPBonds.ip ".
-               "where IPAddress.ip >= '".sprintf('%u', ($ret['ip_bin'] & $ret['mask_bin']))."' and ".
-                       "IPAddress.ip <= '".sprintf('%u', ($ret['ip_bin'] | ($ret['mask_bin_inv']) ))."' ".
-               "having (reserved='yes' or name != '' or IPAddress.name != '' or object_id is not NULL) ". 
-               "order by IPAddress.ip, IPBonds.type, RackObject.name";
-       $res_list=$dbxlink->query ($query);
-       $prev_ip=0;
-       if ($res_list == NULL)
+               "select ip as ip_bin, INET_NTOA(ip) as ip, name, reserved from IPAddress " .
+               "where ip between ${first_bin} and ${last_bin} " .
+               "and (reserved = 'yes' or name != '')";
+       $ipa_res = $dbxlink->query ($query);
+       if ($ipa_res == NULL)
                return $ret;
-       while ($row1 = $res_list->fetch (PDO::FETCH_ASSOC))
+       while ($row = $ipa_res->fetch (PDO::FETCH_ASSOC))
        {
-               if ($prev_ip != $row1['ip'])
-               {
-                       $refcount=0;
-                       $count=ip2long($row1['ip']);
-                       $ret['addrlist'][$count]['name'] = $row1['name'];
-                       $ret['addrlist'][$count]['reserved'] = $row1['reserved'];
-                       $ret['addrlist'][$count]['ip'] = $row1['ip'];
-                       $ret['addrlist'][$count]['ip_bin'] = ip2long($row1['ip']);
-                       $prev_ip = $ret['addrlist'][$count]['ip'];
-                       $ret['addrlist'][$count]['references'] = array();
-               }
+               $ret['addrlist'][$row['ip_bin']] = $row;
+               $tmp = array();
+               foreach (array ('ip', 'name', 'reserved') as $cname)
+                       $tmp[$cname] = $row[$cname];
+               $tmp['references'] = array();
+               $ret['addrlist'][$row['ip_bin']] = $tmp;
+       }
+       $ipa_res->closeCursor();
 
-               if ($row1['bond_type'])
+       $query =
+               "select ipb.ip as ip_bin, INET_NTOA(ipb.ip) as ip, ro.id as object_id," .
+               "ro.name as object_name, ipb.name, ipb.type from " .
+               "IPBonds as ipb inner join RackObject as ro on ipb.object_id = ro.id " .
+               "where ip between ${first_bin} and ${last_bin} " .
+               "order by ipb.type, object_name";
+       $ipb_res = $dbxlink->query ($query);
+       while ($row = $ipb_res->fetch (PDO::FETCH_ASSOC))
+       {
+               if (!isset ($ret['addrlist'][$row['ip_bin']]))
                {
-                       $ret['addrlist'][$count]['references'][$refcount]['type'] = $row1['bond_type'];
-                       $ret['addrlist'][$count]['references'][$refcount]['name'] = $row1['bond_name'];
-                       $ret['addrlist'][$count]['references'][$refcount]['object_id'] = $row1['object_id'];
-                       $ret['addrlist'][$count]['references'][$refcount]['object_name'] = $row1['object_name'];
-                       $refcount++;
+                       $ret['addrlist'][$row['ip_bin']] = array();
+                       $ret['addrlist'][$row['ip_bin']]['ip'] = $row['ip'];
+                       $ret['addrlist'][$row['ip_bin']]['name'] = '';
+                       $ret['addrlist'][$row['ip_bin']]['reserved'] = 'no';
+                       $ret['addrlist'][$row['ip_bin']]['references'] = array();
                }
+               $tmp = array();
+               foreach (array ('object_id', 'object_name', 'type', 'name') as $cname)
+                       $tmp[$cname] = $row[$cname];
+               $ret['addrlist'][$row['ip_bin']]['references'][] = $tmp;
        }
-       $res_list->closeCursor();
+       $ipb_res->closeCursor();
+
        return $ret;
 }