[Nook Touch General] met67:
I have added detailed instructions at the end of this post for those willing to try this solution

Hi,

this post is meant to be as a place to discuss the "missing bookmark" problem, spread the findings on its causes and, if possible, find a solution.

As many of you have noticed, the stock reader app seems to behave differently WRT to bookmarks if launched from the library or from the "Last Book" icon on the status bar: often you open the last book only to find that it has lost the bookmark and it starts again from the first page.

I noticed that this behaviour also includes the hilights and annotations: for a given book I have two different sets of hilights and bookmarks which are used if I open the book from the library or from the "Last Book" icon respectively.

The DBs involved with this "feature" of the Reading app are (at least):

/data/data/com.android.providers.media/databases/internal.db
/data/data/com.bn.nook.reader.activities/databases/bookmarks.db
/data/data/com.bn.nook.reader.activities/databases/annotations.db
/data/data/com.bn.nook.reader.activities/databases/readerlocal.db
/data/data/com.bn.nook.reader.activities/databases/lastreadingpoint.db
/data/data/com.bn.nook.home/files/home.db

As far as I can understand, internal.db holds the books info (in the docs table) and the others hold the info on annotations, bookmarks and last reading point. The last reading point seems to be stored in two different table: readerlocal.db and lastreadingpoint.db.

The home.db apparently holds the "now reading" history.

(Note that since I don't live in the States, all my findings relate to sideloaded books, mostly edited with Sigil.)

In the internal.db there are two fields which are of interest in this discussion: the 'ean' field and the 'data' field.

The 'ean' field is populated with info from the <identifier> tag in the content.opf manifet of each book (if there are more tags, ean is the concatenation of these fields).

The 'data' is the path to book's file.

The problem with the reader app seems related to the identifier used to, ehm, identify a book: when you open a book from the Library, it seems to use the 'data' filed, while when you open from the "Last Book" icon, it seems to use the 'ean' fields.

In both cases, this identifier is stored in a 'ean' field in bookmarks and/or annotations db/table, which lead to the two sets problem: if you open from the Library, the file name is used and gives access to one set of bookmarks/annotations; when you open from the incon, the ean used and the other set is accessed.

lastreadingpoint.db and readerlocal.db use a more uniform approach: they have the same fields (actually lastreadingpoint has a 'sync_status' which is not present in 'readerlocal') but lastreadingpoint always uses the 'ean' id, while readerlocal uses the file name. The point is when the Reading app uses info from one db or the other :)

I don't see any real solution to this problem, but we could come up with some workaround to minimize it, eventually with some SQL trick.

This is the info I gathered up to now, feel free to correct me or integrate with your findings...

EDIT (28.1.2012)

I have some more info to share.

After a backup I tried to delete the above mentioned DBs and reboot: the NOOK recreates the DBs and start populating them from scratch.

The "reader" related DBs are created as soon as you open the first book.

The internal.db is repopulated from the epubs found in the internal flash and in the sdcard, BUT the 'ean' field is left empty.

Now, if you delete an epub file and copy it back in place (I usually sync my books from dropbox...), the internal.db is updated with the 'ean'!

EDIT (31.1.2012)

I addedd to internal.db the triggers in post #5, and manually deleted references to eans in the other DBs (using something like "delete from xxx where ean not like 'file:%';") and it seems to have solved the problem.

I won't post detailed, step by step instructions as long as I am not confident that this solution don't have any side-effect.

If you know how to edit SQLite files and feel like giving it a try, post here your results...

EDIT (15.2.2012)

Here you will find a detailed, step by step procedure to create the triggers and try to fix the problem. The usual caveats apply.

PLEASE NOTE THAT THIS PROCEDURE WILL DELETE SOME OF YOUR BOOKMARKS AND ANNOTATIONS (namely those created while reading a book opened from "Now Reading" icon).

You will need a working ADB connection with your Nook Simple Touch (NST), a copy of sqlite3 for Android and some command line skills. I will use a command prompt window under Windows, you will need to make some adjustment for OSX and Linux.

In the following, ">" stands for the command prompt on your PC, "# " stands for the remote shell on your NST and "sqlite> " stands for the SQLite3 shell on your NST.

1. Start connecting your NST with the USB cable and verify that you can reach it via ADB. From the command prompt do:

>adb devices

You should se something like this:

* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached
1112223334445556 device

Now, if you have a working copy of sqlite3 installed in your NST, skip to step 3.

2. Download "sqlite3 (push to n2e).zip" from here and extract "sqlite3" in (e.g.) C:\temp.

Now, from command prompt, do the following (you will be remounting /system partition with rw access, copy sqlite3 in /system/bin, make it executable and change back /system to ro):

>adb shell mount -o remount,rw /dev/block/mmcblk0p5 /system
>adb push C:\temp\sqlite3 /system/bin
>adb shell chmod 755 /system/bin/sqlite3
>adb shell mount -o remount,ro /dev/block/mmcblk0p5 /system

3. From command prompt, make a backup of each file that we are going to modify:

>adb pull /data/data/com.android.providers.media/databases/internal.db C:\temp\internal.db
>adb pull /data/data/com.bn.nook.reader.activities/databases/lastreadingpoint.db C:\temp\lastreadingpoint.db
>adb pull /data/data/com.bn.nook.reader.activities/databases/readerlocal.db C:\temp\readerlocal.db
>adb pull /data/data/com.bn.nook.reader.activities/databases/annotations.db C:\temp\annotations.db
>adb pull /data/data/com.bn.nook.reader.activities/databases/bookmarks.db C:\temp\bookmarks.db

4. From the command prompt connect to a remote shell on your NST:

>adb shell

You should get the shell prompt:

#

5. From the remote shell prompt delete the bookmarks, annotations and reading points that use the 'ean' field:

# sqlite3 /data/data/com.bn.nook.reader.activities/databases/lastreadingpoint.db "DELETE FROM lastreadingpoint WHERE ean NOT LIKE 'file:%';"
# sqlite3 /data/data/com.bn.nook.reader.activities/databases/readerlocal.db "DELETE FROM readerlocal WHERE ean NOT LIKE 'file:%';"
# sqlite3 /data/data/com.bn.nook.reader.activities/databases/annotations.db "DELETE FROM annotations WHERE ean NOT LIKE 'file:%';"
# sqlite3 /data/data/com.bn.nook.reader.activities/databases/bookmarks.db "DELETE FROM bookmarks WHERE ean NOT LIKE 'file:%';"

6. Now connect to the library DB to erase existing eans and create the triggers:

# sqlite3 /data/data/com.android.providers.media/databases/internal.db

You sholud get the sqlite prompt:

sqlite>

From here do: (some lines are quite long, be sure to copy them exactly...)

sqlite> UPDATE docs SET ean = NULL;
sqlite> DROP TRIGGER IF EXISTS erase_ean_update_docs;
sqlite> CREATE TRIGGER IF NOT EXISTS erase_ean_update_docs AFTER UPDATE OF ean ON docs FOR EACH ROW WHEN new.ean IS NOT NULL BEGIN UPDATE docs SET ean = NULL WHERE _id = new._id; END;
sqlite> DROP TRIGGER IF EXISTS erase_ean_insert_docs;
sqlite> CREATE TRIGGER IF NOT EXISTS erase_ean_insert_docs AFTER INSERT ON docs FOR EACH ROW WHEN new.ean IS NOT NULL BEGIN UPDATE docs SET ean = NULL WHERE _id = new._id; END;

7. Close all the connections and reboot

sqlite> .q
# exit
>adb reboot

From now on, each time the Nook sets an EAN for a book, the trigger will immediatly erase it and the Reader application will always use the filename as an identifier.

/met