satya - Tuesday, November 02, 2010 10:55:49 AM

view_contacts

CREATE VIEW view_contacts AS 

SELECT contacts._id AS _id,
contacts.custom_ringtone AS custom_ringtone, 
name_raw_contact.display_name_source AS display_name_source, 
name_raw_contact.display_name AS display_name, 
name_raw_contact.display_name_alt AS display_name_alt, 
name_raw_contact.phonetic_name AS phonetic_name, 
name_raw_contact.phonetic_name_style AS phonetic_name_style, 
name_raw_contact.sort_key AS sort_key, 
name_raw_contact.sort_key_alt AS sort_key_alt, 
name_raw_contact.contact_in_visible_group AS in_visible_group, 
has_phone_number, 
lookup, 
photo_id, 
contacts.last_time_contacted AS last_time_contacted, 
contacts.send_to_voicemail AS send_to_voicemail, 
contacts.starred AS starred, 
contacts.times_contacted AS times_contacted, status_update_id 

FROM contacts JOIN raw_contacts AS name_raw_contact 
ON(name_raw_contact_id=name_raw_contact._id)

satya - Tuesday, November 02, 2010 10:56:19 AM

contacts

CREATE TABLE contacts 
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
name_raw_contact_id INTEGER REFERENCES raw_contacts(_id),
photo_id INTEGER REFERENCES data(_id),
custom_ringtone TEXT,
send_to_voicemail INTEGER NOT NULL DEFAULT 0,
times_contacted INTEGER NOT NULL DEFAULT 0,
last_time_contacted INTEGER,
starred INTEGER NOT NULL DEFAULT 0,
in_visible_group INTEGER NOT NULL DEFAULT 1,
has_phone_number INTEGER NOT NULL DEFAULT 0,
lookup TEXT,
status_update_id INTEGER REFERENCES data(_id),
single_is_restricted INTEGER NOT NULL DEFAULT 0)

satya - Tuesday, November 02, 2010 11:12:23 AM

raw_contacts

CREATE TABLE raw_contacts 
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
is_restricted INTEGER DEFAULT 0,
account_name STRING DEFAULT NULL, 
account_type STRING DEFAULT NULL, 
sourceid TEXT,
version INTEGER NOT NULL DEFAULT 1,
dirty INTEGER NOT NULL DEFAULT 0,
deleted INTEGER NOT NULL DEFAULT 0,
contact_id INTEGER REFERENCES contacts(_id),
aggregation_mode INTEGER NOT NULL DEFAULT 0,
aggregation_needed INTEGER NOT NULL DEFAULT 1,
custom_ringtone TEXT
send_to_voicemail INTEGER NOT NULL DEFAULT 0,
times_contacted INTEGER NOT NULL DEFAULT 0,
last_time_contacted INTEGER,
starred INTEGER NOT NULL DEFAULT 0,
display_name TEXT,
display_name_alt TEXT,
display_name_source INTEGER NOT NULL DEFAULT 0,
phonetic_name TEXT,
phonetic_name_style TEXT,
sort_key TEXT COLLATE PHONEBOOK,
sort_key_alt TEXT COLLATE PHONEBOOK,
name_verified INTEGER NOT NULL DEFAULT 0,
contact_in_visible_group INTEGER NOT NULL DEFAULT 0,
sync1 TEXT, sync2 TEXT, sync3 TEXT, sync4 TEXT )

satya - Tuesday, November 02, 2010 11:29:20 AM

contact_entities_view

CREATE VIEW contact_entities_view AS 

SELECT raw_contacts.account_name AS account_name,
raw_contacts.account_type AS account_type,
raw_contacts.sourceid AS sourceid,
raw_contacts.version AS version,
raw_contacts.dirty AS dirty,
raw_contacts.deleted AS deleted,
raw_contacts.name_verified AS name_verified,
package AS res_package,
contact_id, 
raw_contacts.sync1 AS sync1, 
raw_contacts.sync2 AS sync2, 
raw_contacts.sync3 AS sync3, 
raw_contacts.sync4 AS sync4, 
mimetype, data1, data2, data3, data4, data5, data6, data7, data8, 
data9, data10, data11, data12, data13, data14, data15, 
data_sync1, data_sync2, data_sync3, data_sync4, 

raw_contacts._id AS _id, 

is_primary, is_super_primary, 
data_version, 
data._id AS data_id,
raw_contacts.starred AS starred,
raw_contacts.is_restricted AS is_restricted,
groups.sourceid AS group_sourceid 

FROM raw_contacts LEFT OUTER JOIN data 
   ON (data.raw_contact_id=raw_contacts._id) 
LEFT OUTER JOIN packages 
  ON (data.package_id=packages._id) 
LEFT OUTER JOIN mimetypes 
  ON (data.mimetype_id=mimetypes._id) 
LEFT OUTER JOIN groups 
  ON (mimetypes.mimetype='vnd.android.cursor.item/group_membership' 
    AND groups._id=data.data1)

satya - Saturday, November 13, 2010 8:08:56 AM

data

CREATE TABLE data 
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
package_id INTEGER REFERENCES package(_id),
mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,
raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,
is_primary INTEGER NOT NULL DEFAULT 0,
is_super_primary INTEGER NOT NULL DEFAULT 0,
data_version INTEGER NOT NULL DEFAULT 0,
data1 TEXT,data2 TEXT,data3 TEXT,data4 TEXT,data5 TEXT,
data6 TEXT,data7 TEXT,data8 TEXT,data9 TEXT,data10 TEXT,
data11 TEXT,data12 TEXT,data13 TEXT,data14 TEXT,data15 TEXT,
data_sync1 TEXT, data_sync2 TEXT, data_sync3 TEXT, data_sync4 TEXT )

satya - Saturday, November 13, 2010 8:10:22 AM

Mimetypes

CREATE TABLE mimetypes 
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
mimetype TEXT NOT NULL)

satya - Wednesday, November 17, 2010 4:15:33 PM

aggregate exceptions tables

CREATE TABLE agg_exceptions 
(_id INTEGER PRIMARY KEY AUTOINCREMENT,
type INTEGER NOT NULL, 
raw_contact_id1 INTEGER REFERENCES raw_contacts(_id), 
raw_contact_id2 INTEGER REFERENCES raw_contacts(_id))