Since learning about Anki’s selective card generation, I want to merge all the “definition” notes that I have with the “translation” notes for the same Spanish terms. I don’t want to lose the scheduling – the learning history – that I’ve already built up for the existing cards.

Anki doesn’t have a a built-in way to support this.

Samuel Allain already published a solution for merging notes and keeping historical data on the Anki forum. It’s quite technical, working directly with the Anki database and its debug console. Here I explore the ideas discussed there to figure out how to do it for myself.

Take Anki offline

Close Anki’s session with Ankiweb before doing anything else. This disables automatic synchronization, meaning any mistakes you make won’t be propagated. We can enable it again later by giving Anki the username and password again.

Make a backup and a working copy

Make a backup copy of the Anki data files. We won’t touch this again unless something goes badly wrong.

cp -r ~/.local/share/Anki2 ~/tmp
mv ~/tmp/Anki2 ~/tmp/Anki2Backup

Make another copy to work on. We’ll make changes here and later sync them if it goes well.

cp -r ~/.local/share/Anki2 ~/tmp
mv ~/tmp/Anki2 ~/tmp/Working

Launch Anki from the working copy in safe mode. Safe mode stops Anki pushing the changes to AnkiWeb. Without it you might as well keep editing the main local copy.

anki --safemode --base ~/tmp/Working

Open the card explorer and note the card count.

Add a placeholder note like something with “DELETE ME” in the fields.

Open the card explorer and check that the card count has incremented.

Close Anki.

Open Anki normally.

anki

Open the card explorer and check that the card count has not incremented here. Thus you have demonstrated that changes to the working copy do not affect the main local copy.

Close Anki.

Using the Anki debug console

Launch Anki from the working copy in safe mode.

anki --safemode --base ~/tmp/Working

Open the debug console. On a Spanish keyboard the key combination is CTRL+SHIFT+:.

The debug console is a sort of Python REPL with some objects already in the environment. Unlike the stock Python REPL, you need to use the print function to see the state of an object.

>>> print("Hello, world!");
Hello, world!
>>> mw
<no output>
>>> print(dir())
['bcard', 'card', 'frm', 'mw', 'pp', 'pprint', 'self', 'text', 'traceback']

Refer to the source code to find out what the objects and types are.

Confirm we are using the working copy.

>>> print(mw.col.path)
/home/isme/tmp/Working/Usuario 1/collection.anki2

But what are we actually looking for in the database? It’s not very comfortable to query it in the debug console, so let’s try somthing else.

Inspecting the Anki data

Anki has a concept of user profiles to store notes per user. I have just one user automatically called “Usuario 1”.

cd ~/tmp/Working/Usuario\ 1/

collection.anki2 is the main sqlite database. You can query it using the sqlite CLI.

$ sqlite3 file:collection.anki2?mode=ro
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
FIELDS            config            notes             templates       
android_metadata  deck_config       notetypes       
cards             decks             revlog          
col               graves            tags

The note type ID for the definitions is

sqlite> .mode line
sqlite> SELECT * FROM notetypes;
[...]
        id = 1612340248158
      name = Definiciones españolas
[...]

You have to read all the data to get that. It’s not possible to filter the rows because of a collation error.

sqlite> SELECT * FROM notetypes WHERE name = '';
Error: no such collation sequence: unicase

Filter the notes by type using the mid column.

sqlite> SELECT * FROM notes WHERE mid = 1612340248158 LIMIT 3;
   id = 1611903132404
 guid = Brv^C
  mid = 1612340248158
  mod = 1614030977
  usn = 0
 tags =  una_ola_de_solidaridad 
 flds = pueblo Potemkinexpresión referida a algo muy bien presentado que disimula su desastroso estado real
 sfld = pueblo Potemkin
 csum = 4260017649
flags = 0
 data = 

   id = 1612340404947
 guid = pkE8$`Q=h/
  mid = 1612340248158
  mod = 1614030977
  usn = 0
 tags =  meteorología 
 flds = anticiclónUn <b>anticiclón</b>&nbsp;es una zona amosférica de alta presión, en la cual la presión atmosférica es superior a la del aire que lo rodea.
 sfld = anticiclón
 csum = 1997138271
flags = 0
 data = 

   id = 1612451226547
 guid = ]2mv1
  mid = 1612340248158
  mod = 1614030977
  usn = 0
 tags = 
 flds = echar un kikitener sexo improvisadohttps://coolturize.com/origen-expresion-echar-un-polvo
 sfld = echar un kiki
 csum = 1364802529
flags = 0
 data =

All the fields are stored in a single column called flds. How is it formatted?

See Anki Database Structure documentation.

the values of the fields in this note. separated by 0x1f (31) character.

This makes it quite complex in SQL to copy a single field from the definition note to the main translation note.

Either I can just do that part manually in the explorer (boo!) or find another way to do it using a plugin or another API.

Looks like it can be done using the Advanced Copy Fields add-on.

There are 106 definitions with fields to copy.

sqlite> SELECT COUNT(*) FROM notes WHERE mid = 1612340248158;
COUNT(*) = 106

There are also 106 cards with schedules to copy.

sqlite> SELECT COUNT(*) FROM cards WHERE nid IN (SELECT id FROM notes WHERE mid = 1612340248158);
COUNT(*) = 106

After making the new cards we need to pair each old and new card to copy the schedule from old to new. Since the original term is the same on each card (or at least it should be!) we can search in the card explorer for that term to see the cards.

You can use a filter like this to find pairs for a term:

("note:Español-Inglés" AND "card:es->en" AND "Término Español:anticiclón") OR
("note:Definiciones españolas" AND "card:Definición" AND "Término:anticiclón")

It turns out that Samuel Allain even developed a plugin for this part too, with the snappy title of Transfer scheduling data from one card to another.

So now it looks like the process is:

  • Identify each note pair
  • Copy the field from the definition note to the translation note using Advanced Copy Fields add-on.
  • Identify the new card pair
  • Copy the schedule from the old definition card to the new definition card using the Transfer scheduling data add-on.
  • Delete the definition note

Let’s try it on one definition note to see how it works.

Trying it out

TODO!