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

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. 

5 

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. 

14 

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. 

22 

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. 

27 

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""" 

33 

34from __future__ import annotations 

35 

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 

45 

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 

58 

59from .const import DEFAULT_MAX_ROWS 

60 

61_FETCH_CHUNK_SIZE = 200 

62 

63 

64class SqlError(Exception): 

65 """A rejected or failed query - not a bug in the shell itself.""" 

66 

67 

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.""" 

72 

73 arrow: Any 

74 convert: Callable[[Any], Any] | None = None 

75 

76 

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) 

91 

92_EPOCH = datetime(1970, 1, 1, tzinfo=UTC) 

93_MICROSECOND = timedelta(microseconds=1) 

94_UTC_MICROS = na.timestamp("us", timezone="UTC") 

95 

96 

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) 

100 

101 

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 

111 

112 

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) 

119 

120 

121def _column_kind(column: Any, raw: bool) -> _ColumnKind | None: 

122 """How this db_schema column goes out, or None to go by its values. 

123 

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 ) 

154 

155 

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. 

163 

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 

193 

194 

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 

206 

207 

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

225 

226 

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 ) 

250 

251 

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 ) 

260 

261 

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.""" 

266 

267 name: str 

268 doc: str 

269 columns: tuple[Any, ...] 

270 select: str 

271 

272 

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) 

289 

290 

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 ) 

342 

343 

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

347 

348 

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) 

375 

376 

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

385 

386 

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 

412 

413 

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 """ 

421 

422 data: bytes 

423 rowcount: int 

424 truncated: bool 

425 

426 

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. 

436 

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. 

443 

444 The query can use a virtual table, such as `state_history`, as if it 

445 were a real one - see _virtual_tables(). 

446 

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) 

475 

476 

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. 

487 

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"])