Changeset 154 for branches/sql-cleanup

Timestamp:

02/04/05 17:29:30 (20 years ago)

Author:

Kris Deugau

Message:

/branches/sql-cleanup

main.cgi:

• Removed commented legacy initialization code
• Tweaked all SQL except for one section that needs serious redesigning to tighten any further. Adjusted processing on data returned via fetchrow_array() to make sure it's correct.

File:

• branches/sql-cleanup/cgi-bin/main.cgi (modified) (10 diffs)

branches/sql-cleanup/cgi-bin/main.cgi

@@ -56 +56 @@
 cleanInput(\%webvar);
 
-# Stuff that gets loaded from the database
-#my @citylist;
-#my @poplist;
-#my %allocated; # Count for allocated blocks in a master block
-#my %free;      # Count for free blocks (routed and unrouted) in a master block
-#my %bigfree;   # Tracking largest free block in a master block
-#my %routed;    # Number of routed blocks in a master block
-
-# Slurp up the master block list - we need this several places
-# While we're at it, initialize the related hashes.
-#$sth = $ip_dbh->prepare("select * from masterblocks order by cidr");
-#$sth->execute;
-#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;
-#}
-
-
-
 
 #main()
@@ -109 +86 @@
 # Thus the "routed" flag.
 
-    $sth = $ip_dbh->prepare("insert into freeblocks values ('$webvar{cidr}',".
-        $cidr->masklen.",'<NULL>','n')");
+    $sth = $ip_dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
+        " values ('$webvar{cidr}',".$cidr->masklen.",'<NULL>','n')");
     $sth->execute;
 
@@ -493 +470 @@
 
   # Fetch only the blocks relevant to this master
-  $sth = $ip_dbh->prepare("select * from routed where cidr <<= '$master' order by cidr");
+  $sth = $ip_dbh->prepare("select cidr,city from routed where cidr <<= '$master' order by cidr");
   $sth->execute();
 
@@ -504 +481 @@
     $bigfree{"$cidr"} = 128;
     # Retain the routing destination
-    $routed{"$cidr"} = $data[2];
+    $routed{"$cidr"} = $data[1];
   }
 
@@ -596 +573 @@
   my $master = new NetAddr::IP $webvar{block};
 
-  $sth = $ip_dbh->prepare("select * from routed where cidr='$master'");
+  $sth = $ip_dbh->prepare("select city from routed where cidr='$master'");
   $sth->execute;
   my @data = $sth->fetchrow_array;
 
   print qq(<center><div class="heading">Summarizing allocated blocks for ).
-        qq($master ($data[2]):</div></center><br>\n);
+        qq($master ($data[0]):</div></center><br>\n);
 
   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 = $ip_dbh->prepare("select cidr,city,type,custid,description".
+        " from allocations where cidr <<= '$master' order by cidr");
   $sth->execute();
 
   my $count=0;
   while (my @data = $sth->fetchrow_array()) {
-    # cidr,custid,type,city,description,notes,maskbits,circuitid
+    # cidr,city,type,custid,description, as per the SELECT
     my $cidr = new NetAddr::IP $data[0];
 
     # Clean up extra spaces that are borking things.
-    $data[2] =~ s/\s+//g;
+#    $data[2] =~ s/\s+//g;
 
     my @row = ("<a href=\"/ip/cgi-bin/main.cgi?action=edit&block=$data[0]\">$data[0]</a>",
-        $data[3], $disp_alloctypes{$data[2]}, $data[1], $data[4]);
+        $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]);
     # If the allocation is a pool, allow listing of the IPs in the pool.
     if ($data[2] =~ /^.[pd]$/) {
@@ -653 +631 @@
   # 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' and cidr <<= '$master' order by cidr");
+  $sth = $ip_dbh->prepare("select cidr from freeblocks where routed='y' and cidr <<= '$master' order by cidr");
   $sth->execute();
   while (my @data = $sth->fetchrow_array()) {
@@ -701 +679 @@
 
   startTable('IP','Customer ID','Available?','Description','');
-  $sth = $ip_dbh->prepare("select * from poolips where pool='$webvar{pool}' order by ip");
+  $sth = $ip_dbh->prepare("select ip,custid,available,description,type".
+        " from poolips where pool='$webvar{pool}' order by ip");
   $sth->execute;
   my $count = 0;
   while (my @data = $sth->fetchrow_array) {
     # pool,ip,custid,city,ptype,available,notes,description,circuitid
-    # If desc is null, make it not null.  <g>
-    if ($data[7] eq '') {
-      $data[7] = '&nbsp;';
+    # ip,custid,available,description,type
+    # If desc is "null", make it not null.  <g>
+    if ($data[3] eq '') {
+      $data[3] = '&nbsp;';
     }
     # Some nice hairy Perl to decide whether to allow unassigning each IP
-    #   -> if $data[5] (aka poolips.available) == 'n' then we print the unassign link
+    #   -> if $data[2] (aka poolips.available) == 'n' then we print the unassign link
     #      else we print a blank space
-    my @row = ( qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[1]">$data[1]</a>),
-        $data[2],$data[5],$data[7],
-        ( ($data[5] eq 'n') ?
-          ("<a href=\"/ip/cgi-bin/main.cgi?action=delete&block=$data[1]&".
-           "alloctype=$data[4]i\">Unassign this IP</a>") :
+    my @row = ( qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[0]">$data[0]</a>),
+        $data[1],$data[2],$data[3],
+        ( ($data[2] eq 'n') ?
+          ("<a href=\"/ip/cgi-bin/main.cgi?action=delete&block=$data[0]&".
+           "alloctype=$data[4]\">Unassign this IP</a>") :
           ("&nbsp;") )
         );
@@ -814 +794 @@
     # ... aside from #^%#$%#@#^%^^!!!! legacy data.  GRRR.
     # Note that we want to retain the requested city to relate to customer info.
+##fixme This needs thought.
+##SELECT DISTINCT pool, Count(*) FROM poolips where available='y' GROUP BY pool;
     if ($base =~ /^[ds]$/) {
       $sql = "select * from poolips where available='y' and".
@@ -883 +865 @@
         }
         if ($webvar{allocfrom} ne '-') {
-          $sql = "select * from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
+          $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
                 " and cidr <<= '$webvar{allocfrom}' and routed='y' order by cidr,maskbits desc";
         } else {
-          $sql = "select * from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
+          $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
                 " and routed='y' order by cidr,maskbits desc";
         }
@@ -1143 +1125 @@
         "type='$webvar{alloctype}',circuitid='$webvar{circid}' where cidr='$webvar{block}'";
     }
-syslog "debug", $sql;
+    # Log the details of the change.
+    syslog "debug", $sql;
     $sth = $ip_dbh->prepare($sql);
     $sth->execute;