Vocabulary builder reset ????

[NiLuJe]

The sqlite3 client is bundled on the device, no need to use a third-party binary .

EDIT: Meh, just realised this was in the userstore, not var... My bad, ;P.

[arad26]

Originally Posted by dtp

There is db file in /system/vocabulary/vocab.db
If you remove the file and restart kindle it won't help, file will be downloaded from Amazon, but if you alter db and after restart kindle it will do.

To alter db use these commands:

this removes mastered words
sqlite3 vocab.db "DELETE FROM WORDS where category=100;"
this will remove not mastered words
sqlite3 vocab.db "DELETE FROM WORDS where category=0;"
this removes word references to books
sqlite3 vocab.db "DELETE FROM LOOKUPS where id!='blabla';"

sqlite3 you can download from https://sqlite.org/download.html
linux or mac most certainly will have it installed.

Could you please explain step by step how to make this on the pw 2? Thank you

[dtp]

Originally Posted by arad26

Could you please explain step by step how to make this on the pw 2? Thank you

I've got PW1, so I don't know if PW2 has different db, but I doubt it so you try and do as I've said.

If you've got linux or mac, skip this step, your OS will have this program already, if not, download it.

1. Go to https://sqlite.org/download.html and download Precompiled Binaries for ___
sqlite-shell-linux-x86-3080403.zip
sqlite-shell-osx-x86-3080403.zip
sqlite-shell-win32-x86-3080403.zip

2. Unarchive it.

3. I strongly suggest you copy [mounted Kindle device]/system/vocabulary/vocab.db to different dir just in case.

4. Open command line / shell / cmd.exe in dir where you have vocab.db and sqlite3 / sqlite3.exe

5. Run commands to achieve what do you want: (change sqlite3 to sqlite3.exe on windows)

this removes mastered words (if you want to remove only them)
sqlite3 vocab.db "DELETE FROM WORDS where category=100;"
this will remove not mastered words
sqlite3 vocab.db "DELETE FROM WORDS where category=0;"
this removes word references to books
sqlite3 vocab.db "DELETE FROM LOOKUPS where id!='blabla';"

If you want complitly reset vocabluary run all three commands.

[arad26]

Thank you for the guidance . I have another question for you. I contacted Amazon about this and the kindle specialist said that all the data from the VB is stored on their server, and that is why even if i do a factory reset after i connect the kindle to wifi all the words are back . So i would like to know if i delete the data this way, after i connect to wifi it will stay clean or it will get back the data from the Amazon's servers?

[dtp]

Originally Posted by arad26

Thank you for the guidance . I have another question for you. I contacted Amazon about this and the kindle specialist said that all the data from the VB is stored on their server, and that is why even if i do a factory reset after i connect the kindle to wifi all the words are back . So i would like to know if i delete the data this way, after i connect to wifi it will stay clean or it will get back the data from the Amazon's servers?

As I've said - yes it will stay clean. If it would be simply up to the point of connection to wifi you could've simply deleted the db file. If you remove db file amazon-server thinks that there something wrong and reuploads db back, because typical kindle-user do not go anywhere but /documents and many do not go anywhere. If you remove entries from db, amazon-server will think that you manually deleted words yourself and will do nothing about it.

[arad26]

Originally Posted by dtp

As I've said - yes it will stay clean. If it would be simply up to the point of connection to wifi you could've simply deleted the db file. If you remove db file amazon-server thinks that there something wrong and reuploads db back, because typical kindle-user do not go anywhere but /documents and many do not go anywhere. If you remove entries from db, amazon-server will think that you manually deleted words yourself and will do nothing about it.

THANK YOU THANK YOU SOO MUCH FOR THIS! Amazon could not offer me a solution to this and you could. I just did it and it works like a charm.

[timlevin]

dtp --
Thanks for posting the sqlite3 instructions for updating the Kindle vocabulary.
I ran these two instructions, only, because I wanted to preserve the mastered words:

# this will remove not mastered words
sqlite3 vocab.db "DELETE FROM WORDS where category=0;"
# this removes word references to books
sqlite3 vocab.db "DELETE FROM LOOKUPS where id!='blabla';"

After I ejected the Kindle and reset it, I was a little surprised that the mastered words were deleted, in addition to the not mastered and book-related words.

Do you have any idea what I did wrong to remove the mastered words?
Cheers,
Tim

[Doitsu]

Originally Posted by timlevin

dtp --
I ran these two instructions, only, because I wanted to preserve the mastered words:

# this will remove not mastered words
sqlite3 vocab.db "DELETE FROM WORDS where category=0;"
# this removes word references to books
sqlite3 vocab.db "DELETE FROM LOOKUPS where id!='blabla';"

After I ejected the Kindle and reset it, I was a little surprised that the mastered words were deleted, in addition to the not mastered and book-related words.

Do you have any idea what I did wrong to remove the mastered words?

