Changeset 157 for trunk/cgi-bin/IPDB.pm

Timestamp:

02/08/05 15:56:33 (19 years ago)

Author:

Kris Deugau

Message:

/trunk

Merge changes from /branches/sql-cleanup into mainline

• Merge was clean

File:

• trunk/cgi-bin/IPDB.pm (modified) (19 diffs)

trunk/cgi-bin/IPDB.pm

@@ -58 +58 @@
 
   # Initialize alloctypes hashes
-  $sth = $dbh->prepare("select * from alloctypes order by listorder");
+  $sth = $dbh->prepare("select type,listname,dispname,listorder from alloctypes order by listorder");
   $sth->execute;
   while (my @data = $sth->fetchrow_array) {
@@ -68 +68 @@
 
   # City and POP listings
-  $sth = $dbh->prepare("select * from cities order by city");
+  $sth = $dbh->prepare("select city,routing from cities order by city");
   $sth->execute;
   return (undef,$sth->errstr) if $sth->err;
@@ -79 +79 @@
 
   # Master block list
-  $sth = $dbh->prepare("select * from masterblocks order by cidr");
+  $sth = $dbh->prepare("select cidr from masterblocks order by cidr");
   $sth->execute;
   for (my $i=0; my @data = $sth->fetchrow_array(); $i++) {
@@ -120 +120 @@
 # Return here if we can't select.  Note that this indicates a
 # problem executing the select.
-  my $sth = $dbh->prepare('select cidr from masterblocks');
+  my $sth = $dbh->prepare("select cidr from masterblocks");
   $sth->execute();
   return (undef,$DBI::errstr) if ($sth->err);
@@ -154 +154 @@
   } else {
     # it connects, try a stmt.
-    my $sth = $dbh->prepare('select cidr from masterblocks');
+    my $sth = $dbh->prepare("select cidr from masterblocks");
     my $err = $sth->execute();
 
@@ -189 +189 @@
   local $dbh->{RaiseError} = 1; # step on our toes by accident.
 
-  if ($type =~ /^[cdsmw]i$/) {
+  if ($type =~ /^.i$/) {
     $msg = "Unable to assign static IP $cidr to $custid";
     eval {
-      # We'll just have to put up with the oddities caused by SQL (un)sort order
-      $sth = $dbh->prepare("select * from poolips where pool='$alloc_from'".
-        " and available='y' order by ip");
-      $sth->execute;
-
+      # We have to do this in two parts because otherwise we lose
+      # the ability to return the IP assigned.  Should that change,
+      # the commented SQL statement below may become usable.
 # update poolips set custid='$custid',city='$city',available='n',
 #       description='$desc',notes='$notes',circuitid='$circid'
 #       where ip=(select ip from poolips where pool='$alloc_from'
 #       and available='y' order by ip limit 1);
-##err Need better handling here;  what if there's no free IPs when this sub gets called?
+
+      $sth = $dbh->prepare("select ip from poolips where pool='$alloc_from'".
+        " and available='y' order by ip");
+      $sth->execute;
+
       my @data = $sth->fetchrow_array;
-      $cidr = $data[1];  # $cidr is already declared when we get here!
+      $cidr = $data[0];  # $cidr is already declared when we get here!
 
       $sth = $dbh->prepare("update poolips set custid='$custid',".
@@ -234 +236 @@
             " where cidr='$cidr'");
           $sth->execute;
-          $sth = $dbh->prepare("insert into routed values ('$cidr',".
-            $cidr->masklen.",'$city')");
+          $sth = $dbh->prepare("insert into routed (cidr,maskbits,city)".
+                " values ('$cidr',".$cidr->masklen.",'$city')");
           $sth->execute;
         } else {
@@ -242 +244 @@
           $sth->execute;
 
-          $sth = $dbh->prepare("insert into allocations values ('$cidr',".
-                "'$custid','$type','$city','$desc','$notes',".
+          $sth = $dbh->prepare("insert into allocations".
+                " (cidr,custid,type,city,description,notes,maskbits,circuitid)".
+                " values ('$cidr','$custid','$type','$city','$desc','$notes',".
                 $cidr->masklen.",'$circid')");
           $sth->execute;
 
           # And initialize the pool, if necessary
+          # PPPoE pools (currently dialup, DSL, and WiFi) get all IPs made available
+          # "DHCP" or "real-subnet" pools have the net, gw, and bcast IPs removed.
           if ($type =~ /^.p$/) {
-            $msg = "Could not initialize IPs in new $disp_alloctypes{$type} pool $cidr";
-            initPool($dbh,$cidr,$type,$city,($type eq 'dp' ? "all" : "normal"));
+            $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr";
+            my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"all");
+            die $rmsg if $code eq 'FAIL';
+          } elsif ($type =~ /^.d$/) {
+            $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr";
+            my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"normal");
+            die $rmsg if $code eq 'FAIL';
           }
 
@@ -258 +268 @@
       }; # end of eval
       if ($@) {
-        $msg = $@;
+        $msg .= ": ".$@;
         eval { $dbh->rollback; };
-        return ('FAIL',$@);
+        return ('FAIL',$msg);
       } else {
         return ('OK',"OK");
@@ -297 +307 @@
           # Insert the new freeblocks entries
           # Note that non-routed blocks are assigned to <NULL>
-          $sth = $dbh->prepare("insert into freeblocks values (?, ?, '<NULL>','n')");
+          # and use the default value for the routed column ('n')
+          $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city)".
+                " values (?, ?, '<NULL>')");
           foreach my $block (@newfreeblocks) {
             $sth->execute("$block", $block->masklen);
@@ -303 +315 @@
 
           # Insert the entry in the routed table
-          $sth = $dbh->prepare("insert into routed values ('$cidr',".
-            $cidr->masklen.",'$city')");
+          $sth = $dbh->prepare("insert into routed (cidr,maskbits,city)".
+                " values ('$cidr',".$cidr->masklen.",'$city')");
           $sth->execute;
           # Insert the (almost) same entry in the freeblocks table
-          $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".
-            $cidr->masklen.",'$city','y')");
+          $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
+                " values ('$cidr',".$cidr->masklen.",'$city','y')");
           $sth->execute;
 
@@ -314 +326 @@
 
           # Insert the new freeblocks entries
-          $sth = $dbh->prepare("insert into freeblocks values (?, ?, ".
-                "(select city from routed where cidr >>= '$cidr'),'y')");
+          $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
+                " values (?, ?, (select city from routed where cidr >>= '$cidr'),'y')");
           foreach my $block (@newfreeblocks) {
             $sth->execute("$block", $block->masklen);
@@ -321 +333 @@
 
           # Insert the allocations entry
-          $sth = $dbh->prepare("insert into allocations values ('$cidr',".
-                "'$custid','$type','$city','$desc','$notes',".$cidr->masklen.
-                ",'$circid')");
+          $sth = $dbh->prepare("insert into allocations (cidr,custid,type,city,".
+                "description,notes,maskbits,circuitid)".
+                " values ('$cidr','$custid','$type','$city','$desc','$notes',".
+                $cidr->masklen.",'$circid')");
           $sth->execute;
 
           # And initialize the pool, if necessary
+          # PPPoE pools (currently dialup, DSL, and WiFi) get all IPs made available
+          # "DHCP" or "real-subnet" pools have the net, gw, and bcast IPs removed.
           if ($type =~ /^.p$/) {
             $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr";
-            initPool($dbh,$cidr,$type,$city,($type eq 'dp' ? "all" : "normal"));
+            my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"all");
+            die $rmsg if $code eq 'FAIL';
+          } elsif ($type =~ /^.d$/) {
+            $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr";
+            my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"normal");
+            die $rmsg if $code eq 'FAIL';
           }
 
