Collection files

Reading and writing Anki’s schema-18 sqlite collections, no Anki required

An .anki2 collection is a single sqlite file. Anki’s desktop app drives it through a Rust library, but nothing about the file needs Rust: it’s ordinary tables of notes, cards and review history, plus a handful of columns holding small protobuf messages (notetype, deck and deck-config settings). This module creates and connects to those files directly. The protobuf classes in fastanki._proto are generated from Anki’s own .proto files, so the blobs we write are byte-compatible with what Anki writes.

from fastcore.test import *
import tempfile
from anki.collection import Collection as AnkiCollection
from google.protobuf.json_format import MessageToDict

Throughout this notebook we check our work against Anki itself: the anki package is a development-time dependency only, used as an oracle. If Anki can open a file we created, add a note to it, and pass its own database check, we got the details right.

The schema

The DDL below is dumped straight from a collection created by Anki 26.05 (the test that follows re-dumps and compares, so drift shows up here rather than in the field). Two details matter. The col table is a leftover from the old single-row JSON format: schema 18 keeps the row but stores empty strings in the JSON columns, with the real data moved to proper tables. And several columns are declared COLLATE unicase, a custom case-insensitive collation that Anki’s Rust registers with sqlite; we must register our own equivalent before any query that compares those columns.

---

source

connect

def connect(
    path
):

Connect to collection sqlite file at path, with Anki’s unicase collation and apsw best practices

---

source

unicase

def unicase(
    a:str, b:str
):

sqlite collation function matching Anki’s case-insensitive unicase

casefold is not identical to the Unicode case folding in Rust’s unicase crate for every codepoint, but sqlite only consults the collation for ordering and uniqueness checks on names, where casefold agrees for any name you’re likely to meet.

To confirm the DDL still matches what Anki generates, create a fresh collection with the oracle and compare table-by-table:

td = Path(tempfile.mkdtemp())
oc = AnkiCollection(str(td/'collection.anki2'))
oc.close()
ocon = connect(td/'collection.anki2')
theirs = {r[0]: r[1] for r in ocon.execute("select name, sql from sqlite_master where sql is not null and name not like 'sqlite_stat%'")}
mcon = connect(':memory:')
mcon.execute(SCHEMA)
ours = {r[0]: r[1] for r in mcon.execute("select name, sql from sqlite_master where sql is not null")}
test_eq(ours, theirs)

Timestamps and ids

---

source

creation_stamp

def creation_stamp():

Collection creation time: the most recent 4am in the local timezone

---

source

day_offset

def day_offset():

Minutes west of UTC, Anki’s creationOffset (eg Australia at +10h is -600)

---

source

now_ms

def now_ms():

Call self as a function.

Anki dates everything from crt, the collection creation stamp, pinned to a 4am day rollover so late-night reviews count as the day before. creationOffset records the timezone it was computed in.

Object ids are epoch milliseconds, bumped past any existing id on collision:

---

source

ts_id

def ts_id(
    con, table
):

New timestamp-based id for table: now in ms, or 1 past the max existing id

Default objects

A working collection needs one deck (“Default”, id 1), one deck config (id 1), and at least one notetype. We build Basic and Cloze, the two our API creates cards with; anything fancier arrives via sync from collections that already have it. The builders construct the same protobuf messages Anki’s stock-notetype code does, including the precomputed reqs (which fields must be non-empty for each template to generate a card).

---

source

notetype_cfg

def notetype_cfg(
    kind:int=0, # 0 normal, 1 cloze
    stock:int=0, # original_stock_kind, for Anki's "restore to default" feature
    reqs:NoneType=None, # [(card_ord, 'any'|'all', [field_ords])] card generation requirements
    css:str='.card {\n    font-family: arial;\n    font-size: 20px;\n    line-height: 1.5;\n    text-align: center;\n    color: black;\n    background-color: white;\n}\n'
):

Call self as a function.

---

source

template_cfg

def template_cfg(
    q, a
):

Call self as a function.

---

source

field_cfg

def field_cfg(
    tag:NoneType=None, # Cloze field role marker
    prevent_deletion:bool=False, # Protect the field from deletion in Anki's UI
):

Call self as a function.