You've most likely executed the wrong line. If you've Firefox installed on your machine, you can use the free SQLite Manager addon to delete entries in a more controlled manner. (Since SQLite Manager searches only for databases with an .sqlite extension by default, you'll need to select All files (*.*) from the file type drop-down list box.)

In SQLite Manager, select the Words table, click Browse & Search and delete the entries that you no longer need. (You can sort by status or language, by clicking the category or lang column headings.)

It goes without saying that you may want to create a backup of vocab.db before modifying it with SQLite Manager.

[Doitsu]

Originally Posted by NiLuJe

The sqlite3 client is bundled on the device, no need to use a third-party binary .

Would it be possible to write an on-device shell script with sqlite3 queries to extract information from vocab.db and save it in a text file in the documents folder?

[NiLuJe]

@Doitsu: I haven't looked at the db layout, but yeah, that's probably doable.

[Tx65]

Found this thread via google while looking for a way to delete my vocabulary builder. The SQLite Manager addon for firefox mentioned above worked nicely (You can also put the SQL commands in there and execute them). However afterwards you have to compress the database, because even if you delete the contents of WORDS and LOOKUPS, they apparently are still in there somewhere. After deletion and compression the vocabulary builder is finally empty.

Btw, I've tried to simply delete the database from my kindle because I thought it would create a new one when I look up a new word, but no, it did not. When you simply delete vocab.db from your kindle, the vocabulary builder won't work any more (at least if you keep your device offline. Otherwise it will download the db from the Amazon servers if I'm not mistaken).

[Doitsu]

I've created a quick & dirty KUAL extension with 4 menu options that execute sqlite3 queries:

Hide all (moves all words from Learning to Mastered)

sqlite3 /mnt/us/system/vocabulary/vocab.db "UPDATE WORDS SET category=100 WHERE category=0;"

Unhide all (moves all words from Mastered to Learning)

sqlite3 /mnt/us/system/vocabulary/vocab.db "UPDATE WORDS SET category=0 WHERE category=100;"

Delete all (deletes all words)

sqlite3 /mnt/us/system/vocabulary/vocab.db "DELETE FROM WORDS;"
sqlite3 /mnt/us/system/vocabulary/vocab.db "DELETE FROM LOOKUPS;"
sqlite3 /mnt/us/system/vocabulary/vocab.db "DELETE FROM DICT_INFO;"
sqlite3 /mnt/us/system/vocabulary/vocab.db "DELETE FROM BOOK_INFO;"

Export all (exports all entries from the WORDS and LOOKUPS tables)

sqlite3 /mnt/us/system/vocabulary/vocab.db "SELECT * FROM WORDS where category=0;" > /mnt/us/unknown_words.txt
sqlite3 /mnt/us/system/vocabulary/vocab.db "SELECT * FROM WORDS where category=100;" > /mnt/us/known_words.txt
sqlite3 /mnt/us/system/vocabulary/vocab.db "SELECT * FROM LOOKUPS;" > /mnt/us/lookups.txt

To obtain a more SRS friendly layout, replace the code in export.sh with the following two lines:

echo "lang|word|stem|usage|authors|title|category" > /mnt/us/vocab.txt
sqlite3 /mnt/us/system/vocabulary/vocab.db "SELECT WORDS.lang, word, stem, usage, authors, title, category  FROM LOOKUPS JOIN WORDS on LOOKUPS.word_key=WORDS.id JOIN BOOK_INFO on LOOKUPS.book_key=BOOK_INFO.id;" >> /mnt/us/vocab.txt

This will generate the following output:

lang|word|stem|usage|authors|title|category
en|quaffed|quaff|The quarter-bottles of cheap wine had been quaffed [...]|Frederick Forsyth|The Veteran|0

(Unfortunately, Paperwhites don't store the actual dictionary definitions in vocab.db.)

The extension works fine on my PW2, however, since none of the sqlite3 queries are reversible and I'm not a programmer, users are strongly recommended to create a backup copy of vocab.db (in /system/vocabulary/) before testing this KUAL extension.

[astone42]

I tried to manually reset my kindle vocab.db using:

"DELETE FROM WORDS;"
"DELETE FROM LOOKUPS;"
"DELETE FROM DICT_INFO;"
"DELETE FROM BOOK_INFO;"

But after restart it keeps downloading the old db from amazon servers. Any idea ?

[Doitsu]

Originally Posted by astone42

I tried to manually reset my kindle vocab.db using:
But after restart it keeps downloading the old db from amazon servers. Any idea ?

You could try deleting all entries in Airplane mode and then look up a random word immediately after deleting all entries. When you disable Airplane mode afterwards, Whispersync shouldn't overwrite the most recent version of vocab.db on your device. However, I haven't tried this myself, since I use my PW in Airplane mode to prevent it from auto-updating to the very latest version, which messes with the jailbreak.

[mrevent]

(an interim question: how do we correctly add the "timestamp" to the categories listed above in the code?

SELECT WORDS.lang, word, stem, usage, authors, title, category

I add ',timestamp' to the sequence above but terminal does not accept 'timestamp'
(timestamp is the category name in the squalite browser that refers to the date a word is looked up).
)