```
███████╗██████╗ ██████╗ ██╗ ██╗██╗███████╗██╗ ██╗
╚══███╔╝██╔══██╗██╔══██╗██║ ██║██║██╔════╝██║ ██║
███╔╝ ██║ ██║██████╔╝██║ ██║██║█████╗ ██║ █╗ ██║
███╔╝ ██║ ██║██╔══██╗╚██╗ ██╔╝██║██╔══╝ ██║███╗██║
███████╗██████╔╝██████╔╝ ╚████╔╝ ██║███████╗╚███╔███╔╝
╚══════╝╚═════╝ ╚═════╝ ╚═══╝ ╚═╝╚══════╝ ╚══╝╚══╝
```
<p align="center">
<a href="https://github.com/MenkeTechnologies/zdbview/actions/workflows/ci.yml"><img src="https://github.com/MenkeTechnologies/zdbview/actions/workflows/ci.yml/badge.svg" alt="CI"></a>
<a href="https://github.com/MenkeTechnologies/zdbview/actions/workflows/release.yml"><img src="https://github.com/MenkeTechnologies/zdbview/actions/workflows/release.yml/badge.svg" alt="Release"></a>
<a href="https://crates.io/crates/zdbview"><img src="https://img.shields.io/crates/v/zdbview.svg" alt="crates.io"></a>
<a href="https://docs.rs/zdbview"><img src="https://docs.rs/zdbview/badge.svg" alt="docs.rs"></a>
<a href="https://github.com/MenkeTechnologies/zdbview/blob/main/LICENSE"><img src="https://img.shields.io/badge/license-MIT-blue.svg" alt="license"></a>
</p>
<p align="center">
<code>[ SYSTEM://STORE_INSPECTOR ]</code><br>
<code>⟦ ONE BINARY, BOTH HALVES OF THE CACHE ⟧</code><br><br>
<strong>Terminal inspector and CRUD editor for rkyv archives and SQLite databases</strong><br>
<em>Built in Rust with <a href="https://github.com/ratatui/ratatui">ratatui</a> + <a href="https://github.com/crossterm-rs/crossterm">crossterm</a></em>
</p>
### [`Read the Docs`](https://menketechnologies.github.io/zdbview/) · [`Engineering Report`](https://menketechnologies.github.io/zdbview/report.html)
---
# zdbview
Terminal inspector and CRUD editor for **rkyv archives** and **SQLite databases**.
One binary opens either kind of file. The file type is detected from the SQLite
header magic (authoritative — a `.db` name whose bytes are not a SQLite header is
treated as binary), falling back to the extension only for files too short to
carry a header.
```
zdbview # no args → recent files + a scan (cached)
zdbview path/to/file.db # SQLite → full CRUD
zdbview path/to/archive.rkyv # rkyv → full CRUD if recognized, else structural
zdbview --sqlite file # force SQLite
zdbview --rkyv file # force rkyv/binary
```
## Install
```sh
brew tap MenkeTechnologies/menketech
brew install zdbview
```
The formula installs the binary, both man pages and the zsh completion, and is
bumped automatically by this repo's `Release` workflow on every `v*` tag.
From [crates.io](https://crates.io/crates/zdbview) — binary only, no man pages
or completion:
```sh
cargo install zdbview
```
From source:
```sh
git clone https://github.com/MenkeTechnologies/zdbview
cd zdbview && cargo build --release
install -m 755 target/release/zdbview /usr/local/bin/
```
SQLite is compiled in (`rusqlite`'s `bundled` feature), so there is no system
library to install. Tagged releases publish prebuilt binaries for macOS
(arm64 + x86_64) and Linux (glibc + static musl, arm64 + x86_64). Homebrew
covers the glibc targets; the static musl tarballs — for Alpine, distroless and
other non-glibc hosts — are attached to the same GitHub release, with their
sha256 sums listed at the bottom of the formula.
## No args — recent files plus a scan
Running `zdbview` with no argument opens a picker of everything it can open:
recently used files first, then whatever a **background scan** finds, so a shard
does not have to be located by hand.
```
┌ zdbview — 127 files (3 recent, scanning… 127 found) ─────────────────────────┐
│ rkyv scripts.rkyv 608 B /Users/me/.awkrs │
│ rkyv scripts.rkyv 760 B /Users/me/.zshrs │
│ rkyv scripts.compat.rkyv 1.3 K /Users/me/.stryke │
│ sqlite compsys.db 187 M /Users/me/.zshrs │
└──────────────────────────────────────────────────────────────────────────────┘
j/k move · / search · Enter open · c scheme · h help · q quit · awkrs script cache (AWKR)
```
The scan runs on its own thread, so the picker is usable immediately and fills in
as results arrive; the title counts what has been found. `/` **filters** the list
by path as you type (`zdbview — 1/127 files /compsys` with everything else gone),
`Enter` keeps the filter, `Esc` clears it, and a second `Esc` quits. Recent rows show their age, scanned rows their size,
and the bottom line names the recognized format of the selected row.
**The walk does not repeat on every start.** A completed scan is saved to
`$XDG_CACHE_HOME/zdbview/scan` (or `~/.cache/zdbview/scan`) and reused for 24
hours, so later starts show the list immediately — measured here, 127 hits take
~550 ms to walk and ~0.4 ms to load back from the 12 KB cache. The title says how
old the saved list is (`scan 3h old · r rescans`); `r` walks again, `R` walks again
after discarding the saved list, and `--rescan` does the same from the command
line. Entries whose file has since disappeared are dropped on load, and `--scan`
roots neither read nor write the cache since they are not the default set.
**Where it looks** — the producers keep their stores in their own home directory
(`~/.zshrs/scripts.rkyv`, `~/.zshrs/compsys.db`, `~/.pythonrs/scripts.rkyv`), so
the default roots are the dot-directories of `$HOME` (most-recently-touched
first), the XDG cache and data directories, the working directory, `$HOME`'s own
files, and finally `~/Library/Application Support` — where a macOS application
keeps its databases (Ableton's `Live Database`, a browser profile, a plugin
index). That last root is walked last because it is the largest, so the cheap
roots are never starved by it. The rest of `~/Library`, VCS and package-manager
caches, and Chromium profile stores are skipped — that is what keeps the list
about your data; reach the rest with `--scan`. The walk is bounded by depth (5
levels) and by a 20 s clock, with a 1M-entry backstop for a pathological tree,
and stops as soon as you pick. Finding a file is never a reason to stop: the
number of hits is not capped, since a cap there would cut the walk mid-root and
lose every root after it.
**What counts as a hit** — the SQLite header magic, or one of the rkyv shard
magics, or a `.rkyv` name (the header-less hash-keyed shards carry no magic).
Nothing else is offered, and files are only read when their extension makes them a
candidate. Ordering puts recognized shards first, then other rkyv archives, then
databases, newest-first within each group.
```sh
zdbview --scan ~/Library --scan /srv # scan these instead of the defaults
zdbview --no-scan # recent files only
zdbview --rescan # ignore the saved scan and walk now
```
Recent files are recorded in `$XDG_CACHE_HOME/zdbview/recent` (or
`~/.cache/zdbview/recent`), most-recent-first. Paths are canonicalized, so the
same file reached through different relative paths or symlinks dedupes to one
entry that moves back to the front on re-open; the list is capped at 50 entries
and written via temp-file-plus-rename, so a concurrent reader never sees a
half-written list. A recent file the scan also finds stays a recent row.
## SQLite — full generic CRUD
SQLite files are self-describing, so every operation works on any database:
- Browse tables (with row counts) and paginated rows.
- `e` — edit the selected cell in place.
- `a` — insert a row using column defaults.
- `d` — delete the selected row (confirm with `y`).
- `:` — open the **SQL editor** (see below).
- `D` — the **database report** (see below).
- `A` — **column statistics** (see below).
- `e` on a blob cell opens the **hex editor**; text cells edit inline.
- `e` also works on the **detail screen**, on the field the arrows select there.
Rows are addressed by `rowid` where there is one, and by the **primary key**
otherwise — so a `WITHOUT ROWID` table is editable too, including one with a
composite key. A keyed row is matched on every key column, so editing `('a','two')`
cannot touch `('b','one')`. Two cases stay read-only and say so: a table with
neither a rowid nor a primary key, and a key column holding a blob (the key is
matched as text).
Identifiers are double-quoted with internal quotes doubled, and edited values are
bound as parameters, so schemas with spaces, keywords or quotes in their names
work unmodified.
A table whose virtual-table module this binary does not have is marked ` ⃰` in the
list rather than left to fail when selected — an FTS index built with a custom
tokenizer, for instance, cannot be read by any other program either.
## Large databases
Nothing the grid does waits on a scan of the whole table. Measured on Ableton's
`Live-files` index — 23 GB, a 300 MB WAL, 6.5M rows in `files` and 88.8M in
`ancestors`:
| open, first page drawn | — | **4 ms** |
| first page under a filter | 4.16 s | **28 ms** |
| step to the next / previous page | grows with the page number | **21 / 36 ms** |
| last page (`G`) | 9.6 s | **1 ms** |
| exact row count behind a filter | 9.2 s, blocking | **1.6 s**, in the background |
Both columns are the development build on the same file, so the ratios are what to
read; a release build is faster on both sides.
Four things get it there.
**Queries run on their own threads.** A page, an exact count and an `n`/`N` search
each have a worker with its own read-only connection, so a scan the user cannot see
never blocks the render thread. Read-only also means browsing a database another
process is writing cannot make zdbview checkpoint its WAL.
**Nothing is cancelled by waiting for it.** Every request carries a generation, and
each connection runs a progress handler that abandons its statement as soon as its
generation is stale. Typing a filter therefore costs one query, not one per key —
no debounce interval to guess at.
**Totals are never scanned for.** The count a page needs is "is there another
page", which one extra row answers for free. Until an exact total arrives the title
says `501+`; the exact figure is counted in the background and fills in. `G` waits
for that count instead of freezing, since only an exact total says which page the
last one is.
**Paging steps by cursor, not by offset.** `LIMIT n OFFSET k` makes SQLite walk and
discard `k` matching rows, so on a filtered grid the cost of a page grows with how
far in it is — the first page of that 270k-match filter took 26 ms and
`OFFSET 269000` took 6.2 s. A step to the neighbouring page asks for the rows after
(or before) a known one instead, which is one index seek whatever the page number,
and the last page is read backwards from the end.
**The exact count uses every core.** One SQLite statement runs on one thread, so
the table is cut into rowid ranges and each is counted on its own connection —
6.1× faster than one statement on that 6.5M-row filter. The cuts are arithmetic:
finding balanced ones means walking the whole index, which cost 5.8 s of cold reads
on that file, more than the count it was meant to speed up. Since rowids are unique
integers, cutting far finer than the worker count and taking ranges one at a time
balances the work without reading anything.
## rkyv — auto-detected key/value CRUD + structural inspection
rkyv archives are **not self-describing**: the format stores no field names or
type tags (https://rkyv.org/format.html), so the schema cannot be recovered from
an *unknown* archive. zdbview handles this with a **format registry**: known
formats are detected by their magic header and decoded to real key/value with a
faithfully-copied, byte-compatible archive type; anything unrecognized falls
back to a raw structural view.
- `0` **Records** — key/value table for a recognized archive: keys on the left,
the selected value's decoded scalar fields plus a hex dump on the right.
Searchable by key. This view is the default whenever a format is recognized.
- `1` **Info** — file size, and (when recognized) the detected format name and
decoded header fields.
- `2` **Strings** — every run of printable text embedded in the archive, with
byte offsets.
- `3` **Hex** — `xxd`-style hex/ascii dump of the raw bytes.
**Recognized formats** (all fusevm-host script/heap caches, rkyv 0.7):
| zshrs script cache | `ZRSC` | magic | script path |
| zshrs autoload cache | `ZRAL` | magic | function name |
| strykelang script cache | `STRY` | magic (native v4 + compat) | script path |
| awkrs script cache | `AWKR` | magic | script path |
| vimlrs script cache | `VIML` | magic | script path |
| elisprs heap-image cache | `ELSP` | magic | script path |
| zshrs canonical shard | `ZSHS` | magic (or validated, unstamped) | `section/key`, `section[i]` |
| zshrs system shard | `ZSHS` | magic (second layout) | entry key |
| pythonrs bytecode cache | *(none)* | validated try-decode | source path |
| rubylang / arb script cache | *(none)* | validated try-decode | u64 content hash |
The canonical shard is the shell's whole captured state for one source root —
aliases, functions, options, bindings, paths — so its records address a section
and a key (`aliases/ll`, `path[0]`, `extras/<sub>/<key>`) rather than one flat
entry map; renames apply to the map sections, lists being positional.
Magic-bearing formats are matched by their header; the header-less hash-keyed
shards (pythonrs, rubylang, arb) are attempted last and gated by rkyv
validation, so an unrelated archive falls through to the structural view rather
than mis-decoding. A format mismatch always surfaces as failed validation →
structural fallback, never silent corruption. Adding a format is one registry
entry: copy its archive type (same rkyv version and features as the producer)
and map its magic (or add a validated try-decode for header-less formats).
### Full CRUD (write-back)
Recognized archives are editable in place, not just readable. In the Records
view:
- `a` — **create** a record (prompt for a key; inserted with an empty value).
- `e` — **update** the selected record's value in a **hex editor** (see below).
- `r` — **rename** a record's key (map-keyed formats).
- `d` — **delete** the record (confirm with `y`).
Every edit deserializes the shard, mutates it, and re-serializes it, then writes
the file back atomically (temp + rename). Re-serialization is **byte-identical**
to what the producing host writes, so the host reads the edited shard normally —
verified by round-tripping every real cache. Edits target a record's stable
identity (map key, or the u64 content hash for the header-less formats), so an
update or delete touches exactly one entry even when several share a display key
(pythonrs stores many records under `<string>`/`<stdin>`). Rename is offered only
for the map-keyed formats; the header-less formats key by a content hash and have
no renameable key.
### Bytecode disassembly (`disasm` feature — on by default)
The script-cache value blobs are `bincode`-encoded `fusevm::Chunk`. The value
pane's **disasm** render mode (`v` cycles to it) decodes the blob and lists ops,
constants, and names using the real `fusevm` types — no vendored copy of the
267-variant opcode enum, so no risk of silently wrong output. bincode is
version-sensitive: disassembly is correct only when the linked `fusevm` version
matches the one that encoded the cache; a mismatch fails loudly (`invalid
variant` / `unexpected EOF`) and you fall back to hex. On by default (`fusevm`
is published on crates.io); disable with `--no-default-features`.
## Keys
| `Tab` | switch focus (table list ↔ rows) |
| `j` / `k`, arrows | move; `←` / `→` change column |
| `gg` / `G` | jump to top / bottom |
| `Enter` | open detail (row / record); focus rows (table list) |
| `/` | **filter** the list/table as you type; `Enter` keeps it, `Esc` clears it |
| `n` / `N` | next / previous match — inside the active filter, in display order |
| `Ctrl-f` / `Ctrl-b`, `PgUp` / `PgDn` | page by a screenful (every view) |
| `e` `a` `d` `:` | edit / add / delete / SQL (SQLite) |
| `E` | edit the cell **as bytes**, whatever it holds (SQLite) |
| `s` | sort by the cursor column (ascending → descending → off) |
| `<` / `>` | move the sort to the previous / next column |
| `a` `e` `r` `d` | create / hex-edit value / rename / delete record (rkyv) |
| `S` | schema view (SQLite) |
| `D` | database report: pragmas, integrity check, foreign-key lint, maintenance (SQLite) |
| `A` | column statistics for the table, with a per-column frequency table (SQLite) |
| `F` | follow the foreign key under the cursor to the row it references (SQLite) |
| `Y` | copy the row as an `INSERT` (SQLite) |
| `0` `1` `2` `3` | Records / Info / Strings / Hex (rkyv) |
| `v` | cycle value render (auto / hex / text / disasm) — detail screen |
| `y` | copy cell / value / key to clipboard (OSC 52) |
| `x` | export table (CSV) / records (JSON) to a file |
| `o` | back to the file list (open another file) |
| `c` | color-scheme chooser |
| `C` | palette editor |
| `h` / `?` | help overlay |
| mouse | wheel scrolls, click selects, right-click selects + opens detail |
| `q` | quit |
| `Esc` | back out of a nested screen; on the first level, back to the file list |
`h` is the help key, not a motion — columns move with `←` / `→`. The overlay keys
(`h`, `c`, `C`) work on every screen, including the recent-files picker.
## Color schemes
The scheme is carried from screen to screen — the picker hands it to the file it
opens and the file hands it back — so opening a file never re-reads it from disk
and cannot land on a different one. Writes to the prefs file are atomic
(temp-plus-rename), because a plain write is briefly empty and another instance
reading at that moment would fall back to the default scheme.
`c` opens the scheme chooser (ported from `iftoprs`): every scheme with a swatch
of its six palette colors, `j`/`k` or the wheel to cycle with live preview,
`Enter` to save, `Esc` to cancel and restore the previous scheme. `C` opens the
palette **editor** — `←`/`→` pick a slot, `↑`/`↓` adjust it by one, `PgUp`/`PgDn`
by sixteen, `Enter` saves the custom palette. Both persist to
`$XDG_CONFIG_HOME/zdbview/prefs` (or `~/.config/zdbview/prefs`) and load on
startup; the editor uses `C` rather than `e`, which edits data everywhere else.
Non-interactively, `--list-themes` prints every scheme with its token and swatch,
and `--theme <token>` overrides the saved scheme for one run:
```sh
zdbview --list-themes # tokens, names, palettes
zdbview data.db --theme blade_runner # this run only, prefs untouched
```
## Help overlay and toasts
`h` (or `?`) opens the keyboard-shortcut overlay: a themed box listing every
binding in three columns, with the section matching what is open — SQLite, rkyv,
or the recent-files picker. Any key or click closes it.
Action results (copy, export, edit, delete, SQL, scheme saved) appear as a
transient **toast** centered above the status bar and dismiss themselves after
three seconds — a port of iftoprs's `StatusMsg` / `draw_status`. The same text
stays in the status bar so it remains readable after the toast fades.
Input prompts (search, SQL, cell/value edit, add/rename) have a movable text
cursor: `←`/`→`, `Home`/`End`, and the readline chords `Ctrl-a`/`Ctrl-e`
(line start/end), `Ctrl-w` (delete word), `Ctrl-u`/`Ctrl-k` (kill to
start/end). Mouse support and the cursor model are ported from `iftoprs`.
## Hex editor
`e` on a record opens a full-screen **hex editor** over that record's value —
ported from [`zmax`](https://github.com/MenkeTechnologies/zmax)'s
`zmax-term/src/ui/hex.rs`. It opens pre-filled with the current bytes, so there
is nothing to retype:
```
plugins/foo.zsh [+] — 40 bytes · cursor 0x00000004 · -- EDIT (hex) --
|-------|--------|
| *(nothing)* | `SELECT`, `INSERT INTO`, `UPDATE`, `DELETE FROM`, `CREATE TABLE` … |
| `select * from ` | the tables, and only the tables |
| `select * from autoloads where na` | **`name`** (the scoped table's column), then `named_dirs` |
| `select * from autoloads a where a.` | `name`, `source`, `offset`, `size`, `body` — the alias resolved |
| `select ` | columns; with no table named yet, from any table |
| `pragma jour` | `journal_mode` |
| `select * from nosuch.` | nothing, rather than a guess |
`FROM`/`JOIN`/`UPDATE`/`INTO` bring tables into scope in the order they are named,
aliases included, so a join offers the left table's columns before the right one's.
Inside a clause the scoped columns lead, then what continues the clause (`AND`,
`ORDER BY`, …), then table names, then the functions. A prefix matching exactly one
candidate is taken without a menu.
### Dot-commands
A line starting with `.` is a dot-command, as in the shell — `Tab` completes them,
and `.help` lists exactly what is implemented:
| `.tables` / `.schema [T]` / `.indexes [T]` | the objects, their `CREATE` statements, a table's indexes with their columns |
| `.dump [T]` | schema and data as replayable SQL (the same writer as `--export sql`) |
| `.databases` / `.attach FILE ALIAS` / `.detach ALIAS` | attached databases, so a statement can join across files |
| `.mode list\|csv\|tsv\|markdown\|line\|insert\|json` | how a result set is rendered; `list` is the grid |
| `.headers on\|off` | column names in redirected output |
| `.output FILE` / `.once FILE` | send results to a file — every result until reset, or just the next one |
| `.timer on\|off` / `.eqp on\|off` | statement timing; plan before each statement |
| `.import FILE TABLE` | load a CSV or TSV (the same reader as `--import`) |
| `.read FILE` | run a file's statements, each one as if typed |
| `.backup FILE` | `VACUUM INTO` a copy |
| `.expert` | index advice for the statement just run (see below) |
| `.recover [FILE]` | salvage rows page by page into a script (see below) |
| `.vacuum` / `.analyze` / `.reindex` | the maintenance statements |
| `.quit` | leave zdbview |
`.expert` is **not** `sqlite3_expert` — that builds candidate indexes and re-plans
against them, and rusqlite exposes no binding for it. This reads the plan the
planner actually produced and the columns the statement compares on, then names an
index for each table the planner chose to scan in full:
```
sql> SELECT id FROM big WHERE note = 'note 5'
sql> .expert
big: full scan — CREATE INDEX "big_note" ON "big"("note");
```
A column is attributed to a table only when no other table in the statement has a
column by that name, so an ambiguous join reports the scan without guessing at the
index.
History persists to `$XDG_CACHE_HOME/zdbview/sql_history` (200 statements, newlines
escaped so one statement stays one line), written temp-plus-rename like the other
appdata.
## Database report (`D`)
`D` opens what `sqlite3` prints across four dot-commands. The pragmas are read
when the screen opens; the two checks and the lint run on a keypress, because both
walk the whole file:
| *(open)* | page size/count, freelist, encoding, journal mode, synchronous, auto-vacuum, schema/user version, application id, foreign keys, derived data size, object counts | `.dbinfo` |
| `i` | `PRAGMA integrity_check` | `.intck` |
| `Q` | `PRAGMA quick_check` | `.intck` (cheap) |
| `f` | foreign keys with no index to serve them | `.lint fkey-indexes` |
| `v` | `VACUUM` — rewrite the file, reclaiming free pages (reports the size change) | `.vacuum` / DB4S "Compact Database" |
| `z` | `ANALYZE` — write `sqlite_stat1` so the planner has statistics | `.analyze` |
| `r` | `REINDEX` — rebuild every index | — |
The lint reads `foreign_key_list`, `index_list` and `index_info` per table and
reports a key only when no index *starts* with the child column, which is the
condition that makes every parent-row change scan the child table. An index on
some other column of the same table does not count.
## Following a foreign key (`F`)
`F` on a foreign-key column jumps to the row it references — the one piece of
navigation a schema gives you for free, and what DB Browser and Datasette both
link. It reads `PRAGMA foreign_key_list`, resolves the parent column (the pragma
leaves it empty when the key targets the parent's primary key), finds the parent
row, and lands on it with the cursor on the key column. A key with no matching
parent row, a `NULL` key and a column that is not a key each say so instead of
moving.
## Column statistics (`A`)
`A` describes every column of the current table — what VisiData's `describe` and
sqlite-utils' `analyze-tables` report:
```
column type rows nulls distinct numeric longest min max mean
id INTEGER 46716 0 46716 46716 5 1 46716 23358.5
name TEXT 46716 0 46716 0 51 _0ad _zzuf —
body BLOB 46716 0 46713 0 552636 <blob … <blob … —
```
`numeric` is the count of cells SQLite actually stores as a number, which is the
only way to see that a column declared `INTEGER` is holding text — a declared type
is an affinity hint, not a constraint. `mean` covers those numeric cells only, so a
text column is not averaged into nonsense.
`Enter` on a column shows its most common values with bars, VisiData's frequency
sheet — one glance says whether a column is skewed:
```
a 2 ########################
b 1 ############
```
Every column costs one pass over the table, and on a real database a wide blob
column costs over a second of it (measured: 1.6s for `count(DISTINCT body)` across
46,716 rows of `~/.zshrs/compsys.db`). So the pass runs on its own connection on a
background thread — the screen opens saying `analyzing …` and fills in when the
work lands, and the UI never blocks.
## Editing blobs
A blob cell has no text form, so `e` on one opens the **hex editor** over its bytes
instead of the line editor, and `^s` writes them back as a blob parameter. Editing
a blob as a string would replace the bytes with their own description. Text,
integer and real cells still edit inline.
`E` forces the hex editor on **any** cell, which is the only way to put binary into
one that is not already a blob: a string starts as its own bytes, a number as its
digits, a `NULL` as nothing at all, and `^s` writes the result back as a blob.
## Import
`--import` loads a CSV (or TSV, by extension) into an existing table — the shell's
`.import`:
```sh
zdbview data.db --import rows.csv --table people
# imported 3 rows into people
```
The header row names the columns, so a file whose columns are ordered differently
from the table still lands correctly, and a header naming a column the table does
not have is an error rather than a silent drop. The whole file goes in one
transaction: a row with the wrong field count rolls all of it back. Values are
inserted as text and left to SQLite's affinity rules, which is what the shell does.
The reader is RFC 4180 — quoted fields may hold the separator, newlines and doubled
quotes, and CRLF or a missing final newline are both accepted.
## Sorting
`s` sorts the row grid by the column under the cursor: ascending, then descending
on a second press, then back to the table's natural `rowid` order on a third.
`<` / `>` move the sort to the previous / next column, keeping the direction. The
sorted column is marked in its header (`qty ▲`) and in the pane title; selecting
another table clears the sort.
Sorting is done in SQL (`ORDER BY "col" ASC|DESC, rowid`), so it orders the
**whole table**, not just the loaded page — numeric columns sort numerically, and
the `rowid` tiebreaker keeps paging stable when the sort column has duplicates.
Search and the `(row N of M)` readout follow the displayed order, so `n` / `N`
step to the next match *on screen* rather than the next by `rowid`.
## Search inside a filter
`/` narrows the list; `n` / `N` then step through matches **within** what is
listed. For the SQLite grid that means the filter travels into the SQL: the search
statement carries both the search `LIKE` and the filter `LIKE`, and the ordinal
that decides which page to jump to counts only listed rows. Without that, a search
under an active filter lands on a hidden row and scrolls to the page it would have
been on unfiltered.
Both halves of that are full scans in the worst case — finding the match, then
counting to it — so a grid search runs on a query thread and the title says
`· searching` while it does. The ordinal is counted on every core, the same way the
total is.
## Filtering
`/` **filters** the current list as you type, the same model as `iftoprs`: only
matching rows stay listed, the title carries the count (`tables 2/3 /user`,
`1/4 keys /alpha`), `Enter` keeps the filter and `Esc` clears it and puts the
cursor back where `/` was pressed. Navigation walks the filtered list, so `j`/`k`
never land on a hidden row.
The list stays live while the pattern is being typed: `↑`/`↓`, `PgUp`/`PgDn`,
`Home`/`End` and `Ctrl-f`/`Ctrl-b` move through the matches with the prompt still
open, so you can narrow and pick in one go. `←`/`→` belong to the pattern's own
text cursor.
For SQLite the filter is a SQL `WHERE` across every column, so it covers the
**whole table** — the row count and paging follow the filter, not just the loaded
page. Such a filter cannot use an index, so it is a full scan by construction; what
keeps it interactive is that the page is fetched without one and the count that does
need one runs in the background (see [Large databases](#large-databases)). A term of
the form `col:value` restricts that term to one column — DB Browser's filter row —
and terms are ANDed:
| `zshrs` | rows where any column contains `zshrs` |
| `cwd:zshrs` | rows whose `cwd` contains `zshrs` |
| `cwd:zshrs line:echo` | both conditions, each on its own column |
| `12:30` | rows containing `12:30` — `12` is not a column, so the token stays one plain term |
Unknown column names stay plain terms, so a filter that happens to contain a colon
keeps working, and each term binds its own parameter rather than being pasted into
the SQL. The table list, the rkyv Records and Strings views, and the file picker
filter the same way; the Hex view is unfiltered (bytes have no rows) and keeps
`/` as a byte search. Matching is case-insensitive substring; the hex byte search
is case-sensitive.
## Write monitor (`w`)
`w` opens a `top` for stores: every shard and database zdbview knows about — the
open file, the recent list and the saved scan — with what is being written to it
right now. It works from the file picker as well as from an open file; it is one
screen shared by both, so the keys and columns are identical.
```
┌ writes — 130 files, 2 active, 70 K in 6s at 29 K/s · sort written ─────────────┐
│kind file size written rate last activity│
│rkyv scripts.rkyv 62 K 62 K 26 K/s 0.0s ▂▃▄▆▇█ │
│sqlite compsys.db 187 M 8.8 K 2.9 K/s 0.0s ▁▁▁▁▁▁ │
│sqlite catalog.db 48 M — — — │
└────────────────────────────────────────────────────────────────────────────────┘
```
| `<` / `>` / `F6` | move the sort to the previous / next column (`s` does `>`) |
| `I` | invert the sort direction |
| click a header | sort by that column; click it again to invert |
| `/` | filter the watched list by path — `Enter` keeps it, `Esc` clears it |
| `p` | pause and resume sampling |
| `+` / `-` | sample faster / slower (100 ms … 5 s, 500 ms default) |
| `Enter` | open the selected file |
| `t` | swap the bottom frame: the log's frames ↔ **bytes written per table** |
| `F` | **walk the log**: every frame, and the rows each one wrote |
| `j` `k`, `PgUp` `PgDn`, `g` `G` | move |
| `w` / `Esc` | back |
Detection is by polling `stat`, not a filesystem notification API: no extra
dependency, identical behaviour on macOS and Linux, and a few hundred `stat` calls
per tick cost less than a frame. The trade is sub-tick resolution — a write that
lands and is undone inside one interval is invisible.
Two things it gets right that a naive size watch does not:
- **SQLite writes land in the `-wal` sidecar first**, often growing it by megabytes
while the database file itself is untouched until a checkpoint. The sidecar is
sampled alongside and its growth attributed to the database, so a busy database
does not read as idle.
- **rkyv shards are rewritten atomically** (temp file plus rename), so they can
shrink. A shrink counts as activity but adds no bytes to the total, and a
checkpoint that moves bytes from WAL to database is not counted twice.
`written` is bytes seen since the monitor opened, `rate` averages the last four
samples so one quiet tick does not read as "stopped", and `activity` is the last 24
samples scaled against the busiest row on screen.
Sorting is by column with a direction, the way htop does it — the sorted column
carries an arrow in its header (`size▼`) and the title repeats it. Each column's
two directions are exact mirrors, and the path breaks ties so rows do not shuffle
between samples. Text columns start ascending, numeric ones descending.
### The WAL frame
Under the table sits a second frame: the selected database's write-ahead log,
newest frame last. A `-wal` holds transactions that are already durable but not
yet folded into the database file, so this is the file's immediate future — the
pages it is about to become.
| `frame` | position in the log |
| `page` | which database page this frame carries |
| `table` | which table or index owns that page (see below) |
| `commit` | `commit` ends a transaction; `stale` means a checkpoint has since rolled the salts, so the frame belongs to a log that is already folded away |
| `db pages` | database size in pages after a commit frame |
The title line counts live frames against total, commits, distinct pages pending
checkpoint, log size, the checkpoint sequence number, and any frames written
since the last commit (an open transaction). Only frame *headers* are read — 24
bytes and a seek each — so tailing a 190 MB log costs what tailing an empty one
does. Selecting an rkyv archive, or a database in `journal_mode=delete`, says so
in the frame rather than sitting blank. Below 12 rows of terminal the frame is
dropped so the table stays readable.
### Which table is being written (`t`)
`t` swaps the bottom frame for a per-table breakdown:
```
┌ tables — 1.1 M across 3 objects of history.db · t for frames ─────────────────┐
│table written rate share │
│history 724 K 148 K/s 64% ################ │
│index history_ts_idx 352 K 72 K/s 31% ######## │
│sqlite_schema 12 K — 1% │
└────────────────────────────────────────────────────────────────────────────────┘
```
This is the thing no other SQLite tool shows: **not how big a table is, but which
one is being written to right now.** It works because a WAL frame header carries
the page number it rewrote, and a page can be traced to the b-tree that owns it:
1. Every table's and index's b-tree is walked from its root page, straight out of
the file, giving a page → owner map (`recover::page_owners`).
2. The database file alone is the *past* in WAL mode — a table created but not yet
checkpointed is not in its schema at all — so the log's newest image of each
page is applied over the file before walking. Without that, every page reads as
unmapped.
3. Each sample reads only the frame headers written since the last one, so the cost
does not grow with the log's length.
4. The map is re-read when the log restarts (a checkpoint moves pages) or when a
frame names a page the map does not cover (the file grew).
`written` is the total since the monitor opened; `rate` is what the *last* sample
attributed, so a table that has stopped reads as idle while its total stays. That
pair is the answer to "which table is being written fastest right now".
Indexes are named as indexes, and a page the map cannot place is shown as
`page N (unmapped)` rather than being folded into a table it might not belong to.
A database in rollback-journal mode gets no breakdown: a journal records the pages
it is *about to* change, not the ones it did.
### Walking the log (`F`)
`F` opens the log itself. A frame does not merely say that a page changed — it
carries the whole page image, so the rows that write put there can be decoded and
shown:
```
┌ 412 frames · 37 commits ────┐┌ what this frame wrote — j/k step · [ ] commits ──┐
│frame page what ││page 6 table leaf history │
│412 2 commit ││commit — the database was 41 pages after this write│
│411 6 history ││ │
│410 9 index hist… ││rowid 118 │
│409 6 history ││ line git push --force-with-lease │
│… ││ ts_ns 1784952602119000000 │
└─────────────────────────────┘└──────────────────────────────────────────────────┘
```
`j`/`k` step one frame (down is *back* in time), `[` and `]` jump a whole
transaction, `g`/`G` go to the newest and oldest frame. Frames belonging to a log a
checkpoint has already folded away are marked `stale`.
`/` filters the log — by a table or index name, by a page number, or by `commit` /
`stale` — and stepping then walks only what is listed, which is what makes a log
with tens of thousands of frames usable. The title carries the count
(`412/9184 frames`), `Esc` clears the filter and a second `Esc` leaves.
The right pane decodes that one page image with the same record reader the recovery
pass uses, labelling values with the table's column names. A value that continues
onto an overflow page says so rather than showing a truncated string — the rest of
it is in a different frame. Pages that are not table leaves (interior nodes,
index pages, overflow) are named as such instead of pretending to hold rows.
This is the only place in zdbview that reads frame *payloads*, so it is on a
keypress and decodes one frame at a time: a 190 MB log holds tens of thousands of
frames and each payload is a whole page.
## Large archives
Opening a big shard used to block: a 382 MB `elisprs` heap image took ~4 s to
scan for strings and ~25 s for rkyv to validate, which reads as a hang. Now the
same file opens in **~213 ms**:
- rkyv validation for anything over 4 MB runs on a background thread. The
structural view (Info / Strings / Hex) is usable immediately and the Records
view appears when the decode lands, with a toast naming the format.
- String extraction is bounded to 20k runs from the first 64 MB. The Info line and
the Strings title say so (`Strings (20000+)`) rather than implying the list is
exhaustive.
## Detail view
`Enter` on a row or record opens a full-screen detail: every field untruncated on
top, and a scrollable value pane below. `v` cycles how the value bytes render —
**auto** (text if it looks textual, else hex), **hex** (`xxd` style), or **text**
(UTF-8). `y` copies the value; the blob is shown raw, so this is how you read a
cell or record value that's too wide for the grid.
## Export
Interactive `x` writes the current table (CSV) or all records (JSON) to a file in
the working directory. Non-interactively, `--export` takes the sqlite3 shell's
output modes, and `--table` restricts it to one table:
```sh
zdbview data.db --export json # object of { table: [rows...] }, all tables
zdbview data.db --export csv # first table as CSV
zdbview data.db --export tsv # .mode tabs (tabs/newlines escaped)
zdbview data.db --export markdown # .mode markdown, columns padded
zdbview data.db --export line # .mode line, one column per line
zdbview data.db --export insert # .mode insert, one INSERT per row (type-exact)
zdbview data.db --export sql # .dump — schema and data, replayable
zdbview data.db --export csv --table users # just that table
zdbview cache.rkyv --export json # recognized records, value blob as hex
```
`--export sql` is `.dump`: every `CREATE` followed by its rows, wrapped in
`PRAGMA foreign_keys=OFF` / `BEGIN` / `COMMIT`. It and `insert` mode both read
values from the database rather than from the grid, so a blob comes out as `x'…'`
and a real keeps its decimal point — the display string for a blob is a
*description* of it (`<blob 2 bytes>`), which is useless in SQL. Everything that
writes SQL uses the exact form; `csv`, `tsv`, `markdown` and `line` show what the
grid shows. Virtual tables are the case that makes a naive dump
unreplayable: `CREATE VIRTUAL TABLE` runs the module's constructor and builds the
shadow tables, which the dump would then try to create a second time. Like the
shell, zdbview registers the virtual table by writing its `sqlite_schema` row
under `PRAGMA writable_schema=ON` and emits the shadow tables itself with
`CREATE TABLE IF NOT EXISTS`, so an FTS5 index survives the round trip and still
answers `MATCH` queries. Verified line-for-line against `sqlite3 .dump` on a
database with an FTS5 table: identical content, differing only in the order
`sqlite_master` is walked.
## Recovery
`--recover` (and `.recover` in the editor) salvages what a file still holds by
reading **pages**, never opening the database — so it works on a file SQLite
refuses. It walks every page rather than only the ones reachable from
`sqlite_master`, which is what brings back rows a corrupt b-tree root has
orphaned:
```sh
zdbview broken.db --recover > recovered.sql
# recovered 400 rows across 1 table
sqlite3 fixed.db < recovered.sql
```
Measured against a 400-row database with its table's root page zeroed — SQLite
answers `database disk image is malformed` and refuses the rows — `sqlite3
.recover` emits 400 inserts and so does this, and replaying the script produces
data identical to the original. Truncated to 20 of its 35 pages, both recover the
same 226 rows.
The parser reads the parts of the [file format](https://sqlite.org/fileformat2.html)
that hold data: the 100-byte header (page size, reserved bytes, page count), table
b-tree pages, cells with their payload and rowid, the overflow chain when a value
does not fit on a page, and the record's serial types. A 20 KB text value spanning
overflow pages comes back whole.
Pages that cannot be attributed to a table — because the schema is damaged, or
because more than one table has that column count — go to a `lost_and_found` table
with the page number each row came from, as the shell's `.recover` does. Every
assumption the pass made is written into the script as a comment:
```sql
-- t: root page 2 is not a readable table page, so its rows are recovered from unreachable pages instead
-- 33 pages unreachable from any root matched t by column count alone
```
A `WITHOUT ROWID` table keeps its rows in an **index b-tree** rather than a table
b-tree, so recovering it needs three page kinds, not one: table leaves (cells carry
a rowid), index leaves (the key record *is* the row), and index interiors — which,
unlike table interiors, carry key payloads of their own, so some rows live there.
Index cells also keep much less of their payload on the page than table cells do
(`((usable-12)*64/255)-23` against `usable-35`), and using the wrong formula
decodes a short record. Measured on a 300-row keyed table with its root zeroed:
`sqlite3 .recover` brings back 298 rows and so does this, replaying to
`key-0000`…`key-0299`.
Rows recovered from an index leaf carry no rowid, so their `INSERT` does not name
`_rowid_` — naming it for a keyed table is an error and the replay would stop.
Ordinary indexes are skipped rather than read as data: their entries duplicate
columns of a table already being recovered, and emitting them would invent rows.
The script replays each `CREATE INDEX` instead. The file is never modified.
## Backup
`--backup` copies a database with `VACUUM INTO`, which is the shell's `.backup`
and the only way to copy a file that may have writers without stopping them. An
existing target is an error rather than an overwrite:
```sh
zdbview ~/.zshrs/history.db --backup /tmp/history-copy.db
# wrote /tmp/history-copy.db (36864 bytes)
```
## Man pages
Installed by the Homebrew formula — `man zdbview` works straight after
`brew install`. From a source checkout:
```sh
man -l man/man1/zdbview.1 # the standard page
man -l man/man1/zdbviewall.1 # the all-in-one reference, zshall-style
# install
cp man/man1/zdbview*.1 /usr/local/share/man/man1/
```
## Zsh completion
The Homebrew formula drops `_zdbview` into Homebrew's `zsh_completion` dir,
which is already on `fpath`. From a source checkout:
```sh
fpath=(/path/to/zdbview/completions $fpath)
autoload -Uz compinit && compinit
```
## Build
```
cargo build
cargo test
```
## License
MIT — see [LICENSE](LICENSE).