Skip to content

Latest commit

 

History

History
124 lines (95 loc) · 28.1 KB

documentation-database.md

File metadata and controls

124 lines (95 loc) · 28.1 KB

Documentation Database

Reference for documentation.db, the SQLite database backing all in-app help: Tier 1/2 tooltips, plus the Tier 3 web content they link to, served in-process by DocumentationRequestInterceptor with WebServer as the HTTP fallback. Read this before touching anything under localWebServer/, idetooltips/, or plugin-manager/.../documentation/, or before writing/editing SQL against this database.

This is a prebuilt database — CoGo never creates or migrates its schema at runtime (see ADR 0001, exception 3). Every consumer reads it; one, PluginDocumentationManager, also writes rows into it, inserting the tooltips and Tier 3 content a plugin contributes into tables it does not own (see How CoGo talks to this database below). The schema is owned by the separate OfflineDocumentationTools project (the docdb-studio tool); never change it from this repo.

Where it lives

  • Installed path: context.getDatabasePath("documentation.db") (Environment.DOC_DB in common/.../utils/Environment.java), i.e. the app's private databases/ dir.
  • Bundled as an asset and extracted by BundledAssetsInstaller / SplitAssetsInstaller at first-time setup only: InstallationViewModel runs the asset install while no JDK or Android SDK is installed, and returns early once both are. An app update keeps the installed file whatever the new APK bundles, so an install first set up with a version 2 database keeps it, and from documentation version 3 on (ADFA-3305) shows no tooltips; clearing the app's data is what gets it the bundled file. That is accepted for ADFA-3305.
  • Debug override: if /sdcard/Download/documentation.db exists and is newer than the installed copy, DocumentationContentSource (behind both Tier 3 transports) and ToolTipManager swap to it at request time (timestamp-compared per request, not just at startup) — a fast way to test a new database on-device without reinstalling. The comparison is on modification time, and adb push preserves the source file's mtime -- so pushing a database saved earlier than the one already on the device silently does not swap, and the app keeps serving the old one with no error anywhere. Follow a push with adb shell touch /sdcard/Download/documentation.db (this cost real debugging time on ADFA-5153). A version 2 file pushed there now shows no tooltips: this build refuses tooltip tables below version 3, and the log line names the debug database and its path. Convert a copy with OfflineDocumentationTools' migrate_tooltip_schema.py before pushing it. WebServer's debug logging and experiment flags are also file-flag-gated under /sdcard/Download/ (CodeOnTheGo.webserver.debug, CodeOnTheGo.exp, CodeOnTheGo.webserver.cs0).
  • Don't trust a local copy's on-disk schema or row content as ground truth without checking freshness first. Any manually downloaded or debug-override copy is independent of git history — a stale one can have a different schema (e.g. missing UNIQUE(path) or templateId) or be missing rows that already exist in the current, maintained database. A stale copy caused a real near-miss in ADFA-5088: a SQL script validated against it would have silently overwritten curated production tooltip content for several tags. Diff or re-download before authoring SQL against a local copy's state, not just before shipping it.

Schema

A star schema: a large fact table at the center, small dimension tables around it. There are two fact tables — Content (Tier 3 web content) and Tooltips (Tier 1/2 tooltips) — because they serve different lookup patterns.

Tier 3: Content (the fact table)

CREATE TABLE Content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    path TEXT NOT NULL,
    languageID INTEGER NOT NULL,
    content BLOB NOT NULL,
    contentTypeID INTEGER NOT NULL,
    templateId INTEGER,
    FOREIGN KEY (languageID) REFERENCES Languages(id),
    FOREIGN KEY (contentTypeID) REFERENCES ContentTypes(id),
    UNIQUE(path)
);

