SQLeet Architecture

A complete spreadsheet engine in pure SQLite, designed for Qt QAbstractItemModel (or similar) integration.

File Structure

Total SQLite Backend Size: 3,264 lines (2,539 algorithmically pertinent non-comment lines).

Schema Complexity: 145 top-level SQLite statements:

• 33 Tables
• 42 Views
• 52 Triggers
• 18 Indexes

Load order (each file layers on the previous):

spreadsheet.sql → views.sql → triggers.sql → operations.sql → textsheet.sql

textsheet.sql is optional: omit it for a grid-only build. The Qt frontend loads all five. Smoke tests: test.sql (grid) and textsheet_test.sql (mixed sheets), each a self-checking PASS/FAIL suite — grep FAIL the output to gate health.

Qt Interface — What the Client Calls

The external code is paper-thin. The entire Qt model boils down to these queries:

data(index, role) → single cell

SELECT value FROM v_data
WHERE sheet_id=:s AND row=:r AND col=:c AND role=:role;

data() → viewport batch (preferred, one query for visible area)

SELECT row, col, display, edit_value, font, bg_color, fg_color, text_align, ...
FROM v_viewport
WHERE sheet_id=:s AND row BETWEEN :r1 AND :r2 AND col BETWEEN :c1 AND :c2;

setData(index, value, role)

INSERT INTO v_set_data(sheet_id, row, col, role, value) VALUES(:s,:r,:c,:role,:val);

setFormula()

INSERT INTO v_set_formula(sheet_id, row, col, formula, formula_sql) VALUES(:s,:r,:c,:f,:fsql);

rowCount() / columnCount()

SELECT row_count, col_count FROM v_model_info WHERE sheet_id=:s;

Structural Operations

INSERT INTO v_op(sheet_id, op, target, count) VALUES(:s, 'insert_row', 5, 1);
INSERT INTO v_op(sheet_id, op, target, count) VALUES(:s, 'delete_col', 3, 2);
INSERT INTO v_copy(sheet_id, start_row, start_col, end_row, end_col) VALUES(...);
INSERT INTO v_paste(sheet_id, target_row, target_col) VALUES(...);
INSERT INTO v_clear(sheet_id, start_row, start_col, end_row, end_col, mode) VALUES(...);
INSERT INTO v_new_workbook(name) VALUES('My Workbook');

Grid Named Ranges (named_ranges)

-- Define (or redefine) a named range; dependent formulas update live.
INSERT INTO v_define_name(sheet_id, name, start_row, start_col, end_row, end_col) VALUES(...);
INSERT INTO v_drop_name(sheet_id, name) VALUES(:s, 'Sales');
SELECT name, a1_notation FROM v_named_range WHERE sheet_ref=:s;   -- list

Names work in formulas both as ranges (=SUM(Sales)) and as scalar values (=B2*Rate). The formula bar shows the name; redefining the range updates dependents, and insert/delete row/col shifts named ranges like everything else. All logic is in SQLite; the Qt client just dispatches these INSERTs and offers a Name Box + Names menu (Define from Selection, Name Manager).

Text Sheet Queries

The Qt side reads v_sheet_meta once per sheet to learn kind/wrap_width/show_grid, then uses the matching views. Grid path is unchanged.

-- Sheet kind dispatch
SELECT kind, wrap_width, show_grid FROM v_sheet_meta WHERE sheet_id=:s;

-- Text viewport (parallel to v_viewport for grids)
SELECT row, col, display, edit_value, item_flags, x_offset, ...
FROM v_text_viewport
WHERE sheet_id=:s AND row BETWEEN :r1 AND :r2 AND col BETWEEN :c1 AND :c2;

-- Text single-cell read
SELECT value FROM v_text_data WHERE sheet_id=:s AND row=:r AND col=:c AND role=:role;

-- Unified read (dispatches on kind internally — works for either sheet type)
SELECT value FROM v_data_all WHERE sheet_id=:s AND row=:r AND col=:c AND role=:role;

-- Text setData (delete/replace/split/append dispatched by trigger on content)
INSERT INTO v_text_set(sheet_id, row, col, role, value) VALUES(:s,:r,:c,:role,:val);

