implement automatic tags table maintenance

2 files changed, 28 insertions, 0 deletions

diff --git a/functions.php b/functions.php
index 553b26563..2331c89f4 100644
--- a/functions.php
+++ b/functions.php
@@ -5798,6 +5798,10 @@
 
 		purge_orphans($link);
 
+		$rc = cleanup_tags($link, 14, 50000);
+
+		if ($debug) _debug("$rc uncached tags cleaned.");
+
 	} // function update_daemon_common
 
 	function sanitize_article_content($text) {
@@ -7137,4 +7141,22 @@
 		return $ids;
 	}
 
+	function cleanup_tags($link, $days = 14, $limit = 1000) {
+
+		if (DB_TYPE == "pgsql") {
+			$interval_query = "date_updated < NOW() - INTERVAL '$days days'";
+		} else if (DB_TYPE == "mysql") {
+			$interval_query = "date_updated < DATE_SUB(NOW(), INTERVAL $days DAY)";
+		}
+
+		$query = "SELECT ttrss_tags.id AS id 
+			FROM ttrss_tags, ttrss_user_entries, ttrss_entries 
+			WHERE post_int_id = int_id AND $interval_query AND
+			ref_id = ttrss_entries.id AND tag_cache != '' LIMIT $limit";
+
+		$result = db_query($link, $query);
+
+		return db_affected_rows($link, $result);
+	}
+
 ?>
diff --git a/update.php b/update.php
index 64b5795fa..ac1d84c8e 100755
--- a/update.php
+++ b/update.php
@@ -19,6 +19,7 @@
 		print "  -feeds         - update feeds\n";
 		print "  -feedbrowser   - update feedbrowser\n";
 		print "  -daemon        - start single-process update daemon\n";
+		print "  -cleanup-tags  - perform tags table maintenance\n";
 		print "  -help          - show this help\n";
 		return;
 	}
@@ -89,6 +90,11 @@
 
 	}
 
+	if ($op == "-cleanup-tags") {
+		$rc = cleanup_tags($link, 14, 50000);
+		print "$rc tags deleted.\n";
+	}
+
 	db_close($link);
 
 	unlink(LOCK_DIRECTORY . "/$lock_filename");