From 99e65b28d840d4705a0a0de3ae2499d97878cb14 Mon Sep 17 00:00:00 2001 From: Simo Leone Date: Sat, 22 Mar 2008 13:21:58 -0500 Subject: Revamped pkg_search_page() - Reduced database hits - Improved speed - Improved sanity (slightly) - Fixed searches,buttons,gizmos Signed-off-by: Simo Leone --- web/html/packages.php | 4 +- web/lib/pkgfuncs.inc | 1034 ++++++++++++++++++++++------------------------- web/template/header.php | 2 +- 3 files changed, 494 insertions(+), 546 deletions(-) (limited to 'web') diff --git a/web/html/packages.php b/web/html/packages.php index 33b8ff42..cf8ad8c0 100644 --- a/web/html/packages.php +++ b/web/html/packages.php @@ -18,8 +18,6 @@ if (isset($_GET['ID'])) { } } else if (!empty($_GET['K'])) { $title = "Search: " . $_GET['K']; -} else if (!empty($_GET['do_MyPackages'])) { - $title = __("My Packages"); } else { $title = __("Packages"); } @@ -520,7 +518,7 @@ if ($_POST['action'] == "do_Flag" || isset($_POST['do_Flag'])) { } } } else { - # do_More/do_Less/do_Search/do_MyPackages - just do a search + # just do a search # pkg_search_page($_COOKIE["AURSID"]); diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 0b31618f..48d5239c 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -4,7 +4,7 @@ include_once("config.inc"); # define variables used during pkgsearch # -$pkgsearch_vars = array("O", "L", "C", "K", "SB", "SO", "PP", "do_MyPackages", "do_Orphans", "SeB"); +$pkgsearch_vars = array("O", "L", "C", "K", "SB", "SO", "PP", "do_Orphans", "SeB"); # Make sure this visitor can delete the requested package comment # They can delete if they were the comment submitter, or if they are a TU/Dev @@ -374,7 +374,7 @@ function package_details($id=0, $SID="") { while (list($k, $darr) = each($deps)) { $url = " 100) + $PP = 100; + } else { + $PP = 25; + } - # determine paging variables - # - if (intval($_GET['O'])) { - $O = $_GET['O']; - } else { - $O = 0; - } - $_REQUEST["PP"] ? $PP = intval($_REQUEST["PP"]) : $PP = 25; - if ($PP < 25) {$PP = 25;} - if ($PP > 100) {$PP = 100;} - - if ($_REQUEST["do_Search"] && $_REQUEST["do_Search"] != 1) { - # reset the offset to zero if they hit Go - # - $_REQUEST["do_MyPackages"] = 0; - $_REQUEST["do_Orphans"] = 0; - $O = 0; - } - if ($_REQUEST["do_MyPackages"] && $_REQUEST["do_MyPackages"] != 1) { - # reset the offset to zero if they hit My Packages - # - $_REQUEST["do_Search"] = 0; - $_REQUEST["do_Orphans"] = 0; - $O = 0; - } - if (!empty($_REQUEST['do_Orphans']) && $_REQUEST['do_Orphans'] != 1) { - # reset the offset to zero if they hit Orphans - # - $_REQUEST["do_Search"] = 0; - $_REQUEST["do_MyPackages"] = 0; - $O = 0; - } - $_REQUEST["O"] = $O; # so that pkg_search_results() works + // The search form - XXX: split into own function? + // + // FIXME: highly fugly. whoever makes this use + // less print statements gets a cookie + // FIXME: ugly html. whoever un-tables this gets + // another cookie + print "
\n"; + print "\n"; + + print "
\n"; + print "\n"; + print "\n"; + print " \n"; + print "\n"; + print "\n"; + print " \n"; + print "\n"; + print "
\n"; + print " ".__("Search Criteria")."\n"; + print "
\n"; + print "\n"; + + print "\n"; + print "\n"; + + print "\n"; + + print "\n"; + + print "\n"; + + print "\n"; + + // Added to break put the buttons in a new line + print"
\n"; + print " ".__("Location"); + print "
\n"; + print " \n"; + print "		\n"; + print " ".__("Category"); + print "
\n"; + print " \n"; + print "		\n"; + print " ".__("Keywords"); + print "
\n"; + print " \n"; + print "		\n"; + print " ".__("Search by"); + print "
\n"; + + print " \n"; + print "		\n"; + print " ".__("Per page"); + print "
\n"; + print " \n"; + print "
"; + + print "\n"; + + print "\n"; + + print "\n"; + print "
 \n"; + print " \n"; + print " \n"; + print " \n"; + print "
\n"; + + print "
\n"; + print "
\n"; + print "
\n"; + print "
\n"; + + + // FIXME: pull out DB-related code. all of it. + // this one's worth a choco-chip cookie, + // one of those nice big soft ones + + // build the package search query + // + $q = "SELECT SQL_CALC_FOUND_ROWS "; + if ($SID) { + $q .= "CommentNotify.UserID AS Notify, + PackageVotes.UsersID AS Voted, "; + } + $q .= "Users.Username AS Maintainer, + PackageCategories.Category, + PackageLocations.Location, + Packages.Name, Packages.Version, Packages.Description, Packages.NumVotes, + Packages.ID, Packages.OutOfDate + + FROM PackageCategories, PackageLocations, Packages + LEFT JOIN Users ON (Packages.MaintainerUID = Users.ID) "; + if ($SID) { + $q .= "LEFT JOIN PackageVotes + ON (Packages.ID = PackageVotes.PackageID AND PackageVotes.UsersID = ".$myuid.") + LEFT JOIN CommentNotify + ON (Packages.ID = CommentNotify.PkgID AND CommentNotify.UserID = ".$myuid.") "; + } + $q .= "WHERE + Packages.CategoryID = PackageCategories.ID + AND Packages.LocationID = PackageLocations.ID + AND Packages.DummyPkg = 0 "; + + // TODO: possibly do string matching on category and + // location to make request variable values more sensible + if (intval($_REQUEST["L"])) { + $q .= "AND Packages.LocationID = ".intval($_REQUEST["L"])." "; + } + if (intval($_REQUEST["C"])) { + $q.= "AND Packages.CategoryID = ".intval($_REQUEST["C"])." "; + } + if ($_REQUEST['K']) { + $K = mysql_real_escape_string(trim($_REQUEST['K'])); + //search by maintainer + if ($_REQUEST["SeB"] == "m"){ + $q.= "AND Users.Username = '".$K."' "; + } elseif ($_REQUEST["SeB"] == "s") { + // FIXME: this shouldn't be making 2 queries + // kill the call to uid_from_username + $q.= "AND SubmitterUID = ".uid_from_username($_REQUEST['K'])." "; + // the default behavior, query the name/description + } else { + $q.= "AND (Name LIKE '%".$K."%' OR "; + $q.= "Description LIKE '%".$K."%') "; + } + } - # grab info for user if they're logged in - # - if ($SID) { - $myuid = uid_from_sid($SID); - $acct = account_from_sid($SID); - $my_votes = pkgvotes_from_sid($SID); - $my_notify = pkgnotify_from_sid($SID); - } + if ($_REQUEST["do_Orphans"]) { + $q.= "AND MaintainerUID = 0 "; + } - # The search form - # - print "
\n"; - #print "\n"; - print "\n"; - - print "
\n"; - print "\n"; - print "\n"; - print " \n"; - print "\n"; - print "\n"; - print " \n"; - print "\n"; - print "
\n"; - print " ".__("Search Criteria")."\n"; - print "
\n"; - print "\n"; - - print "\n"; - print "\n"; - - print "\n"; - - print "\n"; - - print "\n"; - - print "\n"; - - # Added to break put the buttons in a new line - print"
\n"; - print " ".__("Location"); - print "
\n"; - print " \n"; - print "		\n"; - print " ".__("Category"); - print "
\n"; - print " \n"; - print "		\n"; - print " ".__("Keywords"); - print "
\n"; - print " \n"; - print "		\n"; - print " ".__("Search by"); - print "
\n"; - - print " \n"; - print "		\n"; - print " ".__("Per page"); - print "
\n"; - print " \n"; - print "
"; - - print "\n"; - - print "\n"; - - print "\n"; - print "
 \n"; - print " \n"; - print " \n"; - print " \n"; - print "
\n"; - - print "
\n"; - print "
\n"; - print "
\n"; - - # query to pull out package info - # -# $q = "SELECT Packages.*, IF(ISNULL(PackageID), 0, COUNT(*)) AS Votes "; -# $q.= "FROM Packages LEFT JOIN PackageVotes "; -# $q.= "ON Packages.ID = PackageVotes.PackageID "; - $q = "SELECT * FROM Users RIGHT JOIN Packages "; - $q.= "ON (Users.ID = Packages.MaintainerUID) "; - $q.= "WHERE DummyPkg != 1 "; - $has_where = 1; - - if (intval($_REQUEST["L"])) { - if (!$has_where) { - $q.= "WHERE LocationID = ".intval($_REQUEST["L"])." "; - } else { - $q .= "AND LocationID = ".intval($_REQUEST["L"])." "; - } - $has_where = 1; - } - if (intval($_REQUEST["C"])) { - if (!$has_where) { - $q.= "WHERE CategoryID = ".intval($_REQUEST["C"])." "; - $has_where = 1; - } else { - $q.= "AND CategoryID = ".intval($_REQUEST["C"])." "; - } - } - if ($K) { - #search by maintainer - if ($_REQUEST["SeB"] == "m"){ - if (!$has_where) { - $q.= "WHERE Username = '".mysql_real_escape_string($K)."' "; - $has_where = 1; - } else { - $q.= "AND Username = '".mysql_real_escape_string($K)."' "; - } - } elseif ($_REQUEST["SeB"] == "s") { - if (!$has_where) { - $q.= "WHERE SubmitterUID = ".uid_from_username($K)." "; - $has_where = 1; - } else { - $q.= "AND SubmitterUID = ".uid_from_username($K)." "; - } - # the default behaivior, query the name/description - } else { - if (!$has_where) { - $q.= "WHERE (Name LIKE '%".mysql_real_escape_string($K)."%' OR "; - $q.= "Description LIKE '%".mysql_real_escape_string($K)."%') "; - $has_where = 1; - } else { - $q.= "AND (Name LIKE '%".mysql_real_escape_string($K)."%' OR "; - $q.= "Description LIKE '%".mysql_real_escape_string($K)."%') "; - } - } - } - - if ($_REQUEST["do_MyPackages"] && $SID) { - # list packages that the user is a AUR Maintainer of, or if it the - # vistior is a registered user, if they are the Maintainer. - # - if ($myuid) { - if (!$has_where) { - $q.= "WHERE (AURMaintainerUID = ".$myuid." OR "; - $has_where = 1; - } else { - $q.= "AND (AURMaintainerUID = ".$myuid." OR "; - } - //$q.= "MaintainerUID = ".$myuid." OR SubmitterUID = ".$myuid.") "; - $q.= "MaintainerUID = ".$myuid.") "; - } - } - if ($_REQUEST["do_Orphans"]) { - # List packages that have neither a Maintainer nor AURMaintainer - # - if (!$has_where) { - $q.= "WHERE (AURMaintainerUID = 0 AND "; - $q.= "MaintainerUID = 0) "; - $has_where = 1; - } else { - $q.= "AND (AURMaintainerUID = 0 AND "; - $q.= "MaintainerUID = 0) "; - } - } - $order = $_REQUEST["SO"] == 'd' ? 'DESC' : 'ASC'; - - switch ($_REQUEST["SB"]) { - case 'c': - $q.= "ORDER BY CategoryID ".$order.", Name ASC, LocationID ASC "; - $SB = 'c'; - break; - case 'l': - $q.= "ORDER BY LocationID ".$order.", Name ASC, CategoryID DESC "; - $SB = 'l'; - break; - case 'v': - $q.= "ORDER BY NumVotes ".$order.", Name ASC, CategoryID DESC "; - $SB = 'v'; - break; - case 'm': - $q.= "ORDER BY Username ".$order.", Name ASC, LocationID ASC "; - $SB = 'm'; - break; - case 'a': - $q.= "ORDER BY GREATEST(SubmittedTS,ModifiedTS) ".$order.", Name ASC, LocationID ASC "; - $SB = 'a'; - break; - default: - $q.= "ORDER BY Name ".$order.", LocationID ASC, CategoryID DESC "; - break; - } - $allresults = mysql_num_rows(db_query($q, $dbh)); + switch ($_REQUEST["SB"]) { + case 'c': + $q.= "ORDER BY CategoryID ".$order.", Name ASC, LocationID ASC "; + $SB = 'c'; + break; + case 'l': + $q.= "ORDER BY LocationID ".$order.", Name ASC, CategoryID DESC "; + $SB = 'l'; + break; + case 'v': + $q.= "ORDER BY NumVotes ".$order.", Name ASC, CategoryID DESC "; + $SB = 'v'; + break; + case 'm': + $q.= "ORDER BY Maintainer ".$order.", Name ASC, LocationID ASC "; + $SB = 'm'; + break; + case 'a': + $q.= "ORDER BY GREATEST(SubmittedTS,ModifiedTS) ".$order.", Name ASC, LocationID ASC "; + $SB = 'a'; + break; + default: + $q.= "ORDER BY Name ".$order.", LocationID ASC, CategoryID DESC "; + break; + } - $qnext = $q."LIMIT ".($O+$PP).", ".$PP; //next page's worth - $q.= "LIMIT ".$O.", ".$PP; + $q.= "LIMIT ".$O.", ".$PP; + + $result = db_query($q, $dbh); + $total = mysql_result(db_query('SELECT FOUND_ROWS() AS Total', $dbh), 0); + + print "\n"; + print "
\n"; + print "\n"; + print "\n"; + print " \n"; + print "\n"; + print "\n"; + print "
\n"; + print " ".__("Package Listing")."\n"; + print "
\n"; + print "\n"; + + if (!$result) { + print "
"; + print __("Error retrieving package list."); + print "
"; + } elseif ($total == 0) { + print "
"; + print __("No packages matched your search criteria."); + print "
"; + } else { + // print out package search results + // + + // SO_next used to change sort order on header click + if ($_REQUEST["SO"] == "d"){ + $SO_next="a"; + $SO = 'd'; + } else { + $SO_next="d"; + $SO = 'a'; + } + print "\n"; + if ($SID) { + print " \n"; + } + print " \n"; + print " \n"; + print " \n"; + print " \n"; + if ($SID) { + print " \n"; + } + if ($SID) { + print " \n"; + } + print " \n"; + print " \n"; + print "\n"; - $result = db_query($q, $dbh); - - print "
\n"; - print "
 "; + print "".__("Location").""; + print ""; + print "".__("Category").""; + print ""; + print "".__("Name").""; + print ""; + print "".__("Votes").""; + print "".__("Voted")."".__("Notify")."".__("Description").""; + print ""; + print "".__("Maintainer").""; + print "
\n"; - print "\n"; - print " \n"; - print "\n"; - print "\n"; - print " \n"; - print "\n"; - print "
\n"; - print " ".__("Package Listing")."\n"; - print "
\n"; - print "\n"; - - if (!$result) { - print "
"; - print __("Error retrieving package list."); - print "
"; - } elseif (!mysql_num_rows($result)) { - print "
"; - print __("No packages matched your search criteria."); - print "
"; - } else { - # print out package search results - # - - # SO_next used to change sort order on header click - if ($_REQUEST["SO"] == "d"){ - $SO_next="a"; - $SO = 'd'; - } else { - $SO_next="d"; - $SO = 'a'; - } - print "\n"; - if ($SID) { - print " \n"; - } - print " \n"; - print " \n"; - print " \n"; - print " \n"; - if ($SID) { - print " \n"; - } - if ($SID) { - print " \n"; - } - print " \n"; - print " \n"; -# REMOVED LINK TO 'pkgmgmnt.php' -# if ($SID) { -# print " \n"; -# } - print "\n"; - for ($i=0; $row = mysql_fetch_assoc($result); $i++) { - (($i % 2) == 0) ? $c = "data1" : $c = "data2"; - print "\n"; - if ($SID) { - if ($row["OutOfDate"]) { - $c = "outofdate"; - } - print " \n"; - } - print " \n"; - print " \n"; - print " \n"; - print " \n"; - if ($SID) { - print " \n"; - } else { - print " \n"; - } - } - if ($SID) { - print " \n"; - } else { - print " \n"; - } - } - print " \n"; - print " \n"; - -# REMOVED LINK TO 'pkgmgmnt.php' -# # print the managed link if applicable -# # -# if (canManagePackage($myuid, $row["AURMaintainerUID"], -# $row["MaintainerUID"], $row["SubmitterUID"], $managed)) { -# $manage_url = "Manage"; -# print " \n"; -# } else { -# print "\n"; -# } - - print "\n"; - - } - print "
 "; - print "".__("Location").""; - print ""; - print "".__("Category").""; - print ""; - print "".__("Name").""; - print ""; - print "".__("Votes").""; - print "".__("Voted")."".__("Notify")."".__("Description").""; - print ""; - print "".__("Maintainer").""; - print "".__("Manage")."
"; - print ""; -# if ($i == 0) { -# $all_ids = $row["ID"]; -# } else { -# $all_ids .= ":".$row["ID"]; -# } - if ($row["OutOfDate"]) { - print ""; - } - print ""; - print $locs[$row["LocationID"]].""; - print $cats[$row["CategoryID"]].""; - $url = ""; - $url.=""; - $url.=$row["Name"]; - $url.= " ".$row["Version"].""; - print $url.""; - print "   ".$row["NumVotes"].""; - if (isset($my_votes[$row["ID"]])) { - print "  ".__("Yes").""; - if (isset($my_notify[$row["ID"]])) { - print "  ".__("Yes").""; - print $row["Description"].""; - - # print the package manager, also determine if it is managed - # - $managed = 1; - # if (isset($devs[$row["AURMaintainerUID"]])) { - # print $devs[$row["AURMaintainerUID"]]["Username"]; - # } else - # if (isset($tus[$row["MaintainerUID"]])) { - # print $tus[$row["MaintainerUID"]]["Username"]; - if (isset($users[$row["MaintainerUID"]])) { - # Add a link to the user packages, e.g, if you click on the Solve the sorting problem, so we can force the - # maintainer name, you will be redirected to a page with the user packages. - $user = $users[$row["MaintainerUID"]]["Username"]; - print "".$users[$row["MaintainerUID"]]["Username"].""; - } else { - print ""; - print __("orphan"); - print ""; - $managed = 0; - } - print ""; -# print $manage_url." 
\n"; - print "
\n"; -# print "\n"; - if ($_REQUEST["do_MyPackages"]) { - print "\n"; - } - if ($_REQUEST["do_Orphans"]) { - print "\n"; - } - - if ($SID) { - # The 'Actions' box - # - print "
"; - print ""; - print ""; - print "
"; - } - - print "\n"; - print "\n"; - print " \n"; + print "\n"; + print "
\n"; - print " \n"; - print " \n"; - - # figure out the results to use - $first = $O + 1; - - if (($PP+$O) > $allresults) { - $last = $allresults; - } else { - $last = $PP + $O; - } - - # print number of results - # ok this styling sucks - # patches welcome! - print ""; - - # first print the legend - print " \n"; + if ($SID) { + if ($row["OutOfDate"]) { + $c = "outofdate"; + } + print " \n"; + } + print " \n"; + print " \n"; + print " \n"; + print " \n"; + if ($SID) { + print " \n"; + } else { + print " \n"; + } + print " \n"; + } else { + print " \n"; + } + } + print " \n"; + print " \n"; + print "\n"; + + } + print "
"; - print __("Showing results %s - %s of %s", array($first, $last, $allresults)); - print "
"; - print " \n"; - if ($SID) { - print ' '.__("Out of Date").' '."    "; + (($i % 2) == 0) ? $c = "data1" : $c = "data2"; + print "
"; + print ""; + if ($row["OutOfDate"]) { + print ""; + } + print ""; + print $row["Location"].""; + print $row["Category"].""; + $url = ""; + $url.=""; + $url.=$row["Name"]; + $url.= " ".$row["Version"].""; + print $url.""; + print "   ".$row["NumVotes"].""; + if (isset($row["Voted"])) { + print "  ".__("Yes").""; + if (isset($row["Notify"])) { + print "  ".__("Yes").""; + print $row["Description"].""; + + if (isset($row["Maintainer"])) { + print "".$row['Maintainer'].""; + } else { + print ""; + print __("orphan"); + print ""; + } + print "
\n"; + print "
\n"; + + if ($SID) { + // The 'Actions' box + // + print "
"; + print ""; + print ""; + print "
"; + } + + print "\n"; + print "\n"; + print " \n"; - print "\n"; - print "
\n"; + print " \n"; + print " \n"; + + // figure out the results to use + $first = $O + 1; + + if (($PP+$O) > $total) { + $last = $total; + } else { + $last = $PP + $O; + } + + // print number of results + // ok this styling sucks + // patches welcome! + print ""; + + // first print the legend + print " \n"; + print " "; + + + // now print the forward and back buttons on the bottom + // LEFT + print " "; + print " "; + // RIGHT + print " \n"; + print " \n"; } - print " \n"; - print " "; - - - # now print the forward and back buttons on the bottom - # LEFT - print " "; - print " "; - # RIGHT - print " \n"; - print " \n"; - } - print "
"; + print __("Showing results %s - %s of %s", array($first, $last, $total)); + print "
"; + print " \n"; + if ($SID) { + print ' '.__("Out of Date").' '."    "; + } + print "
"; + if (($O-$PP) >= 0) { + print "" . __("Less") . ""; + } else if ($O<$PP && $O>0) { + print "" . __("Less") . ""; + } + print " "; + if ($total - $PP - $O > 0) { + print "" . + __("More") . ""; + } + print "
"; - if (($O-$PP) >= 0) { - print "" . __("Less") . ""; - } else if ($O<$PP && $O>0) { - print "" . __("Less") . ""; - } - print " "; - if (mysql_num_rows(db_query($qnext, $dbh))) { - print "" . - __("More") . ""; - } - print "
\n"; - print "
\n"; - print "\n"; - print "\n"; - - return; + print "
\n"; + print " \n"; + print "\n"; + print "\n"; + print "
\n"; + print "\n"; + + return; } -# vim: ts=2 sw=2 noet ft=php +# vim: ts=4 sw=4 et ft=php ?> diff --git a/web/template/header.php b/web/template/header.php index ea8d43d8..88fd6f65 100644 --- a/web/template/header.php +++ b/web/template/header.php @@ -48,7 +48,7 @@ if (isset($_COOKIE["AURSID"])) { ?>
    • -
    • +
  • ">