Changeset 823 for trunk/cgi-bin


Ignore:
Timestamp:
03/31/16 17:58:33 (9 years ago)
Author:
Kris Deugau
Message:

/trunk

Hack-patch search to show VRF. Needs complete rewrite as per #4.

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/cgi-bin/search.cgi

    r801 r823  
    7474my @templatepath = [ "localtemplates", "templates" ];
    7575
     76## FIXME!
     77## Pretty much everything from here on down is one giant FIXME
     78## FIXME!
     79
    7680my $page;
    7781if (!defined($webvar{stype})) {
    … …  
    9195# Columns actually returned.  Slightly better than hardcoding it
    9296# in each (sub)select
    93 my $cols = "cidr,custid,type,city,description,id,parent_id,available";
     97my $cols = "s.cidr, s.custid, s.type, s.city, s.description, s.id, s.parent_id, s.available, a.vrf";
    9498
    9599# Handle the DB error first
    … …  
    158162
    159163  # First chunk of SQL.  Filter on custid, description, and notes as necessary.
    160   my $sql = qq(SELECT $cols FROM searchme\n);
    161   $sql .= " WHERE $webvar{custexclude} (custid ~ '$webvar{custid}')\n";
    162   $sql .= " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ~ '$webvar{desc}')\n";
    163   $sql .= " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ~ '$webvar{notes}')";
     164  my $sql = qq(SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id\n);
     165  $sql .= " WHERE $webvar{custexclude} (s.custid ~ '$webvar{custid}')\n" if $webvar{custid};
     166  $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{descexclude} s.description ~ '$webvar{desc}')\n" if $webvar{desc};
     167  $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{notesexclude} s.notes ~ '$webvar{notes}')" if $webvar{notes};
    164168
    165169  # If we're not supposed to search for all types, search for the selected types.
    … …  
    167171  $webvar{typeexclude} = '' if !$webvar{typeexclude};
    168172  if ($webvar{alltypes} ne 'on') {
    169     $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
     173    $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{typeexclude} s.type IN (";
    170174    foreach my $key (keys %webvar) {
    171175      $sql .= "'$1'," if $key =~ /type\[(..)\]/;
    … …  
    180184  $webvar{cityexclude} = '' if !$webvar{cityexclude};
    181185  if ($webvar{allcities} ne 'on') {
    182     $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
    183     $sth = $ip_dbh->prepare("select city from cities where id=?");
     186    $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{cityexclude} s.city IN (";
     187    $sth = $ip_dbh->prepare("SELECT city FROM cities WHERE id=?");
    184188    foreach my $key (keys %webvar) {
    185189      if ($key =~ /city\[(\d+)\]/) {
    … …  
    205209      # /0->/9 are silly to worry about right now.  I don't think
    206210      # we'll be getting a class A anytime soon.  <g>
    207       $sql .= " $sqlconcat (select $cols from searchme where ".
    208         "$webvar{cidrexclude} cidr<<='$webvar{cidr}')";
     211      $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE ".
     212        "$webvar{cidrexclude} s.cidr<<='$webvar{cidr}')";
    209213    } else {
    210214      # Partial match;  beginning of subnet and maskbits are provided
    … …  
    212216      # Need some more magic for bare /nn searches:
    213217      my $condition = ($net eq '' ?
    214         "masklen(cidr)=$maskbits" : "text(cidr) like '$net%' and masklen(cidr)=$maskbits");
    215       $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
     218        "masklen(s.cidr)=$maskbits" : "text(s.cidr) like '$net%' and masklen(s.cidr)=$maskbits");
     219      $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
    216220        "($condition))";
    217221    }
    … …  
    219223    # Specific IP address match.  Will show either a single netblock,
    220224    # or a static pool plus an IP.
    221     $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
    222         "cidr >>= '$webvar{cidr}')";
     225    $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
     226        "s.cidr >>= '$webvar{cidr}')";
    223227  } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) {
    224228    # Leading octets in CIDR
    225     $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
    226         "text(cidr) like '$webvar{cidr}%')";
     229    $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
     230        "text(s.cidr) like '$webvar{cidr}%')";
    227231  } else {
    228232    # do nothing.
    … …  
    235239  # Find out how many rows the "core" query will return.
    236240  my $count = countRows($sql);
     241
     242# join against yourself!  only master blocks are really guaranteed to have a VRF set - especially in legacy data
     243#$sql .= " JOIN allocations mv ON
    237244
    238245  if ($count == 0) {
    … …  
    351358  if ($category eq 'all') {
    352359
    353     $sql = "select $cols from searchme";
     360    $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id";
    354361    my $count = countRows($sql);
    355     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     362    $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    356363    queryResults($sql, $webvar{page}, $count);
    357364
    … …  
    363370    # Query for a customer ID.  Note that we can't restrict to "numeric-only"
    364371    # as we have non-numeric custIDs in the legacy data.  :/
    365     $sql = "select $cols from searchme where custid ilike '%$query%' or description like '%$query%'";
     372    $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.custid ilike '%$query%' or s.description like '%$query%'";
    366373    my $count = countRows($sql);
    367     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     374    $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    368375    queryResults($sql, $webvar{page}, $count);
    369376
    … …  
    372379    print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
    373380    # Query based on description (includes "name" from old DB).
    374     $sql = "select $cols from searchme where description ilike '%$query%'".
    375         " or custid ilike '%$query%'";
     381    $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.description ilike '%$query%'".
     382        " or s.custid ilike '%$query%'";
    376383    my $count = countRows($sql);
    377     $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     384    $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    378385    queryResults($sql, $webvar{page}, $count);
    379386
    … …  
    390397        # /0->/9 are silly to worry about right now.  I don't think
    391398        # we'll be getting a class A anytime soon.  <g>
    392         $sql = "select $cols from searchme where cidr='$query'";
     399        $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$query'";
    393400        queryResults($sql, $webvar{page}, 1);
    394401      } else {
    395402        #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
    396403        # Partial match;  beginning of subnet and maskbits are provided
    397         $sql = "select $cols from searchme where text(cidr) like '$net%' and ".
    398                 "text(cidr) like '%$maskbits'";
     404        $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id".
     405                " where text(s.cidr) like '$net%' and text(s.cidr) like '%$maskbits'";
    399406        my $count = countRows($sql);
    400         $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     407        $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    401408        queryResults($sql, $webvar{page}, $count);
    402409      }
    … …  
    406413      my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/);
    407414      my $sfor = new NetAddr::IP $query;
    408       $sth = $ip_dbh->prepare("select $cols from searchme where text(cidr) like '$net%'");
    409       $sth->execute;
    410       while (my @data = $sth->fetchrow_array()) {
    411         my $cidr = new NetAddr::IP $data[0];
    412         if ($cidr->contains($sfor)) {
    413           queryResults("select $cols from searchme where cidr='$cidr'", $webvar{page}, 1);
    414         }
    415       }
     415#      $sth = $ip_dbh->prepare("select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'");
     416#print "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'";
     417
     418#      $sth->execute;
     419#      while (my @data = $sth->fetchrow_array()) {
     420#        my $cidr = new NetAddr::IP $data[0];
     421#       if ($cidr->contains($sfor) || $cidr == $sfor) {
     422#print "cidr: $data[0]\n";
     423#print "<br>select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'";
     424          queryResults(
     425#"select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'",
     426"select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr >>= '$sfor' and s.type <> 'mm' order by masklen(s.cidr) desc",
     427 $webvar{page}, 1);
     428#print $page->output;
     429#       }
     430#      }
    416431    } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
    417432      #print "Finding matches with leading octet(s) $query<br>\n";
    418       $sql = "select $cols from searchme where text(cidr) like '$query%'";
     433      $sql = "SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id".
     434        " WHERE text(s.cidr) LIKE '$query%'";
    419435      my $count = countRows($sql);
    420       $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     436      $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
    421437      queryResults($sql, $webvar{page}, $count);
    422438    } else {
    … …  
    431447
    432448
    433 # args are: a reference to an array with the row to be printed and the
    434 # class(stylesheet) to use for formatting.
    435 # if ommitting the class - call the sub as &printRow(\@array)
    436 sub printRow {
    437   my ($rowRef,$class) = @_;
    438 
    439   if (!$class) {
    440     print "<tr>\n";
    441   } else {
    442     print "<tr class=\"$class\">\n";
    443   }
    444 
    445 ELEMENT:  foreach my $element (@$rowRef) {
    446     if (!defined($element)) {
    447       print "<td></td>\n";
    448       next ELEMENT;
    449     }
    450     $element =~ s|\n|</br>|g;
    451     print "<td>$element</td>\n";
    452   }
    453   print "</tr>";
    454 } # printRow
    455 
    456449
    457450# queryResults()
    … …  
    470463  my $count = 0;
    471464  my @sresults;
    472   while (my ($block, $custid, $type, $city, $desc, $id, $parent, $avail) = $sth->fetchrow_array) {
     465  while (my ($block, $custid, $type, $city, $desc, $id, $parent, $avail, $vrf) = $sth->fetchrow_array) {
    473466    my %row = (
    474467        rowclass => $count++ % 2,
     468        vrf => $vrf,
    475469        issub => ($type =~ /^.r$/ ? 1 : 0),
    476470        ispool => ($type =~ /^.[pd]$/ ? 1 : 0),
    … …  
    532526
    533527
    534 # Prints table headings.  Accepts any number of arguments;
    535 # each argument is a table heading.
    536 sub startTable {
    537   print qq(<center><table width="98%" cellspacing="0" class="center"><tr>);
    538 
    539   foreach(@_) {
    540     print qq(<td class="heading">$_</td>);
    541   }
    542   print "</tr>\n";
    543 } # startTable
    544 
    545528
    546529# Return count of rows to be returned in a "real" query
Note: See TracChangeset for help on using the changeset viewer.