Coverage for src/homeassistant_repl/sql.py: 99%
290 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"""The client-side half of `sql` results: a separate, independent SqlResult
2from custom_components/ha_repl_server/sql.py's, sharing nothing but the
3Arrow IPC bytes `.arrow()` produces there and `SqlResult.from_arrow()`
4reads here - see this project's docs/developer/design/design_principles.md
5("maximum freedom for the developer on the client, maximum restrictions on
6the server side"). The server's own job stops at running the query and
7downloading a complete (`max_rows`-capped, not streamed - HA's own tables
8are small enough that lazy/paged loading isn't worth the complexity yet)
9in-memory Arrow dataset; everything past that - rendering (`.show()`),
10dataframe conversion (`.to_pandas()`/`.to_polars()`), CSV export - runs
11here, against that already-downloaded data, so installing pandas/polars
12(or anything else) is a `pip install` in your own environment, never a
13change to the running Home Assistant instance.
15Once downloaded, a SqlResult behaves like a wrapped local database handle
16in its own right (think `sqlite3`'s cursor, except the storage underneath
17is an Arrow buffer, not a `.db` file) - every operation below (slicing,
18`.project()`, `.sample()`, iteration, ...) works off that one in-memory
19copy, no further round trip to Home Assistant.
20"""
22from __future__ import annotations
24import base64
25import csv
26import dataclasses
27import io
28import json
29import random
30import re
31from collections.abc import Iterator
32from dataclasses import dataclass, field
33from datetime import UTC, datetime, timedelta
34from pathlib import Path
35from typing import Any
37import nanoarrow as na
38from nanoarrow.ipc import StreamWriter
39from rich.table import Table
41from .client import HaReplError
43DEFAULT_MAX_ROWS = 1000
44DEFAULT_SHOW_ROWS = 30
47class SqlError(Exception):
48 """A rejected or failed query - not a bug in the shell itself."""
51@dataclass(frozen=True)
52class SqlColumn:
53 """One column of a recorder table: its name and SQL type, as text."""
55 name: str
56 type: str
57 legacy: bool = False
58 """True for a column Home Assistant still has in the table but no
59 longer writes to (declared UNUSED_LEGACY_* in the recorder's
60 db_schema) - its values have moved elsewhere, e.g. `states.entity_id`
61 to `states_meta`."""
63 def __repr__(self) -> str:
64 return f"Column({self.name!r}, {self.type}{', legacy' if self.legacy else ''})"
67@dataclass(frozen=True)
68class SqlTable:
69 """A recorder table, as described by the server - plain data, not a
70 live handle on anything."""
72 name: str
73 _columns: tuple[SqlColumn, ...]
74 class_name: str = ""
75 """The class Home Assistant's recorder maps to this table, e.g.
76 `States` - found in homeassistant.components.recorder.db_schema."""
77 description: str = ""
78 """That class's docstring."""
79 legacy: bool = False
80 """Whether `columns` and `column_names` take in the legacy columns
81 as well as those in use - as `sql.table(name, legacy=True)` gives."""
83 @property
84 def column_names(self) -> list[str]:
85 """Just the names, in schema order - what a `select *` result
86 shows, legacy columns among them only if `legacy`."""
87 return [column.name for column in self.columns]
89 @property
90 def columns(self) -> list[SqlColumn]:
91 """Name and type of each column, in schema order - legacy ones
92 among them only if `legacy`."""
93 return [c for c in self._columns if self.legacy or not c.legacy]
95 @property
96 def virtual(self) -> bool:
97 """Whether this is a virtual table - a ready-made join to query
98 like a table - rather than a real table in the database."""
99 return False
101 def __repr__(self) -> str:
102 return f"Table({self.name!r}, columns={self.column_names!r})"
105@dataclass(frozen=True, repr=False)
106class SqlVirtualTable(SqlTable):
107 """A virtual table: a ready-made join the server offers under this
108 name, to query as if it were a table. It is not a table in the
109 database - `state_history`, say, is `states` with each row's
110 `entity_id` from `states_meta` - and has no class in db_schema."""
112 definition: str = ""
113 """The query this name stands for."""
115 @property
116 def virtual(self) -> bool:
117 """Whether this is a virtual table - a ready-made join to query
118 like a table - rather than a real table in the database."""
119 return True
121 def __repr__(self) -> str:
122 return f"VirtualTable({self.name!r}, columns={self.column_names!r})"
125@dataclass(frozen=True)
126class SqlRow:
127 """One row of a SqlResult, as `result[0]` gives it: its values in
128 column order, plus the column names and table they came from. Reads
129 like a list (`row[0]`, `len(row)`, a `for` loop) and by column name
130 (`row["entity_id"]`).
131 """
133 values: list[Any]
134 column_names: list[str]
135 table: SqlTable | None = None
136 """The recorder table of the result this row is from, if it had one."""
138 def __getitem__(self, key: int | slice | str) -> Any:
139 if isinstance(key, str):
140 try:
141 return self.values[self.column_names.index(key)]
142 except ValueError:
143 raise KeyError(
144 f"no column {key!r} - columns are: {', '.join(self.column_names)}"
145 ) from None
146 return self.values[key]
148 def __iter__(self) -> Iterator[Any]:
149 return iter(self.values)
151 def __len__(self) -> int:
152 return len(self.values)
154 def to_dict(self) -> dict[str, Any]:
155 return dict(zip(self.column_names, self.values, strict=True))
157 def json_data(self) -> list[Any]:
158 """The values as JSON-ready data, as SqlResult.json_data() has them."""
159 return [_json_value(v) for v in self.values]
161 def __repr__(self) -> str:
162 return f"Row({self.to_dict()!r})"
165@dataclass
166class SqlResult:
167 """A query result downloaded from live mode's `sql`, held as the one
168 Arrow struct array it arrived as (one field per column) - nothing is
169 unpacked or copied until a method asks for Python values. Build one
170 with `SqlResult.from_arrow()`.
171 """
173 _data: Any
174 truncated: bool = False
175 table: SqlTable | None = None
176 """The recorder table these columns come from, when exactly one table
177 has them all - None for a join, an aggregate, or a result that didn't
178 come from `sql` in the first place."""
179 legacy: bool = False
180 """Whether legacy columns the query didn't name are part of this
181 result. They are always downloaded, and left out of everything -
182 `show()`, rows, `column_names`, dataframes, exports - until this is
183 set to True, which can be done at any time."""
184 _hidden: frozenset[str] = frozenset()
185 """The columns `legacy` decides on."""
186 query: str = ""
187 """The query this is the result of, as it was given to `sql` - empty
188 for a result that didn't come from `sql`."""
190 @classmethod
191 def from_arrow(
192 cls,
193 data: bytes,
194 *,
195 truncated: bool = False,
196 tables: list[SqlTable] | None = None,
197 legacy: bool = False,
198 hidden: frozenset[str] = frozenset(),
199 query: str = "",
200 ) -> SqlResult:
201 """Rebuild a SqlResult from Arrow IPC stream bytes (the one shared
202 contract with the server that produced them) - a single
203 struct-typed batch, one named field per column. `truncated` isn't
204 itself encoded in the Arrow data (it's metadata about the query,
205 not the rows), so it travels alongside the bytes rather than
206 inside them. `hidden` names the columns left out unless `legacy`,
207 and `query` is the query the bytes are the result of.
208 """
209 batch = na.ArrayStream.from_readable(data).read_all()
210 names = {field.name for field in batch.schema.fields}
211 matches = [t for t in tables or () if names <= {c.name for c in t._columns}]
212 table = matches[0] if len(matches) == 1 else None
213 return cls(batch, truncated, table, legacy, hidden, query)
215 @classmethod
216 def _from_columns(
217 cls, columns: dict[str, Any], rowcount: int, like: SqlResult
218 ) -> SqlResult:
219 """A result over these per-column arrays, sharing their buffers."""
220 schema = na.struct({name: arr.schema for name, arr in columns.items()})
221 batch = na.c_array_from_buffers(
222 schema, length=rowcount, buffers=[], children=list(columns.values())
223 )
224 return dataclasses.replace(like, _data=na.Array(batch))
226 def _shown(self) -> SqlResult:
227 """This result if all its columns are in view, else one over just
228 those that are - which every reading operation works from."""
229 names = self.column_names
230 if len(names) == len(self._data.schema.fields):
231 return self
232 return self.project(names)
234 def arrow(self) -> Any:
235 """The whole result as one Arrow struct array (a `nanoarrow.Array`,
236 one field per column), without copying. It implements the Arrow
237 PyCapsule interface, so Arrow-aware libraries take it directly:
238 `polars.DataFrame(r.arrow())`, `pyarrow.table(r.arrow())`,
239 `pandas.DataFrame.from_arrow(r.arrow())`.
240 """
241 return self._shown()._data
243 def arrow_ipc(self) -> bytes:
244 """The result serialized as an Arrow IPC stream - the format
245 `from_arrow()` reads, and what `polars.read_ipc_stream()` or
246 `pyarrow.ipc.open_stream()` expect, e.g. to save to a file.
247 """
248 shown = self._shown()
249 data = shown._data
250 if data.n_chunks == 1 and data.offset:
251 # The IPC writer can't encode a sliced (offset) array; write a
252 # compact copy of just these rows instead.
253 data = shown._take(range(self.rowcount))._data
254 buf = io.BytesIO()
255 with StreamWriter.from_writable(buf) as writer:
256 writer.write_stream(data)
257 return buf.getvalue()
259 @property
260 def rowcount(self) -> int:
261 return len(self._data)
263 @property
264 def column_names(self) -> list[str]:
265 return [
266 field.name
267 for field in self._data.schema.fields
268 if self.legacy or field.name not in self._hidden
269 ]
271 def to_dicts(self) -> list[dict[str, Any]]:
272 return self._shown()._data.to_pylist()
274 def to_json(self, **kwargs: Any) -> str:
275 """The whole result as one JSON document: `columns` (names),
276 `rows` (a list per row, in column order), `rowcount` and
277 `truncated`. Compact compared to to_dicts() since column names
278 aren't repeated per row. `kwargs` go to json.dumps (indent, ...).
279 """
280 return json.dumps(self.json_data(), **kwargs)
282 def json_data(self) -> dict[str, Any]:
283 """What to_json() serializes, as plain Python data. Binary values
284 become hex strings; anything else JSON has no type for, str()."""
285 return {
286 "columns": self.column_names,
287 "rows": [[_json_value(v) for v in row] for row in self._rows()],
288 "rowcount": self.rowcount,
289 "truncated": self.truncated,
290 }
292 def to_polars(self) -> Any:
293 import polars as pl # type: ignore[import-not-found]
295 return pl.DataFrame(self.arrow())
297 def to_pandas(self) -> Any:
298 import pandas as pd # type: ignore[import-untyped]
300 return pd.DataFrame({
301 name: column.to_pylist() for name, column in self._column_arrays().items()
302 })
304 def __iter__(self) -> Iterator[list[Any]]:
305 """Rows as plain lists, in column order - so a `for` loop or
306 comprehension over a result just works."""
307 for row in self._rows():
308 yield list(row)
310 def show(
311 self,
312 columns: list[str] | None = None,
313 *,
314 max_rows: int | None = DEFAULT_SHOW_ROWS,
315 max_cols: int | None = None,
316 ) -> Table:
317 """A rich Table rendering of this result. Shows the same columns
318 a row of it has (`result[0]`), unless `columns` names the ones to
319 show or `max_cols` caps how many; at most `max_rows` rows.
320 """
321 all_names = self.column_names
322 if columns is not None:
323 names = columns
324 cols_truncated = False
325 elif max_cols is not None and len(all_names) > max_cols:
326 names = all_names[:max_cols]
327 cols_truncated = True
328 else:
329 names = all_names
330 cols_truncated = False
332 if max_rows is not None and self.rowcount > max_rows:
333 row_cap = max_rows
334 rows_truncated = True
335 else:
336 row_cap = self.rowcount
337 rows_truncated = False
339 caption = f"{self.rowcount} row{'' if self.rowcount == 1 else 's'}"
340 if self.truncated:
341 caption += " (truncated)"
342 if cols_truncated:
343 caption += f" ({len(names)}/{len(all_names)} cols)"
344 if rows_truncated:
345 caption += f" (showing first {row_cap} rows)"
347 table = Table(*names, caption=caption)
348 for i, row in enumerate(self._rows(names)):
349 if i >= row_cap:
350 break
351 table.add_row(*("" if v is None else str(v) for v in row))
352 return table
354 def project(self, columns: list[str]) -> SqlResult:
355 """A new SqlResult with just these columns (same rows, same
356 underlying arrays - no data is copied). A legacy column named
357 here is in view in the new result.
358 """
359 arrays = self._all_arrays()
360 projected = self._from_columns(
361 {name: arrays[name] for name in columns}, self.rowcount, self
362 )
363 projected._hidden = self._hidden - set(columns)
364 return projected
366 def __getitem__(self, key: int | slice) -> Any:
367 """One row by position (`r[0]`, `r[-1]`) as a SqlRow, or a
368 contiguous run of rows with standard Python slice notation
369 (`r[:10]`, `r[-1:]`, `r[10:20]`) - a new SqlResult sharing this
370 one's data, nothing copied. For anything more (a step, a filter,
371 a sort) use a dataframe: `r.to_polars()`.
372 """
373 if isinstance(key, int) and not isinstance(key, bool):
374 index = key + self.rowcount if key < 0 else key
375 if not 0 <= index < self.rowcount:
376 raise IndexError(
377 f"row {key} out of range - result has {self.rowcount} "
378 f"row{'' if self.rowcount == 1 else 's'}"
379 )
380 arrays = self._column_arrays()
381 return SqlRow(
382 [column[index].as_py() for column in arrays.values()],
383 list(arrays),
384 self.table,
385 )
386 if not isinstance(key, slice):
387 raise TypeError(
388 "SqlResult takes a row number (result[0]) or a slice "
389 f"(result[:10]), not {key!r}"
390 )
391 start, stop, step = key.indices(self.rowcount)
392 if step != 1:
393 raise ValueError(
394 "SqlResult slices can't have a step - use result.to_polars() "
395 "for anything beyond a contiguous run of rows"
396 )
397 if self._data.n_chunks != 1: # nothing to share: an empty result
398 return self
399 sliced = na.c_array(self._data)[start : max(start, stop)]
400 return dataclasses.replace(self, _data=na.Array(sliced))
402 def __len__(self) -> int:
403 return self.rowcount
405 def sample(self, count: int = 20) -> SqlResult:
406 """A new SqlResult with `count` rows chosen at random, without
407 replacement (capped at the rows actually here). Rows keep their
408 original relative order, only which ones are picked is random.
409 """
410 return self._take(
411 sorted(random.sample(range(self.rowcount), min(count, self.rowcount)))
412 )
414 def _take(self, indices: Any) -> SqlResult:
415 """A new SqlResult holding copies of just these rows, in this order
416 - only the rows asked for are read, not the whole column."""
417 columns = {
418 name: na.array([_stored(column[i].as_py()) for i in indices], column.schema)
419 for name, column in self._all_arrays().items()
420 }
421 return self._from_columns(columns, len(indices), self)
423 def export_csv(self, path: str | Path | None = None, **kwargs: Any) -> Path:
424 """Write this result to a CSV file at `path` (your own machine,
425 not Home Assistant's) - a header row, then every row with each
426 cell through str(). Without a `path` it's named after `.table`
427 ("states.csv"), or "result.csv" when there isn't one. `kwargs` go
428 straight to csv.writer (dialect, delimiter, ...). The path actually
429 written to is returned.
430 """
431 if path is None:
432 path = f"{self.table.name if self.table else 'result'}.csv"
433 path = Path(path)
434 with path.open("w", newline="", encoding="utf-8") as fp:
435 writer = csv.writer(fp, **kwargs)
436 writer.writerow(self.column_names)
437 for row in self._rows():
438 writer.writerow("" if v is None else str(v) for v in row)
439 return path
441 def _column_arrays(self) -> dict[str, Any]:
442 """Each column in view as its own array - views into the struct,
443 not copies."""
444 arrays = self._all_arrays()
445 return {name: arrays[name] for name in self.column_names}
447 def _all_arrays(self) -> dict[str, Any]:
448 """As _column_arrays(), whether or not the column is in view."""
449 children = list(self._data.iter_children())
450 if self._data.n_chunks == 1 and (
451 self._data.offset or any(len(c) != self.rowcount for c in children)
452 ):
453 # A sliced struct keeps its offset/length on itself; its child
454 # arrays still span the original rows, so narrow them to match.
455 start, stop = self._data.offset, self._data.offset + self.rowcount
456 children = [na.Array(na.c_array(c)[start:stop]) for c in children]
457 names = [field.name for field in self._data.schema.fields]
458 return dict(zip(names, children, strict=True))
460 def _rows(self, names: list[str] | None = None) -> Iterator[tuple[Any, ...]]:
461 if names is None:
462 return self._shown()._data.iter_tuples()
463 arrays = self._all_arrays()
464 return zip(*(arrays[name].iter_py() for name in names), strict=True)
466 def __repr__(self) -> str:
467 suffix = " (truncated)" if self.truncated else ""
468 names = self.column_names
469 return (
470 f"<SqlResult {self.rowcount} rows x {len(names)} cols "
471 f"[{', '.join(names)}]{suffix}>"
472 )
475_EPOCH = datetime(1970, 1, 1, tzinfo=UTC)
476_MICROSECOND = timedelta(microseconds=1)
479def _stored(value: Any) -> Any:
480 """A value read out of an Arrow column, as building one takes it: a
481 time as the microseconds since the epoch the server sends it as."""
482 if isinstance(value, datetime):
483 return (value - _EPOCH) // _MICROSECOND
484 return value
487def _json_value(value: Any) -> Any:
488 if value is None or isinstance(value, (str, int, float, bool)):
489 return value
490 if isinstance(value, (bytes, bytearray, memoryview)):
491 return bytes(value).hex()
492 if isinstance(value, datetime): 492 ↛ 494line 492 didn't jump to line 494 because the condition on line 492 was always true
493 return value.isoformat()
494 return str(value)
497class _Unset:
498 """Stands for "use sql.max_rows" - a default None can't, since None
499 is itself a value to pass (no cap). The repr is what help() shows."""
501 def __repr__(self) -> str:
502 return "sql.max_rows"
505_MAX_ROWS_UNSET: Any = _Unset()
508@dataclass
509class SqlTool:
510 """Live mode's `sql`: a local object, not a name on the Home Assistant
511 side. Calling it sends the query over the websocket, downloads the
512 whole (`max_rows`-capped) result as Arrow and hands back a local
513 SqlResult - so `sql(...).show()`, `.to_polars()` and anything else you
514 do with the result runs in this process, with whatever you've
515 installed here.
517 `.max_rows` is this shell's default row cap (set it to change the
518 default for every call after, or to None to remove it entirely) and
519 `.tables` the recorder's tables, real and virtual, to explore the
520 schema without a query
521 - `.table(name)` picks one of them out by name.
522 """
524 client: Any
525 max_rows: int | None = DEFAULT_MAX_ROWS
526 tables: list[SqlTable] = field(default_factory=list)
528 @classmethod
529 async def connect(cls, client: Any) -> SqlTool:
530 """A SqlTool with `.tables` already downloaded. Raises HaReplError
531 if the server has no sql to offer (switched off in the
532 integration's options, or a server too old to have the command)."""
533 reply = await client.call("ha_repl_server/sql_tables")
534 tables: list[SqlTable] = []
535 for t in reply["tables"]:
536 columns = tuple(
537 SqlColumn(c["name"], c["type"], c.get("legacy", False))
538 for c in t["columns"]
539 )
540 # class and doc are absent from a server older than this client
541 described = (t["name"], columns, t.get("class", ""), t.get("doc", ""))
542 if t.get("virtual"):
543 tables.append(
544 SqlVirtualTable(*described, definition=t.get("definition", ""))
545 )
546 else:
547 tables.append(SqlTable(*described))
548 return cls(client, tables=tables)
550 def table(self, name: str, *, legacy: bool = False) -> SqlTable:
551 """The recorder table called `name`, from `.tables` - with its
552 legacy columns too if `legacy`. Raises KeyError, naming the
553 tables there are, if there's no such table."""
554 for table in self.tables:
555 if table.name == name:
556 return dataclasses.replace(table, legacy=True) if legacy else table
557 known = ", ".join(t.name for t in self.tables)
558 raise KeyError(f"no table {name!r} - tables are: {known}")
560 async def __call__(
561 self,
562 query: str,
563 *,
564 max_rows: int | None = _MAX_ROWS_UNSET,
565 legacy: bool = False,
566 raw: bool = False,
567 ) -> SqlResult:
568 """Run a single read-only SELECT against Home Assistant's Recorder
569 database. At most `max_rows` rows are fetched (default: this
570 object's own `.max_rows`) - pass None for no cap at all.
572 A recorder column comes back as what it holds: a `_ts` or
573 datetime column as a datetime in UTC, a context id as its text.
574 With `raw` they are left as the database has them - seconds
575 since the epoch as a float, the id's bytes.
577 Legacy columns - still in a table, no longer written to - are
578 downloaded but kept out of view unless the query names them or
579 `legacy` is True, so a `select *` shows only the columns in use.
580 Setting `.legacy = True` on the result brings them into view."""
581 params: dict[str, Any] = {
582 "query": query,
583 "max_rows": self.max_rows if max_rows is _MAX_ROWS_UNSET else max_rows,
584 }
585 if raw: # left out otherwise, which an older server needs
586 params["raw"] = True
587 try:
588 reply = await self.client.call("ha_repl_server/sql", **params)
589 except HaReplError as err:
590 if err.code != "sql_error":
591 raise
592 raise SqlError(err.detail) from None
593 return SqlResult.from_arrow(
594 base64.b64decode(reply["arrow"]),
595 truncated=reply["truncated"],
596 tables=self._tables_of(query),
597 legacy=legacy,
598 hidden=self._unnamed_legacy(query),
599 query=query,
600 )
602 def _tables_of(self, query: str) -> list[SqlTable]:
603 """The tables a result of this query could be described by: the
604 virtual tables it mentions - going by its words - or, if none, the
605 real ones. A virtual table's columns are also those of the tables
606 it joins, so without this neither could be told from the other."""
607 words = set(re.findall(r"\w+", query.lower()))
608 virtual = [t for t in self.tables if t.virtual and t.name.lower() in words]
609 return virtual or [t for t in self.tables if not t.virtual]
611 def _unnamed_legacy(self, query: str) -> frozenset[str]:
612 """Names of legacy columns of the tables this query mentions that
613 the query doesn't itself mention - going by its words, not a
614 parse. A name that's also a column in use in another of those
615 tables (`entity_id`, in a join of states and states_meta) isn't
616 one of them."""
617 words = set(re.findall(r"\w+", query.lower()))
618 tables = [t for t in self.tables if t.name.lower() in words]
619 in_use = {c.name for t in tables for c in t._columns if not c.legacy}
620 legacy = {c.name for t in tables for c in t._columns if c.legacy}
621 return frozenset(n for n in legacy - in_use if n.lower() not in words)
623 def __repr__(self) -> str:
624 return (
625 f"<sql(query, max_rows=..., raw=False) - default max_rows={self.max_rows!r}; "
626 "see sql.tables, sql.table(name)>"
627 )