Anki Hacking With Go

Overview

Anki is one of my favorite apps of all time. I’m not going to waste your time explaining what Anki is, or gushing about how awesome it is. I’m assuming you already know and wanted to see how you can hack some scripts together to make your life easier.

I made a ton of cards over the years for learning Japanese but it was only until about a few months ago did I think to myself “Why haven’t I put audio in my cards?”. I had two problems to solve.

  1. How/Where am I going to get the audio?
  2. How do I add it to all my cards?

Downloading Audio

I scoured the internet for a while, but nothing looked good (aka easy). After awhile I remembered that JapanesePod101 had audio on their site, and with a little bit of weeding through the source code. I found the asset URL for their Japanese dictionary and it seemed like this was the best (easiest) route to take.

Here is the asset URL for JapanesePod101: http://assets.languagepod101.com/dictionary/japanese/audiomp3.php It takes two arguments kanji and kana.

An example of a full URL would be http://assets.languagepod101.com/dictionary/japanese/audiomp3.php?kanji=家&kana=いえ

Click on the link above and you should hear the word 家. Pretty easy right? So easy in fact, you could just use cURL every time you wanted a new audio file!

$ curl -L "http://assets.languagepod101.com/dictionary/japanese/audiomp3.php?kanji=家&kana=いえ" > "house.mp3"

This gets EXTREMELY tedious fast. Not to mention a pain in the ass going through and changing the GET parameters and filename each time. Also, what happens when you try to download some obscure word?

Check for Success

Putting in some obscure Japanese word or even just plain old English for the params wont generate an error or a 400 Bad Request. In fact, JapanesePod101 just returns a default mp3 file saying something along the lines of “the audio for this word is currently unavailable”.

This makes checking if the download was a successful one a bit more difficult. Since its the same audio clip each time, the file size will be the same every time there is a failure.

Let’s take a gander at the headers

$ curl -sSL -D - "http://assets.languagepod101.com/dictionary/japanese/audiomp3.php?kanji=DRT&kana=ROCKS" -o /dev/null
HTTP/1.0 200 OK
Date: Tue, 13 Feb 2018 00:40:36 GMT
Server: Apache/2.2.34 (Amazon)
X-Powered-By: PHP/5.3.29
Accept-Ranges: bytes
Content-Length: 52288
Last-Modified: Mon, 02 Feb 2015 10:41:46 GMT
Content-Type: audio/mpeg
X-Cache: MISS from szipXX
X-Cache-Lookup: MISS from szipXX:32080
Via: 1.0 szipXX (squid)
Connection: keep-alive

Take note of Content-Length: 52288, it’ll come in handly later on.

Building a CLI

I wrote this handy CLI in Go, but you can follow along with almost any language, should be pretty easy to translate.

First off, lets make take our URL from earlier and make it a constant. We’ll put in the kanji and kana params but put a %s for the input.

const (
	BaseURLF = "http://assets.languagepod101.com/dictionary/japanese/audiomp3.php?kanji=%s&kana=%s"
)

I want this to be a simple CLI. However, since we need kanji and kana. Put the following at the top of your main funciton

args := os.Args

if len(args) < 3 {
  log.Fatal("usage: jpodaudio 漢字 かな")
}

kanji := args[1]
kana := args[2]
fullUrl := fmt.Sprintf(BaseURLF, kanji, kana)

Now that we have our URL formatted and ready to roll. Let’s do the actual downloading of the file with some simple checks to see if we actually have an audio file we want to save. Check for an internal server error first then check the Content-Length from earlier.

//Do the downloading
resp, err := http.Get(fullUrl)
if err != nil {
  log.Fatal(err)
}

if resp.StatusCode == 500 {
  log.Fatal("Sorry! Internal Server error! Try again in a bit")
}

//The audio for this clip is currently unavailable
if resp.Header.Get("Content-Length") == "52288" {
  log.Fatal("Sorry! Audio doesn't exist! Check kanji and kana or JapanesePod101 doesn't actually have the audio")
}

If all is good to go, we need to save the file. For this case, we’ll make a new file with the name KANJI_KANA.mp3 where the kanji and kana are the arguments from before. To save the file, copy the contents of request body to our newly created file.