-- Create a text sheet
INSERT INTO v_new_text_sheet(workbook_id, name, position, wrap_width) VALUES(1,'Notes',1,80);

-- Bulk-ingest text (tokenizer + wrap run entirely in SQLite)
INSERT INTO v_text_ingest(sheet_id, content) VALUES(:s, :text);

-- Cross-kind named range (obj_ranges — distinct from grid named_ranges; see below)
INSERT INTO v_define_object(workbook_id, scope_sheet_id, name, target_kind, ref_sheet_id,
       start_row, start_col, end_row, end_col) VALUES(...);   -- grid rect
INSERT INTO v_define_object(workbook_id, scope_sheet_id, name, target_kind, ref_sheet_id,
       start_tok_id, end_tok_id) SELECT 1,2,'Title',1,2, MIN(tok_id), MIN(tok_id)+1
       FROM text_tokens WHERE sheet_id=2;                     -- token span

-- Live embedding (host displays live value of source range, any-kind → any-kind)
INSERT INTO v_embed(host_kind, host_sheet_id, host_row, host_col, src_range_id) VALUES(...);

-- Invalidation/recalc queue (also driven automatically by triggers)
SELECT * FROM v_embed_queue;

flags(index) reads the item_flags column from v_text_viewport (35 = normal, 33 = non-editable ref token). The Qt frontend adds a sheet tab bar (mixed kinds), a Sheet menu (New Grid/Text Sheet), and an Embed menu (Name Selection → v_define_object, Embed Named Range Here → v_embed). A second thin QAbstractTableModel (TextSheetModel) reads v_text_data/v_text_viewport and writes v_text_set; the view swaps models on tab change. Everything else stays in SQLite.

Text Sheets & Cross-Sheet Embedding (textsheet.sql)

A workbook can hold two kinds of sheet in one database: grids (kind=0) and text sheets (kind=1). Every table still keys on sheet_id, so the Qt layer keeps calling the same model views; they dispatch on kind internally.

Two-Level Canonical Model

A text sheet stores canonical paragraphs of tokens:

• Paragraphs — the logical row a newline creates. Each has a stable surrogate para_id and a visible para_ord. Splitting identity from order makes anchors durable: a reorder or split never invalidates an existing para_id.
• Tokens — one space-delimited word/symbol per token, with a stable tok_id and a paragraph-local tok_ord. Spaces are not stored; they are the implicit separator, encoded as the +1 in the width model. Paragraph continuity is preserved by token membership (para_id), so wrapping may move a token to any display row without rewriting content.

Wrap Projection

Wrapping is a backend projection, not a rewrite. A materialized text_wrap cache holds one row per visible token, addressed by paragraph-local display row. The global Qt (row, col) is computed as paragraph.disp_row_start + local_row, where disp_row_start is a maintained prefix sum of paragraph line counts. Storing a paragraph-local row is the key trick: a single-paragraph edit rebuilds only its wrap rows — no renumbering of the whole cache, no PK-collision gymnastics.

The wrap algorithm (v_rewrap_para) is a greedy line-break recursive CTE that walks tokens in order carrying (local_row, disp_col, x_offset). A new line starts when x_offset + width + next_width > wrap_width and the line already has a token (so an over-wide token sits alone rather than looping). It writes text_wrap, updates line_count, recomputes the sheet-wide disp_row_start prefix sum and the sheet's reported extents.

Schema Objects (textsheet.sql)

Generated columns on text_tokens earn their keep:

width INTEGER GENERATED ALWAYS AS (COALESCE(measured_width, length(text) + 1)) STORED,
kind  TEXT    GENERATED ALWAYS AS (CASE WHEN ref_range_id IS NOT NULL THEN 'ref' … END) STORED

width = displayed text width + 1 unit (the +1 is the implicit trailing separator). Default is char-cell/monospace (length+1), fully SQL-native; if Qt has real font metrics it pushes measured_width and the generated column prefers it. STORED so it indexes and never recomputes on read.

Tokenizer

v_text_ingest runs a recursive-CTE scan that splits on space and newline simultaneously, inlined on the recursive column (the recursive table may not be referenced from a subquery). Newlines start paragraphs; space runs collapse to one separator; punctuation rides with its word, so the token stream round-trips to the original line via group_concat(text,' '). Example:

