Index: /trunk/cgi-bin/IPDB.pm
===================================================================
--- /trunk/cgi-bin/IPDB.pm (revision 117)
+++ /trunk/cgi-bin/IPDB.pm (revision 118)
@@ -23,4 +23,5 @@
@EXPORT_OK = qw(
%disp_alloctypes %list_alloctypes @citylist @poplist @masterblocks
+ %allocated %free %routed %bigfree
&initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock &deleteBlock
&mailNotify
@@ -30,4 +31,5 @@
%EXPORT_TAGS = ( ALL => [qw(
%disp_alloctypes %list_alloctypes @citylist @poplist @masterblocks
+ %allocated %free %routed %bigfree
&initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock
&deleteBlock &mailNotify
@@ -43,4 +45,8 @@
our @poplist;
our @masterblocks;
+our %allocated;
+our %free;
+our %routed;
+our %bigfree;
# Let's initialize the globals.
@@ -77,4 +83,9 @@
for (my $i=0; my @data = $sth->fetchrow_array(); $i++) {
$masterblocks[$i] = new NetAddr::IP $data[0];
+ $allocated{"$masterblocks[$i]"} = 0;
+ $free{"$masterblocks[$i]"} = 0;
+ $bigfree{"$masterblocks[$i]"} = 128; # Larger number means smaller block.
+ # Set to 128 to prepare for IPv6
+ $routed{"$masterblocks[$i]"} = 0;
}
return (undef,$sth->errstr) if $sth->err;
Index: /trunk/cgi-bin/main.cgi
===================================================================
--- /trunk/cgi-bin/main.cgi (revision 117)
+++ /trunk/cgi-bin/main.cgi (revision 118)
@@ -407,6 +407,6 @@
# Initial display: Show master blocks with total allocated subnets, total free subnets
-sub showSummary
-{
+sub showSummary {
+ # this is horrible-ugly-bad and will Go Away real soon now(TM)
print "Content-type: text/html\n\n";
@@ -419,49 +419,37 @@
my %bigfree;
-# Snag the allocations.
-# I think it's too confusing to leave out internal allocations.
- $sth = $ip_dbh->prepare("select * from allocations");
- $sth->execute();
- while (my @data = $sth->fetchrow_array()) {
- # cidr,custid,type,city,description
- # We only need the cidr
- my $cidr = new NetAddr::IP $data[0];
- foreach my $master (@masterblocks) {
- if ($master->contains($cidr)) {
- $allocated{"$master"}++;
- }
- }
- }
-
-# Snag routed blocks
- $sth = $ip_dbh->prepare("select * from routed");
- $sth->execute();
- while (my @data = $sth->fetchrow_array()) {
- # cidr,maskbits,city
- # We only need the cidr
- my $cidr = new NetAddr::IP $data[0];
- foreach my $master (@masterblocks) {
- if ($master->contains($cidr)) {
- $routed{"$master"}++;
- }
- }
- }
-
-# Snag the free blocks.
- $sth = $ip_dbh->prepare("select * from freeblocks");
- $sth->execute();
- while (my @data = $sth->fetchrow_array()) {
- # cidr,maskbits,city
- # We only need the cidr
- my $cidr = new NetAddr::IP $data[0];
- foreach my $master (@masterblocks) {
- if ($master->contains($cidr)) {
- $free{"$master"}++;
- if ($cidr->masklen < $bigfree{"$master"}) { $bigfree{"$master"} = $cidr->masklen; }
- }
- }
- }
-
-# Print the data.
+ # Count the allocations.
+ $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?");
+ foreach my $master (@masterblocks) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$allocated{"$master"});
+ $sth->fetch();
+ }
+
+ # Count routed blocks
+ $sth = $ip_dbh->prepare("select count(*) from routed where cidr <<= ?");
+ foreach my $master (@masterblocks) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$routed{"$master"});
+ $sth->fetch();
+ }
+
+ # Count the free blocks.
+ $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ?");
+ foreach my $master (@masterblocks) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$free{"$master"});
+ $sth->fetch();
+ }
+
+ # Find the largest free block in each master
+ $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? order by maskbits limit 1");
+ foreach my $master (@masterblocks) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$bigfree{"$master"});
+ $sth->fetch();
+ }
+
+ # Print the data.
my $count=0;
foreach my $master (@masterblocks) {
@@ -501,5 +489,6 @@
my @localmasters;
- $sth = $ip_dbh->prepare("select * from routed order by cidr");
+ # Fetch only the blocks relevant to this master
+ $sth = $ip_dbh->prepare("select * from routed where cidr <<= '$master' order by cidr");
$sth->execute();
@@ -507,53 +496,39 @@
while (my @data = $sth->fetchrow_array()) {
my $cidr = new NetAddr::IP $data[0];
- if ($master->contains($cidr)) {
- $localmasters[$i++] = $cidr;
- $free{"$cidr"} = 0;
- $allocated{"$cidr"} = 0;
+ $localmasters[$i++] = $cidr;
+ $free{"$cidr"} = 0;
+ $allocated{"$cidr"} = 0;
+ $bigfree{"$cidr"} = 128;
# Retain the routing destination
- $routed{"$cidr"} = $data[2];
- }
- }
-
-# Check if there were actually any blocks routed from this master
+ $routed{"$cidr"} = $data[2];
+ }
+
+ # Check if there were actually any blocks routed from this master
if ($i > 0) {
startTable('Routed block','Routed to','Allocated blocks',
'Free blocks','Largest free block');
- # Count the allocations
- $sth = $ip_dbh->prepare("select * from allocations");
- $sth->execute();
- while (my @data = $sth->fetchrow_array()) {
- # cidr,custid,type,city,description
- # We only need the cidr
- my $cidr = new NetAddr::IP $data[0];
- foreach my $master (@localmasters) {
- if ($master->contains($cidr)) {
- $allocated{"$master"}++;
- }
- }
- }
-
- # initialize bigfree base points
- foreach my $lmaster (@localmasters) {
- $bigfree{"$lmaster"} = 128;
- }
-
- # Snag the free blocks.
- $sth = $ip_dbh->prepare("select * from freeblocks");
- $sth->execute();
- while (my @data = $sth->fetchrow_array()) {
- # cidr,maskbits,city
- # We only need the cidr
- my $cidr = new NetAddr::IP $data[0];
- foreach my $lmaster (@localmasters) {
- if ($lmaster->contains($cidr)) {
- $free{"$lmaster"}++;
- if ($cidr->masklen < $bigfree{"$lmaster"}) {
- $bigfree{"$lmaster"} = $cidr->masklen;
- }
- }
- # check for largest free block
- }
+ # Count the allocations
+ $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?");
+ foreach my $master (@localmasters) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$allocated{"$master"});
+ $sth->fetch();
+ }
+
+ # Count the free blocks.
+ $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ?");
+ foreach my $master (@localmasters) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$free{"$master"});
+ $sth->fetch();
+ }
+
+ # Get the size of the largest free block
+ $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? order by maskbits limit 1");
+ foreach my $master (@localmasters) {
+ $sth->execute("$master");
+ $sth->bind_columns(\$bigfree{"$master"});
+ $sth->fetch();
}
@@ -591,16 +566,13 @@
# Snag the free blocks.
my $count = 0;
- $sth = $ip_dbh->prepare("select * from freeblocks where routed='n' order by cidr");
+ $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr <<='$master' and ".
+ "routed='n' order by cidr");
$sth->execute();
while (my @data = $sth->fetchrow_array()) {
- # cidr,maskbits,city
- # We only need the cidr
my $cidr = new NetAddr::IP $data[0];
- if ($master->contains($cidr)) {
- my @row = ("$cidr", $cidr->range);
- printRow(\@row, 'color1' ) if($count%2==0);
- printRow(\@row, 'color2' ) if($count%2!=0);
- $count++;
- }
+ my @row = ("$cidr", $cidr->range);
+ printRow(\@row, 'color1' ) if($count%2==0);
+ printRow(\@row, 'color2' ) if($count%2!=0);
+ $count++;
}
@@ -628,14 +600,14 @@
qq($master ($data[2]):
\n);
- $sth = $ip_dbh->prepare("select * from allocations order by cidr");
+ startTable('CIDR allocation','Customer Location','Type','CustID','Description/Name');
+
+ # Snag the allocations for this block
+ $sth = $ip_dbh->prepare("select * from allocations where cidr <<= '$master' order by cidr");
$sth->execute();
-
- startTable('CIDR allocation','Customer Location','Type','CustID','Description/Name');
my $count=0;
while (my @data = $sth->fetchrow_array()) {
- # cidr,custid,type,city,description,notes,maskbits
+ # cidr,custid,type,city,description,notes,maskbits,circuitid
my $cidr = new NetAddr::IP $data[0];
- if (!$master->contains($cidr)) { next; }
# Clean up extra spaces that are borking things.
@@ -678,16 +650,14 @@
# unrouted free blocks, but it's better to let the database do the work if we can.
$count = 0;
- $sth = $ip_dbh->prepare("select * from freeblocks where routed='y' order by cidr");
+ $sth = $ip_dbh->prepare("select * from freeblocks where routed='y' and cidr <<= '$master' order by cidr");
$sth->execute();
while (my @data = $sth->fetchrow_array()) {
# cidr,maskbits,city
my $cidr = new NetAddr::IP $data[0];
- if ($master->contains($cidr)) {
- my @row = ("$cidr",
+ my @row = ("$cidr",
$cidr->range);
- printRow(\@row, 'color1') if ($count%2 == 0);
- printRow(\@row, 'color2') if ($count%2 != 0);
- $count++;
- }
+ printRow(\@row, 'color1') if ($count%2 == 0);
+ printRow(\@row, 'color2') if ($count%2 != 0);
+ $count++;
}
@@ -840,6 +810,4 @@
" ptype='$base' and (city='Sudbury' or city='North Bay')";
} else {
-## $city doesn't seem to get defined here.
-my $city; # Shut up Perl's "strict" scoping/usage check.
$sql = "select * from poolips where available='y' and".
" ptype='$base' and city='$webvar{pop}'";
@@ -889,4 +857,6 @@
" a set of smaller netblocks or a single smaller netblock.";
} else {
+##fixme
+# This section needs serious Pondering.
if ($webvar{alloctype} =~ /^[cdsmw]p$/) {
if (($webvar{city} !~ /^(Sudbury|North Bay)$/) && ($webvar{alloctype} eq 'dp')) {
@@ -896,5 +866,5 @@
$city = $webvar{city};
$failmsg = "No suitable free block found.
\nYou will have to route another".
- " superblock
\nfrom one of the master blocks in Sudbury or chose a smaller".
+ " superblock from one of the
\nmaster blocks in Sudbury or chose a smaller".
" block size for the pool.";
} else {