@@ -361 +381 @@
   my $pool = new NetAddr::IP $_[1];
 
-  my ($pooltype) = ($type =~ /^(.)p$/);
+##fixme Need to just replace 2nd char of type with i rather than capturing 1st char of type
+  $type =~ s/[pd]$/i/;
   my $sth;
-
-  # have to insert all pool IPs into poolips table as "unallocated".
-  $sth = $dbh->prepare("insert into poolips values ('$pool',".
-        " ?, '6750400', '$city', '$pooltype', 'y', '', '', '')");
-  my @poolip_list = $pool->hostenum;
-  if ($class eq 'all') { # (DSL-ish block - *all* IPs available
-    $sth->execute($pool->addr);
-    for (my $i=0; $i<=$#poolip_list; $i++) {
-      $sth->execute($poolip_list[$i]->addr);
-    }
-    $pool--;
-    $sth->execute($pool->addr);
-  } else { # (real netblock)
-    for (my $i=1; $i<=$#poolip_list; $i++) {
-      $sth->execute($poolip_list[$i]->addr);
-    }
+  my $msg;
+
+  # Trap errors so we can pass them back to the caller.  Even if the
+  # caller is only ever supposed to be local, and therefore already
+  # trapping errors.  >:(
+  local $dbh->{AutoCommit} = 0; # These need to be local so we don't
+  local $dbh->{RaiseError} = 1; # step on our toes by accident.
+
+  eval {
+    # have to insert all pool IPs into poolips table as "unallocated".
+    $sth = $dbh->prepare("insert into poolips (pool,ip,custid,city,type)".
+        " values ('$pool', ?, '6750400', '$city', '$type')");
+    my @poolip_list = $pool->hostenum;
+    if ($class eq 'all') { # (DSL-ish block - *all* IPs available
+      $sth->execute($pool->addr);
+      for (my $i=0; $i<=$#poolip_list; $i++) {
+        $sth->execute($poolip_list[$i]->addr);
+      }
+      $pool--;
+      $sth->execute($pool->addr);
+    } else { # (real netblock)
+      for (my $i=1; $i<=$#poolip_list; $i++) {
+        $sth->execute($poolip_list[$i]->addr);
+      }
+    }
+  };
+  if ($@) {
+    $msg = "'".$sth->errstr."'";
+    eval { $dbh->rollback; };
+    return ('FAIL',$msg);
+  } else {
+    return ('OK',"OK");
   }
 } # end initPool()
