Changeset 594 for branches/stable/cgi-bin

Timestamp:

05/15/13 16:17:00 (12 years ago)

Author:

Kris Deugau

Message:

/branches/stable

Merge SQL changes and other miscellaneous fixes from /trunk through r553.

Location:

branches/stable

Files:

• . (modified) (1 prop)
• cgi-bin/CustIDCK.pm (modified) (2 diffs)
• cgi-bin/IPDB.pm (modified) (12 diffs)
• cgi-bin/admin.cgi (modified) (10 diffs)
• cgi-bin/main.cgi (modified) (29 diffs)
• cgi-bin/search.cgi (modified) (5 diffs)

branches/stable/cgi-bin/CustIDCK.pm

@@ -34 +34 @@
   my $custid = shift;
 
-  return 1 if $custid =~ /^STAFF$/;
-  return 1 if $custid =~ /^5554242$/;  # just in case some later change might block this
-  return 1 if $custid =~ /^\d{7}$/;
-  return 1 if $custid =~ /^\d{10}$/;
+  # hardcoded "OK" custids.
+  return 1 if $custid =~ /^STAFF(?:-\d\d?)?$/;
+  return 1 if $custid =~ /^5554242(?:-\d\d?)?$/;  # just in case some later change might block this
+  return 1 if $custid =~ /^\d{7}(?:-\d\d?)?$/;
+  return 1 if $custid =~ /^\d{10}(?:-\d\d?)?$/;
+
+  # Force uppercase for now...
+  $custid =~ tr/a-z/A-Z/;
 
 # some example code for a database check
@@ -66 +70 @@
 
   # We should have a valid DB connection by now.
-  my $sth = $dbh->prepare("SELECT custid FROM custid WHERE custid = '$custid'");
-  $sth->execute;
+  
+  my $hr = $dbh->selectrow_hashref("SELECT custid FROM custid WHERE custid = ?", undef, ($custid) );
+  my $status = 0;
   if ($dbh->err) {
     $CustIDCK::Error = 1;
     $CustIDCK::ErrMsg = $dbh->errstr();
-    $sth->finish;
-    $dbh->disconnect;
-    return 0;
+  } else {
+    $status = 1 if ( $hr->{custid} );
   }
-  my $hr = $sth->fetchrow_hashref();
-  my $status = 0;
-  $status = 1 if ( $hr->{custid} );
-  $sth->finish;
   $dbh->disconnect;
   return $status;

branches/stable/cgi-bin/IPDB.pm

@@ -23 +23 @@
 @ISA            = qw(Exporter);
 @EXPORT_OK    = qw(
-        %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist @masterblocks
-        %allocated %free %routed %bigfree %IPDBacl %aclmsg
-        &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock &addMaster
-        &deleteBlock &getBlockData &mailNotify
+        %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist
+        %IPDBacl %aclmsg
+        &initIPDBGlobals &connectDB &finish &checkDBSanity
+        &addMaster &touchMaster
+        &listSummary &listMaster &listRBlock &listFree &listPool
+        &getMasterList &getTypeList &getPoolSelect &findAllocateFrom
+        &ipParent &subParent &blockParent &getRoutedCity
+        &allocateBlock &updateBlock &deleteBlock &getBlockData
+        &getNodeList &getNodeName &getNodeInfo
+        &mailNotify
         );
 
@@ -32 +38 @@
 %EXPORT_TAGS    = ( ALL => [qw(
                 %disp_alloctypes %list_alloctypes %def_custids @citylist @poplist
-                @masterblocks %allocated %free %routed %bigfree %IPDBacl %aclmsg
-                &initIPDBGlobals &connectDB &finish &checkDBSanity &allocateBlock
-                &addMaster &deleteBlock &getBlockData &mailNotify
+                %IPDBacl %aclmsg
+                &initIPDBGlobals &connectDB &finish &checkDBSanity
+                &addMaster &touchMaster
+                &listSummary &listMaster &listRBlock &listFree &listPool
+                &getMasterList &getTypeList &getPoolSelect &findAllocateFrom
+                &ipParent &subParent &blockParent &getRoutedCity
+                &allocateBlock &updateBlock &deleteBlock &getBlockData
+                &getNodeList &getNodeName &getNodeInfo
+                &mailNotify
                 )]
         );
@@ -46 +58 @@
 our @citylist;
 our @poplist;
-our @masterblocks;
-our %allocated;
-our %free;
-our %routed;
-our %bigfree;
 our %IPDBacl;
 
@@ -113 +120 @@
   }
 
-  # Master block list
-  $sth = $dbh->prepare("select cidr from masterblocks order by cidr");
-  $sth->execute;
-  return (undef,$sth->errstr) if $sth->err;
-  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;
-  }
-
   # Load ACL data.  Specific username checks are done at a different level.
   $sth = $dbh->prepare("select username,acl from users");
@@ -232 +226 @@
   # Wrap all the SQL in a transaction
   eval {
-    my $sth = $dbh->prepare("select count(*) from masterblocks where cidr <<= '$cidr'");
-    $sth->execute;
-    my @data = $sth->fetchrow_array;
-
-    if ($data[0] eq 0) {
+    my ($mexist) = $dbh->selectrow_array("SELECT cidr FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) );
+
+    if (!$mexist) {
       # First case - master is brand-spanking-new.
 ##fixme: rwhois should be globally-flagable somewhere, much like a number of other things
 ## maybe a db table called "config"?
-      $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
-      $sth->execute;
+      $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr,'y') );
 
 # Unrouted blocks aren't associated with a city (yet).  We don't rely on this
 # elsewhere though;  legacy data may have traps and pitfalls in it to break this.
 # Thus the "routed" flag.
-
-      $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
-        " values ('$cidr',".$cidr->masklen.",'<NULL>','n')");
-      $sth->execute;
+      $dbh->do("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,?,?)", undef,
+        ($cidr, $cidr->masklen, '<NULL>', 'n') );
 
       # If we get here, everything is happy.  Commit changes.
       $dbh->commit;
 
-    } # new master does not contain existing master(s)
+    } # done new master does not contain existing master(s)
     else {
 
       # collect the master(s) we're going to absorb, and snag the longest netmask while we're at it.
       my $smallmask = $cidr->masklen;
-      $sth = $dbh->prepare("select cidr as mask from masterblocks where cidr <<= '$cidr'");
-      $sth->execute;
+      my $sth = $dbh->prepare("SELECT cidr FROM masterblocks WHERE cidr <<= ?");
+      $sth->execute($cidr);
       my @cmasters;
       while (my @data = $sth->fetchrow_array) {
@@ -279 +268 @@
 
       # collect the unrouted free blocks within the new master
-      $sth = $dbh->prepare("select cidr from freeblocks where ".
-                "maskbits>=$smallmask and cidr <<= '$cidr' and routed='n'");
-      $sth->execute;
+      $sth = $dbh->prepare("SELECT cidr FROM freeblocks WHERE maskbits <= ? AND cidr <<= ? AND routed = 'n'");
+      $sth->execute($smallmask, $cidr);
       while (my @data = $sth->fetchrow_array) {
         my $freeblock = new NetAddr::IP $data[0];
@@ -293 +281 @@
 
       # freeblocks
-      $sth = $dbh->prepare("delete from freeblocks where cidr <<= ?");
-      my $sth2 = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed) values (?,?,'<NULL>','n')");
+      $sth = $dbh->prepare("DELETE FROM freeblocks WHERE cidr <<= ?");
+      my $sth2 = $dbh->prepare("INSERT INTO freeblocks (cidr,maskbits,city,routed) VALUES (?,?,'<NULL>','n')");
       foreach my $newblock (@blocklist) {
-        $sth->execute("$newblock");
-        $sth2->execute("$newblock", $newblock->masklen);
+        $sth->execute($newblock);
+        $sth2->execute($newblock, $newblock->masklen);
       }
 
       # master
-      $sth = $dbh->prepare("delete from masterblocks where cidr <<= '$cidr'");
-      $sth->execute;
-      $sth = $dbh->prepare("insert into masterblocks (cidr,rwhois) values ('$cidr','y')");
-      $sth->execute;
+      $dbh->do("DELETE FROM masterblocks WHERE cidr <<= ?", undef, ($cidr) );
+      $dbh->do("INSERT INTO masterblocks (cidr,rwhois) VALUES (?,?)", undef, ($cidr, 'y') );
 
       # *whew*  If we got here, we likely suceeded.
@@ -319 +305 @@
   }
 } # end addMaster