fileName := fmt.Sprintf("%s_%s.mp3", kanji, kana)
audioFile, err := os.Create(fileName)
if err != nil {
  log.Fatal(err)
}

defer audioFile.Close()
defer resp.Body.Close()

//Write the body to the file
_, err = io.Copy(audioFile, resp.Body)
if err != nil {
  log.Fatal(err)
}

fmt.Printf("File %s.mp3 downloaded!\n", fileName)

All Put Together

TL;DR? Here’s the code

package main

import (
	"fmt"
	"io"
	"log"
	"net/http"
	"os"
)

const (
	BaseURLF = "http://assets.languagepod101.com/dictionary/japanese/audiomp3.php?kanji=%s&kana=%s"
)

func main() {
	args := os.Args

	if len(args) < 3 {
		log.Fatal("usage: jpodaudio 漢字 かな")
	}

	kanji := args[1]
	kana := args[2]
	fullUrl := fmt.Sprintf(BaseURLF, kanji, kana)

	//Do the downloading
	resp, err := http.Get(fullUrl)
	if err != nil {
		log.Fatal(err)
	}

	if resp.StatusCode == 500 {
		log.Fatal("Sorry! Internal Server error! Try again in a bit")
	}

	//The audio for this clip is currently unavailable
	if resp.Header.Get("Content-Length") == "52288" {
		log.Fatal("Sorry! Audio doesn't exist! Check kanji and kana or JapanesePod101 doesn't actually have the audio")
	}

	fileName := fmt.Sprintf("%s_%s.mp3", kanji, kana)
	audioFile, err := os.Create(fileName)
	if err != nil {
		log.Fatal(err)
	}

	defer audioFile.Close()
	defer resp.Body.Close()

	//Write the body to the file
	_, err = io.Copy(audioFile, resp.Body)
	if err != nil {
		log.Fatal(err)
	}

	fmt.Printf("File %s.mp3 downloaded!\n", fileName)
}

Usage

# word with both kanji and kana
$ jpodaudio 漢字 かんじ
File 漢字_かんじ.mp3 downloaded!

# word without kanji equivalent
$ jpodaudio もう もう
File もう_もう.mp3 downloaded!

# word in katakana
$ jpodaudio ナビ ナビ
File ナビ_ナビ.mp3 downloaded!

Editing Entire Collections

The CLI is great for one-offs. I use it all the time when adding a random word I come across or if I am adding in a few cards at a time. The biggest issue for me was, what about all the other cards I’ve made over the years. I have hundreds, probably thousands of cards in my decks. I don’t have time to add them one by one. Thats where this handy little script comes in.

Before you venture off into this manipulating your Anki database, I want to give a word of warning:

BACKUP YOUR ANKI DATABASE BEFORE YOU DO ANYTHING

Now that thats out of the way. Let’s dive in!

Understanding the Anki Database

The Anki database is composed of 5 tables: cards col graves notes revlog. I could try and do a whole post on just the Anki database but for right now, the two we’re focused on are col and notes.

col

col “contains a single row that holds various information about the collection”

Most of the data in the col table are large JSON objects containing information to cards, decks, configuration, etc.

The JSON object has the card types ID (epoch time when the card was created) and a JSON object containing all the card information. This houses a lot of data varying from CSS, card type fields, card type name, templates, last modified, etc.

notes

notes “contain the raw information that is formatted into a number of cards according to the models”.

Using Card Data

Before we start writing our script. Let’s take a look at the data.

To view the JSON object in models, type the following in your terminal.

sqlite3 /path/to/anki/backup/collection.anki2 "SELECT models FROM col;" | python -m json.tool | less

Depending on how many card types you have in your collection, you might have a lot of data. For now, look for one type and take notice of the ID and name,

Here is a quick (and extremely ugly) way to get your card type’s ID from its name. You might have to do so tweaking to get it to work just right on your machine though.

> sqlite3 collection.anki2 "SELECT models FROM col;" | python -m json.tool | grep -i "YOUR_CARD_TYPE_HERE" -B 4 | awk -F: '{ print substr($2, 1, length($2)-1) }' | head -1
1464780694722

