Skip to content

Instantly share code, notes, and snippets.

@sartak
Last active June 26, 2024 04:59
Show Gist options
  • Save sartak/3921255 to your computer and use it in GitHub Desktop.
Save sartak/3921255 to your computer and use it in GitHub Desktop.
Anki 2 annotated schema
-- cards are what you review. easy!
CREATE TABLE cards (
id integer primary key,
-- the epoch milliseconds of when the card was created
nid integer not null,
-- notes.id
did integer not null,
-- deck id (available in col table)
ord integer not null,
-- ordinal, seems like. for when a model has multiple templates, or thereabouts
mod integer not null,
-- modified time as epoch seconds
usn integer not null,
-- "From the source code it appears Anki increments this number each time you synchronize with AnkiWeb and applies this number to the cards that were synchronized. My database is up to 1230 for the collection and my cards have various numbers up to 1229." -- contributed by Fletcher Moore
type integer not null,
-- in anki1, type was whether the card was suspended, etc. seems to be the same. values are 0 (suspended), 1 (maybe "learning"?), 2 (normal)
queue integer not null,
-- "queue in the cards table refers to if the card is "new" = 0, "learning" = 1 or 3, "review" = 2 (I don't understand how a 3 occurs, but I have 3 notes out of 23,000 with this value.)" -- contributed by Fletcher Moore
due integer not null,
ivl integer not null,
-- interval (used in SRS algorithm)
factor integer not null,
-- factor (used in SRS algorithm)
reps integer not null,
-- number of reviews
lapses integer not null,
-- possibly the number of times the card went from a "was answered correctly" to "was answered incorrectly" state
left integer not null,
-- 0 for all my cards
odue integer not null,
-- 0 for all my cards
odid integer not null,
-- 0 for all my cards
flags integer not null,
-- 0 for all my cards
data text not null
-- currently unused for decks imported from anki1. maybe extra data for plugins?
);
-- col is a collection of decks
CREATE TABLE col (
id integer primary key,
-- seems to be an autoincrement
crt integer not null,
-- there's the created timestamp
mod integer not null,
-- last modified in milliseconds
scm integer not null,
-- a timestamp in milliseconds. "schema mod time" - contributed by Fletcher Moore
ver integer not null,
-- version? I have "11"
dty integer not null,
-- 0
usn integer not null,
-- 0
ls integer not null,
-- "last sync time" - contributed by Fletcher Moore
conf text not null,
-- json blob of configuration
models text not null,
-- json object with keys being ids(epoch ms), values being configuration
decks text not null,
-- json object with keys being ids(epoch ms), values being configuration
dconf text not null,
-- json object. deck configuration?
tags text not null
-- a cache of tags used in this collection (probably for autocomplete etc)
);
-- dunno what this is yet. my deck has 7. usn is -1, type is 1, and oid varies.
CREATE TABLE graves (
usn integer not null,
oid integer not null,
type integer not null
);
-- notes are the new facts, which can be used by different cards.
CREATE TABLE notes (
id integer primary key,
-- epoch seconds of when the note was created
guid text not null,
-- globally unique id, almost certainly used for syncing
mid integer not null,
-- model id
mod integer not null,
-- modified timestamp, epoch seconds
usn integer not null,
-- -1 for all my notes
tags text not null,
-- space-separated string of tags. seems to include space at the beginning and end of the field, almost certainly for LIKE "% tag %" queries
flds text not null,
-- the values of the fields in this note. separated by 0x1f (31).
sfld integer not null,
-- the text of the first field, used for anki2's new (simplistic) uniqueness checking
csum integer not null,
-- dunno. not a unique field, but very few repeats
flags integer not null,
-- 0 for all my notes
data text not null
-- empty string for all my notes
);
-- revlog is review history; it has a row for every single review you've ever done!
CREATE TABLE revlog (
id integer primary key,
-- epoch-seconds timestamp of when you did the review
cid integer not null,
-- cards.id
usn integer not null,
-- all my reviews have -1
ease integer not null,
-- which button you pushed to score your recall. 1(wrong), 2(hard), 3(ok), 4(easy)
ivl integer not null,
-- interval
lastIvl integer not null,
-- last interval
factor integer not null,
-- factor
time integer not null,
-- how many milliseconds your review took, up to 60000 (60s)
type integer not null
);
CREATE INDEX ix_cards_nid on cards (nid);
CREATE INDEX ix_cards_sched on cards (did, queue, due);
CREATE INDEX ix_cards_usn on cards (usn);
CREATE INDEX ix_notes_csum on notes (csum);
CREATE INDEX ix_notes_usn on notes (usn);
CREATE INDEX ix_revlog_cid on revlog (cid);
CREATE INDEX ix_revlog_usn on revlog (usn);
@fasiha
Copy link

