Merge "Add BaseDatabaseHelperUpgradeTest and ContactsDatabaseHelperUpgradeTest"

3 files changed, 956 insertions, 0 deletions

diff --git a/tests/assets/upgradeTest/contacts2_1108.sql b/tests/assets/upgradeTest/contacts2_1108.sql
new file mode 100644
index 00000000..3559edec
--- /dev/null
+++ b/tests/assets/upgradeTest/contacts2_1108.sql
@@ -0,0 +1,167 @@
+PRAGMA foreign_keys=OFF;
+BEGIN TRANSACTION;
+CREATE TABLE _sync_state (_id INTEGER PRIMARY KEY,account_name TEXT NOT NULL,account_type TEXT NOT NULL,data TEXT,UNIQUE(account_name, account_type));
+CREATE TABLE _sync_state_metadata (version INTEGER);
+INSERT INTO "_sync_state_metadata" VALUES(1);
+CREATE TABLE properties (property_key TEXT PRIMARY KEY, property_value TEXT );
+INSERT INTO "properties" VALUES('database_time_created','20243770769');
+INSERT INTO "properties" VALUES('icu_version','56.1');
+INSERT INTO "properties" VALUES('locale','en-US');
+INSERT INTO "properties" VALUES('aggregation_v2','5');
+INSERT INTO "properties" VALUES('search_index','1');
+INSERT INTO "properties" VALUES('known_accounts','twyen.dev@gmail.comcom.google');
+INSERT INTO "properties" VALUES('directoryScanComplete','1');
+CREATE TABLE accounts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_name TEXT, account_type TEXT, data_set TEXT);
+CREATE TABLE contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT,name_raw_contact_id INTEGER REFERENCES raw_contacts(_id),photo_id INTEGER REFERENCES data(_id),photo_file_id INTEGER REFERENCES photo_files(_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,pinned INTEGER NOT NULL DEFAULT 0,has_phone_number INTEGER NOT NULL DEFAULT 0,lookup TEXT,status_update_id INTEGER REFERENCES data(_id),contact_last_updated_timestamp INTEGER);
+CREATE TABLE deleted_contacts (contact_id INTEGER PRIMARY KEY,contact_deleted_timestamp INTEGER NOT NULL default 0);
+CREATE TABLE raw_contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_id INTEGER REFERENCES accounts(_id),sourceid TEXT,backup_id TEXT,raw_contact_is_read_only INTEGER NOT NULL DEFAULT 0,version INTEGER NOT NULL DEFAULT 1,dirty INTEGER NOT NULL DEFAULT 0,deleted INTEGER NOT NULL DEFAULT 0,metadata_dirty 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,pinned 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,phonebook_label TEXT,phonebook_bucket INTEGER,sort_key_alt TEXT COLLATE PHONEBOOK,phonebook_label_alt TEXT,phonebook_bucket_alt INTEGER,name_verified INTEGER NOT NULL DEFAULT 0,sync1 TEXT, sync2 TEXT, sync3 TEXT, sync4 TEXT );
+CREATE TABLE stream_items (_id INTEGER PRIMARY KEY AUTOINCREMENT, raw_contact_id INTEGER NOT NULL, res_package TEXT, icon TEXT, label TEXT, text TEXT, timestamp INTEGER NOT NULL, comments TEXT, stream_item_sync1 TEXT, stream_item_sync2 TEXT, stream_item_sync3 TEXT, stream_item_sync4 TEXT, FOREIGN KEY(raw_contact_id) REFERENCES raw_contacts(_id));
+CREATE TABLE stream_item_photos (_id INTEGER PRIMARY KEY AUTOINCREMENT, stream_item_id INTEGER NOT NULL, sort_index INTEGER, photo_file_id INTEGER NOT NULL, stream_item_photo_sync1 TEXT, stream_item_photo_sync2 TEXT, stream_item_photo_sync3 TEXT, stream_item_photo_sync4 TEXT, FOREIGN KEY(stream_item_id) REFERENCES stream_items(_id));
+CREATE TABLE photo_files (_id INTEGER PRIMARY KEY AUTOINCREMENT, height INTEGER NOT NULL, width INTEGER NOT NULL, filesize INTEGER NOT NULL);
+CREATE TABLE packages (_id INTEGER PRIMARY KEY AUTOINCREMENT,package TEXT NOT NULL);
+CREATE TABLE mimetypes (_id INTEGER PRIMARY KEY AUTOINCREMENT,mimetype TEXT NOT NULL);
+INSERT INTO "mimetypes" VALUES(1,'vnd.android.cursor.item/email_v2');
+INSERT INTO "mimetypes" VALUES(2,'vnd.android.cursor.item/im');
+INSERT INTO "mimetypes" VALUES(3,'vnd.android.cursor.item/nickname');
+INSERT INTO "mimetypes" VALUES(4,'vnd.android.cursor.item/organization');
+INSERT INTO "mimetypes" VALUES(5,'vnd.android.cursor.item/phone_v2');
+INSERT INTO "mimetypes" VALUES(6,'vnd.android.cursor.item/sip_address');
+INSERT INTO "mimetypes" VALUES(7,'vnd.android.cursor.item/name');
+INSERT INTO "mimetypes" VALUES(8,'vnd.android.cursor.item/postal-address_v2');
+INSERT INTO "mimetypes" VALUES(9,'vnd.android.cursor.item/identity');
+INSERT INTO "mimetypes" VALUES(10,'vnd.android.cursor.item/photo');
+INSERT INTO "mimetypes" VALUES(11,'vnd.android.cursor.item/group_membership');
+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,hash_id TEXT,is_read_only INTEGER NOT NULL DEFAULT 0,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, carrier_presence INTEGER NOT NULL DEFAULT 0 );
+CREATE TABLE phone_lookup (data_id INTEGER REFERENCES data(_id) NOT NULL,raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,normalized_number TEXT NOT NULL,min_match TEXT NOT NULL);
+CREATE TABLE name_lookup (data_id INTEGER REFERENCES data(_id) NOT NULL,raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,normalized_name TEXT NOT NULL,name_type INTEGER NOT NULL,PRIMARY KEY (data_id, normalized_name, name_type));
+CREATE TABLE nickname_lookup (name TEXT,cluster TEXT);
+CREATE TABLE groups (_id INTEGER PRIMARY KEY AUTOINCREMENT,package_id INTEGER REFERENCES package(_id),account_id INTEGER REFERENCES accounts(_id),sourceid TEXT,version INTEGER NOT NULL DEFAULT 1,dirty INTEGER NOT NULL DEFAULT 0,title TEXT,title_res INTEGER,notes TEXT,system_id TEXT,deleted INTEGER NOT NULL DEFAULT 0,group_visible INTEGER NOT NULL DEFAULT 0,should_sync INTEGER NOT NULL DEFAULT 1,auto_add INTEGER NOT NULL DEFAULT 0,favorites INTEGER NOT NULL DEFAULT 0,group_is_read_only INTEGER NOT NULL DEFAULT 0,sync1 TEXT, sync2 TEXT, sync3 TEXT, sync4 TEXT );
+INSERT INTO "groups" VALUES(1,NULL,1,NULL,1,0,'My Contacts',NULL,NULL,'Contacts',0,1,1,1,0,1,NULL,NULL,NULL,NULL);
+INSERT INTO "groups" VALUES(2,NULL,1,NULL,1,0,'Starred in Android',NULL,NULL,NULL,0,1,1,0,1,1,NULL,NULL,NULL,NULL);
+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));
+CREATE TABLE settings (account_name STRING NOT NULL,account_type STRING NOT NULL,data_set STRING,ungrouped_visible INTEGER NOT NULL DEFAULT 0,should_sync INTEGER NOT NULL DEFAULT 1);
+CREATE TABLE visible_contacts (_id INTEGER PRIMARY KEY);
+CREATE TABLE default_directory (_id INTEGER PRIMARY KEY);
+CREATE TABLE calls (_id INTEGER PRIMARY KEY AUTOINCREMENT,number TEXT,presentation INTEGER NOT NULL DEFAULT 1,post_dial_digits TEXT NOT NULL DEFAULT '',date INTEGER,duration INTEGER,data_usage INTEGER,type INTEGER,features INTEGER NOT NULL DEFAULT 0,subscription_component_name TEXT,subscription_id TEXT,phone_account_address TEXT,phone_account_hidden INTEGER NOT NULL DEFAULT 0,sub_id INTEGER DEFAULT -1,new INTEGER,name TEXT,numbertype INTEGER,numberlabel TEXT,countryiso TEXT,voicemail_uri TEXT,is_read INTEGER,geocoded_location TEXT,lookup_uri TEXT,matched_number TEXT,normalized_number TEXT,photo_id INTEGER NOT NULL DEFAULT 0,photo_uri TEXT,formatted_number TEXT,_data TEXT,has_content INTEGER,mime_type TEXT,source_data TEXT,source_package TEXT,transcription TEXT,state INTEGER,dirty INTEGER NOT NULL DEFAULT 0,deleted INTEGER NOT NULL DEFAULT 0, add_for_all_users INTEGER NOT NULL DEFAULT 1);
+CREATE TABLE voicemail_status (_id INTEGER PRIMARY KEY AUTOINCREMENT,source_package TEXT UNIQUE NOT NULL,phone_account_component_name TEXT,phone_account_id TEXT,settings_uri TEXT,voicemail_access_uri TEXT,configuration_state INTEGER,data_channel_state INTEGER,notification_channel_state INTEGER);
+INSERT INTO "voicemail_status" VALUES(1,'com.android.phone','com.android.phone/com.android.services.telephony.TelephonyConnectionService','8901260421762832248',NULL,NULL,0,0,0);
+CREATE TABLE status_updates (status_update_data_id INTEGER PRIMARY KEY REFERENCES data(_id),status TEXT,status_ts INTEGER,status_res_package TEXT, status_label INTEGER, status_icon INTEGER);
+CREATE TABLE directories(_id INTEGER PRIMARY KEY AUTOINCREMENT,packageName TEXT NOT NULL,authority TEXT NOT NULL,typeResourceId INTEGER,typeResourceName TEXT,accountType TEXT,accountName TEXT,displayName TEXT, exportSupport INTEGER NOT NULL DEFAULT 0,shortcutSupport INTEGER NOT NULL DEFAULT 0,photoSupport INTEGER NOT NULL DEFAULT 0);
+INSERT INTO "directories" VALUES(0,'com.android.providers.contacts','com.android.contacts',2130968582,'com.android.providers.contacts:string/default_directory',NULL,NULL,NULL,0,2,3);
+INSERT INTO "directories" VALUES(1,'com.android.providers.contacts','com.android.contacts',2130968583,'com.android.providers.contacts:string/local_invisible_directory',NULL,NULL,NULL,0,2,3);
+CREATE TABLE data_usage_stat(stat_id INTEGER PRIMARY KEY AUTOINCREMENT, data_id INTEGER NOT NULL, usage_type INTEGER NOT NULL DEFAULT 0, times_used INTEGER NOT NULL DEFAULT 0, last_time_used INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(data_id) REFERENCES data(_id));
+CREATE TABLE metadata_sync (_id INTEGER PRIMARY KEY AUTOINCREMENT,raw_contact_backup_id TEXT NOT NULL,account_id INTEGER NOT NULL,data TEXT,deleted INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE pre_authorized_uris (_id INTEGER PRIMARY KEY AUTOINCREMENT, uri STRING NOT NULL, expiration INTEGER NOT NULL DEFAULT 0);
+CREATE TABLE metadata_sync_state (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_id INTEGER NOT NULL,state BLOB);
+CREATE TABLE v1_settings (_id INTEGER PRIMARY KEY,_sync_account TEXT,_sync_account_type TEXT,key STRING NOT NULL,value STRING );
+ANALYZE sqlite_master;
+INSERT INTO "sqlite_stat1" VALUES('contacts','contacts_has_phone_index','9000 500');
+INSERT INTO "sqlite_stat1" VALUES('contacts','contacts_name_raw_contact_id_index','9000 1');
+INSERT INTO "sqlite_stat1" VALUES('contacts','contacts_contact_last_updated_timestamp_index','9000 10');
+INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contacts_contact_id_index','10000 2');
+INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contact_sort_key2_index','10000 2');
+INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contact_sort_key1_index','10000 2');
+INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contacts_source_id_account_id_index','10000 1 1');
+INSERT INTO "sqlite_stat1" VALUES('name_lookup','name_lookup_raw_contact_id_index','35000 4');
+INSERT INTO "sqlite_stat1" VALUES('name_lookup','name_lookup_index','35000 2 2 2 1');
+INSERT INTO "sqlite_stat1" VALUES('name_lookup','sqlite_autoindex_name_lookup_1','35000 3 2 1');
+INSERT INTO "sqlite_stat1" VALUES('phone_lookup','phone_lookup_index','3500 3 2 1');
+INSERT INTO "sqlite_stat1" VALUES('phone_lookup','phone_lookup_min_match_index','3500 3 2 2');
+INSERT INTO "sqlite_stat1" VALUES('phone_lookup','phone_lookup_data_id_min_match_index','3500 2 2');
+INSERT INTO "sqlite_stat1" VALUES('data','data_mimetype_data1_index','60000 5000 2');
+INSERT INTO "sqlite_stat1" VALUES('data','data_raw_contact_id','60000 10');
+INSERT INTO "sqlite_stat1" VALUES('groups','groups_source_id_account_id_index','50 2 2 1 1');
+INSERT INTO "sqlite_stat1" VALUES('nickname_lookup','nickname_lookup_index','500 2 1');
+INSERT INTO "sqlite_stat1" VALUES('calls',NULL,'250');
+INSERT INTO "sqlite_stat1" VALUES('status_updates',NULL,'100');
+INSERT INTO "sqlite_stat1" VALUES('stream_items',NULL,'500');
+INSERT INTO "sqlite_stat1" VALUES('stream_item_photos',NULL,'50');
+INSERT INTO "sqlite_stat1" VALUES('voicemail_status',NULL,'5');
+INSERT INTO "sqlite_stat1" VALUES('accounts',NULL,'3');
+INSERT INTO "sqlite_stat1" VALUES('pre_authorized_uris',NULL,'1');
+INSERT INTO "sqlite_stat1" VALUES('visible_contacts',NULL,'2000');
+INSERT INTO "sqlite_stat1" VALUES('photo_files',NULL,'50');
+INSERT INTO "sqlite_stat1" VALUES('default_directory',NULL,'1500');
+INSERT INTO "sqlite_stat1" VALUES('mimetypes','mime_type','18 1');
+INSERT INTO "sqlite_stat1" VALUES('data_usage_stat','data_usage_stat_index','20 2 1');
+INSERT INTO "sqlite_stat1" VALUES('metadata_sync','metadata_sync_index','10000 1 1');
+INSERT INTO "sqlite_stat1" VALUES('agg_exceptions',NULL,'10');
+INSERT INTO "sqlite_stat1" VALUES('settings',NULL,'10');
+INSERT INTO "sqlite_stat1" VALUES('packages',NULL,'0');
+INSERT INTO "sqlite_stat1" VALUES('directories',NULL,'3');
+INSERT INTO "sqlite_stat1" VALUES('v1_settings',NULL,'0');
+INSERT INTO "sqlite_stat1" VALUES('android_metadata',NULL,'1');
+INSERT INTO "sqlite_stat1" VALUES('_sync_state','sqlite_autoindex__sync_state_1','2 1 1');
+INSERT INTO "sqlite_stat1" VALUES('_sync_state_metadata',NULL,'1');
+INSERT INTO "sqlite_stat1" VALUES('properties','sqlite_autoindex_properties_1','4 1');
+INSERT INTO "sqlite_stat1" VALUES('metadata_sync_state','metadata_sync_state_index','2 1 1');
+INSERT INTO "sqlite_stat1" VALUES('search_index_docsize',NULL,'9000');
+INSERT INTO "sqlite_stat1" VALUES('search_index_content',NULL,'9000');
+INSERT INTO "sqlite_stat1" VALUES('search_index_stat',NULL,'1');
+INSERT INTO "sqlite_stat1" VALUES('search_index_segments',NULL,'450');
+INSERT INTO "sqlite_stat1" VALUES('search_index_segdir','sqlite_autoindex_search_index_segdir_1','9 5 1');
+PRAGMA writable_schema=ON;
+INSERT INTO sqlite_master(type,name,tbl_name,rootpage,sql)VALUES('table','search_index','search_index',0,'CREATE VIRTUAL TABLE search_index USING FTS4 (contact_id INTEGER REFERENCES contacts(_id) NOT NULL,content TEXT, name TEXT, tokens TEXT)');
+CREATE TABLE 'search_index_content'(docid INTEGER PRIMARY KEY, 'c0contact_id', 'c1content', 'c2name', 'c3tokens');
+CREATE TABLE 'search_index_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
+CREATE TABLE 'search_index_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx));
+CREATE TABLE 'search_index_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
+CREATE TABLE 'search_index_stat'(id INTEGER PRIMARY KEY, value BLOB);
+DELETE FROM sqlite_sequence;
+INSERT INTO "sqlite_sequence" VALUES('mimetypes',11);
+INSERT INTO "sqlite_sequence" VALUES('directories',1);
+INSERT INTO "sqlite_sequence" VALUES('voicemail_status',1);
+INSERT INTO "sqlite_sequence" VALUES('calls',496);
+INSERT INTO "sqlite_sequence" VALUES('accounts',1);
+INSERT INTO "sqlite_sequence" VALUES('groups',2);
+CREATE INDEX contacts_has_phone_index ON contacts (has_phone_number);
+CREATE INDEX contacts_name_raw_contact_id_index ON contacts (name_raw_contact_id);
+CREATE INDEX contacts_contact_last_updated_timestamp_index ON contacts(contact_last_updated_timestamp);
+CREATE INDEX deleted_contacts_contact_deleted_timestamp_index ON deleted_contacts(contact_deleted_timestamp);
+CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id);
+CREATE INDEX raw_contacts_source_id_account_id_index ON raw_contacts (sourceid, account_id);
+CREATE UNIQUE INDEX raw_contacts_backup_id_account_id_index ON raw_contacts (backup_id, account_id);
+CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
+CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
+CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
+CREATE INDEX data_hash_id_index ON data (hash_id);
+CREATE INDEX phone_lookup_index ON phone_lookup (normalized_number,raw_contact_id,data_id);
+CREATE INDEX phone_lookup_min_match_index ON phone_lookup (min_match,raw_contact_id,data_id);
+CREATE INDEX phone_lookup_data_id_min_match_index ON phone_lookup (data_id, min_match);
+CREATE INDEX name_lookup_raw_contact_id_index ON name_lookup (raw_contact_id);
+CREATE UNIQUE INDEX nickname_lookup_index ON nickname_lookup (name, cluster);
+CREATE INDEX groups_source_id_account_id_index ON groups (sourceid, account_id);
+CREATE UNIQUE INDEX aggregation_exception_index1 ON agg_exceptions (raw_contact_id1, raw_contact_id2);
+CREATE UNIQUE INDEX aggregation_exception_index2 ON agg_exceptions (raw_contact_id2, raw_contact_id1);
+CREATE UNIQUE INDEX data_usage_stat_index ON data_usage_stat (data_id, usage_type);
+CREATE UNIQUE INDEX metadata_sync_index ON metadata_sync (raw_contact_backup_id, account_id);
+CREATE UNIQUE INDEX metadata_sync_state_index ON metadata_sync_state (account_id);
+CREATE VIEW view_data AS SELECT data._id AS _id,hash_id, raw_contact_id, raw_contacts.contact_id AS contact_id, raw_contacts.account_id,accounts.account_name AS account_name,accounts.account_type AS account_type,accounts.data_set AS data_set,(CASE WHEN accounts.data_set IS NULL THEN accounts.account_type ELSE accounts.account_type||'/'||accounts.data_set END) AS account_type_and_data_set,raw_contacts.sourceid AS sourceid,raw_contacts.backup_id AS backup_id,raw_contacts.version AS version,raw_contacts.dirty AS dirty,raw_contacts.sync1 AS sync1,raw_contacts.sync2 AS sync2,raw_contacts.sync3 AS sync3,raw_contacts.sync4 AS sync4, is_primary, is_super_primary, data_version, data.package_id,package AS res_package,data.mimetype_id,mimetype AS mimetype, is_read_only, data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12, data13, data14, data15, carrier_presence, data_sync1, data_sync2, data_sync3, data_sync4, contacts.custom_ringtone AS custom_ringtone,contacts.send_to_voicemail AS send_to_voicemail,contacts.last_time_contacted AS last_time_contacted,contacts.times_contacted AS times_contacted,contacts.starred AS starred,contacts.pinned AS pinned, 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.phonebook_label AS phonebook_label, name_raw_contact.phonebook_bucket AS phonebook_bucket, name_raw_contact.sort_key_alt AS sort_key_alt, name_raw_contact.phonebook_label_alt AS phonebook_label_alt, name_raw_contact.phonebook_bucket_alt AS phonebook_bucket_alt, has_phone_number, name_raw_contact_id, lookup, photo_id, photo_file_id, CAST(EXISTS (SELECT _id FROM visible_contacts WHERE contacts._id=visible_contacts._id) AS INTEGER) AS in_visible_group, CAST(EXISTS (SELECT _id FROM default_directory WHERE contacts._id=default_directory._id) AS INTEGER) AS in_default_directory, status_update_id, contacts.contact_last_updated_timestamp, (CASE WHEN photo_file_id IS NULL THEN (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||raw_contacts.contact_id|| '/photo' END) ELSE 'content://com.android.contacts/display_photo/'||photo_file_id END) AS photo_uri, (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||raw_contacts.contact_id|| '/photo' END) AS photo_thumb_uri, 0 AS raw_contact_is_user_profile, groups.sourceid AS group_sourceid FROM data JOIN mimetypes ON (data.mimetype_id=mimetypes._id) JOIN raw_contacts ON (data.raw_contact_id=raw_contacts._id) JOIN accounts ON (raw_contacts.account_id=accounts._id) JOIN contacts ON (raw_contacts.contact_id=contacts._id) JOIN raw_contacts AS name_raw_contact ON(name_raw_contact_id=name_raw_contact._id) LEFT OUTER JOIN packages ON (data.package_id=packages._id) LEFT OUTER JOIN groups ON (mimetypes.mimetype='vnd.android.cursor.item/group_membership' AND groups._id=data.data1);
+CREATE VIEW view_raw_contacts AS SELECT raw_contacts._id AS _id,contact_id, aggregation_mode, raw_contact_is_read_only, deleted, raw_contacts.metadata_dirty, display_name_source, display_name, display_name_alt, phonetic_name, phonetic_name_style, sort_key, phonebook_label, phonebook_bucket, sort_key_alt, phonebook_label_alt, phonebook_bucket_alt, 0 AS raw_contact_is_user_profile, custom_ringtone,send_to_voicemail,last_time_contacted,times_contacted,starred,pinned, raw_contacts.account_id,accounts.account_name AS account_name,accounts.account_type AS account_type,accounts.data_set AS data_set,(CASE WHEN accounts.data_set IS NULL THEN accounts.account_type ELSE accounts.account_type||'/'||accounts.data_set END) AS account_type_and_data_set,raw_contacts.sourceid AS sourceid,raw_contacts.backup_id AS backup_id,raw_contacts.version AS version,raw_contacts.dirty AS dirty,raw_contacts.sync1 AS sync1,raw_contacts.sync2 AS sync2,raw_contacts.sync3 AS sync3,raw_contacts.sync4 AS sync4 FROM raw_contacts JOIN accounts ON (raw_contacts.account_id=accounts._id);
+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.phonebook_label AS phonebook_label, name_raw_contact.phonebook_bucket AS phonebook_bucket, name_raw_contact.sort_key_alt AS sort_key_alt, name_raw_contact.phonebook_label_alt AS phonebook_label_alt, name_raw_contact.phonebook_bucket_alt AS phonebook_bucket_alt, has_phone_number, name_raw_contact_id, lookup, photo_id, photo_file_id, CAST(EXISTS (SELECT _id FROM visible_contacts WHERE contacts._id=visible_contacts._id) AS INTEGER) AS in_visible_group, CAST(EXISTS (SELECT _id FROM default_directory WHERE contacts._id=default_directory._id) AS INTEGER) AS in_default_directory, status_update_id, contacts.contact_last_updated_timestamp, contacts.last_time_contacted AS last_time_contacted, contacts.send_to_voicemail AS send_to_voicemail, contacts.starred AS starred, contacts.pinned AS pinned, contacts.times_contacted AS times_contacted, (CASE WHEN photo_file_id IS NULL THEN (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||contacts._id|| '/photo' END) ELSE 'content://com.android.contacts/display_photo/'||photo_file_id END) AS photo_uri, (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||contacts._id|| '/photo' END) AS photo_thumb_uri, 0 AS is_user_profile FROM contacts JOIN raw_contacts AS name_raw_contact ON(name_raw_contact_id=name_raw_contact._id);
+CREATE VIEW view_raw_entities AS SELECT contact_id, raw_contacts.deleted AS deleted,raw_contacts.metadata_dirty, is_primary, is_super_primary, data_version, data.package_id,package AS res_package,data.mimetype_id,mimetype AS mimetype, is_read_only, data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12, data13, data14, data15, carrier_presence, data_sync1, data_sync2, data_sync3, data_sync4, raw_contacts.account_id,accounts.account_name AS account_name,accounts.account_type AS account_type,accounts.data_set AS data_set,(CASE WHEN accounts.data_set IS NULL THEN accounts.account_type ELSE accounts.account_type||'/'||accounts.data_set END) AS account_type_and_data_set,raw_contacts.sourceid AS sourceid,raw_contacts.backup_id AS backup_id,raw_contacts.version AS version,raw_contacts.dirty AS dirty,raw_contacts.sync1 AS sync1,raw_contacts.sync2 AS sync2,raw_contacts.sync3 AS sync3,raw_contacts.sync4 AS sync4, data_sync1, data_sync2, data_sync3, data_sync4, raw_contacts._id AS _id, data._id AS data_id,raw_contacts.starred AS starred,0 AS raw_contact_is_user_profile,groups.sourceid AS group_sourceid FROM raw_contacts JOIN accounts ON (raw_contacts.account_id=accounts._id) 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);
+CREATE VIEW view_entities AS SELECT raw_contacts.contact_id AS _id, raw_contacts.contact_id AS contact_id, raw_contacts.deleted AS deleted,raw_contacts.metadata_dirty, is_primary, is_super_primary, data_version, data.package_id,package AS res_package,data.mimetype_id,mimetype AS mimetype, is_read_only, data1, data2, data3, data4, data5, data6, data7, data8, data9, data10, data11, data12, data13, data14, data15, carrier_presence, data_sync1, data_sync2, data_sync3, data_sync4, raw_contacts.account_id,accounts.account_name AS account_name,accounts.account_type AS account_type,accounts.data_set AS data_set,(CASE WHEN accounts.data_set IS NULL THEN accounts.account_type ELSE accounts.account_type||'/'||accounts.data_set END) AS account_type_and_data_set,raw_contacts.sourceid AS sourceid,raw_contacts.backup_id AS backup_id,raw_contacts.version AS version,raw_contacts.dirty AS dirty,raw_contacts.sync1 AS sync1,raw_contacts.sync2 AS sync2,raw_contacts.sync3 AS sync3,raw_contacts.sync4 AS sync4, 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.phonebook_label AS phonebook_label, name_raw_contact.phonebook_bucket AS phonebook_bucket, name_raw_contact.sort_key_alt AS sort_key_alt, name_raw_contact.phonebook_label_alt AS phonebook_label_alt, name_raw_contact.phonebook_bucket_alt AS phonebook_bucket_alt, has_phone_number, name_raw_contact_id, lookup, photo_id, photo_file_id, CAST(EXISTS (SELECT _id FROM visible_contacts WHERE contacts._id=visible_contacts._id) AS INTEGER) AS in_visible_group, CAST(EXISTS (SELECT _id FROM default_directory WHERE contacts._id=default_directory._id) AS INTEGER) AS in_default_directory, status_update_id, contacts.contact_last_updated_timestamp, contacts.last_time_contacted AS last_time_contacted, contacts.send_to_voicemail AS send_to_voicemail, contacts.starred AS starred, contacts.pinned AS pinned, contacts.times_contacted AS times_contacted, (CASE WHEN photo_file_id IS NULL THEN (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||raw_contacts.contact_id|| '/photo' END) ELSE 'content://com.android.contacts/display_photo/'||photo_file_id END) AS photo_uri, (CASE WHEN photo_id IS NULL OR photo_id=0 THEN NULL ELSE 'content://com.android.contacts/contacts/'||raw_contacts.contact_id|| '/photo' END) AS photo_thumb_uri, 0 AS is_user_profile, data_sync1, data_sync2, data_sync3, data_sync4, raw_contacts._id AS raw_contact_id, data._id AS data_id,groups.sourceid AS group_sourceid FROM raw_contacts JOIN accounts ON (raw_contacts.account_id=accounts._id) JOIN contacts ON (raw_contacts.contact_id=contacts._id) JOIN raw_contacts AS name_raw_contact ON(name_raw_contact_id=name_raw_contact._id) 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);
+CREATE VIEW view_data_usage_stat AS SELECT data_usage_stat.stat_id AS stat_id, data_id, raw_contacts.contact_id AS contact_id, mimetypes.mimetype AS mimetype, usage_type, times_used, last_time_used FROM data_usage_stat JOIN data ON (data._id=data_usage_stat.data_id) JOIN raw_contacts ON (raw_contacts._id=data.raw_contact_id ) JOIN mimetypes ON (mimetypes._id=data.mimetype_id);
+CREATE VIEW view_stream_items AS SELECT stream_items._id, contacts._id AS contact_id, contacts.lookup AS contact_lookup, accounts.account_name, accounts.account_type, accounts.data_set, stream_items.raw_contact_id as raw_contact_id, raw_contacts.sourceid as raw_contact_source_id, stream_items.res_package, stream_items.icon, stream_items.label, stream_items.text, stream_items.timestamp, stream_items.comments, stream_items.stream_item_sync1, stream_items.stream_item_sync2, stream_items.stream_item_sync3, stream_items.stream_item_sync4 FROM stream_items JOIN raw_contacts ON (stream_items.raw_contact_id=raw_contacts._id) JOIN accounts ON (raw_contacts.account_id=accounts._id) JOIN contacts ON (raw_contacts.contact_id=contacts._id);
+CREATE VIEW view_metadata_sync AS SELECT metadata_sync._id, raw_contact_backup_id, account_name, account_type, data_set, data, deleted FROM metadata_sync JOIN accounts ON (metadata_sync.account_id=accounts._id);
+CREATE VIEW view_metadata_sync_state AS SELECT metadata_sync_state._id, account_name, account_type, data_set, state FROM metadata_sync_state JOIN accounts ON (metadata_sync_state.account_id=accounts._id);
+CREATE VIEW view_groups AS SELECT groups._id AS _id,groups.account_id AS account_id,accounts.account_name AS account_name,accounts.account_type AS account_type,accounts.data_set AS data_set,(CASE WHEN accounts.data_set IS NULL THEN accounts.account_type ELSE accounts.account_type||'/'||accounts.data_set END) AS account_type_and_data_set,sourceid,version,dirty,title,title_res,notes,system_id,deleted,group_visible,should_sync,auto_add,favorites,group_is_read_only,sync1,sync2,sync3,sync4,package AS res_package FROM groups JOIN accounts ON (groups.account_id=accounts._id) LEFT OUTER JOIN packages ON (groups.package_id=packages._id);
+CREATE TRIGGER raw_contacts_deleted    BEFORE DELETE ON raw_contacts BEGIN    DELETE FROM data     WHERE raw_contact_id=OLD._id;   DELETE FROM agg_exceptions     WHERE raw_contact_id1=OLD._id        OR raw_contact_id2=OLD._id;   DELETE FROM visible_contacts     WHERE _id=OLD.contact_id       AND (SELECT COUNT(*) FROM raw_contacts            WHERE contact_id=OLD.contact_id           )=1;   DELETE FROM default_directory     WHERE _id=OLD.contact_id       AND (SELECT COUNT(*) FROM raw_contacts            WHERE contact_id=OLD.contact_id           )=1;   DELETE FROM contacts     WHERE _id=OLD.contact_id       AND (SELECT COUNT(*) FROM raw_contacts            WHERE contact_id=OLD.contact_id           )=1; END;
+CREATE TRIGGER raw_contacts_marked_deleted    AFTER UPDATE ON raw_contacts BEGIN    UPDATE raw_contacts     SET version=OLD.version+1      WHERE _id=OLD._id       AND NEW.deleted!= OLD.deleted; END;
+CREATE TRIGGER data_updated AFTER UPDATE ON data BEGIN    UPDATE data     SET data_version=OLD.data_version+1      WHERE _id=OLD._id;   UPDATE raw_contacts     SET version=version+1      WHERE _id=OLD.raw_contact_id; END;
+CREATE TRIGGER data_deleted BEFORE DELETE ON data BEGIN    UPDATE raw_contacts     SET version=version+1      WHERE _id=OLD.raw_contact_id;   DELETE FROM phone_lookup     WHERE data_id=OLD._id;   DELETE FROM status_updates     WHERE status_update_data_id=OLD._id;   DELETE FROM name_lookup     WHERE data_id=OLD._id; END;
+CREATE TRIGGER groups_updated1    AFTER UPDATE ON groups BEGIN    UPDATE groups     SET version=OLD.version+1     WHERE _id=OLD._id; END;
+CREATE TRIGGER groups_auto_add_updated1    AFTER UPDATE OF auto_add ON groups BEGIN    DELETE FROM default_directory; INSERT OR IGNORE INTO default_directory     SELECT contact_id     FROM raw_contacts     WHERE raw_contacts.account_id=(SELECT _id FROM accounts WHERE account_name IS NULL AND account_type IS NULL AND data_set IS NULL); INSERT OR IGNORE INTO default_directory     SELECT contact_id         FROM raw_contacts     WHERE NOT EXISTS         (SELECT _id             FROM groups             WHERE raw_contacts.account_id = groups.account_id             AND auto_add != 0); INSERT OR IGNORE INTO default_directory     SELECT contact_id         FROM raw_contacts     JOIN data           ON (raw_contacts._id=raw_contact_id)     WHERE mimetype_id=(SELECT _id FROM mimetypes WHERE mimetype='vnd.android.cursor.item/group_membership')     AND EXISTS         (SELECT _id             FROM groups                 WHERE raw_contacts.account_id = groups.account_id                 AND auto_add != 0); END;
+CREATE VIEW view_v1_people AS SELECT raw_contacts._id AS _id, name.data1 AS name, raw_contacts.display_name AS display_name, trim(trim(ifnull(name.data7,' ')||' '||ifnull(name.data8,' '))||' '||ifnull(name.data9,' '))  AS phonetic_name , note.data1 AS notes, accounts.account_name, accounts.account_type, raw_contacts.times_contacted AS times_contacted, raw_contacts.last_time_contacted AS last_time_contacted, raw_contacts.custom_ringtone AS custom_ringtone, raw_contacts.send_to_voicemail AS send_to_voicemail, raw_contacts.starred AS starred, organization._id AS primary_organization, email._id AS primary_email, phone._id AS primary_phone, phone.data1 AS number, phone.data2 AS type, phone.data3 AS label, _PHONE_NUMBER_STRIPPED_REVERSED(phone.data1) AS number_key FROM raw_contacts JOIN accounts ON (raw_contacts.account_id=accounts._id) LEFT OUTER JOIN data name ON (raw_contacts._id = name.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = name.mimetype_id)='vnd.android.cursor.item/name') LEFT OUTER JOIN data organization ON (raw_contacts._id = organization.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = organization.mimetype_id)='vnd.android.cursor.item/organization' AND organization.is_primary) LEFT OUTER JOIN data email ON (raw_contacts._id = email.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = email.mimetype_id)='vnd.android.cursor.item/email_v2' AND email.is_primary) LEFT OUTER JOIN data note ON (raw_contacts._id = note.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = note.mimetype_id)='vnd.android.cursor.item/note') LEFT OUTER JOIN data phone ON (raw_contacts._id = phone.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = phone.mimetype_id)='vnd.android.cursor.item/phone_v2' AND phone.is_primary) WHERE raw_contacts.deleted=0;
+CREATE VIEW view_v1_organizations AS SELECT data._id AS _id, raw_contact_id AS person, is_primary AS isprimary, accounts.account_name, accounts.account_type, data1 AS company, data2 AS type, data3 AS label, data4 AS title FROM data JOIN mimetypes ON (data.mimetype_id = mimetypes._id) JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) JOIN accounts ON (raw_contacts.account_id=accounts._id) WHERE mimetypes.mimetype='vnd.android.cursor.item/organization' AND raw_contacts.deleted=0;
+CREATE VIEW view_v1_contact_methods AS SELECT data._id AS _id, data.raw_contact_id AS person, CAST ((CASE WHEN mimetype='vnd.android.cursor.item/email_v2' THEN 1 ELSE (CASE WHEN mimetype='vnd.android.cursor.item/im' THEN 3 ELSE (CASE WHEN mimetype='vnd.android.cursor.item/postal-address_v2' THEN 2 ELSE NULL END) END) END) AS INTEGER) AS kind, data.is_primary AS isprimary, data.data2 AS type, (CASE WHEN mimetype='vnd.android.cursor.item/im' THEN (CASE WHEN data.data5=-1 THEN 'custom:'||data.data6 ELSE 'pre:'||data.data5 END) ELSE data.data1 END) AS data, data.data3 AS label, data.data14 AS aux_data, name.data1 AS name, raw_contacts.display_name AS display_name, trim(trim(ifnull(name.data7,' ')||' '||ifnull(name.data8,' '))||' '||ifnull(name.data9,' '))  AS phonetic_name , note.data1 AS notes, accounts.account_name, accounts.account_type, raw_contacts.times_contacted AS times_contacted, raw_contacts.last_time_contacted AS last_time_contacted, raw_contacts.custom_ringtone AS custom_ringtone, raw_contacts.send_to_voicemail AS send_to_voicemail, raw_contacts.starred AS starred, organization._id AS primary_organization, email._id AS primary_email, phone._id AS primary_phone, phone.data1 AS number, phone.data2 AS type, phone.data3 AS label, _PHONE_NUMBER_STRIPPED_REVERSED(phone.data1) AS number_key FROM data JOIN mimetypes ON (mimetypes._id = data.mimetype_id) JOIN raw_contacts ON (raw_contacts._id = data.raw_contact_id) JOIN accounts ON (raw_contacts.account_id=accounts._id) LEFT OUTER JOIN data name ON (raw_contacts._id = name.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = name.mimetype_id)='vnd.android.cursor.item/name') LEFT OUTER JOIN data organization ON (raw_contacts._id = organization.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = organization.mimetype_id)='vnd.android.cursor.item/organization' AND organization.is_primary) LEFT OUTER JOIN data email ON (raw_contacts._id = email.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = email.mimetype_id)='vnd.android.cursor.item/email_v2' AND email.is_primary) LEFT OUTER JOIN data note ON (raw_contacts._id = note.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = note.mimetype_id)='vnd.android.cursor.item/note') LEFT OUTER JOIN data phone ON (raw_contacts._id = phone.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = phone.mimetype_id)='vnd.android.cursor.item/phone_v2' AND phone.is_primary) WHERE kind IS NOT NULL AND raw_contacts.deleted=0;
+CREATE VIEW view_v1_phones AS SELECT DISTINCT data._id AS _id, data.raw_contact_id AS person, data.is_primary AS isprimary, data.data1 AS number, data.data2 AS type, data.data3 AS label, _PHONE_NUMBER_STRIPPED_REVERSED(data.data1) AS number_key, name.data1 AS name, raw_contacts.display_name AS display_name, trim(trim(ifnull(name.data7,' ')||' '||ifnull(name.data8,' '))||' '||ifnull(name.data9,' '))  AS phonetic_name , note.data1 AS notes, accounts.account_name, accounts.account_type, raw_contacts.times_contacted AS times_contacted, raw_contacts.last_time_contacted AS last_time_contacted, raw_contacts.custom_ringtone AS custom_ringtone, raw_contacts.send_to_voicemail AS send_to_voicemail, raw_contacts.starred AS starred, organization._id AS primary_organization, email._id AS primary_email, phone._id AS primary_phone, phone.data1 AS number, phone.data2 AS type, phone.data3 AS label, _PHONE_NUMBER_STRIPPED_REVERSED(phone.data1) AS number_key FROM data JOIN phone_lookup ON (data._id = phone_lookup.data_id) JOIN mimetypes ON (mimetypes._id = data.mimetype_id) JOIN raw_contacts ON (raw_contacts._id = data.raw_contact_id) JOIN accounts ON (raw_contacts.account_id=accounts._id) LEFT OUTER JOIN data name ON (raw_contacts._id = name.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = name.mimetype_id)='vnd.android.cursor.item/name') LEFT OUTER JOIN data organization ON (raw_contacts._id = organization.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = organization.mimetype_id)='vnd.android.cursor.item/organization' AND organization.is_primary) LEFT OUTER JOIN data email ON (raw_contacts._id = email.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = email.mimetype_id)='vnd.android.cursor.item/email_v2' AND email.is_primary) LEFT OUTER JOIN data note ON (raw_contacts._id = note.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = note.mimetype_id)='vnd.android.cursor.item/note') LEFT OUTER JOIN data phone ON (raw_contacts._id = phone.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = phone.mimetype_id)='vnd.android.cursor.item/phone_v2' AND phone.is_primary) WHERE mimetypes.mimetype='vnd.android.cursor.item/phone_v2' AND raw_contacts.deleted=0;
+CREATE VIEW view_v1_extensions AS SELECT data._id AS _id, data.raw_contact_id AS person, accounts.account_name, accounts.account_type, data1 AS name, data2 AS value FROM data JOIN mimetypes ON (data.mimetype_id = mimetypes._id) JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id) JOIN accounts ON (raw_contacts.account_id=accounts._id) WHERE mimetypes.mimetype='vnd.android.cursor.item/contact_extensions' AND raw_contacts.deleted=0;
+CREATE VIEW view_v1_groups AS SELECT groups._id AS _id, accounts.account_name, accounts.account_type, title AS name, notes AS notes , system_id AS system_id FROM groups JOIN accounts ON (groups.account_id=accounts._id);
+CREATE VIEW view_v1_group_membership AS SELECT data._id AS _id, data.raw_contact_id AS person, accounts.account_name, accounts.account_type, data1 AS group_id, title AS name, notes AS notes, system_id AS system_id, groups.sourceid AS group_sync_id, accounts.account_name AS group_sync_account, accounts.account_type AS group_sync_account_type FROM data JOIN mimetypes ON (data.mimetype_id = mimetypes._id) JOIN raw_contacts ON (data.raw_contact_id = raw_contacts._id)  JOIN accounts ON (raw_contacts.account_id=accounts._id)LEFT OUTER JOIN packages ON (data.package_id = packages._id) LEFT OUTER JOIN groups   ON (mimetypes.mimetype='vnd.android.cursor.item/group_membership'       AND groups._id = data.data1)  WHERE mimetypes.mimetype='vnd.android.cursor.item/group_membership' AND raw_contacts.deleted=0;
+CREATE VIEW view_v1_photos AS SELECT data._id AS _id, data.raw_contact_id AS person, accounts.account_name, accounts.account_type, data.data15 AS data, legacy_photo.data4 AS exists_on_server, legacy_photo.data3 AS download_required, legacy_photo.data2 AS local_version, legacy_photo.data5 AS sync_error FROM data JOIN mimetypes ON (mimetypes._id = data.mimetype_id) JOIN raw_contacts ON (raw_contacts._id = data.raw_contact_id) JOIN accounts ON (raw_contacts.account_id=accounts._id) LEFT OUTER JOIN data name ON (raw_contacts._id = name.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = name.mimetype_id)='vnd.android.cursor.item/name') LEFT OUTER JOIN data organization ON (raw_contacts._id = organization.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = organization.mimetype_id)='vnd.android.cursor.item/organization' AND organization.is_primary) LEFT OUTER JOIN data email ON (raw_contacts._id = email.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = email.mimetype_id)='vnd.android.cursor.item/email_v2' AND email.is_primary) LEFT OUTER JOIN data note ON (raw_contacts._id = note.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = note.mimetype_id)='vnd.android.cursor.item/note') LEFT OUTER JOIN data phone ON (raw_contacts._id = phone.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = phone.mimetype_id)='vnd.android.cursor.item/phone_v2' AND phone.is_primary) LEFT OUTER JOIN data legacy_photo ON (raw_contacts._id = legacy_photo.raw_contact_id AND (SELECT mimetype FROM mimetypes WHERE mimetypes._id = legacy_photo.mimetype_id)='vnd.android.cursor.item/photo_v1_extras' AND data._id = legacy_photo.data1) WHERE mimetypes.mimetype='vnd.android.cursor.item/photo' AND raw_contacts.deleted=0;
+CREATE INDEX name_lookup_index ON name_lookup (normalized_name,name_type, raw_contact_id, data_id);
+CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key);
+CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt);
+PRAGMA writable_schema=OFF;
+COMMIT;
diff --git a/tests/src/com/android/providers/contacts/BaseDatabaseHelperUpgradeTest.java b/tests/src/com/android/providers/contacts/BaseDatabaseHelperUpgradeTest.java
new file mode 100644
index 00000000..a087233b
--- /dev/null
+++ b/tests/src/com/android/providers/contacts/BaseDatabaseHelperUpgradeTest.java
@@ -0,0 +1,275 @@
+/*
+ * Copyright (C) 2015 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License
+ */
+package com.android.providers.contacts;
+
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.test.AndroidTestCase;
+
+import junit.framework.AssertionFailedError;
+
+import java.util.HashMap;
+
+/**
+ * Unit tests for database create/upgrade operations.
+ *
+ * Run the test like this: <code> runtest -c com.android.providers.contacts.BaseDatabaseHelperUpgradeTest
+ * contactsprov </code>
+ */
+public class BaseDatabaseHelperUpgradeTest extends AndroidTestCase {
+
+    protected static final String INTEGER = "INTEGER";
+    protected static final String TEXT = "TEXT";
+    protected static final String STRING = "STRING";
+    protected static final String BLOB = "BLOB";
+
+    protected SQLiteDatabase mDb;
+
+    /**
+     * The column info returned by PRAGMA table_info()
+     */
+    protected static class TableColumn {
+
+        public int cid;
+        public String name;
+        public String type;
+        public boolean notnull;
+        // default value
+        public String dflt_value;
+        // primary key. Not tested.
+        public int pk;
+
+        public TableColumn() {
+
+        }
+
+        public TableColumn(String name, String type, boolean notnull, String defaultValue) {
+            this.name = name;
+            this.type = type;
+            this.notnull = notnull;
+            this.dflt_value = defaultValue;
+        }
+    }
+
+    protected static class TableStructure {
+
+        private final HashMap<String, TableColumn> mColumns = new HashMap<String, TableColumn>();
+        private final String mName;
+
+        public TableStructure(SQLiteDatabase db, String tableName) {
+            mName = tableName;
+            try (final Cursor cursor = db.rawQuery("PRAGMA table_info(" + tableName + ");", null)) {
+                final int cidIndex = cursor.getColumnIndex("cid");
+                final int nameIndex = cursor.getColumnIndex("name");
+                final int typeIndex = cursor.getColumnIndex("type");
+                final int notNullIndex = cursor.getColumnIndex("notnull");
+                final int dfltValueIndex = cursor.getColumnIndex("dflt_value");
+                final int pkIndex = cursor.getColumnIndex("pk");
+                cursor.moveToPosition(-1);
+                while (cursor.moveToNext()) {
+                    TableColumn column = new TableColumn();
+                    column.cid = cursor.getInt(cidIndex);
+                    column.name = cursor.getString(nameIndex);
+                    column.type = cursor.getString(typeIndex);
+                    column.notnull = cursor.getInt(notNullIndex) != 0;
+                    column.dflt_value = cursor.getString(dfltValueIndex);
+                    column.pk = cursor.getInt(pkIndex);
+
+                    addColumn(column);
+                }
+            }
+        }
+
+        private TableStructure() {
+            mName = "";
+        }
+
+        private void addColumn(TableColumn column) {
+            mColumns.put(column.name, column);
+        }
+
+        public void assertHasColumn(String name, String type, boolean notnull,
+                String defaultValue) {
+            final TableColumn column = mColumns.get(name);
+            if (column == null) {
+                throw new AssertionFailedError("Table " + mName + ": Column missing: " + name);
+            }
+            if (!type.equals(column.type)) {
+                throw new AssertionFailedError("Table " + mName + ": Column " + name + " type:"
+                        + column.type + ", " + type + " expected");
+            }
+            if (!notnull == column.notnull) {
+                throw new AssertionFailedError("Table " + mName + ": Column " + name + " notnull:"
+                        + column.notnull + ", " + notnull + " expected");
+            }
+            if (defaultValue == null) {
+                if (column.dflt_value != null) {
+                    throw new AssertionFailedError("Table " + mName + ": Column " + name
+                            + " defaultValue: " + column.dflt_value + ", null expected");
+                }
+            } else if (!defaultValue.equals(column.dflt_value)) {
+                throw new AssertionFailedError("Table " + mName + ": Column " + name
+                        + " defaultValue:" + column.dflt_value + ", " + defaultValue + " expected");
+            }
+        }
+
+
+        public void assertHasColumns(TableColumn[] columns) {
+            for (final TableColumn column : columns) {
+                assertHasColumn(column.name, column.type, column.notnull, column.dflt_value);
+            }
+        }
+
+        /**
+         * Assert the TableStructure has every column in @param columns, and nothing else.
+         */
+        public void assertSame(TableColumn[] columns) {
+            assertHasColumns(columns);
+            if (columns.length != mColumns.size()) {
+                throw new RuntimeException("column count mismatch");
+            }
+        }
+
+    }
+
+    /**
+     * Used to store a tables' name and its' current structure in a array.
+     */
+    protected static class TableListEntry {
+
+        public final String name;
+        public final TableColumn[] columns;
+
+        public TableListEntry(String name, TableColumn[] columns) {
+            this.name = name;
+            this.columns = columns;
+        }
+    }
+
+    @Override
+    protected void setUp() throws Exception {
+        super.setUp();
+        mDb = SQLiteDatabase.create(null);
+    }
+
+    @Override
+    protected void tearDown() throws Exception {
+        mDb.close();
+        super.tearDown();
+    }
+
+    protected void assertDatabaseStructureSameAsList(TableListEntry[] list) {
+        for (TableListEntry entry : list) {
+            TableStructure structure = new TableStructure(mDb, entry.name);
+            structure.assertSame(entry.columns);
+        }
+    }
+
+    public void testAssertHasColumn_Match() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, null);
+        table.assertHasColumn("foo", INTEGER, false, null);
+    }
+
+    public void testAssertHasColumn_Empty() {
+        TableStructure table = new TableStructure();
+
+        try {
+            table.assertHasColumn("bar", INTEGER, false, null);
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    public void testAssertHasColumn_ColumnNotExist() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, null);
+
+        try {
+            table.assertHasColumn("bar", INTEGER, false, null);
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    public void testAssertHasColumn_TypeMismatch() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, null);
+
+        try {
+            table.assertHasColumn("foo", TEXT, false, null);
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    public void testAssertHasColumn_NotNullMismatch() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, null);
+
+        try {
+            table.assertHasColumn("foo", INTEGER, true, null);
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    public void testAssertHasColumn_DefaultMatch() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, "baz");
+        table.assertHasColumn("foo", INTEGER, false, "baz");
+    }
+
+    public void testAssertHasColumn_DefaultMismatch() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, "bar");
+
+        try {
+            table.assertHasColumn("foo", INTEGER, false, "baz");
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    public void testAssertHasColumn_DefaultMismatch_Null1() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, null);
+
+        try {
+            table.assertHasColumn("foo", INTEGER, false, "baz");
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    public void testAssertHasColumn_DefaultMismatch_Null2() {
+        TableStructure table = createOneColumnTable("foo", INTEGER, false, "baz");
+
+        try {
+            table.assertHasColumn("foo", INTEGER, false, null);
+            throw new AssertionError("Assert should fail");
+        } catch (AssertionFailedError e) {
+            // Should fail
+        }
+    }
+
+    private TableStructure createOneColumnTable(String name, String type, boolean notnull,
+            String defaultValue) {
+        TableStructure table = new TableStructure();
+        table.addColumn(new TableColumn(name, type, notnull, defaultValue));
+        return table;
+    }
+
+}
diff --git a/tests/src/com/android/providers/contacts/ContactsDatabaseHelperUpgradeTest.java b/tests/src/com/android/providers/contacts/ContactsDatabaseHelperUpgradeTest.java
new file mode 100644
index 00000000..422f073e
--- /dev/null
+++ b/tests/src/com/android/providers/contacts/ContactsDatabaseHelperUpgradeTest.java
@@ -0,0 +1,514 @@
+/*
+ * Copyright (C) 2015 The Android Open Source Project
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License
+ */
+
+package com.android.providers.contacts;
+
+import android.database.Cursor;
+import android.database.sqlite.SQLiteDatabase;
+import android.provider.BaseColumns;
+import android.provider.CallLog.Calls;
+import android.provider.ContactsContract.AggregationExceptions;
+import android.provider.ContactsContract.Contacts;
+import android.provider.ContactsContract.Data;
+import android.provider.ContactsContract.DeletedContacts;
+import android.provider.ContactsContract.Directory;
+import android.provider.ContactsContract.DisplayNameSources;
+import android.provider.ContactsContract.Groups;
+import android.provider.ContactsContract.MetadataSync;
+import android.provider.ContactsContract.MetadataSyncState;
+import android.provider.ContactsContract.PhotoFiles;
+import android.provider.ContactsContract.PinnedPositions;
+import android.provider.ContactsContract.RawContacts;
+import android.provider.ContactsContract.Settings;
+import android.provider.ContactsContract.StatusUpdates;
+import android.provider.ContactsContract.StreamItemPhotos;
+import android.provider.ContactsContract.StreamItems;
+import android.provider.VoicemailContract.Status;
+import android.provider.VoicemailContract.Voicemails;
+import android.test.AndroidTestCase;
+import android.test.suitebuilder.annotation.SmallTest;
+
+import com.android.providers.contacts.ContactsDatabaseHelper.AccountsColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.AggregationExceptionColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.ContactsColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.DataColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.DataUsageStatColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.DirectoryColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.GroupsColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.MetadataSyncColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.MetadataSyncStateColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.MimetypesColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.NameLookupColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.NicknameLookupColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.PackagesColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.PhoneLookupColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.PreAuthorizedUris;
+import com.android.providers.contacts.ContactsDatabaseHelper.PropertiesColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.RawContactsColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.StatusUpdatesColumns;
+import com.android.providers.contacts.ContactsDatabaseHelper.Tables;
+
+import junit.framework.AssertionFailedError;
+
+import java.io.BufferedReader;
+import java.io.IOException;
+import java.io.InputStream;
+import java.io.InputStreamReader;
+import java.util.HashMap;
+
+/**
+ * Unit tests for database create/upgrade operations in  {@link ContactsDatabaseHelper}.
+ *
+ * Tests are only performed after version 1108, based on the sql dump asset in
+ * ContactsDatabaseHelperUpgradeTest#CONTACTS2_DB_1108_ASSET_NAME
+ *
+ * Run the test like this: <code> runtest -c com.android.providers.contacts.ContactsDatabaseHelperUpgradeTest
+ * contactsprov </code>
+ */
+@SmallTest
+public class ContactsDatabaseHelperUpgradeTest extends BaseDatabaseHelperUpgradeTest {
+
+    private static final String CONTACTS2_DB_1108_ASSET_NAME = "upgradeTest/contacts2_1108.sql";
+
+    private ContactsDatabaseHelper mHelper;
+
+
+    @Override
+    protected void setUp() throws Exception {
+        super.setUp();
+        mHelper = ContactsDatabaseHelper.getNewInstanceForTest(getContext());
+        mHelper.onConfigure(mDb);
+    }
+
+    @Override
+    protected void tearDown() throws Exception {
+        mHelper.close();
+        super.tearDown();
+    }
+
+    public void testDatabaseCreate() {
+        mHelper.onCreate(mDb);
+        assertDatabaseStructureSameAsList(TABLE_LIST);
+    }
+
+    public void testDatabaseUpgrade_UpgradeToCurrent() {
+        create1108(mDb);
+        mHelper.onUpgrade(mDb, 1108, mHelper.DATABASE_VERSION);
+        assertDatabaseStructureSameAsList(TABLE_LIST);
+    }
+
+    /**
+     * Upgrade the database version by version, and test it in each step
+     */
+    public void testDatabaseUpgrade_Incremental() {
+        create1108(mDb);
+        upgradeTo1109();
+        upgradeTo1110();
+        assertDatabaseStructureSameAsList(TABLE_LIST);
+    }
+
+    private void upgradeTo1109() {
+        mHelper.onUpgrade(mDb, 1108, 1109);
+        TableStructure calls = new TableStructure(mDb, Tables.CALLS);
+        calls.assertHasColumn(Calls.LAST_MODIFIED, INTEGER, false, "0");
+
+        TableStructure voicemailStatus = new TableStructure(mDb, Tables.VOICEMAIL_STATUS);
+        voicemailStatus.assertHasColumn(Status.QUOTA_OCCUPIED, INTEGER, false, "-1");
+        voicemailStatus.assertHasColumn(Status.QUOTA_TOTAL, INTEGER, false, "-1");
+    }
+
+    private void upgradeTo1110() {
+        mHelper.onUpgrade(mDb, 1109, 1110);
+        // TODO: Test this upgrade.
+    }
+
+    /**
+     * A snapshot of onCreate() at version 1108, for testing upgrades. Future tests should upgrade
+     * incrementally from this version.
+     */
+    private void create1108(SQLiteDatabase db) {
+        try (InputStream input = getTestContext().getAssets().open(CONTACTS2_DB_1108_ASSET_NAME);) {
+            BufferedReader r = new BufferedReader(new InputStreamReader(input));
+            String query;
+            while ((query = r.readLine()) != null) {
+                db.execSQL(query);
+            }
+        } catch (IOException e) {
+            throw new RuntimeException(e.toString());
+        }
+    }
+
+    /**
+     * The structure of all tables in current version database.
+     */
+    private static final TableColumn[] PROPERTIES_COLUMNS = new TableColumn[] {
+            new TableColumn(PropertiesColumns.PROPERTY_KEY, TEXT, false, null),
+            new TableColumn(PropertiesColumns.PROPERTY_VALUE, TEXT, false, null),
+    };
+
+    private static final TableColumn[] ACCOUNTS_COLUMNS = new TableColumn[] {
+            new TableColumn(AccountsColumns._ID, INTEGER, false, null),
+            new TableColumn(AccountsColumns.ACCOUNT_NAME, TEXT, false, null),
+            new TableColumn(AccountsColumns.ACCOUNT_TYPE, TEXT, false, null),
+            new TableColumn(AccountsColumns.DATA_SET, TEXT, false, null),
+    };
+
+    private static final TableColumn[] CONTACTS_COLUMNS = new TableColumn[] {
+            new TableColumn(BaseColumns._ID, INTEGER, false, null),
+            new TableColumn(Contacts.NAME_RAW_CONTACT_ID, INTEGER, false, null),
+            new TableColumn(Contacts.PHOTO_ID, INTEGER, false, null),
+            new TableColumn(Contacts.PHOTO_FILE_ID, INTEGER, false, null),
+            new TableColumn(Contacts.CUSTOM_RINGTONE, TEXT, false, null),
+            new TableColumn(Contacts.SEND_TO_VOICEMAIL, INTEGER, true, "0"),
+            new TableColumn(Contacts.TIMES_CONTACTED, INTEGER, true, "0"),
+            new TableColumn(Contacts.LAST_TIME_CONTACTED, INTEGER, false, null),
+            new TableColumn(Contacts.STARRED, INTEGER, true, "0"),
+            new TableColumn(Contacts.PINNED, INTEGER, true,
+                    String.valueOf(PinnedPositions.UNPINNED)),
+            new TableColumn(Contacts.HAS_PHONE_NUMBER, INTEGER, true, "0"),
+            new TableColumn(Contacts.LOOKUP_KEY, TEXT, false, null),
+            new TableColumn(ContactsColumns.LAST_STATUS_UPDATE_ID, INTEGER, false, null),
+            new TableColumn(Contacts.CONTACT_LAST_UPDATED_TIMESTAMP, INTEGER, false, null),
+    };
+
+    private static final TableColumn[] DELETED_CONTACTS_COLUMNS = new TableColumn[] {
+            new TableColumn(DeletedContacts.CONTACT_ID, INTEGER, false, null),
+            new TableColumn(DeletedContacts.CONTACT_DELETED_TIMESTAMP, INTEGER, true, "0"),
+    };
+
+    private static final TableColumn[] RAW_CONTACTS_COLUMNS = new TableColumn[] {
+            new TableColumn(RawContacts._ID, INTEGER, false, null),
+            new TableColumn(RawContactsColumns.ACCOUNT_ID, INTEGER, false, null),
+            new TableColumn(RawContacts.SOURCE_ID, TEXT, false, null),
+            new TableColumn(RawContacts.BACKUP_ID, TEXT, false, null),
+            new TableColumn(RawContacts.RAW_CONTACT_IS_READ_ONLY, INTEGER, true, "0"),
+            new TableColumn(RawContacts.VERSION, INTEGER, true, "1"),
+            new TableColumn(RawContacts.DIRTY, INTEGER, true, "0"),
+            new TableColumn(RawContacts.DELETED, INTEGER, true, "0"),
+            new TableColumn(RawContacts.METADATA_DIRTY, INTEGER, true, "0"),
+            new TableColumn(RawContacts.CONTACT_ID, INTEGER, false, null),
+            new TableColumn(RawContacts.AGGREGATION_MODE, INTEGER, true,
+                    String.valueOf(RawContacts.AGGREGATION_MODE_DEFAULT)),
+            new TableColumn(RawContactsColumns.AGGREGATION_NEEDED, INTEGER, true, "1"),
+            new TableColumn(RawContacts.CUSTOM_RINGTONE, TEXT, false, null),
+            new TableColumn(RawContacts.SEND_TO_VOICEMAIL, INTEGER, true, "0"),
+            new TableColumn(RawContacts.TIMES_CONTACTED, INTEGER, true, "0"),
+            new TableColumn(RawContacts.LAST_TIME_CONTACTED, INTEGER, false, null),
+            new TableColumn(RawContacts.STARRED, INTEGER, true, "0"),
+            new TableColumn(RawContacts.PINNED, INTEGER, true,
+                    String.valueOf(PinnedPositions.UNPINNED)),
+            new TableColumn(RawContacts.DISPLAY_NAME_PRIMARY, TEXT, false, null),
+            new TableColumn(RawContacts.DISPLAY_NAME_ALTERNATIVE, TEXT, false, null),
+            new TableColumn(RawContacts.DISPLAY_NAME_SOURCE, INTEGER, true, String.valueOf(
+                    DisplayNameSources.UNDEFINED)),
+            new TableColumn(RawContacts.PHONETIC_NAME, TEXT, false, null),
+            new TableColumn(RawContacts.PHONETIC_NAME_STYLE, TEXT, false, null),
+            new TableColumn(RawContacts.SORT_KEY_PRIMARY, TEXT, false, null),
+            new TableColumn(RawContactsColumns.PHONEBOOK_LABEL_PRIMARY, TEXT, false, null),
+            new TableColumn(RawContactsColumns.PHONEBOOK_BUCKET_PRIMARY, INTEGER, false, null),
+            new TableColumn(RawContacts.SORT_KEY_ALTERNATIVE, TEXT, false, null),
+            new TableColumn(RawContactsColumns.PHONEBOOK_LABEL_ALTERNATIVE, TEXT, false, null),
+            new TableColumn(RawContactsColumns.PHONEBOOK_BUCKET_ALTERNATIVE, INTEGER, false, null),
+            new TableColumn(RawContactsColumns.NAME_VERIFIED_OBSOLETE, INTEGER, true, "0"),
+            new TableColumn(RawContacts.SYNC1, TEXT, false, null),
+            new TableColumn(RawContacts.SYNC2, TEXT, false, null),
+            new TableColumn(RawContacts.SYNC3, TEXT, false, null),
+            new TableColumn(RawContacts.SYNC4, TEXT, false, null),
+    };
+
+    private static final TableColumn[] STREAM_ITEMS_COLUMNS = new TableColumn[] {
+            new TableColumn(StreamItems._ID, INTEGER, false, null),
+            new TableColumn(StreamItems.RAW_CONTACT_ID, INTEGER, true, null),
+            new TableColumn(StreamItems.RES_PACKAGE, TEXT, false, null),
+            new TableColumn(StreamItems.RES_ICON, TEXT, false, null),
+            new TableColumn(StreamItems.RES_LABEL, TEXT, false, null),
+            new TableColumn(StreamItems.TEXT, TEXT, false, null),
+            new TableColumn(StreamItems.TIMESTAMP, INTEGER, true, null),
+            new TableColumn(StreamItems.COMMENTS, TEXT, false, null),
+            new TableColumn(StreamItems.SYNC1, TEXT, false, null),
+            new TableColumn(StreamItems.SYNC2, TEXT, false, null),
+            new TableColumn(StreamItems.SYNC3, TEXT, false, null),
+            new TableColumn(StreamItems.SYNC4, TEXT, false, null),
+    };
+
+    private static final TableColumn[] STREAM_ITEM_PHOTOS_COLUMNS = new TableColumn[] {
+            new TableColumn(StreamItemPhotos._ID, INTEGER, false, null),
+            new TableColumn(StreamItemPhotos.STREAM_ITEM_ID, INTEGER, true, null),
+            new TableColumn(StreamItemPhotos.SORT_INDEX, INTEGER, false, null),
+            new TableColumn(StreamItemPhotos.PHOTO_FILE_ID, INTEGER, true, null),
+            new TableColumn(StreamItemPhotos.SYNC1, TEXT, false, null),
+            new TableColumn(StreamItemPhotos.SYNC2, TEXT, false, null),
+            new TableColumn(StreamItemPhotos.SYNC3, TEXT, false, null),
+            new TableColumn(StreamItemPhotos.SYNC4, TEXT, false, null),
+    };
+
+    private static final TableColumn[] PHOTO_FILES_COLUMNS = new TableColumn[] {
+            new TableColumn(PhotoFiles._ID, INTEGER, false, null),
+            new TableColumn(PhotoFiles.HEIGHT, INTEGER, true, null),
+            new TableColumn(PhotoFiles.WIDTH, INTEGER, true, null),
+            new TableColumn(PhotoFiles.FILESIZE, INTEGER, true, null),
+    };
+
+    private static final TableColumn[] PACKAGES_COLUMNS = new TableColumn[] {
+            new TableColumn(PackagesColumns._ID, INTEGER, false, null),
+            new TableColumn(PackagesColumns.PACKAGE, TEXT, true, null),
+    };
+
+    private static final TableColumn[] MIMETYPES_COLUMNS = new TableColumn[] {
+            new TableColumn(MimetypesColumns._ID, INTEGER, false, null),
+            new TableColumn(MimetypesColumns.MIMETYPE, TEXT, true, null),
+    };
+
+    private static final TableColumn[] DATA_COLUMNS = new TableColumn[] {
+            new TableColumn(Data._ID, INTEGER, false, null),
+            new TableColumn(DataColumns.PACKAGE_ID, INTEGER, false, null),
+            new TableColumn(DataColumns.MIMETYPE_ID, INTEGER, true, null),
+            new TableColumn(Data.RAW_CONTACT_ID, INTEGER, true, null),
+            new TableColumn(Data.HASH_ID, TEXT, false, null),
+            new TableColumn(Data.IS_READ_ONLY, INTEGER, true, "0"),
+            new TableColumn(Data.IS_PRIMARY, INTEGER, true, "0"),
+            new TableColumn(Data.IS_SUPER_PRIMARY, INTEGER, true, "0"),
+            new TableColumn(Data.DATA_VERSION, INTEGER, true, "0"),
+            new TableColumn(Data.DATA1, TEXT, false, null),
+            new TableColumn(Data.DATA2, TEXT, false, null),
+            new TableColumn(Data.DATA3, TEXT, false, null),
+            new TableColumn(Data.DATA4, TEXT, false, null),
+            new TableColumn(Data.DATA5, TEXT, false, null),
+            new TableColumn(Data.DATA6, TEXT, false, null),
+            new TableColumn(Data.DATA7, TEXT, false, null),
+            new TableColumn(Data.DATA8, TEXT, false, null),
+            new TableColumn(Data.DATA9, TEXT, false, null),
+            new TableColumn(Data.DATA10, TEXT, false, null),
+            new TableColumn(Data.DATA11, TEXT, false, null),
+            new TableColumn(Data.DATA12, TEXT, false, null),
+            new TableColumn(Data.DATA13, TEXT, false, null),
+            new TableColumn(Data.DATA14, TEXT, false, null),
+            new TableColumn(Data.DATA15, TEXT, false, null),
+            new TableColumn(Data.SYNC1, TEXT, false, null),
+            new TableColumn(Data.SYNC2, TEXT, false, null),
+            new TableColumn(Data.SYNC3, TEXT, false, null),
+            new TableColumn(Data.SYNC4, TEXT, false, null),
+            new TableColumn(Data.CARRIER_PRESENCE, INTEGER, true, "0"),
+    };
+
+    private static final TableColumn[] PHONE_LOOKUP_COLUMNS = new TableColumn[] {
+            new TableColumn(PhoneLookupColumns.DATA_ID, INTEGER, true, null),
+            new TableColumn(PhoneLookupColumns.RAW_CONTACT_ID, INTEGER, true, null),
+            new TableColumn(PhoneLookupColumns.NORMALIZED_NUMBER, TEXT, true, null),
+            new TableColumn(PhoneLookupColumns.MIN_MATCH, TEXT, true, null),
+    };
+
+    private static final TableColumn[] NAME_LOOKUP_COLUMNS = new TableColumn[] {
+            new TableColumn(NameLookupColumns.DATA_ID, INTEGER, true, null),
+            new TableColumn(NameLookupColumns.RAW_CONTACT_ID, INTEGER, true, null),
+            new TableColumn(NameLookupColumns.NORMALIZED_NAME, TEXT, true, null),
+            new TableColumn(NameLookupColumns.NAME_TYPE, INTEGER, true, null),
+    };
+
+    private static final TableColumn[] NICKNAME_LOOKUP_COLUMNS = new TableColumn[] {
+            new TableColumn(NicknameLookupColumns.NAME, TEXT, false, null),
+            new TableColumn(NicknameLookupColumns.CLUSTER, TEXT, false, null),
+    };
+
+    private static final TableColumn[] GROUPS_COLUMNS = new TableColumn[] {
+            new TableColumn(Groups._ID, INTEGER, false, null),
+            new TableColumn(GroupsColumns.PACKAGE_ID, INTEGER, false, null),
+            new TableColumn(GroupsColumns.ACCOUNT_ID, INTEGER, false, null),
+            new TableColumn(Groups.SOURCE_ID, TEXT, false, null),
+            new TableColumn(Groups.VERSION, INTEGER, true, "1"),
+            new TableColumn(Groups.DIRTY, INTEGER, true, "0"),
+            new TableColumn(Groups.TITLE, TEXT, false, null),
+            new TableColumn(Groups.TITLE_RES, INTEGER, false, null),
+            new TableColumn(Groups.NOTES, TEXT, false, null),
+            new TableColumn(Groups.SYSTEM_ID, TEXT, false, null),
+            new TableColumn(Groups.DELETED, INTEGER, true, "0"),
+            new TableColumn(Groups.GROUP_VISIBLE, INTEGER, true, "0"),
+            new TableColumn(Groups.SHOULD_SYNC, INTEGER, true, "1"),
+            new TableColumn(Groups.AUTO_ADD, INTEGER, true, "0"),
+            new TableColumn(Groups.FAVORITES, INTEGER, true, "0"),
+            new TableColumn(Groups.GROUP_IS_READ_ONLY, INTEGER, true, "0"),
+            new TableColumn(Groups.SYNC1, TEXT, false, null),
+            new TableColumn(Groups.SYNC2, TEXT, false, null),
+            new TableColumn(Groups.SYNC3, TEXT, false, null),
+            new TableColumn(Groups.SYNC4, TEXT, false, null),
+    };
+
+    private static final TableColumn[] AGGREGATION_EXCEPTIONS_COLUMNS = new TableColumn[] {
+            new TableColumn(AggregationExceptionColumns._ID, INTEGER, false, null),
+            new TableColumn(AggregationExceptions.TYPE, INTEGER, true, null),
+            new TableColumn(AggregationExceptions.RAW_CONTACT_ID1, INTEGER, false, null),
+            new TableColumn(AggregationExceptions.RAW_CONTACT_ID2, INTEGER, false, null),
+    };
+
+    private static final TableColumn[] SETTINGS_COLUMNS = new TableColumn[] {
+            new TableColumn(Settings.ACCOUNT_NAME, STRING, true, null),
+            new TableColumn(Settings.ACCOUNT_TYPE, STRING, true, null),
+            new TableColumn(Settings.DATA_SET, STRING, false, null),
+            new TableColumn(Settings.UNGROUPED_VISIBLE, INTEGER, true, "0"),
+            new TableColumn(Settings.SHOULD_SYNC, INTEGER, true, "1"),
+    };
+
+    private static final TableColumn[] VISIBLE_CONTACTS_COLUMNS = new TableColumn[] {
+            new TableColumn(Contacts._ID, INTEGER, false, null),
+    };
+
+    private static final TableColumn[] DEFAULT_DIRECTORY_COLUMNS = new TableColumn[] {
+            new TableColumn(Contacts._ID, INTEGER, false, null),
+    };
+
+    private static final TableColumn[] CALLS_COLUMNS = new TableColumn[] {
+            new TableColumn(Calls._ID, INTEGER, false, null),
+            new TableColumn(Calls.NUMBER, TEXT, false, null),
+            new TableColumn(Calls.NUMBER_PRESENTATION, INTEGER, true,
+                    String.valueOf(Calls.PRESENTATION_ALLOWED)),
+            new TableColumn(Calls.POST_DIAL_DIGITS, TEXT, true, "''"),
+            new TableColumn(Calls.DATE, INTEGER, false, null),
+            new TableColumn(Calls.DURATION, INTEGER, false, null),
+            new TableColumn(Calls.DATA_USAGE, INTEGER, false, null),
+            new TableColumn(Calls.TYPE, INTEGER, false, null),
+            new TableColumn(Calls.FEATURES, INTEGER, true, "0"),
+            new TableColumn(Calls.PHONE_ACCOUNT_COMPONENT_NAME, TEXT, false, null),
+            new TableColumn(Calls.PHONE_ACCOUNT_ID, TEXT, false, null),
+            new TableColumn(Calls.PHONE_ACCOUNT_ADDRESS, TEXT, false, null),
+            new TableColumn(Calls.PHONE_ACCOUNT_HIDDEN, INTEGER, true, "0"),
+            new TableColumn(Calls.SUB_ID, INTEGER, false, "-1"),
+            new TableColumn(Calls.NEW, INTEGER, false, null),
+            new TableColumn(Calls.CACHED_NAME, TEXT, false, null),
+            new TableColumn(Calls.CACHED_NUMBER_TYPE, INTEGER, false, null),
+            new TableColumn(Calls.CACHED_NUMBER_LABEL, TEXT, false, null),
+            new TableColumn(Calls.COUNTRY_ISO, TEXT, false, null),
+            new TableColumn(Calls.VOICEMAIL_URI, TEXT, false, null),
+            new TableColumn(Calls.IS_READ, INTEGER, false, null),
+            new TableColumn(Calls.GEOCODED_LOCATION, TEXT, false, null),
+            new TableColumn(Calls.CACHED_LOOKUP_URI, TEXT, false, null),
+            new TableColumn(Calls.CACHED_MATCHED_NUMBER, TEXT, false, null),
+            new TableColumn(Calls.CACHED_NORMALIZED_NUMBER, TEXT, false, null),
+            new TableColumn(Calls.CACHED_PHOTO_ID, INTEGER, true, "0"),
+            new TableColumn(Calls.CACHED_PHOTO_URI, TEXT, false, null),
+            new TableColumn(Calls.CACHED_FORMATTED_NUMBER, TEXT, false, null),
+            new TableColumn(Calls.ADD_FOR_ALL_USERS, INTEGER, true, "1"),
+            new TableColumn(Calls.LAST_MODIFIED, INTEGER, false, "0"),
+            new TableColumn(Voicemails._DATA, TEXT, false, null),
+            new TableColumn(Voicemails.HAS_CONTENT, INTEGER, false, null),
+            new TableColumn(Voicemails.MIME_TYPE, TEXT, false, null),
+            new TableColumn(Voicemails.SOURCE_DATA, TEXT, false, null),
+            new TableColumn(Voicemails.SOURCE_PACKAGE, TEXT, false, null),
+            new TableColumn(Voicemails.TRANSCRIPTION, TEXT, false, null),
+            new TableColumn(Voicemails.STATE, INTEGER, false, null),
+            new TableColumn(Voicemails.DIRTY, INTEGER, true, "0"),
+            new TableColumn(Voicemails.DELETED, INTEGER, true, "0"),
+    };
+
+    private static final TableColumn[] VOICEMAIL_STATUS_COLUMNS = new TableColumn[] {
+            new TableColumn(Status._ID, INTEGER, false, null),
+            new TableColumn(Status.SOURCE_PACKAGE, TEXT, true, null),
+            new TableColumn(Status.PHONE_ACCOUNT_COMPONENT_NAME, TEXT, false, null),
+            new TableColumn(Status.PHONE_ACCOUNT_ID, TEXT, false, null),
+            new TableColumn(Status.SETTINGS_URI, TEXT, false, null),
+            new TableColumn(Status.VOICEMAIL_ACCESS_URI, TEXT, false, null),
+            new TableColumn(Status.CONFIGURATION_STATE, INTEGER, false, null),
+            new TableColumn(Status.DATA_CHANNEL_STATE, INTEGER, false, null),
+            new TableColumn(Status.NOTIFICATION_CHANNEL_STATE, INTEGER, false, null),
+            new TableColumn(Status.QUOTA_OCCUPIED, INTEGER, false, "-1"),
+            new TableColumn(Status.QUOTA_TOTAL, INTEGER, false, "-1"),
+    };
+
+    private static final TableColumn[] STATUS_UPDATES_COLUMNS = new TableColumn[] {
+            new TableColumn(StatusUpdatesColumns.DATA_ID, INTEGER, false, null),
+            new TableColumn(StatusUpdates.STATUS, TEXT, false, null),
+            new TableColumn(StatusUpdates.STATUS_TIMESTAMP, INTEGER, false, null),
+            new TableColumn(StatusUpdates.STATUS_RES_PACKAGE, TEXT, false, null),
+            new TableColumn(StatusUpdates.STATUS_LABEL, INTEGER, false, null),
+            new TableColumn(StatusUpdates.STATUS_ICON, INTEGER, false, null),
+    };
+
+    private static final TableColumn[] DIRECTORIES_COLUMNS = new TableColumn[] {
+            new TableColumn(Directory._ID, INTEGER, false, null),
+            new TableColumn(Directory.PACKAGE_NAME, TEXT, true, null),
+            new TableColumn(Directory.DIRECTORY_AUTHORITY, TEXT, true, null),
+            new TableColumn(Directory.TYPE_RESOURCE_ID, INTEGER, false, null),
+            new TableColumn(DirectoryColumns.TYPE_RESOURCE_NAME, TEXT, false, null),
+            new TableColumn(Directory.ACCOUNT_TYPE, TEXT, false, null),
+            new TableColumn(Directory.ACCOUNT_NAME, TEXT, false, null),
+            new TableColumn(Directory.DISPLAY_NAME, TEXT, false, null),
+            new TableColumn(Directory.EXPORT_SUPPORT, INTEGER, true,
+                    String.valueOf(Directory.EXPORT_SUPPORT_NONE)),
+            new TableColumn(Directory.SHORTCUT_SUPPORT, INTEGER, true,
+                    String.valueOf(Directory.SHORTCUT_SUPPORT_NONE)),
+            new TableColumn(Directory.PHOTO_SUPPORT, INTEGER, true,
+                    String.valueOf(Directory.PHOTO_SUPPORT_NONE)),
+    };
+
+    private static final TableColumn[] DATA_USAGE_STAT_COLUMNS = new TableColumn[] {
+            new TableColumn(DataUsageStatColumns._ID, INTEGER, false, null),
+            new TableColumn(DataUsageStatColumns.DATA_ID, INTEGER, true, null),
+            new TableColumn(DataUsageStatColumns.USAGE_TYPE_INT, INTEGER, true, "0"),
+            new TableColumn(DataUsageStatColumns.TIMES_USED, INTEGER, true, "0"),
+            new TableColumn(DataUsageStatColumns.LAST_TIME_USED, INTEGER, true, "0"),
+    };
+
+    private static final TableColumn[] METADATA_SYNC_COLUMNS = new TableColumn[] {
+            new TableColumn(MetadataSync._ID, INTEGER, false, null),
+            new TableColumn(MetadataSync.RAW_CONTACT_BACKUP_ID, TEXT, true, null),
+            new TableColumn(MetadataSyncColumns.ACCOUNT_ID, INTEGER, true, null),
+            new TableColumn(MetadataSync.DATA, TEXT, false, null),
+            new TableColumn(MetadataSync.DELETED, INTEGER, true, "0"),
+    };
+
+    private static final TableColumn[] PRE_AUTHORIZED_URIS_COLUMNS = new TableColumn[] {
+            new TableColumn(PreAuthorizedUris._ID, INTEGER, false, null),
+            new TableColumn(PreAuthorizedUris.URI, STRING, true, null),
+            new TableColumn(PreAuthorizedUris.EXPIRATION, INTEGER, true, "0"),
+    };
+
+    private static final TableColumn[] METADATA_SYNC_STATE_COLUMNS = new TableColumn[] {
+            new TableColumn(MetadataSyncState._ID, INTEGER, false, null),
+            new TableColumn(MetadataSyncStateColumns.ACCOUNT_ID, INTEGER, true, null),
+            new TableColumn(MetadataSyncState.STATE, BLOB, false, null),
+    };
+
+    private static final TableListEntry[] TABLE_LIST = {
+            new TableListEntry(Tables.PROPERTIES, PROPERTIES_COLUMNS),
+            new TableListEntry(Tables.ACCOUNTS, ACCOUNTS_COLUMNS),
+            new TableListEntry(Tables.CONTACTS, CONTACTS_COLUMNS),
+            new TableListEntry(Tables.DELETED_CONTACTS, DELETED_CONTACTS_COLUMNS),
+            new TableListEntry(Tables.RAW_CONTACTS, RAW_CONTACTS_COLUMNS),
+            new TableListEntry(Tables.STREAM_ITEMS, STREAM_ITEMS_COLUMNS),
+            new TableListEntry(Tables.STREAM_ITEM_PHOTOS, STREAM_ITEM_PHOTOS_COLUMNS),
+            new TableListEntry(Tables.PHOTO_FILES, PHOTO_FILES_COLUMNS),
+            new TableListEntry(Tables.PACKAGES, PACKAGES_COLUMNS),
+            new TableListEntry(Tables.MIMETYPES, MIMETYPES_COLUMNS),
+            new TableListEntry(Tables.DATA, DATA_COLUMNS),
+            new TableListEntry(Tables.PHONE_LOOKUP, PHONE_LOOKUP_COLUMNS),
+            new TableListEntry(Tables.NAME_LOOKUP, NAME_LOOKUP_COLUMNS),
+            new TableListEntry(Tables.NICKNAME_LOOKUP, NICKNAME_LOOKUP_COLUMNS),
+            new TableListEntry(Tables.GROUPS, GROUPS_COLUMNS),
+            new TableListEntry(Tables.AGGREGATION_EXCEPTIONS, AGGREGATION_EXCEPTIONS_COLUMNS),
+            new TableListEntry(Tables.SETTINGS, SETTINGS_COLUMNS),
+            new TableListEntry(Tables.VISIBLE_CONTACTS, VISIBLE_CONTACTS_COLUMNS),
+            new TableListEntry(Tables.DEFAULT_DIRECTORY, DEFAULT_DIRECTORY_COLUMNS),
+            new TableListEntry(Tables.CALLS, CALLS_COLUMNS),
+            new TableListEntry(Tables.VOICEMAIL_STATUS, VOICEMAIL_STATUS_COLUMNS),
+            new TableListEntry(Tables.STATUS_UPDATES, STATUS_UPDATES_COLUMNS),
+            new TableListEntry(Tables.DIRECTORIES, DIRECTORIES_COLUMNS),
+            new TableListEntry(Tables.DATA_USAGE_STAT, DATA_USAGE_STAT_COLUMNS),
+            new TableListEntry(Tables.METADATA_SYNC, METADATA_SYNC_COLUMNS),
+            new TableListEntry(Tables.PRE_AUTHORIZED_URIS, PRE_AUTHORIZED_URIS_COLUMNS),
+            new TableListEntry(Tables.METADATA_SYNC_STATE, METADATA_SYNC_STATE_COLUMNS),
+    };
+
+}
+