Структура базы данных Home Assistant

Данные про 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

Таблица schema_changes

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

Таблица recorder_runs

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

Таблица events

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);

Таблица states

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);
Редактировать страницу на GitHub