[FAQ][A] About Contacts Import/Export, Backup/Restore and Data/Sync

# mount 
...
rootfs / rootfs ro,relatime 0 0
/dev/block/mmcblk0p9 /system ext4 ro,relatime,barrier=1,data=ordered 0 0
/dev/block/mmcblk0p10 /data ext4 rw,nosuid,nodev,noatime,...,discard 0 0
/dev/block/mmcblk0p4 /mnt/.lfs j4fs rw,relatime 0 0
/dev/block/vold/179:11 /mnt/sdcard vfat rw,dirsync,...,utf8,...,discard 0 0
...

mount -o remount,rw -t ext4 /dev/block/mmcblk0p9 /system

mount -o remount,rw -t yaffs2 /dev/block/mtdblock3 /system

find / -iname '*sqlite*'

cp <path_to_your_sqlite3_binary> /system/bin/sqlite3 
chmod 4755 /system/bin/sqlite3
chown root.shell /system/bin/sqlite3

# sqlite3 -version
3.6.22

#!/bin/sh
# squint.sh - sqlite3 install script for rooted Androids
#
#-rwsr-sr-x root  shell    22228 2011-11-10 12:53 /system/bin/su
#-rwsr-xr-x root  shell  1075144 2011-12-15 16:55 /system/xbin/busybox
#-rw-r--r-- root  root    322120 2011-09-14 14:45 /system/lib/libsqlite.so

# Go to your "sqlite" folder on your (internal) SD card:
cd /mnt/sdcard/sqlite/

cp sqlite3 /system/xbin/sqlite3
chmod 4755 /system/xbin/sqlite3
chown root.shell /system/xbin/sqlite3

cp ./lib/libncurses.so /system/lib/libncurses.so
chmod 644 /system/lib/libncurses.so
chown root.root /system/lib/libncurses.so

find / -iname '*.db'

find /system -iname '*.db' |grep ontact

# cd /data/data/com.android.providers.contacts/databases
 # sqlite3 contacts2.db
 
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> .databases

.databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /data/data/com.android.providers.contacts/databases/contacts2.db

sqlite> .tables

.tables
_sync_state                       settings
_sync_state_metadata              speed_dial
accounts                          status_updates
activities                        v1_settings
agg_exceptions                    view_contacts
android_metadata                  view_contacts_restricted
calls                             view_data
contact_entities_view             view_data_restricted
contact_entities_view_restricted  view_groups
contacts                          view_raw_contacts
data                              view_raw_contacts_restricted
groups                            view_v1_contact_methods
mimetypes                         view_v1_extensions
name_lookup                       view_v1_group_membership
nickname_lookup                   view_v1_groups
packages                          view_v1_organizations
phone_lookup                      view_v1_people
properties                        view_v1_phones
raw_contacts                      view_v1_photos

sqlite> SELECT _id,name, number FROM view_v1_phones;

[...]

sqlite> .bail ON
sqlite> .headers ON
sqlite> .mode csv
sqlite> .output my_contacts_db.csv
sqlite> SELECT _id,name, number FROM view_v1_phones;
sqlite> .output stdout
sqlite> .quit

sqlite3 -csv contacts2.db "SELECT _id,name,primary_email,number FROM view_v1_phones;"
sqlite3 -csv contacts2.db "SELECT _id,mimetype_id,raw_contact_id,data1 FROM data;"
sqlite3 -csv contacts2.db "SELECT _id,display_name FROM raw_contacts WHERE account_type='vnd.sec.contact.phone';"
sqlite3 -csv contacts2.db "SELECT data1 FROM data WHERE raw_contact_id=112 AND mimetype_id=5;"

sqlite3 -csv -header contacts2.db "SELECT _id,package_id,mimetype_id,raw_contact_id,is_primary,is_super_primary,data_version,data1,data2,data3 FROM data WHERE raw_contact_id=21;"
sqlite3 -csv -header contacts2.db "SELECT _id,package_id,mimetype_id,raw_contact_id,is_primary,is_super_primary,data_version,data1,data2,data3 FROM data WHERE is_primary=0 AND data_version=0;"

#SELECT data1 FROM data WHERE raw_contact_id=140 AND mimetype_id=1;
#SELECT data1 FROM data WHERE raw_contact_id=140 AND mimetype_id=5 AND data2=2;
#SELECT data1 FROM data WHERE raw_contact_id=112 AND mimetype_id=5 AND data2=7;
#SELECT data1 FROM data WHERE raw_contact_id=112 AND mimetype_id=5 AND data2=1;

Specify the application used for organizing the data:
-----------------------------------------------------------
vnd.sec.contact.phone           - Default Android phone book
com.facebook.auth.login         - Facebook contacts

sed ':a;N;$!ba;s/\n/,/g'

