Dump sqlite3 address book -- How?

[markfilipak]

TBird has 'lost' the address book (again) <sigh>
'/media/mark/TBird profile/0ysydkep.default/abook.sqlite' exists and is 262.1kB.
'/media/mark/TBird profile/0ysydkep.default/abook.mab' exists and is 9.8kB.

I installed sqlite3.

Code: Select all

mark@mark-VirtualBox:~$ sqlite3 "/media/mark/TBird profile/0ysydkep.default/abook.sqlite"
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width: 
    filename: /media/mark/TBird profile/0ysydkep.default/abook.sqlite
sqlite> .tables
list_cards  lists       properties

How do I dump the address book so I can recover a friend's address?
Is there any way to force TBird to use a human-readable address book?

Greatful for all help.
Mark.

[morat]

Try something like:

Code: Select all

sqlite3.exe abook.sqlite .dump

Code: Select all

sqlite3.exe abook.sqlite "select value from properties where name = 'DisplayName' or name = 'PrimaryEmail'"

SQLite Select Query
http://www.tutorialspoint.com/sqlite/sq ... _query.htm

[markfilipak]

Try something like:

Code: Select all

sqlite3.exe abook.sqlite .dump

Thanks morat. Here's the result.

Code: Select all

mark@mark-VirtualBox:~$ sqlite3 "/media/mark/TBird profile/0ysydkep.default/abook.sqlite" .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE properties (card TEXT, name TEXT, value TEXT);
CREATE TABLE lists (uid TEXT PRIMARY KEY, name TEXT, nickName TEXT, description TEXT);
CREATE TABLE list_cards (list TEXT, card TEXT, PRIMARY KEY(list, card));
CREATE INDEX properties_card ON properties(card);
CREATE INDEX properties_name ON properties(name);
COMMIT;
mark@mark-VirtualBox:~$ 

Code: Select all

sqlite3.exe abook.sqlite "select value from properties where name = 'DisplayName' or name = 'PrimaryEmail'"

And here's the result of that.

Code: Select all

mark@mark-VirtualBox:~$ sqlite3 "/media/mark/TBird profile/0ysydkep.default/abook.sqlite" "select value from properties where name = 'DisplayName' or name = 'PrimaryEmail'"
mark@mark-VirtualBox:~$ 

SQLite Select Query
http://www.tutorialspoint.com/sqlite/sq ... _query.htm

[morat]

Isn't your database empty? There isn't any insert statements.

e.g.

INSERT INTO properties VALUES('8324570e-b348-4d44-89de-131def4e4e64','DisplayName','Alpha Beta');
INSERT INTO properties VALUES('8324570e-b348-4d44-89de-131def4e4e64','FirstName','Alpha');
INSERT INTO properties VALUES('8324570e-b348-4d44-89de-131def4e4e64','LastName',' Beta');
INSERT INTO properties VALUES('8324570e-b348-4d44-89de-131def4e4e64','PrimaryEmail','alpha@beta.org');

[Gone Postal]

Stepping outside Thunderbird on Windows there are various freeware options like "DB Browser for SQlite" which enable you to open an SQlite file and get at the data in a structured format. Are there any similar programs for your operating system which would enable you to find the address book SQlite file in your profile and interrogate the data outside Thunderbird? Or is that not what you are trying to do?

[markfilipak]

Isn't your database empty?

No. I can look into the '.sqlite' file and see there is data. Besides that, the file is 262.1kB.

Stepping outside Thunderbird on Windows there are various freeware options like "DB Browser for SQlite" which enable you to open an SQlite file and get at the data in a structured format.

TBird is running in a Linux virtual machine inside Win7. I'll search for the DB Browser. Thanks.

Are there any similar programs for your operating system ...

Only a typically-cryptic, Linux command line application that's documented for other programmers but not users. That's why I came here.

... which would enable you to find the address book SQlite file in your profile ...

It's '/media/mark/TBird profile/0ysydkep.default/abook.sqlite'.

... and interrogate the data outside Thunderbird? Or is that not what you are trying to do?

Yes, that's exactly what I'm trying to do: recover the email addresses and the names that are in 'abook.sqlite'.

[morat]

Try to recover the database file. Remember to backup.

Recover a corrupt SQLite3 database
http://stackoverflow.com/a/57872238

Thunderbird 102 SQLITE_VERSION is 3.29.0. Maybe your sqlite3.exe file is too old.

Reference
http://searchfox.org/mozilla-esr102/sea ... TE_VERSION

[markfilipak]

Try to recover the database file. Remember to backup.

Recover a corrupt SQLite3 database
http://stackoverflow.com/a/57872238

Thunderbird 102 SQLITE_VERSION is 3.29.0. Maybe your sqlite3.exe file is too old.

Reference
http://searchfox.org/mozilla-esr102/sea ... TE_VERSION

I don't know what I'm doing.

Code: Select all

mark@mark-VirtualBox:~/.thunderbird/0ysydkep.default$ sqlite3 abook.sqlite ".dump" | sqlite3 new.sqlite
mark@mark-VirtualBox:~/.thunderbird/0ysydkep.default$ ls -l
total 308
-rwxrwxrwx 1 mark vboxsf  19845 Jun 10 00:38 abook.mab
-rwxrwxrwx 1 mark vboxsf 262144 Jun 12 23:37 abook.sqlite
-rw-r--r-- 1 mark vboxsf  32768 Aug 13 23:51 new.sqlite

Code: Select all

mark@mark-VirtualBox:~/.thunderbird/0ysydkep.default$ $ sqlite3 -version
3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837balt1
mark@mark-VirtualBox:~/.thunderbird/0ysydkep.default$

[tanstaafl]

See if https://www.recoverytools.com/blog/thun ... converter/ helps.

Its from a company that sells recovery/migration tools for email but it looks like that is one of their free utilities.

[morat]

I would download the SQLite 3.29.0 command line interface for Linux and try the ".recover" command.

SQLite Download Page
https://www.sqlite.org/download.html
https://www.sqlite.org/2019/sqlite-tools-linux-x86-3290000.zip

Code: Select all

cp abook.sqlite broken.sqlite
ls -l *.sqlite
sqlite3 broken.sqlite ".recover" | sqlite3 new.sqlite
sqlite3 new.sqlite ".dump"

A recovered database should have insert statements.

[markfilipak]

Thanks Folks.
Well, I don't know what's in 'abook.sqlite'. I was able to get the names of my correspondants and their email addresses from the 'abook.mab' file, so whatever is in 'abook.sqlite', it apparently doesn't matter. I'm done.

[morat]

I'm glad you got the data.

Thunderbird 68 uses the abook.mab file. That's a Mork formatted file.

Thunderbird 78 (and newer) uses the abook.sqlite file.