def delete_db_note(note_datetime):
""" Deletes a Note from the SQL Database based on it's DateTime entry. """
sql_query = "DELETE FROM " + str(db_v.table_other) + \
" WHERE " + str(db_v.all_tables_datetime) + \
" = ?;"
sql_data = [note_datetime]
sqlite_database.write_to_sql_database(sql_query, sql_data)
def search_sensor_delete_senor_id():
sensor_id = app_cached_variables.checkin_search_sensor_id
if sensor_id != "":
write_to_sql_database("DROP TABLE '" + sensor_id + "';",
None,
sql_database_location=db_loc)
_update_search_sensor_check_ins(sensor_id)
return view_search_sensor_check_ins()
def _thread_clear_check_ins_counts():
logger.network_logger.info(
"Checkin Database 'Clear Old Checkin Data' Started")
for sensor_id in get_sqlite_tables_in_list(db_loc):
_clear_old_sensor_checkin_data(sensor_id)
write_to_sql_database("VACUUM;", None, sql_database_location=db_loc)
logger.network_logger.info(
"Checkin Database 'Clear Old Checkin Data' Finished")
def update_note_in_database(datetime_note):
""" Takes the provided DateTime and Note as a list then updates the note in the SQL Database. """
try:
data_list = datetime_note.split(command_data_separator)
current_datetime = data_list[0]
custom_datetime = data_list[1]
note = data_list[2]
sql_execute = "UPDATE OtherData SET Notes = ?,UserDateTime = ? WHERE DateTime = ?;"
data_entries = [note, custom_datetime, current_datetime]
sqlite_database.write_to_sql_database(sql_execute, data_entries)
except Exception as error:
logger.primary_logger.error("DB note update error: " + str(error))
def _vacuum_database(selected_database):
if selected_database == "MainDatabase":
db_location = file_locations.sensor_database
elif selected_database == "MQTTSubscriberDatabase":
db_location = file_locations.mqtt_subscriber_database
elif selected_database == "CheckinDatabase":
db_location = file_locations.sensor_checkin_database
else:
db_location = file_locations.uploaded_databases_folder + "/" + selected_database
if os.path.isfile(db_location):
write_to_sql_database("VACUUM;", None, sql_database_location=db_location)
msg = selected_database + " Database has been Shrunk"
return get_message_page("Database Vacuum Successful", msg, page_url="sensor-system", skip_menu_select=True)
msg = selected_database + " Database not found"
return get_message_page("Database Vacuum Failed", msg, page_url="sensor-system", skip_menu_select=True)
def record_trigger(reading, sql_column_name, datetime_stamp):
sql_query = "INSERT OR IGNORE INTO TriggerData ("
sql_data_list = [datetime_stamp]
sql_query += "DateTime,SensorName,IP," + sql_column_name + ") VALUES ("
sql_data_list.append(app_cached_variables.hostname)
sql_data_list.append(app_cached_variables.ip)
sql_data_list.append(str(reading))
for _ in range(len(sql_data_list)):
sql_query += "?,"
sql_query = sql_query[:-1] + ");"
write_to_sql_database(sql_query, sql_data_list)
def _write_mqtt_message_to_sql_database(mqtt_message):
all_tables_datetime = app_cached_variables.database_variables.all_tables_datetime
current_utc_datetime = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]
try:
sensor_topic_as_list = str(mqtt_message.topic).strip().split("/")
payload = str(mqtt_message.payload.decode("UTF-8"))
if len(sensor_topic_as_list) > 0:
sensor_id_str = get_clean_sql_table_name(sensor_topic_as_list[0])
try:
column_and_data_dic = eval(payload)
if type(column_and_data_dic) is not dict:
column_and_data_dic = {sensor_topic_as_list[-1]: payload}
if all_tables_datetime in column_and_data_dic:
column_and_data_dic = None
except Exception as error:
logger.network_logger.debug("MQTT Subscription - payload eval conversion failed: " + str(error))
column_and_data_dic = {sensor_topic_as_list[-1]: payload}
if all_tables_datetime in column_and_data_dic:
column_and_data_dic = None
if column_and_data_dic is None:
logger.network_logger.debug("DateTime found in non-dic - Skipping SQL Write")
else:
if all_tables_datetime not in column_and_data_dic:
column_and_data_dic[all_tables_datetime] = current_utc_datetime
columns_sql_str = ""
data_sql_value_place_marks = ""
data_list = []
for column_name, column_data in column_and_data_dic.items():
if column_name.replace("_", "JJ").isalnum():
_check_sql_table_column_exists(sensor_id_str, column_name)
columns_sql_str += column_name + ","
data_sql_value_place_marks += "?,"
data_list.append(str(column_data))
else:
log_msg = "MQTT Subscriber SQL Recording: Incorrect sensor ID or Type - "
logger.network_logger.warning(log_msg + "Must be Alphanumeric")
if len(columns_sql_str) > 0:
columns_sql_str = columns_sql_str[:-1]
data_sql_value_place_marks = data_sql_value_place_marks[:-1]
sql_string = "INSERT OR IGNORE INTO " + sensor_id_str + \
" (" + columns_sql_str + ") VALUES (" + data_sql_value_place_marks + ")"
write_to_sql_database(sql_string, data_list, sql_database_location=mqtt_sub_db_location)
except Exception as error:
logger.primary_logger.error("MQTT Subscriber Recording Failure: " + str(error))
def _clear_old_sensor_checkin_data(sensor_id):
last_checkin_date = get_one_db_entry_wrapper(sensor_id,
db_v.all_tables_datetime)
sensor_name = get_one_db_entry_wrapper(sensor_id, db_v.sensor_name)
sensor_ip = get_one_db_entry_wrapper(sensor_id, db_v.ip)
program_version = get_one_db_entry_wrapper(sensor_id,
db_v.kootnet_sensors_version)
installed_sensors = get_one_db_entry_wrapper(
sensor_id, db_v.sensor_check_in_installed_sensors)
sensor_uptime = get_one_db_entry_wrapper(sensor_id, db_v.sensor_uptime)
primary_logs = get_one_db_entry_wrapper(sensor_id,
db_v.sensor_check_in_primary_log)
network_logs = get_one_db_entry_wrapper(sensor_id,
db_v.sensor_check_in_network_log)
sensors_logs = get_one_db_entry_wrapper(sensor_id,
db_v.sensor_check_in_sensors_log)
try:
write_to_sql_database("DELETE FROM '" + sensor_id + "';",
None,
sql_database_location=db_loc)
check_sensor_checkin_columns(sensor_id)
sql_ex_string = "INSERT OR IGNORE INTO '" + sensor_id + "' (" + \
db_v.all_tables_datetime + "," + \
db_v.sensor_name + "," + \
db_v.ip + "," + \
db_v.kootnet_sensors_version + "," + \
db_v.sensor_check_in_installed_sensors + "," + \
db_v.sensor_uptime + "," + \
db_v.sensor_check_in_primary_log + "," + \
db_v.sensor_check_in_network_log + "," + \
db_v.sensor_check_in_sensors_log + ")" + \
" VALUES (?,?,?,?,?,?,?,?,?);"
sql_data = [
last_checkin_date, sensor_name, sensor_ip, program_version,
installed_sensors, sensor_uptime, primary_logs, network_logs,
sensors_logs
]
write_to_sql_database(sql_ex_string,
sql_data,
sql_database_location=db_loc)
except Exception as error:
logger.primary_logger.error("Sensor Check-ins - Clearing Sensor '" +
sensor_id + "' Data: " + str(error))
def _thread_delete_sensors_older_then(delete_sensors_older_days):
logger.network_logger.info("Checkin Database Clean-up Started")
try:
datetime_sensor_ids_list = _get_sensor_id_and_last_checkin_date_as_list(
)
current_date_time = datetime.utcnow() + timedelta(
hours=app_config_access.primary_config.utc0_hour_offset)
for date_and_sensor_id in datetime_sensor_ids_list:
clean_last_checkin_date = date_and_sensor_id[1]
if (current_date_time -
clean_last_checkin_date).days >= delete_sensors_older_days:
_delete_sensor_id(date_and_sensor_id[0])
write_to_sql_database("VACUUM;", None, sql_database_location=db_loc)
logger.network_logger.info("Checkin Database Clean-up Finished")
except Exception as error:
logger.primary_logger.warning(
"Error trying to delete old sensors from the Check-Ins database: "
+ str(error))
def add_note_to_database(datetime_note):
""" Takes the provided DateTime and Note as a list then writes it to the SQL Database. """
user_date_and_note = datetime_note.split(command_data_separator)
current_datetime = datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S.%f")[:-3]
if len(user_date_and_note) > 1:
custom_datetime = user_date_and_note[0]
note = user_date_and_note[1]
sql_execute = "INSERT OR IGNORE INTO OtherData (" + \
db_v.all_tables_datetime + "," + \
db_v.other_table_column_user_date_time + "," + \
db_v.other_table_column_notes + ")" + \
" VALUES (?,?,?);"
data_entries = [current_datetime, custom_datetime, note]
sqlite_database.write_to_sql_database(sql_execute, data_entries)
else:
logger.primary_logger.error("Unable to add Note to DB: Bad Note")
def _interval_recording():
""" Starts recording all sensor readings to the SQL database every X Seconds (set in config). """
# Sleep to allow cached variables like sensor IP & hostname to populate
sleep(10)
app_cached_variables.interval_recording_thread.current_state = "Disabled"
if not app_config_access.interval_recording_config.enable_interval_recording:
logger.primary_logger.debug(
"Interval Recording Disabled in the Configuration")
while not app_config_access.interval_recording_config.enable_interval_recording:
sleep(5)
app_cached_variables.interval_recording_thread.current_state = "Running"
logger.primary_logger.info(" -- Interval Recording Started")
app_cached_variables.restart_interval_recording_thread = False
while not app_cached_variables.restart_interval_recording_thread:
try:
new_sensor_data = _get_interval_sensor_readings()
sql_column_names = ""
sql_value_placeholders = ""
sql_data = []
for index, data in new_sensor_data.items():
sql_column_names += index + ","
sql_value_placeholders += "?,"
sql_data.append(str(data))
sql_column_names = sql_column_names[:-1]
sql_value_placeholders = sql_value_placeholders[:-1]
sql_string = "INSERT OR IGNORE INTO " + db_v.table_interval + " (" + sql_column_names + ") " + \
"VALUES (" + sql_value_placeholders + ")"
sqlite_database.write_to_sql_database(sql_string, sql_data)
except Exception as error:
logger.primary_logger.error("Interval Recording Failure: " +
str(error))
sleep_duration_interval = app_config_access.interval_recording_config.sleep_duration_interval
sleep_fraction_interval = 1
if sleep_fraction_interval > sleep_duration_interval:
sleep_fraction_interval = sleep_duration_interval
sleep_total = 0
while sleep_total < sleep_duration_interval and not app_cached_variables.restart_interval_recording_thread:
sleep(sleep_fraction_interval)
sleep_total += sleep_fraction_interval
def remote_sensor_check_ins():
if app_config_access.checkin_config.enable_checkin_recording:
if request.form.get("checkin_id"):
checkin_id = get_clean_sql_table_name(str(request.form.get("checkin_id")))
logger.network_logger.debug("* Sensor ID:" + checkin_id + " checked in from " + str(request.remote_addr))
check_sensor_checkin_columns(checkin_id)
try:
sql_ex_string = "INSERT OR IGNORE INTO '" + checkin_id + "' (" + \
db_v.all_tables_datetime + "," + \
db_v.sensor_name + "," + \
db_v.ip + "," + \
db_v.kootnet_sensors_version + "," + \
db_v.sensor_check_in_installed_sensors + "," + \
db_v.sensor_uptime + "," + \
db_v.sensor_check_in_primary_log + "," + \
db_v.sensor_check_in_network_log + "," + \
db_v.sensor_check_in_sensors_log + ")" + \
" VALUES (?,?,?,?,?,?,?,?,?);"
sensor_name = _get_cleaned_data(request.form.get("sensor_name"))
ip_address = _get_cleaned_data(request.form.get("ip_address"))
program_version = _get_cleaned_data(request.form.get("program_version"))
installed_sensors = _get_cleaned_data(request.form.get("installed_sensors"))
sensor_uptime = _get_cleaned_data(request.form.get("sensor_uptime"))
primary_log = _get_cleaned_data(request.form.get("primary_log"))
network_log = _get_cleaned_data(request.form.get("network_log"))
sensor_log = _get_cleaned_data(request.form.get("sensor_log"))
sql_data = [datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S"), sensor_name, ip_address, program_version,
installed_sensors, sensor_uptime, primary_log, network_log, sensor_log]
write_to_sql_database(sql_ex_string, sql_data, sql_database_location=sc_database_location)
return "OK", 202
except Exception as error:
logger.network_logger.warning("Sensor Checkin error for " + checkin_id + ": " + str(error))
return "Failed", 400
return "Checkin Recording Disabled", 503
def _delete_sensor_id(sensor_id):
""" Deletes provided Text Sensor ID from the Check-In database """
write_to_sql_database("DROP TABLE '" + sensor_id + "';",
None,
sql_database_location=db_loc)
