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-18-g5258