def register(args, conf):
# Check that sufficient arguments are available
if "user" not in args or "pass" not in args:
return ServiceResult(
"application/json", jsonsaves({"state": "failure", "message": "Need a username and password"})
)
db = connect(
host=conf["TRAIN_DB_HOST"],
user=conf["GATE_KEEPER_USERNAME"],
passwd=conf["GATE_KEEPER_PASSWD"],
db=conf["USER_DATABASE"],
port=conf["TRAIN_DB_PORT"],
)
username = args.getfirst("user")
passwd = args.getfirst("pass")
cur = db.cursor()
# Check that the user doesn't already exist
cur.execute("SELECT DISTINCT user FROM mysql.user WHERE user=%s", username)
if cur.fetchone() is not None:
cur.close()
db.close()
return ServiceResult("application/json", jsonsaves({"success": False, "message": "User already exists"}))
cur.execute("CREATE USER %s@%s IDENTIFIED BY %s", (username, conf["TRAIN_DB_WEB_HOST"], passwd))
# duplicate backticks for the table name
table_name = username.replace("`", "``") + "_data"
cur.execute("CREATE TABLE `" + table_name + "` (Latitude DOUBLE, Longitude DOUBLE, Time DATETIME);")
cur.execute("GRANT INSERT, DROP ON `" + table_name + "` TO %s@%s", (username, conf["TRAIN_DB_WEB_HOST"]))
cur.execute("GRANT SELECT ON `" + conf["TRAIN_DATABASE"] + "`.* TO %s@%s", (username, conf["TRAIN_DB_WEB_HOST"]))
cur.close()
db.close()
return ServiceResult("application/json", jsonsaves({"success": True, "message": "Created new user"}))
def validate(args, conf):
username = args.getfirst("user")
passwd = args.getfirst("pass")
try:
db = connect(
host=conf["TRAIN_DB_HOST"],
user=username,
passwd=passwd,
db=conf["USER_DATABASE"],
port=conf["TRAIN_DB_PORT"],
)
db.close()
return ServiceResult("application/json", jsonsaves({"value": True, "message": "This user exists"}))
except DatabaseError:
return ServiceResult("application/json", jsonsaves({"value": False, "message": "This user does not exist"}))
def deregister(args, conf):
# Drop the user's data table from the user's account
username = args.getfirst("user")
passwd = args.getfirst("pass")
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = username, \
passwd = passwd, \
db = conf["USER_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
cur = db.cursor()
table_name = username.replace("`", "``") + "_data"
cur.execute("DROP TABLE `" + table_name + "`;")
cur.close()
db.close()
# Connect with a different account for dropping the user
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = conf["GATE_KEEPER_USERNAME"], \
passwd = conf["GATE_KEEPER_PASSWD"], \
db = conf["USER_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
cur = db.cursor()
db.close()
return ServiceResult("application/json", \
jsonsaves({"success": True, "message": "Dropped user from system"}))
def route(args, conf):
cur = db.cursor()
# Retrieve distinct route names from the database
cur.execute("SELECT DISTINCT RouteName FROM trains;")
ret = ServiceResult("application/json", jsonsaves([arr[0] for arr in cur.fetchall()]))
cur.close()
return ret
def route(args, conf):
cur = db.cursor()
# Retrieve distinct route names from the database
cur.execute("SELECT DISTINCT RouteName FROM trains;")
ret = ServiceResult("application/json", jsonsaves([arr[0] for arr in cur.fetchall()]))
cur.close()
return ret
def validate(args, conf):
username = args.getfirst("user")
passwd = args.getfirst("pass")
try:
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = username, \
passwd = passwd, \
db = conf["USER_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
db.close()
return ServiceResult("application/json", \
jsonsaves({"value": True, "message": "This user exists"}))
except DatabaseError:
return ServiceResult("application/json", \
jsonsaves({"value": False, "message": "This user does not exist"}))
def register(args, conf):
# Check that sufficient arguments are available
if "user" not in args or "pass" not in args:
return ServiceResult("application/json", jsonsaves({"state": "failure", "message": "Need a username and password"}))
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = conf["GATE_KEEPER_USERNAME"], \
passwd = conf["GATE_KEEPER_PASSWD"], \
db = conf["USER_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
username = args.getfirst("user")
passwd = args.getfirst("pass")
cur = db.cursor()
# Check that the user doesn't already exist
cur.execute("SELECT DISTINCT user FROM mysql.user WHERE user=%s", \
username)
if cur.fetchone() is not None:
cur.close()
db.close()
return ServiceResult("application/json", jsonsaves({"success": False, \
"message": "User already exists"}))
cur.execute("CREATE USER %s@%s IDENTIFIED BY %s", (username, \
conf["TRAIN_DB_WEB_HOST"], passwd))
# duplicate backticks for the table name
table_name = username.replace("`", "``") + "_data"
cur.execute("CREATE TABLE `" + table_name + \
"` (Latitude DOUBLE, Longitude DOUBLE, Time DATETIME);")
cur.execute("GRANT INSERT, DROP ON `" + table_name + "` TO %s@%s", \
(username, conf["TRAIN_DB_WEB_HOST"]))
cur.execute("GRANT SELECT ON `" + conf["TRAIN_DATABASE"] + "`.* TO %s@%s", \
(username, conf["TRAIN_DB_WEB_HOST"]))
cur.close()
db.close()
return ServiceResult("application/json", \
jsonsaves({"success": True, "message": "Created new user"}))
def source(args, conf):
if "user" not in args or "pass" not in args:
# return an error result
pass
db = connect(
host=conf["TRAIN_DB_HOST"],
user=args.getfirst("user"),
passwd=args.getfirst("pass"),
db=conf["TRAIN_DATABASE"],
port=conf["TRAIN_DB_PORT"],
)
cur = db.cursor()
cur.execute("SELECT DISTINCT Source FROM `" + conf["DEPARTURE_TABLE"] + "`;")
ret = ServiceResult("application/json", jsonsaves([arr[0] for arr in cur.fetchall()]))
cur.close()
db.close()
return ret
def train(args, conf):
db = connect(
host=conf["TRAIN_DB_HOST"],
user=form.getfirst("user"),
passwd=form.getfirst("pass"),
db=conf["TRAIN_DATABASE"],
port=conf["TRAIN_DB_PORT"],
)
cur = db.cursor()
# Retrieve distinct train numbers from the database
cur.execute(
"SELECT DISTINCT `Number` FROM " + conf["TRAIN_TABLE"] + " WHERE RouteName=%s", args.getfirst("route_name")
)
ret = ServiceResult("application/json", jsonsaves([arr[0] for arr in cur.fetchall()]))
cur.close()
db.close()
return ret
def train(args, conf):
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = form.getfirst("user"), \
passwd = form.getfirst("pass"), \
db = conf["TRAIN_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
cur = db.cursor()
# Retrieve distinct train numbers from the database
cur.execute("SELECT DISTINCT `Number` FROM " + \
conf["TRAIN_TABLE"] + " WHERE RouteName=%s", \
args.getfirst("route_name"))
ret = ServiceResult("application/json", jsonsaves([arr[0] for arr in cur.fetchall()]))
cur.close()
db.close()
return ret
def source(args, conf):
if "user" not in args or "pass" not in args:
# return an error result
pass
db = connect( \
host = conf["TRAIN_DB_HOST"], \
user = args.getfirst("user"), \
passwd = args.getfirst("pass"), \
db = conf["TRAIN_DATABASE"], \
port = conf["TRAIN_DB_PORT"] \
)
cur = db.cursor()
cur.execute("SELECT DISTINCT Source FROM `" + \
conf["DEPARTURE_TABLE"] + "`;")
ret = ServiceResult("application/json", jsonsaves([arr[0] for arr in cur.fetchall()]))
cur.close()
db.close()
return ret
def deregister(args, conf):
# Drop the user's data table from the user's account
username = args.getfirst("user")
passwd = args.getfirst("pass")
db = connect(
host=conf["TRAIN_DB_HOST"], user=username, passwd=passwd, db=conf["USER_DATABASE"], port=conf["TRAIN_DB_PORT"]
)
cur = db.cursor()
table_name = username.replace("`", "``") + "_data"
cur.execute("DROP TABLE `" + table_name + "`;")
cur.close()
db.close()
# Connect with a different account for dropping the user
db = connect(
host=conf["TRAIN_DB_HOST"],
user=conf["GATE_KEEPER_USERNAME"],
passwd=conf["GATE_KEEPER_PASSWD"],
db=conf["USER_DATABASE"],
port=conf["TRAIN_DB_PORT"],
)
cur = db.cursor()
db.close()
return ServiceResult("application/json", jsonsaves({"success": True, "message": "Dropped user from system"}))