1PRAGMA foreign_keys=OFF; 2BEGIN TRANSACTION; 3CREATE TABLE _sync_state (_id INTEGER PRIMARY KEY,account_name TEXT NOT NULL,account_type TEXT NOT NULL,data TEXT,UNIQUE(account_name, account_type)); 4CREATE TABLE _sync_state_metadata (version INTEGER); 5INSERT INTO "_sync_state_metadata" VALUES(1); 6CREATE TABLE properties (property_key TEXT PRIMARY KEY, property_value TEXT ); 7INSERT INTO "properties" VALUES('database_time_created','20243770769'); 8INSERT INTO "properties" VALUES('icu_version','56.1'); 9INSERT INTO "properties" VALUES('locale','en-US'); 10INSERT INTO "properties" VALUES('aggregation_v2','5'); 11INSERT INTO "properties" VALUES('search_index','1'); 12INSERT INTO "properties" VALUES('known_accounts','twyen.dev@gmail.comcom.google'); 13INSERT INTO "properties" VALUES('directoryScanComplete','1'); 14CREATE TABLE accounts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_name TEXT, account_type TEXT, data_set TEXT); 15CREATE 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); 16CREATE TABLE deleted_contacts (contact_id INTEGER PRIMARY KEY,contact_deleted_timestamp INTEGER NOT NULL default 0); 17CREATE 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 ); 18CREATE 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)); 19CREATE 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)); 20CREATE TABLE photo_files (_id INTEGER PRIMARY KEY AUTOINCREMENT, height INTEGER NOT NULL, width INTEGER NOT NULL, filesize INTEGER NOT NULL); 21CREATE TABLE packages (_id INTEGER PRIMARY KEY AUTOINCREMENT,package TEXT NOT NULL); 22CREATE TABLE mimetypes (_id INTEGER PRIMARY KEY AUTOINCREMENT,mimetype TEXT NOT NULL); 23INSERT INTO "mimetypes" VALUES(1,'vnd.android.cursor.item/email_v2'); 24INSERT INTO "mimetypes" VALUES(2,'vnd.android.cursor.item/im'); 25INSERT INTO "mimetypes" VALUES(3,'vnd.android.cursor.item/nickname'); 26INSERT INTO "mimetypes" VALUES(4,'vnd.android.cursor.item/organization'); 27INSERT INTO "mimetypes" VALUES(5,'vnd.android.cursor.item/phone_v2'); 28INSERT INTO "mimetypes" VALUES(6,'vnd.android.cursor.item/sip_address'); 29INSERT INTO "mimetypes" VALUES(7,'vnd.android.cursor.item/name'); 30INSERT INTO "mimetypes" VALUES(8,'vnd.android.cursor.item/postal-address_v2'); 31INSERT INTO "mimetypes" VALUES(9,'vnd.android.cursor.item/identity'); 32INSERT INTO "mimetypes" VALUES(10,'vnd.android.cursor.item/photo'); 33INSERT INTO "mimetypes" VALUES(11,'vnd.android.cursor.item/group_membership'); 34CREATE 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 ); 35CREATE 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); 36CREATE 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)); 37CREATE TABLE nickname_lookup (name TEXT,cluster TEXT); 38CREATE 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 ); 39INSERT INTO "groups" VALUES(1,NULL,1,NULL,1,0,'My Contacts',NULL,NULL,'Contacts',0,1,1,1,0,1,NULL,NULL,NULL,NULL); 40INSERT 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); 41CREATE 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)); 42CREATE 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); 43CREATE TABLE visible_contacts (_id INTEGER PRIMARY KEY); 44CREATE TABLE default_directory (_id INTEGER PRIMARY KEY); 45CREATE 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); 46CREATE 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); 47INSERT INTO "voicemail_status" VALUES(1,'com.android.phone','com.android.phone/com.android.services.telephony.TelephonyConnectionService','8901260421762832248',NULL,NULL,0,0,0); 48CREATE 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); 49CREATE 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); 50INSERT 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); 51INSERT 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); 52CREATE 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)); 53CREATE 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); 54CREATE TABLE pre_authorized_uris (_id INTEGER PRIMARY KEY AUTOINCREMENT, uri STRING NOT NULL, expiration INTEGER NOT NULL DEFAULT 0); 55CREATE TABLE metadata_sync_state (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_id INTEGER NOT NULL,state BLOB); 56CREATE TABLE v1_settings (_id INTEGER PRIMARY KEY,_sync_account TEXT,_sync_account_type TEXT,key STRING NOT NULL,value STRING ); 57ANALYZE sqlite_master; 58INSERT INTO "sqlite_stat1" VALUES('contacts','contacts_has_phone_index','9000 500'); 59INSERT INTO "sqlite_stat1" VALUES('contacts','contacts_name_raw_contact_id_index','9000 1'); 60INSERT INTO "sqlite_stat1" VALUES('contacts','contacts_contact_last_updated_timestamp_index','9000 10'); 61INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contacts_contact_id_index','10000 2'); 62INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contact_sort_key2_index','10000 2'); 63INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contact_sort_key1_index','10000 2'); 64INSERT INTO "sqlite_stat1" VALUES('raw_contacts','raw_contacts_source_id_account_id_index','10000 1 1'); 65INSERT INTO "sqlite_stat1" VALUES('name_lookup','name_lookup_raw_contact_id_index','35000 4'); 66INSERT INTO "sqlite_stat1" VALUES('name_lookup','name_lookup_index','35000 2 2 2 1'); 67INSERT INTO "sqlite_stat1" VALUES('name_lookup','sqlite_autoindex_name_lookup_1','35000 3 2 1'); 68INSERT INTO "sqlite_stat1" VALUES('phone_lookup','phone_lookup_index','3500 3 2 1'); 69INSERT INTO "sqlite_stat1" VALUES('phone_lookup','phone_lookup_min_match_index','3500 3 2 2'); 70INSERT INTO "sqlite_stat1" VALUES('phone_lookup','phone_lookup_data_id_min_match_index','3500 2 2'); 71INSERT INTO "sqlite_stat1" VALUES('data','data_mimetype_data1_index','60000 5000 2'); 72INSERT INTO "sqlite_stat1" VALUES('data','data_raw_contact_id','60000 10'); 73INSERT INTO "sqlite_stat1" VALUES('groups','groups_source_id_account_id_index','50 2 2 1 1'); 74INSERT INTO "sqlite_stat1" VALUES('nickname_lookup','nickname_lookup_index','500 2 1'); 75INSERT INTO "sqlite_stat1" VALUES('calls',NULL,'250'); 76INSERT INTO "sqlite_stat1" VALUES('status_updates',NULL,'100'); 77INSERT INTO "sqlite_stat1" VALUES('stream_items',NULL,'500'); 78INSERT INTO "sqlite_stat1" VALUES('stream_item_photos',NULL,'50'); 79INSERT INTO "sqlite_stat1" VALUES('voicemail_status',NULL,'5'); 80INSERT INTO "sqlite_stat1" VALUES('accounts',NULL,'3'); 81INSERT INTO "sqlite_stat1" VALUES('pre_authorized_uris',NULL,'1'); 82INSERT INTO "sqlite_stat1" VALUES('visible_contacts',NULL,'2000'); 83INSERT INTO "sqlite_stat1" VALUES('photo_files',NULL,'50'); 84INSERT INTO "sqlite_stat1" VALUES('default_directory',NULL,'1500'); 85INSERT INTO "sqlite_stat1" VALUES('mimetypes','mime_type','18 1'); 86INSERT INTO "sqlite_stat1" VALUES('data_usage_stat','data_usage_stat_index','20 2 1'); 87INSERT INTO "sqlite_stat1" VALUES('metadata_sync','metadata_sync_index','10000 1 1'); 88INSERT INTO "sqlite_stat1" VALUES('agg_exceptions',NULL,'10'); 89INSERT INTO "sqlite_stat1" VALUES('settings',NULL,'10'); 90INSERT INTO "sqlite_stat1" VALUES('packages',NULL,'0'); 91INSERT INTO "sqlite_stat1" VALUES('directories',NULL,'3'); 92INSERT INTO "sqlite_stat1" VALUES('v1_settings',NULL,'0'); 93INSERT INTO "sqlite_stat1" VALUES('android_metadata',NULL,'1'); 94INSERT INTO "sqlite_stat1" VALUES('_sync_state','sqlite_autoindex__sync_state_1','2 1 1'); 95INSERT INTO "sqlite_stat1" VALUES('_sync_state_metadata',NULL,'1'); 96INSERT INTO "sqlite_stat1" VALUES('properties','sqlite_autoindex_properties_1','4 1'); 97INSERT INTO "sqlite_stat1" VALUES('metadata_sync_state','metadata_sync_state_index','2 1 1'); 98INSERT INTO "sqlite_stat1" VALUES('search_index_docsize',NULL,'9000'); 99INSERT INTO "sqlite_stat1" VALUES('search_index_content',NULL,'9000'); 100INSERT INTO "sqlite_stat1" VALUES('search_index_stat',NULL,'1'); 101INSERT INTO "sqlite_stat1" VALUES('search_index_segments',NULL,'450'); 102INSERT INTO "sqlite_stat1" VALUES('search_index_segdir','sqlite_autoindex_search_index_segdir_1','9 5 1'); 103PRAGMA writable_schema=ON; 104INSERT 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)'); 105CREATE TABLE 'search_index_content'(docid INTEGER PRIMARY KEY, 'c0contact_id', 'c1content', 'c2name', 'c3tokens'); 106CREATE TABLE 'search_index_segments'(blockid INTEGER PRIMARY KEY, block BLOB); 107CREATE TABLE 'search_index_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx)); 108CREATE TABLE 'search_index_docsize'(docid INTEGER PRIMARY KEY, size BLOB); 109CREATE TABLE 'search_index_stat'(id INTEGER PRIMARY KEY, value BLOB); 110DELETE FROM sqlite_sequence; 111INSERT INTO "sqlite_sequence" VALUES('mimetypes',11); 112INSERT INTO "sqlite_sequence" VALUES('directories',1); 113INSERT INTO "sqlite_sequence" VALUES('voicemail_status',1); 114INSERT INTO "sqlite_sequence" VALUES('calls',496); 115INSERT INTO "sqlite_sequence" VALUES('accounts',1); 116INSERT INTO "sqlite_sequence" VALUES('groups',2); 117CREATE INDEX contacts_has_phone_index ON contacts (has_phone_number); 118CREATE INDEX contacts_name_raw_contact_id_index ON contacts (name_raw_contact_id); 119CREATE INDEX contacts_contact_last_updated_timestamp_index ON contacts(contact_last_updated_timestamp); 120CREATE INDEX deleted_contacts_contact_deleted_timestamp_index ON deleted_contacts(contact_deleted_timestamp); 121CREATE INDEX raw_contacts_contact_id_index ON raw_contacts (contact_id); 122CREATE INDEX raw_contacts_source_id_account_id_index ON raw_contacts (sourceid, account_id); 123CREATE UNIQUE INDEX raw_contacts_backup_id_account_id_index ON raw_contacts (backup_id, account_id); 124CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype); 125CREATE INDEX data_raw_contact_id ON data (raw_contact_id); 126CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1); 127CREATE INDEX data_hash_id_index ON data (hash_id); 128CREATE INDEX phone_lookup_index ON phone_lookup (normalized_number,raw_contact_id,data_id); 129CREATE INDEX phone_lookup_min_match_index ON phone_lookup (min_match,raw_contact_id,data_id); 130CREATE INDEX phone_lookup_data_id_min_match_index ON phone_lookup (data_id, min_match); 131CREATE INDEX name_lookup_raw_contact_id_index ON name_lookup (raw_contact_id); 132CREATE UNIQUE INDEX nickname_lookup_index ON nickname_lookup (name, cluster); 133CREATE INDEX groups_source_id_account_id_index ON groups (sourceid, account_id); 134CREATE UNIQUE INDEX aggregation_exception_index1 ON agg_exceptions (raw_contact_id1, raw_contact_id2); 135CREATE UNIQUE INDEX aggregation_exception_index2 ON agg_exceptions (raw_contact_id2, raw_contact_id1); 136CREATE UNIQUE INDEX data_usage_stat_index ON data_usage_stat (data_id, usage_type); 137CREATE UNIQUE INDEX metadata_sync_index ON metadata_sync (raw_contact_backup_id, account_id); 138CREATE UNIQUE INDEX metadata_sync_state_index ON metadata_sync_state (account_id); 139CREATE 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); 140CREATE 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); 141CREATE 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); 142CREATE 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); 143CREATE 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); 144CREATE 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); 145CREATE 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); 146CREATE 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); 147CREATE 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); 148CREATE 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); 149CREATE 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; 150CREATE 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; 151CREATE 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; 152CREATE 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; 153CREATE TRIGGER groups_updated1 AFTER UPDATE ON groups BEGIN UPDATE groups SET version=OLD.version+1 WHERE _id=OLD._id; END; 154CREATE 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; 155CREATE 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; 156CREATE 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; 157CREATE 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; 158CREATE 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; 159CREATE 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; 160CREATE 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); 161CREATE 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; 162CREATE 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; 163CREATE INDEX name_lookup_index ON name_lookup (normalized_name,name_type, raw_contact_id, data_id); 164CREATE INDEX raw_contact_sort_key1_index ON raw_contacts (sort_key); 165CREATE INDEX raw_contact_sort_key2_index ON raw_contacts (sort_key_alt); 166PRAGMA writable_schema=OFF; 167COMMIT; 168