Get a Complete Set of Logs with Andy Log

The importance of proper logging is undeniable. No, we’re not talking about lumberjack work. … more

Try Some Android Lollipop Applications on Your Device

Android 5.0 Lollipop has been officially announced and lucky users of Google Nexus 5 … more

AutoCon Manages Your Connections to Perserve Battery Life

As we’ve talked about in the past, battery life is still somewhat of a sore … more

How to Unlock and Root a Nexus Device – XDA TV

It is official–Google has released the Nexus 6 and the Nexus 9. The Nexus family … more
Post Reply

[Solved] On the problem of broken bookmarks

OP met67

27th January 2012, 03:22 PM   |  #1  
OP Senior Member
Thanks Meter: 32
 
193 posts
Join Date:Joined: Apr 2007
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):
Code:
        /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:
Code:
>adb devices
You should se something like this:
Code:
* 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):
Code:
>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:
Code:
>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:
Code:
>adb shell
You should get the shell prompt:
Code:
#
5. From the remote shell prompt delete the bookmarks, annotations and reading points that use the 'ean' field:
Code:
# 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:
Code:
# sqlite3 /data/data/com.android.providers.media/databases/internal.db
You sholud get the sqlite prompt:
Code:
sqlite>
From here do: (some lines are quite long, be sure to copy them exactly...)
Code:
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
Code:
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
Last edited by met67; 10th April 2012 at 10:49 AM. Reason: Fixed a small typo on step 2 (tnx to viouviou)
The Following 6 Users Say Thank You to met67 For This Useful Post: [ View ]
27th January 2012, 03:28 PM   |  #2  
Googie2149's Avatar
Senior Member
Thanks Meter: 54
 
288 posts
Join Date:Joined: Jan 2012
More
Well, that explains why it keeps going back to the first page all the time.
28th January 2012, 04:09 PM   |  #3  
OP Senior Member
Thanks Meter: 32
 
193 posts
Join Date:Joined: Apr 2007
Updated OP...
30th January 2012, 12:29 AM   |  #4  
Senior Member
Thanks Meter: 47
 
418 posts
Join Date:Joined: May 2011
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.
30th January 2012, 09:09 AM   |  #5  
OP Senior Member
Thanks Meter: 32
 
193 posts
Join Date:Joined: Apr 2007
Quote:
Originally Posted by 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.

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:
Code:
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...
Last edited by met67; 30th January 2012 at 10:49 AM.
31st January 2012, 01:17 AM   |  #6  
Senior Member
Thanks Meter: 47
 
418 posts
Join Date:Joined: May 2011
Hah!
Nice and easy!

Quote:
Originally Posted by met67

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?

Quote:
Originally Posted by met67

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
31st January 2012, 08:03 AM   |  #7  
OP Senior Member
Thanks Meter: 32
 
193 posts
Join Date:Joined: Apr 2007
Quote:
Originally Posted by ApokrifX

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...
31st January 2012, 09:36 AM   |  #8  
OP Senior Member
Thanks Meter: 32
 
193 posts
Join Date:Joined: Apr 2007
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
31st January 2012, 08:23 PM   |  #9  
Senior Member
Flag California
Thanks Meter: 508
 
637 posts
Join Date:Joined: Nov 2009
More
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.
1st February 2012, 01:57 AM   |  #10  
Senior Member
Thanks Meter: 47
 
418 posts
Join Date:Joined: May 2011
Quote:
Originally Posted by met67

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…

Quote:
Originally Posted by met67

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 ----------

Quote:
Originally Posted by 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.

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.

Post Reply Subscribe to Thread
Previous Thread Next Thread
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Top Threads in Nook Touch General by ThreadRank