Changeset 594 for branches/stable/cgi-bin/IPDB.pm

Timestamp:

05/15/13 16:17:00 (11 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/IPDB.pm (modified) (12 diffs)

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()