INSERT INTO v_text_ingest(sheet_id, content)
  VALUES(2, 'The quick brown fox, jumped!' || char(10) || 'Second paragraph here.');
-- tokens:  The | quick | brown | fox, | jumped! | Second | paragraph | here.
-- wrap@18: row0 "The quick brown"  row1 "fox, jumped!"
--          row2 "Second paragraph"  row3 "here."

Inline Live References

A token with ref_range_id set is an embedded ref. Its text is the cached resolved value, so DisplayRole and width/wrap work uniformly. EditRole renders the name of the source range (render-on-read), not the value — the same render-on-read philosophy as v_formula_text for grid formulas. Editing the token detaches it (drops the ref, keeps your literal). Plain tokens never touch the resolver, so ordinary editing stays fast. The delegate marks ref tokens non-editable (item_flags = 33).

Width Strategy

width = COALESCE(measured_width, length(text) + 1) — a STORED generated column. Char-cell by default (SQL-native, deterministic); measured_width lets Qt supply real font-metric advance (the one justified transient transport, since SQLite cannot measure glyphs). Exposed to the delegate via SizeHintRole ("width;1") and x_offset (UserRole 259) for sub-cell painting.

Two Independent Named-Range Systems

SQLeet has two separate named-range systems — do not conflate them:

Grid formulas reference named_ranges only; the embedding system references obj_ranges only. textsheet.sql adds its own INSTEAD OF INSERT triggers on v_op (trg_objrange_{insert,delete}_{row,col}) that shift grid obj_ranges and grid-cell embed hosts on insert/delete row/col, using the exact shift/shrink math operations.sql applies to named_ranges. Text ranges anchor to stable surrogate ids (tok_id/para_id), so other edits never move them; endpoint deletion re-anchors to the nearest surviving neighbour in document order (para_ord, tok_ord).

Live Embedding

embeds is the cross-sheet reference graph: a host location displays the live resolved value of a source obj_ranges span, any-kind → any-kind.

• Invalidation: trg_tok_invalidate_embeds and trg_cell_invalidate_embeds mark dependent embeds dirty when a source token or cell changes.
• Resolution: trg_embed_resolve recomputes v_range_value and pushes the new value to the host, with value-change guards so the resolver writing a token back never loops. A text host receives rewritten text (which reflows its paragraph); a grid host receives computed_value (which the existing display formatter picks up).
• Cycles: v_embed_cycles detects cross-kind cycles. The engine relies on the client to refuse a commit that surfaces a flagged cycle.

-- name a grid range and a text token span, then embed each into the other (live):
INSERT INTO v_define_object(workbook_id,scope_sheet_id,name,target_kind,ref_sheet_id,
       start_row,start_col,end_row,end_col) VALUES(1,1,'Totals',0,1,0,1,1,1);   -- grid B1:B2
INSERT INTO v_define_object(workbook_id,scope_sheet_id,name,target_kind,ref_sheet_id,
       start_tok_id,end_tok_id) SELECT 1,2,'Title',1,2, MIN(tok_id), MIN(tok_id)+1
       FROM text_tokens WHERE sheet_id=2;

INSERT INTO v_embed(host_kind,host_sheet_id,host_row,host_col,src_range_id)
  SELECT 1,2,0,99,(SELECT id FROM obj_ranges WHERE name='Totals');   -- grid range → text token
INSERT INTO v_embed(host_kind,host_sheet_id,host_row,host_col,src_range_id)
  SELECT 0,1,0,3,(SELECT id FROM obj_ranges WHERE name='Title');     -- text range → grid cell

SELECT * FROM v_embed_queue;   -- the invalidation/recalc queue

Observed live propagation: changing grid B2 200→999 updated the embedded ref token to 100 999; editing text token Quarterly→Annual updated grid D1 to Annual report.

Textsheet Index Strategy

Every hot access path is either a clustered PK range scan (viewport) or an equality probe on a covering/partial index (invalidation, resolution, reverse lookup).

Textsheet Trigger Catalog

Materialized vs. Computed Live

Materialized (random-read-hot): width, kind (generated columns), text_wrap, line_count, disp_row_start, embeds.resolved_value.

Computed live (views): v_text_grid, v_text_data, v_range_value, v_named_object, v_embed_queue, v_embed_cycles. Render-on-read keeps derived text correct under structural edits, exactly like v_formula_text for grid formulas.

