Coverage for custom_components/ha_repl_server/sql.py: 94%
201 statements
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-17 19:05 +0000
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-17 19:05 +0000
1"""Run a read-only query against Home Assistant's own Recorder database
2and hand the rows back as Arrow - reusing the recorder's already-configured
3SQLAlchemy engine (so sqlite/postgresql/mysql all just work) rather than
4opening a second connection or driver of our own.
6That is all this side does: one SELECT in, row-capped Arrow IPC bytes out
7(see websocket_api.py's ha_repl_server/sql). There is no result object
8here to explore - the client package (homeassistant_repl.sql) rebuilds one
9from the bytes, and rendering, slicing, dataframes and the rest live there,
10in the developer's own process and environment. Every dependency here is
11one the running Home Assistant instance pays for, hence nanoarrow
12(https://arrow.apache.org/nanoarrow/, ~1MB and dependency-free) and not
13pyarrow.
15Column types come from Home Assistant's own declared recorder schema
16(homeassistant.components.recorder.db_schema) wherever a result column's
17name matches a real column there - no runtime value sniffing needed for the
18handful of stable tables (events, states, statistics, ...) this is meant
19for. A column whose name isn't recognised there (e.g. an aggregate like
20COUNT(*)) falls back to a one-off peek at its own first non-null value, not
21a sniff of every column.
23The same schema says what a column means as well as how it is stored: a
24TIMESTAMP_TYPE or DATETIME_TYPE column goes out as an Arrow timestamp, a
25CONTEXT_BINARY_TYPE one as the id it holds - see _column_kind(). `raw`
26switches all of that off, for the values as the database has them.
28A query can also name a virtual table (see _virtual_tables() below): a
29ready-made join that is not in the database, added to the query as a common table expression
30when the query mentions it, so nothing is ever created in the recorder's
31own database and the database still does all the work of the query.
32"""
34from __future__ import annotations
36import functools
37import inspect
38import io
39import re
40from collections.abc import Callable
41from dataclasses import dataclass
42from datetime import UTC, date, datetime, timedelta
43from decimal import Decimal
44from typing import Any
46import nanoarrow as na
47import sqlalchemy as sa
48import sqlparse
49from homeassistant.components.recorder import db_schema, get_instance
50from homeassistant.components.recorder.core import Recorder
51from homeassistant.components.recorder.models.context import (
52 bytes_to_ulid_or_none,
53 bytes_to_uuid_hex_or_none,
54)
55from homeassistant.core import HomeAssistant
56from homeassistant.helpers.recorder import session_scope
57from nanoarrow.ipc import StreamWriter
59from .const import DEFAULT_MAX_ROWS
61_FETCH_CHUNK_SIZE = 200
64class SqlError(Exception):
65 """A rejected or failed query - not a bug in the shell itself."""
68@dataclass(frozen=True, eq=False)
69class _ColumnKind:
70 """What a recorder column goes out as: its Arrow type, and how each
71 non-null value gets there if it isn't that already."""
73 arrow: Any
74 convert: Callable[[Any], Any] | None = None
77# Most specific first: SmallInteger/BigInteger are themselves Integer
78# subclasses in SQLAlchemy, and Text is a String subclass - the first
79# isinstance match wins, so the narrower types have to come first.
80_STORAGE_KINDS: tuple[tuple[type, _ColumnKind], ...] = (
81 (sa.SmallInteger, _ColumnKind(na.int32())),
82 (sa.BigInteger, _ColumnKind(na.int64())),
83 (sa.Integer, _ColumnKind(na.int64())),
84 (sa.Boolean, _ColumnKind(na.bool_())),
85 (sa.Float, _ColumnKind(na.float64())),
86 (sa.Numeric, _ColumnKind(na.float64())),
87 (sa.LargeBinary, _ColumnKind(na.binary())),
88 (sa.Text, _ColumnKind(na.string())),
89 (sa.String, _ColumnKind(na.string())),
90)
92_EPOCH = datetime(1970, 1, 1, tzinfo=UTC)
93_MICROSECOND = timedelta(microseconds=1)
94_UTC_MICROS = na.timestamp("us", timezone="UTC")
97def _timestamp_micros(value: Any) -> int:
98 """A TIMESTAMP_TYPE value - seconds since the epoch, as a float."""
99 return round(float(value) * 1_000_000)
102def _datetime_micros(value: Any) -> int:
103 """A DATETIME_TYPE value: a datetime, or the text of one where the
104 driver doesn't parse it (SQLite, for a query given as text). The
105 recorder writes these in UTC, so one without a timezone is UTC."""
106 if isinstance(value, str): 106 ↛ 108line 106 didn't jump to line 108 because the condition on line 106 was always true
107 value = datetime.fromisoformat(value)
108 if value.tzinfo is None: 108 ↛ 110line 108 didn't jump to line 110 because the condition on line 108 was always true
109 value = value.replace(tzinfo=UTC)
110 return (value - _EPOCH) // _MICROSECOND
113_TIMESTAMP = _ColumnKind(_UTC_MICROS, _timestamp_micros)
114_DATETIME = _ColumnKind(_UTC_MICROS, _datetime_micros)
115_CONTEXT_ULID = _ColumnKind(na.string(), lambda v: bytes_to_ulid_or_none(bytes(v)))
116_CONTEXT_UUID_HEX = _ColumnKind(
117 na.string(), lambda v: bytes_to_uuid_hex_or_none(bytes(v))
118)
121def _column_kind(column: Any, raw: bool) -> _ColumnKind | None:
122 """How this db_schema column goes out, or None to go by its values.
124 Going by the type db_schema declares it with, unless `raw`:
125 TIMESTAMP_TYPE and DATETIME_TYPE as a UTC timestamp, and
126 CONTEXT_BINARY_TYPE as the id text Home Assistant itself makes of it.
127 Otherwise by how it is stored - where `raw` leaves a Boolean to its
128 values too, since not every database has a boolean to give back.
129 """
130 if not raw:
131 # TIMESTAMP_TYPE is DOUBLE_TYPE under another name - the very same
132 # object, so the type alone can't tell `start_ts` from `mean`. The
133 # `_ts` every timestamp column's name ends in does.
134 if column.type is db_schema.TIMESTAMP_TYPE and column.name.endswith("_ts"):
135 return _TIMESTAMP
136 if column.type is db_schema.DATETIME_TYPE:
137 return _DATETIME
138 if column.type is db_schema.CONTEXT_BINARY_TYPE:
139 # Nothing in db_schema says which id a context column holds: a
140 # ULID, except the user's, which is a UUID - as the recorder's
141 # own ulid_to_bytes_or_none / uuid_hex_to_bytes_or_none calls
142 # have it when it writes them.
143 return _CONTEXT_UUID_HEX if "user_id" in column.name else _CONTEXT_ULID
144 elif isinstance(column.type, sa.Boolean):
145 return None
146 return next(
147 (
148 kind
149 for sqla_type, kind in _STORAGE_KINDS
150 if isinstance(column.type, sqla_type)
151 ),
152 None,
153 )
156@functools.cache
157def _recorder_column_kinds(raw: bool = False) -> dict[str, _ColumnKind]:
158 """{column name: kind} for every current (non-deprecated) column
159 across Home Assistant's own recorder tables - built once from
160 homeassistant.components.recorder.db_schema, the actual SQLAlchemy
161 models every other recorder-reading component already trusts, rather
162 than a copy of our own that could drift from a future schema migration.
164 A name that means different things on different tables (plain "state":
165 text on States, a float on Statistics) is left out rather than guessed
166 at - _build_column() falls back to a one-off peek at the query's own
167 result for those.
168 """
169 by_name: dict[str, _ColumnKind] = {}
170 ambiguous: set[str] = set()
171 for name, obj in vars(db_schema).items():
172 # Legacy* models are old schema versions kept around for migrations,
173 # not what a live query actually reads - including them just creates
174 # spurious "ambiguous" exclusions for columns that are perfectly
175 # unambiguous in the current schema (e.g. LegacyStatisticsMeta.id
176 # was Integer; the current StatisticsMeta.id is BigInteger).
177 if name.startswith("Legacy"):
178 continue
179 table = getattr(obj, "__table__", None)
180 if table is None:
181 continue
182 for column in table.columns:
183 if isinstance(column.type, (db_schema.Unused, db_schema.UnusedDateTime)):
184 continue
185 kind = _column_kind(column, raw)
186 if kind is None:
187 continue
188 if by_name.setdefault(column.name, kind) is not kind:
189 ambiguous.add(column.name)
190 for name in ambiguous:
191 by_name.pop(name, None)
192 return by_name
195def _coerce_for_arrow(value: Any) -> Any:
196 """Recorder/DBAPI values nanoarrow's schema-explicit builder wouldn't
197 otherwise accept as-is - everything else (including a plain int where a
198 float column is declared, which SQLite's dynamic typing can hand back)
199 is left alone; nanoarrow itself coerces that case fine.
200 """
201 if isinstance(value, Decimal): 201 ↛ 202line 201 didn't jump to line 202 because the condition on line 201 was never true
202 return float(value)
203 if isinstance(value, (datetime, date)): 203 ↛ 204line 203 didn't jump to line 204 because the condition on line 203 was never true
204 return value.isoformat()
205 return value
208def _infer_arrow_type(values: list[Any]) -> Any:
209 """A one-off peek at a single unrecognised column's own first non-null
210 value (e.g. an aggregate like COUNT(*)) - not a sniff of every column,
211 only the ones _recorder_column_kinds() doesn't already know."""
212 for value in values:
213 if value is None: 213 ↛ 214line 213 didn't jump to line 214 because the condition on line 213 was never true
214 continue
215 if isinstance(value, bool): 215 ↛ 216line 215 didn't jump to line 216 because the condition on line 215 was never true
216 return na.bool_()
217 if isinstance(value, int):
218 return na.int64()
219 if isinstance(value, float):
220 return na.float64()
221 if isinstance(value, (bytes, bytearray)): 221 ↛ 222line 221 didn't jump to line 222 because the condition on line 221 was never true
222 return na.binary()
223 break
224 return na.string()
227def _build_column(name: str, values: list[Any], raw: bool = False) -> Any:
228 kind = _recorder_column_kinds(raw).get(name)
229 if kind is not None and kind.convert is not None:
230 try:
231 return na.array(
232 [None if v is None else kind.convert(v) for v in values],
233 schema=kind.arrow,
234 )
235 except ValueError, TypeError, OverflowError:
236 # Not the recorder's column of that name after all (an alias,
237 # say) - go by the values, as for any other name.
238 kind = None
239 arrow_type = kind.arrow if kind is not None else _infer_arrow_type(values)
240 converted = [_coerce_for_arrow(v) for v in values]
241 try:
242 return na.array(converted, schema=arrow_type)
243 except ValueError, TypeError:
244 # The declared/inferred type didn't actually fit this query's
245 # values (e.g. a cast, or a dialect quirk) - a plain string column
246 # always works, and a confusing value beats a failed query.
247 return na.array(
248 [None if v is None else str(v) for v in converted], schema=na.string()
249 )
252def _is_legacy(column: Any) -> bool:
253 """Declared in db_schema with one of its UNUSED_LEGACY_* types: still
254 in the table, no longer written to."""
255 return any(
256 column.type is v
257 for k, v in vars(db_schema).items()
258 if k.startswith("UNUSED_LEGACY")
259 )
262@dataclass(frozen=True)
263class _VirtualTable:
264 """A named join offered alongside the recorder's own tables. `select`
265 is the query it stands for, `columns` what that gives, in order."""
267 name: str
268 doc: str
269 columns: tuple[Any, ...]
270 select: str
273def _named_rows(
274 name: str, doc: str, rows: Any, key: str, names: Any, named: str, name_key: str
275) -> _VirtualTable:
276 """The virtual table of the `rows` table with `named`, from the `names` table
277 its `key` column points at, as the first column - then every column
278 of `rows` still in use, apart from `key` itself. Built from db_schema
279 rather than written out, so a column Home Assistant adds to, or
280 retires from, a table is followed here too."""
281 kept = [c for c in rows.c if not _is_legacy(c) and c.name != key]
282 columns = ", ".join([f"m.{named}", *(f"t.{c.name}" for c in kept)])
283 # Every name here is one of db_schema's own; nothing of a query's is in it.
284 select = (
285 f"select {columns} from {rows.name} t " # nosec B608
286 f"join {names.name} m on m.{name_key} = t.{key}"
287 )
288 return _VirtualTable(name, doc, (names.c[named], *kept), select)
291@functools.cache
292def _virtual_tables() -> tuple[_VirtualTable, ...]:
293 states: Any = db_schema.States.__table__
294 events: Any = db_schema.Events.__table__
295 statistics: Any = db_schema.Statistics.__table__
296 short_term: Any = db_schema.StatisticsShortTerm.__table__
297 statistics_meta: Any = db_schema.StatisticsMeta.__table__
298 return (
299 _named_rows(
300 "state_history",
301 "State change history with each row's entity_id: states joined to "
302 "states_meta, without the legacy columns or the join key.",
303 states,
304 "metadata_id",
305 db_schema.StatesMeta.__table__,
306 "entity_id",
307 "metadata_id",
308 ),
309 _named_rows(
310 "event_history",
311 "Event history with each row's event_type: events joined to "
312 "event_types, without the legacy columns or the join key.",
313 events,
314 "event_type_id",
315 db_schema.EventTypes.__table__,
316 "event_type",
317 "event_type_id",
318 ),
319 _named_rows(
320 "statistics_history",
321 "Long term statistics with each row's statistic_id: statistics "
322 "joined to statistics_meta, without the legacy columns or the "
323 "join key.",
324 statistics,
325 "metadata_id",
326 statistics_meta,
327 "statistic_id",
328 "id",
329 ),
330 _named_rows(
331 "statistics_short_term_history",
332 "Short term statistics with each row's statistic_id: "
333 "statistics_short_term joined to statistics_meta, without the "
334 "legacy columns or the join key.",
335 short_term,
336 "metadata_id",
337 statistics_meta,
338 "statistic_id",
339 "id",
340 ),
341 )
344# Databases that would otherwise build a virtual table's whole result first when a
345# query uses it more than once, and so read all of `states` to do it.
346_NOT_MATERIALIZED_DIALECTS = ("sqlite", "postgresql")
349def _add_virtual_tables(query: str, dialect: str | None) -> str:
350 """`query` with a `with` clause defining each virtual table it mentions - going
351 by its words, not a parse - or as it was if it mentions none. Joins a
352 `with` clause the query already starts with, rather than adding a
353 second one.
354 """
355 words = set(re.findall(r"\w+", query.lower()))
356 virtual = [v for v in _virtual_tables() if v.name in words]
357 if not virtual:
358 return query
359 hint = " not materialized" if dialect in _NOT_MATERIALIZED_DIALECTS else ""
360 definitions = ", ".join(f"{v.name} as{hint} ({v.select})" for v in virtual)
361 tokens = [t for s in sqlparse.parse(query) for t in s.flatten()]
362 real = [
363 i
364 for i, t in enumerate(tokens)
365 if not t.is_whitespace and t.ttype not in sqlparse.tokens.Comment
366 ]
367 values = [t.value for t in tokens]
368 first = real[0]
369 if tokens[first].ttype is sqlparse.tokens.Keyword.CTE:
370 recursive = len(real) > 1 and tokens[real[1]].normalized == "RECURSIVE"
371 values.insert((real[1] if recursive else first) + 1, f" {definitions},")
372 else:
373 values.insert(first, f"with {definitions} ")
374 return "".join(values)
377def _check_select_only(query: str) -> None:
378 statements = [s for s in sqlparse.parse(query) if s.token_first(skip_cm=True)]
379 if not statements:
380 raise SqlError("empty query")
381 if len(statements) > 1:
382 raise SqlError("only a single statement is allowed")
383 if statements[0].get_type() != "SELECT":
384 raise SqlError("only SELECT queries are allowed")
387def _fetch_rows(
388 hass: HomeAssistant, query: str, max_rows: int | None
389) -> tuple[list[str], list[tuple[Any, ...]], bool]:
390 """Runs on the recorder's own executor thread - see sql() below for why
391 (SQLite, and the recorder's pooled connections generally, are
392 thread-affine, so this can't run on the event loop or hass's own
393 generic executor).
394 """
395 chunk_size = (
396 _FETCH_CHUNK_SIZE
397 if max_rows is None
398 else max(1, min(max_rows, _FETCH_CHUNK_SIZE))
399 )
400 with session_scope(hass=hass, read_only=True) as session:
401 result = session.execute(sa.text(query))
402 columns = list(result.keys())
403 rows: list[tuple[Any, ...]] = []
404 truncated = False
405 for row in result.yield_per(chunk_size):
406 if max_rows is not None and len(rows) >= max_rows:
407 truncated = True
408 break
409 rows.append(tuple(row))
410 result.close()
411 return columns, rows, truncated
414@dataclass(frozen=True)
415class ArrowResult:
416 """One query's rows, already serialized: `data` is a single Arrow IPC
417 stream holding one struct-typed batch, one named field per column.
418 `truncated` isn't part of the Arrow data (it describes the query, not
419 the rows), so it travels alongside.
420 """
422 data: bytes
423 rowcount: int
424 truncated: bool
427async def sql(
428 hass: HomeAssistant,
429 query: str,
430 *,
431 max_rows: int | None = DEFAULT_MAX_ROWS,
432 raw: bool = False,
433) -> ArrowResult:
434 """Run a single read-only SQL SELECT against Home Assistant's Recorder
435 database and return the rows as Arrow IPC bytes.
437 Only one SELECT statement is allowed - the same restriction Home
438 Assistant's own `sql` integration applies, since this runs against your
439 live recorder database and a stray UPDATE/DELETE here is as real as one
440 from any other integration. At most `max_rows` rows are fetched (default
441 1000) - rows beyond it are never transferred from the database, not
442 just discarded afterwards; None means no cap at all.
444 The query can use a virtual table, such as `state_history`, as if it
445 were a real one - see _virtual_tables().
447 A column named as one of the recorder's own is given what db_schema
448 says it holds - a time for a TIMESTAMP_TYPE or DATETIME_TYPE column,
449 an id's text for a context one - unless `raw`, which leaves every
450 value as the database gave it.
451 """
452 if max_rows is not None and max_rows < 1:
453 raise SqlError("max_rows must be at least 1")
454 _check_select_only(query)
455 try:
456 instance: Recorder = get_instance(hass)
457 except KeyError:
458 raise SqlError("the recorder is not set up on this instance") from None
459 columns, rows, truncated = await instance.async_add_executor_job(
460 _fetch_rows, hass, _add_virtual_tables(query, instance.dialect_name), max_rows
461 )
462 column_values = list(zip(*rows, strict=True)) if rows else [() for _ in columns]
463 arrays = {
464 name: _build_column(name, list(values), raw)
465 for name, values in zip(columns, column_values, strict=True)
466 }
467 schema = na.struct({name: arr.schema for name, arr in arrays.items()})
468 batch = na.c_array_from_buffers(
469 schema, length=len(rows), buffers=[], children=list(arrays.values())
470 )
471 buf = io.BytesIO()
472 with StreamWriter.from_writable(buf) as writer:
473 writer.write_stream(batch)
474 return ArrowResult(buf.getvalue(), len(rows), truncated)
477@functools.cache
478def table_schemas() -> list[dict[str, Any]]:
479 """The recorder's own current (non-deprecated) tables and their columns,
480 as plain data - from homeassistant.components.recorder.db_schema, the
481 same source _recorder_column_kinds() reads above, not a live reflection
482 of the connected database: these tables are schema-defined and the same
483 for every instance of a given HA version. `class` and `doc` are the
484 name and docstring of the db_schema class mapped to each table, and a
485 column is `legacy` when db_schema declares it with one of its
486 UNUSED_LEGACY_* types: still in the table, no longer written to.
488 The virtual tables a query can name are listed among them, marked
489 `virtual`, with no class and with the query each stands for as its
490 `definition`.
491 """
492 classes: dict[str, type] = {}
493 for name, obj in vars(db_schema).items():
494 # only the mapped classes themselves, not an alias of one (OLD_STATE)
495 if name.startswith("Legacy") or not isinstance(obj, type):
496 continue
497 table = getattr(obj, "__table__", None)
498 if table is not None:
499 classes[table.name] = obj
500 tables = [
501 {
502 "name": name,
503 "class": cls.__name__,
504 "doc": inspect.cleandoc(cls.__doc__ or ""),
505 "virtual": False,
506 "columns": [
507 {
508 "name": column.name,
509 "type": str(column.type),
510 "legacy": _is_legacy(column),
511 }
512 for column in cls.__table__.c # type:ignore[attr-defined] # ty: ignore[missing-attribute]
513 ],
514 }
515 for name, cls in classes.items()
516 ]
517 virtual = [
518 {
519 "name": table.name,
520 "class": "",
521 "doc": table.doc,
522 "virtual": True,
523 "definition": table.select,
524 "columns": [
525 {"name": column.name, "type": str(column.type), "legacy": False}
526 for column in table.columns
527 ],
528 }
529 for table in _virtual_tables()
530 ]
531 return sorted(tables + virtual, key=lambda t: t["name"])