Subject
The iPhone 3G is delivered, running some form of UNIX, with crippled bluetooth facility. The manufactuer states that the phone will pair with headsets, only. For me, this meant the comfort of beaming contacts to my telephone was at end.The manufacturer's solution is to pair their iStore with my credit card; and, then manage my phone with the popular iTunes program. The program itself, is a shopping mall. iTunes, of course, runs on Mac OSX and Windows operating environments.
iTunes present source repositories, outlook, google, yahoo, i think. Another venue for updating the iPhones's contacts, independent of manufactuer constraint, is covered by Funambol - mobile open source, and SyncEvolution. The latter methods include uploading your personal information to a web based service, with a client that can obtain the data from that service. A Linux workstation to iPhone relationship should be possible using the SyncEvolution construct with a co-located web server.
Scope
It is well known that the iPhone's Address Book is stored in an SQLite3 database. The sample implement, documented here, covers writing a well behaved .vcf record into a known, initialized, AddressBook.sqlitedb. Some tables are covered here, yet not initialized by the coded example.The example implement does not cover delete or update of contact entries, or ABGroups. The documentation, herein, will address table content, only. No other database objects are addressed (e.g., triggers, indexes, sequences).
No attempt is made to recover SQLite reply (return value or dialog) for all commands issued it.
Object
A native Linux implement is considered to move personal contact information from a previous cell phone (Cingular 8125), to the iPhone 3G.Configuration
The Cingular 8125 can transfer its contacts, individually, over bluetooth and infrared, to my Linux laptop (a Fedora Core 6 based install). Now remains, transfering the contact to the iPhone. To complete this leg of the transfer for the example, a "jailbroken" iPhone is used.Consult one of the iPhone Jailbrake methods for complete documentation and instruction telling how to jailbrake your iPhone. Before jailbraking your iPhone, read the cons for undertaking such an action. This writing assumes a jailbroken iPhone 3G.
Install OpenSSH on your iPhone. Please find detailed instructions, and an install URL link at
Cydia->Home>Users Guide - OpenSSH Access How-ToThough optional to the scope of this writing, VNC Server (Veency) is an invaluable daemon, allowing display of, and interaction with, the iPhone's native UI. At your iPhone, install
Cydia->Manage->Sources->Telesphoreo Tangelo->V->Veency.Install sshfs (fuse) and vncviwer support on your Linux workstation, if required. Install sqlitestudio (or another Sqlite3 manager), to aid in troubleshooting and review of your contacts database, at your linux workstation. The script to insert vcf records into the Sqlite3 database is written in Korn Shell. Install a ksh package (e.d., ksh-20060214 or pdksh-5.2.14-21).
The Program: insert a version 2.1 or 3.0 vcard into the iPhone's AddressBook
This script is written for ksh (posix 1003.2 committee, draft 6). There are manipulatons and constructs that apply to Korn Shell, rather than Bash. Notice the ksh co-process, pattern substitution and string manipulation.#!/bin/ksh
Functions are defined to convert interger to character (itoc) and character to interger (ctoi). Each are useful to encode and decode text values to be stored in the database columns, FirstSort and LastSort.
function itoc {
echo -e "\0$(printf "%o\n" ${1})"
}
function ctoi {
printf "%d\n" "'${1}'"
}
There are three sort encoded fields stored in the FirstSort and LastSort columns of the AddressBook.sqlitedb database, when keying a contact at the iPhone console. The calculator functions, below, coin the sort operations, Code, Count and Upper. Each of the coined fields, optionally include subfields, encoding Firstname, Lastname and Organization. Other fields (e.g., Middlename, Dept, Jobtitle) may be possible to include. But these subfields are not considered, or coded for, here.
Each sort function will return a string to include in the FirstSort and LastSort columns. Notice that the iPhone requires at least one of Firstname, Lastname and Organization to sort a contact entry.
sortCode translates the characters of a column to characters expected by the iPhone sorting method. The translation is seen to be a simple alphabetic projection onto the ASCII character vector. That is,
AbcdefghijkLmnopqrsTuvwxyz, translates to -/13579;=?ACEGIKMOQSUWY[]_. Begining at ASCII character '-' (44 decimal), every other character is the next alphabet in the sequence; so, 44 + (2*index)-1.
Digits are coded '*' followed by a similar projection, starting at 142 decimal, every other character is the next digit. That is, 142+(2*digit).
Special characters are ignored. White space, and the start of the next column, will yield the subfield separater, ASCII STX (start of text, 2 decimal).
function sortCode {
d=
while (( ${#1} ));do
case "${1:0:1}" in
[[:blank:]] ) d=${d}$(itoc 2);;
[[:alpha:]] ) (( i = 43 + 2*(($(ctoi ${1:0:1})&95)-64) ));
d=${d}$(itoc ${i});;
[[:digit:]] ) (( i = 142 + 2*($(ctoi ${1:0:1})-48) ));
d=${d}'&'$(itoc ${i});;
esac
set "${1:1}"
done
printf "%s\n" "${d}"
}
sortCount counts the characters of a column expected by the iPhone sorting method. White space or the start of the next column will yield the subfield separater, ASCII STX (start of text, 2 decimal).
function sortCount {
let i=0
d=
while (( ${#1} )); do
case "${1:0:1}" in
[[:blank:]] ) (( i )) && {
d=${d}$(itoc i+4)
d=${d}$(itoc 2)
let i=0
}
;;
[[:digit:]] ) let i=i+1;;
[[:alpha:]] ) let i=i+1;;
esac
set "${1:1}"
done
(( i )) && d=${d}$(itoc i+4)
printf "%s\n" "${d}"
}
sortUpper encodes uppercase character positions. 143 decimal represents the postion of an uppercase character. If there are a number of lowercase characters bounded by another uppercase character, the number of lowercase characters is subtracted from 134 decimal. The uppercase character is, again, coded 143. The final count of lowercase characters, for the last subfield, is included.
White space or the start of the next column will yield the subfield separater, ASCII STX (start of text, 2 decimal).
function sortUpper {
let i=0
d=
while (( ${#1} ));do
case "${1:0:1}" in
[[:blank:]] ) (( i )) && {
d=${d}$(itoc i+4)
d=${d}$(itoc 2)
let i=0
};;
[[:upper:]] ) (( i )) && d=${d}$(itoc 134-i);
d=${d}$(itoc 143);
i=0;;
[[:lower:][:digit:]] ) let i=i+1;;
esac
set "${1:1}"
done
(( i )) && d=${d}$(itoc i+4)
printf "%s\n" "${d}"
}
At least for Oracle database, the NULL is a mightly and powerfull thing. The setToken function will assign a value (the second argument) to its first argument, or set NULL if no value exists. If the resulting assignment is not NULL, the value will be enclosed in quotes - suitable for an SQL string value.
function setToken {
eval "${1}=\$(echo \${2:-NULL})"
eval [[ "\${${1}}" != NULL ]] && eval ${1}="\'\$${1}\'"
}
addPerson parses some VCard fields for inserting a persons contact entry into the abperson table of AddressBook.sqlitedb. Text table columns for First, Last, Middle, Oganization, Department, Note, Birthday TEXT and JobTitle are parsed. Notice the '-' was particular trouble when included in text strings requiring sort encoding.
function addPerson {
setToken fn "$(awk -F'[:;]' '$1=="N"{print $3}' ${vcf}|tr '-' ' ')"
setToken mn "$(awk -F'[:;]' '$1=="N"{print $4}' ${vcf}|tr '-' ' ')"
setToken ln "$(awk -F'[:;]' '$1=="N"{print $2}' ${vcf}|tr '-' ' ')"
setToken FN "$(awk -F'[:;]' '$1=="FN"{printf("%s",$2)}' ${vcf})"
setToken org "$(awk -F'[:;]' '$1=="ORG"{print $2}' ${vcf}|tr '-' ' ')"
setToken dept "$(awk -F'[:;]' '$1=="ORG"{print $3}' ${vcf})"
setToken jt "$(awk -F'[:;]' '$1=="TITLE"{print $2}' ${vcf})"
setToken note "$(awk -F'[:;]' '$1=="X-MICROSOFT-OFFICELOC"{print $2}' ${vcf})"
The date fields used in the iPhone's contact field, represent seconds since Jan 1 2001 00:00:00 (thanks to Oliver Weber, podzone@freedom-net.de). The birth date parse require special handling, not provide in setToken.
With the name and organization data parsed from the VCard, the firstname, lastname and organization sort coding can be calculated. Strings for firstname sort, firstname count and firstname case mapping are collected to, later, form the FirstSort and LastSort aggregates. The same is done for lastname, and organization.
bday="$(awk -F':' '/^BDAY;/{print $2}' ${vcf}|tr 'Tt' ' ')"
(( ${#bday} )) &&
bday="'$(( $(date -d "${bday}" "+%s")-$(date -d "Jan 1 2001" "+%s") ))'" ||
bday=NULL
Each VCard parse is tested for textual content, and if data is present for the firstname, it will be include in the sort aggregate.
[[ ${fn} != NULL ]] && {If data is present for lastname, it will be included in the aggregate.
FNS="$(sortCode "${fn}")"
FNC="$(sortCount "${fn}")"
FNU="$(sortUpper "${fn}")"
} || FNS='' FNC='' FNU=''
[[ ${ln} != NULL ]] && {Too, if data is present for Organization, it will be included in the aggregate. Each of firstname, lastname and organization are optional; however, one of these fields must be included for the iPhone to sort the contact entry.
LNS="$(sortCode "${ln}")"
LNC="$(sortCount "${ln}")"
LNU="$(sortUpper "${ln}")"
} || LNS='' LNC='' LNU=''
Here, the firstname, lastname and organization sort codes ${fns} are collected to form the sort aggregate for FirstSort. This aggregate is inserted into the abpersonsearchkey, with implication that firstname sorting is configured for the iPhone sort ordering. If the iPhone is configured for lastname sort order, the database table insert should include the lastname sort code agregate, ${lns}.
[[ ${org} != NULL ]] && {
ORGS="$(sortCode "${org}")"
ORGC="$(sortCount "${org}")"
ORGU="$(sortUpper "${org}")"
} || ORGS='' ORGC='' ORGU=''
Firstname counting and uppercase mapping are calculated.
fns="${FNS}"
(( ${#FNS} )) && (( ${#LNS} )) && fns="${FNS}"$(itoc 2)
(( ${#LNS} )) && fns="${fns}${LNS}"
(( ${#fns} )) && (( ${#ORGS} )) && fns="${fns}"$(itoc 2)
(( ${#ORGS} )) && fns="${fns}${ORGS}"
print -p "
insert into abpersonsearchkey values(
(select max(rowid) from abperson)+1,'${fns}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
fnc="${FNC}"And, finally, the collections are aggregated to form the firstname sort, FirstSort, column value. Each of the subfields, firstname code, count and case are appended, in respective order, and separated by ASCII SOH (start of header, 1 decimal).
(( ${#FNC} )) && (( ${#LNC} )) && fnc="${FNC}"$(itoc 2)
(( ${#LNC} )) && fnc="${fnc}${LNC}"
(( ${#fnc} )) && (( ${#ORGC} )) && fnc="${fnc}"$(itoc 2)
(( ${#ORGC} )) && fnc="${fnc}${ORGC}"
fnu="${FNU}"
(( ${#FNU} )) && (( ${#LNU} )) && fnu="${FNU}"$(itoc 2)
(( ${#LNU} )) && fnu="${fnu}${LNU}"
(( ${#fnu} )) && (( ${#ORGU} )) && fnu="${fnu}"$(itoc 2)
(( ${#ORGU} )) && fnu="${fnu}${ORGU}"
Again, what is calculated for firstname is, too, calculated for lastname. And, in the event of lastname sorting (not this sample), can be used in kind. To sort by lastname, include the sort coding entry (${lns}, first subfield calculation) in table abpersonsearchkey, opposed to firstname sort coding.
(( ${#fns} )) && (( ${#fnc} )) && fns="${fns}"$(itoc 1)
(( ${#fnc} )) && fns="${fns}${fnc}"
(( ${#fns} )) && (( ${#fnu} )) && fns="${fns}"$(itoc 1)
(( ${#fnu} )) && fns="${fns}${fnu}"
lns="${LNS}"
(( ${#LNS} )) && (( ${#FNS} )) && lns="${LNS}"$(itoc 2)
(( ${#FNS} )) && lns="${lns}${FNS}"
(( ${#lns} )) && (( ${#ORGS} )) && lns="${lns}"$(itoc 2)
(( ${#ORGS} )) && lns="${lns}${ORGS}"
# If you want lastname sorting, use the following, and comment out the firstname searchkey
# print -p "
# insert into abpersonsearchkey values(
# (select max(rowid) from abperson)+1,'${lns}'
# );
# select '.end';
# "
#
# while read -p row; do
# [[ ${row} = \.end ]] && break
# print "${row}"
# done
lnc="${LNC}"
(( ${#LNC} )) && (( ${#FNC} )) && lnc="${LNC}"$(itoc 2)
(( ${#FNC} )) && lnc="${lnc}${FNC}"
(( ${#lnc} )) && (( ${#ORGC} )) && lnc="${lnc}"$(itoc 2)
(( ${#ORGC} )) && lnc="${lnc}${ORGC}"
lnu="${LNU}"
(( ${#LNU} )) && (( ${#FNU} )) && lnu="${LNU}"$(itoc 2)
(( ${#FNU} )) && lnu="${lnu}${FNU}"
(( ${#lnu} )) && (( ${#ORGU} )) && lnu="${lnu}"$(itoc 2)
(( ${#ORGU} )) && lnu="${lnu}${ORGU}"
(( ${#lns} )) && (( ${#lnc} )) && lns="${lns}"$(itoc 1)
(( ${#lnc} )) && lns="${lns}${lnc}"
(( ${#lns} )) && (( ${#lnu} )) && lns="${lns}"$(itoc 1)
(( ${#lnu} )) && lns="${lns}${lnu}"
And, creation and modification dates are calculated for inclusion in the contact record. Notice that Apple Computer has coded dates for the iPhone's AddressBook.sqlilte as seconds since January 1, 2001 00:00:00. The Linux computer will likely store dates as seconds since January 1, 1970 00:00:00. Simply, the difference between Linux and iPhone seconds for date values is 978307200 seconds.
cdate="$(( $(date "+%s")-$(date -d "Jan 1 2001" "+%s") ))"
mdate="${cdate}"
The root record for a contact entry is added to the abperson table of the iPhone's AddressBook.sqlilte database. Only columns for the simplest contact entry performed with the iPhones native console interface is considered. Some column data, enumerated by hard coding 0 and NULL values, are unknown to this sample implementation.
Notice the rowid column is simply incremented by one (1) for each contact entry. The calculation for rowid in the abperson insert, below, is trivial. Only under the simplest of circumstanses will yield expected behavior.
print -p "
insert into abperson values(
(select max(rowid) from abperson)+1,
${fn},${ln},${mn},NULL,NULL,NULL,
${org},${dept},${note},0,${bday},${jt},${FN},
NULL,NULL,'${fns}','${lns}','${cdate}','${mdate}',
NULL,NULL,NULL,NULL,0,NULL,NULL,'${fns:0:1}','${lns:0:1}',
0,0,-1,NULL,1
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
print -p "
insert into abpersonchanges values(
(select max(rowid) from abperson),
0,NULL,NULL,0
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
The abperson table entry, constitues a contact root entry. By "root" it is meant that this entry is the parent of all supporting objects for this contact.
Supporting objects (datum) to an abperson entry include multiple value entries, Address (with sub entries for Street, State, ZIP, City and CountryCode), Email, Phone numbers, URL's. Each of Address, Email, Phone number and URLs can have multiple entries.
| abmultivalueentry | |
| 5 | Address |
| 4 | Email address |
| 3 | Phone number |
| 22 | URL |
addAddr inserts an Address entry for this interation's contact. A NULL value entry, with property 5, is inserted into the abmultivalue table. Since the address has five (5) sub entries, the entries are individuated in the abmultivalueentry table. The table has columns parent_id, key and value. The parent_id maps to the NULL valued, property 5 entry in the abmultivalue table for this interations contact.
function addAddr {
let i=0
awk -F'[:;]' '/^ADR;/{print}' ${vcf} |
while read a; do
t="${a#*[=;]}" t="${t#TYPE=}" a="${a##*:}"
setToken C "${a##*;}" ; a="${a%;*}"
setToken z "${a##*;}" ; a="${a%;*}"
setToken S "${a##*;}" ; a="${a%;*}"
setToken c "${a##*;}" ; a="${a%;*}"
s="${a##*;}" a="${a%;*}"
s="${s}${a##*;}" a="${a%;*}"
s="${s}${a##*;}" a="${a%;*}"
setToken s "${s}" ; a="${a%;*}"
(( ${#s} > 1 )) || (( ${#c} > 1 )) && {
[[ "${t}" = HOME* ]] && l=${Home} || l=${Work}
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),
5,${i},${l},NULL
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${Street},"${s}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${State},"${S}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${ZIP},"${z}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${City},"${c}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${CountryCode},"${C}"
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
}
let i=i+1
done
}
addEmail inserts an email entry for the contact. The uid column is incremented by one. The recordid column is assigned the row number for this contact, taken from its abpersons entry. The property value is 4. The identifier column starts with zero and is incremented for each of the contact's email addresses inserted. A label index is inserted for the type of email entry (Work or Home). And, the value column is populated with the email address.
function addEmail {
let i=0
awk -F'[:;]' '/^EMAIL;.*INTERNET/{print}' ${vcf} |
while read e; do
[[ "${e}" = "EMAIL;PREF;*" ]] && l=${Home} || l=${Work}
e="${e#*:}"
(( ${#e} > 4 )) && {
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),
4,${i},${l},'${e}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
let i=i+1
}
done
}
addURL inserts a URL entry for the contact. The uid column is incremented by one. The recordid column is assigned the row number for this contact, taken from its abpersons entry. The property value is 22. The identifier column starts with zero and is incremented for each of the contact's URLs inserted. A label index is inserted for the type of URL entry. All URLs are coded as HomePage for this example. And, the value column is populated with the URL.
function addURL {
let i=0
awk -F'[:;]' '/^URL.*/{print}' ${vcf} |
while read u; do
u="${u#*:}"
(( ${#u} > 4 )) && {
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),
22,${i},${HomePage},'${u}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
let i=i+1
}
done
}
addVoice inserts a voice telephone entry for the contact. The uid column is incremented by one. The recordid column is assigned the row number for this contact, taken from its abpersons entry. The property value is 3. The identifier column starts with zero and is incremented for each of the contact's telephone numbers inserted. A label index is inserted for the type of telephone entry (Home, Work or Cellular). And, the value column is populated with the telephone number.
In addition, the abphonelastfour table, storing the last four digits of the telephone numbers, is populated. The table has two columns. The multivalue_id is assigned the uid for this telephone number, taken from its abmultivalue entry. The telephone's last four is cut from this telephone number.
function addVoice {
awk -F'[:;]' '/^TEL;.*VOICE/{print}' ${vcf} |
while read t; do
case ${t} in
*HOME* ) l=${Home};;
*WORK* ) l=${Work};;
*CELL* ) l=${Cell};;
* ) l=${Home};;
esac
t="${t#*:}"
(( ${#t} > 6 )) && {
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),3,
ifnull((select max(identifier)+1 from abmultivalue
where record_id=(select max(rowid) from abperson) and
property=3),0),
${l},'${t}'
);
insert into abphonelastfour values(
(select max(uid) from abmultivalue),
'${t##*-}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
}
done
}
setLabels populates the abmultivalueentrykey and abmultivaluelabel tables. Both store a vector of values used to key or label the abmultivalueentry and abmultivalue items.
function setLabels {
for key in Street State ZIP City CountryCode; do
print -p "select rowid from abmultivalueentrykey where value='${key}';
select '.end';"
found=0
while read -p row; do
[[ ${row} = \.end ]] && break
(( ${row} )) && found=${row}
done
(( ${found} )) || print -p "insert into abmultivalueentrykey values('${key}');"
done
for key in Street State ZIP City CountryCode; do
print -p "select rowid from abmultivalueentrykey where value='${key}';"
read -p row
eval let ${key}=${row}
done
for label in 'Home' 'Work' 'Mobile' 'HomePage'; do
print -p "select rowid from abmultivaluelabel where value='_\$!<${label}>!\$_';
select '.end';"
found=0
while read -p row; do
[[ ${row} = \.end ]] && break
(( ${row} )) && found=${row}
done
(( ${found} )) || print -p "insert into abmultivaluelabel values('_\$!<${label}>!\$_');"
done
for label in 'Home' 'Work' 'Mobile' 'HomePage'; do
print -p "select rowid from abmultivaluelabel where value='_\$!<${label}>!\$_';"
read -p row
eval let ${label}=${row}
done
}
Main
This script will read and write the iPhone's Sqlite3 database file, AddressBook.sqlitedb; its first argument. Contact records described by the .vcf files (remaining arguments) are inserted into database file. Notice that the names of .vcf files does not store with the contact's data - holding no meaning than a file name.main conducts a trivial test for the expected argument pattern - more than two arguments, first two readable - and prints a usage message upon failing. An sqlite3 connection to the database file, AddressBook.sqlitedb, is staged as coprocess. Avoiding dynamic label and key generation, setLabels completes population of tables, abmultivalueentrykey and abmultivaluelabel, with the labels and keys honored in the example.
And then, the .vcf files are parsed for some sample contact tags. Some important VCard tags scanned for the base contact entry include N, ORG. N will be parse for the firstname, middlename and lastname, if any. ORG will be parsed for the Organization. These data are aggregated to form sort keys, FirstSort and LastSort. Which, in turn, go to FirstSortSection and LastSortSection, and the search key stored in table, abpersonsearchkey.
Contact tags for address, email, URL and telephone numbers are parsed. The data parsed is inserted into the database file, AddressBook.sqlitedb, in approriate tables; enough to constitue a simple contact entry for the iPhone's contact database.
# main
(( ${#} >= 2 )) ||
[[ -r "${1}" ]] &&
[[ -r "${2}" ]] || {
print "You must supply an sqlite3db and .vcf files"
print "example: ${0} AddressBook.sqlitedb mycontact_1.vcf mycontact_2.vcf..."
exit 1
}
/usr/bin/sqlite3 "${1}" |& shift
setLabels
while (( ${#} )); do
vcf="${1}"
dos2unix "${vcf}" >/dev/null 2>&1
addPerson
addAddr
addEmail
addURL
addVoice
sleep 1
shift
done
print -p ".exit"
#!/bin/ksh
function itoc {
echo -e "\0$(printf "%o\n" ${1})"
}
function ctoi {
printf "%d\n" "'${1}'"
}
function sortCode {
d=
while (( ${#1} ));do
case "${1:0:1}" in
[[:blank:]] ) d=${d}$(itoc 2);;
[[:alpha:]] ) (( i = 43 + 2*(($(ctoi ${1:0:1})&95)-64) ));
d=${d}$(itoc ${i});;
[[:digit:]] ) (( i = 142 + 2*($(ctoi ${1:0:1})-48) ));
d=${d}'*'$(itoc ${i});;
esac
set "${1:1}"
done
printf "%s\n" "${d}"
}
function sortCount {
let i=0
d=
while (( ${#1} )); do
case "${1:0:1}" in
[[:blank:]] ) (( i )) && {
d=${d}$(itoc i+4)$(itoc 2)
let i=0
};;
[[:digit:][:alpha:]] ) let i=i+1;;
esac
set "${1:1}"
done
(( i )) && d=${d}$(itoc i+4)
printf "%s\n" "${d}"
}
function sortUpper {
let i=0
d=
while (( ${#1} ));do
case "${1:0:1}" in
[[:blank:]] ) (( i )) && {
d=${d}$(itoc i+4)
d=${d}$(itoc 2)
let i=0
};;
[[:upper:]] ) (( i )) && d=${d}$(itoc 134-i);
d=${d}$(itoc 143);
i=0;;
[[:lower:][:digit:]] ) let i=i+1;;
esac
set "${1:1}"
done
(( i )) && d=${d}$(itoc i+4)
printf "%s\n" "${d}"
}
function setToken {
eval "${1}=\$(echo \${2:-NULL})"
eval [[ "\${${1}}" != NULL ]] && eval ${1}="\'\$${1}\'"
}
function addPerson {
setToken fn "$(awk -F'[:;]' '$1=="N"{print $3}' ${vcf}|tr '-' ' ')"
setToken mn "$(awk -F'[:;]' '$1=="N"{print $4}' ${vcf}|tr '-' ' ')"
setToken ln "$(awk -F'[:;]' '$1=="N"{print $2}' ${vcf}|tr '-' ' ')"
setToken FN "$(awk -F'[:;]' '$1=="FN"{printf("%s",$2)}' ${vcf})"
setToken org "$(awk -F'[:;]' '$1=="ORG"{print $2}' ${vcf}|tr '-' ' ')"
setToken dept "$(awk -F'[:;]' '$1=="ORG"{print $3}' ${vcf})"
setToken jt "$(awk -F'[:;]' '$1=="TITLE"{print $2}' ${vcf})"
setToken note "$(awk -F'[:;]' '$1=="X-MICROSOFT-OFFICELOC"{print $2}' ${vcf})"
bday="$(awk -F':' '/^BDAY;/{print $2}' ${vcf}|tr 'Tt' ' ')"
(( ${#bday} )) &&
bday="'$(( $(date -d "${bday}" "+%s")-$(date -d "Jan 1 2001" "+%s") ))'" ||
bday=NULL
[[ ${fn} != NULL ]] && {
FNS="$(sortCode "${fn}")"
FNC="$(sortCount "${fn}")"
FNU="$(sortUpper "${fn}")"
} || FNS='' FNC='' FNU=''
[[ ${ln} != NULL ]] && {
LNS="$(sortCode "${ln}")"
LNC="$(sortCount "${ln}")"
LNU="$(sortUpper "${ln}")"
} || LNS='' LNC='' LNU=''
[[ ${org} != NULL ]] && {
ORGS="$(sortCode "${org}")"
ORGC="$(sortCount "${org}")"
ORGU="$(sortUpper "${org}")"
} || ORGS='' ORGC='' ORGU=''
fns="${FNS}"
(( ${#FNS} )) && (( ${#LNS} )) && fns="${FNS}"$(itoc 2)
(( ${#LNS} )) && fns="${fns}${LNS}"
(( ${#fns} )) && (( ${#ORGS} )) && fns="${fns}"$(itoc 2)
(( ${#ORGS} )) && fns="${fns}${ORGS}"
fnc="${FNC}"
(( ${#FNC} )) && (( ${#LNC} )) && fnc="${FNC}"$(itoc 2)
(( ${#LNC} )) && fnc="${fnc}${LNC}"
(( ${#fnc} )) && (( ${#ORGC} )) && fnc="${fnc}"$(itoc 2)
(( ${#ORGC} )) && fnc="${fnc}${ORGC}"
fnu="${FNU}"
(( ${#FNU} )) && (( ${#LNU} )) && fnu="${FNU}"$(itoc 2)
(( ${#LNU} )) && fnu="${fnu}${LNU}"
(( ${#fnu} )) && (( ${#ORGU} )) && fnu="${fnu}"$(itoc 2)
(( ${#ORGU} )) && fnu="${fnu}${ORGU}"
(( ${#fns} )) && (( ${#fnc} )) && fns="${fns}"$(itoc 1)
(( ${#fnc} )) && fns="${fns}${fnc}"
(( ${#fns} )) && (( ${#fnu} )) && fns="${fns}"$(itoc 1)
(( ${#fnu} )) && fns="${fns}${fnu}"
lns="${LNS}"
(( ${#LNS} )) && (( ${#FNS} )) && lns="${LNS}"$(itoc 2)
(( ${#FNS} )) && lns="${lns}${FNS}"
(( ${#lns} )) && (( ${#ORGS} )) && lns="${lns}"$(itoc 2)
(( ${#ORGS} )) && lns="${lns}${ORGS}"
print -p "
insert into abpersonsearchkey values(
(select max(rowid) from abperson)+1,'${lns}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
lnc="${LNC}"
(( ${#LNC} )) && (( ${#FNC} )) && lnc="${LNC}"$(itoc 2)
(( ${#FNC} )) && lnc="${lnc}${FNC}"
(( ${#lnc} )) && (( ${#ORGC} )) && lnc="${lnc}"$(itoc 2)
(( ${#ORGC} )) && lnc="${lnc}${ORGC}"
lnu="${LNU}"
(( ${#LNU} )) && (( ${#FNU} )) && lnu="${LNU}"$(itoc 2)
(( ${#FNU} )) && lnu="${lnu}${FNU}"
(( ${#lnu} )) && (( ${#ORGU} )) && lnu="${lnu}"$(itoc 2)
(( ${#ORGU} )) && lnu="${lnu}${ORGU}"
(( ${#lns} )) && (( ${#lnc} )) && lns="${lns}"$(itoc 1)
(( ${#lnc} )) && lns="${lns}${lnc}"
(( ${#lns} )) && (( ${#lnu} )) && lns="${lns}"$(itoc 1)
(( ${#lnu} )) && lns="${lns}${lnu}"
cdate="$(( $(date "+%s")-$(date -d "Jan 1 2001" "+%s") ))"
mdate="${cdate}"
print -p "
insert into abperson values(
(select max(rowid) from abperson)+1,
${fn},${ln},${mn},NULL,NULL,NULL,
${org},${dept},${note},0,${bday},${jt},${FN},
NULL,NULL,'${fns}','${lns}','${cdate}','${mdate}',
NULL,NULL,NULL,NULL,0,NULL,NULL,'${fns:0:1}','${lns:0:1}',
0,0,-1,NULL,1
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
print -p "
insert into abpersonchanges values(
(select max(rowid) from abperson),
0,NULL,NULL,0
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
}
function addAddr {
let i=0
awk -F'[:;]' '/^ADR;/{print}' ${vcf} |
while read a; do
t="${a#*[=;]}" t="${t#TYPE=}" a="${a##*:}"
setToken C "${a##*;}" ; a="${a%;*}"
setToken z "${a##*;}" ; a="${a%;*}"
setToken S "${a##*;}" ; a="${a%;*}"
setToken c "${a##*;}" ; a="${a%;*}"
s="${a##*;}" a="${a%;*}"
s="${s}${a##*;}" a="${a%;*}"
s="${s}${a##*;}" a="${a%;*}"
setToken s "${s}" ; a="${a%;*}"
(( ${#s} > 1 )) || (( ${#c} > 1 )) && {
[[ "${t}" = HOME* ]] && l=${Home} || l=${Work}
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),
5,${i},${l},NULL
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${Street},"${s}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${State},"${S}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${ZIP},"${z}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${City},"${c}"
);
insert into abmultivalueentry values(
(select max(uid) from abmultivalue),
${CountryCode},"${C}"
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
}
let i=i+1
done
}
function addEmail {
let i=0
awk -F'[:;]' '/^EMAIL;.*INTERNET/{print}' ${vcf} |
while read e; do
[[ "${e}" = "EMAIL;PREF;*" ]] && l=${Home} || l=${Work}
e="${e#*:}"
(( ${#e} > 4 )) && {
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),
4,${i},${l},'${e}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
let i=i+1
}
done
}
function addURL {
let i=0
awk -F'[:;]' '/^URL.*/{print}' ${vcf} |
while read u; do
u="${u#*:}"
(( ${#u} > 4 )) && {
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),
22,${i},${HomePage},'${u}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
let i=i+1
}
done
}
function addVoice {
case ${1} in
"MAIN" ) l=${Main};;
"HOME" ) l=${Home};;
"WORK" ) l=${Work};;
"CELL" ) l=${Mobile};;
* ) l=${Home};;
esac
awk -F'[:;]' '/^TEL;.*'${1}'[;,]VOICE/{print}' ${vcf} |
while read t; do
t="${t#*:}"
(( ${#t} > 6 )) && {
print -p "
insert into abmultivalue values(
(select max(uid) from abmultivalue)+1,
(select max(rowid) from abperson),3,
ifnull((select max(identifier)+1 from abmultivalue
where record_id=(select max(rowid) from abperson) and
property=3),0),
${l},'${t}'
);
insert into abphonelastfour values(
(select max(uid) from abmultivalue),
'${t##*-}'
);
select '.end';
"
while read -p row; do
[[ ${row} = \.end ]] && break
print "${row}"
done
}
done
}
function setLabels {
for key in Street State ZIP City CountryCode; do
print -p "select rowid from abmultivalueentrykey where value='${key}';
select '.end';"
found=0
while read -p row; do
[[ ${row} = \.end ]] && break
(( ${row} )) && found=${row}
done
(( ${found} )) || print -p "insert into abmultivalueentrykey values('${key}');"
done
for key in Street State ZIP City CountryCode; do
print -p "select rowid from abmultivalueentrykey where value='${key}';"
read -p row
eval let ${key}=${row}
done
for label in 'Home' 'Work' 'Mobile' 'HomePage' 'Main'; do
print -p "select rowid from abmultivaluelabel where value='_\$!<${label}>!\$_';
select '.end';"
found=0
while read -p row; do
[[ ${row} = \.end ]] && break
(( ${row} )) && found=${row}
done
(( ${found} )) || print -p "insert into abmultivaluelabel values('_\$!<${label}>!\$_');"
done
for label in 'Home' 'Work' 'Mobile' 'HomePage' 'Main'; do
print -p "select rowid from abmultivaluelabel where value='_\$!<${label}>!\$_';"
read -p row
eval let ${label}=${row}
done
}
# main
(( ${#} != 2 )) ||
[[ -r "${1}" ]] &&
[[ -r "${2}" ]] || {
print "You must supply a .vcf file and sqlitedb"
print "example: ${0} AddressBook.sqlitedb mycontact.vcf"
exit 1
}
/usr/bin/sqlite3 "${1}" |& shift
setLabels
while (( ${#} )); do
vcf="${1}"
dos2unix "${vcf}" >/dev/null 2>&1
addPerson
addAddr
addEmail
addURL
addVoice HOME
addVoice WORK
addVoice CELL
sleep 1
shift
done
print -p ".exit"