For my card type the ID is 1464780694722. Do a quick count of cards and see if that sounds correct.

> sqlite3 collection.anki2 "SELECT count(id) FROM notes WHERE mid=1464780694722"
1467

That looks right for me. If you go to Manage Note Types in Anki, you can see the names and the count for each one to confirm you’re on the right track. Note Types with Count

My Typical Japanese Card

Here is a typical card in my deck. Yours will obviously look different and thats fine. You just need to take note of the fields you’ll be using and what their index is for the note type. Typical Anki Card For the URL to work correctly, I need Kanji and Kana, and I’ll need to add the sound into my Audio field. The fields are 0 indexed, so I’ll be using 3, 2, and 5 respectively.

Building the Script

Here is a skeleton of the final script

package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"log"
	"net/http"
	"os"
	"strconv"
	"strings"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

const (
	FieldsDelim = "\x1f"
	AudioIndex  = 5 // customize based on your field index
	KanjiIndex  = 3 // customize based on your field index
	KanaIndex   = 2 // customize based on your field index
)

var (
	noteTypeNames = [...]string{
		"Japanese Vocab",
		"Japanese Vocab (and reversed card)",
	} // customize for your note types
	noteTypes = make(map[string]int64)       // keep a map of note names and IDs
	models    = make(map[string]interface{}) // unmarshal JSON to map
	modelData []byte                         // raw JSON data from DB
	cards     []Card                         // hold our card types to update the DB after updating
)

type Card struct {
	Fields string
	Id     int64
}

func downloadAudio(kanji, kana string) error {
	// similar to previous code
	// return error if there is a problem
}

func truncate(kanji, kana string, size int) (string, string) {
	// helper function
}

func main() {
	//open DB


	// query data and unmarshal JSON data


	// go through each note type and process cards
	for name, mid := range noteTypes {

		// query all cards for given note type


		// cycle through each card
		for rows.Next() {
			// scan rows

			// split the field data

			// grab all necessary field data

			// already have audio, skip

			// no kanji for card (possibly katakana or word has no kanji)

			// my cards have a する suffix for suru verbs and a な suffix for na-adjectives
			// these cause a "audio not found" on JapanesePod101, they need to be removed
			// in order to download the audio correctly

			// we good, lets do this shit!
			// download audio
			err = downloadAudio(kanji, kana)
			if err != nil {
				// error downloading file
				// continue marching forward and address afterwards
				fmt.Println(err)
				continue
			}

			// replace the audio field with an Anki sound URI

			// join our fields back together
			// and assign data to our card type
			// so we can update the DB

			//dont overload the server now...
			time.Sleep(time.Millisecond * 1250)
		}
	}

	// update the DB
	for _, card := range cards {
		// prepare update statement

		// execute statement
	}
}

Connect to Database

I like to copy my Anki DB to the working directory. Acts like a back up incase I fubar the whole thing. I recommend doing the same. Connecting to the database is pretty straight forward in Go.

func main() {
	db, err := sql.Open("sqlite3", "./collection.anki2")
	if err != nil {
		log.Fatal(err)
	}
}

Query for Models and Get IDs

Next we need the IDs from our Models so we can query all of the note types. Afterwards we unmarshal the JSON into type map[string]interface{}. We don’t need all the data thats in there. Thats why we’re using an interface. No need to type out a whole struct of the data. Also, I’m lazy.

func main() {
	// db code

	// query data and unmarshal
	db.QueryRow("SELECT models FROM col").Scan(&modelData)
	json.Unmarshal(modelData, &models)

	// cycle through models and find the ones we're looking for
	for id, model := range models {
		//convert model to map to access data
		m, _ := model.(map[string]interface{})

		//check if current model matches one in noteTypeNames
		for _, name := range noteTypeNames {
			if name == m["name"] {
				noteTypes[name], err = strconv.ParseInt(id, 10, 64)
				if err != nil {
					log.Fatal(err)
				} // if err
			} // if names
		} // for _, name
	} // for id, model

	log.Printf("Current noteTypes %v\n", noteTypes)
}

If you were to run the application at this point, you should see something similar to the following:

