[Om2008.9] How to export Vcf Contacts from FR?

[Vasco Névoa]

Great! Now I know which client to use, let's play!!! :)

I've spent 15 minutes reviving my SQL (which I had forgotten for at 
least 5 years), and this is what I have so far:
*
sqlite3 /home/root/Applications/Qtopia/qtopia_db.sqlite 'Select distinct 
nickname, title, firstname, middlename, lastname, suffix, profession, 
b_webpage, company, office, department, jobtitle, default_email, 
phone_number, h_webpage, spouse, gender, birthday, anniversary from 
contacts, contactphonenumbers where 
contacts.recid=contactphonenumbers.recid;' | sed 's/|/\t/g' > 
addressbook.txt
*
This creates an addressbook "tab-delimited-file" with all the fields I 
thought where important for each contact (some info may be missing, 
check the columns and tables).

Each contact that has more than one phone number will appear multiple 
times because I haven't yet come up with a clean way to show the "join" 
between the "contacts" and "contactphonenumbers" tables, so for now it 
just duplicates the whole line, with the only difference being the phone 
number.

Anyone versed in SQL will be able to hack this into a full VCF file 
generator... or you can just go the Python way (but I prefer to use the 
nice tools already in place) :)

Your turn! ;)

Paul wrote:
> Hey Vasco,
>   
>>> You mean creating VCF's from the sqlite-data in a backup?
>>> Would be interesting to play with. I could envision a slq-script that 
>>> dumps the data into a file and then a bash or python script that puts 
>>> things in the proper format. That's not too difficult, if sqlite plays nice. 
>>>     
>>>       
>> I had thought about that too, but I can't find an SQLite client in OM 
>> repos to create the necessary script.
>> How can we talk to SQLite on OM without going the full C/C++ and 
>> respective libs way? Python maybe?...
>>   
>>     
>
> I found sqlite3 on my desktop pc, which makes things a lot easier. I 
> think that is included on anyone's Linux box these days, and on 
> www.sqlite.com/download there are also precompiled binaries for Mac and 
> Windows. You need sqlite version 3 for the .sqlite files on the FR.
>
> I've been playing a bit with it: copied a .sqlite file from the 
> Freerunner to my machine and using sqlite3 I can pull information from 
> it quite easily:
>
> echo ".tables" | sqlite3 qtopia_db.sqlite
> appointmentcategories   contactpresence         mimeTypeMapping      
> appointmentcustom       contacts                pimdependencies      
> appointmentexceptions   content                 servicehistory       
> appointments            contentProps            simcardidmap         
> callhistory             currentsimcard          simlabelidmap        
> callhistorytimezone     databaseProperties      sqlsources           
> categories              defaultMimeApplication  syncServers          
> categoryringtone        emailaddresses          taskcategories       
> changelog               favoriteservices        taskcustom           
> contactaddresses        googleid                tasks                
> contactcategories       locationLookup          versioninfo          
> contactcustom           mapCategoryToContent 
> contactphonenumbers     mimeTypeLookup       
>
> echo ".dump contactphonenumbers" | sqlite3 qtopia_db.sqlite
> BEGIN TRANSACTION;
> CREATE TABLE contactphonenumbers (     phone_number VARCHAR(100) NOT 
> NULL,     recid INTEGER,     phone_type INTEGER,     FOREIGN KEY(recid) 
> REFERENCES contacts(recid) );
> INSERT INTO "contactphonenumbers" VALUES('04xxxxx875',83886113,1);
> INSERT INTO "contactphonenumbers" VALUES('07xxxxx693',83886209,1);
> INSERT INTO "contactphonenumbers" VALUES('+316xxxx7678',83886277,257);
> INSERT INTO "contactphonenumbers" VALUES('0492xxxx75',83886193,1);
> INSERT INTO "contactphonenumbers" VALUES('118',83886361,1);
> INSERT INTO "contactphonenumbers" VALUES('+3162xxxx44',83886365,1);
> INSERT INTO "contactphonenumbers" VALUES('+3162xxxx233',83886357,1);
> CREATE INDEX contactphonenumbersbytype ON contactphonenumbers 
> (phone_type, phone_number);
> CREATE INDEX contactphonenumbersindex ON contactphonenumbers (recid);
> CREATE INDEX contactphonenumbersnumbers ON contactphonenumbers 
> (phone_number, recid);
> CREATE INDEX contactphnenumberscontacts ON contactphonenumbers (recid, 
> phone_number);
> COMMIT;
>
> I can imagine a python script on the desktop/laptop that would read all 
> the dumps, disect all the insert statements, combine the information 
> based on the recid attribute and after pulling all that together, write 
> out Vcards.
>
> Note that I am using qtopia. I am not certain if the structure on 
> OM2008.x is identical. If that is the case, I can imagine a config file 
> per distribution, mapping attribute-names to the necessary Vcard 
> entries. (I have a lot of imagination.) You'd then run the python script 
> with a parameter telling it what config/mapping to use.
>
> I am sure I can write something like that. I am however not sure how 
> long it would take me, as my order for 36-hour days has still not been 
> fullfilled. *grin*
>
> What do you (or anyone) think of this?
>
> Paul
>
>