+
+
+## IPDB::touchMaster()
+# Update last-changed timestamp on a master block.
+sub touchMaster {
+  my $dbh = shift; 
+  my $master = shift;
+
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
+
+  eval {
+    $dbh->do("UPDATE masterblocks SET mtime=now() WHERE cidr = ?", undef, ($master));
+    $dbh->commit;
+  };
+
+  if ($@) {
+    my $msg = $@;
+    eval { $dbh->rollback; };
+    return ('FAIL',$msg);
+  }
+  return ('OK','OK');
+} # end touchMaster()
+
+
+## IPDB::listSummary()
+# Get summary list of all master blocks
+# Returns an arrayref to a list of hashrefs containing the master block, routed count,
+# allocated count, free count, and largest free block masklength
+sub listSummary {
+  my $dbh = shift;
+
+  my $mlist = $dbh->selectall_arrayref("SELECT cidr AS master FROM masterblocks ORDER BY cidr", { Slice => {} });
+
+  foreach (@{$mlist}) {
+    my ($rcnt) = $dbh->selectrow_array("SELECT count(*) FROM routed WHERE cidr <<= ?", undef, ($$_{master}));
+    $$_{routed} = $rcnt;
+    my ($acnt) = $dbh->selectrow_array("SELECT count(*) FROM allocations WHERE cidr <<= ?", undef, ($$_{master}));
+    $$_{allocated} = $acnt;
+    my ($fcnt) = $dbh->selectrow_array("SELECT count(*) FROM freeblocks WHERE cidr <<= ?".
+        " AND (routed='y' OR routed='n')", undef, ($$_{master}));
+    $$_{free} = $fcnt;
+    my ($bigfree) = $dbh->selectrow_array("SELECT maskbits FROM freeblocks WHERE cidr <<= ?".
+        " AND (routed='y' OR routed='n') ORDER BY maskbits LIMIT 1", undef, ($$_{master}));
+##fixme:  should find a way to do this without having to HTMLize the <>
+    $bigfree = "/$bigfree" if $bigfree;
+    $bigfree = '<NONE>' if !$bigfree;
+    $$_{bigfree} = $bigfree;
+  }
+  return $mlist;
+} # end listSummary()
+
+
+## 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 = '<NONE>' if !$bigfree;
+    $$_{lfree} = $bigfree;
+  }
+  return $rlist;
+} # end listMaster()
+
+
+## IPDB::listRBlock()
+# 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 listRBlock {
+  my $dbh = shift;
+  my $routed = shift;
+
+  # Snag the allocations for this block
+  my $sth = $dbh->prepare("SELECT cidr,city,type,custid,swip,description".
+        " FROM allocations WHERE cidr <<= ? ORDER BY cidr");
+  $sth->execute($routed);
+
+  # hack hack hack
+  # set up to flag swip=y records if they don't actually have supporting data in the customers table
+  my $custsth = $dbh->prepare("SELECT count(*) FROM customers WHERE custid = ?");
+
+  my @blocklist;
+  while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) {
+    $custsth->execute($custid);
+    my ($ncust) = $custsth->fetchrow_array();
+    my %row = (
+        block => $cidr,
+        city => $city,
+        type => $disp_alloctypes{$type},
+        custid => $custid,
+        swip => ($swip eq 'y' ? 'Yes' : 'No'),
+        partswip => ($swip eq 'y' && $ncust == 0 ? 1 : 0),
+        desc => $desc
+        );
+    $row{subblock} = ($type =~ /^.r$/);         # hmf.  wonder why these won't work in the hash declaration...
+    $row{listpool} = ($type =~ /^.[pd]$/);
+    push (@blocklist, \%row);
+  }
+  return \@blocklist;
+} # end 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 "routed or unrouted" flag that defaults to unrouted.
+# Returns an arrayref to a list of hashrefs containing the CIDR and range-notation blocks
+# Returns some extra flags in the hashrefs for routed blocks, since those can have several subtypes
+sub listFree {
+  my $dbh = shift;
+  my $master = shift;
+  my $routed = shift || 'n';
+
+  # do it this way so we can waste a little less time iterating
+  my $sth = $dbh->prepare("SELECT cidr,routed FROM freeblocks WHERE cidr <<= ? AND ".
+        ($routed eq 'n' ? '' : 'NOT')." routed = 'n' ORDER BY cidr");
+  $sth->execute($master);
+  my @flist;
+  while (my ($cidr,$rtype) = $sth->fetchrow_array()) {
+    $cidr = new NetAddr::IP $cidr;
+    my %row = (
+        fblock => "$cidr",
+        frange => $cidr->range,
+        );
+    if ($routed eq 'y') {
+      $row{subblock} = ($rtype ne 'y' && $rtype ne 'n');
+      $row{fbtype} = $rtype;
+    }
+    push @flist, \%row;
+  }
+  return \@flist;
+} # end listFree()
+
+
+## IPDB::listPool()
+#
+sub listPool {
+  my $dbh = shift;
+  my $pool = shift;
+
+  my $sth = $dbh->prepare("SELECT ip,custid,available,description,type".
+        " FROM poolips WHERE pool = ? ORDER BY ip");
+  $sth->execute($pool);
+  my @poolips;
+  while (my ($ip,$custid,$available,$desc,$type) = $sth->fetchrow_array) {
+    my %row = (
+        ip => $ip,
+        custid => $custid,
+        available => $available,
+        desc => $desc,
+        delme => $available eq 'n'
+        );
+    push @poolips, \%row;
+  }
+  return \@poolips;
+} # end listPool()
+
+
+## IPDB::getMasterList()
+# Get a list of master blocks, optionally including last-modified timestamps
+# Takes an optional flag to indicate whether to include timestamps;
+#  'm' includes ctime, all others (suggest 'c') do not.
+# Returns an arrayref to a list of hashrefs
+sub getMasterList {
+  my $dbh = shift;
+  my $stampme = shift || 'm';   # optional but should be set by caller for clarity
+
+  my $mlist = $dbh->selectall_arrayref("SELECT cidr AS master".($stampme eq 'm' ? ',mtime' : '').
+        " FROM masterblocks ORDER BY cidr", { Slice => {} });
+  return $mlist;
+} # end getMasterList()
+
+
+## IPDB::getTypeList()
+# Get an alloctype/description pair list suitable for dropdowns
+# Takes a flag to determine which general groups of types are returned
+# Returns an reference to an array of hashrefs
+sub getTypeList {
+  my $dbh = shift;
+  my $tgroup = shift || 'a';    # technically optional, like this, but should
+                                # really be specified in the call for clarity
+  my $tlist;
+  if ($tgroup eq 'p') {
+    # grouping 'p' - primary allocation types.  These include static IP pools (_d and _p),
+    # dynamic-allocation ranges (_e), containers (_c), and the "miscellaneous" cn, in, and en types.
+    $tlist = $dbh->selectall_arrayref("SELECT type,listname FROM alloctypes WHERE listorder < 500 ".
+        "AND type NOT LIKE '_i' AND type NOT LIKE '_r' ORDER BY listorder", { Slice => {} });
+  } elsif ($tgroup eq 'c') {
+    # grouping 'c' - contained types.  These include all static IPs and all _r types.
+    $tlist = $dbh->selectall_arrayref("SELECT type,listname FROM alloctypes WHERE listorder <= 500 ".
+        " AND (type LIKE '_i' OR type LIKE '_r') ORDER BY listorder", { Slice => {} });
+  } else {
+    # grouping 'a' - all standard allocation types.  This includes everything
+    # but mm (present only as a formality).  Make this the default.
+    $tlist = $dbh->selectall_arrayref("SELECT type,listname FROM alloctypes WHERE listorder <= 500 ".
+        " ORDER BY listorder", { Slice => {} });
+  }
+  return $tlist;
+}
+
+
+## IPDB::getPoolSelect()
+# Get a list of pools matching the passed city and type that have 1 or more free IPs
+# Returns an arrayref to a list of hashrefs
+sub getPoolSelect {
+  my $dbh = shift;
+  my $iptype = shift;
+  my $pcity = shift;
+
+  my ($ptype) = ($iptype =~ /^(.)i$/);
+  return if !$ptype;
+  $ptype .= '_';
+
+  my $plist = $dbh->selectall_arrayref(
+        "SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool) AS poolcit, ".
+        "poolips.pool AS poolblock, COUNT(*) AS poolfree FROM poolips,allocations ".
+        "WHERE poolips.available='y' AND poolips.pool=allocations.cidr ".
+        "AND allocations.city = ? AND poolips.type LIKE ? ".
+        "GROUP BY pool", { Slice => {} }, ($pcity, $ptype) );
+  return $plist;
+} # end getPoolSelect()
+
+
+## IPDB::findAllocateFrom()
+# Find free block to add a new allocation from.  (CIDR block version of pool select above, more or less)
+# Takes
+#  - mask length
+#  - allocation type
+#  - POP city "parent"
+#  - optional master-block restriction
+#  - optional flag to allow automatic pick-from-private-network-ranges
+# Returns a string with the first CIDR block matching the criteria, if any
+sub findAllocateFrom {
+  my $dbh = shift;
+  my $maskbits = shift;
+  my $type = shift;
+  my $city = shift;
+  my $pop = shift;
+  my %optargs = @_;
+
+  my $failmsg = "No suitable free block found\n";
+
+## Set up the SQL to find out what freeblock we can (probably) use for an allocation.
+## Very large systems will require development of a reserve system (possibly an extension
+## of the reserve-for-expansion concept in https://secure.deepnet.cx/trac/ipdb/ticket/24?)
+## Also populate a value list for the DBI call.
+
+  my @vallist = ($maskbits, ($type eq 'rm' ? 'n' : ($type =~ /^(.)r$/ ? "$1" : 'y')) );
+  my $sql = "SELECT cidr FROM freeblocks WHERE maskbits <= ? AND routed = ?";
+
+  # for PPP(oE) and container types, the POP city is the one attached to the pool.
+  # individual allocations get listed with the customer city site.
+  ##fixme:  chain cities to align roughly with a full layer-2 node graph
+  $city = $pop if $type !~ /^.[pc]$/;
+  if ($type ne 'rm' && $city) {
+    $sql .= " AND city = ?";
+    push @vallist, $city;
+  }
+  # Allow specifying an arbitrary full block, instead of a master
+  if ($optargs{gimme}) {
+    $sql .= " AND cidr >>= ?";
+    push @vallist, $optargs{gimme};
+  }
+  # if a specific master was requested, allow the requestor to self->shoot(foot)
+  if ($optargs{master} && $optargs{master} ne '-') {
+    $sql .= " AND cidr <<= ?" if $optargs{master} ne '-';
+    push @vallist, $optargs{master};
+  } else {
+    # if a specific master was NOT requested, filter out the RFC 1918 private networks
+    if (!$optargs{allowpriv}) {
+      $sql .= " AND NOT (cidr <<= '192.168.0.0/16' OR cidr <<= '10.0.0.0/8' OR cidr <<= '172.16.0.0/12')";
+    }
+  }
+  # Sorting and limiting, since we don't (currently) care to provide a selection of
+  # blocks to carve up.  This preserves something resembling optimal usage of the IP
+  # space by forcing contiguous allocations and free blocks as much as possible.
+  $sql .= " ORDER BY maskbits DESC,cidr LIMIT 1";
+
+  my ($fbfound) = $dbh->selectrow_array($sql, undef, @vallist);
+  return $fbfound;
+} # end findAllocateFrom()
+
+
+## IPDB::ipParent()
+# Get an IP's parent pool's details
+# Takes a database handle and IP
+# Returns a hashref to the parent pool block, if any
+sub ipParent {
+  my $dbh = shift;
+  my $block = shift;
+
+  my $pinfo = $dbh->selectrow_hashref("SELECT cidr,custid,type,city,description FROM allocations".
+        " WHERE cidr >>= ?", undef, ($block) );
+  return $pinfo;
+} # end ipParent()
+
+
+## IPDB::subParent()
+# Get a block's parent's details
+# Takes a database handle and CIDR block
+# Returns a hashref to the parent container block, if any
+sub subParent {
+  my $dbh = shift;
+  my $block = shift;
+
+  my $pinfo = $dbh->selectrow_hashref("SELECT cidr,custid,type,city,description FROM allocations".
+        " WHERE cidr >>= ?", undef, ($block) );
+  return $pinfo;
+} # end subParent()
+
+
+## IPDB::blockParent()
+# Get a block's parent's details
+# Takes a database handle and CIDR block
+# Returns a hashref to the parent container block, if any
+sub blockParent {
+  my $dbh = shift;
+  my $block = shift;
+
+  my $pinfo = $dbh->selectrow_hashref("SELECT cidr,city FROM routed".
+        " WHERE cidr >>= ?", undef, ($block) );
+  return $pinfo;
+} # end blockParent()
+
+
+## IPDB::getRoutedCity()
+# Get the city for a routed block.
+sub getRoutedCity {
+  my $dbh = shift;
+  my $block = shift;
+
+  my ($rcity) = $dbh->selectrow_array("SELECT city FROM routed WHERE cidr = ?", undef, ($block) );
+  return $rcity;
+} # end getRoutedCity()
 
 
@@ -362 +699 @@
 #       and available='y' order by ip limit 1);
 
