Changeset 572 for trunk/cgi-bin

Timestamp:

12/21/12 17:33:03 (12 years ago)

Author:

Kris Deugau

Message:

/trunk

Work in progress, see #5:
Get-info-about-freeblocks cleanups

• Update/tweak getPoolSelect() for rdepth and saner SQL
• Update findAllocateFrom() for new table logic and structures

File:

• trunk/cgi-bin/IPDB.pm (modified) (3 diffs)

trunk/cgi-bin/IPDB.pm

@@ -587 +587 @@
 
   my $plist = $dbh->selectall_arrayref(
-        "SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool) AS poolcit, ".
-        "poolips.pool AS poolblock, COUNT(*) AS poolfree FROM poolips,allocations ".
-        "WHERE poolips.available='y' AND poolips.pool=allocations.cidr ".
-        "AND allocations.city = ? AND poolips.type LIKE ? ".
-        "GROUP BY pool", { Slice => {} }, ($pcity, $ptype) );
+        "SELECT count(*) AS poolfree,p.pool AS poolblock, a.city AS poolcit, a.rdepth AS poolrdepth ".
+        "FROM poolips p ".
+        "JOIN allocations a ON p.pool=a.cidr ".
+        "WHERE p.available='y' AND a.city = ? AND p.type LIKE ? ".
+        "GROUP BY p.pool,a.city,a.rdepth",
+        { Slice => {} }, ($pcity, $ptype) );
   return $plist;
 } # end getPoolSelect()
@@ -620 +621 @@
 ## Also populate a value list for the DBI call.
 
-  my @vallist = ($maskbits, ($type eq 'rm' ? 'n' : ($type =~ /^(.)r$/ ? "$1" : 'y')) );
-  my $sql = "SELECT cidr FROM freeblocks WHERE maskbits <= ? AND routed = ?";
+  my @vallist = ($maskbits);
+  my $sql = "SELECT cidr,rdepth FROM freeblocks WHERE masklen(cidr) <= ?";
+
+# cases, strict rules
+# .c  ->  container type
+#   requires a routing container, fbtype r
+# .d  ->  DHCP/"normal-routing" static pool
+#   requires a routing container, fbtype r
+# .e  ->  Dynamic-assignment connectivity
+#   requires a routing container, fbtype r
+# .i  ->  error, can't allocate static IPs this way?
+# mm  ->  error, master block
+# rm  ->  routed block
+#   requires master block, fbtype m
+# .n  ->  Miscellaneous usage
+#   requires a routing container, fbtype r
+# .p  ->  PPP(oE) static pool
+#   requires a routing container, fbtype r
+# .r  ->  contained type
+#   requires a matching container, fbtype $1
+##fixme:  strict-or-not flag
+
+  if ($type =~ /^(.)r$/) {
+    push @vallist, $1;
+    $sql .= " AND routed = ?";
+  } elsif ($type eq 'rm') {
+    $sql .= " AND routed = 'm'";
+  } else {
+    $sql .= " AND routed = 'r'";
+  }
 
   # for PPP(oE) and container types, the POP city is the one attached to the pool.
@@ -651 +680 @@
   $sql .= " ORDER BY maskbits DESC,cidr LIMIT 1";
 
-  my ($fbfound) = $dbh->selectrow_array($sql, undef, @vallist);
-  return $fbfound;
+  my ($fbfound,$fbdepth) = $dbh->selectrow_array($sql, undef, @vallist);
+  return $fbfound,$fbdepth;
 } # end findAllocateFrom()