[korakotc]

I read in the documentation about Dbms. It is the native database
for symbian. Some apps use it underneath, eg. Phonebook.
So, I try to use Dbms (e32db) to retrive such information.
Here is the result. There are some kludges here and there.
Hope people can use/improve it. Or make it into a library.

# some set up
import e32db
db = e32db.Dbms()
db.open(u'c:\\system\\data\\contacts.cdb') # contact db file
dbv = e32db.Db_view()

# 3 query helper functions

# search and retrieve from a row
def select_row(query):
dbv.prepare(db, unicode(query))
dbv.first_line()
dbv.get_line()
return [dbv.col(i+1) for i in range(dbv.col_count())]

# search and retrieve from a column
def select_col(query):
dbv.prepare(db, unicode(query))
dbv.first_line()
result = []
for i in range(dbv.count_line()):
..dbv.get_line()
..result.append(dbv.col(1))
..dbv.next_line()
return result

# retrive all rows (to try yield, in the future)
def select_all(query):
dbv.prepare(db, unicode(query))
dbv.first_line()
result = []
col_count = dbv.col_count()
for i in range(dbv.count_line()):
..dbv.get_line()
..row = [dbv.col(i+1) for i in range(col_count)]
..result.append(row)
..dbv.next_line()
return result

""" -------------------------------------------------
now come to the real queries to get data out of the file
I download the contacts.cdb and open it in an editor.
I can then figure out the structure of the database.
Here's the summary

contacts [cm_id, cm_type, cm_last_modified, cm_contactcreationdate, cm_searchabletext]
..meaning = contact (id, type, mtime, ctime, search_text)
phone [cm_id, cm_phonematching, cm_extendedphonematching]
..meaning = phone (c_id, digits, extenstion)
identitytable [parent_cmid, cm_firstname, cm_lastname, cm_companyname]
..meaning = identity (c_id, firstname, lastname, company)
emailtable [email_fieldid, emailparent_cmid, emailaddress]
..meaning = email (e_id, c_id, email)
groups [cm_id, cm_members]
..meaning = group (g_id, member)
------------------------------------------------- """

[korakotc]

# I then make it into some functions. To for general usage.

# get id from name (most functions need id)
def contact_id(firstname=None, lastname=None):
..q = 'select parent_cmid from identitytable '
..if firstname:
....q += "where cm_firstname='%s' " % firstname
..elif lastname:
....q += "where cm_lastname='%s' " % lastname
..return select_row(q)[0]

# phone numbers need to be decode by reversing/zerofill

def reverse_number(num, width=7): # ext width=8
..digits = list(str(num).zfill(width))
..digits.reverse()
..return int(''.join(digits))

def display_number(num, ext):
..rev_d = reverse_number(num, 7)
..rev_x = reverse_number(ext, 8)
..if rev_x == 0: # no area code
....return rev_d
..elif rev_x < 10: # with area code
....return '%02d%07d' % (rev_x, rev_d)
..elif rev_x >= 10: # with country code
....return '+%d%07d' % (rev_x, rev_d)

# what phone numbers does this person have?
def has_phone(id):
..phone_list = []
..q = "select cm_phonematching, cm_extendedphonematching from phone "
..q += "where cm_id=%d" % id
..for m_num, m_ext in select_all(q):
....phone_list.append(display_number(m_num, m_ext))
..return phone_list

# what emails does he have?
def has_email(id):
..q = "select emailaddress from emailtable where emailparent_cmid=%d" % id
..return select_row(q)[0]

# which groups is he in?
# get group names from contact.search_text
def has_groups(id):
..group_list = []
..q = "select cm_id from groups where cm_members=%d" % id
..for gid in select_col(q):
....q2 = "select cm_searchabletext from contacts where cm_id=%d" % gid
....name0, = select_row(q2)
....group_list.append(name0[:-1]) # remove \x00
..return group_list # use names, not gid

# who are the members of this group?
def has_members(groupname):
..q1 = "select cm_id from contacts where cm_searchabletext = '%s\x00' " % groupname
..gid, = select_row(q1)
..member_list = []
..q2 = "select cm_members from groups where cm_id=%d" % gid
..for id in select_col(q2):
....q3 = "select cm_firstname, cm_lastname from identitytable where parent_cmid=%d" % id
....names = tuple(select_row(q3))
....member_list.append((id, '%s %s' % names))
..return member_list

# get all groups
# group is store in contact table as well. it has different value of cm_type
q_groups = "select cm_id, cm_searchabletext from contacts where cm_type=268440330"
all_groups = [(gi, gn[:-1]) for (gi, gn) in select_all(q_groups)] # strip all \x00

Enjoy!

[jaydorsey]

Excellent example. I've been trying to get something similar to this to work but I kept missing small bits and pieces. I hope to see some excellent applications spring up from this

[korakotc]

I spent a week during new year vacation to hack into this.
I gave up a few times but came back with some other ways
to solve the problem. Sadly, e32db doesn't allow retrive the
data where field_type = long_varbin (16). There are still
many important information missing.

What can be retrived now is good enough for simple
applications, I hope.

[jaydorsey]

I found out the trial and error way about the type_codes (it's in the manual, but I didnt' catch it the first read through).

