DB: move to per-packet tables? #8

New issue

Open

opened 2024-04-24 20:27:53 +00:00 by saji · 0 comments

saji commented

2024-04-24 20:27:53 +00:00

Owner

New system

We move to tables named per packet, with the following schema

`timestamp`	`idx <optional>`	`value1`	`value2`
2024-04-01..	3	1.2	012

The table will be named packet_name. The idx column is used to store repeated packet indexes.

We also introduce a packet metadata table:

packet name	can_id	is_extended	etc
bms_measurement	0x12	false	`arbitrary JSON`

Direct queries on single packets are now easy:
SELECT timestamp, value1 FROM bms_measurement WHERE timestamp BETWEEN <etc>

Joins are slightly more complex, but better than they were previously.

SELECT
  timestamp - timestamp % 60 as ts,
  current
  module_temp
in

tbh i don't know the rest

This was a design mistake. Mainly because schema generation is now done at the same time as the CAN packet decoders, which felt wrong. EAV is worse, especially for disk size, which turns out to actually be problematic. ### New system We move to tables named per packet, with the following schema | `timestamp` | `idx <optional>` | `value1` | `value2` | | ------------------ | --------------------- | ----------- | ----------- | | 2024-04-01.. | 3 | 1.2 | 012 | The table will be named `packet_name`. The idx column is used to store repeated packet indexes. We also introduce a packet metadata table: | packet name | can_id | is_extended | etc | | - | - | - | - | | bms_measurement | 0x12 | false | `arbitrary JSON` | Direct queries on single packets are now easy: `SELECT timestamp, value1 FROM bms_measurement WHERE timestamp BETWEEN <etc>` Joins are slightly more complex, but better than they were previously. ```sql SELECT timestamp - timestamp % 60 as ts, current module_temp in ``` tbh i don't know the rest

No labels

No milestone

No project

No assignees

1 participant

Notifications

Due date

The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Reference: saji/gotelem#8

No description provided.