Kindle Paperwhiteの単語帳DB（vocab.db）から英単語を抽出

Kindle Paperwhiteの単語帳

Kindle Paperwhiteで英語の本を読んでいるときにわからない英単語を辞書で調べると自動的に単語帳に追加される。単語帳に追加された英単語はフラッシュカード機能で復習ができて便利だが、Kindle Paperwhiteは電子ペーパーということもありサクサク操作できない。
単語帳に登録されている英単語をCSVファイルで出力できればAnkiアプリなどで使えるので調べてみる。

単語帳データベースvocab.db

Kindle Paperwhiteで調べた英単語はvocab.dbというデータベースに登録されているらしい（世代により違うのかもしれないが）のでMacで確認してみる。
まず、Kindle PaperwhiteとPCをUSBケーブルで接続する。Kindle Paperwhiteを認識したらターミナルアプリを起動して、下記コマンドでデータベースを適当なフォルダにコピー。

$ cp /Volumes/Kindle/system/vocabulary/vocab.db ~/tmp

vocab.dbはSQLiteなのでテーブル情報を出力してみる。

sqlite> .tables
BOOK_INFO  DICT_INFO  LOOKUPS    METADATA   VERSION    WORDS

sqlite> .schema
CREATE TABLE WORDS (
　　id TEXT PRIMARY KEY NOT NULL,
　　word TEXT,
　　stem TEXT,
　　lang TEXT,
　　category INTEGER DEFAULT 0,
　　timestamp INTEGER DEFAULT 0,
　　profileid TEXT
);
CREATE TABLE LOOKUPS (
　　id TEXT PRIMARY KEY NOT NULL,
　　word_key TEXT,
　　book_key TEXT,
　　dict_key TEXT,
　　pos TEXT,
　　usage TEXT,
　　timestamp INTEGER DEFAULT 0
);
CREATE TABLE BOOK_INFO (
　　id TEXT PRIMARY KEY NOT NULL,
　　asin TEXT,
　　guid TEXT,
　　lang TEXT, title TEXT, authors TEXT);
CREATE TABLE DICT_INFO (
　　id TEXT PRIMARY KEY NOT NULL,
　　asin TEXT,
　　langin TEXT,
　　langout TEXT
);
CREATE TABLE VERSION (
　　id TEXT PRIMARY KEY NOT NULL,
　　dsname TEXT,
　　value INTEGER
);
CREATE TABLE METADATA (
　　id TEXT PRIMARY KEY NOT NULL,
　　dsname TEXT,
　　sscnt INTEGER,
　　profileid TEXT
);
CREATE INDEX wordprofileid ON WORDS (profileid);
CREATE INDEX lookupwordkey ON LOOKUPS (word_key);
CREATE INDEX lookupbookkey ON LOOKUPS (book_key);

このページにAnkiアプリ向けに英単語を抽出するSQL文があるので実行してみると、以下のような結果が得られる。

SELECT
　　w.word
　　,group_concat(l.usage || '<i><small>' || b.title || '</i></small>' ,' <br/><br/> ') as usage
　　,count(l.usage) as count_usage
FROM 
　　WORDS w
　　LEFT JOIN LOOKUPS l on l.word_key=w.id
　　LEFT JOIN BOOK_INFO b on b.guid=l.book_key
GROUP BY
　　w.word
HAVING
　　--to be updated according to what you want to learn: filter on words that you have looked-up at least twice for example
　　count(l.usage)>=2

banged|He tried to jam it into the earth, but the blade banged against the ground and bounced off without making a dent. <i><small>Holes</i></small> <br/><br/> His head banged against the table as he fell to the ground. <i><small>Holes</i></small>|2
blotchy|Now her face was blotchy, and her hair was dirty and scraggly. <i><small>Holes</i></small> <br/><br/> Her skin looked blotchy. <i><small>Holes</i></small>|2
　…

このSQL文（HAVING以下はなくてもよい）で英単語、用例、タイトル情報を出力することができるが、辞書を引いた和訳はそもそもデータベースには登録されていないらしい。別の方法で和訳を取得する必要があるが、例えば下記リポジトリでは翻訳サービスのAPIを叩いて訳を取得しているっぽい。ということで、このあたりを参考にすれば何とかなりそう。

GitHub - rampage644/kindle-to-anki: Kindle to anki export
GitHub - smlance/kindletoanki: Puts single-word Kindle highlights into an Anki deck with their definitions.