Index: trunk/cgi-bin/main.cgi
===================================================================
--- trunk/cgi-bin/main.cgi (revision 233)
+++ trunk/cgi-bin/main.cgi (revision 234)
@@ -46,11 +46,9 @@
# Headerize! Make sure we replace the $$EXTRA0$$ bit as needed.
printHeader('', ($IPDBacl{$authuser} =~ /a/ ?
- 'Add new assignment' : ''
+ 'Add new assignment' : ''
));
#prototypes
-sub viewBy($$); # feed it the category and query
-sub queryResults($$$); # args is the sql, the page# and the rowCount
# Needs rewrite/rename
sub countRows($); # returns first element of first row of passed SQL
@@ -132,14 +130,4 @@
elsif($webvar{action} eq 'listpool') {
listPool();
-}
-elsif($webvar{action} eq 'search') {
- if (!$webvar{input}) {
- # No search term. Display everything.
- viewBy('all', '');
- } else {
- # Search term entered. Display matches.
- # We should really sanitize $webvar{input}, no?
- viewBy($webvar{searchfor}, $webvar{input});
- }
}
@@ -198,112 +186,4 @@
-sub viewBy($$) {
- my ($category,$query) = @_;
-
- # Local variables
- my $sql;
-
-#print "\n";
-
-#print "start querysub: query '$query'\n";
-# this may happen with more than one subcategory. Unlikely, but possible.
-
- # Calculate start point for LIMIT clause
- my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
-
-# Possible cases:
-# 1) Partial IP/subnet. Treated as "first-three-octets-match" in old IPDB,
-# I should be able to handle it similarly here.
-# 2a) CIDR subnet. Treated more or less as such in old IPDB.
-# 2b) CIDR netmask. Not sure how it's treated.
-# 3) Customer ID. Not handled in old IPDB
-# 4) Description.
-# 5) Invalid data which might be interpretable as an IP or something, but
-# which probably shouldn't be for reasons of sanity.
-
- if ($category eq 'all') {
-
- print qq(Showing all netblock and static-IP allocations
\n);
-
- # Need to assemble SQL query in this order to avoid breaking things.
- $sql = "select cidr,custid,type,city,description from searchme";
- my $count = countRows("select count(*) from ($sql) foo");
- $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
- queryResults($sql, $webvar{page}, $count);
-
- } elsif ($category eq 'cust') {
-
- print qq(Searching for Customer IDs containing '$query'
\n);
-
- # 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 cidr,custid,type,city,description from searchme where custid ilike '%$query%'";
- my $count = countRows("select count(*) from ($sql) foo");
- $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
- queryResults($sql, $webvar{page}, $count);
-
- } elsif ($category eq 'desc') {
-
- print qq(Searching for descriptions containing '$query'
\n);
- # Query based on description (includes "name" from old DB).
- $sql = "select cidr,custid,type,city,description from searchme where description ilike '%$query%'";
- my $count = countRows("select count(*) from ($sql) foo");
- $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
- queryResults($sql, $webvar{page}, $count);
-
- } elsif ($category =~ /ipblock/) {
-
- # Query is for a partial IP, a CIDR block in some form, or a flat IP.
- print qq(Searching for IP-based matches on '$query'
\n);
-
- $query =~ s/\s+//g;
- if ($query =~ /\//) {
- # 209.91.179/26 should show all /26 subnets in 209.91.179
- my ($net,$maskbits) = split /\//, $query;
- if ($query =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
- # /0->/9 are silly to worry about right now. I don't think
- # we'll be getting a class A anytime soon.
- $sql = "select cidr,custid,type,city,description from searchme where cidr='$query'";
- queryResults($sql, $webvar{page}, 1);
- } else {
- print "Finding all blocks with netmask /$maskbits, leading octet(s) $net
\n";
- # Partial match; beginning of subnet and maskbits are provided
- $sql = "select cidr,custid,type,city,description from searchme where ".
- "text(cidr) like '$net%' and text(cidr) like '%$maskbits'";
- my $count = countRows("select count(*) from ($sql) foo");
- $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
- queryResults($sql, $webvar{page}, $count);
- }
- } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
- # Specific IP address match
- my $sfor = new NetAddr::IP $query;
-# We do this convoluted roundabout way of finding things in order
-# to bring up matches for single IPs that are within a static block;
-# we want to show both the "container" block and the static IP itself.
- $sth = $ip_dbh->prepare("select cidr from searchme where cidr >>= '$sfor'");
- $sth->execute;
- while (my @data = $sth->fetchrow_array()) {
- my $cidr = new NetAddr::IP $data[0];
- queryResults("select cidr,custid,type,city,description from searchme where ".
- "cidr='$cidr'", $webvar{page}, 1);
- }
- } elsif ($query =~ /^\d{1,3}\.\d{1,3}\.\d{1,3}\.?$/) {
- print "Finding matches where the first three octets are $query
\n";
- $sql = "select cidr,custid,type,city,description from searchme where ".
- "text(cidr) like '$query%'";
- my $count = countRows("select count(*) from ($sql) foo");
- $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
- queryResults($sql, $webvar{page}, $count);
- } else {
- # This shouldn't happen, but if it does, whoever gets it deserves what they get...
- printError("Invalid query.");
- }
- } else {
- # This shouldn't happen, but if it does, whoever gets it deserves what they get...
- printError("Invalid searchfor.");
- }
-} # viewBy
-
-
# args are: a reference to an array with the row to be printed and the
# class(stylesheet) to use for formatting.
@@ -328,59 +208,4 @@
print "";
} # printRow
-
-
-# Display certain types of search query. Note that this can't be
-# cleanly reused much of anywhere else as the data isn't neatly tabulated.
-# This is tied to the search sub tightly enough I may just gut it and provide
-# more appropriate tables directly as needed.
-sub queryResults($$$) {
- my ($sql, $pageNo, $rowCount) = @_;
- my $offset = 0;
- $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/);
-
- my $sth = $ip_dbh->prepare($sql);
- $sth->execute();
-
- startTable('Allocation','CustID','Type','City','Description/Name');
- my $count = 0;
-
- while (my @data = $sth->fetchrow_array) {
- # cidr,custid,type,city,description
- # Prefix subblocks with "Sub "
- my @row = ( (($data[2] =~ /^.r$/) ? 'Sub ' : '').
- qq($data[0]),
- $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]);
- # Allow listing of pool if desired/required.
- if ($data[2] =~ /^.[pd]$/) {
- $row[0] .= ' List IPs";
- }
- printRow(\@row, 'color1', 1) if ($count%2==0);
- printRow(\@row, 'color2', 1) if ($count%2!=0);
- $count++;
- }
-
- # Have to think on this call, it's primarily to clean up unfetched rows from a select.
- # In this context it's probably a good idea.
- $sth->finish();
-
- my $upper = $offset+$count;
- print "Records found: $rowCount
Displaying: $offset - $upper | \n";
- print "\n";
-
- # print the page thing..
- if ($rowCount > $RESULTS_PER_PAGE) {
- my $pages = ceil($rowCount/$RESULTS_PER_PAGE);
- print qq( Page: );
- for (my $i = 1; $i <= $pages; $i++) {
- if ($i == $pageNo) {
- print " $i \n";
- } else {
- print qq( $i \n);
- }
- }
- print " Searching............... \n";
+ queryResults($sql, $webvar{page}, $count);
+ }
+
+} else { # how script was called. General case is to show the search criteria page.
+
+ # Display search page. We have to do this here, because otherwise
+ # we can't retrieve data from the database for the types and cities. >:(
+ my $html;
+ open HTML,"<../compsearch.html";
+ $html = join('',);
+ close HTML;
+
+# Generate table of types
+ my $typetable = "\n";
+
+# Generate table of cities
+ my $citytable = "\n";
+
+ $html =~ s/\$\$TYPELIST\$\$/$typetable/;
+ $html =~ s/\$\$CITYLIST\$\$/$citytable/;
+
+ print $html;
+}
+
+# Shut down and clean up.
+finish($ip_dbh);
+printFooter;
+# We shouldn't need to directly execute any code below here; it's all subroutines.
+exit 0;
+
+
+# viewBy()
+# The quick search
+# Takes a category descriptor and a query string
+# Creates appropriate SQL to run the search and display the results
+# with queryResults()
+sub viewBy($$) {
+ my ($category,$query) = @_;
+
+ # Local variables
+ my $sql;
+
+ # Calculate start point for LIMIT clause
+ my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
+
+# Possible cases:
+# 1) Partial IP/subnet. Treated as "octet-prefix".
+# 2a) CIDR subnet. Exact match.
+# 2b) CIDR netmask. YMMV but it should be octet-prefix-with-netmask
+# (ie, all matches with the octet prefix *AND* that netmask)
+# 3) Customer ID. "Match-any-segment"
+# 4) Description. "Match-any-segment"
+# 5) Invalid data which might be interpretable as an IP or something, but
+# which probably shouldn't be for reasons of sanity.
+
+ if ($category eq 'all') {
+
+ print qq(Showing all netblock and static-IP allocations
\n);
+ $sql = "select * from searchme";
+ my $count = countRows($sql);
+ $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+ queryResults($sql, $webvar{page}, $count);
+
+ } elsif ($category eq 'cust') {
+
+ print qq(Searching for Customer IDs containing '$query'
\n);
+
+ # 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 * from searchme where custid ilike '%$query%'";
+ my $count = countRows($sql);
+ $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+ queryResults($sql, $webvar{page}, $count);
+
+ } elsif ($category eq 'desc') {
+
+ print qq(Searching for descriptions containing '$query'
\n);
+ # Query based on description (includes "name" from old DB).
+ $sql = "select * from searchme where description ilike '%$query%'";
+ my $count = countRows($sql);
+ $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+ queryResults($sql, $webvar{page}, $count);
+
+ } elsif ($category =~ /ipblock/) {
+
+ # Query is for a partial IP, a CIDR block in some form, or a flat IP.
+ print qq(Searching for IP-based matches on '$query'
\n);
+
+ $query =~ s/\s+//g;
+ if ($query =~ /\//) {
+ # 209.91.179/26 should show all /26 subnets in 209.91.179
+ my ($net,$maskbits) = split /\//, $query;
+ if ($query =~ /^(\d{1,3}\.){3}\d{1,3}\/\d{2}$/) {
+ # /0->/9 are silly to worry about right now. I don't think
+ # we'll be getting a class A anytime soon.
+ $sql = "select * from searchme where cidr='$query'";
+ queryResults($sql, $webvar{page}, 1);
+ } else {
+ print "Finding all blocks with netmask /$maskbits, leading octet(s) $net
\n";
+ # Partial match; beginning of subnet and maskbits are provided
+ $sql = "select * from searchme where text(cidr) like '$net%' and ".
+ "text(cidr) like '%$maskbits'";
+ my $count = countRows($sql);
+ $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+ queryResults($sql, $webvar{page}, $count);
+ }
+ } elsif ($query =~ /^(\d{1,3}\.){3}\d{1,3}$/) {
+ # Specific IP address match
+ print "4-octet pattern found; finding netblock containing IP $query
\n";
+ 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 * 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 * from searchme where cidr='$cidr'", $webvar{page}, 1);
+ }
+ }
+ } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
+ print "Finding matches with leading octet(s) $query
\n";
+ $sql = "select * from searchme where text(cidr) like '$query%'";
+ my $count = countRows($sql);
+ $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
+ queryResults($sql, $webvar{page}, $count);
+ } else {
+ # This shouldn't happen, but if it does, whoever gets it deserves what they get...
+ printError("Invalid query.");
+ }
+ } else {
+ # This shouldn't happen, but if it does, whoever gets it deserves what they get...
+ printError("Invalid searchfor.");
+ }
+} # viewBy
+
+
+# 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 "\n";
+ } else {
+ print " \n";
+ }
+
+ELEMENT: foreach my $element (@$rowRef) {
+ if (!defined($element)) {
+ print " | \n";
+ next ELEMENT;
+ }
+ $element =~ s|\n||g;
+ print "$element | \n";
+ }
+ print " ";
+} # printRow
+
+
+# queryResults()
+# Display search queries based on the passed SQL.
+# Takes SQL, page number (for multipage search results), and a total count.
+sub queryResults($$$) {
+ my ($sql, $pageNo, $rowCount) = @_;
+ my $offset = 0;
+ $offset = $1 if($sql =~ m/.*limit\s+(.*),.*/);
+
+ my $sth = $ip_dbh->prepare($sql);
+ $sth->execute();
+
+ startTable('Allocation','CustID','Type','City','Description/Name');
+ my $count = 0;
+
+ while (my @data = $sth->fetchrow_array) {
+
+ # cidr,custid,type,city,description,notes
+ # Another bit of HairyPerl(TM) to prefix subblocks with "Sub"
+ my @row = (($data[2] =~ /^.r$/ ? 'Sub ' : '').
+ qq($data[0]),
+ $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]);
+ # Allow listing of pool if desired/required.
+ if ($data[2] =~ /^.[pd]$/) {
+ $row[0] .= ' List IPs";
+ }
+ printRow(\@row, 'color1', 1) if ($count%2==0);
+ printRow(\@row, 'color2', 1) if ($count%2!=0);
+ $count++;
+ }
+
+ # Have to think on this call, it's primarily to clean up unfetched rows from a select.
+ # In this context it's probably a good idea.
+ $sth->finish();
+
+ my $upper = $offset+$count;
+ print "Records found: $rowCount
Displaying: ".($offset+1)." - $upper | \n";
+ print "\n";
+
+ # print the page thing..
+ if ($rowCount > $RESULTS_PER_PAGE) {
+ my $pages = ceil($rowCount/$RESULTS_PER_PAGE);
+ print qq( Page: );
+ for (my $i = 1; $i <= $pages; $i++) {
+ if ($i == $pageNo) {
+ print " $i \n";
+ } else {
+ print qq( $i \n);
+ }
+ }
+ print " );
+
+ foreach(@_) {
+ print qq(| $_ | );
+ }
+ print " \n";
+} # startTable
+
+
+# Return count of rows to be returned in a "real" query
+# with the passed SQL statement
+sub countRows($) {
+ # Note that the "as foo" is required
+ my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo");
+ $sth->execute();
+ my @a = $sth->fetchrow_array();
+ $sth->finish();
+ return $a[0];
+}
Index: trunk/compsearch.html
===================================================================
--- trunk/compsearch.html (revision 234)
+++ trunk/compsearch.html (revision 234)
@@ -0,0 +1,35 @@
+
Index: trunk/header.inc
===================================================================
--- trunk/header.inc (revision 233)
+++ trunk/header.inc (revision 234)
@@ -40,17 +40,14 @@
|
-
Index: trunk/help.html
===================================================================
--- trunk/help.html (revision 233)
+++ trunk/help.html (revision 234)
@@ -11,5 +11,5 @@
-| Searches: |
+ | Quick Searches: |
Index: trunk/ipdb.css
===================================================================
--- trunk/ipdb.css (revision 233)
+++ trunk/ipdb.css (revision 234)
@@ -50,5 +50,5 @@
.regular {
font-family: Verdana, Arial, Helvetica, sans-serif;
- font-size: 100%;
+ font-size: 90%;
}
|