-
-
Save sartak/3921255 to your computer and use it in GitHub Desktop.
-- 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); |
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"
}
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.
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
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
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.
Thanks everyone! I was just starting to annotate the source myself, when I decided to google anki ord mod usn
and found this!
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
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
Just stumbled upon all this activity 😂 Added a comment to the top pointing to the AnkiDroid wiki. Thanks!
Based
Thanks, this is most useful! By the way, revlog.time can actually be >60e3, since you can customize Anki to "Ignore answer times longer than N seconds" under Options->General.