One row per file the web server can serve (HTML, CSS, JS, image, video, PDF, ...) — 30,000+ rows. Key points:

  • path is the lookup key (indexed via the UNIQUE constraint) and is what WebServer matches the HTTP request path against. Paths carry a short source prefix to avoid collisions between doc sets, e.g. k/index.html (Kotlin) vs j/index.html (Java).
  • content is compressed — Brotli for text-like formats, format-specific compression otherwise (images/video/fonts). ContentTypes.compression says which. In a database declaring MAJOR >= 2, every row with ContentTypes.compression = 'brotli' is compressed against the single shared dictionary in CompressionDictionary (see below): the bulk of them converted in one pass by ADFA-5153, and plugin-contributed Tier 3 rows written that way at install time by PluginDocumentationManager (ADFA-5240). So the compression of a row follows from the database's declared version alone — there is no per-row flag, and none is needed. That matters because the two are not distinguishable by inspection, and only half-distinguishable by decode: attaching no dictionary to a stream that needs one reliably throws (IOException, "corrupted input"), since distances into the dictionary region are out of bounds for any spec-compliant decoder. Attaching the wrong dictionary is not reliable in either direction -- one of a different length, or with nothing valid at the offsets the stream references, usually throws, but one of the same length holding plausible bytes there decodes cleanly to content that is simply wrong (both cases verified in BrotliDictionaryDecodeTest). Never use decode success, or failure, as evidence about which dictionary was used. Content over 1 MB is split across multiple rows: the first row's path is the base path, continuation rows are path-1, path-2, ... (languageId = 1), reassembled by DocumentationContentSource before returning.
  • templateId: 0 (or unset) means content is legacy HTML with presentation baked in (the pre-CMS Release 0/1 format). A positive value means content is JSON facts only, rendered through the matching row in Templates (a Pebble template) — the ongoing move to a proper CMS that de-duplicates presentation across near-identical pages (e.g. sin/cos docs).
  • The UNIQUE(path) constraint rejects any duplicate path, regardless of languageID — a second language for an existing path isn't supported yet (only EN-us currently exists). Getting there needs an upstream schema change to composite uniqueness on (path, languageID) (see Known rough edges below).

Dimensions: Languages(id, value) (4-letter codes, e.g. EN-us); ContentTypes(id, value, compression) (MIME type + compression scheme, ~30 rows).

Tier 1/2: Tooltips (the other fact table)

The shape below is documentation version 3 (ADFA-3305), written by OfflineDocumentationTools' migrate_tooltip_schema.py. Version 2 kept the text in Tooltips (an AUTOINCREMENT table with a separate UNIQUE(categoryId, tag) index) and description/uri in every TooltipButtons row; this build reads and writes neither, and refuses a database declaring less than 3 (DatabaseVersionResolver.requireKeyedTooltips).

CREATE TABLE TooltipTexts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    summary TEXT NOT NULL,
    detail TEXT NOT NULL
);
CREATE TABLE Tooltips (
    id INTEGER NOT NULL,
    categoryId INTEGER NOT NULL,
    tag TEXT NOT NULL,
    PRIMARY KEY (categoryId, tag),
    FOREIGN KEY(categoryId) REFERENCES TooltipCategories(id),
    FOREIGN KEY(id) REFERENCES TooltipTexts(id)
) WITHOUT ROWID;
CREATE TABLE TooltipButtons (
    tooltipId INTEGER NOT NULL,
    buttonNumberId INTEGER NOT NULL,
    descriptionId INTEGER NOT NULL,
    uriId INTEGER NOT NULL,
    PRIMARY KEY (tooltipId, buttonNumberId),
    FOREIGN KEY(tooltipId) REFERENCES TooltipTexts(id),
    FOREIGN KEY(buttonNumberId) REFERENCES TooltipButtonNumbers(id),
    FOREIGN KEY(descriptionId) REFERENCES TooltipButtonDescriptions(id),
    FOREIGN KEY(uriId) REFERENCES TooltipButtonUris(id)
) WITHOUT ROWID;
-- plus TooltipButtonDescriptions(id INTEGER PRIMARY KEY, description) and TooltipButtonUris(id INTEGER PRIMARY KEY, uri)
  • summary is Tier 1 (the initial popup), detail is Tier 2 (after "See more"); both may contain HTML.
  • Looked up by (categoryId, tag), which the IDE stamps on the UI widget that owns the tooltip. Tooltips is keyed on exactly that, so the lookup is a search of the table itself, and the text is one more lookup by id in TooltipTexts. The text lives apart because a WITHOUT ROWID table is an index B-tree: whole rows in its interior pages and any row over 1,002 bytes (at 4 KB pages) spilled to a mostly empty overflow page, which cost 1.18 MB on the 2026-10-18 database.
  • A new tooltip takes its id from TooltipTexts (AUTOINCREMENT, so never one a deleted tooltip had); Tooltips has no rowid, so an insert into it returns none.
  • TooltipCategories(id, category) is a tiny dimension table (four categories at the time of writing).
  • TooltipButtons holds the Tier 3 links shown at the bottom of a Tier 2 tooltip, keyed on (tooltipId, buttonNumberId), so a tooltip's buttons are one range of the key, already in display order. Each distinct description and uri is stored once, in TooltipButtonDescriptions and TooltipButtonUris ("View full documentation" alone used to be stored 43,703 times). A uri should resolve to a Content.path (after stripping ?query/#fragment). Neither lookup table has a UNIQUE index, which would cost about as much again; writers look a string up before inserting it.
  • TooltipButtonNumbers(id) exists only to pin a fixed, manually-assigned display order when a tooltip has multiple Tier 3 links. (Flagged in the source design doc as something worth redoing without a whole extra table.)

