summaryrefslogtreecommitdiff
path: root/schema.sql
blob: 3a6017a50a791d9ad0936550b9a3af2ddc4135f1 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
--
-- Contacts Service
--
-- Copyright (c) 2010 - 2012 Samsung Electronics Co., Ltd. All rights reserved.
--
-- Contact: Jongwon Lee <gogosing.lee@samsung.com>
--
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--
-- http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
--

--PRAGMA journal_mode = PERSIST;
--PRAGMA journal_mode = TRUNCATE;

CREATE TABLE persons
(
	person_id			INTEGER PRIMARY KEY AUTOINCREMENT,
	name_contact_id		INTEGER NOT NULL,
	has_phonenumber		INTEGER,
	has_email			INTEGER,
	created_ver			INTEGER NOT NULL,
	changed_ver			INTEGER NOT NULL,
	ringtone_path			TEXT,
	vibration			TEXT,
	image_thumbnail_path		TEXT,
	image_path			TEXT,
	link_count			INTEGER,
	addressbook_ids			TEXT,
	dirty				INTEGER,
	status				TEXT
);

CREATE TRIGGER trg_person_del AFTER DELETE ON persons
 BEGIN
	DELETE FROM favorites WHERE person_id = old.person_id;
	SELECT _PERSON_DELETE_(old.person_id);
 END;

CREATE TABLE addressbooks
(
	addressbook_id		INTEGER PRIMARY KEY AUTOINCREMENT,
	addressbook_name	TEXT NOT NULL,
	account_id			INTEGER,
	mode						INTEGER, -- permission
	last_sync_ver	INTEGER,
	UNIQUE(addressbook_name)
);

insert into addressbooks(addressbook_id, addressbook_name, mode, account_id) values(0, 'http://tizen.org/addressbook/phone', 0, 0);

CREATE TRIGGER trg_addressbook_del AFTER DELETE ON addressbooks
 BEGIN
   DELETE FROM groups WHERE addressbook_id = old.addressbook_id;
   UPDATE contacts SET deleted = 1, person_id = 0, changed_ver = ((SELECT ver FROM cts_version) + 1) WHERE addressbook_id = old.addressbook_id;
   DELETE FROM my_profiles WHERE addressbook_id = old.addressbook_id;
   DELETE FROM contact_deleteds WHERE addressbook_id = old.addressbook_id;
   DELETE FROM group_deleteds WHERE addressbook_id = old.addressbook_id;
 END;

CREATE TABLE contacts
(
	contact_id			INTEGER PRIMARY KEY AUTOINCREMENT,
	person_id			INTEGER NOT NULL,
	addressbook_id			INTEGER NOT NULL DEFAULT 0,
	has_phonenumber		INTEGER,
	has_email			INTEGER,
	is_favorite			INTEGER DEFAULT 0,
	deleted				INTEGER DEFAULT 0,
	display_name			TEXT,
	reverse_display_name		TEXT,
	display_name_source		INTEGER,
	display_name_language		INTEGER,
	reverse_display_name_language		INTEGER,
	sort_name			TEXT,
	reverse_sort_name		TEXT,
	sortkey				TEXT COLLATE NOCASE,
	reverse_sortkey			TEXT COLLATE NOCASE,
	created_ver			INTEGER NOT NULL,
	changed_ver			INTEGER NOT NULL,
	changed_time			INTEGER NOT NULL,
	image_changed_ver	INTEGER NOT NULL,
	uid				TEXT,
	ringtone_path			TEXT,
	vibration			TEXT,
	image_thumbnail_path		TEXT,
	image_path			TEXT
);

CREATE INDEX contacts_idx1 ON contacts(changed_ver);
CREATE INDEX contacts_idx2 ON contacts(person_id);
CREATE INDEX contacts_idx3 ON contacts(display_name_language, sortkey);
CREATE INDEX contacts_idx4 ON contacts(reverse_display_name_language, reverse_sortkey);

-- There are three case of deleting contact logically
--   Case 1 : delete contact
--   Case 2 : delete addressbook
--   Case 3 : delete person
-- In all Case, the deleted contacts(deleted=1) are really deleted in the background.
CREATE TRIGGER trg_contacts_del AFTER DELETE ON contacts
	BEGIN
		DELETE FROM data WHERE contact_id = old.contact_id AND is_my_profile = 0;
		DELETE FROM group_relations WHERE old.addressbook_id != -1 AND contact_id = old.contact_id;
		DELETE FROM activities WHERE contact_id = old.contact_id;
		DELETE FROM persons WHERE person_id = old.person_id AND link_count = 1;
		DELETE FROM search_index WHERE contact_id = old.contact_id;
		DELETE FROM name_lookup WHERE contact_id = old.contact_id;
		DELETE FROM phone_lookup WHERE contact_id = old.contact_id;
		UPDATE persons SET dirty=1 WHERE person_id = old.person_id AND link_count > 1;
	END;

-- It is triggered during really deleting contact in the background (deleted = 1).
-- Deleted version(changed_ver) is already set when updating deleted field as 1.
CREATE TRIGGER trg_contacts_del2 AFTER DELETE ON contacts
	WHEN old.addressbook_id = (SELECT addressbook_id from addressbooks WHERE addressbook_id = old.addressbook_id) AND old.deleted = 1
	BEGIN
		INSERT INTO contact_deleteds VALUES(old.contact_id, old.addressbook_id, old.created_ver, old.changed_ver);
	END;

-- CREATE TRIGGER trg_contacts_del3 AFTER DELETE ON contacts
--	WHEN old.addressbook_id = (SELECT addressbook_id from addressbooks WHERE addressbook_id = old.addressbook_id) AND old.deleted = 0
--	BEGIN
--		INSERT INTO contact_deleteds VALUES(old.contact_id, old.addressbook_id, old.created_ver, (SELECT ver FROM cts_version) + 1);
--	END;

CREATE TRIGGER trg_contacts_update AFTER UPDATE ON contacts
	WHEN new.deleted = 1
	BEGIN
		DELETE FROM group_relations WHERE old.addressbook_id != -1 AND contact_id = old.contact_id;
		DELETE FROM persons WHERE person_id = old.person_id AND link_count = 1;
		UPDATE persons SET dirty=1 WHERE person_id = old.person_id AND link_count > 1;
	END;

CREATE TABLE contact_deleteds
(
	contact_id			INTEGER PRIMARY KEY,
	addressbook_id		INTEGER NOT NULL,
	created_ver			INTEGER NOT NULL,
	deleted_ver			INTEGER NOT NULL
);
CREATE INDEX contact_deleteds_idx1 ON contact_deleteds(deleted_ver);

CREATE TABLE cts_version
(
	ver				INTEGER PRIMARY KEY
);

INSERT INTO cts_version VALUES(0);

CREATE TABLE sdn
(
	id				INTEGER PRIMARY KEY AUTOINCREMENT,
	name				TEXT,
	number				TEXT
);

CREATE TABLE data
(
	id				INTEGER PRIMARY KEY AUTOINCREMENT,
	contact_id			INTEGER NOT NULL,
	datatype			INTEGER NOT NULL,
	is_my_profile		INTEGER,
	is_primary_default		INTEGER,
	is_default			INTEGER,
	data1				INTEGER,
	data2				TEXT,
	data3				TEXT,
	data4				TEXT,
	data5				TEXT,
	data6				TEXT,
	data7				TEXT,
	data8				TEXT,
	data9				TEXT,
	data10				TEXT,
	data11				TEXT,
	data12				TEXT
);

CREATE TRIGGER trg_data_del AFTER DELETE ON data
	BEGIN
		SELECT _DATA_DELETE_(old.id, old.datatype);
	END;

CREATE TRIGGER trg_data_image_del AFTER DELETE ON data
	WHEN old.datatype = 13
		BEGIN
			SELECT _DATA_IMAGE_DELETE_(old.data3);
		END;

CREATE TRIGGER trg_data_company_del AFTER DELETE ON data
	WHEN old.datatype = 6
		BEGIN
			SELECT _DATA_COMPANY_DELETE_(old.data8);
		END;

CREATE TRIGGER trg_data_number_del AFTER DELETE ON data
	WHEN old.datatype = 8
		BEGIN
			DELETE FROM speeddials WHERE  number_id = old.id;
		END;

CREATE INDEX data_contact_idx1 ON data(contact_id);
CREATE INDEX data_contact_idx2 ON data(datatype, contact_id);
CREATE INDEX data_idx1 ON data(data1);
CREATE INDEX data_idx2 ON data(data2);
CREATE INDEX data_idx3 ON data(data3);
CREATE INDEX data_idx4 ON data(data4);
CREATE INDEX data_idx5 ON data(data5);
CREATE INDEX data_idx6 ON data(data6);
CREATE INDEX data_idx7 ON data(data7);
CREATE INDEX data_idx8 ON data(data8);
CREATE INDEX data_idx9 ON data(data9);
CREATE INDEX data_idx10 ON data(data10);

