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

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. 

14 

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

21 

22from __future__ import annotations 

23 

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 

36 

37import nanoarrow as na 

38from nanoarrow.ipc import StreamWriter 

39from rich.table import Table 

40 

41from .client import HaReplError 

42 

43DEFAULT_MAX_ROWS = 1000 

44DEFAULT_SHOW_ROWS = 30 

45 

46 

47class SqlError(Exception): 

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

49 

50 

51@dataclass(frozen=True) 

52class SqlColumn: 

53 """One column of a recorder table: its name and SQL type, as text.""" 

54 

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

62 

63 def __repr__(self) -> str: 

64 return f"Column({self.name!r}, {self.type}{', legacy' if self.legacy else ''})" 

65 

66 

67@dataclass(frozen=True) 

68class SqlTable: 

69 """A recorder table, as described by the server - plain data, not a 

70 live handle on anything.""" 

71 

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

82 

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] 

88 

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] 

94 

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 

100 

101 def __repr__(self) -> str: 

102 return f"Table({self.name!r}, columns={self.column_names!r})" 

103 

104 

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

111 

112 definition: str = "" 

113 """The query this name stands for.""" 

114 

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 

120 

121 def __repr__(self) -> str: 

122 return f"VirtualTable({self.name!r}, columns={self.column_names!r})" 

123 

124 

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

132 

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

137 

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] 

147 

148 def __iter__(self) -> Iterator[Any]: 

149 return iter(self.values) 

150 

151 def __len__(self) -> int: 

152 return len(self.values) 

153 

154 def to_dict(self) -> dict[str, Any]: 

155 return dict(zip(self.column_names, self.values, strict=True)) 

156 

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] 

160 

161 def __repr__(self) -> str: 

162 return f"Row({self.to_dict()!r})" 

163 

164 

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

172 

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

189 

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) 

214 

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

225 

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) 

233 

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 

242 

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

258 

259 @property 

260 def rowcount(self) -> int: 

261 return len(self._data) 

262 

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 ] 

270 

271 def to_dicts(self) -> list[dict[str, Any]]: 

272 return self._shown()._data.to_pylist() 

273 

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) 

281 

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 } 

291 

292 def to_polars(self) -> Any: 

293 import polars as pl # type: ignore[import-not-found] 

294 

295 return pl.DataFrame(self.arrow()) 

296 

297 def to_pandas(self) -> Any: 

298 import pandas as pd # type: ignore[import-untyped] 

299 

300 return pd.DataFrame({ 

301 name: column.to_pylist() for name, column in self._column_arrays().items() 

302 }) 

303 

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) 

309 

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 

331 

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 

338 

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

346 

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 

353 

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 

365 

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

401 

402 def __len__(self) -> int: 

403 return self.rowcount 

404 

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 ) 

413 

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) 

422 

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 

440 

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} 

446 

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

459 

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) 

465 

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 ) 

473 

474 

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

476_MICROSECOND = timedelta(microseconds=1) 

477 

478 

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 

485 

486 

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) 

495 

496 

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

500 

501 def __repr__(self) -> str: 

502 return "sql.max_rows" 

503 

504 

505_MAX_ROWS_UNSET: Any = _Unset() 

506 

507 

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. 

516 

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

523 

524 client: Any 

525 max_rows: int | None = DEFAULT_MAX_ROWS 

526 tables: list[SqlTable] = field(default_factory=list) 

527 

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) 

549 

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

559 

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. 

571 

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. 

576 

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 ) 

601 

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] 

610 

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) 

622 

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 )