Changeset 524


Ignore:
Timestamp:
10/22/12 17:10:07 (11 years ago)
Author:
Kris Deugau
Message:

/trunk

Clean up and move SQL for showmaster to IPDB.pm. See #34.
Tweak template to use odd for row colors

Location:
trunk
Files:
3 edited

Legend:

Unmodified
Added
Removed
  • trunk/cgi-bin/IPDB.pm

    r523 r524  
    2727        &initIPDBGlobals &connectDB &finish &checkDBSanity
    2828        &addMaster
    29         &listSummary &listMaster &listRBlock
     29        &listSummary &listMaster &listRBlock &listFree
    3030        &allocateBlock &deleteBlock &getBlockData
    3131        &getNodeList
     
    3939                &initIPDBGlobals &connectDB &finish &checkDBSanity
    4040                &addMaster
    41                 &listSummary &listMaster &listRBlock
     41                &listSummary &listMaster &listRBlock &listFree
    4242                &allocateBlock &deleteBlock &getBlockData
    4343                &getNodeList
     
    345345
    346346
    347 # &listMaster &listRBlock
     347## IPDB::listMaster()
     348# Get list of routed blocks in the requested master
     349# Returns an arrayref to a list of hashrefs containing the routed block, POP/city the block is routed to,
     350# allocated count, free count, and largest free block masklength
     351sub listMaster {
     352  my $dbh = shift;
     353  my $master = shift;
     354
     355  my $rlist = $dbh->selectall_arrayref("SELECT cidr AS block,city FROM routed WHERE cidr <<= ? ORDER BY cidr",
     356        { Slice => {} }, ($master) );
     357
     358  foreach (@{$rlist}) {
     359    my ($acnt) = $dbh->selectrow_array("SELECT count(*) FROM allocations WHERE cidr <<= ?", undef, ($$_{block}));
     360    $$_{nsubs} = $acnt;
     361    my ($fcnt) = $dbh->selectrow_array("SELECT count(*) FROM freeblocks WHERE cidr <<= ?".
     362        " AND (routed='y' OR routed='n')", undef, ($$_{block}));
     363    $$_{nfree} = $fcnt;
     364    my ($bigfree) = $dbh->selectrow_array("SELECT maskbits FROM freeblocks WHERE cidr <<= ?".
     365        " AND (routed='y' OR routed='n') ORDER BY maskbits LIMIT 1", undef, ($$_{block}));
     366##fixme:  should find a way to do this without having to HTMLize the <>
     367    $bigfree = "/$bigfree" if $bigfree;
     368    $bigfree = '&lt;NONE&gt;' if !$bigfree;
     369    $$_{lfree} = $bigfree;
     370  }
     371  return $rlist;
     372} # end listMaster()
     373
     374
     375# &listRBlock
     376
     377
     378## IPDB::listFree()
     379# Gets a list of free blocks in the requested parent/master in both CIDR and range notation
     380# Takes a parent/master and an optional flag to look at routed or unrouted blocks, depending
     381# on whether the master is a direct master or a routed block
     382# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
     383sub listFree {
     384  my $dbh = shift;
     385  my $master = shift;
     386  my $routed = shift || 'y';
     387
     388  # do it this way so we can waste a little less time iterating
     389  my $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE cidr <<= ? AND routed = ? ORDER BY cidr");
     390  $sth->execute($master, $routed);
     391  my @flist;
     392  while (my ($cidr) = $sth->fetchrow_array()) {
     393    $cidr = new NetAddr::IP $cidr;
     394    my %row = (fblock => "$cidr", frange => $cidr->range);
     395    push @flist, \%row;
     396  }
     397  return \@flist;
     398}
    348399
    349400
  • trunk/cgi-bin/main.cgi

    r523 r524  
    233233
    234234  $page->param(master => $webvar{block});
    235 
    236   my %allocated;
    237   my %free;
    238   my %cities;
    239   my %bigfree;
    240 
    241   my $master = new NetAddr::IP $webvar{block};
    242   my @localmasters;
    243 
    244   # Fetch only the blocks relevant to this master
    245   $sth = $ip_dbh->prepare("select cidr,city from routed where cidr <<= '$master' order by cidr");
    246   $sth->execute();
    247 
    248   my $i=0;
    249   while (my @data = $sth->fetchrow_array()) {
    250     my $cidr = new NetAddr::IP $data[0];
    251     $localmasters[$i++] = $cidr;
    252     $free{"$cidr"} = 0;
    253     $allocated{"$cidr"} = 0;
    254     $bigfree{"$cidr"} = 128;
    255     # Retain the routing destination
    256     $cities{"$cidr"} = $data[1];
    257   }
    258 
    259   # Check if there were actually any blocks routed from this master
    260   if ($i > 0) {
    261 
    262     # Count the allocations
    263     $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?");
    264     foreach my $master (@localmasters) {
    265       $sth->execute("$master");
    266       $sth->bind_columns(\$allocated{"$master"});
    267       $sth->fetch();
    268     }
    269 
    270     # Count the free blocks.
    271     $sth = $ip_dbh->prepare("select count(*) from freeblocks where cidr <<= ? and ".
    272         "(routed='y' or routed='n')");
    273     foreach my $master (@localmasters) {
    274       $sth->execute("$master");
    275       $sth->bind_columns(\$free{"$master"});
    276       $sth->fetch();
    277     }
    278 
    279     # Get the size of the largest free block
    280     $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? and ".
    281         "(routed='y' or routed='n') order by maskbits limit 1");
    282     foreach my $master (@localmasters) {
    283       $sth->execute("$master");
    284       $sth->bind_columns(\$bigfree{"$master"});
    285       $sth->fetch();
    286     }
    287 
    288     my @routed;
    289     my $rowclass = 0;
    290     foreach my $master (@localmasters) {
    291       my %row = (
    292         rowclass => $rowclass++ % 2,
    293         block => "$master",
    294         city => $cities{"$master"},
    295         nsubs => $allocated{"$master"},
    296         nfree => $free{"$master"},
    297         lfree => ( ($bigfree{"$master"} eq 128) ? ("&lt;NONE&gt;") : ("/".$bigfree{"$master"}) )
    298         );
    299       push @routed, \%row;
    300     }
    301     $page->param(routedlist => \@routed);
    302 
    303   } # end check for existence of routed blocks in master
    304 
    305235  $page->param(delmaster => ($IPDBacl{$authuser} =~ /d/));
    306236
    307   # Snag the free blocks.
    308   my $count = 0;
    309   $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr <<='$master' and ".
    310         "routed='n' order by cidr");
    311   $sth->execute();
    312   my @unrouted;
    313   my $rowclass = 0;
    314   while (my @data = $sth->fetchrow_array()) {
    315     my $cidr = new NetAddr::IP $data[0];
    316     my %row = (
    317         rowclass => $rowclass++ % 2,
    318         fblock => "$cidr",
    319         frange => $cidr->range
    320         );
    321     push @unrouted, \%row;
    322   }
    323   $page->param(unrouted => \@unrouted);
    324 
     237  my $rlist = listMaster($ip_dbh, $webvar{block});
     238  $page->param(routedlist => $rlist);
     239
     240  my $flist = listFree($ip_dbh, $webvar{block}, 'n');
     241  $page->param(unrouted => $flist);
    325242} # showMaster
    326243
  • trunk/templates/showmaster.tmpl

    r517 r524  
    1313
    1414<TMPL_LOOP NAME=routedlist>
    15 <tr class="row<TMPL_VAR NAME=rowclass>">
     15<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
    1616<td><a href="<TMPL_VAR NAME=webpath>/cgi-bin/main.cgi?action=showrouted&amp;block=<TMPL_VAR NAME=block>"><TMPL_VAR NAME=block></a></td>
    1717<td><TMPL_VAR NAME=city></td>
     
    5353
    5454<TMPL_LOOP name=unrouted>
    55 <tr class="row<TMPL_VAR NAME=rowclass>">
     55<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
    5656<td><TMPL_VAR NAME=fblock></td>
    5757<td><TMPL_VAR NAME=frange></td>
Note: See TracChangeset for help on using the changeset viewer.