Index: branches/stable/cgi-bin/IPDB.pm =================================================================== --- branches/stable/cgi-bin/IPDB.pm (revision 158) +++ branches/stable/cgi-bin/IPDB.pm (revision 159) @@ -58,5 +58,5 @@ # Initialize alloctypes hashes - $sth = $dbh->prepare("select * from alloctypes order by listorder"); + $sth = $dbh->prepare("select type,listname,dispname,listorder from alloctypes order by listorder"); $sth->execute; while (my @data = $sth->fetchrow_array) { @@ -68,5 +68,5 @@ # City and POP listings - $sth = $dbh->prepare("select * from cities order by city"); + $sth = $dbh->prepare("select city,routing from cities order by city"); $sth->execute; return (undef,$sth->errstr) if $sth->err; @@ -79,5 +79,5 @@ # Master block list - $sth = $dbh->prepare("select * from masterblocks order by cidr"); + $sth = $dbh->prepare("select cidr from masterblocks order by cidr"); $sth->execute; for (my $i=0; my @data = $sth->fetchrow_array(); $i++) { @@ -120,5 +120,5 @@ # Return here if we can't select. Note that this indicates a # problem executing the select. - my $sth = $dbh->prepare('select cidr from masterblocks'); + my $sth = $dbh->prepare("select cidr from masterblocks"); $sth->execute(); return (undef,$DBI::errstr) if ($sth->err); @@ -154,5 +154,5 @@ } else { # it connects, try a stmt. - my $sth = $dbh->prepare('select cidr from masterblocks'); + my $sth = $dbh->prepare("select cidr from masterblocks"); my $err = $sth->execute(); @@ -189,19 +189,21 @@ local $dbh->{RaiseError} = 1; # step on our toes by accident. - if ($type =~ /^[cdsmw]i$/) { + if ($type =~ /^.i$/) { $msg = "Unable to assign static IP $cidr to $custid"; eval { - # We'll just have to put up with the oddities caused by SQL (un)sort order - $sth = $dbh->prepare("select * from poolips where pool='$alloc_from'". - " and available='y' order by ip"); - $sth->execute; - + # We have to do this in two parts because otherwise we lose + # the ability to return the IP assigned. Should that change, + # the commented SQL statement below may become usable. # update poolips set custid='$custid',city='$city',available='n', # description='$desc',notes='$notes',circuitid='$circid' # where ip=(select ip from poolips where pool='$alloc_from' # and available='y' order by ip limit 1); -##err Need better handling here; what if there's no free IPs when this sub gets called? + + $sth = $dbh->prepare("select ip from poolips where pool='$alloc_from'". + " and available='y' order by ip"); + $sth->execute; + my @data = $sth->fetchrow_array; - $cidr = $data[1]; # $cidr is already declared when we get here! + $cidr = $data[0]; # $cidr is already declared when we get here! $sth = $dbh->prepare("update poolips set custid='$custid',". @@ -234,6 +236,6 @@ " where cidr='$cidr'"); $sth->execute; - $sth = $dbh->prepare("insert into routed values ('$cidr',". - $cidr->masklen.",'$city')"); + $sth = $dbh->prepare("insert into routed (cidr,maskbits,city)". + " values ('$cidr',".$cidr->masklen.",'$city')"); $sth->execute; } else { @@ -242,13 +244,21 @@ $sth->execute; - $sth = $dbh->prepare("insert into allocations values ('$cidr',". - "'$custid','$type','$city','$desc','$notes',". + $sth = $dbh->prepare("insert into allocations". + " (cidr,custid,type,city,description,notes,maskbits,circuitid)". + " values ('$cidr','$custid','$type','$city','$desc','$notes',". $cidr->masklen.",'$circid')"); $sth->execute; # And initialize the pool, if necessary + # PPPoE pools (currently dialup, DSL, and WiFi) get all IPs made available + # "DHCP" or "real-subnet" pools have the net, gw, and bcast IPs removed. if ($type =~ /^.p$/) { - $msg = "Could not initialize IPs in new $disp_alloctypes{$type} pool $cidr"; - initPool($dbh,$cidr,$type,$city,(($type eq 'dp' or $type eq 'mp') ? "all" : "normal")); + $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr"; + my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"all"); + die $rmsg if $code eq 'FAIL'; + } elsif ($type =~ /^.d$/) { + $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr"; + my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"normal"); + die $rmsg if $code eq 'FAIL'; } @@ -258,7 +268,7 @@ }; # end of eval if ($@) { - $msg = $@; + $msg .= ": ".$@; eval { $dbh->rollback; }; - return ('FAIL',$@); + return ('FAIL',$msg); } else { return ('OK',"OK"); @@ -297,5 +307,7 @@ # Insert the new freeblocks entries # Note that non-routed blocks are assigned to - $sth = $dbh->prepare("insert into freeblocks values (?, ?, '','n')"); + # and use the default value for the routed column ('n') + $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city)". + " values (?, ?, '')"); foreach my $block (@newfreeblocks) { $sth->execute("$block", $block->masklen); @@ -303,10 +315,10 @@ # Insert the entry in the routed table - $sth = $dbh->prepare("insert into routed values ('$cidr',". - $cidr->masklen.",'$city')"); + $sth = $dbh->prepare("insert into routed (cidr,maskbits,city)". + " values ('$cidr',".$cidr->masklen.",'$city')"); $sth->execute; # Insert the (almost) same entry in the freeblocks table - $sth = $dbh->prepare("insert into freeblocks values ('$cidr',". - $cidr->masklen.",'$city','y')"); + $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)". + " values ('$cidr',".$cidr->masklen.",'$city','y')"); $sth->execute; @@ -314,6 +326,6 @@ # Insert the new freeblocks entries - $sth = $dbh->prepare("insert into freeblocks values (?, ?, ". - "(select city from routed where cidr >>= '$cidr'),'y')"); + $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)". + " values (?, ?, (select city from routed where cidr >>= '$cidr'),'y')"); foreach my $block (@newfreeblocks) { $sth->execute("$block", $block->masklen); @@ -321,13 +333,21 @@ # Insert the allocations entry - $sth = $dbh->prepare("insert into allocations values ('$cidr',". - "'$custid','$type','$city','$desc','$notes',".$cidr->masklen. - ",'$circid')"); + $sth = $dbh->prepare("insert into allocations (cidr,custid,type,city,". + "description,notes,maskbits,circuitid)". + " values ('$cidr','$custid','$type','$city','$desc','$notes',". + $cidr->masklen.",'$circid')"); $sth->execute; # And initialize the pool, if necessary + # PPPoE pools (currently dialup, DSL, and WiFi) get all IPs made available + # "DHCP" or "real-subnet" pools have the net, gw, and bcast IPs removed. if ($type =~ /^.p$/) { $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr"; - initPool($dbh,$cidr,$type,$city,($type eq 'dp' ? "all" : "normal")); + my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"all"); + die $rmsg if $code eq 'FAIL'; + } elsif ($type =~ /^.d$/) { + $msg = "Could not initialize IPs in new $disp_alloctypes{$type} $cidr"; + my ($code,$rmsg) = initPool($dbh,$cidr,$type,$city,"normal"); + die $rmsg if $code eq 'FAIL'; } @@ -361,22 +381,39 @@ my $pool = new NetAddr::IP $_[1]; - my ($pooltype) = ($type =~ /^(.)p$/); +##fixme Need to just replace 2nd char of type with i rather than capturing 1st char of type + $type =~ s/[pd]$/i/; my $sth; - - # have to insert all pool IPs into poolips table as "unallocated". - $sth = $dbh->prepare("insert into poolips values ('$pool',". - " ?, '6750400', '$city', '$pooltype', 'y', '', '', '')"); - my @poolip_list = $pool->hostenum; - if ($class eq 'all') { # (DSL-ish block - *all* IPs available - $sth->execute($pool->addr); - for (my $i=0; $i<=$#poolip_list; $i++) { - $sth->execute($poolip_list[$i]->addr); - } - $pool--; - $sth->execute($pool->addr); - } else { # (real netblock) - for (my $i=1; $i<=$#poolip_list; $i++) { - $sth->execute($poolip_list[$i]->addr); - } + my $msg; + + # Trap errors so we can pass them back to the caller. Even if the + # caller is only ever supposed to be local, and therefore already + # trapping errors. >:( + local $dbh->{AutoCommit} = 0; # These need to be local so we don't + local $dbh->{RaiseError} = 1; # step on our toes by accident. + + eval { + # have to insert all pool IPs into poolips table as "unallocated". + $sth = $dbh->prepare("insert into poolips (pool,ip,custid,city,type)". + " values ('$pool', ?, '6750400', '$city', '$type')"); + my @poolip_list = $pool->hostenum; + if ($class eq 'all') { # (DSL-ish block - *all* IPs available + $sth->execute($pool->addr); + for (my $i=0; $i<=$#poolip_list; $i++) { + $sth->execute($poolip_list[$i]->addr); + } + $pool--; + $sth->execute($pool->addr); + } else { # (real netblock) + for (my $i=1; $i<=$#poolip_list; $i++) { + $sth->execute($poolip_list[$i]->addr); + } + } + }; + if ($@) { + $msg = "'".$sth->errstr."'"; + eval { $dbh->rollback; }; + return ('FAIL',$msg); + } else { + return ('OK',"OK"); } } # end initPool() @@ -406,5 +443,5 @@ eval { - $msg = "Unable to deallocate $type $cidr"; + $msg = "Unable to deallocate $disp_alloctypes{$type} $cidr"; $sth = $dbh->prepare("update poolips set custid='6750400',available='y',". "city=(select city from allocations where cidr >>= '$cidr'),". @@ -455,5 +492,5 @@ $sth->execute; # Set up query to start compacting free blocks. - $sth = $dbh->prepare("select * from freeblocks where ". + $sth = $dbh->prepare("select cidr from freeblocks where ". "maskbits<=".$cidr->masklen." and routed='n' order by maskbits desc"); @@ -463,5 +500,5 @@ $sth->execute; # Special case - delete pool IPs - if ($type =~ /^.p$/) { + if ($type =~ /^.[pd]$/) { # We have to delete the IPs from the pool listing. $sth = $dbh->prepare("delete from poolips where pool='$cidr'"); @@ -470,5 +507,5 @@ # Set up query for compacting free blocks. - $sth = $dbh->prepare("select * from freeblocks where cidr <<= ". + $sth = $dbh->prepare("select cidr from freeblocks where cidr <<= ". "(select cidr from routed where cidr >>= '$cidr') ". " and maskbits<=".$cidr->masklen." and routed='y' order by maskbits desc"); @@ -533,8 +570,9 @@ # insert "new" freeblocks entry if ($type eq 'rr') { - $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen. - ",'','n')"); + $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city)". + " values ('$cidr',".$cidr->masklen.",'')"); } else { - $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen. + $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)". + " values ('$cidr',".$cidr->masklen. ",(select city from routed where cidr >>= '$cidr'),'y')"); } Index: branches/stable/cgi-bin/main.cgi =================================================================== --- branches/stable/cgi-bin/main.cgi (revision 158) +++ branches/stable/cgi-bin/main.cgi (revision 159) @@ -57,27 +57,4 @@ cleanInput(\%webvar); -# Stuff that gets loaded from the database -#my @citylist; -#my @poplist; -#my %allocated; # Count for allocated blocks in a master block -#my %free; # Count for free blocks (routed and unrouted) in a master block -#my %bigfree; # Tracking largest free block in a master block -#my %routed; # Number of routed blocks in a master block - -# Slurp up the master block list - we need this several places -# While we're at it, initialize the related hashes. -#$sth = $ip_dbh->prepare("select * from masterblocks order by cidr"); -#$sth->execute; -#for (my $i=0; my @data = $sth->fetchrow_array(); $i++) { -# $masterblocks[$i] = new NetAddr::IP $data[0]; -# $allocated{"$masterblocks[$i]"} = 0; -# $free{"$masterblocks[$i]"} = 0; -# $bigfree{"$masterblocks[$i]"} = 128; # Larger number means smaller block. -# # Set to 128 to prepare for IPv6 -# $routed{"$masterblocks[$i]"} = 0; -#} - - - #main() @@ -110,6 +87,6 @@ # Thus the "routed" flag. - $sth = $ip_dbh->prepare("insert into freeblocks values ('$webvar{cidr}',". - $cidr->masklen.",'','n')"); + $sth = $ip_dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)". + " values ('$webvar{cidr}',".$cidr->masklen.",'','n')"); $sth->execute; @@ -349,4 +326,5 @@ # Fix up types from pools (which are single-char) # Fixing the database would be... painful. :( +##fixme LEGACY CODE if ($data[2] =~ /^[cdsmw]$/) { $data[2] .= 'i'; @@ -355,5 +333,5 @@ $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]); # Allow listing of pool if desired/required. - if ($data[2] =~ /^[cdsmw]p$/) { + if ($data[2] =~ /^.[pd]$/) { $row[0] .= '   List IPs"; @@ -494,5 +472,5 @@ # Fetch only the blocks relevant to this master - $sth = $ip_dbh->prepare("select * from routed where cidr <<= '$master' order by cidr"); + $sth = $ip_dbh->prepare("select cidr,city from routed where cidr <<= '$master' order by cidr"); $sth->execute(); @@ -505,5 +483,5 @@ $bigfree{"$cidr"} = 128; # Retain the routing destination - $routed{"$cidr"} = $data[2]; + $routed{"$cidr"} = $data[1]; } @@ -597,29 +575,30 @@ my $master = new NetAddr::IP $webvar{block}; - $sth = $ip_dbh->prepare("select * from routed where cidr='$master'"); + $sth = $ip_dbh->prepare("select city from routed where cidr='$master'"); $sth->execute; my @data = $sth->fetchrow_array; print qq(
Summarizing allocated blocks for ). - qq($master ($data[2]):

\n); + qq($master ($data[0]):
\n); startTable('CIDR allocation','Customer Location','Type','CustID','Description/Name'); # Snag the allocations for this block - $sth = $ip_dbh->prepare("select * from allocations where cidr <<= '$master' order by cidr"); + $sth = $ip_dbh->prepare("select cidr,city,type,custid,description". + " from allocations where cidr <<= '$master' order by cidr"); $sth->execute(); my $count=0; while (my @data = $sth->fetchrow_array()) { - # cidr,custid,type,city,description,notes,maskbits,circuitid + # cidr,city,type,custid,description, as per the SELECT my $cidr = new NetAddr::IP $data[0]; # Clean up extra spaces that are borking things. - $data[2] =~ s/\s+//g; +# $data[2] =~ s/\s+//g; my @row = ("$data[0]", - $data[3], $disp_alloctypes{$data[2]}, $data[1], $data[4]); + $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]); # If the allocation is a pool, allow listing of the IPs in the pool. - if ($data[2] =~ /^[cdsmw]p$/) { + if ($data[2] =~ /^.[pd]$/) { $row[0] .= '   List IPs"; @@ -654,5 +633,5 @@ # unrouted free blocks, but it's better to let the database do the work if we can. $count = 0; - $sth = $ip_dbh->prepare("select * from freeblocks where routed='y' and cidr <<= '$master' order by cidr"); + $sth = $ip_dbh->prepare("select cidr from freeblocks where routed='y' and cidr <<= '$master' order by cidr"); $sth->execute(); while (my @data = $sth->fetchrow_array()) { @@ -676,42 +655,49 @@ my $cidr = new NetAddr::IP $webvar{pool}; + my ($pooltype,$poolcity); + # Snag pool info for heading - $sth = $ip_dbh->prepare("select * from allocations where cidr='$cidr'"); + $sth = $ip_dbh->prepare("select type,city from allocations where cidr='$cidr'"); $sth->execute; - my @data = $sth->fetchrow_array; - my $type = $data[2]; # We'll need this later. + $sth->bind_columns(\$pooltype, \$poolcity); + $sth->fetch() || carp $sth->errstr; print qq(
Listing pool IPs for $cidr
\n). - qq(($disp_alloctypes{$type} in $data[3])

\n); - print qq(
Reserved IPs:
\n); - print qq(
|; } else { Index: branches/stable/changes.html =================================================================== --- branches/stable/changes.html (revision 158) +++ branches/stable/changes.html (revision 159) @@ -12,4 +12,10 @@ + + +
Network IP:). + qq(($disp_alloctypes{$pooltype} in $poolcity)
\n); + # Only display net/gw/bcast if it's a "real" netblock and not a PPP(oE) lunacy + if ($pooltype =~ /^.d$/) { + print qq(
Reserved IPs:
\n); + print qq(
\n"; - $cidr++; - print "\n"; - $cidr--; $cidr--; - print "\n". + $cidr++; + print "\n"; + $cidr--; $cidr--; + print "\n". "\n". "
Network IP:). $cidr->addr."
Gateway:".$cidr->addr."
Broadcast:".$cidr->addr."
Gateway:".$cidr->addr."
Broadcast:".$cidr->addr."
Netmask:".$cidr->mask."
\n"; + } # probably have to add an "edit IP allocation" link here somewhere. startTable('IP','Customer ID','Available?','Description',''); - $sth = $ip_dbh->prepare("select * from poolips where pool='$webvar{pool}' order by ip"); + $sth = $ip_dbh->prepare("select ip,custid,available,description,type". + " from poolips where pool='$webvar{pool}' order by ip"); $sth->execute; my $count = 0; while (my @data = $sth->fetchrow_array) { # pool,ip,custid,city,ptype,available,notes,description,circuitid - # If desc is null, make it not null. - if ($data[7] eq '') { - $data[7] = ' '; + # ip,custid,available,description,type + # If desc is "null", make it not null. + if ($data[3] eq '') { + $data[3] = ' '; } # Some nice hairy Perl to decide whether to allow unassigning each IP - # -> if $data[5] (aka poolips.available) == 'n' then we print the unassign link + # -> if $data[2] (aka poolips.available) == 'n' then we print the unassign link # else we print a blank space - my @row = ( qq($data[1]), - $data[2],$data[5],$data[7], - ( ($data[5] eq 'n') ? - ("Unassign this IP") : + my @row = ( qq($data[0]), + $data[1],$data[2],$data[3], + ( ($data[2] eq 'n') ? + ("Unassign this IP") : (" ") ) ); @@ -803,5 +789,5 @@ # + Different flavours of netblock - if ($webvar{alloctype} =~ /^[cdsmw]i$/) { + if ($webvar{alloctype} =~ /^.i$/) { my ($base,undef) = split //, $webvar{alloctype}; # split into individual chars my $sql; @@ -810,4 +796,6 @@ # ... aside from #^%#$%#@#^%^^!!!! legacy data. GRRR. # Note that we want to retain the requested city to relate to customer info. +##fixme This needs thought. +##SELECT DISTINCT pool, Count(*) FROM poolips where available='y' GROUP BY pool; if ($base =~ /^[ds]$/) { $sql = "select * from poolips where available='y' and". @@ -863,5 +851,5 @@ ##fixme # This section needs serious Pondering. - if ($webvar{alloctype} =~ /^[cdsmw]p$/) { + if ($webvar{alloctype} =~ /^.[pd]$/) { if (($webvar{city} !~ /^(Sudbury|North Bay)$/) && ($webvar{alloctype} eq 'dp')) { printError("You must chose Sudbury or North Bay for DSL pools."); @@ -879,8 +867,8 @@ } if ($webvar{allocfrom} ne '-') { - $sql = "select * from freeblocks where city='$city' and maskbits<=$webvar{maskbits}". + $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}". " and cidr <<= '$webvar{allocfrom}' and routed='y' order by cidr,maskbits desc"; } else { - $sql = "select * from freeblocks where city='$city' and maskbits<=$webvar{maskbits}". + $sql = "select cidr from freeblocks where city='$city' and maskbits<=$webvar{maskbits}". " and routed='y' order by cidr,maskbits desc"; } @@ -908,5 +896,5 @@ $cidr = $subblocks[0]; } - } # if ($webvar{alloctype} =~ /^[cdsmw]i$/) + } # if ($webvar{alloctype} =~ /^.i$/) open HTML, "../confirm.html" @@ -974,5 +962,5 @@ "'$webvar{alloctype}' by $authuser failed: '$msg'"; printError("Allocation of $webvar{fullcidr} as $disp_alloctypes{$webvar{alloctype}}". - " failed: $msg\n"); + " failed:
\n$msg\n"); } @@ -1083,4 +1071,5 @@ $data[2] =~ s/\s//; +##fixme LEGACY CODE # Postfix "i" on pool IP types if ($data[2] =~ /^[cdsmw]$/) { @@ -1145,5 +1134,5 @@ # Relatively simple SQL transaction here. my $sql; - if (my $pooltype = ($webvar{alloctype} =~ /^([cdsmw])i$/) ) { + if (my $pooltype = ($webvar{alloctype} =~ /^(.)i$/) ) { $sql = "update poolips set custid='$webvar{custid}',notes='$webvar{notes}',". "circuitid='$webvar{circid}',description='$webvar{desc}' ". @@ -1154,5 +1143,6 @@ "type='$webvar{alloctype}',circuitid='$webvar{circid}' where cidr='$webvar{block}'"; } -syslog "debug", $sql; + # Log the details of the change. + syslog "debug", $sql; $sth = $ip_dbh->prepare($sql); $sth->execute; @@ -1232,5 +1222,5 @@ $desc = "N/A"; $notes = "N/A"; - } elsif ($webvar{alloctype} =~ /^[cdsmw]i$/) { # done with alloctype=rr + } elsif ($webvar{alloctype} =~ /^.i$/) { # done with alloctype=rr # Unassigning a static IP @@ -1245,5 +1235,5 @@ $alloctype .="i"; - } else { # done with alloctype=[cdsmw]i + } else { # done with alloctype=~ /^.i$/ my $sth = $ip_dbh->prepare("select cidr,custid,type,city,circuitid,description,notes from ". @@ -1270,5 +1260,5 @@ # Set the warning text. - if ($alloctype =~ /^[cdsmw]p$/) { + if ($alloctype =~ /^.[pd]$/) { $html =~ s||
Warning: clicking confirm will remove this record entirely.
Any IPs allocated from this pool will also be removed!
Changes to the IPDB
02/09/2005Pool handling has been cleaned up - all PPP-ish pools (modem, DSL, WiFi) now generate a +full list of IPs rather than reserving the network, gateway, and broadcast IPs necessary on +a "real" subnet. They will also not display this incorrect and useless information. +
01/27/2005