android-contacts2-database-schema-nougat.sql

CREATE TABLE android_metadata (locale TEXT);
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);
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,has_phone_number INTEGER NOT NULL DEFAULT 0,lookup TEXT,status_update_id INTEGER REFERENCES data(_id), contact_last_updated_timestamp INTEGER, pinned INTEGER NOT NULL DEFAULT 2147483647);
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 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);
CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
CREATE TABLE data (_id INTEGER PRIMARY KEY AUTOINCREMENT,package_id INTEGER REFERENCES package(_id),mimetype_id INTEGER REFERENCES mimetype(_id) NOT NULL,raw_contact_id INTEGER REFERENCES raw_contacts(_id) NOT NULL,is_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 , hash_id TEXT, carrier_presence INTEGER NOT NULL DEFAULT 0);
CREATE INDEX data_raw_contact_id ON data (raw_contact_id);
CREATE INDEX data_mimetype_data1_index ON data (mimetype_id,data1);
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 INDEX name_lookup_raw_contact_id_index ON name_lookup (raw_contact_id);
CREATE TABLE nickname_lookup (name TEXT,cluster TEXT);
CREATE UNIQUE INDEX nickname_lookup_index ON nickname_lookup (name, cluster);
CREATE TABLE groups (_id INTEGER PRIMARY KEY AUTOINCREMENT,package_id INTEGER REFERENCES package(_id),account_name STRING DEFAULT NULL, account_type STRING DEFAULT NULL, data_set STRING DEFAULT NULL, 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 , account_id INTEGER REFERENCES accounts(_id));
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 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 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,date INTEGER,duration INTEGER,type INTEGER,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,formatted_number TEXT,_data TEXT,has_content INTEGER,mime_type TEXT,source_data TEXT,source_package TEXT,state INTEGER, presentation INTEGER NOT NULL DEFAULT 1, subscription INTEGER NOT NULL DEFAULT 0, duration_type INTEGER NOT NULL DEFAULT 0, subscription_component_name TEXT, subscription_id TEXT, features INTEGER NOT NULL DEFAULT 0, data_usage INTEGER, transcription TEXT, sub_id INTEGER DEFAULT -1, phone_account_address TEXT, phone_account_hidden INTEGER NOT NULL DEFAULT 0, photo_uri TEXT, dirty INTEGER NOT NULL DEFAULT 0, deleted INTEGER NOT NULL DEFAULT 0, post_dial_digits TEXT NOT NULL DEFAULT '', add_for_all_users INTEGER NOT NULL DEFAULT 1, last_modified INTEGER DEFAULT 0);
CREATE TABLE voicemail_status (_id INTEGER PRIMARY KEY AUTOINCREMENT,source_package TEXT UNIQUE NOT NULL,settings_uri TEXT,voicemail_access_uri TEXT,configuration_state INTEGER,data_channel_state INTEGER,notification_channel_state INTEGER, phone_account_component_name TEXT, phone_account_id TEXT, quota_occupied INTEGER DEFAULT -1, quota_total INTEGER DEFAULT -1);
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 properties (property_key TEXT PRIMARY KEY, property_value TEXT );
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);
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 INTERGER NOT NULL DEFAULT 0, FOREIGN KEY(data_id) REFERENCES data(_id));
CREATE UNIQUE INDEX data_usage_stat_index ON data_usage_stat (data_id, usage_type);
CREATE TABLE v1_settings (_id INTEGER PRIMARY KEY,_sync_account TEXT,_sync_account_type TEXT,key STRING NOT NULL,value STRING );
CREATE TABLE accounts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_name TEXT, account_type TEXT, data_set TEXT);
CREATE INDEX groups_source_id_account_id_index ON groups (sourceid, account_id);
CREATE INDEX contacts_contact_last_updated_timestamp_index ON contacts(contact_last_updated_timestamp);
CREATE TABLE deleted_contacts (contact_id INTEGER PRIMARY KEY,contact_deleted_timestamp INTEGER NOT NULL default 0);
CREATE INDEX deleted_contacts_contact_deleted_timestamp_index ON deleted_contacts(contact_deleted_timestamp);
CREATE TABLE raw_contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_id INTEGER REFERENCES accounts(_id),sourceid 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,contact_id INTEGER REFERENCES contacts(_id),aggregation_mode INTEGER NOT NULL DEFAULT 0,aggregation_needed INTEGER NOT NULL DEFAULT 1,custom_ringtone TEXT,send_to_voicemail INTEGER NOT NULL DEFAULT 0,times_contacted INTEGER NOT NULL DEFAULT 0,last_time_contacted INTEGER,starred INTEGER NOT NULL DEFAULT 0,display_name TEXT,display_name_alt TEXT,display_name_source INTEGER NOT NULL DEFAULT 0,phonetic_name TEXT,phonetic_name_style TEXT,sort_key TEXT COLLATE PHONEBOOK,sort_key_alt TEXT COLLATE PHONEBOOK,name_verified INTEGER NOT NULL DEFAULT 0,sync1 TEXT, sync2 TEXT, sync3 TEXT, sync4 TEXT , phonebook_label TEXT, phonebook_bucket INTEGER, phonebook_label_alt TEXT, phonebook_bucket_alt INTEGER, pinned INTEGER NOT NULL DEFAULT  2147483647, backup_id TEXT, metadata_dirty 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 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 UNIQUE INDEX raw_contacts_backup_id_account_id_index ON raw_contacts (backup_id, account_id);
CREATE INDEX data_hash_id_index ON data (hash_id);
CREATE TABLE pre_authorized_uris (_id INTEGER PRIMARY KEY AUTOINCREMENT, uri STRING NOT NULL, expiration INTEGER NOT NULL DEFAULT 0);
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 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 UNIQUE INDEX metadata_sync_index ON metadata_sync (raw_contact_backup_id, account_id);
CREATE TABLE metadata_sync_state (_id INTEGER PRIMARY KEY AUTOINCREMENT, account_id INTEGER NOT NULL, state BLOB);
CREATE UNIQUE INDEX metadata_sync_state_index ON metadata_sync_state (account_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, account_name, account_type, (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) JOIN accounts AS name_accounts ON(name_raw_contact.account_id=name_accounts._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_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_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_type='com.android.localphone'); 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 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);
CREATE VIRTUAL TABLE search_index USING FTS4 (contact_id INTEGER REFERENCES contacts(_id) NOT NULL,content TEXT, name TEXT, tokens TEXT);
CREATE TABLE IF NOT EXISTS 'search_index_content'(docid INTEGER PRIMARY KEY, 'c0contact_id', 'c1content', 'c2name', 'c3tokens');
CREATE TABLE IF NOT EXISTS 'search_index_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS '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 IF NOT EXISTS 'search_index_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE IF NOT EXISTS 'search_index_stat'(id INTEGER PRIMARY KEY, value BLOB);