@@ -406 +443 @@
 
     eval {
-      $msg = "Unable to deallocate $type $cidr";
+      $msg = "Unable to deallocate $disp_alloctypes{$type} $cidr";
       $sth = $dbh->prepare("update poolips set custid='6750400',available='y',".
         "city=(select city from allocations where cidr >>= '$cidr'),".
@@ -455 +492 @@
         $sth->execute;
         # Set up query to start compacting free blocks.
-        $sth = $dbh->prepare("select * from freeblocks where ".
+        $sth = $dbh->prepare("select cidr from freeblocks where ".
                 "maskbits<=".$cidr->masklen." and routed='n' order by maskbits desc");
 
@@ -463 +500 @@
         $sth->execute;
         # Special case - delete pool IPs
-        if ($type =~ /^.p$/) {
+        if ($type =~ /^.[pd]$/) {
           # We have to delete the IPs from the pool listing.
           $sth = $dbh->prepare("delete from poolips where pool='$cidr'");
@@ -470 +507 @@
 
         # Set up query for compacting free blocks.
-        $sth = $dbh->prepare("select * from freeblocks where cidr <<= ".
+        $sth = $dbh->prepare("select cidr from freeblocks where cidr <<= ".
                 "(select cidr from routed where cidr >>= '$cidr') ".
                 " and maskbits<=".$cidr->masklen." and routed='y' order by maskbits desc");
@@ -513 +550 @@
       # insert "new" freeblocks entry
       if ($type eq 'rr') {
-        $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen.
-                ",'<NULL>','n')");
+        $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city)".
+                " values ('$cidr',".$cidr->masklen.",'<NULL>')");
       } else {
-        $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen.
+        $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
+                " values ('$cidr',".$cidr->masklen.
                 ",(select city from routed where cidr >>= '$cidr'),'y')");
       }