network.sqlite3.sql

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "links" (
	"source"	INTEGER NOT NULL,
	"destination"	INTEGER NOT NULL,
	"snr"	REAL DEFAULT -500,
	"seen"	INTEGER NOT NULL DEFAULT 0,
	PRIMARY KEY("source","destination")
);
CREATE TABLE IF NOT EXISTS "nodes" (
	"id"	INTEGER NOT NULL,
	"shortname"	TEXT,
	"longname"	TEXT,
	"seen"	INTEGER,
	"latitude"	REAL,
	"longitude"	REAL,
	"role"	INTEGER DEFAULT 0,
	"hardware"	INTEGER DEFAULT 0,
	"public_key"	TEXT,
	"first_seen"	INTEGER,
	PRIMARY KEY("id")
);
CREATE TABLE IF NOT EXISTS "packet_types" (
	"port_num"	INTEGER,
	"port_name"	TEXT,
	PRIMARY KEY("port_num")
);
CREATE TABLE IF NOT EXISTS "packets" (
	"source"	INTEGER,
	"type"	INTEGER,
	"time"	INTEGER,
	"hops_used"	INTEGER,
	"rx_snr"	REAL,
	"rx_rssi"	INTEGER,
	"channel_util"	REAL,
	"air_util_tx"	REAL,
	"hop_start"	INTEGER
);
CREATE TABLE IF NOT EXISTS "link_history" (
	"id"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"source"	INTEGER NOT NULL,
	"destination"	INTEGER NOT NULL,
	"snr"	REAL,
	"trace_id"	INTEGER,
	"seen"	INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS "security_events" (
	"id"	INTEGER PRIMARY KEY AUTOINCREMENT,
	"kind"	TEXT NOT NULL,
	"node_id"	INTEGER,
	"detail"	TEXT,
	"seen"	INTEGER NOT NULL
);
CREATE INDEX IF NOT EXISTS "idx_links_seen" ON "links" ("seen");
CREATE INDEX IF NOT EXISTS "idx_packets_time" ON "packets" ("time");
CREATE INDEX IF NOT EXISTS "idx_link_history_seen" ON "link_history" ("seen");
CREATE INDEX IF NOT EXISTS "idx_link_history_edge" ON "link_history" ("source","destination");
CREATE INDEX IF NOT EXISTS "idx_link_history_trace" ON "link_history" ("trace_id");
CREATE INDEX IF NOT EXISTS "idx_security_events_seen" ON "security_events" ("seen");
INSERT OR REPLACE INTO "packet_types" VALUES (0,'Unkown');
INSERT OR REPLACE INTO "packet_types" VALUES (1,'Text');
INSERT OR REPLACE INTO "packet_types" VALUES (2,'Remote Hardware');
INSERT OR REPLACE INTO "packet_types" VALUES (3,'Position');
INSERT OR REPLACE INTO "packet_types" VALUES (4,'Node Info');
INSERT OR REPLACE INTO "packet_types" VALUES (5,'Routing');
INSERT OR REPLACE INTO "packet_types" VALUES (6,'Admin');
INSERT OR REPLACE INTO "packet_types" VALUES (7,'Text Message Compressed');
INSERT OR REPLACE INTO "packet_types" VALUES (8,'Waypoint');
INSERT OR REPLACE INTO "packet_types" VALUES (9,'Audio');
INSERT OR REPLACE INTO "packet_types" VALUES (10,'Detection Sensor');
INSERT OR REPLACE INTO "packet_types" VALUES (11,'Alert');
INSERT OR REPLACE INTO "packet_types" VALUES (12,'Key Verification');
INSERT OR REPLACE INTO "packet_types" VALUES (13,'Remote Shell');
INSERT OR REPLACE INTO "packet_types" VALUES (32,'Reply');
INSERT OR REPLACE INTO "packet_types" VALUES (33,'IP Tunnel');
INSERT OR REPLACE INTO "packet_types" VALUES (34,'Pax Counter');
INSERT OR REPLACE INTO "packet_types" VALUES (35,'Store Forward++');
INSERT OR REPLACE INTO "packet_types" VALUES (36,'Node Status');
INSERT OR REPLACE INTO "packet_types" VALUES (64,'Serial');
INSERT OR REPLACE INTO "packet_types" VALUES (65,'Store Forward');
INSERT OR REPLACE INTO "packet_types" VALUES (66,'Range Test');
INSERT OR REPLACE INTO "packet_types" VALUES (67,'Telemetry');
INSERT OR REPLACE INTO "packet_types" VALUES (68,'ZPS');
INSERT OR REPLACE INTO "packet_types" VALUES (69,'Simulator');
INSERT OR REPLACE INTO "packet_types" VALUES (70,'Traceroute');
INSERT OR REPLACE INTO "packet_types" VALUES (71,'Neighbor Info');
INSERT OR REPLACE INTO "packet_types" VALUES (72,'ATAK Plugin');
INSERT OR REPLACE INTO "packet_types" VALUES (73,'Map Report');
INSERT OR REPLACE INTO "packet_types" VALUES (74,'Power Stress');
INSERT OR REPLACE INTO "packet_types" VALUES (75,'LoRaWAN Bridge');
INSERT OR REPLACE INTO "packet_types" VALUES (76,'Reticulum Tunnel');
INSERT OR REPLACE INTO "packet_types" VALUES (77,'Cayenne');
INSERT OR REPLACE INTO "packet_types" VALUES (78,'ATAK Plugin V2');
INSERT OR REPLACE INTO "packet_types" VALUES (112,'Group Alarm');
INSERT OR REPLACE INTO "packet_types" VALUES (256,'Private');
INSERT OR REPLACE INTO "packet_types" VALUES (257,'ATAK Forwarder');
INSERT OR REPLACE INTO "packet_types" VALUES (512,'Device Telemetry');
INSERT OR REPLACE INTO "packet_types" VALUES (513,'Power Telemetry');
INSERT OR REPLACE INTO "packet_types" VALUES (514,'Environment Telemetry');
INSERT OR REPLACE INTO "packet_types" VALUES (515,'Host Metrics');
INSERT OR REPLACE INTO "packet_types" VALUES (516,'Air Quality');
INSERT OR REPLACE INTO "packet_types" VALUES (518,'Local Stats');
INSERT OR REPLACE INTO "packet_types" VALUES (519,'Traffic Management Stats');
CREATE VIEW ViewPackets AS
SELECT source, longname, type, port_name, time, role, hops_used, rx_snr, rx_rssi, channel_util, air_util_tx FROM packets AS p
INNER JOIN packet_types ON packet_types.port_num = p.type
INNER JOIN nodes ON nodes.id = p.source;
CREATE TRIGGER delete_old_links
AFTER INSERT ON links
BEGIN
    DELETE FROM links
    WHERE seen < (strftime('%s', 'now') - 86400);
END;
CREATE TRIGGER delete_old_packets
AFTER INSERT ON packets
BEGIN
    DELETE FROM packets
    WHERE time < (strftime('%s', 'now') - 604800);
END;
CREATE TRIGGER delete_old_link_history
AFTER INSERT ON link_history
BEGIN
    DELETE FROM link_history
    WHERE seen < (strftime('%s', 'now') - 1209600);
END;
CREATE TRIGGER delete_old_security_events
AFTER INSERT ON security_events
BEGIN
    DELETE FROM security_events
    WHERE seen < (strftime('%s', 'now') - 604800);
END;
COMMIT;