The text.db format¶

An indexed dictionary is an SQLite 3 database, text.db, with an optional second database for its media pack, media.db. This page specifies both, for programs that read or write them.

~/.dmshlabs/dictshelf/db/Webster/
  text.db          articles and indexes
  media.db         media pack: images and audio (optional)
  media.link.db    a cache; can be deleted      (optional)
  info.txt         source and build details
  res/             resource overrides           (optional)

text.db alone is a complete dictionary; the other files are optional or derived.

PRAGMA user_version is 1; DictShelf does not open a database with another value. Tables have been added without changing it (see Compatibility), so a reader must check that a table exists before using it.

Schema¶

PRAGMA user_version = 1;

CREATE TABLE meta(key TEXT PRIMARY KEY, value TEXT);

CREATE TABLE entry(id INTEGER PRIMARY KEY,
                   w  TEXT NOT NULL,          -- display headword
                   m  TEXT NOT NULL);         -- article body (see below)
CREATE INDEX idx_entry_w ON entry(w COLLATE NOCASE);

CREATE TABLE alias(w        TEXT    NOT NULL, -- an alternative spelling
                   entry_id INTEGER NOT NULL REFERENCES entry(id));
CREATE INDEX idx_alias_w ON alias(w COLLATE NOCASE);

CREATE VIRTUAL TABLE entry_fts USING fts5(    -- rowid = entry.id
    w, txt, content='', columnsize=0,
    tokenize='unicode61 remove_diacritics 2');

CREATE VIRTUAL TABLE entry_trigram USING fts5(-- only with the contains index
    w, content='', columnsize=0, tokenize='trigram');

entry holds the whole dictionary. The other tables are indexes and can be rebuilt from it.

Reading articles¶

entry.m is either plain HTML text or a DEFLATE-compressed BLOB, decided per row by its first byte:

Article HTML never begins with NUL. Both forms occur in the same table — short articles (under 120 bytes) and ones that did not compress are stored literally — so a reader must handle both.

import sqlite3, zlib

db = sqlite3.connect("file:text.db?mode=ro", uri=True)
for w, m in db.execute("SELECT w, m FROM entry ORDER BY id"):
    blob = m if isinstance(m, bytes) else m.encode()
    html = zlib.decompress(blob[1:], -15).decode() if blob[:1] == b"\x00" \
           else blob.decode()
    print(w, html[:80])

-15 is raw DEFLATE with no zlib or gzip header — zlib.decompress(blob[1:]) without it will fail.

Each row is compressed on its own, without a shared dictionary, so any row can be read alone. Short or incompressible rows are stored literally; reading handles both forms, including databases written before compression was added.

Headwords, aliases and sub-entries¶

• entry.w is the headword as it is displayed. Lookups compare COLLATE NOCASE, which is the collation both indexes are built with.
• alias holds every other spelling that must find this entry: StarDict .syn entries, MDX @@@LINK redirects, Slob aliases, DSL variant headwords. Redirects are resolved during indexing: an alias always points at an existing entry.id, never at another alias.
• A headword beginning with @ (@examples_woman) is an MDict-style sub-entry: a fragment an article pulls in by link, never a word. It stays reachable by exact lookup, is excluded from browsing and contains search, and is not counted in meta.entry_count (meta.sub_entries counts them). Exporters should normally skip w LIKE '@_%'.

The search indexes¶

Both FTS5 tables are contentless (content='') and keyed by rowid = entry.id; they hold no text that needs exporting.

Folded means lowercased, NFD-normalised, with combining marks dropped — so Café is indexed as cafe. meta.fold_version records the rules used; a mismatch marks the contains index outdated and does not affect the articles.

entry_fts exists in every text.db: accent-insensitive exact and prefix lookup use it.

meta¶

A flat key/value table. Unknown keys must be ignored; absent keys must be treated as empty rather than as an error.

name and description are HTML-unescaped on the way out, so a title stored as A & B displays as A & B.

Article HTML¶

Bodies are stored as the dictionary wrote them, converted to HTML where the source format was not HTML. In particular, references to media are left in their original spelling:

<img src="pictures/lion.jpg">
<a href="sound://lion.mp3">🔊</a>

DictShelf rewrites them to /res/<dictionary id>/… when it serves an article, not in the database. An exporter gets the original names and looks each one up in media.db (below) or beside the dictionary file.

media.db¶

Written when the media pack is added. Same conventions and user_version as text.db.

PRAGMA user_version = 1;
CREATE TABLE meta(key TEXT PRIMARY KEY, value TEXT);   -- dict_uuid, name, format
CREATE TABLE resource(name TEXT PRIMARY KEY, mime TEXT, data BLOB);

• resource.name is the name as articles reference it. A name is matched exactly, then case-insensitively, then in both Unicode normalization forms: MDX stores resource names in lower case, loose files keep their spelling, and an article may use either.
• resource.data is the file's bytes, not compressed by DictShelf.
• meta.dict_uuid must equal the dict_uuid in the text.db beside it. A mismatched pair is refused.

media.link.db caches where media is located inside the dictionary files. It is derived, need not be copied, and can be deleted.

Exporting¶

Without SQL:

dictshelf dump -o out ~/.dmshlabs/dictshelf/db/Webster

This writes out/Webster.csv: "#key","value" metadata rows, then one row per entry (headword, article, alternative headwords), with resources in Webster.csv_res. pyglossary converts this layout to any format it supports.

The complete dictionary, with SQL:

SELECT e.id, e.w, e.m,
       (SELECT group_concat(a.w, char(10)) FROM alias a WHERE a.entry_id = e.id) AS aliases
FROM entry e
WHERE e.w NOT LIKE '@_%'          -- skip sub-entry fragments
ORDER BY e.id;

Decode m as shown above. Open the file read-only (file:…?mode=ro) while DictShelf may be running.

Authoring a dictionary directly¶

A text.db written by another program is a DictShelf dictionary. Put its folder in the library or in a dictionary folder.

Requirements:

• PRAGMA user_version = 1.
• Create entry, alias and entry_fts even for a headwords-only dictionary; leave entry_fts.txt as '' when you do not index text.
• Create both indexes exactly as shown, with COLLATE NOCASE; otherwise SQLite does not use them and every lookup scans the table.
• entry_trigram stores the folded headword, not the raw one.
• Article bodies are compressed when this reduces their stored size. Readers also accept literal bodies written by older versions.
• Set meta.name and meta.description: they are the title and the About this dictionary text.
• Set meta.dict_uuid (16 random bytes, hex) if you also write a media.db, and put the same value in both.
• Leave out meta.source_path. If it names an existing file, DictShelf takes the About text from that file and ignores description.
• Write to a temporary file and rename it into place, so an incomplete database is never found.

meta.format is a free label. Use your own (native), not dsl or stardict: DictShelf treats those as built by itself and may offer to rebuild them.

Compatibility¶

• user_version stays at 1. Tables have been added without changing it (entry_trigram); check that a table exists before using it: SELECT count(*) FROM sqlite_master WHERE type='table' AND name='entry_trigram'.
• Unknown meta keys are ignored, so you can add your own.
• A database without an optional index opens with fewer search modes.
• FTS5 is required: an SQLite build without it cannot open a text.db. The trigram tokenizer needs SQLite 3.34 or newer.

The library CLI reference