2018/02/14 15:31:20 Current noteTypes map[Japanese Vocab:1505171166903 Japanese Vocab (and reversed card):1464780694722]

Looks good to me!

Query for All Note Cards

Now that we have IDs for our Note Types, we can query the database for all the notes for each one and edit them. The temp vars below will be used when scanning the rows. We’re only interested in the card’s id and flds column. The flds column holds all of the data for the note. It doesn’t hold the field names (such as “English”, “Japanese”, “Kanji”, etc), just the data. The field data is separated by 0x1f. Perhaps you noticed the constant from earlier FieldsDelim = "\x1f".

func main() {
	/* previous code goes here */

	// go through each note type and process cards
	for name, mid := range noteTypes {
		log.Printf("Processing cards for %s (ID %d)\n", name, mid)

		// query all cards for given note type
		rows, err := db.Query("SELECT id, flds FROM notes WHERE mid=?", mid)
		if err != nil {
			log.Fatal(err)
		}

		// temp vars
		var id int64
		var flds string

		// cycle through each card
		for rows.Next() {
			/* next on the list */
		}
	}
}

Note Processing

This is where the magic happens!~ First we grab our necessary data from the row and store them in our temp variables from earlier. Split the flds into an array so we can work with our note data. The index of your data will depend on your note type. Edit the following constants for your cards. Then get the data.

const (
	AudioIndex  = 5 // customize based on your field index
	KanjiIndex  = 3 // customize based on your field index
	KanaIndex   = 2 // customize based on your field index
)
for rows.Next() {
	err := rows.Scan(&id, &flds)
	if err != nil {
		log.Fatal(err)
	}

	// split the field data
	fields := strings.Split(flds, FieldsDelim)

	// grab all necessary field data
	kanji := fields[KanjiIndex]
	kana := fields[KanaIndex]
	audio := fields[AudioIndex]
}

Some of my cards already have audio in them and don’t need to be updated, so skip and move on to the next row. Also, if there is no kanji equivalent, I don’t add it into my card. Since its necessary for our download script, assign kanji to kana.

for rows.Next() {
	/* previous code */

	// already have audio, skip
	if audio != "" {
		continue
	}

	// no kanji for card (possibly katakana or word has no kanji)
	if kanji == "" {
		kanji = kana
	}
}

This is were I need to do a bit of clean up before attempting to download. Some of my cards have a する suffix for する verbs and a な suffix for な-adjectives. This will cause an “audio not found” error on JapanesePod101, they need to be removed in order for the download function to work correctly. Because we’re working with Unicode characters, we need to change our strings to []rune to ensure we don’t lose any data. Check out the Go Blog for more info on why.

for rows.Next() {
	/* previous code */

	tmpRune := []rune(kanji)

	if len(tmpRune) >= 3 && tmpRune[len(tmpRune)-1] == 'る' && tmpRune[len(tmpRune)-2] == 'す' {
		kanji, kana = truncate(kanji, kana, 2)
	} else if len(tmpRune) >= 2 && tmpRune[len(tmpRune)-1] == 'な' {
		kanji, kana = truncate(kanji, kana, 1)
	}
}

And here is the truncate function

func truncate(kanji, kana string, size int) (string, string) {
	tmpKanjiRune := []rune(kanji)
	tmpKanaRune := []rune(kana)

	kanji = string(tmpKanjiRune[:len(tmpKanjiRune)-size])
	kana = string(tmpKanaRune[:len(tmpKanaRune)-size])

	return kanji, kana
}

And now we download the audio! Just like our CLI version from before. If there is an error, we don’t want to stop the program. That just means there was an issue on the web sites end, or the word wasn’t found. No big deal. Go through the rest of your notes and deal with the cards that caused an error later.

for rows.Next() {
	/* previous code */

	err = downloadAudio(kanji, kana)
	if err != nil {
		// error downloading file
		// continue marching forward and address afterwards
		fmt.Println(err)
		continue
	}
}

Almost finished now! Now that the audio is downloaded, it’s time to update our cards. We need to update our AudioIndex field to tell Anki to look for a sound file. In Anki thats [sound:FILENAME]. Next, we need to rejoin our fields array to commit to the database. Finally we’ll create a new Card (defined earlier) to house our notes. Once all the processing is finished, we can update the database.

