[aur-dev] [PATCH 3/5] Cache all front page stats in APC if available
Use the APC cache to store all of the counts and the recently updated package list in a cache, which cuts down on the number of database queries needed. If the data isn't perfectly up to date we will survive. Signed-off-by: Dan McGee <dan@archlinux.org> --- web/lib/stats.inc | 52 +++++++++++++++++++++++----------- web/template/stats/updates_table.php | 5 +-- 2 files changed, 37 insertions(+), 20 deletions(-) diff --git a/web/lib/stats.inc b/web/lib/stats.inc index 6fbc033..deb246e 100644 --- a/web/lib/stats.inc +++ b/web/lib/stats.inc @@ -2,10 +2,38 @@ include_once('aur.inc'); +# Check if APC extension is loaded +if (!defined('EXTENSION_LOADED_APC')) + define('EXTENSION_LOADED_APC', extension_loaded('apc')); + +# run a simple db query, retrieving and/or caching the value if APC +# is available for use +# +function db_cache_value($dbq, $dbh, $key) +{ + if(!(EXTENSION_LOADED_APC && ($ret = apc_fetch($key)))) { + $result = db_query($dbq, $dbh); + $row = mysql_fetch_row($result); + $ret = $row[0]; + # set the TTL here in seconds: 300 seconds = 5 minutes + apc_store($key, $ret, 300); + } + return $ret; +} + function updates_table($dbh) { - $q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10'; - $newest_packages = db_query($q, $dbh); + $key = 'aur_recent_updates'; + if(!(EXTENSION_LOADED_APC && ($newest_packages = apc_fetch($key)))) { + $q = 'SELECT * FROM Packages WHERE DummyPkg != 1 ORDER BY GREATEST(SubmittedTS,ModifiedTS) DESC LIMIT 0 , 10'; + $result = db_query($q, $dbh); + + $newest_packages = new ArrayObject(); + while ($row = mysql_fetch_assoc($result)) { + $newest_packages->append($row); + } + apc_store($key, $newest_packages, 300); + } include('stats/updates_table.php'); } @@ -42,30 +70,20 @@ function general_stats_table($dbh) { # AUR statistics $q = "SELECT count(*) FROM Packages,PackageLocations WHERE Packages.LocationID = PackageLocations.ID AND PackageLocations.Location = 'unsupported'"; - $result = db_query($q, $dbh); - $row = mysql_fetch_row($result); - $unsupported_count = $row[0]; + $unsupported_count = db_cache_value($q, $dbh, 'aur_unsupported_count'); $q = "SELECT count(*) FROM Packages,PackageLocations WHERE Packages.LocationID = PackageLocations.ID AND PackageLocations.Location = 'community'"; - $result = db_query($q, $dbh); - $row = mysql_fetch_row($result); - $community_count = $row[0]; + $community_count = db_cache_value($q, $dbh, 'aur_community_count'); $q = "SELECT count(*) from Users"; - $result = db_query($q, $dbh); - $row = mysql_fetch_row($result); - $user_count = $row[0]; + $user_count = db_cache_value($q, $dbh, 'aur_user_count'); $q = "SELECT count(*) from Users,AccountTypes WHERE Users.AccountTypeID = AccountTypes.ID AND AccountTypes.AccountType = 'Trusted User'"; - $result = db_query($q, $dbh); - $row = mysql_fetch_row($result); - $tu_count = $row[0]; + $tu_count = db_cache_value($q, $dbh, 'aur_tu_count'); $targstamp = intval(strtotime("-7 days")); $q = "SELECT count(*) from Packages WHERE (Packages.SubmittedTS >= $targstamp OR Packages.ModifiedTS >= $targstamp)"; - $result = db_query($q, $dbh); - $row = mysql_fetch_row($result); - $update_count = $row[0]; + $update_count = db_cache_value($q, $dbh, 'aur_update_count'); include('stats/general_stats_table.php'); } diff --git a/web/template/stats/updates_table.php b/web/template/stats/updates_table.php index e1eb888..9d1af01 100644 --- a/web/template/stats/updates_table.php +++ b/web/template/stats/updates_table.php @@ -6,8 +6,7 @@ </th> </tr> -<?php while ($row = mysql_fetch_assoc($newest_packages)): ?> - +<?php foreach ($newest_packages->getIterator() as $row): ?> <tr> <td class="boxSoft"> <span class="f4"><span class="blue"> @@ -34,7 +33,7 @@ endif; </td> </tr> -<?php endwhile; ?> +<?php endforeach; ?> </table> -- 1.6.1
No need to call this function way too often, especially when on the package list page where it could be called up to once per row. Signed-off-by: Dan McGee <dan@archlinux.org> --- web/lib/pkgfuncs.inc | 13 ++++++------- web/template/header.php | 4 ++-- web/template/pkg_search_results.php | 3 ++- 3 files changed, 10 insertions(+), 10 deletions(-) diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 0c0b56c..2ebae89 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -271,6 +271,7 @@ function pkgname_from_id($id="") { function package_details($id=0, $SID="") { global $_REQUEST; global $pkgsearch_vars; + $atype = account_from_sid($SID); $q = "SELECT Packages.*,Location,Category "; $q.= "FROM Packages,PackageLocations,PackageCategories "; $q.= "WHERE Packages.LocationID = PackageLocations.ID "; @@ -304,8 +305,8 @@ function package_details($id=0, $SID="") { echo " <tr><td class='boxSoft' colspan='2'><span class='f3'>"; if ($row["Location"] == "unsupported" and ( uid_from_sid($SID) == $row["MaintainerUID"] or - (account_from_sid($SID) == "Developer" or - account_from_sid($SID) == "Trusted User"))) { + ($atype == "Developer" or + $atype == "Trusted User"))) { $edit_cat = "<a href='pkgedit.php?change_Category=1&ID="; $edit_cat .= intval($_REQUEST["ID"])."'>".$row["Category"]."</a>"; $edit_cat .= " <span class='fix'>("; @@ -511,14 +512,12 @@ function package_details($id=0, $SID="") { echo "<input type='submit' class='button' name='do_Adopt'"; echo " value='".__("Adopt Packages")."'>\n"; } else if ($row["MaintainerUID"] == uid_from_sid($SID) || - account_from_sid($SID) == "Trusted User" || - account_from_sid($SID) == "Developer") { + $atype == "Trusted User" || $atype == "Developer") { echo "<input type='submit' class='button' name='do_Disown'"; echo " value='".__("Disown Packages")."'>\n"; } - if (account_from_sid($SID) == "Trusted User" || - account_from_sid($SID) == "Developer") { + if ($atype == "Trusted User" || $atype == "Developer") { echo "<input type='submit' class='button' name='do_Delete'"; echo " value='".__("Delete Packages")."'>\n"; } @@ -554,7 +553,7 @@ function package_details($id=0, $SID="") { echo " <table class='boxSoft' width='100%'>\n"; echo " <tr>\n"; echo " <td class='boxSoftTitle'><span class='f3'>"; - if (canDeleteComment($carr["ID"], account_from_sid($SID), $SID)) { + if (canDeleteComment($carr["ID"], $atype, $SID)) { $durl = "<a href='pkgedit.php?del_Comment=1"; $durl.= "&comment_id=".$carr["ID"]."&ID=".$row["ID"]; $durl.= "'><img src='/images/x.png' border='0'"; diff --git a/web/template/header.php b/web/template/header.php index dcf99f3..5b48cd6 100644 --- a/web/template/header.php +++ b/web/template/header.php @@ -49,8 +49,8 @@ if (isset($_COOKIE["AURSID"])) { <li><a href="pkgsubmit.php"><?php print __("Submit"); ?></a></li> <li><a href="packages.php?SeB=m&K=<?php print username_from_sid($_COOKIE["AURSID"]); ?>"><?php print __("My Packages"); ?></a></li> <?php - if (account_from_sid($_COOKIE["AURSID"]) == "Trusted User" - || account_from_sid($_COOKIE["AURSID"]) == "Developer") { + $atype = account_from_sid($SID); + if ($atype == "Trusted User" || $atype == "Developer") { ?> <li><a href="tu.php"><?php print __("Trusted User"); ?></a></li> <?php diff --git a/web/template/pkg_search_results.php b/web/template/pkg_search_results.php index a1cb038..936a3df 100644 --- a/web/template/pkg_search_results.php +++ b/web/template/pkg_search_results.php @@ -48,6 +48,7 @@ $_GET['K'] = urlencode($_GET['K']); </tr> <?php +$atype = account_from_sid($SID); for ($i = 0; $row = mysql_fetch_assoc($result); $i++) { (($i % 2) == 0) ? $c = "data1" : $c = "data2"; if ($row["OutOfDate"]): $c = "outofdate"; endif; @@ -99,7 +100,7 @@ for ($i = 0; $row = mysql_fetch_assoc($result); $i++) { <option value='do_UnFlag'><?php print __("Unflag Out-of-date") ?></option> <option value='do_Adopt'><?php print __("Adopt Packages") ?></option> <option value='do_Disown'><?php print __("Disown Packages") ?></option> - <?php if (account_from_sid($SID) == "Trusted User" || account_from_sid($SID) == "Developer"): ?> + <?php if ($atype == "Trusted User" || $atype == "Developer"): ?> <option value='do_Delete'><?php print __("Delete Packages") ?></option> <?php endif; ?> <option value='do_Notify'><?php print __("Notify") ?></option> -- 1.6.1
Just like the previous patch for account_from_sid() over-usage. Signed-off-by: Dan McGee <dan@archlinux.org> --- web/html/pkgsubmit.php | 12 +++++++----- web/lib/pkgfuncs.inc | 9 +++++---- 2 files changed, 12 insertions(+), 9 deletions(-) diff --git a/web/html/pkgsubmit.php b/web/html/pkgsubmit.php index a7b4152..df25360 100644 --- a/web/html/pkgsubmit.php +++ b/web/html/pkgsubmit.php @@ -25,8 +25,10 @@ if ($_COOKIE["AURSID"]): $error = __("Error - No file uploaded"); } + $uid = uid_from_sid($_COOKIE['AURSID']); + # Temporary dir to put the tarball contents - $tempdir = UPLOAD_DIR . uid_from_sid($_COOKIE['AURSID']) . time(); + $tempdir = UPLOAD_DIR . $uid . time(); if (!$error) { if (!@mkdir($tempdir)) { @@ -278,8 +280,8 @@ if ($_COOKIE["AURSID"]): # If the package was a dummy, undummy it if ($pdata['DummyPkg']) { $q = sprintf( "UPDATE Packages SET DummyPkg = 0, SubmitterUID = %d, MaintainerUID = %d, SubmittedTS = UNIX_TIMESTAMP() WHERE ID = %d", - uid_from_sid($_COOKIE["AURSID"]), - uid_from_sid($_COOKIE["AURSID"]), + $uid, + $uid, $pdata["ID"]); db_query($q, $dbh); @@ -346,8 +348,8 @@ if ($_COOKIE["AURSID"]): mysql_real_escape_string($_REQUEST['category']), mysql_real_escape_string($new_pkgbuild['pkgdesc']), mysql_real_escape_string($new_pkgbuild['url']), - uid_from_sid($_COOKIE["AURSID"]), - uid_from_sid($_COOKIE["AURSID"]), + $uid, + $uid, mysql_real_escape_string($incoming_pkgdir . "/" . $pkg_name . ".tar.gz"), mysql_real_escape_string(URL_DIR . $pkg_name . "/" . $pkg_name . ".tar.gz")); diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 2ebae89..03acbed 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -272,6 +272,7 @@ function package_details($id=0, $SID="") { global $_REQUEST; global $pkgsearch_vars; $atype = account_from_sid($SID); + $uid = uid_from_sid($sid); $q = "SELECT Packages.*,Location,Category "; $q.= "FROM Packages,PackageLocations,PackageCategories "; $q.= "WHERE Packages.LocationID = PackageLocations.ID "; @@ -304,7 +305,7 @@ function package_details($id=0, $SID="") { echo " <tr><td class='boxSoft' colspan='2'><img src='/images/pad.gif' height='30'></td></tr>\n"; echo " <tr><td class='boxSoft' colspan='2'><span class='f3'>"; if ($row["Location"] == "unsupported" and ( - uid_from_sid($SID) == $row["MaintainerUID"] or + $uid == $row["MaintainerUID"] or ($atype == "Developer" or $atype == "Trusted User"))) { $edit_cat = "<a href='pkgedit.php?change_Category=1&ID="; @@ -479,7 +480,7 @@ function package_details($id=0, $SID="") { echo " <input type='hidden' name='ID' value='".$row["ID"]."'>\n"; # Voting Button # - $q = "SELECT * FROM PackageVotes WHERE UsersID = ".uid_from_sid($SID); + $q = "SELECT * FROM PackageVotes WHERE UsersID = ". $uid; $q.= " AND PackageID = ".$row["ID"]; if (!mysql_num_rows(db_query($q, $dbh))) { echo " <input type='submit' class='button' name='do_Vote'"; @@ -490,7 +491,7 @@ function package_details($id=0, $SID="") { } # Comment Nofify Button # - $q = "SELECT * FROM CommentNotify WHERE UserID = ".uid_from_sid($SID); + $q = "SELECT * FROM CommentNotify WHERE UserID = ". $uid; $q.= " AND PkgID = ".$row["ID"]; if (!mysql_num_rows(db_query($q, $dbh))) { echo "<input type='submit' class='button' name='do_Notify'"; @@ -511,7 +512,7 @@ function package_details($id=0, $SID="") { if ($row["MaintainerUID"] == 0) { echo "<input type='submit' class='button' name='do_Adopt'"; echo " value='".__("Adopt Packages")."'>\n"; - } else if ($row["MaintainerUID"] == uid_from_sid($SID) || + } else if ($uid == $row["MaintainerUID"] || $atype == "Trusted User" || $atype == "Developer") { echo "<input type='submit' class='button' name='do_Disown'"; echo " value='".__("Disown Packages")."'>\n"; -- 1.6.1
subject s/und/uid/ On Mon, Dec 29, 2008 at 10:36 PM, Dan McGee <dan@archlinux.org> wrote:
Just like the previous patch for account_from_sid() over-usage.
Signed-off-by: Dan McGee <dan@archlinux.org> --- web/html/pkgsubmit.php | 12 +++++++----- web/lib/pkgfuncs.inc | 9 +++++---- 2 files changed, 12 insertions(+), 9 deletions(-)
On Mon, Dec 29, 2008 at 10:36:07PM -0600, Dan McGee wrote:
Just like the previous patch for account_from_sid() over-usage.
Signed-off-by: Dan McGee <dan@archlinux.org> --- web/html/pkgsubmit.php | 12 +++++++----- web/lib/pkgfuncs.inc | 9 +++++---- 2 files changed, 12 insertions(+), 9 deletions(-)
diff --git a/web/lib/pkgfuncs.inc b/web/lib/pkgfuncs.inc index 2ebae89..03acbed 100644 --- a/web/lib/pkgfuncs.inc +++ b/web/lib/pkgfuncs.inc @@ -272,6 +272,7 @@ function package_details($id=0, $SID="") { global $_REQUEST; global $pkgsearch_vars; $atype = account_from_sid($SID); + $uid = uid_from_sid($sid);
Pushed with a small fix. $uid should be set with: $uid = uid_from_sid($SID);
On Mon, Dec 29, 2008 at 10:36:06PM -0600, Dan McGee wrote:
No need to call this function way too often, especially when on the package list page where it could be called up to once per row.
Signed-off-by: Dan McGee <dan@archlinux.org> --- web/lib/pkgfuncs.inc | 13 ++++++------- web/template/header.php | 4 ++-- web/template/pkg_search_results.php | 3 ++- 3 files changed, 10 insertions(+), 10 deletions(-)
diff --git a/web/template/header.php b/web/template/header.php index dcf99f3..5b48cd6 100644 --- a/web/template/header.php +++ b/web/template/header.php @@ -49,8 +49,8 @@ if (isset($_COOKIE["AURSID"])) { <li><a href="pkgsubmit.php"><?php print __("Submit"); ?></a></li> <li><a href="packages.php?SeB=m&K=<?php print username_from_sid($_COOKIE["AURSID"]); ?>"><?php print __("My Packages"); ?></a></li> <?php - if (account_from_sid($_COOKIE["AURSID"]) == "Trusted User" - || account_from_sid($_COOKIE["AURSID"]) == "Developer") { + $atype = account_from_sid($SID);
Pushed with one added line: $SID = $_COOKIE['AURSID']; Since SID wasn't being set in header.php.
On Sun, Jan 4, 2009 at 1:11 PM, Loui Chang <louipc.ist@gmail.com> wrote:
On Mon, Dec 29, 2008 at 10:36:06PM -0600, Dan McGee wrote:
No need to call this function way too often, especially when on the package list page where it could be called up to once per row.
Signed-off-by: Dan McGee <dan@archlinux.org> --- web/lib/pkgfuncs.inc | 13 ++++++------- web/template/header.php | 4 ++-- web/template/pkg_search_results.php | 3 ++- 3 files changed, 10 insertions(+), 10 deletions(-)
diff --git a/web/template/header.php b/web/template/header.php index dcf99f3..5b48cd6 100644 --- a/web/template/header.php +++ b/web/template/header.php @@ -49,8 +49,8 @@ if (isset($_COOKIE["AURSID"])) { <li><a href="pkgsubmit.php"><?php print __("Submit"); ?></a></li> <li><a href="packages.php?SeB=m&K=<?php print username_from_sid($_COOKIE["AURSID"]); ?>"><?php print __("My Packages"); ?></a></li> <?php - if (account_from_sid($_COOKIE["AURSID"]) == "Trusted User" - || account_from_sid($_COOKIE["AURSID"]) == "Developer") { + $atype = account_from_sid($SID);
Pushed with one added line: $SID = $_COOKIE['AURSID'];
Since SID wasn't being set in header.php.
Great catch, thanks! -Dan
participants (3)
-
Dan McGee
-
Dan McGee
-
Loui Chang