hashserv: Add API to clean unused entries

Adds an API to remove unused entries in the outhash database based on
age and if they are referenced by any unihash

Signed-off-by: Joshua Watt <JPEWhacker@gmail.com>
Signed-off-by: Richard Purdie <richard.purdie@linuxfoundation.org>

1 files changed, 19 insertions, 1 deletions

diff --git a/lib/hashserv/server.py b/lib/hashserv/server.py
index d52e1d46d..45bf476bf 100644
--- a/lib/hashserv/server.py
+++ b/lib/hashserv/server.py
@@ -4,7 +4,7 @@
 #
 
 from contextlib import closing, contextmanager
-from datetime import datetime
+from datetime import datetime, timedelta
 import enum
 import asyncio
 import logging
@@ -187,6 +187,7 @@ class ServerClient(bb.asyncrpc.AsyncServerConnection):
                 'reset-stats': self.handle_reset_stats,
                 'backfill-wait': self.handle_backfill_wait,
                 'remove': self.handle_remove,
+                'clean-unused': self.handle_clean_unused,
             })
 
     def validate_proto_version(self):
@@ -542,6 +543,23 @@ class ServerClient(bb.asyncrpc.AsyncServerConnection):
 
         self.write_message({"count": count})
 
+    async def handle_clean_unused(self, request):
+        max_age = request["max_age_seconds"]
+        with closing(self.db.cursor()) as cursor:
+            cursor.execute(
+                """
+                DELETE FROM outhashes_v2 WHERE created<:oldest AND NOT EXISTS (
+                    SELECT unihashes_v2.id FROM unihashes_v2 WHERE unihashes_v2.method=outhashes_v2.method AND unihashes_v2.taskhash=outhashes_v2.taskhash LIMIT 1
+                )
+                """,
+                {
+                    "oldest": datetime.now() - timedelta(seconds=-max_age)
+                }
+            )
+            count = cursor.rowcount
+
+        self.write_message({"count": count})
+
     def query_equivalent(self, cursor, method, taskhash):
         # This is part of the inner loop and must be as fast as possible
         cursor.execute(