Данные про sqlite базу на версии Home Assistant 2021.1.0
Данные про sqlite базу на версии Home Assistant 2021.1.0
sqlite> .tables
events recorder_runs schema_changes states
Это пример работающего сервера Home Assistant. Сколько строк в каждой таблице
sqlite> select count(*) from events;
238912
sqlite> select count(*) from states;
214979
sqlite> select count(*) from schema_changes;
3
sqlite> select count(*) from recorder_runs;
1
sqlite> .schema schema_changes
CREATE TABLE schema_changes (
change_id INTEGER NOT NULL,
schema_version INTEGER,
changed DATETIME,
PRIMARY KEY (change_id)
);
Пример данных из работающего сервера Home Assistant:
sqlite> select * from schema_changes;
change_id schema_version changed
1 9 2020-10-08 05:20:32.262646
2 10 2020-12-13 22:23:21.029082
3 11 2021-01-06 17:49:20.094181
sqlite> .schema recorder_runs
CREATE TABLE recorder_runs (
run_id INTEGER NOT NULL,
start DATETIME,
"end" DATETIME,
closed_incorrect BOOLEAN,
created DATETIME,
PRIMARY KEY (run_id),
CHECK (closed_incorrect IN (0, 1))
);
CREATE INDEX ix_recorder_runs_start_end ON recorder_runs (start, "end");
Пример данных из работающего сервера Home Assistant:
sqlite> select * from recorder_runs;
run_id start end closed_incorrect created
100 2021-01-06 17:49:09.150516 0 2021-01-06 17:49:20.132109
sqlite> .schema events
CREATE TABLE events (
event_id INTEGER NOT NULL,
event_type VARCHAR(32),
event_data TEXT,
origin VARCHAR(32),
time_fired DATETIME,
created DATETIME,
context_id VARCHAR(36),
context_user_id VARCHAR(36),
context_parent_id VARCHAR(36),
PRIMARY KEY (event_id)
);
CREATE INDEX ix_events_context_parent_id ON events (context_parent_id);
CREATE INDEX ix_events_time_fired ON events (time_fired);
CREATE INDEX ix_events_context_id ON events (context_id);
CREATE INDEX ix_events_event_type_time_fired ON events (event_type, time_fired);
CREATE INDEX ix_events_context_user_id ON events (context_user_id);
sqlite> .schema states
CREATE TABLE states (
state_id INTEGER NOT NULL,
domain VARCHAR(64),
entity_id VARCHAR(255),
state VARCHAR(255),
attributes TEXT,
event_id INTEGER,
last_changed DATETIME,
last_updated DATETIME,
created DATETIME,
old_state_id INTEGER,
PRIMARY KEY (state_id),
FOREIGN KEY(event_id) REFERENCES events (event_id),
FOREIGN KEY(old_state_id) REFERENCES states (state_id)
);
CREATE INDEX ix_states_entity_id_last_updated ON states (entity_id, last_updated);
CREATE INDEX ix_states_event_id ON states (event_id);
CREATE INDEX ix_states_last_updated ON states (last_updated);
CREATE INDEX ix_states_old_state_id ON states (old_state_id);