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);
5INSERT INTO _deleted_people VALUES(1249441421793000,'20d08a710c3df43d','android.contacts.test.eclair@gmail.com',NULL);
6CREATE TABLE _sync_state (_id INTEGER PRIMARY KEY,_sync_account TEXT,data TEXT,UNIQUE(_sync_account));
7INSERT INTO _sync_state VALUES(1,'android.contacts.test.eclair@gmail.com','');
8CREATE TABLE _sync_state_metadata (version INTEGER);
9INSERT INTO _sync_state_metadata VALUES(2);
10CREATE TABLE android_metadata (locale TEXT);
11INSERT INTO android_metadata VALUES('en_US');
12CREATE TABLE calls (_id INTEGER PRIMARY KEY AUTOINCREMENT,number TEXT,date INTEGER,duration INTEGER,type INTEGER,new INTEGER,name TEXT,numbertype INTEGER,numberlabel TEXT);
13INSERT INTO calls VALUES(1,18004664411,1249177360040,10,2,1,'Jane Doe',1,NULL);
14CREATE 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);
15INSERT INTO contact_methods VALUES(1,2,1,'a@acme.com',NULL,1,NULL,1);
16INSERT INTO contact_methods VALUES(2,2,1,'b@acme.com',NULL,2,NULL,0);
17INSERT INTO contact_methods VALUES(3,2,1,'c@acme.com',NULL,3,NULL,0);
18INSERT INTO contact_methods VALUES(4,2,1,'d@acme.com',NULL,3,NULL,0);
19INSERT INTO contact_methods VALUES(5,2,3,'a','pre:5',3,NULL,0);
20INSERT INTO contact_methods VALUES(6,2,3,'b','pre:0',3,NULL,0);
21INSERT INTO contact_methods VALUES(7,2,3,'c','pre:2',3,NULL,0);
22INSERT INTO contact_methods VALUES(8,2,3,'d','pre:3',3,NULL,0);
23INSERT INTO contact_methods VALUES(9,2,3,'e','pre:4',3,NULL,0);
24INSERT INTO contact_methods VALUES(10,2,3,'f','pre:1',3,NULL,0);
25INSERT INTO contact_methods VALUES(11,2,3,'g','pre:6',3,NULL,0);
26INSERT INTO contact_methods VALUES(12,2,3,'h','pre:7',3,NULL,0);
27INSERT INTO contact_methods VALUES(13,3,1,'deer@acme.com',NULL,2,NULL,1);
28INSERT INTO contact_methods VALUES(14,3,2,'12345 Main Street
29Main Town, CA 95000',NULL,2,NULL,0);
30INSERT INTO contact_methods VALUES(15,3,3,'deerdough','pre:5',3,NULL,0);
31CREATE TABLE extensions (_id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT NOT NULL,value TEXT NOT NULL,person INTEGER REFERENCES people(_id),UNIQUE(person, name));
32CREATE TABLE fave_msg_status (_id INTEGER PRIMARY KEY, msg TEXT, next_retry INTEGER, num_retries INTEGER);
33CREATE TABLE fave_phone_changes (_id INTEGER PRIMARY KEY, old_phone TEXT, new_phone TEXT, change_timestamp TEXT);
34CREATE 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);
35INSERT INTO faves VALUES(1,1,0,'','',000000000000,NULL,NULL,NULL);
36INSERT INTO faves VALUES(2,2,0,'','',000000000000,NULL,NULL,NULL);
37INSERT INTO faves VALUES(3,3,0,'','',000000000000,NULL,NULL,NULL);
38INSERT INTO faves VALUES(4,4,0,'','',000000000000,NULL,NULL,NULL);
39INSERT INTO faves VALUES(5,5,0,'','',000000000000,NULL,NULL,NULL);
40CREATE 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);
41INSERT INTO groupmembership VALUES(1,2,NULL,'android.contacts.test.eclair@gmail.com',6);
42INSERT INTO groupmembership VALUES(2,3,NULL,'android.contacts.test.eclair@gmail.com',6);
43INSERT INTO groupmembership VALUES(3,3,NULL,'android.contacts.test.eclair@gmail.com','20a186238cf27aaa');
44INSERT INTO groupmembership VALUES(9,3,NULL,'android.contacts.test.eclair@gmail.com',55555555555);
45INSERT INTO groupmembership VALUES(10,11,1,NULL,NULL);
46INSERT INTO groupmembership VALUES(11,2,3,'android.contacts.test.eclair@gmail.com','2fcbf74c8be345dc');
47CREATE 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));
48INSERT INTO groups VALUES(1,'android.contacts.test.eclair@gmail.com',6,NULL,NULL,NULL,0,1,'System Group: My Contacts','System Group: My Contacts',0,'Contacts');
49INSERT INTO groups VALUES(2,'android.contacts.test.eclair@gmail.com','20a186238cf27aaa',1249172992736000,1249172992736000,NULL,0,1,'Eclair Contacts','Eclair Contacts',0,NULL);
50INSERT INTO groups VALUES(3,'android.contacts.test.eclair@gmail.com','2fcbf74c8be345dc',1249176693566000,1249176693566000,NULL,0,1,'Starred in Android','Starred in Android',0,NULL);
51CREATE 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));
52INSERT INTO organizations VALUES(1,'Acme Corp','President',0,1,NULL,3);
53CREATE 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);
54INSERT INTO people VALUES(1,'non_syncable',NULL,NULL,NULL,NULL,1,1,'Test Android',NULL,0,NULL,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
55INSERT INTO people VALUES(2,'android.contacts.test.eclair@gmail.com','3d09f37e0f0dbc6f',1249441106583000,1249441106583000,NULL,1,1,'Jane Doe',NULL,1,1249177384979,1,1,NULL,1,NULL,NULL,NULL,NULL);
56INSERT INTO people VALUES(3,'android.contacts.test.eclair@gmail.com','5c9ae978b346ac9',1249441067558000,1249441067558000,NULL,1,1,'John Doe','This is a test account for Eclair Android Contacts',0,NULL,1,8,1,13,NULL,'content://media/internal/audio/media/80',1,NULL);
57INSERT INTO people VALUES(11,'android.contacts.test.eclair@gmail.com',NULL,NULL,NULL,NULL,1,NULL,'Added On Android',NULL,0,NULL,0,16,NULL,NULL,NULL,NULL,0,'');
58CREATE TABLE peopleLookup (token TEXT,source INTEGER REFERENCES people(_id),token_index INTEGER);
59INSERT INTO peopleLookup VALUES('4f314d4f0629432f4b45392f',1,0);
60INSERT INTO peopleLookup VALUES('29432f4b45392f',1,1);
61INSERT INTO peopleLookup VALUES('3b294331062f4531',2,0);
62INSERT INTO peopleLookup VALUES('2f4531',2,1);
63INSERT INTO peopleLookup VALUES('3b453743062f4531',3,0);
64INSERT INTO peopleLookup VALUES('2f4531',3,1);
65INSERT INTO peopleLookup VALUES('292f2f312f0645430629432f4b45392f',11,0);
66INSERT INTO peopleLookup VALUES(4543,11,1);
67INSERT INTO peopleLookup VALUES('29432f4b45392f',11,2);
68CREATE TABLE peopleLookupWithPhoneticName (token TEXT,source INTEGER REFERENCES people(_id),token_index INTEGER);
69INSERT INTO peopleLookupWithPhoneticName VALUES('4f314d4f0629432f4b45392f',1,0);
70INSERT INTO peopleLookupWithPhoneticName VALUES('29432f4b45392f',1,1);
71INSERT INTO peopleLookupWithPhoneticName VALUES('3b294331062f4531',2,0);
72INSERT INTO peopleLookupWithPhoneticName VALUES('2f4531',2,1);
73INSERT INTO peopleLookupWithPhoneticName VALUES('3b453743062f4531',3,0);
74INSERT INTO peopleLookupWithPhoneticName VALUES('2f4531',3,1);
75INSERT INTO peopleLookupWithPhoneticName VALUES('292f2f312f0645430629432f4b45392f',11,0);
76INSERT INTO peopleLookupWithPhoneticName VALUES(4543,11,1);
77INSERT INTO peopleLookupWithPhoneticName VALUES('29432f4b45392f',11,2);
78CREATE 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);
79INSERT INTO phones VALUES(1,2,1,'1-800-466-4411',11446640081,NULL,1);
80INSERT INTO phones VALUES(2,2,3,2345678901,1098765432,NULL,0);
81INSERT INTO phones VALUES(3,2,2,3456789012,2109876543,NULL,0);
82INSERT INTO phones VALUES(4,2,5,4567890123,3210987654,NULL,0);
83INSERT INTO phones VALUES(5,2,4,5678901234,4321098765,NULL,0);
84INSERT INTO phones VALUES(6,2,6,6789012345,5432109876,NULL,0);
85INSERT INTO phones VALUES(7,2,7,7890123456,6543210987,NULL,0);
86INSERT INTO phones VALUES(8,3,2,'555-555-5555',5555555555,NULL,0);
87INSERT INTO phones VALUES(11,2,1,1234567890,0987654321,NULL,0);
88INSERT INTO phones VALUES(16,11,2,'1-987-4563',36547891,NULL,1);
89CREATE 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) );
90INSERT INTO photos VALUES(1,0,1,NULL,NULL,NULL,'non_syncable',NULL,NULL,NULL,NULL,0,1);
91INSERT INTO photos VALUES(2,1,2,'4Yujp-X7nRp5v8zJca7cxg','����',NULL,'android.contacts.test.eclair@gmail.com','3d09f37e0f0dbc6f','4Yujp-X7nRp5v8zJca7cxg','4Yujp-X7nRp5v8zJca7cxg',NULL,0,1);
92INSERT INTO photos VALUES(3,1,3,'sQwgwiST8gBzusqDIFkOgg','����',NULL,'android.contacts.test.eclair@gmail.com','5c9ae978b346ac9','sQwgwiST8gBzusqDIFkOgg','sQwgwiST8gBzusqDIFkOgg',NULL,0,1);
93INSERT INTO photos VALUES(11,0,11,NULL,NULL,NULL,'android.contacts.test.eclair@gmail.com',NULL,NULL,NULL,NULL,0,NULL);
94CREATE TABLE settings (_id INTEGER PRIMARY KEY,_sync_account TEXT,key STRING NOT NULL,value STRING );
95INSERT INTO settings VALUES(1,NULL,'syncEverything',1);
96CREATE TABLE sqlite_sequence(name,seq);
97INSERT INTO sqlite_sequence VALUES('groups',3);
98INSERT INTO sqlite_sequence VALUES('people',11);
99INSERT INTO sqlite_sequence VALUES('photos',11);
100INSERT INTO sqlite_sequence VALUES('phones',16);
101INSERT INTO sqlite_sequence VALUES('contact_methods',24);
102INSERT INTO sqlite_sequence VALUES('organizations',2);
103INSERT INTO sqlite_sequence VALUES('calls',1);
104CREATE TABLE voice_dialer_timestamp (_id INTEGER PRIMARY KEY,timestamp INTEGER);
105INSERT INTO voice_dialer_timestamp VALUES(1,1249441215);
106CREATE INDEX contactMethodsPeopleIndex ON contact_methods (person);
107CREATE INDEX extensionsIndex1 ON extensions (person, name);
108CREATE INDEX groupmembershipIndex1 ON groupmembership (person, group_id);
109CREATE INDEX groupmembershipIndex2 ON groupmembership (group_id, person);
110CREATE INDEX groupmembershipIndex3 ON groupmembership (group_sync_account, group_sync_id);
111CREATE INDEX groupsSyncDirtyIndex ON groups (_sync_dirty);
112CREATE INDEX organizationsIndex1 ON organizations (person);
113CREATE INDEX peopleLookupIndex ON peopleLookup (token,source);
114CREATE INDEX peopleLookupWithPhoneticNameIndex ON peopleLookupWithPhoneticName (token,source);
115CREATE INDEX peopleNameIndex ON people (name);
116CREATE INDEX peopleSyncDirtyIndex ON people (_sync_dirty);
117CREATE INDEX peopleSyncIdIndex ON people (_sync_id);
118CREATE INDEX phonesIndex1 ON phones (person);
119CREATE INDEX phonesIndex2 ON phones (number_key);
120CREATE INDEX photoPersonIndex ON photos (person);
121CREATE INDEX photosSyncDirtyIndex ON photos (_sync_dirty);
122CREATE 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;
123CREATE 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;
124CREATE 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;
125CREATE TRIGGER contact_methods_delete DELETE ON contact_methods BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
126CREATE TRIGGER contact_methods_insert INSERT ON contact_methods BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person;END;
127CREATE TRIGGER contact_methods_update UPDATE ON contact_methods BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
128CREATE TRIGGER extensions_delete DELETE ON extensions BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
129CREATE TRIGGER extensions_insert INSERT ON extensions BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person; END;
130CREATE TRIGGER extensions_update AFTER UPDATE ON extensions BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person; END;
131CREATE TRIGGER groupmembership_delete DELETE ON groupmembership BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
132CREATE TRIGGER groupmembership_insert INSERT ON groupmembership BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person; END;
133CREATE TRIGGER groupmembership_update AFTER UPDATE ON groupmembership BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person; END;
134CREATE TRIGGER groups_cleanup DELETE ON groups BEGIN UPDATE groupmembership SET group_id = null WHERE group_id = old._id;END;
135CREATE 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;
136CREATE 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;
137CREATE 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;
138CREATE TRIGGER organizations_delete DELETE ON organizations BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
139CREATE TRIGGER organizations_insert INSERT ON organizations BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person; END;
140CREATE TRIGGER organizations_update AFTER UPDATE ON organizations BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person; END;
141CREATE TRIGGER peopleDeleteAndPhotos DELETE ON people  BEGIN   DELETE FROM photos WHERE person=OLD._id; END;
142CREATE 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;
143CREATE 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;
144CREATE TRIGGER peopleLookup_insert AFTER INSERT ON people BEGIN SELECT _TOKENIZE('peopleLookup', new._id, new.name, ' ', 1);END;
145CREATE 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;
146CREATE 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;
147CREATE 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;
148CREATE 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;
149CREATE TRIGGER phones_delete DELETE ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
150CREATE TRIGGER phones_insert INSERT ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=new.person;END;
151CREATE TRIGGER phones_update UPDATE ON phones BEGIN UPDATE people SET _sync_dirty=1 WHERE people._id=old.person;END;
152CREATE TRIGGER timestamp_trigger1 AFTER UPDATE ON phones BEGIN UPDATE voice_dialer_timestamp SET timestamp=strftime('%s', 'now') WHERE _id=1;END;
153CREATE TRIGGER timestamp_trigger2 AFTER UPDATE OF name ON people BEGIN UPDATE voice_dialer_timestamp SET timestamp=strftime('%s', 'now') WHERE _id=1;END;
154COMMIT;
155