dolthub / dolthub/doltlite

sqlite_master is a projection of the prolly catalog, not a stored table

Open
#1,852 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C
Stars
268
Forks
18
Avg merge
2h 27m
Merged PRs (30d)
447

Description

`sqlite_master` in DoltLite is a projection over the prolly catalog, not a stored table. SQLite keeps the schema as ordinary rows in a b-tree that `writable_schema` can edit directly; DoltLite keeps a content-addressed catalog and renders `sqlite_master` from it. Anything that depends on the schema being a concrete, mutable row store diverges.

Documented in README SQLite Compatibility. This issue is the inventory anchor for those gates. It also absorbs #1200 (canonical `CREATE` text / CHECK error spelling), which is the same projection, not a separate engine-gap.

### CREATE text is canonical, not verbatim

After a schema commit, `SELECT sql FROM sqlite_master` is the catalog's canonical `CREATE` statement (whitespace and quoting normalized), not the original DDL spelling. `CHECK` constraint error messages follow that canonical form. Query results and constraint enforcement are unchanged.

```sql
CREATE TABLE t4(x, y,
CHECK (
x+y==11
OR x*y==12
OR -x==y+10
)
);
-- reopen
SELECT sql FROM sqlite_master WHERE name='t4';
-- DoltLite: CREATE TABLE t4(x, y, CHECK( x+y==11 OR x*y==12 OR -x==y+10))
-- SQLite: original multi-line spelling
```

Pinned by `sql.sqlite_master_canonical` in `test/sqlite_compatibility_contract.tsv`.

### A catalog row that is not a valid entry is ignored, not refused

```sql
CREATE TABLE t(a);
PRAGMA writable_schema=1;
INSERT INTO sqlite_master VALUES(NULL,NULL,NULL,NULL,NULL);
-- reopen
SELECT count(*) FROM t; PRAGMA integrity_check;
```
```
DoltLite: 0 ok
stock: Error: malformed database schema (?)
```

The all-NULL row never becomes a catalog entry, so schema load skips it and `integrity_check` sees a self-consistent store. Stock refuses to open. Gated as `capi3c-8.3`, `capi3-8.3`.

### PK-clustered tables have no `sqlite_autoindex` row

```sql
CREATE TABLE t(a TEXT PRIMARY KEY, b);
SELECT count(*) FROM sqlite_master WHERE name LIKE 'sqlite_autoindex%';
```
```
DoltLite: 0 stock: 1
```

There is no separate index structure to name: the primary key *is* the storage order. Those gates cite #1840.

### Row order is canonical, not creation order

Schema commits adopt the canonical catalog, so `sqlite_master` enumerates in canonical order and oids are reassigned. Tests that bind oids or assert creation-order enumeration diverge.

### Things that do NOT diverge

- `rootpage` values for an ordinary schema: both report `t|2 i|3`
- poking `rootpage` to a bogus value: both refuse at reload with `malformed database schema (t) - invalid rootpage`
- rewriting an index's SQL under `writable_schema`: both report `row 1 missing from index i`

Not expected to grow a stored `sqlite_master` b-tree. Verbatim DDL round-trip is incompatible with a history-independent catalog.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the SQLite Compatibility section in README and the cases in test/sqlite_compatibility_contract.tsv, including the gates named in the issue. Trace how the catalog projection is represented in those tests and document only the compatibility expectations supported there. Done means the inventory and contract tests consistently capture the listed divergences without implying a stored sqlite_master table.

Written by the indexing model from the issue text.

Assessment

Tech stack
c, sqlite
Domain
databases, documentation
Issue type
Documentation
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.