# 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`.