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
""")
