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
> [email protected]
> http://lists.openmoko.org/mailman/listinfo/community
>
_______________________________________________
Openmoko community mailing list
[email protected]
http://lists.openmoko.org/mailman/listinfo/community
