Changeset 823 for trunk

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.

Location:

trunk

Files:

• cgi-bin/search.cgi (modified) (17 diffs)
• templates/search/sresults.tmpl (modified) (2 diffs)

trunk/cgi-bin/search.cgi

@@ -74 +74 @@
 my @templatepath = [ "localtemplates", "templates" ];
 
+## FIXME!
+## Pretty much everything from here on down is one giant FIXME
+## FIXME!
+
 my $page;
 if (!defined($webvar{stype})) {
@@ -91 +95 @@
 # Columns actually returned.  Slightly better than hardcoding it
 # in each (sub)select
-my $cols = "cidr,custid,type,city,description,id,parent_id,available";
+my $cols = "s.cidr, s.custid, s.type, s.city, s.description, s.id, s.parent_id, s.available, a.vrf";
 
 # Handle the DB error first
@@ -158 +162 @@
 
   # First chunk of SQL.  Filter on custid, description, and notes as necessary.
-  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}')";
+  my $sql = qq(SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id\n);
+  $sql .= " WHERE $webvar{custexclude} (s.custid ~ '$webvar{custid}')\n" if $webvar{custid};
+  $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};
+  $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};
 
   # If we're not supposed to search for all types, search for the selected types.
@@ -167 +171 @@
   $webvar{typeexclude} = '' if !$webvar{typeexclude};
   if ($webvar{alltypes} ne 'on') {
-    $sql .= " $sqlconcat (select $cols from searchme where $webvar{typeexclude} type in (";
+    $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{typeexclude} s.type IN (";
     foreach my $key (keys %webvar) {
       $sql .= "'$1'," if $key =~ /type\[(..)\]/;
@@ -180 +184 @@
   $webvar{cityexclude} = '' if !$webvar{cityexclude};
   if ($webvar{allcities} ne 'on') {
-    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cityexclude} city in (";
-    $sth = $ip_dbh->prepare("select city from cities where id=?");
+    $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE $webvar{cityexclude} s.city IN (";
+    $sth = $ip_dbh->prepare("SELECT city FROM cities WHERE id=?");
     foreach my $key (keys %webvar) {
       if ($key =~ /city\[(\d+)\]/) {
@@ -205 +209 @@
       # /0->/9 are silly to worry about right now.  I don't think
       # we'll be getting a class A anytime soon.  <g>
-      $sql .= " $sqlconcat (select $cols from searchme where ".
-        "$webvar{cidrexclude} cidr<<='$webvar{cidr}')";
+      $sql .= " $sqlconcat (SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id WHERE ".
+        "$webvar{cidrexclude} s.cidr<<='$webvar{cidr}')";
     } else {
       # Partial match;  beginning of subnet and maskbits are provided
@@ -212 +216 @@
       # Need some more magic for bare /nn searches:
       my $condition = ($net eq '' ?
-        "masklen(cidr)=$maskbits" : "text(cidr) like '$net%' and masklen(cidr)=$maskbits");
-      $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
+        "masklen(s.cidr)=$maskbits" : "text(s.cidr) like '$net%' and masklen(s.cidr)=$maskbits");
+      $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
         "($condition))";
     }
@@ -219 +223 @@
     # Specific IP address match.  Will show either a single netblock,
     # or a static pool plus an IP.
-    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
-        "cidr >>= '$webvar{cidr}')";
+    $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
+        "s.cidr >>= '$webvar{cidr}')";
   } elsif ($webvar{cidr} =~ /^\d{1,3}(\.(\d{1,3}(\.(\d{1,3}\.?)?)?)?)?$/) {
     # Leading octets in CIDR
-    $sql .= " $sqlconcat (select $cols from searchme where $webvar{cidrexclude} ".
-        "text(cidr) like '$webvar{cidr}%')";
+    $sql .= " $sqlconcat (select $cols from searchme s JOIN allocations a ON s.master_id=a.id where $webvar{cidrexclude} ".
+        "text(s.cidr) like '$webvar{cidr}%')";
   } else {
     # do nothing.
@@ -235 +239 @@
   # Find out how many rows the "core" query will return.
   my $count = countRows($sql);
+
+# join against yourself!  only master blocks are really guaranteed to have a VRF set - especially in legacy data
+#$sql .= " JOIN allocations mv ON 
 
   if ($count == 0) {
@@ -351 +358 @@
   if ($category eq 'all') {
 
-    $sql = "select $cols from searchme";
+    $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id";
     my $count = countRows($sql);
-    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+    $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
     queryResults($sql, $webvar{page}, $count);
 
@@ -363 +370 @@
     # Query for a customer ID.  Note that we can't restrict to "numeric-only"
     # as we have non-numeric custIDs in the legacy data.  :/
-    $sql = "select $cols from searchme where custid ilike '%$query%' or description like '%$query%'";
+    $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%'";
     my $count = countRows($sql);
-    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+    $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
     queryResults($sql, $webvar{page}, $count);
 
@@ -372 +379 @@
     print qq(<div class="heading">Searching for descriptions containing '$query'</div><br>\n);
     # Query based on description (includes "name" from old DB).
-    $sql = "select $cols from searchme where description ilike '%$query%'".
-        " or custid ilike '%$query%'";
+    $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.description ilike '%$query%'".
+        " or s.custid ilike '%$query%'";
     my $count = countRows($sql);
-    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+    $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
     queryResults($sql, $webvar{page}, $count);
 
@@ -390 +397 @@
         # /0->/9 are silly to worry about right now.  I don't think
         # we'll be getting a class A anytime soon.  <g>
-        $sql = "select $cols from searchme where cidr='$query'";
+        $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$query'";
         queryResults($sql, $webvar{page}, 1);
       } else {
         #print "Finding all blocks with netmask /$maskbits, leading octet(s) $net<br>\n";
         # Partial match;  beginning of subnet and maskbits are provided
-        $sql = "select $cols from searchme where text(cidr) like '$net%' and ".
-                "text(cidr) like '%$maskbits'";
+        $sql = "select $cols from searchme s JOIN allocations a ON s.master_id=a.id".
+                " where text(s.cidr) like '$net%' and text(s.cidr) like '%$maskbits'";
         my $count = countRows($sql);
-        $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+        $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
         queryResults($sql, $webvar{page}, $count);
       }
@@ -406 +413 @@
       my ($net,$ip) = ($query =~ /(\d{1,3}\.\d{1,3}\.\d{1,3}\.)(\d{1,3})/);
       my $sfor = new NetAddr::IP $query;
-      $sth = $ip_dbh->prepare("select $cols from searchme where text(cidr) like '$net%'");
-      $sth->execute;
-      while (my @data = $sth->fetchrow_array()) {
-        my $cidr = new NetAddr::IP $data[0];
-        if ($cidr->contains($sfor)) {
-          queryResults("select $cols from searchme where cidr='$cidr'", $webvar{page}, 1);
-        }
-      }
+#      $sth = $ip_dbh->prepare("select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'");
+#print "select $cols from searchme s JOIN allocations a ON s.master_id=a.id where text(s.cidr) like '$net%'";
+
+#      $sth->execute;
+#      while (my @data = $sth->fetchrow_array()) {
+#        my $cidr = new NetAddr::IP $data[0];
+#       if ($cidr->contains($sfor) || $cidr == $sfor) {
+#print "cidr: $data[0]\n";
+#print "<br>select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'";
+          queryResults(
+#"select $cols from searchme s JOIN allocations a ON s.master_id=a.id where s.cidr='$cidr' and s.type <> 'mm'",
+"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",
+ $webvar{page}, 1);
+#print $page->output;
+#       }
+#      }
     } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
       #print "Finding matches with leading octet(s) $query<br>\n";
-      $sql = "select $cols from searchme where text(cidr) like '$query%'";
+      $sql = "SELECT $cols FROM searchme s JOIN allocations a ON s.master_id=a.id".
+        " WHERE text(s.cidr) LIKE '$query%'";
       my $count = countRows($sql);
-      $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+      $sql .= " order by s.cidr limit $RESULTS_PER_PAGE offset $offset";
       queryResults($sql, $webvar{page}, $count);
     } else {
@@ -431 +447 @@
 
 
-# args are: a reference to an array with the row to be printed and the 
-# class(stylesheet) to use for formatting.
-# if ommitting the class - call the sub as &printRow(\@array)
-sub printRow {
-  my ($rowRef,$class) = @_;
-
-  if (!$class) {
-    print "<tr>\n";
-  } else {
-    print "<tr class=\"$class\">\n";
-  }
-
-ELEMENT:  foreach my $element (@$rowRef) {
-    if (!defined($element)) {
-      print "<td></td>\n";
-      next ELEMENT;
-    }
-    $element =~ s|\n|</br>|g;
-    print "<td>$element</td>\n";
-  }
-  print "</tr>";
-} # printRow
-
 
 # queryResults()
@@ -470 +463 @@
   my $count = 0;
   my @sresults;
-  while (my ($block, $custid, $type, $city, $desc, $id, $parent, $avail) = $sth->fetchrow_array) {
+  while (my ($block, $custid, $type, $city, $desc, $id, $parent, $avail, $vrf) = $sth->fetchrow_array) {
     my %row = (
         rowclass => $count++ % 2,
+        vrf => $vrf,
         issub => ($type =~ /^.r$/ ? 1 : 0),
         ispool => ($type =~ /^.[pd]$/ ? 1 : 0),
@@ -532 +526 @@
 
 
-# Prints table headings.  Accepts any number of arguments;
-# each argument is a table heading.
-sub startTable {
-  print qq(<center><table width="98%" cellspacing="0" class="center"><tr>);
-
-  foreach(@_) {
-    print qq(<td class="heading">$_</td>);
-  }
-  print "</tr>\n";
-} # startTable
-
 
 # Return count of rows to be returned in a "real" query

trunk/templates/search/sresults.tmpl

@@ -9 +9 @@
 
 <tr>
+<td class="heading">VRF</td>
 <td class="heading">Allocation</td>
 <td class="heading">CustID</td>
@@ -17 +18 @@
 <TMPL_LOOP NAME=sresults>
 <tr class="row<TMPL_VAR NAME=rowclass>">
+<td><TMPL_VAR NAME=vrf></td>
 <td><TMPL_IF issub>Sub </TMPL_IF>
 <TMPL_IF freeip>