1BEGIN TRANSACTION; 2PRAGMA user_version = 82; 3CREATE TABLE _deleted_groups (_sync_version TEXT,_sync_id TEXT,_sync_account TEXT,_sync_mark INTEGER); 4CREATE TABLE _deleted_people (_sync_version TEXT,_sync_id TEXT,_sync_account TEXT,_sync_mark INTEGER); 5CREATE TABLE _sync_state (_id INTEGER PRIMARY KEY,_sync_account TEXT,data TEXT,UNIQUE(_sync_account)); 6CREATE TABLE _sync_state_metadata (version INTEGER); 7INSERT INTO _sync_state_metadata VALUES(2); 8CREATE TABLE android_metadata (locale TEXT); 9INSERT INTO android_metadata VALUES('en_US'); 10CREATE TABLE calls (_id INTEGER PRIMARY KEY AUTOINCREMENT,number TEXT,date INTEGER,duration INTEGER,type INTEGER,new INTEGER,name TEXT,numbertype INTEGER,numberlabel TEXT); 11CREATE TABLE contact_methods (_id INTEGER PRIMARY KEY AUTOINCREMENT,person INTEGER REFERENCES people(_id),kind INTEGER NOT NULL,data TEXT,aux_data TEXT,type INTEGER NOT NULL,label TEXT,isprimary INTEGER NOT NULL DEFAULT 0); 12INSERT INTO contact_methods VALUES(1,1,1,'gdgjj.fxhnj@ggh.com',NULL,1,NULL,1); 13INSERT INTO contact_methods VALUES(2,3,1,'weyj@xtdt.com',NULL,1,NULL,1); 14INSERT INTO contact_methods VALUES(3,5,1,'mail@dom.com',NULL,1,NULL,1); 15CREATE TABLE extensions (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,value TEXT NOT NULL,person INTEGER REFERENCES people(_id),UNIQUE(person, name)); 16CREATE TABLE fave_msg_status (_id INTEGER PRIMARY KEY, msg TEXT, next_retry INTEGER, num_retries INTEGER); 17CREATE TABLE fave_phone_changes (_id INTEGER PRIMARY KEY, old_phone TEXT, new_phone TEXT, change_timestamp TEXT); 18CREATE TABLE faves (_id INTEGER PRIMARY KEY, ui_pos INTEGER, phone TEXT NOT NULL, nickname TEXT NOT NULL, photo TEXT NOT NULL, timestamp TEXT NOT NULL, pending_phone TEXT, pending_nickname TEXT, pending_photo TEXT); 19INSERT INTO faves VALUES(1,1,0,'','',000000000000,NULL,NULL,NULL); 20INSERT INTO faves VALUES(2,2,0,'','',000000000000,NULL,NULL,NULL); 21INSERT INTO faves VALUES(3,3,0,'','',000000000000,NULL,NULL,NULL); 22INSERT INTO faves VALUES(4,4,0,'','',000000000000,NULL,NULL,NULL); 23INSERT INTO faves VALUES(5,5,0,'','',000000000000,NULL,NULL,NULL); 24CREATE TABLE groupmembership (_id INTEGER PRIMARY KEY,person INTEGER REFERENCES people(_id),group_id INTEGER REFERENCES groups(_id),group_sync_account STRING,group_sync_id STRING); 25INSERT INTO groupmembership VALUES(1,1,1,NULL,NULL); 26INSERT INTO groupmembership VALUES(2,2,1,NULL,NULL); 27INSERT INTO groupmembership VALUES(3,3,1,NULL,NULL); 28INSERT INTO groupmembership VALUES(4,4,1,NULL,NULL); 29INSERT INTO groupmembership VALUES(5,5,1,NULL,NULL); 30CREATE TABLE groups (_id INTEGER PRIMARY KEY AUTOINCREMENT,_sync_account TEXT,_sync_id TEXT,_sync_time TEXT,_sync_version TEXT,_sync_local_id INTEGER,_sync_dirty INTEGER NOT NULL DEFAULT 0,_sync_mark INTEGER,name TEXT NOT NULL,notes TEXT,should_sync INTEGER NOT NULL DEFAULT 0,system_id TEXT,UNIQUE(name,system_id,_sync_account)); 31INSERT INTO groups VALUES(1,NULL,NULL,NULL,NULL,NULL,0,NULL,'Contacts',NULL,0,'Contacts'); 32CREATE TABLE organizations (_id INTEGER PRIMARY KEY AUTOINCREMENT,company TEXT,title TEXT,isprimary INTEGER NOT NULL DEFAULT 0,type INTEGER NOT NULL,label TEXT,person INTEGER REFERENCES people(_id)); 33CREATE TABLE people (_id INTEGER PRIMARY KEY AUTOINCREMENT,_sync_account TEXT,_sync_id TEXT,_sync_time TEXT,_sync_version TEXT,_sync_local_id INTEGER,_sync_dirty INTEGER NOT NULL DEFAULT 0,_sync_mark INTEGER,name TEXT COLLATE LOCALIZED,notes TEXT COLLATE LOCALIZED,times_contacted INTEGER NOT NULL DEFAULT 0,last_time_contacted INTEGER,starred INTEGER NOT NULL DEFAULT 0,primary_phone INTEGER REFERENCES phones(_id),primary_organization INTEGER REFERENCES organizations(_id),primary_email INTEGER REFERENCES contact_methods(_id),photo_version TEXT,custom_ringtone TEXT,send_to_voicemail INTEGER,phonetic_name TEXT COLLATE LOCALIZED); 34INSERT INTO people VALUES(1,NULL,NULL,NULL,NULL,NULL,1,NULL,'Number And Mail',NULL,0,NULL,0,1,NULL,1,NULL,NULL,0,''); 35INSERT INTO people VALUES(2,NULL,NULL,NULL,NULL,NULL,1,NULL,'Number',NULL,0,NULL,0,2,NULL,NULL,NULL,NULL,0,''); 36INSERT INTO people VALUES(3,NULL,NULL,NULL,NULL,NULL,1,NULL,'Mail',NULL,0,NULL,0,NULL,NULL,2,NULL,NULL,0,''); 37INSERT INTO people VALUES(4,NULL,NULL,NULL,NULL,NULL,1,NULL,'',NULL,0,NULL,0,3,NULL,NULL,NULL,NULL,0,''); 38INSERT INTO people VALUES(5,NULL,NULL,NULL,NULL,NULL,1,NULL,'',NULL,0,NULL,0,NULL,NULL,3,NULL,NULL,0,''); 39CREATE TABLE peopleLookup (token TEXT,source INTEGER REFERENCES people(_id),token_index INTEGER); 40INSERT INTO peopleLookup VALUES('4351412b314b0629432f064129393f',1,0); 41INSERT INTO peopleLookup VALUES('29432f',1,1); 42INSERT INTO peopleLookup VALUES('4129393f',1,2); 43INSERT INTO peopleLookup VALUES('4351412b314b',2,0); 44INSERT INTO peopleLookup VALUES('4129393f',3,0); 45INSERT INTO peopleLookup VALUES('',4,0); 46INSERT INTO peopleLookup VALUES('',5,0); 47CREATE TABLE peopleLookupWithPhoneticName (token TEXT,source INTEGER REFERENCES people(_id),token_index INTEGER); 48INSERT INTO peopleLookupWithPhoneticName VALUES('4351412b314b0629432f064129393f',1,0); 49INSERT INTO peopleLookupWithPhoneticName VALUES('29432f',1,1); 50INSERT INTO peopleLookupWithPhoneticName VALUES('4129393f',1,2); 51INSERT INTO peopleLookupWithPhoneticName VALUES('4351412b314b',2,0); 52INSERT INTO peopleLookupWithPhoneticName VALUES('4129393f',3,0); 53INSERT INTO peopleLookupWithPhoneticName VALUES('225d',4,0); 54INSERT INTO peopleLookupWithPhoneticName VALUES('225d',5,0); 55CREATE TABLE phones (_id INTEGER PRIMARY KEY AUTOINCREMENT,person INTEGER REFERENCES people(_id),type INTEGER NOT NULL,number TEXT,number_key TEXT,label TEXT,isprimary INTEGER NOT NULL DEFAULT 0); 56INSERT INTO phones VALUES(1,1,2,'542-31',13245,NULL,1); 57INSERT INTO phones VALUES(2,2,2,'716-5432',2345617,NULL,1); 58INSERT INTO phones VALUES(3,4,2,'698-147-02',20741896,NULL,1); 59CREATE TABLE photos (_id INTEGER PRIMARY KEY AUTOINCREMENT,exists_on_server INTEGER NOT NULL DEFAULT 0,person INTEGER REFERENCES people(_id), local_version TEXT,data BLOB,sync_error TEXT,_sync_account TEXT,_sync_id TEXT,_sync_time TEXT,_sync_version TEXT,_sync_local_id INTEGER,_sync_dirty INTEGER NOT NULL DEFAULT 0,_sync_mark INTEGER,UNIQUE(person) ); 60INSERT INTO photos VALUES(1,0,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL); 61INSERT INTO photos VALUES(2,0,2,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL); 62INSERT INTO photos VALUES(3,0,3,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL); 63INSERT INTO photos VALUES(4,0,4,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL); 64INSERT INTO photos VALUES(5,0,5,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL); 65CREATE TABLE settings (_id INTEGER PRIMARY KEY,_sync_account TEXT,key STRING NOT NULL,value STRING ); 66CREATE TABLE sqlite_sequence(name,seq); 67INSERT INTO sqlite_sequence VALUES('groups',1); 68INSERT INTO sqlite_sequence VALUES('people',5); 69INSERT INTO sqlite_sequence VALUES('photos',5); 70INSERT INTO sqlite_sequence VALUES('phones',3); 71INSERT INTO sqlite_sequence VALUES('contact_methods',3); 72CREATE TABLE voice_dialer_timestamp (_id INTEGER PRIMARY KEY,timestamp INTEGER); 73INSERT INTO voice_dialer_timestamp VALUES(1,1246834337); 74CREATE INDEX contactMethodsPeopleIndex ON contact_methods (person); 75CREATE INDEX extensionsIndex1 ON extensions (person, name); 76CREATE INDEX groupmembershipIndex1 ON groupmembership (person, group_id); 77CREATE INDEX groupmembershipIndex2 ON groupmembership (group_id, person); 78CREATE INDEX groupmembershipIndex3 ON groupmembership (group_sync_account, group_sync_id); 79CREATE INDEX groupsSyncDirtyIndex ON groups (_sync_dirty); 80CREATE INDEX organizationsIndex1 ON organizations (person); 81CREATE INDEX peopleLookupIndex ON peopleLookup (token,source); 82CREATE INDEX peopleLookupWithPhoneticNameIndex ON peopleLookupWithPhoneticName (token,source); 83CREATE INDEX peopleNameIndex ON people (name); 84CREATE INDEX peopleSyncDirtyIndex ON people (_sync_dirty); 85CREATE INDEX peopleSyncIdIndex ON people (_sync_id); 86CREATE INDEX phonesIndex1 ON phones (person); 87CREATE INDEX phonesIndex2 ON phones (number_key); 88CREATE INDEX photoPersonIndex ON photos (person); 89CREATE INDEX photosSyncDirtyIndex ON photos (_sync_dirty); 90CREATE TRIGGER contact_cleanup DELETE ON people BEGIN DELETE FROM peopleLookup WHERE source = old._id;DELETE FROM peopleLookupWithPhoneticName WHERE source = old._id;DELETE FROM phones WHERE person = old._id;DELETE FROM contact_methods WHERE person = old._id;DELETE FROM organizations WHERE person = old._id;DELETE FROM groupmembership WHERE person = old._id;DELETE FROM extensions WHERE person = old._id;END; 91CREATE TRIGGER contact_methods_INSERT_typeAndLabel AFTER INSERT ON contact_methods WHEN (NEW.type != 0 AND NEW.label IS NOT NULL) OR (NEW.type = 0 AND NEW.label IS NULL) BEGIN SELECT RAISE (ABORT, 'exactly one of type or label must be set'); END; 92CREATE TRIGGER contact_methods_UPDATE_typeAndLabel AFTER UPDATE ON contact_methods WHEN (NEW.type != 0 AND NEW.label IS NOT NULL) OR (NEW.type = 0 AND NEW.label IS NULL) BEGIN SELECT RAISE (ABORT, 'exactly one of type or label must be set'); END; 93CREATE TRIGGER contact_methods_delete DELETE ON contact_methods BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 94CREATE TRIGGER contact_methods_insert INSERT ON contact_methods BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person;END; 95CREATE TRIGGER contact_methods_update UPDATE ON contact_methods BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 96CREATE TRIGGER extensions_delete DELETE ON extensions BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 97CREATE TRIGGER extensions_insert INSERT ON extensions BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person; END; 98CREATE TRIGGER extensions_update AFTER UPDATE ON extensions BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person; END; 99CREATE TRIGGER groupmembership_delete DELETE ON groupmembership BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 100CREATE TRIGGER groupmembership_insert INSERT ON groupmembership BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person; END; 101CREATE TRIGGER groupmembership_update AFTER UPDATE ON groupmembership BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person; END; 102CREATE TRIGGER groups_cleanup DELETE ON groups BEGIN UPDATE groupmembership SET group_id = null WHERE group_id = old._id;END; 103CREATE TRIGGER groups_to_deleted DELETE ON groups WHEN old._sync_id is not null BEGIN INSERT INTO _deleted_groups (_sync_id, _sync_account, _sync_version) VALUES (old._sync_id, old._sync_account, old._sync_version);END; 104CREATE TRIGGER organizations_INSERT_typeAndLabel AFTER INSERT ON organizations WHEN (NEW.type != 0 AND NEW.label IS NOT NULL) OR (NEW.type = 0 AND NEW.label IS NULL) BEGIN SELECT RAISE (ABORT, 'exactly one of type or label must be set'); END; 105CREATE TRIGGER organizations_UPDATE_typeAndLabel AFTER UPDATE ON organizations WHEN (NEW.type != 0 AND NEW.label IS NOT NULL) OR (NEW.type = 0 AND NEW.label IS NULL) BEGIN SELECT RAISE (ABORT, 'exactly one of type or label must be set'); END; 106CREATE TRIGGER organizations_delete DELETE ON organizations BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 107CREATE TRIGGER organizations_insert INSERT ON organizations BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person; END; 108CREATE TRIGGER organizations_update AFTER UPDATE ON organizations BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person; END; 109CREATE TRIGGER peopleDeleteAndPhotos DELETE ON people BEGIN DELETE FROM photos WHERE person=OLD._id; END; 110CREATE TRIGGER peopleLookupWithPhoneticName_insert AFTER INSERT ON people BEGIN SELECT _TOKENIZE('peopleLookupWithPhoneticName', new._id, GET_NORMALIZED_STRING(CASE WHEN (new.phonetic_name IS NOT NULL AND new.phonetic_name != '') THEN new.phonetic_name ELSE (CASE WHEN (new.name is NOT NULL AND new.name != '') THEN new.name ELSE '' END) END), ' ', 1);END; 111CREATE TRIGGER peopleLookupWithPhoneticName_update UPDATE OF name, phonetic_name ON people BEGIN DELETE FROM peopleLookupWithPhoneticName WHERE source = new._id;SELECT _TOKENIZE('peopleLookupWithPhoneticName', new._id, GET_NORMALIZED_STRING(CASE WHEN (new.phonetic_name IS NOT NULL AND new.phonetic_name != '') THEN new.phonetic_name ELSE (CASE WHEN (new.name is NOT NULL AND new.name != '') THEN new.name ELSE '' END) END), ' ', 1);END; 112CREATE TRIGGER peopleLookup_insert AFTER INSERT ON people BEGIN SELECT _TOKENIZE('peopleLookup', new._id, new.name, ' ', 1);END; 113CREATE TRIGGER peopleLookup_update UPDATE OF name ON people BEGIN DELETE FROM peopleLookup WHERE source = new._id;SELECT _TOKENIZE('peopleLookup', new._id, new.name, ' ', 1);END; 114CREATE TRIGGER people_timesContacted UPDATE OF last_time_contacted ON people BEGIN UPDATE people SET times_contacted = (new.times_contacted + 1) WHERE _id = new._id;END; 115CREATE TRIGGER phones_INSERT_typeAndLabel AFTER INSERT ON phones WHEN (NEW.type != 0 AND NEW.label IS NOT NULL) OR (NEW.type = 0 AND NEW.label IS NULL) BEGIN SELECT RAISE (ABORT, 'exactly one of type or label must be set'); END; 116CREATE TRIGGER phones_UPDATE_typeAndLabel AFTER UPDATE ON phones WHEN (NEW.type != 0 AND NEW.label IS NOT NULL) OR (NEW.type = 0 AND NEW.label IS NULL) BEGIN SELECT RAISE (ABORT, 'exactly one of type or label must be set'); END; 117CREATE TRIGGER phones_delete DELETE ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 118CREATE TRIGGER phones_insert INSERT ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person;END; 119CREATE TRIGGER phones_update UPDATE ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END; 120CREATE TRIGGER timestamp_trigger1 AFTER UPDATE ON phones BEGIN UPDATE voice_dialer_timestamp SET timestamp=strftime('%s', 'now') WHERE _id=1;END; 121CREATE TRIGGER timestamp_trigger2 AFTER UPDATE OF name ON people BEGIN UPDATE voice_dialer_timestamp SET timestamp=strftime('%s', 'now') WHERE _id=1;END; 122COMMIT; 123