|
|
|
import collections
|
|
|
|
import sqlite3
|
|
|
|
import time
|
|
|
|
|
|
|
|
Subscription = collections.namedtuple(
|
|
|
|
"Subscription",
|
|
|
|
[
|
|
|
|
"id",
|
|
|
|
"phone",
|
|
|
|
"icao",
|
|
|
|
"description",
|
|
|
|
"last_seen",
|
|
|
|
"platform",
|
|
|
|
"min_lat",
|
|
|
|
"min_lon",
|
|
|
|
"max_lat",
|
|
|
|
"max_lon",
|
|
|
|
],
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
def load_database(file_name):
|
|
|
|
con = sqlite3.connect(file_name)
|
|
|
|
|
|
|
|
con.execute(
|
|
|
|
"CREATE TABLE IF NOT EXISTS subscriptions(phone VARCHAR, icao VARCHAR, description VARCHAR, last_seen INTEGER)"
|
|
|
|
)
|
|
|
|
|
|
|
|
for query in [
|
|
|
|
"ALTER TABLE subscriptions ADD COLUMN platform VARCHAR DEFAULT 'textbelt'",
|
|
|
|
"ALTER TABLE subscriptions ADD COLUMN min_lat REAL DEFAULT -90",
|
|
|
|
"ALTER TABLE subscriptions ADD COLUMN min_lon REAL DEFAULT -180",
|
|
|
|
"ALTER TABLE subscriptions ADD COLUMN max_lat REAL DEFAULT 90",
|
|
|
|
"ALTER TABLE subscriptions ADD COLUMN max_lon REAL DEFAULT 180",
|
|
|
|
]:
|
|
|
|
try:
|
|
|
|
con.execute(query)
|
|
|
|
except sqlite3.OperationalError:
|
|
|
|
pass
|
|
|
|
|
|
|
|
con.commit()
|
|
|
|
|
|
|
|
return con
|
|
|
|
|
|
|
|
|
|
|
|
def update_last_seen_time(con, sub_id):
|
|
|
|
con.execute(
|
|
|
|
"UPDATE subscriptions SET last_seen = ? WHERE rowid = ?",
|
|
|
|
(time.time(), sub_id),
|
|
|
|
)
|
|
|
|
|
|
|
|
|
|
|
|
def get_subscriptions(con):
|
|
|
|
for subscription in con.execute(
|
|
|
|
"SELECT rowid, phone, icao, description, last_seen, platform, min_lat, min_lon, max_lat, max_lon FROM subscriptions"
|
|
|
|
).fetchall():
|
|
|
|
yield Subscription(*subscription)
|
|
|
|
|
|
|
|
|
|
|
|
def get_all_icao(con):
|
|
|
|
return [
|
|
|
|
i[0]
|
|
|
|
for i in con.execute(
|
|
|
|
"SELECT DISTINCT icao FROM subscriptions"
|
|
|
|
).fetchall()
|
|
|
|
]
|