You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
66 lines
1.6 KiB
66 lines
1.6 KiB
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() |
|
]
|
|
|