eDGe Library files and backup structure

[ken_jennings]

The entourage forum site appears to be having issues. It's currently insanely slow and sometimes never responds. So, I'm creating this thread here.

There was recently a thread on the entourage site about a request to tech support to extract the backed up library contents to a different edge. Per entourage tech support it could not be done. Excluding DRM'd files the regular content created by the user -- annotations, journals, etc. -- are retrieveable.

This thread is a work in progress to identify the contents of the backup and explain how the Library databases work.

[ken_jennings]

This is an attempt to document the structure of the eDGe library and the files that are backed up by the Local Backup application. Since this is undocumented by entourage, most of what is written here is based on supposition from looking at the file contents and analyzing data made visible with tools. In some places the purpose is just guess work, because the actual content can't be precisely identified.

Local backup of the library creates a file ending in .esi which is actually just a plain zip file. Rename the file extension to .zip and the zip/unzip utilities on Linux and Windows should be able to examine and manipulate the file. There is no password and no apparent encryption of the file itself.

My edge has the library moved to the external SD card. The .esi/.zip file produced contains two *absolute* paths, /fridge/ and /sdcard/. (This absolute path part may be an issue later when restoring a backup that has had the contents manipulated with zip/unzip on a PC.)

Here is a list of all the directories containing one or more files in the .esi/.zip file:

/fridge/.edge/.esi-data/.databases/
/fridge/.edge/.esi-data/.edb/
/fridge/.edge/.esi-system/
/fridge/.edge/.esi-system/.fontconfig/
/sdcard/.edge/.esi-content/
/sdcard/.edge/.esi-content/.esa/

On initial perusal it appears /fridge/ contains a series of sqllite databases. Some contain configuration information, but most are related to library files and used to link separate files together -- books to annotations, etc.

All the real content -- PDFs, annotations, Journals -- are stored under the /sdcard/ path.

Both /fridge/ and /sdcard/ contain only the directory named /.edge/

Several directories contain a file called ".nomedia":

/sdcard/.edge/.esi-content/.nomedia
/fridge/.edge/.esi-system/.nomedia
/fridge/.edge/.esi-data/.databases/.nomedia
/fridge/.edge/.esi-data/.edb/.nomedia

The file contains the text "prevent media scan". A guess: It seems to be a flag, possibly to stop a tool from scanning the filesystem any farther to locate content that could be added to the Library.

[ken_jennings]

/fridge/.edge/.esi-system>

.esi-system/ appears to be the base for system configuration.

/fridge/.edge/.esi-system>
-rw-r--r-- 1 kenjen users   44 2011-04-26 20:58 calib_values
drwxr-xr-x 2 kenjen users 4096 2011-04-26 23:49 .fontconfig/
-rw-r--r-- 1 kenjen users   18 2011-04-26 20:58 .nomedia

The calib_values file seems to be values collected by screen calibration. It is mostly text, but there are a couple non-ascii bytes near the end of the file:

90 19 3876 -404792 -3858 8 78557968 65536^@6^@

The .fontconfig/ directory has information about fonts. The files are too small to contain fonts, so it seems to be a reference file for fonts installed on the system.

/fridge/.edge/.esi-system/.fontconfig>
-rw-r--r-- 1 kenjen users 12456 2011-04-26 20:58 3336a65c52528c9c368e942d3dd307f8-arm.cache-2
-rw-r--r-- 1 kenjen users    72 2011-04-26 20:58 f430392a5d118517a13e2b04e09d101a-arm.cache-2

The files contain mostly binary data with some text strings labeling fonts. The "file" command on linux identifies both files generically as "data" files:

^Dü^Bü^B^@^@^@¨0^@^@ ^@^@^@0^@^@^@^@^@^@^@0^@^@^@^F±ûL/system/fonts^@^@^@^H^@^@^@^H^@^@^@^Q^@^@^@^@^@^@^@1^@^@^@Q^F^@^@É    ^@^@©^^^@^@)"^@^@É%^@^@i)^@^@^A-^@^@^T^@^@^@^T^@^@^@^P^@^@^@ÿÿÿÿ^A^@^@^@¡^@^@^@^B^@^@^@É^@^@^@^C^@^@^@é^@^@^@^D^@^@^@   ^A^@^@^E^@^@^@!^A^@^@^F^@^@^@I^A^@^@^G^@^@^@a^A^@^@^H^@^@^@y^A^@^@  ^@^@^@<91>^A^@^@^N^@^@^@©^A^@^@^U^@^@^@É^A^@^@^V^@^@^@  ^B^@^@^X^@^@^@!^B^@^@^Y^@^@^@9^B^@^@!^@^@^@Q^B^@^@"^@^@^@¹^D^@^@#^@^@^@ñ^D^@^@$^@^@^@   ^E^@^@%^@^@^@1^E^@^@(^@^@^@Y^E^@^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@^Y^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@Droid Sans^@^@^@^@^@^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@^Y^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@en^@^@^@^@^@^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@^Y^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@Bold^@^@^@^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@Éÿÿÿ^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@^Y^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@Droid Sans Bold^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@yÿÿÿ^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@^@^@^@^@^@È^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^A^@^@^@^@^@^@^@d^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@^Y^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@unknown^@^@^@^@^@^@^@^@^@^C^@^@^@^@^@^@^@^Y^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@/system/fonts/DroidSans-Bold.ttf^@^@^@^@...

[ken_jennings]

/fridge/.edge/.esi-data>

.esi-data contains two directories, both contain a series of sqlite databases:

/fridge/.edge/.esi-data>
drwxr-xr-x 2 kenjen users 4096 2011-04-26 23:49 .databases/
drwxr-xr-x 2 kenjen users 4096 2011-04-26 23:49 .edb/

The .databases/ directory contains files for system configuration and the base library database.

The .edb/ directory contains individual database files relating to the documents added to the library.

.databases/

/fridge/.edge/.esi-data/.databases>
-rw-r--r-- 1 kenjen users 153600 2011-04-26 21:00 library.db
-rw-r--r-- 1 kenjen users     18 2011-04-26 20:58 .nomedia
-rw-r--r-- 1 kenjen users    334 2011-04-26 20:58 recent-files
-rw-r--r-- 1 kenjen users      1 2011-04-26 20:58 state.check
-rw-r--r-- 1 kenjen users 139264 2011-04-26 20:58 state.db

The Linux "file" command identifies the library.db and state.db files as SQLite databases:

/fridge/.edge/.esi-data/.databases> file library.db state.db
library.db: SQLite 3.x database, user version 10
state.db:   SQLite 3.x database, user version 2

The state.check file on my system contains only the text character "0". It might be a lock file, or a flag to identify when a consistency check or update is needed on the state.db database.

/fridge/.edge/.esi-data/.databases> cat state.check
0

The recent-files appears to contain a list of recent files either opened or added to the Library, here it just shows a series of journals. The list relates the absolute path and actual filename used in the library content to the friendlier name given the file when it was saved. Given the amount of data kept in the library database it is somewhat strange to find this flat text file here:

/fridge/.edge/.esi-data/.databases> cat recent-files
/sdcard/.edge/.esi-content/jrnl_4db6f4d2.esj jrnl_test2 112
/sdcard/.edge/.esi-content/jrnl_4db6f4f5.esj jrnl_test 113
/sdcard/.edge/.esi-content/jrnl_4da30bc0.esj 2011-04-11_StaffMeeting -1
/sdcard/.edge/.esi-content/jrnl_4d99d342.esj 2011-04-04_StaffMeeting -1
/sdcard/.edge/.esi-content/jrnl_4d909853.esj 2011-03-28StaffMeeting -1

[ken_jennings]

/fridge/.edge/.esi-data/.databases>

If you have sqlite3 available check out the Library database contents:

/fridge/.edge/.esi-data/.databases> sqlite3 library.db
SQLite version 3.6.23
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .database
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/kenjen/Desktop/edge_backup/fridge/.edge/.esi-data/.d

