========================= PI5HOME SCHEMA REPORT Luotu: 2026.09.20 07:00:00 Europe/Helsinki Hakemisto: /home/jarkko/pi5home HOME-järjestelmien tietokannat. HA:n ja järjestelmän sisäiset tietokannat voidaan rajata pois EXCLUDE-listalla. ######################### TIETOKANTA: R | ruuvi.sqlite3 ######################### Tauluja: 2 RAKENNE: ruuvi.sqlite3 +--- rv_measurements +--- rv_names ========================= TAULU: ruuvi.sqlite3 -> rv_measurements ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | received_utc TEXT 1 0 2 | topic TEXT 1 0 3 | gateway_mac TEXT 0 0 4 | sensor_mac TEXT 0 0 5 | rssi REAL 0 0 6 | timestamp REAL 0 0 7 | temperature REAL 0 0 8 | humidity REAL 0 0 9 | pressure REAL 0 0 10 | voltage REAL 0 0 11 | tx_power REAL 0 0 12 | acceleration_x REAL 0 0 13 | acceleration_y REAL 0 0 14 | acceleration_z REAL 0 0 15 | movement_counter INTEGER 0 0 16 | measurement_sequence INTEGER 0 0 17 | raw_data TEXT 0 0 INDEKSIT: idx_rv_measurements_sensor_time unique=0 -> sensor_mac -> received_utc ========================= TAULU: ruuvi.sqlite3 -> rv_names ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | sensor_mac TEXT 0 1 1 | name TEXT 1 0 INDEKSIT: sqlite_autoindex_rv_names_2 unique=1 -> name sqlite_autoindex_rv_names_1 unique=1 -> sensor_mac ######################### TIETOKANTA: Z | zigbee.sqlite3 ######################### Tauluja: 6 RAKENNE: zigbee.sqlite3 +--- zb_raw | +--- zb_device +--- zb_data +--- zb_health +--- zb_health_device +--- zb_linkquality ========================= TAULU: zigbee.sqlite3 -> zb_data ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | raw_id INTEGER 1 0 2 | device_id INTEGER 1 0 3 | ts TEXT 1 0 4 | data_json TEXT 1 0 INDEKSIT: idx_zb_data_device_ts unique=0 -> device_id -> ts FOREIGN KEYT: device_id -> zb_device.id raw_id -> zb_raw.id ========================= TAULU: zigbee.sqlite3 -> zb_device ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | ieee_address TEXT 1 0 2 | friendly_name TEXT 0 0 3 | manufacturer TEXT 0 0 4 | model TEXT 0 0 5 | zigbee_model TEXT 0 0 6 | definition TEXT 0 0 7 | device_type TEXT 0 0 8 | network_role TEXT 0 0 9 | power_source TEXT 0 0 10 | room TEXT 0 0 11 | protocol TEXT 0 0 'zigbee' 12 | firmware_id TEXT 0 0 13 | firmware_version TEXT 0 0 14 | seen_first TEXT 1 0 15 | extra_json TEXT 0 0 INDEKSIT: sqlite_autoindex_zb_device_1 unique=1 -> ieee_address ========================= TAULU: zigbee.sqlite3 -> zb_health ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | ts TEXT 1 0 2 | response_time INTEGER 0 0 3 | load_average_1 REAL 0 0 4 | load_average_5 REAL 0 0 5 | load_average_15 REAL 0 0 6 | memory_used_mb REAL 0 0 7 | memory_percent REAL 0 0 8 | process_uptime_sec REAL 0 0 9 | process_memory_used_mb REAL 0 0 10 | process_memory_percent REAL 0 0 11 | mqtt_connected INTEGER 0 0 12 | mqtt_queued INTEGER 0 0 13 | mqtt_published INTEGER 0 0 14 | mqtt_received INTEGER 0 0 15 | raw_json TEXT 1 0 INDEKSIT: idx_zb_health_ts unique=0 -> ts ========================= TAULU: zigbee.sqlite3 -> zb_health_device ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | health_id INTEGER 1 0 2 | device_id INTEGER 0 0 3 | ts TEXT 1 0 4 | messages INTEGER 0 0 5 | messages_per_sec REAL 0 0 6 | leave_count INTEGER 0 0 7 | network_address_changes INTEGER 0 0 INDEKSIT: idx_zb_health_device_ts unique=0 -> device_id -> ts FOREIGN KEYT: device_id -> zb_device.id health_id -> zb_health.id ========================= TAULU: zigbee.sqlite3 -> zb_linkquality ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | raw_id INTEGER 1 0 2 | device_id INTEGER 1 0 3 | ts TEXT 1 0 4 | linkquality INTEGER 0 0 INDEKSIT: idx_zb_linkquality_device_ts unique=0 -> device_id -> ts FOREIGN KEYT: device_id -> zb_device.id raw_id -> zb_raw.id ========================= TAULU: zigbee.sqlite3 -> zb_raw ========================= SARAKE-MÄÄRITTELY: cid | name type nn pk def 0 | id INTEGER 0 1 1 | ts TEXT 1 0 2 | device_id INTEGER 0 0 3 | topic TEXT 1 0 4 | payload_json TEXT 1 0 INDEKSIT: idx_zb_raw_ts unique=0 -> ts idx_zb_raw_device_ts unique=0 -> device_id -> ts FOREIGN KEYT: device_id -> zb_device.id ######################### FORMAATTI- JA AIKATIEDON TARKISTUKSET ######################### R | ruuvi.sqlite3 TAULU: rv_measurements ------------------------- -- Kenttä: received_utc | tyyppi: TEXT -- Esimerkkidata: '2026-08-14T23:42:58.368028+00:00' SELECT "received_utc" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime( "received_utc", 'localtime')) AS suomen_aika FROM "rv_measurements" WHERE "received_utc" IS NOT NULL ORDER BY datetime("received_utc", 'localtime') DESC LIMIT 1; -- Kenttä: timestamp | tyyppi: REAL -- Esimerkkidata: 1786750974.0 SELECT "timestamp" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime("timestamp", 'unixepoch', 'localtime')) AS suomen_aika FROM "rv_measurements" WHERE "timestamp" IS NOT NULL ORDER BY datetime("timestamp", 'unixepoch', 'localtime') DESC LIMIT 1; ######################### FORMAATTI- JA AIKATIEDON TARKISTUKSET ######################### Z | zigbee.sqlite3 TAULU: zb_data ------------------------- -- Kenttä: ts | tyyppi: TEXT -- Esimerkkidata: '2026-08-15T22:42:08.438+00:00' SELECT "ts" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime("ts", 'localtime')) AS suomen_aika FROM "zb_data" WHERE "ts" IS NOT NULL ORDER BY datetime("ts", 'localtime') DESC LIMIT 1; TAULU: zb_health ------------------------- -- Kenttä: ts | tyyppi: TEXT -- Esimerkkidata: '2026-08-15T22:23:07.650+00:00' SELECT "ts" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime("ts", 'localtime')) AS suomen_aika FROM "zb_health" WHERE "ts" IS NOT NULL ORDER BY datetime("ts", 'localtime') DESC LIMIT 1; -- Kenttä: response_time | tyyppi: INTEGER -- Esimerkkidata: 1786831999279 SELECT "response_time" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime( "response_time" / 1000.0, 'unixepoch', 'localtime')) AS suomen_aika FROM "zb_health" WHERE "response_time" IS NOT NULL ORDER BY datetime("response_time" / 1000.0, 'unixepoch', 'localtime') DESC LIMIT 1; -- Kenttä: process_uptime_sec | tyyppi: REAL -- Esimerkkidata: 9602.0 SELECT "process_uptime_sec" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime( "process_uptime_sec", 'unixepoch', 'localtime')) AS suomen_aika FROM "zb_health" WHERE "process_uptime_sec" IS NOT NULL ORDER BY datetime("process_uptime_sec", 'unixepoch', 'localtime') DESC LIMIT 1; -- Kenttä: mqtt_received | tyyppi: INTEGER -- Esimerkkidata: 16 SELECT "mqtt_received" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime( "mqtt_received", 'unixepoch', 'localtime')) AS suomen_aika FROM "zb_health" WHERE "mqtt_received" IS NOT NULL ORDER BY datetime("mqtt_received", 'unixepoch', 'localtime') DESC LIMIT 1; TAULU: zb_health_device ------------------------- -- Kenttä: ts | tyyppi: TEXT -- Esimerkkidata: '2026-08-15T22:23:07.650+00:00' SELECT "ts" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime("ts", 'localtime')) AS suomen_aika FROM "zb_health_device" WHERE "ts" IS NOT NULL ORDER BY datetime("ts", 'localtime') DESC LIMIT 1; TAULU: zb_linkquality ------------------------- -- Kenttä: ts | tyyppi: TEXT -- Esimerkkidata: '2026-08-15T22:42:08.438+00:00' SELECT "ts" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime("ts", 'localtime')) AS suomen_aika FROM "zb_linkquality" WHERE "ts" IS NOT NULL ORDER BY datetime("ts", 'localtime') DESC LIMIT 1; TAULU: zb_raw ------------------------- -- Kenttä: ts | tyyppi: TEXT -- Esimerkkidata: '2026-08-15T22:23:08.349+00:00' SELECT "ts" AS alkuperainen_timestamp, strftime('%Y.%m.%d %H:%M', datetime("ts", 'localtime')) AS suomen_aika FROM "zb_raw" WHERE "ts" IS NOT NULL ORDER BY datetime("ts", 'localtime') DESC LIMIT 1;