def create_tables(self):
"""
@summary - creates tables for the wificounter database.
@description - creates tables for the wificounter database,
will get commands from the create_tables.txt files
that holds the SQL commands to create
the desired database.
@author - mah60
@param - None
@return - None
"""
# get command
cmd = fr.read_txt_file("create_tables.txt", False).split(':')
for c in cmd: # exectute all commands to create tables
self.db_execute(c, False)
def import_txt_to_db(self, txt_file, index, bytes_send=250000):
"""
@summary - imports the text file data to the given database.
@description - will import the data inside the text file to the
database given in initilisation.
You may be asked during the function to given start
datetime or the filter (mac address and start/end time)
if they have been recorded.
@author - mah60
@param - txt_file - string - file location/path to the recordings text
file.
@param - index - int - config list index, for the text file
@return - None
"""
if (self.db.is_tables()): # check if required tables exist
#print("-------------------------------")
# read data from txt file
data = fr.read_txt_file(txt_file, True)
seconds = [0.00, 0.00,
0.00] # total secounds , current value, reset value
# string for cmds
cmd = [
"INSERT INTO devices ( macAddress ) VALUES ",
"INSERT INTO counters ( macAddress) VALUES ",
"INSERT INTO recordings ( recordingDateTime, rssi, counterId ) VALUES ",
"INSERT INTO devicerecords (deviceId, recordId) Values "
]
# list for stored values
mac_list = []
id_counts = [
self.get_id_count("devices"),
self.get_id_count("counters"),
self.get_id_count("recordings")
]
is_counter_added = False
counter_mac = ""
datetime_set = False
datetime_value = "" # make the start date time value constant
# tell if variables have been reset
self.var_reset = True
for record in data:
if (record != ''):
r_parts = record.split(',')
if (not is_counter_added):
cmd[1] += "('" + r_parts[1] + "'), "
counter_mac = r_parts[1]
id_counts[1] = id_counts[1] + 1
is_counter_added = True
if ('' not in r_parts):
if (not datetime_set
): # check if start datetime value has been set
# done to ensure the device is constant
# check if connected to wifi
if ('1970' in r_parts[2]):
# if not ask for start datetime
#print("Device did not connect to Wifi - what was the start date - time?")
#output = self.ask_datetime()
datetime_value = cf.start_date_time[index]
datetime_set = True
else:
datetime_value = r_parts[2]
datetime_set = True
cmd[0], id_counts[
0], mac_list, match = self.is_mac_exists(
"devices", cmd[0], id_counts[0], r_parts[0],
mac_list)
# make datetime variable for recording time
# if counter crashes timer will restart, then millis given will reset to
seconds[1] = float(r_parts[3]) # set current seconds
if (seconds[2] > seconds[1]):
seconds[0] += seconds[1]
seconds[2] = seconds[1]
else:
# if not add additional second time to total
t = seconds[1] - seconds[2]
seconds[0] += t
seconds[2] = seconds[1]
recordDateTime = datetime.strptime(
datetime_value, '%Y-%m-%d-%H-%M-%S')
recordDateTime = recordDateTime + timedelta(
seconds=seconds[0])
#print(recordDateTime)
cmd[2] += "('" + recordDateTime.strftime(
'%Y-%m-%d-%H-%M-%S.%f') + "', "
cmd[2] += r_parts[4] + ", " + str(id_counts[1]) + "), "
id_counts[2] = id_counts[2] + 1
cmd[3] += "( " + str(match[0]) + ", " + str(
id_counts[2]) + "), "
# send data in insert many once string exceeds x bytes
if (
len(cmd[2].encode('utf-8')) > bytes_send
): # max bytes that can be sent at a time is 4194304
self.send_records(cmd)
self.var_reset = True
# reset variables
mac_list = []
# string for cmds
cmd = [
"INSERT INTO devices ( macAddress ) VALUES ",
"INSERT INTO counters ( macAddress ) VALUES ",
"INSERT INTO recordings ( recordingDateTime, rssi, counterId ) VALUES ",
"INSERT INTO devicerecords (deviceId, recordId) Values "
]
# send final set or records
self.send_records(cmd)
# ask if there is a filter
rssi_limit = self.get_filter(index)
print("the rssi limit is : " + str(rssi_limit))
if (rssi_limit != -100):
datetime_value = datetime.strptime(datetime_value,
'%Y-%m-%d-%H-%M-%S')
print("Start time : " +
datetime_value.strftime('%Y-%m-%d %H:%M:%S'))
self.set_filter(rssi_limit, counter_mac, id_counts[1])