Ignore:
Timestamp:
03/18/05 18:16:13 (20 years ago)
Author:
Kris Deugau
Message:

/branches/new-search-20050223

SQL for all except IP/netblock-based search filtering is complete.
Exclusion options are complete, although they will not behave
entirely predictably if "match any" is used.

Location:
branches/new-search-20050223
Files:
2 edited

Legend:

Unmodified
Added
Removed

  • branches/new-search-20050223/cgi-bin/search.cgi

    r201 r202  
    3737
    3838# Global variables
    39 my $RESULTS_PER_PAGE = 50;
     39my $RESULTS_PER_PAGE = 10;
    4040my %webvar = parse_post();
    4141cleanInput(\%webvar);
    … …  
    5757    # Search term entered.  Display matches.
    5858    # We should really sanitize $webvar{input}, no?
    59     # need to munge up data for $webvar{searchfor}, rather than breaking things here.
    6059    my $searchfor;
    6160    # Chew up leading and trailing whitespace
    6261    $webvar{input} =~ s/^\s+//;
    6362    $webvar{input} =~ s/\s+$//;
    64     if ($webvar{input} =~ /^[\d\.]+(\/\d{2})?$/) {
     63    if ($webvar{input} =~ /^[\d\.]+(\/\d{1,3})?$/) {
    6564      # IP addresses should only have numbers, digits, and maybe a slash+netmask
    6665      $searchfor = "ipblock";
    … …  
    8483  # Individual cases are for the CIDR/IP, CustID, Description, Notes, and individual type
    8584  # requests.
    86 
    87 my $sql = "SELECT cidr,custid,type,city,description FROM searchme";
    8885
    8986my $sqlconcat;
    … …  
    10198}
    10299
    103 # Cases:
    104 # -> No search terms.  Display everything.
    105 # -> One or more terms (cidr/custid/desc/notes)
    106 
    107 # -> Type/city restrictions
    108 
     100# We actually construct a monster SQL statement for all criteria.
     101# Iff something has been entered, it will be used as a filter.
     102
     103# Columns actually returned.  Slightly better than hardcoding it
     104# in each (sub)select
    109105my $cols = "cidr,custid,type,city,description";
    110106
    111 $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
     107# First chunk of SQL.  Filter on custid, description, and notes as necessary.
     108my $sql = "(select $cols from searchme where $webvar{custexclude} custid ilike '%$webvar{custid}%')".
    112109        " $sqlconcat (select $cols from searchme where $webvar{descexclude} description ilike '%$webvar{desc}%')".
    113110        " $sqlconcat (select $cols from searchme where $webvar{notesexclude} notes ilike '%$webvar{notes}%')";
    … …  
    153150#"";
    154151
    155 print $sql."\n";
    156 
    157 $sth = $ip_dbh->prepare($sql);
    158 $sth->execute;
    159 
    160 print "<table border=1>\n";
    161 while (my @data = $sth->fetchrow_array) {
    162   print "<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td></tr>\n";
    163 }
    164 
    165 #print "<pre>\n";
    166 #foreach my $key (keys %webvar) {
    167 #  print "key: $key     value: -'$webvar{$key}'-\n";
    168 #}
    169 #print "</pre>\n";
     152my $offset = ($webvar{page}-1)*$RESULTS_PER_PAGE;
     153
     154print $sql;
     155
     156my $count = countRows($sql);
     157$sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
     158queryResults($sql, $webvar{page}, $count);
     159
     160
     161print "<pre>\n";
     162foreach my $key (keys %webvar) {
     163  print "key: $key      value: -'$webvar{$key}'-\n";
     164}
     165print "</pre>\n";
    170166
    171167} else {
    … …  
    258254    print qq(<div class="heading">Showing all netblock and static-IP allocations</div><br>\n);
    259255    $sql = "select * from searchme";
    260     my $count = countRows("select count(*) from ($sql) foo");
     256    my $count = countRows($sql);
    261257    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
    262258    queryResults($sql, $webvar{page}, $count);
    … …  
    269265    # as we have non-numeric custIDs in the legacy data.  :/
    270266    $sql = "select * from searchme where custid ilike '%$query%'";
    271     my $count = countRows("select count(*) from ($sql) foo");
     267    my $count = countRows($sql);
    272268    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
    273269    queryResults($sql, $webvar{page}, $count);
    … …  
    278274    # Query based on description (includes "name" from old DB).
    279275    $sql = "select * from searchme where description ilike '%$query%'";
    280     my $count = countRows("select count(*) from ($sql) foo");
     276    my $count = countRows($sql);
    281277    $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
    282278    queryResults($sql, $webvar{page}, $count);
    … …  
    301297        $sql = "select * from searchme where text(cidr) like '$net%' and ".
    302298                "text(cidr) like '%$maskbits'";
    303         my $count = countRows("select count(*) from ($sql) foo");
     299        my $count = countRows($sql);
    304300        $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
    305301        queryResults($sql, $webvar{page}, $count);
    … …  
    318314        }
    319315      }
    320     } elsif ($query =~ /^\d{1,3}\.\d{1,3}\.\d{1,3}\.?$/) {
    321       print "Finding matches where the first three octets are $query<br>\n";
     316    } elsif ($query =~ /^(\d{1,3}\.){1,3}\d{1,3}\.?$/) {
     317      print "Finding matches with leading octet(s) $query<br>\n";
    322318      $sql = "select * from searchme where text(cidr) like '$query%'";
    323       my $count = countRows("select count(*) from ($sql) foo");
     319      my $count = countRows($sql);
    324320      $sql .= " order by cidr limit $RESULTS_PER_PAGE offset $offset";
    325321      queryResults($sql, $webvar{page}, $count);
    … …  
    375371
    376372  while (my @data = $sth->fetchrow_array) {
     373
    377374    # cidr,custid,type,city,description,notes
    378     # Fix up types from pools (which are single-char)
    379     # Fixing the database would be...  painful.  :(
    380 ##fixme LEGACY CODE
    381     if ($data[2] =~ /^[cdsmw]$/) {
    382       $data[2] .= 'i';
    383     }
    384     my @row = (qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[0]">$data[0]</a>),
     375    # Another bit of HairyPerl(TM) to prefix subblocks with "Sub"
     376    my @row = (($data[2] =~ /^.r$/ ? 'Sub ' : '').
     377        qq(<a href="/ip/cgi-bin/main.cgi?action=edit&block=$data[0]">$data[0]</a>),
    385378        $data[1], $disp_alloctypes{$data[2]}, $data[3], $data[4]);
    386379    # Allow listing of pool if desired/required.
    … …  
    399392
    400393  my $upper = $offset+$count;
    401   print "<tr><td colspan=10 bgcolor=white class=regular>Records found: $rowCount<br><i>Displaying: $offset - $upper</i></td></tr>\n";
     394  print "<tr><td colspan=10 bgcolor=white class=regular>Records found: $rowCount<br><i>Displaying: ".($offset+1)." - $upper</i></td></tr>\n";
    402395  print "</table></center>\n";
    403396
    … …  
    410403        print "<b>$i&nbsp;</b>\n";
    411404      } else {
    412         print qq(<a href="/ip/cgi-bin/main.cgi?page=$i&input=$webvar{input}&action=search&searchfor=$webvar{searchfor}">$i</a>&nbsp;\n);
     405        print qq(<a href="/ip/cgi-bin/search.cgi?page=$i&stype=$webvar{stype}&);
     406        if ($webvar{stype} eq 'c') {
     407          print "cidr=$webvar{cidr}&custid=$webvar{custid}&desc=$webvar{desc}&".
     408                "notes=$webvar{notes}&which=$webvar{which}&alltypes=$webvar{alltypes}&".
     409                "allcities=$webvar{allcities}&";
     410          foreach my $key (keys %webvar) {
     411            if ($key =~ /^(?:type|city)\[/) {
     412              print "$key=$webvar{$key}&";
     413            }
     414          }
     415        } else {
     416          print "input=$webvar{input}&";
     417        }
     418        print qq(">$i</a>&nbsp;\n);
    413419      }
    414420    }
    … …  
    430436
    431437
    432 # Return first element of passed SQL query
     438# Return count of rows to be returned in a "real" query
     439# with the passed SQL statement
    433440sub countRows($) {
    434   my $sth = $ip_dbh->prepare($_[0]);
     441  # Note that the "as foo" is required
     442  my $sth = $ip_dbh->prepare("select count(*) from ($_[0]) as foo");
    435443  $sth->execute();
    436444  my @a = $sth->fetchrow_array();

  • branches/new-search-20050223/compsearch.html

    r197 r202  
    66<td colspan=2>Match on:<input type=radio checked name=which value="all">All
    77<input type=radio name=which value="any">Any
    8 <div align=right><input type=submit value="Search Now"></div>
     8<td colspan=2 align=right><input type=submit value="Search Now"></td>
    99</td>
    1010<tr class="color1">
    11 <td>IP/netblock:</td><td><input name=cidr></td>
     11<td>Exclude?<input type=checkbox name=cidrexclude value='not'></td>
     12<td>IP/netblock:</td><td colspan=2><input name=cidr></td>
    1213</tr><tr class="color2">
    13 <td>CustID:</td><td><input name=custid></td>
     14<td>Exclude?<input type=checkbox name=custexclude value='not'></td>
     15<td>CustID:</td><td colspan=2><input name=custid></td>
    1416</tr><tr class="color1">
    15 <td>Description:</td><td><input name=desc></td>
     17<td>Exclude?<input type=checkbox name=descexclude value='not'></td>
     18<td>Description:</td><td colspan=2><input name=desc></td>
    1619</tr><tr class="color2">
    17 <td>Notes:</td><td><input name=notes></td>
     20<td>Exclude?<input type=checkbox name=notesexclude value='not'></td>
     21<td>Notes:</td><td colspan=2><input name=notes></td>
    1822</tr><tr class="color1">
    19 <td>Types:</td><td><input type=checkbox name=alltypes checked>Show all types
     23<td>Exclude?<input type=checkbox name=typeexclude value='not'></td>
     24<td>Types:</td><td colspan=2><input type=checkbox name=alltypes checked>Show all types
    2025$$TYPELIST$$</td>
    2126</tr><tr class="color2">
    22 <td>Cities:</td><td><input type=checkbox name=allcities checked>Show all cities
     27<td>Exclude?<input type=checkbox name=cityexclude value='not'></td>
     28<td>Cities:</td><td colspan=2><input type=checkbox name=allcities checked>Show all cities
    2329$$CITYLIST$$</td>
    2430</tr>
    2531<input type=hidden name=stype value=c>
     32<input type=hidden name=page value=1>
    2633</table>
    2734</form>
Note: See TracChangeset for help on using the changeset viewer.