[Solved] On the problem of broken bookmarks

[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

 

[Googie2149]

Well, that explains why it keeps going back to the first page all the time.

 

[met67]

Updated OP...

 

[ApokrifX]

I was wondering about same thing!
Not sure, if it was introduced first in 1.1.0, I do not recall such behavior in early versions.
As for solution, it shouldn’t be a problem to write a app/script to duplicate rows.
I.e. if there is a row with 'ean' field, but there is no one 'data' field with same content -> duplicate.
And vice-versa.

Also interesting, is there a way to find command line arguments used to lunch reader app from
Library and "Last Book" icon (com.bn.nook.reader.activities)

If nook sources are all available, it could be possible to recompile broken component.

And the last:
Anyway to get in touch with developers and submit a bug?
You have pretty much nailed it down already - it should be piece of cake to fix.

 

[met67]

I was wondering about same thing!
Not sure, if it was introduced first in 1.1.0, I do not recall such behavior in early versions.
As for solution, it shouldn’t be a problem to write a app/script to duplicate rows.
I.e. if there is a row with 'ean' field, but there is no one 'data' field with same content -> duplicate.
And vice-versa.

Also interesting, is there a way to find command line arguments used to lunch reader app from
Library and "Last Book" icon (com.bn.nook.reader.activities)

If nook sources are all available, it could be possible to recompile broken component.

And the last:
Anyway to get in touch with developers and submit a bug?
You have pretty much nailed it down already - it should be piece of cake to fix.

I was thinking of a more drastic approach: during my tests, I found that as long as I had no 'ean' in my internal.db/docs, everything worked fine (or at least it did seem to, maybe I didn't test it long enough), so I was thinking of adding a trigger to internal.db that systematically erase 'ean' on each update
I don't know how this would interfere with books purchased in B&N shop, though.

As for B&N, I don't think they are much interested in making the life easier for people who sideload book instead of buying from their shop
Moreover, I am not sure that this behaviour is not a side effect of rooting and using a different home from stock's

EDIT:
I was thinking to something like this:

UPDATE docs SET ean = NULL;

DROP TRIGGER IF EXISTS erase_ean_update_docs;
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;

DROP TRIGGER IF EXISTS erase_ean_insert_docs;
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;

I guess I'll have to somehow reset the others DBs, also.

I cant' test it now, I'll try it and report...

 

[ApokrifX]

Hah!
Nice and easy!

I don't know how this would interfere with books purchased in B&N shop, though.

You can get a free book from B&N shop and test, right?

As for B&N, I don't think they are much interested in making the life easier for people who sideload book instead of buying from their shop
Moreover, I am not sure that this behavior is not a side effect of rooting and using a different home from stock's

I don’t think so. (i.e. it’s not rooting).
Just tested with free B&N and side-loaded books.
The former keeps bookmarks, the later doesn’t…

---------- Post added at 02:17 AM ---------- Previous post was at 02:11 AM ----------

I’d use new.rowid instead of new._id in “WHERE _id = new._id;”
But that’s me

 

[met67]

Hah!
Nice and easy!


You can get a free book from B&N shop and test, right?


I don’t think so. (i.e. it’s not rooting).
Just tested with free B&N and side-loaded books.
The former keeps bookmarks, the later doesn’t…

---------- Post added at 02:17 AM ---------- Previous post was at 02:11 AM ----------

I’d use new.rowid instead of new._id in “WHERE _id = new._id;”
But that’s me

Last time I tried, the shop wouldn't let me download free books without a valid credit card in my account

As for the id, that's the way B&N uses to address rows in other triggers, so I tend to use the same approach...

 

[met67]

I just added the triggers, and it seems to works fine...

If someone brave enough does try this solution, please report here: if it works fine for other people, I'll prepare a step by step guide.

/met

 

[GabrialDestruir]

I think a better long term fix would be posting your findings in the Barnes and Noble Support forums, a B&N admin and/or dev may actually see it there and they could incorporate this into whatever next update comes out making it available to all users not just those of us who have rooted.

 

[ApokrifX]

Last time I tried, the shop wouldn't let me download free books without a valid credit card in my account

I use virtual CC from CITI with $1 limit…

As for the id, that's the way B&N uses to address rows in other triggers, so I tend to use the same approach...

rowid supposed to be a little faster. Doesn’t matter in this case of course.
And a little bit clearer (for me).

Asked in another thread, will ask here again:
BTW: How do you fiddle with databases such as internal.db?
Use adb to pull/push and fiddle on PC, or use adb shell to do it on nook?

---------- Post added at 02:57 AM ---------- Previous post was at 02:53 AM ----------

I think a better long term fix would be posting your findings in the Barnes and Noble Support forums, a B&N admin and/or dev may actually see it there and they could incorporate this into whatever next update comes out making it available to all users not just those of us who have rooted.

I suggested very same thing early.
And I have very same feeling met67 has: they don’t care about side-loaded books, might be even make thing harder on purpose.

 

[GabrialDestruir]

I use virtual CC from CITI with $1 limit…
I suggested very same thing early.
And I have very same feeling met67 has: they don’t care about side-loaded books, might be even make thing harder on purpose.

It still might be worth a try to see if we can get them to fix it.

 

[ApokrifX]

It still might be worth a try to see if we can get them to fix it.

I'm not against it

BTW: Wanna throw in an idea:
It was/is a thread about "Automating shelving" http://xdaforums.com/showthread.php?t=1378510

It’s piece of cake to with very same insert trigger, the shelves are in same internal.db

---------- Post added at 04:43 AM ---------- Previous post was at 04:14 AM ----------

Started working on it and realized, met67 was talking about bookmarks, but I meant annotations. (annotations.db)
I have tons of annotations, plagued by same bug: some have ean, some filepath…
And I kept wondering, I did put a note here, where the heck did it go?
And then they showed up all of the sudden and then disappeared same way.
Crap…

So fix script should be smth. like this:
Update bookmarks.db and annotations.db - replace ean with filepath (lookup in internal.db)
Than clear ean in internal.db.
Any comments?

 

[met67]

...
BTW: How do you fiddle with databases such as internal.db?
Use adb to pull/push and fiddle on PC, or use adb shell to do it on nook?
...

Both, actually.
I use SQLiteStudio to browse data and definitions, and a copy of sqllite3 found on this forum to modify the DB on the device via adb shell

 

[met67]

...
BTW: Wanna throw in an idea:
It was/is a thread about "Automating shelving" http://xdaforums.com/showthread.php?t=1378510

It’s piece of cake to with very same insert trigger, the shelves are in same internal.db
...

Yes, I also thought about this, but having tried the "Automatic shelving" (with a script) for a while, I found that shelves tend to be too much slow and I am not sure it is worth the effort (IMO, of course).

So fix script should be smth. like this:
Update bookmarks.db and annotations.db - replace ean with filepath (lookup in internal.db)
Than clear ean in internal.db.
Any comments?

Yes, I think this should work. I just erased bookmarks and annotation related to ean, since I don't have much of them.

 

[ApokrifX]

Both, actually.
I use SQLiteStudio to browse data and definitions, and a copy of sqllite3 found on this forum to modify the DB on the device via adb shell

I.e. you don't have sqllite apk installed on Nook.
I thought, it'll be a bit easier...

---------- Post added at 07:35 PM ---------- Previous post was at 07:31 PM ----------

Yes, I also thought about this, but having tried the "Automatic shelving" (with a script) for a while, I found that shelves tend to be too much slow and I am not sure it is worth the effort (IMO, of course).

If you have many books, it’s real PiA to scroll to find the one you need. Or it’s just me?

Yes, I think this should work. I just erased bookmarks and annotation related to ean, since I don't have much of them.

Ok. I’ll work on scripts and post them here.

 

[ApokrifX]

Tried to submit bug report to B&N - apparently it’s was done 09-15-2011 05:10 PM already.
http://bookclubs.barnesandnoble.com...y-Manager/Nook-Touch-bug-reports/td-p/1081244
If you follow thread – only first bug was forwarded by Admin to developers.

Read last post: “I can't begin to describe my frustration that this was not fixed in the last update…“
That’s about it - thread looks abandoned - there is no use to post there.

I can add - for a developer it should take from 15 min to 1 hour to fix such a bug.
IMO, of course...

BTW: Anybody seen a NST firmware release report with all fixed and not bugs, as other companies do (Cisco, Juniper, etc.)?
Although, it’s probably done for different market segments, definitely not for “personal customer” one.

 

[met67]

I.e. you don't have sqllite apk installed on Nook.
I thought, it'll be a bit easier...

If you mean SQLite Editor, yes, I have it on the Nook, but is a bit hard to use (scrolling tends to behave errantly).

If you have many books, it’s real PiA to scroll to find the one you need. Or it’s just me?

Yes, but, at least for me, having a shelf for each author is an even worst PiA.
ATM I have 326 book in My Library in 55 pages.
If I use a shelf for each author, I have 139 shelves in 70 pages!

 

[GabrialDestruir]

Reading through this the issue seems to be something somewhere is applying ean in some places and filepath in another. While an initial script to change it all to one or another would be beneficial we really need to find where it's assigning either or both of those values so that we can try and modify the code to remove either ean or filepath from being assigned.

The second thing and more related to automatic shelving than to broken bookmarks is if we could find and disable the code that for some reasons says when new books get added, shelves get emptied out and/or deleted.

---------- Post added at 08:45 AM ---------- Previous post was at 08:40 AM ----------

If you mean SQLite Editor, yes, I have it on the Nook, but is a bit hard to use (scrolling tends to behave errantly).



Yes, but, at least for me, having a shelf for each author is an even worst PiA.
ATM I have 326 book in My Library in 55 pages.
If I use a shelf for each author, I have 139 shelves in 70 pages!

I personally think that arranging books by series (not author or some other method) would make it easier, or at the very least by Read/Unread/Reading then you could collapse a shelf you're not looking through and have less books.

 

[ApokrifX]

Reading through this the issue seems to be something somewhere is applying ean in some places and filepath in another. While an initial script to change it all to one or another would be beneficial we really need to find where it's assigning either or both of those values so that we can try and modify the code to remove either ean or filepath from being assigned.

Since it is very same reader app, I’d guess, it is started with different arguments from “last book” and from library.
“ps -ef”, “ps -x” supposed to show full command line (i.e. app + arguments), but it doesn’t...
Anyway to see full command line to prove?

---------- Post added at 04:21 AM ---------- Previous post was at 04:11 AM ----------

>ATM I have 326 book in My Library in 55 pages.
>If I use a shelf for each author, I have 139 shelves in 70 pages!
I personally think that arranging books by series (not author or some other method) would make it easier, or at the very least by Read/Unread/Reading then you could collapse a shelf you're not looking through and have less books.

I didn’t think about that many books…
I’d say, multi-level shelves might be the way to go - first by series, than by author, etc.
Although, if I think a little bit, anyway I organize it, won’t be necessarily good for somebody else.

It shouldn’t be shelves, but some sort of filtering/drill-down built in into library:
I select 1st criteria (any metadata field, like series, [optionally, with wildcard]), then filter by 2nd (author), etc.

…Sorry, got carried away

---------- Post added at 04:26 AM ---------- Previous post was at 04:21 AM ----------

If you mean SQLite Editor, yes, I have it on the Nook, but is a bit hard to use (scrolling tends to behave errantly).

I meant command line only (on nook).
To use GUI on PC to develop/debug scripts, then exec on nook via command line sqlite.

 

[met67]

I meant command line only (on nook).
To use GUI on PC to develop/debug scripts, then exec on nook via command line sqlite.

As I was saying, I use a copy of sqlite3 found on this forum to exec SQL commands on nook. It is not an apk, just a simple executable: place it in a suitable place (e.g. /bin), chmod +x it and have fun