From e1dc3e8a16ad5766bb1ba7edeaa84723f25abb75 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Roland=20H=C3=A4der?= <roland@mxchange.org>
Date: Mon, 29 May 2023 08:29:10 +0200
Subject: [PATCH] Continued: - is_instance_registered() caused tons of SQL
 queries, let's introduce some   cache here - 1 hour for recheck is good for
 development (even shorter) but a bad idea in   the wild

---
 config.defaults.json |  4 +--
 fba.py               | 66 ++++++++++++++++++++++++++++++++++++--------
 2 files changed, 56 insertions(+), 14 deletions(-)

diff --git a/config.defaults.json b/config.defaults.json
index ca48eef..7cd87ed 100644
--- a/config.defaults.json
+++ b/config.defaults.json
@@ -10,7 +10,7 @@
     "bot_token"         : "",
     "bot_visibility"    : "unlisted",
     "slogan"            : "### Your footer slogan ###",
-    "recheck_instance"  : 3600,
-    "recheck_block"     : 3600,
+    "recheck_instance"  : 43200,
+    "recheck_block"     : 43200,
     "misskey_offset"    : 100
 }
diff --git a/fba.py b/fba.py
index c294a2d..56514ad 100644
--- a/fba.py
+++ b/fba.py
@@ -40,7 +40,9 @@ blacklist = [
     # Flooder (?)
     "mastotroll.netz.org",
     # Testing/developing installations
-    "ngrok.io", "ngrok-free.app",
+    "ngrok.io",
+    "ngrok-free.app",
+    "misskeytest.chn.moe",
 ]
 
 # Array with pending errors needed to be written to database
@@ -110,6 +112,9 @@ language_mapping = {
 # URL for fetching peers
 get_peers_url = "/api/v1/instance/peers"
 
+# Cache for redundant SQL queries
+cache = {}
+
 # Connect to database
 connection = sqlite3.connect("blocks.db")
 cursor = connection.cursor()
@@ -126,6 +131,41 @@ patterns = [
     re.compile("^[a-f0-9]{7}$"),
 ]
 
+##### Cache #####
+
+def is_cache_initialized(key: str) -> bool:
+    return key in cache
+
+def set_all_cache_key(key: str, rows: list, value: any):
+    # DEBUG: print(f"DEBUG: key='{key}',rows()={len(rows)},value[]={type(value)} - CALLED!")
+    if not is_cache_initialized(key):
+        # DEBUG: print(f"DEBUG: Cache for key='{key}' not initialized.")
+        cache[key] = {}
+
+    for sub in rows:
+        # DEBUG: print(f"DEBUG: Setting key='{key}',sub[{type(sub)}]='{sub}'")
+
+        if isinstance(sub, tuple):
+            cache[key][sub[0]] = value
+        else:
+            print(f"WARNING: Unsupported type row[]='{type(row)}'")
+
+    # DEBUG: print("DEBUG: EXIT!")
+
+def set_cache_key(key: str, sub: str, value: any):
+    if not is_cache_initialized(key):
+        print(f"WARNING: Bad method call, key='{key}' is not initialized yet.")
+        raise Exception(f"Cache for key='{key}' is not initialized, but function called")
+
+    cache[key][sub] = value
+
+def is_cache_key_set(key: str, sub: str) -> bool:
+    if not is_cache_initialized(key):
+        print(f"WARNING: Bad method call, key='{key}' is not initialized yet.")
+        raise Exception(f"Cache for key='{key}' is not initialized, but function called")
+
+    return sub in cache[key]
+
 def add_peers(rows: dict) -> list:
     # DEBUG: print(f"DEBUG: rows()={len(rows)} - CALLED!")
     peers = list()
@@ -872,19 +912,19 @@ def block_instance(blocker: str, blocked: str, reason: str, block_level: str):
 
 def is_instance_registered(domain: str) -> bool:
     # DEBUG: print(f"DEBUG: domain='{domain}' - CALLED!")
-    # Default is not registered
-    registered = False
+    if not is_cache_initialized("is_registered"):
+        print(f"DEBUG: Cache for {__name__} not initialized, fetching all rows ...")
+        try:
+            cursor.execute("SELECT domain FROM instances")
 
-    try:
-        cursor.execute(
-            "SELECT rowid FROM instances WHERE domain = ? LIMIT 1", [domain]
-        )
+            # Check Set all
+            set_all_cache_key("is_registered", cursor.fetchall(), True)
+        except BaseException as e:
+            print(f"ERROR: failed SQL query: domain='{domain}',exception[{type(e)}]:'{str(e)}'")
+            sys.exit(255)
 
-        # Check condition
-        registered = cursor.fetchone() != None
-    except BaseException as e:
-        print(f"ERROR: failed SQL query: last_seen='{last_seen}'blocker='{blocker}',blocked='{blocked}',block_level='{block_level}',first_seen='{first_seen}',last_seen='{last_seen}',exception:'{str(e)}'")
-        sys.exit(255)
+    # Is cache found?
+    registered = is_cache_key_set("is_registered", domain)
 
     # DEBUG: print(f"DEBUG: registered='{registered}' - EXIT!")
     return registered
@@ -915,6 +955,8 @@ def add_instance(domain: str, origin: str, originator: str, path: str = None):
             ),
         )
 
+        set_cache_key("is_registered", domain, True)
+
         for key in nodeinfos:
             # DEBUG: print(f"DEBUG: key='{key}',domain='{domain}',nodeinfos[key]={nodeinfos[key]}")
             if domain in nodeinfos[key]:
-- 
2.39.2