android-contacts2-database-schema-oreo.sql

CREATE TABLE android_metadata (locale TEXT);
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);
CREATE TABLE properties (property_key TEXT PRIMARY KEY, property_value TEXT );
CREATE TABLE accounts (_id INTEGER PRIMARY KEY AUTOINCREMENT,account_name TEXT, account_type TEXT, data_set TEXT);
CREATE TABLE sqlite_sequence(name,seq);
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,x_times_contacted INTEGER NOT NULL DEFAULT 0,x_last_time_contacted INTEGER,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 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 TABLE deleted_contacts (contact_id INTEGER PRIMARY KEY,contact_deleted_timestamp INTEGER NOT NULL default 0);
CREATE INDEX deleted_contacts_contact_deleted_timestamp_index ON deleted_contacts(contact_deleted_timestamp);
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,x_times_contacted INTEGER NOT NULL DEFAULT 0,x_last_time_contacted INTEGER,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 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 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);
CREATE UNIQUE INDEX mime_type ON mimetypes (mimetype);
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, preferred_phone_account_component_name TEXT, preferred_phone_account_id TEXT );
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 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 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 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 INDEX name_lookup_raw_contact_id_index ON name_lookup (raw_contact_id);
CREATE TABLE nickname_lookup (name TEXT,cluster TEXT);
CREATE UNIQUE INDEX nickname_lookup_index ON nickname_lookup (name, cluster);
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 );
CREATE INDEX groups_source_id_account_id_index ON groups (sourceid, account_id);
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 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 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 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);
CREATE TABLE data_usage_stat(stat_id INTEGER PRIMARY KEY AUTOINCREMENT, data_id INTEGER NOT NULL, usage_type INTEGER NOT NULL DEFAULT 0, x_times_used INTEGER NOT NULL DEFAULT 0, x_last_time_used 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 UNIQUE INDEX data_usage_stat_index ON data_usage_stat (data_id, usage_type);
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 UNIQUE INDEX metadata_sync_index ON metadata_sync (raw_contact_backup_id, account_id);
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 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, preferred_phone_account_component_name, preferred_phone_account_id, data_sync1, data_sync2, data_sync3, data_sync4, contacts.custom_ringtone AS custom_ringtone,contacts.send_to_voicemail AS send_to_voicemail,contacts.x_last_time_contacted AS x_last_time_contacted,contacts.x_times_contacted AS x_times_contacted,cast((cast((contacts.x_last_time_contacted) as int) / (86400)) * (86400) as int) AS last_time_contacted,cast(ifnull((case when (contacts.x_times_contacted) <= 0 then 0 when (contacts.x_times_contacted) < (10) then (contacts.x_times_contacted) else (cast((contacts.x_times_contacted) as int) / (10)) * (10) end), 0) as int) 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)
/* view_data(_id,hash_id,raw_contact_id,contact_id,account_id,account_name,account_type,data_set,account_type_and_data_set,sourceid,backup_id,version,dirty,sync1,sync2,sync3,sync4,is_primary,is_super_primary,data_version,package_id,res_package,mimetype_id,mimetype,is_read_only,data1,data2,data3,data4,data5,data6,data7,data8,data9,data10,data11,data12,data13,data14,data15,carrier_presence,preferred_phone_account_component_name,preferred_phone_account_id,data_sync1,data_sync2,data_sync3,data_sync4,custom_ringtone,send_to_voicemail,x_last_time_contacted,x_times_contacted,last_time_contacted,times_contacted,starred,pinned,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,has_phone_number,name_raw_contact_id,lookup,photo_id,photo_file_id,in_visible_group,in_default_directory,status_update_id,contact_last_updated_timestamp,photo_uri,photo_thumb_uri,raw_contact_is_user_profile,group_sourceid) */;
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,x_last_time_contacted,cast((cast((x_last_time_contacted) as int) / (86400)) * (86400) as int) AS last_time_contacted,x_times_contacted,cast(ifnull((case when (x_times_contacted) <= 0 then 0 when (x_times_contacted) < (10) then (x_times_contacted) else (cast((x_times_contacted) as int) / (10)) * (10) end), 0) as int) AS 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)
/* view_raw_contacts(_id,contact_id,aggregation_mode,raw_contact_is_read_only,deleted,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,raw_contact_is_user_profile,custom_ringtone,send_to_voicemail,x_last_time_contacted,last_time_contacted,x_times_contacted,times_contacted,starred,pinned,account_id,account_name,account_type,data_set,account_type_and_data_set,sourceid,backup_id,version,dirty,sync1,sync2,sync3,sync4) */;
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.x_last_time_contacted AS x_last_time_contacted, cast((cast((contacts.x_last_time_contacted) as int) / (86400)) * (86400) as int) AS last_time_contacted, contacts.send_to_voicemail AS send_to_voicemail, contacts.starred AS starred, contacts.pinned AS pinned, contacts.x_times_contacted AS x_times_contacted, cast(ifnull((case when (contacts.x_times_contacted) <= 0 then 0 when (contacts.x_times_contacted) < (10) then (contacts.x_times_contacted) else (cast((contacts.x_times_contacted) as int) / (10)) * (10) end), 0) as int) 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)
/* view_contacts(_id,custom_ringtone,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,has_phone_number,name_raw_contact_id,lookup,photo_id,photo_file_id,in_visible_group,in_default_directory,status_update_id,contact_last_updated_timestamp,x_last_time_contacted,last_time_contacted,send_to_voicemail,starred,pinned,x_times_contacted,times_contacted,photo_uri,photo_thumb_uri,is_user_profile) */;
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, preferred_phone_account_component_name, preferred_phone_account_id, 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)
/* view_raw_entities(contact_id,deleted,metadata_dirty,is_primary,is_super_primary,data_version,package_id,res_package,mimetype_id,mimetype,is_read_only,data1,data2,data3,data4,data5,data6,data7,data8,data9,data10,data11,data12,data13,data14,data15,carrier_presence,preferred_phone_account_component_name,preferred_phone_account_id,data_sync1,data_sync2,data_sync3,data_sync4,account_id,account_name,account_type,data_set,account_type_and_data_set,sourceid,backup_id,version,dirty,sync1,sync2,sync3,sync4,"data_sync1:1","data_sync2:1","data_sync3:1","data_sync4:1",_id,data_id,starred,raw_contact_is_user_profile,group_sourceid) */;
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, preferred_phone_account_component_name, preferred_phone_account_id, 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.x_last_time_contacted AS x_last_time_contacted, cast((cast((contacts.x_last_time_contacted) as int) / (86400)) * (86400) as int) AS last_time_contacted, contacts.send_to_voicemail AS send_to_voicemail, contacts.starred AS starred, contacts.pinned AS pinned, contacts.x_times_contacted AS x_times_contacted, cast(ifnull((case when (contacts.x_times_contacted) <= 0 then 0 when (contacts.x_times_contacted) < (10) then (contacts.x_times_contacted) else (cast((contacts.x_times_contacted) as int) / (10)) * (10) end), 0) as int) 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)
/* view_entities(_id,contact_id,deleted,metadata_dirty,is_primary,is_super_primary,data_version,package_id,res_package,mimetype_id,mimetype,is_read_only,data1,data2,data3,data4,data5,data6,data7,data8,data9,data10,data11,data12,data13,data14,data15,carrier_presence,preferred_phone_account_component_name,preferred_phone_account_id,data_sync1,data_sync2,data_sync3,data_sync4,account_id,account_name,account_type,data_set,account_type_and_data_set,sourceid,backup_id,version,dirty,sync1,sync2,sync3,sync4,custom_ringtone,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,has_phone_number,name_raw_contact_id,lookup,photo_id,photo_file_id,in_visible_group,in_default_directory,status_update_id,contact_last_updated_timestamp,x_last_time_contacted,last_time_contacted,send_to_voicemail,starred,pinned,x_times_contacted,times_contacted,photo_uri,photo_thumb_uri,is_user_profile,"data_sync1:1","data_sync2:1","data_sync3:1","data_sync4:1",raw_contact_id,data_id,group_sourceid) */;
CREATE VIEW view_data_usage AS SELECT stat_id, data_id, usage_type, x_times_used, x_last_time_used,cast(ifnull((case when (x_times_used) <= 0 then 0 when (x_times_used) < (10) then (x_times_used) else (cast((x_times_used) as int) / (10)) * (10) end), 0) as int) AS times_used,cast((cast((x_last_time_used) as int) / (86400)) * (86400) as int) AS last_time_used FROM data_usage_stat
/* view_data_usage(stat_id,data_id,usage_type,x_times_used,x_last_time_used,times_used,last_time_used) */;
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, x_times_used, x_last_time_used, times_used, last_time_used FROM view_data_usage AS 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)
/* view_data_usage_stat(stat_id,data_id,contact_id,mimetype,usage_type,x_times_used,x_last_time_used,times_used,last_time_used) */;
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)
/* view_stream_items(_id,contact_id,contact_lookup,account_name,account_type,data_set,raw_contact_id,raw_contact_source_id,res_package,icon,label,text,timestamp,comments,stream_item_sync1,stream_item_sync2,stream_item_sync3,stream_item_sync4) */;
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)
/* view_metadata_sync(_id,raw_contact_backup_id,account_name,account_type,data_set,data,deleted) */;
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)
/* view_metadata_sync_state(_id,account_name,account_type,data_set,state) */;
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)
/* view_groups(_id,account_id,account_name,account_type,data_set,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,res_package) */;
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 TABLE presence (presence_data_id INTEGER PRIMARY KEY REFERENCES data(_id),protocol INTEGER NOT NULL,custom_protocol TEXT,im_handle TEXT,im_account TEXT,presence_contact_id INTEGER REFERENCES contacts(_id),presence_raw_contact_id INTEGER REFERENCES raw_contacts(_id),mode INTEGER,chat_capability INTEGER NOT NULL DEFAULT 0,UNIQUE(protocol, custom_protocol, im_handle, im_account));
CREATE INDEX presenceIndex ON presence (presence_raw_contact_id);
CREATE INDEX presenceIndex2 ON presence (presence_contact_id);
CREATE TABLE agg_presence (presence_contact_id INTEGER PRIMARY KEY REFERENCES contacts(_id),mode INTEGER,chat_capability INTEGER NOT NULL DEFAULT 0);
CREATE TRIGGER presence_deleted BEFORE DELETE ON presence BEGIN    DELETE FROM agg_presence     WHERE presence_contact_id = (SELECT presence_contact_id FROM presence WHERE presence_raw_contact_id=OLD.presence_raw_contact_id AND NOT EXISTS(SELECT presence_raw_contact_id FROM presence WHERE presence_contact_id=OLD.presence_contact_id AND presence_raw_contact_id!=OLD.presence_raw_contact_id)); END;
CREATE TRIGGER presence_inserted AFTER INSERT ON presence BEGIN INSERT OR REPLACE INTO agg_presence(presence_contact_id, mode, chat_capability) SELECT presence_contact_id,mode,chat_capability FROM presence WHERE  (ifnull(mode,0)  * 10 + ifnull(chat_capability, 0)) = (SELECT MAX (ifnull(mode,0)  * 10 + ifnull(chat_capability, 0)) FROM presence WHERE presence_contact_id=NEW.presence_contact_id) AND presence_contact_id=NEW.presence_contact_id; END;
CREATE TRIGGER presence_updated AFTER UPDATE ON presence BEGIN INSERT OR REPLACE INTO agg_presence(presence_contact_id, mode, chat_capability) SELECT presence_contact_id,mode,chat_capability FROM presence WHERE  (ifnull(mode,0)  * 10 + ifnull(chat_capability, 0)) = (SELECT MAX (ifnull(mode,0)  * 10 + ifnull(chat_capability, 0)) FROM presence WHERE presence_contact_id=NEW.presence_contact_id) AND presence_contact_id=NEW.presence_contact_id; 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, cast(0 as int) AS times_contacted, cast(0 as int) 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
/* view_v1_people(_id,name,display_name,phonetic_name,notes,account_name,account_type,times_contacted,last_time_contacted,custom_ringtone,send_to_voicemail,starred,primary_organization,primary_email,primary_phone,number,type,label,number_key) */;
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
/* view_v1_organizations(_id,person,isprimary,account_name,account_type,company,type,label,title) */;
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, cast(0 as int) AS times_contacted, cast(0 as int) 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
/* view_v1_contact_methods(_id,person,kind,isprimary,type,data,label,aux_data,name,display_name,phonetic_name,notes,account_name,account_type,times_contacted,last_time_contacted,custom_ringtone,send_to_voicemail,starred,primary_organization,primary_email,primary_phone,number,"type:1","label:1",number_key) */;
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, cast(0 as int) AS times_contacted, cast(0 as int) 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
/* view_v1_phones(_id,person,isprimary,number,type,label,number_key,name,display_name,phonetic_name,notes,account_name,account_type,times_contacted,last_time_contacted,custom_ringtone,send_to_voicemail,starred,primary_organization,primary_email,primary_phone,"number:1","type:1","label:1","number_key:1") */;
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
/* view_v1_extensions(_id,person,account_name,account_type,name,value) */;
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)
/* view_v1_groups(_id,account_name,account_type,name,notes,system_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
/* view_v1_group_membership(_id,person,account_name,account_type,group_id,name,notes,system_id,group_sync_id,group_sync_account,group_sync_account_type) */;
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
/* view_v1_photos(_id,person,account_name,account_type,data,exists_on_server,download_required,local_version,sync_error) */;
CREATE TABLE v1_settings (_id INTEGER PRIMARY KEY,_sync_account TEXT,_sync_account_type TEXT,key STRING NOT NULL,value STRING );
CREATE TABLE sqlite_stat1(tbl,idx,stat);
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);
CREATE VIRTUAL TABLE search_index USING FTS4 (contact_id INTEGER REFERENCES contacts(_id) NOT NULL,content TEXT, name TEXT, tokens TEXT)
/* search_index(contact_id,content,name,tokens) */;
CREATE TABLE IF NOT EXISTS 'search_index_content'(docid INTEGER PRIMARY KEY, 'c0contact_id', 'c1content', 'c2name', 'c3tokens');
CREATE TABLE IF NOT EXISTS 'search_index_segments'(blockid INTEGER PRIMARY KEY, block BLOB);
CREATE TABLE IF NOT EXISTS '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 IF NOT EXISTS 'search_index_docsize'(docid INTEGER PRIMARY KEY, size BLOB);
CREATE TABLE IF NOT EXISTS 'search_index_stat'(id INTEGER PRIMARY KEY, value BLOB);