537 lines
16 KiB
Python
Executable File
537 lines
16 KiB
Python
Executable File
#!/usr/bin/python3
|
|
|
|
from ffmap.routertools import *
|
|
from ffmap.gwtools import *
|
|
from ffmap.maptools import *
|
|
from ffmap.mysqltools import FreifunkMySQL
|
|
from ffmap.stattools import record_global_stats, record_hood_stats
|
|
from ffmap.config import CONFIG
|
|
from ffmap.misc import *
|
|
|
|
from flask import Blueprint, request, make_response, redirect, url_for, jsonify, Response
|
|
from bson.json_util import dumps as bson2json
|
|
import json
|
|
|
|
from operator import itemgetter
|
|
|
|
import datetime
|
|
import time
|
|
import traceback
|
|
|
|
api = Blueprint("api", __name__)
|
|
|
|
# Load router netif statistics
|
|
@api.route('/load_netif_stats/<dbid>')
|
|
def load_netif_stats(dbid):
|
|
netif = request.args.get("netif","")
|
|
mysql = FreifunkMySQL()
|
|
netiffetch = mysql.fetchall("""
|
|
SELECT netifs.name AS netif, rx, tx, time
|
|
FROM router_stats_netif
|
|
INNER JOIN netifs ON router_stats_netif.netif = netifs.id
|
|
WHERE router = %s AND netifs.name = %s
|
|
""",(dbid,netif,))
|
|
mysql.close()
|
|
|
|
for ns in netiffetch:
|
|
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
|
|
|
|
r = make_response(json.dumps(netiffetch))
|
|
r.mimetype = 'application/json'
|
|
return r
|
|
|
|
# Load router neighbor statistics
|
|
@api.route('/load_neighbor_stats/<dbid>')
|
|
def load_neighbor_stats(dbid):
|
|
mysql = FreifunkMySQL()
|
|
neighfetch = mysql.fetchall("""
|
|
SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s
|
|
""",(dbid,))
|
|
mysql.close()
|
|
|
|
neighdata = {}
|
|
|
|
for ns in neighfetch:
|
|
ns["time"] = {"$date": int(mysql.utcawareint(ns["time"]).timestamp()*1000)}
|
|
if not ns["mac"] in neighdata:
|
|
neighdata[ns["mac"]] = []
|
|
neighdata[ns["mac"]].append(ns)
|
|
|
|
r = make_response(json.dumps(neighdata))
|
|
r.mimetype = 'application/json'
|
|
return r
|
|
|
|
# map ajax
|
|
@api.route('/get_nearest_router')
|
|
def get_nearest_router():
|
|
lng = float(request.args.get("lng"))
|
|
lat = float(request.args.get("lat"))
|
|
|
|
wherelist = []
|
|
if request.args.get("v1") == "on":
|
|
wherelist.append("(v2 = FALSE AND local = FALSE)")
|
|
if request.args.get("v2") == "on":
|
|
wherelist.append("(v2 = TRUE AND local = FALSE)")
|
|
if request.args.get("local") == "on":
|
|
wherelist.append("local = TRUE")
|
|
if wherelist:
|
|
where = " AND ( " + ' OR '.join(wherelist) + " ) "
|
|
else:
|
|
r = make_response(bson2json(None))
|
|
r.mimetype = 'application/json'
|
|
return r
|
|
|
|
mysql = FreifunkMySQL()
|
|
router = mysql.findone("""
|
|
SELECT r.id, r.hostname, r.lat, r.lng, r.description, r.routing_protocol,
|
|
( acos( cos( radians(%s) )
|
|
* cos( radians( r.lat ) )
|
|
* cos( radians( r.lng ) - radians(%s) )
|
|
+ sin( radians(%s) ) * sin( radians( r.lat ) )
|
|
)
|
|
) AS distance
|
|
FROM
|
|
router AS r
|
|
WHERE r.lat IS NOT NULL AND r.lng IS NOT NULL """ + where + """
|
|
ORDER BY
|
|
distance ASC
|
|
LIMIT 1
|
|
""",(lat,lng,lat,))
|
|
if not router:
|
|
r = make_response(bson2json(None))
|
|
r.mimetype = 'application/json'
|
|
return r
|
|
|
|
router["neighbours"] = mysql.fetchall("""
|
|
SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id
|
|
FROM router_neighbor AS nb
|
|
INNER JOIN (
|
|
SELECT router, mac FROM router_netif GROUP BY mac, router
|
|
) AS net ON nb.mac = net.mac
|
|
INNER JOIN router as r ON net.router = r.id
|
|
WHERE nb.router = %s""",(router["id"],))
|
|
mysql.close()
|
|
for n in router["neighbours"]:
|
|
n["color"] = neighbor_color(n["quality"],n["netif"],router["routing_protocol"])
|
|
|
|
r = make_response(bson2json(router))
|
|
r.mimetype = 'application/json'
|
|
return r
|
|
|
|
# router by mac (link from router webui)
|
|
@api.route('/get_router_by_mac/<mac>')
|
|
def get_router_by_mac(mac):
|
|
mysql = FreifunkMySQL()
|
|
res_routers = mysql.fetchall("""
|
|
SELECT id
|
|
FROM router
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
WHERE mac = %s
|
|
GROUP BY mac, id
|
|
""",(mac2int(mac),))
|
|
mysql.close()
|
|
if len(res_routers) != 1:
|
|
return redirect(url_for("router_list", q="mac:%s" % mac))
|
|
else:
|
|
return redirect(url_for("router_info", dbid=res_routers[0]["id"]))
|
|
|
|
# Read alfred data WITH surrounding {"64":"<data>"}
|
|
@api.route('/alfred', methods=['GET', 'POST'])
|
|
def alfred():
|
|
try:
|
|
start_time = time.time()
|
|
mysql = FreifunkMySQL()
|
|
r = make_response(json.dumps({}))
|
|
r.mimetype = 'application/json'
|
|
#import cProfile, pstats, io
|
|
#pr = cProfile.Profile()
|
|
#pr.enable()
|
|
banned = mysql.fetchall("""
|
|
SELECT mac FROM banned
|
|
""",(),"mac")
|
|
hoodsv2 = mysql.fetchall("""
|
|
SELECT name FROM hoodsv2
|
|
""",(),"name")
|
|
statstime = utcnow()
|
|
netifdict = mysql.fetchdict("SELECT id, name FROM netifs",(),"name","id")
|
|
hoodsdict = mysql.fetchdict("SELECT id, name FROM hoods",(),"name","id")
|
|
if request.method == 'POST':
|
|
try:
|
|
alfred_data = request.get_json()
|
|
except Exception as e:
|
|
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],'JSON parsing failed'))
|
|
writefulllog("Warning: Error converting ALFRED data to JSON:\n__%s" % (request.get_data(True,True).replace("\n", "\n__")))
|
|
r.headers['X-API-STATUS'] = "JSON parsing failed"
|
|
return r
|
|
|
|
if alfred_data:
|
|
# load router status xml data
|
|
i = 1
|
|
for mac, xml in alfred_data.get("64", {}).items():
|
|
import_nodewatcher_xml(mysql, mac, xml, banned, hoodsv2, netifdict, hoodsdict, statstime)
|
|
if (i%500 == 0):
|
|
mysql.commit()
|
|
i += 1
|
|
mysql.commit()
|
|
r.headers['X-API-STATUS'] = "ALFRED data imported"
|
|
mysql.close()
|
|
#pr.disable()
|
|
#s = io.StringIO()
|
|
#sortby = 'cumulative'
|
|
#ps = pstats.Stats(pr, stream=s).sort_stats(sortby)
|
|
#ps.print_stats()
|
|
#print(s.getvalue())
|
|
|
|
writelog(CONFIG["debug_dir"] + "/apitime.txt", "%s - %.3f seconds" % (request.environ['REMOTE_ADDR'],time.time() - start_time))
|
|
return r
|
|
except Exception as e:
|
|
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],str(e)))
|
|
writefulllog("Warning: Error while processing ALFRED data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
|
|
r.headers['X-API-STATUS'] = "ERROR processing ALFRED data"
|
|
return r
|
|
|
|
# Read alfred data without surrounding {"64":"<data>"}, so just <data> can be sent
|
|
@api.route('/alfred2', methods=['GET', 'POST'])
|
|
def alfred2():
|
|
try:
|
|
start_time = time.time()
|
|
mysql = FreifunkMySQL()
|
|
banned = mysql.fetchall("""
|
|
SELECT mac FROM banned
|
|
""",(),"mac")
|
|
hoodsv2 = mysql.fetchall("""
|
|
SELECT name FROM hoodsv2
|
|
""",(),"name")
|
|
statstime = utcnow()
|
|
netifdict = mysql.fetchdict("SELECT id, name FROM netifs",(),"name","id")
|
|
hoodsdict = mysql.fetchdict("SELECT id, name FROM hoods",(),"name","id")
|
|
|
|
r = make_response(json.dumps({}))
|
|
r.mimetype = 'application/json'
|
|
if request.method == 'POST':
|
|
try:
|
|
alfred_data = request.get_json()
|
|
except Exception as e:
|
|
writelog(CONFIG["debug_dir"] + "/fail_alfred2.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],'JSON parsing failed'))
|
|
writefulllog("Warning: Error converting ALFRED2 data to JSON:\n__%s" % (request.get_data(True,True).replace("\n", "\n__")))
|
|
r.headers['X-API-STATUS'] = "JSON parsing failed"
|
|
return r
|
|
|
|
if alfred_data:
|
|
# load router status xml data
|
|
i = 1
|
|
for mac, xml in alfred_data.items():
|
|
import_nodewatcher_xml(mysql, mac, xml, banned, hoodsv2, netifdict, hoodsdict, statstime)
|
|
if (i%500 == 0):
|
|
mysql.commit()
|
|
i += 1
|
|
mysql.commit()
|
|
r.headers['X-API-STATUS'] = "ALFRED2 data imported"
|
|
mysql.close()
|
|
|
|
writelog(CONFIG["debug_dir"] + "/apitime.txt", "%s - %.3f seconds (alfred2)" % (request.environ['REMOTE_ADDR'],time.time() - start_time))
|
|
return r
|
|
except Exception as e:
|
|
writelog(CONFIG["debug_dir"] + "/fail_alfred.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],str(e)))
|
|
writefulllog("Warning: Error while processing ALFRED2 data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
|
|
r.headers['X-API-STATUS'] = "ERROR processing ALFRED2 data"
|
|
return r
|
|
|
|
@api.route('/gwinfo', methods=['GET', 'POST'])
|
|
def gwinfo():
|
|
try:
|
|
start_time = time.time()
|
|
mysql = FreifunkMySQL()
|
|
#set_data = {65: "hallo", 66: "welt"}
|
|
set_data = {}
|
|
r = make_response(json.dumps(set_data))
|
|
if request.method == 'POST':
|
|
try:
|
|
gw_data = request.get_json()
|
|
except Exception as e:
|
|
writelog(CONFIG["debug_dir"] + "/fail_gwinfo.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],'JSON parsing failed'))
|
|
writefulllog("Warning: Error converting GWINFO data to JSON:\n__%s" % (request.get_data(True,True).replace("\n", "\n__")))
|
|
return
|
|
|
|
if gw_data:
|
|
import_gw_data(mysql,gw_data)
|
|
mysql.commit()
|
|
r.headers['X-API-STATUS'] = "GW data imported"
|
|
mysql.close()
|
|
|
|
writelog(CONFIG["debug_dir"] + "/gwtime.txt", "%s - %.3f seconds" % (request.environ['REMOTE_ADDR'],time.time() - start_time))
|
|
|
|
r.mimetype = 'application/json'
|
|
return r
|
|
except Exception as e:
|
|
writelog(CONFIG["debug_dir"] + "/fail_gwinfo.txt", "{} - {}".format(request.environ['REMOTE_ADDR'],str(e)))
|
|
writefulllog("Warning: Error while processing GWINFO data: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
|
|
|
|
|
|
# https://github.com/ffansbach/de-map/blob/master/schema/nodelist-schema-1.0.0.json
|
|
@api.route('/nodelist')
|
|
def nodelist():
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT id, hostname, status, clients, last_contact, lat, lng
|
|
FROM router
|
|
""",())
|
|
router_data = mysql.utcawaretuple(router_data,"last_contact")
|
|
mysql.close()
|
|
nodelist_data = {'version': '1.0.0'}
|
|
nodelist_data['nodes'] = list()
|
|
for router in router_data:
|
|
nodelist_data['nodes'].append(
|
|
{
|
|
'id': str(router['id']),
|
|
'name': router['hostname'],
|
|
'node_type': 'AccessPoint',
|
|
'href': 'https://monitoring.freifunk-franken.de/routers/' + str(router['id']),
|
|
'status': {
|
|
'online': router['status'] == 'online',
|
|
'clients': router['clients'],
|
|
'lastcontact': router['last_contact'].isoformat()
|
|
}
|
|
}
|
|
)
|
|
if router['lat'] and router['lng']:
|
|
nodelist_data['nodes'][-1]['position'] = {
|
|
'lat': router['lat'],
|
|
'long': router['lng']
|
|
}
|
|
return jsonify(nodelist_data)
|
|
|
|
@api.route('/wifianal/<selecthood>')
|
|
def wifianal(selecthood):
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT hostname, mac, netif
|
|
FROM router
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
INNER JOIN hoods ON router.hood = hoods.id
|
|
WHERE hoods.name = %s
|
|
GROUP BY router.id, netif
|
|
""",(selecthood,))
|
|
mysql.close()
|
|
|
|
return wifianalhelper(router_data,"Hood: " + selecthood)
|
|
|
|
@api.route('/wifianalall')
|
|
def wifianalall():
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT hostname, mac, netif
|
|
FROM router
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
GROUP BY id, netif
|
|
""",())
|
|
mysql.close()
|
|
|
|
return wifianalhelper(router_data,"ALL hoods")
|
|
|
|
def wifianalhelper(router_data, headline):
|
|
s = "#----------WifiAnalyzer alias file----------\n"
|
|
s += "# \n"
|
|
s += "#Freifunk Franken\n"
|
|
s += "#" + headline + "\n"
|
|
s += "# \n"
|
|
s += "#Encoding: UTF-8.\n"
|
|
s += "#The line starts with # is comment.\n"
|
|
s += "# \n"
|
|
s += "#Content line format:\n"
|
|
s += "#bssid1|alias of bssid1\n"
|
|
s += "#bssid2|alias of bssid2\n"
|
|
s += "# \n"
|
|
|
|
for router in router_data:
|
|
if not router['mac']:
|
|
continue
|
|
if router["netif"] == 'br-mesh':
|
|
s += int2mac(router["mac"]) + "|Mesh_" + router['hostname'] + "\n"
|
|
elif router["netif"] == 'w2ap':
|
|
s += int2mac(router["mac"]) + "|" + router['hostname'] + "\n"
|
|
elif router["netif"] == 'w5ap':
|
|
s += int2mac(router["mac"]) + "|W5_" + router['hostname'] + "\n"
|
|
elif router["netif"] == 'w5mesh':
|
|
s += int2mac(router["mac"]) + "|W5Mesh_" + router['hostname'] + "\n"
|
|
|
|
return Response(s,mimetype='text/plain')
|
|
|
|
@api.route('/dnslist')
|
|
def dnslist():
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT hostname, mac, MIN(ipv6) AS fd43
|
|
FROM router
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
INNER JOIN router_ipv6 ON router.id = router_ipv6.router AND router_netif.netif = router_ipv6.netif
|
|
WHERE LEFT(HEX(ipv6),4) = 'fd43'
|
|
GROUP BY hostname, mac
|
|
""",())
|
|
mysql.close()
|
|
|
|
s = ""
|
|
for router in router_data:
|
|
s += int2shortmac(router["mac"]) + "\t" + bintoipv6(router["fd43"]) + "\n"
|
|
|
|
return Response(s,mimetype='text/plain')
|
|
|
|
@api.route('/dnsentries')
|
|
def dnsentries():
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT hostname, mac, MIN(ipv6) AS fd43
|
|
FROM router
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
INNER JOIN router_ipv6 ON router.id = router_ipv6.router AND router_netif.netif = router_ipv6.netif
|
|
WHERE LEFT(HEX(ipv6),4) = 'fd43'
|
|
GROUP BY hostname, mac
|
|
""",())
|
|
mysql.close()
|
|
|
|
s = ""
|
|
for router in router_data:
|
|
s += int2shortmac(router["mac"]) + ".fff.community. 300 IN AAAA " + bintoipv6(router["fd43"]) + " ; " + router["hostname"] + "\n"
|
|
|
|
return Response(s,mimetype='text/plain')
|
|
|
|
def nodelist_helper(where = "",data=()):
|
|
# Suppresses routers without br-mesh
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT router.id, hostname, status, hoods.id AS hoodid, hoods.name AS hood, contact, nickname, hardware, firmware, clients, lat, lng, last_contact, mac, sys_loadavg, fe80_addr
|
|
FROM router
|
|
INNER JOIN hoods ON router.hood = hoods.id
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
LEFT JOIN users ON router.contact = users.email
|
|
WHERE netif = 'br-mesh' {}
|
|
ORDER BY hostname ASC
|
|
""".format(where),data)
|
|
router_data = mysql.utcawaretuple(router_data,"last_contact")
|
|
router_net = mysql.fetchall("""
|
|
SELECT id, netif, COUNT(router) AS count
|
|
FROM router
|
|
INNER JOIN router_netif ON router.id = router_netif.router
|
|
GROUP BY id, netif
|
|
""")
|
|
mysql.close()
|
|
|
|
net_dict = {}
|
|
for rs in router_net:
|
|
if not rs["id"] in net_dict:
|
|
net_dict[rs["id"]] = []
|
|
net_dict[rs["id"]].append(rs["netif"])
|
|
nodelist_data = {'version': '1.1.0'}
|
|
nodelist_data['nodes'] = list()
|
|
|
|
for router in router_data:
|
|
fastd = 0
|
|
l2tp = 0
|
|
|
|
if router["id"] in net_dict:
|
|
for netif in net_dict[router["id"]]:
|
|
if netif == 'fffVPN':
|
|
fastd += 1
|
|
elif netif.startswith('l2tp'):
|
|
l2tp += 1
|
|
#elif netif['netif'] == 'br-mesh' and 'mac' in netif:
|
|
# mac = netif["mac"]
|
|
|
|
if not router['mac']:
|
|
continue
|
|
|
|
nodelist_data['nodes'].append(
|
|
{
|
|
'id': str(router['id']),
|
|
'name': router['hostname'],
|
|
'mac': int2mac(router['mac']),
|
|
'hoodid': router['hoodid'],
|
|
'hood': router['hood'],
|
|
'status': router['status'],
|
|
'user': router['nickname'],
|
|
'hardware': router['hardware'],
|
|
'firmware': router['firmware'],
|
|
'loadavg': router['sys_loadavg'],
|
|
'href': 'https://monitoring.freifunk-franken.de/mac/' + int2shortmac(router['mac']),
|
|
'clients': router['clients'],
|
|
'lastcontact': router['last_contact'].isoformat(),
|
|
'fe80_addr': bintoipv6(router['fe80_addr']),
|
|
'uplink': {
|
|
'fastd': fastd,
|
|
'l2tp': l2tp
|
|
}
|
|
}
|
|
)
|
|
nodelist_data['nodes'][-1]['position'] = {
|
|
'lat': router['lat'],
|
|
'lng': router['lng']
|
|
}
|
|
return nodelist_data
|
|
|
|
@api.route('/nopos')
|
|
def no_position():
|
|
mysql = FreifunkMySQL()
|
|
router_data = mysql.fetchall("""
|
|
SELECT router.id, hostname, contact, nickname, firmware
|
|
FROM router
|
|
LEFT JOIN users ON router.contact = users.email
|
|
WHERE lat IS NULL OR lng IS NULL
|
|
""")
|
|
mysql.close()
|
|
#nodelist_data = dict()
|
|
nodelist_data = list()
|
|
for router in router_data:
|
|
nick = router['nickname']
|
|
if not nick:
|
|
nick = ""
|
|
nodelist_data.append(
|
|
{
|
|
'name': router['hostname'],
|
|
'href': 'https://monitoring.freifunk-franken.de/routers/' + str(router['id']),
|
|
'firmware': router['firmware'],
|
|
'contact': router['contact'],
|
|
'owner': nick
|
|
}
|
|
)
|
|
|
|
nodelist_data2 = sorted(nodelist_data, key=itemgetter('owner'), reverse=False)
|
|
nodes = dict()
|
|
nodes['nodes'] = list(nodelist_data2)
|
|
|
|
return jsonify(nodes)
|
|
|
|
@api.route('/routers')
|
|
def routers():
|
|
# Suppresses routers without br-mesh
|
|
return jsonify(nodelist_helper())
|
|
|
|
@api.route('/routers_by_nickname/<nickname>')
|
|
def get_routers_by_nickname(nickname):
|
|
mysql = FreifunkMySQL()
|
|
users = mysql.fetchall("""
|
|
SELECT id
|
|
FROM users
|
|
WHERE nickname = %s
|
|
LIMIT 1
|
|
""",(nickname,))
|
|
mysql.close()
|
|
if len(users)==0:
|
|
return "User not found"
|
|
|
|
return jsonify(nodelist_helper("AND nickname = %s",(nickname,)))
|
|
|
|
@api.route('/routers_by_keyxchange_id/<keyxchange_id>')
|
|
def get_routers_by_keyxchange_id(keyxchange_id):
|
|
mysql = FreifunkMySQL()
|
|
hood = mysql.findone("""
|
|
SELECT name
|
|
FROM hoodsv2
|
|
WHERE id = %s
|
|
LIMIT 1
|
|
""",(int(keyxchange_id),),"name")
|
|
mysql.close()
|
|
if not hood:
|
|
return "Hood not found"
|
|
|
|
return jsonify(nodelist_helper('AND hoods.name = %s',(hood,)))
|