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

nickd blumph at gmail.com
Thu Oct 16 05:00:24 CEST 2008


Vasco I've put your contribution up on the wiki. Once step closer to 
working out the export. Also in the discussion I've asked for thoughts 
on either moving 'exporting' to a new page or rename the current one. 
What do you think?
-Nick

Vasco Névoa wrote:
> 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
>>
>>   
>>     
>
>
> _______________________________________________
> Openmoko community mailing list
> community at lists.openmoko.org
> http://lists.openmoko.org/mailman/listinfo/community
>   





More information about the community mailing list