Changeset 118 for trunk/cgi-bin/main.cgi


Ignore:
Timestamp:
01/06/05 15:03:33 (19 years ago)
Author:
Kris Deugau
Message:

/trunk

SQL cleanup:

  • Made liberal use of "<<=" operator and "where" for DBMS-layer CIDR comparisons
  • Made liberal use of "select count(*) from ... where ..." in place of much more cumbersome "select * from ..." constructs

General code cleanup:

  • Better SQL allowed removal of a number of "if $cidr->contains($block)" constructs
  • Removed some unnecessary code originally added to fit existing legacy code
  • Cleaned up comments (indenation consistency, notes on DB fields)
  • Added a few ##fixme tags where the intended function and execution aren't clear, or don't quite work correctly in some odd edge cases
  • Moved %allocated, %free, %routed, %bigfree hashes to IPDB.pm as globals
File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/cgi-bin/main.cgi

    r115 r118  
    407407
    408408# Initial display:  Show master blocks with total allocated subnets, total free subnets
    409 sub showSummary
    410 {
     409sub showSummary {
     410  # this is horrible-ugly-bad and will Go Away real soon now(TM)
    411411  print "Content-type: text/html\n\n";
    412412
    … …  
    419419  my %bigfree;
    420420
    421 # Snag the allocations.
    422 # I think it's too confusing to leave out internal allocations.
    423   $sth = $ip_dbh->prepare("select * from allocations");
    424   $sth->execute();
    425   while (my @data = $sth->fetchrow_array()) {
    426     # cidr,custid,type,city,description
    427     # We only need the cidr
    428     my $cidr = new NetAddr::IP $data[0];
    429     foreach my $master (@masterblocks) {
    430       if ($master->contains($cidr)) {
    431         $allocated{"$master"}++;
    432       }
    433     }
    434   }
    435 
    436 # Snag routed blocks
    437   $sth = $ip_dbh->prepare("select * from routed");
    438   $sth->execute();
    439   while (my @data = $sth->fetchrow_array()) {
    440     # cidr,maskbits,city
    441     # We only need the cidr
    442     my $cidr = new NetAddr::IP $data[0];
    443     foreach my $master (@masterblocks) {
    444       if ($master->contains($cidr)) {
    445         $routed{"$master"}++;
    446       }
    447     }
    448   }
    449 
    450 # Snag the free blocks.
    451   $sth = $ip_dbh->prepare("select * from freeblocks");
    452   $sth->execute();
    453   while (my @data = $sth->fetchrow_array()) {
    454     # cidr,maskbits,city
    455     # We only need the cidr
    456     my $cidr = new NetAddr::IP $data[0];
    457     foreach my $master (@masterblocks) {
    458       if ($master->contains($cidr)) {
    459         $free{"$master"}++;
    460         if ($cidr->masklen < $bigfree{"$master"}) { $bigfree{"$master"} = $cidr->masklen; }
    461       }
    462     }
    463   }
    464 
    465 # Print the data.
     421  # Count the allocations.
     422  $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?");
     423  foreach my $master (@masterblocks) {
     424    $sth->execute("$master");
     425    $sth->bind_columns(\$allocated{"$master"});
     426    $sth->fetch();
     427  }
     428
     429  # Count routed blocks
     430  $sth = $ip_dbh->prepare("select count(*) from routed where cidr <<= ?");
     431  foreach my $master (@masterblocks) {
     432    $sth->execute("$master");
     433    $sth->bind_columns(\$routed{"$master"});
     434    $sth->fetch();
     435  }
     436
     437  # Count the free blocks.
     438  $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ?");
     439  foreach my $master (@masterblocks) {
     440    $sth->execute("$master");
     441    $sth->bind_columns(\$free{"$master"});
     442    $sth->fetch();
     443  }
     444
     445  # Find the largest free block in each master
     446  $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? order by maskbits limit 1");
     447  foreach my $master (@masterblocks) {
     448    $sth->execute("$master");
     449    $sth->bind_columns(\$bigfree{"$master"});
     450    $sth->fetch();
     451  }
     452
     453  # Print the data.
    466454  my $count=0;
    467455  foreach my $master (@masterblocks) {
    … …  
    501489  my @localmasters;
    502490
    503   $sth = $ip_dbh->prepare("select * from routed order by cidr");
     491  # Fetch only the blocks relevant to this master
     492  $sth = $ip_dbh->prepare("select * from routed where cidr <<= '$master' order by cidr");
    504493  $sth->execute();
    505494
    … …  
    507496  while (my @data = $sth->fetchrow_array()) {
    508497    my $cidr = new NetAddr::IP $data[0];
    509     if ($master->contains($cidr)) {
    510       $localmasters[$i++] = $cidr;
    511       $free{"$cidr"} = 0;
    512       $allocated{"$cidr"} = 0;
     498    $localmasters[$i++] = $cidr;
     499    $free{"$cidr"} = 0;
     500    $allocated{"$cidr"} = 0;
     501    $bigfree{"$cidr"} = 128;
    513502    # Retain the routing destination
    514       $routed{"$cidr"} = $data[2];
    515     }
    516   }
    517 
    518 # Check if there were actually any blocks routed from this master
     503    $routed{"$cidr"} = $data[2];
     504  }
     505
     506  # Check if there were actually any blocks routed from this master
    519507  if ($i > 0) {
    520508    startTable('Routed block','Routed to','Allocated blocks',
    521509        'Free blocks','Largest free block');
    522510
    523   # Count the allocations
    524     $sth = $ip_dbh->prepare("select * from allocations");
    525     $sth->execute();
    526     while (my @data = $sth->fetchrow_array()) {
    527       # cidr,custid,type,city,description
    528       # We only need the cidr
    529       my $cidr = new NetAddr::IP $data[0];
    530       foreach my $master (@localmasters) {
    531         if ($master->contains($cidr)) {
    532           $allocated{"$master"}++;
    533         }
    534       }
    535     }
    536 
    537     # initialize bigfree base points
    538     foreach my $lmaster (@localmasters) {
    539       $bigfree{"$lmaster"} = 128;
    540     }
    541 
    542     # Snag the free blocks.
    543     $sth = $ip_dbh->prepare("select * from freeblocks");
    544     $sth->execute();
    545     while (my @data = $sth->fetchrow_array()) {
    546       # cidr,maskbits,city
    547       # We only need the cidr
    548       my $cidr = new NetAddr::IP $data[0];
    549       foreach my $lmaster (@localmasters) {
    550         if ($lmaster->contains($cidr)) {
    551           $free{"$lmaster"}++;
    552           if ($cidr->masklen < $bigfree{"$lmaster"}) {
    553             $bigfree{"$lmaster"} = $cidr->masklen;
    554           }
    555         }
    556         # check for largest free block
    557       }
     511    # Count the allocations
     512    $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?");
     513    foreach my $master (@localmasters) {
     514      $sth->execute("$master");
     515      $sth->bind_columns(\$allocated{"$master"});
     516      $sth->fetch();
     517    }
     518
     519    # Count the free blocks.
     520    $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ?");
     521    foreach my $master (@localmasters) {
     522      $sth->execute("$master");
     523      $sth->bind_columns(\$free{"$master"});
     524      $sth->fetch();
     525    }
     526
     527    # Get the size of the largest free block
     528    $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? order by maskbits limit 1");
     529    foreach my $master (@localmasters) {
     530      $sth->execute("$master");
     531      $sth->bind_columns(\$bigfree{"$master"});
     532      $sth->fetch();
    558533    }
    559534
    … …  
    591566  # Snag the free blocks.
    592567  my $count = 0;
    593   $sth = $ip_dbh->prepare("select * from freeblocks where routed='n' order by cidr");
     568  $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr <<='$master' and ".
     569        "routed='n' order by cidr");
    594570  $sth->execute();
    595571  while (my @data = $sth->fetchrow_array()) {
    596     # cidr,maskbits,city
    597     # We only need the cidr
    598572    my $cidr = new NetAddr::IP $data[0];
    599     if ($master->contains($cidr)) {
    600       my @row = ("$cidr", $cidr->range);
    601       printRow(\@row, 'color1' ) if($count%2==0);
    602       printRow(\@row, 'color2' ) if($count%2!=0);
    603       $count++;
    604     }
     573    my @row = ("$cidr", $cidr->range);
     574    printRow(\@row, 'color1' ) if($count%2==0);
     575    printRow(\@row, 'color2' ) if($count%2!=0);
     576    $count++;
    605577  }
    606578
    … …  
    628600        qq($master ($data[2]):</div></center><br>\n);
    629601
    630   $sth = $ip_dbh->prepare("select * from allocations order by cidr");
     602  startTable('CIDR allocation','Customer Location','Type','CustID','Description/Name');
     603
     604  # Snag the allocations for this block
     605  $sth = $ip_dbh->prepare("select * from allocations where cidr <<= '$master' order by cidr");
    631606  $sth->execute();
    632 
    633   startTable('CIDR allocation','Customer Location','Type','CustID','Description/Name');
    634607
    635608  my $count=0;
    636609  while (my @data = $sth->fetchrow_array()) {
    637     # cidr,custid,type,city,description,notes,maskbits
     610    # cidr,custid,type,city,description,notes,maskbits,circuitid
    638611    my $cidr = new NetAddr::IP $data[0];
    639     if (!$master->contains($cidr)) { next; }
    640612
    641613    # Clean up extra spaces that are borking things.
    … …  
    678650  # unrouted free blocks, but it's better to let the database do the work if we can.
    679651  $count = 0;
    680   $sth = $ip_dbh->prepare("select * from freeblocks where routed='y' order by cidr");
     652  $sth = $ip_dbh->prepare("select * from freeblocks where routed='y' and cidr <<= '$master' order by cidr");
    681653  $sth->execute();
    682654  while (my @data = $sth->fetchrow_array()) {
    683655    # cidr,maskbits,city
    684656    my $cidr = new NetAddr::IP $data[0];
    685     if ($master->contains($cidr)) {
    686       my @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=assign&block=$cidr\">$cidr</a>",
     657    my @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=assign&block=$cidr\">$cidr</a>",
    687658        $cidr->range);
    688       printRow(\@row, 'color1') if ($count%2 == 0);
    689       printRow(\@row, 'color2') if ($count%2 != 0);
    690       $count++;
    691     }
     659    printRow(\@row, 'color1') if ($count%2 == 0);
     660    printRow(\@row, 'color2') if ($count%2 != 0);
     661    $count++;
    692662  }
    693663
    … …  
    840810        " ptype='$base' and (city='Sudbury' or city='North Bay')";
    841811    } else {
    842 ## $city doesn't seem to get defined here.
    843 my $city;       # Shut up Perl's "strict" scoping/usage check.
    844812      $sql = "select * from poolips where available='y' and".
    845813        " ptype='$base' and city='$webvar{pop}'";
    … …  
    889857          " a set of smaller netblocks or a single smaller netblock.";
    890858      } else {
     859##fixme
     860# This section needs serious Pondering.
    891861        if ($webvar{alloctype} =~ /^[cdsmw]p$/) {
    892862          if (($webvar{city} !~ /^(Sudbury|North Bay)$/) && ($webvar{alloctype} eq 'dp')) {
    … …  
    896866          $city = $webvar{city};
    897867          $failmsg = "No suitable free block found.<br>\nYou will have to route another".
    898             " superblock <br>\nfrom one of the master blocks in Sudbury or chose a smaller".
     868            " superblock from one of the<br>\nmaster blocks in Sudbury or chose a smaller".
    899869            " block size for the pool.";
    900870        } else {
Note: See TracChangeset for help on using the changeset viewer.