Nokia PC suite Contacts Database

Have you ever used nokia PC Suite? It is a cool product by Nokia. Among other cool stuff, it can manage your phone contacts. What I noticed, that the program somehow keeps the keeps the contacts and the calendar information, even though the phone is disconnected from the computer.

After looking in some user directories in the computer, I found some files in the Nokia directory and one of them id "PCCSContact.db". The .db extension told me that is might be some kind of database and opening a file in a notepad showed me that the first string of the file is "SQLite format 3". That is very logical. After all, SQLite is being frequently used by the light code developers, such as Apple with iPhone or Google with Offline Gmail extensions and Gears.

For those who did not know:
From wikipedia:

Unlike client–server database management systems, the SQLite engine is not a standalone process with which the application program communicates. Instead, the SQLite library is linked in and thus becomes an integral part of the application program. The library can also be called dynamically. The application program uses SQLite's functionality through simple function calls, which reduces latency in database access as function calls within a single process are more efficient than inter-process communication. The entire database (definitions, tables, indices, and the data itself) is stored as a single cross-platform file on a host machine. This simple design is achieved by locking the entire database file during writing.

The contact data is stored in Boyce-Codd normal form, which, in English, means that it is nice and compact, but less trivial to read. There is of course more data like modified_time_stamp , emails, details, pictures so if you are looking for data mining or forensics, this is an interesting place as well.

Let's say we want to store myself as a contact in such a way:

firstname:Ilya
lastname:Chernyakov
cell:1234567890

-------------------------------
number_data
-------------------------------
uid|number_full |number_type
20 |1234567890 |64

-------------------------------
string_data
-------------------------------
uid|text_data |field_subtype
20 |Ilya |2
20 |Chernyakov |3


As math professors like to say: it is easy to see that tables share the same uid for all the properties of the same contact. Using this SQL query we can read the contact information in a nicer form:

select s.text_data ||' '|| t.text_data as Name , c.number_full as Number
from string_data s, string_data t
join number_data c on s.uid = c.uid
where (s.field_subtype =2 and t.field_subtype = 4)
and s.uid = t.uid
order by 1

I have created a C# dll library that is available at sourceforge. As usual, any comments are welcomed. Please do not use this software to develop nuclear weapons :)

origin
http://programmingatnights.blogspot.com/2010/07/nokia-pc-suite-contacts-database.html