From c3685dc52c219a2ebace65f063fbf256d49fdc66 Mon Sep 17 00:00:00 2001
From: nico <nico@magicbroccoli.de>
Date: Sun, 21 Jul 2019 22:46:25 +0200
Subject: missing create method
+ add check method to prevent sqlite3.OperationalError tracebacks
+ add create method to create base schema if missing
thanks to @weiss for reporting this
---
main.py | 28 ++++++++++++++++++++++------
schema.sql | 16 ++++++++++++++++
2 files changed, 38 insertions(+), 6 deletions(-)
create mode 100644 schema.sql
diff --git a/main.py b/main.py
index 2156d6e..cb85aad 100755
--- a/main.py
+++ b/main.py
@@ -31,6 +31,8 @@ class AbuseReport:
def main(self):
"""main method guiding the actions to take"""
+ # run check method before each execution
+ self.check()
if self.infile is None:
# infile unset -> report top10
@@ -43,6 +45,23 @@ class AbuseReport:
# close sqlite connection
self.conn.close()
+ def check(self):
+ # check if the minimum requirements are met
+ table = ('table', 'spam')
+ master = self.conn.execute('''SELECT type, name from sqlite_master;''').fetchall()
+
+ # if not run create method
+ if table not in master:
+ self.create()
+
+ def create(self):
+ # open and execute base schema file
+ script = "/".join([self.path, "schema.sql"])
+ with open(script) as file:
+ schema = file.read()
+
+ self.conn.executescript(schema)
+
def egest(self):
"""egest method returning the database results"""
# init result list
@@ -74,12 +93,9 @@ class AbuseReport:
else:
# in any other case return top 10 view
- if self.config.get_at("top10_view"):
- result = self.conn.execute('''SELECT * FROM "top10"''').fetchall()
- else:
- result = self.conn.execute('''SELECT COUNT(*) AS messages, COUNT(DISTINCT user) AS bots, domain AS
- domain FROM spam WHERE ts BETWEEN DATE('now','-14 days') AND DATE('now') GROUP BY domain
- ORDER BY 1 DESC LIMIT 10;''').fetchall()
+ result = self.conn.execute('''SELECT COUNT(*) AS messages, COUNT(DISTINCT user) AS bots, domain AS
+ domain FROM spam WHERE ts BETWEEN DATE('now','-14 days') AND DATE('now') GROUP BY domain
+ ORDER BY 1 DESC LIMIT 10;''').fetchall()
# tabelize data
spam_table = tabulate.tabulate(result, headers=["messages", "bots", "domain", "first seen", "last seen"],
diff --git a/schema.sql b/schema.sql
new file mode 100644
index 0000000..3925ec6
--- /dev/null
+++ b/schema.sql
@@ -0,0 +1,16 @@
+-- spam table
+create table IF not exists spam
+(
+ user TEXT,
+ domain TEXT,
+ ts TEXT,
+ message TEXT,
+ primary key (domain, ts)
+);
+
+-- indicies
+create index if not exists domain_tx_idx
+ on spam (domain, ts);
+
+create index if not exists user_domain_idx
+ on spam (user, domain);
--
cgit v1.2.3-54-g00ecf