# The `.ocda.db` format
**The object-centric document abstraction, written as sqlite. Format
version 2.1**, as spine emits and reads it after 0.25.1.
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.1
```
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.2.1 The key prefix carries what the `.ssp` puts in blocks
The `.ssp` has four preamble blocks, `@source`, `@meta`, `@make` and
`@doc_has`. The database has **one** `metadata` table, and tells them apart
by a **prefix on the key**:
| `.ssp` block | database key |
|--------------|-----------------------------------------------------------|
| `@meta` | the key as it stands: `title.main`, `creator.author`, ... |
| `@make` | `make.` + the key: `make.footer`, `make.doc_type` |
| `@doc_has` | `doc_has.` + the key: `doc_has.images`, `doc_has.tables` |
| `@source` | `source.` + the key: `source.language`, `source.digest` |
**A reader must strip the prefix and restore the grouping**, or a writer
over it has to ask `metadata["make.footer"]` from a database and
`make["footer"]` from a `.ssp`, which is exactly the difference the two
serialisations exist not to make.
`source.` also carries two keys with no `@source` counterpart,
`source.filename` and `source.ssp_digest`; the `.ssp` puts the first in its
`% Source:` comment and cannot hold the second, which is a digest *of* it.
Values may carry leading or trailing whitespace here where the `.ssp`
cannot represent it, its `key: value` line being unable to distinguish a
value that begins with a space from the separator. **Strip on load**, as a
reader of the text form necessarily does.
### 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.
> **Parse them as JSON.** A scan that strips `[`, `]`, `,` and `"` looks
> adequate and is wrong on the first escape: the encoder writes `/` as
> `\/`, so `FREE/OPEN-SOURCE SOFTWARE` comes back as
> `FREE\/OPEN-SOURCE SOFTWARE`. All 36 document-languages of the reference
> collection contain at least one, and the defect is invisible to any check
> that does not compare the field itself.
>
> Note also that `table_widths` is an array of **integers except where the
> columns divide evenly**, when it is `[33.3333,33.3333,33.3333]`. A decoder
> that accepts strings and integers and then falls back to a default turns
> a value it could not read into one that looks read.
The **element types**, so that a decoder can be written to accept exactly
what occurs:
| column | elements |
|--------------------------------------------------------------------------------------|-------------------------------------------------------------------|
| `children`, `ancestors`, `ancestors_collapsed`, `dom_status`, `dom_status_collapsed` | integers |
| `table_widths` | integers, or floats where the columns divide evenly |
| `table_aligns` | strings, one of `l` `c` `r` |
| `heading_ancestors_text` | strings, eight of them, empty where that level is not an ancestor |
The `.ssp` writes the first three groups **space separated** and
`heading_ancestors_text` **`|` separated**. The empty slots of the last are
significant: they say which levels a heading sits under, so the separators
carry as much as the text does.
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` |
Three are **expanded**, the `.ssp` folding what the database spreads:
| `.ssp` property | database |
|---------------------------------------------------------|----------------------------------------------------------------------------------------------------------------------------------------------|
| `.indent: ` | `indent_base`, `indent_hang` |
| `.ocn_range: ` | `ocn_range_first`, `ocn_range_last` (2.1: `0` where the object is not a container; a 2.0 database has neither column, and reads as no range) |
| `.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: `0`, or, before 2.1, a poem
sharing its last verse's number (see [`ocda-model.md`](ocda-model.md)
section 3).
### 4.1 Where the two are not symmetric
"Nothing is lost in either direction" is true of the model, and there are
three places where the *files* nonetheless differ. A reader must know all
three, because each of them made two independently written readers disagree.
**1. The database states what the `.ssp` derives.** `identifier` and
`anchor` are written on every row. The `.ssp` omits each where it can be
worked out, the identifier being the ocn and the anchor the segment name
(sections 6.1 and 6.3 of
[`ssp-format-specification.md`](ssp-format-specification.md), section 9 of
[`ocda-model.md`](ocda-model.md)). In `free_culture.lawrence_lessig` that is
**1,314 identifiers and 69 anchors** stated here and absent there. Neither
file is the poorer; a reader that does not derive is.
**2. `translation.ocn_aligned` is here and cannot be in a `.ssp`.** It says
whether the languages of a document share one numbering, and a `.ssp` is one
language. It is therefore a fact about **the file**, not about the document
in it, and it is the one such fact either serialisation carries.
A reader should **keep it out of the document model**, for the same reason
the `schema.*` and `build.*` rows are kept out: a writer over the model must
not be able to tell which serialisation it was handed, and a field that
exists only when the input was a database is exactly that tell. The model
has no place for collection-wide facts yet; when it grows one, this belongs
there.
**3. Whitespace and grouping**, as section 3.2.1 gives them.
## 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//` | `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=` 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`.