-# If no specific IP was requested, pick the next available one from the pool.
-      if (!$cidr) {
-        $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[0];  # $cidr is already declared when we get here!
-      }
-
-      $sth = $dbh->prepare("update poolips set custid=?,city=?,".
-        "available='n',description=?,notes=?,circuitid=?,privdata=?".
-        " where ip=?");
-      $sth->execute($custid, $city, $desc, $notes, $circid, $privdata, "$cidr");
+      if ($cidr) {
+        my ($isavail) = $dbh->selectrow_array("SELECT available FROM poolips WHERE ip=?", undef, ($cidr) );
+        if ($isavail eq 'n') {
+          die "IP already allocated.  Deallocate and reallocate, or update the entry\n";
+        }
+        if (!$isavail) {
+          die "IP is not in an IP pool.\n";
+        }
+      } else {
+        ($cidr) = $dbh->selectrow_array("SELECT ip FROM poolips WHERE pool=? AND available='y' ORDER BY ip",
+                undef, ($alloc_from) );
+      }
+      $dbh->do("UPDATE poolips SET custid=?,city=?,available='n',description=?,notes=?,circuitid=?,privdata=? ".
+        "WHERE ip=?", undef, ($custid, $city, $desc, $notes, $circid, $privdata, $cidr) );
+
 # node hack
       if ($nodeid && $nodeid ne '') {
@@ -382 +720 @@
       }
 # end node hack
+
       $dbh->commit;
     };
     if ($@) {
-      $msg .= ": '".$sth->errstr."'";
+      $msg .= ": $@";
       eval { $dbh->rollback; };
       return ('FAIL',$msg);
@@ -634 +973 @@
 
 
+## IPDB::updateBlock()
+# Update an allocation
+# Takes all allocation fields in a hash
+sub updateBlock {
+  my $dbh = shift;
+  my %args = @_;
+
+  return ('FAIL', 'Missing block to update') if !$args{block};
+
+  # do it all in a transaction
+  local $dbh->{AutoCommit} = 0;
+  local $dbh->{RaiseError} = 1;
+
+  my @fieldlist;
+  my @vallist;
+  foreach ('custid', 'city', 'description', 'notes', 'circuitid', 'privdata') {
+    if ($args{$_}) {
+      push @fieldlist, $_;
+      push @vallist, $args{$_};
+    }
+  }
+
+  my $updtable = 'allocations';
+  my $keyfield = 'cidr';
+  if ($args{type} =~ /^(.)i$/) {
+    $updtable = 'poolips';
+    $keyfield = 'ip';
+  } else {
+## fixme:  there's got to be a better way...
+    if ($args{swip}) {
+      if ($args{swip} eq 'on' || $args{swip} eq '1' || $args{swip} eq 'y') {
+        $args{swip} = 'y';
+      } else {
+        $args{swip} = 'n';
+      }
+    }
+    foreach ('type', 'swip') {
+      if ($args{$_}) {
+        push @fieldlist, $_;
+        push @vallist, $args{$_};
+      }
+    }
+  }
+
+  return ('FAIL', 'No fields to update') if !@fieldlist;
+
+  push @vallist, $args{block};
+  my $sql = "UPDATE $updtable SET ";
+  $sql .= join " = ?, ", @fieldlist;
+  $sql .= " = ? WHERE $keyfield = ?";
+
+  eval {
+    # do the update
+    $dbh->do($sql, undef, @vallist);
+
+    if ($args{node}) {
+      # done with delete/insert so we don't have to worry about funkyness updating a node ref that isn't there
+      $dbh->do("DELETE FROM noderef WHERE block = ?", undef, ($args{block}) );
+      $dbh->do("INSERT INTO noderef (block,node_id) VALUES (?,?)", undef, ($args{block}, $args{node}) );
+    }
+
+    $dbh->commit;
+  };
+  if ($@) {
+    my $msg = $@;
+    $dbh->rollback;
+    return ('FAIL', $msg);
+  }
+  return 0;
+} # end updateBlock()
+
+
 ## IPDB::deleteBlock()
 # Removes an allocation from the database, including deleting IPs
@@ -843 +1254 @@
 
 ## IPDB::getBlockData()
-# Return custid, type, city, and description for a block
+# Get CIDR or IP, custid, type, city, circuit ID, description, notes, modification time, private/restricted 
+# data, for a CIDR block or pool IP
+# Also returns SWIP status flag for CIDR blocks
+# Takes the block/IP to look up
+# Returns an arrayref to a list of hashrefs
 sub getBlockData {
   my $dbh = shift;
   my $block = shift;
 
-  my $sth = $dbh->prepare("select cidr,custid,type,city,description from searchme".
-        " where cidr='$block'");
-  $sth->execute();
-  return $sth->fetchrow_array();
+  my $cidr = new NetAddr::IP $block;
+
+  my $keycol = 'cidr';
+  my $blocktable = 'allocations';
+  my $poolip = 0;
+
+  # Pool IP and IPv6 check all in one!  Still needs to be tightened
+  # up a little for the as-yet-unhandled case of IPv6 IP pools
+  if ($cidr->bits == 32 && $cidr->masklen == 32) {
+    $poolip = 1;
+    $keycol = 'ip';
+    $blocktable = 'poolips';
+  }
+  my $binfo = $dbh->selectrow_hashref("SELECT $keycol AS block, custid, type, city, circuitid, description,".
+        " notes, modifystamp AS lastmod, privdata".($poolip ? '' : ', swip')." FROM $blocktable".
+        " WHERE $keycol = ?", undef, ($block) );
+  return $binfo;
 } # end getBlockData()
+
+
+## IPDB::getNodeList()
+# Gets a list of node ID+name pairs as an arrayref to a list of hashrefs
+sub getNodeList {
+  my $dbh = shift;
+  
+  my $ret = $dbh->selectall_arrayref("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id",
+        { Slice => {} });
+  return $ret;
+} # end getNodeList()
+
+
+## IPDB::getNodeName()
+# Get node name from the ID
+sub getNodeName {
+  my $dbh = shift;
+  my $nid = shift;
+
+  my ($nname) = $dbh->selectrow_array("SELECT node_name FROM nodes WHERE node_id = ?", undef, ($nid) );
+  return $nname;
+} # end getNodeName()
+
+
+## IPDB::getNodeInfo()
+# Get node name and ID associated with a block
+sub getNodeInfo {
+  my $dbh = shift;
+  my $block = shift;
+
+  my ($nid, $nname) = $dbh->selectrow_array("SELECT nodes.node_id,node_name FROM nodes INNER JOIN noderef".
+        " ON nodes.node_id=noderef.node_id WHERE noderef.block = ?", undef, ($block) );
+  return ($nid, $nname);
+} # end getNodeInfo()
 
 

branches/stable/cgi-bin/admin.cgi

@@ -69 +69 @@
 }
 