Now the two stock notetypes. Each is a set of rows: one in notetypes, one per field in fields, one per template in templates. add_notetype inserts them all:

---

source

add_cloze

def add_cloze(
    con
):

Call self as a function.

---

source

add_basic

def add_basic(
    con
):

Call self as a function.

---

source

add_notetype

def add_notetype(
    con, name, flds, tmpls, cfg
):

Insert a notetype with flds [(name, config_bytes)] and tmpls [(name, config_bytes)], returning its id

The test: build ours, decode Anki’s from the oracle collection, and compare as dicts. The random per-field ids (used by newer Ankis to match fields when merging imports) will differ, so they’re dropped before comparing:

add_basic(mcon); _ = add_cloze(mcon)

def ntdicts(con):
    "All notetype/field/template config blobs for Basic and Cloze, decoded, random ids stripped"
    qs = [('notetype', notetypes_pb2.Notetype.Config, "select n.name, 0, n.config from notetypes n"),
          ('field', notetypes_pb2.Notetype.Field.Config, "select n.name, f.ord, f.config from fields f join notetypes n on f.ntid=n.id"),
          ('template', notetypes_pb2.Notetype.Template.Config, "select n.name, t.ord, t.config from templates t join notetypes n on t.ntid=n.id"),]
    res = {}
    for kind,msgcls,q in qs:
        for name,ord_,blob in con.execute(q + " where n.name in ('Basic','Cloze')"):
            m = msgcls(); m.ParseFromString(blob)
            d = MessageToDict(m, preserving_proto_field_name=True); d.pop('id', None)
            res[kind,name,ord_] = d
    return res

test_eq(ntdicts(mcon), ntdicts(ocon))

Creating a collection

create_collection writes everything a fresh collection needs: the col row (legacy JSON columns as empty strings), the config entries Anki expects, the Default deck and deck config, and the two notetypes. The values mirror what Anki 26.05 writes on profile creation, checked by the oracle test at the end.

---

source

deck_kind

def deck_kind(
    config_id:int=1
):

Call self as a function.

---

source

deck_common

def deck_common():

Call self as a function.

---

source

default_deck_config

def default_deck_config():

Call self as a function.

---

source

create_collection

def create_collection(
    path
):

Create a new empty schema-18 collection at path, returning a connection to it

The moment of truth: Anki opens a file we wrote from nothing, passes its own database check, and adds a note to it.

cpath = td/'ours'/'collection.anki2'
con = create_collection(cpath)
con.close()
ac = AnkiCollection(str(cpath))
prob = ac.fix_integrity()
test_eq(prob[1], True)
prob

('Database rebuilt and optimized.', True)

nt = ac.models.by_name('Basic')
n = ac.new_note(nt)
n['Front'],n['Back'] = 'hello','world'
ac.add_note(n, 1)
ac.close()
con = connect(cpath)
flds, = con.execute('select flds from notes').fetchone()
test_eq(flds, 'hello\x1fworld')
test_eq(con.execute('select count(*) from cards').fetchone()[0], 1)

Beyond passing fix_integrity, the values a fresh collection is born with match Anki’s own. The default deck and its config decode to identical protobufs, and the config table agrees key for key — excepting curModel, which names the Basic notetype by its per-collection timestamp id:

ourcon = create_collection(td/'cmp.anki2')
def deckblob(c):
    cm,kd = c.execute('select common, kind from decks where id=1').fetchone()
    return (MessageToDict(decks_pb2.Deck.Common.FromString(cm), preserving_proto_field_name=True),
            MessageToDict(decks_pb2.Deck.KindContainer.FromString(kd), preserving_proto_field_name=True))
def dconfblob(c):
    b, = c.execute('select config from deck_config where id=1').fetchone()
    return MessageToDict(deck_config_pb2.DeckConfig.Config.FromString(b), preserving_proto_field_name=True)
def conftbl(c): return {k:json.loads(v) for k,v in c.execute('select key, val from config') if k!='curModel'}
test_eq(deckblob(ourcon), deckblob(ocon))
test_eq(dconfblob(ourcon), dconfblob(ocon))
test_eq(conftbl(ourcon), conftbl(ocon))
ourcon.close()