Changeset 670 for trunk/cgi-bin

Timestamp:

01/02/15 17:26:32 (10 years ago)

Author:

Kris Deugau

Message:

/trunk

Minimally update search tool for new database layout. Still needs a major rewrite.

Location:

trunk/cgi-bin

Files:

• ipdb-2.7-3.0.sql (modified) (1 diff)
• search.cgi (modified) (6 diffs)

trunk/cgi-bin/ipdb-2.7-3.0.sql

@@ -32 +32 @@
 -- probably need some more indexes
 
+-- Need some additional fields to support search output.  Might arguably
+-- move pool IPs into the allocations table;  we'll see.
+DROP VIEW searchme;
+CREATE VIEW "searchme" AS
+    SELECT allocations.cidr, allocations.custid, allocations."type", allocations.city, 
+      allocations.description, allocations.notes, allocations.circuitid, allocations.id,
+      allocations.parent_id, 'n' AS available
+    FROM allocations
+  UNION
+    SELECT poolips.ip, poolips.custid, poolips.type, poolips.city,
+      poolips.description, poolips.notes, poolips.circuitid, poolips.id,
+      poolips.parent_id, poolips.available
+    FROM poolips;
+
 -- Relabel a few types to match the new structure
 UPDATE alloctypes SET listname='Routing aggregation', dispname='Routing aggregation' WHERE type='rm';

trunk/cgi-bin/search.cgi

@@ -78 +78 @@
   $page = HTML::Template->new(filename => "search/compsearch.tmpl");
 } else {
-  $page = HTML::Template->new(filename => "search/sresults.tmpl");
+  $page = HTML::Template->new(filename => "search/sresults.tmpl", global_vars => 1);
+  $page->param(webpath => $IPDB::webpath);
 }
 
@@ -84 +85 @@
 $header->param(version => $IPDB::VERSION);
 $header->param(addperm => $IPDBacl{$authuser} =~ /a/);
+$header->param(webpath => $IPDB::webpath);
 print "Content-type: text/html\n\n", $header->output;
+
+# Columns actually returned.  Slightly better than hardcoding it
+# in each (sub)select
+my $cols = "cidr,custid,type,city,description,id,parent_id,available";
 
 # Handle the DB error first
@@ -141 +147 @@
 # Iff something has NOT been entered, we still include it but in
 # such a way that it does not actually filter anything out.
-
-  # Columns actually returned.  Slightly better than hardcoding it
-  # in each (sub)select
-  my $cols = "cidr,custid,type,city,description";
 
   # hack fix for undefined variables
@@ -248 +250 @@
   # Node search.
 
-  my $sql = "SELECT cidr,custid,type,city,description FROM searchme".
+  my $sql = "SELECT $cols FROM searchme".
         " WHERE cidr IN (SELECT block FROM noderef WHERE node_id=$webvar{node})";
 
@@ -345 +347 @@
 # 5) Invalid data which might be interpretable as an IP or something, but
 #    which probably shouldn't be for reasons of sanity.
-
-  my $cols = "cidr,custid,type,city,description";
 
   if ($category eq 'all') {
@@ -469 +469 @@
   my $count = 0;
   my @sresults;
-  while (my ($block, $custid, $type, $city, $desc) = $sth->fetchrow_array) {
+  while (my ($block, $custid, $type, $city, $desc, $id, $parent, $avail) = $sth->fetchrow_array) {
     my %row = (
         rowclass => $count++ % 2,
         issub => ($type =~ /^.r$/ ? 1 : 0),
+        ispool => ($type =~ /^.[pd]$/ ? 1 : 0),
+        basetype => ($type =~ /^.i/ ? 'i' : 'b'),
+        freeip => ($avail eq 'y'),
+        parent => $parent,
         block => $block,
-        ispool => ($type =~ /^.[pd]$/ ? 1 : 0),
         custid => $custid,
         disptype => $disp_alloctypes{$type},
         city => $city,
-        desc => $desc
+        desc => $desc,
+        id => $id,
         );
     push @sresults, \%row;