def insert(self, tstamp, devices):
for device in devices:
device_key = deviceToKey(device)
self.cursor.execute("INSERT INTO presence VALUES (?,?)",
(tstamp, device_key))
self.conn.commit()
def update(self, mac, tstamp, name=None, ip=None):
device_key = deviceToKey(mac)
# first insert minimal info
q = "INSERT INTO meta (mac, last_seen) SELECT ?, 0 WHERE NOT EXISTS(SELECT 1 FROM meta WHERE mac = ?) "
params = [device_key, device_key]
# print(q, params)
self.cursor.execute(q, params)
# actual update
fields = ["last_seen = ?"]
values = [tstamp]
if ip:
fields.append("last_ip = ?")
values.append(ip2long(ip))
if name:
fields.append("name = ?")
values.append(name)
q = "UPDATE meta SET " + ", ".join(fields) + " WHERE mac = ?"
values.append(device_key)
# print(q, values)
self.cursor.execute(q, values)
self.conn.commit()
def query(self, mac):
q = "SELECT * FROM meta WHERE mac = ?"
params = [
deviceToKey(mac),
]
# print(q, params)
res = self.cursor.execute(q, params).fetchall()
if not res or len(res) != 1:
return None
res = res[0]
return {
"mac": keyToDevice(res[0]),
"last_seen": int(res[1]),
"last_ip": long2ip(res[2]),
"name": res[3],
}
def query(self,
tstamp_start,
tstamp_end,
device_filter=None,
resolution=None):
q = " FROM presence WHERE timestamp >= ? AND timestamp <= ?"
time_what = "timestamp"
params = [tstamp_start, tstamp_end]
interval_edge = RESOLUTION
if device_filter:
q = q + " AND mac = ?"
params.append(deviceToKey(device_filter))
if resolution == "1h":
q = q + " GROUP BY strftime('%H', datetime(timestamp, 'unixepoch')), mac"
interval_edge = 3600
elif resolution == "24h":
time_what = "strftime('%s', strftime('%Y-%m-%d', timestamp, 'unixepoch'), 'utc')"
q = q + " GROUP BY strftime('%m-%d', datetime(timestamp, 'unixepoch')), mac"
interval_edge = 86400
elif resolution == "1M":
time_what = "strftime('%s', strftime('%Y-%m-01 00:00:00', timestamp, 'unixepoch'), 'utc')"
q = q + " GROUP BY strftime('%Y-%m', datetime(timestamp, 'unixepoch')), mac"
interval_edge = 2678400
else:
q = q + " GROUP BY timestamp, mac"
what = time_what + ", mac"
q = "SELECT " + what + q
# print(q, params)
res = self.cursor.execute(q, params)
devices_to_tstamp = self._groupByDevice(res)
# print(devices_to_tstamp)
return self._getIntervals(devices_to_tstamp, interval_edge)