#!/bin/sh
# contex.sh - Extracts the Name,Phone,Email from the Android Contact Database
#==============================================================================
# Last Change:     2011-12-29
# Name:            Android DB Contacts Exporter
#
# Description:  Extracts contacts from the Android contacts database 
#               to a CSV file for import into other contact list tools.
#
# Usage:    ./contex.sh <output_filename> &
# 
# How to use:  
#
# 1. Copy your Android Contacts Database to your computer:
#    adb pull /data/data/com.android.providers.contacts/databases/contacts2.db ./
# 2. Run this file from the same directory as contacts2.db
#     ./contex.sh output_filename.csv
#
# System Requirements:  sh, sqlite3, sed
# 
# Additional Notes:
#
#    1. This script is very slow, please be patient!
#
#    2. The sed sequence to replace the end on line (\n) with a comma (,) 
#       from a list, but avoiding \n on the last line is:
# 
#       sed ':a;N;$!ba;s/\n/,/g'
#       --- OR ---  
#       sed '{:q;N;s/\n//g;t q}'
# 
#    3. Only numbers that are of the types: HOME, MOBILE, WORK and OTHER 
#       get extracted in this script. These types are specified by the 
#    "data2" field in the "data" table.
# 
#==============================================================================
echo; echo "Android contacts backup script started with PID: $$ "; echo;
DATE=`date +%G%m%d`

#----------------------------------------------------------
# Input and Output files:
#----------------------------------------------------------
CONTACTSDB="./contacts2.db"
if [ ! -f "$CONTACTSDB" ]
then
    echo "Contacts Database file: $CONTACTSDB not found. Exiting."; echo
    exit 1
fi

OUTFILE=$1
if [ -z "$OUTFILE" ]
then
    OUTFILE="contex_backup_${DATE}.csv"
fi
echo "Contacts backup file: $OUTFILE "; echo

if [ -f "$OUTFILE" ]
then
    echo "Contacts Backup file: $OUTFILE already exist. Exiting."; echo
    exit 1
fi

#----------------------------------------------------------
# Write the Table Header:
#----------------------------------------------------------
echo "First Name,Last Name,E-mail,Mobile Phone,Home Phone,Work Phone,Other Phone" >> $OUTFILE

#----------------------------------------------------------
# Setting up SQLite command and mode:
#----------------------------------------------------------
#CMD="sqlite3 -bail -noheader -csv" 
CMD="sqlite3";

#----------------------------------------------------------
# Get the raw IDs of all contacts:
#----------------------------------------------------------
RAWID_LIST=`$CMD $CONTACTSDB "SELECT _id from raw_contacts WHERE account_type='vnd.sec.contact.phone'"`
#echo "$RAWID_LIST"

#----------------------------------------------------------
# Loop through the IDs and extract all necessary data
#----------------------------------------------------------
for RAWID in $RAWID_LIST; do

    # Get the First_Name and Last_Name in one query:
    # NOTE: Need to remove occasional CTRL-M's with sed.
    NAME=`$CMD -csv $CONTACTSDB "SELECT data2, data3 FROM data WHERE raw_contact_id=$RAWID AND mimetype_id=6;" | sed ':a;N;$!ba;s/\n//g'`

    # Get the Email address:
    # NOTE: This will simply append multiple email adresses to one long string, with ";" separator!
    EMAIL=`$CMD $CONTACTSDB "SELECT data1 FROM data WHERE raw_contact_id=$RAWID AND mimetype_id=1;" | sed ':a;N;$!ba;s/\n/;/g'`

    # Here we get the "Home", "Mobile", Work" and "Other" (labeled) phone numbers.
    # NOTE: Each of these may return multiple responses, so we need to convert new-lines (\n) to ",".
    HPHONE=`$CMD $CONTACTSDB "SELECT data1 FROM data WHERE raw_contact_id=$RAWID AND mimetype_id=5 AND data2=1;" | sed ':a;N;$!ba;s/\n/,/g'`;
    MPHONE=`$CMD $CONTACTSDB "SELECT data1 FROM data WHERE raw_contact_id=$RAWID AND mimetype_id=5 AND data2=2;" | sed ':a;N;$!ba;s/\n/,/g'`;
    WPHONE=`$CMD $CONTACTSDB "SELECT data1 FROM data WHERE raw_contact_id=$RAWID AND mimetype_id=5 AND data2=3;" | sed ':a;N;$!ba;s/\n/,/g'`;
    OPHONE=`$CMD $CONTACTSDB "SELECT data1 FROM data WHERE raw_contact_id=$RAWID AND mimetype_id=5 AND data2=7;" | sed ':a;N;$!ba;s/\n/,/g'`;
    PHONES="$MPHONE,$HPHONE,$WPHONE,$OPHONE";

    # If at least one main field is found then write the CSV line
    if [ -n "$NAME" ] || [ -n "$EMAIL" ] || [ -n "$PHONES" ]; then
        #echo "ID: $RAWID"
        echo -n "$NAME,$EMAIL,$PHONES" >> $OUTFILE
        echo >> $OUTFILE
    fi

done
echo 'Done.'