From 0cfa3cc550551ceb7e5b116348e6d94dafee4a29 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Roland=20H=C3=A4der?= Date: Sun, 21 May 2023 09:36:08 +0200 Subject: [PATCH] Continued: - added first_seen and last_updated to instances table - moved timestamp creation to functions (lesser parameter) --- blocks_empty.db | Bin 28672 -> 28672 bytes fba.py | 31 ++++++++++++++++++------------- fetch_blocks.py | 20 ++++++++------------ 3 files changed, 26 insertions(+), 25 deletions(-) diff --git a/blocks_empty.db b/blocks_empty.db index 93efe69100d170780db531eec9a331f466abdfae..bcb93703d52078ccf7038842654a614701d92c94 100644 GIT binary patch delta 122 zcmZp8z}WDBae}nq8wLgjb|_{9(n=F`j3wVN=y^}%<=0_g<>6=GYvz5&!_V8zYs^!= zv2i*#SCb+aySThOV~gNqXP)<3Itpo-Ma3oY#i^-z3Z8x;uI{cuItn?7K%vrtl*E$M M6qx8{Bi?gK0P5x-zW@LL delta 80 zcmZp8z}WDBae}nqD+UGzb|_{9(h3uGj3r+&=y^}%<=0_g<*{VoYvz5&W69giYs^!= eSy130H&@1#FcRee diff --git a/fba.py b/fba.py index 3136ea2..c4e23b2 100644 --- a/fba.py +++ b/fba.py @@ -35,7 +35,8 @@ def update_last_blocked(domain: str): # NOISY-DEBUG: print("DEBUG: Updating last_blocked for domain", domain) try: - c.execute("UPDATE instances SET last_blocked = ? WHERE domain = ?", [ + c.execute("UPDATE instances SET last_blocked = ?, last_updated = ? WHERE domain = ?", [ + time.time(), time.time(), domain ]) @@ -48,8 +49,9 @@ def update_last_error(domain: str, res: any): # NOISY-DEBUG: print("DEBUG: domain,res.status_code", domain, res.status_code) try: - c.execute("UPDATE instances SET last_status_code = ? WHERE domain = ?", [ + c.execute("UPDATE instances SET last_status_code = ?, last_updated = ? WHERE domain = ?", [ res.status_code, + time.time(), domain ]) @@ -61,7 +63,8 @@ def update_last_nodeinfo(domain: str): # NOISY-DEBUG: print("DEBUG: Updating last_nodeinfo for domain:", domain) try: - c.execute("UPDATE instances SET last_nodeinfo = ? WHERE domain = ?", [ + c.execute("UPDATE instances SET last_nodeinfo = ?, last_updated = ? WHERE domain = ?", [ + time.time(), time.time(), domain ]) @@ -176,9 +179,10 @@ def update_block_reason(reason: str, blocker: str, blocked: str, block_level: st # NOISY: print("--- Updating block reason:", reason, blocker, blocked, block_level) try: c.execute( - "UPDATE blocks SET reason = ? WHERE blocker = ? AND blocked = ? AND block_level = ? AND reason = ''", + "UPDATE blocks SET reason = ?, last_seen = ? WHERE blocker = ? AND blocked = ? AND block_level = ? AND reason = ''", ( reason, + time.time(), blocker, blocked, block_level @@ -189,13 +193,13 @@ def update_block_reason(reason: str, blocker: str, blocked: str, block_level: st print("ERROR: failed SQL query:", reason, blocker, blocked, block_level) sys.exit(255) -def update_last_seen(last_seen: int, blocker: str, blocked: str, block_level: str): - # NOISY: print("--- Updating last_seen:", last_seen, blocker, blocked, block_level) +def update_last_seen(blocker: str, blocked: str, block_level: str): + # NOISY: print("--- Updating last_seen for:", blocker, blocked, block_level) try: c.execute( "UPDATE blocks SET last_seen = ? WHERE blocker = ? AND blocked = ? AND block_level = ?", ( - last_seen, + time.time(), blocker, blocked, block_level @@ -206,8 +210,8 @@ def update_last_seen(last_seen: int, blocker: str, blocked: str, block_level: st print("ERROR: failed SQL query:", last_seen, blocker, blocked, block_level) sys.exit(255) -def block_instance(blocker: str, blocked: str, reason: str, block_level: str, first_added: int, last_seen: int): - # NOISY-DEBUG: print("DEBUG: blocker,blocked,reason,block_level,first_added,last_seen:", blocker, blocked, reason, block_level, first_added, last_seen) +def block_instance(blocker: str, blocked: str, reason: str, block_level: str): + # NOISY-DEBUG: print("DEBUG: blocker,blocked,reason,block_level:", blocker, blocked, reason, block_level) if blocker.find("@") > 0: print("WARNING: Bad blocker:", blocker) raise @@ -224,8 +228,8 @@ def block_instance(blocker: str, blocked: str, reason: str, block_level: str, fi blocked, reason, block_level, - first_added, - last_seen + time.time(), + time.time() ), ) @@ -245,13 +249,14 @@ def add_instance(domain: str, origin: str, originator: str): print(f"--- Adding new instance {domain} (origin: {origin})") try: c.execute( - "INSERT INTO instances (domain,origin,originator,hash,software) VALUES (?, ?, ?, ?, ?)", + "INSERT INTO instances (domain, origin, originator, hash, software, first_seen) VALUES (?, ?, ?, ?, ?, ?)", ( domain, origin, originator, get_hash(domain), - determine_software(domain) + determine_software(domain), + time.time() ), ) diff --git a/fetch_blocks.py b/fetch_blocks.py index a4248ac..ba02a3e 100644 --- a/fetch_blocks.py +++ b/fetch_blocks.py @@ -78,7 +78,6 @@ for blocker, software in fba.c.fetchall(): # NOISY-DEBUG: print("DEBUG: Domain wasn't found, adding:", blocked, blocker) fba.add_instance(blocked, blocker, argv[0]) - timestamp = int(time.time()) fba.c.execute( "SELECT * FROM blocks WHERE blocker = ? AND blocked = ? AND block_level = ?", ( @@ -90,7 +89,7 @@ for blocker, software in fba.c.fetchall(): if fba.c.fetchone() == None: # NOISY-DEBUG: print("DEBUG: Blocking:", blocker, blocked, block_level) - fba.block_instance(blocker, blocked, "unknown", block_level, timestamp, timestamp) + fba.block_instance(blocker, blocked, "unknown", block_level) if block_level == "reject": # NOISY-DEBUG: print("DEBUG: Adding to blockdict:", blocked) @@ -101,7 +100,7 @@ for blocker, software in fba.c.fetchall(): }) else: # NOISY-DEBUG: print("DEBUG: Updating last_seen:", blocker, blocked, block_level) - fba.update_last_seen(timestamp, blocker, blocked, block_level) + fba.update_last_seen(blocker, blocked, block_level) fba.conn.commit() @@ -241,7 +240,6 @@ for blocker, software in fba.c.fetchall(): # NOISY-DEBUG: print("DEBUG: Updating domain: ", searchres[0]) blocked = searchres[0] - timestamp = int(time.time()) fba.c.execute( "SELECT * FROM blocks WHERE blocker = ? AND blocked = ? AND block_level = ?", ( @@ -252,7 +250,7 @@ for blocker, software in fba.c.fetchall(): ) if fba.c.fetchone() == None: - fba.block_instance(blocker, blocked if blocked.count("*") <= 1 else blocked_hash, reason, block_level, timestamp, timestamp) + fba.block_instance(blocker, blocked if blocked.count("*") <= 1 else blocked_hash, reason, block_level) if block_level == "reject": blockdict.append( @@ -261,7 +259,7 @@ for blocker, software in fba.c.fetchall(): "reason": reason }) else: - fba.update_last_seen(timestamp, blocker, blocked if blocked.count("*") <= 1 else blocked_hash, block_level) + fba.update_last_seen(blocker, blocked if blocked.count("*") <= 1 else blocked_hash, block_level) if reason != '': # NOISY-DEBUG: print("DEBUG: Updating block reason:", blocker, blocked, reason) @@ -322,13 +320,12 @@ for blocker, software in fba.c.fetchall(): # NOISY-DEBUG: print("DEBUG: Hash wasn't found, adding:", blocked, blocker) fba.add_instance(blocked, blocker) - timestamp = int(time.time()) fba.c.execute( "SELECT * FROM blocks WHERE blocker = ? AND blocked = ?", (blocker, blocked), ) if fba.c.fetchone() == None: - fba.block_instance(blocker, blocked, reason, block_level, timestamp, timestamp) + fba.block_instance(blocker, blocked, reason, block_level) if block_level == "reject": blockdict.append( @@ -337,7 +334,7 @@ for blocker, software in fba.c.fetchall(): "reason": reason }) else: - fba.update_last_seen(timestamp, blocker, blocked, block_level) + fba.update_last_seen(blocker, blocked, block_level) if reason != '': # NOISY-DEBUG: print("DEBUG: Updating block reason:", blocker, blocked, reason) @@ -393,10 +390,9 @@ for blocker, software in fba.c.fetchall(): "reject" ), ) - timestamp = int(time.time()) if fba.c.fetchone() == None: - fba.block_instance(blocker, blocked, "unknown", "reject", timestamp, timestamp) + fba.block_instance(blocker, blocked, "unknown", "reject") blockdict.append( { @@ -404,7 +400,7 @@ for blocker, software in fba.c.fetchall(): "reason": None }) else: - fba.update_last_seen(timestamp, blocker, blocked, "reject") + fba.update_last_seen(blocker, blocked, "reject") if "public_comment" in peer: reason = peer["public_comment"] -- 2.39.5