1 from config import *
2 import MySQLdb
3
13
15 if self.conn != None:
16 self.conn.close()
17 self.conn = None
18
20 sql = "SELECT %s FROM %s WHERE %s LIMIT 1" % (columns, table, where)
21 self.conn.execute(sql)
22 if self.conn.rowcount == 1: return self.conn.fetchone()
23 else: return None
24
26
27 self.conn.execute(sql)
28 if self.conn.rowcount >= 1:
29 row = self.conn.fetchone()
30 while row != None:
31 yield row
32 row = self.conn.fetchone()
33 else: return
34
36 - def __init__(self, datasource=None, channel=None):
37 self.conn = None
38
39 if datasource == None:
40 return None
41
42 self.db = None
43 self.channel = channel
44
45
46 try:
47 self.db = MySQLdb.connect(user=DB_USER, passwd=DB_PASS, db=DB_NAME, host=DB_HOST, port=DB_PORT)
48 except Exception as (errno, errmsg):
49 raise Exception("DBLogger was unable to connect to the database: " \
50 +"(error %d): %s (Note: connection values should be in config.py)." % (errno,errmsg))
51 if self.db == None:
52 raise Exception("DBLogger: Unable to connect to database.")
53 self.conn = self.db.cursor()
54
55
56 self.conn.execute("SELECT ds_id FROM datasources WHERE ds_name LIKE %s LIMIT 1", (datasource,))
57 if self.conn.rowcount == 1: self.ds_id = self.conn.fetchone()
58 else: print "No datasource found matching name:", datasource
59
61 if self.conn != None:
62 self.conn.close()
63 self.conn = None
64
65 - def set_channel(self, chan):
67
68 - def add_packet(self, full=None, scapy=None,
69 bytes=None, rssi=None, location=None, datetime=None, channel=None):
70 if (self.conn==None): raise Exception("DBLogger requires active connection status.")
71
72 if bytes == None and 'bytes' in full: bytes = full['bytes']
73 if rssi == None and 'rssi' in full: rssi = full['rssi']
74 if datetime == None and 'datetime' in full: datetime = full['datetime']
75 if location == None and 'location' in full: location = full['location']
76
77
78 loc_id = self.add_location(location) if location is not None else None
79
80
81 if scapy == None:
82
83 import logging
84 logging.getLogger("scapy.runtime").setLevel(logging.ERROR)
85 from scapy.all import Dot15d4
86 scapy = Dot15d4(bytes)
87
88
89
90
91
92
93 try: srcaddr = scapy.src_addr
94 except: srcaddr = None
95 try: srcpan = scapy.src_panid
96 except: srcpan = None
97 srcdevid = self.add_device(srcaddr, srcpan)
98 try: destaddr = scapy.dest_addr
99 except: destaddr = None
100 try: destpanid = scapy.dest_panid
101 except: destpanid = None
102 destdevid = self.add_device(destaddr, destpanid)
103
104 sql = []
105 sql.append("ds_id=%d" % self.ds_id)
106 sql.append("db_datetime=NOW()")
107 if datetime != None: sql.append("cap_datetime='%s'" % str(datetime))
108 if self.channel != None: sql.append("channel=%d" % self.channel)
109 if srcdevid != None: sql.append("source=%d" % srcdevid)
110 if destdevid != None: sql.append("dest=%d" % destdevid)
111 if rssi != None: sql.append("rssi=%d" % rssi)
112 if loc_id != None: sql.append("loc_id=%d" % loc_id)
113 if channel != None: sql.append("channel=%d" % channel)
114 sql.append("fcf_panidcompress=%d" % scapy.fcf_panidcompress)
115 sql.append("fcf_ackreq=%d" % scapy.fcf_ackreq)
116 sql.append("fcf_pending=%d" % scapy.fcf_pending)
117 sql.append("fcf_security=%d" % scapy.fcf_security)
118 sql.append("fcf_frametype=%d" % scapy.fcf_frametype)
119 sql.append("fcf_srcaddrmode=%d" % scapy.fcf_srcaddrmode)
120 sql.append("fcf_framever=%d" % scapy.fcf_framever)
121 sql.append("fcf_destaddrmode=%d" % scapy.fcf_destaddrmode)
122 sql.append("seqnum=%d" % scapy.seqnum)
123
124 return self.insert(' '.join(['INSERT INTO packets SET', ', '.join(sql)]), packetbytes=bytes)
125
126 - def add_location(self, location):
127 if (self.conn==None): raise Exception("DBLogger requires active connection status.")
128 (lon, lat, alt) = location
129 self.conn.execute("SELECT loc_id FROM locations WHERE %s AND %s AND %s LIMIT 1" % \
130 ( ("longitude = '%f'" % lon) if lon != None else "longitude IS NULL" , \
131 ("latitude = '%f'" % lat) if lat != None else "latitude IS NULL" , \
132 ("elevation = '%f'" % alt) if alt != None else "elevation IS NULL" ))
133 res = self.conn.fetchone()
134 if (res != None):
135 return res
136 else:
137 self.conn.execute("INSERT INTO locations SET %s, %s, %s" % \
138 ( ("longitude = '%f'" % lon) if lon != None else "longitude = NULL" , \
139 ("latitude = '%f'" % lat) if lat != None else "latitude = NULL" , \
140 ("elevation = '%f'" % alt) if alt != None else "elevation = NULL" ))
141 if self.conn.rowcount != 1: raise Exception("Location insert did not succeed.")
142 self.db.commit()
143 return self.conn.lastrowid
144
145 - def add_device(self, shortaddr, panid):
146 if (self.conn==None): raise Exception("DBLogger requires active connection status.")
147
148 self.conn.execute("SELECT dev_id FROM devices WHERE %s AND %s LIMIT 1" % \
149 ( ("short_addr = '%04x'" % shortaddr) if shortaddr != None else "short_addr IS NULL" , \
150 ("pan_id = '%04x'" % panid) if panid != None else "pan_id IS NULL" ))
151 res = self.conn.fetchone()
152 if (res != None):
153 return res
154 else:
155
156 self.conn.execute("INSERT INTO devices SET %s, %s" % \
157 (("short_addr = '%04x'" % shortaddr) if shortaddr != None else "short_addr = NULL" , \
158 ("pan_id = '%04x'" % panid) if panid != None else "pan_id = NULL" ))
159 if self.conn.rowcount != 1: raise Exception("Device insert did not succeed.")
160 self.db.commit()
161 return self.conn.lastrowid
162
163 - def insert(self, sql, packetbytes=None):
164 params = None
165 if packetbytes != None:
166 sql = sql + ", packetbytes=%s"
167 params = (MySQLdb.Binary(packetbytes), )
168
169 self.conn.execute(sql, params)
170 if self.conn.rowcount != 1: raise Exception("DBLogger: Insert did not succeed.")
171 self.db.commit()
172 return self.conn.lastrowid
173