sqlite_master is a projection of the prolly catalog, not a stored table
- 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