Known cost: trg_rewrap_para recomputes the whole-sheet prefix sum (O(paragraphs)) on every paragraph edit — a deliberate correctness-first choice. Optimization path: bulk-mode flag to skip the prefix pass during v_text_ingest, then run v_text_rebuild once; and/or revert to incremental delta-bumps now that creation paths set disp_row_start consistently.

Formula Engine — Two Tiers

# Cell recalc flowchart — top-down, explicit placement

# --- Center spine (top → bottom) ---
A: box "Cell Value Changes" fit at (0cm, 12cm)
B: diamond "Trigger fires?" fit at (0cm, 9.8cm)
C: box "Mark dependents dirty" fit at (0cm, 7.6cm)
D: diamond "Formula type?" fit at (0cm, 5.4cm)

arrow from A.s to B.n
arrow from B.s to C.n
arrow from C.s to D.n

# --- Tier branch (3-way split from D) ---
E: box "Tier 1a: Linear eval" "SUM(coeff x ref_value)" fit at (-5.5cm, 2.2cm)
F: box "Tier 1b: Range eval" "SUM/AVG/MIN/MAX/COUNT" fit at (0cm, 2.2cm)
G: box "Tier 2: Client loop" "SELECT * FROM v_recalc_queue" fit at (5.5cm, 2.2cm)

arrow from D.sw down 0.6cm then left until even with E then to E.n \
    "formula_terms" small above
arrow from D.s down 0.6cm then to F.n \
    "formula_ranges" small above
arrow from D.se down 0.6cm then right until even with G then to G.n \
    "formula_tokens" small above

# --- Merge Tier 1a/1b; separate Tier 2 path ---
H: box "UPDATE computed_value," "is_dirty=0" fit at (0cm, -0.6cm)
I: box "Client executes" "pre-built SQL" fit at (5.5cm, -0.6cm)

arrow from E.s to H.n
arrow from F.s to H.n
arrow from G.s to I.n

# --- Recursive trigger feedback loop ---
arrow from H.w left 1.2cm then up 6.8cm then to C.w \
    "recursive_triggers=ON" small above

The client parser is the one piece of non-trivial application logic. It uppercases the formula (so entry is case-insensitive) and classifies it:

1. Tier 1b — a single range aggregate, e.g. =SUM(A1:A10) → formula_ranges: [('SUM', A1:A10)]
2. Tier 1a — a strict linear combination of coeff*ref terms, e.g. =A1+2*B1-C3 → formula_terms: [(A1, +1.0), (B1, +2.0), (C3, -1.0)]
3. Tier 2 — anything else (cell×cell products, division, parentheses, standalone constants): =B2*C2, =A1/B1, =(A1+B1)*2

Tier 1 handles the common cases entirely inside triggers — zero client involvement. A change marks dependents dirty (cell_deps) and recursive_triggers chains the re-evaluation automatically.

Tier 2 is decomposed into an ordered token stream in formula_tokens (cell references + literal chunks). The tokens are the single source of truth:

• computation — formula_sql (a pre-compiled SQL expression) is generated from the tokens; the view v_recalc_queue resolves topological order via recursive CTE, and the client loop is trivial (drainRecalcQueue just executes each returned update_sql until no dirty Tier 2 cells remain)
• display — see render-on-read below
• structural edits — insert/delete shift each token's ref_row/ref_col (just like formula_terms), and regenerate formula_sql, so a Tier 2 formula survives row/column edits

Render-on-read formula text

The human-readable formula (what EditRole / the formula bar shows) is never stored as frozen text — it is derived on read by v_formula_text from the decomposition (formula_ranges / formula_terms / formula_tokens). Because structural ops shift the decomposition, the displayed references stay correct with no extra bookkeeping: inserting a row turns =SUM(D2:D4) into =SUM(D3:D5) and =B2*C2 into =B3*C3 automatically.

The same render-on-read philosophy extends to text sheets: EditRole on a ref token derives the source range name from obj_ranges, not the cached value.

Key SQLite Features Used

Two-Pass Negate Trick

Row/column insertion on WITHOUT ROWID tables can't use ORDER BY in UPDATE (unsupported). SQLeet solves PK collision during shifts with a negate trick:

-- Pass 1: make all affected rows negative (guaranteed unique)
UPDATE cells SET row = -(row + 1) WHERE row >= :target;
-- Pass 2: restore with offset
UPDATE cells SET row = (-row - 1) + :count WHERE row < 0;

This technique is reused throughout textsheet.sql wherever token ordinals need gap-safe shifting (e.g. multi-word setData that re-tokenizes in place).

Query Logging (--log)

Launch with --log (or --log=/path/to/log.db, default sqleet-log.db) to trace every statement on the main connection into a second, ATTACHed database (logdb.query_log). Each row is timestamped and serialized:

action is the app command that prompted the query — nested queries (recalc drain, trigger-driven SQL) inherit the top-level action, so you can see exactly what each statement was doing. Capture uses sqlite3_trace_v2; writes go through a separate connection (a trace callback must not touch the connection it traces). There's a headless --selftest that drives the real model over the real connection (combine with --log to capture a full session).

Smoke Test Results

Run both suites and grep FAIL:

# Grid suite (test.sql)
sqlite3 :memory: ".read spreadsheet.sql" ".read views.sql" ".read triggers.sql" \
  ".read operations.sql" ".read test.sql"

# Mixed-sheet suite (textsheet_test.sql — 25 assertions)
sqlite3 :memory: ".read spreadsheet.sql" ".read views.sql" ".read triggers.sql" \
  ".read operations.sql" ".read textsheet.sql" ".read textsheet_test.sql"

The grid suite also passes unchanged with textsheet.sql loaded — the new v_op triggers only touch the new tables.

Grid suite (test.sql) — all ✅

• ✅ Workbook/sheet creation via v_new_workbook
• ✅ Value entry with auto type detection (text/number/boolean/date)
• ✅ Display text formatting (numbers strip trailing zeros)
• ✅ data() for DisplayRole, BackgroundRole, ForegroundRole, FontRole, ToolTipRole
• ✅ Linear formula evaluation (=A2-B2 → 400)
• ✅ Range aggregate formula (=SUM(A2:A4) → 4500)
• ✅ Tier 2 general expression (=B2*C2 → product) compute and cascade
• ✅ Cascade recalculation (change A2 → C2 and SUM both update)
• ✅ Case-insensitive formula entry (=sum(a1:a4) ≡ =SUM(A1:A4))
• ✅ Render-on-read formula text stays correct after insert (=SUM(D2:D4) → =SUM(D3:D5))
• ✅ Viewport batch query returns all display data in one call
• ✅ Row insertion with correct shifting of all references (terms, ranges, tokens, deps)
• ✅ Copy/paste
• ✅ Clear (values mode)
• ✅ Circular reference detection

Mixed-sheet suite (textsheet_test.sql) — all ✅

• ✅ Sheet kinds: grid (kind=0) and text (kind=1) coexist in one workbook
• ✅ Text ingest with punctuation + newlines; tokenization round-trips losslessly
• ✅ Wrap at wrap_width=18: rowCount/columnCount reflect wrapped extents
• ✅ Role reads: DisplayRole, EditRole, TextAlignRole, BackgroundRole, ForegroundRole, SizeHintRole
• ✅ setData single-word replace in place
• ✅ setData empty string → token deletion
• ✅ setData multi-word → re-tokenizes in place (negate-trick gap)
• ✅ setData past-end → appends new token
• ✅ Paragraph split and join
• ✅ Reflow on wrap_width change
• ✅ Idempotent v_text_rebuild
• ✅ Grid named range (named_ranges) and text token span (obj_ranges) defined independently
• ✅ Bidirectional live embedding with propagation (grid B2 change updates text ref token; text edit updates grid cell)
• ✅ EditRole on a ref token renders the source name (render-on-read), not the cached value
• ✅ Ref-token detach on edit (drops ref, keeps literal)
• ✅ Grid obj_ranges and grid-cell embed hosts track insert/delete row correctly
• ✅ Token-range re-anchor on endpoint deletion (nearest surviving neighbour, not global first/last)
• ✅ Cross-kind cycle detection via v_embed_cycles
• ✅ PRAGMA foreign_key_check / integrity_check clean; no text_edit_scratch leakage