Exporting Bookmarks from FBReader

rixx

2019-07-19

Old illustration of an old man carrying a lot of books in his arms

Carrying all those books is something I sure don't miss. Source.

I read a lot, and I read a lot across different devices. Usually, I have at least a book open on my Kindle, and one on my mobile phone. I also like to highlight passages that make me laugh, or think, or that touch me in some way. I collect some of those quotes in a fortune(6) file for further usage: Put it on login shells, share it with friends, use it as a tongue-in-cheek Patreon reward.

The Kindle just drops your highlights into a plain text file with a very readable structure, which is very pleasant to work with. It turned out that my Android reader app, FBReader isn't nearly so cooperative, so this post documents how to extract the data from an unrooted phone.

Excuses

First off, I should mention a few things: FBReader actually does support sending your bookmarks (which is what they call highlights) from your phone – if you use their cloud sync. The sync also includes all your books and your reading positions, by way of Google Drive.

This is not an option for me: I'm very private about what I read, and when, and what I highlight or comment on in those books, and I don't want to share this information with anybody. It's very personal, and doesn't belong in the hands of anybody out there. Yes, that means that my Kindle, the little snitch that it wants to be, runs exclusively in offline mode since the day I got it.

That doesn't imply that I don't want to talk about books – I share what I read here on my blog as well as on GoodReads. But those are places where I decide who sees my opinion, and my choices, and my pacing. I don't include all books I read, and I typically fudge my reading times a bit (mostly by being lazy about reviewing in a timely manner). Just syncing all information doesn't afford me this degree of choice.

So, long story short: Online sync is not an option. But my phone is a god-damn Linux-ish computer, it must be possible to retrieve some data, right? Well.

Retrieval

Sadly, the source code for current versions of FBReader is not available any longer (though the older versions are still unmaintained on GitHub), so we can't just fix the open issue about bookmarks export and ask the developers to merge it. Instead I assumed that this app will do what basically every app of this size and shape does: Put all data into an sqlite database in /data.

Groundwork

First off, you'll need adb. The Android Debug Bridge is our command line interface to talk with the phone. It can do a lot of things, like providing port-forwarding, providing file transfer to and from a phone, giving shell access to the phone (a regular unix shell – it's nice to feel at home on your phone, so I'd encourage you to use this feature to take a look!), installing and uninstalling applications, granting app permissions, and running a host of tools for backups, security, debugging, and general scripting. It's a very versatile and pretty well-documented tool! It's usually available via a package called android-tools or adb.

You'll also have to enable USB debugging in your phone's developer settings. Then, when you plug in your phone into your PC, authorize the connection. You'll have to unlock your phone before executing adb commands – a very reasonable security feature to make sure nobody accesses your phone without your consent.

Transfer

Getting data out of /data isn't quite trivial though, especially since the app is not debuggable. /data is protected, so we can't just waltz in with an adb shell and copy it (though you presumably can do this with a rooted phone). If we had access to a debug build of the application, we could use adb shell to use the run-as com.fbreader command to perform sudo-like command execution.

Instead, we'll use the adb backup feature that allows us to extract application data:

adb backup -noapk com.fbreader

After confirming the transaction on your phone, you'll see a file called `backup.ab` locally on your PC.

Extraction

You might think that this is where we're done. Oh, if only. We'll have to extract a usable file archive first. Information on the internet mentions a lot of "run this openssl zlib command":

dd if=backup.ab bs=24 skip=1 | openssl zlib -d > backup.tar

Since my openssl doesn't come with zlib, I was tempted to run the fallback internet advice:

dd if=backup.ab bs=1 skip=24 | python2 -c "import zlib,sys;sys.stdout.write(zlib.decompress(sys.stdin.read()))" >backup.tar

But when I saw that this command actually required Python 2 (with its deprecation rapidly approaching), I grew a bit stubborn – there had to be a better way.

[Turns out](https://android.stackexchange.com/questions/28481/how-do-you-extract-an-apps-data-from-a-full-backup-made-through-adb-backup/78183#78183), you can just prepend a proper tar archive header to convince `tar` to extract the archive!

( printf "\x1f\x8b\x08\x00\x00\x00\x00\x00" ; tail -c +25 backup.ab ) |  tar xfvz -

It will complain about an unexpected file end and report an unrecoverable error (because we're missing the footer checksums), but it will nonetheless extract all data. Now you'll see a directory called apps, and you can head directly to apps/com.fbreader/db, where the books.db file will contain all we want to see.

Output

Now we've got a sqlite database, which means we're basically done. The tables of interest to us are appropriately called Books, Authors, and Bookmarks. Turns out I had read 136 books by 80 authors, and marked 164 quotes.

This is how you can extract the quotes directly into a fortunes format after you access the database with sqlite3 books.db:

SELECT Bookmarks.bookmark_text || char(10) || ' - ' || Authors.name || ', ' || Books.title || char(10) || '%'
FROM Bookmarks
  JOIN Books on Bookmarks.book_id=Books.book_id
  JOIN BookAuthor on Books.book_id=BookAuthor.book_id
  JOIN Authors on BookAuthor.author_id=Authors.author_id;

That's it!

Edit [2020-08-17]: If you're seeing duplicates in your export, try this more complex query provided by Alberto Corni – thank you!

SELECT bookmark_text || char(10) || ' - ' || Authors_name || ', ' || title || char(10) || '%'
FROM (
  SELECT
    Books.title,
    Bookmarks.bookmark_text,
    (
      SELECT group_concat(Authors.name)
      FROM BookAuthor
      JOIN Authors on BookAuthor.author_id=Authors.author_id
      WHERE Books.book_id=BookAuthor.book_id
    ) as Authors_name
  FROM Bookmarks
  JOIN Books on Bookmarks.book_id=Books.book_id
  where Bookmarks.visible = 1
) as subq;