Only thing that I'm kinda stuck on now is that in the contacts table I have (Nokia 3650, not sure if its the same for other phones) I have the following fields (first number is type id, then the field name):

5 cm_id
5 cm_type
5 CM_PrefTemplateRefId
12 CM_UIDSTRING
10 CM_LAST_MODIFIED
10 CM_ContactCreationDate
6 CM_Attributes
6 CM_ReplicationCount
16 CM_Header
16 CM_TextBlob
15 CM_SearchableText
1 CM_HintField

The cm_header and and cm_textblob fields are long_varbin's that I can't extract (no biggie). The cm_searchabletext field seems to contain all of the addresses, phone numbers, urls, etc, but I'm not certain if that's where all the info is kept, or if that is just a "searchable text" field. Pulling out different records the contact information is in different orders it seems (sometimes work number is first, sometimes home number is). At first I thought it was just all of the information null-separated, but because it shows up in different orders I'm not so certain.


Is there anyway to find out the actual schema for the database (what you have posted is a a great start and I'm trying to put some of it together myself).

Thanks,

Jay

[korakotc]

There's another function I use to analyse many table in the contact database.

def t(table, offset=0):
..dbv.prepare(db, u"select * from " + table)
..dbv.first_line()
..for i in range(offset):
....dbv.next_line()
..dbv.get_line()
..col_name = ['bit','tint', 'utint', 'sint', 'usint', 'int', 'uint', 'bint', 'real', 'float', 'time', '11', 'vchar', 'vbin', '14', 'lvchar', 'lvbin']
..for i in range(1, 1+dbv.col_count()):
....col_type = dbv.col_type(i)
....if col_type==16:
......value = '[long binary]'
....else:
......value = dbv.col(i)
....print "%2d %8s %s" % (i, col_name[col_type], value)

(need to do the setup for db, dbv, db.open first, as in first example)

Here I get the field_type name of the data base. There are 17? types. I try to search for
what the number mean what type but cannot find any. So, I try creating a new table with
all types (some of them use the same name). The result is the array above.

(list in fullname)
0: bit
1: tiny int
2: unsigned tiny int
3: small int
4: unsigned small int
5: integer
6: unsigned integer, counter (autoincrement)
7: big int
8: real
9: float
10: time
11: ?
12: var char
13: var binary
14: ?
15: long var char
16: long var binary

You can use the function t(table, offset) to see what column is of what type.
It will show an example record too. If you don't want to use the first record,
you can tell an offset to see record number n.

[busaussie]

hey, i was reading your Thread... and i was wondering is it possible to open my Contacts.cdb file?

what editor do i use?

[korakotc]

Originally posted by busaussie
hey, i was reading your Thread... and i was wondering is it possible to open my Contacts.cdb file?

what editor do i use?

I backed up my phone with PC Suite.
Then, I open the backup file on my PC.

[busaussie]

yeah, i backed up my phone with PC suite also..

What program did you open your file with, so you can view the stucture and also the contacts?

[korakotc]

What I did is I read the binary file.
Guess some field name... then write a script
that run on the phone with another contact file.

So, I didn't read from the backup file.
If you want to restore data from the backup,
you may write the python script that use

db = contacts.open( backupfile)
# then retrive the data as in the doc.

Try this on the emulator.
I can't guarantee that it will work though.

[busaussie]

one problem, i dont know how to program with python or anything. Is it possible that you could send me the emulator and the script and tell me how to do it???

please

[Xen0n]

Hi, I have looked around for any type of example of script that claims to read the contacts database, but i cant figure how to use that examples. All i want is a simple, CLEAR, script that show how to open contact database and load all contacts present into a listbox, nothing else. This will be easy for the experts! please help me! Thanks in advance!

[korakotc]

try this

import contacts, appuifw
db = contacts.open()
names = [db[i].title for i in db]
i = appuifw.selection_list(names)

You can use either a dialog (popup_menu, selection_list) or a list box (Listbox)

Above may be a bad example. You may typically
want to get the corresponding phone number as well.

import contacts, appuifw
db = contacts.open()
names = []
numbers = []
for i in db:
  names.append(db[i].title)
  num = db[i].find('mobile_number')
  if num:
    numbers.append(num[0].value) # first mobile
  else:
    numbers.append(None)

i = appuifw.selection_list(names)
print 'number =', numbers[i]

Hope this help.

[Xen0n]

Hey man, really thank you! I found this sample great! I want to ask 2 more little thing if u know how to proceed!

How can i sort the list in alphabetical order?!

How can i bind key to select the appropriate letter in this list? example, I have 3 contact:

John
Carl
Frank

If i press 3 time the button "3" the selection must place over frank, if I press one time the button "5" the selection must place over john and the same for carl with button "2"

Hope i was clear!

Thank another time man for helping me!

[korakotc]

You can sort any sequence with the sort method e.g.

seq = [3,2,1]
seq.sort()
# now seq become [1,2,3]

You will write more code to accomplish what you want.
Try searching for more python examples on this with google.
(This is not pys60 specific)

I am not so clear of what you want?
why "5" is John?
If you have a lot of contacts, you may try using 'searching'
from multiple selection list (look in the document).