CREATE TABLE groups
(
	group_id			INTEGER PRIMARY KEY AUTOINCREMENT,
	addressbook_id			INTEGER,
	group_name			TEXT,
	extra_data			TEXT,
	is_read_only			INTEGER DEFAULT 0,
	created_ver			INTEGER NOT NULL,
	changed_ver			INTEGER NOT NULL,
	ringtone_path			TEXT,
	vibration			TEXT,
	image_thumbnail_path		TEXT,
	member_changed_ver		INTEGER,
	group_prio			REAL
);

INSERT INTO groups(addressbook_id, group_name, extra_data, is_read_only, created_ver, changed_ver, group_prio)
	VALUES(0, 'Co-workers', 'coworkers', 0, 0, 0, 1);
INSERT INTO groups(addressbook_id, group_name, extra_data, is_read_only, created_ver, changed_ver, group_prio)
	VALUES(0, 'Family', 'family', 0, 0, 0, 2);
INSERT INTO groups(addressbook_id, group_name, extra_data, is_read_only, created_ver, changed_ver, group_prio)
	VALUES(0, 'Friends', 'friends',0, 0, 0, 3);

CREATE TRIGGER trg_groups_del AFTER DELETE ON groups
 BEGIN
   DELETE FROM group_relations WHERE group_id = old.group_id;
 END;

CREATE TRIGGER trg_groups_del2 AFTER DELETE ON groups
	WHEN old.addressbook_id IN (SELECT addressbook_id from addressbooks WHERE addressbook_id = old.addressbook_id)
	BEGIN
		INSERT INTO group_deleteds VALUES(old.group_id, old.addressbook_id, old.created_ver, (SELECT ver FROM cts_version) + 1);
	END;

CREATE TABLE group_deleteds
(
	group_id				INTEGER PRIMARY KEY,
	addressbook_id		INTEGER NOT NULL,
	created_ver			INTEGER NOT NULL,
	deleted_ver			INTEGER NOT NULL
);

CREATE INDEX group_deleteds_idx1 ON group_deleteds(deleted_ver);

CREATE TABLE group_relations
(
	group_id			INTEGER NOT NULL,
	contact_id			INTEGER NOT NULL,
	ver INTEGER NOT NULL,
	deleted INTEGER DEFAULT 0,
	UNIQUE(group_id, contact_id)
);
CREATE INDEX groups_idx1 ON group_relations(contact_id);


CREATE TABLE speeddials
(
	speed_number			INTEGER PRIMARY KEY NOT NULL,
	number_id			INTEGER UNIQUE
);

CREATE TABLE favorites
(
	person_id			INTEGER PRIMARY KEY,
	favorite_prio			REAL
);
CREATE INDEX favorites_idx1 ON favorites(favorite_prio);
CREATE INDEX favorites_idx2 ON favorites(person_id);


--CREATE TRIGGER trg_favorites_del BEFORE DELETE ON favorites
--	BEGIN
--		UPDATE contacts SET is_favorite = 0 WHERE person_id = old.person_id;
--	END;
--CREATE TRIGGER trg_favorites_insert AFTER INSERT ON favorites
--	BEGIN
--		UPDATE contacts SET is_favorite = 1 WHERE person_id = new.person_id;
--	END;


CREATE TABLE phonelogs
(
	id				INTEGER PRIMARY KEY AUTOINCREMENT,
	number				TEXT,
	normal_num			TEXT,
	person_id			INTEGER, --person_id
	log_type			INTEGER,
	log_time			INTEGER,
	data1				INTEGER, --duration, message_id
	data2				TEXT  -- short message
);

CREATE INDEX phonelogs_idx1 ON phonelogs(log_type);
CREATE INDEX phonelogs_idx2 ON phonelogs(log_time);
CREATE TRIGGER trg_phonelogs_del AFTER DELETE ON phonelogs
	BEGIN
		SELECT _PHONE_LOG_DELETE_(old.id);
	END;

--CREATE TRIGGER trg_phonelogs_del AFTER DELETE ON phonelogs
--	WHEN old.log_type = 2 OR old.log_type = 4
--		BEGIN
--			DELETE FROM phonelog_accumulation WHERE log_time < (old.log_time - 3456000); -- 40 days
--			INSERT INTO phonelog_accumulation VALUES(NULL, 1, old.log_time, old.data1);
--		END;