Supporting tables

  • DocumentationDatabaseVersion(major, minor, patch, who, comment, changeTime) — the database's own semver (ADFA-5220), replacing the heuristics that used to infer the format from which tables happened to exist. Exactly one row: the version the file is, which populate_db.py replaces rather than appends. Nothing in the DDL enforces that, so both readers (DatabaseVersionResolver.resolveMajorVersion here, database_major_version in docdb-studio) take the newest row rather than the one with the highest major (the app orders by changeTime DESC, rowid DESC, so the greatest changeTime wins and rowid only breaks ties) — a rebuild from an older content set is a downgrade and has to read as one, which MAX(major) would get wrong. The app's reader additionally logs a warning when it finds more than one row; docdb-studio's does not. populate_db.py collapses a file that somehow accumulated several back to one, and resolveMajorVersion returns null for a database predating the table. MAJOR >= 3 says the tooltip tables have the shape above and every template is compressed (see Templates below). MAJOR >= 2 is what tells the app its brotli Content rows are dictionary-compressed; below that, DocumentationContentSource neither reads nor attaches CompressionDictionary. Gating on the declared version rather than on the table's presence matters in both directions: a database can carry the dictionary table while its content is still plain brotli (every brotli row would then be decoded with a dictionary attached and fail outright, since ADFA-5240 removed the plain-decode retry that used to mask this), and a migrated database that lost the table fails loudly instead of quietly.
  • CompressionDictionary(id, data) — single-row table (id INTEGER PRIMARY KEY CHECK (id = 1)) holding the raw Brotli dictionary every ADFA-5153-migrated compression = 'brotli' Content row is compressed against. First trained from a representative sample across the whole Content table by OfflineDocumentationTools' migrate_content_to_dictionary_brotli.py / populate_db.py, and retrained since by its remint_dictionary.py, which recompresses every brotli Content row against the new dictionary in the same transaction (from documentation version 3, every template as well: OfflineDocumentationTools #48). A dictionary-compressed row or template is decodable only against the exact dictionary it was compressed with, so a re-mint is safe only when it carries every one of them across; replacing the dictionary alone would silently orphan them all. Shipping the dictionary inside documentation.db itself, rather than as a separate bundled asset, keeps it version-locked to the content compressed against it. Both the reader and the writer load it through loadCompressionDictionary in :common, so they cannot disagree about whether a given database's brotli rows carry a dictionary — a row compressed against one the reader won't attach is undecodable, and vice versa. DocumentationContentSource loads it lazily -- not at database open or swap time, but on the first content lookup after the active database changes, whatever that row's own compression, and only when DocumentationDatabaseVersion declares MAJOR >= 2 (see above) -- and caches it from then on, reloading again only on the next database change (a swap can bring in a database with a different dictionary or none, so it can't stay cached across one). Each row is then decoded exactly once, with the dictionary attached or not according to that version; there is no retry, because there is no longer a second way a row might have been written.
  • Templates(id, name, content) — Pebble template source. The DDL is id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, content BLOB NOT NULL, UNIQUE('name') — worth stating, because the single-quoted UNIQUE('name') reads like a constant rather than a column and has twice been taken for an absent constraint. SQLite resolves the quoted string to the column, so a duplicate name, a null name and a null body are all rejected by the database, and readers here do not re-check them. Content.templateId names a page's outermost template by id; every reference between templates is by name (ADFA-5405), which is also how WebServer reaches well-known templates like bookshelf, and how the content source reaches helpmanual-jstopic.peb for Help+Manual's jstopics files. So a template can extends, include, import or embed another one by writing its Templates.name verbatim ({% include "nav.peb" %}) — the table is a flat namespace, with no directories, prefixes or suffixes applied. A reference to a name with no row fails the request rather than rendering the name; before ADFA-5405 the latter is exactly what happened, silently, because the engine's loader treated the name it was handed as the template body. From documentation version 3 (ADFA-3305), every content is the template's UTF-8 text Brotli-compressed against CompressionDictionary, like a brotli Content row. No column says so: OfflineDocumentationTools compresses every template when it builds the database for release, and nothing on the device writes Templates. Below 3, and in a database declaring no version, content is plain UTF-8 and is read as it is (expectsCompressedTemplates): an app update never replaces the installed database, so a 2.x file has to keep rendering its templated pages. In a 3.x file a template that does not decode fails the page that uses it, logged with the template's name and the database's path.
  • Bookshelf(contentID, bookCategoryID, title, description) / BookCategories(id, category, description) — the Dynamic Bookshelf: one row per "book" (PDF or similar), linked to its Tier 3 page via contentID -> Content.id. Two DB triggers keep Bookshelf in sync when a PDF row is inserted/deleted from Content; title/description don't come from those triggers and must be set by hand. Non-PDF books need a separate ingestion path (plugin-provided, e.g. via PluginDocumentationManager).
  • LastChange(documentationSet, changeTime, who) — audit trail for edits made through docdb-studio; not shown to end users. DatabaseVersionResolver reads the documentationSet = 'wholedb' row to report the DB's build/edit stamp in debug logging, falling back to the most recent row of any set if 'wholedb' is missing.
  • Misc ide_tooltip_table and PUCC tables are historical/example artifacts — not part of the live lookup paths above.

How CoGo talks to this database

Of the five sites below, only DocumentationContentSource and ToolTipManager open the file, with SQLiteDatabase.openDatabase(..., OPEN_READONLY) — WebServer and the interceptor read through the content source, and PluginDocumentationManager is the one write path, opening OPEN_READWRITE to install plugin content (see ADR 0001 for why raw SQLite is justified here instead of Room).

  • common/.../documentation/DocumentationContentSource.kt — the one Tier 3 pipeline that reads this database: row lookup (a request target is matched raw first, since stored Content.path values are percent-encoded, with a percent-decoded fallback on a miss — shared by both transports so they agree on which pages exist), chunked-row reassembly, dictionary-aware Brotli decode gated on the declared documentation version, and the database swaps — to a newer sdcard debug database, and reopening the installed file when an asset install rewrites it in place under the cached handle — under a read/write lock so several threads can read while a swap cannot close the handle under them. It also renders the rows that are Pebble template contexts (templateId > 0: the k/ and i/ pages in a 2.x database, the i/ ones being Help+Manual's topic pages (ADFA-6618), and the a/ pages too in a 3.x one) — resolving each template's references to other templates against Templates.name as it goes, and decoding each template with the same dictionary as the rows — so both transports below serve finished pages and neither needs the template engine itself. All of that logic exists once (ADFA-5176). One more rule renders a row that isn't stored: Help+Manual's in-page navigation loads jstopics/X.js, a JavaScript copy of topic X, and once the documentation pipeline stores each Help+Manual topic only as the JSON its page X.html renders from (ADFA-6618), a request for jstopics/X.js that matches no stored row, under either its raw or its percent-decoded path, is rendered from that page's JSON with the helpmanual-jstopic.peb template and served as text/javascript. It stays a 404 unless the page is one row rendered through helpmanual-topic.peb and the database has helpmanual-jstopic.peb; a duplicated page row is reported as ambiguous, like the page itself.
  • common/.../documentation/DocumentationRequestInterceptor.kt — serves Tier 3 in-process for the app's WebViews (HelpActivity, the tooltip fragment, FAQActivity), through WebViewClient.shouldInterceptRequest, so a page's assets cost a database read instead of a TCP connection each (ADFA-5176). It matches the same http://localhost:6174/... URL space, so the strings.xml entries, ToolTipManager's link builder and the DocumentationExtension contract need no changes; anything it declines — a /pr/ endpoint, an unknown path, a failed read — falls through to WebServer unchanged. Both transports get their Content-Type charset from the same place, ContentTypeHeaders (ADFA-5241), so a row does not describe itself differently depending on which one served it. They answer Range differently, both validly: the interceptor answers one satisfiable range with a 206 and Content-Range (RangeResponse, ADFA-3305), because the WebView cuts an in-process body to the range itself and a 200 would then start mid-file; WebServer ignores Range and sends the whole row with a 200. Set /sdcard/Download/CodeOnTheGo.nointercept to force documentation back onto the server.
  • app/.../localWebServer/WebServer.kt — serves Tier 3 over HTTP on port 6174, for WebViews that are not wired to the interceptor above and for the /pr/ developer endpoints. It reads through DocumentationContentSource, so it holds no database, template engine or decode logic of its own; what remains here is HTTP: request parsing, the /pr/ pages, error responses, and the CSS/asset shortcuts. Also serves a Dynamic Bookshelf JSON payload (joining Content/Bookshelf/BookCategories, rendered through the bookshelf template) and debug-only HTML dumps at /pr/db (LastChange, last 20 rows) and /pr/pr (recent projects, from a different database).
  • idetooltips/.../ToolTipManager.kt — serves Tier 1/2, through readTooltip in TooltipQuery.kt, which runs queryTooltip and queryTooltipButtons and builds the item shown. Looks up a tooltip by (category, tag): the category's id from TooltipCategories first, so the row is found by Tooltips' key rather than a scan of the whole table (ADFA-6597), then its text by id. Then its buttons for the Tier 3 links shown at the bottom, by TooltipButtons' key, with their text by id. Refuses a database declaring a version below 3.
  • plugin-manager/.../documentation/PluginDocumentationManager.kt (with Tier3AssetWalker.kt, and the DocumentationExtension contract in plugin-api) — lets plugins contribute their own help content into the same lookup paths, under the reserved plugin/<pluginId>/... prefix. Its tooltip SQL is in PluginTooltipTables.kt: a plugin's tooltips are replaced or removed by category inside one transaction, each button's text is looked up in or added to the lookup tables, and text no button uses any more is deleted afterwards. Brotli assets are compressed against the same CompressionDictionary the rest of the table uses, read from the database being written. When that dictionary can't be read the install is abandoned rather than written plain, and retried on the plugin's next activation.

Not a database reader, but beside them: common/.../documentation/OffDeviceRequests.kt keeps the documentation WebViews (HelpActivity, the tooltip fragment, FAQActivity) and the tooltip popup on the device (ADFA-6589). Each one's shouldInterceptRequest answers an http(s) request to any host but the device itself with an empty 204, after the interceptor and before the network, so requests to localhost still reach WebServer. Tapped off-device links, and an off-device URL passed to HelpActivity, go through UrlManager.openUrl's consent dialog to the browser. keepOnDevice turns Safe Browsing off, which would otherwise have Google Play services send Google hash prefixes of the addresses, charged to the app, even for requests the block answers locally; and it gives service-worker fetches the same block. That service-worker client is process-wide and stays set: from the first documentation WebView on, it covers every WebView in the app, plugins' included, and a plugin that sets its own replaces it only until the next documentation WebView or tooltip popup sets this one again. What the block cannot see: <link rel=preconnect> and dns-prefetch (or a Link: header), WebSockets, WebRTC, and the hops of a redirect. No documentation page uses them, and neither WebServer nor the interceptor redirects or sends Link:. It is not WebSettings.blockNetworkLoads, which would also block WebServer's /pr/ endpoints and the CodeOnTheGo.nointercept fallback.

Editing the database

Schema changes and data edits happen outside this repo, in OfflineDocumentationTools/docdb-studio (a Flet GUI over the master documentation.db). The master stays in documentation version 2: docdb-studio and the other OfflineDocumentationTools scripts read and write the version 2 tooltip tables, and migrate_tooltip_schema.py converts a copy of the master to version 3 for shipping. Conventions enforced there that matter if you're reasoning about data correctness here:

  • The schema is locked — docdb-studio's own AGENTS.md says never change it. If a new column/table is genuinely needed, it's a cross-repo change coordinated with that project, not something to route around in CoGo.
  • Tooltip uniqueness is (categoryId, tag); button uris (TooltipButtons.uri in the version 2 master, TooltipButtonUris.uri once converted) are validated there against Content.path (post ?query/#fragment stripping) before being allowed into the database.
  • Every edit made through the tool updates LastChange for the affected documentation set, which is how DatabaseVersionResolver's debug logging can say what build of the docs is loaded.

Writing one-off SQL scripts against this database

Some tickets (e.g. ADFA-5088) ship a one-off .sql script under docs/docdb/ for a docdb-studio maintainer to run against the real database, rather than editing it directly through the tool. They run against the version 2 master, so they use its tooltip columns (Tooltips.summary, TooltipButtons.uri); never run one against a converted version 3 file. Gotchas found writing those scripts:

  • The system SQLite has no JSON1 on some devices, and your desktop does. JSON_OBJECT, JSON_GROUP_ARRAY and friends compile fine under the sqlite3 CLI (3.44 ships JSON1 built in) and then fail at runtime with no such function: JSON_OBJECT on real hardware — reproduced on a Galaxy Note 20 Ultra, where the Dynamic Bookshelf was an HTTP 500 until the query was rewritten (ADFA-5179). Nothing on the desktop side of the fence will warn you. Write plain relational SQL and assemble the JSON in Kotlin, and treat any device-only 500 from a new query as this until proven otherwise. Applies to every reader of this database, not just WebServer: a docdb-studio script, a tooltip query in ToolTipManager, and PluginDocumentationManager are all equally exposed.
  • Keep each .system line simple. The sqlite3 CLI's .system dot-command can hit a content-dependent shell-parsing failure when a line chains multiple operators (;, &&, ||, parentheses) — it reproduces for some input strings and not others, so it won't necessarily show up in a quick test. Stick to one plain command | pipe > file per .system line.
  • .bail on is required for BEGIN/COMMIT to actually mean atomic. Without it, a mid-script SQL error prints to stderr but the script keeps going — including reaching the final COMMIT, which then persists whatever succeeded before the error (verified empirically, not just documented behavior). .bail also can't see .system shell failures directly, so a failed or empty Brotli payload (which leaves its target file missing or zero-length) needs its own check: insert its READFILE() into a throwaway CREATE TEMP TABLE guarded by NOT NULL CHECK (length(content) > 0) immediately before the real Content insert, turning that failure into a real SQL error .bail will catch. See docs/docdb/ADFA-5088-preference-tooltips.sql for the working pattern.
  • Don't write Brotli payloads to bare /tmp/*.br filenames. A fixed, guessable name directly under world-writable /tmp lets another local user pre-plant a symlink or race the write/read pair between the .system echo | brotli write and the READFILE() read (CWE-377). Create an owner-only working directory instead — rm -rf it, then mkdir -m 700 it (the mode is set atomically at creation, with no window where it's briefly world-accessible) — write every payload under that directory, and remove it again before COMMIT. See the same script for the working pattern.

Known rough edges

  • A Tier 3 page that references something off-device is a bug in the content, not the code. Since ADFA-6589 the documentation WebViews answer such a request with an empty local response, so the font, image or player is simply missing, and a tapped off-device link asks before opening in the browser (OffDeviceRequests). If you see one while working in this area, it's a data problem to report upstream, not a WebServer bug to fix here.
  • Content's UNIQUE(path) constraint (rather than UNIQUE(path, languageID)) means a second language for an existing path can't currently be added without a schema change upstream — multi-language content isn't fully wired yet even though the Languages dimension anticipates it.
  • TooltipButtonNumbers is a whole table whose only job is pinning a manual sort order; a lighter-weight mechanism (e.g. an ordering column directly on TooltipButtons) would remove a table.