From 3836e75c89573baca6b029a5fd36401696b86e8d Mon Sep 17 00:00:00 2001
From: Max Kostikov <max@kostikov.co>
Date: Thu, 21 Jan 2021 09:08:53 +0100
Subject: Implement SQL query background caching

---
 Zotlabs/Daemon/Cache_query.php | 34 ++++++++++++++++++++++++++++
 Zotlabs/Daemon/Expire.php      |  8 +++----
 include/contact_widgets.php    | 38 ++++++++++++++++++--------------
 include/taxonomy.php           | 50 ++++++++++++++++++++++--------------------
 4 files changed, 85 insertions(+), 45 deletions(-)
 create mode 100644 Zotlabs/Daemon/Cache_query.php

diff --git a/Zotlabs/Daemon/Cache_query.php b/Zotlabs/Daemon/Cache_query.php
new file mode 100644
index 000000000..18d19cdf2
--- /dev/null
+++ b/Zotlabs/Daemon/Cache_query.php
@@ -0,0 +1,34 @@
+<?php
+
+namespace Zotlabs\Daemon;
+
+use Zotlabs\Lib\Cache;
+
+class Cache_query {
+
+	static public function run($argc, $argv) {
+
+		if(! $argc == 3)
+			return;
+
+		$key = $argv[1];
+
+		$pid = get_config('procid', $key, false);
+		if ($pid && (function_exists('posix_kill') ? posix_kill($pid, 0) : true)) {
+			logger($key . ': procedure already run with pid ' . $pid, LOGGER_DEBUG);
+			return;
+		}
+
+		$pid = getmypid();
+		set_config('procid', $key, $pid);
+
+		array_shift($argv);
+		array_shift($argv);
+
+		$r = call_user_func_array('q', $argv);
+		if($r)
+			Cache::set($key, serialize($r));
+
+		del_config('procid', $key);
+	}
+}
diff --git a/Zotlabs/Daemon/Expire.php b/Zotlabs/Daemon/Expire.php
index 8ca92b6c5..c4ff8aec6 100644
--- a/Zotlabs/Daemon/Expire.php
+++ b/Zotlabs/Daemon/Expire.php
@@ -9,14 +9,14 @@ class Expire {
 
 		cli_startup();
 
-		$pid = get_config('expire', 'procid', false);
+		$pid = get_config('procid', 'expire', false);
 		if ($pid && (function_exists('posix_kill') ? posix_kill($pid, 0) : true)) {
-			logger('Expire: procedure already run with pid ' . $pid, LOGGER_DEBUG);
+			logger('procedure already run with pid ' . $pid, LOGGER_DEBUG);
 			return;
 		}
 
 		$pid = getmypid();
-		set_config('expire', 'procid', $pid);
+		set_config('procid', 'expire', $pid);
 
 		// perform final cleanup on previously delete items
 
@@ -101,6 +101,6 @@ class Expire {
 			logger('Expire: sys: done', LOGGER_DEBUG);
 		}
 
-		del_config('expire', 'procid');
+		del_config('procid', 'expire');
 	}
 }
diff --git a/include/contact_widgets.php b/include/contact_widgets.php
index 3b22a3c6d..0719d4011 100644
--- a/include/contact_widgets.php
+++ b/include/contact_widgets.php
@@ -71,35 +71,39 @@ function categories_widget($baseurl,$selected = '') {
 
 	$item_normal = item_normal();
 
-    $key = __FUNCTION__ . "-" . App::$profile['profile_uid'];
-    $content = Cache::get($key, '5 MINUTE');
+	$key = __FUNCTION__ . "-" . App::$profile['profile_uid'];
 
+	$content = Cache::get($key, '5 MINUTE');
 	if (! $content) {
-		$r = q("select distinct(term.term) from term join item on term.oid = item.id
-			where item.uid = %d
-			and term.uid = item.uid
-			and term.ttype = %d
-			and term.otype = %d
-			and item.owner_xchan = '%s'
-			and item.item_wall = 1
-			and item.verb != '%s'
+
+		$content = Cache::get($key, '1 MONTH');
+
+		$arr = [
+			"SELECT distinct(term.term) FROM term JOIN item ON term.oid = item.id
+			WHERE item.uid = %d
+			AND term.uid = item.uid
+			AND term.ttype = %d
+			AND term.otype = %d
+			AND item.owner_xchan = '%s'
+			AND item.item_wall = 1
+			AND item.verb != '%s'
 			$item_normal
 			$sql_extra
-			order by term.term asc",
+			ORDER BY term.term ASC",
 			intval(App::$profile['profile_uid']),
 			intval(TERM_CATEGORY),
 			intval(TERM_OBJ_POST),
 			dbesc(App::$profile['channel_hash']),
 			dbesc(ACTIVITY_UPDATE)
-		);
+		];
+
+		\Zotlabs\Daemon\Master::Summon([ 'Cache_query', $key, $arr ]);
 	}
-	else
-		$r = unserialize($content);
 
-	$terms = array();
-	if($r && count($r)) {
+	$r = unserialize($content);
 
-		Cache::set($key, serialize($r));
+	$terms = [];
+	if($r && count($r)) {
 
 		foreach($r as $rr)
 			$terms[] = array('name' => $rr['term'], 'selected' => (($selected == $rr['term']) ? 'selected' : ''));
diff --git a/include/taxonomy.php b/include/taxonomy.php
index e06568d19..5681db88c 100644
--- a/include/taxonomy.php
+++ b/include/taxonomy.php
@@ -354,30 +354,32 @@ function pub_tagadelic($net,$site,$limit,$recent,$safemode,$type) {
                         $sql_extra .= " and not term.term in ( " . stringify_array($unsafetags,true) . ") ";
                 }
         }
-
-
-        $key = __FUNCTION__ . "-" . md5($site . $recent . $safemode . $limit . $type);
-        $content = Cache::get($key, '1 MINUTE');
-
-        if(! $content) {
-                // Fetch tags
-                $r = q("SELECT term, count(term) AS total FROM term LEFT JOIN item ON term.oid = item.id
-                        where term.ttype = %d
-                        and otype = %d and item_type = %d
-                        $sql_extra $uids $item_normal
-                        group by term order by total desc %s",
-                        intval($type),
-                        intval(TERM_OBJ_POST),
-                        intval(ITEM_TYPE_POST),
-                        ((intval($count)) ? "limit $count" : '')
-                );
-        } else
-                $r = unserialize($content);
-
-        if(! $r)
-                return array();
-        else
-                Cache::set($key, serialize($r));
+		
+		$key = __FUNCTION__ . "-" . md5($site . $recent . $safemode . $limit . $type);
+		
+		$content = Cache::get($key, '1 MINUTE');
+		if(! $content) {
+			
+			$content = Cache::get($key, '1 WEEK');
+			$arr = [
+				"SELECT term, count(term) AS total FROM term LEFT JOIN item ON term.oid = item.id
+				WHERE term.ttype = %d
+				AND otype = %d
+				AND item_type = %d
+				$sql_extra $uids $item_normal
+				GROUP BY term ORDER BY total DESC %s",
+				intval($type),
+				intval(TERM_OBJ_POST),
+				intval(ITEM_TYPE_POST),
+				(intval($count) ? "LIMIT $count" : '')
+			];
+			
+			\Zotlabs\Daemon\Master::Summon([ 'Cache_query', $key, $arr ]);
+		}
+		
+		$r = unserialize($content);
+		if(! $r)
+			return [];
 
         return Zotlabs\Text\Tagadelic::calc($r);
 }
-- 
cgit v1.2.3