70 lines
2.5 KiB
SQL
70 lines
2.5 KiB
SQL
CREATE TABLE IF NOT EXISTS lp_ttn_end_device_uplinks (
|
|
lp_ttn_end_device_uplinks_id UUID PRIMARY KEY,
|
|
device_id VARCHAR(255) NOT NULL,
|
|
application_ids VARCHAR(255),
|
|
dev_eui VARCHAR(255),
|
|
join_eui VARCHAR(255),
|
|
dev_addr VARCHAR(255),
|
|
received_at_utc DATE,
|
|
battery NUMERIC,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS wifi_scan (
|
|
wifi_scan_id UUID PRIMARY KEY,
|
|
lp_ttn_end_device_uplinks_id UUID,
|
|
mac VARCHAR(255),
|
|
rssi NUMERIC,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS wifi_location (
|
|
mac VARCHAR(255) PRIMARY KEY,
|
|
latitude DOUBLE,
|
|
longitude DOUBLE,
|
|
request_limit_exceeded boolean NOT NULL,
|
|
location_not_resolvable boolean NOT NULL,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS wifi_location_history (
|
|
wifi_location_history_id UUID PRIMARY KEY,
|
|
mac VARCHAR(255),
|
|
latitude DOUBLE NOT NULL,
|
|
longitude DOUBLE NOT NULL,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS ttn_gateway_reception (
|
|
ttn_gateway_reception_id UUID PRIMARY KEY,
|
|
lp_ttn_end_device_uplinks_id UUID,
|
|
gateway_id VARCHAR(255),
|
|
eui VARCHAR(255),
|
|
rssi NUMERIC,
|
|
latitude DOUBLE,
|
|
longitude DOUBLE,
|
|
altitude NUMERIC,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS location (
|
|
location_id UUID PRIMARY KEY,
|
|
lp_ttn_end_device_uplinks_id UUID,
|
|
wifi_latitude DOUBLE,
|
|
wifi_longitude DOUBLE,
|
|
gnss_latitude DOUBLE,
|
|
gnss_longitude DOUBLE,
|
|
ttn_gw_latitude DOUBLE,
|
|
ttn_gw_longitude DOUBLE,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
|
|
);
|