1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
|
import sqlite3
from datetime import datetime
class CanaryDB:
def __init__(self, filename):
self.conn = sqlite3.connect(filename, detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
# Create Tables if necessary
cursor = self.conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS pings (listAddress, address, uuid, timesent timestamp, timereceived timestamp)")
cursor.execute("CREATE TABLE IF NOT EXISTS accounts (list, address, imapserver, password)")
def close(self):
self.conn.close()
def add_account(self, listAddress, address, imapserver, password):
cursor = self.conn.cursor()
cursor.execute("INSERT INTO accounts (list, address, imapserver, password) VALUES (?, ?, ?, ?)", \
(listAddress, address, imapserver, password))
self.conn.commit()
def remove_account(self, listAddress, address):
cursor = self.conn.cursor()
cursor.execute("DELETE FROM accounts WHERE list=? AND address=?", (listAddress, address))
self.conn.commit()
def get_accounts(self, listAddress = None):
cursor = self.conn.cursor()
if listAddress:
cursor.execute("SELECT list, address, imapserver, password FROM accounts WHERE list=?", (listAddress,));
else:
cursor.execute("SELECT list, address, imapserver, password FROM accounts");
results = list()
for row in cursor:
listAddress = row[0]
address = row[1]
imapserver = row[2]
password = row[3]
results.append((listAddress, address, imapserver, password))
return results
def get_recipients_for_list(self, listAddress):
cursor = self.conn.cursor()
cursor.execute("SELECT address FROM accounts WHERE list=?", (listAddress,));
results = list()
for row in cursor:
results.append(row[0])
return results
def ping(self, listAddress, address, time, uuid):
cursor = self.conn.cursor()
cursor.execute("INSERT INTO pings (listAddress, address, timesent, uuid) VALUES (?, ?, ?, ?)", \
(listAddress, address, time, uuid))
self.conn.commit()
def pong(self, address, time, uuid):
cursor = self.conn.cursor()
cursor.execute("UPDATE pings SET timereceived=? WHERE address=? AND uuid=?", \
(time, address, uuid))
self.conn.commit()
def get_missing_pongs(self, listAddress = None):
'''Return a list of tupls of missing pongs.
Each tupl contains (listAddress, uuid, address, time since send)'''
cursor = self.conn.cursor()
if listAddress:
cursor.execute("SELECT listAddress, uuid, address, timesent FROM pings WHERE timereceived IS NULL AND listAddress = ?", (listAddress,));
else:
cursor.execute("SELECT listAddress, uuid, address, timesent FROM pings WHERE timereceived IS NULL");
now = datetime.now()
results = list()
for row in cursor:
listAddress = row[0]
uuid = row[1]
address = row[2]
delta = now - row[3]
results.append((listAddress, uuid, address, delta))
return results
|