One database, but lots of interesting tables:

sqlite> .tables
android_metadata  esibookmarks      esihighlights     esinotes
esiannotations    esicontent        esiimages         esirecent
esiattachments    esicontenttag     esimeta           esitags

Start at the top with android_metadata, it is simple -- one column, and has only one record. It appears to identify this system's "locale" as english in the US:

sqlite> .schema android_metadata
CREATE TABLE android_metadata (locale TEXT);

sqlite> select count(*) from android_metadata ;
1

sqlite> select * from android_metadata ;
en_US

esibookmarks is a more complicated table, but I have no data in it:

sqlite> .schema esibookmarks
CREATE TABLE "esibookmarks" (
"_id"         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"type"        INTEGER,
"contentId"   INTEGER,
"sectionId"   INTEGER,
"location"    TEXT,
"page"        INTEGER,
"title"       TEXT,
"previewText" TEXT,
"guid"        TEXT,
"version"     TEXT,
"created"     INTEGER,
"modified"    INTEGER,
"backup"      INTEGER,
"dirty"       INTEGER );

sqlite> select count(*) from esibookmarks ;
0

sqlite> select * from esibookmarks ;

Just about everything here has the _id column in it. That is the primary key on most tables and the value used to connect records in different tables together.

Let's see what has records and what doesn't. It seems I'm not big on making bookmarks or attachments:

sqlite> select count(*) from esibookmarks ;
0
sqlite> select count(*) from esihighlights ;
49
sqlite> select count(*) from esinotes ;
3
sqlite> select count(*) from esiannotations ;
114
sqlite> select count(*) from esicontent ;
104
sqlite> select count(*) from esiimages ;
13
sqlite> select count(*) from esirecent ;
10
sqlite> select count(*) from esiattachments ;
0
sqlite> select count(*) from esicontenttag ;
82
sqlite> select count(*) from esimeta ;
1
sqlite> select count(*) from esitags ;
25

PROBABLY IMPORTANT: The esimeta table appears to identify the specific edge device. This table has only one record in it that contains the system's serial number. This may be the only value that prevents a backup on one system from being restored on a different edge. Chances are, updating the value of the "devices" column here to match a different, target eDGe device, then updating the backup .esi/.zip file with the new library.db file would allow the backup file to be restored on a different edge. Pseudi-semi-educated guess at work.

sqlite> .schema esimeta
CREATE TABLE "esimeta" (
"_id"         INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"libraryName" TEXT,
"device"      TEXT);

sqlite> select * from esimeta ;
1|My Library|F303082002AF

No, that is not my system's actual serial number.


esiimages is an interesting table. It stores an image directly in the record in "blob" format. It would be interesting to know how these records are created and what needs to be stored here:

sqlite> .schema esiimages
CREATE TABLE "esiimages" (
"_id"   INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"image" BLOB);

sqlite> select * from esiimages ;
1|�PNG
^Z

2|
3|
4|
5|�PNG
^Z
. . .  etc, more of the same

TO BE CONTINUED

[Filark]

Thank you, ken_jennings!!

I hope I never need to fully understand what you've posted, but I am very grateful that you have gone to the trouble.

I always figure knowing where to find the answer is as good (or better! ) than actually knowing the answer. So, thanks to you, I know where to look if I need it!

[Gunnerp245]

@ken_jennings

Most excellent!

[mrspaceman]

I've done some work on this:

http://andy.aspellclark.org.uk/eDGeLibraryNotes.doc

My edge has been faulty since Feb, but I may be getting a replacement next week.
I don't know if I'll use it though (I now have an Advent Vega and a Blackberry Playbook).

[Filark]

Welcome, mrspaceman!

I saw you were back in the entourageedge.com forum. Sorry about your eDGe. Have you done any more work/thinking about Bibliotecha (did I spell that right?)?

[WDM.Amy]

I tried modifying the library.db file with the other device's S/N but did not have any luck getting it to restore from backup. Any other ideas?