59 lines
2.0 KiB
SQL
59 lines
2.0 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,
|
|
latitude DOUBLE,
|
|
longitude DOUBLE,
|
|
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE UNIQUE INDEX idx_unique_device ON lp_ttn_end_device_uplinks (
|
|
device_id,
|
|
application_ids,
|
|
dev_eui,
|
|
join_eui,
|
|
dev_addr
|
|
);
|
|
|
|
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 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,lp_ttn_end_device_uplinks
|
|
wifi_latitude DOUBLE,
|
|
wifi_longitude DOUBLE,
|
|
gnss_latitude DOUBLE,
|
|
gnss_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)
|
|
);
|