Changeset 524 for trunk

Timestamp:

10/22/12 17:10:07 (12 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:

• cgi-bin/IPDB.pm (modified) (3 diffs)
• cgi-bin/main.cgi (modified) (1 diff)
• templates/showmaster.tmpl (modified) (2 diffs)

trunk/cgi-bin/IPDB.pm

@@ -27 +27 @@
         &initIPDBGlobals &connectDB &finish &checkDBSanity
         &addMaster
-        &listSummary &listMaster &listRBlock
+        &listSummary &listMaster &listRBlock &listFree
         &allocateBlock &deleteBlock &getBlockData
         &getNodeList
@@ -39 +39 @@
                 &initIPDBGlobals &connectDB &finish &checkDBSanity
                 &addMaster
-                &listSummary &listMaster &listRBlock
+                &listSummary &listMaster &listRBlock &listFree
                 &allocateBlock &deleteBlock &getBlockData
                 &getNodeList
@@ -345 +345 @@
 
 
-# &listMaster &listRBlock
+## IPDB::listMaster()
+# Get list of routed blocks in the requested master
+# Returns an arrayref to a list of hashrefs containing the routed block, POP/city the block is routed to,
+# allocated count, free count, and largest free block masklength
+sub listMaster {
+  my $dbh = shift;
+  my $master = shift;
+
+  my $rlist = $dbh->selectall_arrayref("SELECT cidr AS block,city FROM routed WHERE cidr <<= ? ORDER BY cidr",
+        { Slice => {} }, ($master) );
+
+  foreach (@{$rlist}) {
+    my ($acnt) = $dbh->selectrow_array("SELECT count(*) FROM allocations WHERE cidr <<= ?", undef, ($$_{block}));
+    $$_{nsubs} = $acnt;
+    my ($fcnt) = $dbh->selectrow_array("SELECT count(*) FROM freeblocks WHERE cidr <<= ?".
+        " AND (routed='y' OR routed='n')", undef, ($$_{block}));
+    $$_{nfree} = $fcnt;
+    my ($bigfree) = $dbh->selectrow_array("SELECT maskbits FROM freeblocks WHERE cidr <<= ?".
+        " AND (routed='y' OR routed='n') ORDER BY maskbits LIMIT 1", undef, ($$_{block}));
+##fixme:  should find a way to do this without having to HTMLize the <>
+    $bigfree = "/$bigfree" if $bigfree;
+    $bigfree = '&lt;NONE&gt;' if !$bigfree;
+    $$_{lfree} = $bigfree;
+  }
+  return $rlist;
+} # end listMaster()
+
+
+# &listRBlock
+
+
+## IPDB::listFree()
+# Gets a list of free blocks in the requested parent/master in both CIDR and range notation
+# Takes a parent/master and an optional flag to look at routed or unrouted blocks, depending
+# on whether the master is a direct master or a routed block
+# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
+sub listFree {
+  my $dbh = shift;
+  my $master = shift;
+  my $routed = shift || 'y';
+
+  # do it this way so we can waste a little less time iterating
+  my $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE cidr <<= ? AND routed = ? ORDER BY cidr");
+  $sth->execute($master, $routed);
+  my @flist;
+  while (my ($cidr) = $sth->fetchrow_array()) {
+    $cidr = new NetAddr::IP $cidr;
+    my %row = (fblock => "$cidr", frange => $cidr->range);
+    push @flist, \%row;
+  }
+  return \@flist;
+}
 
 

trunk/cgi-bin/main.cgi

@@ -233 +233 @@
 
   $page->param(master => $webvar{block});
-
-  my %allocated;
-  my %free;
-  my %cities;
-  my %bigfree;
-
-  my $master = new NetAddr::IP $webvar{block};
-  my @localmasters;
-
-  # Fetch only the blocks relevant to this master
-  $sth = $ip_dbh->prepare("select cidr,city from routed where cidr <<= '$master' order by cidr");
-  $sth->execute();
-
-  my $i=0;
-  while (my @data = $sth->fetchrow_array()) {
-    my $cidr = new NetAddr::IP $data[0];
-    $localmasters[$i++] = $cidr;
-    $free{"$cidr"} = 0;
-    $allocated{"$cidr"} = 0;
-    $bigfree{"$cidr"} = 128;
-    # Retain the routing destination
-    $cities{"$cidr"} = $data[1];
-  }
-
-  # Check if there were actually any blocks routed from this master
-  if ($i > 0) {
-
-    # 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 <<= ? and ".
-        "(routed='y' or routed='n')");
-    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 <<= ? and ".
-        "(routed='y' or routed='n') order by maskbits limit 1");
-    foreach my $master (@localmasters) {
-      $sth->execute("$master");
-      $sth->bind_columns(\$bigfree{"$master"});
-      $sth->fetch();
-    }
-
-    my @routed;
-    my $rowclass = 0;
-    foreach my $master (@localmasters) {
-      my %row = (
-        rowclass => $rowclass++ % 2,
-        block => "$master",
-        city => $cities{"$master"},
-        nsubs => $allocated{"$master"},
-        nfree => $free{"$master"},
-        lfree => ( ($bigfree{"$master"} eq 128) ? ("&lt;NONE&gt;") : ("/".$bigfree{"$master"}) )
-        );
-      push @routed, \%row;
-    }
-    $page->param(routedlist => \@routed);
-
-  } # end check for existence of routed blocks in master
-
   $page->param(delmaster => ($IPDBacl{$authuser} =~ /d/));
 
-  # Snag the free blocks.
-  my $count = 0;
-  $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr <<='$master' and ".
-        "routed='n' order by cidr");
-  $sth->execute();
-  my @unrouted;
-  my $rowclass = 0;
-  while (my @data = $sth->fetchrow_array()) {
-    my $cidr = new NetAddr::IP $data[0];
-    my %row = (
-        rowclass => $rowclass++ % 2,
-        fblock => "$cidr",
-        frange => $cidr->range
-        );
-    push @unrouted, \%row;
-  }
-  $page->param(unrouted => \@unrouted);
-
+  my $rlist = listMaster($ip_dbh, $webvar{block});
+  $page->param(routedlist => $rlist);
+
+  my $flist = listFree($ip_dbh, $webvar{block}, 'n');
+  $page->param(unrouted => $flist);
 } # showMaster
 

trunk/templates/showmaster.tmpl

@@ -13 +13 @@
 
 <TMPL_LOOP NAME=routedlist>
-<tr class="row<TMPL_VAR NAME=rowclass>">
+<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
 <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>
 <td><TMPL_VAR NAME=city></td>
@@ -53 +53 @@
 
 <TMPL_LOOP name=unrouted>
-<tr class="row<TMPL_VAR NAME=rowclass>">
+<tr class="row<TMPL_IF __odd__>0<TMPL_ELSE>1</TMPL_IF>">
 <td><TMPL_VAR NAME=fblock></td>
 <td><TMPL_VAR NAME=frange></td>