167 lines
7.3 KiB
Python
167 lines
7.3 KiB
Python
#!/usr/bin/env python3
|
|
import os
|
|
import sqlite3
|
|
import aprs
|
|
import json
|
|
import aprslib
|
|
import configparser
|
|
import time
|
|
import logging
|
|
from apscheduler.schedulers.asyncio import AsyncIOScheduler
|
|
import time
|
|
|
|
def read_config():
|
|
config = configparser.ConfigParser()
|
|
config.read('config.ini')
|
|
return config
|
|
|
|
def get_db_connection():
|
|
conn = sqlite3.connect('database.db')
|
|
conn.row_factory = sqlite3.Row
|
|
return conn
|
|
|
|
def refresh_kiss_connection(kiss_conn):
|
|
logging.debug("Restarting KISS connection on schedule")
|
|
logging.debug("Stopping current connection")
|
|
kiss_conn.stop()
|
|
#logging.debug("Waiting 5 seconds")
|
|
#time.sleep(5)
|
|
logging.debug("Starting new connection")
|
|
kiss_conn.start()
|
|
|
|
def main():
|
|
|
|
# Add the call and location of this station to the packet info
|
|
config = read_config()
|
|
# MYCALL = os.environ.get("MYCALL", "W1CDN")
|
|
# KISS_HOST = os.environ.get("KISS_HOST", "192.168.0.30")
|
|
# KISS_PORT = os.environ.get("KISS_PORT", "8001")
|
|
|
|
logging.basicConfig(filename=config['Settings']['log_path'], level=logging.DEBUG, \
|
|
format='%(asctime)s - %(message)s')
|
|
logging.debug('============= kiss_and_db.py running =============')
|
|
|
|
ki = aprs.TCPKISS(host=config['Settings']['kiss_host'], port=int(config['Settings']['kiss_port']))
|
|
ki.start()
|
|
|
|
#scheduler = AsyncIOScheduler()
|
|
#scheduler.add_job(refresh_kiss_connection, 'interval', hours = 1, args = [ki])
|
|
#scheduler.start()
|
|
|
|
# Make a simple frame and send it
|
|
# frame = aprs.APRSFrame.ui(
|
|
# destination="APZ001",
|
|
# source=config['Settings']['mycall'],
|
|
# path=["WIDE1-1"],
|
|
# info=b">Hello World!",
|
|
# )
|
|
#ki.write(frame)
|
|
|
|
# Watch for new packets to come in
|
|
while True:
|
|
conn = get_db_connection()
|
|
for frame in ki.read(min_frames=1):
|
|
logging.debug("New packet, trying to parse")
|
|
logging.debug(str(frame))
|
|
try:
|
|
try:
|
|
a = aprslib.parse(str(frame))
|
|
except Exception as error:
|
|
logging.error("Error with aprslib:", exc_info = error)
|
|
else:
|
|
a['station_call'] = config['Settings']['station_call']
|
|
a['station_lat'] = config['Settings']['station_lat']
|
|
a['station_lon'] = config['Settings']['station_lon']
|
|
a['created_unix'] = int(time.time())
|
|
a['party'] = 1
|
|
|
|
# Make this a string and deal with it later (probably a mistake)
|
|
a['path'] = str(a['path'])
|
|
# Process 3rd-party data
|
|
if 'subpacket' in a:
|
|
b = a['subpacket'] # make a copy
|
|
a['subpacket'] = str(a['subpacket']) # turn the original to a string
|
|
b['party'] = 3
|
|
b['path'] = str(b['path'])
|
|
b['header_raw'] = a['raw'].split("}", 1)[0] # just the first part
|
|
b['station_call'] = config['Settings']['station_call']
|
|
b['station_lat'] = config['Settings']['station_lat']
|
|
b['station_lon'] = config['Settings']['station_lon']
|
|
b['created_unix'] = int(time.time())
|
|
#logging.debug(a['path'])
|
|
# Store true/false as 1/0
|
|
if 'alive' in a:
|
|
if a['alive'] == True:
|
|
a['alive'] = 1
|
|
else:
|
|
a['alive'] = 0
|
|
# Build an INSERT statement based on the fields we have from the frame
|
|
attrib_names = ', '.join('"%s"' % w for w in a.keys())
|
|
attrib_values = ", ".join("?" * len(a.keys()))
|
|
logging.debug(attrib_names)
|
|
logging.debug(a.values())
|
|
if 'subpacket' in a:
|
|
# 3rd-party
|
|
b_attrib_names = ', '.join('"%s"' % w for w in b.keys())
|
|
b_attrib_values = ", ".join("?" * len(b.keys()))
|
|
logging.debug(b_attrib_names)
|
|
logging.debug(b.values())
|
|
|
|
try:
|
|
logging.debug("Inserting into database")
|
|
# Insert data
|
|
sql = "INSERT INTO frames ("+attrib_names+") VALUES ("+attrib_values+")"
|
|
conn.execute(sql, list(a.values()))
|
|
if 'subpacket' in a:
|
|
b_sql = "INSERT INTO frames ("+b_attrib_names+") VALUES ("+b_attrib_values+")"
|
|
conn.execute(b_sql, list(b.values()))
|
|
logging.debug("Frames table updated")
|
|
# TODO update stations table here
|
|
# Original intent was to include the id from the frames table,
|
|
# but that would mean making another query.
|
|
# It's not immediately needed, so I'm skipping it.
|
|
# Build query
|
|
# "from" is wrappedin [] because it is a reserved word and using '' doesn't work.
|
|
# https://www.sqlite.org/lang_keywords.html
|
|
#try:
|
|
station_update = "'"+a['from'] +"', '"+ str(a['created_unix']) +"', '1'"
|
|
query3 = "INSERT INTO stations ([from], last_heard_unix, count) \
|
|
VALUES("+station_update+") \
|
|
ON CONFLICT([from]) \
|
|
DO UPDATE SET count = count + 1,\
|
|
last_heard_unix = excluded.last_heard_unix;"
|
|
# Insert/update data
|
|
conn.execute(query3)
|
|
if 'subpacket' in a:
|
|
b_station_update = "'"+b['from'] +"', '"+ str(b['created_unix']) +"', '1'"
|
|
b_query3 = "INSERT INTO stations ([from], last_heard_unix, count) \
|
|
VALUES("+b_station_update+") \
|
|
ON CONFLICT([from]) \
|
|
DO UPDATE SET count = count + 1,\
|
|
last_heard_unix = excluded.last_heard_unix;"
|
|
# Insert/update data
|
|
conn.execute(b_query3)
|
|
logging.debug("Station table updated")
|
|
conn.commit()
|
|
#except:
|
|
# print("Stations table couldn't be updated.")
|
|
|
|
# TODO remove packets that are older ('created') than a limit set in config.ini
|
|
# "5 minutes" also works
|
|
#conn.execute("DELETE FROM frames WHERE created < DATETIME('now', '"+config['Settings']['keep_time']+"')")
|
|
#conn.commit()
|
|
except Exception as error:
|
|
#print("Error with SQLite!")
|
|
logging.error("Error with SQLite!", exc_info = error)
|
|
except Exception as error:
|
|
#print("Frame could not be parsed.")
|
|
logging.error("Frame could not be parsed:", exc_info = error)
|
|
|
|
|
|
conn.close()
|
|
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|