+if(!defined($webvar{action})) {
+  $webvar{action} = "main";   #shuts up the warnings.
+}
+
 # handle DB error output
 if ($webvar{action} eq 'dberr') {
@@ -88 +92 @@
 my $header = HTML::Template->new(filename => "admin/header.tmpl");
 
-if(!defined($webvar{action})) {
-  $webvar{action} = "main";   #shuts up the warnings.
-}
-
 my $page;
 if (-e "$ENV{HTML_TEMPLATE_ROOT}/admin/$webvar{action}.tmpl") {
@@ -103 +103 @@
   $header->param(mainpage => 1);
 
-  $sth = $ip_dbh->prepare("select type,listname from alloctypes where listorder < 900 order by listorder");
-  $sth->execute;
-
-  my @typelist;
-  my $count = 0;
-  while (my ($type,$listname) = $sth->fetchrow_array) {
-    my %row = (
-        selected => $count++,
-        type => $type,
-        dispname => $listname
-        );
-    push @typelist, \%row;
-  }
-  $page->param(typelist => \@typelist);
-
-  my @masterlist;
-  $sth = $ip_dbh->prepare("select cidr,mtime from masterblocks order by cidr");
-  $sth->execute;
-  while (my ($cidr,$mtime) = $sth->fetchrow_array) {
-    my %row = (
-        master => $cidr,
-        masterdate => $mtime
-        );
-    push @masterlist, \%row;
-  }
-  $page->param(masterlist => \@masterlist);
-
+  my $tlist = getTypeList($ip_dbh, 'a');
+  $tlist->[0]->{sel} = 1;
+  $page->param(typelist => $tlist);
+
+  my $mlist = getMasterList($ip_dbh, 'm');
+  $page->param(masterlist => $mlist);
 }
 
@@ -136 +115 @@
 elsif ($webvar{action} eq 'alloc') {
 
-  if ($webvar{cidr} !~ /^\s*(\d{1,3}\.){3}\d{1,3}(\/\d{2})?\s*$/) {
+  my $cidr = new NetAddr::IP $webvar{cidr};
+  if (!$cidr || "$cidr" =~ /^0/) {
     $page->param(errmsg => "Can't allocate something that's not a netblock/ip");
     goto ERRJUMP;
   }
 
-  $sth = $ip_dbh->prepare("select def_custid from alloctypes where type='$webvar{alloctype}'");
-  $sth->execute;
-  my @data = $sth->fetchrow_array;
-  my $custid = $data[0];
+  my $custid = $def_custids{$webvar{alloctype}};
   if ($custid eq '') {
-    if ($webvar{custid} !~ /^(?:\d{10}|\d{7}|STAFF)(?:-\d\d?)?$/) {
-      # Force uppercase for now...
-      $webvar{custid} =~ tr/a-z/A-Z/;
-      # Crosscheck with billing.
-      my $status = CustIDCK->custid_exist($webvar{custid});
-      if ($CustIDCK::Error) {
-        $page->param(errmsg => "Error verifying customer ID: ".$CustIDCK::ErrMsg);
-        goto ERRJUMP;
-      }
-      if (!$status) {
-        $page->param(errmsg => "Customer ID not valid.  Make sure the Customer ID ".
-          "is correct.<br>\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ".
-          "non-customer assignments.");
-        goto ERRJUMP;
-      }
+    # Crosscheck with billing.
+    my $status = CustIDCK->custid_exist($webvar{custid});
+    if ($CustIDCK::Error) {
+      $page->param(errmsg => "Error verifying customer ID: ".$CustIDCK::ErrMsg);
+      goto ERRJUMP;
+    }
+    if (!$status) {
+      $page->param(errmsg => "Customer ID not valid.  Make sure the Customer ID ".
+        "is correct.<br>\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ".
+        "non-customer assignments.");
+      goto ERRJUMP;
     }
     # Type that doesn't have a default custid
@@ -166 +139 @@
   }
 
-  my $cidr = new NetAddr::IP $webvar{cidr};
-  my @data;
+  my $maskbits = $cidr->masklen;
+  my $fbtmp = findAllocateFrom($ip_dbh, $maskbits, $webvar{alloctype}, '','',
+        (gimme => "$cidr", allowpriv => 1));
+
   if ($webvar{alloctype} eq 'rm') {
-    $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr >>='$cidr' and routed='n'");
-    $sth->execute;
-    @data = $sth->fetchrow_array;
-# User deserves errors if user can't be bothered to find the free block first.
-    if (!$data[0]) {
+    if (!$fbtmp) {
       $page->param(errmsg => "Can't allocate from outside a free block!!");
       goto ERRJUMP;
     }
   } elsif ($webvar{alloctype} =~ /^(.)i$/) {
-    $sth = $ip_dbh->prepare("select cidr from allocations where cidr >>='$cidr' and (type like '_d' or type like '_p')");
-    $sth->execute;
-    @data = $sth->fetchrow_array;
-# User deserves errors if user can't be bothered to find the pool and a free IP first.
-    if (!$data[0]) {
+    my $iptype = $1;
+    my $ptmp = ipParent($ip_dbh, "$cidr");
+    if ($ptmp->{type} =~ /^(.)[dp]$/) {
+      my $newiptype = "$1i";
+      $fbtmp = $ptmp->{cidr};
+      if ($ptmp->{type} !~ /^$iptype./) {
+        $page->param(warnmsg => "Warning:  Allocating IP as '".$disp_alloctypes{$newiptype}."' instead of '".
+                $disp_alloctypes{$webvar{alloctype}}."' to match pool $fbtmp\n");
+        $webvar{alloctype} = $newiptype;
+      }
+    }
+    if (!$fbtmp) {
       $page->param(errmsg => "Can't allocate static IP from outside a pool!!");
       goto ERRJUMP;
     }
   } else {
-    $sth = $ip_dbh->prepare("select cidr from freeblocks where cidr >>='$cidr' and not (routed='n')");
-    $sth->execute;
-    @data = $sth->fetchrow_array;
-# User deserves errors if user can't be bothered to find the free block first.
-    if (!$data[0]) {
+    if (!$fbtmp) {
       $page->param(errmsg => "Can't allocate from outside a routed block!!");
       goto ERRJUMP;
@@ -197 +171 @@
   }
 
-  my $alloc_from = new NetAddr::IP $data[0];
-  $sth->finish;
+  my $alloc_from = new NetAddr::IP $fbtmp;
 
   my @cities;
@@ -226 +199 @@
     $page->param(locerr => "Invalid customer location!  Go back and select customer's location.");
     goto ERRJUMP;
-  } else {
-    if ($webvar{alloctype} =~ /^.i$/) {
-      $sth = $ip_dbh->prepare("update poolips set available='n', custid='$webvar{custid}', ".
-        "city='$webvar{city}', description='$webvar{desc}', notes='$webvar{notes}' ".
-        "where ip='$webvar{cidr}'");
-      $sth->execute;
-      if ($sth->err) {
-        $page->param(errmsg => $sth->errstr);
-        syslog "err", "($authuser) Allocation of '$webvar{cidr}' to '$webvar{custid}' as ".
-                "'$webvar{alloctype}' failed: '".$sth->errstr."'";
-      } else {
-        syslog "notice", "$authuser allocated '$webvar{cidr}' to '$webvar{custid}' as ".
-                "'$webvar{alloctype}'";
-        mailNotify($ip_dbh, "a$webvar{alloctype}", "ADDED: $disp_alloctypes{$webvar{alloctype}} allocation",
-          "$disp_alloctypes{$webvar{alloctype}} $webvar{cidr} allocated to customer $webvar{custid}\n".
-          "Description: $webvar{desc}\n\nAllocated by: $authuser\n");
-      }
-    } else {
-      my ($retcode,$msg) = allocateBlock($ip_dbh, $webvar{cidr}, $webvar{alloc_from},
+  }
+
+  my ($retcode,$msg) = allocateBlock($ip_dbh, $webvar{cidr}, $webvar{alloc_from},
         $webvar{custid}, $webvar{alloctype}, $webvar{city}, $webvar{desc}, $webvar{notes},
         $webvar{circid});
-      if ($retcode eq 'OK') {
-        syslog "notice", "$authuser allocated '$webvar{cidr}' to '$webvar{custid}' as ".
-                "'$webvar{alloctype}'";
-      } else {
-        $page->param(errmsg => $msg);
-        syslog "err", "($authuser) Allocation of '$webvar{cidr}' to '$webvar{custid}' as ".
-                "'$webvar{alloctype}' failed: '$msg'";
-      }
-    } # static IP vs netblock
-
-  } # done city check
+  if ($retcode eq 'OK') {
+    syslog "notice", "$authuser allocated '$webvar{cidr}' to '$webvar{custid}' as ".
+        "'$webvar{alloctype}'";
+    if ($webvar{alloctype} =~ /^.i$/) {
+      mailNotify($ip_dbh, "a$webvar{alloctype}", "ADDED: $disp_alloctypes{$webvar{alloctype}} allocation",
+        "$disp_alloctypes{$webvar{alloctype}} $webvar{cidr} allocated to customer $webvar{custid}\n".
+        "Description: $webvar{desc}\n\nAllocated by: $authuser\n");
+    }
+  } else {
+    $page->param(errmsg => $msg);
+    syslog "err", "($authuser) Allocation of '$webvar{cidr}' to '$webvar{custid}' as ".
+        "'$webvar{alloctype}' failed: '$msg'";
+  }
 
 } elsif ($webvar{action} eq 'alloctweak') {
@@ -270 +229 @@
 } elsif ($webvar{action} eq 'touch') {
 
-  $page->param(master => $webvar{whichmaster});
-  $sth = $ip_dbh->prepare("update masterblocks set mtime=now() where cidr='$webvar{whichmaster}'");
-  $sth->execute;
-  if ($sth->err) {
-    $page->param(errmsg => $sth->errstr);
-  }
+  my ($code,$msg) = touchMaster($ip_dbh, $webvar{whichmaster});
+  $page->param(errmsg => $msg) if $code eq 'FAIL';
 
 } elsif ($webvar{action} eq 'listcust') {
@@ -612 +567 @@
 # List all IPs in a pool, and allow arbitrary admin changes to each
 # Allow changes to ALL fields
-sub showPool($) {
+sub showPool {
   my $pool = new NetAddr::IP $_[0];
 
@@ -627 +582 @@
   $page->param(typelist => \@typelist);
 
-  $sth = $ip_dbh->prepare("select ip,custid,city,type,available,description,notes from poolips where pool='$pool' order by ip");
-  $sth->execute;
+  $sth = $ip_dbh->prepare("SELECT ip,custid,city,type,available,description,notes from poolips".
+        " WHERE pool=? ORDER BY ip");
+  $sth->execute($pool);
   my @iplist;
   while (my ($ip,$custid,$city,$type,$avail,$desc,$notes) = $sth->fetchrow_array) {

branches/stable/cgi-bin/main.cgi

@@ -60 +60 @@
 # Use the connectDB function, otherwise we end up confusing ourselves
 my $ip_dbh;
-my $sth;
 my $errstr;
 ($ip_dbh,$errstr) = connectDB_My;
@@ -90 +89 @@
 my $page;
 if (-e "$ENV{HTML_TEMPLATE_ROOT}/$webvar{action}.tmpl") {
-  $page = HTML::Template->new(filename => "$webvar{action}.tmpl");
+  $page = HTML::Template->new(filename => "$webvar{action}.tmpl", loop_context_vars => 1, global_vars => 1);
 } else {
   $page = HTML::Template->new(filename => "dunno.tmpl");
@@ -129 +128 @@
 }
 elsif($webvar{action} eq 'listpool') {
-  listPool();
+  showPool();
 }
 
@@ -155 +154 @@
 }
 elsif ($webvar{action} eq 'nodesearch') {
-  $sth = $ip_dbh->prepare("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id");
-  $sth->execute() or $page->param(errmsg => $sth->errstr);
-  my @nodelist;
-  while (my ($nid,$nname) = $sth->fetchrow_array()) {
-    my %row = (nodeid => $nid, nodename => $nname);
-    push @nodelist, \%row;
-  }
-  $page->param(nodelist => \@nodelist);
+  my $nodelist = getNodeList($ip_dbh);
+  $page->param(nodelist => $nodelist);
 }
 
@@ -201 +194 @@
 }
 
-
 # Clean up IPDB globals, DB handle, etc.
 finish($ip_dbh);
@@ -224 +216 @@
 # Initial display:  Show master blocks with total allocated subnets, total free subnets
 sub showSummary {
-  my %allocated;
-  my %free;
-  my %routed;
-  my %bigfree;
-
-  # Count the allocations.
-  $sth = $ip_dbh->prepare("select count(*) from allocations where cidr <<= ?");
-  foreach my $master (@masterblocks) {
-    $sth->execute("$master");
-    $sth->bind_columns(\$allocated{"$master"});
-    $sth->fetch();
-  }
-
-  # Count routed blocks
-  $sth = $ip_dbh->prepare("select count(*) from routed where cidr <<= ?");
-  foreach my $master (@masterblocks) {
-    $sth->execute("$master");
-    $sth->bind_columns(\$routed{"$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 (@masterblocks) {
-    $sth->execute("$master");
-    $sth->bind_columns(\$free{"$master"});
-    $sth->fetch();
-  }
-
-  # Find the largest free block in each master
-  $sth = $ip_dbh->prepare("select maskbits from freeblocks where cidr <<= ? and ".
-        "(routed='y' or routed='n') order by maskbits limit 1");
-  foreach my $master (@masterblocks) {
-    $sth->execute("$master");
-    $sth->bind_columns(\$bigfree{"$master"});
-    $sth->fetch();
-  }
-
-  # Assemble the data to stuff into the template.
-  my @masterlist;
-  my $rowclass=0;
-  foreach my $master (@masterblocks) {
-    my %row = (
-        rowclass => $rowclass++ % 2,
-        master => "$master",
-        routed => $routed{"$master"},
-        allocated => $allocated{"$master"},
-        free => $free{"$master"},
-        bigfree => ( ($bigfree{"$master"} eq '') ? ("&lt;NONE&gt;") : ("/".$bigfree{"$master"}) )
-        );
-    push (@masterlist, \%row);
-  }
-  $page->param(masterlist => \@masterlist);
+  my $masterlist = listSummary($ip_dbh);
+  $page->param(masterlist => $masterlist);
 
   $page->param(addmaster => ($IPDBacl{$authuser} =~ /a/) );
-
 } # showSummary
 
@@ -292 +231 @@
 
   $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});
+  $page->param(unrouted => $flist);
 } # showMaster
 
@@ -394 +250 @@
 sub showRBlock {
 
-  my $master = new NetAddr::IP $webvar{block};
-
-  $sth = $ip_dbh->prepare("select city from routed where cidr='$master'");
-  $sth->execute;
-  my ($rcity) = $sth->fetchrow_array;
-
-  $page->param(master => "$master");
+  $page->param(master => $webvar{block});
+  $page->param(delrouted => $IPDBacl{$authuser} =~ /d/);
+
+  my $rcity = getRoutedCity($ip_dbh, $webvar{block});
   $page->param(rcity => $rcity);
 
-  # Snag the allocations for this block
-  $sth = $ip_dbh->prepare("select cidr,city,type,custid,swip,description".
-        " from allocations where cidr <<= '$master' order by cidr");
-  $sth->execute();
-
-  # hack hack hack
-  # set up to flag swip=y records if they don't actually have supporting data in the customers table
-  my $custsth = $ip_dbh->prepare("select count(*) from customers where custid=?");
-
-  my $rowclass = 0;
-  my @blocklist;
-  while (my ($cidr,$city,$type,$custid,$swip,$desc) = $sth->fetchrow_array()) {
-    $custsth->execute($custid);
-    my ($ncust) = $custsth->fetchrow_array();
-
-    my %row = (
-        rowclass => $rowclass++ % 2,
-        block => $cidr,
-        city => $city,
-        type => $disp_alloctypes{$type},
-        custid => $custid,
-        swip => ($swip eq 'y' ? ($ncust == 0 ? 'Yes<small>*</small>' : 'Yes') : 'No'),
-        desc => $desc
-        );
-    $row{subblock} = ($type =~ /^.r$/);         # hmf.  wonder why these won't work in the hash declaration...
-    $row{listpool} = ($type =~ /^.[pd]$/);
-    push (@blocklist, \%row);
-  }
-  $page->param(blocklist => \@blocklist);
-
-  $page->param(delrouted => $IPDBacl{$authuser} =~ /d/);
-
-  # Snag the free blocks.  We don't really *need* to be pedantic about avoiding
-  # unrouted free blocks, but it's better to let the database do the work if we can.
-  $rowclass = 0;
-  my @unassigned;
-  $sth = $ip_dbh->prepare("select cidr,routed from freeblocks where cidr <<= '$master'".
-        " order by cidr");
-  $sth->execute();
-  while (my ($cidr_db,$routed) = $sth->fetchrow_array()) {
-    my $cidr = new NetAddr::IP $cidr_db;
-
-    my %row = (
-        rowclass => $rowclass++ % 2,
-        subblock => ($routed ne 'y' && $routed ne 'n'),
-        fblock => $cidr_db,
-        fbtype => $routed,
-        frange => $cidr->range,
-        );
-    push @unassigned, \%row;
-  }
-  $page->param(unassigned => \@unassigned);
-
+  my $blist = listRBlock($ip_dbh, $webvar{block});
+  $page->param(blocklist => $blist);
+
+  my $flist = listFree($ip_dbh, $webvar{block}, 'y');
+  $page->param(unassigned => $flist);
 } # showRBlock
 
 
 # List the IPs used in a pool
-sub listPool {
+sub showPool {
 
   my $cidr = new NetAddr::IP $webvar{pool};
@@ -473 +278 @@
 
   # Snag pool info for heading
-  $sth = $ip_dbh->prepare("select type,city from allocations where cidr=?");
-  $sth->execute($webvar{pool});
-  my ($pooltype, $poolcity) = $sth->fetchrow_array;
-
-  $page->param(disptype => $disp_alloctypes{$pooltype});
-  $page->param(city => $poolcity);
+  my $poolinfo = getBlockData($ip_dbh, $webvar{pool});
+
+  $page->param(disptype => $disp_alloctypes{$poolinfo->{type}});
+  $page->param(city => $poolinfo->{city});
 
   # Only display net/gw/bcast if it's a "real" netblock and not a PPP(oE) lunacy
-  $page->param(realblock => $pooltype =~ /^.d$/);
+  $page->param(realblock => $poolinfo->{type} =~ /^.d$/);
 
 # probably have to add an "edit IP allocation" link here somewhere.
 
-  $sth = $ip_dbh->prepare("select ip,custid,available,description,type".
-        " from poolips where pool='$webvar{pool}' order by ip");
-  $sth->execute;
-  my @poolips;
-  my $rowclass = 0;
-  while (my ($ip,$custid,$available,$desc,$type) = $sth->fetchrow_array) {
-    my %row = (
-        rowclass => $rowclass++ % 2,
-        ip => $ip,
-        custid => $custid,
-        available => $available,
-        desc => $desc,
-        maydel => $IPDBacl{$authuser} =~ /d/,
-        delme => $available eq 'n'
-        );
-    push @poolips, \%row;
-  }
-  $page->param(poolips => \@poolips);
-
-} # end listPool
+  my $plist = listPool($ip_dbh, $webvar{pool});
+  # technically slightly more efficient to check the ACL in an if () once outside the foreach
+  foreach (@{$plist}) {
+    $$_{maydel} = $IPDBacl{$authuser} =~ /d/;
+  }
+  $page->param(poolips => $plist);
+} # end showPool
 
 
@@ -535 +325 @@
     if ($webvar{fbtype} ne 'y') {
       # Snag the type of the container block from the database.
-      $sth = $ip_dbh->prepare("select type from allocations where cidr >>='$block'");
-      $sth->execute;
-      my @data = $sth->fetchrow_array;
-      $data[0] =~ s/c$/r/;      # Munge the type into the correct form
-      $page->param(fbdisptype => $list_alloctypes{$data[0]});
-      $page->param(type => $data[0]);
+## hmm.  need a flag for parent class/type, sort of?
+      my $pblock = subParent($ip_dbh, $webvar{block});
+      my $ptype = $pblock->{type};
+      $ptype =~ s/c$/r/;
+      $page->param(fbdisptype => $list_alloctypes{$ptype});
+      $page->param(type => $ptype);
     } else {
-      $sth = $ip_dbh->prepare("select type,listname from alloctypes where listorder < 500 ".
-        "and type not like '_i' order by listorder");
-      $sth->execute;
-      my @typelist;
-      my $selflag = 0;
-      while (my @data = $sth->fetchrow_array) {
-        my %row = (tval => $data[0],
-                type => $data[1],
-                sel => ($selflag == 0 ? ' selected' : '')
-                );
-        push (@typelist, \%row);
-        $selflag++;
-      }
-      $page->param(typelist => \@typelist);
+      # get "primary" alloctypes, since these are all that can correctly be assigned if we're in this branch
+      my $tlist = getTypeList($ip_dbh, 'p');
+      $tlist->[0]->{sel} = 1;
+      $page->param(typelist => $tlist);
     }
   } else {
-    my @masterlist;
-    foreach my $master (@masterblocks) {
-      my %row = (master => "$master");
-      push (@masterlist, \%row);
-    }
-    $page->param(masterlist => \@masterlist);
+    my $mlist = getMasterList($ip_dbh, 'c');
+    $page->param(masterlist => $mlist);
 
     my @pops;
@@ -572 +348 @@
     $page->param(pops => \@pops);
 
-    # could arguably include routing (500) in the list, but ATM it doesn't
-    # make sense, and in any case that shouldn't be structurally possible here.
-    $sth = $ip_dbh->prepare("select type,listname from alloctypes where listorder <= 500 order by listorder");
-    $sth->execute;
-    my @typelist;
-    my $selflag = 0;
-    while (my @data = $sth->fetchrow_array) {
-      my %row = (tval => $data[0],
-        type => $data[1],
-        sel => ($selflag == 0 ? ' selected' : '')
-        );
-      push (@typelist, \%row);
-      $selflag++;
-    }
-    $page->param(typelist => \@typelist);
+    # get all standard alloctypes
+    my $tlist = getTypeList($ip_dbh, 'a');
+    $tlist->[0]->{sel} = 1;
+    $page->param(typelist => $tlist);
   }
 
@@ -597 +362 @@
 
 ## node hack
-  $sth = $ip_dbh->prepare("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id");
-  $sth->execute() or print "DEBUG: failed retrieval from nodes: ".$sth->errstr,"<br>\n";
-  my @nodes;
-  while (my ($nid,$nname) = $sth->fetchrow_array()) {
-    my %row = (nid => $nid, nname => $nname);
-    push (@nodes, \%row);
-  }
-  $page->param(nodelist => \@nodes);
+  my $nlist = getNodeList($ip_dbh);
+  $page->param(nodelist => $nlist);
 ## end node hack
 
@@ -632 +391 @@
 
   if ($webvar{alloctype} =~ /^.i$/ && $webvar{fbassign} ne 'y') {
-    my ($base,undef) = split //, $webvar{alloctype};    # split into individual chars
-
-# Ewww.  But it works.
-    $sth = $ip_dbh->prepare("SELECT (SELECT city FROM allocations WHERE cidr=poolips.pool), ".
-        "poolips.pool, COUNT(*) FROM poolips,allocations WHERE poolips.available='y' AND ".
-        "poolips.pool=allocations.cidr AND allocations.city='$webvar{pop}' AND poolips.type LIKE '".$base."_' ".
-        "GROUP BY pool");
-    $sth->execute;
-    my $optionlist;
-
-    my @poollist;
-    while (my ($poolcit,$poolblock,$poolfree) = $sth->fetchrow_array) {
-      # city,pool cidr,free IP count
-      if ($poolfree > 0) {
-        my %row = (poolcit => $poolcit, poolblock => $poolblock, poolfree => $poolfree);
-        push (@poollist, \%row);
-      }
-    }
+    my $plist = getPoolSelect($ip_dbh, $webvar{alloctype}, $webvar{pop});
     $page->param(staticip => 1);
-    $page->param(poollist => \@poollist);
+    $page->param(poollist => $plist) if $plist;
     $cidr = "Single static IP";
 ##fixme:  need to handle "no available pools"
@@ -657 +399 @@
   } else { # end show pool options
 
-    if ($webvar{fbassign} eq 'y') {
+    if ($webvar{fbassign} && $webvar{fbassign} eq 'y') {
       $alloc_from = new NetAddr::IP $webvar{allocfrom};
 ## possibly messy behaviour:  force the _from and block to be the network addr?
@@ -669 +411 @@
         return;
       }
-      my $sql;
-      my $city;
-      my $failmsg;
-      my $extracond = '';
-      if ($webvar{allocfrom} eq '-') {
-        $extracond = ($webvar{allowpriv} eq 'on' ? '' : 
-                " and not (cidr <<= '192.168.0.0/16'".
-                        " or cidr <<= '10.0.0.0/8'".
-                        " or cidr <<= '172.16.0.0/12')");
-      }
-      my $sortorder;
+
+##fixme ick, ew, bleh.  gotta handle the failure message generation better.  push it into findAllocateFrom()?
+      my $failmsg = "No suitable free block found.<br>\n";
       if ($webvar{alloctype} eq 'rm') {
-        if ($webvar{allocfrom} ne '-') {
-          $sql = "select * from freeblocks where maskbits<=$webvar{maskbits} and routed='n'".
-                " and cidr <<= '$webvar{allocfrom}'";
-          $sortorder = "maskbits desc";
-        } else {
-          $sql = "select * from freeblocks where maskbits<=$webvar{maskbits} and routed='n'";
-          $sortorder = "maskbits desc";
-        }
-        $failmsg = "No suitable free block found.<br>\nWe do not have a free".
-          " routeable block of that size.<br>\nYou will have to either route".
-          " a set of smaller netblocks or a single smaller netblock.";
+        $failmsg .= "We do not have a free routeable block of that size.<br>\n".
+                "You will have to either route a set of smaller netblocks or a single smaller netblock.";
       } else {
-##fixme
-# This section needs serious Pondering.
-        # Pools of most types get assigned to the POP they're "routed from"
-        # This includes WAN blocks and other netblock "containers"
-        # This does NOT include cable pools.
         if ($webvar{alloctype} =~ /^.[pc]$/) {
-          $city = $webvar{city};
-          $failmsg = "No suitable free block found.<br>\nYou will have to route another".
-            " superblock from one of the<br>\nmaster blocks or chose a smaller".
-            " block size for the pool.";
+          $failmsg .= "You will have to route another superblock from one of the<br>\n".
+                "master blocks or chose a smaller block size for the pool.";
         } else {
           if (!$webvar{pop}) {
@@ -708 +426 @@
             return;
           }
-          $city = $webvar{pop};
-          $failmsg = "No suitable free block found.<br>\nYou will have to route another".
-            " superblock to $webvar{pop}<br>\nfrom one of the master blocks or".
-            " chose a smaller blocksize.";
-        }
-        if (defined $webvar{allocfrom} && $webvar{allocfrom} ne '-') {
-          $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
-                " and cidr <<= '$webvar{allocfrom}' and routed='".
-                (($webvar{alloctype} =~ /^(.)r$/) ? "$1" : 'y')."'";
-          $sortorder = "maskbits desc,cidr";
-        } else {
-          $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}".
-                " and routed='".(($webvar{alloctype} =~ /^(.)r$/) ? "$1" : 'y')."'";
-          $sortorder = "maskbits desc,cidr";
+          $failmsg .= "You will have to route another superblock to $webvar{pop}<br>\n".
+                "from one of the master blocks or chose a smaller blocksize.";
         }
       }
-      $sql = $sql.$extracond." order by ".$sortorder;
-      $sth = $ip_dbh->prepare($sql);
-      $sth->execute;
-      my @data = $sth->fetchrow_array();
-      if ($data[0] eq "") {
+
+      $cidr = findAllocateFrom($ip_dbh, $webvar{maskbits}, $webvar{alloctype}, $webvar{city}, $webvar{pop},
+        (master => $webvar{allocfrom}, allowpriv => $webvar{allowpriv}) );
+      if (!$cidr) {
         $page->param(err => $failmsg);
         return;
       }
-      $cidr = new NetAddr::IP $data[0];
+      $cidr = new NetAddr::IP $cidr;
 
 # this chunk now specific to "guided" allocation;  freeblock-select can now slice-n-dice on its own.
@@ -754 +459 @@
 ## node hack
   if ($webvar{node} && $webvar{node} ne '-') {
-    $sth = $ip_dbh->prepare("SELECT node_name FROM nodes WHERE node_id=?");
-    $sth->execute($webvar{node});
-    my ($nodename) = $sth->fetchrow_array();
+    my $nodename = getNodeName($ip_dbh, $webvar{node});
     $page->param(nodename => $nodename);
     $page->param(nodeid => $webvar{node});
@@ -875 +578 @@
       return;
     }
-    if ($webvar{custid} !~ /^(?:\d{10}|\d{7}|STAFF)(?:-\d\d?)?$/) {
-      # Force uppercase for now...
-      $webvar{custid} =~ tr/a-z/A-Z/;
-      # Crosscheck with billing.
-      my $status = CustIDCK->custid_exist($webvar{custid});
-      if ($CustIDCK::Error) {
-        $page->param(err => "Error verifying customer ID: ".$CustIDCK::ErrMsg);
-        return;
-      }
-      if (!$status) {
-        $page->param(err => "Customer ID not valid.  Make sure the Customer ID ".
-          "is correct.<br>\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ".
-          "non-customer assignments.");
-        return;
-      }
+    # Crosscheck with billing.
+    my $status = CustIDCK->custid_exist($webvar{custid});
+    if ($CustIDCK::Error) {
+      $page->param(err => "Error verifying customer ID: ".$CustIDCK::ErrMsg);
+      return;
+    }
+    if (!$status) {
+      $page->param(err => "Customer ID not valid.  Make sure the Customer ID ".
+        "is correct.<br>\nUse STAFF for staff static IPs, and $IPDB::defcustid for any other ".
+        "non-customer assignments.");
+      return;
     }
 #    print "<!-- [ In validateInput().  Insert customer ID cross-check here. ] -->\n";
@@ -913 +612 @@
 ##fixme:  hook to force-set POP or city on certain alloctypes
 # if ($webvar{alloctype =~ /foo,bar,bz/ { $webvar{pop} = 'blah'; }
-    if ($webvar{pop} =~ /^-$/) {
+    if ($webvar{pop} && $webvar{pop} =~ /^-$/) {
       $flag = 'to route the block from/through';
     }
@@ -935 +634 @@
 sub edit {
 
-  my $sql;
-
-  # Two cases:  block is a netblock, or block is a static IP from a pool
-  # because I'm lazy, we'll try to make the SELECT's bring out identical)ish) data
-##fixme:  allow "SWIP" (publication to rWHOIS) of static IP data
-  if ($webvar{block} =~ /\/32$/) {
-    $sql = "select ip,custid,type,city,circuitid,description,notes,modifystamp,privdata from poolips where ip='$webvar{block}'";
-  } else {
-    $sql = "select cidr,custid,type,city,circuitid,description,notes,modifystamp,privdata,swip from allocations where cidr='$webvar{block}'"
-  }
-
-  # gotta snag block info from db
-  $sth = $ip_dbh->prepare($sql);
-  $sth->execute;
-  my @data = $sth->fetchrow_array;
-
-  # Clean up extra whitespace on alloc type
-  $data[2] =~ s/\s//;
-
-  # We can't let the city be changed here;  this block is a part of
-  # a larger routed allocation and therefore by definition can't be moved.
-  # block and city are static.
-##fixme
-# Needs thinking.  Have to allow changes to city to correct errors, no?
-# Also have areas where a routed block at a POP serves "many" cities/towns/named crossroads
-
-# @data: cidr,custid,type,city,circuitid,description,notes,modifystamp,privdata,swip
+  # snag block info from db
+  my $blockinfo = getBlockData($ip_dbh, $webvar{block});
+
+  # Clean up extra whitespace on alloc type.  Mainly a legacy-data cleanup.
+  $blockinfo->{type} =~ s/\s//;
 
   $page->param(block => $webvar{block});
 
-  $page->param(custid => $data[1]);
-  $page->param(city => $data[3]);
-  $page->param(circid => $data[4]);
-  $page->param(desc => $data[5]);
-  $page->param(notes => $data[6]);
+  $page->param(custid   => $blockinfo->{custid});
+  $page->param(city     => $blockinfo->{city});
+  $page->param(circid   => $blockinfo->{circuitid});
+  $page->param(desc     => $blockinfo->{description});
+  $page->param(notes    => $blockinfo->{notes});
 
 ##fixme The check here should be built from the database
 # Need to expand to support pool types too
-  if ($data[2] =~ /^.[ne]$/ && $IPDBacl{$authuser} =~ /c/) {
+  if ($blockinfo->{type} =~ /^.[ne]$/ && $IPDBacl{$authuser} =~ /c/) {
     $page->param(changetype => 1);
     $page->param(alloctype => [
-                { selme => ($data[2] eq 'me'), type => "me", disptype => "Dialup netblock" },
-                { selme => ($data[2] eq 'de'), type => "de", disptype => "Dynamic DSL netblock" },
-                { selme => ($data[2] eq 'ce'), type => "ce", disptype => "Dynamic cable netblock" },
-                { selme => ($data[2] eq 'we'), type => "we", disptype => "Dynamic wireless netblock" },
-                { selme => ($data[2] eq 'cn'), type => "cn", disptype => "Customer netblock" },
-                { selme => ($data[2] eq 'en'), type => "en", disptype => "End-use netblock" },
-                { selme => ($data[2] eq 'in'), type => "in", disptype => "Internal netblock" },
+                { selme => ($blockinfo->{type} eq 'me'), type => "me", disptype => "Dialup netblock" },
+                { selme => ($blockinfo->{type} eq 'de'), type => "de", disptype => "Dynamic DSL netblock" },
+                { selme => ($blockinfo->{type} eq 'ce'), type => "ce", disptype => "Dynamic cable netblock" },
+                { selme => ($blockinfo->{type} eq 'we'), type => "we", disptype => "Dynamic wireless netblock" },
+                { selme => ($blockinfo->{type} eq 'cn'), type => "cn", disptype => "Customer netblock" },
+                { selme => ($blockinfo->{type} eq 'en'), type => "en", disptype => "End-use netblock" },
+                { selme => ($blockinfo->{type} eq 'in'), type => "in", disptype => "Internal netblock" },
                 ]
         );
   } else {
-    $page->param(disptype => $disp_alloctypes{$data[2]});
-    $page->param(type => $data[2]);
+    $page->param(disptype => $disp_alloctypes{$blockinfo->{type}});
+    $page->param(type => $blockinfo->{type});
   }
 
 ## node hack
-  $sth = $ip_dbh->prepare("SELECT nodes.node_id,node_name FROM nodes INNER JOIN noderef".
-        " ON nodes.node_id=noderef.node_id WHERE noderef.block='$webvar{block}'");
-  $sth->execute;
-  my ($nodeid,$nodename) = $sth->fetchrow_array();
+  my ($nodeid,$nodename) = getNodeInfo($ip_dbh, $webvar{block});
   $page->param(havenodeid => $nodeid);
 
-  if ($data[2] eq 'fr' || $data[2] eq 'bi') {
+  if ($blockinfo->{type} eq 'fr' || $blockinfo->{type} eq 'bi') {
     $page->param(typesupportsnodes => 1);
     $page->param(nodename => $nodename);
@@ -1006 +680 @@
 #  (currently) have a nodeid set in the first place.
     if ($IPDBacl{$authuser} =~ /c/) {
-      $sth = $ip_dbh->prepare("SELECT node_id, node_name FROM nodes ORDER BY node_type,node_id");
-      $sth->execute;
-      my @nodelist;
-      while (my ($nid,$nname) = $sth->fetchrow_array()) {
-        my %row = (
-                selme => ($nodeid == $nid),
-                nodeid => $nid,
-                nodename => $nname,
-                );
-        push (@nodelist, \%row);
+      my $nlist = getNodeList($ip_dbh);
+      foreach (@{$nlist}) {
+        $$_{selme} = ($$_{node_id} == $nodeid);
       }
-      $page->param(nodelist => \@nodelist);
+      $page->param(nodelist => $nlist);
     }
   }
 ## end node hack
 
-  my ($lastmod,undef) = split /\s+/, $data[7];
+  my ($lastmod,undef) = split /\s+/, $blockinfo->{lastmod};
   $page->param(lastmod => $lastmod);
 
   # not happy with the upside-down logic, but...
-  $page->param(swipable => $data[2] !~ /.i/);
-  $page->param(swip => $data[10] ne 'n');
+  $page->param(swipable => $blockinfo->{type} !~ /.i/);
+  $page->param(swip => $blockinfo->{swip} ne 'n') if $blockinfo->{swip};
 
   # Check to see if we can display sensitive data
   $page->param(nocling => $IPDBacl{$authuser} =~ /s/);
-  $page->param(privdata => $data[8]);
+  $page->param(privdata => $blockinfo->{privdata});
 
   # ACL trickery - these two template booleans control the presence of all form/input tags
@@ -1048 +715 @@
   }
 
-  # Check to see if we can update restricted data
-  my $privdata = '';
-  if ($IPDBacl{$authuser} =~ /s/) {
-    $privdata = ",privdata='$webvar{privdata}'";
-  }
-
   # Make sure incoming data is in correct format - custID among other things.
   return if !validateInput;
 
-  # SQL transaction wrapper
-  eval {
-    # Relatively simple SQL transaction here.
-    my $sql;
-    if (my $pooltype = ($webvar{alloctype} =~ /^(.)i$/) ) {
-      $sql = "UPDATE poolips SET custid='$webvar{custid}',".
-        "city=?,description=?,notes=?,".
-        "circuitid='$webvar{circid}',".
-        "$privdata where ip='$webvar{block}'";
-    } else {
-      $sql = "UPDATE allocations SET custid='$webvar{custid}',".
-        "city=?,description=?,notes=?,".
-        "circuitid='$webvar{circid}'$privdata,".
-        "type='$webvar{alloctype}',".
-        "swip='".($webvar{swip} eq 'on' ? 'y' : 'n')."' ".
-        "where cidr='$webvar{block}'";
-    }
-    # Log the details of the change.
-    syslog "debug", $sql;
-    $sth = $ip_dbh->prepare($sql);
-    $sth->execute($webvar{city}, $webvar{desc}, $webvar{notes});
-## node hack
-    if ($webvar{node}) {
-      # done with delete/insert so we don't have to worry about funkyness updating a node ref that isn't there
-      $ip_dbh->do("DELETE FROM noderef WHERE block='$webvar{block}'");
-      $sth = $ip_dbh->prepare("INSERT INTO noderef (block,node_id) VALUES (?,?)");
-      $sth->execute($webvar{block},$webvar{node});
-    }
-## end node hack
-    $ip_dbh->commit;
-  };
-  if ($@) {
-    my $msg = $@;
-    eval { $ip_dbh->rollback; };
+  $webvar{swip} = 'n' if !$webvar{swip};
+
+  my %updargs = (
+        custid          => $webvar{custid},
+        city            => $webvar{city},
+        description     => $webvar{desc},
+        notes           => $webvar{notes},
+        circuitid       => $webvar{circid},
+        block           => $webvar{block},
+        type            => $webvar{alloctype},
+        swip            => $webvar{swip},
+        );
+
+  # Semioptional values
+  $updargs{privdata} = $webvar{privdata} if $IPDBacl{$authuser} =~ /s/;
+  $updargs{node} = $webvar{node} if $webvar{node};
+
+  my ($code,$msg) = updateBlock($ip_dbh, %updargs);
+
+  if ($code eq 'FAIL') {
     syslog "err", "$authuser could not update block/IP '$webvar{block}': '$msg'";
     $page->param(err => "Could not update block/IP $webvar{block}: $msg");
@@ -1098 +745 @@
   # If we get here, the operation succeeded.
   syslog "notice", "$authuser updated $webvar{block}";
+##fixme:  log details of the change?  old way is in the .debug stream anyway.
 ##fixme:  need to wedge something in to allow "update:field" notifications
 ## hmm.  how to tell what changed?  O_o
@@ -1105 +753 @@
 ## node hack
   if ($webvar{node} && $webvar{node} ne '-') {
-    $sth = $ip_dbh->prepare("SELECT node_name FROM nodes WHERE node_id=?");
-    $sth->execute($webvar{node});
-    my ($nodename) = $sth->fetchrow_array();
+    my $nodename = getNodeName($ip_dbh, $webvar{node});
     $page->param(nodename => $nodename);
   }
@@ -1113 +759 @@
 
   # Link back to browse-routed or list-pool page on "Update complete" page.
-  my $cblock;   # to contain the CIDR of the container block we're retrieving.
-  my $sql;
+  my $cblock;
   if (my $pooltype = ($webvar{alloctype} =~ /^(.)i$/) ) {
     $page->param(backpool => 1);
-    $sql = "select pool from poolips where ip='$webvar{block}'";
+    $cblock = ipParent($ip_dbh, $webvar{block});
   } else {
-    $sql = "select cidr from routed where cidr >>= '$webvar{block}'";
-  }
-  # I define there to be no errors on this operation...  so we don't need to check for them.
-  $sth = $ip_dbh->prepare($sql);
-  $sth->execute;
-  $sth->bind_columns(\$cblock);
-  $sth->fetch();
-  $sth->finish;
-  $page->param(backblock => $cblock);
+    $cblock = blockParent($ip_dbh, $webvar{block});
+  }
+  $page->param(backblock => $cblock->{cidr});
+
+  # Do some HTML fiddling here instead of using ESCAPE=HTML in the template,
+  # because otherwise we can't convert \n to <br>.  *sigh*
+  $webvar{notes} = $q->escapeHTML($webvar{notes});      # escape first...
+  $webvar{notes} =~ s/\n/<br>\n/;                       # ... then convert newlines
+  $webvar{privdata} = ($webvar{privdata} ? $q->escapeHTML($webvar{privdata}) : "&nbsp;");
+  $webvar{privdata} =~ s/\n/<br>\n/;
 
   $page->param(cidr => $webvar{block});
@@ -1134 +780 @@
   $page->param(custid => $webvar{custid});
   $page->param(swip => $webvar{swip} eq 'on' ? 'Yes' : 'No');
-  $page->param(circid => $q->escapeHTML($webvar{circid}));
-  $page->param(desc => $q->escapeHTML($webvar{desc}));
-  $page->param(notes => $q->escapeHTML($webvar{notes}));
-  $webvar{privdata} = ($webvar{privdata} ? $q->escapeHTML($webvar{privdata}) : "&nbsp;");
+  $page->param(circid => $webvar{circid});
+  $page->param(desc => $webvar{desc});
+  $page->param(notes => $webvar{notes});
   $page->param(privdata => $webvar{privdata})
         if $IPDBacl{$authuser} =~ /s/;
@@ -1157 +802 @@
   }
 
-  my ($cidr, $custid, $type, $city, $circid, $desc, $notes, $alloctype, $privdata);
+  my $blockdata;
 
   if ($webvar{alloctype} eq 'rm') {
-    $sth = $ip_dbh->prepare("select cidr,city from routed where cidr='$webvar{block}'");
-    $sth->execute();
-
-# This feels...  extreme.
-    croak $sth->errstr() if($sth->errstr());
-
-    $sth->bind_columns(\$cidr,\$city);
-    $sth->execute();
-    $sth->fetch || croak $sth->errstr();
-    $custid = "N/A";
-    $alloctype = $webvar{alloctype};
-    $circid = "N/A";
-    $desc = "N/A";
-    $notes = "N/A";
-    $privdata = "N/A";
+
+    $blockdata->{block} = $webvar{block};
+    $blockdata->{city} = getRoutedCity($ip_dbh, $webvar{block});
+    $blockdata->{custid} = "N/A";
+    $blockdata->{type} = $webvar{alloctype};
+    $blockdata->{circuitid} = "N/A";
+    $blockdata->{description} = "N/A";
+    $blockdata->{notes} = "N/A";
+    $blockdata->{privdata} = "N/A";
 
   } elsif ($webvar{alloctype} eq 'mm') {
 
-    $cidr = $webvar{block};
-    $city = "N/A";
-    $custid = "N/A";
-    $alloctype = $webvar{alloctype};
-    $circid = "N/A";
-    $desc = "N/A";
-    $notes = "N/A";
-    $privdata = "N/A";
-
-  } elsif ($webvar{alloctype} =~ /^.i$/) { # done with alloctype=[rm]m
-
-    # Unassigning a static IP
-    my $sth = $ip_dbh->prepare("select ip,custid,city,type,notes,circuitid,privdata".
-        " from poolips where ip='$webvar{block}'");
-    $sth->execute();
-#  croak $sth->errstr() if($sth->errstr());
-
-    $sth->bind_columns(\$cidr, \$custid, \$city, \$alloctype, \$notes, \$circid,
-        \$privdata);
-    $sth->fetch() || croak $sth->errstr;
-
-  } else { # done with alloctype=~ /^.i$/
-
-    my $sth = $ip_dbh->prepare("select cidr,custid,type,city,circuitid,description,notes,privdata".
-        " from allocations where cidr='$webvar{block}'");
-    $sth->execute();
-#       croak $sth->errstr() if($sth->errstr());
-
-    $sth->bind_columns(\$cidr, \$custid, \$alloctype, \$city, \$circid, \$desc,
-        \$notes, \$privdata);
-    $sth->fetch() || carp $sth->errstr;
+    $blockdata->{block} = $webvar{block};
+    $blockdata->{city} = "N/A";
+    $blockdata->{custid} = "N/A";
+    $blockdata->{type} = $webvar{alloctype};
+    $blockdata->{circuitid} = "N/A";
+    $blockdata->{description} = "N/A";
+    $blockdata->{notes} = "N/A";
+    $blockdata->{privdata} = "N/A";
+
+  } else {
+
+    $blockdata = getBlockData($ip_dbh, $webvar{block})
+
   } # end cases for different alloctypes
 
-  $page->param(block => $cidr);
-  $page->param(disptype => $disp_alloctypes{$alloctype});
-  $page->param(type => $alloctype);
-  $page->param(city => $city);
-  $page->param(custid => $custid);
-  $page->param(circid => $circid);
-  $page->param(desc => $desc);
-  $page->param(notes => $notes);
-  $privdata = '&nbsp;' if $privdata eq '';
-  $page->param(privdata => $privdata) if $IPDBacl{$authuser} =~ /s/;
-  $page->param(delpool => $alloctype =~ /^.[pd]$/);
+  $page->param(block => $blockdata->{block});
+  $page->param(disptype => $disp_alloctypes{$blockdata->{type}});
+  $page->param(type => $blockdata->{type});
+  $page->param(city => $blockdata->{city});
+  $page->param(custid => $blockdata->{custid});
+  $page->param(circid => $blockdata->{circuitid});
+  $page->param(desc => $blockdata->{description});
+  $blockdata->{notes} = $q->escapeHTML($blockdata->{notes});
+  $blockdata->{notes} =~ s/\n/<br>\n/;
+  $page->param(notes => $blockdata->{notes});
+  $blockdata->{privdata} = $q->escapeHTML($blockdata->{privdata});
+  $blockdata->{privdata} = '&nbsp;' if !$blockdata->{privdata};
+  $blockdata->{privdata} =~ s/\n/<br>\n/;
+  $page->param(privdata => $blockdata->{privdata}) if $IPDBacl{$authuser} =~ /s/;
+  $page->param(delpool => $blockdata->{type} =~ /^.[pd]$/);
 
 } # end remove()
@@ -1237 +862 @@
 
   # need to retrieve block data before deleting so we can notify on that
-  my ($cidr,$custid,$type,$city,$description) = getBlockData($ip_dbh, $webvar{block});
+  my $blockinfo = getBlockData($ip_dbh, $webvar{block});
 
   my ($code,$msg) = deleteBlock($ip_dbh, $webvar{block}, $webvar{alloctype});
@@ -1243 +868 @@
   $page->param(block => $webvar{block});
   if ($code eq 'OK') {
-    syslog "notice", "$authuser deallocated '$webvar{alloctype}'-type netblock $webvar{block}".
-        " $custid, $city, desc='$description'";
+    syslog "notice", "$authuser deallocated '$webvar{alloctype}'-type netblock $webvar{block} ".
+        $blockinfo->{custid}.", ".$blockinfo->{city}.", desc='".$blockinfo->{description}."'";
     mailNotify($ip_dbh, 'da', "REMOVED: $disp_alloctypes{$webvar{alloctype}} $webvar{block}",
         "$disp_alloctypes{$webvar{alloctype}} $webvar{block} deallocated by $authuser\n".
-        "CustID: $custid\nCity: $city\nDescription: $description\n");
+        "CustID: ".$blockinfo->{custid}."\nCity: ".$blockinfo->{city}.
+        "\nDescription: ".$blockinfo->{description}."\n");
   } else {
     $page->param(failmsg => $msg);

branches/stable/cgi-bin/search.cgi

@@ -146 +146 @@
   my $cols = "cidr,custid,type,city,description";
 
+  # hack fix for undefined variables
+  $webvar{custid} = '' if !$webvar{custid};
+  $webvar{desc}   = '' if !$webvar{desc};
+  $webvar{notes}  = '' if !$webvar{notes};
+  $webvar{custexclude}  = '' if !$webvar{custexclude};
+  $webvar{descexclude}  = '' if !$webvar{descexclude};
+  $webvar{notesexclude} = '' if !$webvar{notesexclude};
+
   # First chunk of SQL.  Filter on custid, description, and notes as necessary.
-  my $sql = "(select $cols from searchme where".
-        " $webvar{custexclude} (custid ilike '%$webvar{custid}%'".
-        " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')".
-        " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')";
+  my $sql = qq(SELECT $cols FROM searchme\n);
+  $sql .= " WHERE $webvar{custexclude} (custid ~ '$webvar{custid}')\n";
+  $sql .= " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ~ '$webvar{desc}')\n";
+  $sql .= " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ~ '$webvar{notes}')";
 
   # If we're not supposed to search for all types, search for the selected types.
+  $webvar{alltypes} = '' if !$webvar{alltypes};
+  $webvar{typeexclude} = '' if !$webvar{typeexclude};
   if ($webvar{alltypes} ne 'on') {
     $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
@@ -164 +174 @@
   # If we're not supposed to search for all cities, search for the selected cities.
   # This could be vastly improved with proper foreign keys in the database.
+  $webvar{allcities} = '' if !$webvar{allcities};
+  $webvar{cityexclude} = '' if !$webvar{cityexclude};
   if ($webvar{allcities} ne 'on') {
     $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
@@ -315 +327 @@
 # Creates appropriate SQL to run the search and display the results
 # with queryResults()
-sub viewBy($$) {
+sub viewBy {
   my ($category,$query) = @_;
 
@@ -445 +457 @@
 # Display search queries based on the passed SQL.
 # Takes SQL, page number (for multipage search results), and a total count.
-sub queryResults($$$) {
+sub queryResults {
   my ($sql, $pageNo, $rowCount) = @_;
   my $offset = 0;
@@ -529 +541 @@
 # Return count of rows to be returned in a "real" query
 # with the passed SQL statement
-sub countRows($) {
+sub countRows {
   # Note that the "as foo" is required
   my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo");