localhorst/LocationHub
SHA256
1
0
Fork 0
Code Issues 2 Pull Requests 1 Actions Packages Projects Releases Wiki Activity

add timestamps to DB model

Browse Source
This commit is contained in:
Hendrik Schutter 2025-01-25 21:51:42 +01:00
parent 49fe1b4ce3
commit a745aaf9d0
1 changed files with 3 additions and 1 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

4
server/sql/tables.sql
View File

@ -5,7 +5,7 @@ CREATE TABLE IF NOT EXISTS lp_ttn_end_device_uplinks (
dev_eui VARCHAR(255), dev_eui VARCHAR(255),
join_eui VARCHAR(255), join_eui VARCHAR(255),
dev_addr VARCHAR(255), dev_addr VARCHAR(255),
received_at_utc DATE, received_at_utc DATE NOT NULL,
battery NUMERIC, battery NUMERIC,
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
@ -16,6 +16,7 @@ CREATE TABLE IF NOT EXISTS wifi_scan (
lp_ttn_end_device_uplinks_id UUID, lp_ttn_end_device_uplinks_id UUID,
mac VARCHAR(255), mac VARCHAR(255),
rssi NUMERIC, rssi NUMERIC,
scanned_at_utc DATE NOT NULL;
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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) FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
@ -63,6 +64,7 @@ CREATE TABLE IF NOT EXISTS location (
gnss_longitude DOUBLE, gnss_longitude DOUBLE,
ttn_gw_latitude DOUBLE, ttn_gw_latitude DOUBLE,
ttn_gw_longitude DOUBLE, ttn_gw_longitude DOUBLE,
gnss_location_at_utc DATE;
created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP, created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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) FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)