[PATCH v3] Cache package provider and dependency information

6 Oct 2019

The package provider and dependency queries are quite CPU-intensive and
usually yield rather small result sets. Cache these values if the global
caching mechanism is enabled.

Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>
---
Fixed the key used for package dependencies.

 web/lib/cachefuncs.inc.php | 17 ++++++++++++++++
 web/lib/pkgfuncs.inc.php   | 40 +++++++++++---------------------------
 2 files changed, 28 insertions(+), 29 deletions(-)

diff --git a/web/lib/cachefuncs.inc.php b/web/lib/cachefuncs.inc.php
index 881ad8f..b2b96c2 100644
--- a/web/lib/cachefuncs.inc.php
+++ b/web/lib/cachefuncs.inc.php
@@ -79,4 +79,21 @@ function db_cache_value($dbq, $key, $ttl=600) {
 	return $value;
 }
 
+# Run a simple db query, retrieving and/or caching the result set if APC is
+# available for use. Accepts an optional TTL value (defaults to 600 seconds).
+function db_cache_result($dbq, $key, $fetch_style=PDO::FETCH_NUM, $ttl=600) {
+	$dbh = DB::connect();
+	$status = false;
+	$value = get_cache_value($key, $status);
+	if (!$status) {
+		$result = $dbh->query($dbq);
+		if (!$result) {
+			return false;
+		}
+		$value = $result->fetchAll($fetch_style);
+		set_cache_value($key, $value, $ttl);
+	}
+	return $value;
+}
+
 ?>
diff --git a/web/lib/pkgfuncs.inc.php b/web/lib/pkgfuncs.inc.php
index 00f0cc4..3e4b2a8 100644
--- a/web/lib/pkgfuncs.inc.php
+++ b/web/lib/pkgfuncs.inc.php
@@ -222,17 +222,7 @@ function pkg_providers($name) {
 	$q.= "UNION ";
 	$q.= "SELECT 0, Name FROM OfficialProviders ";
 	$q.= "WHERE Provides = " . $dbh->quote($name);
-	$result = $dbh->query($q);
-
-	if (!$result) {
-		return array();
-	}
-
-	$providers = array();
-	while ($row = $result->fetch(PDO::FETCH_NUM)) {
-		$providers[] = $row;
-	}
-	return $providers;
+	return db_cache_result($q, 'providers:' . $name);
 }
 
 /**
@@ -244,26 +234,18 @@ function pkg_providers($name) {
  * @return array All package dependencies for the package
  */
 function pkg_dependencies($pkgid, $limit) {
-	$deps = array();
 	$pkgid = intval($pkgid);
-	if ($pkgid > 0) {
-		$dbh = DB::connect();
-		$q = "SELECT pd.DepName, dt.Name, pd.DepDesc, ";
-		$q.= "pd.DepCondition, pd.DepArch, p.ID ";
-		$q.= "FROM PackageDepends pd ";
-		$q.= "LEFT JOIN Packages p ON pd.DepName = p.Name ";
-		$q.= "LEFT JOIN DependencyTypes dt ON dt.ID = pd.DepTypeID ";
-		$q.= "WHERE pd.PackageID = ". $pkgid . " ";
-		$q.= "ORDER BY pd.DepName LIMIT " . intval($limit);
-		$result = $dbh->query($q);
-		if (!$result) {
-			return array();
-		}
-		while ($row = $result->fetch(PDO::FETCH_NUM)) {
-			$deps[] = $row;
-		}
+	if (!$pkgid) {
+		return array();
 	}
-	return $deps;
+	$q = "SELECT pd.DepName, dt.Name, pd.DepDesc, ";
+	$q.= "pd.DepCondition, pd.DepArch, p.ID ";
+	$q.= "FROM PackageDepends pd ";
+	$q.= "LEFT JOIN Packages p ON pd.DepName = p.Name ";
+	$q.= "LEFT JOIN DependencyTypes dt ON dt.ID = pd.DepTypeID ";
+	$q.= "WHERE pd.PackageID = ". $pkgid . " ";
+	$q.= "ORDER BY pd.DepName LIMIT " . intval($limit);
+	return db_cache_result($q, 'dependencies:' . $pkgid);
 }
 
 /**
-- 
2.23.0

    