fasiha commented Aug 31, 2014

col.models describes specifically model configuration. It's JSON: keys are the model IDs (epoch ms) and here's my crack at describing its fields:

{
    css : "CSS, shared for all templates",
    did :
        "Deck ID, presumably of the first deck that used this model, since "
        "multiple decks can use the same model. 1 if unused in any deck?",
    flds : [
             "Array of objects with keys:",
             {
               font : "display font",
               media : "?",
               name : "field name",
               ord : "matches cards.ord: the template number",
               rtl : "boolean, right-to-left script",
               size : "? number",
               sticky : "?"
             }
           ],
    id : "string, model ID, matches cards.mid",
    latexPost : "suffix for Latex",
    latexPre : "prefix for Latex",
    mod : "? number",
    name : "model name",
    req : [
            "Array of 3 elements:",
            [
              "array index, 0, 1, ...",
              '? string, "all"',
              "another array",
              ["appears to be the array index again"]
            ]
          ],
    sortf : "? number, 0 or 5 in mine",
    tags : "? array of 0 length",
    tmpls : [
              "array of objects representing templates",
              {
                afmt : "answer template string",
                bafmt : "? some other template string?",
                bqfmt : "? some other template string?",
                did : "? null",
                name : "template name",
                ord : "template number, see flds",
                qfmt : "question format string"
              }
            ],
    type : "? 0 or 1",
    usn :
        "? perhaps related to usn elsewhere in database (Ankiweb.net "
        "synchronization counter)",
    vers : "? zero-length array in mine"
}

@fasiha
Copy link

fasiha commented Oct 24, 2014

cards.ord indeed seems to be an index, starting at 0, of the cards associated with a note, i.e., a cloze-deletion note with six clozes will have six cards, with their own cards.id, associated with the same note id cards.nid, with cards.ord differentiating them. I imagine it's the same for the front-and-back cards too.

@yogert909
Copy link

Thanks for posting this! I was browsing anki's source code and found the following clarifications:
cards.lapses is the number of times a review card is failed back to relearning. Failed learning and relearning cards are not counted.
cards.type seems to be "learning" = 0, "review" = 1, "lapse/relearn" = 2, "filtered" = 3
cards.queue seems to be "suspended" = -1 "new" = 0, "learn" = 1, "review" = 2, "relearn" = 3

@yogert909
Copy link

I found this clarification in the cards.py:

# Type: 0=new, 1=learning, 2=due
# Queue: same as above, and:
#        -1=suspended, -2=user buried, -3=sched buried
# Due is used differently for different queues.
# - new queue: note id or random int
# - rev queue: integer day
# - lrn queue: integer timestamp

@yogert909
Copy link

Also, cards.odid and cards.odue seem to be serve the same function for cards in filtered decks as cards.did and cards.due in regular decks.

@rdrey
Copy link

rdrey commented Apr 30, 2015

Thanks everyone! I was just starting to annotate the source myself, when I decided to google anki ord mod usn and found this!

@cdpm
Copy link

cdpm commented Aug 14, 2015

graves.type denotes the type of data that was deleted: 0 stands for a card, 1 for a note and 2 for a deck.
see the consts REM_CARD, REM_NOTE and REM_DECK defined in https://github.com/dae/anki/blob/master/anki/consts.py and used in sync.py

@timrae
Copy link

timrae commented Sep 1, 2015

I've filled in most of the gaps, corrected some errors, and added it to the AnkiDroid wiki. Thanks all:
https://github.com/ankidroid/Anki-Android/wiki/Database-Structure

@sartak
Copy link
Author

sartak commented May 6, 2017

Just stumbled upon all this activity 😂 Added a comment to the top pointing to the AnkiDroid wiki. Thanks!

@Speculate7348
Copy link

Based

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment