def set(self, key, value):
sql = Database(self.filePath)
if sql.is_present("SELECT * FROM wlc_settings WHERE key=?",(key,)):
sql.write_data("UPDATE wlc_settings SET value=? WHERE key=?",(value,key,))
else:
sql.write_data("INSERT INTO wlc_settings (key,value) VALUES (?,?)",(key,value,))
sql.commit()
def check_db(self):
#init DB
sql = Database(self.filePath)
query = "CREATE TABLE if not exists wlc_aps (ap_key TEXT, ap_name TEXT, ap_location TEXT)"
sql.write_data_raw(query)
query = "CREATE TABLE if not exists wlc_settings (key TEXT PRIMARY KEY, value TEXT)"
sql.write_data_raw(query)
query = "CREATE TABLE if not exists wlc_ap_groups (id INTEGER PRIMARY KEY, group_name TEXT, ap_lat REAL, ap_lng REAL)"
sql.write_data_raw(query)
query = "CREATE TABLE if not exists wlc_ap_group_binding (id INTEGER PRIMARY KEY, group_id, ap_key TEXT)"
sql.write_data_raw(query)
query = "CREATE TABLE if not exists wlc_ap_clients (id INTEGER PRIMARY KEY, ap_key text,timestamp INTEGER, clients INTEGER)"
sql.write_data_raw(query)
query = "CREATE INDEX if not exists timestampSort on wlc_ap_clients (timestamp DESC)"
sql.write_data_raw(query)
sql.commit()
def save_groups(self,groups):
db = Database(self.filePath)
for group in groups:
groupID = group['id']
if group['id'] is not None:
#update
db.write_data("UPDATE wlc_ap_groups SET group_name=?,ap_lat=?,ap_lng=? WHERE id=?",(group['name'],group['lat'],group['lng'],group['id'],))
else:
#insert
db.write_data("INSERT INTO wlc_ap_groups (group_name, ap_lat, ap_lng) VALUES (?,?,?)",(group['name'],group['lat'],group['lng'],))
groupID = db.get_data_single_raw("SELECT max(id) from wlc_ap_groups")
db.write_data("DELETE FROM wlc_ap_group_binding WHERE group_id=?",(groupID,))
APs = group['aps'].split(":")
for ap in APs:
if ap != "":
db.write_data("INSERT INTO wlc_ap_group_binding (group_id,ap_key) VALUES (?,?)",(groupID,ap,))
db.commit()
def save_data(self, ap_data):
sql = Database(self.filePath)
paramList = []
timestamp = time.time()
for ap in ap_data:
params = (str(ap['key']),)
query = "SELECT * FROM wlc_aps WHERE ap_key=?"
apPresent = sql.is_present(query,params)
if apPresent:
params = (str(ap['name']),str(ap['location']),str(ap['key']))
query = "UPDATE wlc_aps SET ap_name=?, ap_location=? WHERE ap_key=?"
sql.write_data(query, params)
else:
params = (str(ap['key']),str(ap['name']),str(ap['location']))
query = "Insert INTO wlc_aps (ap_key,ap_name,ap_location) VALUES (?,?,?)"
sql.write_data(query,params)
params = (str(ap['key']),str(timestamp),str(ap['clients']),)
paramList.append(params)
query = "INSERT INTO wlc_ap_clients (ap_key, timestamp , clients) VALUES (?,?,?)"
sql.write_data_dump(query,paramList)
sql.commit()
def delete_group(self,groupID):
db = Database(self.filePath)
db.write_data("DELETE FROM wlc_ap_group_binding WHERE group_id=?",(groupID,))
db.write_data("DELETE FROM wlc_ap_groups WHERE id=?",(groupID,))
db.commit()
