IF Commons GitHub

AnyFS SQL: the schema

0004Active

0004-sql-schema · owner if · domain commons

Requirements

FR-001checkpassing

migrate MUST bring an empty database to the layout of a store that merges in one transaction with the version and the facts of the copy written in it (the layout, the version, the trust mode allowlist and whether search is on), and a second run MUST change nothing.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/schema.test.ts

Proved by: anyfs-sql/suite/schema.test.ts

Written in: anyfs-sql/src/physical/schema.ts

Tests: passed: suite/schema.test.ts migrate brings an empty database to the layout of a store that merges with the facts of th

FR-002checkpassing

The version rows and the blobs MUST have no row id and a key whose conflict rule ignores a second row (PRIMARY KEY (id, sum) ON CONFLICT IGNORE and the digest as the key of the blobs, both WITHOUT ROWID), MUST have no unique column, auto-increment or foreign key, and the heads, the names, the siblings and the vector MUST be views; a trigger MUST refuse a row of a replica the database does not trust.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/schema.test.ts

Proved by: anyfs-sql/suite/schema.test.ts

Written in: anyfs-sql/src/physical/schema.ts

Tests: passed: suite/schema.test.ts the version rows and the blobs have no row id and a key whose conflict rule ignores a seco

FR-003checkpassing

A database at a newer version than the library knows, and one of an earlier layout, MUST be refused and left unchanged; a migration that takes something away MUST run only where the caller says so; and a store opened to check the layout MUST refuse a database that is not at the version the library writes.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/schema.test.ts

Proved by: anyfs-sql/suite/schema.test.ts

Written in: anyfs-sql/src/physical/schema.ts

Tests: passed: suite/schema.test.ts a database ahead of the library, and one of an earlier layout, are refused and left as the; passed: suite/schema.test.ts a migration that takes something away runs only where the caller says so

FR-004checkpassing

The index of search MUST be made where the engine has one and search is not off (an FTS5 table for SQLite), MUST be said to exist or not, and PostgreSQL and DuckDB MUST be refused with a sentence until their layouts are made.

Enforced (check): .github: cd commons/anyfs-sql && bun run verify — suite/schema.test.ts

Proved by: anyfs-sql/suite/schema.test.ts

Tests: passed: suite/schema.test.ts the index of search is made where asked (SQLite, PostgreSQL) and not where it is off, and

Acceptance scenarios

  1. Given an empty database, When it is migrated, Then it is at version 3 with the facts of the layout, per FR-001.
  2. Given a dump of the version rows, When a row is replayed twice, Then the second is ignored by the key, per FR-002.
  3. Given a database at version 99, When a store is opened, Then it is refused and nothing changes, per FR-003.
  4. Given DuckDB, When a store is opened, Then it is refused with a sentence, per FR-004.

Out of scope

  • Dropping or renaming a column.
  • The layouts of PostgreSQL and DuckDB.

Edge cases

  • A migration is idempotent where it is run twice (FR-001).

Assumptions

  • The stores are held to the laws of AnyFS (anyfs 0003-anyfs-logical FR-003); a value is only ever a parameter.
  • The layout of the rows is the one the merge of replicas works on (0007-sql-merge); a different layout is a replacement of the layout module (0005-sql-store FR-008) and the migrations.

Open questions

  • None.

Key entities

  • Port, Dialect, Layout, Revision.

Success criteria

SC-001success criterion

Every behaviour is tested on the real engines in memory or in temporary folders, with no network and no real time.

Review & acceptance checklist

  • [x] Every requirement is testable (MUST / MUST NOT), not aspirational
  • [x] No company fact is asserted here
  • [x] Every open item is marked, not silently decided

Titles, headings, exports, tables, columns and requirements. Esc closes.