2023-04-28 07:04:09 +00:00
import json
2023-04-27 14:24:32 +00:00
import sqlite3
# Initialize SQLite database
def initialize_database ( ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" CREATE TABLE IF NOT EXISTS longnames (meshtastic_id TEXT PRIMARY KEY, longname TEXT) "
)
2023-06-08 22:00:06 +00:00
cursor . execute (
" CREATE TABLE IF NOT EXISTS shortnames (meshtastic_id TEXT PRIMARY KEY, shortname TEXT) "
)
2023-04-28 07:04:09 +00:00
cursor . execute (
" CREATE TABLE IF NOT EXISTS plugin_data (plugin_name TEXT, meshtastic_id TEXT, data TEXT, PRIMARY KEY (plugin_name, meshtastic_id)) "
)
2023-04-27 14:24:32 +00:00
conn . commit ( )
2023-04-28 07:04:09 +00:00
def store_plugin_data ( plugin_name , meshtastic_id , data ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" INSERT OR REPLACE INTO plugin_data (plugin_name, meshtastic_id, data) VALUES (?, ?, ?) ON CONFLICT (plugin_name, meshtastic_id) DO UPDATE SET data = ? " ,
( plugin_name , meshtastic_id , json . dumps ( data ) , json . dumps ( data ) ) ,
)
conn . commit ( )
2023-05-12 13:43:57 +00:00
def delete_plugin_data ( plugin_name , meshtastic_id ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" DELETE FROM plugin_data WHERE plugin_name=? AND meshtastic_id=? " ,
( plugin_name , meshtastic_id ) ,
)
conn . commit ( )
2023-04-28 07:04:09 +00:00
# Get the data for a given plugin and Meshtastic ID
def get_plugin_data_for_node ( plugin_name , meshtastic_id ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" SELECT data FROM plugin_data WHERE plugin_name=? AND meshtastic_id=? " ,
(
plugin_name ,
meshtastic_id ,
) ,
)
result = cursor . fetchone ( )
return json . loads ( result [ 0 ] if result else " [] " )
# Get the data for a given plugin
def get_plugin_data ( plugin_name ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" SELECT data FROM plugin_data WHERE plugin_name=? " ,
( plugin_name , ) ,
)
return cursor . fetchall ( )
2023-04-27 14:24:32 +00:00
# Get the longname for a given Meshtastic ID
def get_longname ( meshtastic_id ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" SELECT longname FROM longnames WHERE meshtastic_id=? " , ( meshtastic_id , )
)
result = cursor . fetchone ( )
return result [ 0 ] if result else None
def save_longname ( meshtastic_id , longname ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" INSERT OR REPLACE INTO longnames (meshtastic_id, longname) VALUES (?, ?) " ,
( meshtastic_id , longname ) ,
)
conn . commit ( )
def update_longnames ( nodes ) :
if nodes :
for node in nodes . values ( ) :
user = node . get ( " user " )
if user :
meshtastic_id = user [ " id " ]
longname = user . get ( " longName " , " N/A " )
save_longname ( meshtastic_id , longname )
2023-06-08 22:00:06 +00:00
def get_shortname ( meshtastic_id ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" SELECT shortname FROM shortnames WHERE meshtastic_id=? " , ( meshtastic_id , ) )
result = cursor . fetchone ( )
return result [ 0 ] if result else None
def save_shortname ( meshtastic_id , shortname ) :
with sqlite3 . connect ( " meshtastic.sqlite " ) as conn :
cursor = conn . cursor ( )
cursor . execute (
" INSERT OR REPLACE INTO shortnames (meshtastic_id, shortname) VALUES (?, ?) " ,
( meshtastic_id , shortname ) ,
)
conn . commit ( )
2023-06-09 03:37:55 +00:00
def update_shortnames ( nodes ) :
2023-06-09 03:09:37 +00:00
if nodes :
for node in nodes . values ( ) :
2023-06-08 22:00:06 +00:00
user = node . get ( " user " )
if user :
meshtastic_id = user [ " id " ]
shortname = user . get ( " shortName " , " N/A " )
save_shortname ( meshtastic_id , shortname )