Changeset 147 for branches/sql-cleanup


Ignore:
Timestamp:
02/01/05 17:43:34 (20 years ago)
Author:
Kris Deugau
Message:

/branches/sql-cleanup

First round of SQL cleanup and fixes - IPDB.pm

File:
1 edited

Legend:

Unmodified
Added
Removed

  • branches/sql-cleanup/cgi-bin/IPDB.pm

    r135 r147  
    5858
    5959  # Initialize alloctypes hashes
    60   $sth = $dbh->prepare("select * from alloctypes order by listorder");
     60  $sth = $dbh->prepare("select type,listname,dispname,listorder from alloctypes order by listorder");
    6161  $sth->execute;
    6262  while (my @data = $sth->fetchrow_array) {
    … …  
    6868
    6969  # City and POP listings
    70   $sth = $dbh->prepare("select * from cities order by city");
     70  $sth = $dbh->prepare("select city,routing from cities order by city");
    7171  $sth->execute;
    7272  return (undef,$sth->errstr) if $sth->err;
    … …  
    7979
    8080  # Master block list
    81   $sth = $dbh->prepare("select * from masterblocks order by cidr");
     81  $sth = $dbh->prepare("select cidr from masterblocks order by cidr");
    8282  $sth->execute;
    8383  for (my $i=0; my @data = $sth->fetchrow_array(); $i++) {
    … …  
    120120# Return here if we can't select.  Note that this indicates a
    121121# problem executing the select.
    122   my $sth = $dbh->prepare('select cidr from masterblocks');
     122  my $sth = $dbh->prepare("select cidr from masterblocks");
    123123  $sth->execute();
    124124  return (undef,$DBI::errstr) if ($sth->err);
    … …  
    154154  } else {
    155155    # it connects, try a stmt.
    156     my $sth = $dbh->prepare('select cidr from masterblocks');
     156    my $sth = $dbh->prepare("select cidr from masterblocks");
    157157    my $err = $sth->execute();
    158158
    … …  
    192192    $msg = "Unable to assign static IP $cidr to $custid";
    193193    eval {
    194       # We'll just have to put up with the oddities caused by SQL (un)sort order
    195       $sth = $dbh->prepare("select * from poolips where pool='$alloc_from'".
    196         " and available='y' order by ip");
    197       $sth->execute;
    198 
     194      # We have to do this in two parts because otherwise we lose
     195      # the ability to return the IP assigned.  Should that change,
     196      # the commented SQL statement below may become usable.
    199197# update poolips set custid='$custid',city='$city',available='n',
    200198#       description='$desc',notes='$notes',circuitid='$circid'
    201199#       where ip=(select ip from poolips where pool='$alloc_from'
    202200#       and available='y' order by ip limit 1);
    203 ##err Need better handling here;  what if there's no free IPs when this sub gets called?
     201
     202      $sth = $dbh->prepare("select ip from poolips where pool='$alloc_from'".
     203        " and available='y' order by ip");
     204      $sth->execute;
     205
    204206      my @data = $sth->fetchrow_array;
    205       $cidr = $data[1];  # $cidr is already declared when we get here!
     207      $cidr = $data[0];  # $cidr is already declared when we get here!
    206208
    207209      $sth = $dbh->prepare("update poolips set custid='$custid',".
    … …  
    234236            " where cidr='$cidr'");
    235237          $sth->execute;
    236           $sth = $dbh->prepare("insert into routed values ('$cidr',".
    237             $cidr->masklen.",'$city')");
     238          $sth = $dbh->prepare("insert into routed (cidr,maskbits,city)".
     239                " values ('$cidr',".$cidr->masklen.",'$city')");
    238240          $sth->execute;
    239241        } else {
    … …  
    242244          $sth->execute;
    243245
    244           $sth = $dbh->prepare("insert into allocations values ('$cidr',".
    245                 "'$custid','$type','$city','$desc','$notes',".
     246          $sth = $dbh->prepare("insert into allocations".
     247                " (cidr,custid,type,city,description,notes,maskbits,circuitid)".
     248                " values ('$cidr','$custid','$type','$city','$desc','$notes',".
    246249                $cidr->masklen.",'$circid')");
    247250          $sth->execute;
    … …  
    297300          # Insert the new freeblocks entries
    298301          # Note that non-routed blocks are assigned to <NULL>
    299           $sth = $dbh->prepare("insert into freeblocks values (?, ?, '<NULL>','n')");
     302          # and use the default value for the routed column ('n')
     303          $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city)".
     304                " values (?, ?, '<NULL>')");
    300305          foreach my $block (@newfreeblocks) {
    301306            $sth->execute("$block", $block->masklen);
    … …  
    303308
    304309          # Insert the entry in the routed table
    305           $sth = $dbh->prepare("insert into routed values ('$cidr',".
    306             $cidr->masklen.",'$city')");
     310          $sth = $dbh->prepare("insert into routed (cidr,maskbits,city)".
     311                " values ('$cidr',".$cidr->masklen.",'$city')");
    307312          $sth->execute;
    308313          # Insert the (almost) same entry in the freeblocks table
    309           $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".
    310             $cidr->masklen.",'$city','y')");
     314          $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
     315                " values ('$cidr',".$cidr->masklen.",'$city','y')");
    311316          $sth->execute;
    312317
    … …  
    314319
    315320          # Insert the new freeblocks entries
    316           $sth = $dbh->prepare("insert into freeblocks values (?, ?, ".
    317                 "(select city from routed where cidr >>= '$cidr'),'y')");
     321          $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
     322                " values (?, ?, (select city from routed where cidr >>= '$cidr'),'y')");
    318323          foreach my $block (@newfreeblocks) {
    319324            $sth->execute("$block", $block->masklen);
    … …  
    321326
    322327          # Insert the allocations entry
    323           $sth = $dbh->prepare("insert into allocations values ('$cidr',".
    324                 "'$custid','$type','$city','$desc','$notes',".$cidr->masklen.
    325                 ",'$circid')");
     328          $sth = $dbh->prepare("insert into allocations (cidr,custid,type,city,".
     329                "description,notes,maskbits,circuitid)".
     330                " values ('$cidr','$custid','$type','$city','$desc','$notes',".
     331                $cidr->masklen.",'$circid')");
    326332          $sth->execute;
    327333
    … …  
    365371
    366372  # have to insert all pool IPs into poolips table as "unallocated".
    367   $sth = $dbh->prepare("insert into poolips values ('$pool',".
    368         " ?, '6750400', '$city', '$pooltype', 'y', '', '', '')");
     373  $sth = $dbh->prepare("insert into poolips (pool,ip,custid,city,ptype)".
     374        " values ('$pool', ?, '6750400', '$city', '$pooltype'");
    369375  my @poolip_list = $pool->hostenum;
    370376  if ($class eq 'all') { # (DSL-ish block - *all* IPs available
    … …  
    455461        $sth->execute;
    456462        # Set up query to start compacting free blocks.
    457         $sth = $dbh->prepare("select * from freeblocks where ".
     463        $sth = $dbh->prepare("select cidr from freeblocks where ".
    458464                "maskbits<=".$cidr->masklen." and routed='n' order by maskbits desc");
    459465
    … …  
    470476
    471477        # Set up query for compacting free blocks.
    472         $sth = $dbh->prepare("select * from freeblocks where cidr <<= ".
     478        $sth = $dbh->prepare("select cidr from freeblocks where cidr <<= ".
    473479                "(select cidr from routed where cidr >>= '$cidr') ".
    474480                " and maskbits<=".$cidr->masklen." and routed='y' order by maskbits desc");
    … …  
    513519      # insert "new" freeblocks entry
    514520      if ($type eq 'rr') {
    515         $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen.
    516                 ",'<NULL>','n')");
     521        $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city)".
     522                " values ('$cidr',".$cidr->masklen.",'<NULL>')");
    517523      } else {
    518         $sth = $dbh->prepare("insert into freeblocks values ('$cidr',".$cidr->masklen.
     524        $sth = $dbh->prepare("insert into freeblocks (cidr,maskbits,city,routed)".
     525                " values ('$cidr',".$cidr->masklen.
    519526                ",(select city from routed where cidr >>= '$cidr'),'y')");
    520527      }
Note: See TracChangeset for help on using the changeset viewer.