def register_user(nickname, email, password): if not nickname or not email: raise AccountWithEmptyField() mysql = FreifunkMySQL() user_with_nick = mysql.findone("SELECT id, email FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user_with_email = mysql.findone("SELECT id FROM users WHERE email = %s LIMIT 1",(email,),"id") pw = generate_password_hash(password) if user_with_email: mysql.close() raise AccountWithEmailExists() elif user_with_nick and user_with_nick["email"]: mysql.close() raise AccountWithNicknameExists() else: time = mysql.utcnow() if user_with_nick: mysql.execute(""" UPDATE users SET password = %s, email = %s, created = %s, token = NULL WHERE id = %s LIMIT 1 """,(pw,email,time,user_with_nick["id"],)) mysql.commit() mysql.close() return user_with_nick["id"] else: mysql.execute(""" INSERT INTO users (nickname, password, email, created, token) VALUES (%s, %s, %s, %s, NULL) """,(nickname,pw,email,time,)) userid = mysql.cursor().lastrowid mysql.commit() mysql.close() return userid
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `stats_global` ( `time` datetime NOT NULL, `clients` int(11) NOT NULL, `online` int(11) NOT NULL, `offline` int(11) NOT NULL, `unknown` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `stats_global` ADD PRIMARY KEY (`time`) """) mysql.execute(""" CREATE TABLE stats_hood ( `hood` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `time` datetime NOT NULL, `clients` int(11) NOT NULL, `online` int(11) NOT NULL,
def user_info(nickname): mysql = FreifunkMySQL() user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) if not user: mysql.close() return "User not found" if request.method == 'POST': if request.form.get("action") == "changepw": if is_authorized(user["nickname"], session): if request.form["password"] != request.form["password_rep"]: flash("<b>Passwords did not match!</b>", "danger") elif request.form["password"] == "": flash("<b>Password must not be empty!</b>", "danger") else: set_user_password(mysql, user["nickname"], request.form["password"]) flash("<b>Password changed!</b>", "success") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "changemail": if is_authorized(user["nickname"], session): if request.form["email"] != request.form["email_rep"]: flash("<b>E-Mail addresses do not match!</b>", "danger") elif not "@" in request.form["email"]: flash("<b>Invalid E-Mail addresse!</b>", "danger") else: try: set_user_email(mysql, user["nickname"], request.form["email"]) flash("<b>E-Mail changed!</b>", "success") if not session.get('admin'): password = base64.b32encode(os.urandom(10)).decode() set_user_password(mysql, user["nickname"], password) send_email( recipient = request.form['email'], subject = "Password for %s" % user['nickname'], content = "Hello %s,\n\n" % user["nickname"] + "You changed your email address on https://monitoring.freifunk-franken.de/\n" + "To verify your new email address your password was changed to %s\n" % password + "... and sent to your new address. Please log in and change it.\n\n" + "Regards,\nFreifunk Franken Monitoring System" ) mysql.close() return logout() else: # force db data reload user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) except AccountWithEmailExists: flash("<b>There is already an account with this E-Mail Address!</b>", "danger") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "changeadmin": if session.get('admin'): set_user_admin(mysql, nickname, request.form.get("admin") == "true") # force db data reload user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "changeabuse": if session.get('admin'): set_user_abuse(mysql, nickname, request.form.get("abuse") == "true") # force db data reload user = mysql.findone("SELECT * FROM users WHERE nickname = %s LIMIT 1",(nickname,)) user["created"] = mysql.utcaware(user["created"]) else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("action") == "deleteaccount": if is_authorized(user["nickname"], session): mysql.execute("DELETE FROM users WHERE nickname = %s LIMIT 1",(nickname,)) mysql.commit() flash("<b>User <i>%s</i> deleted!</b>" % nickname, "success") mysql.close() if user["nickname"] == session.get("user"): session.pop('user', None) return redirect(url_for("user_list")) else: flash("<b>You are not authorized to perform this action!</b>", "danger") routers = mysql.fetchall(""" SELECT router.id, hostname, status, hoods.id AS hoodid, hoods.name AS hood, firmware, hardware, created, sys_uptime, clients, router.lat, router.lng, reset, blocked, v2, local FROM router INNER JOIN hoods ON router.hood = hoods.id LEFT JOIN ( SELECT router, blocked.mac AS blocked FROM router_netif INNER JOIN blocked ON router_netif.mac = blocked.mac WHERE netif = 'br-mesh' ) AS b ON router.id = b.router WHERE contact = %s ORDER BY hostname ASC """,(user["email"],)) mysql.close() routers = mysql.utcawaretuple(routers,"created") return render_template("user.html", user=user, routers=routers, routers_count=len(routers), authuser = is_authorized(user["nickname"], session), authadmin = session.get('admin') )
def router_info(dbid): try: mysql = FreifunkMySQL() router = mysql.findone(""" SELECT router.*, hoods.id AS hoodid, hoods.name AS hoodname FROM router INNER JOIN hoods ON router.hood = hoods.id WHERE router.id = %s LIMIT 1 """,(dbid,)) mac = None if router: if request.args.get('fffconfig', None) != None: mysql.close() s = "\nconfig fff 'system'\n" s += " option hostname '{}'\n".format(router["hostname"]) s += " option description '{}'\n".format(router["description"]) s += " option latitude '{}'\n".format(router["lat"] if router["lat"] else "") s += " option longitude '{}'\n".format(router["lng"] if router["lng"] else "") s += " option position_comment '{}'\n".format(router["position_comment"]) s += " option contact '{}'\n".format(router["contact"]) return Response(s,mimetype='text/plain') router = mysql.utcaware(router,["created","last_contact"]) router["user"] = mysql.findone("SELECT nickname FROM users WHERE email = %s",(router["contact"],),"nickname") router["netifs"] = mysql.fetchall("""SELECT * FROM router_netif WHERE router = %s""",(dbid,)) netifs = [] for n in router["netifs"]: n["ipv6_addrs"] = mysql.fetchall("""SELECT ipv6 FROM router_ipv6 WHERE router = %s AND netif = %s""",(dbid,n["netif"],),"ipv6") if n["netif"]=="br-mesh": mac = n["mac"] netifs.append(n["netif"]) router["neighbours"] = mysql.fetchall(""" SELECT nb.mac, nb.netif, nb.quality, r.hostname, r.id FROM router_neighbor AS nb LEFT JOIN ( SELECT router, mac FROM router_netif GROUP BY mac, router ) AS net ON nb.mac = net.mac LEFT JOIN router as r ON net.router = r.id WHERE nb.router = %s ORDER BY nb.quality DESC """,(dbid,)) # FIX SQL: only one from router_netif router["gws"] = mysql.fetchall(""" SELECT router_gw.mac AS mac, quality, router_gw.netif AS netif, gw_class, selected, gw.name AS gw, n1.netif AS gwif, n2.netif AS batif, n2.mac AS batmac FROM router_gw LEFT JOIN ( gw_netif AS n1 INNER JOIN gw ON n1.gw = gw.id LEFT JOIN gw_netif AS n2 ON n1.mac = n2.vpnmac AND n1.gw = n2.gw ) ON router_gw.mac = n1.mac WHERE router = %s """,(dbid,)) for gw in router["gws"]: gw["label"] = gw_name(gw) gw["batX"] = gw_bat(gw) router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,)) router["events"] = mysql.utcawaretuple(router["events"],"time") ## Create json with all data except stats if request.args.get('json', None) != None: mysql.close() return Response(bson2json(router, sort_keys=True, indent=4), mimetype='application/json') cwan = "blue" cclient = "orange" cbatman = "#29c329" cvpn = "red" chidden = "gray" ## Label netifs AFTER json if clause for n in router["netifs"]: netif = n["netif"]; desc = None color = None if netif == 'br-mesh': desc = "Bridge" elif netif.endswith('.1'): desc = "Clients via Ethernet" color = cclient elif netif.endswith('.2'): desc = "WAN" color = cwan elif netif.endswith('.3'): desc = "Mesh via Ethernet" color = cbatman elif netif == "w2ap": desc = "Clients @ 2.4 GHz" color = cclient elif netif == "w2mesh" or netif == "w2ibss": desc = "Mesh @ 2.4 GHz" color = cbatman elif netif == "w2configap": desc = "Config @ 2.4 GHz" color = chidden elif netif == "w5ap": desc = "Clients @ 5 GHz" color = cclient elif netif == "w5mesh" or netif == "w5ibss": desc = "Mesh @ 5 GHz" color = cbatman elif netif == "w5configap": desc = "Config @ 5 GHz" color = chidden elif netif == "fffVPN": desc = "Fastd VPN Tunnel" color = cvpn elif netif.startswith("l2tp"): desc = "L2TP VPN Tunnel" color = cvpn elif netif.startswith("bat"): desc = "Batman Interface" elif netif.startswith("eth") and any(item.startswith("{}.".format(netif)) for item in netifs): desc = "Switch" elif netif == "eth1": # already known from above: no switch; no one-port, as there must be eth0 if not "eth0" in netifs or any(item.startswith("eth0.") for item in netifs): desc = "WAN" color = cwan else: # Second port of Nanostation M2 desc = "Ethernet Multi-Port" elif netif == "eth0": if any(item.startswith("eth1.") for item in netifs): # already known from above: no switch desc = "WAN" color = cwan else: # First port of Nanostation M2 or ONE-Port desc = "Ethernet Multi-Port" n["description"] = desc n["color"] = color ## Set color for neighbors AFTER json if clause for n in router["neighbours"]: n["color"] = neighbor_color(n["quality"],n["netif"],router["routing_protocol"]) router["stats"] = mysql.fetchall("""SELECT * FROM router_stats WHERE router = %s""",(dbid,)) for s in router["stats"]: s["time"] = mysql.utcawareint(s["time"]) threshold_neighstats = (utcnow() - datetime.timedelta(hours=24)).timestamp() neighfetch = mysql.fetchall(""" SELECT quality, mac, time FROM router_stats_neighbor WHERE router = %s AND time > %s """,(dbid,threshold_neighstats,)) 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) neighident = mysql.fetchall(""" SELECT snb.mac, r.hostname, n.netif FROM router_stats_neighbor AS snb INNER JOIN router_netif AS n ON snb.mac = n.mac INNER JOIN router AS r ON n.router = r.id WHERE snb.router = %s AND n.netif <> 'w2ap' AND n.netif <> 'w5ap' GROUP BY snb.mac, r.hostname, n.netif """,(dbid,)) neighlabel = {} for ni in neighident: label = ni["hostname"] # add network interface when there are multiple links to same node for ni2 in neighident: if label == ni2["hostname"] and ni["mac"] != ni2["mac"]: # This shows the NEIGHBOR'S interface name label += "@" + ni["netif"] append = " (old)" for nnn in router["neighbours"]: if nnn["mac"] == ni["mac"]: append = "" neighlabel[ni["mac"]] = label + append gwfetch = mysql.fetchall(""" SELECT quality, mac, time FROM router_stats_gw WHERE router = %s """,(dbid,)) for ns in gwfetch: ns["time"] = mysql.utcawareint(ns["time"]) if request.method == 'POST': if request.form.get("act") == "delete": # a router may not have a owner, but admin users still can delete it if is_authorized(router["user"], session): delete_router(mysql,dbid) flash("<b>Router <i>%s</i> deleted!</b>" % router["hostname"], "success") mysql.close() return redirect(url_for("index")) else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("act") == "ban": if session.get('admin'): if mac: ban_router(mysql,dbid) delete_router(mysql,dbid) flash("<b>Router <i>%s</i> banned!</b>" % router["hostname"], "success") mysql.close() return redirect(url_for("index")) else: flash("<b>Router has no br-mesh and thus cannot be banned!</b>", "danger") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("act") == "changeblocked" and mac: if session.get('admin'): if request.form.get("blocked") == "true": added = mysql.utcnow() mysql.execute("INSERT INTO blocked (mac, added) VALUES (%s, %s)",(mac,added,)) mysql.execute(""" INSERT INTO router_events (router, time, type, comment) VALUES (%s, %s, %s, %s) """,(dbid,mysql.utcnow(),"admin","Marked as blocked",)) mysql.commit() else: mysql.execute("DELETE FROM blocked WHERE mac = %s",(mac,)) mysql.execute(""" INSERT INTO router_events (router, time, type, comment) VALUES (%s, %s, %s, %s) """,(dbid,mysql.utcnow(),"admin","Removed blocked status",)) mysql.commit() router["events"] = mysql.fetchall("""SELECT * FROM router_events WHERE router = %s""",(dbid,)) router["events"] = mysql.utcawaretuple(router["events"],"time") else: flash("<b>You are not authorized to perform this action!</b>", "danger") elif request.form.get("act") == "report": abusemails = mysql.fetchall("SELECT email FROM users WHERE abuse = 1") for a in abusemails: send_email( recipient = a["email"], subject = "Monitoring: Router %s reported" % router["hostname"], content = "Hello Admin,\n\n" + "The router with hostname %s has been reported as abusive by a user.\n" % router["hostname"] + "Please take care:\n" + "%s\n\n" % url_for("router_info", dbid=dbid, _external=True) + "Regards,\nFreifunk Franken Monitoring System" ) flash("<b>Router reported to administrators!</b>", "success") else: mysql.close() return "Router not found" router["blocked"] = mysql.findone(""" SELECT blocked.mac FROM router_netif AS n LEFT JOIN blocked ON n.mac = blocked.mac WHERE n.router = %s AND n.netif = 'br-mesh' """,(dbid,),"mac") mysql.close() return render_template("router.html", router = router, mac = mac, tileurls = tileurls, neighstats = neighdata, neighlabel = neighlabel, gwstats = gwfetch, authuser = is_authorized(router["user"], session), authadmin = session.get('admin') ) except Exception as e: writelog(CONFIG["debug_dir"] + "/fail_router.txt", str(e)) import traceback writefulllog("Warning: Failed to display router details page: %s\n__%s" % (e, traceback.format_exc().replace("\n", "\n__")))
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `users` ( `id` int(11) NOT NULL, `nickname` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `password` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `token` varchar(250) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `email` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `admin` tinyint(1) NOT NULL DEFAULT '0', `abuse` tinyint(1) NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `users` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `nickname` (`nickname`), ADD UNIQUE KEY `email` (`email`) """) mysql.execute(""" ALTER TABLE `users`
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE gw ( `id` smallint(5) UNSIGNED NOT NULL, `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `stats_page` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `last_contact` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE gw ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`) """) mysql.execute(""" ALTER TABLE gw MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT """) mysql.execute("""
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `stats_global` ( `time` int(11) NOT NULL, `clients` mediumint(9) NOT NULL, `online` smallint(6) NOT NULL, `offline` smallint(6) NOT NULL, `unknown` smallint(6) NOT NULL, `orphaned` smallint(6) NOT NULL, `rx` int(10) UNSIGNED DEFAULT NULL, `tx` int(10) UNSIGNED DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `stats_global` ADD PRIMARY KEY (`time`) """) mysql.execute(""" CREATE TABLE `stats_gw` ( `time` int(11) NOT NULL, `mac` bigint(20) UNSIGNED NOT NULL,
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `gw` ( `id` smallint(5) UNSIGNED NOT NULL, `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL, `stats_page` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL, `version` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL, `last_contact` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `gw` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`) """) mysql.execute(""" ALTER TABLE `gw` MODIFY `id` smallint(5) UNSIGNED NOT NULL AUTO_INCREMENT """)
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `banned` ( `mac` bigint(20) UNSIGNED NOT NULL, `added` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `banned` ADD PRIMARY KEY (`mac`) """) mysql.execute(""" CREATE TABLE `blocked` ( `mac` bigint(20) UNSIGNED NOT NULL, `added` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `blocked`
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `stats_global` ( `time` int(11) NOT NULL, `clients` mediumint(9) NOT NULL, `online` smallint(6) NOT NULL, `offline` smallint(6) NOT NULL, `unknown` smallint(6) NOT NULL, `orphaned` smallint(6) NOT NULL, `rx` int(10) UNSIGNED DEFAULT NULL, `tx` int(10) UNSIGNED DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `stats_global` ADD PRIMARY KEY (`time`) """) mysql.execute(""" CREATE TABLE stats_gw ( `time` int(11) NOT NULL, `mac` char(17) COLLATE utf8_unicode_ci NOT NULL,
}, { "keyxchange_id": 27, "name": "Arnstein", "net": "10.50.188.0/22", "position": {"lng": 9.970957, "lat": 49.978117} }, { "keyxchange_id": 28, "name": "Erlenbach", "net": "10.50.192.0/22", "position": {"lng": 9.157491, "lat": 49.803930} }] for h in hoods: coord = h.get("position",{}) if coord.get("lat"): cos_lat = math.cos(math.radians(coord.get("lat"))) sin_lat = math.sin(math.radians(coord.get("lat"))) else: cos_lat = None sin_lat = None mysql.execute(""" INSERT INTO hoods (id, name, net, lat, lng, cos_lat, sin_lat) VALUES (%s, %s, %s, %s, %s, %s, %s) """,(h["keyxchange_id"],h["name"],h["net"],coord.get("lat"),coord.get("lng"),cos_lat,sin_lat,)) mysql.commit() mysql.close()
import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE hoods ( `id` int(11) NOT NULL, `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, `net` varchar(30) COLLATE utf8_unicode_ci NOT NULL, `lat` double DEFAULT NULL, `lng` double DEFAULT NULL, `cos_lat` double DEFAULT NULL, `sin_lat` double DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE hoods ADD PRIMARY KEY (`id`), ADD KEY `name` (`name`), ADD KEY `lat` (`lat`), ADD KEY `lng` (`lng`), ADD KEY `cos_lat` (`cos_lat`), ADD KEY `sin_lat` (`sin_lat`) """)
#!/usr/bin/python3 import os import sys sys.path.insert(0, os.path.abspath(os.path.dirname(__file__) + '/' + '../..')) from ffmap.mysqltools import FreifunkMySQL mysql = FreifunkMySQL() mysql.execute(""" CREATE TABLE `hoods` ( `id` smallint(6) UNSIGNED NOT NULL, `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """) mysql.execute(""" ALTER TABLE `hoods` ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `name` (`name`) """) mysql.execute(""" ALTER TABLE `hoods` MODIFY `id` smallint(6) UNSIGNED NOT NULL AUTO_INCREMENT """) mysql.execute(""" ALTER TABLE hoods AUTO_INCREMENT = 30001 """)
mysql.execute(""" CREATE TABLE router ( `id` int(11) NOT NULL, `status` varchar(20) COLLATE utf8_unicode_ci NOT NULL, `hostname` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `created` datetime NOT NULL, `last_contact` datetime NOT NULL, `sys_time` datetime NOT NULL, `sys_uptime` int(11) NOT NULL, `sys_memfree` int(11) NOT NULL, `sys_membuff` int(11) NOT NULL, `sys_memcache` int(11) NOT NULL, `sys_loadavg` double NOT NULL, `sys_procrun` smallint(6) NOT NULL, `sys_proctot` smallint(6) NOT NULL, `clients` smallint(6) NOT NULL, `wan_uplink` tinyint(1) NOT NULL, `cpu` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `chipset` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `hardware` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `os` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `batman` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `kernel` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `nodewatcher` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `firmware` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `firmware_rev` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `description` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `position_comment` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `community` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `hood` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `status_text` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `contact` varchar(200) COLLATE utf8_unicode_ci NOT NULL, `lng` double DEFAULT NULL, `lat` double DEFAULT NULL, `reset` tinyint(1) NOT NULL DEFAULT '0', `neighbors` smallint(6) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci """)