summaryrefslogtreecommitdiffhomepage
diff options
context:
space:
mode:
-rw-r--r--README.md14
-rw-r--r--ocda-db-format-specification.md317
-rw-r--r--ocda-model.md8
-rw-r--r--org/ocda_specification.org355
-rw-r--r--ssp-format-specification.md10
5 files changed, 686 insertions, 18 deletions
diff --git a/README.md b/README.md
index 0dd5631..4111ced 100644
--- a/README.md
+++ b/README.md
@@ -1,8 +1,12 @@
# sisudoc-abstraction-format
-Specification for the **`.ssp`** text format - a human-readable,
-PEG-parsable serialisation of the in-memory document abstraction
-produced by [sisudoc-spine](https://git.sisudoc.org/sisudoc-spine).
+Specification of the **object-centric document abstraction** that
+[sisudoc-spine](https://git.sisudoc.org/sisudoc-spine) builds from
+SiSU markup, and of the two forms it is written in: **`.ssp`**, text and
+PEG-parsable, and **`.ocda.db`**, sqlite.
+
+The two carry one version number between them, because neither may express
+what the other cannot.
## What this is
@@ -14,8 +18,8 @@ A specification, not a program. The repository ships:
*abstraction* and `.ssp` are told apart.
- [`ssp-format-specification.md`](ssp-format-specification.md) - **the
`.ssp`**: the model written as text.
-- `ocda-db-format-specification.md` - **the `.ocda.db`**: the model written
- as sqlite. To come.
+- [`ocda-db-format-specification.md`](ocda-db-format-specification.md) -
+ **the `.ocda.db`**: the model written as sqlite.
- [`example-autonomous-contract.ssp`](example-autonomous-contract.ssp) -
spine's output for that sample document, complete and unedited.
- [`example-wealth-of-networks.ssp`](example-wealth-of-networks.ssp) -
diff --git a/ocda-db-format-specification.md b/ocda-db-format-specification.md
new file mode 100644
index 0000000..ebeeaa6
--- /dev/null
+++ b/ocda-db-format-specification.md
@@ -0,0 +1,317 @@
+# The `.ocda.db` format
+
+**The object-centric document abstraction, written as sqlite. Format
+version 2.0**, as spine 0.25.0 emits and reads it.
+
+This document specifies *one serialisation*. The model it serialises is
+specified in [`ocda-model.md`](ocda-model.md), which the text serialisation
+shares; read that first if the question is what an object *is* rather than
+how it is stored.
+
+## Status
+
+Derived from the writer, `sisudoc/outputs/io_out/sqlite_ocda_db.d`, and
+checked against the 27 published databases of the project's reference
+collection. Where the two could differ the writer is normative and this
+document is wrong.
+
+## 1. What it is, and what makes it different
+
+One file per **document**, holding **every language of it**: a ten language
+document is one database with ten rows in `documents`, written one language
+at a time into the one file.
+
+Two things distinguish it from the `.ssp`:
+
+- **It carries its own source.** The markup, the images, `conf/document_make`,
+ `pod.manifest` and the `tools/po4a` catalogues are all inside it, in the
+ `files` table. A pod can be written back out of it, and spine does exactly
+ that when asked for an artefact that needs markup. The `.ssp` names its
+ images and carries none of them.
+- **It is queryable without being parsed.** A question about the document is
+ a SELECT, answered from the file with no spine and no reader.
+
+### 1.1 It is built from the `.ssp`
+
+The document is emitted as `.ssp`, **read straight back**, and what comes
+back is what is written. So **the database cannot carry an object property
+the `.ssp` cannot express**, and the two cannot drift. That is also why they
+share one version number: a bump moves both.
+
+The carried *files* are the deliberate exception. They come from the pod
+rather than from the abstraction, each with its digest.
+
+## 2. Version
+
+Declared in band, in the `metadata` table:
+
+```sql
+SELECT key, value FROM metadata WHERE doc_id IS NULL;
+-- build.spine_version | 0.25.0
+-- schema.name | sisu-abstraction-db
+-- schema.version | 2.0
+```
+
+The rule is the model's, and identical to the `.ssp`: the minor part is
+raised when a property is added, the major when one changes meaning or goes
+away, and **a reader checks the major part and refuses what it does not
+know**. See [`ssp-format-specification.md`](ssp-format-specification.md)
+section 2 for the history, which is shared.
+
+## 3. The schema
+
+Eight tables, one virtual table, three views.
+
+```
+ documents id, lang, source_filename, ssp_digest UNIQUE(lang)
+ │ doc_id
+ ├─ metadata doc_id, key, value UNIQUE(doc_id, key)
+ │
+ └─ objects id, doc_id, section, seq, ocn, ... 50 columns
+ UNIQUE(doc_id, section, seq)
+ ├─ object_images object_id, seq, name, bytes, sha256,
+ │ width, height, missing
+ ├─ object_links object_id, seq, url
+ ├─ object_anchors object_id, seq, anchor
+ └─ object_subtoc object_id, seq, entry
+
+ files id, role, name, bytes, sha256, width, height,
+ compression, data UNIQUE(role, name)
+
+ objects_fts fts5 over objects.text, external content over objects.id
+```
+
+### 3.1 `doc_id` is the language key
+
+`documents.id`, referred to as `doc_id` everywhere else, is **what tells one
+language's rows from another's**. It is the key to reach for in every query.
+
+`objects.lang` and `objects.meta_lang` are **properties of the object** and
+are not it. They are almost always empty (section 6).
+
+### 3.2 The file-level metadata rows
+
+`metadata` is keyed `UNIQUE(doc_id, key)` and not on key alone, because
+every language has a title and a creator and a key-only key would refuse the
+second.
+
+Three rows describe **the file** rather than a document in it, and are
+written with `doc_id` null: `schema.name`, `schema.version` and
+`build.spine_version`. In SQL no null equals another null, so
+`UNIQUE(doc_id, key)` does not constrain them, and a partial unique index
+over `key` alone `WHERE doc_id IS NULL` is what makes them singular.
+
+### 3.3 Fixed-width arrays are JSON
+
+`children`, `ancestors`, `ancestors_collapsed`, `dom_status`,
+`dom_status_collapsed`, `heading_ancestors_text`, `table_widths` and
+`table_aligns` are stored as **JSON arrays**, with validity checks, so that
+`json_extract`, `json_each` and `json_array_length` reach inside them.
+
+The open-ended lists are **rows in the four child tables** instead of
+columns: images, links, anchor tags and sub-table-of-contents entries. Each
+child table carries `seq`, which preserves the order they appeared in.
+
+### 3.4 Indexes
+
+`objects(doc_id, section)`, `objects(ocn)`, `objects(parent_ocn)`,
+`objects(is_a)`, `objects(heading_level)` where it is a heading, and
+`files(role)`, `object_images(name)`.
+
+## 4. The relationship to the `.ssp`, column by column
+
+**Nothing is lost in either direction.** Every `.ssp` property is a column
+or a child table; every column is a property, a value from the object
+declaration line, or a database key.
+
+Where the names differ they differ for a reason, the `.ssp` preferring the
+short form and the database the explicit one:
+
+| `.ssp` property | database |
+|--------------------|-----------------------|
+| `.parent` | `parent_ocn` |
+| `.last_descendant` | `last_descendant_ocn` |
+| `.dummy` | `dummy_heading` |
+| `.ocn_off` | `object_number_off` |
+| `.part` | `is_of_part` |
+| `.section` | `is_of_section` |
+
+Two are **expanded**, the `.ssp` folding what the database spreads:
+
+| `.ssp` property | database |
+|---------------------------------------------------------|-----------------------------------------------------------------------------------|
+| `.indent: <base> <hang>` | `indent_base`, `indent_hang` |
+| `.has: links notes_reg notes_star images images_no_dim` | `has_links`, `has_notes_reg`, `has_notes_star`, `has_images`, `has_images_no_dim` |
+
+The four **repeatable** properties become the four child tables:
+
+| `.ssp` property | database |
+|-----------------|------------------|
+| `.image` | `object_images` |
+| `.stow_link` | `object_links` |
+| `.anchor_tag` | `object_anchors` |
+| `.lev4_subtoc` | `object_subtoc` |
+
+And four columns come from outside the property list: `ocn`, `is_a` and
+`heading_level` from the object declaration line, and `text` from its `|`
+lines. `id`, `doc_id` and `seq` are the database's own keys; `seq` is the
+object's position within its section, and `(doc_id, section, seq)` is what
+identifies an object when `ocn` will not (see
+[`ocda-model.md`](ocda-model.md) section 3).
+
+## 5. The `files` table: what it carries, and how
+
+| role | named by | `compression` | `data` holds |
+|------------|----------------------------|---------------|------------------|
+| `image` | its bare filename | `none` | the file as read |
+| `source` | `media/text/<lang>/<file>` | `zstd` | a zstd frame |
+| `conf` | `conf/document_make` | `zstd` | a zstd frame |
+| `manifest` | `pod.manifest` | `zstd` | a zstd frame |
+| `tools` | `tools/po4a/...` | `zstd` | a zstd frame |
+
+The `compression` column carries the literal `none` or `zstd`, so a reader
+need not infer it from the role.
+
+Three rules make the file predictable:
+
+1. **Compression is decided by role, never by size.** The same kind of file
+ always stores the same way, so nobody need measure a document to know
+ what its database will look like. A very small file can come out a few
+ bytes larger inside a zstd frame; that is accepted rather than fixed with
+ a threshold, which would make two documents of the same kind store
+ differently for no reason a reader could see.
+2. **Images are not compressed.** png and jpeg are compressed already; zstd
+ gains nothing and costs a decompression on every read.
+3. **`objects.text` is never compressed.** The full text index reads that
+ column directly, and compressing it would stop search working completely
+ rather than subtly.
+
+**`bytes` and `sha256` are always those of the original file**, whatever
+`compression` says. So a digest check, a `digests.txt` line and a rebuilt
+`source.digest` all keep working, and a reader that does not decompress can
+still say what it is looking at.
+
+## 6. Querying it
+
+`sqlite3` is all that is needed. The recipes below were run against the
+published databases.
+
+### 6.1 What is this file
+
+```sql
+SELECT key, value FROM metadata WHERE doc_id IS NULL;
+SELECT id, lang, source_filename FROM documents ORDER BY id;
+SELECT role, count(*), sum(bytes) FROM files GROUP BY role;
+```
+
+### 6.2 The views
+
+| view | gives |
+|------------------|---------------------------------------------------------------------------------------------|
+| `outline` | the headings: lang, section, seq, ocn, both levels, `last_descendant_ocn`, identifier, text |
+| `citable` | every object with an ocn, ordered by ocn, language named |
+| `document_files` | what the file carries: role, name, bytes, sha256, pixel size |
+
+**`outline` and `citable` name the language and order by it first.** A view
+over a file that may hold ten languages and does not say which would read as
+one document and be several.
+
+**A trap in `citable`:** it is `SELECT d.lang AS lang, o.*`, and `objects`
+has a `lang` column of its own, so sqlite renames the second one. The view
+has **`lang`, the document's language, and `lang:1`, the object's own**.
+Quote the second if you want it: `SELECT "lang:1" FROM citable`.
+
+### 6.3 Structure
+
+A heading's subtree is a **range**, not a traversal, because every heading
+carries `last_descendant_ocn`:
+
+```sql
+SELECT count(*) FROM objects o,
+ (SELECT doc_id, ocn, last_descendant_ocn FROM objects
+ WHERE is_a='heading' AND ocn=66) h
+ WHERE o.doc_id = h.doc_id
+ AND o.ocn BETWEEN h.ocn AND h.last_descendant_ocn;
+```
+
+And the path back up, on `parent_ocn`:
+
+```sql
+WITH RECURSIVE up(ocn, parent_ocn, text) AS (
+ SELECT ocn, parent_ocn, text FROM objects WHERE doc_id=1 AND ocn=345
+ UNION ALL
+ SELECT o.ocn, o.parent_ocn, o.text FROM objects o
+ JOIN up ON o.ocn = up.parent_ocn AND o.doc_id=1 AND o.is_a='heading'
+ WHERE up.parent_ocn > 0)
+SELECT ocn, substr(text,1,44) FROM up;
+```
+
+**Keep the `parent_ocn > 0` guard.** A parent of 0 is a real,
+non-substantive parent and not a missing value, and without the guard the
+recursion does not terminate.
+
+### 6.4 Full text
+
+```sql
+SELECT o.ocn, substr(o.text,1,44) FROM objects_fts f
+ JOIN objects o ON o.id = f.rowid
+ JOIN documents d ON d.id = o.doc_id
+ WHERE f.text MATCH 'commons' AND d.lang='en' AND o.ocn > 0
+ ORDER BY rank LIMIT 3;
+```
+
+fts5 syntax is available in full: `MATCH 'peer AND production'`,
+`MATCH '"peer production"'`, `MATCH 'peer NEAR production'`.
+
+The index is **external content** over `objects.id`: the text is indexed,
+not stored twice.
+
+### 6.5 Three things to know before trusting a query
+
+1. **An ocn is not unique within a document**, and `0` is shared by many
+ objects. Key on `objects.id`, or on `(doc_id, section, seq)`.
+2. **`objects.text` is internal markup**, neither the author's markup nor
+ any output. Fine for search and for reading structure, not for display
+ without decoding. The markers are in
+ [`ssp-format-specification.md`](ssp-format-specification.md) section 3.2.
+3. **A multi-language file answers an unfiltered query with every language
+ at once.** A single-language document has one `documents` row, so plain
+ queries read naturally there; anything else wants `WHERE lang='..'` or a
+ join to `documents`.
+
+## 7. Digests, and verifying a file
+
+```
+the markup files ──> metadata source.digest sha256 over every markup file
+ of that language
+the .ssp ──> documents.ssp_digest of the .ssp it was built from
+each object ──> objects.sha256 over the object's text
+each carried file──> files.sha256 over the original bytes
+the spine used ──> metadata build.spine_version
+```
+
+So `.sst -> .ssp -> .ocda.db` is checkable end to end, and
+`spine --ocda-verify=<file>` closes the loop by writing the carried markup
+back out and re-parsing it: **does this spine still make this abstraction
+from the markup this file carries?** It writes nothing and the exit status
+is the answer.
+
+**Only this artefact can answer that from itself alone**, which is the
+strongest sense in which it is the one to work with.
+
+The database's own digest is taken once, when the last language has been
+written, and appears in `digests.txt`. The metadata page links the database
+and claims no hash for it, deliberately: the page is written per language
+while the database is added to per language, so a digest taken there would
+be of a file that no longer exists by the end of the run.
+
+## 8. Conformance
+
+A reader **must** check `schema.version`'s major part and refuse a major it
+does not know; **must** treat `ocn` as not an identity; **must** stop at
+`parent_ocn = 0` when walking upward; and **should** say what it did not
+understand, for the reason given in
+[`ssp-format-specification.md`](ssp-format-specification.md) section 8.2.
+
+A writer **must** keep `bytes` and `sha256` those of the original file
+whatever `compression` says, and **must not** compress `objects.text`.
diff --git a/ocda-model.md b/ocda-model.md
index 1c916ca..f4bf245 100644
--- a/ocda-model.md
+++ b/ocda-model.md
@@ -6,10 +6,10 @@ of any file it is written to.
Two serialisations carry this model, and they carry **one version number
between them**, because neither may express what the other cannot:
-| | |
-|--------------------------------------------------------------|--------------------------------------------|
-| [`ssp-format-specification.md`](ssp-format-specification.md) | the OCDA as text, `.ssp` |
-| `ocda-db-format-specification.md` | the OCDA as sqlite, `.ocda.db` *(to come)* |
+| | |
+|----------------------------------------------------------------------|--------------------------------|
+| [`ssp-format-specification.md`](ssp-format-specification.md) | the OCDA as text, `.ssp` |
+| [`ocda-db-format-specification.md`](ocda-db-format-specification.md) | the OCDA as sqlite, `.ocda.db` |
This document is what they share. It describes objects; they describe
writing objects down.
diff --git a/org/ocda_specification.org b/org/ocda_specification.org
index bcb4b0b..442315a 100644
--- a/org/ocda_specification.org
+++ b/org/ocda_specification.org
@@ -21,9 +21,13 @@
#+BEGIN_SRC md
# sisudoc-abstraction-format
-Specification for the **`.ssp`** text format - a human-readable,
-PEG-parsable serialisation of the in-memory document abstraction
-produced by [sisudoc-spine](https://git.sisudoc.org/sisudoc-spine).
+Specification of the **object-centric document abstraction** that
+[sisudoc-spine](https://git.sisudoc.org/sisudoc-spine) builds from
+SiSU markup, and of the two forms it is written in: **`.ssp`**, text and
+PEG-parsable, and **`.ocda.db`**, sqlite.
+
+The two carry one version number between them, because neither may express
+what the other cannot.
## What this is
@@ -35,8 +39,8 @@ A specification, not a program. The repository ships:
*abstraction* and `.ssp` are told apart.
- [`ssp-format-specification.md`](ssp-format-specification.md) - **the
`.ssp`**: the model written as text.
-- `ocda-db-format-specification.md` - **the `.ocda.db`**: the model written
- as sqlite. To come.
+- [`ocda-db-format-specification.md`](ocda-db-format-specification.md) -
+ **the `.ocda.db`**: the model written as sqlite.
- [`example-autonomous-contract.ssp`](example-autonomous-contract.ssp) -
spine's output for that sample document, complete and unedited.
- [`example-wealth-of-networks.ssp`](example-wealth-of-networks.ssp) -
@@ -186,10 +190,10 @@ of any file it is written to.
Two serialisations carry this model, and they carry **one version number
between them**, because neither may express what the other cannot:
-| | |
-|--------------------------------------------------------------|--------------------------------------------|
-| [`ssp-format-specification.md`](ssp-format-specification.md) | the OCDA as text, `.ssp` |
-| `ocda-db-format-specification.md` | the OCDA as sqlite, `.ocda.db` *(to come)* |
+| | |
+|----------------------------------------------------------------------|--------------------------------|
+| [`ssp-format-specification.md`](ssp-format-specification.md) | the OCDA as text, `.ssp` |
+| [`ocda-db-format-specification.md`](ocda-db-format-specification.md) | the OCDA as sqlite, `.ocda.db` |
This document is what they share. It describes objects; they describe
writing objects down.
@@ -759,6 +763,16 @@ meaningful and is defined per property in the table. Four properties
repeat, appearing once per value rather than carrying a list: `.image`,
`.stow_link`, `.lev4_subtoc` and `.anchor_tag`.
+Two consequences of the order, which a validator needs stated and which
+hold over the whole reference collection:
+
+- **a repeated property's occurrences are consecutive.** They share one
+ position in the order, and nothing is written between them. Checked: no
+ repeat in the collection is separated by another property.
+- **no property follows the text.** The record is declaration, then
+ properties, then text lines, and once a `|` line has been written no
+ further property appears. Checked: 0 occurrences in the collection.
+
In the table, "emitted when" is the condition under which the property
appears at all. Counts are occurrences across the 36 document reference
set, as a guide to which parts of the format are load bearing.
@@ -958,3 +972,326 @@ well, which this format does not.
Each carries, in its opening comments, the command that regenerates it.
#+END_SRC
+
+** ocda-db-format-specification.md
+
+#+HEADER: :tangle ../ocda-db-format-specification.md
+#+BEGIN_SRC md
+# The `.ocda.db` format
+
+,**The object-centric document abstraction, written as sqlite. Format
+version 2.0**, as spine 0.25.0 emits and reads it.
+
+This document specifies *one serialisation*. The model it serialises is
+specified in [`ocda-model.md`](ocda-model.md), which the text serialisation
+shares; read that first if the question is what an object *is* rather than
+how it is stored.
+
+## Status
+
+Derived from the writer, `sisudoc/outputs/io_out/sqlite_ocda_db.d`, and
+checked against the 27 published databases of the project's reference
+collection. Where the two could differ the writer is normative and this
+document is wrong.
+
+## 1. What it is, and what makes it different
+
+One file per **document**, holding **every language of it**: a ten language
+document is one database with ten rows in `documents`, written one language
+at a time into the one file.
+
+Two things distinguish it from the `.ssp`:
+
+- **It carries its own source.** The markup, the images, `conf/document_make`,
+ `pod.manifest` and the `tools/po4a` catalogues are all inside it, in the
+ `files` table. A pod can be written back out of it, and spine does exactly
+ that when asked for an artefact that needs markup. The `.ssp` names its
+ images and carries none of them.
+- **It is queryable without being parsed.** A question about the document is
+ a SELECT, answered from the file with no spine and no reader.
+
+### 1.1 It is built from the `.ssp`
+
+The document is emitted as `.ssp`, **read straight back**, and what comes
+back is what is written. So **the database cannot carry an object property
+the `.ssp` cannot express**, and the two cannot drift. That is also why they
+share one version number: a bump moves both.
+
+The carried *files* are the deliberate exception. They come from the pod
+rather than from the abstraction, each with its digest.
+
+## 2. Version
+
+Declared in band, in the `metadata` table:
+
+```sql
+SELECT key, value FROM metadata WHERE doc_id IS NULL;
+-- build.spine_version | 0.25.0
+-- schema.name | sisu-abstraction-db
+-- schema.version | 2.0
+```
+
+The rule is the model's, and identical to the `.ssp`: the minor part is
+raised when a property is added, the major when one changes meaning or goes
+away, and **a reader checks the major part and refuses what it does not
+know**. See [`ssp-format-specification.md`](ssp-format-specification.md)
+section 2 for the history, which is shared.
+
+## 3. The schema
+
+Eight tables, one virtual table, three views.
+
+```
+ documents id, lang, source_filename, ssp_digest UNIQUE(lang)
+ │ doc_id
+ ├─ metadata doc_id, key, value UNIQUE(doc_id, key)
+ │
+ └─ objects id, doc_id, section, seq, ocn, ... 50 columns
+ UNIQUE(doc_id, section, seq)
+ ├─ object_images object_id, seq, name, bytes, sha256,
+ │ width, height, missing
+ ├─ object_links object_id, seq, url
+ ├─ object_anchors object_id, seq, anchor
+ └─ object_subtoc object_id, seq, entry
+
+ files id, role, name, bytes, sha256, width, height,
+ compression, data UNIQUE(role, name)
+
+ objects_fts fts5 over objects.text, external content over objects.id
+```
+
+### 3.1 `doc_id` is the language key
+
+`documents.id`, referred to as `doc_id` everywhere else, is **what tells one
+language's rows from another's**. It is the key to reach for in every query.
+
+`objects.lang` and `objects.meta_lang` are **properties of the object** and
+are not it. They are almost always empty (section 6).
+
+### 3.2 The file-level metadata rows
+
+`metadata` is keyed `UNIQUE(doc_id, key)` and not on key alone, because
+every language has a title and a creator and a key-only key would refuse the
+second.
+
+Three rows describe **the file** rather than a document in it, and are
+written with `doc_id` null: `schema.name`, `schema.version` and
+`build.spine_version`. In SQL no null equals another null, so
+`UNIQUE(doc_id, key)` does not constrain them, and a partial unique index
+over `key` alone `WHERE doc_id IS NULL` is what makes them singular.
+
+### 3.3 Fixed-width arrays are JSON
+
+`children`, `ancestors`, `ancestors_collapsed`, `dom_status`,
+`dom_status_collapsed`, `heading_ancestors_text`, `table_widths` and
+`table_aligns` are stored as **JSON arrays**, with validity checks, so that
+`json_extract`, `json_each` and `json_array_length` reach inside them.
+
+The open-ended lists are **rows in the four child tables** instead of
+columns: images, links, anchor tags and sub-table-of-contents entries. Each
+child table carries `seq`, which preserves the order they appeared in.
+
+### 3.4 Indexes
+
+`objects(doc_id, section)`, `objects(ocn)`, `objects(parent_ocn)`,
+`objects(is_a)`, `objects(heading_level)` where it is a heading, and
+`files(role)`, `object_images(name)`.
+
+## 4. The relationship to the `.ssp`, column by column
+
+,**Nothing is lost in either direction.** Every `.ssp` property is a column
+or a child table; every column is a property, a value from the object
+declaration line, or a database key.
+
+Where the names differ they differ for a reason, the `.ssp` preferring the
+short form and the database the explicit one:
+
+| `.ssp` property | database |
+|--------------------|-----------------------|
+| `.parent` | `parent_ocn` |
+| `.last_descendant` | `last_descendant_ocn` |
+| `.dummy` | `dummy_heading` |
+| `.ocn_off` | `object_number_off` |
+| `.part` | `is_of_part` |
+| `.section` | `is_of_section` |
+
+Two are **expanded**, the `.ssp` folding what the database spreads:
+
+| `.ssp` property | database |
+|---------------------------------------------------------|-----------------------------------------------------------------------------------|
+| `.indent: <base> <hang>` | `indent_base`, `indent_hang` |
+| `.has: links notes_reg notes_star images images_no_dim` | `has_links`, `has_notes_reg`, `has_notes_star`, `has_images`, `has_images_no_dim` |
+
+The four **repeatable** properties become the four child tables:
+
+| `.ssp` property | database |
+|-----------------|------------------|
+| `.image` | `object_images` |
+| `.stow_link` | `object_links` |
+| `.anchor_tag` | `object_anchors` |
+| `.lev4_subtoc` | `object_subtoc` |
+
+And four columns come from outside the property list: `ocn`, `is_a` and
+`heading_level` from the object declaration line, and `text` from its `|`
+lines. `id`, `doc_id` and `seq` are the database's own keys; `seq` is the
+object's position within its section, and `(doc_id, section, seq)` is what
+identifies an object when `ocn` will not (see
+[`ocda-model.md`](ocda-model.md) section 3).
+
+## 5. The `files` table: what it carries, and how
+
+| role | named by | `compression` | `data` holds |
+|------------|----------------------------|---------------|------------------|
+| `image` | its bare filename | `none` | the file as read |
+| `source` | `media/text/<lang>/<file>` | `zstd` | a zstd frame |
+| `conf` | `conf/document_make` | `zstd` | a zstd frame |
+| `manifest` | `pod.manifest` | `zstd` | a zstd frame |
+| `tools` | `tools/po4a/...` | `zstd` | a zstd frame |
+
+The `compression` column carries the literal `none` or `zstd`, so a reader
+need not infer it from the role.
+
+Three rules make the file predictable:
+
+1. **Compression is decided by role, never by size.** The same kind of file
+ always stores the same way, so nobody need measure a document to know
+ what its database will look like. A very small file can come out a few
+ bytes larger inside a zstd frame; that is accepted rather than fixed with
+ a threshold, which would make two documents of the same kind store
+ differently for no reason a reader could see.
+2. **Images are not compressed.** png and jpeg are compressed already; zstd
+ gains nothing and costs a decompression on every read.
+3. **`objects.text` is never compressed.** The full text index reads that
+ column directly, and compressing it would stop search working completely
+ rather than subtly.
+
+,**`bytes` and `sha256` are always those of the original file**, whatever
+`compression` says. So a digest check, a `digests.txt` line and a rebuilt
+`source.digest` all keep working, and a reader that does not decompress can
+still say what it is looking at.
+
+## 6. Querying it
+
+`sqlite3` is all that is needed. The recipes below were run against the
+published databases.
+
+### 6.1 What is this file
+
+```sql
+SELECT key, value FROM metadata WHERE doc_id IS NULL;
+SELECT id, lang, source_filename FROM documents ORDER BY id;
+SELECT role, count(*), sum(bytes) FROM files GROUP BY role;
+```
+
+### 6.2 The views
+
+| view | gives |
+|------------------|---------------------------------------------------------------------------------------------|
+| `outline` | the headings: lang, section, seq, ocn, both levels, `last_descendant_ocn`, identifier, text |
+| `citable` | every object with an ocn, ordered by ocn, language named |
+| `document_files` | what the file carries: role, name, bytes, sha256, pixel size |
+
+,**`outline` and `citable` name the language and order by it first.** A view
+over a file that may hold ten languages and does not say which would read as
+one document and be several.
+
+,**A trap in `citable`:** it is `SELECT d.lang AS lang, o.*`, and `objects`
+has a `lang` column of its own, so sqlite renames the second one. The view
+has **`lang`, the document's language, and `lang:1`, the object's own**.
+Quote the second if you want it: `SELECT "lang:1" FROM citable`.
+
+### 6.3 Structure
+
+A heading's subtree is a **range**, not a traversal, because every heading
+carries `last_descendant_ocn`:
+
+```sql
+SELECT count(*) FROM objects o,
+ (SELECT doc_id, ocn, last_descendant_ocn FROM objects
+ WHERE is_a='heading' AND ocn=66) h
+ WHERE o.doc_id = h.doc_id
+ AND o.ocn BETWEEN h.ocn AND h.last_descendant_ocn;
+```
+
+And the path back up, on `parent_ocn`:
+
+```sql
+WITH RECURSIVE up(ocn, parent_ocn, text) AS (
+ SELECT ocn, parent_ocn, text FROM objects WHERE doc_id=1 AND ocn=345
+ UNION ALL
+ SELECT o.ocn, o.parent_ocn, o.text FROM objects o
+ JOIN up ON o.ocn = up.parent_ocn AND o.doc_id=1 AND o.is_a='heading'
+ WHERE up.parent_ocn > 0)
+SELECT ocn, substr(text,1,44) FROM up;
+```
+
+,**Keep the `parent_ocn > 0` guard.** A parent of 0 is a real,
+non-substantive parent and not a missing value, and without the guard the
+recursion does not terminate.
+
+### 6.4 Full text
+
+```sql
+SELECT o.ocn, substr(o.text,1,44) FROM objects_fts f
+ JOIN objects o ON o.id = f.rowid
+ JOIN documents d ON d.id = o.doc_id
+ WHERE f.text MATCH 'commons' AND d.lang='en' AND o.ocn > 0
+ ORDER BY rank LIMIT 3;
+```
+
+fts5 syntax is available in full: `MATCH 'peer AND production'`,
+`MATCH '"peer production"'`, `MATCH 'peer NEAR production'`.
+
+The index is **external content** over `objects.id`: the text is indexed,
+not stored twice.
+
+### 6.5 Three things to know before trusting a query
+
+1. **An ocn is not unique within a document**, and `0` is shared by many
+ objects. Key on `objects.id`, or on `(doc_id, section, seq)`.
+2. **`objects.text` is internal markup**, neither the author's markup nor
+ any output. Fine for search and for reading structure, not for display
+ without decoding. The markers are in
+ [`ssp-format-specification.md`](ssp-format-specification.md) section 3.2.
+3. **A multi-language file answers an unfiltered query with every language
+ at once.** A single-language document has one `documents` row, so plain
+ queries read naturally there; anything else wants `WHERE lang='..'` or a
+ join to `documents`.
+
+## 7. Digests, and verifying a file
+
+```
+the markup files ──> metadata source.digest sha256 over every markup file
+ of that language
+the .ssp ──> documents.ssp_digest of the .ssp it was built from
+each object ──> objects.sha256 over the object's text
+each carried file──> files.sha256 over the original bytes
+the spine used ──> metadata build.spine_version
+```
+
+So `.sst -> .ssp -> .ocda.db` is checkable end to end, and
+`spine --ocda-verify=<file>` closes the loop by writing the carried markup
+back out and re-parsing it: **does this spine still make this abstraction
+from the markup this file carries?** It writes nothing and the exit status
+is the answer.
+
+,**Only this artefact can answer that from itself alone**, which is the
+strongest sense in which it is the one to work with.
+
+The database's own digest is taken once, when the last language has been
+written, and appears in `digests.txt`. The metadata page links the database
+and claims no hash for it, deliberately: the page is written per language
+while the database is added to per language, so a digest taken there would
+be of a file that no longer exists by the end of the run.
+
+## 8. Conformance
+
+A reader **must** check `schema.version`'s major part and refuse a major it
+does not know; **must** treat `ocn` as not an identity; **must** stop at
+`parent_ocn = 0` when walking upward; and **should** say what it did not
+understand, for the reason given in
+[`ssp-format-specification.md`](ssp-format-specification.md) section 8.2.
+
+A writer **must** keep `bytes` and `sha256` those of the original file
+whatever `compression` says, and **must not** compress `objects.text`.
+#+END_SRC
diff --git a/ssp-format-specification.md b/ssp-format-specification.md
index 1f54aab..17974a3 100644
--- a/ssp-format-specification.md
+++ b/ssp-format-specification.md
@@ -359,6 +359,16 @@ meaningful and is defined per property in the table. Four properties
repeat, appearing once per value rather than carrying a list: `.image`,
`.stow_link`, `.lev4_subtoc` and `.anchor_tag`.
+Two consequences of the order, which a validator needs stated and which
+hold over the whole reference collection:
+
+- **a repeated property's occurrences are consecutive.** They share one
+ position in the order, and nothing is written between them. Checked: no
+ repeat in the collection is separated by another property.
+- **no property follows the text.** The record is declaration, then
+ properties, then text lines, and once a `|` line has been written no
+ further property appears. Checked: 0 occurrences in the collection.
+
In the table, "emitted when" is the condition under which the property
appears at all. Counts are occurrences across the 36 document reference
set, as a guide to which parts of the format are load bearing.