--CREATE TABLE phonelog_accumulation
--(
--	id				INTEGER PRIMARY KEY AUTOINCREMENT,
--	log_cnt			INTEGER,
--	log_time			INTEGER,
--	duration			INTEGER
--);
--INSERT INTO phonelog_accumulation VALUES(1, 0, NULL, 0);
--INSERT INTO phonelog_accumulation VALUES(2, 0, NULL, 0); --total

CREATE TABLE phonelog_stat
(
	log_type			INTEGER PRIMARY KEY,
	log_count			INTEGER
);

CREATE TRIGGER trg_phonelogs_insert AFTER INSERT ON phonelogs
	BEGIN
		INSERT OR REPLACE INTO phonelog_stat values(new.log_type, coalesce((SELECT log_count+1 FROM phonelog_stat WHERE log_type=new.log_type), 1));
	END;

CREATE TABLE contact_stat
(
	id				INTEGER PRIMARY KEY AUTOINCREMENT,
	person_id			INTEGER,
	usage_type			INTEGER,
	times_used			INTEGER
);

CREATE TABLE activities
(
	id				INTEGER PRIMARY KEY AUTOINCREMENT,
	contact_id			INTEGER NOT NULL,
	source_name			TEXT,
	status				TEXT,
	timestamp			INTEGER,
	service_operation	TEXT,
	uri					TEXT,
	sync_data1			TEXT,
	sync_data2			TEXT,
	sync_data3			TEXT,
	sync_data4			TEXT
);

CREATE TABLE activity_photos
(
	id				INTEGER PRIMARY KEY AUTOINCREMENT,
	activity_id			INTEGER NOT NULL,
	photo_url			TEXT,
	sort_index			INTEGER
);

CREATE TRIGGER trg_activities_insert AFTER INSERT ON activities
	BEGIN
		UPDATE persons SET status=(SELECT status FROM activities WHERE contact_id IN (SELECT contact_id FROM contacts WHERE person_id = (select person_id FROM contacts WHERE contact_id = new.contact_id)) ORDER BY timestamp DESC LIMIT 1)  WHERE person_id = (SELECT person_id FROM contacts WHERE contact_id = new.contact_id);
	END;

CREATE TRIGGER trg_activities_delete AFTER DELETE ON activities
	BEGIN
		UPDATE persons SET status=(SELECT status FROM activities WHERE contact_id IN (SELECT contact_id FROM contacts WHERE person_id = (select person_id FROM contacts WHERE contact_id = old.contact_id)) ORDER BY timestamp DESC LIMIT 1)  WHERE person_id = (SELECT person_id FROM contacts WHERE contact_id = old.contact_id);
		DELETE FROM activity_photos WHERE activity_id = old.id;
	END;

CREATE VIRTUAL TABLE search_index USING FTS4
(
	contact_id INTEGER NOT NULL,
	data TEXT,
	name TEXT,
	number TEXT,
	UNIQUE(contact_id)
);

CREATE TABLE name_lookup
(
	data_id	INTEGER NOT NULL,
	contact_id INTEGER NOT NULL,
	name TEXT,
	type INTEGER
);

CREATE TABLE phone_lookup
(
	data_id	INTEGER NOT NULL,
	contact_id INTEGER NOT NULL,
	number TEXT,
	min_match TEXT
);


CREATE TABLE my_profiles
(
	my_profile_id			INTEGER PRIMARY KEY AUTOINCREMENT,
	addressbook_id			INTEGER NOT NULL DEFAULT 0,
	display_name			TEXT,
	reverse_display_name		TEXT,
	created_ver			INTEGER NOT NULL,
	changed_ver			INTEGER NOT NULL,
	changed_time			INTEGER NOT NULL,
	uid				TEXT,
	image_thumbnail_path		TEXT,
	deleted				INTEGER DEFAULT 0,
	UNIQUE(addressbook_id)
);

CREATE TRIGGER trg_my_profiles_del AFTER DELETE ON my_profiles
	BEGIN
		DELETE FROM data WHERE contact_id = old.my_profile_id AND is_my_profile = 1;
	END;

CREATE TRIGGER trg_my_profile_update AFTER UPDATE ON my_profiles
	WHEN new.deleted = 1
	BEGIN
		DELETE FROM data WHERE contact_id = old.my_profile_id AND is_my_profile = 1;
	END;