New API endpoint: stations #28

New Issue

W1CDN · 2023-05-13T17:39:11-05:00

W1CDN commented

2023-05-13 17:39:11 -05:00

#1 mentions keeping a list of stations (from field in frames table) in a separate table and updating the location and timestamp every time a new frame is received.

This issue is for querying that table and exposing results to an endpoint, which should be less work than filtering down from the frames table results.

Rows in the stations table would be kept for much longer than rows in the frames table (months? a year?).

Would objects be stored in a separate table, or included here?

stations have a from callsign-SSID and no name
objects have a from callsign-SSID and a name
messages have a from callsign-SSID and format: 'message'

#1 mentions keeping a list of stations (`from` field in frames table) in a separate table and updating the location and timestamp every time a new frame is received. This issue is for querying that table and exposing results to an endpoint, which should be less work than filtering down from the frames table results. Rows in the stations table would be kept for much longer than rows in the frames table (months? a year?). Would objects be stored in a separate table, or included here? - stations have a `from` callsign-SSID and no name - objects have a `from` callsign-SSID and a name - messages have a `from` callsign-SSID and `format: 'message'`

W1CDN referenced this issue

2023-05-13 21:13:19 -05:00

Sort, filter, and limit results at /packets #23

W1CDN commented

2023-05-13 21:17:44 -05:00

Like this from current dw logs and frontail. Fields like callsign, count, last heard, location, symbol?

Like this from current dw logs and frontail. Fields like callsign, count, last heard, location, symbol? ![image](/attachments/91e2170f-f459-40d6-9efc-b6b99f8d7825)

image.png

347 KiB

W1CDN commented

2023-06-24 19:07:24 -05:00

Working on this in #30.

W1CDN commented

2023-06-27 21:47:19 -05:00

No, this will be a different PR. The query right now seems OK, and I turned off autoremove for now in 8f3b2ae707.

No, this will be a different PR. The query right now seems OK, and I turned off autoremove for now in https://amiok.net/gitea/W1CDN/aprs_tool/commit/8f3b2ae7075cc5c2df22ed9e1293e2690e31f4d8.

W1CDN commented

2023-06-27 21:48:56 -05:00

Copied from #30 (comment):

Experimenting with new stations table.

CREATE TABLE "stations" (
	"id"	INTEGER NOT NULL UNIQUE,
	"from"	TEXT,
	"frames_id"	INTEGER,
	"last_heard_unix"	INTEGER,
	"count"	INTEGER,
	PRIMARY KEY("id" AUTOINCREMENT)
);

I set up a query in unique_stations() to show the station list for now, but if frames is cleaned up periodically (#31), the station list may still be useful in the future.

I have this comment in place in tcp_kiss_send_recv.py:
# TODO update stations table here

Copied from https://amiok.net/gitea/W1CDN/aprs_tool/pulls/30#issuecomment-947: ----- Experimenting with new `stations` table. ``` CREATE TABLE "stations" ( "id" INTEGER NOT NULL UNIQUE, "from" TEXT, "frames_id" INTEGER, "last_heard_unix" INTEGER, "count" INTEGER, PRIMARY KEY("id" AUTOINCREMENT) ); ``` I set up a query in `unique_stations()` to show the station list for now, but if `frames` is cleaned up periodically (#31), the station list may still be useful in the future. I have this comment in place in `tcp_kiss_send_recv.py`: `# TODO update stations table here`

W1CDN commented

2024-01-09 09:54:46 -06:00

This has been completed.

W1CDN closed this issue

2024-01-09 09:54:47 -06:00

W1CDN referenced this issue

2024-01-09 09:55:39 -06:00

Autoremove old packets #56

Sign in to join this conversation.