for rows.Next() {
	/* previous code */

	// replace the audio field with an Anki sound URI
	fields[AudioIndex] = fmt.Sprintf("[sound:%s_%s.mp3]", kanji, kana)

	// join our fields back together
	// and assign data to our card type
	// so we can update the DB
	newFields := strings.Join(fields, FieldsDelim)
	cards = append(cards, Card{Fields: newFields, Id: id})

	//dont overload the server now...
	time.Sleep(time.Millisecond * 1250)
}

Also, don’t overload the server…If you go too fast, you can cause an Internal Server error.

All thats left now is to update the database! Again, its pretty straight forward. Go through all the Cards that were processed.

func main() {
	/* previous code */

	// update the DB
	for _, card := range cards {
		stmt, err := db.Prepare("UPDATE notes SET flds=?, mod=?, usn=? WHERE id=?")
		if err != nil {
			log.Fatal(err)
		}

		_, err = stmt.Exec(card.Fields, time.Now().Unix(), -1, card.Id)
		if err != nil {
			log.Fatal(err)
		}
	}
}

Finished!

All Together Now

Below is the whole script.

package main

import (
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"io"
	"log"
	"net/http"
	"os"
	"strconv"
	"strings"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

const (
	FieldsDelim = "\x1f"
	AudioIndex  = 5 // customize based on your field index
	KanjiIndex  = 3 // customize based on your field index
	KanaIndex   = 2 // customize based on your field index
)

var (
	noteTypeNames = [...]string{
		"Japanese Vocab",
		"Japanese Vocab (and reversed card)",
	} // customize for your note types
	noteTypes = make(map[string]int64)       // keep a map of note names and IDs
	models    = make(map[string]interface{}) // unmarshal JSON to map
	modelData []byte                         // raw JSON data from DB
	cards     []Card                         // hold our card types to update the DB after updating
)

type Card struct {
	Fields string
	Id     int64
}

func downloadAudio(kanji, kana string) error {
	BaseURLF := "http://assets.languagepod101.com/dictionary/japanese/audiomp3.php?kanji=%s&kana=%s"

	fullURL := fmt.Sprintf(BaseURLF, kanji, kana)

	//Do the downloading
	resp, err := http.Get(fullURL)
	if err != nil {
		return err
	}

	if resp.StatusCode == 500 {
		return errors.New("Sorry! Internal Server error! Try again in a bit")
	}

	//The audio for this clip is currently unavailable
	if resp.Header.Get("Content-Length") == "52288" {
		return errors.New(fmt.Sprintf("Couln't find audio for %s[%s] -- check kanji/kana", kanji, kana))
	}

	fileName := fmt.Sprintf("%s_%s.mp3", kanji, kana)
	audioFile, err := os.Create(fileName)
	if err != nil {
		return errors.New(fmt.Sprintf("Error creating file %s[%s]: %s", kanji, kana, err))
	}

	defer audioFile.Close()
	defer resp.Body.Close()

	//Write the body to the file
	_, err = io.Copy(audioFile, resp.Body)
	if err != nil {
		return errors.New(fmt.Sprintf("Error saving file %s[%s]: %s", kanji, kana, err))
	}

	return nil
}

func truncate(kanji, kana string, size int) (string, string) {
	tmpKanjiRune := []rune(kanji)
	tmpKanaRune := []rune(kana)

	kanji = string(tmpKanjiRune[:len(tmpKanjiRune)-size])
	kana = string(tmpKanaRune[:len(tmpKanaRune)-size])

	return kanji, kana
}

func main() {
	//open DB
	db, err := sql.Open("sqlite3", "./collection.anki2")
	if err != nil {
		log.Fatal(err)
	}

	// query data and unmarshal
	db.QueryRow("SELECT models FROM col").Scan(&modelData)
	json.Unmarshal(modelData, &models)

	// cycle through models and find the ones we're looking for
	for id, model := range models {
		//convert model to map to access data
		m, _ := model.(map[string]interface{})

		//check if current model matches one in noteTypeNames
		for _, name := range noteTypeNames {
			if name == m["name"] {
				noteTypes[name], err = strconv.ParseInt(id, 10, 64)
				if err != nil {
					log.Fatal(err)
				} // if err
			} // if names
		} // for _, name
	} // for id, model

	log.Printf("Current noteTypes %v\n", noteTypes)

	// go through each note type and process cards
	for name, mid := range noteTypes {
		log.Printf("Processing cards for %s (ID %d)\n", name, mid)

		// query all cards for given note type
		rows, err := db.Query("SELECT id, flds FROM notes WHERE mid=?", mid)
		if err != nil {
			log.Fatal(err)
		}

		// temp vars
		var id int64
		var flds string

		// cycle through each card
		for rows.Next() {
			err := rows.Scan(&id, &flds)
			if err != nil {
				log.Fatal(err)
			}

			// split the field data
			fields := strings.Split(flds, FieldsDelim)

			// grab all necessary field data
			kanji := fields[KanjiIndex]
			kana := fields[KanaIndex]
			audio := fields[AudioIndex]

			// already have audio, skip
			if audio != "" {
				continue
			}

			// no kanji for card (possibly katakana or word has no kanji)
			if kanji == "" {
				kanji = kana
			}

			// my cards have a する suffix for suru verbs and a な suffix for na-adjectives
			// these cause a "audio not found" on JapanesePod101, they need to be removed
			// in order to download the audio correctly
			tmpRune := []rune(kanji)

			if len(tmpRune) >= 3 && tmpRune[len(tmpRune)-1] == 'る' && tmpRune[len(tmpRune)-2] == 'す' {
				kanji, kana = truncate(kanji, kana, 2)
			} else if len(tmpRune) >= 2 && tmpRune[len(tmpRune)-1] == 'な' {
				kanji, kana = truncate(kanji, kana, 1)
			}

			// we good, lets do this shit!
			// download audio
			err = downloadAudio(kanji, kana)
			if err != nil {
				// error downloading file
				// continue marching forward and address afterwards
				fmt.Println(err)
				continue
			}

			// replace the audio field with an Anki sound URI
			fields[AudioIndex] = fmt.Sprintf("[sound:%s_%s.mp3]", kanji, kana)

			// join our fields back together
			// and assign data to our card type
			// so we can update the DB
			newFields := strings.Join(fields, FieldsDelim)
			cards = append(cards, Card{Fields: newFields, Id: id})

			//dont overload the server now...
			time.Sleep(time.Millisecond * 1250)
		}
	}

	// update the DB
	for _, card := range cards {
		stmt, err := db.Prepare("UPDATE notes SET flds=?, mod=?, usn=? WHERE id=?")
		if err != nil {
			log.Fatal(err)
		}

		_, err = stmt.Exec(card.Fields, time.Now().Unix(), -1, card.Id)
		if err != nil {
			log.Fatal(err)
		}
	}
}

Add Everything to Anki

Run your application and you should see something similar to the following.

$ go run anki_in_bulk.go
2018/02/15 09:09:42 Current noteTypes map[Japanese Vocab (and reversed card):1464780694722 Japanese Vocab:1505171166903]
2018/02/15 09:09:42 Processing cards for Japanese Vocab (and reversed card) (ID 1464780694722)
Couln't find audio for お風呂[おふろ] -- check kanji/kana
Couln't find audio for 市民病院[しみんびょういん] -- check kanji/kana
....

And your working directory should be filled with mp3 files!

$ ls *.mp3 | wc -l
635

Hopefully, you will have downloaded audio for most of your notes.

Make sure Anki is CLOSED! Then copy your database back to your Anki folder

cp collection.anki2 /path/to/anki/user/collection.anki2

Now copy all the downloaded files to the media folder

cp *.mp3 /path/to/anki/user/collection.media/.

Open up Anki and everything should sync as normal. Under Tools run the Check Media... and Check Database... options to make sure everything was handled correctly.

Finished! for real this time.

Final Thoughts

I’ve been using both of these scripts a lot when adding new decks of notes to Anki. It’s by no means the best solution, and it does take a bit of work to get everything up and working. I think its a great basis for starting out, if you want to hack away a bit at Anki.

Comments